1 package FS::part_export::sql_Common;
2 use base qw( FS::part_export );
8 tie my %options, 'Tie::IxHash',
9 'datasrc' => { label => 'DBI data source' },
10 'username' => { label => 'Database username' },
11 'password' => { label => 'Database password' },
12 'table' => { label => 'Database table' },
13 'schema' => { label =>
14 'Database schema mapping to Freeside methods.',
17 'static' => { label =>
18 'Database schema mapping to static values.',
21 'primary_key' => { label => 'Database primary key' },
27 sub _schema_map { shift->_map('schema'); }
28 sub _static_map { shift->_map('static'); }
32 map { /^\s*(\S+)\s*(\S+)\s*$/ } split("\n", $self->option(shift) );
35 sub _map_arg_callback {
39 sub rebless { shift; }
42 my($self, $svc_x) = (shift, shift);
44 my %schema = $self->_schema_map;
45 my %static = $self->_static_map;
49 ( map { $_ => $static{$_} } keys %static ),
51 ( map { my $value = $schema{$_};
52 my @arg = $self->_map_arg_callback($value);
53 $_ => $svc_x->$value(@arg);
60 $self->sql_Common_queue(
63 $self->option('table'),
66 return $err_or_queue unless ref($err_or_queue);
73 my($self, $new, $old) = (shift, shift, shift);
75 my %schema = $self->_schema_map;
76 my %static = $self->_static_map;
79 if ( $self->option('primary_key') =~ /,/ ) {
80 foreach my $key ( split(/\s*,\s*/, $self->option('primary_key') ) ) {
81 my $keymap = $schema{$key};
82 push @primary_key, $old->$keymap();
85 my $keymap = $schema{$self->option('primary_key')};
86 push @primary_key, $old->$keymap();
91 ( map { $_ => $static{$_} } keys %static ),
93 ( map { my $value = $schema{$_};
94 my @arg = $self->_map_arg_callback($value);
95 $_ => $new->$value(@arg);
101 my $err_or_queue = $self->sql_Common_queue(
104 $self->option('table'),
105 $self->option('primary_key'), @primary_key,
108 return $err_or_queue unless ref($err_or_queue);
113 my ( $self, $svc_x ) = (shift, shift);
115 my %schema = $self->_schema_map;
117 my %primary_key = ();
118 if ( $self->option('primary_key') =~ /,/ ) {
119 foreach my $key ( split(/\s*,\s*/, $self->option('primary_key') ) ) {
120 my $keymap = $schema{$key};
121 $primary_key{ $key } = $svc_x->$keymap();
124 my $keymap = $schema{$self->option('primary_key')};
125 $primary_key{ $self->option('primary_key') } = $svc_x->$keymap(),
128 my $err_or_queue = $self->sql_Common_queue(
131 $self->option('table'),
133 #$self->option('primary_key') => $svc_x->$keymap(),
135 return $err_or_queue unless ref($err_or_queue);
139 sub sql_Common_queue {
140 my( $self, $svcnum, $method ) = (shift, shift, shift);
141 my $queue = new FS::queue {
143 'job' => "FS::part_export::sql_Common::sql_Common_$method",
146 $self->option('datasrc'),
147 $self->option('username'),
148 $self->option('password'),
153 sub sql_Common_insert { #subroutine, not method
154 my $dbh = sql_Common_connect(shift, shift, shift);
155 my( $table, %record ) = @_;
157 my $sth = $dbh->prepare(
158 "INSERT INTO $table ( ". join(", ", keys %record).
159 " ) VALUES ( ". join(", ", map '?', keys %record ). " )"
160 ) or die $dbh->errstr;
162 $sth->execute( values(%record) )
163 or die "can't insert into $table table: ". $sth->errstr;
168 sub sql_Common_delete { #subroutine, not method
169 my $dbh = sql_Common_connect(shift, shift, shift);
170 my( $table, %record ) = @_;
172 my $sth = $dbh->prepare(
173 "DELETE FROM $table WHERE ". join(' AND ', map "$_ = ? ", keys %record )
174 ) or die $dbh->errstr;
176 $sth->execute( map $record{$_}, keys %record )
177 or die "can't delete from $table table: ". $sth->errstr;
182 sub sql_Common_replace { #subroutine, not method
183 my $dbh = sql_Common_connect(shift, shift, shift);
185 my( $table, $pkey ) = ( shift, shift );
187 my %primary_key = ();
188 if ( $pkey =~ /,/ ) {
189 foreach my $key ( split(/\s*,\s*/, $pkey ) ) {
190 $primary_key{$key} = shift;
193 $primary_key{$pkey} = shift;
198 my $sth = $dbh->prepare(
200 ' SET '. join(', ', map "$_ = ?", keys %record ).
201 ' WHERE '. join(' AND ', map "$_ = ?", keys %primary_key )
202 ) or die $dbh->errstr;
204 $sth->execute( values(%record), values(%primary_key) );
209 sub sql_Common_connect {
210 #my($datasrc, $username, $password) = @_;
211 #DBI->connect($datasrc, $username, $password) or die $DBI::errstr;
212 FS::DBI->connect(@_) or die $FS::DBI::errstr;