В предыдущих моих постах (часть 1, часть 2) я описал то, как получить данные из интернета как HTML, как настроить простой сервис для регулярной загрузки данных, как скорректировать HTML и загрузить его в CLR-объект. В этом посте мы обсудим то, как хранить и обновлять данные в базе. Также я приведу полное описание процесса скрейпинга.
Если у вас крутится сервис который записывает хоть что-то в базу, важно избежать повторов, т.е. дубликации записей. Решение проблемы – создание UPSERT-процедуры. Upsert – это update или insert в зависимости от того, есть уже запись или нет. Если записи нет, ее можно добавить. Если она уже есть, ее можно обновить.
В SQL Server 2008 вместо триггеров и прочих извратов можно воспользоваться инструкцией MERGE, которая создана специально для реализации UPSERT-поведения. Одна проблема – инструкция эта сама по себе выглядит ужасно, поэтому лучше всего ее автогенерировать из сушествующих сущностей.
Мой подход к генерации MERGE DML примерно такой: поскольку ORM не хранит информацию относительно того, какие элементы должны совпадать чтобы это был действительно UPDATEd а не INSERTed, мне проще всего контролировать этот файл вручную. С другой стороны, у меня обязательно присутствует та или иная модель, и хочется использовать именно ее для генерации начальных данных.
Посмотрим как это делается с использованием EF4.0. В EF у нас есть файл с расшинением EDMX, и если копнуть его вглубь по XPath-у
Для сущностей не получится трансформировать EDMX сразу в SQL по ряду причин – во-первых, нам не замэпить схему EDMX т.к. она составная и не парсится, ну и если бы даже мы ее замэпили, пришлось бы редактировать созданный SQL для удаления из него тех сравнений, которые являются “образующими”. Сейчас объясню что к чему.
Итак, возьмем типичный случай – сущность
Первое что мы делаем – выдираем секцию
Далее, создаем мэппинг, который транслирует этот XML в более простой XML (относительно), в котором пожно пометить, какие поля могут меняться, а какие нет.
В результате трансформации мы получаем примерно такой документ:
Поле
Теперь эта хранимая процедура мэпится на EF, Linq2Sql или какой-то другой ORM, и ее можно использовать. Вот пример в EF4:
В примере выше мы также пожем проверить, был ли добавлен новый объект или обновлен старый, и в любом из случаев мы сможем получить
Замечу что вполне возможно вместо “двойного прыжка” с XSLT сделать один Т4 файл которые все сам бы делал, но это настолько нудная задачка, что в принципе легче сделать так как я описал. Конечно, тот факт что придется выдирать
Настало время полностью описать процесс создания типичного скрейпера. Если коротко, то в типичной реализации мы производим следующие действия:
Вот как-то так. Конечно, наверняка есть более простые пути. Опять же, как уже кто-то писал, вместо мэппингов можно использовать Linq напрямую, и в простых сценариях это вполне хорошо работает. Удачи!
Избежание повторов путем автогенерации 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 напрямую, и в простых сценариях это вполне хорошо работает. Удачи!