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.
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
We can then query that database interactively with
psql blogTo create our tables using the automatically generated DODS script, we use the \i command from within psql:
\i /home/reuven/enhydraApps/blog/src/il/co/lerner/ data/create_tables.sqlYou should see several CREATE messages, and then the psql prompt once again. Using the \d command, we see that DODS actually didn't create a table named BlogEntries. Rather, it created two tables, one named objectid and the other (primary) table named newdbtable, which is the default. The objectid table comes in place of a PostgreSQL sequence, demonstrating one limitation of this sort of generic tool, and has a column “next” that indicates which ID comes next. We thus insert information into the newdbtable table, adding a new row to objectid each time we do so.
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.
Practical books for the most technical people on the planet. Newly available books include:
- Agile Product Development by Ted Schmidt
- Improve Business Processes with an Enterprise Job Scheduler by Mike Diehl
- Finding Your Way: Mapping Your Network to Improve Manageability by Bill Childers
- DIY Commerce Site by Reven Lerner
Plus many more.