# -*- coding: utf-8 -*-
from __future__ import (unicode_literals, division, absolute_import, print_function)
__license__   = 'GPL v3'
__copyright__ = '2014, DaltonST <DaltonShiTzu@outlook.com>'
__docformat__ = 'restructuredtext en'
__my_version__ = "3.6.87"
import os, sys
import apsw 
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
from difflib import SequenceMatcher
import re
from calibre_plugins.quarantine_and_scrub.config import prefs
from calibre_plugins.quarantine_and_scrub.titlecase import titlecase
from calibre_plugins.quarantine_and_scrub.booklevel import  ( __explode_custom_column_8_if_needed,
                                                                                                      remove_all_double_quotes)
from calibre_plugins.quarantine_and_scrub.heading import log_heading_common
from calibre_plugins.quarantine_and_scrub.sqlobjects_global_authors import add_global_authors
myquotes = "\""
mysinglequote = "\'"
mynothing = ""
mynull = None
my_terminate_early = False
notifications = Queue()
my_current_book_id = "000000"
my_book_ids = []
scrubbed_books_final = {}
c_work_author_table = "custom_column_4"       
l_work_author_table = "books_custom_column_4_link"  
c_work_title_table = "custom_column_8"  
l_work_title_table = "books_custom_column_8_link"  
valid_global_authors = False
list_of_known_authors = []
list_of_known_books = []
list_of_known_books_swapped_author = []
re_already_imported = False
book_title_list = []
global_dict1 = {}
global_dict2 = {}
global_dict3 = {}
global_dict4 = {}
header_s1 = None
header_s2 = None
header_s3 = None
header_s4 = None
header_s5 = None
def main_scrub_author_level(self, guidb, book_ids, scrubbed_books_final, log=None, abort=None, notifications=True):
    global header_s1
    global header_s2
    global header_s3
    global header_s4
    global header_s5
    global my_terminate_early
    global mynothing
    global my_book_ids
    global scrubbed_books_final_list
    scrubbed_books_final_list = []
    notifications.put((0.002, 'Beginning Author-Level Scrubbing'))
    my_book_ids = deepcopy(book_ids)
    del book_ids
    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, '/')
    log("Library DB: " + path)
    try:
        my_db =apsw.Connection(path)
    except Exception as e:
        log(str(e))
        raise e
        return
    my_cursor = my_db.cursor()
    header_s1 =  str("SQLite Version: " + str(apsw.SQLITE_VERSION_NUMBER) + "  [APSW]")  
    mysql = str("PRAGMA main.locking_mode=EXCLUSIVE;")
    my_cursor.execute(mysql) 
    header_s2 = mysql
    header_s3 = "Beginning Author Level Scrubbing"
    log_heading_common(log,header_s1,header_s2,header_s3,header_s4,header_s5)
    mysql = "PRAGMA main.busy_timeout = 5000;"     
    my_cursor.execute(mysql)
    add_global_authors(my_db, my_cursor, log)    
    __delete_invalid_global_authors(my_db, my_cursor, log)
    Create_SQLite_User_Functions(my_db, my_cursor, log)
    __reformat_author_initials(my_db, my_cursor, log)
    __update_pristine_authors_for_utf8_display(my_db, my_cursor, log)
    sleep(0.2)
    __update_global_authors_for_utf8_display(my_db, my_cursor, log)
    sleep(0.2)
    update_global_author_sorts(my_db, my_cursor, log)
    sleep(0.2)
    update_global_author_links(my_db, my_cursor, log)
    sleep(0.2)
    __delete_invalid_global_authors(my_db, my_cursor, log)
    sleep(0.2)
    Scrub_Control(my_db, my_cursor, my_book_ids, notifications, log,scrubbed_books_final)
    __update_work_columns_for_utf8_display(my_db, my_cursor, notifications, log)
    my_db.close()
    sleep(1.0)
    log("Job complete.")
    return scrubbed_books_final
def Scrub_Control(my_db, my_cursor,  my_book_ids, notifications, log, scrubbed_books_final):
    scrubbed_books_final = {}
    __delete_unused_values(my_db, my_cursor, notifications, log)
    remove_all_double_quotes(my_db, my_cursor, notifications, log)
    __explode_custom_column_4_if_needed(my_db, my_cursor, log)   
    __explode_custom_column_8_if_needed(my_db, my_cursor, log)   
    __explode_custom_column_10_if_needed(my_db, my_cursor, log)  
    s_abort_execution = __count_auth_ok_usage(my_db, my_cursor, log)
    if s_abort_execution == "FALSE":
        __count_global_authors_series(my_db, my_cursor, log)
        Author_Level_Scrub_Control(my_db, my_cursor, notifications, log)
        __delete_unused_values(my_db, my_cursor, notifications, log)
    else:
        for x in range(1,20):
            log("<font color='#FF0000'>This job cannot be run a second time for the same set and statuses of Work Data.  Recopy all Real Data to Work Data first.</font>")
    if s_abort_execution == "TRUE":
        notifications.put((0.99, 'TERMINATING DUE TO ERROR: SEE LOG'))
        sleep(10.0)
    mysql = "SELECT Count(*) FROM __book_real_and_work_comparison WHERE (realtitle <> worktitle OR realauthor <> workauthor) \
                    AND (book NOT IN(SELECT book FROM __books_work_status WHERE value = 'book_ok' )) "
    my_cursor.execute(mysql)
    count1 = my_cursor.fetchall()
    count1 = __strip_numerics(count1)
    log(" ")
    log(" ")
    log("Total Number of  Books with Authors and/or Titles Just Changed:  ", str(count1))
    log(" ")
    log(" ")
    return scrubbed_books_final
def Author_Level_Scrub_Control(my_db, my_cursor, notifications, log):
    notifications.put((0.01, 'Beginning Analysis of All Authors and Titles'))
    mysql = "SELECT Count(*) FROM books "
    my_cursor.execute(mysql)
    count1 = my_cursor.fetchall()
    count1 = __strip_numerics(count1)
    log("Number of books: " + str(count1))
    if count1 == "0" or count1 == "[0]":
        return
    mysql = "SELECT Count(*) FROM books_custom_column_4_link WHERE book NOT NULL"
    my_cursor.execute(mysql)
    count1 = my_cursor.fetchall()
    count1 = __strip_numerics(count1)
    log("Number of books with a Work Author: " + str(count1))
    if count1 == "0" or count1 == "[0]":
        return
    mysql = "SELECT Count(*) FROM __books_work_status WHERE value = 'book_ok' "
    my_cursor.execute(mysql)
    count1 = my_cursor.fetchall()
    count1 = __strip_numerics(count1)
    log("Number of books with a Status of 'book_ok': " + str(count1))
    mysql = "SELECT Count(*) FROM __books_work_status WHERE value = 'dirty' "
    my_cursor.execute(mysql)
    count1 = my_cursor.fetchall()
    count1 = __strip_numerics(count1)
    log("Number of books with a Status of 'dirty': " + str(count1))
    if count1 == "0" or count1 == "[0]":
        return
    notifications.put((0.01, 'Please Be Patient While All Titles and Authors Are Compared to All Global Authors and Pristine Authors In All Of Their Permutations'))
    log(" ")
    log("<font size='4'><b><font color='#0000FF'>'New' Valid Real Authors Must Be Added to the Pristine Author Table Prior to Author Level Scrubbing.</b></font>")
    log("<font size='4'><b><font color='#0000FF'>'New' Valid Real Authors Are Not Already Global Authors and Not Already Pristine Authors.</b></font>")
    log("<font size='4'><b><font color='#0000FF'>Calibre>Q&S>[Menu] Q&S Copy Work to Real > Copy Valid Real Authors to Q&S Pristine Author Validation Table [Selected Books] </b></font>")
    log("<font size='4'><b><font color='#0000FF'>Be certain to select only truly valid Real Authors to copy to the  Q&S Pristine Author Validation Table.</b></font>")
    log(" ")
    Scrub_author_in_author_but_split_in_two(my_db, my_cursor, notifications, log)  
    Scrub_author_in_title_but_split_in_two(my_db, my_cursor, notifications, log)
    Scrub_author_title_reversed(my_db, my_cursor, notifications, log)      
    Scrub_author_in_author_with_nonauthor(my_db, my_cursor, notifications, log)
    Rename_authors_per_mapping_rules(my_db, my_cursor, notifications, log)  
    global mynothing
    global list_of_known_books_swapped_author
    global list_of_known_authors
    list_of_known_authors = []
    tmp_rows = []
    del tmp_rows
    bstr_author = ""
    utf8_author = ""
    sleep(1.0)
    notifications.put((0.51, 'Selecting Global Authors'))
    log(" ")
    log(" ")
    log("Biographies & Autobiographies (per Work Tags) are ignored due to inability to accurately distinguish between Author and the Subject Person.")
    log(" ")
    mysql = 'SELECT name from _global_authors  ;'
    tmp_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_rows:
        pass 
    else:
        for row in tmp_rows:
            utf8_author = str(row)
            bstr_author = str(utf8_author)
            bstr_author = str(__change_tuple_to_bytestring(bstr_author))
            bstr_author = str(bstr_author.replace(",", mynothing, 4))
            if bstr_author == mynothing or (not bstr_author > " ") :
                continue
            else:
                list_of_known_authors.append(bstr_author)
        set_of_authors = set( list_of_known_authors)
        list_of_known_authors = list(set_of_authors)
    notifications.put((0.52, 'Selecting Pristine Authors'))
    tmp_rows = []
    del tmp_rows
    bstr_author = ""
    utf8_author = ""
    mysql = 'SELECT name from _pristine_authors  ;'
    tmp_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_rows:
        pass 
    else:
        if len(tmp_rows) == 0:
            pass
        else:
            for row in tmp_rows:
                utf8_author = str(row)
                bstr_author = str(utf8_author)
                bstr_author = str(__change_tuple_to_bytestring(bstr_author))
                bstr_author = str(bstr_author.replace(",", mynothing, 4))
                if bstr_author == mynothing or (not bstr_author > " ") :
                    continue
                else:
                    list_of_known_authors.append(bstr_author)
        set_of_authors = set( list_of_known_authors)
        list_of_known_authors = list(set_of_authors)
        n = len(list_of_known_authors)
        notifications.put((0.53, 'Making a List of Books'))
    Create_book_title_list(my_db, my_cursor, notifications, log)
    notifications.put((0.55, 'Identifying Remaining Titles Containing Authors'))
    Make_list_of_known_books_swapped_author(my_db, my_cursor, notifications, log)
    n1 = 0
    try:
        n1 = len(list_of_known_books_swapped_author)
        if n1 < 1 :
            return
        else:
            pass
    except:
        return
    notifications.put((0.80, 'Fixing Swapped Authors/Titles Etc.'))
    Swap_author_title(my_db, my_cursor, notifications, log)
def Make_list_of_known_books_swapped_author(my_db, my_cursor, notifications, log):
    global mynothing
    global list_of_known_authors
    global list_of_known_books_swapped_author
    global global_dict1
    global global_dict2
    global global_dict3
    global global_dict4
    tmp_rows = []
    del tmp_rows
    list_of_known_books_swapped_author = []
    global_dict1 = {}
    global_dict2 = {}
    global_dict3 = {}
    global_dict4 = {}
    for row in list_of_known_authors:  
        tmp_value = str(row)            
        tmp_rows = __search_title(tmp_value)
        if not tmp_rows:
            continue
        else:
            for row in tmp_rows:
                tmp_book = str(row)
                tmp_book = str(__change_tuple_to_bytestring(tmp_book))
                tmp_book = str(tmp_book.replace(",", mynothing, 4))
                list_of_known_books_swapped_author.append(tmp_book)
                global_dict1[tmp_book] = str(tmp_value)
            del tmp_rows
    tmp_rows = []
    del tmp_rows
    for row in list_of_known_authors:  
        tmp_value = str(row)
        try:
            s = tmp_value.split(" ")
            s1 = s[0]
            s2 = s[1]
            s1 = str(s1.strip())
            s2 = str(s2.strip())
            tmp_value = str(s2 + "," + s1)       
            tmp_rows = __search_title(tmp_value)
            if not tmp_rows:
                continue
            else:
                for row in tmp_rows:
                    tmp_book = str(row)
                    tmp_book = str(__change_tuple_to_bytestring(tmp_book))
                    tmp_book = str(tmp_book.replace(",", mynothing, 4))
                    list_of_known_books_swapped_author.append(tmp_book)
                    global_dict2[tmp_book] = str(tmp_value)
                del tmp_rows
        except:
            pass
    tmp_rows = []
    del tmp_rows
    for row in list_of_known_authors:  
        tmp_value = str(row)
        try:
            s = tmp_value.split(" ")
            s1 = s[0]
            s2 = s[1]
            s1 = str(s1.strip())
            s2 = str(s2.strip())
            tmp_value = str(s2 + ", " + s1) 
            tmp_rows = __search_title(tmp_value)
            if not tmp_rows:
                continue
            else:
                for row in tmp_rows:
                    tmp_book = str(row)
                    tmp_book = str(__change_tuple_to_bytestring(tmp_book))
                    tmp_book = str(tmp_book.replace(",", mynothing, 4))
                    list_of_known_books_swapped_author.append(tmp_book)
                    global_dict3[tmp_book] = str(tmp_value)
                del tmp_rows
        except:
            pass
    tmp_rows = []
    del tmp_rows
    for row in list_of_known_authors:  
        tmp_value = str(row)            
        tmp_value = str(tmp_value.strip())
        s0 = str(tmp_value.replace(" ", ".", 1))
        tmp_value = str(tmp_value.replace(" ", "\.", 1))
        tmp_rows = __search_title(tmp_value)
        if not tmp_rows:
            continue
        else:
            for row in tmp_rows:
                tmp_book = str(row)
                tmp_book = str(__change_tuple_to_bytestring(tmp_book))
                tmp_book = str(tmp_book.replace(",", mynothing, 4))
                list_of_known_books_swapped_author.append(tmp_book)
                global_dict4[tmp_book] = str(s0)
            del tmp_rows
    set_of_known_books_swapped_author = set(list_of_known_books_swapped_author) 
    list_of_known_books_swapped_author = list(set_of_known_books_swapped_author) 
    n = len(list_of_known_books_swapped_author)
    del set_of_known_books_swapped_author
def __search_title(tmp_value):
    global book_title_list
    tmp_rows = []
    del tmp_rows
    tmp_rows = []
    tmp_value = str(tmp_value)
    ns = tmp_value.count(" ")
    nc = tmp_value.count(",")
    nd = tmp_value.count("\.")
    if ns == 0 and nc == 0 and nd == 0:
        return None
    n_forever_jack = tmp_value.count(", ") 
    n_length = len(tmp_value)
    if n_length < 9 and n_forever_jack > 0: 
        return None
    tmp_value = str(tmp_value.replace("|", " ", 10)) 
    n = tmp_value.count("\.") 
    if n > 0:
        pass
    else:
        tmp_value = str(tmp_value.replace(".", mynothing, 50)) 
    tmp_value = str(tmp_value.replace("MD", mynothing, 10))
    tmp_value = str(tmp_value.replace("PhD", mynothing, 10))
    tmp_value = str(tmp_value.replace("PHD", mynothing, 10))
    tmp_value = str(tmp_value.replace("  ", " ", 10))
    tmp_value = str(tmp_value.strip())
    my_re1 = tmp_value
    p1 = re.compile(my_re1, re.IGNORECASE)
    i = 1
    for row in book_title_list:
        s = str(row)
        match1 = p1.search(s)
        if match1:
            s_list = s.split("^")
            s1 = str(s_list[0])
            tmp_rows.append(s1)
        i = i + 1
    n = len(tmp_rows)
    if not n > 0:
        tmp_rows = None
    return tmp_rows
def Create_book_title_list(my_db, my_cursor, notifications, log):
    global book_title_list
    global mynothing
    book_title_list = []
    mysql = "SELECT book, booktitle FROM __books_work_populate WHERE tagsall NOT LIKE '%biograph%' "
    tmp_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    tmp_rows.sort()
    for row in tmp_rows:
        book, title = row
        s = str(book)
        book = str(__change_tuple_to_bytestring(s))
        s1 = str(title)
        title = str(__change_tuple_to_bytestring(s1))
        title = str(title.replace("|", " ", 10))
        title = str(title.replace(",  ", ", ", 10))
        title = str(title.replace("  ", " ", 10))
        s2 = str(book + "^" + title)
        book_title_list.append(s2)
    s_set = set(book_title_list)
    book_title_list = list(s_set)
    book_title_list.sort()
def Swap_author_title(my_db, my_cursor, notifications, log):
    global mynothing
    global list_of_known_authors
    global list_of_known_books_swapped_author
    global global_dict1
    global global_dict2
    global global_dict3
    global global_dict4
    tmp_auth_rows = []
    del tmp_auth_rows
    tmp_title_rows = []
    del tmp_title_rows
    n0 = len(list_of_known_books_swapped_author)
    if n0 == 0:
        return
    log("Additional Author-in-Title Scrubbing will now be performed.")
    n1 = 0
    for row in list_of_known_books_swapped_author:
        n1 = n1 + 1
        n2 = float(n1/n0)
        pc = float((.2* n2) + .80)
        notifications.put((pc, 'Fixing Swapped Authors/Titles'))
        sleep(.7)
        my_current_book = str(row)
        my_current_book = str(__change_tuple_to_bytestring(my_current_book))
        mysql = "SELECT authname from __books_work_populate WHERE book = '" + my_current_book + "' ;"
        tmp_auth_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
        if not tmp_auth_rows:
            continue
        else:
            tmp_auth = str(tmp_auth_rows[0])
            tmp_auth = str(__change_tuple_to_bytestring(tmp_auth))
            tmp_auth = str(tmp_auth.replace(",", mynothing, 2))
        mysql = "SELECT booktitle FROM __books_work_populate WHERE book = '" + my_current_book + "' ;"
        tmp_title_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
        if not tmp_title_rows:
            continue
        else:
            tmp_title = str(tmp_title_rows[0])
            tmp_title = str(__change_tuple_to_bytestring(tmp_title))
            tmp_title = str(tmp_title.replace(",", mynothing, 2))
        re_1 = "^[a-z ]+[']s\s[a-z ]+$"                                     
        p = re.compile(re_1, re.IGNORECASE)
        match1 = p.search(tmp_title)
        if match1:
            continue
        try:
            m1 = global_dict1[my_current_book]
        except:
            m1 = None
        try:
            m2 = global_dict2[my_current_book]
        except:
            m2 = None
        try:
            m3 = global_dict3[my_current_book]
        except:
            m3 = None
        try:
            m4 = global_dict4[my_current_book]
        except:
            m4 = None
        if (not m1) and (not m2) and (not m3) and (not m4):
            continue
        new_auth = "ERROR" 
        tmp_title = str(tmp_title.replace("|", "", 10))
        if m1:
            if m1 <>  mynothing and m1 > "":
                m1 = str(__change_tuple_to_bytestring(m1))
                m1 = str(m1.strip())
                tmp_title = str(tmp_title.replace(m1, mynothing, 1))
                new_auth = str(m1)
            else:
                continue
        else:
            if m2:
                if m2 <> mynothing and m2 > " ":
                    m2 = str(__change_tuple_to_bytestring(m2))
                    m2 = str(m2.strip())
                    tmp_title = str(tmp_title.replace(m2, mynothing, 1))
                    new_auth = str(m2)
                else:
                    continue
            else:
                if m3:
                    if m3 <> mynothing and m3 > " ":
                        m3 = str(__change_tuple_to_bytestring(m3))
                        m3 = str(m3.strip())
                        tmp_title = str(tmp_title.replace(m3, mynothing, 1))
                        new_auth = str(m3)
                    else:
                        continue
                else:
                   if m4:
                        if m4 <> mynothing and m4 > " ":
                            m4 = str(__change_tuple_to_bytestring(m4))
                            m4 = str(m4.strip())
                            tmp_title = str(tmp_title.replace(m4, mynothing, 1))
                            new_auth = str(m4)
                        else:
                            continue
                   else:
                        continue
        tmp_title = tmp_title.strip()
        tmp_auth = tmp_auth.strip()
        s = str(tmp_auth)
        s = str(s.replace(",", " ", 1))
        tmp_title = str(tmp_title.replace(tmp_auth, mynothing, 1))
        tmp_title = str(tmp_title.replace(s, mynothing, 1))
        tmp_title = tmp_title.strip()
        s_separator = " "
        n_colona = tmp_auth.count(":")
        n_dasha = tmp_auth.count("-")
        n_colont = tmp_title.count(":")
        n_dasht = tmp_title.count("-")
        if n_colont > 0: s_separator = " : "
        else:
            if n_dasht > 0: s_separator = " - "
            else:
                if n_colona > 0: s_separator = " : "
                else:
                    if n_dasha > 0: s_separator = " - "
        new_title = str(tmp_auth + s_separator + tmp_title) 
        s = str(new_auth)
        s = str(s.replace(",", mynothing, 1))
        new_title = str(new_title.replace(new_auth, mynothing, 1))
        new_title = str(new_title.replace(s, mynothing, 1))
        new_title = str(new_title.replace("--", "- ", 1))
        new_title = str(new_title.replace("- -", "- ", 1))
        new_title = str(new_title.replace("-  -", "- ", 1))
        new_title = str(new_title.replace(": :", ": ", 1))
        new_title = str(new_title.replace(":  :", ": ", 1))
        new_title = str(new_title.replace("|", "", 10))
        new_title = new_title.strip()
        if new_title.startswith("-") or new_title.startswith(":"):
            new_title = new_title[1: ]
            new_title = new_title.strip()
        if new_title.endswith("-") or new_title.endswith(":"):
            new_title = new_title[0:-1]
            new_title = new_title.strip()
        new_title = str(new_title.replace("|", ",", 1))
        if new_auth == mynothing or (not new_auth > " "):
            continue
        new_auth = str(new_auth.replace(".", " ", 1)) 
        if new_title.startswith("Unknown"):
            new_title = str(new_title.replace("Unknown", mynothing, 1)) 
            new_title = new_title.strip()
        tmp_id_rows = []
        del tmp_id_rows
        tmp_id_rows = []
        mysql = 'SELECT id from custom_column_4 WHERE value = "' + new_auth + '" ;'
        tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
        if tmp_id_rows:
            sleep(.05)
            tmp_id = tmp_id_rows[0]
            tmp_id = __strip_numerics(tmp_id)
            mysql = "DELETE FROM books_custom_column_4_link WHERE book = '" + my_current_book + "' ;"
            __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
            mysql = "INSERT INTO books_custom_column_4_link (id,book,value) VALUES (null, '" + my_current_book + "','" + tmp_id + "'); "
            __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        else:
            sleep(.05)
            mysql = 'INSERT OR IGNORE INTO custom_column_4 (id,value) VALUES (null, "' + new_auth + '") ;'
            __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
            sleep(.2)
            mysql = 'SELECT id from custom_column_4  WHERE value = "' + new_auth + '" ;'
            tmp_auth_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
            if not tmp_auth_rows:
                continue
            else:
                tmp_id = tmp_auth_rows[0]
                tmp_id = __strip_numerics(tmp_id)
            sleep(.3)
            mysql = "DELETE FROM books_custom_column_4_link WHERE book = '" + my_current_book + "' ;"
            __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
            sleep(.1)
            mysql = "INSERT  INTO books_custom_column_4_link (id, book, value) VALUES \
                            (null, '" + my_current_book + "', '" + tmp_id + "');"
            __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(.1)
        mysql = str('UPDATE custom_column_8 SET value =  ? WHERE  custom_column_8.id IN \
                                    (SELECT value FROM books_custom_column_8_link WHERE book = ? ) ;')
        __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_title, my_current_book)
        tmp_id_rows = []
        del tmp_id_rows
        mysql = "SELECT id FROM custom_column_18 WHERE value = 'auth_ok' "
        tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
        if not tmp_id_rows:
            sleep(.1)
            mysql = "INSERT OR IGNORE INTO custom_column_18 (id,value) VALUES (null, 'auth_ok') "
            __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
            sleep(.1)
            mysql = "SELECT id FROM custom_column_18 WHERE value = 'auth_ok' "
            tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
        if not tmp_id_rows:
            continue
        tmp_id = tmp_id_rows[0]
        tmp_id = __strip_numerics(tmp_id)
        sleep(.1)
        mysql = "DELETE FROM books_custom_column_18_link WHERE book = ? AND book > ?"
        __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, my_current_book, "0")
        sleep(.2)
        mysql = "INSERT OR REPLACE INTO books_custom_column_18_link (id,book,value) VALUES (null,?,?) "
        __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, my_current_book, tmp_id)
        __clear_work_freeze(my_db, my_cursor, my_current_book, notifications, log)
        scrub_author_good_but_author_also_in_title(my_db, my_cursor,  my_current_book, notifications, log)
def scrub_author_good_but_author_also_in_title(my_db, my_cursor,  my_current_book, notifications, log):
    global mynothing
    mysql = "SELECT authname, booktitle from  __books_work_populate WHERE book = '" + my_current_book + "' ;"
    tmp_auth_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if tmp_auth_rows:
        for row in tmp_auth_rows:
            tmp_auth, tmp_title = row
            break
    else:
        return
    tmp_auth = str(__change_tuple_to_bytestring(tmp_auth))
    tmp_title = str(__change_tuple_to_bytestring(tmp_title))
    orig_title = str(tmp_title)
    s_string = str("scrub_author_good_but_author_also_in_title...[0]prior to update or return:  origtitle:  \
                        " + str(orig_title) + ">>>>>tmptitle:" + str(tmp_title) + ">>>>>tmpauth:" + str(tmp_auth))
    n_orig1 = len(tmp_title)
    re_1 = "^[a-z ]+[']s\s[a-z ]+$"                                     
    p = re.compile(re_1, re.IGNORECASE)
    match1 = p.search(tmp_title)
    if match1:
        return
    if tmp_title == "Collected Stories of":   
        tmp_title = str("Collected Stories")
    s_string = str("scrub_author_good_but_author_also_in_title....[1]prior to update or return:  origtitle:  \
                    " + str(orig_title) + ">>>>>tmptitle:" + str(tmp_title) + ">>>>>tmpauth:" + str(tmp_auth))
    n_orig1 = len(tmp_title)
    tmp_title = str(tmp_title.replace(tmp_auth, mynothing, 1)) 
    tmp_title = str(tmp_title.strip()) 
    a = str(tmp_auth) 
    a_split = a.split(" ")
    n1 = len(a_split)
    if n1 == 2:         
        b = str(a_split[1] + " " + a_split[0])  
        tmp_title = tmp_title.replace(b, mynothing, 1)
        b = str(a_split[1] + ", " + a_split[0])  
        tmp_title = tmp_title.replace(b, mynothing, 1)
    else:
        if n1 == 3:    
            b = str(a_split[2] + " " + a_split[0]  + " " + a_split[1])
            tmp_title = tmp_title.replace(b, mynothing, 1)
            b = str(a_split[2] + ", " + a_split[0]  + " " + a_split[1])
            tmp_title = tmp_title.replace(b, mynothing, 1)
        else:
            pass
    tmp_title = str(tmp_title.strip()) 
    n_orig2 = len(tmp_title)
    if n_orig1 <> n_orig2:
        pass
    else:
        a = str(tmp_auth) 
        t1 = tmp_title.replace(a, mynothing, 1)
        a = a.replace(".",". ", 4) 
        t2 = t1.replace(a, mynothing, 1) 
        a = a.replace(".", mynothing, 4) 
        t3 = t2.replace(a, mynothing, 1) 
        a_split = a.split(" ") 
        for row in a_split:
            a0 = str(row)
            t4 = str(t3.replace(a0, mynothing, 1)) 
            a0 = str(a0.replace(".", ". ", 4)) 
            t5 = str(t4.replace(a0, mynothing, 1)) 
            t6 = str(t5.replace(". ", mynothing, 10))
            if t6.startswith(","):
                t6 = t6[1: ]
            t6 = t6.strip()
            t3 = t6
        tmp_title = str(t6)
        tmp_title = str(tmp_title.replace("  ", " ", 4))             
        tmp_title = str(tmp_title.strip()) 
        tmp_title = str(titlecase(tmp_title))
        tmp_title = str(tmp_title.strip()) 
    if tmp_title == mynothing:
        tmp_title = "_Unknown_"
    if tmp_title.startswith("-") or tmp_title.startswith(":") or tmp_title.startswith(";"):
        tmp_title = str(tmp_title[1: ])
        tmp_title = str(tmp_title.strip()) 
    s_string = str("scrub_author_good_but_author_also_in_title....[2]prior to update or return:  origtitle:\
                    " + str(orig_title) + ">>>>>tmptitle:" + str(tmp_title) + ">>>>>tmpauth:" + str(tmp_auth))
    if tmp_title == orig_title or tmp_title == mynothing or (not (tmp_title > " ")):
        return
    tmp_title = str(__change_tuple_to_bytestring(tmp_title))
    tmp_title = str(tmp_title.strip()) 
    mysql = str('UPDATE custom_column_8 SET value =  ? WHERE  custom_column_8.id IN \
                                (SELECT value FROM books_custom_column_8_link WHERE book = ? ) ;')
    __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql,  tmp_title, my_current_book)
def __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql):
    sleep(0.1)
    tmp_rows = []
    del tmp_rows
    tmp_rows = []
    mysql = str(mysql)
    try:
        my_cursor.execute(mysql)
        tmp_rows = my_cursor.fetchall()
        if tmp_rows:
            return tmp_rows
        else:
            tmp_rows = []
            return tmp_rows
    except:
        sleep(11.0)
        tmp_rows = []
        del tmp_rows
        tmp_rows = []
        try:
            my_cursor.execute(mysql)
            tmp_rows = my_cursor.fetchall()
            if tmp_rows:
                return tmp_rows
            else:
                tmp_rows = []
                return tmp_rows
        except Exception as e:
            log(str(e))
            my_db.close()
            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:
        my_cursor.execute("begin")
        my_cursor.execute(mysql) 
        my_cursor.execute("commit")
    except:
        sleep(5.0)
        try:
            my_cursor.execute(mysql) 
            my_cursor.execute("commit")
        except Exception as e:
            log(str(e))
            my_db.close()
            raise e
            sys.exit("ERROR in __execute_mysql_with_commit_generic....")
    sleep(0.03)
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
    s2 = str(v2) 
    s2 = str(__change_tuple_to_bytestring(s2))
    if s2 == mynothing:
        s2 = str("IGNORE")
    try:
        my_cursor.execute("begin")
        if s2 <> "IGNORE":
            my_cursor.execute(mysql,(v1,v2)) 
        else:
            my_cursor.execute(mysql,(v1)) 
        my_cursor.execute("commit")
    except:
        log("database locked; sleeping and then will retry")
        sleep(25.0)
        try:
            if s2 <> "IGNORE":
                my_cursor.execute(mysql,(v1,v2)) 
            else:
                my_cursor.execute(mysql,(v1)) 
            my_cursor.execute("commit")
        except Exception as e:
            log(str(e))
            my_db.close()
            raise e
            sys.exit("ERROR in __execute_mysql_for_custom_column_generic")
    sleep(0.03)
def __execute_mysql_for_custom_column_generic_3_args(my_db, my_cursor, log, mysql, v1, v2, v3):
    global mynothing
    global freeze_current_book
    freeze_current_book = True
    try:
        my_cursor.execute("begin")
        my_cursor.execute(mysql,(v1,v2,v3)) 
        my_cursor.execute("commit")
    except Exception as e:
        log(str(e))
        my_db.close()
        raise e
        sys.exit("ERROR in __execute_mysql_for_custom_column_generic_3_args")
    sleep(0.03)
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):
    global mynothing
    s_string = str(tuple)
    s_string = s_string.replace('(u"', mynothing, 100)
    s_string = s_string.replace("(u'", mynothing, 100)
    s_string = s_string.replace("',))", mynothing, 100)
    s_string = s_string.replace("',)", mynothing, 100)
    s_string = s_string.replace('",)', mynothing, 100)
    s_string = s_string.replace('u"', mynothing, 100)  
    s_string = s_string.replace("u'", mynothing, 100)
    s_string = s_string.replace("'(", "'", 100)
    s_string = s_string.replace(")'", "'", 100)
    s_string = s_string.replace('u"', mynothing, 100) 
    s_string = s_string.replace('")', mynothing, 100)
    n1 = s_string.find("(")
    n2 = s_string.find(")")
    if n1 >= 0:
        if n2 >= 0 and n2 > n1:  
            return s_string
        else: 
            pass
    else:
        pass
    n = s_string.find("(")
    if n == 0:
        s_string = s_string.replace("(", mynothing, 1)
        c = s_string.count(")") 
        if c == 1:
            s_string = s_string.replace(")", mynothing, 1) 
        else:
            pass
    n = s_string.rfind("'") 
    l = len(s_string) 
    if n == (l - 1): 
        z = (n - 1)
        s_string = s_string[0:z] 
    else:
        pass
    n = s_string.rfind(")") 
    l = len(s_string) 
    if n == (l - 1): 
        z = (n - 1)
        s_string = s_string[0:z] 
        pass
    n = s_string.rfind("'") 
    l = len(s_string) 
    if n == (l - 1): 
        z = (n - 1)
        s_string = s_string[0:z] 
    else:
        pass
    n = s_string.rfind(")") 
    l = len(s_string) 
    if n == (l - 1): 
        z = (n - 1)
        s_string = s_string[0:z] 
        pass
    s_string = s_string.strip()
    if isinstance(s_string, unicode):
        s_string = str(s_string)
        if isinstance(s_string, unicode):
            pass
    return s_string
def __printsafe(*arg):
    try:
        pass
    except:
        pass
def __update_work_columns_for_utf8_display(my_db, my_cursor, notifications, log):
    mysql = 'UPDATE custom_column_4 SET value = (update_utf8_for_display(value) ) '
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = 'UPDATE custom_column_8 SET value = (update_utf8_for_display(value) ) '
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
def Create_SQLite_User_Functions(my_db, my_cursor, log):
        global my_terminate_early
        try:
            my_db.createscalarfunction("update_utf8_for_display", SQLite_User_Function_1)
        except:
            my_terminate_early = True
            log("Create_SQLite_User_Function [1] failed...cannot proceed...")
        try:
            my_db.createscalarfunction("regexp", SQLite_User_Function_3)
        except Exception as e:
            my_terminate_early = True
            log("Create_SQLite_User_Function [3] failed...cannot proceed...")
        try:
            my_db.createscalarfunction("update_author_sort", SQLite_User_Function_5)
        except Exception as e:
            my_terminate_early = True
            log("Create_SQLite_User_Function [5] failed...cannot proceed...")
        try:
            my_db.createscalarfunction("update_author_link", SQLite_User_Function_6)
        except Exception as e:
            my_terminate_early = True
            log("Create_SQLite_User_Function [6] failed...cannot proceed...")
        try:
            my_db.createscalarfunction("u_rfind", SQLite_User_Function_7,2)           
        except Exception as e:
            my_terminate_early = True
            log("Create_SQLite_User_Function [7] failed...cannot proceed...")
        try:
            my_db.createscalarfunction("u_count", SQLite_User_Function_8,2)           
        except Exception as e:
            my_terminate_early = True
            log("Create_SQLite_User_Function [8] failed...cannot proceed...")
            log(str(e))
        try:
            my_db.createscalarfunction("reformat_initials", SQLite_User_Function_9,1)           
        except Exception as e:
            my_terminate_early = True
            log("Create_SQLite_User_Function [9] failed...cannot proceed...")
            log(str(e))
        sleep(1.0)
def SQLite_User_Function_1(s):
    mynothing = ""
    t = s
    if t is None:
        return s
    else:
        try:
            s_string = str(t)
            t = __change_tuple_to_bytestring(s_string)
            t = "u'" + s_string + "'"
            if isinstance(t, unicode):
                t = eval(t)  
                return t
            else:
                pass
        except Exception as e:
            pass
    return s
def SQLite_User_Function_3(regexpr,avalue):
    global re_already_imported
    if not re_already_imported:
        import re
        re_already_imported = True
    if regexpr:
        if avalue:
            try:
                s_string = str(avalue)
                s_string = __change_tuple_to_bytestring(s_string)
                re_string = str(regexpr)
                re_string = __change_tuple_to_bytestring(re_string)
                import re 
                p = re.compile(re_string, re.IGNORECASE)
                match = p.search(s_string)
                if match:
                    return True
                else:
                    return False
            except Exception as e:
                return False
def SQLite_User_Function_5(s):
    mynothing = ""
    t = s
    if t is None:
        return s
    else:
        try:
            t = str(t)
            t = __change_tuple_to_bytestring(t)
            t = str(t.replace(", Jr.", mynothing, 1))
            t = str(t.replace(", Jr", mynothing, 1))
            t = str(t.replace(" Jr.", mynothing, 1))
            t = str(t.replace(" Jr", mynothing, 1))
            t = str(t.replace(" II", mynothing, 1))
            t = str(t.replace(" III", mynothing, 1))
            t = str(t.replace(" Ph.D.", mynothing, 1))
            n = t.count(str(" "))
            if n == 0:
                return s
            if n == 1:
                s_split = t.split(" ")
                n = len(s_split)
                if n == 2:
                    firstname = str(s_split[0])
                    if len(firstname) == 1:
                        firstname = str(firstname + ".")
                    lastname = str(s_split[1])
                    firstname = str(firstname.strip())
                    lastname = str(lastname.strip())
                    ssort = str(lastname + ", " + firstname)
                    ssort = unicode(ssort)
                    return ssort
                else:
                    return s
            if n == 2:
                s_split = t.split(" ")
                n1 = len(s_split)
                if n1 == 3:
                    firstname = str(s_split[0])
                    middlename = str(s_split[1])
                    lastname = str(s_split[2])
                    firstname = str(firstname.strip())
                    if len(firstname) == 1:
                        firstname = str(firstname + ".")
                    middlename = str(middlename.strip())
                    if len(middlename) == 1:
                        middlename = str(middlename + ".")
                    lastname = str(lastname.strip())
                    ssort = str(lastname + ", " + firstname + " " + middlename)
                    ssort = unicode(ssort)
                    return ssort
                else:
                    return s
            return s
        except Exception as e:
            s_error = str(e)
            return s_error
            pass
    return s
def SQLite_User_Function_6(ssort):
    orig_sort = ssort
    newlink = str("")
    if ssort is None:
        return 'ERROR[NULL]'
    else:
        try:
            s = unicodedata.normalize('NFKD', ssort).encode('ascii', 'ignore')
            ssort = str(s)
            ssort = __change_tuple_to_bytestring(ssort)
            nc = ssort.count(",")
            ns = ssort.count(" ")
            if nc > 0 and ns == 0:   
                ssort = str(ssort.replace(",", ", ", 4)) 
            if ns == 0: 
                return str("~" + ssort + "~")
            ssort = str(ssort.replace(",", "", 4))
            s_split = ssort.split(" ")
            n = len(s_split)
            if n == 0 or n == 1:
                return "ERROR[0]"
            if ns == 1:       
                if n == 2:                           
                    s0 = str(s_split[0])          
                    s0 = str(s0.strip())
                    s1 = str(s_split[1])          
                    s1 = str(s1[0:1] + ".")     
                    s1 = str(s1.strip())
                    newlink = str("~" + s1 + " " + s0 )                                
                    newlink = str(newlink + str("~" + s1 +  s0 + "~") )      
                    if newlink > " ":
                        return unicode(newlink)
                    else:
                        return "ERROR[1A]"
                else: 
                    return "ERROR[1]"
            if ns == 2:            
                if n == 3:
                    s0 = str(s_split[0])          
                    s0 = str(s0.strip())
                    s1 = str(s_split[1])          
                    s1 = str(s1[0:1] + ".")     
                    s1 = str(s1.strip())
                    s2 = str(s_split[2])          
                    s2 = str(s2[0:1] + ".")     
                    s2 = str(s2.strip())
                    newlink = str("~" + s1 + " " + s2 + " " + s0)                      
                    newlink = str(newlink + str("~" + s1 + s2 + " " + s0) )      
                    newlink = str(newlink + str("~" + s1 + s2 + s0 + "~"))      
                    if newlink > " ":
                        return unicode(newlink)
                    else:
                        return "ERROR[2A]"
                else:
                    return "ERROR[2]"
            if ns == 3:    
                if n == 4:
                    s0 = str(s_split[0])          
                    s0 = str(s0.strip())
                    s1 = str(s_split[1])          
                    s1 = str(s1[0:1] + ".")     
                    s1 = str(s1.strip())
                    s2 = str(s_split[2])          
                    s2 = str(s2[0:1] + ".")     
                    s2 = str(s2.strip())
                    s3 = str(s_split[3])          
                    s3 = str(s3[0:1] + ".")     
                    s3 = str(s3.strip())
                    newlink = str("~" + s1 + " " + s2 + " " + s3 + " " + s0)              
                    newlink = str(newlink + str("~" + s1 + s2 + s3 + " " + s0) )      
                    newlink = str(newlink + str("~" + s1+ s2 + s3 + s0 + "~"))      
                    if newlink > " ":
                        return unicode(newlink)
                    else:
                        return "ERROR[3A]"
                else:
                    return "ERROR[3]"
            if " de " in ssort or " du " in ssort:  
                newlink = str("~" + ssort + "~")
                return unicode(newlink)
            else:
                return "ERROR[4]" 
        except Exception as e:
            s_error = str("ERROR[5]  " + str(e))
            return s_error
    return "ERROR[6]"
def SQLite_User_Function_7(searchedstring,searchforstring):
    if searchedstring is None or searchforstring is None:    
        return 0
    else:
        n = len(searchedstring)
        if n < 4:     
            return 0
        try:
            s = unicodedata.normalize('NFKD', searchedstring).encode('ascii', 'ignore')
            s = str(s.strip())
            t = str(searchforstring)
            ns = s.count(t)
            if ns == 0:
                return 0
            else:
                n = s.rfind(str(t))
                n = n + 1  
                return n
        except Exception as e:
            return 0
def SQLite_User_Function_8(searchedstring,countedstring):
    if searchedstring is None or countedstring is None:    
        return 0
    else:
        try:
            ns = searchedstring.count(countedstring)
            return ns
        except Exception as e:
            return 0
def SQLite_User_Function_9(name):
    if name is None:    
        return name
    else:
        try:
            initials_mode = 'A.B.'
            ignore_words = ['von', 'van', 'jr', 'jr.', 'sr', 'sr.', 'st', 'st.',
                            'ed', 'ed.', 'dr', 'dr.', 'phd', 'ph.d', 'ph.d.']
            ignore_upper_words = ['ii', 'iii']
            ignore_words_map = dict((k,True) for k in ignore_words)
            parts = name.split()
            new_parts = []
            append_to_previous = False
            for tok in parts:
                if len(tok) == 0:
                    continue
                handled = False
                if tok.lower() in ignore_words_map and tok.upper() != tok:
                    pass
                elif tok.lower() in ignore_upper_words:
                    pass
                elif tok.isdigit(): 
                    pass
                elif '.' in tok or (tok.upper() == tok and len(tok) <= 2):
                    if initials_mode == 'A.B.':
                        new_tok = ''
                        for c in tok.replace('.',''):
                            new_tok += c + '.'
                        if append_to_previous:
                            new_parts[-1] = new_parts[-1] + new_tok
                        else:
                            new_parts.append(new_tok)
                            append_to_previous = True
                    else:
                        return name
                    handled = True
                if not handled:
                    new_parts.append(tok)
                    append_to_previous = False
            return ' '.join(new_parts)
        except Exception as e:
            return name
def __reformat_author_initials(my_db, my_cursor, log):
    mysql = "UPDATE OR IGNORE _global_authors SET name =(reformat_initials(name)), sort = '', link = ''  \
                    WHERE name <> (reformat_initials(name)) "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE OR IGNORE _pristine_authors SET name =(reformat_initials(name)), sort = '', link = ''  \
                    WHERE name <> (reformat_initials(name)) "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE OR IGNORE _book_awards SET author =(reformat_initials(author))  "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "DELETE FROM _global_authors WHERE name <> (reformat_initials(name)) "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "DELETE FROM _pristine_authors WHERE name <> (reformat_initials(name)) "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "DELETE FROM _book_awards WHERE author <> (reformat_initials(author)) "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
def update_global_author_sorts(my_db, my_cursor, log):
    mysql = "UPDATE _global_authors SET sort = (update_author_sort(name)) WHERE sort NOT LIKE '%, %'  ;"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE _pristine_authors SET sort = ' '  ;"                                          
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE _pristine_authors SET sort = (update_author_sort(name)) ;"  
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
def update_global_author_links(my_db, my_cursor, log):
    mysql = "UPDATE _global_authors SET link = (update_author_link(sort)) WHERE link NOT LIKE '%~%'  ;"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE _global_authors SET link =(link|| substr(sort,1,(instr(sort,', ')- 0 ) )||' '||substr(name,1,1 )||'.'||'~'||substr(sort,1,(instr(sort,', ')- 0 ) )||''||\
                substr(name,1,1 )||'.'||'~'|| substr(sort,1,(instr(sort,',')- 1 ) )|| ' '||substr(name,1,1 )||'.'||'~'|| substr(sort,1,(instr(sort,',')- 1 ) )|| ' '||substr(name,1,1 )||''||' ^' ) \
                WHERE sort LIKE '%,%' AND link NOT LIKE '%^%'  ;"                                     
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE _pristine_authors SET link = ' '  ;"                                                  
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE _pristine_authors SET link = (update_author_link(sort));"              
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE _pristine_authors SET link =(link|| substr(sort,1,(instr(sort,', ')- 0 ) )||' '||substr(name,1,1 )||'.'||'~'||substr(sort,1,(instr(sort,', ')- 0 ) )||''||\
                substr(name,1,1 )||'.'||'~'|| substr(sort,1,(instr(sort,',')- 1 ) )|| ' '||substr(name,1,1 )||'.'||'~'|| substr(sort,1,(instr(sort,',')- 1 ) )|| ' '||substr(name,1,1 )||''||' ^' ) \
                WHERE sort LIKE '%,%' AND link NOT LIKE '%^%'  ;"                                     
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
def __explode_custom_column_4_if_needed(my_db, my_cursor, log):
    try:
        mysql = "DELETE FROM custom_column_4 WHERE id > 0 " 
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        mysql = "DELETE FROM books_custom_column_4_link WHERE id > 0 "
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(0.5)
        c_work_author_table = "custom_column_4"       
        mysql = "INSERT OR IGNORE INTO custom_column_4 SELECT book,name FROM __book_author_name_sort WHERE name not null"
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        l_work_author_table = "books_custom_column_4_link"  
        mysql = "INSERT or IGNORE INTO books_custom_column_4_link SELECT book,book,book FROM __book_author_name_sort "
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(0.5)
        mysql = "DELETE FROM books_custom_column_4_link WHERE book IN(SELECT book FROM __books_work_populate \
                                                                                                WHERE authname NOT NULL AND booktitle IS NULL \
                                                                                                AND seriesname  IS NULL AND seriesindex  IS NULL \
                                                                                                AND seriesfull  IS NULL AND tagsall  IS NULL)"
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(0.5)
        mysql = "DELETE FROM custom_column_4 WHERE id NOT IN(SELECT value FROM books_custom_column_4_link WHERE id > '0')"
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(0.5)
        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 WHERE book NOT NULL)"
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(0.5)
        mysql = "DELETE FROM _books_work WHERE book NOT NULL"
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(0.2)
    except Exception as e:
        raise e
def __explode_custom_column_10_if_needed(my_db, my_cursor, log):
    mysql = "INSERT or REPLACE INTO custom_column_10 (id,value) SELECT book, seriesname FROM __books_work_populate WHERE seriesname > ' '  ; "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.5)
    mysql = "UPDATE books_custom_column_10_link  SET value = books_custom_column_10_link.book "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.5)
    mysql = "DELETE FROM custom_column_10 WHERE id NOT IN (SELECT value FROM books_custom_column_10_link)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.5)
def __delete_unused_values(my_db, my_cursor, notifications, log):
    mysql = 'DELETE FROM custom_column_4 WHERE id IN __author_unused'
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = 'DELETE FROM custom_column_8 WHERE id IN __title_unused'
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = 'DELETE FROM custom_column_10 WHERE id IN __series_unused'
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = 'DELETE FROM custom_column_13 WHERE id IN __tags_unused'
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
def __clear_work_freeze(my_db, my_cursor, my_current_book, notifications, log):
    mysql = "DELETE FROM custom_column_16 WHERE book = '" + my_current_book + "' ;"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "INSERT INTO custom_column_16 (id,book,value) VALUES (null, ?, ?)"
    __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, my_current_book, "0")
def __count_global_authors_series(my_db, my_cursor, log):
    log("===================================")
    log("For Your Reference")
    log("===================================")
    mysql = "SELECT Count(*) FROM _global_authors"
    my_cursor.execute(mysql)
    count1 = my_cursor.fetchall()
    count1 = __strip_numerics(count1)
    log("Number of Global Authors:  ", str(count1))
    mysql = "SELECT Count(*) FROM _global_series"
    my_cursor.execute(mysql)
    count2 = my_cursor.fetchall()
    count2 = __strip_numerics(count2)
    log("Number of Global Series:    ", str(count2))
    mysql = "SELECT Count(*) FROM _pristine_authors"
    my_cursor.execute(mysql)
    count3 = my_cursor.fetchall()
    count3 = __strip_numerics(count3)
    log("Number of Pristine Authors:", str(count3))
    mysql = "SELECT Count(*) FROM _pristine_series"
    my_cursor.execute(mysql)
    count4 = my_cursor.fetchall()
    count4 = __strip_numerics(count4)
    log("Number of Pristine Series:  ", str(count4))
    log("===================================")
    log("===================================")
def __count_auth_ok_usage(my_db, my_cursor, log):
    s_abort_execution = str("FALSE")
    mysql = str("SELECT Count(*) FROM books_custom_column_18_link WHERE value IN(SELECT id FROM custom_column_18 WHERE value = 'auth_ok' ) ")
    my_cursor.execute(mysql)
    count1 = my_cursor.fetchall()
    count1 = str(__strip_numerics(count1))
    if count1 <> "[0]":
        s_abort_execution = str("TRUE")
    return s_abort_execution
def __delete_invalid_global_authors(my_db, my_cursor, log):
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%Unknown%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%Other Stories%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%Others%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%Various%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '% and %' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%:%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%-.%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '% by %' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '% of %' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE link LIKE '%ERROR[4]%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name NOT LIKE '% %' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%|%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%(%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%[%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _global_authors WHERE name LIKE '%editor%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%Unknown%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%Other Stories%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%Others%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%Various%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '% and %' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%:%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%-.%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '% by %' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '% of %' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE link LIKE '%ERROR[4]%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name NOT LIKE '% %' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%,%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%|%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%(%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%[%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    mysql = "DELETE FROM _pristine_authors WHERE name LIKE '%editor%' "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    my_cursor.execute("\
                            BEGIN TRANSACTION;\
                            DELETE FROM _global_authors WHERE name IN(SELECT name from _global_series);\
                            DELETE FROM _global_author_series_link WHERE authid NOT IN(SELECT id from _global_authors);\
                            DELETE FROM _global_author_series_link WHERE seriesid NOT IN(SELECT id from _global_series);\
                            DELETE FROM _pristine_authors WHERE name IN(SELECT name from _pristine_series);\
                            DELETE FROM _pristine_authors WHERE name IN(SELECT name from _global_series);\
                            DELETE FROM _pristine_authors WHERE name IN(SELECT name from _global_authors);\
                            DELETE FROM _pristine_series WHERE name IN(SELECT name from _global_series);\
                            DELETE FROM _pristine_author_series_link WHERE authid NOT IN(SELECT id from _pristine_authors);\
                            DELETE FROM _pristine_author_series_link WHERE seriesid NOT IN(SELECT id from _pristine_series);\
                            COMMIT;")
def __update_pristine_authors_for_utf8_display(my_db, my_cursor, log):
    sleep(0.02)
    try:
        mysql = 'UPDATE _pristine_authors SET name = (update_utf8_for_display(name) ) WHERE name LIKE "%\\%" '
        my_cursor.execute("begin")
        my_cursor.execute(mysql) 
        my_cursor.execute("commit")
    except:
        pass
    sleep(0.02)
    mysql = 'UPDATE _pristine_authors SET sort = (update_utf8_for_display(sort) ) WHERE sort LIKE "%\\%" '
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.02)
    mysql = 'UPDATE _pristine_authors SET link = (update_utf8_for_display(link) )  WHERE link LIKE "%\\%" '
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.02)
    mysql = 'DELETE FROM _pristine_authors  WHERE name LIKE "%\\%" '        
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.02)
def __update_global_authors_for_utf8_display(my_db, my_cursor, log):
    sleep(0.02)
    try:
        mysql = 'UPDATE _global_authors SET name = (update_utf8_for_display(name) ) WHERE name LIKE "%\\%" '
        my_cursor.execute("begin")
        my_cursor.execute(mysql) 
        my_cursor.execute("commit")
    except:
        pass
    sleep(0.02)
    mysql = 'UPDATE _global_authors SET sort = (update_utf8_for_display(sort) )  WHERE sort LIKE "%\\%" '
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.02)
    mysql = 'UPDATE _global_authors SET link = (update_utf8_for_display(link) )  WHERE link LIKE "%\\%" '
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.02)
    mysql = 'DELETE FROM _global_authors  WHERE name LIKE "%\\%" '        
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.02)
def Scrub_author_title_reversed(my_db, my_cursor, notifications, log):
    mysql = "UPDATE custom_column_8 SET value = (SELECT replace(value,authname,'') FROM __instr_author_title_by_id \
                    WHERE book = custom_column_8.id ) WHERE value IN(SELECT booktitle FROM __instr_author_title_by_id \
                    WHERE book = custom_column_8.id AND __instr_author_title_by_id.book = __instr_author_title_by_id.id ) \
                    AND custom_column_8.id IN(SELECT book FROM __books_work_status WHERE value = 'dirty' AND book = custom_column_8.id)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE custom_column_8 SET value =(trim(value))"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE custom_column_4 SET value = (replace(value,'|',','))"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "DELETE FROM _author_title_work"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "INSERT OR REPLACE INTO _author_title_work SELECT book,authname,booktitle,name,authname FROM __instr_global_author_title \
                    WHERE book IN(SELECT book FROM __books_work_status WHERE value = 'dirty') \
                    AND book NOT NULL AND authname NOT NULL AND booktitle NOT NULL AND name NOT NULL \
                    AND (instr(name,' ') > 0 ) \
                    AND authname <> name AND authname NOT IN(SELECT name FROM _global_authors WHERE name = authname) "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "INSERT OR REPLACE INTO _author_title_work SELECT book,authname,booktitle,name,authname FROM __instr_pristine_author_title \
                    WHERE book IN(SELECT book FROM __books_work_status WHERE value = 'dirty') \
                    AND book NOT NULL AND authname NOT NULL AND booktitle NOT NULL AND name NOT NULL \
                    AND (instr(name,' ') > 0 ) \
                    AND authname <> name AND authname NOT IN(SELECT name FROM _pristine_authors WHERE name = authname)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    notifications.put((0.05, 'Please Be Patient While All Titles and Authors Are Compared to All Global Authors and Pristine Authors In All Of Their Permutations'))
    mysql = "INSERT OR REPLACE INTO _author_title_work SELECT book,authname,booktitle,name,booktitle||'  '||authname FROM __instr_global_author_sort_title \
                    WHERE book IN(SELECT book FROM __books_work_status WHERE value = 'dirty') \
                    AND book NOT NULL AND authname NOT NULL AND booktitle NOT NULL AND name NOT NULL \
                    AND (instr(name,' ') > 0 ) AND booktitle <> authname \
                    AND authname <> name AND authname NOT IN(SELECT name FROM _global_authors WHERE name = authname)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "INSERT OR REPLACE INTO _author_title_work SELECT book,authname,booktitle,name,booktitle||'  '||authname FROM __instr_pristine_author_sort_title \
                    WHERE book IN(SELECT book FROM __books_work_status WHERE value = 'dirty') \
                    AND book NOT NULL AND authname NOT NULL AND booktitle NOT NULL AND name NOT NULL \
                    AND (instr(name,' ') > 0 ) AND booktitle <> authname \
                    AND authname <> name AND authname NOT IN(SELECT name FROM _pristine_authors WHERE name = authname)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    notifications.put((0.10, 'Please Be Patient While All Titles and Authors Are Compared to All Global Authors and Pristine Authors In All Of Their Permutations'))
    mysql = "UPDATE custom_column_8 SET value = (replace(value,(SELECT sort FROM  __instr_global_author_sort_title  \
                    WHERE __instr_global_author_sort_title.book = custom_column_8.id),'')) \
                    WHERE custom_column_8.id IN(SELECT book FROM __books_work_status WHERE value = 'dirty' AND book = custom_column_8.id) \
                    AND custom_column_8.id IN(SELECT book FROM  __instr_global_author_sort_title  \
                    WHERE __instr_global_author_sort_title.book = custom_column_8.id)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE custom_column_8 SET value = (replace(value,(SELECT sort FROM  __instr_pristine_author_sort_title  \
                    WHERE __instr_pristine_author_sort_title.book = custom_column_8.id),'')) \
                    WHERE custom_column_8.id IN(SELECT book FROM __books_work_status WHERE value = 'dirty' AND book = custom_column_8.id) \
                    AND custom_column_8.id IN (SELECT book FROM  __instr_pristine_author_sort_title  \
                    WHERE __instr_pristine_author_sort_title.book = custom_column_8.id)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    notifications.put((0.15, 'Please Be Patient While All Titles and Authors Are Compared to All Global Authors and Pristine Authors In All Of Their Permutations'))
    mysql = "UPDATE custom_column_8 SET value=(replace(custom_column_8.value,(SELECT first1 FROM __instr_global_author_link_title_part2 \
                WHERE book = custom_column_8.id),'') ) \
                WHERE custom_column_8.id IN(SELECT book FROM __books_work_status WHERE value = 'dirty' AND book = custom_column_8.id) \
                AND custom_column_8.id =(SELECT book FROM __instr_global_author_link_title_part2 \
                WHERE booktitle = custom_column_8.value AND book = custom_column_8.id)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "UPDATE custom_column_8 SET value=(replace(custom_column_8.value,(SELECT first1 FROM __instr_pristine_author_link_title_part2 \
                WHERE book = custom_column_8.id),'') ) \
                WHERE custom_column_8.id IN(SELECT book FROM __books_work_status WHERE value = 'dirty' AND book = custom_column_8.id) \
                AND custom_column_8.id =(SELECT book FROM __instr_pristine_author_link_title_part2 \
                WHERE booktitle = custom_column_8.value AND book = custom_column_8.id)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    notifications.put((0.20, 'Please Be Patient While All Titles and Authors Are Compared to All Global Authors and Pristine Authors In All Of Their Permutations'))
    mysql = "INSERT OR IGNORE INTO custom_column_18 (id,value) VALUES (null, 'auth_ok') "
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    mysql = "SELECT book,old_author,old_title,new_author,new_title FROM _author_title_work WHERE book NOT NULL"
    tmp_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_rows:
       pass
    else:
        log("Swapped Author & Title Will Be Corrected for: Book, Old Author, Old Title, New Author, New Title")
        for row in tmp_rows:
            book,oldauth,oldtitle,newauth,newtitle = row
            newtitle = unicodedata.normalize('NFKD', newtitle).encode('ascii', 'ignore')
            oldauth = unicodedata.normalize('NFKD', oldauth).encode('ascii', 'ignore')
            orig_oldauth = str(oldauth)
            log(" ")
            a2 = str(oldauth) +  "                                                                                  "
            a3 = str(oldtitle) +  "                                                                                   "
            a4 = str(newauth) +  "                                                                                "
            a5 = str(newtitle) +  "                                                                                 "
            a2 = str(a2[0:60])
            a3 = str(a3[0:60])
            a4 = str(a4[0:50])
            a5 = str(a5[0:60])
            log(str(book) + "  " + str(a2) + "  " + str(a3) + "  " + str(a4) + "  " + str(a5))
            book = str(__strip_numerics(book))
            mysql = "UPDATE custom_column_4 SET value = (SELECT new_author FROM _author_title_work  WHERE _author_title_work.book = [BOOK] ) \
                            WHERE custom_column_4.id = [BOOK] "
            mysql = str(mysql.replace("[BOOK]", str(book), 2))
            __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
            sleep(0.2)
            tmp_global_auth_rows = []
            del tmp_global_auth_rows
            tmp_global_link_rows = []
            del tmp_global_link_rows
            mysql = "SELECT sort,link FROM _global_authors WHERE name = ?"
            my_cursor.execute(mysql,([newauth]))
            tmp_global_auth_rows = my_cursor.fetchall()
            if not tmp_global_auth_rows:
                pass
            else:
                for item in tmp_global_auth_rows:
                    ssort,link = item
                    ssort = unicodedata.normalize('NFKD', ssort).encode('ascii', 'ignore')
                    ssort = __change_tuple_to_bytestring(ssort)
                    link = unicodedata.normalize('NFKD', link).encode('ascii', 'ignore')
                    link = __change_tuple_to_bytestring(link)
                    tmp_global_link_rows = link.split("~")
                    if len(tmp_global_link_rows) == 0:
                        tmp_global_link_rows.append("~")
                newtitle = str(newtitle.replace(newauth,mynothing,2))
                newtitle = str(newtitle.replace(ssort,mynothing,2))
                for line in tmp_global_link_rows:
                    s = str(line)
                    s = str(__change_tuple_to_bytestring(s))
                    newtitle = str(newtitle.replace(s,mynothing,2))
            tmp_global_auth_rows = []
            del tmp_global_auth_rows
            tmp_global_link_rows = []
            del tmp_global_link_rows
            mysql = "SELECT sort,link FROM _pristine_authors WHERE name = ?"         
            my_cursor.execute(mysql,([newauth]))
            tmp_global_auth_rows = my_cursor.fetchall()
            if not tmp_global_auth_rows:
                pass
            else:
                for authrow in tmp_global_auth_rows:
                    ssort,link = authrow
                    ssort = unicodedata.normalize('NFKD', ssort).encode('ascii', 'ignore')
                    ssort = __change_tuple_to_bytestring(ssort)
                    link = unicodedata.normalize('NFKD', link).encode('ascii', 'ignore')
                    link = __change_tuple_to_bytestring(link)
                    tmp_global_link_rows = link.split("~")
                    if len(tmp_global_link_rows) == 0:
                        tmp_global_link_rows.append("~")
                newtitle = str(newtitle.replace(newauth,mynothing,2))
                newtitle = str(newtitle.replace(ssort,mynothing,2))
                for linkitem in tmp_global_link_rows:
                    s = str(linkitem)
                    s = str(__change_tuple_to_bytestring(s))
                    newtitle = str(newtitle.replace(s,mynothing,2))
            newtitle = str(__scrub_interim_newtitle(orig_oldauth,newtitle,log))
            mysql = "UPDATE custom_column_8 SET value = ?  WHERE custom_column_8.id = ? "
            __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, newtitle, book)
            sleep(0.2)
            mysql = "UPDATE books_custom_column_18_link SET value =(SELECT id FROM custom_column_18 \
                        WHERE value = 'auth_ok') WHERE books_custom_column_18_link.book = [BOOK] "
            mysql = str(mysql.replace("[BOOK]", str(book), 1))
            __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
            sleep(0.2)
            mysql = "DELETE FROM _author_title_work WHERE book  = [BOOK]"
            mysql = str(mysql.replace("[BOOK]", str(book), 1))
            __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
            sleep(0.2)
            newtitle = ""
            book = ""
    sleep(0.2)
    mysql = "UPDATE custom_column_4 SET value = (replace(value,'|',','))"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(0.2)
    notifications.put((0.50, 'Continuing Next Phase...'))
def __scrub_interim_newtitle(orig_oldauth,newtitle,log):
    newtitle = str(newtitle.strip())
    if newtitle.startswith("-") or newtitle.startswith(",") or newtitle.startswith(":"):
        newtitle = str(newtitle[1: ])
        newtitle = str(newtitle.strip())
    try:
        newtitle = str(newtitle.title())     
    except:
        pass
    tmp_title = str(newtitle)
    try:
        tmp_title = str(tmp_title.lower())
        orig_oldauth = str(orig_oldauth)
        orig_oldauth = str(orig_oldauth.lower())
    except:
        return newtitle
    if orig_oldauth in tmp_title:   
        n = tmp_title.count(orig_oldauth)
        if n > 1:
            backup_title = str(tmp_title)
            tmp_title = str(tmp_title.replace(str(orig_oldauth),"",(n-1)))
            newtitle = str(tmp_title)
            newtitle = str(newtitle.title())
            newtitle = str(newtitle.replace("  ", " ", 4))
            newtitle = str(newtitle.strip())
            if newtitle.startswith("-") or newtitle.startswith(",") or newtitle.startswith(":"):
                newtitle = str(newtitle[1: ])
                newtitle = str(newtitle.strip())
            if newtitle == str(""):
                newtitle = str(backup_title)
    s_split_0 = []
    del s_split_0
    s_split = []
    del s_split
    s = str("")
    s1 = str("")
    s2 = str("")
    s3 = str("")
    s4 = str("")
    s5 = str("")
    s6 = str("")
    sa = str("")
    sb = str("")
    sc = str("")
    sd = str("")
    se = str("")
    sf = str("")
    n = 0
    try:
        my_re_1 =  "^[a-z ]+[ ][0-9]+[ ]"                                   
        my_re_2 =  "[[(]*[a-z ]+[#]*[0-9]+[)\]]*$"                       
        p1 = re.compile(my_re_1, re.IGNORECASE)
        p2 = re.compile(my_re_2, re.IGNORECASE)
        match1 = p1.search(newtitle)
        match2 = p2.search(newtitle)
        if (not match1) or (not match2):
            return str(newtitle)
        else:
            try:
                s1 = match1.group(0)
                s1 = str(s1)
                s1 = str(__change_tuple_to_bytestring(s1)) 
                s2 = match2.group(0)
                s2 = str(s2)
                s2 = str(__change_tuple_to_bytestring(s2)) 
                my_re_3 = "[0-9]+"     
                p3 = re.compile(my_re_3, re.IGNORECASE)
                match3 = p3.search(s1)
                match4 = p3.search(s2)
            except:
                return str(newtitle)   
            if not match3 or not match4:
                return str(newtitle)   
            else:
                s3 = match3.group(0)
                s3 = str(s3)
                s3 = str(__change_tuple_to_bytestring(s3)) 
                s4 = match4.group(0)
                s4 = str(s4)
                s4 = str(__change_tuple_to_bytestring(s4)) 
                if s3 > s4:
                    newtitle = str(newtitle.replace(s4,s3,1))
                newtitle = str(newtitle.replace(s1,"",1))
                newtitle = str(newtitle.strip())
                if newtitle.startswith("-") or newtitle.startswith(",") or newtitle.startswith(":"):
                    newtitle = str(newtitle[1: ])
                newtitle = str(newtitle.replace("  ", " ", 4))
                newtitle = str(newtitle.strip())
                newtitle = str(newtitle.replace("(", "[", 8))
                newtitle = str(newtitle.replace(")", "]", 8))
                if not "[" in newtitle:
                    return str(newtitle)
                s_split_0 = newtitle.split("[")
                n = len(s_split_0)
                if n == 0 or n == 1:
                    return str(newtitle)
                s = str(s_split_0[0])
                s = str(s.replace("  ", " ", 4))
                s = str(s.strip())
                s_split = s.split(" ")  
                n = len(s_split)
                if n == 0 or n == 1:
                    return str(newtitle)
                if n == 2:    
                    sa = str(s_split[0])  
                    sb = str(s_split[1])  
                    sa = str(sa.replace("-", "", 2))
                    sa = str(sa.strip())
                    sb = str(sb.replace("-", "", 2))
                    sb = str(sb.strip())
                    if sa == sb:      
                        newtitle = str(newtitle.replace(sa,"",1))
                    else: 
                        pass
                    newtitle = str(newtitle.replace("  ", " ", 4))
                    newtitle = str(newtitle.strip())
                    return str(newtitle)
                if n >= 4 and n < 6:     
                    sa = str(s_split[0])    
                    sb = str(s_split[1])    
                    sc = str(s_split[2])    
                    sd = str(s_split[3])    
                    sa = str(sa.replace("-", "", 2))
                    sb = str(sb.replace("-", "", 2))
                    sc = str(sc.replace("-", "", 2))
                    sd = str(sd.replace("-", "", 2))
                    sa = str(sa.strip())
                    sb = str(sb.strip())
                    sc = str(sc.strip())
                    sd = str(sd.strip())
                    if sa == sc:      
                        newtitle = str(newtitle.replace(sa,"",1))   
                    if sb == sd:
                        newtitle = str(newtitle.replace(sb,"",1))   
                    newtitle = str(newtitle.replace("  ", " ", 4))
                    newtitle = str(newtitle.strip())
                    return str(newtitle)
                else:
                    if n >= 6:     
                        sa = str(s_split[0])    
                        sb = str(s_split[1])   
                        sc = str(s_split[2])   
                        sd = str(s_split[3])  
                        se = str(s_split[4])   
                        sf = str(s_split[5])    
                        sa = str(sa.replace("-", "", 2))
                        sb = str(sb.replace("-", "", 2))
                        sc = str(sc.replace("-", "", 2))
                        sd = str(sd.replace("-", "", 2))
                        se = str(se.replace("-", "", 2))
                        sf = str(sf.replace("-", "", 2))
                        sa = str(sa.strip())
                        sb = str(sb.strip())
                        sc = str(sc.strip())
                        sd = str(sd.strip())
                        se = str(se.strip())
                        sf = str(sf.strip())
                        if sa == sd:      
                            newtitle = str(newtitle.replace(sa,"",1))   
                        if sb == se:
                            newtitle = str(newtitle.replace(sb,"",1))   
                        if sc == sf:      
                            newtitle = str(newtitle.replace(sc,"",1))   
                        newtitle = str(newtitle.replace("  ", " ", 4))
                        newtitle = str(newtitle.strip())
                        return str(newtitle)
                    else:
                        newtitle = str(newtitle.replace("  ", " ", 4))
                        newtitle = str(newtitle.strip())
                        return str(newtitle)
    except Exception as e:
        pass
    newtitle = str(newtitle.replace("  ", " ", 4))
    newtitle = str(newtitle.strip())
    return str(newtitle)
def Scrub_author_in_author_with_nonauthor(my_db, my_cursor, notifications, log):
    book_list = []
    tmp_list = []
    del tmp_list
    mysql = "SELECT book,booktitle,authname,name FROM __instr_global_author_author \
                    WHERE authname NOT IN(SELECT name from _global_authors WHERE name = authname) \
                    AND authname NOT IN(SELECT name from _pristine_authors WHERE name = authname)"
    my_cursor.execute(mysql)
    tmp_list = my_cursor.fetchall()
    if not tmp_list:
        pass
    else:
        if len(tmp_list) == 0:
            pass
        else:
            log(" ")
            for row in tmp_list:
                book,booktitle,authname,name = row
                new_auth = name
                make_series = False
                for c in authname:
                    if c.isdigit():
                        make_series = True
                        break
                if make_series:
                    authname = "[ " + authname + " ]"
                s = authname
                s = s.replace(name,"",2)
                s = s.replace("-.","",2)
                s = s.replace(" - "," ",4)
                s = s.replace(" -"," ",4)
                s = s.replace("- "," ",4)   
                s = s.strip()
                if len(s) < 5:   
                    s = ""
                s = s.replace(booktitle, "",1)          
                new_title = booktitle + " " + s
                new_title = new_title.replace(name,"",2)
                new_title = new_title.replace("-.","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.replace("[]","",2)
                new_title = new_title.replace("[ ]","",2)
                new_title = new_title.replace("[  ]","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.strip()
                mysql = "UPDATE custom_column_4 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_auth, book)
                mysql = "UPDATE custom_column_8 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_title, book)
                authname = authname + "                                                                                       "
                new_auth = new_auth   + "                                                                                       "
                new_title = new_title     + "                                                                                       "
                authname = authname[0:60]
                new_auth = new_auth[0:30]
                new_title = new_title[0:60]
                log("[Author]: " + authname + "  [was changed to]: " + new_auth + " [for a book with a new title of]: " + new_title)
                book_list.append(str(book))
    tmp_list = []
    del tmp_list
    mysql = "SELECT book,booktitle,authname,name FROM __instr_pristine_author_author \
                    WHERE authname NOT IN(SELECT name from _pristine_authors WHERE name = authname) \
                      AND authname NOT IN(SELECT name from _global_authors WHERE name = authname)"
    my_cursor.execute(mysql)
    tmp_list = my_cursor.fetchall()
    if not tmp_list:
        pass
    else:
        if len(tmp_list) == 0:
            pass
        else:
            log(" ")
            for row in tmp_list:
                book,booktitle,authname,name = row
                new_auth = name
                make_series = False
                for c in authname:
                    if c.isdigit():
                        make_series = True
                        break
                if make_series:
                    authname = "[ " + authname + " ]"
                s = authname
                s = s.replace(name,"",2)
                s = s.strip()
                if len(s) < 5:   
                    s = ""
                new_title = booktitle + " " + s
                new_title = new_title.replace(name,"",2)
                new_title = new_title.replace("-.","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.replace("[]","",2)
                new_title = new_title.replace("[ ]","",2)
                new_title = new_title.replace("[  ]","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.strip()
                mysql = "UPDATE custom_column_4 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_auth, book)
                mysql = "UPDATE custom_column_8 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_title, book)
                authname = authname + "                                                                                       "
                new_auth = new_auth   + "                                                                                       "
                new_title = new_title     + "                                                                                       "
                authname = authname[0:60]
                new_auth = new_auth[0:30]
                new_title = new_title[0:60]
                log("[Author]: " + authname + "  [was changed to]: " + new_auth + " [for a book with a new title of]: " + new_title)
                book_list.append(str(book))
    if len(book_list) == 0:
        return
    tmp_id_rows = []
    del tmp_id_rows
    mysql = "SELECT id FROM custom_column_18 WHERE value = 'auth_ok' "
    tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_id_rows:
        sleep(.1)    
        mysql = "INSERT OR IGNORE INTO custom_column_18 (id,value) VALUES (null, 'auth_ok') "
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(.1)   
        mysql = "SELECT id FROM custom_column_18 WHERE value = 'auth_ok' "
        tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_id_rows:
        log("sql error: custom_column_18 WHERE value = 'auth_ok' ; notify developer")
        return
    tmp_id = tmp_id_rows[0]
    tmp_id = __strip_numerics(tmp_id)
    for row in book_list:
        my_current_book = str(row)
        sleep(.1)    
        mysql = "DELETE FROM books_custom_column_18_link WHERE book = ? AND book > ?"
        __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, my_current_book, "0")
        sleep(.2)   
        mysql = "INSERT OR REPLACE INTO books_custom_column_18_link (id,book,value) VALUES (null,?,?) "
        __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, my_current_book, tmp_id)
        sleep(.02)   
        __clear_work_freeze(my_db, my_cursor, my_current_book, notifications, log)
        sleep(.02)   
def Scrub_author_in_title_but_split_in_two(my_db, my_cursor, notifications, log):
    book_list = []
    mysql = "SELECT name,book,authname,booktitle,(substr(booktitle,0,(instr(booktitle,' ')) )) AS matchleft,\
                substr(booktitle,u_rfind(booktitle,' '),length(booktitle) - u_rfind(booktitle,' ') +1 )  AS matchright \
                FROM __books_work_populate,_global_authors \
                WHERE name <> booktitle \
                AND length(booktitle) > 8 \
                AND instr(booktitle,' ') > 0 \
                AND authname <> name AND matchleft NOT NULL AND matchright NOT NULL AND booktitle NOT NULL \
                AND authname NOT NULL AND book NOT NULL \
                AND authname NOT IN(SELECT name FROM _global_authors WHERE name = authname) \
                AND authname NOT IN(SELECT name FROM _pristine_authors WHERE name = authname) \
                AND _global_authors.name LIKE  '%'||(substr(booktitle,0,(instr(booktitle,' ')) ))||'%' \
                AND _global_authors.name LIKE  '%'||matchright||'%'  "
    my_cursor.execute(mysql)
    tmp_list = my_cursor.fetchall()
    if not tmp_list:
        pass
    else:
        if len(tmp_list) == 0:
            pass
        else:
            log(" ")
            log("Global Author Names Split into Two Parts in Title But Also Not in Work Author (If Any) Will Be Corrected. ")
            log(" ")
            for row in tmp_list:
                name,book,authname,booktitle,matchleft,matchright = row
                s_found = False
                for c in booktitle:
                    if (not c.isalpha() ) and (not c == ' '):   
                        s_found = True      
                        break
                if not s_found:
                    continue
                if len(matchleft) < 2 or str(matchright) == "" or matchright.strip() == "":
                    continue
                if matchleft.startswith("The ") or matchleft.startswith("An ") or matchleft.startswith("A "):
                    continue
                if len(matchleft) + len(matchright) == len(booktitle):
                    continue
                matchright = matchright.strip()
                if not matchright.isalpha():
                    continue
                s_split = name.split(" ")
                n = len(s_split)
                if n < 2:
                    continue
                if n == 2:
                    sl = s_split[1]
                    try:
                        sl = sl.lower()
                        smr = matchright.lower()
                    except:
                        smr = matchright
                    if sl <> smr:
                        continue
                else:
                    sl = s_split[2]
                    try:
                        sl = sl.lower()
                        smr = matchright.lower()
                    except:
                        smr = matchright
                    if sl <> smr:
                        continue
                for item in s_split:
                    s0 = item.strip()
                    booktitle = booktitle.replace(s0,"",4)
                    authname  = authname.replace(s0,"",4)  
                    try:
                        s0 = s0.lower()
                        booktitle = booktitle.replace(s0,"",4)
                        authname  = authname.replace(s0,"",4)  
                    except:
                        log("lower exception")
                        pass
                new_auth = name
                new_title = booktitle + " " + authname
                new_title = new_title.replace(name,"",2)     
                new_title = new_title.replace("-.","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.replace("[]","",2)
                new_title = new_title.replace("[ ]","",2)
                new_title = new_title.replace("[  ]","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.strip()
                mysql = "UPDATE custom_column_4 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_auth, book)
                mysql = "UPDATE custom_column_8 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_title, book)
                log("[Author]: " + authname + "  [was changed to]: " + new_auth + " [for a book with a new title of]: " + new_title)
                log(" ")
                log(" ")
                book_list.append(str(book))
    mysql = "SELECT name,book,authname,booktitle,(substr(booktitle,0,(instr(booktitle,' ')) )) AS matchleft,\
                substr(booktitle,u_rfind(booktitle,' '),length(booktitle) - u_rfind(booktitle,' ') + 1 )  AS matchright \
                FROM __books_work_populate,_pristine_authors \
                WHERE name <> booktitle \
                AND length(booktitle) > 8 \
                AND instr(booktitle,' ') > 0 \
                AND authname <> name AND matchleft NOT NULL AND matchright NOT NULL AND booktitle NOT NULL \
                AND authname NOT NULL AND book NOT NULL \
                AND authname NOT IN(SELECT name FROM _pristine_authors WHERE name = authname) \
                AND authname NOT IN(SELECT name FROM _global_authors WHERE name = authname) \
                AND _pristine_authors.name LIKE  '%'||(substr(booktitle,0,(instr(booktitle,' ')) ))||'%' \
                AND _pristine_authors.name LIKE  '%'||matchright||'%'  "
    my_cursor.execute(mysql)
    tmp_list = my_cursor.fetchall()
    if not tmp_list:
        pass
    else:
        if len(tmp_list) == 0:
            pass
        else:
            log(" ")
            log("Pristine Author Names Split in Two Parts in Title But Not In Work Author Corrected. ")
            log(" ")
            for row in tmp_list:
                name,book,authname,booktitle,matchleft,matchright = row
                s_found = False
                for c in booktitle:
                    if (not c.isalpha() ) and (not c == ' '):   
                        s_found = True      
                        break
                if not s_found:
                    continue
                if len(matchleft) < 2 or str(matchright) == "" or matchright.strip() == "":
                    continue
                if matchleft.startswith("The ") or matchleft.startswith("An ") or matchleft.startswith("A "):
                    continue
                if len(matchleft) + len(matchright) == len(booktitle):
                    continue
                matchright = matchright.strip()
                if not matchright.isalpha():
                    continue
                s_split = name.split(" ")
                n = len(s_split)
                if n < 2:
                    continue
                if n == 2:
                    sl = s_split[1]
                    try:
                        sl = sl.lower()
                        smr = matchright.lower()
                    except:
                        smr = matchright
                    if sl <> smr:
                        continue
                else:
                    sl = s_split[2]
                    try:
                        sl = sl.lower()
                        smr = matchright.lower()
                    except:
                        smr = matchright
                    if sl <> smr:
                        continue
                for item in s_split:
                    s0 = item.strip()
                    booktitle = booktitle.replace(s0,"",4)
                    authname  = authname.replace(s0,"",4)  
                    try:
                        s0 = s0.lower()
                        booktitle = booktitle.replace(s0,"",4)
                        authname  = authname.replace(s0,"",4)  
                    except:
                        log("lower exception")
                        pass
                new_auth = name
                new_title = booktitle + " " + authname
                new_title = new_title.replace(name,"",2)     
                new_title = new_title.replace("-.","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.replace("[]","",2)
                new_title = new_title.replace("[ ]","",2)
                new_title = new_title.replace("[  ]","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.strip()
                mysql = "UPDATE custom_column_4 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_auth, book)
                mysql = "UPDATE custom_column_8 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_title, book)
                log("[Author]: " + authname + "  [was changed to]: " + new_auth + " [for a book with a new title of]: " + new_title)
                log(" ")
                log(" ")
                book_list.append(str(book))
    if len(book_list) == 0:
        return
    tmp_id_rows = []
    del tmp_id_rows
    mysql = "SELECT id FROM custom_column_18 WHERE value = 'auth_ok' "
    tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_id_rows:
        sleep(.1)    
        mysql = "INSERT OR IGNORE INTO custom_column_18 (id,value) VALUES (null, 'auth_ok') "
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(.1)   
        mysql = "SELECT id FROM custom_column_18 WHERE value = 'auth_ok' "
        tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_id_rows:
        log("sql error: custom_column_18 WHERE value = 'auth_ok' ; notify developer")
        return
    tmp_id = tmp_id_rows[0]
    tmp_id = __strip_numerics(tmp_id)
    for row in book_list:
        my_current_book = str(row)
        sleep(.1)    
        mysql = "DELETE FROM books_custom_column_18_link WHERE book = ? AND book > ?"
        __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, my_current_book, "0")
        sleep(.2)   
        mysql = "INSERT OR REPLACE INTO books_custom_column_18_link (id,book,value) VALUES (null,?,?) "
        __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, my_current_book, tmp_id)
        sleep(.02)   
        __clear_work_freeze(my_db, my_cursor, my_current_book, notifications, log)
        sleep(.02)   
def Scrub_author_in_author_but_split_in_two(my_db, my_cursor, notifications, log):
    book_list = []
    mysql = "SELECT name,book,authname,booktitle,(substr(authname,0,(instr(authname,' ')) )) AS matchleft,\
                substr(authname,u_rfind(authname,' '),length(authname) - u_rfind(authname,' ') +1 )  AS matchright \
                FROM __books_work_populate,_global_authors \
                WHERE name <> authname \
                AND length(authname) > 8 \
                AND instr(authname,' ') > 0 \
                AND matchleft NOT NULL AND matchright NOT NULL AND booktitle NOT NULL \
                AND authname NOT NULL AND book NOT NULL \
                AND authname NOT IN(SELECT name FROM _global_authors WHERE name = authname) \
                AND authname NOT IN(SELECT name FROM _pristine_authors WHERE name = authname) \
                AND _global_authors.name LIKE  '%'||(substr(authname,0,(instr(authname,' ')) ))||'%' \
                AND _global_authors.name LIKE  '%'||matchright||'%'  \
                AND trim(matchright) <> trim(matchleft)  \
                AND (u_count(authname,' ') > 1)"
    my_cursor.execute(mysql)
    tmp_list = my_cursor.fetchall()
    if not tmp_list:
        pass
    else:
        if len(tmp_list) == 0:
            pass
        else:
            log(" ")
            log("Global Author Names Split into Two Parts (Along With Bits of Title) in Work Author Will Be Corrected. ")
            log(" ")
            for row in tmp_list:
                name,book,authname,booktitle,matchleft,matchright = row
                s_found = False
                for c in booktitle:
                    if (not c.isalpha() ) and (not c == ' '):   
                        s_found = True      
                        break
                if not s_found:
                    continue
                if "." in matchleft:   
                    continue
                if matchleft.strip() == matchright.strip():    
                    continue
                if len(matchleft) < 2 or str(matchright) == "" or matchright.strip() == "":
                    continue
                if matchleft.startswith("The ") or matchleft.startswith("An ") or matchleft.startswith("A "):
                    continue
                if len(matchleft) + len(matchright) == len(booktitle):
                    continue
                matchright = matchright.strip()
                if not matchright.isalpha():
                    continue
                s_split = name.split(" ")     
                n = len(s_split)
                if n < 2:
                    continue
                if n == 2:
                    sl = s_split[1]
                    try:
                        sl = sl.lower()
                        smr = matchright.lower()
                    except:
                        smr = matchright
                    if sl <> smr:
                        continue
                else:
                    sl = s_split[2]
                    try:
                        sl = sl.lower()
                        smr = matchright.lower()
                    except:
                        smr = matchright
                    if sl <> smr:
                        continue
                new_auth = name
                if any(char.isdigit() for char in authname):        
                    authname = "[ " + authname + " ]"  
                try:
                    authname = authname.title()  
                    booktitle = booktitle.title()      
                except:
                    pass
                new_title = booktitle + " " + authname  
                s2 = authname 
                for item in s_split:
                    s0 = item.strip()
                    new_title = new_title.replace(s0,"",4)
                    s2 = s2.replace(s0,"",4)     
                    try:
                        s0 = s0.lower()
                        new_title = new_title.replace(s0,"",4)
                        s2 = s2.lower()
                        s2 = s2.replace(s0,"",4)     
                    except:
                        pass
                s2 = s2.replace("-.","",4)
                s2 = s2.replace("-","",4)
                s2 = s2.strip()
                try:
                    s2 = s2.title() 
                except:
                    pass
                new_title = new_title.replace(s2,"",1)           
                new_title = new_title.replace(name,"",2)     
                new_title = new_title.replace("-.","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.replace("[]","",2)
                new_title = new_title.replace("[ ]","",2)
                new_title = new_title.replace("[  ]","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.strip()
                mysql = "UPDATE custom_column_4 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_auth, book)
                mysql = "UPDATE custom_column_8 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_title, book)
                log("[Author]: " + authname + "  [was changed to]: " + new_auth + " [for a book with a new title of]: " + new_title)
                log(" ")
                log(" ")
                book_list.append(str(book))
    mysql = "SELECT name,book,authname,booktitle,(substr(authname,0,(instr(authname,' ')) )) AS matchleft,\
                substr(authname,u_rfind(authname,' '),length(authname) - u_rfind(authname,' ') +1 )  AS matchright \
                FROM __books_work_populate,_pristine_authors \
                WHERE name <> authname \
                AND length(authname) > 8 \
                AND instr(authname,' ') > 0 \
                AND matchleft NOT NULL AND matchright NOT NULL AND booktitle NOT NULL \
                AND authname NOT NULL AND book NOT NULL \
                AND authname NOT IN(SELECT name FROM _pristine_authors WHERE name = authname) \
                AND authname NOT IN(SELECT name FROM _global_authors WHERE name = authname) \
                AND _pristine_authors.name LIKE  '%'||(substr(authname,0,(instr(authname,' ')) ))||'%' \
                AND _pristine_authors.name LIKE  '%'||matchright||'%' \
                AND trim(matchright) <> trim(matchleft)  \
                AND (u_count(authname,' ') > 1)"
    my_cursor.execute(mysql)
    tmp_list = my_cursor.fetchall()
    if not tmp_list:
        pass
    else:
        if len(tmp_list) == 0:
            pass
        else:
            log(" ")
            log("Pristine Author Names Split into Two Parts (Along With Bits of Title) in Work Author Will Be Corrected. ")
            log(" ")
            for row in tmp_list:
                name,book,authname,booktitle,matchleft,matchright = row
                s_found = False
                for c in booktitle:
                    if (not c.isalpha() ) and (not c == ' '):   
                        s_found = True      
                        break
                if not s_found:
                    continue
                if "." in matchleft:
                    continue
                if matchleft.strip() == matchright.strip():    
                    continue
                if len(matchleft) < 2 or str(matchright) == "" or matchright.strip() == "":
                    continue
                if matchleft.startswith("The ") or matchleft.startswith("An ") or matchleft.startswith("A "):
                    continue
                if len(matchleft) + len(matchright) == len(booktitle):
                    continue
                matchright = matchright.strip()
                if not matchright.isalpha():
                    continue
                s_split = name.split(" ")     
                n = len(s_split)
                if n < 2:
                    continue
                if n == 2:
                    sl = s_split[1]
                    try:
                        sl = sl.lower()
                        smr = matchright.lower()
                    except:
                        smr = matchright
                    if sl <> smr:
                        continue
                else:
                    sl = s_split[2]
                    try:
                        sl = sl.lower()
                        smr = matchright.lower()
                    except:
                        smr = matchright
                    if sl <> smr:
                        continue
                new_auth = name
                if any(char.isdigit() for char in authname):        
                    authname = "[ " + authname + " ]"  
                try:
                    authname = authname.title()  
                    booktitle = booktitle.title()      
                except:
                    pass
                new_title = booktitle + " " + authname  
                s2 = authname 
                for item in s_split:
                    s0 = item.strip()
                    new_title = new_title.replace(s0,"",4)
                    s2 = s2.replace(s0,"",4)     
                    try:
                        s0 = s0.lower()
                        new_title = new_title.replace(s0,"",4)
                        s2 = s2.lower()
                        s2 = s2.replace(s0,"",4)     
                    except:
                        pass
                s2 = s2.replace("-.","",4)
                s2 = s2.replace("-","",4)
                s2 = s2.strip()
                try:
                    s2 = s2.title() 
                except:
                    pass
                new_title = new_title.replace(s2,"",1)           
                new_title = new_title.replace(name,"",2)     
                new_title = new_title.replace("-.","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.replace("[]","",2)
                new_title = new_title.replace("[ ]","",2)
                new_title = new_title.replace("[  ]","",2)
                new_title = new_title.replace("  ", " ", 8)
                new_title = new_title.strip()
                mysql = "UPDATE custom_column_4 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_auth, book)
                mysql = "UPDATE custom_column_8 SET value = ? WHERE id = ? "
                __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, new_title, book)
                log("[Author]: " + authname + "  [was changed to]: " + new_auth + " [for a book with a new title of]: " + new_title)
                log(" ")
                log(" ")
                book_list.append(str(book))
    if len(book_list) == 0:
        return
    tmp_id_rows = []
    del tmp_id_rows
    mysql = "SELECT id FROM custom_column_18 WHERE value = 'auth_ok' "
    tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_id_rows:
        sleep(.1)    
        mysql = "INSERT OR IGNORE INTO custom_column_18 (id,value) VALUES (null, 'auth_ok') "
        __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
        sleep(.1)   
        mysql = "SELECT id FROM custom_column_18 WHERE value = 'auth_ok' "
        tmp_id_rows = __execute_mysql_fetchall_generic(my_db, my_cursor, log, mysql)
    if not tmp_id_rows:
        log("sql error: custom_column_18 WHERE value = 'auth_ok' ; notify developer")
        return
    tmp_id = tmp_id_rows[0]
    tmp_id = __strip_numerics(tmp_id)
    for row in book_list:
        my_current_book = str(row)
        sleep(.1)    
        mysql = "DELETE FROM books_custom_column_18_link WHERE book = ? AND book > ?"
        __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, my_current_book, "0")
        sleep(.2)   
        mysql = "INSERT OR REPLACE INTO books_custom_column_18_link (id,book,value) VALUES (null,?,?) "
        __execute_mysql_for_custom_column_generic(my_db, my_cursor, log, mysql, my_current_book, tmp_id)
        sleep(.02)   
        __clear_work_freeze(my_db, my_cursor, my_current_book, notifications, log)
        sleep(.02)   
def Rename_authors_per_mapping_rules(my_db, my_cursor, notifications, log):
    mysql = "SELECT Count(*) FROM custom_column_4 WHERE value IN(SELECT name FROM  _author_mapping WHERE name = custom_column_4.value AND newname NOT NULL AND name NOT NULL)"
    my_cursor.execute(mysql)
    count1 = my_cursor.fetchall()
    count1 = __strip_numerics(count1)
    log(" ")
    log(" ")
    log("Number of Author Names Changed Per Author Mapping Rules: " + str(count1))
    log(" ")
    mysql = "UPDATE custom_column_4 SET value =(SELECT newname FROM _author_mapping WHERE name = custom_column_4.value ) \
                    WHERE value IN(SELECT name FROM  _author_mapping WHERE name = custom_column_4.value AND newname NOT NULL AND name NOT NULL)"
    __execute_mysql_with_commit_generic(my_db, my_cursor, log, mysql)
    sleep(.1)   
