1 package FS::part_export::domain_sql;
3 use vars qw(@ISA %info);
7 @ISA = qw(FS::part_export);
9 #quite a bit of false laziness w/acct_sql - some stuff should be generalized
10 #out to a "dababase base class"
12 tie my %options, 'Tie::IxHash',
13 'datasrc' => { label => 'DBI data source' },
14 'username' => { label => 'Database username' },
15 'password' => { label => 'Database password' },
16 'table' => { label => 'Database table' },
17 'schema' => { label =>
18 'Database schema mapping to Freeside methods.',
21 'static' => { label =>
22 'Database schema mapping to static values.',
25 'primary_key' => { label => 'Database primary key' },
28 tie my %postfix_transport_map, 'Tie::IxHash',
31 my $postfix_transport_map =
32 join('\n', map "$_ $postfix_transport_map{$_}",
33 keys %postfix_transport_map );
34 tie my %postfix_transport_static, 'Tie::IxHash',
35 'transport' => 'virtual:',
37 my $postfix_transport_static =
38 join('\n', map "$_ $postfix_transport_static{$_}",
39 keys %postfix_transport_static );
42 'svc' => 'svc_domain',
43 'desc' => 'Real time export of domains to SQL databases '.
45 'options' => \%options,
47 Export domains (svc_domain records) to SQL databases. Currently this is a
48 simple export with a default for Postfix, but it can be extended for other
51 <BR><BR>Use these buttons for useful presets:
53 <LI><INPUT TYPE="button" VALUE="postfix_transport" onClick='
54 this.form.table.value = "transport";
55 this.form.schema.value = "$postfix_transport_map";
56 this.form.static.value = "$postfix_transport_static";
57 this.form.primary_key.value = "domain";
63 sub _schema_map { shift->_map('schema'); }
64 sub _static_map { shift->_map('static'); }
68 map { /^\s*(\S+)\s*(\S+)\s*$/ } split("\n", $self->option(shift) );
72 my($self, $svc_domain) = (shift, shift);
74 my %schema = $self->_schema_map;
75 my %static = $self->_static_map;
77 my %record = ( ( map { $_ => $static{$_} } keys %static ),
78 ( map { my $method = $schema{$_};
79 $_ => $svc_domain->$method();
86 $self->domain_sql_queue(
89 $self->option('table'),
92 return $err_or_queue unless ref($err_or_queue);
98 my($self, $new, $old) = (shift, shift, shift);
100 my %schema = $self->_schema_map;
101 my %static = $self->_static_map;
102 #my %map = (%schema, %static);
104 my @primary_key = ();
105 if ( $self->option('primary_key') =~ /,/ ) {
106 foreach my $key ( split(/\s*,\s*/, $self->option('primary_key') ) ) {
107 my $keymap = $schema{$key};
108 push @primary_key, $old->$keymap();
111 my %map = (%schema, %static);
112 my $keymap = $map{$self->option('primary_key')};
113 push @primary_key, $old->$keymap();
116 my %record = ( ( map { $_ => $static{$_} } keys %static ),
117 ( map { my $method = $schema{$_};
118 $_ => $new->$method();
124 my $err_or_queue = $self->domain_sql_queue(
127 $self->option('table'),
128 $self->option('primary_key'), @primary_key,
131 return $err_or_queue unless ref($err_or_queue);
136 my ( $self, $svc_domain ) = (shift, shift);
138 my %schema = $self->_schema_map;
139 my %static = $self->_static_map;
140 my %map = (%schema, %static);
142 my %primary_key = ();
143 if ( $self->option('primary_key') =~ /,/ ) {
144 foreach my $key ( split(/\s*,\s*/, $self->option('primary_key') ) ) {
145 my $keymap = $map{$key};
146 $primary_key{ $key } = $svc_domain->$keymap();
149 my $keymap = $map{$self->option('primary_key')};
150 $primary_key{ $self->option('primary_key') } = $svc_domain->$keymap(),
153 my $err_or_queue = $self->domain_sql_queue(
156 $self->option('table'),
158 #$self->option('primary_key') => $svc_domain->$keymap(),
160 return $err_or_queue unless ref($err_or_queue);
164 sub domain_sql_queue {
165 my( $self, $svcnum, $method ) = (shift, shift, shift);
166 my $queue = new FS::queue {
168 'job' => "FS::part_export::domain_sql::domain_sql_$method",
171 $self->option('datasrc'),
172 $self->option('username'),
173 $self->option('password'),
178 sub domain_sql_insert { #subroutine, not method
179 my $dbh = domain_sql_connect(shift, shift, shift);
180 my( $table, %record ) = @_;
182 my $sth = $dbh->prepare(
183 "INSERT INTO $table ( ". join(", ", keys %record).
184 " ) VALUES ( ". join(", ", map '?', keys %record ). " )"
185 ) or die $dbh->errstr;
187 $sth->execute( values(%record) )
188 or die "can't insert into $table table: ". $sth->errstr;
193 sub domain_sql_delete { #subroutine, not method
194 my $dbh = domain_sql_connect(shift, shift, shift);
195 my( $table, %record ) = @_;
197 my $sth = $dbh->prepare(
198 "DELETE FROM $table WHERE ". join(' AND ', map "$_ = ? ", keys %record )
199 ) or die $dbh->errstr;
201 $sth->execute( map $record{$_}, keys %record )
202 or die "can't delete from $table table: ". $sth->errstr;
207 sub domain_sql_replace { #subroutine, not method
208 my $dbh = domain_sql_connect(shift, shift, shift);
210 my( $table, $pkey ) = ( shift, shift );
212 my %primary_key = ();
213 if ( $pkey =~ /,/ ) {
214 foreach my $key ( split(/\s*,\s*/, $pkey ) ) {
215 $primary_key{$key} = shift;
218 $primary_key{$pkey} = shift;
223 my $sth = $dbh->prepare(
225 ' SET '. join(', ', map "$_ = ?", keys %record ).
226 ' WHERE '. join(' AND ', map "$_ = ?", keys %primary_key )
227 ) or die $dbh->errstr;
229 $sth->execute( values(%record), values(%primary_key) );
234 sub domain_sql_connect {
235 #my($datasrc, $username, $password) = @_;
236 #DBI->connect($datasrc, $username, $password) or die $DBI::errstr;
237 DBI->connect(@_) or die $DBI::errstr;