Building Schema Manipulation Queries

Besides normal data retrieval and manipulation queries, the query builder also offers a set of methods for building and executing SQL queries that can manipulate the schema of a database. In particular, it supports the following queries:

• createTable(): creates a table • renameTable(): renames a table • dropTable(): drops a table

• truncateTable(): truncates a table • addColumn(): adds a table column

4.3 Query Builder 99

• renameColumn(): renames a table column • alterColumn(): alters a table column

• addForeignKey(): adds a foreign key (available since 1.1.6) • dropForeignKey(): drops a foreign key (available since 1.1.6) • dropColumn(): drops a table column

• createIndex(): creates an index • dropIndex(): drops an index

Info: Although the actual SQL statements for manipulating database schema vary widely across different DBMS, the query builder attempts to provide a uniform interface for building these queries. This simplifies the task of migrating a database from one DBMS to another.

Abstract Data Types

The query builder introduces a set of abstract data types that can be used in defining table columns. Unlike the physical data types that are specific to particular DBMS and are quite different in different DBMS, the abstract data types are independent of DBMS. When abstract data types are used in defining table columns, the query builder will convert them into the corresponding physical data types.

The following abstract data types are supported by the query builder.

• pk: a generic primary key type, will be converted into int(11) NOT NULL AUTO INCREMENT PRIMARY KEYfor MySQL;

• string: string type, will be converted intovarchar(255) for MySQL; • text: text type (long string), will be converted into text for MySQL; • integer: integer type, will be converted into int(11)for MySQL; • float: floating number type, will be converted into floatfor MySQL; • decimal: decimal number type, will be converted intodecimal for MySQL; • datetime: datetime type, will be converted into datetimefor MySQL;

• timestamp: timestamp type, will be converted intotimestampfor MySQL; • time: time type, will be converted into time for MySQL;

• date: date type, will be converted intodate for MySQL;

• binary: binary data type, will be converted intoblob for MySQL; • boolean: boolean type, will be converted intotinyint(1) for MySQL;

• money: money/currency type, will be converted intodecimal(19,4)for MySQL. This type has been available since version 1.1.8.

createTable()

function createTable($table, $columns, $options=null)

ThecreateTable()method builds and executes a SQL statement for creating a table. The $table parameter specifies the name of the table to be created. The $columnsparameter specifies the columns in the new table. They must be given as name-definition pairs (e.g. ’username’=>’string’). The $options parameter specifies any extra SQL fragment that should be appended to the generated SQL. The query builder will quote the table name as well as the column names properly.

When specifying a column definition, one can use an abstract data type as described above. The query builder will convert the abstract data type into the corresponding physical data type, according to the currently used DBMS. For example, string will be converted into varchar(255) for MySQL.

A column definition can also contain non-abstract data type or specifications. They will be put in the generated SQL without any change. For example,point is not an abstract data type, and if used in a column definition, it will appear as is in the resulting SQL; andstring NOT NULLwill be converted intovarchar(255) NOT NULL(i.e., only the abstract type stringis converted).

Below is an example showing how to create a table:

// CREATE TABLE ‘tbl user‘ (

// ‘id‘ int(11) NOT NULL AUTO INCREMENT PRIMARY KEY,

// ‘username‘ varchar(255) NOT NULL,

// ‘location‘ point

// ) ENGINE=InnoDB

4.3 Query Builder 101

’id’ => ’pk’,

’username’ => ’string NOT NULL’,

’location’ => ’point’,

), ’ENGINE=InnoDB’)

renameTable()

function renameTable($table, $newName)

The renameTable() method builds and executes a SQL statement for renaming a table. The $table parameter specifies the name of the table to be renamed. The $newName parameter specifies the new name of the table. The query builder will quote the table names properly.

Below is an example showing how to rename a table:

// RENAME TABLE ‘tbl users‘ TO ‘tbl user‘

renameTable(’tbl users’, ’tbl user’)

dropTable()

function dropTable($table)

The dropTable()method builds and executes a SQL statement for dropping a table. The $table parameter specifies the name of the table to be dropped. The query builder will quote the table name properly.

Below is an example showing how to drop a table:

// DROP TABLE ‘tbl user‘

dropTable(’tbl user’)

truncateTable()

function truncateTable($table)

ThetruncateTable() method builds and executes a SQL statement for truncating a table. The $tableparameter specifies the name of the table to be truncated. The query builder will quote the table name properly.

Below is an example showing how to truncate a table:

// TRUNCATE TABLE ‘tbl user‘

truncateTable(’tbl user’)

addColumn()

function addColumn($table, $column, $type)

The addColumn() method builds and executes a SQL statement for adding a new table column. The $table parameter specifies the name of the table that the new column will be added to. The $column parameter specifies the name of the new column. And $type specifies the definition of the new column. Column definition can contain abstract data type, as described in the subsection of ”createTable”. The query builder will quote the table name as well as the column name properly.

Below is an example showing how to add a table column:

// ALTER TABLE ‘tbl user‘ ADD ‘email‘ varchar(255) NOT NULL

addColumn(’tbl user’, ’email’, ’string NOT NULL’)

dropColumn()

function dropColumn($table, $column)

The dropColumn() method builds and executes a SQL statement for dropping a table column. The $table parameter specifies the name of the table whose column is to be dropped. The $column parameter specifies the name of the column to be dropped. The query builder will quote the table name as well as the column name properly.

Below is an example showing how to drop a table column:

// ALTER TABLE ‘tbl user‘ DROP COLUMN ‘location‘

dropColumn(’tbl user’, ’location’)

renameColumn()

4.3 Query Builder 103

The renameColumn()method builds and executes a SQL statement for renaming a table column. The $table parameter specifies the name of the table whose column is to be renamed. The$nameparameter specifies the old column name. And$newNamespecifies the new column name. The query builder will quote the table name as well as the column names properly.

Below is an example showing how to rename a table column:

// ALTER TABLE ‘tbl users‘ CHANGE ‘name‘ ‘username‘ varchar(255) NOT NULL

renameColumn(’tbl user’, ’name’, ’username’)

alterColumn()

function alterColumn($table, $column, $type)

The alterColumn() method builds and executes a SQL statement for altering a table column. The $table parameter specifies the name of the table whose column is to be altered. The $column parameter specifies the name of the column to be altered. And $type specifies the new definition of the column. Column definition can contain abstract data type, as described in the subsection of ”createTable”. The query builder will quote the table name as well as the column name properly.

Below is an example showing how to alter a table column:

// ALTER TABLE ‘tbl user‘ CHANGE ‘username‘ ‘username‘ varchar(255) NOT NULL

alterColumn(’tbl user’, ’username’, ’string NOT NULL’)

addForeignKey()

function addForeignKey($name, $table, $columns,

$refTable, $refColumns, $delete=null, $update=null)

The addForeignKey() method builds and executes a SQL statement for adding a foreign key constraint to a table. The $name parameter specifies the name of the foreign key. The $table and $columns parameters specify the table name and column name that the foreign key is about. If there are multiple columns, they should be separated by comma characters. The$refTableand$refColumnsparameters specify the table name and column name that the foreign key references. The$deleteand $updateparameters specify theON DELETE and ON UPDATE options in the SQL statement, respectively. Most DBMS support

these options: RESTRICT, CASCADE, NO ACTION, SET DEFAULT, SET NULL. The query builder will properly quote the table name, index name and column name(s).

Below is an example showing how to add a foreign key constraint,

// ALTER TABLE ‘tbl profile‘ ADD CONSTRAINT ‘fk profile user id‘ // FOREIGN KEY (‘user id‘) REFERENCES ‘tbl user‘ (‘id‘)

// ON DELETE CASCADE ON UPDATE CASCADE

addForeignKey(’fk profile user id’, ’tbl profile’, ’user id’,

’tbl user’, ’id’, ’CASCADE’, ’CASCADE’)

dropForeignKey()

function dropForeignKey($name, $table)

ThedropForeignKey()method builds and executes a SQL statement for dropping a foreign key constraint. The $name parameter specifies the name of the foreign key constraint to be dropped. The$table parameter specifies the name of the table that the foreign key is on. The query builder will quote the table name as well as the constraint names properly. Below is an example showing how to drop a foreign key constraint:

// ALTER TABLE ‘tbl profile‘ DROP FOREIGN KEY ‘fk profile user id‘

dropForeignKey(’fk profile user id’, ’tbl profile’)

createIndex()

function createIndex($name, $table, $column, $unique=false)

The createIndex() method builds and executes a SQL statement for creating an index. The$nameparameter specifies the name of the index to be created. The$tableparameter specifies the name of the table that the index is on. The $column parameter specifies the name of the column to be indexed. And the $unique parameter specifies whether a unique index should be created. If the index consists of multiple columns, they must be separated by commas. The query builder will properly quote the table name, index name and column name(s).

Below is an example showing how to create an index:

// CREATE INDEX ‘idx username‘ ON ‘tbl user‘ (‘username‘)