| osC_Database - Database Class | Last Update: 7th April, 2005
Article ID: 1 |
- Introduction
- Examples
- Class API - osC_Database
- Class API - osC_Database_Result
Introduction
The introduction of the database class in osCommerce 2.2 Milestone 3 is a start in abstracting database queries from the core codebase to make it easy to adapt to any database server.
As database independency is not a feature of the 2.2 release, databases other than MySQL will be supported out-of-the-box after the 2.2 release has been made. This allows us to focus on optimizing the codebase for MySQL databases while at the same time providing a framework that will greatly benefit database independency in the future.
The current framework provides powerful features such as debugging queries at a global scale or on a per query basis, and such as being able to cache any query made for performance improvements, which can be controlled by calling class methods with just one line of code.
The framework is split into 2 static classes and 1 dynamic class. The 2 static classes are the main osC_Database class which handles the connection to the database, and the osC_Database_Result class which handles the raw query and the results of the query.
The dynamic class is an extension to the osC_Database class which calls native PHP database functions, ie, mysql_*() for MySQL, sybase_*() for Sybase, etc.
The osC_Database_Result class cleans the actual SQL query in the PHP source code by replacing the need of calling variables within a string with a system of binding keywords a certain value (similar to a template engine). The method of binding values strengthens the Privacy and Security implementation by processing database input data (queries) and output data (results) through special-type functions to make sure the data at hand is how it is meant to be.
Examples
Connecting to the database:
<?php
$osC_Database = osC_Database::connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
$osC_Database->selectDatabase(DB_DATABASE);
?>
Executing a simple query which would return no results:
<?php
$osC_Database->simpleQuery('update table set field_1 = value where field_2 = id');
?>
Executing a query which would return no results and which would contain variables:
<?php
$Qupdate = $osC_Database->query('update table set field_1 = :field_1 where field_2 = :field_2');
$Qupdate->bindValue(':field_1', value);
$Qupdate->bindValue(':field_2', id);
$Qupdate->execute();
?>
Executing a query which would return a result set:
<?php
$Qselect = $osC_Database->query('select field_1, field_2 from table where field_3 = :field_3');
$Qselect->bindValue(':field_3', id);
$Qselect->execute();
while ($Qselect->next()) {
echo '<p>Field 1 = ' . $Qselect->value('field_1') . '<br>' .
'Field 2 = ' . $Qselect->value('field_2') . '</p>';
}
$Qselect->freeResult();
?>
Executing a query within a query:
<?php
$Qselect = $osC_Database->query('select field_1 from table_1 where field_2 = :field_2');
$Qselect->bindValue(':field_2', id);
$Qselect->execute();
while ($Qselect->next()) {
$Qupdate = $osC_Database->query('update table_2 set field_1 = :field_1 where field_2 = :field_2');
$Qupdate->bindValue(':field_1', 'value');
$Qupdate->bindValue(':field_2', $Qselect->value('field_1'));
$Qupdate->execute();
}
$Qselect->freeResult();
?>
Debugging a query:
<?php
$Qselect = $osC_Database->query('select field_1 from table_1 where field_2 = :field_2');
$Qselect->bindValue(':field_2', id);
$Qselect->setDebug(true);
$Qselect->execute();
?>
Debugging all queries:
<?php
$osC_Database = osC_Database::connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
$osC_Database->selectDatabase(DB_DATABASE);
$osC_Database->setDebug(true);
?>
Executing a query and caching the resultset for 60 minutes:
<?php
$Qselect = $osC_Database->query('select field_1, field_2 from table where field_3 = :field_3');
$Qselect->bindValue(':field_3', id);
$Qselect->setCache('selectTable', 60);
$Qselect->execute();
while ($Qselect->next()) {
echo '<p>Field 1 = ' . $Qselect->value('field_1') . '<br>' .
'Field 2 = ' . $Qselect->value('field_2') . '</p>';
}
$Qselect->freeResult();
?>
When caching a query result set, it is important to call the freeResult() method so that the data is written to the cache file.
Class API - osC_Database
- &connect($server, $username, $password, $type = 'mysql')
- setConnected($boolean)
- isConnected()
- &query($query)
- setError($error, $error_number = '')
- isError()
- getError()
- setErrorReporting($boolean)
- setDebug($boolean)
- importSQL($sql_file, $database, $table_prefix = -1)
- hasCreatePermission($database)
- numberOfQueries()
- timeOfQueries()
- getMicroTime()
&connect($server, $username, $password, $type = 'mysql')
Returns an instance of a database server specific module. Database modules are available in the classes/database/ directory, with the mysql module used by default.
All database server specific modules are classes that extend the main osC_Database class.
setConnected($boolean)
Sets whether a successfull connection to the database server was made, by giving the $is_connected variable the value of $boolean.
isConnected()
Returns the value of $is_connected, to see if a successfull connection to the database server was made. (true/false)
&query($query)
Returns an instance of the osC_Database_Result class.
setError($error, $error_number = '')
If $error_reporting is enabled, sets the error message and error number in the messageStack.
isError()
Returns whether an error has been set or not. (true/false)
getError()
Returns the error message, and error number if an error number has been set.
setErrorReporting($boolean)
Sets $error_reporting to the value of $boolean. (true/false)
setDebug($boolean)
Sets $debug the value of $boolean. (true/false)
Enabling debug at this level enables debugging for all queries made.
Debugging can be enabled on a per query basis via the osC_Database_Result instance.
importSQL($sql_file, $database, $table_prefix = -1)
Execute queries in an external file.
If $table_prefix is set, all table name instances of "osc_" will be replaced with the $table_prefix value.
hasCreatePermission($database)
Creates a database, if $database does not exist, and a temporary table inside the database to see if the user connected to the database server has sufficient access rights.
The temporary table is dropped; the database is only dropped if it has been created.
numberOfQueries()
Returns the $number_of_queries value.
timeOfQueries()
Returns the $time_of_queries value.
getMicroTime()
Get the current microtime that is used to time the execution of queries.
Class API - osC_Database_Result
- osC_Database_Result(&$db_class)
- setQuery($query)
- setDebug($boolean)
- valueMixed($column, $type = 'string')
- value($column)
- valueProtected($column)
- valueInt($column)
- valueDecimal($column)
- bindValueMixed($place_holder, $value, $type = 'string')
- bindReplace($place_holder, $value)
- bindValue($place_holder, $value)
- bindInt($place_holder, $value)
- bindRaw($place_holder, $value)
- next()
- freeResult()
- numberOfRows()
- execute()
- executeRandom()
- executeRandomMulti()
- setCache($key, $expire = 0)
osC_Database_Result(&$db_class)
Class Constructor
Add a reference to the main instance of the osC_Database class.
setQuery($query)
Set the query to use for processing.
setDebug($boolean)
Set debugging on a per query basis. (true/false)
valueMixed($column, $type = 'string')
Returns a value from the result set that has been parsed depending on the value of the $type parameter.
This is an internal/private class method which value(), valueProtected(), valueInt(), and valueDecimal() use.
value($column)
Returns a string value from the result set.
valueProtected($column)
Returns a string value from the result set that has been parsed with tep_output_string_protected().
valueInt($column)
Returns an integer value from the result set.
valueDecimal($column)
Returns a decimal value from the result set.
bindValueMixed($place_holder, $value, $type = 'string')
Prepares to replace a keyword in the query with a value that has been parsed depending on the value of the $type parameter.
This is an internal/private class method which bindValue(), bindInt(), and bindRaw() use.
bindReplace($place_holder, $value)
Replaces the first found place holder keyword in the query with a value.
bindValue($place_holder, $value)
Escapes special characters in the value to be safely used in the query.
bindInt($place_holder, $value)
Makes the value an integer type.
bindRaw($place_holder, $value)
Returns the value without any parsing.
next()
Increment the result set pointer to return the next set of results.
freeResult()
Frees the resources used for the query, and nullifies the osC_Database_Result class instance.
If caching is enabled, write the value to the cache file.
numberOfRows()
Returns the number of rows of the result set.
execute()
Executes the query.
executeRandom()
Returns a single row from the result set randomly.
executeRandomMulti()
Returns a pointer to a random row from the result set.
setCache($key, $expire = 0)
Cache the query with the key value that is to be used for the cache filename, and expires the cache with the $expire value in minutes.
If the expire value is 0, the cache will always be used if the cached file exists.
