/**
* @group pgsql8
* @group mysql5
* @group mssql10
*/
public function testThrowWhenFalse()
{
dbsteward::$ignore_custom_roles = FALSE;
try {
xml_parser::role_enum($this->dbdoc, 'invalid');
} catch (Exception $ex) {
$this->assertEquals('Failed to confirm custom role: invalid', $ex->getMessage());
return;
}
$this->fail("Expected exception when not ignoring custom roles");
}
public static function get_creation_sql($db_doc, $node_schema, $node_view)
{
if (isset($node_view['description']) && strlen($node_view['description']) > 0) {
$ddl = "-- {$node_view['description']}\n";
}
$view_name = mysql5::get_fully_qualified_table_name($node_schema['name'], $node_view['name']);
$definer = strlen($node_view['owner']) > 0 ? xml_parser::role_enum($db_doc, $node_view['owner']) : 'CURRENT_USER';
$ddl = "CREATE OR REPLACE DEFINER = {$definer} SQL SECURITY DEFINER VIEW {$view_name}\n";
$ddl .= " AS " . static::get_view_query($node_view) . ";";
return $ddl;
}
/**
* Creates and returns SQL for creation of trigger.
*
* @return created SQL
*/
public static function get_creation_sql($node_language)
{
/*
CREATE [ PROCEDURAL ] LANGUAGE name
CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
HANDLER call_handler [ VALIDATOR valfunction ]
/**/
$ddl = "CREATE " . (strcasecmp(dbsteward::string_cast($node_language['trusted']), 'true') == 0 ? "TRUSTED " : "") . (strcasecmp(dbsteward::string_cast($node_language['procedural']), 'true') == 0 ? "PROCEDURAL " : "") . "LANGUAGE " . pgsql8::get_quoted_object_name($node_language['name']) . (strlen($node_language['handler']) > 0 ? "HANDLER " . pgsql8::get_quoted_function_name($node_language['handler']) : "") . (strlen($node_language['validator']) > 0 ? "VALIDATOR " . pgsql8::get_quoted_function_name($node_language['validator']) : "") . " ;\n";
if (strlen($node_language['owner']) > 0) {
$ddl .= "ALTER " . (strcasecmp(dbsteward::string_cast($node_language['procedural']), 'true') == 0 ? "PROCEDURAL " : "") . "LANGUAGE " . pgsql8::get_quoted_object_name($node_language['name']) . " OWNER TO " . xml_parser::role_enum(dbsteward::$new_database, $node_language['owner']) . " ;\n";
}
return $ddl;
}
/**
* Creates and returns SQL for creation of the view.
*
* @return string
*/
public static function get_creation_sql($db_doc, $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);
}
if (isset($node_view['description']) && strlen($node_view['description']) > 0) {
$ddl = "-- " . dbsteward::string_cast($node_view['description']) . "\n";
}
$view_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_table_name($node_view['name']);
$ddl = "CREATE OR REPLACE VIEW " . $view_name;
$ddl .= "\n\tAS " . pgsql8_view::get_view_query($node_view) . ";\n";
if (isset($node_view['owner']) && strlen($node_view['owner']) > 0) {
$ddl .= "ALTER VIEW " . $view_name . "\n\tOWNER TO " . xml_parser::role_enum($db_doc, $node_view['owner']) . ";\n";
}
return $ddl;
}
/**
* Creates and returns SQL for creation of the schema.
*
* @return created SQL
*/
public static function get_creation_sql($node_schema)
{
if (strcasecmp('public', $node_schema['name']) == 0) {
// don't create the public schema
$ddl = '';
} else {
$schema_name = pgsql8::get_quoted_schema_name($node_schema['name']);
$ddl = "CREATE SCHEMA " . $schema_name . ";\n";
// schema ownership
if (isset($node_schema['owner']) && strlen($node_schema['owner']) > 0) {
// see dtd owner attribute enum: ROLE_OWNER, ROLE_APPLICATION, ROLE_SLONY
// map ROLE_ enums to database->role->owner etc
$ddl .= "ALTER SCHEMA " . $schema_name . " OWNER TO " . xml_parser::role_enum(dbsteward::$new_database, $node_schema['owner']) . ";\n";
}
// schema comment
if (isset($node_schema['description']) && strlen($node_schema['description']) > 0) {
$ddl .= "COMMENT ON SCHEMA " . $schema_name . " IS '" . pg_escape_string($node_schema['description']) . "';\n";
}
}
return $ddl;
}
public static function get_sql($db_doc, $node_schema, $node_object, $node_permission)
{
$operations = mssql10_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());
$sql = '';
switch ($object_type) {
case 'SCHEMA':
$object_name = mssql10::get_quoted_schema_name($node_schema['name']);
for ($i = 0; $i < count($operations); $i++) {
// see http://msdn.microsoft.com/en-us/library/ms187940.aspx
if (strcasecmp($operations[$i], 'USAGE') == 0) {
// for schemas, translate USAGE into SELECT
$operations[$i] = 'SELECT';
}
if (strcasecmp($operations[$i], 'ALL') == 0) {
$operations[$i] = 'SELECT';
$operations[] = 'INSERT';
$operations[] = 'UPDATE';
$operations[] = 'DELETE';
}
// CREATE TABLE permission is database-wide
// so create it explicitly here in-line
// and then remove it from the list of operations to define
if (strcasecmp($operations[$i], 'CREATE TABLE') == 0) {
for ($j = 0; $j < count($roles); $j++) {
$sql .= "GRANT CREATE TABLE TO " . mssql10::get_quoted_object_name(xml_parser::role_enum($db_doc, $roles[$j])) . ";\n";
}
unset($operations[$i]);
$operations = array_merge($operations);
$i--;
}
}
break;
case 'SEQUENCE':
for ($i = 0; $i < count($operations); $i++) {
// for sequences, translate USAGE into INSERT
if (strcasecmp($operations[$i], 'USAGE') == 0) {
$operations[$i] = 'INSERT';
}
}
// give explicit DELETE permission for pseudo sequences, implemented as mssql10_bit_table
if (!in_array('DELETE', $operations)) {
$operations[] = 'DELETE';
}
case 'TABLE':
case 'VIEW':
case 'FUNCTION':
$object_name = mssql10::get_quoted_schema_name($node_schema['name']) . '.' . mssql10::get_quoted_table_name($node_object['name']);
break;
default:
throw new exception("unknown object type encountered: " . $object_type);
}
for ($j = 0; $j < count($roles); $j++) {
$with = '';
if (isset($node_permission['with']) && strlen($node_permission['with']) > 0) {
$with = "WITH " . $node_permission['with'] . " OPTION";
}
// treat sequences as tables, because that's how mssql10_bit_table created them
if (strcasecmp($object_type, 'SEQUENCE') == 0) {
$object_type = 'TABLE';
}
// translate pg to ms object type names that the GRANT ... CLASS :: specifier accepts
$ms_object_type = $object_type;
// in mssql a table is an object when doing grants
if (strcasecmp($object_type, 'TABLE') == 0) {
$ms_object_type = 'OBJECT';
}
// in mssql a view is an object when doing grants
if (strcasecmp($object_type, 'VIEW') == 0) {
$ms_object_type = 'OBJECT';
}
// in mssql a function and a procedure is an object when doing grants
if (strcasecmp($object_type, 'FUNCTION') == 0) {
$ms_object_type = 'OBJECT';
}
if (strlen($sql) > 0) {
$sql .= "\n";
}
$sql .= self::compile_sql_statement(strtoupper($node_permission->getName()), implode(', ', $operations), $ms_object_type, $object_name, mssql10::get_quoted_object_name(xml_parser::role_enum($db_doc, $roles[$j])), $with);
}
return $sql;
}
/**
* Creates and returns SQL command for creation of the sequence.
*
* @return created SQL command
*/
public static function get_creation_sql($node_schema, $node_sequence)
{
if (isset($node_sequence['start']) && !is_numeric((string) $node_sequence['start'])) {
throw new exception("start value is not numeric: " . $node_sequence['start']);
}
if (isset($node_sequence['inc']) && !is_numeric((string) $node_sequence['inc'])) {
throw new exception("increment by value is not numeric: " . $node_sequence['inc']);
}
if (isset($node_sequence['max']) && !is_numeric((string) $node_sequence['max'])) {
throw new exception("max value is not numeric: " . $node_sequence['max']);
}
if (isset($node_sequence['min']) && !is_numeric((string) $node_sequence['min'])) {
throw new exception("min value is not numeric: " . $node_sequence['min']);
}
format::set_context_replica_set_id($node_sequence);
$cache = 1;
if (isset($node_sequence['cache'])) {
if (!is_numeric((string) $node_sequence['cache'])) {
throw new exception($node_schema['name'] . '.' . $node_sequence['name'] . " cache parameter for is not numeric: " . $node_sequence['cache']);
}
$cache = (int) $node_sequence['cache'];
}
if (isset($node_sequence['start'])) {
$start = (int) $node_sequence['start'];
} else {
if (isset($node_sequence['min'])) {
$start = (int) $node_sequence['min'];
} else {
$start = '1';
}
}
if (isset($node_sequence['inc'])) {
$inc = (int) $node_sequence['inc'];
} else {
$inc = '1';
}
$max_value = (int) $node_sequence['max'];
if ($max_value > 0) {
$max_value = 'MAXVALUE ' . $max_value;
} else {
$max_value = 'NO MAXVALUE';
}
$min_value = (int) $node_sequence['min'];
if ($min_value > 0) {
$min_value = 'MINVALUE ' . $min_value;
} else {
$min_value = 'NO MINVALUE';
}
$cycle = 'NO CYCLE';
if (strlen($node_sequence['cycle']) > 0 && strcasecmp($node_sequence['cycle'], 'false') != 0) {
$cycle = 'CYCLE';
}
$owned_by = '';
if (strlen($node_sequence['ownedBy']) > 0) {
$owned_by = " OWNED BY " . $node_sequence['ownedBy'];
}
$sequence_name = pgsql8::get_quoted_schema_name($node_schema['name']) . '.' . pgsql8::get_quoted_object_name($node_sequence['name']);
$ddl = "CREATE SEQUENCE " . $sequence_name . "\n\tSTART WITH " . $start . "\n\tINCREMENT BY " . $inc . "\n\t" . $max_value . "\n\t" . $min_value . "\n\tCACHE " . $cache . "\n\t" . $cycle . $owned_by . ";\n";
// sequence ownership
if (isset($node_sequence['owner'])) {
$ddl .= "ALTER TABLE " . $sequence_name . " OWNER TO " . xml_parser::role_enum(dbsteward::$new_database, $node_sequence['owner']) . ";\n";
}
// sequence comment
if (isset($schema['description']) && strlen($schema['description']) > 0) {
$ddl .= "COMMENT ON SCHEMA " . $sequence_name . " IS '" . pg_escape_string($schema['description']) . "';\n";
}
return $ddl;
}
public static function equals($node_schema_a, $node_function_a, $node_function_b, $ignore_function_whitespace)
{
if (strcasecmp($node_function_a['name'], $node_function_b['name']) != 0) {
return false;
}
$a_definition = pgsql8_function::get_definition($node_function_a);
$b_definition = pgsql8_function::get_definition($node_function_b);
if ($ignore_function_whitespace) {
$a_definition = preg_replace("/\\s+/", " ", $a_definition);
$b_definition = preg_replace("/\\s+/", " ", $b_definition);
}
$db_doc = $node_schema_a->xpath('..');
$a_owner = xml_parser::role_enum($db_doc[0], $node_function_a['owner']);
$b_owner = xml_parser::role_enum($db_doc[0], $node_function_b['owner']);
$equals = strcasecmp(pgsql8_function::get_declaration($node_schema_a, $node_function_a), pgsql8_function::get_declaration($node_schema_a, $node_function_b)) == 0 && strcasecmp($a_definition, $b_definition) == 0 && strcasecmp($a_owner, $b_owner) == 0 && strcasecmp($node_function_a['type'], $node_function_b['type']) == 0;
return $equals;
}
public static function get_permission_roles($db_doc, $node_permission)
{
return array_map(function ($o) use($db_doc) {
return xml_parser::role_enum($db_doc, $o);
}, preg_split(dbsteward::PATTERN_SPLIT_ROLE, $node_permission['role'], -1, PREG_SPLIT_NO_EMPTY));
}
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;
}
public static function get_creation_sql($node_schema, $node_function)
{
$name = static::get_declaration($node_schema, $node_function);
$definer = strlen($node_function['owner']) > 0 ? xml_parser::role_enum(dbsteward::$new_database, $node_function['owner']) : 'CURRENT_USER';
// always drop the function first, just to be safe, and to be compatible with pgsql8's CREATE OR REPLACE
$sql = static::get_drop_sql($node_schema, $node_function) . "\n";
if (mysql5::$swap_function_delimiters) {
$sql .= 'DELIMITER ' . static::ALT_DELIMITER . "\n";
}
$function_type = static::is_procedure($node_function) ? 'PROCEDURE' : 'FUNCTION';
$sql .= "CREATE DEFINER = {$definer} {$function_type} {$name} (";
if (isset($node_function->functionParameter)) {
$params = array();
foreach ($node_function->functionParameter as $param) {
if (isset($param['direction']) && !static::is_procedure($node_function)) {
throw new exception("Parameter directions are not supported in MySQL functions");
}
if (empty($param['name'])) {
throw new exception("Function parameters must have names in MySQL. In function '{$node_function['name']}'");
}
$type = $param['type'];
if ($node_type = mysql5_type::get_type_node(dbsteward::$new_database, $node_schema, $type)) {
$type = mysql5_type::get_enum_type_declaration($node_type);
}
$sparam = '';
if (isset($param['direction'])) {
$sparam .= (string) $param['direction'] . ' ';
}
$sparam .= mysql5::get_quoted_function_parameter($param['name']) . ' ' . $type;
$params[] = $sparam;
}
$sql .= implode(', ', $params);
}
$sql .= ")\n";
// Procedures don't have a return statement
if (!static::is_procedure($node_function)) {
$returns = $node_function['returns'];
if ($node_type = mysql5_type::get_type_node(dbsteward::$new_database, $node_schema, $returns)) {
$returns = mysql5_type::get_enum_type_declaration($node_type);
}
$sql .= "RETURNS " . $returns . "\n";
}
$sql .= "LANGUAGE SQL\n";
list($eval_type, $determinism) = static::get_characteristics((string) $node_function['cachePolicy'], (string) $node_function['mysqlEvalType']);
$eval_type = str_replace('_', ' ', $eval_type);
$sql .= "{$eval_type}\n{$determinism}\n";
// unlike pgsql8, mysql5 defaults to SECURITY DEFINER, so we need to set it to INVOKER unless explicitly told to leave it DEFINER
if (!isset($node_function['securityDefiner']) || strcasecmp($node_function['securityDefiner'], 'false') == 0) {
$sql .= "SQL SECURITY INVOKER\n";
} else {
$sql .= "SQL SECURITY DEFINER\n";
}
if (!empty($node_function['description'])) {
$sql .= "COMMENT " . mysql5::quote_string_value($node_function['description']) . "\n";
}
$sql .= trim(static::get_definition($node_function));
$sql = rtrim($sql, ';');
if (mysql5::$swap_function_delimiters) {
$sql .= static::ALT_DELIMITER . "\nDELIMITER ;";
} else {
$sql .= ';';
}
return $sql;
}