From d06f96388d754ed41876f7fccb63f84241d44963 Mon Sep 17 00:00:00 2001 From: Michaël Ball Date: Sun, 26 Mar 2017 10:19:59 +0100 Subject: Works on python 2/pypy --- db/db_manager.py | 275 ++++++++++++++++++++++++++++--------------------------- 1 file changed, 138 insertions(+), 137 deletions(-) (limited to 'db/db_manager.py') diff --git a/db/db_manager.py b/db/db_manager.py index ad2fd14..9c2dd53 100644 --- a/db/db_manager.py +++ b/db/db_manager.py @@ -1,9 +1,18 @@ -import configparser +""" +db_manager exposes a DbManager class to make interacting with sqlite +databases easier. +""" +import logging import os import sqlite3 +import six -class DbManager: + +_LOGGER = logging.getLogger(__name__) + +class DbManager(object): + """DBManager makes interacting with sqlite databases easier.""" create_album_table = "CREATE TABLE IF NOT EXISTS album (id INTEGER "\ "PRIMARY KEY, name TEXT, date TEXT, musicbrainz_albumid TEXT)" @@ -38,141 +47,133 @@ class DbManager: create_track_number_index = "CREATE INDEX IF NOT EXISTS "\ "track_tracknumber_IDX ON track(tracknumber)" - class __DbManager: - config = configparser.ConfigParser() - config.read("mach2.ini") - - def iterdump(connection): - cu = connection.cursor() - yield("BEGIN TRANSACTION;") - - q = """ - SELECT "name", "type", "sql" - FROM "sqlite_master" - WHERE "sql" NOT NULL AND - "type" == 'table' - ORDER BY "name"; - """ - schema_res = cu.execute(q).fetchall() - for table_name, type, sql in schema_res: - if table_name == "sqlite_sequence": - yield("DELETE FROM \"sqlite_sequence\";") - elif table_name == "sqlite_stat1": - yield("ANALYZE \"sqlite_master\";") - elif table_name.startswith("sqlite_"): - continue - else: - yield("{0};".format(sql)) - - table_name_ident = table_name.replace("\"", "\"\"") - res = cu.execute("PRAGMA table_info(\"{0}\")".format( - table_name_ident)) - column_names = [ - str(table_info[1]) for table_info in res.fetchall()] - q = """ - SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}"; - """.format(table_name_ident, ",".join( + @staticmethod + def iterdump(connection): + """Iterates through the database, creating commands to dump all the + tables line by line.""" + cursor = connection.cursor() + + query = """ + SELECT "name", "type", "sql" + FROM "sqlite_master" + WHERE "sql" NOT NULL AND + "type" == 'table' + ORDER BY "name"; + """ + schema_res = cursor.execute(query).fetchall() + for table_name, dummy, sql in schema_res: + if table_name == "sqlite_sequence": + yield "DELETE FROM \"sqlite_sequence\";" + elif table_name == "sqlite_stat1": + yield "ANALYZE \"sqlite_master\";" + elif table_name.startswith("sqlite_"): + continue + else: + yield six.u("{0};").format(sql) + + table_name_ident = table_name.replace("\"", "\"\"") + res = cursor.execute("PRAGMA table_info(\"{0}\")".format( + table_name_ident)) + column_names = [ + str(table_info[1]) for table_info in res.fetchall()] + query = """ + SELECT 'INSERT INTO "{0}" VALUES({1})' FROM "{0}"; + """.format(table_name_ident, ",".join( """'||quote("{0}")||'""".format( col.replace( "\"", "\"\"")) for col in column_names)) - query_res = cu.execute(q) - for row in query_res: - yield("{0};".format(row[0])) - - q = """ - SELECT "name", "type", "sql" - FROM "sqlite_master" - WHERE "sql" NOT NULL AND - "type" IN ('index', 'trigger', 'view') - """ - schema_res = cu.execute(q) - for name, type, sql in schema_res.fetchall(): - yield("{0};".format(sql)) - - yield("COMMIT;") - - def __init__(self, db=None): - new_db = False - cache_size_kb = 9766 - - if db: - self.conn = sqlite3.connect(db) - - else: - if not os.path.isfile(self.config["DEFAULT"]["database"]): - new_db = True - - if new_db: - self.conn = sqlite3.connect(":memory:") - self.create_tables() - else: - self.conn = sqlite3.connect( - self.config["DEFAULT"]["database"]) - library_info = os.stat(self.config["DEFAULT"]["database"]) - cache_size_kb = round((library_info.st_size * 1.2) / 1024) - - cursor = self.conn.cursor() - # Setting pragma with ? placeholder produces an error - cursor.execute("pragma cache_size=-%s" % cache_size_kb) - cursor.close() - - self.conn.row_factory = sqlite3.Row - - def export(self): - if not os.path.isfile(self.config["DEFAULT"]["database"]): - script = "" - - for line in DbManager.__DbManager.iterdump(self.conn): - script = "\n".join((script, line)) - - tempconn = sqlite3.connect( - self.config["DEFAULT"]["database"]) - tempcur = tempconn.cursor() - - tempcur.executescript(script) - tempcur.close() - - def __str__(self): - return repr(self) - - def execute(self, script, parameters=None): - if parameters: - return self.conn.execute(script, parameters) - - return self.conn.execute(script) - - def commit(self): - return self.conn.commit() - - def cursor(self): - return self.conn.cursor() - - def close(self): - return self.conn.close() - - def interrupt(self): - return self.conn.interrupt() - - def create_tables(self): - with self.conn: - self.conn.execute(DbManager.create_album_table) - self.conn.execute(DbManager.create_album_artist_table) - self.conn.execute(DbManager.create_album_track_table) - self.conn.execute(DbManager.create_artist_table) - self.conn.execute(DbManager.create_artist_track_table) - self.conn.execute(DbManager.create_track_table) - self.conn.execute(DbManager.create_musicbrainz_artist_index) - self.conn.execute(DbManager.create_track_filename_index) - self.conn.execute(DbManager.create_track_grouping_index) - self.conn.execute(DbManager.create_track_name_index) - self.conn.execute(DbManager.create_track_number_index) - - instance = None - - def __new__(self, db=None): - if db: - return DbManager.__DbManager(db) - elif not DbManager.instance: - DbManager.instance = DbManager.__DbManager() - - return DbManager.instance + query_res = cursor.execute(query) + for row in query_res: + yield six.u("{0};").format(row[0]) + + query = """ + SELECT "name", "type", "sql" + FROM "sqlite_master" + WHERE "sql" NOT NULL AND + "type" IN ('index', 'trigger', 'view') + """ + schema_res = cursor.execute(query) + for dummy, dummy2, sql in schema_res.fetchall(): + yield six.u("{0};").format(sql) + + def __init__(self, db_file): + new_db = False + cache_size_kb = 9766 + self.db_file = db_file + + if not os.path.isfile(self.db_file): + new_db = True + + if new_db: + self.conn = sqlite3.connect(":memory:") + self.create_tables() + else: + self.conn = sqlite3.connect(self.db_file) + library_info = os.stat(self.db_file) + cache_size_kb = round((library_info.st_size * 1.2) / 1024) + + cursor = self.conn.cursor() + # Setting pragma with ? placeholder produces an error + cursor.execute("pragma cache_size=-%s" % cache_size_kb) + cursor.close() + + self.conn.row_factory = sqlite3.Row + + def export(self): + """Export the database.""" + if not os.path.isfile(self.db_file): + tempconn = sqlite3.connect(self.db_file) + tempcur = tempconn.cursor() + tempcur.execute("PRAGMA journal_mode=WAL;") + tempcur.close() + + try: + with tempconn: + for line in DbManager.iterdump(self.conn): + tempconn.execute(line) + + except sqlite3.Error as exc: + _LOGGER.error(exc) + + tempconn.close() + + def __str__(self): + return repr(self) + + def execute(self, script, parameters=None): + """Execute an sql statement""" + if parameters: + return self.conn.execute(script, parameters) + + return self.conn.execute(script) + + def commit(self): + """Commit the current transaction""" + return self.conn.commit() + + def cursor(self): + """Create a cursor""" + return self.conn.cursor() + + def close(self): + """Close the connection""" + return self.conn.close() + + def interrupt(self): + """Interrupt the connection""" + return self.conn.interrupt() + + def create_tables(self): + """Create the database tables""" + with self.conn: + self.conn.execute(DbManager.create_album_table) + self.conn.execute(DbManager.create_album_artist_table) + self.conn.execute(DbManager.create_album_track_table) + self.conn.execute(DbManager.create_artist_table) + self.conn.execute(DbManager.create_artist_track_table) + self.conn.execute(DbManager.create_track_table) + self.conn.execute(DbManager.create_musicbrainz_artist_index) + self.conn.execute(DbManager.create_track_filename_index) + self.conn.execute(DbManager.create_track_grouping_index) + self.conn.execute(DbManager.create_track_name_index) + self.conn.execute(DbManager.create_track_number_index) -- cgit v1.2.3