#!/usr/bin/env python
# vim: set fileencoding=utf-8 :
"""
    Usage: cat bookpedia.txt | ./bookpedia_to_mysql.py | mysql -p bookpedia

    Reads a bookpedia Full-utf8.txt file and dumps the result to
    a set of MySQL statements.

    Copyright © 2007, Christopher Swingley <cswingle@gmail.com>
    and licensed under the terms of the GNU General Public License 2.0

    Visit http://people.iarc.uaf.edu/~cswingle/books/software.php for the
    current version of this program.

    Note:  I've made one customization to the Bookpedia database: I changed
    CustomOne to Copyright, and I put the Copyright year in this field.  The
    Copright year will differ from the release date for anything except
    first printings, and it's often more useful to know when an author 
    published a book, rather than when a publisher reprinted it, especially
    for classics.

    You will also need the Full-UTF8.txt template for Bookpedia.  Put it
    in ~/Library/Application\ Support/Bookpedia/Templates/

    Bookpedia works surprisingly hard to generate an export of a large
    database using this export template, so you may need to export your
    database in pieces.  When my database neared 1,300 books, Bookpedia
    started crashing when I exported the entire thing, so I had to use
    a couple smart collections to divide it into pieces that could be
    exported.

    Changelog:

    v0.9 2007-May-11    Initial release

         Christopher Swingley <cswingle@gmail.com>

"""
import re
import sys
import random
import codecs
DEFAULT_ENCODING = 'utf-8'
sys.stdout = codecs.getwriter(DEFAULT_ENCODING)(sys.stdout)

fields = ("Title", "Author", "Publisher", "Illustrator", "Translator",
        "Editor", "Genre", "Pages", "Edition", "Format", "Release", "Year",
        "Month", "ISBN", "Asin", "PurchasedAt", "PurchasedOn", "ListPrice",
        "MarketPrice", "Summary", "Subjects", "CoverImageURL", "Comments",
        "Notes", "LastRead", "Awards", "BorrowedBy", "BorrowedOn",
        "BuyerAddress", "BuyerEmail", "BuyerName", "Children", "Collection",
        "Condition", "Copyright", "CustomTwo", "CustomThree", "CustomFour",
        "DateAdded", "Dewey", "Dimensions", "DueDate", "Gift", "LCCN",
        "Locale", "Location", "MyRating", "OnSale", "PageMark", "PaidPrice",
        "AskingPrice", "PlacedForSaleAt", "PlacePublished", "ProductURL",
        "ReaderRating", "Returned", "Series", "Signed", "SoldBook", "SoldOn",
        "SoldPrice")

field_re = re.compile('^([^ ]+):(.*)$')
debug = False
# debug = True

def safe_float(string, length, precision):
    """ Formats a string to floating point length and precision, returns a string result 
        Handles currency markers ($,£)"""
    format = "%%%d.%df" % (length, precision)
    currencies = u"$£"
    if string and string[0] in currencies:
        string = string[1:]
    try:
        result = format % (float(string))
    except:
        result = "NULL"
    return result

def safe_int(string, length):
    """ Formats a string to an integer with length, returns a string """
    format = "%%%dd" % (length)
    try:
        result = format % (int(string))
    except:
        result = "NULL"
    return result

def fix_date(date):
    """ Converts a date in MMM YYYY format to a SQL date YYYY-MM-14 """
    months_dict = {'Jan':'1', 'Feb':'2', 'Mar':'3', 'Apr':'4',  'May':'5',  'Jun':'6',
                   'Jul':'7', 'Aug':'8', 'Sep':'9', 'Oct':'10', 'Nov':'11', 'Dec':'12'}

    try:
        (month, year) = date.split()
        return "'%d-%02d-14'" % (int(year), int(months_dict[month]))
    except:
        return "NULL"

def fix_strings(string):
    """ Returns the string in single quotes, or NULL """
    if string:
        return "'%s'" % string
    else:
        return "NULL"

def fix_cover_url(url):
    """ Removes everything but the NNN.jpg from the URL """
    try:
        return "'%s'" % re.search('^.*/([0-9]+.jpg)$', url).group(1)
    except:
        return "NULL"

def find_reader_ratings(rating_string):
    """ Return a list of rating, and number of readers or null from
        4.0 (55 votes) """
    matches = re.match('([0-9.]+) \(([0-9]+) votes\)', rating_string)
    if matches:
        return ("'%s'" % matches.group(1), "'%s'" % matches.group(2))
    else:
        return ("NULL", "NULL")

def dump_sql_creates():
    """ Prints out the SQL DROP / CREATE TABLE statements """
    print """DROP TABLE IF EXISTS books;
    CREATE TABLE books (
        book_id         VARCHAR(13) NOT NULL,
        title           VARCHAR(255) NOT NULL,
        publisher_id    SMALLINT DEFAULT NULL,
        genre_id        SMALLINT DEFAULT NULL,
        pages           SMALLINT DEFAULT NULL,
        edition_id      SMALLINT DEFAULT NULL,
        format_id       SMALLINT DEFAULT NULL,
        release_date    DATE DEFAULT NULL,
        isbn            VARCHAR(13) DEFAULT NULL,
        asin            VARCHAR(13) DEFAULT NULL,
        vendor_id       SMALLINT DEFAULT NULL,
        purchased_on    DATE DEFAULT NULL,
        list_price      DECIMAL(7,2) DEFAULT NULL,
        market_price    DECIMAL(7,2) DEFAULT NULL,
        summary         TEXT DEFAULT NULL,
        cover_image_url VARCHAR(512) DEFAULT NULL,
        comments        TEXT DEFAULT NULL,
        notes           TEXT DEFAULT NULL,
        last_read       DATE DEFAULT NULL,
        children_id     SMALLINT DEFAULT NULL,
        copyright       SMALLINT DEFAULT NULL,
        date_added      DATE DEFAULT NULL,
        gifter_id       SMALLINT DEFAULT NULL,
        my_rating       TINYINT DEFAULT NULL,
        paid_price      DECIMAL(7,2) DEFAULT NULL,
        product_url     VARCHAR(512) DEFAULT NULL,
        reader_rating   DECIMAL(4,1) DEFAULT NULL,
        reader_votes    SMALLINT DEFAULT NULL,
        PRIMARY KEY (book_id),
        KEY copyright (copyright),
        KEY purcahsed_on (purchased_on),
        KEY last_read (last_read),
        KEY isbn (isbn),
        KEY genre_id (genre_id)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS authors;
    CREATE TABLE authors (
        author_id       SMALLINT NOT NULL,
        first           VARCHAR(80) DEFAULT NULL,
        middle          VARCHAR(80) DEFAULT NULL,
        last            VARCHAR(80) DEFAULT NULL,
        suffix          VARCHAR(8) DEFAULT NULL,
        PRIMARY KEY (author_id),
        KEY last (last),
        KEY first_last (first, last)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS booksauthors;
    CREATE TABLE booksauthors (
        book_id         VARCHAR(13) NOT NULL,
        author_id       SMALLINT NOT NULL,
        rank            SMALLINT NOT NULL,
        KEY book_id (book_id),
        KEY author_id (author_id)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS editors;
    CREATE TABLE editors (
        editor_id       SMALLINT NOT NULL,
        first           VARCHAR(80) DEFAULT NULL,
        middle          VARCHAR(80) DEFAULT NULL,
        last            VARCHAR(80) DEFAULT NULL,
        suffix          VARCHAR(8) DEFAULT NULL,
        PRIMARY KEY (editor_id),
        KEY last (last),
        KEY first_last (first, last)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS bookseditors;
    CREATE TABLE bookseditors (
        book_id         VARCHAR(13) NOT NULL,
        editor_id       SMALLINT NOT NULL,
        rank            SMALLINT NOT NULL,
        KEY book_id (book_id),
        KEY editor_id (editor_id)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS translators;
    CREATE TABLE translators (
        translator_id   SMALLINT NOT NULL,
        first           VARCHAR(80) DEFAULT NULL,
        middle          VARCHAR(80) DEFAULT NULL,
        last            VARCHAR(80) DEFAULT NULL,
        suffix          VARCHAR(8) DEFAULT NULL,
        PRIMARY KEY (translator_id),
        KEY last (last),
        KEY first_last (first, last)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS bookstranslators;
    CREATE TABLE bookstranslators (
        book_id         VARCHAR(13) NOT NULL,
        translator_id   SMALLINT NOT NULL,
        rank            SMALLINT NOT NULL,
        KEY book_id (book_id),
        KEY translator_id (translator_id)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS illustrators;
    CREATE TABLE illustrators (
        illustrator_id  SMALLINT NOT NULL,
        first           VARCHAR(80) DEFAULT NULL,
        middle          VARCHAR(80) DEFAULT NULL,
        last            VARCHAR(80) DEFAULT NULL,
        suffix          VARCHAR(8) DEFAULT NULL,
        PRIMARY KEY (illustrator_id),
        KEY last (last),
        KEY first_last (first, last)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS booksillustrators;
    CREATE TABLE booksillustrators (
        book_id         VARCHAR(13) NOT NULL,
        illustrator_id  SMALLINT NOT NULL,
        rank            SMALLINT NOT NULL,
        KEY book_id (book_id),
        KEY illustrator_id (illustrator_id)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS publishers;
    CREATE TABLE publishers (
        publisher_id    SMALLINT NOT NULL,
        publisher       VARCHAR(80) DEFAULT NULL,
        PRIMARY KEY (publisher_id),
        KEY publisher (publisher)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS genres;
    CREATE TABLE genres (
        genre_id        SMALLINT NOT NULL,
        genre           VARCHAR(80) DEFAULT NULL,
        PRIMARY KEY (genre_id),
        KEY genre_id (genre_id)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS subjects;
    CREATE TABLE subjects (
        subject_id      SMALLINT NOT NULL,
        subject         VARCHAR(80) DEFAULT NULL,
        PRIMARY KEY (subject_id),
        KEY subject (subject)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS bookssubjects;
    CREATE TABLE bookssubjects (
        book_id         VARCHAR(13) NOT NULL,
        subject_id      SMALLINT NOT NULL,
        rank            SMALLINT NOT NULL,
        KEY book_id (book_id),
        KEY subject_id (subject_id)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS formats;
    CREATE TABLE formats (
        format_id       SMALLINT NOT NULL,
        format          VARCHAR(80) DEFAULT NULL,
        PRIMARY KEY (format_id),
        KEY format  (format)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS vendors;
    CREATE TABLE vendors (
        vendor_id       SMALLINT NOT NULL,
        vendor          VARCHAR(80) DEFAULT NULL,
        PRIMARY KEY (vendor_id),
        KEY vendor  (vendor)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS gifters;
    CREATE TABLE gifters (
        gifter_id       SMALLINT NOT NULL,
        gifter          VARCHAR(80) DEFAULT NULL,
        PRIMARY KEY (gifter_id),
        KEY gifter  (gifter)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS childrens;
    CREATE TABLE childrens (
        children_id     SMALLINT NOT NULL,
        children        VARCHAR(80) DEFAULT NULL,
        PRIMARY KEY (children_id),
        KEY children  (children)
    ) DEFAULT CHARACTER SET utf8;
    """
    print """DROP TABLE IF EXISTS editions;
    CREATE TABLE editions (
        edition_id       SMALLINT NOT NULL,
        edition          VARCHAR(80) DEFAULT NULL,
        PRIMARY KEY (edition_id),
        KEY edition  (edition)
    ) DEFAULT CHARACTER SET utf8;
    """

def parse_authors(authors):
    """ Reads an authors string, breaks it down into a list of 
        (first, middle, last, suffix) """
    parsed = []
    if authors:
        names = authors.split(',')
        for name in names:
            name = name.strip()
            parts = name.split()
            if len(parts) == 1:
                (first, middle, last, suffix) = ('', '', parts[0], '')
            else:
                if re.search('(Jr|Sr|II|III|IV).?', parts[-1]):
                    suffix = parts[-1]
                    parts = parts[:-1]
                else:
                    suffix = ""
                if re.search('(Mr|Mrs|Ms|Miss|Dr).?', parts[0]):
                    parts = parts[1:]
                first = parts[0]
                last = parts[-1]
                middle = " ".join(parts[1:-1])

            parsed.append((first, middle, last, suffix))
    return parsed

def insert_into_author_table(author_table, parsed):
    """ Takes the author table structure and inserts the authors in the
        parsed structure, returning a list of author_id's """
    id_list = []
    for author in parsed:
        (first, middle, last, suffix) = author
        if author_table.has_key('%s|%s|%s|%s' % (first, middle, last, suffix)):
            id = author_table['%s|%s|%s|%s' % (first, middle, last, suffix)]
        else:
            author_table['%s|%s|%s|%s' % (first, middle, last, suffix)] = author_table['MAX']
            id = author_table['MAX']
            print "INSERT INTO authors VALUES (%d, '%s', '%s', '%s', '%s');" % (id,
                    first, middle, last, suffix)
            author_table['MAX'] += 1
        id_list.append(id)
    return id_list
    
def insert_into_editor_table(editor_table, parsed):
    """ Takes the editor table structure and inserts the editors in the
        parsed structure, returning a list of editor_id's """
    id_list = []
    for editor in parsed:
        (first, middle, last, suffix) = editor
        if editor_table.has_key('%s|%s|%s|%s' % (first, middle, last, suffix)):
            id = editor_table['%s|%s|%s|%s' % (first, middle, last, suffix)]
        else:
            editor_table['%s|%s|%s|%s' % (first, middle, last, suffix)] = editor_table['MAX']
            id = editor_table['MAX']
            print "INSERT INTO editors VALUES (%d, '%s', '%s', '%s', '%s');" % (id,
                    first, middle, last, suffix)
            editor_table['MAX'] += 1
        id_list.append(id)
    return id_list

def insert_into_translator_table(translator_table, parsed):
    """ Takes the translator table structure and inserts the translators in the
        parsed structure, returning a list of translator_id's """
    id_list = []
    for translator in parsed:
        (first, middle, last, suffix) = translator
        if translator_table.has_key('%s|%s|%s|%s' % (first, middle, last, suffix)):
            id = translator_table['%s|%s|%s|%s' % (first, middle, last, suffix)]
        else:
            translator_table['%s|%s|%s|%s' % (first, middle, last, suffix)] = translator_table['MAX']
            id = translator_table['MAX']
            print "INSERT INTO translators VALUES (%d, '%s', '%s', '%s', '%s');" % (id,
                    first, middle, last, suffix)
            translator_table['MAX'] += 1
        id_list.append(id)
    return id_list

def insert_into_illustrator_table(illustrator_table, parsed):
    """ Takes the illustrator table structure and inserts the illustrators in the
        parsed structure, returning a list of illustrator_id's """
    id_list = []
    for illustrator in parsed:
        (first, middle, last, suffix) = illustrator
        if illustrator_table.has_key('%s|%s|%s|%s' % (first, middle, last, suffix)):
            id = illustrator_table['%s|%s|%s|%s' % (first, middle, last, suffix)]
        else:
            illustrator_table['%s|%s|%s|%s' % (first, middle, last, suffix)] = illustrator_table['MAX']
            id = illustrator_table['MAX']
            print "INSERT INTO illustrators VALUES (%d, '%s', '%s', '%s', '%s');" % (id,
                    first, middle, last, suffix)
            illustrator_table['MAX'] += 1
        id_list.append(id)
    return id_list

def insert_into_subjects_table(subject_table, parsed):
    """ Takes the subjects table structure and inserts the subjects in the
        parsed structure, returning a list of subject_id's """
    if parsed:
        id_list = []
        for subject in parsed:
            if subject_table.has_key(subject):
                id = subject_table[subject]
            else:
                subject_table[subject] = subject_table['MAX']
                id = subject_table['MAX']
                print "INSERT INTO subjects VALUES (%d, '%s');" % (id, subject)
                subject_table['MAX'] += 1
            id_list.append(id)
        return id_list
    else:
        return (0,)
    
def insert_into_publisher_table(publisher_table, publisher):
    """ Takes the publisher table structure and inserts the publisher in the
        parsed structure, returning the publisher_id """
    if publisher:
        if publisher_table.has_key(publisher):
            id = publisher_table[publisher]
        else:
            publisher_table[publisher] = publisher_table['MAX']
            id = publisher_table['MAX']
            print "INSERT INTO publishers (publisher_id, publisher) VALUES (%d, '%s');" % (id, publisher)
            publisher_table['MAX'] += 1
        return id
    else:
        return 0

def insert_into_genre_table(genre_table, genre):
    """ Takes the genre table structure and inserts the genre in the
        parsed structure, returning the genre_id """
    if genre_table.has_key(genre):
        id = genre_table[genre]
    else:
        genre_table[genre] = genre_table['MAX']
        id = genre_table['MAX']
        print "INSERT INTO genres (genre_id, genre) VALUES (%d, '%s');" % (id, genre)
        genre_table['MAX'] += 1
    return id

def insert_into_format_table(format_table, format):
    """ Takes the format table structure and inserts the format in the
        parsed structure, returning the format_id """
    if format:
        if format_table.has_key(format):
            id = format_table[format]
        else:
            format_table[format] = format_table['MAX']
            id = format_table['MAX']
            print "INSERT INTO formats (format_id, format) VALUES (%d, '%s');" % (id, format)
            format_table['MAX'] += 1
        return id
    else:
        return 0

def insert_into_vendor_table(vendor_table, vendor):
    """ Takes the vendor table structure and inserts the vendor in the
        parsed structure, returning the vendor_id """
    if vendor:
        if vendor_table.has_key(vendor):
            id = vendor_table[vendor]
        else:
            vendor_table[vendor] = vendor_table['MAX']
            id = vendor_table['MAX']
            print "INSERT INTO vendors (vendor_id, vendor) VALUES (%d, '%s');" % (id, vendor)
            vendor_table['MAX'] += 1
        return id
    else:
        return 0

def insert_into_gifter_table(gifter_table, gifter):
    """ Takes the gifter table structure and inserts the gifter in the
        parsed structure, returning the gifter_id """
    if gifter:
        if gifter_table.has_key(gifter):
            id = gifter_table[gifter]
        else:
            gifter_table[gifter] = gifter_table['MAX']
            id = gifter_table['MAX']
            print "INSERT INTO gifters (gifter_id, gifter) VALUES (%d, '%s');" % (id, gifter)
            gifter_table['MAX'] += 1
        return id
    else:
        return 0

def insert_into_edition_table(edition_table, edition):
    """ Takes the edition table structure and inserts the edition in the
        parsed structure, returning the edition_id """
    if edition:
        if edition_table.has_key(edition):
            id = edition_table[edition]
        else:
            edition_table[edition] = edition_table['MAX']
            id = edition_table['MAX']
            print "INSERT INTO editions (edition_id, edition) VALUES (%d, '%s');" % (id, edition)
            edition_table['MAX'] += 1
        return id
    else:
        return 0

def insert_into_children_table(children_table, children):
    """ Takes the children table structure and inserts the children in the
        parsed structure, returning the children_id """
    if children:
        if children_table.has_key(children):
            id = children_table[children]
        else:
            children_table[children] = children_table['MAX']
            id = children_table['MAX']
            print "INSERT INTO childrens (children_id, children) VALUES (%d, '%s');" % (id, children)
            children_table['MAX'] += 1
        return id
    else:
        return 0

def process_books(books):
    author_table = {}
    author_table['MAX'] = 1
    editor_table = {}
    editor_table['MAX'] = 1
    translator_table = {}
    translator_table['MAX'] = 1
    illustrator_table = {}
    illustrator_table['MAX'] = 1
    subject_table = {}
    # subject_table['0'] = 'None'
    subject_table['MAX'] = 1
    print "INSERT INTO subjects (subject_id, subject) VALUES (0, NULL);"
    publisher_table = {}
    publisher_table['MAX'] = 1
    print "INSERT INTO publishers (publisher_id, publisher) VALUES (0, NULL);"
    genre_table = {}
    genre_table['MAX'] = 1
    print "INSERT INTO genres (genre_id, genre) VALUES (0, NULL);"
    format_table = {}
    format_table['MAX'] = 1
    print "INSERT INTO formats (format_id, format) VALUES (0, NULL);"
    vendor_table = {}
    vendor_table['MAX'] = 1
    print "INSERT INTO vendors (vendor_id, vendor) VALUES (0, NULL);"
    gifter_table = {}
    gifter_table['MAX'] = 1
    print "INSERT INTO gifters (gifter_id, gifter) VALUES (0, NULL);"
    edition_table = {}
    edition_table['MAX'] = 1
    print "INSERT INTO editions (edition_id, edition) VALUES (0, NULL);"
    children_table = {}
    children_table['MAX'] = 1
    print "INSERT INTO childrens (children_id, children) VALUES (0, NULL);"
    isbn_list = {}
    for book in books:
        # Fix some stuff
        if book['Subjects']:
            book['Subjects'] = book['Subjects'].split('\n')
        if book['ISBN']:
            book_id = book['ISBN']
        else:
            book_id = 'C%08dC' % random.randint(0,99999999)
        if isbn_list.has_key(book_id):
            sys.stderr.write("Possible duplicate: %s\n" % book_id)
            isbn_list[book_id] += 1
            book_id = '%s-%d' % (book_id, isbn_list[book_id] - 1)
        else:
            isbn_list[book_id] = 1
        # Consider authors
        book['Authors'] = parse_authors(book['Author'])
        author_id_list = insert_into_author_table(author_table, book['Authors'])
        rank = 1
        for author_id in author_id_list:
            print "INSERT INTO booksauthors VALUES ('%s', %d, %d);" % (book_id, author_id, rank)
            rank += 1
        # Consider editors
        book['Editors'] = parse_authors(book['Editor'])
        editor_id_list = insert_into_editor_table(editor_table, book['Editors'])
        rank = 1
        for editor_id in editor_id_list:
            print "INSERT INTO bookseditors VALUES ('%s', %d, %d);" % (book_id, editor_id, rank)
            rank += 1
        # Consider translators
        book['Translators'] = parse_authors(book['Translator'])
        translator_id_list = insert_into_translator_table(translator_table, book['Translators'])
        rank = 1
        for translator_id in translator_id_list:
            print "INSERT INTO bookstranslators VALUES ('%s', %d, %d);" % (book_id, translator_id, rank)
            rank += 1
        # Consider illustrators
        book['Illustrators'] = parse_authors(book['Illustrator'])
        illustrator_id_list = insert_into_illustrator_table(illustrator_table, book['Illustrators'])
        rank = 1
        for illustrator_id in illustrator_id_list:
            print "INSERT INTO booksillustrators VALUES ('%s', %d, %d);" % (book_id, illustrator_id, rank)
            rank += 1
        # Consider Subjects
        subject_id_list = insert_into_subjects_table(subject_table, book['Subjects'])
        rank = 1
        for subject_id in subject_id_list:
            print "INSERT INTO bookssubjects VALUES ('%s', %d, %d);" % (book_id, subject_id, rank)
            rank += 1
        # Consider publisher
        publisher_id = insert_into_publisher_table(publisher_table, book['Publisher'])
        # Consider genre
        genre_id = insert_into_genre_table(genre_table, book['Genre'])
        # Consider format
        format_id = insert_into_format_table(format_table, book['Format'])
        # Consider vendor
        vendor_id = insert_into_vendor_table(vendor_table, book['PurchasedAt'])
        # Consider gifter
        gifter_id = insert_into_gifter_table(gifter_table, book['Gift'])
        # Consider edition
        edition_id = insert_into_edition_table(edition_table, book['Edition'])
        # Consider children
        children_id = insert_into_children_table(children_table, book['Children'])
        # Consider book itself
        # Fix dates
        purchased_on = fix_date(book['PurchasedOn'])
        last_read = fix_date(book['LastRead'])
        release_date = fix_date(book['Release'])
        cover_image_url = fix_cover_url(book['CoverImageURL'])
        summary = fix_strings(book['Summary'])
        comments = fix_strings(book['Comments'])
        notes = fix_strings(book['Notes'])
        date_added = fix_date(book['DateAdded'])
        (reader_rating, reader_votes) = find_reader_ratings(book['ReaderRating'])
        print "INSERT INTO books (book_id, title, publisher_id, genre_id, format_id, vendor_id, gifter_id, pages, purchased_on, last_read, copyright,",
        print "isbn, list_price, paid_price, edition_id, children_id, release_date, asin, market_price, summary, cover_image_url, comments, notes,",
        print "date_added, my_rating, product_url, reader_rating, reader_votes)",
        print " VALUES ('%s', '%s', %s, %s, %s, %s, %s, %s, %s, %s, %s, '%s', %s, %s, %s, %s, %s, '%s', %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);" % (book_id,
                book['Title'], publisher_id, genre_id, format_id, 
                vendor_id, gifter_id, safe_int(book['Pages'], 4), purchased_on, last_read, safe_int(book['Copyright'], 4), book_id, 
                safe_float(book['ListPrice'], 7, 2), safe_float(book['PaidPrice'], 7, 2), edition_id, children_id, release_date, book['Asin'],
                safe_float(book['MarketPrice'], 7, 2), summary, cover_image_url, comments, notes, date_added, safe_int(book['MyRating'], 4),
                fix_strings(book['ProductURL']), reader_rating, reader_votes)

########################################################################
# MAIN
########################################################################

dump_sql_creates()

current_field = ""
current_value = ""
current_book = {}
books = []
for line in sys.stdin.xreadlines():
    line = line.strip()
    if field_re.match(line):
        (fld, vlue) = field_re.match(line).groups()
        if fld in fields:
            if len(current_field):
                if debug:
                    sys.stderr.write("%s: %s\n" % (current_field, current_value))
                # Comment single quotes
                current_value = re.sub("'", "\\'", current_value)
                # Make sure we're using utf-8
                current_book[current_field] = unicode(current_value, DEFAULT_ENCODING)
            current_field = fld
            if vlue:
                current_value = vlue.strip()
            else:
                current_value = ""
        else:
            if debug:
                sys.stderr.write("ERROR: unrecognized field: %s, assuming it's part of a multi-line value\n" % fld)
            current_value += "\n%s" % line
    elif line == "########################################################################":
        if debug:
            sys.stderr.write("%s: %s\n" % (current_field, current_value))
            sys.stderr.write("########################################################################\n")
        current_book[current_field] = current_value
        books.append(current_book)
        current_book = {}
    else:
        current_value += "\n%s" % line

process_books(books)