egghelp.org community Forum Index
[ egghelp.org home | forum home ]
egghelp.org community
Discussion of eggdrop bots, shell accounts and tcl scripts.
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Shoutcast 2.47 tcl and sqlite3

 
Post new topic   Reply to topic    egghelp.org community Forum Index -> Script Requests
View previous topic :: View next topic  
Author Message
ricktee76
Voice


Joined: 12 Jul 2016
Posts: 10

PostPosted: Thu Jul 21, 2016 5:11 pm    Post subject: Shoutcast 2.47 tcl and sqlite3 Reply with quote

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
View user's profile Send private message
caesar
Ass Kicker


Joined: 14 Oct 2001
Posts: 3403
Location: Area 51

PostPosted: Fri Jul 22, 2016 8:08 am    Post subject: Reply with quote

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.
_________________
You may say anything about me, but don't misspell my name.
Back to top
View user's profile Send private message
ricktee76
Voice


Joined: 12 Jul 2016
Posts: 10

PostPosted: Sat Jul 23, 2016 3:08 pm    Post subject: Reply with quote

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
View user's profile Send private message
caesar
Ass Kicker


Joined: 14 Oct 2001
Posts: 3403
Location: Area 51

PostPosted: Sun Jul 24, 2016 3:53 am    Post subject: Reply with quote

Your welcome, glad I could help. Smile
_________________
You may say anything about me, but don't misspell my name.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    egghelp.org community Forum Index -> Script Requests All times are GMT - 4 Hours
Page 1 of 1

 
Jump to:  
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


Forum hosting provided by Reverse.net

Powered by phpBB © 2001, 2005 phpBB Group
subGreen style by ktauber