def _forceMagazineScan(self, **kwargs):
if 'wait' in kwargs:
magazineScan()
else:
threading.Thread(target=magazineScan, name='API-MAGSCAN', args=[]).start()
def _forceMagazineScan(**kwargs):
if 'wait' in kwargs:
magazineScan()
else:
threading.Thread(target=magazineScan, name='API-MAGSCAN',
args=[]).start()
def _forceMagazineScan(self, **kwargs):
if 'wait' in kwargs:
magazinescan.magazineScan()
else:
threading.Thread(target=magazinescan.magazineScan, args=[]).start()
def dbupgrade(db_current_version):
try:
myDB = database.DBConnection()
db_version = 0
result = myDB.match('PRAGMA user_version')
if result and result[0]:
value = str(result[0])
if value.isdigit():
db_version = int(value)
check = myDB.match('PRAGMA integrity_check')
if check and check[0]:
result = check[0]
if result == 'ok':
logger.debug('Database integrity check: %s' % result)
else:
logger.error('Database integrity check: %s' % result)
# should probably abort now
if db_version < db_current_version:
myDB = database.DBConnection()
if db_version < 1:
if not has_column(myDB, "authors", "AuthorID"):
# it's a new database. Create tables but no need for any upgrading
db_version = db_current_version
lazylibrarian.UPDATE_MSG = 'Creating new database, version %s' % db_version
else:
lazylibrarian.UPDATE_MSG = 'Updating database to version %s, current version is %s' % (
db_current_version, db_version)
logger.info(lazylibrarian.UPDATE_MSG)
myDB.action(
'CREATE TABLE IF NOT EXISTS authors (AuthorID TEXT UNIQUE, AuthorName TEXT UNIQUE, \
AuthorImg TEXT, AuthorLink TEXT, DateAdded TEXT, Status TEXT, LastBook TEXT, LastBookImg TEXT, \
LastLink Text, LastDate TEXT, HaveBooks INTEGER, TotalBooks INTEGER, AuthorBorn TEXT, \
AuthorDeath TEXT, UnignoredBooks INTEGER, Manual TEXT)')
myDB.action('CREATE TABLE IF NOT EXISTS books (AuthorID TEXT, \
BookName TEXT, BookSub TEXT, BookDesc TEXT, BookGenre TEXT, BookIsbn TEXT, BookPub TEXT, \
BookRate INTEGER, BookImg TEXT, BookPages INTEGER, BookLink TEXT, BookID TEXT UNIQUE, BookFile TEXT, \
BookDate TEXT, BookLang TEXT, BookAdded TEXT, Status TEXT, WorkPage TEXT, Manual TEXT)'
)
myDB.action(
'CREATE TABLE IF NOT EXISTS wanted (BookID TEXT, NZBurl TEXT, NZBtitle TEXT, NZBdate TEXT, \
NZBprov TEXT, Status TEXT, NZBsize TEXT, AuxInfo TEXT, NZBmode TEXT, Source TEXT, DownloadID TEXT)'
)
myDB.action(
'CREATE TABLE IF NOT EXISTS pastissues AS SELECT * FROM wanted WHERE 0'
) # same columns
myDB.action(
'CREATE TABLE IF NOT EXISTS magazines (Title TEXT UNIQUE, Regex TEXT, Status TEXT, \
MagazineAdded TEXT, LastAcquired TEXT, IssueDate TEXT, IssueStatus TEXT, Reject TEXT, \
LatestCover TEXT)')
myDB.action(
'CREATE TABLE IF NOT EXISTS languages (isbn TEXT, lang TEXT)'
)
myDB.action(
'CREATE TABLE IF NOT EXISTS issues (Title TEXT, IssueID TEXT UNIQUE, IssueAcquired TEXT, \
IssueDate TEXT, IssueFile TEXT)')
myDB.action(
'CREATE TABLE IF NOT EXISTS stats (authorname text, GR_book_hits int, GR_lang_hits int, \
LT_lang_hits int, GB_lang_change, cache_hits int, bad_lang int, bad_char int, uncached int, \
duplicates int)')
myDB.action(
'CREATE TABLE IF NOT EXISTS series (SeriesID INTEGER PRIMARY KEY, SeriesName TEXT, \
Status TEXT)')
myDB.action(
'CREATE TABLE IF NOT EXISTS member (SeriesID INTEGER, BookID TEXT, SeriesNum TEXT)'
)
myDB.action(
'CREATE TABLE IF NOT EXISTS seriesauthors (SeriesID INTEGER, AuthorID TEXT)'
)
# These are the incremental changes before database versioning was introduced.
# Old database tables might already have these incorporated depending on version, so we need to check...
if db_version < 1:
if not has_column(myDB, "books", "BookSub"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold book subtitles.'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE books ADD COLUMN BookSub TEXT')
if not has_column(myDB, "books", "BookSub"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold book publisher'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE books ADD COLUMN BookPub TEXT')
if not has_column(myDB, "books", "BookGenre"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold bookgenre'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE books ADD COLUMN BookGenre TEXT')
if not has_column(myDB, "books", "BookFile"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold book filename'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE books ADD COLUMN BookFile TEXT')
if not has_column(myDB, "wanted", "AuxInfo"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold AuxInfo'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE wanted ADD COLUMN AuxInfo TEXT')
if not has_column(myDB, "wanted", "NZBsize"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold NZBsize'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE wanted ADD COLUMN NZBsize TEXT')
if not has_column(myDB, "wanted", "NZBmode"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold NZBmode'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE wanted ADD COLUMN NZBmode TEXT')
if not has_column(myDB, "authors", "UnignoredBooks"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold UnignoredBooks'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action(
'ALTER TABLE authors ADD COLUMN UnignoredBooks INTEGER'
)
if not has_column(myDB, "magazines", "IssueStatus"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold IssueStatus'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action(
'ALTER TABLE magazines ADD COLUMN IssueStatus TEXT')
addedWorkPage = False
if not has_column(myDB, "books", "WorkPage"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold WorkPage'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE books ADD COLUMN WorkPage TEXT')
addedWorkPage = True
addedSeries = False
if not has_column(myDB, "series",
"SeriesID") and not has_column(
myDB, "books", "Series"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold Series'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE books ADD COLUMN Series TEXT')
addedSeries = True
# SeriesOrder shouldn't be an integer, some later written books
# and novellas logically go inbetween books of the main series,
# and their SeriesOrder is not an integer, eg 1.5
# so we need to update SeriesOrder to store as text.
# Because sqlite can't drop columns we create a new column SeriesNum,
# inherit the old column values, and use SeriesNum instead
if not has_column(myDB, "books", "SeriesNum") and has_column(
myDB, "books", "SeriesOrder"):
# no SeriesNum column, so create one
lazylibrarian.UPDATE_MSG = 'Updating books to hold SeriesNum'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE books ADD COLUMN SeriesNum TEXT')
myDB.action('UPDATE books SET SeriesNum = SeriesOrder')
myDB.action('UPDATE books SET SeriesOrder = Null')
addedIssues = False
if not has_column(myDB, "issues", "Title"):
lazylibrarian.UPDATE_MSG = 'Updating database to hold Issues table'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action(
'CREATE TABLE issues (Title TEXT, IssueID TEXT, IssueAcquired TEXT, IssueDate TEXT, IssueFile TEXT)'
)
addedIssues = True
if not has_column(myDB, "issues", "IssueID"):
lazylibrarian.UPDATE_MSG = 'Updating Issues table to hold IssueID'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE issues ADD COLUMN IssueID TEXT')
addedIssues = True
myDB.action('DROP TABLE if exists capabilities')
if addedIssues:
try:
magazinescan.magazineScan()
except Exception as e:
logger.debug("Failed to scan magazines, %s" % str(e))
if addedWorkPage:
try:
lazylibrarian.UPDATE_MSG = 'Adding WorkPage to existing books'
logger.debug(lazylibrarian.UPDATE_MSG)
threading.Thread(target=bookwork.setWorkPages,
name="ADDWORKPAGE",
args=[]).start()
except Exception as e:
logger.debug("Failed to update WorkPages, %s" % str(e))
if addedSeries:
try:
books = myDB.select(
'SELECT BookID, BookName FROM books')
if books:
lazylibrarian.UPDATE_MSG = 'Adding series to existing books'
logger.debug(lazylibrarian.UPDATE_MSG)
tot = len(books)
cnt = 0
for book in books:
cnt += 1
lazylibrarian.UPDATE_MSG = 'Adding series to existing books: %s of %s' % (
cnt, tot)
series, seriesNum = bookSeries(
book["BookName"])
if series:
controlValueDict = {
"BookID": book["BookID"]
}
newValueDict = {
"series": series,
"seriesNum": seriesNum
}
myDB.upsert("books", newValueDict,
controlValueDict)
except Exception as e:
logger.error('Error: ' + str(e))
if db_version < 2:
try:
results = myDB.select(
'SELECT BookID,NZBsize FROM wanted WHERE NZBsize LIKE "% MB"'
)
if results:
lazylibrarian.UPDATE_MSG = 'Removing units from wanted table'
logger.debug(lazylibrarian.UPDATE_MSG)
tot = len(results)
cnt = 0
for units in results:
cnt += 1
lazylibrarian.UPDATE_MSG = 'Removing units from wanted table: %s of %s' % (
cnt, tot)
nzbsize = units["NZBsize"]
nzbsize = nzbsize.split(' ')[0]
myDB.action(
'UPDATE wanted SET NZBsize = "%s" WHERE BookID = "%s"'
% (nzbsize, units["BookID"]))
except Exception as e:
logger.error('Error: ' + str(e))
if db_version < 3:
if has_column(myDB, "books", "SeriesOrder"):
lazylibrarian.UPDATE_MSG = 'Removing SeriesOrder from books table'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action(
'CREATE TABLE IF NOT EXISTS temp_books (AuthorID TEXT, AuthorName TEXT, AuthorLink TEXT, \
BookName TEXT, BookSub TEXT, BookDesc TEXT, BookGenre TEXT, BookIsbn TEXT, BookPub TEXT, \
BookRate INTEGER, BookImg TEXT, BookPages INTEGER, BookLink TEXT, BookID TEXT UNIQUE, \
BookFile TEXT, BookDate TEXT, BookLang TEXT, BookAdded TEXT, Status TEXT, Series TEXT, \
SeriesNum TEXT, WorkPage TEXT)')
myDB.action(
'INSERT INTO temp_books SELECT AuthorID,AuthorName,AuthorLink,BookName,BookSub, \
BookDesc,BookGenre,BookIsbn,BookPub,BookRate,BookImg,BookPages,BookLink,BookID, \
BookFile,BookDate,BookLang,BookAdded,Status,Series,SeriesNum,WorkPage FROM books'
)
myDB.action('DROP TABLE books')
myDB.action('ALTER TABLE temp_books RENAME TO books')
if not has_column(myDB, "pastissues", "BookID"):
lazylibrarian.UPDATE_MSG = 'Moving magazine past issues into new table'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action(
'CREATE TABLE pastissues AS SELECT * FROM wanted WHERE Status="Skipped" AND length(AuxInfo) > 0'
)
myDB.action(
'DELETE FROM wanted WHERE Status="Skipped" AND length(AuxInfo) > 0'
)
if db_version < 4:
if not has_column(myDB, "stats", "duplicates"):
lazylibrarian.UPDATE_MSG = 'Updating stats table to hold duplicates'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE stats ADD COLUMN duplicates INT')
if db_version < 5:
issues = myDB.select(
'SELECT IssueID,IssueDate from issues WHERE length(IssueDate) < 4 and length(IssueDate) > 0'
)
if issues:
lazylibrarian.UPDATE_MSG = 'Updating issues table to hold 4 digit issue numbers'
logger.debug(lazylibrarian.UPDATE_MSG)
tot = len(issues)
cnt = 0
for issue in issues:
cnt += 1
lazylibrarian.UPDATE_MSG = 'Updating issues table 4 digits: %s of %s' % (
cnt, tot)
issueid = issue['IssueID']
issuedate = str(issue['IssueDate'])
issuedate = issuedate.zfill(4)
myDB.action(
'UPDATE issues SET IssueDate="%s" WHERE IssueID="%s"'
% (issuedate, issueid))
mags = myDB.select(
'SELECT Title,IssueDate from magazines WHERE length(IssueDate) < 4 and length(IssueDate) > 0'
)
if mags:
lazylibrarian.UPDATE_MSG = 'Updating magazines table to 4 digits'
logger.debug(lazylibrarian.UPDATE_MSG)
tot = len(mags)
cnt = 0
for mag in mags:
cnt += 1
lazylibrarian.UPDATE_MSG = 'Updating magazines table to 4 digits: %s of %s' % (
cnt, tot)
title = mag['Title']
issuedate = str(mag['IssueDate'])
issuedate = issuedate.zfill(4)
myDB.action(
'UPDATE magazines SET IssueDate="%s" WHERE Title="%s"'
% (issuedate, title))
if db_version < 6:
if not has_column(myDB, "books", "Manual"):
lazylibrarian.UPDATE_MSG = 'Updating books table to hold Manual setting'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE books ADD COLUMN Manual TEXT')
if db_version < 7:
if not has_column(myDB, "wanted", "Source"):
lazylibrarian.UPDATE_MSG = 'Updating wanted table to hold Source and DownloadID'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE wanted ADD COLUMN Source TEXT')
myDB.action(
'ALTER TABLE wanted ADD COLUMN DownloadID TEXT')
if db_version < 8:
src = os.path.join(lazylibrarian.PROG_DIR,
'data/images/cache/')
dst = lazylibrarian.CACHEDIR
images = myDB.select(
'SELECT AuthorID, AuthorImg FROM authors WHERE AuthorImg LIKE "images/cache/%"'
)
if images:
logger.debug('Moving author images to new location')
tot = len(images)
cnt = 0
for image in images:
cnt += 1
lazylibrarian.UPDATE_MSG = "Moving author images to new location: %s of %s" % (
cnt, tot)
img = image['AuthorImg']
img = img[7:]
myDB.action(
'UPDATE authors SET AuthorImg="%s" WHERE AuthorID="%s"'
% (img, image['AuthorID']))
img = img[6:]
srcfile = os.path.join(src, img)
if os.path.isfile(srcfile):
try:
shutil.move(os.path.join(src, img),
os.path.join(dst, img))
except Exception as e:
logger.warn("dbupgrade: %s" % str(e))
logger.debug("Author Image cache updated")
images = myDB.select(
'SELECT BookID, BookImg FROM books WHERE BookImg LIKE "images/cache/%"'
)
if images:
logger.debug('Moving book images to new location')
tot = len(images)
cnt = 0
for image in images:
cnt += 1
lazylibrarian.UPDATE_MSG = "Moving book images to new location: %s of %s" % (
cnt, tot)
img = image['BookImg']
img = img[7:]
myDB.action(
'UPDATE books SET BookImg="%s" WHERE BookID="%s"' %
(img, image['BookID']))
img = img[6:]
srcfile = os.path.join(src, img)
if os.path.isfile(srcfile):
try:
shutil.move(srcfile, os.path.join(dst, img))
except Exception as e:
logger.warn("dbupgrade: %s" % str(e))
logger.debug("Book Image cache updated")
if db_version < 9:
if not has_column(myDB, "magazines", "Reject"):
# remove frequency column, rename regex to reject, add new regex column for searches
lazylibrarian.UPDATE_MSG = 'Updating magazines table'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action(
'CREATE TABLE IF NOT EXISTS temp_table (Title TEXT, Regex TEXT, Status TEXT, \
MagazineAdded TEXT, LastAcquired TEXT, IssueDate TEXT, IssueStatus TEXT, Reject TEXT)'
)
myDB.action(
'INSERT INTO temp_table SELECT Title, Regex, Status, MagazineAdded, LastAcquired, \
IssueDate, IssueStatus, Regex FROM magazines')
myDB.action('DROP TABLE magazines')
myDB.action('ALTER TABLE temp_table RENAME TO magazines')
myDB.action('UPDATE magazines SET Regex = Null')
if db_version < 10:
# make sure columns in pastissues match those in wanted table
# needed when upgrading from old 3rd party packages (eg freenas)
myDB.action('DROP TABLE pastissues')
myDB.action(
'CREATE TABLE pastissues AS SELECT * FROM wanted WHERE 0'
) # same columns, but empty table
if db_version < 11:
# keep last book image
if not has_column(myDB, "authors", "LastBookImg"):
lazylibrarian.UPDATE_MSG = 'Updating author table to hold last book image'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action(
'ALTER TABLE authors ADD COLUMN LastBookImg TEXT')
books = myDB.select(
'SELECT AuthorID, AuthorName, LastBook from authors')
if books:
for book in books:
lazylibrarian.UPDATE_MSG = 'Updating last book image for %s' % book[
'AuthorName']
if book['LastBook']:
match = myDB.match(
'SELECT BookImg from books WHERE AuthorID="%s" AND BookName="%s"'
% (book['AuthorID'], book['LastBook']))
if match:
myDB.action(
'UPDATE authors SET LastBookImg="%s" WHERE AuthorID=%s'
% (match['BookImg'], book['AuthorID']))
if db_version < 12:
# keep last magazine issue image
if not has_column(myDB, "Magazines", "LatestCover"):
lazylibrarian.UPDATE_MSG = 'Updating magazine table to hold last issue image'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action(
'ALTER TABLE magazines ADD COLUMN LatestCover TEXT')
mags = myDB.select(
'SELECT Title, LastAcquired from magazines')
if mags:
for mag in mags:
lazylibrarian.UPDATE_MSG = 'Updating last issue image for %s' % mag[
'Title']
match = myDB.match(
'SELECT IssueFile from issues WHERE IssueAcquired="%s" AND Title="%s"'
% (mag['LastAcquired'], mag['Title']))
if match:
coverfile = os.path.splitext(
match['IssueFile'])[0] + '.jpg'
if os.path.exists(coverfile):
myDB.action(
'UPDATE magazines SET LatestCover="%s" WHERE Title="%s"'
% (coverfile, mag['Title']))
if db_version < 13:
if not has_column(myDB, "authors", "Manual"):
lazylibrarian.UPDATE_MSG = 'Updating authors table to hold Manual setting'
logger.debug(lazylibrarian.UPDATE_MSG)
myDB.action('ALTER TABLE authors ADD COLUMN Manual TEXT')
if db_version < 14:
src = lazylibrarian.CACHEDIR
try:
os.mkdir(os.path.join(src, 'author'))
except OSError as e:
if e.errno is not 17: # already exists is ok
logger.debug('mkdir author cache reports: %s' % str(e))
query = 'SELECT AuthorName, AuthorID, AuthorImg FROM authors '
query += 'WHERE AuthorImg LIKE "cache/%" '
query += 'AND AuthorImg NOT LIKE "cache/author/%"'
images = myDB.select(query)
if images:
tot = len(images)
logger.debug('Moving %s author images to new location' %
tot)
cnt = 0
for image in images:
cnt += 1
lazylibrarian.UPDATE_MSG = "Moving author images to new location: %s of %s" % (
cnt, tot)
try:
img = image['AuthorImg']
img = img.rsplit('/', 1)[1]
srcfile = os.path.join(src, img)
if os.path.isfile(srcfile):
try:
shutil.move(
srcfile,
os.path.join(src, "author", img))
myDB.action(
'UPDATE authors SET AuthorImg="cache/author/%s" WHERE AuthorID="%s"'
% (img, image['AuthorID']))
except Exception as e:
logger.warn("dbupgrade: %s" % str(e))
except Exception as e:
logger.warn(
'Failed to update author image for %s: %s' %
(image['AuthorName'], str(e)))
logger.debug("Author Image cache updated")
try:
os.mkdir(os.path.join(src, 'book'))
except OSError as e:
if e.errno is not 17: # already exists is ok
logger.debug('mkdir book cache reports: %s' % str(e))
query = 'SELECT BookName, BookID, BookImg FROM books '
query += 'WHERE BookImg LIKE "cache/%" '
query += 'AND BookImg NOT LIKE "cache/book/%"'
images = myDB.select(query)
if images:
tot = len(images)
logger.debug('Moving %s book images to new location' % tot)
cnt = 0
for image in images:
cnt += 1
lazylibrarian.UPDATE_MSG = "Moving book images to new location: %s of %s" % (
cnt, tot)
try:
img = image['BookImg']
img = img.rsplit('/', 1)[1]
srcfile = os.path.join(src, img)
if os.path.isfile(srcfile):
try:
shutil.move(srcfile,
os.path.join(src, "book", img))
myDB.action(
'UPDATE books SET BookImg="cache/book/%s" WHERE BookID="%s"'
% (img, image['BookID']))
except Exception as e:
logger.warn("dbupgrade: %s" % str(e))
except Exception as e:
logger.warn(
'Failed to update book image for %s: %s' %
(image['BookName'], str(e)))
logger.debug("Book Image cache updated")
# at this point there should be no more .jpg files in the root of the cachedir
# any that are still there are for books/authors deleted from database
# or magazine latest issue cover files that get copied as required
for image in os.listdir(src):
if image.endswith('.jpg'):
os.remove(os.path.join(src, image))
if db_version < 15:
myDB.action(
'CREATE TABLE IF NOT EXISTS series (SeriesID INTEGER PRIMARY KEY, SeriesName TEXT, \
AuthorID TEXT, Status TEXT)')
myDB.action(
'CREATE TABLE IF NOT EXISTS member (SeriesID INTEGER, BookID TEXT, SeriesNum TEXT)'
)
if has_column(myDB, "books", "SeriesNum"):
lazylibrarian.UPDATE_MSG = 'Populating series and member tables'
books = myDB.select(
'SELECT BookID, Series, SeriesNum from books')
if books:
tot = len(books)
logger.debug("Updating book series for %s book%s" %
(tot, plural(tot)))
cnt = 0
for book in books:
cnt += 1
lazylibrarian.UPDATE_MSG = "Updating book series: %s of %s" % (
cnt, tot)
seriesdict = getWorkSeries(book['BookID'])
if not seriesdict: # no workpage series, use the current values if present
if book['Series'] and book['SeriesNum']:
seriesdict = {
cleanName(unaccented(book['Series'])):
book['SeriesNum']
}
setSeries(seriesdict,
book['BookID'],
seriesauthors=False)
# deleteEmptySeries # shouldn't be any on first run?
lazylibrarian.UPDATE_MSG = "Book series update complete"
logger.debug(lazylibrarian.UPDATE_MSG)
lazylibrarian.UPDATE_MSG = 'Removing seriesnum from books table'
myDB.action(
'CREATE TABLE IF NOT EXISTS temp_table (AuthorID TEXT, AuthorName TEXT, AuthorLink TEXT, \
BookName TEXT, BookSub TEXT, BookDesc TEXT, BookGenre TEXT, BookIsbn TEXT, BookPub TEXT, \
BookRate INTEGER, BookImg TEXT, BookPages INTEGER, BookLink TEXT, BookID TEXT UNIQUE, \
BookFile TEXT, BookDate TEXT, BookLang TEXT, BookAdded TEXT, Status TEXT, Series TEXT, \
WorkPage TEXT, Manual TEXT)')
myDB.action(
'INSERT INTO temp_table SELECT AuthorID, AuthorName, AuthorLink, BookName, BookSub, \
BookDesc, BookGenre, BookIsbn, BookPub, BookRate, BookImg, BookPages, BookLink, BookID, \
BookFile, BookDate, BookLang, BookAdded, Status, Series, WorkPage, Manual from books'
)
myDB.action('DROP TABLE books')
myDB.action('ALTER TABLE temp_table RENAME TO books')
lazylibrarian.UPDATE_MSG = 'Reorganisation of books table complete'
if db_version < 16:
if has_column(myDB, "books", "AuthorLink"):
lazylibrarian.UPDATE_MSG = 'Removing series, authorlink and authorname from books table'
myDB.action(
'CREATE TABLE IF NOT EXISTS temp_table (AuthorID TEXT, \
BookName TEXT, BookSub TEXT, BookDesc TEXT, BookGenre TEXT, BookIsbn TEXT, BookPub TEXT, \
BookRate INTEGER, BookImg TEXT, BookPages INTEGER, BookLink TEXT, BookID TEXT UNIQUE, \
BookFile TEXT, BookDate TEXT, BookLang TEXT, BookAdded TEXT, Status TEXT, WorkPage TEXT, \
Manual TEXT)')
myDB.action(
'INSERT INTO temp_table SELECT AuthorID, BookName, BookSub, \
BookDesc, BookGenre, BookIsbn, BookPub, BookRate, BookImg, BookPages, BookLink, BookID, \
BookFile, BookDate, BookLang, BookAdded, Status, WorkPage, Manual from books'
)
myDB.action('DROP TABLE books')
myDB.action('ALTER TABLE temp_table RENAME TO books')
lazylibrarian.UPDATE_MSG = 'Reorganisation of books table complete'
if db_version < 17:
if has_column(myDB, "series", "AuthorID"):
lazylibrarian.UPDATE_MSG = 'Creating seriesauthors table'
# In this version of the database there is only one author per series so use that as starting point
myDB.action(
'CREATE TABLE seriesauthors (SeriesID INTEGER, AuthorID TEXT, UNIQUE (SeriesID,AuthorID))'
)
series = myDB.select(
'SELECT SeriesID,AuthorID from series')
cnt = 0
tot = len(series)
for item in series:
cnt += 1
lazylibrarian.UPDATE_MSG = "Updating seriesauthors: %s of %s" % (
cnt, tot)
myDB.action(
'insert into seriesauthors (SeriesID, AuthorID) values (%s, %s)'
% (item['SeriesID'], item['AuthorID']),
suppress='UNIQUE')
myDB.action('DROP TABLE temp_table')
myDB.action(
'CREATE TABLE temp_table (SeriesID INTEGER PRIMARY KEY, SeriesName TEXT, \
Status TEXT)')
myDB.action(
'INSERT INTO temp_table SELECT SeriesID, SeriesName, Status FROM series'
)
myDB.action('DROP TABLE series')
myDB.action('ALTER TABLE temp_table RENAME TO series')
lazylibrarian.UPDATE_MSG = 'Reorganisation of series table complete'
if db_version < 18:
data = myDB.match('pragma index_list(seriesauthors)')
if not data:
lazylibrarian.UPDATE_MSG = 'Adding unique constraint to seriesauthors table'
myDB.action('DROP TABLE IF EXISTS temp_table')
myDB.action(
'ALTER TABLE seriesauthors RENAME to temp_table')
myDB.action(
'CREATE TABLE seriesauthors (SeriesID INTEGER, AuthorID TEXT, UNIQUE (SeriesID,AuthorID))'
)
series = myDB.select(
'SELECT SeriesID,AuthorID from temp_table')
cnt = 0
tot = len(series)
for item in series:
cnt += 1
lazylibrarian.UPDATE_MSG = "Updating seriesauthors: %s of %s" % (
cnt, tot)
myDB.action(
'insert into seriesauthors (SeriesID, AuthorID) values (%s, %s)'
% (item['SeriesID'], item['AuthorID']),
suppress='UNIQUE')
myDB.action('DROP TABLE temp_table')
lazylibrarian.UPDATE_MSG = 'Reorganisation of seriesauthors complete'
# Now do any non-version-specific tidying
try:
authors = myDB.select(
'SELECT AuthorID FROM authors WHERE AuthorName IS NULL')
if authors:
logger.debug(
'Removing %s un-named author%s from database' %
(len(authors), plural(len(authors))))
for author in authors:
authorid = author["AuthorID"]
myDB.action('DELETE from authors WHERE AuthorID="%s"' %
authorid)
myDB.action('DELETE from books WHERE AuthorID="%s"' %
authorid)
except Exception as e:
logger.error('Error: ' + str(e))
myDB.action('PRAGMA user_version = %s' % db_current_version)
lazylibrarian.UPDATE_MSG = 'Cleaning Database after upgrade'
myDB.action('vacuum')
lazylibrarian.UPDATE_MSG = 'Database updated to version %s' % db_current_version
logger.info(lazylibrarian.UPDATE_MSG)
restartJobs(start='Start')
lazylibrarian.UPDATE_MSG = ''
except Exception:
logger.error('Unhandled exception in database update: %s' %
traceback.format_exc())
lazylibrarian.UPDATE_MSG = ''
def dbcheck():
conn = sqlite3.connect(DBFILE)
c = conn.cursor()
c.execute(
"CREATE TABLE IF NOT EXISTS authors (AuthorID TEXT, AuthorName TEXT UNIQUE, AuthorImg TEXT, AuthorLink TEXT, DateAdded TEXT, Status TEXT, LastBook TEXT, LastLink Text, LastDate TEXT, HaveBooks INTEGER, TotalBooks INTEGER, AuthorBorn TEXT, AuthorDeath TEXT, UnignoredBooks INTEGER)"
)
c.execute(
"CREATE TABLE IF NOT EXISTS books (AuthorID TEXT, AuthorName TEXT, AuthorLink TEXT, BookName TEXT, BookSub TEXT, BookDesc TEXT, BookGenre TEXT, BookIsbn TEXT, BookPub TEXT, BookRate INTEGER, BookImg TEXT, BookPages INTEGER, BookLink TEXT, BookID TEXT UNIQUE, BookDate TEXT, BookLang TEXT, BookAdded TEXT, Status TEXT, Series TEXT, SeriesOrder INTEGER)"
)
c.execute(
"CREATE TABLE IF NOT EXISTS wanted (BookID TEXT, NZBurl TEXT, NZBtitle TEXT, NZBdate TEXT, NZBprov TEXT, Status TEXT, NZBsize TEXT, AuxInfo TEXT)"
)
c.execute(
"CREATE TABLE IF NOT EXISTS magazines (Title TEXT, Frequency TEXT, Regex TEXT, Status TEXT, MagazineAdded TEXT, LastAcquired TEXT, IssueDate TEXT, IssueStatus TEXT)"
)
c.execute("CREATE TABLE IF NOT EXISTS languages ( isbn TEXT, lang TEXT )")
c.execute(
"CREATE TABLE IF NOT EXISTS stats ( authorname text, GR_book_hits int, GR_lang_hits int, LT_lang_hits int, GB_lang_change, cache_hits int, bad_lang int, bad_char int, uncached int )"
)
try:
logger.info("Checking database")
c.execute("SELECT BookSub from books")
except sqlite3.OperationalError:
logger.info("Updating database to hold book subtitles.")
c.execute("ALTER TABLE books ADD COLUMN BookSub TEXT")
try:
c.execute("SELECT BookPub from books")
except sqlite3.OperationalError:
logger.info("Updating database to hold book publisher")
c.execute("ALTER TABLE books ADD COLUMN BookPub TEXT")
try:
c.execute("SELECT BookGenre from books")
except sqlite3.OperationalError:
logger.info("Updating database to hold bookgenre")
c.execute("ALTER TABLE books ADD COLUMN BookGenre TEXT")
try:
c.execute("SELECT BookFile from books")
except sqlite3.OperationalError:
logger.info("Updating database to hold book filename")
c.execute("ALTER TABLE books ADD COLUMN BookFile TEXT")
try:
c.execute("SELECT AuxInfo from wanted")
except sqlite3.OperationalError:
logger.info("Updating database to hold AuxInfo")
c.execute("ALTER TABLE wanted ADD COLUMN AuxInfo TEXT")
try:
c.execute("SELECT NZBsize from wanted")
except sqlite3.OperationalError:
logger.info("Updating database to hold NZBside")
c.execute("ALTER TABLE wanted ADD COLUMN NZBsize TEXT")
try:
c.execute("SELECT NZBmode from wanted")
except sqlite3.OperationalError:
logger.info("Updating database to hold NZBmode")
c.execute("ALTER TABLE wanted ADD COLUMN NZBmode TEXT")
try:
c.execute("SELECT UnignoredBooks from authors")
except sqlite3.OperationalError:
logger.info("Updating database to hold UnignoredBooks")
c.execute("ALTER TABLE authors ADD COLUMN UnignoredBooks INTEGER")
try:
c.execute("SELECT IssueStatus from magazines")
except sqlite3.OperationalError:
logger.info("Updating database to hold IssueStatus")
c.execute("ALTER TABLE magazines ADD COLUMN IssueStatus TEXT")
addedSeries = False
try:
c.execute("SELECT Series from books")
except sqlite3.OperationalError:
logger.info("Updating database to hold Series")
c.execute("ALTER TABLE books ADD COLUMN Series TEXT")
addedSeries = True
try:
c.execute("SELECT SeriesOrder from books")
except sqlite3.OperationalError:
logger.info("Updating database to hold SeriesOrder")
c.execute("ALTER TABLE books ADD COLUMN SeriesOrder INTEGER")
addedIssues = False
try:
c.execute("SELECT Title from issues")
except sqlite3.OperationalError:
logger.info("Updating database to hold Issues")
c.execute("CREATE TABLE issues ( Title TEXT, IssueAcquired TEXT, IssueDate TEXT, IssueFile TEXT )")
addedIssues = True
conn.commit()
c.close()
if addedIssues:
try:
magazinescan.magazineScan(thread="MAIN")
except:
logger.debug("Failed to scan magazines")
if addedSeries:
try:
myDB = database.DBConnection()
books = myDB.select("SELECT BookID, BookName FROM books")
if books:
logger.info("Adding series to existing books")
for book in books:
result = re.search(r"\(([\S\s]+)\, #(\d+)|\(([\S\s]+) #(\d+)", book["BookName"])
if result:
if result.group(1) == None:
series = result.group(3)
seriesOrder = result.group(4)
else:
series = result.group(1)
seriesOrder = result.group(2)
controlValueDict = {"BookID": book["BookID"]}
newValueDict = {"series": series, "seriesOrder": seriesOrder}
myDB.upsert("books", newValueDict, controlValueDict)
except Exception, z:
logger.info("Error: " + str(z))
def _forceMagazineScan(self, **kwargs):
if 'wait' in kwargs:
magazinescan.magazineScan()
else:
threading.Thread(target=magazinescan.magazineScan, args=[]).start()
