Шаблон провизионирования сервера (не SQL Server)

  • Статья

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

  1. [basetable] — на имя базовой таблицы, например orders;

  2. [trackingtable] — на имя таблицы отслеживания, например orders_tracking;

  3. [bt.allcols] — на список столбцов базовой таблицы (bt), например bt.order_id, bt.order_date;

  4. [bt.pk=tt.pk] — так, чтобы соединить столбцы первичного ключа базовой таблицы (bt) с соответствующими столбцами таблицы отслеживания (tt), например bt.order_id = tt.order_id;

  5. [bt.nonPkCols] — на список столбцов базовой таблицы, не являющихся столбцами первичного ключа, например bt.order_date;

  6. [@allcols_with_datatype] — на список столбцов базовой таблицы как переменных sql вместе с типами данных, например @order\_id int, @order\_date datetime;

  7. [pkcol= @pkcol\] — для сопоставления столбцов первичного ключа соответствующим переменным sql, например order_id = @order\_id;

  8. [allcols] — на список столбцов базовой таблицы, например order_id, order_date;

  9. [@allcols] — на список столбцов базовой таблицы как переменных sql, например @order\_id, @order\_date;

  10. [@pkcols_with_Datatype] — на список столбцов первичного ключа базовой таблицы как переменных sql вместе с типами данных, например @order\_id int;

  11. [set_allcols_except_PK] — так, чтобы присвоить всем столбцам, не являющимся столбцами первичного ключа, значения из соответствующих переменных sql, например order_date = @order\_date;

  12. [tt.pk=@pk] — так, чтобы соединить столбцы первичного ключа таблицы отслеживания (tt) с соответствующими переменными sql, например tt.order_id = @order\_id;

  13. [pkcols] — на список столбцов первичного ключа базовой таблицы, например order_id;

  14. Замените [database] именем базы данных, содержащей базовую таблицу, например peer1

  15. [ScopeTableMap] — на имя таблицы, содержащей сопоставление областей и таблиц, например scope_table_map.

--
-- ========================================================================================================================================================================
--  Template for Creating Stored Procedures for DbSyncAdapter Commands
-- ========================================================================================================================================================================
--

use [database]
go

if object_id(N'dbo.sp_[basetable]_selectchanges', 'P') is not null
    drop procedure dbo.sp_[basetable]_selectchanges

if object_id(N'dbo.sp_[basetable]_applyinsert', 'P') is not null
    drop procedure dbo.sp_[basetable]_applyinsert
    
if object_id(N'dbo.sp_[basetable]_insertmetadata', 'P') is not null
    drop procedure dbo.sp_[basetable]_insertmetadata    

if object_id(N'dbo.sp_[basetable]_applyupdate', 'P') is not null
    drop procedure dbo.sp_[basetable]_applyupdate

if object_id(N'dbo.sp_[basetable]_updatemetadata', 'P') is not null
    drop procedure dbo.sp_[basetable]_updatemetadata

if object_id(N'dbo.sp_[basetable]_applydelete', 'P') is not null
    drop procedure dbo.sp_[basetable]_applydelete

if object_id(N'dbo.sp_[basetable]_deletemetadata', 'P') is not null
    drop procedure dbo.sp_[basetable]_deletemetadata

if object_id(N'dbo.sp_[basetable]_selectrow', 'P') is not null
    drop procedure dbo.sp_[basetable]_selectrow

if object_id(N'dbo.sp_[basetable]_selecttombstones', 'P') is not null
    drop procedure dbo.sp_[basetable]_selecttombstones

if object_id (N'dbo.sp_select_shared_scopes', 'P') is not null
    drop procedure dbo.sp_select_shared_scopes
go

go
--
--  ***********************************************
--     Select Incremental Changes Proc for [basetable]
--  ***********************************************
--
create procedure dbo.sp_[basetable]_selectchanges (
    @sync_min_timestamp bigint,
    @sync_metadata_only int,
    @sync_scope_local_id int
)
as
begin
    select  tt.pkcols,
            bt.nonPkCols, 
            tt.sync_row_is_tombstone,
            tt.local_update_peer_timestamp as sync_row_timestamp, 
            case when (tt.update_scope_local_id is null or tt.update_scope_local_id <> @sync_scope_local_id) 
                 then case when (tt.restore_timestamp is null) then tt.local_update_peer_timestamp else tt.restore_timestamp end else tt.scope_update_peer_timestamp end as sync_update_peer_timestamp,
            case when (tt.update_scope_local_id is null or tt.update_scope_local_id <> @sync_scope_local_id) 
                 then tt.local_update_peer_key else tt.scope_update_peer_key end as sync_update_peer_key,
            case when (tt.create_scope_local_id is null or tt.create_scope_local_id <> @sync_scope_local_id) 
                 then tt.local_create_peer_timestamp else tt.scope_create_peer_timestamp end as sync_create_peer_timestamp,
            case when (tt.create_scope_local_id is null or tt.create_scope_local_id <> @sync_scope_local_id) 
                 then tt.local_create_peer_key else tt.scope_create_peer_key end as sync_create_peer_key
    from [basetable] bt right join [trackingtable] tt on [bt.pk = tt.pk]
    where tt.local_update_peer_timestamp > @sync_min_timestamp
end
go


--
--  ***********************************************
--     Insert Procs for [basetable]
--  ***********************************************
--
create procedure dbo.sp_[basetable]_applyinsert (                        
        @order_id int,
        @order_date datetime,
        @sync_row_count int out)        
as
    if not exists (select * from [trackingtable] tt where [pkcol = @pkcol])
        insert into [basetable] ([allcols]) 
            values ([@allcols])
    set @sync_row_count = @@rowcount
go


create procedure dbo.sp_[basetable]_insertmetadata (
        [@pkcols_with_datatype],
        @sync_scope_local_id int,
        @sync_row_is_tombstone int,
        @sync_create_peer_key int ,
        @sync_create_peer_timestamp bigint,                 
        @sync_update_peer_key int ,
        @sync_update_peer_timestamp timestamp,                              
        @sync_check_concurrency int,    
        @sync_row_timestamp timestamp,  
        @sync_row_count int out)        
as  
begin
update [trackingtable] set 
    [create_scope_local_id] = @sync_scope_local_id, 
    [scope_create_peer_key] = @sync_create_peer_key,
    [scope_create_peer_timestamp] = @sync_create_peer_timestamp,
    [local_create_peer_key] = 0,
    [local_create_peer_timestamp] = @@DBTS+1,
    [update_scope_local_id] = @sync_scope_local_id,
    [scope_update_peer_key] = @sync_update_peer_key,
    [scope_update_peer_timestamp] = @sync_update_peer_timestamp,
    [local_update_peer_key] = 0,
    [restore_timestamp] = NULL,
    [sync_row_is_tombstone] = @sync_row_is_tombstone 
    where [pkcol = @pkcol]
    and (@sync_check_concurrency = 0 or local_update_peer_timestamp = @sync_row_timestamp) 
set @sync_row_count = @@ROWCOUNT
end
go


create procedure dbo.sp_[basetable]_insertmetadata (
        [@pkcols_with_datatype],
        @sync_scope_local_id int,
        @sync_row_is_tombstone int,
        @sync_create_peer_key int ,
        @sync_create_peer_timestamp bigint,                 
        @sync_update_peer_key int ,
        @sync_update_peer_timestamp timestamp,                              
        @sync_check_concurrency int,    
        @sync_row_timestamp timestamp,  
        @sync_row_count int out)        
as  
begin
    update [trackingtable] set 
    [create_scope_local_id] = @sync_scope_local_id, 
    [scope_create_peer_key] = @sync_create_peer_key,
    [scope_create_peer_timestamp] = @sync_create_peer_timestamp,
    [local_create_peer_key] = 0,
    [local_create_peer_timestamp] = @@DBTS+1,
    [update_scope_local_id] = @sync_scope_local_id,
    [scope_update_peer_key] = @sync_update_peer_key,
    [scope_update_peer_timestamp] = @sync_update_peer_timestamp,
    [local_update_peer_key] = 0,
    [restore_timestamp] = NULL,
    [sync_row_is_tombstone] = @sync_row_is_tombstone 
    where [pkcol = @pkcol]
    and (@sync_check_concurrency = 0 or local_update_peer_timestamp = @sync_row_timestamp) 

    set @sync_row_count = @@ROWCOUNT
    
    if (@sync_row_count = 0 )
    begin
        -- inserting metadata for row if it does not already exist
        -- this can happen if a node sees a delete for a row it never had, we insert only metadata
        -- for the row in that case
        insert into [trackingtable] (   
        [pkcols],
        [create_scope_local_id], 
        [scope_create_peer_key],
        [scope_create_peer_timestamp],
        [local_create_peer_key],
        [local_create_peer_timestamp],
        [update_scope_local_id],
        [scope_update_peer_key],
        [scope_update_peer_timestamp],
        [local_update_peer_key],
        [restore_timestamp],
        [sync_row_is_tombstone] )values (
        [@pkcols],
        @sync_scope_local_id, 
        @sync_create_peer_key,
        @sync_create_peer_timestamp,
        0,
        @@DBTS+1,
        @sync_scope_local_id,
        @sync_update_peer_key,
        @sync_update_peer_timestamp,
        0,
        NULL,
        @sync_row_is_tombstone)
        set @sync_row_count = @@ROWCOUNT
    end
end
go


--
--  ***********************************************
--     Update Procs for [basetable]
--  ***********************************************
--

create procedure dbo.sp_[basetable]_applyupdate (                                    
        [@allcols_with_datatype],
        @sync_force_write int,
        @sync_min_timestamp bigint ,                                
        @sync_row_count int out)        
as      
    update [basetable]
    set [set_allcols_except_PK] from [basetable] bt join [trackingtable] tt on [bt.pk = tt.pk]
    where (tt.local_update_peer_timestamp <= @sync_min_timestamp or @sync_force_write = 1)        
        and [tt.pk = @pk]  
    set @sync_row_count = @@rowcount                        
go


create procedure dbo.sp_[basetable]_updatemetadata (
        [@pkcols_with_datatype],
        @sync_scope_local_id int,
        @sync_row_is_tombstone int,
        @sync_create_peer_key int ,
        @sync_create_peer_timestamp bigint,                 
        @sync_update_peer_key int ,
        @sync_update_peer_timestamp timestamp,                      
        @sync_row_timestamp timestamp,
        @sync_check_concurrency int,        
        @sync_row_count int out)        
as          

    declare @was_tombstone int 
    select @was_tombstone = sync_row_is_tombstone from [trackingtable] 
    where [order_id] = @order_id 
    if (@was_tombstone is not null and @was_tombstone=1 and @sync_row_is_tombstone=0) 
        update [trackingtable] set 
            [update_scope_local_id] = @sync_scope_local_id, 
            [scope_update_peer_key] = @sync_update_peer_key,
            [scope_update_peer_timestamp] = @sync_update_peer_timestamp,
            [local_update_peer_key] = 0,
            [restore_timestamp] = NULL,
            [create_scope_local_id] = @sync_scope_local_id, 
            [scope_create_peer_key] = @sync_create_peer_key, 
            [scope_create_peer_timestamp] =  @sync_create_peer_timestamp, 
            [sync_row_is_tombstone] = @sync_row_is_tombstone 
        where [pkcol = @pkcol]
        and (@sync_check_concurrency = 0 or local_update_peer_timestamp = @sync_row_timestamp) 
    else 
        update [trackingtable] set 
            [update_scope_local_id] = @sync_scope_local_id, 
            [scope_update_peer_key] = @sync_update_peer_key,
            [scope_update_peer_timestamp] = @sync_update_peer_timestamp,
            [local_update_peer_key] = 0,
            [restore_timestamp] = NULL,
            [sync_row_is_tombstone] = @sync_row_is_tombstone 
        where [pkcol = @pkcol]
        and (@sync_check_concurrency = 0 or local_update_peer_timestamp = @sync_row_timestamp) 
    set @sync_row_count = @@rowcount
go  

--
--  ***********************************************
--     Delete Procs for [basetable]
--  ***********************************************
--

create procedure dbo.sp_[basetable]_applydelete(
    [@pkcols_with_datatype],
    @sync_min_timestamp bigint ,            
    @sync_force_write int,
    @sync_row_count int out)     
as  
    delete [basetable] from [basetable] bt join [trackingtable] tt on [bt.pk = tt.pk]
    where (tt.local_update_peer_timestamp <= @sync_min_timestamp or @sync_force_write = 1)
        and [tt.pk = @pk]            
    set @sync_row_count = @@rowcount              
go

create procedure dbo.sp_[basetable]_deletemetadata(
    [@pkcols_with_datatype],   
    @sync_row_timestamp timestamp,  
    @sync_check_concurrency int,    
    @sync_row_count int out)    
as    
    -- delete metadata only
    delete tt
    from [trackingtable] tt
    where [tt.pk = @pk] and (@sync_check_concurrency = 0 or local_update_peer_timestamp = @sync_row_timestamp)
    set @sync_row_count = @@rowcount            
go


--
--  ***********************************************
--     Get conflicting row proc
--  ***********************************************
--

create procedure dbo.sp_[basetable]_selectrow
        [@pkcols_with_datatype],
        @sync_scope_local_id int
as

select      [tt.pk],
            [bt.nonPkCols], 
            tt.sync_row_is_tombstone,
            tt.local_update_peer_timestamp as sync_row_timestamp, 
            case when (tt.update_scope_local_id is null or tt.update_scope_local_id <> @sync_scope_local_id) 
                 then case when (tt.restore_timestamp is null) then tt.local_update_peer_timestamp else tt.restore_timestamp end else tt.scope_update_peer_timestamp end as sync_update_peer_timestamp,
            case when (tt.update_scope_local_id is null or tt.update_scope_local_id <> @sync_scope_local_id) 
                 then tt.local_update_peer_key else tt.scope_update_peer_key end as sync_update_peer_key,
            case when (tt.create_scope_local_id is null or tt.create_scope_local_id <> @sync_scope_local_id) 
                 then tt.local_create_peer_timestamp else tt.scope_create_peer_timestamp end as sync_create_peer_timestamp,
            case when (tt.create_scope_local_id is null or tt.create_scope_local_id <> @sync_scope_local_id) 
                 then tt.local_create_peer_key else tt.scope_create_peer_key end as sync_create_peer_key
    from [basetable] bt right join [trackingtable] tt on [bt.pk = tt.pk]    
    where [tt.pk = @pk]
go
--
--  ***********************************************
--     Get tombstones for cleanup proc
--  ***********************************************
--


create procedure dbo.sp_[basetable]_selecttombstones     
    @tombstone_aging_in_hours int,
    @sync_scope_local_id int
    
as
    select  [pkcols],
    local_update_peer_timestamp as sync_row_timestamp,  
    case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
        then case when (restore_timestamp is null) then local_update_peer_timestamp else restore_timestamp end else scope_update_peer_timestamp end as sync_update_peer_timestamp,
        case when (update_scope_local_id is null or update_scope_local_id <> @sync_scope_local_id) 
        then local_update_peer_key else scope_update_peer_key end as sync_update_peer_key,
        case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
        then local_create_peer_timestamp else scope_create_peer_timestamp end as sync_create_peer_timestamp,
        case when (create_scope_local_id is null or create_scope_local_id <> @sync_scope_local_id) 
        then local_create_peer_key else scope_create_peer_key end as sync_create_peer_key           
    from [trackingtable] 
    where sync_row_is_tombstone=1 
    and DATEDIFF(hh, last_change_datetime, GetDate()) > @tombstone_aging_in_hours go

create procedure dbo.sp_select_shared_scopes
        @sync_scope_name nvarchar(100)      
as
    select  scopeTableMap2.table_name as sync_table_name, 
            scopeTableMap2.scope_name as sync_shared_scope_name
    from [ScopeTableMap] scopeTableMap1 join [ScopeTableMap] scopeTableMap2
    on scopeTableMap1.table_name = scopeTableMap2.table_name
    and scopeTableMap1.scope_name = @sync_scope_name
    where scopeTableMap2.scope_name <> @sync_scope_name
go

См. также

Другие ресурсы

Как подготовить серверную базу данных для синхронизации совместной работы (не SQL Server)