示例#1
0
 /**
  * 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;
 }
示例#2
0
 /**
  * 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;
 }
示例#3
0
 /**
  * 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;
 }
示例#4
0
 /**
  * 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;
 }