<?xml version="1.0"?>
<!--
 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.
-->
<document>

 <properties>
  <title>Database information</title>
 </properties>

 <body>

  <section name="Introduction">
    This document contains informations on compatible versions of supported
    databases and other database-specific information.
  </section>

  <section name="Supported RDBMS">
    <table>
      <tr>
        <th>RDBMS</th>
        <th>Driver</th>
        <th>Tester</th>
      </tr>
      <tr>
        <td><a href="#Apache_Derby">Apache Derby</a></td>
        <td>org.apache.derby.jdbc.EmbeddedDriver</td>
        <td>Thomas Fox</td>
      </tr>
      <tr>
        <td><a href="#Hsqldb">Hsqldb</a></td>
        <td>org.hsql.jdbcDriver</td>
        <td>Thomas Fox</td>
      </tr>
      <tr>
        <td><a href="#MySQL">MySQL</a></td>
        <td>org.gjt.mm.mysql.Driver</td>
        <td><a href="mailto:seade@backstagetech.com.au">Scott Eade</a></td>
      </tr>
      <tr>
        <td><a href="#PostgreSQL">Postgres</a></td>
        <td>org.postgresql.Driver</td>
        <td><a href="mailto:seade@backstagetech.com.au">Scott Eade</a></td>
      </tr>
      <tr>
        <td><a href="#Oracle">Oracle</a></td>
        <td>oracle.jdbc.driver.OracleDriver</td>
        <td>Thomas Fox</td>
      </tr>
      <tr>
        <td><a href="#Microsoft_SQL_Server">MS SQL</a></td>
        <td>net.sourceforge.jtds.jdbc.Driver</td>
        <td><a href="mailto:Greg.Monroe@DukeCE.com">Greg Monroe</a></td>
      </tr>
    </table>

    <p>
      If your RDBMS is not listed here, please read the document about
      <a href="new-database-support.html">writing DB Adapters</a>.
    </p>
  </section>

  <section name="Apache Derby">
    <subsection name="Supported Versions">
      <p>
        Derby 10.5.1.1 introduced support for limit and offset.
        This is used in within Torque 4, so limit and offset does not
        work for Derby &lt; 10.5.1.1
      </p>
    </subsection>
  </section>

  <section name="HSQLDB">
    <subsection name="Supported Versions">
      <p>
        Torque 4 was tested against HSQLDB 2.2.8. 
        It is unknown whether Torque 4 still works with hsqldb 1.x.-
      </p>
    </subsection>
  </section>

  <section name="MySQL" >
    <subsection name="Supported Versions">
      <p>
        Torque 4 was tested against MySQL 5.5. 
        Subselects are known not to work for MySQL &lt; 5.0.
        There are no other known issues for old MySQL versions.
      </p>
    </subsection>
    <subsection name="Table create options">
      <p>
        The MySQL templates can handle create options for tables.
        For example, the desired create-sql output is
      </p>
<source><![CDATA[
CREATE TABLE book
(
    ...
) COLLATE=latin1_german2_ci ENGINE=InnoDB;
]]></source>
      <p>
        For this, specify the desired options in the option elements of the
        table to create:
      </p>
<source><![CDATA[
  <table name="book" description="Book table">
    <option key="COLLATE" value="latin1_german2_ci"/>
    <option key="ENGINE" value="InnoDB"/>
    ...
  </table>
]]></source>
      <p>
        Torque recognizes the following keys to be separated with an equals 
        character("=") between key and value:
        <ul>
          <li>ENGINE</li>
          <li>AVG_ROW_LENGTH</li>
          <li>CHARACTER SET</li>
          <li>DEFAULT CHARACTER SET</li>
          <li>CHECKSUM</li>
          <li>COLLATE</li>
          <li>DEFAULT COLLATE</li>
          <li>CONNECTION</li>
          <li>DATA DIRECTORY</li>
          <li>DELAY_KEY_WRITE</li>
          <li>INDEX DIRECTORY</li>
          <li>INSERT_METHOD</li>
          <li>KEY_BLOCK_SIZE</li>
          <li>MAX_ROWS</li>
          <li>MIN_ROWS</li>
          <li>PACK_KEYS</li>
          <li>PASSWORD</li>
          <li>ROW_FORMAT</li>
          <li>TABLESPACE</li>
          <li>UNION</li>
        </ul>
      </p>
      <p>
        Torque recognizes the following keys to be separated with a space 
        character(" ") between key and value:
        <ul>
          <li>PARTITION BY</li>
        </ul>
      </p>
      <p>
        All other keys are ignored.
      </p>
    </subsection>
  </section>

  <section name="PostgreSQL">
    <subsection name="Supported Versions">
      <p>
        Torque 4 was tested against PostgreSQL 9.1.
        The CASCADE option for the drop table commands in the generated SQL
        will only work for PostgreSQL 7.3 and later.
        Columns declared as BLOB can not be set to null by Torque,
        as torque maps them to BYTEA but treats them as Types.BLOB internally,
        which is not accepted by Postgresql.
        Use one of the other *BINARY types instead, they are all mapped to the
        postgresql datatype BYTEA.
        There are no other known issues for old PostgreSQL versions.
      </p>
    </subsection>
  </section>

  <section name="Oracle Howto" >

    <subsection name="Supported Versions">
      <p>
        Torque 4 was tested against Oracle 11g.
        There are no known issues for Oracle 9i and later.
        The following issues exist for older versions of Oracle:
      </p>

      <p>
        The data type TIMESTAMP is not supported up to and including
        Oracle 8.1.7. Use the types DATE or TIME instead of TIMESTAMP.
      </p>

    </subsection>

    <subsection name="Oracle peculiarities">
      <p>
        TODO check for Torque 4
        The data type TIME only has day accuracy for Oracle. This is due to
        the fact that oracle does not support a SQL type TIME.
        If you are using Oracle 9i and later, you can use the type TIMESTAMP
        instead which gives you millisecond accuracy.
      </p>

      <p>
        Oracle does not distinguish between empty strings and null strings.
        If an empty string is inserted into a table, it will be treated as null.
        If you want to re-read this column by selecting records which contain
        an empty string, oracle will return no columns. The only way to select
        the column is to query for records which contain null in that column.
        <br/>
        This behaviour is different in most other databases. So if you
        want to write code which works for other databases and oracle, you need
        to consider both cases - the column might be null and it might contain
        an empty string. For example:
      </p>

<source>
Criterion c1 = new Criterion(COLUMN, "", Criteria.EQUAL);
Criterion c2 = new Criterion(COLUMN, null, Criteria.ISNULL);
criteria.and(c1.or(c2));
</source>
    </subsection>
  </section>
  
  <section name="Microsoft SQL Server">
    <subsection name="Supported Versions">
      <p>
        Torque 4 was tested against MSSQL 2012.
        There are no known issues for MSSQL versions >= MSSQL 2000.
        See the jira issue
        <a href="http://issues.apache.org/jira/browse/TORQUE-46">TORQUE-46</a>
        for the issues with MSSQL 7.
      </p>
      <p>
        MSSQL 2005 and earlier do not support the Date data type.
        So to work with these databases, replace the data type
        of all DATE columns by the data type TIMESTAMP.
      </p>
    </subsection>


    <subsection name="Database Creation">
      <p>
        It helps to name the database the same name as your project.
        If the name is not the same, you will need to manually change
        the database name in your properties files.
        After the database is created, you will need to create a new user, or
        give an existing user permissions to the new database.
        Create the new user with SQL Server Authentication, 
        and assign the users default database to the newly created database.
        Make sure the database user has the db_owner role.
        Please refer to SQL Server documentation for further details.
      </p>
    </subsection>

    <subsection name="JDBC Driver">
      <p>
        There is an excellent open source driver for MS SQL called 
        <a href="http://jtds.sourceforge.net/">jTDS</a>.  This driver is
        being used in production mode with Torque and many other JDBC applications.
        This driver is used for testing Torque with MSSQL, other
        drivers should work the same, but your milage may vary.
      </p>
    </subsection>

    <subsection name="Generated SQL Code">
      <p>
        SQL Scripts generated by the Ant sql target do not contain
        the fully qualified  table names, e.g. user.table.
        When manually running these scripts, do this  as the user
        you want to own the tables. 
      </p>
    </subsection>
  </section>
 </body>
</document>
