DBUtil Class
The DBUtil class allows you to manage and perform routine operations on your databases.
DBUtil allows you to create, rename, alter and drop database fields.
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 |
DBUtil::set_connection('my_database_group');
...
DBUtil::set_connection(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 |
DBUtil::create_database('my_database');
try
{
DBUtil::create_database('my_database');
}
catch(\Database_Exception $e)
{
}
DBUtil::create_database('new_database', 'utf8');
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.
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 |
DBUtil::drop_database('my_database');
try
{
DBUtil::drop_database('my_database');
}
catch(\Database_Exception $e)
{
}
|
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 |
if(DBUtil::table_exists('my_table'))
{
} else
{
}
|
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 |
DBUtil::drop_table('my_table');
try
{
DBUtil::drop_table('my_table');
}
catch(\Database_Exception $e)
{
}
|
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 |
DBUtil::rename_table('my_table', 'my_new_table');
try
{
DBUtil::rename_table('my_table', 'my_new_table');
}
catch(\Database_Exception $e)
{
}
|
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'
),
),
),
),
);
|
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
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')))
{
}
else
{
}
|
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'),
));
|
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');
|
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.'),
));
|
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');
\DBUtil::create_index('table', array('nameA', 'nameB'), 'name');
\DBUtil::create_index('table', array('nameA' => 'ASC', 'nameB'), 'name', 'fulltext');
|
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');
|
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'
));
|
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');
|
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');
|
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)
{
}
|
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)
{
}
|
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)
{
}
|
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)
{
}
|