HowTo Use Postgresql Database System with Jena2

What is PostgreSQL

PostgreSQL(pronounced Post-Gres-Q-L.) is an enhancement of the POSTGRES database management system, a DBMS research prototype developed at the University of California - Berkeley in the 1990s.. PostgreSQL is open source and supported by an active developer community.  It runs on a variety of hardware and software platforms with excellent performance. See http://www.postgresql.org for more information.

Jena2 has been tested with the PostgreSQL Standard binary release. Jena tables are created using InnoDB tables which provide ACID transaction support. Modifications to Jena database models are immediately written to the database. If the application has an open transaction (see model.begin()), the changes are committed (u.e. visible to other Jena applications) when the application does a commit. Otherwise, the changes areimmediately committed to the database (i.e., each modification is its own transaction). Jena communicates with MySQL through a JDBC driver.

Download and Installation of PostgreSQL

These instructions assume you are using the binary distribution of PostgreSQL. If you prefer the source code disitribution, see the instructions at http://www.postgresql.org for downloading and building from the source. Currently, Jena2 has only been tested with Windows XP.

Jena2 has been tested with PostgreSQL on WindowsXP with the following versions.

The PostgreSQL server runs on Windows using Cygwin. The Cygwin package provides a UNIX-like API on top of the Win32 API. These notes are taken directly from the FAQ_MSWIN in the doc/postgresql subdirectory of the Cygwin distribution. They are provided here as a convenience. If something goes wrong, please refer to those notes. Any commands mentioned below should be performed in a Cygwin shell.

PostgreSQL recommends creating a separate user account for the PostgreSQL server. If it is not required. If you are just experimenting or require only single-user access, you may find it more convenient to simply run the server under your own user account. If you wish to create a separate server account, follow the directions in Section 3.1 of the PostgreSQL 7.3.3 Administrator's Guide. Then, perform the commands below while using that account.

1. Install the latest Cygwin package, available at http://cygwin.com/. A pre-built PostgreSQL is part of the standard Cygwin distribution and is installed by Cygwin's setup.exe. You are encouraged to use this version unless it does not meet your needs. Please read the README file, /usr/doc/Cygwin/postgresql-${version}.README, where "${version}" is the version (e.g., 7.3.3).

2. As of this writing, PostgreSQL still depends on cygipc. But that may change in the future as cygipc is deprecated. Install the latest cygipc package, available at http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/index.html. If you already have cygipc installed, do not use versions prior to 1.04, they will not work. Use the following command to install the cygipc package:
       $ tar -C / -xjf cygipc-${version}.tar.bz2
   where "${version}" is the version (e.g., 1.14).

3. The Cygwin bin directory has to be placed in the path before the Windows program directories, because the sort.exe has to be taken from Cygwin, not Windows.

4. Start ipc-daemon from the cygipc package. Use "net start ipc-daemon", if ipc-daemon is installed as a service; otherwise, use "ipc-daemon &". This program needs to be running anytime you start the PostgreSQL server (postmaster) or initialize a database (initdb).

5. Before creating a database, a PostgreSQL database cluster must be created and initialized. This is simply a data storage area on the disk to hold a collection of databases accessible by a single instance of a PostgreSQL server. Use the following command:
       $ initdb -D /usr/local/pgsql/data
   Any directory may be used although the one given above is a popular convention. Note, initdb will create the directories if they do not exist.

6. Creating the database cluster also installs a PostgreSQL configuration file in the cluster directory. The file name is postgresql.conf and this file holds various runtime configuration parameters for PostgreSQL. You need to set the following configuration parameter in postgresql.conf: tcpip_socket = true.  This configures the PostgreSQL server to accept TCP/IP connections. An alternative to setting the tcpip_socket parameter is to use the "-i" option when starting Postmaster (Step7).

7. Start the PostgreSQL server with the following command.
           $ postmaster [-i] -D /usr/local/pgsql/data > logfile 2>&1 &
   The "-i" option is an alternative to setting the tcpip_socket configuration parameter (Step6). Check the logfile to ensure the server started correctly. Of course, specify the actual location of your database cluster if you did not use the conventional location in Step5. To stop the server, send it a signal (SIGTERM disables new transaction but allows current ones to continue, SIGINT aborts existing transactions, SIGQUIT is immediate shutdown which will require database recovery to be run upon restart; SIGKILL is not recommended as PostgreSQL may be unable to release system resources). As an alternative, the script pg_ctl can be used to start and stop the server, e.g., pg_ctl start and pg_ctl stop. It takes the same options as the postmaster command.

8. You can use either the "Create DATABASE [ database name ]" command from within psql, or else the createdb script from the shell to create a new database.
         $ createdb [ database name ]

9. Start the PostgreSQL interactive SQL interface.
       $ psql [ database_name ]
   Note: to exit the SQL interface, enter "\q". See below for other useful commands within psql.

10. Now the JDBC driver must be installed. It may be obtained from http://jdbc.postgresql.org/. Jena2 has been tested with the JDBC2 (file name pg73jdbc2.jar). Install this on your machine. Make sure the jar file is accessible on the classpath.

11. If you use the run the Jena unit/regression tests, you need to create a PostgreSQL user account named test. To do this, start psql and enter the command: create user test;

Connecting Your Jena Program to PostgreSQL

Jena supports both memory models and database models. In general, a Jena program may use both types of models identically. However, there are some differences in how the models are created. Creating a memory model can be done with a single Jena call. Creating a database model, or opening a previously created one, requires several steps as as follows.

1. Load the JDBC driver. This enables the Jena program to communicate with the database instance.
2. Create a database connection. This creates a Java object for a database connection.
3. Create or open a model on the connection. This connects to the database and creates an empty model or opens a previously created model.

These steps are illustrated in the following Java code.

// Load the Driver

String className = "org.postgresql.Driver"      // path of driver class

Class.forName (className);                      // load driver

 

String DB_URL = "jdbc:postgresql://localhost/test";  // URL of database server

String DB_USER = "test";                        // database user id

String DB_PASSWD = "";                          // database password

String DB = "PostgreSQL";                            // database type

 

// Create database connection

IDBConnection conn = new DBConnection ( URL, DB_USER, DB_PASSWD, DB );

// Create a model in the database

ModelMaker maker = ModelFactory.createModelRDBMaker(conn);

ModelRDB m = maker.createModel ();

 

< your Jena code to work with the model goes here >

 

// Remove the model. NOTE: this deletes the model from the database.

// Subsequent attempts to open this model will fail.

m.remove();   // NOTE: remove deletes the model from the database

 

// Close the database connection                                                                                conn.close();                                                                                               

 

// Alternative to creating an unnamed model ...

// Create a named model

ModelRDB m = maker.createModel("myName");

 

...  later, or in another Jena application ...

 

// Open a named model.

ModelRDB m = maker.openModel("myName");

Performance and Usage Notes

Jena stores all models in a single database using whatever database (name) specified on the database connection. See the Jena Database Release Notes for details on the physical layout.

PostgreSQL Utility Commands

Some Jena users may wish to occasionally access the PostgreSQL database directly to determine if changes have taken effect. Some useful PostgreSQL commands are listed here (replace the names in italics by names for your database instance). To use them, start the PostgreSQL command line interface: psql [database]

List All Databases Available: \l

Use a Specific Database: \c databaseName;

List All Objects in the Database: \d

List Tables: \dt    (or \d[ivsS] to list indexes, views, sequences, Schema objects)

Count All Rows in a Table: select count(*) from tableName;

List All Rows in a Table: select * from tableName;

List PSQL commands: \?

Exit PSQL: \q