Duplicate Title Magic Node that excludes Different album

mdave13 · Post by **mdave13** » Wed Sep 28, 2005 3:02 pm

Have Been unsucessful in coming up with a node that will only contain songs with a duplicate title AND album, not just a duplicate title. Any Help would be appreciated.

Bex · Post by **Bex** » Wed Sep 28, 2005 4:11 pm

Songs with same title and album (albumID)

Code: Select all

DupsTitleAlbumID|SQL filter: IDAlbum&SongTitle IN (SELECT IDAlbum&SongTitle FROM Songs GROUP BY IDAlbum&SongTitle HAVING Count(Songs.ID)>1)\<Artist>

Songs with same title and AlbumName

Code: Select all

DupsTitleAlbumName|SQL filter: Album&SongTitle IN (SELECT Album&SongTitle FROM Songs, Albums WHERE songs.idalbum=albums.id GROUP BY Album&SongTitle HAVING Count(Songs.ID)>1)\<Artist>

Havent tested them and they will be slow on big collections. Let me know if they work!

They are now tested but beware, they are slow, especially the second one.

Edit: Corrected code.

karltinsly · Post by **karltinsly** » Thu Sep 29, 2005 9:47 am

I tried the second query (the album name one), but I got a SQL error and my MM locked up. When I restarted, the node was still there. When I clicked on the node, MM locked up again, so I restarted and deleted the node. I'm trying the album ID script now -- it's running long, as expected, but didn't throw an error.

Karl

karltinsly · Post by **karltinsly** » Thu Sep 29, 2005 10:31 am

I was unable to complete running the albumID node query. It popped up a message every few minutes to ask if I wanted to continue or end the query. I clicked continue several times over 20 minutes or so, but was in the middle of typing an email when the message popped up suddenly, accepted an 'e' I was typing as an indication to end, and quit. Besides that bit of frustration, the query completely ties up MM and slows everything else on my computer as well.

Is there anyway to add an index to the database to perhaps speed the query up?

Karl

Bex · Post by **Bex** » Thu Sep 29, 2005 4:59 pm

karltinsly wrote:I was unable to complete running the albumID node query. It popped up a message every few minutes to ask if I wanted to continue or end the query. I clicked continue several times over 20 minutes or so, but was in the middle of typing an email when the message popped up suddenly, accepted an 'e' I was typing as an indication to end, and quit. Besides that bit of frustration, the query completely ties up MM and slows everything else on my computer as well.

Is there anyway to add an index to the database to perhaps speed the query up?

Karl

Yeah, I've corrected the last code and tested them now and they are just too slow..
The db is to my understanding indexed. The very same query is fast when executed directly in Access. Perhaps a hint could be used in the SQL. But my knowledge regarding hints is limited, especially in Access.

How many tracks do you have in MM?

karltinsly · Post by **karltinsly** » Thu Sep 29, 2005 5:23 pm

I have 26000+ files. I don't know Access at all, but I'm a systems analyst for an Oracle application. If Access is like Oracle, then there would have to be an index specifically for only the columns involved in the query. I'll see if I can figure out how to create one.

Karl

psyXonova · Post by **psyXonova** » Fri Sep 30, 2005 3:40 am

Access has very litle in common with Oracle but it does support indexing of individuals columns (not clustered though).
Anyway, the Columns that are in use from those scripts are allready indexed inside Access and in fact the queries are quite fast inside access. So the problem is at the Front-End which i don't believe that was designed to perform such long and intensive queries...
Will give it a shoot though and see what happens

mdave13 · Post by **mdave13** » Sat Oct 01, 2005 12:42 am

I'm fairly new to magic nodes, but could a new field called "TITLE AND ALBUM" Similar to "ALBUM AND ARTIST" be created and then scanned for duplicates?

psyXonova · Post by **psyXonova** » Sat Oct 01, 2005 3:47 am

Ok,
i have tried to solve this Using Magic Nodes and i came to the result that allthough it can be done it is very very slow (especially if you try to expand the node). So, i tried to find a way to force Magic Nodes not to show any child nodes under the DuplicateTitleAndAlbumID node but was unsuccesfull.
This is why i decided to write a small script that will create that node without child nodes. This node is located after the "Files to edit" node.

One more thing i realised is that if a try to pass the whole query using scripting then you have to wait a long time before coming with any results. The same query inside access though is rapid fast!!!
So this is what i propose:

Step 1
Open MediaMonkey.mdb and create a new query, switch to SQL view and paste the following code:

Code: Select all

SELECT Count(Songs.ID) AS CountSongID, Songs.SongTitle, Songs.IDAlbum, Count(Songs.IDAlbum) AS CountOfIDAlbum
FROM Songs
GROUP BY Songs.SongTitle, Songs.IDAlbum
HAVING (((Count(Songs.IDAlbum))>1));

Save this query as Dups
Close Access

Step 2
Create a new text file and paste the following code

Code: Select all

'Find Dups Script
'by psyXonova


Sub onStartUp
Dim Tree        ' The main tree
Dim CustomNode	' The Node to be added

Set Tree = SDB.MainTree
Set CustomNode = Tree.CreateNode
CustomNode.Caption = "Duplicate Title and AlbumID"
CustomNode.IconIndex = 40
CustomNode.UseScript = Script.ScriptPath
CustomNode.OnFillTracksFunct = "FillDupTracks"

Tree.AddNode Tree.Node_FilesToEdit, CustomNode, 1    ' Add as the last child

End Sub



Sub FillDupTracks(Node)

Dim Tracks  

Node.HasChildren = False
Set Tracks = SDB.MainTracksWindow
	
	Tracks.AddTracksFromQuery("AND IDAlbum IN (SELECT Dups.IDAlbum from Dups) AND SongTitle IN (SELECT Dups.SongTitle FROM Dups)")
  Tracks.FinishAdding
End Sub

Save this file as dups.vbs inside the Scipts\Auto folder

Restart MM, the new node should be after the "Files to Edit Node"
It displays all the Songs that have both the Title and AlbumID the same.
You may also notice that some songs are displayed that althoug they have the same title dont have the same Album. This only occurs if you have files that have the Album Field empty. So for better results i suggest to eliminate those empty fields.

For better readability i suggest to sort the results first by Album and then by Title

Hope it helps,

Cheers

karltinsly · Post by **karltinsly** » Sat Oct 01, 2005 9:40 am

Thanks, Psyxonova, for all your work on this! One question, when you say

Open MediaMonkey.mdb and create a new query, switch to SQL view and paste the following code:

Do you mean open the mdb in Access, or is there some other application for that?

Thanks again!

psyXonova · Post by **psyXonova** » Sat Oct 01, 2005 9:46 am

I mean in Access.

rovingcowboy · Post by **rovingcowboy** » Sat Oct 01, 2005 11:37 am

i am not sure where to put these codes ? do make them work can you tell me where to put them at in media monkeys folders?

and also

i keep thinking every time i see one person say they got one then running but the other person says they did not get it running, that maybe some one is low on ram? or virt memory. or both?

so do you that have not got it running have loads of ram? and virt memory? as i am low on both and if you have lots of ram and it still don't work then maybe it wont work on mine either?

Bex · Post by **Bex** » Sat Oct 01, 2005 12:23 pm

psyxonova, great work!

I've been thinking about something like this for a while. But my idea was to create Access Queries as substitutes for the SQLfilters used within Magic Nodes so it'll be faster.
I also have some ideas on different ways to find duplicates wich involves some advanced query techniques. I'll give it a go and let you know the result.

mdave13 · Post by **mdave13** » Sat Oct 01, 2005 12:57 pm

I do not possess Access. Is there any other way to add the query?

Bex · Post by **Bex** » Sat Oct 01, 2005 3:20 pm

mdave13 wrote:I do not possess Access. Is there any other way to add the query?

Not that I know of...