# -*- coding: utf-8 -*-
from __future__ import (unicode_literals, division, absolute_import, print_function)
__license__   = 'GPL v3'
__copyright__ = '2014, 2015 DaltonST <DaltonShiTzu@outlook.com>'

__my_version__ = "3.6.92"    # adjustment for broadened use of a standard calibre user defined sqlite function.
import os, sys
import sqlite3
from sqlite3 import IntegrityError, OperationalError, Row
from calibre.library.database import LibraryDatabase
import datetime, re, sre_constants
from calibre.db.backend import DB
from calibre import isbytestring, force_unicode, prints
from calibre.constants import iswindows, filesystem_encoding, preferred_encoding
from Queue import Empty, Queue
from calibre.gui2.threaded_jobs import ThreadedJob, BaseJob
from calibre.utils.logging import Log as log
from calibre.gui2 import error_dialog, info_dialog
import codecs
from time import sleep
import time
import unicodedata
from copy import deepcopy

notifications = Queue()

mynothing = ""

from calibre_plugins.quarantine_and_scrub.heading import log_heading_common
header_s1 = None
header_s2 = None
header_s3 = None
header_s4 = None
header_s5 = None

    #----------------------------------------------------------------------------------------------------------------
def util_copy_original_metadata(self, guidb, log=None, abort=None, notifications=True):  # <<<<====called from jobs.py

    global header_s1
    global header_s2
    global header_s3
    global header_s4
    global header_s5
    global mynothing


    db = guidb
    path = db.library_path
    if isbytestring(path):
        path = path.decode(filesystem_encoding)
    path = path.replace(os.sep, '/')
    path = os.path.join(path, 'metadata.db')
    path = path.replace(os.sep, '/')
    print(path)
    log(path)
    try:
        my_db = sqlite3.connect(path)
    except Exception as e:
        return (self,gui,None,None,None)

    my_cursor = my_db.cursor()

    header_s1 =  str("SQLite Version: " + str(sqlite3.version))

    mysql = str("PRAGMA main.locking_mode=EXCLUSIVE;")
    my_cursor.execute(mysql)
    header_s2 = mysql

    header_s3 = "Beginning Copying Real to Work Data"

    #check library uuid.  it must be:  07111111-0000-4000-b000-f00000000001
    my_cursor.execute("SELECT count(*) FROM library_id WHERE uuid = '07111111-0000-4000-b000-f00000000001'  ;")
    data = my_cursor.fetchone()[0]
    if data > 0:
        log_heading_common(log,header_s1,header_s2,header_s3,header_s4,header_s5)
    else:
        header_s4 = "Library UUID is NOT correct."
        log_heading_common(log,header_s1,header_s2,header_s3,header_s4,header_s5)
        my_db.close()
        return

    mysql = "PRAGMA main.busy_timeout = 10000;"      #PRAGMA busy_timeout = milliseconds;  (obviously just for this connection)
    my_cursor.execute(mysql)

    #now check for custom sqlite objects:
    n_rows = 0
    mysql = "SELECT id, label FROM custom_columns WHERE label = 'work_author' or label = 'work_tags' \
            or label = 'work_title' or label = 'work_series' or label = 'work_series_number' or label = 'work_freeze' or label = 'status'     ;"
    try:
        my_cursor.execute (mysql)
        tmp_rows = my_cursor.fetchall() #will get the (remaining) rows from the query
        if tmp_rows:
            n_rows = len(tmp_rows)
            if n_rows == 0:
                log("[1] custom columns for this plugin were not found in the custom_column table.")
                my_db.close()
                return
        else:
            log("[2] custom columns for this plugin were not found in the custom_column table.")
            my_db.close()
            return
    except Exception as e:
        raise e
        log(str(e))
        my_db.close()
        return

    r1 = ""
    r2 = ""
    r3 = ""
    r4 = ""
    r5 = ""
    r6 = ""
    r7 = ""

    for row in tmp_rows:
        s = str(row)
        print(s)
        tmp_list = s.split(",")
        for item in tmp_list:
            n1 = tmp_list[1].find("work_author")
            n2 = tmp_list[1].find("work_title")
            n3 = tmp_list[1].find("work_series")
            n4 =  tmp_list[1].find("work_series_number")
            n5 =  tmp_list[1].find("work_tags")
            n6 =  tmp_list[1].find("work_freeze")
            n7 =  tmp_list[1].find("status")

            if n1 >= 0:
                r1 = tmp_list[0]
            else:
                if n2 >= 0:
                    r2  = tmp_list[0]
                else:
                    if n4 >= 0: #must do prior to n3...substring match...
                        r4 = tmp_list[0]
                    else:
                        if n3 >= 0:
                            r3  = tmp_list[0]
                        else:
                            if n5 >= 0:
                                r5  = tmp_list[0]
                            else:
                                if n6 >= 0:
                                    r6 = tmp_list[0]
                                else:
                                    if n7 >= 0:
                                        r7 = tmp_list[0]
                                    else:
                                        pass

        #END FOR
    #END FOR

    r1 = Strip_UTF8_To_String(r1)
    r2 = Strip_UTF8_To_String(r2)
    r3 = Strip_UTF8_To_String(r3)
    r4 = Strip_UTF8_To_String(r4)
    r5 = Strip_UTF8_To_String(r5)
    r6 = Strip_UTF8_To_String(r6)
    r7 = Strip_UTF8_To_String(r7)

    print("custom column number for work_author is:        ", str(r1))
    print("custom column number for work_title is:         ", str(r2))
    print("custom column number for work_series is:        ", str(r3))
    print("custom column number for work_series_number is: ", str(r4))
    print("custom column number for work_tags is:          ", str(r5))
    print("custom column number for work_freeze is:        ", str(r6))
    print("custom column number for status           is:        ", str(r7))

    if r1 <> '4' or r2 <> '8' or r3 <> '10' or r4 <> '12' or r5 <> '13' or r6 <> '16' or r7 <> '18' :
        print("r1 <> 4 or r2 <> 8 or r3 <> 10 or r4 <> 12 or r5 <> 13 or r6 <> 16 or r7 <> 18")
        log("This Metadata.db Has Been Corrupted. Custom Columns Incorrect for Plugin. Reinstall Plugin From Beginning.")
        log("This Metadata.db Is Special, and was designed for this Plugin.  Download it, replace this one, and Check Library")
        log("The Calibre Check Library function will check the books not showing in the replacement metadata.db, and add them to it.")
        log("You will NOT lose your books.  However, you will lose the Metadata if you have not let Calibre write out the .opf files.")
        log("In which case, you will have to 'Add Books' again for the replaced metadata.db so it will rebuild the Metadata.")
        log("Moral of this story:  Do Not Mess With the Custom Columns of this Plugin's Special metadata.db")
        log("The tables, views and indices have been hand-crafted to make this Plugin work efficiently and effectively.")
        my_db.close()
        return
    else:
        pass
        #log("The 7 Custom Columns for Q&S were verified as being present and unchanged.")



    ##Create_SQLite_objects(my_db, my_cursor, log)       #not used at this time


    log("Erasing All Work Custom Column Data")
    notifications.put((0.01, 'Erasing All Work Custom Column Data'))
    #----------------------------------------------------------------------------------------------
    try:
        c_work_author_table = "custom_column_4"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_4 WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        l_work_author_table = "books_custom_column_4_link"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM books_custom_column_4_link WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        c_work_title_table = "custom_column_8"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_8 WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        l_work_title_table = "books_custom_column_8_link"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM books_custom_column_8_link WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        c_work_series_table = "custom_column_10"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_10 WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        l_work_series_table = "books_custom_column_10_link"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM books_custom_column_10_link WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        c_work_series_index_table = "custom_column_12"  #this is an integer.  this table IS the link table.
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_12 WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        c_work_tags_table = "custom_column_13"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_13 WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        l_work_tags_table = "books_custom_column_13_link"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM books_custom_column_13_link WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        c_work_tags_table = "custom_column_15"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_15 WHERE id >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()

        c_work_freeze_table = "custom_column_16"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_16 WHERE id >= '0' "
        my_cursor.execute (mysql)
        my_db.commit()

        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _tags_work_single WHERE tag NOT NULL"
        my_cursor.execute (mysql)
        my_db.commit()


        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _dg_missing_tag_rules"       #avoid having it grow forever because the user doesn't purge it after it no longer is useful.
        my_cursor.execute (mysql)
        my_db.commit()


        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _books_work"                       #used to explode cc4 for "bad authors", such as the Cowgirl example...
        my_cursor.execute (mysql)
        my_db.commit()


        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _book_awards_mapping"
        my_cursor.execute (mysql)
        my_db.commit()




    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    print("sleep 1")
    sleep(2.0)

    #----------------------------------------------------------------------------------------------
    log("Populating All Work Custom Columns From Real Metadata")
    notifications.put((0.02, 'Populating All Work Custom Columns From Real Metadata'))
    #----------------------------------------------------------------------------------------------


    #IMPORTANT NOTE:  Calibre will "on its own"  relink duplicate authors in custom column 4 so the authors are unique regardless of the sqlite constraints!
    #Calibre will do this whenever the metadata is edited in the GUI.  Example!  copy real to work for a selected book (no job; normal Calibre GUI processing).
    #So, the only way to fix what Calibre does is to run authorlevel or booklevel, which both do the same thing as shown below.
    try:
        c_work_author_table = "custom_column_4"        #work author    is unique to each book, but multiple real authors can exist, so ignore all but first
        mysql = "INSERT OR IGNORE INTO custom_column_4 SELECT book,name FROM __book_author_name_sort  WHERE name not null"
        my_cursor.execute (mysql)
        my_db.commit()

        l_work_author_table = "books_custom_column_4_link"   #work author book link with a work author unique to each book,
        #                                                                                        but multiple real authors can exist, so ignore all but first
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO books_custom_column_4_link SELECT book,book,book FROM __book_author_name_sort "
        my_cursor.execute (mysql)
        my_db.commit()
        sleep(0.5)

        fix_bad_authors_to_conform_to_explosion_logic(my_db, my_cursor)     #concatenates "bad authors" and makes them a single work author. example:  Cowgirls Up & Rodeo (2009)
        sleep(0.2)
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "UPDATE custom_column_4 SET value = (SELECT authorname FROM _books_work WHERE book = custom_column_4.id) WHERE custom_column_4.id IN(SELECT book FROM   _books_work)"
        my_cursor.execute (mysql)
        my_db.commit()
        sleep(0.5)
        #leave the existing entries in table _books_work for use by authorlevel scrubbing or booklevel scrubbing when the explode cc4...
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e


    try:
        c_work_title_table = "custom_column_8"   #work title
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO custom_column_8 SELECT id, title FROM books WHERE id > 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        l_work_title_table = "books_custom_column_8_link"   #work title book link
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO books_custom_column_8_link SELECT id, id, id FROM custom_column_8 WHERE id > 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e




    try:
        c_work_title_table = "custom_column_10"   #work series
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO custom_column_10 SELECT id, name FROM series WHERE id > 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        l_work_title_table = "books_custom_column_10_link"   #work series book link
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO books_custom_column_10_link SELECT * FROM books_series_link WHERE id > 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    #now, give each book its own row in custom_column_10 by making the id = book
    try:
        c_work_title_table = "custom_column_10"   #work series
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO custom_column_10 SELECT book, seriesname FROM __books_work_populate WHERE book > 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    #now link the book to its personal row in cc10
    try:
        l_work_title_table = "books_custom_column_10_link"   #work series book link
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "UPDATE books_custom_column_10_link  SET value = book"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e


    #now delete the original non-redundant cc10 entries now that each book has its own cc10 entry.  they are no longer used.
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM custom_column_10 WHERE id NOT IN(SELECT value FROM books_custom_column_10_link)"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e


    try:
        c_work_title_table = "custom_column_12"   #work series index (this table IS the link table)
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO custom_column_12 SELECT id, id, series_index FROM books WHERE id > 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e


    try:  #set the seriesindex to 0 if the seriesname is null to make updates to the work_seriesindex obvious and noticeable
        c_work_title_table = "custom_column_12"   #work series index (this table IS the link table)
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql =  "UPDATE custom_column_12 SET value = 0  WHERE book IN (SELECT book FROM  __books_work_populate WHERE seriesname is null)"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        c_work_title_table = "custom_column_15"   #work series full (this table IS the link table)
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO custom_column_15 SELECT book,book,seriesfull FROM __series_work_full WHERE book > 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:  #remove the .0 from all series indexes in the work series full table
        c_work_title_table = "custom_column_15"   #work series index (this table IS the link table)
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql =  "UPDATE custom_column_15 SET value=REPLACE(value,'.0','') WHERE book > 0"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:  #tweak the series index of 0.5 to look identical to Calibre's 0.50
        c_work_title_table = "custom_column_15"   #work series index (this table IS the link table)
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql =  "UPDATE custom_column_15 SET value=REPLACE(value,'.5]','.50]') WHERE book > 0"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        c_work_freeze_table = "custom_column_16"
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT OR REPLACE INTO custom_column_16 (id,book,value) SELECT book, book,'0' FROM books_custom_column_4_link WHERE book > '0' "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    print("sleep 2")
    sleep(2.0)

    notifications.put((0.10, 'Populating Work Tables for Tags'))

    concatenate_tags_for_books(my_db, my_cursor)  #custom_column_13 and its link table

    print("sleep 3")
    sleep(1.0)

    notifications.put((0.20, 'Populating Work Custom Columns From Real Metadata'))

    #remove leading and trailing spaces from everything
    mysql =   "UPDATE _global_subject_codes SET subject = trim(subject); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e
    mysql =   "UPDATE _global_authors SET name = trim(name); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        pass
    mysql =   "UPDATE _global_authors SET sort = trim(sort); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        pass
    mysql =   "UPDATE _global_series SET name = trim(name); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        pass
    mysql =   "UPDATE _global_series SET sort = trim(sort); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e
    mysql =   "UPDATE _pristine_authors SET name = trim(name); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        pass
    mysql =   "UPDATE _pristine_authors SET sort = trim(sort); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    notifications.put((0.55, 'Populating Work Custom Columns From Real Columns'))

    mysql =   "UPDATE _pristine_series SET name = trim(name); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e
    mysql =   "UPDATE custom_column_4 SET value = trim(value); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    mysql =   "UPDATE custom_column_8 SET value = trim(value); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e
    mysql =   "UPDATE custom_column_10 SET value = trim(value); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e
    mysql =   "UPDATE custom_column_13 SET value = trim(value); "
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    s1 = '"'
    s2 = ''
    s1 = str(s1)
    s2 = str(s2)
    #pure sql:   UPDATE custom_column_8 SET value = replace(custom_column_8.value,'"','')       # double quotes cause sql errors!
    try:
        my_cursor.execute ('BEGIN TRANSACTION')
        mysql = str('UPDATE custom_column_4 SET value = replace(custom_column_4.value,?,?)' )
        my_cursor.execute(mysql,(s1,s2))  #apsw
        mysql = str('UPDATE custom_column_8 SET value = replace(custom_column_8.value,?,?)' )
        my_cursor.execute(mysql,(s1,s2))  #apsw
        mysql = str('UPDATE custom_column_10 SET value = replace(custom_column_10.value,?,?)' )
        my_cursor.execute(mysql,(s1,s2))  #apsw
        mysql = str('UPDATE custom_column_13 SET value = replace(custom_column_13.value,?,?)' )
        my_cursor.execute(mysql,(s1,s2))  #apsw
        mysql = str('UPDATE custom_column_15 SET value = replace(custom_column_15.value,?,?)' )
        my_cursor.execute(mysql,(s1,s2))  #apsw
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e


    print("sleep 4")
    sleep(2.0)

    update_status(my_db, my_cursor) #change the status to 'dirty'

    notifications.put((0.95, 'Reindexing Custom Column Tables'))

    print("Q&S Custom Column Tables Will Now Be Reindexed")
    log("Q&S Custom Column Tables Will Now Be Reindexed")


    try:
        mysql = "REINDEX _custom_column_4"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX _custom_column_8"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX _custom_column_10"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX _custom_column_12"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX _custom_column_13"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e


    try:
        mysql = "REINDEX  books_custom_column_4_link_aidx"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX  books_custom_column_4_link_bidx"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX  books_custom_column_8_link_aidx"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX  books_custom_column_8_link_bidx"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX  books_custom_column_10_link_aidx"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX  books_custom_column_10_link_bidx"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX  books_custom_column_13_link_aidx"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        mysql = "REINDEX  books_custom_column_13_link_bidx"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    notifications.put((0.98, 'Scrubbing ISBNs'))

    try:
        mysql = "UPDATE identifiers SET val = (replace(val,'-','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%-%')"
        my_cursor.execute (mysql)
        my_db.commit()
        sleep(0.1)
        mysql = "UPDATE identifiers SET val = (replace(val,'isbn:','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%isbn:%') "
        my_cursor.execute (mysql)
        my_db.commit()
        sleep(0.1)
        mysql = "UPDATE identifiers SET val = (replace(val,'eBook ISBN:','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '%eBook ISBN:%') "
        my_cursor.execute (mysql)
        my_db.commit()
        sleep(0.1)
        mysql = "UPDATE identifiers SET val = (replace(val,' ','')) WHERE val IN(SELECT val FROM identifiers WHERE type = 'isbn' AND val LIKE '% %') "
        my_cursor.execute (mysql)
        my_db.commit()
        print("All ISBN13s have been standardized by removing any dashes and prefixes.")
        log("All ISBN13s have been standardized by removing any dashes and prefixes.")
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    _convert_identifiers_isbn_from_10_to_13(my_db, my_cursor, log)


    notifications.put((0.99, 'Q&S Database Being Vacuumed [Defragmented]') )
    print("Q&S Database Will Now Be Vacuumed [Defragmented]")
    log("Q&S Database Will Now Be Vacuumed [Defragmented]")

    try:
        mysql = "Vacuum"
        my_cursor.execute (mysql)
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    my_db.close()

    print("Job complete.")
    log("Job complete.")

  #---------------------------------------------------------------------------------------------------------------------------------------
def Strip_UTF8_To_String(u_string):

    #does NOT remove commas and/or spaces here; do it separately if needed, but do NOT do it here...

    global mynothing

    utf8_to_convert = u_string

    try:
         utf8_to_convert.decode('utf8').encode('iso8859-15', "strict")  #<type 'unicode'> to <type 'str'>
    except:
        try:
            utf8_to_convert.encode('iso8859-15', "strict")
        except:
            try:
                utf8_to_convert.encode('ascii', "replace")  #<type 'unicode'> to <type 'str'
            except:
                try:
                    utf8_to_convert = str(utf8_to_convert)
                    utf8_to_convert = utf8_to_convert.replace("u'", mynothing, 100)
                    utf8_to_convert = utf8_to_convert.replace("'", mynothing, 100)
                    utf8_to_convert = utf8_to_convert.replace("(", mynothing, 100)
                    utf8_to_convert = utf8_to_convert.replace(")", mynothing, 100)
                    try:
                        log("unicode conversion to byte string was problematic:  ", str(utf8_to_convert))
                    except:
                        pass
                except:
                    pass

    try: #backup logic
        utf8_to_convert = utf8_to_convert.replace("u'", mynothing, 100)
        utf8_to_convert = utf8_to_convert.replace("'", mynothing, 100)
        utf8_to_convert = utf8_to_convert.replace("(", mynothing, 100)
        utf8_to_convert =utf8_to_convert.replace(")", mynothing, 100)
        try:
            log("unicode conversion to byte string was problematic:  ", str(utf8_to_convert))
        except:
            pass
    except:
        pass

    return utf8_to_convert

    #----------------------------------------------------------------------------------------------------------------------------
def concatenate_tags_for_books(my_db, my_cursor):

    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "DELETE FROM _tags_work WHERE book >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    #initially populate it with the key of every book that has a tag; the value of tagsconcat is defaulted to Unknown temporarily
    try:
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT or IGNORE INTO _tags_work SELECT book, 'Unknown'  FROM books_tags_link WHERE book >= 0 "
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    #since there are multiple tags per book, must concatenate them into the work_tags column separated by a comma

    #optimized for speed in version 3.1.2 ----------------------------------------------------------------BEGIN

    tmp_rows = []
    del tmp_rows
    tmp_rows = []
    mysql = str("SELECT book FROM _tags_work WHERE book > 0 ")
    try:
        my_cursor.execute(mysql)
        tmp_rows = my_cursor.fetchall() #will get all of the rows from the query
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    my_counter = 0
    my_cursor.execute ("BEGIN TRANSACTION")
    for item in tmp_rows:
        my_counter = my_counter + 1
        book = str(item)
        book = str(__strip_numerics(book))
        try:
            mysql = str("UPDATE _tags_work SET tagsconcat = (SELECT tagsconcat FROM __tags_work_concatenate WHERE book = [BOOK] ) \
                                                             WHERE _tags_work.book = [BOOK] ")
            mysql = str(mysql.replace("[BOOK]", str(book), 2))
            my_cursor.execute (mysql)
            if my_counter > 499:
                my_db.commit()
                my_counter = 0
                sleep(0.02)
                my_cursor.execute ("BEGIN TRANSACTION")
            else:
                pass
        except Exception as e:
            print(mysql)
            print(str(e))
            raise e
    #END FOR
    if my_counter > 0:
        try:
            my_db.commit()
        except:
            pass

    #optimized for speed in version 3.1.2 ----------------------------------------------------------------END


    #now update the 2 work custom_column tag tables using table _tags_work

    try:
        c_work_tag_table = "custom_column_13"   #work tags       the id == book
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT OR IGNORE INTO custom_column_13 (id,value) SELECT book,tagsconcat FROM _tags_work"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    try:
        l_work_tag_table = "books_custom_column_13_link"   #work tags  the id == book == value == id from above which == book
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT OR IGNORE INTO books_custom_column_13_link (id,book,value) SELECT book,book,book FROM _tags_work"
        my_cursor.execute (mysql)
        my_db.commit()
    except Exception as e:
        print(mysql)
        print(str(e))
        raise e

    #----------------------------------------------------------------------------------------------------------------------------
def update_status(my_db, my_cursor):
   #now update status
    tmp_id_rows = []
    del tmp_id_rows

    mysql = "SELECT id FROM custom_column_18 WHERE value = 'dirty' "
    tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_id_rows:
        sleep(.1) #avoid db locks
        mysql = "INSERT OR IGNORE INTO custom_column_18 (id,value) VALUES (null, 'dirty') "
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        print("sleep 5")
        sleep(1.0)
        mysql = "SELECT id FROM custom_column_18 WHERE value = 'dirty' "
        tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)

    if not tmp_id_rows:
        print("sql error: custom_column_18 WHERE value = 'dirty' ")
        return

    tmp_id = tmp_id_rows[0]
    tmp_id = __strip_numerics(tmp_id)

    sleep(.1) #avoid db locks
    mysql = "DELETE FROM books_custom_column_18_link WHERE book > ? AND book > ?"
    __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, "0", "0")
    print("sleep 6")
    sleep(1.0)
    mysql = "INSERT OR REPLACE INTO books_custom_column_18_link (id,book,value) SELECT book, book, ? FROM __books_work_populate WHERE book > ? "
    __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, tmp_id, "0")

  #----------------------------------------------------------------------------------------------------------------
def __strip_numerics(s):

    global mynothing

    s = str(s)

    s = __change_tuple_to_bytestring(s)

    s = s.replace(',', mynothing, 4)
    s = s.replace("(", mynothing, 4)
    s = s.replace(")", mynothing, 4)
    s = s.replace(" ", mynothing, 10)
    s = s.strip()

    return s

   #---------------------------------------------------------------------------------------------------------------------------------------------
def __change_tuple_to_bytestring(tuple):
    #do not ever remove spaces here;  for commas,  only remove unicode special character combinations that may contain a comma.

    #example:   (u'Microsoft Word - New Microsoft Office Word Document',)
    #example:   (u"Saurabh's Account",)
    #example:   (u"Saurabh's Account", u"Account, Saurabh's")  <<=== keep the possessive 's
    #example:   '(199)' should be simply  '199'  in mysql
    #example:   (199) should be simply 199
    #example:   Sarah Miles'  should be simply  Sarah Miles
    #example:   Matt Scudder)  should be simply Matt Scudder
    #example:  (u'Hope Over Fear (Over #1)',)) should be simply  Hope Over Fear (Over #1)
    #example:   u"The Wizard King: Heart's Desire Book 3") should be simply   The Wizard King: Heart's Desire Book 3

    global mynothing

    s_string = str(tuple)

    s_string = s_string.replace('(u"', mynothing, 100)
    s_string = s_string.replace("(u'", mynothing, 100) #example: (u'Hope Over Fear (Over #1)',)) becomes Hope Over Fear (Over #1)',))

    s_string = s_string.replace("',))", mynothing, 100) #example: Hope Over Fear (Over #1)',)) becomes Hope Over Fear (Over #1)
    s_string = s_string.replace("',)", mynothing, 100) #example:   (u"Saurabh's Account",)
    s_string = s_string.replace('",)', mynothing, 100)

    s_string = s_string.replace('u"', mynothing, 100)   #example:   (u"Saurabh's Account", u"Account, Saurabh's")  <<=== keep the possessive 's
    s_string = s_string.replace("u'", mynothing, 100)

    s_string = s_string.replace("'(", "'", 100) #example:   '(199)' should be simply  '199'  in mysql
    s_string = s_string.replace(")'", "'", 100)

    s_string = s_string.replace('u"', mynothing, 100)  #example:   u"The Wizard King: Heart's Desire Book 3")
    s_string = s_string.replace('")', mynothing, 100)

    #return if string is good at this point.  example:  Hope Over Fear (Over #1)
    #__printsafe("in strip 1: ", s_string)
    n1 = s_string.find("(")
    n2 = s_string.find(")")
    if n1 >= 0:
        if n2 >= 0 and n2 > n1:   #example:  Hope Over Fear (Over #1)     ... matched set ...
            #__printsafe("in strip 2 returning: ", s_string)
            return s_string
        else:  #example:  Hope Over Fear ) (Over #1                                        ... not a matched set ...
            pass
    else:
        pass

    #__printsafe("in strip 3 NOT returning: ", s_string)

    n = s_string.find("(")
    if n == 0: #example:  is (199)
        s_string = s_string.replace("(", mynothing, 1) #now is 199)
        c = s_string.count(")")  #example:  is 199)
        if c == 1:
            s_string = s_string.replace(")", mynothing, 1)  #example:  now 199
        else:
            pass

    #must do each of these twice
    n = s_string.rfind("'")  #s_string =  Sarah Miles', so n == 11
    l = len(s_string)  #l = 12
    if n == (l - 1):  # 11 = 12 - 1, so True
        z = (n - 1) # 11 - 1 = 10
        s_string = s_string[0:z]  # now  Sarah Miles not Sarah Miles'
    else:
        pass

    n = s_string.rfind(")")  #s_string =  Matt Scudder) , so n == 13
    l = len(s_string)  #l = 14
    if n == (l - 1):  # 13 = 14 - 1, so True
        z = (n - 1) # 11 - 1 = 10
        s_string = s_string[0:z]  # now  Matt Scudder not Matt Scudder)
        pass

    #must do each of these twice
    n = s_string.rfind("'")  #s_string =  Sarah Miles', so n == 11
    l = len(s_string)  #l = 12
    if n == (l - 1):  # 11 = 12 - 1, so True
        z = (n - 1) # 11 - 1 = 10
        s_string = s_string[0:z]  # now  Sarah Miles not Sarah Miles'
    else:
        pass

    n = s_string.rfind(")")  #s_string =  Matt Scudder) , so n == 13
    l = len(s_string)  #l = 14
    if n == (l - 1):  # 13 = 14 - 1, so True
        z = (n - 1) # 11 - 1 = 10
        s_string = s_string[0:z]  # now  Matt Scudder not Matt Scudder)
        pass

    s_string = s_string.strip()

    if isinstance(s_string, unicode):
        s_string = str(s_string)
        if isinstance(s_string, unicode):
            __printsafe("s_string is still a unicode object! [2]", s_string)

    return s_string

 #----------------------------------------------------------------------------------------------------------------------------------------------
def __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql):

    sleep(0.02) #avoid db locks

    tmp_rows = []
    del tmp_rows
    tmp_rows = []

    mysql = str(mysql)
    try:
        sleep(1.0)  #avoid lock errors
        my_cursor.execute(mysql)
        tmp_rows = my_cursor.fetchall() #will get all of the rows from the query
        if tmp_rows:
            return tmp_rows
        else:
            tmp_rows = []
            return tmp_rows
    except:
        #likely due to database lock; sleep then try again...
        print("database LOCK error; sleeping and will then try again")
        sleep(10.0)
        tmp_rows = []
        del tmp_rows
        tmp_rows = []
        try:
            my_cursor.execute(mysql)
            tmp_rows = my_cursor.fetchall() #will get all of the rows from the query
            if tmp_rows:
                return tmp_rows
            else:
                tmp_rows = []
                return tmp_rows
        except Exception as e:
            log(str(e))
            __printsafe(str(e))
            __printsafe(mysql)
            __printsafe("__execute_mysql_fetchall_generic:   try-except failure")
            my_db.close()
            __printsafe("database has been CLOSED")
            raise e
            sys.exit("ERROR in __execute_mysql_fetchall_generic....")

    #-----------------------------------------------------------------------------------------------------------------
def __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql):

    global freeze_current_book
    freeze_current_book = True

    mysql = str(mysql)
    try:
        sleep(1.0)  #avoid lock errors
        #my_cursor.execute ("BEGIN TRANSACTION")
        my_cursor.execute("begin") #apsw
        my_cursor.execute(mysql)  #apsw
        my_cursor.execute("commit") #apsw
        #my_cursor.execute (mysql)
        #my_cursor.execute ("commit")
    except:
        print("database locked; sleeping and then will retry")
        sleep(10.0)
        try:
            try:
                my_cursor.execute("begin") #apsw
                my_cursor.execute(mysql)  #apsw
                my_cursor.execute("commit") #apsw
            except:
                my_cursor.execute(mysql)  #apsw
                try:
                    my_cursor.execute("commit") #apsw
                except:
                    pass
        except Exception as e:
            log(str(e))
            __printsafe(str(e))
            my_db.close()
            __printsafe("database has been CLOSED")
            raise e
            sys.exit("ERROR in __execute_mysql_with_commit_generic....")

    sleep(0.03) #avoid db locks


       #-----------------------------------------------------------------------------------------------------------------
def __printsafe(*arg):

    try:
        print(arg)
    except:
        print("...cannot be printed...")

#---------------------------------------------------------------------------------------------------------------------------------------------
def __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, v1, v2):

    global mynothing

    global freeze_current_book
    freeze_current_book = True

    __printsafe("v1: ", str(v1), " v2: ", str(v2))

    s2 = str(v2)  #second parameter is optional
    s2 = str(__change_tuple_to_bytestring(s2))

    if s2 == mynothing:
        s2 = str("IGNORE")

    try:
        sleep(1.0)  #avoid lock errors
        my_cursor.execute("begin") #apsw
        if s2 <> "IGNORE":
            my_cursor.execute(mysql,(v1,v2))  #apsw
        else:
            my_cursor.execute(mysql,(v1))  #apsw
        my_cursor.execute("commit") #apsw
    except:
        print("database locked; sleeping and then will retry")
        sleep(5.0)
        try:
            if s2 <> "IGNORE":
                my_cursor.execute(mysql,(v1,v2))  #apsw
            else:
                my_cursor.execute(mysql,(v1))  #apsw
                my_cursor.execute("commit") #apsw
        except Exception as e:
            log(str(e))
            __printsafe(str(e))
            my_db.close()
            __printsafe("database has been CLOSED")
            raise e
            sys.exit("ERROR in __execute_mysql_for_custom_column_generic")

    sleep(0.03) #avoid db locks


 #----------------------------------------------------------------------------------------------------------------
 #----------------------------------------------------------------------------------------------------------------
def _convert_identifiers_isbn_from_10_to_13(my_db, my_cursor, log):
    #optimized for speed: version 3.1.3

    mysql = "SELECT book, val FROM identifiers WHERE type = 'isbn'  AND val NOT LIKE '978%'  \
                    AND val NOT LIKE '979%' AND val NOT LIKE '%isbn%'  AND val NOT NULL;"
    tmp_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_rows:
        return
    else:
        n = len(tmp_rows)
        log("Number of ISBNs Requiring Conversion: " + str(n))
        if n == 0 :
            return
        my_counter = 0
        my_total = 0
        my_cursor.execute("begin")  #apsw
        for row in tmp_rows:
            my_counter = my_counter + 1
            my_total = my_total + 1
            book, val = row
            book = str(__strip_numerics(book))
            book = str(book.strip())
            old_isbn = str(__strip_numerics(val))
            old_isbn = str(old_isbn.strip())
            if len(old_isbn) == 10:
                new_isbn = str(_convert_isbn_convert_10_to_13(old_isbn))
                if len(new_isbn) == 13:
                    if isinstance(new_isbn, str):
                        new_isbn = unicode(new_isbn)
                    mysql = "UPDATE identifiers SET val = ? WHERE book = ? AND type = 'isbn' "
                    #__execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_isbn, book)
                    my_cursor.execute(mysql,(new_isbn, book))  #apsw
                    log("ISBN10 Converted to ISBN13: " + str(old_isbn) + " >>> " + str(new_isbn))
                else:
                    if isinstance(old_isbn, str):
                        old_isbn = unicode(old_isbn)
                    log("This ISBN10 appears to not really be an ISBN10, and was deleted:  " + str(old_isbn) )
                    mysql = "DELETE FROM identifiers WHERE val = ? AND book = ? AND type = 'isbn' "
                    #__execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, old_isbn, book)
                    my_cursor.execute(mysql,(old_isbn, book))  #apsw
            else:
                if isinstance(old_isbn, str):
                    old_isbn = unicode(old_isbn)
                log("This ISBN appears to not really be any kind of ISBN, and was deleted:  " + str(old_isbn) )
                mysql = "DELETE FROM identifiers WHERE val = ? AND book = ? AND type = 'isbn' "
                #__execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, old_isbn, book)
                my_cursor.execute(mysql,(old_isbn, book))  #apsw
            sleep(0.05)
            if my_counter > 500 :
                try:
                    my_cursor.execute("commit")   #apsw
                    my_cursor.execute("begin")  #apsw
                except:
                    pass
                my_counter = 0
                log("Total ISBNs Processed So Far: " + str(my_total) + "  " + str((my_total/n)) )
        #END FOR
        if my_counter > 0:
            try:
                my_cursor.execute("commit")   #apsw
            except:
                pass
        log("Total ISBNs Converted or Deleted: " + str(my_total))

 #----------------------------------------------------------------------------------------------------------------
 #----------------------------------------------------------------------------------------------------------------

def _convert_isbn_check_digit_13(isbn):
    try:
        assert len(isbn) == 12
        sum = 0
        for i in range(len(isbn)):
            c = int(isbn[i])
            if i % 2: w = 3
            else: w = 1
            sum += w * c
        r = 10 - (sum % 10)
        if r == 10: return '0'
        else: return str(r)
    except:
        return isbn

def _convert_isbn_convert_10_to_13(isbn):
    try:
        assert len(isbn) == 10
        prefix = '978' + isbn[:-1]
        check = _convert_isbn_check_digit_13(prefix)
        return prefix + check
    except:
        return isbn

 #----------------------------------------------------------------------------------------------------------------
 #----------------------------------------------------------------------------------------------------------------
def fix_bad_authors_to_conform_to_explosion_logic(my_db, my_cursor):
    #concatenates "bad authors" and makes them a single work author. example:  Cowgirls Up & Rodeo (2009)
    #do what ui.py does with copying real to work for only selected books...

    list_of_books_with_2_authors = []

    try:
        mysql = "SELECT book,name FROM __book_author_name_sort  WHERE book NOT NULL"
        my_cursor.execute (mysql)
        tmp_rows = my_cursor.fetchall()
        if not tmp_rows:
            return
        else:
            n_rows = len(tmp_rows)
            if n_rows == 0:
                return
        for row in tmp_rows:
            tmp_rows2 = []
            del tmp_rows2
            book,name = row
            mysql = str("SELECT COUNT(book) FROM __book_author_name_sort  WHERE book = ?  GROUP BY book")
            my_cursor.execute (mysql,([book]))
            tmp_rows2 = my_cursor.fetchall()
            if not tmp_rows2:
                continue
            else:
                n_rows = len(tmp_rows2)
                if n_rows == 0:
                    continue
                for item in tmp_rows2:
                    for col in item:
                        count = str(col)
                if count <> '2' :       #Cowgirls Up & Rodeo (2009)
                    continue
                list_of_books_with_2_authors.append(str(book))
    except Exception as e:
        raise e
        log(str(e))
        my_db.close()
        return

    n = len(list_of_books_with_2_authors)
    if n == 0:
        return

    set_temp = set(list_of_books_with_2_authors)
    list_of_books_with_2_authors = list(set_temp)     #no duplicates
    list_of_books_with_2_authors.sort()

    for row in list_of_books_with_2_authors:
        tmp_rows = []
        del tmp_rows
        book = str(row)
        mysql = "SELECT book,name FROM __book_author_name_sort  WHERE book = ?"
        my_cursor.execute (mysql,([book]))
        tmp_rows = my_cursor.fetchall()
        if not tmp_rows:
            continue
        else:
            n_rows = len(tmp_rows)
            if n_rows <> 2:
                continue
        author_is_bad = False        #same logic  as in ui.py for Cowgirl Up & Ride (2009)
        for row in tmp_rows:   #always 2 rows for a single book because of "count" logic above...
            book,name = row
            if any(char.isdigit() for char in name):
                author_is_bad = True
            if ":" in name or "{" in name:
                author_is_bad = True
        if not author_is_bad:
            continue
        author = " "
        for row in tmp_rows:
            book,name = row
            author = author + " & " + name
        #END FOR
        author = author.strip()
        if author.startswith("&"):
            author = author[1: ]
        author = author.strip()
        my_cursor.execute ("BEGIN TRANSACTION")
        mysql = "INSERT OR REPLACE INTO _books_work (book,booktitle,authorname,seriesname,seriesindex) VALUES (?,null,?,null,null) "
        my_cursor.execute (mysql,(book,author))
        my_db.commit()
    #END FOR
    return

 #----------------------------------------------------------------------------------------------------------------
 #----------------------------------------------------------------------------------------------------------------
 #----------------------------------------------------------------------------------------------------------------
 #----------------------------------------------------------------------------------------------------------------

#END of copywork.py