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 | | | | <#data-type-mapping-matrix>`_ for the underlying database) | +-------------------------------+-------------------------------+-----------------------------------------------------------------------+ | ``{field_name}.length`` | ``255 for string`` | The length of the field | | | ``11 for int/integer`` | | | | ``1 for boolean`` | | | | | | | | ``array(0, 0) for decimal`` | | | | | | | | ``array(0, 0) for float`` | | +-------------------------------+-------------------------------+-----------------------------------------------------------------------+ | ``{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 | +==============+=================+========================================================================================+ | tinyint | TINYINT | 1-byte integer values: | | | | | | | | - Unsigned integer with a range of **0** to **255** | | | | - Signed integer with a range of **-128** to **127** | +--------------+-----------------+----------------------------------------------------------------------------------------+ | 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.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.