public function get_partition_info($table)
{
$parts = $this->query("SELECT partition_method, partition_name,\n partition_expression, partition_description\n FROM partitions\n WHERE table_schema = ?\n AND table_name = ?\n ORDER BY partition_ordinal_position", array($this->dbname, $table->table_name));
if (count($parts) === 0) {
return null;
}
$method = strtoupper($parts[0]->partition_method);
switch ($method) {
case 'HASH':
case 'LINEAR HASH':
return (object) array('type' => $method, 'number' => count($parts), 'expression' => $parts[0]->partition_expression);
case 'KEY':
case 'LINEAR KEY':
return (object) array('type' => $method, 'number' => count($parts), 'columns' => str_replace(mysql5::QUOTE_CHAR, '', $parts[0]->partition_expression));
case 'LIST':
case 'RANGE':
case 'RANGE COLUMNS':
return (object) array('type' => $method, 'expression' => $method == 'RANGE COLUMNS' ? str_replace(mysql5::QUOTE_CHAR, '', $parts[0]->partition_expression) : $parts[0]->partition_expression, 'segments' => array_map(function ($p) {
return (object) array('name' => $p->partition_name, 'value' => $p->partition_description);
}, $parts));
default:
dbsteward::error("Unrecognized partition method {$method}!");
}
return null;
}
/**
* in MSSQL indexes must contain column references, value expressions are not allowed
*
*/
public static function index_dimension_scan($node_schema, $node_table, $node_index, &$add_column_sql)
{
$dimension_list = '';
$add_column_sql = '';
// in MSSQL, index dimensions that are not explicit columns must be converted to computed columns to make the index work like it does in postgresql
$i = 0;
foreach ($node_index->indexDimension as $dimension) {
$i++;
$dimension_name = (string) $dimension;
if (mssql10_table::contains_column($node_table, $dimension_name)) {
// dimension is an explicit column
// check unique index indexDimensions for nulled columns
// mssql index constraint engine will not ignore null values for nullable columns
if (isset($node_index['unique']) && strcasecmp($node_index['unique'], 'true') == 0) {
$node_column = dbx::get_table_column($node_table, $dimension_name);
if (mssql10_column::null_allowed($node_table, $node_column)) {
dbsteward::error("dimension_name = " . $dimension_name);
//var_dump($node_column);
throw new exception("nulled column index found");
}
}
} else {
// not an explicit column, so create one
$dimension_name = $node_index['name'] . '_' . $i;
$add_column_sql .= "ALTER TABLE " . mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_table['name']) . "\n" . " ADD " . $dimension_name . " AS " . (string) $dimension . ";\n";
}
$dimension_list .= $dimension_name . ', ';
}
$dimension_list = substr($dimension_list, 0, -2);
return $dimension_list;
}
public static function get_creation_sql($node_schema, $node_trigger)
{
$events = self::get_events($node_trigger);
if (strcasecmp($node_trigger['sqlFormat'], dbsteward::get_sql_format())) {
$note = "Ignoring {$node_trigger['sqlFormat']} trigger '{$node_trigger['name']}'";
dbsteward::warning($note);
return "-- {$note}\n";
}
if (empty($node_trigger['function'])) {
throw new Exception("No trigger body defined for trigger '{$node_trigger['name']}'");
}
if (!($when = self::validate_when($node_trigger['when']))) {
throw new Exception("Invalid WHEN clause for trigger '{$node_trigger['name']}': '{$node_trigger['when']}'");
}
$notes = "";
if (count($events) == 0) {
throw new Exception("No events were given for trigger {$node_trigger['name']}");
} elseif (count($events) > 1) {
$notes .= "-- You specified more than one event for trigger {$node_trigger['name']}, but MySQL only supports a single event a time\n";
$notes .= "-- generating separate triggers for each event\n";
dbsteward::warning("You specified more than one event for trigger {$node_trigger['name']}, but MySQL only supports a single event a time");
dbsteward::warning(" generating separate triggers for each event");
}
if (!empty($node_trigger['forEach']) && strcasecmp($node_trigger['forEach'], 'row')) {
dbsteward::error($notes .= "-- You specified a forEach value of {$node_trigger['forEach']} on trigger {$node_trigger['name']} but MySQL only supports ROW - ignoring\n");
}
$node_table = dbx::get_table($node_schema, $node_trigger['table']);
if ($node_table == null) {
throw new exception("Failed to find trigger's table '{$node_trigger['table']}' in schema node '{$node_schema['name']}'");
}
$table_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']);
// always drop triggers before creating them
$ddl = static::get_drop_sql($node_schema, $node_trigger);
$single = count($events) == 1;
foreach ($events as $event) {
if (!($event = self::validate_event($event))) {
throw new Exception("Invalid event on trigger '{$node_trigger['name']}': '{$event}'");
}
// @TODO: use something like get_fully_qualified_object_name
$trigger_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_trigger['name'] . ($single ? '' : "_{$event}"));
$trigger_fn = trim($node_trigger['function']);
if (substr($trigger_fn, -1) != ';') {
$trigger_fn .= ';';
}
$ddl .= <<<SQL
CREATE TRIGGER {$trigger_name} {$when} {$event} ON {$table_name}
FOR EACH ROW {$trigger_fn}
SQL;
}
return $notes . $ddl;
}
/**
* Creates and returns SQL for creation of the table.
*
* @return created SQL command
*/
public static function get_creation_sql($node_schema, $node_table)
{
if ($node_schema->getName() != 'schema') {
throw new exception("node_schema object element name is not schema. check stack for offending caller");
}
if ($node_table->getName() != 'table') {
throw new exception("node_table object element name is not table. check stack for offending caller");
}
if (strlen($node_table['inherits']) > 0) {
//@TODO: implement compatibility with pgsql table inheritance
dbsteward::error("Skipping table '{$node_table['name']}' because MySQL does not support table inheritance");
return "-- Skipping table '{$node_table['name']}' because MySQL does not support table inheritance";
}
$table_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_table['name']);
$sql = "CREATE TABLE {$table_name} (\n";
$cols = array();
foreach ($node_table->column as $column) {
$cols[] = mysql5_column::get_full_definition(dbsteward::$new_database, $node_schema, $node_table, $column, false);
}
$part_sql = static::get_partition_sql($node_schema, $node_table);
$sql .= " " . implode(",\n ", $cols) . "\n)";
$opt_sql = mysql5_table::get_table_options_sql(mysql5_table::get_table_options($node_schema, $node_table));
if (!empty($opt_sql)) {
$sql .= "\n" . $opt_sql;
}
if (strlen($node_table['description']) > 0) {
$sql .= "\nCOMMENT " . mysql5::quote_string_value($node_table['description']);
}
if (!empty($part_sql)) {
$sql .= "\n" . $part_sql;
}
$sql .= ';';
// @TODO: implement column statistics
// @TODO: table ownership with $node_table['owner'] ?
return $sql;
}
/**
* Overlay table rows from an overlay file onto a base table element
*
* @param type $base_table base table element to put overlay into
* @param type $overlay_table_rows overlay table rows element
* @throws exception
*/
public static function data_rows_overlay(&$base_table, &$overlay_table_rows)
{
$base_table_rows =& dbx::get_table_rows($base_table, TRUE, $overlay_table_rows['columns']);
$base_table_rows_count = count($base_table_rows->row);
// if the rows element columns attribute doesnt have a column that the overlay does
if (strlen($base_table_rows['columns']) == 0) {
throw new exception("base rows element missing columns attribute - unexpected");
}
if (strlen($overlay_table_rows['columns']) == 0) {
throw new exception("overlay rows element missing columns attribute - unexpected");
}
$base_cols = preg_split("/[\\,\\s]+/", $base_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
$overlay_cols = preg_split("/[\\,\\s]+/", $overlay_table_rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
$cols_diff = array_diff($overlay_cols, $base_cols);
// contains any values $overlay_cols does that $base_cols didnt, so add them
foreach ($cols_diff as $cols_diff_col) {
// add the missing column, padding the base's row->col entries with empty col's to match the new size
$base_cols[] = $cols_diff_col;
for ($i = 0; $i < $base_table_rows_count; $i++) {
// need to do it for each row entry, check for default for the column
$node_col = $base_table_rows->row[$i]->addChild('col', self::column_default_value($base_table, $cols_diff_col, $node_col));
}
}
// put the new columns list back in the node
$base_table_rows['columns'] = implode(', ', $base_cols);
// determine the "natural" ordering of primary key columns, so that we can deterministically create a primary key key
$base_primary_keys = preg_split("/[\\,\\s]+/", $base_table['primaryKey'], -1, PREG_SPLIT_NO_EMPTY);
$primary_key_index = self::data_row_overlay_primary_key_index($base_primary_keys, $base_cols, $overlay_cols);
// primary key key => row index
$base_pklookup = array();
$i = 0;
foreach ($base_table_rows->row as $base_row) {
$s = '';
foreach ($primary_key_index['base'] as $index) {
$s .= ':' . $base_row->col[$index];
}
$base_pklookup[$s] = $i++;
}
// merge all row entries for the rows element
$base_row_index = 0;
foreach ($overlay_table_rows->row as $overlay_row) {
// sanity check the overlay's rows columns list against the col count of the row
$overlay_row_count = count($overlay_row->col);
if (count($overlay_cols) != $overlay_row_count) {
dbsteward::error(count($overlay_cols) . " overlay columns != " . $overlay_row_count . " overlay elements");
var_dump($overlay_cols);
foreach ($overlay_row->col as $olcol) {
var_dump($olcol);
}
throw new exception("overlay_cols list count does not match overlay_row->col count");
}
// simple optimization:
// if the node had no ->row's to start
// don't try to match any of the children we are considering in this loop
if ($base_table_rows_count == 0) {
dbsteward::debug("DEBUG: skipping " . $base_table['name'] . " overlay -- no base table rows");
$row_match = FALSE;
} else {
$s = '';
foreach ($primary_key_index['overlay'] as $index) {
$s .= ':' . $overlay_row->col[$index];
}
if (array_key_exists($s, $base_pklookup)) {
$row_match = TRUE;
$base_row_index = $base_pklookup[$s];
} else {
$row_match = FALSE;
}
// $row_match = self::data_row_overlay_key_search($base_table_rows, $overlay_row, $primary_key_index, $base_row_index);
}
if ($row_match) {
// $base_row_index is set to $i in _match() when a match is found, so use it to overlay the matched row
$node_row = $base_table_rows->row[$base_row_index];
} else {
// not found, add the row and empty col entries
$node_row = $base_table_rows->addChild('row');
foreach ($base_cols as $base_col) {
$node_col = $node_row->addChild('col');
$node_col = self::column_default_value($base_table, $base_col, $node_col);
}
// then overlay the data in the overlay row
}
self::data_row_overlay_row($base_table, $node_row, $overlay_row, $base_cols, $overlay_cols);
}
}
public static function cmd($command, $error_fatal = TRUE)
{
dbsteward::debug("dbsteward::cmd( " . $command . " )");
$output = array();
$return_value = 0;
$last_line = exec($command, $output, $return_value);
if ($return_value > 0) {
if ($error_fatal) {
dbsteward::error("ERROR(" . $return_value . ") with command: " . $command);
dbsteward::error(implode("\n", $output));
throw new exception("ERROR(" . $return_value . ") with command: " . $command);
}
}
return TRUE;
}