Bravenec

Informační systémy

Propojení Open Office s databází

Úvod

Ve svém zaměstnání jsem byl postaven před úkol propojit výrobní informační systém s kancelářským prostředím. I když se používá v kancelářích především Miscrosoft Office, tomuto sofwareovému balíku se věnovat nebudu, a pozornost obrátím především na OpenOffice. Informační systém u nás běží nad databází PostgreSQL, s jinými databázemi nemám zkušenosti. Bližší informace o ostatních databázích je možné najít na Internetu - odkazy jsou v samostatné části tohoto článku. Chcete-li se připojit k Nepatrně Blbější Ale Mnohem Rychlejší SQL Databázi (koleduju si o přeshubu, protože uživatelů této databáze je mnohem více než uživatelů PostgreSQL), najdete driver vestavěný přímo v OpenOffice a není třeba jej instalovat.

Odkazy

http://jdbc.postgresql.org: PostgreSQL JDBC Drivers. Zde získáte hotový JDBC driver pro vaši verzi Javy a databázového serveru.
http://www.fankhausers.com/postgresql/jdbc/: PostgreSQL and JDBC FAQ
http://dba.openoffice.org/drivers/postgresql/index.html: Driver PostgreSQL napsaný originálně pro OpenOffice. Neztrácejte s ním čas. Je téměř nepoužitelný. Odkaz uvádím pouze kvůli varování před zklamáním a marnou snahou dosáhnout rozumných výsledků.
http://dba.openoffice.org/FAQ/specific.html: OpenOffice Database Access - Tips'n'tricks
http://www.unixodbc.org: unixODBC - asi funguje, ale překlad a nastavení je poněkud složité

Instalace

Moje experimentování ukázalo, že zřejmě nejpoužitelnější způsob propojení OpenOffice s databází PostgreSQL je přes JDBC. I když je v OpenOffice napsaný driver speciálně pro komunikaci s PostgreSQL, neosvědčil se mi. Při použití nativního driveru pro PostgreSQL jsou problémy s datumovými položkami a s reálnými čísly a možná i mnoho dalších problémů, na které jsem nepřišel, protože jsem se nedokázal přenést ani přes neschopnost driveru pracovat s datumy.

V dalším textu předpokládám, že OpenOffice, PostgreSQL i Java už jsou nainstalované a funkční. Všechny tři balíky bývají celkem běžnou součástí Linuxových distribucí, takže se omezím pouze na popis instalace JDBC driveru.

Driver je pro každou databázi jiný. Při pátrání po JDBC driver hledejte driver konkrétně pro vaši verzi databáze a Javy. V mém případě je to soubor pg74.1jdbc3.jar. Ten překopírujte někam, kam ukazuje proměnná CLASSPATH, případně na takové místo v instalaci Javy, které je určené pro externí knihovny. Než jsem začal psát tento článek, bylo potřeba zkopírovat soubor pg74.1.jdbc3.jar sem:

cp pg74.1.jdbc3.jar /usr/java/j2sdk1.4.1_02/jre/lib/ext/postgres.jar.

Poté, co jsem kvůli Mozille nainstaloval novější verzi Javy, musel jsem driver nainstalovat takto:

cp pg74.1.jdbc3.jar /usr/java/j2re1.4.2_03/lib/ext/postgres.jar.

V principu jde pouze o to zkopírovat driver někam, kde Java hledá knihovny, nejlépe přímo do adresáře, který je určený na externí moduly (lib/ext). Nezapomeňte samozřejmě na to, že soubor musí mít nastavená příslušná přístupová práva: -rw-r--r--

Tím by měla být celá instalace hotová. Aby se dal driver JDBC používat, musí se ještě nakonfigurovat jak databázový server, tak OpenOffice.

Nastavení databáze PostgreSQL

Aby byl databázový server schopen poslouchat na portu TCP/5432 a komunikovat s klienty po síti, musí být TCP/IP komunikace povolená. Jednou z možností je nastartovat databázový server s parametrem -i, čímž se povolí síťová komunikace.

Jiná možnost je upravit konfigurační soubor postgresql.conf, doplnit do něj řádky:

tcpip_socket = true port = 5432

Než začneme s dalšími experimenty, bylo by dobré založit pokusnou databázi. Chcete-li přistupovat do existující databáze s daty, na nichž vám aspoň trochu záleží, pak bych doporučoval jako bezpečnostní opatření vytvořit uživatele, říkejme mu "office", s právy pouze pro čtení. OpenOffice totiž otevírá tabulky i pro zápis a v prostředí offisové tabulky je až příliš jednoduché přepsat nežádoucím způsobem data.

Budete-li ukládat do databáze znaky v češtině, je nutné zadat při zakládání databáze i její kódování. JDBC driver v OpenOffice jinak protestuje a není možné získat z tabulek žádná data. U databáze PostgreSQL se musí zadat kódování databáze při jejím založení:

createdb -E Latin2 pokusy createuser office sql template1 <<! alter user office with unencrypted password 'heslo'; !

Ať už povolíte síťovou komunikaci databázového serveru tak či onak, musíte ještě nakonfigurovat práva pro síťový přístup v souboru pg_hba.conf (předpokládám síť 10.1.1.0/24 - doplňte vlastní hodnoty). V konfiguračním souboru pg_hba.conf lze zvolit, jestli bude při připojení k databázi vyžadované heslo. Chcete-li povolit přístup k databázi bez hesel i z ostatních počítačů v síti, nejen z databázového serveru, použijte místo slova "password" slovo "trust":

host pokusy office 127.0.0.1 255.255.255.255 trust host pokusy office 10.1.1.0 255.255.255.0 password

Vlastníkem vytvořené databáze nemusí být právě uživatel office. Uživatel office dokonce nemusí vůbec existovat jako linuxový účet (či jako účet jiného systému, na kterém provozujete databázový stroj). Nicméně při vytvoření nové databáze je jednodušší, pokud uživatel office existuje i v systému.

Pro potřeby tohoto článku jsem si vytvořil novou databázi a aby nebyla úplně prázdná, vytvořil jsem v ní jednoduchou tabulku a naplnil ji daty:

sql pokusy <<! create table telefonni_seznam ( radek int4 primary key, prijmeni text not null, jmeno text not null, cislo text); insert into telefonni_seznam (radek, prijmeni, jmeno, cislo) values (1, 'Vopička', 'Franta', '789 333 443'); insert into telefonni_seznam (radek, prijmeni, jmeno, cislo) values (2, 'Slepička', 'Franta', '789 333 143'); insert into telefonni_seznam (radek, prijmeni, jmeno, cislo) values (3, 'Bobeček', 'Josef', '789 233 143'); insert into telefonni_seznam (radek, prijmeni, jmeno, cislo) values (4, 'Blonďatá', 'Marie', '789 233 143'); grant all on telefonni_seznam to office; !

Připojujete-li se k existující databázi, je potřebné dát uživateli office alespoň minimální práva na tabulky, ke kterým chcete z OpenOffice přistupovat. Chcete-li do tabulek vkládat a používáte datový typ serial, nezapomeňte ani na příslušné sekvence:

grant select on databazova_tabulka to office; grant update on databazova_tabulka to office; grant insert on databazova_tabulka to office; grant select on databazova_tabulka_klic_seq to office; grant update on databazova_tabulka_klic_seq to office;

Nastavení datových zdrojů

Máme-li nastavenou databázi a nainstalovaný JDBC driver, můžeme se pokusit o propojení OpenOffice s daty. Do konfiguračního formuláře se dostaneme z menu přes Nástroje->Zdroje dat...

Ve formuláři klikněte na tlačítko "Nový zdroj dat" na vrcholku seznamu zdrojů dat. Tlačítko je nenápadně umístěné a tím, že jsem vás na ně upozornil, jsem vám ušetřil spoustu práce a přemýšlení, jak se v OpenOffice připojit k různým databázím.

Na kartě "Obecné" připojení pojmenujte, zvolte typ databáze (JDBC) a vyplňte URL zdroje dat. URL může vypadat například takto:

jdbc:postgresql://dbserver/pokusy

V URL se říká, že se má použít interface JDBC, driver PostgreSQL, připojovat se budeme k serveru dbserver k databázi pokusy.

Na kartě JDBC vyplňte třídu JDBC ovladače. Ta má být pro databázi PostgreSQL "org.postgresql.Driver". Další položkou, kterou je třeba vyplnit, je uživatelské jméno. V našem příkladě použijte jméno "office". Zaškrtnout musíme ještě případné vyžádání hesla. Office se sám od sebe na heslo neptá a vyžaduje-li databáze přístup pomocí hesla, nedovede se OpenOffice připojit, není-li tato volba zaškrtnutá. Tím je veškeré nezbytné nastavení hotovo a můžeme se pokusit o připojení k databázi. Přepnutím na kartu "Tabulky" se OpenOffice pokusí připojit k databázi a vytvořit seznam tabulek.

Nástroj pro správu datových zdrojů je sice primitivní, ale je poměrně mocný a nebezpečný. Nechci samozřejmě nikoho navádět, ale označte některou tabulku a klikněte na obrázek s červeným křížkem. Pak se podívejte, jak byla nastavená v databázi přístupová práva pro uživatele office a jestli tabulka ještě existuje a zauvažujte nad tím, o jak důležitá data jste jediným klikancem přišli. Právě jednoduchost, s jakou lze tímto nástrojem tabulky vytvářet, modifikovat a likvidovat, je důvodem pro maximálně restriktivní nastavení přístupových práv z OpenOffice. V žádném případě nedávejte běžným uživatelům práva pro zápis nebo modifikace tabulek. Naštěstí lze v PostgreSQL snadno vytvořit pravidla i pro zápisy do view, naprogramovat funkce pro kontrolu dat a zakázat přístupy do skutečných tabulek s daty.

V OpenOffice lze databázové tabulky jediným kliknutím myši i vytvářet, nejen mazat. Vytvářet lze ale jen velmi primitivní tabulky bez vazeb na své okolí. Mně tento způsob tvorby tabulek nikdy nevyhovoval, protože přes OpenOffice nelze zadat většinu vlastností, kterými moje tabulky disponují (cizí klíče, kontroly mezí, unikátní indexy a podobně).

Propojení databázových tabulek s dokumenty OpenOffice

Podařilo-li se vám připojit k databázi a vylistovat seznam tabulek, nic už vám nebrání otevřít databázovou tabulku a naimportovat data například do tabulkového kalkulátoru.

Ještě než začnete, rád bych varoval před chybou, která mě stála spoustu času a námahy. Množství tabulek jsem vytvářel v MS Excelu 2000 s použitím MS Query. Jste-li stejně jako já zvyklí na import dat do Excelu, nacvičený postup vás zavede přes položku v menu Data->Průvodce daty do jámy pekelné. Tudy cesta nevede. Nástroj je určený pouze na tvorbu kontingenčních tabulek a pokud jste alespoň minimálně sběhlí v SQL dotazech, doporučuji použít raději uložené SQL dotazy či view - tedy zpracovávat data na straně databázového serveru. O uložení dat ví databázový server mnohem více než si tabulkový kalkulátor vůbec dovede představit a dokáže data zpracovat s neporovnatelně větší efektivitou a mnohem, mnohem rychleji.

V OpenOffice existuje mnohem přímočařejší nástroj, než je MS Query v Office 2000 (Možná, že v novějších verzích MS Office existují podobné nástroje jako v OpenOffice. Nevím. Novější office od Microsoftu jsem dosud neměl příležitost používat). Otevřete si novou tabulku v tabulkovém kalkulátoru a stiskněte klávesu F4 nebo zvolte v menu položku Zobrazit->Zdroje dat. Okno s tabulkou se rozdělí a v jeho horní části se otevře nástroj pro práci s databázemi.

Data lze přímo v nástoji nejen prohlížet, ale i upravovat či mazat a vkládat do tabulek nové věty. Není problém změnit data v tabulkách k nepoznání i pouhým omylem. To je další důvod pro řádné nastavení přístupových práv a striktní oddělení jednotlivých uživatelů.

Budete-li zkoušet měnit data v ukázkové tabulce telefonni_seznam, pravděpodobně nenarazíte na žádný zádrhel. Jakmile ale začnete experimentovat s vlastními tabulkami, možná zjistíte, že některé tabulky prostě nelze změnit, i kdybyste se na hlavu postavili, a OpenOffice vám odmítne prozradit, proč se chová tak nevypočitatelně. Je to kvůli primárním klíčům - aby byly databázové údaje v OpenOffice modifikovatelné, musí mít tabulka vytvořený unikátní primární klíč. OpenOffice jinak nedovede rozeznat jednotlivé věty v tabulce od sebe a než by riskoval nespokojenost uživatele s tím, že oprava jedné věty přepíše půl databázové tabulky, raději zakáže změny úplně.

Ale článek jsem psal hlavně kvůli importu dat do dokumentů OpenOffice. Zvyklý na oklikové postupy z MS Office 2000 jsem marně klikal na tabulku pravým tlačítkem, bloudil v kontingenčních tabulkách a proklínal tvůrce OpenOffice z tak šíleného programu. Řešení je přitom jednoduché a zcela přímočaré: tabulku uchopte myší za prázdné levé horní pole v nadpisech a přetáhněte na požadované místo v dokumentu. Data importovaná do tabulkového kalkulátoru nevyžadují žádné další operace. Importujete-li ale data do textového editoru, máte ještě možnost zvolit způsob zobrazní dat (tabulka, pole, text a spoustu dalších formátovacích atributů).

Data jednou iportovaná do dokumentu už nemají přímou vazbu na databázi. Změníte-li některé údaje, změny se do databáze nepromítnou. Neznamená to ovšem, že vazba na databázi zmizela úplně. Oblasti s databázovými dotazy lze zobrazit v nástroji Data->Vybrat Oblast a následně obnovit data z databáze volbou Data->Obnovit Oblast;

Omezení

Je-li v tabulce definováno více datových oblastí, nedokázal jsem obnovit všechny oblasti najednou - vždy bylo potřeba každou datovou oblast označit a obnovit zvlášť. Možná, že na to existuje podobně jednoduchý fígl jako s přetažením tabulky do dokumentu myší - já jsem na něj zatím nepřišel.

OpenOffice nedovede importovat některé typy dat. Nepoužívám samozřejmě všechny možné datové typy v PostgreSQL. V databázi mám uložená spíše data ekonomického významu a nikdy jsem nepoužil různé geometrické či internetové datové typy. Narazil jsem jen u datového typu interval. Tam lze naštěstí řešit situaci přepočtem například na hodiny přímo v databázi.

Další omezení, tentokrát mnohem nepříjemnější, je praktická nemožnost práce s primárními klíči typu serial při modifikaci dat. Tento datový typ se při insertu běžně nevkládá a obsah datové položky tvoří sama databáze - Office se o vygenerovaných datech nedoví. Při jednoduchém importu do dokumentů se ale tento problém nijak neprojevuje. Starosti začíná dělat až při práci s formuláři, které ale nejsou tématem tohoto článku. Narazit na toto omezení můžete při postupech popsaných v tomto článku jen při změnách dat v nástroji "Zdroje dat".

Závěrem

V článku jsem předvedl pouze nejjednodušší způsob propojení databáze s OpenOffice. I když je v článku zmíněna pouze databáze PostgreSQL, práce s libovolnou jinou SQL databází na principu klient-server se nebude příliš lišit. Nebude ani příliš záležet na způsobu připojení - připojení přes JDBC, ODBC či přes nativní driver se bude lišit jen detailech.

V OpenOffice lze vytvářet i formuláře přímo propojené s databází. Takové formuláře pak lze využít i jako programový prostředek pro sběr dat. I když jsem nedovedl své experimenty v této oblasti až do finále a nakonec jsem žádné smysluplné formuláře nikdy nevytvořil a nepoužívám, podařilo se mi nasbírat pár zkušeností, které by mohly v budoucnu přinést pokračování v článcích s touto tématikou.

Přejít na navigaci stránky