/* **************************************************************************************************************************
 * Copyright 2020 VMware, Inc.   All rights reserved. -- VMware Confidential
 * **************************************************************************************************************************/

/*=====================================================================================================================================================*/
/* VCDB_NDU_CNT_ADD_FOREIGN_KEY_CONST.sql                                                                                                              */
/*=====================================================================================================================================================*/
/* Execution Instructions & Examples:                                                                                                                  */
/* To executed the ADD CONSTRAINT procedure:                                                                                                           */
/*                                                                                                                                                     */
/* call vcdb_ndu_add_foreign_key_const('tab_schema', 'table_name', 'constraint_name', 'column_name', 'ref_table_name', 'ref_column_name', 'on_delete') */
/* The on_delete parameter is for cases using ON DELETE CASCADE option, if you don't want that option ommit this intup parameter. If you wish to use   */
/* it write it like so 'ON DELETE CASCADE'                                                                                                             */
/*                                                                                                                                                     */
/* For additional detail visit confluence page:                                                                                                        */
/* https://confluence.eng.vmware.com/display/VCUSOF/Operation+ALTER+TABLE+ADD+FOREIGN+KEY+CONSTRAINT+template                                          */
/*=====================================================================================================================================================*/

CREATE OR REPLACE PROCEDURE vcdb_ndu_add_foreign_key_const
(
tab_schema    VARCHAR(50),
tab_name      VARCHAR(50),
const_name    VARCHAR(50),
col_name      VARCHAR(50),
ref_tab_name  VARCHAR(50),
ref_col_name  VARCHAR(50),
on_delete     TEXT DEFAULT ''
)
 LANGUAGE plpgsql
AS $vcdb_ndu_add_foreign_key_const$

DECLARE
    const_check       INT := 0;
    l_add_const       TEXT;
    l_validate_const  TEXT;
BEGIN

   SET LOCAL statement_timeout = 2000;
   SET LOCAL lock_timeout = '2s';

   tab_schema   := lower(tab_schema);
   tab_name     := lower(tab_name);
   const_name   := lower(const_name);
   col_name     := lower(col_name);
   ref_tab_name := lower(ref_tab_name);
   ref_col_name := lower(ref_col_name);

   /* Prepare function variables */
   l_add_const := FORMAT('ALTER TABLE %I.%I ADD CONSTRAINT %I FOREIGN KEY (%I) REFERENCES %I (%I) %s NOT VALID', tab_schema, tab_name, const_name, col_name, ref_tab_name, ref_col_name, on_delete);
   l_validate_const := FORMAT('ALTER TABLE %I.%I VALIDATE CONSTRAINT %I', tab_schema, tab_name, const_name);

   /* STEP 1 */
   /* Perform check to verify constraint does NOT EXISTS */
   BEGIN
      SELECT COUNT(con.conname) INTO const_check
        FROM pg_catalog.pg_constraint con
             INNER JOIN pg_catalog.pg_class rel
                        ON rel.oid = con.conrelid
             INNER JOIN pg_catalog.pg_namespace nsp
                        ON nsp.oid = connamespace
        WHERE nsp.nspname = tab_schema
          AND rel.relname = tab_name
          AND con.conname = const_name;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG '- sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   /* Add check constraint if it doesn't EXISTS */
   BEGIN
      IF ( const_check = 0 )
         THEN EXECUTE l_add_const;
           RAISE LOG 'Constraint % added to table %.%', const_name, tab_schema, tab_name;
      ELSIF ( const_check = 1 )
         THEN RAISE LOG 'Constraint % already added to table %.%', const_name, tab_schema, tab_name;
      END IF;
   EXCEPTION
     WHEN OTHERS THEN
        RAISE LOG 'Failed to add constraint % on table %.% - sqlstate: %, sqlerrm: %', const_name, tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;
   COMMIT; /* In situations where the procedure fails on STEP 2 this COMMIT ensures that the constraint will be present when the procedure is re-initiated, hence leaving only the VALIDATION to be executed */

   /* STEP 2 */
   /* Perform check to verify constraint EXISTS and is NOT validated */
   BEGIN
      SELECT COUNT(con.conname) INTO const_check
        FROM pg_catalog.pg_constraint con
             INNER JOIN pg_catalog.pg_class rel
                        ON rel.oid = con.conrelid
             INNER JOIN pg_catalog.pg_namespace nsp
                        ON nsp.oid = connamespace
        WHERE nsp.nspname = tab_schema
          AND rel.relname = tab_name
          AND con.conname = const_name
          AND con.convalidated IS FALSE;
   EXCEPTION
     WHEN OTHERS THEN
       RAISE LOG '- sqlstate: %, sqlerrm: %', SQLSTATE, SQLERRM;
   END;

   /* Validate check constraint if it EXISTS */
   BEGIN
      IF ( const_check = 1 )
         THEN EXECUTE l_validate_const;
           RAISE LOG 'Constraint % validated for table %.%', const_name, tab_schema, tab_name;
      ELSIF ( const_check = 0 )
         THEN RAISE LOG 'Constraint % already validated for table %.%', const_name, tab_schema, tab_name;
      END IF;
   EXCEPTION
     WHEN OTHERS THEN
        RAISE LOG 'Failed to validate constraint % on table %.% - sqlstate: %, sqlerrm: %', const_name, tab_schema, tab_name, SQLSTATE, SQLERRM;
   END;

END;
$vcdb_ndu_add_foreign_key_const$;

----------------------------------------------------
-- END OF FUNCTION
----------------------------------------------------