/** * 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"); } }