/[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

Diff of /misc/master.sql

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1.1.1.1 by dpavlin, Wed Dec 20 17:22:35 2000 UTC revision 1.3 by dpavlin, Sat Aug 16 09:41:34 2003 UTC
# Line 10  drop table _RSERV_SERVERS_; Line 10  drop table _RSERV_SERVERS_;
10    
11  create table _RSERV_SERVERS_  create table _RSERV_SERVERS_
12  (  (
13          server  int4,   -- slave server id          server  serial primary key,     -- slave server id
14          host    text,   -- server' host          host    text not null,          -- server' host
15          port    int4,   -- server' port          port    int4 default 5432,      -- server' port
16          dbase   text    -- db name          dbase   text not null,          -- db name
17            unique(host,port,dbase)
18  );  );
19    
20    
# Line 24  drop table _RSERV_TABLES_; Line 25  drop table _RSERV_TABLES_;
25    
26  create table _RSERV_TABLES_  create table _RSERV_TABLES_
27  (  (
28          tname  name,    -- table name          tname   name not null,          -- table name
29          cname  name,    -- column name          cname   name not null,          -- column name
30          reloid  oid,    -- table oid          reloid  oid primary key,        -- table oid
31          key             int4    -- key attnum          key     int4 not null           -- key attnum
32  );  );
33    
34    
# Line 38  drop table _RSERV_LOG_; Line 39  drop table _RSERV_LOG_;
39    
40  create table _RSERV_LOG_  create table _RSERV_LOG_
41  (  (
42          reloid          oid,          reloid  oid references _RSERV_TABLES_(reloid)
43          logid           int4,           -- xid of last update xaction                  on delete cascade on update cascade,
44          logtime         timestamp,      -- last update xaction start time          logid   int4 not null,  -- xid of last update xaction
45          deleted         int4,           -- deleted or inserted/updated          logtime timestamp not null, -- last update xaction start time
46          key                     text            --          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 tuples  -- This is to speedup lookup deleted, updated and inserted tuples
54  create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (deleted, logid);  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  -- This is to speedup cleanup
64  create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid);  create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid);
# Line 54  create index _RSERV_LOG_INDX_TM_ID_ on _ Line 66  create index _RSERV_LOG_INDX_TM_ID_ on _
66  -- This is to speedup trigger and lookup updated tuples  -- This is to speedup trigger and lookup updated tuples
67  create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ (reloid, key);  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  --  --
 -- How much each slave servers are sync-ed  
 --  
83  drop table _RSERV_SYNC_;  drop table _RSERV_SYNC_;
84    
85  create table _RSERV_SYNC_  create table _RSERV_SYNC_
86  (  (
87          server          int4,          server  int4 references _RSERV_SERVERS_(server)
88          syncid          int4,           -- from _rserv_sync_seq_                          on delete cascade on update cascade,
89          synctime        timestamp,      --          syncid  int4 not null,  -- from _rserv_sync_seq_
90          status          int4,           -- prepared (0) | applied          synctime timestamp,
91          minid           int4,           -- min xid from serializable snapshot          status  int4 not null,  -- prepared (0) | applied
92          maxid           int4,           -- max xid from serializable snapshot          minid   int4 not null,  -- min xid from serializable snapshot
93          active          text            -- list of active xactions          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);  create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ (server, syncid);
Legend:
Removed from v.1.1.1.1  
changed lines
  Added in v.1.3
  ViewVC Help
Powered by ViewVC 1.1.26