• R/O
  • SSH

execsql-upsert: 提交

Default repository for the execsql-upsert scripts


Commit MetaInfo

修訂5d867f6a5a6847c97608c9fd63401938bd65fbfa (tree)
時間2019-06-04 11:33:08
作者Elizabeth Shea <elizabethshea@prot...>
CommiterElizabeth Shea

Log Message

Added UPDTPKQA_ONE to pg_upsert and correction to logging in ss_upsert.

Change Summary

差異

diff -r 462eeeeb09f0 -r 5d867f6a5a68 upsert/pg_upsert.sql
--- a/upsert/pg_upsert.sql Wed May 29 18:55:08 2019 -0700
+++ b/upsert/pg_upsert.sql Mon Jun 03 19:33:08 2019 -0700
@@ -152,6 +152,8 @@
152152 -- exclude any columns that are only in staging table, such
153153 -- as any "new_" PK columns to be used for PK updates). ES.
154154 -- 2019-05-29 Added script UPDTPK_ONE. ES.
155+-- 2019-05-30 Added UPDTPKQA_ONE through check #8. ES.
156+-- 2019-06-03 Completed UPDTPKQA_ONE and added UPDTPKQA_ONE_INNERLOOP. ES.
155157 -- ==================================================================
156158
157159
@@ -2010,6 +2012,7 @@
20102012 cast('new_' || column_name || ' is null' as text) as newpk_col_empty,
20112013 cast('new_' || column_name || ' is not null' as text) as newpk_col_not_empty,
20122014 cast(column_name || ' = s.new_' || column_name as text) as assmt_expr,
2015+ cast('b.' || column_name || ' = s.new_' || column_name as text) as join_expr_oldnew,
20132016 cast('s.new_' || column_name || ' = b.new_' || column_name as text) as join_expr_new,
20142017 k.ordinal_position
20152018 into temporary table ups_pkcol_info
@@ -2029,8 +2032,7 @@
20292032
20302033
20312034 -- 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!!)
2035+-- !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!!)
20342036
20352037
20362038 -- Run the PK update ONLY if QA check script returned no errors
@@ -2123,4 +2125,632 @@
21232125
21242126 -- !x! END SCRIPT
21252127 -- ################### End of UPDTPK_ONE ########################
2126--- ################################################################
\ No newline at end of file
2128+-- ################################################################
2129+
2130+
2131+-- ################################################################
2132+-- Script UPDTPKQA_ONE
2133+--
2134+-- Performs QA checks on requested primary key updates to a table,
2135+-- based on old and new values of the table's primary key columns
2136+-- in a staging table.
2137+--
2138+-- Input parameters:
2139+-- base_schema : The name of the base table schema.
2140+-- staging : The name of the staging schema.
2141+-- table : The table name--same for base and staging.
2142+-- pkinfo_table : The name of a temporary table to be passed by
2143+-- the caller that contains information about the table PK,
2144+-- including strings to be used in constructing
2145+-- SQL for checks
2146+-- qaerror_table : The name of a temporary table to
2147+-- store any errors found by QA checks.
2148+-- display_errors : A value of 'Yes' or 'No' to indicate whether
2149+-- any errors should be displayed in a GUI.
2150+-- Output parameters:
2151+-- error_list : The name of the variable to receive FILL IN.
2152+--
2153+-- Global variables:
2154+-- logfile : The name of a log file to which update
2155+-- messages will be written. Optional.
2156+-- log_sql : A value of 'Yes' or 'No' indicating whether
2157+-- the update and insert statements should
2158+-- also be written to the logfile. Optional.
2159+-- Currently only writes SQL for foreign key checks
2160+-- (final check) to log.
2161+-- log_errors : A value of 'Yes' or 'No' to indicate whether
2162+-- errors are written to the logfile. Optional.
2163+--
2164+-- Tables and views created or modified:
2165+-- ups_missing_pk_cols : temporary table
2166+-- ups_pkqa_str_lib : tempoarary table
2167+-- ups_any_pk_cols : temporary table
2168+-- ups_empty_pk_cols : temporary table
2169+-- ups_empty_pk_cols_rwcnt : temporary view
2170+-- ups_old_pks_wc : temporary table
2171+-- ups_invalid_old_pks : temporary table
2172+-- ups_invld_pk_rwcnt : temporary view
2173+-- ups_existing_new_pks : temporary table
2174+-- ups_exst_nwpk_rwcnt : temporary view
2175+-- ups_pk_mapping_conflict : temporary table
2176+-- ups_map_conf_rwcnt : temporary view
2177+-- ups_pk_duplicate_keys : temporary table
2178+-- ups_dup_key_rwcnt : temporary view
2179+-- ups_fkcol_refs : temporary table
2180+-- ups_pkcol_deps : temporary table
2181+-- ups_pkfk_ctrl : temporary table
2182+-- ===============================================================
2183+
2184+-- !x! BEGIN SCRIPT UPDTPKQA_ONE with parameters (base_schema, staging, table, pkinfo_table, qaerror_table, display_errors)
2185+
2186+-- Write an initial header to the logfile.
2187+-- !x! if(sub_defined(logfile))
2188+ -- !x! write "" to !!logfile!!
2189+ -- !x! write "==================================================================" to !!logfile!!
2190+ -- !x! write "!!$current_time!! -- QA checks for primary key updates on table !!#table!!" to !!logfile!!
2191+-- !x! endif
2192+
2193+-- !x! write "Conducting QA checks on table !!#staging!!.!!#table!! for primary key updates to table !!#base_schema!!.!!#table!!"
2194+
2195+-- Initialize the status and progress bars if the console is running.
2196+-- !x! if(console_on)
2197+ -- !x! console status "QA checks for PK updates on !!#base_schema!!.!!#table!!"
2198+-- !x! endif
2199+
2200+
2201+-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2202+-- Check 1 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2203+-- No primary key constraint on base table
2204+-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2205+-- !x! if(not hasrows(!!#pkinfo_table!!))
2206+
2207+ -- !x! sub ~error_description No primary key constraint on base table !!#base_schema!!.!!#table!!
2208+ -- !x! write " !!~error_description!!"
2209+ -- !x! if(sub_defined(logfile))
2210+ -- !x! write "" to !!logfile!!
2211+ -- !x! write "!!~error_description!!" to !!logfile!!
2212+ -- !x! endif
2213+ insert into !!#qaerror_table!! (error_code, error_description)
2214+ values ('No PK on base table', '!!~error_description!!')
2215+ ;
2216+
2217+-- No other QA checks are conducted if this check fails:
2218+-- Remaining QA checks are conducted ONLY if base table has PK
2219+-- !x! else
2220+
2221+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2222+ -- Check 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2223+ -- A "new" PK column exists in staging table for every PK column of base table
2224+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2225+
2226+ -- Find any MISSING PK columns in staging table
2227+ drop table if exists ups_missing_pk_cols cascade;
2228+ select
2229+ string_agg(newpk_col, ', ' order by ordinal_position) as missing_newpk_cols
2230+ into temporary table ups_missing_pk_cols
2231+ from
2232+ --Base table PK columns, with expected name in staging table ("new_" prepended to column name)
2233+ !!#pkinfo_table!! as pk
2234+ --Staging table columns
2235+ left join
2236+ (
2237+ select table_name, column_name
2238+ from information_schema.columns
2239+ where
2240+ table_schema = '!!#staging!!'
2241+ ) as stag on pk.table_name=stag.table_name and pk.newpk_col=stag.column_name
2242+ where
2243+ stag.column_name is null
2244+ having count(*)>0
2245+ ;
2246+
2247+ -- !x! if(hasrows(ups_missing_pk_cols))
2248+
2249+ -- !x! subdata ~error_info ups_missing_pk_cols
2250+
2251+ -- !x! sub ~error_description New primary key column(s) missing from staging table: !!~error_info!!
2252+
2253+ -- !x! write " !!~error_description!!"
2254+ -- !x! if(sub_defined(logfile))
2255+ -- !x! write "" to !!logfile!!
2256+ -- !x! write "!!~error_description!!" to !!logfile!!
2257+ -- !x! endif
2258+ insert into !!#qaerror_table!! (error_code, error_description)
2259+ values ('Missing new PK column(s)', '!!~error_description!!')
2260+ ;
2261+
2262+ -- No other QA checks are conducted if this check fails:
2263+ -- Remaining QA checks are all conducted ONLY if all expected "new PK" columns exist in staging table
2264+ -- !x! else
2265+
2266+ -- Library of aggregated strings used to construct SQL for the remaining checks
2267+
2268+ -- Just base table
2269+ -- !x! sub ~base_table !!#base_schema!!.!!#table!!
2270+
2271+ -- Just staging table
2272+ -- !x! sub ~staging_table !!#staging!!.!!#table!!
2273+
2274+ drop table if exists ups_pkqa_str_lib;
2275+ select
2276+ string_agg(column_name, ', ' order by ordinal_position) as old_pkcol,
2277+ string_agg(staging_aliased, ', ' order by ordinal_position) as old_pkcol_aliased,
2278+ string_agg(staging_aliased_prefix, ', ' order by ordinal_position) as old_pkcol_aliased_prefix,
2279+ string_agg(newpk_col, ', ' order by ordinal_position) as new_pkcol,
2280+ string_agg(newpk_col_aliased, ', ' order by ordinal_position) as new_pkcol_aliased,
2281+ string_agg(join_expr, ' and ' order by ordinal_position) as joincond_origorig,
2282+ string_agg(join_expr_oldnew, ' and ' order by ordinal_position) as joincond_oldnew,
2283+ string_agg(join_expr_new, ' and ' order by ordinal_position) as joincond_newnew,
2284+ string_agg(newpk_col_not_empty, ' or ' order by ordinal_position) as any_newpk_col_not_empty,
2285+ string_agg(newpk_col_not_empty, ' and ' order by ordinal_position) as all_newpk_col_not_empty,
2286+ string_agg(newpk_col_empty, ' or ' order by ordinal_position) as any_newpk_col_empty,
2287+ string_agg(newpk_col_empty, ' and ' order by ordinal_position) as all_newpk_col_empty
2288+ into temporary table ups_pkqa_str_lib
2289+ from !!#pkinfo_table!!
2290+ ;
2291+ -- !x! select_sub ups_pkqa_str_lib
2292+
2293+
2294+
2295+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2296+ -- Check 3 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2297+ -- There are any rows with PK updates specified.
2298+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2299+
2300+
2301+ -- Find any populated new PK columns in staging table
2302+ drop table if exists ups_any_pk_cols cascade;
2303+ select * into temporary table ups_any_pk_cols
2304+ from !!~staging_table!!
2305+ where !!@any_newpk_col_not_empty!!
2306+ ;
2307+ -- !x! if(not hasrows(ups_any_pk_cols))
2308+ -- !x! sub ~error_description No primary key updates specified in !!#staging!!.!!#table!!
2309+ -- !x! write " !!~error_description!!"
2310+ -- !x! if(sub_defined(logfile))
2311+ -- !x! write "" to !!logfile!!
2312+ -- !x! write "!!~error_description!!" to !!logfile!!
2313+ -- !x! endif
2314+ insert into !!#qaerror_table!! (error_code, error_description)
2315+ values ('No PK updates specified in staging table', '!!~error_description!!')
2316+ ;
2317+ -- No other QA checks are conducted if this check fails
2318+ -- !x! else
2319+
2320+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2321+ -- Check 4 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2322+ -- Where any "new" PK column is populated in the staging table, they are all populated.
2323+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2324+
2325+ -- Construct SQL statement looking for any NULLs in "new" PK columns in rows where any PK columns are populated
2326+ -- Find any EMPTY PK columns in staging table
2327+ drop table if exists ups_empty_pk_cols cascade;
2328+ select
2329+ !!@old_pkcol!!,
2330+ !!@new_pkcol!!
2331+ into temporary table ups_empty_pk_cols
2332+ from
2333+ !!~staging_table!!
2334+ where
2335+ not (!!@all_newpk_col_empty!!)
2336+ and (!!@any_newpk_col_empty!!)
2337+ ;
2338+
2339+ -- !x! if(hasrows(ups_empty_pk_cols))
2340+ drop view if exists ups_empty_pk_cols_rwcnt cascade;
2341+ create temporary view ups_empty_pk_cols_rwcnt as
2342+ select count(*) as rwcnt
2343+ from ups_empty_pk_cols
2344+ ;
2345+ -- !x! subdata ~rowcount ups_empty_pk_cols_rwcnt
2346+ -- !x! sub ~error_description Missing values in new PK columns in !!#staging!!.!!#table!!: !!~rowcount!! row(s)
2347+ -- !x! write " !!~error_description!!"
2348+ insert into !!#qaerror_table!! (error_code, error_description)
2349+ values ('Incomplete mapping', '!!~error_description!!')
2350+ ;
2351+ -- !x! if(sub_defined(logfile))
2352+ -- !x! write "" to !!logfile!!
2353+ -- !x! write "!!~error_description!!" to !!logfile!!
2354+ -- !x! if(sub_defined(log_errors))
2355+ -- !x! andif(is_true(!!log_errors!!))
2356+ -- !x! export ups_empty_pk_cols append to !!logfile!! as txt
2357+ -- !x! endif
2358+ -- !x! endif
2359+ -- !x! if(is_true(!!#display_errors!!))
2360+ -- !x! prompt message "Missing values in new PK columns in !!#staging!!.!!#table!!" display ups_empty_pk_cols
2361+ -- !x! endif
2362+ -- !x! endif
2363+
2364+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2365+ -- Check 5 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2366+ -- Where any "new" PK column is populated in the staging table, the value of the original PK for that row is valid
2367+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2368+
2369+ -- New PK col in staging table are not empty
2370+ drop table if exists ups_old_pks_wc cascade;
2371+ select base_aliased
2372+ into temporary table ups_old_pks_wc
2373+ from !!#pkinfo_table!!
2374+ order by ordinal_position
2375+ limit 1;
2376+ -- !x! subdata ~old_pk_firstcol ups_old_pks_wc
2377+
2378+ drop table if exists ups_invalid_old_pks cascade;
2379+ select
2380+ !!@old_pkcol_aliased!!,
2381+ !!@new_pkcol!!
2382+ into temporary table ups_invalid_old_pks
2383+ from !!~staging_table!! as s
2384+ left join !!~base_table!! as b on !!@joincond_origorig!!
2385+ where !!@all_newpk_col_not_empty!! and !!~old_pk_firstcol!! is null
2386+ ;
2387+
2388+ -- !x! if(hasrows(ups_invalid_old_pks))
2389+ drop view if exists ups_invld_pk_rwcnt cascade;
2390+ create temporary view ups_invld_pk_rwcnt as
2391+ select count(*) as rwcnt
2392+ from ups_invalid_old_pks
2393+ ;
2394+ -- !x! subdata ~rowcount ups_invld_pk_rwcnt
2395+ -- !x! sub ~error_description Invalid original PK in !!#staging!!.!!#table!!: !!~rowcount!! row(s)
2396+ -- !x! write " !!~error_description!!"
2397+ insert into !!#qaerror_table!! (error_code, error_description)
2398+ values ('Invalid old PK value', '!!~error_description!!')
2399+ ;
2400+ -- !x! if(sub_defined(logfile))
2401+ -- !x! write "" to !!logfile!!
2402+ -- !x! write "!!~error_description!!" to !!logfile!!
2403+ -- !x! if(sub_defined(log_errors))
2404+ -- !x! andif(is_true(!!log_errors!!))
2405+ -- !x! export ups_invalid_old_pks append to !!logfile!! as txt
2406+ -- !x! endif
2407+ -- !x! endif
2408+ -- !x! if(is_true(!!#display_errors!!))
2409+ -- !x! prompt message "Invalid original PK in !!#staging!!.!!#table!!" display ups_invalid_old_pks
2410+ -- !x! endif
2411+ -- !x! endif
2412+
2413+
2414+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2415+ -- Check 6 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2416+ -- None of the "new" PK values already exist in the base table
2417+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2418+ drop table if exists ups_existing_new_pks cascade;
2419+ select
2420+ !!@old_pkcol_aliased_prefix!!,
2421+ !!@new_pkcol!!,
2422+ b.*
2423+ into temporary table ups_existing_new_pks
2424+ from !!~staging_table!! as s
2425+ inner join !!~base_table!! as b on !!@joincond_oldnew!!
2426+ ;
2427+
2428+ -- !x! if(hasrows(ups_existing_new_pks))
2429+ drop view if exists ups_exst_nwpk_rwcnt cascade;
2430+ create temporary view ups_exst_nwpk_rwcnt as
2431+ select count(*) as rwcnt
2432+ from ups_existing_new_pks
2433+ ;
2434+ -- !x! subdata ~rowcount ups_exst_nwpk_rwcnt
2435+ -- !x! sub ~error_description New PK already exists in !!#base_schema!!.!!#table!!: !!~rowcount!! row(s)
2436+ -- !x! write " !!~error_description!!"
2437+ insert into !!#qaerror_table!! (error_code, error_description)
2438+ values ('Existing new PK value', '!!~error_description!!')
2439+ ;
2440+ -- !x! if(sub_defined(logfile))
2441+ -- !x! write "" to !!logfile!!
2442+ -- !x! write "!!~error_description!!" to !!logfile!!
2443+ -- !x! if(sub_defined(log_errors))
2444+ -- !x! andif(is_true(!!log_errors!!))
2445+ -- !x! export ups_existing_new_pks append to !!logfile!! as txt
2446+ -- !x! endif
2447+ -- !x! endif
2448+ -- !x! if(is_true(!!#display_errors!!))
2449+ -- !x! prompt message "New PK already exists in !!#base_schema!!.!!#table!!" display ups_existing_new_pks
2450+ -- !x! endif
2451+ -- !x! endif
2452+
2453+
2454+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2455+ -- Check 7 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2456+ -- No two (or more) original PK values map to same new PK value
2457+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2458+ drop table if exists ups_pk_mapping_conflict cascade;
2459+ select
2460+ !!@old_pkcol_aliased!!,
2461+ !!@new_pkcol_aliased!!
2462+ into temporary table ups_pk_mapping_conflict
2463+ from !!~staging_table!! as s
2464+ inner join
2465+ (
2466+ select
2467+ !!@new_pkcol!!
2468+ from
2469+ (select distinct !!@old_pkcol!!, !!@new_pkcol!! from !!~staging_table!! where !!@all_newpk_col_not_empty!!) as a
2470+ group by
2471+ !!@new_pkcol!!
2472+ having count(*) >1
2473+ ) as b on !!@joincond_newnew!!
2474+ ;
2475+
2476+ -- !x! if(hasrows(ups_pk_mapping_conflict))
2477+ drop view if exists ups_map_conf_rwcnt cascade;
2478+ create temporary view ups_map_conf_rwcnt as
2479+ select count(*) as rwcnt
2480+ from ups_pk_mapping_conflict
2481+ ;
2482+ -- !x! subdata ~rowcount ups_map_conf_rwcnt
2483+ -- !x! sub ~error_description Multiple original PKs mapped to same new PK in !!#staging!!.!!#table!!: !!~rowcount!! row(s)
2484+ -- !x! write " !!~error_description!!"
2485+ insert into !!#qaerror_table!! (error_code, error_description)
2486+ values ('Mapping conflict', '!!~error_description!!')
2487+ ;
2488+ -- !x! if(sub_defined(logfile))
2489+ -- !x! write "" to !!logfile!!
2490+ -- !x! write "!!~error_description!!" to !!logfile!!
2491+ -- !x! if(sub_defined(log_errors))
2492+ -- !x! andif(is_true(!!log_errors!!))
2493+ -- !x! export ups_pk_mapping_conflict append to !!logfile!! as txt
2494+ -- !x! endif
2495+ -- !x! endif
2496+ -- !x! if(is_true(!!#display_errors!!))
2497+ -- !x! prompt message "Multiple original PKs mapped to same new PK in !!#staging!!.!!#table!!" display ups_pk_mapping_conflict
2498+ -- !x! endif
2499+ -- !x! endif
2500+
2501+
2502+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2503+ -- Check 8 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2504+ -- No single original PK value maps to multiple new PK values
2505+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2506+
2507+ drop table if exists ups_pk_duplicate_keys cascade;
2508+ select
2509+ !!@old_pkcol_aliased!!,
2510+ !!@new_pkcol_aliased!!
2511+ into temporary table ups_pk_duplicate_keys
2512+ from !!~staging_table!! as s
2513+ inner join
2514+ (
2515+ select
2516+ !!@old_pkcol!!
2517+ from
2518+ (select distinct !!@old_pkcol!!, !!@new_pkcol!! from !!~staging_table!! where !!@all_newpk_col_not_empty!!) as a
2519+ group by
2520+ !!@old_pkcol!!
2521+ having count(*)>1
2522+ ) as b on !!@joincond_origorig!!
2523+ ;
2524+
2525+ -- !x! if(hasrows(ups_pk_duplicate_keys))
2526+ drop view if exists ups_dup_key_rwcnt cascade;
2527+ create temporary view ups_dup_key_rwcnt as
2528+ select count(*) as rwcnt
2529+ from ups_pk_duplicate_keys
2530+ ;
2531+ -- !x! subdata ~rowcount ups_dup_key_rwcnt
2532+ -- !x! sub ~error_description Original PK mapped to multiple new PKs in !!#staging!!.!!#table!!: !!~rowcount!! row(s)
2533+ -- !x! write " !!~error_description!!"
2534+ insert into !!#qaerror_table!! (error_code, error_description)
2535+ values ('Duplicate keys', '!!~error_description!!')
2536+ ;
2537+ -- !x! if(sub_defined(logfile))
2538+ -- !x! write "" to !!logfile!!
2539+ -- !x! write "!!~error_description!!" to !!logfile!!
2540+ -- !x! if(sub_defined(log_errors))
2541+ -- !x! andif(is_true(!!log_errors!!))
2542+ -- !x! export ups_pk_duplicate_keys append to !!logfile!! as txt
2543+ -- !x! endif
2544+ -- !x! endif
2545+ -- !x! if(is_true(!!#display_errors!!))
2546+ -- !x! prompt message "Original PK mapped to multiple new PKs in !!#staging!!.!!#table!!" display ups_pk_duplicate_keys
2547+ -- !x! endif
2548+ -- !x! endif
2549+
2550+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2551+ -- Check 9 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2552+ -- If any of the PK columns reference a parent table, all the "new" values of that column are valid
2553+ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2554+
2555+ -- Get ALL column references for the base table
2556+ drop table if exists ups_fkcol_refs cascade;
2557+ select
2558+ fk_constraint,
2559+ staging_schema,
2560+ fkinf.table_schema,
2561+ fkinf.table_name,
2562+ att1.attname as column_name,
2563+ fkinf.column_id,
2564+ ns2.nspname as parent_schema,
2565+ cls2.relname as parent_table,
2566+ att2.attname as parent_column,
2567+ fkinf.parent_column_id
2568+ into temporary table ups_fkcol_refs
2569+ from
2570+ (
2571+ select
2572+ cons.conname as fk_constraint,
2573+ '!!#staging!!' as staging_schema,
2574+ ns1.nspname as table_schema,
2575+ cls1.relname as table_name,
2576+ cons.conrelid as table_id,
2577+ unnest(cons.conkey) as column_id,
2578+ cons.confrelid as parent_table_id,
2579+ unnest(cons.confkey) as parent_column_id
2580+ from
2581+ pg_constraint as cons
2582+ inner join pg_class as cls1 on cls1.oid=cons.conrelid
2583+ inner join pg_namespace ns1 on ns1.oid = cls1.relnamespace
2584+
2585+ where
2586+ cons.contype='f'
2587+ and ns1.nspname = '!!#base_schema!!'
2588+ and cls1.relname = '!!#table!!'
2589+ ) as fkinf
2590+ inner join pg_class as cls2 on cls2.oid=fkinf.parent_table_id
2591+ inner join pg_namespace ns2 on ns2.oid = cls2.relnamespace
2592+ inner join pg_attribute att1 on att1.attrelid = fkinf.table_id and att1.attnum = fkinf.column_id
2593+ inner join pg_attribute att2 on att2.attrelid = fkinf.parent_table_id and att2.attnum = fkinf.parent_column_id
2594+ ;
2595+
2596+ -- Narrow the list down to ONLY dependencies that affect PK columns
2597+ -- Include not just the PK columns themselves, but ALL columns included in FKs
2598+ -- that include ANY PK columns (probably rare/unlikely that a non-PK column would be
2599+ -- part of the same foreign key as a PK column, but this ensures that ALL columns of the FK
2600+ -- are included, whether or not the column is part of the PK)
2601+ drop table if exists ups_pkcol_deps cascade;
2602+ select
2603+ refs.*
2604+ into temporary table ups_pkcol_deps
2605+ from ups_fkcol_refs as refs
2606+ inner join
2607+ --Distinct list of FK constraints on the table that include ANY PK columns
2608+ (
2609+ select distinct
2610+ fk_constraint, r.table_schema, r.table_name
2611+ from
2612+ ups_fkcol_refs as r
2613+ 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
2614+ ) as const on refs.fk_constraint=const.fk_constraint and refs.table_schema=const.table_schema and refs.table_name=const.table_name
2615+ ;
2616+
2617+ -- Create a control table for looping to check each fk
2618+ -- Include (for later use) some of the constructed strings that apply to the entire PK (not
2619+ -- just the FK being checked)
2620+ drop table if exists ups_pkfk_ctrl cascade;
2621+ select
2622+ fk_constraint,
2623+ staging_schema, table_schema, table_name, parent_schema, parent_table,
2624+ min(parent_column) as any_referenced_column,
2625+ '!!@old_pkcol_aliased!!' as old_pkcol_aliased,
2626+ '!!@new_pkcol!!' as new_pkcol,
2627+ '!!@all_newpk_col_not_empty!!' as all_newpk_col_not_empty,
2628+ False::boolean as processed
2629+ into temporary table ups_pkfk_ctrl
2630+ from ups_pkcol_deps
2631+ group by
2632+ fk_constraint,
2633+ staging_schema, table_schema, table_name, parent_schema, parent_table
2634+ ;
2635+
2636+ -- Create a view to select one constraint to process.
2637+ drop view if exists ups_next_fk cascade;
2638+ create temporary view ups_next_fk as
2639+ select *
2640+ from ups_pkfk_ctrl
2641+ where not processed
2642+ limit 1
2643+ ;
2644+
2645+ --Process all constraints: check every foreign key
2646+ --!x! execute script updtpkqa_one_innerloop with (qaerror_table=!!#qaerror_table!!, display_errors=!!#display_errors!!)
2647+ -- !x! endif
2648+ -- !x! endif
2649+-- !x! endif
2650+-- !x! END SCRIPT
2651+-- ################### UPDTPKQA_ONE ########################
2652+-- ################################################################
2653+-- Script UPDTPKQA_ONE_INNERLOOP
2654+-- ----------------------------------------------------------------
2655+-- !x! BEGIN SCRIPT UPDTPKQA_ONE_INNERLOOP with parameters(qaerror_table, display_errors)
2656+-- !x! if(hasrows(ups_next_fk))
2657+
2658+ -- !x! select_sub ups_next_fk
2659+
2660+ -- Compile FK info for the selected constraint
2661+ drop table if exists ups_sel_fk_cols cascade;
2662+ select
2663+ staging_schema, fk_constraint, table_schema, table_name, parent_schema,
2664+ parent_table,
2665+ string_agg(parent_column, ', ' order by column_id) as referenced_cols,
2666+ string_agg('s.new_' || column_name || '=' || 'b.' || parent_column, ' and ' order by column_id) as join_condition
2667+ into temporary table ups_sel_fk_cols
2668+ from ups_pkcol_deps
2669+ where fk_constraint='!!@fk_constraint!!'
2670+ group by
2671+ staging_schema, fk_constraint, table_schema, table_name, parent_schema,
2672+ parent_table
2673+ ;
2674+ -- !x! select_sub ups_sel_fk_cols
2675+
2676+ -- Construct SQL to check the selected FK
2677+ -- !x! sub ~select_stmt select !!@old_pkcol_aliased!!, !!@new_pkcol!! into temporary table ups_pk_fk_check from !!@staging_schema!!.!!@table_name!! as s
2678+ -- !x! sub ~join_stmt left join !!@parent_schema!!.!!@parent_table!! as b on !!@join_condition!!
2679+ -- !x! sub ~where_clause where !!@all_newpk_col_not_empty!! and b.!!@any_referenced_column!! is null
2680+
2681+ -- !x! sub ~fk_check drop table if exists ups_pk_fk_check cascade;
2682+ -- !x! sub_append ~fk_check !!~select_stmt!!
2683+ -- !x! sub_append ~fk_check !!~join_stmt!!
2684+ -- !x! sub_append ~fk_check !!~where_clause!!
2685+
2686+ -- Write the SQL to the log file if requested.
2687+ -- !x! if(sub_defined(logfile))
2688+ -- !x! andif(sub_defined(log_sql))
2689+ -- !x! andif(is_true(!!log_sql!!))
2690+ -- !x! write "" to !!logfile!!
2691+ -- !x! write "SQL for checking foreign key !!@fk_constraint!! for PK update to !!@table_schema!!.!!@table_name!!:" to !!logfile!!
2692+ -- !x! write [!!~fk_check!!] to !!logfile!!
2693+ -- !x! endif
2694+
2695+ -- Run the check
2696+ !!~fk_check!!;
2697+
2698+
2699+ -- !x! if(hasrows(ups_pk_fk_check))
2700+
2701+ drop view if exists ups_pk_fk_check_rwcnt cascade;
2702+ create temporary view ups_pk_fk_check_rwcnt as
2703+ select count(*) as rwcnt
2704+ from ups_pk_fk_check
2705+ ;
2706+
2707+ -- !x! subdata ~rowcount ups_pk_fk_check_rwcnt
2708+ -- !x! sub ~error_description !!@parent_schema!!.!!@parent_table!! (!!@referenced_cols!!): !!~rowcount!! row(s)
2709+
2710+ -- !x! write " Violation of foreign key !!@fk_constraint!! in new primary key columns in !!@staging_schema!!.!!@table_name!! referencing !!@parent_schema!!.!!@parent_table!!: !!~rowcount!! row(s)"
2711+
2712+ drop view if exists ups_pk_fk_qa_error cascade;
2713+ create temporary view ups_pk_fk_qa_error as
2714+ select
2715+ error_code, error_description
2716+ from !!#qaerror_table!!
2717+ where error_code='Invalid reference to parent table(s)';
2718+ -- !x! if(hasrows(ups_pk_fk_qa_error))
2719+ update !!#qaerror_table!!
2720+ set error_description=error_description || '; !!~error_description!!'
2721+ where error_code='Invalid reference to parent table(s)';
2722+
2723+ -- !x! else
2724+ insert into !!#qaerror_table!! (error_code, error_description)
2725+ values ('Invalid reference to parent table(s)', '!!~error_description!!')
2726+ ;
2727+ -- !x! endif
2728+
2729+
2730+ -- !x! if(sub_defined(logfile))
2731+ -- !x! write "" to !!logfile!!
2732+ -- !x! write "Violation of foreign key !!@fk_constraint!! in new primary key columns in !!@staging_schema!!.!!@table_name!! referencing !!@parent_schema!!.!!@parent_table!!: !!~rowcount!! row(s)" to !!logfile!!
2733+ -- !x! if(sub_defined(log_errors))
2734+ -- !x! andif(is_true(!!log_errors!!))
2735+ -- !x! export ups_pk_fk_check append to !!logfile!! as txt
2736+ -- !x! endif
2737+ -- !x! endif
2738+ -- !x! if(is_true(!!#display_errors!!))
2739+ -- !x! prompt message "Violation of foreign key !!@fk_constraint!! in new primary key columns in !!@staging_schema!!.!!@table_name!! referencing !!@parent_schema!!.!!@parent_table!!" display ups_pk_fk_check
2740+ -- !x! endif
2741+
2742+ -- !x! endif
2743+
2744+ -- Mark constraint as processed
2745+ update ups_pkfk_ctrl
2746+ set processed=True
2747+ where fk_constraint='!!@fk_constraint!!';
2748+
2749+ --LOOP
2750+ -- !x! execute script updtpkqa_one_innerloop with (qaerror_table=!!#qaerror_table!!, display_errors=!!#display_errors!!)
2751+
2752+-- !x! endif
2753+
2754+-- !x! END SCRIPT
2755+-- #################### End of UPDTPKQA_ONE ########################
2756+-- ################################################################
diff -r 462eeeeb09f0 -r 5d867f6a5a68 upsert/ss_upsert.sql
--- a/upsert/ss_upsert.sql Wed May 29 18:55:08 2019 -0700
+++ b/upsert/ss_upsert.sql Mon Jun 03 19:33:08 2019 -0700
@@ -123,6 +123,8 @@
123123 -- 2019-05-21 Completed first draft of UPDTPKQA_ONE. ES.
124124 -- 2019-05-22 Rearranged, organized, and standardized variable names
125125 -- in UPDTPKQA_ONE. ES.
126+-- 2019-05-29 Corrections to comments. ES.
127+-- 2019-06-03 Correction to logging in UPDTPKQA_ONE_INNERLOOP. ES.
126128 -- ==================================================================
127129
128130 -- ################################################################
@@ -2318,6 +2320,7 @@
23182320 --
23192321 -- Tables and views created or modified:
23202322 -- ups_missing_pk_cols : temporary table
2323+-- ups_any_pk_cols : temporary table
23212324 -- ===============================================================
23222325
23232326 -- !x! BEGIN SCRIPT UPDTPKQA_ONE with parameters (base_schema, staging, table, pkinfo_table, qaerror_table, display_errors)
@@ -2880,7 +2883,7 @@
28802883
28812884 -- !x! if(sub_defined(logfile))
28822885 -- !x! write "" to !!logfile!!
2883- -- !x! write "Violation of foreign key !!@fk_constraint!! in new primary key columns in !!@staging_schema!!.!!@table_name!! referencing !!@parent_schema!!.!!@parent_table!!" to !!logfile!!
2886+ -- !x! write "Violation of foreign key !!@fk_constraint!! in new primary key columns in !!@staging_schema!!.!!@table_name!! referencing !!@parent_schema!!.!!@parent_table!!: !!~rowcount!! row(s)" to !!logfile!!
28842887 -- !x! if(sub_defined(log_errors))
28852888 -- !x! andif(is_true(!!log_errors!!))
28862889 -- !x! export #ups_pk_fk_check append to !!logfile!! as txt
Show on old repository browser