http://www.devart.com/dotconnect/postgr ... ework.html has a tutorial sample.
Created two databases dbserver and dbclient on same PostgreSQL instance.
Server provisioning is working with this code
Code: Select all
using (var serverConnection = new PgSqlConnection(ConnectionString))
{
var scopeDesc = new DbSyncScopeDescription("ProductsScope");
var tableDesc = PgSqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConnection);
scopeDesc.Tables.Add(tableDesc);
var serverProvision = new PgSqlSyncScopeProvisioning(serverConnection, scopeDesc);
serverProvision.Apply();
}Client side code is straight from tutorial
Code: Select all
using (var serverConn = new PgSqlConnection(ServerConnectionString))
{
using (var clientConn = new PgSqlConnection(ClientConnectionString))
{
var scopeDescription = PgSqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn);
SyncScopeProvisioning productionProvisioning = new PgSqlSyncScopeProvisioning(clientConn, scopeDescription);
productionProvisioning.Apply();
}
}with productionProvisioning.Script() I can see what sql is tried to execute and it contains strange double quotes
Code: Select all
CREATE SEQUENCE "seq_timestamp"
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 1;
CREATE TABLE "scopeinfo" (
scope_local_id SERIAL,
scope_id UUID NOT NULL,
scope_name VARCHAR(100) NOT NULL,
scope_sync_knowledge BYTEA NULL,
scope_tombstone_cleanup_knowledge BYTEA NULL,
scope_timestamp BIGINT NULL,
scope_cleanup_timestamp BIGINT NULL,
scope_user_comment VARCHAR(200) NULL,
scope_config_id UUID NULL,
scope_restore_count INT default 0,
PRIMARY KEY (scope_local_id),
UNIQUE (scope_name)
);
CREATE TABLE "schemainfo" (
schema_major_version INT NOT NULL,
schema_minor_version INT NOT NULL,
schema_extended_info varchar(100) NOT NULL,
PRIMARY KEY (schema_major_version,schema_minor_version)
)
;
INSERT INTO "schemainfo" (schema_major_version, schema_minor_version, schema_extended_info ) VALUES ('2','1','');
CREATE TABLE "scopeconfig" (
config_id UUID NOT NULL,
config_data TEXT,
scope_status char(1) NULL,
PRIMARY KEY (config_id)
);
CREATE TABLE "scope_parameters" (
sync_scope_name VARCHAR(100) NOT NULL,
parameter_data TEXT NOT NULL,
CONSTRAINT PK_scope_parameters PRIMARY KEY (sync_scope_name)
);
CREATE TABLE "scope_templates" (
template_id SERIAL,
template_name VARCHAR(100) NOT NULL,
template_config_id uuid NOT NULL,
template_description VARCHAR(100) NOT NULL,
CONSTRAINT PK_scope_templates PRIMARY KEY (template_id),
UNIQUE (template_name)
);
CREATE FUNCTION "update_scopeinfo" (
p_sync_scope_name varchar,
p_sync_scope_knowledge bytea,
p_sync_scope_cleanup_knowledge bytea,
p_sync_check_concurrency INT,
p_sync_scope_timestamp BIGINT,
OUT sync_row_count int
)
RETURNS int AS $$
BEGIN
update "scopeinfo" set
scope_sync_knowledge = p_sync_scope_knowledge,
scope_tombstone_cleanup_knowledge = p_sync_scope_cleanup_knowledge
where scope_name = p_sync_scope_name
AND (p_sync_check_concurrency = 0 OR scope_timestamp = p_sync_scope_timestamp);
GET DIAGNOSTICS sync_row_count = ROW_COUNT;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION "get_newtimestamp" (
OUT sync_new_timestamp int
)
RETURNS int AS $$
BEGIN
sync_new_timestamp := nextval('"seq_timestamp"');
END;
$$ LANGUAGE plpgsql;
INSERT INTO "scopeconfig" (config_id, config_data, scope_status) VALUES('6a38519d-3847-4061-ad19-799bbdc371ee', '<SyncProviderScopeConfiguration xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" IsTemplate="false"><Adapter DelTrig="""products"_on_delete"" GlobalName="products" InsTrig="""products"_on_insert"" UpdTrig="""products"_on_update"" Name="""products""" TrackingTable="""products"_tracking"" SelChngProc="""products"_selectchanges"" SelRowProc="""products"_selectrow"" InsMetaProc="""products"_insertmetadata"" UpdMetaProc="""products"_updatemetadata"" DelMetaProc="""products"_deletemetadata"" InsProc="""products"_insert"" UpdProc="""products"_update"" DelProc="""products"_delete""><Col name="id" type="int4" prec="32" scale="0" size="-1" pk="true" /><Col name="name" type="varchar" prec="0" scale="0" size="100" null="true" /><Col name="category_id" type="int4" prec="32" scale="0" size="-1" null="true" /><Col name="is_available" type="bool" prec="0" scale="0" size="-1" null="true" /></Adapter></SyncProviderScopeConfiguration>', 'P');
INSERT INTO "scopeinfo" (scope_id, scope_name, scope_config_id, scope_user_comment, scope_timestamp) VALUES('60c5fa02-5ef8-4bdd-b098-60c034dfc659', 'ProductsScope', '6a38519d-3847-4061-ad19-799bbdc371ee', '', nextval('"seq_timestamp"'));
CREATE TABLE [b]""[/b]products[b]""[/b] (
"id" int4 NOT NULL,
"name" varchar(100),
"category_id" int4,
"is_available" bool,
PRIMARY KEY ("id")
);
CREATE TABLE [b]""products"[/b]_tracking" (
"id" int4 NOT NULL,
update_scope_local_id INT NULL,
scope_update_peer_key INT,
scope_update_peer_timestamp BIGINT NULL,
local_update_peer_key INT,
local_update_peer_timestamp BIGINT,
create_scope_local_id INT NULL,
scope_create_peer_key INT,
scope_create_peer_timestamp BIGINT,
local_create_peer_key INT,
local_create_peer_timestamp INT,
sync_row_is_tombstone INT,
restore_timestamp INT,
last_change_datetime TIMESTAMP NULL,
PRIMARY KEY ("id")
);
INSERT INTO [b]""products"_tracking"[/b] ("id", create_scope_local_id, local_create_peer_key, local_create_peer_timestamp, update_scope_local_id, local_update_peer_key, restore_timestamp, sync_row_is_tombstone, local_update_peer_timestamp)
SELECT m."id", null, 0, nextval('"seq_timestamp"'), NULL, 0, NULL, 0, nextval('"seq_timestamp"')
FROM [b]""[/b]products[b]""[/b] m
LEFT OUTER JOIN ""products"_tracking" t ON m."id" = t."id"
WHERE t."id" IS NULL;
CREATE FUNCTION ""products"_on_insert_func"() RETURNS trigger AS $$
BEGIN
UPDATE ""products"_tracking"
SET sync_row_is_tombstone = 0, local_update_peer_key = 0, local_update_peer_timestamp = nextval('"seq_timestamp"'), update_scope_local_id = NULL,
last_change_datetime = 'NOW'
WHERE "id" = new."id";
IF NOT FOUND THEN
INSERT INTO ""products"_tracking" (
"id",
create_scope_local_id,
local_create_peer_key,
local_create_peer_timestamp,
update_scope_local_id,
local_update_peer_key,
local_update_peer_timestamp,
sync_row_is_tombstone,
last_change_datetime)
VALUES (
new."id",
NULL,
0,
nextval('"seq_timestamp"'),
NULL,
0,
nextval('"seq_timestamp"'),
0,
'NOW');
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER ""products"_on_insert" AFTER INSERT ON ""products""
FOR EACH ROW EXECUTE PROCEDURE ""products"_on_insert_func"();
CREATE OR REPLACE FUNCTION ""products"_on_update_func"() RETURNS trigger AS $$
BEGIN
UPDATE ""products"_tracking" SET
update_scope_local_id = NULL, local_update_peer_key = 0,
local_update_peer_timestamp = nextval('"seq_timestamp"'), last_change_datetime = 'NOW'
WHERE "id" = old."id";
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER ""products"_on_update" AFTER UPDATE ON ""products""
FOR EACH ROW EXECUTE PROCEDURE ""products"_on_update_func"();
CREATE OR REPLACE FUNCTION ""products"_on_delete_func"() RETURNS trigger AS $$
BEGIN
UPDATE ""products"_tracking" SET
sync_row_is_tombstone = 1,
update_scope_local_id = NULL,
local_update_peer_key = 0,
local_update_peer_timestamp = nextval('"seq_timestamp"'),
last_change_datetime = 'NOW'
WHERE "id" = old."id";
RETURN OLD;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER ""products"_on_delete" AFTER DELETE ON ""products""
FOR EACH ROW EXECUTE PROCEDURE ""products"_on_delete_func"();
CREATE FUNCTION ""products"_selectchanges" (
p_sync_min_timestamp bigint,
p_sync_metadata_only int,
p_sync_scope_local_id int,
p_sync_initialize int)
RETURNS refcursor AS $$
DECLARE
changes refcursor;
BEGIN
OPEN changes FOR SELECT
t."id",
m."name",
m."category_id",
m."is_available",
t.sync_row_is_tombstone,
t.local_update_peer_timestamp as sync_row_timestamp,
CASE
WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> p_sync_scope_local_id)
THEN
CASE
WHEN (t.restore_timestamp is null) THEN t.local_update_peer_timestamp
ELSE t.restore_timestamp
END
ELSE t.scope_update_peer_timestamp
END AS sync_update_peer_timestamp,
CASE
WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> p_sync_scope_local_id) THEN t.local_update_peer_key
ELSE t.scope_update_peer_key
END AS sync_update_peer_key,
CASE
WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> p_sync_scope_local_id) THEN t.local_create_peer_timestamp
ELSE t.scope_create_peer_timestamp
END AS sync_create_peer_timestamp,
CASE
WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> p_sync_scope_local_id) THEN t.local_create_peer_key
ELSE t.scope_create_peer_key
END AS sync_create_peer_key
FROM ""products"" m
RIGHT JOIN ""products"_tracking" t
ON m."id" = t."id"
WHERE (t.update_scope_local_id is NULL OR t.update_scope_local_id <> p_sync_scope_local_id OR
(t.update_scope_local_id = p_sync_scope_local_id AND t.scope_update_peer_key <> CASE WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> p_sync_scope_local_id)
THEN t.local_update_peer_key ELSE t.scope_update_peer_key END))
AND t.local_update_peer_timestamp > p_sync_min_timestamp;
RETURN changes;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION ""products"_selectrow" (
p_id INT4,
p_sync_scope_local_id INT
)
RETURNS refcursor AS $$
DECLARE
row refcursor;
BEGIN
OPEN row FOR SELECT * FROM (SELECT
t."id",
m."name",
m."category_id",
m."is_available",
t.sync_row_is_tombstone,
t.local_update_peer_timestamp as sync_row_timestamp,
CASE
WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> p_sync_scope_local_id)
THEN
CASE
WHEN (t.restore_timestamp is null) THEN t.local_update_peer_timestamp
ELSE t.restore_timestamp
END
ELSE t.scope_update_peer_timestamp
END AS sync_update_peer_timestamp,
CASE
WHEN (t.update_scope_local_id is null or t.update_scope_local_id <> p_sync_scope_local_id) THEN t.local_update_peer_key
ELSE t.scope_update_peer_key
END AS sync_update_peer_key,
CASE
WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> p_sync_scope_local_id) THEN t.local_create_peer_timestamp
ELSE t.scope_create_peer_timestamp
END AS sync_create_peer_timestamp,
CASE
WHEN (t.create_scope_local_id is null or t.create_scope_local_id <> p_sync_scope_local_id) THEN t.local_create_peer_key
ELSE t.scope_create_peer_key
END AS sync_create_peer_key
FROM ""products"" m
RIGHT JOIN ""products"_tracking" t
ON m."id" = t."id") t
WHERE t."id" = p_id;
RETURN row;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION ""products"_insert" (
p_id INT4,
p_name VARCHAR,
p_category_id INT4,
p_is_available BOOL,
OUT sync_row_count int)
RETURNS int AS $$
DECLARE
rowexist INTEGER;
BEGIN
sync_row_count = 0;
SELECT INTO rowexist COUNT(*) FROM ""products"_tracking" t WHERE t."id" = p_id;
IF rowexist = 0 THEN
INSERT INTO ""products"" ("id", "name", "category_id", "is_available")
VALUES (p_id, p_name, p_category_id, p_is_available);
GET DIAGNOSTICS sync_row_count = ROW_COUNT;
END IF;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION ""products"_update" (
p_id INT4,
p_name VARCHAR,
p_category_id INT4,
p_is_available BOOL,
p_sync_min_timestamp bigint,
p_sync_force_write int,
OUT sync_row_count int
) RETURNS int AS $$
BEGIN
UPDATE ""products""
SET "name" = p_name, "category_id" = p_category_id, "is_available" = p_is_available
WHERE "id" = p_id AND
EXISTS (SELECT * FROM ""products"_tracking" t WHERE (t.local_update_peer_timestamp <= p_sync_min_timestamp OR p_sync_force_write = 1) AND
t."id" = p_id);
GET DIAGNOSTICS sync_row_count = ROW_COUNT;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION ""products"_delete" (
p_id INT4,
p_sync_min_timestamp bigint,
p_sync_force_write int,
OUT sync_row_count int
)
RETURNS int AS $$
BEGIN
DELETE FROM ""products""
WHERE "id" = p_id AND
EXISTS (SELECT * FROM ""products"_tracking" t WHERE (t.local_update_peer_timestamp <= p_sync_min_timestamp OR p_sync_force_write = 1) AND
t."id" = p_id);
GET DIAGNOSTICS sync_row_count = ROW_COUNT;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION ""products"_insertmetadata" (
p_id INT4,
p_sync_scope_local_id int,
p_sync_row_is_tombstone int,
p_sync_create_peer_key int,
p_sync_create_peer_timestamp bigint,
p_sync_update_peer_key INT,
p_sync_update_peer_timestamp bigint,
p_sync_check_concurrency int,
p_sync_row_timestamp bigint,
OUT sync_row_count int)
RETURNS int AS $$
BEGIN
UPDATE ""products"_tracking" SET
create_scope_local_id = p_sync_scope_local_id,
scope_create_peer_key = p_sync_create_peer_key,
scope_create_peer_timestamp = p_sync_create_peer_timestamp,
local_create_peer_key = 0,
local_create_peer_timestamp = nextval('"seq_timestamp"'),
update_scope_local_id = p_sync_scope_local_id,
scope_update_peer_key = p_sync_update_peer_key,
scope_update_peer_timestamp = p_sync_update_peer_timestamp,
local_update_peer_key = 0,
restore_timestamp = NULL,
sync_row_is_tombstone = p_sync_row_is_tombstone
WHERE "id" = p_id
AND (p_sync_check_concurrency = 0 OR local_update_peer_timestamp = p_sync_row_timestamp);
IF NOT FOUND THEN
INSERT into ""products"_tracking"
VALUES (
p_id,
p_sync_scope_local_id,
p_sync_create_peer_key,
p_sync_create_peer_timestamp,
0,
nextval('"seq_timestamp"'),
p_sync_scope_local_id,
p_sync_update_peer_key,
p_sync_update_peer_timestamp,
0,
NULL,
p_sync_row_is_tombstone);
END IF;
GET DIAGNOSTICS sync_row_count = ROW_COUNT;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION ""products"_updatemetadata" (
p_id INT4,
p_sync_scope_local_id int,
p_sync_row_is_tombstone int,
p_sync_create_peer_key int,
p_sync_create_peer_timestamp bigint,
p_sync_update_peer_key int,
p_sync_update_peer_timestamp bigint,
p_sync_row_timestamp bigint,
p_sync_check_concurrency int,
OUT sync_row_count int
)
RETURNS int AS $$
DECLARE
was_tombstone int;
BEGIN
SELECT sync_row_is_tombstone INTO was_tombstone FROM ""products"_tracking" t
WHERE t."id" = p_id;
IF (was_tombstone IS NOT NULL AND was_tombstone=1 AND p_sync_row_is_tombstone=0) THEN
UPDATE ""products"_tracking" SET
update_scope_local_id = p_sync_scope_local_id,
scope_update_peer_key = p_sync_update_peer_key,
scope_update_peer_timestamp = p_sync_update_peer_timestamp,
local_update_peer_key = 0,
restore_timestamp = NULL,
create_scope_local_id = p_sync_scope_local_id,
scope_create_peer_key = p_sync_create_peer_key,
scope_create_peer_timestamp = p_sync_create_peer_timestamp,
sync_row_is_tombstone = p_sync_row_is_tombstone,
local_create_peer_timestamp = nextval('"seq_timestamp"')
WHERE "id" = p_id
AND (p_sync_check_concurrency = 0 OR local_update_peer_timestamp = p_sync_row_timestamp);
ELSE
UPDATE ""products"_tracking" SET
update_scope_local_id = p_sync_scope_local_id,
scope_update_peer_key = p_sync_update_peer_key,
scope_update_peer_timestamp = p_sync_update_peer_timestamp,
local_update_peer_key = 0,
restore_timestamp = NULL,
sync_row_is_tombstone = p_sync_row_is_tombstone
WHERE "id" = p_id
AND (p_sync_check_concurrency = 0 OR local_update_peer_timestamp = p_sync_row_timestamp);
END IF;
GET DIAGNOSTICS sync_row_count = ROW_COUNT;
END;
$$ LANGUAGE plpgsql
;
CREATE FUNCTION ""products"_deletemetadata" (
p_id INT4,
p_sync_row_timestamp bigint,
p_sync_check_concurrency int,
OUT sync_row_count int
)
RETURNS int AS $$
BEGIN
DELETE FROM ""products"_tracking"
WHERE "id" = p_id
AND (p_sync_check_concurrency = 0 OR local_update_peer_timestamp = p_sync_row_timestamp);
GET DIAGNOSTICS sync_row_count = ROW_COUNT;
END;
$$ LANGUAGE plpgsql;
UPDATE "scopeconfig" SET scope_status = 'C' WHERE config_id = '6a38519d-3847-4061-ad19-799bbdc371ee';