/[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.8 by dpavlin, Wed Oct 29 18:00:18 2003 UTC revision 1.9 by dpavlin, Thu Oct 30 19:58:51 2003 UTC
# Line 15  $| = 1; Line 15  $| = 1;
15  my ($debug,$verbose) = (0,0);  my ($debug,$verbose) = (0,0);
16  my ($help,$masterhost,$masterport,$masteruser,$masterpassword);  my ($help,$masterhost,$masterport,$masteruser,$masterpassword);
17  my $lib;  my $lib;
 my $mserver = 0;  
18    
19  my $result = GetOptions(  my $result = GetOptions(
20          "debug!" => \$debug, "verbose!" => \$verbose, "help" => \$help,          "debug!" => \$debug, "verbose!" => \$verbose, "help" => \$help,
21          "masterhost=s" => \$masterhost, "masterport=i" => \$masterport,          "masterhost=s" => \$masterhost, "masterport=i" => \$masterport,
22          "masteruser=s" => \$masteruser, "masterpassword=s" => \$masterpassword,          "masteruser=s" => \$masteruser, "masterpassword=s" => \$masterpassword,
23          "lib=s" => \$lib,          "lib=s" => \$lib,
         "masterserver=i" => \$mserver,  
24  );  );
25    
26  if (defined($help) || (scalar(@ARGV) < 1)) {  if (defined($help) || (scalar(@ARGV) < 1)) {
# Line 73  sub RollbackAndQuit { Line 71  sub RollbackAndQuit {
71      exit (-1);      exit (-1);
72  }  }
73    
74  my $conn = Pg::connectdb($minfo);  my $mconn = Pg::connectdb($minfo);
75  if ($conn->status != PGRES_CONNECTION_OK) {  if ($mconn->status != PGRES_CONNECTION_OK) {
76      print STDERR "Failed opening $minfo\n";      print STDERR "Failed opening $minfo\n";
77      exit 1;      exit 1;
78  }  }
79    
80  $result = $conn->exec("BEGIN");  $result = $mconn->exec("BEGIN");
81  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
82    
83  $result = $conn->exec("set transaction isolation level serializable");  $result = $mconn->exec("set transaction isolation level serializable");
84  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
85    
86  # List of slave servers  # List of slave servers
87  $result = $conn->exec("create table _RSERV_SERVERS_" .  $result = $mconn->exec("create table _RSERV_SERVERS_" .
88                        " (server serial primary key, host text not ".                        " (server serial primary key, host text not ".
89                        "null, port int4 default 5432, dbase text not ".                        "null, port int4 default 5432, dbase text not ".
90                        "null, unique(host,port,dbase))");                        "null, unique(host,port,dbase))");
91  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
92    
93  # insert master server in list  # insert master server in list
94  $result = $conn->exec("insert into _RSERV_SERVERS_" .  $result = $mconn->exec("insert into _RSERV_SERVERS_" .
95                        " (server, host, port, dbase) ".                        " (server, host, port, dbase) ".
96                        " values ($mserver,".                        " values (0,".
97                        ($masterhost || "'localhost'").",".                        ($masterhost || "'localhost'").",".
98                        ($masterport || 5432).",".                        ($masterport || 5432).",".
99                        "'$master')");                        "'$master')");
100  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
101    
102  # List of replicated tables  # List of replicated tables
103  $result = $conn->exec("create table _RSERV_TABLES_" .  $result = $mconn->exec("create table _RSERV_TABLES_" .
104                        " (tname name not null, cname name not null, ".                        " (tname name not null, cname name not null, ".
105                        "reloid oid primary key, key int4 not null)");                        "reloid oid primary key, key int4 not null)");
106  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
107    
108  ## should always call MasterDelTable  ## should always call MasterDelTable
109  #$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)");  #$result = $mconn->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)");
110  #RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  #RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
111    
112  # Bookkeeping log for row replication    # Bookkeeping log for row replication  
113  $result = $conn->exec("create table _RSERV_LOG_" .  $result = $mconn->exec("create table _RSERV_LOG_" .
114                        " (reloid oid REFERENCES _RSERV_TABLES_(reloid) ON ".                        " (reloid oid REFERENCES _RSERV_TABLES_(reloid) ON ".
115                        "DELETE CASCADE ON UPDATE CASCADE, logid int4 not ".                        "DELETE CASCADE ON UPDATE CASCADE, logid int4 not ".
116                        "null, logtime timestamp not null, insert smallint, ".                        "null, logtime timestamp not null, insert smallint, ".
117                        "update smallint, delete smallint, key text, ".                        "update smallint, delete smallint, key text, ".
118                        "server int4, ".                        "server int4, ".
119                        "CONSTRAINT only_one CHECK (insert+update+delete=1))");                        "CONSTRAINT only_one CHECK (insert+update+delete=1))");
120  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
121    
122  # This is to speedup lookup of deleted tuples  # This is to speedup lookup of deleted tuples
123  $result = $conn->exec("create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ ".  $result = $mconn->exec("create index _RSERV_LOG_INDX_DLT_ID_ on _RSERV_LOG_ ".
124                        "(delete, logid) WHERE delete = 1");                        "(delete, logid) WHERE delete = 1");
125  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
126    
127  # This is to speedup lookup of updated tuples  # This is to speedup lookup of updated tuples
128  $result = $conn->exec("create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ ".  $result = $mconn->exec("create index _RSERV_LOG_INDX_UPD_ID_ on _RSERV_LOG_ ".
129                        "(update, logid) WHERE update = 1");                        "(update, logid) WHERE update = 1");
130  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
131    
132  # This is to speedup lookup of insert tuples  # This is to speedup lookup of insert tuples
133  $result = $conn->exec("create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ ".  $result = $mconn->exec("create index _RSERV_LOG_INDX_INS_ID_ on _RSERV_LOG_ ".
134                        "(insert, logid) WHERE insert = 1");                        "(insert, logid) WHERE insert = 1");
135  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
136    
137  # This is to speedup cleanup  # This is to speedup cleanup
138  $result = $conn->exec("create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ ".  $result = $mconn->exec("create index _RSERV_LOG_INDX_TM_ID_ on _RSERV_LOG_ ".
139                        "(logtime, logid)");                        "(logtime, logid)");
140  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
141    
142  # This is to speedup trigger  # This is to speedup trigger
143  $result = $conn->exec("create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ ".  $result = $mconn->exec("create index _RSERV_LOG_INDX_REL_KEY_ on _RSERV_LOG_ ".
144                        "(reloid, key)");                        "(reloid, key)");
145  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
146    
147  # View to help managing _rserv_log_ table  # View to help managing _rserv_log_ table
148  $result = $conn->exec("CREATE VIEW _RSERV_HUMAN_LOG_ AS SELECT log.logid, ".  $result = $mconn->exec("CREATE VIEW _RSERV_HUMAN_LOG_ AS SELECT log.logid, ".
149                        "tab.tname AS table_name, tab.cname AS column_name, ".                        "tab.tname AS table_name, tab.cname AS column_name, ".
150                        "log.key AS column_value, log.insert, log.update, ".                        "log.key AS column_value, log.insert, log.update, ".
151                        "log.delete, log.logtime FROM _RSERV_LOG_ log, ".                        "log.delete, log.logtime FROM _RSERV_LOG_ log, ".
152                        "_RSERV_TABLES_ tab WHERE tab.reloid ".                        "_RSERV_TABLES_ tab WHERE tab.reloid ".
153                        "= log.reloid ORDER BY log.logtime");                        "= log.reloid ORDER BY log.logtime");
154  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
155    
156  # View to help logging daily transactions  # View to help logging daily transactions
157  $result = $conn->exec("CREATE VIEW _RSERV_DAILY_LOG_ AS ".  $result = $mconn->exec("CREATE VIEW _RSERV_DAILY_LOG_ AS ".
158                        "SELECT count(*) AS \"# records\", ".                        "SELECT count(*) AS \"# records\", ".
159                        "to_char(_rserv_log_.logtime, 'YYYY-MM-DD') ".                        "to_char(_rserv_log_.logtime, 'YYYY-MM-DD') ".
160                        "AS day FROM _rserv_log_ GROUP BY day");                        "AS day FROM _rserv_log_ GROUP BY day");
161  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
162    
163  # Sync point for each slave server  # Sync point for each slave server
164  $result = $conn->exec("create table _RSERV_SYNC_ " .  $result = $mconn->exec("create table _RSERV_SYNC_ " .
165                        "(server int REFERENCES _RSERV_SERVERS_(server) ".                        "(server int REFERENCES _RSERV_SERVERS_(server) ".
166                        "ON DELETE CASCADE ON UPDATE CASCADE, syncid int4 ".                        "ON DELETE CASCADE ON UPDATE CASCADE, syncid int4 ".
167                        "not null, synctime timestamp, status int4 not null,".                        "not null, synctime timestamp, status int4 not null,".
168                        " minid int4 not null, maxid int4 not null, active text)");                        " minid int4 not null, maxid int4 not null, active text)");
169  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
170    
171  $result = $conn->exec("create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ ".  $result = $mconn->exec("create index _RSERV_SYNC_INDX_SRV_ID_ on _RSERV_SYNC_ ".
172                        "(server, syncid)");                        "(server, syncid)");
173  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
174    
175  # Sync point reference numbers  # Sync point reference numbers
176  $result = $conn->exec("create sequence _rserv_sync_seq_");  $result = $mconn->exec("create sequence _rserv_sync_seq_");
177  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
178    
179  $result = $conn->exec("CREATE FUNCTION _rserv_log_() RETURNS opaque" .  $result = $mconn->exec("CREATE FUNCTION _rserv_log_() RETURNS opaque" .
180                        " AS '$lib' LANGUAGE 'c'");                        " AS '$lib' LANGUAGE 'c'");
181  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
182    
183  $result = $conn->exec("CREATE FUNCTION _rserv_sync_(int4) RETURNS int4" .  $result = $mconn->exec("CREATE FUNCTION _rserv_sync_(int4) RETURNS int4" .
184                        " AS '$lib' LANGUAGE 'c'");                        " AS '$lib' LANGUAGE 'c'");
185  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
186    
187  $result = $conn->exec("CREATE FUNCTION _rserv_debug_(int4) RETURNS int4" .  $result = $mconn->exec("CREATE FUNCTION _rserv_debug_(int4) RETURNS int4" .
188                        " AS '$lib' LANGUAGE 'c'");                        " AS '$lib' LANGUAGE 'c'");
189  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
190    
191  $result = $conn->exec("COMMIT");  $result = $mconn->exec("COMMIT");
192  RollbackAndQuit($conn) if ($result->resultStatus ne PGRES_COMMAND_OK);  RollbackAndQuit($mconn) if ($result->resultStatus ne PGRES_COMMAND_OK);
193    
194  exit (0);  exit (0);
Legend:
Removed from v.1.8  
changed lines
  Added in v.1.9
  ViewVC Help
Powered by ViewVC 1.1.26