Migration Methods

Overview of the migration methods available

Introduction

All migrations inherent from the Ruckusing_BaseMigration class, so they must be prefixed with $this (e.g. $this->add_column().

The available methods are (brief list below, with detailed usageg further down):

Database-level operations

• create_database
• drop_database

Table-level operations

• create_table
• drop_table
• rename_table

Column-level operations

• add_column
• remove_column
• rename_column
• change_column

Index-level operations

• add_index
• remove_index

Query execution

• execute
• select_one
• select_all

Database-level Operations

There are two database-level operations, create_database and drop_database. Migrations that manipulate databases on this high of a level are used rarely.

Creating a new Database

This command is slightly useless since normally you would be running your migrations against an existing database (created and setup with whatever your traditional RDMBS creation methods are). However, if you wanted to create another database from a migration, this method is available:

Method Call: create_database

Parameters name : Name of the new database

Example:

    $this->create_database("my_project");

Removing a database

To completely remove a database and all of its tables (and data!).

Method Call: drop_database

Parameters name : Name of the existing database

Example:

    $this->drop_database("my_project");

This method is probably the most complex of all methods, but also one of the most widely used.

Method Call: create_table

Parameters

name : Name of the new table

options : (Optional) An associative array of options for creating the new table.

Supported option key/value pairs are:

id : Boolean - whether or not the framework should automatically generate a primary key. For MySQL the column will be called id and be of type integer with auto-incrementing.

options : A string representing finalization parameters that will be passed verbatim to the tail of the create table command. Often this is used to specify the storage engine for MySQL, e.g. 'options' => 'Engine=InnoDB'

Assumptions Ultimately this method delegates to the appropriate RDMBS adapter and the MySQL adapter makes some important assumptions about the structure of the table.

Table-level operations

The database migration framework offers a rich facility for creating, removing and renaming tables.

Creating tables

A call to $this->create_table(...) actually returns a TableDefinition object. This method of the framework is one of the very few which actually returns a result that you must interact with (as and end user).

The steps for creating a new table are:

• Create the table with a name and any optional options and store the return value for later use:

    $users = $this->create_table("users");

• Add columns to the table definition:

    $users->column("first_name", "string");
    $users->column("last_name", "string");

• Call finish() to actually create the table with the definition and its columns:

    $users->finish();

By default, the table type will be what your database defaults too. To specify a different table type (e.g. InnoDB), pass a key of options into the $options array, e.g.

Example A: Create a new InnoDB table called users.

    $this->create_table('users', array('options' => 'Engine=InnoDB'));

• This command also assumes that you want an id column. This column does not need to be specified, it will be auto-generated, unless explicitly told not to via the id key in $options array.

Example B: Create a new table called users but do not automatically make a primary key.

    $this->create_table('users', array('id' => false));

The primary key column will be created with attributes of int(11) unsigned auto_increment.

Example C: To specify your own primary key called 'guid':

    $t = $this->create_table('users', array('id' => false, 'options' => 'Engine=InnoDB'));
    $t->column('guid', 'string', array('primary_key' => true, 'limit' => 64));
    $t->finish();

Removing tables

Tables can be removed by using the drop_table method call. As might be expected, removing a table also removes all of its columns and any indexes.

Method Call: drop_table

Arguments:: table_name: The name of the table to remove.

Renaming tables

Tables can be renamed using the rename_table method.

Method Call: rename_table

Arguments:: table_name: The existing name of the table. new_name: The new name of the table.

Column-level operations

Adding a new column to a table

For the complete documentation on adding new columns, please see Adding Columns.

Removing Columns

Removing a database column is very simple, but keep in mind that any index associated with that column will also be removed.

Method call: remove_column

Arguments table_name: The name of the table from which the column will be removed.

column_name: The column to be removed.

Example A:: Remove the age column from the users table.

    $this->remove_column("users", "age"); 

Renaming a column

Database columns can be renamed (assuming the underlying RDMBS/adapter supports it).

Method call: rename_column

Arguments: table_name: The name of table from which the column is to be renamed.

column_name: The existing name of the column.

new_column_name: The new name of the column.

Example A: From the users table, rename first_name to fname

    $this->rename_column("users", "first_name", "fname");

Modifying an existing column

The type, defaults or NULL support for existing columns can be modified. If you want to just rename a column then use the rename_column method. This method takes a generalized type for the column's type and also an array of options which affects the column definition. For the available types and options, see the documentation on adding new columns, AddingColumns.

Method Call: change_column

Arguments: table_name: The name of the table from which the column will be altered.

column_name: The name of the column to change.

type: The desired generalized type of the column.

options: (Optional) An associative array of options for the column definition.

Example A: From the users table, change the length of the first_name column to 128.

    $this->change_column("users", "first_name", "string", array('limit' => 128) );

Index-level operations

Indexes can be created and removed using the framework methods.

Adding a new index

Method Call: add_index

Arguments: table: The name of the table to add the index to.

column: The column to create the index on. If this is a string, then it is presumed to be the name of the column, and the index will be a single-column index. If it is an array, then it is presumed to be a list of columns name and the index will then be a multi-column index, on the columns specified.

options: (Optional) An associative array of options to control the index generation. Keys / Value pairs:

unique: values: true or false. Whether or not create a unique index for this column. Defaults to false.

name : values: user defined. The name of the index. If not specified, a default name will be generated based on the table and column name.

Known Issues / Workarounds: MySQL is currently limited to 64 characters for identifier names. When add_index is used without specifying the name of the index, Ruckusing will generate a suitable name based on the table name and the column(s) being index. For example, if there is a users table and an index is being generated on the username column then the generated index name would be: idx_users_username . If one is attempting to add a multi-column index then its very possible that the generated name would be longer than MySQL's limit of 64 characters. In such situations Ruckusing will raise an error suggesting you use a custom index name via the name option parameter. See Example C.

Example A: Create an index on the email column in the users table.

    $this->add_index("users", "email");

Example B: Create a unique index on the ssn column in the users table.

    $this->add_index("users", "ssn", array('unique' => true)));

Example C: Create an index on the blog_id column in the posts table, but specify a specific name for the index.

    $this->add_index("posts", "blog_id", array('name' => 'index_on_blog_id'));

Example D: Create a multi-column index on the email and ssn columns in the users table.

    $this->add_index("users", array('email', 'ssn') );

Removing an index

Easy enough. If the index was created using the sibling to this method (add_index) then one would need to just specify the same arguments to that method (but calling remove_index).

Method Call: remove_index

Arguments: table_name: The name of the table to remove the index from.

column_name: The name of the column from which to remove the index from.

options: (Optional) An associative array of options to control the index removal process. Key / Value pairs: name : values: user defined. The name of the index to remove. If not specified, a default name will be generated based on the table and column name. If during the index creation process (using the add_index method) and a name is specified then you will need to do the same here and specify the same name. Otherwise, the default name that is generated will likely not match with the actual name of the index.

Example A: Remove the (single-column) index from the users table on the email column.

    $this->remove_index("users", "email");

Example B: Remove the (multi-column) index from the users table on the email and ssn columns.

    $this->remove_index("users", array("email", "ssn") );

Example C: Remove the (single-column) named index from the users table on the email column.

    $this->remove_index("users", "email", array('name' => "index_on_email_column") );

Query Execution

Arbitrary query execution is available via a set of methods.

Execute method

The execute() method is intended for queries which do not return any data, e.g. INSERT, UPDATE or DELETE.

Example A: Update all rows give some criteria

    $this->execute("UPDATE foo SET name = 'bar' WHERE .... ");

Queries that return results

For queries that return results, e.g. SELECT queries, then use either select_one or select_all depending on what you are returning.

Both of these methods return an associative array with each element of the array being itself another associative array of the column names and their values.

select_one() is intended for queries where you are expecting a single result set, and select_all() is intended for all other cases (where you might not necessarily know how many rows you will be getting).

NOTE: Since these methods take raw SQL queries as input, they might not necessarily be portable across all RDBMS.

Example A (select_one): Get the sum of of a column

    $result = $this->select_one("SELECT SUM(total_price) AS total_price FROM orders");
    if($result) {
     echo "Your revenue is: " . $result['total_price'];
    }

**Example B (select_all): **: Get all rows and iterate over each one, performing some operation:

    $result = $this->select_all("SELECT email, first_name, last_name FROM users WHERE created_at >= SUBDATE( NOW(), INTERVAL 7 DAY)");

    if($result) {
      echo "New customers: (" . count($result) . ")\n";
      foreach($result as $row) {
        printf("(%s) %s %s\n", $row['email'], $row['first_name'], $row['last_name']);
      }
    }