AhjEBbH8

1USE [master]
2GO
3CREATE DATABASE [WWWConference]
4 CONTAINMENT = NONE
5 ON  PRIMARY
6( NAME = N'WWWConference', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\WWWConference_Data', SIZE =25600KB , MAXSIZE = 102400KB , FILEGROWTH = 10%)
7 LOG ON
8( NAME = N'WWWConference_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\WWWConference_Log' , SIZE =25600KB , MAXSIZE = 51200KB , FILEGROWTH = 20%)
9GO
10ALTER DATABASE [WWWConference] SET COMPATIBILITY_LEVEL = 120
11GO
12IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
13begin
14EXEC [WWWConference].[dbo].[sp_fulltext_database] @action = 'enable'
15end
16GO
17ALTER DATABASE [WWWConference] SET ANSI_NULL_DEFAULT OFF
18GO
19ALTER DATABASE [WWWConference] SET ANSI_NULLS OFF
20GO
21ALTER DATABASE [WWWConference] SET ANSI_PADDING OFF
22GO
23ALTER DATABASE [WWWConference] SET ANSI_WARNINGS OFF
24GO
25ALTER DATABASE [WWWConference] SET ARITHABORT OFF
26GO
27ALTER DATABASE [WWWConference] SET AUTO_CLOSE OFF
28GO
29ALTER DATABASE [WWWConference] SET AUTO_SHRINK OFF
30GO
31ALTER DATABASE [WWWConference] SET AUTO_UPDATE_STATISTICS ON
32GO
33ALTER DATABASE [WWWConference] SET CURSOR_CLOSE_ON_COMMIT OFF
34GO
35ALTER DATABASE [WWWConference] SET CURSOR_DEFAULT  GLOBAL
36GO
37ALTER DATABASE [WWWConference] SET CONCAT_NULL_YIELDS_NULL OFF
38GO
39ALTER DATABASE [WWWConference] SET NUMERIC_ROUNDABORT OFF
40GO
41ALTER DATABASE [WWWConference] SET QUOTED_IDENTIFIER OFF
42GO
43ALTER DATABASE [WWWConference] SET RECURSIVE_TRIGGERS OFF
44GO
45ALTER DATABASE [WWWConference] SET  DISABLE_BROKER
46GO
47ALTER DATABASE [WWWConference] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
48GO
49ALTER DATABASE [WWWConference] SET DATE_CORRELATION_OPTIMIZATION OFF
50GO
51ALTER DATABASE [WWWConference] SET TRUSTWORTHY OFF
52GO
53ALTER DATABASE [WWWConference] SET ALLOW_SNAPSHOT_ISOLATION OFF
54GO
55ALTER DATABASE [WWWConference] SET PARAMETERIZATION SIMPLE
56GO
57ALTER DATABASE [WWWConference] SET READ_COMMITTED_SNAPSHOT OFF
58GO
59ALTER DATABASE [WWWConference] SET HONOR_BROKER_PRIORITY OFF
60GO
61ALTER DATABASE [WWWConference] SET RECOVERY SIMPLE
62GO
63ALTER DATABASE [WWWConference] SET  MULTI_USER
64GO
65ALTER DATABASE [WWWConference] SET PAGE_VERIFY CHECKSUM  
66GO
67ALTER DATABASE [WWWConference] SET DB_CHAINING OFF
68GO
69ALTER DATABASE [WWWConference] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
70GO
71ALTER DATABASE [WWWConference] SET TARGET_RECOVERY_TIME = 0 SECONDS
72GO
73ALTER DATABASE [WWWConference] SET DELAYED_DURABILITY = DISABLED
74GO
75EXEC sys.sp_db_vardecimal_storage_format N'WWWConference', N'ON'
76GO
77 
78USE [WWWConference]
79GO
80 
81 --Create users
82CREATE LOGIN moder WITH PASSWORD = 'moder'
83CREATE USER moder FOR LOGIN moder
84GO
85
86 --Create roles
87CREATE ROLE administrator
88GRANT INSERT, SELECT, UPDATE, DELETE
89    ON messages, users, users_pending_review
90    TO administrator
91EXEC sp_addrolemember 'administrator', 'moder'
92GO
93
94CREATE ROLE www_user
95GRANT INSERT
96    ON messages, users_pending_review
97    TO www_user
98
99GRANT SELECT 
100	ON messages
101    TO www_user
102
103CREATE ROLE guest
104GRANT SELECT
105	ON messages 
106	TO guest
107
108-- Create schemas
109 
110-- Create tables
111SET ANSI_NULLS ON
112GO
113SET QUOTED_IDENTIFIER ON
114GO
115CREATE TABLE messages
116(
117    id INTEGER NOT NULL,
118    topic VARCHAR(255) NOT NULL,
119    text VARCHAR(1000) NOT NULL,
120    date DATE NOT NULL,
121    parent INTEGER,
122    author INTEGER NOT NULL,
123    PRIMARY KEY(id)
124);
125GO
126SET ANSI_NULLS ON
127GO
128SET QUOTED_IDENTIFIER ON
129GO
130CREATE TABLE users
131(
132    id INTEGER NOT NULL,
133    username VARCHAR(255) NOT NULL,
134    password VARCHAR(255) NOT NULL,
135    fullname VARCHAR(255) NOT NULL,
136	birth_date DATE,
137	email VARCHAR (255),
138    PRIMARY KEY(id)
139);
140GO
141SET ANSI_NULLS ON
142GO
143SET QUOTED_IDENTIFIER ON
144GO
145CREATE TABLE users_pending_review
146(
147    primary_key INTEGER NOT NULL,
148    username VARCHAR(255) NOT NULL,
149    password VARCHAR(255) NOT NULL,
150    fullname VARCHAR(255) NOT NULL,
151	birth_date DATE,
152	email VARCHAR (255),
153    PRIMARY KEY(primary_key)
154);
155GO
156 
157-- Create FKs
158ALTER TABLE messages
159    ADD    FOREIGN KEY (author)
160    REFERENCES users(primary_key)
161   --MATCH SIMPLE
162;
163GO    
164ALTER TABLE messages
165    ADD    FOREIGN KEY (parent)
166    REFERENCES messages(id)
167   -- MATCH SIMPLE
168;
169GO  
170 
171-- Create Indexes
172CREATE INDEX full_text ON messages (text, topic, author);
173GO
174
175--Create Views
176CREATE VIEW short_message_review AS
177	SELECT id, topic, author, date
178	FROM messages
179
180--Create functions
181--Функция на добавление сообщения с созданием новой темы. @text - текст сообщения, @topic - тема.
182CREATE FUNCTION new_topic (@text varchar (1000), @topic varchar (256))
183AS
184BEGIN
185	DECLARE @author int
186	SET @author = SELECT FIRST(id) FROM users WHERE username = USER_NAME ()
187	INSERT INTO messages (topic, text, date, author)
188	VALUES (@topic, @text, GETDATE(), @author)	
189END
190GO
191
192--Функция на добавление ответа к уже созданному сообщению. @text - текст сообщения, @parent_id - id сообщения, на который дается ответ
193CREATE FUNCTION reply_message (@text varchar (1000), @parent_id int)
194AS
195BEGIN
196	DECLARE @author int
197	DECLARE @topic varchar (255)
198	SET @author = SELECT FIRST(id) FROM users WHERE username = USER_NAME ()
199    SET @topic = 'RE:' + 
200				(SELECT FIRST(topic) FROM messages WHERE id = @parent_id)
201	INSERT INTO messages (topic, text, date, author, parent)
202	VALUES (@topic, @text, GETDATE(), @author, @parent_id)	
203END
204GO
205 
206--Функция отправки зароса на регистрацию. @username - имя пользователя, @password - пароль. @fullname - полное имя
207--@email - адрес электронной почты, @birth_date - дата рождения. Возвращает 1, если пользователь с таким userame уже есть в таблице, иначе - 0.
208CREATE FUNCTION require_registration (@username varchar (255), @password varchar (255), @fullname varchar (255), @email varchar (255), @birth_date DATE)
209RETURNS int
210AS
211BEGIN
212	IF EXISTS (SELECT * FROM users WHERE username = @username)
213	BEGIN
214		PRINT N'User with such name has already been registered';
215		RETURN 1
216	END
217	ELSE
218	BEGIN
219		INSERT INTO users_pending_review (username, password, fullname, email, birth_date)
220		VALUES (@username, @password, @fullname, @email, @birth_date)
221		RETURN 0
222	END
223END
224GO
225
226
227--Функция для подтверждения регистрации пользователя с id @id из таблицы users_pending_review. Возвращает 0, если успешно, 1 - если неуспешно
228CREATE FUNCTION confirm_registration (@id int)
229RETURNS int
230AS
231BEGIN
232	DECLARE @user_exists int
233	DECLARE @username, @password, @fullname, @email varchar (255)
234	DECLARE @birth_date DATE
235	SET @username = SELECT FIRST (username) FROM users_pending_review WHERE id = @id
236	SET @password = SELECT FIRST (password) FROM users_pending_review WHERE id = @id
237	SET @fullname = SELECT FIRST (fullname) FROM users_pending_review WHERE id = @id
238	SET @birth_date = SELECT FIRST (birth_date) FROM users_pending_review WHERE id = @id
239	IF EXISTS (SELECT * FROM users WHERE username = @username)
240	BEGIN
241		PRINT N'User with such name has already been registered';
242		RETURN 1
243	END
244	ELSE
245	BEGIN
246		CREATE LOGIN @username WITH PASSWORD = @password
247		CREATE USER @username FOR LOGIN @username
248		exec sp_addrolemember 'www_user', username
249		INSERT INTO users (username, password, fullname, email, birth_date)
250		VALUES (@username, @password, @fullname, @email, @birth_date)
251		DELETE FROM users_pending_review
252		WHERE id = @id
253		RETURN 0
254	END
255END
256GO
257
258--Функция, возвращающая текст сообщения по id
259CREATE FUNCTION get_text_message (@id int)
260RETURNS varchar (1000)
261AS
262	RETURN (SELECT text FROM messages WHERE id = @id)
263GO
264
265--Функция, блокирующая доступ пользователя с логином @username к добавлению сообщений
266CREATE FUNCTION block_user (@username varchar (255))
267AS
268DENY INSERT
269	ON messages 
270	TO @username
271GO
272
273--Функция, разблокирующая доступ пользователя с логином @username к добавлению сообщений
274CREATE FUNCTION block_user (@username varchar (255))
275AS
276GRANT INSERT
277	ON messages 
278	TO @username
279GO
280
281--Функция, удаляющая сообщение с номером @id из таблицы
282CREATE FUNCTION delete_message (@id int)
283AS
284	DELETE FROM messages
285	WHERE id = @id
286GO
287
288--Функция поиска по слову в теме
289CREATE FUNCTION search_in_topic (@word varchar (255))
290AS
291	SELECT * FROM messages where topic LIKE '%' + @word + '%'
292GO
293
294
295--Функция поиска по слову в тексте
296CREATE FUNCTION search_in_text (@word varchar (255))
297AS
298	SELECT * FROM messages where text LIKE '%' + @word + '%'
299GO
300
301
302--Функция поиска по автору
303CREATE FUNCTION search_in_author (@word varchar (255))
304AS
305	SELECT * FROM messages where author LIKE '%' + @word + '%'
306GO
307
308
309--create triggers
310CREATE TRIGGER delete_trigger
311ON messages
312AFTER DELETE
313AS
314	DELETE FROM messages
315	WHERE parent_id = (SELECT FIRST(id) FROM deleted)
316GO
317
318
319USE [master]
320GO
321ALTER DATABASE [WWWConference] SET  READ_WRITE
322GO