3 ## radsqlrelay.pl This program tails a SQL logfile and forwards
4 ## the queries to a database server. Used to
5 ## replicate accounting records to one (central)
6 ## database, even if the database has extended
11 ## Author: Nicolas Baradakis <nicolas.baradakis@cegetel.net>
13 ## Copyright (C) 2005 Cegetel
15 ## This program is free software; you can redistribute it and/or
16 ## modify it under the terms of the GNU General Public License
17 ## as published by the Free Software Foundation; either version 2
18 ## of the License, or (at your option) any later version.
20 ## This program is distributed in the hope that it will be useful,
21 ## but WITHOUT ANY WARRANTY; without even the implied warranty of
22 ## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
23 ## GNU General Public License for more details.
25 ## You should have received a copy of the GNU General Public License
26 ## along with this program; if not, write to the Free Software
27 ## Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA
33 use POSIX qw(:unistd_h :errno_h);
38 my $maxcollect = 100; # tunable, works for MySQL!
50 # /!\ OS-dependent structure
57 # c2ph says: typedef='s2 l2 i', sizeof=16
58 my $FLOCK_STRUCT = 's2l2i';
62 my ($fh, $start, $len) = @_;
63 $start = 0 unless defined $start;
64 $len = 0 unless defined $len;
66 #type whence start till pid
67 my $packed = pack($FLOCK_STRUCT, F_WRLCK, SEEK_SET, $start, $len, 0);
68 if (fcntl($fh, F_SETLKW, $packed)) { return 1 }
75 usage: radsqlrelay [options] file_path
77 -? Print this help message.
78 -1 One-shot mode: push the file to database and exit.
79 -b database Name of the database to use.
80 -d sql_driver Driver to use: mysql, pg, oracle.
81 -f file Read password from file, instead of command line.
82 -h host Connect to host.
83 -P port Port number to use for connection.
84 -p passord Password to use when connecting to server.
85 -u user User for login.
95 $dbh = DBI->connect($dbinfo->{base}, $dbinfo->{user}, $dbinfo->{pass},
96 { RaiseError => 0, PrintError => 0,
101 $dbinfo->{handle} = $dbh;
108 my ($dbinfo, $path) = @_;
112 if (scalar(@values) > 0) {
113 my $query = $lastinsert . " ";
114 $query .= join(" ), ( ",@values);
116 do_query($dbinfo,$query);
122 my ($dbinfo,$query) = @_;
123 until ($dbinfo->{handle}->do($query)) {
124 print $dbinfo->{handle}->errstr."\n";
125 if ($dbinfo->{handle}->ping) {
128 print "error: Lost connection to database\n";
129 $dbinfo->{handle}->disconnect;
130 connect_wait($dbinfo);
135 unless (-e $path.'.work') {
136 until (rename($path, $path.'.work')) {
139 return if $need_exit;
141 print STDERR "error: Couldn't move $path to $path.work: $!\n";
147 open(FILE, "+< $path.work") or die "error: Couldn't open $path.work: $!\n";
148 setlock(\*FILE) or die "error: Couldn't lock $path.work: $!\n";
154 chomp (my $line = $_);
156 if (!($line =~ /^\s*insert\s+into\s+`?\w+`?\s+(?:\(.*?\)\s+)?
157 values\s*\(.*\)\s*;\s*$/ix)) {
158 # This is no INSERT, so start new collection
161 # must output this line
162 do_query($dbinfo, "$line");
165 # This is an INSERT, so collect it
168 $insert =~ s/^\s*(insert\s+into\s+`?\w+`?\s+(?:\(.*?\)\s+)?
169 values\s*\().*\)\s*;\s*$/$1/ix;
170 $values =~ s/^\s*insert\s+into\s+`?\w+`?\s+(?:\(.*?\)\s+)?
171 values\s*\((.*)\)\s*;\s*$/$1/ix;
173 if (($lastinsert ne "") && ($insert ne $lastinsert)) {
174 # This is different from the last one
177 push(@values, $values);
178 $lastinsert = $insert; # start new collection
181 # limit to $maxcollect collected lines
182 if (scalar(@values) >= $maxcollect) {
190 unlink($path.'.work');
191 close(FILE); # and unlock
203 my $ret = getopts("b:d:f:h:P:p:u:x1?", \%args);
204 if (!$ret or @ARGV != 1) {
214 if (lc($args{d}) eq 'mysql') {
215 $data_source = "DBI:mysql:database=$args{b};host=$args{h}";
216 } elsif (lc($args{d}) eq 'pg') {
217 $data_source = "DBI:Pg:dbname=$args{b};host=$args{h}";
218 } elsif (lc($args{d}) eq 'oracle') {
219 $data_source = "DBI:Oracle:$args{b}";
220 # Oracle does not conform to the SQL standard for multirow INSERTs
223 print STDERR "error: SQL driver not supported yet: $args{d}\n";
226 $data_source .= ";port=$args{P}" if $args{'P'};
230 open(FILE, "< $args{f}") or die "error: Couldn't open $args{f}: $!\n";
235 # args{p} is always defined.
239 $SIG{INT} = \&got_signal;
240 $SIG{TERM} = \&got_signal;
243 base => $data_source,
247 connect_wait(\%dbinfo);
249 my $path = shift @ARGV;
252 process_file(\%dbinfo, $path);
253 last if ($args{1} || $need_exit);
257 $dbinfo{handle}->disconnect;