/** * return SQL command for dropping the view * * @return string */ public static function get_drop_sql($node_schema, $node_view) { // set replica set context for view if (pgsql8::set_context_replica_set_id($node_view) === -10) { // view doesn't specify one, set from for schema object pgsql8::set_context_replica_set_id($node_schema); } $ddl = "DROP VIEW IF EXISTS " . pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_view['name']) . ";\n"; return $ddl; }
/** * Creates and returns SQL for dropping the trigger. * * @return created SQL */ public static function get_drop_sql($node_schema, $node_trigger) { $node_table = dbx::get_table($node_schema, $node_trigger['table']); if ($node_table == null) { throw new exception("Failed to find trigger table " . $node_trigger['table'] . " in schema node " . $node_schema['name']); } $table_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_table['name']); $ddl = "DROP TRIGGER " . pgsql8::get_quoted_object_name($node_trigger['name']) . " ON " . $table_name . ";\n"; return $ddl; }
/** * Modify sequence values if they have changed * * @param $ofs output file pointer * @param $old_schema original schema * @param $new_schema new schema */ private static function add_modified_sequences($ofs, $old_schema, $new_schema) { foreach (dbx::get_sequences($new_schema) as $new_sequence) { if ($old_schema != null && pgsql8_schema::contains_sequence($old_schema, $new_sequence['name'])) { $old_sequence = dbx::get_sequence($old_schema, $new_sequence['name']); $sql = ''; if ($new_sequence['inc'] != null && strcasecmp($new_sequence['inc'], $old_sequence['inc']) != 0) { $sql .= "\n\tINCREMENT BY " . $new_sequence['inc']; } if ($new_sequence['min'] == null && $old_sequence['min'] != null) { $sql .= "\n\tNO MINVALUE"; } else { if ($new_sequence['min'] != null && strcasecmp($new_sequence['min'], $old_sequence['min']) != 0) { $sql .= "\n\tMINVALUE " . $new_sequence['min']; } } if ($new_sequence['max'] == null && $old_sequence['max'] != null) { $sql .= "\n\tNO MAXVALUE"; } else { if ($new_sequence['max'] != null && strcasecmp($new_sequence['max'], $old_sequence['max']) != 0) { $sql .= "\n\tMAXVALUE " . $new_sequence['max']; } } if (!pgsql8_diff::$ignore_start_with) { if ($new_sequence['start'] != null && strcasecmp($new_sequence['start'], $old_sequence['start']) != 0) { $sql .= "\n\tRESTART WITH " . $new_sequence['start']; } } if ($new_sequence['cache'] != null && strcasecmp($new_sequence['cache'], $old_sequence['cache']) != 0) { $sql .= "\n\tCACHE " . $new_sequence['cache']; } if ($old_sequence['cycle'] && !$new_sequence['cycle']) { $sql .= "\n\tNO CYCLE"; } else { if (!$old_sequence['cycle'] && $new_sequence['cycle']) { $sql .= "\n\tCYCLE"; } } if (strlen($sql) > 0) { $ofs->write("ALTER SEQUENCE " . pgsql8::get_quoted_schema_name($new_schema['name']) . '.' . pgsql8::get_quoted_object_name($new_sequence['name']) . $sql . ";\n"); } } } }
/** * Outputs commands for ALTER DOMAIN * @param $ofs output file pointer * @param $old_schema original schema * @param $old_type original type * @param $new_schema new schema * @param $new_type new type */ private static function apply_domain_changes($ofs, $old_schema, $old_type, $new_schema, $new_type) { // http://www.postgresql.org/docs/8.1/static/sql-alterdomain.html $domain = pgsql8::get_quoted_schema_name($new_schema['name']) . '.' . pgsql8::get_quoted_object_name($new_type['name']); $old_domain = $old_type->domainType; $new_domain = $new_type->domainType; // if base type changes, we need to drop and re-add if (strcasecmp($old_domain['baseType'], $new_domain['baseType']) !== 0) { $ofs->write("-- domain base type changed from {$old_domain['baseType']} to {$new_domain['baseType']} - recreating\n"); $ofs->write(pgsql8_type::get_drop_sql($old_schema, $old_type) . "\n"); $ofs->write(pgsql8_type::get_creation_sql($new_schema, $new_type) . "\n"); return; } $base_type = strtolower($new_domain['baseType']); // default is dropped if (isset($old_domain['default']) && !isset($new_domain['default'])) { $ofs->write("-- domain default dropped\n"); $ofs->write("ALTER DOMAIN {$domain} DROP DEFAULT;\n"); } elseif (strcmp($old_domain['default'], $new_domain['default']) !== 0) { $old_default = pgsql8::value_escape($base_type, (string) $old_domain['default']); $new_default = pgsql8::value_escape($base_type, (string) $new_domain['default']); $ofs->write("-- domain default changed from {$old_default}\n"); $ofs->write("ALTER DOMAIN {$domain} SET DEFAULT {$new_default};\n"); } $old_null = strcasecmp($old_domain['null'], 'false') !== 0; $new_null = strcasecmp($new_domain['null'], 'false') !== 0; // NULL -> NOT NULL if ($old_null && !$new_null) { $ofs->write("-- domain changed from NULL to NOT NULL\n"); $ofs->write("ALTER DOMAIN {$domain} SET NOT NULL;\n"); } elseif (!$old_null && $new_null) { $ofs->write("-- domain changed from NOT NULL to NULL\n"); $ofs->write("ALTER DOMAIN {$domain} DROP NOT NULL;\n"); } // diff constraints $old_constraints = array(); foreach ($old_type->domainConstraint as $old_constraint) { $old_constraints[(string) $old_constraint['name']] = pgsql8_type::normalize_domain_constraint($old_constraint); } foreach ($new_type->domainConstraint as $new_constraint) { $name = (string) $new_constraint['name']; $constraint = pgsql8_type::normalize_domain_constraint($new_constraint); if (array_key_exists($name, $old_constraints)) { if (strcmp($constraint, $old_constraints[$name]) !== 0) { $ofs->write("-- domain constraint {$name} changed from {$old_constraints[$name]}\n"); $ofs->write("ALTER DOMAIN {$domain} DROP CONSTRAINT {$name};\n"); $ofs->write("ALTER DOMAIN {$domain} ADD CONSTRAINT {$name} CHECK({$constraint});\n"); } unset($old_constraints[$name]); } else { $ofs->write("-- domain constraint {$name} added\n"); $ofs->write("ALTER DOMAIN {$domain} ADD CONSTRAINT {$name} CHECK({$constraint});\n"); } } foreach ($old_constraints as $name => $constraint) { $ofs->write("-- domain constraint {$name} removed\n"); $ofs->write("ALTER DOMAIN {$domain} DROP CONSTRAINT {$name};\n"); } }
/** * Creates and returns SQL command for dropping the sequence. * * @return string */ public static function get_drop_sql($node_schema, $node_sequence) { format::set_context_replica_set_id($node_sequence); $ddl = "DROP SEQUENCE " . pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_object_name($node_sequence['name']) . ";\n"; return $ddl; }
public static function get_drop_sql($node_schema, $node_table, $node_index) { $ddl = "DROP INDEX " . pgsql8::get_quoted_schema_name($node_schema['name']) . "." . pgsql8::get_quoted_object_name($node_index['name']) . ";\n"; return $ddl; }
/** * 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; }
/** * returns DDL to drop specified schema * * @return string */ public static function get_drop_sql($node_schema) { $ddl = "DROP SCHEMA " . pgsql8::get_quoted_schema_name($node_schema['name']) . " CASCADE;\n"; return $ddl; }
/** * Creates declaration string for the function. The string consists * of function name, '(', list of argument types separated by ',' and ')'. * * @param $node_function */ public static function get_declaration($node_schema, $node_function, $include_names = TRUE) { $r = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_function_name($node_function['name']) . '('; $parameters = dbx::get_function_parameters($node_function); foreach ($parameters as $parameter) { $arg = ''; if (isset($parameter['direction']) && strlen($parameter['direction']) > 0) { $arg .= $parameter['direction'] . ' '; } if ($include_names && strlen($parameter['name']) > 0) { $arg .= $parameter['name'] . ' '; } $arg .= $parameter['type']; $r .= $arg . ', '; } if (count($parameters) > 0) { $r = substr($r, 0, -2); } $r .= ')'; return $r; }
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; }
private static function create_procedure(&$schema, $table) { $code = "DECLARE\n\tmod_result INT;\nBEGIN\n" . "\tmod_result := NEW." . self::$part_column . ' % ' . self::$part_number . ";\n"; $part_append = strlen(self::$part_number); for ($i = 0; $i < self::$part_number; $i++) { $code .= "\t"; if ($i != 0) { $code .= 'ELSE'; } $tname = pgsql8::get_quoted_schema_name($schema['name']) . '.' . pgsql8::get_quoted_table_name('partition_' . sprintf("%0{$part_append}u", $i)); $code .= "IF (mod_result = {$i}) THEN\n" . "\t\tINSERT INTO {$tname} VALUES (NEW.*);\n"; } $code .= "\tEND IF;\n\tRETURN NULL;\nEND;"; $function = $schema->addChild('function'); $def = $function->addChild('functionDefinition', $code); $def->addAttribute('language', 'PLPGSQL'); $def->addAttribute('sqlFormat', 'pgsql8'); $function->addAttribute('name', 'insert_trigger'); $function->addAttribute('returns', 'TRIGGER'); $function->addAttribute('owner', $table['owner']); $function->addAttribute('description', 'DBSteward auto-generated for table partition'); $grant1 = $function->addChild('grant'); $grant1->addAttribute('operation', 'EXECUTE'); $grant1->addAttribute('role', 'ROLE_APPLICATION'); }
public static function get_sql($db_doc, $node_schema, $node_object, $node_permission) { format::set_context_replica_set_id($node_object); $perms = pgsql8_permission::get_permission_operations($node_permission); $roles = preg_split(dbsteward::PATTERN_SPLIT_ROLE, $node_permission['role'], -1, PREG_SPLIT_NO_EMPTY); $object_type = strtoupper($node_object->getName()); switch ($object_type) { case 'SCHEMA': $object_name = pgsql8::get_quoted_schema_name($node_schema['name']); break; case 'SEQUENCE': case 'TABLE': case 'VIEW': $object_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_object['name']); break; case 'FUNCTION': $object_name = pgsql8_function::get_declaration($node_schema, $node_object); break; default: throw new exception("unknown object type encountered: " . $object_type); } $sql = ''; for ($j = 0; $j < count($roles); $j++) { $with = ''; if (isset($node_permission['with']) && strlen($node_permission['with']) > 0) { $with = "WITH " . $node_permission['with'] . " OPTION"; } if (strcasecmp($object_type, 'VIEW') == 0) { // postgresql doesn't want you to name the view keyword when you grant rights to views $pg_object_type = ''; } else { $pg_object_type = $object_type; } if (strlen($sql) > 0) { $sql .= "\n"; } $sql .= self::compile_sql_statement(strtoupper($node_permission->getName()), implode(', ', $perms), $pg_object_type, $object_name, xml_parser::role_enum($db_doc, $roles[$j]), $with); // SCHEMA IMPLICIT GRANTS if (strcasecmp($object_type, 'SCHEMA') == 0) { // READYONLY USER PROVISION: grant usage on the schema for the readonly user if (strlen($db_doc->database->role->readonly) > 0) { if (strlen($sql) > 0) { $sql .= "\n"; } $sql .= self::compile_sql_statement('GRANT', 'USAGE', 'SCHEMA', pgsql8::get_quoted_schema_name($node_schema['name']), $db_doc->database->role->readonly); } } // SEQUENCE IMPLICIT GRANTS if (strcasecmp($object_type, 'SEQUENCE') == 0) { // READYONLY USER PROVISION: generate a SELECT on the sequence for the readonly user if (strlen($db_doc->database->role->readonly) > 0) { if (strlen($sql) > 0) { $sql .= "\n"; } $sql .= self::compile_sql_statement('GRANT', 'SELECT', 'SEQUENCE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_object['name']), $db_doc->database->role->readonly); } } // TABLE IMPLICIT GRANTS if (strcasecmp($object_type, 'TABLE') == 0) { // READYONLY USER PROVISION: grant select on the table for the readonly user if (strlen($db_doc->database->role->readonly) > 0) { if (strlen($sql) > 0) { $sql .= "\n"; } $sql .= self::compile_sql_statement('GRANT', 'SELECT', 'TABLE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_object['name']), $db_doc->database->role->readonly); } // don't need to grant cascaded serial permissions to the table owner if (strcasecmp('ROLE_OWNER', $roles[$j]) == 0) { continue; } // set serial columns permissions based on table permissions foreach ($node_object->column as $column) { if (preg_match(pgsql8::PATTERN_TABLE_LINKED_TYPES, $column['type']) > 0) { $col_sequence = pgsql8::identifier_name($node_schema['name'], $node_object['name'], $column['name'], '_seq'); $seq_priv = array(); // if you can SELECT, INSERT or UPDATE the table, you can SELECT on the sequence if (in_array('SELECT', $perms) || in_array('INSERT', $perms) || in_array('UPDATE', $perms)) { $seq_priv[] = 'SELECT'; } // if you can INSERT or UPDATE the table, you can UPDATE the sequence if (in_array('INSERT', $perms) || in_array('UPDATE', $perms)) { $seq_priv[] = 'UPDATE'; } // if you only have USAGE or SELECT // then seq_priv is empty, and no grant should be issued if (count($seq_priv) > 0) { $with = ''; if (isset($node_permission['with']) && strlen($node_permission['with']) > 0) { $with = "WITH " . $node_permission['with'] . " OPTION"; } if (strlen($sql) > 0) { $sql .= "\n"; } $sql .= self::compile_sql_statement('GRANT', implode(',', $seq_priv), 'SEQUENCE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($col_sequence), xml_parser::role_enum($db_doc, $roles[$j]), $with); } // READYONLY USER PROVISION: grant implicit select on the sequence for the readonly user if (strlen($db_doc->database->role->readonly) > 0) { if (strlen($sql) > 0) { $sql .= "\n"; } $sql .= self::compile_sql_statement('GRANT', 'SELECT', 'SEQUENCE', pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($col_sequence), $db_doc->database->role->readonly); } } } } } return $sql; }