File: test91_check_source_changes.sql

package info (click to toggle)
mimeo 1.5.1-4
  • links: PTS, VCS
  • area: main
  • in suites: buster
  • size: 5,276 kB
  • sloc: sql: 85,916; python: 81; makefile: 27; sh: 17
file content (118 lines) | stat: -rw-r--r-- 9,740 bytes parent folder | download | duplicates (3)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
/*
 * Checks monitoring functions to ensure they catch new source tables and source column changes on non-snap tables
 * Further checks that snapshot replication copies column changes
 */
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true

-- This should be the last batch of tests since I don't feel like resetting the batch limits for any to come after them

SELECT set_config('search_path','mimeo, dblink, public',false);

SELECT plan(6);

SELECT dblink_connect('mimeo_test', 'host=localhost port=5432 dbname=mimeo_source user=mimeo_owner password=mimeo_owner');
SELECT is(dblink_get_connections() @> '{mimeo_test}', 't', 'Remote database connection established');
SELECT dblink_connect('mimeo_owner', 'host=localhost port=5432 dbname=mimeo_source user=mimeo_owner password=mimeo_owner');
SELECT is(dblink_get_connections() @> '{mimeo_owner}', 't', 'Remote database connection established');

SELECT dblink_exec('mimeo_test', 'CREATE TABLE mimeo_source.brand_new_table(id serial, stuff text)');

SELECT results_eq('SELECT schemaname, tablename FROM check_missing_source_tables()'
    , $$VALUES ('mimeo_source', 'brand_new_table') $$
    , 'Ensure check_missing_source_tables() returns table on source that doesn''t exist on destination');

SELECT is_empty('SELECT * FROM check_source_columns()', 'Check that check_source_columns returns nothing before changes on source');

SELECT dblink_exec('mimeo_test', 'DROP VIEW mimeo_source.snap_test_source_view');
SELECT dblink_exec('mimeo_test', 'DROP VIEW mimeo_source.inserter_test_source_view');
SELECT dblink_exec('mimeo_test', 'DROP VIEW mimeo_source.updater_test_source_view');
SELECT dblink_exec('mimeo_test', 'ALTER TABLE mimeo_source.snap_test_source DROP COLUMN col3');
SELECT dblink_exec('mimeo_test', 'ALTER TABLE mimeo_source.snap_test_source ADD COLUMN col4 varchar(42)');
SELECT dblink_exec('mimeo_test', 'ALTER TABLE mimeo_source.inserter_test_source ADD COLUMN col4 text');
SELECT dblink_exec('mimeo_test', 'ALTER TABLE mimeo_source.updater_test_source ALTER COLUMN col4 TYPE text');
SELECT dblink_exec('mimeo_test', 'ALTER TABLE mimeo_source.dml_test_source ADD COLUMN col4 inet');
SELECT dblink_exec('mimeo_test', 'ALTER TABLE mimeo_source.logdel_test_source ADD COLUMN col4 point');
SELECT dblink_exec('mimeo_test', 'ALTER TABLE mimeo_source.logdel_test_source ALTER col1 TYPE text');
SELECT dblink_exec('mimeo_owner', 'CREATE VIEW mimeo_source.snap_test_source_view AS SELECT * FROM mimeo_source.snap_test_source');
SELECT dblink_exec('mimeo_owner', 'CREATE VIEW mimeo_source.inserter_test_source_view AS SELECT * FROM mimeo_source.inserter_test_source');
SELECT dblink_exec('mimeo_owner', 'CREATE VIEW mimeo_source.updater_test_source_view AS SELECT * FROM mimeo_source.updater_test_source');
-- Ensure views have permissions needed for mimeo_test role to call refreshes
SELECT dblink_exec('mimeo_owner', 'GRANT SELECT, TRIGGER ON ALL TABLES IN SCHEMA mimeo_source TO mimeo_test');

SELECT results_eq('SELECT dest_schemaname, dest_tablename, src_schemaname, src_tablename, missing_column_name, missing_column_type FROM check_source_columns() ORDER BY 1,2,3,4,5,6'
    , $$VALUES ('mimeo_dest','dml_test_dest_multi','mimeo_source','dml_test_source','col4','inet')
        , ('mimeo_dest','inserter_test_dest','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_dest','inserter_test_dest_condition','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_dest','inserter_test_dest_nodata','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_dest','inserter_test_dest_serial','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_dest','inserter_test_dest_serial_view','mimeo_source','inserter_test_source_view','col4','text')
        , ('mimeo_dest','logdel_test_dest_multi','mimeo_source','logdel_test_source','col1','text')
        , ('mimeo_dest','logdel_test_dest_multi','mimeo_source','logdel_test_source','col4','point')
        , ('mimeo_dest','snap_test_dest','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_dest','snap_test_dest_condition','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_dest','snap_test_dest_nodata','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_dest','table_test_dest','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_dest','table_test_dest_condition','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_dest','table_test_dest_nodata','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_dest','table_test_dest_view','mimeo_source','snap_test_source_view','col4','character varying(42)')
        , ('mimeo_dest','updater_test_dest','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_dest','updater_test_dest_condition','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_dest','updater_test_dest_nodata','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_dest','updater_test_dest_serial','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_dest','updater_test_dest_serial_view','mimeo_source','updater_test_source_view','col4','text')
        , ('mimeo_source','dml_test_source','mimeo_source','dml_test_source','col4','inet')
        , ('mimeo_source','inserter_test_source','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_source','inserter_test_source_view','mimeo_source','inserter_test_source_view','col4','text')
        , ('mimeo_source','logdel_test_source','mimeo_source','logdel_test_source','col1','text')
        , ('mimeo_source','logdel_test_source','mimeo_source','logdel_test_source','col4','point')
        , ('mimeo_source','snap_test_source','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_source','snap_test_source_view','mimeo_source','snap_test_source_view','col4','character varying(42)')
        , ('mimeo_source','updater_test_source','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_source','updater_test_source_view','mimeo_source','updater_test_source_view','col4','text')$$
    , ' Ensure check_source_columns() returns correct column diff from source');

SELECT diag('Running snap refresh...');

SELECT refresh_snap('mimeo_source.snap_test_source');
SELECT refresh_snap('mimeo_dest.snap_test_dest');
SELECT refresh_snap('mimeo_dest.snap_test_dest_nodata');
SELECT refresh_snap('mimeo_dest.snap_test_dest_filter');
SELECT refresh_snap('mimeo_dest.snap_test_dest_condition');
SELECT refresh_snap('mimeo_source.snap_test_source_empty');
SELECT refresh_snap('mimeo_dest.snap_test_dest_change_col');
SELECT refresh_snap('mimeo_source.snap_test_source_view');

SELECT results_eq('SELECT dest_schemaname, dest_tablename, src_schemaname, src_tablename, missing_column_name, missing_column_type FROM check_source_columns() ORDER BY 1,2,3,4,5,6'
    , $$VALUES ('mimeo_dest','dml_test_dest_multi','mimeo_source','dml_test_source','col4','inet')
        , ('mimeo_dest','inserter_test_dest','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_dest','inserter_test_dest_condition','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_dest','inserter_test_dest_nodata','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_dest','inserter_test_dest_serial','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_dest','inserter_test_dest_serial_view','mimeo_source','inserter_test_source_view','col4','text')
        , ('mimeo_dest','logdel_test_dest_multi','mimeo_source','logdel_test_source','col1','text')
        , ('mimeo_dest','logdel_test_dest_multi','mimeo_source','logdel_test_source','col4','point')
        , ('mimeo_dest','table_test_dest','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_dest','table_test_dest_condition','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_dest','table_test_dest_nodata','mimeo_source','snap_test_source','col4','character varying(42)')
        , ('mimeo_dest','table_test_dest_view','mimeo_source','snap_test_source_view','col4','character varying(42)')
        , ('mimeo_dest','updater_test_dest','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_dest','updater_test_dest_condition','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_dest','updater_test_dest_nodata','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_dest','updater_test_dest_serial','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_dest','updater_test_dest_serial_view','mimeo_source','updater_test_source_view','col4','text')
        , ('mimeo_source','dml_test_source','mimeo_source','dml_test_source','col4','inet')
        , ('mimeo_source','inserter_test_source','mimeo_source','inserter_test_source','col4','text')
        , ('mimeo_source','inserter_test_source_view','mimeo_source','inserter_test_source_view','col4','text')
        , ('mimeo_source','logdel_test_source','mimeo_source','logdel_test_source','col1','text')
        , ('mimeo_source','logdel_test_source','mimeo_source','logdel_test_source','col4','point')
        , ('mimeo_source','updater_test_source','mimeo_source','updater_test_source','col4','text')
        , ('mimeo_source','updater_test_source_view','mimeo_source','updater_test_source_view','col4','text')$$
    , ' Ensure snapshots replicated column changes by checking that check_source_columns() no longer lists them');


SELECT dblink_disconnect('mimeo_test');
SELECT dblink_disconnect('mimeo_owner');

SELECT * FROM finish();