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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

setuser
GO

setuser N'4guys1girl'
GO

EXEC sp_bindrule N'[4guys1girl].[MSC_Rule_CheckDataType]', N'[MSC_Type_DataType]'
GO

setuser
GO

setuser N'4guys1girl'
GO

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

setuser
GO

setuser N'4guys1girl'
GO

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

setuser
GO

setuser N'4guys1girl'
GO

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

setuser
GO

CREATE TABLE [4guys1girl].[ClassInfo] (
	[ClassID] [int] IDENTITY (1, 1) NOT NULL ,
	[ClassName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Size] [smallint] NULL ,
	[Finals] [float] NULL ,
	[Midterms] [float] NULL ,
	[Homeworks] [float] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[Files] (
	[FileID] [int] IDENTITY (1, 1) NOT NULL ,
	[FileLocation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[VersionNum] [int] NULL ,
	[StudentHomeworkID] [int] NULL ,
	[TimeStamp] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[HomeworkInfo] (
	[HomeworkID] [int] IDENTITY (1, 1) NOT NULL ,
	[HomeworkNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeworkDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassID] [int] NULL ,
	[HomeworkDue] [smalldatetime] NULL ,
	[HomeworkWorth] [int] NULL ,
	[NumberGraded] [smallint] NULL ,
	[Type] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[StudentClass] (
	[StudentClassID] [int] IDENTITY (1, 1) NOT NULL ,
	[StudentID] [int] NULL ,
	[ClassID] [int] NULL ,
	[Permissions] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[StudentHomework] (
	[StudentHomeworkID] [int] IDENTITY (1, 1) NOT NULL ,
	[StudentID] [int] NULL ,
	[HomeworkID] [int] NULL ,
	[Grade] [int] NULL ,
	[Comments] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[GradedBy] [nvarchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[StudentInfo] (
	[StudentID] [int] IDENTITY (1, 1) NOT NULL ,
	[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Email] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[StudentsInClasses] (
	[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[all works] (
	[StudentID] [int] NULL ,
	[HomeworkID] [int] NOT NULL ,
	[Type] [int] NULL ,
	[ClassID] [int] NOT NULL ,
	[Permissions] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [int] NULL ,
	[Grade] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[homework per student per class] (
	[CUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassID] [int] NOT NULL ,
	[StudentID] [int] NULL ,
	[HomeworkID] [int] NOT NULL ,
	[HomeworkNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeworkDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeworkDue] [smalldatetime] NULL ,
	[HomeworkWorth] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[loginclasses] (
	[CUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Permissions] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[studenthomeworkid] (
	[StudentID] [int] NULL ,
	[HomeworkID] [int] NULL ,
	[StudentHomeworkID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[ta_homeworks for class] (
	[ClassID] [int] NULL ,
	[HomeworkNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeworkDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeworkDue] [smalldatetime] NULL ,
	[HomeworkWorth] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[ta_students in class] (
	[CUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Permissions] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassID] [int] NOT NULL ,
	[HomeworkNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeworkDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeworkDue] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[test grades] (
	[CUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassID] [int] NOT NULL ,
	[HomeworkNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeworkDescription] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Grade] [int] NULL ,
	[HomeworkWorth] [int] NULL ,
	[HomeworkDue] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[test homeworks uploaded] (
	[CUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[HomeworkNumber] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FileLocation] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[VersionNum] [int] NULL ,
	[TimeStamp] [smalldatetime] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [4guys1girl].[top 1] (
	[StudentID] [int] NOT NULL ,
	[CUID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[LastName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FirstName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClassID] [int] NULL ,
	[Permissions] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Status] [int] NULL 
) ON [PRIMARY]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

setuser N'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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'4guys1girl'
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