Bravenec

Informační systémy

Propojení OpenOffice.org s databází PostgreSQL

Úvod

Před šesti lety jsem pro abclinuxu 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ázemí 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.

Možnosti propojení

Stejně jako před šest lety, i dnes jsou možnosti dvě: JDBC a SDBC (driver napsaný speciálně pro OpenOffice.org). Sám používám driver JDBC, který mě za všechny ty roky používání nikdy nezklamal.

JDBC

Driver JDBC je vyvíjený nezávisle na kancelářském balíku. Jde o driver 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žitá 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í.

SDBC

Driver SDBC je software vyvíjený speciálně pro kancelářský balík OpenOffice.org.

Driver SDBC najdete na adrese

http://dba.openoffice.org/drivers/postgresql/index.html

Driveru SDBC se nebudu v dalším textu příliš podrobně věnovat. Podle samotných tvůrců je driver 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í.

Nastavení databáze PostgreSQL

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 nutné 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.

Instalace JDBC

Postup je obecný pro libovolnou databázi. Podobně se můžete připojit k čemukoli, k čemu je k dispozici JDBC driver.

Driver 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, driver s nikým nepotřebuji sdílet. Pro podobné drobnosti (drivery, skripty) jsem si vytvořil adresář ~/bin. Pro instalaci driveru tak nepotřebuji rootovská práva (pro zápis do /usr/local/bin), na druhou stranu takto driver mohu používat pouze já.

OpenOffice.org je potřeba informovat, kde má driver hledat. Nastavení najdete v menu: "Nástroje, Volby, OpenOffice.org, Java, Cesta k třídám, Přidat archiv".

Po nastavení je OpenOffice.org třeba 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 systray.

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 SDBC driver):

Na další kartě je potřeba vyplnit údaje o umístění databáze a o použitém driveru. 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 driver můžete otestovat tlačítkem "Otestovat třídu". Neprojde-li test, nemá smysl pokračovat - driver 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ště, 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.

Na další kartě se vyplňují informace o uživateli a hesle. 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řepinačem -i nebo příslušnou volbou v postgresql.conf, a že v pg_hba.conf musí být konkrétní spojení povoleno.

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:

Přístup k datům z OpenOffice.org

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 taulkového kalulátoru. Abyste dlouho netápali, nakreslil jsem do obrázku šipku. Na začátku šipky uchopíte myší "co chcete" a na konci šipky obsah upustíte na místo, kde jej chcete mít. Klávesou F4 pak můžete zdroje dat zavřít.

S databázi 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.

Získání dat makrem

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 ve interním procedurálním jazyku databáze (Pg/PlSQL) a do sešitu tabulkového kalkulátoru vložit pouze hotová data (například jeden řádek). Přístup k datům přes makra používám například ve své kalkulačce pro návrh investiční strategie IronCondor, 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í Pg/PlSQL funkce). Hotovou kalkulačku najdete na adrese http://brave-equity.com/cs/iron-condor-kalkulacka.

Upozorňuji, že doposud jsme se v článku pohybovali prošlapanými cestičkami - instalace driveru, 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:

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 datumy, 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é:

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.

Po přirazení akce vypněte na nástrojové liště návrhový režim a můžete svůj výtvor vyzkoušet.

Přejít na navigaci stránky