Автоматическое документирование базы данных

В процессе разработки баз данных часто возникает следующая ситуация. В процессе развития базы данных в ней создаются различные объекты: файловые группы, схемы, таблицы, роли. Когда объектов становится много, программист может забыть о назначении некоторых из них, например, таблиц и их столбцов, об особенностях работы процедур. Разработчики, которые работают над одной частью базы данных могут не иметь достаточных знаний об особенностях работы других частей базы, которые были созданы другими разработчиками. Если разработчик создал таблицу, схему или хранимую процедуру, а затем долгое время с ней не работал, то возвращаясь к этим объектам, он не всегда может вспомнить об их назначении и особенностях.
Для того. чтобы база данных была документирована во многих организациях разработчики создают документы, например, файлы Word или Visio с описаниями объектов, бизнес-процессов или алгоритмов работы процедур. Но при создании таких документов требуется поддерживать их в актуальном состоянии, продумывать параллельный доступ к ним нескольких разработчиков, заботиться об их сохранности. Предлагаю использовать для документирования баз данных механизм расширенных свойств. Для объекта базы данных можно добавить одно или несколько расширенных свойств, в которых может содержаться описание объекта, алгоритм его работы. Данные сведения можно извлекать запросами, они входят как в полный так и в скриптовой бэкап, что делает подобное документирование очень удобным и независимым.
Для изучения расширенных свойств создадим базу данных:

use master

go

create database BankTransactions

go

use BankTransactions

go

Добавим описание базы данных:
exec sp_addextendedproperty
    @name = N'Назначение базы данных',
    @value = N'База данных предназначена для осуществления ежедневных банковских транзакций.'

Создадим схему и таблицу:

create schema AccData authorization dbo

go

create table AccData.Accounts

(

    AccountId     int         not null,

    AccountNumber char ( 20 ) not null,

    constraint PK_Accounts_AccountId primary key clustered ( AccountId asc ) on [PRIMARY],

    constraint AK_Accounts_AccountNumber unique nonclustered ( AccountNumber asc ) on [PRIMARY]

) on [PRIMARY]
go

Добавим описания объектов:

exec sp_addextendedproperty

    @name = N'Описание схемы', @value = N'Схема предназначена для хранения объектов,

        связанных со счетами, бухгалтерским учетом.',

    @level0type = N'SCHEMA', @level0name = N'AccData'

exec sp_addextendedproperty

    @name = N'Описание таблицы',

    @value = N'Таблица хранит перечень лицевых номеров счетов',

    @level0type = N'SCHEMA', @level0name = N'AccData',

    @level1type = N'TABLE', @level1name = N'Accounts'

Добавим описания столбцов таблицы:

exec sp_addextendedproperty

    @name = N'MS_Description', @value = N'Идентификатор лицевого счета.',

    @level0type = N'Schema', @level0name = N'AccData',

    @level1type = N'Table', @level1name = N'Accounts',

    @level2type = N'Column', @level2name = N'AccountId'

exec sp_addextendedproperty

    @name = N'MS_Description', @value = N'Номер лицевого счета.',

    @level0type = N'Schema', @level0name = N'AccData',

    @level1type = N'Table', @level1name = N'Accounts',

    @level2type = N'Column', @level2name = N'AccountNumber'

Здесь в качестве имени свойства мы использовали встроенное свойство MS_Description. Благодаря этому мы сможем автоматически увидеть эти описания при создании диаграммы базы данных в Management Studio.

Для полноты картины создадим хранимую процедуру:

create proc AccData.AccountGet

as

begin

       set nocount, xact_abort on

       select AccountId, AccountNumber

       from AccData.Accounts

end
go

Для нее мы добавим уже 2 свойства: назначение процедуры и подробное описание алгоритма:

exec sp_addextendedproperty

       @name = N'Описание',

       @value = N'Доступ к счетам',

       @level0type = N'schema',

       @level0name = N'AccData',

       @level1type = N'procedure',

       @level1name = N'AccountGet'

exec sp_addextendedproperty

       @name = N'Алгоритм',

       @value = N'Процедура выводит перечень лицевых счетов, делая запрос к таблице Accounts.',

       @level0type = N'schema',

       @level0name = N'AccData',

       @level1type = N'procedure',

       @level1name = N'AccountGet'

Итак, описания добавлены. Как их теперь просматривать? Можно выполнять запрос к представлению sys.extended_properties:

select class_desc, minor_id, name, value

from sys.extended_properties

where major_id in ( object_id ( N'AccData.Accounts', N'U' ), object_id ( N'AccData.AccountGet', N'P' ), schema_id ( N'AccData' ) )

       or class_desc in ( N'DATABASE' )

Можно написать общий запрос, который будет дополнительно извлекать по полю major_id имена таблиц, процедур, схем, а по полю minor_id имена столбцов:

; with Data

as

(

    select

        case

            when sch.name is not null then N'схема'

            when obj.name is not null and col.name is null then obj.type_desc collate Cyrillic_General_CI_AS

            when obj.name is not null and col.name is not null then N'столбец таблицы ' +

                quotename ( object_schema_name ( obj.object_id ) ) + N'.' + quotename ( obj.name )

            when prop.class_desc = N'DATABASE' then N'база данных'

        end [Тип объекта],

        case

            when sch.name is not null then sch.name

            when obj.name is not null and col.name is null then

                quotename ( object_schema_name ( obj.object_id ) ) + N'.' + quotename ( obj.name )

            when obj.name is not null and col.name is not null then col.name

            when prop.class_desc = N'DATABASE' then db_name ()

        end [Имя объекта],

        case when prop.name = N'MS_Description' then N'Описание' else prop.name end Свойство,

             prop.value Значение

    from

        sys.extended_properties prop

            left outer join

        sys.objects obj on prop.class_desc in ( N'OBJECT_OR_COLUMN' ) and prop.major_id = obj.[object_id]

            left outer join

        sys.columns col on obj.object_id = col.object_id and prop.minor_id = col.column_id

            left outer join

        sys.schemas sch on prop.class_desc in ( N'SCHEMA' ) and prop.major_id = sch.schema_id

)

select [Тип объекта], [Имя объекта], Свойство, Значение

from Data

Этот запрос удобен тем, что можно добавить условие where и получить свойства только определенных объектов или столбцов. Лучше всего инкапсулировать этот код в представление.

Кроме запуска sql-кода можно посмотреть и изменить значения расширенных свойств через Management Studio, надо только в обозревателе объектов, выбрать объект, щелкнуть на нем правой кнопкой мыши и в форме свойств перейти на вкладку Расширенные свойства. В скриншоте ниже показана форма свойств для хранимой процедуры AccData.AccountGet:

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

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

Особенности в работе функции openrowset

Как известно для запросов к Excel-файлам можно использовать функцию openrowset. Проверим работу этой функции на примере. Создадим файл 1.xlsx с таким содержанием:

Сохраним файл на рабочем столе. Убедимся в том, что сервер видит его:
exec xp_fileexist 'C:\Users\В\Desktop\1.xlsx'

Теперь попробуем сделать к нему запрос:

select *

from openrowset

(

       'Microsoft.ACE.OLEDB.12.0',

       'Excel 12.0;Database=C:\Users\В\Desktop\1.xlsx;HDR=NO',

       'select * from [Лист1$]'

)

Однако при попытке его выполнить появляется сообщение об ошибке:

Сообщение 7399, уровень 16, состояние 1, строка 1

Поставщик OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)" сообщил об ошибке. Поставщик не предоставил данных об ошибке.

Сообщение 7330, уровень 16, состояние 2, строка 1

Не удалось получить строку от поставщика OLE DB "Microsoft.ACE.OLEDB.12.0" для связанного сервера "(null)".

Разберемся с причиной ошибки. Изначально я подключился к серверу на основе своей учетной записи Windows. При этом вхожу в роль sysadmin. Теперь подключимся на основе учетной SQL Server, под логином sa. Запускаем запрос и он успешно возвращает данные:

Оказывается, когда мы запускаем запрос то его результаты сохраняются в файле, который находится в директории переменной окружения temp или tmp. Посмотрим что это за директория. Для этого откроем список переменных среды:

Откроем папку C:\Users\В\AppData\Local\Temp и отсортируем ее содержимое по дате модификации. Сверху есть файл с именем tmp3BED.tmp. Откроем его при помощи Notepad:

В нем как раз результаты нашего запроса. Теперь можно сделать вывод, что при запуске openrowset с провайдером ACE под учетной записью Windows доступ к этой директории осуществляется именно при помощи учетной записи Windows. Если же мы подключаемся к серверу под учетной записью SQL Server, то доступ в Temp выполняется под учетной записью службы сервера. Если у учетной записи не хватает прав на работу с файлами, содержащими результаты работы запросов, то возникает ошибка.

Цикл в атомарной операции

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

create table dbo.Accounts

(

       iAccountId   int identity ( 1, 1 ) not null,

       iGroupId     int                   not null,

       vcAccount    char ( 20 )           not null,

       fRest        float                 not null,

       fCoef        float                 not null,

       constraint PK_Accounts_iGroupId_iAccountId primary key clustered ( iGroupId asc, iAccountId asc ) on [PRIMARY]

) on [PRIMARY]

Вставим несколько строк:

insert into dbo.Accounts ( iGroupId, vcAccount, fRest, fCoef )

       values

             ( 1, '20202810896983470198', 15, 1.4 ),

             ( 1, '20202810736329201930', 14, 1.5 ),

             ( 1, '20202810176070804078', 56, 1.9 ),

             ( 1, '20202810210923822168', 23, 2.4 ),

             ( 2, '40702840636152513621', 11, 0.5 ),

             ( 2, '40702840774312017863', 7, 0.9 ),

             ( 2, '40702840704759073191', 87, 2.8 )

Теперь сформулируем задачу. Для каждой группы счетов требуется вычислить какую-то ее числовую характеристику, которая определяется столбцами fRest и fCoef счетов этой группы и вычисляется по определенной формуле. Для примера рассмотрим такую реккурентную формулу, записанную псевдокодом: fChar_Cur = ( fChar_Prev + fRest_Cur * fCoef_Cur ) / ( 1 + fCoef_Cur ).
То есть характеристика группы рассчитывается по этой формуле поочереди для каждого счета. Счета берутся по порядку (по полю iAccountId). Имея определенный iAccountId мы берем его fRest и fCoef и подставляем их в формулу вместе со значением характеристики, вычисленной для предыдущего iAccountId в данной группе. Когда мы работаем с самым первым iAccountId данной группы, то в качестве fChar_Prev берем ноль.

По всему видно, что операция циклическая и посчитать fChar для каждой группы можно было бы с помощью курсора на таблице. Можно ли придумать алгоритм решения задачи без использования циклов, а только с помощью операций над множествами? Если бы в формуле не было умножения и деления, то все сводилось бы к расчету нарастающего итога и сумма для последнего iAccountId давала бы характеристику группы. Нарастающий итог можно посчитать одним запросом, используя оконные функции, как указано в одной из моих предыдущих работ: Новые аналитические функции MS SQL 2012.

Если бы у нас в формуле не было знаменателя, то fChar для каждой группы равнялся бы произведению fRest для первого iAccountId в группе на произведения величин ( 1 + fCoef ) по всем fCoef от первого до текущего iAccountId группы. В этом случае можно было бы создать агрегирующую функцию на базе clr-сборки, которая вычисляет произведение ( 1 + fCoef ) по всем элементам группы. Однако наличие знаменателя все портит: общая формула становится сложнее и что самое критичное значение начинает зависеть от порядка, в котором берутся iAccountId. Поэтому агрегирующие функции на базе CLR не помогут.

Ниже я приведу решение, которое позволяет избежать применения курсоров и решить задачу сделав один update и один select. Для начала надо добавить к таблице столбец fChar, в котором будет храниться атрибут группы. Его значение будет искомым для максимального iAccountId в пределах группы:
alter table dbo.Accounts add fChar float not null default ( 0 )

Также необходимо добавить столбец в минимальным iAccountId для данной группы. Он необходим для контроля над тем когда в fChar должен быть нулем для применения формулы первый раз для очередной группы:

alter table dbo.Accounts add iMinAccId int null

go

update acc

       set acc.iMinAccId = minacc.iAccountId

       from

             dbo.Accounts acc

                    inner join

             (

                    select iGroupId, min ( iAccountId ) iAccountId

                    from dbo.Accounts

                    group by iGroupId

             ) minacc on acc.iGroupId = minacc.iGroupId

Теперь сделаем такой update:

declare @fChar float = 0

update dbo.Accounts

       set @fChar = fChar = (

           case when iAccountId = iMinAccId then 0 else @fChar end +

           fRest * fCoef ) / ( 1 + fCoef )

Видно что в ходе update происходит не только обновление поля fChar, но циклическое вычисление для переменной @fChar, которая затем используется для обновления fChar следующей строки. При этом на таблице упорядоченность по кластерному индексу должна соответствовать тому порядку, в котором происходит вычисление. Столбец iMinAccId используется для того, чтобы сбросить @fChar в ноль при переходе к начальному iAccountId для новой группы. Теперь для получения атрибутов групп достаточно выполнить такой запрос:

select acc.iGroupId, acc.fChar

from

       dbo.Accounts acc

             inner join

       (

             select iGroupId, max ( iAccountId ) iAccountId

             from dbo.Accounts

             group by iGroupId

       ) maxacc on

             acc.iGroupId = maxacc.iGroupId and

             acc.iAccountId = maxacc.iAccountId

Функция grouping sets

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

if object_id ( N'tempdb..#MonthsData', N'U' ) is not null

       drop table #MonthsData

create table #MonthsData

(

       dtDate date   not null,

       iType  int          not null,

       fSum   float  not null

) on [PRIMARY]

insert into #MonthsData ( dtDate, iType, fSum )

       values

             ( '2010-01-01', 1, 100 ),

             ( '2010-02-01', 1, 130 ),

             ( '2010-03-01', 1, 200 ),

             ( '2010-04-01', 1, 90 ),

             ( '2010-05-01', 1, 85 ),

             ( '2010-01-01', 2, 23 ),

             ( '2010-02-01', 2, 521 ),

             ( '2010-03-01', 2, 34 ),

             ( '2010-04-01', 2, 300 ),

             ( '2010-05-01', 2, 120 )

Перед нами стоит задача получить набор данных этой таблицы, так чтобы в в последней строке дополнительно была указана общая сумма продаж по всем типам, за все время. Для решения этой задачи можно было бы написать запрос к таблице, добавив с помощью union строку с общей суммой. Но есть более элегантное решение. Функция grouping sets позволяет получить набор данных, в котором будут результаты группирований на различных уровнях. Можно написать простой запрос, который возвратит таблицу с общей суммой в последней строке:

select dtDate, iType, sum ( fSum ) fSum

from #MonthsData

group by grouping sets ( ( dtDate, iType ), () )

Здесь виден алгоритм функции grouping sets: в скобках перечисляются наборы, по которым идет группировка. Кортэж ( dtDate, iType ) гарантирует, что вернутся строки таблицы #MonthData. А пустой кортэж исключит из группировки все столбцы, которые входят в select:

Видно, что в строке, в которой вычисляется общая сумма, в качестве dtDate и fSum стоят пустые значения. Во многих случаях это могло бы использоваться как признак того, что уровень группирования изменен. Однако не всегда можно этим пользоваться, поскольку некоторые столбцы в таблице могут иметь значения null. Рассмотрим это на примере:

alter table #MonthsData alter column dtDate date null

alter table #MonthsData alter column iType int null

insert into #MonthsData ( dtDate, iType, fSum )

       values ( null, null, 130 )

Чтобы отличить null, который был вставлен в таблицу, от null, который появился в результирующем наборе из-за группирования, воспользуемся функцией grouping:

select dtDate,

       case when grouping ( dtDate ) = 0 and grouping ( iType ) = 0 then cast ( iType as nvarchar ( 100 ) )

       else N'Общий итог: ' end iType, sum ( fSum ) fSum

from #MonthsData

group by grouping sets ( ( dtDate, iType ), () )

Когда кортэж содержит более одного элемента, то удобнее функция grouping_id, которая может получать в качестве параметра кортэжи. Она получает в качестве параметра кортэж и возвращает битовую маску. То есть, если элемент кортэжа не входит в группировку для данной строки, то для него соответствуюий бит выставлен в 1, иначе в 0. Биты считаются справа налево:

select dtDate,

       case when grouping_id ( dtDate, iType ) = 3 then N'Общий итог: ' else cast ( iType as nvarchar ( 100 ) ) end iType,

       sum ( fSum ) fSum

from #MonthsData

group by grouping sets ( ( dtDate, iType ), () )

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

select dtDate,

       case

             when grouping_id ( dtDate, iType ) = 3 then N'Общий итог: '

             when grouping_id ( dtDate, iType ) = 2 then N'Сумма для всех с типом ' + isnull ( cast ( iType as nvarchar ( 100 ) ), 'null' ) + ': '

       else cast ( iType as nvarchar ( 100 ) ) end iType,

       sum ( fSum ) fSum

from #MonthsData

group by grouping sets ( iType, ( dtDate, iType ), () )

Результаты такие: