create table SYNC_DEVICES (
  DEV_ID integer identity,
  DEV_USER_ID integer,   -- references WS.WS.SYS_DAV_USER(U_ID)
  DEV_URI varchar,
  DEV_MAN varchar,       -- manufacturer
  DEV_MOD varchar,       -- model
  DEV_OEM varchar,       -- OEM
  DEV_FWV varchar,       -- firmware version
  DEV_SWV varchar,       -- software version
  DEV_HWV varchar,       -- hardware version
  DEV_DEVID varchar,     -- device id ; must be unique
  DEV_DEVTYP varchar,    -- device type
  DEV_UTC integer,       -- requires datetime in UTC
  DEV_SUPP_LOB integer,  -- supports large objects
  DEV_SUPP_NOC integer,  -- supports "number of changes"
  primary key (DEV_URI)  -- constraint foobar unique(DEV_USER_ID, DEV_URI)
)
;

create table SYNC_MAPS (
  MAP_DEV_ID integer, -- references SYNC_DEVICES(DEV_ID)
  MAP_COL_ID int,     -- references WS.WS.SYS_DAV_COL (COL_ID)
  MAP_LUID varchar,   -- local id (from client's view)
  MAP_GUID varchar,   -- references WS.WS.SYS_DAV_RES (RES_ID)
  primary key (MAP_DEV_ID, MAP_LUID, MAP_GUID)
)
;

create table SYNC_ANCHORS (
  A_COL_ID integer,   -- references WS.WS.SYS_DAV_COL(COL_ID)
  A_DEV_ID integer,   -- references SYNC_DEVICES(DEV_ID)
  A_LAST_LOCAL datetime,    -- last local anchor
  A_LAST_REMOTE varchar,    -- last remote anchor
  A_NEXT_LOCAL datetime,    -- last local anchor
  A_NEXT_REMOTE varchar,    -- last remote anchor
  primary key (A_COL_ID, A_DEV_ID)
)
;

create table SYNC_RPLOG (
  RLOG_RES_ID int,	-- references WS.WS.SYS_DAV_RES (RES_ID)
  RLOG_RES_COL int,	-- references WS.WS.SYS_DAV_COL (COL_ID)
  DMLTYPE varchar, 	-- IUD - insert/update/delete
  SNAPTIME datetime,	-- timestamp
  primary key (RLOG_RES_ID)
)
;

create table SYNC_SESSION
	(
	S_ID varchar,	-- session ID (unique per device)
	S_DEV varchar,	-- device URI
	S_DEV_ID int,	-- references SYNC_DEVICES(DEV_ID)
	S_UID int,	-- user ID SYS_USERS.U_ID
	S_LAST_MSG int,	-- last massage no
	S_LAST_CMD int,	-- last command no
	S_DATA long varbinary,	-- internal use
	S_TS timestamp,		-- last used
	S_AUTH int default 0,	-- authenticated for this session
	S_NONCE varchar default '', -- nonce value
	S_INIT int default 1,	    -- init state flag
	primary key (S_ID, S_DEV)
	)
;