DBUtil Class

The DBUtil class allows you to manage and perform routine operations on your databases.

Managing functions

DBUtil allows you to create, rename, alter and drop database fields.

create_database($database, charset = null)

The create_database method creates a database. Will throw a Database_Exception if it cannot.

Static Yes
Parameters
Param Default Description
$database required the database name
$charset null the default charset
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example
// Create a database named `my_database`
DBUtil::create_database('my_database');

// Catch the exception
try
{
	DBUtil::create_database('my_database');
}
catch(\Database_Exception $e)
{
	// Creation failed...
}

// You can also set a default charset.
// CREATE DATABASE `new_database` DEFAULT CHARACTER SET 'utf8'
DBUtil::create_database('new_database', 'utf8');

// CREATE DATABASE `new_database  DEFAULT CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci'
DBUtil::create_database('new_database', 'utf8_unicode_ci');

If no charset is provided it will fall back to the charset of the active db group.

drop_database($database)

The drop_database method drops a database. Will throw a Database_Exception if it cannot.

Static Yes
Parameters
Param Default Description
$database required the database name
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example
// Drop a database named `my_database`
DBUtil::drop_database('my_database');

// Catch the exception
try
{
	DBUtil::drop_database('my_database');
}
catch(\Database_Exception $e)
{
	// Drop failed...
}

drop_table($table)

The drop_table method drops a table. Will throw a Database_Exception if it cannot.

Static Yes
Parameters
Param Default Description
$table required the table name
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example
// Drop a table named `my_table`
DBUtil::drop_table('my_table');

// Catch the exception
try
{
	DBUtil::drop_table('my_table');
}
catch(\Database_Exception $e)
{
	// Drop failed...
}

rename_table($table, $new_table_name)

The rename_table method renames a table.

Static Yes
Parameters
Param Default Description
$table required The old table name
$new_table_name required The new table name
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example
// Rename `my_table` to `my_new_table`
DBUtil::rename_table('my_table', 'my_new_table');

// Catch the exception
try
{
	DBUtil::rename_table('my_table', 'my_new_table');
}
catch(\Database_Exception $e)
{
	// Rename failed...
}

create_table($table, $fields, $primary_keys = array(), $if_not_exists = true, $engine = false, $charset = null, $foreign_keys = array())

The create_table method creates a table.

Static Yes
Parameters
Param Default Description
$table required the table name
$fields required array containing fields
$primary_keys array() array containing primary keys
$if_not_exists true whether to use IF NOT EXISTS
$engine false which storage engine to use (MyISAM, InnoDB, ...)
$charset null the default charset, falls back to the active db group charset
$foreign_keys array() array of foreign key constraints definitions. The array keys 'key' and 'reference' are required and throw an error if missing, all others are optional.
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example
\DBUtil::create_table(
	'users',
	array(
		'id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true),
		'name' => array('type' => 'text'),
		'email' => array('constraint' => 50, 'type' => 'varchar'),
		'title' => array('constraint' => 50, 'type' => 'varchar', 'default' => 'mr.'),
		'password' => array('constraint' => 125, 'type' => 'varchar'),
	),
	array('id'), false, 'InnoDB', 'utf8_unicode_ci',
	array(
		array(
			'constraint' => 'constraintA',
			'key' => 'keyA',
			'reference' => array(
				'table' => 'table',
				'column' => 'field',
			),
			'on_update' => 'CASCADE',
			'on_delete' => 'RESTRICT'
		),
		array(
			'key' => 'keyB',
			'reference' => array(
				'table' => 'table',
				'column' => array(
					'fieldA',
					'fieldB'
				),
			),
		),
	),
);

/* produces the following SQL statement:
CREATE TABLE `users` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` text NOT NULL,
	`email` varchar(50) NOT NULL,
	`title` varchar(50) DEFAULT 'mr.' NOT NULL,
	`password` varchar(125) NOT NULL,
	PRIMARY KEY `id` (`id`),
	CONSTRAINT constraintA FOREIGN KEY (keyA) REFERENCES table (field) ON UPDATE CASCADE ON DELETE RESTRICT,
	FOREIGN KEY (keyB) REFERENCES table (fieldA, fieldB)
) ENGINE = InnoDB  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
*/
Possible parameters
Param Description
name the field name
type field type (varchar, enum, text, ...)
constraint Length or values.
charset field charset
unsigned boolean, true if is unsigned, defaults to false
default default value
null boolean, true if nullable, defaults to false
auto_increment set to true to use auto incrementing

Default fields values are escaped by default. In some cases you might want not to escape this value. If so, you can use DB::escape.

\DBUtil::create_table('users', array(
	'id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true),
	'name' => array('type' => 'text'),
	'email' => array('constraint' => 50, 'type' => 'varchar'),
	'title' => array('constraint' => 50, 'type' => 'varchar', 'default' => 'mr.'),
	'created' => array('type' => 'timestamp', 'default' => \DB::expr('CURRENT_TIMESTAMP')),
	'password' => array('constraint' => 125, 'type' => 'varchar'),
), array('id'));

Escaping values work the same for DBUtil::add_fields and DBUtil::modify_fields

add_fields($table, $fields)

The add_fields method adds fields to a table.

Static Yes
Parameters
Param Default Description
$table required the table name
$fields required array containing fields
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example
\DBUtil::add_fields('users', array(
	'surname' => array('constraint' => 100, 'type' => 'varchar'),
	'twitter_name' => array('constraint' => 100, 'type' => 'varchar'),
));

drop_fields($table, $fields)

The drop_fields method drops fields from a table.

Static Yes
Parameters
Param Default Description
$table required string of the table name
$fields required string or array containing fields
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example
\DBUtil::drop_fields('users', 'surname');

modify_fields($table, $fields)

The modify_fields method alters fields in a table.

Static Yes
Parameters
Param Default Description
$table required the table name
$fields required array containing fields
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example
\DBUtil::modify_fields('users2', array(
	'name' => array('constraint' => 100, 'type' => 'varchar', 'charset' => 'utf8_general_ci'),
	'title' => array('constraint' => 50, 'type' => 'varchar', 'default' => 'mrs.'),
));

create_index($table, $index_columns, $index_name, $index = '')

The create_index method allows you to create secondary indexes on a table.

Static Yes
Parameters
Param Default Description
$table required the table name
$index_columns required mixed, string or array of strings containing fields
$index_name required name of the index to be created
$index optional type of index to be created.
Currently supported: UNIQUE, FULLTEXT, SPATIAL, NONCLUSTERED
Returns Returns the result of the database operation.
Throws \Database_Exception on failure
Example
\DBUtil::create_index('table', 'name');
// produces CREATE INDEX name ON table ( `name` )

\DBUtil::create_index('table', array('nameA', 'nameB'), 'name');
// produces CREATE INDEX name ON table ( `nameA`, `nameB` )

\DBUtil::create_index('table', array('nameA' => 'ASC', 'nameB'), 'name', 'fulltext');
// produces CREATE FULLTEXT INDEX name ON table ( `nameA` ASC, `nameB` )

drop_index($table, $index_name)

The drop_index method allows you to drop a secondary index from a table.

Static Yes
Parameters
Param Default Description
$table required the table name
$index_name required name of the index to be dropped
Returns Returns the result of the database operation.
Throws \Database_Exception on failure
Example
\DBUtil::drop_index('table', 'name');
// produces DROP INDEX name ON table

Database operations

truncate_table($table)

The truncate_table method truncates a table.

Static Yes
Parameters
Param Default Description
$table required the table name.
Returns Returns the number of affected rows.
Throws \Database_Exception when not supported
Example
\DBUtil::truncate_table('my_table');

analyze_table($table)

The analyze_table method analyzes a table.

Static Yes
Parameters
Param Default Description
$table required the table name.
Returns True if the table is OK. False when needs attention. If not supported it logs the error message.
Example
if(\DBUtil::analyze_table('table_name') === false)
{
	// Do something
}

check_table($table)

The check_table method checks a table.

Static Yes
Parameters
Param Default Description
$table required the table name.
Returns True if the table is OK. False when needs attention. If not supported it logs the error message.
Example
if(\DBUtil::check_table('table_name') === false)
{
	// Do something
}

optimize_table($table)

The check_table method optimizes a table.

Static Yes
Parameters
Param Default Description
$table required the table name.
Returns True if the table is OK or optimized. False on failure. If not supported or failed it logs the error message.
Example
if(\DBUtil::optimize_table('table_name') === false)
{
	// Do something
}

repair_table($table)

The repair_table method repairs a table.

Static Yes
Parameters
Param Default Description
$table required the table name.
Returns True if the table is OK or repaired. false on failure. If not supported or failed it logs the error message.
Example
if(\DBUtil::repair_table('table_name') === false)
{
	// Do something
}