<?xml version="1.0" encoding="UTF-8"?>
  <!--
    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.
  -->
<!DOCTYPE document PUBLIC "-//MAKUMBA//DTD Documentation V2.0//EN" "document-v20-mak.dtd">
<document>
  <header>
    <title>Configuring your Makumba database</title>
  </header>
  <body>
    <p>
      This file describes which database engines are supported, and in which conditions. Many new SQL engines can be
      supported and many existing support can be optimised dramatically. Please send your questions or contributions to
      <a href="mailto:makumba-devel AT lists.sourceforge.net">makumba-devel AT lists.sourceforge.net</a>
      . Thank you!
    </p>
    <section id="config-files">
      <title>Database configuration files</title>
      <p>
        An application can be made to work with multiple databases, each one is instantiated with one
        <strong> database configuration file</strong>
        . Such files are
        <code>java.util.Properties</code>
        files and are passed to the method
        <a href="ext:getConnectionTo">MakumbaSystem.getConnectionTo(String configName)</a>
        .
      </p>
      <section id="config-files-names">
        <title>Database configuration file names</title>
        <p>The file name of configuration files should be as follows:</p>
        <source>Host_SQLEngineType_DatabaseName.properties</source>
        <p>
          The extension is not indicated to the constructor.
          <br />
          For example
        </p>
        <source>dbhost.yourdomain.org_mysql_makumba.properties</source>
        <p>will be used as</p>
        <source>Database.getDatabase("dbhost.yourdomain.org_mysql_makumba");</source>
        <p>Such a call will automatically connect to the following JDBC URL:</p>
        <source>jdbc:mysql://dbhost.yourdomain.org/makumba</source>
        <p>If SQL engine port numbers are needed, file names are like:</p>
        <source>Host_port_SQLEngineType_DatabaseName.properties</source>
        <p>which will connect to JDBC URLs like:</p>
        <source>jdbc:mysql//dbhost.yourdomain.org:8282/makumba</source>
      </section>
      <section id="config-properties">
        <title>Database configuration properties</title>
        <p>In the configuation files, the following properties can be defined:</p>
        <table>
          <tr>
            <td>
              <code> sql.setting=value</code>
            </td>
            <td>sends a setting to the JDBC driver (for example user, password, create, etc). Such settings are
              JDBC driver specific</td>
          </tr>
          <tr>
            <td>
              <code>dbsv=value</code>
            </td>
            <td>the DBSV will be used to set the most significant 8 bits of all primary keys generated for the new
              records created by this makumba client. Using different DBSVs can help to ensure key uniqueness among
              multiple clients using (or synchronizing with) the same database. Database inserts of records that have
              the same primary key as an already existing record in that table (i.e. a record created by a client that
              used the same DBSV) will fail. You cannot specify both dbsv and auto_increment in the same configuration
              file, but you can access the same database with some clients using (different) dbsv-s, and others using
              autoIncrement. </td>
          </tr>
          <tr>
            <td>
              <code>autoIncrement=true</code>
            </td>
            <td>if autoIncrement is on, makumba will use the server-side primary key generation. You cannot specify
              both dbsv and auto_increment in the same configuration file, but you can access the same database with
              some clients using (different) dbsv-s, and others using autoIncrement.</td>
          </tr>
          <tr>
            <td>
              <code> initConnections=value </code>
            </td>
            <td>specifies the initial number of connections that makumba will open when connecting to the
              database. Default is 1, though some makumba versions will open 2-3 more connections pretty early in their
              activity. High server load will lead to more connections being open.</td>
          </tr>
          <tr>
            <td>
              <code>dbclass=javaClass</code>
            </td>
            <td>The main class of the makumba database driver. This is normally read from
              org/makumba/db/sql/sqlEngines.properties, but other, more powerful drivers can be plugged in.</td>
          </tr>
          <tr>
            <td>
              <em>
                <code>tableclass=javaClass</code>
              </em>
            </td>
            <td>Table handler of the database driver (optional, norlally known by the dbclass or read from
              org/makumba/db/sql/sqlEngines.properties)</td>
          </tr>
          <tr>
            <td>
              <code> typename = SQLtableName </code>
            </td>
            <td>Sets the name of the SQL table representing the given type to SQLtableName (to inhibit automatic
              table name generation)</td>
          </tr>
          <tr>
            <td>
              <code>typename-&gt;field=SQLfieldName</code>
          </td>
  <td>Sets the name of the SQL field representing the given object
attribute to SQLfieldName (to inhibit automatic field name generation)</td>
</tr>
<tr>
<td><code>typenamePrefix=typenameShorthand</code></td>
  <td>Before automatic tablename generation; shortens the names of
all the types that begin with the indicated prefix by replacing it with the
indicated shorthand. Useful for SQL engines that have severe limitations
on tablename length</td>
</tr><tr>
<td> <code>alter#typenameShorthand=true|false</code></td>
  <td>If true allows automatic alteration of the SQL table representing
the type(s) when the SQL table doesn't match the type definition. For example:<br/>
    <code>alter#=true</code> <br/>
    <code>alter#general=false</code><br/>
will allow alteration of all tables from the database except for tables whose
names begin with "general"</td>
</tr><tr>
<td> <code>admin#typenameShorthand=true|false</code></td>
  <td>If true allows deletion of multiple records of the respective
types during <code>org.makumba.delete</code> and <code>org.makumba.copy</code> the type(s) when the
SQL table doesn't match the type definition. </td>
</tr><tr>
 <td><code>addUnderscore=true|false</code></td>
  <td>Specifies whether to add an underscore at the end of the generated
field and table names during automatic name generation. It is true by default.
(Introduced to avoid conflicts with reserved words on some SQL engines)</td></tr>
</table>

</section>

    <section id="database-lookup-files">
      <title>Database lookup files</title>

<p> An application which runs in several places (machines and/or directories)
with several database can discover its database automatically using <strong>database
lookup file</strong>s. Then the <a
 href="ext:getDefaultDatabaseName">MakumbaSystem.getDefaultDatabaseName()</a>
family of methods can be used. The format of the entries in the lookup file
is:</p>
<p class='quote'>
<code>Host#currentDirectory= configFileName</code> <br/>
  <code>Host#default= configFileName</code> <br/>
  <code>default= configFileName</code>
</p>
<p>
The local host and current directory are searched for. If that is not found,
the default database configuration is used. For example</p>
<p class='quote'>
<code>server.domain.com#/server/home= dbhost.yourdomain.com_pgsql_makumba</code>
  <br/>
  <code>server.domain.com#default= dbhost.yourdomain.com_pgsql_makumba_test</code>
  <br/>
  <code>default= localhost_mysql_makumba</code></p>
  <p>
 The non-argument <a
 href="ext:getDefaultDatabaseName">MakumbaSystem.getDefaultDatabaseName()</a>
will look in <strong>MakumbaDatabase.properties</strong>. Instead of this default, a
String argument (a database lookup file in the classpath), or a Properties
object read from a database lookup file can be indicated.</p>

</section>
    </section>

    <section id="supported-sql-engines">
      <title>Supported SQL engines</title>
      
       <p>Only SQL engines or bridges are supported, although there are possibilities
to use non-SQL databases with Makumba <br/>
A "makumba" database is assumed to exist in the SQL engine.</p>
<p>If you want to use one of the supported SQL engines, you should put its
JDBC driver (URLs are provided) in the CLASSPATH.</p>
<p>Whenever you instantiate a org.makumba.db.Database object, you need to
supply a <a href="#Configuration">configuation file</a>. In this file you
see what you need to include in the configuration for specific SQL engines.
</p>

<table>
    <tr>
 <td><strong>SQL Engine and JDBC driver and versions</strong></td>
  <td><strong>&nbsp;Database configuration file (*.properties)</strong></td>
  <td><strong>Observations</strong></td>
 </tr>
  <tr>
 <td><strong>MySQL <sup>TM</sup></strong> <br/>
      <a href="http://www.mysql.com">www.mysql.com</a> <br/>
      <a href="http://www.mysql.com/downloads/api-jdbc.html">MySQL Connector/J</a> JDBC driver</td>
  <td><strong>hostname_mysql_databasename </strong>will connect to the
indicated database on the indicated host.</td>
  <td>The main driver tested. <br/>
Earlier versions of the JDBC driver have the maximum packet length limited
to 64K <br/>
To get a development-like low memory footprint of the mysql daemon (it's
initially made for 32 M memory), decrease <code>key_buffer, flush_time, thread
stack</code>&nbsp; in the <strong>c:\my.cnf</strong> file, then restart the daemon. <br/>
If you work with large binary objects, increase <code>max_allowed_packet</code>
so they can be transmitted in one shot...</td>
 </tr>
  <tr>
 <td><strong>ODBC <sup>TM</sup></strong>
 <ul>
 <li> The jdbc-odbc bridge that
comes with the JDK is used.</li>
  <li> The Windows ODBC Jet driver (used with access, excell, etc) lower
than 4</li>
  <li> Other, more serious ODBC drivers like MS SQL server and Oracle should
work better</li>
 </ul>
 </td>
  <td><strong>hostname_odbc_data-source-name</strong> <br/>
will connect to the repsective ODBC data source on the local host. The hostname
is not used.</td>
  <td>not tested since spring 2002. worked nicely though</td>
 </tr>
  <tr>
 <td><strong>PostgreSQL&nbsp;</strong> <br/>
      <a href="http://www.postgresql.org">www.postgresql.org</a>
  <ul>
      <li> the
        <a href="http://www.retep.org.uk/postgres/">JDBC driver</a> distributed
with PostgreSQL is use</li>
  <li> JDK 1.2 and higher need a specific driver</li>
  </ul>
 </td>
  <td><strong>hostname_postgresql_databasename</strong>
  <ul>
  <li>
will connect to the local (!) postgresql daemon and the indicated databas</li>
  <li> If the PostgreSQL daemon runs on another machine than the Makumba
application, any form of "repeating" to port 5432 of the local machine is
known to work</li>
  </ul>
 </td>
  <td>Limited JDBC functionality before version 7.1. Not tested with 7.1 yet.
      <br/>
PostgreSQL has a limitation of table name length to 32 chars. Use table renaming:
      <br/>
      <source><em>some_org.some_application</em>= <em>so.sa</em></source> <br/>
This will replace with the shorter "so.sa" the left part of the type names
that begin with "some_org.some_application"</td>
 </tr>
  <tr>
 <td><strong>IBM DB2<sup>TM</sup></strong> <br/>
      <strong/>&nbsp;</td>
  <td><strong>hostname_db2_databasename</strong></td>
  <td>Was adapted to makumba very easily. Needs more testing.</td>
 </tr>
  <tr>
 <td><a href="http://www-3.ibm.com/software/data/informix/"><strong>Informix<sup>TM</sup></strong></a> Dynamic Server, version 9.20.UC2,
 using <a href="http://www6.software.ibm.com/devcon/devcon/docs/jdbcv221.htm">IBM Informix JDBC Driver/Embedded SQL V2.21</a></td>
  <td><strong>hostname_port_informix-sqli_databasename</strong></td>
  <td>Works, was easy to adapt. Tricky part was creating an informix db with transactions enabled and setting up a
connection to it. See <a href="http://cvs.makumba.org/classes/test/somehost_1560_informix-sqli_dbname.properties.example">sample config file</a>.</td>
 </tr>
  <tr>
 <td><strong>Cloudscape<sup>TM</sup></strong> <br/>
SQL engine written in Java <br/>
      <a href="http://www.cloudscape.com">www.cloudscape.com</a> <br/>
includes JDBC driver <br/>
requires JDK 1.2 or higher</td>
  <td><strong>hostname_cloudscape_databasename</strong> <br/>
sql.create=true will create the named database <br/>
The host name is not significant</td>
  <td>not tested lately, but can be get to work</td>
 </tr>
  <tr>
 <td><strong>QED<sup>TM</sup></strong> <br/>
SQL engine written in Java <br/>
      <a href="http://www.quadcap.com/home.html">http://www.quadcap.com/home.html</a>
      <br/>
includes JDBC driver <br/>
requires JDK 1.2 or higher</td>
  <td><strong>hostname_qed_databasename</strong> <br/>
sql.create=true will create the named database <br/>
The host name is not significant</td>
  <td>fast and full of features but not stable at the time of last tests
(begining of 2001)<br/>
sensitive to Ctrl-C during operation (lockfile must be deleted)</td>
 </tr>
  <tr>
 <td><strong>FirstSQL<sup>TM</sup></strong> <br/>
SQL engine written in Java <br/>
      <a href="http://www.firstsql.com/firstsqlj/">http://www.firstsql.com/firstsqlj/</a>
      <br/>
includes JDBC driver <br/>
requires JDK 1.1, does not work with 1.2</td>
  <td><strong>hostname_dbcp_databasename</strong> <br/>
Has only one database. Hostname and database name are not used. sql.user should
be adm or beta</td>
  <td>Not stable at the time of last tests (begining of 2001). Ctrl-C can
corrupt database. Need careful databae closing for database not to be corrupted
upon exceptions. <br/>
Incorrect responses on large data. Limited JDBC features (setBytes and setStream
not supported)</td>
 </tr>
  <tr>
 <td><strong>mSQL<sup>TM</sup></strong> <br/>
      <a href="http://www.hughes.com.au/">http://www.hughes.com.au/</a> <br/>
the <a href="http://www.imaginary.com/Java/Soul/">Imaginary JDBC driver </a>is
used</td>
  <td><br/>
      </td>
  <td>hasn't been tested since 1999, but it shouldn't be difficult to get
it work with the latest sql code. Still, its limitations at the time made
it unsuitable for makumba.&nbsp;</td>
 </tr>

</table>
      
      
</section>

<section id="other-engines">
      <title>Other SQL engines</title>

<p>Lots of&nbsp; other SQL engines cab be supported, please contribute! You
can test any database engine as follows (subprotocol is its JDBC subprotocol,
such as odbc, mysql, etc)</p>
<ol>
 <li> In <code>org\makumba\db\sql\sqlEngines.properties</code>
  add <code>subprotocol=JDBC_driver_class</code></li>
<li> make a database configuration
file <strong>hostname_subprotocol_databasename.properties</strong></li>
  <li> create the database <strong>databasename</strong></li>
  <li> At this point you should be able to open a database. Create a "makumba"
database in the db server and try to execute "ant test"</li>
</ol>

<section id="support-other-engines">
      <title>Adding support for other engines</title>

<p>After following section 3 above, after your JDBC driver loads correctly,
and your database is found, you might still see errors while running the test
cases (java test.table) unless your SQL engine is SQL-92 compatible (and
few of them are...). To treat <a href="http://downloads.hemtech.co.za/jdo/manual/database_specific_information.html">particularities of your engine</a>, you will need
to define a new handler family. To do that</p>
<ul>
 <li> look in the <a href="ext:develdoc">developer
documentation</a> about record handlers. Check out the other specific SQL
handler families (org.makumba.db.sql.*)</li>
  <li> make a new package "subprotocol"</li>
  <li> mae a new class <code>subprotocol.Database extends org.makumba.db.sql.Database</code></li>
  <li> make an empty class <code>subprotocol.RecordManager extends org.makumba.db.sql.RecordManager</code></li>
  <li> make an empty class <code>subprotocol.FieldManager extends org.makumba.db.sql.FieldManager</code></li>
  <li> Add the following to org\makumba\db\sql\sqlEngines.properties

  <ul>
 <li> <code>subprotocol.dbclass= subprotocol.Database</code></li>
  <li> <code>subprotocol.tableclass= subprotocol.RecordManager</code></li>

  </ul>
  </li>

  <li> overwriting certain methods in one of the three classes above should
be able to express your SQL engine's specifics</li>
  <li> if these specifics are related to certain types of fields, you should
create a class for that type T (TManager extends FieldManager) and work with
redirectManager.properties to indicate type similarities</li>

</ul>

</section>

</section>



  </body>
</document>
