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