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
|
import configparser
import os
import apsw
class DbManager:
class __DbManager:
config = configparser.ConfigParser()
config.read("mach2.ini")
def __init__(self):
new_db = False
if not os.path.isfile(self.config["DEFAULT"]["database"]):
new_db = True
self.conn = apsw.Connection(self.config["DEFAULT"]["database"],
apsw.SQLITE_OPEN_READWRITE |
apsw.SQLITE_OPEN_CREATE)
if new_db:
self.create_tables()
else:
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 errors out
cursor.execute("pragma cache_size=%s" % cache_size_kb)
cursor.close()
def __str__(self):
return repr(self)
def cursor(self):
return self.conn.cursor()
def close(self):
return self.conn.close()
def interrupt(self):
return self.conn.interrupt()
def last_insert_rowid(self):
return self.conn.last_insert_rowid()
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
|
