text: ﻿/*************************************************************
      Stored procedures - ReindexResource_2
  **************************************************************/
  --
  -- STORED PROCEDURE
  --     ReindexResource_2
  --
  -- DESCRIPTION
  --     Updates the search indices of a given resource
  --
  -- PARAMETERS
  --     @resourceTypeId
  --         * The ID of the resource type (See ResourceType table)
  --     @resourceId
  --         * The resource ID (must be the same as the in the resource itself)
  --     @etag
  --         * If specified, the version of the resource to update
  --     @searchParamHash
  --          * A hash of the resource's latest indexed search parameters
  --     @resourceWriteClaims
  --         * Claims on the principal that performed the write
  --     @compartmentAssignments
  --         * Compartments that the resource is part of
  --     @referenceSearchParams
  --         * Extracted reference search params
  --     @tokenSearchParams
  --         * Extracted token search params
  --     @tokenTextSearchParams
  --         * The text representation of extracted token search params
  --     @stringSearchParams
  --         * Extracted string search params
  --     @numberSearchParams
  --         * Extracted number search params
  --     @quantitySearchParams
  --         * Extracted quantity search params
  --     @uriSearchParams
  --         * Extracted URI search params
  --     @dateTimeSearchParms
  --         * Extracted datetime search params
  --     @referenceTokenCompositeSearchParams
  --         * Extracted reference$token search params
  --     @tokenTokenCompositeSearchParams
  --         * Extracted token$token tokensearch params
  --     @tokenDateTimeCompositeSearchParams
  --         * Extracted token$datetime search params
  --     @tokenQuantityCompositeSearchParams
  --         * Extracted token$quantity search params
  --     @tokenStringCompositeSearchParams
  --         * Extracted token$string search params
  --     @tokenNumberNumberCompositeSearchParams
  --         * Extracted token$number$number search params
  --
  CREATE PROCEDURE dbo.ReindexResource_2
      @resourceTypeId smallint,
      @resourceId varchar(64),
      @eTag int = NULL,
      @searchParamHash varchar(64),
      @resourceWriteClaims dbo.BulkResourceWriteClaimTableType_1 READONLY,
      @compartmentAssignments dbo.BulkCompartmentAssignmentTableType_1 READONLY,
      @referenceSearchParams dbo.BulkReferenceSearchParamTableType_1 READONLY,
      @tokenSearchParams dbo.BulkTokenSearchParamTableType_1 READONLY,
      @tokenTextSearchParams dbo.BulkTokenTextTableType_1 READONLY,
      @stringSearchParams dbo.BulkStringSearchParamTableType_2 READONLY,
      @numberSearchParams dbo.BulkNumberSearchParamTableType_1 READONLY,
      @quantitySearchParams dbo.BulkQuantitySearchParamTableType_1 READONLY,
      @uriSearchParams dbo.BulkUriSearchParamTableType_1 READONLY,
      @dateTimeSearchParms dbo.BulkDateTimeSearchParamTableType_2 READONLY,
      @referenceTokenCompositeSearchParams dbo.BulkReferenceTokenCompositeSearchParamTableType_1 READONLY,
      @tokenTokenCompositeSearchParams dbo.BulkTokenTokenCompositeSearchParamTableType_1 READONLY,
      @tokenDateTimeCompositeSearchParams dbo.BulkTokenDateTimeCompositeSearchParamTableType_1 READONLY,
      @tokenQuantityCompositeSearchParams dbo.BulkTokenQuantityCompositeSearchParamTableType_1 READONLY,
      @tokenStringCompositeSearchParams dbo.BulkTokenStringCompositeSearchParamTableType_1 READONLY,
      @tokenNumberNumberCompositeSearchParams dbo.BulkTokenNumberNumberCompositeSearchParamTableType_1 READONLY
  AS
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  BEGIN TRANSACTION;
  DECLARE @resourceSurrogateId AS BIGINT;
  DECLARE @version AS BIGINT;
  
  -- This should place a range lock on a row in the IX_Resource_ResourceTypeId_ResourceId nonclustered filtered index
  SELECT @resourceSurrogateId = ResourceSurrogateId,
         @version = Version
  FROM   dbo.Resource WITH (UPDLOCK, HOLDLOCK)
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceId = @resourceId
         AND IsHistory = 0;
  IF (@etag IS NOT NULL
      AND @etag <> @version)
      BEGIN
          THROW 50412, 'Precondition failed', 1;
      END
  UPDATE dbo.Resource
  SET    SearchParamHash = @searchParamHash
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  
  -- First, delete all the resource's indices.
  DELETE dbo.ResourceWriteClaim
  WHERE  ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.CompartmentAssignment
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.ReferenceSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.TokenSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.TokenText
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.StringSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.UriSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.NumberSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.QuantitySearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.DateTimeSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.ReferenceTokenCompositeSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.TokenTokenCompositeSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.TokenDateTimeCompositeSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.TokenQuantityCompositeSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.TokenStringCompositeSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  DELETE dbo.TokenNumberNumberCompositeSearchParam
  WHERE  ResourceTypeId = @resourceTypeId
         AND ResourceSurrogateId = @resourceSurrogateId;
  
  -- Next, insert all the new indices.
  INSERT INTO dbo.ResourceWriteClaim (ResourceSurrogateId, ClaimTypeId, ClaimValue)
  SELECT @resourceSurrogateId,
         ClaimTypeId,
         ClaimValue
  FROM   @resourceWriteClaims;
  INSERT INTO dbo.CompartmentAssignment (ResourceTypeId, ResourceSurrogateId, CompartmentTypeId, ReferenceResourceId, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  CompartmentTypeId,
                  ReferenceResourceId,
                  0
  FROM   @compartmentAssignments;
  INSERT INTO dbo.ReferenceSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, BaseUri, ReferenceResourceTypeId, ReferenceResourceId, ReferenceResourceVersion, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  BaseUri,
                  ReferenceResourceTypeId,
                  ReferenceResourceId,
                  ReferenceResourceVersion,
                  0
  FROM   @referenceSearchParams;
  INSERT INTO dbo.TokenSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, SystemId, Code, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  SystemId,
                  Code,
                  0
  FROM   @tokenSearchParams;
  INSERT INTO dbo.TokenText (ResourceTypeId, ResourceSurrogateId, SearchParamId, Text, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  Text,
                  0
  FROM   @tokenTextSearchParams;
  INSERT INTO dbo.StringSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, Text, TextOverflow, IsHistory, IsMin, IsMax)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  Text,
                  TextOverflow,
                  0,
                  IsMin,
                  IsMax
  FROM   @stringSearchParams;
  INSERT INTO dbo.UriSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, Uri, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  Uri,
                  0
  FROM   @uriSearchParams;
  INSERT INTO dbo.NumberSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, SingleValue, LowValue, HighValue, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  SingleValue,
                  LowValue,
                  HighValue,
                  0
  FROM   @numberSearchParams;
  INSERT INTO dbo.QuantitySearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, SystemId, QuantityCodeId, SingleValue, LowValue, HighValue, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  SystemId,
                  QuantityCodeId,
                  SingleValue,
                  LowValue,
                  HighValue,
                  0
  FROM   @quantitySearchParams;
  INSERT INTO dbo.DateTimeSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, StartDateTime, EndDateTime, IsLongerThanADay, IsHistory, IsMin, IsMax)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  StartDateTime,
                  EndDateTime,
                  IsLongerThanADay,
                  0,
                  IsMin,
                  IsMax
  FROM   @dateTimeSearchParms;
  INSERT INTO dbo.ReferenceTokenCompositeSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, BaseUri1, ReferenceResourceTypeId1, ReferenceResourceId1, ReferenceResourceVersion1, SystemId2, Code2, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  BaseUri1,
                  ReferenceResourceTypeId1,
                  ReferenceResourceId1,
                  ReferenceResourceVersion1,
                  SystemId2,
                  Code2,
                  0
  FROM   @referenceTokenCompositeSearchParams;
  INSERT INTO dbo.TokenTokenCompositeSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, SystemId1, Code1, SystemId2, Code2, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  SystemId1,
                  Code1,
                  SystemId2,
                  Code2,
                  0
  FROM   @tokenTokenCompositeSearchParams;
  INSERT INTO dbo.TokenDateTimeCompositeSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, SystemId1, Code1, StartDateTime2, EndDateTime2, IsLongerThanADay2, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  SystemId1,
                  Code1,
                  StartDateTime2,
                  EndDateTime2,
                  IsLongerThanADay2,
                  0
  FROM   @tokenDateTimeCompositeSearchParams;
  INSERT INTO dbo.TokenQuantityCompositeSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, SystemId1, Code1, SingleValue2, SystemId2, QuantityCodeId2, LowValue2, HighValue2, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  SystemId1,
                  Code1,
                  SingleValue2,
                  SystemId2,
                  QuantityCodeId2,
                  LowValue2,
                  HighValue2,
                  0
  FROM   @tokenQuantityCompositeSearchParams;
  INSERT INTO dbo.TokenStringCompositeSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, SystemId1, Code1, Text2, TextOverflow2, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  SystemId1,
                  Code1,
                  Text2,
                  TextOverflow2,
                  0
  FROM   @tokenStringCompositeSearchParams;
  INSERT INTO dbo.TokenNumberNumberCompositeSearchParam (ResourceTypeId, ResourceSurrogateId, SearchParamId, SystemId1, Code1, SingleValue2, LowValue2, HighValue2, SingleValue3, LowValue3, HighValue3, HasRange, IsHistory)
  SELECT DISTINCT @resourceTypeId,
                  @resourceSurrogateId,
                  SearchParamId,
                  SystemId1,
                  Code1,
                  SingleValue2,
                  LowValue2,
                  HighValue2,
                  SingleValue3,
                  LowValue3,
                  HighValue3,
                  HasRange,
                  0
  FROM   @tokenNumberNumberCompositeSearchParams;
  COMMIT TRANSACTION;
  GO
  