Update Script Database Structure Change

From Cloudrexx Development Wiki
Jump to: navigation, search


Check for table's existence

The method \Cx\Lib\UpdateUtil::table_exist($table) can be used to check for a certain table's existence. <highlightsyntax>if (\Cx\Lib\UpdateUtil::table_exist(DBPREFIX.'module_foo')) {

   // table contrexx_module_foo does exist

}</highlightsyntax>

Drop table

The method \Cx\Lib\UpdateUtil::drop_table($table) can be used to drop a database table. <highlightsyntax>// this will drop the table contrexx_module_foo \Cx\Lib\UpdateUtil::drop_table(DBPREFIX.'module_foo'); </highlightsyntax>

Check for column's existence

The method \Cx\Lib\UpdateUtil::column_exist($table, $column) can be used to check for a certain column within a table.

if (\Cx\Lib\UpdateUtil::column_exist(DBPREFIX.'module_foo', 'bar')) {
   // column contrexx_module_foo.bar is present
}

Check column's type

The method \Cx\Lib\UpdateUtil::check_column_type($table, $column, $type) can be used to verify a certain column's type. <highlightsyntax>if (\Cx\Lib\UpdateUtil::check_column_type(DBPREFIX.'module_foo', 'bar', 'varchar')) {

   // column contrexx_module_foo.bar is of type 'varchar'

}</highlightsyntax>

Change table structure

The method \Cx\Lib\UpdateUtil::table($table, $arrTableStructure, $arrIndexes, $engine, $comment, $constraints) can be used to manipulate the structure (incl. indexes) of a table. <highlightsyntax>/* $arrTableDefinition should be a multidimensional array with the following structure: $arrTableStructure = array( $table $arrTableDefinition, $arrIndexes $dbEngine $comment $constraints );

  • /

\Cx\Lib\UpdateUtil::table(

   // table name
   DBPREFIX.'module_foo',
   // columns
   array(
       'id'             => array('type' => 'INT(1)', 'notnull' => true, 'auto_increment' => true, 'primary' => true),
       'bar_id'         => array('type' => 'INT(11)', 'notnull' => true),
       'bar'            => array('type' => 'VARCHAR(255)', 'notnull' => true, 'default' => ),
       'type'           => array('type' => 'ENUM(\'left\',\'straight\',\'right\')', 'notnull' => true, 'default' => 'left'),
       'content'        => array('type' => 'LONGTEXT'),
       'recipients'     => array('type' => 'TEXT')
   ),
   // indexes
   array(
       'mail'           => array('fields' => array('type','lang_id'), 'type' => 'UNIQUE')
   ),
   // engine
   'InnoDB',
   // comment
   ,
   // constraints
   array(
       'bar_id'    => array(
           'table' => DBPREFIX.'module_bar',
           'column'    => 'id',
           'onDelete'  => 'CASCADE',
           'onUpdate'  => 'RESTRICT',
       ),
   ),

);</highlightsyntax>

Change constraints definition on table

The method \Cx\Lib\UpdateUtil::set_constraints($table, $arrConstraints) can be used to set the foreign key constraints on a table. <highlightsyntax>/* $arrConstraints should be an associative array where the keys represent the foreign keys and the values are arrays defining the constraint on the foreign keys: array(

   'foreign_key' => array(
       'table'     => 'foreign_table', # table of foreign key constraint
       'column'    => 'foreign_column', # table's column of foreign key constraint
       'onDelete'     => 'CASCADE|SET NULL|NO ACTION|RESTRICT', # constraint action on foreign relation' delete
       'onUpdate'     => 'CASCADE|SET NULL|NO ACTION|RESTRICT', # constraint action on foreign relation' update
   ),

) If left empty, all existing constraints will be removed from specified table

  • /

\Cx\Lib\UpdateUtil::set_constraints(

   // table name
   DBPREFIX.'module_foo',
   // constraints
   array(
       'bar_id'    => array(
           'table' => DBPREFIX.'module_bar',
           'column'    => 'id',
           'onDelete'  => 'CASCADE',
           'onUpdate'  => 'RESTRICT',
       ),
   ),

);</highlightsyntax>

Rename table column

<highlightsyntax> \Cx\Lib\UpdateUtil::table(

   DBPREFIX.'module_foo',
   array(
       'new_name'             => array('type' => 'INT(1)', 'notnull' => true, 'renamefrom' => 'old_name')
   )

);</highlightsyntax>

Add new column

<highlightsyntax> \Cx\Lib\UpdateUtil::table(

   DBPREFIX.'module_foo',
   array(
       'new_column'             => array('type' => 'INT(1)', 'notnull' => true, 'after' => 'existing_column')
   )

);</highlightsyntax>