upgrade_tables.sql

gnugk软交换产品

CREATE OR REPLACE FUNCTION upgrade_db() RETURNS BOOLEAN AS
'
DECLARE
	s TEXT;
	majorver INT := 1;
	minorver INT := 0;
	buildno INT := 5;
	dbmajorver INT;
	dbminorver INT;
	dbbuildno INT;
	attrfound INT;
	constraintname TEXT;
	query TEXT;
BEGIN
	SELECT INTO s relname FROM pg_catalog.pg_class 
		WHERE relname = ''voipglobals'';
	IF s IS NULL THEN
		RAISE WARNING ''Upgrade is supported only for database version 1.0.1 or newer'';
		RETURN FALSE;
	END IF;
	
	SELECT INTO dbmajorver, dbminorver, dbbuildno majorversion, minorversion,
		buildnumber FROM voipglobals;
	IF NOT FOUND THEN
		RAISE EXCEPTION ''Unable to extract version information for the database - no rows in voipglobals table'';
		RETURN FALSE;
	END IF;
	
	IF dbmajorver > majorver OR (dbmajorver = majorver AND dbminorver > minorver)
		OR (dbmajorver = majorver AND dbminorver = minorver AND dbbuildno > buildno) THEN
		RAISE WARNING ''Trying to upgrade from a newer database version (%.%.% > %.%.%)'',
			dbmajorver, dbminorver, dbbuildno, majorver, minorver, buildno;
		RETURN FALSE;
	END IF;

	RAISE INFO ''Upgrading database version from %.%.% to %.%.%...'',
		dbmajorver, dbminorver, dbbuildno, majorver, minorver, buildno;
		
	UPDATE voipglobals SET majorversion = majorver, minorversion = minorver,
		buildnumber = buildno;

	-- check for voiptariffdst.exactmatch column presence
	SELECT INTO attrfound COUNT(*) FROM pg_class C JOIN pg_attribute A ON a.attrelid = C.oid
		WHERE c.relname = ''voiptariffdst'' AND A.attname = ''exactmatch'';
	IF attrfound = 0 THEN
		ALTER TABLE voiptariffdst ADD COLUMN exactmatch BOOLEAN;
		UPDATE voiptariffdst SET exactmatch = FALSE;
		ALTER TABLE voiptariffdst ALTER COLUMN exactmatch SET DEFAULT FALSE;
		ALTER TABLE voiptariffdst ALTER COLUMN exactmatch SET NOT NULL;
	END IF;

	-- adjucts indexes for voiptariffdst
	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voiptariffdst_activeasciipfx'';
	IF attrfound <> 0 THEN
		DROP INDEX voiptariffdst_activeasciipfx;
	END IF;


	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voiptariffdst_activeh323id_idx'';
	IF attrfound = 0 THEN
		CREATE UNIQUE INDEX voiptariffdst_activeh323id_idx 
			ON voiptariffdst(prefix) WHERE active AND exactmatch;
	END IF;

	-- check for voiptariff.terminating column presence
	SELECT INTO attrfound COUNT(*) FROM pg_class C JOIN pg_attribute A ON a.attrelid = C.oid
		WHERE c.relname = ''voiptariff'' AND A.attname = ''terminating'';
	IF attrfound = 0 THEN
		ALTER TABLE voiptariff ADD COLUMN terminating BOOLEAN;
		UPDATE voiptariff SET terminating = FALSE;
		ALTER TABLE voiptariff ALTER COLUMN terminating SET DEFAULT FALSE;
		ALTER TABLE voiptariff ALTER COLUMN terminating SET NOT NULL;
	END IF;
	
	SELECT INTO attrfound COUNT(*) FROM pg_class WHERE relname = ''voipcalltermtariff'';
	IF attrfound = 0 THEN
		CREATE SEQUENCE voipcalltermtariff_id_seq CYCLE;
		CREATE TABLE voipcalltermtariff (
			id INT DEFAULT nextval(''voipcalltermtariff_id_seq''),
			callid INT NOT NULL,
			accountid INT NOT NULL,
			h323id TEXT NOT NULL,
			terminatingip INET,
			cost NUMERIC(12,4),
			price NUMERIC(9,4) NOT NULL,
			currencysym CHAR(3) NOT NULL DEFAULT ''USD'',
			initialincrement INT NOT NULL DEFAULT 60,
			regularincrement INT NOT NULL DEFAULT 60,
			graceperiod INT NOT NULL DEFAULT 0,
			tariffdesc TEXT NOT NULL DEFAULT '''',
		
			PRIMARY KEY (id),
			FOREIGN KEY (callid) REFERENCES voipcall(id) ON UPDATE CASCADE ON DELETE CASCADE,
			FOREIGN KEY (accountid) REFERENCES voipaccount(id) ON UPDATE CASCADE
		);
	END IF;
	
	-- check for voipuser.termgateway column presence
	SELECT INTO attrfound COUNT(*) FROM pg_class C JOIN pg_attribute A ON a.attrelid = C.oid
		WHERE c.relname = ''voipuser'' AND A.attname = ''terminating'';
	IF attrfound = 0 THEN
		ALTER TABLE voipuser ADD COLUMN terminating BOOLEAN;
		UPDATE voipuser SET terminating = FALSE;
		ALTER TABLE voipuser ALTER COLUMN terminating SET DEFAULT FALSE;
		ALTER TABLE voipuser ALTER COLUMN terminating SET NOT NULL;
	END IF;

	-- create any missing voipuser indexes
	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voipuser_terminatingh323id_idx'';
	IF attrfound = 0 THEN
		CREATE UNIQUE INDEX voipuser_terminatingh323id_idx ON voipuser(h323id) 
			WHERE terminating;
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voipuser_terminatingip_idx'';
	IF attrfound = 0 THEN
		CREATE UNIQUE INDEX voipuser_terminatingip_idx ON voipuser(framedip) 
			WHERE terminating;
	END IF;

	-- recreate voiptariff constraints
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariff_dstid_key'';
	IF attrfound <> 0 THEN
		ALTER TABLE voiptariff DROP CONSTRAINT voiptariff_dstid_key;
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariff_unique'';
	IF attrfound = 0 THEN
		ALTER TABLE voiptariff ADD CONSTRAINT voiptariff_unique UNIQUE (dstid, grpid, currencysym, terminating);
	END IF;

	constraintname := NULL;
	SELECT INTO constraintname CC.conname FROM (SELECT C.conname, C.confrelid FROM pg_constraint C 
		JOIN pg_type T ON C.conrelid = T.typrelid WHERE T.typname = ''voiptariff''
		AND C.contype = ''f'') AS CC JOIN pg_type TT ON CC.confrelid = TT.typrelid
		AND TT.typname = ''voiptariffdst'';
	IF constraintname IS NOT NULL THEN
		EXECUTE ''ALTER TABLE voiptariff DROP CONSTRAINT ''
			|| quote_ident(constraintname);
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariff_destination_exists'';
	IF attrfound = 0 THEN
		ALTER TABLE voiptariff ADD CONSTRAINT voiptariff_destination_exists 
			FOREIGN KEY (dstid) REFERENCES voiptariffdst(id) ON UPDATE CASCADE;
	END IF;

	constraintname := NULL;
	SELECT INTO constraintname CC.conname FROM (SELECT C.conname, C.confrelid FROM pg_constraint C 
		JOIN pg_type T ON C.conrelid = T.typrelid WHERE T.typname = ''voiptariff''
		AND C.contype = ''f'') AS CC JOIN pg_type TT ON CC.confrelid = TT.typrelid
		AND TT.typname = ''voiptariffgrp'';
	IF constraintname IS NOT NULL THEN
		EXECUTE ''ALTER TABLE voiptariff DROP CONSTRAINT ''
			|| quote_ident(constraintname);
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariff_group_exists'';
	IF attrfound = 0 THEN
		ALTER TABLE voiptariff ADD CONSTRAINT voiptariff_group_exists 
			FOREIGN KEY (grpid) REFERENCES voiptariffgrp(id) ON UPDATE CASCADE;
	END IF;
		
	-- recreate voiptariffdst constraints
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariffdst_prefix_key'';
	IF attrfound <> 0 THEN
		ALTER TABLE voiptariffdst DROP CONSTRAINT voiptariffdst_prefix_key;
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariffdst_unique'';
	IF attrfound = 0 THEN
		ALTER TABLE voiptariffdst ADD CONSTRAINT voiptariffdst_unique UNIQUE (prefix);
	END IF;

	-- recreate voiptariffsel constraints
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariffsel_grpid_key'';
	IF attrfound <> 0 THEN
		ALTER TABLE voiptariffsel DROP CONSTRAINT voiptariffsel_grpid_key;
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariffsel_unique'';
	IF attrfound = 0 THEN
		ALTER TABLE voiptariffsel ADD CONSTRAINT voiptariffsel_unique UNIQUE (grpid, accountid);
	END IF;
	
	-- recreate voipuser constraints
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voipuser_h323id_key'';
	IF attrfound <> 0 THEN
		ALTER TABLE voipuser DROP CONSTRAINT voipuser_h323id_key;
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voipuser_unique'';
	IF attrfound = 0 THEN
		ALTER TABLE voipuser ADD CONSTRAINT voipuser_unique UNIQUE (h323id);
	END IF;

	constraintname := NULL;
	SELECT INTO constraintname CC.conname FROM (SELECT C.conname, C.confrelid FROM pg_constraint C 
		JOIN pg_type T ON C.conrelid = T.typrelid WHERE T.typname = ''voipuser''
		AND C.contype = ''f'') AS CC JOIN pg_type TT ON CC.confrelid = TT.typrelid
		AND TT.typname = ''voipaccount'';
	IF constraintname IS NOT NULL THEN
		EXECUTE ''ALTER TABLE voipuser DROP CONSTRAINT ''
			|| quote_ident(constraintname);
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voipuser_account_exists'';
	IF attrfound = 0 THEN
		ALTER TABLE voipuser ADD CONSTRAINT voipuser_account_exists 
			FOREIGN KEY (accountid) REFERENCES voipuser(id) ON UPDATE CASCADE;
	END IF;

	-- recreate voipcall constraints
	constraintname := NULL;
	SELECT INTO constraintname CC.conname FROM (SELECT C.conname, C.confrelid FROM pg_constraint C 
		JOIN pg_type T ON C.conrelid = T.typrelid WHERE T.typname = ''voipcall''
		AND C.contype = ''f'') AS CC JOIN pg_type TT ON CC.confrelid = TT.typrelid
		AND TT.typname = ''voipaccount'';
	IF constraintname IS NOT NULL THEN
		EXECUTE ''ALTER TABLE voipcall DROP CONSTRAINT ''
			|| quote_ident(constraintname);
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voipcall_account_exists'';
	IF attrfound = 0 THEN
		ALTER TABLE voipcall ADD CONSTRAINT voipcall_account_exists 
			FOREIGN KEY (accountid) REFERENCES voipaccount(id) ON UPDATE CASCADE;
	END IF;

	-- adjust indexes for voipcall
	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voipcall_h323id_idx'';
	IF attrfound <> 0 THEN
		DROP INDEX voipcall_h323id_idx;
	END IF;
	
	-- recreate voipcalltermtariff constraints
	constraintname := NULL;
	SELECT INTO constraintname CC.conname FROM (SELECT C.conname, C.confrelid FROM pg_constraint C 
		JOIN pg_type T ON C.conrelid = T.typrelid WHERE T.typname = ''voipcalltermtariff''
		AND C.contype = ''f'') AS CC JOIN pg_type TT ON CC.confrelid = TT.typrelid
		AND TT.typname = ''voipaccount'';
	IF constraintname IS NOT NULL THEN
		EXECUTE ''ALTER TABLE voipcalltermtariff DROP CONSTRAINT ''
			|| quote_ident(constraintname);
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voipcalltermtariff_account_exists'';
	IF attrfound = 0 THEN
		ALTER TABLE voipcalltermtariff ADD CONSTRAINT voipcalltermtariff_account_exists 
			FOREIGN KEY (accountid) REFERENCES voipaccount(id) ON UPDATE CASCADE;
	END IF;

	constraintname := NULL;
	SELECT INTO constraintname CC.conname FROM (SELECT C.conname, C.confrelid FROM pg_constraint C 
		JOIN pg_type T ON C.conrelid = T.typrelid WHERE T.typname = ''voipcalltermtariff''
		AND C.contype = ''f'') AS CC JOIN pg_type TT ON CC.confrelid = TT.typrelid
		AND TT.typname = ''voipcall'';
	IF constraintname IS NOT NULL THEN
		EXECUTE ''ALTER TABLE voipcalltermtariff DROP CONSTRAINT ''
			|| quote_ident(constraintname);
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voipcalltermtariff_call_exists'';
	IF attrfound = 0 THEN
		ALTER TABLE voipcalltermtariff ADD CONSTRAINT voipcalltermtariff_call_exists 
			FOREIGN KEY (callid) REFERENCES voipcall(id) ON UPDATE CASCADE ON DELETE CASCADE;
	END IF;

	-- recreate voiptariffsel constraints
	constraintname := NULL;
	SELECT INTO constraintname CC.conname FROM (SELECT C.conname, C.confrelid FROM pg_constraint C 
		JOIN pg_type T ON C.conrelid = T.typrelid WHERE T.typname = ''voiptariffsel''
		AND C.contype = ''f'') AS CC JOIN pg_type TT ON CC.confrelid = TT.typrelid
		AND TT.typname = ''voiptariffgrp'';
	IF constraintname IS NOT NULL THEN
		EXECUTE ''ALTER TABLE voiptariffsel DROP CONSTRAINT ''
			|| quote_ident(constraintname);
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariffsel_group_exists'';
	IF attrfound = 0 THEN
		ALTER TABLE voiptariffsel ADD CONSTRAINT voiptariffsel_group_exists 
			FOREIGN KEY (grpid) REFERENCES voiptariffgrp(id) ON UPDATE CASCADE;
	END IF;

	constraintname := NULL;
	SELECT INTO constraintname CC.conname FROM (SELECT C.conname, C.confrelid FROM pg_constraint C 
		JOIN pg_type T ON C.conrelid = T.typrelid WHERE T.typname = ''voiptariffsel''
		AND C.contype = ''f'') AS CC JOIN pg_type TT ON CC.confrelid = TT.typrelid
		AND TT.typname = ''voipaccount'';
	IF constraintname IS NOT NULL THEN
		EXECUTE ''ALTER TABLE voiptariffsel DROP CONSTRAINT ''
			|| quote_ident(constraintname);
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_constraint WHERE conname = ''voiptariffsel_account_exists'';
	IF attrfound = 0 THEN
		ALTER TABLE voiptariffsel ADD CONSTRAINT voiptariffsel_account_exists 
			FOREIGN KEY (accountid) REFERENCES voipaccount(id) ON UPDATE CASCADE;
	END IF;

	-- check for voipcall.prefix column presence
	SELECT INTO attrfound COUNT(*) FROM pg_class C JOIN pg_attribute A ON a.attrelid = C.oid
		WHERE c.relname = ''voipcall'' AND A.attname = ''prefix'';
	IF attrfound = 0 THEN
		ALTER TABLE voipcall ADD COLUMN prefix TEXT;
		UPDATE voipcall SET prefix = '''';
		ALTER TABLE voipcall ALTER COLUMN prefix SET DEFAULT '''';
		ALTER TABLE voipcall ALTER COLUMN prefix SET NOT NULL;
	END IF;

	-- create/deelte new/old voiptariffdst indexes
	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voiptariffdst_activepfx_idx'';
	IF attrfound <> 0 THEN
		DROP INDEX voiptariffdst_activepfx_idx;
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voiptariffdst_pfx_idx'';
	IF attrfound = 0 THEN
		CREATE UNIQUE INDEX voiptariffdst_pfx_idx ON voiptariffdst(prefix);
	END IF;

	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voiptariffdst_activeh323id_idx'';
	IF attrfound <> 0 THEN
		DROP INDEX voiptariffdst_activeh323id_idx;
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voiptariffdst_h323id_idx'';
	IF attrfound = 0 THEN
		CREATE UNIQUE INDEX voiptariffdst_h323id_idx ON voiptariffdst(prefix)
			WHERE exactmatch;
	END IF;

	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voiptariffdst_activepc_idx'';
	IF attrfound <> 0 THEN
		DROP INDEX voiptariffdst_activepc_idx;
	END IF;
	SELECT INTO attrfound COUNT(*) FROM pg_indexes WHERE indexname = ''voiptariffdst_pc_idx'';
	IF attrfound = 0 THEN
		CREATE UNIQUE INDEX voiptariffdst_pc_idx ON voiptariffdst(prefix)
			WHERE prefix = ''PC'';
	END IF;

	RAISE INFO ''Upgrade complete'';
	RETURN TRUE;
END;
' LANGUAGE 'plpgsql' SECURITY INVOKER;

SELECT CASE upgrade_db() WHEN TRUE THEN 'Success' ELSE 'Failure' END AS Upgrade_result;