Database Schemas

From Deep Thought

1 Intro
2 Current Direction
3 Daedalus Database DTD
4 Turbine XML (DDL)
5 Apache Torque DTD
6 Links
7 Meaningless Blather

Intro

Keeping your application code updated with the latest version is fairly easy these days. Version control systems such as CSV, Subversion, and Git are everywhere and are very efficient at keeping all your code files inline. However, keeping your database changes in sync with your code is more of a challenge.

The following document is a work in progress, outlining the challenges and solutions we've come up with in keeping our distributed applications well managed in a source control system. That means the ENTIRE application, the code and data all living together in perfect harmony.

[edit]

Current Direction

Our current train of thought is to pursue a standard database definition language (DDL) that keeps our core data structures in a standard ASCII format that can be easily managed with code-centric version control systems. We'll graft on a definition for version information to be attached to tables, indexes, and other required data objects if necessary. We'll follow that up with a few real-world tools for processing our selected DDL files to create first-install and upgrade paths for production applications.

[edit]

Daedalus Database DTD

Our first revision of a database definition language based on an XML DTD format is here: Daedalus Database DTD

An example of a Daedalus compliant schema file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE database SYSTEM "http://www.daedalus.com/dtd/database.dtd">

<database name="tagmaster" version="1.0">
  <table name="tags" version="1.0">
    <column name        = "tag_id"
	    type        = "integer"
	    primaryKey  = "true"
	    required    = "true"
	/>
    <column name        = "tag"
            type        = "varchar"
            size        = "150"
	    required    = "true"
	/>
  </table>
</database>

[edit]

Turbine XML (DDL)

An example of Turbine XML is noted below.

Notice the DTD references #Apache Torque DTD.

<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">
<database name="testdb">
  <table name="author">
    <column name="author_id"
            type="INTEGER"
            primaryKey="true"
            required="true"/>
    <column name="name"
            type="VARCHAR"
            size="50"
            required="true"/>
    <column name="organisation"
            type="VARCHAR"
            size="50"
            required="false"/>
  </table>

  <table name="book">
    <column name="book_id"
            type="INTEGER"
            required="true"
            primaryKey="true"
            autoIncrement="true"/>
    <column name="isbn"
            type="VARCHAR"
            size="15"
            required="true"/>
    <column name="author_id"
            type="INTEGER"
            required="true"/>
    <column name="title"
            type="VARCHAR"
            size="255"
            defaultValue="N/A"
            required="true"/>

    <foreign-key foreignTable="author">
      <reference local="author_id" foreign="author_id"/>
    </foreign-key>  

    <index name="book_isbn">
      <index-column name="isbn"/>
    </index>
  </table>
</database>

[edit]

Apache Torque DTD

Torque is an object-relational mapper for java. In other words, Torque lets you access and manipulate data in a relational database using java objects.

HOWEVER - this does not mean the Torque DTD can only be used for Java apps. XML and the related DTD's are platform independent.

For more info visit the Apache Torque DTD official site.

<!--
 Licensed to the Apache Software Foundation (ASF) under one
 or more contributor license agreements.  See the NOTICE file
 distributed with this work for additional information
 regarding copyright ownership.  The ASF licenses this file
 to you under the Apache License, Version 2.0 (the
 "License"); you may not use this file except in compliance
 with the License.  You may obtain a copy of the License at

   http://www.apache.org/licenses/LICENSE-2.0

 Unless required by applicable law or agreed to in writing,
 software distributed under the License is distributed on an
 "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 KIND, either express or implied.  See the License for the
 specific language governing permissions and limitations
 under the License.
-->
<!--
    Torque XML database schema DTD
    $Id: database.dtd 584517 2007-10-14 09:00:14Z tfischer $
-->

<!--
For: database.defaultIdMethod and table.idMethod

Do not use autoincrement or sequence. They are deprecated in favor of
using native which will use the Connection pool to determine
which database it is talking to (yes, it knows that) and then use
whatever native database methodology for insert increments that it can.
Otherwise, you should use idbroker or none. none is good if you have a
table that is just a join table. idbroker is good if you want a
centralized repository for brokering out clumps of id's in a database
agnostic way.
-->

<!--

defaultJavaNamingMethod determines how a table or column name,
from the name attribute in the xml database file, is converted to a
Java class or method name.

     nochange   - indicates not change is performed.
     underscore - Underscores and dots are removed, first letter is
              capitalized, first letter after an underscore
              is capitalized, first letter after a dot is capitalized,
              the rest of the letters are converted to lowercase.
     underscoreOmitSchema - The section of the name before and including
              the last dot in the name is removed. For the remaining part,
              underscores are removed, first letter is capitalized,
              first letter after an underscore is capitalized,
              the rest of the letters are converted to lowercase.
     javaname   - same as underscore, but no letters are converted
              to lowercase.
-->

<!ELEMENT database (option*, external-schema*, domain*, table+)>
<!ATTLIST database
  name CDATA #REQUIRED
  defaultIdMethod (idbroker|native|none) "none"
  defaultJavaType (object|primitive) "primitive"
  package CDATA #IMPLIED
  baseClass CDATA #IMPLIED
  basePeer CDATA #IMPLIED
  defaultJavaNamingMethod (nochange|underscore|underscoreOmitSchema|javaname) "underscore"
  heavyIndexing (true|false) "false"
>

<!ELEMENT option EMPTY>
<!ATTLIST option
  key CDATA #REQUIRED
  value CDATA #REQUIRED
>

<!ELEMENT external-schema EMPTY>
<!ATTLIST external-schema
  filename CDATA #REQUIRED
>

<!ELEMENT domain EMPTY>
<!ATTLIST domain
  name CDATA #REQUIRED
  type
    (
          BIT  | TINYINT | SMALLINT    | INTEGER    | BIGINT    | FLOAT
        | REAL | NUMERIC | DECIMAL     | CHAR       | VARCHAR   | LONGVARCHAR
        | DATE | TIME    | TIMESTAMP   | BINARY     | VARBINARY | LONGVARBINARY
        | NULL | OTHER   | JAVA_OBJECT | DISTINCT   | STRUCT    | ARRAY
        | BLOB | CLOB    | REF         | BOOLEANINT | BOOLEANCHAR
        | DOUBLE
    ) "VARCHAR"
  size CDATA #IMPLIED
  scale CDATA #IMPLIED
  default CDATA #IMPLIED
  description CDATA #IMPLIED
>


<!--
     note: the interface="true", requires that useManagers=true in the
     properties file.
-->
<!ELEMENT table (option*,column+,(foreign-key|index|unique|id-method-parameter)*)>
<!ATTLIST table
  name CDATA #REQUIRED
  javaName CDATA #IMPLIED
  idMethod (idbroker|native|none|null) "null"
  skipSql (true|false) "false"
  abstract (true|false) "false"
  baseClass CDATA #IMPLIED
  basePeer CDATA #IMPLIED
  alias CDATA #IMPLIED
  interface CDATA #IMPLIED
  javaNamingMethod (nochange|underscore|underscoreOmitSchema|javaname) #IMPLIED
  heavyIndexing (true|false) #IMPLIED
  description CDATA #IMPLIED
>

<!ELEMENT id-method-parameter EMPTY>
<!ATTLIST id-method-parameter
  name CDATA "default"
  value CDATA #REQUIRED
>

<!ELEMENT column (option*, inheritance*)>
<!ATTLIST column
  name CDATA #REQUIRED
  javaName CDATA #IMPLIED
  primaryKey (true|false) "false"
  required (true|false) "false"
  protected (true|false) "false"
  domain CDATA #IMPLIED
  type
    (
          BIT  | TINYINT | SMALLINT    | INTEGER    | BIGINT    | FLOAT
        | REAL | NUMERIC | DECIMAL     | CHAR       | VARCHAR   | LONGVARCHAR
        | DATE | TIME    | TIMESTAMP   | BINARY     | VARBINARY | LONGVARBINARY
        | NULL | OTHER   | JAVA_OBJECT | DISTINCT   | STRUCT    | ARRAY
        | BLOB | CLOB    | REF         | BOOLEANINT | BOOLEANCHAR
        | DOUBLE
    ) #IMPLIED
  javaType (object|primitive) #IMPLIED
  size CDATA #IMPLIED
  scale CDATA #IMPLIED
  default CDATA #IMPLIED
  autoIncrement (true|false) #IMPLIED
  inheritance (single|false) "false"
  inputValidator CDATA #IMPLIED
  javaNamingMethod (nochange|underscore|javaname) #IMPLIED
  description CDATA #IMPLIED
>

<!ELEMENT inheritance EMPTY>
<!ATTLIST inheritance
  key CDATA #REQUIRED
  class CDATA #REQUIRED
  extends CDATA #IMPLIED
>

<!ELEMENT foreign-key (option*,reference+)>
<!ATTLIST foreign-key
  foreignTable CDATA #REQUIRED
  name CDATA #IMPLIED
  onUpdate (cascade|setnull|restrict|none) "none"
  onDelete (cascade|setnull|restrict|none) "none"
>

<!ELEMENT reference EMPTY>
<!ATTLIST reference
  local CDATA #REQUIRED
  foreign CDATA #REQUIRED
>

<!ELEMENT index (option*,index-column+)>
<!ATTLIST index
  name CDATA #IMPLIED
>

<!-- The index-column's size element is currently ignored
     and will be removed in a further version. -->
<!ELEMENT index-column EMPTY>
<!ATTLIST index-column
  name CDATA #REQUIRED
  size CDATA #IMPLIED
>

<!ELEMENT unique (option*,unique-column+)>
<!ATTLIST unique
  name CDATA #IMPLIED
>

<!ELEMENT unique-column EMPTY>
<!ATTLIST unique-column
  name CDATA #REQUIRED
>

[edit]

Meaningless Blather

I killed Superman. Super man. - Rain Man, Eminem

Retrieved from "http://www.cybersprocket.com/wiki/index.php/Database_Schemas"

Category: Database

Database Schemas

From Deep Thought

Contents

Intro

Current Direction

Daedalus Database DTD

Turbine XML (DDL)

Apache Torque DTD

Links

Meaningless Blather

Views

Personal tools

stuff

Cyber Sprocket Labs

Cyber Sprocket Tech

extra

Search

Toolbox