View source for Update Script Database Structure Change

[[Category:Bug Fixing]]
[[Category:Update Script]]

== Check for table's existence ==
The method <code>\Cx\Lib\UpdateUtil::table_exist($table)</code> can be used to check for a certain table's existence.
<syntaxhighlight lang=php>if (\Cx\Lib\UpdateUtil::table_exist(DBPREFIX.'module_foo')) {
    // table contrexx_module_foo does exist
}</syntaxhighlight>

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

== Check for column's existence ==
The method <code>\Cx\Lib\UpdateUtil::column_exist($table, $column)</code> can be used to check for a certain column within a table.
<syntaxhighlight lang=php>if (\Cx\Lib\UpdateUtil::column_exist(DBPREFIX.'module_foo', 'bar')) {
   // column contrexx_module_foo.bar is present
}</syntaxhighlight>

== Check column's type ==
The method <code>\Cx\Lib\UpdateUtil::check_column_type($table, $column, $type)</code> can be used to verify a certain column's type.
<syntaxhighlight lang=php>if (\Cx\Lib\UpdateUtil::check_column_type(DBPREFIX.'module_foo', 'bar', 'varchar')) {
    // column contrexx_module_foo.bar is of type 'varchar'
}</syntaxhighlight>

== Change table structure ==
The method <code>\Cx\Lib\UpdateUtil::table($table, $arrTableStructure, $arrIndexes, $engine, $comment, $constraints)</code> can be used to manipulate the structure (incl. indexes) of a table.
<syntaxhighlight lang=php>/* $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',
        ),
    ),
);</syntaxhighlight>

== Change constraints definition on table ==
The method <code>\Cx\Lib\UpdateUtil::set_constraints($table, $arrConstraints)</code> can be used to set the foreign key constraints on a table.
<syntaxhighlight lang=php>/* $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',
        ),
    ),
);</syntaxhighlight>

== Rename table column ==
<syntaxhighlight lang=php>
\Cx\Lib\UpdateUtil::table(
    DBPREFIX.'module_foo',
    array(
        'new_name'             => array('type' => 'INT(1)', 'notnull' => true, 'renamefrom' => 'old_name')
    )
);</syntaxhighlight>

== Add new column ==
<syntaxhighlight lang=php>
\Cx\Lib\UpdateUtil::table(
    DBPREFIX.'module_foo',
    array(
        'new_column'             => array('type' => 'INT(1)', 'notnull' => true, 'after' => 'existing_column')
    )
);</syntaxhighlight>

[[category:MkDocs]]