Geekido

In MacIDOL’s Jamroom package, I use artist quotas to weed out spammers and drive-by subscribers to artist accounts. A new artist is “provisional” until they upload at least one song. A provisional artist gets deleted after 30 days of inactivity.

Up until now, it’s been a manual process to go in, look at the provisional artists, and convert those with at least a song to regular artists. I changed that today.

The SQL code to do this is actually pretty simple if songs hidden by the artist don’t qualify them for promotion. The quota ID for the provisional artist is 6, that of a regular artist is 1. So the following SQL finds all artists in quota 6 with at least one song, and changes their quota to 1:

UPDATE jamroom_band_info SET band_quota=1 WHERE band_quota=6 AND band_song_count>0;

Now its a simple matter to put that in a file and have a cron job run it every so often.

WARNING: The script needs to be somewhere the http daemon can’t get to it, and needs appropriate permissions. You don’t want people being able to edit this script or replace the contents. That would let them execute arbitrary SQL commands, then it’s game over.

If you want any song at all to count for promoting the artist, whether they have hidden it or not, it’s more complicated. As far as the jamroom_band_info table is concerned, if the artist has no visible songs, he has no songs. The songs do exist, keyed by band_id, in the jamroom_song_info table, though. So now the update needs two inner joins — one with a subquery to pull the bands in quota 6, and one with the jamroom_song_info table. Here’s the code:

UPDATE jamroom_band_info AS jbi
 INNER JOIN (SELECT band_id FROM jamroom_band_info WHERE band_quota=6) AS ljbi ON jbi.band_id=ljbi.band_id
 INNER JOIN jamroom_song_info AS jsi ON ljbi.band_id=jsi.band_id
SET band_quota=1;