Database migrations

Database : Migrations

Migration files are files that enable us to create or modify our database table in the format that is desirable for our project applications. Spoova has a simple migration tool that makes it easy to perform tasks like table creation, alteration or modification. Migration files are usually generated from the command line by running some specific commands that generates migration files.

Generating migration files

Migration files can be generated with the command line by running the following commands:

  php mi add:migrator file_name

Using the syntax above, the "file_name" is generated into the 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.

Example : Migration file sample

  <?php 

    class M1673742992_file_name {

       function up() {
 
       } 

       function down() {

       }
 
       function tablename() : string {
          return 'some_table';
       }

    }

The structure above resembles a full format of a migration file. While the 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.

Migration Schema

The 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:

The DBSCHEMA::CREATE() method is used to generate or create database tables

The DBSCHEMA::ALTER() method is used to alter or modify existing database tables

The DBSCHEMA::DROP_TABLE() method is the only way to drop a table.

The DBSCHEMA::DROP_FIELD() method is the easiest way to drop a table's column outside the DB::ALTER() scope.

Migration Draft

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){

          });

       }

    }

We can also supply an object as the first argument of 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';

       }

    }

Data type methods

These methods helps to define the type of a field. The can be applied either within the 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()

Constraint related methods

These methods are mostly used to set constraints on tables. They are non-static methods that can be chained on data type method using valid chain structures. These methods include:

DEFAULT(), NULL(), NOT_NULL(), SIGNED(), UNSIGNED(), INDEX(), UNIQUE(), FOREIGN_KEY(), PRIMARY_KEY(), CONSTRAINT(), AUTO() or AUTO_INCREMENT(), CASCADE(), RESTRICT()

Modifier methods

Similarly to data type methods, the modifier methods are static methods that are only defined within the 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;
        
      } );

    });

The structure above defines the format which is used to modify a column's data type only. Since it modifies only one column, we can reformat our code as below:

    DBSCHEMA::ALTER($this, function(DRAFT $DRAFT){

      $DRAFT::MODIFY( fn() => $DRAFT::DATA_TYPE() );

    });

Example

    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

    });

Setting column data type

The data type methods above sets the data type of field. The first argument in most cases is the name of the field to be altered or created. However the 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)

Arguments are defined by the scope in which they are used or the type of field being created. Each of these arguments are explained below:

$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] ]

Options such as 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.

The ID() Data Type Method

The 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"

Setting database column constraints

Constraint methods are used to apply contraints on table columns. These constraint can be applied during table creation or modification on tables or generated table columns. These methods DEFAULT(), NULL, NOT_NULL(), PRIMARY_KEY(), FOREIGN_KEY(), SIGNED(), UNSIGNED(), CONSTRAINT(), AUTO_INCREMENT() are listed and explained below:

DEFAULT CONSTRAINT

The method 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.

Defining special strings

Special strings are known as unquoted strings which may be a constant or function. In order to separate normal strings from special string, the special strings must be wrapped within a parenthesis (i.e "(value)"). Any value placed within the parenthesis is treated as a special string. In this way, setting a default "CURRENT_TIMESTAMP" constant will be defined as 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.

NULL and NOT NULL CONSTRAINT

The method 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 AND UNSIGNED CONSTRAINT

The 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

The 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.

UNIQUE CONSTRAINT

A unique constraint can be set on a single field by chaining it on a any of the data type methods. In this case, no argument is required because the current field is assumed to be the unique field. However, to set multiple fields as unique fields, they must be called on the 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 CONSTRAINT

In order to define a field as a primary key field, the 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 (or AUTO_INCREMENT) CONSTRAINT

The AUTO() is a short alias for AUTO_INCREMENT() which defines the automatic increment feature on a primarily defined integer column.

FOREIGN KEY CONSTRAINT

This method is used to apply a foreign key constraint on two tables. In this case, the first argument is expected to be the parent table's name while the second argument is an array which contains an index and relative value. The index is expected to be the foreign key (or column) name in the current migration table while the value is the local key (or column) name on the parent table

INDEX CONSTRAINT

The INDEX() method sets an index constraint on a table's field.

Creating new table Format

The creation of new tables are handled by the DBSCHEMA::CREATE() scope using the DRAFT data type chaining structures.

Sample: Creating a table

    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;

    })

The format above is a sample of table columns definition format.

Adding Fields To Existing Table

Most of the data type method can be applied to add more fields to a table or to add indexes to table. Since the 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;

    })

Dropping Fields or Indexes From Existing Table

The 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;

    })

Dropping an Existing Table

From the DRAFT class the DROP(true) or DROP_TABLE() can be used to drop the current table.

Drop current table users

    DBSCHEMA::DROP_TABLE('users'); // drop current table "users".

Renaming an Existing Table

The migration engine can also be used to rename a table through the DRAFT::RENAME() method.

    DBSCHEMA::ALTER('users', function(DRAFT $DRAFT){

      $DRAFT::RENAME_TO('user_table'); // rename current table "users" to "user_table"

      return $DRAFT;
        
    })

Renaming an Existing Table's Field

The 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;

        }); 
        
    })

In the code above we are able to change the fields names and data types through the 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.

Converting Table Character Type

A table's character type can be converted to another character type by the use of the DRAFT::CONVERT_TO() method.

    DBSCHEMA::ALTER('users', function(DRAFT $DRAFT){

      $DRAFT::CONVERT_TO('latin 1'); //set current table character type to latin
        
    })

Table Partitioning

The migration files can also be used to set up table partitions. Partitioning is mostly done by through the use of partitioning methods which are: PARTITION_BY, COLUMNS, PARTITION and VALUE Usually, these methods are called in the order in which they are listed.

PARTITION_BY

This method set the type of partition that is expected to be used to partition the tables. Valid options are RANGE and LIST. The second argument takes a callback function.

Example of usage

    DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){

      $DRAFT::PARTITION_BY('RANGE', function(DRAFT $DRAFT){

        //some code here
        return $DRAFT;

      });

    })

COLUMNS

This specifies allows the definition of partition types as columns. For example, through this method we can have a type of "RANGE COLUMN" and "LISTS COLUMN". It also uses specific structures to determine if a partition is "RANGE" or "RANGE COLUMN". The first argument which is expected to be an array will either set a partition type as the type defined through the 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:

Example 1a: Type of RANGE

    DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){

      $DRAFT::PARTITION_BY('RANGE', fn() => $DRAFT::COLUMN('col1') );

    })

Example 1b: Type of RANGE

    DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){

        $DRAFT::PARTITION_BY('RANGE', fn(DRAFT $DRAFT) =>

          $DRAFT::COLUMN(['col1', 'col'])

        );

    })

Example 2: Type of RANGE COLUMN

    DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){

        $DRAFT::PARTITION_BY('RANGE', function(DRAFT $DRAFT){

          $DRAFT::COLUMN([['col1','col2'], ['col3','col4']]);

          return $DRAFT;

        });

    })

The only difference between the RANGE and RANGE COLUMN above is that in RANGE COLUMN, the array argument supplied to COLUMN() method also contains array values.

PARTITION

This method is used to set the identifier names of each partition specified. By setting the partitioning names, the database table can easily be queried using the specified partitions. The 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").

Example of usage

    DBSCHEMA::CREATE('tablename', function(DRAFT $DRAFT){

        $DRAFT::PARTITION_BY('RANGE', fn() =>

          $DRAFT::COLUMNS('col1')

               ->PARTITION('p0','VALUES LESS THAN');

        );

    })

VALUE

This method the value or list of values that is used to separate a table. The values defined within this method are the values by which the table will be partitioned.

Example of usage

    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)

        );

    })

Note that the 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)

        );

    })

Executing migration files

The migration files up() and down() methods are responsible for executing migrations when we run certain commands from the command line.

Running migrations up

  >> php mi migration up

The command above will execute all migration files by calling the up() method of each files in an ascending order. We can also execute the down() method by running the command below:

Running migrations down

  >> php mi migrate down

In the cases where we want to run the migration files down in a specific number of times, we can also specify the number of times we want to run down (or reverse) the migration files just as shown below:

Running migrations down in number of times

  >> php mi migrate down 4

The command above will step down migration files starting from the last executed migration file and ending in the file that occupies the number of times specified. This stepping down system is only applied for a down migration. Also, note that the connection used depends on the configuration file settings defined in the icore/dbconfig.php file.

Getting migration status

  >> php mi migrate status

The command above tries to fetch the migration status from the database reserved migrations table. The status table is only shown if the database connection is successful and the migrations table has been generated.