Data acquisition, часть 3

    В предыдущих моих постах (часть 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 напрямую, и в простых сценариях это вполне хорошо работает. Удачи!

    Средняя зарплата в IT

    120 000 ₽/мес.
    Средняя зарплата по всем IT-специализациям на основании 7 518 анкет, за 1-ое пол. 2021 года Узнать свою зарплату
    Реклама
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее

    Комментарии 7

    • НЛО прилетело и опубликовало эту надпись здесь
      • НЛО прилетело и опубликовало эту надпись здесь
        • НЛО прилетело и опубликовало эту надпись здесь
            0
            Спасибо за ответ. У меня несколько вопросов. В частности, интересно какой процессор вы используете на шаге 3). Также интересна точная механика того как вы разбираете XML на параметры в хранимой процедуре, и как вы делаете собственно batching. Также вопрос — как вы создаете XSLT-шаблон? Вручную или с помощью дизайнера?
            • НЛО прилетело и опубликовало эту надпись здесь
                0
                OK, но это же просто insert, а не upsert. В скрейпинге часто нужно обходить один и тот же сайт сотни раз и при этом записывать изменения — ваше решение с XML интересно, но оно ведь породит море излишних приложений в таком сценарии, не так ли?

                Что касается Mvp.Xml, то я эту библиотеку использовал раньше, но к сожалению она тоже отстала от XSLT2, поэтому мне уже не воспользоваться этой библиотекой для трансформаций того XSLT который мне дают мэпперы.

                И еще, хочу заметить что 99the time скорость вставки в базу данных не играет абсолютно никакой роли, поэтому отсутствие батчинга в upsert мне кажется не критично.
                • НЛО прилетело и опубликовало эту надпись здесь

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое