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