· 8 years ago · Dec 05, 2017, 03:10 AM
1-- #1
2CREATE TABLE SHOP (
3 ID BIGINT NOT NULL PRIMARY KEY,
4 NAME VARCHAR(100) NOT NULL
5);
6
7CREATE TABLE CARD_TYPE (
8 ID INTEGER NOT NULL PRIMARY KEY,
9 NAME_CARD VARCHAR(54) NOT NULL,
10 START_DATE TIMESTAMP NOT NULL,
11 END_DATE TIMESTAMP NOT NULL
12);
13
14CREATE TABLE EVENT_TYPE (
15 EVENT_ID BIGINT NOT NULL PRIMARY KEY,
16 EVENT_NAME VARCHAR NOT NULL,
17 STATUS BOOLEAN NOT NULL
18);
19
20CREATE TABLE ADDRESS (
21 ID BIGINT NOT NULL PRIMARY KEY,
22 NAME VARCHAR NOT NULL,
23 PAR_ID BIGINT REFERENCES ADDRESS (id)
24);
25
26-- parent of all dictionary tables
27CREATE TABLE DICTIONARY (
28 ID BIGINT NOT NULL PRIMARY KEY,
29 NAME VARCHAR(100),
30 START_DATE TIMESTAMP,
31 END_DATE TIMESTAMP,
32 STATUS BOOLEAN,
33 PAR_ID BIGINT,
34 REL TEXT -- i.e. EVENT_TYPE, ADDRESS, etc.
35);
36
37
38/*
39 Refactor existing tables.
40 We could inherit while creating the above tables
41 instead of altering.
42 */
43
44ALTER TABLE SHOP
45 ADD COLUMN START_DATE TIMESTAMP;
46
47ALTER TABLE SHOP
48 ADD COLUMN END_DATE TIMESTAMP;
49
50ALTER TABLE SHOP
51 ADD COLUMN STATUS BOOLEAN;
52
53ALTER TABLE SHOP
54 ADD COLUMN PAR_ID BIGINT;
55
56ALTER TABLE SHOP
57 ADD COLUMN REL TEXT;
58
59ALTER TABLE SHOP
60 INHERIT DICTIONARY;
61
62--
63
64ALTER TABLE CARD_TYPE
65 ALTER COLUMN ID TYPE BIGINT;
66
67ALTER TABLE CARD_TYPE
68 RENAME NAME_CARD TO NAME;
69
70ALTER TABLE CARD_TYPE
71 ALTER COLUMN NAME TYPE VARCHAR(100);
72
73ALTER TABLE CARD_TYPE
74 ADD COLUMN STATUS BOOLEAN;
75
76ALTER TABLE CARD_TYPE
77 ADD COLUMN PAR_ID BIGINT;
78
79ALTER TABLE CARD_TYPE
80 ADD COLUMN REL TEXT;
81
82ALTER TABLE CARD_TYPE
83 INHERIT DICTIONARY;
84
85--
86
87ALTER TABLE EVENT_TYPE
88 RENAME EVENT_ID TO ID;
89
90ALTER TABLE EVENT_TYPE
91 RENAME EVENT_NAME TO NAME;
92
93ALTER TABLE EVENT_TYPE
94 ALTER COLUMN NAME TYPE VARCHAR(100);
95
96ALTER TABLE EVENT_TYPE
97 ADD COLUMN START_DATE TIMESTAMP;
98
99ALTER TABLE EVENT_TYPE
100 ADD COLUMN END_DATE TIMESTAMP;
101
102ALTER TABLE EVENT_TYPE
103 ADD COLUMN PAR_ID BIGINT;
104
105ALTER TABLE EVENT_TYPE
106 ADD COLUMN REL TEXT;
107
108ALTER TABLE EVENT_TYPE
109 INHERIT DICTIONARY;
110
111--
112
113ALTER TABLE ADDRESS
114 ALTER COLUMN NAME TYPE VARCHAR(100);
115
116ALTER TABLE ADDRESS
117 ADD COLUMN START_DATE TIMESTAMP;
118
119ALTER TABLE ADDRESS
120 ADD COLUMN END_DATE TIMESTAMP;
121
122ALTER TABLE ADDRESS
123 ADD COLUMN STATUS BOOLEAN;
124
125ALTER TABLE ADDRESS
126 ADD COLUMN REL TEXT;
127
128ALTER TABLE ADDRESS
129 INHERIT DICTIONARY;
130
131-- #2
132
133-- insert 1000 event type records
134INSERT INTO EVENT_TYPE (ID, NAME, STATUS, REL)
135 SELECT
136 generate_series(1, 1000) AS ID,
137 md5(random() :: VARCHAR) AS NAME,
138 cast(cast(random() AS INTEGER) AS BOOLEAN),
139 'EVENT_TYPE';
140
141-- checking
142-- SELECT COUNT(*) FROM ONLY DICTIONARY;
143
144-- Event types with active status query
145-- EXPLAIN ( ANALYSE )
146SELECT
147 ID,
148 NAME,
149 STATUS FROM DICTIONARY
150WHERE REL = 'EVENT_TYPE' AND STATUS;
151
152/*
153 Create index on specific business requirements, that is,
154 query for event types with active status.
155 In case of 1000 random records (with ~50% of active status events)
156 execution time is ~1ms vs. ~9ms
157*/
158CREATE INDEX dictionary_event_type_is_active_idx
159 ON event_type (REL, STATUS)
160 WHERE STATUS AND REL = 'EVENT_TYPE';
161
162-- drop above index
163-- DROP INDEX dictionary_event_type_is_active_idx;
164
165-- #3 (statistics)
166
167CREATE TABLE SHOP_EVENT_STAT (
168 -- we don't know fo sure about how many records will be
169 ID BIGSERIAL NOT NULL PRIMARY KEY,
170 -- references to a shop and an event
171 SHOP_ID BIGINT NOT NULL,
172 EVENT_ID BIGINT NOT NULL,
173 -- not neccessary (optional)
174 EVENT_NAME VARCHAR(100) NOT NULL,
175 -- date
176 EVENT_DATE DATE NOT NULL DEFAULT current_date,
177 -- events counter
178 EVENTS_COUNT SMALLINT NOT NULL,
179
180 FOREIGN KEY (SHOP_ID) REFERENCES SHOP (ID),
181 FOREIGN KEY (EVENT_ID) REFERENCES EVENT_TYPE (ID)
182);
183
184-- Trigger function
185CREATE OR REPLACE FUNCTION shop_events_fun()
186 RETURNS TRIGGER AS
187$BODY$
188DECLARE
189 schName TEXT := 'task2'; -- schema name
190 masterTbl TEXT := 'SHOP_EVENT_STAT'; -- master table name
191 childTbl TEXT; -- child table name
192 records INTEGER; -- number of events (events count)
193BEGIN
194
195 -- forming child table name
196 childTbl := masterTbl || '_' || replace(NEW.EVENT_DATE :: TEXT, '-', '_');
197
198 RAISE NOTICE 'Child table name: %', childTbl;
199
200 -- check if child table already exists
201 IF EXISTS(
202 SELECT tablename FROM pg_tables
203 WHERE pg_tables.schemaname = schName AND UPPER(pg_tables.tablename) = childTbl
204 )
205 THEN
206 RAISE NOTICE 'Child table exists!';
207
208 -- query events count
209 EXECUTE 'SELECT EVENTS_COUNT FROM ' || schName || '.' || childTbl
210 || ' WHERE EVENT_ID = ''' || NEW.EVENT_ID || ''' AND SHOP_ID = ' || NEW.SHOP_ID
211 INTO records;
212
213 RAISE NOTICE 'EVENTS_COUNT: %', records;
214
215 IF (records IS NOT NULL)
216 THEN
217 -- increment events counter if it's not null
218 EXECUTE
219 'UPDATE ' || schName || '.' || childTbl
220 || ' SET EVENTS_COUNT = ' || records + NEW.EVENTS_COUNT
221 || ' WHERE EVENT_ID = ' || NEW.EVENT_ID
222 || ' AND SHOP_ID = ' || NEW.SHOP_ID;
223 ELSE
224 -- insert a new record
225 EXECUTE 'INSERT INTO ' || schName || '.' || childTbl
226 || ' (SHOP_ID, EVENT_ID, EVENT_NAME, EVENT_DATE, EVENTS_COUNT) VALUES ('
227 || NEW.SHOP_ID || ', ' || NEW.EVENT_ID || ', ''' || NEW.EVENT_NAME || ''', ''' || NEW.EVENT_DATE || ''', '
228 || NEW.EVENTS_COUNT || ')';
229 END IF;
230
231 ELSE
232 RAISE NOTICE 'Child table does not exist';
233
234 -- create child table
235 EXECUTE 'CREATE TABLE ' || schName || '.' || childTbl
236 || ' (LIKE ' || schName || '.' || masterTbl
237 || ' INCLUDING ALL) INHERITS (' || schName || '.' || masterTbl || ');';
238
239 EXECUTE 'ALTER TABLE ' || schName || '.' || childTbl
240 || ' ADD CONSTRAINT partition_check CHECK (EVENT_DATE = '''
241 || NEW.EVENT_DATE || ''');';
242
243 -- insert a new record
244 EXECUTE 'INSERT INTO ' || schName || '.' || childTbl
245 || ' (SHOP_ID, EVENT_ID, EVENT_NAME, EVENT_DATE, EVENTS_COUNT) VALUES ('
246 || NEW.SHOP_ID || ', ' || NEW.EVENT_ID || ', ''' || NEW.EVENT_NAME || ''', ''' || NEW.EVENT_DATE || ''', '
247 || NEW.EVENTS_COUNT || ')';
248
249 END IF;
250
251 RETURN NULL;
252END;
253$BODY$
254LANGUAGE plpgsql VOLATILE;
255
256-- Trigger
257CREATE TRIGGER shop_event_stat_trigger
258BEFORE INSERT
259 ON SHOP_EVENT_STAT
260FOR EACH ROW
261EXECUTE PROCEDURE shop_events_fun();
262
263-- add some shops
264INSERT INTO SHOP (ID, NAME, REL)
265 SELECT
266 generate_series(1, 30000) AS ID,
267 md5(random() :: VARCHAR) AS NAME,
268 'SHOP';
269
270-- insert some statistics data
271INSERT INTO SHOP_EVENT_STAT (SHOP_ID, EVENT_ID, EVENT_NAME, EVENT_DATE, EVENTS_COUNT)
272VALUES (1, 1, (SELECT NAME FROM EVENT_TYPE
273WHERE ID = 1), current_date, 10);
274
275INSERT INTO SHOP_EVENT_STAT (SHOP_ID, EVENT_ID, EVENT_NAME, EVENT_DATE, EVENTS_COUNT)
276VALUES (1, 3, (SELECT NAME FROM EVENT_TYPE
277WHERE ID = 3), current_date, 5);
278
279INSERT INTO SHOP_EVENT_STAT (SHOP_ID, EVENT_ID, EVENT_NAME, EVENT_DATE, EVENTS_COUNT)
280VALUES (1, 1, (SELECT NAME FROM EVENT_TYPE
281WHERE ID = 1), current_date, 54);
282
283INSERT INTO SHOP_EVENT_STAT (SHOP_ID, EVENT_ID, EVENT_NAME, EVENT_DATE, EVENTS_COUNT)
284VALUES (30, 34, (SELECT NAME FROM EVENT_TYPE
285WHERE ID = 34), current_date, 117);
286
287-- query w/o join (if we include EVENT_NAME column in statistics table and there's no need in event's status)
288SELECT
289 stat.EVENT_ID,
290 stat.EVENT_NAME,
291 stat.EVENTS_COUNT FROM
292 SHOP_EVENT_STAT AS stat
293WHERE SHOP_ID = 30 AND EVENT_DATE = current_date;
294
295-- otherwise we have to join
296EXPLAIN ( ANALYSE )
297SELECT
298 stat.EVENT_ID,
299 stat.EVENT_NAME,
300 stat.EVENTS_COUNT,
301 e_type.STATUS
302FROM
303 SHOP_EVENT_STAT AS stat
304 INNER JOIN EVENT_TYPE AS e_type
305 ON stat.EVENT_ID = e_type.ID
306WHERE stat.SHOP_ID = 30 AND EVENT_DATE = current_date;
307
308-- in order to optimize create index on th above request requirements
309CREATE INDEX shop_events_idx
310 ON SHOP_EVENT_STAT (SHOP_ID, EVENT_DATE);
311
312-- just checking if it works (on a few records)
313SET ENABLE_SEQSCAN TO 'off';
314SHOW ENABLE_SEQSCAN;