Data Modeling with DODS

WelcomePresentation, however, will fill in the date and text sections with information that it retrieved from a relational database table. We will have to create the table, as well as populate it with some information, before we can continue.

This is where DODS enters the picture. The DODS program, which is in $ENHYDRA/bin/dods, is another graphical client-side program written in Java. When working with DODS, we have to remember that we're using a bridge between two different paradigms, and the terminology may often be a bit weird.

DODS begins with a package, which is a container for all of the tables and other attributes that we will create. By default, as you can see from the initial DODS window, the package is named root; I changed this to blog by clicking on the root folder and selecting Package from the Edit menu.

We will create a database table (BlogEntries) with two attributes: the date and the contents. (These match the two id tags that are in our version of Welcome.html.) We first add a new table to BlogEntries, using the Insert menu, choosing “data object” and giving it the name BlogEntries.

Next, we must add two fields (date and text) to our table. In order to do this, click on the BlogEntries name in the upper left-hand corner of the DODS window, and use the Insert menu to add each new attribute. Both of our attributes will be of type “varchar”, meaning that we'll treat them both as text. And while this will work perfectly well for our purposes, it's unfortunate that DODS doesn't support the PostgreSQL TIMESTAMP data type, a tool that allows us to perform all sorts of clever and sophisticated manipulations on dates and times. So we'll store the dates in text-only format, knowing that we can use ORDER BY to retrieve them in order—and not much more than that.

Because we want to ensure maximum speed for our web/database application, and because we'll be inserting text much less frequently than we retrieve it, we will tell DODS to make both of our fields indexable and queryable. The former will modify the SQL defintion, ensuring that an index is created for the attribute. The latter will create additional methods that allow us to retrieve information based on this column.

Finally, we choose PostgreSQL from the Database menu, telling DODS to create PostgreSQL-style SQL, rather than any other database.

Once we're done creating our table with DODS, we export it into DOML (using File Save as), an XML format that describes our tables and can be used to generate both the SQL and Java. Make sure to save the DOML file into the source directory within your project and package; thus, I put it in blog/src/il/co/lerner. Once our DOML file is complete (see Listing 2), we can turn it into Java and SQL with the build all command from the File menu. Building everything results in the creation of a number of files within the data directory, so when you're asked where you want to build things, select the data directory in which you stuck the blog.doml file. A window will pop up, describing what DODS is doing at each moment. If everything is successful, you can exit from DODS.

Listing 2. blog.doml, the Automatically Generated File that DODS Creates

Running build all from DODS turns the DOML file into a set of files under the data directory. This directory now contains not just a Makefile (previously empty), but also the blog subdirectory in which the following four Java classes now exist: BlogEntriesDO, a data-object class similar to Enhydra's presentation objects for displaying information; BlogEntriesDataStruct, which actually contains the data from our table; BlogEntriesDOI, an interface for the BlogEntriesDO class; and BlogEntriesQuery, which allows us to query fields we've described as queryable.

In addition to the Java source code that was created, we also have several files containing SQL. In particular, the data directory contains create_tables.sql, which we can use as input to create our database.

To do this, we use the CREATEDB command, which is normally executed by an authorized PostgreSQL user from the UNIX shell. (This does not necessarily mean the root user; check your PostgreSQL installation documentation to see how to create PostgreSQL superusers.)

We thus say

CREATEDB blog

We can then query that database interactively with

psql blog

\i /home/reuven/enhydraApps/blog/src/il/co/lerner/ data/create_tables.sql

Let's add several items to our table so that we'll then be able to retrieve them:

INSERT INTO newdbtable (entrydate, text, objectid, objectversion)
VALUES (NOW(), 'First blog entry', 1, 1);
INSERT INTO objectid ( next ) VALUES ( '2' );
INSERT INTO newdbtable (entrydate, text, objectid, objectversion)
VALUES (NOW(), 'Second blog entry', 2, 1);
INSERT INTO objectid ( next ) VALUES ( '3' );

Now that we have two blog entries inserted, we can exit psql and go onto modifying our Java class.