1.133. upgradeschema( text )

Function Properties

Language: PLPGSQL

Return Type: text

Called during "update functions" by slonik to perform schema changes

declare
        p_old   alias for $1;
begin
	-- upgrade sl_table
	if p_old IN ('1.0.2', '1.0.5', '1.0.6') then
		-- Add new column(s) sl_table.tab_relname, sl_table.tab_nspname
		execute 'alter table sl_table add column tab_relname name';
		execute 'alter table sl_table add column tab_nspname name';

		-- populate the colums with data
		update sl_table set
			tab_relname = PGC.relname, tab_nspname = PGN.nspname
			from pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN
			where sl_table.tab_reloid = PGC.oid
			and PGC.relnamespace = PGN.oid;

		-- constrain the colums
		execute 'alter table sl_table alter column tab_relname set NOT NULL';
		execute 'alter table sl_table alter column tab_nspname set NOT NULL';

	end if;

	-- upgrade sl_sequence
	if p_old IN ('1.0.2', '1.0.5', '1.0.6') then
		-- Add new column(s) sl_sequence.seq_relname, sl_sequence.seq_nspname
		execute 'alter table sl_sequence add column seq_relname name';
		execute 'alter table sl_sequence add column seq_nspname name';

		-- populate the columns with data
		update sl_sequence set
			seq_relname = PGC.relname, seq_nspname = PGN.nspname
			from pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN
			where sl_sequence.seq_reloid = PGC.oid
			and PGC.relnamespace = PGN.oid;

		-- constrain the data
		execute 'alter table sl_sequence alter column seq_relname set NOT NULL';
		execute 'alter table sl_sequence alter column seq_nspname set NOT NULL';
	end if;

	-- ----
	-- Changes from 1.0.x to 1.1.0
	-- ----
	if p_old IN ('1.0.2', '1.0.5', '1.0.6') then
		-- Add new column sl_node.no_spool for virtual spool nodes
		execute 'alter table sl_node add column no_spool boolean';
		update sl_node set no_spool = false;
	end if;

	-- ----
	-- Changes for 1.1.3
	-- ----
	if p_old IN ('1.0.2', '1.0.5', '1.0.6', '1.1.0', '1.1.1', '1.1.2') then
		-- Add new table sl_nodelock
		execute 'create table sl_nodelock (
						nl_nodeid		int4,
						nl_conncnt		serial,
						nl_backendpid	int4,

						CONSTRAINT "sl_nodelock-pkey"
						PRIMARY KEY (nl_nodeid, nl_conncnt)
					)';
		-- Drop obsolete functions
		execute 'drop function terminateNodeConnections(name)';
		execute 'drop function cleanupListener()';
		execute 'drop function truncateTable(text)';
	end if;

	-- ----
	-- Changes for 1.2
	-- ----
	if p_old IN ('1.0.2', '1.0.5', '1.0.6', '1.1.0', '1.1.1', '1.1.2', '1.1.3') then
		-- Add new table sl_registry
		execute 'create table sl_registry (
						reg_key			text primary key,
						reg_int4		int4,
						reg_text		text,
						reg_timestamp	timestamp
					) without oids';
                execute 'alter table sl_config_lock set without oids;';
                execute 'alter table sl_confirm set without oids;';
                execute 'alter table sl_event set without oids;';
                execute 'alter table sl_listen set without oids;';
                execute 'alter table sl_log_1 set without oids;';
                execute 'alter table sl_log_2 set without oids;';
                execute 'alter table sl_node set without oids;';
                execute 'alter table sl_nodelock set without oids;';
                execute 'alter table sl_path set without oids;';
                execute 'alter table sl_seqlog set without oids;';
                execute 'alter table sl_sequence set without oids;';
                execute 'alter table sl_set set without oids;';
                execute 'alter table sl_setsync set without oids;';
                execute 'alter table sl_subscribe set without oids;';
                execute 'alter table sl_table set without oids;';
                execute 'alter table sl_trigger set without oids;';
	end if;

	return p_old;
end;