• R/O
  • SSH

execsql-upsert: 提交

Default repository for the execsql-upsert scripts


Commit MetaInfo

修訂462eeeeb09f0b3c911a21a2fa00087351eaf7133 (tree)
時間2019-05-30 10:55:08
作者Elizabeth Shea <elizabethshea@prot...>
CommiterElizabeth Shea

Log Message

Added script UPDTPK_ONE to pg_upsert

Change Summary

差異

diff -r 8714d454fe71 -r 462eeeeb09f0 upsert/pg_upsert.sql
--- a/upsert/pg_upsert.sql Tue May 28 18:37:19 2019 -0700
+++ b/upsert/pg_upsert.sql Wed May 29 18:55:08 2019 -0700
@@ -39,6 +39,8 @@
3939 -- PKQA_ONE : Perform primary key check on one staging table.
4040 -- FKQA_ONE : Perform foreign key checks on one staging table.
4141 -- UPSERT_ONE : Load data from one staging table.
42+-- UPDTPK_ONE : Perform PK updates for one table.
43+-- UPDTPKQA_ONE : Perform QA checks related to PK updates, for one table.
4244 -- This file contains other scripts that are intended to be used
4345 -- only by one of the scripts listed above, and not called
4446 -- directly by the user.
@@ -65,7 +67,7 @@
6567 -- base table.
6668 -- 7. All of these scripts assume that schema, table, and column
6769 -- names need not be quoted.
68--- 8. These scripts create temporary tables and views. All of thes
70+-- 8. These scripts create temporary tables and views. All of these
6971 -- have prefixes of "ups_". Scripts that include this file
7072 -- should not use this prefix to avoid possible conflicts.
7173 --
@@ -149,6 +151,7 @@
149151 -- include only columns that are also in base table (i.e.,
150152 -- exclude any columns that are only in staging table, such
151153 -- as any "new_" PK columns to be used for PK updates). ES.
154+-- 2019-05-29 Added script UPDTPK_ONE. ES.
152155 -- ==================================================================
153156
154157
@@ -1924,3 +1927,200 @@
19241927 -- #################################################################
19251928
19261929
1930+
1931+-- ################################################################
1932+-- Script UPDTPK_ONE
1933+--
1934+-- Updates primary keys in base table, based on new and older
1935+-- values of PK columns in a staging table, using UPDATE
1936+-- statements. Displays data to be modified to the
1937+-- user before any modifications are done. Reports the changes
1938+-- made to the console and optionally to a log file.
1939+--
1940+-- Input parameters:
1941+-- base_schema : The name of the base table schema.
1942+-- staging : The name of the staging schema.
1943+-- table : The table name--same for base and staging.
1944+-- display_errors : A value of 'Yes' or 'No' to indicate whether
1945+-- any errors should be displayed in a GUI.
1946+-- display_changes : A value of 'Yes' or 'No' to indicate whether
1947+-- or not the changes to be made to the
1948+-- base table should be displayed in a GUI.
1949+--
1950+-- Global variables:
1951+-- logfile : The name of a log file to which update
1952+-- messages will be written. Optional.
1953+-- log_sql : A value of 'Yes' or 'No' indicating whether
1954+-- the update and insert statements should
1955+-- also be written to the logfile. Optional.
1956+-- log_changes : A value of 'Yes' or 'No' indicating whether
1957+-- the updated and inserted data should be
1958+-- written to the logfile. Optional.
1959+--
1960+-- Tables and views created or modified:
1961+-- ups_pkqa_errors : temporary table
1962+-- ups_pkcollinfo : temporary table
1963+-- ups_pkupdates : temporary table
1964+-- ups_pkupdate_strings : temporary view
1965+-- ups_pkupdates : temporary table
1966+-- ===============================================================
1967+
1968+-- !x! BEGIN SCRIPT UPDTPK_ONE with parameters (base_schema, staging, table, display_errors, display_changes)
1969+
1970+-- !x! if(console_on)
1971+ -- !x! console status "Primary key updates"
1972+-- !x! endif
1973+
1974+
1975+-- Validate inputs: base/staging schemas and table
1976+-- !x! execute script validate_one with args (base_schema=!!#base_schema!!, staging=!!#staging!!, table=!!#table!!, script=!!$CURRENT_SCRIPT!!, script_line=!!$SCRIPT_LINE!!)
1977+
1978+-- Write an initial header to the logfile.
1979+-- !x! if(sub_defined(logfile))
1980+ -- !x! write "" to !!logfile!!
1981+ -- !x! write "==================================================================" to !!logfile!!
1982+ -- !x! write "!!$current_time!! -- Performing primary key updates on table !!#base_schema!!.!!#table!! from !!#staging!!.!!#table!!" to !!logfile!!
1983+-- !x! endif
1984+
1985+-- !x! write "Performing primary key updates on table !!#base_schema!!.!!#table!! from !!#staging!!.!!#table!!"
1986+
1987+-- Create a temp table to store the results of the PK update QA checks
1988+drop table if exists ups_pkqa_errors cascade;
1989+create temporary table ups_pkqa_errors (
1990+ error_code varchar(40),
1991+ error_description varchar(500)
1992+);
1993+
1994+
1995+-- Populate a (temporary) table with the names of the primary key columns of the base table.
1996+-- Get the old and new primary key columns from staging table into various formats
1997+-- to use later to construct SQL statement to select records in various ways for both updates and QA checks.
1998+-- Include column lists, join expression, and where clause
1999+drop table if exists ups_pkcol_info cascade;
2000+select
2001+ k.table_schema,
2002+ k.table_name,
2003+ k.column_name,
2004+ cast('b.' || column_name as text) as base_aliased,
2005+ cast('s.' || column_name as text) as staging_aliased,
2006+ cast('s.' || column_name || ' as staging_' || column_name as text) as staging_aliased_prefix,
2007+ cast('b.' || column_name || ' = s.' || column_name as text) as join_expr,
2008+ cast('new_' || column_name as text) as newpk_col,
2009+ cast('s.new_' || column_name as text) as newpk_col_aliased,
2010+ cast('new_' || column_name || ' is null' as text) as newpk_col_empty,
2011+ cast('new_' || column_name || ' is not null' as text) as newpk_col_not_empty,
2012+ cast(column_name || ' = s.new_' || column_name as text) as assmt_expr,
2013+ cast('s.new_' || column_name || ' = b.new_' || column_name as text) as join_expr_new,
2014+ k.ordinal_position
2015+into temporary table ups_pkcol_info
2016+from information_schema.table_constraints as tc
2017+inner join information_schema.key_column_usage as k
2018+ on tc.constraint_type = 'PRIMARY KEY'
2019+ and tc.constraint_name = k.constraint_name
2020+ and tc.constraint_catalog = k.constraint_catalog
2021+ and tc.constraint_schema = k.constraint_schema
2022+ and tc.table_schema = k.table_schema
2023+ and tc.table_name = k.table_name
2024+ and tc.constraint_name = k.constraint_name
2025+where
2026+ k.table_name = '!!#table!!'
2027+ and k.table_schema = '!!#base_schema!!'
2028+;
2029+
2030+
2031+-- Run QA checks
2032+-- UNCOMMENT AFTER QA SCRIPT IS DEVELOPED
2033+-- -- !x! execute script UPDTPKQA_ONE with arguments(base_schema=!!#base_schema!!, staging=!!#staging!!, table=!!#table!!, pkinfo_table=#ups_pkcol_info, qaerror_table=#ups_pkqa_errors, display_errors=!!#display_errors!!)
2034+
2035+
2036+-- Run the PK update ONLY if QA check script returned no errors
2037+-- !x! if(not hasrows(ups_pkqa_errors))
2038+ -- !x! rm_sub ~updatestmt
2039+
2040+ -- !x! sub ~do_updates Yes
2041+
2042+ -- !x! if(sub_defined(logfile))
2043+ -- !x! write "" to !!logfile!!
2044+ -- !x! write "==================================================================" to !!logfile!!
2045+ -- !x! write "!!$current_time!! -- Performing primary key update on table !!#base_schema!!.!!#table!!" to !!logfile!!
2046+ -- !x! endif
2047+
2048+ -- !x! if(console_on)
2049+ -- !x! console status "Performing PK updates"
2050+ -- !x! console progress 0
2051+ -- !x! endif
2052+
2053+ -- !x! write "Performing primary key update on table !!#base_schema!!.!!#table!!"
2054+
2055+ -- Create strings necessary to construct SQL to perform the updates
2056+ drop view if exists ups_pkupdate_strings cascade;
2057+ create temporary view ups_pkupdate_strings as
2058+ select
2059+ string_agg(base_aliased, ', ' order by ordinal_position) as oldpk_cols,
2060+ string_agg(newpk_col, ', ' order by ordinal_position) as newpk_cols,
2061+ string_agg(join_expr, ' and ' order by ordinal_position) as joinexpr,
2062+ string_agg(newpk_col_not_empty, ' and ' order by ordinal_position) as all_newpk_col_not_empty,
2063+ string_agg(assmt_expr, ', ' order by ordinal_position) as assmt_expr
2064+ from ups_pkcol_info
2065+ group by table_schema, table_name
2066+ ;
2067+ -- !x! select_sub ups_pkupdate_strings
2068+
2069+ -- Create a FROM clause for an inner join between base and staging
2070+ -- tables on the primary key column(s).
2071+ -- !x! sub ~fromclause FROM !!#base_schema!!.!!#table!! as b INNER JOIN !!#staging!!.!!#table!! as s ON !!@joinexpr!!
2072+
2073+ -- Create a WHERE clause for the rows to include in the selection (only those having new PK columns populated in the staging table)
2074+ -- !x! sub ~whereclause WHERE !!@all_newpk_col_not_empty!!
2075+
2076+ -- Select all matches for PK update into temp table
2077+ drop table if exists ups_pkupdates cascade;
2078+ select
2079+ !!@oldpk_cols!!,
2080+ !!@newpk_cols!!
2081+ into temporary table ups_pkupdates
2082+ !!~fromclause!!
2083+ !!~whereclause!!
2084+ ;
2085+
2086+ -- Prompt user to examine matching data and commit, don't commit, or quit.
2087+ -- !x! if(hasrows(ups_pkupdates))
2088+ -- !x! if(is_true(!!#display_changes!!))
2089+ -- !x! prompt ask "Do you want to make these changes to primary key values for table !!#table!!?" sub ~do_updates display ups_pkupdates
2090+ -- !x! endif
2091+ -- !x! if(is_true(!!~do_updates!!))
2092+
2093+ -- Create an UPDATE statement to update PK columns of the base table with
2094+ -- "new" PK columns from the staging table. No semicolon terminating generated SQL.
2095+ -- !x! sub ~updatestmt UPDATE !!#base_schema!!.!!#table!! as b SET !!@assmt_expr!! FROM !!#staging!!.!!#table!! as s WHERE !!@joinexpr!! and !!@all_newpk_col_not_empty!!
2096+
2097+ -- !x! write "Updating !!#base_schema!!.!!#table!!"
2098+ -- !x! if(sub_defined(logfile))
2099+ -- !x! write "" to !!logfile!!
2100+ -- !x! if(sub_defined(log_sql))
2101+ -- !x! andif(is_true(!!log_sql!!))
2102+ -- !x! write "UPDATE statement for !!#base_schema!!.!!#table!!:" to !!logfile!!
2103+ -- !x! write [!!~updatestmt!!] to !!logfile!!
2104+ -- !x! endif
2105+ -- !x! if(sub_defined(log_changes))
2106+ -- !x! andif(is_true(!!log_changes!!))
2107+ -- !x! write "Updates:" to !!logfile!!
2108+ -- !x! export ups_pkupdates append to !!logfile!! as txt
2109+ -- !x! endif
2110+ -- !x! write "" to !!logfile!!
2111+ -- !x! endif
2112+ !!~updatestmt!!;
2113+ -- !x! if(sub_defined(logfile))
2114+ -- !x! write "!!$last_rowcount!! rows of !!#base_schema!!.!!#table!! updated." to !!logfile!!
2115+ -- !x! endif
2116+ -- !x! write " !!$last_rowcount!! rows updated."
2117+ -- !x! endif
2118+ -- !x! else
2119+ --!x! write "No primary key updates specified for existing records in !!#base_schema!!.!!#table!!"
2120+ -- !x! endif
2121+-- !x! endif
2122+
2123+
2124+-- !x! END SCRIPT
2125+-- ################### End of UPDTPK_ONE ########################
2126+-- ################################################################
\ No newline at end of file
diff -r 8714d454fe71 -r 462eeeeb09f0 upsert/ss_upsert.sql
--- a/upsert/ss_upsert.sql Tue May 28 18:37:19 2019 -0700
+++ b/upsert/ss_upsert.sql Wed May 29 18:55:08 2019 -0700
@@ -2182,9 +2182,6 @@
21822182
21832183 -- Run the PK update ONLY if QA check script returned no errors
21842184 -- !x! if(not hasrows(#ups_pkqa_errors))
2185- -- Remove substitution variables that will contain the generated
2186- -- update and insert statements so that the existence of valid
2187- -- statements can be later tested based on the existence of these variables.
21882185 -- !x! rm_sub ~updatestmt
21892186
21902187 -- !x! sub ~do_updates Yes
Show on old repository browser