/**
* 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 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 TYPE command.
*
* @param database database
* @param command CREATE TYPE command
*/
public static function parse($database, $command)
{
$line = $command;
// CREATE PROCEDURAL LANGUAGE plpgsql;
// CREATE [ PROCEDURAL ] LANGUAGE name
// CREATE [ TRUSTED ] [ PROCEDURAL ] LANGUAGE name
// HANDLER call_handler [ VALIDATOR valfunction ]
if (preg_match(self::PATTERN_CREATE_LANGUAGE, $line, $matches) > 0) {
// simplify parsing by killing last semicolon
$line = sql_parser::remove_last_semicolon($line);
// break up the command by whitespace
$chunks = preg_split('/[\\s]+/', $line, -1, PREG_SPLIT_NO_EMPTY);
// shift the LANGUAGE keyword off
array_shift($chunks);
// shift the language name off
$language_name = array_shift($chunks);
// create language entry
$language =& dbx::get_language($database, $language_name, true);
// grab the language modifiers
while (strcasecmp('LANGUAGE', $chunks[0]) != 0) {
if (strcasecmp('CREATE', $chunks[0]) == 0) {
// expected first CREATE lead doesn't modify anything
} else {
if (strcasecmp('TRUSTED', $chunks[0]) == 0) {
dbx::set_attribute($language, 'trusted', $chunks[0]);
} else {
if (strcasecmp('PROCEDURAL', $chunks[0]) == 0) {
dbx::set_attribute($language, 'procedural', $chunks[0]);
} else {
throw new exception("unknown CREATE LANGUAGE modifier: " . $chunks[0]);
}
}
}
// shift the lead chunk off now that it has been interpreted
array_shift($chunks);
}
// if there are chunks left, figure out what optional parameteres they are and save them in the language object
// make sure it's not the trailing ;, we don't care
while (count($chunks) > 0 && trim(implode(' ', $chunks)) != ';') {
if (strcasecmp('HANDLER', $chunks[0]) == 0) {
dbx::set_attribute($language, 'handler', $chunks[1]);
} else {
if (strcasecmp('VALIDATOR', $chunks[0]) == 0) {
dbx::set_attribute($language, 'validator', $chunks[1]);
} else {
throw new exception("unknown CREATE LANGUAGE callback: " . $chunks[0]);
}
}
// shift the lead chunk and its value off now that it has been interpreted
array_shift($chunks);
array_shift($chunks);
}
} else {
throw new exception("Cannot parse command: " . $line);
}
}
/**
* 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_CONFIG_PARAMETER, $command, $matches) > 0) {
if (count($matches) != 3) {
var_dump($matches);
throw new exception("Database configuration parameter call preg exploded into " . count($matches) . ", panic!");
}
// just do what the call does push around the name -> value
$configuration_parameter =& dbx::get_configuration_parameter($database, $matches[1], true);
dbx::set_attribute($configuration_parameter, 'value', $matches[2]);
} 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 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 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);
}
}
/**
* 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 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 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 all rows in ALTER TABLE command.
*
* @param table table being parsed
* @param commands commands
*
* @throws ParserException Thrown if problem occured while parsing DDL.
*/
private static function parse_rows(&$db_doc, &$node_schema, &$node_table, $commands)
{
$line = $commands;
$subCommand = null;
while (strlen($line) > 0) {
$commandEnd = sql_parser::get_command_end($line, 0);
$subCommand = trim(substr($line, 0, $commandEnd));
$line = $commandEnd >= strlen($line) ? "" : substr($line, $commandEnd + 1);
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_ADD_CONSTRAINT_FOREIGN_KEY, $subCommand, $matches) > 0) {
$column_name = trim($matches[3]);
$constraint_name = trim($matches[1]);
$node_constraint = pgsql8_constraint::get_table_constraint($db_doc, $node_table, $constraint_name, true);
dbx::set_attribute($node_constraint, 'definition', trim($matches[2]));
$subCommand = "";
}
}
if (preg_match(self::PATTERN_ADD_CONSTRAINT, $subCommand, $matches) > 0) {
$constraint_name = trim($matches[1]);
$node_constraint = pgsql8_constraint::get_table_constraint($db_doc, $node_table, $constraint_name, true);
dbx::set_attribute($node_constraint, 'definition', trim($matches[2]));
$subCommand = "";
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_ADD_PRIMARY_KEY, $subCommand, $matches) > 0) {
$definition = trim($matches[1]);
$column_name = trim($matches[2]);
$constraint_name = $node_table['name'] . '_pkey';
dbx::set_attribute($node_table, 'primaryKey', $column_name);
$subCommand = "";
}
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_ADD_FOREIGN_KEY, $subCommand, $matches) > 0) {
$column_name = trim($matches[2]);
$constraint_name = pgsql8::identifier_name($node_schema['name'], $node_table['name'], $column_name, '_fkey');
$node_constraint = pgsql8_constraint::get_table_constraint($db_doc, $node_table, $constraint_name, true);
dbx::set_attribute($node_constraint, 'definition', trim($matches[1]));
$subCommand = "";
}
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_SET_DEFAULT, $subCommand, $matches) > 0) {
$column_name = trim($matches[1]);
$default_value = trim($matches[2]);
if ($node_table->contains_column($column_name)) {
$node_column =& dbx::get_column($node_table, $column_name);
dbx::set_attribute($node_column, 'default', $default_value);
} else {
throw new exception("Cannot find column '" . $column_name . " 'in table '" . $node_table['name'] . "'");
}
$subCommand = "";
}
}
if (preg_match(self::PATTERN_ALTER_COLUMN_STATISTICS, $subCommand, $matches) > 0) {
$column_name = trim($matches[2]);
$value = trim($matches[3]);
$node_column =& dbx::get_column($node_table, $column_name);
dbx::set_attribute($node_column, 'statistics', $value);
$subCommand = "";
}
if (preg_match(self::PATTERN_CLUSTER_ON, $subCommand, $matches) > 0) {
$indexName = trim($matches[1]);
dbx::set_attribute($node_column, 'clusterIndexName', $indexName);
$subCommand = "";
}
if (strlen($subCommand) > 0) {
if (preg_match(self::PATTERN_TRIGGER, $subCommand, $matches) > 0) {
$triggerName = trim($matches[2]);
throw new exception("@TODO: do something with ALTER TABLE ... ENABLE / DISABLE trigger statements");
$subCommand = "";
}
}
if (strlen($subCommand) > 0) {
throw new exception("Don't know how to parse: " . $subCommand);
}
}
}
/**
* 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);
}
}
function parse($sql)
{
$model = new sql_parser();
return $model->parse($sql);
}
/**
* @TODO: this needs rewritten to delete the XML document row instead
*/
public static function delete_data_row(&$node_table, $where)
{
// break up the clause by its parenthetical () and logical AND OR segmentation
$clause = sql_parser::clause_explode($where);
if (!is_array($clause)) {
$clause = array($clause);
}
$new_table_rows = dbx::get_table_rows($new_table);
$data_rows = $new_table_rows->row;
dbsteward::trace("CLAUSE " . $where);
dbsteward::trace("BEFORE this->data has " . count($data_rows) . " rows");
for ($i = 0; $i < count($data_rows); $i++) {
if (self::clause_match($data_rows[$i], $clause)) {
unset($data_rows[$i]);
$data_rows = array_merge($data_rows);
// redo array keys
$i--;
}
}
dbsteward::trace("AFTER this->data has " . count($data_rows) . " rows");
}
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;
}
public static function column_split($column_list, $token_quotes)
{
$columns = array();
$in_quoted = false;
$col = "";
for ($i = 0; $i < strlen($column_list); $i++) {
$c = substr($column_list, $i, 1);
$c_next = "";
if ($i < strlen($column_list) - 1) {
$c_next = substr($column_list, $i + 1, 1);
}
// quoted string enclosure
if (in_array($c, $token_quotes)) {
// not escaped apostrophe?
if ($c_next != "'") {
// invert in_quoted state to represent beginning/end
$in_quoted = !$in_quoted;
} else {
// it is, include it in $c value
$c .= $c_next;
$i++;
}
}
// not in quoted string and end of token? (comma)
if (!$in_quoted && $c == ",") {
$col = trim($col);
// explicit or capitalized quoted column names "likeThis" are safe to strip as our column list is case senstive
// and far simplifies pgsql8_table::add_data_row() sanity check logic
$col = sql_parser::quoted_name_strip($col);
$columns[] = $col;
$col = '';
} else {
// not the end, add it to the currently building comma separated value
$col .= $c;
}
}
// if there is column name/data remaining, append it to the array
if (strlen(trim($col)) > 0) {
$columns[] = sql_parser::quoted_name_strip(trim($col));
}
return $columns;
}
/**
* Parses all rows in CREATE TYPE command.
*
* @param type type being parsed
* @param command command without 'CREATE SEQUENCE ... (' string
*
* @throws ParserException Thrown if problem occurred with parsing of DDL.
*/
private static function parse_rows($type, $command)
{
$line = $command;
$post_columns = false;
while (strlen($line) > 0) {
$commandEnd = sql_parser::get_command_end($line, 0);
$subCommand = trim(substr($line, 0, $commandEnd));
if ($post_columns) {
$line = self::parse_post_columns($type, $subCommand);
break;
} else {
if (substr($line, $commandEnd, 1) == ')') {
$post_columns = true;
}
}
self::parse_column_defs($type, $subCommand);
$line = $commandEnd >= strlen($line) ? "" : substr($line, $commandEnd + 1);
}
$line = trim($line);
if (strlen($line) > 0) {
throw new exception("Cannot parse CREATE TYPE '" . $type->get_name() . "' - do not know how to parse '" . $line . "'");
}
}
public static function parse_params($line)
{
if (substr($line, 0, 1) != '(') {
throw new exception("slonik params don't start with (: " . $line);
}
if (substr($line, -1, 1) != ')') {
throw new exception("slonik params don't end with ): " . $line);
}
// knock off the ( and ) now that we know they are there
$param_list = explode(',', substr($line, 1, strlen($line) - 2));
$params = array();
for ($i = 0; $i < count($param_list); $i++) {
// split the params at the =
$chunks = explode('=', trim($param_list[$i]));
// kill whitespace again
$name = trim($chunks[0]);
$value = trim($chunks[1]);
// lowercase the param name
$name = strtolower($name);
// if data value is enclosed in '', kill it
$value = sql_parser::quoted_value_strip($value);
$params[$name] = $value;
}
return $params;
}
/**
* 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;
}