/**
* 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;
}
/**
* Modify sequence values if they have changed
*
* @param $ofs output file pointer
* @param $old_schema original schema
* @param $new_schema new schema
*/
private static function add_modified_sequences($ofs, $old_schema, $new_schema)
{
foreach (dbx::get_sequences($new_schema) as $new_sequence) {
if ($old_schema != null && pgsql8_schema::contains_sequence($old_schema, $new_sequence['name'])) {
$old_sequence = dbx::get_sequence($old_schema, $new_sequence['name']);
$sql = '';
if ($new_sequence['inc'] != null && strcasecmp($new_sequence['inc'], $old_sequence['inc']) != 0) {
$sql .= "\n\tINCREMENT BY " . $new_sequence['inc'];
}
if ($new_sequence['min'] == null && $old_sequence['min'] != null) {
$sql .= "\n\tNO MINVALUE";
} else {
if ($new_sequence['min'] != null && strcasecmp($new_sequence['min'], $old_sequence['min']) != 0) {
$sql .= "\n\tMINVALUE " . $new_sequence['min'];
}
}
if ($new_sequence['max'] == null && $old_sequence['max'] != null) {
$sql .= "\n\tNO MAXVALUE";
} else {
if ($new_sequence['max'] != null && strcasecmp($new_sequence['max'], $old_sequence['max']) != 0) {
$sql .= "\n\tMAXVALUE " . $new_sequence['max'];
}
}
if (!pgsql8_diff::$ignore_start_with) {
if ($new_sequence['start'] != null && strcasecmp($new_sequence['start'], $old_sequence['start']) != 0) {
$sql .= "\n\tRESTART WITH " . $new_sequence['start'];
}
}
if ($new_sequence['cache'] != null && strcasecmp($new_sequence['cache'], $old_sequence['cache']) != 0) {
$sql .= "\n\tCACHE " . $new_sequence['cache'];
}
if ($old_sequence['cycle'] && !$new_sequence['cycle']) {
$sql .= "\n\tNO CYCLE";
} else {
if (!$old_sequence['cycle'] && $new_sequence['cycle']) {
$sql .= "\n\tCYCLE";
}
}
if (strlen($sql) > 0) {
$ofs->write("ALTER SEQUENCE " . pgsql8::get_quoted_schema_name($new_schema['name']) . '.' . pgsql8::get_quoted_object_name($new_sequence['name']) . $sql . ";\n");
}
}
}
}
/**
* Outputs commands for ALTER DOMAIN
* @param $ofs output file pointer
* @param $old_schema original schema
* @param $old_type original type
* @param $new_schema new schema
* @param $new_type new type
*/
private static function apply_domain_changes($ofs, $old_schema, $old_type, $new_schema, $new_type)
{
// http://www.postgresql.org/docs/8.1/static/sql-alterdomain.html
$domain = pgsql8::get_quoted_schema_name($new_schema['name']) . '.' . pgsql8::get_quoted_object_name($new_type['name']);
$old_domain = $old_type->domainType;
$new_domain = $new_type->domainType;
// if base type changes, we need to drop and re-add
if (strcasecmp($old_domain['baseType'], $new_domain['baseType']) !== 0) {
$ofs->write("-- domain base type changed from {$old_domain['baseType']} to {$new_domain['baseType']} - recreating\n");
$ofs->write(pgsql8_type::get_drop_sql($old_schema, $old_type) . "\n");
$ofs->write(pgsql8_type::get_creation_sql($new_schema, $new_type) . "\n");
return;
}
$base_type = strtolower($new_domain['baseType']);
// default is dropped
if (isset($old_domain['default']) && !isset($new_domain['default'])) {
$ofs->write("-- domain default dropped\n");
$ofs->write("ALTER DOMAIN {$domain} DROP DEFAULT;\n");
} elseif (strcmp($old_domain['default'], $new_domain['default']) !== 0) {
$old_default = pgsql8::value_escape($base_type, (string) $old_domain['default']);
$new_default = pgsql8::value_escape($base_type, (string) $new_domain['default']);
$ofs->write("-- domain default changed from {$old_default}\n");
$ofs->write("ALTER DOMAIN {$domain} SET DEFAULT {$new_default};\n");
}
$old_null = strcasecmp($old_domain['null'], 'false') !== 0;
$new_null = strcasecmp($new_domain['null'], 'false') !== 0;
// NULL -> NOT NULL
if ($old_null && !$new_null) {
$ofs->write("-- domain changed from NULL to NOT NULL\n");
$ofs->write("ALTER DOMAIN {$domain} SET NOT NULL;\n");
} elseif (!$old_null && $new_null) {
$ofs->write("-- domain changed from NOT NULL to NULL\n");
$ofs->write("ALTER DOMAIN {$domain} DROP NOT NULL;\n");
}
// diff constraints
$old_constraints = array();
foreach ($old_type->domainConstraint as $old_constraint) {
$old_constraints[(string) $old_constraint['name']] = pgsql8_type::normalize_domain_constraint($old_constraint);
}
foreach ($new_type->domainConstraint as $new_constraint) {
$name = (string) $new_constraint['name'];
$constraint = pgsql8_type::normalize_domain_constraint($new_constraint);
if (array_key_exists($name, $old_constraints)) {
if (strcmp($constraint, $old_constraints[$name]) !== 0) {
$ofs->write("-- domain constraint {$name} changed from {$old_constraints[$name]}\n");
$ofs->write("ALTER DOMAIN {$domain} DROP CONSTRAINT {$name};\n");
$ofs->write("ALTER DOMAIN {$domain} ADD CONSTRAINT {$name} CHECK({$constraint});\n");
}
unset($old_constraints[$name]);
} else {
$ofs->write("-- domain constraint {$name} added\n");
$ofs->write("ALTER DOMAIN {$domain} ADD CONSTRAINT {$name} CHECK({$constraint});\n");
}
}
foreach ($old_constraints as $name => $constraint) {
$ofs->write("-- domain constraint {$name} removed\n");
$ofs->write("ALTER DOMAIN {$domain} DROP CONSTRAINT {$name};\n");
}
}
public static function get_drop_sql($node_schema, $node_table, $node_index)
{
$ddl = "DROP INDEX " . pgsql8::get_quoted_schema_name($node_schema['name']) . "." . pgsql8::get_quoted_object_name($node_index['name']) . ";\n";
return $ddl;
}
/**
* 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;
}
/**
* Creates and returns SQL for dropping the language.
*
* @return string
*/
public static function get_drop_sql($node_language)
{
$ddl = "DROP " . (strcasecmp(dbsteward::string_cast($node_language['procedural']), 'true') == 0 ? "PROCEDURAL " : "") . " LANGUAGE " . pgsql8::get_quoted_object_name($node_language['name']) . " ;";
return $ddl;
}
/**
* 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;
}
public static function column_type($db_doc, $schema, $table, $column, &$foreign)
{
// if it is a foreign keyed column, solve for the foreignKey type
if (isset($column['foreignTable'])) {
dbx::foreign_key($db_doc, $schema, $table, $column, $foreign);
$column_type = $foreign['column']['type'];
// for foreign keys, translate serial types to their integer base
if (strcasecmp('serial', $column_type) == 0) {
$column_type = 'int';
} else {
if (strcasecmp('bigserial', $column_type) == 0) {
$column_type = 'bigint';
}
}
} else {
if (!isset($column['type']) || strlen($column['type']) == 0) {
throw new Exception("column missing type -- " . $schema['name'] . "." . $table['name'] . "." . $column['name']);
}
$column_type = $column['type'];
if (dbx::get_type($schema, $column_type) != NULL) {
// this is a user defined type or enum, enforce quoting if set
$column_type = pgsql8::get_quoted_object_name($column_type);
}
}
return $column_type;
}
public static function apply_table_options_diff($ofs1, $ofs3, $schema, $table, $alter_options, $create_options, $drop_options)
{
$schema_name = (string) $schema['name'];
$table_name = (string) $table['name'];
$fq_name = pgsql8::get_fully_qualified_table_name($schema_name, $table_name);
$actions = array();
$sql = "";
// create and alter have the same syntax:
$create_alter = array_merge($create_options, $alter_options);
foreach ($create_alter as $name => $value) {
switch (strtolower($name)) {
case 'with':
// ALTER TABLE ... SET (params) doesn't accept oids=true/false, unlike CREATE TABLE
// only WITH OIDS or WITHOUT OIDS
$params = pgsql8_table::parse_storage_params($value);
if (array_key_exists('oids', $params)) {
$oids = $params['oids'];
unset($params['oids']);
if (strcasecmp($oids, 'true') === 0) {
$actions[] = "SET WITH OIDS";
} else {
$actions[] = "SET WITHOUT OIDS";
}
} else {
// we might have gotten rid of the oids param
$actions[] = "SET WITHOUT OIDS";
}
// set the rest of the params normally
$params = pgsql8_table::compose_storage_params($params);
$actions[] = "SET {$params}";
break;
case 'tablespace':
$tbsp = (string) $value;
$actions[] = "SET TABLESPACE " . pgsql8::get_quoted_object_name($tbsp);
$sql .= <<<SQL
CREATE FUNCTION __dbsteward_migrate_move_index_tablespace(TEXT,TEXT,TEXT) RETURNS void AS \$\$
DECLARE idx RECORD;
BEGIN
-- need to move the tablespace of the indexes as well
FOR idx IN SELECT index_pgc.relname FROM pg_index
INNER JOIN pg_class index_pgc ON index_pgc.oid = pg_index.indexrelid
INNER JOIN pg_class table_pgc ON table_pgc.oid = pg_index.indrelid AND table_pgc.relname=\$2
INNER JOIN pg_namespace ON pg_namespace.oid = table_pgc.relnamespace AND pg_namespace.nspname=\$1 LOOP
EXECUTE 'ALTER INDEX ' || quote_ident(\$1) || '.' || quote_ident(idx.relname) || ' SET TABLESPACE ' || quote_ident(\$3) || ';';
END LOOP;
END \$\$ LANGUAGE plpgsql;
SELECT __dbsteward_migrate_move_index_tablespace('{$schema_name}','{$table_name}','{$tbsp}');
DROP FUNCTION __dbsteward_migrate_move_index_tablespace(TEXT,TEXT,TEXT);
SQL;
break;
}
}
foreach ($drop_options as $name => $value) {
switch (strtolower($name)) {
case 'with':
$params = pgsql8_table::parse_storage_params($value);
// handle oids separately, since pgsql doesn't recognise it as
// a storage parameter in an ALTER TABLE statement
if (array_key_exists('oids', $params)) {
$oids = $params['oids'];
unset($params['oids']);
$actions[] = "SET WITHOUT OIDS";
}
$names = '(' . implode(',', array_keys($params)) . ')';
$actions[] = "RESET {$names}";
break;
case 'tablespace':
// the only way to switch table and index to an unknown-beforehand value
// is with a function that's immediately executed
$sql .= <<<SQL
CREATE OR REPLACE FUNCTION __dbsteward_migrate_reset_tablespace(TEXT,TEXT) RETURNS void AS \$\$
DECLARE tbsp TEXT;
DECLARE idx RECORD;
BEGIN
SELECT setting FROM pg_settings WHERE name='default_tablespace' INTO tbsp;
IF tbsp = '' THEN
tbsp := 'pg_default';
END IF;
EXECUTE 'ALTER TABLE ' || quote_ident(\$1) || '.' || quote_ident(\$2) || ' SET TABLESPACE ' || quote_ident(tbsp) || ';';
-- need to move the tablespace of the indexes as well
FOR idx IN SELECT index_pgc.relname FROM pg_index
INNER JOIN pg_class index_pgc ON index_pgc.oid = pg_index.indexrelid
INNER JOIN pg_class table_pgc ON table_pgc.oid = pg_index.indrelid AND table_pgc.relname=\$2
INNER JOIN pg_namespace ON pg_namespace.oid = table_pgc.relnamespace AND pg_namespace.nspname=\$1 LOOP
EXECUTE 'ALTER INDEX ' || quote_ident(\$1) || '.' || quote_ident(idx.relname) || ' SET TABLESPACE ' || quote_ident(tbsp) || ';';
END LOOP;
END \$\$ LANGUAGE plpgsql;
SELECT __dbsteward_migrate_reset_tablespace('{$schema_name}','{$table_name}');
DROP FUNCTION __dbsteward_migrate_reset_tablespace(TEXT,TEXT);
SQL;
}
}
if (!empty($actions)) {
$sql .= "\nALTER TABLE {$fq_name}\n " . implode(",\n ", $actions) . ";";
}
if (!empty($sql)) {
$ofs1->write($sql . "\n");
}
}
public static function compile_sql_statement($action, $right, $object_type, $object_name, $role, $with = '')
{
// the PUBLIC role is actually a keyword, not an identifier, so don't quote it
if (strcasecmp($role, "PUBLIC") !== 0) {
$role = pgsql8::get_quoted_object_name($role);
}
$sql = $action . ' ' . $right . " ON " . $object_type . " " . $object_name . " TO " . $role;
if (strlen($with) > 0) {
$sql .= ' ' . $with;
}
$sql .= ";";
return $sql;
}
