qdb.pl by Andreas Guldstrand

This is a quote database script written in perl


Prerequisites
=============

To use this quote database, you will need:
  * PostgreSQL
  * perl 5.10.1 or higher
  * Mojolicious
  * Mojo::Pg
  * Regexp::Common
  * DBD::Pg

If you've got sqlite, postgresql, and perl, you can install the other
prerequisites by:

   sudo cpan Mojo::Pg
                        # ^ this will pull in Mojolicious and DBD::Pg as well
   sudo cpan Regexp::Common

Please note that sqlite is only needed for installing
Mojolicious::Plugin::Database; it is not needed after that is done (unless you
need to import an old sqlite database into postgresql).


Configuration
=============

There is a qdb.conf.sample file included, which you can copy or move to
qdb.conf, and edit to your likings.

   cp qdb.conf.sample qdb.conf
   vim qdb.conf

The hypnotoad settings are for which address(es) to bind the listening socket
to, and under which username/group to run. It's important that the user you
specify has read/write access to the directory where the qdb.db (see later)
will be located. If you intend to listen on port 80 (or any other port below
1024) you'll need to start it under a privileged account. It will switch to the
specified user after it has opened the port(s) it needs.

The title setting is for setting the main title of the qdb website.

The secrets setting is an array of "secrets" that will be used to encrypt the
cookies Mojolicious sends; if this is a guessable string, someone can
impersonate an admin without much difficulty. So make sure it's difficult to
guess. This is not something you will need to remember either, so random crap
is fine.

And lastly there's an admins hash, where each key is an admin username,
and each value is a password. You can add as many of these as you want, but
you need at least one. Otherwise you'll be unable to edit/delete/approve any
quotes.

If you use sudo to load hypnotoad, you will likely need to specify the username
parameter for the database to use as well, or it will try to use it as root,
which may not be what you want.

Make sure there is a database created that the script can use:

   createdb -E UTF-8 qdb

If you have another database you want to use, change the dsn in the database
hash accordingly.


Running the qdb
===============

Mojolicious comes with the hypnotoad webserver, which you'll run by just doing:

   sudo hypnotoad qdb.pl

in the directory you extracted the qdb script to.

If you ever need to update the script, you can just rerun the hypnotoad
command; it will kill the old script gracefully with 0 downtime, and replace it
with the current version.


Not using hypnotoad
===================

Mojolicious is easy to get going as a normal CGI script, or using FastCGI, or
even under mod_perl, but it will be most efficient to run it under hypnotoad.
Therefore, other solutions will be up to the user to figure out.


Troubleshooting
===============

If something goes wrong, and hypnotoad doesn't tell you anything, try running
it in the foreground instead:

   sudo hypnotoad -f qdb.pl

in the directory you extracted the qdb script to. This should tell you what
it's doing, and help seeing where things go wrong.


Migrating from SQLite
=====================

There is an import.pl script included that will use the qdb.conf to connect to
a PostgreSQL database and read in quotes from an SQLite database into it:

   ./import.pl qdb.db # if your old SQLite database is in qdb.db

It is far from perfect, so manual finetuning may be needed, in particular how
you update the quotes_id_seq sequence differs between PostgreSQL versions.


Tuning PostgreSQL
=================

You may want to create some indexes for your quotes, particularly one for the
text column in lowercase:

   CREATE INDEX quotes_lower_text_idx ON quotes (LOWER(text) text_pattern_ops);

For more suggestions, contact your DBA.