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