Page 1 of 3
Duplicate Title Magic Node that excludes Different album
Posted: Wed Sep 28, 2005 3:02 pm
by mdave13
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.
Posted: Wed Sep 28, 2005 4:11 pm
by Bex
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.
Posted: Thu Sep 29, 2005 9:47 am
by karltinsly
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
Posted: Thu Sep 29, 2005 10:31 am
by karltinsly
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
Posted: Thu Sep 29, 2005 4:59 pm
by Bex
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?
Posted: Thu Sep 29, 2005 5:23 pm
by karltinsly
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
Posted: Fri Sep 30, 2005 3:40 am
by psyXonova
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
Duplicate Title Magic Node that excludes Different album
Posted: Sat Oct 01, 2005 12:42 am
by mdave13
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?
Posted: Sat Oct 01, 2005 3:47 am
by psyXonova
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
Posted: Sat Oct 01, 2005 9:40 am
by karltinsly
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!
Posted: Sat Oct 01, 2005 9:46 am
by psyXonova
I mean in Access.
Posted: Sat Oct 01, 2005 11:37 am
by rovingcowboy
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?
Posted: Sat Oct 01, 2005 12:23 pm
by Bex
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.
Duplicate Title Magic Node that excludes Different album
Posted: Sat Oct 01, 2005 12:57 pm
by mdave13
I do not possess Access. Is there any other way to add the query?
Re: Duplicate Title Magic Node that excludes Different album
Posted: Sat Oct 01, 2005 3:20 pm
by Bex
mdave13 wrote:I do not possess Access. Is there any other way to add the query?
Not that I know of...