PHP Unframework
PHP Unframework
PHP Unframework
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:
CREATE TABLE groups (
group_id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE users (
user_id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(200) NOT NULL UNIQUE,
posts INTEGER NOT NULL DEFAULT 0,
birthday DATE,
status VARCHAR(20) NOT NULL DEFAULT 'Inactive' CHECK(status IN ('Active', 'Inactive')),
group_id INTEGER REFERENCES groups(group_id) ON DELETE CASCADE ON UPDATE CASCADE
);The fSchema class simply requires and instance of the fDatabase class in order to be instantiated:
$db = new fDatabase('sqlite', $_SERVER['DOCUMENT_ROOT'] . '/example.db');
$schema = new fSchema($db);
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] => groups
[1] => users
)</pre>
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:
$column_info = $schema->getColumnInfo('users');
fCore::expose($column_info);The HTML output would be:
<pre class="exposed">Array
(
[user_id] => Array
(
[type] => integer
[not_null] => {true}
[auto_increment] => {true}
[default] => {null}
[valid_values] => {null}
[min_value] => -2147483648
[max_value] => 2147483647
[max_length] => {null}
[decimal_places] => {null}
[comment] => {empty_string}
)
[name] => Array
(
[type] => varchar
[max_length] => 200
[not_null] => {true}
[default] => {null}
[valid_values] => {null}
[min_value] => {null}
[max_value] => 2147483647
[decimal_places] => {null}
[auto_increment] => {false}
[comment] => {empty_string}
)
[posts] => Array
(
[type] => integer
[not_null] => {true}
[default] => 0
[valid_values] => {null}
[max_length] => {null}
[min_value] => -2147483648
[max_value] => 2147483647
[decimal_places] => {null}
[auto_increment] => {false}
[comment] => {empty_string}
)
[birthday] => Array
(
[type] => date
[not_null] => {false}
[default] => {null}
[valid_values] => {null}
[max_length] => {null}
[min_value] => {null}
[max_value] => {null}
[decimal_places] => {null}
[auto_increment] => {false}
[comment] => {empty_string}
)
[status] => Array
(
[type] => varchar
[max_length] => 20
[not_null] => {true}
[default] => Inactive
[valid_values] => Array
(
[0] => Active
[1] => Inactive
)
[min_value] => {null}
[max_value] => {null}
[decimal_places] => {null}
[auto_increment] => {false}
[comment] => {empty_string}
)
[group_id] => Array
(
[type] => integer
[not_null] => {false}
[default] => {null}
[valid_values] => {null}
[min_value] => -2147483648
[max_value] => 2147483647
[max_length] => {null}
[auto_increment] => {false}
[comment] => {empty_string}
)
)</pre>
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:
$keys = $schema->getKeys('users');
fCore::expose($keys);And here is the HTML that would be output:
<pre class="exposed">Array
(
[primary] => Array
(
[0] => user_id
)
[foreign] => Array
(
[0] => Array
(
[column] => group_id
[foreign_table] => groups
[foreign_column] => group_id
[on_delete] => cascade
[on_update] => cascade
)
)
[unique] => Array
(
[0] => Array
(
[0] => name
)
)
)</pre>
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:
$users_relationships = $schema->getRelationships('users');
fCore::expose($users_relationships);
$groups_relationships = $schema->getRelationships('groups');
fCore::expose($groups_relationships);
The output for the users relationships would be:
<pre class="exposed">Array
(
[one-to-one] => Array
(
)
[many-to-one] => Array
(
[0] => Array
(
[column] => group_id
[related_table] => groups
[related_column] => group_id
)
)
[one-to-many] => Array
(
)
[many-to-many] => Array
(
)
)</pre>
While the output for the groups relationships would be:
<pre class="exposed">Array
(
[one-to-one] => Array
(
)
[many-to-one] => Array
(
)
[one-to-many] => Array
(
[0] => Array
(
[column] => group_id
[related_table] => users
[related_column] => group_id
[on_delete] => cascade
[on_update] => cascade
)
)
[many-to-many] => Array
(
)
)</pre>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.
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.
CREATE TABLE users (
user_id INTEGER PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email_address VARCHAR(200) NOT NULL UNIQUE,
hashed_password VARCHAR(100) NOT NULL
);
CREATE TABLE groups (
group_id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL UNIQUE,
group_leader INTEGER,
group_founder INTEGER
);
CREATE TABLE users_groups (
user_id INTEGER NOT NULL,
group_id INTEGER NOT NULL,
PRIMARY KEY(user_id, group_id)
);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.
$schema = new fSchema($database);
// If we are using the ORM well want to attach this instance
fORMSchema::attach($schema);
// Set up the foreign keys from groups to users
$schema->setKeysOverride(
array(
array(
'column' => 'group_founder',
'foreign_table' => 'users',
'foreign_column' => 'user_id',
'on_delete' => 'cascade',
'on_update' => 'cascade'
),
array(
'column' => 'group_leader',
'foreign_table' => 'users',
'foreign_column' => 'user_id',
'on_delete' => 'cascade',
'on_update' => 'cascade'
)
),
'groups',
'foreign'
);
// Set up the keys for users_groups to create a join table
// for a many-to-many relationship between users and groups
$schema->setKeysOverride(
array(
array(
'column' => 'user_id',
'foreign_table' => 'users',
'foreign_column' => 'user_id',
'on_delete' => 'cascade',
'on_update' => 'cascade'
),
array(
'column' => 'group_id',
'foreign_table' => 'groups',
'foreign_column' => 'group_id',
'on_delete' => 'cascade',
'on_update' => 'cascade'
)
),
'users_groups',
'foreign'
);
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.
When using the Flourish ORM, the fORM class provides some useful caching functionality that will automatically clear the cache when database errors occur.