/* ---------------------------------------------------------------------------------------------------------------------------------
Routine Name: createTablesPostgreSQL.sql
Author:       Mike Revitt
Date:         12/11/2018
------------------------------------------------------------------------------------------------------------------------------------
Revision History    Push Down List
------------------------------------------------------------------------------------------------------------------------------------
Date        | Name          | Description
------------+---------------+-------------------------------------------------------------------------------------------------------
            |               |
12/11/2018  | M Revitt      | Initial version
------------+---------------+-------------------------------------------------------------------------------------------------------
Background:     PostGre does not support Materialized View Fast Refreshes, this suite of scripts is a PL/SQL coded mechanism to
                provide that functionality, the next phase of this projecdt is to fold these changes into the PostGre kernel.

Description:    This script creates the SCHEMA and USER to hold the Materialized View Fast Refresh code along with the necessary
                data dictionary views, then it calls the create function scripts in the correct order

Notes:          There are 2 data dictionary tables
                o   mike$_pgiew_logs
                o   mike$_pgiews

                Access is controlled via 3 database roles
                o   mike_execute   -   is given the privileges to run the public materialized view functions
                o   mike_usage     -   is given usage on the mike_pgiew schema
                o   mike_view      -   is given access to the DDL tables

Issues:         There is a bug in RDS for PostGres version 10.4 that prevents this code from working, this but is fixed in
                versions 10.5 and 10.3

                https://forums.aws.amazon.com/thread.jspa?messageID=860564

************************************************************************************************************************************
Copyright 2018 Amazon.com, Inc. or its affiliates. All Rights Reserved.

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files
(the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify,
merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is
furnished to do so.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
***********************************************************************************************************************************/

-- psql -h $PGHOST -p $PGPORT -U $PGUSER -d $PGNAME -f @createTablesPostgreSQL.sql
SET   client_min_messages TO ERROR;

\o /dev/null

-- ------------ Write DROP-DATABASE-stage scripts -----------

DROP    SCHEMA  IF      EXISTS snap_test CASCADE;

-- ------------ Write CREATE-DATABASE-stage scripts -----------
\set  ON_ERROR_STOP 1

CREATE  SCHEMA   IF NOT EXISTS snap_test;

-- ------------ Write CREATE-TABLE-stage scripts -----------

CREATE TABLE snap_test.edge_case_view(
    c1_code         INTEGER       NOT NULL,
    c1_name         TEXT          NOT NULL,
    c1_created      TIMESTAMP     NOT NULL,
    t1_key          INTEGER       NOT NULL,
    t1_name         TEXT          NOT NULL,
    t1_created      TIMESTAMP     NOT NULL,
    t1_order_date   TIMESTAMP,
    t1_order_value  NUMERIC(20,2),
    t1_updated      TIMESTAMP,
    t2_key          INTEGER       NOT NULL,
    t2_name         TEXT          NOT NULL,
    t2_created      TIMESTAMP     NOT NULL,
    t2_order_date   TIMESTAMP,
    t2_order_value  NUMERIC(20,2),
    t2_updated      TIMESTAMP,
    t3_key          INTEGER,
    t3_name         TEXT,
    t3_created      TIMESTAMP,
    t3_order_date   TIMESTAMP,
    t3_order_value  NUMERIC(20,2),
    t3_updated      TIMESTAMP,
    t4_key          INTEGER,
    t4_name         TEXT,
    t4_created      TIMESTAMP,
    t4_order_date   TIMESTAMP,
    t4_order_value  NUMERIC(20,2),
    t4_updated      TIMESTAMP,
    t5_key          INTEGER,
    t5_name         TEXT,
    t5_created      TIMESTAMP,
    t5_order_date   TIMESTAMP,
    t5_order_value  NUMERIC(20,2),
    t5_updated      TIMESTAMP,
    t6_key          INTEGER,
    t6_name         TEXT,
    t6_created      TIMESTAMP,
    t6_order_date   TIMESTAMP,
    t6_order_value  NUMERIC(20,2),
    t6_updated      TIMESTAMP,
    t7_key          INTEGER,
    t7_name         TEXT,
    t7_created      TIMESTAMP,
    t7_order_date   TIMESTAMP,
    t7_order_value  NUMERIC(20,2),
    t7_updated      TIMESTAMP,
    t8_key          INTEGER,
    t8_name         TEXT,
    t8_created      TIMESTAMP,
    t8_order_date   TIMESTAMP,
    t8_order_value  NUMERIC(20,2),
    t8_updated      TIMESTAMP,
    t9_key          INTEGER,
    t9_name         TEXT,
    t9_created      TIMESTAMP,
    t9_order_date   TIMESTAMP,
    t9_order_value  NUMERIC(20,2),
    t9_updated      TIMESTAMP,
    t10_key         INTEGER,
    t10_name        TEXT,
    t10_created     TIMESTAMP,
    t10_order_date  TIMESTAMP,
    t10_order_value NUMERIC(20,2),
    t10_updated     TIMESTAMP,
    c1_rowid        TEXT,
    t1_rowid        TEXT,
    t2_rowid        TEXT,
    t3_rowid        TEXT,
    t4_rowid        TEXT,
    t5_rowid        TEXT,
    t6_rowid        TEXT,
    t7_rowid        TEXT,
    t8_rowid        TEXT,
    t9_rowid        TEXT,
    t10_rowid       TEXT
);

CREATE TABLE snap_test.edge_case_pk_view(
    t1_id             INTEGER       NOT NULL,
    t1_name           TEXT          NOT NULL,
    t1_created        TIMESTAMP     NOT NULL,
    t1_order_date     TIMESTAMP,
    t1_order_value    NUMERIC(20,2),
    t1_updated        TIMESTAMP,
    t1a_id            INTEGER       NOT NULL,
    t1a_name          TEXT          NOT NULL,
    t1a_created       TIMESTAMP     NOT NULL,
    t1a_order_date    TIMESTAMP,
    t1a_order_value   NUMERIC(20,2),
    t1a_updated       TIMESTAMP,
    t2_id             INTEGER       NOT NULL,
    t2_name           TEXT          NOT NULL,
    t2_created        TIMESTAMP     NOT NULL,
    t2_order_date     TIMESTAMP,
    t2_order_value    NUMERIC(20,2),
    t2_updated        TIMESTAMP,
    t3_id             INTEGER       NOT NULL,
    t3_name           TEXT          NOT NULL,
    t3_created        TIMESTAMP     NOT NULL,
    t3_order_date     TIMESTAMP,
    t3_order_value    NUMERIC(20,2),
    t3_updated        TIMESTAMP,
    t4_id             INTEGER,
    t4_name           TEXT,
    t4_created        TIMESTAMP,
    t4_order_date     TIMESTAMP,
    t4_order_value    NUMERIC(20,2),
    t4_updated        TIMESTAMP,
    t5_id             INTEGER       NOT NULL,
    t5_name           TEXT          NOT NULL,
    t5_created        TIMESTAMP     NOT NULL,
    t5_order_date     TIMESTAMP,
    t5_order_value    NUMERIC(20,2),
    t5_updated        TIMESTAMP,
    t5a_id            INTEGER       NOT NULL,
    t5a_name          TEXT          NOT NULL,
    t5a_created       TIMESTAMP     NOT NULL,
    t5a_order_date    TIMESTAMP,
    t5a_order_value   NUMERIC(20,2),
    t5a_updated       TIMESTAMP,
    t6_id             INTEGER,
    t6_name           TEXT,
    t6_created        TIMESTAMP,
    t6_order_date     TIMESTAMP,
    t6_order_value    NUMERIC(20,2),
    t6_updated        TIMESTAMP,
    t7_id             INTEGER,
    t7_name           TEXT,
    t7_created        TIMESTAMP,
    t7_order_date     TIMESTAMP,
    t7_order_value    NUMERIC(20,2),
    t7_updated        TIMESTAMP,
    t8_id             INTEGER,
    t8_name           TEXT,
    t8_created        TIMESTAMP,
    t8_order_date     TIMESTAMP,
    t8_order_value    NUMERIC(20,2),
    t8_updated        TIMESTAMP,
    t9_id             INTEGER,
    t9_name           TEXT,
    t9_created        TIMESTAMP,
    t9_order_date     TIMESTAMP,
    t9_order_value    NUMERIC(20,2),
    t9_updated        TIMESTAMP,
    t10_id            INTEGER,
    t10_name          TEXT,
    t10_created       TIMESTAMP,
    t10_order_date    TIMESTAMP,
    t10_order_value   NUMERIC(20,2),
    t10_updated       TIMESTAMP,
    t11_id            INTEGER,
    t11_name          TEXT,
    t11_created       TIMESTAMP,
    t11_order_date    TIMESTAMP,
    t11_order_value   NUMERIC(20,2),
    t11_updated       TIMESTAMP,
    t12a_id           INTEGER       NOT NULL,
    t12a_name         TEXT          NOT NULL,
    t12a_created      TIMESTAMP     NOT NULL,
    t12a_order_date   TIMESTAMP,
    t12a_order_value  NUMERIC(20,2),
    t12a_updated      TIMESTAMP,
    t12b_id           INTEGER,
    t12b_name         TEXT,
    t12b_created      TIMESTAMP,
    t12b_order_date   TIMESTAMP,
    t12b_order_value  NUMERIC(20,2),
    t12b_updated      TIMESTAMP,
    t12c_id           INTEGER,
    t12c_name         TEXT,
    t12c_created      TIMESTAMP,
    t12c_order_date   TIMESTAMP,
    t12c_order_value  NUMERIC(20,2),
    t12c_updated      TIMESTAMP,
    t12d_id           INTEGER,
    t12d_name         TEXT,
    t12d_created      TIMESTAMP,
    t12d_order_date   TIMESTAMP,
    t12d_order_value  NUMERIC(20,2),
    t12d_updated      TIMESTAMP,
    t12e_id           INTEGER,
    t12e_name         TEXT,
    t12e_created      TIMESTAMP,
    t12e_order_date   TIMESTAMP,
    t12e_order_value  NUMERIC(20,2),
    t12e_updated      TIMESTAMP,
    t12f_id           INTEGER,
    t12f_name         TEXT,
    t12f_created      TIMESTAMP,
    t12f_order_date   TIMESTAMP,
    t12f_order_value  NUMERIC(20,2),
    t12f_updated      TIMESTAMP,
    t12g_id           INTEGER,
    t12g_name         TEXT,
    t12g_created      TIMESTAMP,
    t12g_order_date   TIMESTAMP,
    t12g_order_value  NUMERIC(20,2),
    t12g_updated      TIMESTAMP,
    t12h_id           INTEGER,
    t12h_name         TEXT,
    t12h_created      TIMESTAMP,
    t12h_order_date   TIMESTAMP,
    t12h_order_value  NUMERIC(20,2),
    t12h_updated      TIMESTAMP,
    t12i_id           INTEGER,
    t12i_name         TEXT,
    t12i_created      TIMESTAMP,
    t12i_order_date   TIMESTAMP,
    t12i_order_value  NUMERIC(20,2),
    t12i_updated      TIMESTAMP,
    t1_rowid          TEXT,
    t1a_rowid         TEXT,
    t2_rowid          TEXT,
    t3_rowid          TEXT,
    t4_rowid          TEXT,
    t5_rowid          TEXT,
    t5a_rowid         TEXT,
    t6_rowid          TEXT,
    t7_rowid          TEXT,
    t8_rowid          TEXT,
    t9_rowid          TEXT,
    t10_rowid         TEXT,
    t11_rowid         TEXT,
    t12a_rowid        TEXT,
    t12b_rowid        TEXT,
    t12c_rowid        TEXT,
    t12d_rowid        TEXT,
    t12e_rowid        TEXT,
    t12f_rowid        TEXT,
    t12g_rowid        TEXT,
    t12h_rowid        TEXT,
    t12i_rowid        TEXT
);

CREATE TABLE snap_test.edge_case_all_cols_view(
    t1_id             INTEGER       NOT NULL,
    t1_name           TEXT          NOT NULL,
    t1_created        TIMESTAMP     NOT NULL,
    t1_order_date     TIMESTAMP,
    t1_order_value    NUMERIC(20,2),
    t1_updated        TIMESTAMP,
    t1a_id            INTEGER       NOT NULL,
    t1a_name          TEXT          NOT NULL,
    t1a_created       TIMESTAMP     NOT NULL,
    t1a_order_date    TIMESTAMP,
    t1a_order_value   NUMERIC(20,2),
    t1a_updated       TIMESTAMP,
    t2_id             INTEGER       NOT NULL,
    t2_name           TEXT          NOT NULL,
    t2_created        TIMESTAMP     NOT NULL,
    t2_order_date     TIMESTAMP,
    t2_order_value    NUMERIC(20,2),
    t2_updated        TIMESTAMP,
    t3_id             INTEGER       NOT NULL,
    t3_name           TEXT          NOT NULL,
    t3_created        TIMESTAMP     NOT NULL,
    t3_order_date     TIMESTAMP,
    t3_order_value    NUMERIC(20,2),
    t3_updated        TIMESTAMP,
    t4_id             INTEGER,
    t4_name           TEXT,
    t4_created        TIMESTAMP,
    t4_order_date     TIMESTAMP,
    t4_order_value    NUMERIC(20,2),
    t4_updated        TIMESTAMP,
    t5_id             INTEGER       NOT NULL,
    t5_name           TEXT          NOT NULL,
    t5_created        TIMESTAMP     NOT NULL,
    t5_order_date     TIMESTAMP,
    t5_order_value    NUMERIC(20,2),
    t5_updated        TIMESTAMP,
    t5a_id            INTEGER       NOT NULL,
    t5a_name          TEXT          NOT NULL,
    t5a_created       TIMESTAMP     NOT NULL,
    t5a_order_date    TIMESTAMP,
    t5a_order_value   NUMERIC(20,2),
    t5a_updated       TIMESTAMP,
    t6_id             INTEGER,
    t6_name           TEXT,
    t6_created        TIMESTAMP,
    t6_order_date     TIMESTAMP,
    t6_order_value    NUMERIC(20,2),
    t6_updated        TIMESTAMP,
    t7_id             INTEGER,
    t7_name           TEXT,
    t7_created        TIMESTAMP,
    t7_order_date     TIMESTAMP,
    t7_order_value    NUMERIC(20,2),
    t7_updated        TIMESTAMP,
    t8_id             INTEGER,
    t8_name           TEXT,
    t8_created        TIMESTAMP,
    t8_order_date     TIMESTAMP,
    t8_order_value    NUMERIC(20,2),
    t8_updated        TIMESTAMP,
    t9_id             INTEGER,
    t9_name           TEXT,
    t9_created        TIMESTAMP,
    t9_order_date     TIMESTAMP,
    t9_order_value    NUMERIC(20,2),
    t9_updated        TIMESTAMP,
    t10_id            INTEGER,
    t10_name          TEXT,
    t10_created       TIMESTAMP,
    t10_order_date    TIMESTAMP,
    t10_order_value   NUMERIC(20,2),
    t10_updated       TIMESTAMP,
    t11_id            INTEGER,
    t11_name          TEXT,
    t11_created       TIMESTAMP,
    t11_order_date    TIMESTAMP,
    t11_order_value   NUMERIC(20,2),
    t11_updated       TIMESTAMP,
    t12a_id           INTEGER       NOT NULL,
    t12a_name         TEXT          NOT NULL,
    t12a_created      TIMESTAMP     NOT NULL,
    t12a_order_date   TIMESTAMP,
    t12a_order_value  NUMERIC(20,2),
    t12a_updated      TIMESTAMP,
    t12b_id           INTEGER,
    t12b_name         TEXT,
    t12b_created      TIMESTAMP,
    t12b_order_date   TIMESTAMP,
    t12b_order_value  NUMERIC(20,2),
    t12b_updated      TIMESTAMP,
    t12c_id           INTEGER,
    t12c_name         TEXT,
    t12c_created      TIMESTAMP,
    t12c_order_date   TIMESTAMP,
    t12c_order_value  NUMERIC(20,2),
    t12c_updated      TIMESTAMP,
    t12d_id           INTEGER,
    t12d_name         TEXT,
    t12d_created      TIMESTAMP,
    t12d_order_date   TIMESTAMP,
    t12d_order_value  NUMERIC(20,2),
    t12d_updated      TIMESTAMP,
    t12e_id           INTEGER,
    t12e_name         TEXT,
    t12e_created      TIMESTAMP,
    t12e_order_date   TIMESTAMP,
    t12e_order_value  NUMERIC(20,2),
    t12e_updated      TIMESTAMP,
    t12f_id           INTEGER,
    t12f_name         TEXT,
    t12f_created      TIMESTAMP,
    t12f_order_date   TIMESTAMP,
    t12f_order_value  NUMERIC(20,2),
    t12f_updated      TIMESTAMP,
    t12g_id           INTEGER,
    t12g_name         TEXT,
    t12g_created      TIMESTAMP,
    t12g_order_date   TIMESTAMP,
    t12g_order_value  NUMERIC(20,2),
    t12g_updated      TIMESTAMP,
    t12h_id           INTEGER,
    t12h_name         TEXT,
    t12h_created      TIMESTAMP,
    t12h_order_date   TIMESTAMP,
    t12h_order_value  NUMERIC(20,2),
    t12h_updated      TIMESTAMP,
    t12i_id           INTEGER,
    t12i_name         TEXT,
    t12i_created      TIMESTAMP,
    t12i_order_date   TIMESTAMP,
    t12i_order_value  NUMERIC(20,2),
    t12i_updated      TIMESTAMP,
    t1_rowid          TEXT,
    t1a_rowid         TEXT,
    t2_rowid          TEXT,
    t3_rowid          TEXT,
    t4_rowid          TEXT,
    t5_rowid          TEXT,
    t5a_rowid         TEXT,
    t6_rowid          TEXT,
    t7_rowid          TEXT,
    t8_rowid          TEXT,
    t9_rowid          TEXT,
    t10_rowid         TEXT,
    t11_rowid         TEXT,
    t12a_rowid        TEXT,
    t12b_rowid        TEXT,
    t12c_rowid        TEXT,
    t12d_rowid        TEXT,
    t12e_rowid        TEXT,
    t12f_rowid        TEXT,
    t12g_rowid        TEXT,
    t12h_rowid        TEXT,
    t12i_rowid        TEXT
);

-- ------------ Write CREATE-CONSTRAINT-stage scripts -----------

ALTER   TABLE snap_test.edge_case_view
ADD     PRIMARY KEY (c1_code);

ALTER   TABLE snap_test.edge_case_pk_view
ADD     PRIMARY KEY (t1_id);

CREATE  INDEX edge_case_complex_view_ind
ON      snap_test.edge_case_all_cols_view(t1_id);
