Schema Manager

As of version 1.14, PHPLucidFrame added a new feature called Schema Manager to manage your site database. A schema file for your database can be defined in the directory /db. The file name format is schema.[namespace].php where namespace is your database namespace defined in $lc_databases of /inc/config.php. If [namespace] is omitted, “default” is used. The schema file syntax is an array of options and table names that must be returned to the caller. A sample schema file is available at /db/schema.sample.php in the release.

Default Options for Tables

The schema syntax starts with _options which is a set of defaults for all tables, but it can be overidden by each table definition.

'_options' => array(
    // defaults for all tables; this can be overidden by each table
    'timestamps'    => true, // all tables will have 3 datetime fields - `created`, `updated`, `deleted`
    'constraints'   => true, // all FK constraints to all tables
    'engine'        => 'InnoDB', // db engine for all tables
    'charset'       => 'utf8', // charset for all tables
    'collate'       => _p('db.default.collation'), // collation for all tables; inherited from /inc/parameter/
),

Table Definition

After then, each table can be defined using table name as key and value as an array of field definition. The following is a snapshot of example schema definition for two tables (category and post) that have one-to-many relation.

// array keys are table names without prefix
'category' => array(
    'slug'          => array('type' => 'string', 'length' => 255, 'null' => false, 'unique' => true),
    'catName'       => array('type' => 'string', 'length' => 200, 'null' => false),
    'catName_en'    => array('type' => 'string', 'length' => 200, 'null' => true),
    'catName_my'    => array('type' => 'string', 'length' => 200, 'null' => true),
    'options' => array(
        'pk'  => array('cat_id'),     // type: integer, autoinc: true, null: false, unsigned: true
        'timestamps' => true,        // created, updated, deleted; override to _options.timestamps
        'charset'    => 'utf8',      // override to _options.charset
        'collate'    => 'utf8_unicode_ci',  // override to _options.collate
        'engine'     => 'InnoDB',           // override to _options.engine
    ),
    '1:m' => array(
        // one-to-many relation between `category` and `post`
        // there must also be 'm:1' definition at the side of `post`
        'post' => array(
            'name' => 'cat_id', // FK field name in the other table (defaults to "table_name + _id")
            //'unique'  => false,   // Unique index for FK; defaults to false
            //'default' => null,    // default value for FK; defaults to null
            'cascade'   => true,    // true for ON DELETE CASCADE; false for ON DELETE RESTRICT
        ),
    ),
),
'post' => array(
    'slug'          => array('type' => 'string', 'length' => 255, 'null' => false, 'unique' => true),
    'title'     => array('type' => 'string', 'null' => false),
    'title_en'  => array('type' => 'string', 'null' => true),
    'title_my'  => array('type' => 'string', 'null' => true),
    'body'      => array('type' => 'text', 'null' => false),
    'body_en'   => array('type' => 'text', 'null' => true),
    'body_my'   => array('type' => 'text', 'null' => true),
    'options' => array(
        'Pk' => array('id'), // if this is not provided, default field name to `id`
    ),
    '1:m' => array(
        // one-to-many relation between `post` and `post_image`
        // there must also be 'm:1' definition at the side of `post_image`
        'post_image' => array(
            'name'      => 'id',
            'cascade'   => true,
        ),
    ),
    'm:1' => array(
        'category', // reversed 1:m relation between `category` and `post`
        'user',     // reversed 1:m relation between `user` and `post`
    ),
    'm:m' => array(
        // many-to-many relation between `post` and `tag`
        // there must also be 'm:m' definition at the side of `tag`
        'tag' => array(
            'name'      => 'id',
            'cascade'   => true,
        ),
    ),
),

The following describes the rule explanation of table schema array.

Name Default Explanation
{field_name}  

The field name (a valid field name for the underlying database table). Use the field name “slug” for the sluggable field. Generally, you don’t have to define primary key field. It will be added by default using the field name “id” with the following rule:

  • type: integer
  • autoinc: true
  • null: false
  • unsigned: true

However, if you want to use other field type (e.g, string type) and rule for your primary key, you must define the field here using your own rule, for example, 'id' => array('type' => 'string', 'length' => 64, 'null' => false)

{field_name}.type   The data type (See Data Type Mapping Matrix for the underlying database)
{field_name}.length

255 for string 11 for int/integer 1 for boolean

array(0, 0) for decimal

array(0, 0) for float

The length of the field
{field_name}.null true Allow NULL or NOT NULL
{field_name}.default   The default value for the field
{field_name}.unsigned false Unsigned or signed
{field_name}.autoinc false Auto-increment field
{field_name}.unique false Unique index for the field
options   The array of the table options
options.pk array('id')

One or more primary key field names. The default primary key field name is “id”. If you want to use a different name rather than “id” (e.g., user_id, post_id), you can define it here. The default primary key field definition is

  • type: integer
  • autoinc: true
  • null: false
  • unsigned: true
options.timestamps true Include 3 datetime fields - created, updated, deleted; override to _optons.timestamps
options.charset utf8 The charset for the table; override to _options.charset
options.collate utf8_unicode_ci The charset for the table; override to _options.collate
options.engine InnoDB The charset for the table; override to _options.engine
options.unique  

Unique index for composite fields

array('keyName' => array('field_name1', 'field_name2'))

1:m   One-to-Many relationship; if you define this, there must be m:1 definition at the many-side table
1:m.{table_name}   The name of the many-side table as array key with the following options.
1:m.{table_name}.name table_name + “_id” The foreign key field name in the many-side table
1:m.{table_name}.unique false Unique index for the foreign key field
1:m.{table_name}.default null Default value for the foreign key field
1:m.{table_name}.cascade false
  • true for ON DELETE CASCADE
  • false for ON DELETE RESTRICT
  • null for ON DELETE SET NULL
m:1   Array of table names that are reverse of one-to-many relations to 1:m
m:1.{table_name}   The name of the one-side table
m:m   Many-to-many relationship; if you define this, there must be m:m definition at the other many-side table
m:m.{table_name}   The name of the reference table
m:m.{table_name}.table   Optional pivot table name; if it is not defined, the two table names will be used concatenating with _to_ such as table1_to_table2
m:m.{table_name}.name table_name + “_id” The reference field name in the pivot table
m:m.{table_name}.cascade false
  • true for ON DELETE CASCADE
  • false for ON DELETE RESTRICT
  • null for ON DELETE SET NULL
1:1   One-to-One relationship
1:1.{table_name}   The name of the reference table
1:1.{table_name}.name table_name + “_id” Foreign key field name that will be included in the table; it maps to the primary key of the reference table
1:1.{table_name}.cascade false
  • true for ON DELETE CASCADE
  • false for ON DELETE RESTRICT
  • null for ON DELETE SET NULL

Data Type Mapping Matrix

The following table shows the matrix that contains the mapping information for how a specific type is mapped to the database.

Type Name MySQL Data Type Explanation
smallint SMALLINT

2-byte integer values:

  • Unsigned integer with a range of 0 to 65535
  • Signed integer with a range of −32768 to 32767
mediumint MEDIUMINT

3-byte integer values:

  • Unsigned integer with a range of 0 to 16777215
  • Signed integer with a range of −8388608 to 8388607
int/integer INT

4-byte integer values:

  • Unsigned integer with a range of 0 to 4294967295
  • Signed integer with a range of −2147483648 to 2147483647
bigint BIGINT

8-byte integer values:

  • Unsigned integer with a range of 0 to 18446744073709551615
  • Signed integer with a range of −9223372036854775808 to 9223372036854775807
decimal NUMERIC(p,s) A numeric data with fixed (exact) point precision. The precision (p) represents the number of significant digits that are stored for values. The scale (s) represents the number of digits that can be stored following the decimal point.
float DOUBLE(p,s) A numeric data with floating (approximate) point precision. The precision (p) represents the number of significant digits that are stored for values. The scale (s) represents the number of digits that can be stored following the decimal point.
string VARCHAR String data with a maximum length specified
char CHAR String data with a fixed length specified
binary VARBINARY Binary string data with a maximum length specified
tinytext TINYTEXT String data with a maximum length of 255 characters.
text TEXT String data with a maximum length of 6,553 characters.
mediumtext MEDIUMTEXT String data with a maximum length of 16,777,215 characters.
longtext LONGTEXT String data with a maximum length of 4,294,967,295 characters.
tinyblob TINYBLOB String data with a maximum length of 6,553 characters.
blob BLOB Binary string data with a maximum length of 6,553 characters.
mediumblob MEDIUMBLOB Binary string data with a maximum length of 16,777,215 characters.
longblob LONGBLOB Binary string data with a maximum length of 4,294,967,295 characters.
array TEXT An array data based on PHP serialization. It uses serialization to represent an exact copy of your array as string. The database and values retrieved from the database are always converted to PHP’s array type using deserialization.
json TEXT An array data based on PHP’s JSON encoding functions. It stores a valid UTF8 encoded JSON format string and values received from the database are always the return value from PHP’s json_decode() function.
boolean TINYINT(1) A boolean data. If you know that the data to be stored always is a boolean (true or false), you should consider using this type.
date DATE A date data with format YYYY-MM-DD
datetime DATETIME A date and time with format YYYY-MM-DD HH:MM:SS
time TIME A time data with format HH:MM:SS

Loading Your Schema

Assuming that you have created your application database and you have defined your schema in /db/schema.php for the database, you can load or import the database using LucidFrame console tool by running the command:

$ php lucidframe schema:load

It will import the database defined under the namespace “default”. If you want to load another database defined under a different namespace, for example “sample”, you just need to provide the namespace in the command such as

$ php lucidframe schema:load sample

Exporting Your Schema

You can export or dump your database loaded by your schema definition. The LuicdFrame console command schema:export will help you.

$ php lucidframe schema:export

It will export the database of the namespace “default” in the directory /db/generated/ as .sql file. You can also provide the namespace in the command such as

$ php lucidframe schema:export sample

Managing Schema Changes

As of version 2.2.0, PHPLucidFrame provides a way to manage schema changes. It helps you to programmatically deploy new versions of your database schema easily in a standardized way.

Let’s say an example, we use the sample database as our default and we are adding a new field wechatUrl in the table social_profile. Let’s edit the file /db/schema.sample.php

'social_profile' => array(
    'facebook_url'  => array('type' => 'string', 'null' => true),
    'twitter_url'   => array('type' => 'string', 'null' => true),
    'instagram_url' => array('type' => 'string', 'null' => true),
    'linkedin_url'  => array('type' => 'string', 'null' => true),
    '1:1' => array(
        // one-to-one relation between `social_profile` and `user`
        // no need to define 1:1 at the side of `user`
        'user' => array(
            'name'      => 'uid',
            'cascade'   => true,
        ),
    ),
),

Then, run schema:diff sample and it will generate a file with extension sqlc in /db/version/sample

$ php lucidframe schema:diff sample
PHPLucidFrame 3.3.0 by Sithu K.

./db/version/sample/20170406223436.sqlc is exported.
Check the file and run `php lucidframe schema:update sample`
Done.

You can open that sqlc file and check its content. Finally, you can run schema:update sample to apply this changes in your underlying database.

$ php lucidframe schema:update sample
PHPLucidFrame 3.3.0 by Sithu K.

IMPORTANT! Backup your database before executing this command.
Some of your data may be lost. Type "y" or "yes" to continue: y

Executing 20170406223436

Your schema has been updated.
Done.

The following example will show you in another scenario where renaming the fields. Let’s say we are remove Url from all field names of the table social_profile such as

'social_profile' => array(
    'facebook'  => array('type' => 'string', 'null' => true),
    'twitter'   => array('type' => 'string', 'null' => true),
    'instagram' => array('type' => 'string', 'null' => true),
    'linkedin'  => array('type' => 'string', 'null' => true),
    '1:1' => array(
        // one-to-one relation between `social_profile` and `user`
        // no need to define 1:1 at the side of `user`
        'user' => array(
            'name'      => 'uid',
            'cascade'   => true,
        ),
    ),
),

Again, run schema:diff sample and you will be confirmed for renaming fields.

$ php lucidframe schema:diff sample
PHPLucidFrame 3.3.0 by Sithu K.


Type "y" to rename or type "n" to drop/create for the following fields:

Field renaming from `facebook_url` to `social_profile.facebook`: y
Field renaming from `twitter_url` to `social_profile.twitter`: y
Field renaming from `instagram_url` to `social_profile.instagrams`: y
Field renaming from `linkedin_url` to `social_profile.linkedin`: y

./db/version/sample/20170406224852.sqlc is exported.
Check the file and run `php lucidframe schema:update sample`
Done.

Now you can see there are two sqlc files in the directory /db/version/sample. Then, as suggested above, you just need to run schema:update sample to update your database schema.

$ php lucidframe schema:update sample
PHPLucidFrame 3.3.0 by Sithu K.

IMPORTANT! Backup your database before executing this command.
Some of your data may be lost. Type "y" or "yes" to continue: y

Executing 20170406224852

Your schema has been updated.
Done.

That’s it! You now have two version files of your schema changes stored in /db/version/sample.

If you are of team of developers and your team uses version control system, those sqlc files should be tracked in your VCS to make it available to other developers in the team. When they get the files, they simply needs to run the command schema:update to synchronize their databases as yours.