Database Connections and Queries

Database Connections and Queries

Connecting to and communication with databases is handled through the PHP Data Objects (PDO) extension, a lightweight and consistent interface for accessing databases in PHP. Each instance of the PDO class opens a new connection to a database server and provides a consistent API for communication regardless of the system used (MySQL, PostgreSQL, MS SQL Server, SQLite, etc.).

PDO not only manages database connections, it also supports transactions, prepared statements, stored procedures, and error handling. Our database class extends the base PHP PDO class to also provide support for caching query result sets.

Connecting to a Database Server

Connecting to a database server is performed through the osCommerce\OM\Core\PDO::initialize() class method as follows:

<?php
  use osCommerce\OM\Core\PDO;

  $PDO = PDO::initialize();
?>

The initialize class method accepts the following parameters:

Parameter	Description	Default Value
$server	The database server address name, IP address, or socket.	db_server configuration value
$username	The user to connect as.	db_server_username configuration value
$password	The user password to connect with.	db_server_password configuration value
$database	The name of the database to connect to.	db_database configuration value
$port	The port number of the database server to connect to.	db_server_port configuration value
$driver	The database driver to use.	db_driver configuration value
$driver_options	Database driver specific options to use on the connection.	ATTR_ERRMODE = ERRMODE_WARNING ATTR_DEFAULT_FETCH_MODE = FETCH_ASSOC ATTR_STATEMENT_CLASS = osCommerce\OM\Core\PDOStatement

Database Drivers

Supported database drivers are located in osCommerce/OM/Core/PDO/ and are used to connect to the database server.

Supported Drivers
MySQL\Standard (MyISAM)
MySQL\V5 (InnoDB)
SQLite3

Drivers in Development
Microsoft\SqlServer
PostgreSQL

Abstract Queries

SQL queries are abstracted into modules to not only support various database systems, but to also optimize each query with the unique features of the database system to gain the best performance possible. This allows us to take advantage of stored procedures, views, and database system specific optimizations to perform queries as fast and efficiently as the database system allows.

Abstract queries are called through the OSCOM::callDB() class method with the following parameters:

Parameter	Description	Default Value
$procedure	The abstracted query module to call.
$data	Parameters to pass to the procedure.
$type	The level the procedure is located in.	Application

The $type parameter informs the method where the procedure is located in the namespace level and parses a shortened namespace version for the Core, Site, and Application levels. A shortened namespace version is used to avoid the necessity of passing the full namespace level as a function parameter.

The following examples show how $procedure and $type are internally used to build the full namespace level of the procedure:

$procedure	$type	Full Namespace
GetLanguages	Core	osCommerce\OM\Core\SQL\[Driver]\GetLanguages
Shop\GetConfiguration	Site	osCommerce\OM\Core\Site\Shop\SQL\[Driver]\GetConfiguration
Admin\Countries\Get	Application	osCommerce\OM\Core\Site\Admin\Application\Countries\SQL\[Driver]\Get

Abstract queries are stored in the following directories:

Level	Directory
Core	osCommerce/OM/Core/SQL/[Driver]/
Site	osCommerce/OM/Core/Site/[Site]/SQL/[Driver]/
Application	osCommerce/OM/Core/Site/[Site]/Application/[Application]/SQL/[Driver]/

Executing Abstract Queries

The following examples show how abstract queries are executed:

Retrieve and set configuration parameters

<?php
  use osCommerce\OM\Core\OSCOM;

  foreach ( OSCOM::callDB('Shop\GetConfiguration', null, 'Site') as $param ) {
	define($param['cfgkey'], $param['cfgvalue']);
  }
?>

Return a country entry with the ID value of 1

<?php
  use osCommerce\OM\Core\OSCOM;

  $data = array('id' => '1');

  $result = OSCOM::callDB('Admin\Countries\Get', $data); // uses the default $type parameter value of Application

  echo $result['countries_name'];
?>

SQL Queries

SQL queries can be performed through an instance of the osCommerce\OM\Core\PDO class.

The following query methods are available:

Method	Description
exec()	Execute the query and return the number of affected rows.
query()	Execute the query and return a result set.
prepare()	Securely pass parameters to and execute the query and return a result set.

exec()

exec() is used to execute simple sql queries where no parameters are passed to the query and no returned result set is needed.

The following example shows how exec() is used:

<?php
  use osCommerce\OM\Core\Registry;

  $OSCOM_PDO = Registry::get('PDO');

  $affected_rows = $OSCOM_PDO->exec('delete from :table_customers where customers_id = 1');

  if ( $affected_rows === 1 ) {
	echo 'Customer record deleted.';
  } else {
	echo 'Customer record not deleted.';
  }
?>

query()

query() is used to execute simple sql queries where no parameters are passed and a returned result set is needed.

The following example shows how query() is used:

<?php
  use osCommerce\OM\Core\Registry;

  $OSCOM_PDO = Registry::get('PDO');

  $Qcustomer = $OSCOM_PDO->query('select customers_firstname, customers_lastname from :table_customers where customers_id = 1');
  $Qcustomer->execute();

  echo 'Welcome back ' . $Qcustomer->valueProtected('customers_firstname') . ' ' . $Qcustomer->valueProtected('customers_lastname') . '!';
?>

prepare()

prepare() is used to execute simple sql queries where parameters are passed to the query and a returned result set is needed.

The following example shows how prepare() is used:

<?php
  use osCommerce\OM\Core\Registry;

  $OSCOM_PDO = Registry::get('PDO');

  $customer_id = $_GET['cid'];

  $Qcustomer = $OSCOM_PDO->prepare('select customers_firstname, customers_lastname from :table_customers where customers_id = :customers_id');
  $Qcustomer->bindInt(':customers_id', $customer_id);
  $Qcustomer->execute();

  echo 'Welcome back ' . $Qcustomer->valueProtected('customers_firstname') . ' ' . $Qcustomer->valueProtected('customers_lastname') . '!';
?>

Parameters can be securely passed to the sql query with the following methods:

Method	Description
bindValue()	Pass a string value to the query.
bindInt()	Pass an integer value to the query.
bindBool()	Pass a boolean value to the query.
bindNull()	Pass a null value to the query.

Caching Result Sets

Result sets can be cached for a defined period to greatly improve the performance of the web and database server.

SQL queries can be cached by using the setCache() method as follows:

<?php
  use osCommerce\OM\Core\Registry;

  $OSCOM_PDO = Registry::get('PDO');

  $Qcfg = $OSCOM_PDO->query('select configuration_key as cfgkey, configuration_value cfgvalue from :table_configuration');
  $Qcfg->setCache('configuration', 60);
  $Qcfg->execute();

  foreach ( $Qcfg->fetch() as $param ) {
	define($param['cfgkey'], $param['cfgvalue']);
  }
?>

A key value is defined to identify the cached result set and a period in minutes is defined to keep using the cached result before it is refreshed. If no period is defined, the cached result set is used until it has been deleted manually.

Cached result sets are stored in the osCommerce/OM/Work/Cache directory.

Transactions

Transactions allow multiple queries to be grouped where actual changes to the database are performed after the last query is executed and no errors previously occurred, or cancels the changes from the first query if an error has occurred. This allows multiple database records to be changed in a safe manner.

Transactions are controlled with the following methods:

Method	Description
beginTransaction()	Start a transaction.
commit()	Save the database changes.
rollBack()	Cancel the database changes.

The following is an example transaction:

<?php
  use osCommerce\OM\Core\Registry;

  $OSCOM_PDO = Registry::get('PDO');

  try {
	$OSCOM_PDO->beginTransaction();

	$data = array('parent_id' => 1,
						  'language_id' => 1,
						  'name' => 'Electronics');

	$Qcat = $OSCOM_PDO->prepare('insert into :table_categories (parent_id, date_added) values (:parent_id, now())');
	$Qcat->bindInt(':parent_id', $data['parent_id']);
	$Qcat->execute();

	$category_id = $OSCOM_PDO->lastInsertId();

	$Qcd = $OSCOM_PDO->prepare('insert into :table_categories_description (categories_id, language_id, categories_name) values (:categories_id, :language_id, :categories_name)');
	$Qcd->bindInt(':categories_id', $category_id);
	$Qcd->bindInt(':language_id', $data['language_id']);
	$Qcd->bindValue(':categories_name', $data['name']);
	$Qcd->execute();

	$OSCOM_PDO->commit();
  } catch ( PDOException $e ) { // an error has occurred
	$OSCOM_PDO->rollBack();
  }
?>

Foreign Key Relationships (MySQL MyISAM Compatibility)

An additional optimization we are utilizing is the use of Foreign Key relationships in the database schema structure. This strengthens the relationships between tables where records in multiple tables are linked together and have certain rules applied.

The constraints between a parent and child relationship are:

ON DELETE or ON UPDATE Constraint	Description
RESTRICT	Rejects the delete or update operation for the parent table.
CASCADE	Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table.
SET NULL	Delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.

For an example, a Foreign Key constraint is set on the customers table linking the primary key customer_id field to other tables holding additional customer information. Deleting a customer record with the following sql query:

delete from osc_customers where customers_id = 1;

would not only delete the record from the customers table, but with a CASCADE constraint would also automatically delete the customer records from the linked tables using that one sql query.

This brings an enormous benefit when Add-Ons are installed as the records in any additional tables created can be defined to be automatically managed through Foreign Key restraints.

MySQL MyISAM Compatibility


An example relationship that is defined in the table is:

from_table	to_table	from_field	to_field	on_update	on_delete
address_book	customers	customers_id	customers_id	cascade	cascade

This automatically deletes the customers record from the [prefix]_address_book table when the customer is deleted from the [prefix]_customers table.

The MySQL\Standard driver automatically checks and performs the constraint actions each time an update or delete sql query is executed.