|
23.01.2017, 16:02
#39389614
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Откуда: с болот
Сообщения: 3 797
Рейтинг:
0
/ 0
|
|
|
|
У меня еще одна идейка оформилась, которую набросал, но еще не проверял. Не буду сейчас расписывать всевозможные варианты того, как я собираюсь "это" использовать (на описание уйдет немало времени), просто гляньте, плз, кому не влом.
Выглядит, конечно, пахабно, но тем не менее... 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
/*
* Типы объектов
*/
CREATE TABLE "obj_types"
(
"id" ID NOT NULL,
"name" NAME NOT NULL,
CONSTRAINT "obj_types__pk" PRIMARY KEY ("id"),
CONSTRAINT "obj_types__uq" UNIQUE ("name")
);
/*
* Атрибуты типа объекта
*/
CREATE TABLE "type_attrs"
(
"type_id" ID NOT NULL
"id" ID NOT NULL,
"name" NAME NOT NULL,
CONSTRAINT "type_attrs__pk" PRIMARY KEY ("id"),
CONSTRAINT "type_attrs__uq" UNIQUE ("type_id", "name"),
CONSTRAINT "type_attrs__fk__obj_types" FOREIGN KEY ("type_id")
REFERENCES "obj_types" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);
REVOKE ALL ON TABLE "type_attrs" FROM ROLE USERS;
CREATE GENERATOR "type_attrs__GEN";
SET TERM ^;
CREATE OR ALTER TRIGGER "type_attrs__TR_BIU"
FOR "type_attrs"
ACTIVE
BEFORE INSERT OR UPDATE
POSITION 0
AS
BEGIN
IF (NOT(NEW."id" IS NOT NULL
AND NEW."id" <= GEN_ID("type_attrs__GEN", 0)
) THEN
NEW."id" = GEN_ID("type_attrs__GEN", 1);
END^
SET TERM ;^
CREATE OR ALTER EXCEPTION "attr_value__xRowExists"
'Существует ссылка внешнего ключа';
SET TERM ^;
CREATE OR ALTER PROCEDURE "type_attrs__set"
(
"oldId" ID DEFAULT NULL,
"typeId" ID DEFAULT NULL,
"name" NAME DEFAULT NULL
)
RETURNS
(
"id" ID,
"x" BOOL
)
AS
DECLARE VARIABLE typeId ID DEFAULT NULL;
BEGIN
IF (:"oldId" IS NULL) THEN
BEGIN
IF (:"typeId" IS NOT NULL AND :"name" IS NOT NULL) THEN
INSERT INTO "type_attrs" ("type_id", "name")
VALUES (:"typeId", :"name")
RETURNING "id" INTO: "id";
END
ELSE
IF (:"typeId" IS NOT NULL AND :"name" IS NOT NULL) THEN
BEGIN
SELECT "type_id", "id"
FROM "type_attrs"
WHERE "id" = :"oldId"
FOR UPDATE WITH LOCK
INTO: typeId, "id";
IF (:"id" IS NULL) THEN EXIT;
-- Для записей в "obj" и "type_attrs"
-- блокируем общую запись в "obj_types"
-- для предотвращения изменения соответствующих записей из других
-- транзакций в таблицах "type_attr" и "attr_value"
SELECT "id"
FROM "obj_types"
WHERE "id" = :typeId
FOR UPDATE WITH LOCK;
INTO: typeId
SAVEPOINT "type_attrs__set__SVPNT";
BEGIN
"x" = 'N';
-- определяем наличие реферального FK
UPDATE "type_attrs"
SET "id" = NULL
WHERE "id" = :"oldId";
WHEN EXCEPTION "attr_value__xRowExists" DO
"x" = 'Y';
END
ROLLBACK TO SAVEPOINT "type_attrs__set__SVPNT";
RELEASE SAVEPOINT "type_attrs__set__SVPNT";
UPDATE "type_attrs"
SET "type_id" = :"typeId",
"name" = :"name"
WHERE "id" = :"oldId"
RETURNING "id" INTO: "id";
END
ELSE
DELETE FROM "type_attrs"
WHERE "id" = :"oldId";
SUSPEND;
END^
SET TERM ;^
GRANT ALL ON TABLE "type_attr" TO PROCEDURE "type_attr__set";
GRANT EXECUTE ON PROCEDURE "type_attr__set" TO ROLE USERS;
/*
* Объекты
*/
CREATE TABLE "obj"
(
"type_id" ID NOT NULL,
"id" ID NOT NULL,
"name" NAME NOT NULL,
CONSTRAINT "obj__pk" PRIMARY KEY ("id"),
CONSTRAINT "obj__uq" UNIQUE ("name"),
CONSTRAINT "obj__fk__obj_types" FOREIGN KEY ("type_id")
REFERENCES "obj_types" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);
REVOKE ALL ON TABLE "obj" FROM ROLE USERS;
CREATE GENERATOR "obj__GEN";
SET TERM ^;
CREATE OR ALTER TRIGGER "obj__TR_BIU"
FOR "obj"
ACTIVE
BEFORE INSERT OR UPDATE
POSITION 0
AS
BEGIN
IF (NOT(NEW."id" IS NOT NULL
AND NEW."id" <= GEN_ID("obj__GEN", 0)
) THEN
NEW."id" = GEN_ID("obj__GEN", 1);
END^
SET TERM ;^
SET TERM ^;
CREATE OR ALTER PROCEDURE "obj__set"
(
"oldId" ID DEFAULT NULL,
"typeId" ID DEFAULT NULL,
"name" NAME DEFAULT NULL
)
RETURNS
(
"id" ID,
"x" BOOL
)
AS
DECLARE VARIABLE typeId ID DEFAULT NULL;
BEGIN
IF (:"oldId" IS NULL) THEN
BEGIN
IF (:"typeId" IS NOT NULL AND :"name" IS NOT NULL) THEN
INSERT INTO "obj" ("type_id", "name")
VALUES (:"typeId", :"name")
RETURNING "id" INTO: "id";
END
ELSE
IF (:"typeId" IS NOT NULL AND :"name" IS NOT NULL) THEN
BEGIN
SELECT "type_id", "id"
FROM "obj"
WHERE "id" = :"oldId"
FOR UPDATE WITH LOCK
INTO: typeId, "id";
IF (:"id" IS NULL) THEN EXIT;
-- Для записей в "obj" и "type_attrs"
-- блокируем общую запись в "obj_types"
-- для предотвращения изменения соответствующих записей из других
-- транзакций в таблицах "type_attr" и "attr_value"
SELECT "id"
FROM "obj_types"
WHERE "id" = :typeId
FOR UPDATE WITH LOCK;
INTO: typeId
SAVEPOINT "obj__set__SVPNT";
BEGIN
"x" = 'N';
-- определяем наличие реферального FK
UPDATE "obj"
SET "id" = NULL
WHERE "id" = :"oldId";
WHEN EXCEPTION "attr_value__xRowExists" DO
"x" = 'Y';
END
ROLLBACK TO SAVEPOINT "obj__set__SVPNT";
RELEASE SAVEPOINT "obj__set__SVPNT";
UPDATE "obj"
SET "type_id" = :"typeId",
"name" = :"name"
WHERE "id" = :"oldId"
RETURNING "id" INTO: "id";
END
ELSE
DELETE FROM "obj"
WHERE "id" = :"oldId";
SUSPEND;
END^
SET TERM ;^
GRANT ALL ON TABLE "obj" TO PROCEDURE "obj__set";
GRANT EXECUTE ON PROCEDURE "obj__set" TO ROLE USERS;
/*
* Значения атрибутов объектов
*/
CREATE TABLE "attr_value"
(
"attr_id" ID NOT NULL,
"obj_id" ID NOT NULL,
"id" ID NOT NULL,
"value" DOUBLE PRECISION DEFAULT NULL,
CONSTRAINT "attr_value__pk" PRIMARY KEY ("id"),
CONSTRAINT "attr_value__fk__type_attrs" FOREIGN KEY ("attr_id")
REFERENCES "type_attrs" ("id") ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT "attr_value__fk__obj" FOREIGN KEY ("obj_id")
REFERENCES "obj" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);
SET TERM ^;
CREATE OR ALTER TRIGGER "attr_value__TR_BU"
AS
BEGIN
IF (NEW."attr_id" IS NULL) THEN
EXCEPTION "attr_value__xRowExists";
END^
SET TERM ;^
Интересует ваше мнение по поводу данной реализации - чем такой вариант чреват и что я не учел? |
|
|
