--- misc/master.sql	2000/12/20 17:22:35	1.1
+++ misc/master.sql	2003/08/15 23:01:36	1.2
@@ -10,10 +10,11 @@
 
 create table _RSERV_SERVERS_
 (
-	server	int4,	-- slave server id
-	host	text,	-- server' host
-	port	int4,	-- server' port
-	dbase	text	-- db name
+	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)
 );
 
 
@@ -24,10 +25,10 @@
 
 create table _RSERV_TABLES_
 (
-	tname  name,	-- table name
-	cname  name,	-- column name
-	reloid	oid,	-- table oid
-	key		int4	-- key attnum 
+	tname	name not null,		-- table name
+	cname	name not null,		-- column name
+	reloid	oid primary key,	-- table oid
+	key	int4 not null		-- key attnum 
 );
 
 
@@ -38,15 +39,26 @@
 
 create table _RSERV_LOG_
 (
-	reloid		oid,
-	logid		int4,		-- xid of last update xaction
-	logtime		timestamp,	-- last update xaction start time
-	deleted		int4,		-- deleted or inserted/updated
-	key			text		-- 
+	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
+	deleted	smallint,	-- deleted or inserted/updated
+	update	smallint,	-- deleted or inserted/updated
+	delete	smallint,	-- deleted or inserted/updated
+	key	text,		-- primary key in table
+	constraint only_one check (insert+update+delete=1)
 );
 
--- This is to speedup lookup deleted tuples
-create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (deleted, logid);
+-- This is to speedup lookup deleted, updated and inserted tuples
+create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (deleted, logid)
+	where delete = 1;
+
+create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ (deleted, logid)
+	where update = 1;
+
+create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ (deleted, logid)
+	where insert = 1;
 
 -- This is to speedup cleanup
 create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid);
@@ -54,21 +66,32 @@
 -- 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;
 
 --
--- How much each slave servers are sync-ed
---
 drop table _RSERV_SYNC_;
 
 create table _RSERV_SYNC_
 (
-	server		int4,
-	syncid		int4,		-- from _rserv_sync_seq_
-	synctime	timestamp,	-- 
-	status		int4,		-- prepared (0) | applied
-	minid		int4,		-- min xid from serializable snapshot
-	maxid		int4,		-- max xid from serializable snapshot
-	active		text		-- list of active xactions
+	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);