Database schema configuration

This article described how to set up a database schema in order to adjust the autogenerated SQL written during deployment.

1 Introduction

    Database schemas are used to adjust the autogenerated SQL written during deployment work with different database systems. When configuring a DataSource, an optional database schema can be given as a attribute to the Datasource configuration entry. This reference should point to a XMl file holding information about things like reserved words, null/not-null settings and case-sensitivity.

    Orion comes with default database schemas for Oracle, Sybase, HypersonicSQL and many others. These can be used freely or edited to suite the particular database type's version used.

    The default database schemas shipped with Orion can normaly be found in the directory /[orion dir]/config/database-schemas/ .

    This article will describe what the available options are in order for users to be able to edit the existing schemas or add new ones.

    More information about DataSource configuration can be found here.

2 Database schema fields description

    Below is the syntax of a Database schema configuration entry. Although a database schema configuration file can span multiple configuration entries, it is adviced to have one entry per file.


    <?xml version="1.0"?>
    <!DOCTYPE database-schema PUBLIC "-//Evermind//- Database schema"
            "http://www.orionserver.com/dtds/database-schema.dtd">
    <database-schema
        abs-function="abs({1})"
        case-sensitive="true"
        locate-function="locate({1}, {2})"
        locate-threearg-function="locate({1}, {2}, {3})"
        max-table-name-length="30"
        name="My DB name"
        not-null="not null"
        null="null"
        primary-key="primary key"
        square-root-function="sqrt({1})"
        string-length-function="length({1})"
        substring-function="substr({1}, {2})"
        substring-threearg-function="substr({1}, {2}, {3})"
        supports-boolean-type="true"
        supports-exists="true"
        supports-select-sources="false" >
            <type-mapping
                   name="smallint"
                   type="int" />
            <disallowed-field
                   name="order" />
    </database-schema>
    Listing 1: A sample daabase-schema entry with some default values.

    Notice how some attribute values are in the form of expressions, such as the value of the abs-function attribute. These expressions uses a {n} syntax to show where function parameters should be inserted. The value of n is used to show the number of function parameters to be inserted.

    Below are explanations for the attributes given in Listing 1 above..

    2.1 The database-schema tag

      The database-schema tag is the super tag of the database-schema configuration entry.

      Below are the possible attributes for this tag.

      2.1.1 abs-function

        If stated, the value of this attribute will override the default value that states that the abs-function should be expressed as "abs({1})".

      2.1.2 case-sensitive

        The case-sensitive attribute states if the value of the name attribute of the disallowed-field tag should be parsed as case-sensitive or not.

        If the value of this attribute is set to "true" and a disallowed field name of "order" is given, a field name of "Order" would be allowed.

        If the value of this attribute is set to "false" and a disallowed field name of "order" is given, a field name of "Order" would not be allowed.

      2.1.3 locate-function

        If stated, the value of this attribute will override the default value that states that the locate-function should be expressed as "locate({1}, {2})".

      2.1.4 locate-threearg-function

        If stated, the value of this attribute will override the default value that states that the locate-threearg-function should be expressed as "locate({1}, {2}, {3})".

      2.1.5 max-table-name

        The value of the optional max-table-name attribute is used to state the maximum number of letters allowed for table names. Names longer than the specified value will be truncated.

      2.1.6 name

        The value of the name attribute states the name of this database schema and can be used when displaying the schema in various tools.

      2.1.7 not-null

        The value of the not-null attribute should hold the keywords necessary for creating a table field that does not allow null values. If no such expression is needed for the database in question, this value should be left as "" (blank).

      2.1.8 null

        The value of the null attribute should hold the keywords necessary for creating a table field that allows null values. If no such expression is needed for the database in question, this value should be left as "" (blank).

      2.1.9 primary key

        The value of the primary-key attribute should hold the keywords necessary for specifying that a field is a primary key field. If no such expression is needed for the database in question, this value should be left as "" (blank).

      2.1.10 square-root-function

        If stated, the value of this attribute will override the default value that states that the square-root-function should be expressed as "sqrt({1})".

      2.1.11 string-length-function

        If stated, the value of this attribute will override the default value that states that the string-length-function should be expressed as "length({1})".

      2.1.12 substring-function

        If stated, the value of this attribute will override the default value that states that the substring-function should be expressed as "substr({1}, {2})".

      2.1.13 substring-threearg-function

        If stated, the value of this attribute will override the default value that states that the substring-threearg-function should be expressed as "substr({1}, {2}, {3})".

      2.1.14 supports-boolean-type

        The boolean value of the supports-boolean-type attribute should be set to false if the database does not support boolean types. The default value is "true".

      2.1.15 supports-exists

        The boolean value of the supports-exists attribute should be set to false if the database does not support the EXISTS SQL function. The default value is "true".

      2.1.16 supports-select-sources

        The boolean value of the supports-select-sources attribute should be set to false if the database does not support select sources. The default value is "false".

    2.2 The type-mapping tag

      The type-mapping tag is a subtag of the database-schema tag. It is used to map java primitives/classes to the database's field types.

      One database-schema tag normaly holds a number of type-mapping tags.

      Below are explanations of the attributes.

      2.2.1 name

        The value of the name attribute should hold the database type for this type-mapping entry.

      2.2.2 type

        The value of the type attribute should hold the java primitive/class for this type-mapping entry.

    2.3 The disallowed-field tag

      The disallowed-field tag is a subtag of the database-schema tag. It is used to denote a field/name that cannot be used since it's a reserved keyword in this database.

      One database-schema tag normaly holds a number of type-mapping tags.

      Below is a explanation of the attribute

      2.3.1 name

        The value of the name attribute should hold a dissalowed field/name. Case-sensitivity is set with the case-sensisitity attribute in the database-schema tag.

3 Available Schemas

    Below is a list of the DBMS that has sample schemas in the config/database-schemas/ directory of the Orion distribution:

Copyright © 2005 IronFlare AB