Directions for Creating a Simple Database Enabled Enhydra Application Using PostgreSQL

Original author of this tutorial, based on MS Access: G.W. Estep II

This PostgreSQL-based tutorial adapted from Estep's by: Jim Caley
Email: caley@chesco.com
 
 

Overview

I found G.W. Estep's tutorial, based on MS Access, to be most helpful in getting a feel for developing a database enabled Enhydra application. However, since I desired to use PostgreSQL as the database engine, I ran into a number of configuration issues along the way which were different.

My simple requirements, with the exception of the database type, were the same as Estep's:

For more background information, please see the Overview in Estep's tutorial.

My environment was as follows:

The following procedure should get you going.


Install Enhydra

There are directions elsewhere for doing a normal install of the Enhydra binaries, and if you are using Enhydra 3.0 you can follow those and skip the rest of this section.  However, for Enhydra 2.3 I was only able to get this tutorial to work by applying the following patch to the source code and building and installing from there.  Be sure to run configure as explained in the Enhydra documentation.

For more information on this patch take a look at the mailing list message from Ludovic Lange.

========== PATCH FOR DATABASE MANAGER / DODS  ===========
Index:
modules/DatabaseManager/src/com/lutris/appserver/server/sql/CoreDO.java
===================================================================
RCS file:
/cvs/Enhydra/modules/DatabaseManager/src/com/lutris/ appserver/server/sql/CoreDO.java,v


retrieving revision 1.13
diff -r1.13 CoreDO.java
125,126c125,126
<     public static void setOIdColumnName(String oidColumnName) {
<       oidColumnName = oidColumnName;
---
>     public static void setOIdColumnName(String _oidColumnName) {
>       oidColumnName = _oidColumnName;
141,142c141,142
<     public static void setVersionColumnName(String versionColumnName) {
<       versionColumnName = versionColumnName;
---
>     public static void setVersionColumnName(String _versionColumnName) {
>       versionColumnName = _versionColumnName;

Index:
modules/DODS/src/com/lutris/dods/builder/ generator/query/templates/writeFileHeader.template
===================================================================
RCS file:
 /cvs/Enhydra/modules/DODS/src/ com/lutris/dods/builder/generator/query/templates/writeFileHeader.template,v

retrieving revision 1.3
diff -r1.3 writeFileHeader.template
125a126
 >      builder.setStringMatchDetails( "<STRING_MATCH>","<STRING_WILDCARD>" );


Add the PostgreSQL JDBC driver to your Enhydra installation

The PostgreSQL JDBC driver must be loaded by the system classloader, so it must be in your CLASSPATH. You can add the PostgreSQL driver to the CLASSPATH in the start script for your application (see the section "Build, run and test the application again" below for more information).  If you choose this method you can skip the rest of this section and move on to "Setup the project directory and test".  Alternatively, you can permanently add it by editing the multiserver file for your Enhydra installation as follows, depending on which version of Enhydra you are using:

For Enhydra 2.3: Edit /usr/local/enhyra2.3/bin/multiserver , adding the following line just before the "Build up classpath." section (use the correct path for the location of your PostgreSQL JDBC driver):

    DRIVER_CLASSES=/usr/lib/pgsql/jdbc6.5-1.2.jar
and making the "Build up classpath." section look like this:
    #
    # Build up classpath.
    #
    if [ "X{CLASSPATH}" = "X" ] ; then
        CLASSPATH=${ENHYDRA_CLASSES}${PS}${DRIVER_CLASSES}
    else
        CLASSPATH=${ENHYDRA_CLASSES}${PS}${CLASSPATH}${PS}${DRIVER_CLASSES}
    fi
For Enhydra 3.0: Edit /usr/local/enhyra3.0/bin/multiserver , adding the following line just before the "Build up classpath." section (use the correct path for the location of your PostgreSQL JDBC driver):
    DRIVER_CLASSES=/usr/lib/pgsql/jdbc6.5-1.2.jar
and making the "Build up classpath." section look like this:
    #
    # Build up classpath.
    #

    if [ "X${CLASSPATH}" = "X" ] ; then
        NEWCP=${ENHYDRA_CLASSES}${PS}${DRIVER_CLASSES}
    else
        NEWCP=${ENHYDRA_CLASSES}${PS}${CLASSPATH}${PS}${DRIVER_CLASSES}
    fi

Setup the project directory and test

cd to the directory under which you want your root project directory to be created.  We will assume here that it is /usr/prj and use this throughout the rest of this example text.  Obviously if you choose another directory you would  replace all occurrences of /usr/prj/... below with your choice.
cd /usr/prj
  1. Run the Enhydra newapp command to create the project tree.
    newapp simpledb
  2. Build your new Enhydra application
    cd /usr/prj/simpledb
    make
  3. Run your new Enhydra application
    cd /usr/prj/simpledb/output
    
    ./start
  4. Test your new Enhydra application

    Load the following URL in your browser.  Be patient, it takes a while (a minute or more) to load the page the first time.

    http://localhost:9000/Welcome.po
  5. Kill the Enhydra server
    Ctl-C

Modify the default HTML presentation file to include a simple reference to the database

  1. Modify the HTML file
    cd /usr/prj/simpledb/simpledb/presentation
    Edit Welcome.html
    Insert the following line right before the </BODY> tag: <P><SPAN ID="name">This is the text that will be replaced when this application is completely working.</SPAN>Save the file

  2. Build your new Enhydra application again
    cd /usr/prj/simpledb    
    
    make
  3. Run your new Enhydra application again
    cd /usr/prj/simpledb/output
    
    ./start

  4. Test your new Enhydra application again

    Load the following URL in your browser.  Be patient, it takes a while (a minute or more) to load the page the first time.

    http://localhost:9000/Welcome.po
    You should see the line you typed in the HTML file at the bottom of the page.
    At this point you haven't referenced the database yet.

  5. Kill the Enhydra server
    Ctl-C

Run DODS to create the data objects for the project

  1. Run DODS:
    dods <enter>
  2. Setup the packages correctly:

    1. Select the <root> package node in the lower left window
      
      Edit->Package
      
      Change name to "simpledb" no quotes
      
      <OK>
    2. Select the <simpledb> package node in the lower left window
      
      Insert->Package
      
      Change Name field to "data" no quotes
      
      <OK>
    3. Select the <data> package node in the lower left window
      
      Insert->Data Object
      
      Change Name field to "Person" no quotes
      
      Select Database Tab
      
      Change db Table Name field to "person" no quotes
      
      <OK>
    4. Select <Person> Data Object Node in lower left window
      
      Insert->Attribute
      
      Change name field to "name" no quotes
      
      Click the Database tab and check "Can be queried"
      
      <OK>
    5. Database->PostgreSQL
    6. File->Save As
      
      Browse to /usr/prj/simpledb 
      change the File name field to simpledb.doml
      
      NOTE: You must type the extension
      
      <Save>
    7. File->Build All
      
      Browse to /usr/prj/simpledb/simpledb 
      
      change the File name field to "data" no quotes
      
      <Re/Create Directory>
    8. You'll see a window pop up that says "Directory contents will be destroyed. Are you sure?"
    9. <Yes>
      
      Next you'll see a window that shows the build progress of the DODS output.
      
      <Close> when the last message is "DODS BUILD COMPLETE"
  3. Quit DODS

Create a PostgreSQL database that will work with Enhydra

  1. Run:
        createdb simpledb
        psql simpledb
  2. In psql create tables using SQL file which DODS created for you:
        simpledb= > \i /usr/prj/simpledb/simpledb/data/create_tables.sql
  3. Verify the the tables were created:
        simpledb= > select * from person;
    should show:
        name|objectid|objectversion
        ----+--------+-------------
        (0 rows)
    and:
        simpledb= > select * from objectid;
    should show:
        next
        ----
        (0 rows)
  4. Add records to the person and objectid tables:
        simpledb=> INSERT INTO person (name, objectid, objectversion)
        simpledb-> VALUES ('simpledb works!', 1, 1);
        simpledb=> INSERT INTO objectid ( next ) VALUES ( '2' );
  5. Quit psql:
        \q

Modify the Welcome.java file to get some data out of the database and replace a field in the HMTL file with it.

  1. Edit the /usr/prj/simpledb/simpledb/presentation/Welcome.java file.

  2. Add code to access the DODS generated Data Object package by inserting the following code in red into the file Welcome.java . This code is written against the Welcome.java in 3.0, but can be modified easily for other versions.
    package simpledb.presentation;
    
    import java.util.Date;
    import com.lutris.xml.xmlc.*;
    import com.lutris.appserver.server.httpPresentation.*;
    
    import simpledb.data.*;
    import com.lutris.dods.builder.generator.query.*;
    
    public class Welcome implements HttpPresentation {
    
        public void run(HttpPresentationComms comms) 
            throws HttpPresentationException,
            NonUniqueQueryException, DataObjectException  {
    
            String now = new Date().toString();
            WelcomeHTML welcome = 
                (WelcomeHTML)comms.xmlcFactory.create(WelcomeHTML.class);
            welcome.setTextTime(now);
    
            PersonDO person;
            PersonQuery pquery = new PersonQuery();
            person = pquery.getNextDO();
            welcome.setTextName( person.getName() );
                                       
            comms.response.writeHTML(welcome);
        }
    }
  3. Save the file


Add the Database stuff to the simpledb.conf file

Append the following text between the BEGIN_APPEND and END_APPEND lines to the end of the /usr/prj/simpledb/simpledb/simpledb.conf file.  Skim through this new addition, making sure there is nothing that needs to be modified for your installation.  Pay particular attention to the JDBC driver (this is different between PostgreSQL 6.x and 7.x -- see inline comment), the user, and password.
BEGIN_APPEND
#-----------------------------------------------------------------------------
#                   Database Manager Configuration
#-----------------------------------------------------------------------------

#
# The databases that are used by CSAM.  Each of these databases
# has configuration parameters set under DatabaseManager.DB."databaseName".
#
DatabaseManager.Databases[] = "simpledb"
# 
# The default database used in this application.
#

DatabaseManager.DefaultDatabase = "simpledb"
# 
# Turn on/off debugging for transactions or queries. Valid values
# are "true" or "false".
#

DatabaseManager.Debug = "false"
#
# The type of database. Normally this is "Standard".
#

DatabaseManager.DB.simpledb.ClassType = "PostgreSQL"
#
# The jdbc driver to use.
# Note that this changes to org.postgresql.Driver with PGSQL 7.0
#

DatabaseManager.DB.simpledb.JdbcDriver = "postgresql.Driver"
#
# Database url.
#

DatabaseManager.DB.simpledb.Connection.Url = "jdbc:postgresql:simpledb"
#
# Database user name.  All connections are allocated by this user.
#
DatabaseManager.DB.simpledb.Connection.User = ""
#
# Database user password.
#

DatabaseManager.DB.simpledb.Connection.Password = ""
#
# The maximum number of connections that a connection
# pool will hold.  If set to zero, then connections
# are allocated indefinitely or until the database
# refuses to allocate any new connections.
#

DatabaseManager.DB.simpledb.Connection.MaxPoolSize = 30

#
# Maximum amount of time that a thread will wait for
# a connection from the connection pool before an
# exception is thrown.  This will prevent possible dead
# locks.  The time out is in milliseconds.  If the
# time out is <= zero, the allocation of connections
# will wait indefinitely.
#

DatabaseManager.DB.simpledb.Connection.AllocationTimeout = 10000
#
# Used to log database (SQL) activity.
#

DatabaseManager.DB.simpledb.Connection.Logging = false

#
# The number of object identifiers that are allocated
# as a group and held in memory.  These identifiers
# are assigned to new data objects that are inserted
# into the database.  
#

DatabaseManager.DB.simpledb.ObjectId.CacheSize = 20
DatabaseManager.DB.simpledb.ObjectId.MinValue = 1
#
# Following two lines must be added for PostgreSQL,
# since oID can't be used for column name
#

DatabaseManager.ObjectIdColumnName = "ObjectId"
DatabaseManager.VersionColumnName = "ObjectVersion"
END_APPEND

Build, run and test the application again

  1. Edit the start file
    Note:  You can skip this step if you already added the JDBC driver in your Enhydra installation's multiserver file

    Add your PostgreSQL JDBC driver into the CLASSPATH of your /usr/prj/simpledb/simpledb/start file by adding the following two lines before the multiserver line:

    CLASSPATH=/usr/lib/pgsql/jdbc6.5-1.2.jar
    export CLASSPATH
    Be sure to use the correct path to your driver.

  2. Build the application
    cd /usr/prj/simpledb
    
    make

  3. Run the application
    cd output
    
    ./start

  4. Test the application
    http://localhost:9000/Welcome.po
    You should see the original text that mentioned it would be replaced when
    this application works with a message that says it works!

Troubleshooting

Here are a couple of tips which I used to assist me in troubleshooting when things didn't go quite right with the tutorial (e.g. failures due to incorrectly setting the PostgreSQL user and password in simpledb.conf):

If you did all of the above right, you now have a simple Enhydra application on your hands that works with a database and DODS output which is integrated in a newapp generated application.  Congrats!!!

G.W. & J.C.
Last revision: June 19, 2000