our $dbh = DBI->connect("dbi:SQLite:dbname=lastfm_cache.db", '', '', { sqlite_unicode => 1 });
$dbh->do("PRAGMA foreign_keys = ON");

$dbh->do(<<'');
CREATE TABLE IF NOT EXISTS usernames (uid INTEGER PRIMARY KEY, username TEXT UNIQUE, valid INTEGER);

$dbh->do(<<'');
CREATE TABLE IF NOT EXISTS nicknames (nickname TEXT UNIQUE, uid INTEGER NOT NULL,
    FOREIGN KEY (uid) REFERENCES usernames(uid));

$dbh->do(<<'');
CREATE TABLE IF NOT EXISTS artists (artid INTEGER PRIMARY KEY, artist TEXT UNIQUE, timestamp INTEGER);

$dbh->do(<<'');
CREATE TABLE IF NOT EXISTS artist_tag_cache (artid INTEGER, tag TEXT,
    votes INTEGER, FOREIGN KEY (artid) REFERENCES artists(artid));

my %prep = (
    clean => {
        artist_tag_cache => $dbh->prepare(<<''),
DELETE FROM artist_tag_cache WHERE
    artid = (SELECT artid FROM artists WHERE
        artists.artid = artist_tag_cache.artid AND
        artists.timestamp < ?);

        },
    get => {
        nicks_for_user => $dbh->prepare(<<''),
SELECT nickname FROM nicknames NATURAL JOIN usernames WHERE username LIKE ?;

        user_for_nick => $dbh->prepare(<<''),
SELECT username FROM usernames NATURAL JOIN nicknames WHERE nickname LIKE ?;

        valid_users => $dbh->prepare(<<''),
SELECT username FROM usernames WHERE valid = 1;

        is_user_valid => $dbh->prepare(<<''),
SELECT valid FROM usernames WHERE username LIKE ?;

        tags_for_artist => $dbh->prepare(<<''),
SELECT tag FROM artist_tag_cache NATURAL JOIN artists WHERE
    artist LIKE ? ORDER BY votes;

        },
    add => {
        user => $dbh->prepare(<<''),
INSERT INTO usernames (username, valid) VALUES (?, ?);

        nickname => $dbh->prepare(<<''),
INSERT INTO nicknames (nickname, uid) VALUES
    (?, (SELECT uid FROM usernames WHERE username LIKE ?));

        artist => $dbh->prepare(<<''),
INSERT INTO artists (artist, timestamp) VALUES (?, strftime('%s', 'now'));

        artist_tag => $dbh->prepare(<<''),
INSERT INTO artist_tag_cache (artid, tag, votes) VALUES
    ((SELECT artid FROM artists WHERE artist LIKE ?), ?, ?);

        },
    );