Tools contained in STORE
Author: Peter Conrad, conrad@tivano.de
$Id: tools.html,v 1.2 2002/10/14 09:26:17 apel Exp $
- 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:
- Make sure you have a java executable in your path.
- Include store.jar and site.jar in you CLASSPATH. You can download SiTE
here.
- 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
- Start dia.
- Create a new diagram using the menu.
- Select "UML" as the diagram type.
- 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.
- The name of a UML class will be used as the table name in the
database.
- The attributes of each UML class represent the columns of the
corresponding
table. The attribute name is used as the column name, of course.
- The attribute type contains the column type as well as certain constraints
(see below).
- The attribute value is optional and will be used as the default value for
the column, if present.
- Methods of classes will be ignored and can be used for documentation
purposes (e. g. for non-standard finder or creator methods).
- 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
- Make sure you have a java executable in your path.
- Include store.jar in you CLASSPATH.
- 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.
- 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.
| 
