[Rapforums-developer] sql rapForumsMSSQL.sql,NONE,1.1

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

Update of /cvsroot/rapforums/sql
In directory sc8-pr-cvs1:/tmp/cvs-serv26824

Added Files:
	rapForumsMSSQL.sql 
Log Message:
no message

--- NEW FILE: rapForumsMSSQL.sql ---
-- RAP FORUMS SQL SCRIPTS
-- This script creates a database called "uPortal_Channels" and creates all tables needed by RAP Forums
--
-- Fullerton College
-- Brad Rippe (br...@fu...)

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'uPortal_Channels')
	DROP DATABASE [uPortal_Channels]
GO

CREATE DATABASE [uPortal_Channels]
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO

exec sp_dboption N'uPortal_Channels', N'autoclose', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'bulkcopy', N'true'
GO

exec sp_dboption N'uPortal_Channels', N'trunc. log', N'true'
GO

exec sp_dboption N'uPortal_Channels', N'torn page detection', N'true'
GO

exec sp_dboption N'uPortal_Channels', N'read only', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'dbo use', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'single', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'autoshrink', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'ANSI null default', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'recursive triggers', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'ANSI nulls', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'concat null yields null', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'cursor close on commit', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'default to local cursor', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'quoted identifier', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'ANSI warnings', N'false'
GO

exec sp_dboption N'uPortal_Channels', N'auto create statistics', N'true'
GO

exec sp_dboption N'uPortal_Channels', N'auto update statistics', N'true'
GO

use [uPortal_Channels]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapBonus]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapBonus]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapCategory]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapCategory]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapCategoryProperties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapCategoryProperties]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapComment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapComment]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapCommentProperties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapCommentProperties]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapForum]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapForum]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapForumProperties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapForumProperties]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapID]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapModerate]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapModerate]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapThread]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapThread]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapThreadProperties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapThreadProperties]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapUserPermissions]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapUserPermissions]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapUserProperties]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapUserProperties]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[rapWatch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[rapWatch]
GO

if not exists (select * from dbo.sysusers where name = N'DBA' and uid < 16382)
	EXEC sp_grantdbaccess N'DBA', N'DBA'
GO

if not exists (select * from dbo.sysusers where name = N'uPortal_CH' and uid < 16382)
	EXEC sp_grantdbaccess N'uPortal_CH', N'uPortal_CH'
GO

exec sp_addrolemember N'db_datareader', N'uPortal_CH'
GO

exec sp_addrolemember N'db_datawriter', N'uPortal_CH'
GO

exec sp_addrolemember N'db_owner', N'uPortal_CH'
GO

CREATE TABLE [dbo].[rapBonus] (
	[userID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[creationDate] [smalldatetime] NULL ,
	[bonusPoints] [int] NULL ,
	[commentID] [int] NULL ,
	[threadID] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapCategory] (
  [categoryID] [int] NOT NULL ,
	[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[creationDate] [smalldatetime] NULL ,
	[modifiedDate] [smalldatetime] NULL ,
	[lft] [int] NULL ,
	[rgt] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapCategoryProperties] (
	[categoryID] [int] NULL ,
	[name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[propValue] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapComment] (
	[commentID] [int] NOT NULL ,
	[parentCommentID] [int] NULL ,
	[threadID] [int] NULL ,
	[forumID] [int] NULL ,
	[userID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[subject] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[body] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[modValue] [int] NULL ,
	[bonusPoints] [int] NULL ,
	[creationDate] [smalldatetime] NULL ,
	[modifiedDate] [smalldatetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapCommentProperties] (
	[commentID] [int] NULL ,
	[name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[propValue] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapForum] (
	[forumID] [int] NOT NULL ,
	[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[creationDate] [smalldatetime] NULL ,
	[modifiedDate] [smalldatetime] NULL ,
	[categoryID] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapForumProperties] (
	[forumID] [int] NULL ,
	[name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[propValue] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapID] (
	[idType] [int] NULL ,
	[id] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapModerate] (
	[objectID] [int] NULL ,
	[objectType] [int] NULL ,
	[userID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[modDate] [smalldatetime] NULL ,
	[modValue] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapThread] (
	[threadID] [int] NOT NULL ,
	[forumID] [int] NOT NULL ,
	[rootCommentID] [int] NULL ,
	[modValue] [int] NULL ,
	[bonusPoints] [int] NULL ,
	[creationDate] [smalldatetime] NULL ,
	[modifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapThreadProperties] (
	[threadID] [int] NULL ,
	[name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[propValue] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapUserPermissions] (
	[categoryID] [int] NULL ,
	[forumID] [int] NULL ,
	[userID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[userType] [int] NULL ,
	[permission] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapUserProperties] (
	[userID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[propValue] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[rapWatch] (
	[userID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[categoryID] [int] NULL ,
	[forumID] [int] NULL ,
	[threadID] [int] NULL ,
	[watchType] [int] NULL ,
	[expirable] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[rapCategory] WITH NOCHECK ADD
	CONSTRAINT [PK_rapCategory] PRIMARY KEY  CLUSTERED
	(
		[categoryID]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[rapComment] WITH NOCHECK ADD
	CONSTRAINT [PK_rapComment] PRIMARY KEY  CLUSTERED
	(
		[commentID]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[rapForum] WITH NOCHECK ADD
	CONSTRAINT [PK_rapForum] PRIMARY KEY  CLUSTERED
	(
		[forumID]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[rapThread] WITH NOCHECK ADD
	CONSTRAINT [PK_rapThread] PRIMARY KEY  CLUSTERED
	(
		[threadID],
		[forumID]
	)  ON [PRIMARY]
GO

ALTER TABLE [dbo].[rapBonus] WITH NOCHECK ADD
	CONSTRAINT [DF_rapBonus_creationDate] DEFAULT (getdate()) FOR [creationDate]
GO

ALTER TABLE [dbo].[rapCategory] WITH NOCHECK ADD
	CONSTRAINT [DF_rapCategory_creationDate] DEFAULT (getdate()) FOR [creationDate],
	CONSTRAINT [DF_rapCategory_modifiedDate] DEFAULT (getdate()) FOR [modifiedDate]
GO

ALTER TABLE [dbo].[rapComment] WITH NOCHECK ADD
	CONSTRAINT [DF_rapComment_creationDate] DEFAULT (getdate()) FOR [creationDate],
	CONSTRAINT [DF_rapComment_modifiedDate] DEFAULT (getdate()) FOR [modifiedDate]
GO

ALTER TABLE [dbo].[rapForum] WITH NOCHECK ADD
	CONSTRAINT [DF_rapForum_creationDate] DEFAULT (getdate()) FOR [creationDate],
	CONSTRAINT [DF_rapForum_modifiedDate] DEFAULT (getdate()) FOR [modifiedDate]
GO

ALTER TABLE [dbo].[rapThread] WITH NOCHECK ADD
	CONSTRAINT [DF_rapThread_bonusPoints] DEFAULT (0) FOR [bonusPoints],
	CONSTRAINT [DF_rapThread_creationDate] DEFAULT (getdate()) FOR [creationDate],
	CONSTRAINT [DF_rapThread_modifiedDate] DEFAULT (getdate()) FOR [modifiedDate]
GO