private static function expand_partitioned_table(&$doc, $schema, $table)
{
// Validate
if (!isset($table->tablePartition['type'])) {
throw new exception('No table partiton type selected for ' . $table['name']);
}
if ($table->tablePartition['type'] != 'MODULO') {
throw new exception('Invalid partition type: ' . $table->tablePartition['type']);
}
// Establish the partition column and number of partitions
self::$part_number = NULL;
self::$part_column = NULL;
self::$first_slony_id = NULL;
self::$last_slony_id = NULL;
foreach ($table->tablePartition->tablePartitionOption as $opt) {
if ($opt['name'] == 'number') {
self::$part_number = $opt['value'];
}
if ($opt['name'] == 'column') {
self::$part_column = pgsql8::get_quoted_column_name($opt['value']);
}
if ($opt['name'] == 'firstSlonyId') {
self::$first_slony_id = (int) $opt['value'];
}
if ($opt['name'] == 'lastSlonyId') {
self::$last_slony_id = (int) $opt['value'];
}
}
if (empty(self::$part_number)) {
throw new exception('tablePartitionOption "number" must be specified for table ' . $table['name']);
}
if (empty(self::$part_column)) {
throw new exception('tablePartitionOption "column" must be specified for table ' . $table['name']);
}
if (!is_null(self::$first_slony_id) && !is_null(self::$last_slony_id)) {
$slony_ids_allocated = self::$last_slony_id - self::$first_slony_id + 1;
if ($slony_ids_allocated != self::$part_number) {
throw new exception('Requested ' . self::$part_number . " partitions but provided {$slony_ids_allocated} slony IDs");
}
}
// Create the schema node for the partitions
$new_schema = $doc->addChild('schema');
self::create_partition_schema($schema, $table, $new_schema);
// Clone the node as many times as needed to create the partition tables
self::create_partition_tables($schema, $new_schema, $table);
// Remove attributes from the main table that move to the partitions
unset($table->index);
// Add the trigger to the main table
$trigger = $schema->addChild('trigger');
$trigger->addAttribute('name', $table['name'] . '_part_trg');
$trigger->addAttribute('sqlFormat', 'pgsql8');
$trigger->addAttribute('event', 'INSERT');
$trigger->addAttribute('when', 'BEFORE');
$trigger->addAttribute('table', $table['name']);
$trigger->addAttribute('forEach', 'ROW');
$trigger->addAttribute('function', $new_schema['name'] . '.insert_trigger()');
// Create the stored prodecure
self::create_procedure($new_schema, $table);
}
/**
* Creates and returns SQL for creation of the index.
*
* @return created SQL
*/
public static function get_creation_sql($node_schema, $node_table, $node_index)
{
$sql = "CREATE ";
if (isset($node_index['unique']) && strcasecmp($node_index['unique'], 'true') == 0) {
$sql .= "UNIQUE ";
}
$sql .= "INDEX ";
if (isset($node_index['concurrently']) && strcasecmp($node_index['concurrently'], 'true') == 0) {
$sql .= "CONCURRENTLY ";
}
$sql .= pgsql8::get_quoted_object_name($node_index['name']) . " ON " . pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_table['name']);
if (isset($node_index['using']) && strlen($node_index['using']) > 0) {
$sql .= ' USING ' . $node_index['using'];
}
$sql .= ' (';
foreach ($node_index->indexDimension as $dimension) {
// don't quote the identifier if it's defined as being sql, e.g. '<indexDimension>X + 1</indexDimension>' -> "X + 1"
// '<indexDimension sql="true">X + 1</indexDimension> -> X + 1
if (isset($dimension['sql']) && strcasecmp($dimension['sql'], 'true') == 0) {
$sql .= $dimension . ', ';
} else {
$sql .= pgsql8::get_quoted_column_name($dimension) . ', ';
}
}
$sql = substr($sql, 0, -2);
$sql .= ')';
if (isset($node_index->indexWhere)) {
$index_where_clause = NULL;
foreach ($node_index->indexWhere as $node_index_where) {
if (empty($node_index_where['sqlFormat'])) {
throw new Exception("Attribute sqlFormat required for indexWhere definitions. See index '{$node_index['name']}' definition");
}
if ($node_index_where['sqlFormat'] == dbsteward::get_sql_format()) {
if ($index_where_clause !== NULL) {
throw new Exception("duplicate indexWhere definition for {$node_index_where['sqlFormat']} in index '{$node_index['name']}' definition");
}
$index_where_clause = (string) $node_index_where;
}
}
if (strlen($index_where_clause) > 0) {
$sql .= " WHERE ( " . $index_where_clause . " )";
}
}
$sql .= ';';
return $sql;
}
/**
* alter_column_type_placeholder's companion - restore $columns list of columns to $node_type type
*
* @param $columns reference columns returned by reference
* @param $node_schema
* @param $node_type
* @return string DDL
*/
public static function alter_column_type_restore($columns, $node_schema, $node_type)
{
$ddl = '';
foreach ($columns as $column_map) {
$ddl .= "ALTER TABLE " . pgsql8::get_quoted_schema_name($column_map['alter_column_schema']['name']) . '.' . pgsql8::get_quoted_table_name($column_map['alter_column_table']['name']) . " ALTER COLUMN " . pgsql8::get_quoted_column_name($column_map['alter_column_column']['name']) . " TYPE " . pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_object_name($node_type['name']) . " USING " . pgsql8::get_quoted_column_name($column_map['alter_column_column']['name']) . "::" . pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_object_name($node_type['name']) . ";\n";
}
return $ddl;
}
/**
* compare composite db doc to specified database
*
* @return string XML
*/
public static function compare_db_data($db_doc, $host, $port, $database, $user, $password)
{
dbsteward::notice("Connecting to pgsql8 host " . $host . ':' . $port . ' database ' . $database . ' as ' . $user);
// if not supplied, ask for the password
if ($password === FALSE) {
// @TODO: mask the password somehow without requiring a PHP extension
echo "Password: "******"host={$host} port={$port} dbname={$database} user={$user} password={$password}");
dbsteward::info("Comparing composited dbsteward definition data rows to postgresql database connection table contents");
// compare the composited dbsteward document to the established database connection
// effectively looking to see if rows are found that match primary keys, and if their contents are the same
foreach ($db_doc->schema as $schema) {
foreach ($schema->table as $table) {
if (isset($table->rows)) {
$table_name = dbsteward::string_cast($schema['name']) . '.' . dbsteward::string_cast($table['name']);
$primary_key_cols = self::primary_key_split($table['primaryKey']);
$cols = preg_split("/[\\,\\s]+/", $table->rows['columns'], -1, PREG_SPLIT_NO_EMPTY);
$col_types = array();
foreach ($table->column as $table_column) {
$type = '';
// foreign keyed columns inherit their foreign reference type
if (isset($table_column['foreignTable']) && isset($table_column['foreignColumn'])) {
if (strlen($type) > 0) {
throw new exception("type of " . $type . " was found for " . dbsteward::string_cast($cols[$j]) . " in table " . dbsteward::string_cast($table['name']) . " but it is foreign keyed!");
}
$foreign = array();
dbx::foreign_key($db_doc, $schema, $table, $table_column, $foreign);
// don't need to error-check, foreign_key() is self-checking if it doesnt find the fkey col it will complain
$type = $foreign['column']['type'];
} else {
$type = dbsteward::string_cast($table_column['type']);
}
if (strlen($type) == 0) {
throw new exception($table_name . " column " . $table_column['name'] . " type not found!");
}
$col_types[dbsteward::string_cast($table_column['name'])] = $type;
}
foreach ($table->rows->row as $row) {
// glue the primary key expression together for the where
$primary_key_expression = '';
for ($k = 0; $k < count($primary_key_cols); $k++) {
$column_name = pgsql8::get_quoted_column_name($primary_key_cols[$k]);
$pk_index = array_search($primary_key_cols[$k], $cols);
if ($pk_index === FALSE) {
throw new exception("failed to find " . $schema['name'] . "." . $table['name'] . " primary key column " . $primary_key_cols[$k] . " in cols list (" . implode(", ", $cols) . ")");
}
$primary_key_expression .= $column_name . " = " . pgsql8::value_escape($col_types[$primary_key_cols[$k]], $row->col[$pk_index], $db_doc);
if ($k < count($primary_key_cols) - 1) {
$primary_key_expression .= ' AND ';
}
}
$sql = "SELECT *\n FROM " . $table_name . "\n WHERE " . $primary_key_expression;
$rs = pgsql8_db::query($sql);
// is the row supposed to be deleted?
if (strcasecmp('true', $row['delete']) == 0) {
if (pg_num_rows($rs) > 0) {
dbsteward::notice($table_name . " row marked for DELETE found WHERE " . $primary_key_expression);
}
} else {
if (pg_num_rows($rs) == 0) {
dbsteward::notice($table_name . " does not contain row WHERE " . $primary_key_expression);
} else {
if (pg_num_rows($rs) > 1) {
dbsteward::notice($table_name . " contains more than one row WHERE " . $primary_key_expression);
while (($db_row = pg_fetch($rs)) !== FALSE) {
dbsteward::notice("\t" . implode(', ', $db_row));
}
} else {
$db_row = pg_fetch_assoc($rs);
// make sure any aspects of the $row are present in the $db_row
for ($i = 0; $i < count($cols); $i++) {
$xml_value = self::pgdata_homogenize($col_types[$cols[$i]], dbsteward::string_cast($row->col[$i]));
$db_value = self::pgdata_homogenize($col_types[$cols[$i]], dbsteward::string_cast($db_row[$cols[$i]]));
$values_match = FALSE;
// evaluate if they are equal
$values_match = $xml_value == $db_value;
// if they are not PHP equal, and are alternate expressionable, ask the database
if (!$values_match && preg_match('/^time.*|^date.*|^interval/i', $col_types[$cols[$i]]) > 0) {
// do both describe atleast some value (greater than zero len?)
if (strlen($xml_value) > 0 && strlen($db_value) > 0) {
$sql = "SELECT '{$xml_value}'::" . $col_types[$cols[$i]] . " = '{$db_value}'::" . $col_types[$cols[$i]] . " AS equal_eval";
$values_match = pgsql8_db::query_str($sql) == 't';
}
}
if (!$values_match) {
dbsteward::warning($table_name . " row column WHERE (" . $primary_key_expression . ") " . $cols[$i] . " data does not match database row column: '" . $xml_value . "' VS '" . $db_value . "'");
}
}
}
}
}
}
}
}
}
//xml_parser::validate_xml($db_doc->asXML());
return xml_parser::format_xml($db_doc->saveXML());
}
/**
* Generate column defaults from column definitions, returns FALSE if
* no defaults were defined, otherwise return the
* ALTER TABLE ALTER COLUMN SET statements needed.
*
* Don't know if this would work with functions referenced as function(argument1 ... argumentN)
*
* @param type $node_schema
* @param type $node_table
* @param type $node_column
* @param type $add_defaults
* @param type $include_null_definition
* @param type $include_default_nextval
* @return boolean|string
*/
public static function set_column_defaults($node_schema, $node_table, $node_column, $add_defaults, $include_null_definition = true, $include_default_nextval = TRUE)
{
$fq_table_name = pgsql8::get_fully_qualified_table_name($node_schema['name'], $node_table['name']);
$base_sql = "ALTER TABLE " . $fq_table_name . " ALTER COLUMN " . pgsql8::get_quoted_column_name($node_column['name']) . " SET";
$sql = $base_sql;
$changes = FALSE;
if (strlen($node_column['default']) > 0) {
if (!$include_default_nextval && static::has_default_nextval($node_table, $node_column)) {
// if the default is a nextval expression, don't specify it in the regular full definition
// because if the sequence has not been defined yet,
// the nextval expression will be evaluated inline and fail
dbsteward::info("Skipping " . $node_column['name'] . " default expression \"" . $node_column['default'] . "\" - this default expression will be applied after all sequences have been created");
return $changes;
} else {
$sql .= " DEFAULT " . $node_column['default'];
$changes = TRUE;
}
} else {
if (!pgsql8_column::null_allowed($node_table, $node_column) && $add_defaults) {
$default_col_value = pgsql8_column::get_default_value($node_column['type']);
if ($default_col_value != null) {
$sql .= " DEFAULT " . $default_col_value;
$changes = TRUE;
}
}
}
if ($include_null_definition && !pgsql8_column::null_allowed($node_table, $node_column)) {
if ($changes) {
$sql .= ";\n";
$sql .= $base_sql . " NOT NULL";
} else {
$sql .= " NOT NULL";
$changes = TRUE;
}
}
// no changes? we don't have a default for this column... keep going pls
if (!$changes) {
return $changes;
}
$sql .= ";\n";
return $sql;
}
protected static function get_data_row_update($node_schema, $node_table, $old_data_row_columns, $old_data_row, $new_data_row_columns, $new_data_row, $changed_columns)
{
if (count($changed_columns) == 0) {
throw new exception("empty changed_columns passed");
}
// what columns from new_data_row are different in old_data_row?
// those are the ones to push through the update statement to make the database current
$old_columns = '';
$update_columns = '';
foreach ($changed_columns as $changed_column) {
if (!isset($changed_column['old_col'])) {
$old_columns .= 'NOTDEFINED, ';
} else {
$old_col_value = pgsql8::column_value_default($node_schema, $node_table, $changed_column['name'], $changed_column['old_col']);
$old_columns .= $changed_column['name'] . ' = ' . $old_col_value . ', ';
}
$update_col_name = pgsql8::get_quoted_column_name($changed_column['name']);
$update_col_value = pgsql8::column_value_default($node_schema, $node_table, $changed_column['name'], $changed_column['new_col']);
$update_columns .= $update_col_name . ' = ' . $update_col_value . ', ';
}
// if the computed update_columns expression is < 5 chars, complain
if (strlen($update_columns) < 5) {
var_dump($update_columns);
throw new exception(sprintf("%s.%s update_columns is < 5 chars, unexpected", $node_schema['name'], $node_table['name']));
}
// kill trailing ', '
$update_columns = substr($update_columns, 0, -2);
$old_columns = substr($old_columns, 0, -2);
// use multiline comments here, so when data has newlines they can be preserved, but upgrade scripts don't catch on fire
$sql = sprintf("UPDATE %s.%s SET %s WHERE (%s); /* old values: %s */\n", pgsql8::get_quoted_schema_name($node_schema['name']), pgsql8::get_quoted_table_name($node_table['name']), $update_columns, dbx::primary_key_expression(dbsteward::$new_database, $node_schema, $node_table, $new_data_row_columns, $new_data_row), $old_columns);
return $sql;
}
