if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Threads_Forums]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK_Threads_Forums
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_Relationships_CB]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_Relationships] DROP CONSTRAINT MSC_FK_Relationships_CB
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_RelationshipType_CB]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_RelationshipTypes] DROP CONSTRAINT MSC_FK_RelationshipType_CB
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_ValidRelationships_CB]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_ValidRelationships] DROP CONSTRAINT MSC_FK_ValidRelationships_CB
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_MSC_E_class__ID]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_E_class] DROP CONSTRAINT FK_MSC_E_class__ID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_MSC_E_department__ID]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_E_department] DROP CONSTRAINT FK_MSC_E_department__ID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_MSC_E_person__ID]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_E_person] DROP CONSTRAINT FK_MSC_E_person__ID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_MSC_E_school__ID]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_E_school] DROP CONSTRAINT FK_MSC_E_school__ID
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_E_user__id]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_E_user] DROP CONSTRAINT MSC_FK_E_user__id
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_Entities_CB]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_Entities] DROP CONSTRAINT MSC_FK_Entities_CB
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_Relationships_SE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_Relationships] DROP CONSTRAINT MSC_FK_Relationships_SE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_Relationships_TE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_Relationships] DROP CONSTRAINT MSC_FK_Relationships_TE
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_Entities_ET]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_Entities] DROP CONSTRAINT MSC_FK_Entities_ET
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_EntityTypeAttributes_ET]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_EntityTypeAttributes] DROP CONSTRAINT MSC_FK_EntityTypeAttributes_ET
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_Relationships_SET]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_Relationships] DROP CONSTRAINT MSC_FK_Relationships_SET
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_Relationships_TET]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_Relationships] DROP CONSTRAINT MSC_FK_Relationships_TET
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_ValidRelationships_SET]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_ValidRelationships] DROP CONSTRAINT MSC_FK_ValidRelationships_SET
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_ValidRelationships_TET]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_ValidRelationships] DROP CONSTRAINT MSC_FK_ValidRelationships_TET
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_Relationships_RT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_Relationships] DROP CONSTRAINT MSC_FK_Relationships_RT
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_ValidRelationships_RT]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_ValidRelationships] DROP CONSTRAINT MSC_FK_ValidRelationships_RT
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_FK_Relationships_VR]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[MSC_Relationships] DROP CONSTRAINT MSC_FK_Relationships_VR
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Messages_Threads]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK_Messages_Threads
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_categories_cal_users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[categories] DROP CONSTRAINT FK_categories_cal_users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_events_categories]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[events] DROP CONSTRAINT FK_events_categories
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_enrollments_classes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[enrollments] DROP CONSTRAINT FK_enrollments_classes
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_events_classes]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[events] DROP CONSTRAINT FK_events_classes
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_categories_users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[categories] DROP CONSTRAINT FK_categories_users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[FK_enrollments_users]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [fision].[enrollments] DROP CONSTRAINT FK_enrollments_users
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[udf_ChooseMax]') and xtype in (N'FN', N'IF', N'TF'))
drop function [fision].[udf_ChooseMax]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[udf_GetPrivileges]') and xtype in (N'FN', N'IF', N'TF'))
drop function [fision].[udf_GetPrivileges]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[udf_IsAllowed]') and xtype in (N'FN', N'IF', N'TF'))
drop function [fision].[udf_IsAllowed]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_AddEntityType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_AddEntityType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_AddRelationship]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_AddRelationship]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_AddRelationshipType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_AddRelationshipType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_AddValidRelationship]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_AddValidRelationship]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_ContextSearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_ContextSearch]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_DeactivateEntity]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_DeactivateEntity]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_DeactivateRelationship]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_DeactivateRelationship]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_Deactivate_E_user]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_Deactivate_E_user]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_GetAutomaticRelationshipType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_GetAutomaticRelationshipType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_GetEntityTypeDetails]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_GetEntityTypeDetails]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_GetNewEntityID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_GetNewEntityID]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_GetUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_GetUser]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_ListEntityTypes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_ListEntityTypes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_ListRelationshipTypes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_ListRelationshipTypes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_ListRelationships]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_ListRelationships]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_ListValidRelationships]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_ListValidRelationships]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_LookupEntity]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_LookupEntity]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_RegisterEntityAttribute]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_RegisterEntityAttribute]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_RegisterEntityType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_RegisterEntityType]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_SaveEntity]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_SaveEntity]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_SP_UpdateAutomaticRelationship]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [fision].[MSC_SP_UpdateAutomaticRelationship]
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

if exists (select * from dbo.systypes where name = N'MSC_Type_DataType')
exec sp_droptype N'MSC_Type_DataType'
GO

if exists (select * from dbo.systypes where name = N'MSC_Type_EntityType')
exec sp_droptype N'MSC_Type_EntityType'
GO

if exists (select * from dbo.systypes where name = N'MSC_Type_Identifier')
exec sp_droptype N'MSC_Type_Identifier'
GO

if exists (select * from dbo.systypes where name = N'MSC_Type_RelationshipType')
exec sp_droptype N'MSC_Type_RelationshipType'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[fision].[MSC_Rule_CheckDataType]') and OBJECTPROPERTY(id, N'IsRule') = 1)
drop rule [fision].[MSC_Rule_CheckDataType]
GO

setuser N'fision'
GO

CREATE RULE MSC_Rule_CheckDataType 
         AS @DataType IN (
 'number',        /** Indicates a numeric field.  Constraint
                      is length+decimals, e.g. '4.1'*/
 'string',        /** Indicates a string.  Constraint
                      is length, e.g. '4' */
 'phone',         /** Indicates a telephone number.
                      Constraints are ignored. */
 'date',          /** Indicates a date.  Constraints
                      are ignored. */
 'time',          /** Indicates a time.  Constraints
                      are ignored. */
 'start-date',    /** Indicates a date, before which,
                      the entity is disabled.
                      Constraints are ignored. */
 'end-date',      /** Indicates a date, after which,
                      the entity is disabed.
                      Constraints are ignored. */
 'regexp',        /** Indicates a regular expression-
                      backed string.  Constraints are
                      the regular expression and the string
                      length, e.g. '14,[A-Z]+[A-Z,0-9]*' */
 'entity'         /** Indicates a link to another entity.
                      Constraint is the name of the entity
                      to link to. */
)

GO
setuser
GO

setuser N'fision'
GO

EXEC sp_addtype N'MSC_Type_DataType', N'varchar (32)', N'not null'
GO

setuser
GO

setuser N'fision'
GO

EXEC sp_bindrule N'[fision].[MSC_Rule_CheckDataType]', N'[MSC_Type_DataType]'
GO

setuser
GO

setuser N'fision'
GO

EXEC sp_addtype N'MSC_Type_EntityType', N'varchar (20)', N'not null'
GO

setuser
GO

setuser N'fision'
GO

EXEC sp_addtype N'MSC_Type_Identifier', N'numeric(7,0)', N'not null'
GO

setuser
GO

setuser N'fision'
GO

EXEC sp_addtype N'MSC_Type_RelationshipType', N'varchar (40)', N'not null'
GO

setuser
GO

CREATE TABLE [dbo].[BOARD_PARAM] (
	[bgColor] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[text_color] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[table_bgColor] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[header_img] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[newtopic_img] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[reply_img] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[replyLocked_img] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[forumLocked_img] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[link_color] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[vlin_color] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[def_font] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[topics_per_page] [int] NULL ,
	[pots_per_page] [int] NULL ,
	[sitename] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[allow_html] [bit] NULL ,
	[allow_sig] [bit] NULL ,
	[timeOut_session] [smalldatetime] NULL ,
	[header] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[footer] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[bad_word_list] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Forums] (
	[FID] [int] IDENTITY (1000, 1) NOT NULL ,
	[Forum] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[TCount] [int] NULL ,
	[MCount] [int] NULL ,
	[LastPost] [smalldatetime] NULL ,
	[GID] [int] NULL ,
	[Description] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Groups] (
	[GID] [int] IDENTITY (1000, 1) NOT NULL ,
	[GroupName] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassID] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSAL_Events] (
	[eventID] [decimal](10, 0) IDENTITY (1, 1) NOT NULL ,
	[priority] [decimal](3, 0) NOT NULL ,
	[moduleName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[activity] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[activityInfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[personID] [decimal](10, 0) NULL ,
	[dateTimeStamp] [datetime] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_E_class] (
	[_id] [MSC_Type_Identifier] NOT NULL ,
	[A_sectionkey] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_coursenumber] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_sectionnumber] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_semester] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_coursename] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_points] [numeric](5, 1) NULL ,
	[_deactivated] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_E_department] (
	[_id] [MSC_Type_Identifier] NOT NULL ,
	[A_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[_deactivated] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_E_person] (
	[_id] [MSC_Type_Identifier] NOT NULL ,
	[A_firstname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_middlename] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_lastname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_cuid] [varchar] (11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_cunixid] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_email] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_phonenumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_address1] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_address2] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_address3] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_state] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_country] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[_deactivated] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_E_school] (
	[_id] [MSC_Type_Identifier] NOT NULL ,
	[A_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[_deactivated] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_E_user] (
	[_id] [MSC_Type_Identifier] NOT NULL ,
	[A_username] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_password] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[_deactivated] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_Entities] (
	[EntityID] [MSC_Type_Identifier] IDENTITY (1, 1) NOT NULL ,
	[EntityType] [MSC_Type_EntityType] NOT NULL ,
	[CreatedBy] [MSC_Type_Identifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_EntityTypeAttributes] (
	[SortOrder] [numeric](7, 0) IDENTITY (1, 1) NOT NULL ,
	[EntityType] [MSC_Type_EntityType] NOT NULL ,
	[AttributeName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DisplayName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[DataType] [MSC_Type_DataType] NOT NULL ,
	[DataConstraints] [varchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Searchable] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_EntityTypes] (
	[EntityType] [MSC_Type_EntityType] NOT NULL ,
	[DisplayName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[CanLogin] [bit] NULL ,
	[CreatedBy] [MSC_Type_Identifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_RelationshipTypes] (
	[RelationshipType] [MSC_Type_RelationshipType] NOT NULL ,
	[DisplayName] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[AutomaticRelationship] [bit] NULL ,
	[CreatedBy] [MSC_Type_Identifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_Relationships] (
	[RelationshipType] [MSC_Type_RelationshipType] NOT NULL ,
	[SourceEntityType] [MSC_Type_EntityType] NOT NULL ,
	[SourceEntity] [MSC_Type_Identifier] NOT NULL ,
	[TargetEntityType] [MSC_Type_EntityType] NOT NULL ,
	[TargetEntity] [MSC_Type_Identifier] NOT NULL ,
	[CreatedBy] [MSC_Type_Identifier] NOT NULL ,
	[_deactivated] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[MSC_ValidRelationships] (
	[RelationshipType] [MSC_Type_RelationshipType] NOT NULL ,
	[SourceEntityType] [MSC_Type_EntityType] NOT NULL ,
	[TargetEntityType] [MSC_Type_EntityType] NOT NULL ,
	[CreatedBy] [MSC_Type_Identifier] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Messages] (
	[MID] [int] IDENTITY (1000, 1) NOT NULL ,
	[TID] [int] NOT NULL ,
	[UID] [int] NOT NULL ,
	[Subject] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[PostDate] [smalldatetime] NULL ,
	[ReplyTo] [int] NULL ,
	[RemoteIP] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[UseSig] [bit] NOT NULL ,
	[SendMail] [bit] NOT NULL ,
	[HotPost] [bit] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Threads] (
	[TID] [int] IDENTITY (1000, 1) NOT NULL ,
	[FID] [int] NOT NULL ,
	[Thread] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[StartDate] [smalldatetime] NULL ,
	[MCount] [int] NULL ,
	[LastPost] [smalldatetime] NULL ,
	[StartDate_UID] [int] NULL ,
	[LastPost_UID] [int] NULL ,
	[Status] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[cal_users] (
	[userid] [int] IDENTITY (1, 1) NOT NULL ,
	[cunix] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[categories] (
	[categoryid] [int] IDENTITY (1, 1) NOT NULL ,
	[userid] [int] NULL ,
	[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[allowstudent] [int] NOT NULL ,
	[allowprofessor] [int] NOT NULL ,
	[allowta] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[classes] (
	[classid] [int] IDENTITY (1, 1) NOT NULL ,
	[name] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[code] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[disabled] (
	[UID] [int] IDENTITY (1000, 1) NOT NULL ,
	[Username] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Password] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[RealName] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Website] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ZipCode] [int] NULL ,
	[CID] [int] NULL ,
	[Signature] [char] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Expand] [bit] NOT NULL ,
	[ReverseSort] [bit] NOT NULL ,
	[ShowEmail] [bit] NOT NULL ,
	[CanPost] [bit] NOT NULL ,
	[MCount] [int] NULL ,
	[ActCode] [int] NULL ,
	[ActDate] [smalldatetime] NULL ,
	[JoinDate] [smalldatetime] NULL ,
	[ClassList] [char] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Admin] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[enrollments] (
	[classid] [int] NOT NULL ,
	[userid] [int] NOT NULL ,
	[isstudent] [bit] NOT NULL ,
	[ista] [bit] NOT NULL ,
	[isprofessor] [bit] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [fision].[events] (
	[eventid] [int] IDENTITY (1, 1) NOT NULL ,
	[categoryid] [int] NOT NULL ,
	[classid] [int] NULL ,
	[begindate] [datetime] NOT NULL ,
	[enddate] [datetime] NOT NULL ,
	[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [fision].[users] (
	[userid] [int] IDENTITY (1, 1) NOT NULL ,
	[cunix] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [fision].[MSC_E_class] WITH NOCHECK ADD 
	CONSTRAINT [PK_MSC_E_class] PRIMARY KEY  CLUSTERED 
	(
		[_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_E_department] WITH NOCHECK ADD 
	CONSTRAINT [PK_MSC_E_department] PRIMARY KEY  CLUSTERED 
	(
		[_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_E_person] WITH NOCHECK ADD 
	CONSTRAINT [PK_MSC_E_person] PRIMARY KEY  CLUSTERED 
	(
		[_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_E_school] WITH NOCHECK ADD 
	CONSTRAINT [PK_MSC_E_school] PRIMARY KEY  CLUSTERED 
	(
		[_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_E_user] WITH NOCHECK ADD 
	CONSTRAINT [MSC_PK_E_user] PRIMARY KEY  CLUSTERED 
	(
		[_id]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_Entities] WITH NOCHECK ADD 
	CONSTRAINT [MSC_PK_Entities] PRIMARY KEY  CLUSTERED 
	(
		[EntityID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_EntityTypeAttributes] WITH NOCHECK ADD 
	CONSTRAINT [MSC_PK_EntityTypeAttributes] PRIMARY KEY  CLUSTERED 
	(
		[EntityType],
		[AttributeName]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_EntityTypes] WITH NOCHECK ADD 
	CONSTRAINT [MSC_PK_EntityTypes] PRIMARY KEY  CLUSTERED 
	(
		[EntityType]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_RelationshipTypes] WITH NOCHECK ADD 
	CONSTRAINT [MSC_PK_RelationshipTypes] PRIMARY KEY  CLUSTERED 
	(
		[RelationshipType]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_Relationships] WITH NOCHECK ADD 
	CONSTRAINT [MSC_PK_Relationships] PRIMARY KEY  CLUSTERED 
	(
		[RelationshipType],
		[SourceEntityType],
		[SourceEntity],
		[TargetEntityType],
		[TargetEntity]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSC_ValidRelationships] WITH NOCHECK ADD 
	CONSTRAINT [MSC_PK_ValidRelationships] PRIMARY KEY  CLUSTERED 
	(
		[RelationshipType],
		[SourceEntityType],
		[TargetEntityType]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[cal_users] WITH NOCHECK ADD 
	CONSTRAINT [PK_cal_users] PRIMARY KEY  CLUSTERED 
	(
		[userid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[categories] WITH NOCHECK ADD 
	CONSTRAINT [PK_categories] PRIMARY KEY  CLUSTERED 
	(
		[categoryid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[classes] WITH NOCHECK ADD 
	CONSTRAINT [PK_classes] PRIMARY KEY  CLUSTERED 
	(
		[classid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[events] WITH NOCHECK ADD 
	CONSTRAINT [PK_events] PRIMARY KEY  CLUSTERED 
	(
		[eventid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [fision].[users] WITH NOCHECK ADD 
	CONSTRAINT [PK_users] PRIMARY KEY  CLUSTERED 
	(
		[userid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Forums] WITH NOCHECK ADD 
	CONSTRAINT [PK_Forums] PRIMARY KEY  NONCLUSTERED 
	(
		[FID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Groups] WITH NOCHECK ADD 
	CONSTRAINT [PK_Groups] PRIMARY KEY  NONCLUSTERED 
	(
		[GID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [fision].[MSAL_Events] WITH NOCHECK ADD 
	CONSTRAINT [DF__MSAL_Even__perso__55009F39] DEFAULT (0) FOR [personID],
	CONSTRAINT [DF__MSAL_Even__dateT__55F4C372] DEFAULT (getdate()) FOR [dateTimeStamp]
GO

ALTER TABLE [fision].[MSC_EntityTypeAttributes] WITH NOCHECK ADD 
	CONSTRAINT [DF__MSC_Entit__Searc__60A75C0F] DEFAULT (1) FOR [Searchable]
GO

ALTER TABLE [fision].[MSC_EntityTypes] WITH NOCHECK ADD 
	CONSTRAINT [DF__MSC_Entit__CanLo__5DCAEF64] DEFAULT (0) FOR [CanLogin]
GO

ALTER TABLE [dbo].[Messages] WITH NOCHECK ADD 
	CONSTRAINT [DF_Messages_HotPost] DEFAULT (0) FOR [HotPost],
	CONSTRAINT [PK_Messages] PRIMARY KEY  NONCLUSTERED 
	(
		[MID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Threads] WITH NOCHECK ADD 
	CONSTRAINT [PK_Threads] PRIMARY KEY  NONCLUSTERED 
	(
		[TID]
	) WITH  FILLFACTOR = 90  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[disabled] WITH NOCHECK ADD 
	CONSTRAINT [DF_Users_Admin] DEFAULT (0) FOR [Admin]
GO

 CREATE  INDEX [MSC_IDX_user_un] ON [fision].[MSC_E_user]([A_username]) ON [PRIMARY]
GO

 CREATE  INDEX [MSC_IDX_Relationships_Relationship] ON [fision].[MSC_Relationships]([RelationshipType]) ON [PRIMARY]
GO

 CREATE  INDEX [MSC_IDX_Relationships_Source] ON [fision].[MSC_Relationships]([SourceEntityType], [SourceEntity]) ON [PRIMARY]
GO

 CREATE  INDEX [MSC_IDX_Relationships_Target] ON [fision].[MSC_Relationships]([TargetEntityType], [TargetEntity]) ON [PRIMARY]
GO

 CREATE  INDEX [IX_Messages] ON [dbo].[Messages]([TID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_Messages_1] ON [dbo].[Messages]([UID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

 CREATE  INDEX [IX_Threads] ON [dbo].[Threads]([FID]) WITH  FILLFACTOR = 90 ON [PRIMARY]
GO

setuser N'fision'
GO

EXEC sp_bindrule N'[fision].[MSC_Rule_CheckDataType]', N'[MSC_EntityTypeAttributes].[DataType]'
GO

setuser
GO

ALTER TABLE [fision].[MSC_E_class] ADD 
	CONSTRAINT [FK_MSC_E_class__ID] FOREIGN KEY 
	(
		[_id]
	) REFERENCES [fision].[MSC_Entities] (
		[EntityID]
	)
GO

ALTER TABLE [fision].[MSC_E_department] ADD 
	CONSTRAINT [FK_MSC_E_department__ID] FOREIGN KEY 
	(
		[_id]
	) REFERENCES [fision].[MSC_Entities] (
		[EntityID]
	)
GO

ALTER TABLE [fision].[MSC_E_person] ADD 
	CONSTRAINT [FK_MSC_E_person__ID] FOREIGN KEY 
	(
		[_id]
	) REFERENCES [fision].[MSC_Entities] (
		[EntityID]
	)
GO

ALTER TABLE [fision].[MSC_E_school] ADD 
	CONSTRAINT [FK_MSC_E_school__ID] FOREIGN KEY 
	(
		[_id]
	) REFERENCES [fision].[MSC_Entities] (
		[EntityID]
	)
GO

ALTER TABLE [fision].[MSC_E_user] ADD 
	CONSTRAINT [MSC_FK_E_user__id] FOREIGN KEY 
	(
		[_id]
	) REFERENCES [fision].[MSC_Entities] (
		[EntityID]
	)
GO

ALTER TABLE [fision].[MSC_Entities] ADD 
	CONSTRAINT [MSC_FK_Entities_CB] FOREIGN KEY 
	(
		[CreatedBy]
	) REFERENCES [fision].[MSC_Entities] (
		[EntityID]
	),
	CONSTRAINT [MSC_FK_Entities_ET] FOREIGN KEY 
	(
		[EntityType]
	) REFERENCES [fision].[MSC_EntityTypes] (
		[EntityType]
	)
GO

ALTER TABLE [fision].[MSC_EntityTypeAttributes] ADD 
	CONSTRAINT [MSC_FK_EntityTypeAttributes_ET] FOREIGN KEY 
	(
		[EntityType]
	) REFERENCES [fision].[MSC_EntityTypes] (
		[EntityType]
	)
GO

ALTER TABLE [fision].[MSC_RelationshipTypes] ADD 
	CONSTRAINT [MSC_FK_RelationshipType_CB] FOREIGN KEY 
	(
		[CreatedBy]
	) REFERENCES [fision].[MSC_E_user] (
		[_id]
	)
GO

ALTER TABLE [fision].[MSC_Relationships] ADD 
	CONSTRAINT [MSC_FK_Relationships_CB] FOREIGN KEY 
	(
		[CreatedBy]
	) REFERENCES [fision].[MSC_E_user] (
		[_id]
	),
	CONSTRAINT [MSC_FK_Relationships_RT] FOREIGN KEY 
	(
		[RelationshipType]
	) REFERENCES [fision].[MSC_RelationshipTypes] (
		[RelationshipType]
	),
	CONSTRAINT [MSC_FK_Relationships_SE] FOREIGN KEY 
	(
		[SourceEntity]
	) REFERENCES [fision].[MSC_Entities] (
		[EntityID]
	),
	CONSTRAINT [MSC_FK_Relationships_SET] FOREIGN KEY 
	(
		[SourceEntityType]
	) REFERENCES [fision].[MSC_EntityTypes] (
		[EntityType]
	),
	CONSTRAINT [MSC_FK_Relationships_TE] FOREIGN KEY 
	(
		[TargetEntity]
	) REFERENCES [fision].[MSC_Entities] (
		[EntityID]
	),
	CONSTRAINT [MSC_FK_Relationships_TET] FOREIGN KEY 
	(
		[TargetEntityType]
	) REFERENCES [fision].[MSC_EntityTypes] (
		[EntityType]
	),
	CONSTRAINT [MSC_FK_Relationships_VR] FOREIGN KEY 
	(
		[RelationshipType],
		[SourceEntityType],
		[TargetEntityType]
	) REFERENCES [fision].[MSC_ValidRelationships] (
		[RelationshipType],
		[SourceEntityType],
		[TargetEntityType]
	)
GO

ALTER TABLE [fision].[MSC_ValidRelationships] ADD 
	CONSTRAINT [MSC_FK_ValidRelationships_CB] FOREIGN KEY 
	(
		[CreatedBy]
	) REFERENCES [fision].[MSC_E_user] (
		[_id]
	),
	CONSTRAINT [MSC_FK_ValidRelationships_RT] FOREIGN KEY 
	(
		[RelationshipType]
	) REFERENCES [fision].[MSC_RelationshipTypes] (
		[RelationshipType]
	),
	CONSTRAINT [MSC_FK_ValidRelationships_SET] FOREIGN KEY 
	(
		[SourceEntityType]
	) REFERENCES [fision].[MSC_EntityTypes] (
		[EntityType]
	),
	CONSTRAINT [MSC_FK_ValidRelationships_TET] FOREIGN KEY 
	(
		[TargetEntityType]
	) REFERENCES [fision].[MSC_EntityTypes] (
		[EntityType]
	)
GO

ALTER TABLE [dbo].[Messages] ADD 
	CONSTRAINT [FK_Messages_Threads] FOREIGN KEY 
	(
		[TID]
	) REFERENCES [dbo].[Threads] (
		[TID]
	)
GO

ALTER TABLE [dbo].[Threads] ADD 
	CONSTRAINT [FK_Threads_Forums] FOREIGN KEY 
	(
		[FID]
	) REFERENCES [dbo].[Forums] (
		[FID]
	)
GO

ALTER TABLE [fision].[categories] ADD 
	CONSTRAINT [FK_categories_cal_users] FOREIGN KEY 
	(
		[userid]
	) REFERENCES [fision].[cal_users] (
		[userid]
	),
	CONSTRAINT [FK_categories_users] FOREIGN KEY 
	(
		[userid]
	) REFERENCES [fision].[users] (
		[userid]
	)
GO

ALTER TABLE [fision].[enrollments] ADD 
	CONSTRAINT [FK_enrollments_classes] FOREIGN KEY 
	(
		[classid]
	) REFERENCES [fision].[classes] (
		[classid]
	),
	CONSTRAINT [FK_enrollments_users] FOREIGN KEY 
	(
		[userid]
	) REFERENCES [fision].[users] (
		[userid]
	)
GO

ALTER TABLE [fision].[events] ADD 
	CONSTRAINT [FK_events_categories] FOREIGN KEY 
	(
		[categoryid]
	) REFERENCES [fision].[categories] (
		[categoryid]
	),
	CONSTRAINT [FK_events_classes] FOREIGN KEY 
	(
		[classid]
	) REFERENCES [fision].[classes] (
		[classid]
	)
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_AddEntityType
(
@EntityType MSC_Type_EntityType,
@DisplayName varchar (32),
@CanLogin bit,
@AttributeColumnList varchar (7500),
@AttributeRegisterCalls varchar (8000),
@CreatedBy MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure is used to physically create an entity
 * in the system.
 */ 
    BEGIN TRANSACTION
/**
 * Call register entity type here.  If the entity type exists,
 * we should crash here.
 */
        execute MSC_SP_RegisterEntityType @EntityType, @DisplayName,
                @CanLogin, @CreatedBy

/**
 * Build the full list of attributes.
 */
        DECLARE @FullAttributeList varchar (8000)

        IF (@AttributeColumnList IS NULL) OR (@AttributeColumnList = '')
        BEGIN
            SET @FullAttributeList='(_id MSC_Type_Identifier,'+
                '_deactivated smalldatetime NULL,'+
                ' CONSTRAINT FK_MSC_E_'+@EntityType+
                '__ID FOREIGN KEY (_ID) REFERENCES MSC_Entities'+
                ', CONSTRAINT PK_MSC_E_'+@EntityType+' PRIMARY KEY CLUSTERED (_ID))'
        END
        ELSE
        BEGIN
            SET @FullAttributeList='(_id MSC_Type_Identifier,'+
                @AttributeColumnList+','+
                '_deactivated smalldatetime NULL,'+
                ' CONSTRAINT FK_MSC_E_'+@EntityType+
                '__ID FOREIGN KEY (_ID) REFERENCES MSC_Entities'+
                ', CONSTRAINT PK_MSC_E_'+@EntityType+' PRIMARY KEY CLUSTERED (_ID))'
        END

/**
 * Create actual table.
 */
        execute ('CREATE TABLE MSC_E_'+@EntityType+' '+@FullAttributeList)

/**
 * Register the attributes.
 */
        IF NOT ((@AttributeRegisterCalls IS NULL) OR
                (@AttributeRegisterCalls =''))
        BEGIN
            execute (@AttributeRegisterCalls)
        END
        
    COMMIT TRANSACTION

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_AddRelationship
(
@RelationshipType MSC_Type_RelationshipType,
@SourceEntityType MSC_Type_EntityType,
@SourceEntity MSC_Type_Identifier,
@TargetEntityType MSC_Type_EntityType,
@TargetEntity MSC_Type_Identifier,
@CreatedBy MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure is used to add a relationship
 * between two entities
 */ 
    INSERT 
      INTO MSC_Relationships 
         ( RelationshipType,
           SourceEntityType,
           SourceEntity,
           TargetEntityType,
           TargetEntity,
           CreatedBy)
    VALUES
         ( @RelationshipType,
           @SourceEntityType,
           @SourceEntity,
           @TargetEntityType,
           @TargetEntity,
           @CreatedBy)

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_AddRelationshipType
(
@RelationshipType MSC_Type_RelationshipType,
@DisplayName varchar (32),
@CreatedBy MSC_Type_Identifier,
@AutomaticRelationship bit=0
)
AS    
/** 
 * This stored procedure is used to create a relationship type.
 */ 
    INSERT 
      INTO MSC_RelationshipTypes 
         ( RelationshipType,
           DisplayName,
           AutomaticRelationship,
           CreatedBy )
    VALUES
         ( @RelationshipType,
           @DisplayName,
           @AutomaticRelationship,
           @CreatedBy )

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_AddValidRelationship
(
@RelationshipType MSC_Type_RelationshipType,
@SourceEntityType MSC_Type_EntityType,
@TargetEntityType MSC_Type_EntityType,
@CreatedBy MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure is used to enable a certain
 * relationship between two entity types.
 */ 
    INSERT 
      INTO MSC_ValidRelationships
         ( RelationshipType,
           SourceEntityType,
           TargetEntityType,
           CreatedBy)
    VALUES
         ( @RelationshipType,
           @SourceEntityType,
           @TargetEntityType,
           @CreatedBy)

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_ContextSearch
(
@Query varchar(8000)
)
AS    
/** 
 * This stored procedure is used to perform a query for context data.
 */ 
    Execute(@Query)

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_DeactivateEntity
(
@EntityType MSC_Type_EntityType,
@_id MSC_Type_Identifier
)
AS
/** 
 * This stored procedure is used to deactivate an entity.
 */
    DECLARE @IDString varchar(20)
    SET @IDString=Convert(varchar(20),@_ID)

    Execute (
        'IF EXISTS (SELECT * FROM MSC_E_'+@EntityType+
        ' WHERE _id='+@IDString+
        ' AND _deactivated IS null) BEGIN UPDATE MSC_E_'+@EntityType+
        ' SET _deactivated=GETDATE() WHERE _id='+
        @IDString+' END'
    )


    Execute (
        'IF EXISTS (SELECT * FROM MSC_E_user'+
        ' WHERE _id='+@IDString+
        ' AND _deactivated IS null) BEGIN UPDATE MSC_E_user'+
        ' SET _deactivated=GETDATE() WHERE _id='+
        @IDString+' END'
    )

/**
 * Disable relationships whose source entity is this one.
 */
 	DECLARE @SourceRels varchar(8000)
	SET @SourceRels=
        'IF EXISTS (SELECT * FROM MSC_Relationships '+
        'WHERE SourceEntityType='+CHAR(39)+@EntityType+
        CHAR(39)+' AND SourceEntity='+@IDString+
        ' AND _deactivated IS null) BEGIN UPDATE MSC_Relationships '+
        'SET _deactivated=GETDATE() WHERE SourceEntity='+
        @IDString+' AND SourceEntityType='+CHAR(39)+
        @EntityType+CHAR(39)+' END'
    Execute (@SourceRels)

/**
 * Disable relationships whose target entity is this one.
 */
 	DECLARE @TargetRels varchar(8000)
	SET @TargetRels=
        'IF EXISTS (SELECT * FROM MSC_Relationships '+
        'WHERE TargetEntityType='+CHAR(39)+@EntityType+
        CHAR(39)+' AND TargetEntity='+@IDString+
        ' AND _deactivated IS null) BEGIN UPDATE MSC_Relationships '+
        'SET _deactivated=GETDATE() WHERE SourceEntity='+
        @IDString+' AND TargetEntityType='+CHAR(39)+
        @EntityType+CHAR(39)+' END'
    Execute (@TargetRels)

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_DeactivateRelationship
(
@RelationshipType MSC_Type_RelationshipType,
@SourceEntityType MSC_Type_EntityType,
@SourceEntity MSC_Type_Identifier,
@TargetEntityType MSC_Type_EntityType,
@TargetEntity MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure is used to deactivate a relationship
 */ 
    IF EXISTS (SELECT *
                 FROM MSC_Relationships
                WHERE _deactivated IS NULL
                  AND RelationshipType=@RelationshipType
                  AND SourceEntityType=@SourceEntityType
                  AND SourceEntity=@SourceEntity
                  AND TargetEntityType=@TargetEntityType
                  AND TargetEntity=@TargetEntity )
    BEGIN
        UPDATE MSC_Relationships
           SET _deactivated=GetDate()
         WHERE _deactivated IS NULL
           AND RelationshipType=@RelationshipType
           AND SourceEntityType=@SourceEntityType
           AND SourceEntity=@SourceEntity
           AND TargetEntityType=@TargetEntityType
           AND TargetEntity=@TargetEntity
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_Deactivate_E_user
(
@_id MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure is used to deactivate a user.
 */
    IF EXISTS (SELECT *
                 FROM MSC_E_user
                WHERE _id=@_ID
                  AND _deactivated IS null)
    BEGIN
        UPDATE MSC_E_user
           SET _deactivated=GETDATE()
         WHERE _id=@_ID
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_GetAutomaticRelationshipType
(
@SourceEntityType MSC_Type_EntityType,
@AttributeName varchar(32),
@AutomaticRelationshipType MSC_Type_RelationshipType OUTPUT
)
AS    
/** 
 * This stored procedure constructs the name of an automatic
 * relationship type for two entities.
 */
    SET @AutomaticRelationshipType=LEFT('_'+@SourceEntityType+'_'+
        @AttributeName, 40)


GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_GetEntityTypeDetails
(
@EntityType MSC_Type_EntityType
)
AS    
/** 
 * This stored procedure is used to retrieve details
 * about an entity type.  It also lists their attributes.
 */ 
    SELECT EntityType,
           DisplayName,
           CanLogin,
           CreatedBy
      FROM MSC_EntityTypes
     WHERE EntityType=@EntityType

    SELECT EntityType,
           AttributeName,
           DisplayName,
           DataType,
           DataConstraints,
           Searchable
      FROM MSC_EntityTypeAttributes
     WHERE EntityType=@EntityType
     ORDER BY SortOrder

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_GetNewEntityID
(
@EntityType MSC_Type_EntityType,
@CreatedBy MSC_Type_Identifier,
@EntityID MSC_Type_Identifier OUTPUT
)
AS    
/** 
 * This stored procedure is used to add a new entry
 * to the entity list, and return its corresponding ID
 */ 
    INSERT 
      INTO MSC_Entities 
         ( EntityType,
           CreatedBy)
    VALUES
         ( @EntityType,
           @CreatedBy)

    SET @EntityID=@@Identity

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_GetUser
(
@username varchar(16),
@password varchar(64)
)
AS    
/** 
 * This stored procedure is used to validate/get a user
 * for a login process.
 */
    SELECT EntityType,
           EntityID
      FROM MSC_Entities, MSC_E_user
     WHERE MSC_Entities.EntityID=MSC_E_user._id
       AND LOWER(MSC_E_user.A_username)=LOWER(@username)
       AND MSC_E_user.A_password=@password
       AND MSC_E_user._deactivated IS NULL

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_ListEntityTypes
AS    
/** 
 * This stored procedure is used to list the entity types
 * in the system.
 */ 
    SELECT EntityType,
           DisplayName,
           CanLogin
      FROM MSC_EntityTypes 

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_ListRelationshipTypes
AS    
/** 
 * This stored procedure lists all relationship types
 * in the system.
 */
    SELECT RelationshipType,
           DisplayName
      FROM MSC_RelationshipTypes

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_ListRelationships
(
@RelationshipType MSC_Type_RelationshipType,
@EntityType MSC_Type_EntityType,
@Entity MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure lists all relationships for an
 * entity.  Optionally, the type of relationship can be
 * specified
 */
    IF @EntityType IS NOT NULL
    BEGIN
        SELECT RelationshipType,
               SourceEntityType,
               SourceEntity,
               TargetEntityType,
               TargetEntity
          FROM MSC_Relationships
         WHERE ((     SourceEntityType=@EntityType
                  AND SourceEntity=@Entity)
             OR (     TargetEntityType=@EntityType
                  AND TargetEntity=@Entity))
        	AND _deactivated IS NULL
            AND RelationshipType=ISNULL(@RelationshipType,RelationshipType)
    END
    ELSE
    BEGIN
        SELECT RelationshipType,
               SourceEntityType,
               SourceEntity,
               TargetEntityType,
               TargetEntity
          FROM MSC_Relationships
         WHERE _deactivated IS NULL
            AND RelationshipType=ISNULL(@RelationshipType,RelationshipType)
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_ListValidRelationships
(
@RelationshipType MSC_Type_RelationshipType,
@EntityType MSC_Type_EntityType
)
AS    
/** 
 * This stored procedure is used to list the valid
 * relationships.  It can filter the query by a
 * specific entity, relationship, or both.
 */ 
    SELECT MSC_ValidRelationships.RelationshipType,
           MSC_RelationshipTypes.DisplayName AS RelationshipDisplayName,
           MSC_ValidRelationships.SourceEntityType,
           Source.DisplayName AS SourceDisplayName,
           Source.CanLogin AS SourceCanLogin,
           MSC_ValidRelationships.TargetEntityType,
           Target.DisplayName AS TargetDisplayName,
           Target.CanLogin AS TargetCanLogin
      FROM MSC_ValidRelationships, MSC_RelationshipTypes,
           MSC_EntityTypes Source, MSC_EntityTypes Target
     WHERE MSC_ValidRelationships.RelationshipType=
             ISNULL(@RelationshipType,MSC_ValidRelationships.RelationshipType)
       AND ( SourceEntityType=ISNULL(@EntityType,MSC_ValidRelationships.SourceEntityType)
          OR TargetEntityType=ISNULL(@EntityType,MSC_ValidRelationships.TargetEntityType)
           )
       AND MSC_ValidRelationships.RelationshipType=MSC_RelationshipTypes.RelationshipType
       AND Source.EntityType=MSC_ValidRelationships.SourceEntityType
       AND Target.EntityType=MSC_ValidRelationships.TargetEntityType

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_LookupEntity
(
@EntityID MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure is used to look up an entity in
 * the entity list.
 */ 
    SELECT EntityID,
           EntityType,
           CreatedBy
      FROM MSC_Entities
     WHERE EntityID=@EntityID

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_RegisterEntityAttribute
(
@EntityType MSC_Type_EntityType,
@AttributeName varchar (20),
@DisplayName varchar (32),
@DataType MSC_Type_DataType,
@DataConstraints varchar(512),
@Searchable bit,
@CreatedBy MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure is used to put an entry into
 * the list of attributes for an entity type.  This does
 * not actually effect the entity's table.
 */ 
    INSERT 
      INTO MSC_EntityTypeAttributes
         ( EntityType,
           AttributeName,
           DisplayName,
           DataType,
           DataConstraints,
           Searchable)
    VALUES
         ( @EntityType,
           @AttributeName,
           @DisplayName,
           @DataType,
           @DataConstraints,
           @Searchable)

    IF @DataType='entity'
    BEGIN
        DECLARE @AutoType MSC_Type_RelationshipType
        DECLARE @AutoDisplayName varchar(60)
        
        SELECT @AutoDisplayName=Source.DisplayName+CHAR(39)+'s '+Target.DisplayName
          FROM MSC_EntityTypes Source, MSC_EntityTypes Target
         WHERE Source.EntityType=@EntityType
           AND Target.EntityType=@DataConstraints

        EXEC MSC_SP_GetAutomaticRelationshipType @EntityType, @AttributeName, @AutoType OUTPUT
        EXEC MSC_SP_AddRelationshipType @AutoType, @AutoDisplayName, 1, @CreatedBy
        EXEC MSC_SP_AddValidRelationship @AutoType, @EntityType, @DataConstraints, @CreatedBy
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_RegisterEntityType
(
@EntityType MSC_Type_EntityType,
@DisplayName varchar (32),
@CanLogin bit,
@CreatedBy MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure is used to put an entry into
 * the list of entities.  This does not create the table
 * that defines the entity itself.
 * If, however, an entity is said to be able to login,
 * what this script WILL do, is ensure that there can be
 * a login between this module and the user module.
 */ 
    INSERT 
      INTO MSC_EntityTypes 
         ( EntityType,
           DisplayName,
           CanLogin,
           CreatedBy)
    VALUES
         ( @EntityType,
           @DisplayName,
           @CanLogin,
           @CreatedBy)

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_SaveEntity
(
@EntityType MSC_Type_EntityType,
@InsertFieldList varchar (7500),
@InsertValueList varchar (7500),
@UpdateList varchar (7500),
@AutomaticRelationships varchar(7500),
@CreatedBy MSC_Type_Identifier,
@EntityID MSC_Type_Identifier OUTPUT
)
AS    
/** 
 * This stored procedure is used to save an entity into the
 * system.  Here's a sample usage:
 *
 * DECLARE @ID MSC_Type_Identifier
 * SET @ID=NULL
 * MSC_SP_SaveEntity 'user','username, password', '''john'',''mypass''', 'username=''john'', password=''mypass''', '', 1, @ID OUTPUT
 * This assumes that user 1 is creating a new user, whose user name is 'john'
 * and whose password is 'mypass'.  If ID were not null, then an entity of that type
 * would have been saved.
 */ 

    BEGIN TRANSACTION
	IF @EntityID IS NULL
	BEGIN
            EXEC MSC_SP_GetNewEntityID @EntityType, @CreatedBy, @EntityID OUTPUT

            DECLARE @FullAttributeList varchar (8000)
            DECLARE @FullAttributeValues varchar (8000)

            IF (@InsertValueList IS NULL) OR (@InsertValueList='')
            BEGIN
                SET @FullAttributeList='(_id)'
                SET @FullAttributeValues='('+CONVERT(varchar(10),@EntityID)+')'
            END
            ELSE
            BEGIN
                SET @FullAttributeList='(_id,'+@InsertFieldList+')'
                SET @FullAttributeValues='('+CONVERT(varchar(10),@EntityID)+
                                         ','+@InsertValueList+')'
            END

            EXECUTE ('INSERT INTO MSC_E_'+@EntityType+' '+
                 @FullAttributeList+' VALUES '+@FullAttributeValues)

/**
 * Create a blank login record, if one is needed.
 */
	    IF EXISTS (SELECT * FROM MSC_EntityTypes WHERE EntityType=@EntityType and CanLogin=1 AND EntityType<>'user')
	    BEGIN
		INSERT INTO MSC_E_user (_id) VALUES (@EntityID)
	    END
        END
        ELSE
        BEGIN
/** On an update, there's nothing to do if no fields were provided */
            IF (@UpdateList IS NOT NULL) AND (NOT @UpdateList='')
            BEGIN
                DECLARE @RowCondition varchar (64)
                SET @RowCondition='_id='+CONVERT(varchar(10),@EntityID)
                EXECUTE ('UPDATE MSC_E_'+@EntityType+' SET '+@UpdateList+' '+
                         'WHERE '+@RowCondition)
            END
        END

        IF (@AutomaticRelationships IS NOT NULL) AND (NOT @AutomaticRelationships='')
        BEGIN
            DECLARE @AutomaticRelationshipPrefix varchar(8000)
            SET @AutomaticRelationshipPrefix='declare @SavedEntityID MSC_Type_Identifier set @SavedEntityID='+
                CONVERT(varchar(10),@EntityID)+' declare @CreatedBy MSC_Type_Identifier set @CreatedBy='+
                CONVERT(varchar(10),@CreatedBy)
            EXECUTE (@AutomaticRelationshipPrefix+' '+@AutomaticRelationships)
        END

    COMMIT TRANSACTION

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'fision'
GO


CREATE PROCEDURE MSC_SP_UpdateAutomaticRelationship
(
@SourceEntityType MSC_Type_EntityType,
@SourceEntityID MSC_Type_Identifier,
@TargetEntityType MSC_Type_EntityType,
@TargetEntityID MSC_Type_Identifier,
@AttributeName varchar(32),
@CreatedBy MSC_Type_Identifier
)
AS    
/** 
 * This stored procedure is used to update the automatic relationship
 * for an entity.
 */
    DECLARE @AutoType MSC_Type_RelationshipType
    exec MSC_SP_GetAutomaticRelationshipType @SourceEntityType, @AttributeName, @AutoType OUTPUT

    UPDATE MSC_Relationships
       SET _deactivated=GETDATE()
     WHERE SourceEntityType=@SourceEntityType
       AND SourceEntity=@SourceEntityID
       AND RelationshipType=@AutoType
       AND TargetEntityType=@TargetEntityType
       AND (@TargetEntityID IS NULL
         OR TargetEntity<>@TargetEntityID)
       AND _deactivated IS NULL

    IF @TargetEntityID IS NOT NULL
    BEGIN
        IF NOT EXISTS(SELECT *
                        FROM MSC_Relationships
                       WHERE SourceEntityType=@SourceEntityType
                         AND SourceEntity=@SourceEntityID
                         AND RelationshipType=@AutoType
                         AND TargetEntityType=@TargetEntityType
                         AND TargetEntity=@TargetEntityID
                         AND _deactivated IS NULL)
        BEGIN
            EXEC MSC_SP_AddRelationship @AutoType, @SourceEntityType, @SourceEntityID,
                                     @TargetEntityType, @TargetEntityID, @CreatedBy
        END
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

setuser N'fision'
GO


CREATE FUNCTION udf_ChooseMax (@a int, @b int)
RETURNS INT
AS
BEGIN
DECLARE @i int
  IF @a > @b
    SET @i = @a
  ELSE
    SET @i = @b
RETURN (@i)
END


GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

setuser N'fision'
GO


CREATE FUNCTION udf_GetPrivileges(@userid int, @categoryid int, @classid int)
RETURNS INT
AS
BEGIN
DECLARE @r int
  SET @r = 0
  SELECT @r = 2
    FROM categories AS c
    WHERE c.categoryid = @categoryid AND c.userid = @userid;
  IF NOT @r = 2
    SELECT @r = ginyuu.udf_ChooseMax(em.isstudent * c.allowstudent, ginyuu.udf_ChooseMax(em.ista * c.allowta, em.isprofessor * c.allowprofessor))
    FROM enrollments AS em, categories AS c
    WHERE em.classid = @classid AND em.userid = @userid AND c.categoryid = @categoryid
RETURN (@r)
END




GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

setuser N'fision'
GO


CREATE FUNCTION udf_IsAllowed
(
  @requiredpermission INT,
  @userid INT,
  @classid INT,
  @allowstudent INT,
  @allowta INT,
  @allowprofessor INT
)
RETURNS bit AS  
BEGIN
DECLARE @b bit

IF
(
 EXISTS
    (
      SELECT * FROM enrollments AS em
      WHERE
      (
        em.userid = @userid
        AND
        (@classid IS NULL OR em.classid = @classid)
        AND
        (
          (em.isstudent * @allowstudent >= @requiredpermission)
          OR
          (em.ista * @allowta >= @requiredpermission)
          OR
          (em.isprofessor * @allowprofessor >= @requiredpermission)
        )
      )  
    )
)
  SET @b = 1
ELSE
  SET @b = 0
RETURN @b

END



GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO