Provides a common API for different databases - will automatically use any installed extension
This class is implemented to use the UTF-8 character encoding. Please see http://flourishlib.com/docs/UTF-8 for more information.
The following databases are supported:
The class will automatically use the first of the following extensions it finds:
The odbc and pdo_odbc extensions are not supported due to character encoding and stability issues on Windows, and functionality on non-Windows operating systems.
1.0.0b41 | Fixed an array to string conversion notice 9/21/12 |
---|---|
1.0.0b40 | Fixed a bug with notices being triggered when failing to connect to a SQLite database 6/20/11 |
1.0.0b39 | Fixed a bug with detecting some MySQL database version numbers 5/24/11 |
1.0.0b38 | Backwards Compatibility Break - callbacks registered to the extracted hook via registerHookCallback() no longer receive the $strings parameter, instead all strings are added into the $values parameter - added getVersion(), fixed a bug with SQLite messaging, fixed a bug with __destruct(), improved handling of transactional queries, added close(), enhanced class to throw four different exceptions for different connection errors, silenced PHP warnings upon connection error 5/9/11 |
1.0.0b37 | Fixed usage of the mysqli extension to only call mysqli_set_charset() if it exists 3/4/11 |
1.0.0b36 | Updated escape() and methods that use escape() to handle float values that don't contain a digit before or after the . 2/1/11 |
1.0.0b35 | Updated the class to replace LIMIT and OFFSET value placeholders in the SQL with their values before translating since most databases that translate LIMIT statements need to move or add values together 1/11/11 |
1.0.0b34 | Fixed a bug with creating translated prepared statements 1/9/11 |
1.0.0b33 | Added code to explicitly set the connection encoding for the mysql and mysqli extensions since some PHP installs don't see to fully respect SET NAMES 12/6/10 |
1.0.0b32 | Fixed handling auto-incrementing values for Oracle when the trigger was on INSERT OR UPDATE instead of just INSERT 12/4/10 |
1.0.0b31 | Fixed handling auto-incrementing values for MySQL when the INTO keyword is left out of an INSERT statement 11/4/10 |
1.0.0b30 | Fixed the pgsql, mssql and mysql extensions to force a new connection instead of reusing an existing one 8/17/10 |
1.0.0b29 | Backwards Compatibility Break - removed enableSlowQueryWarnings(), added ability to replicate via registerHookCallback() 8/10/10 |
1.0.0b28 | Backwards Compatibility Break - removed ODBC support. Added support for the pdo_ibm extension. 7/31/10 |
1.0.0b27 | Fixed a bug with running multiple copies of a SQL statement with string values through a single translatedQuery() call 7/14/10 |
1.0.0b26 | Updated the class to use new fCore functionality 7/5/10 |
1.0.0b25 | Added IBM DB2 support 4/13/10 |
1.0.0b24 | Fixed an auto-incrementing transaction bug with Oracle and debugging issues with all databases 3/17/10 |
1.0.0b23 | Resolved another bug with capturing auto-incrementing values for PostgreSQL and Oracle 3/15/10 |
1.0.0b22 | Changed clearCache() to also clear the cache on the fSQLTranslation 3/9/10 |
1.0.0b21 | Added execute() for result-less SQL queries, prepare() and translatedPrepare() to create fStatement objects for prepared statements, support for prepared statements in query() and unbufferedQuery(), fixed default caching key for enableCaching() 3/2/10 |
1.0.0b20 | Added a parameter to enableCaching() to provide a key token that will allow cached values to be shared between multiple databases with the same schema 10/28/09 |
1.0.0b19 | Added support for escaping identifiers (column and table names) to escape(), added support for database schemas, rewrote internal SQL string spliting 10/22/09 |
1.0.0b18 | Updated the class for the new fResult and fUnbufferedResult APIs, fixed unescape() to not touch NULLs 8/12/09 |
1.0.0b17 | Added the ability to pass an array of all values as a single parameter to escape() instead of one value per parameter 8/11/09 |
1.0.0b16 | Fixed PostgreSQL and Oracle from trying to get auto-incrementing values on inserts when explicit values were given 8/6/09 |
1.0.0b15 | Fixed a bug where auto-incremented values would not be detected when table names were quoted 7/15/09 |
1.0.0b14 | Changed determineExtension() and determineCharacterSet() to be protected instead of private 7/8/09 |
1.0.0b13 | Updated escape() to accept arrays of values for insertion into full SQL strings 7/6/09 |
1.0.0b12 | Updates to unescape() to improve performance 6/15/09 |
1.0.0b11 | Changed replacement values in preg_replace() calls to be properly escaped 6/11/09 |
1.0.0b10 | Changed date/time/timestamp escaping from strtotime() to fDate/fTime/fTimestamp for better localization support 6/1/09 |
1.0.0b9 | Fixed a bug with escape() where floats that start with a . were encoded as NULL 5/9/09 |
1.0.0b8 | Added Oracle support, change PostgreSQL code to no longer cause lastval() warnings, added support for arrays of values to escape() 5/3/09 |
1.0.0b7 | Updated for new fCore API 2/16/09 |
1.0.0b6 | Fixed a bug with executing transaction queries when using the mysqli extension 2/12/09 |
1.0.0b5 | Changed @ error suppression operator to error_reporting() calls 1/26/09 |
1.0.0b4 | Added a few error suppression operators back in so that developers don't get errors and exceptions 1/14/09 |
1.0.0b3 | Removed some unnecessary error suppresion operators 12/11/08 |
1.0.0b2 | Fixed a bug with PostgreSQL when using the PDO extension and executing an INSERT statement 12/11/08 |
1.0.0b | The initial implementation 9/25/07 |
The extension to use for the database specified
Options include:
string
A cache of database-specific code
array
Composes text using fText if loaded
string compose( string $message, mixed $component [, ... ] )
string | $message | The message to compose |
mixed | $component [, ... ] | A string or number to insert into the message |
The composed and possible translated message
Configures the connection to a database - connection is not made until the first query is executed
Passing NULL to any parameter other than $type and $database will cause the default value to be used.
fDatabase __construct( string $type, string $database, string $username=NULL, string $password=NULL, string $host=NULL, integer $port=NULL, integer $timeout=NULL )
string | $type | The type of the database: 'db2', 'mssql', 'mysql', 'oracle', 'postgresql', 'sqlite' |
string | $database | Name of the database. If SQLite the path to the database file. |
string | $username | Database username - not used for SQLite |
string | $password | The password for the username specified - not used for SQLite |
string | $host | Database server host or IP, defaults to localhost - not used for SQLite. MySQL socket connection can be made by entering 'sock:' followed by the socket path. PostgreSQL socket connection can be made by passing just 'sock:'. |
integer | $port | The port to connect to, defaults to the standard port for the database type specified - not used for SQLite |
integer | $timeout | The number of seconds to timeout after if a connection can not be made - not used for SQLite |
Please note: this method is public, however it is primarily intended for internal use by Flourish and will normally not be useful in site/application code
Closes the open database connection
void __destruct( )
Please note: this method is public, however it is primarily intended for internal use by Flourish and will normally not be useful in site/application code
All requests that hit this method should be requests for callbacks
callback __get( string $method )
string | $method | The method to create a callback for |
The callback for the method requested
Clears all of the schema info out of the object and, if set, the fCache object
void clearCache( )
Closes the database connection
void close( )
Connects to the database specified, if no connection exists
This method is only intended to force a connection, all operations that require a database connection will automatically call this method.
void connect( )
Determines the character set of a SQL Server database
void determineCharacterSet( )
Figures out which extension to use for the database type selected
void determineExtension( )
Sets the schema info to be cached to the fCache object specified
void enableCaching( fCache $cache, string $key_token=NULL )
Sets if debug messages should be shown
void enableDebugging( boolean $flag )
boolean | $flag | If debugging messages should be shown |
Escapes a value for insertion into SQL
The valid data types are:
In addition to being able to specify the data type, you can also pass in an SQL statement with data type placeholders in the following form:
Depending on what $sql_or_type and $value are, the output will be slightly different. If $sql_or_type is a data type or a single placeholder and $value is:
If $sql_or_type is a SQL string and $value is:
If $sql_or_type is a SQL string, it is also possible to pass an array of all values as a single parameter instead of one value per parameter. An example would look like the following:
$db->escape(
"SELECT * FROM users WHERE status = %s AND authorization_level = %s",
array('Active', 'Admin')
);
mixed escape( string $sql_or_type, mixed $value [, ... ] )
string | $sql_or_type | This can either be the data type to escape or an SQL string with a data type placeholder - see method description |
mixed | $value [, ... ] | The value to escape - both single values and arrays of values are supported, see method description for details |
The escaped value/SQL or an array of the escaped values
Executes one or more SQL queries without returning any results
void execute( string|fStatement $statement, mixed $value [, ... ] )
string|fStatement | $statement | One or more SQL statements in a string or an fStatement prepared statement |
mixed | $value [, ... ] | The optional value(s) to place into any placeholders in the SQL - see escape() for details |
Returns the database connection resource or object
mixed getConnection( )
The database connection
Gets the name of the database currently connected to
string getDatabase( )
The name of the database currently connected to
Please note: this method is public, however it is primarily intended for internal use by Flourish and will normally not be useful in site/application code
Gets the php extension being used
string getExtension( )
The php extension used for database interaction
Gets the host for this database
string getHost( )
The host
Gets the port for this database
string getPort( )
The port
Gets the fSQLTranslation object used for translated queries
fSQLTranslation getSQLTranslation( )
The SQL translation object
Gets the database type
string getType( )
The database type: 'mssql', 'mysql', 'postgresql' or 'sqlite'
Gets the username for this database
string getUsername( )
The username
Gets the version of the database system
string getVersion( )
The database system version
Please note: this method is public, however it is primarily intended for internal use by Flourish and will normally not be useful in site/application code
Injects an fSQLTranslation object to handle translation
void inject( fSQLTranslation $sql_translation )
fSQLTranslation | $sql_translation | The SQL translation object |
Will indicate if a transaction is currently in progress
boolean isInsideTransaction( )
If a transaction has been started and not yet rolled back or committed
Prepares a single fStatement object to execute prepared statements
Identifier placeholders (%r) are not supported with prepared statements. In addition, multiple values can not be escaped by a placeholder - only a single value can be provided.
fStatement prepare( string $sql )
string | $sql | The SQL to prepare |
A prepared statement object that can be passed to query(), unbufferedQuery() or execute()
Please note: this method is public, however it is primarily intended for internal use by Flourish and will normally not be useful in site/application code
Preprocesses SQL by escaping values, spliting queries, cleaning escaped semicolons, fixing backslashed single quotes and translating
array preprocess( string $sql, array $values, boolean $translate, array &$rollback_queries=NULL )
string | $sql | The SQL to process |
array | $values | Literal values to escape into the SQL |
boolean | $translate | If the SQL should be translated |
array | &$rollback_queries | MySQL doesn't allow transactions around ALTER TABLE statements, and some of those require multiple statements, so this is an array of "undo" SQL statements |
The split out SQL queries, queries that have been translated will have a string key of a number, : and the original SQL, non-translated SQL will have a numeric key
Executes one or more SQL queries and returns the result(s)
fResult|array query( string|fStatement $statement, mixed $value [, ... ] )
string|fStatement | $statement | One or more SQL statements in a string or a single fStatement prepared statement |
mixed | $value [, ... ] | The optional value(s) to place into any placeholders in the SQL - see escape() for details |
The fResult object(s) for the query
Registers a callback for one of the various query hooks - multiple callbacks can be registered for each hook
The following hooks are available:
Methods for the 'unmodified' hook should have the following signature:
Methods for the 'extracted' hook should have the following signature:
The extracted hook is the best place to modify the SQL since there is no risk of breaking string literals. Please note that there may be empty strings ('') present in the SQL since some databases treat those as NULL.
Methods for the 'run' hook should have the following signature:
void registerHookCallback( string $hook, callback $callback )
string | $hook | The hook to register for |
callback | $callback | The callback to register - see the method description for details about the method signature |
Translates one or more SQL statements using fSQLTranslation and executes them without returning any results
void translatedExecute( string $sql, mixed $value [, ... ] )
string | $sql | One or more SQL statements |
mixed | $value [, ... ] | The optional value(s) to place into any placeholders in the SQL - see escape() for details |
Translates a SQL statement and creates an fStatement object from it
Identifier placeholders (%r) are not supported with prepared statements. In addition, multiple values can not be escaped by a placeholder - only a single value can be provided.
fStatement translatedPrepare( string $sql )
string | $sql | The SQL to prepare |
A prepared statement object that can be passed to query(), unbufferedQuery() or execute()
Translates one or more SQL statements using fSQLTranslation and executes them
fResult|array translatedQuery( string $sql, mixed $value [, ... ] )
string | $sql | One or more SQL statements |
mixed | $value [, ... ] | The optional value(s) to place into any placeholders in the SQL - see escape() for details |
The fResult object(s) for the query
Executes a single SQL statement in unbuffered mode. This is optimal for
large results sets since it does not load the whole result set into memory first. The gotcha is that only one unbuffered result can exist at one time. If another unbuffered query is executed, the old result will be deleted.
fUnbufferedResult unbufferedQuery( string|fStatement $statement, mixed $value [, ... ] )
string|fStatement | $statement | A single SQL statement |
mixed | $value [, ... ] | The optional value(s) to place into any placeholders in the SQL - see escape() for details |
The result object for the unbuffered query
Translates the SQL statement using fSQLTranslation and then executes it
in unbuffered mode. This is optimal for large results sets since it does not load the whole result set into memory first. The gotcha is that only one unbuffered result can exist at one time. If another unbuffered query is executed, the old result will be deleted.
fUnbufferedResult unbufferedTranslatedQuery( string $sql, mixed $value [, ... ] )
string | $sql | A single SQL statement |
mixed | $value [, ... ] | The optional value(s) to place into any placeholders in the SQL - see escape() for details |
The result object for the unbuffered query
Unescapes a value coming out of a database based on its data type
The valid data types are:
mixed unescape( string $data_type, mixed $value )
string | $data_type | The data type being unescaped - see method description for valid values |
mixed | $value | The value or array of values to unescape |
The unescaped value