/* --------------------------------------------------------------------------------------------------------------------------------- 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);