--- /dev/null
+=pod
+
+=head1 NAME $RCSfile: MyDB.pm,v $
+
+Object oriented, quick and easy interface to MySQL/MariaDB databases
+
+=head1 VERSION
+
+=over
+
+=item Author
+
+Andrew DeFaria <Andrew@DeFaria.com>
+
+=item Revision
+
+$Revision: 1.0 $
+
+=item Created
+
+Sat 19 Jun 2021 11:05:00 PDT
+
+=item Modified
+
+$Date: $
+
+=back
+
+=head1 SYNOPSIS
+
+Provides lower level, basic database routines in an Perl object
+
+ # Instanciate MyDB object
+ my $db = MyDB->new(<database>, <username>, <password>, %opts);
+
+ # Add record
+ my $status = $db->add(<tablename>, <%record>);
+
+ # Delete record
+ my $status = $db->delete(<tablename>, <condition>);
+
+ # Modify record
+ my $status = $db->modify(<tablename>, <%record>, <condition>)
+
+ # Get records
+ my @records = $db->get(<tablename>, <condition>, <fields>, <additional>)
+
+=head1 DESCRIPTION
+
+Low level but convienent database routines
+
+=head1 ROUTINES
+
+The following routines are exported:
+
+=cut
+
+package MyDB;
+
+use strict;
+use warnings;
+
+use Carp;
+use DBI;
+use Exporter;
+
+use Utils;
+
+# Globals
+our $VERSION = '$Revision: 1.0 $';
+ ($VERSION) = ($VERSION =~ /\$Revision: (.*) /);
+
+my %opts = (
+ MYDB_USERNAME => $ENV{MYDB_USERNAME},
+ MYDB_PASSWORD => $ENV{MYDB_PASSWORD},
+ MYDB_DATABASE => $ENV{MYDB_DATABASE},
+ MYDB_SERVER => $ENV{MYDB_SERVER} || 'localhost',
+);
+
+# Internal methods
+sub _dberror($$) {
+ my ($self, $msg, $statement) = @_;
+
+ my $dberr = $self->{db}->err;
+ my $dberrmsg = $self->{db}->errstr;
+
+ $dberr ||= 0;
+ $dberrmsg ||= 'Success';
+
+ my $message = '';
+
+ if ($dberr) {
+ my $function = (caller(1)) [3];
+
+ $message = "$function: $msg\nError #$dberr: $dberrmsg\n"
+ . "SQL Statement: $statement";
+ } # if
+
+ return $dberr, $message;
+} # _dberror
+
+sub _encode_decode ($$$) {
+ my ($self, $type, $password, $userid) = @_;
+
+ my $statement = 'select ';
+
+ if ($type eq 'encode') {
+ $statement .= "hex(aes_encrypt('$password','$userid'))";
+ } elsif ($type eq 'decode') {
+ $statement .= "aes_decrypt(unhex('$password'),'$userid')";
+ } # if
+
+ my $sth = $self->{db}->prepare($statement)
+ or return $self->_dberror('MyDB::$type: Unable to prepare statement', $statement);
+
+ $sth->execute
+ or $self->_dberror('MyDB::$type: Unable to execute statement', $statement);
+
+ my @row = $sth->fetchrow_array;
+
+ return $row[0];
+} # _encode_decode
+
+sub _formatValues(@) {
+ my ($self, @values) = @_;
+
+ my @returnValues;
+
+ # Quote data values
+ push @returnValues, ($_ and $_ ne '')
+ ? $self->{db}->quote($_)
+ : 'null'
+ for (@values);
+
+ return @returnValues;
+} # _formatValues
+
+sub _formatNameValues(%) {
+ my ($self, %rec) = @_;
+
+ my @nameValueStrs;
+
+ for (keys %rec) {
+ if ($rec{$_}) {
+ push @nameValueStrs, "$_=" . $self->{db}->quote($rec{$_});
+ } else {
+ push @nameValueStrs, "$_=null";
+ } # if
+ } # for
+
+ return @nameValueStrs;
+} # _formatNameValues
+
+sub add($%) {
+ my ($self, $table, %rec) = @_;
+
+ my $statement = "insert into $table (";
+ $statement .= join ',', keys %rec;
+ $statement .= ') values (';
+ $statement .= join ',', $self->_formatValues(values %rec);
+ $statement .= ')';
+
+ $self->{db}->do($statement);
+
+ return $self->_dberror("Unable to add record to $table", $statement);
+} # add
+
+sub check($) {
+ my ($self, $table) = @_;
+
+ my @tables;
+
+ if (ref $table eq 'ARRAY') {
+ @tables = @$table;
+ } else {
+ push @tables, $table;
+ } # if
+
+ my $statement = 'check table ';
+ $statement .= join ',', @tables;
+
+ $self->{db}->do($statement);
+
+ return $self->_dberror('MyDB::check: Unable to check tables', $statement);
+} # check
+
+sub count($;$) {
+ my ($self, $table, $condition) = @_;
+
+ my $statement = "select count(*) from $table";
+ $statement .= " where $condition" if $condition;
+
+ my $sth = $self->{db}->prepare($statement)
+ or return $self->_dberror('MyDB::count: Unable to prepare statement', $statement);
+
+ $sth->execute
+ or return $self->_dberror('MyDB::count: Unable to execute statement', $statement);
+
+ # Get return value, which should be how many entries there are
+ my @row = $sth->fetchrow_array;
+
+ # Done with $sth
+ $sth->finish;
+
+ my $count;
+
+ # Retrieve returned value
+ unless ($row[0]) {
+ wantarray ? return (0, 'No records found') : return 0;
+ } else {
+ wantarray ? return ($row[0], 'Records found') : return $row[0];
+ } # unless
+
+ return;
+} # count
+
+sub count_distinct($$;$) {
+ my ($self, $table, $column, $condition) = @_;
+
+ my $statement = "select count(distinct $column) from $table";
+ $statement .= " where $condition" if $condition;
+
+ my $sth = $self->{db}->prepare($statement)
+ or return $self->_dberror('MyDB::count: Unable to prepare statement', $statement);
+
+ $sth->execute
+ or return $self->_dberror('MyDB::count: Unable to execute statement', $statement);
+
+ # Get return value, which should be how many entries there are
+ my @row = $sth->fetchrow_array;
+
+ # Done with $sth
+ $sth->finish;
+
+ my $count;
+
+ # Retrieve returned value
+ unless ($row[0]) {
+ wantarray ? return (0, 'No records found') : return 0;
+ } else {
+ wantarray ? return ($row[0], 'Records found') : return $row[0];
+ } # unless
+
+ return;
+} # count_distinct
+
+sub decode($$) {
+ my ($self, $password, $userid) = @_;
+
+ return $self->_encode_decode('decode', $password, $userid);
+} # decode
+
+sub delete($;$) {
+ my ($self, $table, $condition) = @_;
+
+ my $count = $self->count($table, $condition);
+
+ return ($count, 'Records deleted') if $count == 0;
+
+ my $statement = "delete from $table ";
+ $statement .= "where $condition" if $condition;
+
+ $self->{db}->do($statement);
+
+ if ($self->{db}->err) {
+ my ($err, $msg) = $self->_dberror("MyDB::delete: Unable to delete record(s) from $table", $statement);
+
+ wantarray ? return (-$err, $msg) : return -$err;
+ } else {
+ wantarray ? return ($count, 'Records deleted') : return $count;
+ } # if
+
+ return;
+} # delete
+
+sub DESTROY {
+ my ($self) = @_;
+
+ $self->{db}->disconnect if $self->{db};
+
+ return;
+} # DESTROY
+
+sub encode($$) {
+ my ($self, $password, $userid) = @_;
+
+ return $self->_encode_decode('encode', $password, $userid);
+} # encode
+
+sub find($;$@) {
+ my ($self, $table, $condition, $fields, $additional) = @_;
+
+ $fields //= '*';
+
+ $fields = join ',', @$fields if ref $fields eq 'ARRAY';
+
+ my $statement = "select $fields from $table";
+ $statement .= " where $condition" if $condition;
+ $statement .= " $additional" if $additional;
+
+ $self->{sth} = $self->{db}->prepare($statement)
+ or return $self->_dberror('MyDB::find: Unable to prepare statement', $statement);
+
+ $self->{sth}->execute
+ or return $self->_dberror('MyDB::find: Unable to execute statement', $statement);
+
+ return $self->_dberror("MyDB::find: Unable to find record ($table, $condition)", $statement);
+} # find
+
+sub get($;$$$) {
+ my ($self, $table, $condition, $fields, $additional) = @_;
+
+ $fields //= '*';
+
+ $fields = join ',', @$fields if ref $fields eq 'ARRAY';
+
+ my $statement = "select $fields from $table";
+ $statement .= " where $condition" if $condition;
+ $statement .= " $additional" if $additional;
+
+ my $rows = $self->{db}->selectall_arrayref($statement, { Slice => {} });
+
+ return $rows if $rows;
+ return $self->_dberror('MyDB::get: Unable to prepare/execute statement', $statement);
+} # get
+
+sub getone($;$$$) {
+ my ($self, $table, $condition, $fields, $additional) = @_;
+
+ my $rows = $self->get($table, $condition, $fields, $additional);
+
+ return $rows->[0];
+} # getone
+
+sub getnext() {
+ my ($self) = @_;
+
+ return unless $self->{sth};
+
+ return $self->{sth}->fetchrow_hashref;
+} # getnext
+
+sub lastid() {
+ my ($self) = @_;
+
+ my $statement = 'select last_insert_id()';
+
+ my $sth = $self->{db}->prepare($statement)
+ or $self->_dberror('MyDB::lastid: Unable to prepare statement', $statement);
+
+ $sth->execute
+ or $self->_dberror('MyDB::lastid: Unable to execute statement', $statement);
+
+ my @row = $sth->fetchrow_array;
+
+ return $row[0];
+} # lastid
+
+sub lock(;$$) {
+ my ($self, $type, $table) = @_;
+
+ $type //= 'read';
+
+ croak "Type must be read or write" unless $type =~ /(read|write)/;
+
+ my $tables;
+
+ if (ref $table eq 'ARRAY') {
+ $tables = join " $type,", @$table;
+ } else {
+ $tables = $table;
+ } # if
+
+ my $statement = "lock tables $tables";
+ $statement .= " $type";
+
+ $self->{db}->do($statement);
+
+ return $self->_dberror("MyDB::lock Unable to lock $tables", $statement);
+} # lock
+
+sub modify($$%) {
+ my ($self, $table, $condition, %rec) = @_;
+
+ my $statement = "update $table set ";
+ $statement .= join ',', $self->_formatNameValues(%rec);
+ $statement .= " where $condition" if $condition;
+
+ $self->{db}->do($statement);
+
+ return $self->_dberror("MyDB::modify: Unable to update record in $table", $statement);
+} # modify
+
+sub new(;$$$$) {
+ my ($class, $username, $password, $database, $dbserver) = @_;
+
+ my $self = {
+ username => $username || $opts{MYDB_USERNAME},
+ password => $password || $opts{MYDB_PASSWORD},
+ database => $database || $opts{MYDB_DATABASE},
+ dbserver => $dbserver || $opts{MYDB_SERVER},
+ };
+
+ bless $self, $class;
+
+ $self->{dbdriver} = 'mysql';
+
+ $self->{db} = DBI->connect(
+ "DBI:$self->{dbdriver}:$database:$self->{dbserver}",
+ $self->{username},
+ $self->{password},
+ {PrintError => 0},
+ ) or croak "MyDB::new: Couldn't connect to $database database as $self->{username}\@$self->{dbserver}";
+
+ return $self;
+} # new
+
+sub optimize($) {
+ my ($self, $table) = @_;
+
+ my @tables;
+
+ if (ref $table eq 'ARRAY') {
+ @tables = @$table;
+ } else {
+ push @tables, $table;
+ } # if
+
+ my $statement = 'optimize table ';
+ $statement .= join ',', @tables;
+
+ $self->{db}->do($statement);
+
+ return $self->_dberror('MyDB::optimize: Unable to optimize tables', $statement);
+} # optimize
+
+sub unlock() {
+ my ($self) = @_;
+
+ my $statement = 'unlock tables';
+
+ $self->{db}->do($statement);
+
+ return $self->_dberror('MyDB::unlock: Unable to unlock tables', $statement);
+} # unlock
+
+sub update($$%) {
+ # Using a Perl goto statement in this fashion really just creates an alias
+ # such that the user can call either modify or update.
+ goto &modify;
+} # update
+
+1;
projects / clearscm.git / blobdiff