/[rserv]/misc/master.sql
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /misc/master.sql

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.3 - (show annotations)
Sat Aug 16 09:41:34 2003 UTC (20 years, 7 months ago) by dpavlin
Branch: MAIN
CVS Tags: before_onlytables, before_multmaster, r_0_3, HEAD
Changes since 1.2: +6 -6 lines
I shouldn't code so late in night... fixes column names

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 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 );
19
20
21 --
22 -- Tables to sync
23 --
24 drop table _RSERV_TABLES_;
25
26 create table _RSERV_TABLES_
27 (
28 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 );
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 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 delete smallint, -- deleted
47 update smallint, -- updated
48 insert smallint, -- inserted
49 key text, -- primary key in table
50 constraint only_one check (insert+update+delete=1)
51 );
52
53 -- This is to speedup lookup deleted, updated and inserted tuples
54 create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (delete, logid)
55 where delete = 1;
56
57 create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ (update, logid)
58 where update = 1;
59
60 create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ (insert, logid)
61 where insert = 1;
62
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 -- 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
82 --
83 drop table _RSERV_SYNC_;
84
85 create table _RSERV_SYNC_
86 (
87 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 );
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 ;

  ViewVC Help
Powered by ViewVC 1.1.26