OSDN > Pesquisar Software > Database > Front-Ends > execsql-upsert > SCM > hg > execsql-upsert > Commit

execsql-upsert
Fork

execsql-upsert: Commit

Default repository for the execsql-upsert scripts

Commit MetaInfo

Revisão	ef2f84bc256914cafc24900a10ab6c687ee9c85f (tree)
Hora	2019-06-24 05:13:40
Autor	Dreas Nielsen <dreas.nielsen@gmai...>
Commiter	Dreas Nielsen

Mensagem de Log

PK UPSERT script completed for MariaDB/MySQL. All versions updated to 2.0.0.

Mudança Sumário

Diff

diff -r 5d867f6a5a68 -r ef2f84bc2569 test/md/test_pk_update.sql

--- /dev/null Thu Jan 01 00:00:00 1970 +0000

+++ b/test/md/test_pk_update.sql Sun Jun 23 13:13:40 2019 -0700

		@@ -0,0 +1,300 @@
	1	+-- test_pk_update.sql
	2	+--
	3	+-- PURPOSE
	4	+-- Test the UPDTPK_ONE script in md_upsert.sql
	5	+--
	6	+-- NOTES
	7	+-- 1.
	8	+--
	9	+-- AUTHOR
	10	+-- Dreas Nielsen (RDN)
	11	+--
	12	+-- HISTORY
	13	+-- Date Remarks
	14	+-- ---------- -----------------------------------------------------
	15	+-- 2019-06-16 Created. RDN.
	16	+-- ==================================================================
	17	+
	18	+-- !x! include ../../upsert/md_upsert.sql
	19	+
	20	+
	21	+
	22	+-- ==================================================================
	23	+-- Base Tables
	24	+-- Create base tables in the current database.
	25	+-- ==================================================================
	26	+
	27	+-- !x! if(table_exists(d_sampatt))
	28	+ drop table if exists d_sampatt cascade;
	29	+-- !x! endif
	30	+-- !x! if(table_exists(d_sampsplit))
	31	+ drop table if exists d_sampsplit cascade;
	32	+-- !x! endif
	33	+-- !x! if(table_exists(d_samploc))
	34	+ drop table if exists d_samploc cascade;
	35	+-- !x! endif
	36	+-- !x! if(table_exists(d_sample))
	37	+ drop table if exists d_sample cascade;
	38	+-- !x! endif
	39	+-- !x! if(table_exists(d_location))
	40	+ drop table if exists d_location cascade;
	41	+-- !x! endif
	42	+-- !x! if(table_exists(d_study))
	43	+ drop table if exists d_study cascade;
	44	+-- !x! endif
	45	+-- !x! if(table_exists(e_sampatt))
	46	+ drop table if exists e_sampatt cascade;
	47	+-- !x! endif
	48	+-- !x! if(table_exists(e_unit))
	49	+ drop table if exists e_unit cascade;
	50	+-- !x! endif
	51	+
	52	+create table e_unit (
	53	+ unit varchar(24) not null,
	54	+ description varchar(100) not null,
	55	+ constraint pk_eunit primary key (unit)
	56	+ );
	57	+
	58	+create table e_sampatt (
	59	+ attribute varchar(24) not null,
	60	+ description varchar(100) not null,
	61	+ constraint pk_esampatt primary key (attribute)
	62	+ );
	63	+
	64	+create table d_location (
	65	+ location varchar(60) not null,
	66	+ description varchar(200),
	67	+ constraint pk_location primary key (location)
	68	+ );
	69	+
	70	+create table d_study (
	71	+ study_id varchar(24) not null,
	72	+ study_name varchar(100) not null,
	73	+ constraint pk_study primary key (study_id)
	74	+ );
	75	+
	76	+-- The d_sample table has three single-column foreign keys,
	77	+-- two of them to the same table. Column names differ for
	78	+-- the foreign keys to the e_unit table.
	79	+create table d_sample (
	80	+ study_id varchar(24) not null,
	81	+ sample_id varchar(24) not null,
	82	+ volume double precision,
	83	+ volume_unit varchar(24),
	84	+ mass double precision,
	85	+ mass_unit varchar(24),
	86	+ constraint pk_sample primary key (study_id, sample_id),
	87	+ constraint fk_sampstudy foreign key (study_id)
	88	+ references d_study (study_id)
	89	+ on update cascade on delete cascade,
	90	+ constraint fk_sampvolunit foreign key (volume_unit)
	91	+ references e_unit (unit)
	92	+ on update cascade on delete restrict,
	93	+ constraint fk_sampmassunit foreign key (mass_unit)
	94	+ references e_unit (unit)
	95	+ on update cascade on delete restrict
	96	+ );
	97	+
	98	+create table d_samploc (
	99	+ study_id varchar(24) not null,
	100	+ sample_id varchar(24) not null,
	101	+ location varchar(60) not null,
	102	+ constraint pk_samploc primary key (study_id, sample_id, location),
	103	+ constraint fk_samploc_samp foreign key (study_id, sample_id)
	104	+ references d_sample (study_id, sample_id)
	105	+ on update cascade on delete cascade,
	106	+ constraint fk_samploc_loc foreign key (location)
	107	+ references d_location (location)
	108	+ on update cascade on delete cascade
	109	+ );
	110	+
	111	+-- The d_sampatt table has a single-column foreign key and
	112	+-- a two-column foreign key.
	113	+create table d_sampatt (
	114	+ study_id varchar(24) not null,
	115	+ sample_id varchar(24) not null,
	116	+ attribute varchar(24) not null,
	117	+ value varchar(100) not null,
	118	+ constraint pk_sampatt primary key (study_id, sample_id, attribute),
	119	+ constraint fk_sampattsamp foreign key (study_id, sample_id)
	120	+ references d_sample (study_id, sample_id)
	121	+ on update cascade on delete cascade,
	122	+ constraint fk_sampattatt foreign key (attribute)
	123	+ references test.e_sampatt (attribute)
	124	+ on update cascade on delete restrict
	125	+ );
	126	+
	127	+create table d_sampsplit (
	128	+ study_id varchar(24) not null,
	129	+ sample_id varchar(24) not null,
	130	+ sample_no varchar(24) not null,
	131	+ constraint pk_sampsplit primary key (study_id, sample_id, sample_no),
	132	+ constraint fk_parent foreign key (study_id, sample_id)
	133	+ references d_sample (study_id, sample_id)
	134	+ on update cascade on delete cascade
	135	+ );
	136	+
	137	+
	138	+-- ==================================================================
	139	+-- Populate the Base Tables
	140	+-- Define and use a script for repeatability.
	141	+-- ==================================================================
	142	+
	143	+-- !x! create script populate_base_tables
	144	+delete from d_sampsplit;
	145	+delete from d_sampatt;
	146	+delete from d_samploc;
	147	+delete from d_sample;
	148	+delete from d_study;
	149	+delete from d_location;
	150	+delete from e_sampatt;
	151	+delete from e_unit;
	152	+
	153	+insert into e_unit (unit, description)
	154	+values
	155	+ ('cm', 'Centimeters'),
	156	+ ('m', 'Meters'),
	157	+ ('in', 'Inches'),
	158	+ ('ft', 'Feet'),
	159	+ ('g', 'Grams'),
	160	+ ('kg', 'Kilograms'),
	161	+ ('mL', 'Milliliters'),
	162	+ ('L', 'Liters');
	163	+
	164	+insert into e_sampatt (attribute, description)
	165	+values
	166	+ ('color', 'Color'),
	167	+ ('odor', 'Odor'),
	168	+ ('NAPL', 'NAPL presence'),
	169	+ ('sheen', 'Sheen presence');
	170	+
	171	+insert into d_study (study_id, study_name)
	172	+values
	173	+ ('A', 'Study A'),
	174	+ ('B', 'Study B');
	175	+
	176	+insert into d_location (location, description)
	177	+values
	178	+ ('GR01', 'Station GR01'),
	179	+ ('SS25', 'Station SS25'),
	180	+ ('WC-013a', 'WC station 13a'),
	181	+ ('WC-013b', 'WC station 13b'),
	182	+ ('WC-013c', 'WC station 13c'),
	183	+ ('WC-017', 'WC station 18');
	184	+
	185	+insert into d_sample
	186	+ (study_id, sample_id, volume, volume_unit, mass, mass_unit)
	187	+values
	188	+ ('A', 'S001', null, null, 25.2, 'g'),
	189	+ ('A', 'S002', null, null, 81.0, 'g'),
	190	+ ('B', 'W001', 5, 'L', null, null),
	191	+ ('B', 'W002', 4.8, 'L', null, null);
	192	+
	193	+insert into d_samploc
	194	+ (study_id, sample_id, location)
	195	+values
	196	+ ('A', 'S001', 'GR01'),
	197	+ ('A', 'S002', 'SS25'),
	198	+ ('B', 'W001', 'WC-017'),
	199	+ ('B', 'W002', 'WC-013a'),
	200	+ ('B', 'W002', 'WC-013b');
	201	+
	202	+insert into d_sampatt
	203	+ (study_id, sample_id, attribute, value)
	204	+values
	205	+ ('A', 'S001', 'color', 'Brown'),
	206	+ ('A', 'S001', 'odor', 'Petroleum'),
	207	+ ('A', 'S001', 'sheen', 'Present');
	208	+
	209	+insert into d_sampsplit
	210	+ (study_id, sample_id, sample_no)
	211	+values
	212	+ ('A', 'S001', 'N001'),
	213	+ ('A', 'S001', 'N002'),
	214	+ ('A', 'S002', 'N003')
	215	+ ;
	216	+
	217	+--!x! end script populate_base_tables
	218	+
	219	+-- !x! execute script populate_base_tables
	220	+
	221	+
	222	+
	223	+
	224	+-- ==================================================================
	225	+-- Staging Tables
	226	+-- Create staging tables with a "stg_" prefix.
	227	+-- ==================================================================
	228	+
	229	+-- !x! if(table_exists(stg_d_sampatt ))
	230	+drop table if exists stg_d_sampatt cascade;
	231	+-- !x! endif
	232	+
	233	+
	234	+-- New primary keys for sample attributes.
	235	+create table stg_d_sampatt (
	236	+ study_id varchar(24),
	237	+ sample_id varchar(24),
	238	+ attribute varchar(24),
	239	+ new_study_id varchar(24),
	240	+ new_sample_id varchar(24),
	241	+ new_attribute varchar(24)
	242	+ );
	243	+insert into stg_d_sampatt
	244	+ (study_id, sample_id, attribute, new_study_id, new_sample_id, new_attribute)
	245	+values
	246	+ ('A', 'S001', 'color', 'B', 'W001', 'color'),
	247	+ ('A', 'S001', 'odor', 'B', 'W002', 'odor'),
	248	+ ('A', 'S001', 'sheen', 'A', 'S002', 'sheen')
	249	+ ;
	250	+
	251	+
	252	+
	253	+
	254	+-- ##################################################################
	255	+-- TESTING
	256	+-- ##################################################################
	257	+
	258	+-- !x! write ""
	259	+-- !x! write "Testing script UPDTPK_ONE."
	260	+
	261	+-- Substitutions common to all tests.
	262	+-- !x! sub log_sql Yes
	263	+-- !x! sub log_errors Yes
	264	+-- !x! sub log_changes Yes
	265	+-- !x! sub base_schema test
	266	+-- !x! sub staging staging
	267	+-- !x! sub display_errors Yes
	268	+-- !x! sub display_changes Yes
	269	+
	270	+
	271	+-- ------------------------------------------------------------------
	272	+-- Test with valid staging data.
	273	+-- ------------------------------------------------------------------
	274	+
	275	+---- !x! console on
	276	+---- !x! config console wait_when_done Yes
	277	+-- !x! write "Testing with valid data in the staging tables--this should succeed."
	278	+
	279	+-- Display the data before testing.
	280	+-- !x! write ""
	281	+-- !x! write "d_sampatt table before the update:"
	282	+-- !x! export d_sampatt to stdout as txt
	283	+
	284	+-- !x! write ""
	285	+-- !x! write "stg_d_sampatt table before the update:"
	286	+-- !x! export stg_d_sampatt to stdout as txt
	287	+-- !x! write ""
	288	+
	289	+-- !x! sub logfile test_updtpk_01.log
	290	+-- !x! rm_file !!logfile!!
	291	+
	292	+-- Check and load the data.
	293	+-- !x! autocommit off
	294	+-- !x! execute script updtpk_one with (stage_pfx=stg_, table=d_sampatt, display_errors=!!display_errors!!, display_changes=!!display_changes!!)
	295	+
	296	+-- Display the data after testing.
	297	+-- !x! write ""
	298	+-- !x! write "d_sampatt table after the update:"
	299	+-- !x! export d_sampatt to stdout as txt
	300	+

diff -r 5d867f6a5a68 -r ef2f84bc2569 upsert/md_upsert.sql

--- a/upsert/md_upsert.sql Mon Jun 03 19:33:08 2019 -0700

+++ b/upsert/md_upsert.sql Sun Jun 23 13:13:40 2019 -0700

		@@ -91,7 +91,7 @@
91	91	-- Elizabeth Shea (ES)
92	92	--
93	93	-- VERSION
94		--- 1.5.1
	94	+-- 2.0.0
95	95	--
96	96	-- HISTORY
97	97	-- Date Remarks

		@@ -138,6 +138,10 @@
138	138	-- 2019-03-15 Added VALIDATE_ONE and calls to it. RDN.
139	139	-- 2019-03-24 Added constraints to PKQA_ONE to limit to the
140	140	-- current database. RDN.
	141	+-- 2019-06-08 Began addition of PK updating scripts. Complete
	142	+-- through QA check 8. RDN.
	143	+-- 2019-06-16 Nominally complete revisions of PK updating scripts. RDN.
	144	+-- 2019-06-23 Completed debugging of PK updating scripts. RDN.
141	145	-- ==================================================================
142	146
143	147

		@@ -1989,3 +1993,981 @@
1989	1993	-- #################################################################
1990	1994
1991	1995
	1996	+
	1997	+
	1998	+-- ################################################################
	1999	+-- Script UPDTPK_ONE
	2000	+--
	2001	+-- Updates primary keys in the base table, based on new and existing
	2002	+-- values of PK columns in a staging table, using UPDATE
	2003	+-- statements. Displays data to be modified to the
	2004	+-- user before any modifications are done. Reports the changes
	2005	+-- made to the console and optionally to a log file.
	2006	+--
	2007	+-- Input parameters:
	2008	+-- stage_pfx : The prefix to the name of the staging table.
	2009	+-- table : The table name--same for base and staging,
	2010	+-- except for the prefix on the staging table.
	2011	+-- display_errors : A value of 'Yes' or 'No' to indicate whether
	2012	+-- any errors should be displayed in a GUI.
	2013	+-- display_changes : A value of 'Yes' or 'No' to indicate whether
	2014	+-- or not the changes to be made to the
	2015	+-- base table should be displayed in a GUI.
	2016	+--
	2017	+-- Global variables:
	2018	+-- logfile : The name of a log file to which update
	2019	+-- messages will be written. Optional.
	2020	+-- log_sql : A value of 'Yes' or 'No' indicating whether
	2021	+-- the update and insert statements should
	2022	+-- also be written to the logfile. Optional.
	2023	+-- log_changes : A value of 'Yes' or 'No' indicating whether
	2024	+-- the updated and inserted data should be
	2025	+-- written to the logfile. Optional.
	2026	+--
	2027	+-- Tables and views created or modified:
	2028	+-- ups_pkqa_errors : temporary table
	2029	+-- ups_pkcol_info : temporary table
	2030	+-- ups_pkupdates : temporary table
	2031	+-- ups_pkupdate_strings : temporary view
	2032	+-- ups_pkupdates : temporary table
	2033	+-- ===============================================================
	2034	+
	2035	+-- !x! BEGIN SCRIPT UPDTPK_ONE with parameters (stage_pfx, table, display_errors, display_changes)
	2036	+
	2037	+-- !x! if(console_on)
	2038	+ -- !x! console status "Primary key updates"
	2039	+-- !x! endif
	2040	+
	2041	+
	2042	+-- Validate inputs: base/staging schemas and table
	2043	+-- !x! execute script validate_one with args (stage_pfx=!!#stage_pfx!!, table=!!#table!!, script=!!$CURRENT_SCRIPT!!, script_line=!!$SCRIPT_LINE!!)
	2044	+
	2045	+-- Write an initial header to the logfile.
	2046	+-- !x! if(sub_defined(logfile))
	2047	+ -- !x! write "" to !!logfile!!
	2048	+ -- !x! write "==================================================================" to !!logfile!!
	2049	+ -- !x! write "!!$current_time!! -- Performing primary key updates on table !!#table!! from !!#stage_pfx!!!!#table!!" to !!logfile!!
	2050	+-- !x! endif
	2051	+
	2052	+-- !x! write "Performing primary key updates on table !!#table!! from !!#stage_pfx!!!!#table!!"
	2053	+
	2054	+-- Create a temp table to store the results of the PK update QA checks
	2055	+-- !x! if(table_exists(ups_pkqa_errors))
	2056	+ drop table if exists ups_pkqa_errors cascade;
	2057	+-- !x! endif
	2058	+create table ups_pkqa_errors (
	2059	+ error_code varchar(40),
	2060	+ error_description varchar(500)
	2061	+);
	2062	+
	2063	+
	2064	+-- Populate a (temporary) table with the names of the primary key columns of the base table.
	2065	+-- Get the old and new primary key columns from staging table into various formats
	2066	+-- to use later to construct SQL statement to select records in various ways for both updates and QA checks.
	2067	+-- Include column lists, join expression, and where clause
	2068	+-- !x! if(table_exists(ups_pkcol_info))
	2069	+ drop table if exists ups_pkcol_info cascade;
	2070	+-- !x! endif
	2071	+create table ups_pkcol_info
	2072	+select
	2073	+ k.table_schema,
	2074	+ k.table_name,
	2075	+ k.column_name,
	2076	+ cast(concat('b.', column_name) as varchar(2000)) as base_aliased,
	2077	+ cast(concat('s.', column_name) as varchar(2000)) as staging_aliased,
	2078	+ cast(concat('s.', column_name, ' as staging_', column_name) as varchar(2000)) as staging_aliased_prefix,
	2079	+ cast(concat('b.', column_name, ' = s.', column_name) as varchar(2000)) as join_expr,
	2080	+ cast(concat('new_', column_name) as varchar(2000)) as newpk_col,
	2081	+ cast(concat('s.new_', column_name) as varchar(2000)) as newpk_col_aliased,
	2082	+ cast(concat('new_', column_name, ' is null') as varchar(2000)) as newpk_col_empty,
	2083	+ cast(concat('new_', column_name, ' is not null') as varchar(2000)) as newpk_col_not_empty,
	2084	+ cast(concat('b.', column_name, ' = s.new_', column_name) as varchar(2000)) as assmt_expr,
	2085	+ cast(concat('b.', column_name, ' = s.new_', column_name) as varchar(2000)) as join_expr_oldnew,
	2086	+ cast(concat('s.new_', column_name, ' = b.new_', column_name) as varchar(2000)) as join_expr_new,
	2087	+ k.ordinal_position
	2088	+from information_schema.table_constraints as tc
	2089	+inner join information_schema.key_column_usage as k
	2090	+ on tc.constraint_type = 'PRIMARY KEY'
	2091	+ and tc.constraint_name = k.constraint_name
	2092	+ and tc.constraint_catalog = k.constraint_catalog
	2093	+ and tc.constraint_schema = k.constraint_schema
	2094	+ and tc.table_schema = k.table_schema
	2095	+ and tc.table_name = k.table_name
	2096	+ and tc.constraint_name = k.constraint_name
	2097	+where
	2098	+ k.table_name = '!!#table!!'
	2099	+ and k.table_schema = '!!$DB_NAME!!'
	2100	+;
	2101	+
	2102	+
	2103	+-- Run QA checks
	2104	+-- !x! execute script UPDTPKQA_ONE with arguments(stage_pfx=!!#stage_pfx!!, table=!!#table!!, pkinfo_table=ups_pkcol_info, qaerror_table=ups_pkqa_errors, display_errors=!!#display_errors!!)
	2105	+
	2106	+
	2107	+-- Run the PK update ONLY if QA check script returned no errors
	2108	+-- !x! if(not hasrows(ups_pkqa_errors))
	2109	+ -- !x! rm_sub ~updatestmt
	2110	+
	2111	+ -- !x! sub ~do_updates Yes
	2112	+
	2113	+ -- !x! if(sub_defined(logfile))
	2114	+ -- !x! write "" to !!logfile!!
	2115	+ -- !x! write "==================================================================" to !!logfile!!
	2116	+ -- !x! write "!!$current_time!! -- Performing primary key update on table !!#table!!" to !!logfile!!
	2117	+ -- !x! endif
	2118	+
	2119	+ -- !x! if(console_on)
	2120	+ -- !x! console status "Performing PK updates"
	2121	+ -- !x! console progress 0
	2122	+ -- !x! endif
	2123	+
	2124	+ -- !x! write "Performing primary key update on table !!#table!!"
	2125	+
	2126	+ -- Create strings necessary to construct SQL to perform the updates
	2127	+ -- !x! if(view_exists(ups_pkupdate_strings))
	2128	+ drop view if exists ups_pkupdate_strings cascade;
	2129	+ -- !x! endif
	2130	+ create view ups_pkupdate_strings as
	2131	+ select
	2132	+ group_concat(base_aliased order by ordinal_position separator ', ') as oldpk_cols,
	2133	+ group_concat(newpk_col order by ordinal_position separator ', ') as newpk_cols,
	2134	+ group_concat(join_expr order by ordinal_position separator ' and ') as joinexpr,
	2135	+ group_concat(newpk_col_not_empty order by ordinal_position separator ' and ') as all_newpk_col_not_empty,
	2136	+ group_concat(assmt_expr order by ordinal_position separator ', ') as assmt_expr
	2137	+ from ups_pkcol_info
	2138	+ group by table_name
	2139	+ ;
	2140	+ -- !x! select_sub ups_pkupdate_strings
	2141	+
	2142	+ -- Create a FROM clause for an inner join between base and staging
	2143	+ -- tables on the primary key column(s).
	2144	+ -- !x! sub ~fromclause FROM !!#table!! as b INNER JOIN !!#stage_pfx!!!!#table!! as s ON !!@joinexpr!!
	2145	+
	2146	+ -- Create a WHERE clause for the rows to include in the selection (only those having new PK columns populated in the staging table)
	2147	+ -- !x! sub ~whereclause WHERE !!@all_newpk_col_not_empty!!
	2148	+
	2149	+ -- Select all matches for PK update into temp table
	2150	+ -- !x! if(table_exists(ups_pkupdates))
	2151	+ drop table if exists ups_pkupdates cascade;
	2152	+ -- !x! endif
	2153	+ create table ups_pkupdates
	2154	+ select
	2155	+ !!@oldpk_cols!!,
	2156	+ !!@newpk_cols!!
	2157	+ !!~fromclause!!
	2158	+ !!~whereclause!!
	2159	+ ;
	2160	+
	2161	+ -- Prompt user to examine matching data and commit, don't commit, or quit.
	2162	+ -- !x! if(hasrows(ups_pkupdates))
	2163	+ -- !x! if(is_true(!!#display_changes!!))
	2164	+ -- !x! prompt ask "Do you want to make these changes to primary key values for table !!#table!!?" sub ~do_updates display ups_pkupdates
	2165	+ -- !x! endif
	2166	+ -- !x! if(is_true(!!~do_updates!!))
	2167	+
	2168	+ -- Create an UPDATE statement to update PK columns of the base table with
	2169	+ -- "new" PK columns from the staging table. No semicolon terminating generated SQL.
	2170	+ -- !x! sub ~updatestmt UPDATE !!#table!! as b, !!#stage_pfx!!!!#table!! as s SET !!@assmt_expr!! WHERE !!@joinexpr!! and !!@all_newpk_col_not_empty!!
	2171	+
	2172	+ -- !x! write "Updating !!#table!!"
	2173	+ -- !x! if(sub_defined(logfile))
	2174	+ -- !x! write "" to !!logfile!!
	2175	+ -- !x! if(sub_defined(log_sql))
	2176	+ -- !x! andif(is_true(!!log_sql!!))
	2177	+ -- !x! write "UPDATE statement for !!#table!!:" to !!logfile!!
	2178	+ -- !x! write [!!~updatestmt!!] to !!logfile!!
	2179	+ -- !x! endif
	2180	+ -- !x! if(sub_defined(log_changes))
	2181	+ -- !x! andif(is_true(!!log_changes!!))
	2182	+ -- !x! write "Updates:" to !!logfile!!
	2183	+ -- !x! export ups_pkupdates append to !!logfile!! as txt
	2184	+ -- !x! endif
	2185	+ -- !x! write "" to !!logfile!!
	2186	+ -- !x! endif
	2187	+ !!~updatestmt!!;
	2188	+ -- !x! if(sub_defined(logfile))
	2189	+ -- !x! write "!!$last_rowcount!! rows of !!#table!! updated." to !!logfile!!
	2190	+ -- !x! endif
	2191	+ -- !x! write " !!$last_rowcount!! rows updated."
	2192	+ -- !x! endif
	2193	+ -- !x! else
	2194	+ --!x! write "No primary key updates specified for existing records in !!#table!!"
	2195	+ -- !x! endif
	2196	+-- !x! endif
	2197	+
	2198	+
	2199	+-- !x! if(table_exists(ups_pkqa_errors))
	2200	+ drop table if exists ups_pkqa_errors cascade;
	2201	+-- !x! endif
	2202	+-- !x! if(table_exists(ups_pkcol_info))
	2203	+ drop table if exists ups_pkcol_info cascade;
	2204	+-- !x! endif
	2205	+-- !x! if(view_exists(ups_pkupdate_strings))
	2206	+ drop view if exists ups_pkupdate_strings cascade;
	2207	+-- !x! endif
	2208	+-- !x! if(table_exists(ups_pkupdates))
	2209	+ drop table if exists ups_pkupdates cascade;
	2210	+-- !x! endif
	2211	+
	2212	+
	2213	+-- !x! END SCRIPT
	2214	+-- ################### End of UPDTPK_ONE ########################
	2215	+-- ################################################################
	2216	+
	2217	+
	2218	+-- ################################################################
	2219	+-- Script UPDTPKQA_ONE
	2220	+--
	2221	+-- Performs QA checks on requested primary key updates to a table,
	2222	+-- based on old and new values of the table's primary key columns
	2223	+-- in a staging table.
	2224	+--
	2225	+-- Input parameters:
	2226	+-- stage_pfx : The prefix to the staging table name.
	2227	+-- table : The table name--same for base and staging,
	2228	+-- except for the prefix of the staging table.
	2229	+-- pkinfo_table : The name of a temporary table to be passed by
	2230	+-- the caller that contains information about the table PK,
	2231	+-- including strings to be used in constructing
	2232	+-- SQL for checks
	2233	+-- qaerror_table : The name of a temporary table to
	2234	+-- store any errors found by QA checks.
	2235	+-- display_errors : A value of 'Yes' or 'No' to indicate whether
	2236	+-- any errors should be displayed in a GUI.
	2237	+-- Output parameters:
	2238	+-- error_list : The name of the variable to receive FILL IN.
	2239	+--
	2240	+-- Global variables:
	2241	+-- logfile : The name of a log file to which update
	2242	+-- messages will be written. Optional.
	2243	+-- log_sql : A value of 'Yes' or 'No' indicating whether
	2244	+-- the update and insert statements should
	2245	+-- also be written to the logfile. Optional.
	2246	+-- Currently only writes SQL for foreign key checks
	2247	+-- (final check) to log.
	2248	+-- log_errors : A value of 'Yes' or 'No' to indicate whether
	2249	+-- errors are written to the logfile. Optional.
	2250	+--
	2251	+-- Tables and views created or modified:
	2252	+-- ups_missing_pk_cols : temporary table
	2253	+-- ups_pkqa_str_lib : tempoarary table
	2254	+-- ups_any_pk_cols : temporary table
	2255	+-- ups_empty_pk_cols : temporary table
	2256	+-- ups_empty_pk_cols_rwcnt : temporary view
	2257	+-- ups_old_pks_wc : temporary table
	2258	+-- ups_invalid_old_pks : temporary table
	2259	+-- ups_invld_pk_rwcnt : temporary view
	2260	+-- ups_existing_new_pks : temporary table
	2261	+-- ups_exst_nwpk_rwcnt : temporary view
	2262	+-- ups_pk_mapping_conflict : temporary table
	2263	+-- ups_map_conf_rwcnt : temporary view
	2264	+-- ups_pk_duplicate_keys : temporary table
	2265	+-- ups_dup_key_rwcnt : temporary view
	2266	+-- ups_fkcol_refs : temporary table
	2267	+-- ups_pkcol_deps : temporary table
	2268	+-- ups_pkfk_ctrl : temporary table
	2269	+-- ===============================================================
	2270	+
	2271	+-- !x! BEGIN SCRIPT UPDTPKQA_ONE with parameters (stage_pfx, table, pkinfo_table, qaerror_table, display_errors)
	2272	+
	2273	+-- Write an initial header to the logfile.
	2274	+-- !x! if(sub_defined(logfile))
	2275	+ -- !x! write "" to !!logfile!!
	2276	+ -- !x! write "==================================================================" to !!logfile!!
	2277	+ -- !x! write "!!$current_time!! -- QA checks for primary key updates on table !!#table!!" to !!logfile!!
	2278	+-- !x! endif
	2279	+
	2280	+-- !x! write "Conducting QA checks on table !!#stage_pfx!!!!#table!! for primary key updates to table !!#table!!"
	2281	+
	2282	+-- Initialize the status and progress bars if the console is running.
	2283	+-- !x! if(console_on)
	2284	+ -- !x! console status "QA checks for PK updates on !!#table!!"
	2285	+-- !x! endif
	2286	+
	2287	+
	2288	+-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2289	+-- Check 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2290	+-- No primary key constraint on base table
	2291	+-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2292	+-- !x! if(not hasrows(!!#pkinfo_table!!))
	2293	+
	2294	+ -- !x! sub ~error_description No primary key constraint on base table !!#table!!
	2295	+ -- !x! write " !!~error_description!!"
	2296	+ -- !x! if(sub_defined(logfile))
	2297	+ -- !x! write "" to !!logfile!!
	2298	+ -- !x! write "!!~error_description!!" to !!logfile!!
	2299	+ -- !x! endif
	2300	+ insert into !!#qaerror_table!! (error_code, error_description)
	2301	+ values ('No PK on base table', '!!~error_description!!')
	2302	+ ;
	2303	+
	2304	+-- No other QA checks are conducted if this check fails:
	2305	+-- Remaining QA checks are conducted ONLY if base table has PK
	2306	+-- !x! else
	2307	+
	2308	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2309	+ -- Check 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2310	+ -- A "new" PK column exists in staging table for every PK column of base table
	2311	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2312	+
	2313	+ -- Find any MISSING PK columns in staging table
	2314	+ -- !x! if(table_exists(ups_missing_pk_cols))
	2315	+ drop table if exists ups_missing_pk_cols cascade;
	2316	+ -- !x! endif
	2317	+ create table ups_missing_pk_cols
	2318	+ select
	2319	+ group_concat(newpk_col order by ordinal_position separator ', ') as missing_newpk_cols
	2320	+ from
	2321	+ --Base table PK columns, with expected name in staging table ("new_" prepended to column name)
	2322	+ !!#pkinfo_table!! as pk
	2323	+ --Staging table columns
	2324	+ left join
	2325	+ (
	2326	+ select table_name, column_name
	2327	+ from information_schema.columns
	2328	+ where
	2329	+ table_schema = '!!$DB_NAME!!'
	2330	+ and table_name = '!!#stage_pfx!!!!#table!!'
	2331	+ ) as stag on pk.newpk_col=stag.column_name
	2332	+ where
	2333	+ stag.column_name is null
	2334	+ having count(*)>0
	2335	+ ;
	2336	+
	2337	+ -- !x! if(hasrows(ups_missing_pk_cols))
	2338	+
	2339	+ -- !x! subdata ~error_info ups_missing_pk_cols
	2340	+
	2341	+ -- !x! sub ~error_description New primary key column(s) missing from staging table: !!~error_info!!
	2342	+
	2343	+ -- !x! write " !!~error_description!!"
	2344	+ -- !x! if(sub_defined(logfile))
	2345	+ -- !x! write "" to !!logfile!!
	2346	+ -- !x! write "!!~error_description!!" to !!logfile!!
	2347	+ -- !x! endif
	2348	+ insert into !!#qaerror_table!! (error_code, error_description)
	2349	+ values ('Missing new PK column(s)', '!!~error_description!!')
	2350	+ ;
	2351	+
	2352	+ -- No other QA checks are conducted if this check fails:
	2353	+ -- Remaining QA checks are all conducted ONLY if all expected "new PK" columns exist in staging table
	2354	+ -- !x! else
	2355	+
	2356	+ -- Library of aggregated strings used to construct SQL for the remaining checks
	2357	+
	2358	+ -- Just base table
	2359	+ -- !x! sub ~base_table !!#table!!
	2360	+
	2361	+ -- Just staging table
	2362	+ -- !x! sub ~staging_table !!#stage_pfx!!!!#table!!
	2363	+
	2364	+ -- !x! if(table_exists(ups_pkqa_str_lib))
	2365	+ drop table if exists ups_pkqa_str_lib;
	2366	+ -- !x! endif
	2367	+ create table ups_pkqa_str_lib
	2368	+ select
	2369	+ group_concat(column_name order by ordinal_position separator ', ') as old_pkcol,
	2370	+ group_concat(staging_aliased order by ordinal_position separator ', ') as old_pkcol_aliased,
	2371	+ group_concat(staging_aliased_prefix order by ordinal_position separator ', ') as old_pkcol_aliased_prefix,
	2372	+ group_concat(newpk_col order by ordinal_position separator ', ') as new_pkcol,
	2373	+ group_concat(newpk_col_aliased order by ordinal_position separator ', ') as new_pkcol_aliased,
	2374	+ group_concat(join_expr order by ordinal_position separator ' and ') as joincond_origorig,
	2375	+ group_concat(join_expr_oldnew order by ordinal_position separator ' and ') as joincond_oldnew,
	2376	+ group_concat(join_expr_new order by ordinal_position separator ' and ') as joincond_newnew,
	2377	+ group_concat(newpk_col_not_empty order by ordinal_position separator ' or ') as any_newpk_col_not_empty,
	2378	+ group_concat(newpk_col_not_empty order by ordinal_position separator ' and ') as all_newpk_col_not_empty,
	2379	+ group_concat(newpk_col_empty order by ordinal_position separator ' or ') as any_newpk_col_empty,
	2380	+ group_concat(newpk_col_empty order by ordinal_position separator ' and ') as all_newpk_col_empty
	2381	+ from !!#pkinfo_table!!
	2382	+ ;
	2383	+ -- !x! select_sub ups_pkqa_str_lib
	2384	+
	2385	+
	2386	+
	2387	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2388	+ -- Check 3 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2389	+ -- There are any rows with PK updates specified.
	2390	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2391	+
	2392	+ -- Find any populated new PK columns in staging table
	2393	+ -- !x! if(table_exists(ups_any_pk_cols))
	2394	+ drop table if exists ups_any_pk_cols cascade;
	2395	+ -- !x! endif
	2396	+ create table ups_any_pk_cols
	2397	+ select *
	2398	+ from !!~staging_table!!
	2399	+ where !!@any_newpk_col_not_empty!!
	2400	+ ;
	2401	+ -- !x! if(not hasrows(ups_any_pk_cols))
	2402	+ -- !x! sub ~error_description No primary key updates specified in !!#stage_pfx!!!!#table!!
	2403	+ -- !x! write " !!~error_description!!"
	2404	+ -- !x! if(sub_defined(logfile))
	2405	+ -- !x! write "" to !!logfile!!
	2406	+ -- !x! write "!!~error_description!!" to !!logfile!!
	2407	+ -- !x! endif
	2408	+ insert into !!#qaerror_table!! (error_code, error_description)
	2409	+ values ('No PK updates specified in staging table', '!!~error_description!!')
	2410	+ ;
	2411	+ -- No other QA checks are conducted if this check fails
	2412	+ -- !x! else
	2413	+
	2414	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2415	+ -- Check 4 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2416	+ -- Where any "new" PK column is populated in the staging table, they are all populated.
	2417	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2418	+
	2419	+ -- Construct SQL statement looking for any NULLs in "new" PK columns in rows where any PK columns are populated
	2420	+ -- Find any EMPTY PK columns in staging table
	2421	+ -- !x! if(table_exists(ups_empty_pk_cols))
	2422	+ drop table if exists ups_empty_pk_cols cascade;
	2423	+ -- !x! endif
	2424	+ create table ups_empty_pk_cols
	2425	+ select
	2426	+ !!@old_pkcol!!,
	2427	+ !!@new_pkcol!!
	2428	+ from
	2429	+ !!~staging_table!!
	2430	+ where
	2431	+ not (!!@all_newpk_col_empty!!)
	2432	+ and (!!@any_newpk_col_empty!!)
	2433	+ ;
	2434	+
	2435	+ -- !x! if(hasrows(ups_empty_pk_cols))
	2436	+ -- !x! if(view_exists(ups_empty_pk_cols_rwcnt))
	2437	+ drop view if exists ups_empty_pk_cols_rwcnt cascade;
	2438	+ -- !x! endif
	2439	+ create view ups_empty_pk_cols_rwcnt as
	2440	+ select count(*) as rwcnt
	2441	+ from ups_empty_pk_cols
	2442	+ ;
	2443	+ -- !x! subdata ~rowcount ups_empty_pk_cols_rwcnt
	2444	+ -- !x! sub ~error_description Missing values in new PK columns in !!#stage_pfx!!!!#table!!: !!~rowcount!! row(s)
	2445	+ -- !x! write " !!~error_description!!"
	2446	+ insert into !!#qaerror_table!! (error_code, error_description)
	2447	+ values ('Incomplete mapping', '!!~error_description!!')
	2448	+ ;
	2449	+ -- !x! if(sub_defined(logfile))
	2450	+ -- !x! write "" to !!logfile!!
	2451	+ -- !x! write "!!~error_description!!" to !!logfile!!
	2452	+ -- !x! if(sub_defined(log_errors))
	2453	+ -- !x! andif(is_true(!!log_errors!!))
	2454	+ -- !x! export ups_empty_pk_cols append to !!logfile!! as txt
	2455	+ -- !x! endif
	2456	+ -- !x! endif
	2457	+ -- !x! if(is_true(!!#display_errors!!))
	2458	+ -- !x! prompt message "Missing values in new PK columns in !!#stage_pfx!!!!#table!!" display ups_empty_pk_cols
	2459	+ -- !x! endif
	2460	+ -- !x! endif
	2461	+
	2462	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2463	+ -- Check 5 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2464	+ -- Where any "new" PK column is populated in the staging table, the value of the original PK for that row is valid
	2465	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2466	+
	2467	+ -- New PK col in staging table are not empty
	2468	+ -- !x! if(table_exists(ups_old_pks_wc))
	2469	+ drop table if exists ups_old_pks_wc cascade;
	2470	+ -- !x! endif
	2471	+ create table ups_old_pks_wc
	2472	+ select base_aliased
	2473	+ from !!#pkinfo_table!!
	2474	+ order by ordinal_position
	2475	+ limit 1;
	2476	+ -- !x! subdata ~old_pk_firstcol ups_old_pks_wc
	2477	+
	2478	+ -- !x! if(table_exists(ups_invalid_old_pks))
	2479	+ drop table if exists ups_invalid_old_pks cascade;
	2480	+ -- !x! endif
	2481	+ create table ups_invalid_old_pks
	2482	+ select
	2483	+ !!@old_pkcol_aliased!!,
	2484	+ !!@new_pkcol!!
	2485	+ from !!~staging_table!! as s
	2486	+ left join !!~base_table!! as b on !!@joincond_origorig!!
	2487	+ where !!@all_newpk_col_not_empty!! and !!~old_pk_firstcol!! is null
	2488	+ ;
	2489	+
	2490	+ -- !x! if(hasrows(ups_invalid_old_pks))
	2491	+ -- !x! if(view_exists(ups_invalid_pk_rwcnt))
	2492	+ drop view if exists ups_invld_pk_rwcnt cascade;
	2493	+ -- !x! endif
	2494	+ create view ups_invld_pk_rwcnt as
	2495	+ select count(*) as rwcnt
	2496	+ from ups_invalid_old_pks
	2497	+ ;
	2498	+ -- !x! subdata ~rowcount ups_invld_pk_rwcnt
	2499	+ -- !x! sub ~error_description Invalid original PK in !!#stage_pfx!!!!#table!!: !!~rowcount!! row(s)
	2500	+ -- !x! write " !!~error_description!!"
	2501	+ insert into !!#qaerror_table!! (error_code, error_description)
	2502	+ values ('Invalid old PK value', '!!~error_description!!')
	2503	+ ;
	2504	+ -- !x! if(sub_defined(logfile))
	2505	+ -- !x! write "" to !!logfile!!
	2506	+ -- !x! write "!!~error_description!!" to !!logfile!!
	2507	+ -- !x! if(sub_defined(log_errors))
	2508	+ -- !x! andif(is_true(!!log_errors!!))
	2509	+ -- !x! export ups_invalid_old_pks append to !!logfile!! as txt
	2510	+ -- !x! endif
	2511	+ -- !x! endif
	2512	+ -- !x! if(is_true(!!#display_errors!!))
	2513	+ -- !x! prompt message "Invalid original PK in !!#stage_pfx!!!!#table!!" display ups_invalid_old_pks
	2514	+ -- !x! endif
	2515	+ -- !x! endif
	2516	+
	2517	+
	2518	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2519	+ -- Check 6 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2520	+ -- None of the "new" PK values already exist in the base table
	2521	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2522	+
	2523	+ -- !x! if(table_exists(ups_existing_new_pks))
	2524	+ drop table if exists ups_existing_new_pks cascade;
	2525	+ -- !x! endif
	2526	+ create table ups_existing_new_pks
	2527	+ select
	2528	+ !!@old_pkcol_aliased_prefix!!,
	2529	+ !!@new_pkcol!!,
	2530	+ b.*
	2531	+ from !!~staging_table!! as s
	2532	+ inner join !!~base_table!! as b on !!@joincond_oldnew!!
	2533	+ ;
	2534	+
	2535	+ -- !x! if(hasrows(ups_existing_new_pks))
	2536	+ -- !x! if(view_exists(ups_exst_nwpk_rwcnt))
	2537	+ drop view if exists ups_exst_nwpk_rwcnt cascade;
	2538	+ -- !x! endif
	2539	+ create view ups_exst_nwpk_rwcnt as
	2540	+ select count(*) as rwcnt
	2541	+ from ups_existing_new_pks
	2542	+ ;
	2543	+ -- !x! subdata ~rowcount ups_exst_nwpk_rwcnt
	2544	+ -- !x! sub ~error_description New PK already exists in !!#table!!: !!~rowcount!! row(s)
	2545	+ -- !x! write " !!~error_description!!"
	2546	+ insert into !!#qaerror_table!! (error_code, error_description)
	2547	+ values ('Existing new PK value', '!!~error_description!!')
	2548	+ ;
	2549	+ -- !x! if(sub_defined(logfile))
	2550	+ -- !x! write "" to !!logfile!!
	2551	+ -- !x! write "!!~error_description!!" to !!logfile!!
	2552	+ -- !x! if(sub_defined(log_errors))
	2553	+ -- !x! andif(is_true(!!log_errors!!))
	2554	+ -- !x! export ups_existing_new_pks append to !!logfile!! as txt
	2555	+ -- !x! endif
	2556	+ -- !x! endif
	2557	+ -- !x! if(is_true(!!#display_errors!!))
	2558	+ -- !x! prompt message "New PK already exists in !!#table!!" display ups_existing_new_pks
	2559	+ -- !x! endif
	2560	+ -- !x! endif
	2561	+
	2562	+
	2563	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2564	+ -- Check 7 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2565	+ -- No two (or more) original PK values map to same new PK value
	2566	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2567	+
	2568	+ -- !x! if(table_exists(ups_pk_mapping_conflict))
	2569	+ drop table if exists ups_pk_mapping_conflict cascade;
	2570	+ -- !x! endif
	2571	+ create table ups_pk_mapping_conflict
	2572	+ select
	2573	+ !!@old_pkcol_aliased!!,
	2574	+ !!@new_pkcol_aliased!!
	2575	+ from !!~staging_table!! as s
	2576	+ inner join
	2577	+ (
	2578	+ select
	2579	+ !!@new_pkcol!!
	2580	+ from
	2581	+ (select distinct !!@old_pkcol!!, !!@new_pkcol!! from !!~staging_table!! where !!@all_newpk_col_not_empty!!) as a
	2582	+ group by
	2583	+ !!@new_pkcol!!
	2584	+ having count(*) >1
	2585	+ ) as b on !!@joincond_newnew!!
	2586	+ ;
	2587	+
	2588	+ -- !x! if(hasrows(ups_pk_mapping_conflict))
	2589	+ -- !x! if(view_exists(ups_map_conf_rwcnt))
	2590	+ drop view if exists ups_map_conf_rwcnt cascade;
	2591	+ -- !x! endif
	2592	+ create view ups_map_conf_rwcnt as
	2593	+ select count(*) as rwcnt
	2594	+ from ups_pk_mapping_conflict
	2595	+ ;
	2596	+ -- !x! subdata ~rowcount ups_map_conf_rwcnt
	2597	+ -- !x! sub ~error_description Multiple original PKs mapped to same new PK in !!#stage_pfx!!!!#table!!: !!~rowcount!! row(s)
	2598	+ -- !x! write " !!~error_description!!"
	2599	+ insert into !!#qaerror_table!! (error_code, error_description)
	2600	+ values ('Mapping conflict', '!!~error_description!!')
	2601	+ ;
	2602	+ -- !x! if(sub_defined(logfile))
	2603	+ -- !x! write "" to !!logfile!!
	2604	+ -- !x! write "!!~error_description!!" to !!logfile!!
	2605	+ -- !x! if(sub_defined(log_errors))
	2606	+ -- !x! andif(is_true(!!log_errors!!))
	2607	+ -- !x! export ups_pk_mapping_conflict append to !!logfile!! as txt
	2608	+ -- !x! endif
	2609	+ -- !x! endif
	2610	+ -- !x! if(is_true(!!#display_errors!!))
	2611	+ -- !x! prompt message "Multiple original PKs mapped to same new PK in !!#stage_pfx!!!!#table!!" display ups_pk_mapping_conflict
	2612	+ -- !x! endif
	2613	+ -- !x! endif
	2614	+
	2615	+
	2616	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2617	+ -- Check 8 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2618	+ -- No single original PK value maps to multiple new PK values
	2619	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2620	+
	2621	+ -- !x! if(table_exists(ups_pk_duplicate_keys))
	2622	+ drop table if exists ups_pk_duplicate_keys cascade;
	2623	+ -- !x! endif
	2624	+ create table ups_pk_duplicate_keys
	2625	+ select
	2626	+ !!@old_pkcol_aliased!!,
	2627	+ !!@new_pkcol_aliased!!
	2628	+ from !!~staging_table!! as s
	2629	+ inner join
	2630	+ (
	2631	+ select
	2632	+ !!@old_pkcol!!
	2633	+ from
	2634	+ (select distinct !!@old_pkcol!!, !!@new_pkcol!! from !!~staging_table!! where !!@all_newpk_col_not_empty!!) as a
	2635	+ group by
	2636	+ !!@old_pkcol!!
	2637	+ having count(*)>1
	2638	+ ) as b on !!@joincond_origorig!!
	2639	+ ;
	2640	+
	2641	+ -- !x! if(hasrows(ups_pk_duplicate_keys))
	2642	+ -- !x! if(view_exists(ups_dup_key_rwcnt))
	2643	+ drop view if exists ups_dup_key_rwcnt cascade;
	2644	+ -- !x! endif
	2645	+ create view ups_dup_key_rwcnt as
	2646	+ select count(*) as rwcnt
	2647	+ from ups_pk_duplicate_keys
	2648	+ ;
	2649	+ -- !x! subdata ~rowcount ups_dup_key_rwcnt
	2650	+ -- !x! sub ~error_description Original PK mapped to multiple new PKs in !!#stage_pfx!!!!#table!!: !!~rowcount!! row(s)
	2651	+ -- !x! write " !!~error_description!!"
	2652	+ insert into !!#qaerror_table!! (error_code, error_description)
	2653	+ values ('Duplicate keys', '!!~error_description!!')
	2654	+ ;
	2655	+ -- !x! if(sub_defined(logfile))
	2656	+ -- !x! write "" to !!logfile!!
	2657	+ -- !x! write "!!~error_description!!" to !!logfile!!
	2658	+ -- !x! if(sub_defined(log_errors))
	2659	+ -- !x! andif(is_true(!!log_errors!!))
	2660	+ -- !x! export ups_pk_duplicate_keys append to !!logfile!! as txt
	2661	+ -- !x! endif
	2662	+ -- !x! endif
	2663	+ -- !x! if(is_true(!!#display_errors!!))
	2664	+ -- !x! prompt message "Original PK mapped to multiple new PKs in !!#stage_pfx!!!!#table!!" display ups_pk_duplicate_keys
	2665	+ -- !x! endif
	2666	+ -- !x! endif
	2667	+
	2668	+
	2669	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2670	+ -- Check 9 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2671	+ -- If any of the PK columns reference a parent table, all the "new" values of that column are valid
	2672	+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
	2673	+
	2674	+ -- Get ALL foreign key column references for the base table
	2675	+ -- !x! if(table_exists(ups_fkcol_refs))
	2676	+ drop table if exists ups_fkcol_refs cascade;
	2677	+ -- !x! endif
	2678	+ create table ups_fkcol_refs
	2679	+ select
	2680	+ rc.constraint_name as fk_constraint,
	2681	+ cu.table_schema,
	2682	+ cu.table_name,
	2683	+ cu.column_name,
	2684	+ cu.ordinal_position,
	2685	+ cu_uq.table_schema as parent_schema,
	2686	+ cu_uq.table_name as parent_table,
	2687	+ cu_uq.column_name as parent_column,
	2688	+ cu_uq.ordinal_position as parent_position
	2689	+ from
	2690	+ (select constraint_catalog, constraint_schema, constraint_name,
	2691	+ table_name,
	2692	+ unique_constraint_catalog, unique_constraint_schema, unique_constraint_name,
	2693	+ referenced_table_name
	2694	+ from information_schema.referential_constraints
	2695	+ where constraint_schema = '!!$db_name!!'
	2696	+ ) as rc
	2697	+ inner join (select * from information_schema.table_constraints
	2698	+ where constraint_type = 'FOREIGN KEY' and constraint_schema = '!!$db_name!!'
	2699	+ ) as tc
	2700	+ on tc.constraint_catalog = rc.constraint_catalog
	2701	+ and tc.constraint_schema = rc.constraint_schema
	2702	+ and tc.constraint_name = rc.constraint_name
	2703	+ and tc.table_name = rc.table_name
	2704	+ inner join (select * from information_schema.table_constraints
	2705	+ where constraint_type not in ('FOREIGN KEY', 'CHECK')
	2706	+ and constraint_schema = '!!$db_name!!'
	2707	+ ) as tc_uq
	2708	+ on tc_uq.constraint_catalog = rc.unique_constraint_catalog
	2709	+ and tc_uq.constraint_schema = rc.unique_constraint_schema
	2710	+ and tc_uq.constraint_name = rc.unique_constraint_name
	2711	+ and tc_uq.table_name = rc.referenced_table_name
	2712	+ inner join information_schema.key_column_usage as cu
	2713	+ on cu.constraint_catalog = tc.constraint_catalog
	2714	+ and cu.constraint_schema = tc.constraint_schema
	2715	+ and cu.constraint_name = tc.constraint_name
	2716	+ and cu.table_schema = tc.table_schema
	2717	+ and cu.table_name = tc.table_name
	2718	+ inner join information_schema.key_column_usage as cu_uq
	2719	+ on cu_uq.constraint_catalog = tc_uq.constraint_catalog
	2720	+ and cu_uq.constraint_schema = tc_uq.constraint_schema
	2721	+ and cu_uq.constraint_name = tc_uq.constraint_name
	2722	+ and cu_uq.table_schema = tc_uq.table_schema
	2723	+ and cu_uq.table_name = tc_uq.table_name
	2724	+ and cu_uq.ordinal_position = cu.ordinal_position
	2725	+ where
	2726	+ rc.table_name = '!!#table!!'
	2727	+ ;
	2728	+
	2729	+ -- Narrow the list down to ONLY dependencies that affect PK columns
	2730	+ -- Include not just the PK columns themselves, but ALL columns included in FKs
	2731	+ -- that include ANY PK columns (probably rare/unlikely that a non-PK column would be
	2732	+ -- part of the same foreign key as a PK column, but this ensures that ALL columns of the FK
	2733	+ -- are included, whether or not the column is part of the PK)
	2734	+ -- !x! if(table_exists(ups_pkcol_deps))
	2735	+ drop table if exists ups_pkcol_deps cascade;
	2736	+ -- !x! endif
	2737	+ create table ups_pkcol_deps
	2738	+ select
	2739	+ refs.*
	2740	+ from
	2741	+ ups_fkcol_refs as refs
	2742	+ inner join
	2743	+ --Distinct list of FK constraints on the table that include ANY PK columns
	2744	+ (
	2745	+ select distinct
	2746	+ fk_constraint, r.table_schema, r.table_name
	2747	+ from
	2748	+ ups_fkcol_refs as r
	2749	+ inner join ups_pkcol_info as p on r.table_schema=p.table_schema and r.table_name=p.table_name and r.column_name=p.column_name
	2750	+ ) as const on refs.fk_constraint=const.fk_constraint and refs.table_schema=const.table_schema and refs.table_name=const.table_name
	2751	+ ;
	2752	+
	2753	+ -- Create a control table for looping to check each fk
	2754	+ -- Include (for later use) some of the constructed strings that apply to the entire PK (not
	2755	+ -- just the FK being checked)
	2756	+ -- !x! if(table_exists(ups_pkfk_ctrl))
	2757	+ drop table if exists ups_pkfk_ctrl cascade;
	2758	+ -- !x! endif
	2759	+ create table ups_pkfk_ctrl
	2760	+ select
	2761	+ fk_constraint,
	2762	+ table_name, parent_table,
	2763	+ min(parent_column) as any_referenced_column,
	2764	+ '!!@old_pkcol_aliased!!' as old_pkcol_aliased,
	2765	+ '!!@new_pkcol!!' as new_pkcol,
	2766	+ '!!@all_newpk_col_not_empty!!' as all_newpk_col_not_empty,
	2767	+ False as processed
	2768	+ from ups_pkcol_deps
	2769	+ group by
	2770	+ fk_constraint, table_name, parent_table
	2771	+ ;
	2772	+
	2773	+ -- Create a view to select one constraint to process.
	2774	+ -- !x! if(view_exists(ups_next_fk))
	2775	+ drop view if exists ups_next_fk cascade;
	2776	+ -- !x! endif
	2777	+ create view ups_next_fk as
	2778	+ select *
	2779	+ from ups_pkfk_ctrl
	2780	+ where not processed
	2781	+ limit 1
	2782	+ ;
	2783	+
	2784	+ --Process all constraints: check every foreign key
	2785	+ --!x! execute script updtpkqa_one_innerloop with (stage_pfx=!!#stage_pfx!!, qaerror_table=!!#qaerror_table!!, display_errors=!!#display_errors!!)
	2786	+ -- !x! endif
	2787	+ -- !x! endif
	2788	+-- !x! endif
	2789	+
	2790	+-- !x! if(table_exists(ups_missing_pk_cols))
	2791	+ drop table if exists ups_missing_pk_cols cascade;
	2792	+-- !x! endif
	2793	+-- !x! if(table_exists(ups_pkqa_str_lib))
	2794	+ drop table if exists ups_pkqa_str_lib;
	2795	+-- !x! endif
	2796	+-- !x! if(table_exists(ups_any_pk_cols))
	2797	+ drop table if exists ups_any_pk_cols cascade;
	2798	+-- !x! endif
	2799	+-- !x! if(table_exists(ups_empty_pk_cols))
	2800	+ drop table if exists ups_empty_pk_cols cascade;
	2801	+-- !x! endif
	2802	+-- !x! if(view_exists(ups_empty_pk_cols_rwcnt))
	2803	+ drop view if exists ups_empty_pk_cols_rwcnt cascade;
	2804	+-- !x! endif
	2805	+-- !x! if(table_exists(ups_old_pks_wc))
	2806	+ drop table if exists ups_old_pks_wc cascade;
	2807	+-- !x! endif
	2808	+-- !x! if(table_exists(ups_invalid_old_pks))
	2809	+ drop table if exists ups_invalid_old_pks cascade;
	2810	+-- !x! endif
	2811	+-- !x! if(view_exists(ups_invalid_pk_rwcnt))
	2812	+ drop view if exists ups_invld_pk_rwcnt cascade;
	2813	+-- !x! endif
	2814	+-- !x! if(table_exists(ups_existing_new_pks))
	2815	+ drop table if exists ups_existing_new_pks cascade;
	2816	+-- !x! endif
	2817	+-- !x! if(view_exists(ups_exst_nwpk_rwcnt))
	2818	+ drop view if exists ups_exst_nwpk_rwcnt cascade;
	2819	+-- !x! endif
	2820	+-- !x! if(table_exists(ups_pk_mapping_conflict))
	2821	+ drop table if exists ups_pk_mapping_conflict cascade;
	2822	+-- !x! endif
	2823	+-- !x! if(view_exists(ups_map_conf_rwcnt))
	2824	+ drop view if exists ups_map_conf_rwcnt cascade;
	2825	+-- !x! endif
	2826	+-- !x! if(table_exists(ups_pk_duplicate_keys))
	2827	+ drop table if exists ups_pk_duplicate_keys cascade;
	2828	+-- !x! endif
	2829	+-- !x! if(view_exists(ups_dup_key_rwcnt))
	2830	+ drop view if exists ups_dup_key_rwcnt cascade;
	2831	+-- !x! endif
	2832	+-- !x! if(table_exists(ups_fkcol_refs))
	2833	+ drop table if exists ups_fkcol_refs cascade;
	2834	+-- !x! endif
	2835	+-- !x! if(table_exists(ups_pkcol_deps))
	2836	+ drop table if exists ups_pkcol_deps cascade;
	2837	+-- !x! endif
	2838	+-- !x! if(table_exists(ups_pkfk_ctrl))
	2839	+ drop table if exists ups_pkfk_ctrl cascade;
	2840	+-- !x! endif
	2841	+-- !x! if(view_exists(ups_next_fk))
	2842	+ drop view if exists ups_next_fk cascade;
	2843	+-- !x! endif
	2844	+
	2845	+-- !x! END SCRIPT
	2846	+-- ################### UPDTPKQA_ONE ########################
	2847	+-- ################################################################
	2848	+-- Script UPDTPKQA_ONE_INNERLOOP
	2849	+-- ----------------------------------------------------------------
	2850	+-- !x! BEGIN SCRIPT UPDTPKQA_ONE_INNERLOOP with parameters(stage_pfx, qaerror_table, display_errors)
	2851	+-- !x! if(hasrows(ups_next_fk))
	2852	+
	2853	+ -- !x! select_sub ups_next_fk
	2854	+
	2855	+ -- Compile FK info for the selected constraint
	2856	+ -- !x! if(table_exists(ups_sel_fk_cols))
	2857	+ drop table if exists ups_sel_fk_cols cascade;
	2858	+ -- !x! endif
	2859	+ create table ups_sel_fk_cols
	2860	+ select
	2861	+ fk_constraint, table_name,
	2862	+ parent_table,
	2863	+ group_concat(parent_column order by column_name separator ', ') as referenced_cols,
	2864	+ group_concat('s.new_' \|\| column_name \|\| '=' \|\| 'b.' \|\| parent_column order by column_name separator ' and ') as join_condition
	2865	+ from ups_pkcol_deps
	2866	+ where fk_constraint='!!@fk_constraint!!'
	2867	+ group by
	2868	+ fk_constraint, table_name,
	2869	+ parent_table
	2870	+ ;
	2871	+ -- !x! select_sub ups_sel_fk_cols
	2872	+
	2873	+ -- Construct SQL to check the selected FK
	2874	+ -- !x! sub ~select_stmt create table ups_pk_fk_check select !!@old_pkcol_aliased!!, !!@new_pkcol!! from !!#stage_pfx!!!!@table_name!! as s
	2875	+ -- !x! sub ~join_stmt left join !!@parent_table!! as b on !!@join_condition!!
	2876	+ -- !x! sub ~where_clause where !!@all_newpk_col_not_empty!! and b.!!@any_referenced_column!! is null
	2877	+
	2878	+ -- !x! sub ~fk_check !!~select_stmt!!
	2879	+ -- !x! sub_append ~fk_check !!~join_stmt!!
	2880	+ -- !x! sub_append ~fk_check !!~where_clause!!
	2881	+
	2882	+ -- Write the SQL to the log file if requested.
	2883	+ -- !x! if(sub_defined(logfile))
	2884	+ -- !x! andif(sub_defined(log_sql))
	2885	+ -- !x! andif(is_true(!!log_sql!!))
	2886	+ -- !x! write "" to !!logfile!!
	2887	+ -- !x! write "SQL for checking foreign key !!@fk_constraint!! for PK update to !!@table_name!!:" to !!logfile!!
	2888	+ -- !x! write [!!~fk_check!!] to !!logfile!!
	2889	+ -- !x! endif
	2890	+
	2891	+ -- Run the check
	2892	+ -- !x! if(table_exists(ups_pk_fk_check))
	2893	+ drop table if exists ups_pk_fk_check cascade;
	2894	+ -- !x! endif
	2895	+
	2896	+ !!~fk_check!!;
	2897	+
	2898	+ -- !x! if(hasrows(ups_pk_fk_check))
	2899	+
	2900	+ -- !x! if(view_exists(ups_pk_fk_check_rwcnt))
	2901	+ drop view if exists ups_pk_fk_check_rwcnt cascade;
	2902	+ -- !x! endif
	2903	+ create or replace view ups_pk_fk_check_rwcnt as
	2904	+ select count(*) as rwcnt
	2905	+ from ups_pk_fk_check
	2906	+ ;
	2907	+
	2908	+ -- !x! subdata ~rowcount ups_pk_fk_check_rwcnt
	2909	+ -- !x! sub ~error_description !!@parent_table!! (!!@referenced_cols!!): !!~rowcount!! row(s)
	2910	+
	2911	+ -- !x! write " Violation of foreign key !!@fk_constraint!! in new primary key columns in !!@stag_pfx!!!!@table_name!! referencing !!@parent_table!!: !!~rowcount!! row(s)"
	2912	+
	2913	+ -- !x! if(view_exists(pk_fk_qa_error))
	2914	+ drop view if exists ups_pk_fk_qa_error cascade;
	2915	+ -- !x! endif
	2916	+ create or replace view ups_pk_fk_qa_error as
	2917	+ select
	2918	+ error_code, error_description
	2919	+ from !!#qaerror_table!!
	2920	+ where error_code='Invalid reference to parent table(s)';
	2921	+ -- !x! if(hasrows(ups_pk_fk_qa_error))
	2922	+ update !!#qaerror_table!!
	2923	+ set error_description=error_description \|\| '; !!~error_description!!'
	2924	+ where error_code='Invalid reference to parent table(s)';
	2925	+
	2926	+ -- !x! else
	2927	+ insert into !!#qaerror_table!! (error_code, error_description)
	2928	+ values ('Invalid reference to parent table(s)', '!!~error_description!!')
	2929	+ ;
	2930	+ -- !x! endif
	2931	+
	2932	+
	2933	+ -- !x! if(sub_defined(logfile))
	2934	+ -- !x! write "" to !!logfile!!
	2935	+ -- !x! write "Violation of foreign key !!@fk_constraint!! in new primary key columns in !!@stage_pfx!!!!@table_name!! referencing !!@parent_table!!: !!~rowcount!! row(s)" to !!logfile!!
	2936	+ -- !x! if(sub_defined(log_errors))
	2937	+ -- !x! andif(is_true(!!log_errors!!))
	2938	+ -- !x! export ups_pk_fk_check append to !!logfile!! as txt
	2939	+ -- !x! endif
	2940	+ -- !x! endif
	2941	+ -- !x! if(is_true(!!#display_errors!!))
	2942	+ -- !x! prompt message "Violation of foreign key !!@fk_constraint!! in new primary key columns in !!@stage_pfx!!!!@table_name!! referencing !!@parent_table!!" display ups_pk_fk_check
	2943	+ -- !x! endif
	2944	+
	2945	+ -- !x! endif
	2946	+
	2947	+ -- Mark constraint as processed
	2948	+ update ups_pkfk_ctrl
	2949	+ set processed=True
	2950	+ where fk_constraint='!!@fk_constraint!!';
	2951	+
	2952	+ -- !x! if(table_exists(ups_sel_fk_cols))
	2953	+ drop table if exists ups_sel_fk_cols cascade;
	2954	+ -- !x! endif
	2955	+ -- !x! if(table_exists(ups_pk_fk_check))
	2956	+ drop table if exists ups_pk_fk_check cascade;
	2957	+ -- !x! endif
	2958	+ -- !x! if(view_exists(ups_pk_fk_check_rwcnt))
	2959	+ drop view if exists ups_pk_fk_check_rwcnt cascade;
	2960	+ -- !x! endif
	2961	+ -- !x! if(view_exists(pk_fk_qa_error))
	2962	+ drop view if exists ups_pk_fk_qa_error cascade;
	2963	+ -- !x! endif
	2964	+
	2965	+ --LOOP
	2966	+ -- !x! execute script updtpkqa_one_innerloop with (stage_pfx=!!#stage_pfx!!, qaerror_table=!!#qaerror_table!!, display_errors=!!#display_errors!!)
	2967	+
	2968	+-- !x! endif
	2969	+
	2970	+-- !x! END SCRIPT
	2971	+-- #################### End of UPDTPKQA_ONE ########################
	2972	+-- ################################################################
	2973	+

diff -r 5d867f6a5a68 -r ef2f84bc2569 upsert/pg_upsert.sql

--- a/upsert/pg_upsert.sql Mon Jun 03 19:33:08 2019 -0700

+++ b/upsert/pg_upsert.sql Sun Jun 23 13:13:40 2019 -0700

		@@ -88,7 +88,7 @@
88	88	-- Tom Schulz (TS)
89	89	--
90	90	-- VERSION
91		--- 1.4.0
	91	+-- 2.0.0
92	92	--
93	93	-- HISTORY
94	94	-- Date Remarks

diff -r 5d867f6a5a68 -r ef2f84bc2569 upsert/ss_upsert.sql

--- a/upsert/ss_upsert.sql Mon Jun 03 19:33:08 2019 -0700

+++ b/upsert/ss_upsert.sql Sun Jun 23 13:13:40 2019 -0700

		@@ -92,7 +92,7 @@
92	92	-- Dreas Nielsen (RDN)
93	93	--
94	94	-- VERSION
95		--- 1.0.1
	95	+-- 2.0.0
96	96	--
97	97	-- HISTORY
98	98	-- Date Remarks

Show on old repository browser