#!/usr/bin/perl
use strict;
use warnings;
use PerlIO::gzip;
use JSON;
use Encode;

# Sample OpenLibrary editions entry from the
# "editions" dump at http://openlibrary.org/developers/dumps

# /type/edition	/books/OL10000179M	4	2010-04-24T17:54:01.503315	{"publishers": ["Stationery Office"], "physical_format": "Hardcover", "subtitle": "26 January - 4 February 1998", "title": "Parliamentary Debates, House of Lords, 1997-98", "isbn_10": ["0107805855"], "identifiers": {"goodreads": ["2862283"]}, "isbn_13": ["9780107805852"], "edition_name": "5th edition", "languages": [{"key": "/languages/eng"}], "number_of_pages": 124, "last_modified": {"type": "/type/datetime", "value": "2010-04-24T17:54:01.503315"}, "latest_revision": 4, "key": "/books/OL10000179M", "authors": [{"key": "/authors/OL2645811A"}], "publish_date": "January 1999", "works": [{"key": "/works/OL7925994W"}], "type": {"key": "/type/edition"}, "subjects": ["Bibliographies, catalogues, discographies", "POLITICS & GOVERNMENT", "Reference works", "Bibliographies & Indexes", "Reference"], "revision": 4}

my $max_rows = 1000000;

open(OUTFH, '>', 'openlib.tsv');

print OUTFH <<HERE;
DROP TABLE IF EXISTS openlib;
CREATE TABLE IF NOT EXISTS openlib (id INT, title TEXT, subjects TEXT, unweighted_kw TSVECTOR, weighted_kw TSVECTOR);
SELECT DATE_TRUNC('second', NOW()) || ' - started loading' AS timer;
COPY openlib (id, title, subjects) FROM STDIN;
HERE

binmode(OUTFH);

open(OLFH, '<:gzip', 'ol_dump_editions_latest.txt.gz');
my $i = 0;
while(<OLFH>) {
    $i++;
    my @cols = split /\t/;
    my $work_id = $cols[1];
    $work_id =~ s{^/books/OL(\d+)M$}{$1};
    my $metadata = decode_json($cols[4]);
    my $title = encode_utf8(decode_utf8($metadata->{title}));
    my $subjects = $metadata->{subjects};
    if ($subjects) {
        $subjects = encode_utf8(join(" ", @$subjects));
        $subjects =~ s{\\}{}g;
        $subjects =~ s{\t}{ }g;
        $subjects =~ s{\n}{ }g;
    } else {
        $subjects = '\N';
    }
    $title =~ s{\\}{}g;
    $title =~ s{\t}{}g;
    $title =~ s{\n}{}g;
    print OUTFH "$work_id\t$title\t$subjects\n";
    last if $i > $max_rows;
}
close(OLFH);
print OUTFH '\.' . "\n";

print OUTFH <<HERE;
SELECT DATE_TRUNC('second', NOW()) || ' - finished loading' AS timer;
UPDATE openlib SET unweighted_kw = to_tsvector('english', title) || to_tsvector('english', COALESCE(subjects, ''));
SELECT DATE_TRUNC('second', NOW()) || ' - populated unweighted_kw' AS timer;
UPDATE openlib SET weighted_kw = setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', COALESCE(subjects, '')), 'D');
SELECT DATE_TRUNC('second', NOW()) || ' - populated weighted_kw' AS timer;
CREATE INDEX openlib_title ON openlib USING GIN (to_tsvector('english', title));
SELECT DATE_TRUNC('second', NOW()) || ' - created openlib_title index' AS timer;
CREATE INDEX openlib_subjects ON openlib USING GIN (to_tsvector('english', subjects));
SELECT DATE_TRUNC('second', NOW()) || ' - created openlib_subjects index' AS timer;
CREATE INDEX openlib_unweighted ON openlib USING GIN (unweighted_kw);
SELECT DATE_TRUNC('second', NOW()) || ' - created openlib_unweighted index' AS timer;
CREATE INDEX openlib_weighted ON openlib USING GIN (weighted_kw);
SELECT DATE_TRUNC('second', NOW()) || ' - created openlib_weighted index' AS timer;
HERE

close(OUTFH);
