fDatabase

The fDatabase class abstracts interaction with Microsoft SQL Server (MSSQL), MySQL, PostgreSQL and SQLite databases.

Supported Databases

The fDatabase class allows for interaction with a number of popular relational database management systems. Rather than requiring a specific PHP extension to interact with each of these DBs, Flourish shows its portable nature by automatically detecting and using the installed extension for the database type specified.

Here is a list of the supported DBs and the PHP extensions that are currently supported:

Connecting

As a first step to interacting with a database, a connection needs to be made. This is done by creating a new instance of the fDatabase class. The constructor takes the database type, name, username, password, server and port as parameters. MSSQL, MySQL, Oracle and PostgreSQL databases require all parameters except for the server and port. SQLite databases only require the type and name parameters.

// Connecting to a MSSQL database via an ODBC DSN
$mssql_db  = new fDatabase('mssql', 'dsn:my_database', 'username', 'password');
 
// Connecting to a MSSQL database on localhost running on the default port
$mssql_db  = new fDatabase('mssql', 'my_database', 'username', 'password');
 
// Connecting to a MySQL database on the server example.com
$mysql_db  = new fDatabase('mysql', 'my_database', 'username', 'password', 'example.com');
 
// Connecting to an Oracle database via an ODBC DSN
$oracle_db = new fDatabase('oracle', 'dsn:my_database', 'username', 'password');
 
// Connecting to an Oracle database on localhost
$oracle_db = new fDatabase('oracle', 'my_database', 'username', 'password');
 
// Connecting to a PostgreSQL database on the current server using a non-standard port
$pgsql_db  = new fDatabase('postgresql', 'my_database', 'username', 'password', 'localhost', 1234);
 
// Connection to an SQLite database
$sqlite_db = new fDatabase('sqlite', '/path/to/database/file');

It is possible to connect to a MySQL database using a socket connection by passing sock:/path/to/the/socket as the $server parameter. For a PostgreSQL socket connection, simply pass sock: in the $server parameter.

Queries

Once you have established a databases connection you can start executing queries using the query() method. This method executes a query and returns an instance of the fResult class to access returned rows and get information about the query that was executed.

Please note this method executes queries in a buffered manner. This means that all results are loaded into PHP memory, which can cause performance issues for very large result sets (in the order of 500+ rows). For large result sets, unbuffered queries will generally yield better performance (at the cost of certain other restrictions).

// Execute a SQL query and retrieve all returned rows
$result = $mysql_db->query('SELECT * FROM users LIMIT 5');
foreach ($result as $row) {
    // Access the row
}

For more information about what can be done with a query result, please see the fResult page.

Unbuffered Queries

Unbuffered queries will often have better performance for large results sets, however the exact details can vary from database driver to driver. Many database drivers will only allow a single unbuffered query to be active at any point. If another database query is called, it will either cause the previous call to close or will fail itself.

Calling unbufferedQuery() will return an instance of fUnbufferedResult. This is similar to an fResult object, however does not have the ability to retrieve the number of returned rows, or seek to different rows in the set.

Please note that some database/extension combinations do not provide unbuffered query functionality, and thus will not necessarily gain the same performance benefits as others. The following database extensions are known to have unbuffered benefits: pdo_*, mysql, mysqli, sqlite.

$result = $mysql_db->unbufferedQuery('SELECT * FROM users');
foreach ($result as $row) {
    // Don't execute another query in here or the original result will be destroyed
}

Non-Queries

In situations where no result is required to be iterated over, such as an UPDATE statement, the execute() method can be used. This method takes all of the same parameters as query(), however it does not return an fResult object.

$db->execute("UPDATE users SET name = %s WHERE name = %s", 'Will', 'William');

Like the query() and unbufferedQuery() methods, an fSQLException will be thrown if a SQL error occurs.

Cross-Database SQL

One of the features fDatabase provides for portable code is the ability to run SQL queries that work across all supported databases. The Flourish SQL page includes a list of all supported SQL syntax and what it is translated into for each different database engine.

The two methods translatedQuery() and unbufferedTranslatedQuery() work exactly the same as query() and unbufferedQuery() except that the SQL statements are translated from Flourish SQL into the SQL dialect supported by the current database.

For instance, if you are familiar with MSSQL databases, you will know that the LIMIT syntax is not valid, but instead it required you to use the TOP keyword. The following PHP:

$result = $mssql_db->translatedQuery("SELECT * FROM users LIMIT 5");

Would actually be executed as the following SQL:

SELECT TOP 5 * FROM users

Transactions

If you aren't familiar with what database transactions are, please read the Wikipedia page Database transaction first.

The fDatabase class by default executes all SQL queries immediately, in what is referred to as auto-commit mode. To perform one or more queries in a transaction that can be rolled back or commited, basic SQL queries are used instead of method calls.

fDatabase does minimal translation of transaction SQL queries since there isn't a single consistent set of commands for all supported databases. Each of these queries will work with any of the supported databases.

// Start a transaction
$db->query('BEGIN');
$db->query('START TRANSACTION');
 
// Commit changes made during this transaction
$db->query('COMMIT');
 
// Rollback changes that have not been committed
$db->query('ROLLBACK');

Please note that MySQL MyISAM tables do not support transactions and will auto-commit even if a transaction has been started. Please see the ORM Conventions: MySQL Storage Engine section for details about this and other drawbacks to using MyISAM.

Escaping Data (Security)

If you have any experience with database interaction you are probably familiar with SQL injection attacks. For this reason, and the fact that the supported databases have varying representations for the various data types, it is recommended that all data going in and coming out of the database be escaped and unescaped respectively.

Escaping data can be done at any point by the escape() method, or can also be done when calling query() (also translatedQuery(), unbufferedQuery(), unbufferedTranslatedQuery()). When using the escape() method, two or more parameters are required. The first, $sql_or_type, allows passing the data type to be escaped, or an SQL statement with data type placeholders. The second (and subsequent) parameter, $value is the PHP value to escape.

The permissible data types to pass into $sql_or_type include:

• 'blob'
• 'boolean'
• 'date'
• 'float'
• 'integer'
• 'string'
• 'time'
• 'timestamp'

As mentioned before, you can also pass an SQL statement with data type placeholders. These placeholder are similar to some of the formatting strings in sprintf(). Here is a list of the various placeholders and what data type the value will be escaped as:

• '%l': blob
• '%b': boolean
• '%d': date
• '%f': float
• '%i': integer
• '%s': string
• '%t': time
• '%p': timestamp

Escaping not only protects against SQL injection attacks, but also ensures that you are comparing proper data types in your SQL since all values are validated before being escaped. Below are some examples using escape() in various ways:

// Escape a string
$sql_string = $db->escape('string', "This ain't gonna break your SQL");
 
// Escape a boolean
$sql_boolean = $db->escape('boolean', TRUE);
 
// Escape a float
$sql_float   = $db->escape('%f', '12.39');
 
// Escape a user id and name into a SQL statement
$sql = $db->escape('SELECT * FROM users WHERE age = %i AND last_name = %s', 18, "O'Shea");

The above statements would produce the following SQL (in a SQLite database):

'This ain''t gonna break your SQL'
 
'1'
 
12.39
 
SELECT * FROM users WHERE age = 18 AND last_name = 'O''Shea'

In addition to escaping single values for SQL, it is also possible to escape an array of values. When passing a data type or placeholder as the first parameter and the array of values as the second, an array of escaped values will be returned.

$escaped_integers = $db->escape('integer', array(1, 5, 'not an int'));

If the first parameter is a SQL string, the array of values will be inserted into the SQL, separated by commas.

// This will return "SELECT * FROM users WHERE user_id IN (1, 3, 7, 10)"
$sql = $db->escape(
    "SELECT * FROM users WHERE user_id IN (%i)",
    array(1, 3, 7, 10)
);

Unescaping Data

In addition to databases requiring that data going in be formatted a certain way, many database/driver combinations in PHP don’t deliver values back in the appropriate PHP data type. The unescape() method provides a consistent way to ensure that all data coming out of the database is stored correctly in PHP.

The first parameter, $data_type, should be one of the string data type names or a data type placeholder as convered with the escape() method. The second parameter, $value, is the value being returned from the database.

Here are some examples of using the unescape() method:

$is_authorized = $db->unescape('boolean', $row['is_authorized']);
 
$date_created  = $db->unescape('%d', $row['date_created']);

Please note that if you are using the ORM in Flourish, unescaping will be done automatically.

Prepared Statements

Prepared statements can improve performance when working with a query that will be executed multiple times with different sets of data. The prepare() and translatedPrepare() methods accept a single parameter, $sql, which should contain the SQL statement to prepare. They both return an fStatement object which can in turn be passed to execute(), query() or unbufferedQuery() in place of a SQL string.

$statement = $db->prepare("INSERT INTO users (first_name, last_name) VALUES (%s, %s)");
 
$db->query($statement, 'Will', 'Bond');
$db->query($statement, 'John', 'Smith');

Just like with query() and translatedQuery(), the values for the placeholders are passed as parameters after the fStatement object.

Performance

Computation is saved since the placeholders are parsed only when the statement is created, instead of upon every execution. If translatedPrepare() is called, the SQL translation is also performed only once.

In addition to Flourish level optimizations, many databases and PHP database extensions support prepared statements and may improve performance by caching query plans. For the databases/extensions that don't support prepared statements, a prepared statement will be executed behind the scenes using the normal query() method.

Please note, due to the way that fDatabase is written, prepared statements are not necessary to prevent SQL injection attacks.

Limitations

The one limitation of using prepared statements instead of a normal query is that multiple values can not be passed for a placeholder. For those situations, execute(), query() or translatedQuery() should be used instead.

// This will NOT work
$statement = $db->prepare("SELECT * FROM users WHERE user_id IN (%i)");
$res       = $db->query($statement, array(1, 2, 3));
foreach (res as $row) {
    // …
}

Debugging

There is some useful debugging functionality built into the class that can help when diagnosing SQL issues and performance. If enableDebugging() or fCore::enableDebugging() (for global debugging) is called with TRUE, the fDatabase class will display each SQL statement and how long it took to perform. In addition, when the class is destructed, a total SQL execution time will be output.

For performance debugging, the method enableSlowQueryWarnings() will cause PHP warnings to be triggered if an SQL query takes too long. The first parameter, $threshold, should be the executing time (in milliseconds) that will cause a query to be considered "slow."

// Enable SQL statement printing
$db->enableDebugging(TRUE);
 
// Enable slow query warnings for any query over 1/2 a second
$db->enableSlowQueryWarnings(500);

Caching

Both PostgreSQL and Oracle databases require some schema information about the database to properly fetch the last generated auto-incrementing primary key that is generated from an INSERT SQL statement. The fDatabase class will automatically retrieve that schema information, however in the interest of performance, you may wish to cache the results.

Along a similar vein, MSSQL databases don't support UTF-8 as the character set for non-national data types, so the fDatabase class will determine the database’s character set to ensure proper transcoding to UTF-8 is performed.

The enableCaching() method accepts an instance of the fCache class, and will save this schema information so it does not need to be fetched on each page load.

$db->enableCaching(new fCache('file', '/path/to/db.cache'));

The method clearCache() will clear out the cached information, which would be useful when the database schema changes.

When using the Flourish ORM, the fORM class provides some useful caching functionality that will automatically clear the cache when database errors occur.