File: test03_run_maker_functions.sql

package info (click to toggle)
mimeo 1.5.1-4
  • links: PTS, VCS
  • area: main
  • in suites: bullseye, buster
  • size: 5,276 kB
  • sloc: sql: 85,916; python: 81; makefile: 27; sh: 17
file content (158 lines) | stat: -rw-r--r-- 16,267 bytes parent folder | download | duplicates (2)
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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
\set ON_ERROR_ROLLBACK 1
\set ON_ERROR_STOP true

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

SELECT plan(1);

SELECT diag('Running maker for destination: mimeo_source.snap_test_source');
SELECT snapshot_maker('mimeo_source.snap_test_source',data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.snap_test_dest');
SELECT snapshot_maker('mimeo_source.snap_test_source', data_source_id, 'mimeo_dest.snap_test_dest') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.snap_test_dest_nodata');
SELECT snapshot_maker('mimeo_source.snap_test_source', data_source_id, 'mimeo_dest.snap_test_dest_nodata', p_pulldata := false) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.snap_test_dest_filter');
SELECT snapshot_maker('mimeo_source.snap_test_source', data_source_id, 'mimeo_dest.snap_test_dest_filter', p_filter := '{"col1","col2"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.snap_test_dest_condition');
SELECT snapshot_maker('mimeo_source.snap_test_source', data_source_id, 'mimeo_dest.snap_test_dest_condition', p_condition := 'WHERE col1 > 9000') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.snap_test_source_empty');
SELECT snapshot_maker('mimeo_source.snap_test_source_empty', data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.snap_test_source_change_col');
SELECT snapshot_maker('mimeo_source.snap_test_source_change_col', data_source_id, 'mimeo_dest.snap_test_dest_change_col') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.snap_test_source_view');
SELECT snapshot_maker('mimeo_source.snap_test_source_view', data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
-- Change owner to view and underlying tables since view select permissions are based on the view owner
ALTER TABLE mimeo_dest.snap_test_dest_change_col OWNER TO mimeo_test;
ALTER TABLE mimeo_dest.snap_test_dest_change_col_snap1 OWNER TO mimeo_test;
ALTER TABLE mimeo_dest.snap_test_dest_change_col_snap2 OWNER TO mimeo_test;
GRANT SELECT ON TABLE mimeo_dest.snap_test_dest_change_col TO mimeo_dumb_role;
GRANT SELECT ON TABLE mimeo_dest.snap_test_dest_change_col_snap1 TO mimeo_dumb_role;
GRANT SELECT ON TABLE mimeo_dest.snap_test_dest_change_col_snap2 TO mimeo_dumb_role;
SELECT diag('Running maker for destination: mimeo_source.Snap-test-Source');
SELECT snapshot_maker('mimeo_source.Snap-test-Source', data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
-- Change owner to view and underlying tables since view select permissions are based on the view owner
ALTER TABLE mimeo_source."Snap-test-Source" OWNER TO mimeo_test;
ALTER TABLE mimeo_source."Snap-test-Source_snap1" OWNER TO mimeo_test;
ALTER TABLE mimeo_source."Snap-test-Source_snap2" OWNER TO mimeo_test;
GRANT SELECT ON TABLE mimeo_source."Snap-test-Source" TO mimeo_dumb_role;
GRANT SELECT ON TABLE mimeo_source."Snap-test-Source_snap1" TO mimeo_dumb_role;
GRANT SELECT ON TABLE mimeo_source."Snap-test-Source_snap2" TO mimeo_dumb_role;

-- Table maker
-- Just reuse snap table source since it's doing mostly the same thing
SELECT diag('Running maker for destination: mimeo_dest.table_test_dest');
SELECT table_maker('mimeo_source.snap_test_source', data_source_id, 'mimeo_dest.table_test_dest', p_sequences := '{"mimeo_dest.col1_seq"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.table_test_dest_nodata');
SELECT table_maker('mimeo_source.snap_test_source', data_source_id, 'mimeo_dest.table_test_dest_nodata', p_pulldata := false) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.table_test_dest_filter');
SELECT table_maker('mimeo_source.snap_test_source', data_source_id, 'mimeo_dest.table_test_dest_filter', p_filter := '{"col1","col2"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.table_test_dest_condition');
SELECT table_maker('mimeo_source.snap_test_source', data_source_id, 'mimeo_dest.table_test_dest_condition', p_condition := 'WHERE col1 > 9000') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.table_test_dest_empty');
SELECT table_maker('mimeo_source.snap_test_source_empty', data_source_id, 'mimeo_dest.table_test_dest_empty') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.Table-test-Source');
SELECT table_maker('mimeo_source.Snap-test-Source', data_source_id, 'mimeo_dest.Table-test-Source', p_sequences := '{"mimeo_dest.PRIMARY-seq"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.table_test_dest_view');
SELECT table_maker('mimeo_source.snap_test_source_view', data_source_id, 'mimeo_dest.table_test_dest_view') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
CREATE SEQUENCE mimeo_dest.col1_seq;    -- Add a sequence to test that sequence reset is working
CREATE SEQUENCE mimeo_dest."PRIMARY-seq";
ALTER TABLE mimeo_dest.table_test_dest ALTER col1 SET DEFAULT nextval('mimeo_dest.col1_seq');
ALTER TABLE mimeo_dest."Table-test-Source" ALTER "primary" SET DEFAULT nextval('mimeo_dest."PRIMARY-seq"');

-- Inserter time
SELECT diag('Running maker for destination: mimeo_source.inserter_test_source');
SELECT inserter_maker('mimeo_source.inserter_test_source', 'time', 'col3', data_source_id, '00:00:05') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.inserter_test_dest');
SELECT inserter_maker('mimeo_source.inserter_test_source', 'time', 'col3', data_source_id,'00:00:05', 'mimeo_dest.inserter_test_dest') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.inserter_test_dest_nodata');
SELECT inserter_maker('mimeo_source.inserter_test_source', 'time', 'col3', data_source_id,'00:00:05', 'mimeo_dest.inserter_test_dest_nodata', p_pulldata := false) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.inserter_test_dest_filter');
SELECT inserter_maker('mimeo_source.inserter_test_source', 'time', 'col3', data_source_id,'00:00:05', 'mimeo_dest.inserter_test_dest_filter', p_filter := '{"col1","col3"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.inserter_test_dest_condition');
SELECT inserter_maker('mimeo_source.inserter_test_source', 'time', 'col3', data_source_id,'00:00:05', 'mimeo_dest.inserter_test_dest_condition', p_condition := 'WHERE col1 > 9000') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.inserter_test_source_empty');
SELECT inserter_maker('mimeo_source.inserter_test_source_empty', 'time', 'col3', data_source_id, '00:00:05') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.Inserter-Test-Source');
SELECT inserter_maker('mimeo_source.Inserter-Test-Source', 'time', 'Col-3', data_source_id, '00:00:05') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.inserter_test_source_view');
SELECT inserter_maker('mimeo_source.inserter_test_source_view', 'time', 'col3', data_source_id, '00:00:05') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';

--Inserter serial
SELECT diag('Running maker for destination: mimeo_dest.inserter_test_dest_serial');
SELECT inserter_maker('mimeo_source.inserter_test_source', 'serial', 'col1', data_source_id, '0', 'mimeo_dest.inserter_test_dest_serial') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.Inserter-Test-Source_Serial');
SELECT inserter_maker('mimeo_source.Inserter-Test-Source', 'serial', 'col1', data_source_id, '0', 'mimeo_dest.Inserter-Test-Source_Serial') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.inserter_test_dest_serial_view');
SELECT inserter_maker('mimeo_source.inserter_test_source_view', 'serial', 'col1', data_source_id, '0', 'mimeo_dest.inserter_test_dest_serial_view') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';

-- Updater time
SELECT diag('Running maker for destination: mimeo_source.updater_test_source');
SELECT updater_maker('mimeo_source.updater_test_source', 'time', 'col3', data_source_id, '00:00:05') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.updater_test_dest');
SELECT updater_maker('mimeo_source.updater_test_source', 'time', 'col3', data_source_id, '00:00:05', 'mimeo_dest.updater_test_dest') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.updater_test_dest_nodata');
SELECT updater_maker('mimeo_source.updater_test_source', 'time', 'col3', data_source_id, '00:00:05', 'mimeo_dest.updater_test_dest_nodata', p_pulldata := false) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.updater_test_dest_filter');
SELECT updater_maker('mimeo_source.updater_test_source', 'time', 'col3', data_source_id, '00:00:05', 'mimeo_dest.updater_test_dest_filter', p_filter := '{"col1","col3"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.updater_test_dest_condition');
SELECT updater_maker('mimeo_source.updater_test_source', 'time', 'col3', data_source_id, '00:00:05', 'mimeo_dest.updater_test_dest_condition', p_condition := 'WHERE col1 > 9000') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.updater_test_source_empty');
SELECT updater_maker('mimeo_source.updater_test_source_empty', 'time', 'col3', data_source_id, '00:00:05') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.Updater-Test-Source');
SELECT updater_maker('mimeo_source.Updater-Test-Source', 'time', 'Col3', data_source_id, '00:00:05') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.updater_test_source_view');
SELECT updater_maker('mimeo_source.updater_test_source_view', 'time', 'col3', data_source_id, '00:00:05', p_pk_name := '{"col1"}', p_pk_type := '{"int"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';

-- Updater serial
SELECT diag('Running maker for destination: mimeo_dest.updater_test_dest_serial');
SELECT updater_maker('mimeo_source.updater_test_source', 'serial', 'col4', data_source_id, '0', 'mimeo_dest.updater_test_dest_serial') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.Updater-Test-Source_Serial');
SELECT updater_maker('mimeo_source.Updater-Test-Source', 'serial', 'COL-1', data_source_id, '0', 'mimeo_dest.Updater-Test-Source_Serial') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.updater_test_dest_serial_view');
SELECT updater_maker('mimeo_source.updater_test_source_view', 'serial', 'col4', data_source_id, '0', 'mimeo_dest.updater_test_dest_serial_view', p_pk_name := '{"col1"}', p_pk_type := '{"int"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';

-- DML
SELECT diag('Running maker for destination: mimeo_source.dml_test_source');
SELECT dml_maker('mimeo_source.dml_test_source', data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.dml_test_dest');
SELECT dml_maker('mimeo_source.dml_test_source2', data_source_id, 'mimeo_dest.dml_test_dest') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
-- Testing insert_on_fetch = false
UPDATE refresh_config_dml SET insert_on_fetch = false WHERE dest_table = 'mimeo_dest.dml_test_dest';
SELECT diag('Running maker for destination: mimeo_dest.dml_test_dest_multi');
SELECT dml_maker('mimeo_source.dml_test_source', data_source_id, 'mimeo_dest.dml_test_dest_multi') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.dml_test_dest_nodata');
SELECT dml_maker('mimeo_source.dml_test_source_nodata', data_source_id, 'mimeo_dest.dml_test_dest_nodata', p_pulldata := false) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.dml_test_dest_filter');
SELECT dml_maker('mimeo_source.dml_test_source_filter', data_source_id, 'mimeo_dest.dml_test_dest_filter', p_filter := '{"col1","col2"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.dml_test_dest_condition');
SELECT dml_maker('mimeo_source.dml_test_source_condition', data_source_id, 'mimeo_dest.dml_test_dest_condition', p_condition := 'WHERE col1 > 9000') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.dml_test_source_empty');
SELECT dml_maker('mimeo_source.dml_test_source_empty', data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.Dml-Test-Source');
SELECT dml_maker('mimeo_source.Dml-Test-Source', data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';

-- Logdel
SELECT diag('Running maker for destination: mimeo_source.logdel_test_source');
SELECT logdel_maker('mimeo_source.logdel_test_source', data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.logdel_test_dest');
SELECT logdel_maker('mimeo_source.logdel_test_source2', data_source_id, 'mimeo_dest.logdel_test_dest') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
-- Testing insert_on_fetch = false
UPDATE refresh_config_logdel SET insert_on_fetch = false WHERE dest_table = 'mimeo_dest.logdel_test_dest';

SELECT diag('Running maker for destination: mimeo_dest.logdel_test_dest_multi');
SELECT logdel_maker('mimeo_source.logdel_test_source', data_source_id, 'mimeo_dest.logdel_test_dest_multi') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.logdel_test_dest_nodata');
SELECT logdel_maker('mimeo_source.logdel_test_source_nodata', data_source_id, 'mimeo_dest.logdel_test_dest_nodata', p_pulldata := false) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.logdel_test_dest_filter');
SELECT logdel_maker('mimeo_source.logdel_test_source_filter', data_source_id, 'mimeo_dest.logdel_test_dest_filter', p_filter := '{"col1","col2"}') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_dest.logdel_test_dest_condition');
SELECT logdel_maker('mimeo_source.logdel_test_source_condition', data_source_id, 'mimeo_dest.logdel_test_dest_condition', p_condition := 'WHERE col1 > 9000') FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.logdel_test_source_empty');
SELECT logdel_maker('mimeo_source.logdel_test_source_empty', data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';
SELECT diag('Running maker for destination: mimeo_source.LogDel-Test-Source');
SELECT logdel_maker('mimeo_source.LogDel-Test-Source', data_source_id) FROM dblink_mapping_mimeo WHERE username = 'mimeo_test';

SELECT pass('Maker functions finished. Sleeping for 40 seconds to ensure gap for incremental tests...');
SELECT pg_sleep(40);

SELECT * FROM finish();