root/trunk/website/forums/installation/sqlserver.sql @ 30

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

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

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

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

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

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

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

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

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

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

CREATE TABLE [dbo].[galleon_conferences] (
        [id] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [active] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[galleon_forums] (
        [id] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [description] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [readonly] [bit] NOT NULL ,
        [active] [bit] NOT NULL ,
        [attachments] [bit] NOT NULL ,
        [conferenceidfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[galleon_groups] (
        [id] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [group] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[galleon_messages] (
        [id] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [title] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [body] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [posted] [datetime] NOT NULL ,
        [useridfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [threadidfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [attachment] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [filename] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[galleon_ranks] (
        [id] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [minposts] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[galleon_search_log] (
        [searchterms] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [datesearched] [datetime] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[galleon_subscriptions] (
        [id] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [useridfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [threadidfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [forumidfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
        [conferenceidfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[galleon_threads] (
        [id] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [name] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [readonly] [bit] NOT NULL ,
        [useridfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [forumidfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [datecreated] [datetime] NOT NULL ,
        [active] [bit] NOT NULL ,
        [sticky] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[galleon_users] (
        [id] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [username] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [emailaddress] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [signature] [nvarchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [datecreated] [datetime] NOT NULL ,
        [confirmed] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[galleon_users_groups] (
        [useridfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
        [groupidfk] [nvarchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[galleon_conferences] WITH NOCHECK ADD 
        CONSTRAINT [PK_conferences] PRIMARY KEY  CLUSTERED 
        (
                [id]
        )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[galleon_forums] WITH NOCHECK ADD 
        CONSTRAINT [PK_forums] PRIMARY KEY  CLUSTERED 
        (
                [id]
        )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[galleon_groups] WITH NOCHECK ADD 
        CONSTRAINT [PK_groups] PRIMARY KEY  CLUSTERED 
        (
                [id]
        )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[galleon_messages] WITH NOCHECK ADD 
        CONSTRAINT [PK_messages] PRIMARY KEY  CLUSTERED 
        (
                [id]
        )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[galleon_ranks] WITH NOCHECK ADD 
        CONSTRAINT [PK_galleon_ranks] PRIMARY KEY  CLUSTERED 
        (
                [id]
        )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[galleon_subscriptions] WITH NOCHECK ADD 
        CONSTRAINT [PK_subscriptions] PRIMARY KEY  CLUSTERED 
        (
                [id]
        )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[galleon_threads] WITH NOCHECK ADD 
        CONSTRAINT [PK_threads] PRIMARY KEY  CLUSTERED 
        (
                [id]
        )  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[galleon_users] WITH NOCHECK ADD 
        CONSTRAINT [PK_users] PRIMARY KEY  CLUSTERED 
        (
                [id]
        )  ON [PRIMARY] 
GO


insert into [dbo].[galleon_users](id,username,password,emailaddress,datecreated,confirmed,signature)
values('AD0CD90E-07C8-CFFE-F80C5EB6688AF47A','admin','admin','admin@127.0.0.1',getDate(),1,'')
GO

insert into [dbo].[galleon_groups](id,[group])
values('AD0EA988-0C8E-E2B3-DF0CF594C5DAAD63','forumsadmin')
GO

insert into [dbo].[galleon_groups](id,[group])
values('AD0F29B5-BEED-B8BD-CAA9379711EBF168','forumsmember')
GO

insert into [dbo].[galleon_groups](id,[group])
values('AD0F717C-AFE5-FD0E-77EB8FF5BDD858A2','forumsmoderator')
GO

insert into [dbo].[galleon_users_groups](useridfk,groupidfk)
values('AD0CD90E-07C8-CFFE-F80C5EB6688AF47A','AD0EA988-0C8E-E2B3-DF0CF594C5DAAD63')
go