Open Source Projects Europe

# along with this program.  If not, see <http://www.gnu.org/licenses/>.

# along with this program.  If not, see <http://www.gnu.org/licenses/>.

import sys

import sys

import os

import os

import sqlite3

import sqlite3

from timeit import default_timer as timer

from timeit import default_timer as timer

from uprclutils import g_myprefix, audiomtypes, docfolder, uplog, \

from uprclutils import g_myprefix, audiomtypes, docfolder, uplog, \

     rcldirentry, rcldoctoentry, cmpentries

     rcldirentry, rcldoctoentry, cmpentries

# After initialization, this holds the list of all records out of

# After initialization, this holds the list of all records out of

# recoll (it's a reference to the original in uprclfolders)

# recoll (it's a reference to the original in uprclfolders)

g_alldocs = []

g_alldocs = []

#sqconn = sqlite3.connect('/tmp/tracks.sqlite')

# Tags for which we create auxiliary tables for facet descent.

# Tags for which we create auxiliary tables for facet descent.

#

#

# TBD: The list will come from the config file one day

# TBD: The list will come from the config file one day

# TBD: alias et al configuration

# TBD: alias et al configuration

# Create the db and fill it up with the values we need, taken out of

# Create the db and fill it up with the values we need, taken out of

# the recoll records list

# the recoll records list

def recolltosql(docs):

def recolltosql(docs):

    global g_alldocs

    global g_alldocs

    g_alldocs = docs

    g_alldocs = docs

    _createsqdb(sqconn)

    _createsqdb(sqconn)

    # Compute a list of table names and corresponding recoll

    # Compute a list of table names and corresponding recoll

    # fields. most often they are identical

    # fields. most often they are identical

    tabfields = []

    tabfields = []

    totcnt = 0

    totcnt = 0

    for docidx in range(len(docs)):

    for docidx in range(len(docs)):

        doc = docs[docidx]

        doc = docs[docidx]

        totcnt += 1

        totcnt += 1

        # No need to include non-audio types in the visible tree.

        # No need to include non-audio types in the visible tree.

        if doc.mtype not in audiomtypes or doc.mtype == 'inode/directory':

        if doc.mtype not in audiomtypes or doc.mtype == 'inode/directory':

            continue

            continue

        # Create album record if needed.

        # Create album record if needed.

                c.execute(stmt, (artist_id, album_id))

                c.execute(stmt, (artist_id, album_id))

            except:

            except:

                pass

                pass

    sqconn.commit()

    sqconn.commit()

# Create our top-level directories, with fixed entries, and stuff from

# Create our top-level directories, with fixed entries, and stuff from

# the tags tables

# the tags tables

def rootentries(pid):

def rootentries(pid):

    c = sqconn.cursor()

    c.execute("SELECT COUNT(*) from albums")

    c.execute("SELECT COUNT(*) from albums")

    nalbs = str(c.fetchone()[0])

    nalbs = str(c.fetchone()[0])

    c.execute("SELECT COUNT(*) from tracks")

    c.execute("SELECT COUNT(*) from tracks")

    nitems = str(c.fetchone()[0])

    nitems = str(c.fetchone()[0])

    entries = [rcldirentry(pid + 'albums', pid, nalbs + ' albums'),

    entries = [rcldirentry(pid + 'albums', pid, nalbs + ' albums'),

# Check what tags still have multiple values inside the selected set,

# Check what tags still have multiple values inside the selected set,

# and return their list.

# and return their list.

def _subtreetags(docidsl):

def _subtreetags(docidsl):

    docids = ','.join([str(i) for i in docidsl])

    docids = ','.join([str(i) for i in docidsl])

    uplog("subtreetags, docids %s" % docids)

    uplog("subtreetags, docids %s" % docids)

    c = sqconn.cursor()

    tags = []

    tags = []

    for tt,tb in tagtables.iteritems():

    for tt,tb in tagtables.iteritems():

        stmt = 'SELECT COUNT(DISTINCT ' + _clid(tb) + \

        stmt = 'SELECT COUNT(DISTINCT ' + _clid(tb) + \

               ') FROM tracks WHERE docidx IN (' + docids + ')'

               ') FROM tracks WHERE docidx IN (' + docids + ')'

        uplog("subtreetags: executing: <%s>" % stmt)

        uplog("subtreetags: executing: <%s>" % stmt)

            if cnt > 1:

            if cnt > 1:

                tags.append(tt)

                tags.append(tt)

    return tags

    return tags

def _trackentriesforstmt(stmt, values, pid, httphp, pathprefix):

def _trackentriesforstmt(stmt, values, pid, httphp, pathprefix):

    c = sqconn.cursor()

    c.execute(stmt, values)

    c.execute(stmt, values)

    entries = []

    entries = []

    for r in c:

    for r in c:

        docidx = r[0]

        docidx = r[0]

        id = pid + '$i' + str(docidx)

        id = pid + '$i' + str(docidx)

# Return a list of trackids as selected by the current

# Return a list of trackids as selected by the current

# path <selwhere> is like: WHERE col1_id = ? AND col2_id = ? [...], and

# path <selwhere> is like: WHERE col1_id = ? AND col2_id = ? [...], and

# <values> holds the corresponding values

# <values> holds the corresponding values

def _docidsforsel(selwhere, values):

def _docidsforsel(selwhere, values):

    c = sqconn.cursor()

    stmt = 'SELECT docidx FROM tracks ' + selwhere + ' ORDER BY trackno'

    stmt = 'SELECT docidx FROM tracks ' + selwhere + ' ORDER BY trackno'

    uplog("docidsforsel: executing <%s> values %s" % (stmt, values))

    uplog("docidsforsel: executing <%s> values %s" % (stmt, values))

    c.execute(stmt, values)

    c.execute(stmt, values)

    return [r[0] for r in c.fetchall()]

    return [r[0] for r in c.fetchall()]

    stmt = 'SELECT docidx FROM tracks WHERE album_id = ? ORDER BY trackno'

    stmt = 'SELECT docidx FROM tracks WHERE album_id = ? ORDER BY trackno'

    return _trackentriesforstmt(stmt, (albid,), pid, httphp, pathprefix)

    return _trackentriesforstmt(stmt, (albid,), pid, httphp, pathprefix)

def _direntriesforalbums(pid, where):

def _direntriesforalbums(pid, where):

    entries = []

    entries = []

    c = sqconn.cursor()

    if not where:

    if not where:

        where = 'WHERE artist.artist_id = albums.artist_id'

        where = 'WHERE artist.artist_id = albums.artist_id'

    else:

    else:

        where += ' AND artist.artist_id = albums.artist_id'

        where += ' AND artist.artist_id = albums.artist_id'

    return entries

    return entries

# This is called when an 'albums' element is encountered in the

# This is called when an 'albums' element is encountered in the

# selection path.

# selection path.

def _tagsbrowsealbums(pid, qpath, i, selwhere, values, httphp, pathprefix):

def _tagsbrowsealbums(pid, qpath, i, selwhere, values, httphp, pathprefix):

    c = sqconn.cursor()

    docidsl = _docidsforsel(selwhere, values)

    docidsl = _docidsforsel(selwhere, values)

    entries = []

    entries = []

    if i == len(qpath)-1:

    if i == len(qpath)-1:

        albidsl = _subtreealbums(docidsl)

        albidsl = _subtreealbums(docidsl)

        albids = ','.join([str(a) for a in albidsl])

        albids = ','.join([str(a) for a in albidsl])

def _subtreealbums(docidsl):

def _subtreealbums(docidsl):

    docids = ','.join([str(r) for r in docidsl])

    docids = ','.join([str(r) for r in docidsl])

    albids = []

    albids = []

    stmt = 'SELECT album_id from tracks where docidx IN (' + docids + ') ' + \

    stmt = 'SELECT album_id from tracks where docidx IN (' + docids + ') ' + \

           'GROUP BY album_id'

           'GROUP BY album_id'

    c = sqconn.cursor()

    uplog('subtreealbums: executing %s' % stmt)

    uplog('subtreealbums: executing %s' % stmt)

    c.execute(stmt)

    c.execute(stmt)

    for r in c:

    for r in c:

        albids.append(r[0])

        albids.append(r[0])

    uplog('subtreealbums: returning %s' % albids)

    uplog('subtreealbums: returning %s' % albids)

        stmt = "SELECT " + selwhat + " FROM tracks, " + col + \

        stmt = "SELECT " + selwhat + " FROM tracks, " + col + \

               selwhere + \

               selwhere + \

               " GROUP BY tracks." + _clid(col) + \

               " GROUP BY tracks." + _clid(col) + \

               " ORDER BY value"

               " ORDER BY value"

        uplog("tagsbrowse: executing <%s> values %s" % (stmt,values))

        uplog("tagsbrowse: executing <%s> values %s" % (stmt,values))

        c = sqconn.cursor()

        c.execute(stmt, values)

        c.execute(stmt, values)

        for r in c:

        for r in c:

            id = pid + '$' + str(r[0])

            id = pid + '$' + str(r[0])

            entries.append(rcldirentry(id, pid, r[1]))

            entries.append(rcldirentry(id, pid, r[1]))

    return entries

    return entries

# Browse the top-level tree named like 'xxx albums'. There are just 2

# Browse the top-level tree named like 'xxx albums'. There are just 2

# levels: the whole albums list, then for each entry the specified

# levels: the whole albums list, then for each entry the specified

# albums track list

# albums track list

def _albumsbrowse(pid, qpath, flag, httphp, pathprefix):

def _albumsbrowse(pid, qpath, flag, httphp, pathprefix):

    c = sqconn.cursor()

    entries = []

    entries = []

    if len(qpath) == 1:

    if len(qpath) == 1:

        entries = _direntriesforalbums(pid, '')

        entries = _direntriesforalbums(pid, '')

    elif len(qpath) == 2:

    elif len(qpath) == 2:

        e1 = qpath[1]

        e1 = qpath[1]

############ Misc test/trial code, not used by uprcl ########################

############ Misc test/trial code, not used by uprcl ########################

def misctries():

def misctries():

    c = sqconn.cursor()

    c.execute('''SELECT COUNT(*) FROM tracks''')

    c.execute('''SELECT COUNT(*) FROM tracks''')

    uplog("Count(*) %d" % (c.fetchone()[0],))

    uplog("Count(*) %d" % (c.fetchone()[0],))

    #for row in c.execute('''SELECT album

    #for row in c.execute('''SELECT album

    #                        FROM tracks where artist LIKE "%Gould%"

    #                        FROM tracks where artist LIKE "%Gould%"