3 =head1 NAME $RCSfile: MyDB.pm,v $
5 Object oriented, quick and easy interface to MySQL/MariaDB databases
13 Andrew DeFaria <Andrew@DeFaria.com>
21 Sat 19 Jun 2021 11:05:00 PDT
31 Provides lower level, basic database routines in an Perl object
33 # Instanciate MyDB object
34 my $db = MyDB->new(<database>, <username>, <password>, %opts);
37 my $status = $db->add(<tablename>, <%record>);
40 my $status = $db->delete(<tablename>, <condition>);
43 my $status = $db->modify(<tablename>, <%record>, <condition>)
46 my @records = $db->get(<tablename>, <condition>, <fields>, <additional>)
50 Low level but convienent database routines
54 The following routines are exported:
70 our $VERSION = '$Revision: 1.0 $';
71 ($VERSION) = ($VERSION =~ /\$Revision: (.*) /);
74 MYDB_USERNAME => $ENV{MYDB_USERNAME},
75 MYDB_PASSWORD => $ENV{MYDB_PASSWORD},
76 MYDB_DATABASE => $ENV{MYDB_DATABASE},
77 MYDB_SERVER => $ENV{MYDB_SERVER} || 'localhost',
82 my ($self, $msg, $statement) = @_;
84 my $dberr = $self->{db}->err;
85 my $dberrmsg = $self->{db}->errstr;
88 $dberrmsg ||= 'Success';
93 my $function = (caller(1)) [3];
95 $message = "$function: $msg\nError #$dberr: $dberrmsg\n"
96 . "SQL Statement: $statement";
99 return $dberr, $message;
102 sub _encode_decode ($$$) {
103 my ($self, $type, $password, $userid) = @_;
105 my $statement = 'select ';
107 if ($type eq 'encode') {
108 $statement .= "hex(aes_encrypt('$password','$userid'))";
109 } elsif ($type eq 'decode') {
110 $statement .= "aes_decrypt(unhex('$password'),'$userid')";
113 my $sth = $self->{db}->prepare($statement)
114 or return $self->_dberror('MyDB::$type: Unable to prepare statement', $statement);
117 or $self->_dberror('MyDB::$type: Unable to execute statement', $statement);
119 my @row = $sth->fetchrow_array;
124 sub _formatValues(@) {
125 my ($self, @values) = @_;
130 push @returnValues, ($_ and $_ ne '')
131 ? $self->{db}->quote($_)
135 return @returnValues;
138 sub _formatNameValues(%) {
139 my ($self, %rec) = @_;
145 push @nameValueStrs, "$_=" . $self->{db}->quote($rec{$_});
147 push @nameValueStrs, "$_=null";
151 return @nameValueStrs;
152 } # _formatNameValues
155 my ($self, $table, %rec) = @_;
157 my $statement = "insert into $table (";
158 $statement .= join ',', keys %rec;
159 $statement .= ') values (';
160 $statement .= join ',', $self->_formatValues(values %rec);
163 $self->{db}->do($statement);
165 return $self->_dberror("Unable to add record to $table", $statement);
169 my ($self, $table) = @_;
173 if (ref $table eq 'ARRAY') {
176 push @tables, $table;
179 my $statement = 'check table ';
180 $statement .= join ',', @tables;
182 $self->{db}->do($statement);
184 return $self->_dberror('MyDB::check: Unable to check tables', $statement);
188 my ($self, $table, $condition) = @_;
190 my $statement = "select count(*) from $table";
191 $statement .= " where $condition" if $condition;
193 my $sth = $self->{db}->prepare($statement)
194 or return $self->_dberror('MyDB::count: Unable to prepare statement', $statement);
197 or return $self->_dberror('MyDB::count: Unable to execute statement', $statement);
199 # Get return value, which should be how many entries there are
200 my @row = $sth->fetchrow_array;
207 # Retrieve returned value
209 wantarray ? return (0, 'No records found') : return 0;
211 wantarray ? return ($row[0], 'Records found') : return $row[0];
217 sub count_distinct($$;$) {
218 my ($self, $table, $column, $condition) = @_;
220 my $statement = "select count(distinct $column) from $table";
221 $statement .= " where $condition" if $condition;
223 my $sth = $self->{db}->prepare($statement)
224 or return $self->_dberror('MyDB::count: Unable to prepare statement', $statement);
227 or return $self->_dberror('MyDB::count: Unable to execute statement', $statement);
229 # Get return value, which should be how many entries there are
230 my @row = $sth->fetchrow_array;
237 # Retrieve returned value
239 wantarray ? return (0, 'No records found') : return 0;
241 wantarray ? return ($row[0], 'Records found') : return $row[0];
248 my ($self, $password, $userid) = @_;
250 return $self->_encode_decode('decode', $password, $userid);
254 my ($self, $table, $condition) = @_;
256 my $count = $self->count($table, $condition);
258 return ($count, 'Records deleted') if $count == 0;
260 my $statement = "delete from $table ";
261 $statement .= "where $condition" if $condition;
263 $self->{db}->do($statement);
265 if ($self->{db}->err) {
266 my ($err, $msg) = $self->_dberror("MyDB::delete: Unable to delete record(s) from $table", $statement);
268 wantarray ? return (-$err, $msg) : return -$err;
270 wantarray ? return ($count, 'Records deleted') : return $count;
279 $self->{db}->disconnect if $self->{db};
285 my ($self, $password, $userid) = @_;
287 return $self->_encode_decode('encode', $password, $userid);
291 my ($self, $table, $condition, $fields, $additional) = @_;
295 $fields = join ',', @$fields if ref $fields eq 'ARRAY';
297 my $statement = "select $fields from $table";
298 $statement .= " where $condition" if $condition;
299 $statement .= " $additional" if $additional;
301 $self->{sth} = $self->{db}->prepare($statement)
302 or return $self->_dberror('MyDB::find: Unable to prepare statement', $statement);
304 $self->{sth}->execute
305 or return $self->_dberror('MyDB::find: Unable to execute statement', $statement);
307 return $self->_dberror("MyDB::find: Unable to find record ($table, $condition)", $statement);
311 my ($self, $table, $condition, $fields, $additional) = @_;
315 $fields = join ',', @$fields if ref $fields eq 'ARRAY';
317 my $statement = "select $fields from $table";
318 $statement .= " where $condition" if $condition;
319 $statement .= " $additional" if $additional;
321 my $rows = $self->{db}->selectall_arrayref($statement, { Slice => {} });
323 return $rows if $rows;
324 return $self->_dberror('MyDB::get: Unable to prepare/execute statement', $statement);
328 my ($self, $table, $condition, $fields, $additional) = @_;
330 my $rows = $self->get($table, $condition, $fields, $additional);
338 return unless $self->{sth};
340 return $self->{sth}->fetchrow_hashref;
346 my $statement = 'select last_insert_id()';
348 my $sth = $self->{db}->prepare($statement)
349 or $self->_dberror('MyDB::lastid: Unable to prepare statement', $statement);
352 or $self->_dberror('MyDB::lastid: Unable to execute statement', $statement);
354 my @row = $sth->fetchrow_array;
360 my ($self, $type, $table) = @_;
364 croak "Type must be read or write" unless $type =~ /(read|write)/;
368 if (ref $table eq 'ARRAY') {
369 $tables = join " $type,", @$table;
374 my $statement = "lock tables $tables";
375 $statement .= " $type";
377 $self->{db}->do($statement);
379 return $self->_dberror("MyDB::lock Unable to lock $tables", $statement);
383 my ($self, $table, $condition, %rec) = @_;
385 my $statement = "update $table set ";
386 $statement .= join ',', $self->_formatNameValues(%rec);
387 $statement .= " where $condition" if $condition;
389 $self->{db}->do($statement);
391 return $self->_dberror("MyDB::modify: Unable to update record in $table", $statement);
395 my ($class, $username, $password, $database, $dbserver) = @_;
398 username => $username || $opts{MYDB_USERNAME},
399 password => $password || $opts{MYDB_PASSWORD},
400 database => $database || $opts{MYDB_DATABASE},
401 dbserver => $dbserver || $opts{MYDB_SERVER},
406 $self->{dbdriver} = 'mysql';
408 $self->{db} = DBI->connect(
409 "DBI:$self->{dbdriver}:$database:$self->{dbserver}",
413 ) or croak "MyDB::new: Couldn't connect to $database database as $self->{username}\@$self->{dbserver}";
419 my ($self, $table) = @_;
423 if (ref $table eq 'ARRAY') {
426 push @tables, $table;
429 my $statement = 'optimize table ';
430 $statement .= join ',', @tables;
432 $self->{db}->do($statement);
434 return $self->_dberror('MyDB::optimize: Unable to optimize tables', $statement);
440 my $statement = 'unlock tables';
442 $self->{db}->do($statement);
444 return $self->_dberror('MyDB::unlock: Unable to unlock tables', $statement);
448 # Using a Perl goto statement in this fashion really just creates an alias
449 # such that the user can call either modify or update.