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