# -*- Mode: perl; tab-width: 4; indent-tabs-mode: nil; -*-
################################
# Quotes Module                #
################################
# Based on a request from Nortis http://www.blomstereng.org/

# XXX need to support multiple quote servers:
# !discworld

package BotModules::Quotes;
use vars qw(@ISA);
@ISA = qw(BotModules);
use Fcntl;
use DBI;
1;

# This uses a number of MySQL-specific features.

sub Help {
    my $self = shift;
    my ($event) = @_;
    my $help = {
        '' => 'A module to manage quotes.',
        'quote' => 'Search for a quote, or return a random one. To search for a quote, you must specify search parameters, see the help entries for id, text, author, note, match. Otherwise, a random quote is returned.',
        'match' => 'If there are multiple matches, you can specify which match you want by appending the match number to your search terms, for example \'quote author=blake 4\' will return the fourth quote whose author is \'blake\'. The default is 1.',
        'id' => 'To search for a quote by its numeric ID, append the ID to the \'quote\' command. For example, \'quote 42\'. If you specify other search parameters, this will return the relevant match from that list, see the help entry for \'match\'.',
        'text' => 'To search for a quote by text, append \'text="foo"\' to the \'quote\' command. For example, \'quote text="meaning of life"\' or \'quote text=life\'. You could also just say \'quote hello world\' or \'quote hello world 2\' (to get the second match).',
        'author' => 'To search for a quote by author or attribution, append \'author="foo"\' to the \'quote\' command. For example, \'quote author="Douglas Adams"\' or \'quote author=asimov\'.',
        'note' => 'To search for a quote by text in its note, append \'note="foo"\' to the \'quote\' command. For example, \'quote note=""\' or \'quote author=asimov\'.',
        'quotelast' => 'Returns the last quote added. Append a numer to return the nth but last quote added, as in \'lastquote 2\'.',
        'status' => 'Prints some information about the status of the quotes database.',
    };
    if ($self->canAdd($event)) {
        $help->{'addquote'} = 'Add a quote to the database. The format is \'addquote quote - author (note)\'. The \'(note)\' part may be omitted. The author may not.';
    }
    if ($self->canDelete($event)) {
        $help->{'delquote'} = 'Delete a quote from the database. The format is \'delquote id\'.';
    }
    if ($self->canEdit($event)) {
        $help->{'editquote'} = 'Edit a quote in the database. The format is \'editquote id quote - author (note)\' which will update the quote with that ID, using the new text, author, etc, in the same way as for \'addquote\'.';
    }
    if ($self->isAdmin($event)) {
        $help->{'setupquotes'} = 'Configure the quotes database connection. Format: \'setupquotes dbhost.example.com:dbport dbname dbuser dbpass\'. Port is optional (default 3306). You can also just say \'setupquotes\' to check on the configuration. See also \'help quote-defaults\'.';
        $help->{'quote-defaults'} = 'To get the default configuration, use \'setupquotes mozbotquotes.damowmow.com:3306 mozbotquotes mozbotquotes mozbotquotes\'.';
    }
    return $help;
}

# RegisterConfig - Called when initialised, should call registerVariables
sub RegisterConfig {
    my $self = shift;
    $self->SUPER::RegisterConfig(@_);
    $self->registerVariables(
      # [ name, save?, settable? ]
      ['prefix', 1, 1, '!'], # the prefix to put before the undirected quote commands
      ['dbhost', 1, 1, 'mozbotquotes.damowmow.com'],
      ['dbport', 1, 1, '3306'],
      ['dbname', 1, 1, 'mozbotquotes'],
      ['dbuser', 1, 1, 'mozbotquotes'],
      ['dbpass', 1, 1, 'mozbotquotes'],
      ['tableName', 1, 1, 'quotes'],
      ['usersAdd', 1, 1, []],
      ['usersDelete', 1, 1, []],
      ['usersEdit', 1, 1, []],
    );
}

# call this at the top of any function that uses tableName
sub sanitiseTableName {
    my $self = shift;
    $self->{tableName} =~ s/[^a-zA-Z]//gos;
    if (length($self->{tableName}) < 1) {
        $self->{tableName} = 'quotes';
    }
    $self->saveConfig();
}

sub canAdd {
    my $self = shift;
    return $self->checkRights('Add', @_);
}

sub canDelete {
    my $self = shift;
    return $self->checkRights('Delete', @_);
}

sub canEdit {
    my $self = shift;
    return $self->checkRights('Edit', @_);
}

sub checkRights {
    my $self = shift;
    my ($right, $event) = @_;
    return 1 if $self->isAdmin($event);
    foreach my $user (@{$self->{"users$right"}}) {
        return 1 if $user eq $event->{userName};
    }
    return 0;
}

sub Schedule {
    my $self = shift;
    my ($event) = @_;
    unless ($self->dbconnect()) {
        $self->say($event, "Failed to connect to quotes database: $self->{dberror}");
        $self->say($event, 'Use the \'setupquotes\' command to configure the database.');
    }
    $self->SUPER::Schedule($event);
}

sub dbconnect {
    my $self = shift;
    eval {
        $self->{dbhandle} =
          DBI->connect("DBI:mysql:$self->{dbname}:$self->{dbhost}:$self->{dbport}",
                       $self->{dbuser}, $self->{dbpass},
                       {RaiseError => 1, PrintError => 1, AutoCommit => 1, Taint => 0});
    };
    if (not $self->{dbhandle}) {
        $self->{dberror} = $@;
        $self->debug("Failed to connect to quotes database: $self->{dberror}");
        return 0;
    }
    return 1;
}

sub dbdisconnect {
    my $self = shift;
    my ($event) = @_;
    if ($self->{dbhandle}) {
        $self->{dbhandle}->disconnect();
        $self->{dbhandle} = undef;
    }
}

sub Unload {
    my $self = shift;
    my ($event) = @_;
    $self->dbdisconnect($event);
}

sub dbcheckconfig {
    my $self = shift;
    my ($event) = @_;

    $self->sanitiseTableName();

    # count tables
    my $tables = $self->{dbhandle}->selectall_arrayref('SHOW TABLES');
    my $wantedTable = undef;
    $tables = [] unless defined $tables;
    foreach (@$tables) {
        $_ = $_->[0];
    }
    if (@$tables == 1) {
        # if only one, assume that's the one we want to use
        $wantedTable = $tables->[0];
    } else {
        # otherwise, assume the name is 'quotes'
        $wantedTable = $self->{tableName} || 'quotes';
    }

    # check table exists
    $self->{dbtables} = $tables;
    foreach my $table (@$tables) {
        if (lc $table eq lc $wantedTable) {
            $self->{tableName} = $table;
            $self->saveConfig();
            return 1;
        }
    }
    return 0;
}

sub dbcreatetables {
    my $self = shift;
    my ($event) = @_;

    $self->sanitiseTableName();

    # create table
    eval {
        $self->{dbhandle}->do("CREATE TABLE IF NOT EXISTS $self->{tableName} (
                               id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
                               quote TEXT NOT NULL DEFAULT '',
                               author VARCHAR(100) NOT NULL DEFAULT 'Unknown',
                               date DATETIME NOT NULL DEFAULT 0,
                               note TEXT NULL DEFAULT NULL,
                               shown INTEGER UNSIGNED NOT NULL DEFAULT 0,
                               age INTEGER UNSIGNED NOT NULL DEFAULT 1,
                               INDEX (author), INDEX(shown), INDEX(age)
                             )");
    };
    if ($@) {
        $self->{dberror} = $@;
        $self->debug("Failed to create quotes table: $self->{dberror}");
        return 0;
    }
    return 1;
}

sub verifyConnection {
    my $self = shift;
    my ($event) = @_;
    if ($self->dbconnect()) {
        if (not $self->dbcheckconfig($event)) {
            if (@{$self->{dbtables}}) {
                local $" = '\', \'';
                $self->say($event, "Connected, but I there were several tables and I wasn't sure which to use. The tables in this database are: '@{$self->{dbtables}}'");
                $self->say($event, "To make me create a new table (called '$self->{tableName}') use 'setupquotes table'. To make me use a particular table from the list above, use 'setupquotes use table $self->{dbtables}->[0]' (or whatever table you want to use).");
            } else {
                $self->say($event, "Connected, but I couldn't find a quotes table in the database. If you want me to create a table (named '$self->{tableName}') for you, use 'setupquotes tables'. To create one with a specific name, e.g. 'mozQuotes', use 'setupquotes tables mozQuotes'.");
            }
        } else {
            $self->say($event, "Connected (using table '$self->{tableName}').");
        }
    } else {
        $self->say($event, "Failed to connect to quotes database: $self->{dberror}");
    }
}

sub Told {
    my $self = shift;
    my ($event, $message) = @_;
    if ($message =~ /^\s*set\s*up\s*quotes?(?:\s+(.*?))?\s*$/osi and $self->isAdmin($event)) {
        my $data = $1;
        if ($data =~ m/^(\S+?)(?::(\S+))?\s+(\S+)\s+(\S+)\s+(\S+)$/osi) {
            $self->dbdisconnect($event);
            $self->{'dbhost'} = $1;
            $self->{'dbport'} = $2 || 3306;
            $self->{'dbname'} = $3;
            $self->{'dbuser'} = $4;
            $self->{'dbpass'} = $5;
            $self->saveConfig();
            $self->say($event, "Ok, trying to connect...");
            $self->verifyConnection($event);
        } elsif ($data =~ m/^tables?(?:\s+(\S+))?$/osi) {
            if ($self->{dbhandle}) {
                if ($1) {
                    $self->{tableName} = $1;
                    $self->sanitiseTableName();
                }
                if ($self->dbcreatetables($event)) {
                    $self->say($event, "Connected (using table '$self->{tableName}').");
                } else {
                    $self->say($event, "Failed to create the table ('$self->{dberror}') -- make sure you have the right permissions set up.");
                }
            } else {
                $self->say($event, 'I haven\'t yet successfully connected to a database. Please select a MySQL server to connect to, e.g. \'setupquotes mozbotquotes.damowmow.com:3306 mozbotquotes mozbotquotes mozbotquotes\'');
            }
        } elsif ($data =~ m/^use\s*tables?\s+(\S+)$/osi) {
            $self->{tableName} = $1;
            $self->sanitiseTableName();
            if ($self->{dbhandle}) {
                if (not $self->dbcheckconfig($event)) {
                    if (@{$self->{dbtables}}) {
                        local $" = '\', \'';
                        $self->say($event, "The table you requested, '$self->{tableName}', doesn't exist in this database. The tables in this database are: '@{$self->{dbtables}}'");
                        $self->say($event, "To make me create this new table (called '$self->{tableName}') use 'setupquotes table'. To make me use one of the tables from the list above, use 'setupquotes use table $self->{dbtables}->[0]' (or whatever table you want to use).");
                    } else {
                        $self->say($event, "The table you requested, '$self->{tableName}', doesn't exist in this database. In fact this database has no tables at all. If you want me to create a table (called '$self->{tableName}') for you, use 'setupquotes tables'.");
                    }
                } else {
                    $self->say($event, "Connected (using table '$self->{tableName}').");
                }
            } else {
                $self->say($event, 'Noted. However, I haven\'t yet successfully connected to a database, so this is not enough to complete configuration.');
                $self->say($event, 'Please select a MySQL server to connect to, e.g. \'setupquotes mozbotquotes.damowmow.com:3306 mozbotquotes mozbotquotes mozbotquotes\'');
            }
        } elsif ($data =~ m/^\s*$/osi) {
            $self->dbdisconnect($event);
            $self->say($event, "Checking connection...");
            $self->verifyConnection($event);
        } else {
            $self->say($event, 'The format is: \'setupquotes host.domain.tld:port database username password\' (\':port\' is optional, defaults to 3306) or just \'setupquotes\' to check the configuration.');
        }
    } elsif ($message =~ /^\s*quote(?:\s+(.+?))?\s*$/osi) {
        $self->getQuote($event, $1);
    } elsif ($message =~ /^\s*(?:quotelast|last\s*quote)(?:\s+(.+?))?\s*$/osi) {
        $self->getLastQuote($event, $1);
    } elsif ($message =~ /^\s*add\s*quote(?:\s+(.+?))?\s*$/osi) {
        $self->addQuote($event, $1);
    } elsif ($message =~ /^\s*(?:delete|del|remove|rem)?\s*quote(?:\s+(.+?))?\s*$/osi) {
        $self->deleteQuote($event, $1);
    } elsif ($message =~ /^\s*edit\s*quote(?:\s+(.+?))?\s*$/osi) {
        $self->editQuote($event, $1);
    } elsif ($message =~ /^\s*(?:quotes?\s*)?status\s*$/osi) {
        $self->printStatus($event);
    } elsif ($self->checkBangCommands(@_)) {
        return $self->SUPER::Told(@_);
    }
    return 0; # we've dealt with it, no need to do anything else.
}

sub Heard {
    my $self = shift;
    if ($self->checkBangCommands(@_)) {
        return $self->SUPER::Heard(@_);
    }
    return 0; # we've dealt with it, no need to do anything else.
}

sub checkBangCommands {
    my $self = shift;
    my ($event, $message) = @_;
    if ($message =~ /^$self->{prefix}quote(?:\s+(.+?))?\s*$/si) {
        $self->getQuote($event, $1);
    } elsif ($message =~ /^$self->{prefix}(?:quotelast|lastquote)(?:\s+(.+?))?\s*$/si) {
        $self->getLastQuote($event, $1);
    } elsif ($message =~ /^$self->{prefix}addquote(?:\s+(.+?))?\s*$/si) {
        $self->addQuote($event, $1);
    } elsif ($message =~ /^$self->{prefix}delquote(?:\s+(.+?))?\s*$/si) {
        $self->deleteQuote($event, $1);
    } elsif ($message =~ /^$self->{prefix}editquote(?:\s+(.+?))?\s*$/si) {
        $self->editQuote($event, $1);
    } else {
        return 1; # nope
    }
    return 0; # we've dealt with it, no need to do anything else.
}

sub markRead {
    my $self = shift;
    my ($id) = @_;
    eval {
        $self->{dbhandle}->do("UPDATE $self->{tableName} SET shown = shown + 1 WHERE id = ?", undef, $id);
        $self->{dbhandle}->do("UPDATE $self->{tableName} SET age = age + 1");
    };
    # ignore errors (don't have to worry about timeouts, this is only
    # ever done after recent db access)
}

sub getQuote {
    my $self = shift;
    my ($event, $data) = @_;
    if (not $self->{dbhandle}) {
        $self->say($event, "$event->{from}: I haven't got a connection to a database yet, sorry.");
        return;
    }
    if (defined $data) {
        if ($data =~ m/^\s*([0-9]+)\s*$/os) {
            $self->getQuoteById($event, $1);
        } else {
            $self->searchQuote($event, $data);
        }
    } else {
        $self->randomQuote($event);
    }
}

sub randomQuoteInternal {
    my $self = shift;
    my ($event) = @_;
    my($id, $quote, $author, $note);
    return 0 unless $self->attempt($event, sub { ($id, $quote, $author, $note) = $self->{dbhandle}->selectrow_array("SELECT id, quote, author, note, shown/age AS freq FROM $self->{tableName} ORDER BY freq, RAND() LIMIT 1", undef); }, 'read from the database for some reason', 'read a random quote from');
    if (defined $quote) {
        $self->markRead($id);
        $note = defined $note ? " ($note)" : '';
        $self->say($event, "Quote $id: $quote - $author$note");
        return 0;
    }
    return 1; # try again
}

sub randomQuote {
    my $self = shift;
    my ($event) = @_;
    $self->sanitiseTableName();
    if ($self->randomQuoteInternal($event)) {
        # no quotes?
        # weird... let's see if reconnecting helps
        if ($self->dbconnect()) {
            if ($self->randomQuoteInternal($event)) {
                # there must really be no quotes
                $self->say($event, "$event->{from}: There are no quotes in the database yet.");
            } # else ok
        } else {
            $self->say($event, "$event->{from}: I'm sorry, I can't reach the database right now.");
            $self->tellAdmin($event, "While trying to get a random quote from the database, I found no quotes, so I tried reconnecting to the database, but it said '$self->{dberror}'!");
        }
    } # else ok
}

sub getQuoteById {
    my $self = shift;
    my ($event, $id, $action) = @_;
    $self->sanitiseTableName();
    my($quote, $author, $note);
    return unless $self->attempt($event, sub {
        ($quote, $author, $note) = $self->{dbhandle}->selectrow_array("SELECT quote, author, note FROM $self->{tableName} WHERE id=?", undef, $id);
    }, 'read from the database for some reason', 'read a quote from');
    if (defined $quote) {
        $self->markRead($id);
        $note = defined $note ? " ($note)" : '';
        $action = defined $action ? "$action: " : '';
        $self->say($event, "\u${action}Quote $id: $quote - $author$note");
    } elsif (defined $action) {
        return 0;
    } else {
        $self->say($event, "$event->{from}: There is no quote with ID $id as far as I can tell.");
    }
    return 1;
}

sub searchQuote {
    my $self = shift;
    my ($event, $data) = @_;
    # [author=""] [text=""] [note=""] [text] [n]
    my (@columns, @values);
    my $skip = 0;
    while (length $data) {
        if ($data =~ s/^\s*text="([^"]*)"(?:\s|\z)//osi or
            $data =~ s/^\s*text='([^']*)'(?:\s|\z)//osi or
            $data =~ s/^\s*text=(\S+)(?:\s|\z)//osi) {
            push(@columns, 'quote LIKE ?');
            push(@values, "%$1%");
        } elsif ($data =~ s/^\s*author="([^"]*)"(?:\s|\z)//osi or
                 $data =~ s/^\s*author='([^']*)'(?:\s|\z)//osi or
                 $data =~ s/^\s*author=(\S+)(?:\s|\z)//osi) {
            push(@columns, 'author LIKE ?');
            push(@values, "%$1%");
        } elsif ($data =~ s/^\s*note="([^"]*)"(?:\s|\z)//osi or
                 $data =~ s/^\s*note='([^']*)'(?:\s|\z)//osi or
                 $data =~ s/^\s*note=(\S+)(?:\s|\z)//osi) {
            push(@columns, 'note LIKE ?');
            push(@values, "%$1%");
        } elsif ($data =~ s/^\s*(\w+)="([^"]*)"(?:\s|\z)//osi or
                 $data =~ s/^\s*(\w+)='([^']*)'(?:\s|\z)//osi or
                 $data =~ s/^\s*(\w+)=(\S+)(?:\s|\z)//osi) {
            $self->say($event, "$event->{from}: I don't know how to search for '$1'. The valid search types are 'author', 'note', and 'text'. See the help entry for 'quote' for more information on the quote searching syntax.");
            return;
        } elsif ($data =~ s/^\s*([0-9]+)\s*$//osi) {
            $skip = $1 - 1;
        } elsif ($data =~ s/^\s*"([^"]+)"(?:\s|\z)//osi or
                 $data =~ s/^\s*'([^']+)'(?:\s|\z)//osi or
                 $data =~ s/^\s*(\S+)(?:\s|\z)//osi) {
            push(@columns, 'quote LIKE ?');
            push(@values, "%$1%");
        } else {
            # wtf
            $self->say($event, "$event->{from}: I didn't quite understand what you were looking for ('$data'?). See the help entry for 'quote' for more information on the quote searching syntax.");
            return;
        }
    }

    $self->sanitiseTableName();
    my($id, $count, $quote, $author, $note);
    return unless $self->attempt($event, sub {
        local $" = ' AND ';
        ($count) = $self->{dbhandle}->selectrow_array("SELECT COUNT(*) FROM $self->{tableName} WHERE @columns", undef, @values);
        ($id, $quote, $author, $note) = $self->{dbhandle}->selectrow_array("SELECT id, quote, author, note FROM $self->{tableName} WHERE @columns LIMIT $skip,1", undef, @values);
    }, 'read from the database for some reason', 'search for a quote in');
    if (defined $quote) {
        $self->markRead($id);
        $note = defined $note ? " ($note)" : '';
        my $n = $skip + 1;
        $count = "about $n" if $count < $n; # sanitise output in case of race condition
        my $match = $count == 1 ? 'only match' : "match $n of $count";
        $self->say($event, "Quote $id ($match): $quote - $author$note");
    } else {
        $self->say($event, "$event->{from}: No matching quotes found.");
    }
}

sub getLastQuote {
    my $self = shift;
    my ($event, $data) = @_;
    if (not $self->{dbhandle}) {
        $self->say($event, "$event->{from}: I haven't got a connection to a database yet, sorry.");
        return;
    }
    if ($data !~ m/^\s*([0-9]+)?\s*$/os) {
        $self->say($event, "$event->{from}: The syntax is 'lastquote 2', where 2 is the number of the quote to show (counting from the end). You can omit the number to get the last quote added.");
        return;
    }
    my $skip = ($1 || 1) - 1;
    $self->sanitiseTableName();
    my($id, $quote, $author, $note);
    return unless $self->attempt($event, sub {
        ($id, $quote, $author, $note) = $self->{dbhandle}->selectrow_array("SELECT id, quote, author, note FROM $self->{tableName} ORDER BY id DESC LIMIT $skip,1", undef);
    }, 'read from the database for some reason', 'read the last few quotes from the database');
    if (defined $quote) {
        $self->markRead($id);
        $note = defined $note ? " ($note)" : '';
        $self->say($event, "Quote $id: $quote - $author$note");
    } else {
        $self->say($event, "$event->{from}: There are no quotes in the database yet.");
    }
}

sub addQuote {
    my $self = shift;
    my ($event, $data) = @_;
    if (not $self->canAdd($event)) {
        $self->say($event, "$event->{from}: You are not allowed to add quotes, sorry.");
        return;
    }
    if (not $self->{dbhandle}) {
        $self->say($event, "$event->{from}: I haven't got a connection to a database yet, sorry.");
        return;
    }
    # quote - author (note)
    if ($data =~ m/^ (.+\S)
                     \s* - \s*
                     (.+?)
                     (?:\s+\((.+)\))?
                   $/osx) {
        my $quote = $1;
        my $author = $2;
        my $note = $3;
        # insert data
        $self->sanitiseTableName();
        return unless $self->attempt($event, sub {
            $self->{dbhandle}->do("INSERT INTO $self->{tableName} SET
                                   quote = ?, author = ?, date = NOW(), note = ?",
                                 undef, $quote, $author, $note);
            my $id = $self->{dbhandle}->{mysql_insertid};
            if (not $self->getQuoteById($event, $id, 'inserted')) {
                $self->say($event, "$event->{from}: Your quote disappeared after I inserted it into the database. You may wish to speak to the other people who have access to the quotes database about this... :-)");
            }
        }, 'seem to add that quote to the database.', 'add a quote to');
    } else {
        $self->say($event, "$event->{from}: The syntax for adding a quote is 'quote - author' or 'quote - author (note)'.");
    }
}

sub deleteQuote {
    my $self = shift;
    my ($event, $data) = @_;
    if (not $self->canDelete($event)) {
        $self->say($event, "$event->{from}: You are not allowed to delete quotes, sorry.");
        return;
    }
    if (not $self->{dbhandle}) {
        $self->say($event, "$event->{from}: I haven't got a connection to a database yet, sorry.");
        return;
    }
    if ($data !~ m/^\s*([0-9]+)\s*$/os) {
        $self->say($event, "$event->{from}: The syntax is 'delquote 5', where 5 is the id of the quote to delete.");
        return;
    }
    my $id = $1;
    $self->sanitiseTableName();
    my($quote, $author, $note);
    return unless $self->attempt($event, sub {
        ($quote, $author, $note) = $self->{dbhandle}->selectrow_array("SELECT quote, author, note FROM $self->{tableName} WHERE ID=?", undef, $id);
    }, 'read from the database for some reason', 'read a quote to delete from');
    if (defined $quote) {
        return unless $self->attempt($event, sub {
            $self->{dbhandle}->do("DELETE FROM $self->{tableName} WHERE ID=?", undef, $id);
        }, 'delete from the database. Maybe I don\'t have enough privileges on the database server', 'delete from');
        $note = defined $note ? " ($note)" : '';
        $self->say($event, "Deleted: Quote $id: $quote - $author$note");
    } else {
        $self->say($event, "$event->{from}: There is no quote with ID $id as far as I can tell.");
    }
}

sub editQuote {
    my $self = shift;
    my ($event, $data) = @_;
    if (not $self->canEdit($event)) {
        $self->say($event, "$event->{from}: You are not allowed to edit quotes, sorry.");
        return;
    }
    if (not $self->{dbhandle}) {
        $self->say($event, "$event->{from}: I haven't got a connection to a database yet, sorry.");
        return;
    }
    if ($data =~ m/^ ([0-9]+) \s+
                     (.+\S)
                     \s* - \s*
                     (.+?)
                     (?:\s+\((.+)\))?
                   $/osx) {
        my $id = $1;
        my $quote = $2;
        my $author = $3;
        my $note = $4;
        # insert data
        $self->sanitiseTableName();
        return unless $self->attempt($event, sub {
            $self->{dbhandle}->do("UPDATE $self->{tableName} SET
                                   quote = ?, author = ?, note = ?
                                   WHERE id = ?",
                                 undef, $quote, $author, $note, $id);
            if (not $self->getQuoteById($event, $id, 'edited')) {
                $self->say($event, "$event->{from}: I couldn't find a quote with ID $id.");
            }
        }, 'seem to edit that quote', 'edit a quote in');
    } else {
        $self->say($event, "$event->{from}: The syntax for editing a quote is 'id quote - author' or 'id quote - author (note)', much like for adding a quote but with the id of the quote to edit at the start.");
    }
}

sub printStatus {
    my $self = shift;
    my ($event) = @_;
    if (not $self->{dbhandle}) {
        $self->say($event, "$event->{from}: No connection could be established to the quotes datbase.");
        return;
    }
    $self->sanitiseTableName();
    my ($quotes, $sources, $shown, $id) = @_;
    return unless $self->attempt($event, sub {
        ($quotes, $sources, $shown) = $self->{dbhandle}->selectrow_array("SELECT COUNT(*), COUNT(DISTINCT author), SUM(shown) FROM $self->{tableName}");
        ($id) = $self->{dbhandle}->selectrow_array("SELECT id, shown/age AS freq FROM $self->{tableName} ORDER BY freq, shown LIMIT 1");
    }, 'connect to the quotes database', 'obtain statistics of');
    if ($quotes) {
        my $s1 = $quotes == 1 ? '' : 's';
        my $s2 = $sources == 1 ? '' : 's';
        my $s3 = $shown == 1 ? '' : 's';
        $self->say($event, "$event->{from}: The database contains $quotes quote$s1 attributed to $sources source$s2. I have shown these quotes $shown time$s3 in total. The most popular quote (relatively speaking) is quote ID $id.");
    } else {
        $self->say($event, "$event->{from}: The database contains 0 quotes.");
    }
}

sub attempt {
    my $self = shift;
    my($event, $sub, $action1, $action2) = @_;
    eval { &$sub };
    if ($@) {
        chomp $@;
        my $error = $@;
        # A common error is:
        # "DBD::mysql::db selectrow_array failed: MySQL server has
        # gone away at (eval 34) line 357."
        # ...so we try to reconnect and do it again
        if ($self->dbconnect()) {
            eval { &$sub };
            if ($@) {
                chomp $@;
                $self->say($event, "$event->{from}: I'm sorry, I can't $action1.");
                if ($@ eq $error) {
                    $self->tellAdmin($event, "While trying to $action2 the database, I got '$@'. I tried reconnecting but that didn't help.");
                } else {
                    $self->tellAdmin($event, "While trying to $action2 the database, I got '$error'. Then I tried reconnecting and it worked but when I tried to $action2 the database a second time, it said '$@'.");
                }
                return 0;
            }
        } else {
            $self->say($event, "$event->{from}: I'm sorry, I can't $action1.");
            $self->tellAdmin($event, "While trying to $action2 the database, I got '$error', so I tried reconnecting to the database but I got '$self->{dberror}'. Help!");
            return 0;
        }
    }
    return 1;
}