ConnectionManager
The notion of the ConnectionManager is to define the configuration and implementation of a database abstraction and access layer. Ideally, this approach eases switching from one database driver to another.
Despite the fact, that this procedure is described a little bit too idealistic - different databases often have different features - the ConnectionManager itself gives a common understanding of how an application's data layer should communicate with the database abstraction layer.
1. Configuration
The ConnectionManager is a kind of factory to concrete implementations of database driver classes. To load the desired driver the abstraction layer must be configured using the config file
/config/core/database/{CONTEXT}/{ENVIRONMENT}_connections.iniDetails on configuration files can be found in the configuration chapter. The configuration file mentioned above must contain one section for each driver layer:
[sectionname]
DB.Host = ""
DB.User = ""
DB.Pass = ""
DB.Name = ""
[DB.DebugMode = "true|false"]The sectionsname is used to create the driver instance, DB.Host contains the hostname of the database server, DB.User and DB.Pass define the logon credentials and DB.Name defines the name of the database to use. DB.Type is aimed to switch the debug mode on of off.
2. Practice
To be able to use the ConnectionManager the component must be imported byimport('core::database','ConnectionManager');$cM = &$this->__getServiceObject('core::database','ConnectionManager');
$db_driver = &$cM->getConnection('sectionname');3. Extending the database drivers
The class AbstractDatabaseHandler describes the interface of a database driver that can be created using the ConnectionManager. In order to implement another abstraction layer any other driver must inherit from AbstractDatabaseHandler and reside in the core::database namespace.
4. Existing abstraction layers
The adventure php framework comes with two different abstraction layers:- MySQLx: MySQL driver. Based on the mysql_* functions.
- SQLite: SQLite driver. Based on the sqlite_* functions. Needs PHP > 5.0.0!
- MySQLi: MySQLi driver. Based on the mysqli_* functions. Needs PHP > 5.0.0!
4.1. MySQL driver
In order to create an instance of the MySQL abstraction class the following configuration has to be included in the config file mentioned above:
[MySQL]
DB.Host = "host"
DB.User = "user"
DB.Pass = "pass"
DB.Name = "name"
DB.Type = "MySQLx"
[DB.DebugMode = "true|false"]
[DB.Charset = ""]
[DB.Collation = ""]The two parameters DB.Charset and DB.Collation are used to configure the character set and the collation of the MySQL connection. The directive DB.Charset sets the MySQL variables
- character_set_client
- character_set_connection
- character_set_results
- collation_connection
- collation_database
$cM = &$this->__getServiceObject('core::database','ConnectionManager');
$MySQL = &$cM->getConnection('MySQL');Due to performance issues, the connection manager only creates singleton drivers.
4.2. SQLite driver
Any SQLite abstraction layer instance can be configured by[SQLite]
DB.Name = "/path/to/my/database.sqlite"
DB.Type = "SQLite"
DB.DebugMode = "true|false"$cM = &$this->__getServiceObject('core::database','ConnectionManager');
$SQLite = &$cM->getConnection('SQLite');4.3. MySQLi driver
The MySQLi driver is merely identical to the implementation of the MySQLx driver. It supports execution of textual statements and statements stored in sql files.
In order to create an instance of the MySQLi driver, the following configuration is necessary:
[MySQLi]
DB.Host = "host"
DB.User = "user"
DB.Pass = "pass"
DB.Name = "name"
DB.Type = "MySQLi"
[DB.DebugMode = "true|false"]
[DB.Charset = ""]
[DB.Collation = ""]Für die Konfiguration des Zeichensatz und der Collation gelten die selben Bedingungen wie für eine MySQLx-Connection.
Über die Funktion des MySQLx-Treibers hinaus implementiert er Methoden, die es erlauben, Statements mit Bind-Parametern gegen die Datenbank auszuführen. Diese sind:
- executeTextBindStatement()
- executeBindStatement()
Mit executeTextBindStatement() kann ein Statement, das - analog zu executeTextStatement() - als String übergeben wurde gegen die Datenbank ausgeführt werden. Als zweiten Parameter erwartet es die Werte der Bind-Parameter.
Die Methode executeBindStatement() führt ein Statement aus, das in einer SQL-Datei abgelegt wurde und reichert dieses mit den im Aufruf übergebenen Parametern an. Da die Implementierung der Bind-Parameter für MySQL-Datenbanken eine genaue Beachtung der Reihenfolge der Bind-Parameter voraussetzt, wurde in der Implementierung eine automatische Re-Sortierung eingeführt, die dafür sort, dass die Zuordnung der Parameter des Methoden-Aufrufs zur Definition der Parameter in der SQL-Datei passt.
Für die Nutzung der Methode executeTextBindStatement() steht die Funktion fetchBindResult() zur Verfügung. Diese holt analog zu fetchData() die gewünschte Ergebnismenge ab.
Die folgende Code-Box zweigt Anwendungsbeispiele für den Umgang mit Bind-Statements:
// retrieve db connection
$cm = &$this->__getServiceObject('core::database','ConnectionManager');
$conn = $cm->getConnection('MySQLi');
// execute textual statement with bind params
$data = $conn->executeTextBindStatement(
'SELECT * FROM ent_user_2 WHERE FirstName LIKE ?',
array('Christian')
);
// execute statement within an sql file with bind params
$data = $conn->executeBindStatement(
'my::module',
'notepad_entries',
array('date_from' => '2009-03-20 00:00:00','date_until' => '2010-04-10 00:00:00')
);Die Ausführung der letzten Methode setzt voraus, dass eine SQL-Datei unter
/apps/config/my/module/{CONTEXT}/{ENVIRONMENT}_notepad_entries.sqldie übergebenen Parameter in der Form
SELECT *
FROM notepad
WHERE
save_date BETWEEN [date_from] AND [date_until]enthält.
