-- emaj_upgrade_after_postgres_upgrade.sql
-- This psql script belongs to the E-Maj extension
--
-- The script has to be run after a major postgres version upgrade. It installs the new or modified E-Maj components needed by
-- the newly upgraded postgres version.
-- Currently, it only manages the components that are needed to setup the event trigger protection. These components are also created
-- by the standart installation procedure. But their effective creation depends on the postgres version used at installation time
-- because the event trigger feature doesn't exist in the oldest postgres versions supported by E-Maj. So the script is useful when
-- E-Maj was initially installed with a postgres version prior 9.5 and a postgres version upgrade leads to a more recent version.
--
-- It must be executed once connected as SUPERUSER.
--
--\set ECHO all
\set ON_ERROR_STOP on

------------------------------------
--                                --
-- checks                         --
--                                --
------------------------------------
-- Check that the upgrade conditions are met.
DO
$do$
  DECLARE
    v_emajVersion            TEXT;
    v_groupList              TEXT;
  BEGIN
-- check the current role is a superuser
    PERFORM 0 FROM pg_roles WHERE rolname = current_user AND rolsuper;
    IF NOT FOUND THEN
      RAISE EXCEPTION 'E-Maj upgrade: the current user (%) is not a superuser.', current_user;
    END IF;
-- the emaj version registered in emaj_param must be '2.1.0'
    SELECT param_value_text INTO v_emajVersion FROM emaj.emaj_param WHERE param_key = 'emaj_version';
    IF v_emajVersion <> '2.1.0' THEN
      RAISE EXCEPTION 'E-Maj upgrade: the current E-Maj version (%) is not 2.1.0.',v_emajVersion;
    END IF;
-- the installed postgres version must be at least 9.1
    IF current_setting('server_version_num')::int < 90100 THEN
      RAISE EXCEPTION 'E-Maj upgrade: the current PostgreSQL version (%) is not compatible with E-Maj 2.1.0. The PostgreSQL version should be at least 9.1.', current_setting('server_version');
    END IF;
  END;
$do$;

-- OK, the upgrade operation can start...
BEGIN;
-- lock emaj_group table to avoid any concurrent E-Maj activity
LOCK TABLE emaj.emaj_group IN EXCLUSIVE MODE;

--
-- Create objects that depends on postgres version
--
DO
$do$
  BEGIN

-- beginning of 9.3+ specific code
    IF emaj._pg_version_num() >= 90300 THEN
-- the sql_drop event trigger is only possible with postgres 9.3+
-- if the emaj_protection_trg event trigger does not exist, create it
      PERFORM 1 FROM pg_catalog.pg_event_trigger WHERE evtname = 'emaj_protection_trg';
      IF FOUND THEN
        RAISE NOTICE 'E-Maj upgrade script: the "emaj_protection_trg" event trigger already exists.';
      ELSE
        RAISE NOTICE 'E-Maj upgrade script: create the "emaj_protection_trg" event trigger.';
--
CREATE OR REPLACE FUNCTION public._emaj_protection_event_trigger_fnct()
 RETURNS EVENT_TRIGGER LANGUAGE plpgsql AS
$_emaj_protection_event_trigger_fnct$
-- This function is called by the emaj_protection_trg event trigger
-- The function only blocks any attempt to drop the emaj schema or the emaj extension
-- It is located into the public schema to be able to detect the emaj schema removal attempt
-- It is also unlinked from the emaj extension to be able to detest the emaj extension removal attempt
-- Another pair of function and event trigger handles all other drop attempts
  DECLARE
    r_dropped                RECORD;
  BEGIN
-- scan all dropped objects
    FOR r_dropped IN
      SELECT object_type, object_name FROM pg_event_trigger_dropped_objects()
    LOOP
      IF r_dropped.object_type = 'schema' AND r_dropped.object_name = 'emaj' THEN
-- detecting an attempt to drop the emaj object
        RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the schema "emaj". Please use the emaj_uninstall.sql script if you really want to remove all E-Maj components.';
      END IF;
      IF r_dropped.object_type = 'extension' AND r_dropped.object_name = 'emaj' THEN
-- detecting an attempt to drop the emaj extension
        RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the emaj extension. Please use the emaj_uninstall.sql script if you really want to remove all E-Maj components.';
      END IF;
    END LOOP;
  END;
$_emaj_protection_event_trigger_fnct$;
COMMENT ON FUNCTION public._emaj_protection_event_trigger_fnct() IS
$$E-Maj extension: support of the emaj_protection_trg event trigger.$$;

-- all commands involving an event trigger is submitted by an EXECUTE instruction
-- this is needed for compatibility with pre 9.3 postgres version that do not know event triggers
EXECUTE '
CREATE EVENT TRIGGER emaj_protection_trg
  ON sql_drop
  WHEN TAG IN (''DROP EXTENSION'',''DROP SCHEMA'')
  EXECUTE PROCEDURE public._emaj_protection_event_trigger_fnct();
';
EXECUTE '
COMMENT ON EVENT TRIGGER emaj_protection_trg IS
$$Blocks the removal of the emaj extension or schema.$$;
';

      END IF;
-- if the emaj_sql_drop_trg event trigger does not exist, create it
      PERFORM 1 FROM pg_catalog.pg_event_trigger WHERE evtname = 'emaj_sql_drop_trg';
      IF FOUND THEN
        RAISE NOTICE 'E-Maj upgrade script: the "emaj_sql_drop_trg" event trigger already exists.';
      ELSE
        RAISE NOTICE 'E-Maj upgrade script: create the "emaj_sql_drop_trg" event trigger.';
--

CREATE OR REPLACE FUNCTION emaj._event_trigger_sql_drop_fnct()
 RETURNS EVENT_TRIGGER LANGUAGE plpgsql AS
$_event_trigger_sql_drop_fnct$
-- This function is called by the emaj_sql_drop_trg event trigger
-- The function blocks any ddl operation that leads to a drop of
--   - an application table or a sequence registered into an active (not stopped) E-Maj group, or a schema containing such tables/sequence
--   - an E-Maj schema, a log table, a log sequence, a log function or a log trigger
-- The drop of emaj schema or extension is managed by another event trigger
  DECLARE
    v_groupName              TEXT;
    r_dropped                RECORD;
  BEGIN
-- scan all dropped objects
    FOR r_dropped IN
      SELECT * FROM pg_event_trigger_dropped_objects()
-- TODO: when postgres 9.4 will not be supported any more, replace the statement by:
--      SELECT object_type, schema_name, object_name, object_identity, original FROM pg_event_trigger_dropped_objects()
-- (the 'original' column is not known in pg9.4- versions)
    LOOP
      CASE
        WHEN r_dropped.object_type = 'schema' THEN
-- the object is a schema
--   look at the emaj_relation table to verify that the schema being dropped does not belong to any active (not stopped) group
          SELECT string_agg(DISTINCT rel_group, ', ') INTO v_groupName FROM emaj.emaj_relation, emaj.emaj_group
            WHERE rel_schema = r_dropped.object_name
              AND group_name = rel_group AND group_is_logging;
          IF v_groupName IS NOT NULL THEN
            RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the application schema "%". But it belongs to the active tables groups "%".', r_dropped.object_name, v_groupName;
          END IF;
--   look at the emaj_relation table to verify that the schema being dropped is not an E-Maj schema containing log tables
          PERFORM 1 FROM emaj.emaj_relation
            WHERE rel_log_schema = r_dropped.object_name;
          IF FOUND THEN
            RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the schema "%". But dropping an E-Maj schema is not allowed.', r_dropped.object_name;
          END IF;
        WHEN r_dropped.object_type = 'table' THEN
-- the object is a table
--   look at the emaj_relation table to verify that the table being dropped does not belong to any active (not stopped) group
          SELECT rel_group INTO v_groupName FROM emaj.emaj_relation, emaj.emaj_group
            WHERE rel_schema = r_dropped.schema_name AND rel_tblseq = r_dropped.object_name
              AND group_name = rel_group AND group_is_logging;
          IF FOUND THEN
            RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the application table "%.%". But it belongs to the active tables group "%".', r_dropped.schema_name, r_dropped.object_name, v_groupName;
          END IF;
--   look at the emaj_relation table to verify that the table being dropped is not a log table
          PERFORM 1 FROM emaj.emaj_relation
            WHERE rel_log_schema = r_dropped.schema_name AND rel_log_table = r_dropped.object_name;
          IF FOUND THEN
            RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the log table "%.%". But dropping an E-Maj log table is not allowed.',
                            r_dropped.schema_name, r_dropped.object_name;
          END IF;
        WHEN r_dropped.object_type = 'sequence' THEN
-- the object is a sequence
--   look at the emaj_relation table to verify that the sequence being dropped does not belong to any active (not stopped) group
          SELECT rel_group INTO v_groupName FROM emaj.emaj_relation, emaj.emaj_group
            WHERE rel_schema = r_dropped.schema_name AND rel_tblseq = r_dropped.object_name
              AND group_name = rel_group AND group_is_logging;
          IF FOUND THEN
            RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the application sequence "%.%". But it belongs to the active tables group "%".', r_dropped.schema_name, r_dropped.object_name, v_groupName;
          END IF;
--   look at the emaj_relation table to verify that the sequence being dropped is not a log sequence
          PERFORM 1 FROM emaj.emaj_relation
            WHERE rel_log_schema = r_dropped.schema_name AND rel_log_sequence = r_dropped.object_name;
          IF FOUND THEN
            RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the log sequence "%.%". But dropping an E-Maj sequence is not allowed.', r_dropped.schema_name, r_dropped.object_name;
          END IF;
        WHEN r_dropped.object_type = 'function' THEN
-- the object is a function
--   look at the emaj_relation table to verify that the function being dropped is not a log function
          PERFORM 1 FROM emaj.emaj_relation
            WHERE  r_dropped.object_identity = quote_ident(rel_log_schema) || '.' || quote_ident(rel_log_function) || '()';
          IF FOUND THEN
            RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the log function "%". But dropping an E-Maj log function is not allowed.', r_dropped.object_identity;
          END IF;
        WHEN r_dropped.object_type = 'trigger' THEN
-- the object is a trigger
--   if postgres version is 9.5+ (to see the 'original' column of the pg_event_trigger_dropped_objects() function),
--   look at the trigger name pattern to identify emaj trigger
--   and do not raise an exception if the triggers drop is derived from a drop of a table or a function
          IF emaj._pg_version_num() >= 90500 THEN
            IF r_dropped.original AND
               (r_dropped.object_identity LIKE 'emaj_log_trg%' OR r_dropped.object_identity LIKE 'emaj_trunc_trg%') THEN
              RAISE EXCEPTION 'E-Maj event trigger: attempting to drop the "%" E-Maj trigger. But dropping an E-Maj trigger is not allowed.', r_dropped.object_identity;
            END IF;
          END IF;
        ELSE
          CONTINUE;
      END CASE;
    END LOOP;
  END;
$_event_trigger_sql_drop_fnct$;

COMMENT ON FUNCTION emaj._event_trigger_sql_drop_fnct() IS
$$E-Maj extension: support of the emaj_sql_drop_trg event trigger.$$;

EXECUTE '
CREATE EVENT TRIGGER emaj_sql_drop_trg
  ON sql_drop
  WHEN TAG IN (''DROP FUNCTION'',''DROP SCHEMA'',''DROP SEQUENCE'',''DROP TABLE'',''DROP TRIGGER'')
  EXECUTE PROCEDURE emaj._event_trigger_sql_drop_fnct();
';
EXECUTE '
COMMENT ON EVENT TRIGGER emaj_sql_drop_trg IS
$$Controls the removal of E-Maj components.$$;
';

-- add both event trigger components to the emaj extension
ALTER EXTENSION emaj ADD FUNCTION emaj._event_trigger_sql_drop_fnct();
EXECUTE '
ALTER EXTENSION emaj ADD EVENT TRIGGER emaj_sql_drop_trg;
';

      END IF;
-- end of 9.3+ specific code
    END IF;

-- beginning of 9.5+ specific code
    IF emaj._pg_version_num() >= 90500 THEN
-- table_rewrite event trigger are only possible with postgres 9.5+
-- if the emaj_table_rewrite_trg event trigger does not exist, create it
      PERFORM 1 FROM pg_catalog.pg_event_trigger WHERE evtname = 'emaj_table_rewrite_trg';
      IF FOUND THEN
        RAISE NOTICE 'E-Maj upgrade script: the "emaj_table_rewrite_trg" event trigger already exists.';
      ELSE
        RAISE NOTICE 'E-Maj upgrade script: create the "emaj_table_rewrite_trg" event trigger.';
--

CREATE OR REPLACE FUNCTION emaj._emaj_event_trigger_table_rewrite_fnct()
 RETURNS EVENT_TRIGGER LANGUAGE plpgsql AS
$_emaj_event_trigger_table_rewrite_fnct$
-- This function is called by the emaj_table_rewrite_trg event trigger
-- The function blocks any ddl operation that leads to a table rewrite for:
--   - an application table registered into an active (not stopped) E-Maj group
--   - an E-Maj log table
  DECLARE
    v_tableSchema            TEXT;
    v_tableName              TEXT;
    v_groupName              TEXT;
  BEGIN
-- get the schema and table names of the altered table
    SELECT nspname, relname INTO v_tableSchema, v_tableName FROM pg_catalog.pg_class, pg_catalog.pg_namespace
      WHERE relnamespace = pg_namespace.oid AND pg_class.oid = pg_event_trigger_table_rewrite_oid();
-- look at the emaj_relation table to verify that the table being rewritten does not belong to any active (not stopped) group
    SELECT rel_group INTO v_groupName FROM emaj.emaj_relation, emaj.emaj_group
      WHERE rel_schema = v_tableSchema AND rel_tblseq = v_tableName
              AND group_name = rel_group AND group_is_logging;
    IF FOUND THEN
-- the table is an application table that belongs to a group, so raise an exception
      RAISE EXCEPTION 'E-Maj event trigger: attempting to change the application table "%.%" structure. But the table belongs to the active tables group "%".',
                      v_tableSchema, v_tableName , v_groupName;
    END IF;
-- look at the emaj_relation table to verify that the table being rewritten is not a known log table
    SELECT rel_group INTO v_groupName FROM emaj.emaj_relation
      WHERE rel_log_schema = v_tableSchema AND rel_log_table = v_tableName;
    IF FOUND THEN
-- the table is an E-Maj log table, so raise an exception
      RAISE EXCEPTION 'E-Maj event trigger: attempting to change the log table "%.%" structure. But the table belongs to the tables group "%".',
                      v_tableSchema, v_tableName , v_groupName;
    END IF;
  END;
$_emaj_event_trigger_table_rewrite_fnct$;

COMMENT ON FUNCTION emaj._emaj_event_trigger_table_rewrite_fnct() IS
$$E-Maj extension: support of the emaj_table_rewrite_trg event trigger.$$;

EXECUTE '
CREATE EVENT TRIGGER emaj_table_rewrite_trg
  ON table_rewrite
  EXECUTE PROCEDURE emaj._emaj_event_trigger_table_rewrite_fnct();
';
EXECUTE '
COMMENT ON EVENT TRIGGER emaj_table_rewrite_trg IS
$$Controls some changes in E-Maj tables structure.$$;
';

-- add both event trigger components to the emaj extension
ALTER EXTENSION emaj ADD FUNCTION emaj._emaj_event_trigger_table_rewrite_fnct();
EXECUTE '
ALTER EXTENSION emaj ADD EVENT TRIGGER emaj_table_rewrite_trg;
';

      END IF;
-- end of 9.5+ specific code
    END IF;
  END;
$do$;
COMMIT;