PostgreSQL-SDBC

From The Document Foundation Wiki
Jump to: navigation, search

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
OS X 10.4 to 10.6 yes (--with-libq-path only) no yes unconfirmed
OS X 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.