Source code for openlp.core.lib.db

# -*- coding: utf-8 -*-
# vim: autoindent shiftwidth=4 expandtab textwidth=120 tabstop=4 softtabstop=4

###############################################################################
# OpenLP - Open Source Lyrics Projection                                      #
# --------------------------------------------------------------------------- #
# Copyright (c) 2008-2017 OpenLP Developers                                   #
# --------------------------------------------------------------------------- #
# This program is free software; you can redistribute it and/or modify it     #
# under the terms of the GNU General Public License as published by the Free  #
# Software Foundation; version 2 of the License.                              #
#                                                                             #
# This program is distributed in the hope that it will be useful, but WITHOUT #
# ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or       #
# FITNESS FOR A PARTICULAR PURPOSE. See the 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, write to the Free Software Foundation, Inc., 59  #
# Temple Place, Suite 330, Boston, MA 02111-1307 USA                          #
###############################################################################

"""
The :mod:`db` module provides the core database functionality for OpenLP
"""
import logging
import os
from urllib.parse import quote_plus as urlquote

from sqlalchemy import Table, MetaData, Column, types, create_engine
from sqlalchemy.exc import SQLAlchemyError, InvalidRequestError, DBAPIError, OperationalError
from sqlalchemy.orm import scoped_session, sessionmaker, mapper
from sqlalchemy.pool import NullPool
from alembic.migration import MigrationContext
from alembic.operations import Operations

from openlp.core.common import AppLocation, Settings, translate, delete_file
from openlp.core.lib.ui import critical_error_message_box

log = logging.getLogger(__name__)


[docs]def init_db(url, auto_flush=True, auto_commit=False, base=None): """ Initialise and return the session and metadata for a database :param url: The database to initialise connection with :param auto_flush: Sets the flushing behaviour of the session :param auto_commit: Sets the commit behaviour of the session :param base: If using declarative, the base class to bind with """ engine = create_engine(url, poolclass=NullPool) if base is None: metadata = MetaData(bind=engine) else: base.metadata.bind = engine metadata = base.metadata session = scoped_session(sessionmaker(autoflush=auto_flush, autocommit=auto_commit, bind=engine)) return session, metadata
[docs]def get_db_path(plugin_name, db_file_name=None): """ Create a path to a database from the plugin name and database name :param plugin_name: Name of plugin :param db_file_name: File name of database :return: The path to the database as type str """ if db_file_name is None: return 'sqlite:///{path}/{plugin}.sqlite'.format(path=AppLocation.get_section_data_path(plugin_name), plugin=plugin_name) else: return 'sqlite:///{path}/{name}'.format(path=AppLocation.get_section_data_path(plugin_name), name=db_file_name)
[docs]def handle_db_error(plugin_name, db_file_name): """ Log and report to the user that a database cannot be loaded :param plugin_name: Name of plugin :param db_file_name: File name of database :return: None """ db_path = get_db_path(plugin_name, db_file_name) log.exception('Error loading database: {db}'.format(db=db_path)) critical_error_message_box(translate('OpenLP.Manager', 'Database Error'), translate('OpenLP.Manager', 'OpenLP cannot load your database.\n\nDatabase: {db}').format(db=db_path))
[docs]def init_url(plugin_name, db_file_name=None): """ Return the database URL. :param plugin_name: The name of the plugin for the database creation. :param db_file_name: The database file name. Defaults to None resulting in the plugin_name being used. """ settings = Settings() settings.beginGroup(plugin_name) db_type = settings.value('db type') if db_type == 'sqlite': db_url = get_db_path(plugin_name, db_file_name) else: db_url = '{type}://{user}:{password}@{host}/{db}'.format(type=db_type, user=urlquote(settings.value('db username')), password=urlquote(settings.value('db password')), host=urlquote(settings.value('db hostname')), db=urlquote(settings.value('db database'))) settings.endGroup() return db_url
[docs]def get_upgrade_op(session): """ Create a migration context and an operations object for performing upgrades. :param session: The SQLAlchemy session object. """ context = MigrationContext.configure(session.bind.connect()) return Operations(context)
[docs]class BaseModel(object): """ BaseModel provides a base object with a set of generic functions """
[docs] @classmethod def populate(cls, **kwargs): """ Creates an instance of a class and populates it, returning the instance """ instance = cls() for key, value in kwargs.items(): instance.__setattr__(key, value) return instance
[docs]def upgrade_db(url, upgrade): """ Upgrade a database. :param url: The url of the database to upgrade. :param upgrade: The python module that contains the upgrade instructions. """ session, metadata = init_db(url) class Metadata(BaseModel): """ Provides a class for the metadata table. """ pass metadata_table = Table( 'metadata', metadata, Column('key', types.Unicode(64), primary_key=True), Column('value', types.UnicodeText(), default=None) ) metadata_table.create(checkfirst=True) mapper(Metadata, metadata_table) version_meta = session.query(Metadata).get('version') if version_meta is None: # Tables have just been created - fill the version field with the most recent version if session.query(Metadata).get('dbversion'): version = 0 else: version = upgrade.__version__ version_meta = Metadata.populate(key='version', value=version) session.add(version_meta) session.commit() else: version = int(version_meta.value) if version > upgrade.__version__: session.remove() return version, upgrade.__version__ version += 1 try: while hasattr(upgrade, 'upgrade_{version:d}'.format(version=version)): log.debug('Running upgrade_{version:d}'.format(version=version)) try: upgrade_func = getattr(upgrade, 'upgrade_{version:d}'.format(version=version)) upgrade_func(session, metadata) session.commit() # Update the version number AFTER a commit so that we are sure the previous transaction happened version_meta.value = str(version) session.commit() version += 1 except (SQLAlchemyError, DBAPIError): log.exception('Could not run database upgrade script ' '"upgrade_{version:d}", upgrade process has been halted.'.format(version=version)) break except (SQLAlchemyError, DBAPIError): version_meta = Metadata.populate(key='version', value=int(upgrade.__version__)) session.commit() upgrade_version = upgrade.__version__ version = int(version_meta.value) session.remove() return version, upgrade_version
[docs]def delete_database(plugin_name, db_file_name=None): """ Remove a database file from the system. :param plugin_name: The name of the plugin to remove the database for :param db_file_name: The database file name. Defaults to None resulting in the plugin_name being used. """ if db_file_name: db_file_path = os.path.join(AppLocation.get_section_data_path(plugin_name), db_file_name) else: db_file_path = os.path.join(AppLocation.get_section_data_path(plugin_name), plugin_name) return delete_file(db_file_path)
[docs]class Manager(object): """ Provide generic object persistence management """ def __init__(self, plugin_name, init_schema, db_file_name=None, upgrade_mod=None, session=None): """ Runs the initialisation process that includes creating the connection to the database and the tables if they do not exist. :param plugin_name: The name to setup paths and settings section names :param init_schema: The init_schema function for this database :param db_file_name: The upgrade_schema function for this database :param upgrade_mod: The file name to use for this database. Defaults to None resulting in the plugin_name being used. """ self.is_dirty = False self.session = None self.db_url = None if db_file_name: log.debug('Manager: Creating new DB url') self.db_url = init_url(plugin_name, db_file_name) else: self.db_url = init_url(plugin_name) if upgrade_mod: try: db_ver, up_ver = upgrade_db(self.db_url, upgrade_mod) except (SQLAlchemyError, DBAPIError): handle_db_error(plugin_name, db_file_name) return if db_ver > up_ver: critical_error_message_box( translate('OpenLP.Manager', 'Database Error'), translate('OpenLP.Manager', 'The database being loaded was created in a more recent version of ' 'OpenLP. The database is version {db_ver}, while OpenLP expects version {db_up}. ' 'The database will not be loaded.\n\nDatabase: {db_name}').format(db_ver=db_ver, db_up=up_ver, db_name=self.db_url)) return if not session: try: self.session = init_schema(self.db_url) except (SQLAlchemyError, DBAPIError): handle_db_error(plugin_name, db_file_name) else: self.session = session
[docs] def save_object(self, object_instance, commit=True): """ Save an object to the database :param object_instance: The object to save :param commit: Commit the session with this object """ for try_count in range(3): try: self.session.add(object_instance) if commit: self.session.commit() self.is_dirty = True return True except OperationalError: # This exception clause is for users running MySQL which likes to terminate connections on its own # without telling anyone. See bug #927473. However, other dbms can raise it, usually in a # non-recoverable way. So we only retry 3 times. log.exception('Probably a MySQL issue - "MySQL has gone away"') self.session.rollback() if try_count >= 2: raise except InvalidRequestError: self.session.rollback() log.exception('Object list save failed') return False except: self.session.rollback() raise
[docs] def save_objects(self, object_list, commit=True): """ Save a list of objects to the database :param object_list: The list of objects to save :param commit: Commit the session with this object """ for try_count in range(3): try: self.session.add_all(object_list) if commit: self.session.commit() self.is_dirty = True return True except OperationalError: # This exception clause is for users running MySQL which likes to terminate connections on its own # without telling anyone. See bug #927473. However, other dbms can raise it, usually in a # non-recoverable way. So we only retry 3 times. log.exception('Probably a MySQL issue, "MySQL has gone away"') self.session.rollback() if try_count >= 2: raise except InvalidRequestError: self.session.rollback() log.exception('Object list save failed') return False except: self.session.rollback() raise
[docs] def get_object(self, object_class, key=None): """ Return the details of an object :param object_class: The type of object to return :param key: The unique reference or primary key for the instance to return """ if not key: return object_class() else: for try_count in range(3): try: return self.session.query(object_class).get(key) except OperationalError: # This exception clause is for users running MySQL which likes to terminate connections on its own # without telling anyone. See bug #927473. However, other dbms can raise it, usually in a # non-recoverable way. So we only retry 3 times. log.exception('Probably a MySQL issue, "MySQL has gone away"') if try_count >= 2: raise
[docs] def get_object_filtered(self, object_class, filter_clause): """ Returns an object matching specified criteria :param object_class: The type of object to return :param filter_clause: The criteria to select the object by """ for try_count in range(3): try: return self.session.query(object_class).filter(filter_clause).first() except OperationalError as oe: # This exception clause is for users running MySQL which likes to terminate connections on its own # without telling anyone. See bug #927473. However, other dbms can raise it, usually in a # non-recoverable way. So we only retry 3 times. if try_count >= 2 or 'MySQL has gone away' in str(oe): raise log.exception('Probably a MySQL issue, "MySQL has gone away"')
[docs] def get_all_objects(self, object_class, filter_clause=None, order_by_ref=None): """ Returns all the objects from the database :param object_class: The type of objects to return :param filter_clause: The filter governing selection of objects to return. Defaults to None. :param order_by_ref: Any parameters to order the returned objects by. Defaults to None. """ query = self.session.query(object_class) if filter_clause is not None: query = query.filter(filter_clause) if isinstance(order_by_ref, list): query = query.order_by(*order_by_ref) elif order_by_ref is not None: query = query.order_by(order_by_ref) for try_count in range(3): try: return query.all() except OperationalError: # This exception clause is for users running MySQL which likes to terminate connections on its own # without telling anyone. See bug #927473. However, other dbms can raise it, usually in a # non-recoverable way. So we only retry 3 times. log.exception('Probably a MySQL issue, "MySQL has gone away"') if try_count >= 2: raise
[docs] def get_object_count(self, object_class, filter_clause=None): """ Returns a count of the number of objects in the database. :param object_class: The type of objects to return. :param filter_clause: The filter governing selection of objects to return. Defaults to None. """ query = self.session.query(object_class) if filter_clause is not None: query = query.filter(filter_clause) for try_count in range(3): try: return query.count() except OperationalError: # This exception clause is for users running MySQL which likes to terminate connections on its own # without telling anyone. See bug #927473. However, other dbms can raise it, usually in a # non-recoverable way. So we only retry 3 times. log.exception('Probably a MySQL issue, "MySQL has gone away"') if try_count >= 2: raise
[docs] def delete_object(self, object_class, key): """ Delete an object from the database :param object_class: The type of object to delete :param key: The unique reference or primary key for the instance to be deleted """ if key != 0: object_instance = self.get_object(object_class, key) for try_count in range(3): try: self.session.delete(object_instance) self.session.commit() self.is_dirty = True return True except OperationalError: # This exception clause is for users running MySQL which likes to terminate connections on its own # without telling anyone. See bug #927473. However, other dbms can raise it, usually in a # non-recoverable way. So we only retry 3 times. log.exception('Probably a MySQL issue, "MySQL has gone away"') self.session.rollback() if try_count >= 2: raise except InvalidRequestError: self.session.rollback() log.exception('Failed to delete object') return False except: self.session.rollback() raise else: return True
[docs] def delete_all_objects(self, object_class, filter_clause=None): """ Delete all object records. This method should only be used for simple tables and **not** ones with relationships. The relationships are not deleted from the database and this will lead to database corruptions. :param object_class: The type of object to delete :param filter_clause: The filter governing selection of objects to return. Defaults to None. """ for try_count in range(3): try: query = self.session.query(object_class) if filter_clause is not None: query = query.filter(filter_clause) query.delete(synchronize_session=False) self.session.commit() self.is_dirty = True return True except OperationalError: # This exception clause is for users running MySQL which likes to terminate connections on its own # without telling anyone. See bug #927473. However, other dbms can raise it, usually in a # non-recoverable way. So we only retry 3 times. log.exception('Probably a MySQL issue, "MySQL has gone away"') self.session.rollback() if try_count >= 2: raise except InvalidRequestError: self.session.rollback() log.exception('Failed to delete {name} records'.format(name=object_class.__name__)) return False except: self.session.rollback() raise
[docs] def finalise(self): """ VACUUM the database on exit. """ if self.is_dirty: engine = create_engine(self.db_url) if self.db_url.startswith('sqlite'): engine.execute("vacuum")