File mkmusicdb.tcl artifact eba3f42920 on branch trunk

#!/usr/opt/bin/tclsh

if {$argc!=1} {puts "Usage: $argv0 Dir - generates Dir/index.sqlite"; exit}
set Dir [lindex $argv 0]
if {![file isdirectory $Dir]} {puts "argument must be a directory"; exit}
set DB $Dir/index.sqlite
if {[file exists $DB]} {puts "$DB exists"; exit}

package require sqlite3
sqlite3 db $DB
db eval {PRAGMA foreign_keys = ON}
proc match {x y} {return [regexp "^$y/.*" $x]}
db function match -argcount 2 -deterministic {match}

db eval {
CREATE TABLE track(
 trid INTEGER PRIMARY KEY, -- "Track ID"
 nid  TEXT,   -- / separated two digit numbers for entering with keyboard
 trp  TEXT,   -- real path in file system of an audio file (track)
 trtxt TEXT,  -- description of the audio file (track)
 cdid REFERENCES cd(cdid) ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE cd(
 cdid INTEGER PRIMARY KEY, -- "CD ID"
 cdp  TEXT UNIQUE, -- path to directory containing CD or CD collections
 cdtxt TEXT        -- description of audio files under cdp
);
CREATE VIEW conc AS SELECT * FROM track LEFT OUTER JOIN cd USING (cdid);
}

# cd contains the paths under 'base' containing files named 'Desc'

proc mkcd {base rpath} {
set apath [file join $base $rpath]
if {[file isfile $apath] && [file tail $apath] eq "Desc"} {
 set fd [open $apath r]; set txt [read $fd]; close $fd
 set rdir [file dirname $rpath]
 db eval {INSERT INTO cd (cdp, cdtxt) VALUES (:rdir, :txt)}
 return 1}
if {[file isdirectory $apath]} {
 foreach file [lsort [glob -nocomplain -tails -directory $apath *]] {
  mkcd $base [file join $rpath $file]}}}

# track contains paths trp to audiofiles (.wav, .mp3), 
# cdid points to innermost path from cd containing the file below it,
# trtxt is not jet filled.

proc mktrack {base rpath nid} {
set apath [file join $base $rpath]
if {[file isfile $apath]} {
 if {[regexp {.*\.wav$|.*\.mp3$} $apath]} {
  set cdid [db onecolumn {SELECT max(cdid) FROM cd WHERE cdp MATCH :rpath}]
  if {[string is integer -strict $cdid]} {
   db eval {INSERT INTO track (nid, trp, cdid) VALUES (:nid, :rpath, :cdid)}
   } else {
   db eval {INSERT INTO track (nid, trp) VALUES (:nid, :rpath)}}
  return 1} else {return 0}}
if {[file isdirectory $apath]} {
 if {$nid eq ""} {set nid 01} else {set nid "$nid/01"}
 foreach file [lsort [glob -nocomplain -tails -directory $apath *]] {
  if {[mktrack $base [file join $rpath $file] $nid]} {
   set nid [split $nid /]
   regsub "^0*" [lindex $nid end] "" n
   incr n; set n [format %02d $n]
   set nid [join [lreplace $nid end end $n] /]}}
 return 1}}

mkcd $Dir ""
mktrack $Dir "" ""
db close