rserv/misc/master.sql

-- 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'
;
1	dpavlin	1.1	-- erServer
2			-- Master server setup for erServer demonstration implementation
3			-- (c) 2000 Vadim Mikheev, PostgreSQL Inc.
4			--
5
6			--
7			-- Slave servers
8			--
9			drop table _RSERV_SERVERS_;
10
11			create table _RSERV_SERVERS_
12			(
13	dpavlin	1.2	server serial primary key, -- slave server id
14			host text not null, -- server' host
15			port int4 default 5432, -- server' port
16			dbase text not null, -- db name
17			unique(host,port,dbase)
18	dpavlin	1.1	);
19
20
21			--
22			-- Tables to sync
23			--
24			drop table _RSERV_TABLES_;
25
26			create table _RSERV_TABLES_
27			(
28	dpavlin	1.2	tname name not null, -- table name
29			cname name not null, -- column name
30			reloid oid primary key, -- table oid
31			key int4 not null -- key attnum
32	dpavlin	1.1	);
33
34
35			--
36			-- Log for inserts/updates/deletes to sync-ed tables
37			--
38			drop table _RSERV_LOG_;
39
40			create table _RSERV_LOG_
41			(
42	dpavlin	1.2	reloid oid references _RSERV_TABLES_(reloid)
43			on delete cascade on update cascade,
44			logid int4 not null, -- xid of last update xaction
45			logtime timestamp not null, -- last update xaction start time
46	dpavlin	1.3	delete smallint, -- deleted
47			update smallint, -- updated
48			insert smallint, -- inserted
49	dpavlin	1.2	key text, -- primary key in table
50			constraint only_one check (insert+update+delete=1)
51	dpavlin	1.1	);
52
53	dpavlin	1.2	-- This is to speedup lookup deleted, updated and inserted tuples
54	dpavlin	1.3	create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (delete, logid)
55	dpavlin	1.2	where delete = 1;
56
57	dpavlin	1.3	create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ (update, logid)
58	dpavlin	1.2	where update = 1;
59
60	dpavlin	1.3	create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ (insert, logid)
61	dpavlin	1.2	where insert = 1;
62	dpavlin	1.1
63			-- This is to speedup cleanup
64			create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid);
65
66			-- This is to speedup trigger and lookup updated tuples
67			create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ (reloid, key);
68
69	dpavlin	1.2	-- View to help managing _rserv_log_ table
70			create view _RSERV_HUMAN_LOG_ as
71			select log.logid, tab.tname as table_name, tab.cname as column_name,
72			log.key as column_value, log.insert, log.update, log.delete,
73			log.logtime from _RSERV_LOG_ log, _RSERV_TABLES_ tab
74			where tab.reloid = log.reloid order by log.logtime;
75
76			-- View to help logging daily transactions
77			create view _RSERV_DAILY_LOG_ as
78			select count(*) AS "# records",
79			to_char(_rserv_log_.logtime, 'YYYY-MM-DD') as day
80			from _rserv_log_ group by day;
81	dpavlin	1.1
82			--
83			drop table _RSERV_SYNC_;
84
85			create table _RSERV_SYNC_
86			(
87	dpavlin	1.2	server int4 references _RSERV_SERVERS_(server)
88			on delete cascade on update cascade,
89			syncid int4 not null, -- from _rserv_sync_seq_
90			synctime timestamp,
91			status int4 not null, -- prepared (0) \| applied
92			minid int4 not null, -- min xid from serializable snapshot
93			maxid int4 not null, -- max xid from serializable snapshot
94			active text -- list of active xactions
95	dpavlin	1.1	);
96
97			create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ (server, syncid);
98
99			drop sequence _rserv_sync_seq_;
100			create sequence _rserv_sync_seq_;
101
102			drop function _rserv_log_();
103
104			CREATE FUNCTION _rserv_log_()
105			RETURNS opaque
106			AS '_OBJWD_/rserv_DLSUFFIX_'
107			LANGUAGE 'c'
108			;
109
110			drop function _rserv_sync_(int4);
111
112			CREATE FUNCTION _rserv_sync_(int4)
113			RETURNS int4
114			AS '_OBJWD_/rserv_DLSUFFIX_'
115			LANGUAGE 'c'
116			;
117
118			drop function _rserv_debug_(int4);
119
120			CREATE FUNCTION _rserv_debug_(int4)
121			RETURNS int4
122			AS '_OBJWD_/rserv_DLSUFFIX_'
123			LANGUAGE 'c'
124			;