b5ncqeJN

1USE Lab3DB;
2GO
3
4IF OBJECT_ID('dbo.AttrValues', 'U') IS NOT NULL 
5  DROP TABLE dbo.AttrValues; 
6
7IF OBJECT_ID('dbo.Entities', 'U') IS NOT NULL 
8  DROP TABLE dbo.Entities; 
9    
10IF OBJECT_ID('dbo.Attributes', 'U') IS NOT NULL 
11  DROP TABLE dbo.Attributes; 
12
13 CREATE TABLE Entities(
14	id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
15	name NVARCHAR(200) UNIQUE NOT NULL,
16	ent_description text);
17GO
18
19CREATE TABLE Attributes(
20	id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
21	name NVARCHAR(200) UNIQUE NOT NULL,
22	attr_description text);
23GO
24
25CREATE TABLE AttrValues(
26	id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
27	ent_id BIGINT NOT NULL REFERENCES Entities(id),
28	attr_id BIGINT NOT NULL REFERENCES Attributes(id),
29	value NVARCHAR(200) NOT NULL);
30GO
31
32--Procedures
33
34
35--getAll
36IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'getAll')
37	DROP PROCEDURE getAll
38GO
39
40CREATE PROCEDURE getAll
41	AS SELECT Entities.*, Attributes.*, AttrValues.*
42	FROM Entities, Attributes, AttrValues
43GO
44
45
46--getEntity
47IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'getEntity')
48	DROP PROCEDURE getEntity
49GO
50
51CREATE PROCEDURE getEntity @id BIGINT 
52AS SELECT Entities.*, Attributes.name, Attributes.attr_description, AttrValues.value
53    FROM AttrValues
54        JOIN Entities ON AttrValues.ent_id = Entities.id
55        JOIN Attributes ON AttrValues.attr_id = Attributes.id
56	WHERE Entities.id = @id;
57GO
58
59--getByCondition
60IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'getByCondition')
61	DROP PROCEDURE getByCondition
62GO
63
64CREATE PROCEDURE getByCondition @attr_name NVARCHAR(200), @attr_value NVARCHAR(200)
65	AS SELECT Entities.*, Attributes.*, AttrValues.* 
66	FROM Entities, Attributes, AttrValues
67	WHERE Attributes.name = @attr_name AND AttrValues.value = @attr_value
68GO
69
70--addOneAttrEntity
71IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'addOneAttrEntity')
72	DROP PROCEDURE addOneAttrEntity
73GO
74
75CREATE PROCEDURE addOneAttrEntity @name NVARCHAR(200), @ent_descript text, 
76	@attr_name NVARCHAR(200), @attr_descript text, @attr_value NVARCHAR(200)
77	AS BEGIN
78		INSERT INTO Entities(name, ent_description) VALUES (@name, @ent_descript)
79		IF NOT EXISTS (SELECT TOP 1 name FROM Attributes WHERE name = @attr_name)
80			INSERT INTO Attributes VALUES (@attr_name, @attr_descript);
81		INSERT INTO AttrValues(ent_id, attr_id, value) VALUES (
82			(SELECT Entities.id FROM Entities WHERE name = @name),
83			(SELECT Attributes.id FROM Attributes WHERE name = @attr_name),
84			@attr_value)
85	END
86GO
87
88--there won't be adding of standalone value, it's impossible
89--add Attribute to Entity. 
90IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'addAttribute')
91	DROP PROCEDURE addAttribute
92GO
93
94CREATE PROCEDURE addAttribute @ent_id BIGINT, @attr_name NVARCHAR(200), @attr_descript text, @attr_value NVARCHAR(200)
95	AS BEGIN
96		IF NOT EXISTS (SELECT TOP 1 name FROM Attributes WHERE name = @attr_name)
97			INSERT INTO Attributes(name, attr_description) VALUES(@attr_name, @attr_descript);
98		INSERT AttrValues(ent_id, attr_id, value) VALUES(@ent_id, 
99		(SELECT Attributes.id FROM Attributes WHERE name = @attr_name),
100		@attr_value)
101	END
102GO
103
104
105
106--get all attributes
107IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'getAllAttributes')
108	DROP PROCEDURE getAllAttributes
109GO
110
111CREATE PROCEDURE getAllAttributes
112	AS SELECT * FROM Attributes;
113GO
114
115--Testing
116
117EXEC addOneAttrEntity 'Утро на море', 'Известная картина, написанная в 1883 году.', 'author', 
118	'Автор картины.', 'И.К. Айвазовский';
119GO
120
121EXEC getEntity 1;
122GO
123
124EXEC addOneAttrEntity 'Бурлаки на Волге', 'Первая значит. картина художника, написанная в 1873 году.', 'author', 
125	'Автор картины.', 'И.Е. Репин';
126GO
127
128EXEC getAll;
129GO
130
131EXEC addAttribute 1, 'subject', 'Тематика картины.', 'Море.';
132GO
133
134EXEC getAllAttributes;
135GO
136
137EXEC getEntity 1;
138GO