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 |
---|---|---|
|
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:
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,
|
|
|
The data type (See Data Type Mapping Matrix for the underlying database) |
|
|
|
The length of the field |
|
true |
Allow |
|
The default value for the field |
|
|
false |
Unsigned or signed |
|
false |
Auto-increment field |
|
false |
Unique index for the field |
|
The array of the table options |
|
|
|
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
|
|
true |
Include 3 datetime fields - |
|
utf8 |
The charset for the table; override to |
|
utf8_unicode_ci |
The charset for the table; override to |
|
InnoDB |
The charset for the table; override to |
|
Unique index for composite fields
|
|
|
One-to-Many relationship; if you define this, there must be |
|
|
The name of the many-side table as array key with the following options. |
|
|
table_name + “_id” |
The foreign key field name in the many-side table |
|
false |
Unique index for the foreign key field |
|
null |
Default value for the foreign key field |
|
false |
|
|
Array of table names that are reverse of one-to-many relations to
|
|
|
The name of the one-side table |
|
|
Many-to-many relationship; if you define this, there must be |
|
|
The name of the reference table |
|
|
Optional pivot table name; if it is not defined, the two table names
will be used concatenating with |
|
|
table_name + “_id” |
The reference field name in the pivot table |
|
false |
|
|
One-to-One relationship |
|
|
The name of the reference table |
|
|
table_name + “_id” |
Foreign key field name that will be included in the table; it maps to the primary key of the reference table |
|
false |
|
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 |
---|---|---|
tinyint |
TINYINT |
1-byte integer values:
|
smallint |
SMALLINT |
2-byte integer values:
|
mediumint |
MEDIUMINT |
3-byte integer values:
|
int/integer |
INT |
4-byte integer values:
|
bigint |
BIGINT |
8-byte integer values:
|
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 |
boolean |
TINYINT(1) |
A boolean data. If you know that the data to be stored always is a boolean
( |
date |
DATE |
A date data with format |
datetime |
DATETIME |
A date and time with format |
time |
TIME |
A time data with format |
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.4.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.4.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.4.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.4.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.