Postgresql Configuration

Cary O'Brien <cobrien-//[email protected]> Fri, 25 Oct 2002 10:21:25 -0400

Newsgroups	gmane.comp.java.openjms.devel
Message-ID	<[email protected]>

Hi.

If anyone is interested, I got openjms version 0.7.2 working with 
PostgreSQL 7.1.2
(what was on my machine (Linux/i386/RedHat 7.2)).  

I ran test.sh and everything passed.  I ran some of the send/receive
test with the -persistent flag and they ran ok.  Is there a better test?

We are in an evalutation phase, so it isn't running in the real world 
anywhere.
Mostly I'm trying to figure out if JMS is worth the effort (at this 
point I think
it probably is).

Here is the config file section:

<!-- PostgreSQL -->
    <RdbmsDatabaseConfiguration
      driver="org.postgresql.Driver"
      url="jdbc:postgresql://localhost/jms"
      user="cary"
      password="jms"
      retries="5"
      timeout ="2000" />

In the url jms is the name of the database.  The driver is the
standard jdbc driver that comes with PostgreSQL.

I am attaching the sql file for creating the tables.  The only
change was to make some of the timestamp fields int8.

I'll attach the config file too in case the xml above gets eaten.

-- cary

Cary O'Brien
cobrien-//[email protected]

create_postgresql.sql (text/plain, 1.9 KB)

DROP TABLE system_data;
create table system_data (
  id                     int not null,
  version                varchar(20) not null,
  creationDate           DATE not null
);
create unique index system_data_pk on system_data(id);

DROP TABLE message_id;
create table message_id (
  id                     int not null,
  maxId                  int not null
);
create unique index message_id_pk on message_id(id);

DROP TABLE seeds;
create table seeds (
  name                   varchar(20) not null,
  seed                   int not null
);
create unique index seeds_pk on seeds(name);

DROP TABLE destinations;
create table destinations (
  name                   varchar(255) not null,
  isQueue                char,
  destinationId          int not null
);
create unique index destinations_pk on destinations(destinationId);

DROP TABLE messages;
create table messages (
  messageId             int8,
  destinationId         int8 not null,
  messageType           varchar(20) not null,
  priority              int,
  createTime            int8,
  expiryTime            int8,
  processed             int,
  messageBlob           varchar not null
);
create index messages_pk on messages(messageId);

DROP TABLE message_handles;
CREATE TABLE message_handles (
   messageId            int8,
   destinationId        int8 NOT NULL,
   consumerId           int8 NOT NULL,
   priority             int,
   acceptedTime         int8,
   sequenceNumber       int8,
   expiryTime           int8,
   delivered            int
);
CREATE INDEX message_handles_pk ON message_handles(destinationId, consumerId, messageId);

DROP TABLE consumers;
create table consumers (
  name                 varchar(50) not null,
  destinationId        int8 not null,
  consumerId           int8 not NULL,
  created              int8 NOT NULL
);
create unique index consumers_pk on consumers(name, destinationId);

DROP TABLE jndi_context;

rmi_jms_postgresql.xml (text/xml, 3.6 KB)

<?xml version="1.0"?>

<!-- OpenJMS configuration:
     . RMI connectors  
     . JDBC persistency
     . embedded JNDI provider
     . embedded RMI registry, running on port 1099
     . preconfigured destinations
 -->

<Configuration>

  <!-- Required when using an RMI connector -->
  <Connectors>
    <Connector scheme="rmi">
      <ConnectionFactories>
        <QueueConnectionFactory name="JmsQueueConnectionFactory" />
        <TopicConnectionFactory name="JmsTopicConnectionFactory" />
      </ConnectionFactories>
    </Connector>
  </Connectors>

  <!-- Required -->
  <DatabaseConfiguration>

  <!-- Uncomment the appropriate RbmsDatabaseConfiguration and change the
       parameters to match your setup
   -->

  <!-- hsql Database Engine (remote version)
  <RdbmsDatabaseConfiguration
      driver="org.hsqldb.jdbcDriver" 
      url="jdbc:hsqldb:hsql://localhost"
      user="openjms"
      password="openjms" />
  -->

  <!-- hsql Database Engine (local version)
  <RdbmsDatabaseConfiguration
      driver="org.hsqldb.jdbcDriver" 
      url="jdbc:hsqldb:openjms.db"
      user="sa"
      password="" />
  -->

  <!-- MySQL
    <RdbmsDatabaseConfiguration
      driver="org.gjt.mm.mysql.Driver" 
      url="jdbc:mysql://localhost/test"
      user="openjms" 
      password="openjms" />
   -->

  <!-- Oracle
    <RdbmsDatabaseConfiguration
      driver="oracle.jdbc.driver.OracleDriver"
      url="jdbc:oracle:oci8:@mqtest" 
      user="system" 
      password="manager" />
   -->

  <!-- Sybase configuration
    <RdbmsDatabaseConfiguration
      driver="com.sybase.jdbc2.jdbc.SybDriver" 
      url="jdbc:sybase:Tds:homent:5002/test" 
      user="jim" 
      password="ab1234" />
   -->

  <!-- MS SQLServer
    <RdbmsDatabaseConfiguration
      driver="com.thinweb.tds.Driver" 
      url="jdbc:twtds:sqlserver://localhost:1433/openjms"
      user="sa" 
      password="sa" />
   -->

  <!-- Interbase
    <RdbmsDatabaseConfiguration
      driver="interbase.interclient.Driver" 
      url="jdbc:interbase://jima/c:\interbase\bin\openjms"
      user="openjms" 
      password="openjms" />
  -->

  <!-- JDataStore (remote/multiuser version)
    <RdbmsDatabaseConfiguration
      driver="com.borland.datastore.jdbc.DataStoreDriver" 
      url="jdbc:borland:dsremote://myserver/E:\temp\openjms\openjms.jds"
      user="myuser" 
      password="password" />
  --> 
  
  <!-- JDataStore (local version)
    <RdbmsDatabaseConfiguration
      driver="com.borland.datastore.jdbc.DataStoreDriver" 
      url="jdbc:borland:dslocal:E:\temp\openjms\openjms"
      user="myuser" 
      password="password" />  
  -->

  <!-- DB2
    <RdbmsDatabaseConfiguration
      driver="COM.ibm.db2.jdbc.app.DB2Driver"
      url="jdbc:db2:openjms"
      userName="username"
      password="password"
      retries="5"
      timeout ="2000" />
  -->
  
<!-- PostgreSQL -->
    <RdbmsDatabaseConfiguration
      driver="org.postgresql.Driver"
      url="jdbc:postgresql://localhost/jms"
      user="cary"
      password="jms"
      retries="5"
      timeout ="2000" />


  </DatabaseConfiguration>

  <!-- Required -->
  <AdminConfiguration
     script="${openjms.home}\bin\startjms.bat"
     config="${openjms.home}\config\rmi_jms_postgresql.xml" />

  <!-- Optional. If not specified, no destinations will be created -->
  <AdministeredDestinations>
    <AdministeredTopic name="topic1">
      <Subscriber name="sub1" />
      <Subscriber name="sub2" />
    </AdministeredTopic>
  
    <AdministeredQueue name="queue1" />
    <AdministeredQueue name="queue2" />
    <AdministeredQueue name="queue3" />
  </AdministeredDestinations>

</Configuration>