/**
* Generate column defaults from column definitions, returns FALSE if
* no defaults were defined, otherwise return the
* ALTER TABLE ALTER COLUMN SET statements needed.
*
* Don't know if this would work with functions referenced as function(argument1 ... argumentN)
*
* @param type $node_schema
* @param type $node_table
* @param type $node_column
* @param type $add_defaults
* @param type $include_null_definition
* @param type $include_default_nextval
* @return boolean|string
*/
public static function set_column_defaults($node_schema, $node_table, $node_column, $add_defaults, $include_null_definition = true, $include_default_nextval = TRUE)
{
$fq_table_name = pgsql8::get_fully_qualified_table_name($node_schema['name'], $node_table['name']);
$base_sql = "ALTER TABLE " . $fq_table_name . " ALTER COLUMN " . pgsql8::get_quoted_column_name($node_column['name']) . " SET";
$sql = $base_sql;
$changes = FALSE;
if (strlen($node_column['default']) > 0) {
if (!$include_default_nextval && static::has_default_nextval($node_table, $node_column)) {
// if the default is a nextval expression, don't specify it in the regular full definition
// because if the sequence has not been defined yet,
// the nextval expression will be evaluated inline and fail
dbsteward::info("Skipping " . $node_column['name'] . " default expression \"" . $node_column['default'] . "\" - this default expression will be applied after all sequences have been created");
return $changes;
} else {
$sql .= " DEFAULT " . $node_column['default'];
$changes = TRUE;
}
} else {
if (!pgsql8_column::null_allowed($node_table, $node_column) && $add_defaults) {
$default_col_value = pgsql8_column::get_default_value($node_column['type']);
if ($default_col_value != null) {
$sql .= " DEFAULT " . $default_col_value;
$changes = TRUE;
}
}
}
if ($include_null_definition && !pgsql8_column::null_allowed($node_table, $node_column)) {
if ($changes) {
$sql .= ";\n";
$sql .= $base_sql . " NOT NULL";
} else {
$sql .= " NOT NULL";
$changes = TRUE;
}
}
// no changes? we don't have a default for this column... keep going pls
if (!$changes) {
return $changes;
}
$sql .= ";\n";
return $sql;
}
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");
}
}
