Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 ServiceBroker и ловля UPDATE в таблице. Вариант реализации.  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Добрый день.
Суть:
Есть служба, настройки которой хранятся в БД.
Есть клиентское ПО, которое имеет UI и может менять эти настройки.
Надо чтобы служба во время работы без перезапуска видела изменения настроек.
Варианты решения:
1) Служба в отдельной нити по таймеру читает таблицу настроек. - нет
2) В службе реализовать API по изменению настроек и из клиента напрямую высылать изменения например по TCP. - нет
3) Использовать ServiceBroker. (Похоже на 1 пункт но ловит только то, что изменилось, а не всю таблицу)
Я выбрал 3 вариант.
Служб может быть развернуто несколько и все смотрят в одну таблицу настроек. Соответственно уведомление должны получить все.
т.к. ServiceBroker для меня не знаком, то методом проб и ошибок удалось реализовать следующее:
CREATE MESSAGE TYPE [SettingsMessage] VALIDATION = NONE
CREATE CONTRACT [SettingsMessageContract] ([SettingsMessage] SENT BY INITIATOR)
CREATE QUEUE [dbo].[SettingsMessageQueueSender]
CREATE SERVICE [SettingsMessageServiceSender]  ON QUEUE [dbo].[SettingsMessageQueueSender] ([SettingsMessageContract])

Пока сообщение без валидации, но в будущем прикручу xsd.
Формат xml примерно такой:
<XML><Row Id="" Value="" /><Row Id="" Value="" />...</XML>

Создал очередь и сервис отправления.. Она всегда будет одна.
Очередей приемщиков будет столько, сколько служб и создаваться они будут при старте службы. Соответственно при остановке службы они будут удаляться.
CREATE QUEUE [%s]
CREATE SERVICE [%s] ON QUEUE [%s] ([SettingsMessageContract])

где имя это SettingsMessageQueueReceiver_{GUID}
Тут важно понимать что нам никогда не известно имя сервиса приемщика, оно каждый раз при запуске генерируется через GUID.
Повесил на таблицу триггер на UPDATE:
  DECLARE
  	@ReceiverName SYSNAME,
  	@SQL NVARCHAR(MAX),
  	@XmlText NVARCHAR(MAX)
  SET @XmlText = (SELECT
                    i.Id_Settings,
                    COALESCE(i.ValueInt, i.ValueStr) AS [Value] -- тут говнокод, не обращайте внимания
                  FROM
                    INSERTED AS i FOR XML RAW, ROOT(N'SettingsMessage'))
  DECLARE cur CURSOR LOCAL FOR
    SELECT
      s2.name
    FROM
      sys.services AS s
      JOIN sys.service_contract_usages AS scu
        ON scu.service_id = s.service_id
      JOIN sys.service_contract_usages AS scu2
        ON scu2.service_contract_id = scu.service_contract_id
      JOIN sys.services AS s2
        ON s2.service_id = scu2.service_id
    WHERE
      s.name = N'SettingsMessageServiceSender' AND
      s2.name <> N'SettingsMessageServiceSender'
  OPEN cur
  FETCH FROM cur INTO @ReceiverName
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @SQL = N'DECLARE @A UNIQUEIDENTIFIER; '
    SET @SQL = @SQL + N'BEGIN DIALOG CONVERSATION @A FROM SERVICE [SettingsMessageServiceSender] TO SERVICE N''' + @ReceiverName + N''' ON CONTRACT [SettingsMessageContract] WITH ENCRYPTION = OFF; '
    SET @SQL = @SQL + N'SEND ON CONVERSATION @A MESSAGE TYPE [SettingsMessage] (N''' + @XmlText + '''); '
    SET @SQL = @SQL + N'END CONVERSATION @A'
    EXEC (@SQL)
    FETCH FROM cur INTO @ReceiverName
  END
  CLOSE cur
  DEALLOCATE cur

Суть в том что в триггере определяем список сервисов через контракт и всем рассылаем уведомление.
Затем в службе в отдельной нити крутится Query который дергает свою очередь:
  DECLARE
  	@SQL NVARCHAR(MAX)

  SET @SQL = N'DECLARE
	               @MessageBody NVARCHAR(MAX),
	               @Handle UNIQUEIDENTIFIER,
	               @MessageType SYSNAME

               WAITFOR
               (
                 RECEIVE TOP(1)
                   @Handle = [conversation_handle],
                   @MessageType = message_type_name,
                   @MessageBody = CONVERT(NVARCHAR(MAX), message_body)    
                 FROM
                   [' + @SettingsMessageQueueReceiver + ']
               )

               IF (@MessageType = N''http://schemas.microsoft.com/SQL/ServiceBroker/Error'') OR
                  (@MessageType = N''http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'')
               BEGIN
                 END CONVERSATION @Handle;
                 SELECT NULL WHERE 1 = 0
               END
               ELSE
               BEGIN
                 SELECT
                   @MessageBody	AS MessageBody
               END'
  
  EXEC (@SQL)

В целом все хорошо, все работает. С правами правда помучался, но победил...
Так вот, вопрос. Имеет ли такой вариант на жизнь или с помощью ServiceBroker это решается как-то проще?
4 сен 15, 12:23    [18107855]     Ответить | Цитировать Сообщить модератору
 Re: ServiceBroker и ловля UPDATE в таблице. Вариант реализации.  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
1. Если есть непреодолимое желание сочинить велосипед, тогда да.
2. Иначе:
3) Использовать ServiceBroker. (Похоже на 1 пункт но ловит только то, что изменилось, а не всю таблицу)
Я выбрал 3 вариант.


да, SqlDependency, Query Notifications.
Пусть возвращает сам факт зменения таблицы, на клиенте разбирайтесь, чо именно изменилось, так даже логичнее по-моему.
4 сен 15, 12:43    [18108012]     Ответить | Цитировать Сообщить модератору
 Re: ServiceBroker и ловля UPDATE в таблице. Вариант реализации.  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Вариант нумбер 4 :

В вашем ПО дописываете 1 маленький сервис который запрашивает версию настроек , и если она измениналось - требует обновить ПО
В БД делаете 1 таблицу с версией актуальной,при изменение параметров настройки - просто апдейтите таблицу

И нафига етот весь лисапед - проще надо быть
4 сен 15, 12:47    [18108056]     Ответить | Цитировать Сообщить модератору
 Re: ServiceBroker и ловля UPDATE в таблице. Вариант реализации.  [new]
хе-хе )
Guest
А зачем вешать опросник очередей, если у очередей есть процедуры активации?
4 сен 15, 12:49    [18108079]     Ответить | Цитировать Сообщить модератору
 Re: ServiceBroker и ловля UPDATE в таблице. Вариант реализации.  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Покой нам только снится (ц)

Из триггера засылается сообщение в очередь с измененными записями. На фига?

А если изменений будет N, зашлются N сообщений с кусками таблицы, и все будут отрабатываться по большому кругу,
и это вместо того чтобы слать сигнал, что в таблице есть изменения и перечитывать ее один раз, когда клиенту хочется.
Кроме того, можно читать не всю таблицу, в таблицу можно добавить timestamp,
и получив сигнал читать читать только изменения, помня текущий камень.
и т.д.

Но можно использовать нотификации.
4 сен 15, 12:57    [18108147]     Ответить | Цитировать Сообщить модератору
 Re: ServiceBroker и ловля UPDATE в таблице. Вариант реализации.  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Winnipuh
Query Notifications

Я так и не понял как прикрутить этот контракт и сервис
хе-хе
А зачем вешать опросник очередей, если у очередей есть процедуры активации?

Потому что процедура будет активироваться в рамках sql сервера, а мне надо чтобы в моей службе вернулся DataSet с измененными настройками.
Winnipuh
Из триггера засылается сообщение в очередь с измененными записями. На фига?

Можно присылать только Id измененной настройки и перечитывать только ее, но тут заложен фундамент на будущее, когда будет присылаться старое и новое значение, чтобы сервис еще мог выполнить какую-нибудь логику по смене значения.

Фактически на триггере на UPDATE должна быть отправка TCP пакета всем подписанным службам (службы не SQL, а windows services самописные). Но т.к. синхронность не требуется, то выбран вариант с ServiceBroker.
4 сен 15, 14:39    [18108844]     Ответить | Цитировать Сообщить модератору
 Re: ServiceBroker и ловля UPDATE в таблице. Вариант реализации.  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Чем не устраивает SqlDependency?
пусть сервис сам после получения читает свой датасет
4 сен 15, 14:46    [18108914]     Ответить | Цитировать Сообщить модератору
 Re: ServiceBroker и ловля UPDATE в таблице. Вариант реализации.  [new]
X-Cite
Member

Откуда: Минск
Сообщений: 1838
Winnipuh
Чем не устраивает SqlDependency?
пусть сервис сам после получения читает свой датасет

Потому что сервис написан не на .Net. Там нет класса такого.
4 сен 15, 14:50    [18108953]     Ответить | Цитировать Сообщить модератору
 Re: ServiceBroker и ловля UPDATE в таблице. Вариант реализации.  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
X-Cite
Winnipuh
Чем не устраивает SqlDependency?
пусть сервис сам после получения читает свой датасет

Потому что сервис написан не на .Net. Там нет класса такого.


я не в курсе на чем он написан.

Вот тут умные люди пишут, может будет полезно:

http://stackoverflow.com/questions/2158839/notify-a-c-application-when-a-change-in-a-sql-server-table-is-made
4 сен 15, 15:19    [18109184]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить