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