CVE-2022-0939: Server-Side Request Forgery (SSRF) (CWE-918)
value: Server-Side Request Forgery (SSRF) in GitHub repository janeczku/calibre-web prior to 0.6.18.
CWE description: The web server receives a URL or similar request from an upstream component and retrieves the contents of this URL, but it does not sufficiently ensure that the request is being sent to the expected destination.
CVSS v3 base score: 9.9
Published: 2022-04-04T10:15Z
Repository: https://github.com/janeczku/calibre-web
Language: Python
Files changed: cps.py, cps/__init__.py, cps/admin.py, cps/db.py, cps/editbooks.py, cps/epub.py, cps/helper.py, cps/opds.py, cps/pagination.py, cps/remotelogin.py, cps/templates/book_edit.html, cps/templates/book_table.html, cps/templates/detail.html, cps/templates/layout.html, cps/usermanagement.py, cps/web.py, optional-requirements.txt, requirements.txt
Commit date: 2022-03-13 19:00:37 +0100
Commit message: Better epub cover parsing with multiple cover-image items
Code cosmetics
renamed variables
refactored xml page generation
refactored prepare author

Diff: diff --git a/cps.py b/cps.py
index 277da288fc..8959679ab1 100755
--- a/cps.py
+++ b/cps.py
@@ -38,11 +38,11 @@ from cps.web import web
 from cps.jinjia import jinjia
 from cps.about import about
 from cps.shelf import shelf
 from cps.admin import admi
 from cps.gdrive import gdrive
-from cps.editbooks import editbook
+from cps.editbooks import EditBook
 from cps.remotelogin import remotelogin
 from cps.search_metadata import meta
 from cps.error_handler import init_errorhandler
 
 try:
@@ -71,11 +71,11 @@ def main():
     app.register_blueprint(shelf)
     app.register_blueprint(admi)
     app.register_blueprint(remotelogin)
     app.register_blueprint(meta)
     app.register_blueprint(gdrive)
-    app.register_blueprint(editbook)
+    app.register_blueprint(EditBook)
     if kobo_available:
         app.register_blueprint(kobo)
         app.register_blueprint(kobo_auth)
     if oauth_available:
         app.register_blueprint(oauth)
diff --git a/cps/__init__.py b/cps/__init__.py
index 2dfad699d5..0b912d23cc 100644
--- a/cps/__init__.py
+++ b/cps/__init__.py
@@ -154,11 +154,11 @@ def create_app():
         services.ldap.init_app(app, config)
     if services.goodreads_support:
         services.goodreads_support.connect(config.config_goodreads_api_key,
                                            config.config_goodreads_api_secret,
                                            config.config_use_goodreads)
-    config.store_calibre_uuid(calibre_db, db.Library_Id)
+    config.store_calibre_uuid(calibre_db, db.LibraryId)
     return app
 
 @babel.localeselector
 def get_locale():
     # if a user is logged in, use the locale from the user settings
diff --git a/cps/admin.py b/cps/admin.py
index 3d4ca60963..5d8ed9c168 100644
--- a/cps/admin.py
+++ b/cps/admin.py
@@ -25,12 +25,13 @@ import re
 import base64
 import json
 import time
 import operator
 from datetime import datetime, timedelta
+from functools import wraps
 
-from babel import Locale as LC
+from babel import Locale
 from babel.dates import format_datetime
 from flask import Blueprint, flash, redirect, url_for, abort, request, make_response, send_from_directory, g, Response
 from flask_login import login_required, current_user, logout_user, confirm_login
 from flask_babel import gettext as _
 from flask import session as flask_session
@@ -45,11 +46,10 @@ from .helper import check_valid_domain, send_test_mail, reset_password, generate
     valid_email, check_username
 from .gdriveutils import is_gdrive_ready, gdrive_support
 from .render_template import render_title_template, get_sidebar_config
 from . import debug_info, _BABEL_TRANSLATIONS
 
-from functools import wraps
 
 log = logger.create()
 
 feature_support = {
         'ldap': bool(services.ldap),
@@ -187,14 +187,14 @@ def admin():
                     form_date += timedelta(hours=int(commit[20:22]), minutes=int(commit[23:]))
             commit = format_datetime(form_date - tz, format='short', locale=get_locale())
         else:
             commit = version['version']
 
-    allUser = ub.session.query(ub.User).all()
+    all_user = ub.session.query(ub.User).all()
     email_settings = config.get_mail_settings()
     kobo_support = feature_support['kobo'] and config.config_kobo_sync
-    return render_title_template("admin.html", allUser=allUser, email=email_settings, config=config, commit=commit,
+    return render_title_template("admin.html", allUser=all_user, email=email_settings, config=config, commit=commit,
                                  feature_support=feature_support, kobo_support=kobo_support,
                                  title=_(u"Admin page"), page="admin")
 
 
 @admi.route("/admin/dbconfig", methods=["GET", "POST"])
@@ -240,16 +240,16 @@ def calibreweb_alive():
 
 @admi.route("/admin/viewconfig")
 @login_required
 @admin_required
 def view_configuration():
-    read_column = calibre_db.session.query(db.Custom_Columns)\
-        .filter(and_(db.Custom_Columns.datatype == 'bool', db.Custom_Columns.mark_for_delete == 0)).all()
-    restrict_columns = calibre_db.session.query(db.Custom_Columns)\
-        .filter(and_(db.Custom_Columns.datatype == 'text', db.Custom_Columns.mark_for_delete == 0)).all()
+    read_column = calibre_db.session.query(db.CustomColumns)\
+        .filter(and_(db.CustomColumns.datatype == 'bool', db.CustomColumns.mark_for_delete == 0)).all()
+    restrict_columns = calibre_db.session.query(db.CustomColumns)\
+        .filter(and_(db.CustomColumns.datatype == 'text', db.CustomColumns.mark_for_delete == 0)).all()
     languages = calibre_db.speaking_language()
-    translations = [LC('en')] + babel.list_translations()
+    translations = [Locale('en')] + babel.list_translations()
     return render_title_template("config_view_edit.html", conf=config, readColumns=read_column,
                                  restrictColumns=restrict_columns,
                                  languages=languages,
                                  translations=translations,
                                  title=_(u"UI Configuration"), page="uiconfig")
@@ -259,12 +259,12 @@ def view_configuration():
 @login_required
 @admin_required
 def edit_user_table():
     visibility = current_user.view_settings.get('useredit', {})
     languages = calibre_db.speaking_language()
-    translations = babel.list_translations() + [LC('en')]
-    allUser = ub.session.query(ub.User)
+    translations = babel.list_translations() + [Locale('en')]
+    all_user = ub.session.query(ub.User)
     tags = calibre_db.session.query(db.Tags)\
         .join(db.books_tags_link)\
         .join(db.Books)\
         .filter(calibre_db.common_filters()) \
         .group_by(text('books_tags_link.tag'))\
@@ -272,14 +272,14 @@ def edit_user_table():
     if config.config_restricted_column:
         custom_values = calibre_db.session.query(db.cc_classes[config.config_restricted_column]).all()
     else:
         custom_values = []
     if not config.config_anonbrowse:
-        allUser = allUser.filter(ub.User.role.op('&')(constants.ROLE_ANONYMOUS) != constants.ROLE_ANONYMOUS)
+        all_user = all_user.filter(ub.User.role.op('&')(constants.ROLE_ANONYMOUS) != constants.ROLE_ANONYMOUS)
     kobo_support = feature_support['kobo'] and config.config_kobo_sync
     return render_title_template("user_table.html",
-                                 users=allUser.all(),
+                                 users=all_user.all(),
                                  tags=tags,
                                  custom_values=custom_values,
                                  translations=translations,
                                  languages=languages,
                                  visiblility=visibility,
@@ -330,11 +330,11 @@ def list_users():
 
     for user in users:
         if user.default_language == "all":
             user.default = _("All")
         else:
-            user.default = LC.parse(user.default_language).get_language_name(get_locale())
+            user.default = Locale.parse(user.default_language).get_language_name(get_locale())
 
     table_entries = {'totalNotFiltered': total_count, 'total': filtered_count, "rows": users}
     js_list = json.dumps(table_entries, cls=db.AlchemyEncoder)
     response = make_response(js_list)
     response.headers["Content-Type"] = "application/json; charset=utf-8"
@@ -378,11 +378,11 @@ def delete_user():
 
 @admi.route("/ajax/getlocale")
 @login_required
 @admin_required
 def table_get_locale():
-    locale = babel.list_translations() + [LC('en')]
+    locale = babel.list_translations() + [Locale('en')]
     ret = list()
     current_locale = get_locale()
     for loc in locale:
         ret.append({'value': str(loc), 'text': loc.get_language_name(current_locale)})
     return json.dumps(ret)
@@ -442,11 +442,11 @@ def edit_list_user(param):
                 elif param == 'kindle_mail':
                     user.kindle_mail = valid_email(vals['value']) if vals['value'] else ""
                 elif param.endswith('role'):
                     value = int(vals['field_index'])
                     if user.name == "Guest" and value in \
-                                 [constants.ROLE_ADMIN, constants.ROLE_PASSWD, constants.ROLE_EDIT_SHELFS]:
+                            [constants.ROLE_ADMIN, constants.ROLE_PASSWD, constants.ROLE_EDIT_SHELFS]:
                         raise Exception(_("Guest can't have this role"))
                     # check for valid value, last on checks for power of 2 value
                     if value > 0 and value <= constants.ROLE_VIEWER and (value & value-1 == 0 or value == 1):
                         if vals['value'] == 'true':
                             user.role |= value
@@ -522,20 +522,20 @@ def update_table_settings():
     return ""
 
 
 def check_valid_read_column(column):
     if column != "0":
-        if not calibre_db.session.query(db.Custom_Columns).filter(db.Custom_Columns.id == column) \
-              .filter(and_(db.Custom_Columns.datatype == 'bool', db.Custom_Columns.mark_for_delete == 0)).all():
+        if not calibre_db.session.query(db.CustomColumns).filter(db.CustomColumns.id == column) \
+              .filter(and_(db.CustomColumns.datatype == 'bool', db.CustomColumns.mark_for_delete == 0)).all():
             return False
     return True
 
 
 def check_valid_restricted_column(column):
     if column != "0":
-        if not calibre_db.session.query(db.Custom_Columns).filter(db.Custom_Columns.id == column) \
-              .filter(and_(db.Custom_Columns.datatype == 'text', db.Custom_Columns.mark_for_delete == 0)).all():
+        if not calibre_db.session.query(db.CustomColumns).filter(db.CustomColumns.id == column) \
+              .filter(and_(db.CustomColumns.datatype == 'text', db.CustomColumns.mark_for_delete == 0)).all():
             return False
     return True
 
 
 @admi.route("/admin/viewconfig", methods=["POST"])
@@ -1076,16 +1076,16 @@ def _configuration_gdrive_helper(to_save):
 def _configuration_oauth_helper(to_save):
     active_oauths = 0
     reboot_required = False
     for element in oauthblueprints:
         if to_save["config_" + str(element['id']) + "_oauth_client_id"] != element['oauth_client_id'] \
-            or to_save["config_" + str(element['id']) + "_oauth_client_secret"] != element['oauth_client_secret']:
+                or to_save["config_" + str(element['id']) + "_oauth_client_secret"] != element['oauth_client_secret']:
             reboot_required = True
             element['oauth_client_id'] = to_save["config_" + str(element['id']) + "_oauth_client_id"]
             element['oauth_client_secret'] = to_save["config_" + str(element['id']) + "_oauth_client_secret"]
         if to_save["config_" + str(element['id']) + "_oauth_client_id"] \
-            and to_save["config_" + str(element['id']) + "_oauth_client_secret"]:
+                and to_save["config_" + str(element['id']) + "_oauth_client_secret"]:
             active_oauths += 1
             element["active"] = 1
         else:
             element["active"] = 0
         ub.session.query(ub.OAuthProvider).filter(ub.OAuthProvider.id == element['id']).update(
@@ -1134,11 +1134,11 @@ def _configuration_ldap_helper(to_save):
     config.save()
 
     if not config.config_ldap_provider_url \
         or not config.config_ldap_port \
         or not config.config_ldap_dn \
-        or not config.config_ldap_user_object:
+            or not config.config_ldap_user_object:
         return reboot_required, _configuration_result(_('Please Enter a LDAP Provider, '
                                                         'Port, DN and User Object Identifier'))
 
     if config.config_ldap_authentication > constants.LDAP_AUTH_ANONYMOUS:
         if config.config_ldap_authentication > constants.LDAP_AUTH_UNAUTHENTICATE:
@@ -1209,10 +1209,11 @@ def _db_configuration_update_helper():
 
     to_save['config_calibre_dir'] = re.sub(r'[\\/]metadata\.db$',
                                            '',
                                            to_save['config_calibre_dir'],
                                            flags=re.IGNORECASE)
+    db_valid = False
     try:
         db_change, db_valid = _db_simulate_change()
 
         # gdrive_error drive setup
         gdrive_error = _configuration_gdrive_helper(to_save)
@@ -1227,15 +1228,15 @@ def _db_configuration_update_helper():
             db_change = True
     except Exception as ex:
         return _db_configuration_result('{}'.format(ex), gdrive_error)
 
     if db_change or not db_valid or not config.db_configured \
-          or config.config_calibre_dir != to_save["config_calibre_dir"]:
+            or config.config_calibre_dir != to_save["config_calibre_dir"]:
         if not calibre_db.setup_db(to_save['config_calibre_dir'], ub.app_DB_path):
             return _db_configuration_result(_('DB Location is not Valid, Please Enter Correct Path'),
                                             gdrive_error)
-        config.store_calibre_uuid(calibre_db, db.Library_Id)
+        config.store_calibre_uuid(calibre_db, db.LibraryId)
         # if db changed -> delete shelfs, delete download books, delete read books, kobo sync...
         if db_change:
             log.info("Calibre Database changed, delete all Calibre-Web info related to old Database")
             ub.session.query(ub.Downloads).delete()
             ub.session.query(ub.ArchivedBook).delete()
@@ -1270,11 +1271,11 @@ def _configuration_update_helper():
 
         _config_checkbox_int(to_save, "config_uploading")
         _config_checkbox_int(to_save, "config_unicode_filename")
         # Reboot on config_anonbrowse with enabled ldap, as decoraters are changed in this case
         reboot_required |= (_config_checkbox_int(to_save, "config_anonbrowse")
-                             and config.config_login_type == constants.LOGIN_LDAP)
+                            and config.config_login_type == constants.LOGIN_LDAP)
         _config_checkbox_int(to_save, "config_public_reg")
         _config_checkbox_int(to_save, "config_register_email")
         reboot_required |= _config_checkbox_int(to_save, "config_kobo_sync")
         _config_int(to_save, "config_external_port")
         _config_checkbox_int(to_save, "config_kobo_proxy")
@@ -1558,11 +1559,11 @@ def _handle_edit_user(to_save, content, languages, translations, kobo_support):
 @login_required
 @admin_required
 def new_user():
     content = ub.User()
     languages = calibre_db.speaking_language()
-    translations = [LC('en')] + babel.list_translations()
+    translations = [Locale('en')] + babel.list_translations()
     kobo_support = feature_support['kobo'] and config.config_kobo_sync
     if request.method == "POST":
         to_save = request.form.to_dict()
         _handle_new_user(to_save, content, languages, translations, kobo_support)
     else:
@@ -1645,11 +1646,11 @@ def edit_user(user_id):
     content = ub.session.query(ub.User).filter(ub.User.id == int(user_id)).first()  # type: ub.User
     if not content or (not config.config_anonbrowse and content.name == "Guest"):
         flash(_(u"User not found"), category="error")
         return redirect(url_for('admin.admin'))
     languages = calibre_db.speaking_language(return_all_languages=True)
-    translations = babel.list_translations() + [LC('en')]
+    translations = babel.list_translations() + [Locale('en')]
     kobo_support = feature_support['kobo'] and config.config_kobo_sync
     if request.method == "POST":
         to_save = request.form.to_dict()
         resp = _handle_edit_user(to_save, content, languages, translations, kobo_support)
         if resp:
diff --git a/cps/db.py b/cps/db.py
index 2292614cad..b910363b7d 100644
--- a/cps/db.py
+++ b/cps/db.py
@@ -15,17 +15,17 @@
 #  GNU General Public License for more details.
 #
 #  You should have received a copy of the GNU General Public License
 #  along with this program. If not, see <http://www.gnu.org/licenses/>.
 
-import copy
 import os
 import re
 import ast
 import json
 from datetime import datetime
 from urllib.parse import quote
+import unidecode
 
 from sqlalchemy import create_engine
 from sqlalchemy import Table, Column, ForeignKey, CheckConstraint
 from sqlalchemy import String, Integer, Boolean, TIMESTAMP, Float
 from sqlalchemy.orm import relationship, sessionmaker, scoped_session
@@ -47,15 +47,10 @@ from flask import flash
 from . import logger, ub, isoLanguages
 from .pagination import Pagination
 
 from weakref import WeakSet
 
-try:
-    import unidecode
-    use_unidecode = True
-except ImportError:
-    use_unidecode = False
 
 log = logger.create()
 
 cc_exceptions = ['composite', 'series']
 cc_classes = {}
@@ -91,11 +86,11 @@ books_publishers_link = Table('books_publishers_link', Base.metadata,
                               Column('book', Integer, ForeignKey('books.id'), primary_key=True),
                               Column('publisher', Integer, ForeignKey('publishers.id'), primary_key=True)
                               )
 
 
-class Library_Id(Base):
+class LibraryId(Base):
     __tablename__ = 'library_id'
     id = Column(Integer, primary_key=True)
     uuid = Column(String, nullable=False)
 
 
@@ -110,11 +105,11 @@ class Identifiers(Base):
     def __init__(self, val, id_type, book):
         self.val = val
         self.type = id_type
         self.book = book
 
-    def formatType(self):
+    def format_type(self):
         format_type = self.type.lower()
         if format_type == 'amazon':
             return u"Amazon"
         elif format_type.startswith("amazon_"):
             return u"Amazon.{0}".format(format_type[7:])
@@ -182,12 +177,12 @@ class Comments(Base):
 
     id = Column(Integer, primary_key=True)
     book = Column(Integer, ForeignKey('books.id'), nullable=False, unique=True)
     text = Column(String(collation='NOCASE'), nullable=False)
 
-    def __init__(self, text, book):
-        self.text = text
+    def __init__(self, comment, book):
+        self.text = comment
         self.book = book
 
     def get(self):
         return self.text
 
@@ -365,22 +360,21 @@ class Books(Base):
         self.series_index = series_index
         self.last_modified = last_modified
         self.path = path
         self.has_cover = (has_cover != None)
 
-
     def __repr__(self):
         return u"<Books('{0},{1}{2}{3}{4}{5}{6}{7}{8}')>".format(self.title, self.sort, self.author_sort,
                                                                  self.timestamp, self.pubdate, self.series_index,
                                                                  self.last_modified, self.path, self.has_cover)
 
     @property
     def atom_timestamp(self):
-        return (self.timestamp.strftime('%Y-%m-%dT%H:%M:%S+00:00') or '')
+        return self.timestamp.strftime('%Y-%m-%dT%H:%M:%S+00:00') or ''
 
 
-class Custom_Columns(Base):
+class CustomColumns(Base):
     __tablename__ = 'custom_columns'
 
     id = Column(Integer, primary_key=True)
     label = Column(String)
     name = Column(String)
@@ -434,11 +428,11 @@ class AlchemyEncoder(json.JSONEncoder):
             return fields
 
         return json.JSONEncoder.default(self, o)
 
 
-class CalibreDB():
+class CalibreDB:
     _init = False
     engine = None
     config = None
     session_factory = None
     # This is a WeakSet so that references here don't keep other CalibreDB
@@ -448,21 +442,21 @@ class CalibreDB():
     def __init__(self, expire_on_commit=True):
         """ Initialize a new CalibreDB session
         """
         self.session = None
         if self._init:
-            self.initSession(expire_on_commit)
+            self.init_session(expire_on_commit)
 
         self.instances.add(self)
 
-    def initSession(self, expire_on_commit=True):
+    def init_session(self, expire_on_commit=True):
         self.session = self.session_factory()
         self.session.expire_on_commit = expire_on_commit
         self.update_title_sort(self.config)
 
     @classmethod
-    def setup_db_cc_classes(self, cc):
+    def setup_db_cc_classes(cls, cc):
         cc_ids = []
         books_custom_column_links = {}
         for row in cc:
             if row.datatype not in cc_exceptions:
                 if row.datatype == 'series':
@@ -537,20 +531,20 @@ class CalibreDB():
         dbpath = os.path.join(config_calibre_dir, "metadata.db")
         if not os.path.exists(dbpath):
             return False, False
         try:
             check_engine = create_engine('sqlite://',
-                          echo=False,
-                          isolation_level="SERIALIZABLE",
-                          connect_args={'check_same_thread': False},
-                          poolclass=StaticPool)
+                                         echo=False,
+                                         isolation_level="SERIALIZABLE",
+                                         connect_args={'check_same_thread': False},
+                                         poolclass=StaticPool)
             with check_engine.begin() as connection:
                 connection.execute(text("attach database '{}' as calibre;".format(dbpath)))
                 connection.execute(text("attach database '{}' as app_settings;".format(app_db_path)))
                 local_session = scoped_session(sessionmaker())
                 local_session.configure(bind=connection)
-                database_uuid = local_session().query(Library_Id).one_or_none()
+                database_uuid = local_session().query(LibraryId).one_or_none()
                 # local_session.dispose()
 
             check_engine.connect()
             db_change = config_calibre_uuid != database_uuid.uuid
         except Exception:
@@ -601,11 +595,11 @@ class CalibreDB():
 
         cls.session_factory = scoped_session(sessionmaker(autocommit=False,
                                                           autoflush=True,
                                                           bind=cls.engine))
         for inst in cls.instances:
-            inst.initSession()
+            inst.init_session()
 
         cls._init = True
         return True
 
     def get_book(self, book_id):
@@ -642,16 +636,14 @@ class CalibreDB():
         return self.session.query(Data).filter(Data.book == book_id).filter(Data.format == file_format).first()
 
     # Language and content filters for displaying in the UI
     def common_filters(self, allow_show_archived=False, return_all_languages=False):
         if not allow_show_archived:
-            archived_books = (
-                ub.session.query(ub.ArchivedBook)
-                    .filter(ub.ArchivedBook.user_id == int(current_user.id))
-                    .filter(ub.ArchivedBook.is_archived == True)
-                    .all()
-            )
+            archived_books = (ub.session.query(ub.ArchivedBook)
+                              .filter(ub.ArchivedBook.user_id == int(current_user.id))
+                              .filter(ub.ArchivedBook.is_archived == True)
+                              .all())
             archived_book_ids = [archived_book.book_id for archived_book in archived_books]
             archived_filter = Books.id.notin_(archived_book_ids)
         else:
             archived_filter = true()
 
@@ -666,15 +658,15 @@ class CalibreDB():
         if self.config.config_restricted_column:
             try:
                 pos_cc_list = current_user.allowed_column_value.split(',')
                 pos_content_cc_filter = true() if pos_cc_list == [''] else \
                     getattr(Books, 'custom_column_' + str(self.config.config_restricted_column)). \
-                        any(cc_classes[self.config.config_restricted_column].value.in_(pos_cc_list))
+                    any(cc_classes[self.config.config_restricted_column].value.in_(pos_cc_list))
                 neg_cc_list = current_user.denied_column_value.split(',')
                 neg_content_cc_filter = false() if neg_cc_list == [''] else \
                     getattr(Books, 'custom_column_' + str(self.config.config_restricted_column)). \
-                        any(cc_classes[self.config.config_restricted_column].value.in_(neg_cc_list))
+                    any(cc_classes[self.config.config_restricted_column].value.in_(neg_cc_list))
             except (KeyError, AttributeError):
                 pos_content_cc_filter = false()
                 neg_content_cc_filter = true()
                 log.error(u"Custom Column No.%d is not existing in calibre database",
                           self.config.config_restricted_column)
@@ -726,21 +718,21 @@ class CalibreDB():
         if join_archive_read:
             if not config_read_column:
                 query = (self.session.query(database, ub.ReadBook.read_status, ub.ArchivedBook.is_archived)
                          .select_from(Books)
                          .outerjoin(ub.ReadBook,
-                               and_(ub.ReadBook.user_id == int(current_user.id), ub.ReadBook.book_id == Books.id)))
+                                    and_(ub.ReadBook.user_id == int(current_user.id), ub.ReadBook.book_id == Books.id)))
             else:
                 try:
                     read_column = cc_classes[config_read_column]
                     query = (self.session.query(database, read_column.value, ub.ArchivedBook.is_archived)
                              .select_from(Books)
                              .outerjoin(read_column, read_column.book == Books.id))
                 except (KeyError, AttributeError):
                     log.error("Custom Column No.%d is not existing in calibre database", read_column)
                     # Skip linking read column and return None instead of read status
-                    query =self.session.query(database, None, ub.ArchivedBook.is_archived)
+                    query = self.session.query(database, None, ub.ArchivedBook.is_archived)
             query = query.outerjoin(ub.ArchivedBook, and_(Books.id == ub.ArchivedBook.book_id,
                                                           int(current_user.id) == ub.ArchivedBook.user_id))
         else:
             query = self.session.query(database)
         off = int(int(pagesize) * (page - 1))
@@ -810,11 +802,10 @@ class CalibreDB():
                     entry.ordered_authors = authors_ordered
             else:
                 return authors_ordered
         return entries
 
-
     def get_typeahead(self, database, query, replace=('', ''), tag_filter=true()):
         query = query or ''
         self.session.connection().connection.connection.create_function("lower", 1, lcase)
         entries = self.session.query(database).filter(tag_filter). \
             filter(func.lower(database.name).ilike("%" + query + "%")).all()
@@ -870,11 +861,11 @@ class CalibreDB():
                 Books.publishers.any(func.lower(Publishers.name).ilike("%" + term + "%")),
                 func.lower(Books.title).ilike("%" + term + "%")
                 ))
 
     # read search results from calibre-database and return it (function is used for feed and simple search
-    def get_search_results(self, term, offset=None, order=None, limit=None, allow_show_archived=False,
+    def get_search_results(self, term, offset=None, order=None, limit=None,
                            config_read_column=False, *join):
         order = order[0] if order else [Books.sort]
         pagination = None
         result = self.search_query(term, config_read_column, *join).order_by(*order).all()
         result_count = len(result)
@@ -913,11 +904,10 @@ class CalibreDB():
                     .group_by(text('books_languages_link.lang_code')).all()
             for lang in languages:
                 lang.name = isoLanguages.get_language_name(get_locale(), lang.lang_code)
             return sorted(languages, key=lambda x: x.name, reverse=reverse_order)
 
-
     def update_title_sort(self, config, conn=None):
         # user defined sort function for calibre databases (Series, etc.)
         def _title_sort(title):
             # calibre sort stuff
             title_pat = re.compile(config.config_title_regex, re.IGNORECASE)
@@ -971,8 +961,8 @@ class CalibreDB():
 
 def lcase(s):
     try:
         return unidecode.unidecode(s.lower())
     except Exception as ex:
-        log = logger.create()
-        log.error_or_exception(ex)
+        _log = logger.create()
+        _log.error_or_exception(ex)
         return s.lower()
diff --git a/cps/editbooks.py b/cps/editbooks.py
index 30535ef3de..728164f915 100755
--- a/cps/editbooks.py
+++ b/cps/editbooks.py
@@ -29,11 +29,11 @@ from markupsafe import escape
 from functools import wraps
 
 try:
     from lxml.html.clean import clean_html
 except ImportError:
-    pass
+    clean_html = None
 
 from flask import Blueprint, request, flash, redirect, url_for, abort, Markup, Response
 from flask_babel import gettext as _
 from flask_login import current_user, login_required
 from sqlalchemy.exc import OperationalError, IntegrityError
@@ -46,11 +46,11 @@ from .tasks.upload import TaskUpload
 from .render_template import render_title_template
 from .usermanagement import login_required_if_no_ano
 from .kobo_sync_status import change_archived_books
 
 
-editbook = Blueprint('editbook', __name__)
+EditBook = Blueprint('edit-book', __name__)
 log = logger.create()
 
 
 def upload_required(f):
     @wraps(f)
@@ -59,19 +59,21 @@ def upload_required(f):
             return f(*args, **kwargs)
         abort(403)
 
     return inner
 
+
 def edit_required(f):
     @wraps(f)
     def inner(*args, **kwargs):
         if current_user.role_edit() or current_user.role_admin():
             return f(*args, **kwargs)
         abort(403)
 
     return inner
 
+
 def search_objects_remove(db_book_object, db_type, input_elements):
     del_elements = []
     for c_elements in db_book_object:
         found = False
         if db_type == 'languages':
@@ -117,20 +119,21 @@ def remove_objects(db_book_object, db_session, del_elements):
             changed = True
             if len(del_element.books) == 0:
                 db_session.delete(del_element)
     return changed
 
+
 def add_objects(db_book_object, db_object, db_session, db_type, add_elements):
     changed = False
     if db_type == 'languages':
         db_filter = db_object.lang_code
     elif db_type == 'custom':
         db_filter = db_object.value
     else:
         db_filter = db_object.name
     for add_element in add_elements:
-        # check if a element with that name exists
+        # check if an element with that name exists
         db_element = db_session.query(db_object).filter(db_filter == add_element).first()
         # if no element is found add it
         if db_type == 'author':
             new_element = db_object(add_element, helper.get_sorted_author(add_element.replace('|', ',')), "")
         elif db_type == 'series':
@@ -145,11 +148,10 @@ def add_objects(db_book_object, db_object, db_session, db_type, add_elements):
             changed = True
             db_session.add(new_element)
             db_book_object.append(new_element)
         else:
             db_element = create_objects_for_addition(db_element, add_element, db_type)
-            changed = True
             # add element to book
             changed = True
             db_book_object.append(db_element)
     return changed
 
@@ -176,11 +178,11 @@ def create_objects_for_addition(db_element, add_element, db_type):
     elif db_element.name != add_element:
         db_element.name = add_element
     return db_element
 
 
-# Modifies different Database objects, first check if elements if elements have to be deleted,
+# Modifies different Database objects, first check if elements have to be deleted,
 # because they are no longer used, than check if elements have to be added to database
 def modify_database_object(input_elements, db_book_object, db_object, db_session, db_type):
     # passing input_elements not as a list may lead to undesired results
     if not isinstance(input_elements, list):
         raise TypeError(str(input_elements) + " should be passed as a list")
@@ -205,11 +207,11 @@ def modify_identifiers(input_identifiers, db_identifiers, db_session):
     changed = False
     error = False
     input_dict = dict([(identifier.type.lower(), identifier) for identifier in input_identifiers])
     if len(input_identifiers) != len(input_dict):
         error = True
-    db_dict = dict([(identifier.type.lower(), identifier) for identifier in db_identifiers ])
+    db_dict = dict([(identifier.type.lower(), identifier) for identifier in db_identifiers])
     # delete db identifiers not present in input or modify them with input val
     for identifier_type, identifier in db_dict.items():
         if identifier_type not in input_dict.keys():
             db_session.delete(identifier)
             changed = True
@@ -222,18 +224,19 @@ def modify_identifiers(input_identifiers, db_identifiers, db_session):
         if identifier_type not in db_dict.keys():
             db_session.add(identifier)
             changed = True
     return changed, error
 
-@editbook.route("/ajax/delete/<int:book_id>", methods=["POST"])
+
+@EditBook.route("/ajax/delete/<int:book_id>", methods=["POST"])
 @login_required
 def delete_book_from_details(book_id):
     return Response(delete_book_from_table(book_id, "", True), mimetype='application/json')
 
 
-@editbook.route("/delete/<int:book_id>", defaults={'book_format': ""}, methods=["POST"])
-@editbook.route("/delete/<int:book_id>/<string:book_format>", methods=["POST"])
+@EditBook.route("/delete/<int:book_id>", defaults={'book_format': ""}, methods=["POST"])
+@EditBook.route("/delete/<int:book_id>/<string:book_format>", methods=["POST"])
 @login_required
 def delete_book_ajax(book_id, book_format):
     return delete_book_from_table(book_id, book_format, False)
 
 
@@ -250,12 +253,12 @@ def delete_whole_book(book_id, book):
     modify_database_object([u''], book.tags, db.Tags, calibre_db.session, 'tags')
     modify_database_object([u''], book.series, db.Series, calibre_db.session, 'series')
     modify_database_object([u''], book.languages, db.Languages, calibre_db.session, 'languages')
     modify_database_object([u''], book.publishers, db.Publishers, calibre_db.session, 'publishers')
 
-    cc = calibre_db.session.query(db.Custom_Columns). \
-        filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).all()
+    cc = calibre_db.session.query(db.CustomColumns). \
+        filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).all()
     for c in cc:
         cc_string = "custom_column_" + str(c.id)
         if not c.is_multiple:
             if len(getattr(book, cc_string)) > 0:
                 if c.datatype == 'bool' or c.datatype == 'integer' or c.datatype == 'float':
@@ -281,53 +284,53 @@ def delete_whole_book(book_id, book):
             modify_database_object([u''], getattr(book, cc_string), db.cc_classes[c.id],
                                    calibre_db.session, 'custom')
     calibre_db.session.query(db.Books).filter(db.Books.id == book_id).delete()
 
 
-def render_delete_book_result(book_format, jsonResponse, warning, book_id):
+def render_delete_book_result(book_format, json_response, warning, book_id):
     if book_format:
-        if jsonResponse:
-            return json.dumps([warning, {"location": url_for("editbook.edit_book", book_id=book_id),
+        if json_response:
+            return json.dumps([warning, {"location": url_for("edit-book.edit_book", book_id=book_id),
                                          "type": "success",
                                          "format": book_format,
                                          "message": _('Book Format Successfully Deleted')}])
         else:
             flash(_('Book Format Successfully Deleted'), category="success")
-            return redirect(url_for('editbook.edit_book', book_id=book_id))
+            return redirect(url_for('edit-book.edit_book', book_id=book_id))
     else:
-        if jsonResponse:
+        if json_response:
             return json.dumps([warning, {"location": url_for('web.index'),
                                          "type": "success",
                                          "format": book_format,
                                          "message": _('Book Successfully Deleted')}])
         else:
             flash(_('Book Successfully Deleted'), category="success")
             return redirect(url_for('web.index'))
 
 
-def delete_book_from_table(book_id, book_format, jsonResponse):
+def delete_book_from_table(book_id, book_format, json_response):
     warning = {}
     if current_user.role_delete_books():
         book = calibre_db.get_book(book_id)
         if book:
             try:
                 result, error = helper.delete_book(book, config.config_calibre_dir, book_format=book_format.upper())
                 if not result:
-                    if jsonResponse:
-                        return json.dumps([{"location": url_for("editbook.edit_book", book_id=book_id),
-                                           "type": "danger",
-                                           "format": "",
-                                           "message": error}])
+                    if json_response:
+                        return json.dumps([{"location": url_for("edit-book.edit_book", book_id=book_id),
+                                            "type": "danger",
+                                            "format": "",
+                                            "message": error}])
                     else:
                         flash(error, category="error")
-                        return redirect(url_for('editbook.edit_book', book_id=book_id))
+                        return redirect(url_for('edit-book.edit_book', book_id=book_id))
                 if error:
-                    if jsonResponse:
-                        warning = {"location": url_for("editbook.edit_book", book_id=book_id),
-                                                "type": "warning",
-                                                "format": "",
-                                                "message": error}
+                    if json_response:
+                        warning = {"location": url_for("edit-book.edit_book", book_id=book_id),
+                                   "type": "warning",
+                                   "format": "",
+                                   "message": error}
                     else:
                         flash(error, category="warning")
                 if not book_format:
                     delete_whole_book(book_id, book)
                 else:
@@ -337,39 +340,40 @@ def delete_book_from_table(book_id, book_format, jsonResponse):
                         kobo_sync_status.remove_synced_book(book.id, True)
                 calibre_db.session.commit()
             except Exception as ex:
                 log.error_or_exception(ex)
                 calibre_db.session.rollback()
-                if jsonResponse:
-                    return json.dumps([{"location": url_for("editbook.edit_book", book_id=book_id),
+                if json_response:
+                    return json.dumps([{"location": url_for("edit-book.edit_book", book_id=book_id),
                                         "type": "danger",
                                         "format": "",
                                         "message": ex}])
                 else:
                     flash(str(ex), category="error")
-                    return redirect(url_for('editbook.edit_book', book_id=book_id))
+                    return redirect(url_for('edit-book.edit_book', book_id=book_id))
 
         else:
             # book not found
             log.error('Book with id "%s" could not be deleted: not found', book_id)
-        return render_delete_book_result(book_format, jsonResponse, warning, book_id)
+        return render_delete_book_result(book_format, json_response, warning, book_id)
     message = _("You are missing permissions to delete books")
-    if jsonResponse:
-        return json.dumps({"location": url_for("editbook.edit_book", book_id=book_id),
+    if json_response:
+        return json.dumps({"location": url_for("edit-book.edit_book", book_id=book_id),
                            "type": "danger",
                            "format": "",
                            "message": message})
     else:
         flash(message, category="error")
-        return redirect(url_for('editbook.edit_book', book_id=book_id))
+        return redirect(url_for('edit-book.edit_book', book_id=book_id))
 
 
 def render_edit_book(book_id):
-    cc = calibre_db.session.query(db.Custom_Columns).filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).all()
+    cc = calibre_db.session.query(db.CustomColumns).filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).all()
     book = calibre_db.get_filtered_book(book_id, allow_show_archived=True)
     if not book:
-        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"), category="error")
+        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"),
+              category="error")
         return redirect(url_for("web.index"))
 
     for lang in book.languages:
         lang.language_name = isoLanguages.get_language_name(get_locale(), lang.lang_code)
 
@@ -378,13 +382,13 @@ def render_edit_book(book_id):
     author_names = []
     for authr in book.authors:
         author_names.append(authr.name.replace('|', ','))
 
     # Option for showing convertbook button
-    valid_source_formats=list()
+    valid_source_formats = list()
     allowed_conversion_formats = list()
-    kepub_possible=None
+    kepub_possible = None
     if config.config_converterpath:
         for file in book.data:
             if file.format.lower() in constants.EXTENSIONS_CONVERT_FROM:
                 valid_source_formats.append(file.format.lower())
     if config.config_kepubifypath and 'epub' in [file.format.lower() for file in book.data]:
@@ -428,10 +432,11 @@ def edit_book_ratings(to_save, book):
         if len(book.ratings) > 0:
             book.ratings.remove(book.ratings[0])
             changed = True
     return changed
 
+
 def edit_book_tags(tags, book):
     input_tags = tags.split(',')
     input_tags = list(map(lambda it: it.strip(), input_tags))
     # Remove duplicates
     input_tags = helper.uniq(input_tags)
@@ -444,52 +449,52 @@ def edit_book_series(series, book):
     return modify_database_object(input_series, book.series, db.Series, calibre_db.session, 'series')
 
 
 def edit_book_series_index(series_index, book):
     # Add default series_index to book
-    modif_date = False
+    modify_date = False
     series_index = series_index or '1'
     if not series_index.replace('.', '', 1).isdigit():
         flash(_("%(seriesindex)s is not a valid number, skipping", seriesindex=series_index), category="warning")
         return False
     if str(book.series_index) != series_index:
         book.series_index = series_index
-        modif_date = True
-    return modif_date
+        modify_date = True
+    return modify_date
 
 
 # Handle book comments/description
 def edit_book_comments(comments, book):
-    modif_date = False
+    modify_date = False
     if comments:
         comments = clean_html(comments)
     if len(book.comments):
         if book.comments[0].text != comments:
             book.comments[0].text = comments
-            modif_date = True
+            modify_date = True
     else:
         if comments:
-            book.comments.append(db.Comments(text=comments, book=book.id))
-            modif_date = True
-    return modif_date
+            book.comments.append(db.Comments(comment=comments, book=book.id))
+            modify_date = True
+    return modify_date
 
 
-def edit_book_languages(languages, book, upload=False, invalid=None):
+def edit_book_languages(languages, book, upload_mode=False, invalid=None):
     input_languages = languages.split(',')
     unknown_languages = []
-    if not upload:
+    if not upload_mode:
         input_l = isoLanguages.get_language_codes(get_locale(), input_languages, unknown_languages)
     else:
         input_l = isoLanguages.get_valid_language_codes(get_locale(), input_languages, unknown_languages)
-    for l in unknown_languages:
-        log.error("'%s' is not a valid language", l)
+    for lang in unknown_languages:
+        log.error("'%s' is not a valid language", lang)
         if isinstance(invalid, list):
-            invalid.append(l)
+            invalid.append(lang)
         else:
-            raise ValueError(_(u"'%(langname)s' is not a valid language", langname=l))
+            raise ValueError(_(u"'%(langname)s' is not a valid language", langname=lang))
     # ToDo: Not working correct
-    if upload and len(input_l) == 1:
+    if upload_mode and len(input_l) == 1:
         # If the language of the file is excluded from the users view, it's not imported, to allow the user to view
         # the book it's language is set to the filter language
         if input_l[0] != current_user.filter_language() and current_user.filter_language() != "all":
             input_l[0] = calibre_db.session.query(db.Languages). \
                 filter(db.Languages.lang_code == current_user.filter_language()).first().lang_code
@@ -569,21 +574,24 @@ def edit_cc_data_string(book, c, to_save, cc_db_value, cc_string):
                 cc_class.value == to_save[cc_string].strip()).first()
         # add cc value to book
         getattr(book, cc_string).append(new_cc)
     return changed, to_save
 
+
 def edit_single_cc_data(book_id, book, column_id, to_save):
-    cc = (calibre_db.session.query(db.Custom_Columns)
-          .filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions))
-          .filter(db.Custom_Columns.id == column_id)
+    cc = (calibre_db.session.query(db.CustomColumns)
+          .filter(db.CustomColumns.datatype.notin_(db.cc_exceptions))
+          .filter(db.CustomColumns.id == column_id)
           .all())
     return edit_cc_data(book_id, book, to_save, cc)
 
+
 def edit_all_cc_data(book_id, book, to_save):
-    cc = calibre_db.session.query(db.Custom_Columns).filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).all()
+    cc = calibre_db.session.query(db.CustomColumns).filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).all()
     return edit_cc_data(book_id, book, to_save, cc)
 
+
 def edit_cc_data(book_id, book, to_save, cc):
     changed = False
     for c in cc:
         cc_string = "custom_column_" + str(c.id)
         if not c.is_multiple:
@@ -612,14 +620,15 @@ def edit_cc_data(book_id, book, to_save, cc):
                                               db.cc_classes[c.id],
                                               calibre_db.session,
                                               'custom')
     return changed
 
-def upload_single_file(request, book, book_id):
+
+def upload_single_file(file_request, book, book_id):
     # Check and handle Uploaded file
-    if 'btn-upload-format' in request.files:
-        requested_file = request.files['btn-upload-format']
+    if 'btn-upload-format' in file_request.files:
+        requested_file = file_request.files['btn-upload-format']
         # check for empty request
         if requested_file.filename != '':
             if not current_user.role_upload():
                 abort(403)
             if '.' in requested_file.filename:
@@ -667,21 +676,21 @@ def upload_single_file(request, book, book_id):
                     flash(_(u"Database error: %(error)s.", error=e.orig), category="error")
                     return redirect(url_for('web.show_book', book_id=book.id))
 
             # Queue uploader info
             link = '<a href="{}">{}</a>'.format(url_for('web.show_book', book_id=book.id), escape(book.title))
-            uploadText=_(u"File format %(ext)s added to %(book)s", ext=file_ext.upper(), book=link)
-            WorkerThread.add(current_user.name, TaskUpload(uploadText, escape(book.title)))
+            upload_text = _(u"File format %(ext)s added to %(book)s", ext=file_ext.upper(), book=link)
+            WorkerThread.add(current_user.name, TaskUpload(upload_text, escape(book.title)))
 
             return uploader.process(
                 saved_filename, *os.path.splitext(requested_file.filename),
                 rarExecutable=config.config_rarfile_location)
 
 
-def upload_cover(request, book):
-    if 'btn-upload-cover' in request.files:
-        requested_file = request.files['btn-upload-cover']
+def upload_cover(cover_request, book):
+    if 'btn-upload-cover' in cover_request.files:
+        requested_file = cover_request.files['btn-upload-cover']
         # check for empty request
         if requested_file.filename != '':
             if not current_user.role_upload():
                 abort(403)
             ret, message = helper.save_cover(requested_file, book.path)
@@ -704,12 +713,12 @@ def handle_title_on_edit(book, book_title):
     return False
 
 
 def handle_author_on_edit(book, author_name, update_stored=True):
     # handle author(s)
-    # renamed = False
-    input_authors = author_name.split('&')
+    input_authors, renamed = prepare_authors(author_name)
+    '''input_authors = author_name.split('&')
     input_authors = list(map(lambda it: it.strip().replace(',', '|'), input_authors))
     # Remove duplicates in authors list
     input_authors = helper.uniq(input_authors)
     # we have all author names now
     if input_authors == ['']:
@@ -723,11 +732,11 @@ def handle_author_on_edit(book, author_name, update_stored=True):
             all_books = calibre_db.session.query(db.Books) \
                 .filter(db.Books.authors.any(db.Authors.name == renamed_author.name)).all()
             sorted_renamed_author = helper.get_sorted_author(renamed_author.name)
             sorted_old_author = helper.get_sorted_author(in_aut)
             for one_book in all_books:
-                one_book.author_sort = one_book.author_sort.replace(sorted_renamed_author, sorted_old_author)
+                one_book.author_sort = one_book.author_sort.replace(sorted_renamed_author, sorted_old_author)'''
 
     change = modify_database_object(input_authors, book.authors, db.Authors, calibre_db.session, 'author')
 
     # Search for each author if author is in database, if not, author name and sorted author name is generated new
     # everything then is assembled for sorted author field in database
@@ -744,15 +753,15 @@ def handle_author_on_edit(book, author_name, update_stored=True):
         book.author_sort = sort_authors
         change = True
     return input_authors, change, renamed
 
 
-@editbook.route("/admin/book/<int:book_id>", methods=['GET', 'POST'])
+@EditBook.route("/admin/book/<int:book_id>", methods=['GET', 'POST'])
 @login_required_if_no_ano
 @edit_required
 def edit_book(book_id):
-    modif_date = False
+    modify_date = False
 
     # create the function for sorting...
     try:
         calibre_db.update_title_sort(config)
     except sqliteOperationalError as e:
@@ -765,17 +774,18 @@ def edit_book(book_id):
 
     book = calibre_db.get_filtered_book(book_id, allow_show_archived=True)
 
     # Book not found
     if not book:
-        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"), category="error")
+        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"),
+              category="error")
         return redirect(url_for("web.index"))
 
     meta = upload_single_file(request, book, book_id)
     if upload_cover(request, book) is True:
         book.has_cover = 1
-        modif_date = True
+        modify_date = True
     try:
         to_save = request.form.to_dict()
         merge_metadata(to_save, meta)
         # Update book
         edited_books_id = None
@@ -784,19 +794,19 @@ def edit_book(book_id):
         title_change = handle_title_on_edit(book, to_save["book_title"])
 
         input_authors, authorchange, renamed = handle_author_on_edit(book, to_save["author_name"])
         if authorchange or title_change:
             edited_books_id = book.id
-            modif_date = True
+            modify_date = True
 
         if config.config_use_google_drive:
             gdriveutils.updateGdriveCalibreFromLocal()
 
-        error = False
+        error = ""
         if edited_books_id:
             error = helper.update_dir_structure(edited_books_id, config.config_calibre_dir, input_authors[0],
-                                               renamed_author=renamed)
+                                                renamed_author=renamed)
 
         if not error:
             if "cover_url" in to_save:
                 if to_save["cover_url"]:
                     if not current_user.role_upload():
@@ -806,46 +816,46 @@ def edit_book(book_id):
                         book.has_cover = 0
                     else:
                         result, error = helper.save_cover_from_url(to_save["cover_url"], book.path)
                         if result is True:
                             book.has_cover = 1
-                            modif_date = True
+                            modify_date = True
                         else:
                             flash(error, category="error")
 
             # Add default series_index to book
-            modif_date |= edit_book_series_index(to_save["series_index"], book)
+            modify_date |= edit_book_series_index(to_save["series_index"], book)
             # Handle book comments/description
-            modif_date |= edit_book_comments(Markup(to_save['description']).unescape(), book)
+            modify_date |= edit_book_comments(Markup(to_save['description']).unescape(), book)
             # Handle identifiers
             input_identifiers = identifier_list(to_save, book)
             modification, warning = modify_identifiers(input_identifiers, book.identifiers, calibre_db.session)
             if warning:
                 flash(_("Identifiers are not Case Sensitive, Overwriting Old Identifier"), category="warning")
-            modif_date |= modification
+            modify_date |= modification
             # Handle book tags
-            modif_date |= edit_book_tags(to_save['tags'], book)
+            modify_date |= edit_book_tags(to_save['tags'], book)
             # Handle book series
-            modif_date |= edit_book_series(to_save["series"], book)
+            modify_date |= edit_book_series(to_save["series"], book)
             # handle book publisher
-            modif_date |= edit_book_publisher(to_save['publisher'], book)
+            modify_date |= edit_book_publisher(to_save['publisher'], book)
             # handle book languages
-            modif_date |= edit_book_languages(to_save['languages'], book)
+            modify_date |= edit_book_languages(to_save['languages'], book)
             # handle book ratings
-            modif_date |= edit_book_ratings(to_save, book)
+            modify_date |= edit_book_ratings(to_save, book)
             # handle cc data
-            modif_date |= edit_all_cc_data(book_id, book, to_save)
+            modify_date |= edit_all_cc_data(book_id, book, to_save)
 
             if to_save["pubdate"]:
                 try:
                     book.pubdate = datetime.strptime(to_save["pubdate"], "%Y-%m-%d")
                 except ValueError:
                     book.pubdate = db.Books.DEFAULT_PUBDATE
             else:
                 book.pubdate = db.Books.DEFAULT_PUBDATE
 
-            if modif_date:
+            if modify_date:
                 book.last_modified = datetime.utcnow()
                 kobo_sync_status.remove_synced_book(edited_books_id, all=True)
 
             calibre_db.session.merge(book)
             calibre_db.session.commit()
@@ -903,18 +913,11 @@ def identifier_list(to_save, book):
             continue
         result.append(db.Identifiers(to_save[val_key], type_value, book.id))
     return result
 
 
-def prepare_authors_on_upload(title, authr):
-    if title != _(u'Unknown') and authr != _(u'Unknown'):
-        entry = calibre_db.check_exists_book(authr, title)
-        if entry:
-            log.info("Uploaded book probably exists in library")
-            flash(_(u"Uploaded book probably exists in the library, consider to change before upload new: ")
-                  + Markup(render_title_template('book_exists_flash.html', entry=entry)), category="warning")
-
+def prepare_authors(authr):
     # handle authors
     input_authors = authr.split('&')
     # handle_authors(input_authors)
     input_authors = list(map(lambda it: it.strip().replace(',', '|'), input_authors))
     # Remove duplicates in authors list
@@ -933,10 +936,22 @@ def prepare_authors_on_upload(title, authr):
                 .filter(db.Books.authors.any(db.Authors.name == renamed_author.name)).all()
             sorted_renamed_author = helper.get_sorted_author(renamed_author.name)
             sorted_old_author = helper.get_sorted_author(in_aut)
             for one_book in all_books:
                 one_book.author_sort = one_book.author_sort.replace(sorted_renamed_author, sorted_old_author)
+    return input_authors, renamed
+
+
+def prepare_authors_on_upload(title, authr):
+    if title != _(u'Unknown') and authr != _(u'Unknown'):
+        entry = calibre_db.check_exists_book(authr, title)
+        if entry:
+            log.info("Uploaded book probably exists in library")
+            flash(_(u"Uploaded book probably exists in the library, consider to change before upload new: ")
+                  + Markup(render_title_template('book_exists_flash.html', entry=entry)), category="warning")
+
+    input_authors, renamed = prepare_authors(authr)
 
     sort_authors_list = list()
     db_author = None
     for inp in input_authors:
         stored_author = calibre_db.session.query(db.Authors).filter(db.Authors.name == inp).first()
@@ -953,46 +968,46 @@ def prepare_authors_on_upload(title, authr):
         sort_authors_list.append(sort_author)
     sort_authors = ' & '.join(sort_authors_list)
     return sort_authors, input_authors, db_author, renamed
 
 
-def create_book_on_upload(modif_date, meta):
+def create_book_on_upload(modify_date, meta):
     title = meta.title
     authr = meta.author
     sort_authors, input_authors, db_author, renamed_authors = prepare_authors_on_upload(title, authr)
 
     title_dir = helper.get_valid_filename(title, chars=96)
     author_dir = helper.get_valid_filename(db_author.name, chars=96)
 
-    # combine path and normalize path from windows systems
+    # combine path and normalize path from Windows systems
     path = os.path.join(author_dir, title_dir).replace('\\', '/')
 
     # Calibre adds books with utc as timezone
     db_book = db.Books(title, "", sort_authors, datetime.utcnow(), datetime(101, 1, 1),
                        '1', datetime.utcnow(), path, meta.cover, db_author, [], "")
 
-    modif_date |= modify_database_object(input_authors, db_book.authors, db.Authors, calibre_db.session,
-                                         'author')
+    modify_date |= modify_database_object(input_authors, db_book.authors, db.Authors, calibre_db.session,
+                                          'author')
 
     # Add series_index to book
-    modif_date |= edit_book_series_index(meta.series_id, db_book)
+    modify_date |= edit_book_series_index(meta.series_id, db_book)
 
     # add languages
-    invalid=[]
-    modif_date |= edit_book_languages(meta.languages, db_book, upload=True, invalid=invalid)
+    invalid = []
+    modify_date |= edit_book_languages(meta.languages, db_book, upload_mode=True, invalid=invalid)
     if invalid:
-        for l in invalid:
-            flash(_(u"'%(langname)s' is not a valid language", langname=l), category="warning")
+        for lang in invalid:
+            flash(_(u"'%(langname)s' is not a valid language", langname=lang), category="warning")
 
     # handle tags
-    modif_date |= edit_book_tags(meta.tags, db_book)
+    modify_date |= edit_book_tags(meta.tags, db_book)
 
     # handle publisher
-    modif_date |= edit_book_publisher(meta.publisher, db_book)
+    modify_date |= edit_book_publisher(meta.publisher, db_book)
 
     # handle series
-    modif_date |= edit_book_series(meta.series, db_book)
+    modify_date |= edit_book_series(meta.series, db_book)
 
     # Add file to book
     file_size = os.path.getsize(meta.file_path)
     db_data = db.Data(db_book, meta.extension.upper()[1:], file_size, title_dir)
     db_book.data.append(db_data)
@@ -1000,10 +1015,11 @@ def create_book_on_upload(modif_date, meta):
 
     # flush content, get db_book.id available
     calibre_db.session.flush()
     return db_book, input_authors, title_dir, renamed_authors
 
+
 def file_handling_on_upload(requested_file):
     # check if file extension is correct
     if '.' in requested_file.filename:
         file_ext = requested_file.filename.rsplit('.', 1)[-1].lower()
         if file_ext not in constants.EXTENSIONS_UPLOAD and '' not in constants.EXTENSIONS_UPLOAD:
@@ -1043,32 +1059,32 @@ def move_coverfile(meta, db_book):
         flash(_(u"Failed to Move Cover File %(file)s: %(error)s", file=new_coverpath,
                 error=e),
               category="error")
 
 
-@editbook.route("/upload", methods=["POST"])
+@EditBook.route("/upload", methods=["POST"])
 @login_required_if_no_ano
 @upload_required
 def upload():
     if not config.config_uploading:
         abort(404)
     if request.method == 'POST' and 'btn-upload' in request.files:
         for requested_file in request.files.getlist("btn-upload"):
             try:
-                modif_date = False
+                modify_date = False
                 # create the function for sorting...
                 calibre_db.update_title_sort(config)
                 calibre_db.session.connection().connection.connection.create_function('uuid4', 0, lambda: str(uuid4()))
 
                 meta, error = file_handling_on_upload(requested_file)
                 if error:
                     return error
 
-                db_book, input_authors, title_dir, renamed_authors = create_book_on_upload(modif_date, meta)
+                db_book, input_authors, title_dir, renamed_authors = create_book_on_upload(modify_date, meta)
 
-                # Comments needs book id therefore only possible after flush
-                modif_date |= edit_book_comments(Markup(meta.description).unescape(), db_book)
+                # Comments need book id therefore only possible after flush
+                modify_date |= edit_book_comments(Markup(meta.description).unescape(), db_book)
 
                 book_id = db_book.id
                 title = db_book.title
                 if config.config_use_google_drive:
                     helper.upload_new_file_gdrive(book_id,
@@ -1094,16 +1110,16 @@ def upload():
                 if config.config_use_google_drive:
                     gdriveutils.updateGdriveCalibreFromLocal()
                 if error:
                     flash(error, category="error")
                 link = '<a href="{}">{}</a>'.format(url_for('web.show_book', book_id=book_id), escape(title))
-                uploadText = _(u"File %(file)s uploaded", file=link)
-                WorkerThread.add(current_user.name, TaskUpload(uploadText, escape(title)))
+                upload_text = _(u"File %(file)s uploaded", file=link)
+                WorkerThread.add(current_user.name, TaskUpload(upload_text, escape(title)))
 
                 if len(request.files.getlist("btn-upload")) < 2:
                     if current_user.role_edit() or current_user.role_admin():
-                        resp = {"location": url_for('editbook.edit_book', book_id=book_id)}
+                        resp = {"location": url_for('edit-book.edit_book', book_id=book_id)}
                         return Response(json.dumps(resp), mimetype='application/json')
                     else:
                         resp = {"location": url_for('web.show_book', book_id=book_id)}
                         return Response(json.dumps(resp), mimetype='application/json')
             except (OperationalError, IntegrityError) as e:
@@ -1111,53 +1127,55 @@ def upload():
                 log.error_or_exception("Database error: {}".format(e))
                 flash(_(u"Database error: %(error)s.", error=e.orig), category="error")
         return Response(json.dumps({"location": url_for("web.index")}), mimetype='application/json')
 
 
-@editbook.route("/admin/book/convert/<int:book_id>", methods=['POST'])
+@EditBook.route("/admin/book/convert/<int:book_id>", methods=['POST'])
 @login_required_if_no_ano
 @edit_required
 def convert_bookformat(book_id):
     # check to see if we have form fields to work with -  if not send user back
     book_format_from = request.form.get('book_format_from', None)
     book_format_to = request.form.get('book_format_to', None)
 
     if (book_format_from is None) or (book_format_to is None):
         flash(_(u"Source or destination format for conversion missing"), category="error")
-        return redirect(url_for('editbook.edit_book', book_id=book_id))
+        return redirect(url_for('edit-book.edit_book', book_id=book_id))
 
     log.info('converting: book id: %s from: %s to: %s', book_id, book_format_from, book_format_to)
     rtn = helper.convert_book_format(book_id, config.config_calibre_dir, book_format_from.upper(),
                                      book_format_to.upper(), current_user.name)
 
     if rtn is None:
         flash(_(u"Book successfully queued for converting to %(book_format)s",
-                    book_format=book_format_to),
-                    category="success")
+                book_format=book_format_to),
+              category="success")
     else:
         flash(_(u"There was an error converting this book: %(res)s", res=rtn), category="error")
-    return redirect(url_for('editbook.edit_book', book_id=book_id))
+    return redirect(url_for('edit-book.edit_book', book_id=book_id))
 
-@editbook.route("/ajax/getcustomenum/<int:c_id>")
+
+@EditBook.route("/ajax/getcustomenum/<int:c_id>")
 @login_required
 def table_get_custom_enum(c_id):
     ret = list()
-    cc = (calibre_db.session.query(db.Custom_Columns)
-              .filter(db.Custom_Columns.id == c_id)
-              .filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).one_or_none())
+    cc = (calibre_db.session.query(db.CustomColumns)
+          .filter(db.CustomColumns.id == c_id)
+          .filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).one_or_none())
     ret.append({'value': "", 'text': ""})
     for idx, en in enumerate(cc.get_display_dict()['enum_values']):
         ret.append({'value': en, 'text': en})
     return json.dumps(ret)
 
 
-@editbook.route("/ajax/editbooks/<param>", methods=['POST'])
+@EditBook.route("/ajax/editbooks/<param>", methods=['POST'])
 @login_required_if_no_ano
 @edit_required
 def edit_list_book(param):
     vals = request.form.to_dict()
     book = calibre_db.get_book(vals['pk'])
+    sort_param = ""
     # ret = ""
     try:
         if param == 'series_index':
             edit_book_series_index(vals['value'], book)
             ret = Response(json.dumps({'success': True, 'newValue': book.series_index}), mimetype='application/json')
@@ -1170,11 +1188,11 @@ def edit_list_book(param):
             ret = Response(json.dumps({'success': True, 'newValue':  ', '.join([serie.name for serie in book.series])}),
                            mimetype='application/json')
         elif param == 'publishers':
             edit_book_publisher(vals['value'], book)
             ret = Response(json.dumps({'success': True,
-                                        'newValue': ', '.join([publisher.name for publisher in book.publishers])}),
+                                       'newValue': ', '.join([publisher.name for publisher in book.publishers])}),
                            mimetype='application/json')
         elif param == 'languages':
             invalid = list()
             edit_book_languages(vals['value'], book, invalid=invalid)
             if invalid:
@@ -1184,17 +1202,17 @@ def edit_list_book(param):
             else:
                 lang_names = list()
                 for lang in book.languages:
                     lang_names.append(isoLanguages.get_language_name(get_locale(), lang.lang_code))
                 ret = Response(json.dumps({'success': True, 'newValue':  ', '.join(lang_names)}),
-                                mimetype='application/json')
+                               mimetype='application/json')
         elif param == 'author_sort':
             book.author_sort = vals['value']
             ret = Response(json.dumps({'success': True, 'newValue':  book.author_sort}),
                            mimetype='application/json')
         elif param == 'title':
-            sort = book.sort
+            sort_param = book.sort
             handle_title_on_edit(book, vals.get('value', ""))
             helper.update_dir_structure(book.id, config.config_calibre_dir)
             ret = Response(json.dumps({'success': True, 'newValue':  book.title}),
                            mimetype='application/json')
         elif param == 'sort':
@@ -1206,16 +1224,17 @@ def edit_list_book(param):
             ret = Response(json.dumps({'success': True, 'newValue':  book.comments[0].text}),
                            mimetype='application/json')
         elif param == 'authors':
             input_authors, __, renamed = handle_author_on_edit(book, vals['value'], vals.get('checkA', None) == "true")
             helper.update_dir_structure(book.id, config.config_calibre_dir, input_authors[0], renamed_author=renamed)
-            ret = Response(json.dumps({'success': True,
-                                       'newValue':  ' & '.join([author.replace('|',',') for author in input_authors])}),
-                           mimetype='application/json')
+            ret = Response(json.dumps({
+                'success': True,
+                'newValue':  ' & '.join([author.replace('|', ',') for author in input_authors])}),
+                mimetype='application/json')
         elif param == 'is_archived':
             is_archived = change_archived_books(book.id, vals['value'] == "True",
-                                                message="Book {} archivebit set to: {}".format(book.id, vals['value']))
+                                                message="Book {} archive bit set to: {}".format(book.id, vals['value']))
             if is_archived:
                 kobo_sync_status.remove_synced_book(book.id)
             return ""
         elif param == 'read_status':
             ret = helper.edit_book_read_status(book.id, vals['value'] == "True")
@@ -1236,22 +1255,22 @@ def edit_list_book(param):
         book.last_modified = datetime.utcnow()
 
         calibre_db.session.commit()
         # revert change for sort if automatic fields link is deactivated
         if param == 'title' and vals.get('checkT') == "false":
-            book.sort = sort
+            book.sort = sort_param
             calibre_db.session.commit()
     except (OperationalError, IntegrityError) as e:
         calibre_db.session.rollback()
         log.error_or_exception("Database error: {}".format(e))
         ret = Response(json.dumps({'success': False,
                                    'msg': 'Database error: {}'.format(e.orig)}),
                        mimetype='application/json')
     return ret
 
 
-@editbook.route("/ajax/sort_value/<field>/<int:bookid>")
+@EditBook.route("/ajax/sort_value/<field>/<int:bookid>")
 @login_required
 def get_sorted_entry(field, bookid):
     if field in ['title', 'authors', 'sort', 'author_sort']:
         book = calibre_db.get_filtered_book(bookid)
         if book:
@@ -1264,11 +1283,11 @@ def get_sorted_entry(field, bookid):
             if field == 'author_sort':
                 return json.dumps({'author_sort': book.author})
     return ""
 
 
-@editbook.route("/ajax/simulatemerge", methods=['POST'])
+@EditBook.route("/ajax/simulatemerge", methods=['POST'])
 @login_required
 @edit_required
 def simulate_merge_list_book():
     vals = request.get_json().get('Merge_books')
     if vals:
@@ -1280,11 +1299,11 @@ def simulate_merge_list_book():
                 from_book.append(calibre_db.get_book(book_id).title)
             return json.dumps({'to': to_book, 'from': from_book})
     return ""
 
 
-@editbook.route("/ajax/mergebooks", methods=['POST'])
+@EditBook.route("/ajax/mergebooks", methods=['POST'])
 @login_required
 @edit_required
 def merge_list_book():
     vals = request.get_json().get('Merge_books')
     to_file = list()
@@ -1293,12 +1312,13 @@ def merge_list_book():
         to_book = calibre_db.get_book(vals[0])
         vals.pop(0)
         if to_book:
             for file in to_book.data:
                 to_file.append(file.format)
-            to_name = helper.get_valid_filename(to_book.title, chars=96) + ' - ' + \
-                      helper.get_valid_filename(to_book.authors[0].name, chars=96)
+            to_name = helper.get_valid_filename(to_book.title,
+                                                chars=96) + ' - ' + helper.get_valid_filename(to_book.authors[0].name,
+                                                                                              chars=96)
             for book_id in vals:
                 from_book = calibre_db.get_book(book_id)
                 if from_book:
                     for element in from_book.data:
                         if element.format not in to_file:
@@ -1312,23 +1332,24 @@ def merge_list_book():
                             copyfile(filepath_old, filepath_new)
                             to_book.data.append(db.Data(to_book.id,
                                                         element.format,
                                                         element.uncompressed_size,
                                                         to_name))
-                    delete_book_from_table(from_book.id,"", True)
+                    delete_book_from_table(from_book.id, "", True)
                     return json.dumps({'success': True})
     return ""
 
 
-@editbook.route("/ajax/xchange", methods=['POST'])
+@EditBook.route("/ajax/xchange", methods=['POST'])
 @login_required
 @edit_required
 def table_xchange_author_title():
     vals = request.get_json().get('xchange')
+    edited_books_id = False
     if vals:
         for val in vals:
-            modif_date = False
+            modify_date = False
             book = calibre_db.get_book(val)
             authors = book.title
             book.authors = calibre_db.order_authors([book])
             author_names = []
             for authr in book.authors:
@@ -1336,19 +1357,19 @@ def table_xchange_author_title():
 
             title_change = handle_title_on_edit(book, " ".join(author_names))
             input_authors, authorchange, renamed = handle_author_on_edit(book, authors)
             if authorchange or title_change:
                 edited_books_id = book.id
-                modif_date = True
+                modify_date = True
 
             if config.config_use_google_drive:
                 gdriveutils.updateGdriveCalibreFromLocal()
 
             if edited_books_id:
                 helper.update_dir_structure(edited_books_id, config.config_calibre_dir, input_authors[0],
-                                           renamed_author=renamed)
-            if modif_date:
+                                            renamed_author=renamed)
+            if modify_date:
                 book.last_modified = datetime.utcnow()
             try:
                 calibre_db.session.commit()
             except (OperationalError, IntegrityError) as e:
                 calibre_db.session.rollback()
diff --git a/cps/epub.py b/cps/epub.py
index 563590e824..80c12c3561 100644
--- a/cps/epub.py
+++ b/cps/epub.py
@@ -51,15 +51,15 @@ def get_epub_info(tmp_file_path, original_file_name, original_file_extension):
 
     epub_zip = zipfile.ZipFile(tmp_file_path)
 
     txt = epub_zip.read('META-INF/container.xml')
     tree = etree.fromstring(txt)
-    cfname = tree.xpath('n:rootfiles/n:rootfile/@full-path', namespaces=ns)[0]
-    cf = epub_zip.read(cfname)
+    cf_name = tree.xpath('n:rootfiles/n:rootfile/@full-path', namespaces=ns)[0]
+    cf = epub_zip.read(cf_name)
     tree = etree.fromstring(cf)
 
-    coverpath = os.path.dirname(cfname)
+    cover_path = os.path.dirname(cf_name)
 
     p = tree.xpath('/pkg:package/pkg:metadata', namespaces=ns)[0]
 
     epub_metadata = {}
 
@@ -88,11 +88,11 @@ def get_epub_info(tmp_file_path, original_file_name, original_file_extension):
     lang = epub_metadata['language'].split('-', 1)[0].lower()
     epub_metadata['language'] = isoLanguages.get_lang3(lang)
 
     epub_metadata = parse_epub_series(ns, tree, epub_metadata)
 
-    cover_file = parse_epub_cover(ns, tree, epub_zip, coverpath, tmp_file_path)
+    cover_file = parse_epub_cover(ns, tree, epub_zip, cover_path, tmp_file_path)
 
     if not epub_metadata['title']:
         title = original_file_name
     else:
         title = epub_metadata['title']
@@ -112,13 +112,16 @@ def get_epub_info(tmp_file_path, original_file_name, original_file_extension):
 
 
 def parse_epub_cover(ns, tree, epub_zip, cover_path, tmp_file_path):
     cover_section = tree.xpath("/pkg:package/pkg:manifest/pkg:item[@id='cover-image']/@href", namespaces=ns)
     cover_file = None
-    if len(cover_section) > 0:
-        cover_file = _extract_cover(epub_zip, cover_section[0], cover_path, tmp_file_path)
-    else:
+    # if len(cover_section) > 0:
+    for cs in cover_section:
+        cover_file = _extract_cover(epub_zip, cs, cover_path, tmp_file_path)
+        if cover_file:
+            break
+    if not cover_file:
         meta_cover = tree.xpath("/pkg:package/pkg:metadata/pkg:meta[@name='cover']/@content", namespaces=ns)
         if len(meta_cover) > 0:
             cover_section = tree.xpath(
                 "/pkg:package/pkg:manifest/pkg:item[@id='"+meta_cover[0]+"']/@href", namespaces=ns)
             if not cover_section:
@@ -141,12 +144,11 @@ def parse_epub_cover(ns, tree, epub_zip, cover_path, tmp_file_path):
                     filename = os.path.relpath(os.path.join(os.path.dirname(os.path.join(cover_path, cover_section[0])),
                                                             img_src[0]))
                     cover_file = _extract_cover(epub_zip, filename, "", tmp_file_path)
             else:
                 cover_file = _extract_cover(epub_zip, cs, cover_path, tmp_file_path)
-            if cover_file:
-                break
+            if cover_file: break
     return cover_file
 
 
 def parse_epub_series(ns, tree, epub_metadata):
     series = tree.xpath("/pkg:package/pkg:metadata/pkg:meta[@name='calibre:series']/@content", namespaces=ns)
diff --git a/cps/helper.py b/cps/helper.py
index 74a8dbbbab..3b0c2a04cf 100644
--- a/cps/helper.py
+++ b/cps/helper.py
@@ -21,15 +21,14 @@ import os
 import io
 import mimetypes
 import re
 import shutil
 import socket
-import unicodedata
 from datetime import datetime, timedelta
 from tempfile import gettempdir
-from urllib.parse import urlparse
 import requests
+import unidecode
 
 from babel.dates import format_datetime
 from babel.units import format_unit
 from flask import send_from_directory, make_response, redirect, abort, url_for
 from flask_babel import gettext as _
@@ -39,19 +38,23 @@ from sqlalchemy.exc import InvalidRequestError, OperationalError
 from werkzeug.datastructures import Headers
 from werkzeug.security import generate_password_hash
 from markupsafe import escape
 from urllib.parse import quote
 
+
 try:
-    import unidecode
-    use_unidecode = True
+    import advocate
+    from advocate.exceptions import UnacceptableAddressException
+    use_advocate = True
 except ImportError:
-    use_unidecode = False
+    use_advocate = False
+    advocate = requests
+    UnacceptableAddressException = MissingSchema = BaseException
 
 from . import calibre_db, cli
 from .tasks.convert import TaskConvert
-from . import logger, config, get_locale, db, ub, kobo_sync_status
+from . import logger, config, get_locale, db, ub
 from . import gdriveutils as gd
 from .constants import STATIC_DIR as _STATIC_DIR
 from .subproc_wrapper import process_wait
 from .services.worker import WorkerThread, STAT_WAITING, STAT_FAIL, STAT_STARTED, STAT_FINISH_SUCCESS
 from .tasks.mail import TaskEmail
@@ -141,11 +144,11 @@ def check_send_to_kindle_with_converter(formats):
         bookformats.append({'format': 'Mobi',
                             'convert': 1,
                             'text': _('Convert %(orig)s to %(format)s and send to Kindle',
                                       orig='Epub',
                                       format='Mobi')})
-    if 'AZW3' in formats and not 'MOBI' in formats:
+    if 'AZW3' in formats and 'MOBI' not in formats:
         bookformats.append({'format': 'Mobi',
                             'convert': 2,
                             'text': _('Convert %(orig)s to %(format)s and send to Kindle',
                                       orig='Azw3',
                                       format='Mobi')})
@@ -183,15 +186,15 @@ def check_send_to_kindle(entry):
 
 
 # Check if a reader is existing for any of the book formats, if not, return empty list, otherwise return
 # list with supported formats
 def check_read_formats(entry):
-    EXTENSIONS_READER = {'TXT', 'PDF', 'EPUB', 'CBZ', 'CBT', 'CBR', 'DJVU'}
+    extensions_reader = {'TXT', 'PDF', 'EPUB', 'CBZ', 'CBT', 'CBR', 'DJVU'}
     bookformats = list()
     if len(entry.data):
         for ele in iter(entry.data):
-            if ele.format.upper() in EXTENSIONS_READER:
+            if ele.format.upper() in extensions_reader:
                 bookformats.append(ele.format.lower())
     return bookformats
 
 
 # Files are processed in the following order/priority:
@@ -211,14 +214,14 @@ def send_mail(book_id, book_format, convert, kindle_mail, calibrepath, user_id):
 
     for entry in iter(book.data):
         if entry.format.upper() == book_format.upper():
             converted_file_name = entry.name + '.' + book_format.lower()
             link = '<a href="{}">{}</a>'.format(url_for('web.show_book', book_id=book_id), escape(book.title))
-            EmailText = _(u"%(book)s send to Kindle", book=link)
+            email_text = _(u"%(book)s send to Kindle", book=link)
             WorkerThread.add(user_id, TaskEmail(_(u"Send to Kindle"), book.path, converted_file_name,
                              config.get_mail_settings(), kindle_mail,
-                             EmailText, _(u'This e-mail has been sent via Calibre-Web.')))
+                             email_text, _(u'This e-mail has been sent via Calibre-Web.')))
             return
     return _(u"The requested file could not be read. Maybe wrong permissions?")
 
 
 def get_valid_filename(value, replace_whitespace=True, chars=128):
@@ -227,19 +230,12 @@ def get_valid_filename(value, replace_whitespace=True, chars=128):
     filename. Limits num characters to 128 max.
     """
     if value[-1:] == u'.':
         value = value[:-1]+u'_'
     value = value.replace("/", "_").replace(":", "_").strip('\0')
-    if use_unidecode:
-        if config.config_unicode_filename:
-            value = (unidecode.unidecode(value))
-    else:
-        value = value.replace(u'§', u'SS')
-        value = value.replace(u'ß', u'ss')
-        value = unicodedata.normalize('NFKD', value)
-        re_slugify = re.compile(r'[\W\s-]', re.UNICODE)
-        value = re_slugify.sub('', value)
+    if config.config_unicode_filename:
+        value = (unidecode.unidecode(value))
     if replace_whitespace:
         #  *+:\"/<>? are replaced by _
         value = re.sub(r'[*+:\\\"/<>?]+', u'_', value, flags=re.U)
         # pipe has to be replaced with comma
         value = re.sub(r'[|]+', u',', value, flags=re.U)
@@ -264,10 +260,11 @@ def split_authors(values):
                 authors_list.append(author.strip())
     return authors_list
 
 
 def get_sorted_author(value):
+    value2 = None
     try:
         if ',' not in value:
             regexes = [r"^(JR|SR)\.?$", r"^I{1,3}\.?$", r"^IV\.?$"]
             combined = "(" + ")|(".join(regexes) + ")"
             value = value.split(" ")
@@ -288,10 +285,11 @@ def get_sorted_author(value):
             value2 = value[0]
         else:
             value2 = value
     return value2
 
+
 def edit_book_read_status(book_id, read_status=None):
     if not config.config_read_column:
         book = ub.session.query(ub.ReadBook).filter(and_(ub.ReadBook.user_id == int(current_user.id),
                                                          ub.ReadBook.book_id == book_id)).first()
         if book:
@@ -301,13 +299,13 @@ def edit_book_read_status(book_id, read_status=None):
                 else:
                     book.read_status = ub.ReadBook.STATUS_FINISHED
             else:
                 book.read_status = ub.ReadBook.STATUS_FINISHED if read_status else ub.ReadBook.STATUS_UNREAD
         else:
-            readBook = ub.ReadBook(user_id=current_user.id, book_id = book_id)
-            readBook.read_status = ub.ReadBook.STATUS_FINISHED
-            book = readBook
+            read_book = ub.ReadBook(user_id=current_user.id, book_id=book_id)
+            read_book.read_status = ub.ReadBook.STATUS_FINISHED
+            book = read_book
         if not book.kobo_reading_state:
             kobo_reading_state = ub.KoboReadingState(user_id=current_user.id, book_id=book_id)
             kobo_reading_state.current_bookmark = ub.KoboBookmark()
             kobo_reading_state.statistics = ub.KoboStatistics()
             book.kobo_reading_state = kobo_reading_state
@@ -330,16 +328,17 @@ def edit_book_read_status(book_id, read_status=None):
                 calibre_db.session.add(new_cc)
                 calibre_db.session.commit()
         except (KeyError, AttributeError):
             log.error(u"Custom Column No.%d is not existing in calibre database", config.config_read_column)
             return "Custom Column No.{} is not existing in calibre database".format(config.config_read_column)
-        except (OperationalError, InvalidRequestError) as e:
+        except (OperationalError, InvalidRequestError) as ex:
             calibre_db.session.rollback()
-            log.error(u"Read status could not set: {}".format(e))
-            return _("Read status could not set: {}".format(e.orig))
+            log.error(u"Read status could not set: {}".format(ex))
+            return _("Read status could not set: {}".format(ex.orig))
     return ""
 
+
 # Deletes a book fro the local filestorage, returns True if deleting is successfull, otherwise false
 def delete_book_file(book, calibrepath, book_format=None):
     # check that path is 2 elements deep, check that target path has no subfolders
     if book.path.count('/') == 1:
         path = os.path.join(calibrepath, book.path)
@@ -359,19 +358,19 @@ def delete_book_file(book, calibrepath, book_format=None):
                                         book.path, folders))
                             return True, _("Deleting bookfolder for book %(id)s failed, path has subfolders: %(path)s",
                                            id=book.id,
                                            path=book.path)
                     shutil.rmtree(path)
-                except (IOError, OSError) as e:
-                    log.error("Deleting book %s failed: %s", book.id, e)
-                    return False, _("Deleting book %(id)s failed: %(message)s", id=book.id, message=e)
+                except (IOError, OSError) as ex:
+                    log.error("Deleting book %s failed: %s", book.id, ex)
+                    return False, _("Deleting book %(id)s failed: %(message)s", id=book.id, message=ex)
                 authorpath = os.path.join(calibrepath, os.path.split(book.path)[0])
                 if not os.listdir(authorpath):
                     try:
                         shutil.rmtree(authorpath)
-                    except (IOError, OSError) as e:
-                        log.error("Deleting authorpath for book %s failed: %s", book.id, e)
+                    except (IOError, OSError) as ex:
+                        log.error("Deleting authorpath for book %s failed: %s", book.id, ex)
                 return True, None
 
     log.error("Deleting book %s from database only, book path in database not valid: %s",
               book.id, book.path)
     return True, _("Deleting book %(id)s from database only, book path in database not valid: %(path)s",
@@ -393,25 +392,25 @@ def clean_author_database(renamed_author, calibre_path="", local_book=None, gdri
                 new_author = calibre_db.session.query(db.Authors).filter(db.Authors.name == r).first()
                 all_new_authordir = get_valid_filename(new_author.name, chars=96)
                 all_titledir = book.path.split('/')[1]
                 all_new_path = os.path.join(calibre_path, all_new_authordir, all_titledir)
                 all_new_name = get_valid_filename(book.title, chars=42) + ' - ' \
-                               + get_valid_filename(new_author.name, chars=42)
+                    + get_valid_filename(new_author.name, chars=42)
                 # change location in database to new author/title path
                 book.path = os.path.join(all_new_authordir, all_titledir).replace('\\', '/')
                 for file_format in book.data:
                     if not gdrive:
                         shutil.move(os.path.normcase(os.path.join(all_new_path,
                                                                   file_format.name + '.' + file_format.format.lower())),
-                            os.path.normcase(os.path.join(all_new_path,
-                                                          all_new_name + '.' + file_format.format.lower())))
+                                    os.path.normcase(os.path.join(all_new_path,
+                                                                  all_new_name + '.' + file_format.format.lower())))
                     else:
-                        gFile = gd.getFileFromEbooksFolder(all_new_path,
-                                                           file_format.name + '.' + file_format.format.lower())
-                        if gFile:
-                            gd.moveGdriveFileRemote(gFile, all_new_name + u'.' + file_format.format.lower())
-                            gd.updateDatabaseOnEdit(gFile['id'], all_new_name + u'.' + file_format.format.lower())
+                        g_file = gd.getFileFromEbooksFolder(all_new_path,
+                                                            file_format.name + '.' + file_format.format.lower())
+                        if g_file:
+                            gd.moveGdriveFileRemote(g_file, all_new_name + u'.' + file_format.format.lower())
+                            gd.updateDatabaseOnEdit(g_file['id'], all_new_name + u'.' + file_format.format.lower())
                         else:
                             log.error("File {} not found on gdrive"
                                       .format(all_new_path, file_format.name + '.' + file_format.format.lower()))
                     file_format.name = all_new_name
 
@@ -424,28 +423,29 @@ def rename_all_authors(first_author, renamed_author, calibre_path="", localbook=
         for r in renamed_author:
             new_author = calibre_db.session.query(db.Authors).filter(db.Authors.name == r).first()
             old_author_dir = get_valid_filename(r, chars=96)
             new_author_rename_dir = get_valid_filename(new_author.name, chars=96)
             if gdrive:
-                gFile = gd.getFileFromEbooksFolder(None, old_author_dir)
-                if gFile:
-                    gd.moveGdriveFolderRemote(gFile, new_author_rename_dir)
+                g_file = gd.getFileFromEbooksFolder(None, old_author_dir)
+                if g_file:
+                    gd.moveGdriveFolderRemote(g_file, new_author_rename_dir)
             else:
                 if os.path.isdir(os.path.join(calibre_path, old_author_dir)):
                     try:
                         old_author_path = os.path.join(calibre_path, old_author_dir)
                         new_author_path = os.path.join(calibre_path, new_author_rename_dir)
                         shutil.move(os.path.normcase(old_author_path), os.path.normcase(new_author_path))
-                    except (OSError) as ex:
+                    except OSError as ex:
                         log.error("Rename author from: %s to %s: %s", old_author_path, new_author_path, ex)
                         log.debug(ex, exc_info=True)
                         return _("Rename author from: '%(src)s' to '%(dest)s' failed with error: %(error)s",
                                  src=old_author_path, dest=new_author_path, error=str(ex))
     else:
         new_authordir = get_valid_filename(localbook.authors[0].name, chars=96)
     return new_authordir
 
+
 # Moves files in file storage during author/title rename, or from temp dir to file storage
 def update_dir_structure_file(book_id, calibre_path, first_author, original_filepath, db_filename, renamed_author):
     # get book database entry from id, if original path overwrite source with original_filepath
     localbook = calibre_db.get_book(book_id)
     if original_filepath:
@@ -481,15 +481,13 @@ def update_dir_structure_file(book_id, calibre_path, first_author, original_file
     # Rename all files from old names to new names
     return rename_files_on_change(first_author, renamed_author, localbook, original_filepath, path, calibre_path)
 
 
 def upload_new_file_gdrive(book_id, first_author, renamed_author, title, title_dir, original_filepath, filename_ext):
-    error = False
     book = calibre_db.get_book(book_id)
     file_name = get_valid_filename(title, chars=42) + ' - ' + \
-                get_valid_filename(first_author, chars=42) + \
-                filename_ext
+        get_valid_filename(first_author, chars=42) + filename_ext
     rename_all_authors(first_author, renamed_author, gdrive=True)
     gdrive_path = os.path.join(get_valid_filename(first_author, chars=96),
                                title_dir + " (" + str(book_id) + ")")
     book.path = gdrive_path.replace("\\", "/")
     gd.uploadFileToEbooksFolder(os.path.join(gdrive_path, file_name).replace("\\", "/"), original_filepath)
@@ -503,24 +501,24 @@ def update_dir_structure_gdrive(book_id, first_author, renamed_author):
     titledir = book.path.split('/')[1]
     new_authordir = rename_all_authors(first_author, renamed_author, gdrive=True)
     new_titledir = get_valid_filename(book.title, chars=96) + u" (" + str(book_id) + u")"
 
     if titledir != new_titledir:
-        gFile = gd.getFileFromEbooksFolder(os.path.dirname(book.path), titledir)
-        if gFile:
-            gd.moveGdriveFileRemote(gFile, new_titledir)
+        g_file = gd.getFileFromEbooksFolder(os.path.dirname(book.path), titledir)
+        if g_file:
+            gd.moveGdriveFileRemote(g_file, new_titledir)
             book.path = book.path.split('/')[0] + u'/' + new_titledir
-            gd.updateDatabaseOnEdit(gFile['id'], book.path)     # only child folder affected
+            gd.updateDatabaseOnEdit(g_file['id'], book.path)     # only child folder affected
         else:
             return _(u'File %(file)s not found on Google Drive', file=book.path)  # file not found
 
     if authordir != new_authordir and authordir not in renamed_author:
-        gFile = gd.getFileFromEbooksFolder(os.path.dirname(book.path), new_titledir)
-        if gFile:
-            gd.moveGdriveFolderRemote(gFile, new_authordir)
+        g_file = gd.getFileFromEbooksFolder(os.path.dirname(book.path), new_titledir)
+        if g_file:
+            gd.moveGdriveFolderRemote(g_file, new_authordir)
             book.path = new_authordir + u'/' + book.path.split('/')[1]
-            gd.updateDatabaseOnEdit(gFile['id'], book.path)
+            gd.updateDatabaseOnEdit(g_file['id'], book.path)
         else:
             return _(u'File %(file)s not found on Google Drive', file=authordir)  # file not found
 
     # change location in database to new author/title path
     book.path = os.path.join(new_authordir, new_titledir).replace('\\', '/')
@@ -540,19 +538,19 @@ def move_files_on_change(calibre_path, new_authordir, new_titledir, localbook, d
             # Check new path is not valid path
             if not os.path.exists(new_path):
                 # move original path to new path
                 log.debug("Moving title: %s to %s", path, new_path)
                 shutil.move(os.path.normcase(path), os.path.normcase(new_path))
-            else: # path is valid copy only files to new location (merge)
+            else:  # path is valid copy only files to new location (merge)
                 log.info("Moving title: %s into existing: %s", path, new_path)
                 # Take all files and subfolder from old path (strange command)
                 for dir_name, __, file_list in os.walk(path):
                     for file in file_list:
                         shutil.move(os.path.normcase(os.path.join(dir_name, file)),
-                                        os.path.normcase(os.path.join(new_path + dir_name[len(path):], file)))
+                                    os.path.normcase(os.path.join(new_path + dir_name[len(path):], file)))
         # change location in database to new author/title path
-        localbook.path = os.path.join(new_authordir, new_titledir).replace('\\','/')
+        localbook.path = os.path.join(new_authordir, new_titledir).replace('\\', '/')
     except OSError as ex:
         log.error("Rename title from: %s to %s: %s", path, new_path, ex)
         log.debug(ex, exc_info=True)
         return _("Rename title from: '%(src)s' to '%(dest)s' failed with error: %(error)s",
                  src=path, dest=new_path, error=str(ex))
@@ -585,16 +583,16 @@ def delete_book_gdrive(book, book_format):
     if book_format:
         name = ''
         for entry in book.data:
             if entry.format.upper() == book_format:
                 name = entry.name + '.' + book_format
-        gFile = gd.getFileFromEbooksFolder(book.path, name)
+        g_file = gd.getFileFromEbooksFolder(book.path, name)
     else:
-        gFile = gd.getFileFromEbooksFolder(os.path.dirname(book.path), book.path.split('/')[1])
-    if gFile:
-        gd.deleteDatabaseEntry(gFile['id'])
-        gFile.Trash()
+        g_file = gd.getFileFromEbooksFolder(os.path.dirname(book.path), book.path.split('/')[1])
+    if g_file:
+        gd.deleteDatabaseEntry(g_file['id'])
+        g_file.Trash()
     else:
         error = _(u'Book path %(path)s not found on Google Drive', path=book.path)  # file not found
 
     return error is None, error
 
@@ -622,16 +620,17 @@ def generate_random_password():
     return "".join(s[c % len(s)] for c in os.urandom(passlen))
 
 
 def uniq(inpt):
     output = []
-    inpt = [ " ".join(inp.split()) for inp in inpt]
+    inpt = [" ".join(inp.split()) for inp in inpt]
     for x in inpt:
         if x not in output:
             output.append(x)
     return output
 
+
 def check_email(email):
     email = valid_email(email)
     if ub.session.query(ub.User).filter(func.lower(ub.User.email) == email.lower()).first():
         log.error(u"Found an existing account for this e-mail address")
         raise Exception(_(u"Found an existing account for this e-mail address"))
@@ -640,11 +639,11 @@ def check_email(email):
 
 def check_username(username):
     username = username.strip()
     if ub.session.query(ub.User).filter(func.lower(ub.User.name) == username.lower()).scalar():
         log.error(u"This username is already taken")
-        raise Exception (_(u"This username is already taken"))
+        raise Exception(_(u"This username is already taken"))
     return username
 
 
 def valid_email(email):
     email = email.strip()
@@ -726,17 +725,17 @@ def get_book_cover_internal(book, use_generic_cover_on_failure):
 
 
 # saves book cover from url
 def save_cover_from_url(url, book_path):
     try:
-        if not cli.allow_localhost:
-            # 127.0.x.x, localhost, [::1], [::ffff:7f00:1]
-            ip = socket.getaddrinfo(urlparse(url).hostname, 0)[0][4][0]
-            if ip.startswith("127.") or ip.startswith('::ffff:7f') or ip == "::1" or ip == "0.0.0.0" or ip == "::":
-                log.error("Localhost was accessed for cover upload")
-                return False, _("You are not allowed to access localhost for cover uploads")
-        img = requests.get(url, timeout=(10, 200), allow_redirects=False)      # ToDo: Error Handling
+        if cli.allow_localhost:
+            img = requests.get(url, timeout=(10, 200), allow_redirects=False)  # ToDo: Error Handling
+        elif use_advocate:
+            img = advocate.get(url, timeout=(10, 200), allow_redirects=False)      # ToDo: Error Handling
+        else:
+            log.error("python modul advocate is not installed but is needed")
+            return False, _("Python modul 'advocate' is not installed but is needed for cover downloads")
         img.raise_for_status()
         return save_cover(img, book_path)
     except (socket.gaierror,
             requests.exceptions.HTTPError,
             requests.exceptions.ConnectionError,
@@ -744,10 +743,13 @@ def save_cover_from_url(url, book_path):
         log.info(u'Cover Download Error %s', ex)
         return False, _("Error Downloading Cover")
     except MissingDelegateError as ex:
         log.info(u'File Format Error %s', ex)
         return False, _("Cover Format Error")
+    except UnacceptableAddressException:
+        log.error("Localhost was accessed for cover upload")
+        return False, _("You are not allowed to access localhost for cover uploads")
 
 
 def save_cover_from_filestorage(filepath, saved_filename, img):
     # check if file path exists, otherwise create it, copy file to calibre path and delete temp file
     if not os.path.exists(filepath):
@@ -806,11 +808,11 @@ def save_cover(img, book_path):
 
         if not os.path.isdir(tmp_dir):
             os.mkdir(tmp_dir)
         ret, message = save_cover_from_filestorage(tmp_dir, "uploaded_cover.jpg", img)
         if ret is True:
-            gd.uploadFileToEbooksFolder(os.path.join(book_path, 'cover.jpg').replace("\\","/"),
+            gd.uploadFileToEbooksFolder(os.path.join(book_path, 'cover.jpg').replace("\\", "/"),
                                         os.path.join(tmp_dir, "uploaded_cover.jpg"))
             log.info("Cover is saved on Google Drive")
             return True, None
         else:
             return False, message
@@ -818,13 +820,13 @@ def save_cover(img, book_path):
         return save_cover_from_filestorage(os.path.join(config.config_calibre_dir, book_path), "cover.jpg", img)
 
 
 def do_download_file(book, book_format, client, data, headers):
     if config.config_use_google_drive:
-        #startTime = time.time()
+        # startTime = time.time()
         df = gd.getFileFromEbooksFolder(book.path, data.name + "." + book_format)
-        #log.debug('%s', time.time() - startTime)
+        # log.debug('%s', time.time() - startTime)
         if df:
             return gd.do_gdrive_download(df, headers)
         else:
             abort(404)
     else:
@@ -844,20 +846,20 @@ def do_download_file(book, book_format, client, data, headers):
         return response
 
 ##################################
 
 
-def check_unrar(unrarLocation):
-    if not unrarLocation:
+def check_unrar(unrar_location):
+    if not unrar_location:
         return
 
-    if not os.path.exists(unrarLocation):
+    if not os.path.exists(unrar_location):
         return _('Unrar binary file not found')
 
     try:
-        unrarLocation = [unrarLocation]
-        value = process_wait(unrarLocation, pattern='UNRAR (.*) freeware')
+        unrar_location = [unrar_location]
+        value = process_wait(unrar_location, pattern='UNRAR (.*) freeware')
         if value:
             version = value.group(1)
             log.debug("unrar version %s", version)
 
     except (OSError, UnicodeDecodeError) as err:
@@ -880,23 +882,23 @@ def json_serial(obj):
     raise TypeError("Type %s not serializable" % type(obj))
 
 
 # helper function for displaying the runtime of tasks
 def format_runtime(runtime):
-    retVal = ""
+    ret_val = ""
     if runtime.days:
-        retVal = format_unit(runtime.days, 'duration-day', length="long", locale=get_locale()) + ', '
+        ret_val = format_unit(runtime.days, 'duration-day', length="long", locale=get_locale()) + ', '
     mins, seconds = divmod(runtime.seconds, 60)
     hours, minutes = divmod(mins, 60)
     # ToDo: locale.number_symbols._data['timeSeparator'] -> localize time separator ?
     if hours:
-        retVal += '{:d}:{:02d}:{:02d}s'.format(hours, minutes, seconds)
+        ret_val += '{:d}:{:02d}:{:02d}s'.format(hours, minutes, seconds)
     elif minutes:
-        retVal += '{:2d}:{:02d}s'.format(minutes, seconds)
+        ret_val += '{:2d}:{:02d}s'.format(minutes, seconds)
     else:
-        retVal += '{:2d}s'.format(seconds)
-    return retVal
+        ret_val += '{:2d}s'.format(seconds)
+    return ret_val
 
 
 # helper function to apply localize status information in tasklist entries
 def render_task_status(tasklist):
     renderedtasklist = list()
@@ -949,12 +951,12 @@ def check_valid_domain(domain_text):
     result = ub.session.query(ub.Registration).from_statement(text(sql)).params(domain=domain_text).all()
     return not len(result)
 
 
 def get_cc_columns(filter_config_custom_read=False):
-    tmpcc = calibre_db.session.query(db.Custom_Columns)\
-        .filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).all()
+    tmpcc = calibre_db.session.query(db.CustomColumns)\
+        .filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).all()
     cc = []
     r = None
     if config.config_columns_to_ignore:
         r = re.compile(config.config_columns_to_ignore)
 
@@ -969,10 +971,11 @@ def get_cc_columns(filter_config_custom_read=False):
 
 
 def get_download_link(book_id, book_format, client):
     book_format = book_format.split(".")[0]
     book = calibre_db.get_filtered_book(book_id, allow_show_archived=True)
+    data1= ""
     if book:
         data1 = calibre_db.get_book_format(book.id, book_format.upper())
     else:
         log.error("Book id {} not found for downloading".format(book_id))
         abort(404)
diff --git a/cps/opds.py b/cps/opds.py
index 1f9b9db4b4..c0637c6bac 100644
--- a/cps/opds.py
+++ b/cps/opds.py
@@ -26,11 +26,10 @@ from functools import wraps
 
 from flask import Blueprint, request, render_template, Response, g, make_response, abort
 from flask_login import current_user
 from sqlalchemy.sql.expression import func, text, or_, and_, true
 from werkzeug.security import check_password_hash
-from tornado.httputil import HTTPServerRequest
 from . import constants, logger, config, db, calibre_db, ub, services, get_locale, isoLanguages
 from .helper import get_download_link, get_book_cover
 from .pagination import Pagination
 from .web import render_read_books
 from .usermanagement import load_user_from_request
@@ -97,30 +96,11 @@ def feed_normal_search():
 
 
 @opds.route("/opds/books")
 @requires_basic_auth_if_no_ano
 def feed_booksindex():
-    shift = 0
-    off = int(request.args.get("offset") or 0)
-    entries = calibre_db.session.query(func.upper(func.substr(db.Books.sort, 1, 1)).label('id'))\
-        .filter(calibre_db.common_filters()).group_by(func.upper(func.substr(db.Books.sort, 1, 1))).all()
-
-    elements = []
-    if off == 0:
-        elements.append({'id': "00", 'name':_("All")})
-        shift = 1
-    for entry in entries[
-                 off + shift - 1:
-                 int(off + int(config.config_books_per_page) - shift)]:
-        elements.append({'id': entry.id, 'name': entry.id})
-
-    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
-                            len(entries) + 1)
-    return render_xml_template('feed.xml',
-                               letterelements=elements,
-                               folder='opds.feed_letter_books',
-                               pagination=pagination)
+    return render_element_index(db.Books.sort, None, 'opds.feed_letter_books')
 
 
 @opds.route("/opds/books/letter/<book_id>")
 @requires_basic_auth_if_no_ano
 def feed_letter_books(book_id):
@@ -169,47 +149,27 @@ def feed_hot():
     all_books = ub.session.query(ub.Downloads, func.count(ub.Downloads.book_id)).order_by(
         func.count(ub.Downloads.book_id).desc()).group_by(ub.Downloads.book_id)
     hot_books = all_books.offset(off).limit(config.config_books_per_page)
     entries = list()
     for book in hot_books:
-        downloadBook = calibre_db.get_book(book.Downloads.book_id)
-        if downloadBook:
+        download_book = calibre_db.get_book(book.Downloads.book_id)
+        if download_book:
             entries.append(
                 calibre_db.get_filtered_book(book.Downloads.book_id)
             )
         else:
             ub.delete_download(book.Downloads.book_id)
-    numBooks = entries.__len__()
+    num_books = entries.__len__()
     pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1),
-                            config.config_books_per_page, numBooks)
+                            config.config_books_per_page, num_books)
     return render_xml_template('feed.xml', entries=entries, pagination=pagination)
 
 
 @opds.route("/opds/author")
 @requires_basic_auth_if_no_ano
 def feed_authorindex():
-    shift = 0
-    off = int(request.args.get("offset") or 0)
-    entries = calibre_db.session.query(func.upper(func.substr(db.Authors.sort, 1, 1)).label('id'))\
-        .join(db.books_authors_link).join(db.Books).filter(calibre_db.common_filters())\
-        .group_by(func.upper(func.substr(db.Authors.sort, 1, 1))).all()
-
-    elements = []
-    if off == 0:
-        elements.append({'id': "00", 'name':_("All")})
-        shift = 1
-    for entry in entries[
-                 off + shift - 1:
-                 int(off + int(config.config_books_per_page) - shift)]:
-        elements.append({'id': entry.id, 'name': entry.id})
-
-    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
-                            len(entries) + 1)
-    return render_xml_template('feed.xml',
-                               letterelements=elements,
-                               folder='opds.feed_letter_author',
-                               pagination=pagination)
+    return render_element_index(db.Authors.sort, db.books_authors_link, 'opds.feed_letter_author')
 
 
 @opds.route("/opds/author/letter/<book_id>")
 @requires_basic_auth_if_no_ano
 def feed_letter_author(book_id):
@@ -226,16 +186,11 @@ def feed_letter_author(book_id):
 
 
 @opds.route("/opds/author/<int:book_id>")
 @requires_basic_auth_if_no_ano
 def feed_author(book_id):
-    off = request.args.get("offset") or 0
-    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
-                                                        db.Books,
-                                                        db.Books.authors.any(db.Authors.id == book_id),
-                                                        [db.Books.timestamp.desc()])
-    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
+    return render_xml_dataset(db.Authors, book_id)
 
 
 @opds.route("/opds/publisher")
 @requires_basic_auth_if_no_ano
 def feed_publisherindex():
@@ -252,41 +207,18 @@ def feed_publisherindex():
 
 
 @opds.route("/opds/publisher/<int:book_id>")
 @requires_basic_auth_if_no_ano
 def feed_publisher(book_id):
-    off = request.args.get("offset") or 0
-    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
-                                                        db.Books,
-                                                        db.Books.publishers.any(db.Publishers.id == book_id),
-                                                        [db.Books.timestamp.desc()])
-    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
+    return render_xml_dataset(db.Publishers, book_id)
 
 
 @opds.route("/opds/category")
 @requires_basic_auth_if_no_ano
 def feed_categoryindex():
-    shift = 0
-    off = int(request.args.get("offset") or 0)
-    entries = calibre_db.session.query(func.upper(func.substr(db.Tags.name, 1, 1)).label('id'))\
-        .join(db.books_tags_link).join(db.Books).filter(calibre_db.common_filters())\
-        .group_by(func.upper(func.substr(db.Tags.name, 1, 1))).all()
-    elements = []
-    if off == 0:
-        elements.append({'id': "00", 'name':_("All")})
-        shift = 1
-    for entry in entries[
-                 off + shift - 1:
-                 int(off + int(config.config_books_per_page) - shift)]:
-        elements.append({'id': entry.id, 'name': entry.id})
+    return render_element_index(db.Tags.name, db.books_tags_link, 'opds.feed_letter_category')
 
-    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
-                            len(entries) + 1)
-    return render_xml_template('feed.xml',
-                               letterelements=elements,
-                               folder='opds.feed_letter_category',
-                               pagination=pagination)
 
 @opds.route("/opds/category/letter/<book_id>")
 @requires_basic_auth_if_no_ano
 def feed_letter_category(book_id):
     off = request.args.get("offset") or 0
@@ -304,40 +236,18 @@ def feed_letter_category(book_id):
 
 
 @opds.route("/opds/category/<int:book_id>")
 @requires_basic_auth_if_no_ano
 def feed_category(book_id):
-    off = request.args.get("offset") or 0
-    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
-                                                        db.Books,
-                                                        db.Books.tags.any(db.Tags.id == book_id),
-                                                        [db.Books.timestamp.desc()])
-    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
+    return render_xml_dataset(db.Tags, book_id)
 
 
 @opds.route("/opds/series")
 @requires_basic_auth_if_no_ano
 def feed_seriesindex():
-    shift = 0
-    off = int(request.args.get("offset") or 0)
-    entries = calibre_db.session.query(func.upper(func.substr(db.Series.sort, 1, 1)).label('id'))\
-        .join(db.books_series_link).join(db.Books).filter(calibre_db.common_filters())\
-        .group_by(func.upper(func.substr(db.Series.sort, 1, 1))).all()
-    elements = []
-    if off == 0:
-        elements.append({'id': "00", 'name':_("All")})
-        shift = 1
-    for entry in entries[
-                 off + shift - 1:
-                 int(off + int(config.config_books_per_page) - shift)]:
-        elements.append({'id': entry.id, 'name': entry.id})
-    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
-                            len(entries) + 1)
-    return render_xml_template('feed.xml',
-                               letterelements=elements,
-                               folder='opds.feed_letter_series',
-                               pagination=pagination)
+    return render_element_index(db.Series.sort, db.books_series_link, 'opds.feed_letter_series')
+
 
 @opds.route("/opds/series/letter/<book_id>")
 @requires_basic_auth_if_no_ano
 def feed_letter_series(book_id):
     off = request.args.get("offset") or 0
@@ -368,11 +278,11 @@ def feed_series(book_id):
 @opds.route("/opds/ratings")
 @requires_basic_auth_if_no_ano
 def feed_ratingindex():
     off = request.args.get("offset") or 0
     entries = calibre_db.session.query(db.Ratings, func.count('books_ratings_link.book').label('count'),
-                               (db.Ratings.rating / 2).label('name')) \
+                                       (db.Ratings.rating / 2).label('name')) \
         .join(db.books_ratings_link)\
         .join(db.Books)\
         .filter(calibre_db.common_filters()) \
         .group_by(text('books_ratings_link.rating'))\
         .order_by(db.Ratings.rating).all()
@@ -386,16 +296,11 @@ def feed_ratingindex():
 
 
 @opds.route("/opds/ratings/<book_id>")
 @requires_basic_auth_if_no_ano
 def feed_ratings(book_id):
-    off = request.args.get("offset") or 0
-    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
-                                                        db.Books,
-                                                        db.Books.ratings.any(db.Ratings.id == book_id),
-                                                        [db.Books.timestamp.desc()])
-    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
+    return render_xml_dataset(db.Tags, book_id)
 
 
 @opds.route("/opds/formats")
 @requires_basic_auth_if_no_ano
 def feed_formatindex():
@@ -489,11 +394,11 @@ def feed_shelf(book_id):
 
 @opds.route("/opds/download/<book_id>/<book_format>/")
 @requires_basic_auth_if_no_ano
 def opds_download_link(book_id, book_format):
     # I gave up with this: With enabled ldap login, the user doesn't get logged in, therefore it's always guest
-    # workaround, loading the user from the request and checking it's download rights here
+    # workaround, loading the user from the request and checking its download rights here
     # in case of anonymous browsing user is None
     user = load_user_from_request(request) or current_user
     if not user.role_download():
         return abort(403)
     if "Kobo" in request.headers.get('User-Agent'):
@@ -515,10 +420,35 @@ def get_metadata_calibre_companion(uuid, library):
         return response
     else:
         return ""
 
 
+@opds.route("/opds/thumb_240_240/<book_id>")
+@opds.route("/opds/cover_240_240/<book_id>")
+@opds.route("/opds/cover_90_90/<book_id>")
+@opds.route("/opds/cover/<book_id>")
+@requires_basic_auth_if_no_ano
+def feed_get_cover(book_id):
+    return get_book_cover(book_id)
+
+
+@opds.route("/opds/readbooks")
+@requires_basic_auth_if_no_ano
+def feed_read_books():
+    off = request.args.get("offset") or 0
+    result, pagination = render_read_books(int(off) / (int(config.config_books_per_page)) + 1, True, True)
+    return render_xml_template('feed.xml', entries=result, pagination=pagination)
+
+
+@opds.route("/opds/unreadbooks")
+@requires_basic_auth_if_no_ano
+def feed_unread_books():
+    off = request.args.get("offset") or 0
+    result, pagination = render_read_books(int(off) / (int(config.config_books_per_page)) + 1, False, True)
+    return render_xml_template('feed.xml', entries=result, pagination=pagination)
+
+
 def feed_search(term):
     if term:
         entries, __, ___ = calibre_db.get_search_results(term, config_read_column=config.config_read_column)
         entries_count = len(entries) if len(entries) > 0 else 1
         pagination = Pagination(1, entries_count, entries_count)
@@ -536,12 +466,12 @@ def check_auth(username, password):
     user = ub.session.query(ub.User).filter(func.lower(ub.User.name) ==
                                             username.decode('utf-8').lower()).first()
     if bool(user and check_password_hash(str(user.password), password)):
         return True
     else:
-        ip_Address = request.headers.get('X-Forwarded-For', request.remote_addr)
-        log.warning('OPDS Login failed for user "%s" IP-address: %s', username.decode('utf-8'), ip_Address)
+        ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
+        log.warning('OPDS Login failed for user "%s" IP-address: %s', username.decode('utf-8'), ip_address)
         return False
 
 
 def authenticate():
     return Response(
@@ -557,28 +487,35 @@ def render_xml_template(*args, **kwargs):
     response = make_response(xml)
     response.headers["Content-Type"] = "application/atom+xml; charset=utf-8"
     return response
 
 
-@opds.route("/opds/thumb_240_240/<book_id>")
-@opds.route("/opds/cover_240_240/<book_id>")
-@opds.route("/opds/cover_90_90/<book_id>")
-@opds.route("/opds/cover/<book_id>")
-@requires_basic_auth_if_no_ano
-def feed_get_cover(book_id):
-    return get_book_cover(book_id)
-
-
-@opds.route("/opds/readbooks")
-@requires_basic_auth_if_no_ano
-def feed_read_books():
+def render_xml_dataset(data_table, book_id):
     off = request.args.get("offset") or 0
-    result, pagination = render_read_books(int(off) / (int(config.config_books_per_page)) + 1, True, True)
-    return render_xml_template('feed.xml', entries=result, pagination=pagination)
+    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
+                                                        db.Books,
+                                                        data_table.any(data_table.id == book_id),
+                                                        [db.Books.timestamp.desc()])
+    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
 
 
-@opds.route("/opds/unreadbooks")
-@requires_basic_auth_if_no_ano
-def feed_unread_books():
-    off = request.args.get("offset") or 0
-    result, pagination = render_read_books(int(off) / (int(config.config_books_per_page)) + 1, False, True)
-    return render_xml_template('feed.xml', entries=result, pagination=pagination)
+def render_element_index(database_column, linked_table, folder):
+    shift = 0
+    off = int(request.args.get("offset") or 0)
+    entries = calibre_db.session.query(func.upper(func.substr(database_column, 1, 1)).label('id'))
+    if linked_table:
+        entries = entries.join(linked_table).join(db.Books)
+    entries = entries.filter(calibre_db.common_filters()).group_by(func.upper(func.substr(database_column, 1, 1))).all()
+    elements = []
+    if off == 0:
+        elements.append({'id': "00", 'name': _("All")})
+        shift = 1
+    for entry in entries[
+                 off + shift - 1:
+                 int(off + int(config.config_books_per_page) - shift)]:
+        elements.append({'id': entry.id, 'name': entry.id})
+    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
+                            len(entries) + 1)
+    return render_xml_template('feed.xml',
+                               letterelements=elements,
+                               folder=folder,
+                               pagination=pagination)
diff --git a/cps/pagination.py b/cps/pagination.py
index 7a9bfb702a..bda9f4c8cf 100644
--- a/cps/pagination.py
+++ b/cps/pagination.py
@@ -55,14 +55,14 @@ class Pagination(object):
 
     @property
     def has_next(self):
         return self.page < self.pages
 
-    # right_edge: last right_edges count of all pages are shown as number, means, if 10 pages are paginated -> 9,10 shwn
-    # left_edge: first left_edges count of all pages are shown as number                                    -> 1,2 shwn
-    # left_current: left_current count below current page are shown as number, means if current page 5      -> 3,4 shwn
-    # left_current: right_current count above current page are shown as number, means if current page 5     -> 6,7 shwn
+    # right_edge: last right_edges count of all pages are shown as number, means, if 10 pages are paginated -> 9,10 shown
+    # left_edge: first left_edges count of all pages are shown as number                                    -> 1,2 shown
+    # left_current: left_current count below current page are shown as number, means if current page 5      -> 3,4 shown
+    # left_current: right_current count above current page are shown as number, means if current page 5     -> 6,7 shown
     def iter_pages(self, left_edge=2, left_current=2,
                    right_current=4, right_edge=2):
         last = 0
         left_current = self.page - left_current - 1
         right_current = self.page + right_current + 1
diff --git a/cps/remotelogin.py b/cps/remotelogin.py
index a9994f09e1..ea613c29a2 100644
--- a/cps/remotelogin.py
+++ b/cps/remotelogin.py
@@ -20,23 +20,20 @@
 #  You should have received a copy of the GNU General Public License
 #  along with this program. If not, see <http://www.gnu.org/licenses/>.
 
 import json
 from datetime import datetime
+from functools import wraps
 
 from flask import Blueprint, request, make_response, abort, url_for, flash, redirect
 from flask_login import login_required, current_user, login_user
 from flask_babel import gettext as _
 from sqlalchemy.sql.expression import true
 
 from . import config, logger, ub
 from .render_template import render_title_template
 
-try:
-    from functools import wraps
-except ImportError:
-    pass  # We're not using Python 3
 
 remotelogin = Blueprint('remotelogin', __name__)
 log = logger.create()
 
 
diff --git a/cps/templates/book_edit.html b/cps/templates/book_edit.html
index 57470858c8..98866fd685 100644
--- a/cps/templates/book_edit.html
+++ b/cps/templates/book_edit.html
@@ -20,11 +20,11 @@
     {% endif %}
 {% endif %}
 
 {%  if source_formats|length > 0 and conversion_formats|length > 0 %}
   <div class="text-center more-stuff"><h4>{{_('Convert book format:')}}</h4>
-      <form class="padded-bottom" action="{{ url_for('editbook.convert_bookformat', book_id=book.id) }}" method="post" id="book_convert_frm">
+      <form class="padded-bottom" action="{{ url_for('edit-book.convert_bookformat', book_id=book.id) }}" method="post" id="book_convert_frm">
           <input type="hidden" name="csrf_token" value="{{ csrf_token() }}">
           <div class="form-group">
               <div class="text-left">
                   <label class="control-label" for="book_format_from">{{_('Convert from:')}}</label>
                   <select class="form-control" name="book_format_from" id="book_format_from">
@@ -46,11 +46,11 @@
       </form>
   </div>
 {% endif %}
 
   </div>
-<form role="form" action="{{ url_for('editbook.edit_book', book_id=book.id) }}" method="post" enctype="multipart/form-data" id="book_edit_frm">
+<form role="form" action="{{ url_for('edit-book.edit_book', book_id=book.id) }}" method="post" enctype="multipart/form-data" id="book_edit_frm">
   <input type="hidden" name="csrf_token" value="{{ csrf_token() }}">
   <div class="col-sm-9 col-xs-12">
     <div class="form-group">
       <label for="book_title">{{_('Book Title')}}</label>
       <input type="text" class="form-control" name="book_title" id="book_title" value="{{book.title}}">
diff --git a/cps/templates/book_table.html b/cps/templates/book_table.html
index 4b379b3702..9ba173bb98 100644
--- a/cps/templates/book_table.html
+++ b/cps/templates/book_table.html
@@ -4,11 +4,11 @@
     {% if sort %}data-sortable="true" {% endif %}
     data-visible = "{{visiblility.get(parameter)}}"
     data-escape="true"
     {% if g.user.role_edit() %}
         data-editable-type="text"
-        data-editable-url="{{ url_for('editbook.edit_list_book', param=parameter)}}"
+        data-editable-url="{{ url_for('edit-book.edit_list_book', param=parameter)}}"
         data-editable-title="{{ edit_text }}"
         data-edit="true"
         {% if validate %}data-edit-validate="{{ _('This Field is Required') }}" {% endif %}
     {% endif %}
 >{{ show_text }}</th>
@@ -64,34 +64,34 @@
             {{ text_table_row('sort', _('Enter Title Sort'),_('Title Sort'), false, true) }}
             {{ text_table_row('author_sort', _('Enter Author Sort'),_('Author Sort'), false, true) }}
             {{ text_table_row('authors', _('Enter Authors'),_('Authors'), true, true) }}
             {{ text_table_row('tags', _('Enter Categories'),_('Categories'), false, true) }}
             {{ text_table_row('series', _('Enter Series'),_('Series'), false, true) }}
-            <th data-field="series_index" id="series_index" data-visible="{{visiblility.get('series_index')}}" data-edit-validate="{{ _('This Field is Required') }}" data-sortable="true" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.01" data-editable-min="0" data-editable-url="{{ url_for('editbook.edit_list_book', param='series_index')}}" data-edit="true" data-editable-title="{{_('Enter Title')}}"{% endif %}>{{_('Series Index')}}</th>
+            <th data-field="series_index" id="series_index" data-visible="{{visiblility.get('series_index')}}" data-edit-validate="{{ _('This Field is Required') }}" data-sortable="true" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.01" data-editable-min="0" data-editable-url="{{ url_for('edit-book.edit_list_book', param='series_index')}}" data-edit="true" data-editable-title="{{_('Enter Title')}}"{% endif %}>{{_('Series Index')}}</th>
             {{ text_table_row('languages', _('Enter Languages'),_('Languages'), false, true) }}
             <!--th data-field="pubdate" data-type="date" data-visible="{{visiblility.get('pubdate')}}" data-viewformat="dd.mm.yyyy" id="pubdate" data-sortable="true">{{_('Publishing Date')}}</th-->
             {{ text_table_row('publishers', _('Enter Publishers'),_('Publishers'), false, true) }}
-            <th data-field="comments" id="comments" data-escape="true" data-editable-mode="popup"  data-visible="{{visiblility.get('comments')}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="wysihtml5" data-editable-url="{{ url_for('editbook.edit_list_book', param='comments')}}" data-edit="true" data-editable-title="{{_('Enter comments')}}"{% endif %}>{{_('Comments')}}</th>
+            <th data-field="comments" id="comments" data-escape="true" data-editable-mode="popup"  data-visible="{{visiblility.get('comments')}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="wysihtml5" data-editable-url="{{ url_for('edit-book.edit_list_book', param='comments')}}" data-edit="true" data-editable-title="{{_('Enter comments')}}"{% endif %}>{{_('Comments')}}</th>
             {% if g.user.check_visibility(32768) %}
                 {{ book_checkbox_row('is_archived', _('Archiv Status'), false)}}
             {%  endif %}
             {{ book_checkbox_row('read_status', _('Read Status'), false)}}
             {% for c in cc %}
               {% if c.datatype == "int" %}
-                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="1" data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
+                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="1" data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
               {% elif c.datatype == "rating" %}
-                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-formatter="ratingFormatter" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.5" data-editable-step="1" data-editable-min="1" data-editable-max="5" data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
+                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-formatter="ratingFormatter" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.5" data-editable-step="1" data-editable-min="1" data-editable-max="5" data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
               {% elif c.datatype == "float" %}
-                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.01" data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
+                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.01" data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
               {% elif c.datatype == "enumeration" %}
-                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="select" data-editable-source={{ url_for('editbook.table_get_custom_enum', c_id=c.id)  }} data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
+                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="select" data-editable-source={{ url_for('edit-book.table_get_custom_enum', c_id=c.id)  }} data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
               {% elif c.datatype in ["datetime"] %}
                   <!-- missing -->
               {% elif c.datatype == "text" %}
                  {{ text_table_row('custom_column_' + c.id|string, _('Enter ') + c.name, c.name, false, false) }}
               {% elif c.datatype == "comments" %}
-                  <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-escape="true" data-editable-mode="popup"  data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="wysihtml5" data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
+                  <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-escape="true" data-editable-mode="popup"  data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="wysihtml5" data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
               {% elif c.datatype == "bool" %}
                   {{ book_checkbox_row('custom_column_' + c.id|string, c.name, false)}}
               {% else %}
                 <!--{{ text_table_row('custom_column_' + c.id|string, _('Enter ') + c.name, c.name, false, false) }} -->
               {% endif %}
diff --git a/cps/templates/detail.html b/cps/templates/detail.html
index c2153db8b8..38005cb9d3 100644
--- a/cps/templates/detail.html
+++ b/cps/templates/detail.html
@@ -136,11 +136,11 @@
       {% if entry.identifiers|length > 0 %}
       <div class="identifiers">
         <p>
         <span class="glyphicon glyphicon-link"></span>
         {% for identifier in entry.identifiers %}
-          <a href="{{identifier}}" target="_blank" class="btn btn-xs btn-success" role="button">{{identifier.formatType()}}</a>
+          <a href="{{identifier}}" target="_blank" class="btn btn-xs btn-success" role="button">{{identifier.format_type()}}</a>
         {%endfor%}
       </p>
       </div>
       {% endif %}
 
@@ -293,11 +293,11 @@
 
       {% endif %}
       {% if g.user.role_edit() %}
       <div class="btn-toolbar" role="toolbar">
         <div class="btn-group" role="group" aria-label="Edit/Delete book">
-          <a href="{{ url_for('editbook.edit_book', book_id=entry.id) }}" class="btn btn-sm btn-primary" id="edit_book" role="button"><span class="glyphicon glyphicon-edit"></span> {{_('Edit Metadata')}}</a>
+          <a href="{{ url_for('edit-book.edit_book', book_id=entry.id) }}" class="btn btn-sm btn-primary" id="edit_book" role="button"><span class="glyphicon glyphicon-edit"></span> {{_('Edit Metadata')}}</a>
         </div>
       </div>
       {% endif %}
       </div>
 
diff --git a/cps/templates/layout.html b/cps/templates/layout.html
index ec69c91ba6..0d0df7785d 100644
--- a/cps/templates/layout.html
+++ b/cps/templates/layout.html
@@ -58,11 +58,11 @@
           {% endif %}
           <ul class="nav navbar-nav navbar-right" id="main-nav">
             {% if g.user.is_authenticated or g.allow_anonymous %}
               {% if g.user.role_upload() and g.allow_upload %}
                   <li>
-                    <form id="form-upload" class="navbar-form" action="{{ url_for('editbook.upload') }}" data-title="{{_('Uploading...')}}" data-footer="{{_('Close')}}" data-failed="{{_('Error')}}" data-message="{{_('Upload done, processing, please wait...')}}" method="post" enctype="multipart/form-data">
+                    <form id="form-upload" class="navbar-form" action="{{ url_for('edit-book.upload') }}" data-title="{{_('Uploading...')}}" data-footer="{{_('Close')}}" data-failed="{{_('Error')}}" data-message="{{_('Upload done, processing, please wait...')}}" method="post" enctype="multipart/form-data">
                       <input type="hidden" name="csrf_token" value="{{ csrf_token() }}">
                       <div class="form-group">
                         <span class="btn btn-default btn-file">{{_('Upload')}}<input id="btn-upload" name="btn-upload"
                         type="file" accept="{% for format in accept %}.{% if format != ''%}{{format}}{% else %}*{% endif %}{{ ',' if not loop.last }}{% endfor %}" multiple></span>
                       </div>
diff --git a/cps/usermanagement.py b/cps/usermanagement.py
index 71da7701ce..62fe6f77f9 100644
--- a/cps/usermanagement.py
+++ b/cps/usermanagement.py
@@ -16,21 +16,18 @@
 #  You should have received a copy of the GNU General Public License
 #  along with this program. If not, see <http://www.gnu.org/licenses/>.
 
 import base64
 import binascii
+from functools import wraps
 
 from sqlalchemy.sql.expression import func
 from werkzeug.security import check_password_hash
 from flask_login import login_required, login_user
 
 from . import lm, ub, config, constants, services
 
-try:
-    from functools import wraps
-except ImportError:
-    pass  # We're not using Python 3
 
 def login_required_if_no_ano(func):
     @wraps(func)
     def decorated_view(*args, **kwargs):
         if config.config_anonbrowse == 1:
diff --git a/cps/web.py b/cps/web.py
index 8c057b9d16..6f2f1af152 100644
--- a/cps/web.py
+++ b/cps/web.py
@@ -27,11 +27,11 @@ import mimetypes
 import chardet  # dependency of requests
 import copy
 from functools import wraps
 
 from babel.dates import format_date
-from babel import Locale as LC
+from babel import Locale
 from flask import Blueprint, jsonify
 from flask import request, redirect, send_from_directory, make_response, flash, abort, url_for
 from flask import session as flask_session
 from flask_babel import gettext as _
 from flask_login import login_user, logout_user, login_required, current_user
@@ -58,43 +58,48 @@ from .redirect import redirect_back
 from .usermanagement import login_required_if_no_ano
 from .kobo_sync_status import remove_synced_book
 from .render_template import render_title_template
 from .kobo_sync_status import change_archived_books
 
-
 feature_support = {
     'ldap': bool(services.ldap),
     'goodreads': bool(services.goodreads_support),
     'kobo': bool(services.kobo)
 }
 
 try:
     from .oauth_bb import oauth_check, register_user_with_oauth, logout_oauth_user, get_oauth_status
+
     feature_support['oauth'] = True
 except ImportError:
     feature_support['oauth'] = False
     oauth_check = {}
+    register_user_with_oauth = logout_oauth_user = get_oauth_status = None
 
 try:
     from natsort import natsorted as sort
 except ImportError:
     sort = sorted  # Just use regular sort then, may cause issues with badly named pages in cbz/cbr files
 
 
 @app.after_request
 def add_security_headers(resp):
-    resp.headers['Content-Security-Policy'] = "default-src 'self'" + ''.join([' '+host for host in config.config_trustedhosts.strip().split(',')]) + " 'unsafe-inline' 'unsafe-eval'; font-src 'self' data:; img-src 'self' data:"
-    if request.endpoint == "editbook.edit_book" or config.config_use_google_drive:
+    csp = "default-src 'self'"
+    csp += ''.join([' ' + host for host in config.config_trustedhosts.strip().split(',')])
+    csp += " 'unsafe-inline' 'unsafe-eval'; font-src 'self' data:; img-src 'self' data:"
+    resp.headers['Content-Security-Policy'] = csp
+    if request.endpoint == "edit-book.edit_book" or config.config_use_google_drive:
         resp.headers['Content-Security-Policy'] += " *"
     elif request.endpoint == "web.read_book":
         resp.headers['Content-Security-Policy'] += " blob:;style-src-elem 'self' blob: 'unsafe-inline';"
     resp.headers['X-Content-Type-Options'] = 'nosniff'
     resp.headers['X-Frame-Options'] = 'SAMEORIGIN'
     resp.headers['X-XSS-Protection'] = '1; mode=block'
     resp.headers['Strict-Transport-Security'] = 'max-age=31536000;'
     return resp
 
+
 web = Blueprint('web', __name__)
 log = logger.create()
 
 
 # ################################### Login logic and rights management ###############################################
@@ -117,10 +122,11 @@ def viewer_required(f):
             return f(*args, **kwargs)
         abort(403)
 
     return inner
 
+
 # ################################### data provider functions #########################################################
 
 
 @web.route("/ajax/emailstat")
 @login_required
@@ -138,15 +144,15 @@ def set_bookmark(book_id, book_format):
                                               ub.Bookmark.format == book_format)).delete()
     if not bookmark_key:
         ub.session_commit()
         return "", 204
 
-    lbookmark = ub.Bookmark(user_id=current_user.id,
-                            book_id=book_id,
-                            format=book_format,
-                            bookmark_key=bookmark_key)
-    ub.session.merge(lbookmark)
+    l_bookmark = ub.Bookmark(user_id=current_user.id,
+                             book_id=book_id,
+                             format=book_format,
+                             bookmark_key=bookmark_key)
+    ub.session.merge(l_bookmark)
     ub.session_commit("Bookmark for user {} in book {} created".format(current_user.id, book_id))
     return "", 201
 
 
 @web.route("/ajax/toggleread/<int:book_id>", methods=['POST'])
@@ -160,11 +166,11 @@ def toggle_read(book_id):
 
 
 @web.route("/ajax/togglearchived/<int:book_id>", methods=['POST'])
 @login_required
 def toggle_archived(book_id):
-    is_archived = change_archived_books(book_id, message="Book {} archivebit toggled".format(book_id))
+    is_archived = change_archived_books(book_id, message="Book {} archive bit toggled".format(book_id))
     if is_archived:
         remove_synced_book(book_id)
     return ""
 
 
@@ -228,10 +234,11 @@ def get_comic_book(book_id, book_format, page):
                 fileData={"name": names[page], "page":page, "last":len(names)-1, "content": extractedfile}
                 return make_response(json.dumps(fileData))
         return "", 204
 '''
 
+
 # ################################### Typeahead ##################################################################
 
 
 @web.route("/get_authors_json", methods=['GET'])
 @login_required_if_no_ano
@@ -295,10 +302,16 @@ def get_matching_tags():
                 tag_dict['tags'].append(tag.id)
     json_dumps = json.dumps(tag_dict)
     return json_dumps
 
 
+def generate_char_list(data_colum, db_link):
+    return (calibre_db.session.query(func.upper(func.substr(data_colum, 1, 1)).label('char'))
+            .join(db_link).join(db.Books).filter(calibre_db.common_filters())
+            .group_by(func.upper(func.substr(data_colum, 1, 1))).all())
+
+
 def get_sort_function(sort_param, data):
     order = [db.Books.timestamp.desc()]
     if sort_param == 'stored':
         sort_param = current_user.get_view_property(data, 'stored')
     else:
@@ -371,11 +384,11 @@ def render_books_list(data, sort_param, book_id, page):
         offset = int(int(config.config_books_per_page) * (page - 1))
         return render_adv_search_results(term, offset, order, config.config_books_per_page)
     else:
         website = data or "newest"
         entries, random, pagination = calibre_db.fill_indexpage(page, 0, db.Books, True, order[0],
-        														False, 0,
+                                                                False, 0,
                                                                 db.books_series_link,
                                                                 db.Books.id == db.books_series_link.c.book,
                                                                 db.Series)
         return render_title_template('index.html', random=random, entries=entries, pagination=pagination,
                                      title=_(u"Books"), page=website, order=order[1])
@@ -405,36 +418,37 @@ def render_discover_books(page, book_id):
         return render_title_template('discover.html', entries=entries, pagination=pagination, id=book_id,
                                      title=_(u"Discover (Random Books)"), page="discover")
     else:
         abort(404)
 
+
 def render_hot_books(page, order):
     if current_user.check_visibility(constants.SIDEBAR_HOT):
         if order[1] not in ['hotasc', 'hotdesc']:
-        # Unary expression comparsion only working (for this expression) in sqlalchemy 1.4+
-        #if not (order[0][0].compare(func.count(ub.Downloads.book_id).desc()) or
-        #        order[0][0].compare(func.count(ub.Downloads.book_id).asc())):
+            # Unary expression comparsion only working (for this expression) in sqlalchemy 1.4+
+            # if not (order[0][0].compare(func.count(ub.Downloads.book_id).desc()) or
+            #        order[0][0].compare(func.count(ub.Downloads.book_id).asc())):
             order = [func.count(ub.Downloads.book_id).desc()], 'hotdesc'
         if current_user.show_detail_random():
             random = calibre_db.session.query(db.Books).filter(calibre_db.common_filters()) \
                 .order_by(func.random()).limit(config.config_random_books)
         else:
             random = false()
         off = int(int(config.config_books_per_page) * (page - 1))
-        all_books = ub.session.query(ub.Downloads, func.count(ub.Downloads.book_id))\
+        all_books = ub.session.query(ub.Downloads, func.count(ub.Downloads.book_id)) \
             .order_by(*order[0]).group_by(ub.Downloads.book_id)
         hot_books = all_books.offset(off).limit(config.config_books_per_page)
         entries = list()
         for book in hot_books:
-            downloadBook = calibre_db.session.query(db.Books).filter(calibre_db.common_filters()).filter(
+            download_book = calibre_db.session.query(db.Books).filter(calibre_db.common_filters()).filter(
                 db.Books.id == book.Downloads.book_id).first()
-            if downloadBook:
-                entries.append(downloadBook)
+            if download_book:
+                entries.append(download_book)
             else:
                 ub.delete_download(book.Downloads.book_id)
-        numBooks = entries.__len__()
-        pagination = Pagination(page, config.config_books_per_page, numBooks)
+        num_books = entries.__len__()
+        pagination = Pagination(page, config.config_books_per_page, num_books)
         return render_title_template('index.html', random=random, entries=entries, pagination=pagination,
                                      title=_(u"Hot Books (Most Downloaded)"), page="hot", order=order[1])
     else:
         abort(404)
 
@@ -460,20 +474,20 @@ def render_downloaded_books(page, order, user_id):
                                                             db.books_series_link,
                                                             db.Books.id == db.books_series_link.c.book,
                                                             db.Series,
                                                             ub.Downloads, db.Books.id == ub.Downloads.book_id)
         for book in entries:
-            if not calibre_db.session.query(db.Books).filter(calibre_db.common_filters()) \
-                             .filter(db.Books.id == book.id).first():
+            if not calibre_db.session.query(db.Books).\
+                                            filter(calibre_db.common_filters()).filter(db.Books.id == book.id).first():
                 ub.delete_download(book.id)
         user = ub.session.query(ub.User).filter(ub.User.id == user_id).first()
         return render_title_template('index.html',
                                      random=random,
                                      entries=entries,
                                      pagination=pagination,
                                      id=user_id,
-                                     title=_(u"Downloaded books by %(user)s",user=user.name),
+                                     title=_(u"Downloaded books by %(user)s", user=user.name),
                                      page="download",
                                      order=order[1])
     else:
         abort(404)
 
@@ -637,33 +651,31 @@ def render_read_books(page, are_read, as_xml=False, order=None):
             if not as_xml:
                 flash(_("Custom Column No.%(column)d is not existing in calibre database",
                         column=config.config_read_column),
                       category="error")
                 return redirect(url_for("web.index"))
-            return [] # ToDo: Handle error Case for opds
+            return []  # ToDo: Handle error Case for opds
 
     if as_xml:
         return entries, pagination
     else:
         if are_read:
             name = _(u'Read Books') + ' (' + str(pagination.total_count) + ')'
-            pagename = "read"
+            page_name = "read"
         else:
             name = _(u'Unread Books') + ' (' + str(pagination.total_count) + ')'
-            pagename = "unread"
+            page_name = "unread"
         return render_title_template('index.html', random=random, entries=entries, pagination=pagination,
-                                     title=name, page=pagename, order=order[1])
+                                     title=name, page=page_name, order=order[1])
 
 
 def render_archived_books(page, sort_param):
     order = sort_param[0] or []
-    archived_books = (
-        ub.session.query(ub.ArchivedBook)
-        .filter(ub.ArchivedBook.user_id == int(current_user.id))
-        .filter(ub.ArchivedBook.is_archived == True)
-        .all()
-    )
+    archived_books = (ub.session.query(ub.ArchivedBook)
+                      .filter(ub.ArchivedBook.user_id == int(current_user.id))
+                      .filter(ub.ArchivedBook.is_archived == True)
+                      .all())
     archived_book_ids = [archived_book.book_id for archived_book in archived_books]
 
     archived_filter = db.Books.id.in_(archived_book_ids)
 
     entries, random, pagination = calibre_db.fill_indexpage_with_archived_books(page, db.Books,
@@ -672,53 +684,52 @@ def render_archived_books(page, sort_param):
                                                                                 order,
                                                                                 True,
                                                                                 False, 0)
 
     name = _(u'Archived Books') + ' (' + str(len(archived_book_ids)) + ')'
-    pagename = "archived"
+    page_name = "archived"
     return render_title_template('index.html', random=random, entries=entries, pagination=pagination,
-                                 title=name, page=pagename, order=sort_param[1])
+                                 title=name, page=page_name, order=sort_param[1])
 
 
 def render_prepare_search_form(cc):
     # prepare data for search-form
-    tags = calibre_db.session.query(db.Tags)\
-        .join(db.books_tags_link)\
-        .join(db.Books)\
+    tags = calibre_db.session.query(db.Tags) \
+        .join(db.books_tags_link) \
+        .join(db.Books) \
         .filter(calibre_db.common_filters()) \
-        .group_by(text('books_tags_link.tag'))\
+        .group_by(text('books_tags_link.tag')) \
         .order_by(db.Tags.name).all()
-    series = calibre_db.session.query(db.Series)\
-        .join(db.books_series_link)\
-        .join(db.Books)\
+    series = calibre_db.session.query(db.Series) \
+        .join(db.books_series_link) \
+        .join(db.Books) \
         .filter(calibre_db.common_filters()) \
-        .group_by(text('books_series_link.series'))\
-        .order_by(db.Series.name)\
+        .group_by(text('books_series_link.series')) \
+        .order_by(db.Series.name) \
         .filter(calibre_db.common_filters()).all()
-    shelves = ub.session.query(ub.Shelf)\
-        .filter(or_(ub.Shelf.is_public == 1, ub.Shelf.user_id == int(current_user.id)))\
+    shelves = ub.session.query(ub.Shelf) \
+        .filter(or_(ub.Shelf.is_public == 1, ub.Shelf.user_id == int(current_user.id))) \
         .order_by(ub.Shelf.name).all()
-    extensions = calibre_db.session.query(db.Data)\
-        .join(db.Books)\
+    extensions = calibre_db.session.query(db.Data) \
+        .join(db.Books) \
         .filter(calibre_db.common_filters()) \
-        .group_by(db.Data.format)\
+        .group_by(db.Data.format) \
         .order_by(db.Data.format).all()
     if current_user.filter_language() == u"all":
         languages = calibre_db.speaking_language()
     else:
         languages = None
     return render_title_template('search_form.html', tags=tags, languages=languages, extensions=extensions,
-                                 series=series,shelves=shelves, title=_(u"Advanced Search"), cc=cc, page="advsearch")
+                                 series=series, shelves=shelves, title=_(u"Advanced Search"), cc=cc, page="advsearch")
 
 
 def render_search_results(term, offset=None, order=None, limit=None):
     join = db.books_series_link, db.Books.id == db.books_series_link.c.book, db.Series
     entries, result_count, pagination = calibre_db.get_search_results(term,
                                                                       offset,
                                                                       order,
                                                                       limit,
-                                                                      False,
                                                                       config.config_read_column,
                                                                       *join)
     return render_title_template('search.html',
                                  searchterm=term,
                                  pagination=pagination,
@@ -757,16 +768,17 @@ def books_table():
     visibility = current_user.view_settings.get('table', {})
     cc = get_cc_columns(filter_config_custom_read=True)
     return render_title_template('book_table.html', title=_(u"Books List"), cc=cc, page="book_table",
                                  visiblility=visibility)
 
+
 @web.route("/ajax/listbooks")
 @login_required
 def list_books():
     off = int(request.args.get("offset") or 0)
     limit = int(request.args.get("limit") or config.config_books_per_page)
-    search = request.args.get("search")
+    search_param = request.args.get("search")
     sort_param = request.args.get("sort", "id")
     order = request.args.get("order", "").lower()
     state = None
     join = tuple()
 
@@ -782,24 +794,25 @@ def list_books():
         order = [db.Publishers.name.asc()] if order == "asc" else [db.Publishers.name.desc()]
         join = db.books_publishers_link, db.Books.id == db.books_publishers_link.c.book, db.Publishers
     elif sort_param == "authors":
         order = [db.Authors.name.asc(), db.Series.name, db.Books.series_index] if order == "asc" \
             else [db.Authors.name.desc(), db.Series.name.desc(), db.Books.series_index.desc()]
-        join = db.books_authors_link, db.Books.id == db.books_authors_link.c.book, db.Authors, \
-               db.books_series_link, db.Books.id == db.books_series_link.c.book, db.Series
+        join = db.books_authors_link, db.Books.id == db.books_authors_link.c.book, db.Authors, db.books_series_link, \
+            db.Books.id == db.books_series_link.c.book, db.Series
     elif sort_param == "languages":
         order = [db.Languages.lang_code.asc()] if order == "asc" else [db.Languages.lang_code.desc()]
         join = db.books_languages_link, db.Books.id == db.books_languages_link.c.book, db.Languages
     elif order and sort_param in ["sort", "title", "authors_sort", "series_index"]:
         order = [text(sort_param + " " + order)]
     elif not state:
         order = [db.Books.timestamp.desc()]
 
-    total_count = filtered_count = calibre_db.session.query(db.Books).filter(calibre_db.common_filters(allow_show_archived=True)).count()
+    total_count = filtered_count = calibre_db.session.query(db.Books).filter(
+        calibre_db.common_filters(allow_show_archived=True)).count()
     if state is not None:
-        if search:
-            books = calibre_db.search_query(search, config.config_read_column).all()
+        if search_param:
+            books = calibre_db.search_query(search_param, config.config_read_column).all()
             filtered_count = len(books)
         else:
             if not config.config_read_column:
                 books = (calibre_db.session.query(db.Books, ub.ReadBook.read_status, ub.ArchivedBook.is_archived)
                          .select_from(db.Books)
@@ -816,19 +829,18 @@ def list_books():
                 except (KeyError, AttributeError):
                     log.error("Custom Column No.%d is not existing in calibre database", read_column)
                     # Skip linking read column and return None instead of read status
                     books = calibre_db.session.query(db.Books, None, ub.ArchivedBook.is_archived)
             books = (books.outerjoin(ub.ArchivedBook, and_(db.Books.id == ub.ArchivedBook.book_id,
-                                                          int(current_user.id) == ub.ArchivedBook.user_id))
+                                                           int(current_user.id) == ub.ArchivedBook.user_id))
                      .filter(calibre_db.common_filters(allow_show_archived=True)).all())
         entries = calibre_db.get_checkbox_sorted(books, state, off, limit, order, True)
-    elif search:
-        entries, filtered_count, __ = calibre_db.get_search_results(search,
+    elif search_param:
+        entries, filtered_count, __ = calibre_db.get_search_results(search_param,
                                                                     off,
-                                                                    [order,''],
+                                                                    [order, ''],
                                                                     limit,
-                                                                    True,
                                                                     config.config_read_column,
                                                                     *join)
     else:
         entries, __, __ = calibre_db.fill_indexpage_with_archived_books((int(off) / (int(limit)) + 1),
                                                                         db.Books,
@@ -843,22 +855,23 @@ def list_books():
     result = list()
     for entry in entries:
         val = entry[0]
         val.read_status = entry[1] == ub.ReadBook.STATUS_FINISHED
         val.is_archived = entry[2] is True
-        for index in range(0, len(val.languages)):
-            val.languages[index].language_name = isoLanguages.get_language_name(get_locale(), val.languages[
-                index].lang_code)
+        for lang_index in range(0, len(val.languages)):
+            val.languages[lang_index].language_name = isoLanguages.get_language_name(get_locale(), val.languages[
+                lang_index].lang_code)
         result.append(val)
 
     table_entries = {'totalNotFiltered': total_count, 'total': filtered_count, "rows": result}
     js_list = json.dumps(table_entries, cls=db.AlchemyEncoder)
 
     response = make_response(js_list)
     response.headers["Content-Type"] = "application/json; charset=utf-8"
     return response
 
+
 @web.route("/ajax/table_settings", methods=['POST'])
 @login_required
 def update_table_settings():
     current_user.view_settings['table'] = json.loads(request.data)
     try:
@@ -884,39 +897,38 @@ def author_list():
             order = db.Authors.sort.asc()
             order_no = 1
         entries = calibre_db.session.query(db.Authors, func.count('books_authors_link.book').label('count')) \
             .join(db.books_authors_link).join(db.Books).filter(calibre_db.common_filters()) \
             .group_by(text('books_authors_link.author')).order_by(order).all()
-        charlist = calibre_db.session.query(func.upper(func.substr(db.Authors.sort, 1, 1)).label('char')) \
-            .join(db.books_authors_link).join(db.Books).filter(calibre_db.common_filters()) \
-            .group_by(func.upper(func.substr(db.Authors.sort, 1, 1))).all()
+        char_list = generate_char_list(db.Authors.sort, db.books_authors_link)
         # If not creating a copy, readonly databases can not display authornames with "|" in it as changing the name
         # starts a change session
-        autor_copy = copy.deepcopy(entries)
-        for entry in autor_copy:
+        author_copy = copy.deepcopy(entries)
+        for entry in author_copy:
             entry.Authors.name = entry.Authors.name.replace('|', ',')
-        return render_title_template('list.html', entries=autor_copy, folder='web.books_list', charlist=charlist,
+        return render_title_template('list.html', entries=author_copy, folder='web.books_list', charlist=char_list,
                                      title=u"Authors", page="authorlist", data='author', order=order_no)
     else:
         abort(404)
 
+
 @web.route("/downloadlist")
 @login_required_if_no_ano
 def download_list():
     if current_user.get_view_property('download', 'dir') == 'desc':
         order = ub.User.name.desc()
         order_no = 0
     else:
         order = ub.User.name.asc()
         order_no = 1
     if current_user.check_visibility(constants.SIDEBAR_DOWNLOAD) and current_user.role_admin():
-        entries = ub.session.query(ub.User, func.count(ub.Downloads.book_id).label('count'))\
+        entries = ub.session.query(ub.User, func.count(ub.Downloads.book_id).label('count')) \
             .join(ub.Downloads).group_by(ub.Downloads.user_id).order_by(order).all()
-        charlist = ub.session.query(func.upper(func.substr(ub.User.name, 1, 1)).label('char')) \
+        char_list = ub.session.query(func.upper(func.substr(ub.User.name, 1, 1)).label('char')) \
             .filter(ub.User.role.op('&')(constants.ROLE_ANONYMOUS) != constants.ROLE_ANONYMOUS) \
             .group_by(func.upper(func.substr(ub.User.name, 1, 1))).all()
-        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=charlist,
+        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=char_list,
                                      title=_(u"Downloads"), page="downloadlist", data="download", order=order_no)
     else:
         abort(404)
 
 
@@ -931,14 +943,12 @@ def publisher_list():
         order_no = 1
     if current_user.check_visibility(constants.SIDEBAR_PUBLISHER):
         entries = calibre_db.session.query(db.Publishers, func.count('books_publishers_link.book').label('count')) \
             .join(db.books_publishers_link).join(db.Books).filter(calibre_db.common_filters()) \
             .group_by(text('books_publishers_link.publisher')).order_by(order).all()
-        charlist = calibre_db.session.query(func.upper(func.substr(db.Publishers.name, 1, 1)).label('char')) \
-            .join(db.books_publishers_link).join(db.Books).filter(calibre_db.common_filters()) \
-            .group_by(func.upper(func.substr(db.Publishers.name, 1, 1))).all()
-        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=charlist,
+        char_list = generate_char_list(db.Publishers.name, db.books_publishers_link)
+        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=char_list,
                                      title=_(u"Publishers"), page="publisherlist", data="publisher", order=order_no)
     else:
         abort(404)
 
 
@@ -950,29 +960,23 @@ def series_list():
             order = db.Series.sort.desc()
             order_no = 0
         else:
             order = db.Series.sort.asc()
             order_no = 1
+        char_list = generate_char_list(db.Series.sort, db.books_series_link)
         if current_user.get_view_property('series', 'series_view') == 'list':
             entries = calibre_db.session.query(db.Series, func.count('books_series_link.book').label('count')) \
                 .join(db.books_series_link).join(db.Books).filter(calibre_db.common_filters()) \
                 .group_by(text('books_series_link.series')).order_by(order).all()
-            charlist = calibre_db.session.query(func.upper(func.substr(db.Series.sort, 1, 1)).label('char')) \
-                .join(db.books_series_link).join(db.Books).filter(calibre_db.common_filters()) \
-                .group_by(func.upper(func.substr(db.Series.sort, 1, 1))).all()
-            return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=charlist,
+            return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=char_list,
                                          title=_(u"Series"), page="serieslist", data="series", order=order_no)
         else:
             entries = calibre_db.session.query(db.Books, func.count('books_series_link').label('count'),
                                                func.max(db.Books.series_index), db.Books.id) \
-                .join(db.books_series_link).join(db.Series).filter(calibre_db.common_filters())\
+                .join(db.books_series_link).join(db.Series).filter(calibre_db.common_filters()) \
                 .group_by(text('books_series_link.series')).order_by(order).all()
-            charlist = calibre_db.session.query(func.upper(func.substr(db.Series.sort, 1, 1)).label('char')) \
-                .join(db.books_series_link).join(db.Books).filter(calibre_db.common_filters()) \
-                .group_by(func.upper(func.substr(db.Series.sort, 1, 1))).all()
-
-            return render_title_template('grid.html', entries=entries, folder='web.books_list', charlist=charlist,
+            return render_title_template('grid.html', entries=entries, folder='web.books_list', charlist=char_list,
                                          title=_(u"Series"), page="serieslist", data="series", bodyClass="grid-view",
                                          order=order_no)
     else:
         abort(404)
 
@@ -986,11 +990,11 @@ def ratings_list():
             order_no = 0
         else:
             order = db.Ratings.rating.asc()
             order_no = 1
         entries = calibre_db.session.query(db.Ratings, func.count('books_ratings_link.book').label('count'),
-                                   (db.Ratings.rating / 2).label('name')) \
+                                           (db.Ratings.rating / 2).label('name')) \
             .join(db.books_ratings_link).join(db.Books).filter(calibre_db.common_filters()) \
             .group_by(text('books_ratings_link.rating')).order_by(order).all()
         return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=list(),
                                      title=_(u"Ratings list"), page="ratingslist", data="ratings", order=order_no)
     else:
@@ -1021,18 +1025,18 @@ def formats_list():
 @web.route("/language")
 @login_required_if_no_ano
 def language_overview():
     if current_user.check_visibility(constants.SIDEBAR_LANGUAGE) and current_user.filter_language() == u"all":
         order_no = 0 if current_user.get_view_property('language', 'dir') == 'desc' else 1
-        charlist = list()
+        char_list = list()
         languages = calibre_db.speaking_language(reverse_order=not order_no, with_count=True)
         for lang in languages:
             upper_lang = lang[0].name[0].upper()
-            if upper_lang not in charlist:
-                charlist.append(upper_lang)
+            if upper_lang not in char_list:
+                char_list.append(upper_lang)
         return render_title_template('languages.html', languages=languages,
-                                     charlist=charlist, title=_(u"Languages"), page="langlist",
+                                     charlist=char_list, title=_(u"Languages"), page="langlist",
                                      data="language", order=order_no)
     else:
         abort(404)
 
 
@@ -1047,14 +1051,12 @@ def category_list():
             order = db.Tags.name.asc()
             order_no = 1
         entries = calibre_db.session.query(db.Tags, func.count('books_tags_link.book').label('count')) \
             .join(db.books_tags_link).join(db.Books).order_by(order).filter(calibre_db.common_filters()) \
             .group_by(text('books_tags_link.tag')).all()
-        charlist = calibre_db.session.query(func.upper(func.substr(db.Tags.name, 1, 1)).label('char')) \
-            .join(db.books_tags_link).join(db.Books).filter(calibre_db.common_filters()) \
-            .group_by(func.upper(func.substr(db.Tags.name, 1, 1))).all()
-        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=charlist,
+        char_list = generate_char_list(db.Tags.name, db.books_tags_link)
+        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=char_list,
                                      title=_(u"Categories"), page="catlist", data="category", order=order_no)
     else:
         abort(404)
 
 
@@ -1174,11 +1176,19 @@ def adv_search_read_status(q, read_status):
                     .filter(ub.ReadBook.user_id == int(current_user.id),
                             coalesce(ub.ReadBook.read_status, 0) != ub.ReadBook.STATUS_FINISHED)
     return q
 
 
-def adv_search_extension(q, include_extension_inputs, exclude_extension_inputs):
+def adv_search_text(q, include_inputs, exclude_inputs, data_value):
+    for inp in include_inputs:
+        q = q.filter(db.Books.data.any(data_value == inp))
+    for excl in exclude_inputs:
+        q = q.filter(not_(db.Books.data.any(data_value == excl)))
+    return q
+
+
+'''def adv_search_extension(q, include_extension_inputs, exclude_extension_inputs):
     for extension in include_extension_inputs:
         q = q.filter(db.Books.data.any(db.Data.format == extension))
     for extension in exclude_extension_inputs:
         q = q.filter(not_(db.Books.data.any(db.Data.format == extension)))
     return q
@@ -1195,19 +1205,21 @@ def adv_search_tag(q, include_tag_inputs, exclude_tag_inputs):
 def adv_search_serie(q, include_series_inputs, exclude_series_inputs):
     for serie in include_series_inputs:
         q = q.filter(db.Books.series.any(db.Series.id == serie))
     for serie in exclude_series_inputs:
         q = q.filter(not_(db.Books.series.any(db.Series.id == serie)))
-    return q
+    return q'''
+
 
 def adv_search_shelf(q, include_shelf_inputs, exclude_shelf_inputs):
-    q = q.outerjoin(ub.BookShelf, db.Books.id == ub.BookShelf.book_id)\
+    q = q.outerjoin(ub.BookShelf, db.Books.id == ub.BookShelf.book_id) \
         .filter(or_(ub.BookShelf.shelf == None, ub.BookShelf.shelf.notin_(exclude_shelf_inputs)))
     if len(include_shelf_inputs) > 0:
         q = q.filter(ub.BookShelf.shelf.in_(include_shelf_inputs))
     return q
 
+
 def extend_search_term(searchterm,
                        author_name,
                        book_title,
                        publisher,
                        pub_start,
@@ -1230,11 +1242,11 @@ def extend_search_term(searchterm,
             searchterm.extend([_(u"Published before ") +
                                format_date(datetime.strptime(pub_end, "%Y-%m-%d"),
                                            format='medium', locale=get_locale())])
         except ValueError:
             pub_end = u""
-    elements = {'tag': db.Tags, 'serie':db.Series, 'shelf':ub.Shelf}
+    elements = {'tag': db.Tags, 'serie': db.Series, 'shelf': ub.Shelf}
     for key, db_element in elements.items():
         tag_names = calibre_db.session.query(db_element).filter(db_element.id.in_(tags['include_' + key])).all()
         searchterm.extend(tag.name for tag in tag_names)
         tag_names = calibre_db.session.query(db_element).filter(db_element.id.in_(tags['exclude_' + key])).all()
         searchterm.extend(tag.name for tag in tag_names)
@@ -1282,12 +1294,12 @@ def render_adv_search_results(term, offset=None, order=None, limit=None):
             # Skip linking read column
             query = calibre_db.session.query(db.Books, ub.ArchivedBook.is_archived, None)
     query = query.outerjoin(ub.ArchivedBook, and_(db.Books.id == ub.ArchivedBook.book_id,
                                                   int(current_user.id) == ub.ArchivedBook.user_id))
 
-    q = query.outerjoin(db.books_series_link, db.Books.id == db.books_series_link.c.book)\
-        .outerjoin(db.Series)\
+    q = query.outerjoin(db.books_series_link, db.Books.id == db.books_series_link.c.book) \
+        .outerjoin(db.Series) \
         .filter(calibre_db.common_filters(True))
 
     # parse multiselects to a complete dict
     tags = dict()
     elements = ['tag', 'serie', 'shelf', 'language', 'extension']
@@ -1309,47 +1321,47 @@ def render_adv_search_results(term, offset=None, order=None, limit=None):
     if book_title:
         book_title = book_title.strip().lower()
     if publisher:
         publisher = publisher.strip().lower()
 
-    searchterm = []
+    search_term = []
     cc_present = False
     for c in cc:
         if c.datatype == "datetime":
             column_start = term.get('custom_column_' + str(c.id) + '_start')
             column_end = term.get('custom_column_' + str(c.id) + '_end')
             if column_start:
-                searchterm.extend([u"{} >= {}".format(c.name,
-                                                      format_date(datetime.strptime(column_start, "%Y-%m-%d").date(),
-                                                                      format='medium',
-                                                                      locale=get_locale())
-                                                      )])
+                search_term.extend([u"{} >= {}".format(c.name,
+                                                       format_date(datetime.strptime(column_start, "%Y-%m-%d").date(),
+                                                                   format='medium',
+                                                                   locale=get_locale())
+                                                       )])
                 cc_present = True
             if column_end:
-                searchterm.extend([u"{} <= {}".format(c.name,
-                                                      format_date(datetime.strptime(column_end, "%Y-%m-%d").date(),
-                                                                      format='medium',
-                                                                      locale=get_locale())
-                                                      )])
+                search_term.extend([u"{} <= {}".format(c.name,
+                                                       format_date(datetime.strptime(column_end, "%Y-%m-%d").date(),
+                                                                   format='medium',
+                                                                   locale=get_locale())
+                                                       )])
                 cc_present = True
         elif term.get('custom_column_' + str(c.id)):
-            searchterm.extend([(u"{}: {}".format(c.name, term.get('custom_column_' + str(c.id))))])
+            search_term.extend([(u"{}: {}".format(c.name, term.get('custom_column_' + str(c.id))))])
             cc_present = True
 
-
-    if any(tags.values()) or author_name or book_title or publisher or pub_start or pub_end or rating_low \
-       or rating_high or description or cc_present or read_status:
-        searchterm, pub_start, pub_end = extend_search_term(searchterm,
-                                                            author_name,
-                                                            book_title,
-                                                            publisher,
-                                                            pub_start,
-                                                            pub_end,
-                                                            tags,
-                                                            rating_high,
-                                                            rating_low,
-                                                            read_status)
+    if any(tags.values()) or author_name or book_title or \
+        publisher or pub_start or pub_end or rating_low or rating_high \
+            or description or cc_present or read_status:
+        search_term, pub_start, pub_end = extend_search_term(search_term,
+                                                             author_name,
+                                                             book_title,
+                                                             publisher,
+                                                             pub_start,
+                                                             pub_end,
+                                                             tags,
+                                                             rating_high,
+                                                             rating_low,
+                                                             read_status)
         # q = q.filter()
         if author_name:
             q = q.filter(db.Books.authors.any(func.lower(db.Authors.name).ilike("%" + author_name + "%")))
         if book_title:
             q = q.filter(func.lower(db.Books.title).ilike("%" + book_title + "%"))
@@ -1358,16 +1370,16 @@ def render_adv_search_results(term, offset=None, order=None, limit=None):
         if pub_end:
             q = q.filter(func.datetime(db.Books.pubdate) < func.datetime(pub_end))
         q = adv_search_read_status(q, read_status)
         if publisher:
             q = q.filter(db.Books.publishers.any(func.lower(db.Publishers.name).ilike("%" + publisher + "%")))
-        q = adv_search_tag(q, tags['include_tag'], tags['exclude_tag'])
-        q = adv_search_serie(q, tags['include_serie'], tags['exclude_serie'])
+        q = adv_search_text(q, tags['include_tag'], tags['exclude_tag'], db.Tags.id)
+        q = adv_search_text(q, tags['include_serie'], tags['exclude_serie'], db.Series.id)
+        q = adv_search_text(q, tags['include_extension'], tags['exclude_extension'], db.Data.format)
         q = adv_search_shelf(q, tags['include_shelf'], tags['exclude_shelf'])
-        q = adv_search_extension(q, tags['include_extension'], tags['exclude_extension'])
-        q = adv_search_language(q, tags['include_language'], tags['exclude_language'])
-        q = adv_search_ratings(q, rating_high, rating_low)
+        q = adv_search_language(q, tags['include_language'], tags['exclude_language'], )
+        q = adv_search_ratings(q, rating_high, rating_low, )
 
         if description:
             q = q.filter(db.Books.comments.any(func.lower(db.Comments.text).ilike("%" + description + "%")))
 
         # search custom culumns
@@ -1388,11 +1400,11 @@ def render_adv_search_results(term, offset=None, order=None, limit=None):
     else:
         offset = 0
         limit_all = result_count
     entries = calibre_db.order_authors(q[offset:limit_all], list_return=True, combined=True)
     return render_title_template('search.html',
-                                 adv_searchterm=searchterm,
+                                 adv_searchterm=search_term,
                                  pagination=pagination,
                                  entries=entries,
                                  result_count=result_count,
                                  title=_(u"Advanced Search"), page="advsearch",
                                  order=order[1])
@@ -1412,14 +1424,16 @@ def advanced_search_form():
 @web.route("/cover/<int:book_id>")
 @login_required_if_no_ano
 def get_cover(book_id):
     return get_book_cover(book_id)
 
+
 @web.route("/robots.txt")
 def get_robots():
     return send_from_directory(constants.STATIC_DIR, "robots.txt")
 
+
 @web.route("/show/<int:book_id>/<book_format>", defaults={'anyname': 'None'})
 @web.route("/show/<int:book_id>/<book_format>/<anyname>")
 @login_required_if_no_ano
 @viewer_required
 def serve_book(book_id, book_format, anyname):
@@ -1559,11 +1573,11 @@ def login():
                 log.debug(u"You are now logged in as: '%s'", user.name)
                 flash(_(u"you are now logged in as: '%(nickname)s'", nickname=user.name),
                       category="success")
                 return redirect_back(url_for("web.index"))
             elif login_result is None and user and check_password_hash(str(user.password), form['password']) \
-                and user.name != "Guest":
+                    and user.name != "Guest":
                 login_user(user, remember=bool(form.get('remember_me')))
                 ub.store_user_session()
                 log.info("Local Fallback Login as: '%s'", user.name)
                 flash(_(u"Fallback Login as: '%(nickname)s', LDAP Server not reachable, or user not known",
                         nickname=user.name),
@@ -1571,37 +1585,37 @@ def login():
                 return redirect_back(url_for("web.index"))
             elif login_result is None:
                 log.info(error)
                 flash(_(u"Could not login: %(message)s", message=error), category="error")
             else:
-                ip_Address = request.headers.get('X-Forwarded-For', request.remote_addr)
-                log.warning('LDAP Login failed for user "%s" IP-address: %s', form['username'], ip_Address)
+                ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
+                log.warning('LDAP Login failed for user "%s" IP-address: %s', form['username'], ip_address)
                 flash(_(u"Wrong Username or Password"), category="error")
         else:
-            ip_Address = request.headers.get('X-Forwarded-For', request.remote_addr)
+            ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
             if 'forgot' in form and form['forgot'] == 'forgot':
                 if user is not None and user.name != "Guest":
                     ret, __ = reset_password(user.id)
                     if ret == 1:
                         flash(_(u"New Password was send to your email address"), category="info")
-                        log.info('Password reset for user "%s" IP-address: %s', form['username'], ip_Address)
+                        log.info('Password reset for user "%s" IP-address: %s', form['username'], ip_address)
                     else:
                         log.error(u"An unknown error occurred. Please try again later")
                         flash(_(u"An unknown error occurred. Please try again later."), category="error")
                 else:
                     flash(_(u"Please enter valid username to reset password"), category="error")
-                    log.warning('Username missing for password reset IP-address: %s', ip_Address)
+                    log.warning('Username missing for password reset IP-address: %s', ip_address)
             else:
                 if user and check_password_hash(str(user.password), form['password']) and user.name != "Guest":
                     login_user(user, remember=bool(form.get('remember_me')))
                     ub.store_user_session()
                     log.debug(u"You are now logged in as: '%s'", user.name)
                     flash(_(u"You are now logged in as: '%(nickname)s'", nickname=user.name), category="success")
                     config.config_is_initial = False
                     return redirect_back(url_for("web.index"))
                 else:
-                    log.warning('Login failed for user "%s" IP-address: %s', form['username'], ip_Address)
+                    log.warning('Login failed for user "%s" IP-address: %s', form['username'], ip_address)
                     flash(_(u"Wrong Username or Password"), category="error")
 
     next_url = request.args.get('next', default=url_for("web.index"), type=str)
     if url_for("web.logout") == next_url:
         next_url = url_for("web.index")
@@ -1615,11 +1629,11 @@ def login():
 
 @web.route('/logout')
 @login_required
 def logout():
     if current_user is not None and current_user.is_authenticated:
-        ub.delete_user_session(current_user.id, flask_session.get('_id',""))
+        ub.delete_user_session(current_user.id, flask_session.get('_id', ""))
         logout_user()
         if feature_support['oauth'] and (config.config_login_type == 2 or config.config_login_type == 3):
             logout_oauth_user()
     log.debug(u"User logged out")
     return redirect(url_for('web.login'))
@@ -1637,11 +1651,11 @@ def change_profile(kobo_support, local_oauth_check, oauth_status, translations,
             current_user.kindle_mail = valid_email(to_save["kindle_mail"])
         if to_save.get("email", current_user.email) != current_user.email:
             current_user.email = check_email(to_save["email"])
         if current_user.role_admin():
             if to_save.get("name", current_user.name) != current_user.name:
-                # Query User name, if not existing, change
+                # Query username, if not existing, change
                 current_user.name = check_username(to_save["name"])
         current_user.random_books = 1 if to_save.get("show_random") == "on" else 0
         if to_save.get("default_language"):
             current_user.default_language = to_save["default_language"]
         if to_save.get("locale"):
@@ -1691,11 +1705,11 @@ def change_profile(kobo_support, local_oauth_check, oauth_status, translations,
 
 @web.route("/me", methods=["GET", "POST"])
 @login_required
 def profile():
     languages = calibre_db.speaking_language()
-    translations = babel.list_translations() + [LC('en')]
+    translations = babel.list_translations() + [Locale('en')]
     kobo_support = feature_support['kobo'] and config.config_kobo_sync
     if feature_support['oauth'] and config.config_login_type == 2:
         oauth_status = get_oauth_status()
         local_oauth_check = oauth_check
     else:
@@ -1725,11 +1739,12 @@ def profile():
 def read_book(book_id, book_format):
     book = calibre_db.get_filtered_book(book_id)
     book.ordered_authors = calibre_db.order_authors([book], False)
 
     if not book:
-        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"), category="error")
+        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"),
+              category="error")
         log.debug(u"Oops! Selected book title is unavailable. File does not exist or is not accessible")
         return redirect(url_for("web.index"))
 
     # check if book has a bookmark
     bookmark = None
@@ -1766,11 +1781,12 @@ def read_book(book_id, book_format):
                         title = title + " #" + '{0:.2f}'.format(book.series_index).rstrip('0').rstrip('.')
                 log.debug(u"Start comic reader for %d", book_id)
                 return render_title_template('readcbr.html', comicfile=all_name, title=title,
                                              extension=fileExt)
         log.debug(u"Oops! Selected book title is unavailable. File does not exist or is not accessible")
-        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"), category="error")
+        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"),
+              category="error")
         return redirect(url_for("web.index"))
 
 
 @web.route("/book/<int:book_id>")
 @login_required_if_no_ano
@@ -1780,18 +1796,18 @@ def show_book(book_id):
         read_book = entries[1]
         archived_book = entries[2]
         entry = entries[0]
         entry.read_status = read_book == ub.ReadBook.STATUS_FINISHED
         entry.is_archived = archived_book
-        for index in range(0, len(entry.languages)):
-            entry.languages[index].language_name = isoLanguages.get_language_name(get_locale(), entry.languages[
-                index].lang_code)
+        for lang_index in range(0, len(entry.languages)):
+            entry.languages[lang_index].language_name = isoLanguages.get_language_name(get_locale(), entry.languages[
+                lang_index].lang_code)
         cc = get_cc_columns(filter_config_custom_read=True)
-        book_in_shelfs = []
+        book_in_shelves = []
         shelfs = ub.session.query(ub.BookShelf).filter(ub.BookShelf.book_id == book_id).all()
         for sh in shelfs:
-            book_in_shelfs.append(sh.shelf)
+            book_in_shelves.append(sh.shelf)
 
         entry.tags = sort(entry.tags, key=lambda tag: tag.name)
 
         entry.ordered_authors = calibre_db.order_authors([entry])
 
@@ -1804,13 +1820,13 @@ def show_book(book_id):
                 entry.audioentries.append(media_format.format.lower())
 
         return render_title_template('detail.html',
                                      entry=entry,
                                      cc=cc,
-                                     is_xhr=request.headers.get('X-Requested-With')=='XMLHttpRequest',
+                                     is_xhr=request.headers.get('X-Requested-With') == 'XMLHttpRequest',
                                      title=entry.title,
-                                     books_shelfs=book_in_shelfs,
+                                     books_shelfs=book_in_shelves,
                                      page="book")
     else:
         log.debug(u"Oops! Selected book title is unavailable. File does not exist or is not accessible")
         flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"),
               category="error")
diff --git a/optional-requirements.txt b/optional-requirements.txt
index 04f7bb0c1d..aea1efb7a3 100644
--- a/optional-requirements.txt
+++ b/optional-requirements.txt
@@ -1,7 +1,7 @@
 # GDrive Integration
-google-api-python-client>=1.7.11,<2.37.0
+google-api-python-client>=1.7.11,<2.41.0
 gevent>20.6.0,<22.0.0
 greenlet>=0.4.17,<1.2.0
 httplib2>=0.9.2,<0.21.0
 oauth2client>=4.0.0,<4.1.4
 uritemplate>=3.0.0,<4.2.0
@@ -10,12 +10,12 @@ pyasn1>=0.1.9,<0.5.0
 PyDrive2>=1.3.1,<1.11.0
 PyYAML>=3.12
 rsa>=3.4.2,<4.9.0
 
 # Gmail
-google-auth-oauthlib>=0.4.3,<0.5.0
-google-api-python-client>=1.7.11,<2.37.0
+google-auth-oauthlib>=0.4.3,<0.6.0
+google-api-python-client>=1.7.11,<2.41.0
 
 # goodreads
 goodreads>=0.3.2,<0.4.0
 python-Levenshtein>=0.12.0,<0.13.0
 
@@ -27,11 +27,11 @@ Flask-SimpleLDAP>=1.4.0,<1.5.0
 Flask-Dance>=2.0.0,<5.2.0
 SQLAlchemy-Utils>=0.33.5,<0.39.0
 
 # metadata extraction
 rarfile>=3.2
-scholarly>=1.2.0,<1.6
+scholarly>=1.2.0,<1.7
 markdown2>=2.0.0,<2.5.0
 html2text>=2020.1.16,<2022.1.1
 python-dateutil>=2.1,<2.9.0
 beautifulsoup4>=4.0.1,<4.11.0
 cchardet>=2.0.0,<2.2.0
diff --git a/requirements.txt b/requirements.txt
index d9bed7bb6d..de08fbdaa8 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -10,8 +10,9 @@ pytz>=2016.10
 requests>=2.11.1,<2.28.0
 SQLAlchemy>=1.3.0,<1.5.0
 tornado>=4.1,<6.2
 Wand>=0.4.4,<0.7.0
 unidecode>=0.04.19,<1.4.0
-lxml>=3.8.0,<4.8.0
+lxml>=3.8.0,<4.9.0
 flask-wtf>=0.14.2,<1.1.0
 chardet>=3.0.0,<4.1.0
+advocate>=1.0.0,<1.1.0

Vulnerable code: from cps.editbooks import editbook
    app.register_blueprint(editbook)
    config.store_calibre_uuid(calibre_db, db.Library_Id)
from babel import Locale as LC
from functools import wraps
    allUser = ub.session.query(ub.User).all()
    return render_title_template("admin.html", allUser=allUser, email=email_settings, config=config, commit=commit,
    read_column = calibre_db.session.query(db.Custom_Columns)\
        .filter(and_(db.Custom_Columns.datatype == 'bool', db.Custom_Columns.mark_for_delete == 0)).all()
    restrict_columns = calibre_db.session.query(db.Custom_Columns)\
        .filter(and_(db.Custom_Columns.datatype == 'text', db.Custom_Columns.mark_for_delete == 0)).all()
    translations = [LC('en')] + babel.list_translations()
    translations = babel.list_translations() + [LC('en')]
    allUser = ub.session.query(ub.User)
        allUser = allUser.filter(ub.User.role.op('&')(constants.ROLE_ANONYMOUS) != constants.ROLE_ANONYMOUS)
                                 users=allUser.all(),
            user.default = LC.parse(user.default_language).get_language_name(get_locale())
    locale = babel.list_translations() + [LC('en')]
                                 [constants.ROLE_ADMIN, constants.ROLE_PASSWD, constants.ROLE_EDIT_SHELFS]:
        if not calibre_db.session.query(db.Custom_Columns).filter(db.Custom_Columns.id == column) \
              .filter(and_(db.Custom_Columns.datatype == 'bool', db.Custom_Columns.mark_for_delete == 0)).all():
        if not calibre_db.session.query(db.Custom_Columns).filter(db.Custom_Columns.id == column) \
              .filter(and_(db.Custom_Columns.datatype == 'text', db.Custom_Columns.mark_for_delete == 0)).all():
            or to_save["config_" + str(element['id']) + "_oauth_client_secret"] != element['oauth_client_secret']:
            and to_save["config_" + str(element['id']) + "_oauth_client_secret"]:
        or not config.config_ldap_user_object:
          or config.config_calibre_dir != to_save["config_calibre_dir"]:
        config.store_calibre_uuid(calibre_db, db.Library_Id)
                             and config.config_login_type == constants.LOGIN_LDAP)
    translations = [LC('en')] + babel.list_translations()
    translations = babel.list_translations() + [LC('en')]
import copy
try:
    import unidecode
    use_unidecode = True
except ImportError:
    use_unidecode = False
class Library_Id(Base):
    def formatType(self):
    def __init__(self, text, book):
        self.text = text

        return (self.timestamp.strftime('%Y-%m-%dT%H:%M:%S+00:00') or '')
class Custom_Columns(Base):
class CalibreDB():
            self.initSession(expire_on_commit)
    def initSession(self, expire_on_commit=True):
    def setup_db_cc_classes(self, cc):
                          echo=False,
                          isolation_level="SERIALIZABLE",
                          connect_args={'check_same_thread': False},
                          poolclass=StaticPool)
                database_uuid = local_session().query(Library_Id).one_or_none()
            inst.initSession()
            archived_books = (
                ub.session.query(ub.ArchivedBook)
                    .filter(ub.ArchivedBook.user_id == int(current_user.id))
                    .filter(ub.ArchivedBook.is_archived == True)
                    .all()
            )
                        any(cc_classes[self.config.config_restricted_column].value.in_(pos_cc_list))
                        any(cc_classes[self.config.config_restricted_column].value.in_(neg_cc_list))
                               and_(ub.ReadBook.user_id == int(current_user.id), ub.ReadBook.book_id == Books.id)))
                    query =self.session.query(database, None, ub.ArchivedBook.is_archived)

    def get_search_results(self, term, offset=None, order=None, limit=None, allow_show_archived=False,

        log = logger.create()
        log.error_or_exception(ex)
    pass
editbook = Blueprint('editbook', __name__)
        # check if a element with that name exists
            changed = True
# Modifies different Database objects, first check if elements if elements have to be deleted,
    db_dict = dict([(identifier.type.lower(), identifier) for identifier in db_identifiers ])
@editbook.route("/ajax/delete/<int:book_id>", methods=["POST"])
@editbook.route("/delete/<int:book_id>", defaults={'book_format': ""}, methods=["POST"])
@editbook.route("/delete/<int:book_id>/<string:book_format>", methods=["POST"])
    cc = calibre_db.session.query(db.Custom_Columns). \
        filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).all()
def render_delete_book_result(book_format, jsonResponse, warning, book_id):
        if jsonResponse:
            return json.dumps([warning, {"location": url_for("editbook.edit_book", book_id=book_id),
            return redirect(url_for('editbook.edit_book', book_id=book_id))
        if jsonResponse:
def delete_book_from_table(book_id, book_format, jsonResponse):
                    if jsonResponse:
                        return json.dumps([{"location": url_for("editbook.edit_book", book_id=book_id),
                                           "type": "danger",
                                           "format": "",
                                           "message": error}])
                        return redirect(url_for('editbook.edit_book', book_id=book_id))
                    if jsonResponse:
                        warning = {"location": url_for("editbook.edit_book", book_id=book_id),
                                                "type": "warning",
                                                "format": "",
                                                "message": error}
                if jsonResponse:
                    return json.dumps([{"location": url_for("editbook.edit_book", book_id=book_id),
                    return redirect(url_for('editbook.edit_book', book_id=book_id))
        return render_delete_book_result(book_format, jsonResponse, warning, book_id)
    if jsonResponse:
        return json.dumps({"location": url_for("editbook.edit_book", book_id=book_id),
        return redirect(url_for('editbook.edit_book', book_id=book_id))
    cc = calibre_db.session.query(db.Custom_Columns).filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).all()
        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"), category="error")
    valid_source_formats=list()
    kepub_possible=None
    modif_date = False
        modif_date = True
    return modif_date
    modif_date = False
            modif_date = True
            book.comments.append(db.Comments(text=comments, book=book.id))
            modif_date = True
    return modif_date
def edit_book_languages(languages, book, upload=False, invalid=None):
    if not upload:
    for l in unknown_languages:
        log.error("'%s' is not a valid language", l)
            invalid.append(l)
            raise ValueError(_(u"'%(langname)s' is not a valid language", langname=l))
    if upload and len(input_l) == 1:
    cc = (calibre_db.session.query(db.Custom_Columns)
          .filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions))
          .filter(db.Custom_Columns.id == column_id)
    cc = calibre_db.session.query(db.Custom_Columns).filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).all()
def upload_single_file(request, book, book_id):
    if 'btn-upload-format' in request.files:
        requested_file = request.files['btn-upload-format']
            uploadText=_(u"File format %(ext)s added to %(book)s", ext=file_ext.upper(), book=link)
            WorkerThread.add(current_user.name, TaskUpload(uploadText, escape(book.title)))
def upload_cover(request, book):
    if 'btn-upload-cover' in request.files:
        requested_file = request.files['btn-upload-cover']
    # renamed = False
    input_authors = author_name.split('&')
                one_book.author_sort = one_book.author_sort.replace(sorted_renamed_author, sorted_old_author)
@editbook.route("/admin/book/<int:book_id>", methods=['GET', 'POST'])
    modif_date = False
        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"), category="error")
        modif_date = True
            modif_date = True
        error = False
                                               renamed_author=renamed)
                            modif_date = True
            modif_date |= edit_book_series_index(to_save["series_index"], book)
            modif_date |= edit_book_comments(Markup(to_save['description']).unescape(), book)
            modif_date |= modification
            modif_date |= edit_book_tags(to_save['tags'], book)
            modif_date |= edit_book_series(to_save["series"], book)
            modif_date |= edit_book_publisher(to_save['publisher'], book)
            modif_date |= edit_book_languages(to_save['languages'], book)
            modif_date |= edit_book_ratings(to_save, book)
            modif_date |= edit_all_cc_data(book_id, book, to_save)
            if modif_date:
def prepare_authors_on_upload(title, authr):
    if title != _(u'Unknown') and authr != _(u'Unknown'):
        entry = calibre_db.check_exists_book(authr, title)
        if entry:
            log.info("Uploaded book probably exists in library")
            flash(_(u"Uploaded book probably exists in the library, consider to change before upload new: ")
                  + Markup(render_title_template('book_exists_flash.html', entry=entry)), category="warning")

def create_book_on_upload(modif_date, meta):
    # combine path and normalize path from windows systems
    modif_date |= modify_database_object(input_authors, db_book.authors, db.Authors, calibre_db.session,
                                         'author')
    modif_date |= edit_book_series_index(meta.series_id, db_book)
    invalid=[]
    modif_date |= edit_book_languages(meta.languages, db_book, upload=True, invalid=invalid)
        for l in invalid:
            flash(_(u"'%(langname)s' is not a valid language", langname=l), category="warning")
    modif_date |= edit_book_tags(meta.tags, db_book)
    modif_date |= edit_book_publisher(meta.publisher, db_book)
    modif_date |= edit_book_series(meta.series, db_book)
@editbook.route("/upload", methods=["POST"])
                modif_date = False
                db_book, input_authors, title_dir, renamed_authors = create_book_on_upload(modif_date, meta)
                # Comments needs book id therefore only possible after flush
                modif_date |= edit_book_comments(Markup(meta.description).unescape(), db_book)
                uploadText = _(u"File %(file)s uploaded", file=link)
                WorkerThread.add(current_user.name, TaskUpload(uploadText, escape(title)))
                        resp = {"location": url_for('editbook.edit_book', book_id=book_id)}
@editbook.route("/admin/book/convert/<int:book_id>", methods=['POST'])
        return redirect(url_for('editbook.edit_book', book_id=book_id))
                    book_format=book_format_to),
                    category="success")
    return redirect(url_for('editbook.edit_book', book_id=book_id))
@editbook.route("/ajax/getcustomenum/<int:c_id>")
    cc = (calibre_db.session.query(db.Custom_Columns)
              .filter(db.Custom_Columns.id == c_id)
              .filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).one_or_none())
@editbook.route("/ajax/editbooks/<param>", methods=['POST'])
                                        'newValue': ', '.join([publisher.name for publisher in book.publishers])}),
                                mimetype='application/json')
            sort = book.sort
            ret = Response(json.dumps({'success': True,
                                       'newValue':  ' & '.join([author.replace('|',',') for author in input_authors])}),
                           mimetype='application/json')
                                                message="Book {} archivebit set to: {}".format(book.id, vals['value']))
            book.sort = sort
@editbook.route("/ajax/sort_value/<field>/<int:bookid>")
@editbook.route("/ajax/simulatemerge", methods=['POST'])
@editbook.route("/ajax/mergebooks", methods=['POST'])
            to_name = helper.get_valid_filename(to_book.title, chars=96) + ' - ' + \
                      helper.get_valid_filename(to_book.authors[0].name, chars=96)
                    delete_book_from_table(from_book.id,"", True)
@editbook.route("/ajax/xchange", methods=['POST'])
            modif_date = False
                modif_date = True
                                           renamed_author=renamed)
            if modif_date:
    cfname = tree.xpath('n:rootfiles/n:rootfile/@full-path', namespaces=ns)[0]
    cf = epub_zip.read(cfname)
    coverpath = os.path.dirname(cfname)
    cover_file = parse_epub_cover(ns, tree, epub_zip, coverpath, tmp_file_path)
    if len(cover_section) > 0:
        cover_file = _extract_cover(epub_zip, cover_section[0], cover_path, tmp_file_path)
    else:
            if cover_file:
                break
import unicodedata
from urllib.parse import urlparse
    import unidecode
    use_unidecode = True
    use_unidecode = False
from . import logger, config, get_locale, db, ub, kobo_sync_status
    if 'AZW3' in formats and not 'MOBI' in formats:
    EXTENSIONS_READER = {'TXT', 'PDF', 'EPUB', 'CBZ', 'CBT', 'CBR', 'DJVU'}
            if ele.format.upper() in EXTENSIONS_READER:
            EmailText = _(u"%(book)s send to Kindle", book=link)
                             EmailText, _(u'This e-mail has been sent via Calibre-Web.')))
    if use_unidecode:
        if config.config_unicode_filename:
            value = (unidecode.unidecode(value))
    else:
        value = value.replace(u'§', u'SS')
        value = value.replace(u'ß', u'ss')
        value = unicodedata.normalize('NFKD', value)
        re_slugify = re.compile(r'[\W\s-]', re.UNICODE)
        value = re_slugify.sub('', value)
            readBook = ub.ReadBook(user_id=current_user.id, book_id = book_id)
            readBook.read_status = ub.ReadBook.STATUS_FINISHED
            book = readBook
        except (OperationalError, InvalidRequestError) as e:
            log.error(u"Read status could not set: {}".format(e))
            return _("Read status could not set: {}".format(e.orig))
                except (IOError, OSError) as e:
                    log.error("Deleting book %s failed: %s", book.id, e)
                    return False, _("Deleting book %(id)s failed: %(message)s", id=book.id, message=e)
                    except (IOError, OSError) as e:
                        log.error("Deleting authorpath for book %s failed: %s", book.id, e)
                               + get_valid_filename(new_author.name, chars=42)
                            os.path.normcase(os.path.join(all_new_path,
                                                          all_new_name + '.' + file_format.format.lower())))
                        gFile = gd.getFileFromEbooksFolder(all_new_path,
                                                           file_format.name + '.' + file_format.format.lower())
                        if gFile:
                            gd.moveGdriveFileRemote(gFile, all_new_name + u'.' + file_format.format.lower())
                            gd.updateDatabaseOnEdit(gFile['id'], all_new_name + u'.' + file_format.format.lower())
                gFile = gd.getFileFromEbooksFolder(None, old_author_dir)
                if gFile:
                    gd.moveGdriveFolderRemote(gFile, new_author_rename_dir)
                    except (OSError) as ex:
    error = False
                get_valid_filename(first_author, chars=42) + \
                filename_ext
        gFile = gd.getFileFromEbooksFolder(os.path.dirname(book.path), titledir)
        if gFile:
            gd.moveGdriveFileRemote(gFile, new_titledir)
            gd.updateDatabaseOnEdit(gFile['id'], book.path)     # only child folder affected
        gFile = gd.getFileFromEbooksFolder(os.path.dirname(book.path), new_titledir)
        if gFile:
            gd.moveGdriveFolderRemote(gFile, new_authordir)
            gd.updateDatabaseOnEdit(gFile['id'], book.path)
            else: # path is valid copy only files to new location (merge)
                                        os.path.normcase(os.path.join(new_path + dir_name[len(path):], file)))
        localbook.path = os.path.join(new_authordir, new_titledir).replace('\\','/')
        gFile = gd.getFileFromEbooksFolder(book.path, name)
        gFile = gd.getFileFromEbooksFolder(os.path.dirname(book.path), book.path.split('/')[1])
    if gFile:
        gd.deleteDatabaseEntry(gFile['id'])
        gFile.Trash()
    inpt = [ " ".join(inp.split()) for inp in inpt]
        raise Exception (_(u"This username is already taken"))
        if not cli.allow_localhost:
            # 127.0.x.x, localhost, [::1], [::ffff:7f00:1]
            ip = socket.getaddrinfo(urlparse(url).hostname, 0)[0][4][0]
            if ip.startswith("127.") or ip.startswith('::ffff:7f') or ip == "::1" or ip == "0.0.0.0" or ip == "::":
                log.error("Localhost was accessed for cover upload")
                return False, _("You are not allowed to access localhost for cover uploads")
        img = requests.get(url, timeout=(10, 200), allow_redirects=False)      # ToDo: Error Handling
            gd.uploadFileToEbooksFolder(os.path.join(book_path, 'cover.jpg').replace("\\","/"),
        #startTime = time.time()
        #log.debug('%s', time.time() - startTime)
def check_unrar(unrarLocation):
    if not unrarLocation:
    if not os.path.exists(unrarLocation):
        unrarLocation = [unrarLocation]
        value = process_wait(unrarLocation, pattern='UNRAR (.*) freeware')
    retVal = ""
        retVal = format_unit(runtime.days, 'duration-day', length="long", locale=get_locale()) + ', '
        retVal += '{:d}:{:02d}:{:02d}s'.format(hours, minutes, seconds)
        retVal += '{:2d}:{:02d}s'.format(minutes, seconds)
        retVal += '{:2d}s'.format(seconds)
    return retVal
    tmpcc = calibre_db.session.query(db.Custom_Columns)\
        .filter(db.Custom_Columns.datatype.notin_(db.cc_exceptions)).all()
from tornado.httputil import HTTPServerRequest
    shift = 0
    off = int(request.args.get("offset") or 0)
    entries = calibre_db.session.query(func.upper(func.substr(db.Books.sort, 1, 1)).label('id'))\
        .filter(calibre_db.common_filters()).group_by(func.upper(func.substr(db.Books.sort, 1, 1))).all()

    elements = []
    if off == 0:
        elements.append({'id': "00", 'name':_("All")})
        shift = 1
    for entry in entries[
                 off + shift - 1:
                 int(off + int(config.config_books_per_page) - shift)]:
        elements.append({'id': entry.id, 'name': entry.id})

    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
                            len(entries) + 1)
    return render_xml_template('feed.xml',
                               letterelements=elements,
                               folder='opds.feed_letter_books',
                               pagination=pagination)
        downloadBook = calibre_db.get_book(book.Downloads.book_id)
        if downloadBook:
    numBooks = entries.__len__()
                            config.config_books_per_page, numBooks)
    shift = 0
    off = int(request.args.get("offset") or 0)
    entries = calibre_db.session.query(func.upper(func.substr(db.Authors.sort, 1, 1)).label('id'))\
        .join(db.books_authors_link).join(db.Books).filter(calibre_db.common_filters())\
        .group_by(func.upper(func.substr(db.Authors.sort, 1, 1))).all()

    elements = []
    if off == 0:
        elements.append({'id': "00", 'name':_("All")})
        shift = 1
    for entry in entries[
                 off + shift - 1:
                 int(off + int(config.config_books_per_page) - shift)]:
        elements.append({'id': entry.id, 'name': entry.id})

    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
                            len(entries) + 1)
    return render_xml_template('feed.xml',
                               letterelements=elements,
                               folder='opds.feed_letter_author',
                               pagination=pagination)
    off = request.args.get("offset") or 0
    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
                                                        db.Books,
                                                        db.Books.authors.any(db.Authors.id == book_id),
                                                        [db.Books.timestamp.desc()])
    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
    off = request.args.get("offset") or 0
    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
                                                        db.Books,
                                                        db.Books.publishers.any(db.Publishers.id == book_id),
                                                        [db.Books.timestamp.desc()])
    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
    shift = 0
    off = int(request.args.get("offset") or 0)
    entries = calibre_db.session.query(func.upper(func.substr(db.Tags.name, 1, 1)).label('id'))\
        .join(db.books_tags_link).join(db.Books).filter(calibre_db.common_filters())\
        .group_by(func.upper(func.substr(db.Tags.name, 1, 1))).all()
    elements = []
    if off == 0:
        elements.append({'id': "00", 'name':_("All")})
        shift = 1
    for entry in entries[
                 off + shift - 1:
                 int(off + int(config.config_books_per_page) - shift)]:
        elements.append({'id': entry.id, 'name': entry.id})
    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
                            len(entries) + 1)
    return render_xml_template('feed.xml',
                               letterelements=elements,
                               folder='opds.feed_letter_category',
                               pagination=pagination)
    off = request.args.get("offset") or 0
    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
                                                        db.Books,
                                                        db.Books.tags.any(db.Tags.id == book_id),
                                                        [db.Books.timestamp.desc()])
    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
    shift = 0
    off = int(request.args.get("offset") or 0)
    entries = calibre_db.session.query(func.upper(func.substr(db.Series.sort, 1, 1)).label('id'))\
        .join(db.books_series_link).join(db.Books).filter(calibre_db.common_filters())\
        .group_by(func.upper(func.substr(db.Series.sort, 1, 1))).all()
    elements = []
    if off == 0:
        elements.append({'id': "00", 'name':_("All")})
        shift = 1
    for entry in entries[
                 off + shift - 1:
                 int(off + int(config.config_books_per_page) - shift)]:
        elements.append({'id': entry.id, 'name': entry.id})
    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
                            len(entries) + 1)
    return render_xml_template('feed.xml',
                               letterelements=elements,
                               folder='opds.feed_letter_series',
                               pagination=pagination)
                               (db.Ratings.rating / 2).label('name')) \
    off = request.args.get("offset") or 0
    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
                                                        db.Books,
                                                        db.Books.ratings.any(db.Ratings.id == book_id),
                                                        [db.Books.timestamp.desc()])
    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
    # workaround, loading the user from the request and checking it's download rights here
        ip_Address = request.headers.get('X-Forwarded-For', request.remote_addr)
        log.warning('OPDS Login failed for user "%s" IP-address: %s', username.decode('utf-8'), ip_Address)
@opds.route("/opds/thumb_240_240/<book_id>")
@opds.route("/opds/cover_240_240/<book_id>")
@opds.route("/opds/cover_90_90/<book_id>")
@opds.route("/opds/cover/<book_id>")
@requires_basic_auth_if_no_ano
def feed_get_cover(book_id):
    return get_book_cover(book_id)


@opds.route("/opds/readbooks")
@requires_basic_auth_if_no_ano
def feed_read_books():
    result, pagination = render_read_books(int(off) / (int(config.config_books_per_page)) + 1, True, True)
    return render_xml_template('feed.xml', entries=result, pagination=pagination)
@opds.route("/opds/unreadbooks")
@requires_basic_auth_if_no_ano
def feed_unread_books():
    off = request.args.get("offset") or 0
    result, pagination = render_read_books(int(off) / (int(config.config_books_per_page)) + 1, False, True)
    return render_xml_template('feed.xml', entries=result, pagination=pagination)
    # right_edge: last right_edges count of all pages are shown as number, means, if 10 pages are paginated -> 9,10 shwn
    # left_edge: first left_edges count of all pages are shown as number                                    -> 1,2 shwn
    # left_current: left_current count below current page are shown as number, means if current page 5      -> 3,4 shwn
    # left_current: right_current count above current page are shown as number, means if current page 5     -> 6,7 shwn
try:
    from functools import wraps
except ImportError:
    pass  # We're not using Python 3
      <form class="padded-bottom" action="{{ url_for('editbook.convert_bookformat', book_id=book.id) }}" method="post" id="book_convert_frm">
<form role="form" action="{{ url_for('editbook.edit_book', book_id=book.id) }}" method="post" enctype="multipart/form-data" id="book_edit_frm">
        data-editable-url="{{ url_for('editbook.edit_list_book', param=parameter)}}"
            <th data-field="series_index" id="series_index" data-visible="{{visiblility.get('series_index')}}" data-edit-validate="{{ _('This Field is Required') }}" data-sortable="true" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.01" data-editable-min="0" data-editable-url="{{ url_for('editbook.edit_list_book', param='series_index')}}" data-edit="true" data-editable-title="{{_('Enter Title')}}"{% endif %}>{{_('Series Index')}}</th>
            <th data-field="comments" id="comments" data-escape="true" data-editable-mode="popup"  data-visible="{{visiblility.get('comments')}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="wysihtml5" data-editable-url="{{ url_for('editbook.edit_list_book', param='comments')}}" data-edit="true" data-editable-title="{{_('Enter comments')}}"{% endif %}>{{_('Comments')}}</th>
                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="1" data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-formatter="ratingFormatter" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.5" data-editable-step="1" data-editable-min="1" data-editable-max="5" data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.01" data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="select" data-editable-source={{ url_for('editbook.table_get_custom_enum', c_id=c.id)  }} data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
                  <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-escape="true" data-editable-mode="popup"  data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="wysihtml5" data-editable-url="{{ url_for('editbook.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
          <a href="{{identifier}}" target="_blank" class="btn btn-xs btn-success" role="button">{{identifier.formatType()}}</a>
          <a href="{{ url_for('editbook.edit_book', book_id=entry.id) }}" class="btn btn-sm btn-primary" id="edit_book" role="button"><span class="glyphicon glyphicon-edit"></span> {{_('Edit Metadata')}}</a>
                    <form id="form-upload" class="navbar-form" action="{{ url_for('editbook.upload') }}" data-title="{{_('Uploading...')}}" data-footer="{{_('Close')}}" data-failed="{{_('Error')}}" data-message="{{_('Upload done, processing, please wait...')}}" method="post" enctype="multipart/form-data">
try:
    from functools import wraps
except ImportError:
    pass  # We're not using Python 3
from babel import Locale as LC

    resp.headers['Content-Security-Policy'] = "default-src 'self'" + ''.join([' '+host for host in config.config_trustedhosts.strip().split(',')]) + " 'unsafe-inline' 'unsafe-eval'; font-src 'self' data:; img-src 'self' data:"
    if request.endpoint == "editbook.edit_book" or config.config_use_google_drive:
    lbookmark = ub.Bookmark(user_id=current_user.id,
                            book_id=book_id,
                            format=book_format,
                            bookmark_key=bookmark_key)
    ub.session.merge(lbookmark)
    is_archived = change_archived_books(book_id, message="Book {} archivebit toggled".format(book_id))
        														False, 0,
        # Unary expression comparsion only working (for this expression) in sqlalchemy 1.4+
        #if not (order[0][0].compare(func.count(ub.Downloads.book_id).desc()) or
        #        order[0][0].compare(func.count(ub.Downloads.book_id).asc())):
        all_books = ub.session.query(ub.Downloads, func.count(ub.Downloads.book_id))\
            downloadBook = calibre_db.session.query(db.Books).filter(calibre_db.common_filters()).filter(
            if downloadBook:
                entries.append(downloadBook)
        numBooks = entries.__len__()
        pagination = Pagination(page, config.config_books_per_page, numBooks)
            if not calibre_db.session.query(db.Books).filter(calibre_db.common_filters()) \
                             .filter(db.Books.id == book.id).first():
                                     title=_(u"Downloaded books by %(user)s",user=user.name),
            return [] # ToDo: Handle error Case for opds
            pagename = "read"
            pagename = "unread"
                                     title=name, page=pagename, order=order[1])
    archived_books = (
        ub.session.query(ub.ArchivedBook)
        .filter(ub.ArchivedBook.user_id == int(current_user.id))
        .filter(ub.ArchivedBook.is_archived == True)
        .all()
    )
    pagename = "archived"
                                 title=name, page=pagename, order=sort_param[1])
    tags = calibre_db.session.query(db.Tags)\
        .join(db.books_tags_link)\
        .join(db.Books)\
        .group_by(text('books_tags_link.tag'))\
    series = calibre_db.session.query(db.Series)\
        .join(db.books_series_link)\
        .join(db.Books)\
        .group_by(text('books_series_link.series'))\
        .order_by(db.Series.name)\
    shelves = ub.session.query(ub.Shelf)\
        .filter(or_(ub.Shelf.is_public == 1, ub.Shelf.user_id == int(current_user.id)))\
    extensions = calibre_db.session.query(db.Data)\
        .join(db.Books)\
        .group_by(db.Data.format)\
                                 series=series,shelves=shelves, title=_(u"Advanced Search"), cc=cc, page="advsearch")
                                                                      False,
    search = request.args.get("search")
        join = db.books_authors_link, db.Books.id == db.books_authors_link.c.book, db.Authors, \
               db.books_series_link, db.Books.id == db.books_series_link.c.book, db.Series
    total_count = filtered_count = calibre_db.session.query(db.Books).filter(calibre_db.common_filters(allow_show_archived=True)).count()
        if search:
            books = calibre_db.search_query(search, config.config_read_column).all()
                                                          int(current_user.id) == ub.ArchivedBook.user_id))
    elif search:
        entries, filtered_count, __ = calibre_db.get_search_results(search,
                                                                    [order,''],
                                                                    True,
        for index in range(0, len(val.languages)):
            val.languages[index].language_name = isoLanguages.get_language_name(get_locale(), val.languages[
                index].lang_code)
        charlist = calibre_db.session.query(func.upper(func.substr(db.Authors.sort, 1, 1)).label('char')) \
            .join(db.books_authors_link).join(db.Books).filter(calibre_db.common_filters()) \
            .group_by(func.upper(func.substr(db.Authors.sort, 1, 1))).all()
        autor_copy = copy.deepcopy(entries)
        for entry in autor_copy:
        return render_title_template('list.html', entries=autor_copy, folder='web.books_list', charlist=charlist,
        entries = ub.session.query(ub.User, func.count(ub.Downloads.book_id).label('count'))\
        charlist = ub.session.query(func.upper(func.substr(ub.User.name, 1, 1)).label('char')) \
        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=charlist,
        charlist = calibre_db.session.query(func.upper(func.substr(db.Publishers.name, 1, 1)).label('char')) \
            .join(db.books_publishers_link).join(db.Books).filter(calibre_db.common_filters()) \
            .group_by(func.upper(func.substr(db.Publishers.name, 1, 1))).all()
        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=charlist,
            charlist = calibre_db.session.query(func.upper(func.substr(db.Series.sort, 1, 1)).label('char')) \
                .join(db.books_series_link).join(db.Books).filter(calibre_db.common_filters()) \
                .group_by(func.upper(func.substr(db.Series.sort, 1, 1))).all()
            return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=charlist,
                .join(db.books_series_link).join(db.Series).filter(calibre_db.common_filters())\
            charlist = calibre_db.session.query(func.upper(func.substr(db.Series.sort, 1, 1)).label('char')) \
                .join(db.books_series_link).join(db.Books).filter(calibre_db.common_filters()) \
                .group_by(func.upper(func.substr(db.Series.sort, 1, 1))).all()

            return render_title_template('grid.html', entries=entries, folder='web.books_list', charlist=charlist,
                                   (db.Ratings.rating / 2).label('name')) \
        charlist = list()
            if upper_lang not in charlist:
                charlist.append(upper_lang)
                                     charlist=charlist, title=_(u"Languages"), page="langlist",
        charlist = calibre_db.session.query(func.upper(func.substr(db.Tags.name, 1, 1)).label('char')) \
            .join(db.books_tags_link).join(db.Books).filter(calibre_db.common_filters()) \
            .group_by(func.upper(func.substr(db.Tags.name, 1, 1))).all()
        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=charlist,
def adv_search_extension(q, include_extension_inputs, exclude_extension_inputs):
    return q
    q = q.outerjoin(ub.BookShelf, db.Books.id == ub.BookShelf.book_id)\
    elements = {'tag': db.Tags, 'serie':db.Series, 'shelf':ub.Shelf}
    q = query.outerjoin(db.books_series_link, db.Books.id == db.books_series_link.c.book)\
        .outerjoin(db.Series)\
    searchterm = []
                searchterm.extend([u"{} >= {}".format(c.name,
                                                      format_date(datetime.strptime(column_start, "%Y-%m-%d").date(),
                                                                      format='medium',
                                                                      locale=get_locale())
                                                      )])
                searchterm.extend([u"{} <= {}".format(c.name,
                                                      format_date(datetime.strptime(column_end, "%Y-%m-%d").date(),
                                                                      format='medium',
                                                                      locale=get_locale())
                                                      )])
            searchterm.extend([(u"{}: {}".format(c.name, term.get('custom_column_' + str(c.id))))])

    if any(tags.values()) or author_name or book_title or publisher or pub_start or pub_end or rating_low \
       or rating_high or description or cc_present or read_status:
        searchterm, pub_start, pub_end = extend_search_term(searchterm,
                                                            author_name,
                                                            book_title,
                                                            publisher,
                                                            pub_start,
                                                            pub_end,
                                                            tags,
                                                            rating_high,
                                                            rating_low,
                                                            read_status)
        q = adv_search_tag(q, tags['include_tag'], tags['exclude_tag'])
        q = adv_search_serie(q, tags['include_serie'], tags['exclude_serie'])
        q = adv_search_extension(q, tags['include_extension'], tags['exclude_extension'])
        q = adv_search_language(q, tags['include_language'], tags['exclude_language'])
        q = adv_search_ratings(q, rating_high, rating_low)
                                 adv_searchterm=searchterm,
                and user.name != "Guest":
                ip_Address = request.headers.get('X-Forwarded-For', request.remote_addr)
                log.warning('LDAP Login failed for user "%s" IP-address: %s', form['username'], ip_Address)
            ip_Address = request.headers.get('X-Forwarded-For', request.remote_addr)
                        log.info('Password reset for user "%s" IP-address: %s', form['username'], ip_Address)
                    log.warning('Username missing for password reset IP-address: %s', ip_Address)
                    log.warning('Login failed for user "%s" IP-address: %s', form['username'], ip_Address)
        ub.delete_user_session(current_user.id, flask_session.get('_id',""))
                # Query User name, if not existing, change
    translations = babel.list_translations() + [LC('en')]
        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"), category="error")
        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"), category="error")
        for index in range(0, len(entry.languages)):
            entry.languages[index].language_name = isoLanguages.get_language_name(get_locale(), entry.languages[
                index].lang_code)
        book_in_shelfs = []
            book_in_shelfs.append(sh.shelf)
                                     is_xhr=request.headers.get('X-Requested-With')=='XMLHttpRequest',
                                     books_shelfs=book_in_shelfs,
google-api-python-client>=1.7.11,<2.37.0
google-auth-oauthlib>=0.4.3,<0.5.0
google-api-python-client>=1.7.11,<2.37.0
scholarly>=1.2.0,<1.6
lxml>=3.8.0,<4.8.0
Fixed code: from cps.editbooks import EditBook
    app.register_blueprint(EditBook)
    config.store_calibre_uuid(calibre_db, db.LibraryId)
from functools import wraps
from babel import Locale
    all_user = ub.session.query(ub.User).all()
    return render_title_template("admin.html", allUser=all_user, email=email_settings, config=config, commit=commit,
    read_column = calibre_db.session.query(db.CustomColumns)\
        .filter(and_(db.CustomColumns.datatype == 'bool', db.CustomColumns.mark_for_delete == 0)).all()
    restrict_columns = calibre_db.session.query(db.CustomColumns)\
        .filter(and_(db.CustomColumns.datatype == 'text', db.CustomColumns.mark_for_delete == 0)).all()
    translations = [Locale('en')] + babel.list_translations()
    translations = babel.list_translations() + [Locale('en')]
    all_user = ub.session.query(ub.User)
        all_user = all_user.filter(ub.User.role.op('&')(constants.ROLE_ANONYMOUS) != constants.ROLE_ANONYMOUS)
                                 users=all_user.all(),
            user.default = Locale.parse(user.default_language).get_language_name(get_locale())
    locale = babel.list_translations() + [Locale('en')]
                            [constants.ROLE_ADMIN, constants.ROLE_PASSWD, constants.ROLE_EDIT_SHELFS]:
        if not calibre_db.session.query(db.CustomColumns).filter(db.CustomColumns.id == column) \
              .filter(and_(db.CustomColumns.datatype == 'bool', db.CustomColumns.mark_for_delete == 0)).all():
        if not calibre_db.session.query(db.CustomColumns).filter(db.CustomColumns.id == column) \
              .filter(and_(db.CustomColumns.datatype == 'text', db.CustomColumns.mark_for_delete == 0)).all():
                or to_save["config_" + str(element['id']) + "_oauth_client_secret"] != element['oauth_client_secret']:
                and to_save["config_" + str(element['id']) + "_oauth_client_secret"]:
            or not config.config_ldap_user_object:
    db_valid = False
            or config.config_calibre_dir != to_save["config_calibre_dir"]:
        config.store_calibre_uuid(calibre_db, db.LibraryId)
                            and config.config_login_type == constants.LOGIN_LDAP)
    translations = [Locale('en')] + babel.list_translations()
    translations = babel.list_translations() + [Locale('en')]
import unidecode
class LibraryId(Base):
    def format_type(self):
    def __init__(self, comment, book):
        self.text = comment
        return self.timestamp.strftime('%Y-%m-%dT%H:%M:%S+00:00') or ''
class CustomColumns(Base):
class CalibreDB:
            self.init_session(expire_on_commit)
    def init_session(self, expire_on_commit=True):
    def setup_db_cc_classes(cls, cc):
                                         echo=False,
                                         isolation_level="SERIALIZABLE",
                                         connect_args={'check_same_thread': False},
                                         poolclass=StaticPool)
                database_uuid = local_session().query(LibraryId).one_or_none()
            inst.init_session()
            archived_books = (ub.session.query(ub.ArchivedBook)
                              .filter(ub.ArchivedBook.user_id == int(current_user.id))
                              .filter(ub.ArchivedBook.is_archived == True)
                              .all())
                    any(cc_classes[self.config.config_restricted_column].value.in_(pos_cc_list))
                    any(cc_classes[self.config.config_restricted_column].value.in_(neg_cc_list))
                                    and_(ub.ReadBook.user_id == int(current_user.id), ub.ReadBook.book_id == Books.id)))
                    query = self.session.query(database, None, ub.ArchivedBook.is_archived)
    def get_search_results(self, term, offset=None, order=None, limit=None,
        _log = logger.create()
        _log.error_or_exception(ex)
    clean_html = None
EditBook = Blueprint('edit-book', __name__)



        # check if an element with that name exists
# Modifies different Database objects, first check if elements have to be deleted,
    db_dict = dict([(identifier.type.lower(), identifier) for identifier in db_identifiers])

@EditBook.route("/ajax/delete/<int:book_id>", methods=["POST"])
@EditBook.route("/delete/<int:book_id>", defaults={'book_format': ""}, methods=["POST"])
@EditBook.route("/delete/<int:book_id>/<string:book_format>", methods=["POST"])
    cc = calibre_db.session.query(db.CustomColumns). \
        filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).all()
def render_delete_book_result(book_format, json_response, warning, book_id):
        if json_response:
            return json.dumps([warning, {"location": url_for("edit-book.edit_book", book_id=book_id),
            return redirect(url_for('edit-book.edit_book', book_id=book_id))
        if json_response:
def delete_book_from_table(book_id, book_format, json_response):
                    if json_response:
                        return json.dumps([{"location": url_for("edit-book.edit_book", book_id=book_id),
                                            "type": "danger",
                                            "format": "",
                                            "message": error}])
                        return redirect(url_for('edit-book.edit_book', book_id=book_id))
                    if json_response:
                        warning = {"location": url_for("edit-book.edit_book", book_id=book_id),
                                   "type": "warning",
                                   "format": "",
                                   "message": error}
                if json_response:
                    return json.dumps([{"location": url_for("edit-book.edit_book", book_id=book_id),
                    return redirect(url_for('edit-book.edit_book', book_id=book_id))
        return render_delete_book_result(book_format, json_response, warning, book_id)
    if json_response:
        return json.dumps({"location": url_for("edit-book.edit_book", book_id=book_id),
        return redirect(url_for('edit-book.edit_book', book_id=book_id))
    cc = calibre_db.session.query(db.CustomColumns).filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).all()
        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"),
              category="error")
    valid_source_formats = list()
    kepub_possible = None

    modify_date = False
        modify_date = True
    return modify_date
    modify_date = False
            modify_date = True
            book.comments.append(db.Comments(comment=comments, book=book.id))
            modify_date = True
    return modify_date
def edit_book_languages(languages, book, upload_mode=False, invalid=None):
    if not upload_mode:
    for lang in unknown_languages:
        log.error("'%s' is not a valid language", lang)
            invalid.append(lang)
            raise ValueError(_(u"'%(langname)s' is not a valid language", langname=lang))
    if upload_mode and len(input_l) == 1:

    cc = (calibre_db.session.query(db.CustomColumns)
          .filter(db.CustomColumns.datatype.notin_(db.cc_exceptions))
          .filter(db.CustomColumns.id == column_id)

    cc = calibre_db.session.query(db.CustomColumns).filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).all()


def upload_single_file(file_request, book, book_id):
    if 'btn-upload-format' in file_request.files:
        requested_file = file_request.files['btn-upload-format']
            upload_text = _(u"File format %(ext)s added to %(book)s", ext=file_ext.upper(), book=link)
            WorkerThread.add(current_user.name, TaskUpload(upload_text, escape(book.title)))
def upload_cover(cover_request, book):
    if 'btn-upload-cover' in cover_request.files:
        requested_file = cover_request.files['btn-upload-cover']
    input_authors, renamed = prepare_authors(author_name)
    '''input_authors = author_name.split('&')
                one_book.author_sort = one_book.author_sort.replace(sorted_renamed_author, sorted_old_author)'''
@EditBook.route("/admin/book/<int:book_id>", methods=['GET', 'POST'])
    modify_date = False
        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"),
              category="error")
        modify_date = True
            modify_date = True
        error = ""
                                                renamed_author=renamed)
                            modify_date = True
            modify_date |= edit_book_series_index(to_save["series_index"], book)
            modify_date |= edit_book_comments(Markup(to_save['description']).unescape(), book)
            modify_date |= modification
            modify_date |= edit_book_tags(to_save['tags'], book)
            modify_date |= edit_book_series(to_save["series"], book)
            modify_date |= edit_book_publisher(to_save['publisher'], book)
            modify_date |= edit_book_languages(to_save['languages'], book)
            modify_date |= edit_book_ratings(to_save, book)
            modify_date |= edit_all_cc_data(book_id, book, to_save)
            if modify_date:
def prepare_authors(authr):
    return input_authors, renamed


def prepare_authors_on_upload(title, authr):
    if title != _(u'Unknown') and authr != _(u'Unknown'):
        entry = calibre_db.check_exists_book(authr, title)
        if entry:
            log.info("Uploaded book probably exists in library")
            flash(_(u"Uploaded book probably exists in the library, consider to change before upload new: ")
                  + Markup(render_title_template('book_exists_flash.html', entry=entry)), category="warning")

    input_authors, renamed = prepare_authors(authr)
def create_book_on_upload(modify_date, meta):
    # combine path and normalize path from Windows systems
    modify_date |= modify_database_object(input_authors, db_book.authors, db.Authors, calibre_db.session,
                                          'author')
    modify_date |= edit_book_series_index(meta.series_id, db_book)
    invalid = []
    modify_date |= edit_book_languages(meta.languages, db_book, upload_mode=True, invalid=invalid)
        for lang in invalid:
            flash(_(u"'%(langname)s' is not a valid language", langname=lang), category="warning")
    modify_date |= edit_book_tags(meta.tags, db_book)
    modify_date |= edit_book_publisher(meta.publisher, db_book)
    modify_date |= edit_book_series(meta.series, db_book)

@EditBook.route("/upload", methods=["POST"])
                modify_date = False
                db_book, input_authors, title_dir, renamed_authors = create_book_on_upload(modify_date, meta)
                # Comments need book id therefore only possible after flush
                modify_date |= edit_book_comments(Markup(meta.description).unescape(), db_book)
                upload_text = _(u"File %(file)s uploaded", file=link)
                WorkerThread.add(current_user.name, TaskUpload(upload_text, escape(title)))
                        resp = {"location": url_for('edit-book.edit_book', book_id=book_id)}
@EditBook.route("/admin/book/convert/<int:book_id>", methods=['POST'])
        return redirect(url_for('edit-book.edit_book', book_id=book_id))
                book_format=book_format_to),
              category="success")
    return redirect(url_for('edit-book.edit_book', book_id=book_id))

@EditBook.route("/ajax/getcustomenum/<int:c_id>")
    cc = (calibre_db.session.query(db.CustomColumns)
          .filter(db.CustomColumns.id == c_id)
          .filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).one_or_none())
@EditBook.route("/ajax/editbooks/<param>", methods=['POST'])
    sort_param = ""
                                       'newValue': ', '.join([publisher.name for publisher in book.publishers])}),
                               mimetype='application/json')
            sort_param = book.sort
            ret = Response(json.dumps({
                'success': True,
                'newValue':  ' & '.join([author.replace('|', ',') for author in input_authors])}),
                mimetype='application/json')
                                                message="Book {} archive bit set to: {}".format(book.id, vals['value']))
            book.sort = sort_param
@EditBook.route("/ajax/sort_value/<field>/<int:bookid>")
@EditBook.route("/ajax/simulatemerge", methods=['POST'])
@EditBook.route("/ajax/mergebooks", methods=['POST'])
            to_name = helper.get_valid_filename(to_book.title,
                                                chars=96) + ' - ' + helper.get_valid_filename(to_book.authors[0].name,
                                                                                              chars=96)
                    delete_book_from_table(from_book.id, "", True)
@EditBook.route("/ajax/xchange", methods=['POST'])
    edited_books_id = False
            modify_date = False
                modify_date = True
                                            renamed_author=renamed)
            if modify_date:
    cf_name = tree.xpath('n:rootfiles/n:rootfile/@full-path', namespaces=ns)[0]
    cf = epub_zip.read(cf_name)
    cover_path = os.path.dirname(cf_name)
    cover_file = parse_epub_cover(ns, tree, epub_zip, cover_path, tmp_file_path)
    # if len(cover_section) > 0:
    for cs in cover_section:
        cover_file = _extract_cover(epub_zip, cs, cover_path, tmp_file_path)
        if cover_file:
            break
    if not cover_file:
            if cover_file: break
import unidecode

    import advocate
    from advocate.exceptions import UnacceptableAddressException
    use_advocate = True
    use_advocate = False
    advocate = requests
    UnacceptableAddressException = MissingSchema = BaseException
from . import logger, config, get_locale, db, ub
    if 'AZW3' in formats and 'MOBI' not in formats:
    extensions_reader = {'TXT', 'PDF', 'EPUB', 'CBZ', 'CBT', 'CBR', 'DJVU'}
            if ele.format.upper() in extensions_reader:
            email_text = _(u"%(book)s send to Kindle", book=link)
                             email_text, _(u'This e-mail has been sent via Calibre-Web.')))
    if config.config_unicode_filename:
        value = (unidecode.unidecode(value))
    value2 = None

            read_book = ub.ReadBook(user_id=current_user.id, book_id=book_id)
            read_book.read_status = ub.ReadBook.STATUS_FINISHED
            book = read_book
        except (OperationalError, InvalidRequestError) as ex:
            log.error(u"Read status could not set: {}".format(ex))
            return _("Read status could not set: {}".format(ex.orig))

                except (IOError, OSError) as ex:
                    log.error("Deleting book %s failed: %s", book.id, ex)
                    return False, _("Deleting book %(id)s failed: %(message)s", id=book.id, message=ex)
                    except (IOError, OSError) as ex:
                        log.error("Deleting authorpath for book %s failed: %s", book.id, ex)
                    + get_valid_filename(new_author.name, chars=42)
                                    os.path.normcase(os.path.join(all_new_path,
                                                                  all_new_name + '.' + file_format.format.lower())))
                        g_file = gd.getFileFromEbooksFolder(all_new_path,
                                                            file_format.name + '.' + file_format.format.lower())
                        if g_file:
                            gd.moveGdriveFileRemote(g_file, all_new_name + u'.' + file_format.format.lower())
                            gd.updateDatabaseOnEdit(g_file['id'], all_new_name + u'.' + file_format.format.lower())
                g_file = gd.getFileFromEbooksFolder(None, old_author_dir)
                if g_file:
                    gd.moveGdriveFolderRemote(g_file, new_author_rename_dir)
                    except OSError as ex:

        get_valid_filename(first_author, chars=42) + filename_ext
        g_file = gd.getFileFromEbooksFolder(os.path.dirname(book.path), titledir)
        if g_file:
            gd.moveGdriveFileRemote(g_file, new_titledir)
            gd.updateDatabaseOnEdit(g_file['id'], book.path)     # only child folder affected
        g_file = gd.getFileFromEbooksFolder(os.path.dirname(book.path), new_titledir)
        if g_file:
            gd.moveGdriveFolderRemote(g_file, new_authordir)
            gd.updateDatabaseOnEdit(g_file['id'], book.path)
            else:  # path is valid copy only files to new location (merge)
                                    os.path.normcase(os.path.join(new_path + dir_name[len(path):], file)))
        localbook.path = os.path.join(new_authordir, new_titledir).replace('\\', '/')
        g_file = gd.getFileFromEbooksFolder(book.path, name)
        g_file = gd.getFileFromEbooksFolder(os.path.dirname(book.path), book.path.split('/')[1])
    if g_file:
        gd.deleteDatabaseEntry(g_file['id'])
        g_file.Trash()
    inpt = [" ".join(inp.split()) for inp in inpt]

        raise Exception(_(u"This username is already taken"))
        if cli.allow_localhost:
            img = requests.get(url, timeout=(10, 200), allow_redirects=False)  # ToDo: Error Handling
        elif use_advocate:
            img = advocate.get(url, timeout=(10, 200), allow_redirects=False)      # ToDo: Error Handling
        else:
            log.error("python modul advocate is not installed but is needed")
            return False, _("Python modul 'advocate' is not installed but is needed for cover downloads")
    except UnacceptableAddressException:
        log.error("Localhost was accessed for cover upload")
        return False, _("You are not allowed to access localhost for cover uploads")
            gd.uploadFileToEbooksFolder(os.path.join(book_path, 'cover.jpg').replace("\\", "/"),
        # startTime = time.time()
        # log.debug('%s', time.time() - startTime)
def check_unrar(unrar_location):
    if not unrar_location:
    if not os.path.exists(unrar_location):
        unrar_location = [unrar_location]
        value = process_wait(unrar_location, pattern='UNRAR (.*) freeware')
    ret_val = ""
        ret_val = format_unit(runtime.days, 'duration-day', length="long", locale=get_locale()) + ', '
        ret_val += '{:d}:{:02d}:{:02d}s'.format(hours, minutes, seconds)
        ret_val += '{:2d}:{:02d}s'.format(minutes, seconds)
        ret_val += '{:2d}s'.format(seconds)
    return ret_val
    tmpcc = calibre_db.session.query(db.CustomColumns)\
        .filter(db.CustomColumns.datatype.notin_(db.cc_exceptions)).all()
    data1= ""
    return render_element_index(db.Books.sort, None, 'opds.feed_letter_books')
        download_book = calibre_db.get_book(book.Downloads.book_id)
        if download_book:
    num_books = entries.__len__()
                            config.config_books_per_page, num_books)
    return render_element_index(db.Authors.sort, db.books_authors_link, 'opds.feed_letter_author')
    return render_xml_dataset(db.Authors, book_id)
    return render_xml_dataset(db.Publishers, book_id)
    return render_element_index(db.Tags.name, db.books_tags_link, 'opds.feed_letter_category')
    return render_xml_dataset(db.Tags, book_id)
    return render_element_index(db.Series.sort, db.books_series_link, 'opds.feed_letter_series')

                                       (db.Ratings.rating / 2).label('name')) \
    return render_xml_dataset(db.Tags, book_id)
    # workaround, loading the user from the request and checking its download rights here
@opds.route("/opds/thumb_240_240/<book_id>")
@opds.route("/opds/cover_240_240/<book_id>")
@opds.route("/opds/cover_90_90/<book_id>")
@opds.route("/opds/cover/<book_id>")
@requires_basic_auth_if_no_ano
def feed_get_cover(book_id):
    return get_book_cover(book_id)


@opds.route("/opds/readbooks")
@requires_basic_auth_if_no_ano
def feed_read_books():
    off = request.args.get("offset") or 0
    result, pagination = render_read_books(int(off) / (int(config.config_books_per_page)) + 1, True, True)
    return render_xml_template('feed.xml', entries=result, pagination=pagination)


@opds.route("/opds/unreadbooks")
@requires_basic_auth_if_no_ano
def feed_unread_books():
    off = request.args.get("offset") or 0
    result, pagination = render_read_books(int(off) / (int(config.config_books_per_page)) + 1, False, True)
    return render_xml_template('feed.xml', entries=result, pagination=pagination)


        ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
        log.warning('OPDS Login failed for user "%s" IP-address: %s', username.decode('utf-8'), ip_address)
def render_xml_dataset(data_table, book_id):
    entries, __, pagination = calibre_db.fill_indexpage((int(off) / (int(config.config_books_per_page)) + 1), 0,
                                                        db.Books,
                                                        data_table.any(data_table.id == book_id),
                                                        [db.Books.timestamp.desc()])
    return render_xml_template('feed.xml', entries=entries, pagination=pagination)
def render_element_index(database_column, linked_table, folder):
    shift = 0
    off = int(request.args.get("offset") or 0)
    entries = calibre_db.session.query(func.upper(func.substr(database_column, 1, 1)).label('id'))
    if linked_table:
        entries = entries.join(linked_table).join(db.Books)
    entries = entries.filter(calibre_db.common_filters()).group_by(func.upper(func.substr(database_column, 1, 1))).all()
    elements = []
    if off == 0:
        elements.append({'id': "00", 'name': _("All")})
        shift = 1
    for entry in entries[
                 off + shift - 1:
                 int(off + int(config.config_books_per_page) - shift)]:
        elements.append({'id': entry.id, 'name': entry.id})
    pagination = Pagination((int(off) / (int(config.config_books_per_page)) + 1), config.config_books_per_page,
                            len(entries) + 1)
    return render_xml_template('feed.xml',
                               letterelements=elements,
                               folder=folder,
                               pagination=pagination)
    # right_edge: last right_edges count of all pages are shown as number, means, if 10 pages are paginated -> 9,10 shown
    # left_edge: first left_edges count of all pages are shown as number                                    -> 1,2 shown
    # left_current: left_current count below current page are shown as number, means if current page 5      -> 3,4 shown
    # left_current: right_current count above current page are shown as number, means if current page 5     -> 6,7 shown
from functools import wraps
      <form class="padded-bottom" action="{{ url_for('edit-book.convert_bookformat', book_id=book.id) }}" method="post" id="book_convert_frm">
<form role="form" action="{{ url_for('edit-book.edit_book', book_id=book.id) }}" method="post" enctype="multipart/form-data" id="book_edit_frm">
        data-editable-url="{{ url_for('edit-book.edit_list_book', param=parameter)}}"
            <th data-field="series_index" id="series_index" data-visible="{{visiblility.get('series_index')}}" data-edit-validate="{{ _('This Field is Required') }}" data-sortable="true" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.01" data-editable-min="0" data-editable-url="{{ url_for('edit-book.edit_list_book', param='series_index')}}" data-edit="true" data-editable-title="{{_('Enter Title')}}"{% endif %}>{{_('Series Index')}}</th>
            <th data-field="comments" id="comments" data-escape="true" data-editable-mode="popup"  data-visible="{{visiblility.get('comments')}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="wysihtml5" data-editable-url="{{ url_for('edit-book.edit_list_book', param='comments')}}" data-edit="true" data-editable-title="{{_('Enter comments')}}"{% endif %}>{{_('Comments')}}</th>
                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="1" data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-formatter="ratingFormatter" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.5" data-editable-step="1" data-editable-min="1" data-editable-max="5" data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="number" data-editable-placeholder="1" data-editable-step="0.01" data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
                <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="select" data-editable-source={{ url_for('edit-book.table_get_custom_enum', c_id=c.id)  }} data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
                  <th data-field="custom_column_{{ c.id|string }}" id="custom_column_{{ c.id|string }}" data-escape="true" data-editable-mode="popup"  data-visible="{{visiblility.get('custom_column_'+ c.id|string)}}" data-sortable="false" {% if g.user.role_edit() %} data-editable-type="wysihtml5" data-editable-url="{{ url_for('edit-book.edit_list_book', param='custom_column_'+ c.id|string)}}" data-edit="true" data-editable-title="{{_('Enter ') + c.name}}"{% endif %}>{{c.name}}</th>
          <a href="{{identifier}}" target="_blank" class="btn btn-xs btn-success" role="button">{{identifier.format_type()}}</a>
          <a href="{{ url_for('edit-book.edit_book', book_id=entry.id) }}" class="btn btn-sm btn-primary" id="edit_book" role="button"><span class="glyphicon glyphicon-edit"></span> {{_('Edit Metadata')}}</a>
                    <form id="form-upload" class="navbar-form" action="{{ url_for('edit-book.upload') }}" data-title="{{_('Uploading...')}}" data-footer="{{_('Close')}}" data-failed="{{_('Error')}}" data-message="{{_('Upload done, processing, please wait...')}}" method="post" enctype="multipart/form-data">
from functools import wraps
from babel import Locale

    register_user_with_oauth = logout_oauth_user = get_oauth_status = None
    csp = "default-src 'self'"
    csp += ''.join([' ' + host for host in config.config_trustedhosts.strip().split(',')])
    csp += " 'unsafe-inline' 'unsafe-eval'; font-src 'self' data:; img-src 'self' data:"
    resp.headers['Content-Security-Policy'] = csp
    if request.endpoint == "edit-book.edit_book" or config.config_use_google_drive:


    l_bookmark = ub.Bookmark(user_id=current_user.id,
                             book_id=book_id,
                             format=book_format,
                             bookmark_key=bookmark_key)
    ub.session.merge(l_bookmark)
    is_archived = change_archived_books(book_id, message="Book {} archive bit toggled".format(book_id))

def generate_char_list(data_colum, db_link):
    return (calibre_db.session.query(func.upper(func.substr(data_colum, 1, 1)).label('char'))
            .join(db_link).join(db.Books).filter(calibre_db.common_filters())
            .group_by(func.upper(func.substr(data_colum, 1, 1))).all())


                                                                False, 0,

            # Unary expression comparsion only working (for this expression) in sqlalchemy 1.4+
            # if not (order[0][0].compare(func.count(ub.Downloads.book_id).desc()) or
            #        order[0][0].compare(func.count(ub.Downloads.book_id).asc())):
        all_books = ub.session.query(ub.Downloads, func.count(ub.Downloads.book_id)) \
            download_book = calibre_db.session.query(db.Books).filter(calibre_db.common_filters()).filter(
            if download_book:
                entries.append(download_book)
        num_books = entries.__len__()
        pagination = Pagination(page, config.config_books_per_page, num_books)
            if not calibre_db.session.query(db.Books).\
                                            filter(calibre_db.common_filters()).filter(db.Books.id == book.id).first():
                                     title=_(u"Downloaded books by %(user)s", user=user.name),
            return []  # ToDo: Handle error Case for opds
            page_name = "read"
            page_name = "unread"
                                     title=name, page=page_name, order=order[1])
    archived_books = (ub.session.query(ub.ArchivedBook)
                      .filter(ub.ArchivedBook.user_id == int(current_user.id))
                      .filter(ub.ArchivedBook.is_archived == True)
                      .all())
    page_name = "archived"
                                 title=name, page=page_name, order=sort_param[1])
    tags = calibre_db.session.query(db.Tags) \
        .join(db.books_tags_link) \
        .join(db.Books) \
        .group_by(text('books_tags_link.tag')) \
    series = calibre_db.session.query(db.Series) \
        .join(db.books_series_link) \
        .join(db.Books) \
        .group_by(text('books_series_link.series')) \
        .order_by(db.Series.name) \
    shelves = ub.session.query(ub.Shelf) \
        .filter(or_(ub.Shelf.is_public == 1, ub.Shelf.user_id == int(current_user.id))) \
    extensions = calibre_db.session.query(db.Data) \
        .join(db.Books) \
        .group_by(db.Data.format) \
                                 series=series, shelves=shelves, title=_(u"Advanced Search"), cc=cc, page="advsearch")

    search_param = request.args.get("search")
        join = db.books_authors_link, db.Books.id == db.books_authors_link.c.book, db.Authors, db.books_series_link, \
            db.Books.id == db.books_series_link.c.book, db.Series
    total_count = filtered_count = calibre_db.session.query(db.Books).filter(
        calibre_db.common_filters(allow_show_archived=True)).count()
        if search_param:
            books = calibre_db.search_query(search_param, config.config_read_column).all()
                                                           int(current_user.id) == ub.ArchivedBook.user_id))
    elif search_param:
        entries, filtered_count, __ = calibre_db.get_search_results(search_param,
                                                                    [order, ''],
        for lang_index in range(0, len(val.languages)):
            val.languages[lang_index].language_name = isoLanguages.get_language_name(get_locale(), val.languages[
                lang_index].lang_code)

        char_list = generate_char_list(db.Authors.sort, db.books_authors_link)
        author_copy = copy.deepcopy(entries)
        for entry in author_copy:
        return render_title_template('list.html', entries=author_copy, folder='web.books_list', charlist=char_list,

        entries = ub.session.query(ub.User, func.count(ub.Downloads.book_id).label('count')) \
        char_list = ub.session.query(func.upper(func.substr(ub.User.name, 1, 1)).label('char')) \
        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=char_list,
        char_list = generate_char_list(db.Publishers.name, db.books_publishers_link)
        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=char_list,
        char_list = generate_char_list(db.Series.sort, db.books_series_link)
            return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=char_list,
                .join(db.books_series_link).join(db.Series).filter(calibre_db.common_filters()) \
            return render_title_template('grid.html', entries=entries, folder='web.books_list', charlist=char_list,
                                           (db.Ratings.rating / 2).label('name')) \
        char_list = list()
            if upper_lang not in char_list:
                char_list.append(upper_lang)
                                     charlist=char_list, title=_(u"Languages"), page="langlist",
        char_list = generate_char_list(db.Tags.name, db.books_tags_link)
        return render_title_template('list.html', entries=entries, folder='web.books_list', charlist=char_list,
def adv_search_text(q, include_inputs, exclude_inputs, data_value):
    for inp in include_inputs:
        q = q.filter(db.Books.data.any(data_value == inp))
    for excl in exclude_inputs:
        q = q.filter(not_(db.Books.data.any(data_value == excl)))
    return q


'''def adv_search_extension(q, include_extension_inputs, exclude_extension_inputs):
    return q'''

    q = q.outerjoin(ub.BookShelf, db.Books.id == ub.BookShelf.book_id) \

    elements = {'tag': db.Tags, 'serie': db.Series, 'shelf': ub.Shelf}
    q = query.outerjoin(db.books_series_link, db.Books.id == db.books_series_link.c.book) \
        .outerjoin(db.Series) \
    search_term = []
                search_term.extend([u"{} >= {}".format(c.name,
                                                       format_date(datetime.strptime(column_start, "%Y-%m-%d").date(),
                                                                   format='medium',
                                                                   locale=get_locale())
                                                       )])
                search_term.extend([u"{} <= {}".format(c.name,
                                                       format_date(datetime.strptime(column_end, "%Y-%m-%d").date(),
                                                                   format='medium',
                                                                   locale=get_locale())
                                                       )])
            search_term.extend([(u"{}: {}".format(c.name, term.get('custom_column_' + str(c.id))))])
    if any(tags.values()) or author_name or book_title or \
        publisher or pub_start or pub_end or rating_low or rating_high \
            or description or cc_present or read_status:
        search_term, pub_start, pub_end = extend_search_term(search_term,
                                                             author_name,
                                                             book_title,
                                                             publisher,
                                                             pub_start,
                                                             pub_end,
                                                             tags,
                                                             rating_high,
                                                             rating_low,
                                                             read_status)
        q = adv_search_text(q, tags['include_tag'], tags['exclude_tag'], db.Tags.id)
        q = adv_search_text(q, tags['include_serie'], tags['exclude_serie'], db.Series.id)
        q = adv_search_text(q, tags['include_extension'], tags['exclude_extension'], db.Data.format)
        q = adv_search_language(q, tags['include_language'], tags['exclude_language'], )
        q = adv_search_ratings(q, rating_high, rating_low, )
                                 adv_searchterm=search_term,


                    and user.name != "Guest":
                ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
                log.warning('LDAP Login failed for user "%s" IP-address: %s', form['username'], ip_address)
            ip_address = request.headers.get('X-Forwarded-For', request.remote_addr)
                        log.info('Password reset for user "%s" IP-address: %s', form['username'], ip_address)
                    log.warning('Username missing for password reset IP-address: %s', ip_address)
                    log.warning('Login failed for user "%s" IP-address: %s', form['username'], ip_address)
        ub.delete_user_session(current_user.id, flask_session.get('_id', ""))
                # Query username, if not existing, change
    translations = babel.list_translations() + [Locale('en')]
        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"),
              category="error")
        flash(_(u"Oops! Selected book title is unavailable. File does not exist or is not accessible"),
              category="error")
        for lang_index in range(0, len(entry.languages)):
            entry.languages[lang_index].language_name = isoLanguages.get_language_name(get_locale(), entry.languages[
                lang_index].lang_code)
        book_in_shelves = []
            book_in_shelves.append(sh.shelf)
                                     is_xhr=request.headers.get('X-Requested-With') == 'XMLHttpRequest',
                                     books_shelfs=book_in_shelves,
google-api-python-client>=1.7.11,<2.41.0
google-auth-oauthlib>=0.4.3,<0.6.0
google-api-python-client>=1.7.11,<2.41.0
scholarly>=1.2.0,<1.7
lxml>=3.8.0,<4.9.0
advocate>=1.0.0,<1.1.0