| View previous topic :: View next topic |
| Author |
Message |
ricktee76
Voice
Joined: 12 Jul 2016
Posts: 11
|
 Posted: Thu Jul 21, 2016 5:11 pm Post subject: Shoutcast 2.47 tcl and sqlite3 |
 |
|
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 ################# |
|
|
| Back to top |
|
 |
caesar
Mint Rubber
Joined: 14 Oct 2001
Posts: 3741
Location: Mint Factory
|
| Posted: Fri Jul 22, 2016 8:08 am Post subject: |
|
|
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.
_________________
Once the game is over, the king and the pawn go back in the same box. |
|
| Back to top |
|
|
ricktee76
Voice
Joined: 12 Jul 2016
Posts: 11
|
| Posted: Sat Jul 23, 2016 3:08 pm Post subject: |
|
|
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. |
|
| Back to top |
|
|
caesar
Mint Rubber
Joined: 14 Oct 2001
Posts: 3741
Location: Mint Factory
|
| Posted: Sun Jul 24, 2016 3:53 am Post subject: |
|
|
Your welcome, glad I could help. 
_________________
Once the game is over, the king and the pawn go back in the same box. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
