/[cvs]/nfo/perl/libs/Data/Storage/Handler/DBI.pm
ViewVC logotype

Diff of /nfo/perl/libs/Data/Storage/Handler/DBI.pm

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1.2 by joko, Fri Oct 25 11:43:27 2002 UTC revision 1.16 by joko, Fri Apr 11 01:17:18 2003 UTC
# Line 3  Line 3 
3  #  $Id$  #  $Id$
4  #  #
5  #  $Log$  #  $Log$
6    #  Revision 1.16  2003/04/11 01:17:18  joko
7    #  sendQuery:
8    #  + introduced crud action 'DELETE'
9    #  + some pre-flight checks
10    #
11    #  Revision 1.15  2003/04/09 07:53:33  joko
12    #  minor namespace update
13    #
14    #  Revision 1.14  2003/04/09 06:06:04  joko
15    #  sendQuery now is capable of doing 'SELECT'- or 'INSERT'-queries
16    #
17    #  Revision 1.13  2003/04/08 23:06:45  joko
18    #  renamed core database helper functions
19    #
20    #  Revision 1.12  2003/01/30 22:28:21  joko
21    #  + implemented new concrete methods
22    #
23    #  Revision 1.11  2002/12/19 16:31:05  joko
24    #  + sub dropDb
25    #  + sub rebuildDb
26    #
27    #  Revision 1.10  2002/12/15 02:02:22  joko
28    #  + fixed logging-message
29    #
30    #  Revision 1.9  2002/12/05 07:58:20  joko
31    #  + now using Tie::SecureHash as a base for the COREHANDLE
32    #  + former public COREHANDLE becomes private _COREHANDLE now
33    #
34    #  Revision 1.8  2002/12/01 22:20:43  joko
35    #  + sub createDb (from Storage.pm)
36    #
37    #  Revision 1.7  2002/12/01 07:09:09  joko
38    #  + sub getListFiltered (dummy redirecting to getListUnfiltered)
39    #
40    #  Revision 1.6  2002/12/01 04:46:01  joko
41    #  + sub eraseAll
42    #
43    #  Revision 1.5  2002/11/29 05:00:26  joko
44    #  + sub getListUnfiltered
45    #  + sub sendQuery
46    #
47    #  Revision 1.4  2002/11/17 08:46:42  jonen
48    #  + wrapped eval around DBI->connect to prevent deaths
49    #
50    #  Revision 1.3  2002/11/17 06:34:39  joko
51    #  + locator metadata can now be reached via ->{locator}
52    #  - sub hash2sql now taken from libdb
53    #
54  #  Revision 1.2  2002/10/25 11:43:27  joko  #  Revision 1.2  2002/10/25 11:43:27  joko
55  #  + enhanced robustness  #  + enhanced robustness
56  #  + more logging for debug-levels  #  + more logging for debug-levels
# Line 20  use warnings; Line 68  use warnings;
68    
69  use base ("Data::Storage::Handler::Abstract");  use base ("Data::Storage::Handler::Abstract");
70    
71    
72  use DBI;  use DBI;
73    use Data::Dumper;
74    use shortcuts::database qw( hash2sql dsn2dbname );
75    use Data::Storage::Result::DBI;
76    
77    
78  # get logger instance  # get logger instance
79  my $logger = Log::Dispatch::Config->instance;  my $logger = Log::Dispatch::Config->instance;
80    
81    
82  our $metainfo = {  sub getMetaInfo {
83    'disconnectMethod' => 'disconnect',    my $self = shift;
84  };    $logger->debug( __PACKAGE__ . "->getMetaInfo()"  );
85      return {
86        'disconnectMethod' => 'disconnect',
87      };
88    }
89    
90  sub connect {  sub connect {
91    
92      my $self = shift;      my $self = shift;
93            
94      # create handle      # create handle
95        if ( my $dsn = $self->{dbi}->{dsn} ) {        if ( my $dsn = $self->{locator}->{dbi}->{dsn} ) {
96          #if ( my $dsn = $self->{locator}->{dsn} ) {
97          $logger->debug( __PACKAGE__ . "->connect( dsn $dsn )" );          $logger->debug( __PACKAGE__ . "->connect( dsn $dsn )" );
98                    
99          # HACK:          # HACK:
# Line 45  sub connect { Line 103  sub connect {
103                    
104          #use Data::Dumper; print Dumper($self->{dbi});          #use Data::Dumper; print Dumper($self->{dbi});
105                    
106          $self->{COREHANDLE} = DBI->connect(          eval {
107            $dsn, '', '', {            $self->{_COREHANDLE} = DBI->connect( $dsn, '', '', $self->{locator}->{dbi} );
108              RaiseError => $self->{dbi}->{RaiseError},            if (!$self->{_COREHANDLE}) {
109              #RaiseError => 1,              $logger->warning( __PACKAGE__ . "->connect failed: " . DBI::errstr );
110              PrintError => $self->{dbi}->{PrintError},              return;
             HandleError => $self->{dbi}->{HandleError},  
111            }            }
112          );          };
113          if (!$self->{COREHANDLE}) {          $logger->warning( __PACKAGE__ . "->connect failed: " . $@ ) if $@;
114            $logger->warning( __PACKAGE__ . "->connect failed: " . DBI::errstr );  
           return;  
         }  
115        }        }
116        $self->configureCOREHANDLE();        $self->configureCOREHANDLE();
117        
118        $self->{locator}->{status}->{connected} = 1;
119    
120      return 1;      return 1;
121            
122  }  }
# Line 68  sub configureCOREHANDLE { Line 125  sub configureCOREHANDLE {
125    
126    my $self = shift;    my $self = shift;
127    
128    $logger->debug( __PACKAGE__ . "->_configureCOREHANDLE" );    $logger->debug( __PACKAGE__ . "->configureCOREHANDLE" );
129    
130      return if !$self->{_COREHANDLE};
131    
132    # apply configured modifications    # apply configured modifications to DBI-handle
133      if (exists $self->{dbi}->{trace_level} && exists $self->{dbi}->{trace_file}) {      if (exists $self->{locator}->{dbi}->{trace_level} && exists $self->{locator}->{dbi}->{trace_file}) {
134        $self->{COREHANDLE}->trace($self->{dbi}->{trace_level}, $self->{dbi}->{trace_file});        $self->{_COREHANDLE}->trace($self->{locator}->{dbi}->{trace_level}, $self->{locator}->{dbi}->{trace_file});
135      }      }
136      if (exists $self->{dbi}->{RaiseError}) {      if (exists $self->{locator}->{dbi}->{RaiseError}) {
137        $self->{COREHANDLE}->{RaiseError} = $self->{dbi}->{RaiseError};        $self->{_COREHANDLE}->{RaiseError} = $self->{locator}->{dbi}->{RaiseError};
138      }      }
139      if (exists $self->{dbi}->{PrintError}) {      if (exists $self->{locator}->{dbi}->{PrintError}) {
140        $self->{COREHANDLE}->{PrintError} = $self->{dbi}->{PrintError};        $self->{_COREHANDLE}->{PrintError} = $self->{locator}->{dbi}->{PrintError};
141      }      }
142      if (exists $self->{dbi}->{HandleError}) {      if (exists $self->{locator}->{dbi}->{HandleError}) {
143        $self->{COREHANDLE}->{HandleError} = $self->{dbi}->{HandleError};        $self->{_COREHANDLE}->{HandleError} = $self->{locator}->{dbi}->{HandleError};
144      }      }
145    
146  }  }
# Line 90  sub _sendSql { Line 149  sub _sendSql {
149    my $self = shift;    my $self = shift;
150    my $sql = shift;    my $sql = shift;
151        
152      # pre-flight check: is $sql defined?
153      if (!$sql) {
154        $logger->warning( __PACKAGE__ . "->_sendSql: \$sql was empty." );
155        return;
156      }
157      
158    # two-level handling for implicit connect:    # two-level handling for implicit connect:
159    # if there's no corehandle ...    # if there's no corehandle ...
160    if (!$self->{COREHANDLE}) {    if (!$self->{_COREHANDLE}) {
161      # ... try to connect, but ...      # ... try to connect, but ...
162      $self->connect();      $self->connect();
163      # ... if this still fails, there's something wrong probably, so we won't continue      # ... if this still fails, there's something wrong probably, so we won't continue
164      if (!$self->{COREHANDLE}) {      if (!$self->{_COREHANDLE}) {
165        return;        return;
166      }      }
167    }    }
168        
169    my $sth = $self->{COREHANDLE}->prepare($sql);    #print "prepare sql: $sql\n";
170      
171      my $sth = $self->{_COREHANDLE}->prepare($sql);
172    $sth->execute();    $sth->execute();
173    return $sth;    return $sth;
174  }  }
# Line 109  sub _sendSql { Line 176  sub _sendSql {
176  sub sendCommand {  sub sendCommand {
177    my $self = shift;    my $self = shift;
178    my $command = shift;    my $command = shift;
179      # TODO: when tracing: yes, do actually log this
180    #$logger->debug( __PACKAGE__ . "->sendCommand( command $command )" );    #$logger->debug( __PACKAGE__ . "->sendCommand( command $command )" );
181    my $cmdHandle = $self->_sendSql($command);    my $cmdHandle = $self->_sendSql($command);
182    my $result = Data::Storage::Result::DBI->new( RESULTHANDLE => $cmdHandle );    my $result = Data::Storage::Result::DBI->new( RESULTHANDLE => $cmdHandle );
183    return $result;    return $result;
184  }  }
185    
186  sub quoteSql {  sub getChildNodes {
187    my $self = shift;    my $self = shift;
188    my $string = shift;    my @nodes;
189    if ($string) {    $logger->debug( __PACKAGE__ . "->getChildNodes()" );
190      $string =~ s/'/\\'/g;    my $locator = $self->{locator};
191      #print Dumper($locator); exit;
192      if (my $result = $self->sendCommand( 'SHOW TABLES;' ) ) {
193        my $dbname = dsn2dbname($self->{locator}->{dbi}->{dsn});
194        my $key = "Tables_in_$dbname";
195        while ( my $row = $result->getNextEntry() ) {
196          push @nodes, $row->{$key};
197        }
198    }    }
199    return $string;    return \@nodes;
200  }  }
201    
202  sub hash2Sql {  sub getListUnfiltered {
203      my $self = shift;
204      my $nodename = shift;
205      my @list;
206      $logger->debug( __PACKAGE__ . "->getListUnfiltered( nodename => '" . $nodename . "' )" );
207      # get list of rows from rdbms by table name
208      my $result = $self->sendCommand("SELECT * FROM $nodename");
209      while ( my $row = $result->getNextEntry() ) {
210        push @list, $row;
211      }
212      return \@list;
213    }
214    
215    sub sendQuery {
216    my $self = shift;    my $self = shift;
217        my $query = shift;
218    my $table = shift;  
219    my $hash = shift;    $logger->debug( __PACKAGE__ . "->sendQuery" );
220    my $mode = shift;  
221    my $crit = shift;    my $crud = $query->{options}->{crud};
222        $crud ||= $query->{options}->{action};
223    my $sql;    $crud ||= 'RETRIEVE';
224    if ($mode eq 'SQL_INSERT') {  
225      $sql = "INSERT INTO $table (#fields#) VALUES (#values#);";    if (my $guid = $query->{options}->{GUID}) {
226        $query->{criterias} = [ { key => 'guid', op => 'eq', val => $guid } ];
227    }    }
228    if ($mode eq 'SQL_UPDATE') {  
229      $sql = "UPDATE $table SET #fields-values# WHERE $crit;";    # check criterias (load & save)
230      #my $sql = "SELECT cs FROM $self->{metainfo}->{$descent}->{node} WHERE $self->{metainfo}->{$descent}->{IdentProvider}->{arg}='$self->{entry}->{source}->{ident}';";
231      #my $result = $self->{metainfo}->{$descent}->{storage}->sendCommand($sql);
232      my @crits;
233      foreach (@{$query->{criterias}}) {
234        my $op = '';
235        $op = '=' if lc $_->{op} eq 'eq';
236        push @crits, "$_->{key}$op'$_->{val}'";
237    }    }
238      # HACK: this is hardcoded ;(    expand possibilities!
239      my $crit = join(' AND ', @crits);
240        
241    my (@fields, @values);    # check subnodes (load)
242    foreach my $key (keys %{$hash}) {    my $subnodes = {};
243      push @fields, $key;    map { $subnodes->{$_}++ } @{$query->{subnodes}};
     push @values, $hash->{$key};  
   }  
   # quote each element  
   map { if (defined $_) { $_ = "'$_'" } else { $_ = "null" } } @values;  
244        
245    my $fields = join(', ', @fields);    # check payload (save)
246    my $values = join(', ', @values);    #print Dumper($query->{payload});
247    my $fields_values = '';    #$subnodes ||= $query->{payload};
   my $fc = 0;  
   foreach (@fields) {  
     $fields_values .= $_ . '=' . $values[$fc] . ', ';  
     $fc++;  
   }  
   $fields_values = substr($fields_values, 0, -2);  
248        
249    $sql =~ s/#fields#/$fields/;    # dispatch action
250    $sql =~ s/#values#/$values/;    if ($crud eq 'RETRIEVE') {
251    $sql =~ s/#fields-values#/$fields_values/;      my $sql = hash2sql($query->{node}, $subnodes, 'SELECT', $crit);
252          return $self->sendCommand($sql);
253    return $sql;  
254      } elsif ($crud eq 'UPDATE') {
255        my $sql = hash2sql($query->{node}, $query->{payload}, 'UPDATE', $crit);
256        $self->sendCommand($sql);
257    
258      } elsif ($crud eq 'DELETE') {
259        my $sql = hash2sql($query->{node}, $query->{payload}, 'DELETE', $crit);
260        $self->sendCommand($sql);
261    
262      }
263    
264  }  }
265    
266    sub eraseAll {
267      my $self = shift;
268      my $classname = shift;
269      $logger->debug( __PACKAGE__ . "->eraseAll" );
270      my $sql = "DELETE FROM $classname";
271      $self->sendCommand($sql);
272    }
273    
274  sub getChildNodes {  # TODO: actually implement the filtering functionality using $this->sendQuery
275    sub getListFiltered {
276      my $self = shift;
277      my $nodename = shift;
278      return $self->getListUnfiltered($nodename);
279    }
280    
281    # TODO: do this via a parametrized "$self->connect(<connect just to database server - don't select database>)"
282    sub createDb {
283      
284    my $self = shift;    my $self = shift;
285    my @nodes;    
286      # get dsn from Data::Storage::Locator instance
287      my $dsn = $self->{locator}->{dbi}->{dsn};
288    
289    if (my $result = $self->sendCommand( 'SHOW TABLES;' ) ) {    $logger->debug( __PACKAGE__ .  "->createDb( dsn $dsn )" );
290      while ( my $row = $result->_getNextEntry() ) {  
291        push @nodes, $row;    # remove database setting from dsn-string
292      $dsn =~ s/database=(.+?);//;
293      
294      # remember extracted database name to know what actually to create right now
295      my $database_name = $1;
296    
297      # flag to indicate goodness
298      my $ok;
299      
300      # connect to database server - don't select/use any specific database
301      #if ( my $dbh = DBI->connect($dsn, '', '', { PrintError => 0 } ) ) {
302      if ( my $dbh = DBI->connect($dsn, '', '', $self->{locator}->{dbi} ) ) {
303    
304        if ($database_name) {
305          if ($dbh->do("CREATE DATABASE $database_name")) {
306            $ok = 1;
307          }
308      }      }
309    
310        $dbh->disconnect();
311    
312    }    }
313        
314    return \@nodes;    return $ok;
315      
316    }
317    
318    sub getCOREHANDLE2 {
319      my $self = shift;
320      return $self->{_COREHANDLE};
321  }  }
322    
323    sub dropDb {
324      my $self = shift;
325      my $dsn = $self->{locator}->{dbi}->{dsn};
326    
327      $logger->debug( __PACKAGE__ .  "->dropDb( dsn $dsn )" );
328    
329      $dsn =~ s/database=(.+?);//;
330      my $database_name = $1;
331    
332  package Data::Storage::Result::DBI;    my $ok;
333      
334      if ( my $dbh = DBI->connect($dsn, '', '', {
335                                                          PrintError => 0,
336                                                          } ) ) {
337        if ($database_name) {
338          if ($dbh->do("DROP DATABASE $database_name;")) {
339            $ok = 1;
340          }
341        }
342    
343  use strict;      $dbh->disconnect();
 use warnings;  
344    
345  use base ("Data::Storage::Result");    }
346      
347      return $ok;
348    }
349    
350  sub DESTROY {  sub rebuildDb {
351    my $self = shift;    my $self = shift;
352    #$logger->debug( __PACKAGE__ . "->" . "DESTROY" );    $logger->info( __PACKAGE__ . "->rebuildDb()" );
353    $self->{RESULTHANDLE} && $self->{RESULTHANDLE}->finish();    my @results;
354    
355      # sum up results (bool (0/1)) in array
356      #push @results, $self->retreatSchema();
357      push @results, $self->dropDb();
358      push @results, $self->createDb();
359      #push @results, $self->deploySchema();
360    
361      # scan array for "bad ones"
362      my $res = 1;
363      map {
364        $res = 0 if (!$_);
365      } @results;
366      
367      return $res;
368  }  }
369    
370  sub _getNextEntry {  sub testAvailability {
371    my $self = shift;    my $self = shift;
372    $self->{RESULTHANDLE} && return $self->{RESULTHANDLE}->fetchrow_hashref;    my $status = $self->testDsn();
373      $self->{locator}->{status}->{available} = $status;
374      return $status;
375  }  }
376    
377    sub testDsn {
378      my $self = shift;
379      my $dsn = $self->{locator}->{dbi}->{dsn};
380      my $result;
381      if ( my $dbh = DBI->connect($dsn, '', '', {
382                                                          PrintError => 0,
383                                                          } ) ) {
384        
385        # TODO: REVIEW
386        $dbh->disconnect();
387        
388        return 1;
389      } else {
390        $logger->warning( __PACKAGE__ .  "[$self->{locator}->{type}]" . "->testDsn(): " . "DBI-error: " . $DBI::errstr );
391      }
392    }
393    
 1;  
394    1;
395    __END__
Legend:
Removed from v.1.2  
changed lines
  Added in v.1.16
MailToCvsAdmin">MailToCvsAdmin
 ViewVC Help
Powered by ViewVC 1.1.26 RSS 2.0 feed