Version 22

vacuumlo -v -h localhost -p 5432 -U postgres pdm_parking

BEGIN;

-- Test if database is ready for migration. If not an exception will be raised, which stops script execution.
DO $$
DECLARE
	last_change_set varchar;
BEGIN
	SELECT id INTO last_change_set FROM public.database_change_log ORDER BY id DESC LIMIT 1;
	IF last_change_set = '220204T1222' THEN
		RAISE NOTICE  'Database is ready to be migrated to version 22.';
	ELSE
		RAISE EXCEPTION  'DATABASE IS NOT READY. PLEASE INSTALL PDM.CONTROL 21.8 FIRST.';
	END IF;
END;
$$;

-- Create user pdm_control
CREATE USER pdm_control PASSWORD 'bYue[IOZ3"oc%r5T*BCT';
-- Let user pdm_control own the database
ALTER DATABASE "pdm_parking" OWNER TO pdm_control;
-- Move ownership of public schema.
ALTER SCHEMA public OWNER TO pdm_control;

-- Create new schema for PDM.control.
CREATE SCHEMA control AUTHORIZATION pdm_control;

-- Drop liquibase change logs
DROP TABLE database_change_log;
DROP TABLE database_change_log_lock;


-- Alter ownership of all objects to uses pdm_control;
-- It needs to be done in multiple  steps because custom types are not part of standard SQL.

DO $$
DECLARE
	-- Configuration
	v_schema varchar := 'public';
	v_new_schema varchar:= 'control';
	v_new_owner varchar := 'pdm_control';
	-- Internal variables
	r record;
	cmd_owner varchar;
	cmd_schema varchar;
BEGIN
	-- Move only tables (except liquibase and users, they will stay in public schema). Owner will be changed in new schema.
	RAISE INFO 'Moving tables ...';
	FOR r IN
		-- Table owner
		SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.tables WHERE table_schema = v_schema
		union all
		-- Table schema (leave liquibase tables in public schema)
		SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" SET SCHEMA ' || v_new_schema || ';' AS a FROM information_schema.tables WHERE table_schema = v_schema
	LOOP
		RAISE NOTICE '%', r.a;
		EXECUTE r.a;
	END LOOP;

	-- Rest of operation must be done in new loop because tables now belong to new schema.
	RAISE INFO 'Moving functions, views, sequences ...';
	FOR r IN
		-- Sequence owner
		SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.sequences WHERE sequence_schema = v_schema
		union all
		-- Sequence schema
		SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" SET SCHEMA ' || v_new_schema || ';' AS a FROM information_schema.sequences WHERE sequence_schema = v_schema
		union all
		-- View owner
		SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.views WHERE table_schema = v_schema
		union all
		-- View schema
		SELECT 'ALTER VIEW "' || table_schema || '"."' || table_name || '" SET SCHEMA ' || v_new_schema || ';' AS a FROM information_schema.views WHERE table_schema = v_schema
		union all
		-- Function owner
		SELECT 'ALTER FUNCTION "'||nsp.nspname||'"."'||p.proname||'"('||pg_get_function_identity_arguments(p.oid)||') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE nsp.nspname = v_schema
		union all
		-- Function schema
		SELECT 'ALTER FUNCTION "'||nsp.nspname||'"."'||p.proname||'"('||pg_get_function_identity_arguments(p.oid)||') SET SCHEMA ' || v_new_schema || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE nsp.nspname = v_schema
	LOOP
		RAISE NOTICE '%', r.a;
		EXECUTE r.a;
	END LOOP;

	-- Types needs to be moved by extra command. They are not part of standard SQL therefore to command is more complex. Source: https://stackoverflow.com/a/3703727
	RAISE INFO 'Moving types ...';
	FOR r IN
		SELECT n.nspname as schema, t.typname as type
		FROM pg_type t
		LEFT JOIN  pg_catalog.pg_namespace n ON n.oid = t.typnamespace
		WHERE (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid))
		AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
		AND n.nspname = v_schema
	LOOP
		cmd_owner:= format('ALTER TYPE "%s"."%s" OWNER TO %s', r.schema, r.type, v_new_owner);
		cmd_schema:= format('ALTER TYPE "%s"."%s" SET SCHEMA %s', r.schema, r.type, v_new_schema);
		raise notice '%', cmd_owner;
		EXECUTE cmd_owner;
		raise notice '%', cmd_schema;
		EXECUTE cmd_schema;
	END LOOP;
END$$;

-- Re-declare function 'add_default_message_config' in new schema 'control'
DROP FUNCTION IF EXISTS control.add_default_message_config(integer, integer, control.status_message_alert, control.status_message_impact, boolean, boolean);
CREATE OR REPLACE FUNCTION control.add_default_message_config(
	in_msg_nr integer,
	in_solve_nr integer,
	in_alert_type control.status_message_alert,
	in_msg_impact control.status_message_impact,
	in_manual boolean DEFAULT false,
	in_important boolean DEFAULT false)
		RETURNS void
		LANGUAGE 'plpgsql'
		COST 100
		VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
BEGIN
	-- Sanity checks
	IF in_msg_impact = 'NEGATIVE' AND in_solve_nr IS NULL THEN RAISE EXCEPTION 'Negative message has to define solve number.'; END IF;
	IF in_msg_impact IN ('POSITIVE', 'TRANSIENT') AND in_solve_nr IS NOT NULL THEN RAISE EXCEPTION 'Positive and transient message must not define solve number.'; END IF;
	IF in_manual IS NULL THEN RAISE EXCEPTION 'Manual mode must not be null'; END IF;
	IF in_important IS NULL THEN RAISE EXCEPTION 'Important mode must not be null'; END IF;
	IF in_msg_impact = 'POSITIVE' AND in_alert_type != 'NONE' IS NULL THEN RAISE EXCEPTION 'Alert type for positive messages has to be NONE'; END IF;

	-- Test solve message properties
	IF in_solve_nr IS NOT NULL
	THEN
		-- Test if referenced message exists
		IF (SELECT COUNT(*) FROM control.message_config_default WHERE mcd_number = in_solve_nr) = 0 THEN RAISE EXCEPTION 'Referenced positive message % does not exits.', in_solve_nr; END IF;
		-- Test if referenced message is really positive
		IF (SELECT mcd_impact FROM control.message_config_default WHERE mcd_number = in_solve_nr) != 'POSITIVE' THEN RAISE EXCEPTION 'Referenced positive message % is not positive.', in_solve_nr; END IF;
	END IF;

	INSERT INTO control.message_config_default(mcd_number, mcd_solve, mcd_alert, mcd_impact, mcd_manual, mcd_important)
	VALUES (in_msg_nr, in_solve_nr, in_alert_type, in_msg_impact, in_manual, in_important)
	ON CONFLICT (mcd_number)
	DO UPDATE SET mcd_solve = in_solve_nr, mcd_alert = in_alert_type, mcd_impact = in_msg_impact, mcd_manual = in_manual, mcd_important = in_important;
END;
$BODY$;

ALTER FUNCTION control.add_default_message_config(integer, integer, control.status_message_alert, control.status_message_impact, boolean, boolean) OWNER TO pdm_control;

-- Re-declare comment on function function extendible_permits in new schema 'control'
COMMENT ON FUNCTION control.extendible_permits(integer, character varying, integer) IS 'List all extendible permits having provided lpn OR psn. Use null for not provided value. Both values can be provided simultaneously.';

-- Mark change log for liquibase
CREATE TABLE IF NOT EXISTS public.database_change_log
(
	id character varying NOT NULL,
	author character varying NOT NULL,
	filename character varying NOT NULL,
	dateexecuted timestamp without time zone NOT NULL,
	orderexecuted integer NOT NULL,
	exectype character varying NOT NULL,
	md5sum character varying,
	description character varying,
	comments character varying,
	tag character varying,
	liquibase character varying,
	contexts character varying,
	labels character varying,
	deployment_id character varying
);
ALTER TABLE public.database_change_log OWNER to pdm_control;
ALTER TABLE public.database_change_log ADD CONSTRAINT pk_db_changelog PRIMARY KEY (id, author);

INSERT INTO public.database_change_log (id, author, filename, dateexecuted, orderexecuted, exectype, md5sum, description, comments, tag, liquibase, contexts, labels, deployment_id)
VALUES ('220216T0000', 'ales.holec', 'db/022/220216_db_init.sql', '2022-02-16 15:07:51.844589', 1, 'EXECUTED', '8:aa1cff7923b36796cec821452f59b568', 'sql', '', NULL, '4.5.0', NULL, NULL, '5020469796');

DO $$
DECLARE
	oid_to_update oid;
	cmd_owner varchar;
BEGIN
	RAISE INFO 'Updating large objects ...';
	FOR oid_to_update IN
		SELECT DISTINCT sws_data FROM control.software_item
	LOOP
		cmd_owner:= format('ALTER LARGE OBJECT %s OWNER TO pdm_control', oid_to_update);
		EXECUTE cmd_owner;
	END LOOP;
END;
$$;

ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
CONTEXT:  SQL statement "ALTER LARGE OBJECT 69059 OWNER TO pdm_control"
PL/pgSQL function inline_code_block line 13 on EXECUTE
SQL state: 53200

max_locks_per_transaction = 512

COMMIT;

ROLLBACK;

ALTER DATABASE pdm_parking RENAME TO my_database;

ALTER ROLE pdm_control PASSWORD 'xxxxxx';
ALTER ROLE pdm_control RENAME TO my_user_name;

-- Clearings
CREATE INDEX tmp_clearing_timestamp ON control.clearing USING btree (clr_date_pdm ASC NULLS LAST);
CLUSTER control.clearing USING tmp_clearing_timestamp;
DROP INDEX control.tmp_clearing_timestamp;
-- Status
CLUSTER control.logbook USING idx_logbook__date_pdm;
-- Payments
CLUSTER control.translog USING fki_translog__pdm;

spring:
  datasource:
	url: jdbc:postgresql://localhost:5432/pdm_parking
	username: pdm_control
	password: 'bYue[IOZ3"oc%r5T*BCT'