| main | console | Legend - Hand of God | web | contact |
| cd.* | music.* | ogg.* |
| applyTracklist | dbIndex | makeMp3 | makeOgg | makeWav |
This script is called with directories as parameter music.applyTracklist dir_1 dir_2 .. dir_n. It takes all audio files (mp3 and ogg) from the specified directories, reads their tags and writes them into a MySQL database.
The settings to connect to the database need to be specified at the beginning of the script.
# mysql settings set config(host) "localhost" ;# the mysql server set config(user) "tcl" ;# the user (needs enough permissions to access and update the db) set config(pass) "tcl" ;# the user's password set config(base) "music" ;# the database to use
The script can be run again and again. Then it will update those entries that changed, add new entries, or delete those belonging to files that do not exist anymore.
Like music.applyTracklist this script will use the index file. From there it gathers the album title and "global" album artist.
This way, you can specify a different artist for every track, but still have a global artist (like "Various") in the album entry in the database. Of course, the script gets the individual tags for each song from the files.
Note1: If you just want to delete old entries without adding new files to the database, just call music.dbIndex --check
Note2: If the database or the required tables do not exist, they will be created.
With the help of my Music Browser you can get a nice web interface for your music collection. From there you can stream your music to everyone in your LAN.
1#!/bin/sh 2# the next line restarts using tclsh \ 3exec tclsh "$0" "$@" 4 5lappend auto_path /usr/local/lib 6package require mysqltcl 7 8 9# 10# required packages: 11# mysqltcl 12# required programs: 13# id3v2 14# ogginfo 15# mp3info 16# ogg.displayTags (script) 17# 18 19 20# mysql settings 21set config(host) "localhost" 22set config(user) "tcl" 23set config(pass) "tcl" 24set config(base) "music" 25 26# 27# RETURNS PLAYBACK LENGTH OF AN OGG FILE 28# 29proc ogg_length {file} { 30 set text [exec ogginfo $file] 31 32 set secs 0 33 34 foreach line [split $text "\n"] { 35 set line [string trim $line] 36 37 if {[string range $line 0 15] == "Playback length:"} then { 38 set line [string trim [join [lrange [split $line ":"] 1 end] ":"]] 39 40 # line has now the form "4m:17s", now convert to seconds 41 foreach i [split $line ":"] { 42 set val [string range $i 0 end-1] 43 set mod [string index $i end] 44 45 if {[string index $val 0] == "0" && [string length $val] > 1} then {set val [string range $val 1 end]} 46 47 if {$mod == "s"} then {set secs [expr $secs + $val]} 48 if {$mod == "m"} then {set secs [expr $secs + ($val * 60)]} 49 if {$mod == "h"} then {set secs [expr $secs + ($val * 60 * 60)]} 50 51 unset val mod i 52 } 53 break 54 } 55 } 56 unset line text 57 return $secs 58} 59 60# 61# RETURNS ALL INFORMATION ABOUT AN MP3 file 62# 63proc mp3_info {file} { 64 set text [exec id3v2 -l $file] 65 66 set artist "" 67 set title "" 68 set album "" 69 set tracknr "" 70 71 foreach line [split $text \n] { 72 if {[string range $line 0 3] == "TPE1"} then {set artist [string trim [join [lrange [split $line ":"] 1 end] ":"]]} 73 if {[string range $line 0 3] == "TIT2"} then {set title [string trim [join [lrange [split $line ":"] 1 end] ":"]]} 74 if {[string range $line 0 3] == "TALB"} then {set album [string trim [join [lrange [split $line ":"] 1 end] ":"]]} 75 if {[string range $line 0 3] == "TRCK"} then {set tracknr [string trim [join [lrange [split $line ":"] 1 end] ":"]]} 76 unset line 77 } 78 79 set length [exec mp3info -p "%S" $file] 80 81 82 set list [list $artist $title $album $tracknr $length] 83 84 unset text artist title album tracknr length 85 86 return $list 87} 88 89# 90# RETURNS ALL INFOS FOR A FOLDER 91# 92proc folder_info {folder} { 93 set indexFile [file join $folder index] 94 95 if {![file exists $indexFile]} then {return "no index document"} 96 97 set fd [open $indexFile r] 98 set text [read $fd] 99 100 set artist "" 101 set album "" 102 set genre "" 103 set year "" 104 105 foreach line [split $text "\n"] { 106 set line [split $line ":"] 107 if {[string tolower [lindex $line 0]] == "artist"} then {set artist [string trim [join [lrange $line 1 end] ":"]]} 108 if {[string tolower [lindex $line 0]] == "title"} then {set album [string trim [join [lrange $line 1 end] ":"]]} 109 if {[string tolower [lindex $line 0]] == "genre"} then {set genre [string trim [join [lrange $line 1 end] ":"]]} 110 if {[string tolower [lindex $line 0]] == "year"} then {set year [string trim [join [lrange $line 1 end] ":"]]} 111 } 112 113 set l [list $artist $album $genre $year] 114 unset artist album genre year indexFile 115 116 return $l 117} 118 119 120# 121# just delete old database entries? 122# 123set do_check no 124 125set i [lsearch $argv "--check"] 126if {$i > -1} then { 127 # we do just the check 128 set do_check yes 129 set argv "" 130} 131unset i 132 133 134if {! $do_check} then { 135 136 # 137 # exit if nothing to do 138 # 139 if {[llength $argv] == 0} then { 140 puts "Usage: [file tail $argv0] \[options\] \[directory..\]\n" 141 142 puts "\t--check\t\tjust remove old entries from the database\n" 143 exit 1 144 } 145} 146 147# 148# open connection to mysql server 149# 150catch {set db [mysqlconnect -host $config(host) -user $config(user) -pass $config(pass)]} sql_result 151 152# if connection was successful, we'll get a variable db 153if {![info exists db]} then { 154 puts $sql_result 155 unset sql_result 156 exit 157} 158 159# 160# connect to database 161# 162catch {mysqluse $db $config(base)} sql_result 163 164if {$sql_result != ""} then { 165 # database is not available 166 puts "Creating database" 167 catch {mysqlexec $db "CREATE DATABASE $config(base)"} sql_result 168 169 if {$sql_result != "1"} then { 170 puts " Error: $sql_result" 171 mysqlclose $db 172 unset sql_result db 173 exit 1 174 } else { 175 # now connect to database 176 mysqluse $db $config(base) 177 } 178} 179unset sql_result 180 181# 182# check whether tables exist 183# 184set listOfTables [mysqlinfo $db tables] 185 186if {[lsearch $listOfTables "albums"] == "-1"} then { 187 puts "Creating albums table" 188 catch {mysqlexec $db " 189 CREATE TABLE `albums` ( 190 `albumID` int(11) NOT NULL auto_increment, 191 `artist` varchar(255) default NULL, 192 `title` varchar(255) default NULL, 193 `genre` varchar(255) default NULL, 194 `year` int(4) default NULL, 195 `directory` varchar(255) default NULL, 196 `playcount` int(11) default '0', 197 `last_played` datetime NOT NULL default '0000-00-00 00:00:00', 198 PRIMARY KEY (`albumID`) 199 ) TYPE=MyISAM;" 200 } sql_result 201 202 if {$sql_result != "0"} then { 203 puts " Error: $sql_result" 204 mysqlclose $db 205 unset listOfTables sql_result db 206 exit 1 207 } 208 209} 210 211if {[lsearch $listOfTables "artists"] == "-1"} then { 212 puts "Creating artists table" 213 catch {mysqlexec $db " 214 CREATE TABLE `artists` ( 215 `artistID` int(11) NOT NULL auto_increment, 216 `name` varchar(255) default NULL, 217 PRIMARY KEY (`artistID`) 218 ) TYPE=MyISAM;" 219 } sql_result 220 221 if {$sql_result != "0"} then { 222 puts " Error: $sql_result" 223 mysqlclose $db 224 unset listOfTables sql_result db 225 exit 1 226 } 227 228} 229 230if {[lsearch $listOfTables "songs"] == "-1"} then { 231 puts "Creating songs table" 232 catch {mysqlexec $db " 233 CREATE TABLE `songs` ( 234 `songID` int(11) NOT NULL auto_increment, 235 `albumID` int(11) default NULL, 236 `artistID` int(11) default NULL, 237 `title` varchar(255) default NULL, 238 `tracknr` int(11) default NULL, 239 `length` int(11) default NULL, 240 `file` varchar(255) default NULL, 241 `playcount` int(11) default '0', 242 PRIMARY KEY (`songID`) 243 ) TYPE=MyISAM;" 244 } sql_result 245 246 if {$sql_result != "0"} then { 247 puts " Error: $sql_result" 248 mysqlclose $db 249 unset listOfTables sql_result db 250 exit 1 251 } 252 253} 254 255unset listOfTables 256 257 258 259# 260# catalogize files now 261# 262foreach folder [lsort -dictionary $argv] { 263 # 264 # the folder is first saved to the database 265 # 266 267 # create absolute path 268 set folder [file normalize $folder] 269 270 set info [folder_info $folder] 271 272 if {$info == "no index document"} then { 273 puts "$folder/ skipped (no index document)" 274 unset folder info 275 continue 276 } 277 278 set artist [mysqlescape $db [lindex $info 0]] 279 set album [mysqlescape $db [lindex $info 1]] 280 set genre [mysqlescape $db [lindex $info 2]] 281 set year [mysqlescape $db [lindex $info 3]] 282 283 # insert into database 284 if {[mysqlsel $db "select * from albums where directory = '$folder'"] == 0} then { 285 set error [catch {mysqlexec $db "insert into albums (artist,title,genre,year,directory) VALUES ('$artist','$album','$genre','$year','$folder')"}] 286 if {$error} then { 287 puts "$folder/ skipped (SQL error \[ins\])" 288 unset artist album genre year folder error 289 continue 290 } else { 291 puts "$folder/" 292 # db was modified, do check for old db entries afterwards 293 set do_check yes 294 } 295 } else { 296 set error [catch {mysqlexec $db "update albums set artist='$artist',title='$album',genre='$genre',year='$year' where directory = '$folder'"}] 297 if {$error} then { 298 puts "$folder/ skipped (SQL error \[upd\])" 299 unset artist album genre year folder error 300 continue 301 } else { 302 puts "$folder/" 303 # db was modified, do check for old db entries afterwards 304 set do_check yes 305 } 306 } 307 308 unset artist album 309 310 # find albumID for this folder 311 set albumID [mysqlsel $db "select albumID from albums where directory = '$folder'" -list] 312 313 # 314 # now all individual songs 315 # 316 foreach file [lsort [glob -nocomplain -types f [file join $folder *.{ogg,mp3}]]] { 317 318 if {[file extension $file] == ".ogg"} then { 319 set artist [mysqlescape $db [exec ogg.displayTags --artist --nohead $file]] 320 set title [mysqlescape $db [exec ogg.displayTags --title --nohead $file]] 321 set album [mysqlescape $db [exec ogg.displayTags --album --nohead $file]] 322 set tracknr [mysqlescape $db [exec ogg.displayTags --tracknr --nohead $file]] 323 set length [mysqlescape $db [ogg_length $file]] 324 } else { 325 set info [mp3_info $file] 326 set artist [mysqlescape $db [lindex $info 0]] 327 set title [mysqlescape $db [lindex $info 1]] 328 set album [mysqlescape $db [lindex $info 2]] 329 set tracknr [mysqlescape $db [lindex $info 3]] 330 set length [mysqlescape $db [lindex $info 4]] 331 332 unset info 333 } 334 335 if {$artist == "" || $title == "" || $album == ""} then { 336 puts "$file skipped (missing information)" 337 unset file artist title album tracknr length 338 continue 339 } 340 341 # 342 # retrieve artistID 343 # 344 set artistID [mysqlsel $db "select artistID from artists where name = '$artist'" -list] 345 346 if {$artistID == ""} then { 347 # artist doesn't exist, add it to database 348 set error [catch {mysqlexec $db "insert into artists (name) VALUES ('$artist')"}] 349 if {$error} then { 350 puts "$folder/ skipped (SQL error \[ins artist\])" 351 unset file artist title album artistID tracknr length error 352 continue 353 } else { 354 set artistID [mysqlsel $db "select artistID from artists where name = '$artist'" -list] 355 } 356 } 357 358 359 # 360 # write into database (replace will overwrite existing files) 361 # 362 if {[mysqlsel $db "select * from songs where file = '$file'"] == 0} then { 363 # song does not exist in database, add it 364 set error [catch {mysqlexec $db "insert into songs (albumID,artistID,title,tracknr,length,file) VALUES ('$albumID','$artistID','$title','$tracknr','$length','$file')"}] 365 if {$error} then { 366 puts " [file tail $file] skipped (SQL error \[ins\])" 367 unset file artist title album artistID tracknr length error 368 continue 369 } else { 370 puts " [file tail $file] added" 371 372 # db was modified, do check for old db entries afterwards 373 set do_check yes 374 } 375 unset error 376 } else { 377 # song does already exist, get old information and update only if they've changed 378 set old_infos [mysqlsel $db "select albumID,artistID,title,tracknr,length from songs where file = '$file'" -flatlist] 379 set old_albumID [lindex $old_infos 0] 380 set old_artistID [lindex $old_infos 1] 381 set old_title [lindex $old_infos 2] 382 set old_tracknr [lindex $old_infos 3] 383 if {$old_tracknr < 10} then { 384 set old_tracknr "0$old_tracknr" 385 } 386 set old_length [lindex $old_infos 4] 387 388 if {![string match $albumID $old_albumID] || \ 389 ![string match $artistID $old_artistID] || \ 390 ![string match $title $old_title] || \ 391 ![string match $tracknr $old_tracknr] || \ 392 ![string match $length $old_length]} then { 393 394 set error [catch {mysqlexec $db "update songs set albumID='$albumID',artistID='$artistID',title='$title',tracknr='$tracknr',length='$length' where file = '$file'"}] 395 if {$error} then { 396 puts " [file tail $file] skipped (SQL error \[upd\])" 397 unset file artist title album artistID tracknr length error 398 continue 399 } else { 400 puts " [file tail $file] updated" 401 # db was modified, do check for old db entries afterwards 402 set do_check yes 403 } 404 unset error 405 } 406 unset old_infos old_albumID old_artistID old_title old_tracknr old_length 407 } 408 409 unset file artist title album tracknr length 410 } 411 unset folder albumID year genre 412} 413 414# 415# delete old entries from the database 416# only happens, if either the --check parameter was given, a file/folder was added/updated to the database 417# 418 419if {$do_check} then { 420 puts "Checking and deleting old database entries" 421 422 # check songs 423 foreach file [lsort [mysqlsel $db "select file from songs" -list]] { 424 425 if {![file exists $file]} then { 426 mysqlexec $db "delete from songs where file = '$file'" 427 puts "$file deleted" 428 } 429 430 unset file 431 } 432 433 # check albums 434 foreach album [lsort [mysqlsel $db "select albumID,directory from albums" -list]] { 435 436# if {![file exists $folder]} then { 437# mysqlexec $db "delete from albums where directory = '$folder'" 438# puts "$folder/ deleted" 439# } 440 441 set albumID [lindex $album 0] 442 set albumDir [lindex $album 1] 443 444 # does a song exist, that has this albumID? 445 if {[mysqlsel $db "select count(*) from songs where albumID = '$albumID'" -list] == 0} then { 446 # no, delete album 447 mysqlexec $db "delete from albums where albumID = '$albumID'" 448 puts "$albumDir removed" 449 } 450 451 unset album albumID albumDir 452 } 453 454 # check artists 455 foreach artist [lsort [mysqlsel $db "select artistID,name from artists" -list]] { 456 set artistID [lindex $artist 0] 457 set artistName [lindex $artist 1] 458 459 # does a song exist, that has this artistID? 460 if {[mysqlsel $db "select count(*) from songs where artistID = '$artistID'" -list] == 0} then { 461 # no, delete artist 462 mysqlexec $db "delete from artists where artistID = '$artistID'" 463 puts "artist \"$artistName\" removed" 464 } 465 466 unset artist artistID artistName 467 } 468} 469 470# close connection to database 471mysqlclose $db 472 473exit 0
