proc_ar_BumpCacheInvalidationCounter - Final Solution.

This Script will restore all missing stored procedures:

1. Please copy the entire script in New Query on your SharedServices_DB.
2. Change USE [SharedServices_DB] to your Database name!!
3. Run the script!

NOTE:
Grant the following rights in shared services:
Default Content Access Account:Manage Analytics, Manage Audiences, Manage User Profiles, Personal Features, Personal Site, Set Permissions
Search Service Account: Manage Analytics, Manage Audiences, Manage User Profiles, Personal Features, Personal Site, Set Permission


USE [SharedServices_DB]

GO/****** Object: StoredProcedure [dbo].[proc_ar_AddOrInsertLocalizedNameForMetadataObjectId] Script Date: 06/23/2008 08:03:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_AddOrInsertLocalizedNameForMetadataObjectId] ( @MetadataObjectId int, @LocalizedName nvarchar (255), @LCID int, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION
-- Check for MaxDECLARE @LocNamesForObject intSET @LocNamesForObject = (SELECT COUNT (Id) FROM AR_LocalizedName WHERE MetadataObjectId = @MetadataObjectId)IF (@LocNamesForObject >= 100)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Add or insertDELETE FROM AR_LocalizedNameWITH (HOLDLOCK)WHERE MetadataObjectId = @MetadataObjectId AND LCID = @LCID
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
INSERT INTO AR_LocalizedNameWITH (HOLDLOCK) (MetadataObjectId, LocalizedName, LCID) VALUES (@MetadataObjectId, @LocalizedName, @LCID)
IF (@@ROWCOUNT != 1)BEGIN SET @ErrorCode = -1 GOTO ON_ERROREND
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_AddOrInsertPropertyForMetadataObjectId] Script Date: 06/23/2008 08:04:00 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_AddOrInsertPropertyForMetadataObjectId] ( @MetadataObjectId int, @Name nvarchar (255), @Value sql_variant, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
-- check object existsIF NOT EXISTS (SELECT Id FROM AR_MetadataObject WHERE Id=@MetadataObjectId)BEGIN SET @ErrorCode = -2 GOTO ON_ERROREND
-- check for MaxDECLARE @PropertiesForObject intSET @PropertiesForObject = (SELECT COUNT (Id) FROM AR_Property WHERE MetadataObjectId = @MetadataObjectId)IF( @PropertiesForObject >= 50)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Add or insertBEGIN TRANSACTIONDELETE FROM AR_PropertyWITH (HOLDLOCK)WHERE AR_Property.MetadataObjectId = @MetadataObjectId AND AR_Property.Name = @Name
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
INSERT INTO AR_PropertyWITH (HOLDLOCK) (MetadataObjectId, Name, Value) VALUES (@MetadataObjectId, @Name, @Value)
IF (@@ROWCOUNT != 1)BEGIN SET @ErrorCode = -1 GOTO ON_ERROREND
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_BumpCacheInvalidationCounter] Script Date: 06/23/2008 08:04:00 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_BumpCacheInvalidationCounter] ( @MetadataObjectType [nvarchar] (250), @ObjectCache [bit])AS
BEGIN TRANSACTIONDECLARE @count [int]IF @ObjectCache = 1BEGIN SELECT @count = ObjectCacheCounter FROM AR_CacheCounters WHERE MetadataObjectType = @MetadataObjectType IF @count >= 2147483646 SET @count = 0 UPDATE AR_CacheCounters SET ObjectCacheCounter = (@count+1) WHERE MetadataObjectType = @MetadataObjectTypeENDELSEBEGIN SELECT @count = RelationshipCacheCounter FROM AR_CacheCounters WHERE MetadataObjectType = @MetadataObjectType IF @count >= 2147483646 SET @count = 0 UPDATE AR_CacheCounters SET RelationshipCacheCounter = (@count+1) WHERE MetadataObjectType = @MetadataObjectTypeEND
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_BumpMetadataObjectVersionCounter] Script Date: 06/23/2008 08:04:00 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_BumpMetadataObjectVersionCounter] ( @MetadataObjectId int)AS
BEGIN TRANSACTION
DECLARE @count [int]
SELECT @count = VersionFROM AR_MetadataObjectWITH (HOLDLOCK)WHERE Id = @MetadataObjectId
IF @count >= 2147483646 SET @count = 0
UPDATE AR_MetadataObjectSET Version = (@count+1) WHERE Id = @MetadataObjectId
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_ClearAccessControlEntriesForMetadataObject] Script Date: 06/23/2008 08:04:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_ClearAccessControlEntriesForMetadataObject] ( @MetadataObjectId [int])AS
DELETE FROM AR_MetadataObjectSecurity WHERE MetadataObjectId = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_CopyAccessControlEntriesForMetadataObjectId] Script Date: 06/23/2008 08:04:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CopyAccessControlEntriesForMetadataObjectId] ( @SourceMetadataObjectId int, @DestinationMetadataObjectId int)AS
BEGIN TRANSACTION
IF( @SourceMetadataObjectId != @DestinationMetadataObjectId)BEGIN
EXEC proc_ar_ClearAccessControlEntriesForMetadataObject @DestinationMetadataObjectId
INSERT INTO AR_MetadataObjectSecurity (MetadataObjectId, IdentityName, DisplayName, RawSid, Rights) SELECT @DestinationMetadataObjectId, IdentityName, DisplayName, RawSid, Rights FROM AR_MetadataObjectSecurity WHERE MetadataObjectId = @SourceMetadataObjectIdEND
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateAction] Script Date: 06/23/2008 08:04:01 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateAction] ( @Name nvarchar(50), @IsCached bit, @EntityId int, @Position tinyint, @IsDisplayed bit, @IsOpenedInNewWindow bit, @Icon nvarchar(255), @Url nvarchar(255), @CreatedId int OUTPUT, @ErrorCode int OUTPUT
)AS
DECLARE @MaxActions intDECLARE @NoOfActions int
BEGIN TRAN
SET @ErrorCode = 0SET @MaxActions = 20
-- Lock Action table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 7 -- action table
-- Check for max limitSET @NoOfActions = (SELECT count(*) FROM AR_Action WHERE AR_Action.EntityId = @EntityId)
IF (@NoOfActions = @MaxActions)BEGIN SET @ErrorCode = -3 --Max no of actions exceeded GOTO ON_ERROREND
-- Check for duplicatesSELECT AR_MetadataObject.IdFROM AR_ActionINNER JOIN AR_MetadataObjectON AR_Action.Id = AR_MetadataObject.IdWHERE AR_Action.EntityId = @EntityId AND AR_MetadataObject.Name = @Name
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
--For creating an Action we need to create two rows--one in the metadataobject table--and other in the actions tableINSERT INTO AR_MetadataObject(Name, IsCached) VALUES (@Name, @IsCached)SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
SET @CreatedId = @@IDENTITYINSERT INTO AR_Action(Id, EntityId, Position, IsDisplayed, IsOpenedInNewWindow, Icon, Url) VALUES (@CreatedId, @EntityId, @Position, @IsDisplayed, @IsOpenedInNewWindow, @Icon, @Url)SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Copy ACL -- not individually securable --
COMMIT TRANSACTIONRETURN
ON_ERROR:ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateActionParameter] Script Date: 06/23/2008 08:04:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateActionParameter] ( @Name nvarchar(50), @IsCached bit, @ActionId int, @Index tinyint, @CreatedId int OUTPUT, @ErrorCode int OUTPUT)AS
BEGIN TRAN
-- Lock ActionParameter table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 13 -- action parameter table
-- validate inputDECLARE @MaxActionParameters intDECLARE @NoOfActionParameters int
SET @ErrorCode = 0SET @MaxActionParameters = 10
SET @NoOfActionParameters = (SELECT count(*) FROM AR_ActionParameter WHERE AR_ActionParameter.ActionId = @ActionId)
IF (@NoOfActionParameters = @MaxActionParameters)BEGIN SET @ErrorCode = -3 --Max no of actionparameters exceeded GOTO ON_ERROREND
-- Check for duplicatesSELECT AR_MetadataObject.IdFROM AR_ActionParameterINNER JOIN AR_MetadataObjectON AR_ActionParameter.Id = AR_MetadataObject.IdWHERE AR_ActionParameter.ActionId = @ActionId AND AR_MetadataObject.Name = @Name
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
--For creating an ActionParamter we need to create two rows--one in the metadataobject table--and other in the actionparameters table
INSERT INTO AR_MetadataObject(Name, IsCached) VALUES (@Name, @IsCached)SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
SET @CreatedId = @@IDENTITYINSERT INTO AR_ActionParameterVALUES (@CreatedId, @ActionId, @Index)SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANRETURN
ON_ERROR:ROLLBACK TRANRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateAssociation] Script Date: 06/23/2008 08:04:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateAssociation] ( @Name nvarchar(255), @IsCached bit, @ReturnTypeDescriptorId int, @SourceEntityIds nvarchar(255), @DestinationEntityId int, @CreatedId int OUTPUT, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
DECLARE @MethodId intSELECT @MethodId=MethodId FROM AR_Parameter WHERE Id IN (SELECT ParameterId FROM AR_TypeDescriptor WHERE Id=@ReturnTypeDescriptorId)
DECLARE @EntityId intSELECT @EntityId=ClassId FROM AR_Method WHERE Id=@MethodId
DECLARE @SystemId int SELECT @SystemId=SystemId FROM AR_Class WHERE Id=@EntityId
BEGIN TRANSACTION-- Lock Association table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 8 -- association table
-- Check for duplicates; an association must be uniquely named within a SystemSELECT AR_Association.IdFROM AR_Association INNER JOIN AR_MethodInstanceON AR_Association.Id = AR_MethodInstance.IdINNER JOIN AR_MetadataObjectON AR_MethodInstance.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_MethodInstance.MethodId IN (SELECT Id FROM AR_Method WHERE ClassId IN (SELECT Id FROM AR_Class WHERE SystemId=@SystemId))
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check object specific constraintsDECLARE @AssociationsAsDestinationForEntity intSET @AssociationsAsDestinationForEntity = (SELECT DISTINCT COUNT (AssociationId) FROM AR_AssociationMember WHERE EntityId = @DestinationEntityId AND EntityRole = 1)IF(@AssociationsAsDestinationForEntity >= 1000)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND--IF(@EntityId != @DestinationEntityId)--BEGIN -- destination entity must be the one containing the method that provides the association functionality-- SET @ErrorCode = -400 -- GOTO ON_ERROR--END
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
SET @CreatedId = @@IDENTITY
-- Insert into MethodInstanceINSERT INTO AR_MethodInstance (Id, MethodId, Type, ReturnTypeDescriptorId)VALUES (@CreatedId, @MethodId, 4, @ReturnTypeDescriptorId)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Insert into AssociationINSERT INTO AR_Association (Id)VALUES (@CreatedId)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Insert Destination into AssociationMemberINSERT INTO AR_AssociationMember (AssociationId, EntityId, EntityRole)VALUES (@CreatedId, @DestinationEntityId, 1)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Insert Sources into AssociationMemberDECLARE @currentId nvarchar (255)DECLARE @index intDECLARE @oldIndex intSET @oldIndex = 0SET @index=CHARINDEX(',',@SourceEntityIds, @oldIndex)WHILE(@oldIndex < currentid =" SUBSTRING(@SourceEntityIds," oldindex =" @index+1" associationsassourceforentity =" (SELECT" entityid =" @DestinationEntityId" entityrole =" 0)">= 1000) BEGIN SET @ErrorCode = -3 GOTO ON_ERROR END
INSERT INTO AR_AssociationMember (AssociationId, EntityId, EntityRole) VALUES (@CreatedId, CAST(@currentId AS int), 0)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
SET @index=CHARINDEX(',',@SourceEntityIds, @oldIndex)END
-- Copy ACLEXEC proc_ar_CopyAccessControlEntriesForMetadataObjectId @EntityId, @CreatedId
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateEntity] Script Date: 06/23/2008 08:04:02 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateEntity] ( @Name nvarchar(255), @IsCached bit, @SystemId int, @EstimatedInstanceCount int, @CreatedId int OUTPUT, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock System table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 4 -- entity table
-- Check for duplicatesSELECT AR_Entity.IdFROM AR_EntityINNER JOIN AR_ClassON AR_Entity.Id = AR_Class.IdINNER JOIN AR_MetadataObjectON AR_Class.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_Class.SystemId = @SystemId
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check for MaxDECLARE @EntitiesForSystem intSET @EntitiesForSystem = (SELECT COUNT (AR_Entity.Id) FROM AR_Entity INNER JOIN AR_Class ON AR_Entity.Id = AR_Class.Id WHERE AR_Class.SystemId = @SystemId)IF(@EntitiesForSystem >= 200)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERRORSET @CreatedId = @@IDENTITY
-- Insert into ClassINSERT INTO AR_Class (Id, SystemId) VALUES (@CreatedId, @SystemId)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Insert into EntityINSERT INTO AR_Entity (Id,EstimatedInstanceCount)VALUES (@CreatedId,@EstimatedInstanceCount)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Copy ACLEXEC proc_ar_CopyAccessControlEntriesForMetadataObjectId @SystemId, @CreatedId
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateFilterDescriptor] Script Date: 06/23/2008 08:04:03 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateFilterDescriptor] ( @Name nvarchar(255), @IsCached bit, @MethodId int, @TypeName nvarchar(255), @CreatedId int OUTPUT, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock FilterDescriptor table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 9 -- filterdescriptor table
-- Check for duplicatesSELECT AR_FilterDescriptor.IdFROM AR_FilterDescriptorINNER JOIN AR_MetadataObjectON AR_FilterDescriptor.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_FilterDescriptor.MethodId = @MethodId
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check for MaxDECLARE @FilterDescriptorsForMethod intSET @FilterDescriptorsForMethod = (SELECT COUNT (Id) FROM AR_FilterDescriptor WHERE MethodId = @MethodId)IF( @FilterDescriptorsForMethod >= 200)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERRORSET @CreatedId = @@IDENTITY
-- Insert into FilterDescriptorINSERT INTO AR_FilterDescriptor (Id, TypeName, MethodId)VALUES (@CreatedId, @TypeName, @MethodId)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Copy ACL -- not individually securable --
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateIdentifier] Script Date: 06/23/2008 08:04:03 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateIdentifier] ( @Name nvarchar(255), @IsCached bit, @EntityId int, @TypeName nvarchar(255), @CreatedId int OUTPUT, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock Identifier table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 5 -- identifier table
-- Check for duplicatesSELECT AR_Identifier.IdFROM AR_IdentifierINNER JOIN AR_MetadataObjectON AR_Identifier.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_Identifier.EntityId = @EntityId
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check for MaxDECLARE @IdentifiersForEntity intSET @IdentifiersForEntity = (SELECT COUNT(Id) FROM AR_Identifier WHERE EntityId = @EntityId)IF( @IdentifiersForEntity >= 20)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
SET @CreatedId = @@IDENTITY
DECLARE @OrdinalNumber intIF EXISTS (SELECT TOP 1 * FROM AR_Identifier WHERE EntityId = @EntityId)BEGIN SELECT TOP 1 @OrdinalNumber = OrdinalNumber+1 FROM AR_Identifier WHERE EntityId = @EntityId ORDER BY OrdinalNumber DESCENDELSEBEGIN SET @OrdinalNumber = 0END
-- Insert into IdentifierINSERT INTO AR_Identifier (Id, TypeName, EntityId, OrdinalNumber) VALUES (@CreatedId, @TypeName, @EntityId, @OrdinalNumber)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Copy ACL -- not individually securable --
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateMethod] Script Date: 06/23/2008 08:04:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateMethod] ( @Name nvarchar(255), @IsCached bit, @ClassId int, @IsStatic bit, @CreatedId int OUTPUT, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock System table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 6 -- method table
-- Check for duplicatesSELECT AR_Method.IdFROM AR_MethodINNER JOIN AR_MetadataObjectON AR_Method.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_Method.ClassId = @ClassId
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check for MaxDECLARE @MethodsForEntity intSET @MethodsForEntity = (SELECT COUNT (Id) FROM AR_Method WHERE ClassId = @ClassId)IF (@MethodsForEntity >= 50)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
SET @CreatedId = @@IDENTITY
-- Insert into MethodINSERT INTO AR_Method (Id, ClassId, IsStatic) VALUES (@CreatedId, @ClassId, @IsStatic)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Copy ACLEXEC proc_ar_CopyAccessControlEntriesForMetadataObjectId @ClassId, @CreatedId
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateMethodInstance] Script Date: 06/23/2008 08:04:04 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateMethodInstance] ( @Name nvarchar(255), @IsCached bit, @MethodId int, @ReturnTypeDescriptorId int, @Type tinyint, @CreatedId int OUTPUT, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock MethodInstance table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 10 -- methodinstance table
-- find the class/entity this methodinstance needs to belong toDECLARE @ClassId intSELECT @ClassId=ClassId FROM AR_Method WHERE Id = @MethodId
-- Check for duplicate names in all MethodInstances in the entity/class that the given Method belongs toSELECT AR_MethodInstance.IdFROM AR_MethodInstanceINNER JOIN AR_MetadataObjectON AR_MethodInstance.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_MethodInstance.MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @ClassId)
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check for MaxDECLARE @MethodInstancesForMethod intSET @MethodInstancesForMethod = (SELECT COUNT (Id) FROM AR_MethodInstance WHERE MethodId = @MethodId)IF (@MethodInstancesForMethod >= 15)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Check object specific constraintsIF(@Type = 1) -- 'Finder' BEGIN IF EXISTS (SELECT Id FROM AR_MethodInstance WHERE Type=1 AND MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @ClassId)) BEGIN -- object specific errors start at -100, -200 etc. SET @ErrorCode = -200 --More than 1 'Finder' method instance in entity GOTO ON_ERROR ENDEND
IF(@Type = 2) -- 'SpecificFinder' BEGIN IF EXISTS (SELECT Id FROM AR_MethodInstance WHERE Type=2 AND MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @ClassId)) BEGIN SET @ErrorCode = -201 --More than 1 'SpecificFinder' method instance in entity GOTO ON_ERROR ENDEND
IF(@Type = 5) -- 'IdEnumerator' BEGIN IF EXISTS (SELECT Id FROM AR_MethodInstance WHERE Type=5 AND MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @ClassId)) BEGIN SET @ErrorCode = -202 --More than 1 'IdEnumerator' method instance in entity GOTO ON_ERROR ENDEND
DECLARE @ReturnTypeDescriptorMethodId intDECLARE @Direction intSELECT @ReturnTypeDescriptorMethodId=MethodId, @Direction=Direction FROM AR_Parameter WHERE Id IN (SELECT ParameterId FROM AR_TypeDescriptor WHERE Id = @ReturnTypeDescriptorId)IF(@ReturnTypeDescriptorMethodId != @MethodId)BEGIN SET @ErrorCode = -203 -- Return typedescriptor's parameter's method and this method instance's method are not the same GOTO ON_ERROREND
IF(@Direction = 1) -- 1 = DirectionType.InBEGIN SET @ErrorCode = -204 -- Return typedescriptor's parameter's direction is DirectionType.In GOTO ON_ERROREND
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERRORSET @CreatedId = @@IDENTITY
-- Insert into MethodInstanceINSERT INTO AR_MethodInstance (Id, MethodId, ReturnTypeDescriptorId, Type)VALUES (@CreatedId, @MethodId, @ReturnTypeDescriptorId, @Type)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Copy ACL (from Class/Entity, not Method)-- The security ownership relationship is slightly different from the structural ownership relationshipEXEC proc_ar_CopyAccessControlEntriesForMetadataObjectId @ClassId, @CreatedId
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateParameter] Script Date: 06/23/2008 08:04:05 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateParameter] ( @Name nvarchar(255), @IsCached bit, @MethodId int, @Direction tinyint, @TypeReflectorTypeName nvarchar(255), @CreatedId int OUTPUT, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock Parameter table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 11 -- parameter table
-- Check for duplicatesSELECT AR_Parameter.IdFROM AR_ParameterINNER JOIN AR_MetadataObjectON AR_Parameter.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_Parameter.MethodId = @MethodId
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check for MaxDECLARE @ParametersForMethod intSET @ParametersForMethod = (SELECT COUNT (Id) FROM AR_Parameter WHERE MethodId = @MethodId)IF (@ParametersForMethod >= 30)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Check object specific constraintsIF(@Direction = 4) --DirectionType.ReturnBEGIN IF EXISTS (SELECT Id FROM AR_Parameter WHERE MethodId = @MethodId AND Direction=4) BEGIN -- object specific errors start at -100, -200 etc. SET @ErrorCode = -100 --More than 1 'Return' parameter GOTO ON_ERROR ENDEND
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
SET @CreatedId = @@IDENTITY
DECLARE @OrdinalNumber intIF EXISTS (SELECT TOP 1 * FROM AR_Parameter WHERE MethodId = @MethodId)BEGIN SELECT TOP 1 @OrdinalNumber = OrdinalNumber+1 FROM AR_Parameter WHERE MethodId = @MethodId ORDER BY OrdinalNumber DESCENDELSEBEGIN SET @OrdinalNumber = 0END
-- Insert into FilterDescriptorINSERT INTO AR_Parameter (Id, MethodId, TypeReflectorTypeName, Direction, OrdinalNumber)VALUES (@CreatedId, @MethodId, @TypeReflectorTypeName, @Direction, @OrdinalNumber)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Copy ACL -- not individually securable --
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateSystem] Script Date: 06/23/2008 08:04:05 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateSystem] ( @Name nvarchar(255), @IsCached bit, @SystemUtilityTypeName nvarchar(255), @ConnectionManagerTypeName nvarchar(255), @EntityInstanceTypeName nvarchar(255), @CreatedId int OUTPUT, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock System table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 2 -- system table
-- Check for duplicatesSELECT AR_System.IdFROM AR_SystemINNER JOIN AR_MetadataObjectON AR_System.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERRORSET @CreatedId = @@IDENTITY
-- Insert into SystemINSERT INTO AR_System (Id, SystemUtilityTypeName, ConnectionFactoryTypeName, SystemEntityTypeName) VALUES (@CreatedId, @SystemUtilityTypeName, @ConnectionManagerTypeName, @EntityInstanceTypeName)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Copy ACLDECLARE @SourceMetadataObjectId intSELECT @SourceMetadataObjectId = Id FROM AR_ApplicationRegistryEXEC proc_ar_CopyAccessControlEntriesForMetadataObjectId @SourceMetadataObjectId, @CreatedId
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateSystemInstance] Script Date: 06/23/2008 08:04:05 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateSystemInstance] ( @Name nvarchar(255), @IsCached bit, @SystemId int, @CreatedId int OUTPUT, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock SystemInstance table for concurrent inserts or updatesSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 3 -- systeminstance table
-- Check for duplicates globallySELECT AR_SystemInstance.IdFROM AR_SystemInstanceINNER JOIN AR_MetadataObjectON AR_SystemInstance.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check for MaxDECLARE @SystemInstancesForSystem intSET @SystemInstancesForSystem = (SELECT COUNT (Id) FROM AR_SystemInstance WHERE SystemId = @SystemId)IF( @SystemInstancesForSystem >= 300)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
SET @CreatedId = @@IDENTITY
-- Insert into SystemInstanceINSERT INTO AR_SystemInstance (Id, SystemId) VALUES (@CreatedId, @SystemId)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Copy ACLEXEC proc_ar_CopyAccessControlEntriesForMetadataObjectId @SystemId, @CreatedId
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_CreateTypeDescriptor] Script Date: 06/23/2008 08:04:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_CreateTypeDescriptor] ( @Name nvarchar(255), @IsCached bit, @ParameterId int, @ParentTypeDescriptorId int, @TypeName nvarchar(255), @IdentifierId int, @FilterDescriptorId int, @IsCollection bit, @CreatedId int OUTPUT, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock TypeDescriptor table for concurrent insertsSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 12 -- typedescriptor table
-- Check for duplicatesSELECT AR_TypeDescriptor.IdFROM AR_TypeDescriptorINNER JOIN AR_MetadataObjectON AR_TypeDescriptor.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_TypeDescriptor.ParentTypeDescriptorId = @ParentTypeDescriptorId AND AR_TypeDescriptor.ParameterId = @ParameterId
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check for MaxDECLARE @ChildTypeDescriptors intDECLARE @TypeDescriptorsRelatedToFilterDescriptor intSET @ChildTypeDescriptors = (SELECT COUNT (Id) FROM AR_TypeDescriptor WHERE ParentTypeDescriptorId = @ParentTypeDescriptorId AND ParameterId = @ParameterId AND @ParentTypeDescriptorId IS NOT NULL)SET @TypeDescriptorsRelatedToFilterDescriptor = (SELECT COUNT (Id) FROM AR_TypeDescriptor WHERE FilterDescriptorId = @FilterDescriptorId AND @FilterDescriptorId IS NOT NULL)IF (@ChildTypeDescriptors >= 300 OR @TypeDescriptorsRelatedToFilterDescriptor >= 10)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Check object specific constraintsDECLARE @ParentIsCollection bitIF(@IsCollection=1 AND @ParentTypeDescriptorId IS NOT NULL)BEGIN SELECT @ParentIsCollection=IsCollection FROM AR_TypeDescriptor WHERE Id = @ParentTypeDescriptorId IF (@ParentIsCollection = 1) BEGIN SET @ErrorCode = -305 -- cannot have parent-child typedescriptors with IsCollection set GOTO ON_ERROR ENDEND
IF(@ParentTypeDescriptorId IS NOT NULL AND EXISTS (SELECT Id FROM AR_TypeDescriptor WHERE ParentTypeDescriptorId = @ParentTypeDescriptorId)) -- if there is a siblingBEGIN SELECT @ParentIsCollection=IsCollection FROM AR_TypeDescriptor WHERE Id = @ParentTypeDescriptorId IF(@ParentIsCollection = 1) BEGIN SET @ErrorCode = -306 -- a type desc with IsCollection can have only one child; do not create a typedesc if the parent has isCollection set and already has a child GOTO ON_ERROR ENDEND
IF(@ParentTypeDescriptorId IS NULL AND EXISTS (SELECT Id FROM AR_TypeDescriptor WHERE ParameterId=@ParameterId AND ParentTypeDescriptorId IS NULL))BEGIN SET @ErrorCode = -302 -- cannot have multiple root type descriptors for a single parameter GOTO ON_ERROREND
DECLARE @FilterDescriptorMethodId intDECLARE @TypeDescriptorMethodId intSELECT @FilterDescriptorMethodId=MethodId FROM AR_FilterDescriptor WHERE Id=@FilterDescriptorIdSELECT @TypeDescriptorMethodId=MethodId FROM AR_Parameter WHERE Id=@ParameterIdIF(@FilterDescriptorId IS NOT NULL AND @TypeDescriptorMethodId != @FilterDescriptorMethodId)BEGIN SET @ErrorCode = -303 -- filter doesn't belong to the same method as the typedescriptor GOTO ON_ERROREND
-- Insert into MetadataObjectINSERT INTO AR_MetadataObject (Name, IsCached) VALUES (@Name, @IsCached)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERRORSET @CreatedId = @@IDENTITY
-- Insert into TypeDescriptorINSERT INTO AR_TypeDescriptor (Id, ParentTypeDescriptorId, ParameterId, TypeName, InterpretedTypeName, ContainsIdentifier, IdentifierId, ContainsFilterDescriptor, FilterDescriptorId, IsCollection)VALUES (@CreatedId, @ParentTypeDescriptorId, @ParameterId, @TypeName, @TypeName, 0, @IdentifierId, 0, @FilterDescriptorId, @IsCollection)
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Propogate Contains* flagsEXEC proc_ar_TypeDescriptorContainsFlagsFixup @CreatedId, @ErrorCode OUTPUTIF(@ErrorCode != 0) GOTO ON_ERRORUPDATE AR_MetadataObject SET Version=0 WHERE Id=@CreatedId -- reset any version increment caused by fixup
-- Copy ACL -- not individually securable --
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteActionById] Script Date: 06/23/2008 08:04:06 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteActionById] ( @Id int, @Version int, @ErrorCode int OUTPUT)AS
BEGIN TRAN
SET @ErrorCode = 0
-- Lock Action table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent deleteSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (7,13) -- action, action parameter
--before deleting make sure that the action existsSELECT IdFROM AR_ActionWITH (HOLDLOCK) -- guarantee no one else deletes the same objectWHERE AR_Action.ID = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --action not found GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- Delete the action itself and cascade delete action parametersDELETE FROM AR_Metadataobject WHERE AR_Metadataobject.Id IN (SELECT Id FROM AR_ActionParameter WHERE ActionId = @Id)SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
DELETE FROM AR_Metadataobject WHERE AR_Metadataobject.id = @IdSELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANRETURN
ON_ERROR:ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteActionParameterById] Script Date: 06/23/2008 08:04:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteActionParameterById] ( @Id int, @Version int, @ErrorCode int OUTPUT)AS
BEGIN TRAN
SET @ErrorCode = 0
-- Lock ActionParameter table for concurrent deletesSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (13) -- action parameter
-- before deleting make sure that the actionparameter existsSELECT IdFROM AR_ActionParameterWHERE AR_ActionParameter.ID = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --actionparameter not found GOTO ON_ERROREND
-- before deleting make sure there are no child objects -- there are no child objects
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- cascading deletes will take care of other tablesDELETE FROM AR_Metadataobject WHERE Id = @Id
COMMIT TRANRETURN
ON_ERROR:ROLLBACK TRANRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteAssociationById] Script Date: 06/23/2008 08:04:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteAssociationById] ( @Id int, @Version int, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION
-- Lock Association table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent delete.SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (8) -- association
-- before deleting make sure Association existsSELECT IdFROM AR_AssociationWITH (HOLDLOCK) -- guarantee no one else deletes the same objectWHERE Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objects -- no child objects --
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- cascading deletes will take care of other tablesDELETE FROM AR_DefaultValueWHERE AR_DefaultValue.MethodInstanceId = @IdSELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
DELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteDefaultValue] Script Date: 06/23/2008 08:04:07 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteDefaultValue] ( @TypeDescriptorId int, @MethodInstanceId int, @ErrorCode int OUTPUT)AS
BEGIN TRANSACTION
SET @ErrorCode = 0
-- ensure the MethodInstance and TypeDescriptor existIF NOT EXISTS (SELECT Id FROM AR_MethodInstance WHERE Id = @MethodInstanceId)BEGIN SET @ErrorCode = -2 GOTO ON_ERRORENDIF NOT EXISTS (SELECT Id FROM AR_TypeDescriptor WHERE Id = @TypeDescriptorId)BEGIN SET @ErrorCode = -2 GOTO ON_ERROREND
-- do the workDELETE FROM AR_DefaultValue WHERE MethodInstanceId = @MethodInstanceId AND TypeDescriptorId = @TypeDescriptorId
COMMIT TRANSACTIONRETURN
ON_ERROR:ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteEntityById] Script Date: 06/23/2008 08:04:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteEntityById] ( @Id int, @Version int, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION
-- Lock Entity table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent deleteSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (4,5,6,7,8) -- entity, identifier, method, action, association
-- before deleting make sure Entity existsSELECT IdFROM AR_EntityWITH (HOLDLOCK) -- guarantee no one else deletes the same objectWHERE AR_Entity.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objectsSELECT IdFROM AR_AssociationMemberWHERE EntityId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
SELECT IdFROM AR_ActionWHERE EntityId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
SELECT IdFROM AR_MethodWHERE ClassId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
SELECT IdFROM AR_IdentifierWHERE EntityId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- may be safe here to delete non-cascading children that are -- NOT individually securable by calling the appropriate child-- delete stored proc (which in turn must make sure it has no -- individuallysecurable children before succeeding), but we'll -- opt for simplicity where all delete cascades are handled in the OM
-- cascading deletes will take care of other tablesDELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteFilterDescriptorById] Script Date: 06/23/2008 08:04:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteFilterDescriptorById] ( @Id int, @Version int, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION
-- Lock FilterDescriptor table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent deleteSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (9,12) -- filterdescriptor, typedescriptor
-- before deleting make sure FilterDescriptor existsSELECT IdFROM AR_FilterDescriptorWITH (HOLDLOCK) -- guarantee no one else deletes the same objectWHERE AR_FilterDescriptor.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objects -- there are no child objects
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- update TypeDescriptor references DECLARE tdFdCursor CURSOR LOCALFORWARD_ONLYKEYSETFOR SELECT AR_TypeDescriptor.Id FROM AR_TypeDescriptor WHERE AR_TypeDescriptor.FilterDescriptorId = @Id
OPEN tdFdCursor
DECLARE @currentId int
FETCH NEXT FROM tdFdCursor INTO @currentIdWHILE(@@FETCH_STATUS=0)BEGIN -- important to run the stored proc as opposed -- to doing the updates directly, as the proc can -- take care of incrementing the version counter -- (to invalidate TypeDesc objects that may -- already exist) and other details UPDATE AR_TypeDescriptor SET FilterDescriptorId = NULL WHERE Id=@currentId EXEC proc_ar_TypeDescriptorContainsFlagsFixup @currentId, @ErrorCode OUTPUT IF(@ErrorCode != 0) GOTO ON_ERROR FETCH NEXT FROM tdFdCursor INTO @currentIdENDDEALLOCATE tdFdCursor
-- cascading deletes will take care of other tablesDELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteIdentifierById] Script Date: 06/23/2008 08:04:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteIdentifierById] ( @Id int, @Version int, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION
-- Lock Identifier table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent delete. Also-- want to ensure ordinal reordering is serialized in case-- of concurrent delete requests to IdentifiersSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (5,12) -- type descriptor
-- before deleting make sure Identifier existsSELECT IdFROM AR_IdentifierWITH (HOLDLOCK) -- guarantee no one else deletes the same objectWHERE AR_Identifier.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objects -- there are no child objects
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- update TypeDescriptor references DECLARE tdIdCursor CURSOR LOCALFORWARD_ONLYKEYSETFOR SELECT AR_TypeDescriptor.Id FROM AR_TypeDescriptor WHERE AR_TypeDescriptor.IdentifierId = @Id
OPEN tdIdCursor
DECLARE @currentId int
FETCH NEXT FROM tdIdCursor INTO @currentIdWHILE(@@FETCH_STATUS=0)BEGIN -- important to run the stored proc as opposed -- to doing the updates directly, as the proc can -- take care of incrementing the version counter -- (to invalidate TypeDesc objects that may -- already exist) and other details UPDATE AR_TypeDescriptor SET IdentifierId = NULL WHERE Id=@currentId EXEC proc_ar_TypeDescriptorContainsFlagsFixup @currentId, @ErrorCode OUTPUT IF(@ErrorCode != 0) GOTO ON_ERROR FETCH NEXT FROM tdIdCursor INTO @currentIdENDDEALLOCATE tdIdCursor
-- save key fields for ordinal number reordering DECLARE @EntityId intSELECT @EntityId = EntityId FROM AR_Identifier WHERE Id=@Id
-- cascading deletes will take care of other tablesDELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- reorder OrdinalsDECLARE ordinalNumberCursor CURSOR LOCALFORWARD_ONLYKEYSETFOR SELECT OrdinalNumber FROM AR_Identifier WHERE EntityId = @EntityId ORDER BY OrdinalNumber ASCFOR UPDATE OF OrdinalNumber
OPEN ordinalNumberCursor
DECLARE @OrdinalNumberCounter tinyintSET @OrdinalNumberCounter = 0FETCH NEXT FROM ordinalNumberCursorWHILE(@@FETCH_STATUS=0)BEGIN UPDATE AR_Identifier SET OrdinalNumber=@OrdinalNumberCounter WHERE CURRENT OF ordinalNumberCursor
EXEC proc_ar_BumpMetadataObjectVersionCounter @Id
SET @OrdinalNumberCounter = @OrdinalNumberCounter + 1 FETCH NEXT FROM ordinalNumberCursorENDDEALLOCATE ordinalNumberCursor
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteLocalizedNameForMetadataObjectByLCID] Script Date: 06/23/2008 08:04:08 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteLocalizedNameForMetadataObjectByLCID] ( @MetadataObjectId int, @LCID int, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTIONDELETE FROM AR_LocalizedNameWITH (HOLDLOCK)WHERE MetadataObjectId = @MetadataObjectId AND LCID = @LCID
IF (@@ROWCOUNT <> 1)BEGIN SET @ErrorCode = -2 GOTO ON_ERROREND
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteLocalizedNamesByMetadataObjectId] Script Date: 06/23/2008 08:04:09 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteLocalizedNamesByMetadataObjectId] ( @MetadataObjectId int, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTIONDELETE FROM AR_LocalizedNameWHERE MetadataObjectId = @MetadataObjectId
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteMethodById] Script Date: 06/23/2008 08:04:09 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteMethodById] ( @Id int, @Version int, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION
-- Lock Method table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent deleteSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (6,9) -- method, filter descriptor
-- before deleting make sure Method existsSELECT IdFROM AR_MethodWITH (HOLDLOCK) -- guarantee no one else deletes the same objectWHERE AR_Method.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objectsSELECT IdFROM AR_FilterDescriptorWHERE MethodId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
SELECT IdFROM AR_MethodInstanceWHERE MethodId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
SELECT IdFROM AR_ParameterWHERE MethodId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- may be safe here to delete non-cascading children that are -- NOT individually securable by calling the appropriate child-- delete stored proc (which in turn must make sure it has no -- individuallysecurable children before succeeding), but we'll -- opt for simplicity where all delete cascades are handled in the OM
-- cascading deletes will take care of other tablesDELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteMethodInstanceById] Script Date: 06/23/2008 08:04:09 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteMethodInstanceById] ( @Id int, @Version int, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION
-- lock method instanceSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 10 -- methodinstance table
-- before deleting make sure MethodInstance existsSELECT IdFROM AR_MethodInstanceWITH (HOLDLOCK) -- guarantee no one else deletes the same objectWHERE Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objects -- no child objects --
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- may be safe here to delete non-cascading children that are -- NOT individually securable by calling the appropriate child-- delete stored proc (which in turn must make sure it has no -- individuallysecurable children before succeeding), but we'll -- opt for simplicity where all delete cascades are handled in the OM
-- cascading deletes will take care of other tablesDELETE FROM AR_DefaultValueWHERE AR_DefaultValue.MethodInstanceId = @IdSELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
DELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteParameterById] Script Date: 06/23/2008 08:04:09 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteParameterById] ( @Id int, @Version int, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION
-- Lock Parameter table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent delete.-- We also don't want concurrent parameter deletes, because -- a parameter deletion causes an ordinal reordering -- which needs to be serializedSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (11,12) -- parameter, type descriptor
-- before deleting make sure Parameter existsSELECT IdFROM AR_ParameterWITH (HOLDLOCK) -- guarantee no one else deletes the same objectWHERE AR_Parameter.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objectsSELECT IdFROM AR_TypeDescriptorWHERE ParameterId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- save key fields for ordinal number reordering DECLARE @MethodId intSELECT @MethodId = MethodId FROM AR_Parameter WHERE Id=@Id
-- cascading deletes will take care of other tablesDELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- reorder OrdinalsDECLARE ordinalNumberCursor CURSOR LOCALFORWARD_ONLYKEYSETFOR SELECT OrdinalNumber FROM AR_Parameter WHERE MethodId = @MethodId ORDER BY OrdinalNumber ASCFOR UPDATE OF OrdinalNumber
OPEN ordinalNumberCursor
DECLARE @OrdinalNumberCounter tinyintSET @OrdinalNumberCounter = 0FETCH NEXT FROM ordinalNumberCursorWHILE(@@FETCH_STATUS=0)BEGIN UPDATE AR_Parameter SET OrdinalNumber=@OrdinalNumberCounter WHERE CURRENT OF ordinalNumberCursor
EXEC proc_ar_BumpMetadataObjectVersionCounter @Id
SET @OrdinalNumberCounter = @OrdinalNumberCounter + 1 FETCH NEXT FROM ordinalNumberCursorENDDEALLOCATE ordinalNumberCursor
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeletePropertiesById] Script Date: 06/23/2008 08:04:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeletePropertiesById] ( @MetadataObjectId int, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTIONDELETE FROM AR_PropertyWHERE AR_Property.MetadataObjectId = @MetadataObjectId
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeletePropertyForMetadataObjectId] Script Date: 06/23/2008 08:04:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeletePropertyForMetadataObjectId] ( @MetadataObjectId int, @Name nvarchar (255), @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTIONDELETE FROM AR_PropertyWITH (HOLDLOCK)WHERE AR_Property.MetadataObjectId = @MetadataObjectId AND AR_Property.Name = @Name
IF (@@ROWCOUNT <> 1)BEGIN SET @ErrorCode = -2 GOTO ON_ERROREND
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteSystemById] Script Date: 06/23/2008 08:04:10 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteSystemById] ( @Id int, @Version int, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION
-- Lock System table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent deleteSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (2,3,4) -- system, systeminstance and entity tables
-- before deleting make sure System existsSELECT IdFROM AR_SystemWITH (HOLDLOCK) -- guarantee no one else deletes the same System WHERE AR_System.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objectsSELECT IdFROM AR_SystemInstanceWHERE AR_SystemInstance.SystemId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
SELECT IdFROM AR_ClassWHERE AR_Class.SystemId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- delete non-cascading children -- there are none --
-- cascading deletes will take care of other tablesDELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteSystemInstanceById] Script Date: 06/23/2008 08:04:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteSystemInstanceById] ( @Id int, @Version int, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock SystemInstance table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent deleteSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 3 -- systeminstance table
-- before deleting make sure SystemInstance existsSELECT IdFROM AR_SystemInstanceWITH (HOLDLOCK) -- guarantee no one else deletes the same SystemInstanceWHERE AR_SystemInstance.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objects-- (SystemInstance has no child objects)
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- delete non-cascading children -- there are none --
-- cascading deletes will take care of other tablesDELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteTypeDescriptorById] Script Date: 06/23/2008 08:04:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteTypeDescriptorById] ( @Id int, @Version int, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
-- BEGIN TRANSACION; allow nested invocationDECLARE @TranCount int SET @TranCount=@@TRANCOUNT IF(@@TRANCOUNT=0) BEGIN TRANSACTION
-- Lock TypeDescriptor table for concurrent deletes; want to -- guarantee that no one will insert a child object-- between the time we check for child objects and -- decide to go ahead or abort the parent deleteSELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId IN (10, 12) -- methodinstance, typedescriptor
-- before deleting make sure TypeDescriptor existsSELECT IdFROM AR_TypeDescriptorWITH (HOLDLOCK) -- guarantee no one else deletes the same objectWHERE AR_TypeDescriptor.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --object not found GOTO ON_ERROREND
-- before deleting make sure there are no child objectsSELECT IdFROM AR_MethodInstanceWHERE ReturnTypeDescriptorId = @Id
IF (@@ROWCOUNT > 0)BEGIN SET @ErrorCode = -5 --child objects found GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWITH (HOLDLOCK) -- now no one can update this before we deleteWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version AND @Version != -1)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
-- the delete is *really* expensive because of all the cascading constraints. We want -- to do all deletes together as a result; we'll collate them in this temp tableIF NOT EXISTS (select * from tempdb..sysobjects where id = object_id(N'tempdb..##__DeletedTypeDescriptorList'))BEGIN CREATE TABLE ##__DeletedTypeDescriptorList ( [Id] [int] NOT NULL, [SessionId] [int] NOT NULL ) ON [PRIMARY]
ALTER TABLE ##__DeletedTypeDescriptorList WITH NOCHECK ADD CONSTRAINT [PK] PRIMARY KEY CLUSTERED ( [Id] ) ON [PRIMARY] END
-- delete all typedescriptors; use the root typedescriptor as the 'session'. -- The session allows the same tmp table to be used by concurrent delete operations -- (even though we don't allow concurrent deletes in general; this is more robust)EXEC proc_ar_DeleteTypeDescriptorByIdInternal @Id, @Id, -1, @ErrorCode OUTPUT -- -1 ==> ignore version checkIF( @ErrorCode != 0) GOTO ON_ERROR
-- save parent before deletingDECLARE @parentId intSELECT @parentId=ParentTypeDescriptorId FROM AR_TypeDescriptor WHERE Id=@Id
-- Perform delete on collected TypeDescriptors DELETE FROM AR_MetadataObject WHERE AR_MetadataObject.Id IN (SELECT Id FROM ##__DeletedTypeDescriptorList WHERE SessionId = @Id)SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- fixup ParentTypeDescriptorEXEC proc_ar_TypeDescriptorContainsFlagsFixup @parentId, @ErrorCode OUTPUTIF(@ErrorCode != 0) GOTO ON_ERROR
-- reset the DeletedTypeDescriptorListDELETE FROM ##__DeletedTypeDescriptorList WHERE SessionId = @IdSELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
IF(@TranCount < @@TRANCOUNT) COMMIT TRANSACTIONRETURN ON_ERROR:IF(@TranCount < @@TRANCOUNT) ROLLBACK TRANSACTIONRETURN GO/****** Object: StoredProcedure [dbo].[proc_ar_DeleteTypeDescriptorByIdInternal] Script Date: 06/23/2008 08:04:11 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_DeleteTypeDescriptorByIdInternal] ( @Id int, @SessionId int, @Version int, @ErrorCode int OUTPUT)AS SET @ErrorCode = 0 -- before deleting delete child objects-- (ArshishK: I considered several hierarchical SQL representation models -- here (nested sets, enumerated lineage, etc.). I'm adopting the most -- intuitive and easy to write one here - admin OM perf is not our design-- point; the runtime OM will be fast, and that's all that matters)DECLARE @ParameterId intSELECT @ParameterId=ParameterId FROM AR_TypeDescriptor WHERE Id=@Id
DECLARE childCursor CURSOR LOCALFORWARD_ONLYKEYSETFOR SELECT Id FROM AR_TypeDescriptor WHERE ParameterId = @ParameterId AND ParentTypeDescriptorId = @Id
OPEN childCursorDECLARE @childId intFETCH NEXT FROM childCursor INTO @childIdWHILE(@@FETCH_STATUS=0)BEGIN EXEC proc_ar_DeleteTypeDescriptorByIdInternal @childId, @SessionId, -1, @ErrorCode OUTPUT -- -1 ==> ignore version check IF( @ErrorCode != 0) BEGIN DEALLOCATE childCursor GOTO ON_ERROR END FETCH NEXT FROM childCursor INTO @childIdENDDEALLOCATE childCursor
-- cascading deletes will take care of other tablesDELETE FROM AR_DefaultValueWHERE AR_DefaultValue.TypeDescriptorId = @IdSELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- save this in the list of candidates to be deleted in one swoopINSERT INTO ##__DeletedTypeDescriptorList (Id, SessionId) VALUES (@Id, @SessionId)
RETURN
ON_ERROR:RETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_EnsureApplicationRegistryExists] Script Date: 06/23/2008 08:04:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_EnsureApplicationRegistryExists]AS
BEGIN TRANSACTION
IF NOT EXISTS (SELECT * FROM AR_ApplicationRegistry)BEGIN DECLARE @StaticAppRegId int INSERT INTO AR_MetadataObject (Name,IsCached) VALUES ('ApplicationRegistry',1) SET @StaticAppRegId = @@IDENTITY INSERT INTO AR_ApplicationRegistry (Id) VALUES (@StaticAppRegId)
INSERT INTO AR_MetadataObjectSecurity (MetadataObjectId, IdentityName, DisplayName, Rights) VALUES (@StaticAppRegId, 'domainUser', 'domainUser', 31)END
COMMIT TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAccessControlEntriesForMetadataObject] Script Date: 06/23/2008 08:04:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAccessControlEntriesForMetadataObject] ( @MetadataObjectId [int], @ErrorCode [int] OUTPUT)AS
SET @ErrorCode = 0
IF EXISTS (SELECT * FROM AR_MetadataObject WHERE Id = @MetadataObjectId)BEGIN
SELECT MetadataObjectId, IdentityName, DisplayName, RawSid, Rights FROM AR_MetadataObjectSecurity WHERE MetadataObjectId = @MetadataObjectIdENDELSEBEGIN SET @ErrorCode = -2END
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetActionById] Script Date: 06/23/2008 08:04:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetActionById] ( @MetadataObjectId int)AS
SELECT AR_Action.[Id], AR_Action.EntityId, AR_Action.Position, AR_Action.IsDisplayed, AR_Action.IsOpenedInNewWindow, AR_Action.Icon, AR_Action.Url, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_ActionINNER JOIN AR_MetadataObjectON AR_Action.Id = AR_MetadataObject.IdWHERE AR_Action.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetActionParameterById] Script Date: 06/23/2008 08:04:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetActionParameterById] ( @MetadataObjectId int)AS
SELECT AR_ActionParameter.[Id], AR_ActionParameter.ActionId, AR_ActionParameter.[Index], AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_ActionParameterINNER JOIN AR_MetadataObjectON AR_ActionParameter.Id = AR_MetadataObject.IdWHERE AR_ActionParameter.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetActionParametersForActionWithCount] Script Date: 06/23/2008 08:04:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetActionParametersForActionWithCount] ( @ActionId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_ActionParameterINNER JOIN AR_MetadataObjectON AR_ActionParameter.Id = AR_MetadataObject.IdWHERE AR_ActionParameter.ActionId = @ActionId
SELECT AR_ActionParameter.[Id], AR_ActionParameter.ActionId, AR_ActionParameter.[Index], AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_ActionParameterON AR_ActionParameter.Id = AR_MetadataObject.Id WHERE AR_ActionParameter.ActionId = @ActionIdORDER BY AR_ActionParameter.[Index]ASCCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetActionsForEntityWithCount] Script Date: 06/23/2008 08:04:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetActionsForEntityWithCount] ( @EntityId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_ActionINNER JOIN AR_MetadataObjectON AR_Action.Id = AR_MetadataObject.IdWHERE AR_Action.EntityId = @EntityId
SELECT AR_Action.[Id], AR_Action.EntityId, AR_Action.Position, AR_Action.IsDisplayed, AR_Action.IsOpenedInNewWindow, AR_Action.Icon, AR_Action.Url, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_ActionON AR_Action.Id = AR_MetadataObject.Id WHERE AR_Action.EntityId = @EntityIdORDER BY AR_Action.PositionASCCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAllLocalizedNamesForMetadataObjectWithCount] Script Date: 06/23/2008 08:04:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAllLocalizedNamesForMetadataObjectWithCount] ( @MetadataObjectId int)AS
BEGIN TRANSACTIONSELECT COUNT( * ) FROM AR_LocalizedNameWHERE AR_LocalizedName.MetadataObjectId = @MetadataObjectId
SELECT AR_LocalizedName.[Id], AR_LocalizedName.LCID, AR_LocalizedName.LocalizedName, AR_LocalizedName.MetadataObjectId FROM AR_LocalizedNameWHERE AR_LocalizedName.MetadataObjectId = @MetadataObjectIdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAllSystemInstancesLikeNameWithCount] Script Date: 06/23/2008 08:04:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAllSystemInstancesLikeNameWithCount] ( @SystemInstanceName nvarchar(255), @LCID int)AS
BEGIN TRANSACTIONSELECT DISTINCT COUNT( AR_SystemInstance.[Id] ) FROM AR_SystemInstanceINNER JOIN AR_MetadataObjectON AR_SystemInstance.Id = AR_MetadataObject.IdLEFT JOIN AR_LocalizedNameON AR_MetadataObject.Id = AR_LocalizedName.MetadataObjectIdWHERE UPPER(AR_MetadataObject.Name) LIKE @SystemInstanceName OR (UPPER(AR_LocalizedName.LocalizedName) LIKE @SystemInstanceName AND (AR_LocalizedName.LCID = @LCID OR AR_LocalizedName.LCID = 0))
SELECT DISTINCT AR_SystemInstance.[Id], AR_SystemInstance.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.Version FROM AR_SystemInstanceINNER JOIN AR_MetadataObject ON AR_SystemInstance.Id = AR_MetadataObject.IdLEFT JOIN AR_LocalizedNameON AR_MetadataObject.Id = AR_LocalizedName.MetadataObjectIdWHERE UPPER(AR_MetadataObject.Name) LIKE @SystemInstanceName OR (UPPER(AR_LocalizedName.LocalizedName) LIKE @SystemInstanceName AND (AR_LocalizedName.LCID = @LCID OR AR_LocalizedName.LCID = 0))
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAllSystemInstancesWithCount] Script Date: 06/23/2008 08:04:13 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAllSystemInstancesWithCount]AS
BEGIN TRANSACTIONSELECT COUNT( * ) FROM AR_SystemInstance
SELECT AR_SystemInstance.[Id], AR_SystemInstance.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.Version FROM AR_SystemInstanceINNER JOIN AR_MetadataObject ON AR_SystemInstance.Id = AR_MetadataObject.IdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAllSystemsWithCount] Script Date: 06/23/2008 08:04:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAllSystemsWithCount]AS
BEGIN TRANSACTIONSELECT COUNT( * ) FROM AR_System
SELECT AR_System.[Id], AR_System.SystemUtilityTypeName, AR_System.ConnectionFactoryTypeName, AR_System.SystemEntityTypeName, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_SystemINNER JOIN AR_MetadataObject ON AR_System.Id = AR_MetadataObject.IdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAssociationById] Script Date: 06/23/2008 08:04:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAssociationById] ( @MetadataObjectId int)AS
SELECT AR_Association.[Id], AR_MethodInstance.MethodId, AR_MethodInstance.ReturnTypeDescriptorId, AR_MethodInstance.Type, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_AssociationINNER JOIN AR_MethodInstanceON AR_Association.Id = AR_MethodInstance.IdINNER JOIN AR_MetadataObject ON AR_MethodInstance.Id = AR_MetadataObject.IdWHERE AR_Association.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAssociationByName] Script Date: 06/23/2008 08:04:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAssociationByName] ( @AssociationName [nvarchar] (255), @SystemId int) AS
SELECT AR_Association.[Id], AR_MethodInstance.MethodId, AR_MethodInstance.ReturnTypeDescriptorId, AR_MethodInstance.Type, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_AssociationINNER JOIN AR_MethodInstanceON AR_MethodInstance.Id = AR_Association.IdINNER JOIN AR_MetadataObjectON AR_MetadataObject.Id = AR_MethodInstance.IdWHERE AR_MetadataObject.Id IN (SELECT DISTINCT AR_Association.ID FROM AR_Association INNER JOIN AR_AssociationMember ON AR_Association.Id = AR_AssociationMember.AssociationId INNER JOIN AR_Class ON AR_AssociationMember.EntityId = AR_Class.Id INNER JOIN AR_System ON AR_Class.SystemId = AR_System.Id WHERE AR_System.Id = @SystemId AND AR_Association.Id IN (SELECT AR_Association.Id FROM AR_MetadataObject, AR_Association WHERE AR_MetadataObject.Name=@AssociationName AND AR_MetadataObject.Id = AR_Association.Id) )
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAssociationsForDataClassWithCount] Script Date: 06/23/2008 08:04:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAssociationsForDataClassWithCount] ( @ClassId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_AssociationINNER JOIN AR_MethodInstanceON AR_Association.Id = AR_MethodInstance.Id WHERE AR_MethodInstance.MethodId IN (SELECT AR_Method.[Id] AS Method FROM AR_Method WHERE AR_Method.ClassId = @ClassId)
SELECT AR_Association.[Id], AR_MethodInstance.MethodId, AR_MethodInstance.ReturnTypeDescriptorId, AR_MethodInstance.Type, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_AssociationINNER JOIN AR_MethodInstanceON AR_Association.Id = AR_MethodInstance.Id INNER JOIN AR_MetadataObjectON AR_MethodInstance.Id = AR_MetadataObject.IdWHERE AR_MethodInstance.MethodId IN (SELECT AR_Method.[Id] AS Method FROM AR_Method WHERE AR_Method.ClassId = @ClassId)
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAssociationsForEntityAndRoleWithCount] Script Date: 06/23/2008 08:04:14 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAssociationsForEntityAndRoleWithCount] ( @EntityId int, @EntityRole bit) AS
BEGIN TRANSACTIONSELECT COUNT( * )FROM AR_AssociationINNER JOIN AR_MethodInstanceON AR_MethodInstance.Id = AR_Association.IdINNER JOIN AR_MetadataObjectON AR_MetadataObject.Id = AR_MethodInstance.IdWHERE AR_Association.Id IN ( SELECT AR_AssociationMember.AssociationId FROM AR_AssociationMember WHERE AR_AssociationMember.EntityId = @EntityId AND AR_AssociationMember.EntityRole = @EntityRole)
SELECT AR_Association.[Id], AR_MethodInstance.MethodId, AR_MethodInstance.ReturnTypeDescriptorId, AR_MethodInstance.Type, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_AssociationINNER JOIN AR_MethodInstanceON AR_MethodInstance.Id = AR_Association.IdINNER JOIN AR_MetadataObjectON AR_MetadataObject.Id = AR_MethodInstance.IdWHERE AR_Association.Id IN ( SELECT AR_AssociationMember.AssociationId FROM AR_AssociationMember WHERE AR_AssociationMember.EntityId = @EntityId AND AR_AssociationMember.EntityRole = @EntityRole)
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetAssociationsForMethodWithCount] Script Date: 06/23/2008 08:04:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetAssociationsForMethodWithCount] ( @MethodId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_AssociationINNER JOIN AR_MethodInstanceON AR_Association.Id = AR_MethodInstance.Id WHERE AR_MethodInstance.MethodId = @MethodId
SELECT AR_Association.[Id], AR_MethodInstance.MethodId, AR_MethodInstance.ReturnTypeDescriptorId, AR_MethodInstance.Type, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_AssociationINNER JOIN AR_MethodInstanceON AR_Association.Id = AR_MethodInstance.Id INNER JOIN AR_MetadataObjectON AR_MethodInstance.Id = AR_MetadataObject.IdWHERE AR_MethodInstance.MethodId = @MethodId
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetCacheInvalidationCountersWithCount] Script Date: 06/23/2008 08:04:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetCacheInvalidationCountersWithCount] AS
SELECT COUNT( * ) FROM AR_CacheCountersWITH (NOLOCK) -- number of rows will never change; each row contains independent data; dirty reads ok; no need to encase in xact
SELECT MetadataObjectType, ObjectCacheCounter, RelationshipCacheCounterFROM AR_CacheCountersWITH (NOLOCK)ORDER BY MetadataObjectType
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetChildTypeDescriptorsForTypeDescriptorWithCount] Script Date: 06/23/2008 08:04:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetChildTypeDescriptorsForTypeDescriptorWithCount] ( @ParentTypeDescriptorId int)AS
BEGIN TRANSACTIONSELECT COUNT (*)FROM AR_MetadataObjectINNER JOIN AR_TypeDescriptorON AR_TypeDescriptor.Id = AR_MetadataObject.Id WHERE AR_TypeDescriptor.ParentTypeDescriptorId = @ParentTypeDescriptorId
SELECT AR_TypeDescriptor.[Id], AR_TypeDescriptor.ParameterId, AR_TypeDescriptor.ParentTypeDescriptorId, AR_TypeDescriptor.TypeName, AR_TypeDescriptor.InterpretedTypeName, AR_TypeDescriptor.ContainsIdentifier, AR_TypeDescriptor.IdentifierId, AR_TypeDescriptor.ContainsFilterDescriptor, AR_TypeDescriptor.FilterDescriptorId, AR_TypeDescriptor.IsCollection, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_TypeDescriptorON AR_TypeDescriptor.Id = AR_MetadataObject.Id WHERE AR_TypeDescriptor.ParentTypeDescriptorId = @ParentTypeDescriptorIdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetDataClassById] Script Date: 06/23/2008 08:04:15 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetDataClassById] ( @MetadataObjectId int)AS
SELECT AR_Class.[Id], AR_Class.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_ClassINNER JOIN AR_MetadataObject ON AR_Class.Id = AR_MetadataObject.IdLEFT JOIN AR_Entity ON AR_Class.Id = AR_Entity.IdWHERE AR_Class.Id = @MetadataObjectId AND AR_Entity.Id IS NULL
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetDataClassesForSystemWithCount] Script Date: 06/23/2008 08:04:16 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetDataClassesForSystemWithCount] ( @SystemId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_ClassLEFT JOIN AR_Entity ON AR_Class.Id = AR_Entity.IdWHERE AR_Class.SystemId = @SystemId AND AR_Entity.Id IS NULL
SELECT AR_Class.[Id], AR_Class.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_ClassON AR_Class.Id = AR_MetadataObject.Id LEFT JOIN AR_EntityON AR_Class.Id = AR_Entity.IdWHERE AR_Class.SystemId = @SystemId AND AR_Entity.Id IS NULLCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetDefaultValuesForTypeDescriptor] Script Date: 06/23/2008 08:04:16 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetDefaultValuesForTypeDescriptor] ( @TypeDescriptorId int, @ErrorCode int OUTPUT)AS
BEGIN TRANSACTION
SET @ErrorCode = 0
IF NOT EXISTS (SELECT Id FROM AR_TypeDescriptor WHERE Id = @TypeDescriptorId)BEGIN SET @ErrorCode = -2 -- objectNotFoundEND
SELECT AR_DefaultValue.[Id], AR_DefaultValue.[Value], AR_DefaultValue.[TypeDescriptorId], AR_DefaultValue.[MethodInstanceId], AR_MetadataObject.[Name] AS MethodInstanceNameFROM AR_DefaultValue, AR_MetadataObjectWHERE AR_DefaultValue.TypeDescriptorId = @TypeDescriptorId AND AR_MetadataObject.Id = AR_DefaultValue.MethodInstanceId
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetDependentEntitiesForEntity] Script Date: 06/23/2008 08:04:16 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetDependentEntitiesForEntity] ( @EntityId int)AS
BEGIN TRANSACTION
SELECT AR_Entity.[Id] AS EntityId-- AR_Entity.EstimatedInstanceCount,-- AR_Class.SystemId,-- AR_MetadataObject.[Name], -- AR_MetadataObject.IsCached,-- AR_MetadataObject.VersionFROM AR_Entity--INNER JOIN-- AR_Class--ON-- AR_Entity.Id = AR_Class.Id--INNER JOIN -- AR_MetadataObject --ON-- AR_Class.Id = AR_MetadataObject.IdWHERE AR_Entity.Id != @EntityId AND AR_Entity.Id IN (SELECT EntityId FROM AR_Identifier WHERE Id IN (SELECT IdentifierId FROM AR_TypeDescriptor WHERE IdentifierId IS NOT NULL AND ParameterId IN (SELECT Id FROM AR_Parameter WHERE MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @EntityId))))
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetEntitiesForAssociationAndRoleWithCount] Script Date: 06/23/2008 08:04:16 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetEntitiesForAssociationAndRoleWithCount] ( @AssociationId int, @EntityRole bit) AS
BEGIN TRANSACTIONSELECT COUNT( * )FROM AR_EntityINNER JOIN AR_ClassON AR_Class.Id = AR_Entity.IdINNER JOIN AR_MetadataObjectON AR_MetadataObject.Id = AR_Class.IdWHERE AR_Entity.IdIN ( SELECT AR_AssociationMember.EntityId FROM AR_AssociationMember WHERE AR_AssociationMember.AssociationId = @AssociationId AND AR_AssociationMember.EntityRole = @EntityRole)
SELECT AR_Entity.Id, AR_Entity.EstimatedInstanceCount, AR_Class.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_EntityINNER JOIN AR_ClassON AR_Class.Id = AR_Entity.IdINNER JOIN AR_MetadataObjectON AR_MetadataObject.Id = AR_Class.IdWHERE AR_Entity.IdIN ( SELECT AR_AssociationMember.EntityId FROM AR_AssociationMember WHERE AR_AssociationMember.AssociationId = @AssociationId AND AR_AssociationMember.EntityRole = @EntityRole)
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetEntitiesForSystemLikeNameWithCount] Script Date: 06/23/2008 08:04:16 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetEntitiesForSystemLikeNameWithCount] ( @MetadataObjectName nvarchar (255), @SystemId int)AS
BEGIN TRANSACTION
SELECT COUNT (AR_MetadataObject.Id)FROM AR_EntityINNER JOIN AR_ClassON AR_Entity.Id = AR_Class.IdINNER JOIN AR_MetadataObject ON AR_Class.Id = AR_MetadataObject.IdWHERE UPPER(AR_MetadataObject.Name) LIKE @MetadataObjectName AND AR_Class.SystemId = @SystemId
SELECT AR_Entity.[Id], AR_Entity.EstimatedInstanceCount, AR_Class.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_EntityINNER JOIN AR_ClassON AR_Entity.Id = AR_Class.IdINNER JOIN AR_MetadataObject ON AR_Class.Id = AR_MetadataObject.IdWHERE UPPER(AR_MetadataObject.Name) LIKE @MetadataObjectName AND AR_Class.SystemId = @SystemId
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetEntitiesForSystemWithCount] Script Date: 06/23/2008 08:04:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetEntitiesForSystemWithCount] ( @SystemId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_EntityINNER JOIN AR_ClassON AR_Class.Id = AR_Entity.IdWHERE AR_Class.SystemId = @SystemId
SELECT AR_Entity.[Id], AR_Entity.EstimatedInstanceCount, AR_Class.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_ClassON AR_Class.Id = AR_MetadataObject.Id INNER JOIN AR_EntityON AR_Class.Id = AR_Entity.IdWHERE AR_Class.SystemId = @SystemIdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetEntityById] Script Date: 06/23/2008 08:04:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetEntityById] ( @MetadataObjectId int)AS
SELECT AR_Entity.[Id], AR_Entity.EstimatedInstanceCount, AR_Class.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_EntityINNER JOIN AR_ClassON AR_Entity.Id = AR_Class.IdINNER JOIN AR_MetadataObject ON AR_Class.Id = AR_MetadataObject.IdWHERE AR_Entity.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetFilterDescriptorById] Script Date: 06/23/2008 08:04:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetFilterDescriptorById] ( @MetadataObjectId int)AS
SELECT AR_FilterDescriptor.[Id], AR_FilterDescriptor.[TypeName], AR_FilterDescriptor.[MethodId], AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_FilterDescriptorINNER JOIN AR_MetadataObjectON AR_FilterDescriptor.Id = AR_MetadataObject.IdWHERE AR_FilterDescriptor.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetFilterDescriptorsForMethodWithCount] Script Date: 06/23/2008 08:04:17 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetFilterDescriptorsForMethodWithCount] ( @MethodId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_FilterDescriptorINNER JOIN AR_MetadataObjectON AR_FilterDescriptor.Id = AR_MetadataObject.IdWHERE AR_FilterDescriptor.MethodId = @MethodId
SELECT AR_FilterDescriptor.[Id], AR_FilterDescriptor.[TypeName], AR_FilterDescriptor.[MethodId], AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_FilterDescriptorON AR_FilterDescriptor.Id = AR_MetadataObject.Id WHERE AR_FilterDescriptor.MethodId = @MethodIdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetIdentifierById] Script Date: 06/23/2008 08:04:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetIdentifierById] ( @MetadataObjectId int)AS
SELECT AR_Identifier.[Id], AR_Identifier.[TypeName], AR_Identifier.[EntityId], AR_Identifier.[OrdinalNumber], AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_IdentifierINNER JOIN AR_MetadataObjectON AR_Identifier.Id = AR_MetadataObject.IdWHERE AR_Identifier.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetIdentifiersForEntityWithCount] Script Date: 06/23/2008 08:04:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetIdentifiersForEntityWithCount] ( @EntityId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_IdentifierINNER JOIN AR_MetadataObjectON AR_Identifier.Id = AR_MetadataObject.IdWHERE AR_Identifier.EntityId = @EntityId
SELECT AR_Identifier.[Id], AR_Identifier.[TypeName], AR_Identifier.[EntityId], AR_Identifier.[OrdinalNumber], AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_IdentifierINNER JOIN AR_MetadataObjectON AR_Identifier.Id = AR_MetadataObject.IdWHERE AR_Identifier.EntityId = @EntityId ORDER BY AR_Identifier.[OrdinalNumber] ASCCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetMethodById] Script Date: 06/23/2008 08:04:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetMethodById] ( @MetadataObjectId int)AS
SELECT AR_Method.[Id], AR_Method.ClassId, AR_Method.IsStatic, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MethodINNER JOIN AR_MetadataObjectON AR_Method.Id = AR_MetadataObject.IdWHERE AR_Method.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetMethodInstanceById] Script Date: 06/23/2008 08:04:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetMethodInstanceById] ( @MetadataObjectId int)AS
SELECT AR_MethodInstance.[Id], AR_MethodInstance.MethodId, AR_MethodInstance.ReturnTypeDescriptorId, AR_MethodInstance.Type, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MethodInstanceINNER JOIN AR_MetadataObjectON AR_MethodInstance.Id = AR_MetadataObject.Id-- exclude out those method instances that are associations-- as they will be materialized and treated separately LEFT JOIN AR_AssociationON AR_MethodInstance.Id = AR_Association.IdWHERE AR_MethodInstance.Id = @MetadataObjectId AND AR_Association.Id IS NULL
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetMethodInstancesForDataClassWithCount] Script Date: 06/23/2008 08:04:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetMethodInstancesForDataClassWithCount] ( @ClassId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_MetadataObjectINNER JOIN AR_MethodInstanceON AR_MethodInstance.Id = AR_MetadataObject.Id -- exclude out those method instances that are associations-- as they will be materialized and treated separately LEFT JOIN AR_AssociationON AR_MethodInstance.Id = AR_Association.IdWHERE AR_MethodInstance.MethodId IN (SELECT AR_Method.[Id] AS Method FROM AR_Method WHERE AR_Method.ClassId = @ClassId) AND AR_Association.Id IS NULL
SELECT AR_MethodInstance.[Id], AR_MethodInstance.MethodId, AR_MethodInstance.ReturnTypeDescriptorId, AR_MethodInstance.Type, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_MethodInstanceON AR_MethodInstance.Id = AR_MetadataObject.Id -- exclude out those method instances that are associations-- as they will be materialized and treated separately LEFT JOIN AR_AssociationON AR_MethodInstance.Id = AR_Association.IdWHERE AR_MethodInstance.MethodId IN (SELECT AR_Method.[Id] AS Method FROM AR_Method WHERE AR_Method.ClassId = @ClassId) AND AR_Association.Id IS NULL
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetMethodInstancesForMethodWithCount] Script Date: 06/23/2008 08:04:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetMethodInstancesForMethodWithCount] ( @MethodId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_MetadataObjectINNER JOIN AR_MethodInstanceON AR_MethodInstance.Id = AR_MetadataObject.Id -- exclude out those method instances that are associations-- as they will be materialized and treated separately LEFT JOIN AR_AssociationON AR_MethodInstance.Id = AR_Association.IdWHERE AR_MethodInstance.MethodId = @MethodId AND AR_Association.Id IS NULL
SELECT AR_MethodInstance.[Id], AR_MethodInstance.MethodId, AR_MethodInstance.ReturnTypeDescriptorId, AR_MethodInstance.Type, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_MethodInstanceON AR_MethodInstance.Id = AR_MetadataObject.Id -- exclude out those method instances that are associations-- as they will be materialized and treated separately LEFT JOIN AR_AssociationON AR_MethodInstance.Id = AR_Association.IdWHERE AR_MethodInstance.MethodId = @MethodId AND AR_Association.Id IS NULL
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetMethodsForDataClassWithCount] Script Date: 06/23/2008 08:04:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetMethodsForDataClassWithCount] ( @ClassId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_MethodINNER JOIN AR_MetadataObjectON AR_Method.Id = AR_MetadataObject.IdWHERE AR_Method.ClassId = @ClassId
SELECT AR_Method.[Id], AR_Method.ClassId, AR_Method.IsStatic, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_MethodON AR_Method.Id = AR_MetadataObject.Id WHERE AR_Method.ClassId = @ClassIdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetParameterById] Script Date: 06/23/2008 08:04:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetParameterById] ( @MetadataObjectId int)AS
SELECT AR_Parameter.[Id], AR_Parameter.MethodId, AR_Parameter.Direction, AR_Parameter.OrdinalNumber, AR_Parameter.TypeReflectorTypeName, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_ParameterINNER JOIN AR_MetadataObjectON AR_Parameter.Id = AR_MetadataObject.IdWHERE AR_Parameter.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetParametersForMethodWithCount] Script Date: 06/23/2008 08:04:19 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetParametersForMethodWithCount] ( @MethodId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_ParameterINNER JOIN AR_MetadataObjectON AR_Parameter.Id = AR_MetadataObject.IdWHERE AR_Parameter.MethodId = @MethodId
SELECT AR_Parameter.[Id], AR_Parameter.MethodId, AR_Parameter.Direction, AR_Parameter.OrdinalNumber, AR_Parameter.TypeReflectorTypeName, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_ParameterON AR_Parameter.Id = AR_MetadataObject.Id WHERE AR_Parameter.MethodId = @MethodIdORDER BY AR_Parameter.OrdinalNumberASCCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetPropertiesForMetadataObject] Script Date: 06/23/2008 08:04:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetPropertiesForMetadataObject] ( @MetadataObjectId int, @ErrorCode int OUTPUT)AS
BEGIN TRANSACTION
SET @ErrorCode = 0
IF NOT EXISTS (SELECT Id FROM AR_MetadataObject WHERE Id = @MetadataObjectId)BEGIN SET @ErrorCode = -2 -- objectNotFoundEND
SELECT AR_Property.[Name], AR_Property.[Value]FROM AR_PropertyWHERE AR_Property.MetadataObjectId = @MetadataObjectId
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetRootTypeDescriptorForParameter] Script Date: 06/23/2008 08:04:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetRootTypeDescriptorForParameter] ( @ParameterId int)AS
SELECT AR_TypeDescriptor.[Id], AR_TypeDescriptor.ParameterId, AR_TypeDescriptor.ParentTypeDescriptorId, AR_TypeDescriptor.TypeName, AR_TypeDescriptor.InterpretedTypeName, AR_TypeDescriptor.ContainsIdentifier, AR_TypeDescriptor.IdentifierId, AR_TypeDescriptor.ContainsFilterDescriptor, AR_TypeDescriptor.FilterDescriptorId, AR_TypeDescriptor.IsCollection, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_TypeDescriptorON AR_TypeDescriptor.Id = AR_MetadataObject.Id WHERE AR_TypeDescriptor.ParameterId = @ParameterId AND AR_TypeDescriptor.ParentTypeDescriptorId IS NULL
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetSystemById] Script Date: 06/23/2008 08:04:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetSystemById] ( @MetadataObjectId int)AS
SELECT AR_System.[Id], AR_System.ConnectionFactoryTypeName, AR_System.SystemUtilityTypeName, AR_System.SystemEntityTypeName, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_SystemINNER JOIN AR_MetadataObject ON AR_System.Id = AR_MetadataObject.IdWHERE AR_System.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetSystemDataBySystemName] Script Date: 06/23/2008 08:04:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetSystemDataBySystemName] ( @SystemName nvarchar (255))AS
DECLARE @SystemId intSELECT @SystemId=AR_System.Id FROM AR_System,AR_MetadataObject WHERE AR_MetadataObject.Id = AR_System.Id AND AR_MetadataObject.Name = @SystemName
BEGIN TRANSACTIONSELECT AR_SystemData.[Length], AR_SystemData.[Data]FROM AR_SystemDataWHERE AR_SystemData.[SystemId] = @SystemIdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetSystemInstanceById] Script Date: 06/23/2008 08:04:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetSystemInstanceById] ( @MetadataObjectId int)AS
SELECT AR_SystemInstance.[Id], AR_SystemInstance.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_SystemInstanceINNER JOIN AR_MetadataObject ON AR_SystemInstance.Id = AR_MetadataObject.IdWHERE AR_SystemInstance.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetSystemInstanceByName] Script Date: 06/23/2008 08:04:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetSystemInstanceByName] ( @Name nvarchar(50))AS
BEGIN TRANSACTIONSELECT AR_SystemInstance.[Id], AR_SystemInstance.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.Version FROM AR_SystemInstanceINNER JOIN AR_MetadataObject ON AR_SystemInstance.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @NameCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetSystemInstancesForSystemWithCount] Script Date: 06/23/2008 08:04:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetSystemInstancesForSystemWithCount] ( @SystemId int)AS
BEGIN TRANSACTIONSELECT COUNT(*)FROM AR_SystemInstanceWHERE AR_SystemInstance.SystemId = @SystemId
SELECT AR_SystemInstance.[Id], AR_SystemInstance.SystemId, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.Version FROM AR_SystemInstanceINNER JOIN AR_MetadataObject ON AR_SystemInstance.Id = AR_MetadataObject.IdWHERE AR_SystemInstance.SystemId = @SystemIdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetSystemsLikeNameWithCount] Script Date: 06/23/2008 08:04:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetSystemsLikeNameWithCount] ( @MetadataObjectName nvarchar (255), @LCID int)AS
BEGIN TRANSACTION
SELECT DISTINCT COUNT (AR_System.Id)FROM AR_SystemINNER JOIN AR_MetadataObject ON AR_System.Id = AR_MetadataObject.IdLEFT JOIN AR_LocalizedNameON AR_MetadataObject.Id = AR_LocalizedName.MetadataObjectIdWHERE UPPER(AR_MetadataObject.Name) LIKE @MetadataObjectName OR (UPPER(AR_LocalizedName.LocalizedName) LIKE @MetadataObjectName AND (AR_LocalizedName.LCID = @LCID OR AR_LocalizedName.LCID = 0))
SELECT DISTINCT AR_System.[Id], AR_System.ConnectionFactoryTypeName, AR_System.SystemUtilityTypeName, AR_System.SystemEntityTypeName, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_SystemINNER JOIN AR_MetadataObject ON AR_System.Id = AR_MetadataObject.IdLEFT JOIN AR_LocalizedNameON AR_MetadataObject.Id = AR_LocalizedName.MetadataObjectIdWHERE UPPER(AR_MetadataObject.Name) LIKE @MetadataObjectName OR (UPPER(AR_LocalizedName.LocalizedName) LIKE @MetadataObjectName AND (AR_LocalizedName.LCID = @LCID OR AR_LocalizedName.LCID = 0))
COMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetTypeDescriptorById] Script Date: 06/23/2008 08:04:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetTypeDescriptorById] ( @MetadataObjectId int)AS
SELECT AR_TypeDescriptor.[Id], AR_TypeDescriptor.ParameterId, AR_TypeDescriptor.ParentTypeDescriptorId, AR_TypeDescriptor.TypeName, AR_TypeDescriptor.InterpretedTypeName, AR_TypeDescriptor.ContainsIdentifier, AR_TypeDescriptor.IdentifierId, AR_TypeDescriptor.ContainsFilterDescriptor, AR_TypeDescriptor.FilterDescriptorId, AR_TypeDescriptor.IsCollection, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_TypeDescriptorINNER JOIN AR_MetadataObjectON AR_TypeDescriptor.Id = AR_MetadataObject.IdWHERE AR_TypeDescriptor.Id = @MetadataObjectId
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetTypeDescriptorsByNameAndParameter] Script Date: 06/23/2008 08:04:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetTypeDescriptorsByNameAndParameter] ( @ParameterId int, @Name nvarchar(255))AS
SELECT AR_TypeDescriptor.[Id], AR_TypeDescriptor.ParameterId, AR_TypeDescriptor.ParentTypeDescriptorId, AR_TypeDescriptor.TypeName, AR_TypeDescriptor.InterpretedTypeName, AR_TypeDescriptor.ContainsIdentifier, AR_TypeDescriptor.IdentifierId, AR_TypeDescriptor.ContainsFilterDescriptor, AR_TypeDescriptor.FilterDescriptorId, AR_TypeDescriptor.IsCollection, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_TypeDescriptorINNER JOIN AR_MetadataObjectON AR_TypeDescriptor.Id = AR_MetadataObject.IdWHERE AR_TypeDescriptor.ParameterId = @ParameterId AND AR_MetadataObject.Name = @Name
GO/****** Object: StoredProcedure [dbo].[proc_ar_GetTypeDescriptorsForFilterDescriptorWithCount] Script Date: 06/23/2008 08:04:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_GetTypeDescriptorsForFilterDescriptorWithCount] ( @FilterDescriptorId int)AS
BEGIN TRANSACTIONSELECT COUNT (*)FROM AR_TypeDescriptorWHERE AR_TypeDescriptor.FilterDescriptorId = @FilterDescriptorId
SELECT AR_TypeDescriptor.[Id], AR_TypeDescriptor.ParameterId, AR_TypeDescriptor.ParentTypeDescriptorId, AR_TypeDescriptor.TypeName, AR_TypeDescriptor.InterpretedTypeName, AR_TypeDescriptor.ContainsIdentifier, AR_TypeDescriptor.IdentifierId, AR_TypeDescriptor.ContainsFilterDescriptor, AR_TypeDescriptor.FilterDescriptorId, AR_TypeDescriptor.IsCollection, AR_MetadataObject.[Name], AR_MetadataObject.IsCached, AR_MetadataObject.VersionFROM AR_MetadataObjectINNER JOIN AR_TypeDescriptorON AR_TypeDescriptor.Id = AR_MetadataObject.Id WHERE AR_TypeDescriptor.FilterDescriptorId = @FilterDescriptorIdCOMMIT TRANSACTION
GO/****** Object: StoredProcedure [dbo].[proc_ar_SetAccessControlEntryForMetadataObject] Script Date: 06/23/2008 08:04:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_SetAccessControlEntryForMetadataObject] ( @MetadataObjectId [int], @IdentityName [nvarchar] (250), @DisplayName [nvarchar] (250), @RawSid [varbinary] (512), @Rights [bigint])AS
DELETE FROM AR_MetadataObjectSecurityWHERE IdentityName = @IdentityName AND MetadataObjectId = @MetadataObjectID
INSERT INTO AR_MetadataObjectSecurity (MetadataObjectId, IdentityName, DisplayName, RawSid, Rights)VALUES (@MetadataObjectId, @IdentityName, @DisplayName, @RawSid, @Rights)
GO/****** Object: StoredProcedure [dbo].[proc_ar_SetDefaultAction] Script Date: 06/23/2008 08:04:22 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_SetDefaultAction] ( @EntityId int, @ActionName nvarchar(255), @ErrorCode int OUTPUT)AS
BEGIN TRANSACTION
SET @ErrorCode = 0
IF(@ActionName IS NOT NULL)BEGIN -- See if the action/entity pair exists SELECT AR_MetadataObject.Id FROM AR_Action INNER JOIN AR_MetadataObject ON AR_Action.Id = AR_MetadataObject.Id WHERE AR_Action.EntityId = @EntityId AND AR_MetadataObject.Name = @ActionName
IF (@@ROWCOUNT = 0) BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROR ENDEND
-- TODO: Make this a proper field on the entity table-- See if property already existsSELECT ValueFROM AR_PropertyWHERE MetadataObjectId = @EntityId AND Name = 'DefaultAction'
IF (@@ROWCOUNT = 0)BEGIN IF(@ActionName IS NOT NULL) BEGIN -- Need to create the property INSERT INTO AR_Property (Name,Value,MetadataObjectId) VALUES ('DefaultAction', @ActionName, @EntityId)
SELECT @ErrorCode = @@Error IF @ErrorCode <> 0 GOTO ON_ERROR ENDENDELSEBEGIN IF(@ActionName IS NOT NULL) BEGIN -- Update old property UPDATE AR_Property SET Value = @ActionName WHERE MetadataObjectId = @EntityId AND Name = 'DefaultAction' SELECT @ErrorCode = @@Error IF @ErrorCode <> 0 GOTO ON_ERROR END ELSE BEGIN -- delete property DELETE AR_Property WHERE MetadataObjectId = @EntityId AND Name = 'DefaultAction' SELECT @ErrorCode = @@Error IF @ErrorCode <> 0 GOTO ON_ERROR ENDEND
COMMIT TRANSACTIONRETURN
ON_ERROR:ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_SetDefaultValuesForTypeDescriptor] Script Date: 06/23/2008 08:04:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_SetDefaultValuesForTypeDescriptor] ( @TypeDescriptorId int, @MethodInstanceId int, @Value sql_variant, @ErrorCode int OUTPUT)AS
BEGIN TRANSACTION
SET @ErrorCode = 0
-- ensure the MethodInstance and TypeDescriptor existIF NOT EXISTS (SELECT Id FROM AR_MethodInstance WHERE Id = @MethodInstanceId)BEGIN SET @ErrorCode = -2 GOTO ON_ERRORENDIF NOT EXISTS (SELECT Id FROM AR_TypeDescriptor WHERE Id = @TypeDescriptorId)BEGIN SET @ErrorCode = -2 GOTO ON_ERROREND
-- ensure the MethodInstance and TypeDescriptor belong to same MethodDECLARE @TypeDescriptorMethodId intSELECT @TypeDescriptorMethodId=Id FROM AR_Method WHERE Id IN (SELECT MethodId FROM AR_Parameter WHERE Id IN (SELECT ParameterId FROM AR_TypeDescriptor WHERE Id = @TypeDescriptorId))DECLARE @MethodInstanceMethodId intSELECT @MethodInstanceMethodId=MethodId FROM AR_MethodInstance WHERE Id=@MethodInstanceId
IF(@MethodInstanceMethodId != @TypeDescriptorMethodId)BEGIN SET @ErrorCode = -600 GOTO ON_ERROREND
-- ensure MaxDECLARE @DefaultValuesForTypeDescriptor intSET @DefaultValuesForTypeDescriptor = (SELECT COUNT (Id) FROM AR_DefaultValue WHERE TypeDescriptorId = @TypeDescriptorId)IF(@DefaultValuesForTypeDescriptor >= 15)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- insert/updateIF EXISTS (SELECT * FROM AR_DefaultValue WHERE TypeDescriptorId = @TypeDescriptorId AND MethodInstanceId = @MethodInstanceId)BEGIN UPDATE AR_DefaultValue SET Value=@Value WHERE TypeDescriptorId = @TypeDescriptorId AND MethodInstanceId = @MethodInstanceIdENDELSEBEGIN INSERT INTO AR_DefaultValue (TypeDescriptorId, MethodInstanceId, Value) VALUES (@TypeDescriptorId, @MethodInstanceId, @Value)END
COMMIT TRANSACTIONRETURN
ON_ERROR:ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_SetSystemDataBySystemName] Script Date: 06/23/2008 08:04:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_SetSystemDataBySystemName] ( @SystemName nvarchar(255), @AssemblyName nvarchar (255), @Length int, @Data Image)AS
BEGIN TRANSACTIONDECLARE @Result intSET @Result = 0;
DECLARE @SystemId intSELECT @SystemId=AR_System.Id FROM AR_System,AR_MetadataObject WHERE AR_MetadataObject.Id = AR_System.Id AND AR_MetadataObject.Name = @SystemName
IF NOT EXISTS ( SELECT * FROM AR_SystemData WHERE SystemId = @SystemId)BEGIN INSERT INTO AR_SystemData ( [SystemId], [Name], [Length], [Data] ) VALUES ( @SystemId, @AssemblyName, @Length, @Data )
SET @Result = @@ROWCOUNTENDELSEBEGIN UPDATE AR_SystemData SET AR_SystemData.[Name] = @AssemblyName, AR_SystemData.[Length] = @Length, AR_SystemData.[Data] = @Data WHERE AR_SystemData.[SystemId] = @SystemId
SET @Result = @@ROWCOUNTEND
COMMIT TRANSACTIONRETURN @Result
GO/****** Object: StoredProcedure [dbo].[proc_ar_TypeDescriptorContainsFlagsFixup] Script Date: 06/23/2008 08:04:23 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_TypeDescriptorContainsFlagsFixup] ( @TypeDescriptorId int, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0IF(@TypeDescriptorId IS NULL OR @TypeDescriptorId=0) RETURN
-- BEGIN TRANSACION; allow nested invocation. SQL doesn't like if a stored proc rolls back a txn that it didn't start. -- Another way to look at it is @@TRANCOUNT must be same at the start and end of a stored procDECLARE @TranCount int SET @TranCount=@@TRANCOUNT IF(@@TRANCOUNT=0) BEGIN TRANSACTION
DECLARE @currentParameter int
-- logical OR of currentTypeDescriptor's children and whether or not the currentTypeDescriptor has an IdentifierId or FilterDescriptorId respectivelyDECLARE @currentContainsIdentifier bitDECLARE @currentContainsFilterDescriptor bit
-- setting the initial condition of the contains* vars based on the presence of Identifier/FilterDesc Ids in the current rowDECLARE @containsIdentifier bit -- the cumulative result of the OR operationDECLARE @currentIdentifierId intDECLARE @containsFilterDescriptor bit -- the cumulative result of the OR operationDECLARE @currentFilterDescriptorId intSELECT @currentParameter=ParameterId, @currentIdentifierId=IdentifierId, @currentFilterDescriptorId=FilterDescriptorId FROM AR_TypeDescriptor WHERE Id=@TypeDescriptorIdIF (@currentIdentifierId != 0) SET @containsIdentifier = 1 ELSE SET @containsIdentifier = 0IF (@currentFilterDescriptorId != 0) SET @containsFilterDescriptor = 1 ELSE SET @containsFilterDescriptor = 0
-- setting the outer loop to loop from current to root DECLARE @current intSET @current = @TypeDescriptorIdDECLARE @maxNestDepth tinyintSET @maxNestDepth = 10WHILE(@current != 0 AND @maxNestDepth > 0)BEGIN -- perform logical OR for Identifiers -- IF EXISTS(SELECT Id FROM AR_TypeDescriptor WHERE ParameterId = @currentParameter AND ParentTypeDescriptorId = @current AND ContainsIdentifier = 1) BEGIN SET @containsIdentifier = 1 END
-- perform logical OR for FilterDescriptors -- IF EXISTS(SELECT Id FROM AR_TypeDescriptor WHERE ParameterId = @currentParameter AND ParentTypeDescriptorId = @current AND ContainsFilterDescriptor = 1) BEGIN SET @containsFilterDescriptor = 1 END
-- now before updating, make sure we're actually changing something ... DECLARE @changedContainsIdentifier bit SET @changedContainsIdentifier = 0 DECLARE @changedContainsFilterDescriptor bit SET @changedContainsFilterDescriptor = 0 SELECT @currentContainsIdentifier=ContainsIdentifier, @currentContainsFilterDescriptor=ContainsFilterDescriptor FROM AR_TypeDescriptor WHERE Id=@current IF (@currentContainsIdentifier != @containsIdentifier) SET @changedContainsIdentifier = 1 IF (@currentContainsFilterDescriptor != @containsFilterDescriptor) SET @changedContainsFilterDescriptor = 1 IF (@changedContainsIdentifier = 1 OR @changedContainsFilterDescriptor = 1) BEGIN UPDATE AR_TypeDescriptor SET ContainsIdentifier = @containsIdentifier, ContainsFilterDescriptor = @containsFilterDescriptor WHERE Id = @current SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
EXEC proc_ar_BumpMetadataObjectVersionCounter @current END
-- update current for next iteration SELECT @current=ParentTypeDescriptorId FROM AR_TypeDescriptor WHERE Id=@current
-- control max typeDescriptor nest level SET @maxNestDepth = @maxNestDepth - 1END
IF(@maxNestDepth = 0)BEGIN SET @ErrorCode = -300 GOTO ON_ERROREND
IF(@TranCount < @@TRANCOUNT) COMMIT TRANSACTIONRETURN ON_ERROR:IF(@TranCount < @@TRANCOUNT) ROLLBACK TRANSACTIONRETURN GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateActionById] Script Date: 06/23/2008 08:04:24 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateActionById] ( @Id int, @Name nvarchar(50), @IsCached bit, @Version int OUTPUT, @Position int, @IsDisplayed bit, @IsOpenedInNewWindow bit, @Icon nvarchar(255), @Url nvarchar(255), @ErrorCode int OUTPUT )AS BEGIN TRAN SET @ErrorCode = 0 -- Lock Action table so no one can insert a Action with the same-- name as the one we may update the existing Action to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 7 --before updating make sure that the action existsSELECT IdFROM AR_ActionWHERE AR_Action.ID = @Id IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 --action not found GOTO ON_ERROREND -- check for duplicatesSELECT AR_Action.IdFROM AR_ActionINNER JOIN AR_MetadataObjectON AR_Action.Id = AR_MetadataObject.IdWHERE AR_Action.EntityId IN (SELECT EntityId FROM AR_Action WHERE Id=@Id) AND AR_MetadataObject.Name = @Name AND AR_Action.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- UpdateUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @IdSELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
UPDATE AR_ACTIONSET Position = @Position, IsDisplayed = @IsDisplayed, IsOpenedInNewWindow = @IsOpenedInNewWindow, Icon = @Icon, Url = @UrlWHERE Id = @IdSELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANRETURN
ON_ERROR:ROLLBACK TRANRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateActionParameterById] Script Date: 06/23/2008 08:04:24 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateActionParameterById] ( @Id int, @IsCached bit, @Version int OUTPUT, @Name nvarchar(50), @Index tinyint, @ErrorCode int OUTPUT
)AS
BEGIN TRAN
SET @ErrorCode = 0
-- Lock ActionParameter table so no one can insert a ActionParameter with the same-- name as the one we may update the existing ActionParameter to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 13
--before updating make sure that the object existsSELECT IdFROM AR_ActionParameterWHERE Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesSELECT AR_ActionParameter.IdFROM AR_ActionParameterINNER JOIN AR_MetadataObjectON AR_ActionParameter.Id = AR_MetadataObject.IdWHERE AR_ActionParameter.ActionId IN (SELECT ActionId FROM AR_ActionParameter WHERE Id=@Id) AND AR_MetadataObject.Name = @Name AND AR_MetadataObject.Id != @Id -- don't consider yourself a duplicate
IF (@@ROWCOUNT != 0)BEGIN SET @ErrorCode = -1 -- Duplicate name GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- UpdateUPDATE AR_METADATAOBJECTSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @IdSELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
UPDATE AR_ACTIONPARAMETERSET [Index] = @IndexWHERE Id = @IdSELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANRETURN
ON_ERROR:ROLLBACK TRANRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateAssociationById] Script Date: 06/23/2008 08:04:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateAssociationById] ( @Id int, @Name nvarchar(255), @IsCached bit, @ReturnTypeDescriptorId int, @Type tinyint, @Version int OUTPUT, -- input/output param @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock Association table so no one can insert a Association with the same-- name as the one we may update the existing Association to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 8
--before updating make sure that the object existsSELECT IdFROM AR_AssociationWHERE Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesDECLARE @MethodId intSELECT @MethodId=MethodId FROM AR_MethodInstance WHERE Id=@Id
DECLARE @EntityId intSELECT @EntityId=ClassId FROM AR_Method WHERE Id=@MethodId
DECLARE @SystemId int SELECT @SystemId=SystemId FROM AR_Class WHERE Id=@EntityId
SELECT AR_Association.IdFROM AR_Association INNER JOIN AR_MethodInstanceON AR_Association.Id = AR_MethodInstance.IdINNER JOIN AR_MetadataObjectON AR_MethodInstance.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_MethodInstance.MethodId IN (SELECT Id FROM AR_Method WHERE ClassId IN (SELECT Id FROM AR_Class WHERE SystemId=@SystemId)) AND AR_MetadataObject.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate name GOTO ON_ERROREND
-- Check object specific constraints-- do not allow this sproc to update ReturnTypeDescriptorId or MethodInstanceType; the parameters are only passed in to support Associations subclassing MethodInstanceDECLARE @oldReturnTypeDescriptorId intDECLARE @oldType tinyintSELECT @oldReturnTypeDescriptorId=ReturnTypeDescriptorId, @oldType=Type FROM AR_MethodInstance WHERE Id=@IdIF( @oldReturnTypeDescriptorId != @ReturnTypeDescriptorId OR @oldType != @Type )BEGIN SET @ErrorCode = -500 GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
ALL_WELL:COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateEntityById] Script Date: 06/23/2008 08:04:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateEntityById] ( @Id int, @Name nvarchar(255), @IsCached bit, @Version int OUTPUT, -- input/output param @SystemId int, @EstimatedInstanceCount int, @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock Entity table so no one can insert an Entity with the same-- name as the one we may update the existing Entity to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 4
--before updating make sure that the object existsSELECT IdFROM AR_EntityWHERE AR_Entity.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesSELECT AR_Entity.IdFROM AR_EntityINNER JOIN AR_ClassON AR_Entity.Id = AR_Class.IdINNER JOIN AR_MetadataObjectON AR_Class.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_Class.SystemId = @SystemId AND AR_MetadataObject.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate name GOTO ON_ERROREND
-- Check for MaxDECLARE @EntitiesForSystem intSET @EntitiesForSystem = (SELECT COUNT (AR_Entity.Id) FROM AR_Entity INNER JOIN AR_Class ON AR_Entity.Id = AR_Class.Id WHERE AR_Class.SystemId = @SystemId)IF(@EntitiesForSystem >= 200)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Update ClassUPDATE AR_ClassSET SystemId = @SystemIdWHERE Id = @Id
-- Update EntityUPDATE AR_EntitySET EstimatedInstanceCount = @EstimatedInstanceCountWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateFilterDescriptorById] Script Date: 06/23/2008 08:04:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateFilterDescriptorById] ( @Id int, @Name nvarchar(255), @IsCached bit, @Version int OUTPUT, -- input/output param-- @MethodId int, -- forbid updating this @TypeName nvarchar(255), @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock FilterDescriptor table so no one can insert a FilterDescriptor with the same-- name as the one we may update the existing FilterDescriptor to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 9
--before updating make sure that the object existsSELECT IdFROM AR_FilterDescriptorWHERE Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesSELECT AR_FilterDescriptor.IdFROM AR_FilterDescriptorINNER JOIN AR_MetadataObjectON AR_FilterDescriptor.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_FilterDescriptor.MethodId IN (SELECT MethodId FROM AR_FilterDescriptor WHERE Id = @Id) AND AR_MetadataObject.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate name GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Update FilterDescriptorUPDATE AR_FilterDescriptorSET TypeName = @TypeNameWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateIdentifierById] Script Date: 06/23/2008 08:04:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateIdentifierById] ( @Id int, @Name nvarchar(255), @IsCached bit, @Version int OUTPUT, -- input/output param-- @EntityId int, -- forbid updating this-- @OrdinalNumber int, - no need; this is automaintained @TypeName nvarchar(255), @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock Identifier table so no one can insert an Identifier with the same-- name as the one we may update the existing Identifier to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 5
--before updating make sure that the object existsSELECT IdFROM AR_IdentifierWHERE AR_Identifier.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesSELECT AR_Identifier.IdFROM AR_IdentifierINNER JOIN AR_MetadataObjectON AR_Identifier.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_Identifier.EntityId IN (SELECT EntityId FROM AR_Identifier WHERE Id = @Id) AND AR_MetadataObject.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate name GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Update IdentifierUPDATE AR_IdentifierSET TypeName = @TypeNameWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateMethodById] Script Date: 06/23/2008 08:04:26 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateMethodById] ( @Id int, @Name nvarchar(255), @IsCached bit, @Version int OUTPUT, -- input/output param-- @ClassId int, -- do not allow update @IsStatic bit, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock Method table so no one can insert a Method with the same-- name as the one we may update the existing Method to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 6
--before updating make sure that the object existsSELECT IdFROM AR_MethodWHERE AR_Method.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesSELECT AR_Method.IdFROM AR_MethodINNER JOIN AR_MetadataObjectON AR_Method.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_Method.ClassId IN (SELECT ClassId FROM AR_Method WHERE Id=@Id) AND AR_MetadataObject.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate name GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Update MethodUPDATE AR_MethodSET IsStatic = @IsStaticWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateMethodInstanceById] Script Date: 06/23/2008 08:04:27 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateMethodInstanceById] ( @Id int, @Name nvarchar(255), @IsCached bit, @Version int OUTPUT, -- input/output param-- @MethodId int, -- do not allow update @ReturnTypeDescriptorId int, @Type tinyint, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock MethodInstance table so no one can insert a MethodInstance with the same-- name as the one we may update the existing MethodInstance to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 10
--before updating make sure that the object existsSELECT IdFROM AR_MethodInstanceWHERE Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- find the class/entity this methodinstance needs to belong toDECLARE @MethodId intSELECT @MethodId=MethodId FROM AR_MethodInstance WHERE Id=@IdDECLARE @ClassId intSELECT @ClassId=ClassId FROM AR_Method WHERE Id = @MethodId
-- Check for duplicate names in all MethodInstances in the entity/class that the given Method belongs toSELECT AR_MethodInstance.IdFROM AR_MethodInstanceINNER JOIN AR_MetadataObjectON AR_MethodInstance.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_MethodInstance.MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @ClassId) AND AR_MetadataObject.Id != @Id
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate metadata name GOTO ON_ERROREND
-- Check object specific constraintsIF(@Type = 1) -- 'Finder' BEGIN IF EXISTS (SELECT Id FROM AR_MethodInstance WHERE Id != @Id AND Type=1 AND MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @ClassId)) BEGIN -- object specific errors start at -100, -200 etc. SET @ErrorCode = -200 --More than 1 'Finder' method instance in entity GOTO ON_ERROR ENDEND
IF(@Type = 2) -- 'SpecificFinder' BEGIN IF EXISTS (SELECT Id FROM AR_MethodInstance WHERE Id != @Id AND Type=2 AND MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @ClassId)) BEGIN SET @ErrorCode = -201 --More than 1 'SpecificFinder' method instance in entity GOTO ON_ERROR ENDEND
IF(@Type = 5) -- 'IdEnumerator' BEGIN IF EXISTS (SELECT Id FROM AR_MethodInstance WHERE Id != @Id AND Type=5 AND MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @ClassId)) BEGIN SET @ErrorCode = -202 --More than 1 'IdEnumerator' method instance in entity GOTO ON_ERROR ENDEND
IF(@Type = 7) -- 'AccessChecker' BEGIN IF EXISTS (SELECT Id FROM AR_MethodInstance WHERE Id != @Id AND Type=7 AND MethodId IN (SELECT Id FROM AR_Method WHERE ClassId = @ClassId)) BEGIN SET @ErrorCode = -205 --More than 1 'IdEnumerator' method instance in entity GOTO ON_ERROR ENDEND
DECLARE @ReturnTypeDescriptorMethodId intDECLARE @Direction intSELECT @ReturnTypeDescriptorMethodId=MethodId, @Direction=Direction FROM AR_Parameter WHERE Id IN (SELECT ParameterId FROM AR_TypeDescriptor WHERE Id=@ReturnTypeDescriptorId)IF(@ReturnTypeDescriptorMethodId != @MethodId)BEGIN SET @ErrorCode = -203 -- Return typeDescriptor's parameter's method and this method instance's method are not the same GOTO ON_ERROREND
IF(@Direction = 1) -- 1 = DirectionType.InBEGIN SET @ErrorCode = -204 -- Return type descriptor's parameter's direction is DirectionType.In GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Update MethodUPDATE AR_MethodInstanceSET Type = @Type, ReturnTypeDescriptorId = @ReturnTypeDescriptorIdWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateParameterById] Script Date: 06/23/2008 08:04:27 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateParameterById] ( @Id int, @Name nvarchar(255), @IsCached bit, @Version int OUTPUT, -- input/output param-- @MethodId int, -- forbid updating this @OrdinalNumber tinyint OUTPUT, -- input/output param @Direction tinyint, @TypeReflectorTypeName nvarchar(255), @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock Parameter table so no one can insert a Parameter with the same-- name as the one we may update the existing Parameter to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 11
--before updating make sure that the object existsSELECT IdFROM AR_ParameterWHERE Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesDECLARE @MethodId intSELECT @MethodId = MethodId FROM AR_Parameter WHERE Id = @IdSELECT AR_Parameter.IdFROM AR_ParameterINNER JOIN AR_MetadataObjectON AR_Parameter.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_Parameter.MethodId = @MethodId AND AR_MetadataObject.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate name GOTO ON_ERROREND
-- Check object specific constraintsIF(@Direction = 4) -- DirectionType.Return directionBEGIN IF EXISTS (SELECT Id FROM AR_Parameter WHERE MethodId = @MethodId AND Direction=4 AND Id!=@Id) BEGIN -- object specific errors start at -100, -200 etc. SET @ErrorCode = -100 --More than 1 'Return' parameter GOTO ON_ERROR ENDEND
IF(@Direction = 1) -- DirectionType.In BEGIN -- If there exists a MethodInstance such that its ReturnTypeDescriptor belongs to the set of TypeDescriptors used by this Parameter IF EXISTS (SELECT Id FROM AR_MethodInstance WHERE ReturnTypeDescriptorId IN (SELECT Id FROM AR_TypeDescriptor WHERE ParameterId = @Id)) BEGIN SET @ErrorCode = -102 --Can't have Parameter DirectionType.In if it's used by a ReturnTypeDescriptor in some other MethodInstance GOTO ON_ERROR ENDEND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- save old ordinalDECLARE @oldOrdinalNumber intSELECT @oldOrdinalNumber=OrdinalNumber FROM AR_Parameter WHERE Id=@Id
-- Update ParameterDECLARE @NumParametersInMethod intSELECT @NumParametersInMethod = COUNT(*) FROM AR_Parameter WHERE MethodId = @MethodIdIF (@OrdinalNumber < ordinalnumber =" 0IF"> @NumParametersInMethod) SET @OrdinalNumber=@NumParametersInMethod - 1
UPDATE AR_ParameterSET TypeReflectorTypeName = @TypeReflectorTypeName, Direction = @Direction, OrdinalNumber = @OrdinalNumberWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- figure out if ordinal update caused upward or downward movement of parameterIF(@oldOrdinalNumber != @OrdinalNumber)BEGIN DECLARE @startIndex tinyint DECLARE @endIndex tinyint DECLARE @OrdinalNumberCounter tinyint
IF( @oldOrdinalNumber > @OrdinalNumber ) BEGIN -- move up case SET @startIndex = @OrdinalNumber+1 SET @endIndex = @NumParametersInMethod
-- now reorder ordinals between @startIndex and @endIndex DECLARE ordinalNumberCursor CURSOR LOCAL FORWARD_ONLY KEYSET FOR -- issue query for all params excluding the one to be updated SELECT Id, OrdinalNumber FROM AR_Parameter WHERE MethodId = @MethodId AND Id != @Id ORDER BY OrdinalNumber DESC FOR UPDATE OF OrdinalNumber
OPEN ordinalNumberCursor
SET @OrdinalNumberCounter = @NumParametersInMethod-1 FETCH NEXT FROM ordinalNumberCursor WHILE(@@FETCH_STATUS=0) BEGIN IF( @OrdinalNumberCounter >= @startIndex AND @OrdinalNumberCounter < @endIndex) BEGIN UPDATE AR_Parameter SET OrdinalNumber=@OrdinalNumberCounter WHERE CURRENT OF ordinalNumberCursor EXEC proc_ar_BumpMetadataObjectVersionCounter @Id END SET @OrdinalNumberCounter = @OrdinalNumberCounter - 1 FETCH NEXT FROM ordinalNumberCursor END DEALLOCATE ordinalNumberCursor END -- end move up case ELSE BEGIN -- start move down case SET @startIndex = 0 SET @endIndex = @OrdinalNumber
-- now reorder ordinals between @startIndex and @endIndex DECLARE ordinalNumberCursor CURSOR LOCAL FORWARD_ONLY KEYSET FOR -- issue query for all params excluding the one to be updated SELECT Id, OrdinalNumber FROM AR_Parameter WHERE MethodId = @MethodId AND Id != @Id ORDER BY OrdinalNumber ASC FOR UPDATE OF OrdinalNumber
OPEN ordinalNumberCursor
SET @OrdinalNumberCounter = 0 FETCH NEXT FROM ordinalNumberCursor WHILE(@@FETCH_STATUS=0) BEGIN IF( @OrdinalNumberCounter >= @startIndex AND @OrdinalNumberCounter < @endIndex) BEGIN UPDATE AR_Parameter SET OrdinalNumber=@OrdinalNumberCounter WHERE CURRENT OF ordinalNumberCursor EXEC proc_ar_BumpMetadataObjectVersionCounter @Id END SET @OrdinalNumberCounter = @OrdinalNumberCounter + 1 FETCH NEXT FROM ordinalNumberCursor END DEALLOCATE ordinalNumberCursor END -- end move down case
END -- end @oldOrdinalNumber != @OrdinalNumber Test
ALL_WELL:COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateSystemById] Script Date: 06/23/2008 08:04:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateSystemById] ( @Id int, @Name nvarchar(255), @IsCached bit, @Version int OUTPUT, -- input/output param @SystemUtilityTypeName nvarchar(255), @ConnectionManagerTypeName nvarchar(255), @EntityInstanceTypeName nvarchar(255), @ErrorCode int OUTPUT
)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock System table so no one can insert a System with the same-- name as the one we may update the existing System to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 2
--before updating make sure that the object existsSELECT IdFROM AR_SystemWHERE AR_System.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesSELECT AR_System.IdFROM AR_SystemINNER JOIN AR_MetadataObjectON AR_System.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_MetadataObject.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate name GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Update SystemUPDATE AR_SystemSET SystemUtilityTypeName = @SystemUtilityTypeName, ConnectionFactoryTypeName = @ConnectionManagerTypeName, SystemEntityTypeName = @EntityInstanceTypeNameWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateSystemInstanceById] Script Date: 06/23/2008 08:04:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateSystemInstanceById] ( @Id int, @Name nvarchar(255), @IsCached bit, @Version int OUTPUT, -- input/output param @SystemId int, @ErrorCode int OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock SystemInst table so no one can insert a SystemInst with the same-- name as the one we may change the existing SystemInst to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 3 -- system instance table
--before updating make sure that the object existsSELECT IdFROM AR_SystemInstanceWHERE AR_SystemInstance.Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesSELECT AR_SystemInstance.IdFROM AR_SystemInstanceINNER JOIN AR_MetadataObjectON AR_SystemInstance.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_SystemInstance.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate name GOTO ON_ERROREND
-- Check for MaxDECLARE @SystemInstancesForSystem intSET @SystemInstancesForSystem = (SELECT COUNT (Id) FROM AR_SystemInstance WHERE SystemId = @SystemId)IF( @SystemInstancesForSystem >= 300)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Update SystemInstanceUPDATE AR_SystemInstanceSET SystemId = @SystemId WHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN
GO/****** Object: StoredProcedure [dbo].[proc_ar_UpdateTypeDescriptorById] Script Date: 06/23/2008 08:04:28 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE Procedure [dbo].[proc_ar_UpdateTypeDescriptorById] ( @Id int, @Name nvarchar(255), @IsCached bit, @Version int OUTPUT, -- input/output param-- @ParameterId int, -- forbid updating this @ParentTypeDescriptorId int, @TypeName nvarchar(255), @IdentifierId int, @FilterDescriptorId int, @IsCollection bit, @ErrorCode int OUTPUT, @ContainsIdentifier bit OUTPUT, @ContainsFilterDescriptor bit OUTPUT)AS
SET @ErrorCode = 0
BEGIN TRANSACTION-- Lock TypeDescriptor table so no one can insert a TypeDescriptor with the same-- name as the one we may update the existing TypeDescriptor to ...SELECT LockIdFROM AR_AdminLocksWITH (HOLDLOCK)WHERE LockId = 12
--before updating make sure that the object existsSELECT IdFROM AR_TypeDescriptorWHERE Id = @Id
IF (@@ROWCOUNT = 0)BEGIN SET @ErrorCode = -2 -- not found GOTO ON_ERROREND
-- Check for duplicatesDECLARE @ParameterId intDECLARE @OldParentTypeDescriptorId intSELECT @OldParentTypeDescriptorId=ParentTypeDescriptorId, @ParameterId=ParameterId FROM AR_TypeDescriptor WHERE Id = @Id
SELECT AR_TypeDescriptor.IdFROM AR_TypeDescriptorINNER JOIN AR_MetadataObjectON AR_TypeDescriptor.Id = AR_MetadataObject.IdWHERE AR_MetadataObject.Name = @Name AND AR_TypeDescriptor.ParameterId = @ParameterId AND AR_TypeDescriptor.ParentTypeDescriptorId IN (SELECT ParentTypeDescriptorId FROM AR_TypeDescriptor WHERE Id=@Id) AND AR_MetadataObject.Id != @Id -- don't consider yourself as a duplicate
IF (@@ROWCOUNT <> 0)BEGIN SET @ErrorCode = -1 --Duplicate name GOTO ON_ERROREND
-- Check for MaxDECLARE @ChildTypeDescriptors intDECLARE @TypeDescriptorsRelatedToFilterDescriptor intSET @ChildTypeDescriptors = (SELECT COUNT (Id) FROM AR_TypeDescriptor WHERE ParentTypeDescriptorId = @ParentTypeDescriptorId AND ParameterId = @ParameterId AND @ParentTypeDescriptorId IS NOT NULL)SET @TypeDescriptorsRelatedToFilterDescriptor = (SELECT COUNT (Id) FROM AR_TypeDescriptor WHERE FilterDescriptorId = @FilterDescriptorId AND @FilterDescriptorId IS NOT NULL)IF (@ChildTypeDescriptors >= 300 OR @TypeDescriptorsRelatedToFilterDescriptor >= 10)BEGIN SET @ErrorCode = -3 GOTO ON_ERROREND
-- Check object specific constraintsIF(@ParentTypeDescriptorId IS NULL AND EXISTS (SELECT Id FROM AR_TypeDescriptor WHERE ParameterId=@ParameterId AND ParentTypeDescriptorId IS NULL AND Id != @Id))BEGIN SET @ErrorCode = -302 -- cannot have multiple root type descriptors for a single parameter GOTO ON_ERROREND
DECLARE @FilterDescriptorMethodId intDECLARE @TypeDescriptorMethodId intSELECT @FilterDescriptorMethodId=MethodId FROM AR_FilterDescriptor WHERE Id=@FilterDescriptorIdSELECT @TypeDescriptorMethodId=MethodId FROM AR_Parameter WHERE Id IN (SELECT ParameterId FROM AR_TypeDescriptor WHERE Id=@Id)IF(@FilterDescriptorId IS NOT NULL AND @TypeDescriptorMethodId != @FilterDescriptorMethodId)BEGIN SET @ErrorCode = -303 -- filter doesn't belong to the same method as the typedescriptor GOTO ON_ERROREND
DECLARE @ParentsParameterId intSELECT @ParentsParameterId=ParameterId FROM AR_TypeDescriptor WHERE Id=@ParentTypeDescriptorIdIF(@ParentTypeDescriptorId IS NOT NULL AND @ParameterId != @ParentsParameterId)BEGIN SET @ErrorCode = -304 GOTO ON_ERROREND
IF(@IsCollection=1 AND EXISTS (SELECT Id FROM AR_TypeDescriptor WHERE Id = @ParentTypeDescriptorId AND IsCollection = 1))BEGIN SET @ErrorCode = -305 -- cannot have parent-child typedescriptors with IsCollection set GOTO ON_ERROREND
IF(@IsCollection=1 AND EXISTS (SELECT Id FROM AR_TypeDescriptor WHERE ParentTypeDescriptorId = @Id AND IsCollection = 1))BEGIN SET @ErrorCode = -305 -- cannot have parent-child typedescriptors with IsCollection set GOTO ON_ERROREND
IF(@IsCollection=1)BEGIN DECLARE @ChildCount int SELECT @ChildCount=COUNT(Id) FROM AR_TypeDescriptor WHERE ParentTypeDescriptorId = @Id IF(@ChildCount > 1) BEGIN SET @ErrorCode = -306 -- a type desc with IsCollection can have only one child GOTO ON_ERROR ENDEND
-- Optimistic concurrency checkDECLARE @DBVersion intSELECT @DBVersion = VersionFROM AR_MetadataObjectWHERE AR_MetadataObject.Id = @Id
IF (@DBVersion != @Version)BEGIN SET @ErrorCode = -6 GOTO ON_ERROREND
SET @Version = @Version + 1IF @Version >= 2147483646 SET @Version = 0
-- Update MetadataObjectUPDATE AR_MetadataObjectSET Name = @Name, IsCached = @IsCached, Version = @VersionWHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- Update TypeDescriptorUPDATE AR_TypeDescriptorSET ParentTypeDescriptorId = @ParentTypeDescriptorId, TypeName = @TypeName, InterpretedTypeName = @TypeName, ContainsIdentifier = 0, IdentifierId = @IdentifierId, ContainsFilterDescriptor = 0, FilterDescriptorId = @FilterDescriptorId, IsCollection = @IsCollection WHERE Id = @Id
SELECT @ErrorCode=@@Error IF(@ErrorCode <> 0) GOTO ON_ERROR
-- fixup
-- if the parent changes, we may need to fix up the old parentIF( @OldParentTypeDescriptorId != @ParentTypeDescriptorId AND @OldParentTypeDescriptorId IS NOT NULL)BEGIN EXEC proc_ar_TypeDescriptorContainsFlagsFixup @OldParentTypeDescriptorId, @ErrorCode OUTPUT IF(@ErrorCode != 0) GOTO ON_ERROREND
-- now fix up the current node; we could optimize this away by checking if the contains* flags changed in the futureEXEC proc_ar_TypeDescriptorContainsFlagsFixup @Id, @ErrorCode OUTPUTIF(@ErrorCode != 0) GOTO ON_ERROR
-- set up return params; version is included because the fixups may change itSELECT @ContainsIdentifier=ContainsIdentifier, @ContainsFilterDescriptor=ContainsFilterDescriptor FROM AR_TypeDescriptor WHERE Id=@IdSELECT @Version=Version FROM AR_MetadataObject WHERE Id=@Id
COMMIT TRANSACTIONRETURN
ON_ERROR:IF(@@TRANCOUNT > 0) ROLLBACK TRANSACTIONRETURN