Skip to content

Instantly share code, notes, and snippets.

@py4object

py4object/gist:963b99f1e2f6ade435c2

Created December 18, 2015 11:47
Show Gist options
  • Save py4object/963b99f1e2f6ade435c2 to your computer and use it in GitHub Desktop.
Save py4object/963b99f1e2f6ade435c2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.txt
CREATE DATABASE NewsPortal
go
use NewsPortal
go
CREATE SCHEMA content
go
CREATE SCHEMA members
go
CREATE SCHEMA management
go
CREATE TABLE members.Users(
UserID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
UserName nvarchar(30) NOT NULL UNIQUE,
[Password] nvarchar (200) NOT NULL ,
Name NVarChar(20) NOT NULL,
Surname NVARCHAR(20) NOT NULL,
Email NVARCHAR(50) NOT NULL ,
Picture NVARCHAR(50),
[Role] NVARCHAR(10) NOT NULL,
IsActive BIT NOT NULL
)
go
CREATE TABLE members.Authors(
AuthorID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
UserID int Foreign key references members.Users(UserID),
Bio NVARCHAR(300),
TwitterAccount NVARCHAR(50),
FacebookAccount NVARCHAR(50),
)
go
CREATE TABLE members.Readers(
ReaderID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
UserID int Foreign key references members.Users(UserID),
)
go
CREATE TABLE content.MainCategories(
MainID int IDENTITY (1,1) NOT NULL PRIMARY KEY,
Name NVARCHAR(15) UNIQUE NOT NULL,
[Path] NVARCHAR(20) NOT NULL,
)
go
CREATE TABLE content.SubCategories(
SubID INT IDENTITY (1,1) NOT NULL PRIMARY KEY,
Name NVARCHAR(15) UNIQUE NOT NULL,
[Path] NVARCHAR(20) NOT NULL,
MainCategoryID int Foreign KEY references content.MainCategories(MainID),
)
go
CREATE TABLE content.Media(
MediaID int IDENTITY (1,1) NOT NULL PRIMARY KEY,
[Path] NVARCHAR(50) UNIQUE NOT NULL,
[Type] NVARCHAR(10) NOT NULL,
IsDeleted BIT NOT NULL
)
go
CREATE TABLE content.Topics(
TopicID int IDENTITY(1,1)PRIMARY KEY,
Name NVARCHAR(20) UNIQUE NOT NULL,
)
go
CREATE TABLE content.Articles(
ArticleID int IDENTITY (1,1) NOT NULL PRIMARY KEY,
[Date] DATETIME NOT NULL,
[Source] NVARCHAR(20) ,
[Text] NVARCHAR(4000),
CoverPicture int FOREIGN KEY REFERENCES content.Media(MediaID) NOT NULL,
AuthorID int FOREIGN KEY REFERENCES members.Authors(AuthorID),
SubID int FOREIGN KEY REFERENCES content.SubCategories(SubID),
MainTopicID int FOREIGN KEY REFERENCES content.Topics(TopicID),
Title NVARCHAR(20) NOT NULL,
[FileName] NVARCHAR(50) UNIQUE NOT NULL,
IsDeleted BIT NOT NULL
)
GO
CREATE TABLE content.Comments(
CommentID int IDENTITY (1,1) NOT NULL PRIMARY KEY,
Text NVARCHAR(300)NOT NULL,
ArticleID int FOREIGN KEY REFERENCES content.Articles(ArticleID) on delete cascade,
ReaderID int FOREIGN KEY REFERENCES members.Readers(ReaderID),
IsDeleted BIT NOT NULL
)
CREATE TABLE content.Belong_To(
ArticleID int FOREIGN KEY REFERENCES content.Articles(ArticleID),
TopicID int FOREIGN KEY REFERENCES content.Topics(TopicID),
)
go
CREATE TABLE content.INCLUDE(
MediaID int FOREIGN KEY REFERENCES content.Media(MediaID),
ArticleID int FOREIGN KEY REFERENCES content.Articles(ArticleID),
)
go
CREATE TABLE management.Authorized_on(
AuthorID int FOREIGN KEY REFERENCES members.Authors(AuthorID),
SubID int FOREIGN KEY REFERENCES content.SubCategories(SubID),
)
go
CREATE TABLE content.Upvote(
CommentID int FOREIGN KEY REFERENCES content.Comments(CommentID),
ReaderID int FOREIGN KEY REFERENCES members.Readers(ReaderID),
PRIMARY KEY(CommentID,ReaderID)
)
go
CREATE TABLE content.Downvote(
CommentID int FOREIGN KEY REFERENCES content.Comments(CommentID),
ReaderID int FOREIGN KEY REFERENCES members.Readers(ReaderID),
PRIMARY KEY(CommentID,ReaderID)
)
go
CREATE PROCEDURE content.ArticlesByAuthor(@AuthorUserName nvarchar(30))
AS
BEGIN
DECLARE @AuthorID int;
SELECT @AuthorID=MA.AuthorID FROM members.Users AS MU JOIN members.Authors AS MA ON MA.UserID=MU.UserID WHERE MU.UserName=@AuthorUserName
SELECT * from content.Articles where AuthorID=@AuthorID;
END
go
CREATE PROCEDURE content.ArticlesBySubCategories(@SubID int)
AS
BEGIN
SELECT * from content.Articles where SubID=@SubID;
END
go
go
CREATE PROCEDURE content.ArticlesByMainCategories(@MainID INT)
AS
BEGIN
SELECT ArticleID ,[Date],[Source],[Text],AuthorID,cs.SubID,MainTopicID,Title,[FileName]
from content.Articles as CA join content.SubCategories CS on CA.SubID=CS.SubID
join content.MainCategories as CM on cs.MainCategoryID=CM.MainID
where CM.MainID=@MainID
END
go
CREATE PROCEDURE content.ArticlesByMainCategoriesName(@Name AS nvarchar(20))
AS
SELECT ArticleID ,[Date],[Source],[Text],AuthorID,cs.SubID,MainTopicID,Title,[FileName]
from content.Articles as CA join content.SubCategories CS on CA.SubID=CS.SubID
join content.MainCategories as CM on cs.MainCategoryID=CM.MainID
where CM.Name=@Name
go
CREATE PROCEDURE management.AuthorsByAuthorized_onSubCategories(@SubID AS int)
AS
SELECT MA.AuthorID ,Name, Surname, Bio
from members.Authors as MA join members.Users MU on MA.UserID= MU.UserID
join management.Authorized_on MO on MA.AuthorID= MO.AuthorID
where MO.SubID=@SubID
Exec management.AuthorsByAuthorized_onSubCategories @SubID=2
go
CREATE PROCEDURE management.AuthorsByAuthorized_onSubCategoriesName(@Name AS nvarchar(20))
AS
SELECT MA.AuthorID ,MU.Name, Surname, Bio
from members.Authors as MA join members.Users MU on MA.UserID= MU.UserID
join management.Authorized_on MO on MA.AuthorID= MO.AuthorID
join content.SubCategories CS on MO.SubID= CS.SubID
where CS.Name= @Name
exec management.AuthorsByAuthorized_onSubCategoriesName @Name='UK'
go
CREATE PROCEDURE management.AuthorsByAuthorized_onMainCategoriesName(@MainCatName nvarchar(15) )
AS BEGIN
DECLARE @MainId int
SELECT @MainId=MainID FROM content.MainCategories where Name=@MainCatName
SELECT DISTINCT MA.AuthorID ,MU.Name, Surname, Bio
from members.Users as MU join members.Authors as MA on MA.UserID=MU.UserID
join management.Authorized_on as MO on MA.AuthorID=Mo.AuthorID
join content.SubCategories AS CS on CS.SubID=MO.SubID
join content.MainCategories as CM on CM.MainID =CS.MainCategoryID
where CM.MainID=@MainId;
END
go
CREATE PROCEDURE content.ArticlesPathByID(@ArticleID as INT ,@Path as varchar(100) OUTPUT)
AS
select @Path=CM.[Path] +CS.[Path] +'/'+CA.[FileName]
from content.Articles as CA join content.SubCategories as CS on CA.SubID=CS.SubID
JOIN content.MainCategories as CM on CS.MainCategoryID=CM.MainID
where CA.ArticleID=@ArticleID
declare @OUT as nvarchar(100);
exec content.ArticlesPathByID @ArticleID=12, @Path=@OUT OUTPUT;
print @OUT
go
go
CREATE FUNCTION content.MediabyMainCategoryAndType(@MainID as int, @Type as NVARCHAR(20))
RETURNS TABLE
as
Return
(SELECT * from content.media where content.Media.MediaID in
(Select CM.MediaID
from content.Media as CM join content.INCLUDE as CI on CI.MediaID = CM.MediaID
join content.Articles as CA on CA.ArticleID = CI.ArticleID
join content.SubCategories as CS on CS.SubID = CA.SubID
join content.MainCategories as MA on MA.MainID = CS.MainCategoryID
where CM.[Type]= @Type and MA.MainID = @MainID)
or content.Media.MediaID in
(select CME.MediaID
from content.MainCategories as CMA join content.SubCategories as CSU on CSU.MainCategoryID=CMA.MainID
join content.Articles as CAR on CSU.SubID=CAR.SubID
join content.Media as CME on CAR.CoverPicture=CME.MediaID
Where CME.[Type]= @Type and CMA.MainID= @MainID));
go
CREATE VIEW members.AuthorPersonalInfo as
SELECT MU.Name, MU.Surname ,MU.Email,MA.TwitterAccount,MA.FacebookAccount,MA.Bio
FROM members.Users as MU join members.Authors as MA on MA.UserID=MU.UserID;
go
CREATE VIEW members.ActiveReaders as
SELECT Name, Surname, Email
from members.Users as MU join members.Readers as MR on MU.UserID=MR.UserID
join content.Comments as CC on MR.ReaderID=CC.ReaderID
where MU.IsActive=1
GO
CREATE VIEW members.UnActiveReaders as
select Name, Surname, Email
from members.Users as MU join members.Readers as MR on MR.UserID=Mu.UserID
left outer join content.Comments as CM on CM.ReaderID=MR.ReaderID
where CM.CommentID is NULL
GO
CREATE VIEW content.CommentsInfo AS
SELECT
(SELECT MU.UserName FROM members.Readers AS MR JOIN members.Users AS MU ON MU.UserID=MR.UserID WHERE MR.ReaderID=CC.ReaderID ) As [Commented By],
CC.Text,
(SELECT Title FROM content.Articles WHERE ArticleID=CC.ArticleID) AS [On Article],
(SELECT MU.UserName FROM members.Authors AS MA join content.Articles AS CA ON CA.AuthorID=MA.AuthorID
JOIN members.Users AS MU ON MU.UserID=MA.UserID
WHERE CA.ArticleID=CC.ArticleID) AS [Author],
(SELECT COUNT(*) FROM content.Upvote AS CU inner JOIN members.Readers AS MR ON MR.ReaderID=CC.ReaderID
JOIN members.Users AS MU ON MU.UserID=MR.UserID AND MU.IsActive=1
WHERE CU.CommentID=CC.CommentID ) AS [Likes],
(SELECT COUNT(*) FROM content.Downvote AS CU inner join members.Readers AS MR ON MR.ReaderID=CC.ReaderID
JOIN members.Users AS MU ON MU.UserID=MR.UserID AND MU.IsActive=1
WHERE CU.CommentID=CC.CommentID ) AS [Dislikes]
FROM content.Comments AS CC WHERE CC.IsDeleted=0
GO
SELECT * from content.CommentsInfo;
---------
GO
CREATE TRIGGER members.OnDeleteUserTrigger ON members.Users INSTEAD OF DELETE
AS
BEGIN
UPDATE members.Users SET IsActive=0 WHERE UserID in (SELECT UserID FROM deleted);
END
GO
CREATE TRIGGER members.OnDeleteAuthorTrigger ON members.Authors INSTEAD OF DELETE
AS
BEGIN
UPDATE members.Users SET IsActive=0 WHERE UserID in (SELECT UserID FROM deleted);
END
GO
CREATE TRIGGER members.OnDeleteReaderTrigger ON members.Readers INSTEAD OF DELETE
AS
BEGIN
UPDATE members.Users SET IsActive=0 WHERE UserID in (SELECT UserID FROM deleted);
UPDATE content.Comments SET IsDeleted=1 WHERE ReaderID in (SELECT ReaderID FROM members.Readers WHERE UserID IN(SELECT UserId FROM deleted))
END
GO
CREATE TRIGGER content.OnDeleteArticleTrigger ON content.Articles INSTEAD OF DELETE
AS
BEGIN
UPDATE content.Articles SET IsDeleted=1 WHERE ArticleID in (SELECT ArticleID FROM deleted);
UPDATE content.Media SET IsDeleted=1 WHERE MediaID in(
SELECT MediaID from content.INCLUDE AS BT join deleted on BT.ArticleID=deleted.ArticleID WHERE BT.MediaID NOT IN
(select CB.MediaID from content.INCLUDE AS CB join content.Articles AS CA ON CA.ArticleID=CB.ArticleID AND CA.IsDeleted =0
WHERE CB.ArticleID NOT IN (select ArticleID from deleted)))
UPDATE content.Comments SET IsDeleted=1 where ArticleID in(select ArticleID from deleted)
END
GO
CREATE PROCEDURE members.IsAuthenticationInfoValid(@UserName nvarchar (30),@Pass nvarchar (200),@Result bit OUTPUT )
AS
BEGIN
SET @Result=0
IF EXISTS (select userId from members.Users where UserName=@UserName AND [Password]=@Pass)
SET @Result=1
END
GO
CREATE PROCEDURE members.AddNewAuthor (@UserName nvarchar(30),@Pass nvarchar(200),@Name nvarchar(20),@Surname nvarchar(20),@Picture nvarchar(50),@Email nvarchar(50),@Bio nvarchar(300),@TwAc nvarchar(50),@FB nvarchar(50))
AS
BEGIN
DECLARE @UserId int;
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO members.Users VALUES(@UserName,@Pass,@Name,@Surname,@Email,@Picture,'AUTHOR',1)
SET @UserId=SCOPE_IDENTITY()
INSERT INTO members.Authors VALUES(@UserId,@Bio,@TwAc,@FB);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO
CREATE PROCEDURE members.AddNewReader(@UserName nvarchar(30),@Pass nvarchar(200),@Name nvarchar(20),@Surname nvarchar(20),@Picture nvarchar(50),@Email nvarchar(50))
AS
BEGIN
DECLARE @UserId int;
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO members.Users VALUES(@UserName,@Pass,@Name,@Surname,@Email,@Picture,'READER',1)
SET @UserId=SCOPE_IDENTITY()
INSERT INTO members.Readers VALUES(@UserId);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO
CREATE PROCEDURE content.AddNewArticle(@AuthorUserName nvarchar (30),@Date datetime, @Source nvarchar(20),@Text nvarchar(4000),@Title nvarchar(20),@FileName nvarchar(50),@SubName nvarchar(15),@MainTopic nvarchar(20),@CoverPicturePath nvarchar (30))
AS
BEGIN
DECLARE @AtID int ,@MainTopicID int,@SubCatId int;
DECLARE @PicID int;
SELECT @PicID=MediaID FROM content.Media where [path]=@CoverPicturePath
SET @AtID=(SELECT MA.AuthorID
from members.Authors as MA join members.Users AS MU ON MA.UserID=MU.UserID
WHERE MU.UserName=@AuthorUserName)
SET @MainTopic=(SELECT TopicID
from content.Topics where Name=@MainTopic);
SET @SubCatId =(select SubID from content.SubCategories where Name=@SubName)
Insert into content.Articles values (@Date , @Source , @Text,@PicID,@AtID,@SubCatId,@MainTopicID, @Title, @FileName,0)
END
GO
CREATE PROCEDURE content.AddNewTopic (@Name nvarchar(20))
AS INSERT INTO content.Topics VALUES(@Name)
GO
CREATE PROCEDURE content.AddTopicToArticle(@FileName nvarchar(50),@Topic nvarchar(20))
AS
BEGIN
DECLARE @AID int ,@TopicId int;
SELECT @AID=ArticleID FROM content.Articles where [FileName]=@FileName
SELECT @TopicId=TopicID FROM content.Topics where Name=@Topic
INSERT INTO content.Belong_To VALUES(@TopicId,@AID)
END
GO
CREATE PROCEDURE content.AddMediaToArticle(@MediaPath nvarchar(50),@FileName nvarchar(50))
AS
BEGIN
DECLARE @AID int ,@MediaID int
SELECT @AID=ArticleID FROM content.Articles where [FileName]=@FileName
SELECT @MediaID=MediaID FROM content.Media where Media.[Path]=@MediaPath
INSERT INTO content.INCLUDE VALUES(@MediaID,@AID)
END
GO
CREATE PROCEDURE content.AddNewMedia(@Path nvarchar(50),@Type nvarchar(10))
AS
BEGIN
INSERT INTO content.Media VALUES (@Path,@Type,0)
END
GO
CREATE PROCEDURE content.NumberOfUpVotesAndDownVotes (@CommentId int ,@UpVotesCount int OUTPUT ,@DownVotesCount int OUTPUT )
AS
BEGIN
SET @UpVotesCount=0
SET @DownVotesCount=0
SELECT @UpVotesCount= COUNT(*)
from content.Upvote AS UP join members.Readers AS MR ON MR.ReaderID=Up.ReaderID
JOIN members.Users MU ON MU.UserID=MR.UserID WHERE MU.IsActive=1
SELECT @DownVotesCount= COUNT(*)
from content.Downvote AS UP join members.Readers AS MR ON MR.ReaderID=Up.ReaderID
JOIN members.Users MU ON MU.UserID=MR.UserID WHERE MU.IsActive=1
END
GO
CREATE PROCEDURE content.AddNewComment(@Text nvarchar(300),@ArticleFileName nvarchar(50),@ReaderUserName nvarchar(30))
AS
BEGIN
DECLARE @ArticleId int ,@ReaderID int
SELECT @ArticleId=ArticleID FROM content.Articles WHERE FileName=@ArticleFileName
SELECT @ReaderID=MR.ReaderID FROM members.Users AS MU JOIN members.Readers AS MR ON MR.UserID=MU.UserID WHERE MU.UserName=@ReaderUserName
INSERT INTO content.Comments VALUES(@Text,@ArticleId,@ReaderID,0)
END
GO
GO
CREATE PROCEDURE content.AddNewMainCategory(@Name nvarchar(15),@Path nvarchar(20))
AS
BEGIN
INSERT INTO content.MainCategories VALUES(@Name ,@Path)
END
GO
CREATE PROCEDURE content.AddNewSubCaregory(@MainCategory nvarchar(15),@Name nvarchar(15),@Path nvarchar(20))
AS
BEGIN
INSERT INTO content.SubCategories VALUES(@Name,@Path,(select MainID FROM content.MainCategories WHERE Name=@MainCategory))
END
GO
CREATE PROCEDURE content.UpVoteComment(@CommentID int ,@ReaderUserName nvarchar(30))
AS
BEGIN
INSERT INTO content.Upvote VALUES(@CommentID,(SELECT MR.ReaderID from members.Users AS MU JOIN members.Readers AS MR ON MU.UserID=MR.UserID WHERE MU.UserName=@ReaderUserName))
END
GO
CREATE PROCEDURE content.DownVoteComment(@CommentID int ,@ReaderUserName nvarchar(30))
AS
BEGIN
INSERT INTO content.Downvote VALUES(@CommentID,(SELECT MR.ReaderID from members.Users AS MU JOIN members.Readers AS MR ON MU.UserID=MR.UserID WHERE MU.UserName=@ReaderUserName))
END
GO
CREATE TRIGGER content.OnUpVoteComment ON content.Upvote AFTER INSERT
AS
BEGIN
IF EXISTS (SELECT * FROM content.Downvote WHERE ReaderID in(select ReaderID from inserted) AND CommentID in(select CommentID FROM inserted) )
DELETE content.Downvote WHERE ReaderID in(select ReaderID from inserted) AND CommentID in(select CommentID FROM inserted)
END
GO
CREATE TRIGGER content.DownUpVoteComment ON content.Downvote AFTER INSERT
AS
BEGIN
IF EXISTS (SELECT * FROM content.Downvote WHERE ReaderID in(select ReaderID from inserted) AND CommentID in(select CommentID FROM inserted) )
DELETE content.Upvote WHERE ReaderID in(select ReaderID from inserted) AND CommentID in(select CommentID FROM inserted)
END
GO
CREATE VIEW content.MainCatgoriesInfo
AS
SELECT CM.Name,CM.[Path],
(SELECT COUNT(*) from content.SubCategories WHERE MainCategoryID=CM.MainID) AS [Number of subCategories],
(SELECT COUNT (*) from content.SubCategories AS CS join content.Articles AS CA ON CA.SubID=CS.SubID WHERE CS.MainCategoryID=CM.MainID) AS [Number of Articles],
(SELECT COUNT (*) FROM management.Authorized_on AS MA JOIN content.SubCategories AS CS ON CS.SubID=MA.SubID WHERE CS.MainCategoryID=CM.MainID) AS[Number Of Authorized Authors],
(SELECT COUNT (*) FROM content.Comments AS CC JOIN content.Articles AS CA ON CA.ArticleID=CC.ArticleID
JOIN content.SubCategories AS CS ON CS.SubID=CA.SubID join members.Readers AS MR ON CC.ReaderID=MR.ReaderID join members.Users AS MU ON MU.UserID=MR.UserID WHERE MU.IsActive=1 AND CS.MainCategoryID=CM.MainID) AS[Number of comments]
FROM content.MainCategories AS CM
GO
CREATE PROCEDURE management.AuthorizeAuthorOnSubCatageory(@AuthorUserName nvarchar(30),@SubName nvarchar(15))
AS
BEGIN
INSERT INTO management.Authorized_on VALUES((SELECT Mu.UserID FROM members.Users AS MU JOIN members.Authors AS MA ON MU.UserID=MA.UserID WHERE Mu.UserName=@AuthorUserName ),(SELECT SubID FROM content.SubCategories WHERE Name=@SubName))
END
GO
exec content.AddNewMainCategory 'Poltics','/potlics'
exec content.AddNewSubCaregory 'Poltics','Turkey','/Turkey'
exec members.AddNewAuthor 'a1','aads','Bo','Fo','pic.jpg','12@w.com','ads',NULL,NULL
exec content.AddNewMedia '/pic.jpg','pic'
exec content.AddNewTopic 'Turkey'
exec content.AddNewArticle 'a1','12-12-2012 1:0:0',NULL,'asdasd','War on ISIS','war-on-isis.html','Turkey','Turkey','/pic.jpg'
exec members.AddNewReader 'ax','asda','REader','ASD','1.pic','asdasd@1.com'
exec content.AddNewComment 'adsasdasd','war-on-isis.html','py'
exec content.DownVoteComment 2,'az'
exec members.AddNewReader 'az','asd','boo','moo','asd.jpg','123@1.com'
exec content.UpVoteComment 1,'az'
exec management.AuthorizeAuthorOnSubCatageory 'a1','Turkey'
delete members.Readers where ReaderID=1
select * from content.CommentsInfo
select * from content.MainCatgoriesInfo
exec content.AddNewComment 'asdasd','war-on-isis.html','az'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment