Репликация средствами Change Tracking. Небольшое упражнение на FOR XML PATH и XQuery.


Продолжение. Начало - см. пост Репликация таблиц средствами Change Tracking.


 


Пусть имеем накопленные изменения с версии номер 1320.


 


select ct.SYS_CHANGE_VERSION, ct.SYS_CHANGE_CREATION_VERSION, ct.SYS_CHANGE_OPERATION, sct.commit_time, ct.id1, ct.id2, t.fld1, t.fld2  from changetable(changes tbl_1, 1320) ct join sys.dm_tran_commit_table sct on ct.sys_change_version = sct.commit_ts left join tbl_1 t on t.id1 = ct.id1 and t.id2 = ct.id2 order by 1


 








































































































SYS_CHANGE_VERSION


SYS_CHANGE_CREATION_VERSION


SYS_CHANGE_OPERATION


commit_time


id1


id2


fld1


fld2


1322


NULL


D


12/08/2009 13:23:16.963


11


9018645


NULL


NULL


1326


1325


D


12/08/2009 13:23:38.023


14


9979771


NULL


NULL


1328


1324


D


12/08/2009 13:23:47.047


13


9938787


NULL


NULL


1329


1329


I


12/08/2009 13:23:53.060


16


601941


iii


nnn


1332


1327


I


12/08/2009 13:24:06.097


15


354335


kkk


ppp


1333


1331


D


12/08/2009 13:24:09.110


17


878077


NULL


NULL


1335


1321


D


12/08/2009 13:24:18.133


12


9169359


NULL


NULL


1336


1336


I


12/08/2009 13:24:21.143


18


1462335


bbb


yyy


1337


NULL


D


12/08/2009 13:24:23.163


8


7996619


NULL


NULL


1338


NULL


U


12/08/2009 13:24:30.170


10


8995273


yyy


ccc


Скрипт 1


 


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


 


И пусть хотим получить этот результат не в виде рекордсета, а XML вида:


 


<CT_Changes table_name="tbl_1" version_since="1320" version_upto="1338">


  <Record operation="D" change_no="1337" commit_time="2009-08-12T13:24:23.163">


    <PK>


      <id1>8</id1>


      <id2>7996619</id2>


    </PK>


  </Record>


  <Record operation="U" change_no="1338" commit_time="2009-08-12T13:24:30.170">


    <PK>


      <id1>10</id1>


      <id2>8995273</id2>


    </PK>


    <fld1>yyy</fld1>


    <fld2>ccc</fld2>


  </Record>


  <Record operation="I" change_no="1332" commit_time="2009-08-12T13:24:06.097">


    <PK>


      <id1>15</id1>


      <id2>354335</id2>


    </PK>


    <fld1>kkk</fld1>


    <fld2>ppp</fld2>


  </Record>


  <Record operation="I" change_no="1329" commit_time="2009-08-12T13:23:53.060">


    <PK>


      <id1>16</id1>


      <id2>601941</id2>


    </PK>


    <fld1>iii</fld1>


    <fld2>nnn</fld2>


  </Record>


  <Record operation="D" change_no="1333" commit_time="2009-08-12T13:24:09.110">


    <PK>


      <id1>17</id1>


      <id2>878077</id2>


    </PK>


  </Record>


...


</CT_Changes>


Скрипт 2


 


В шапке CT_Changes указывается таблица, изменения по которой смотрим; версия изменений, от которой работала функция changetable; максимальная версия изменений на данный момент. В дочерних элементах Record перечисляются строки результата Скрипт 1. В атрибутах - тип операции (удаление, вставка, обновление), версия этого изменения и время, когда оно случилось. В элементах приводятся значения полей соответствущей записи таблицы-оригинала. Поля, составляющие первичный ключ, выделяются в элемент РК, остальные поля просто перечисляются. В случае удаления остальные поля можно не перечислять, достаточно РК. Для получения такого XML надо написать следующий запрос:


 


declare @n bigint = 1320


select 'tbl_1' as [@table_name], @n as [@version_since], change_tracking_current_version() as [@version_upto],


(


select ct.SYS_CHANGE_OPERATION as [@operation], ct.SYS_CHANGE_VERSION as [@change_no], sct.commit_time as [@commit_time],


ct.id1 as [PK/id1], ct.id2 as [PK/id2], t.fld1 as fld1, t.fld2 as fld2


from changetable(changes tbl_1, 1320) ct


join sys.dm_tran_commit_table sct on ct.sys_change_version = sct.commit_ts


left join tbl_1 t on t.id1 = ct.id1 and t.id2 = ct.id2


for xml path('Record'), type


)


for xml path('CT_Changes')


Скрипт 3


 


Fld1 и fld2 будут NULL в случае SYS_CHANGE_OPERATION = 'D', потому что этой записи уже нет в tbl_1. По умолчанию, в XML они не отразятся, но это и не требуется, т.к. для ее удаления из копии (tbl_2) достаточно иметь значения полей первичного ключа.


Директива type во вложенном select нужна для того, чтобы его результаты воспринимались в виде вложенных элементов XML по отношению к XML, порожденному внешним select'ом. Без нее они будут восприниматься как строки:


 


<CT_Changes table_name="tbl_1" version_since="1320" version_upto="1338">&lt;Record operation="D" change_no="1337" commit_time="2009-08-12T13:24:23.163"&gt;&lt;PK&gt;&lt;id1&gt;8&lt;/id1&gt;&lt;id2&gt;7996619&lt;/id2&gt;&lt;/PK&gt;&lt;/Record&gt;&lt;Record...


 


Решим обратную задачу: XML со Скрипта 2 требуется превратить в табличный вид по типу Скрипт 1.


XQuery, фильтрующий из предыдущего XML операции удаления и возвращающий из них РК удаленных записей:


 


declare @n bigint = 1320


declare @x xml =


(


select 'tbl_1' as [@table_name], @n as [@version_since], change_tracking_current_version() as [@version_upto],


(


select ct.SYS_CHANGE_OPERATION as [@operation], ct.SYS_CHANGE_VERSION as [@change_no], sct.commit_time as [@commit_time],


ct.id1 as [PK/id1], ct.id2 as [PK/id2], t.fld1 as fld1, t.fld2 as fld2


from changetable(changes tbl_1, @n) ct


join sys.dm_tran_commit_table sct on ct.sys_change_version = sct.commit_ts


left join tbl_1 t on t.id1 = ct.id1 and t.id2 = ct.id2


for xml path('Record'), type


)


for xml path('CT_Changes')


)


 


select x.value('(PK/id1)[1]', 'int') id1, x.value('(PK/id2)[1]', 'int') id2 from @x.nodes('CT_Changes/Record[@operation="D"]') d(x)


 


id1   id2


8     7996619


11    9018645


12    9169359


13    9938787


14    9979771


17    878077


Скрипт 4


 


То же для вставки:


 


select x.value('(PK/id1)[1]', 'int') id1, x.value('(PK/id2)[1]', 'int') id2, x.value('fld1[1]', 'nvarchar(10)') fld1, x.value('fld2[1]', 'nvarchar(10)') fld2 from @x.nodes('CT_Changes/Record[@operation="I"]') d(x)


 


id1          id2                          fld1        fld2


15           354335                  kkk         ppp


16           601941                  iii             nnn


18           1462335                bbb        yyy


Скрипт 5


 


И аналогично для обновления.


 


Comments (0)

Skip to main content