Before the DBAL will do anything different for you than just connecting to the default database you will have to configure it. By default it connects using the “native” handler type - which means direct interaction with MySQL.
Since the DBAL offers to store information in multiple sources and not just a single database you might have to understand handlers first.
First, some definitions:
handler type - which kind of interface is used for a data handler. The options are “native”, “adodb” or “userdefined”.
native - Connects directly to MySQL with hardcoded PHP functions
adodb - Is an instance of ADOdb database API offering support for a long list of databases other than MySQL. The DBAL extension has been developed with a focus on ADOdb until now, so it should work.
userdefined - Is an instance of a userdefined class which must contain certain functions to supply results from the “database” - offers support for just any kind of data source you can program an interface to yourself!
handlerKey - a string which uniquely identifies a data handler. Each handler represents an instance of a handler type (see above). The handlerKey can be any alphanumeric string. The handler key “_DEFAULT” is the default handler for all tables unless otherwise configured.
tablename - the database table name seen from the TYPO3 side in the system (might differ from the real database name if mapping is enabled!)
The DBAL is configurable through $TYPO3_CONF_VARS['EXTCONF']['dbal'] entered in “ext_localconf.php” / “localconf.php”. This table is an overview of the main keys in this array:
Key | Datatype: | Description: |
|---|---|---|
handlerCfg[handlerKey] | ->handlerCfg | Configuration of each data handler you want to use in the system. Each handler is identified with a string (handlerKey) which is used in the “table2handlerKeys” configuration (see below) to pair tablenames with handlers. There is always a default handler needed which has the handlerKey “_DEFAULT”. By default this handler is configured with the classic username/password/host and database settings from localconf.php in TYPO3. If you want to use ADOdb or just need to store a table in another database you can configure a handler here and map the tables you need to that handler (with “table2handlerKeys”, see below). |
table2handlerKeys[tablename] | handlerKey | Using other handlers than the “_DEFAULT” handler key is possible on a per-table basis and simply done by entering the table name as key in this array and letting the value be the handlerKey you want to use for this table! Notice: If tables are joined both tables must use the same handlerKey. If they do not TYPO3 will exit with a fatal error! You can use the debug options to track all table joins and assess which tables can safely be handled together. |
mapping[tablename] | ->mapping | Configuration of mapping of table and fieldnames. For instance you can configure that TYPO3 should use a physical table in the database named “typo3_pages” instead of “pages”. Or you can map fieldname in a similar fashion. The point is that TYPO3 always sees a table or field names as TYPO3 requires internally but in reality the table- or field name could be something different in the physical database source. There is a performance loss by configuring such mapping of course: Result rows are preprocessed before being returned and all SQL queries are parsed, transformed and re-compiled again before execution. |
debugOptions | ->debug | Options for various debugging in the DBAL. |
Configuration of a data handler
Key | Datatype: | Description: |
|---|---|---|
type | handler type (string) | The type of the handler. The type is a fixed keyword between these:
(See description of each in the introduction above) The “native” handler is used by default (and is MySQL-only!) The handler type will determine what options are available for “config” |
config | array | Array containing configuration for the handler. See below for options. Notice that the options are supported depending on handler type. For this, see information in italic and square brackets. |
config[username] | string | Username for connection Notice: For the “_DEFAULT” handler this is overridden by $typo_db_username from localconf.php [Only native / adodb ] |
config[password] | string | Password for connection Notice: For the “_DEFAULT” handler this is overridden by $typo_db_password from localconf.php [Only native / adodb ] |
config[host] | string | Host for the database server Notice: For the “_DEFAULT” handler this is overridden by $typo_db_host from localconf.php [Only native / adodb ] |
config[database] | string | The database name Notice: For the “_DEFAULT” handler this is overridden by $typo_db from localconf.php [Only native / adodb ] |
config[driver] | string | Which driver, (eg. “mysql”, “oci8” etc.). Depending on API (see ADOdb documentation for details) [Only adodb ] |
config[classFile] | string | Class file for user defined DB handler class. Eg. “EXT:dbal/handlers/class.tx_dbal_handler_xmldb.php” Must be relative path to PATH_site. The “EXT:” prefix can be used for locations inside of extensions. [Only userdefined] |
config[class] | string | Class name for the handler inside of config[classFile]. Eg. “tx_dbal_handler_xmldb” Please see examples/templates of userdefined handlers inside dbal/handlers/ directory. [Only userdefined] |
Using ADOdb or PEAR::DB for the _DEFAULT handler
1: $TYPO3_CONF_VARS['EXTCONF']['dbal']['handlerCfg'] = array (
2: '_DEFAULT' => array (
3: 'type' => 'adodb',
4: 'config' => array(
5: 'driver' => 'mysql',
6: )
7: )
8: );
If you need to use other databases, just change the value in line 5 to the name of the other database driver. See ADOdb manual for details.
Using another MySQL database for the “tt_guest” and “sys_note” tables
1: $TYPO3_CONF_VARS['EXTCONF']['dbal']['handlerCfg'] = array (
2: '_DEFAULT' => array (
3: 'type' => 'native',
4: 'config' => array(
5: 'username' => '', // Set by default (overridden)
6: 'password' => '', // Set by default (overridden)
7: 'host' => '', // Set by default (overridden)
8: 'database' => '', // Set by default (overridden)
10: )
11: ),
12: 'alternativeMySQLdb' => array (
13: 'type' => 'native',
14: 'config' => array(
15: 'username' => 'your_username',
16: 'password' => 'your_password',
17: 'host' => 'localhost',
18: 'database' => 'alternative_database_name',
19: )
20: ),
21: );
22:
23: $TYPO3_CONF_VARS['EXTCONF']['dbal']['table2handlerKeys'] = array (
24: 'tt_guest' => 'alternativeMySQLdb',
25: 'sys_note' => 'alternativeMySQLdb',
26: );
In line 24 and 25 we configure the two tables to use the handler key “alternativeMySQLdb” instead of the “_DEFAULT” handler. In both cases the handlers will connect natively to MySQL - but two different databases at the “same time”.
Storing “tt_guest” and “sys_note” tables in Oracle
1: $TYPO3_CONF_VARS['EXTCONF']['dbal']['handlerCfg'] = array (
2: '_DEFAULT' => array (
3: 'type' => 'native',
4: 'config' => array(
5: 'username' => '', // Set by default (overridden)
6: 'password' => '', // Set by default (overridden)
7: 'host' => '', // Set by default (overridden)
8: 'database' => '', // Set by default (overridden)
9: )
10: ),
11: 'oracleDB' => array (
12: 'type' => 'adodb',
13: 'config' => array(
14: 'username' => 'your_username',
15: 'password' => 'your_password',
16: 'host' => 'localhost',
17: 'database' => 'oracleDB',
18: 'driver' => 'oci8'
19: )
20: ),
21: );
22:
23: $TYPO3_CONF_VARS['EXTCONF']['dbal']['table2handlerKeys'] = array (
24: 'tt_guest' => 'oracleDB',
25: 'sys_note' => 'oracleDB',
26: );
This example is basically similar to the former, just that the key name was changed to “oracleDB” for convenience.
The real change is that
line 12 configures ADOdb to be used and
line 18 configures ADOdb to use the “oci8” driver instead of MySQL.
Storing “tt_guest” and “sys_note” tables in an XML file
1: $TYPO3_CONF_VARS['EXTCONF']['dbal']['handlerCfg'] = array (
2: '_DEFAULT' => array (
3: 'type' => 'native',
4: 'config' => array(
5: 'username' => '', // Set by default (overridden)
6: 'password' => '', // Set by default (overridden)
7: 'host' => '', // Set by default (overridden)
8: 'database' => '', // Set by default (overridden)
9: )
10: ),
11: 'xmlDB' => array (
12: 'type' => 'userdefined',
13: 'config' => array(
14: 'classFile' => 'EXT:dbal/handlers/class.tx_dbal_handler_xmldb.php',
15: 'class' => 'tx_dbal_handler_xmldb',
16: 'tableFiles' => array(
17: 'tt_guest' => 'fileadmin/tt_guest.xml',
18: 'sys_note' => 'fileadmin/sys_note.xml',
19: )
20: )
21: ),
22: );
23:
24: $TYPO3_CONF_VARS['EXTCONF']['dbal']['table2handlerKeys'] = array (
25: 'tt_guest' => 'xmlDB',
26: 'sys_note' => 'xmlDB',
27: );
In this example the handler key “xmlDB” sets up a userdefined handler; basically a PHP class with certain functions for INSERT / UPDATE / DELETE and SELECT operations and data-to-disc I/O. In this case it is just an example using the class “tx_dbal_handler_xmldb” which is shipped with this extensions. Configuration might be different since that class (at time of writing) is not finished.
Anyways, the point is that this userdefined, PHP written handler will simulate an SQL server and allow to insert, select, update and delete records which is actually stored in some XML files and not real database tables!
This goes to show the possibilities, right... :-)
Notice on joins and tables separated into different databases
If you chose to configure that some tables like “sys_note” and “tt_guest” will go into other databases as the example shows above you will have to make sure they are never joined with any tables from other databases. If they are, you will face a fatal error from the DBAL; logically you cannot join tables across database systems!
Contains mapping of tablename and fields in a table. Notice that entering any configuration for a table might affect performance since translation is needed before results are returned or queries executed.
Mapping is totally transparent for applications inside TYPO3 and mapping is independent of handler type - the translation goes on between these two spheres.
Mapping can work as a work-around for reserved field- or table names.
Key | Datatype: | Description: |
|---|---|---|
mapTableName | string | Real, physical tablename for the table |
mapFieldNames[fieldname] | string | Real, physical fieldname in the table. |
Example
$TYPO3_CONF_VARS['EXTCONF']['dbal']['mapping'] = array (
'sys_note' => array (
'mapTableName' => 'SysNoteTable',
'mapFieldNames' => array (
'uid' => 'uid999',
'pid' => 'pid999',
'deleted' => 'deleted999',
'tstamp' => 'tstamp999',
'crdate' => 'crdate999',
'cruser' => 'cruser999',
'author' => 'author999',
'email' => 'email999',
'subject' => 'subject999',
'message' => 'message999',
'personal' => 'personal999',
'category' => 'category999'
)
),
'_tt_content' => array (
'mapTableName' => 'tt_content999',
'mapFieldNames' => array (
'bodytext' => 'bodytext999',
'header' => 'header999',
'image' => 'image999',
'pid' => 'pid999',
'sorting' => 'sorting999',
)
)
);
In this example two classic TYPO3 tables have been mapped; the “sys_note” table (from the “sys_note” extension) and the “tt_content” table (Content Elements).
According to this mapping example the sys_note table in the database (or whatever data source) is actually named “SysNoteTable” and all fields are actually named differently; with “...999” after (this is just an example).
When you try to make a look up in the sys_note like “SELECT uid FROM sys_note WHERE uid=123” then this is transformed into “SELECT uid999 FROM SysNoteTable WHERE uid999=123” before executed. And the result row which will be array('uid999' => 123) will be transformed back to array('uid' => 123) before you receive it inside of TYPO3.
Debugging options
Key | Datatype: | Description: |
|---|---|---|
enabled | boolean | If set, TYPO3 will log every SQL execution in the tx_dbal_debuglog table. This option must be set for the other options below to work. You can view the log from the backend; There is a DBAL module in the Tools main module. |
printErrors | boolean | If set, SQL errors will be debug()'ed to browser after any SQL execution. |
EXPLAIN | boolean | Will log the result of a EXPLAIN SELECT... in case of select-queries. Can help you to benchmark the performance of you indices in the database. |
parseQuery | boolean | Will parse all possible parts of the SQL queries, compile them again and match the results. If the parsed and recompiled queries did not match they will enter the log table and can subsequently be addressed. This will help you to spot “TYPO3 incompatible SQL” (as defined by the core parser of “t3lib_sqlengine”). |
joinTables | boolean | Will log every SELECT query performed with a table join - necessary to make sure that all tables that may be joined in TYPO3 is also handled by the same handlerKey (which is required for obvious reasons!) |
Example
This enables all debug options:
$TYPO3_CONF_VARS['EXTCONF']['dbal']['debugOptions'] = array(
'enabled' => TRUE, // Generally, enable debugging.
'printErrors' => TRUE, // Enable output of SQL errors after query executions.
'EXPLAIN' => 1, // EXPLAIN SELECT ...(Only on default handler)
'parseQuery' => 1, // Parsing queries, testing parsability (All queries)
'joinTables' => 1
);
Depending on the database you use, the meaning of the host/username/password settings may slightly differ. This the case for Oracle, and maybe other RDBMS as well.
RDBMS | Host | Username | Password | DB Name |
|---|---|---|---|---|
MySQL | DB server | Username | Password | Database name |
PostgreSQL | DB server | Username | Password | Database name |
Oracle | DB server | Username | Password | SID Must be entered in localconf.php manually! |
Firebird | DB server | Username | Password | Full path to the database file, e.g. /tmp/testfb.fdb |
MS SQL Server (using ODBC) | ODBC DNS | Username | Password | Set to some dummy string! |
If your RDBMS is not shown in the list, try with the usual meaning of those parameters first, if that doesn't work, but you figure out how to connect, then please let us know, so we can update this document.
