Import & Delete

[PHAZE FM]

Hi,

Just curious if anyone knows of a way in the form of a script that can be executed on a weekly basis that removes ALL files from a category in the DB and reimports new files from a local directory and adds them back into the db.

We have a CSR that changes their adverts weekly and we need to keep up to date

I had a look at the events section but the closet thing would be creating a .BAT file to remove the physical file, getting the program to rescan for missing files and then importing the new files some how

[rogiermaas]

I would say a PHP-script that lists and deletes records from a particular Cat/Subcat. Shouldn't be too hard.
Reimporting should be more difficult because it involves actually reading the files and getting the play-length of them, except when you add them as a Variable Duration File. In that case, that shouldn't be too hard as well. Ask your nearest 9-year old to write you a script 

So here is an example you could use in a PHP-file you could have in a cronjob on your server, running every week at times nobody will notice if it fails:

Code: [Select]

// Create connection
$db = mysqli_connect( "hostname", "db_user", "password", "db_name" );

// Check connection
if ( mysqli_connect_errno() ) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// DELETE all enabled tracks from a particular subcat of a certain type (just to be sure):
$result = mysqli_query($db,"DELETE FROM songs WHERE `song_type`= XXX AND `enabled`=1 AND id_subcat = YYY ");

// XXX=track type, 0 is music, but they might be some other type like 'commercial' or VDF.
// YYY=Subcategory the tracks are in.

This is just out of the top of my head, please test this on a subcat you have put some unimportant files in. If you use this, it will delete ALL files in the subcat you provide. So make sure you only put the tracks in one subcat you'd like to clear completely. This query is 'back-rollable', so if you make a mistake and your entire database is empty and your server supports roll-backs, you should be fine.

For importing new files, Marius should be able to help you. I could help you, but that would take some time for me to figure out the best and fail-safe way to do it.

- Rogier

[PHAZE FM]

Hi,

Would RadioDJ be able to understand a PHP file?

[rogiermaas]

Not unless you let this run on a webserver ;-)

You could try Heidi or any other SQL-viewer to perform SQL-tasks. Just run the SQL-statement.

- Rogier

[PHAZE FM]

The least I can run on our studiopcs the better. I'll have to work out a work around for my issues.

[AndyDeGroo]

Well, PHP can act as a web server itself since version 5.4, but you don't need to use a full blown web server to run a PHP script. In fact, nothing has to run continuously on your PC to execute PHP scripts. You can run a script as scheduled task once a day or even every hour. All you have to do is download PHP, unzip it to C:\php\, rename php.ini-production to php.ini and you're ready to execute php scripts. Now you can run php scripts by calling C:\php\php-win.exe my_automation_script.php in command prompt or as any other program in a scheduled task.

It is easy to remove songs from RadioDJ's database. Adding new songs is more complicate, because during import RadioDJ analyses audio files to determine optimal start and end cue points by cutting silence. Unfortunately, I have not come across similar solution in PHP which would not involve running a separate command line application.
Another issue is that the import script would have to parse ID3 tags, which can be done in PHP and I could write a decent script which can do it.

In short, it is doable if you are ok with occasional silence at the beginning and end of imported tracks.

Update: I realised how to make RadioDJ import tracks and perform it's magic; The "Load M3U Playlist" action does import all file into database, but there is a drawback - RadioDJ doesn't assign category, subcategory, track type or genre. However, it can be accomplished by smartly combining two scheduled PHP scripts and one RadioDJ event. First PHP script would generate an M3U file by scanning a directory. Minutes later, RadioDJ would load the M3U file and immediately clear playlist. Then second PHP would run and assign category, subcategory and track type to list of imported files. Updating genres using PHP is more complicated task, but it can be done.