Update Script Database Structure Change

Check for table's existence

The method \Cx\Lib\UpdateUtil::table_exist($table) can be used to check for a certain table's existence.

if (\Cx\Lib\UpdateUtil::table_exist(DBPREFIX.'module_foo')) {
    // table contrexx_module_foo does exist
}

Drop table

The method \Cx\Lib\UpdateUtil::drop_table($table) can be used to drop a database table.

// this will drop the table contrexx_module_foo
\Cx\Lib\UpdateUtil::drop_table(DBPREFIX.'module_foo');

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.

if (\Cx\Lib\UpdateUtil::check_column_type(DBPREFIX.'module_foo', 'bar', 'varchar')) {
    // column contrexx_module_foo.bar is of type 'varchar'
}

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.

/* $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',
        ),
    ),
);

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.

/* $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',
        ),
    ),
);

Rename table column

\Cx\Lib\UpdateUtil::table(
    DBPREFIX.'module_foo',
    array(
        'new_name'             => array('type' => 'INT(1)', 'notnull' => true, 'renamefrom' => 'old_name')
    )
);

Add new column

\Cx\Lib\UpdateUtil::table(
    DBPREFIX.'module_foo',
    array(
        'new_column'             => array('type' => 'INT(1)', 'notnull' => true, 'after' => 'existing_column')
    )
);