<<css mode="next" class="sidebar"></css>> (((
- '''<a href="/docs/fSchema">Class Documentation</a>''' - <a href="/api/fSchema">API Reference</a> - <a href="https://github.com/flourishlib/flourish-classes/blob/master/fSchema.php" target="_blank">Source Code</a>
<<toc></toc>>
- fDatabase - fNoRowsException - fResult - fSQLException - fSQLTranslation - fSQLSchemaTranslation - '''fSchema''' - fStatement - fUnbufferedResult
)))
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.
The commands throughout this page are used to display information about database structure. Here are the `CREATE TABLE` statements for the example database:
The fSchema class simply requires and instance of the fDatabase class in order to be instantiated:
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():
The output of the above PHP would be:
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()`.
Here is an example:
The HTML output would be:
In addition to determining basic information about table and columns in a database, fSchema can also detect all of the primary, foreign and unique keys in a database using the ::getKeys() method.
Here is the PHP to get the key information:
And here is the HTML that would be output:
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:
The output for the `users` relationships would be:
While the output for the `groups` relationships would be:
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 doesn’t 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.
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.
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.
The exact array structure to use with ::setKeysOverride() can be found in the ::getKeys() method documentation.
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.
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.