Version 9 of Saving song data from file names to sql

Updated 2009-05-02 01:41:13 by theover

TV

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 $fz

That'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 $fz

A 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 $fz

When 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+z

Now Use this list to make a table extry for appearing albums, bands or titles starting with such symbols:

 set fz [open //192.168.1.33/theo/Bind/Docroot/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 $fz

We 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).

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.