Русский
Русский
English
Статистика
Реклама

Mssql

Основы правил проектирования базы данных

16.08.2020 12:14:15 | Автор: admin

Введение


Как это часто бывает, архитектору БД нужно разработать базу данных под конкретное решение.
Однажды в пятницу вечером, возвращаясь на электричке домой с работы, подумал, как бы я создал сервис по найму сотрудников в разные компании. Ведь ни один из существующих сервисов не позволяет быстро понять насколько подходит тебе кандидат, нельзя создать сложные фильтры именно по конкретным навыкам, проектам и позициям или исключающие определенные навыки, позиции и проекты. Максимум, что предлагают сервисы это фильтры по компаниям и частично по навыкам.

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

В данном примере разберем создание базы данных в MS SQL Server для сервиса поиска соискателей на работу.

Также данный материал можно перенести и на другую СУБД такую как MySQL или PostgreSQL.

Основы правил проектирования


Для проектирования схемы базы данных, нужно вспомнить 7 формальных правил и саму концепцию нормализации и денормализации. Они и лежат в основе всех правил проектирования.

Опишем более детально 7 формальных правил:
  1. отношение один к одному:
    1.1) с обязательной связью:
    примером может выступать гражданин и его паспорт (у любого гражданина должен быть паспорт, и паспорт один для каждого гражданина)

    Реализовать данную связь можно двумя способами:
    1.1.1) в одной сущности (таблице):


    Рис.1. Сущность Citizen

    Здесь таблица Citizen представляет из себя сущность гражданина, а атрибут (поле) PassportData содержит все паспортные данные гражданина и не может быть пустым (NOT NULL).

    1.1.2) в двух разных сущностях (таблицах):


    Рис.2. Отношение сущностей Citizen и PassportData

    Здесь таблица Citizen представляет из себя сущность гражданина, а таблица PassportData представляет из себя сущность паспортных данных гражданина (т е самого паспорта). Сущность гражданина содержит атрибут (поле) PassportID, который ссылается на первичный ключ таблицы PassportData. В свою очередь сущность паспортных данных содержит атрибут (поле) CitizenID, которое ссылается на первичный ключ CitizenID таблицы Citizen. Поле PassportID таблицы Citizen не может быть пустым (NOT NULL). Также здесь важно поддерживать целостность и поля CitizenID таблицы PassportData, чтобы обеспечить связь один к одному. Т е поле PassportID таблицы Citizen и поле CitizenID таблицы PassportData должны ссылаться на одни и те же записи как если бы это была одна сущность (таблица), которая была показана в пункте 1.1.1.

    1.2) с необязательной связью:
    примером может выступать человек, у которого может быть паспорт, а может и не быть конкретной страны. Т е в первом случае он будет являться гражданином рассматриваемой страны, а во втором-нет.

    Реализовать данную связь можно двумя способами:
    1.2.1) в одной сущности (таблице):

    Рис.3. Сущность Person

    Здесь таблица Person представляет из себя сущность человека, а атрибут (поле) PassportData содержит все паспортные данные человека и может быть пустым (NULL).

    1.2.2) в двух сущностях (таблицах):


    Рис.4. Отношение сущностей Person и PassportData

    Здесь таблица Person представляет из себя сущность человека, а таблица PassportData представляет из себя сущность паспортных данных человека (т е самого паспорта). Сущность человека содержит атрибут (поле) PassportID, который ссылается на первичный ключ таблицы PassportData. В свою очередь сущность паспортных данных содержит атрибут (поле) PersonID, которое ссылается на первичный ключ PersonID таблицы Person. Поле PassportID таблицы Person может быть пустым (NULL). Также здесь важно поддерживать целостность и поля PersonID таблицы PassportData, чтобы обеспечить связь один к одному. Т е поле PassportID таблицы Person и поле PersonID таблицы PassportData должны ссылаться на одни и те же записи как если бы это была одна сущность (таблица), которая была показана в пункте 1.2.1, или же данные поля должны быть неопределенными, т е содержать NULL.
  2. отношение один ко многим:
    2.1) с обязательной связью:
    примером могут выступать родитель и его дети. У каждого родителя есть как минимум один ребенок.

    Реализовать данную связь можно двумя способами:
    2.1.1) в одной сущности (таблице):


    Рис.5. Сущность Parent

    Здесь таблица Parent представляет сущность родителя, а атрибут (поле) ChildList содержит в себе информацию о детях, т е самих детей. Данное поле не может быть пустым (NOT NULL). Обычно типом поля ChildList выступает неполно структурированные данные (NoSQL) такие как XML, JSON и т д.

    2.1.2) в двух сущностях (таблицах):


    Рис.6. Отношение сущностей Parent и Child

    Здесь таблица Parent представляет сущность родителя, а таблица Child представляет сущность ребенка. У таблицы Child есть поле ParentID, которое ссылается на первичный ключ ParentID таблицы Parent. Поле ParentID таблицы Child не может быть пустым (NOT NULL).

    2.2) с необязательной связью:
    примером может выступать человек, у которого могут как быть дети, так и полностью отсутствовать.

    Реализовать данную связь можно двумя способами:
    2.2.1) в одной сущности (таблице):


    Рис.7. Сущность Person

    Здесь таблица Parent представляет сущность родителя, а атрибут (поле) ChildList содержит в себе информацию о детях, т е самих детей. Данное поле может быть пустым (NULL). Обычно типом поля ChildList выступает неполно структурированные данные (NoSQL) такие как XML, JSON и т д.

    2.2.2) в двух сущностях (таблицах):


    Рис.8. Отношение сущностей Person и Child

    Здесь таблица Parent представляет сущность родителя, а таблица Child представляет сущность ребенка. У таблицы Child есть поле ParentID, которое ссылается на первичный ключ ParentID таблицы Parent. Поле ParentID таблицы Child может быть пустым (NULL).

    2.2.3) в одной сущности со ссылкой на саму себя при условии, что у сущностей (таблиц) родителя и ребенка будут одинаковые наборы атрибутов (полей) без учета ссылки на родителя:


    Рис.9. Сущность Person со связью на саму себя

    Здесь сущность (таблица) Person содержит атрибут (поле) ParentID, которое ссылается на первичный ключ PersonID этой же таблицы Person и может содержать пустое значение (NULL).

    Также данная реализация является примером реализации и отношения многие к одному с необязательной связью.
  3. отношение многие к одному:
    Эту связь можно зеркально рассмотреть к рассмотренной выше связи один ко многим, т е отношение сущности дети к сущности родители, где обязательная связь будет если предполагается, что у ребенка есть хотя бы один родитель, а если в рассмотрение взять всех детей, в том числе и которые находятся в детских домах, то такое отношение будет с необязательной связью.
  4. отношение многие ко многим:
    здесь примером может выступить недвижимость, которая может находиться в собственности как у одного человека, так и у нескольких. С другой стороны, у человека может быть во владении несколько домов или долей собственности нескольких домов.

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


    Рис.10. Отношение сущностей Person и RealEstate

    Здесь таблицы Person и RealEstate представляют сущности человека и недвижимость соответственно. Связываются данные сущности (таблицы) через сущность (таблицы) PersonRealEstate через атрибуты (поля) PersonID и RealEstateID, которые ссылаются на первичные ключи PersonID таблицы Person и RealEstateID таблицы RealEstate соответственно. Обратите внимание, что для таблицы PersonRealEstate пара (PersonID; RealEstateID) является всегда уникальной и потому может выступать первичный ключем для самой связующей сущности PersonRealEstate.

    Также данное отношение можно реализовать и через более 3-х сущностей, добавляя нужные атрибуты, которые ссылаются на первичные ключи необходимых соответствущих сущностей. Такая реализация похожа на примеры, описанные выше в пунктах 1.1.2 и 1.2.2.


Также отношения один ко многим и многие к одному можно реализовать и более чем через 2 сущности, добавляя нужные атрибуты, которые ссылаются на первичные ключи необходимых соответствущих сущностей. Такая реализация похожа на примеры, описанные выше в пунктах 1.1.2 и 1.2.2.

А где же 7 формальных правил?
Вот они:
  1. п.1 (п.1.1 и п.1.2)-это первое и второе формальных правила
  2. п.2 (п.2.1 и п.2.2) это третье и четвертое формальных правила
  3. п.3 (аналогично п.2) это пятое и шестое формальных правила
  4. п.4 это седьмое формальное правило


Просто в тексте выше эти 7 формальных правил объединены в 4 блока по функционалу.

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

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

Вот и вся суть правил проектирования баз данных.

А вы уверены что поняли отношения в 7 формальных правил? Именно поняли, а не узнали. Ведь знать и понять-это совершенно два разных понятия.

Объясню более детально. Спросите себя-можете ли вы за пару часов набросать пусть и укрупненный по сущностям, но модель базы данных для любой предметной области и для любой информационной системы? (тонкости и детали можно достроить, поспрашивав аналитиков и представителей заказчиков). Если вопрос вас удивил и вы думаете что это невозможно, то значит вы знаете 7 формальных правил, но не понимаете их.

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

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

Позволю себе рассказать небольшой отрывок из своей истории, где от знания семи формальных правил я пришел к пониманию этих отношений.

В свое время меня смущало то, что в ВУЗе я знал эти 7 отношений, но очень долго строил модели базы данных для разных предметных областей в производственных практиках (когда ВУЗ отправляет студентов в компании для приобретения опыта по профилю). И я задумавшись понял, что не понимаю этих отношений.

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

Сон был про семью, в которой есть и отец, и мать, а также дети. Со временем отец погибает в автомобильной катастрофе, а мать начинает пить. Потому в итоге детей забирают в детский дом от матери и долгое время дети остаются без родителей. Затем появляются попечители для некоторых из этих детей. Этих попечителей тоже несколько.

Давайте посмотрим внимательно. Вы проследили какие отношения были между субъектами и как менялись эти отношения?

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

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

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

Надеюсь, теперь вы значительно приблизились к пониманию этих 7 формальных правил.

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

После понимания 7 формальных правил, вы без труда сможете спроектировать любой сложности модель базы данных для любой информационной системы.

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

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

Особенно это важно для хранилищ данных, где изменения влекут пересохранение больших объемов данных от нескольких гигабайт до многих терабайт данных.

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

Проектирование схемы базы данных для поиска соискателей на работу


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

Для начала определим что важно для сотрудников из компании, которые осуществляют поиск соискателя на работу:
  1. для HR:
    1.1) компании, где работал соискатель
    1.2) позиции, которые занимал соискатель в данных компаниях
    1.3) навыки и умения, которыми пользовался соискатель в работе
    а также продолжительность работы соискателя в каждой компании и на каждой позиции, длительность использования каждого навыка и умения
  2. для технического специалиста:
    2.1) позиции, которые занимал соискатель в данных компаниях
    2.2) навыки и умения, которыми пользовался соискатель в работе
    2.3) проекты, в которых участвовал соискатель
    а также продолжительность работы соискателя на каждой позиции и в каждом проекте, длительность использования каждого навыка и умения


Для начала выявим нужные сущности:
  1. сотрудники (Employee)
  2. компании (Company)
  3. позиции (должности) (Position)
  4. проекты (Project)
  5. навыки (Skill)


Компании и сотрудники имеют отношение многие ко многим, т к сотрудник мог работать в нескольких компаниях, а в компании работают несколько сотрудников.

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

С другой стороны, сотрудник мог работать на разных позициях как в рамках одной компании, так и в рамках разных компаний. Таким образом, получаем, что отношение между позициями и компаниями тоже является многие ко многим.

Аналогично и по проектам, т е проекты относятся ко всем выше рассмотренным сущностям как многие ко многим.

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

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


Рис.11. Схема базы данных для поиска соискателей на работу

Здесь таблица JobHistory выступает в виде сущности истории работы каждого соискателя, т е самого резюме, которое и реализует отношения многие ко многим между сущностями сотрудник, компания, позиция и проект.

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

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

Здесь можно было упростить схему и добавления данных, если навыки вложить в сущность проекты через неполно структурированные данные (NoSQL) в виде XML, JSON или просто перечисляя названия навыков через точку с запятой. Но это бы усложнило выборку с группировкой по навыкам и фильтрацию по отдельным навыкам.

Подобная полученная модель лежит в основе базы данных проекта Geecko.

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

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

Немного лирики


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

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

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

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

Послесловие


Диаграммы для примеров были реализованы с помощью инструмента Database Diagram Tool for SQL Server. Однако, подобный функционал есть и в DBeaver.

Источники


Подробнее..

Делаем откаты БД в msi. История про создание резервных копий и удаление БД в WixSharp

31.03.2021 14:23:38 | Автор: admin

При работе с базами данных (БД) в установщике, про который мы уже писали в прошлой статье (Пишем установщик на WixSharp. Плюшки, проблемы, возможности), в первую очередь, были реализованы проверка доступности СУБД по логину/паролю, добавление и обновление собственно БД (в нашем приложении их несколько) накатыванием миграций, a также добавление пользователей. Все это реализовано для двух СУБД Microsoft SqlServer и PostgreSql.
На первый взгляд этого достаточно, но иногда есть необходимость удалять БД и пользователей, а это влечет за собой создание резервных копий. Сразу выявили две необходимые задачи:

1.Удаление БД и пользователей при откате приложения в случае ошибки при первичной установке. При установке приложения, если возникает ошибка, происходил откат всех настроек, кроме БД. Добавленные БД и пользователи оставались. И, если при боевой эксплуатации, после серии тестирования эта ситуация непредвиденной ошибки маловероятна, то в процессе разработки и доработки установщика, ошибки возникают часто. Их, однозначно, нужно удалять.

2.Создание резервных копий (бэкапов) и удаление БД и пользователей при полном удалении приложения установщиком. Правильно ли оставлять БД после полного удаления приложения? Мы решили, что нет. Но бэкапы, конечно, сохранять нужно.

Из второго пункта возникла новая задача:

3.Создание бэкапов БД при обновлении приложения. Если мы сохраняем бэкапы при удалении, неплохо создавать их и перед обновлением, накатыванием миграций и прочими изменениями. Подстраховка еще никому не мешала. :)

Удаление БД и пользователей при откате приложения в случае ошибки при первичной установке

Если что-то пошло не так и при установке возникли ошибки, мы сразу же позаботились об удалении добавленных директорий и настроек, а также об очистке реестра. Но БД и пользователей также нужно удалять. В WixSharp для этого предусмотрен механизм роллбэка для CustomActions. Для существующего пользовательского действия нужно добавить еще один параметр - название пользовательского действия откатывающего изменения. Необходимо учесть, что данный механизм доступен только для deferred action (отложенных действий).

new ManagedAction(AddDatabaseAction, Return.check, When.After, Step.PreviousAction, Condition.NOT_Installed, DeleteAddedDatabasesAction)             {    UsesProperties = $@"{DATABASE_PROPERTIES}={database_properties}",    Execute = Execute.deferred,   ProgressText = $"Выполняется создание БД {databaseName}"              };

Тут сложностей не возникло и для каждого из СУБД было добавлено выполнение скрипта с удалением БД и пользователей, учитывая в скрипте, что в этот момент база может использоваться.

Создание бэкапов и удаление БД при полном удалении приложения установщиком

В данном случае, необходимо сохранять бэкапы БД, а затем, проводить удаление.

Пользовательское действие для создания бэкапа желательно выполнять до того, как начнут вноситься изменения установщиком, для этого предусмотрен тип immediate. В отличие от deferred, он выполняется сразу. Чтобы данное действие выполнялось только при удалении приложения, укажем условие Condition.BeingUninstalled:

new ManagedAction(BackupDatabaseAction, Return.check, When.After, Step.PreviousAction, Condition.BeingUninstalled){   Execute = Execute.immediate,   UsesProperties = DeleteAddedDatabases,   ProgressText = $"Выполняется скрипт по созданию резервных копий БД" }

Бэкапы решено было сохранять по пути, доступному текущему пользователю. Так как у нас несколько БД, группировку проводили по версии приложения. Название БД формировалось классически, с указанием имени и даты-времени создания.
\Users\{CurrentUser}\AppData\Local\{ApplicationName}\Backups\{VersionNumber}

Создаем этот путь:

Version installedVersion = session.LookupInstalledVersion();  string localUserPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData); string backupPath = Path.Combine(localUserPath, "ApplicationName", "Backups", installedVersion.ToString());  Directory.CreateDirectory(backupPath);

И если для Microsoft SqlServer создание бэкапов заключалось в выполнении банального sql-скрипта:

$" USE master" +                    $" BACKUP DATABASE [{databaseName}]" +                    $" TO DISK = N'{path}'" +                    $" WITH NOFORMAT, NOINIT, " +                    $" NAME = N'{backupName}', SKIP, NOREWIND, NOUNLOAD,  STATS = 10 ";

То для PostgreSql одним скриптом не обойтись. Бэкап можно создать запуском команды через командную строку. Понадобится выполнение следующих действий:

  • Запускать pg_dump.exe из соответствующей папки PostgreSql
    C:\Program Files\PostgreSQL\{Version}\bin
    Мы не знаем какая версия установлена у заказчика (обычно в документации мы указываем версию, не ниже которой требуется), какой путь был выбран. Поэтому основной путь с указанием версии получим из реестра:

const string KEY_MASK = @"SOFTWARE\PostgreSQL\Installations\";var currentVersion = Registry.LocalMachine.OpenSubKey(KEY_MASK)?.GetSubKeyNames()[0];if (currentVersion == null){  return ActionResult.Failure;}var keyName = $@"HKEY_LOCAL_MACHINE\{KEY_MASK}{currentVersion}";var postgresPath = (string)Registry.GetValue(keyName, "Base Directory", string.Empty);
  • Проверять, добавлены ли переменные среды для PostgreSql. И в случае необходимости добавить.
    C:\Program Files\PostgreSQL\12\bin
    C:\Program Files\PostgreSQL\12\lib

    Если они отсутствуют, запуск pg_dump будет невозможен.

string binEnv = $@"{postgresPath}\bin";string path = "PATH";var scope = EnvironmentVariableTarget.User;var currentEnvironmentVariable = Environment.GetEnvironmentVariable(name, scope);if (!currentEnvironmentVariable.ToUpper().Contains(binEnv.ToUpper())){  var newEnvironmentVariable = $@"{currentEnvironmentVariable};{binEnv}";  Environment.SetEnvironmentVariable(name, newEnvironmentVariable, scope);}
  • Сформировать аргументы создания бэкапа с помощью командной строки. Здесь необходимо указать параметры подключения, имя БД и путь сохранения бэкапа. Так как ранее нам не приходилось создавать бэкапы для PostgreSql, несложный поиск в интернете показывал примерно такое решение:

    pg_dump -h {host} -p {port} -U {username} {database_name} > {backuppath}

    Если в конфиг файле pg_hba не указано для local connections безусловное подключение trust, то будет требоваться введение пароля. В данном случае, требуется добавление файла .pgpass для текущего пользователя. Тогда, можно добавить в команду атрибут -w и пароль будет считываться оттуда. Так как вновь возникает ситуация, когда мы не знаем, как это организовано у заказчика, была найдена универсальная запись, с помощью которой можно передать все параметры в рамках одной команды:

    pg_dump --dbname=postgresql://{username}:{password}@{address}:{port}
    /{databaseName}-f {backupPath}

После того, как бэкапы созданы, можно удалить БД и пользователей. Здесь будет использоваться то же пользовательское действие DeleteAddedDatabasesAction, что и для отката из пункта 1. Оно будет отложенным и будет запускаться при условии деинсталляции Condition.BeingUninstalled:

new ManagedAction(DeleteAddedDatabasesAction, Return.check, When.After, Step.PreviousAction, Condition.BeingUninstalled){  Execute = Execute.deferred,  UsesProperties = $@"{DATABASE_PROPERTIES}={database_properties}",  ProgressText = $"Выполняется удаление БД {databaseName} и роли {role}" };

Операции с БД при обновлении приложения

При обновлении приложения последовательно происходит удаление, инициализация данных из реестра и установка новой версии. До внесения наших изменений все было хорошо, базы и пользователи оставались жить. Теперь нужно отличать чистое удаление от удаления при обновлении. Решили мы это добавлением новой переменной в реестр, которая инициализируется при обновлении (определяем сравнением версий), а также фиксацией пользовательского действия удаления.

Вывод

Для PostgreSql и Microsoft SqlServer в нашем установщике удалось наладить:

  • механизм удаления БД и пользователей;

  • создание резервных копий в случае полного удаления;

  • создание резервных копий в случае обновления приложения;

  • реализацию отката добавленных БД в случае неудачной первичной установки, либо ее отмене.

    Продолжаем пилить msi ;)

Подробнее..

Сравнение схем двух баз данных

14.09.2020 10:18:41 | Автор: admin

При разработке приложений иногда возникает потребность в сравнении двух баз данных (например prod и dev).

Существует ряд подходов для решения этого вопроса - от создания dump-файла со структурой db и последующим использованием diff, до использования специализированных платных решений типа dbForge или RedGate (большой список здесь).

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

Compalex - это набор скриптов для быстрого сравнения двух схем баз даных. Для облегчения использования, все это упаковано в Docker-контейнер, который содержит как непосредственно скрипты, так и настроенное окружение для подключения к DB.

Для запуска контейнера необходимо указать:

docker run -it -e DATABASE_DRIVER='mysql' \-e DATABASE_ENCODING='utf8' \-e SAMPLE_DATA_LENGTH='100' \-e DATABASE_HOST='host.docker.internal' \-e DATABASE_PORT='3306' \-e DATABASE_NAME='compalex_dev' \-e DATABASE_USER='root' \-e DATABASE_PASSWORD='password' \-e DATABASE_DESCRIPTION='Developer database' \-e DATABASE_HOST_SECONDARY='host.docker.internal' \-e DATABASE_PORT_SECONDARY='3306' \-e DATABASE_NAME_SECONDARY='compalex_prod' \-e DATABASE_USER_SECONDARY='root' \-e DATABASE_PASSWORD_SECONDARY='password' \-e DATABASE_DESCRIPTION_SECONDARY='Production database' \-p 8000:8000 dlevsha/compalex

Где:

DATABASE_DRIVER - используемый драйвер для подключения

  • mysql - для MySQL

  • pgsql - для PostgreSQL

  • dblib - для Microsoft SQL Server

  • oci - для Oracle

DATABASE_HOST and DATABASE_HOST_SECONDARY - имя хоста или IP для подключения к первому и второму серверу.

Если базы развернуты локально на localhost:

Для пользователей MacOS и Windows: используйте host.docker.internal вместо localhost , поскольку скрипт, запущенный внутри контейнера должен подключиться на хостовую машину.

Для пользователей Linux: используйте опцию --network host (в этом случае контейнер "делит" сеть с хостовой машиной) и в качестве имя хоста для подключения к DB используйте localhost, либо с помощью команды ip a в секции docker0 посмотрите ip адрес хостовой машины.

3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500    link/ether 02:42:e8:a9:95:58 brd ff:ff:ff:ff:ff:ff    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0       valid_lft forever preferred_lft forever
Вариант с пробросом коннектов к DB через SSH

Если вы имеете только SSH доступ на машины DB и вам не хочется открывать порты наружу - вы можете воспользоваться пробросом портов через SSH на localhost на разные порты, чтобы потом использовать эти подключения локально.

Пример:

ssh -L 1522:localhost:1521 [user name]@[remote host 1]ssh -L 1523:localhost:1521 [user name]@[remote host 2]

Пробросит 1521 порт с удаленных хостов remote host 1 и remote host 2 на localhost порты 1522 и 1523 соответственно.

Если у вас есть удаленный коннект не непосредственно на машину с DB, а на некоторый application сервер, который уже имеет подключение к DB, то подключения будут выглядеть так:

ssh -L 1522:[remote database server 1]:1521 [user name]@[remote application host 1]ssh -L 1523:[remote database server 2]:1521 [user name]@[remote application host 2]

Во всех описанных выше случаях соединение будет проброшено на локальную машину и в переменных DATABASE_HOST and DATABASE_HOST_SECONDARY нужно будет использовать ip, по схеме, описанной выше.

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

DATABASE_PORT and DATABASE_PORT_SECONDARY - порт для подключения к хосту баз данных.

Порты по умолчанию:

  • 3306 - Mysql

  • 5432 - PostgreSQL

  • 1433 - MSSQL

  • 1521 - Oracle

DATABASE_NAME and DATABASE_NAME_SECONDARY - название сравниваемых баз данных

DATABASE_USER / DATABASE_PASSWORD and DATABASE_USER_SECONDARY / DATABASE_PASSWORD_SECONDARY - логин и пароль для доступа к базам данных

DATABASE_DESCRIPTION and DATABASE_DESCRIPTION_SECONDARY - описание баз данных (опционально, в справочных целях).

После того как вы запустите контейнер, откройте браузер и перейдите http://localhost:8000. Должна отобразиться схема-сравнение, состоящая из двух колонок, в соответствии с указанными параметрами баз данных.

Иногда нет возможности организовать прямое локальное подключение к базам данных и изменения необходимо посмотреть прямо на сервере из консоли.

Для этих целей я рекомендую использовать консольный браузер eLinks, который поддерживает HTML-форматирование и цвета в разметке.

Необходимо установить скрипт на сервере, запустить контейнер (как это описано выше) и выполнить:

$ elinks http://localhost:8000

В своем терминале вы увидите примерно следующее:

Более подробная информация на сайте (на английском) http://compalex.net/.

Здесь можно попробовать как все это работает http://demo.compalex.net/.

Проект на GitHub https://github.com/dlevsha/compalex

Посмотреть на DockerHub https://hub.docker.com/r/dlevsha/compalex

Подробнее..

Как быстрее всего передавать данные с PostgreSQL на MS SQL

21.04.2021 00:08:36 | Автор: admin

Однажды мне потребовалось забирать регулярно относительно большие объемы данных в MS SQL из PostgreSQL. Неожиданно выяснилось, что самый очевидный способ, через Linked Server на родные ODBC к PostgreSQL, очень медленный.


История вопроса

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

SET STATISTICS TIME ONDECLARE  @sql_str nvarchar(max)DROP TABLE IF EXISTS #tCREATE TABLE #t (  N int,  T datetime)SELECT @sql_str='  SELECT N, T  FROM generate_series(1,1000,1) N  CROSS JOIN generate_series($$2020-01-01$$::timestamp,    $$2020-12-31$$::timestamp, $$1 day$$::interval) T'INSERT #t (N, T)EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Такой простейший пример выборки всего 366 тысяч записей оказался жутко медленным:

SQL Server Execution Times:   CPU time = 8187 ms,  elapsed time = 14793 ms.

Решение

В первую очередь, захотелось исключить самый подозрительный элемент - ODBC. К тому времени MS уже предоставлял утилиту bcp для Linux. Поэтому bcp был установлен на сервер, где работал PostgreSQL и проведен следующий тест:

SET STATISTICS TIME ONDECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'DROP TABLE IF EXISTS ##tCREATE TABLE ##t (  N int,  T datetime)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##t'' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'','\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Результат сразу порадовал, причем сильно:

SQL Server Execution Times:   CPU time = 0 ms,  elapsed time = 881 ms.

Реализация

Не сложно заметить, что такой подход требует явного указания логина и пароля. Причем, bcp для Linux до сих пор не умеет авторизоваться через Kerberos. Поэтому использовать его можно только указывая кредентиалы в командной строке.

Вторая проблема в том, что в обычную временную таблицу bcp записать не может. Он ее просто не увидит. Значит нужно использовать постоянную таблицу или глобальную временную.

Давать права пользователю, кредентиалы которого открытым текстом видны в SQL запросе, на таблицы своей БД совершенно не хочется. Тем более на запись. Поэтому остается только вариант с глобальной временной таблицей.

В связи с тем, что процессы на сервере могут запускаться асинхронно и одновременно, использовать фиксированное имя глобальной временной таблицы опасно. Но тут нас опять спасает динамический SQL.

Итоговое решение следующее:

DECLARE  @sql_str        nvarchar(max),  @proxy_account  sysname='proxy_account',  @proxy_password sysname='111111'SELECT @sql_str='  DROP TABLE IF EXISTS ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+'  CREATE TABLE ##proxy_table_'+CONVERT(nvarchar(max),@@SPID)+' (    N int,    T datetime  )'EXEC (@sql_str)SELECT @sql_str='  COPY (    SELECT N, T    FROM generate_series(1,1000,1) N    CROSS JOIN generate_series($$2020-01-01$$::timestamp,      $$2020-12-31$$::timestamp, $$1 day$$::interval) T )  TO PROGRAM $pgm$ tmp_file=$'+'(mktemp /tmp/pgsql_bcp_to_mssql.XXXXXXXXX); '    +'cat > $tmp_file; /opt/mssql-tools/bin/bcp ''##proxy_table_'''    +CONVERT(nvarchar(max),@@SPID)+' '    +'in $tmp_file -S '+REPLACE(@@SERVERNAME,'\','\\')    +' -U '+@proxy_account+' -P '''    +@proxy_password+''' -c -b 10000000 -a 65535; '    +'rm $tmp_file $pgm$ NULL $nil$$nil$;'EXEC (@sql_str) AT LINKED_SERVER_TO_POSTGRES

Пояснения

В PostgreSQL команда COPY может писать в файл или на стандартный ввод вызываемой ей программы. В данном случае вместо программы использован скрипт на sh. Вывод COPY, поступающий на стандартный ввод, записывается во временный файл с уникальным именем, форимруемым mktemp. К сожалению, bcp не умеет читать данные со стандартного ввода, поэтому приходится ему создавать файл.

Для совместимости формата, формируемого командой COPY и формата, ожидаемого bcp, обязательно следует указывать в COPY параметр NULL $nil$$nil$

Остальные параметры bcp:

  • -c - символьный формат, так как бинарный формат PostgreSQL не совместим с бинарным форматом MS SQL и мы вынуждены использовать только символьный;

  • -b - количество записей, вставляемых одной транзакцией. В моей конфигурации десять миллионов оказалось оптимальным значением. В иной конфигурации это число, скорее, может потребоваться уменьшить, чем увеличить;

  • -a - размер пакета. В нашем случае лучше указывать сразу максимальный. Если сервер не поддерживает указанную длину пакета, то просто будет использована максимальная длина пакета, поддерживаемая сервером.

Если кто-то знает более быстрый способ получения данных на MS SQL из PostgreSQL - буду очень рад увидеть описание этого способа в комментариях.

Подробнее..
Категории: Postgresql , Sql , Postgres , Microsoft sql server , Mssql , Bcp

Ведение разработки БД. Шаблоны созданияизменения объектов MSSQL

05.11.2020 04:10:16 | Автор: admin

При постоянной работе с MSSQL необходимо создавать различные объекты БД: таблицы, представления, триггеры и т.д.
В статье приведу шаблоны SQL запросов, которые помогут, и, возможно, стандартизируют подход создания кода на языке T-SQL.
Кроме этого, опишу о том, как я веду репозиторий БД в системе контроля версий.


Основные требования реализации SQL скриптов


1) скрипт должен выполняться многократно не выдавая ошибок
2) в скрипте должны быть предусмотрены операторы PRINT для удобства отладки
3) выполнение скриптов должно логироваться в один общий файл
4) скрипты должны выполняться через командную строку используя стандартный набор утилит (sqlcmd, bcp)
5) создание и изменение каждого объекта БД хранится отдельным SQL файлом
6) SQL скрипты (файлы) запускаются BAT файлом при каждом обновлении БД


Далее приводятся примеры SQL файлов и BAT файл для запуска этих SQL запросов.


Шаблоны T-SQL


Создание/изменение таблицы
--USE [DatabaseName]--GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--проверяем на существование таблицы в БДif objectproperty (object_id ('dbo.TableName'), N'IsUserTable') IS NULLbegin    print N'Создание таблицы - dbo.TableName'    create table dbo.TableName    (        TableNameId uniqueidentifier default newid() not null,        FieldName1 uniqueidentifier not null,        FieldName2 varchar(20) not null,        CONSTRAINT PK_TableName PRIMARY KEY (TableNameId),        CONSTRAINT FK_TableName_FieldName1 FOREIGN KEY (FieldName1)                REFERENCES dbo.ReferenceTableName (RefFieldName)                ON UPDATE CASCADE,        CONSTRAINT UQ_TableName_FieldName1_FieldName2 UNIQUE (FieldName1, FieldName2)    );endGO-- Добавить полеif not exists (        select *        from INFORMATION_SCHEMA.COLUMNS        where TABLE_SCHEMA = 'dbo'          and TABLE_NAME = 'TableName'          and COLUMN_NAME = 'FieldName'        )begin    alter table dbo.TableName add FieldName varchar(500)    print N'Добавлено поле FieldName в таблице dbo.TableName'end-- Создать FOREIGN KEY, если его не существуетif not exists (select * from sys.foreign_keys where object_id = OBJECT_ID(N'dbo.FK_TableName_FieldName1') AND parent_object_id = OBJECT_ID(N'dbo.TableName'))ALTER TABLE dbo.TableName WITH CHECK ADD CONSTRAINT FK_TableName_FieldName1 FOREIGN KEY(FieldName1)REFERENCES dbo.ReferenceTableName (RefFieldName)GOIF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'dbo.FK_TableName_FieldName1') AND parent_object_id = OBJECT_ID(N'dbo.TableName'))ALTER TABLE dbo.TableName CHECK CONSTRAINT FK_TableName_FieldName1GO-- Создание уникального индекса, если он не существуетif not exists (select * from information_schema.key_column_usage where CONSTRAINT_NAME='UQ_TableName_FieldName1_FieldName2')begin    ALTER TABLE dbo.TableName ADD CONSTRAINT UQ_TableName_FieldName1_FieldName2 UNIQUE (FieldName1, FieldName2)end-- Создание DEFAULT ограничения, если он не существуетif not exists (select * from sysconstraints where id = OBJECT_ID('dbo.TableName') AND COL_NAME(id,colid) = 'FieldName2' AND OBJECTPROPERTY(constid, 'IsDefaultCnst') = 1)begin    ALTER TABLE dbo.TableName ADD CONSTRAINT DF_TableName_FieldName2 DEFAULT ('DefaultValue') FOR FieldName2enddeclare @SchemaName varchar(128) = 'dbo'declare @TableName varchar(128) = 'TableName'-- Создание описания таблицыIF NOT EXISTS (SELECT * FROM fn_listextendedproperty('MS_Description', 'SCHEMA', @SchemaName, 'TABLE', @TableName, default, default))    EXECUTE sp_addextendedproperty        N'MS_Description', N'Описание таблицы',        N'SCHEMA', @SchemaName,        N'TABLE', @TableName-- Создание описания поля, если его не существуетIF NOT EXISTS (SELECT * FROM fn_listextendedproperty ('MS_Description', 'schema', @SchemaName, 'table', @TableName, 'column', 'FieldName1'))    EXECUTE sp_addextendedproperty        N'MS_Description', N'Описание поля FieldName1',        N'SCHEMA', @SchemaName,        N'TABLE', @TableName,        N'COLUMN', N'FieldName1'GO

Создание/изменение представления
--USE [DatabaseName]--GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--проверяем на существование представления в БДif objectproperty (object_id ('dbo.vw_ViewName'), N'IsView') is nullBEGIN    PRINT 'CREATE VIEW - '+db_name()+'.dbo.vw_ViewName'    EXECUTE('CREATE VIEW dbo.vw_ViewName AS SELECT 1/0 as ColumnName');ENDGOPRINT 'ALTER VIEW - '+db_name()+'.dbo.vw_ViewName'GOalter view dbo.vw_ViewNameasselect    FieldName1, FieldName2from dbo.TableName

Создание/изменение процедуры
--USE [DatabaseName]--GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOif objectproperty (object_id ('dbo.usp_StoredProcedureName'), N'IsProcedure') is nullBEGIN-- If procedure exists, we exclude script execution.    PRINT 'CREATE PROCEDURE - '+db_name()+'.dbo.usp_StoredProcedureName'    EXECUTE('CREATE PROCEDURE dbo.usp_StoredProcedureName as select 1/0');ENDELSE    PRINT 'ALTER PROCEDURE - '+db_name()+'.dbo.usp_StoredProcedureName'GOalter procedure dbo.usp_StoredProcedureNameas select null

Создание/изменение триггера
--USE [DatabaseName]--GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO--проверяем на существование триггера в БДif objectproperty (object_id ('dbo.TriggerName'), N'IsTrigger') is nullBEGIN    PRINT 'CREATE TRIGGER - '+db_name()+'.dbo.TriggerName'    EXECUTE('CREATE TRIGGER dbo.TriggerName on dbo.TableName for insert as print 0');ENDGOPRINT 'ALTER TRIGGER - '+db_name()+'.dbo.TriggerName'GOalter trigger dbo.TriggerName on dbo.TableNamefor insert, update, deleteasif (exists(SELECT 1 from inserted) and exists (SELECT 1 from deleted)) -- определение UPDATEbegin  if update(FieldName)    begin      update dbo.TableName      set FieldName = FieldValue      where SearchFieldName in      (        select FieldValue from inserted        union        select FieldValue from deleted      )    endendelseif (exists(SELECT 1 from inserted) and not exists (SELECT 1 from deleted)) -- определение INSERTbegin  /* Ваш код обработки */endelseif (not exists(SELECT 1 from inserted) and exists (SELECT 1 from deleted)) -- определение DELETEbegin  /* Ваш код обработки */end;

Создание/изменение табличного типа
--USE [DatabaseName]--GO/*DROP PROCEDURE dbo.ProcedureNameDROP TYPE dbo.CustomUserType*/IF TYPE_ID(N'dbo.CustomUserType') IS NULL BEGIN    PRINT N'Создание типа - dbo.CustomUserType'    --Если тип не существует, создаем его.    CREATE TYPE dbo.CustomUserType AS TABLE    (        FieldId TYPE,        PRIMARY KEY (FieldId)    )ENDGO

Создание/изменение скалярной функции
--USE [DatabaseName]--GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOif objectproperty (object_id ('dbo.ufn_FunctionName'), N'IsScalarFunction') is nullBEGIN-- If procedure exists, we exclude script execution.    PRINT 'CREATE FUNCTION - '+db_name()+'.dbo.ufn_FunctionName'    EXECUTE('CREATE FUNCTION dbo.ufn_FunctionName() returns int begin return 0 end');ENDELSE    PRINT 'ALTER FUNCTION - '+db_name()+'.dbo.ufn_FunctionName'GOalter function dbo.ufn_FunctionName ()returns varchar(100)begin    return null;endGO

Создание/изменение табличной функции
--USE [DatabaseName]--GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOif objectproperty (object_id ('dbo.ufn_TableFunctionName'), N'IsTableFunction') is nullBEGIN-- If procedure exists, we exclude script execution.    PRINT 'CREATE FUNCTION - '+db_name()+'.dbo.ufn_TableFunctionName'    EXECUTE('CREATE FUNCTION dbo.ufn_TableFunctionName() returns table as return (select null as c)');ENDELSE    PRINT 'ALTER FUNCTION - '+db_name()+'.dbo.ufn_TableFunctionName'GOALTER function dbo.ufn_TableFunctionName()returns table    as return(    select 1 as Field1, 2 as Field2)

Удаление/создание XSD-схем (XmlSchemaCollection)
--USE [DatabaseName]--GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOif exists (select * from sys.xml_schema_collections where [schema_id] = schema_id('dbo') and [name] = 'SdmxXsd')begin    drop XML SCHEMA COLLECTION dbo.SdmxXsd    print 'Удалена XSD схема - dbo.SdmxXsd'end--Создать XSD схему, если она отсутствует в БДif not exists (select * from sys.xml_schema_collections where [schema_id] = schema_id('dbo') and [name] = 'SdmxXsd')begin    CREATE XML SCHEMA COLLECTION dbo.SdmxXsd asN'<?xml version="1.0" encoding="utf-16"?><xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://personeltest.ru/away/www.w3.org/2001/XMLSchema">  <xs:element name="Ref">    <xs:complexType>      <xs:attribute name="agencyID" type="xs:string" use="required" />      <xs:attribute name="id" type="xs:unsignedInt" use="required" />      <xs:attribute name="version" type="xs:decimal" use="required" />    </xs:complexType>  </xs:element></xs:schema>'    ALTER XML SCHEMA COLLECTION dbo.SdmxXsd addN'<?xml version="1.0" encoding="utf-16"?><xs:schema xmlns:tns="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/common" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/common" xmlns:xs="http://personeltest.ru/away/www.w3.org/2001/XMLSchema">  <xs:import />  <xs:element name="Structure">    <xs:complexType>      <xs:sequence>        <xs:element ref="Ref" />      </xs:sequence>    </xs:complexType>  </xs:element></xs:schema>'    ALTER XML SCHEMA COLLECTION dbo.SdmxXsd addN'<?xml version="1.0" encoding="utf-16"?><xs:schema xmlns:tns="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic" xmlns:xs="http://personeltest.ru/away/www.w3.org/2001/XMLSchema">  <xs:element name="Series">    <xs:complexType>      <xs:sequence>        <xs:element name="SeriesKey">          <xs:complexType>            <xs:sequence>              <xs:element maxOccurs="unbounded" name="Value">                <xs:complexType>                  <xs:attribute name="id" type="xs:string" use="required" />                  <xs:attribute name="value" type="xs:string" use="required" />                </xs:complexType>              </xs:element>            </xs:sequence>          </xs:complexType>        </xs:element>        <xs:element name="Attributes">          <xs:complexType>            <xs:sequence>              <xs:element maxOccurs="unbounded" name="Value">                <xs:complexType>                  <xs:attribute name="id" type="xs:string" use="required" />                  <xs:attribute name="value" type="xs:string" use="required" />                </xs:complexType>              </xs:element>            </xs:sequence>          </xs:complexType>        </xs:element>        <xs:element name="Obs">          <xs:complexType>            <xs:sequence>              <xs:element name="ObsDimension">                <xs:complexType>                  <xs:attribute name="value" type="xs:string" use="required" />                </xs:complexType>              </xs:element>              <xs:element name="ObsValue">                <xs:complexType>                  <xs:attribute name="value" type="xs:decimal" use="required" />                </xs:complexType>              </xs:element>            </xs:sequence>          </xs:complexType>        </xs:element>      </xs:sequence>    </xs:complexType>  </xs:element></xs:schema>'    ALTER XML SCHEMA COLLECTION dbo.SdmxXsd addN'<?xml version="1.0" encoding="utf-16"?><xs:schema xmlns:message="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/message" xmlns:common="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/common" xmlns:xsi="http://personeltest.ru/away/www.w3.org/2001/XMLSchema-instance" xmlns:generic="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic" attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/message" xmlns:xs="http://personeltest.ru/away/www.w3.org/2001/XMLSchema">  <xs:import namespace="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/common" />  <xs:import namespace="http://personeltest.ru/away/www.sdmx.org/resources/sdmxml/schemas/v2_1/data/generic" />  <xs:element name="GenericData">    <xs:complexType>      <xs:sequence>        <xs:element name="Header">          <xs:complexType>            <xs:sequence>              <xs:element name="ID" type="xs:string" />              <xs:element name="Test" type="xs:boolean" />              <xs:element name="Truncated" type="xs:boolean" />              <xs:element name="Prepared" type="xs:dateTime" />              <xs:element name="Sender">                <xs:complexType>                  <xs:attribute name="id" type="xs:string" use="required" />                </xs:complexType>              </xs:element>              <xs:element name="Structure">                <xs:complexType>                  <xs:sequence>                    <xs:element ref="common:Structure" />                  </xs:sequence>                  <xs:attribute name="structureID" type="xs:string" use="required" />                  <xs:attribute name="dimensionAtObservation" type="xs:string" use="required" />                </xs:complexType>              </xs:element>            </xs:sequence>          </xs:complexType>        </xs:element>        <xs:element name="DataSet">          <xs:complexType>            <xs:sequence>              <xs:element maxOccurs="unbounded" ref="generic:Series" />            </xs:sequence>            <xs:attribute name="structureRef" type="xs:string" use="required" />          </xs:complexType>        </xs:element>      </xs:sequence>    </xs:complexType>  </xs:element></xs:schema>'    print 'Создана XSD схема - dbo.SdmxXsd'endgo

Ведение репозитория


1) Репозиторий состоит из папок, разделенных по типу объектов (например: Tables, Views, Triggers и т.д.)
2) Каждый объект БД хранится в отдельном SQL файле для удобства просмотра истории изменений в системе контроля версий.
3) Если изменения касаются данных, то такие изменения ведутся в отдельном файле "CommonChanges (version 000).sql", который создается на каждое обновление БД
4) Для автоматизации применения изменений на нескольких серверах запросы выполняются с помощью BAT файла.


Пример BAT файла
:: Описание: Обновление БД Microsoft SQL Server:::: ВНИМАНИЕ: В случае если в разделе "1. Описание параметров" содержатся русские символы, то необходимо раскомментировать следующую строку::chcp 1251:::: 1. ОПИСАНИЕ ПАРАМЕТРОВ:::: Получить общие настройки (соединение с БД и др.)call Settings.bat:::: Лог-файл выполнения запроса (перезаписывается с каждым вызовом sqlcmd)set LogFileName="%LogDir%\update_0001_%NowDateTime%_tmp.log":::: Полный лог-файл обновления БДset FullLogFileName="%LogDir%\update_0001_%NowDateTime%.log":::: Лог файла командного выполненияset LogCmdFileName="%LogDir%\update_0001_%NowDateTime%_cmd.log":::: 2. ВПОЛНЕНИЕ SQL-ЗАПРОСОВ::set SqlFileName="..\CommonChanges (version 001).sql"@If Exist %SqlFileName% (%sqlcmd% -S %ServerName% -U %UserName% -P %Password% -d %DatabaseName% -i %SqlFileName% -o %LogFileName% -btype %LogFileName% >> %FullLogFileName%) ElSE (echo Не найден файл скрипта %SqlFileName% >> %LogCmdFileName%)::set SqlFileName="..\AlterObjects\StoredProcedures\dbo.SP.sql"@If Exist %SqlFileName% (%sqlcmd% -S %ServerName% -U %UserName% -P %Password% -d %DatabaseName% -i %SqlFileName% -o %LogFileName% -btype %LogFileName% >> %FullLogFileName%) ElSE (echo Не найден файл скрипта %SqlFileName% >> %LogCmdFileName%)::set SqlFileName="..\UpdateVersion.sql"@If Exist %SqlFileName% (%sqlcmd% -S %ServerName% -U %UserName% -P %Password% -d %DatabaseName% -i %SqlFileName% -o %LogFileName% -btype %LogFileName% >> %FullLogFileName%) ElSE (echo Не найден файл скрипта %SqlFileName% >> %LogCmdFileName%):::: Вывести содержимое лога файла командного выполнения в общий лог, если он был сформирован@If Exist %LogCmdFileName% (echo ----Внимание, не найдены следующие файлы ожидающие выполнения:---- >> %FullLogFileName%type %LogCmdFileName% >> %FullLogFileName%:: Удалить лог файла командного выполненияdel %LogCmdFileName%):: Удалить сокращенный лог (оставшийся при последнем вызове sqlcmd)del %LogFileName%:::: Вывести содержимое файла-лога с результатом в командную строкуtype %FullLogFileName%pause

Файл Settings.bat
:: Настройки соединения с БД:::: ВНИМАНИЕ: В случае если в разделе "1. Описание параметров" содержатся русские символы, то необходимо раскомментировать следующую строку::chcp 1251:::: 1. ОПИСАНИЕ ПАРАМЕТРОВ:::: Путь к файлу sqlcmd.exe на локальном дискеset sqlcmd="sqlcmd.exe":::: Путь к файлу bcp.exe на локальном дискеset bcp="bcp.exe":::: Имя SQL-сервера (именованный экземпляр)set ServerName="(local)\InstanceName":::: Имя пользователяset UserName="sa":::: Парольset Password="my_password":::: Имя БДset DatabaseName=my_db:::: Каталог логовset LogDir=.\log:::: Использовать на русскоязычной версии Windows. Текущее время в формате YYYYMMDD_HHMMSS (рекомендуется использовать в имени файла лога)set NowDateTime=%date:~6%%date:~3,2%%date:~0,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%:: Использовать на английской версии Windows. Текущее время в формате YYYYMMDD_HHMMSS (рекомендуется использовать в имени файла лога)::set NowDateTime=%date:~10,4%%date:~4,2%%date:~7,2%_%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%:::: 2. СОЗДАНИЕ КАТАЛОГА ДЛЯ ХРАНЕНИЯ ЛОГОВmd %LogDir%

Достоинства


1) Удобно отслеживать историю изменения каждого объекта БД
2) Не требуется разрабатывать собственные приложения для выполнения SQL файлов
3) Работает на всех версиях MSSQL и Windows
4) Настройки соединения с БД, имена БД и др. переменные хранятся в одном файле "Settings.bat", которые легко изменить


Недостатки


1) Не предусмотрен общий откат изменений, если какой-то скрипт выполнится с ошибкой
2) Если репозиторий состоит из большого количества объектов БД, то чтобы не передавать заказчику весь репозиторий, нужно копировать файлы (входящие в обновление) в отдельный каталог


Выгрузка структуры БД


В качестве автоматизации выгрузки структуры БД, в формате один объект = один файл, на данный момент использую SSMS, но в скором времени подобный функционал будет в моей программе ImportExportDataSql, которую рекомендую всем разработчикам БД.


Немного о ImportExportDataSql


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


С помощью ImportExportDataSql Вы сможете:


  • быстро загружать CSV файлы большого объема (более 1Гб) в SQL Server
  • загружать Excel файлы и CSV с возможностью настройки полей, а также с ограничением количества обрабатываемых строк (удобно при отладке)
  • выгружать выборочные данные из БД, в SQL формате и затем выполнять этот скрипт на другой БД (т.е. использовать как средство синхронизации данных)
  • копировать джобы с одной машины на другую

    image
  • выгружать структуру БД.

Главной особенностью ImportExportDataSql, является то, что можно объединять несколько SELECT запросов, выгружая результат в виде SQL в один файл.


Добавляйтесь в группу VK, пишите свои пожелания, буду рад доработать приложение под Ваши нужды.


Заключение


Рад был поделиться результатом своего опыта. Данный способ ведения репозитория меня вполне устраивает. Надеюсь, эта статья поможет нам навести порядок в базах данных.

Подробнее..

Clarion. Процесс миграции Clarion приложения на Microsoft SQL 2019

30.05.2021 18:13:46 | Автор: admin

Продолжаю повествовать о жизни с Clarion. В этом посте я опишу свой путь решения одной из частых задач, стоящих перед Clarion разработчиками, это миграция Clarion программы на СУБД Miscrosoft SQL.

Так получилось что несколько месяцев назад мне на обслуживание передали 2 программы на технологии Clarion, повод грустный, уходит старое поколение, так и случилось с моим научным руководителем. Несколько лет я вместе с ним работал программистом на Clarion, далее я потерял интерес к этой технологии и наши пути разошлись. А сейчас, по прошествии лет передо мной стоит необходимость поддерживать и изредка развивать 2 программы.

Проблематика

Главная на мой взгляд проблема и сложность это работа программы только с нативной СУБД Clarion, доступ к данным при таком подходе очень неудачный, требуется большой объем кода для написания даже простейших задач, которые решаются отправкой на сервер простейшего Update или Insert в Clarion это десятки строчек кода по открытию файла, получению доступа к инфе и его последующего закрытия. Ниже пример:

       Access:Agent.Open !Открываем файл       Access:Agent.UseFile!Открываем файл       clear(AGN:Record)!Делаем очистку записи на всякий случай       AGN:ID_AGENT = some_id !Присваиваем ключу значение       set(AGN:BY_ID,AGN:BY_ID)!Устанавливаем "каретку" на первое значение ключа       next(agent)!Встаем на первую запись удовлетворяющую ключу       IF errorcode() or AGN:ID_AGENT <> some_id!Проверяем не вышла ли каретка за область ключа            RETVAL = 'Контрагент не найден'!Выкидываем ошибку          ELSE            RETVAL = AGN:N_AGENT!Возвращаем имя агента       .       Access:Agent.Close  !Закрываем файл

Вот столько действий надо сделать чтобы просто получить запись, далее если записей пачка, то надо запустить цикл и прогнать цикл до конца и "по дороге" контролировать чтобы "каретка" не выбежала за требуемые условия обработки данных. Сущий ад просто. Это можно заменить одним запросом SQL вида:

select agent.name where id = some_id

Задача

Первые задачи передо мной встали очень грустной перспективой, одна из них звучала как "написать модуль выгрузки данные в 1С Бухгалтерию", я сразу представил алгоритм длиной в пару тысяч строк, который можно заменить на один запрос SQL, и вопрос перехода на SQL уже был решен. Речь конечно не о полном переходе, а только о частичной замене части таблиц на версию SQL и поэтапной миграции системы в SQL.

Характеристики системы

Обе системы примерно схожие по объему кодовой базы и по количеству пользователей

Общее количество пользователей: около 80

Общее количество таблиц: около 250

Сфера деятельности: Торговля + Сфера обслуживания (Салоны красоты)

Подразделения:

3 Салона красоты

5 Подразделений торговых предприятий - мелкооптовая торговля

Используемые инструменты

  • Самодельная программа миграции

  • DCT2SQL

  • Cldump

  • BULK insert

  • UltimateSQL & Ultimate Debug

Самодельная программа миграции

Программа предназначена для автоматизации хранения скриптов по созданию, удалению, очистке таблиц и прочих действий полезных в процессе переноса данных. Также можно запустить произвольный скрипт. Помимо этого программа отправляет post запросы для конвертации dat файлов с их последующей загрузкой в соответствующие таблицы.

DCT2SQL

Данный компонент позволяет генерировать скрипты для экспорта структуры БД из Dictionary в SQL, поддерживает экспорт всех возможных типов данных, также экспортирует индексы и foreign keys. Очень удобно работает, все импортируется в пару кликов. Данные скрипты я храню в таблице миграций.

Можно скачать на Github - https://github.com/RobertArtigas/DCT2SQL

Также есть обучающие ролики на youtube по работе и правильной выгрузке. Вообще там очень много инфы в этих роликах по миграции на SQL.

https://www.youtube.com/watch?v=MjMgQYMc_xY

https://www.youtube.com/watch?v=bAolfvrz2oE&t=7067s

CLDUMP

Данная программа конвертирует данные из *.dat файла в csv таблицы готовые для загрузки через скрипт BULK. Достоинство этой программы - скорость. Она может сконвертировать таблицу накладных за 10 лет за 15-20 секунд. Главная проблема данной утилиты в том, что она доступна только в репозиториях Linux, в частности debian. Пришлось на основе этой команды создать микро-сервис, который на входе принимает post запрос, а на выходе выдает ссылку для скачивания данного файла в виде csv таблицы.

Также одна из проблем была в том, что часть таблиц лежит в папках с "годами" то есть данные каждый год начинаются в таблице заново, например таблица с заказами, ей требуется уникальная идентификация накладных внутри года, в таком случае таблицу кладут в отдельную папку соответствующую "году" происходящего в этой таблице. Чтобы посмотреть заказы "другого года" надо выйти из программы и "зайти в другой год". Такова архитектура приложения. Этот ньюанс тоже надо проработать, поэтому для каждой таблицы есть свой признак, лежит она в общей папке или папке с "годом".

Программу cldump можно скачать командой в любой debian подобной системе:

apt-get install cldump

BULK insert

За доли секунд втягивает таблицу из csv в SQL. В данном случает, т.к. связки данных уже настроены на существующие ID, но при этом надо чтобы работал автоинкремент, поэтому его временно надо отключить, также потратил довольно много времени чтобы подобрать подходящие разделители:

BULK INSERT dbo.%table_name%FROM table_name.csv WITH ( FORMAT = 'CSV', FIELDQUOTE = '', FIRSTROW = 1, FIELDTERMINATOR = '0x3b', ROWTERMINATOR = '0x0a', CODEPAGE='65001',TABLOCK, KeepIdentity)

UltimateSQL & Ultimate Debug

Данные компоненты позволяют загружать данные из SQL в QUEUE примерно таким образом:

SQL_Result = sql.query('select id, path_to_result from dbo.export_tasks as et where (status_complete = 0 or status_complete = 2) and export_table_id = '& exp:id,qexport_tasks)

Выполнять запросы без возвращаемых значений:

sql.Query('Update export_tasks set status_complete = 2 where id = ' & qexport_tasks.id)

Есть отличное описание как использовать на youtube:

https://www.youtube.com/watch?v=RVit-5RPncs&t=2259s

Также при установке внутри шаблонов есть "пасхалка" от автора, как решить квест описывается по ссылке:

https://clarionhub.com/t/need-some-help-with-ultimatesql-error-when-trying-to-include-it-in-my-project/4182

Подробнее..
Категории: Data engineering , Субд , Mssql , Database , Clarion 11

Категории

Последние комментарии

  • Имя: Макс
    24.08.2022 | 11:28
    Я разраб в IT компании, работаю на арбитражную команду. Мы работаем с приламы и сайтами, при работе замечаются постоянные баны и лаги. Пацаны посоветовали сервис по анализу исходного кода,https://app Подробнее..
  • Имя: 9055410337
    20.08.2022 | 17:41
    поможем пишите в телеграм Подробнее..
  • Имя: sabbat
    17.08.2022 | 20:42
    Охренеть.. это просто шикарная статья, феноменально круто. Большое спасибо за разбор! Надеюсь как-нибудь с тобой связаться для обсуждений чего-либо) Подробнее..
  • Имя: Мария
    09.08.2022 | 14:44
    Добрый день. Если обладаете такой информацией, то подскажите, пожалуйста, где можно найти много-много материала по Yggdrasil и его уязвимостях для написания диплома? Благодарю. Подробнее..
© 2006-2024, personeltest.ru