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.

set_connection($connection)

The set_connection method sets the default DBUtil connection for all methods.

Static Yes
Parameters
Param Default Description
$connection required The database connection.
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example 
// Set an alternative database connection for DBUtil
DBUtil::set_connection('my_database_group');

// Do other DBUtil calls with that connection.
...

// And set it back to default.
DBUtil::set_connection(null);

create_database($database, charset = null, $if_not_exists = true, $db = 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
$if_not_exists true whether to use IF NOT EXISTS
$db 
null
 The database connection.
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 IF NOT EXISTS `new_database` DEFAULT CHARACTER SET 'utf8'
DBUtil::create_database('new_database', 'utf8');

// CREATE DATABASE IF NOT EXISTS `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, $db = null)

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
$db 
null
 The database connection.
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...
}

table_exists($table, $db = null)

The table_exists method checks if a given table exists.

Static Yes
Parameters
Param Default Description
$table required the table name
$db 
null
 The database connection.
Returns Returns true if table exists, false if it does not.
Example 
// Check if table named 'my_table' exists
if(DBUtil::table_exists('my_table'))
{
    // Table exists
} else
{
    // Table does NOT exist, create it!
}

drop_table($table, $db = null)

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
$db 
null
 The database connection.
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, $db = null)

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(), $db = null)

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.
$db 
null
 The database connection.
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. Values can be provided in an array
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
comment adds a comment to your field

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

\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

field_exists($table, $columns, $db = null)

The field_exists method checks if given field(s) in a given table exists.

Static Yes
Parameters
Param Default Description
$table required the table name
$columns required array containing fields
$db 
null
 The database connection.
Returns Returns true if field/column exists, false otherwise
Example 

if(DBUtil::field_exists('my_table', array('my_field_or_column')))
{
    // Fields exist
}
else
{
    // Fields are not available on the table
}

add_fields($table, $fields, $db = null)

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
$db 
null
 The database connection.
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, $db = null)

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
$db 
null
 The database connection.
Returns Returns the number of affected rows.
Throws \Database_Exception on failure
Example 
\DBUtil::drop_fields('users', 'surname');

modify_fields($table, $fields, $db = null)

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
$db 
null
 The database connection.
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 = '', $db = null)

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 optional name of the index to be created
$index optional type of index to be created.
Currently supported: UNIQUE, FULLTEXT, SPATIAL, NONCLUSTERED
$db 
null
 The database connection.
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, $db = null)

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
$db 
null
 The database connection.
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

add_foregin_key($table, $foreign_key)

The add_foreign_key method allows you to add a foreign key to a table after its creation.

Static Yes
Parameters
Param Default Description
$table required the table name
$foreign_key required array containing the foreign key definition.
Returns Returns the result of the database operation.
Throws \InvalidArgumentException on invalid input, \Database_Exception on failure
Example 
\DBUtil::add_foreign_key('users', array(
    'constraint' => 'constraintA',
    'key' => 'keyA',
    'reference' => array(
        'table' => 'table',
        'column' => 'field',
    ),
    'on_update' => 'CASCADE',
    'on_delete' => 'RESTRICT'
));

drop_foreign_key($table, $fk_name)

The drop_foreign_key method allows you to drop a foreign key from a table.

Static Yes
Parameters
Param Default Description
$table required the table name
$fk_name required name of the foreign key to be dropped
Returns Returns the result of the database operation.
Throws \Database_Exception on failure
Example 
\DBUtil::drop_foreign_key('table', 'name');
// produces ALTER TABLE `table` DROP FOREIGN KEY name

Database operations

truncate_table($table, $db = null)

The truncate_table method truncates a table.

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

analyze_table($table, $db = null)

The analyze_table method analyzes a table.

Static Yes
Parameters
Param Default Description
$table required the table name.
$db 
null
 The database connection.
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, $db = null)

The check_table method checks a table.

Static Yes
Parameters
Param Default Description
$table required the table name.
$db 
null
 The database connection.
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, $db = null)

The optimize_table method optimizes a table.

Static Yes
Parameters
Param Default Description
$table required the table name.
$db 
null
 The database connection.
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, $db = null)

The repair_table method repairs a table.

Static Yes
Parameters
Param Default Description
$table required the table name.
$db 
null
 The database connection.
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
}