public static function get_multiple_create_bits($node_schema, $node_table, $constraints)
{
$bits = array();
foreach ($constraints as $constraint) {
$bits[] = mysql5_constraint::get_constraint_sql($constraint, FALSE);
if (strcasecmp($constraint['type'], 'PRIMARY KEY') == 0) {
// we're adding the PK constraint, so we need to add AUTO_INCREMENT on any affected columns immediately after!
$columns = mysql5_table::primary_key_columns($node_table);
foreach ($columns as $col) {
$node_column = dbx::get_table_column($node_table, $col);
if (mysql5_column::is_auto_increment($node_column['type'])) {
$bits[] = "MODIFY " . mysql5_column::get_full_definition(dbsteward::$new_database, $node_schema, $node_table, $node_column, FALSE, TRUE, TRUE);
break;
// there can only be one AI column per table
}
}
}
}
return $bits;
}
public static function get_data_sql($old_schema, $old_table, $new_schema, $new_table, $delete_mode = false)
{
$sql = '';
if ($old_table == null) {
if (!$delete_mode) {
// old table doesnt exist, pump inserts
$new_table_rows = dbx::get_table_rows($new_table);
if ($new_table_rows) {
$new_table_row_columns = preg_split("/[\\,\\s]+/", $new_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
foreach ($new_table_rows->row as $data_row) {
// is the row marked for delete?
if (isset($data_row['delete']) && strcasecmp($data_row['delete'], 'true') == 0) {
// don't insert it, we are inserting data that should be there
} else {
$sql .= static::get_data_row_insert($new_schema, $new_table, $new_table_row_columns, $data_row);
}
}
}
// set serial columns with serialStart defined to that value
// this is done in get_data_sql to ensure the serial start is set post row insertion
$sql .= mysql5_column::get_serial_start_dml($new_schema, $new_table);
}
} else {
// data row match scenarios are based on primary key matching
$old_table_rows = dbx::get_table_rows($old_table);
if ($old_table_rows) {
$old_table_row_columns = preg_split("/[\\,\\s]+/", $old_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
}
// is caller asking for deletes or data updates?
if ($delete_mode) {
// what old rows have no matches in the new rows? delete them
if ($old_table_rows) {
static::table_data_rows_compare($old_table, $new_table, false, $old_rows, $new_rows, $changes);
$count_old_rows = count($old_rows);
for ($i = 0; $i < $count_old_rows; $i++) {
static::get_data_row_delete($old_schema, $old_table, $old_table_row_columns, $old_rows[$i], $sql_append);
//@REVISIT
$sql .= $sql_append;
}
}
} else {
$new_table_rows = dbx::get_table_rows($new_table);
if ($new_table_rows) {
$new_table_row_columns = preg_split("/[\\,\\s]+/", $new_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
}
// what columns in matching rows between old and new are different?
if ($old_table_rows && $new_table_rows) {
$new_table_primary_keys = mysql5_table::primary_key_columns($new_table);
static::table_data_rows_compare($old_table, $new_table, true, $old_rows, $new_rows, $changes);
$count_old_rows = count($old_rows);
for ($i = 0; $i < $count_old_rows; $i++) {
$new_data_row = null;
$changed_columns = null;
if (count($changes[$i]) > 0) {
// changes were found between primary key matched old_table_row and new_table_row
// get the sql to make that happen
$sql .= static::get_data_row_update($new_schema, $new_table, $old_table_row_columns, $old_rows[$i], $new_table_row_columns, $new_rows[$i], $changes[$i]);
}
}
}
// what new rows are missing from the old? insert them
if ($new_table_rows) {
static::table_data_rows_compare($new_table, $old_table, false, $new_rows, $old_rows, $changes);
$count_new_rows = count($new_rows);
for ($i = 0; $i < $count_new_rows; $i++) {
$sql .= static::get_data_row_insert($new_schema, $new_table, $new_table_row_columns, $new_rows[$i]);
}
}
}
}
return $sql;
}
/** Convert from arbitrary type notations to mysql5 specific type representations */
protected static function mysql5_type_convert($type, $value = null)
{
if ($is_ai = mysql5_column::is_auto_increment($type)) {
$type = mysql5_column::un_auto_increment($type);
}
// when used in an index, varchars can only have a max of 3500 bytes
// so when converting types, we don't know if it might be in an index,
// so we play it safe
if (substr($type, -2) == '[]') {
$type = 'varchar(3500)';
}
switch (strtolower($type)) {
case 'bool':
case 'boolean':
// $type = 'tinyint';
if ($value) {
switch (strtolower($value)) {
case "'t'":
case 'true':
case '1':
$value = '1';
break;
case "'f'":
case 'false':
case '0':
$value = '0';
break;
default:
throw new Exception("Unknown column type boolean default {$value}");
break;
}
}
break;
// boolean
// boolean
case 'inet':
$type = 'varchar(16)';
break;
case 'int':
case 'integer':
$type = 'int(11)';
break;
case 'interval':
$type = 'varchar(3500)';
break;
case 'character varying':
case 'varchar':
$type = 'varchar(3500)';
break;
// mysql's timezone support is attrocious.
// see: http://dev.mysql.com/doc/refman/5.5/en/datetime.html
// mysql's timezone support is attrocious.
// see: http://dev.mysql.com/doc/refman/5.5/en/datetime.html
case 'timestamp without timezone':
case 'timestamp with timezone':
case 'timestamp without time zone':
case 'timestamp with time zone':
$type = 'timestamp';
break;
case 'time with timezone':
case 'time with time zone':
$type = 'time';
break;
case 'serial':
case 'bigserial':
// emulated with triggers and sequences later on in the process
// mysql5 interprets the 'serial' type as "BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE"
// which is dumb compared to the emulation with triggers/sequences to act more like pgsql's
break;
case 'uuid':
// 8 digits, 3 x 4 digits, 12 digits = 32 digits + 4 hyphens = 36 chars
$type = 'varchar(40)';
break;
}
// character varying(N) => varchar(N)
// $type = preg_replace('/character varying\((.+)\)/i','varchar($1)',$type);
// mysql doesn't understand epoch
if (isset($value) && strcasecmp($value, "'epoch'") == 0) {
// 00:00:00 is reserved for the "zero" value of a timestamp field. 01 is the closest we can get.
$value = "'1970-01-01 00:00:01'";
}
if ($is_ai) {
$type = (string) $type . " AUTO_INCREMENT";
}
return array($type, $value);
}
public function testTimestamps()
{
$xml = <<<XML
<schema name="public" owner="NOBODY">
<table name="test" primaryKey="id" owner="NOBODY">
<column name="a" type="timestamp"/>
<column name="b" type="timestamp" null="false"/>
<column name="c" type="timestamp" null="true"/>
<!-- default=NULL and not nullable doesn't make sense -->
<column name="d" type="timestamp" default="NULL" null="true"/>
<column name="e" type="timestamp" default="CURRENT_TIMESTAMP"/>
<column name="f" type="timestamp" default="CURRENT_TIMESTAMP" null="false"/>
<column name="g" type="timestamp" default="CURRENT_TIMESTAMP" null="true"/>
<column name="h" type="timestamp" default="'2012-05-05 11:11:11'"/>
<column name="i" type="timestamp" default="'2012-05-05 11:11:11'" null="false"/>
<column name="j" type="timestamp" default="'2012-05-05 11:11:11'" null="true"/>
</table>
</schema>
XML;
$schema = new SimpleXMLElement($xml);
$cols = $schema->table->column;
$def = function ($col) use(&$schema) {
return mysql5_column::get_full_definition($schema, $schema, $schema->table, $col, true, true);
};
// no default
$this->assertEquals("`a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP", $def($cols[0]));
$this->assertEquals("`b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP", $def($cols[1]));
$this->assertEquals("`c` timestamp NULL DEFAULT NULL", $def($cols[2]));
// default null
$this->assertEquals("`d` timestamp NULL DEFAULT NULL", $def($cols[3]));
// default CURRENT_TIMESTAMP
$this->assertEquals("`e` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP", $def($cols[4]));
$this->assertEquals("`f` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP", $def($cols[5]));
$this->assertEquals("`g` timestamp NULL DEFAULT CURRENT_TIMESTAMP", $def($cols[6]));
// default arbitrary time
$this->assertEquals("`h` timestamp NOT NULL DEFAULT '2012-05-05 11:11:11'", $def($cols[7]));
$this->assertEquals("`i` timestamp NOT NULL DEFAULT '2012-05-05 11:11:11'", $def($cols[8]));
$this->assertEquals("`j` timestamp NULL DEFAULT '2012-05-05 11:11:11'", $def($cols[9]));
}
/**
* escape a column's value, or return the default value if none specified
*
* @NOTE: it is critical to note that colmn values should always be escaped with this function
* as it also converts MSSQL specific values from postgresql ones
*
* @return string
*/
public static function column_value_default($node_schema, $node_table, $data_column_name, $node_col)
{
// if marked, make it null or default, depending on column options
if (isset($node_col['null']) && strcasecmp('true', $node_col['null']) == 0) {
return 'NULL';
}
// columns that specify empty attribute are made empty strings
if (isset($node_col['empty']) && strcasecmp('true', $node_col['empty']) == 0) {
return "''";
}
// don't esacape columns marked literal sql values
if (isset($node_col['sql']) && strcasecmp($node_col['sql'], 'true') == 0) {
return '(' . $node_col . ')';
}
$node_column = dbx::get_table_column($node_table, $data_column_name);
if ($node_column === NULL) {
throw new exception("Failed to find table " . $node_table['name'] . " column " . $data_column_name . " for default value check");
}
$value_type = mysql5_column::column_type(dbsteward::$new_database, $node_schema, $node_table, $node_column);
// else if col is zero length, make it default, or DB NULL
if (strlen($node_col) == 0) {
// is there a default defined for the column?
$dummy_data_column = new stdClass();
$column_default_value = xml_parser::column_default_value($node_table, $data_column_name, $dummy_data_column);
if ($column_default_value != NULL) {
// run default value through value_escape to allow data value conversions to happen
$value = mysql5::value_escape($value_type, $column_default_value);
} else {
$value = 'NULL';
}
} else {
$value = mysql5::value_escape($value_type, dbsteward::string_cast($node_col));
}
return $value;
}
public static function get_triggers_needed($schema, $table)
{
$triggers = array();
foreach ($table->column as $column) {
// we need a trigger for each serial column
if (mysql5_column::is_serial($column['type'])) {
$trigger_name = mysql5_column::get_serial_trigger_name($schema, $table, $column);
$sequence_name = mysql5_column::get_serial_sequence_name($schema, $table, $column);
$table_name = $table['name'];
$column_name = mysql5::get_quoted_column_name($column['name']);
$xml = <<<XML
<trigger name="{$trigger_name}"
sqlFormat="mysql5"
when="BEFORE"
event="INSERT"
table="{$table_name}"
forEach="ROW"
function="SET NEW.{$column_name} = COALESCE(NEW.{$column_name}, nextval('{$sequence_name}'));"/>
XML;
$triggers[] = new SimpleXMLElement($xml);
}
// @TODO: convert DEFAULT expressions (not constants) to triggers for pgsql compatibility
}
return $triggers;
}
