View difference between Paste ID: xmP16Tz3 and AhjEBbH8

 USE [master]
 GO
 CREATE DATABASE [WWWConference]
  CONTAINMENT = NONE
  ON  PRIMARY
 ( NAME = N'WWWConference', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\WWWConference_Data', SIZE =25600KB , MAXSIZE = 102400KB , FILEGROWTH = 10%)
  LOG ON
 ( NAME = N'WWWConference_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\WWWConference_Log' , SIZE =25600KB , MAXSIZE = 51200KB , FILEGROWTH = 20%)
 GO
 ALTER DATABASE [WWWConference] SET COMPATIBILITY_LEVEL = 120
 GO
 IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
 begin
 EXEC [WWWConference].[dbo].[sp_fulltext_database] @action = 'enable'
 end
 GO
 ALTER DATABASE [WWWConference] SET ANSI_NULL_DEFAULT OFF
 GO
 ALTER DATABASE [WWWConference] SET ANSI_NULLS OFF
 GO
 ALTER DATABASE [WWWConference] SET ANSI_PADDING OFF
 GO
 ALTER DATABASE [WWWConference] SET ANSI_WARNINGS OFF
 GO
 ALTER DATABASE [WWWConference] SET ARITHABORT OFF
 GO
 ALTER DATABASE [WWWConference] SET AUTO_CLOSE OFF
 GO
 ALTER DATABASE [WWWConference] SET AUTO_SHRINK OFF
 GO
 ALTER DATABASE [WWWConference] SET AUTO_UPDATE_STATISTICS ON
 GO
 ALTER DATABASE [WWWConference] SET CURSOR_CLOSE_ON_COMMIT OFF
 GO
 ALTER DATABASE [WWWConference] SET CURSOR_DEFAULT  GLOBAL
 GO
 ALTER DATABASE [WWWConference] SET CONCAT_NULL_YIELDS_NULL OFF
 GO
 ALTER DATABASE [WWWConference] SET NUMERIC_ROUNDABORT OFF
 GO
 ALTER DATABASE [WWWConference] SET QUOTED_IDENTIFIER OFF
 GO
 ALTER DATABASE [WWWConference] SET RECURSIVE_TRIGGERS OFF
 GO
 ALTER DATABASE [WWWConference] SET  DISABLE_BROKER
 GO
 ALTER DATABASE [WWWConference] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
 GO
 ALTER DATABASE [WWWConference] SET DATE_CORRELATION_OPTIMIZATION OFF
 GO
 ALTER DATABASE [WWWConference] SET TRUSTWORTHY OFF
 GO
 ALTER DATABASE [WWWConference] SET ALLOW_SNAPSHOT_ISOLATION OFF
 GO
 ALTER DATABASE [WWWConference] SET PARAMETERIZATION SIMPLE
 GO
 ALTER DATABASE [WWWConference] SET READ_COMMITTED_SNAPSHOT OFF
 GO
 ALTER DATABASE [WWWConference] SET HONOR_BROKER_PRIORITY OFF
 GO
 ALTER DATABASE [WWWConference] SET RECOVERY SIMPLE
 GO
 ALTER DATABASE [WWWConference] SET  MULTI_USER
 GO
 ALTER DATABASE [WWWConference] SET PAGE_VERIFY CHECKSUM
 GO
 ALTER DATABASE [WWWConference] SET DB_CHAINING OFF
 GO
 ALTER DATABASE [WWWConference] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
 GO
 ALTER DATABASE [WWWConference] SET TARGET_RECOVERY_TIME = 0 SECONDS
 GO
 ALTER DATABASE [WWWConference] SET DELAYED_DURABILITY = DISABLED
 GO
 EXEC sys.sp_db_vardecimal_storage_format N'WWWConference', N'ON'
 GO
 
 USE [WWWConference]
 GO
 
  --Create users
 CREATE LOGIN moder WITH PASSWORD = 'moder'
 CREATE USER moder FOR LOGIN moder
 GO
 
  --Create roles
 CREATE ROLE administrator
 GRANT INSERT, SELECT, UPDATE, DELETE
     ON messages, users, users_pending_review
     TO administrator
 EXEC sp_addrolemember 'administrator', 'moder'
 GO
 
 CREATE ROLE www_user
 GRANT INSERT
     ON messages, users_pending_review
     TO www_user
 
 GRANT SELECT
 	ON messages
     TO www_user
 
 CREATE ROLE guest
 GRANT SELECT
 	ON messages
 	TO guest
 
 -- Create schemas
 
 -- Create tables
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE messages
 (
     id INTEGER NOT NULL,
     topic VARCHAR(255) NOT NULL,
     text VARCHAR(1000) NOT NULL,
     date DATE NOT NULL,
     parent INTEGER,
     author INTEGER NOT NULL,
     PRIMARY KEY(id)
 );
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE users
 (
     id INTEGER NOT NULL,
     username VARCHAR(255) NOT NULL,
     password VARCHAR(255) NOT NULL,
     fullname VARCHAR(255) NOT NULL,
 	birth_date DATE,
 	email VARCHAR (255),
     PRIMARY KEY(id)
 );
 GO
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE TABLE users_pending_review
 (
     primary_key INTEGER NOT NULL,
     username VARCHAR(255) NOT NULL,
     password VARCHAR(255) NOT NULL,
     fullname VARCHAR(255) NOT NULL,
 	birth_date DATE,
 	email VARCHAR (255),
     PRIMARY KEY(primary_key)
 );
 GO
 
 -- Create FKs
 ALTER TABLE messages
     ADD    FOREIGN KEY (author)
     REFERENCES users(primary_key)
    --MATCH SIMPLE
 ;
 GO
 ALTER TABLE messages
     ADD    FOREIGN KEY (parent)
     REFERENCES messages(id)
    -- MATCH SIMPLE
 ;
 GO
 
 -- Create Indexes
 CREATE INDEX full_text ON messages (text, topic, author);
 GO
 
 --Create Views
 CREATE VIEW short_message_review AS
 	SELECT id, topic, author, date
 	FROM messages
 
 --Create functions
 --Функция на добавление сообщения с созданием новой темы. @text - текст сообщения, @topic - тема.
 CREATE FUNCTION new_topic (@text varchar (1000), @topic varchar (256))
 AS
 BEGIN
 	DECLARE @author int
 	SET @author = SELECT FIRST(id) FROM users WHERE username = USER_NAME ()
 	INSERT INTO messages (topic, text, date, author)
 	VALUES (@topic, @text, GETDATE(), @author)
 END
 GO
 
 --Функция на добавление ответа к уже созданному сообщению. @text - текст сообщения, @parent_id - id сообщения, на который дается ответ
 CREATE FUNCTION reply_message (@text varchar (1000), @parent_id int)
 AS
 BEGIN
 	DECLARE @author int
 	DECLARE @topic varchar (255)
 	SET @author = SELECT FIRST(id) FROM users WHERE username = USER_NAME ()
     SET @topic = 'RE:' +
 				(SELECT FIRST(topic) FROM messages WHERE id = @parent_id)
 	INSERT INTO messages (topic, text, date, author, parent)
 	VALUES (@topic, @text, GETDATE(), @author, @parent_id)
 END
 GO
 
 --Функция отправки зароса на регистрацию. @username - имя пользователя, @password - пароль. @fullname - полное имя
 --@email - адрес электронной почты, @birth_date - дата рождения. Возвращает 1, если пользователь с таким userame уже есть в таблице, иначе - 0.
 CREATE FUNCTION require_registration (@username varchar (255), @password varchar (255), @fullname varchar (255), @email varchar (255), @birth_date DATE)
 RETURNS int
 AS
 BEGIN
 	IF EXISTS (SELECT * FROM users WHERE username = @username)
 	BEGIN
 		PRINT N'User with such name has already been registered';
 		RETURN 1
 	END
 	ELSE
 	BEGIN
 		INSERT INTO users_pending_review (username, password, fullname, email, birth_date)
 		VALUES (@username, @password, @fullname, @email, @birth_date)
 		RETURN 0
 	END
 END
 GO
 
 
 --Функция для подтверждения регистрации пользователя с id @id из таблицы users_pending_review. Возвращает 0, если успешно, 1 - если неуспешно
 CREATE FUNCTION confirm_registration (@id int)
 RETURNS int
 AS
 BEGIN
 	DECLARE @user_exists int
 	DECLARE @username, @password, @fullname, @email varchar (255)
 	DECLARE @birth_date DATE
 	SET @username = SELECT FIRST (username) FROM users_pending_review WHERE id = @id
 	SET @password = SELECT FIRST (password) FROM users_pending_review WHERE id = @id
 	SET @fullname = SELECT FIRST (fullname) FROM users_pending_review WHERE id = @id
 	SET @birth_date = SELECT FIRST (birth_date) FROM users_pending_review WHERE id = @id
 	IF EXISTS (SELECT * FROM users WHERE username = @username)
 	BEGIN
 		PRINT N'User with such name has already been registered';
 		RETURN 1
 	END
 	ELSE
 	BEGIN
 		CREATE LOGIN @username WITH PASSWORD = @password
 		CREATE USER @username FOR LOGIN @username
 		exec sp_addrolemember 'www_user', username
 		INSERT INTO users (username, password, fullname, email, birth_date)
 		VALUES (@username, @password, @fullname, @email, @birth_date)
 		DELETE FROM users_pending_review
 		WHERE id = @id
 		RETURN 0
 	END
 END
 GO
 
 --Функция, возвращающая текст сообщения по id
 CREATE FUNCTION get_text_message (@id int)
 RETURNS varchar (1000)
 AS
 	RETURN (SELECT text FROM messages WHERE id = @id)
 GO
 
 --Функция, блокирующая доступ пользователя с логином @username к добавлению сообщений
 CREATE FUNCTION block_user (@username varchar (255))
 AS
 DENY INSERT
 	ON messages
 	TO @username
 GO
 
 --Функция, разблокирующая доступ пользователя с логином @username к добавлению сообщений
 CREATE FUNCTION block_user (@username varchar (255))
 AS
 GRANT INSERT
 	ON messages
 	TO @username
 GO
 
 --Функция, удаляющая сообщение с номером @id из таблицы
 CREATE FUNCTION delete_message (@id int)
 AS
 	DELETE FROM messages
 	WHERE id = @id
 GO
 
 --Функция поиска по слову в теме
 CREATE FUNCTION search_in_topic (@word varchar (255))
 AS
 	SELECT * FROM messages where topic LIKE '%' + @word + '%'
 GO
 
 
 --Функция поиска по слову в тексте
 CREATE FUNCTION search_in_text (@word varchar (255))
 AS
 	SELECT * FROM messages where text LIKE '%' + @word + '%'
 GO
 
 
 --Функция поиска по автору
 CREATE FUNCTION search_in_author (@word varchar (255))
 AS
 	SELECT * FROM messages where author LIKE '%' + @word + '%'
 GO
 
 --create procedures
 --Процедура вывода списка сообщений
 CREATE PROCEDURE review_messages
 AS
 	SELECT * FROM short_messages_review
 	GROUP BY parent_id
 GO
 
 
 --create triggers
 CREATE TRIGGER delete_trigger
 ON messages
 AFTER DELETE
 AS
 	DELETE FROM messages
 	WHERE parent_id = (SELECT FIRST(id) FROM deleted)
 GO
 
 
 USE [master]
 GO
 ALTER DATABASE [WWWConference] SET  READ_WRITE
 GO