/** * Returns full definition of the column. * * @param add_defaults whether default value should be added in case NOT * NULL constraint is specified but no default value is set * * @return full definition of the column */ public static function get_full_definition($db_doc, $node_schema, $node_table, $node_column, $add_defaults, $include_null_definition = true, $include_auto_increment = false) { // ignore AUTO_INCREMENT flags for now $is_auto_increment = static::is_auto_increment($node_column['type']); $orig_type = (string) $node_column['type']; $node_column['type'] = static::un_auto_increment($node_column['type']); $column_type = static::column_type($db_doc, $node_schema, $node_table, $node_column); $definition = mysql5::get_quoted_column_name($node_column['name']) . ' ' . $column_type; $nullable = static::null_allowed($node_table, $node_column); $is_timestamp = static::is_timestamp($node_column); if ($include_null_definition) { if ($nullable) { if ($is_timestamp) { $definition .= " NULL"; } } else { $definition .= " NOT NULL"; } } if ($include_auto_increment && $is_auto_increment) { $definition .= " AUTO_INCREMENT"; } if (strlen($node_column['default']) > 0) { if (static::is_serial($node_column['type'])) { $note = "Ignoring default '{$node_column['default']}' on {$node_schema['name']}.{$node_table['name']}.{$node_column['name']} because it is a serial type"; dbsteward::warning($note . "\n"); } else { $definition .= " DEFAULT " . $node_column['default']; } } else { if ($add_defaults && $is_timestamp) { if ($nullable) { $definition .= " DEFAULT NULL"; } else { $definition .= " DEFAULT CURRENT_TIMESTAMP"; } } else { if (!$nullable && $add_defaults) { $default_col_value = self::get_default_value($node_column['type']); if ($default_col_value != null) { $definition .= " DEFAULT " . $default_col_value; } } } } if (strlen($node_column['description']) > 0) { $definition .= " COMMENT " . mysql5::quote_string_value($node_column['description']); } // restore the original type of the column $node_column['type'] = $orig_type; return $definition; }
/** * Creates and returns SQL command for dropping the sequence. * * @return string */ public static function get_drop_sql($node_schema, $node_sequences) { $table_name = mysql5::get_quoted_table_name(self::TABLE_NAME); $seq_col = mysql5::get_quoted_column_name(self::SEQ_COL); $sequences = dbx::to_array($node_sequences); if (count($sequences) === 0) { return ''; } $sequence_names = "('" . implode("', '", array_map(function ($n) { if ($n instanceof SimpleXMLElement) { return $n['name']; } return $n; }, $sequences)) . "')"; return "DELETE FROM {$table_name} WHERE {$seq_col} IN {$sequence_names};"; }
private static function get_data_row_insert($node_schema, $node_table, $data_row_columns, $data_row) { $columns = array(); $values = array(); $data_row_columns_count = count($data_row_columns); for ($i = 0; $i < $data_row_columns_count; $i++) { $columns[] = mysql5::get_quoted_column_name($data_row_columns[$i]); $values[] = mysql5::column_value_default($node_schema, $node_table, $data_row_columns[$i], $data_row->col[$i]); } $columns = implode(', ', $columns); $values = implode(', ', $values); return sprintf("INSERT INTO %s (%s) VALUES (%s);\n", mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']), $columns, $values); }
protected static function get_dimension_list($node_schema, $node_table, $node_index) { $dimensions = array(); foreach ($node_index->indexDimension as $dimension) { // mysql only supports indexed columns, not indexed expressions like in pgsql or mssql if (!mysql5_table::contains_column($node_table, $dimension)) { throw new Exception("Table " . mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']) . " does not contain column '{$dimension}'"); } $dimensions[] = mysql5::get_quoted_column_name($dimension); } return $dimensions; }
public static function build_data($db_doc, $ofs, $tables) { // use the dependency order to then write out the actual data inserts into the data sql file $limit_to_tables_count = count(dbsteward::$limit_to_tables); foreach ($tables as $dep_table) { $schema = $dep_table['schema']; $table = $dep_table['table']; if ($table['name'] === dbsteward::TABLE_DEPENDENCY_IGNORABLE_NAME) { // don't do anything with this table, it is a magic internal DBSteward value continue; } if ($limit_to_tables_count > 0) { if (in_array($schema['name'], array_keys(dbsteward::$limit_to_tables))) { if (in_array($table['name'], dbsteward::$limit_to_tables[(string) $schema['name']])) { // table is to be included } else { continue; } } else { continue; } } $ofs->write(mysql5_diff_tables::get_data_sql(NULL, NULL, $schema, $table, FALSE)); $table_primary_keys = mysql5_table::primary_key_columns($table); $table_column_names = dbx::to_array($table->column, 'name'); $node_rows =& dbx::get_table_rows($table); // the <rows> element $data_column_names = preg_split("/,|\\s/", $node_rows['columns'], -1, PREG_SPLIT_NO_EMPTY); // set serial primary keys to the max value after inserts have been performed // only if the PRIMARY KEY is not a multi column if (count($table_primary_keys) == 1 && in_array($table_primary_keys[0], $data_column_names)) { $pk_column_name = $table_primary_keys[0]; $node_pk_column = dbx::get_table_column($table, $pk_column_name); if ($node_pk_column == NULL) { throw new exception("Failed to find primary key column '" . $pk_column_name . "' for " . $schema['name'] . "." . $table['name']); } // only set the pkey to MAX() if the primary key column is also a serial/bigserial and if serialStart is not defined if (mysql5_column::is_serial($node_pk_column['type']) && !isset($node_pk_column['serialStart'])) { $fqtn = mysql5::get_fully_qualified_table_name($schema['name'], $table['name']); $qcol = mysql5::get_quoted_column_name($pk_column_name); $setval = mysql5_sequence::get_setval_call(mysql5_column::get_serial_sequence_name($schema, $table, $node_pk_column), "MAX({$qcol})", "TRUE"); $sql = "SELECT {$setval} FROM {$fqtn};\n"; $ofs->write($sql); } } // unlike the pg class, we cannot just set identity column start values here with setval without inserting a row // check if primary key is a column of this table - FS#17481 if (count(array_diff($table_primary_keys, $table_column_names)) != 0) { throw new exception('Primary key ' . $table['primaryKey'] . ' does not exist as a column in table ' . $table['name']); } } // include all of the unstaged sql elements dbx::build_staged_sql($db_doc, $ofs, NULL); $ofs->write("\n"); }
private static function diff_single($ofs, $old_seq, $new_seq) { $sql = array(); if (!dbsteward::$ignore_oldnames && !empty($new_seq['oldSequenceName'])) { $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::SEQ_COL) . " = '{$new_seq['name']}'"; } if ($new_seq['inc'] == null && $old_seq['inc'] != null) { $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::INC_COL) . ' = DEFAULT'; } if ($new_seq['inc'] != null && strcasecmp($new_seq['inc'], $old_seq['inc']) != 0) { $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::INC_COL) . ' = ' . $new_seq['inc']; } if ($new_seq['min'] == null && $old_seq['min'] != null) { $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MIN_COL) . ' = DEFAULT'; } elseif ($new_seq['min'] != null && strcasecmp($new_seq['min'], $old_seq['min']) != 0) { $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MIN_COL) . ' = ' . $new_seq['min']; } if ($new_seq['max'] == null && $old_seq['max'] != null) { $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MAX_COL) . ' = DEFAULT'; } elseif ($new_seq['max'] != null && strcasecmp($new_seq['max'], $old_seq['max']) != 0) { $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::MAX_COL) . ' = ' . $new_seq['max']; } if ($new_seq['start'] != null && strcasecmp($new_seq['start'], $old_seq['start']) != 0) { $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::CUR_COL) . ' = ' . $new_seq['start']; } if ($new_seq['cycle'] == null && $old_seq['cycle'] != null) { $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::CYC_COL) . ' = DEFAULT'; } elseif ($new_seq['cycle'] != null && strcasecmp($new_seq['cycle'], $old_seq['cycle']) != 0) { $value = strcasecmp($new_seq['cycle'], 'false') == 0 ? 'FALSE' : 'TRUE'; $sql[] = mysql5::get_quoted_column_name(mysql5_sequence::CYC_COL) . ' = ' . $value; } if (!empty($sql)) { $out = "UPDATE " . mysql5::get_quoted_table_name(mysql5_sequence::TABLE_NAME); $out .= "\nSET " . implode(",\n ", $sql); $out .= "\nWHERE " . mysql5::get_quoted_column_name(mysql5_sequence::SEQ_COL) . " = "; $out .= "'" . $old_seq['name'] . "'"; $ofs->write("{$out};\n"); } }
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; }