sample-db-script.sql

USE [master]
GO
/****** Object:  Database [HEDIYE]    Script Date: 18.08.2020 11:12:09 ******/
CREATE DATABASE [HEDIYE]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'SolidOrnek', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\HEDIYE.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'SolidOrnek_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\HEDIYE_log.ldf' , SIZE = 4224KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [HEDIYE] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [HEDIYE].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [HEDIYE] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [HEDIYE] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [HEDIYE] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [HEDIYE] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [HEDIYE] SET ARITHABORT OFF 
GO
ALTER DATABASE [HEDIYE] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [HEDIYE] SET AUTO_CREATE_STATISTICS ON 
GO
ALTER DATABASE [HEDIYE] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [HEDIYE] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [HEDIYE] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [HEDIYE] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [HEDIYE] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [HEDIYE] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [HEDIYE] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [HEDIYE] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [HEDIYE] SET  DISABLE_BROKER 
GO
ALTER DATABASE [HEDIYE] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [HEDIYE] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [HEDIYE] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [HEDIYE] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [HEDIYE] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [HEDIYE] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [HEDIYE] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [HEDIYE] SET RECOVERY FULL 
GO
ALTER DATABASE [HEDIYE] SET  MULTI_USER 
GO
ALTER DATABASE [HEDIYE] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [HEDIYE] SET DB_CHAINING OFF 
GO
ALTER DATABASE [HEDIYE] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [HEDIYE] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO
EXEC sys.sp_db_vardecimal_storage_format N'HEDIYE', N'ON'
GO
USE [HEDIYE]
GO
/****** Object:  Table [dbo].[Category]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Category](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ParentCategoryId] [int] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Summary] [nvarchar](200) NULL,
	[Type] [varchar](50) NOT NULL,
	[IsActive] [bit] NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Contact]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Contact](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](200) NOT NULL,
	[Email] [varchar](200) NULL,
	[Phone] [varchar](100) NULL,
	[Subject] [varchar](200) NULL,
	[Message] [text] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Menu]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Menu](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ParentMenuId] [int] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Href] [nvarchar](200) NULL,
	[OrderNo] [int] NULL,
	[Type] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Newsletter]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Newsletter](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Email] [nvarchar](100) NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_Newsletter] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Param]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Param](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[Description] [varchar](1000) NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_Param] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Post]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Post](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](255) NOT NULL,
	[Summary] [varchar](1000) NULL,
	[FullContent] [text] NULL,
	[UserId] [int] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
	[Type] [varchar](50) NOT NULL,
	[IsActive] [bit] NOT NULL,
	[ImagePath] [varchar](100) NULL,
	[ReadCount] [int] NOT NULL,
	[LikeCount] [int] NOT NULL,
	[UnLikeCount] [int] NOT NULL,
	[MetaTitle] [nvarchar](200) NULL,
	[MetaKeyword] [nvarchar](200) NULL,
	[MetaDescription] [nvarchar](300) NULL,
 CONSTRAINT [PK_POST] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PostCategory]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PostCategory](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PostId] [int] NOT NULL,
	[CategoryId] [int] NOT NULL,
 CONSTRAINT [PK_PostCategory] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PostComment]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PostComment](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PostId] [int] NOT NULL,
	[Email] [varchar](100) NOT NULL,
	[Name] [varchar](100) NOT NULL,
	[Web] [varchar](100) NULL,
	[Comment] [text] NOT NULL,
	[IsActive] [bit] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_PostComment] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PostDetail]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PostDetail](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PostId] [int] NOT NULL,
	[ReadCount] [int] NOT NULL,
	[LikeCount] [int] NULL,
	[UnLikeCount] [int] NULL,
	[MetaTitle] [nvarchar](100) NULL,
	[MetaKeyword] [nvarchar](100) NULL,
	[MetaDescription] [nvarchar](200) NULL,
 CONSTRAINT [PK_PostDetail] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[User]    Script Date: 18.08.2020 11:12:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[User](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Password] [nvarchar](50) NOT NULL,
	[FullName] [nvarchar](200) NOT NULL,
	[Description] [text] NULL,
	[Email] [varchar](100) NOT NULL,
	[Web] [varchar](100) NULL,
	[Phone] [varchar](50) NULL,
	[Address] [varchar](1000) NULL,
	[CityCode] [int] NULL,
	[CountyCode] [int] NULL,
	[MapsUrl] [varchar](500) NULL,
	[ImageUrl] [varchar](100) NULL,
	[Type] [varchar](50) NOT NULL,
	[IsActive] [bit] NOT NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Category] ADD  CONSTRAINT [DF_Category_Type]  DEFAULT ('category') FOR [Type]
GO
ALTER TABLE [dbo].[Category] ADD  CONSTRAINT [DF_Category_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO
ALTER TABLE [dbo].[Contact] ADD  CONSTRAINT [DF_Contact_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[Menu] ADD  CONSTRAINT [DF_Menu_Type]  DEFAULT ('main') FOR [Type]
GO
ALTER TABLE [dbo].[Newsletter] ADD  CONSTRAINT [DF_Newsletter_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[Newsletter] ADD  CONSTRAINT [DF_Newsletter_IsActive]  DEFAULT ((1)) FOR [IsActive]
GO
ALTER TABLE [dbo].[Post] ADD  CONSTRAINT [DF_POST_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[Post] ADD  CONSTRAINT [DF_Post_Type]  DEFAULT ('post') FOR [Type]
GO
ALTER TABLE [dbo].[Post] ADD  CONSTRAINT [DF_Post_IsActive]  DEFAULT ((0)) FOR [IsActive]
GO
ALTER TABLE [dbo].[Post] ADD  CONSTRAINT [DF_Post_ReadCount]  DEFAULT ((0)) FOR [ReadCount]
GO
ALTER TABLE [dbo].[Post] ADD  CONSTRAINT [DF_Post_LikeCount]  DEFAULT ((0)) FOR [LikeCount]
GO
ALTER TABLE [dbo].[Post] ADD  CONSTRAINT [DF_Post_UnLikeCount]  DEFAULT ((0)) FOR [UnLikeCount]
GO
ALTER TABLE [dbo].[PostComment] ADD  CONSTRAINT [DF_PostComment_IsActive]  DEFAULT ((0)) FOR [IsActive]
GO
ALTER TABLE [dbo].[PostComment] ADD  CONSTRAINT [DF_Comment_CreateDate]  DEFAULT (getdate()) FOR [CreateDate]
GO
ALTER TABLE [dbo].[PostDetail] ADD  CONSTRAINT [DF_PostDetail_ReadCount]  DEFAULT ((0)) FOR [ReadCount]
GO
ALTER TABLE [dbo].[PostDetail] ADD  CONSTRAINT [DF_PostDetail_LikeCount]  DEFAULT ((0)) FOR [LikeCount]
GO
ALTER TABLE [dbo].[PostDetail] ADD  CONSTRAINT [DF_PostDetail_UnLikeCount]  DEFAULT ((0)) FOR [UnLikeCount]
GO
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_Type]  DEFAULT ('author') FOR [Type]
GO
ALTER TABLE [dbo].[User] ADD  CONSTRAINT [DF_User_IsActive]  DEFAULT ((0)) FOR [IsActive]
GO
ALTER TABLE [dbo].[Post]  WITH CHECK ADD  CONSTRAINT [FK_Post_User] FOREIGN KEY([UserId])
REFERENCES [dbo].[User] ([Id])
GO
ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [FK_Post_User]
GO
ALTER TABLE [dbo].[PostCategory]  WITH CHECK ADD  CONSTRAINT [FK_PostCategory_Category] FOREIGN KEY([CategoryId])
REFERENCES [dbo].[Category] ([Id])
GO
ALTER TABLE [dbo].[PostCategory] CHECK CONSTRAINT [FK_PostCategory_Category]
GO
ALTER TABLE [dbo].[PostCategory]  WITH CHECK ADD  CONSTRAINT [FK_PostCategory_Post] FOREIGN KEY([PostId])
REFERENCES [dbo].[Post] ([Id])
GO
ALTER TABLE [dbo].[PostCategory] CHECK CONSTRAINT [FK_PostCategory_Post]
GO
ALTER TABLE [dbo].[PostComment]  WITH CHECK ADD  CONSTRAINT [FK_PostComment_Post] FOREIGN KEY([PostId])
REFERENCES [dbo].[Post] ([Id])
GO
ALTER TABLE [dbo].[PostComment] CHECK CONSTRAINT [FK_PostComment_Post]
GO
ALTER TABLE [dbo].[PostDetail]  WITH CHECK ADD  CONSTRAINT [FK_PostDetail_Post] FOREIGN KEY([PostId])
REFERENCES [dbo].[Post] ([Id])
GO
ALTER TABLE [dbo].[PostDetail] CHECK CONSTRAINT [FK_PostDetail_Post]
GO
USE [master]
GO
ALTER DATABASE [HEDIYE] SET  READ_WRITE 
GO