A script to put information stored in file names on disc like this:
10cc And Godley & Creme_The Very Best Of_01_The Wall Street Shuffle.wav 10cc And Godley & Creme_The Very Best Of_02_I'm Not In Love.wav 10cc And Godley & Creme_The Very Best Of_03_Art For Art's Sake.wav 10cc And Godley & Creme_The Very Best Of_04_Dreadlock Holiday.wav 10cc And Godley & Creme_The Very Best Of_05_People I Love.wav 10cc And Godley & Creme_The Very Best Of_06_Donna.wav
where there may be no extra underscores, which can be done by having your cd collection ooked up on some data server (I forgot the name), and editing out the extra _'s. I have about 2000 songs on disc that way, and just used this script to put them in a simple sql database (I used Linux for the server, but windows has one too):
proc dosql { {s} } {
global db
if {[catch {pg_exec $db $s} reply]} {
puts "sql error : $reply,[pg_result $reply -error]"
return "Error"
}
if {[pg_result $reply -status] == "PGRES_COMMAND_OK"} {
# catch {pg_exec $db "insert into history values (DEFAULT, [pg_quote $s] , 'now' , NULL)"}
return {}
}
if {[pg_result $reply -status] != "PGRES_TUPLES_OK"} {
puts "sql error: [pg_result $reply -error]"
return "Error"
}
set res [pg_result $reply -llist]
# catch {pg_exec $db "insert into history values (DEFAULT, [pg_quote $s] , 'now' , NULL)"}
return $res
# we only put non-errors in the history
# pg_result $reply -clear
return
}
package require pgintcl
# Fill this in for your sql server:
set db [pg_connect -conninfo [list host = 192.168.0.1 user = zxy dbname = test password = 56fg6754]]
# This of course **Only Once**
dosql "create table songs (file text, album text, band text, nr int, title text)"
# get the parts of the filenames:
foreach i [lsort -dict ['some length 34 pathname'*.wav]] {
foreach {file band title nr song} [concat
[list [
string range $i 34 end]
] [
split [string range $i 34 end-4] _
]
] {
dosql "insert into songs values (
[pg_quote $file],
[pg_quote $title],
[pg_quote $band],
[pg_quote $nr],
[pg_quote $song]
)"
}
}When this has executed with no error, which in my case took under half a minute, the database is ready to use.
E.g.:
% dosql "select nr from songs limit 5" 1 1 2 3 4
or:
% foreach i [dosql "select * from songs limit 5"] {puts $i}
{4 Non Blondes_Various 100 no. 1 hits [CD2]_01_What's up.wav} {Various 100 no. 1 hits [CD2]} {4 Non Blondes} 1 {What's up}
{10cc And Godley & Creme_The Very Best Of_01_The Wall Street Shuffle.wav} {The Very Best Of} {10cc And Godley & Creme} 1 {The Wall Street Shuffle}
{10cc And Godley & Creme_The Very Best Of_02_I'm Not In Love.wav} {The Very Best Of} {10cc And Godley & Creme} 2 {I'm Not In Love}
{10cc And Godley & Creme_The Very Best Of_03_Art For Art's Sake.wav} {The Very Best Of} {10cc And Godley & Creme} 3 {Art For Art's Sake}
{10cc And Godley & Creme_The Very Best Of_04_Dreadlock Holiday.wav} {The Very Best Of} {10cc And Godley & Creme} 4 {Dreadlock Holiday}Or:
% foreach i [dosql "select distinct album from songs order by album asc limit 5"] {puts $i}
{}
1962-1966
{25 jaar na Waterloo - deel 2}
5
{Albert Hall Experience (1)}Or:
% foreach i [dosql "select nr,title from songs where band like [pg_quote "%10cc%"] group by nr,title order by nr asc limit 5"] {puts $i}
1 {The Wall Street Shuffle}
2 {I'm Not In Love}
3 {Art For Art's Sake}
4 {Dreadlock Holiday}
5 {People I Love}TV (april 30 '09) I did these experiments with some more tcl and sql (postgres, in fact) processing to create webpages automatically from the music data as stored above.
set fz [open //192.168.0.1/Doc_root/Tmp/test.html w]
puts $fz "<html>\n"
foreach i [dosql "select distinct title, nr, album from songs order by title asc"] {puts $fz "<a href=\"[lindex $i 0].html\">[lindex $i 0]</a><br>\n "}
close $fzThat's a long list (about 1600 in this case) of song titles pointing to non-existing pages with the same name. Now lets select something, so we get the same but for a subselection and print some more data(not minding character conversion posibly necessary to be decent in a web page, there should be some function to do that):
set fz [open //192.168.0.1/Doc_root/Tmp/test2.html w]
puts $fz "<html>\n"
foreach i [dosql "select distinct title, nr, album from songs where band like 'Rolling%' order by title,nr,album asc"] {puts $fz "<a href=\"[lindex $i 0].html\">[lindex $i 0]</a>,[lindex $i 1],[lindex $i 2]<br>\n"}
puts $fz "[date]</body><P>\n"
close $fzA list of all the albums, using a second database command from tcl to get band names for the unique album list:
file del //192.168.0.1//Doc_root/Tmp/test3.html
set fz [open //192.168.0.1/theo/Bind/Docroot/Tmp/test3.html w]
puts $fz "<html>\n<head><h2>Albums</h2></head>\n<body><P>\n"
foreach i [dosql "select distinct album from songs order by album asc"] {puts $fz "<a href=\"[lindex $i 0].html\">[lindex $i 0]</a> [dosql "select distinct band from songs where album = [pg_quote [lindex $i 0]] order by band asc"]<br>\n"}
puts $fz "<p>[clock format [clock seconds]]<br><P><a href=\"http://www.theover.org\"><Home></a></body>\n"
close $fzWhen you're smart enought o follow (should be ok for interested people with a littl escripting experience) you can try for yourself or probably find the test file still on my server.
Now I make an alphabetical list:
% dosql "select distinct substring(title,1,1) from songs order by substring(title,1,1) asc" . ' ( 1 2 3 5 9 A B C D E É F G H I J K l L M N O P Q R S T U Ü V w W X Y Z
or determine all first letters appearing in all relevant fields, surely including all letter in the alphabet:
% foreach i [
dosql "((select distinct lower(substring(band,1,1)) as t from songs) union (
select distinct lower(substring(album,1,1)) as t from songs) union (
select distinct lower(substring(title,1,1)) as t from songs) union (
select * from (values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),
('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) as t)
) order by t asc"
] {
puts -nonewline "+[lindex $i 0]"
} ; puts {}
++.+'+(+1+2+3+4+5+9+a+b+c+d+e+..+f+g+h+i+j+k+l+m+n+o+p+q+r+s+t+u+..+v+w+x+y+zNow Use this list to make a table extry for appearing albums, bands or titles starting with such symbols:
set fz [open //192.168.0.1/Doc_root/Tmp/test4.html w]
puts $fz "<table>\n<tr><td style=\"text-align: center;\"> First <br> Letter </td><td> Album </td><td> Artist </td><td>Song</td></tr>" ;
set ll {};
foreach i [
dosql "((select distinct lower(substring(band,1,1)) as t from songs
) union (
select distinct lower(substring(album,1,1)) as t from songs
) union (
select distinct lower(substring(title,1,1)) as t from songs
) union (
select * from (values('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),
('m'),('n'),('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z')) as t)) order by t asc"
] {
lappend ll [lindex $i 0]
}
foreach i $ll {
puts -nonewline $fz "<tr><td style=\"text-align: center;\"> <b>$i</b> </td>
<td style=\"text-align: center;\">" ;
set j [lindex [dosql "select distinct lower(substring(album,1,1)) as t
from songs where lower(substring(album,1,1)) = [pg_quote $i] order by t asc"] 0] ;
if {$j != {}} {
puts -nonewline $fz "<a href=\"album/$i.html\"> * </a>"
} ;
puts -nonewline $fz " </td> <td style=\"text-align: center;\"> ";
set j [lindex [dosql "select distinct lower(substring(band,1,1)) as t
from songs where lower(substring(band,1,1)) = [pg_quote $i] order by t asc"] 0] ;
if {$j != {}} {
puts -nonewline $fz "<a href=\"band/$i.html\"> * </a>"
} ;
puts -nonewline $fz "</td> <td style=\"text-align: center;\">" ;
set j [lindex [dosql "select distinct lower(substring(title,1,1)) as t
from songs where lower(substring(title,1,1)) = [pg_quote $i] order by t asc"] 0] ;
if {$j != {}} {
puts -nonewline $fz "<a href=\"title/$i.html\"> * </a>"
} ;
puts $fz " </td> </tr> "
} ;
puts $fz "</table>"
close $fzWe get:
First Letter Album Artist Song
. *
' *
( *
1 * * *
2 * *
3 * *
4 * *
5 * *
...Where the stars are html links to subpages for letters.
To translate the characters for correct html I tried what's in this tcl file, an array def with unicode chars which translates to html code [L1 ]
proc tohtml in {
global ch ; set o {} ;
foreach i [split $in {}] {
if [info exists ch($i)] {
append o [set ch($i)]
} {
append o $i
}
} ;
return $o
}That works for using around the letter column string, which then in web browser gives the correct accents.
Now a way to make the automatically generated links comply with the URL coding rules (only ascii, with limitations, and even for trivial characters some translations). First, a list of all first characters not being a letter, accent letter or number:
set lln {} ;
foreach i [dosql "(select distinct lower(substring(band,1,1)) as t from songs where
not (
(lower(substring(band,1,1))>='a' and lower(substring(band,1,1))<='z') or
(lower(substring(band,1,1))>='0' and lower(substring(band,1,1))<='9'))
union select distinct lower(substring(album,1,1)) as t from songs where not (
(lower(substring(album,1,1))>='a' and lower(substring(album,1,1))<='z') or
(lower(substring(album,1,1))>='0' and lower(substring(album,1,1))<='9'))
union select distinct lower(substring(title,1,1)) as t from songs where not (
(lower(substring(title,1,1))>='a' and lower(substring(title,1,1))<='z') or
(lower(substring(title,1,1))>='0' and lower(substring(title,1,1))<='9'))
) order by t asc"
] {
lappend lln [lindex $i 0]
}In my case:
% set lln
{} . ' (Now:
foreach i $lln {set urltrans($i) nl}
proc tourl { in } {
global urltrans
# currently 1 letter only input proc
# lookup list
if [info exists urltrans($in)] {
return [set urltrans($in)]
}
set m [dosql "((select * from (values('a'),('b'),('c'),('d'),
('e'),('f'),('g'),('h'),('i'),('j'),('k'),('l'),('m'),('n'),
('o'),('p'),('q'),('r'),('s'),('t'),('u'),('v'),('w'),('x'),
('y'),('z'),('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7'),
('8'),('9')) as t) union (select * from (values(lower([pg_quote $in]))) as t))"]
if {[llength $m] == 36} {
# done, it´s a letter or number
return $in
}
#compute mapped value, hope lower() and string tolower coincide on the unicode and such
if {[lsearch $m [string tolower $in]] == 0} {
return 'a'
}
return [lindex $m [expr [lsearch $m [string tolower $in]]-1]]
} I'm sure there should be like dozens of inplementations of this around, but I didn't quickly find any. Maybe people want to keep the just-not-totally-trivial magic to themselves, but I personally don't think that's the Open Source thought, in web related tcl.
Anyhow, then I'll generate the 3x30 or so page titles and content automatically, too for a nice web server hostable index list.
Let's start with the special character beginning names which are separated from the letters and numbers beginning names (including accents):
% foreach i [dosql "select distinct album as t from songs where not ((lower(substring(album,1,1))>='a' and lower(substring(album,1,1))<='z') or (lower(substring(album,1,1))>='0' and lower(substring(album,1,1))<='9')) order by album asc"] { puts -nonewline "<b>[tohtml [lindex $i 0]]</b> , " ; foreach j [dosql "select distinct band from songs where album = [pg_quote [lindex $i 0]] order by band"] {puts "[tohtml [lindex $j 0]] <br>"} }
<b></b> , Abba <br>Appearently, this was a empty name album in the database, now the pages for the first letters from artist and song name:
foreach i [dosql "select distinct band as t from songs where not ((lower(substring(band,1,1))>='a' and lower(substring(band,1,1))<='z') or (lower(substring(band,1,1))>='0' and lower(substring(band,1,1))<='9')) order by band asc"] { puts -nonewline "<b>[tohtml [lindex $i 0]]</b> , " ; foreach j [dosql "select album from songs where band = [pg_quote [lindex $i 0]] order by album"] {puts "[tohtml [lindex $j 0]] <br>"} }
foreach i [
dosql "select distinct title as t from songs where
not (
(
lower(substring(title,1,1))>='a' and lower(substring(title,1,1))<='z'
) or (
lower(substring(title,1,1))>='0' and lower(substring(title,1,1))<='9'
)
) order by title asc"
] {
puts -nonewline "<b>[tohtml [lindex $i 0]]</b> , " ;
foreach j [
dosql "select album, band from songs where title = [pg_quote [lindex $i 0]] order by album, band"
] {
puts "[tohtml [lindex $j 0]] , <i>[tohtml [lindex $j 1]]</i> <br>"
}
}gives empty and:
<b>'39</b> , A Night At The Opera , <i>Queen</i> <br> <b>... And The Gods Made Love</b> , Electric Ladyland , <i>Jimi Hendrix</i> <br> <b>(Gimme That) Old Time Religion</b> , Live At The Opry , <i>Jim Reevers</i> <br> <b>(I Can't Get No) Satisfaction</b> , Singles Collection - The London Years (CD 1) , <i>The Rolling Stones</i> <br> <b>(Let Me Be Your) Teddy Bear</b> , ELV1S 30 #1 Hits , <i>Elvis Presley</i> <br> ... etc
which can be put on the nl.html pages (or on seperate ones, after making special names for all the relevant chars found above) for the 3 fields.
Ifirst have to match the accent letters with the main letters, but to try it out: the letter and numer albums to html files, from the database, automatically generated from tcl:
foreach i {1 2 3 4 5 6 7 8 9 a b c d e f g h i j k l m n o p q r s t u v w x y z} {
set fz [open //192.168.0.1/Doc_root/Tmp/album/[tourl $i].html w]
puts -nonewline $fz "<html><head><h2>albums [tohtml $i]</h2></head><body>" ;
#puts $i
set j [dosql "select distinct album as t
from songs where lower(substring(album,1,1)) = [pg_quote $i] order by t asc"];
if {$j != {}} {
foreach m $j {
puts -nonewline $fz "<br><b>[tohtml [lindex $m 0]]</b> " ;
foreach k [dosql "select distinct band from songs where album = [pg_quote [lindex $m 0]] order by band"] {
puts $fz ", [tohtml [lindex $k 0]] "
}
}
} ;
puts $fz "\n</body></html>"
close $fz
}The main part of the first column of my test only web rendered database now works partially [L2 ], more later..
'Jul 10 '10' I worked with this idea a bit now I put like 3500 songs in the database, corrected filenames (all from legal CDs), and made a new webtest, see my history files if you're interested: [L3 ] [L4 ] . New test pages from those activities: [L5 ]