Here is how todo it:
http://www.mediamonkey.com/forum/viewtopic.php?t=5924

But i think that if you put the sql (sqlfilter) in a MN it would be to slow...

/Bex

My Duplicate Content\<signature|MIN tracks:2>\<artist>

I was searching for a duplicate magic nodes code and came across this thread...

My questions are somewhat related to the comments on the thread, so I ask...

First, MM is supposed to generate a 'signature' when a track is first scanned, which is what the 'duplicate content' node utilizes... Right?

Second, What database field(s) are used to store this 'signature' and does/could magicnodes implement it as a MN mask.

Or how does one access this 'signature' via script?

In psuedocode, what I wish to accomplish is thus:

[code]My Duplicate Content\<signature|MIN tracks:2>\<artist>[/code]

Is something like this possible either directly with MN or via script?

Psyxonova!

Any news on this one?

/Bex

psyxonova, thats cool!

Looking forward to your solution!

Bex, Good news here,

I think i found a workaround for executing Action Queries from within a script.
I still haven't found a way to create a make table query though but i dont think is needed.
From what i see all your queries require just one table with one indexed column, the songID.
What i am thinking is to create a script that will prepare the data for MagicNodes everytime you want to check for duplicates or any other advanced query.
That way we will have only ONE generic MagicNode that will always check in our table for SongID.
All the advanced queries will be saved inside the MediaMonkey.ini with the help of that script. So, eg if you want to check for Duplicate Title and Album you will run the script and select the specific option. From now on, it is on the script to delete all previous data from our table, query the database and insert the new data in it and afterwards give focus to the Generic MagiicNode that will display results.
I am almost done with the query but i will be very busy the next week so expect news from me after some days.

SELECT Albums.Album, Songs.SongTitle
FROM Songs INNER JOIN Albums ON Songs.IDAlbum = Albums.ID
GROUP BY Albums.Album, Songs.SongTitle
HAVING (((Count(Songs.ID))>1));

SELECT Songs.ID INTO SameTitleAlbumName
FROM (Songs INNER JOIN Albums ON Songs.IDAlbum = Albums.ID) INNER JOIN SameTitleAlbumName1 ON (Songs.SongTitle = SameTitleAlbumName1.SongTitle) AND (Albums.Album = SameTitleAlbumName1.Album)
GROUP BY Songs.ID;

DELETE SameTitleAlbumName.ID
FROM SameTitleAlbumName;

SameTitleAlbumName|SQL filter: songs.id in (select id from SameTitleAlbumName)\<Artist>

And here you the code for same title and albumName. This Code is actually to prefer since it also includes albums wich has same name but different id.
Follow the procedure from above:
[code]SELECT Albums.Album, Songs.SongTitle
FROM Songs INNER JOIN Albums ON Songs.IDAlbum = Albums.ID
GROUP BY Albums.Album, Songs.SongTitle
HAVING (((Count(Songs.ID))>1));[/code]
Save as SameTitleAlbumName1
[code]SELECT Songs.ID INTO SameTitleAlbumName
FROM (Songs INNER JOIN Albums ON Songs.IDAlbum = Albums.ID) INNER JOIN SameTitleAlbumName1 ON (Songs.SongTitle = SameTitleAlbumName1.SongTitle) AND (Albums.Album = SameTitleAlbumName1.Album)
GROUP BY Songs.ID;[/code]
Save as SameTitleAlbumName3

- Run the make table query (SameTitleAlbumName3). Close it and open up the newly created SameTitleAlbumName table in design view and add a key to the ID field.

- Open the SameTitleAlbumName3 query in design view and change the query type to Append query.
[code]
DELETE SameTitleAlbumName.ID
FROM SameTitleAlbumName;[/code]
Save as SameTitleAlbumName2

The Magic Node:
[code]SameTitleAlbumName|SQL filter: songs.id in (select id from SameTitleAlbumName)\<Artist>[/code]

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

SELECT Songs.ID INTO SameTitleAlbumID
FROM Songs INNER JOIN SameTitleAlbumID1 ON (Songs.SongTitle = SameTitleAlbumID1.SongTitle) AND (Songs.IDArtist = SameTitleAlbumID1.IDAlbum)
GROUP BY Songs.ID;

DELETE SameTitleAlbumID.ID
FROM SameTitleAlbumID;

SameTitleAlbumID|SQL filter: songs.id in (select id from SameTitleAlbumID)\<Artist>

[quote="trooper95"]Nice work Bex!
I have very limited knowledge of MS Access and it only took me 30 minutes to get this to work. I havn't set up the macro portion yet. That's next.
Node works great. My only problem is that it returns 16,000 songs out of a 54,000 song library. Something seems odd.
Anyway, is there a way to do this so that it matches the same song AND same album? You know, if you have the same album in 3 places on your hard drives.[/quote]

Thanks!
16 000 out of 54 000? That does indeed sounds odd. But are they duplicates or not?

Here is the code for same title and albumID. Follow the procedure from above:
[code]SELECT Songs.IDAlbum, Songs.SongTitle
FROM Songs
GROUP BY Songs.IDAlbum, Songs.SongTitle
HAVING (((Count(Songs.ID))>1));[/code]
Save as SameTitleAlbumID1
[code]SELECT Songs.ID INTO SameTitleAlbumID
FROM Songs INNER JOIN SameTitleAlbumID1 ON (Songs.SongTitle = SameTitleAlbumID1.SongTitle) AND (Songs.IDArtist = SameTitleAlbumID1.IDAlbum)
GROUP BY Songs.ID;[/code]
Save as SameTitleAlbumID3

- Run the make table query (SameTitleAlbumID3). Close it and open up the newly created SameTitleAlbumID table in design view and add a key to the ID field.

- Open the SameTitleAlbumID3 query in design view and change the query type to Append query.
[code]
DELETE SameTitleAlbumID.ID
FROM SameTitleAlbumID;[/code]
Save as SameTitleAlbumID2

The Magic Node:
[code]SameTitleAlbumID|SQL filter: songs.id in (select id from SameTitleAlbumID)\<Artist>[/code]

Nice work Bex!
I have very limited knowledge of MS Access and it only took me 30 minutes to get this to work. I havn't set up the macro portion yet. That's next.
Node works great. My only problem is that it returns 16,000 songs out of a 54,000 song library. Something seems odd.
Anyway, is there a way to do this so that it matches the same song AND same album? You know, if you have the same album in 3 places on your hard drives.

DupsArtistTitleLenght|SQL filter: IDArtist&SongTitle&Format(SongLength/1000/86400,'hh:nn:ss') IN (SELECT IDArtist&SongTitle&Format(SongLength/1000/86400,'hh:nn:ss') FROM Songs GROUP BY IDArtist&SongTitle&Format(SongLength/1000/86400,'hh:nn:ss') HAVING Count(Songs.ID)>1)\<Artist>

DupsArtistTitleLenght|SQL filter: songs.id in (select id from TABLENAME)\<Artist>

SELECT Songs.IDArtist, Songs.SongTitle, Format([SongLength]/1000/86400,"hh:nn:ss") AS Length
FROM Songs
GROUP BY Songs.IDArtist, Songs.SongTitle, Format([SongLength]/1000/86400,"hh:nn:ss")
HAVING (((Count(Songs.ID))>1));

SELECT Songs.ID INTO SamArtTitLen
FROM Songs INNER JOIN SamArtTitLen1 ON (Songs.SongTitle = SamArtTitLen1.SongTitle) AND (Songs.IDArtist = SamArtTitLen1.IDArtist)
WHERE (((Format([SongLength]/1000/86400,"hh:nn:ss"))=[Length]))
GROUP BY Songs.ID;

DELETE SamArtTitLen.ID
FROM SamArtTitLen;

DupsArtistTitleLenght|SQL filter: songs.id in (select id from SamArtTitLen)\<Artist>

Ok here we go!

Here is my workaround to make slow Magic Nodes fast. (Magic Nodes with advanced SQLFilters.)

The idea is to create an indexed table in Access as a substitute for the SQLFilter used in the Magic Node.

Example:
Here is a very slow Magic Node wich displays all tracks with the same Artist, Title and Length.
[code]DupsArtistTitleLenght|SQL filter: IDArtist&SongTitle&Format(SongLength/1000/86400,'hh:nn:ss') IN (SELECT IDArtist&SongTitle&Format(SongLength/1000/86400,'hh:nn:ss') FROM Songs GROUP BY IDArtist&SongTitle&Format(SongLength/1000/86400,'hh:nn:ss') HAVING Count(Songs.ID)>1)\<Artist>[/code]

If you instead base the SQLFilter on an Acces Table it would look like this:
[code]DupsArtistTitleLenght|SQL filter: songs.id in (select id from TABLENAME)\<Artist>[/code]
So how do we create such a table in access? Well it's somehow complicated but i'll try to explain. (I assume you know how access works so I will not write any exact step by step instructions.)

1. First create a select query by pasting this code into the sql view of an Access query:
[code]SELECT Songs.IDArtist, Songs.SongTitle, Format([SongLength]/1000/86400,"hh:nn:ss") AS Length
FROM Songs
GROUP BY Songs.IDArtist, Songs.SongTitle, Format([SongLength]/1000/86400,"hh:nn:ss")
HAVING (((Count(Songs.ID))>1));[/code]
Save it as SamArtTitLen1

2. Create a "make table" query from this code:
[code]SELECT Songs.ID INTO SamArtTitLen
FROM Songs INNER JOIN SamArtTitLen1 ON (Songs.SongTitle = SamArtTitLen1.SongTitle) AND (Songs.IDArtist = SamArtTitLen1.IDArtist)
WHERE (((Format([SongLength]/1000/86400,"hh:nn:ss"))=[Length]))
GROUP BY Songs.ID;[/code]
Save it as SamArtTitLen3

3. Run the make table query (SamArtTitLen3). Close it and open up the newly created SamArtTitLen table in design view and add a key to the ID field.

4. Open the SamArtTitLen3 query in design view and change the query type to Append query.

5. Create a new delete query from this code:
[code]DELETE SamArtTitLen.ID
FROM SamArtTitLen;[/code]
Save it as SamArtTitLen2

Now you will have three queries:
- SamArtTitLen1, which is nested into SamArtTitLen3
- SamArtTitLen2, which deletes all data in table SamArtTitLen
- SamArtTitLen3, which adds new data to table SamArtTitLen
And one new table:
- SamArtTitLen, which is indexed on field ID
(The reason for having one delete and one add query is that we want to keep the key in the table SamArtTitLen which wouldnt be the case if we only used one make table query instead.)

Create a new Magic Node:
[code]DupsArtistTitleLenght|SQL filter: songs.id in (select id from SamArtTitLen)\<Artist>[/code]
This Magic Node is fast! 8)

The downside is that you MUST run the SamArtTitLen2 and SamArtTitLen3 queries everytime you change or add tracks to MM.
This could however be simplified by creating an Access Macro:
- SetWarnings = no
- OpenQuery SamArtTitLen2
- OpenQuery SamArtTitLen3
Save it as MacroSamArtTitLen
The macro is executed in 3 sec!


This method could be used on some really advanced Magic Nodes which would have been impossible on large collections. Due to too long execute time.
Eg:
- Display all "Duplicate content" without those marked with OK in custom3
- Display all tracks with same Artist, title and (lenght+-3 sec)
- Display all tracks with partial match on artist and title
- Etc..

Let me know what you think of this and what you want. I'll try to help out if I can.

Enjoy!

PS If it is possible to run macros or action queries in Access from a script then this method is really something to explore. Anyone knows?

[quote="psyxonova"]I am glad i helped,

Bex i am waiting for your queries. I my self am very interested too on creating cool magic NOdes

My scripts is just a workaround for the huge time Magin Nodes take to execute advanced SQL queries.
I hope we can make it...[/quote]

Haha, and I am waiting for you to check if it's possible to execute Access action queries (or preferable macros) from a scipt!

Well, I'm at work now but I'll post some cool queries here tonght 8)

I am glad i helped,

Bex i am waiting for your queries. I my self am very interested too on creating cool magic NOdes

My scripts is just a workaround for the huge time Magin Nodes take to execute advanced SQL queries.
I hope we can make it...

Super! Thanks again to both of you, bex and psyxonova!

[quote="karltinsly"]I'd like to add my thanks too -- this really helps find all those dupes! Do I understand correctly that I need to rerun the query in access and then restart MM if I want to see changes made on this node, such as removing a bunch of these dupes?[/quote]

No, the query is executed every time you click on the node!

I'd like to add my thanks too -- this really helps find all those dupes! Do I understand correctly that I need to rerun the query in access and then restart MM if I want to see changes made on this node, such as removing a bunch of these dupes?

Psyxonova,
Since I started this topic I thought I would Thank you an Bex for your work.
I was able to add your query to my database using a friends computer who had Access.
It worked very well, processing my 17,000+ songs in 2-3 seconds and gave exactly the results I was looking for.
Again, thanks to you and Bex.
Dave :D