/**
* Table columns format update.
*
* @param string $table table name
* @param string $column column name
* @param array $definition column definition
* @param array $problems problematic options
*/
public static function updateTableColumnFormat($table, $column, $definition, $problems)
{
$primaryKeys = array();
if (isset($definition['primary']) && $definition['primary'] === true) {
// Needs to add a PK
$column_dfn = self::getTableColumns($table, false);
// Getting all other PK
if (is_array($column_dfn)) {
foreach ($column_dfn as $col) {
if (isset($col['Key']) && $col['Key'] === 'PRI') {
$primaryKeys[] = $col['Field'];
}
}
}
// Drop primary keys only if there is at least on primary key
if (count($primaryKeys) > 0) {
$query = 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY; ';
}
$primaryKeys[] = $column;
unset($definition['primary']);
} else {
if (in_array('primary', $problems)) {
// Needs to remove a PK
// First remove all PK
$query = 'ALTER TABLE ' . $table . ' DROP PRIMARY KEY; ';
// Get all PK of the table
$column_dfn = self::getTableColumns($table, false);
// Creating list of PK without the PK to remove
if (is_array($column_dfn)) {
foreach ($column_dfn as $col) {
if (isset($col['Key']) && $col['Key'] === 'PRI' && $col['Field'] !== $column) {
$primaryKeys[] = $col['Field'];
}
}
}
}
}
$query .= 'ALTER TABLE ' . $table . ' MODIFY ' . $column . ' ' . self::columnDefinition($definition) . '; ';
// Finaly, if PK have to be created, create these
if (count($primaryKeys) > 0) {
$query .= 'ALTER TABLE ' . $table . ' ADD PRIMARY KEY (' . implode(', ', $primaryKeys) . ')';
}
DBI::exec($query);
}
/**
* Table columns format update.
*
* @param string $table table name
* @param string $column column name
* @param array $definition column definition
* @param array $problems problematic options
*/
public static function updateTableColumnFormat($table, $column, $definition, $problems)
{
if (in_array('type', $problems)) {
// Cahnge data type
$type = '';
switch ($definition['type']) {
case 'int':
case 'uint':
$size = array_key_exists('size', $definition) ? $definition['size'] : 'medium';
if (!$size) {
$size = 'medium';
}
$s2s = array('small' => 'smallint', 'medium' => 'integer', 'big' => 'bigint');
$type .= $s2s[$size];
break;
case 'string':
$type .= 'character varying(' . $definition['size'] . ')';
break;
case 'bool':
$type .= 'boolean';
break;
case 'text':
$type .= 'text';
break;
case 'date':
$type .= 'date';
break;
case 'datetime':
$type .= 'timestamp';
break;
case 'time':
$type .= 'time';
break;
}
if ($type) {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' TYPE ' . $type);
}
}
// Options defaults
foreach (array('null', 'primary', 'unique', 'autoinc') as $k) {
if (!array_key_exists($k, $definition)) {
$definition[$k] = false;
}
}
// Change nullable
if (in_array('null', $problems)) {
if ($definition['null']) {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' DROP NOT NULL');
} else {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' SET NOT NULL');
}
}
// Change default
if (in_array('default', $problems)) {
if (array_key_exists('default', $definition)) {
if (is_null($definition['default'])) {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' SET DEFAULT NULL');
} else {
if (is_bool($definition['default'])) {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' SET DEFAULT ' . ($definition['default'] ? '1' : '0'));
} else {
DBI::prepare('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' SET DEFAULT :default') - execute(array(':default' => $definition['default']));
}
}
} else {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' DROP DEFAULT');
}
}
// Change primary
if (in_array('primary', $problems)) {
if ($definition['primary']) {
DBI::exec('ALTER TABLE ' . $table . ' ADD CONSTRAINT primary_' . $column . ' PRIMARY KEY (' . $column . ')');
} else {
DBI::exec('ALTER TABLE ' . $table . ' DROP CONSTRAINT primary_' . $column);
}
}
// Change unique
if (in_array('unique', $problems)) {
if ($definition['unique']) {
DBI::exec('ALTER TABLE ' . $table . ' ADD CONSTRAINT unique_' . $column . ' UNIQUE (' . $column . ')');
} else {
DBI::exec('ALTER TABLE ' . $table . ' DROP CONSTRAINT unique_' . $column);
}
}
// Change autoinc
if (in_array('autoinc', $problems)) {
if ($definition['autoinc']) {
self::createSequence($table, $column);
} else {
DBI::exec('ALTER TABLE ' . $table . ' ALTER COLUMN ' . $column . ' DROP DEFAULT');
// Should we drop the sequence as well ?
}
}
}
/**
* Table columns format update.
*
* @param string $table table name
* @param string $column column name
* @param array $definition column definition
* @param array $problems problematic options
*/
public static function updateTableColumnFormat($table, $column, $definition, $problems)
{
$query = 'ALTER TABLE ' . $table . ' MODIFY ' . $column . ' ' . self::columnDefinition($definition);
DBI::exec($query);
}
