Things n' Stuff Thoughts about the universe in general

For some reason which is completely beyond me, Apache java projects – at least the ones I encounter lately and that are a less known outside the Java community – tend to be huge, complicated, and require lots of dependancies (some even circular inter-dependancies).

All this makes using them out-of-the-box as I expect any development library to work, almost impossible. In order to use something simple such as a templating system you need to have a full set of Apache java projects installed, including a project management suite, an HTTP client, a WebDAV client (which for some wierd reason is not the same thing), database persistancy layer, and more and more.

At this point I’m trying to use the Apache Torque (db-torque in jpackage lingo) which is a Java database abstraction layer, as insalled from JPackage 1.6. Apparently, using it is so “simple” – you just need to edit 3 (!!) files :-/.. and then do this and this and this and this.. and none of it is properly documented where you would expect to find such documentation – in the jpackage ..-manual package or the web site for the project. The manual package at least comes with a basic tutorial but its (a) extremly skimpy on actual details, (b) refers to files that cannot be found on the jpackage archive, and (c) apparently was removed from the actual web site for some reason.

So this is how I’ve done it. My target is an Oracle 9i schema which is already setup (IIRC one of Torque’s goals is for you to define your schema using their “database agnostic” schema definition and then Torque will build it for you in the database. For various reasons this is not the path I’m taking).

All Torque files will be stored in a schema directory under my project tree, unless specifically written otherwise.

1. Make a project.properties file for your project. Apparently this is not exactly the same as the build.properties file that is bundled with db-torque-gen master conf, so here is mine as an example:
   
   # The name of the project Torque will generate code for.
   torque.project = project

   # The target database platform.
   torque.database = oracle

   # The target package to put the generated classes in.
   torque.targetPackage = my.project.package

   # The JDBC URL that Torque can use to create and
   # drop databases if instructed to do so.
   #torque.database.createUrl = jdbc:oracle:thin:@localhost:1521:ORACLE_SID

   # The JDBC URL that will be used to create tables in your database.
   #torque.database.buildUrl = jdbc:oracle:thin:@localhost:1521:ORACLE_SID

   # The JDBC URL that will be used to access your database.
   torque.database.url = jdbc:oracle:thin:@localhost:1521:ORACLE_SID

   # The JDBC database driver to use when connecting to your database.
   torque.database.driver = oracle.jdbc.driver.OracleDriver

   # The administrative username that has sufficient privileges to create
   # and drop databases and tables that Torque executes at generation time.
   torque.database.user = user

   # The administrative password for the supplied username.
   torque.database.password = password

   # The hostname or IP address of your database server.
   torque.database.host = 127.0.0.1

   # The location of the your *-schema.xml files (see below).
torque.schema.dir = ./


   As you can see, the URLs that allow torque to mess up my DB are currently disabled until I figure out what to do with em.

2. Next you need your schema in Torque’s “database agnostic” XML format, in a file named project.schema (and like the previous file it mustn’t be named any other name unless you enjoy messing with convulated XML build scriptss). as can be seen above, I put it in the same schema directory under my project tree. You can write this file by hand (see in Torque’s documentation how it is built, and in the tutorial for simple example). As I already have a schema in the databse, created from a script, and I didn’t feel like coding it by hand – and doing all the mistakes that cannot be easily validated in the process – I wrote a simple script to convert my Oracle SQL script to the schema – here it is for your (mis)use:
   
#!/usr/bin/perl -n
BEGIN {
  $in=0;
  sub fixType {
    my ($type) = @_;
    return "INTEGER" if ($type eq "int");
    return "VARCHAR\" size=\"$1" if ($type =~ /varchar\((\d+)\)/);
    return "TIMESTAMP" if ($type eq "timestamp");
    return "SHORT" if ($type eq "smallint");
    return "BIGDECIMAL" if ($type =~ /NUMERIC/i)
  }
};
m|^\s*create table\s+([\w_]+)| and do {
  $tbl=$1;
  next;
};
$tbl and m|^\s+([\w_]+)\s+(\S+)(\s+primary key)?\s*,$| and do {
  push @{$tables{$tbl}}, "<column name=\"$1\" required=\"true\" primaryKey=\"".
    ($3 ? "true" : "false") .
    "\" type=\"". fixType($2) . "\" description=\"$1\"/>";
  next;
};
$tbl and m|^\s*\)| and do {
  undef $tbl;
  next;
};
m|alter table ([\w_]+)| and do {
  $frtable=$1; next;
};
$frtable and m|add constraint ([\w_]+)| and do {
  $frname=$1; next;
};
$frtable and m|foreign key \(([\w_]+)\)| and do {
  $frcol=$1; next;
};
$frtable and m|references ([\w_]+)\(([\w_]+)\)| and do {
  push @{$tables{$frtable}}, "<foreign -key foreignTable=\"$1\" name=\"$frname\">";
  push @{$tables{$frtable}}, "\t<reference local=\"$frcol\" foreign=\"$2\"/>";
  push @{$tables{$frtable}}, "</foreign>";
};
END {
  print qq{<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database_3_1.dtd">
    &glt;database name="dbname" defaultIdMethod="native"
    package="my.project.package" defaultJavaNamingMethod="underscore">
  };
  for $tbl (keys %tables) {
    print "\t<table name=\"$tbl\" description=\"$tbl\">";
    for $l (@{$tables{$tbl}}) {
      print "\t\t$l";
    }
    print "\t</table>";
  }
  print qq{</database>
  };
}


   I named the file schema2xml.pl and then run it like this:

   
cat dbschema.sql | perl schema/schema2xml.pl > schema/project-schema.xml


   You then should have a schema file that should look something like this:

   
<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database_3_1.dtd">
<database name="dbname" defaultIdMethod="native"
    package="my.project.package"
    defaultJavaNamingMethod="underscore">
  <table name="GENERIC_CODES" description="GENERIC_CODES">
    <column name="GENERIC_CODE_ID" required="true"
      primaryKey="true" type="INTEGER" description="GENERIC_CODE_ID"/>
    <column name="GENERIC_CODE_TYPE_ID" required="true"
        primaryKey="false" type="INTEGER" description="GENERIC_CODE_TYPE_ID"/>
    <column name="STRING_VALUE" required="true" primaryKey="false"
      type="VARCHAR" size="255" description="STRING_VALUE"/>
    <column name="CODE_NAME" required="true" primaryKey="false"
      type="VARCHAR" size="50" description="CODE_NAME"/>
    <column name="NUMERIC_VALUE" required="true"
      primaryKey="false" type="INTEGER" description="NUMERIC_VALUE"/>
    <column name="LANGUAGE_ID" required="true" primaryKey="false"
      type="INTEGER" description="LANGUAGE_ID"/>
    <column name="CREATION_DATE" required="true"
      primaryKey="false" type="TIMESTAMP" description="CREATION_DATE"/>
    <column name="CREATOR_NAME" required="true"
      primaryKey="false" type="VARCHAR" size="50" description="CREATOR_NAME"/>
    <column name="LAST_UPDATE_DATE" required="true"
      primaryKey="false" type="TIMESTAMP" description="LAST_UPDATE_DATE"/>
    <column name="LAST_UPDATOR_NAME" required="true" primaryKey="false"
      type="VARCHAR" size="50" description="LAST_UPDATOR_NAME"/>
    <foreign -key foreignTable="GENERIC_CODE_TYPES"
      name="FK_GENERICCO_GENERICTYPES">
      <reference local="GENERIC_CODE_TYPE_ID"
        foreign="GENERIC_CODE_TYPE_ID"/>
    </foreign>
  </table>
  <table name="STORE_ITEMS" description="STORE_ITEMS">
...
    <column name="ITEM_STATUS_ID" required="true" primaryKey="false"
      type="INTEGER" description="ITEM_STATUS_ID"/>
    <foreign -key foreignTable="LICENSES"
      name="FK_STORE_ITEMS_LICENSES">
      <reference local="LICENSE_ID" foreign="LICENSE_ID"/>
    </foreign>
    <foreign -key foreignTable="PURCHASE_ITEMS"
      name="FK_STORE_ITEMS_PURCHASE_ITEMS">
      <reference local="STORE_ITEM_ID" foreign="STORE_ITEM_ID"/>
    </foreign>
  </table>
</database>


   This of course follows my database schema conventions, but I assume the script would work with most conventions, and if not then it should be easy to modify to suit other needs by anyone who knows Perl.

3. The last file we need is the build.properties file (remember I talked about 3 files at the preface?). This file is too complicated for us to write outselves, so torque provides us with a shortcut. This thankfully is bundled with JPackage’s db-torque: from /usr/share/db-torque-<your version&gt>/conf/master copy the files Torque.master, default.properties and build.xml to your schema directory (on my installation of db-torque-3.1.1, these are the only files there). I also renamed build.xml to build-properties.xml so I won’t confuse it with another ant build file.

   Now edit default.properties to reflect your setup. In my project, it looks like this:
   
application.root = src
torque.database.name = dbname
torque.database.type = oracle
torque.database.user = user
torque.database.password = password
torque.database.driver = oracle.jdbc.driver.OracleDriver
torque.database.url = jdbc:oracle:thin:@localhost:1521:ORACLE_SID
torque.database.validationQuery = SELECT 1 FROM DUAL


   If you’ve noticed duplication of data from the build.properties file, then so have I – I don’t have anything else to say about the issue except what I’ve implicitly mentioned before about the sanity of the people developing these Apache Java project.

   From the configuration file, application.root refers to where your database abstraction Java classes will be created – in my project they will be created under the directory schema/src – away from the project’s main sources directory. I use eclipse to setup multiple source folders and everything is just fine.

   Its not over yet – what about the other files we copied from Torque’s master conf directory ? Torque.master is the template for the real file we need to create – build.properties, and build.xml (or build-properties.xml as I like to call it) will help us to create the required file from the template and file with just added. Now just run:

   
ant -f build-properties.xml


   And it will create a file called Torque.properties. Just rename it to build.properties and be done with it.

   I then had to edit the generated build.properties file:

   • Add the property torque.schema.dir=. to it, otherwise the build would not file the correct directory to store the files in (by default, unless torque.schema.dir is set in the build.properties, it defaults to just add ‘schema’ to the current directory).
   • Add the property turque.output.dir=. so that all generated files will be stored in schema. Torque creates two directories there – java contains the java source code, and sql contains the SQL scripts.
   • Add the property project=projectname otherwise some files get generated with a name that contain ${project}.
   • Add the property database=oracle. For some reason its not copied correctly from default.properties so I had to add it manually.
   • Add the property torque.useClasspath=true. By this property defaults to false which means that Torque would not be able to load its default templates from its jars, even if they are setup properly on the class path.
4. Once you have the schema, the project properties file and the build properties file, you can start to generate the database abstraction classes. I used the ant built file which is obviously not bundled and I had to find it on the web as well (I got it from Torque’s CVS. This is the 3.1 branch, which is the version that JPackage 1.6 has). I had to fix the file like this:
   • Change “lib.dir” property in the file to point to /usr/share/java which is where JPackage put the jar files for everything.
   • Change the “torque.jar” property to “${torque.lib.dir}/db-torque-gen.jar” (the name given to the Torque generator jar by JPackage). I have to put the library dir property in as well, as the classpath definition below only does it automatically for other jars.
   • Remove the version numbers from all the other jar referenced in the Torque classpath entry – the JPackage way (which I fully agree with) is to create a symbolic link between the non-versioned name of the jar to the correct versioned file name, and as long as you don’t need a specific version of a jar, you can use the non-versioned one. This is very similar to how ldconfig is managing Linux shared libraries.
5. Now run
   
ant -f build-torque.xml


   At this point it should run happily and build you your files.

What to do with ’em ? Not sure yet – now I need to play with that 🙂

Isn’t it simple to use Torque with your database ? piece of cake, I say – it only took me the better part of two days figuring this out with :-/

This entry was posted on Thursday, December 29th, 2005 at 15:35 and is filed under Software. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.