summaryrefslogtreecommitdiff
path: root/db/db_manager.py
diff options
context:
space:
mode:
Diffstat (limited to 'db/db_manager.py')
-rw-r--r--db/db_manager.py275
1 files changed, 138 insertions, 137 deletions
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)
generated by cgit v1.2.3 (git 2.39.1) at 2024-11-10 04:24:37 +0000