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; }