d68PuRSM

1ERROR | 2 | pg_query(): Query failed: ERROR: syntax error at or near "MSK" LINE 7: ...date_part('year',current_timestamp AT TIME ZONE ''MSK'')::IN... ^ | /home/pistat/src/Engine/DataProvider/DB/Database.php:66
2Подробнее
3Array
4(
5    [0] => Array
6        (
7            [function] => error_handler
8            [args] => Array
9                (
10                    [0] => 2
11                    [1] => pg_query(): Query failed: ERROR:  syntax error at or near "MSK"
12LINE 7: ...date_part('year',current_timestamp AT TIME ZONE ''MSK'')::IN...
13                                                             ^
14                    [2] => /home/pistat/src/Engine/DataProvider/DB/Database.php
15                    [3] => 66
16                    [4] => Array
17                        (
18                            [query] => BEGIN;
19CREATE SCHEMA project_3;
20GRANT ALL ON SCHEMA project_3 TO postgres;
21GRANT ALL ON SCHEMA project_3 TO public;
22CREATE DOMAIN project_3.PTIME_TYPE TIMESTAMPTZ NOT NULL DEFAULT current_timestamp;
23CREATE DOMAIN project_3.TIME_TYPE TIMESTAMPTZ;
24CREATE DOMAIN project_3.YEAR_TYPE INTEGER NOT NULL DEFAULT date_part('year',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
25CREATE DOMAIN project_3.MONTH_TYPE INTEGER NOT NULL DEFAULT date_part('month',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
26CREATE DOMAIN project_3.WEEK_TYPE INTEGER NOT NULL DEFAULT date_part('week',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
27CREATE DOMAIN project_3.DAY_TYPE INTEGER NOT NULL DEFAULT date_part('day',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
28CREATE DOMAIN project_3.HOUR_TYPE INTEGER NOT NULL DEFAULT date_part('hour',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
29
30CREATE TABLE project_3.params(
31	name NAME_TYPE PRIMARY KEY,
32	"value" VARCHAR
33);
34
35
36CREATE TABLE project_3.names(
37	id SERIAL NOT NULL PRIMARY KEY,
38	"type" INTEGER NOT NULL DEFAULT 0,
39	subtype INTEGER NOT NULL DEFAULT 0,
40	"class" INTEGER NOT NULL DEFAULT 0,
41	name NAME_TYPE
42);
43CREATE INDEX ON project_3.names("type");
44CREATE INDEX ON project_3.names(subtype);
45CREATE INDEX ON project_3.names(name);
46CREATE INDEX ON project_3.names(lower(name));
47
48CREATE TABLE project_3."values"(
49	id BIGSERIAL NOT NULL PRIMARY KEY,
50	"type" INTEGER NOT NULL DEFAULT 0,
51	subtype INTEGER DEFAULT NULL REFERENCES project_3.names ON DELETE CASCADE,
52	"value" NAME_TYPE,
53	num_value NUMERIC DEFAULT NULL,
54	text_value VARCHAR DEFAULT NULL
55);
56CREATE INDEX ON project_3."values"("type");
57CREATE INDEX ON project_3."values"(subtype);
58CREATE INDEX ON project_3."values"("value");
59CREATE INDEX ON project_3."values"(num_value);
60CREATE INDEX ON project_3."values"(lower("value"));
61
62CREATE OR REPLACE FUNCTION project_3.values_insert_trigger()
63    RETURNS TRIGGER AS $$
64DECLARE
65    tbl VARCHAR := '';
66    tblexists VARCHAR := '';
67BEGIN
68	tbl := 'project_3.values_' || NEW."type";
69	SELECT to_regclass((tbl)::varchar) INTO tblexists;
70	IF tblexists IS NULL THEN
71		EXECUTE 'CREATE TABLE ' || tbl || '(
72			CHECK ("type"=' || NEW."type" || '), 
73			CONSTRAINT val_' || NEW."type" || '_pk PRIMARY KEY(id), 
74			CONSTRAINT val_' || NEW."type" || '_fkey FOREIGN KEY (subtype) REFERENCES project_3.names ON DELETE CASCADE
75			) INHERITS (project_3.values) WITH (OIDS=FALSE);';
76		EXECUTE 'CREATE INDEX ON ' || tbl || '("type");';
77		EXECUTE 'CREATE INDEX ON ' || tbl || '(subtype);';
78		EXECUTE 'CREATE INDEX ON ' || tbl || '("value");';
79		EXECUTE 'CREATE INDEX ON ' || tbl || '(lower("value"));';
80		EXECUTE 'CREATE INDEX ON ' || tbl || '(num_value);';
81	END IF;
82	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
83	RETURN NULL;
84END;
85$$ LANGUAGE plpgsql;
86
87CREATE TRIGGER insert_values_trigger BEFORE INSERT ON project_3."values"
88    FOR EACH ROW EXECUTE PROCEDURE project_3.values_insert_trigger();
89
90
91CREATE DOMAIN project_3.PNAME_TYPE AS INTEGER NOT NULL;
92CREATE DOMAIN project_3.PVAL_TYPE AS BIGINT NOT NULL;
93
94
95CREATE TABLE project_3.versions(
96	id SERIAL NOT NULL PRIMARY KEY,
97	version NAME_TYPE,
98	ctime CTIME_TYPE,
99  	flags integer NOT NULL DEFAULT 0
100);
101CREATE INDEX ON project_3.versions(version);
102CREATE INDEX ON project_3.versions(flags);
103
104CREATE TABLE project_3.persons(
105	id SERIAL NOT NULL PRIMARY KEY
106);
107
108CREATE TABLE project_3.ips(
109	id BIGSERIAL NOT NULL PRIMARY KEY,
110	ip VARCHAR(64) NOT NULL UNIQUE,
111	country VARCHAR(2)
112);
113CREATE INDEX ON project_3.ips(country);
114
115--users
116CREATE TABLE project_3.devices(
117	id SERIAL NOT NULL PRIMARY KEY,
118	udid VARCHAR(128) NOT NULL UNIQUE,
119	ctime CTIME_TYPE
120);
121
122CREATE TABLE project_3.device_params(
123	device INTEGER NOT NULL REFERENCES project_3.devices ON DELETE CASCADE,
124	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
125	"value" project_3.PVAL_TYPE
126);
127CREATE INDEX ON project_3.device_params(device);
128CREATE INDEX ON project_3.device_params(name);
129CREATE INDEX ON project_3.device_params("value");
130
131
132CREATE TABLE project_3.user_states(
133	id BIGSERIAL NOT NULL PRIMARY KEY,
134	"user" INTEGER NOT NULL, 
135	ctime CTIME_TYPE
136);
137
138CREATE TABLE project_3.state_params(
139	state INTEGER NOT NULL REFERENCES project_3.user_states ON DELETE CASCADE,
140	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
141	"value" project_3.PVAL_TYPE
142);
143CREATE INDEX ON project_3.state_params(state);
144CREATE INDEX ON project_3.state_params(name);
145CREATE INDEX ON project_3.state_params("value");
146
147
148CREATE TABLE project_3.users(
149	id SERIAL NOT NULL PRIMARY KEY,
150	token VARCHAR(128) NOT NULL UNIQUE,
151	uid VARCHAR(128),
152	person INTEGER DEFAULT NULL REFERENCES project_3.persons ON DELETE SET NULL,
153	ctime CTIME_TYPE,
154	regtime INTEGER DEFAULT NULL,
155	regptime project_3.TIME_TYPE,
156	regip INTEGER DEFAULT NULL REFERENCES project_3.ips ON DELETE SET NULL,
157	last_session INTEGER DEFAULT NULL,
158	last_enter INTEGER NOT NULL DEFAULT 0,
159	last_penter project_3.TIME_TYPE,
160	last_activity INTEGER NOT NULL DEFAULT 0,
161	pays INTEGER NOT NULL DEFAULT 0,
162	flags INTEGER NOT NULL DEFAULT 0,
163	device INTEGER DEFAULT NULL REFERENCES project_3.devices ON DELETE SET NULL,
164	version INTEGER DEFAULT NULL REFERENCES project_3.versions ON DELETE SET NULL,
165	state BIGINT DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
166);
167CREATE INDEX ON project_3.users(uid);
168CREATE INDEX ON project_3.users(regtime);
169CREATE INDEX ON project_3.users(last_enter);
170CREATE INDEX ON project_3.users(last_activity);
171CREATE INDEX ON project_3.users(pays);
172
173ALTER TABLE project_3.user_states ADD CONSTRAINT user_states_user_fkey FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE;
174
175CREATE TABLE project_3.user_devices(
176	device INTEGER NOT NULL REFERENCES project_3.devices ON DELETE CASCADE,
177	"user" INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
178	ctime CTIME_TYPE,
179	PRIMARY KEY(device,"user")
180);
181
182CREATE TABLE project_3.packs(
183	"user" INTEGER NOT NULL,
184  	pack VARCHAR(128) NOT NULL,
185  	ctime CTIME_TYPE,
186  	command VARCHAR(128),
187  	params VARCHAR,
188  	processor INTEGER NOT NULL DEFAULT 0,
189  	status INTEGER NOT NULL DEFAULT 0,
190  	PRIMARY KEY("user",pack)
191);
192CREATE INDEX ON project_3.packs(ctime);
193CREATE INDEX ON project_3.packs(processor);
194CREATE INDEX ON project_3.packs(status);
195
196CREATE TABLE project_3.events_base(
197	"user" INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
198	ctime CTIME_TYPE,
199	ptime project_3.PTIME_TYPE,
200	year project_3.YEAR_TYPE,
201	month project_3.MONTH_TYPE,
202	week project_3.WEEK_TYPE,
203	day project_3.DAY_TYPE,
204	hour project_3.HOUR_TYPE,
205	device INTEGER DEFAULT NULL REFERENCES project_3.devices ON DELETE SET NULL,
206	version INTEGER DEFAULT NULL REFERENCES project_3.versions ON DELETE SET NULL,
207	state INTEGER DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
208);
209CREATE TABLE project_3.event_params_base(
210	ptime project_3.TIME_TYPE,
211	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
212	"value" project_3.PVAL_TYPE
213);
214
215--sessions
216CREATE TABLE project_3.sessions(
217	id BIGSERIAL NOT NULL PRIMARY KEY,
218	etime INTEGER DEFAULT NULL,
219	eptime project_3.TIME_TYPE,
220	ip INTEGER DEFAULT NULL
221) INHERITS (project_3.events_base);
222CREATE INDEX ON project_3.sessions("user");
223CREATE INDEX ON project_3.sessions(ctime);
224CREATE INDEX ON project_3.sessions(ptime);
225CREATE INDEX ON project_3.sessions(etime);
226CREATE INDEX ON project_3.sessions(eptime);
227
228CREATE OR REPLACE FUNCTION project_3.sessions_insert_trigger()
229    RETURNS TRIGGER AS $$
230DECLARE
231	tm VARCHAR := '';
232	tmon VARCHAR :='';
233	tmoff VARCHAR :='';
234    tbl VARCHAR(255) := '';
235    tblexists VARCHAR(255) := '';
236BEGIN
237	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
238	tbl := 'project_3.sessions_' || tm;
239	SELECT to_regclass((tbl)::varchar) INTO tblexists;
240	IF tblexists IS NULL THEN
241		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
242		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
243		EXECUTE 'CREATE TABLE ' || tbl || '(
244			LIKE project_3.sessions INCLUDING ALL,
245			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
246			CONSTRAINT sess_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
247			CONSTRAINT sess_' || tm || '_fk2 FOREIGN KEY (ip) REFERENCES project_3.ips ON DELETE CASCADE,
248			CONSTRAINT sess_' || tm || '_fk3 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
249			CONSTRAINT sess_' || tm || '_fk4 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
250			CONSTRAINT sess_' || tm || '_fk5 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL
251			) INHERITS (project_3.sessions) WITH (OIDS=FALSE);';
252	END IF;
253	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
254	RETURN NULL;
255END;
256$$ LANGUAGE plpgsql;
257
258CREATE TRIGGER insert_sessions_trigger BEFORE INSERT ON project_3.sessions
259    FOR EACH ROW EXECUTE PROCEDURE project_3.sessions_insert_trigger();
260
261
262CREATE TABLE project_3.session_params(
263	session BIGINT NOT NULL
264) INHERITS (project_3.event_params_base);
265CREATE INDEX ON project_3.session_params(session);
266CREATE INDEX ON project_3.session_params(ptime);
267CREATE INDEX ON project_3.session_params(name);
268CREATE INDEX ON project_3.session_params("value");
269
270CREATE OR REPLACE FUNCTION project_3.session_params_insert_trigger()
271    RETURNS TRIGGER AS $$
272DECLARE
273	tm VARCHAR := '';
274    tbl VARCHAR := '';
275    tblexists VARCHAR := '';
276    tmon VARCHAR := '';
277    tmoff VARCHAR := '';
278BEGIN
279	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
280	tbl := 'project_3.session_params_' || tm;
281	SELECT to_regclass((tbl)::varchar) INTO tblexists;
282	IF tblexists IS NULL THEN
283		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
284		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
285		EXECUTE 'CREATE TABLE ' || tbl || '(
286			LIKE project_3.session_params INCLUDING ALL,
287			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
288			CONSTRAINT sessp_' || tm || '_fk1 FOREIGN KEY (session) REFERENCES project_3.sessions_' || tm ||' ON DELETE CASCADE,
289			CONSTRAINT sessp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
290			) INHERITS (project_3.session_params) WITH (OIDS=FALSE);';
291	END IF;
292	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
293	RETURN NULL;
294END;
295$$ LANGUAGE plpgsql;
296
297CREATE TRIGGER insert_session_params_trigger BEFORE INSERT ON project_3.session_params
298    FOR EACH ROW EXECUTE PROCEDURE project_3.session_params_insert_trigger();
299
300--payments
301CREATE TABLE project_3.skus(
302	id SERIAL NOT NULL PRIMARY KEY,
303	sku VARCHAR(128) NOT NULL UNIQUE,
304	name NAME_TYPE
305);
306
307CREATE TABLE project_3.currencies(
308	id SERIAL NOT NULL PRIMARY KEY,
309	name VARCHAR(3) NOT NULL UNIQUE,
310	factor NUMERIC NOT NULL DEFAULT 1
311);
312
313CREATE TABLE project_3.payments(
314	id BIGSERIAL NOT NULL PRIMARY KEY,
315	sku INTEGER DEFAULT NULL,
316	amount NUMERIC NOT NULL,
317	price NUMERIC NOT NULL,
318	currency INTEGER NOT NULL
319) INHERITS (project_3.events_base);
320CREATE INDEX ON project_3.payments("user");
321CREATE INDEX ON project_3.payments(ctime);
322CREATE INDEX ON project_3.payments(ptime);
323CREATE INDEX ON project_3.payments(sku);
324
325CREATE OR REPLACE FUNCTION project_3.payments_insert_trigger()
326    RETURNS TRIGGER AS $$
327DECLARE
328	tm VARCHAR := '';
329    tbl VARCHAR(255) := '';
330    tblexists VARCHAR(255) := '';
331    tmon VARCHAR := 0;
332    tmoff VARCHAR := 0;
333BEGIN
334	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
335	tbl := 'project_3.payments_' || tm;
336	SELECT to_regclass((tbl)::varchar) INTO tblexists;
337	IF tblexists IS NULL THEN
338		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
339		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
340		EXECUTE 'CREATE TABLE ' || tbl || '(
341			LIKE project_3.payments INCLUDING ALL,
342			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
343			CONSTRAINT pay_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
344			CONSTRAINT pay_' || tm || '_fk2 FOREIGN KEY (sku) REFERENCES project_3.skus ON DELETE SET NULL,
345			CONSTRAINT pay_' || tm || '_fk3 FOREIGN KEY (currency) REFERENCES project_3.currencies,
346			CONSTRAINT pay_' || tm || '_fk4 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
347			CONSTRAINT pay_' || tm || '_fk5 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
348			CONSTRAINT pay_' || tm || '_fk6 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL
349			) INHERITS (project_3.payments) WITH (OIDS=FALSE);';
350	END IF;
351	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
352	RETURN NULL;
353END;
354$$ LANGUAGE plpgsql;
355
356CREATE TRIGGER insert_payments_trigger BEFORE INSERT ON project_3.payments
357    FOR EACH ROW EXECUTE PROCEDURE project_3.payments_insert_trigger();
358
359CREATE TABLE project_3.payment_params(
360	payment BIGINT NOT NULL
361) INHERITS(project_3.event_params_base);
362CREATE INDEX ON project_3.payment_params(payment);
363CREATE INDEX ON project_3.payment_params(ptime);
364CREATE INDEX ON project_3.payment_params(name);
365CREATE INDEX ON project_3.payment_params("value");
366
367CREATE OR REPLACE FUNCTION project_3.payment_params_insert_trigger()
368    RETURNS TRIGGER AS $$
369DECLARE
370	tm VARCHAR := '';
371    tbl VARCHAR := '';
372    tblexists VARCHAR := '';
373    tmon VARCHAR := '';
374    tmoff VARCHAR := '';
375BEGIN
376	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
377	tbl := 'project_3.payment_params_' || tm;
378	SELECT to_regclass((tbl)::varchar) INTO tblexists;
379	IF tblexists IS NULL THEN
380		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
381		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
382		EXECUTE 'CREATE TABLE ' || tbl || '(
383			LIKE project_3.payment_params INCLUDING ALL,
384			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
385			CONSTRAINT payp_' || tm || '_fk1 FOREIGN KEY (payment) REFERENCES project_3.payments_' || tm ||' ON DELETE CASCADE,
386			CONSTRAINT payp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
387			) INHERITS (project_3.payment_params) WITH (OIDS=FALSE);';
388	END IF;
389	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
390	RETURN NULL;
391END;
392$$ LANGUAGE plpgsql;
393
394CREATE TRIGGER insert_pparams_trigger BEFORE INSERT ON project_3.payment_params
395    FOR EACH ROW EXECUTE PROCEDURE project_3.payment_params_insert_trigger();
396
397--stats
398CREATE TABLE project_3.stat_descr(
399	id SERIAL NOT NULL PRIMARY KEY,
400	name NAME_TYPE,
401	"type" INTEGER NOT NULL DEFAULT 0,
402	proc INTEGER NOT NULL DEFAULT 0,
403	pproc INTEGER NOT NULL DEFAULT 0,
404	"value" VARCHAR,
405	period INTEGER NOT NULL DEFAULT 1,
406  	recalc INTEGER NOT NULL DEFAULT 1,
407  	ttl INTEGER NOT NULL DEFAULT 0,
408  	uptime INTEGER NOT NULL DEFAULT 0,
409  	flags INTEGER NOT NULL DEFAULT 0
410);
411CREATE INDEX ON project_3.stat_descr(name);
412CREATE INDEX ON project_3.stat_descr("type");
413CREATE INDEX ON project_3.stat_descr(proc);
414CREATE INDEX ON project_3.stat_descr(pproc);
415CREATE INDEX ON project_3.stat_descr(period);
416CREATE INDEX ON project_3.stat_descr(ttl);
417CREATE INDEX ON project_3.stat_descr(uptime);
418
419CREATE TABLE project_3.stats(
420	stat INTEGER NOT NULL REFERENCES project_3.stat_descr ON DELETE CASCADE,
421	"time" INTEGER NOT NULL,
422	ptime project_3.TIME_TYPE,
423	"value" NUMERIC NOT NULL DEFAULT 0,
424  	admin_value NUMERIC NOT NULL DEFAULT 0,
425  	blocked_value NUMERIC NOT NULL DEFAULT 0,
426  	anon_value NUMERIC NOT NULL DEFAULT 0,	
427	PRIMARY KEY (stat,"time")
428);
429CREATE INDEX ON project_3.stats(ptime);
430
431CREATE OR REPLACE FUNCTION project_3.stats_insert_trigger()
432    RETURNS TRIGGER AS $$
433DECLARE
434    tbl VARCHAR := '';
435    tblexists VARCHAR := '';
436BEGIN
437	tbl := 'project_3.stats_' || NEW.stat;
438	SELECT to_regclass((tbl)::varchar) INTO tblexists;
439	IF tblexists IS NULL THEN
440		EXECUTE 'CREATE TABLE ' || tbl || '(
441			LIKE project_3.stats INCLUDING ALL,
442			CHECK (stat=' || NEW.stat || '),
443			CONSTRAINT stat_' || NEW.stat || '_fk FOREIGN KEY (stat) REFERENCES project_3.stat_descr ON DELETE CASCADE
444			) INHERITS (project_3.stats) WITH (OIDS=FALSE);';
445	END IF;
446	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
447	RETURN NULL;
448END;
449$$ LANGUAGE plpgsql;
450
451CREATE TRIGGER insert_stats_trigger BEFORE INSERT ON project_3.stats
452    FOR EACH ROW EXECUTE PROCEDURE project_3.stats_insert_trigger();
453
454CREATE TABLE project_3.actions(
455	id SERIAL NOT NULL PRIMARY KEY,
456	name NAME_TYPE,
457	description VARCHAR,
458	stime INTEGER,
459	etime INTEGER
460);
461CREATE INDEX ON project_3.actions(stime);
462CREATE INDEX ON project_3.actions(etime);
463
464CREATE TABLE project_3.dashboard_vals(
465	id SERIAL NOT NULL PRIMARY KEY,
466	"type" INTEGER NOT NULL DEFAULT 0,
467  	name NAME_TYPE,
468  	x INTEGER NOT NULL DEFAULT 0,
469  	y INTEGER NOT NULL DEFAULT 0,
470  	w INTEGER NOT NULL DEFAULT 0,
471  	h INTEGER NOT NULL DEFAULT 0,
472	stat INTEGER NOT NULL REFERENCES project_3.stat_descr ON DELETE CASCADE,
473  	period INTEGER NOT NULL DEFAULT 0,
474  	params JSON,
475  	flags INTEGER NOT NULL DEFAULT 0
476);
477
478--events
479CREATE TABLE project_3.events(
480	id BIGSERIAL NOT NULL PRIMARY KEY,
481	name INTEGER NOT NULL REFERENCES project_3.names ON DELETE CASCADE,
482	"type" INTEGER NOT NULL,
483	"value" BIGINT NOT NULL
484) INHERITS (project_3.events_base);
485CREATE INDEX ON project_3.events(name);
486CREATE INDEX ON project_3.events(ctime);
487CREATE INDEX ON project_3.events(ptime);
488CREATE INDEX ON project_3.events("user");
489CREATE INDEX ON project_3.events("type");
490CREATE INDEX ON project_3.events("value");
491
492CREATE OR REPLACE FUNCTION project_3.events_insert_trigger()
493    RETURNS TRIGGER AS $$
494DECLARE
495	tm VARCHAR := '';
496    tbl VARCHAR := '';
497    tblexists VARCHAR := '';
498    tmon VARCHAR :=0;
499    tmoff VARCHAR :=0;
500BEGIN
501	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
502	tm := NEW.name || '_' || tm;
503	tbl := 'project_3.events_' || tm;
504	SELECT to_regclass((tbl)::varchar) INTO tblexists;
505	IF tblexists IS NULL THEN
506		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
507		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
508		EXECUTE 'CREATE TABLE ' || tbl || '(
509			LIKE project_3.events INCLUDING ALL,
510			CHECK (name=' || NEW.name || '),
511			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
512			CONSTRAINT ev_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
513			CONSTRAINT ev_' || tm || '_fk2 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
514			CONSTRAINT ev_' || tm || '_fk3 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
515			CONSTRAINT ev_' || tm || '_fk4 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL,
516			CONSTRAINT ev_' || tm || '_fk5 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
517			) INHERITS (project_3.events) WITH (OIDS=FALSE);';
518	END IF;
519	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
520	RETURN NULL;
521END;
522$$ LANGUAGE plpgsql;
523
524CREATE TRIGGER insert_events_trigger BEFORE INSERT ON project_3.events
525    FOR EACH ROW EXECUTE PROCEDURE project_3.events_insert_trigger();
526    
527CREATE TABLE project_3.event_descr(
528	id SERIAL NOT NULL PRIMARY KEY,
529	event_name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
530	event_value project_3.PVAL_TYPE,
531	param_name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
532	param_value project_3.PVAL_TYPE,
533	UNIQUE(event_name,event_value,param_name,param_value)
534);
535CREATE INDEX ON project_3.event_descr(event_name);
536CREATE INDEX ON project_3.event_descr(event_value);
537CREATE INDEX ON project_3.event_descr(param_name);
538CREATE INDEX ON project_3.event_descr(param_value);
539CREATE INDEX ON project_3.event_descr(event_name,param_name);
540
541CREATE TABLE project_3.event_params(
542	event BIGINT NOT NULL,
543	event_name INTEGER NOT NULL,
544	descr INTEGER REFERENCES project_3.event_descr ON DELETE CASCADE
545) INHERITS (project_3.event_params_base);
546CREATE INDEX ON project_3.event_params(event);
547CREATE INDEX ON project_3.event_params(event_name);
548CREATE INDEX ON project_3.event_params(ptime);
549CREATE INDEX ON project_3.event_params(name);
550CREATE INDEX ON project_3.event_params("value");
551
552CREATE OR REPLACE FUNCTION project_3.event_params_insert_trigger()
553    RETURNS TRIGGER AS $$
554DECLARE
555	tm VARCHAR := '';
556    tbl VARCHAR := '';
557    tblexists VARCHAR := '';
558    tmon VARCHAR :='';
559    tmoff VARCHAR :='';
560BEGIN
561	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
562	tm := NEW.event_name || '_' || tm;
563	tbl := 'project_3.event_params_' || tm;
564	SELECT to_regclass((tbl)::varchar) INTO tblexists;
565	IF tblexists IS NULL THEN
566		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
567		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
568		EXECUTE 'CREATE TABLE ' || tbl || '(
569			LIKE project_3.event_params INCLUDING ALL,
570			CHECK (event_name=' || NEW.event_name || '),
571			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
572			CONSTRAINT evp_' || tm || '_fk1 FOREIGN KEY (event) REFERENCES project_3.events_' || tm || ' ON DELETE CASCADE,
573			CONSTRAINT evp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE,
574			CONSTRAINT evp_' || tm || '_fk3 FOREIGN KEY (descr) REFERENCES project_3.event_descr ON DELETE CASCADE
575			) INHERITS (project_3.event_params) WITH (OIDS=FALSE);';
576	END IF;
577	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
578	RETURN NULL;
579END;
580$$ LANGUAGE plpgsql;
581
582CREATE TRIGGER insert_event_params_trigger BEFORE INSERT ON project_3.event_params
583    FOR EACH ROW EXECUTE PROCEDURE project_3.event_params_insert_trigger();
584
585--v2
586CREATE TABLE project_3.project_custom_stat(
587	id SERIAL NOT NULL PRIMARY KEY,
588	title NAME_TYPE,
589	type INTEGER NOT NULL DEFAULT 0,
590	aggs VARCHAR(200) NOT NULL,
591	compare BOOL NOT NULL DEFAULT TRUE
592);
593
594CREATE TABLE project_3.project_custom_stat_filters(
595	id SERIAL NOT NULL PRIMARY KEY,
596	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
597	name NAME_TYPE,
598	title NAME_TYPE,
599	type NAME_TYPE,
600	exdata VARCHAR(200) NOT NULL
601);
602
603CREATE TABLE project_3.project_custom_stat_values(
604	id SERIAL NOT NULL PRIMARY KEY,
605	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
606	name NAME_TYPE,
607	title NAME_TYPE
608);
609
610CREATE TABLE project_3.project_custom_stat_query(
611	id SERIAL NOT NULL PRIMARY KEY,
612	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
613	name NAME_TYPE,
614	tables NAME_TYPE,
615	fields VARCHAR(200),
616	cond VARCHAR(200),
617	"group" NAME_TYPE
618);
619
620--v3
621CREATE TABLE project_3.tasks(
622	id SERIAL NOT NULL PRIMARY KEY,
623	task NAME_TYPE,
624	params VARCHAR,
625	pid INTEGER DEFAULT NULL,
626	heartbeat INTEGER DEFAULT NULL,
627	state INTEGER NOT NULL DEFAULT 0
628);
629
630--v4
631ALTER TABLE project_3.events ADD COLUMN "session" INTEGER DEFAULT NULL REFERENCES project_3.sessions ON DELETE SET NULL;
632CREATE INDEX ON project_3.events ("session");
633
634--v5
635CREATE TABLE project_3.leaving_users(
636	id SERIAL NOT NULL PRIMARY KEY,
637	"user"  INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
638	time_leave CTIME_TYPE,
639	time_return INTEGER NOT NULL DEFAULT 0,
640	state INTEGER DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
641);
642
643CREATE INDEX ON project_3.leaving_users (time_leave,time_return,state);
644
645--v6
646ALTER TABLE project_3.currencies ADD COLUMN flags INTEGER NOT NULL DEFAULT 0;
647
648
649--v7
650DROP INDEX IF EXISTS project_3.users_version_idx;
651DROP INDEX IF EXISTS project_3.users_person_idx;
652DROP INDEX IF EXISTS project_3.users_state_idx;
653DROP INDEX IF EXISTS project_3.users_flags_idx;
654DROP INDEX IF EXISTS project_3.users_last_penter_idx;
655DROP INDEX IF EXISTS project_3.users_last_session_idx;
656DROP INDEX IF EXISTS project_3.users_regptime_idx;
657DROP INDEX IF EXISTS project_3.users_regip_idx;
658
659DROP INDEX IF EXISTS project_3.sessions_year_idx;
660DROP INDEX IF EXISTS project_3.sessions_month_idx;
661DROP INDEX IF EXISTS project_3.sessions_week_idx;
662DROP INDEX IF EXISTS project_3.sessions_day_idx;
663DROP INDEX IF EXISTS project_3.sessions_hour_idx;
664DROP INDEX IF EXISTS project_3.sessions_ip_idx;
665DROP INDEX IF EXISTS project_3.sessions_device_idx;
666DROP INDEX IF EXISTS project_3.sessions_version_idx;
667DROP INDEX IF EXISTS project_3.sessions_state_idx;
668
669DROP INDEX IF EXISTS project_3.payments_year_idx;
670DROP INDEX IF EXISTS project_3.payments_month_idx;
671DROP INDEX IF EXISTS project_3.payments_week_idx;
672DROP INDEX IF EXISTS project_3.payments_day_idx;
673DROP INDEX IF EXISTS project_3.payments_hour_idx;
674DROP INDEX IF EXISTS project_3.payments_device_idx;
675DROP INDEX IF EXISTS project_3.payments_version_idx;
676DROP INDEX IF EXISTS project_3.payments_state_idx;
677
678DROP INDEX IF EXISTS project_3.events_year_idx;
679DROP INDEX IF EXISTS project_3.events_month_idx;
680DROP INDEX IF EXISTS project_3.events_week_idx;
681DROP INDEX IF EXISTS project_3.events_day_idx;
682DROP INDEX IF EXISTS project_3.events_hour_idx;
683DROP INDEX IF EXISTS project_3.events_device_idx;
684DROP INDEX IF EXISTS project_3.events_version_idx;
685DROP INDEX IF EXISTS project_3.events_state_idx;
686
687--v8
688CREATE TABLE project_3.app_versions (
689  id SERIAL NOT NULL,
690  name public.name_type,
691  description VARCHAR,
692  rdate public.ctime_type,
693  PRIMARY KEY(id)
694);
695
696--v9
697ALTER TABLE
698  project_3.users
699ADD COLUMN payed_value REAL DEFAULT 0 NOT NULL;
700
701--v10
702ALTER TABLE project_3.stat_descr
703  ALTER COLUMN period DROP DEFAULT;
704
705ALTER TABLE project_3.stat_descr
706  ALTER COLUMN period TYPE BIGINT;
707
708ALTER TABLE project_3.stat_descr
709  ALTER COLUMN period SET DEFAULT 1;
710
711INSERT INTO project_3.params(name,"value") VALUES('dbversion','10');
712COMMIT;
713
714                        )
715
716                )
717
718        )
719
720    [1] => Array
721        (
722            [file] => /home/pistat/src/Engine/DataProvider/DB/Database.php
723            [line] => 66
724            [function] => pg_query
725            [args] => Array
726                (
727                    [0] => Resource id #1
728                    [1] => BEGIN;
729CREATE SCHEMA project_3;
730GRANT ALL ON SCHEMA project_3 TO postgres;
731GRANT ALL ON SCHEMA project_3 TO public;
732CREATE DOMAIN project_3.PTIME_TYPE TIMESTAMPTZ NOT NULL DEFAULT current_timestamp;
733CREATE DOMAIN project_3.TIME_TYPE TIMESTAMPTZ;
734CREATE DOMAIN project_3.YEAR_TYPE INTEGER NOT NULL DEFAULT date_part('year',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
735CREATE DOMAIN project_3.MONTH_TYPE INTEGER NOT NULL DEFAULT date_part('month',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
736CREATE DOMAIN project_3.WEEK_TYPE INTEGER NOT NULL DEFAULT date_part('week',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
737CREATE DOMAIN project_3.DAY_TYPE INTEGER NOT NULL DEFAULT date_part('day',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
738CREATE DOMAIN project_3.HOUR_TYPE INTEGER NOT NULL DEFAULT date_part('hour',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
739
740CREATE TABLE project_3.params(
741	name NAME_TYPE PRIMARY KEY,
742	"value" VARCHAR
743);
744
745
746CREATE TABLE project_3.names(
747	id SERIAL NOT NULL PRIMARY KEY,
748	"type" INTEGER NOT NULL DEFAULT 0,
749	subtype INTEGER NOT NULL DEFAULT 0,
750	"class" INTEGER NOT NULL DEFAULT 0,
751	name NAME_TYPE
752);
753CREATE INDEX ON project_3.names("type");
754CREATE INDEX ON project_3.names(subtype);
755CREATE INDEX ON project_3.names(name);
756CREATE INDEX ON project_3.names(lower(name));
757
758CREATE TABLE project_3."values"(
759	id BIGSERIAL NOT NULL PRIMARY KEY,
760	"type" INTEGER NOT NULL DEFAULT 0,
761	subtype INTEGER DEFAULT NULL REFERENCES project_3.names ON DELETE CASCADE,
762	"value" NAME_TYPE,
763	num_value NUMERIC DEFAULT NULL,
764	text_value VARCHAR DEFAULT NULL
765);
766CREATE INDEX ON project_3."values"("type");
767CREATE INDEX ON project_3."values"(subtype);
768CREATE INDEX ON project_3."values"("value");
769CREATE INDEX ON project_3."values"(num_value);
770CREATE INDEX ON project_3."values"(lower("value"));
771
772CREATE OR REPLACE FUNCTION project_3.values_insert_trigger()
773    RETURNS TRIGGER AS $$
774DECLARE
775    tbl VARCHAR := '';
776    tblexists VARCHAR := '';
777BEGIN
778	tbl := 'project_3.values_' || NEW."type";
779	SELECT to_regclass((tbl)::varchar) INTO tblexists;
780	IF tblexists IS NULL THEN
781		EXECUTE 'CREATE TABLE ' || tbl || '(
782			CHECK ("type"=' || NEW."type" || '), 
783			CONSTRAINT val_' || NEW."type" || '_pk PRIMARY KEY(id), 
784			CONSTRAINT val_' || NEW."type" || '_fkey FOREIGN KEY (subtype) REFERENCES project_3.names ON DELETE CASCADE
785			) INHERITS (project_3.values) WITH (OIDS=FALSE);';
786		EXECUTE 'CREATE INDEX ON ' || tbl || '("type");';
787		EXECUTE 'CREATE INDEX ON ' || tbl || '(subtype);';
788		EXECUTE 'CREATE INDEX ON ' || tbl || '("value");';
789		EXECUTE 'CREATE INDEX ON ' || tbl || '(lower("value"));';
790		EXECUTE 'CREATE INDEX ON ' || tbl || '(num_value);';
791	END IF;
792	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
793	RETURN NULL;
794END;
795$$ LANGUAGE plpgsql;
796
797CREATE TRIGGER insert_values_trigger BEFORE INSERT ON project_3."values"
798    FOR EACH ROW EXECUTE PROCEDURE project_3.values_insert_trigger();
799
800
801CREATE DOMAIN project_3.PNAME_TYPE AS INTEGER NOT NULL;
802CREATE DOMAIN project_3.PVAL_TYPE AS BIGINT NOT NULL;
803
804
805CREATE TABLE project_3.versions(
806	id SERIAL NOT NULL PRIMARY KEY,
807	version NAME_TYPE,
808	ctime CTIME_TYPE,
809  	flags integer NOT NULL DEFAULT 0
810);
811CREATE INDEX ON project_3.versions(version);
812CREATE INDEX ON project_3.versions(flags);
813
814CREATE TABLE project_3.persons(
815	id SERIAL NOT NULL PRIMARY KEY
816);
817
818CREATE TABLE project_3.ips(
819	id BIGSERIAL NOT NULL PRIMARY KEY,
820	ip VARCHAR(64) NOT NULL UNIQUE,
821	country VARCHAR(2)
822);
823CREATE INDEX ON project_3.ips(country);
824
825--users
826CREATE TABLE project_3.devices(
827	id SERIAL NOT NULL PRIMARY KEY,
828	udid VARCHAR(128) NOT NULL UNIQUE,
829	ctime CTIME_TYPE
830);
831
832CREATE TABLE project_3.device_params(
833	device INTEGER NOT NULL REFERENCES project_3.devices ON DELETE CASCADE,
834	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
835	"value" project_3.PVAL_TYPE
836);
837CREATE INDEX ON project_3.device_params(device);
838CREATE INDEX ON project_3.device_params(name);
839CREATE INDEX ON project_3.device_params("value");
840
841
842CREATE TABLE project_3.user_states(
843	id BIGSERIAL NOT NULL PRIMARY KEY,
844	"user" INTEGER NOT NULL, 
845	ctime CTIME_TYPE
846);
847
848CREATE TABLE project_3.state_params(
849	state INTEGER NOT NULL REFERENCES project_3.user_states ON DELETE CASCADE,
850	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
851	"value" project_3.PVAL_TYPE
852);
853CREATE INDEX ON project_3.state_params(state);
854CREATE INDEX ON project_3.state_params(name);
855CREATE INDEX ON project_3.state_params("value");
856
857
858CREATE TABLE project_3.users(
859	id SERIAL NOT NULL PRIMARY KEY,
860	token VARCHAR(128) NOT NULL UNIQUE,
861	uid VARCHAR(128),
862	person INTEGER DEFAULT NULL REFERENCES project_3.persons ON DELETE SET NULL,
863	ctime CTIME_TYPE,
864	regtime INTEGER DEFAULT NULL,
865	regptime project_3.TIME_TYPE,
866	regip INTEGER DEFAULT NULL REFERENCES project_3.ips ON DELETE SET NULL,
867	last_session INTEGER DEFAULT NULL,
868	last_enter INTEGER NOT NULL DEFAULT 0,
869	last_penter project_3.TIME_TYPE,
870	last_activity INTEGER NOT NULL DEFAULT 0,
871	pays INTEGER NOT NULL DEFAULT 0,
872	flags INTEGER NOT NULL DEFAULT 0,
873	device INTEGER DEFAULT NULL REFERENCES project_3.devices ON DELETE SET NULL,
874	version INTEGER DEFAULT NULL REFERENCES project_3.versions ON DELETE SET NULL,
875	state BIGINT DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
876);
877CREATE INDEX ON project_3.users(uid);
878CREATE INDEX ON project_3.users(regtime);
879CREATE INDEX ON project_3.users(last_enter);
880CREATE INDEX ON project_3.users(last_activity);
881CREATE INDEX ON project_3.users(pays);
882
883ALTER TABLE project_3.user_states ADD CONSTRAINT user_states_user_fkey FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE;
884
885CREATE TABLE project_3.user_devices(
886	device INTEGER NOT NULL REFERENCES project_3.devices ON DELETE CASCADE,
887	"user" INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
888	ctime CTIME_TYPE,
889	PRIMARY KEY(device,"user")
890);
891
892CREATE TABLE project_3.packs(
893	"user" INTEGER NOT NULL,
894  	pack VARCHAR(128) NOT NULL,
895  	ctime CTIME_TYPE,
896  	command VARCHAR(128),
897  	params VARCHAR,
898  	processor INTEGER NOT NULL DEFAULT 0,
899  	status INTEGER NOT NULL DEFAULT 0,
900  	PRIMARY KEY("user",pack)
901);
902CREATE INDEX ON project_3.packs(ctime);
903CREATE INDEX ON project_3.packs(processor);
904CREATE INDEX ON project_3.packs(status);
905
906CREATE TABLE project_3.events_base(
907	"user" INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
908	ctime CTIME_TYPE,
909	ptime project_3.PTIME_TYPE,
910	year project_3.YEAR_TYPE,
911	month project_3.MONTH_TYPE,
912	week project_3.WEEK_TYPE,
913	day project_3.DAY_TYPE,
914	hour project_3.HOUR_TYPE,
915	device INTEGER DEFAULT NULL REFERENCES project_3.devices ON DELETE SET NULL,
916	version INTEGER DEFAULT NULL REFERENCES project_3.versions ON DELETE SET NULL,
917	state INTEGER DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
918);
919CREATE TABLE project_3.event_params_base(
920	ptime project_3.TIME_TYPE,
921	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
922	"value" project_3.PVAL_TYPE
923);
924
925--sessions
926CREATE TABLE project_3.sessions(
927	id BIGSERIAL NOT NULL PRIMARY KEY,
928	etime INTEGER DEFAULT NULL,
929	eptime project_3.TIME_TYPE,
930	ip INTEGER DEFAULT NULL
931) INHERITS (project_3.events_base);
932CREATE INDEX ON project_3.sessions("user");
933CREATE INDEX ON project_3.sessions(ctime);
934CREATE INDEX ON project_3.sessions(ptime);
935CREATE INDEX ON project_3.sessions(etime);
936CREATE INDEX ON project_3.sessions(eptime);
937
938CREATE OR REPLACE FUNCTION project_3.sessions_insert_trigger()
939    RETURNS TRIGGER AS $$
940DECLARE
941	tm VARCHAR := '';
942	tmon VARCHAR :='';
943	tmoff VARCHAR :='';
944    tbl VARCHAR(255) := '';
945    tblexists VARCHAR(255) := '';
946BEGIN
947	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
948	tbl := 'project_3.sessions_' || tm;
949	SELECT to_regclass((tbl)::varchar) INTO tblexists;
950	IF tblexists IS NULL THEN
951		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
952		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
953		EXECUTE 'CREATE TABLE ' || tbl || '(
954			LIKE project_3.sessions INCLUDING ALL,
955			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
956			CONSTRAINT sess_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
957			CONSTRAINT sess_' || tm || '_fk2 FOREIGN KEY (ip) REFERENCES project_3.ips ON DELETE CASCADE,
958			CONSTRAINT sess_' || tm || '_fk3 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
959			CONSTRAINT sess_' || tm || '_fk4 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
960			CONSTRAINT sess_' || tm || '_fk5 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL
961			) INHERITS (project_3.sessions) WITH (OIDS=FALSE);';
962	END IF;
963	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
964	RETURN NULL;
965END;
966$$ LANGUAGE plpgsql;
967
968CREATE TRIGGER insert_sessions_trigger BEFORE INSERT ON project_3.sessions
969    FOR EACH ROW EXECUTE PROCEDURE project_3.sessions_insert_trigger();
970
971
972CREATE TABLE project_3.session_params(
973	session BIGINT NOT NULL
974) INHERITS (project_3.event_params_base);
975CREATE INDEX ON project_3.session_params(session);
976CREATE INDEX ON project_3.session_params(ptime);
977CREATE INDEX ON project_3.session_params(name);
978CREATE INDEX ON project_3.session_params("value");
979
980CREATE OR REPLACE FUNCTION project_3.session_params_insert_trigger()
981    RETURNS TRIGGER AS $$
982DECLARE
983	tm VARCHAR := '';
984    tbl VARCHAR := '';
985    tblexists VARCHAR := '';
986    tmon VARCHAR := '';
987    tmoff VARCHAR := '';
988BEGIN
989	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
990	tbl := 'project_3.session_params_' || tm;
991	SELECT to_regclass((tbl)::varchar) INTO tblexists;
992	IF tblexists IS NULL THEN
993		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
994		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
995		EXECUTE 'CREATE TABLE ' || tbl || '(
996			LIKE project_3.session_params INCLUDING ALL,
997			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
998			CONSTRAINT sessp_' || tm || '_fk1 FOREIGN KEY (session) REFERENCES project_3.sessions_' || tm ||' ON DELETE CASCADE,
999			CONSTRAINT sessp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
1000			) INHERITS (project_3.session_params) WITH (OIDS=FALSE);';
1001	END IF;
1002	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1003	RETURN NULL;
1004END;
1005$$ LANGUAGE plpgsql;
1006
1007CREATE TRIGGER insert_session_params_trigger BEFORE INSERT ON project_3.session_params
1008    FOR EACH ROW EXECUTE PROCEDURE project_3.session_params_insert_trigger();
1009
1010--payments
1011CREATE TABLE project_3.skus(
1012	id SERIAL NOT NULL PRIMARY KEY,
1013	sku VARCHAR(128) NOT NULL UNIQUE,
1014	name NAME_TYPE
1015);
1016
1017CREATE TABLE project_3.currencies(
1018	id SERIAL NOT NULL PRIMARY KEY,
1019	name VARCHAR(3) NOT NULL UNIQUE,
1020	factor NUMERIC NOT NULL DEFAULT 1
1021);
1022
1023CREATE TABLE project_3.payments(
1024	id BIGSERIAL NOT NULL PRIMARY KEY,
1025	sku INTEGER DEFAULT NULL,
1026	amount NUMERIC NOT NULL,
1027	price NUMERIC NOT NULL,
1028	currency INTEGER NOT NULL
1029) INHERITS (project_3.events_base);
1030CREATE INDEX ON project_3.payments("user");
1031CREATE INDEX ON project_3.payments(ctime);
1032CREATE INDEX ON project_3.payments(ptime);
1033CREATE INDEX ON project_3.payments(sku);
1034
1035CREATE OR REPLACE FUNCTION project_3.payments_insert_trigger()
1036    RETURNS TRIGGER AS $$
1037DECLARE
1038	tm VARCHAR := '';
1039    tbl VARCHAR(255) := '';
1040    tblexists VARCHAR(255) := '';
1041    tmon VARCHAR := 0;
1042    tmoff VARCHAR := 0;
1043BEGIN
1044	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1045	tbl := 'project_3.payments_' || tm;
1046	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1047	IF tblexists IS NULL THEN
1048		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1049		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1050		EXECUTE 'CREATE TABLE ' || tbl || '(
1051			LIKE project_3.payments INCLUDING ALL,
1052			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1053			CONSTRAINT pay_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
1054			CONSTRAINT pay_' || tm || '_fk2 FOREIGN KEY (sku) REFERENCES project_3.skus ON DELETE SET NULL,
1055			CONSTRAINT pay_' || tm || '_fk3 FOREIGN KEY (currency) REFERENCES project_3.currencies,
1056			CONSTRAINT pay_' || tm || '_fk4 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
1057			CONSTRAINT pay_' || tm || '_fk5 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
1058			CONSTRAINT pay_' || tm || '_fk6 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL
1059			) INHERITS (project_3.payments) WITH (OIDS=FALSE);';
1060	END IF;
1061	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1062	RETURN NULL;
1063END;
1064$$ LANGUAGE plpgsql;
1065
1066CREATE TRIGGER insert_payments_trigger BEFORE INSERT ON project_3.payments
1067    FOR EACH ROW EXECUTE PROCEDURE project_3.payments_insert_trigger();
1068
1069CREATE TABLE project_3.payment_params(
1070	payment BIGINT NOT NULL
1071) INHERITS(project_3.event_params_base);
1072CREATE INDEX ON project_3.payment_params(payment);
1073CREATE INDEX ON project_3.payment_params(ptime);
1074CREATE INDEX ON project_3.payment_params(name);
1075CREATE INDEX ON project_3.payment_params("value");
1076
1077CREATE OR REPLACE FUNCTION project_3.payment_params_insert_trigger()
1078    RETURNS TRIGGER AS $$
1079DECLARE
1080	tm VARCHAR := '';
1081    tbl VARCHAR := '';
1082    tblexists VARCHAR := '';
1083    tmon VARCHAR := '';
1084    tmoff VARCHAR := '';
1085BEGIN
1086	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1087	tbl := 'project_3.payment_params_' || tm;
1088	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1089	IF tblexists IS NULL THEN
1090		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1091		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1092		EXECUTE 'CREATE TABLE ' || tbl || '(
1093			LIKE project_3.payment_params INCLUDING ALL,
1094			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1095			CONSTRAINT payp_' || tm || '_fk1 FOREIGN KEY (payment) REFERENCES project_3.payments_' || tm ||' ON DELETE CASCADE,
1096			CONSTRAINT payp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
1097			) INHERITS (project_3.payment_params) WITH (OIDS=FALSE);';
1098	END IF;
1099	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1100	RETURN NULL;
1101END;
1102$$ LANGUAGE plpgsql;
1103
1104CREATE TRIGGER insert_pparams_trigger BEFORE INSERT ON project_3.payment_params
1105    FOR EACH ROW EXECUTE PROCEDURE project_3.payment_params_insert_trigger();
1106
1107--stats
1108CREATE TABLE project_3.stat_descr(
1109	id SERIAL NOT NULL PRIMARY KEY,
1110	name NAME_TYPE,
1111	"type" INTEGER NOT NULL DEFAULT 0,
1112	proc INTEGER NOT NULL DEFAULT 0,
1113	pproc INTEGER NOT NULL DEFAULT 0,
1114	"value" VARCHAR,
1115	period INTEGER NOT NULL DEFAULT 1,
1116  	recalc INTEGER NOT NULL DEFAULT 1,
1117  	ttl INTEGER NOT NULL DEFAULT 0,
1118  	uptime INTEGER NOT NULL DEFAULT 0,
1119  	flags INTEGER NOT NULL DEFAULT 0
1120);
1121CREATE INDEX ON project_3.stat_descr(name);
1122CREATE INDEX ON project_3.stat_descr("type");
1123CREATE INDEX ON project_3.stat_descr(proc);
1124CREATE INDEX ON project_3.stat_descr(pproc);
1125CREATE INDEX ON project_3.stat_descr(period);
1126CREATE INDEX ON project_3.stat_descr(ttl);
1127CREATE INDEX ON project_3.stat_descr(uptime);
1128
1129CREATE TABLE project_3.stats(
1130	stat INTEGER NOT NULL REFERENCES project_3.stat_descr ON DELETE CASCADE,
1131	"time" INTEGER NOT NULL,
1132	ptime project_3.TIME_TYPE,
1133	"value" NUMERIC NOT NULL DEFAULT 0,
1134  	admin_value NUMERIC NOT NULL DEFAULT 0,
1135  	blocked_value NUMERIC NOT NULL DEFAULT 0,
1136  	anon_value NUMERIC NOT NULL DEFAULT 0,	
1137	PRIMARY KEY (stat,"time")
1138);
1139CREATE INDEX ON project_3.stats(ptime);
1140
1141CREATE OR REPLACE FUNCTION project_3.stats_insert_trigger()
1142    RETURNS TRIGGER AS $$
1143DECLARE
1144    tbl VARCHAR := '';
1145    tblexists VARCHAR := '';
1146BEGIN
1147	tbl := 'project_3.stats_' || NEW.stat;
1148	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1149	IF tblexists IS NULL THEN
1150		EXECUTE 'CREATE TABLE ' || tbl || '(
1151			LIKE project_3.stats INCLUDING ALL,
1152			CHECK (stat=' || NEW.stat || '),
1153			CONSTRAINT stat_' || NEW.stat || '_fk FOREIGN KEY (stat) REFERENCES project_3.stat_descr ON DELETE CASCADE
1154			) INHERITS (project_3.stats) WITH (OIDS=FALSE);';
1155	END IF;
1156	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1157	RETURN NULL;
1158END;
1159$$ LANGUAGE plpgsql;
1160
1161CREATE TRIGGER insert_stats_trigger BEFORE INSERT ON project_3.stats
1162    FOR EACH ROW EXECUTE PROCEDURE project_3.stats_insert_trigger();
1163
1164CREATE TABLE project_3.actions(
1165	id SERIAL NOT NULL PRIMARY KEY,
1166	name NAME_TYPE,
1167	description VARCHAR,
1168	stime INTEGER,
1169	etime INTEGER
1170);
1171CREATE INDEX ON project_3.actions(stime);
1172CREATE INDEX ON project_3.actions(etime);
1173
1174CREATE TABLE project_3.dashboard_vals(
1175	id SERIAL NOT NULL PRIMARY KEY,
1176	"type" INTEGER NOT NULL DEFAULT 0,
1177  	name NAME_TYPE,
1178  	x INTEGER NOT NULL DEFAULT 0,
1179  	y INTEGER NOT NULL DEFAULT 0,
1180  	w INTEGER NOT NULL DEFAULT 0,
1181  	h INTEGER NOT NULL DEFAULT 0,
1182	stat INTEGER NOT NULL REFERENCES project_3.stat_descr ON DELETE CASCADE,
1183  	period INTEGER NOT NULL DEFAULT 0,
1184  	params JSON,
1185  	flags INTEGER NOT NULL DEFAULT 0
1186);
1187
1188--events
1189CREATE TABLE project_3.events(
1190	id BIGSERIAL NOT NULL PRIMARY KEY,
1191	name INTEGER NOT NULL REFERENCES project_3.names ON DELETE CASCADE,
1192	"type" INTEGER NOT NULL,
1193	"value" BIGINT NOT NULL
1194) INHERITS (project_3.events_base);
1195CREATE INDEX ON project_3.events(name);
1196CREATE INDEX ON project_3.events(ctime);
1197CREATE INDEX ON project_3.events(ptime);
1198CREATE INDEX ON project_3.events("user");
1199CREATE INDEX ON project_3.events("type");
1200CREATE INDEX ON project_3.events("value");
1201
1202CREATE OR REPLACE FUNCTION project_3.events_insert_trigger()
1203    RETURNS TRIGGER AS $$
1204DECLARE
1205	tm VARCHAR := '';
1206    tbl VARCHAR := '';
1207    tblexists VARCHAR := '';
1208    tmon VARCHAR :=0;
1209    tmoff VARCHAR :=0;
1210BEGIN
1211	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1212	tm := NEW.name || '_' || tm;
1213	tbl := 'project_3.events_' || tm;
1214	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1215	IF tblexists IS NULL THEN
1216		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1217		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1218		EXECUTE 'CREATE TABLE ' || tbl || '(
1219			LIKE project_3.events INCLUDING ALL,
1220			CHECK (name=' || NEW.name || '),
1221			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1222			CONSTRAINT ev_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
1223			CONSTRAINT ev_' || tm || '_fk2 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
1224			CONSTRAINT ev_' || tm || '_fk3 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
1225			CONSTRAINT ev_' || tm || '_fk4 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL,
1226			CONSTRAINT ev_' || tm || '_fk5 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
1227			) INHERITS (project_3.events) WITH (OIDS=FALSE);';
1228	END IF;
1229	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1230	RETURN NULL;
1231END;
1232$$ LANGUAGE plpgsql;
1233
1234CREATE TRIGGER insert_events_trigger BEFORE INSERT ON project_3.events
1235    FOR EACH ROW EXECUTE PROCEDURE project_3.events_insert_trigger();
1236    
1237CREATE TABLE project_3.event_descr(
1238	id SERIAL NOT NULL PRIMARY KEY,
1239	event_name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
1240	event_value project_3.PVAL_TYPE,
1241	param_name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
1242	param_value project_3.PVAL_TYPE,
1243	UNIQUE(event_name,event_value,param_name,param_value)
1244);
1245CREATE INDEX ON project_3.event_descr(event_name);
1246CREATE INDEX ON project_3.event_descr(event_value);
1247CREATE INDEX ON project_3.event_descr(param_name);
1248CREATE INDEX ON project_3.event_descr(param_value);
1249CREATE INDEX ON project_3.event_descr(event_name,param_name);
1250
1251CREATE TABLE project_3.event_params(
1252	event BIGINT NOT NULL,
1253	event_name INTEGER NOT NULL,
1254	descr INTEGER REFERENCES project_3.event_descr ON DELETE CASCADE
1255) INHERITS (project_3.event_params_base);
1256CREATE INDEX ON project_3.event_params(event);
1257CREATE INDEX ON project_3.event_params(event_name);
1258CREATE INDEX ON project_3.event_params(ptime);
1259CREATE INDEX ON project_3.event_params(name);
1260CREATE INDEX ON project_3.event_params("value");
1261
1262CREATE OR REPLACE FUNCTION project_3.event_params_insert_trigger()
1263    RETURNS TRIGGER AS $$
1264DECLARE
1265	tm VARCHAR := '';
1266    tbl VARCHAR := '';
1267    tblexists VARCHAR := '';
1268    tmon VARCHAR :='';
1269    tmoff VARCHAR :='';
1270BEGIN
1271	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1272	tm := NEW.event_name || '_' || tm;
1273	tbl := 'project_3.event_params_' || tm;
1274	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1275	IF tblexists IS NULL THEN
1276		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1277		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1278		EXECUTE 'CREATE TABLE ' || tbl || '(
1279			LIKE project_3.event_params INCLUDING ALL,
1280			CHECK (event_name=' || NEW.event_name || '),
1281			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1282			CONSTRAINT evp_' || tm || '_fk1 FOREIGN KEY (event) REFERENCES project_3.events_' || tm || ' ON DELETE CASCADE,
1283			CONSTRAINT evp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE,
1284			CONSTRAINT evp_' || tm || '_fk3 FOREIGN KEY (descr) REFERENCES project_3.event_descr ON DELETE CASCADE
1285			) INHERITS (project_3.event_params) WITH (OIDS=FALSE);';
1286	END IF;
1287	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1288	RETURN NULL;
1289END;
1290$$ LANGUAGE plpgsql;
1291
1292CREATE TRIGGER insert_event_params_trigger BEFORE INSERT ON project_3.event_params
1293    FOR EACH ROW EXECUTE PROCEDURE project_3.event_params_insert_trigger();
1294
1295--v2
1296CREATE TABLE project_3.project_custom_stat(
1297	id SERIAL NOT NULL PRIMARY KEY,
1298	title NAME_TYPE,
1299	type INTEGER NOT NULL DEFAULT 0,
1300	aggs VARCHAR(200) NOT NULL,
1301	compare BOOL NOT NULL DEFAULT TRUE
1302);
1303
1304CREATE TABLE project_3.project_custom_stat_filters(
1305	id SERIAL NOT NULL PRIMARY KEY,
1306	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
1307	name NAME_TYPE,
1308	title NAME_TYPE,
1309	type NAME_TYPE,
1310	exdata VARCHAR(200) NOT NULL
1311);
1312
1313CREATE TABLE project_3.project_custom_stat_values(
1314	id SERIAL NOT NULL PRIMARY KEY,
1315	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
1316	name NAME_TYPE,
1317	title NAME_TYPE
1318);
1319
1320CREATE TABLE project_3.project_custom_stat_query(
1321	id SERIAL NOT NULL PRIMARY KEY,
1322	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
1323	name NAME_TYPE,
1324	tables NAME_TYPE,
1325	fields VARCHAR(200),
1326	cond VARCHAR(200),
1327	"group" NAME_TYPE
1328);
1329
1330--v3
1331CREATE TABLE project_3.tasks(
1332	id SERIAL NOT NULL PRIMARY KEY,
1333	task NAME_TYPE,
1334	params VARCHAR,
1335	pid INTEGER DEFAULT NULL,
1336	heartbeat INTEGER DEFAULT NULL,
1337	state INTEGER NOT NULL DEFAULT 0
1338);
1339
1340--v4
1341ALTER TABLE project_3.events ADD COLUMN "session" INTEGER DEFAULT NULL REFERENCES project_3.sessions ON DELETE SET NULL;
1342CREATE INDEX ON project_3.events ("session");
1343
1344--v5
1345CREATE TABLE project_3.leaving_users(
1346	id SERIAL NOT NULL PRIMARY KEY,
1347	"user"  INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
1348	time_leave CTIME_TYPE,
1349	time_return INTEGER NOT NULL DEFAULT 0,
1350	state INTEGER DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
1351);
1352
1353CREATE INDEX ON project_3.leaving_users (time_leave,time_return,state);
1354
1355--v6
1356ALTER TABLE project_3.currencies ADD COLUMN flags INTEGER NOT NULL DEFAULT 0;
1357
1358
1359--v7
1360DROP INDEX IF EXISTS project_3.users_version_idx;
1361DROP INDEX IF EXISTS project_3.users_person_idx;
1362DROP INDEX IF EXISTS project_3.users_state_idx;
1363DROP INDEX IF EXISTS project_3.users_flags_idx;
1364DROP INDEX IF EXISTS project_3.users_last_penter_idx;
1365DROP INDEX IF EXISTS project_3.users_last_session_idx;
1366DROP INDEX IF EXISTS project_3.users_regptime_idx;
1367DROP INDEX IF EXISTS project_3.users_regip_idx;
1368
1369DROP INDEX IF EXISTS project_3.sessions_year_idx;
1370DROP INDEX IF EXISTS project_3.sessions_month_idx;
1371DROP INDEX IF EXISTS project_3.sessions_week_idx;
1372DROP INDEX IF EXISTS project_3.sessions_day_idx;
1373DROP INDEX IF EXISTS project_3.sessions_hour_idx;
1374DROP INDEX IF EXISTS project_3.sessions_ip_idx;
1375DROP INDEX IF EXISTS project_3.sessions_device_idx;
1376DROP INDEX IF EXISTS project_3.sessions_version_idx;
1377DROP INDEX IF EXISTS project_3.sessions_state_idx;
1378
1379DROP INDEX IF EXISTS project_3.payments_year_idx;
1380DROP INDEX IF EXISTS project_3.payments_month_idx;
1381DROP INDEX IF EXISTS project_3.payments_week_idx;
1382DROP INDEX IF EXISTS project_3.payments_day_idx;
1383DROP INDEX IF EXISTS project_3.payments_hour_idx;
1384DROP INDEX IF EXISTS project_3.payments_device_idx;
1385DROP INDEX IF EXISTS project_3.payments_version_idx;
1386DROP INDEX IF EXISTS project_3.payments_state_idx;
1387
1388DROP INDEX IF EXISTS project_3.events_year_idx;
1389DROP INDEX IF EXISTS project_3.events_month_idx;
1390DROP INDEX IF EXISTS project_3.events_week_idx;
1391DROP INDEX IF EXISTS project_3.events_day_idx;
1392DROP INDEX IF EXISTS project_3.events_hour_idx;
1393DROP INDEX IF EXISTS project_3.events_device_idx;
1394DROP INDEX IF EXISTS project_3.events_version_idx;
1395DROP INDEX IF EXISTS project_3.events_state_idx;
1396
1397--v8
1398CREATE TABLE project_3.app_versions (
1399  id SERIAL NOT NULL,
1400  name public.name_type,
1401  description VARCHAR,
1402  rdate public.ctime_type,
1403  PRIMARY KEY(id)
1404);
1405
1406--v9
1407ALTER TABLE
1408  project_3.users
1409ADD COLUMN payed_value REAL DEFAULT 0 NOT NULL;
1410
1411--v10
1412ALTER TABLE project_3.stat_descr
1413  ALTER COLUMN period DROP DEFAULT;
1414
1415ALTER TABLE project_3.stat_descr
1416  ALTER COLUMN period TYPE BIGINT;
1417
1418ALTER TABLE project_3.stat_descr
1419  ALTER COLUMN period SET DEFAULT 1;
1420
1421INSERT INTO project_3.params(name,"value") VALUES('dbversion','10');
1422COMMIT;
1423
1424                )
1425
1426        )
1427
1428    [2] => Array
1429        (
1430            [file] => /home/pistat/src/Engine/DataProvider/DB/Provider.php
1431            [line] => 49
1432            [function] => execQuery
1433            [class] => Engine\DataProvider\DB\Database
1434            [type] => ->
1435            [args] => Array
1436                (
1437                    [0] => BEGIN;
1438CREATE SCHEMA project_3;
1439GRANT ALL ON SCHEMA project_3 TO postgres;
1440GRANT ALL ON SCHEMA project_3 TO public;
1441CREATE DOMAIN project_3.PTIME_TYPE TIMESTAMPTZ NOT NULL DEFAULT current_timestamp;
1442CREATE DOMAIN project_3.TIME_TYPE TIMESTAMPTZ;
1443CREATE DOMAIN project_3.YEAR_TYPE INTEGER NOT NULL DEFAULT date_part('year',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
1444CREATE DOMAIN project_3.MONTH_TYPE INTEGER NOT NULL DEFAULT date_part('month',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
1445CREATE DOMAIN project_3.WEEK_TYPE INTEGER NOT NULL DEFAULT date_part('week',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
1446CREATE DOMAIN project_3.DAY_TYPE INTEGER NOT NULL DEFAULT date_part('day',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
1447CREATE DOMAIN project_3.HOUR_TYPE INTEGER NOT NULL DEFAULT date_part('hour',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
1448
1449CREATE TABLE project_3.params(
1450	name NAME_TYPE PRIMARY KEY,
1451	"value" VARCHAR
1452);
1453
1454
1455CREATE TABLE project_3.names(
1456	id SERIAL NOT NULL PRIMARY KEY,
1457	"type" INTEGER NOT NULL DEFAULT 0,
1458	subtype INTEGER NOT NULL DEFAULT 0,
1459	"class" INTEGER NOT NULL DEFAULT 0,
1460	name NAME_TYPE
1461);
1462CREATE INDEX ON project_3.names("type");
1463CREATE INDEX ON project_3.names(subtype);
1464CREATE INDEX ON project_3.names(name);
1465CREATE INDEX ON project_3.names(lower(name));
1466
1467CREATE TABLE project_3."values"(
1468	id BIGSERIAL NOT NULL PRIMARY KEY,
1469	"type" INTEGER NOT NULL DEFAULT 0,
1470	subtype INTEGER DEFAULT NULL REFERENCES project_3.names ON DELETE CASCADE,
1471	"value" NAME_TYPE,
1472	num_value NUMERIC DEFAULT NULL,
1473	text_value VARCHAR DEFAULT NULL
1474);
1475CREATE INDEX ON project_3."values"("type");
1476CREATE INDEX ON project_3."values"(subtype);
1477CREATE INDEX ON project_3."values"("value");
1478CREATE INDEX ON project_3."values"(num_value);
1479CREATE INDEX ON project_3."values"(lower("value"));
1480
1481CREATE OR REPLACE FUNCTION project_3.values_insert_trigger()
1482    RETURNS TRIGGER AS $$
1483DECLARE
1484    tbl VARCHAR := '';
1485    tblexists VARCHAR := '';
1486BEGIN
1487	tbl := 'project_3.values_' || NEW."type";
1488	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1489	IF tblexists IS NULL THEN
1490		EXECUTE 'CREATE TABLE ' || tbl || '(
1491			CHECK ("type"=' || NEW."type" || '), 
1492			CONSTRAINT val_' || NEW."type" || '_pk PRIMARY KEY(id), 
1493			CONSTRAINT val_' || NEW."type" || '_fkey FOREIGN KEY (subtype) REFERENCES project_3.names ON DELETE CASCADE
1494			) INHERITS (project_3.values) WITH (OIDS=FALSE);';
1495		EXECUTE 'CREATE INDEX ON ' || tbl || '("type");';
1496		EXECUTE 'CREATE INDEX ON ' || tbl || '(subtype);';
1497		EXECUTE 'CREATE INDEX ON ' || tbl || '("value");';
1498		EXECUTE 'CREATE INDEX ON ' || tbl || '(lower("value"));';
1499		EXECUTE 'CREATE INDEX ON ' || tbl || '(num_value);';
1500	END IF;
1501	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1502	RETURN NULL;
1503END;
1504$$ LANGUAGE plpgsql;
1505
1506CREATE TRIGGER insert_values_trigger BEFORE INSERT ON project_3."values"
1507    FOR EACH ROW EXECUTE PROCEDURE project_3.values_insert_trigger();
1508
1509
1510CREATE DOMAIN project_3.PNAME_TYPE AS INTEGER NOT NULL;
1511CREATE DOMAIN project_3.PVAL_TYPE AS BIGINT NOT NULL;
1512
1513
1514CREATE TABLE project_3.versions(
1515	id SERIAL NOT NULL PRIMARY KEY,
1516	version NAME_TYPE,
1517	ctime CTIME_TYPE,
1518  	flags integer NOT NULL DEFAULT 0
1519);
1520CREATE INDEX ON project_3.versions(version);
1521CREATE INDEX ON project_3.versions(flags);
1522
1523CREATE TABLE project_3.persons(
1524	id SERIAL NOT NULL PRIMARY KEY
1525);
1526
1527CREATE TABLE project_3.ips(
1528	id BIGSERIAL NOT NULL PRIMARY KEY,
1529	ip VARCHAR(64) NOT NULL UNIQUE,
1530	country VARCHAR(2)
1531);
1532CREATE INDEX ON project_3.ips(country);
1533
1534--users
1535CREATE TABLE project_3.devices(
1536	id SERIAL NOT NULL PRIMARY KEY,
1537	udid VARCHAR(128) NOT NULL UNIQUE,
1538	ctime CTIME_TYPE
1539);
1540
1541CREATE TABLE project_3.device_params(
1542	device INTEGER NOT NULL REFERENCES project_3.devices ON DELETE CASCADE,
1543	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
1544	"value" project_3.PVAL_TYPE
1545);
1546CREATE INDEX ON project_3.device_params(device);
1547CREATE INDEX ON project_3.device_params(name);
1548CREATE INDEX ON project_3.device_params("value");
1549
1550
1551CREATE TABLE project_3.user_states(
1552	id BIGSERIAL NOT NULL PRIMARY KEY,
1553	"user" INTEGER NOT NULL, 
1554	ctime CTIME_TYPE
1555);
1556
1557CREATE TABLE project_3.state_params(
1558	state INTEGER NOT NULL REFERENCES project_3.user_states ON DELETE CASCADE,
1559	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
1560	"value" project_3.PVAL_TYPE
1561);
1562CREATE INDEX ON project_3.state_params(state);
1563CREATE INDEX ON project_3.state_params(name);
1564CREATE INDEX ON project_3.state_params("value");
1565
1566
1567CREATE TABLE project_3.users(
1568	id SERIAL NOT NULL PRIMARY KEY,
1569	token VARCHAR(128) NOT NULL UNIQUE,
1570	uid VARCHAR(128),
1571	person INTEGER DEFAULT NULL REFERENCES project_3.persons ON DELETE SET NULL,
1572	ctime CTIME_TYPE,
1573	regtime INTEGER DEFAULT NULL,
1574	regptime project_3.TIME_TYPE,
1575	regip INTEGER DEFAULT NULL REFERENCES project_3.ips ON DELETE SET NULL,
1576	last_session INTEGER DEFAULT NULL,
1577	last_enter INTEGER NOT NULL DEFAULT 0,
1578	last_penter project_3.TIME_TYPE,
1579	last_activity INTEGER NOT NULL DEFAULT 0,
1580	pays INTEGER NOT NULL DEFAULT 0,
1581	flags INTEGER NOT NULL DEFAULT 0,
1582	device INTEGER DEFAULT NULL REFERENCES project_3.devices ON DELETE SET NULL,
1583	version INTEGER DEFAULT NULL REFERENCES project_3.versions ON DELETE SET NULL,
1584	state BIGINT DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
1585);
1586CREATE INDEX ON project_3.users(uid);
1587CREATE INDEX ON project_3.users(regtime);
1588CREATE INDEX ON project_3.users(last_enter);
1589CREATE INDEX ON project_3.users(last_activity);
1590CREATE INDEX ON project_3.users(pays);
1591
1592ALTER TABLE project_3.user_states ADD CONSTRAINT user_states_user_fkey FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE;
1593
1594CREATE TABLE project_3.user_devices(
1595	device INTEGER NOT NULL REFERENCES project_3.devices ON DELETE CASCADE,
1596	"user" INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
1597	ctime CTIME_TYPE,
1598	PRIMARY KEY(device,"user")
1599);
1600
1601CREATE TABLE project_3.packs(
1602	"user" INTEGER NOT NULL,
1603  	pack VARCHAR(128) NOT NULL,
1604  	ctime CTIME_TYPE,
1605  	command VARCHAR(128),
1606  	params VARCHAR,
1607  	processor INTEGER NOT NULL DEFAULT 0,
1608  	status INTEGER NOT NULL DEFAULT 0,
1609  	PRIMARY KEY("user",pack)
1610);
1611CREATE INDEX ON project_3.packs(ctime);
1612CREATE INDEX ON project_3.packs(processor);
1613CREATE INDEX ON project_3.packs(status);
1614
1615CREATE TABLE project_3.events_base(
1616	"user" INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
1617	ctime CTIME_TYPE,
1618	ptime project_3.PTIME_TYPE,
1619	year project_3.YEAR_TYPE,
1620	month project_3.MONTH_TYPE,
1621	week project_3.WEEK_TYPE,
1622	day project_3.DAY_TYPE,
1623	hour project_3.HOUR_TYPE,
1624	device INTEGER DEFAULT NULL REFERENCES project_3.devices ON DELETE SET NULL,
1625	version INTEGER DEFAULT NULL REFERENCES project_3.versions ON DELETE SET NULL,
1626	state INTEGER DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
1627);
1628CREATE TABLE project_3.event_params_base(
1629	ptime project_3.TIME_TYPE,
1630	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
1631	"value" project_3.PVAL_TYPE
1632);
1633
1634--sessions
1635CREATE TABLE project_3.sessions(
1636	id BIGSERIAL NOT NULL PRIMARY KEY,
1637	etime INTEGER DEFAULT NULL,
1638	eptime project_3.TIME_TYPE,
1639	ip INTEGER DEFAULT NULL
1640) INHERITS (project_3.events_base);
1641CREATE INDEX ON project_3.sessions("user");
1642CREATE INDEX ON project_3.sessions(ctime);
1643CREATE INDEX ON project_3.sessions(ptime);
1644CREATE INDEX ON project_3.sessions(etime);
1645CREATE INDEX ON project_3.sessions(eptime);
1646
1647CREATE OR REPLACE FUNCTION project_3.sessions_insert_trigger()
1648    RETURNS TRIGGER AS $$
1649DECLARE
1650	tm VARCHAR := '';
1651	tmon VARCHAR :='';
1652	tmoff VARCHAR :='';
1653    tbl VARCHAR(255) := '';
1654    tblexists VARCHAR(255) := '';
1655BEGIN
1656	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1657	tbl := 'project_3.sessions_' || tm;
1658	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1659	IF tblexists IS NULL THEN
1660		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1661		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1662		EXECUTE 'CREATE TABLE ' || tbl || '(
1663			LIKE project_3.sessions INCLUDING ALL,
1664			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1665			CONSTRAINT sess_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
1666			CONSTRAINT sess_' || tm || '_fk2 FOREIGN KEY (ip) REFERENCES project_3.ips ON DELETE CASCADE,
1667			CONSTRAINT sess_' || tm || '_fk3 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
1668			CONSTRAINT sess_' || tm || '_fk4 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
1669			CONSTRAINT sess_' || tm || '_fk5 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL
1670			) INHERITS (project_3.sessions) WITH (OIDS=FALSE);';
1671	END IF;
1672	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1673	RETURN NULL;
1674END;
1675$$ LANGUAGE plpgsql;
1676
1677CREATE TRIGGER insert_sessions_trigger BEFORE INSERT ON project_3.sessions
1678    FOR EACH ROW EXECUTE PROCEDURE project_3.sessions_insert_trigger();
1679
1680
1681CREATE TABLE project_3.session_params(
1682	session BIGINT NOT NULL
1683) INHERITS (project_3.event_params_base);
1684CREATE INDEX ON project_3.session_params(session);
1685CREATE INDEX ON project_3.session_params(ptime);
1686CREATE INDEX ON project_3.session_params(name);
1687CREATE INDEX ON project_3.session_params("value");
1688
1689CREATE OR REPLACE FUNCTION project_3.session_params_insert_trigger()
1690    RETURNS TRIGGER AS $$
1691DECLARE
1692	tm VARCHAR := '';
1693    tbl VARCHAR := '';
1694    tblexists VARCHAR := '';
1695    tmon VARCHAR := '';
1696    tmoff VARCHAR := '';
1697BEGIN
1698	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1699	tbl := 'project_3.session_params_' || tm;
1700	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1701	IF tblexists IS NULL THEN
1702		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1703		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1704		EXECUTE 'CREATE TABLE ' || tbl || '(
1705			LIKE project_3.session_params INCLUDING ALL,
1706			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1707			CONSTRAINT sessp_' || tm || '_fk1 FOREIGN KEY (session) REFERENCES project_3.sessions_' || tm ||' ON DELETE CASCADE,
1708			CONSTRAINT sessp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
1709			) INHERITS (project_3.session_params) WITH (OIDS=FALSE);';
1710	END IF;
1711	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1712	RETURN NULL;
1713END;
1714$$ LANGUAGE plpgsql;
1715
1716CREATE TRIGGER insert_session_params_trigger BEFORE INSERT ON project_3.session_params
1717    FOR EACH ROW EXECUTE PROCEDURE project_3.session_params_insert_trigger();
1718
1719--payments
1720CREATE TABLE project_3.skus(
1721	id SERIAL NOT NULL PRIMARY KEY,
1722	sku VARCHAR(128) NOT NULL UNIQUE,
1723	name NAME_TYPE
1724);
1725
1726CREATE TABLE project_3.currencies(
1727	id SERIAL NOT NULL PRIMARY KEY,
1728	name VARCHAR(3) NOT NULL UNIQUE,
1729	factor NUMERIC NOT NULL DEFAULT 1
1730);
1731
1732CREATE TABLE project_3.payments(
1733	id BIGSERIAL NOT NULL PRIMARY KEY,
1734	sku INTEGER DEFAULT NULL,
1735	amount NUMERIC NOT NULL,
1736	price NUMERIC NOT NULL,
1737	currency INTEGER NOT NULL
1738) INHERITS (project_3.events_base);
1739CREATE INDEX ON project_3.payments("user");
1740CREATE INDEX ON project_3.payments(ctime);
1741CREATE INDEX ON project_3.payments(ptime);
1742CREATE INDEX ON project_3.payments(sku);
1743
1744CREATE OR REPLACE FUNCTION project_3.payments_insert_trigger()
1745    RETURNS TRIGGER AS $$
1746DECLARE
1747	tm VARCHAR := '';
1748    tbl VARCHAR(255) := '';
1749    tblexists VARCHAR(255) := '';
1750    tmon VARCHAR := 0;
1751    tmoff VARCHAR := 0;
1752BEGIN
1753	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1754	tbl := 'project_3.payments_' || tm;
1755	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1756	IF tblexists IS NULL THEN
1757		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1758		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1759		EXECUTE 'CREATE TABLE ' || tbl || '(
1760			LIKE project_3.payments INCLUDING ALL,
1761			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1762			CONSTRAINT pay_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
1763			CONSTRAINT pay_' || tm || '_fk2 FOREIGN KEY (sku) REFERENCES project_3.skus ON DELETE SET NULL,
1764			CONSTRAINT pay_' || tm || '_fk3 FOREIGN KEY (currency) REFERENCES project_3.currencies,
1765			CONSTRAINT pay_' || tm || '_fk4 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
1766			CONSTRAINT pay_' || tm || '_fk5 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
1767			CONSTRAINT pay_' || tm || '_fk6 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL
1768			) INHERITS (project_3.payments) WITH (OIDS=FALSE);';
1769	END IF;
1770	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1771	RETURN NULL;
1772END;
1773$$ LANGUAGE plpgsql;
1774
1775CREATE TRIGGER insert_payments_trigger BEFORE INSERT ON project_3.payments
1776    FOR EACH ROW EXECUTE PROCEDURE project_3.payments_insert_trigger();
1777
1778CREATE TABLE project_3.payment_params(
1779	payment BIGINT NOT NULL
1780) INHERITS(project_3.event_params_base);
1781CREATE INDEX ON project_3.payment_params(payment);
1782CREATE INDEX ON project_3.payment_params(ptime);
1783CREATE INDEX ON project_3.payment_params(name);
1784CREATE INDEX ON project_3.payment_params("value");
1785
1786CREATE OR REPLACE FUNCTION project_3.payment_params_insert_trigger()
1787    RETURNS TRIGGER AS $$
1788DECLARE
1789	tm VARCHAR := '';
1790    tbl VARCHAR := '';
1791    tblexists VARCHAR := '';
1792    tmon VARCHAR := '';
1793    tmoff VARCHAR := '';
1794BEGIN
1795	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1796	tbl := 'project_3.payment_params_' || tm;
1797	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1798	IF tblexists IS NULL THEN
1799		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1800		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1801		EXECUTE 'CREATE TABLE ' || tbl || '(
1802			LIKE project_3.payment_params INCLUDING ALL,
1803			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1804			CONSTRAINT payp_' || tm || '_fk1 FOREIGN KEY (payment) REFERENCES project_3.payments_' || tm ||' ON DELETE CASCADE,
1805			CONSTRAINT payp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
1806			) INHERITS (project_3.payment_params) WITH (OIDS=FALSE);';
1807	END IF;
1808	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1809	RETURN NULL;
1810END;
1811$$ LANGUAGE plpgsql;
1812
1813CREATE TRIGGER insert_pparams_trigger BEFORE INSERT ON project_3.payment_params
1814    FOR EACH ROW EXECUTE PROCEDURE project_3.payment_params_insert_trigger();
1815
1816--stats
1817CREATE TABLE project_3.stat_descr(
1818	id SERIAL NOT NULL PRIMARY KEY,
1819	name NAME_TYPE,
1820	"type" INTEGER NOT NULL DEFAULT 0,
1821	proc INTEGER NOT NULL DEFAULT 0,
1822	pproc INTEGER NOT NULL DEFAULT 0,
1823	"value" VARCHAR,
1824	period INTEGER NOT NULL DEFAULT 1,
1825  	recalc INTEGER NOT NULL DEFAULT 1,
1826  	ttl INTEGER NOT NULL DEFAULT 0,
1827  	uptime INTEGER NOT NULL DEFAULT 0,
1828  	flags INTEGER NOT NULL DEFAULT 0
1829);
1830CREATE INDEX ON project_3.stat_descr(name);
1831CREATE INDEX ON project_3.stat_descr("type");
1832CREATE INDEX ON project_3.stat_descr(proc);
1833CREATE INDEX ON project_3.stat_descr(pproc);
1834CREATE INDEX ON project_3.stat_descr(period);
1835CREATE INDEX ON project_3.stat_descr(ttl);
1836CREATE INDEX ON project_3.stat_descr(uptime);
1837
1838CREATE TABLE project_3.stats(
1839	stat INTEGER NOT NULL REFERENCES project_3.stat_descr ON DELETE CASCADE,
1840	"time" INTEGER NOT NULL,
1841	ptime project_3.TIME_TYPE,
1842	"value" NUMERIC NOT NULL DEFAULT 0,
1843  	admin_value NUMERIC NOT NULL DEFAULT 0,
1844  	blocked_value NUMERIC NOT NULL DEFAULT 0,
1845  	anon_value NUMERIC NOT NULL DEFAULT 0,	
1846	PRIMARY KEY (stat,"time")
1847);
1848CREATE INDEX ON project_3.stats(ptime);
1849
1850CREATE OR REPLACE FUNCTION project_3.stats_insert_trigger()
1851    RETURNS TRIGGER AS $$
1852DECLARE
1853    tbl VARCHAR := '';
1854    tblexists VARCHAR := '';
1855BEGIN
1856	tbl := 'project_3.stats_' || NEW.stat;
1857	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1858	IF tblexists IS NULL THEN
1859		EXECUTE 'CREATE TABLE ' || tbl || '(
1860			LIKE project_3.stats INCLUDING ALL,
1861			CHECK (stat=' || NEW.stat || '),
1862			CONSTRAINT stat_' || NEW.stat || '_fk FOREIGN KEY (stat) REFERENCES project_3.stat_descr ON DELETE CASCADE
1863			) INHERITS (project_3.stats) WITH (OIDS=FALSE);';
1864	END IF;
1865	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1866	RETURN NULL;
1867END;
1868$$ LANGUAGE plpgsql;
1869
1870CREATE TRIGGER insert_stats_trigger BEFORE INSERT ON project_3.stats
1871    FOR EACH ROW EXECUTE PROCEDURE project_3.stats_insert_trigger();
1872
1873CREATE TABLE project_3.actions(
1874	id SERIAL NOT NULL PRIMARY KEY,
1875	name NAME_TYPE,
1876	description VARCHAR,
1877	stime INTEGER,
1878	etime INTEGER
1879);
1880CREATE INDEX ON project_3.actions(stime);
1881CREATE INDEX ON project_3.actions(etime);
1882
1883CREATE TABLE project_3.dashboard_vals(
1884	id SERIAL NOT NULL PRIMARY KEY,
1885	"type" INTEGER NOT NULL DEFAULT 0,
1886  	name NAME_TYPE,
1887  	x INTEGER NOT NULL DEFAULT 0,
1888  	y INTEGER NOT NULL DEFAULT 0,
1889  	w INTEGER NOT NULL DEFAULT 0,
1890  	h INTEGER NOT NULL DEFAULT 0,
1891	stat INTEGER NOT NULL REFERENCES project_3.stat_descr ON DELETE CASCADE,
1892  	period INTEGER NOT NULL DEFAULT 0,
1893  	params JSON,
1894  	flags INTEGER NOT NULL DEFAULT 0
1895);
1896
1897--events
1898CREATE TABLE project_3.events(
1899	id BIGSERIAL NOT NULL PRIMARY KEY,
1900	name INTEGER NOT NULL REFERENCES project_3.names ON DELETE CASCADE,
1901	"type" INTEGER NOT NULL,
1902	"value" BIGINT NOT NULL
1903) INHERITS (project_3.events_base);
1904CREATE INDEX ON project_3.events(name);
1905CREATE INDEX ON project_3.events(ctime);
1906CREATE INDEX ON project_3.events(ptime);
1907CREATE INDEX ON project_3.events("user");
1908CREATE INDEX ON project_3.events("type");
1909CREATE INDEX ON project_3.events("value");
1910
1911CREATE OR REPLACE FUNCTION project_3.events_insert_trigger()
1912    RETURNS TRIGGER AS $$
1913DECLARE
1914	tm VARCHAR := '';
1915    tbl VARCHAR := '';
1916    tblexists VARCHAR := '';
1917    tmon VARCHAR :=0;
1918    tmoff VARCHAR :=0;
1919BEGIN
1920	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1921	tm := NEW.name || '_' || tm;
1922	tbl := 'project_3.events_' || tm;
1923	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1924	IF tblexists IS NULL THEN
1925		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1926		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1927		EXECUTE 'CREATE TABLE ' || tbl || '(
1928			LIKE project_3.events INCLUDING ALL,
1929			CHECK (name=' || NEW.name || '),
1930			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1931			CONSTRAINT ev_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
1932			CONSTRAINT ev_' || tm || '_fk2 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
1933			CONSTRAINT ev_' || tm || '_fk3 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
1934			CONSTRAINT ev_' || tm || '_fk4 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL,
1935			CONSTRAINT ev_' || tm || '_fk5 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
1936			) INHERITS (project_3.events) WITH (OIDS=FALSE);';
1937	END IF;
1938	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1939	RETURN NULL;
1940END;
1941$$ LANGUAGE plpgsql;
1942
1943CREATE TRIGGER insert_events_trigger BEFORE INSERT ON project_3.events
1944    FOR EACH ROW EXECUTE PROCEDURE project_3.events_insert_trigger();
1945    
1946CREATE TABLE project_3.event_descr(
1947	id SERIAL NOT NULL PRIMARY KEY,
1948	event_name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
1949	event_value project_3.PVAL_TYPE,
1950	param_name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
1951	param_value project_3.PVAL_TYPE,
1952	UNIQUE(event_name,event_value,param_name,param_value)
1953);
1954CREATE INDEX ON project_3.event_descr(event_name);
1955CREATE INDEX ON project_3.event_descr(event_value);
1956CREATE INDEX ON project_3.event_descr(param_name);
1957CREATE INDEX ON project_3.event_descr(param_value);
1958CREATE INDEX ON project_3.event_descr(event_name,param_name);
1959
1960CREATE TABLE project_3.event_params(
1961	event BIGINT NOT NULL,
1962	event_name INTEGER NOT NULL,
1963	descr INTEGER REFERENCES project_3.event_descr ON DELETE CASCADE
1964) INHERITS (project_3.event_params_base);
1965CREATE INDEX ON project_3.event_params(event);
1966CREATE INDEX ON project_3.event_params(event_name);
1967CREATE INDEX ON project_3.event_params(ptime);
1968CREATE INDEX ON project_3.event_params(name);
1969CREATE INDEX ON project_3.event_params("value");
1970
1971CREATE OR REPLACE FUNCTION project_3.event_params_insert_trigger()
1972    RETURNS TRIGGER AS $$
1973DECLARE
1974	tm VARCHAR := '';
1975    tbl VARCHAR := '';
1976    tblexists VARCHAR := '';
1977    tmon VARCHAR :='';
1978    tmoff VARCHAR :='';
1979BEGIN
1980	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
1981	tm := NEW.event_name || '_' || tm;
1982	tbl := 'project_3.event_params_' || tm;
1983	SELECT to_regclass((tbl)::varchar) INTO tblexists;
1984	IF tblexists IS NULL THEN
1985		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
1986		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
1987		EXECUTE 'CREATE TABLE ' || tbl || '(
1988			LIKE project_3.event_params INCLUDING ALL,
1989			CHECK (event_name=' || NEW.event_name || '),
1990			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
1991			CONSTRAINT evp_' || tm || '_fk1 FOREIGN KEY (event) REFERENCES project_3.events_' || tm || ' ON DELETE CASCADE,
1992			CONSTRAINT evp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE,
1993			CONSTRAINT evp_' || tm || '_fk3 FOREIGN KEY (descr) REFERENCES project_3.event_descr ON DELETE CASCADE
1994			) INHERITS (project_3.event_params) WITH (OIDS=FALSE);';
1995	END IF;
1996	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
1997	RETURN NULL;
1998END;
1999$$ LANGUAGE plpgsql;
2000
2001CREATE TRIGGER insert_event_params_trigger BEFORE INSERT ON project_3.event_params
2002    FOR EACH ROW EXECUTE PROCEDURE project_3.event_params_insert_trigger();
2003
2004--v2
2005CREATE TABLE project_3.project_custom_stat(
2006	id SERIAL NOT NULL PRIMARY KEY,
2007	title NAME_TYPE,
2008	type INTEGER NOT NULL DEFAULT 0,
2009	aggs VARCHAR(200) NOT NULL,
2010	compare BOOL NOT NULL DEFAULT TRUE
2011);
2012
2013CREATE TABLE project_3.project_custom_stat_filters(
2014	id SERIAL NOT NULL PRIMARY KEY,
2015	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
2016	name NAME_TYPE,
2017	title NAME_TYPE,
2018	type NAME_TYPE,
2019	exdata VARCHAR(200) NOT NULL
2020);
2021
2022CREATE TABLE project_3.project_custom_stat_values(
2023	id SERIAL NOT NULL PRIMARY KEY,
2024	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
2025	name NAME_TYPE,
2026	title NAME_TYPE
2027);
2028
2029CREATE TABLE project_3.project_custom_stat_query(
2030	id SERIAL NOT NULL PRIMARY KEY,
2031	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
2032	name NAME_TYPE,
2033	tables NAME_TYPE,
2034	fields VARCHAR(200),
2035	cond VARCHAR(200),
2036	"group" NAME_TYPE
2037);
2038
2039--v3
2040CREATE TABLE project_3.tasks(
2041	id SERIAL NOT NULL PRIMARY KEY,
2042	task NAME_TYPE,
2043	params VARCHAR,
2044	pid INTEGER DEFAULT NULL,
2045	heartbeat INTEGER DEFAULT NULL,
2046	state INTEGER NOT NULL DEFAULT 0
2047);
2048
2049--v4
2050ALTER TABLE project_3.events ADD COLUMN "session" INTEGER DEFAULT NULL REFERENCES project_3.sessions ON DELETE SET NULL;
2051CREATE INDEX ON project_3.events ("session");
2052
2053--v5
2054CREATE TABLE project_3.leaving_users(
2055	id SERIAL NOT NULL PRIMARY KEY,
2056	"user"  INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
2057	time_leave CTIME_TYPE,
2058	time_return INTEGER NOT NULL DEFAULT 0,
2059	state INTEGER DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
2060);
2061
2062CREATE INDEX ON project_3.leaving_users (time_leave,time_return,state);
2063
2064--v6
2065ALTER TABLE project_3.currencies ADD COLUMN flags INTEGER NOT NULL DEFAULT 0;
2066
2067
2068--v7
2069DROP INDEX IF EXISTS project_3.users_version_idx;
2070DROP INDEX IF EXISTS project_3.users_person_idx;
2071DROP INDEX IF EXISTS project_3.users_state_idx;
2072DROP INDEX IF EXISTS project_3.users_flags_idx;
2073DROP INDEX IF EXISTS project_3.users_last_penter_idx;
2074DROP INDEX IF EXISTS project_3.users_last_session_idx;
2075DROP INDEX IF EXISTS project_3.users_regptime_idx;
2076DROP INDEX IF EXISTS project_3.users_regip_idx;
2077
2078DROP INDEX IF EXISTS project_3.sessions_year_idx;
2079DROP INDEX IF EXISTS project_3.sessions_month_idx;
2080DROP INDEX IF EXISTS project_3.sessions_week_idx;
2081DROP INDEX IF EXISTS project_3.sessions_day_idx;
2082DROP INDEX IF EXISTS project_3.sessions_hour_idx;
2083DROP INDEX IF EXISTS project_3.sessions_ip_idx;
2084DROP INDEX IF EXISTS project_3.sessions_device_idx;
2085DROP INDEX IF EXISTS project_3.sessions_version_idx;
2086DROP INDEX IF EXISTS project_3.sessions_state_idx;
2087
2088DROP INDEX IF EXISTS project_3.payments_year_idx;
2089DROP INDEX IF EXISTS project_3.payments_month_idx;
2090DROP INDEX IF EXISTS project_3.payments_week_idx;
2091DROP INDEX IF EXISTS project_3.payments_day_idx;
2092DROP INDEX IF EXISTS project_3.payments_hour_idx;
2093DROP INDEX IF EXISTS project_3.payments_device_idx;
2094DROP INDEX IF EXISTS project_3.payments_version_idx;
2095DROP INDEX IF EXISTS project_3.payments_state_idx;
2096
2097DROP INDEX IF EXISTS project_3.events_year_idx;
2098DROP INDEX IF EXISTS project_3.events_month_idx;
2099DROP INDEX IF EXISTS project_3.events_week_idx;
2100DROP INDEX IF EXISTS project_3.events_day_idx;
2101DROP INDEX IF EXISTS project_3.events_hour_idx;
2102DROP INDEX IF EXISTS project_3.events_device_idx;
2103DROP INDEX IF EXISTS project_3.events_version_idx;
2104DROP INDEX IF EXISTS project_3.events_state_idx;
2105
2106--v8
2107CREATE TABLE project_3.app_versions (
2108  id SERIAL NOT NULL,
2109  name public.name_type,
2110  description VARCHAR,
2111  rdate public.ctime_type,
2112  PRIMARY KEY(id)
2113);
2114
2115--v9
2116ALTER TABLE
2117  project_3.users
2118ADD COLUMN payed_value REAL DEFAULT 0 NOT NULL;
2119
2120--v10
2121ALTER TABLE project_3.stat_descr
2122  ALTER COLUMN period DROP DEFAULT;
2123
2124ALTER TABLE project_3.stat_descr
2125  ALTER COLUMN period TYPE BIGINT;
2126
2127ALTER TABLE project_3.stat_descr
2128  ALTER COLUMN period SET DEFAULT 1;
2129
2130INSERT INTO project_3.params(name,"value") VALUES('dbversion','10');
2131COMMIT;
2132
2133                )
2134
2135        )
2136
2137    [3] => Array
2138        (
2139            [file] => /home/pistat/src/Admin/Admin/Projects.php
2140            [line] => 162
2141            [function] => executeQuery
2142            [class] => Engine\DataProvider\DB\Provider
2143            [type] => ->
2144            [args] => Array
2145                (
2146                    [0] => pistat
2147                    [1] => BEGIN;
2148CREATE SCHEMA project_3;
2149GRANT ALL ON SCHEMA project_3 TO postgres;
2150GRANT ALL ON SCHEMA project_3 TO public;
2151CREATE DOMAIN project_3.PTIME_TYPE TIMESTAMPTZ NOT NULL DEFAULT current_timestamp;
2152CREATE DOMAIN project_3.TIME_TYPE TIMESTAMPTZ;
2153CREATE DOMAIN project_3.YEAR_TYPE INTEGER NOT NULL DEFAULT date_part('year',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
2154CREATE DOMAIN project_3.MONTH_TYPE INTEGER NOT NULL DEFAULT date_part('month',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
2155CREATE DOMAIN project_3.WEEK_TYPE INTEGER NOT NULL DEFAULT date_part('week',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
2156CREATE DOMAIN project_3.DAY_TYPE INTEGER NOT NULL DEFAULT date_part('day',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
2157CREATE DOMAIN project_3.HOUR_TYPE INTEGER NOT NULL DEFAULT date_part('hour',current_timestamp AT TIME ZONE ''MSK'')::INTEGER;
2158
2159CREATE TABLE project_3.params(
2160	name NAME_TYPE PRIMARY KEY,
2161	"value" VARCHAR
2162);
2163
2164
2165CREATE TABLE project_3.names(
2166	id SERIAL NOT NULL PRIMARY KEY,
2167	"type" INTEGER NOT NULL DEFAULT 0,
2168	subtype INTEGER NOT NULL DEFAULT 0,
2169	"class" INTEGER NOT NULL DEFAULT 0,
2170	name NAME_TYPE
2171);
2172CREATE INDEX ON project_3.names("type");
2173CREATE INDEX ON project_3.names(subtype);
2174CREATE INDEX ON project_3.names(name);
2175CREATE INDEX ON project_3.names(lower(name));
2176
2177CREATE TABLE project_3."values"(
2178	id BIGSERIAL NOT NULL PRIMARY KEY,
2179	"type" INTEGER NOT NULL DEFAULT 0,
2180	subtype INTEGER DEFAULT NULL REFERENCES project_3.names ON DELETE CASCADE,
2181	"value" NAME_TYPE,
2182	num_value NUMERIC DEFAULT NULL,
2183	text_value VARCHAR DEFAULT NULL
2184);
2185CREATE INDEX ON project_3."values"("type");
2186CREATE INDEX ON project_3."values"(subtype);
2187CREATE INDEX ON project_3."values"("value");
2188CREATE INDEX ON project_3."values"(num_value);
2189CREATE INDEX ON project_3."values"(lower("value"));
2190
2191CREATE OR REPLACE FUNCTION project_3.values_insert_trigger()
2192    RETURNS TRIGGER AS $$
2193DECLARE
2194    tbl VARCHAR := '';
2195    tblexists VARCHAR := '';
2196BEGIN
2197	tbl := 'project_3.values_' || NEW."type";
2198	SELECT to_regclass((tbl)::varchar) INTO tblexists;
2199	IF tblexists IS NULL THEN
2200		EXECUTE 'CREATE TABLE ' || tbl || '(
2201			CHECK ("type"=' || NEW."type" || '), 
2202			CONSTRAINT val_' || NEW."type" || '_pk PRIMARY KEY(id), 
2203			CONSTRAINT val_' || NEW."type" || '_fkey FOREIGN KEY (subtype) REFERENCES project_3.names ON DELETE CASCADE
2204			) INHERITS (project_3.values) WITH (OIDS=FALSE);';
2205		EXECUTE 'CREATE INDEX ON ' || tbl || '("type");';
2206		EXECUTE 'CREATE INDEX ON ' || tbl || '(subtype);';
2207		EXECUTE 'CREATE INDEX ON ' || tbl || '("value");';
2208		EXECUTE 'CREATE INDEX ON ' || tbl || '(lower("value"));';
2209		EXECUTE 'CREATE INDEX ON ' || tbl || '(num_value);';
2210	END IF;
2211	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
2212	RETURN NULL;
2213END;
2214$$ LANGUAGE plpgsql;
2215
2216CREATE TRIGGER insert_values_trigger BEFORE INSERT ON project_3."values"
2217    FOR EACH ROW EXECUTE PROCEDURE project_3.values_insert_trigger();
2218
2219
2220CREATE DOMAIN project_3.PNAME_TYPE AS INTEGER NOT NULL;
2221CREATE DOMAIN project_3.PVAL_TYPE AS BIGINT NOT NULL;
2222
2223
2224CREATE TABLE project_3.versions(
2225	id SERIAL NOT NULL PRIMARY KEY,
2226	version NAME_TYPE,
2227	ctime CTIME_TYPE,
2228  	flags integer NOT NULL DEFAULT 0
2229);
2230CREATE INDEX ON project_3.versions(version);
2231CREATE INDEX ON project_3.versions(flags);
2232
2233CREATE TABLE project_3.persons(
2234	id SERIAL NOT NULL PRIMARY KEY
2235);
2236
2237CREATE TABLE project_3.ips(
2238	id BIGSERIAL NOT NULL PRIMARY KEY,
2239	ip VARCHAR(64) NOT NULL UNIQUE,
2240	country VARCHAR(2)
2241);
2242CREATE INDEX ON project_3.ips(country);
2243
2244--users
2245CREATE TABLE project_3.devices(
2246	id SERIAL NOT NULL PRIMARY KEY,
2247	udid VARCHAR(128) NOT NULL UNIQUE,
2248	ctime CTIME_TYPE
2249);
2250
2251CREATE TABLE project_3.device_params(
2252	device INTEGER NOT NULL REFERENCES project_3.devices ON DELETE CASCADE,
2253	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
2254	"value" project_3.PVAL_TYPE
2255);
2256CREATE INDEX ON project_3.device_params(device);
2257CREATE INDEX ON project_3.device_params(name);
2258CREATE INDEX ON project_3.device_params("value");
2259
2260
2261CREATE TABLE project_3.user_states(
2262	id BIGSERIAL NOT NULL PRIMARY KEY,
2263	"user" INTEGER NOT NULL, 
2264	ctime CTIME_TYPE
2265);
2266
2267CREATE TABLE project_3.state_params(
2268	state INTEGER NOT NULL REFERENCES project_3.user_states ON DELETE CASCADE,
2269	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
2270	"value" project_3.PVAL_TYPE
2271);
2272CREATE INDEX ON project_3.state_params(state);
2273CREATE INDEX ON project_3.state_params(name);
2274CREATE INDEX ON project_3.state_params("value");
2275
2276
2277CREATE TABLE project_3.users(
2278	id SERIAL NOT NULL PRIMARY KEY,
2279	token VARCHAR(128) NOT NULL UNIQUE,
2280	uid VARCHAR(128),
2281	person INTEGER DEFAULT NULL REFERENCES project_3.persons ON DELETE SET NULL,
2282	ctime CTIME_TYPE,
2283	regtime INTEGER DEFAULT NULL,
2284	regptime project_3.TIME_TYPE,
2285	regip INTEGER DEFAULT NULL REFERENCES project_3.ips ON DELETE SET NULL,
2286	last_session INTEGER DEFAULT NULL,
2287	last_enter INTEGER NOT NULL DEFAULT 0,
2288	last_penter project_3.TIME_TYPE,
2289	last_activity INTEGER NOT NULL DEFAULT 0,
2290	pays INTEGER NOT NULL DEFAULT 0,
2291	flags INTEGER NOT NULL DEFAULT 0,
2292	device INTEGER DEFAULT NULL REFERENCES project_3.devices ON DELETE SET NULL,
2293	version INTEGER DEFAULT NULL REFERENCES project_3.versions ON DELETE SET NULL,
2294	state BIGINT DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
2295);
2296CREATE INDEX ON project_3.users(uid);
2297CREATE INDEX ON project_3.users(regtime);
2298CREATE INDEX ON project_3.users(last_enter);
2299CREATE INDEX ON project_3.users(last_activity);
2300CREATE INDEX ON project_3.users(pays);
2301
2302ALTER TABLE project_3.user_states ADD CONSTRAINT user_states_user_fkey FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE;
2303
2304CREATE TABLE project_3.user_devices(
2305	device INTEGER NOT NULL REFERENCES project_3.devices ON DELETE CASCADE,
2306	"user" INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
2307	ctime CTIME_TYPE,
2308	PRIMARY KEY(device,"user")
2309);
2310
2311CREATE TABLE project_3.packs(
2312	"user" INTEGER NOT NULL,
2313  	pack VARCHAR(128) NOT NULL,
2314  	ctime CTIME_TYPE,
2315  	command VARCHAR(128),
2316  	params VARCHAR,
2317  	processor INTEGER NOT NULL DEFAULT 0,
2318  	status INTEGER NOT NULL DEFAULT 0,
2319  	PRIMARY KEY("user",pack)
2320);
2321CREATE INDEX ON project_3.packs(ctime);
2322CREATE INDEX ON project_3.packs(processor);
2323CREATE INDEX ON project_3.packs(status);
2324
2325CREATE TABLE project_3.events_base(
2326	"user" INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
2327	ctime CTIME_TYPE,
2328	ptime project_3.PTIME_TYPE,
2329	year project_3.YEAR_TYPE,
2330	month project_3.MONTH_TYPE,
2331	week project_3.WEEK_TYPE,
2332	day project_3.DAY_TYPE,
2333	hour project_3.HOUR_TYPE,
2334	device INTEGER DEFAULT NULL REFERENCES project_3.devices ON DELETE SET NULL,
2335	version INTEGER DEFAULT NULL REFERENCES project_3.versions ON DELETE SET NULL,
2336	state INTEGER DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
2337);
2338CREATE TABLE project_3.event_params_base(
2339	ptime project_3.TIME_TYPE,
2340	name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
2341	"value" project_3.PVAL_TYPE
2342);
2343
2344--sessions
2345CREATE TABLE project_3.sessions(
2346	id BIGSERIAL NOT NULL PRIMARY KEY,
2347	etime INTEGER DEFAULT NULL,
2348	eptime project_3.TIME_TYPE,
2349	ip INTEGER DEFAULT NULL
2350) INHERITS (project_3.events_base);
2351CREATE INDEX ON project_3.sessions("user");
2352CREATE INDEX ON project_3.sessions(ctime);
2353CREATE INDEX ON project_3.sessions(ptime);
2354CREATE INDEX ON project_3.sessions(etime);
2355CREATE INDEX ON project_3.sessions(eptime);
2356
2357CREATE OR REPLACE FUNCTION project_3.sessions_insert_trigger()
2358    RETURNS TRIGGER AS $$
2359DECLARE
2360	tm VARCHAR := '';
2361	tmon VARCHAR :='';
2362	tmoff VARCHAR :='';
2363    tbl VARCHAR(255) := '';
2364    tblexists VARCHAR(255) := '';
2365BEGIN
2366	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
2367	tbl := 'project_3.sessions_' || tm;
2368	SELECT to_regclass((tbl)::varchar) INTO tblexists;
2369	IF tblexists IS NULL THEN
2370		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
2371		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
2372		EXECUTE 'CREATE TABLE ' || tbl || '(
2373			LIKE project_3.sessions INCLUDING ALL,
2374			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
2375			CONSTRAINT sess_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
2376			CONSTRAINT sess_' || tm || '_fk2 FOREIGN KEY (ip) REFERENCES project_3.ips ON DELETE CASCADE,
2377			CONSTRAINT sess_' || tm || '_fk3 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
2378			CONSTRAINT sess_' || tm || '_fk4 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
2379			CONSTRAINT sess_' || tm || '_fk5 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL
2380			) INHERITS (project_3.sessions) WITH (OIDS=FALSE);';
2381	END IF;
2382	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
2383	RETURN NULL;
2384END;
2385$$ LANGUAGE plpgsql;
2386
2387CREATE TRIGGER insert_sessions_trigger BEFORE INSERT ON project_3.sessions
2388    FOR EACH ROW EXECUTE PROCEDURE project_3.sessions_insert_trigger();
2389
2390
2391CREATE TABLE project_3.session_params(
2392	session BIGINT NOT NULL
2393) INHERITS (project_3.event_params_base);
2394CREATE INDEX ON project_3.session_params(session);
2395CREATE INDEX ON project_3.session_params(ptime);
2396CREATE INDEX ON project_3.session_params(name);
2397CREATE INDEX ON project_3.session_params("value");
2398
2399CREATE OR REPLACE FUNCTION project_3.session_params_insert_trigger()
2400    RETURNS TRIGGER AS $$
2401DECLARE
2402	tm VARCHAR := '';
2403    tbl VARCHAR := '';
2404    tblexists VARCHAR := '';
2405    tmon VARCHAR := '';
2406    tmoff VARCHAR := '';
2407BEGIN
2408	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
2409	tbl := 'project_3.session_params_' || tm;
2410	SELECT to_regclass((tbl)::varchar) INTO tblexists;
2411	IF tblexists IS NULL THEN
2412		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
2413		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
2414		EXECUTE 'CREATE TABLE ' || tbl || '(
2415			LIKE project_3.session_params INCLUDING ALL,
2416			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
2417			CONSTRAINT sessp_' || tm || '_fk1 FOREIGN KEY (session) REFERENCES project_3.sessions_' || tm ||' ON DELETE CASCADE,
2418			CONSTRAINT sessp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
2419			) INHERITS (project_3.session_params) WITH (OIDS=FALSE);';
2420	END IF;
2421	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
2422	RETURN NULL;
2423END;
2424$$ LANGUAGE plpgsql;
2425
2426CREATE TRIGGER insert_session_params_trigger BEFORE INSERT ON project_3.session_params
2427    FOR EACH ROW EXECUTE PROCEDURE project_3.session_params_insert_trigger();
2428
2429--payments
2430CREATE TABLE project_3.skus(
2431	id SERIAL NOT NULL PRIMARY KEY,
2432	sku VARCHAR(128) NOT NULL UNIQUE,
2433	name NAME_TYPE
2434);
2435
2436CREATE TABLE project_3.currencies(
2437	id SERIAL NOT NULL PRIMARY KEY,
2438	name VARCHAR(3) NOT NULL UNIQUE,
2439	factor NUMERIC NOT NULL DEFAULT 1
2440);
2441
2442CREATE TABLE project_3.payments(
2443	id BIGSERIAL NOT NULL PRIMARY KEY,
2444	sku INTEGER DEFAULT NULL,
2445	amount NUMERIC NOT NULL,
2446	price NUMERIC NOT NULL,
2447	currency INTEGER NOT NULL
2448) INHERITS (project_3.events_base);
2449CREATE INDEX ON project_3.payments("user");
2450CREATE INDEX ON project_3.payments(ctime);
2451CREATE INDEX ON project_3.payments(ptime);
2452CREATE INDEX ON project_3.payments(sku);
2453
2454CREATE OR REPLACE FUNCTION project_3.payments_insert_trigger()
2455    RETURNS TRIGGER AS $$
2456DECLARE
2457	tm VARCHAR := '';
2458    tbl VARCHAR(255) := '';
2459    tblexists VARCHAR(255) := '';
2460    tmon VARCHAR := 0;
2461    tmoff VARCHAR := 0;
2462BEGIN
2463	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
2464	tbl := 'project_3.payments_' || tm;
2465	SELECT to_regclass((tbl)::varchar) INTO tblexists;
2466	IF tblexists IS NULL THEN
2467		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
2468		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
2469		EXECUTE 'CREATE TABLE ' || tbl || '(
2470			LIKE project_3.payments INCLUDING ALL,
2471			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
2472			CONSTRAINT pay_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
2473			CONSTRAINT pay_' || tm || '_fk2 FOREIGN KEY (sku) REFERENCES project_3.skus ON DELETE SET NULL,
2474			CONSTRAINT pay_' || tm || '_fk3 FOREIGN KEY (currency) REFERENCES project_3.currencies,
2475			CONSTRAINT pay_' || tm || '_fk4 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
2476			CONSTRAINT pay_' || tm || '_fk5 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
2477			CONSTRAINT pay_' || tm || '_fk6 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL
2478			) INHERITS (project_3.payments) WITH (OIDS=FALSE);';
2479	END IF;
2480	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
2481	RETURN NULL;
2482END;
2483$$ LANGUAGE plpgsql;
2484
2485CREATE TRIGGER insert_payments_trigger BEFORE INSERT ON project_3.payments
2486    FOR EACH ROW EXECUTE PROCEDURE project_3.payments_insert_trigger();
2487
2488CREATE TABLE project_3.payment_params(
2489	payment BIGINT NOT NULL
2490) INHERITS(project_3.event_params_base);
2491CREATE INDEX ON project_3.payment_params(payment);
2492CREATE INDEX ON project_3.payment_params(ptime);
2493CREATE INDEX ON project_3.payment_params(name);
2494CREATE INDEX ON project_3.payment_params("value");
2495
2496CREATE OR REPLACE FUNCTION project_3.payment_params_insert_trigger()
2497    RETURNS TRIGGER AS $$
2498DECLARE
2499	tm VARCHAR := '';
2500    tbl VARCHAR := '';
2501    tblexists VARCHAR := '';
2502    tmon VARCHAR := '';
2503    tmoff VARCHAR := '';
2504BEGIN
2505	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
2506	tbl := 'project_3.payment_params_' || tm;
2507	SELECT to_regclass((tbl)::varchar) INTO tblexists;
2508	IF tblexists IS NULL THEN
2509		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
2510		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
2511		EXECUTE 'CREATE TABLE ' || tbl || '(
2512			LIKE project_3.payment_params INCLUDING ALL,
2513			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
2514			CONSTRAINT payp_' || tm || '_fk1 FOREIGN KEY (payment) REFERENCES project_3.payments_' || tm ||' ON DELETE CASCADE,
2515			CONSTRAINT payp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
2516			) INHERITS (project_3.payment_params) WITH (OIDS=FALSE);';
2517	END IF;
2518	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
2519	RETURN NULL;
2520END;
2521$$ LANGUAGE plpgsql;
2522
2523CREATE TRIGGER insert_pparams_trigger BEFORE INSERT ON project_3.payment_params
2524    FOR EACH ROW EXECUTE PROCEDURE project_3.payment_params_insert_trigger();
2525
2526--stats
2527CREATE TABLE project_3.stat_descr(
2528	id SERIAL NOT NULL PRIMARY KEY,
2529	name NAME_TYPE,
2530	"type" INTEGER NOT NULL DEFAULT 0,
2531	proc INTEGER NOT NULL DEFAULT 0,
2532	pproc INTEGER NOT NULL DEFAULT 0,
2533	"value" VARCHAR,
2534	period INTEGER NOT NULL DEFAULT 1,
2535  	recalc INTEGER NOT NULL DEFAULT 1,
2536  	ttl INTEGER NOT NULL DEFAULT 0,
2537  	uptime INTEGER NOT NULL DEFAULT 0,
2538  	flags INTEGER NOT NULL DEFAULT 0
2539);
2540CREATE INDEX ON project_3.stat_descr(name);
2541CREATE INDEX ON project_3.stat_descr("type");
2542CREATE INDEX ON project_3.stat_descr(proc);
2543CREATE INDEX ON project_3.stat_descr(pproc);
2544CREATE INDEX ON project_3.stat_descr(period);
2545CREATE INDEX ON project_3.stat_descr(ttl);
2546CREATE INDEX ON project_3.stat_descr(uptime);
2547
2548CREATE TABLE project_3.stats(
2549	stat INTEGER NOT NULL REFERENCES project_3.stat_descr ON DELETE CASCADE,
2550	"time" INTEGER NOT NULL,
2551	ptime project_3.TIME_TYPE,
2552	"value" NUMERIC NOT NULL DEFAULT 0,
2553  	admin_value NUMERIC NOT NULL DEFAULT 0,
2554  	blocked_value NUMERIC NOT NULL DEFAULT 0,
2555  	anon_value NUMERIC NOT NULL DEFAULT 0,	
2556	PRIMARY KEY (stat,"time")
2557);
2558CREATE INDEX ON project_3.stats(ptime);
2559
2560CREATE OR REPLACE FUNCTION project_3.stats_insert_trigger()
2561    RETURNS TRIGGER AS $$
2562DECLARE
2563    tbl VARCHAR := '';
2564    tblexists VARCHAR := '';
2565BEGIN
2566	tbl := 'project_3.stats_' || NEW.stat;
2567	SELECT to_regclass((tbl)::varchar) INTO tblexists;
2568	IF tblexists IS NULL THEN
2569		EXECUTE 'CREATE TABLE ' || tbl || '(
2570			LIKE project_3.stats INCLUDING ALL,
2571			CHECK (stat=' || NEW.stat || '),
2572			CONSTRAINT stat_' || NEW.stat || '_fk FOREIGN KEY (stat) REFERENCES project_3.stat_descr ON DELETE CASCADE
2573			) INHERITS (project_3.stats) WITH (OIDS=FALSE);';
2574	END IF;
2575	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
2576	RETURN NULL;
2577END;
2578$$ LANGUAGE plpgsql;
2579
2580CREATE TRIGGER insert_stats_trigger BEFORE INSERT ON project_3.stats
2581    FOR EACH ROW EXECUTE PROCEDURE project_3.stats_insert_trigger();
2582
2583CREATE TABLE project_3.actions(
2584	id SERIAL NOT NULL PRIMARY KEY,
2585	name NAME_TYPE,
2586	description VARCHAR,
2587	stime INTEGER,
2588	etime INTEGER
2589);
2590CREATE INDEX ON project_3.actions(stime);
2591CREATE INDEX ON project_3.actions(etime);
2592
2593CREATE TABLE project_3.dashboard_vals(
2594	id SERIAL NOT NULL PRIMARY KEY,
2595	"type" INTEGER NOT NULL DEFAULT 0,
2596  	name NAME_TYPE,
2597  	x INTEGER NOT NULL DEFAULT 0,
2598  	y INTEGER NOT NULL DEFAULT 0,
2599  	w INTEGER NOT NULL DEFAULT 0,
2600  	h INTEGER NOT NULL DEFAULT 0,
2601	stat INTEGER NOT NULL REFERENCES project_3.stat_descr ON DELETE CASCADE,
2602  	period INTEGER NOT NULL DEFAULT 0,
2603  	params JSON,
2604  	flags INTEGER NOT NULL DEFAULT 0
2605);
2606
2607--events
2608CREATE TABLE project_3.events(
2609	id BIGSERIAL NOT NULL PRIMARY KEY,
2610	name INTEGER NOT NULL REFERENCES project_3.names ON DELETE CASCADE,
2611	"type" INTEGER NOT NULL,
2612	"value" BIGINT NOT NULL
2613) INHERITS (project_3.events_base);
2614CREATE INDEX ON project_3.events(name);
2615CREATE INDEX ON project_3.events(ctime);
2616CREATE INDEX ON project_3.events(ptime);
2617CREATE INDEX ON project_3.events("user");
2618CREATE INDEX ON project_3.events("type");
2619CREATE INDEX ON project_3.events("value");
2620
2621CREATE OR REPLACE FUNCTION project_3.events_insert_trigger()
2622    RETURNS TRIGGER AS $$
2623DECLARE
2624	tm VARCHAR := '';
2625    tbl VARCHAR := '';
2626    tblexists VARCHAR := '';
2627    tmon VARCHAR :=0;
2628    tmoff VARCHAR :=0;
2629BEGIN
2630	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
2631	tm := NEW.name || '_' || tm;
2632	tbl := 'project_3.events_' || tm;
2633	SELECT to_regclass((tbl)::varchar) INTO tblexists;
2634	IF tblexists IS NULL THEN
2635		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
2636		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
2637		EXECUTE 'CREATE TABLE ' || tbl || '(
2638			LIKE project_3.events INCLUDING ALL,
2639			CHECK (name=' || NEW.name || '),
2640			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
2641			CONSTRAINT ev_' || tm || '_fk1 FOREIGN KEY ("user") REFERENCES project_3.users ON DELETE CASCADE,
2642			CONSTRAINT ev_' || tm || '_fk2 FOREIGN KEY (device) REFERENCES project_3.devices ON DELETE SET NULL,
2643			CONSTRAINT ev_' || tm || '_fk3 FOREIGN KEY (version) REFERENCES project_3.versions ON DELETE SET NULL,
2644			CONSTRAINT ev_' || tm || '_fk4 FOREIGN KEY (state) REFERENCES project_3.user_states ON DELETE SET NULL,
2645			CONSTRAINT ev_' || tm || '_fk5 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE
2646			) INHERITS (project_3.events) WITH (OIDS=FALSE);';
2647	END IF;
2648	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
2649	RETURN NULL;
2650END;
2651$$ LANGUAGE plpgsql;
2652
2653CREATE TRIGGER insert_events_trigger BEFORE INSERT ON project_3.events
2654    FOR EACH ROW EXECUTE PROCEDURE project_3.events_insert_trigger();
2655    
2656CREATE TABLE project_3.event_descr(
2657	id SERIAL NOT NULL PRIMARY KEY,
2658	event_name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
2659	event_value project_3.PVAL_TYPE,
2660	param_name project_3.PNAME_TYPE REFERENCES project_3.names ON DELETE CASCADE,
2661	param_value project_3.PVAL_TYPE,
2662	UNIQUE(event_name,event_value,param_name,param_value)
2663);
2664CREATE INDEX ON project_3.event_descr(event_name);
2665CREATE INDEX ON project_3.event_descr(event_value);
2666CREATE INDEX ON project_3.event_descr(param_name);
2667CREATE INDEX ON project_3.event_descr(param_value);
2668CREATE INDEX ON project_3.event_descr(event_name,param_name);
2669
2670CREATE TABLE project_3.event_params(
2671	event BIGINT NOT NULL,
2672	event_name INTEGER NOT NULL,
2673	descr INTEGER REFERENCES project_3.event_descr ON DELETE CASCADE
2674) INHERITS (project_3.event_params_base);
2675CREATE INDEX ON project_3.event_params(event);
2676CREATE INDEX ON project_3.event_params(event_name);
2677CREATE INDEX ON project_3.event_params(ptime);
2678CREATE INDEX ON project_3.event_params(name);
2679CREATE INDEX ON project_3.event_params("value");
2680
2681CREATE OR REPLACE FUNCTION project_3.event_params_insert_trigger()
2682    RETURNS TRIGGER AS $$
2683DECLARE
2684	tm VARCHAR := '';
2685    tbl VARCHAR := '';
2686    tblexists VARCHAR := '';
2687    tmon VARCHAR :='';
2688    tmoff VARCHAR :='';
2689BEGIN
2690	SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYYMMDD') INTO tm;
2691	tm := NEW.event_name || '_' || tm;
2692	tbl := 'project_3.event_params_' || tm;
2693	SELECT to_regclass((tbl)::varchar) INTO tblexists;
2694	IF tblexists IS NULL THEN
2695		SELECT to_char(NEW.ptime AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmon;
2696		SELECT to_char((NEW.ptime + INTERVAL '1 day') AT TIME ZONE ''MSK'','YYYY-MM-DD') INTO tmoff;
2697		EXECUTE 'CREATE TABLE ' || tbl || '(
2698			LIKE project_3.event_params INCLUDING ALL,
2699			CHECK (event_name=' || NEW.event_name || '),
2700			CHECK (ptime >= (TIMESTAMP '''|| tmon ||' 00:00:00'' AT TIME ZONE '''MSK''') AND ptime < (TIMESTAMP '''||tmoff||' 00:00:00'' AT TIME ZONE '''MSK''')),  
2701			CONSTRAINT evp_' || tm || '_fk1 FOREIGN KEY (event) REFERENCES project_3.events_' || tm || ' ON DELETE CASCADE,
2702			CONSTRAINT evp_' || tm || '_fk2 FOREIGN KEY (name) REFERENCES project_3.names ON DELETE CASCADE,
2703			CONSTRAINT evp_' || tm || '_fk3 FOREIGN KEY (descr) REFERENCES project_3.event_descr ON DELETE CASCADE
2704			) INHERITS (project_3.event_params) WITH (OIDS=FALSE);';
2705	END IF;
2706	EXECUTE 'INSERT INTO ' || tbl || ' select $1.*' USING NEW;
2707	RETURN NULL;
2708END;
2709$$ LANGUAGE plpgsql;
2710
2711CREATE TRIGGER insert_event_params_trigger BEFORE INSERT ON project_3.event_params
2712    FOR EACH ROW EXECUTE PROCEDURE project_3.event_params_insert_trigger();
2713
2714--v2
2715CREATE TABLE project_3.project_custom_stat(
2716	id SERIAL NOT NULL PRIMARY KEY,
2717	title NAME_TYPE,
2718	type INTEGER NOT NULL DEFAULT 0,
2719	aggs VARCHAR(200) NOT NULL,
2720	compare BOOL NOT NULL DEFAULT TRUE
2721);
2722
2723CREATE TABLE project_3.project_custom_stat_filters(
2724	id SERIAL NOT NULL PRIMARY KEY,
2725	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
2726	name NAME_TYPE,
2727	title NAME_TYPE,
2728	type NAME_TYPE,
2729	exdata VARCHAR(200) NOT NULL
2730);
2731
2732CREATE TABLE project_3.project_custom_stat_values(
2733	id SERIAL NOT NULL PRIMARY KEY,
2734	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
2735	name NAME_TYPE,
2736	title NAME_TYPE
2737);
2738
2739CREATE TABLE project_3.project_custom_stat_query(
2740	id SERIAL NOT NULL PRIMARY KEY,
2741	custom_stat INTEGER NOT NULL REFERENCES project_3.project_custom_stat ON DELETE CASCADE,
2742	name NAME_TYPE,
2743	tables NAME_TYPE,
2744	fields VARCHAR(200),
2745	cond VARCHAR(200),
2746	"group" NAME_TYPE
2747);
2748
2749--v3
2750CREATE TABLE project_3.tasks(
2751	id SERIAL NOT NULL PRIMARY KEY,
2752	task NAME_TYPE,
2753	params VARCHAR,
2754	pid INTEGER DEFAULT NULL,
2755	heartbeat INTEGER DEFAULT NULL,
2756	state INTEGER NOT NULL DEFAULT 0
2757);
2758
2759--v4
2760ALTER TABLE project_3.events ADD COLUMN "session" INTEGER DEFAULT NULL REFERENCES project_3.sessions ON DELETE SET NULL;
2761CREATE INDEX ON project_3.events ("session");
2762
2763--v5
2764CREATE TABLE project_3.leaving_users(
2765	id SERIAL NOT NULL PRIMARY KEY,
2766	"user"  INTEGER NOT NULL REFERENCES project_3.users ON DELETE CASCADE,
2767	time_leave CTIME_TYPE,
2768	time_return INTEGER NOT NULL DEFAULT 0,
2769	state INTEGER DEFAULT NULL REFERENCES project_3.user_states ON DELETE SET NULL
2770);
2771
2772CREATE INDEX ON project_3.leaving_users (time_leave,time_return,state);
2773
2774--v6
2775ALTER TABLE project_3.currencies ADD COLUMN flags INTEGER NOT NULL DEFAULT 0;
2776
2777
2778--v7
2779DROP INDEX IF EXISTS project_3.users_version_idx;
2780DROP INDEX IF EXISTS project_3.users_person_idx;
2781DROP INDEX IF EXISTS project_3.users_state_idx;
2782DROP INDEX IF EXISTS project_3.users_flags_idx;
2783DROP INDEX IF EXISTS project_3.users_last_penter_idx;
2784DROP INDEX IF EXISTS project_3.users_last_session_idx;
2785DROP INDEX IF EXISTS project_3.users_regptime_idx;
2786DROP INDEX IF EXISTS project_3.users_regip_idx;
2787
2788DROP INDEX IF EXISTS project_3.sessions_year_idx;
2789DROP INDEX IF EXISTS project_3.sessions_month_idx;
2790DROP INDEX IF EXISTS project_3.sessions_week_idx;
2791DROP INDEX IF EXISTS project_3.sessions_day_idx;
2792DROP INDEX IF EXISTS project_3.sessions_hour_idx;
2793DROP INDEX IF EXISTS project_3.sessions_ip_idx;
2794DROP INDEX IF EXISTS project_3.sessions_device_idx;
2795DROP INDEX IF EXISTS project_3.sessions_version_idx;
2796DROP INDEX IF EXISTS project_3.sessions_state_idx;
2797
2798DROP INDEX IF EXISTS project_3.payments_year_idx;
2799DROP INDEX IF EXISTS project_3.payments_month_idx;
2800DROP INDEX IF EXISTS project_3.payments_week_idx;
2801DROP INDEX IF EXISTS project_3.payments_day_idx;
2802DROP INDEX IF EXISTS project_3.payments_hour_idx;
2803DROP INDEX IF EXISTS project_3.payments_device_idx;
2804DROP INDEX IF EXISTS project_3.payments_version_idx;
2805DROP INDEX IF EXISTS project_3.payments_state_idx;
2806
2807DROP INDEX IF EXISTS project_3.events_year_idx;
2808DROP INDEX IF EXISTS project_3.events_month_idx;
2809DROP INDEX IF EXISTS project_3.events_week_idx;
2810DROP INDEX IF EXISTS project_3.events_day_idx;
2811DROP INDEX IF EXISTS project_3.events_hour_idx;
2812DROP INDEX IF EXISTS project_3.events_device_idx;
2813DROP INDEX IF EXISTS project_3.events_version_idx;
2814DROP INDEX IF EXISTS project_3.events_state_idx;
2815
2816--v8
2817CREATE TABLE project_3.app_versions (
2818  id SERIAL NOT NULL,
2819  name public.name_type,
2820  description VARCHAR,
2821  rdate public.ctime_type,
2822  PRIMARY KEY(id)
2823);
2824
2825--v9
2826ALTER TABLE
2827  project_3.users
2828ADD COLUMN payed_value REAL DEFAULT 0 NOT NULL;
2829
2830--v10
2831ALTER TABLE project_3.stat_descr
2832  ALTER COLUMN period DROP DEFAULT;
2833
2834ALTER TABLE project_3.stat_descr
2835  ALTER COLUMN period TYPE BIGINT;
2836
2837ALTER TABLE project_3.stat_descr
2838  ALTER COLUMN period SET DEFAULT 1;
2839
2840INSERT INTO project_3.params(name,"value") VALUES('dbversion','10');
2841COMMIT;
2842
2843                )
2844
2845        )
2846
2847    [4] => Array
2848        (
2849            [file] => /home/pistat/src/Admin/Base/ModelPage.php
2850            [line] => 127
2851            [function] => itemSaved
2852            [class] => Admin\Admin\Projects
2853            [type] => ->
2854            [args] => Array
2855                (
2856                    [0] => 3
2857                    [1] => 1
2858                )
2859
2860        )
2861
2862    [5] => Array
2863        (
2864            [file] => /home/pistat/src/Admin/Base/AdminTemplate.php
2865            [line] => 59
2866            [function] => save
2867            [class] => Admin\Base\ModelPage
2868            [type] => ->
2869            [args] => Array
2870                (
2871                )
2872
2873        )
2874
2875    [6] => Array
2876        (
2877            [file] => /home/pistat/src/Handlers/Ajax/AdminHandler.php
2878            [line] => 26
2879            [function] => ajax
2880            [class] => Admin\Base\AdminTemplate
2881            [type] => ->
2882            [args] => Array
2883                (
2884                    [0] => Array
2885                        (
2886                            [handler] => ajax
2887                            [module] => admin.projects
2888                            [action] => save
2889                            [data] => Array
2890                                (
2891                                    [parent] => 0
2892                                    [name] => TruckX_iOS
2893                                    [code] => 2222
2894                                    [description] => 
2895                                    [platform] => 0
2896                                    [callback] => 
2897                                    [app_key] => ZxdJE9gEaTBc1SvgawwxGpElepIyuQJs
2898                                    [secret_key] => Jr0aKlIceIjl8Nd6B0Fat4BHjOTESgsC
2899                                    [iconUrl] => 
2900                                )
2901
2902                            [_] => 1547191763367
2903                        )
2904
2905                )
2906
2907        )
2908
2909    [7] => Array
2910        (
2911            [file] => /home/pistat/src/Handlers/AjaxHandler.php
2912            [line] => 39
2913            [function] => handleRequest
2914            [class] => Handlers\Ajax\AdminHandler
2915            [type] => ->
2916            [args] => Array
2917                (
2918                    [0] => Array
2919                        (
2920                            [handler] => ajax
2921                            [module] => admin.projects
2922                            [action] => save
2923                            [data] => Array
2924                                (
2925                                    [parent] => 0
2926                                    [name] => TruckX_iOS
2927                                    [code] => 2222
2928                                    [description] => 
2929                                    [platform] => 0
2930                                    [callback] => 
2931                                    [app_key] => ZxdJE9gEaTBc1SvgawwxGpElepIyuQJs
2932                                    [secret_key] => Jr0aKlIceIjl8Nd6B0Fat4BHjOTESgsC
2933                                    [iconUrl] => 
2934                                )
2935
2936                            [_] => 1547191763367
2937                        )
2938
2939                )
2940
2941        )
2942
2943)