k9d5d4hZ

1-- Действия над ошибками
2create sequence if not exists public.smev3_error_action_id_seq increment by 1 minvalue 1 maxvalue 32767 start 1 cache 1 no cycle;
3-- Permissions
4alter sequence public.smev3_error_action_id_seq owner to postgres;
5grant all on sequence public.smev3_error_action_id_seq to postgres;
6create table if not exists public.smev3_error_action (
7	id int2 not null default nextval('public.smev3_error_action_id_seq'),
8	code varchar not null,
9	name varchar not null,
10	date_mode timestamp not null default now(),
11	edit_owner int8 not null,
12	constraint error_action_pkey primary key(id)
13);
14comment on table public.smev3_error_action is 'Действия над ошибками';
15-- Column comments
16comment on column public.smev3_error_action.id is 'id действия над ошибкой';
17comment on column public.smev3_error_action.code is 'Код ошибки';
18comment on column public.smev3_error_action.name is 'Название ошибки';
19comment on column public.smev3_error_action.date_mode is 'Дата изменения записи';
20comment on column public.smev3_error_action.edit_owner is 'Сотрудник, изменивший запись';
21
22
23-- Уведомления
24create sequence if not exists public.smev3_notification_id_seq increment by 1 minvalue 1 maxvalue 32767 start 1 cache 1 no cycle;
25-- Permissions
26alter sequence public.smev3_notification_id_seq owner to postgres;
27grant all on sequence public.smev3_notification_id_seq to postgres;
28create table if not exists public.smev3_notification (
29	id int2 not null default nextval('public.smev3_notification_id_seq'),
30	code varchar not null,
31	name varchar not null,
32	date_mode timestamp not null default now(),
33	edit_owner int8 not null,
34	constraint smev3_notification_pkey primary key(id)
35);
36comment on table public.smev3_notification IS 'Уведомления';
37-- Column comments
38comment on column public.smev3_notification.id is 'id уведомления';
39comment on column public.smev3_notification.code is 'Код уведомления';
40comment on column public.smev3_notification.name is 'Название уведомления';
41comment on column public.smev3_notification.date_mode is 'Дата изменения записи';
42comment on column public.smev3_notification.edit_owner is 'Сотрудник, изменивший запись';
43-- Permissions
44alter table public.smev3_notification owner to postgres;
45grant all on table public.smev3_notification to postgres;
46
47
48-- Точки доступа
49create sequence if not exists public.smev3_access_point_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1 no cycle;
50-- Permissions
51alter sequence public.smev3_access_point_id_seq owner to postgres;
52grant all on sequence public.smev3_access_point_id_seq to postgres;
53create table if not exists public.smev3_access_point (
54	id int8 not null default nextval('public.smev3_access_point_id_seq'),
55	name varchar not null,
56	access_point varchar not null,
57	smev_version varchar not null,
58	send_handler varchar not null,
59	synch bool not null,
60	date_mode timestamp not null default now(),
61	edit_owner int8 not null,
62	constraint smev3_access_point_pkey primary key(id)
63);
64comment on table public.smev3_access_point IS 'Точки доступа';
65-- Column comments
66comment on column public.smev3_access_point.id is 'id сведения';
67comment on column public.smev3_access_point.name is 'Название';
68comment on column public.smev3_access_point.access_point is 'Адрес точки доступа';
69comment on column public.smev3_access_point.smev_version is 'Версия СМЭВ';
70comment on column public.smev3_access_point.send_handler is 'Обработчик отправки';
71comment on column public.smev3_access_point.synch is 'Признак синхронности';
72comment on column public.smev3_access_point.date_mode is 'Дата изменения записи';
73comment on column public.smev3_access_point.edit_owner is 'Сотрудник, изменивший запись';
74-- Permissions
75alter table public.smev3_access_point owner to postgres;
76grant all on table public.smev3_access_point to postgres;
77
78
79-- Статусы
80create sequence if not exists public.smev3_status_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1 no cycle;
81-- Permissions
82alter sequence public.smev3_status_id_seq owner to postgres;
83grant all on sequence public.smev3_status_id_seq to postgres;
84create table if not exists public.smev3_status (
85	id int2 not null default nextval('public.smev3_status_id_seq'),
86	code varchar not null,
87	name varchar not null,
88	date_mode timestamp not null default now(),
89	edit_owner int8 not null,
90	constraint smev3_status_pkey primary key(id)
91);
92comment on table public.smev3_status IS 'Документ';
93-- Column comments
94comment on column public.smev3_status.id is 'id статуса';
95comment on column public.smev3_status.code is 'Состояние документа';
96comment on column public.smev3_status.name is 'Атрибут один';
97comment on column public.smev3_status.date_mode is 'Дата изменения записи';
98comment on column public.smev3_status.edit_owner is 'Сотрудник, изменивший запись';
99-- Permissions
100alter table public.smev3_status owner to postgres;
101grant all on table public.smev3_status to postgres;
102
103
104-- Вид сведений
105create sequence if not exists public.smev3_type_of_inf_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1 no cycle;
106-- Permissions
107alter sequence public.smev3_type_of_inf_id_seq owner to postgres;
108grant all on sequence public.smev3_type_of_inf_id_seq to postgres;
109create table if not exists public.smev3_type_of_inf (
110	id int8 not null default nextval('public.smev3_type_of_inf_id_seq'),
111	name varchar not null,
112	rsid varchar not null,
113	namespace varchar not null,
114	root_element varchar not null,
115	root_element_description varchar not null,
116	access_point_id int2 not null,
117	handler varchar not null,
118	signature bool not null,
119	test_mode bool not null,
120	activated bool not null,
121	date_mode timestamp not null default now(),
122	edit_owner int8 not null,
123	constraint smev3_type_of_inf_pkey primary key(id),
124	constraint type_of_inf_access_point_fkey foreign key(access_point_id) references smev3_access_point(id)
125);
126comment on table public.smev3_type_of_inf IS 'Очередь запросов';
127-- Column comments
128comment on column public.smev3_type_of_inf.id is 'id сведения';
129comment on column public.smev3_type_of_inf.name is 'Название';
130comment on column public.smev3_type_of_inf.rsid is 'RSID в СМЭВ';
131comment on column public.smev3_type_of_inf.namespace is 'NameSpace URI';
132comment on column public.smev3_type_of_inf.root_element is 'rootElementName';
133comment on column public.smev3_type_of_inf.root_element_description is 'rootElementDescription';
134comment on column public.smev3_type_of_inf.access_point_id is 'Точка доступа';
135comment on column public.smev3_type_of_inf.handler is 'Обработчик';
136comment on column public.smev3_type_of_inf.signature is 'Признак подписи сотрудника';
137comment on column public.smev3_type_of_inf.test_mode is 'Признак тестового взаимодействия';
138comment on column public.smev3_type_of_inf.activated is 'Признак активности';
139comment on column public.smev3_type_of_inf.date_mode is 'Дата изменения записи';
140comment on column public.smev3_type_of_inf.edit_owner is 'Сотрудник, изменивший запись';
141-- Permissions
142alter table public.smev3_type_of_inf owner to postgres;
143grant all on table public.smev3_type_of_inf to postgres;
144
145
146-- Ошибки для Видов сведений
147create sequence if not exists public.smev3_error_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1 no cycle;
148-- Permissions
149alter sequence public.smev3_error_id_seq owner to postgres;
150grant all on sequence public.smev3_error_id_seq to postgres;
151create table if not exists public.smev3_error (
152	id int8 not null default nextval('public.smev3_error_id_seq'),
153	error_code varchar not null,
154	error_descr varchar null,
155	type_of_inf_id int8 not null,
156	error_action_id int2 not null,
157	date_mode timestamp not null default now(),
158	edit_owner int8 not null,
159	constraint smev3_error_pkey primary key(id),
160	constraint error_error_action_fkey foreign key(error_action_id) references smev3_error_action(id),
161	constraint error_type_of_inf_fkey foreign key(type_of_inf_id) references smev3_type_of_inf(id)
162);
163comment on table public.smev3_error IS 'Ошибки для Видов сведений';
164-- Column comments
165comment on column public.smev3_error.id is 'id ошибки';
166comment on column public.smev3_error.error_code is 'Код ошибки';
167comment on column public.smev3_error.error_descr is 'Описание ошибки';
168comment on column public.smev3_error.type_of_inf_id is 'Вид сведений';
169comment on column public.smev3_error.error_action_id is 'Действие';
170comment on column public.smev3_error.date_mode is 'Дата изменения записи';
171comment on column public.smev3_error.edit_owner is 'Сотрудник, изменивший запись';
172
173
174-- Состояние документа(doc_state)
175create sequence if not exists public.smev3_doc_state_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1 no cycle;
176-- Permissions
177alter sequence public.smev3_doc_state_id_seq owner to postgres;
178grant all on sequence public.smev3_doc_state_id_seq to postgres;
179create table if not exists public.smev3_doc_state (
180	id int8 not null default nextval('public.smev3_doc_state_id_seq'),
181	name_id int8 not null,
182	case_number varchar not null,
183	service_or_function_type varchar not null,
184	service_or_function_code varchar not null,
185	statement int8 not null,
186	status_id int2 not null,
187	esrn_personal int8 not null,
188	employee int8 not null,
189	signature varchar not null,
190	request_date timestamp not null default now(),
191	response_date timestamp null,
192	plan_response_date timestamp null,
193	signature_response varchar not null,
194	notification_id int2 not null,
195	date_mode timestamp not null default now(),
196	edit_owner int8 not null,
197	constraint smev3_doc_state_pkey primary key(id),
198	constraint doc_state_type_of_inf_fkey foreign key(name_id) references smev3_type_of_inf(id),
199	constraint doc_state_notification_fkey foreign key(notification_id) references smev3_notification(id),
200	constraint doc_state_status_fkey foreign key(status_id) references smev3_status(id)
201);
202comment on table public.smev3_doc_state IS 'Состояние документа';
203-- Column comments
204comment on column public.smev3_doc_state.id is 'id состояния документа';
205comment on column public.smev3_doc_state.name_id is 'вид сведений';
206comment on column public.smev3_doc_state.case_number is 'ВРМ номер дела';
207comment on column public.smev3_doc_state.service_or_function_type is 'ВРМ признак услуга/функция';
208comment on column public.smev3_doc_state.service_or_function_code is 'ВРМ код ФРГУ';
209comment on column public.smev3_doc_state.statement is 'Заявление';
210comment on column public.smev3_doc_state.status_id is 'Статус документа';
211comment on column public.smev3_doc_state.esrn_personal is 'Личное дело';
212comment on column public.smev3_doc_state.employee is 'Сотрудник';
213comment on column public.smev3_doc_state.signature is 'Подпись запроса';
214comment on column public.smev3_doc_state.request_date is 'Дата запроса';
215comment on column public.smev3_doc_state.response_date is 'Дата ответа';
216comment on column public.smev3_doc_state.plan_response_date is 'Плановая дата ответа';
217comment on column public.smev3_doc_state.signature_response is 'Подпись ответа';
218comment on column public.smev3_doc_state.date_mode is 'Дата изменения записи';
219comment on column public.smev3_doc_state.edit_owner is 'Сотрудник, изменивший запись';
220comment on column public.smev3_doc_state.notification_id is 'Уведомление';
221-- Permissions
222alter table public.smev3_doc_state owner to postgres;
223grant all on table public.smev3_doc_state to postgres;
224
225
226-- Документ
227create sequence if not exists public.smev3_doc_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1 no cycle;
228-- Permissions
229alter sequence public.smev3_doc_id_seq owner to postgres;
230grant all on sequence public.smev3_doc_id_seq to postgres;
231create table if not exists public.smev3_doc (
232	id int8 not null default nextval('public.smev3_doc_id_seq'),
233	doc_state_id int8 not null,
234	attr1 int2 null,
235	attr2 varchar null,
236	date_mode timestamp not null default now(),
237	edit_owner int8 not null,
238	constraint smev3_doc_pkey primary key(id),
239	constraint doc_state_doc_state_fkey foreign key(doc_state_id) references smev3_doc_state(id)
240);
241comment on table public.smev3_doc IS 'Документ';
242-- Column comments
243comment on column public.smev3_doc.id is 'id документа';
244comment on column public.smev3_doc.doc_state_id is 'Состояние документа';
245comment on column public.smev3_doc.attr1 is 'Атрибут один';
246comment on column public.smev3_doc.attr2 is 'Атрибут два';
247comment on column public.smev3_doc.date_mode is 'Дата изменения записи';
248comment on column public.smev3_doc.edit_owner is 'Сотрудник, изменивший запись';
249-- Permissions
250alter table public.smev3_doc owner to postgres;
251grant all on table public.smev3_doc to postgres;
252
253
254-- Очередь запросов
255create sequence if not exists public.smev3_toi_queue_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1 no cycle;
256-- Permissions
257alter sequence public.smev3_toi_queue_id_seq owner to postgres;
258grant all on sequence public.smev3_toi_queue_id_seq to postgres;
259create table if not exists public.smev3_toi_queue (
260	id int8 not null default nextval('public.smev3_toi_queue_id_seq'),
261	doc_state_id int8 not null,
262	last_response_data_time timestamp null,
263	queue_status_id int2 not null,
264	error_id int2 not null,
265	smev_status varchar not null,
266	message_id varchar not null,
267	reference_message_id varchar not null,
268	send_request_date_time timestamp not null default now(),
269	get_response_date_time timestamp null,
270	k_request_date_time timestamp null,
271	date_mode timestamp not null default now(),
272	edit_owner int8 not null,
273	constraint smev3_toi_queue_pkey primary key(id),
274	constraint toi_queue_doc_state_fkey foreign key(doc_state_id) references smev3_doc_state(id),
275	constraint toi_queue_status_fkey foreign key(queue_status_id) references smev3_status(id),
276	constraint toi_queue_error_fkey foreign key(error_id) references smev3_error(id)
277);
278comment on table public.smev3_toi_queue IS 'Очередь запросов';
279-- Column comments
280comment on column public.smev3_toi_queue.id is 'id запроса';
281comment on column public.smev3_toi_queue.doc_state_id is 'Состояние документа';
282comment on column public.smev3_toi_queue.last_response_data_time is 'Дата последнего ответа';
283comment on column public.smev3_toi_queue.queue_status_id is 'Статус объекта очереди';
284comment on column public.smev3_toi_queue.error_id is 'Описание ошибки';
285comment on column public.smev3_toi_queue.smev_status is 'Статус из СМЭВ';
286comment on column public.smev3_toi_queue.message_id is 'Message ID';
287comment on column public.smev3_toi_queue.reference_message_id is 'Reference Message ID';
288comment on column public.smev3_toi_queue.send_request_date_time is 'Дата отправки запроса';
289comment on column public.smev3_toi_queue.get_response_date_time is 'Дата отправки ответа';
290comment on column public.smev3_toi_queue.k_request_date_time is 'Дата отправки Ack';
291comment on column public.smev3_toi_queue.date_mode is 'Дата изменения записи';
292comment on column public.smev3_toi_queue.edit_owner is 'Сотрудник, изменивший запись';
293-- Permissions
294alter table public.smev3_toi_queue owner to postgres;
295grant all on table public.smev3_toi_queue to postgres;
296
297
298-- Услуга-Вид сведений
299create sequence if not exists public.smev3_usluga_type_of_inf_id_seq increment by 1 minvalue 1 maxvalue 2147483647 start 1 cache 1 no cycle;
300-- Permissions
301alter sequence public.smev3_usluga_type_of_inf_id_seq owner to postgres;
302grant all on sequence public.smev3_usluga_type_of_inf_id_seq to postgres;
303create table if not exists public.smev3_usluga_type_of_inf (
304	id int8 not null default nextval('public.smev3_usluga_type_of_inf_id_seq'),
305	type_of_inf_id int8 not null,
306	usluga int2 not null,
307	date_mode timestamp not null default now(),
308	edit_owner int8 not null,
309	constraint smev3_usluga_type_of_inf_pkey primary key(id),
310	constraint usluga_type_of_inf_type_of_inf_fkey foreign key(type_of_inf_id) references smev3_type_of_inf(id)
311);
312comment on table public.smev3_usluga_type_of_inf IS 'Услуга-Вид сведений';
313-- Column comments
314comment on column public.smev3_usluga_type_of_inf.id is 'id уведомления';
315comment on column public.smev3_usluga_type_of_inf.type_of_inf_id is 'Вид сведений';
316comment on column public.smev3_usluga_type_of_inf.usluga is 'Услуга';
317comment on column public.smev3_usluga_type_of_inf.date_mode is 'Дата изменения записи';
318comment on column public.smev3_usluga_type_of_inf.edit_owner is 'Сотрудник, изменивший запись';
319-- Permissions
320alter table public.smev3_usluga_type_of_inf owner to postgres;
321grant all on table public.smev3_usluga_type_of_inf to postgres;