EXEC sp_configure 'show advanced options',1;
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries',1;
RECONFIGURE

CREATE DATABASE r12Sync;
GO
USE r12Sync
SELECT *
INTO Produkty
FROM OPENDATASOURCE('SQLNCLI','Data Source=.;Integrated Security=SSPI').AdventureWorks2008.Production.Product;

ALTER TABLE dbo.Produkty ADD CONSTRAINT
PK_Produkty PRIMARY KEY CLUSTERED (ProductID);

CREATE DATABASE r12SyncCT;
GO
ALTER DATABASE r12SyncCT 
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
ALTER DATABASE r12SyncCT
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

USE r12SyncCT
CREATE TABLE Zamowienia
(id INT NOT NULL PRIMARY KEY, 
data DATETIME NULL);
GO
ALTER TABLE Zamowienia
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

INSERT INTO Zamowienia (id, data ) 
VALUES(1, GetDate()), (2, GetDate());
GO
DELETE Zamowienia
WHERE id=2;

SELECT z.*, c.SYS_CHANGE_VERSION, c.SYS_CHANGE_OPERATION, c.SYS_CHANGE_CONTEXT, c.id 
FROM CHANGETABLE (CHANGES Zamowienia, NULL) AS c
LEFT OUTER JOIN Zamowienia AS z
ON Z.ID = c.ID;

DECLARE @klientID VARBINARY = 0x4F;
WITH CHANGE_TRACKING_CONTEXT (@klientID)
INSERT Zamowienia (id, data) 
VALUES (3,GETDATE());
GO
SELECT SYS_CHANGE_CONTEXT, id 
FROM CHANGETABLE (CHANGES Zamowienia, NULL) AS c
WHERE id = 3;
GO

CREATE PROCEDURE dbo.spGetIncInserts(
@klientID UNIQUEIDENTIFIER, @ostatniaSynch BIGINT)  
AS
SELECT z.id, z.data
FROM Zamowienia AS z
JOIN CHANGETABLE(CHANGES Zamowienia, @ostatniaSynch) AS c 
ON c.id = z.id
WHERE c.SYS_CHANGE_OPERATION = N'I'
AND ISNULL(c.SYS_CHANGE_CONTEXT,0) <> @klientID
ORDER BY c.id;
GO

CREATE PROCEDURE dbo.spApplyInsert (
@klientID UNIQUEIDENTIFIER , @liczbaWierszy INT OUT, @id INT = NULL , @data DATETIME = NULL)        
AS
DECLARE @convKlientID VARBINARY(128) = CONVERT(VARBINARY(128), @klientID);
WITH CHANGE_TRACKING_CONTEXT(@convKlientID)
INSERT INTO Zamowienia (id, data) 
VALUES (@id, @data);
SET @liczbaWierszy = @@ROWCOUNT;    
GO

CREATE PROCEDURE dbo.spApplyUpdate (
@ostatniaSynch  BIGINT, @klientID UNIQUEIDENTIFIER , @liczbaWierszy INT OUT, @id INT = NULL, @data DATETIME = NULL)
AS
DECLARE @convKlientID VARBINARY(128) = CONVERT(VARBINARY(128), @klientID);
WITH CHANGE_TRACKING_CONTEXT (@convKlientID)
UPDATE z
SET z.data = @data
FROM Zamowienia AS z JOIN CHANGETABLE(CHANGES Zamowienia, 0) AS c ON z.id = c.id
WHERE c.id = @id AND c.SYS_CHANGE_VERSION <= @ostatniaSynch;
SET @liczbaWierszy = @@ROWCOUNT;
GO

CREATE PROCEDURE dbo.spApplyDelete (
@ostatniaSynch  BIGINT , @klientID UNIQUEIDENTIFIER , @liczbaWierszy INT OUT, @id INT = NULL , @data DATETIME = NULL)
AS
DECLARE @convKlientID VARBINARY(128) = CONVERT(VARBINARY(128), @klientID);
WITH CHANGE_TRACKING_CONTEXT (@convKlientID)
DELETE z      
FROM Zamowienia AS z JOIN CHANGETABLE(CHANGES Zamowienia, 0) AS c 
ON z.id = c.id
WHERE c.id = @id AND c.SYS_CHANGE_VERSION <= @ostatniaSynch;
SET @liczbaWierszy = @@ROWCOUNT
GO

CREATE PROCEDURE dbo.spGetUpdateConflict
@id INT
AS
SELECT id, data 
FROM Zamowienia
WHERE id = @id;
GO

CREATE PROCEDURE dbo.spGetDeleteConflict
@id INT
AS
SELECT c.id
FROM CHANGETABLE(CHANGES Zamowienia, 0) AS c 
WHERE c.SYS_CHANGE_OPERATION = N'D' AND id = @id
ORDER BY c.id;


