Před šesti lety jsem pro AbcLinuxu.cz napsal článek o propojení kancelářského balíku OpenOffice.org s databází PostgreSQL. I když se od té doby s databází ani s kancelářským balíkem nic zásadního neudálo, pár změn a zjednodušení se objevilo.
S databázemi v OpenOffice.org pracuji poměrně často. Ale i když používám data z externího zdroje prakticky každý den, samotné propojení databáze s kancelářským balíkem nastavuji jen velmi zřídka. Detaily si samozřejmě nepamatuji a věrný pomocník Google mě vždy spolehlivě odešle na můj šest let starý článek, který se v mnoha detailech liší od situace platné dnes, a nastavovat podle něj propojení z aktuálního OpenOffice.org je docela obtížné. Není tedy na škodu oprášit staré téma a popsat některé postupy znovu.
Tento článek je napsaný na míru databázi PostgreSQL, většinu informací však můžete využít i při propojení na libovolnou jinou databázi – odlišnosti budou především v použitém ovladači a v nastavení databáze.
Stejně jako před šest lety, i dnes jsou možnosti dvě: JDBC a SDBC (ovladač napsaný speciálně pro OpenOffice.org). Sám používám ovladač JDBC, který mě za všechny ty roky používání nikdy nezklamal.
Ovladač JDBC je vyvíjen nezávisle na kancelářském balíku. Jde o ovladač sloužící k propojení prostředí Java a databáze Postgres. Bývá nasazován v mnohem širším spektru produktů než jen v jednom kancelářském balíku – dá se tedy předpokládat, že bude dobře proklepnutý a prověřený v různých podmínkách. Balík stáhnete zde: http://jdbc.postgresql.org/download.html
Balíků je k dispozici hned několik, výběr balíku zavisí na použité verzi Javy. Máte-li nainstalované JDK 1.6, použijte verzi JDBC4. U většiny současných desktopových distribucí je použita právě verze 1.6 (koukal jsem na Gentoo, Ubuntu a Debian). Jiný než nejnovější driver JDBC4 tedy použijte pouze v případě staršího či léta neaktualizovaného prostředí.
Ovladač SDBC je software vyvíjený speciálně pro kancelářský balík OpenOffice.org. SDBC najdete na adrese http://dba.openoffice.org/drivers/postgresql/index.html.
Ovladači SDBC se nebudu v dalším textu věnovat. Podle samotných tvůrců je ovladač ve stavu „lepší beta“. A mohu-li si vybrat mezi betou a osvědčeným řešením, volím osvědčené řešení.
Abyste se mohli připojit k databázi, musí být databázový server nakonfigurovaný tak, aby poslouchal příchozí požadavky na spojení i přes síť a umožňoval dálková připojení. V případě databáze postgresql je nutné spouštět server s parametrem -i.
Jinou možností s mnohem podrobnějším nastavením je povolení síťových připojení k databázovému serveru v souboru postgresql.conf (číslo portu nemusíte uvádět, pokud chcete použít standardní port 5432):
listen_addresses='*' port=5432
Hvězdičkou povolíte připojení na všech adresách. Povolit můžete i připojení pouze na lokálním počítači (uveďte 127.0.0.1) nebo na vybraných adresách (má-li váš server více adres nebo síťových karet). Adresy oddělujte mezerami:
listen_addresses='127.0.0.1 10.10.1.12'
Máte-li takto svůj server nakonfigurovaný, neznamená to ještě, že se budete moci připojit. Dálkový přístup k databázím může být potřeba nastavit ještě v souboru pg_hba.conf, například:
# TYPE DATABASE USER ADDRESS METHOD host all all 127.0.0.1/32 trust host all all 10.10.1.0/24 password
Podle uvedené konfigurace se můžete ke všem svým databázím připojit bez hesla z lokálního počítače (tedy přímo na serveru). Při připojení ze sítě 10.10.1.0/24 bude vyžadováno heslo. V souboru pg_hba.conf můžete pro každou databázi velmi podrobně nastavit, kdo a odkud se může k databázi připojovat.
Postup je obecný pro libovolnou databázi. Podobně se můžete připojit k čemukoli, k čemu je k dispozici ovladač JDBC.
Ovladač JDBC získáte v podobě archivu jar. Archiv si uložte někam na disk. Pracuje-li na počítači více uživatelů, můžete uložit archiv například do adresáře /usr/local/bin/. Já mám svůj počítač sám pro sebe, ovladač s nikým nepotřebuji sdílet. Pro podobné drobnosti (ovladače, skripty) jsem si vytvořil adresář ~/bin. Pro instalaci ovladače tak nepotřebuji rootovská práva (pro zápis do /usr/local/bin), na druhou stranu takto mohu ovladač používat pouze já.
OpenOffice.org je potřeba informovat, kde má ovladač hledat. Nastavení najdete v menu: „Nástroje, Volby, OpenOffice.org, Java, Cesta k třídám, Přidat archiv“.
Instalace JDBC driveru
Po nastavení je OpenOffice.org nutné restartovat. Máte-li nastartovaný OpenOffice.org ve Windows, pamatujte na to, že musíte ukončit i nástroj pro rychle spuštění OpenOffice.org. Najdete ho v systémové části panelu.
Po restartu můžete pokračovat připojením databáze. V následujícím textu předpokládám, že se připojujete k již existující databázi, kterou nebudete přes OpenOffice.org upravovat (na správu databáze jsou k dispozici rozhodně vhodnější nástroje než kancelářský balík).
I když se budeme připojovat k existující databázi, v OpenOffice.org budeme vytvářet databázi novou (OpenOffice.org si ukládá připojení na externí databázi jako vlastní samostatný soubor s příponou odb). Databázi vytvoříme v menu: „Soubor, Nový, Databáze“. Na první kartě v průvodci vytvořením databáze zaškrtněte volbu „Připojit se k databázi“ a zvolte JDBC (najdete-li v seznamu i volbu postgres, ignorujte ji; pravděpodobně jste zkoušeli instalovat i ovladač SDBC).
Připojení k existující databázi
Na další kartě je potřeba vyplnit údaje o umístění databáze a o použitém ovladači. Do pole „Třída JDBC ovladače“ napište „org.postgresql.Driver“ – u JDBC ovladačů pro připojení k jiné databázi se bude název třídy nejspíš lišit. JDBC ovladač můžete otestovat tlačítkem „Otestovat třídu“. Neprojde-li test, nemá smysl pokračovat – ovladač je nainstalovaný špatně a spojení se vám nepodaří navázat.
Pokud skončil test třídy v pořádku, můžete pokračovat dále. Do pole URL datového zdroje napište, kde je databáze k nalezení a jak se k ní může driver připojit. URL adresa pro připojení má následující tvar:
postgresql://databázový-server:port/databáze
Jako databázový server zadejte jméno nebo IP adresu počítače, na kterém běží databáze. Může to být například localhost nebo 127.0.0.1, běží-li databáze na stejném stroji, na kterém spouštíte i OpenOffice.org. Při použití standardního portu 5432 není třeba číslo portu uvádět.
Zadání parametrů pro připojení
Na další kartě se vyplňují informace o uživateli a heslu. Spojení opět ověřte tlačítkem „Otestovat spojení“. Neprojde-li ověření, opět nemá smysl pokračovat. Případnou chybu hledejte na předchozí kartě v poli URL nebo v logu databáze. Znovu připomínám, že k úspěšnému připojení musí být databázový server spuštěn s přepínačem -i nebo příslušnou volbou v postgresql.conf, a že v pg_hba.conf musí být konkrétní spojení povoleno.
Zadání uživatele a hesla a ověření spojení
Na poslední kartě nastavení dokončíte a databázi (pouze informaci o připojení) uložíte. Na kartě zvolte „Ano, zaregistrovat databázi“ – to je pravděpodobně to, co budete ve svých dokumentech potřebovat. Vaše databáze bude díky tomu dostupná ve všech dokumentech jako jeden z datových zdrojů. Protože databáze, ke které jsme se takto připojovali, už je hotová, ponecháme pole „Otevřít databázi pro úpravy“ nezaškrtnuté – tabulky v databázi PostgreSQL je možné spravovat mnohem efektivněji jinde. Nakonec uložte své připojení do souboru:
Dokončení připojení, registrace databáze
Nejjednodušší přístup ke svým datům získáte přes „Zdroje dat“. Formulář zapnete buď v menu: „Zobrazit, Zdroje dat“, nebo jednoduše klávesou F4. V seznamu databází byste měli vidět všechny zaregistrované databáze. Ve stromu najděte tabulku, která vás zajímá, a klikněte na ni. V tabulce vpravo by se mělo vypsat několik řádků z vybrané tabulky.
Jste-li zvyklí na jiný kancelářský balík z jiného prostředí, kde drag&drop funguje pouze v několika málo vybraných případech, může vám chvíli trvat, než zjistite, jak načíst data do tabulkového kalulátoru. Abyste dlouho netápali, nakreslil jsem do obrázku šipku. Na začátku šipky uchopíte myší data, která chcete mít v sešitu, a na konci šipky data upustíte na místo, kde je chcete mít. Klávesou F4 pak můžete zdroje dat zavřít.
Zdroje dat v OpenOffice.org
S databází v sešitu tabulkového kalkulátoru pak můžete pracovat obvyklými databázovými funkcemi (DGET, DCOUNT, DMIN, DMAX a podobně). Do databáze v sešitu nedělejte žádné změny, při načtení nových dat z databáze (v menu „Data, Obnovit oblast“) byste o všechny změny přišli.
Výpis dat z tabulky může být užitečný, někdy je však vhodnější pro složitější zpracování napsat vlastní funkci v interním procedurálním jazyku databáze (PL/PgSQL) a do sešitu tabulkového kalkulátoru vložit pouze hotová data (například jeden řádek).
Upozorňuji, že doposud jsme se v článku pohybovali prošlapanými cestičkami – instalace ovladače, připojení databáze a získání dat vám nemusí zabrat více než čtvrt hodiny. Jakmile však začnete experimentovat s makry, můžete mít o zábavu s OpenOffice.org vystaráno na mnoho dlouhých zimních večerů.
Pro studium maker v OpenOffice.org mohu doporučit tento odkaz: http://wiki.services.openoffice.org/wiki/Documentation/BASIC_Guide.
Makra můžete spouštět, tvořit a editovat ve správci maker, v menu „Nástroje, Makra, Správce maker, OpenOffice.org Basic…“. Hotové makro pro získání dat může vypadat například takto:
AbcLinuxu.cz
ITBiz.cz
HDmag.cz
AbcPráce.cz
Sub PosledniData
Dim DatabaseContext as Object
Dim Doc As Object
Dim Sheet As Object
Dim Titul As String
' Získání parametrů ze sešitu
' Konkrétní sešit je pojmenovaný IC, každá položka, se kterou pracuji, má své vlastní jméno.
' Nemusím tedy adresovat buňku či rozsah klasicky (A1:D30), ale můžu použít jméno (TITUL).
Doc = ThisComponent
Sheet = Doc.Sheets.getByName("IC")
Titul = Sheet.getCellRangeByName("TITUL").String
' Připojení k databázi
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("DatabázeOpce")
' Pokud databáze potřebuje heslo, zeptej se
If Not DataSource.isPasswordRequired Then
Connection = DataSource.GetConnection("","")
Else
InteracionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If
' Nyní už samotný dotaz
' Z databáze vybereme nejčerstvějších deset údaju zadaného titulu
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("select to_char(datum, 'DD.MM.YYYY'), open, low, high, close " & _
" from data where titul = (select titul from titul where nazev='" & Titul & "') " & _
" order by datum desc limit 10;")
' Získaná data uložit do sešitu
if Not IsNull(ResultSet) Then
Dim i As Integer
i = 0
Dim DData as Object
DData = Sheet.getCellRangeByName("DDATA")
While ResultSet.next
' Neumím pracovat s daty, v ukázkovém příkladu mi to nevadí, zobrazím datum jako řetězec
DData.GetCellByPosition(0,i).Value = ResultSet.getString(1)
DData.GetCellByPosition(1,i).Value = ResultSet.getDouble(2)
DData.GetCellByPosition(2,i).Value = ResultSet.getDouble(3)
DData.GetCellByPosition(3,i).Value = ResultSet.getDouble(4)
DData.GetCellByPosition(4,i).Value = ResultSet.getDouble(5)
i = i + 1
Wend
End If
End Sub
Makro spustíte ze správce souborů, pro spouštění makra lze ale vytvořit v sešitu i tlačítko. Zapněte si potřebnou nástrojovou lištu v menu: „Zobrazit, Panely nástrojů, Ovládací prvky formuláře“. Formulář přepněte do návrhového režimu (druhé tlačítko) a zapněte tvorbu tlačítek (sedmé nebo osmé tlačítko). Oba ovládací prvky jsou na přiloženém obrázku zapnuté:
Panel nástrojů: Ovládací prvky formuláře
V sešitu vytvořte vlastní tlačítko a v kontextovém menu tlačítka pak volbou „Ovládací prvek…“ vyvolejte formulář s nastavením tlačítka. Na kartě „Události“ najděte událost „Provést akci“ (tj. akce po kliknutí na tlačítko) a přiřaďte k akci své vytvořené makro.
Propojení makra s tlačítkem
Po přiřazení akce vypněte na nástrojové liště návrhový režim a můžete svůj výtvor vyzkoušet.
Pomocí maker můžete z databáze získat data, která byste jinak v kancelářském balíku zpracovávali jen velice těžko. Přístup k datům přes makra používám například ve své kalkulačce pro výpočty investiční strategie Iron Condor, kde z databáze získávám pravděpodobnost určitého chování trhu na základě dat z posledních dvaceti let. Na přiloženém obrázku je celá tabulka „Pravděpodobnosti“ generovaná jedním makrem s několika SQL dotazy (v databázi se volají vlastní PL/PgSQL funkce). Hotovou kalkulačku najdete na adrese http://brave-equity.com/cs/iron-condor-kalkulacka.
Kalkulačka Iron Condor
Nástroje: Tisk bez diskuse
Tiskni
Sdílej:
8.9.2010 00:46
Vlastimil Ott | skóre: 66
| blog: Plastique
| Opava
8.9.2010 06:38
Petr Bravenec | skóre: 42
| blog: Bravenec
8.9.2010 09:21
Petr Bravenec | skóre: 42
| blog: Bravenec
.
8.9.2010 07:44
okbob | skóre: 27
| blog: systemakuv_blog
| Benešov
8.9.2010 08:12
Petr Bravenec | skóre: 42
| blog: Bravenec
Linux-MINT (verze 9) + OpenOffice.org - Base.
|
|
|
|
|
