You are currently browsing Crystal’s documentation for the 0.4 version - Switch to version: 0.3

Database manipulation

The Database manipulation module contains functions that help you manage your database.

Initializing the database manipulation module

Load the module as follows:

 $manipulation = Crystal::manipulation();

Creating and Dropping Databases

create_database(database name)

Permits you to create the specified database.

 $manipulation->create_database('database_name')->execute();

drop_database(database name)

Permits you to drop the specified database.

 $manipulation-> drop_database('database_name')->execute();

Creating and Dropping Tables

create_table(table name, table options)->with_fields('fields')

Creating tables in Crystal is optimized for readability. Before you can create a new table, you must declare fields and keys.

$fields = array('id' => array('type' => 'int', 'auto_increment' => TRUE, 'unsigned' => TRUE, 'primary_key' => TRUE),
   		'title' => array('type' => 'varchar', 'constraint' => '128'),
		'content' => array('type' => 'text')	 
		);
$table_options = array('engine' => 'MYISAM', 'char_set' => 'utf8','collation' => 'utf8_general_ci');
   
$manipulation->create_table('test_table', $table_options)->with_fields($fields)->execute();

Table options

engine - The engine for the table.

char_set -The character set for the table.

collation - The character collation for the table.

Fields options

type - The datatype of the field. For example varchar, text, enum, etc.

 
//Crystal supports a special type - 'id'. It defines the relevant primary key for the database type.
$fields = array('language_id' => array('type' => 'id', 'primary_key' => TRUE));
// In MySQL this will generate id NOT NULL AUTO_INCREMENT 
// In PostgreSQL the same fields will generate id serial NOT NULL
   
$manipulation->create_table('my_table')->with_fields($fields)->execute();

constraint - Many datatypes (for example VARCHAR) require a 'constraint' key.

auto_increment - Generates an auto_increment flag on the field.

unsigned - Generates "UNSIGNED" in the field definition

default - Generates a default value in the field definition.

primary_key - Generates an primary ket flag on the field.

choises - Generates an ENUM flag on the field.

 
$fields = array('language_type' => array('type' => 'enum', 'choises' => array('main', 'additional'), 'default' => 'main'));
   
$manipulation->alter_table('my_table')->add_fields($fields)->execute();
// Executes ALTER TABLE `my_table` ADD `language_type` ENUM ( 'main' , 'additional' ) NOT NULL DEFAULT 'main' 

in_database(database name)

Creates the table in the specified database. Without this function Crystal uses the database from the configuration file.

$manipulation->create_table('test_table', $table_options)with_fields($fields)->in_database('database_name')->execute();

drop_table(table name)

Creates a DROP TABLE sql. You can specify in_database if you need to delete table in different from the described in the configuration file database.

   
$manipulation->drop_table('test_table')->execute();
//Executes DROP TABLE IF EXISTS `test_table`

$manipulation->drop_table('test_table')->in_database('another_database')->execute();
// Executes DROP TABLE IF EXISTS `test_table` in the database: another_database

Modifying Tables

You can add fields to existing table using the alter_table() function

alter_table(table name)->add_fields(fields)

The alter_table() function is used to modify an existing table. You can add unlimited number of additional fields with the function add_fields().

$fields = array('date' => array('type' => 'date'));

$manipulation->alter_table('test_table')->add_fields($fields)->execute();
// gives ALTER TABLE test_table ADD `date` DATE NOT NULL 

If you need different database add the in_database() function.

$fields = array('date' => array('type' => 'date'));

$manipulation->alter_table('test_table')->in_database('backup_database')->add_fields($fields)->execute(); 

You can remove fields with the remove_fields() function like this:

alter_table(table name)->remove_fields(fields)

$remove = array('title', 'content');

$manipulation->alter_table('test_table')->remove_fields($fields)->execute();
// gives ALTER TABLE `test_table ` DROP `title` ,DROP `content ` 

$manipulation->alter_table('test_table')->in_database('database_name')->remove_fields($fields)->execute();
// gives ALTER TABLE `test_table ` DROP `title` ,DROP `content ` in the database 'database_name'

rename_table(old table name, new table name)

Executes a table rename

   
$manipulation->rename_table('test_table','another_table')->execute();
// gives ALTER TABLE test_table RENAME TO another_table

$manipulation->rename_table('test_table','another_table')->in_database('another_database')->execute();
// gives ALTER TABLE test_table RENAME TO another_table in the specified database