/** * Parses ALTER SEQUENCE command. * * @param database database * @param command ALTER SEQUENCE command */ public static function parse($database, $command) { $line = $command; if (preg_match(self::PATTERN_SEQUENCE_OWNED_BY, $command, $matches) > 0) { $sequenceName = trim($matches[1]); // make sequenceName fully qualified // default_schema will make set search path induced schemas come through correctly $sequenceName = sql_parser::get_schema_name($sequenceName, $database) . '.' . sql_parser::get_object_name($sequenceName); $ownerTable = trim($matches[2]); // make ownerTable fully qualified // default_schema will make set search path induced schemas come through correctly $ownerTable = sql_parser::get_schema_name($ownerTable, $database) . '.' . sql_parser::get_object_name($ownerTable); $schema = $database->get_schema(sql_parser::get_schema_name($sequenceName, $database)); if ($schema == null) { throw new exception("Schema " . sql_parser::get_schema_name($sequenceName, $database) . " not found"); } $sequence = $schema->get_sequence($sequenceName); if ($sequence == null) { throw new exception("Sequence " . $sequenceName . " not found. Is the create for it missing or after the ALTER SEQUENCE statement ?"); } $sequence->set_owned_by($ownerTable); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses CREATE TRIGGER command. * * @param database database * @param command CREATE TRIGGER command * * @throws ParserException Thrown if problem occured while parsing the * command. */ public static function parse($database, $command) { if (preg_match(self::PATTERN, trim($command), $matches) > 0) { $trigger_name = trim($matches[1]); $when = $matches[2]; $events = array(); if (strlen($matches[3]) > 0) { $events[] = $matches[3]; } if (strlen($matches[4]) > 0) { $events[] = $matches[4]; } if (strlen($matches[5]) > 0) { $events[] = $matches[5]; } $table_name = trim($matches[6]); $fireOn = $matches[7]; $procedure = $matches[8]; $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($table_name, $database)); $node_table =& dbx::get_table($node_schema, sql_parser::get_object_name($table_name)); if ($node_table == null) { throw new exception("Failed to find trigger table " . $trigger->get_table_name()); } $node_trigger =& dbx::get_table_trigger($node_schema, $node_table, $trigger_name, true); dbx::set_attribute($node_trigger, 'when', strcasecmp('BEFORE', $when) == 0 ? 'BEFORE' : 'AFTER'); dbx::set_attribute($node_trigger, 'forEach', strcasecmp('ROW', $when) == 0 ? 'ROW' : 'STATEMENT'); dbx::set_attribute($node_trigger, 'function', trim($procedure)); dbx::set_attribute($node_trigger, 'event', implode(', ', $events)); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses CREATE VIEW command. * * @param database database * @param command CREATE VIEW command */ public static function parse($database, $command) { if (preg_match(self::PATTERN, trim($command), $matches) > 0) { $view_name = $matches[1]; $column_names = $matches[2]; $query = $matches[3]; if (strlen($view_name) == 0 || strlen($query) == 0) { throw new exception("Cannot parse command: " . $command); } $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($view_name, $database)); $node_view =& dbx::get_view($node_schema, sql_parser::get_object_name($view_name, $database), true); $node_view->addChild('viewQuery', $query); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses CREATE TYPE command. * * @param database database * @param command CREATE TYPE command */ public static function parse($database, $command) { $line = $command; if (preg_match(self::PATTERN_TYPE_NAME, $line, $matches) > 0) { $typeName = trim($matches[1]); $line = preg_replace(self::PATTERN_TYPE_NAME, '', $line); } else { throw new exception("Cannot parse command: " . $line); } $type = new pgsql8_type(sql_parser::get_object_name($typeName)); $schemaName = sql_parser::get_schema_name($typeName, $database); $schema = $database->get_schema($schemaName); if ($schema == null) { throw new exception("Cannot get schema '" . $schemaName . "'. Need to issue 'CREATE SCHEMA " . $schemaName . ";' before 'CREATE TYPE " . $typeName . "...;'?"); } $schema->add_type($type); self::parse_rows($type, sql_parser::remove_last_semicolon($line)); }
/** * Parses DELETE FROM command. * * @param database database * @param command DELETE FROM command * */ public static function parse($database, $command) { if (preg_match(self::PATTERN_DELETE_FROM, $command, $matches) > 0) { $line = $command; $table_name = $matches[1]; $where_clause = $matches[2]; $table_name = sql_parser::get_schema_name($table_name, $database) . '.' . sql_parser::get_object_name($table_name); $schema = $database->get_schema(sql_parser::get_schema_name($table_name, $database)); if ($schema == null) { throw new exception("Failed to find schema for data delete: " . sql_parser::get_schema_name($table_name, $database)); } $table = $schema->get_table(sql_parser::get_object_name($table_name)); if ($table == null) { throw new exception("Failed to find table for data delete: " . $table_name); } pgsql8_table::delete_data_row($table, $where_clause); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses CREATE INDEX command. * * @param database database * @param command CREATE INDEX command */ public static function parse($database, $command) { if (preg_match(self::CREATE_PATTERN, trim($command), $matches) > 0) { $unique_value = strlen(trim($matches[1])) > 0 ? 'true' : 'false'; $index_name = $matches[2]; $table_name = $matches[3]; $using = trim($matches[4]); if ($index_name == null || $table_name == null || $using == null) { throw new exception("Cannot parse command: " . $command); } $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name(trim($table_name), $database)); $node_table =& dbx::get_table($node_schema, sql_parser::get_object_name(trim($table_name))); if ($node_table == null) { throw new exception("Failed to find table: " . $table_name); } $node_index =& dbx::create_table_index($node_table, $index_name); dbx::set_attribute($node_index, 'using', $using); dbx::set_attribute($node_index, 'unique', $unique_value); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses CREATE SEQUENCE command. * * @param database database * @param command CREATE SEQUENCE command */ public static function parse($database, $command) { $line = $command; if (preg_match(self::PATTERN_SEQUENCE_NAME, $line, $matches) > 0) { $sequence_name = trim($matches[1]); $line = preg_replace(self::PATTERN_SEQUENCE_NAME, '', $line); } else { throw new exception("Cannot parse line: " . $line); } $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($sequence_name, $database)); $node_sequence =& dbx::get_sequence($node_schema, sql_parser::get_object_name($sequence_name), true); $line = sql_parser::remove_last_semicolon($line); $line = self::processMaxValue($node_sequence, $line); $line = self::processMinValue($node_sequence, $line); $line = self::processCycle($node_sequence, $line); $line = self::processCache($node_sequence, $line); $line = self::processIncrement($node_sequence, $line); $line = self::processstart_with($node_sequence, $line); $line = trim($line); if (strlen($line) > 0) { throw new exception("Cannot parse commmand '" . $command . "', string '" . $line . "'"); } }
/** * Parses ALTER FUNCTION command. * * @param database database * @param command ALTER FUNCTION command * */ public static function parse($database, $command) { if (preg_match(self::PATTERN_OWNER, $command, $matches) > 0) { $line = $command; $function_name = trim($matches[1]); // make all functionName's fully qualified // default_schema will make set search path induced schemas come through correctly $function_name = sql_parser::get_schema_name($function_name, $database) . '.' . sql_parser::get_object_name($function_name); $arguments = $matches[2]; $owner_name = trim($matches[3]); $node_schema = dbx::get_schema($database, sql_parser::get_schema_name($function_name, $database)); if ($node_schema == null) { throw new exception("Failed to find function schema for " . $function_name); } $node_function = dbx::get_function($node_schema, sql_parser::get_object_name($function_name)); if ($node_function == null) { throw new exception("Failed to find function " . $function_name . " in schema " . $node_schema['name']); } dbx::set_attribute($node_function, 'owner', $owner_name); } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses ALTER VIEW command. * * @param database database * @param command ALTER VIEW command * */ public static function parse($database, $command) { if (preg_match(self::PATTERN_OWNER, $command, $matches) > 0) { $line = $command; $view_name = trim($matches[1]); // make all view name fully qualified // default_schema will make set search path induced schemas come through correctly $view_schema_name = sql_parser::get_schema_name($view_name, $database); $view_name = sql_parser::get_object_name($view_name); $owner_name = trim($matches[2]); $schema = $database->get_schema($view_schema_name); if ($schema === null) { throw new exception("Failed to find view schema " . $view_schema_name); } $view = $schema->get_view($view_name); if ($view === null) { throw new exception("Failed to find view " . $view_name . " in schema " . $schema->get_name()); } $view->set_owner($owner_name); } else { throw new exception("Cannot parse command: " . $command); } }
public static function parse($database, $command) { if (preg_match(self::PATTERN_INSERT_INTO, $command, $matches) > 0) { $line = $command; $table_name = $matches[1]; $columns_signature = md5($matches[2]); if (!isset(self::$columns_cache[$columns_signature])) { self::$columns_cache[$columns_signature] = $columns = self::column_split($matches[2], array('"')); } else { $columns = self::$columns_cache[$columns_signature]; } $data_row = self::column_split($matches[3], array("'")); if (count($columns) != count($data_row)) { throw new exception("column count " . count($columns) . " does not match data_row count " . count($data_row)); } // merge together as an alpha index row for ($i = 0; $i < count($columns); $i++) { $row[$columns[$i]] = $data_row[$i]; } $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($table_name, $database)); if ($node_schema == null) { throw new exception("Failed to find schema for data append: " . sql_parser::get_schema_name($table_name, $database)); } $node_table =& dbx::get_table($node_schema, sql_parser::get_object_name($table_name)); if ($node_table == null) { throw new exception("Failed to find table for data append: " . $table_name); } try { pgsql8_table::add_data_row($node_table, $row); } catch (Exception $e) { var_dump($command); throw $e; } } else { throw new exception("Cannot parse command: " . $command); } }
/** * Parses all rows in CREATE TABLE command. * * @param $node_schema schema table belongs to * @param $node_table table being parsed * @param $command command without 'CREATE TABLE ... (' string */ private static function parse_rows(&$node_schema, &$node_table, $command) { $line = $command; $post_columns = false; while (strlen($line) > 0) { $command_end = sql_parser::get_command_end($line, 0); $subCommand = trim(substr($line, 0, $command_end)); if ($post_columns) { $line = self::parse_post_columns($node_table, $subCommand); break; } else { if (substr($line, $command_end, 1) == ')') { $post_columns = true; } } // look for modifier tokens and act accordingly $tokens = preg_split("/[\\s]+/", $subCommand, -1, PREG_SPLIT_NO_EMPTY); // start at 2, first is always name, second is always type for ($i = 2; $i < count($tokens); $i++) { if (strcasecmp($tokens[$i], 'UNIQUE') == 0) { // CREATE TABLE test_table ( // test_table_id varchar(64) PRIMARY KEY, // test_table_col_c varchar(100) UNIQUE NOT NULL // ); // NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_table_pkey" for table "test_table" // NOTICE: CREATE TABLE / UNIQUE will create implicit index "test_table_test_table_col_c_key" for table "test_table" dbsteward::debug("NOTICE: CREATE TABLE with UNIQUE column attribute -- creating implicit index \"" . pgsql8_index::index_name(sql_parser::get_object_name($node_table->get_name()), sql_parser::get_object_name($tokens[0]), 'key') . "\" for table \"" . $node_schema->get_name() . '.' . $node_table->get_name() . "\""); $node_index =& dbx::create_table_index($node_table, pgsql8_index::index_name(sql_parser::get_object_name($node_table['name']), sql_parser::get_object_name($tokens[0]), 'key')); dbx::set_attribute($node_index, 'unique', 'true'); dbx::set_attribute($node_index, 'using', 'btree'); $node_index->addChild('indexDimension', sql_parser::get_object_name($tokens[0]))->addAttribute('name', $tokens[0] . '_unq'); // make sure we don't process this token again unset($tokens[$i]); $tokens = array_merge($tokens); $i--; continue; } // @TODO: other cases? // other cases is how you would fix pgsql8_column::parse_definition() throwing 'column definition parse fail' exceptions } $subCommand = implode(' ', $tokens); self::parse_column_defs($node_schema, $node_table, $subCommand); $line = $command_end >= strlen($line) ? "" : substr($line, $command_end + 1); } $line = trim($line); if (strlen($line) > 0) { throw new exception("Cannot parse CREATE TABLE '" . $node_table['name'] . "' - do not know how to parse '" . $line . "'"); } }
/** * Parses ALTER TABLE command. * * @param database database * @param command ALTER TABLE command * */ public static function parse($database, $command) { $line = $command; if (preg_match(self::PATTERN_OWNER, $command, $matches) > 0) { $table_name = trim($matches[1]); $table_owner = sql_parser::remove_last_semicolon(trim($matches[2])); $schema_name = sql_parser::get_schema_name($table_name, $database); $node_schema = dbx::get_schema($database, $schema_name); if ($node_schema == null) { throw new exception("schema " . $schema_name . " not found in database object"); } // is it actually a sequence ownership reference? if (substr($table_name, -4) == '_seq') { //@TODO: figure out what sequences are not table linked and need to have ownership set on them } else { $node_table = dbx::get_table($node_schema, sql_parser::get_object_name($table_name)); if ($node_table == null) { throw new exception("table " . sql_parser::get_object_name($table_name) . " not found in " . $node_schema['name'] . " schema object"); } dbx::set_attribute($node_table, 'owner', $table_owner); } } else { if (preg_match(self::PATTERN_START, $line, $matches) > 0) { $table_name = trim($matches[1]); } else { throw new exception("Cannot parse command: " . $line); } $schema_name = sql_parser::get_schema_name($table_name, $database); $node_schema = dbx::get_schema($database, $schema_name); $node_table = dbx::get_table($node_schema, sql_parser::get_object_name($table_name)); $line = sql_parser::remove_last_semicolon($matches[2]); self::parse_rows($database, $node_schema, $node_table, $line); } }
/** * Parses CREATE FUNCTION and CREATE OR REPLACE FUNCTION command. * * @param database database * @param command CREATE FUNCTION command */ public static function parse($database, $command) { if (preg_match(self::PATTERN, trim($command), $matches) > 0) { $function_name = trim($matches[1]); // make all functionName's fully qualified // default_schema will make set search path induced schemas come through correctly $function_name = sql_parser::get_schema_name($function_name, $database) . '.' . sql_parser::get_object_name($function_name); $arguments = $matches[2]; $node_schema = dbx::get_schema($database, sql_parser::get_schema_name($function_name, $database)); if ($node_schema == null) { throw new exception("Failed to find function schema for " . $function_name); } $node_function = dbx::get_function($node_schema, sql_parser::get_object_name($function_name), null, true); //@TODO: this may be a problem when there is more than one prototype for a function $function_declaration = pgsql8_function::set_declaration($node_schema, $node_function, $arguments); // check remaining definition for function modifiers by chopping of function declaration $function_close_position = stripos($command, ')'); $function_modifiers = str_replace("\n", ' ', substr($command, $function_close_position + 1)); // kill extra whitespace by regex match $function_modifiers = preg_replace("/\\s+/", " ", $function_modifiers); // kill trailing semicolon $function_modifiers = trim($function_modifiers); if (substr($function_modifiers, -1) == ';') { $function_modifiers = trim(substr($function_modifiers, 0, -1)); } $function_modifiers = ' ' . $function_modifiers . ' '; // AS token (definition) token // AS $_$ BEGIN DO STUFF END $_$ if (($as_pos = stripos($function_modifiers, ' AS ')) !== false) { $end_as_token_pos = strpos($function_modifiers, ' ', $as_pos + 4); $as_token = substr($function_modifiers, $as_pos + 4, $end_as_token_pos - ($as_pos + 4)); $definition_start = strpos($function_modifiers, $as_token, $as_pos) + strlen($as_token); $definition_end = strpos($function_modifiers, $as_token, $definition_start + strlen($as_token)); $definition = substr($function_modifiers, $definition_start, $definition_end - $definition_start); $definition = trim($definition); pgsql8_function::set_definition($node_function, $definition); // cut out what we just found $function_modifiers = substr($function_modifiers, 0, $as_pos) . ' ' . substr($function_modifiers, $definition_end + strlen($as_token)); } // now that the AS <token> (definition) <token> section is gone, parsing is simpler: // RETURNS (type) if (preg_match(self::PATTERN_RETURNS, $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'returns', trim($matches[1])); } // LANGUAGE (languagename) if (preg_match(self::PATTERN_LANGUAGE, $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'language', trim($matches[1])); } // check for IMMUTABLE | STABLE | VOLATILE modifiers if (preg_match('/.*\\s+IMMUTABLE\\s+.*/i', $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'cachePolicy', 'IMMUTABLE'); } if (preg_match('/.*\\s+STABLE\\s+.*/i', $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'cachePolicy', 'STABLE'); } if (preg_match('/.*\\s+VOLATILE\\s+.*/i', $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'cachePolicy', 'VOLATILE'); } // check for SECURITY DEFINER modifier if (preg_match('/.*\\s+SECURITY DEFINER\\s+.*/i', $function_modifiers, $matches) > 0) { dbx::set_attribute($node_function, 'securityDefiner', 'true'); } } else { throw new exception("Cannot parse command: " . $command); } }
public static function build($output_prefix, $db_doc) { if (strlen($output_prefix) == 0) { throw new exception("pgsql8::build() sanity failure: output_prefix is blank"); } // build full db creation script $build_file = $output_prefix . '_build.sql'; dbsteward::info("Building complete file " . $build_file); $build_file_fp = fopen($build_file, 'w'); if ($build_file_fp === FALSE) { throw new exception("failed to open full file " . $build_file . ' for output'); } $build_file_ofs = new output_file_segmenter($build_file, 1, $build_file_fp, $build_file); if (count(dbsteward::$limit_to_tables) == 0) { $build_file_ofs->write("-- full database definition file generated " . date('r') . "\n"); } if (!dbsteward::$generate_slonik) { $build_file_ofs->write("BEGIN;\n\n"); } dbsteward::info("Calculating table foreign key dependency order.."); $table_dependency = xml_parser::table_dependency_order($db_doc); // database-specific implementation code refers to dbsteward::$new_database when looking up roles/values/conflicts etc dbsteward::$new_database = $db_doc; dbx::set_default_schema($db_doc, 'public'); // language defintions if (dbsteward::$create_languages) { foreach ($db_doc->language as $language) { $build_file_ofs->write(pgsql8_language::get_creation_sql($language)); } } // by default, postgresql will validate the contents of LANGUAGE SQL functions during creation // because we are creating all functions before tables, this doesn't work when LANGUAGE SQL functions // refer to tables yet to be created. // scan language="sql" functions for <functionDefiniton>s that contain FROM (<TABLE>) statements $set_check_function_bodies = TRUE; // on in default postgresql configs dbx::set_default_schema($db_doc, 'public'); foreach ($db_doc->schema as $schema) { foreach ($schema->function as $function) { if (pgsql8_function::has_definition($function)) { $definition = pgsql8_function::get_definition($function); if (strcasecmp($definition['language'], 'sql') == 0 && $definition['sqlFormat'] == 'pgsql8' && !is_null($referenced_table_name = static::function_definition_references_table($definition))) { $table_schema_name = sql_parser::get_schema_name($referenced_table_name, $db_doc); $node_schema = dbx::get_schema($db_doc, $table_schema_name); $node_table = dbx::get_table($node_schema, sql_parser::get_object_name($referenced_table_name)); if ($node_table) { // the referenced table is in the definition // turn off check_function_bodies $set_check_function_bodies = FALSE; $set_check_function_bodies_info = "Detected LANGUAGE SQL function " . $schema['name'] . '.' . $function['name'] . " referring to table " . $table_schema_name . '.' . $node_table['name'] . " in the database definition"; dbsteward::info($set_check_function_bodies_info); break 2; } } } } } if (!$set_check_function_bodies) { $build_file_ofs->write("\n"); $build_file_ofs->write("SET check_function_bodies = FALSE; -- DBSteward " . $set_check_function_bodies_info . "\n\n"); } if (dbsteward::$only_schema_sql || !dbsteward::$only_data_sql) { dbsteward::info("Defining structure"); pgsql8::build_schema($db_doc, $build_file_ofs, $table_dependency); } if (!dbsteward::$only_schema_sql || dbsteward::$only_data_sql) { dbsteward::info("Defining data inserts"); pgsql8::build_data($db_doc, $build_file_ofs, $table_dependency); } dbsteward::$new_database = NULL; if (!dbsteward::$generate_slonik) { $build_file_ofs->write("COMMIT;\n\n"); } if (dbsteward::$generate_slonik) { $replica_sets = static::get_slony_replica_sets($db_doc); foreach ($replica_sets as $replica_set) { // output preamble file standalone for tool chains that use the preamble to do additional slonik commands pgsql8::build_slonik_preamble($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_preamble.slonik"); // output paths specificity standalone for tool chains that use the store path slonik statements separately pgsql8::build_slonik_paths($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_paths.slonik"); // output create set file standalone for tool chains that use the create_set slonik separately $create_set_filename = $output_prefix . '_slony_replica_set_' . $replica_set['id'] . '_create_set.slonik'; pgsql8::build_slonik_create_set($db_doc, $replica_set, $create_set_filename); pgsql8::build_slonik_preamble($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik"); pgsql8::build_slonik_store_nodes($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik"); pgsql8::build_slonik_paths($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_create_nodes.slonik"); // build full subscribe steps that creates sets and subscribes nodes $subscribe_filename = $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_subscribe.slonik"; pgsql8::build_slonik_preamble($db_doc, $replica_set, $subscribe_filename); // create_set does one time slony configuration comparison. // so append the content of _create_set into _subscribe built earlier file_put_contents($subscribe_filename, file_get_contents($create_set_filename), FILE_APPEND); foreach ($replica_set->slonyReplicaSetNode as $replica_set_node) { pgsql8::build_slonik_subscribe_set_node($db_doc, $replica_set, $output_prefix . "_slony_replica_set_" . $replica_set['id'] . "_subscribe.slonik", $replica_set_node); } static::slony_ids_required_during_build($replica_set, $db_doc); } $count = 0; foreach (array_keys(self::$sequence_slony_ids) as $slony_set_id) { $count += count(self::$sequence_slony_ids[$slony_set_id]); } dbsteward::notice("[slony] ID summary: " . count(self::$table_slony_ids) . " tables " . $count . " sequences"); dbsteward::notice("[slony] table ID segments: " . static::slony_id_segment_summary(self::$table_slony_ids)); // keep this from bombing on there being no ids in $sequence_slony_ids // if there were none returned (i.e. either there weren't any defined // or they were all set to IGNORE_REQUIRED which hopefully doesn't happen // because why would you do that for all of them) if (!empty(self::$sequence_slony_ids)) { foreach (array_keys(self::$sequence_slony_ids) as $slony_set_id) { $console_line = "[slony] sequence ID segments"; if ($slony_set_id != 'NoSlonySet') { $console_line .= " for slonySetId {$slony_set_id}"; } $console_line .= ": "; dbsteward::notice($console_line . static::slony_id_segment_summary(self::$sequence_slony_ids[$slony_set_id])); } } } return $db_doc; }
/** * Parses GRANT and REVOKE commands * * @param database database * @param command REVOKE command * */ public static function parse($database, $command) { $command = sql_parser::remove_last_semicolon($command); if (preg_match(self::PATTERN_GRANT_REVOKE, $command, $matches) > 0) { if (count($matches) != 5) { throw new exception("GRANT/REVOKE definition preg exploded into " . count($matches) . ", panic!"); } $action = strtoupper($matches[1]); switch ($action) { case 'GRANT': case 'REVOKE': break; default: throw new exception("permission action " . $action . " is unknown, panic!"); break; } $operations = preg_split("/[\\,\\s]+/", $matches[2], -1, PREG_SPLIT_NO_EMPTY); if (!is_array($operations)) { $permission = array($operations); } for ($i = 0; $i < count($operations); $i++) { $operations[$i] = strtoupper($operations[$i]); switch ($operations[$i]) { case 'ALL': case 'SELECT': case 'INSERT': case 'UPDATE': case 'DELETE': case 'USAGE': case 'REFERENCES': case 'TRIGGER': break; default: var_dump($operations); throw new exception("the operation " . $operations[$i] . " is unknown, panic!"); break; } } $object = $matches[3]; $chunks = preg_split("/[\\s]+/", $object, -1, PREG_SPLIT_NO_EMPTY); if (count($chunks) == 1) { // if there is no white space separating this bit // then let postgresql decide what it is when the grant is run $object_type = ''; $object_name = $chunks[0]; } else { if (count($chunks) == 2) { // SEQUENCE schema.table_table_id_seq // TABLE schema.table $object_type = $chunks[0]; $object_name = $chunks[1]; // if it's a schema, don't try to explode / default the schema prefix if (strcasecmp($object_type, 'SCHEMA') == 0) { $schema =& dbx::get_schema($database, $object_name); } else { $object_name = sql_parser::get_schema_name($object_name, $database) . '.' . sql_parser::get_object_name($object_name); $schema =& dbx::get_schema($database, sql_parser::get_schema_name($object_name, $database)); } if ($schema == null) { throw new exception("Failed to find schema for grant/revoke: " . sql_parser::get_schema_name($object_name, $database)); } } else { throw new exception("object definition exploded into " . count($chunks) . " chunks, panic!"); } } $role = $matches[4]; // find the node_object, swtich'd on $object_type // based on http://www.postgresql.org/docs/8.4/static/sql-grant.html // empty object_type should be considered a TABLE GRANT/REVOKE if (strlen($object_type) == 0) { $object_type = 'TABLE'; } /* var_dump($command); var_dump(sql_parser::get_schema_name($object_name, $database)); var_dump(sql_parser::get_object_name($object_name)); /**/ switch (strtoupper($object_type)) { case 'SCHEMA': $node_object =& dbx::get_schema($database, $object_name); break; case 'SEQUENCE': $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($object_name, $database)); $node_object =& dbx::get_sequence($node_schema, sql_parser::get_object_name($object_name)); break; case 'TABLE': $node_schema =& dbx::get_schema($database, sql_parser::get_schema_name($object_name, $database)); $node_object =& dbx::get_table($node_schema, sql_parser::get_object_name($object_name)); break; default: throw new exception("unknown object_type " . $object_type . " encountered, panic!"); break; } dbx::set_permission($node_object, $action, $operations, $role); } else { throw new exception("Cannot parse command: " . $command); } }
public static function enum_type_check($db_doc, $node_schema, $node_table, $node_column, &$drop_sql, &$add_sql) { // if the column type is a defined enum, (re)add a check constraint to enforce the pseudo-enum $foreign = array(); $column_type = mssql10_column::column_type($db_doc, $node_schema, $node_table, $node_column, $foreign, FALSE); if (preg_match('/' . dbx::enum_regex($db_doc) . '/i', $column_type) > 0) { $type_schema_name = sql_parser::get_schema_name($column_type, $db_doc); $type_schema = dbx::get_schema($db_doc, $type_schema_name); $node_type = dbx::get_type($type_schema, sql_parser::get_object_name($column_type, $db_doc)); if (!$node_type) { var_dump($node_type); throw new exception('failed to find column_type ' . $column_type . ' in type_schema_name ' . $type_schema_name); } $drop_sql = mssql10_type::get_drop_check_sql($node_schema, $node_table, $node_column, $node_type); $add_sql = mssql10_type::get_add_check_sql($node_schema, $node_table, $node_column, $node_type); return TRUE; } return FALSE; }