(SOFT) SOFT (2009 год)

SQL-Client_for_remote_XML-WebService - клиент meteonova.ru




На этой страничке я опишу OpenSource SQL-CLR-Assembly и несколько процедур ее обвязки в Microsoft SQL server 2005/2008, которые помогут сформировать на сайте прогноз погоды (курсы валют, дорожные пробки, расписание поездов...). Обратите внимание, что сам по себе сервис прогноза погоды meteonova.ru, клиент которого здесь описан - является платным и для доступа к нему вы должны приобрести у Александра Королькова логин и пароль.

Однако, мое решение по клиенту к погодному сервису полностью бесплатное. Более того, моя сборка и все примененные мною здесь решения вполне универсальны и поучительны и намеренно разрезаны по горизонтали таким образом, чтобы мое решение было легко переделать для любой аналогичной задачки.

Описанное здесь решение ориентировано на квалифицированного администратора сайта (со знанием SQL и логики работы своего сайта) - в противном случае вам следует приобрести готовый погодный информер на сайте http://www.votpusk.ru/ или на сайте http://www.meteonova.ru/ - стоят они недорого, а все заботы об их качественной работе возьмет на себя поставщик информера.






В принципе, клиента публичного Web-сервиса, такого как METEONOVA.RU можно было бы написать тысячей способов. Я выбрал наиболее простой для меня путь - который позволил решить эту проблему за пару часов. Архитектуру моего решения вы видите на рисунке слева.

В основании всего моего решения лежит моя SQL-CLR-сборка (SQL-CRL-Assembly). Для непосвященных в программирование придется пояснить, что это такое. SQL-сборка - это такой код, который считается внутри SQL-сервера и является фактически дополнением обычных стандартизированных SQL-команд INSERT, UPDATE, SELECT, DELETE.

Сборки стало возможно использовать в Microsoft SQL начиная с версии SQL2005/2008. Хотя этот функционал уже много лет существует в Oracle, аналогичный функционал для Microsoft SQL-сервера писался на шестом бейсике и загружался во внутрь SQL-сервера командой SP_OACreate.

У меня на сайте опубликовано довольно много таких компонентов, расширяющих функциональность SQL-сервера, например многие мои решения для http://digitalshop.ru/ были основаны именно на таких COM-обьектах, загружаемых во внутрь SQL-сервера - эти расширения позволяют не только Microsoft SQL-серверу выдавать при работе разнообразные сообщения на терминале SQL-сервера, но и передают удаленным клиентам оповещения об изменениях в своих рекордсетах. Фактически это аналог Micrisoft Notisication Server - только в отличии от микрософтовского, мое решение с уведомлением удаленным клиентам об изменениях в рекорсетах работает.

Когда появился SQL2005, и COM-объекты превратились в настоящие .NET-сборки - я продолжил практику решения различных задач именно cборками внутри SQL-сервера. Одну из своих сборок для микрософтовского проекта ITSM я описал у себя на сайте - сборка, выполняющая JOIN по динамически возникающим условиям. Впоследствии я также опубликовал еще несколько своих NET-Assembly, например GetAspNetProfileProperty, написанную для компании http://www.votpusk.ru. Поэтому и клиента для Meteonova.ru я тоже решил по традиционной для себя схеме - с помощью SQL-сборок.

Надеюсь, после этого пространного обьяснения - стало понятно, что из себя представляют сборки, как они позволяют расширить функциональность SQL-сервера и что именно является самым нижним урованем моего решения погодного клиента.

В целом я не сторонник нарезки решений по горизонтали (универсальными компонентами, которые используют все подсистемы предприятия) и часто нарезаю решения по вертикали (для достижения автономности отдельных подсистем предприятия) - но в данном случае сборка намеренно сделана упрощенной и чрезмерно универсальной.

Итак, вот собственно эти несколько волшебных строк SQL-сборки (которые также доступны для загрузки в бинарном виде):


   1:  Partial Public Class UserDefinedFunctions
   2:      <Microsoft.SqlServer.Server.SqlFunction()> _
   3:      Public Shared Function GetPage(ByVal URL As String, ByVal BasicAU_Name As String, ByVal BasicAU_Pass As String) As <Microsoft.SqlServer.Server.SqlFacet(MaxSize:=-1)> System.Data.SqlTypes.SqlString
   4:          Try
   5:              'запрос по HTTP
   6:              Dim PageRequest As System.Net.HttpWebRequest = CType(System.Net.WebRequest.Create(URL), System.Net.HttpWebRequest)
   7:              Dim NetCredential As New Net.NetworkCredential(BasicAU_Name, BasicAU_Pass)
   8:              PageRequest.Credentials = NetCredential
   9:              'Отправлен запрос
  10:              Dim PageResponse As System.Net.HttpWebResponse = PageRequest.GetResponse
  11:              'Получен ответ
  12:              Dim Reader As New System.IO.StreamReader(PageResponse.GetResponseStream(), System.Text.Encoding.Default, True)
  13:              Dim HTML As String = Reader.ReadToEnd
  14:              Reader.Close()
  15:              'Загружено в память
  16:              Return HTML
  17:          Catch ex As Exception
  18:              Return "Error: " & ex.Message
  19:          End Try
  20:      End Function
  21:  End Class

В коде этой сборки все волшебство заключено в выделенных атрибутах. Атрибуты - это некие классы с фиксированным содержимым, которые управляют поведением компилятора и SQL-сервера. Я их всегда активно использую: создаю их сам свои собственные и своими ран-тайм движками их обрабатываю. Один из примеров такой активной работы с атрибутами я описал на своем сайте - Бизнес-обьекты с плагинной архитектурой (это решение я выполнил для швейцарской компании http://sal.ch/). Но в представленном выше коде сборке все гораздо проще - я установил в коде именно такие атрибуты, которые нужны движку компилятору и Microsoft SQL-серверу.


Для того, чтобы поставить сборку в SQL-сервер - нужно немного напрячся:

Обратите внимание, что эти минимально необходимые действия необходимо выполнить ДО начала собственно загрузки сборки. В этих действия предполагается, что среда NET 2.0 благополучно и правильно была заведомо загружена микрософтом изначально в SQL-сервер. И вы грузите отдельную библиотеку, которая в ран-тайме SQL-сервера сможет разрешить все ссылки. Так, однако, бывает не всегда. Частенько версии, на которых компилилировался код сборки, совершенно не совпадают с тем, что изначально загружено во внутрь SQL-сервера микрософтом. В этом случае вам придется загрузить свою текущую среду компиляции во внутрь SQL-сервера самостоятельно, как это описано у меня на сайте здесь.


Итак, взяв готовый бинарник с моего сайта (или самостоятельно откомпилировав исходный текст выше) и, выставив указанные мною разрешения - можно приступать непосредственно к загрузке сборки

CREATE ASSEMBLY [GetPage] AUTHORIZATION [dbo] FROM 0x4D5A9000030.........0000000 WITH PERMISSION_SET = EXTERNAL_ACCESS


Теперь сборку надо протестировать:



Напоминаю вам, что в отличии от моей сборки, сам по себе доступ к веб-сервису платный, чем и вызвано закрашивание логина/пароля.


Если указанные действия прошли нормально и сборка, работая внутри SQL-сервера, считывает данные с удаленного сайта - можно переходить к следующему этапу - обработке считанного через интернет XML-файла встроенным в SQL-сервер XML-парсером.

Для этого сначала добавим стандартный враппер сборок, которые добавляет студия. Строго говоря, этот уровень не является необходимым, но так удобнее для тех, кто будет просто публиковать сборку диалогами Visual Studio. Кроме того, это позволяет удобно видеть на уровне SQL название параметров вызова сборки:


   1:  CREATE FUNCTION [dbo].[GetPage](@URL [nvarchar](4000), @BasicAU_Name [nvarchar](4000), @BasicAU_Pass [nvarchar](4000))
   2:  RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
   3:  AS 
   4:  EXTERNAL NAME [GetPage].[GetPage.UserDefinedFunctions].[GetPage]

Следующий шаг я тоже намеренно разбил на два уровня. Первый уровень точно разбирает данные, которые выдает WebService meteonova.ru, а второй уровень - фактически согласовывает формат данных, передаваемых meteonova.ru с форматом данных, хранимых в моем конкретном, удобном для сайта формате.

Распарсить полученную страничку и уложить итог в базу - это задачу тоже можно было решить тысячей способов. Но у меня на сайте опубликованы десятки разнообразнейших решений с XML - и эти решения я люблю так же, как и сборки. XML-решения обычно не быстрые, но ведь о какой скорости идет речь, если все это будет в потоке задания, да еще и читает нечто через интернет. Поэтому и этот уровень я с чистой совестью решил привычным для себя способом с помощью встроенного микрософтовского XML-парcера. Однако в процессе этого решения я вдруг столкнулся с неожиданной сложностью - согласование ANSI-кодировки meteonova.ru и юникода Что-то заклинило в голове и я больше часа не мог сообразить как согласовать кодировки. Очень сбивали с толку советы в интернете - все советовали переходить в самом Web-сервисе на кодировку UTF-8, но ведь Meteonova работает в ANSI!

Итак, для того, чтобы распарсить и перекодировать вот этот XML в кодировке windows-1251:




нам потребуется вот такая процедура - в ней вы можете увидеть как я удалил второй байт юникода и вызвал функцию парсера:


   1:  -- =========================================================
   2:  -- получить метео-данные с meteonova.ru по указанному городу
   3:  -- =========================================================
   4:   
   5:  CREATE FUNCTION [dbo].[GetMeteo]
   6:  (    
   7:          @Town int
   8:  )
   9:  RETURNS @MeteoTable TABLE 
  10:  (
  11:      [CityID] [int] NULL,
  12:      [Date] [smalldatetime] NULL,
  13:      [CloudID] [tinyint] NULL,
  14:      [PrecipID] [tinyint] NULL,
  15:      [MaxP] [int] NULL,
  16:      [MinP] [int] NULL,
  17:      [MaxT] [smallint] NULL,
  18:      [MinT] [smallint] NULL,
  19:      [MaxW] [tinyint] NULL,
  20:      [MinW] [tinyint] NULL,
  21:      [RumbWID] [tinyint] NULL,
  22:      [MaxRW] [tinyint] NULL,
  23:      [MinRW] [tinyint] NULL
  24:  )
  25:  AS
  26:  BEGIN
  27:   
  28:  Declare @URL as nvarchar(max)
  29:  --считываем XML через интернет
  30:  select  @URL = 'http://userdata.meteonova.ru/xml3/' + CAST (@Town as nvarchar(max)) +'.xml' 
  31:  Declare @HTML1 as nvarchar(max)
  32:  select  @HTML1=dbo.GetPage(@URL, 'XXXXXXXXXXXXXXX', 'YYYYYYYYYYYYYYY')
  33:  --преобразовываем результат из nvarchar сначала в varchar, потом в XML
  34:  declare @HTML2 as varchar(max)
  35:  select  @HTML2=cast(@HTML1 as varchar(max))
  36:  Declare @XML1 as XML
  37:  select  @XML1 = cast(@HTML2 as xml)
  38:   
  39:   
  40:  --SET DATEFORMAT dmy
  41:   
  42:  Insert @MeteoTable select 
  43:  cast(TOWN_index as integer), 
  44:  cast(dateadd(hh, cast(FORECAST_hour as int), cast(FORECAST_month + '-' + FORECAST_day + '-' + FORECAST_year as datetime)) as smalldatetime),
  45:  cast(PHENOMENA_cloudiness as tinyint),
  46:  cast(PHENOMENA_precipitation as tinyint),
  47:  cast(PRESSURE_max as int),
  48:  cast(PRESSURE_min as int),
  49:  cast(TEMPERATURE_max as smallint),
  50:  cast(TEMPERATURE_min as smallint),
  51:  cast(WIND_max as tinyint),
  52:  cast(WIND_min as tinyint),
  53:  cast(WIND_direction as tinyint),
  54:  cast(RELWET_min as tinyint),
  55:  cast(RELWET_max as tinyint)
  56:  from dbo.ParseMeteoXML(@XML1)
  57:      
  58:      RETURN 
  59:  END

Кроме того, поскольку у меня уже существовала некая структура базы, из которой сайт уже умел выводить вот такую картинку (у вас, конечно, и структура базы и картинка будет иная), то в строках 43-56 процедуры GetMeteo выполняется согласование того, что передает сервис meteonova.ru с тем, что берет из базы для отображения юзерам код самого сайта.

В строке 56 функции GetMeteo вы можете увидеть собственно вызов табличной функция XML-парсера ParseMeteoXML, написанную мною на Xpath/Xquery. Xpath/Xquery - это не только язык навигации по узлам XML-дерева, но и язык запросов со сложными условиями и функциями (из которых в строка 66-86 активно использована функция data()). В целом значение Xpath/Xquery для иерархических XML-данных полностью соответствует языку SQL для реляционных данных.

Запросы к узлам XML на языке Xquery в процедуре ParseMeteoXML привязаны к данным, выдаваемым сервисом meteonova.ru и для вашего решения эта функция будет точно такая же:


   1:  -- ==============================
   2:  -- XML парсер данных meteonova.ru
   3:  -- ==============================
   4:  CREATE FUNCTION [dbo].[ParseMeteoXML] 
   5:  (    
   6:          @XML1 XML 
   7:  )
   8:  RETURNS @MeteoTable TABLE 
   9:  (
  10:  TOWN_index int NULL,   
  11:  TOWN_name varchar(max) NULL,
  12:  TOWN_latitude money NULL,
  13:  TOWN_longitude  money NULL,
  14:  TOWN_type varchar(max) NULL,
  15:  FORECAST_day nvarchar(max) NULL, 
  16:  FORECAST_month nvarchar(max) NULL, 
  17:  FORECAST_year nvarchar(max) NULL, 
  18:  FORECAST_hour nvarchar(max) NULL, 
  19:  FORECAST_tod nvarchar(max) NULL, 
  20:  FORECAST_predict nvarchar(max) NULL, 
  21:  FORECAST_weekday nvarchar(max) NULL,
  22:  PHENOMENA_cloudiness nvarchar(max) NULL,
  23:  PHENOMENA_precipitation nvarchar(max) NULL,
  24:  PHENOMENA_rpower nvarchar(max) NULL,
  25:  PHENOMENA_spower nvarchar(max) NULL,
  26:  PRESSURE_max nvarchar(max) NULL,
  27:  PRESSURE_min nvarchar(max) NULL,
  28:  TEMPERATURE_max nvarchar(max) NULL,
  29:  TEMPERATURE_min nvarchar(max) NULL,
  30:  WIND_max nvarchar(max) NULL,
  31:  WIND_min nvarchar(max) NULL,
  32:  WIND_direction nvarchar(max) NULL,
  33:  RELWET_min nvarchar(max) NULL,
  34:  RELWET_max nvarchar(max) NULL,
  35:  HEAT_min nvarchar(max) NULL,
  36:  HEAT_max nvarchar(max) NULL
  37:  )
  38:  AS
  39:  BEGIN
  40:   
  41:  Declare @TOWN_index as integer, @TOWN_name as varchar(max), @TOWN_latitude as money, @TOWN_longitude as money, @TOWN_type as varchar(max)
  42:   
  43:  select  
  44:  @TOWN_index=@XML1.value('/MMWEATHER[1]/REPORT[1]/TOWN[1]/@index', 'int'),   
  45:  @TOWN_name=@XML1.value('/MMWEATHER[1]/REPORT[1]/TOWN[1]/@name', 'varchar(max)'),
  46:  @TOWN_latitude=@XML1.value('/MMWEATHER[1]/REPORT[1]/TOWN[1]/@latitude', 'money'),
  47:  @TOWN_longitude=@XML1.value('/MMWEATHER[1]/REPORT[1]/TOWN[1]/@longitude', 'money'),
  48:  @TOWN_type=@XML1.value('/MMWEATHER[1]/REPORT[1]/@type', 'varchar(max)')
  49:   
  50:  Insert @MeteoTable
  51:  select 
  52:  --можно посмотреть чего тут распарсило
  53:  --XML1.Root.query('.') AS FORECAST,
  54:  --XML1.Root.query('PHENOMENA') AS PHENOMENA,
  55:  --XML1.Root.query('PRESSURE') AS PRESSURE,
  56:  --XML1.Root.query('TEMPERATURE') AS TEMPERATURE,
  57:  --XML1.Root.query('WIND') AS WIND,
  58:  --XML1.Root.query('RELWET') AS RELWET,
  59:  --XML1.Root.query('HEAT') AS HEAT,
  60:  @TOWN_index as [TOWN_index],   
  61:  @TOWN_name as [TOWN_name],
  62:  @TOWN_latitude as [TOWN_latitude],
  63:  @TOWN_longitude as [TOWN_longitude],
  64:  @TOWN_type as [TOWN_type],
  65:  cast(XML1.Root.query('data(@day)') as nvarchar) AS FORECAST_day, 
  66:  cast(XML1.Root.query('data(@month)') as nvarchar)  AS FORECAST_month, 
  67:  cast(XML1.Root.query('data(@year)') as nvarchar)  AS FORECAST_year, 
  68:  cast(XML1.Root.query('data(@hour)') as nvarchar)  AS FORECAST_hour, 
  69:  cast(XML1.Root.query('data(@tod)') as nvarchar)  AS FORECAST_tod, 
  70:  cast(XML1.Root.query('data(@predict)') as nvarchar)  AS FORECAST_predict, 
  71:  cast(XML1.Root.query('data(@weekday)') as nvarchar)  AS FORECAST_weekday,
  72:  cast(XML1.Root.query('data(PHENOMENA/@cloudiness)') as nvarchar)  AS PHENOMENA_cloudiness,
  73:  cast(XML1.Root.query('data(PHENOMENA/@precipitation)') as nvarchar)  AS PHENOMENA_precipitation,
  74:  cast(XML1.Root.query('data(PHENOMENA/@rpower)') as nvarchar)  AS PHENOMENA_rpower,
  75:  cast(XML1.Root.query('data(PHENOMENA/@spower)') as nvarchar)  AS PHENOMENA_spower,
  76:  cast(XML1.Root.query('data(PRESSURE/@max)') as nvarchar)  AS PRESSURE_max,
  77:  cast(XML1.Root.query('data(PRESSURE/@min)') as nvarchar)  AS PRESSURE_min,
  78:  cast(XML1.Root.query('data(TEMPERATURE/@max)') as nvarchar)  AS TEMPERATURE_max,
  79:  cast(XML1.Root.query('data(TEMPERATURE/@min)') as nvarchar)  AS TEMPERATURE_min,
  80:  cast(XML1.Root.query('data(WIND/@max)') as nvarchar)  AS WIND_max,
  81:  cast(XML1.Root.query('data(WIND/@min)') as nvarchar)  AS WIND_min,
  82:  cast(XML1.Root.query('data(WIND/@direction)') as nvarchar)  AS WIND_direction,
  83:  cast(XML1.Root.query('data(RELWET/@min)') as nvarchar)  AS RELWET_min,
  84:  cast(XML1.Root.query('data(RELWET/@max)') as nvarchar)  AS RELWET_max,
  85:  cast(XML1.Root.query('data(HEAT/@min)') as nvarchar)  AS HEAT_min,
  86:  cast(XML1.Root.query('data(HEAT/@max)') as nvarchar)  AS HEAT_max
  87:  from @XML1.nodes('/MMWEATHER/REPORT/TOWN/FORECAST') as XML1(Root)
  88:   
  89:  --другой вариант решения - OPENXML
  90:  --DECLARE @XmlPointer int
  91:  --EXEC sp_xml_preparedocument @XmlPointer OUTPUT, @XML1
  92:  --SELECT    *
  93:  --FROM       OPENXML (@XmlPointer, '/MMWEATHER/REPORT/TOWN/FORECAST')
  94:  --EXEC sp_xml_removedocument @XmlPointer
  95:      
  96:      RETURN 
  97:  END
  98:   
  99:  GO

Если без преобразования процедура GetMeteo выдавала бы типы данных XML:




то после преобразования - мы получаем необходимые для базы типы данных:




Итак, давайте остановимся и призадумаемся - что же мы получили? А получили мы то, что задавая этой табличной функции определенный город - мы непосредственно в SQL получаем все погодные данные о нем, причем в формате, требуемом кодом нашего собственного сайта. Не правда ли - это немало?

Теперь нам потребуется несколько совсем простеньких уровней - процедура UpdateWeather, которая удалит в базе старый прогноз погоды по конкретно заданному городу - и обновит его на новый:


   1:  CREATE procedure [dbo].[UpdateWeather]
   2:  @CityID int
   3:  as
   4:   
   5:  begin tran
   6:  delete from dbo.Weather where CityID=@CityID
   7:   
   8:  Begin try
   9:   
  10:     insert Weather
  11:     select * from dbo.GetMeteo(@CityID)
  12:     
  13:     IF(@@ERROR = 0)  
  14:          begin
  15:            COMMIT TRANSACTION
  16:            return 0
  17:          end
  18:     ELSE
  19:          begin
  20:            ROLLBACK TRANSACTION
  21:            return -1
  22:          end
  23:  end try
  24:   
  25:  begin catch
  26:     ROLLBACK TRANSACTION
  27:     select error_message() as Error
  28:     return -2
  29:  end catch

Эту процедуру должна вызывать процедура еще более высокого уровня UpdateWeatherAll - которая сможет обновлять локальный кеш сайта по всем городам (список которых вы получить на сайте meteonova.ru):


   1:  CREATE procedure [dbo].[UpdateWeatherAll]
   2:  as
   3:  DECLARE @CityID int
   4:  DECLARE CiTY_CURSOR CURSOR FOR 
   5:  SELECT ID from WCity with (nolock) where show = 1 order by id
   6:   
   7:  OPEN CiTY_CURSOR
   8:   
   9:  FETCH NEXT FROM CiTY_CURSOR INTO @CityID
  10:  WHILE @@FETCH_STATUS = 0
  11:  BEGIN
  12:      EXEC    [dbo].[UpdateWeather] @CityID = @CityID
  13:      FETCH NEXT FROM CiTY_CURSOR INTO @CityID
  14:  END
  15:  CLOSE CiTY_CURSOR
  16:  DEALLOCATE CiTY_CURSOR

И вот эту последнюю процедуру - вы уже можете скормить SQL-заданию Update_Weather, которое будет периодически пересчитывать обновленный прогноз погоды.




В заключение посмотрим еще раз на решение в целом и посмотрим какие свои ключевые профессиональные навыки закрепит администратор сайта, применяя это решение.

Пожалуйста, сообщайте обо всех замеченных багах в баг-трекер этой программы. Вы можете также оставить свои комментарии к моему решению этой задачки.



Comments ( )
<00>  <01>  <02>  <03>  <04>  <05>  <06>  <07>  <08>  <09>  <10>  <11>  <12>  <13>  <14>  <15>  <16>  <17>  <18>  <19>  <20>  <21>  <22>  <23> 
Link to this page: //www.vb-net.com/Meteonova/index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>