'Add a library UUID.
Add an identifiers table.
Add a languages table.
Add a last_modified column.
NOTE: You cannot downgrade after this update, if you do
any changes you make to book isbns will be lost.'

def upgrade_version_18(self):

    script = (u'\n                                DROP    TABLE    IF    EXISTS    library_id;\n                                CREATE    TABLE    library_id    (    id            INTEGER    PRIMARY    KEY,\n                                                                                                                                        uuid    TEXT    NOT    NULL,\n                                                                                                                                        UNIQUE(uuid)\n                                );\n\n                                DROP    TABLE    IF    EXISTS    identifiers;\n                                CREATE    TABLE    identifiers        (    id                    INTEGER    PRIMARY    KEY,\n                                                                                                                                                book            INTEGER    NOT    NULL,\n                                                                                                                                                type            TEXT    NOT    NULL    DEFAULT    "isbn"    COLLATE    NOCASE,\n                                                                                                                                                val                TEXT    NOT    NULL    COLLATE    NOCASE,\n                                                                                                                                                UNIQUE(book,    type)\n                                );\n\n                                DROP    TABLE    IF    EXISTS    languages;\n                                CREATE    TABLE    languages                (    id                                INTEGER    PRIMARY    KEY,\n                                                                                                                                                lang_code    TEXT    NOT    NULL    COLLATE    NOCASE,\n                                                                                                                                                UNIQUE(lang_code)\n                                );\n\n                                DROP    TABLE    IF    EXISTS    books_languages_link;\n                                CREATE    TABLE    books_languages_link    (    id    INTEGER    PRIMARY    KEY,\n                                                                                                                                                                                book    INTEGER    NOT    NULL,\n                                                                                                                                                                                lang_code    INTEGER    NOT    NULL,\n                                                                                                                                                                                item_order    INTEGER    NOT    NULL    DEFAULT    0,\n                                                                                                                                                                                UNIQUE(book,    lang_code)\n                                );\n\n                                DROP    TRIGGER    IF    EXISTS    fkc_delete_on_languages;\n                                CREATE    TRIGGER    fkc_delete_on_languages\n                                BEFORE    DELETE    ON    languages\n                                BEGIN\n                                                SELECT    CASE\n                                                                WHEN    (SELECT    COUNT(id)    FROM    books_languages_link    WHERE    lang_code=OLD.id)    >    0\n                                                                THEN    RAISE(ABORT,    \'Foreign    key    violation:    language    is    still    referenced\')\n                                                END;\n                                END;\n\n                                DROP    TRIGGER    IF    EXISTS    fkc_delete_on_languages_link;\n                                CREATE    TRIGGER    fkc_delete_on_languages_link\n                                BEFORE    INSERT    ON    books_languages_link\n                                BEGIN\n                                        SELECT    CASE\n                                                        WHEN    (SELECT    id    from    books    WHERE    id=NEW.book)    IS    NULL\n                                                        THEN    RAISE(ABORT,    \'Foreign    key    violation:    book    not    in    books\')\n                                                        WHEN    (SELECT    id    from    languages    WHERE    id=NEW.lang_code)    IS    NULL\n                                                        THEN    RAISE(ABORT,    \'Foreign    key    violation:    lang_code    not    in    languages\')\n                                        END;\n                                END;\n\n                                DROP    TRIGGER    IF    EXISTS    fkc_update_books_languages_link_a;\n                                CREATE    TRIGGER    fkc_update_books_languages_link_a\n                                BEFORE    UPDATE    OF    book    ON    books_languages_link\n                                BEGIN\n                                                SELECT    CASE\n                                                                WHEN    (SELECT    id    from    books    WHERE    id=NEW.book)    IS    NULL\n                                                                THEN    RAISE(ABORT,    \'Foreign    key    violation:    book    not    in    books\')\n                                                END;\n                                END;\n                                DROP    TRIGGER    IF    EXISTS    fkc_update_books_languages_link_b;\n                                CREATE    TRIGGER    fkc_update_books_languages_link_b\n                                BEFORE    UPDATE    OF    lang_code    ON    books_languages_link\n                                BEGIN\n                                                SELECT    CASE\n                                                                WHEN    (SELECT    id    from    languages    WHERE    id=NEW.lang_code)    IS    NULL\n                                                                THEN    RAISE(ABORT,    \'Foreign    key    violation:    lang_code    not    in    languages\')\n                                                END;\n                                END;\n\n                                DROP    INDEX    IF    EXISTS    books_languages_link_aidx;\n                                CREATE    INDEX    books_languages_link_aidx    ON    books_languages_link    (lang_code);\n                                DROP    INDEX    IF    EXISTS    books_languages_link_bidx;\n                                CREATE    INDEX    books_languages_link_bidx    ON    books_languages_link    (book);\n                                DROP    INDEX    IF    EXISTS    languages_idx;\n                                CREATE    INDEX    languages_idx    ON    languages    (lang_code    COLLATE    NOCASE);\n\n                                DROP    TRIGGER    IF    EXISTS    books_delete_trg;\n                                CREATE    TRIGGER    books_delete_trg\n                                                AFTER    DELETE    ON    books\n                                                BEGIN\n                                                                DELETE    FROM    books_authors_link    WHERE    book=OLD.id;\n                                                                DELETE    FROM    books_publishers_link    WHERE    book=OLD.id;\n                                                                DELETE    FROM    books_ratings_link    WHERE    book=OLD.id;\n                                                                DELETE    FROM    books_series_link    WHERE    book=OLD.id;\n                                                                DELETE    FROM    books_tags_link    WHERE    book=OLD.id;\n                                                                DELETE    FROM    books_languages_link    WHERE    book=OLD.id;\n                                                                DELETE    FROM    data    WHERE    book=OLD.id;\n                                                                DELETE    FROM    comments    WHERE    book=OLD.id;\n                                                                DELETE    FROM    conversion_options    WHERE    book=OLD.id;\n                                                                DELETE    FROM    books_plugin_data    WHERE    book=OLD.id;\n                                                                DELETE    FROM    identifiers    WHERE    book=OLD.id;\n                                END;\n\n                                INSERT    INTO    identifiers    (book,    val)    SELECT    id,isbn    FROM    books    WHERE    isbn;\n\n                                ALTER    TABLE    books    ADD    COLUMN    last_modified    TIMESTAMP    NOT    NULL    DEFAULT    "%s";\n\n                                ' % isoformat(DEFAULT_DATE, sep=u'    '))
    self.db.execute(script)
