summaryrefslogtreecommitdiff
path: root/db/db_manager.py
blob: c55ac39a70e78fa623d9c4d1e1d60c3c992de4bc (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
import configparser
import os
import sqlite3
import tempfile


class DbManager:
    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(
                            """'||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):
            new_db = False
            cache_size_kb = 9766

            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 __del__(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 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):
            cursor = self.conn.cursor()
            cursor.execute("""CREATE TABLE IF NOT EXISTS album (id
                           INTEGER PRIMARY KEY, name TEXT, date TEXT,
                           musicbrainz_albumid TEXT)""")
            cursor.execute("""CREATE TABLE IF NOT EXISTS album_artist (
                    album_id INTEGER,
                    artist_id INTEGER,
                    CONSTRAINT ALBUM_ARTIST_PK PRIMARY KEY (album_id,
                    artist_id),
                    CONSTRAINT ALBUM_ARTIST_FK_ALBUM FOREIGN KEY (album_id)
                        REFERENCES album(id),
                    CONSTRAINT ALBUM_ARTIST_FK_ARTIST FOREIGN KEY
                        (artist_id) REFERENCES artist(id)
                )""")
            cursor.execute("""CREATE TABLE IF NOT EXISTS album_track (
                    album_id INTEGER,
                    track_id INTEGER,
                    CONSTRAINT ALBUM_TRACK_PK PRIMARY KEY (album_id,
                        track_id),
                    FOREIGN KEY(album_id) REFERENCES album(id),
                    FOREIGN KEY(track_id) REFERENCES track(id)
                )""")
            cursor.execute("""CREATE TABLE IF NOT EXISTS artist (id
                    INTEGER PRIMARY KEY, name
                    TEXT(2000000000), sortname TEXT(2000000000),
                    musicbrainz_artistid TEXT(2000000000))""")
            cursor.execute("""CREATE TABLE IF NOT EXISTS artist_track (
                    artist_id INTEGER,
                    track_id INTEGER,
                    CONSTRAINT ARTIST_TRACK_PK PRIMARY KEY (artist_id,
                        track_id),
                    FOREIGN KEY(artist_id) REFERENCES artist(id),
                    FOREIGN KEY(track_id) REFERENCES track(id)
                )""")
            cursor.execute("""CREATE TABLE IF NOT EXISTS track (
                    id INTEGER,
                    tracknumber INTEGER,
                    name TEXT(2000000000),
                    grouping TEXT(2000000000),
                    filename TEXT(2000000000),
                    CONSTRAINT TRACK_PK PRIMARY KEY (id)
                )""")
            cursor.execute("""CREATE UNIQUE INDEX IF NOT EXISTS
                           artist_musicbrainz_artistid ON
                           artist(musicbrainz_artistid ASC)""")
            cursor.execute("""CREATE INDEX IF NOT EXISTS track_filename_IDX
                           ON track(filename)""")
            cursor.execute("""CREATE INDEX IF NOT EXISTS track_grouping_IDX
                           ON track(grouping)""")
            cursor.execute("""CREATE INDEX IF NOT EXISTS track_name_IDX ON
                           track(name)""")
            cursor.execute("""CREATE INDEX IF NOT EXISTS
                           track_tracknumber_IDX ON track(tracknumber)""")
            cursor.close()

    instance = None

    def __new__(self):
        if not DbManager.instance:
            DbManager.instance = DbManager.__DbManager()

        return DbManager.instance