/** * 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); }