How to run the Apache DB Torque tutorial with Derby

["Jean T. Anderson" <jt...@bristowhill.com>]

Apache DB Torque is a persistence layer that does object mapping between 
java objects and a relational database.

Torque works with Derby, thought it isn't visible on the Torque web site 
(yet).

Torque 3.2-rc2 has a good tutorial based on mysql:

http://db.apache.org/torque/releases/torque-3.2-rc2/runtime/tutorial/index.html

Below are the changes required for each step to run the tutorial on 
Derby 10.1 using the embedded driver. Switching it to the network server 
and the Derby Network Client should be easy, just a matter of changing 
the driver and the connection URLs where needed, but I haven't tested 
that yet.

======
Step 1
======
No changes.

======
Step 2
======

Here is the complete project.properties file:

    # The name of the project Torque will generate code for.
    torque.project = bookstore

    # The target database platform.
    torque.database = derby

    # The target package to put the generated classes in.
    torque.targetPackage = com.kazmier.om

    # The JDBC URL that Torque can use to create and
    # drop databases if instructed to do so.
    # Derby note: Manually create/drop databases
    torque.database.createUrl =

    # The JDBC URL that will be used to create tables in your database.
    torque.database.buildUrl = jdbc:derby:bookstore

    # The JDBC URL that will be used to access your database.
    torque.database.url = jdbc:derby:bookstore

    # The JDBC database driver to use when connecting to your database.
    torque.database.driver = org.apache.derby.jdbc.EmbeddedDriver

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

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

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

======
Step 3
======

-----------------------------------------
Adding the driver to the maven repository
-----------------------------------------

I added the Derby 10.1 jar to my local maven repo like this:

   mkdir ~/.maven/repository/derby
   mkdir ~/.maven/repository/derby/jars
   cp /opt/Apache/db-derby-10.1.1.0-bin/lib/derby.jar 
~/.maven/repository/derby/jars/derby-10.1.1.0-bin.jar

I'm sure there are other ways to do this that don't require renaming the 
jar to include the version.

--------------------------------
Specifying the driver dependency
--------------------------------

Here is the complete project.xml file:

    <project>
      <pomVersion>3</pomVersion>
      <groupId>torque</groupId>
      <id>torque-tutorial</id>
      <name>Torque</name>
      <currentVersion>3.2-rc2</currentVersion>

      <dependencies>

        <dependency>
          <artifactId>derby</artifactId>
          <groupId>derby</groupId>
          <version>10.1.1.0-bin</version>
        </dependency>
      </dependencies>
    </project>

----------------------
Creating the database
----------------------

The instructions mention that 'maven torque:create-db' won't work for
all databases, and I didn't find a way to make it work for Derby.
(I also didn't try very hard, so it might be possible.)

At any rate, I manually created the database in the root directory of
my torque project like this:

    java org.apache.derby.tools.ij
    ij version 10.1
    ij> connect 'jdbc:derby:bookstore;create=true';
    ij> quit;

--------------------
Creating the tables
--------------------

There's an issue with the project-schema.sql script that torque generates;
it creates a BOOK table that references two tables that don't exist yet
(publisher and author).  You could edit the file to swap the CREATE 
statements
around, or run the table creates twice. I'll mention this issue to the
torque developers.

Here are two strategies that both work, one using maven and the other 
using ij.

Create the tables with maven:

    maven torque:id-table-init-sql
    maven torque:insert-sql
    maven torque:insert-sql

Create the tables manually:

    java org.apache.derby.tools.ij
    ij version 10.1
    ij> connect 'jdbc:derby:bookstore';
    ij> run 'target/sql/id-table-schema.sql';
    ij> run 'target/sql/project-schema-idtable-init.sql';
    ij> run 'target/sql/project-schema.sql';
    ij> run 'target/sql/project-schema.sql';
    ij> quit;

=========
Step 4
=========

------------------------
Setting up the classpath
------------------------
The version for the village dependency defined in this step 
(2.0-dev-20030825)
does not match Step 6; so set it as shown below:

     <dependency>
       <artifactId>village</artifactId>
       <groupId>village</groupId>
       <version>2.0</version>
     </dependency>

--------------------------
Torque Run-Time Properties
--------------------------

Here is the complete src/conf/torque.properties file:

    torque.database.default = bookstore
    torque.database.bookstore.adapter = derby

    #Using commons-dbcp
    torque.dsfactory.bookstore.factory = 
org.apache.torque.dsfactory.SharedPoolDataSourceFactory
    torque.dsfactory.bookstore.connection.driver = 
org.apache.derby.jdbc.EmbeddedDriver
    torque.dsfactory.bookstore.connection.url = jdbc:derby:bookstore
    torque.dsfactory.bookstore.connection.user = app
    torque.dsfactory.bookstore.connection.password = app


=======
Step 5
=======
No changes.

========
Step 6
========

------------------------------
Running the sample application
------------------------------

I made two modifications:

  - Explicitly specify the derby system home
        -Dderby.system.home=/home/jta/Apache/TorqueTutorial

  - Replace the mysql jar with the derby jar
        lib/derby-10.1.1.0-bin.jar

Here's the command I ran -- and that succeeded:

[jta@gertie target]$ java 
-Dderby.system.home=/home/jta/Apache/TorqueTutorial -cp 
classes:lib/avalon-framework-4.1.4.jar:lib/commons-beanutils-1.7.0.jar:lib/commons-collections-3.1.jar:lib/commons-configuration-1.1.jar:lib/commons-dbcp-1.2.1.jar:lib/commons-lang-2.1.jar:lib/commons-logging-1.0.4.jar:lib/commons-pool-1.2.jar:lib/jcs-20030822.182132.jar:lib/derby-10.1.1.0-bin.jar:lib/torque-3.2-rc2.jar:lib/village-2.0.jar 
com.kazmier.Bookstore

Re: How to run the Apache DB Torque tutorial with Derby

["Jean T. Anderson" <jt...@bristowhill.com>]

Mag Gam wrote:
> nice tutorial!

I agree -- it's very nicely laid out and easy to follow (and actually 
turns out to be a terrific maven tutorial at the same time). I 
especially like how they add a link for each step to a wiki page for 
feedback.

context that dropped out of this thread;
 
http://db.apache.org/torque/releases/torque-3.2-rc2/runtime/tutorial/index.html

  -jean

> 
> On 10/5/05, Jean T. Anderson <jt...@apache.org> wrote:
> 
>>Jean T. Anderson wrote:
>>
>>>...
>>>======
>>>Step 3
>>>======
>>>...
>>>--------------------
>>>Creating the tables
>>>--------------------
>>>
>>>There's an issue with the project-schema.sql script that torque generates;
>>>it creates a BOOK table that references two tables that don't exist yet
>>>(publisher and author).  You could edit the file to swap the CREATE
>>>statements
>>>around, or run the table creates twice. I'll mention this issue to the
>>>torque developers.
>>>
>>
>>Actually there's a natural solution for this that doesn't require any
>>hacks. Step 2 specifies the schema in the src/schema/project-schema.xml
>>file. It looks like Torque generates DDL for those objects in order. I
>>moved the BOOK definition to the end of project-schema.xml and, voila,
>>the SQL generated works well in derby without hacks.
>>
>>  -jean
>>
>>
>>

Re: How to run the Apache DB Torque tutorial with Derby

[Mag Gam <ma...@gmail.com>]

nice tutorial!

On 10/5/05, Jean T. Anderson <jt...@apache.org> wrote:
> Jean T. Anderson wrote:
> > ...
> > ======
> > Step 3
> > ======
> > ...
> > --------------------
> > Creating the tables
> > --------------------
> >
> > There's an issue with the project-schema.sql script that torque generates;
> > it creates a BOOK table that references two tables that don't exist yet
> > (publisher and author).  You could edit the file to swap the CREATE
> > statements
> > around, or run the table creates twice. I'll mention this issue to the
> > torque developers.
> >
>
> Actually there's a natural solution for this that doesn't require any
> hacks. Step 2 specifies the schema in the src/schema/project-schema.xml
> file. It looks like Torque generates DDL for those objects in order. I
> moved the BOOK definition to the end of project-schema.xml and, voila,
> the SQL generated works well in derby without hacks.
>
>   -jean
>
>
>

Re: How to run the Apache DB Torque tutorial with Derby

["Jean T. Anderson" <jt...@apache.org>]

Jean T. Anderson wrote:
> ...
> ======
> Step 3
> ======
> ...  
> --------------------
> Creating the tables
> --------------------
> 
> There's an issue with the project-schema.sql script that torque generates;
> it creates a BOOK table that references two tables that don't exist yet
> (publisher and author).  You could edit the file to swap the CREATE 
> statements
> around, or run the table creates twice. I'll mention this issue to the
> torque developers.
> 

Actually there's a natural solution for this that doesn't require any 
hacks. Step 2 specifies the schema in the src/schema/project-schema.xml 
file. It looks like Torque generates DDL for those objects in order. I 
moved the BOOK definition to the end of project-schema.xml and, voila, 
the SQL generated works well in derby without hacks.

  -jean

Re: How to run the Apache DB Torque tutorial with Derby

["Jean T. Anderson" <jt...@bristowhill.com>]

I cleaned up the instructions a little and put them here:

http://db.apache.org/derby/integrate/db_torque.html

Also, note that Torque currently only works with the derby embedded 
driver, not with the derby network client. For details see 
http://issues.apache.org/jira/browse/DERBY-142 .

  -jean