/Kilimanjaro_Trunk/DialogPool/Scripts/2 - Dialog pool setup.sql

  1--------------------------------------------------------------------
  2-- Script for dialog pool sample.
  3--
  4-- This file is part of the Microsoft SQL Server Code Samples.
  5-- Copyright (C) Microsoft Corporation. All Rights reserved.
  6-- This source code is intended only as a supplement to Microsoft
  7-- Development Tools and/or on-line documentation. See these other
  8-- materials for detailed information regarding Microsoft code samples.
  9--
 10-- THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
 11-- ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO
 12-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
 13-- PARTICULAR PURPOSE.
 14--------------------------------------------------------------------
 15
 16--------------------------------------------------------------------------
 17-- Dialog pool setup.
 18--------------------------------------------------------------------------
 19
 20USE SsbDemoDb;
 21GO
 22
 23--------------------------------------------------------------------------
 24-- The dialog pool table.
 25-- Obtain a conversation handle using from service, to service, and contract.
 26-- Also indicates age and usage of dialog for auditing purposes.
 27--------------------------------------------------------------------------
 28
 29IF EXISTS (SELECT name FROM sys.tables WHERE name = 'DialogPool')
 30      DROP TABLE DialogPool;
 31GO
 32
 33CREATE TABLE DialogPool (
 34      FromService SYSNAME NOT NULL,
 35      ToService SYSNAME NOT NULL,
 36      OnContract SYSNAME NOT NULL,
 37      Handle UNIQUEIDENTIFIER NOT NULL,
 38      OwnerSPID INT NOT NULL,
 39      CreationTime DATETIME NOT NULL,
 40      SendCount BIGINT NOT NULL,
 41      UNIQUE (Handle));
 42GO
 43
 44--------------------------------------------------------------------------
 45-- Get dialog procedure.
 46-- Reuse a free dialog in the pool or create a new one in case
 47-- no free dialogs exist.
 48-- Input is from service, to service, and contract.
 49-- Output is dialog handle and count of message previously sent on dialog.
 50--------------------------------------------------------------------------
 51
 52IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_get_dialog')
 53      DROP PROC usp_get_dialog;
 54GO
 55
 56CREATE PROCEDURE usp_get_dialog (
 57      @fromService SYSNAME,
 58      @toService SYSNAME,
 59      @onContract SYSNAME,
 60      @dialogHandle UNIQUEIDENTIFIER OUTPUT,
 61      @sendCount BIGINT OUTPUT)
 62AS
 63BEGIN
 64      SET NOCOUNT ON;
 65
 66      DECLARE @dialog TABLE
 67      (
 68          FromService SYSNAME NOT NULL,
 69          ToService SYSNAME NOT NULL,
 70          OnContract SYSNAME NOT NULL,
 71          Handle UNIQUEIDENTIFIER NOT NULL,
 72          OwnerSPID INT NOT NULL,
 73          CreationTime DATETIME NOT NULL,
 74          SendCount BIGINT NOT NULL
 75      );
 76
 77      -- Try to claim an unused dialog in DialogPool
 78      -- READPAST option avoids blocking on locked dialogs.
 79      BEGIN TRANSACTION;
 80      DELETE @dialog;
 81      UPDATE TOP(1) DialogPool WITH(READPAST)
 82             SET OwnerSPID = @@SPID
 83             OUTPUT INSERTED.* INTO @dialog
 84             WHERE FromService = @fromService
 85                   AND ToService = @toService
 86                   AND OnContract = @OnContract
 87                   AND OwnerSPID = -1;
 88      IF @@ROWCOUNT > 0
 89      BEGIN
 90           SET @dialogHandle = (SELECT Handle FROM @dialog);
 91           SET @sendCount = (SELECT SendCount FROM @dialog);
 92      END
 93      ELSE
 94      BEGIN
 95           -- No free dialogs: need to create a new one
 96           BEGIN DIALOG CONVERSATION @dialogHandle
 97                 FROM SERVICE @fromService
 98                 TO SERVICE @toService
 99                 ON CONTRACT @onContract
100                 WITH ENCRYPTION = OFF;
101
102           INSERT INTO DialogPool
103                  (FromService, ToService, OnContract, Handle, OwnerSPID,
104                      CreationTime, SendCount)
105                  VALUES
106                  (@fromService, @toService, @onContract, @dialogHandle, @@SPID,
107                      GETDATE(), 0);
108          SET @sendCount = 0;
109      END
110      COMMIT;
111END;
112GO
113
114--------------------------------------------------------------------------
115-- Free dialog procedure.
116-- Return the dialog to the pool.
117-- Inputs are dialog handle and updated send count.
118--------------------------------------------------------------------------
119
120IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_free_dialog')
121      DROP PROC usp_free_dialog;
122GO
123
124CREATE PROCEDURE usp_free_dialog (
125      @dialogHandle UNIQUEIDENTIFIER,
126      @sendCount BIGINT)
127AS
128BEGIN
129      SET NOCOUNT ON;
130
131      DECLARE @rowcount INT;
132      DECLARE @string VARCHAR(50);
133
134      BEGIN TRANSACTION;
135
136      -- Release dialog by setting OwnerSPID to -1.
137      UPDATE [DialogPool] SET OwnerSPID = -1, SendCount = @sendCount WHERE Handle = @dialogHandle;
138      SELECT @rowcount = @@ROWCOUNT;
139      IF @rowcount = 0
140      BEGIN
141           SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));
142           RAISERROR('usp_free_dialog: dialog %s not found in dialog pool', 16, 1, @string) WITH LOG;
143      END
144      ELSE IF @rowcount > 1
145      BEGIN
146           SET @string = (SELECT CAST( @dialogHandle AS VARCHAR(50)));
147           RAISERROR('usp_free_dialog: duplicate dialog %s found in dialog pool', 16, 1, @string) WITH LOG;
148      END
149
150      COMMIT;
151END;
152GO
153
154--------------------------------------------------------------------------
155-- Delete dialog procedure.
156-- Delete the dialog from the pool. This does not end the dialog.
157-- Input is dialog handle.
158--------------------------------------------------------------------------
159IF EXISTS (SELECT name FROM sys.procedures WHERE name = 'usp_delete_dialog')
160      DROP PROC usp_delete_dialog;
161GO
162
163CREATE PROCEDURE usp_delete_dialog (
164      @dialogHandle UNIQUEIDENTIFIER)
165AS
166BEGIN
167      SET NOCOUNT ON;
168
169      BEGIN TRANSACTION;
170      DELETE [DialogPool] WHERE Handle = @dialogHandle;
171      COMMIT;
172END;
173GO