PostgreSQL-SDBC

    From The Document Foundation Wiki

    Home of the native (SDBC) PostgreSQL driver for LibreOffice.

    Status

    Merged into master branch and is part of LibreOffice 3.5. Has been converted to an optionally installable part of LibreOffice proper, not an extension anymore. State in LibreOffice 3.5:

    Platform --with-system-postgresql or --with-libpq-path --without-system-postgresql
    builds runs builds runs
    GNU/Linux yes yes yes yes
    macOS 10.4 to 10.6 yes (--with-libq-path only) no yes unconfirmed
    macOS 10.7 or later yes probably yes unconfirmed
    MS Windows unconfirmed probably not yes yes

    Remarks:

    • --without-system-postgresql builds did not support GSSAPI or Kerberos authentication on GNU/Linux and *BSD. They do since 3.6.
    • --with-system-postgresql/--with-libpq-path builds support whatever the libpq that is linked to supports, usually everything.

    Was previously developed as a partial fork of libreoffice-3-4 repositories.


    Backward compatibility

    Generally, requires PostgreSQL 8.4 or later. However, adding PostgreSQL 8.4 array unnest function to PostgreSQL 8.3 or earlier might make it compatible with this driver (untested).

    3.5.0 is read-only with PostgreSQL 8.4 (read/write with PostgreSQL 9.0 or later). This has been fixed in 3.5.1 (and later).

    Building from Source

    • Get LibreOffice sources (master or 3.5 branch) as usual.
    • You need lipq (PostgreSQL C interface) development packages. Get them directly from the PostgreSQL project or from OS-specific packages/ports (Debian, Fedora, Fink, MacPorts, ...). Get version 9.0 or later. The package is usually called something like libpq-dev, libpq-devel, postgresql-dev or postgresql-devel.
    • Pass options "--enable-postgresql-sdbc" to autogen.sh
    • If you use --with-system-postgresql and pg_config is not in your PATH or other default location, set environment variable PGCONFIG or use --with-libpq-path.
    • Build & Install LibreOffice

    The generated LibreOffice will have PostgreSQL-SDBC included.

    Binaries

    Packaged in Debian sid (unstable), and from there trickled down to Debian wheezy (testing) and Ubuntu oneiric (universe).

    PostgreSQL-SDBC is bundled with LibreOffice 3.5 beta / release candidate builds on all platforms.

    It can also be downloaded from the LibreOffice extensions website.

    Bug reports

    Use Bugzilla, product LibreOffice, component Database, and assign the bug to "lionel@mamane.lu" (without the quotes).

    Obsolete Instructions (for older versions)

    Getting Source

    Get the original LibreOffice boostrap repository; make sure to take libreoffice-3-4 branch, a subbranch (e.g. libreoffice-3-4-3) or a specific tag (release) in the 3.4.x series (e.g. libreoffice-3.4.3.2):=

    $ git clone -b libreoffice-3-4 git://anongit.freedesktop.org/libreoffice/bootstrap libo-3.4
    $ cd libo-3.4

    Get the PostgreSQL-SDBC changes to bootstrap:

    $ git remote add pgsql-sdbc git://anonscm.alioth.debian.org/users/lmamane/libreoffice/bootstrap.git
    $ git fetch pgsql-sdbc
    $ git checkout -b lo-3.4-sdbc-pgsql remotes/pgsql-sdbc/lo-3.4-sdbc-pgsql
    $ git rebase remotes/origin/libreoffice-3-4

    Get the rest of the original LibreOffice sources. Make sure to use the same branch or tag as for bootstrap.

    $ ./autogen.sh --appropriate-options
    $ ./g clone -b libreoffice-3-4

    Get the PostgreSQL-SDBC code proper:

    $ cd clone/libs-core
    $ git remote add pgsql-sdbc git://anonscm.alioth.debian.org/users/lmamane/libreoffice/libs-core.git
    $ git fetch pgsql-sdbc
    $ git checkout -b lo-3.4-sdbc-pgsql remotes/pgsql-sdbc/lo-3.4-sdbc-pgsql
    $ git rebase remotes/origin/libreoffice-3-4
    $ cd ../..

    Updating

    To update the general LibreOffice code:

    ./g pull --rebase origin libreoffice-3-4

    To update the PostgreSQL-SDBC code:

    git fetch pgsql-sdbc
    git checkout --no-track -b TMP remotes/pgsql-sdbc/lo-3.4-sdbc-pgsql
    git rebase lo-3.4-sdbc-pgsql
    git checkout lo-3.4-sdbc-pgsql
    git merge --ff-only TMP
    git branch -d TMP
    cd connectivity
    git fetch pgsql-sdbc
    git checkout --no-track -b TMP remotes/pgsql-sdbc/lo-3.4-sdbc-pgsql
    git rebase lo-3.4-sdbc-pgsql
    git checkout lo-3.4-sdbc-pgsql
    git merge --ff-only TMP
    git branch -d TMP
    cd ..

    Compiling, Installing

    Compile LibreOffice as usual. Points to be aware of:

    • pg_config must be in your ${PATH}
    • The driver needs libpq version >= 9.0 to compile, but the resulting binary should be able to connect to PostgreSQL 8.4.

    After successful compilation, the PostgreSQL-SDBC extension is in connectivity/${INPATH}/lib/postgresql-sdbc-${VERSION}.oxt, where ${VERSION} is for example "0.8.1" (without the quotes). Install that ".oxt" file, either using

    unopkg add /path/to/postgresql-sdbc-${VERSION}.oxt

    or the GUI extension manager (menu: Tools / Extension Manager).

    If you recompiled a new .oxt file and ${VERSION} has not changed, you need to install with

    unopkg add --force /path/to/postgresql-sdbc-${VERSION}.oxt

    In all cases, you can use "--shared" to install the extension for all users or not, as you wish and/or require.

    Usage

    During Base DB setting wizard the user is asked for a connection string without an explanation of its structure. A connection string should look like this:

    dbname=postgres hostaddr=127.0.0.1 port=5432 user=db_user password=libreoffice

    The information was found in http://ask.libreoffice.org/question/459/generic-form-of-url-for-postgresql

    Major changes since 0.7.x versions

    Version 0.8.0 (tag lo-3.4-sdbc-pgsql-0.8.0)

    • FIX: write to PostgreSQL tables with OO.org >= 3.3 and LibreOffice >= 3.3
    • ENHANCEMENT: Database Metadata more correct
    • FIX: LibreOffice will not use ODBC-specific escapes with PostgreSQL-SDBC
      • FIXES: filter and query with conditions on columns of date don't work
      • FIXES: (full) outer join in query design mode
    • ENHANCEMENT: provide a proper (long) description
    • Various small fixes and code cleanups
    • FIX: Handle passwords with spaces, backslashes, single quotes, etc. Original bug report

    Version 0.8.1 (tag lo-3.4-sdbc-pgsql-0.8.1)

    • FIX: write to PostgreSQL tables with LibreOffice >= 3.5

    Later changes:

    • No separate changelog maintained anymore. You can use git log -- connectivity/source/drivers/postgresql for a detailed changelog.