-
Notifications
You must be signed in to change notification settings - Fork 2
/
init_test_data.sql
149 lines (138 loc) · 7.08 KB
/
init_test_data.sql
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
TRUNCATE core_data;
TRUNCATE batch CASCADE;
TRUNCATE states CASCADE;
/* make two states, NY and PA */
INSERT INTO states VALUES ('NY', 'New York');
INSERT INTO states VALUES ('PA', 'Pennsylvania');
/* non-daily batch, 3/20 - final state */
DO $$
DECLARE last_batch_id BIGINT;
BEGIN
/* This returns the batch ID, can be used in other inserts. */
INSERT INTO batch
(created_at, shift_lead, batch_note, is_published, is_revision, data_entry_type) VALUES
('2020-03-20 10:00:00', 'JK', '3/20 morning', TRUE, FALSE, 'push')
RETURNING batch_id INTO last_batch_id;
/* Insert some data for this batch */
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('PA', '2020-03-20', '2020-03-20 09:55:00', '2020-03-20', 130, 'JK', 'PA morning', last_batch_id);
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('NY', '2020-03-20', '2020-03-20 09:56:00', '2020-03-20', 168, 'JK', 'NY morning', last_batch_id);
END $$;
/* daily batch, 3/20 - final state */
DO $$
DECLARE last_batch_id BIGINT;
BEGIN
INSERT INTO batch
(created_at, shift_lead, batch_note, is_published, is_revision, data_entry_type) VALUES
('2020-03-20 15:00:00', 'JK', '3/20 afternoon, daily', TRUE, FALSE, 'daily_push')
RETURNING batch_id INTO last_batch_id;
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('PA', '2020-03-20 14:00:00', '2020-03-20 14:55:00', '2020-03-20', 131, 'JK', 'PA afternoon', last_batch_id);
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('NY', '2020-03-20 14:00:00', '2020-03-20 14:56:00', '2020-03-20', 170, 'JK', 'NY afternoon', last_batch_id);
END $$;
/* non-daily batch, 3/20, final state */
DO $$
DECLARE last_batch_id BIGINT;
BEGIN
INSERT INTO batch
(created_at, shift_lead, batch_note, is_published, is_revision, data_entry_type) VALUES
('2020-03-20 20:00:00', 'JK', '3/20 night', TRUE, FALSE, 'push')
RETURNING batch_id INTO last_batch_id;
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('PA', '2020-03-20 17:00:00', '2020-03-20 17:55:00', '2020-03-20', 135, 'JK', 'PA night', last_batch_id);
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('NY', '2020-03-20 17:00:00', '2020-03-20 17:56:00', '2020-03-20', 175, 'JK', 'NY night', last_batch_id);
END $$;
/* next day, revise some data from previous day */
DO $$
DECLARE last_batch_id BIGINT;
BEGIN
INSERT INTO batch
(created_at, shift_lead, batch_note, is_published, is_revision, data_entry_type) VALUES
('2020-03-21 08:00:00', 'JK', 'Missed 5 tests in NY on 3/20', TRUE, TRUE, 'daily_push')
RETURNING batch_id INTO last_batch_id;
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('NY', '2020-03-21 00:00:00', '2020-03-21 07:55:00', '2020-03-20', 175, 'JK', 'Missed 5 tests in NY for 3/20 daily', last_batch_id);
END $$;
/* non-daily batch, next day 3/21, final state */
DO $$
DECLARE last_batch_id BIGINT;
BEGIN
INSERT INTO batch
(created_at, shift_lead, batch_note, is_published, is_revision, data_entry_type) VALUES
('2020-03-21 10:00:00', 'JK', '3/21 morning', TRUE, FALSE, 'push')
RETURNING batch_id INTO last_batch_id;
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('PA', '2020-03-21 00:00:00', '2020-03-21 09:55:00', '2020-03-21', 140, 'JK', 'PA morning', last_batch_id);
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('NY', '2020-03-21 00:00:00', '2020-03-21 09:56:00', '2020-03-21', 190, 'JK', 'NY morning', last_batch_id);
END $$;
/* daily batch, 3/21, final state */
DO $$
DECLARE last_batch_id BIGINT;
BEGIN
INSERT INTO batch
(created_at, shift_lead, batch_note, is_published, is_revision, data_entry_type) VALUES
('2020-03-21 15:00:00', 'JK', '3/21 afternoon, daily', TRUE, FALSE, 'daily_push')
RETURNING batch_id INTO last_batch_id;
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('PA', '2020-03-21 14:00:00', '2020-03-21 14:55:00', '2020-03-21', 150, 'JK', 'PA afternoon', last_batch_id);
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('NY', '2020-03-21 14:00:00', '2020-03-21 14:56:00', '2020-03-21', 200, 'JK', 'NY afternoon', last_batch_id);
END $$;
/* non-daily batch, 3/21, preview state */
DO $$
DECLARE last_batch_id BIGINT;
BEGIN
INSERT INTO batch
(created_at, shift_lead, batch_note, is_published, is_revision, data_entry_type) VALUES
('2020-03-21 20:00:00', 'JK', '3/21 night', FALSE, FALSE, 'push')
RETURNING batch_id INTO last_batch_id;
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('PA', '2020-03-21 17:00:00', '2020-03-21 17:55:00', '2020-03-21', 160, 'JK', 'PA afternoon', last_batch_id);
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('NY', '2020-03-21 17:00:00', '2020-03-21 17:56:00', '2020-03-21', 210, 'JK', 'NY afternoon', last_batch_id);
END $$;
/* non-daily batch 2nd try, 3/21, preview state */
DO $$
DECLARE last_batch_id BIGINT;
BEGIN
INSERT INTO batch
(created_at, shift_lead, batch_note, is_published, is_revision, data_entry_type) VALUES
('2020-03-21 20:05:00', 'JK', '3/21 night', FALSE, FALSE, 'push')
RETURNING batch_id INTO last_batch_id;
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('PA', '2020-03-21 17:00:00', '2020-03-21 17:55:00', '2020-03-21', 170, 'JK', 'PA afternoon', last_batch_id);
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('NY', '2020-03-21 17:00:00', '2020-03-21 17:56:00', '2020-03-21', 210, 'JK', 'NY afternoon', last_batch_id);
END $$;
/* Revise data from the 20th again. This will cause issues with naive queries that just get the most recent batchid */
DO $$
DECLARE last_batch_id BIGINT;
BEGIN
INSERT INTO batch
(created_at, shift_lead, batch_note, is_published, is_revision, data_entry_type) VALUES
('2020-03-21 20:05:00', 'AS', 'Fixing PA 3/20 data', TRUE, TRUE, 'daily_push')
RETURNING batch_id INTO last_batch_id;
INSERT INTO core_data
(state_name, last_update_time, last_check_time, data_date, tests, checker, public_notes, batch_id) VALUES
('PA', '2020-03-21 17:00:00', '2020-03-21 17:55:00', '2020-03-20', 167, 'AS', 'Missed tests in PA 3/20', last_batch_id);
END $$;
SELECT * FROM core_data INNER JOIN batch ON core_data.batch_id = batch.batch_id;