1 set db [pg_connect -conninfo [list host = 192.168.1.33 user = dpsearch dbname = test password = nummer]] 2 package require pgtcl 3 package require pgintcl 4 set db [pg_connect -conninfo [list host = 192.168.1.33 user = dpsearch dbname = test password = nummer]] 5 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 } 6 dosql "select distinct band as t from songs where lower(substring(band,1,1)) = [pg_quote a] order by t asc" 7 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/band/[tourl $i].html w] puts -nonewline $fz "

Artists [tohtml $i]

" ; #puts $i set j [dosql "select distinct band as t from songs where lower(substring(band,1,1)) = [pg_quote $i] order by t asc"]; if {$j != {}} { foreach m $j { puts -nonewline $fz "
[tohtml [lindex $m 0]] " ; foreach k [dosql "select distinct album from songs where band = [pg_quote [lindex $m 0]] order by album"] { puts $fz ", [tohtml [lindex $k 0]] " } } } ; puts $fz "\n" close $fz } 8 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]] } 9 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.1.33/theo/Bind/Docroot/Tmp/title/[tourl $i].html w] puts -nonewline $fz "

Songs [tohtml $i]

" ; #puts $i set j [dosql "select distinct title as t from songs where lower(substring(title,1,1)) = [pg_quote $i] order by t asc"]; if {$j != {}} { foreach m $j { puts -nonewline $fz "
[tohtml [lindex $m 0]] " ; foreach k [dosql "select distinct band from songs where title = [pg_quote [lindex $m 0]] order by band"] { puts $fz ", [tohtml [lindex $k 0]] " } } } ; puts $fz "\n" close $fz } 10 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 } 11 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.1.33/theo/Bind/Docroot/Tmp/title/[tourl $i].html w] puts -nonewline $fz "

Songs [tohtml $i]

" ; #puts $i set j [dosql "select distinct title as t from songs where lower(substring(title,1,1)) = [pg_quote $i] order by t asc"]; if {$j != {}} { foreach m $j { puts -nonewline $fz "
[tohtml [lindex $m 0]] " ; foreach k [dosql "select distinct band from songs where title = [pg_quote [lindex $m 0]] order by band"] { puts $fz ", [tohtml [lindex $k 0]] " } } } ; puts $fz "\n" close $fz } 12 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.1.33/theo/Bind/Docroot/Tmp/band/[tourl $i].html w] puts -nonewline $fz "

Artists [tohtml $i]

" ; #puts $i set j [dosql "select distinct band as t from songs where lower(substring(band,1,1)) = [pg_quote $i] order by t asc"]; if {$j != {}} { foreach m $j { puts -nonewline $fz "
[tohtml [lindex $m 0]] " ; foreach k [dosql "select distinct album from songs where band = [pg_quote [lindex $m 0]] order by album"] { puts $fz ", [tohtml [lindex $k 0]] " } } } ; puts $fz "\n" close $fz } 13 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.1.33/theo/Bind/Docroot/Tmp/album/[tourl $i].html w] puts -nonewline $fz "

albums [tohtml $i]

" ; #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 "
[tohtml [lindex $m 0]] " ; 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" close $fz } 14 set fz [open //192.168.1.33/theo/Bind/Docroot/Tmp/test5.html w] 15 puts $fz "\n" ; 16 set ll {}; 17 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] } 18 set ll 19 foreach i $ll { puts -nonewline $fz " " } ; 20 puts $fz "
First
Letter 		Album Artist Song
[tohtml $i] " ; 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 " * " } ; puts -nonewline $fz " "; 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 " * " } ; puts -nonewline $fz " " ; 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 " * " } ; puts $fz "
" 21 close $fz 22 pwd 23 savehis hissqlsongs6c.txt