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