lyrics-to-sqlite

#!/usr/bin/perl

# lyrics-to-sqlite, a tool for filling an SQLite database with lyrics.
# Copyright (C) 2019 defanor <defanor@uberspace.net>

# This program is free software: you can redistribute it and/or modify
# it under the terms of the GNU Affero General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.

# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.

# You should have received a copy of the GNU Affero General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

use strict;
use warnings;
use DBI;
use File::Basename;
use LyricsDB::Preprocessing;

die "Usage: lyrics-to-sqlite.pl <database directory> <sqlite db file>"
    unless ($#ARGV >= 1);
my $dirname = shift @ARGV;
my $database = shift @ARGV;

die "The database file already exists" if (-f $database);

my $dbh = DBI->connect("DBI:SQLite:dbname=$database", "", "")
    or die $DBI::errstr;
$dbh->begin_work;

$dbh->do(q(
         CREATE TABLE lyrics (
         lyrics_id INTEGER PRIMARY KEY,
         artist TEXT NOT NULL,
         album TEXT NOT NULL,
         title TEXT NOT NULL,
         text TEXT NOT NULL
         )));
$dbh->do(q(
         CREATE TABLE aliases (
         lyrics_id INTEGER NOT NULL,
         alias TEXT NOT NULL,
         alias_column TEXT NOT NULL,
         alias_type TEXT NOT NULL,
         FOREIGN KEY (lyrics_id) REFERENCES lyrics(lyrics_id)
         )));
my $insert_q = $dbh->prepare(
    "INSERT INTO lyrics (artist, album, title, text) VALUES (?, ?, ?, ?)");
my $alias_q = $dbh->prepare("INSERT INTO aliases VALUES (?, ?, ?, ?)");

sub add_aliases {
    my ($lyrics_id, $alias_column, $original) = @_;
    my %aliases = preprocess($original);
    for my $alias_type (keys %aliases) {
        $alias_q->execute($lyrics_id,
                          $aliases{$alias_type},
                          $alias_column,
                          $alias_type);
    }
}

for my $path (glob("$dirname/*/*/*/*")) {
    my $title = basename $path;
    my $album = basename(dirname $path);
    my $artist = basename(dirname(dirname $path));
    my $lyrics = do { local( @ARGV, $/ ) = $path; <> };
    $insert_q->execute($artist, $album, $title, $lyrics);
    my $lyrics_id = $dbh->last_insert_id("", "", "", "");
    add_aliases($lyrics_id, 'artist', $artist);
    add_aliases($lyrics_id, 'album', $album);
    add_aliases($lyrics_id, 'title', $title);
}

$dbh->do("CREATE INDEX idx_aliases_column ON aliases (alias_column, alias)");

$dbh->commit;
$dbh->disconnect();