upmpdcli / Code / Diff of /src/mediaserver/cdplugins/uprcl/uprcltags.py

Diff of /src/mediaserver/cdplugins/uprcl/uprcltags.py [5f1aca] .. [41e394]

Switch to unified view


...
#
# You should have received a copy of the GNU General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

import sys
import os
import sqlite3
from timeit import default_timer as timer

from uprclutils import g_myprefix, audiomtypes, docfolder, uplog, \
     rcldirentry, rcldoctoentry, cmpentries
...
        c.execute('''DROP TABLE albums''')
        c.execute('''DROP TABLE tracks''')
    except:
        pass
    c.execute(
        '''CREATE TABLE albums (album_id INTEGER PRIMARY KEY, artist_id INT,
           albtitle TEXT, albfolder TEXT)''')

    tracksstmt = '''CREATE TABLE tracks
    (docidx INT, album_id INT, trackno INT, title TEXT'''

    for tb in tagtables.itervalues():
...
    totcnt = 0
    for docidx in range(len(docs)):
        doc = docs[docidx]
        totcnt += 1

        # No need to include non-audio types in the visible tree.
        if doc.mtype not in audiomtypes or doc.mtype == 'inode/directory':



            continue

        # Create album record if needed.
        # The albums table is special, can't use auxtableinsert()
        folder = docfolder(doc).decode('utf-8', errors = 'replace')
        album = getattr(doc, 'album', None)
        if not album:


            uplog("No album: mtype %s title %s" % (doc.mtype, doc.url))
            album = os.path.basename(folder)
        if doc.albumartist:
            albartist_id = _auxtableinsert(sqconn, 'artist', doc.albumartist)
        else:
            albartist_id = None
        c.execute('''SELECT album_id,artist_id FROM albums
        WHERE albtitle = ? AND albfolder = ?''', (album, folder))
        r = c.fetchone()
        if r:
            album_id = r[0]
            albartist_id = r[1]
        else:
            c.execute('''INSERT INTO albums(albtitle, albfolder, artist_id)
            VALUES (?,?,?)''', (album, folder, albartist_id))
            album_id = c.lastrowid

        # tracknos like n/max are now supposedly processed by rclaudio
        # and should not arrive here
        try:
            l= doc.tracknumber.split('/')
            trackno = int(l[0])
        except:
            trackno = 0














        # Set base values for column names, values list, placeholders,
        # then append data from auxiliary tables array
        columns = 'docidx,album_id,trackno,title'
        values = [docidx, album_id, trackno, doc.title]
        placehold = '?,?,?,?'
        for tb,rclfld in tabfields:
...
            rowid = _auxtableinsert(sqconn, tb, value)
            columns += ',' + _clid(tb)
            values.append(rowid)
            placehold += ',?'

        # Finally create the main record in the tracks table with
        # references to the aux tables
        stmt = 'INSERT INTO tracks(' + columns + ') VALUES(' + placehold + ')'
        c.execute(stmt, values)
        #uplog(doc.title)

        if not albartist_id:
            lcols = columns.split(',')
            try:
                i = lcols.index('artist_id')
                artist_id = values[i]
                stmt = 'UPDATE albums SET artist_id = ? WHERE album_id = ?'
                c.execute(stmt, (artist_id, album_id))
            except:
                pass
                      
    sqconn.commit()
    uplog("recolltosql: processed %d docs" % totcnt)


# Create our top-level directories, with fixed entries, and stuff from
...
    return [r[0] for r in c.fetchall()]

def _trackentriesforalbum(albid, pid, httphp, pathprefix):
    stmt = 'SELECT docidx FROM tracks WHERE album_id = ? ORDER BY trackno'
    return _trackentriesforstmt(stmt, (albid,), pid, httphp, pathprefix)

def _direntriesforalbums(pid, where):
    entries = []
    c = sqconn.cursor()
    if not where:
        where = 'WHERE artist.artist_id = albums.artist_id'
    else:
        where += ' AND artist.artist_id = albums.artist_id'

    stmt = 'SELECT album_id, albtitle, artist.value FROM albums,artist ' + \
              where + ' ORDER BY albtitle'
    uplog('direntriesforalbums: %s' % stmt)
    c.execute(stmt)
    for r in c:
        id = pid + '$' + str(r[0])
        entries.append(rcldirentry(id, pid, r[1], artist=r[2]))
    return entries

# This is called when an 'albums' element is encountered in the
# selection path.
def _tagsbrowsealbums(pid, qpath, i, selwhere, values, httphp, pathprefix):
    c = sqconn.cursor()
    docidsl = _docidsforsel(selwhere, values)
    entries = []
    if i == len(qpath)-1:
        albidsl = _subtreealbums(docidsl)
        albids = ','.join([str(a) for a in albidsl])
        where = ' WHERE album_id in (' + albids + ') '
        entries = _direntriesforalbums(pid, where)



    elif i == len(qpath)-2:
        albid = int(qpath[-1])
        docids = ','.join([str(i) for i in docidsl])
        stmt = 'SELECT COUNT(docidx) FROM tracks WHERE album_id = ?'
        c.execute(stmt, (albid,))
...
        # ORDER BY col.value
        stmt = "SELECT " + selwhat + " FROM tracks, " + col + \
               selwhere + \
               " GROUP BY tracks." + _clid(col) + \
               " ORDER BY value"
        uplog("tagsbrowse: executing <%s> values %s" % (stmt,values))
        c = sqconn.cursor()
        c.execute(stmt, values)
        for r in c:
            id = pid + '$' + str(r[0])
            entries.append(rcldirentry(id, pid, r[1]))
...
# albums track list
def _albumsbrowse(pid, qpath, flag, httphp, pathprefix):
    c = sqconn.cursor()
    entries = []
    if len(qpath) == 1:
        entries = _direntriesforalbums(pid, '')



    elif len(qpath) == 2:
        e1 = qpath[1]


        album_id = int(e1)
        entries = _trackentriesforalbum(album_id, pid, httphp, pathprefix)
    else:
        raise Exception("Bad path in album tree (too deep): <%s>" % qpath)

    return entries


# Top level browse routine. Handle the special cases and call the

	a/src/mediaserver/cdplugins/uprcl/uprcltags.py		b/src/mediaserver/cdplugins/uprcl/uprcltags.py
	...		...
13	#	13	#
14	# You should have received a copy of the GNU General Public License	14	# You should have received a copy of the GNU General Public License
15	# along with this program. If not, see <http://www.gnu.org/licenses/>.	15	# along with this program. If not, see <http://www.gnu.org/licenses/>.
16		16
17	import sys	17	import sys
		18	import os
18	import sqlite3	19	import sqlite3
19	from timeit import default_timer as timer	20	from timeit import default_timer as timer
20		21
21	from uprclutils import g_myprefix, audiomtypes, docfolder, uplog, \	22	from uprclutils import g_myprefix, audiomtypes, docfolder, uplog, \
22	rcldirentry, rcldoctoentry, cmpentries	23	rcldirentry, rcldoctoentry, cmpentries
	...		...
67	c.execute('''DROP TABLE albums''')	68	c.execute('''DROP TABLE albums''')
68	c.execute('''DROP TABLE tracks''')	69	c.execute('''DROP TABLE tracks''')
69	except:	70	except:
70	pass	71	pass
71	c.execute(	72	c.execute(
72	'''CREATE TABLE albums	73	'''CREATE TABLE albums (album_id INTEGER PRIMARY KEY, artist_id INT,
73	(album_id INTEGER PRIMARY KEY, albtitle TEXT, albfolder TEXT)''')	74	albtitle TEXT, albfolder TEXT)''')
74		75
75	tracksstmt = '''CREATE TABLE tracks	76	tracksstmt = '''CREATE TABLE tracks
76	(docidx INT, album_id INT, trackno INT, title TEXT'''	77	(docidx INT, album_id INT, trackno INT, title TEXT'''
77		78
78	for tb in tagtables.itervalues():	79	for tb in tagtables.itervalues():
	...		...
127	totcnt = 0	128	totcnt = 0
128	for docidx in range(len(docs)):	129	for docidx in range(len(docs)):
129	doc = docs[docidx]	130	doc = docs[docidx]
130	totcnt += 1	131	totcnt += 1
131		132
132	# No need to include non-audio types in the visible	133	# No need to include non-audio types in the visible tree.
133	# tree.	134	if doc.mtype not in audiomtypes or doc.mtype == 'inode/directory':
134	# TBD: We'll have to do some processing on image types though
135	# (will go before these lines)
136	if doc.mtype not in audiomtypes:
137	continue	135	continue
138		136
		137	# Create album record if needed.
		138	# The albums table is special, can't use auxtableinsert()
		139	folder = docfolder(doc).decode('utf-8', errors = 'replace')
139	album = getattr(doc, 'album', None)	140	album = getattr(doc, 'album', None)
140	if not album:	141	if not album:
141	if doc.mtype != 'inode/directory':
142	pass
143	#uplog("No album: mtype %s title %s" % (doc.mtype, doc.url))	142	uplog("No album: mtype %s title %s" % (doc.mtype, doc.url))
		143	album = os.path.basename(folder)
		144	if doc.albumartist:
		145	albartist_id = _auxtableinsert(sqconn, 'artist', doc.albumartist)
		146	else:
		147	albartist_id = None
		148	c.execute('''SELECT album_id,artist_id FROM albums
		149	WHERE albtitle = ? AND albfolder = ?''', (album, folder))
		150	r = c.fetchone()
		151	if r:
144	album = '[no album]'	152	album_id = r[0]
145	continue	153	albartist_id = r[1]
146		154	else:
147	folder = docfolder(doc).decode('utf-8', errors = 'replace')	155	c.execute('''INSERT INTO albums(albtitle, albfolder, artist_id)
		156	VALUES (?,?,?)''', (album, folder, albartist_id))
		157	album_id = c.lastrowid
148		158
149	# tracknos like n/max are now supposedly processed by rclaudio	159	# tracknos like n/max are now supposedly processed by rclaudio
150	# and should not arrive here	160	# and should not arrive here
151	try:	161	try:
152	l= doc.tracknumber.split('/')	162	l= doc.tracknumber.split('/')
153	trackno = int(l[0])	163	trackno = int(l[0])
154	except:	164	except:
155	trackno = 0	165	trackno = 0
156
157	# Create album record if needed. There is probably a
158	# single-statement syntax for this. The albums table is
159	# special, can't use auxtableinsert()
160	c.execute('''SELECT album_id FROM albums
161	WHERE albtitle = ? AND albfolder = ?''', (album, folder))
162	r = c.fetchone()
163	if r:
164	album_id = r[0]
165	else:
166	c.execute('''INSERT INTO albums(albtitle, albfolder)
167	VALUES (?,?)''', (album, folder))
168	album_id = c.lastrowid
169		166
170	# set base values for column names, values list, placeholders,	167	# Set base values for column names, values list, placeholders,
171	# then append data from auxiliary tables array	168	# then append data from auxiliary tables array
172	columns = 'docidx,album_id,trackno,title'	169	columns = 'docidx,album_id,trackno,title'
173	values = [docidx, album_id, trackno, doc.title]	170	values = [docidx, album_id, trackno, doc.title]
174	placehold = '?,?,?,?'	171	placehold = '?,?,?,?'
175	for tb,rclfld in tabfields:	172	for tb,rclfld in tabfields:
	...		...
179	rowid = _auxtableinsert(sqconn, tb, value)	176	rowid = _auxtableinsert(sqconn, tb, value)
180	columns += ',' + _clid(tb)	177	columns += ',' + _clid(tb)
181	values.append(rowid)	178	values.append(rowid)
182	placehold += ',?'	179	placehold += ',?'
183		180
		181	# Finally create the main record in the tracks table with
		182	# references to the aux tables
184	stmt = 'INSERT INTO tracks(' + columns + ') VALUES(' + placehold + ')'	183	stmt = 'INSERT INTO tracks(' + columns + ') VALUES(' + placehold + ')'
185	c.execute(stmt, values)	184	c.execute(stmt, values)
186	#uplog(doc.title)	185	#uplog(doc.title)
187		186
		187	if not albartist_id:
		188	lcols = columns.split(',')
		189	try:
		190	i = lcols.index('artist_id')
		191	artist_id = values[i]
		192	stmt = 'UPDATE albums SET artist_id = ? WHERE album_id = ?'
		193	c.execute(stmt, (artist_id, album_id))
		194	except:
		195	pass
		196
188	sqconn.commit()	197	sqconn.commit()
189	uplog("recolltosql: processed %d docs" % totcnt)	198	uplog("recolltosql: processed %d docs" % totcnt)
190		199
191		200
192	# Create our top-level directories, with fixed entries, and stuff from	201	# Create our top-level directories, with fixed entries, and stuff from
	...		...
245	return [r[0] for r in c.fetchall()]	254	return [r[0] for r in c.fetchall()]
246		255
247	def _trackentriesforalbum(albid, pid, httphp, pathprefix):	256	def _trackentriesforalbum(albid, pid, httphp, pathprefix):
248	stmt = 'SELECT docidx FROM tracks WHERE album_id = ? ORDER BY trackno'	257	stmt = 'SELECT docidx FROM tracks WHERE album_id = ? ORDER BY trackno'
249	return _trackentriesforstmt(stmt, (albid,), pid, httphp, pathprefix)	258	return _trackentriesforstmt(stmt, (albid,), pid, httphp, pathprefix)
250		259
		260	def _direntriesforalbums(pid, where):
		261	entries = []
		262	c = sqconn.cursor()
		263	if not where:
		264	where = 'WHERE artist.artist_id = albums.artist_id'
		265	else:
		266	where += ' AND artist.artist_id = albums.artist_id'
		267
		268	stmt = 'SELECT album_id, albtitle, artist.value FROM albums,artist ' + \
		269	where + ' ORDER BY albtitle'
		270	uplog('direntriesforalbums: %s' % stmt)
		271	c.execute(stmt)
		272	for r in c:
		273	id = pid + '$' + str(r[0])
		274	entries.append(rcldirentry(id, pid, r[1], artist=r[2]))
		275	return entries
		276
251	# This is called when an 'albums' element is encountered in the	277	# This is called when an 'albums' element is encountered in the
252	# selection path.	278	# selection path.
253	def _tagsbrowsealbums(pid, qpath, i, selwhere, values, httphp, pathprefix):	279	def _tagsbrowsealbums(pid, qpath, i, selwhere, values, httphp, pathprefix):
254	c = sqconn.cursor()	280	c = sqconn.cursor()
255	docidsl = _docidsforsel(selwhere, values)	281	docidsl = _docidsforsel(selwhere, values)
256	entries = []	282	entries = []
257	if i == len(qpath)-1:	283	if i == len(qpath)-1:
258	albidsl = _subtreealbums(docidsl)	284	albidsl = _subtreealbums(docidsl)
259	albids = ','.join([str(a) for a in albidsl])	285	albids = ','.join([str(a) for a in albidsl])
260	c.execute('SELECT album_id, albtitle FROM albums WHERE album_id in (' +	286	where = ' WHERE album_id in (' + albids + ') '
261	albids + ') ORDER BY albtitle')	287	entries = _direntriesforalbums(pid, where)
262	for r in c:
263	id = pid + '$' + str(r[0])
264	entries.append(rcldirentry(id, pid, r[1]))
265	elif i == len(qpath)-2:	288	elif i == len(qpath)-2:
266	albid = int(qpath[-1])	289	albid = int(qpath[-1])
267	docids = ','.join([str(i) for i in docidsl])	290	docids = ','.join([str(i) for i in docidsl])
268	stmt = 'SELECT COUNT(docidx) FROM tracks WHERE album_id = ?'	291	stmt = 'SELECT COUNT(docidx) FROM tracks WHERE album_id = ?'
269	c.execute(stmt, (albid,))	292	c.execute(stmt, (albid,))
	...		...
391	# ORDER BY col.value	414	# ORDER BY col.value
392	stmt = "SELECT " + selwhat + " FROM tracks, " + col + \	415	stmt = "SELECT " + selwhat + " FROM tracks, " + col + \
393	selwhere + \	416	selwhere + \
394	" GROUP BY tracks." + _clid(col) + \	417	" GROUP BY tracks." + _clid(col) + \
395	" ORDER BY value"	418	" ORDER BY value"
396	uplog("tagsbrowse: executing <%s> values %s" % (stmt, values))	419	uplog("tagsbrowse: executing <%s> values %s" % (stmt,values))
397	c = sqconn.cursor()	420	c = sqconn.cursor()
398	c.execute(stmt, values)	421	c.execute(stmt, values)
399	for r in c:	422	for r in c:
400	id = pid + '$' + str(r[0])	423	id = pid + '$' + str(r[0])
401	entries.append(rcldirentry(id, pid, r[1]))	424	entries.append(rcldirentry(id, pid, r[1]))
	...		...
407	# albums track list	430	# albums track list
408	def _albumsbrowse(pid, qpath, flag, httphp, pathprefix):	431	def _albumsbrowse(pid, qpath, flag, httphp, pathprefix):
409	c = sqconn.cursor()	432	c = sqconn.cursor()
410	entries = []	433	entries = []
411	if len(qpath) == 1:	434	if len(qpath) == 1:
412	c.execute('SELECT album_id, albtitle FROM albums ORDER BY albtitle')	435	entries = _direntriesforalbums(pid, '')
413	for r in c:
414	id = pid + '$*' + str(r[0])
415	entries.append(rcldirentry(id, pid, r[1]))
416	elif len(qpath) == 2:	436	elif len(qpath) == 2:
417	e1 = qpath[1]	437	e1 = qpath[1]
418	if not e1.startswith("*"):
419	raise Exception("Bad album id in albums tree. Pth: %s" %idpath)
420	album_id = int(e1[1:])	438	album_id = int(e1)
421	entries = _trackentriesforalbum(album_id, pid, httphp, pathprefix)	439	entries = _trackentriesforalbum(album_id, pid, httphp, pathprefix)
422	else:	440	else:
423	raise Exception("Bad path in album tree (too deep): <%s>"%idpath)	441	raise Exception("Bad path in album tree (too deep): <%s>" % qpath)
424		442
425	return entries	443	return entries
426		444
427		445
428	# Top level browse routine. Handle the special cases and call the	446	# Top level browse routine. Handle the special cases and call the