RadioDJ - Free Radio Automation Software Forum

RadioDJ v1.7+ => v1.7+ - Support Forum => Topic started by: Skydragon980 on May 30, 2016, 02:54:45 PM

Title: Help with SQL string
Post by: Skydragon980 on May 30, 2016, 02:54:45 PM
Hello, i'm searching a solution to create a commercial rotation to use in radio dj, looking over the rotation rules i found one named SQL Query, so how can i made a query that, for example, play a random commercial the "Paid Commercial" section that has not been played more than 10 times?

and another question, is there a way to reset that count everyday at midnight?
Title: Re: Help with SQL string
Post by: Filip83 on May 31, 2016, 07:01:17 PM
This is very interesting!

The 1st part should be relatively easy for some forum members. I hope they will see this post. You can check this thread for examples:http://www.radiodj.ro/community/index.php?topic=6658.msg50604#msg50604 (http://www.radiodj.ro/community/index.php?topic=6658.msg50604#msg50604)
Let's see if a pro can pick this up. Otherwise I might be able to scrape together a string for you.

As for resetting the play count that could be done with Events.

Add an event that will run once every day that will use plugin action > PluginAction|Utilities|ResetPlaycounts But you probably need to add some arguments.
Again I can't help to reduce the reset to a specific category,... I'm sure it can be done.

Also I have been told there's 3rd party apps that are made to run commercials somehow but then again I have no need for commercials and hence I don't really know much on the subject.

Let's see if we can get some more feedback on the subject and then decide what to do. If we decide to use the SQL thingy We will need to know the "ID" of the "category" OR "track type" and any other criteria you need to use to select from the correct pool.
Title: Re: Help with SQL string
Post by: Filip83 on May 31, 2016, 07:07:36 PM
I'm assuming every block of commercials will have an "intro" and "outro" jingle. So what happens if there is no more Commercials to be played that day? We have to be sure that at least 1 commercial will be pulled from the database. Maybe make the last entry "least times played" or "least recently played".

This is just something you have to consider and think about. The what if scenario. :)
Title: Re: Help with SQL string
Post by: Skydragon980 on June 07, 2016, 11:53:38 AM
I'm assuming every block of commercials will have an "intro" and "outro" jingle. So what happens if there is no more Commercials to be played that day? We have to be sure that at least 1 commercial will be pulled from the database. Maybe make the last entry "least times played" or "least recently played".

This is just something you have to consider and think about. The what if scenario. :)

you can put a number of playing even to the intro and outro for example if you have enough commercial to make 10 spot cluster so the intro and outro will have 10 play count when they'll reach 0 the server will skip the commercial cluster.

anyway if you can help me with the "SQL Thing" i will appreciate it :)
Title: Re: Help with SQL string
Post by: Filip83 on June 09, 2016, 06:20:06 PM
That would not work as jingles don't usually follow repeat rules. I don't know how to do that. If there is no commercials to play the intro and outro would still play.

Insert intro jingle here

Code: [Select]
#INSERT TITLE HERE
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs`
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
WHERE `song_type`=4
AND `enabled`=1
AND `count_played`<10
AND  `queuelist`.`artist` IS NULL
ORDER BY `date_played` ASC
LIMIT 5;

insert outro jingle here


try it. no guarantees as I am pretty much a noob!

Note that the commercials will play the least recently played as I think that works better than random.
Make sure your commercials are in fact song type 4. This is probably true but they could use a different id.
There should be no duplicate "artist" in the queue list. This means that the same spot should not repeat more than 1x in a block.
There will be a maximum of 5 spots added to the queue.

please report.


Additionally I think there might be an option to write a complex single entry that would cover intro and outro jingles and add conditions to only load if there is actual commercials to play. But that's way out my reaches.

Title: Re: Help with SQL string
Post by: Capt_Fuzzy on June 10, 2016, 04:09:37 PM
That would not work as jingles don't usually follow repeat rules. I don't know how to do that. If there is no commercials to play the intro and outro would still play.

Insert intro jingle here

Code: [Select]
#INSERT TITLE HERE
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs`
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
WHERE `song_type`=4
AND `enabled`=1
AND `count_played`<10
AND  `queuelist`.`artist` IS NULL
ORDER BY `date_played` ASC
LIMIT 5;

insert outro jingle here

The way events load, that would not quite be right...
When loading events to the top of the que, they tend to load in reverse order, so setup your event like this:

Load outro
Load SQL Query
Load intro

When the event runs, the outro will be loaded first, followed by the query, and then the intro, so it will actually look like this in the que:

intro
SQL Query
outro
Title: Re: Help with SQL string
Post by: Filip83 on June 10, 2016, 08:01:36 PM
:)
Title: Re: Help with SQL string
Post by: Skydragon980 on June 13, 2016, 08:38:16 AM
That would not work as jingles don't usually follow repeat rules. I don't know how to do that. If there is no commercials to play the intro and outro would still play.

Insert intro jingle here

Code: [Select]
#INSERT TITLE HERE
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs`
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
WHERE `song_type`=4
AND `enabled`=1
AND `count_played`<10
AND  `queuelist`.`artist` IS NULL
ORDER BY `date_played` ASC
LIMIT 5;

insert outro jingle here


try it. no guarantees as I am pretty much a noob!

Note that the commercials will play the least recently played as I think that works better than random.
Make sure your commercials are in fact song type 4. This is probably true but they could use a different id.
There should be no duplicate "artist" in the queue list. This means that the same spot should not repeat more than 1x in a block.
There will be a maximum of 5 spots added to the queue.

please report.


Additionally I think there might be an option to write a complex single entry that would cover intro and outro jingles and add conditions to only load if there is actual commercials to play. But that's way out my reaches.

i give it a try, stay tuned and thanks for now :)
Title: Re: Help with SQL string
Post by: Skydragon980 on June 27, 2016, 11:46:59 AM
i give it a try, stay tuned and thanks for now :)

this script return an error... what are the type of song for radiodj? i just put my commercial under commercial / paid commercial tag
Title: Re: Help with SQL string
Post by: Jhonny on June 27, 2016, 11:52:34 AM
`song_type`=0
Title: Re: Help with SQL string
Post by: Skydragon980 on June 27, 2016, 12:09:02 PM
`song_type`=0

nothing... doesn't work... ok i try to examine the script maybe is better...

Code: [Select]
#INSERT TITLE HERE   <-- title of the rotation in my case Commercial Cluster
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs` <-- Select songs and id from where? songs is the sql table where radio dj register all my songs i presume
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`) <-- create a playlist  based on the search you did before
WHERE `song_type`=0 <-- where song type is 0 (but what are the type of songs for radiodj?
AND `enabled`=1 <- obviously the song need to be enabled
AND `count_played`<11 <- and it's count must be 10 or less
AND  `queuelist`.`artist` IS NULL <-- the artist must be null 'cause i can't have repeated artist on my playlist
ORDER BY `date_played` ASC <-- order my playlist by least played first (what happen if all commercials are new?)
LIMIT 5; <-- limit 5 entry for playlist so the listeners are not bored


is that right?
Title: Re: Help with SQL string
Post by: Filip83 on June 27, 2016, 12:18:59 PM
Hope you are not using the "<--- and comment" in the actual code.
Note that I wrote: WHERE `song_type`=4

Song type 0 would be a song while 4 is a commercial. This number represents what category are we looking at. These numbers could be different in your case. You need to look at the actual table in SQL to confirm the id of the category you want to use.

I made this a while ago but I think it did work for me.
Title: Re: Help with SQL string
Post by: Jhonny on June 27, 2016, 12:52:44 PM
Found no errors in the qwerty.

(http://dj-jhonny.nl/thisQ.gif)

I have imported the tracks as:

Category = Commercials

Track Type = Commercial
Title: Re: Help with SQL string
Post by: Skydragon980 on June 30, 2016, 10:51:03 AM
Ok the script finally work but now i have another problem i just tried creating an event that do those things in order:

1. Clear Playlist (hoping it clear everything in queue to be played)
2. Load Jingle Start Cluster
3. Load Rotation Commercial Cluster (the one with SQl string)
4. Load Jingle End Cluster
5. Load Normal Rotation
but it load only the 2 jingles and no commercials... where is the error?
Title: Re: Help with SQL string
Post by: Filip83 on June 30, 2016, 11:07:12 AM
Well now that I look at it again I noticed it actually can't be done.

Unless someone knows a way to pull single tracks to the playlist using SQL.
Using rotations for this task is not appropriate. but you can still load a set number of commercials to a block (using "load track from category"). You can even use repeat rules. Unfortunately there is no way to make sure a commercial won't play for more than "set" times. But it's the closest you can get.

This would be a great addition to RDJ tho. Hope Marius sees this and adds this function in the future.
The ability to add tracks from SQL using events. That would be awesome.
Title: Re: Help with SQL string
Post by: Skydragon980 on June 30, 2016, 10:53:05 PM
Well now that I look at it again I noticed it actually can't be done.

Unless someone knows a way to pull single tracks to the playlist using SQL.
Using rotations for this task is not appropriate. but you can still load a set number of commercials to a block (using "load track from category"). You can even use repeat rules. Unfortunately there is no way to make sure a commercial won't play for more than "set" times. But it's the closest you can get.

This would be a great addition to RDJ tho. Hope Marius sees this and adds this function in the future.
The ability to add tracks from SQL using events. That would be awesome.

well, so i'was doing all right and the problem isn't in my commands but in a limit of the program, BTW using rotations i can maybe put a play x times then deactvate the track, but do you know if there's a way or a script to clear the count on files? i write an example to be clear, i'm fucking tired and my english semms pulled out of a freaking nightmare :D

example:
commercial 1 has a count of 10 times before deactivation, how can i reset the playcount only on those files?

thanks for your precious help
Title: Re: Help with SQL string
Post by: Filip83 on July 01, 2016, 02:44:26 AM
You can do that with an event. You can repeat the event as often as you want. It will reset the play count. But I am not sure you can actually limit the reset to a specific category. By default it will reset play count for all entries including songs!

PluginAction|Utilities|ResetPlaycounts

I think it may be possible to limit the reset to a specific category but have no idea how to do it.
Title: Re: Help with SQL string
Post by: Marius on July 01, 2016, 11:05:39 AM
For future reference, here are the internal track types:
Code: [Select]
        Music = 0
        Jingle = 1
        Sweeper = 2
        Voiceover = 3
        Commercial = 4
        InternetStream = 5
        Other = 6
        VDF = 7
        Podcast = 8
        Request = 9
        News = 10
        PlaylistEvent = 11
        FileByDate = 12
        NewestFromFolder = 13
        Teaser = 14

Now regarding the query, you've said that you need to play the commercials from "Paid Commercial" subcategory. Do you have tracks set as commercials in other subcategories too? I ask this because in your query you are simply querying about track type 4 without selecting a subcategory.

Quote
Ok the script finally work but now i have another problem i just tried creating an event that do those things in order:

1. Clear Playlist (hoping it clear everything in queue to be played)
2. Load Jingle Start Cluster
3. Load Rotation Commercial Cluster (the one with SQl string)
4. Load Jingle End Cluster
5. Load Normal Rotation
but it load only the 2 jingles and no commercials... where is the error?

The rotation won't add any tracks in the playlist because you already have something in it (the jingle). You could try something like this:

1. Clear playlist
2. Load sql query rotation
3. Load jingle start ON TOP
4. Load jingle end ON BOTTOM
5. Load normal rotation

The real problem in this approach is that there isn't a way to reset manually just a subcategory. The best fix for this would be to implement an event action to run sql queries, which i will do right now.
What i can do right now to help you is to write a small application which would allow you to run UPDATE sql queries, (like set play count=0) and then you can start it using events (Run application action). Let me know if this can help you.
Title: Re: Help with SQL string
Post by: Marius on July 01, 2016, 05:22:50 PM
I completed the program: SQLRun 1.0 (Schedule and run SQL queries in RadioDJ) (http://www.radiodj.ro/download).

This utility allows you tu run MySQL queries on RadioDJ database using events. In order to do this, you need to place "SQLRun.exe" in the same folder as RadioDJ.exe.
To run it, you must create an event and add the "Run Application" action, select "SQLRun.exe" application and as argument enter the path to the .sql file to run.

Note: the utility can only INSERT/DELETE/UPDATE records on the database, SELECT will not work.

Now to reset the play count for a specific subcategory, you must create a sql file, let's name it "test.sql".

In that file we will add:
Code: [Select]
UPDATE songs SET count_played=0 WHERE id_subcat=X;
X=the ID of the subcategory you need to reset.

In the next version this feature is built in, but for now i can't publish it, because it needs more testing.

[attachment deleted by admin]
Title: Re: Help with SQL string
Post by: Filip83 on July 01, 2016, 06:23:29 PM
Wow this is awesome! I will have a look at this later on. :)
Title: Re: Help with SQL string
Post by: Skydragon980 on July 02, 2016, 04:30:07 PM
I completed the program: SQLRun 1.0 (Schedule and run SQL queries in RadioDJ) (http://www.radiodj.ro/download).

This utility allows you tu run MySQL queries on RadioDJ database using events. In order to do this, you need to place "SQLRun.exe" in the same folder as RadioDJ.exe.
To run it, you must create an event and add the "Run Application" action, select "SQLRun.exe" application and as argument enter the path to the .sql file to run.

Note: the utility can only INSERT/DELETE/UPDATE records on the database, SELECT will not work.

Now to reset the play count for a specific subcategory, you must create a sql file, let's name it "test.sql".

In that file we will add:
Code: [Select]
UPDATE songs SET count_played=0 WHERE id_subcat=X;
X=the ID of the subcategory you need to reset.

In the next version this feature is built in, but for now i can't publish it, because it needs more testing.

thank you marius you are great, and thanks to the other friends too :)
Title: Re: Help with SQL string
Post by: Skydragon980 on July 02, 2016, 04:32:39 PM
For future reference, here are the internal track types:
Code: [Select]
        Music = 0
        Jingle = 1
        Sweeper = 2
        Voiceover = 3
        Commercial = 4
        InternetStream = 5
        Other = 6
        VDF = 7
        Podcast = 8
        Request = 9
        News = 10
        PlaylistEvent = 11
        FileByDate = 12
        NewestFromFolder = 13
        Teaser = 14

Now regarding the query, you've said that you need to play the commercials from "Paid Commercial" subcategory. Do you have tracks set as commercials in other subcategories too? I ask this because in your query you are simply querying about track type 4 without selecting a subcategory.

The rotation won't add any tracks in the playlist because you already have something in it (the jingle). You could try something like this:

1. Clear playlist
2. Load sql query rotation
3. Load jingle start ON TOP
4. Load jingle end ON BOTTOM
5. Load normal rotation

The real problem in this approach is that there isn't a way to reset manually just a subcategory. The best fix for this would be to implement an event action to run sql queries, which i will do right now.
What i can do right now to help you is to write a small application which would allow you to run UPDATE sql queries, (like set play count=0) and then you can start it using events (Run application action). Let me know if this can help you.

i use the commercial category only for paid commercial, free commercials aren't "good looked" for SIAE (the italian organization that grant me the streaming license)
Title: Re: Help with SQL string
Post by: Skydragon980 on August 01, 2016, 01:33:23 PM
EDIT: FINALLY IT WORK! you need to add a pause between the commercial rotation and the top and bottom jingles

Code: [Select]
1. Clear playlist
2. Load sql query rotation
3. Pause for 1 second
4. Load jingle start ON TOP
5. Load jingle end ON BOTTOM
6. Load normal rotation
Title: Re: Help with SQL string
Post by: Filip83 on August 01, 2016, 01:47:17 PM
I am glad you were able to make it all work! Could you do a recap of all the steps you used to made it work for other users?
Title: Re: Help with SQL string
Post by: Skydragon980 on August 03, 2016, 11:42:12 AM
Hello, here are the solution for my problem, thanks to everyone of you that helped me  8)



create a new SQL rotation with this SQL string:
Code: [Select]
#INSERT TITLE HERE
SELECT `songs`.`ID`, `songs`.`artist` FROM `songs`
LEFT JOIN `queuelist` ON ( `songs`.`artist` = `queuelist`.`artist`)
WHERE `song_type`=4
AND `enabled`=1
AND `count_played`<10
AND  `queuelist`.`artist` IS NULL
ORDER BY `date_played` ASC
LIMIT 5;

song type reference:
Code: [Select]
        Music = 0
        Jingle = 1
        Sweeper = 2
        Voiceover = 3
        Commercial = 4
        InternetStream = 5
        Other = 6
        VDF = 7
        Podcast = 8
        Request = 9
        News = 10
        PlaylistEvent = 11
        FileByDate = 12
        NewestFromFolder = 13
        Teaser = 14

then create an event :

Code: [Select]
1. Clear Playlist!
2. Load Rotation "SQL ROTATION"
3. Seconds to wait 1
4. Load Jingle on Top
5. Load Jingle on Bottom
6. Load standard Rotation

it work for me :)