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

Code

/config/core/database/{CONTEXT}/{ENVIRONMENT}_connections.ini

Details on configuration files can be found in the Configuration chapter. The configuration file mentioned above must contain one section for each driver layer:

APF-Konfiguration

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

The debug mode can be used to display the executed statement directly on screen. If you like to monitor the statements executes, you may use the optional parameter $logStatement applied to the executeStatement() and executeTextStatement() method. If set to true, the currently executed statement is appended to a log file. Details on the function can be obtained from the API documentation.

2. Practice

To be able to use the ConnectionManager the component must be imported by

PHP-Code

import('core::database','ConnectionManager');

prior to use. Further, you can create a database driver instance using the following code snippet:

PHP-Code

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

APF-Konfiguration

[MySQL]
DB.Host = "host"
DB.User = "user"
DB.Pass = "pass"
DB.Name = "name"
DB.Type = "MySQLx"
[DB.Port = "3306"]
[DB.Socket = "/tmp/mysql.sock"]
[DB.DebugMode = "true|false"]
[DB.Charset = ""]
[DB.Collation = ""]

The parameters DB.Port and DB.Socket adds the possibility to define the TCP/IP-port respectively the unix-socket for the connection.

The PHP-MySQL-extension supports the definition of TCP/IP-port respectively unix-socket within the hostname-parameter which results in the fact that some people added this information directly in the parameter DB.Host. The PHP-MySQLi-extension makes this impossible because port and socket are independently added parameters during the establishment of a new connection. Because of this fact the parameters DB.Port and DB.Socket were added and the MySQL-workaround shouldn't be used anymore.

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

and the value of DB.Collation is applied to the variables

collation_connection
collation_database

Both params are optional and can be defined alternately. There is no need to specify both params at the same time.

Details on the configuration param values can be taken from the MySQL documentation within chapter Connection Character Sets and Collations (MySQL 5.0).

Afterwards, the instance of the abstraction layer can be created using the following lines of code:

PHP-Code

$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

APF-Konfiguration

[SQLite]
DB.Name = "/path/to/my/database.sqlite"
DB.Type = "SQLite"
DB.DebugMode = "true|false"

Please note, that host, user and password don't have to be configured, due to the fact, that SQLite is an integrated database engine. Creating an instance of the driver class looks like this:

PHP-Code

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

APF-Konfiguration

[MySQLi]
DB.Host = "host"
DB.User = "user"
DB.Pass = "pass"
DB.Name = "name"
DB.Type = "MySQLi"
[DB.Port = "3306"]
[DB.Socket = "/tmp/mysql.sock"]
[DB.DebugMode = "true|false"]
[DB.Charset = ""]
[DB.Collation = ""]

The configuration of port/socket, charset and collation applies the same conditions to as a MySQLx-connection.

Compared to the MySQLx driver the current implementation provides methods to execute statements with bind parameters. These are:

executeTextBindStatement()
executeBindStatement()

Using executeTextBindStatement() you can execute a statement that - analog to executeTextStatement() - that is passed as string. The second param expects the bind parameters.

executeBindStatement() executes a statement that is stored within an SQL file and contains bind parameters. The bind parameter implementation for MySQL expects the order of the applied parameters to be equal to the definition within the statement file. Hence, the method re-orders the parameters to fit them automatically.

Using the executeTextBindStatement() method, you may use the fetchBindResult() method that uses the data similar to fetchData().

The following code box contains samples for using the bind statement feature:

PHP-Code

// 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.sql',
   array('date_from' => '2009-03-20 00:00:00','date_until' => '2010-04-10 00:00:00')
);

Executing the last method, you need to provide the SQL file

Code

/apps/config/my/module/{CONTEXT}/{ENVIRONMENT}_notepad_entries.sql

that contains the following content:

SQL-Statement

SELECT *
FROM notepad
WHERE
   save_date BETWEEN [date_from] AND [date_until]

4.4. PDO Driver

Since version 1.15 it's possible to create a PDO-driver instance. An additional configuration parameter is needed.

APF-Konfiguration

[MySQL]
DB.Host = "host"
DB.User = "user"
DB.Pass = "pass"
DB.Name = "name"
DB.Type = "PDO"
DB.PDO = "MySQL"
[DB.Port = "3306"]
[DB.Socket = "/tmp/mysql.sock"]
[DB.DebugMode = "true|false"]
[DB.Charset = ""]
[DB.Collation = ""]

The parameter DB.Type is used to inform the framework to use the PDOHandler. The additional parameter DB.PDO is used to inform PDO which specific driver should be used.

With the current state (version 1.15) only the PDO_MYSQL-Driver is supported.

To configure ports/sockets, charset and collation use the documentation chapter of MySQLx

A special feature of the PDO-driver is, that the execute*()-methods returns a PDOStatement, therefore it is possible to use the APF-methods or the methods described under PHP.net PDOStatement

The PDOStatement is directly executed with a execute*()-method, so no PDO specific placeholder parameters could be used. To use PDO placeholders in the statement there is a prepareStatement()-method. There is no need to call the execute-method of the PDOStatement object.

In addition to the methods of the MySQLx-driver there are additional methods implemented.

beginTransaction()
commit()
rollBack()
inTransaction()
prepareStatement()

beginTransaction() starts a transaction, this means the autocommit mode is turned off. Changes made to the database are not executed directly. With the methods commit() and rollBack() the changes a executed or dropped.

With commit() all changes within a transaction are executed and the autocommit mode is turned on.

With rollBack() all changes within a transaction are dropped and the autocommit mode is turned on.

inTransaction() checks if a transaction is active or not.

prepareStatement() returns a PDOStatement. PDO placeholders can be within the statement. To execute the statement the execute()-method of the PDOStatement object must be called. The placeholders could be set via bindParam()-method or via an array as first parameter of the execute-method.

Here are some examples of using the PDO-driver:

PHP-Code

// retrieve db connection
$cm = $this->getServiceObject('core::database', 'ConnectionManager');
$conn = $cm->getConnection('PDO');

$statement = $conn->executeTextStatement("SELECT * FROM `ent_user`");

// apf methods
while ($row = $conn->fetchData($statement)) {

}

// or PDO methods
while ($row = $statement->fetch()) {

}


// prepare a statement
$statement = $conn->prepare("SELECT * FROM `ent_user` WHERE `FirstName` = :firstname");

// bind params and execute
$statement->bindParam(':firstname', 'Christian');
$statement->execute();

// or execute with param array
$statement->execute(array(':firstname' => 'Christian'));

5. Statement files

Each implementation of the AbstractDatabaseHandler provides the possibility to handle separate statement files. The benefit of such files is the usage by multiple components. This is especially interesting using the Pager, since the addressed statement can be used by the pager itself and a data layer component.

5.1. Configuration

Due to the fact that statement files are configuration files as well, all information about the configuration mechanism can be applied to them. Technically, the APF brings the StatementConfigurationProvider and the StatementConfiguration as a statement configuration file abstraction. Each database connection implementation shipped with the APF uses these two classes.

By default, the name of the configuration file consists of the environment prefix followed by its name and the file extension. The latter one is ".sql" within the default setup but can be adapted as described under Configuration. Sample:

Code

DEFAULT_load_all_entries_for_category.sql

The storage location equals "normal" configuration files, too. It is concatenated by the namespace and the present context. Given the statement name load_all_entries_for_category.sql and the namespace widgets::articles the fully qualified path is as follows:

Code

/APF/config/widgets/articles/siteone/DEFAULT_load_all_entries_for_category.sql

For this sample, the context of the application has been set to "siteone".

5.2. Usage

After creating a statement file under the appropriate path it can be used by invoking the executeStatement() or executeBindStatement() (only for MySQLi connections) methods on the current connection:

PHP-Code

$conn = $this->getServiceObject('core::database', 'ConnectionManager')
             ->getConnection('datastore');

$params = array('somefield' => 'somevalue');
$result = $conn->executeStatement(
                     'widgets::articles',
                     'load_all_entries_for_category',
                     $params);

// for MySQLi connections
$data = $conn->executeBindStatement(
                     'widgets::articles',
                     'load_all_entries_for_category',
                     $params);

The third argument of each of the methods is designed to take the dynamic parameters that should be passed to the driver on execution time. Within the statement definition, these parameters are noted in brackets. In case you want to pass a dynamic value for the somefield parameter the statement is as follows:

SQL-Statement

SELECT somefield, anotherfield
FROM mytable
WHERE somefield = '[somefield]';

Statements can take any number of dynamic parameters regardless of their position. Further, dynamic parameters can contain simple values or any kind of statement fragments.

A further sample is described under Comment function.

Comments

Do you want to add a comment to the article above, or do you want to post additional hints? So please click here. Comments already posted can be found below.

There are no comments belonging to this article.

ConnectionManager

Comments

QUICKNAVI