# Step 2: Defining the Torque Database Schema

The second file that you must edit to configure Torque is the database schema. The database schema is an XML file that represents the structure of your SQL database in Torque. This is where you define all of your tables, column names and types, as well as the keys used to index these tables.

If you already hava a database set up, you can generate a base schema with the following command in your top-level project directory:

    ant jdbc

This will by default generate a schema.xml file. By default or if you do not define defaultOutputDir it will be located in target/generated-schema. Hint: Copy the generated schema to src/main/schema directory and rename it to bookstore-schema.xml, then add appropriate attributes e.g.

- defaultIdMethod to database element
- autoIncrement attribute to columns

     <database xmlns="http://db.apache.org/torque/5.0/templates/database"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://db.apache.org/torque/5.0/templates/database
            http://db.apache.org/torque/torque-5.0/documentation/orm-reference/database-5-0.xsd"
      name="default"
      defaultIdMethod="none" defaultJavaType="object">

Your database schema file is typically located in the src/main/schema directory under the base of your project. In this directory, you will create the file bookstore-schema.xml which defines your database schema. The name of your database schema file must end with schema.xml because Torque will only generate object models for files ending with that pattern.

In this tutorial, we will use a simple database that might be used to support a bookstore application. The database will contain three tables: author, publisher, and book. The first table will contain publisher information (name). The second table will contain author information (first and last name). And the third table will contain book information (title, and ISBN). The author id and publisher id will be foreign keys in the book table. The schema representation for this database is stored in the file bookstore-schema.xml, which should be created in the src/main/schema directory and contain the following:

    <?xml version="1.0" encoding="ISO-8859-1" ?>
    <database xmlns="http://db.apache.org/torque/5.0/templates/database"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xsi:schemaLocation="http://db.apache.org/torque/5.0/templates/database
            http://db.apache.org/torque/torque-5.0/documentation/orm-reference/database-5-0-strict.xsd"
      name="bookstore"
      defaultIdMethod="native">

      <table name="publisher" description="Publisher Table">
        <column
          name="publisher_id"
          required="true"
          primaryKey="true"
          type="INTEGER"
          description="Publisher Id"/>
        <column
          name="name"
          required="true"
          type="VARCHAR"
          size="128"
          description="Publisher Name"/>
      </table>
      <table name="author" description="Author Table">
        <column
          name="author_id"
          required="true"
          primaryKey="true"
          type="INTEGER"
          description="Author Id"/>
        <column
          name="first_name"
          required="true"
          type="VARCHAR"
          size="128"
          description="First Name"/>
        <column
          name="last_name"
          required="true"
          type="VARCHAR"
          size="128"
          description="Last Name"/>
      </table>
      <table name="book" description="Book Table">
        <column
          name="book_id"
          required="true"
          primaryKey="true"
          type="INTEGER"
          description="Book Id"/>
        <column
          name="title"
          required="true"
          type="VARCHAR"
          size="255"
          description="Book Title"/>
        <column
          name="isbn"
          required="true"
          type="VARCHAR"
          size="24"
          javaName="ISBN"
          description="ISBN Number"/>
        <column
          name="publisher_id"
          required="true"
          type="INTEGER"
          description="Foreign Key Publisher"/>
        <column
          name="author_id"
          required="true"
          type="INTEGER"
          description="Foreign Key Author"/>
        <foreign-key foreignTable="publisher">
          <reference
            local="publisher_id"
            foreign="publisher_id"/>
        </foreign-key>
        <foreign-key foreignTable="author">
          <reference
            local="author_id"
            foreign="author_id"/>
        </foreign-key>
      </table>
    </database>

There are several items of importance to note. The database element's name attribute must be the same as the database's' name in the runtime property file torque.properties described later in this tutorial. Failure to do so will prevent your object model from working properly.

Another item of importance is the database element's defaultIdMethod attribute. This attribute specifies the default method that Torque will use to generate IDs for primary keys (columns with the primaryKey attribute set to true: book_id, publisher_id, and author_id) in your database tables. There are several possible values:

Property | Description
--- | ---
native | Instructs Torque to use the underlying database's mechanism to generate IDs (varies per database e.g. autoincrement for mysql and schemata for postgresql).
idbroker | Instructs Torque to use its IDBroker service to generate IDs in a database agnostic manner. An extra table is used for this, which needs to be created separately.
none | Instructs Torque to not generate IDs. Use this setting if you want to define the primary key yourself.

The defaultIdMethod selected will be used for all tables in your schema unless an individual table element contains the idMethod attribute, in which case, its value will override the defaultIdMethod. idMethod takes the same values as defaultIdMethod.

Another common mistake is to forget that XML is case-sensitive. All of the elements and attributes must be specified according to either the

- 4.0 strict schema or default strict Schema XSD or the Schema XSD
- 5.0 strict schema or default strict Schema XSD or the Schema XSD
for the database schema. In addition, you should include the XML declaration and XML schema specification in your database schema file. This helps to detect errors in the schema file easily.

For additional information on the XML elements and attributes, please refer to the Schema XSD or 5.0 Schema XSD.

# Where to next

That completes the configuration of the Torque generator. You are now ready to start building your object model and creating your database.

Maven users should look at Invoking the Torque Maven plugin, wheras ant users should go to Invoking the ant build containing Torque.

# User Comments

User comments for this step