· 6 years ago · Jul 19, 2019, 02:12 PM
1-- *@*-pg_sql-*@* "koborp" database backup
2-- Version: v0.01.00, Created: 19.07.2019 17:06:18 MSK(+03); Month: July, Week: 29, Day: 200, Friday.
3
4--
5-- PostgreSQL database dump
6--
7
8-- Dumped from database version 9.5.2
9-- Dumped by pg_dump version 9.5.2
10
11SET statement_timeout = 0;
12SET lock_timeout = 0;
13SET client_encoding = 'UTF8';
14SET standard_conforming_strings = on;
15SET check_function_bodies = false;
16SET client_min_messages = warning;
17SET row_security = off;
18
19--
20-- Name: auth; Type: SCHEMA; Schema: -; Owner: kobo_owner
21--
22
23CREATE SCHEMA auth;
24
25
26ALTER SCHEMA auth OWNER TO kobo_owner;
27
28--
29-- Name: com; Type: SCHEMA; Schema: -; Owner: postgres
30--
31
32CREATE SCHEMA com;
33
34
35ALTER SCHEMA com OWNER TO postgres;
36
37--
38-- Name: ety; Type: SCHEMA; Schema: -; Owner: postgres
39--
40
41CREATE SCHEMA ety;
42
43
44ALTER SCHEMA ety OWNER TO postgres;
45
46--
47-- Name: pdic; Type: SCHEMA; Schema: -; Owner: postgres
48--
49
50CREATE SCHEMA pdic;
51
52
53ALTER SCHEMA pdic OWNER TO postgres;
54
55--
56-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
57--
58
59CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
60
61
62--
63-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
64--
65
66COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
67
68
69SET search_path = auth, pg_catalog;
70
71--
72-- Name: auth_expected_user(); Type: FUNCTION; Schema: auth; Owner: kobo_owner
73--
74
75CREATE FUNCTION auth_expected_user() RETURNS boolean
76 LANGUAGE plpgsql COST 10
77 AS $$--
78DECLARE
79_user_id integer;
80BEGIN
81 SELECT INTO _user_id current_setting('user_session_var.expected_user_id')::integer;
82 if NOT FOUND OR _user_id IS NULL then
83 RAISE EXCEPTION 'Failed to set current user for DB session: expected user session variable not set!';
84 end if;
85
86 SELECT ety.set_current_user_id(_user_id);
87 SELECT ety.reset_expected_user_id();
88
89 return true;
90END;
91$$;
92
93
94ALTER FUNCTION auth.auth_expected_user() OWNER TO kobo_owner;
95
96--
97-- Name: get_user_login_data(text); Type: FUNCTION; Schema: auth; Owner: kobo_owner
98--
99
100CREATE FUNCTION get_user_login_data(p_login text) RETURNS TABLE(code integer, user_id integer, alias text, entry_tag text)
101 LANGUAGE plpgsql IMMUTABLE COST 1 ROWS 1
102 AS $$--
103-- code 0 for all ok , auth available
104-- code 1 no user found
105-- code 2 no permit to login
106DECLARE
107 _user_id integer;
108 _alias text;
109 _entry_tag text;
110 _permit boolean;
111 _code integer;
112
113BEGIN
114 if p_login IS NULL THEN
115 return query select 1, NULL::integer , NULL::text, NULL::text;
116 end if;
117
118
119 select u.id, u.fullname, u.permit, u.entry_tag into _user_id, _alias, _permit, _entry_tag
120 FROM ety.user u where u.name = p_login and NOT u.entry_tag is NULL ;
121
122 if found then
123 if _permit then
124 perform ety.set_expected_user_id(_user_id);
125 return query select 0, _user_id::integer, _alias::text, _entry_tag::text;
126 else
127 return query select 2, _user_id::integer, _alias::text, _entry_tag::text;
128 end if;
129 else
130 return query select 1, NULL::integer , NULL::text, NULL::text;
131 end if;
132
133 return query select 1, NULL::integer, NULL::text, NULL::text;
134END;
135$$;
136
137
138ALTER FUNCTION auth.get_user_login_data(p_login text) OWNER TO kobo_owner;
139
140--
141-- Name: reset_session_data(); Type: FUNCTION; Schema: auth; Owner: kobo_owner
142--
143
144CREATE FUNCTION reset_session_data() RETURNS boolean
145 LANGUAGE plpgsql COST 10
146 AS $$--
147DECLARE
148BEGIN
149 SELECT ety.reset_expected_user_id();
150 SELECT ety.reset_current_user_id();
151
152 return true;
153END;
154$$;
155
156
157ALTER FUNCTION auth.reset_session_data() OWNER TO kobo_owner;
158
159--
160-- Name: tf_user_substitution(); Type: FUNCTION; Schema: auth; Owner: kobo_owner
161--
162
163CREATE FUNCTION tf_user_substitution() RETURNS trigger
164 LANGUAGE plpgsql
165 AS $$--
166--
167DECLARE
168
169BEGIN
170 if TG_LEVEL = 'STATEMENT' then return NULL; end if;
171
172 if TG_WHEN = 'BEFORE' then
173 if TG_OP = 'DELETE' then return OLD; end if;
174
175-- if TG_OP = 'INSERT' then
176-- elsif TG_OP = 'UPDATE' then
177
178 if NOT ety.is_user_admin(NEW.origin_user_id) then
179 RAISE EXCEPTION 'Подменяемый пользователь (id=%) должен иметь права Администратора!', NEW.origin_user_id;
180 end if;
181
182 if ety.is_user_admin(NEW.substn_user_id) then
183 RAISE EXCEPTION 'Подменный пользователь (id=%) не может быть Администратором!', NEW.substn_user_id;
184 end if;
185
186 return NEW;
187
188 end if;
189
190 return NULL;
191END;$$;
192
193
194ALTER FUNCTION auth.tf_user_substitution() OWNER TO kobo_owner;
195
196SET search_path = com, pg_catalog;
197
198--
199-- Name: gen_uuid(); Type: FUNCTION; Schema: com; Owner: kobo_owner
200--
201
202CREATE FUNCTION gen_uuid() RETURNS uuid
203 LANGUAGE plpgsql COST 1
204 AS $$
205DECLARE
206 s_uuid varchar = '';
207 i integer;
208 digit integer;
209
210BEGIN
211
212 return gen_random_uuid(); -- включается посредством команды: create extension pgcrypto
213
214EXCEPTION WHEN OTHERS THEN
215
216 for i in 0..31 loop
217
218 digit = trunc(random()*16);
219 if ( digit < 10 ) then
220 s_uuid := s_uuid || digit::varchar;
221 else
222 case digit
223 when 10 then s_uuid := s_uuid || 'a';
224 when 11 then s_uuid := s_uuid || 'b';
225 when 12 then s_uuid := s_uuid || 'c';
226 when 13 then s_uuid := s_uuid || 'd';
227 when 14 then s_uuid := s_uuid || 'e';
228 else s_uuid := s_uuid || 'f';
229 end case;
230 end if;
231
232 end loop;
233
234 return s_uuid::uuid;
235END;
236$$;
237
238
239ALTER FUNCTION com.gen_uuid() OWNER TO kobo_owner;
240
241SET search_path = ety, pg_catalog;
242
243--
244-- Name: _advisory_lock_exists(integer, integer); Type: FUNCTION; Schema: ety; Owner: kobo_owner
245--
246
247CREATE FUNCTION _advisory_lock_exists(p_class_id integer, p_obj_id integer) RETURNS boolean
248 LANGUAGE plpgsql IMMUTABLE COST 1
249 AS $$--
250BEGIN
251 return exists ( select l.*
252 from pg_locks l
253 join pg_database d on d.oid = l.database
254 where datname = current_database()
255 and l.locktype = 'advisory'
256 and l.classid = p_class_id
257 and l.objid = p_obj_id );
258END;
259$$;
260
261
262ALTER FUNCTION ety._advisory_lock_exists(p_class_id integer, p_obj_id integer) OWNER TO kobo_owner;
263
264--
265-- Name: _class_id_undo_act(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
266--
267
268CREATE FUNCTION _class_id_undo_act() RETURNS integer
269 LANGUAGE sql IMMUTABLE COST 1
270 AS $$select 19125;$$;
271
272
273ALTER FUNCTION ety._class_id_undo_act() OWNER TO kobo_owner;
274
275--
276-- Name: _class_id_undo_obj(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
277--
278
279CREATE FUNCTION _class_id_undo_obj() RETURNS integer
280 LANGUAGE sql IMMUTABLE COST 1
281 AS $$select 19125;$$;
282
283
284ALTER FUNCTION ety._class_id_undo_obj() OWNER TO kobo_owner;
285
286--
287-- Name: _get_admin_role_id(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
288--
289
290CREATE FUNCTION _get_admin_role_id() RETURNS integer
291 LANGUAGE plpgsql IMMUTABLE COST 1
292 AS $$--
293DECLARE
294 _admin_role_id integer;
295
296BEGIN
297 select id into _admin_role_id from ety.role where code = 'admin_role';
298 if FOUND then
299 return _admin_role_id;
300 end if;
301
302 return 0;
303END;
304$$;
305
306
307ALTER FUNCTION ety._get_admin_role_id() OWNER TO kobo_owner;
308
309--
310-- Name: cu_user_id(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
311--
312
313CREATE FUNCTION cu_user_id() RETURNS integer
314 LANGUAGE plpgsql IMMUTABLE COST 1
315 AS $$--
316--
317--
318DECLARE
319 _user_id integer;
320 _sub_user_id integer;
321
322BEGIN
323-- RAISE NOTICE 'CU: %', current_user;
324
325 _user_id := null;
326
327 if exists ( select * from pg_roles where rolname = current_user and rolsuper ) then
328 _user_id := ety.get_admin_user_id(); -- супер-пользователь PG является Админом БД
329 elsif pg_has_role(current_user,'kobo_owner','MEMBER') then
330 _user_id := ety.get_admin_user_id(); -- пользователь 'cbms_owner' является Админом БД
331 elsif NOT pg_has_role(current_user,'kobo_waitress','MEMBER') then
332 RAISE EXCEPTION 'Пользователь не имеет доступа к БД!';
333 end if;
334
335 if _user_id IS NULL then
336
337 -- попытка получить пользователя БД из переменных сессии PG
338 SELECT INTO _user_id current_setting('user_session_var.current_user_id')::integer;
339
340 if NOT FOUND OR _user_id IS NULL then
341 RAISE EXCEPTION 'Зарегистрированный пользователь в БД не имеет к ней доступа!';
342 end if;
343 end if;
344
345
346 if ety.is_user_admin(_user_id) then
347 select substn_user_id into _sub_user_id
348 from auth.user_substitution s
349 where origin_user_id = _user_id;
350
351 if FOUND then
352 _user_id := _sub_user_id;
353 end if;
354 end if;
355
356
357 return _user_id;
358
359EXCEPTION WHEN syntax_error_or_access_rule_violation THEN
360 RAISE EXCEPTION 'Пользователь не зарегистрирован в БД и не имеет к ней доступа!';
361END;
362$$;
363
364
365ALTER FUNCTION ety.cu_user_id() OWNER TO kobo_owner;
366
367--
368-- Name: get_admin_user_id(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
369--
370
371CREATE FUNCTION get_admin_user_id() RETURNS integer
372 LANGUAGE sql IMMUTABLE COST 1
373 AS $$select id from ety.user where code = 'admin_user'$$;
374
375
376ALTER FUNCTION ety.get_admin_user_id() OWNER TO kobo_owner;
377
378--
379-- Name: FUNCTION get_admin_user_id(); Type: COMMENT; Schema: ety; Owner: kobo_owner
380--
381
382COMMENT ON FUNCTION get_admin_user_id() IS '!!!!УСТАРЕЛО!!!!! УДАЛИТЬ !!!!!';
383
384
385--
386-- Name: get_message_target(integer); Type: FUNCTION; Schema: ety; Owner: kobo_owner
387--
388
389CREATE FUNCTION get_message_target(p_mess_id integer) RETURNS SETOF integer
390 LANGUAGE plpgsql IMMUTABLE COST 1
391 AS $$--
392-- returns set of user id's for go server to on who should get message
393-- based on ety.room _user table
394-- returns one if message target is user
395-- or multiple user id's if target is room
396
397DECLARE
398
399 _target_id integer;
400 _target_type integer;
401
402BEGIN
403 IF p_mess_id IS NULL THEN RAISE EXCEPTION 'No message was given!'; end if;
404
405 select o.ety_id, o.id into _target_type, _target_id
406 from ety.obj o
407 join ety.act a on o.id = a.target_id
408 where a.id = p_mess_id;
409
410 IF found then
411 if _target_type = pdic.get_ety_id_by_name('Room') THEN
412 RETURN QUERY SELECT user_id from ety.room_user where room_id = _target_id;
413
414 ELSIF _target_type = pdic.get_ety_id_by_name('User') THEN
415 RETURN QUERY SELECT _target_id;
416
417 END if;
418 END IF;
419
420 return;
421END;
422$$;
423
424
425ALTER FUNCTION ety.get_message_target(p_mess_id integer) OWNER TO kobo_owner;
426
427--
428-- Name: is_current_user_admin(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
429--
430
431CREATE FUNCTION is_current_user_admin() RETURNS boolean
432 LANGUAGE plpgsql IMMUTABLE COST 1
433 AS $$--
434DECLARE
435 _cur_user_id integer;
436 _admin_role_id integer;
437
438BEGIN
439 _admin_role_id := ety._get_admin_role_id();
440 if _admin_role_id = 0 then return false; end if;
441
442 _cur_user_id := ety.cu_user_id();
443
444 return exists(select * from ety.user_role where user_id = _cur_user_id and role_id = _admin_role_id);
445END;
446$$;
447
448
449ALTER FUNCTION ety.is_current_user_admin() OWNER TO kobo_owner;
450
451--
452-- Name: is_target_group(integer); Type: FUNCTION; Schema: ety; Owner: kobo_owner
453--
454
455CREATE FUNCTION is_target_group(p_target integer) RETURNS boolean
456 LANGUAGE plpgsql IMMUTABLE
457 AS $$--
458DECLARE
459 _target_ety_id integer;
460BEGIN
461 IF p_target IS NULL THEN RAISE EXCEPTION 'Missing target ( % )!', p_target ; END IF;
462
463 select ety_id into _target_ety_id from ety.obj where id = p_target;
464 IF NOT FOUND THEN RAISE EXCEPTION 'Target does not exist ( id: % )!', p_target ; END IF;
465
466 IF _target_ety_id = pdic.get_ety_id_by_name('Room') then RETURN true; end if;
467 return false;
468END;
469$$;
470
471
472ALTER FUNCTION ety.is_target_group(p_target integer) OWNER TO kobo_owner;
473
474--
475-- Name: is_user_admin(integer); Type: FUNCTION; Schema: ety; Owner: kobo_owner
476--
477
478CREATE FUNCTION is_user_admin(p_user_id integer) RETURNS boolean
479 LANGUAGE plpgsql IMMUTABLE COST 1
480 AS $$--
481DECLARE
482 _admin_role_id integer;
483
484BEGIN
485 _admin_role_id := ety._get_admin_role_id();
486 if _admin_role_id = 0 then return false; end if;
487
488 return exists(select * from ety.user_role where user_id = p_user_id and role_id = _admin_role_id);
489END;
490$$;
491
492
493ALTER FUNCTION ety.is_user_admin(p_user_id integer) OWNER TO kobo_owner;
494
495--
496-- Name: reset_current_user_id(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
497--
498
499CREATE FUNCTION reset_current_user_id() RETURNS boolean
500 LANGUAGE plpgsql
501 AS $$--
502DECLARE
503BEGIN
504 EXECUTE 'SET SESSION user_session_var.current_user_id = NULL';
505 return true;
506END;
507$$;
508
509
510ALTER FUNCTION ety.reset_current_user_id() OWNER TO kobo_owner;
511
512--
513-- Name: reset_expected_user_id(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
514--
515
516CREATE FUNCTION reset_expected_user_id() RETURNS boolean
517 LANGUAGE plpgsql
518 AS $$--
519DECLARE
520BEGIN
521 EXECUTE 'SET SESSION user_session_var.expected_user_id = NULL';
522 return true;
523END;
524$$;
525
526
527ALTER FUNCTION ety.reset_expected_user_id() OWNER TO kobo_owner;
528
529--
530-- Name: room_add_user(integer, integer); Type: FUNCTION; Schema: ety; Owner: kobo_owner
531--
532
533CREATE FUNCTION room_add_user(p_room integer, p_user integer) RETURNS boolean
534 LANGUAGE plpgsql COST 1
535 AS $$--
536DECLARE
537BEGIN
538 IF p_room IS NULL OR p_user IS NULL THEN RAISE EXCEPTION 'Unable to add user to room. No room ( % ) or user ( % ) specidied!', p_room, p_user ; END IF;
539
540 if not exists (select 1 from ety.room_user where room_id = p_room AND user_id = p_user) then
541 insert into ety.room_user (room_id, user_id) VALUES (p_room, p_user);
542 return true;
543 end if;
544 return false;
545
546END;
547$$;
548
549
550ALTER FUNCTION ety.room_add_user(p_room integer, p_user integer) OWNER TO kobo_owner;
551
552--
553-- Name: room_get_user_list(integer); Type: FUNCTION; Schema: ety; Owner: kobo_owner
554--
555
556CREATE FUNCTION room_get_user_list(p_room_id integer) RETURNS TABLE(user_id integer)
557 LANGUAGE plpgsql IMMUTABLE COST 10 ROWS 100
558 AS $$--
559DECLARE
560BEGIN
561 IF p_room_id IS NULL THEN RAISE EXCEPTION 'No room id given!'; END IF;
562 if not exists (select 1 from ety.room where room_id = p_room_id ) then RAISE EXCEPTION 'Object with id: % is not a room!', p_room_id; END IF;
563
564 return query select user_id from ety.room_user where room_id = p_room_id;
565
566END;
567$$;
568
569
570ALTER FUNCTION ety.room_get_user_list(p_room_id integer) OWNER TO kobo_owner;
571
572--
573-- Name: send_message(text, integer, integer); Type: FUNCTION; Schema: ety; Owner: kobo_owner
574--
575
576CREATE FUNCTION send_message(p_text text, p_target integer, p_context integer DEFAULT NULL::integer) RETURNS TABLE(mess_id integer, txt text, target integer, context integer, owner_id integer)
577 LANGUAGE plpgsql
578 AS $$--
579DECLARE
580 _mess_id integer;
581BEGIN
582 IF p_text IS NULL OR p_text = '' OR p_target IS NULL THEN RAISE EXCEPTION 'Message not valid. No text ( % ) or target ( % )!', p_text, p_target ; END IF;
583
584 insert into ety.message (text, target, context) VALUES (p_text, p_target, p_context) returning id into _mess_id;
585 return query select _mess_id, p_text, p_target, p_context, ety.cu_user_id();
586
587END;
588$$;
589
590
591ALTER FUNCTION ety.send_message(p_text text, p_target integer, p_context integer) OWNER TO kobo_owner;
592
593--
594-- Name: set_current_user_id(integer); Type: FUNCTION; Schema: ety; Owner: kobo_owner
595--
596
597CREATE FUNCTION set_current_user_id(p_user_id integer) RETURNS boolean
598 LANGUAGE plpgsql
599 AS $$--
600--
601--
602DECLARE
603 _user_not_logged_in bool;
604 _user_name text;
605
606BEGIN
607 select name into _user_name from ety.user where id = p_user_id;
608 if NOT FOUND then
609 RAISE EXCEPTION 'Не найден пользователь для id=''%''!', p_user_id;
610 end if;
611
612 -- Отключено из-за множественных логонов 1С под одним пользователем
613 --select into _user_not_logged_in pg_try_advisory_lock( ety._class_id_logged_in_user(), p_user_id );
614
615 --if FOUND and not _user_not_logged_in then
616 --RAISE EXCEPTION 'Пользователь ''%'' (id=%) уже работает в системе!', _user_name, p_user_id;
617 --end if;
618
619 EXECUTE 'SET SESSION user_session_var.current_user_id =' || p_user_id;
620
621 return true;
622END;
623$$;
624
625
626ALTER FUNCTION ety.set_current_user_id(p_user_id integer) OWNER TO kobo_owner;
627
628--
629-- Name: set_expected_user_id(integer); Type: FUNCTION; Schema: ety; Owner: kobo_owner
630--
631
632CREATE FUNCTION set_expected_user_id(p_user_id integer) RETURNS boolean
633 LANGUAGE plpgsql
634 AS $$--
635--
636--
637DECLARE
638 _user_not_logged_in bool;
639 _user_name text;
640
641BEGIN
642 select name into _user_name from ety.user where id = p_user_id;
643 if NOT FOUND then
644 RAISE EXCEPTION 'Не найден пользователь для id=''%''!', p_user_id;
645 end if;
646
647 -- Отключено из-за множественных логонов 1С под одним пользователем
648 --select into _user_not_logged_in pg_try_advisory_lock( ety._class_id_logged_in_user(), p_user_id );
649
650 --if FOUND and not _user_not_logged_in then
651 --RAISE EXCEPTION 'Пользователь ''%'' (id=%) уже работает в системе!', _user_name, p_user_id;
652 --end if;
653
654 EXECUTE 'SET SESSION user_session_var.expected_user_id =' || p_user_id;
655
656 return true;
657END;
658$$;
659
660
661ALTER FUNCTION ety.set_expected_user_id(p_user_id integer) OWNER TO kobo_owner;
662
663--
664-- Name: tf_action(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
665--
666
667CREATE FUNCTION tf_action() RETURNS trigger
668 LANGUAGE plpgsql
669 AS $$--
670--
671--
672DECLARE
673 _act_id integer;
674 _is_abstract boolean;
675
676 _cuser_id integer;
677
678BEGIN
679 if TG_LEVEL = 'STATEMENT' then return NULL; end if;
680
681 if TG_WHEN = 'BEFORE' then
682 if TG_OP = 'DELETE' then return OLD; end if;
683
684 if TG_OP = 'INSERT' then
685
686 if NOT NEW.id IS NULL and NEW.id <= 0 then NEW.id := nextval('ety.act_id_seq'::regclass); end if;
687
688 select act_id, is_abstract into _act_id, _is_abstract
689 from pdic.action where ety_tabname = TG_TABLE_NAME;
690 if NOT FOUND then
691 RAISE EXCEPTION 'Сущность таблицы: ''%'' не зарегистрирована!', TG_TABLE_NAME;
692 end if;
693
694 if _is_abstract then
695 RAISE EXCEPTION 'Прямая вставка записей в абстрактную таблицу: ''%'' НЕВОЗМОЖНА!', TG_TABLE_NAME;
696 end if;
697
698 NEW.act_id := _act_id;
699
700 _cuser_id := ety.cu_user_id();
701 NEW.ac_id := 1; --ety.get_user_create_ac_id(_cuser_id, _ety_id);
702
703 if NEW.create_date is NULL then NEW.create_date := clock_timestamp(); end if;
704 if NEW.uuid is NULL then NEW.uuid := com.gen_uuid(); end if;
705
706 NEW.create_user_id := _cuser_id;
707 if NEW.owner_id IS NULL then NEW.owner_id := _cuser_id; end if;
708
709 if NOT NEW.delete_user_id IS NULL AND NEW.delete_user_id < 0 then
710 NEW.delete_date := clock_timestamp();
711 NEW.delete_user_id := _cuser_id;
712 end if;
713
714 elsif TG_OP = 'UPDATE' then
715
716 if NOT NEW.delete_user_id IS NULL AND NEW.delete_user_id < 0 AND NOT OLD.delete_date IS NULL then
717 return NULL; -- нельзя пометить на удаление уже помеченную ну удаление запись
718 end if;
719
720 NEW.act_id := OLD.act_id;
721
722 _cuser_id := ety.cu_user_id();
723
724 if (NOT NEW.delete_user_id IS NULL AND NEW.delete_user_id < 0)
725 OR (NOT NEW.delete_date IS NULL AND OLD.delete_date IS NULL)
726 OR (NOT NEW.delete_user_id IS NULL AND OLD.delete_user_id IS NULL) then
727
728 NEW.delete_date := clock_timestamp();
729 NEW.delete_user_id := _cuser_id;
730 else
731
732 NEW.modify_date := clock_timestamp();
733 NEW.modify_user_id := _cuser_id;
734 end if;
735 end if;
736
737 return NEW;
738
739 end if;
740
741 --- ПОСЛЕ ----
742
743 if TG_WHEN = 'AFTER' then
744 if TG_OP = 'DELETE' then
745 -- удалить все ссылки объекта
746 --delete from ety.obj_bilink where obj_id = OLD.id;
747
748 -- удалить все Напоминания объекта
749 --delete from ety.reminder where obj_id = OLD.id;
750
751 return OLD;
752 end if;
753
754 if TG_OP = 'UPDATE' then
755 if NEW.id != OLD.id then
756 --update ety.obj_bilink set obj_id = NEW.id where obj_id = OLD.id;
757
758 --update ety.reminder set obj_id = NEW.id where obj_id = OLD.id;
759 end if;
760
761 -- Запись изменений в историю объекта
762 /*
763 if NEW.owner_id != OLD.owner_id then
764
765 if NOT ety._advisory_lock_exists(ety._class_id_undo_obj(), NEW.id) then
766 insert into ety.obj_hist(modify_user_id, obj_id, owner_id)
767 values(NEW.modify_user_id, NEW.id, OLD.owner_id);
768 end if;
769 end if;
770 */
771 return NEW;
772 end if;
773
774 end if;
775
776 return NULL;
777END;
778$$;
779
780
781ALTER FUNCTION ety.tf_action() OWNER TO kobo_owner;
782
783--
784-- Name: tf_entity(); Type: FUNCTION; Schema: ety; Owner: kobo_owner
785--
786
787CREATE FUNCTION tf_entity() RETURNS trigger
788 LANGUAGE plpgsql
789 AS $$--
790--
791--
792DECLARE
793 _ety_id integer;
794 _is_abstract boolean;
795
796 _cuser_id integer;
797
798BEGIN
799 if TG_LEVEL = 'STATEMENT' then return NULL; end if;
800
801 if TG_WHEN = 'BEFORE' then
802 if TG_OP = 'DELETE' then return OLD; end if;
803
804 if TG_OP = 'INSERT' then
805
806 if NOT NEW.id IS NULL and NEW.id <= 0 then NEW.id := nextval('ety.obj_id_seq'::regclass); end if;
807
808 select ety_id, is_abstract into _ety_id, _is_abstract
809 from pdic.ety where ety_tabname = TG_TABLE_NAME;
810 if NOT FOUND then
811 RAISE EXCEPTION 'Сущность таблицы: ''%'' не зарегистрирована!', TG_TABLE_NAME;
812 end if;
813
814 if _is_abstract then
815 RAISE EXCEPTION 'Прямая вставка записей в абстрактную таблицу: ''%'' НЕВОЗМОЖНА!', TG_TABLE_NAME;
816 end if;
817
818 NEW.ety_id := _ety_id;
819
820 _cuser_id := ety.cu_user_id();
821 NEW.ac_id := 1; --ety.get_user_create_ac_id(_cuser_id, _ety_id);
822
823 if NEW.create_date is NULL then NEW.create_date := clock_timestamp(); end if;
824 if NEW.uuid is NULL then NEW.uuid := com.gen_uuid(); end if;
825
826 NEW.create_user_id := _cuser_id;
827 if NEW.owner_id IS NULL then NEW.owner_id := _cuser_id; end if;
828
829 if NOT NEW.delete_user_id IS NULL AND NEW.delete_user_id < 0 then
830 NEW.delete_date := clock_timestamp();
831 NEW.delete_user_id := _cuser_id;
832 end if;
833
834 elsif TG_OP = 'UPDATE' then
835
836 if NOT NEW.delete_user_id IS NULL AND NEW.delete_user_id < 0 AND NOT OLD.delete_date IS NULL then
837 return NULL; -- нельзя пометить на удаление уже помеченную ну удаление запись
838 end if;
839
840 NEW.ety_id := OLD.ety_id;
841
842 _cuser_id := ety.cu_user_id();
843
844 if (NOT NEW.delete_user_id IS NULL AND NEW.delete_user_id < 0)
845 OR (NOT NEW.delete_date IS NULL AND OLD.delete_date IS NULL)
846 OR (NOT NEW.delete_user_id IS NULL AND OLD.delete_user_id IS NULL) then
847
848 NEW.delete_date := clock_timestamp();
849 NEW.delete_user_id := _cuser_id;
850 else
851
852 NEW.modify_date := clock_timestamp();
853 NEW.modify_user_id := _cuser_id;
854 end if;
855 end if;
856
857 return NEW;
858
859 end if;
860
861 --- ПОСЛЕ ----
862
863 if TG_WHEN = 'AFTER' then
864 if TG_OP = 'DELETE' then
865 -- удалить все ссылки объекта
866 --delete from ety.obj_bilink where obj_id = OLD.id;
867
868 -- удалить все Напоминания объекта
869 --delete from ety.reminder where obj_id = OLD.id;
870
871 return OLD;
872 end if;
873
874 if TG_OP = 'UPDATE' then
875 if NEW.id != OLD.id then
876 --update ety.obj_bilink set obj_id = NEW.id where obj_id = OLD.id;
877
878 --update ety.reminder set obj_id = NEW.id where obj_id = OLD.id;
879 end if;
880
881 -- Запись изменений в историю объекта
882
883 if NEW.owner_id != OLD.owner_id then
884
885 if NOT ety._advisory_lock_exists(ety._class_id_undo_obj(), NEW.id) then
886 insert into ety.obj_hist(modify_user_id, obj_id, owner_id)
887 values(NEW.modify_user_id, NEW.id, OLD.owner_id);
888 end if;
889 end if;
890
891 return NEW;
892 end if;
893
894 end if;
895
896 return NULL;
897END;
898$$;
899
900
901ALTER FUNCTION ety.tf_entity() OWNER TO kobo_owner;
902
903SET search_path = pdic, pg_catalog;
904
905--
906-- Name: get_action_id_by_name(text); Type: FUNCTION; Schema: pdic; Owner: kobo_owner
907--
908
909CREATE FUNCTION get_action_id_by_name(actname text) RETURNS integer
910 LANGUAGE plpgsql IMMUTABLE COST 1
911 AS $$--
912--
913--
914DECLARE
915 _id integer;
916
917BEGIN
918 select act_id into _id from pdic.action where act_name = actname;
919 if FOUND then
920 return _id;
921 end if;
922
923 return NULL;
924
925EXCEPTION WHEN syntax_error_or_access_rule_violation THEN
926 return NULL;
927END;
928$$;
929
930
931ALTER FUNCTION pdic.get_action_id_by_name(actname text) OWNER TO kobo_owner;
932
933--
934-- Name: get_ety_id_by_name(text); Type: FUNCTION; Schema: pdic; Owner: kobo_owner
935--
936
937CREATE FUNCTION get_ety_id_by_name(etyname text) RETURNS integer
938 LANGUAGE plpgsql IMMUTABLE COST 1
939 AS $$--
940--
941--
942DECLARE
943 _id integer;
944
945BEGIN
946 select ety_id into _id from pdic.ety where ety_name = etyname;
947 if FOUND then
948 return _id;
949 end if;
950
951 return NULL;
952
953EXCEPTION WHEN syntax_error_or_access_rule_violation THEN
954 return NULL;
955END;
956$$;
957
958
959ALTER FUNCTION pdic.get_ety_id_by_name(etyname text) OWNER TO kobo_owner;
960
961SET search_path = public, pg_catalog;
962
963--
964-- Name: __bck_get_version(); Type: FUNCTION; Schema: public; Owner: kobo_owner
965--
966
967CREATE FUNCTION __bck_get_version() RETURNS text
968 LANGUAGE sql IMMUTABLE COST 1
969 AS $$select '0.01.00'::text;$$;
970
971
972ALTER FUNCTION public.__bck_get_version() OWNER TO kobo_owner;
973
974SET search_path = auth, pg_catalog;
975
976SET default_tablespace = '';
977
978SET default_with_oids = false;
979
980--
981-- Name: logon; Type: TABLE; Schema: auth; Owner: kobo_owner
982--
983
984CREATE TABLE logon (
985 logon_id uuid DEFAULT com.gen_uuid() NOT NULL,
986 logon_date timestamp with time zone DEFAULT clock_timestamp() NOT NULL,
987 user_id integer NOT NULL,
988 client_ip cidr NOT NULL,
989 user_agent text,
990 sess_id text,
991 php_server_ip cidr,
992 mta_master text,
993 last_activity timestamp with time zone,
994 user_activity timestamp with time zone,
995 id integer NOT NULL
996);
997
998
999ALTER TABLE logon OWNER TO kobo_owner;
1000
1001--
1002-- Name: COLUMN logon.sess_id; Type: COMMENT; Schema: auth; Owner: kobo_owner
1003--
1004
1005COMMENT ON COLUMN logon.sess_id IS 'ID сессии Web-сервера (PHPSESSID);
1006файлы сессий в ''/var/lib/php5/sessions''.';
1007
1008
1009--
1010-- Name: COLUMN logon.php_server_ip; Type: COMMENT; Schema: auth; Owner: kobo_owner
1011--
1012
1013COMMENT ON COLUMN logon.php_server_ip IS 'Адрес сервера PHP с которого было произведено подключение к БД';
1014
1015
1016--
1017-- Name: logon_id_seq; Type: SEQUENCE; Schema: auth; Owner: kobo_owner
1018--
1019
1020CREATE SEQUENCE logon_id_seq
1021 START WITH 1
1022 INCREMENT BY 1
1023 NO MINVALUE
1024 NO MAXVALUE
1025 CACHE 1;
1026
1027
1028ALTER TABLE logon_id_seq OWNER TO kobo_owner;
1029
1030--
1031-- Name: logon_id_seq; Type: SEQUENCE OWNED BY; Schema: auth; Owner: kobo_owner
1032--
1033
1034ALTER SEQUENCE logon_id_seq OWNED BY logon.id;
1035
1036
1037--
1038-- Name: user_substitution; Type: TABLE; Schema: auth; Owner: kobo_owner
1039--
1040
1041CREATE TABLE user_substitution (
1042 origin_user_id integer NOT NULL,
1043 substn_user_id integer NOT NULL
1044);
1045
1046
1047ALTER TABLE user_substitution OWNER TO kobo_owner;
1048
1049SET search_path = ety, pg_catalog;
1050
1051--
1052-- Name: act; Type: TABLE; Schema: ety; Owner: kobo_owner
1053--
1054
1055CREATE TABLE act (
1056 id integer NOT NULL,
1057 ac_id integer,
1058 uuid uuid DEFAULT com.gen_uuid() NOT NULL,
1059 text text,
1060 act_id integer NOT NULL,
1061 create_date timestamp with time zone DEFAULT clock_timestamp() NOT NULL,
1062 modify_date timestamp with time zone,
1063 delete_date timestamp with time zone,
1064 create_user_id integer,
1065 modify_user_id integer,
1066 delete_user_id integer,
1067 owner_id integer,
1068 code text,
1069 target_id integer NOT NULL,
1070 target_context integer,
1071 target_modifier integer,
1072 comment text
1073);
1074
1075
1076ALTER TABLE act OWNER TO kobo_owner;
1077
1078--
1079-- Name: act_id_seq; Type: SEQUENCE; Schema: ety; Owner: kobo_owner
1080--
1081
1082CREATE SEQUENCE act_id_seq
1083 START WITH 1
1084 INCREMENT BY 1
1085 NO MINVALUE
1086 NO MAXVALUE
1087 CACHE 1;
1088
1089
1090ALTER TABLE act_id_seq OWNER TO kobo_owner;
1091
1092--
1093-- Name: act_id_seq; Type: SEQUENCE OWNED BY; Schema: ety; Owner: kobo_owner
1094--
1095
1096ALTER SEQUENCE act_id_seq OWNED BY act.id;
1097
1098
1099--
1100-- Name: message; Type: TABLE; Schema: ety; Owner: kobo_owner
1101--
1102
1103CREATE TABLE message (
1104)
1105INHERITS (act);
1106
1107
1108ALTER TABLE message OWNER TO kobo_owner;
1109
1110--
1111-- Name: obj; Type: TABLE; Schema: ety; Owner: kobo_owner
1112--
1113
1114CREATE TABLE obj (
1115 id integer NOT NULL,
1116 ac_id integer,
1117 uuid uuid DEFAULT com.gen_uuid() NOT NULL,
1118 name text NOT NULL,
1119 fullname text,
1120 ety_id integer NOT NULL,
1121 create_date timestamp with time zone DEFAULT clock_timestamp() NOT NULL,
1122 modify_date timestamp with time zone,
1123 delete_date timestamp with time zone,
1124 create_user_id integer,
1125 modify_user_id integer,
1126 delete_user_id integer,
1127 owner_id integer,
1128 code text,
1129 comment text,
1130 imported text
1131);
1132
1133
1134ALTER TABLE obj OWNER TO kobo_owner;
1135
1136--
1137-- Name: obj_hist; Type: TABLE; Schema: ety; Owner: kobo_owner
1138--
1139
1140CREATE TABLE obj_hist (
1141 obj_hist_id integer NOT NULL,
1142 modify_date timestamp with time zone DEFAULT clock_timestamp() NOT NULL,
1143 modify_user_id integer NOT NULL,
1144 obj_id integer NOT NULL,
1145 owner_id integer
1146);
1147
1148
1149ALTER TABLE obj_hist OWNER TO kobo_owner;
1150
1151--
1152-- Name: TABLE obj_hist; Type: COMMENT; Schema: ety; Owner: kobo_owner
1153--
1154
1155COMMENT ON TABLE obj_hist IS 'Истроия изменения объекта (для UNDO)';
1156
1157
1158--
1159-- Name: COLUMN obj_hist.modify_date; Type: COMMENT; Schema: ety; Owner: kobo_owner
1160--
1161
1162COMMENT ON COLUMN obj_hist.modify_date IS 'Дата изменения объекта';
1163
1164
1165--
1166-- Name: COLUMN obj_hist.modify_user_id; Type: COMMENT; Schema: ety; Owner: kobo_owner
1167--
1168
1169COMMENT ON COLUMN obj_hist.modify_user_id IS 'Пользователь, производивший изменения';
1170
1171
1172--
1173-- Name: COLUMN obj_hist.obj_id; Type: COMMENT; Schema: ety; Owner: kobo_owner
1174--
1175
1176COMMENT ON COLUMN obj_hist.obj_id IS 'Объект, подвергшийся изменениям';
1177
1178
1179--
1180-- Name: COLUMN obj_hist.owner_id; Type: COMMENT; Schema: ety; Owner: kobo_owner
1181--
1182
1183COMMENT ON COLUMN obj_hist.owner_id IS 'Поле объекта (owner_id), которое было изменено';
1184
1185
1186--
1187-- Name: obj_hist_obj_hist_id_seq; Type: SEQUENCE; Schema: ety; Owner: kobo_owner
1188--
1189
1190CREATE SEQUENCE obj_hist_obj_hist_id_seq
1191 START WITH 1
1192 INCREMENT BY 1
1193 NO MINVALUE
1194 NO MAXVALUE
1195 CACHE 1;
1196
1197
1198ALTER TABLE obj_hist_obj_hist_id_seq OWNER TO kobo_owner;
1199
1200--
1201-- Name: obj_hist_obj_hist_id_seq; Type: SEQUENCE OWNED BY; Schema: ety; Owner: kobo_owner
1202--
1203
1204ALTER SEQUENCE obj_hist_obj_hist_id_seq OWNED BY obj_hist.obj_hist_id;
1205
1206
1207--
1208-- Name: obj_id_seq; Type: SEQUENCE; Schema: ety; Owner: kobo_owner
1209--
1210
1211CREATE SEQUENCE obj_id_seq
1212 START WITH 1
1213 INCREMENT BY 1
1214 NO MINVALUE
1215 NO MAXVALUE
1216 CACHE 1;
1217
1218
1219ALTER TABLE obj_id_seq OWNER TO kobo_owner;
1220
1221--
1222-- Name: obj_id_seq; Type: SEQUENCE OWNED BY; Schema: ety; Owner: kobo_owner
1223--
1224
1225ALTER SEQUENCE obj_id_seq OWNED BY obj.id;
1226
1227
1228--
1229-- Name: role; Type: TABLE; Schema: ety; Owner: kobo_owner
1230--
1231
1232CREATE TABLE role (
1233 is_auto smallint DEFAULT 0 NOT NULL,
1234 create_ac_id integer,
1235 ui_id integer
1236)
1237INHERITS (obj);
1238
1239
1240ALTER TABLE role OWNER TO kobo_owner;
1241
1242--
1243-- Name: room; Type: TABLE; Schema: ety; Owner: kobo_owner
1244--
1245
1246CREATE TABLE room (
1247)
1248INHERITS (obj);
1249
1250
1251ALTER TABLE room OWNER TO kobo_owner;
1252
1253--
1254-- Name: room_user; Type: TABLE; Schema: ety; Owner: postgres
1255--
1256
1257CREATE TABLE room_user (
1258 user_id integer NOT NULL,
1259 room_id integer NOT NULL
1260);
1261
1262
1263ALTER TABLE room_user OWNER TO postgres;
1264
1265--
1266-- Name: user; Type: TABLE; Schema: ety; Owner: kobo_owner
1267--
1268
1269CREATE TABLE "user" (
1270 permit boolean DEFAULT false NOT NULL,
1271 entry_tag text,
1272 permit_ip cidr[]
1273)
1274INHERITS (obj);
1275
1276
1277ALTER TABLE "user" OWNER TO kobo_owner;
1278
1279--
1280-- Name: user_role; Type: TABLE; Schema: ety; Owner: kobo_owner
1281--
1282
1283CREATE TABLE user_role (
1284 user_id integer NOT NULL,
1285 role_id integer NOT NULL,
1286 _order integer DEFAULT 0 NOT NULL,
1287 comment text
1288);
1289
1290
1291ALTER TABLE user_role OWNER TO kobo_owner;
1292
1293--
1294-- Name: COLUMN user_role._order; Type: COMMENT; Schema: ety; Owner: kobo_owner
1295--
1296
1297COMMENT ON COLUMN user_role._order IS 'Порядок ролей пользователя
1298
1299Роль с наименьшим порядковым номером считается Основной ролью пользователя.
1300Всем новым объектам назначатся класс доступа Основной роли пользователя';
1301
1302
1303SET search_path = pdic, pg_catalog;
1304
1305--
1306-- Name: action; Type: TABLE; Schema: pdic; Owner: kobo_owner
1307--
1308
1309CREATE TABLE action (
1310 act_id integer NOT NULL,
1311 super_act_id integer,
1312 ety_name text NOT NULL,
1313 ety_tabname name NOT NULL,
1314 ety_rusname text NOT NULL,
1315 ety_pfx character varying(4),
1316 is_abstract boolean DEFAULT false NOT NULL,
1317 comment text,
1318 grant_perm boolean DEFAULT false NOT NULL
1319);
1320
1321
1322ALTER TABLE action OWNER TO kobo_owner;
1323
1324--
1325-- Name: ety; Type: TABLE; Schema: pdic; Owner: kobo_owner
1326--
1327
1328CREATE TABLE ety (
1329 ety_id integer NOT NULL,
1330 super_ety_id integer,
1331 ety_name text NOT NULL,
1332 ety_tabname name NOT NULL,
1333 ety_rusname text NOT NULL,
1334 ety_pfx character varying(4),
1335 is_abstract boolean DEFAULT false NOT NULL,
1336 comment text,
1337 grant_perm boolean DEFAULT false NOT NULL
1338);
1339
1340
1341ALTER TABLE ety OWNER TO kobo_owner;
1342
1343SET search_path = auth, pg_catalog;
1344
1345--
1346-- Name: id; Type: DEFAULT; Schema: auth; Owner: kobo_owner
1347--
1348
1349ALTER TABLE ONLY logon ALTER COLUMN id SET DEFAULT nextval('logon_id_seq'::regclass);
1350
1351
1352SET search_path = ety, pg_catalog;
1353
1354--
1355-- Name: id; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1356--
1357
1358ALTER TABLE ONLY act ALTER COLUMN id SET DEFAULT nextval('act_id_seq'::regclass);
1359
1360
1361--
1362-- Name: id; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1363--
1364
1365ALTER TABLE ONLY message ALTER COLUMN id SET DEFAULT nextval('act_id_seq'::regclass);
1366
1367
1368--
1369-- Name: uuid; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1370--
1371
1372ALTER TABLE ONLY message ALTER COLUMN uuid SET DEFAULT com.gen_uuid();
1373
1374
1375--
1376-- Name: create_date; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1377--
1378
1379ALTER TABLE ONLY message ALTER COLUMN create_date SET DEFAULT clock_timestamp();
1380
1381
1382--
1383-- Name: id; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1384--
1385
1386ALTER TABLE ONLY obj ALTER COLUMN id SET DEFAULT nextval('obj_id_seq'::regclass);
1387
1388
1389--
1390-- Name: obj_hist_id; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1391--
1392
1393ALTER TABLE ONLY obj_hist ALTER COLUMN obj_hist_id SET DEFAULT nextval('obj_hist_obj_hist_id_seq'::regclass);
1394
1395
1396--
1397-- Name: id; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1398--
1399
1400ALTER TABLE ONLY role ALTER COLUMN id SET DEFAULT nextval('obj_id_seq'::regclass);
1401
1402
1403--
1404-- Name: uuid; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1405--
1406
1407ALTER TABLE ONLY role ALTER COLUMN uuid SET DEFAULT com.gen_uuid();
1408
1409
1410--
1411-- Name: create_date; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1412--
1413
1414ALTER TABLE ONLY role ALTER COLUMN create_date SET DEFAULT clock_timestamp();
1415
1416
1417--
1418-- Name: id; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1419--
1420
1421ALTER TABLE ONLY room ALTER COLUMN id SET DEFAULT nextval('obj_id_seq'::regclass);
1422
1423
1424--
1425-- Name: uuid; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1426--
1427
1428ALTER TABLE ONLY room ALTER COLUMN uuid SET DEFAULT com.gen_uuid();
1429
1430
1431--
1432-- Name: create_date; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1433--
1434
1435ALTER TABLE ONLY room ALTER COLUMN create_date SET DEFAULT clock_timestamp();
1436
1437
1438--
1439-- Name: id; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1440--
1441
1442ALTER TABLE ONLY "user" ALTER COLUMN id SET DEFAULT nextval('obj_id_seq'::regclass);
1443
1444
1445--
1446-- Name: uuid; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1447--
1448
1449ALTER TABLE ONLY "user" ALTER COLUMN uuid SET DEFAULT com.gen_uuid();
1450
1451
1452--
1453-- Name: create_date; Type: DEFAULT; Schema: ety; Owner: kobo_owner
1454--
1455
1456ALTER TABLE ONLY "user" ALTER COLUMN create_date SET DEFAULT clock_timestamp();
1457
1458
1459SET search_path = auth, pg_catalog;
1460
1461--
1462-- Data for Name: logon; Type: TABLE DATA; Schema: auth; Owner: kobo_owner
1463--
1464
1465
1466
1467--
1468-- Name: logon_id_seq; Type: SEQUENCE SET; Schema: auth; Owner: kobo_owner
1469--
1470
1471SELECT pg_catalog.setval('logon_id_seq', 1, false);
1472
1473
1474--
1475-- Data for Name: user_substitution; Type: TABLE DATA; Schema: auth; Owner: kobo_owner
1476--
1477
1478
1479
1480SET search_path = ety, pg_catalog;
1481
1482--
1483-- Data for Name: act; Type: TABLE DATA; Schema: ety; Owner: kobo_owner
1484--
1485
1486
1487
1488--
1489-- Name: act_id_seq; Type: SEQUENCE SET; Schema: ety; Owner: kobo_owner
1490--
1491
1492SELECT pg_catalog.setval('act_id_seq', 2, true);
1493
1494
1495--
1496-- Data for Name: message; Type: TABLE DATA; Schema: ety; Owner: kobo_owner
1497--
1498
1499INSERT INTO message (id, ac_id, uuid, text, act_id, create_date, modify_date, delete_date, create_user_id, modify_user_id, delete_user_id, owner_id, code, target_id, target_context, target_modifier, comment) VALUES (1, 1, '8d030e18-905e-0c86-b3dc-87f1312793c1', '3123213adasd', 2, '2019-07-19 15:46:53.008813+03', '2019-07-19 15:47:02.440193+03', NULL, 5, 4, NULL, 5, NULL, 10, NULL, NULL, NULL);
1500INSERT INTO message (id, ac_id, uuid, text, act_id, create_date, modify_date, delete_date, create_user_id, modify_user_id, delete_user_id, owner_id, code, target_id, target_context, target_modifier, comment) VALUES (2, 1, '8a16bcb5-8089-6625-1274-7a58881de837', 'test', 2, '2019-07-19 16:28:40.995361+03', '2019-07-19 16:28:52.512358+03', NULL, 8, 4, NULL, 8, NULL, 5, NULL, NULL, NULL);
1501
1502
1503--
1504-- Data for Name: obj; Type: TABLE DATA; Schema: ety; Owner: kobo_owner
1505--
1506
1507
1508
1509--
1510-- Data for Name: obj_hist; Type: TABLE DATA; Schema: ety; Owner: kobo_owner
1511--
1512
1513INSERT INTO obj_hist (obj_hist_id, modify_date, modify_user_id, obj_id, owner_id) VALUES (1, '2019-07-05 18:08:14.425948+03', 4, 6, 4);
1514INSERT INTO obj_hist (obj_hist_id, modify_date, modify_user_id, obj_id, owner_id) VALUES (2, '2019-07-05 18:08:15.580877+03', 4, 7, 4);
1515INSERT INTO obj_hist (obj_hist_id, modify_date, modify_user_id, obj_id, owner_id) VALUES (3, '2019-07-05 18:08:49.059189+03', 4, 6, 5);
1516INSERT INTO obj_hist (obj_hist_id, modify_date, modify_user_id, obj_id, owner_id) VALUES (4, '2019-07-05 18:08:50.07262+03', 4, 7, 5);
1517
1518
1519--
1520-- Name: obj_hist_obj_hist_id_seq; Type: SEQUENCE SET; Schema: ety; Owner: kobo_owner
1521--
1522
1523SELECT pg_catalog.setval('obj_hist_obj_hist_id_seq', 4, true);
1524
1525
1526--
1527-- Name: obj_id_seq; Type: SEQUENCE SET; Schema: ety; Owner: kobo_owner
1528--
1529
1530SELECT pg_catalog.setval('obj_id_seq', 10, true);
1531
1532
1533--
1534-- Data for Name: role; Type: TABLE DATA; Schema: ety; Owner: kobo_owner
1535--
1536
1537INSERT INTO role (id, ac_id, uuid, name, fullname, ety_id, create_date, modify_date, delete_date, create_user_id, modify_user_id, delete_user_id, owner_id, code, comment, imported, is_auto, create_ac_id, ui_id) VALUES (6, 1, '3f38813a-b6cb-58c8-f2b9-92c968ad02f4', 'Administrator', 'Administrator', 6, '2019-06-21 17:42:41.412924+03', '2019-07-05 18:08:49.05865+03', NULL, 4, 4, NULL, 4, 'admin_role', NULL, NULL, 0, NULL, NULL);
1538INSERT INTO role (id, ac_id, uuid, name, fullname, ety_id, create_date, modify_date, delete_date, create_user_id, modify_user_id, delete_user_id, owner_id, code, comment, imported, is_auto, create_ac_id, ui_id) VALUES (7, 1, '13ca4040-71bc-a749-af33-1fd88758a5db', 'User', 'User', 6, '2019-06-21 17:46:03.828776+03', '2019-07-05 18:08:50.072078+03', NULL, 4, 4, NULL, 4, 'user_role', NULL, NULL, 0, NULL, NULL);
1539
1540
1541--
1542-- Data for Name: room; Type: TABLE DATA; Schema: ety; Owner: kobo_owner
1543--
1544
1545INSERT INTO room (id, ac_id, uuid, name, fullname, ety_id, create_date, modify_date, delete_date, create_user_id, modify_user_id, delete_user_id, owner_id, code, comment, imported) VALUES (10, 1, '0cc6a051-04b2-d507-fc68-f6e74444d18d', 'Test Room', 'Test Room', 5, '2019-07-14 17:44:44.142483+03', NULL, NULL, 4, NULL, NULL, 4, NULL, NULL, NULL);
1546
1547
1548--
1549-- Data for Name: room_user; Type: TABLE DATA; Schema: ety; Owner: postgres
1550--
1551
1552INSERT INTO room_user (user_id, room_id) VALUES (5, 10);
1553INSERT INTO room_user (user_id, room_id) VALUES (8, 10);
1554INSERT INTO room_user (user_id, room_id) VALUES (9, 10);
1555
1556
1557--
1558-- Data for Name: user; Type: TABLE DATA; Schema: ety; Owner: kobo_owner
1559--
1560
1561INSERT INTO "user" (id, ac_id, uuid, name, fullname, ety_id, create_date, modify_date, delete_date, create_user_id, modify_user_id, delete_user_id, owner_id, code, comment, imported, permit, entry_tag, permit_ip) VALUES (4, 1, '38d00d4b-21e0-edae-daee-422e971b01e3', 'Administrator', 'Administrator', 1, '2019-06-21 15:25:27.275484+03', '2019-06-22 15:41:48.493499+03', NULL, NULL, 4, NULL, NULL, 'admin_user', NULL, NULL, false, NULL, NULL);
1562INSERT INTO "user" (id, ac_id, uuid, name, fullname, ety_id, create_date, modify_date, delete_date, create_user_id, modify_user_id, delete_user_id, owner_id, code, comment, imported, permit, entry_tag, permit_ip) VALUES (8, 1, 'c4182c6d-f0dc-250b-4fca-350421d95fb1', 'TestUser1', 'TestUser1', 1, '2019-06-22 15:41:48.477119+03', '2019-07-04 16:03:10.104689+03', NULL, 4, 4, NULL, 4, NULL, '123', NULL, true, '$2a$12$.LzZxXvjBSBS4so5LAOwT.ob9u9Pz2iqKzFSXBSpHhBRPJZFG4uHS
1563', NULL);
1564INSERT INTO "user" (id, ac_id, uuid, name, fullname, ety_id, create_date, modify_date, delete_date, create_user_id, modify_user_id, delete_user_id, owner_id, code, comment, imported, permit, entry_tag, permit_ip) VALUES (5, 1, '80f16a2d-cba5-1db4-4571-9aec1fa8f717', 'Liza', 'Liza', 1, '2019-06-21 17:40:08.389201+03', '2019-07-04 16:03:16.310166+03', NULL, 4, 4, NULL, 4, NULL, 'TestPassLiza', NULL, true, '$2a$12$um.yhjy4vMys1.Q5ALDcZ.z7FtiJU4juKyWzTCtgLBFkQxPsXvtQy
1565', NULL);
1566INSERT INTO "user" (id, ac_id, uuid, name, fullname, ety_id, create_date, modify_date, delete_date, create_user_id, modify_user_id, delete_user_id, owner_id, code, comment, imported, permit, entry_tag, permit_ip) VALUES (9, 1, '4c968f48-0142-75eb-4b57-06c37acc98ee', 'TestUser2', 'TestUser2', 1, '2019-06-22 15:52:36.182813+03', '2019-07-04 16:03:49.892998+03', NULL, 4, 4, NULL, 4, NULL, '321', NULL, false, '$2a$12$MmdaXqXUCqj89KH5E6M02.aqiKcmzjJSGudmguCqyLczYI/PB6Bue', NULL);
1567
1568
1569--
1570-- Data for Name: user_role; Type: TABLE DATA; Schema: ety; Owner: kobo_owner
1571--
1572
1573INSERT INTO user_role (user_id, role_id, _order, comment) VALUES (4, 6, 0, NULL);
1574
1575
1576SET search_path = pdic, pg_catalog;
1577
1578--
1579-- Data for Name: action; Type: TABLE DATA; Schema: pdic; Owner: kobo_owner
1580--
1581
1582INSERT INTO action (act_id, super_act_id, ety_name, ety_tabname, ety_rusname, ety_pfx, is_abstract, comment, grant_perm) VALUES (3, 1, 'Roll', 'roll', 'РоллКубов', NULL, false, 'РоллКубов', false);
1583INSERT INTO action (act_id, super_act_id, ety_name, ety_tabname, ety_rusname, ety_pfx, is_abstract, comment, grant_perm) VALUES (1, NULL, 'Action', 'act', 'Действие', NULL, false, 'Абстрактное действие', false);
1584INSERT INTO action (act_id, super_act_id, ety_name, ety_tabname, ety_rusname, ety_pfx, is_abstract, comment, grant_perm) VALUES (2, 1, 'Message', 'message', 'Сообщение', NULL, false, 'Сообщение', false);
1585
1586
1587--
1588-- Data for Name: ety; Type: TABLE DATA; Schema: pdic; Owner: kobo_owner
1589--
1590
1591INSERT INTO ety (ety_id, super_ety_id, ety_name, ety_tabname, ety_rusname, ety_pfx, is_abstract, comment, grant_perm) VALUES (1, NULL, 'User', 'user', 'Пользователь', NULL, false, 'Пользователь', false);
1592INSERT INTO ety (ety_id, super_ety_id, ety_name, ety_tabname, ety_rusname, ety_pfx, is_abstract, comment, grant_perm) VALUES (5, NULL, 'Room', 'room', 'Комната', NULL, false, 'Комната', false);
1593INSERT INTO ety (ety_id, super_ety_id, ety_name, ety_tabname, ety_rusname, ety_pfx, is_abstract, comment, grant_perm) VALUES (6, NULL, 'Role', 'role', 'Роль', NULL, false, 'Роль', false);
1594
1595
1596SET search_path = auth, pg_catalog;
1597
1598--
1599-- Name: pk_logon; Type: CONSTRAINT; Schema: auth; Owner: kobo_owner
1600--
1601
1602ALTER TABLE ONLY logon
1603 ADD CONSTRAINT pk_logon PRIMARY KEY (id);
1604
1605
1606--
1607-- Name: pk_user_substitution; Type: CONSTRAINT; Schema: auth; Owner: kobo_owner
1608--
1609
1610ALTER TABLE ONLY user_substitution
1611 ADD CONSTRAINT pk_user_substitution PRIMARY KEY (origin_user_id);
1612
1613
1614SET search_path = ety, pg_catalog;
1615
1616--
1617-- Name: act_pk; Type: CONSTRAINT; Schema: ety; Owner: kobo_owner
1618--
1619
1620ALTER TABLE ONLY act
1621 ADD CONSTRAINT act_pk PRIMARY KEY (id);
1622
1623
1624--
1625-- Name: obj_pk; Type: CONSTRAINT; Schema: ety; Owner: kobo_owner
1626--
1627
1628ALTER TABLE ONLY obj
1629 ADD CONSTRAINT obj_pk PRIMARY KEY (id);
1630
1631
1632--
1633-- Name: pk_message; Type: CONSTRAINT; Schema: ety; Owner: kobo_owner
1634--
1635
1636ALTER TABLE ONLY message
1637 ADD CONSTRAINT pk_message PRIMARY KEY (id);
1638
1639
1640--
1641-- Name: pk_obj_hist; Type: CONSTRAINT; Schema: ety; Owner: kobo_owner
1642--
1643
1644ALTER TABLE ONLY obj_hist
1645 ADD CONSTRAINT pk_obj_hist PRIMARY KEY (obj_hist_id);
1646
1647
1648--
1649-- Name: pk_role; Type: CONSTRAINT; Schema: ety; Owner: kobo_owner
1650--
1651
1652ALTER TABLE ONLY role
1653 ADD CONSTRAINT pk_role PRIMARY KEY (id);
1654
1655
1656--
1657-- Name: pk_room; Type: CONSTRAINT; Schema: ety; Owner: kobo_owner
1658--
1659
1660ALTER TABLE ONLY room
1661 ADD CONSTRAINT pk_room PRIMARY KEY (id);
1662
1663
1664--
1665-- Name: pk_user; Type: CONSTRAINT; Schema: ety; Owner: kobo_owner
1666--
1667
1668ALTER TABLE ONLY "user"
1669 ADD CONSTRAINT pk_user PRIMARY KEY (id);
1670
1671
1672--
1673-- Name: pk_user_role; Type: CONSTRAINT; Schema: ety; Owner: kobo_owner
1674--
1675
1676ALTER TABLE ONLY user_role
1677 ADD CONSTRAINT pk_user_role PRIMARY KEY (user_id, role_id);
1678
1679
1680SET search_path = pdic, pg_catalog;
1681
1682--
1683-- Name: pk_act; Type: CONSTRAINT; Schema: pdic; Owner: kobo_owner
1684--
1685
1686ALTER TABLE ONLY action
1687 ADD CONSTRAINT pk_act PRIMARY KEY (act_id);
1688
1689
1690--
1691-- Name: pk_ent; Type: CONSTRAINT; Schema: pdic; Owner: kobo_owner
1692--
1693
1694ALTER TABLE ONLY ety
1695 ADD CONSTRAINT pk_ent PRIMARY KEY (ety_id);
1696
1697
1698SET search_path = auth, pg_catalog;
1699
1700--
1701-- Name: fki__user__user_substn__origin_user; Type: INDEX; Schema: auth; Owner: kobo_owner
1702--
1703
1704CREATE INDEX fki__user__user_substn__origin_user ON user_substitution USING btree (origin_user_id);
1705
1706
1707--
1708-- Name: fki__user__user_substn__substn_user; Type: INDEX; Schema: auth; Owner: kobo_owner
1709--
1710
1711CREATE INDEX fki__user__user_substn__substn_user ON user_substitution USING btree (substn_user_id);
1712
1713
1714--
1715-- Name: ix_logon__logon_date; Type: INDEX; Schema: auth; Owner: kobo_owner
1716--
1717
1718CREATE INDEX ix_logon__logon_date ON logon USING btree (logon_date);
1719
1720
1721--
1722-- Name: ux__user_substitution__origin__substn; Type: INDEX; Schema: auth; Owner: kobo_owner
1723--
1724
1725CREATE INDEX ux__user_substitution__origin__substn ON user_substitution USING btree (origin_user_id, substn_user_id);
1726
1727
1728--
1729-- Name: ux_logon__logon_id; Type: INDEX; Schema: auth; Owner: kobo_owner
1730--
1731
1732CREATE UNIQUE INDEX ux_logon__logon_id ON logon USING btree (logon_id);
1733
1734
1735SET search_path = ety, pg_catalog;
1736
1737--
1738-- Name: fki__user__obj_hist__owner; Type: INDEX; Schema: ety; Owner: kobo_owner
1739--
1740
1741CREATE INDEX fki__user__obj_hist__owner ON obj_hist USING btree (owner_id);
1742
1743
1744--
1745-- Name: fki_user__obj_hist__modify_user; Type: INDEX; Schema: ety; Owner: kobo_owner
1746--
1747
1748CREATE INDEX fki_user__obj_hist__modify_user ON obj_hist USING btree (modify_user_id);
1749
1750
1751--
1752-- Name: fki_user_role__role; Type: INDEX; Schema: ety; Owner: kobo_owner
1753--
1754
1755CREATE INDEX fki_user_role__role ON user_role USING btree (role_id);
1756
1757
1758--
1759-- Name: fki_user_role__user; Type: INDEX; Schema: ety; Owner: kobo_owner
1760--
1761
1762CREATE INDEX fki_user_role__user ON user_role USING btree (user_id);
1763
1764
1765--
1766-- Name: ux__room_user__room__user; Type: INDEX; Schema: ety; Owner: postgres
1767--
1768
1769CREATE UNIQUE INDEX ux__room_user__room__user ON room_user USING btree (room_id, user_id);
1770
1771
1772SET search_path = auth, pg_catalog;
1773
1774--
1775-- Name: biut_user_substitution; Type: TRIGGER; Schema: auth; Owner: kobo_owner
1776--
1777
1778CREATE TRIGGER biut_user_substitution BEFORE INSERT OR UPDATE ON user_substitution FOR EACH ROW EXECUTE PROCEDURE tf_user_substitution();
1779
1780
1781SET search_path = ety, pg_catalog;
1782
1783--
1784-- Name: aud__message__action; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1785--
1786
1787CREATE TRIGGER aud__message__action AFTER DELETE OR UPDATE ON message FOR EACH ROW EXECUTE PROCEDURE tf_action();
1788
1789
1790--
1791-- Name: aud__role__entity; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1792--
1793
1794CREATE TRIGGER aud__role__entity AFTER DELETE OR UPDATE ON role FOR EACH ROW EXECUTE PROCEDURE tf_entity();
1795
1796
1797--
1798-- Name: aud__room__entity; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1799--
1800
1801CREATE TRIGGER aud__room__entity AFTER DELETE OR UPDATE ON room FOR EACH ROW EXECUTE PROCEDURE tf_entity();
1802
1803
1804--
1805-- Name: aud__user__entity; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1806--
1807
1808CREATE TRIGGER aud__user__entity AFTER DELETE OR UPDATE ON "user" FOR EACH ROW EXECUTE PROCEDURE tf_entity();
1809
1810
1811--
1812-- Name: audt__act__action; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1813--
1814
1815CREATE TRIGGER audt__act__action AFTER DELETE OR UPDATE ON act FOR EACH ROW EXECUTE PROCEDURE tf_action();
1816
1817
1818--
1819-- Name: audt__obj__entity; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1820--
1821
1822CREATE TRIGGER audt__obj__entity AFTER DELETE OR UPDATE ON obj FOR EACH ROW EXECUTE PROCEDURE tf_entity();
1823
1824
1825--
1826-- Name: biut__message__action; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1827--
1828
1829CREATE TRIGGER biut__message__action BEFORE INSERT OR UPDATE ON message FOR EACH ROW EXECUTE PROCEDURE tf_action();
1830
1831
1832--
1833-- Name: biut_role_entity; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1834--
1835
1836CREATE TRIGGER biut_role_entity BEFORE INSERT OR UPDATE ON role FOR EACH ROW EXECUTE PROCEDURE tf_entity();
1837
1838
1839--
1840-- Name: biut_room_entity; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1841--
1842
1843CREATE TRIGGER biut_room_entity BEFORE INSERT OR UPDATE ON room FOR EACH ROW EXECUTE PROCEDURE tf_entity();
1844
1845
1846--
1847-- Name: biut_user_entity; Type: TRIGGER; Schema: ety; Owner: kobo_owner
1848--
1849
1850CREATE TRIGGER biut_user_entity BEFORE INSERT OR UPDATE ON "user" FOR EACH ROW EXECUTE PROCEDURE tf_entity();
1851
1852
1853SET search_path = auth, pg_catalog;
1854
1855--
1856-- Name: fk__user__user_substn__origin_user; Type: FK CONSTRAINT; Schema: auth; Owner: kobo_owner
1857--
1858
1859ALTER TABLE ONLY user_substitution
1860 ADD CONSTRAINT fk__user__user_substn__origin_user FOREIGN KEY (origin_user_id) REFERENCES ety."user"(id) ON UPDATE CASCADE ON DELETE CASCADE;
1861
1862
1863--
1864-- Name: fk__user__user_substn__substn_user; Type: FK CONSTRAINT; Schema: auth; Owner: kobo_owner
1865--
1866
1867ALTER TABLE ONLY user_substitution
1868 ADD CONSTRAINT fk__user__user_substn__substn_user FOREIGN KEY (substn_user_id) REFERENCES ety."user"(id) ON UPDATE CASCADE ON DELETE CASCADE;
1869
1870
1871--
1872-- Name: fk_user__logon; Type: FK CONSTRAINT; Schema: auth; Owner: kobo_owner
1873--
1874
1875ALTER TABLE ONLY logon
1876 ADD CONSTRAINT fk_user__logon FOREIGN KEY (user_id) REFERENCES ety."user"(id) ON UPDATE CASCADE ON DELETE CASCADE;
1877
1878
1879SET search_path = ety, pg_catalog;
1880
1881--
1882-- Name: fk__ety_obj__obj; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1883--
1884
1885ALTER TABLE ONLY act
1886 ADD CONSTRAINT fk__ety_obj__obj FOREIGN KEY (target_id) REFERENCES obj(id) ON UPDATE CASCADE ON DELETE RESTRICT;
1887
1888
1889--
1890-- Name: fk__pdic_act__act; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1891--
1892
1893ALTER TABLE ONLY act
1894 ADD CONSTRAINT fk__pdic_act__act FOREIGN KEY (act_id) REFERENCES pdic.action(act_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1895
1896
1897--
1898-- Name: fk__pdic_act__message; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1899--
1900
1901ALTER TABLE ONLY message
1902 ADD CONSTRAINT fk__pdic_act__message FOREIGN KEY (act_id) REFERENCES pdic.action(act_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1903
1904
1905--
1906-- Name: fk__pdic_ety__obj; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1907--
1908
1909ALTER TABLE ONLY obj
1910 ADD CONSTRAINT fk__pdic_ety__obj FOREIGN KEY (ety_id) REFERENCES pdic.ety(ety_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1911
1912
1913--
1914-- Name: fk__pdic_ety__role; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1915--
1916
1917ALTER TABLE ONLY role
1918 ADD CONSTRAINT fk__pdic_ety__role FOREIGN KEY (ety_id) REFERENCES pdic.ety(ety_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1919
1920
1921--
1922-- Name: fk__pdic_ety__room; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1923--
1924
1925ALTER TABLE ONLY room
1926 ADD CONSTRAINT fk__pdic_ety__room FOREIGN KEY (ety_id) REFERENCES pdic.ety(ety_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1927
1928
1929--
1930-- Name: fk__pdic_ety__user; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1931--
1932
1933ALTER TABLE ONLY "user"
1934 ADD CONSTRAINT fk__pdic_ety__user FOREIGN KEY (ety_id) REFERENCES pdic.ety(ety_id) ON UPDATE CASCADE ON DELETE RESTRICT;
1935
1936
1937--
1938-- Name: fk__room_user__room; Type: FK CONSTRAINT; Schema: ety; Owner: postgres
1939--
1940
1941ALTER TABLE ONLY room_user
1942 ADD CONSTRAINT fk__room_user__room FOREIGN KEY (room_id) REFERENCES room(id) ON UPDATE CASCADE ON DELETE RESTRICT;
1943
1944
1945--
1946-- Name: fk__room_user__user; Type: FK CONSTRAINT; Schema: ety; Owner: postgres
1947--
1948
1949ALTER TABLE ONLY room_user
1950 ADD CONSTRAINT fk__room_user__user FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE RESTRICT;
1951
1952
1953--
1954-- Name: fk__user__message; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1955--
1956
1957ALTER TABLE ONLY message
1958 ADD CONSTRAINT fk__user__message FOREIGN KEY (owner_id) REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE RESTRICT;
1959
1960
1961--
1962-- Name: fk__user__obj_hist__owner; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1963--
1964
1965ALTER TABLE ONLY obj_hist
1966 ADD CONSTRAINT fk__user__obj_hist__owner FOREIGN KEY (owner_id) REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE RESTRICT;
1967
1968
1969--
1970-- Name: fk__user__role; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1971--
1972
1973ALTER TABLE ONLY role
1974 ADD CONSTRAINT fk__user__role FOREIGN KEY (owner_id) REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE RESTRICT;
1975
1976
1977--
1978-- Name: fk__user__room; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1979--
1980
1981ALTER TABLE ONLY room
1982 ADD CONSTRAINT fk__user__room FOREIGN KEY (owner_id) REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE RESTRICT;
1983
1984
1985--
1986-- Name: fk__user__user; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1987--
1988
1989ALTER TABLE ONLY "user"
1990 ADD CONSTRAINT fk__user__user FOREIGN KEY (owner_id) REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE RESTRICT;
1991
1992
1993--
1994-- Name: fk_role__user_role; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
1995--
1996
1997ALTER TABLE ONLY user_role
1998 ADD CONSTRAINT fk_role__user_role FOREIGN KEY (role_id) REFERENCES role(id) ON UPDATE CASCADE ON DELETE CASCADE;
1999
2000
2001--
2002-- Name: fk_user__obj_hist__modify_user; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
2003--
2004
2005ALTER TABLE ONLY obj_hist
2006 ADD CONSTRAINT fk_user__obj_hist__modify_user FOREIGN KEY (modify_user_id) REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE RESTRICT;
2007
2008
2009--
2010-- Name: fk_user__user_role; Type: FK CONSTRAINT; Schema: ety; Owner: kobo_owner
2011--
2012
2013ALTER TABLE ONLY user_role
2014 ADD CONSTRAINT fk_user__user_role FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE ON DELETE CASCADE;
2015
2016
2017SET search_path = pdic, pg_catalog;
2018
2019--
2020-- Name: fk__act__act; Type: FK CONSTRAINT; Schema: pdic; Owner: kobo_owner
2021--
2022
2023ALTER TABLE ONLY action
2024 ADD CONSTRAINT fk__act__act FOREIGN KEY (super_act_id) REFERENCES action(act_id) ON UPDATE CASCADE ON DELETE RESTRICT;
2025
2026
2027--
2028-- Name: fk__ety__ety; Type: FK CONSTRAINT; Schema: pdic; Owner: kobo_owner
2029--
2030
2031ALTER TABLE ONLY ety
2032 ADD CONSTRAINT fk__ety__ety FOREIGN KEY (super_ety_id) REFERENCES ety(ety_id) ON UPDATE CASCADE ON DELETE RESTRICT;
2033
2034
2035--
2036-- Name: auth; Type: ACL; Schema: -; Owner: kobo_owner
2037--
2038
2039REVOKE ALL ON SCHEMA auth FROM PUBLIC;
2040REVOKE ALL ON SCHEMA auth FROM kobo_owner;
2041GRANT ALL ON SCHEMA auth TO kobo_owner;
2042GRANT USAGE ON SCHEMA auth TO kobo_waitress;
2043
2044
2045--
2046-- Name: ety; Type: ACL; Schema: -; Owner: postgres
2047--
2048
2049REVOKE ALL ON SCHEMA ety FROM PUBLIC;
2050REVOKE ALL ON SCHEMA ety FROM postgres;
2051GRANT ALL ON SCHEMA ety TO postgres;
2052GRANT ALL ON SCHEMA ety TO kobo_owner;
2053GRANT USAGE ON SCHEMA ety TO kobo_waitress;
2054
2055
2056--
2057-- Name: pdic; Type: ACL; Schema: -; Owner: postgres
2058--
2059
2060REVOKE ALL ON SCHEMA pdic FROM PUBLIC;
2061REVOKE ALL ON SCHEMA pdic FROM postgres;
2062GRANT ALL ON SCHEMA pdic TO postgres;
2063GRANT ALL ON SCHEMA pdic TO kobo_owner;
2064GRANT USAGE ON SCHEMA pdic TO kobo_waitress;
2065
2066
2067--
2068-- Name: public; Type: ACL; Schema: -; Owner: postgres
2069--
2070
2071REVOKE ALL ON SCHEMA public FROM PUBLIC;
2072REVOKE ALL ON SCHEMA public FROM postgres;
2073GRANT ALL ON SCHEMA public TO postgres;
2074GRANT ALL ON SCHEMA public TO PUBLIC;
2075
2076
2077SET search_path = auth, pg_catalog;
2078
2079--
2080-- Name: logon; Type: ACL; Schema: auth; Owner: kobo_owner
2081--
2082
2083REVOKE ALL ON TABLE logon FROM PUBLIC;
2084REVOKE ALL ON TABLE logon FROM kobo_owner;
2085GRANT ALL ON TABLE logon TO kobo_owner;
2086GRANT ALL ON TABLE logon TO kobo_waitress;
2087
2088
2089--
2090-- Name: user_substitution; Type: ACL; Schema: auth; Owner: kobo_owner
2091--
2092
2093REVOKE ALL ON TABLE user_substitution FROM PUBLIC;
2094REVOKE ALL ON TABLE user_substitution FROM kobo_owner;
2095GRANT ALL ON TABLE user_substitution TO kobo_owner;
2096GRANT ALL ON TABLE user_substitution TO kobo_waitress;
2097
2098
2099SET search_path = ety, pg_catalog;
2100
2101--
2102-- Name: act; Type: ACL; Schema: ety; Owner: kobo_owner
2103--
2104
2105REVOKE ALL ON TABLE act FROM PUBLIC;
2106REVOKE ALL ON TABLE act FROM kobo_owner;
2107GRANT ALL ON TABLE act TO kobo_owner;
2108GRANT ALL ON TABLE act TO kobo_waitress;
2109
2110
2111--
2112-- Name: message; Type: ACL; Schema: ety; Owner: kobo_owner
2113--
2114
2115REVOKE ALL ON TABLE message FROM PUBLIC;
2116REVOKE ALL ON TABLE message FROM kobo_owner;
2117GRANT ALL ON TABLE message TO kobo_owner;
2118GRANT ALL ON TABLE message TO kobo_waitress;
2119
2120
2121--
2122-- Name: obj; Type: ACL; Schema: ety; Owner: kobo_owner
2123--
2124
2125REVOKE ALL ON TABLE obj FROM PUBLIC;
2126REVOKE ALL ON TABLE obj FROM kobo_owner;
2127GRANT ALL ON TABLE obj TO kobo_owner;
2128GRANT ALL ON TABLE obj TO kobo_waitress;
2129
2130
2131--
2132-- Name: obj_hist; Type: ACL; Schema: ety; Owner: kobo_owner
2133--
2134
2135REVOKE ALL ON TABLE obj_hist FROM PUBLIC;
2136REVOKE ALL ON TABLE obj_hist FROM kobo_owner;
2137GRANT ALL ON TABLE obj_hist TO kobo_owner;
2138GRANT ALL ON TABLE obj_hist TO kobo_waitress;
2139
2140
2141--
2142-- Name: obj_id_seq; Type: ACL; Schema: ety; Owner: kobo_owner
2143--
2144
2145REVOKE ALL ON SEQUENCE obj_id_seq FROM PUBLIC;
2146REVOKE ALL ON SEQUENCE obj_id_seq FROM kobo_owner;
2147GRANT ALL ON SEQUENCE obj_id_seq TO kobo_owner;
2148GRANT USAGE ON SEQUENCE obj_id_seq TO kobo_waitress;
2149
2150
2151--
2152-- Name: role; Type: ACL; Schema: ety; Owner: kobo_owner
2153--
2154
2155REVOKE ALL ON TABLE role FROM PUBLIC;
2156REVOKE ALL ON TABLE role FROM kobo_owner;
2157GRANT ALL ON TABLE role TO kobo_owner;
2158GRANT ALL ON TABLE role TO kobo_waitress;
2159
2160
2161--
2162-- Name: room; Type: ACL; Schema: ety; Owner: kobo_owner
2163--
2164
2165REVOKE ALL ON TABLE room FROM PUBLIC;
2166REVOKE ALL ON TABLE room FROM kobo_owner;
2167GRANT ALL ON TABLE room TO kobo_owner;
2168GRANT ALL ON TABLE room TO kobo_waitress;
2169
2170
2171--
2172-- Name: user; Type: ACL; Schema: ety; Owner: kobo_owner
2173--
2174
2175REVOKE ALL ON TABLE "user" FROM PUBLIC;
2176REVOKE ALL ON TABLE "user" FROM kobo_owner;
2177GRANT ALL ON TABLE "user" TO kobo_owner;
2178GRANT ALL ON TABLE "user" TO kobo_waitress;
2179
2180
2181--
2182-- Name: user_role; Type: ACL; Schema: ety; Owner: kobo_owner
2183--
2184
2185REVOKE ALL ON TABLE user_role FROM PUBLIC;
2186REVOKE ALL ON TABLE user_role FROM kobo_owner;
2187GRANT ALL ON TABLE user_role TO kobo_owner;
2188GRANT ALL ON TABLE user_role TO kobo_waitress;
2189
2190
2191SET search_path = pdic, pg_catalog;
2192
2193--
2194-- Name: action; Type: ACL; Schema: pdic; Owner: kobo_owner
2195--
2196
2197REVOKE ALL ON TABLE action FROM PUBLIC;
2198REVOKE ALL ON TABLE action FROM kobo_owner;
2199GRANT ALL ON TABLE action TO kobo_owner;
2200GRANT ALL ON TABLE action TO kobo_waitress;
2201
2202
2203--
2204-- Name: ety; Type: ACL; Schema: pdic; Owner: kobo_owner
2205--
2206
2207REVOKE ALL ON TABLE ety FROM PUBLIC;
2208REVOKE ALL ON TABLE ety FROM kobo_owner;
2209GRANT ALL ON TABLE ety TO kobo_owner;
2210GRANT ALL ON TABLE ety TO kobo_waitress;
2211
2212
2213--
2214-- PostgreSQL database dump complete
2215--