| 1 | package Data::All::IO::Database; | ||
| 2 | |||
| 3 | # $Id: Database.pm,v 1.1.1.1 2005/05/10 23:56:20 dmandelbaum Exp $ | ||
| 4 | |||
| 5 | |||
| 6 | use strict; | ||
| 7 | no warnings; | ||
| 8 | |||
| 9 | use Data::All::IO::Base; | ||
| 10 | use DBI; | ||
| 11 | |||
| 12 | our $VERSION = 0.16; | ||
| 13 | |||
| 14 | attribute '__DBH'; | ||
| 15 | attribute '__STH'; | ||
| 16 | |||
| 17 | attribute 'format'; | ||
| 18 | attribute 'fields'; | ||
| 19 | attribute 'ioconf'; | ||
| 20 | attribute 'path'; | ||
| 21 | |||
| 22 | attribute 'is_open' => 0; | ||
| 23 | |||
| 24 | sub open($) | ||
| 25 | { | ||
| 26 | my $self = shift; | ||
| 27 | my $query = $self->path()->[3]; | ||
| 28 | |||
| 29 | unless ($self->is_open()) | ||
| 30 | { | ||
| 31 | #warn " -> Opening database connection for ", $self->ioconf()->{'perm'}; | ||
| 32 | #warn " -> path:", join ', ', @{ $self->path() }; | ||
| 33 | #warn " -> format:", $self->format()->{'type'}; | ||
| 34 | #warn " -> io:", $self->ioconf->{'type'}; | ||
| 35 | |||
| 36 | $self->_create_dbh(); # Open DB connection | ||
| 37 | if ($self->ioconf()->{'perm'} =~ /r/) | ||
| 38 | { | ||
| 39 | #warn " -> Executing query"; | ||
| 40 | |||
| 41 | my $sth = $self->__DBH()->prepare($query); | ||
| 42 | $sth->execute() or die "Can't execute statement: $DBI::errstr"; | ||
| 43 | $self->__STH($sth); | ||
| 44 | $self->_extract_fields(); | ||
| 45 | } | ||
| 46 | |||
| 47 | $self->is_open(1); | ||
| 48 | } | ||
| 49 | |||
| 50 | return $self->is_open(); | ||
| 51 | } | ||
| 52 | |||
| 53 | sub close() | ||
| 54 | { | ||
| 55 | my $self = shift; | ||
| 56 | |||
| 57 | $self->__STH()->finish() | ||
| 58 | , $self->__DBH()->commit() # NOTE: uncomment this if autocommit = 0 | ||
| 59 | if ($self->__STH()); | ||
| 60 | |||
| 61 | $self->__DBH()->disconnect(); | ||
| 62 | $self->is_open(0); | ||
| 63 | |||
| 64 | return; | ||
| 65 | } | ||
| 66 | |||
| 67 | sub nextrecord() { $_[0]->__STH()->fetchrow_hashref() } | ||
| 68 | |||
| 69 | sub getrecord_hash() | ||
| 70 | { | ||
| 71 | my $self = shift; | ||
| 72 | my $sth = $self->__STH(); | ||
| 73 | |||
| 74 | return $sth->fetchrow_hashref(); | ||
| 75 | } | ||
| 76 | |||
| 77 | sub getrecord_array() | ||
| 78 | { | ||
| 79 | my $self = shift; | ||
| 80 | my $record = $self->__STH()->fetchrow_arrayref(); | ||
| 81 | |||
| 82 | return !wantarray ? $record : @{ $record }; | ||
| 83 | } | ||
| 84 | |||
| 85 | sub getrecords | ||
| 86 | { | ||
| 87 | my $self = shift; | ||
| 88 | |||
| 89 | return undef unless ($self->__STH()->rows); | ||
| 90 | |||
| 91 | my (@records); | ||
| 92 | while (my $ref = $self->__STH()->fetchrow_hashref()) | ||
| 93 | { | ||
| 94 | push (@records, $ref); | ||
| 95 | } | ||
| 96 | |||
| 97 | return !wantarray ? \@records : @records; | ||
| 98 | |||
| 99 | } | ||
| 100 | |||
| 101 | sub putfields() | ||
| 102 | { | ||
| 103 | my $self = shift; | ||
| 104 | |||
| 105 | # We don't do nothin' with fields for the database | ||
| 106 | |||
| 107 | # IDEA: Maybe we could use this call for creating a table | ||
| 108 | } | ||
| 109 | |||
| 110 | |||
| 111 | sub putrecord($;\%) | ||
| 112 | { | ||
| 113 | my $self = shift; | ||
| 114 | my ($record, $options) = @_; | ||
| 115 | |||
| 116 | |||
| 117 | my @vars = $self->_generate_query_vars( | ||
| 118 | $options, $self->hash_to_array($record)); | ||
| 119 | |||
| 120 | #print join(':', @vars), "\n"; | ||
| 121 | |||
| 122 | $self->__STH($self->__DBH()->prepare($self->path()->[3])) | ||
| 123 | unless $self->__STH(); | ||
| 124 | |||
| 125 | $self->__STH()->execute(@vars); | ||
| 126 | |||
| 127 | return 1; | ||
| 128 | } | ||
| 129 | |||
| 130 | |||
| 131 | sub putrecords() | ||
| 132 | { | ||
| 133 | my $self = shift; | ||
| 134 | my ($records, $options) = @_; | ||
| 135 | |||
| 136 | my $query = $self->path()->[3]; | ||
| 137 | |||
| 138 | |||
| 139 | die("$self->putrecords() needs records") unless ($#{ $records }+1); | ||
| 140 | |||
| 141 | $self->__STH($self->__DBH()->prepare($query)); | ||
| 142 | |||
| 143 | my $record; | ||
| 144 | foreach my $rec (@{ $records }) | ||
| 145 | { | ||
| 146 | $self->putrecord($rec, $options); | ||
| 147 | } | ||
| 148 | |||
| 149 | # Close the statement handle | ||
| 150 | $self->__STH()->finish(); | ||
| 151 | |||
| 152 | } | ||
| 153 | |||
| 154 | sub count() | ||
| 155 | # TODO: Refactor this count() functionality. | ||
| 156 | # What about INSERT queries. We could keep track of how many were | ||
| 157 | # successfully inserted. | ||
| 158 | { | ||
| 159 | my $self = shift; | ||
| 160 | my $query = $self->path()->[3]; | ||
| 161 | my ($sth, $ref, $count); | ||
| 162 | |||
| 163 | return $count unless($self->ioconf()->{'perm'} =~ /^r/); | ||
| 164 | |||
| 165 | $query =~ s/SELECT\s.+?\sFROM/SELECT COUNT(*) as cnt FROM/im; | ||
| 166 | |||
| 167 | return undef unless ($sth = $self->__DBH()->prepare($query)); | ||
| 168 | |||
| 169 | $count = $self->__STH()->execute() or return undef; | ||
| 170 | |||
| 171 | $self->__STH()->finish(); | ||
| 172 | |||
| 173 | return $count; | ||
| 174 | } | ||
| 175 | |||
| 176 | |||
| 177 | |||
| 178 | |||
| 179 | |||
| 180 | sub _generate_query_vars($$) | ||
| 181 | # Create an ordered array of values to use in a DBI->execute() call to | ||
| 182 | # replace '?' in the query. | ||
| 183 | { | ||
| 184 | my $self = shift; | ||
| 185 | my ($options, $vars) = @_; | ||
| 186 | my @vars; | ||
| 187 | |||
| 188 | # TODO: Move arrayref checking to some form of option parser | ||
| 189 | |||
| 190 | if (defined($options->{'extra_pre_vars'})) | ||
| 191 | { | ||
| 192 | my @pre_vars = (ref($options->{'extra_pre_vars'}) eq 'ARRAY') | ||
| 193 | ? @{ $options->{'extra_pre_vars'} } | ||
| 194 | : ($options->{'extra_pre_vars'}); | ||
| 195 | |||
| 196 | # Add the prefix values to the beginning of the array | ||
| 197 | push(@vars, @pre_vars); | ||
| 198 | } | ||
| 199 | |||
| 200 | # Put the actual values into the array (in an INSERT, putrecord() will | ||
| 201 | # send the ordered field values here) | ||
| 202 | push(@vars, @{ $vars }); | ||
| 203 | |||
| 204 | # Complete the array with the suffix values | ||
| 205 | if (defined($options->{'extra_post_vars'})) | ||
| 206 | { | ||
| 207 | my @post_vars = (ref($options->{'extra_post_vars'}) eq 'ARRAY') | ||
| 208 | ? @{ $options->{'extra_post_vars'} } | ||
| 209 | : ($options->{'extra_post_vars'}); | ||
| 210 | |||
| 211 | # Add the prefix values to the beginning of the array | ||
| 212 | push(@vars, @post_vars); | ||
| 213 | } | ||
| 214 | |||
| 215 | return wantarray ? @vars : \@vars; | ||
| 216 | } | ||
| 217 | |||
| 218 | sub _create_dbh() | ||
| 219 | { | ||
| 220 | my $self = shift; | ||
| 221 | my $dbh = $self->__DBH() || $self->_db_connect(); | ||
| 222 | |||
| 223 | ($dbh) | ||
| 224 | ? $self->__DBH($dbh) | ||
| 225 | : die("Cannot create DB Connection"); | ||
| 226 | |||
| 227 | #$self->__DBH()->trace(2); | ||
| 228 | } | ||
| 229 | |||
| 230 | sub _create_sth() | ||
| 231 | { | ||
| 232 | my $self = shift; | ||
| 233 | my $sth = $self->__DBH()->prepare(); | ||
| 234 | |||
| 235 | ($sth) | ||
| 236 | ? $self->__STH($sth) | ||
| 237 | : die("Cannot prepare statement handle"); | ||
| 238 | } | ||
| 239 | |||
| 240 | sub _db_connect() | ||
| 241 | { | ||
| 242 | my $self = shift; | ||
| 243 | return if ($self->is_open()); | ||
| 244 | # NOTE: See line 53 if you want to set autocommit = 0 | ||
| 245 | return DBI->connect($self->_create_connect(), { PrintWarn=>1,PrintError=>1, RaiseError => 1, AutoCommit => 0 }); | ||
| 246 | } | ||
| 247 | |||
| 248 | sub _create_connect() | ||
| 249 | { | ||
| 250 | my $self = shift; | ||
| 251 | return ($self->path()->[0],$self->path()->[1],$self->path()->[2]); | ||
| 252 | } | ||
| 253 | |||
| 254 | sub _extract_fields() | ||
| 255 | { | ||
| 256 | my $self = shift; | ||
| 257 | return if ($self->fields()); | ||
| 258 | |||
| 259 | $self->fields($self->__STH()->{'NAME'}); | ||
| 260 | } | ||
| 261 | |||
| 262 | |||
| 263 | |||
| 264 | 1; |