vnrgs61e

1create sequence message_id_seq
2;
3
4create domain color_value as integer
5	constraint color_value_check check ((VALUE >= 0) AND (VALUE <= 255))
6;
7
8create type skin_color as
9(
10	r color_value,
11	g color_value,
12	b color_value
13)
14;
15
16create domain size_value as real
17	constraint size_value_check check (VALUE >= (0)::double precision)
18;
19
20create type size as
21(
22	len size_value,
23	width size_value,
24	height size_value
25)
26;
27
28create table if not exists mechanicdetalis
29(
30	model text not null
31		constraint mechanicdetalis_model_pk
32			primary key,
33	material text,
34	size size
35)
36;
37
38create table if not exists motors
39(
40	model text not null
41		constraint motors_model_pk
42			primary key,
43	power real
44		constraint motors_power_check
45			check (power >= (0)::double precision),
46	operatingvoltage real
47		constraint motors_operatingvoltage_check
48			check (operatingvoltage >= (0)::double precision),
49	maxvoltage real
50		constraint motors_maxvoltage_check
51			check (maxvoltage >= (0)::double precision),
52	constraint motors_check
53		check (maxvoltage >= operatingvoltage)
54)
55;
56
57create table if not exists platforms
58(
59	model text not null
60		constraint platforms_model_pk
61			primary key,
62	microcontroller text,
63	operating_voltage real
64		constraint platforms_operating_voltage_check
65			check (operating_voltage >= (0)::double precision),
66	max_voltage real
67		constraint platforms_max_voltage_check
68			check (max_voltage >= (0)::double precision),
69	type_connection text,
70	amperage real
71		constraint platforms_amperage_check
72			check (amperage >= (0)::double precision),
73	freq real,
74	analog_inputs text,
75	flashmemory text,
76	ram text,
77	clock_freq text,
78	constraint platforms_check
79		check (max_voltage >= operating_voltage)
80)
81;
82
83create table if not exists controllers
84(
85	model text not null
86		constraint controllers_model_pk
87			primary key,
88	model_cpu text,
89	ram text,
90	operating_voltage real,
91	max_voltage real,
92	analog_inputs integer,
93	constraint controllers_check
94		check ((operating_voltage >= (0)::double precision) AND (max_voltage >= (0)::double precision) AND (analog_inputs >= 0)),
95	constraint controllers_check1
96		check (operating_voltage <= max_voltage)
97)
98;
99
100create table if not exists sensors
101(
102	model text not null
103		constraint sensors_model_pk
104			primary key,
105	operating_voltage real
106		constraint sensors_operating_voltage_check
107			check (operating_voltage >= (0)::double precision),
108	max_voltage real
109		constraint sensors_max_voltage_check
110			check (max_voltage >= (0)::double precision),
111	constraint sensors_check
112		check (max_voltage >= operating_voltage)
113)
114;
115
116create table if not exists other_resourses
117(
118	material text,
119	model text not null
120		constraint other_resourses_model_pk
121			primary key
122)
123;
124
125create table if not exists tools
126(
127	material text,
128	model text not null
129		constraint tools_model_pk
130			primary key
131)
132;
133
134create table if not exists stores
135(
136	id integer not null
137		constraint stores_pkey
138			primary key,
139	address text not null,
140	opening_time time,
141	closing_time time,
142	constraint stores_check
143		check (opening_time <= closing_time)
144)
145;
146
147create table if not exists worker
148(
149	id integer not null
150		constraint worker_pkey
151			primary key,
152	position text not null,
153	salary real
154		constraint worker_salary_check
155			check (salary >= (0)::double precision),
156	date_hiring timestamp not null,
157	empl_history text not null,
158	dismiss_date timestamp default '9999-12-31 00:00:00'::timestamp without time zone not null,
159	id_store integer
160		constraint worker_stores_id_fk
161			references stores,
162	id_humans integer,
163	constraint worker_check
164		check (date_hiring <= dismiss_date),
165	constraint worker_check1
166		check (date_hiring <= dismiss_date)
167)
168;
169
170create table if not exists "user"
171(
172	id integer not null
173		constraint user_pkey
174			primary key,
175	name text not null,
176	"e-mail" text,
177	phone text
178		constraint user_phone_check
179			check (phone ~~ '+%(___)___-__-__'::text),
180	surname text not null
181)
182;
183
184create table if not exists orders
185(
186	id integer not null
187		constraint orders_pkey
188			primary key,
189	id_user integer
190		constraint orders_user_id_fk
191			references "user",
192	price real
193		constraint orders_price_check
194			check (price >= (0)::double precision),
195	date timestamp,
196	id_store integer
197		constraint orders_stores_id_fk
198			references stores,
199	address text not null,
200	payment_type text
201		constraint orders_payment_type_check
202			check ((payment_type ~~* 'Наличными'::text) OR (payment_type ~~* 'Безналичными при получении'::text) OR (payment_type ~~* 'VISA'::text) OR (payment_type ~~* 'MasterCard'::text))
203)
204;
205
206create or replace function check_item_availability() returns trigger
207	language plpgsql
208as $$
209DECLARE
210  has_unavailable_items BOOLEAN;
211BEGIN
212  has_unavailable_items := (with
213    items_available as (
214      select p.amount, p.part_number from products p, products_cart pc, orders o
215      where p.part_number = pc.part_number and pc.id_cart = o.id_user
216    ), items_required as (
217      select amount, part_number from products_cart where id_user = new.id_user
218    )
219select exists(
220    select from items_required
221    inner join items_available on 2
222    where items_required.amount > items_available.amount));
223
224  IF has_unavailable_items
225  THEN RAISE EXCEPTION 'Not enough items';
226  END IF;
227END;
228$$
229;
230
231create trigger validate_order
232	before insert
233	on orders
234	for each row
235	execute procedure check_item_availability()
236;
237
238create table if not exists message
239(
240	text_message text,
241	from_user boolean not null,
242	id_user integer not null
243		constraint message_user_id_fk
244			references "user",
245	id_worker integer not null
246		constraint message_worker_id_fk
247			references worker,
248	id integer default nextval('message_id_seq'::regclass) not null
249		constraint message_id_pk
250			primary key
251		constraint message_id_key
252			unique,
253	time timestamp not null
254)
255;
256
257create table if not exists shopping_cart
258(
259	price integer
260		constraint shopping_cart_price_check
261			check (price >= 0),
262	date timestamp,
263	id_user integer not null
264		constraint "shopping_cart _id_user_pk"
265			primary key
266		constraint "shopping_cart _user_id_fk"
267			references "user"
268)
269;
270
271create unique index if not exists "shopping_cart _id_user_uindex"
272	on shopping_cart (id_user)
273;
274
275create table if not exists products
276(
277	part_number integer not null
278		constraint products_part_number_pk
279			primary key,
280	id_store integer
281		constraint products_stores_id_fk
282			references stores,
283	model text,
284	amount integer not null
285		constraint products_amount_check
286			check (amount >= 0)
287		constraint products_amount_check1
288			check (amount >= 0)
289)
290;
291
292create table if not exists products_order
293(
294	part_number integer
295		constraint order_products_products_part_number_fk
296			references products,
297	id_order integer
298		constraint order_products_orders_id_fk
299			references orders,
300	amount integer not null
301		constraint products_order_amount_check
302			check (amount > 0)
303		constraint products_order_amount_check1
304			check (amount >= 0),
305	unit_price integer not null
306		constraint products_order_unit_price_check
307			check (unit_price >= 0)
308		constraint products_order_unit_price_check1
309			check (unit_price >= 0)
310)
311;
312
313create table if not exists products_cart
314(
315	part_number integer
316		constraint products_cart_products_part_number_fk
317			references products,
318	id_cart integer
319		constraint "products_cart_shopping_cart _id_user_fk"
320			references shopping_cart,
321	amount integer not null
322		constraint products_cart_amount_check
323			check (amount > 0)
324		constraint products_cart_amount_check1
325			check (amount >= 0)
326		constraint products_cart_amount_check2
327			check (amount >= 0)
328		constraint products_cart_amount_check3
329			check (amount >= 0)
330)
331;
332
333create table if not exists compatibility
334(
335	model_1 text not null,
336	model_2 text not null
337)
338;
339
340create table if not exists information
341(
342	type text
343		constraint information_type_check
344			check ((type ~~* 'Сенсор'::text) OR (type ~~* 'Механическая деталь'::text) OR (type ~~* 'Мотор'::text) OR (type ~~* 'Платформа'::text) OR (type ~~* 'Контроллер'::text) OR (type ~~* 'Инструмент'::text) OR (type ~~* 'Прочие ресурсы'::text)),
345	model text not null
346		constraint information_pkey
347			primary key,
348	name text not null,
349	price real
350		constraint information_price_check
351			check (price >= (0)::double precision),
352	date_of_creation timestamp,
353	provider text,
354	description text,
355	count integer not null
356)
357;
358
359alter table mechanicdetalis
360	add constraint mechanicdetalis_information_model_fk
361		foreign key (model) references information
362;
363
364alter table motors
365	add constraint motors_information_model_fk
366		foreign key (model) references information
367;
368
369alter table platforms
370	add constraint platforms_information_model_fk
371		foreign key (model) references information
372;
373
374alter table controllers
375	add constraint controllers_information_model_fk
376		foreign key (model) references information
377;
378
379alter table sensors
380	add constraint sensors_information_model_fk
381		foreign key (model) references information
382;
383
384alter table other_resourses
385	add constraint other_resourses_information_model_fk
386		foreign key (model) references information
387;
388
389alter table tools
390	add constraint tools_information_model_fk
391		foreign key (model) references information
392;
393
394alter table products
395	add constraint products_information_model_fk
396		foreign key (model) references information
397;
398
399alter table compatibility
400	add constraint compatibility_information_model_fk
401		foreign key (model_1) references information
402;
403
404alter table compatibility
405	add constraint compatibility_information_model_fk_2
406		foreign key (model_2) references information
407;
408
409create table if not exists humans
410(
411	id serial not null
412		constraint humans_pkey
413			primary key,
414	name text not null,
415	surname text not null,
416	document text not null
417		constraint humans_document_check
418			check ((document ~~* 'Паспорт'::text) OR (document ~~* 'Временное удостоверение личности'::text) OR (document ~~* 'Свидетельство о рождении'::text) OR (document ~~* 'Заграничный паспорт'::text)),
419	number_document text not null,
420	address text,
421	birthdate timestamp not null,
422	patronym text,
423	gender char not null
424		constraint humans_gender_check
425			check ((gender = 'Ж'::bpchar) OR (gender = 'М'::bpchar)),
426	"e-mail" text,
427	phone text
428		constraint humans_phone_check
429			check (phone ~~ '+%(___)___-__-__'::text),
430	color skin_color,
431	constraint humans_document_number_document_key
432		unique (document, number_document)
433)
434;
435
436alter table worker
437	add constraint worker_humans_id_fk
438		foreign key (id_humans) references humans
439;