# Configuring your Makumba database

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 makumba-devel AT lists.sourceforge.net. Thank you!

Contents:

> 1. Database configuration files
> 1.1 Database configuration file name
> 1.2 Database configuration properties
> 1.3 Database lookup files
> 2 Supported SQL engines
> 3. other SQL engines
> 3.1 adding support for new engines

## 1. Database configuration files

An application can be made to work with multiple databases, each one is instantiated with one database configuration file. Such files are `java.util.Properties` files and are passed to the method MakumbaSystem.getConnectionTo(String configName).

### 1.1 Database configuration file names

The file name of configuration files should be as follows:

> `Host_SQLEngineType_DatabaseName.properties`

The extension is not indicated to the constructor.
For example

> `dbhost.yourdomain.org_mysql_makumba.properties`

will be used as

> `Database.getDatabase("dbhost.yourdomain.org_mysql_makumba");`

Such a call will automatically connect to the following JDBC URL:

> `jdbc:mysql://dbhost.yourdomain.org/makumba`

If SQL engine port numbers are needed, file names are like:

> `Host_port_SQLEngineType_DatabaseName.properties`

which will connect to JDBC URLs like:

> `jdbc:mysql//dbhost.yourdomain.org:8282/makumba`

### 1.2 Database configuration properties

In the configuation files, the following properties can be defined:

> `sql.setting=value`

> > sends a setting to the JDBC driver (for example user, password, create, etc). Such settings are JDBC driver specific

> `dbsv=value`

> > 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.

> `autoIncrement=true`

> > 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.

> `initConnections=value`

> > 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.

> `dbclass=javaClass`

> > 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.

> `tableclass=javaClass`

> > Table handler of the database driver (optional, norlally known by the dbclass or read from org/makumba/db/sql/sqlEngines.properties)

> `typename=SQLtableName`

> > Sets the name of the SQL table representing the given type to SQLtableName (to inhibit automatic table name generation)

> `typename->field=SQLfieldName`

> > Sets the name of the SQL field representing the given object attribute to SQLfieldName (to inhibit automatic field name generation)

> `typenamePrefix=typenameShorthand`

> > Before automatic tablename generation; shortens the names of all the types that begin with the indicated prefix by replacing it with the indicated shorthand. Usefull for SQL engines that have severe limitations on tablename length

> `alter#typenameShorthand=true|false`

> > 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:

> > > `alter#=true`
> > > `alter#general=false`

> > will allow alteration of all tables from the database except for tables whose names begin with "general"

> `admin#typenameShorthand=true|false`

> > If true allows deletion of multiple records of the respective typesduring org.makumba.delete and org.makumba.copy the type(s) when the SQL table doesn't match the type definition.

> `addUnderscore=true|false`

> > 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)

### 1.3 Database lookup files

An application which runs in several places (machines and/or directories) with several database can discover its database automatically using database lookup files. Then the MakumbaSystem.getDefaultDatabaseName() family of methods can be used. The format of the entries in the lookup file is

> `Host#currentDirectory= configFileName`
> `Host#default= configFileName`
> `default= configFileName`

The local host and current directory are searched for. If that is not found, the default database configuration is used. For example

> `server.domain.com#/server/home= dbhost.yourdomain.com_pgsql_makumba`
> `server.domain.com#default= dbhost.yourdomain.com_pgsql_makumba_test`
> `default= localhost_mysql_makumba`

The non-argument MakumbaSystem.getDefaultDatabaseName() will look in MakumbaDatabase.properties. 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.

## 2. Supported SQL engines

Only SQL engines or bridges are supported, although there are possibilities to use non-SQL databases with Makumba
A "makumba" database is assumed to exist in the SQL engine.

If you want to use one of the supported SQL engines, you should put its JDBC driver (URLs are provided) in the CLASSPATH.

Whenever you instantiate a org.makumba.db.Database object, you need to supply a configuation file. In this file you see what you need to include in the configuration for specific SQL engines.

SQL Engine and JDBC driver and versions | Database configuration file (*.properties) | Observations
MySQL TM www.mysql.com MySQL Connector/J JDBC driver | hostname_mysql_databasename will connect to the indicated database on the indicated host. | The main driver tested. Earlier versions of the JDBC driver have the maximum packet length limited to 64K To get a development-like low memory footprint of the mysql daemon (it's initially made for 32 M memory), decrease `key_buffer, flush_time, thread stack` in the c:\my.cnf file, then restart the daemon. If you work with large binary objects, increase `max_allowed_packet` so they can be transmitted in one shot...
ODBC TM The jdbc-odbc bridge that comes with the JDK is used. The Windows ODBC Jet driver (used with access, excell, etc) lower than 4 Other, more serious ODBC drivers like MS SQL server and Oracle should work better | hostname_odbc_data-source-name will connect to the repsective ODBC data source on the local host. The hostname is not used. | not tested since spring 2002. worked nicely though
PostgreSQL www.postgresql.org the JDBC driver distributed with PostgreSQL is use JDK 1.2 and higher need a specific driver | hostname_postgresql_databasename will connect to the local (!) postgresql daemon and the indicated databas 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 | Limited JDBC functionality before version 7.1. Not tested with 7.1 yet. PostgreSQL has a limitation of table name length to 32 chars. Use table renaming: `some_org.some_application= so.sa` This will replace with the shorter "so.sa" the left part of the type names that begin with "some_org.some_application"
IBM DB2TM | hostname_db2_databasename | Was adapted to makumba very easily. Needs more testing.
InformixTM Dynamic Server, version 9.20.UC2, using IBM Informix JDBC Driver/Embedded SQL V2.21 | hostname_port_informix-sqli_databasename | Works, was easy to adapt. Tricky part was creating an informix db with transactions enabled and setting up a connection to it. See sample config file.
CloudscapeTM SQL engine written in Java www.cloudscape.com includes JDBC driver requires JDK 1.2 or higher | hostname_cloudscape_databasename sql.create=true will create the named database The host name is not significant | not tested lately, but can be get to work
QEDTM SQL engine written in Java http://www.quadcap.com/home.html includes JDBC driver requires JDK 1.2 or higher | hostname_qed_databasename sql.create=true will create the named database The host name is not significant | fast and full of features but not stable at the time of last tests (begining of 2001) sensitive to Ctrl-C during operation (lockfile must be deleted)
FirstSQLTM SQL engine written in Java http://www.firstsql.com/firstsqlj/ includes JDBC driver requires JDK 1.1, does not work with 1.2 | hostname_dbcp_databasename Has only one database. Hostname and database name are not used. sql.user should be adm or beta | 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. Incorrect responses on large data. Limited JDBC features (setBytes and setStream not supported)
mSQLTM http://www.hughes.com.au/ the Imaginary JDBC driver is used |  | 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.

## 3. Other SQL engines

Lots of 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)

1. In `org\makumba\db\sql\sqlEngines.properties` add `subprotocol=JDBC_driver_class`
2. make a database configuration file hostname_subprotocol_databasename.properties
3. create the database databasename
4. 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"

### 3.1 Adding support for new engines

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 particularities of your engine, you will need to define a new handler family. To do that

- look in the developer documentation about record handlers. Check out the other specific SQL handler families (org.makumba.db.sql.*)
- make a new package "subprotocol"
- mae a new class `subprotocol.Database extends org.makumba.db.sql.Database`
- make an empty class `subprotocol.RecordManager extends org.makumba.db.sql.RecordManager`
- make an empty class `subprotocol.FieldManager extends org.makumba.db.sql.FieldManager`
- Add the following to org\makumba\db\sql\sqlEngines.properties
  - `subprotocol.dbclass= subprotocol.Database`
  - `subprotocol.tableclass= subprotocol.RecordManager`
- overwriting certain methods in one of the three classes above should be able to express your SQL engine's specifics
- 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