· 5 years ago · Aug 21, 2020, 09:40 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 created_at DATE NOT NULL,
8 updated_at DATE,
9 deleted_at DATE,
10 domain VARCHAR(70) NOT NULL,
11 name_servers TEXT[] NOT NULL
12-- , PRIMARY KEY (domain, tld_id)
13);
14CREATE UNLOGGED TABLE IF NOT EXISTS t2 (
15 domain VARCHAR(70) NOT NULL,
16 name_servers TEXT[] NOT NULL
17-- , PRIMARY KEY (domain)
18);
19
20-- очищаем
21truncate t1, t2;
22
23-- вставляем в t1 (пример старой базы, вчерашняя выгрузка)
24INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (2, '0', '2020-07-26', null, null, '0--o--0', '{ns1.google.com,ns2.google.com}');
25INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (1, '0', '2020-07-26', null, '2020-08-10', '0-0', '{}');
26INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (1, '0', '2020-07-26', null, null, '0-0-0', '{ns2.google.com,ns3.google.com}');
27INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (1, '0', '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', '{ns3.google.com,ns4.google.com}');
28INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (1, '0', '2020-07-26', null, null, '0-00-0000', '{ns1.google.com,ns2.google.com}');
29INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (1, '0', '2020-07-26', null, null, '0-0b', '{ns3.google.com,ns4.google.com}');
30INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (1, '0', '2020-07-26', null, null, '0-1-5-1', '{ns1.google.com,ns2.google.com}');
31INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (1, '0', '2020-07-26', null, null, '0-1-8', '{ns1.google.com,ns2.google.com}');
32INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (1, '0', '2020-07-26', null, null, '0-10', '{ns1.google.com,ns2.google.com,ns3.google.com,ns4.google.com}');
33INSERT INTO t1 (tld_id, letter, created_at, updated_at, deleted_at, domain, name_servers) VALUES (1, '0', '2020-07-26', null, '2020-08-10', '0-1cc-85', '{}');
34
35-- вставляем в t2 (пример новой базы, сегодняшняя выгрузка)
36INSERT INTO t2 (domain, name_servers) VALUES ('0-0b', '{ns3.google.com,ns4.google.com}');
37INSERT INTO t2 (domain, name_servers) VALUES ('0-1-5-1', '{ns1.google.com,ns2.google.com}');
38INSERT INTO t2 (domain, name_servers) VALUES ('0-1-8', '{ns1.google.com,ns2.google.com}');
39INSERT INTO t2 (domain, name_servers) VALUES ('0-10', '{ns1.google.com,ns3.google.com,ns4.google.com}');
40INSERT INTO t2 (domain, name_servers) VALUES ('0-1cc-85', '{ns4.google.com,ns5.google.com}');
41INSERT INTO t2 (domain, name_servers) VALUES ('0-1u1aa', '{ns2.google.com,ns3.google.com}');
42INSERT INTO t2 (domain, name_servers) VALUES ('0-1u2ca', '{ns2.google.com,ns3.google.com}');
43INSERT INTO t2 (domain, name_servers) VALUES ('0-1u3sa', '{ns2.google.com,ns3.google.com}');
44INSERT INTO t2 (domain, name_servers) VALUES ('0-2', '{ns2.google.com,ns3.google.com}');
45INSERT INTO t2 (domain, name_servers) VALUES ('0-33pro', '{ns1.google.com,ns2.google.com}');
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.id is null;
66 Y IS NOT DISTINCT FROM NULL; -- "антиджойн"
67
68
69-- обновляем оставшиеся
70UPDATE
71 t1 D
72SET
73 name_servers = T.name_servers,
74 updated_at = now()
75FROM
76 t2 T
77WHERE
78 D.tld_id = 1 AND
79 D.domain = T.domain AND
80 D.deleted_at is null AND
81 -- а тут можно допилить
82 (D.name_servers) IS DISTINCT FROM (T.name_servers);
83
84
85-- обновляем удаленные
86UPDATE
87 t1 D
88SET
89 name_servers = T.name_servers,
90 created_at = now(),
91 updated_at = now(),
92 deleted_at = null
93FROM
94 t2 T
95WHERE
96 D.tld_id = 1 AND
97 D.domain = T.domain AND
98 D.deleted_at is not null AND
99 -- и тут можно допилить
100 (D.name_servers) IS DISTINCT FROM (T.name_servers);
101
102
103-- вставляем отсутствующие
104INSERT INTO
105 t1 (name_servers, domain, letter, tld_id, created_at)
106SELECT
107 T.name_servers, T.domain, '0', 1, '2020-08-20'
108FROM
109 t2 T
110LEFT JOIN
111 t1 D
112 USING(domain)
113WHERE
114 D IS NOT DISTINCT FROM NULL;
115
116
117-- потом хочется делать вот такие запросы:
118SELECT
119 count(domain)
120FROM t1
121WHERE
122 tld_id = 1 AND
123 ARRAY['ns4.google.com', 'ns3.google.com'] <@ name_servers AND
124 (updated_at = '2020-08-21' OR created_at = '2020-08-21');
125
126