php mi add:migrator file_name
migrations/
directory.
Files are generated by appending the migration file name supplied to the current time. Hence, the
migration file will look like M1673742992_file_name.php
. The file created is usually
one which must implement the up
and down()
method. The up()
method will be called when the migration file is stepped up while the down()
method will be called
when the migration files are stepped down. When defining migration file names, it is
only logical to use names that makes it easier to identify what the migration file does for easy recognition. Hence,
a migration file will resemble the format below.
<?php class M1673742992_file_name { function up() { } function down() { } function tablename() : string { return 'some_table'; } }
up()
and down()
methods are essential, the tablename()
method is is optional.
However, by declaring the tablename()
method, it makes it easier to manipulate our data in a
way that will be discussed later.
DBSCHEMA
class is the only class that makes it easier to create manage tables successfully through the
command line environment. Along with a DRAFT
class, they can create, alter, modify, change or drop database
tables. The DBSCHEMA
class has only four main methods which are listed below:
DBSCHEMA::CREATE()
method is used to generate or create database tables
DBSCHEMA::ALTER()
method is used to alter or modify existing database tables
DBSCHEMA::DROP_TABLE()
method is the only way to drop a table.
DBSCHEMA::DROP_FIELD()
method is the easiest way to drop a table's column outside the
DB::ALTER()
scope.
The DRAFT
class defines the structure and format of a table. It contains database designs and modification
plans. This class uses methods to pivot how a table should be modififed or created. These methods are used to either define
a table's data type or foreign constraints or used to alter a tables structure. The DRAFT
class is usually applied
within the DBSCHEMA::CREATE()
or DBSCHEMA::ALTER()
environment in a migration file using the format below:
There are lists of methods which can be applied
and we shall be taking a closer look to some of these methods:
<?php class M1673742992_file_name { function up() { DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){ }); } function down() { DBSCHEMA::ALTER('tablename', function(DRAFT $DRAFT){ }); } }
CREATE
, ALTER
, DROP_TABLE
or DROP_FIELD
methods
provided that the object implements a method "tablename()" which returns a database table name. Hence, the format
below is also valid.
<?php class M1673742992_file_name { function up() { DBSCHEMA::CREATE($this, function(DRAFT $DRAFT){ }); } function down() { DBSCHEMA::ALTER($this, function(DRAFT $DRAFT){ }); } function tablename(){ return 'table_name'; } }
DBSCHEMA::CREATE()
or DBSCHEMA::ALTER()
environment through the use of DRAFT
class. This is because they can be used when generating or modifying a table's
column. The following are static methods used for defining a table's data type:
VARCHAR()
,
CHAR()
,
ENUM()
,
JSON()
,
TEXT()
,
TEXTFIELD()
,
INT()
,
INTFIELD()
,
BOOL()
,
BIT()
,
BINARY()
,
SERIAL()
,
DATE()
,
TIME()
,
YEAR()
,
BLOB()
,
BLOBFIELD()
,
REAL()
,
DECIMAL()
,
DOUBLE()
,
FLOAT()
DEFAULT()
,
NULL()
,
NOT_NULL()
,
SIGNED()
,
UNSIGNED()
,
INDEX()
,
UNIQUE()
,
FOREIGN_KEY()
,
PRIMARY_KEY()
,
CONSTRAINT()
,
AUTO()
or
AUTO_INCREMENT()
,
CASCADE()
,
RESTRICT()
DBSCHEMA::ALTER()
scope.
They take only one argument which is a callback closure argument that must return a DRAFT
object. Since their main function is to modify an already existing table,
the DRAFT
object is used to pivot an alteration within them and the DRAFT
is returned back by the callback closure function. There are only three
type of these method which are MODIFY()
, CHANGE()
and DROP()
. It is however important to note that unlike the
MODIFY()
and CHANGE()
methods, the DROP()
method does not implement a callback function. Assuming we have a DRAFT
object "$DRAFT"
,
the MODIFY
or CHANGE
methods can be applied in the following format:
DBSCHEMA::ALTER($this, function(DRAFT $DRAFT){
$DRAFT::MODIFY( function($DRAFT) {
$DRAFT::DATA_TYPE(); // use any of the data type method here.
return $DRAFT;
} );
});
DBSCHEMA::ALTER($this, function(DRAFT $DRAFT){
$DRAFT::MODIFY( fn() => $DRAFT::DATA_TYPE() );
});
DBSCHEMA::ALTER($this, function(DRAFT $DRAFT){
$DRAFT::CHANGE( fn() => $DRAFT::INT('fieldname', 2) );
});
In the example above, assuming the "fieldname" was a "VARCHAR" field before, the new data type "INT" will convert the field to an integer field using the supplied length. The second argument in this case which is the length, must be the argument accepted to be valid by the field.
The DROP()
method drops a database table column or database table's index.
It takes one or two arguments depending on what is expected to be dropped. If one argument is supplied,
it is assumes that the migration table's field is expected to be dropped unless it is set as "PRIMARY KEY" which
will drop the current table's primary key. However, if two arguments are supplied, then the first is assumed to be
an index type (e.g UNIQUE) while second argument will be the index name that is expected to be dropped. An example is shown below:
DBSCHEMA::ALTER($this, function(DRAFT $DRAFT){ $DRAFT::DROP( 'address' ); // drop current database table's field });
DBSCHEMA::ALTER($this, function(DRAFT $DRAFT){ $DRAFT::DROP( 'UNIQUE', 'initials' ); // drop unique index "initials" from current table });
INTFIELD()
, TEXTFIELD()
and BLOBFIELD()
takes the
type of field first before the second argument which is the name of the column to be altered or modified. After
defining the column name, then other properties are defined based on size, precision
or options depending on the type of method called. The data type methods are listed below:
VARCHAR($name, $size, $default)
CHAR($name, $size, $default)
ENUM($name, $options, $default)
TEXT($name, $size, $default)
INT($name, $size, $default)
BLOB($name, $default)
TEXTFIELD($type, $name, $size, $default)
INTFIELD($type, $name, $size, $default)
BLOBFIELD($type, $name, $default)
BIT($name)
BINARY($name, $default)
BOOL($name, $default)
SERIAL($name, $default)
FLOAT($name, $size, $default)
DOUBLE($name, $size, $default)
DECIMAL($name, $size, $default)
REAL($name, $size, $default)
DATE($name, $default)
DATETIME($name, $precision, $default)
TIMESTAMP($name, $precision, $default)
TIME($name, $precision, $default)
YEAR($name, $precision, $default)
COMMENT($string)
$name
This is usually the first argument of any of the data type methods except in INTFIELD()
BLOBFIELD()
and TEXTFIELD()
method where it comes as the second argument.
It defines the name of a table's column when used within
the DBSCHEMA::CREATE()
scope. However, within the DBSCHEMA::ALTER
it supports an array of old column name to new table name, where the index of the array is the
old column name while the value of that array index is the new column name.
$type
This is usually the first argument of any of the three data type methods INTFIELD()
BLOBFIELD
and TEXTFIELD
. It mostly defines the type of a field based on the
relative data type through any of the options listed below
Field | Description | Options |
---|---|---|
INTFIELD | For integer columns | [ INT | [TINYINT/TINY] | [SMALLINT/SMALL] | [BIGINT/BIG] ] |
TEXTFIELD | For text columns | [ TEXT | [TINYTEXT/TINY] | [MEDIUMTEXT/MEDIUM] | [LONGTEXT/LONG] ] |
BLOBFIELD | For text columns | [ BLOB | [TINYBLOB/TINY] | [SMALLBLOB/SMALL] [MEDIUMBLOB/MEDIUM] | [LONGBLOB/LONG] ] |
TINY
, SMALL
, BIG
, MEDIUM
and
LONG
are usually aliases for their respective methods.
$size
This defines the size of a table's column. Most but not all of the data type methods supports the
$size
argument. The $size method may exist as an array, string or integer depending on the type of
table column being defined. In methods such as FLOAT
, DOUBLE
, DECIMAL
and REAL
, the $size
usually define both the precision and column length. In integer
related methods, the $size
is defined as a string while in text related methods, it is defined as a string.
In decimal related methods such as FLOAT
, DECIMAL
, REAL
and DOUBLE
,
this argument is supplied as an array.
$option
This argument is only implemented by the ENUM()
method which is an array that contains a list of
valid options to be applied on a field.
$precision
This argument is only used in time related methods such as DATE
,
DATETIME
, TIMESTAMP
, TIME
and YEAR
.
It defines the precision for the field that is expected to be created or modified.
$default
In methods that implements this argument, the argument sets a default value for the field or column
to be created. This can also be added by using the DEFAULT()
constriant method.
$comment
This argument is implemented by the COMMENT()
method. It is used to set a comment on a particular
field.
ID()
method is used to generate an auto-incremental primary key field. By default, the column
name is set as "id" but that can be modified by supplying a custom name as the second argument. The first argument takes
the length of field from a minimum of 1 to a maximum of 255. The syntax is shown below: DRAFT::ID($size, $custom_name);
$size
as length of field $custom_name
as custom column name. Default is "id"
DEFAULT()
,
NULL
, NOT_NULL()
, PRIMARY_KEY()
, FOREIGN_KEY()
, SIGNED()
,
UNSIGNED()
, CONSTRAINT()
, AUTO_INCREMENT()
are listed and explained below:
DEFAULT()
is used to set a default value for a field. While numeric values may be supplied for
interger or float fields, strings are mostly used to define string values, constants or functions.
Generally, all strings arguments are treated as string unless it uses a defined structure for constant or functions.
DEFAULT("(CURRENT_TIMESTAMP)")
while systemic functions such as "GETDATE()" can be defined
as "(GET_DATE())"
. Without setting the parenthesis, the value may be treated as a normal string.
NOT_NULL()
is used to set a "NOT NULL" constraint on fields while the NULL()
method defines that a field may accept a null value.
SIGNED()
or UNSIGNED()
constraints can be chained along with an INT()
or
INTFIELD()
method which literally defines whether the intended integer field to be created is signed or unsigned.
CONSTRAINT()
method is used to define a new constraint that has an identifier name which can be used to store the
intended unique or foreign key field. It takes an argument of an index storage name. Usually, this method may come before methods like
UNIQUE()
or FOREIGN_KEY()
is called.
CONSTRAINT()
method which takes an argument of the unique identifier name. If the
UNIQUE()
method is chained on CONSTRAINT()
, then multiple column names can be supplied as an array which denotes that
the array lists of supplied column names are intended to be uniquely binded.
PRIMARY_KEY()
method is employed.
This method can take a string or an array as the names of fields intended to be set as primary key field.
When no argument is supplied, it uses the current field name as the intended primary key field.
AUTO()
is a short alias for AUTO_INCREMENT()
which defines the automatic increment feature on a primarily defined
integer column.
INDEX()
method sets an index constraint on a table's field.
DBSCHEMA::CREATE()
scope using the DRAFT
data type
chaining structures.
DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){ $DRAFT::ID(); $DRAFT::VARCHAR('firstname', 100)->NOT_NULL(); $DRAFT::VARCHAR('lastname', 100)->NOT_NULL(); $DRAFT::VARCHAR('username', 255)->NOT_NULL(); $DRAFT::VARCHAR('password', 255)->NOT_NULL(); $DRAFT::VARCHAR('email', 255)->NOT_NULL(); $DRAFT::VARCHAR('address', 1000); $DRAFT::VARCHAR('phone', 20); $DRAFT::DATETIME('added_on', "(CURRENT_TIMESTAMP)"); $DRAFT::DATETIME('updated_on')->DEFAULT("(CURRENT_TIMESTAMP)"); $DRAFT::UNIQUE(['username', 'password']); return $DRAFT; })
DBSCHEMA::ALTER()
is used to modify tables, by declaring a data type method within this method, the
migrator engine assumes that a new field is intended to be created. The methods like AFTER()
and BEFORE()
can be applied to specify the position where the new column is expected to be added. An example is shown below: DBSCHEMA::ALTER('tablename', function(DRAFT $DRAFT){ $DRAFT::VARCHAR('fullname')->AFTER('username'); // add fullname field after username column $DRAFT::VARCHAR('address')->AFTER('firstname'); // add firstname field to the beginning of the table return $DRAFT; })
DBSCHEMA::ALTER()
environment can also be used to drop a database table or table indexes just as shown below.
DBSCHEMA::ALTER('users', function(DRAFT $DRAFT){ $DRAFT::DROP('fullname'); // drop fullname column in users table $DRAFT::DROP('PRIMARY KEY'); // drop the primary key from the table $DRAFT::DROP('INDEX', 'unique1'); // drop the index name "unique1" from the table. $DRAFT::DROP('UNIQUE', 'unique2'); // drop the unique name "unique2" from the table. $DRAFT::DROP('FOREIGN KEY', 'unique3'); // drop the FOREIGN KEY name "unique3" from the table. return $DRAFT; })
DRAFT
class the DROP(true)
or DROP_TABLE()
can be used to drop the current table.
DBSCHEMA::DROP_TABLE('users'); // drop current table "users".
DRAFT::RENAME()
method.
DBSCHEMA::ALTER('users', function(DRAFT $DRAFT){
$DRAFT::RENAME_TO('user_table'); // rename current table "users" to "user_table"
return $DRAFT;
})
CHANGE()
method can be used to change the field name as shown below .
DBSCHEMA::ALTER('users', function(DRAFT $DRAFT){ $DRAFT::CHANGE(function() use($DRAFT) { $DRAFT::VARCHAR(['firstname' => 'fname']); // change firstname to fname of VARCHAR type $DRAFT::VARCHAR(['lastname' => 'lname']); // change lastname to lname of VARCHAR type $DRAFT::INT(['serial_no' => 'serial']); // change serial_no to serial field of INT type return $DRAFT; }); })
CHANGE()
modifier method
within the DBSCHEMA::ALTER()
scope. It is worthy to note that only the CHANGE()
method accepts array
arguments for data type methods, which makes it easier to change fields names. The $DRAFT
object can also be supplied within the
callback function instead of defining it within the use()
function.
DRAFT::CONVERT_TO()
method.
DBSCHEMA::ALTER('users', function(DRAFT $DRAFT){
$DRAFT::CONVERT_TO('latin 1'); //set current table character type to latin
})
PARTITION_BY
, COLUMNS
, PARTITION
and VALUE
Usually, these methods are called in the order in which they are listed.
RANGE
and LIST
. The second argument takes a callback function.
DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){
$DRAFT::PARTITION_BY('RANGE', function(DRAFT $DRAFT){
//some code here
return $DRAFT;
});
})
PARTITION_BY()
(i.e RANGE or LIST) or it will automatically append a "COLUMN"
string to the partition set if it detects
an array within a supplied array argument. For example:
DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){ $DRAFT::PARTITION_BY('RANGE', fn() => $DRAFT::COLUMN('col1') ); })
DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){ $DRAFT::PARTITION_BY('RANGE', fn(DRAFT $DRAFT) => $DRAFT::COLUMN(['col1', 'col']) ); })
DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){ $DRAFT::PARTITION_BY('RANGE', function(DRAFT $DRAFT){ $DRAFT::COLUMN([['col1','col2'], ['col3','col4']]); return $DRAFT; }); })
RANGE
and RANGE COLUMN
above is that
in RANGE COLUMN
, the array argument supplied to COLUMN()
method also contains
array values.
PARTITION
method takes
two arguments. The first argument is the partition identifier name, while the second argument defines the logic used
for partitioning. This logic can be "LESS THAN" (or "VALUE LESS THAN") and "IN" (or "VALUES IN").
DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){ $DRAFT::PARTITION_BY('RANGE', fn() => $DRAFT::COLUMNS('col1') ->PARTITION('p0','VALUES LESS THAN'); ); })
DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){ $DRAFT::INT('number', '2')->NOT_NULL(); $DRAFT::PARTITION_BY('RANGE', fn(DRAFT $DRAFT) => $DRAFT::COLUMNS('number') ->PARTITION('p0','VALUES LESS THAN')->VALUE(500) ->PARTITION('p1','VALUES LESS THAN')->VALUE(300) ->PARTITION('p2','VALUES LESS THAN')->VALUE(200) ); })
VALUE
method can also take string as its parameter. An example is shown below:
DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){ $DRAFT::INT('number', '2')->NOT_NULL(); $DRAFT::DATETIME('date')->DEFAULT('(CURRENT_TIMESTAMP)'); $DRAFT::PARTITION_BY('RANGE', fn(DRAFT $DRAFT) => $DRAFT::COLUMNS('date') ->PARTITION('p0','VALUES LESS THAN')->VALUE(500) ->PARTITION('p1','VALUES LESS THAN')->VALUE(300) ->PARTITION('p2','VALUES LESS THAN')->VALUE(200) ); })
up()
and down()
methods are responsible for executing migrations
when we run certain commands from the command line.
>> php mi migration up
up()
method of each files in an ascending order.
We can also execute the down()
method by running the command below:
>> php mi migrate down
>> php mi migrate down 4
icore/dbconfig.php
file.
>> php mi migrate status