1 package FS::part_export::sql_Common;
2 use base qw( FS::part_export );
7 tie my %options, 'Tie::IxHash',
8 'datasrc' => { label => 'DBI data source' },
9 'username' => { label => 'Database username' },
10 'password' => { label => 'Database password' },
11 'table' => { label => 'Database table' },
12 'schema' => { label =>
13 'Database schema mapping to Freeside methods.',
16 'static' => { label =>
17 'Database schema mapping to static values.',
20 'primary_key' => { label => 'Database primary key' },
26 sub _schema_map { shift->_map('schema'); }
27 sub _static_map { shift->_map('static'); }
31 map { /^\s*(\S+)\s*(\S+)\s*$/ } split("\n", $self->option(shift) );
34 sub _map_arg_callback {
38 sub rebless { shift; }
41 my($self, $svc_x) = (shift, shift);
43 my %schema = $self->_schema_map;
44 my %static = $self->_static_map;
48 ( map { $_ => $static{$_} } keys %static ),
50 ( map { my $value = $schema{$_};
51 my @arg = $self->_map_arg_callback($value);
52 $_ => $svc_x->$value(@arg);
59 $self->sql_Common_queue(
62 $self->option('table'),
65 return $err_or_queue unless ref($err_or_queue);
72 my($self, $new, $old) = (shift, shift, shift);
74 my %schema = $self->_schema_map;
75 my %static = $self->_static_map;
78 if ( $self->option('primary_key') =~ /,/ ) {
79 foreach my $key ( split(/\s*,\s*/, $self->option('primary_key') ) ) {
80 my $keymap = $schema{$key};
81 push @primary_key, $old->$keymap();
84 my $keymap = $schema{$self->option('primary_key')};
85 push @primary_key, $old->$keymap();
90 ( map { $_ => $static{$_} } keys %static ),
92 ( map { my $value = $schema{$_};
93 my @arg = $self->_map_arg_callback($value);
94 $_ => $new->$value(@arg);
100 my $err_or_queue = $self->sql_Common_queue(
103 $self->option('table'),
104 $self->option('primary_key'), @primary_key,
107 return $err_or_queue unless ref($err_or_queue);
112 my ( $self, $svc_x ) = (shift, shift);
114 my %schema = $self->_schema_map;
116 my %primary_key = ();
117 if ( $self->option('primary_key') =~ /,/ ) {
118 foreach my $key ( split(/\s*,\s*/, $self->option('primary_key') ) ) {
119 my $keymap = $schema{$key};
120 $primary_key{ $key } = $svc_x->$keymap();
123 my $keymap = $schema{$self->option('primary_key')};
124 $primary_key{ $self->option('primary_key') } = $svc_x->$keymap(),
127 my $err_or_queue = $self->sql_Common_queue(
130 $self->option('table'),
132 #$self->option('primary_key') => $svc_x->$keymap(),
134 return $err_or_queue unless ref($err_or_queue);
138 sub sql_Common_queue {
139 my( $self, $svcnum, $method ) = (shift, shift, shift);
140 my $queue = new FS::queue {
142 'job' => "FS::part_export::sql_Common::sql_Common_$method",
145 $self->option('datasrc'),
146 $self->option('username'),
147 $self->option('password'),
152 sub sql_Common_insert { #subroutine, not method
153 my $dbh = sql_Common_connect(shift, shift, shift);
154 my( $table, %record ) = @_;
156 my $sth = $dbh->prepare(
157 "INSERT INTO $table ( ". join(", ", keys %record).
158 " ) VALUES ( ". join(", ", map '?', keys %record ). " )"
159 ) or die $dbh->errstr;
161 $sth->execute( values(%record) )
162 or die "can't insert into $table table: ". $sth->errstr;
167 sub sql_Common_delete { #subroutine, not method
168 my $dbh = sql_Common_connect(shift, shift, shift);
169 my( $table, %record ) = @_;
171 my $sth = $dbh->prepare(
172 "DELETE FROM $table WHERE ". join(' AND ', map "$_ = ? ", keys %record )
173 ) or die $dbh->errstr;
175 $sth->execute( map $record{$_}, keys %record )
176 or die "can't delete from $table table: ". $sth->errstr;
181 sub sql_Common_replace { #subroutine, not method
182 my $dbh = sql_Common_connect(shift, shift, shift);
184 my( $table, $pkey ) = ( shift, shift );
186 my %primary_key = ();
187 if ( $pkey =~ /,/ ) {
188 foreach my $key ( split(/\s*,\s*/, $pkey ) ) {
189 $primary_key{$key} = shift;
192 $primary_key{$pkey} = shift;
197 my $sth = $dbh->prepare(
199 ' SET '. join(', ', map "$_ = ?", keys %record ).
200 ' WHERE '. join(' AND ', map "$_ = ?", keys %primary_key )
201 ) or die $dbh->errstr;
203 $sth->execute( values(%record), values(%primary_key) );
208 sub sql_Common_connect {
209 #my($datasrc, $username, $password) = @_;
210 #DBI->connect($datasrc, $username, $password) or die $DBI::errstr;
211 DBI->connect(@_) or die $DBI::errstr;