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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[braintrust].[MSC_T_Unique_Class]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [braintrust].[MSC_T_Unique_Class]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[braintrust].[MSC_T_Unique_Department]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [braintrust].[MSC_T_Unique_Department]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[braintrust].[MSC_T_Unique_Person]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [braintrust].[MSC_T_Unique_Person]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[braintrust].[MSC_T_Unique_Subject]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [braintrust].[MSC_T_Unique_Subject]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[braintrust].[MSC_T_Unique_User]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [braintrust].[MSC_T_Unique_User]
GO

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

if exists (select * from dbo.sysobjects where id = object_id(N'[braintrust].[trackreq]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [braintrust].[trackreq]
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'[braintrust].[MSC_Rule_CheckDataType]') and OBJECTPROPERTY(id, N'IsRule') = 1)
drop rule [braintrust].[MSC_Rule_CheckDataType]
GO

setuser N'braintrust'
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'braintrust'
GO

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

setuser
GO

setuser N'braintrust'
GO

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

setuser
GO

setuser N'braintrust'
GO

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

setuser
GO

setuser N'braintrust'
GO

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

setuser
GO

setuser N'braintrust'
GO

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

setuser
GO

CREATE TABLE [braintrust].[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 [braintrust].[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 [braintrust].[MSC_E_department] (
	[_id] [MSC_Type_Identifier] NOT NULL ,
	[A_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[_deactivated] [smalldatetime] NULL ,
	[A_acronym] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[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_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 ,
	[A_phonenumber] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[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 [braintrust].[MSC_E_subject] (
	[_id] [MSC_Type_Identifier] NOT NULL ,
	[A_name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[A_acronym] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[_deactivated] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[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 [braintrust].[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 [braintrust].[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 ,
	[Required] [bit] NULL ,
	[UniqueValue] [bit] NULL ,
	[Summary] [bit] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[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 [braintrust].[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 [braintrust].[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 [braintrust].[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 [braintrust].[class] (
	[classid] [int] NOT NULL ,
	[classname] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[lecturerId] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[classprereq] (
	[classid] [int] NOT NULL ,
	[prereqid] [int] NOT NULL ,
	[prereqname] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[classprereq1] (
	[classid] [int] NOT NULL ,
	[prereqid] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[options] (
	[listid] [int] NOT NULL ,
	[classid] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[studentoldgrades] (
	[userid] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[classid] [int] NOT NULL ,
	[semester] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[grade] [float] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[studenttrack] (
	[userid] [int] NOT NULL ,
	[trackid] [int] NULL ,
	[advisorid] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[studenttrack1] (
	[userid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[trackid] [int] NULL ,
	[advisorid] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[techelective] (
	[classid] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[track] (
	[trackid] [int] NOT NULL ,
	[trackname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cooltrackname] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [braintrust].[trackreq] (
	[trackid] [int] NOT NULL ,
	[reqid] [int] NULL ,
	[indicationof] [int] NULL ,
	[classid] [int] NULL ,
	[electnum] [int] NULL ,
	[listid] [int] NULL ,
	[listnum] [int] NULL 
) ON [PRIMARY]
GO

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

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

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

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

ALTER TABLE [braintrust].[MSC_E_subject] WITH NOCHECK ADD 
	CONSTRAINT [PK_MSC_E_subject] PRIMARY KEY  CLUSTERED 
	(
		[_id]
	)  ON [PRIMARY] 
GO

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

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

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

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

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

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

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

ALTER TABLE [dbo].[classprereq] WITH NOCHECK ADD 
	CONSTRAINT [PK_classprereq] PRIMARY KEY  CLUSTERED 
	(
		[classid],
		[prereqid]
	)  ON [PRIMARY] 
GO

ALTER TABLE [braintrust].[MSAL_Events] WITH NOCHECK ADD 
	CONSTRAINT [DF__MSAL_Even__perso__2704CA5F] DEFAULT (0) FOR [personID],
	CONSTRAINT [DF__MSAL_Even__dateT__27F8EE98] DEFAULT (getdate()) FOR [dateTimeStamp]
GO

ALTER TABLE [braintrust].[MSC_EntityTypeAttributes] WITH NOCHECK ADD 
	CONSTRAINT [DF__MSC_Entit__Searc__7CA47C3F] DEFAULT (1) FOR [Searchable],
	CONSTRAINT [DF__MSC_Entit__Requi__7D98A078] DEFAULT (0) FOR [Required],
	CONSTRAINT [DF__MSC_Entit__Uniqu__7E8CC4B1] DEFAULT (0) FOR [UniqueValue],
	CONSTRAINT [DF__MSC_Entit__Summa__7F80E8EA] DEFAULT (0) FOR [Summary]
GO

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

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

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

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

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

setuser N'braintrust'
GO

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

setuser
GO

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

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

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

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

ALTER TABLE [braintrust].[MSC_E_subject] ADD 
	CONSTRAINT [FK_MSC_E_subject__ID] FOREIGN KEY 
	(
		[_id]
	) REFERENCES [braintrust].[MSC_Entities] (
		[EntityID]
	)
GO

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

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

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

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

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

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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 OFF 
GO
SET ANSI_NULLS ON 
GO

setuser N'braintrust'
GO


CREATE PROCEDURE MSC_SP_Add_Relationship
(
@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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
GO

CREATE PROCEDURE MSC_SP_InstallBootstrap (
			@chunk varchar(8000) ) AS
			Execute(@chunk)
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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'braintrust'
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 OFF 
GO
SET ANSI_NULLS ON 
GO

setuser N'braintrust'
GO


CREATE TRIGGER MSC_T_Unique_Class
    ON MSC_E_Class
   FOR UPDATE,INSERT
    AS
    IF EXISTS(SELECT *
                FROM MSC_E_Class, inserted
               WHERE MSC_E_Class._deactivated IS NULL
                 AND MSC_E_Class._id <> inserted._id
                 AND inserted.A_sectionkey IS NOT NULL
                 AND MSC_E_Class.A_sectionkey=inserted.A_sectionkey)
    BEGIN
        RAISERROR('The same value of sectionkey already exists in the database for entity Class',16,1) WITH SETERROR
        ROLLBACK TRANSACTION
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

setuser N'braintrust'
GO



CREATE TRIGGER MSC_T_Unique_Department
    ON MSC_E_Department
   FOR UPDATE,INSERT
    AS
    IF EXISTS(SELECT *
                FROM MSC_E_Department, inserted
               WHERE MSC_E_Department._deactivated IS NULL
                 AND MSC_E_Department._id <> inserted._id
                 AND inserted.A_acronym IS NOT NULL
                 AND MSC_E_Department.A_acronym=inserted.A_acronym)
    BEGIN
        RAISERROR('The same value of acronym already exists in the database for entity Department',16,1) WITH SETERROR
        ROLLBACK TRANSACTION
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

setuser N'braintrust'
GO

CREATE TRIGGER MSC_T_Unique_Person
    ON MSC_E_Person
   FOR UPDATE,INSERT
    AS
    IF EXISTS(SELECT *
                FROM MSC_E_Person, inserted
               WHERE MSC_E_Person._deactivated IS NULL
                 AND MSC_E_Person._id <> inserted._id
                 AND inserted.A_cuid IS NOT NULL
                 AND MSC_E_Person.A_cuid=inserted.A_cuid)
    BEGIN
        RAISERROR('The same value of cuid already exists in the database for entity Person',16,1) WITH SETERROR
        ROLLBACK TRANSACTION
    END
    IF EXISTS(SELECT *
                FROM MSC_E_Person, inserted
               WHERE MSC_E_Person._deactivated IS NULL
                 AND MSC_E_Person._id <> inserted._id
                 AND inserted.A_cunixid IS NOT NULL
                 AND MSC_E_Person.A_cunixid=inserted.A_cunixid)
    BEGIN
        RAISERROR('The same value of cunixid already exists in the database for entity Person',16,1) WITH SETERROR
        ROLLBACK TRANSACTION
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

setuser N'braintrust'
GO


CREATE TRIGGER MSC_T_Unique_Subject
    ON MSC_E_Subject
   FOR UPDATE,INSERT
    AS
    IF EXISTS(SELECT *
                FROM MSC_E_Subject, inserted
               WHERE MSC_E_Subject._deactivated IS NULL
                 AND MSC_E_Subject._id <> inserted._id
                 AND inserted.A_acronym IS NOT NULL
                 AND MSC_E_Subject.A_acronym=inserted.A_acronym)
    BEGIN
        RAISERROR('The same value of acronym already exists in the database for entity Subject',16,1) WITH SETERROR
        ROLLBACK TRANSACTION
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

setuser N'braintrust'
GO



CREATE TRIGGER MSC_T_Unique_User
    ON MSC_E_User
   FOR UPDATE,INSERT
    AS
    IF EXISTS(SELECT *
                FROM MSC_E_User, inserted
               WHERE MSC_E_User._deactivated IS NULL
                 AND MSC_E_User._id <> inserted._id
                 AND inserted.A_username IS NOT NULL
                 AND MSC_E_User.A_username=inserted.A_username)
    BEGIN
        RAISERROR('The same value of username already exists in the database for entity User',16,1) WITH SETERROR
        ROLLBACK TRANSACTION
    END

GO
setuser
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO