· 5 years ago · Aug 19, 2020, 09:42 PM
1drop table t1;
2drop table t2;
3
4CREATE UNLOGGED TABLE IF NOT EXISTS t1 (
5 tld_id INTEGER NOT NULL,
6 letter VARCHAR(1) NOT NULL,
7 name_servers VARCHAR[] NOT NULL,
8 created_at DATE DEFAULT NOW() NOT NULL,
9 updated_at DATE,
10 deleted_at DATE,
11 domain VARCHAR(70) NOT NULL,
12 PRIMARY KEY (domain, tld_id)
13);
14CREATE UNLOGGED TABLE IF NOT EXISTS t2 (
15 name_servers VARCHAR[] NOT NULL,
16 domain VARCHAR(70) NOT NULL,
17 PRIMARY KEY (domain)
18);
19
20-- очищаем
21truncate t1, t2;
22
23-- вставляем в t1 (пример старой базы, вчерашняя выгрущка)
24INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (2, '0', '{ns1.google.com,ns2.google.com}', '2020-07-26', null, null, '0--o--0');
25INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (1, '0', '{}', '2020-07-26', null, '2020-08-10', '0-0');
26INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (1, '0', '{ns2.google.com,ns3.google.com}', '2020-07-26', null, null, '0-0-0');
27INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (1, '0', '{ns3.google.com,ns4.google.com}', '2020-07-26', null, null, '0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0-0');
28INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (1, '0', '{ns1.google.com,ns2.google.com}', '2020-07-26', null, null, '0-00-0000');
29INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (1, '0', '{ns3.google.com,ns4.google.com}', '2020-07-26', null, null, '0-0b');
30INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (1, '0', '{ns1.google.com,ns2.google.com}', '2020-07-26', null, null, '0-1-5-1');
31INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (1, '0', '{ns1.google.com,ns2.google.com}', '2020-07-26', null, null, '0-1-8');
32INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (1, '0', '{ns1.google.com,ns2.google.com,ns3.google.com,ns4.google.com}', '2020-07-26', null, null, '0-10');
33INSERT INTO t1 (tld_id, letter, name_servers, created_at, updated_at, deleted_at, domain) VALUES (1, '0', '{}', '2020-07-26', null, '2020-08-10', '0-1cc-85');
34
35-- вставляем в t2 (пример новой базы, сегодняшняя выгрузка)
36INSERT INTO t2 (name_servers, domain) VALUES ('{ns3.google.com,ns4.google.com}', '0-0b');
37INSERT INTO t2 (name_servers, domain) VALUES ('{ns1.google.com,ns2.google.com}', '0-1-5-1');
38INSERT INTO t2 (name_servers, domain) VALUES ('{ns1.google.com,ns2.google.com}', '0-1-8');
39INSERT INTO t2 (name_servers, domain) VALUES ('{ns1.google.com,ns3.google.com,ns4.google.com}', '0-10');
40INSERT INTO t2 (name_servers, domain) VALUES ('{ns4.google.com,ns5.google.com}', '0-1cc-85');
41INSERT INTO t2 (name_servers, domain) VALUES ('{ns2.google.com,ns3.google.com}', '0-1u1aa');
42INSERT INTO t2 (name_servers, domain) VALUES ('{ns2.google.com,ns3.google.com}', '0-1u2ca');
43INSERT INTO t2 (name_servers, domain) VALUES ('{ns2.google.com,ns3.google.com}', '0-1u3sa');
44INSERT INTO t2 (name_servers, domain) VALUES ('{ns2.google.com,ns3.google.com}', '0-2');
45INSERT INTO t2 (name_servers, domain) VALUES ('{ns1.google.com,ns2.google.com}', '0-33pro');
46
47
48--помечаем удаленным, но физически не удаляем
49UPDATE
50 t1 D
51SET
52 name_servers = '{}',
53 updated_at = null,
54 deleted_at = now()
55FROM
56 t1 X
57LEFT JOIN
58 t2 Y
59 USING (domain)
60WHERE
61 D.tld_id = 1 AND
62 D.domain = X.domain AND
63 D.tld_id = X.tld_id AND
64 X.deleted_at is null AND
65 Y IS NOT DISTINCT FROM NULL; -- "антиджойн"
66
67-- обновляем оставшиеся (которые не удалены ранее)
68UPDATE
69 t1 D
70SET
71 name_servers = T.name_servers,
72 updated_at = now()
73FROM
74 t2 T
75WHERE
76 D.tld_id = 1 AND
77 D.domain = T.domain AND
78 D.deleted_at is null AND
79 -- а тут можно допилить
80 (D.name_servers) IS DISTINCT FROM (T.name_servers);
81
82
83-- обновляем удаленные ранее
84UPDATE
85 t1 D
86SET
87 name_servers = T.name_servers,
88 created_at = now(),
89 updated_at = now(),
90 deleted_at = null
91FROM
92 t2 T
93WHERE
94 D.tld_id = 1 AND
95 D.domain = T.domain AND
96 D.deleted_at is not null AND
97 -- и тут можно допилить
98 (D.name_servers) IS DISTINCT FROM (T.name_servers);
99
100
101-- вставляем отсутствующие в новой выгрузке
102INSERT INTO
103 t1 (name_servers, domain, letter, tld_id)
104SELECT
105 T.name_servers, T.domain, '0', 1
106FROM
107 t2 T
108LEFT JOIN
109 t1 D
110 USING(domain)
111WHERE
112 D IS NOT DISTINCT FROM NULL;
113