Організація користувача лічильників (генераторів) в Microsoft SQL Server, Інші СУБД, Бази даних, статті

Microsoft регулярно дорікають за те, що вSQL Server до сих пір немає реалізації чогось подібного SEQUENCE в Oracle.


В рамках підготовки до доповіді про всілякі ідентифікаторах, лічильниках та інша в Russian SQL Server User Group я зацікавився тим, що пропонує цей конкурент і зрозумів, що нічого, крім старого знайомого IDENTITY, “але по-іншому” для відомих мені бізнес-задач там немає.


Припустимо, є циклічність. Це добре, але м’яко кажучи, не на першому місці за необхідності.
Добре, що SEQUENCE можна користуватися, як функцією, а не тільки, як DEFAULT значенням на поле таблиці і не пов’язувати момент отримання значення з моментом фізичного додавання запису.
Але головні реальні вимоги – динамічне формування імені послідовності (або будь-який інший спосіб реалізації “хочу нумерацію з початку року / місяця”) і хоч якийсь механізм “повторного” отримання “Пропущених значень” відсутні і там.
А якщо бізнес не може пред’явити хоч якісь критерії управління такими послідовностями, то власне все одно, яка функція генерують ці послідовності – IDENTITY, SEQUENCE або NEWID – особливої ​​різниці немає, для номера накладної і рахунки-фактури всі три рішення підходять однаково погано.


Зазвичай запропоновані в рішення по генерації “бізнес-номерів”, написані на TSQL і базуються на таблицях з поточними значеннями лічильників, мають один жутчайшей недолік – блокування. Дійсно, якщо ми “генеруємо” новий номер, то на час генерації ми повинні заблокувати лічильник, щоб в іншому з’єднанні не було отримано теж значення. А як правило, номер нам потрібно отримувати в рамках вже відкритої транзакції, що загрожує тим, що два спочатку незалежних бізнес-процесу будуть в кращому випадку довго блокувати один одного, а в гіршому – виявляться не настільки незалежними, щоб уникнути взаимоблокировки (deadlock).


Крім того, генерацію номера на базі таблиці лічильників не можна “загорнути” в функцію, чого б дуже хотілося для реалізації конструкцій види:


   insert into MyTable(DocNum, DocDate, Comment)
   select Generator.NextValue(“SequenceFor_DocNum”), IncomeDate, Comment
   from #SomeBuffer



оскільки у функціях заборонені будь-які зміни даних і ми не можемо змінити поточне значення.


Але не зовсім будь-які! У функціях є можливість викликати розширені збережені процедури і CLR процедури і функції.


А ось в CLR функції у нас є можливість підключитися до того ж серверу і базі, але вже в іншому з’єднанні і викликати процедуру, що генерує цікавить нас номер, не накладаючи тривалих блокувань.
Для цього доведеться зробити кілька додаткових дій крім написання самої функції:



Тепер перейдемо від “многа букф” до більш зрозумілому T-SQL коду.
Що цікаво, на тому ж TSQL ми зробимо і саму збірку – не потрібний навіть VisualStudio, але про це пізніше.


(Усі запити виконуємо в тій базі, де нам потрібні лічильники)


Налаштовуємо сервер – включаємо CLR:

if exists (select * from sys.configurations where name=”clr enabled” and value_in_use=0)

begin

      exec(“sp_configure “”show advanced options””, 1″)

      exec(“reconfigure”)

      exec(“sp_configure “”clr enabled””, 1″)

      exec(“reconfigure”)

end

go

 

Налаштовуємо базу – дозволяємо в ній працювати unsafe збірок:

declare @sql nvarchar(max)

set @sql = N”alter database “+DB_NAME()+N” set trustworthy on”

exec(@sql)


Створюємо схему _Generator, в якій будуть перебувати основні об’єкти, необхідні для маніпуляції з лічильниками:
if SCHEMA_ID(“_Generator”) is null exec (“create schema _Generator”) 

Створюємо таблицю, в якій будуть зберігатися параметри лічильників:

create table _Generator.List

      (

      ID uniqueidentifier not null default newid() ,

      Name sysname not null,

      StartValue int not null constraint DF__Generator_List_StartValue default 0,

      IsWorkWithHoles tinyint,

      constraint PK_List_ID primary key clustered(ID),

      constraint AK_List_Name unique (Name)

      )

go

  

Тепер створимо тригер, який для кожного внесеного в _Generator.List лічильника буде створювати в схемі з ім’ям G $імя_счетчіка, Функції NextValue і CurrentValue. Причому в залежності від параметра IsWorkWithHoles, реалізації функції NextValue трохи відрізняються.

create trigger [_Generator].[TR_List_UpdateGenerator] on [_Generator].[List]

with execute as owner

after insert, update, delete

as

begin

      set nocount on

      set ansi_nulls on

      declare

            @Name            nvarchar(128),

            @IsWorkWithHoles tinyint,

            @ID              uniqueidentifier,

            @FunctionName    nvarchar(128),

            @sql             nvarchar(maX)

 

      declare cursd cursor local static forward_only for

            select N”G$”+i.Name

            from deleted i

      open cursd

      while 1=1

      begin

            fetch next from cursd into @Name

            if @@FETCH_STATUS <> 0 break

           

            if object_id(@Name+N”.CurrentValue”, N”FN”) is not null

                  exec(N”drop function “+@Name+N”.CurrentValue”)

            if object_id(@Name+N”.NextValue”, N”FN”) is not null

                  exec(N”drop function “+@Name+N”.NextValue”)

            if object_id(@Name+N”.NextValueHole”, N”FN”) is not null

                  exec(N”drop function “+@Name+N”.NextValueHole”)

            — Delete *.RegisterHole stored prcedure, if exists

            if object_id(@Name+N”.RegisterHole”, N”P”) is not null

                  exec(N”drop procedure “+@Name+N”.RegisterHole”)

            — If generator”s schema is “empty” (i.e. it was used only by generator)

            if not exists(select * from sys.all_objects    where schema_id=schema_id(@Name))

                  exec(N”drop schema “+@Name)

      end

 

      declare cursi cursor local static forward_only for

            select i.Name, i.ID, i.IsWorkWithHoles

            from inserted i

      open cursi

      while 1=1

      begin

            fetch next from cursi into @Name, @ID, @IsWorkWithHoles

            if @@FETCH_STATUS <> 0 break

           

            set @FunctionName = “NextValue” + case when @IsWorkWithHoles=1 then “Hole” else “” end

           

            if SCHEMA_ID(@Name) is null

                  exec(N”create schema G$”+@Name)

                 

            if object_id(@Name+N”.CurrentValue”) is null

            begin

                  set @sql = N”create function G$”+@Name+N”.CurrentValue(@Sequence nvarchar(256)) returns int as

begin

      declare @CV int

      select @CV=CurrentValue

      from _Generator.Sequence

      where Name=”””+@Name+N”.””+isnull(“”.””+@Sequence, “”””)

      if @@rowcount = 0

            select

                  @CV = StartValue

            from _Generator.List

            where ID = “””    +convert(varchar(40), @ID)+N”””

      return @CV

end”

                  exec(@sql)

            end

           

            if object_id(@Name+N”.NextValue”) is null

            begin

                  set @sql = N”create function G$”+@Name+N”.NextValue(@Sequence nvarchar(256)) returns int as

begin

      return (_Generator.”+@FunctionName+N”(“””+@Name+”.””+isnull(“”.””+@Sequence, “”””), @@SPID, @@SERVERNAME, DB_NAME()))

end”

                  exec(@sql)

            end

           

           

      end

end

 

Зверніть увагу – тригер створений з опцією with execute as owner, Що дозволяє фактично перетворити операції по вставці даних у таблицю в DDL операції, що розширюють синтаксис в нашій базі і при цьому створює генератор користувач зовсім не повинен мати будь-які права на модифікацію структури БД. Крім того, так мені було зручніше налагоджувати сам механізм 😉

 

Створюємо таблицю, в яку будемо поміщати невикористані значення лічильників:

Схожі статті:


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

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*