egghelp/eggheads community

Shoutcast 2.47 tcl and sqlite3

2016-07-24T03:53:32-04:00

Your welcome, glad I could help.

Statistics: Posted by caesar — Sun Jul 24, 2016 3:53 am

Shoutcast 2.47 tcl and sqlite3

2016-07-23T15:08:25-04:00

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.

Statistics: Posted by ricktee76 — Sat Jul 23, 2016 3:08 pm

Shoutcast 2.47 tcl and sqlite3

2016-07-22T08:08:30-04:00

Replace:

Code:

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

with:

Code:

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:

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:

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:

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:

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:

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

Want to calculate it's rating?

Code:

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

for example.

Statistics: Posted by caesar — Fri Jul 22, 2016 8:08 am

Shoutcast 2.47 tcl and sqlite3

2016-07-21T17:11:07-04:00

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:

######## 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 #################

Statistics: Posted by ricktee76 — Thu Jul 21, 2016 5:11 pm