· 8 years ago · Mar 26, 2018, 03:42 PM
1"""
2Python script used to create the initial (empty) SQLite database
3file if required for the music database, and populate the database
4from the file generated by the ReleaseLogger plugin for Picard.
5
6Once processed, the source ReleaseLogger data file is appended to
7a backup file, and the source file reset to an empty file.
8
9Copyright © 2017, Bob Swift. Released under GPL-2.0 or later.
10"""
11
12import os
13import sys
14import time
15import datetime
16import sqlite3
17import unicodedata
18
19
20_FILE_LOG = 'ReleaseLogger.txt'
21_FILE_BACKUP = 'C:\\Databases\\Picard Music Database\\ReleaseLoggerProcessed.txt'
22
23_FILE_DB = 'C:\\Databases\\Picard Music Database\\MusicData.db'
24
25_DB_CONFIG = [
26 "X:\\Music\\CoverArt",
27]
28
29_TABLE_SPECS = {
30 'Configuration': [
31 ('CoverArtDir', 'TEXT'),
32 ],
33 'Releases': [
34 ('Selected', 'INTEGER'),
35 ('ReleaseID', 'TEXT'),
36 ('ReleaseYear', 'TEXT'),
37 ('ReleaseTypePrimary', 'TEXT'),
38 ('ReleaseTypeFull', 'TEXT'),
39 ('ReleaseDiscs', 'INTEGER'),
40 ('ReleaseTitle', 'TEXT'),
41 ('ReleaseArtist', 'TEXT'),
42 ('ReleaseArtistSort', 'TEXT'),
43 ('ReleaseInCollection', 'INTEGER'),
44 ('ReleaseAddedDate', 'TEXT'),
45 ],
46 'ReleaseMedia': [
47 ('ReleaseID', 'TEXT'),
48 ('MediaNumber', 'INTEGER'),
49 ('MediaType', 'TEXT'),
50 ('MediaName', 'TEXT'),
51 ],
52 'Artists': [
53 ('ArtistID', 'TEXT'),
54 ('ArtistName', 'TEXT'),
55 ('ArtistNameSort', 'TEXT'),
56 ],
57 'Tracks': [
58 ('ReleaseID', 'TEXT'),
59 ('TrackID', 'TEXT'),
60 ('RecordingID', 'TEXT'),
61 ('DiscNumber', 'INTEGER'),
62 ('TrackNumber', 'INTEGER'),
63 ('TrackLength', 'TEXT'),
64 ('TrackTitle', 'TEXT'),
65 ('TrackArtist', 'TEXT'),
66 ],
67 'ReleaseArtists': [
68 ('ReleaseID', 'TEXT'),
69 ('ArtistIndex', 'INTEGER'),
70 ('ArtistID', 'TEXT'),
71 ],
72 'TrackArtists': [
73 ('TrackID', 'TEXT'),
74 ('ArtistIndex', 'INTEGER'),
75 ('ArtistID', 'TEXT'),
76 ]
77}
78
79
80
81
82##############################################################################
83
84def erase_file(file_to_erase):
85 """
86 Delete the specified file if it exists.
87 """
88 if os.path.isfile(file_to_erase):
89 os.remove(file_to_erase)
90
91
92##############################################################################
93
94def timestring():
95 """
96 Return the local time string for the current time.
97 """
98 time_value = time.time()
99 mils = "%03d" % (int((time_value - int(time_value)) * 1000))
100 tistr = time.strftime("%H:%M:%S", time.localtime(time_value))
101 #return "%s.%s" % (tistr, mils)
102 return "%s" % (tistr)
103
104
105##############################################################################
106
107def ts_print(text_to_print):
108 """
109 Prints the specified text with the current timestamp.
110 """
111 print("%s: %s" % (timestring(), text_to_print))
112
113
114##############################################################################
115
116def make_table(tableName):
117 """
118 Creates the specified table within the database.
119 """
120 temp = []
121 for (a, b) in (_TABLE_SPECS[tableName]):
122 temp.append("%s %s" % (a, b))
123 if tableName == "Configuration":
124 tableSpec = ""
125 else:
126 tableSpec = "ID INTEGER PRIMARY KEY AUTOINCREMENT, "
127 tableSpec = tableSpec + ", ".join(temp[:])
128 ts_print("Creating database table: %s" % (tableName,))
129 with sqlite3.connect(_FILE_DB) as c:
130 cursor = c.cursor()
131 cursor.execute('PRAGMA encoding="UTF-8";')
132 createCommand = "CREATE TABLE %s (%s)" % (tableName, tableSpec)
133 cursor.execute(createCommand)
134 c.commit()
135
136
137##############################################################################
138
139def make_database():
140 """
141 Creates the SQLite database for the music data.
142 """
143 ts_print("Creating database file: %s" % (_FILE_DB,))
144 for table_name in _TABLE_SPECS.keys():
145 make_table(table_name)
146 ts_print("Databse file creation complete.")
147 initialize_database_configuration()
148
149
150##############################################################################
151
152def initialize_database_configuration():
153 """
154 Initializes the configuration for the SQLite music database.
155 """
156 if len(_DB_CONFIG) == len(_TABLE_SPECS["Configuration"]):
157 ts_print("Initializing database configuration.")
158 insertItem("Configuration", _DB_CONFIG)
159 else:
160 ts_print("ERROR! Invalid Configuration table information.")
161 sys.exit(3)
162 ts_print("Databse configuration initialized.")
163
164
165##############################################################################
166
167def checkItem(tableName, checkList, checkItems):
168 """
169 Checks if an item already exists in the SQLite music database.
170 """
171 if os.path.isfile(_FILE_DB):
172 if tableName in _TABLE_SPECS.keys():
173 with sqlite3.connect(_FILE_DB) as c:
174 cursor = c.cursor()
175 cursor.execute('PRAGMA encoding="UTF-8";')
176 createCommand = "SELECT COUNT(*) FROM %s WHERE %s" % (tableName, checkList,)
177# print("")
178# print(createCommand)
179# print(checkItems[:])
180 cursor.execute(createCommand, (checkItems[:]))
181 return int("%s" % cursor.fetchone())
182 else:
183 ts_print("ERROR! Table '%s' not found." % (tableName,))
184 sys.exit(2)
185 else:
186 ts_print("ERROR! Database not found.")
187 sys.exit(1)
188
189
190##############################################################################
191
192def insertItem(tableName, parts):
193 """
194 Inserts a new item in the SQLite music database.
195 """
196 if os.path.isfile(_FILE_DB):
197 if tableName in _TABLE_SPECS.keys():
198 cCount = len(_TABLE_SPECS[tableName])
199 if cCount:
200 tItems = []
201 qMarks = ", ".join("?" * cCount)
202 for (tItem, tSpec) in _TABLE_SPECS[tableName]:
203 tItems.append(tItem)
204 cmdItems = ", ".join(tItems[:])
205 with sqlite3.connect(_FILE_DB) as c:
206 cursor = c.cursor()
207 cursor.execute('PRAGMA encoding="UTF-8";')
208 createCommand = "INSERT INTO %s (%s) VALUES (%s)" % (tableName, cmdItems, qMarks)
209 cursor.execute(createCommand, (parts[:]))
210 c.commit()
211 else:
212 ts_print("ERROR! Table '%s' not found." % (tableName,))
213 sys.exit(2)
214 else:
215 ts_print("ERROR! Database not found.")
216 sys.exit(1)
217
218
219##############################################################################
220
221def main():
222 """
223 Main function of the module.
224 Creates a new SQLite music database, if necessary, and processes the
225 file generated by the ReleaseLogger plugin for Picarddatabase to populate
226 the database.
227 """
228 print("\n")
229 ts_print("Checking for database file.")
230 if not os.path.isfile(_FILE_DB):
231 make_database()
232 else:
233 ts_print("Database file found.")
234 ts_print("Checking for ReleaseLogger log file.")
235 if not os.path.isfile(_FILE_LOG):
236 ts_print("ERROR! ReleaseLogger log file not found.")
237 sys.exit(4)
238 lCount = [ 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 ]
239 # Offset: 0 = Lines Read
240 # 1 = Unknown Lines
241 # 2 = Blank Lines
242 # 3 = Releases Existing
243 # 4 = Releases Added
244 # 5 = Artists Existing
245 # 6 = Artists Added
246 # 7 = Tracks Existing
247 # 8 = Tracks Added
248 # 9 = ReleaseArtists Existing
249 # 10 = ReleaseArtists Added
250 # 11 = TrackArtists Existing
251 # 12 = TrackArtists Added
252 # 13 = Media Existing
253 # 14 = Media Added
254 # 15 = unused
255 # 16 = unused
256 # 17 = unused
257 # 18 = unused
258 # 19 = unused
259
260 ts_print("Processing the ReleaseLogger log file.")
261 with open(_FILE_LOG, encoding="utf8") as iFile:
262 for line in iFile:
263 lCount[0] += 1
264 lCountText = format(lCount[0], ",d")
265 print(" Line: %10s\r" % (lCountText), end="", flush=True)
266 parts = line.strip().split("^^^")
267 if len(parts):
268 #time.sleep(.2)
269 lineType = parts[0]
270 parts = parts[1:]
271 tableName = "Unknown"
272 checkItems = []
273 if lineType.upper() == "R":
274 parts.append(0,)
275 tableName = "Releases"
276 checkList = "ReleaseID=?"
277 checkItems.append(parts[0])
278 cOffset = 3
279 parts.insert(0, 0)
280 elif lineType.upper() == "A":
281 tableName = "Artists"
282 checkList = "ArtistID=?"
283 checkItems.append(parts[0])
284 cOffset = 5
285 elif lineType.upper() == "T":
286 tableName = "Tracks"
287 checkList = "TrackID=?"
288 checkItems.append(parts[1])
289 cOffset = 7
290 elif lineType.upper() == "RA":
291 tableName = "ReleaseArtists"
292 checkList = "ReleaseID=? AND ArtistID=?"
293 checkItems.append(parts[0])
294 checkItems.append(parts[2])
295 cOffset = 9
296 elif lineType.upper() == "TA":
297 tableName = "TrackArtists"
298 checkList = "TrackID=? AND ArtistID=?"
299 checkItems.append(parts[0])
300 checkItems.append(parts[2])
301 cOffset = 11
302 elif lineType.upper() == "M":
303 tableName = "ReleaseMedia"
304 checkList = "ReleaseID=? AND MediaNumber=?"
305 checkItems.append(parts[0])
306 checkItems.append(parts[1])
307 cOffset = 13
308 else:
309 lCount[1] += 1
310 if tableName in _TABLE_SPECS.keys():
311 if checkItem(tableName, checkList, checkItems):
312 lCount[cOffset] += 1
313 else:
314 insertItem(tableName, parts)
315 lCount[cOffset + 1] += 1
316 else:
317 lCount[2] += 1
318
319 ts_print("Data import complete.")
320 ts_print("Backing up the ReleaseLogger log file.")
321 with open(_FILE_BACKUP, "a", encoding="utf8") as oFile:
322 with open(_FILE_LOG, encoding="utf8") as iFile:
323 oFile.write(iFile.read())
324 with open(_FILE_LOG, "w") as iFile:
325 pass
326 ts_print("Log file moved to processed backup file.")
327
328 a = format(lCount[0], ",d")
329 b = format(lCount[2], ",d")
330 c = format(lCount[1], ",d")
331 print("\nLines Processed: %s (%s blank, %s unknown)" % (a, b, c,))
332 a = format(lCount[4], ",d")
333 b = format(lCount[3], ",d")
334 print("Releases Added: %s (%s existing)" % (a, b,))
335 a = format(lCount[14], ",d")
336 b = format(lCount[13], ",d")
337 print("Release Media Added: %s (%s existing)" % (a, b,))
338 a = format(lCount[6], ",d")
339 b = format(lCount[5], ",d")
340 print("Artists Added: %s (%s existing)" % (a, b,))
341 a = format(lCount[8], ",d")
342 b = format(lCount[7], ",d")
343 print("Tracks Added: %s (%s existing)" % (a, b,))
344 a = format(lCount[10], ",d")
345 b = format(lCount[9], ",d")
346 print("Release Artists Added: %s (%s existing)" % (a, b,))
347 a = format(lCount[12], ",d")
348 b = format(lCount[11], ",d")
349 print("Track Artists Added: %s (%s existing)" % (a, b,))
350 print("")
351
352
353##############################################################################
354
355if __name__ == '__main__':
356 main()
357
358
359##############################################################################