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,
31 my $postfix_transport_map =
32 join('\n', map "$_ $postfix_transport_map{$_}",
33 keys %postfix_transport_map );
34 tie my %postfix_transport_static,
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 { $_ => $svc_domain->$_() } keys %schema );
81 $self->domain_sql_queue(
84 $self->option('table'),
87 return $err_or_queue unless ref($err_or_queue);
93 my($self, $new, $old) = (shift, shift, shift);
95 my %schema = $self->_schema_map;
96 my %static = $self->_static_map;
99 if ( $self->option('primary_key') =~ /,/ ) {
100 foreach my $key ( split(/\s*,\s*/, $self->option('primary_key') ) ) {
101 my $keymap = $schema{$key};
102 push @primary_key, $old->$keymap();
105 my $keymap = $map{$self->option('primary_key')};
106 push @primary_key, $old->$keymap();
109 my %record = ( map { $_ => $static{$_} } keys %static ),
110 ( map { $_ => $svc_domain->$_() } keys %schema );
112 my $err_or_queue = $self->domain_sql_queue(
115 $self->option('table'),
116 $self->option('primary_key'), @primary_key,
119 return $err_or_queue unless ref($err_or_queue);
124 my ( $self, $svc_domain ) = (shift, shift);
126 my %schema = $self->_schema_map;
127 my %static = $self->_static_map;
129 my %primary_key = ();
130 if ( $self->option('primary_key') =~ /,/ ) {
131 foreach my $key ( split(/\s*,\s*/, $self->option('primary_key') ) ) {
132 my $keymap = $map{$key};
133 $primary_key{ $key } = $svc_domain->$keymap();
136 my $keymap = $map{$self->option('primary_key')};
137 $primary_key{ $self->option('primary_key') } = $svc_domain->$keymap(),
140 my $err_or_queue = $self->domain_sql_queue(
143 $self->option('table'),
145 #$self->option('primary_key') => $svc_domain->$keymap(),
147 return $err_or_queue unless ref($err_or_queue);
151 sub domain_sql_queue {
152 my( $self, $svcnum, $method ) = (shift, shift, shift);
153 my $queue = new FS::queue {
155 'job' => "FS::part_export::domain_sql::domain_sql_$method",
158 $self->option('datasrc'),
159 $self->option('username'),
160 $self->option('password'),
165 sub domain_sql_insert { #subroutine, not method
166 my $dbh = domain_sql_connect(shift, shift, shift);
167 my( $table, %record ) = @_;
169 my $sth = $dbh->prepare(
170 "INSERT INTO $table ( ". join(", ", keys %record).
171 " ) VALUES ( ". join(", ", map '?', keys %record ). " )"
172 ) or die $dbh->errstr;
174 $sth->execute( values(%record) )
175 or die "can't insert into $table table: ". $sth->errstr;
180 sub domain_sql_delete { #subroutine, not method
181 my $dbh = domain_sql_connect(shift, shift, shift);
182 my( $table, %record ) = @_;
184 my $sth = $dbh->prepare(
185 "DELETE FROM $table WHERE ". join(' AND ', map "$_ = ? ", keys %record )
186 ) or die $dbh->errstr;
188 $sth->execute( map $record{$_}, keys %record )
189 or die "can't delete from $table table: ". $sth->errstr;
194 sub domain_sql_replace { #subroutine, not method
195 my $dbh = domain_sql_connect(shift, shift, shift);
197 my( $table, $pkey ) = ( shift, shift );
199 my %primary_key = ();
200 if ( $pkey =~ /,/ ) {
201 foreach my $key ( split(/\s*,\s*/, $pkey ) ) {
202 $primary_key{$key} = shift;
205 $primary_key{$pkey} = shift;
210 my $sth = $dbh->prepare(
212 ' SET '. join(', ', map "$_ = ?", keys %record ).
213 ' WHERE '. join(' AND ', map "$_ = ?", keys %primary_key )
214 ) or die $dbh->errstr;
216 $sth->execute( values(%record), values(%primary_key) );
221 sub domain_sql_connect {
222 #my($datasrc, $username, $password) = @_;
223 #DBI->connect($datasrc, $username, $password) or die $DBI::errstr;
224 DBI->connect(@_) or die $DBI::errstr;