Database Classes

The fSchema class provides information about the structure of a database, from table and column info to keys and relationships. The database schema is converted to a standard format that can then be used by other code wishing to interact with the database.

Example Database

The commands throughout this page are used to display information about database structure. Here are the CREATE TABLE statements for the example database:

Table Listing

One of the most basic tasks to perform with the fSchema class is to return a list of all tables in the database by using getTables():

$tables = $schema->getTables();
fCore::expose($tables);

The output of the above PHP would be:

<pre class="exposed">Array
(
[0] =&gt; groups
[1] =&gt; users
)</pre>

Column Information

Column information can be returned for any table in the database using the getColumnInfo() method. The returned associative array contains the following information about each column: type, not_null, default, valid_values, max_length, min_value, max_value, decimal_places and auto_increment. For details about the returned array and the data type mapping that occurs, please view the documentation for getColumnInfo().

Relationships

Key information for the database is useful, but another very useful bit of information is how the different tables in the database are related. getRelationships() method uses the key information to determine the one-to-one, one-to-many, many-to-one and many-to-many relationships present:

Overriding

The fSchema class is used extensively by the object relational mapping code built into Flourish. Occasionally Flourish will support certain features based on database structure that are impossible to accomplish in a certain type of database.

To allow the object relational mapping code to still perform the necessary tasks, even if the database engine doesnt support a feature, the setColumnInfoOverride() and setKeysOverride() methods allow schema information to be overridden.

Please note that faking foreign-key relationships for MyISAM tables in MySQL may cause your data to get into an inconsistent state. This is because MyISAM tables do not support transactions, which Flourish uses for the purpose of atomic changes to the database across multiple tables at a time.

Example Database

Below are some example tables to show how foreign keys can be faked to help provide relational data integrity. These examples are presented using MySQL.

PHP to Override

The following PHP will create relationships between groups and users. There will not be any normal situations where you would want to override the primary or unique keys on a table. If you are using the ORM, this code should be executed before any fActiveRecord objects are used, such as in the bootstrap page, or something called from it.

Caching

Since the fSchema class executes a number of database calls to determine the structure of the database, it may be desirable to cache the information to reduce database load and script execution time. The method enableCaching() accepts an instance of the fCache object and will use it to save all of the schema information.

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

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