Pull to refresh

Data acquisition, часть 3

Reading time 6 min
Views 1.4K
В предыдущих моих постах (часть 1, часть 2) я описал то, как получить данные из интернета как HTML, как настроить простой сервис для регулярной загрузки данных, как скорректировать HTML и загрузить его в CLR-объект. В этом посте мы обсудим то, как хранить и обновлять данные в базе. Также я приведу полное описание процесса скрейпинга.


Избежание повторов путем автогенерации UPSERT (MERGE DML)


Если у вас крутится сервис который записывает хоть что-то в базу, важно избежать повторов, т.е. дубликации записей. Решение проблемы – создание UPSERT-процедуры. Upsert – это update или insert в зависимости от того, есть уже запись или нет. Если записи нет, ее можно добавить. Если она уже есть, ее можно обновить.

В SQL Server 2008 вместо триггеров и прочих извратов можно воспользоваться инструкцией MERGE, которая создана специально для реализации UPSERT-поведения. Одна проблема – инструкция эта сама по себе выглядит ужасно, поэтому лучше всего ее автогенерировать из сушествующих сущностей.

Мой подход к генерации MERGE DML примерно такой: поскольку ORM не хранит информацию относительно того, какие элементы должны совпадать чтобы это был действительно UPDATEd а не INSERTed, мне проще всего контролировать этот файл вручную. С другой стороны, у меня обязательно присутствует та или иная модель, и хочется использовать именно ее для генерации начальных данных.

Посмотрим как это делается с использованием EF4.0. В EF у нас есть файл с расшинением EDMX, и если копнуть его вглубь по XPath-у Edmx/Runtime/ConceptualModels/Schema, мы получим описание всех сущностей. Для того чтобы их замэпить на что-то-там, нужно сначала найти схему System.Data.Resources.CSDLSchema_2.xsd – находится она там же где установлена Студия, в папке \xml\Schemas.

Для сущностей не получится трансформировать EDMX сразу в SQL по ряду причин – во-первых, нам не замэпить схему EDMX т.к. она составная и не парсится, ну и если бы даже мы ее замэпили, пришлось бы редактировать созданный SQL для удаления из него тех сравнений, которые являются “образующими”. Сейчас объясню что к чему.

Итак, возьмем типичный случай – сущность Person { Name, Age } которую нужно обновлять (возраст меняется) или добавлять новую (если имя новое).

Первое что мы делаем – выдираем секцию <Schmema> из концептуальной схемы. Получаем примерно следующее:

<Schema><br/>
  <EntityContainer Name="ModelContainer" annotation:LazyLoadingEnabled="true"><br/>
    <EntitySet Name="People" EntityType="Model.Person"/><br/>
  </EntityContainer><br/>
  <EntityType Name="Person"><br/>
    <Key><br/>
      <PropertyRef Name="Id"/><br/>
    </Key><br/>
    <Property Type="Int32" Name="Id" Nullable="false" annotation:StoreGeneratedPattern="Identity"/><br/>
    <Property Type="String" Name="Name" Nullable="false"/><br/>
    <Property Type="Int32" Name="Age" Nullable="false"/><br/>
  </EntityType><br/>
</Schema><br/>

Далее, создаем мэппинг, который транслирует этот XML в более простой XML (относительно), в котором пожно пометить, какие поля могут меняться, а какие нет.




В результате трансформации мы получаем примерно такой документ:

<tables><br/>
  <table name="Person"><br/>
    <field type="String" name="Name"/><br/>
    <field type="Int32" name="Age"/><br/>
  </table><br/>
</tables><br/>

Поле Id сюда не попало, т.к. в Upsert-операции мы не делаем сравнение по Id. (С другой стороны, следует помнить что в сгенерированной процедуре мы возвращаем SCOPE_IDENTITY(), поэтому неполучится дать Id тип вроде uniqueidentifier.) Затем, этот документ трансформируется другим XSLT (которому уже много лет :) и в результате получается именно то, что нужно, а именно:

/* Check that the stored procedure does not exist, and erase if it does. */<br/>
if object_id ('dbo.PersonUpsert''P'is not null<br/>
  drop procedure [dbo].[PersonUpsert];<br/>
go<br/>
/* Upserts an entry into the 'Person' table. */<br/>
create procedure [dbo].[PersonUpsert](<br/>
  @Id int output,<br/>
  @Name nvarchar(max),<br/>
  @Age int)<br/>
as<br/>
 begin<br/>
  merge People as tbl<br/>
   using (select<br/>
    @Name as Name,<br/>
    @Age as Age) as row<br/>
   on<br/>
    tbl.Name = row.Name<br/>
when not matched then<br/>
  insert(Name,Age)<br/>
  values(row.Name,row.Age)<br/>
when matched then<br/>
 update set<br/>
  @Id = tbl.Id,<br/>
  tbl.Name = row.Name,<br/>
  tbl.Age = row.Age<br/>
;<br/>
if @Id is null<br/>
  set @Id = SCOPE_IDENTITY()<br/>
return @Id<br/>
end<br/>

Теперь эта хранимая процедура мэпится на EF, Linq2Sql или какой-то другой ORM, и ее можно использовать. Вот пример в EF4:

var op = new ObjectParameter("Id"typeof(Int32));<br/>
using (var mc = new ModelContainer())<br/>
{<br/>
  // add me
  mc.PersonUpsert(op, "Dmitri", 25);<br/>
  mc.SaveChanges();<br/>
}<br/>

В примере выше мы также пожем проверить, был ли добавлен новый объект или обновлен старый, и в любом из случаев мы сможем получить Id объекта для последующего использования. Конечно, в типичном сценарии использования все эти процессы реализованы через Repository/UnitOfWork со всякими там TransactionScope и иже с ними.

Замечу что вполне возможно вместо “двойного прыжка” с XSLT сделать один Т4 файл которые все сам бы делал, но это настолько нудная задачка, что в принципе легче сделать так как я описал. Конечно, тот факт что придется выдирать <Schema> из EDMX это тоже неидеально, но пока сойдет. Кстати, хочу также заметить что по непонятным причинам (а может я плохо искал) не существует мэппера который мог бы мэпить XML на TXT и при этом производить XSLT-трансформацию. Я глянул на FlexText, но эта программа не позволила мне сделать вставки в строках, а также MapForce порождал с помощью нее только C#, а делать XSLT отказался.

Полное описание процесса


Настало время полностью описать процесс создания типичного скрейпера. Если коротко, то в типичной реализации мы производим следующие действия:

  • Находим те страницы которые нужно обработать и смотрим на них с помощью FireBug
  • Скачиваем страницы – если нужна сложная аутентификация или ввод со стороны польователя, используем WatiN, иначе используем WebRequest и т.п.
  • Находим на страницах те элементы что нам нужны и
    • Трансформируем элементы чтобы сделать их XML-совместимыми
    • Делаем сущность (entity) для хранения данных из этого куска XML
    • Делаем класс-коллекцию Collection<T> для этой сущности
    • Генерируем для класса-коллекции соответствующий XSD с помощью xsd -t:MyCollection MyAssembly.exe
    • Автогенерируем XSD с исходного HTML
    • Создаем мэппинг с одного XSD на другой

  • В коде, делаем мэппинг с обработанного HTML на XML
  • Считываем сущность или коллекцию сущностей из полученного XML
  • Создаем Upsert-процедуру (пример):
    • Выдираем элемент <Schema> из WSDL
    • Трансформируем элемент в упрощенную форму
    • Трансформируем результирующий XML в SQL для создания хранимой процедуры
    • Создаем хранимую процедуру в базе
    • Импортируем хранимую процедуру в наш ORM

  • После создания сущности, записываем ее в базу (обновляем или создаем новую)

Вот как-то так. Конечно, наверняка есть более простые пути. Опять же, как уже кто-то писал, вместо мэппингов можно использовать Linq напрямую, и в простых сценариях это вполне хорошо работает. Удачи!
Tags:
Hubs:
+8
Comments 7
Comments Comments 7

Articles