Artist separation (repeat rules) - Bug?

[AndyDeGroo]

Could you not use iLike to make it case insensitive?

It is not the update part which is case sensitive. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

I just realised that to find case-insensitive delimiter, the procedure must be changed:

Code Select Expand


DELIMITER $$
CREATE PROCEDURE UpdateFeatured( trackID INT, delimStr VARCHAR(15) )
BEGIN

SET @Artist = ( SELECT `artist` FROM `songs` WHERE ID = trackID );
# Find main artist by splitting artist
SET @mainArtist = SUBSTRING( @Artist, 1, POSITION( delimStr IN @Artist ));
# Check if mainArtist was found
IF (@mainArtist != @Artist) THEN
# Find featured artist
SET @featArtist = SUBSTRING( @Artist, POSITION(delimStr IN @Artist )+CHAR_LENGTH(delimStr));
# Update matching entries
UPDATE `songs` SET `artist_played` = NOW()
WHERE `artist` LIKE CONCAT(@mainArtist, '%')
OR `artist` LIKE CONCAT(@featArtist, '%');
END IF;

END$$
DELIMITER ;


Btw, you can search case-insensitive with LIKE if database collation is case-insensitive like "utf8_general_ci".

[Marius]

Ok, managed to split the artist which contains mixed separators.

At this point AndyDeGroo, i could do the inserts in a loop, or even better to avoid multiple queries, i could build the string using a single separator and then send it to your procedure which would handle the tables update.

PS: The cases when there are more than 2 artists are pretty rare, so i think that even updating from the loop would work just fine, because the procedure would be complicated to support unlimited artists.

[attachment deleted by admin]

[Jhonny]

I think that problems will arise with the name

Tom & the band, it's a group name


grtz.

[HMC]

Ok, managed to split the artist which contains mixed separators.

At this point AndyDeGroo, i could do the inserts in a loop, or even better to avoid multiple queries, i could build the string using a single separator and then send it to your procedure which would handle the tables update.

PS: The cases when there are more than 2 artists are pretty rare, so i think that even updating from the loop would work just fine, because the procedure would be complicated to support unlimited artists.

Nice work. One more scenario I've seen and even have my self is features separated by a comma.

Justin Timberlake f. Jay-z, some artist, another artist & last artist

[Marius]

Comma shouldn't be a problem, but as i said before both "and" and "&" will be, because they can be found also in some band names, so it would be wise to exclude them from the list.
For me it's clear that for optimal results, the user will have to do a little edits in some cases.

[HMC]

Comma shouldn't be a problem, but as i said before both "and" and "&" will be, because they can be found also in some band names, so it would be wise to exclude them from the list.
For me it's clear that for optimal results, the user will have to do a little edits in some cases.

If you can get 90% of most cases about like you've gotten it. I think we(I) would be able to live with that. There will always be some exceptions and then we can manually edit those.

[Jhonny]

60% is enough
With use of the track manager it is certainly fast adjusted.

J.

[AndyDeGroo]

Ok, managed to split the artist which contains mixed separators.

At this point AndyDeGroo, i could do the inserts in a loop, or even better to avoid multiple queries, i could build the string using a single separator and then send it to your procedure which would handle the tables update.

PS: The cases when there are more than 2 artists are pretty rare, so i think that even updating from the loop would work just fine, because the procedure would be complicated to support unlimited artists.

I don't see how your split artist name strings could be passed to my procedure. If you read trough it, it takes track ID as first argument, gets artist's name from that song entry and splits the name by single separator specified by second argument. There is no need to for procedure if you have already separated the artists.

`artist_played` timestamps can be updated in one query. All you have to do is concatenate OR `artist` LIKE 'Artists Name%' to the query string.

You should add another to split strings if you are using String.Split instead of Regex.Split: "ft;"
I see it in my database as "John Legend ft;  The Roots & Questlove".

IMO, you should use Regex.Split with '\s+(f|ft|feat|&|and)[\.;\/]?\s+', because it covers cases with different punctuation.

Here is an SQL query to see how many artists you can find with similar regex in your database:

Code Select Expand


SELECT `artist`, `artist_played`
FROM `songs`
WHERE `artist` REGEXP '.+ (f|ft|feat|&|and)[\.;\/]? .+'
GROUP BY `artist`;


[Marius]

Your procedure only accepts one separator, that's why i said that after i separate the artists that have mixed separators i can then send a string like artist1;artist2 etc and then you will simply split to a known character.

However, after a few tests i will not complicate things. I simply compose the query in the loop and i run it once with the procedure call, so i still use the existing connection.
The generated query looks like this:

Code Select Expand


CALL UpdateTracks(165,0,0,30,0);
UPDATE `songs` SET `artist_played`=NOW() WHERE `artist` LIKE '%Macklemore%';
UPDATE `songs` SET `artist_played`=NOW() WHERE `artist` LIKE '%Ryan Lewis%';
UPDATE `songs` SET `artist_played`=NOW() WHERE `artist` LIKE '%Ray Dalton%';


The good part is that the existing procedure can remain as it is and the users will not have to update again the database. You saw what a mess was when some users didn't updated properly.

About regex, string.split() accepts arrays and is far more easy for me to load them from a external file in which the user is able to add/remove split keywords, so i will use it.

[SnowHow]

What a wonderful topic - I've been taking notes.... 

Three more separators that I have seen that I don't recall being mentioned:

vs

vs.

versus


(I never understood these - Are the artists in a battle?  )


J-

[HMC]

What a wonderful topic - I've been taking notes.... 

Three more separators that I have seen that I don't recall being mentioned:

vs

vs.

versus


(I never understood these - Are the artists in a battle?  )


J-

Honestly that seems something editable in the song details. I see Vs as mashups or collaborations and mostly in EDM music IMO. I look at those as one artist and most of the time some EDM music doesn't have vocals so who probably couldn't tell who was playing unless you are really familiar with the music.

[HMC]

BTW, I've been using a PHP script to time stamp the artists that Andy suggested to help with the separation. It's not working completely because there is a larger overall problem with the Autodj function.
During this discussion we've noticed that RDJ sees these as different artists.

Pitbull f. Danny Mercer - Outta Nowhere
Pitbull f. G.R.L - Wild Wild Love

Well after "Pitbull f. Danny Mercer - Outta Nowhere" plays, the PHP script goes and time stamps all the Pitbull and featuring Pitbull songs. However since autodj already created the playlist there are 3 other songs already in the playlist with Pitbull in it. Autodj loads many tracks at once so I don't how the separation is going to completely work with Autodj selecting that many tracks at once. Since it's already queued, those songs still play.  Sorry to find more problems. 

[Marius]

There are two different artist/track checks that are made by AutoDJ:
1. It selects only files which have the track and artist played date and time older or equal to the user set repeat duration;
2. It checks if the track or artist is not already in the queue.

All things that has been discussed on this topic affects the first check, because only when a track gets played the track and artist played date and time are updated.
To the second check the problem is bigger, because it compares directly the artist fields from the database (ignore all the tracks from the songs table where the artist is found in the queue).

If i would update the track and artist played in the moment when i add a file to the playlist, if you would delete the file from the playlist, the file would still be marked as played (so it would need to wait until it would pass by the first check), beside that all tracks from a playlist would have the same played time which would be very bad for reports.

[Marius]

Ok, i think that i found a workaround also to the second check, but i will need some testing.
The modified RadioDJ.exe can be downloaded from here: http://radiodj.ro/tests/RadioDJTest.zip
The archive also has a .txt file which contains the artist separators. Put both files in RadioDJ folder.

[HMC]

Ok, i think that i found a workaround also to the second check, but i will need some testing.
The modified RadioDJ.exe can be downloaded from here: http://radiodj.ro/tests/RadioDJTest.zip
The archive also has a .txt file which contains the artist separators. Put both files in RadioDJ folder.

Cool, I'll test it out. I didn't realize the program was so small. 1.5MB?  . Anyway, will I have to reimport music or will it just know based on the .txt file with the separators?