-- erServer
-- Master server setup for erServer demonstration implementation
-- (c) 2000 Vadim Mikheev, PostgreSQL Inc.
--

--
-- Slave servers
--
drop table _RSERV_SERVERS_;

create table _RSERV_SERVERS_
(
	server	serial primary key,	-- slave server id
	host	text not null,		-- server' host
	port	int4 default 5432,	-- server' port
	dbase	text not null,		-- db name
	unique(host,port,dbase)
);


--
-- Tables to sync
--
drop table _RSERV_TABLES_;

create table _RSERV_TABLES_
(
	tname	name not null,		-- table name
	cname	name not null,		-- column name
	reloid	oid primary key,	-- table oid
	key	int4 not null		-- key attnum 
);


--
-- Log for inserts/updates/deletes to sync-ed tables
--
drop table _RSERV_LOG_;

create table _RSERV_LOG_
(
	reloid	oid references _RSERV_TABLES_(reloid)
		on delete cascade on update cascade,
	logid	int4 not null,	-- xid of last update xaction
	logtime	timestamp not null, -- last update xaction start time
	delete	smallint,	-- deleted
	update	smallint,	-- updated
	insert	smallint,	-- inserted
	key	text,		-- primary key in table
	constraint only_one check (insert+update+delete=1)
);

-- This is to speedup lookup deleted, updated and inserted tuples
create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (delete, logid)
	where delete = 1;

create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ (update, logid)
	where update = 1;

create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ (insert, logid)
	where insert = 1;

-- This is to speedup cleanup
create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid);

-- This is to speedup trigger and lookup updated tuples
create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ (reloid, key);

-- View to help managing _rserv_log_ table
create view _RSERV_HUMAN_LOG_ as
	select log.logid, tab.tname as table_name, tab.cname as column_name,
		log.key as column_value, log.insert, log.update, log.delete,
		log.logtime from _RSERV_LOG_ log, _RSERV_TABLES_ tab
		where tab.reloid = log.reloid order by log.logtime;

-- View to help logging daily transactions
create view _RSERV_DAILY_LOG_ as
	select count(*) AS "# records",
		to_char(_rserv_log_.logtime, 'YYYY-MM-DD') as day
		from _rserv_log_ group by day;

--
drop table _RSERV_SYNC_;

create table _RSERV_SYNC_
(
	server	int4 references _RSERV_SERVERS_(server)
			on delete cascade on update cascade,
	syncid	int4 not null,	-- from _rserv_sync_seq_
	synctime timestamp,
	status	int4 not null,	-- prepared (0) | applied
	minid	int4 not null,	-- min xid from serializable snapshot
	maxid	int4 not null,	-- max xid from serializable snapshot
	active	text		-- list of active xactions
);

create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ (server, syncid);

drop sequence _rserv_sync_seq_;
create sequence _rserv_sync_seq_;

drop function _rserv_log_();

CREATE FUNCTION _rserv_log_()
	RETURNS opaque
	AS '_OBJWD_/rserv_DLSUFFIX_'
	LANGUAGE 'c'
;

drop function _rserv_sync_(int4);

CREATE FUNCTION _rserv_sync_(int4)
	RETURNS int4
	AS '_OBJWD_/rserv_DLSUFFIX_'
	LANGUAGE 'c'
;

drop function _rserv_debug_(int4);

CREATE FUNCTION _rserv_debug_(int4)
	RETURNS int4
	AS '_OBJWD_/rserv_DLSUFFIX_'
	LANGUAGE 'c'
;
