SITE
  Documentation
  Download
  License
  Performance
  Mailing List

FACADE
  Documentation
  Download
  License
  Mailing List

STORE
  Overview
  Tools
  Download
  License
  Mailing List

JUNIT EXTENSIONS
  Documentation
  Download
  License

AMANDA CD-RW
TAPER

  Overview
  Installation & Usage
  Internals

Tools contained in STORE

  • DBPeaGenerator - Create Pea classes from JDBC ResultSetMetaData
  • ParseDia - Turn a graphical representation of a database into a series of CREATE TABLE and other statements

DBPeaGenerator

Like many other tools, DBPeaGenerator was written to handle a tedious task automatically. The particular tedious task handled by DBPeaGenerator involves creating Pea classes representing database tables, which basically consist of

  • private member variables holding column values
  • getter and setter methods for these variables
  • finder, creator and updater methods creating and issuing SQL statements to the database
  • reading values from a JDBC ResultSet into member variables

All of these classes follow the same pattern, and all the information needed for creating a pea class representing a table is already present in the database. The information required primarily consists of column names and data types, both of which can be retrieved from a JDBC ResultSet via ResultSetMetaData.

This is just what DBPeaGenerator does: issue an SQL query, get meta data from the ResultSet and turn it into a java class. The creation of the java class is based on templates and it's done using our SiTE template engine.

Usage

First, look at the Overview document to get an idea of the class layout you're going to use. When you know what you want to do you can run DBPeaGenerator like this:

  1. Make sure you have a java executable in your path.
  2. Include store.jar and site.jar in you CLASSPATH. You can download SiTE here.
  3. Enter
    java de.tivano.store.util.DBPeaGenerator options JDBC-URL SQL-SELECT-statement classname
    (all on one line).

Remember to enclose the URL and especially the SQL-statement in quotes so the arguments aren't mangled by your command line interpreter!

options may be some of the following:

-x name
The (fully qualified) name of a class that the generated pea class will extend
-xpk name
The (fully qualified) name of a class that the generated primary key class will extend (provided the template has support for it)
-pkt filename
The name of a file that contains the template for the primary key class, relative to the classpath in which it must be contained. The templates that come with STORE are in the subdirectory "templates" (in the source distribution they are in "lib/templates").
-impl name
The (fully qualified) name of the class to be instanciated in finder and creator methods

JDBC-URL must be the URL of the database where the SQL-statement will be executed. The required driver classes must be in your CLASSPATH and they must be loaded e. g. by setting the system property "jdbc.drivers" to the name of the driver class. Consult the manual of your Java interpreter how to set system properties.

SQL-SELECT-Statement is an SQL statement that will be executed in the given database. The ResultSet returned by the query need not return any rows, but it must contain all the columns that are to be represented by the generated pea class. The statement should follow the pattern
SELECT columns FROM table WHERE where-clause.
The columns part will be used for beautifying the column names (with regard to upper/lowercase), because the database will usually provide all-uppercase column names.
All columns found in the where-clause are assumed to be part of the primary key.

Finally, classname is the fully qualified name of the class that will be generated. The name of the primary key class will be derived from classname by appending the uppercase letters "PK" to it. The generated .java files will be written to the current directory (or to the appropriate subdirectory if classname contains a package name). Any files that are already in that position will be overwritten without warning! Tivano disclaims all responsibility should you accidentally overwrite important files. You have been warned.

Templates

STORE comes with a number of default templates for DBPeaGenerator. These are

PeaGeneratorTemplate
This is the most general template for Pea classes - it is used by AbstractPeaGenerator to create a Pea class using a given list of Pea members and PrimaryKey members. It contains a tag named "abstractMethods" that contains some abstract methods for creating, updating and deleting data in a DataStore.
DBPeaGeneratorTemplate
This template is not for a complete Pea class, is it meant for replacing the "abstractMethods" tag in PeaGeneratorTemplate. It contains implementations of the create, update and delete methods that operate on a database, as well as some simple finder methods for looking up Peas in the database.
PeaGeneratorPKTemplate
This is a template for a simple PrimaryKey class. It is the default PrimaryKey template used by DBPeaGenerator.
PeaGeneratorMySQLSequencePKTemplate
This is an extension of the PeaGeneratorPKTemplate that has an additional constructor with no arguments. It is used for auto-generating a primary key by fetching a value from a special MySQL table (see the description of "ParseDia" below for details on these "sequences").
PeaGeneratorOracleSequencePKTemplate
This is an extension of the PeaGeneratorPKTemplate that has an additional constructor with no arguments. It is used for auto-generating a primary key by fetching a value from an Oracle sequence.

For more detailed information how these templates are used and populated look at the JavaDoc for DBPeaGenerator, AbstractDBPeaGenerator and AbstractPeaGenerator.

Limitations / Known Problems

Not all ANSI SQL data types are supported. Especially LOBs and user-defined types are still missing.

Some JDBC drivers return incorrect values in ResultSetMetaData. E. g. the mm.mysql (version 2.0.12) driver returns a scale of 1 even for INT columns.

Database engines differ in their notion of what's a reserved word. So if you want portability, don't use reserved words of any database engine for your table and column names.

ParseDia

When you design a database you want a nice graphical tool where you can represent tables as boxes with a name and a few attributes. You want to connect the boxes with lines to visualize relations, and you want to be able to rearrange the boxes whenever the diagram becomes too obscure.

ParseDia is not such a tool. But it enables you to use an existing tool for drawing diagrams. The files produced by that tool can be parsed by ParseDia and turned into a series of SQL statements for creating the database tables. The tool we're talking about is called "dia", and it's available here. You can also create Java (Pea) classes directly using ParseDia instead of creating only the database with ParseDia and then creating the classes with DBPeaGenerator.

The nice thing is that (within certain constraints) you can develop your database representation and -interface almost completely independent of the underlying database engine! In fact, we originally created ParseDia as a Perl script for an application that was developed with MySQL and put into production on an Oracle database (that's why, at this time, only MySQL and Oracle are supported).

Please be aware that even though in theory you can develop an application in an environment that is different from the production environment, this is by no means advisable! (We have a certain experience with that kind of setup. Believe us. :-/ )

Drawing the Diagram

  1. Start dia.
  2. Create a new diagram using the menu.
  3. Select "UML" as the diagram type.
  4. Use UML classes to represent tables. Anything else should be ignored by ParseDia, so you can use Packages, Interfaces, Lines, Comments etc. to put a lot of human-readable information into the diagram.
  5. The name of a UML class will be used as the table name in the database.
  6. The attributes of each UML class represent the columns of the corresponding table. The attribute name is used as the column name, of course.
  7. The attribute type contains the column type as well as certain constraints (see below).
  8. The attribute value is optional and will be used as the default value for the column, if present.
  9. Methods of classes will be ignored and can be used for documentation purposes (e. g. for non-standard finder or creator methods).
  10. A class without any attributes will be ignored. This can also be used for documentation purposes (e. g. for describing interfaces).

To preserve portability between different database engines you must stick to the following rules. We do not guarantee that a diagram conforming to these rules will be usable with all database engines that may be supported in the future.

Supported column types:

NUMERIC(p,s)
A numeric data type with precision p and scale s (s denotes the number of decimal digits to the right of the decimal point). The allowed range of values for p and s differs between database engines, use small values for portability.
For MySQL if s <= 0 ParseDia may translate this to one of TINYINT, SMALLINT, MEDIUMINT, INT or BIGINT, depending on p. These types are handled more efficiently than the NUMERIC type.
Similarly, for PostgreSQL SMALLINT, INT or BIGINT may be used, and for SAP-DB SMALLINT or INT.
NUMERIC will be turned into FIXED for SAP-DB.
NUMERIC(p)
Synonym for NUMERIC(p,0)
CHAR(l) or VARCHAR(l)
A string of constant (for CHAR) or variable (for VARCHAR) length of up to l one-byte characters, l should not be greater than 4000 for portability reasons.
For MySQL ParseDia will use TEXT if l > 255. For SAP-DB ParseDia will use LONG if l > 4000.
TEXT
Turned into VARCHAR(4000) for Oracle and into LONG for SAP-DB.
DATE, DATETIME, TIME, TIMESTAMP
Date and / or time values
For Oracle, ParseDia will always convert this to DATE. For SAP-DB and PostgreSQL, TIMESTAMP will be used instead of DATETIME.

Note that the actual types used in the database may differ from those in your diagram (e. g. a VARCHAR(1000) column will be turned into TEXT for MySQL because VARCHAR can be at most 255 characters in MySQL). ParseDia will always use a datatype that can hold at least as many values as the type used in the diagram (and possibly more).

The following column constraints are recognized by ParseDia (others are allowed but may not be portable and will generate Warning messages from ParseDia):

PRIMARY KEY
All columns with this constraint together form the primary key for the table. If there is no PRIMARY KEY column, the first UNIQUE and NOT NULL column is used as a primary key instead. If there is no such column either, all columns together form the primary key (which implies that no columns can be modified!).
NULL or NOT NULL
The column may (may not) contain NULL values.
FOREIGN KEY t or REFERENCES t
This defines a referential constraint (i. e. for every non-NULL value in this column there must be a row in t that has that value as its primary key). Note that MySQL does not enforce referential integrity.
Because referential constraints are often used for table JOINs, ParseDia automatically creates indices on all columns with referential constraints. In some cases this may hamper performance instead of improving it. You'll have to experiment with this if performance is important to you. Also note that in some cases multi-column indices may deliver a better performance boost than the single-column indices generated by ParseDia. Consult the documentation of your database engine for details, and use the EXPLAIN statement to find out how your queries are executed.
UNIQUE
There must not be two rows in the table that have the same value in this column. Many database engines will automatically create an index on UNIQUE columns. I'm not sure if MySQL does.
UNIQUE:x
A multi-column UNIQUE constraint on all columns (in this table) that have the same UNIQUE:x constraint (use any string or number instead of x).

As a common replacement for MySQL AUTO_INCREMENT columns and Oracle SEQUENCEs (that are usually used for the same purpose) ParseDia will automatically create

  • an actual SEQUENCE in Oracle, PostgreSQL and SAP-DB
  • an additional table with an AUTO_INCREMENT column in MySQL

for every PRIMARY KEY column that is not also a FOREIGN KEY (or REFERENCE). You can use DBPeaGenerator with the templates PeaGeneratorMySQLSequencePKTemplate.txt or PeaGeneratorSequencePKTemplate.txt (the latter is for Oracle) to generate PrimaryKey classes that make use of these "sequences".

Using ParseDia

  1. Make sure you have a java executable in your path.
  2. Include store.jar in you CLASSPATH.
  3. Enter
    java de.tivano.store.util.ParseDia -m filename.dia filename.sql
    Substitute the "-m" with "-o" if you're using Oracle instead of MySQL. Replace "filename.dia" with the filename of your dia diagram and "filename.sql" with a filename where the generated SQL statements shall be written. You can also leave out the "filename.sql" argument, in that case the SQL will be written to standard output.
  4. Execute the generated SQL e. g. with
    mysql -u user -p database <filename.sql>
    for MySQL or
    sqlplus user/password@database <filename.sql>
    for Oracle.

Apart from "-m" and "-o" ParseDia supports the following options:

-p
Generate statements for a PostgreSQL database
-s
Generate statements for a SAP-DB database
-c
Generate CREATE statements only
-d
Generate DROP statements only
-jdbc URL
Connect a database and execute the generated statements
Use the system property 'jdbc.drivers' to load the required driver class. System.getProperties() will be used for creating the connection.
-gen classname
Generate java code (Pea classes) for tables. classname may contain the placeholder "%t", which will be replaced by the table name. Any existing .java file for classname or classnamePK will be overwritten without warning!
-x classname
A class that the generated Pea classes will extend. May also contain "%t". Only useful in combination with -gen.
-impl classname
This class will be instanciated by the generated Pea classes. May also contain "%t". If -impl is specified but no .java file of that name exists it will be created.
Only useful in combination with -gen.

The automatic generation of Pea classes works just like DBPeaGenerator (this means you also need site.jar in your CLASSPATH). In contrast to DBPeaGenerator the information contained in the diagram is used to construct the classes, not the information taken from the database.

Example

The file doc/userdb.dia contains a very simply example of a database that contains users and groups similar to UNIX users and groups.

Every user has a numerical ID, a login, a password, a full name and a primary group. In addition, every user can be a member of other groups ("secondary groups").
Groups simply have a numerical ID and a name.

Study the diagram with the above definitions in mind to get an idea how to use the constraints to model relationships between database tables. Or use ParseDia to see how the diagram is converted into SQL statements.

Use the following sytax to create the example tables in your MySQL database (use a JDBC URL that fits your environment, and modify the classpath to include the mm.mysql JDBC driver).

java -cp src:mm.mysql-2.0.12-bin.jar -Djdbc.drivers=org.gjt.mm.mysql.Driver de.tivano.store.util.ParseDia -m -c -jdbc 'jdbc:mysql://server/mysql?user=dbuser&password=dbpassword' doc/userdb.dia userdb.sql

For Oracle it would look like this:

java -cp src:classes12.zip -Djdbc.drivers=oracle.jdbc.driver.OracleDriver -Duser=dbuser -Dpassword=dbpassword de.tivano.store.util.ParseDia -o -c -jdbc 'jdbc:oracle:thin:@server:1521:TIVANO' doc/userdb.dia userdb.sql

Limitations / Known Problems

ParseDia is meant to be used with dia 0.88.1 and MySQL 3.23.6 (or higher) using MyISAM tables.

LOB types are currently not supported.

ParseDia has been tested mostly with Oracle and a little with MySQL. Testing with PostgreSQL and SAP-DB has been very rudimentary only.