--- Aubin Paul <[EMAIL PROTECTED]> wrote:
> No objection from me; I always had iTunes in mind
> when I was looking
> into the music db.
>
> Aubin
Hi,
It was a bit more work than i thought.
And are stil some open issues:
-I need a table-versioning scheme.
I saw something about that in the epg mails.
minimal version in dbutil patch.
-mmpython(eyeD3) will return genre but no bitrate and
comment.(on my id3v1.1 files)
I used a modified version of mmpython and mp3info.py
to
extract the data.
no patch supplied.
-There needs to ba a way to detect a file has
changed(tag update),so the database gets updated too.
Add add_date and file_date fields to music table?
Index: extendedmeta.py
===================================================================
RCS file:
/cvsroot/freevo/freevo/src/util/extendedmeta.py,v
retrieving revision 1.13
diff -u -r1.13 extendedmeta.py
--- extendedmeta.py 10 Jul 2004 12:33:42 -0000 1.13
+++ extendedmeta.py 26 Oct 2004 19:53:26 -0000
@@ -59,8 +59,22 @@
dbschema = """CREATE TABLE music (id INTEGER PRIMARY
KEY, dirtitle VARCHAR(255), path VARCHAR(255),
filename VARCHAR(255), type VARCHAR(3),
artist VARCHAR(255), title VARCHAR(255), album
VARCHAR(255),
year VARCHAR(255), track NUMERIC(3),
track_total NUMERIC(3), bpm NUMERIC(3), last_play
float,
- play_count NUMERIC, start_time NUMERIC,
end_time NUMERIC, rating NUMERIC, eq VARCHAR)"""
+ play_count NUMERIC, start_time NUMERIC,
end_time NUMERIC, rating NUMERIC, eq VARCHAR,
+ bitrate NUMERIC,genre VARCHAR(255),comment
VARCHAR(255)
+ )"""
+
+#sequence of sql statements for db-update:
+dbupdate_sql = {}
+dbupdate_sql[(1,2)] = [ #v1->v2
+dbschema.replace('CREATE TABLE music','CREATE
TEMPORARY TABLE tt_music')
+,"insert into tt_music select *,0,'','' from music"
+,'DROP TABLE music'
+,dbschema
+,'insert into music select * from tt_music'
+,'DROP TABLE tt_music'
+]
+
def make_query(filename,dirtitle):
if not os.path.exists(filename):
print "File %s does not exist" % (filename)
@@ -68,38 +82,113 @@
a = mediainfo.get(filename)
t = tracknum(a['trackno'])
-
- VALUES =
"(null,\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',\'%s\',%i,%i,%i,\'%s\',%f,%i,\'%s\',\'%s\',%i,\'%s\')"
\
- %
(util.escape(dirtitle),util.escape(os.path.dirname(filename)),util.escape(os.path.basename(filename)),
\
-
'mp3',util.escape(a['artist']),util.escape(a['title']),util.escape(a['album']),inti(a['date']),t[0],
\
- t[1],
100,0,0,'0',inti(a['length']),0,'null')
-
+ try:
+ bitrate = int(a['bitrate'])
+ except:
+ bitrate = 1 #some tags are really bad.
+ if bitrate > 1000: #? returns bitrate*1000?
+ bitrate = bitrate / 1000
+ try: #id3v1 permits invalid years.
+ year = int(['date'])
+ except:
+ year = 0
+ length = inti(a['length'])
+ if length == '':
+ length = 0
+
+ VALUES = "(null,'%s','%s','%s','%s','%s'\
+ ,'%s','%s',%i,%i,%i,'%s',%f,%i,'%s'\
+ ,'%s',%i,'%s',%i,'%s','%s')\
+ " % (util.escape(dirtitle)
+ ,util.escape(os.path.dirname(filename))
+ ,util.escape(os.path.basename(filename)),
+
'mp3',util.escape(a['artist']),util.escape(a['title'])
+ ,util.escape(a['album']),year,t[0]
+ ,t[1], 100,0,0,'0',a['length'],0,'null'
+
,bitrate,util.escape(a['genre']),util.escape(a['comment']))
+
SQL = 'INSERT OR IGNORE INTO music VALUES ' +
VALUES
return SQL
-def addPathDB(path, dirtitle, type='*.mp3',
verbose=True):
+def make_query_update(filename,dirtitle):
+ #file changed or database updated.
+ #todo:file changed,important for changed id3
tags.
+ #this version:only updates id3-tags+bitrate
+ if not os.path.exists(filename):
+ print "File %s does not exist" % (filename)
+ return None
+ a = mediainfo.get(filename)
+ t = tracknum(a['trackno'])
+ try:
+ bitrate = int(a['bitrate'])
+ except:
+ bitrate = 1 #some tags are really bad.
+ if bitrate > 1000: #? returns bitrate*1000?
+ bitrate = bitrate / 1000
+ try: #id3v1 permits invalid years.
+ year = int(['date'])
+ except:
+ year = 0
+
+ SQL = """update music set
artist='%s',album='%s',track=%i,title='%s'
+ ,year=%i,bitrate=%i,genre='%s',comment='%s'
+ where path='%s' and filename='%s'
+ """ % (util.escape(a['artist'])
+ ,util.escape(a['album'])
+ ,t[0]
+ ,util.escape(a['title'])
+ ,year
+ ,bitrate
+ ,util.escape(a['genre'])
+ ,util.escape(a['comment'])
+ ,util.escape(os.path.dirname(filename))
+ ,util.escape(os.path.basename(filename)),
+ )
+
+ return SQL
+
+def addPathDB(path, dirtitle, type='*.mp3',
verbose=True):
+ dbupdate = False
# Get some stuff ready
count = 0
+ updcount = 0
db = MetaDatabase();
- if not db.checkTable('music'):
+ version = db.checkTableVersion('music')
+ if not version:
db.runQuery(dbschema)
+ elif version <> 2:
+ if verbose:
+ print 'updating database
definitions...................'
+ dbupdate = True
+ for sql in dbupdate_sql[(version,2)]:
+ db.runQuery(sql,close=False)
+ db.commit()
+
# Compare and contrast the db to the disc
songs = util.recursefolders(path,1,type,1)
- for row in db.runQuery('SELECT path, filename
FROM music'):
- try:
-
songs.remove(os.path.join(row['path'],row['filename']))
+ updsongs = []
+ for row in db.runQuery('SELECT path, filename
FROM music'):
+ filename =
os.path.join(row['path'],row['filename'])
+ if filename in songs:
+ if dbupdate: #todo:or file changed!
+ updsongs.append(filename)
+ updcount +=1
+
+ songs.remove(filename)
count = count + 1
- except ValueError:
- # Why doesn't it just give a return code
- pass
if count > 0 and verbose:
print " Skipped %i songs already in the
database..." % (count)
for song in songs:
db.runQuery(make_query(song,dirtitle))
+
+ if updcount > 0 and verbose:
+ print " Updating %i songs..." % (count)
+ for song in updsongs:
+ db.runQuery(make_query_update(song,dirtitle))
db.close()
Index: dbutil.py
===================================================================
RCS file: /cvsroot/freevo/freevo/src/util/dbutil.py,v
retrieving revision 1.6
diff -u -r1.6 dbutil.py
--- dbutil.py 10 Jul 2004 12:33:42 -0000 1.6
+++ dbutil.py 26 Oct 2004 20:03:28 -0000
@@ -83,7 +83,6 @@
# defines:
DATABASE = os.path.join(config.FREEVO_CACHEDIR,
'freevo.sqlite')
-
try:
import sqlite
except:
@@ -99,7 +98,7 @@
self.cursor = self.db.cursor()
def runQuery(self,query, close=False):
- try:
+ try:
self.cursor.execute(query)
except TypeError:
traceback.print_exc()
@@ -131,4 +130,24 @@
return None
return table
+ def checkTableVersion(self,table=None):
+ """
+ returns version of a table as an integer
+ or None if none exists
+ """
+ #Todo:better versioning scheme.
+ self.cursor.execute('SELECT sql FROM
sqlite_master where \
+ name="%s" and
type="table"' % table)
+ sql = self.cursor.fetchone()
+ if not sql:
+ return None
+ sql = sql[0]
+ if table == 'music':
+ version = 1
+ if sql.find('bitrate') > 0:
+ version = 2
+ else:
+ raise NotImplementedError('No versioning
scheme for '+table)
+ return version
+
__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail
-------------------------------------------------------
This SF.Net email is sponsored by:
Sybase ASE Linux Express Edition - download now for FREE
LinuxWorld Reader's Choice Award Winner for best database on Linux.
http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click
_______________________________________________
Freevo-devel mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/freevo-devel
