/[rserv]/bin/MasterInit
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 /bin/MasterInit

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

revision 1.2 by dpavlin, Tue Aug 5 09:20:08 2003 UTC revision 1.3 by dpavlin, Tue Aug 5 09:52:36 2003 UTC
# Line 9  eval '(exit $?0)' && eval 'exec perl -S Line 9  eval '(exit $?0)' && eval 'exec perl -S
9  use Pg;  use Pg;
10  use Getopt::Long;  use Getopt::Long;
11    
12    $lib = '/usr/lib/postgresql/lib/rserv.so';
13    
14  $| = 1;  $| = 1;
15    
16  $result = GetOptions("debug!", "verbose!", "help",  $result = GetOptions("debug!", "verbose!", "help",
17                       "host=s", "user=s", "password=s");                       "host=s", "user=s", "password=s", "lib=s");
18    
19  my $debug = $opt_debug || 0;  my $debug = $opt_debug || 0;
20  my $verbose = $opt_verbose || 0;  my $verbose = $opt_verbose || 0;
21    
22  if (defined($opt_help) || (scalar(@ARGV) < 1)) {  if (defined($opt_help) || (scalar(@ARGV) < 1)) {
23      print "Usage: $0 --host=name --user=name --password=string masterdb\n";      print "Usage: $0 --host=name --user=name --password=string --lib=libpath masterdb\n";
24      exit ((scalar(@ARGV) < 1)? 1:0);      exit ((scalar(@ARGV) < 1)? 1:0);
25  }  }
26    
 my $module_filename = '$libdir/rserv';  
   
27  my $master = $ARGV[0] || "master";  my $master = $ARGV[0] || "master";
28    
29  my $minfo = "dbname=$master";  my $minfo = "dbname=$master";
# Line 31  $minfo = "$minfo host=$opt_host" if (def Line 31  $minfo = "$minfo host=$opt_host" if (def
31  $minfo = "$minfo user=$opt_user" if (defined($opt_user));  $minfo = "$minfo user=$opt_user" if (defined($opt_user));
32  $minfo = "$minfo password=$opt_password" if (defined($opt_password));  $minfo = "$minfo password=$opt_password" if (defined($opt_password));
33    
34    $lib = $opt_lib if (defined($opt_lib));
35    
36  sub RollbackAndQuit {  sub RollbackAndQuit {
37      $conn = shift @_;      $conn = shift @_;
38    
# Line 53  RollbackAndQuit($conn) if ($result->resu Line 55  RollbackAndQuit($conn) if ($result->resu
55    
56  # List of slave servers  # List of slave servers
57  $result = $conn->exec("create table _RSERV_SERVERS_" .  $result = $conn->exec("create table _RSERV_SERVERS_" .
58                                            " (server serial, host text, post int4, dbase text)");                        " (server serial primary key, host text not ".
59                          "null, port int4 default 5432, dbase text not ".
60                          "null, unique(host,port,dbase))");
61  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
62    
63  # List of replicated tables  # List of replicated tables
64  $result = $conn->exec("create table _RSERV_TABLES_" .  $result = $conn->exec("create table _RSERV_TABLES_" .
65                                            " (tname name, cname name, reloid oid, key int4)");                        " (tname name not null, cname name not null, ".
66                          "reloid oid primary key, key int4 not null)");
67  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
68    
69  # Bookkeeping log for row replication  ## should always call MasterDelTable
70    #$result = $conn->exec("CREATE RULE _rserv_deltrig_ AS ON delete to _RSERV_TABLES_ DO (DELETE FROM pg_trigger WHERE tgname='_rserv_trigger_t_' AND tgrelid=(SELECT oid FROM pg_class WHERE relname=old.tname);UPDATE pg_class SET reltriggers=reltriggers-1 WHERE relname=old.tname)");
71    #RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
72    
73    # Bookkeeping log for row replication  
74  $result = $conn->exec("create table _RSERV_LOG_" .  $result = $conn->exec("create table _RSERV_LOG_" .
75                                            " (reloid oid, logid int4, logtime timestamp, deleted int4, key text)");                        " (reloid oid REFERENCES _RSERV_TABLES_(reloid) ON ".
76                          "DELETE CASCADE ON UPDATE CASCADE, logid int4 not ".
77                          "null, logtime timestamp not null, insert smallint, ".
78                          "update smallint, delete smallint, key text, ".
79                          "CONSTRAINT only_one CHECK (insert+update+delete=1))");
80  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
81    
82  # This is to speedup lookup of deleted tuples  # This is to speedup lookup of deleted tuples
83  $result = $conn->exec("create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ (deleted, logid)");  $result = $conn->exec("create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ ".
84                          "(delete, logid) WHERE delete = 1");
85    RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
86    
87    # This is to speedup lookup of updated tuples
88    $result = $conn->exec("create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ ".
89                          "(update, logid) WHERE update = 1");
90    RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
91    
92    # This is to speedup lookup of insert tuples
93    $result = $conn->exec("create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ ".
94                          "(insert, logid) WHERE insert = 1");
95  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
96    
97  # This is to speedup cleanup  # This is to speedup cleanup
98  $result = $conn->exec("create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ (logtime, logid)");  $result = $conn->exec("create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ ".
99                          "(logtime, logid)");
100    RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
101    
102    # This is to speedup trigger
103    $result = $conn->exec("create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ ".
104                          "(reloid, key)");
105    RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
106    
107    # View to help managing _rserv_log_ table
108    $result = $conn->exec("CREATE VIEW _RSERV_HUMAN_LOG_ AS SELECT log.logid, ".
109                          "tab.tname AS table_name, tab.cname AS column_name, ".
110                          "log.key AS column_value, log.insert, log.update, ".
111                          "log.delete, log.logtime FROM _RSERV_LOG_ log, ".
112                          "_RSERV_TABLES_ tab WHERE tab.reloid ".
113                          "= log.reloid ORDER BY log.logtime");
114  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
115    
116  # This is to speedup trigger and lookup of updated tuples  # View to help logging daily transactions
117  $result = $conn->exec("create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ (reloid, key)");  $result = $conn->exec("CREATE VIEW _RSERV_DAILY_LOG_ AS ".
118                          "SELECT count(*) AS \"# records\", ".
119                          "to_char(_rserv_log_.logtime, 'YYYY-MM-DD') ".
120                          "AS day FROM _rserv_log_ GROUP BY day");
121  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
122    
123  # Sync point for each slave server  # Sync point for each slave server
124  $result = $conn->exec("create table _RSERV_SYNC_" .  $result = $conn->exec("create table _RSERV_SYNC_ " .
125                                            " (server int4, syncid int4, synctime timestamp" .                        "(server int REFERENCES _RSERV_SERVERS_(server) ".
126                                            ", status int4, minid int4, maxid int4, active text)");                        "ON DELETE CASCADE ON UPDATE CASCADE, syncid int4 ".
127                          "not null, synctime timestamp, status int4 not null,".
128                          " minid int4 not null, maxid int4 not null, active text)");
129  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
130    
131  $result = $conn->exec("create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ (server, syncid)");  $result = $conn->exec("create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ ".
132                          "(server, syncid)");
133  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
134    
135  # Sync point reference numbers  # Sync point reference numbers
136  $result = $conn->exec("create sequence _rserv_sync_seq_");  $result = $conn->exec("create sequence _rserv_sync_seq_");
137  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
138    
139  $result = $conn->exec("CREATE FUNCTION _rserv_log_() RETURNS trigger" .  $result = $conn->exec("CREATE FUNCTION _rserv_log_() RETURNS opaque" .
140                                            " AS '$module_filename' LANGUAGE 'c'");                        " AS '$lib' LANGUAGE 'c'");
141  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
142    
143  $result = $conn->exec("CREATE FUNCTION _rserv_sync_(int4) RETURNS int4" .  $result = $conn->exec("CREATE FUNCTION _rserv_sync_(int4) RETURNS int4" .
144                                            " AS '$module_filename' LANGUAGE 'c'");                        " AS '$lib' LANGUAGE 'c'");
145  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
146    
147  $result = $conn->exec("CREATE FUNCTION _rserv_debug_(int4) RETURNS int4" .  $result = $conn->exec("CREATE FUNCTION _rserv_debug_(int4) RETURNS int4" .
148                                            " AS '$module_filename' LANGUAGE 'c'");                        " AS '$lib' LANGUAGE 'c'");
149  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);
150    
151  $result = $conn->exec("COMMIT");  $result = $conn->exec("COMMIT");
Legend:
Removed from v.1.2  
changed lines
  Added in v.1.3
  ViewVC Help
Powered by ViewVC 1.1.26