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
/APF/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 configuration
[section-name] Host = "" User = "" Pass = "" Name = "" [DebugMode = "true|false"]

The section-name is used to create the driver instance, Host contains the hostname of the database server, User and Pass define the logon credentials and Name defines the name of the database to use. 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 you have to create an instance using the following code snippet:

PHP code
use APF\core\database\ConnectionManager; $cm = &$this->getServiceObject('APF\core\database\ConnectionManager'); $driver = &$cM->getConnection('section-name');

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 or implement the DatabaseConnection interface.

In case you want to implement your custom driver please consider an existing driver implementation as a start.

4. Existing abstraction layers

The Adventure PHP Framework comes with three different abstraction layers:

  • 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.2. SQLite driver

Any SQLite abstraction layer instance can be configured by

APF configuration
[SQLite] Name = "/path/to/my/database.sqlite" Type = "APF\core\database\SQLiteHandler" 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
use APF\core\database\ConnectionManager; $cm = &$this->getServiceObject('APF\core\database\ConnectionManager'); $SQLite = &$cM->getConnection('SQLite');

4.3. MySQLi driver

The MySQLi driver 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 configuration
[MySQLi] Host = "host" User = "user" Pass = "pass" Name = "name" Type = "APF\core\database\MySQLiHandler" [Port = "3306"] [Socket = "/tmp/mysql.sock"] [DebugMode = "true|false"] [Charset = ""] [Collation = ""]

The parameters Port and 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 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 Port and Socket were added and the MySQL-workaround shouldn't be used anymore.

The two parameters Charset and Collation are used to configure the character set and the collation of the MySQL connection. The directive Charset sets the MySQL variables

  • character_set_client
  • character_set_connection
  • character_set_results

and the value of 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.

The following methods allow to execute statements with bind parameters:

  • 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
use APF\core\database\ConnectionManager; $cm = &$this->getServiceObject('APF\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( 'VENDOR\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
/VENDOR/config/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

You may also use a PDO-driver instance. To use it, an additional configuration parameter is needed:

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

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

With the current state only the PDO_MYSQL-Driver is supported.

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

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 MySQLi-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
use APF\core\database\ConnectionManager; $cm = &$this->getServiceObject('APF\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 VENDOR\widgets\articles the fully qualified path is as follows:

Code
/VENDOR/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
use APF\core\database\ConnectionManager; $conn = $this->getServiceObject('APF\core\database\ConnectionManager') ->getConnection('datastore'); $params = array('somefield' => 'somevalue'); $result = $conn->executeStatement( 'VENDOR\widgets\articles', 'load_all_entries_for_category', $params); // for MySQLi connections $data = $conn->executeBindStatement( 'VENDOR\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.

6. Creation of database connections with DI container

Besides the way of creating database connections described in chapter 2 using the ConnectionManager you can also utilize the APF DI container. The following chapters describe creating database connections as services using the DIServiceManager in detail.

6.1. Configuration

In order to create services or objects respectively with the DIServiceManager a configuration is required. A database connection can be defined by a configuration section including all relevant information just as any other service. To create an instance of the MySQLiHandler the following definition can be used:

APF configuration
[mysql-connection] class = "APF\core\database\MySQLiHandler" servicetype = "SINGLETON" setupmethod = "setup" conf.host.method = "setHost" conf.host.value = "localhost" conf.name.method = "setDatabaseName" conf.name.value = "products" conf.user.method = "setUser" conf.user.value = "root" conf.pass.method = "setPass" conf.pass.value = "..." conf.charset.method = "setCharset" conf.charset.value = "utf8" conf.collation.method = "setCollation" conf.collation.value = "utf8_general_ci"
Configuring the services all setter methods of the MySQLiHandler class can be included. If you want to activate the debug mode (details see chapter 1) and change the LogWriter (details see Logger), please add another group for each option. Example:
APF configuration
conf.debug.method = "setDebug" conf.debug.value = "true" conf.log-target.method = "setLogTarget" conf.log-target.value = "..."
Please be aware to initialize the database connection with the setup() method. Otherwise, the instance of MySQLiHandler is created but connection to database is not established. Details on using the setupmethod attribute can be taken from chapter Services.

The way of creating configurations can be applied to each database driver delivered with the APF.

6.2. Usage

The database connection defined in chapter 6.1 can be retrieved from the DIServiceManager for usage within a document controller for instance. For this reason, you may want to use the following code:

PHP code
use APF\core\database\MySQLiHandler; use APF\core\pagecontroller\BaseDocumentController; class DoSomethingController extends BaseDocumentController { public function transformContent() { /* @var $conn MySQLiHandler */ $conn = $this->getDIServiceObject('VENDOR\..\data', 'mysql-connection'); $conn->executeTextStatement('SELECT * FROM ...'); ... } }

The service defined within the previous chapter can not only be used directly but also to initialize other services. Details on that can be taken from chapter 4.4.4 of the DIServiceManager documentation.

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.
« 1   2   »
Entries/Page: | 5 | 10 | 15 | 20 |
1
cialis_online 06.12.2016, 09:36:31
Buying cialis online Online Guide / Buy Cialis Online For Full Customer Satisfaction!
2
cialis_online 06.12.2016, 09:36:20
cialis online For Sale Vancouver: Online Drugstore, Top Quality Medications.
3
online_pharmacy 07.10.2016, 16:15:32
Generic online pharmacy helps to accomplished desire results in sexual activity for ED affected person.
4
online_pharmacy 07.10.2016, 09:17:03
No prescription online pharmacy the coming Euro is generated by writing songs for a.
5
street 04.10.2016, 13:27:44
After all, game street fighter for women almost works similar.
6
street 04.10.2016, 13:27:22
Generic cialis levitra sale tadalafil pharmacy online game street fighter for pill buy tablets soft.
7
cialis 14.09.2016, 11:59:18

Support for multiple attempts generic cialis softtabs with a.
8
cia_lis 14.09.2016, 11:59:05
I like what you guys are up too. Such smart work and reporting! Carry on the excellent works guys Ive incorporated you guys to my blogroll. I think it'll improve the value of my website
9
cialis 14.09.2016, 11:58:48
I like what you guys are up too. Such smart work and reporting! Carry on the excellent works guys Ive incorporated you guys to my blogroll. I think it'll improve the value of my website
10
cialis 14.09.2016, 11:58:36

We are the only one you will need to turn to for generic generic cialis softtabs.