Source

VPlayer / queries.py

  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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
# -*- coding: utf-8 -*-

import os
from PyQt4 import QtSql, QtCore

from vplayer import common
from vplayer.length import Length
from vplayer.track import Track
from vplayer.logger import log

def open_database():
    dbfile = os.path.join(common.get_config_dir(), 'database.db')
    if not os.path.exists(dbfile):
        f = open(dbfile, 'w')
        f.close()
    db = QtSql.QSqlDatabase().addDatabase('QSQLITE')
    db.setDatabaseName(dbfile)
    db.open()
    return db

def create_tables():
    "Create tables in database"

    query = QtSql.QSqlQuery()

    #collection
    query.exec_("""CREATE TABLE IF NOT EXISTS artists
                   (
                     id INTEGER NOT NULL PRIMARY KEY,
                     name TEXT UNIQUE COLLATE NOCASE
                   )""")
    query.exec_("""CREATE TABLE IF NOT EXISTS albums
                   (
                     id INTEGER NOT NULL PRIMARY KEY,
                     artist INTEGER,
                     title TEXT,
                     year INTEGER
                   )""")
    query.exec_("""CREATE TABLE IF NOT EXISTS tracks
                   (
                     id INTEGER NOT NULL PRIMARY KEY,
                     artist INTEGER,
                     album INTEGER,
                     title TEXT,
                     year INTEGER,
                     url TEXT,
                     length INTEGER,
                     trackno INTEGER,
                     cache BOOL
                   )""")

    #covers
    query.exec_("""CREATE TABLE IF NOT EXISTS covers
                   (
                     id INTEGER NOT NULL PRIMARY KEY,
                     artist TEXT,
                     album TEXT,
                     file TEXT
                   )""")

    #playlists
    query.exec_("""CREATE TABLE IF NOT EXISTS playlists_titles
                   (
                     id INTEGER NOT NULL PRIMARY KEY,
                     title TEXT
                   )""")
    query.exec_("""CREATE TABLE IF NOT EXISTS playlists_tracks
                   (
                     id INTEGER NOT NULL PRIMARY KEY,
                     artist TEXT,
                     title TEXT,
                     url TEXT,
                     length INTEGER,
                     playlist INTEGER
                   )""")

def get_artist_id(name):
    "Get artist id or None if it does not exist"
    query = QtSql.QSqlQuery("""SELECT id FROM artists
                               WHERE name = :artist COLLATE NOCASE""")
    query.bindValue((":artist"), QtCore.QVariant(name))
    if not query.exec_():
        log.error("Could not select artist id from database")
    if query.first():
        return query.value(0).toInt()[0]
    else:
        return None

def create_artist_id(name):
    "Creates artist record in databas if it does not exist and returns id"
    artist_id = get_artist_id(name)
    if not artist_id:
        query = QtSql.QSqlQuery("""INSERT INTO artists (name)
                                   VALUES (:artist)""")
        query.bindValue((":artist"), QtCore.QVariant(name))
        if not query.exec_():
            log.error("Could not insert artist record into database")
            return None
        artist_id = query.lastInsertId().toInt()[0]
    if not artist_id:
        return None
    return artist_id

def get_album_id(artistid, title, year = 0):
    "Get album id or None if it does not exist"
    query = QtSql.QSqlQuery("""SELECT id FROM albums
                               WHERE artist = :artistid AND
                                     title = :title COLLATE NOCASE AND
                                     year = :year""")
    query.bindValue((":artistid"), QtCore.QVariant(artistid))
    query.bindValue((":title"), QtCore.QVariant(title))
    query.bindValue((":year"), QtCore.QVariant(year))
    if not query.exec_():
        log.error("Could not select album id from database")
    if query.first():
        return query.value(0).toInt()[0]
    else:
        return None

def create_album_id(artistid, title, year = 0):
    "Create album record in database if it does not exist and returns id"
    album_id = get_album_id(artistid, title, year)
    if not album_id:
        query = QtSql.QSqlQuery("""INSERT INTO albums
                                          (artist, title, year)
                                   VALUES (:artistid, :title, :year)""")
        query.bindValue((":artistid"), QtCore.QVariant(artistid))
        query.bindValue((":title"), QtCore.QVariant(title))
        query.bindValue((":year"), QtCore.QVariant(year))
        if not query.exec_():
            log.error("Could not insert album record into database")
            return None
        album_id = query.lastInsertId().toInt()[0]
    if not album_id:
        return None
    return album_id

def insert_track(track, cache = True):
    "Insert track into database if it is not present there"
    artist_id = create_artist_id(track.artist)
    album_id = create_album_id(artist_id, track.album, track.year)

    query = QtSql.QSqlQuery("""SELECT id FROM tracks
                               WHERE artist = :artistid AND
                                     album = :albumid AND
                                     url = :url""")
    query.bindValue(":artistid", QtCore.QVariant(artist_id))
    query.bindValue(":albumid", QtCore.QVariant(album_id))
    query.bindValue(":url", QtCore.QVariant(track.url))
    if not query.exec_():
        log.error("Could not check if track is present in db")
        return False
    if query.first():
        log.debug("Track is already present in db")
        return True

    query = QtSql.QSqlQuery("""INSERT INTO tracks
                               ( artist, album, title, url, year, length,
                                 trackno, cache)
                               VALUES ( :artistid, :albumid, :title, :url,
                                        :year, :length, :trackno, :cache )""")

    query.bindValue(":artistid", QtCore.QVariant(artist_id))
    query.bindValue(":albumid", QtCore.QVariant(album_id))
    query.bindValue(":title", QtCore.QVariant(track.title))
    query.bindValue(":url", QtCore.QVariant(track.url))
    query.bindValue(":year", QtCore.QVariant(track.year))
    query.bindValue(":length", QtCore.QVariant(int(track.length)))
    query.bindValue(":trackno", QtCore.QVariant(int(track.trackno)))
    query.bindValue(":cache", QtCore.QVariant(cache))
    if not query.exec_():
        log.error("Could not insert track into database")
        return False
    return True

def delete_track(url):
    query = QtSql.QSqlQuery("""SELECT artist, album FROM tracks
                               WHERE url = :url""")
    query.bindValue(':url', QtCore.QVariant(url))
    if not query.exec_():
        log.error('Could not delete track from database')
        return False
    if not query.first():
        return True
    artist = query.value(0).toInt()[0]
    album = query.value(1).toInt()[0]

    query = QtSql.QSqlQuery("""DELETE FROM tracks
                               WHERE url = :url""")
    query.bindValue(':url', QtCore.QVariant(url))
    if not query.exec_():
        log.error('Could not delete track from database')
        return False

    tracks = get_track_list(artist, album)
    if tracks:
        return True
    query = QtSql.QSqlQuery("""DELETE FROM albums
                               WHERE id = :album""")
    query.bindValue(':album', QtCore.QVariant(album))
    if not query.exec_():
        log.error('Could not delete album from database')
        return False
    albums = get_album_list(artist)
    if albums:
        return True

    query = QtSql.QSqlQuery("""DELETE FROM artists
                               WHERE id = :artist""")
    query.bindValue(':artist', QtCore.QVariant(artist))
    if not query.exec_():
        log.error('Could not delete artist from database')
        return False

    return True


def update_track(track):
    if delete_track(track.url):
        insert_track(track)
    else:
        log.error('Cannot update track as it could not be deleted')


def get_track_url(track):
    artist_id = get_artist_id(track.artist)
    if not artist_id:
        log.debug('Artist id not found')
        return None
    query = QtSql.QSqlQuery("""SELECT url FROM tracks WHERE
                               artist = :artist AND
                               title = :title COLLATE NOCASE AND
                               length = :length""")
    query.bindValue(':artist', QtCore.QVariant(artist_id))
    query.bindValue(':title', QtCore.QVariant(track.title))
    query.bindValue(':length', QtCore.QVariant(int(track.length)))
    if not query.exec_():
        err = unicode(query.lastError().text())
        log.error('Could not select track from database: %s' % err)
    if query.first():
        return unicode(query.value(0).toString(), 'utf-8')
    else:
        return None

def get_artist_list(search = ''):
    if not search:
        query = QtSql.QSqlQuery("""SELECT id, name FROM artists
                                   GROUP BY name""")
    else:
        query = QtSql.QSqlQuery("""SELECT id, name FROM artists
                                   WHERE id IN
                                    (SELECT artist FROM albums WHERE id IN
                                     (SELECT album FROM tracks WHERE title LIKE ?)
                                     OR title LIKE ?)
                                   OR name LIKE ?
                                   GROUP BY name""")
        for i in range(3):
            query.addBindValue(QtCore.QVariant("%%%s%%" % search))
    if not query.exec_():
        err = unicode(query.lastError().text())
        log.error("Could not select artist names from database: %s" % err)
        return []
    names = []
    while query.next():
        names.append( { 'id' : query.value(0).toInt()[0],
                        'name': unicode(query.value(1).toString()) } )
    return names

def get_album_list(artist, search = ''):
    if not search:
        query = QtSql.QSqlQuery("""SELECT id, title, year FROM albums
                                  WHERE artist = :artist
                                  GROUP BY title""")
        query.bindValue(':artist', QtCore.QVariant(artist))
    else:
        query = QtSql.QSqlQuery("""SELECT id, title, year FROM albums
                                   WHERE artist = ? AND
                                   ( id IN ( SELECT album FROM tracks
                                            WHERE artist = ? AND
                                            title LIKE ? )
                                     OR title LIKE ? )""")
        query.addBindValue(QtCore.QVariant(artist))
        query.addBindValue(QtCore.QVariant(artist))
        query.addBindValue(QtCore.QVariant("%%%s%%" % search))
        query.addBindValue(QtCore.QVariant("%%%s%%" % search))
    if not query.exec_():
        err = unicode(query.lastError().text())
        log.error("Could not select albums from database: %s" % err)
        return []
    albums = []
    while query.next():
        albums.append( { 'id' : query.value(0).toInt()[0],
                         'title' : unicode(query.value(1).toString()),
                         'year' : query.value(2).toInt()[0] } )
    return albums

def get_track_list(artist, album, search = ''):
    if not search:
        query = QtSql.QSqlQuery("""SELECT id, title, year, url, length, trackno
                                  FROM tracks
                                  WHERE artist = :artist AND
                                        album = :album""")
        query.bindValue(':artist', QtCore.QVariant(artist))
        query.bindValue(':album', QtCore.QVariant(album))
    else:
        query = QtSql.QSqlQuery("""SELECT id, title, year, url, length, trackno
                                  FROM tracks
                                  WHERE artist = :artist AND
                                        album = :album AND
                                        title LIKE :search""")
        query.bindValue(':artist', QtCore.QVariant(artist))
        query.bindValue(':album', QtCore.QVariant(album))
        query.bindValue(':search', QtCore.QVariant("%%%s%%" % search))
    if not query.exec_():
        err = unicode(query.lastError().text())
        log.error("Could not select tracks from database: %s" % err)
        return []
    tracks = []
    while query.next():
        tracks.append( { 'id' : query.value(0).toInt()[0],
                        'title': unicode(query.value(1).toString()),
                        'year' : unicode(query.value(2).toString()),
                        'url' : unicode(query.value(3).toString()),
                        'length' : Length(query.value(4).toInt()[0]),
                        'trackno' : query.value(5).toInt()[0] } )
    return tracks

def get_album_cover(artist, album):
    artist_id = get_artist_id(artist)
    album_id = get_album_id(artist_id, album)
    query = QtSql.QSqlQuery("SELECT file FROM covers WHERE album = :album")
    query.bindValue(':album', QtCore.QVariant(album_id))
    if not query.exec_():
        log.error("Could not select cover from database")
        return []
    if query.first():
        return unicode(query.value(0).toString())
    else:
        return None

def add_album_cover(artist, album, filename):
    query = QtSql.QSqlQuery("""INSERT INTO covers (artist, album, file)
                               VALUES ( :artist, :album, :file)""")
    query.bindValue(':artist', QtCore.QVariant(artist))
    query.bindValue(':album', QtCore.QVariant(album))
    query.bindValue(':file', QtCore.QVariant(filename))
    if not query.exec_():
        log.error("Could not insert cover to database")
        return False
    return True

def get_album_cover(artist, album, year = 0):
    query = QtSql.QSqlQuery("""SELECT file FROM covers
                               WHERE artist = :artist AND album = :album""")
    query.bindValue(':artist', QtCore.QVariant(artist))
    query.bindValue(':album', QtCore.QVariant(album))
    if not query.exec_():
        log.error("Could not select cover file from database")
        return unicode()
    if not query.first():
        return unicode()
    return unicode(query.value(0).toString())

def add_playlist(title, tracks):
    if not tracks:
        return True
    delete_playlist(title)
    query = QtSql.QSqlQuery("""INSERT INTO playlists_titles (title)
                               VALUES (:title)""")
    query.bindValue(':title', QtCore.QVariant(title))
    if not query.exec_():
        log.error("Could not add playlist title to database")
        return False
    plid = query.lastInsertId().toInt()[0]
    for track in tracks:
        query = QtSql.QSqlQuery("""INSERT INTO playlists_tracks
                                   (artist, title, url, length, playlist)
                                   VALUES
                                   (:artist, :title, :url, :length, :playlist)""")
        query.bindValue(':artist', QtCore.QVariant(track.artist))
        query.bindValue(':title', QtCore.QVariant(track.title))
        query.bindValue(':url', QtCore.QVariant(track.url))
        query.bindValue(':length', QtCore.QVariant(int(track.length)))
        query.bindValue(':playlist', QtCore.QVariant(plid))
        if not query.exec_():
            log.error("Could not add track to database")
            return False
    return True

def get_playlist_id(title):
    query = QtSql.QSqlQuery("""SELECT id FROM playlists_titles
                               WHERE title = :title""")
    query.bindValue(':title', QtCore.QVariant(title))
    if not query.exec_():
        log.error('Could not select playlist id from database')
        return None
    if query.first():
        return query.value(0).toInt()[0]
    else:
        return None

def delete_playlist(title):
    plid = get_playlist_id(title)
    if not plid: return True
    query = QtSql.QSqlQuery("""DELETE FROM playlists_tracks
                               WHERE playlist = :plid""")
    query.bindValue(':plid', QtCore.QVariant(plid))
    if not query.exec_():
        log.error('Could not delete playlists tracks from database')
        return False
    query = QtSql.QSqlQuery("""DELETE FROM playlists_titles
                               WHERE id = :plid""")
    query.bindValue(':id', QtCore.QVariant(plid))
    if not query.exec_():
        log.error('Could not delete playlist title from database')
        return False
    return True

def get_playlist(title):
    plid = get_playlist_id(title)
    if plid is None:
        return []
    query = QtSql.QSqlQuery("""SELECT artist, title, url, length
                               FROM playlists_tracks
                               WHERE playlist = :plid""")
    query.bindValue(':plid', QtCore.QVariant(plid))
    if not query.exec_():
        log.error('Could not select playlists tracks from database')
        return []
    tracks = []
    while query.next():
        tracks.append( { 'artist' : unicode(query.value(0).toString()),
                         'title' : unicode(query.value(1).toString()),
                         'url' : unicode(query.value(2).toString()),
                         'length' : Length(query.value(3).toInt()[0]) } )
    return tracks

def get_playlists():
    query = QtSql.QSqlQuery("SELECT id, title FROM playlists_titles")
    if not query.exec_():
        log.error('Could not select playlists from database')
        return []
    playlists = []
    while query.next():
        playlists.append( { 'id' : query.value(0).toInt()[0],
                            'title' : unicode(query.value(1).toString()) } )
    return playlists

def get_playlist_duration(title):
    plid = get_playlist_id(title)
    if not plid:
        return 0
    query = QtSql.QSqlQuery("""SELECT SUM(length) FROM playlists_tracks
                               WHERE playlist = :plid""")
    query.bindValue(':plid', QtCore.QVariant(plid))
    if not query.exec_():
        log.error('Could not count playlist duration in database')
        return 0
    if not query.first():
        log.error('Could not select playlists length')
        return 0
    return query.value(0).toInt()[0]

def get_playlist_track_count(title):
    plid = get_playlist_id(title)
    if not plid:
        return 0
    query = QtSql.QSqlQuery("""SELECT COUNT(*) FROM playlists_tracks
                               WHERE playlist = :plid""")
    query.bindValue(':plid', QtCore.QVariant(plid))
    if not query.exec_():
        log.error('Could not count track number in database')
        return 0
    if not query.first():
        log.error('Could not select playlists tracks')
        return 0
    return query.value(0).toInt()[0]

def check_track_existance(url):
    query = QtSql.QSqlQuery("""SELECT url FROM tracks
                               WHERE url = :url""")
    query.bindValue(':url', QtCore.QVariant(url))
    if not query.exec_():
        log.error('Could not track existance in database')
        return False
    if query.first():
        return True
    return False

def get_all_cached_files():
    query = QtSql.QSqlQuery('SELECT url FROM tracks WHERE cache = "true"')
    if not query.exec_():
        err = query.lastError().text()
        log.error('Could not select cache files from database: %s' % err)
        return []
    res = []
    while query.next():
        res.append(unicode(query.value(0).toString()))
    return res

def get_all_collection_files():
    query = QtSql.QSqlQuery('SELECT url FROM tracks WHERE cache = "false"')
    if not query.exec_():
        err = query.lastError().text()
        log.error('Could not select collection files from database: %s' % err)
        return []
    res = []
    while query.next():
        res.append(unicode(query.value(0).toString()))
    return res

def clean_orphaned():
    query = QtSql.QSqlQuery("""DELETE FROM albums WHERE id NOT IN
                               (SELECT DISTINCT album FROM tracks)""")
    if not query.exec_():
        err = query.lastError().text()
        log.error('Could not delete orphaned albums from database: %s' % err)
        return False
    query = QtSql.QSqlQuery("""DELETE FROM artists WHERE id NOT IN
                               (SELECT DISTINCT artist FROM tracks)""")
    if not query.exec_():
        err = query.lastError().text()
        log.error('Could not delete orphaned artists from database: %s' % err)
        return False
    return True

def clear_cache():
    query = QtSql.QSqlQuery('DELETE FROM tracks WHERE cache = "true"')
    if not query.exec_():
        err = query.lastError().text()
        log.error('Could not delete cache files from database: %s' % err)
        return False
    return clean_orphaned()

def get_track_album_name(track):
    query = QtSql.QSqlQuery("""SELECT title FROM albums WHERE id IN
                               ( SELECT album FROM tracks WHERE
                                 title = :title COLLATE NOCASE AND
                                 artist IN (SELECT id FROM artists WHERE
                                           name = :artist COLLATE NOCASE LIMIT 1)
                                 LIMIT 1 )""")
    query.bindValue(':title', QtCore.QVariant(track.title))
    query.bindValue(':artist', QtCore.QVariant(track.artist))
    if not query.exec_():
        err = query.lastError().text()
        log.error('Could get track album name from database: %s' % err)
        return None
    if query.first():
        return unicode(query.value(0).toString())
    else:
        return None