Shoutcast 2.47 tcl and sqlite3

[ricktee76]

I have modified Domsen's Shoutcast script to SC2.47.
I would like it to add each song to the database as it is played.
This seems to do nothing, nor does it report errors.
All help appreciated.

Code: Select all

######## CHECK IF SONG EXISTS ##############         
         putlog "\00309Checking if song is already in Database...\003"
         
    set kdbfile "database.db"  
    sqlite3 kdb $kdbfile      
          
          if { [kdb eval {SELECT * FROM Songs WHERE Song=$cursong}] == 1} {
      putlog "\00304Record already exists\003"
    }  
     if { [kdb eval {SELECT * FROM Songs WHERE Song=$songtitle}] == 0} {
          kdb [eval { INSERT INTO Songs (SongID, Song) VALUES (ABS(RANDOM()) % (999999999 - 1) + 1, $cursong) }]
          kdb [eval { INSERT INTO Ratings (SongID, Song, Rating, Votes) VALUES (ABS(RANDOM()) % (999999999 - 1) + 1, $cursong, 0,0) }]
          putlog "\00309Song Succesfully Added.\003"
         }
         
  ########### END OF CHECK ################# 

[caesar]

Replace:

Code: Select all

if { [kdb eval {SELECT * FROM Songs WHERE Song=$cursong}] == 1} { 

with:

Code: Select all

if {[kdb eval {SELECT 1 FROM Songs WHERE Song=$cursong}]} {

if you want to see if whatever you have in $cursong exists in the database, as it will return 1 for true and nothing for false.

Keep in mind that the Song = $cursong in the above query as is right now is in case sensitive mode, meaning Something is not equal with something or SOMETHING, or whatever variation you want to add. To make it ignore the case then add COLLATE NOCASE at the end:

Code: Select all

if {[kdb eval {SELECT 1 FROM Songs WHERE Song=$cursong COLLATE NOCASE}]} {

Also, there's no need to do run the same query twice when you have the TRUE case with the above and can use an else statement like for the FALSE one:

Code: Select all

if {[kdb eval {SELECT 1 FROM Songs WHERE Song=$cursong}]} {
      putlog "\00304Record already exists\003"
} else {
	# do the inserts or whatever you wish
}

If SongID is the primary key and for some reason want to know it then replace the 1 with the SongID in the above statement. Always select what you will be using and don't be lazy and use * if you don't plan to use all that info.

Edit: I see that you use ABS(RANDOM()) % (999999999 - 1) + 1 to create some random number representing the SongID. If you want a unique number that would also be auto-incremented, then I would be recreating the Songs table and dumping the Ratings one like this:

Code: Select all

CREATE TABLE Songs(SongID INTEGER PRIMARY KEY AUTOINCREMENT, Song TEXT, Rating REAL, Votes INTEGER, UNIQUE(Song))

and when would insert something in the Songs table would just:

Code: Select all

INSERT INTO Songs (Song, Rating, Votes) VALUES ($cursong, 0, 0)

and the SongID is automatically added.

Want to add votes to a certain song?

Code: Select all

UPDATE Songs SET Votes = Votes + 1 WHERE SongID = $id

Want to calculate it's rating?

Code: Select all

UPDATE Songs SET Rating = $rating WHERE SongID = $id

for example.

[ricktee76]

thanks caesar, I've had to spend some time doing this as i'm new to tcl but i've finally got it done, your reply really helped point me in the right direction.
Much appreciated.

[caesar]

Your welcome, glad I could help.