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

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


При постоянной работе с 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, пишите свои пожелания, буду рад доработать приложение под Ваши нужды.


Заключение


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

Источник: habr.com
К списку статей
Опубликовано: 05.11.2020 04:10:16
0

Сейчас читают

Комментариев (0)
Имя
Электронная почта

Sql

Microsoft sql server

Sql server

Ms sql server

Mssql

Ms sql

Tsql

Категории

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

  • Имя: Макс
    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