The Database Forge Class contains methods that help you manage your database. Important In order to initialize the Forge class, your database driver must already be running, since the Forge class relies on it. Load the Forge Class as
follows: You can also pass another database group name to the DB Forge loader, in case the database you want to manage isn’t the default one: <?php $this->myforge = \Config\Database::forge('other_db'); In the above example, we’re passing the name of a different database group to connect to as the first parameter. Creating and Dropping Databases$forge->createDatabase(‘db_name’)Permits you to create the database specified in the first parameter. Returns true/false based on success or failure: <?php if ($forge->createDatabase('my_db')) { echo 'Database created!'; } An optional second parameter set to true will add <?php $forge->createDatabase('my_db', true); /* * gives CREATE DATABASE IF NOT EXISTS `my_db` * or will check if a database exists */ $forge->dropDatabase(‘db_name’)Permits you to drop the database specified in the first parameter. Returns true/false based on success or failure: <?php if ($forge->dropDatabase('my_db')) { echo 'Database deleted!'; } Creating Databases in the Command LineCodeIgniter supports creating databases straight from your favorite terminal using the dedicated To start, just type the command and the name of the database (e.g., > php spark db:create foo If everything went fine, you should expect the If you are on a testing environment or you are using the SQLite3 driver, you may pass in the file extension for the file where the database will be created using the > php spark db:create foo --ext sqlite // will create the db file in WRITEPATH/foo.sqlite Note When using the special SQLite3 database name Creating and Dropping TablesThere are several things you may wish to do when creating tables. Add fields, add keys to the table, alter columns. CodeIgniter provides a mechanism for this. Adding FieldsFields are normally created via an associative array. Within the array, you must include a <?php $fields = [ 'users' => [ 'type' => 'VARCHAR', 'constraint' => 100, ], ]; // will translate to "users VARCHAR(100)" when the field is added. Additionally, the following key/values can be used:
<?php $fields = [ 'id' => [ 'type' => 'INT', 'constraint' => 5, 'unsigned' => true, 'auto_increment' => true, ], 'title' => [ 'type' => 'VARCHAR', 'constraint' => '100', 'unique' => true, ], 'author' => [ 'type' => 'VARCHAR', 'constraint' => 100, 'default' => 'King of Town', ], 'description' => [ 'type' => 'TEXT', 'null' => true, ], 'status' => [ 'type' => 'ENUM', 'constraint' => ['publish', 'pending', 'draft'], 'default' => 'pending', ], ]; After the fields have been defined, they can be added using $forge->addField()The add fields method will accept the above array. Raw Sql Strings as Default ValuesSince v4.2.0, <?php use CodeIgniter\Database\RawSql; $fields = [ 'id' => [ 'type' => 'INT', 'constraint' => 5, 'unsigned' => true, 'auto_increment' => true, ], 'created_at' => [ 'type' => 'TIMESTAMP', 'default' => new RawSql('CURRENT_TIMESTAMP'), ], ]; $forge->addField($fields); /* gives: "id" INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL */ Warning When you use Passing Strings as FieldsIf you know exactly how you want a field to be created, you can pass the string into the field definitions with <?php $forge->addField("label varchar(100) NOT NULL DEFAULT 'default label'"); Note Passing raw strings as fields cannot be followed by Note Multiple
calls to Creating an id fieldThere is a special exception for creating id fields. A field with type id will automatically be assigned as an INT(9) auto_incrementing Primary Key. <?php $forge->addField('id'); // gives `id` INT(9) NOT NULL AUTO_INCREMENT Adding KeysGenerally speaking, you’ll want your table to have Keys. This is accomplished with Multiple column non-primary keys must be sent as an array. Sample output below is for MySQL. <?php $forge->addKey('blog_id', true); // gives PRIMARY KEY `blog_id` (`blog_id`) $forge->addKey('blog_id', true); $forge->addKey('site_id', true); // gives PRIMARY KEY `blog_id_site_id` (`blog_id`, `site_id`) $forge->addKey('blog_name'); // gives KEY `blog_name` (`blog_name`) $forge->addKey(['blog_name', 'blog_label']); // gives KEY `blog_name_blog_label` (`blog_name`, `blog_label`) $forge->addKey(['blog_id', 'uri'], false, true); // gives UNIQUE KEY `blog_id_uri` (`blog_id`, `uri`) To make code reading more objective it is also possible to add primary and unique keys with specific methods: <?php $forge->addPrimaryKey('blog_id'); // gives PRIMARY KEY `blog_id` (`blog_id`) $forge->addUniqueKey(['blog_id', 'uri']); // gives UNIQUE KEY `blog_id_uri` (`blog_id`, `uri`) Adding Foreign KeysForeign Keys help to enforce relationships and actions across your tables. For tables that support Foreign Keys, you may add them directly in forge: <?php $forge->addForeignKey('users_id', 'users', 'id'); // gives CONSTRAINT `TABLENAME_users_foreign` FOREIGN KEY(`users_id`) REFERENCES `users`(`id`) $forge->addForeignKey(['users_id', 'users_name'], 'users', ['id', 'name']); // gives CONSTRAINT `TABLENAME_users_foreign` FOREIGN KEY(`users_id`, `users_name`) REFERENCES `users`(`id`, `name`) You can specify the desired action for the “on delete” and “on update” properties of the constraint: <?php $forge->addForeignKey('users_id', 'users', 'id', 'CASCADE', 'CASCADE'); // gives CONSTRAINT `TABLENAME_users_foreign` FOREIGN KEY(`users_id`) REFERENCES `users`(`id`) ON DELETE CASCADE ON UPDATE CASCADE $forge->addForeignKey(['users_id', 'users_name'], 'users', ['id', 'name'], 'CASCADE', 'CASCADE'); // gives CONSTRAINT `TABLENAME_users_foreign` FOREIGN KEY(`users_id`, `users_name`) REFERENCES `users`(`id`, `name`) ON DELETE CASCADE ON UPDATE CASCADE Creating a TableAfter fields and keys have been declared, you can create a new table with <?php $forge->createTable('table_name'); // gives CREATE TABLE table_name An optional second parameter set to true will create the table only if it doesn’t already exist. <?php $forge->createTable('table_name', true); // creates table only if table does not exist You could also pass
optional table attributes, such as MySQL’s <?php $attributes = ['ENGINE' => 'InnoDB']; $forge->createTable('table_name', false, $attributes); // produces: CREATE TABLE `table_name` (...) ENGINE = InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci Note Unless you specify the Dropping a TableExecute a <?php // Produces: DROP TABLE `table_name` $forge->dropTable('table_name'); // Produces: DROP TABLE IF EXISTS `table_name` $forge->dropTable('table_name', true); A third parameter can be passed to add a <?php // Produces: DROP TABLE `table_name` CASCADE $forge->dropTable('table_name', false, true); Dropping a Foreign KeyExecute a DROP FOREIGN KEY. <?php // Produces: ALTER TABLE `tablename` DROP FOREIGN KEY `users_foreign` $forge->dropForeignKey('tablename', 'users_foreign'); Dropping a KeyExecute a DROP KEY. <?php // Produces: DROP INDEX `users_index` ON `tablename` $forge->dropKey('tablename', 'users_index'); Renaming a TableExecutes a TABLE rename <?php $forge->renameTable('old_table_name', 'new_table_name'); // gives ALTER TABLE `old_table_name` RENAME TO `new_table_name` Modifying TablesAdding a Column to a Table$forge->addColumn()The <?php $fields = [ 'preferences' => ['type' => 'TEXT'], ]; $forge->addColumn('table_name', $fields); // Executes: ALTER TABLE `table_name` ADD `preferences` TEXT If you are using MySQL or CUBIRD, then you can take advantage of their Examples: <?php // Will place the new column after the `another_field` column: $fields = [ 'preferences' => ['type' => 'TEXT', 'after' => 'another_field'], ]; // Will place the new column at the start of the table definition: $fields = [ 'preferences' => ['type' => 'TEXT', 'first' => true], ]; Dropping Columns From a Table$forge->dropColumn()Used to remove a column from a table. <?php $forge->dropColumn('table_name', 'column_to_drop'); // to drop one single column Used to remove multiple columns from a table. <?php $forge->dropColumn('table_name', 'column_1,column_2'); // by proving comma separated column names $forge->dropColumn('table_name', ['column_1', 'column_2']); // by proving array of column names Modifying a Column in a Table$forge->modifyColumn()The usage of this method is identical to <?php $fields = [ 'old_name' => [ 'name' => 'new_name', 'type' => 'TEXT', ], ]; $forge->modifyColumn('table_name', $fields); // gives ALTER TABLE `table_name` CHANGE `old_name` `new_name` TEXT Class ReferenceclassCodeIgniter\Database\ Forge
addColumn ($table[, $field = []])
Adds a column to a table. Usage: See Adding a Column to a Table. addField ($field)
addForeignKey ($fieldName, $tableName, $tableField[, $onUpdate = '', $onDelete = ''])
Adds a foreign key to the set that will be used to create a table. Usage: See Adding Foreign Keys. addKey ($key[, $primary = false[, $unique =
false]])
Adds a key to the set that will be used to create a table. Usage: See Adding Keys. addPrimaryKey ($key)
Adds a primary key to the set that will be used to create a table. Usage: See Adding Keys. addUniqueKey ($key)
Adds a unique key to the set that will be used to create a table. Usage: See Adding Keys. createDatabase ($dbName[, $ifNotExists =
false])
Creates a new database. Usage: See Creating and Dropping Databases. createTable ($table[, $if_not_exists = false[, array $attributes =
[]]])
Creates a new table. Usage: See Creating a Table. dropColumn ($table, $column_name)
Drops single or multiple columns from a table. Usage: See Dropping Columns From a Table. dropDatabase ($dbName)
Drops a database. Usage: See Creating and Dropping Databases. dropTable ($table_name[, $if_exists =
false])
Drops a table. Usage: See Dropping a Table. modifyColumn ($table, $field)
Modifies a table column. Usage: See Modifying a Column in a Table. renameTable ($table_name,
$new_table_name)
Renames a table. Usage: See Renaming a Table. |