USE [QuickEye_Union]
GO
/****** Object:  Table [dbo].[users]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[users](
	[userid] [int] IDENTITY(1,1) NOT NULL,
	[username] [nvarchar](15) NOT NULL,
	[usergroupid] [int] NOT NULL,
	[password] [nvarchar](32) NOT NULL,
	[email] [nvarchar](30) NOT NULL,
	[homepage] [nvarchar](50) NULL,
	[joindate] [datetime] NOT NULL,
	[sex] [tinyint] NOT NULL,
	[realname] [nvarchar](10) NULL,
	[address] [nvarchar](50) NULL,
	[qq] [decimal](16, 0) NULL,
	[msn] [nvarchar](30) NULL,
	[avatar] [nvarchar](100) NULL,
	[intro] [text] NULL,
	[tel] [nvarchar](20) NULL,
	[posts] [int] NOT NULL,
	[lastvisit] [datetime] NOT NULL,
	[deposit] [int] NOT NULL,
	[userip] [varchar](15) NULL,
	[ip] [binary](4) NULL,
	[favswitch] [tinyint] NOT NULL,
 CONSTRAINT [PK_book_user] PRIMARY KEY CLUSTERED 
(
	[userid] 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:  StoredProcedure [dbo].[sp_update]    Script Date: 02/15/2012 15:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_update]
    @tbname      varchar(100),   --表名 
    @FieldValue  varchar(1000),   --按该列来进行分页 
    @Where       varchar(1000)=''   --查询条件 
AS
DECLARE @sql nvarchar(4000)
if @Where !=''
	set @Where=' where '+@Where
SET   @sql= 'update '+@tbname+' set '+@FieldValue+@Where
exec (@sql)
GO
/****** Object:  StoredProcedure [dbo].[sp_page]    Script Date: 02/15/2012 15:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   PROCEDURE   [dbo].[sp_page] 
    @tbname            varchar(100),   --表名 
    @FieldShow       varchar(800),--要查询出的字段列表,*表示全部字段 
	@FieldOrder   nvarchar(1000)= ' ',     --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 
    @PageSize      int,                   --每页记录数 
    @PageCurrent          int,                   --指定页 
    @Where     varchar(800)   --查询条件 
AS 
DECLARE   @sql   nvarchar(4000)
if @Where !=''
	set @Where=' where '+@Where
SET   @sql= 'select * from 
	(select row_number() over (order by '+@FieldOrder+') as pos,'+@FieldShow+' from 
	'+@tbname+@Where+') as sp
where pos between '+str((@PageCurrent-1)*@PageSize+1)+' AND '+str(@PageCurrent*@PageSize)

IF   @PageCurrent=1--第一页 
	SET   @sql= 'SELECT TOP '+CAST(@PageSize   AS   varchar)+ '   '+@FieldShow+ ' FROM '+@tbname+' '+ 
        @Where+ ' ORDER BY '+@FieldOrder
EXEC(@sql)
GO
/****** Object:  StoredProcedure [dbo].[sp_getPage]    Script Date: 02/15/2012 15:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_getPage]
	@tbname      varchar(100),   --表名
    @FieldShow    varchar(800),--要查询出的字段列表,*表示全部字段 
	@FieldOrder   nvarchar(1000)= ' ',     --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC 
    @Where        varchar(800)   --查询条件 
AS 
DECLARE   @sql   nvarchar(4000)
SET   @sql='select top 1 row_number() over (order by '+@FieldOrder+
		' ) as pos,'+@FieldShow+' from '+@tbname+' where '+@Where
EXEC(@sql)
GO
/****** Object:  StoredProcedure [dbo].[sp_count]    Script Date: 02/15/2012 15:13:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_count]
    @tbname            varchar(100),   --表名 
    @FieldKey           varchar(50),   --按该列来进行分页 
    @Where     varchar(800)='',--查询条件 
    @PageCount   int   OUTPUT       --总记录数 
AS 
DECLARE   @sql   nvarchar(4000)
if @Where !=''
	set @Where=' where '+@Where
SET   @sql= 'SELECT   @PageCount=COUNT('+@FieldKey+') FROM   '+@tbname+' '+@Where 

EXEC sp_executesql @sql,N'@PageCount  int   OUTPUT',@PageCount OUTPUT  --计算总记录数
GO
/****** Object:  Table [dbo].[site]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[site](
	[siteid] [int] NOT NULL,
	[sitename] [nvarchar](50) NOT NULL,
	[siteurl] [varchar](50) NOT NULL,
	[siteurl2] [varchar](50) NULL,
	[directorypagesiteno] [int] NOT NULL,
	[chapterpagesiteno] [int] NOT NULL,
	[skip] [tinyint] NOT NULL,
	[level] [tinyint] NOT NULL,
	[indir] [tinyint] NOT NULL,
	[ddo] [int] NOT NULL,
 CONSTRAINT [PK_site] PRIMARY KEY CLUSTERED 
(
	[siteid] 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].[setting]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[setting](
	[varname] [nvarchar](50) NOT NULL,
	[content] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
/****** Object:  StoredProcedure [dbo].[GetRecordFromPage]    Script Date: 02/15/2012 15:13:01 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE   PROCEDURE  [dbo].[GetRecordFromPage]   
          @tblName            varchar(255),                --   表名 
          @RetColumns         varchar(1000) = '*',         --   需要返回的列，默认为全部   
          @Orderfld           varchar(255),                --   排序字段名   
          @PageSize           int  =  10,                  --   页尺寸   
          @PageIndex          int  =  1,                   --   页码   
          @IsCount            bit  =  0,                   --   返回记录总数,   非   0   值则返回   
          @OrderType          varchar(50)  =  'asc',       --   设置排序类型,   非   asc   值则降序   
          @strWhere           varchar(1000)  =  ''         --   查询条件   (注意:   不要加   where)   
  AS
      
  declare   @strSQL       varchar(1000)          --   主语句   
  declare   @strTmp       varchar(300)           --   临时变量   
  declare   @strOrder     varchar(400)           --   排序类型   
    
  if  @IsCount  !=   0    --执行总数统计
  
    begin
      if @strWhere != ''   
          set   @strSQL   =   'select   count(*)   as   Total   from   ['   +   @tblName   +   ']  where ' + @strWhere 
      else
          set   @strSQL   =   'select   count(*)   as   Total   from   ['   +   @tblName   +   ']'
    end
    
  else                    --执行查询操作
  
  begin
            
	if  @OrderType  !=   'asc'   
		begin   
				set   @strTmp   =   '<(select   min'   
				set   @strOrder   =   '   order   by   ['   +   @Orderfld   +']   desc'   
		end   
	else   
		begin   
				set  @strTmp   =   '>(select   max'   
				set  @strOrder   =   '   order   by   ['   +   @Orderfld   +']   asc'   
		end   
		
	set  @strSQL   =   'select   top   '   +   str(@PageSize)   +   '   ' + @RetColumns + '    from   ['   
			+   @tblName   +   ']   where   ['   +   @Orderfld   +   ']'   +   @strTmp   +   '(['   
			+   @Orderfld   +   '])   from   (select   top   '   +   str((@PageIndex-1)*@PageSize)   +   '   ['   
			+   @Orderfld   +   ']   from   ['   +   @tblName   +   ']'   +   @strOrder   +   ')   as   tblTmp)'   
			+   @strOrder   
		
	if  @strWhere  !=   ''   
			set   @strSQL   =   'select   top   '   +   str(@PageSize)   +   '   ' + @RetColumns + '   from   ['   
					+   @tblName   +   ']   where   ['   +   @Orderfld   +   ']'   +   @strTmp   +   '(['   
					+   @Orderfld   +   '])   from   (select   top   '   +   str((@PageIndex-1)*@PageSize)   +   '   ['   
					+   @Orderfld   +   ']   from   ['   +   @tblName   +   ']   where   ('   +   @strWhere   +   ')   '   
					+   @strOrder   +   ')   as   tblTmp)   and   ('   +   @strWhere   +   ')   '   +   @strOrder   
		
	if  @PageIndex   =   1   
		begin   
				set   @strTmp   =   ''   
				if   @strWhere   !=   ''   
						set   @strTmp   =   '   where   ('   +   @strWhere   +   ')'   
			
				set   @strSQL   =   'select   top   '   +   str(@PageSize)   +   '   ' + @RetColumns + '   from   ['  		
						+   @tblName   +   ']'   +   @strTmp   +   '   '   +   @strOrder   
		end
 end
   
--print((@strSQL)) 
exec  (@strSQL)
GO
/****** Object:  Table [dbo].[favorite]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[favorite](
	[favoriteid] [int] IDENTITY(1,1) NOT NULL,
	[bookid] [int] NOT NULL,
	[userid] [int] NOT NULL,
 CONSTRAINT [PK_favorite] PRIMARY KEY CLUSTERED 
(
	[favoriteid] 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].[directory]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[directory](
	[directoryid] [int] NOT NULL,
	[bookid] [int] NOT NULL,
	[siteid] [int] NOT NULL,
	[dirurl] [nvarchar](128) NOT NULL,
	[hot] [int] NOT NULL,
 CONSTRAINT [PK_directory] PRIMARY KEY CLUSTERED 
(
	[directoryid] 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].[chapter0]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[chapter0](
	[chapterid] [int] NOT NULL,
	[bookid] [int] NOT NULL,
	[chaptername] [nvarchar](50) NOT NULL,
	[siteid] [int] NOT NULL,
	[url] [nvarchar](200) NOT NULL,
	[updatetime] [datetime] NOT NULL,
 CONSTRAINT [PK_chapter] PRIMARY KEY CLUSTERED 
(
	[chapterid] 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
CREATE NONCLUSTERED INDEX [list] ON [dbo].[chapter0] 
(
	[bookid] ASC,
	[siteid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [uptime] ON [dbo].[chapter0] 
(
	[updatetime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[book_usergroup]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[book_usergroup](
	[usergroupid] [int] NULL,
	[title] [varchar](50) NOT NULL,
	[isadmin] [tinyint] NOT NULL,
	[ismanager] [tinyint] NOT NULL,
	[iswriter] [tinyint] NOT NULL,
	[canaddarticle] [tinyint] NOT NULL,
	[caneditarticle] [tinyint] NOT NULL,
	[canremovearticle] [tinyint] NOT NULL,
	[canaddsort] [tinyint] NOT NULL,
	[caneditsort] [tinyint] NOT NULL,
	[canremovesort] [tinyint] NOT NULL,
	[canaddannouncement] [tinyint] NOT NULL,
	[caneditannouncement] [tinyint] NOT NULL,
	[canremoveannouncement] [tinyint] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[book_setting]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[book_setting](
	[title] [nvarchar](100) NOT NULL,
	[varname] [nvarchar](100) NOT NULL,
	[content] [ntext] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[book]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[book](
	[bookid] [int] NOT NULL,
	[sortid] [int] NOT NULL,
	[bookname] [nvarchar](50) NOT NULL,
	[authorid] [int] NOT NULL,
	[author] [nvarchar](50) NULL,
	[description] [nvarchar](512) NULL,
	[cover] [nvarchar](128) NULL,
	[lastchapterid] [int] NOT NULL,
	[lastchapter] [nvarchar](50) NULL,
	[status] [tinyint] NOT NULL,
	[updatetime] [datetime] NOT NULL,
	[weekviews] [int] NOT NULL,
	[views] [int] NOT NULL,
 CONSTRAINT [PK_book] PRIMARY KEY CLUSTERED 
(
	[bookid] 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
CREATE NONCLUSTERED INDEX [uptime] ON [dbo].[book] 
(
	[updatetime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[begindate]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[begindate](
	[title] [nvarchar](50) NOT NULL,
	[begindate] [datetime] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[author]    Script Date: 02/15/2012 15:13:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[author](
	[authorid] [int] NOT NULL,
	[authorname] [nvarchar](50) NOT NULL,
	[description] [ntext] NULL,
 CONSTRAINT [PK_author] PRIMARY KEY CLUSTERED 
(
	[authorid] 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
/****** Object:  Default [DF_book_sortid]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[book] ADD  CONSTRAINT [DF_book_sortid]  DEFAULT ((0)) FOR [sortid]
GO
/****** Object:  Default [DF_book_authorid]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[book] ADD  CONSTRAINT [DF_book_authorid]  DEFAULT ((1)) FOR [authorid]
GO
/****** Object:  Default [DF_book_lastchapterid]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[book] ADD  CONSTRAINT [DF_book_lastchapterid]  DEFAULT ((0)) FOR [lastchapterid]
GO
/****** Object:  Default [DF_book_status]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[book] ADD  CONSTRAINT [DF_book_status]  DEFAULT ((0)) FOR [status]
GO
/****** Object:  Default [DF_book_updatetime]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[book] ADD  CONSTRAINT [DF_book_updatetime]  DEFAULT (getdate()) FOR [updatetime]
GO
/****** Object:  Default [DF_book_weekviews]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[book] ADD  CONSTRAINT [DF_book_weekviews]  DEFAULT ((0)) FOR [weekviews]
GO
/****** Object:  Default [DF_book_views]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[book] ADD  CONSTRAINT [DF_book_views]  DEFAULT ((0)) FOR [views]
GO
/****** Object:  Default [DF_chapter_updatetime]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[chapter0] ADD  CONSTRAINT [DF_chapter_updatetime]  DEFAULT (getdate()) FOR [updatetime]
GO
/****** Object:  Default [DF_directory_hot]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[directory] ADD  CONSTRAINT [DF_directory_hot]  DEFAULT ((0)) FOR [hot]
GO
/****** Object:  Default [DF_site_directorypagesiteno]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[site] ADD  CONSTRAINT [DF_site_directorypagesiteno]  DEFAULT ((1)) FOR [directorypagesiteno]
GO
/****** Object:  Default [DF_site_chapterpagesiteno]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[site] ADD  CONSTRAINT [DF_site_chapterpagesiteno]  DEFAULT ((1)) FOR [chapterpagesiteno]
GO
/****** Object:  Default [DF_site_skip]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[site] ADD  CONSTRAINT [DF_site_skip]  DEFAULT ((0)) FOR [skip]
GO
/****** Object:  Default [DF_site_leve]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[site] ADD  CONSTRAINT [DF_site_leve]  DEFAULT ((1)) FOR [level]
GO
/****** Object:  Default [DF_site_indir]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[site] ADD  CONSTRAINT [DF_site_indir]  DEFAULT ((0)) FOR [indir]
GO
/****** Object:  Default [DF_site_ddo]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[site] ADD  CONSTRAINT [DF_site_ddo]  DEFAULT ((0)) FOR [ddo]
GO
/****** Object:  Default [DF_book_user_posts]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_book_user_posts]  DEFAULT ((0)) FOR [posts]
GO
/****** Object:  Default [DF_book_user_deposit]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_book_user_deposit]  DEFAULT ((0)) FOR [deposit]
GO
/****** Object:  Default [DF_book_user_favswitch]    Script Date: 02/15/2012 15:13:02 ******/
ALTER TABLE [dbo].[users] ADD  CONSTRAINT [DF_book_user_favswitch]  DEFAULT ((0)) FOR [favswitch]
GO
