SQL-Viewer 2.4 [Script] 2009-10-25

Download and get help for different MediaMonkey for Windows 4 Addons.

Moderators: Peke, Gurus

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Bex »

It's not a script issue. It's a SQLite syntax issue. This is how I should have done it:
"DELETE FROM PodcastEpisodes"
See SQLite website for the syntax they use:
http://www.sqlite.org/lang.html
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Owyn »

Thanks for the link (and the script) Bex. Got it tagged for future reference.

Thought for a second I was going to have to write a special purpose script. Should have known better. :o
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies
Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Owyn »

I am trying to check for multiple / unknown AlbumArtist in folders. Planning to use COUNT(DISTINCT X) for the analysis.

First step was to check the database. So,

SELECT SongPath, AlbumArtist FROM Songs

First stumbling block.
How can I extract the Location (folder) from SongPath, AND, use it in a SELECT?
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Bex »

You really should try my Tagging Inconsistencies Script, it already has those features! :)

But if you want an SQL, you can play around with these:
Multiple AlbumArtists

Code: Select all

SELECT RTRIM(SongPath,REPLACE(SongPath,'\','')) Folder, SongTitle, Artist, Album, AlbumArtist
FROM Songs WHERE IDFolder IN
(SELECT IDFolder
FROM Songs
GROUP BY IDFolder
HAVING COUNT(DISTINCT Album)=1 AND COUNT(DISTINCT AlbumArtist)>1)
Unknown AlbumArtists

Code: Select all

SELECT RTRIM(SongPath,REPLACE(SongPath,'\','')) Folder, SongTitle, Artist, Album, AlbumArtist
FROM Songs WHERE IDFolder IN
(SELECT IDFolder
FROM Songs
GROUP BY IDFolder
HAVING COUNT(DISTINCT Album)=1 AND COUNT(CASE WHEN TRIM(AlbumArtist)='' THEN NULL ELSE 1 END)=0)
Not fully tested so they might need some twaeking. Getting the Driveletter is a bit complex, but perhaps you don't need it?
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Owyn »

Thanks again Bex. For both the script and the SQL.

Going to work with both. :D

PS: My next cleanup project is to get Original Artist correct on Covers. In preparation I have switched to using Genre as tags, e.g. "Cover; Rock". I think I am going to have to write a script for that, nothing obvious showed up searching this forum. Should involve some shameless cut and paste from your (and other) scripts plus testing the SQL in the Viewer. :wink:
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Bex »

Thanks! :)

No problem, copy as much as you want but forget the source. If you miss something in Tagging Inconsistencies, just let me know and I'll to implement it, if it's not too user specific that is. :wink:
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Owyn »

The script worked a treat. "Multiple Artist Albums" was exactly what I needed to locate the "confused" folders. Fixed the bulk of the errors but left enough so that I could test the SQL.

More features in the script already than I can quickly figure out how to use.

The SQL looks good. I had missed the Folders table and Songs.IDFolder when I first started thinking about how to do the analysis.

***************
Just finished testing the multiple artist SQL. Pure gold. Just needed to bump the display count to the number of rows shown at the bottom to see the results.

**************
Ditto for the blank SQL. They were all in specific podcast feeds. Made a note to take a closer look at the RSS info for the feeds. Might be a bug in the episode tagger.
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies
Mizery_Made
Posts: 2283
Joined: Tue Aug 29, 2006 1:09 pm
Location: Kansas City, Missouri, United States

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Mizery_Made »

Maybe you could help me out Bex, since you seem like an SQL Wizard.

I have a database I've been using since Jan. 2007, so it contains a lot of play history. However, the computer it's on was done for the past three months so I've been using my new laptop. I have now fixed this PC and would like to migrate over to the old Database without losing the play history for the past three months. Thus, I copied the information from the new database and adjusted for the Song ID difference manually, and appended the new plays to the old database with the use of an INSERT command. The played table looks perfect.

Though, the trouble now comes with the Playcounts and Last Played data. Neither of these have been updated in the old database with information from the new database. This also comes from the More/Less Playcount in your Tagging Inconsistencies script as well, as I don't care how many hollow plays my iPod has added, I want a representation of what's been played and recorded with a time and such.

So, is there an SQL statement that would sort through the Played table and count the number of records and thus update the Playcount column in the Songs table with a correct count? Same goes for the Last Played, one that could run through and pull the last played date for these newly added plays.

Any ideas?
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Bex »

Update statements can be a bit tricky to get 100% correct so before you do anything please take a backup of your database!

This should display the tracks that needs updating:

Code: Select all

SELECT ID, Artist, SongTitle, PlayCounter, COUNT(*) CalcPlayCount, LastTimePlayed, MAX(PlayDate) MaxHistPlayDate
FROM Played, Songs 
WHERE Played.IDSong = Songs.ID 
GROUP BY ID, Artist, SongTitle, PlayCounter, LastTimePlayed
HAVING PlayCounter<>COUNT(*) OR CAST(LastTimePlayed AS TEXT)<>CAST(MAX(PlayDate) AS TEXT)
Here's the UPDATE SQL which actually alter your data:

Code: Select all

UPDATE Songs
SET PlayCounter = (SELECT COUNT(*) FROM Played WHERE Played.IDSong = Songs.ID GROUP BY Played.IDSong),
LastTimePlayed = (SELECT MAX(PlayDate) FROM Played WHERE Played.IDSong = Songs.ID GROUP BY Played.IDSong)
WHERE Songs.ID IN 
(SELECT IDSong FROM Played, Songs WHERE Played.IDSong = Songs.ID GROUP BY IDSong
HAVING PlayCounter<>COUNT(*) OR CAST(LastTimePlayed AS TEXT)<>CAST(MAX(PlayDate) AS TEXT))
I have not tested the Update SQL so let me know if it workes as intended.
Last edited by Bex on Sun Oct 19, 2008 3:01 pm, edited 1 time in total.
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
Mizery_Made
Posts: 2283
Joined: Tue Aug 29, 2006 1:09 pm
Location: Kansas City, Missouri, United States

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Mizery_Made »

The select statement appears to show the proper information, however there is trouble with the update statement.

Code: Select all

There was a problem querying the database:
Error executing QL "UPDATE Songs
SET PlayCounter = (SELECT COUNT(*) FROM Played WHERE Played.IDSong = Songs.ID GROUP BY Played.IDSong),
SET LastTimePlayed = (SELECT MAX(PlayDate) FROM Played WHERE Played.IDSong = Songs.ID GROUP BY Played.IDSong)
WHERE Songs.ID IN
(SELECT IDSong FROM Played, Songs WHERE Played.IDSong = Songs.ID GROUP BY IDSong
HAVING PlayCounter<>COUNT(*) OR CAST(LastTimePlayed AS TEXT)<>CAST(MAX(PlayDate) AS TEXT))
" : near "SET": syntax error (1,1)
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Bex »

Sorry, there's a SET too much. This should hopefully work:

Code: Select all

UPDATE Songs
SET PlayCounter = (SELECT COUNT(*) FROM Played WHERE Played.IDSong = Songs.ID GROUP BY Played.IDSong),
LastTimePlayed = (SELECT MAX(PlayDate) FROM Played WHERE Played.IDSong = Songs.ID GROUP BY Played.IDSong)
WHERE Songs.ID IN
(SELECT IDSong FROM Played, Songs WHERE Played.IDSong = Songs.ID GROUP BY IDSong
HAVING PlayCounter<>COUNT(*) OR CAST(LastTimePlayed AS TEXT)<>CAST(MAX(PlayDate) AS TEXT))
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Owyn »

Bex:
Got the first cut of a Covers finder SELECT working in the viewer.

Code: Select all

SELECT SongTitle, Artist, Album, Genre FROM Songs WHERE SongTitle IN
(SELECT DISTINCT SongTitle FROM Songs WHERE Artist LIKE "Beatles")
ORDER BY SongTitle
Now to get it into a script.
Your Find More From Same code looks like it should give me most of the boilerplate.
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies
Mizery_Made
Posts: 2283
Joined: Tue Aug 29, 2006 1:09 pm
Location: Kansas City, Missouri, United States

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Mizery_Made »

Looks to have done the trick, Bex.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Bex »

@Owyn,
I'm a bit uncertain on what you're trying to accomplish but if you need help, let me know.
Btw, don't use " around text fields, use ' insted. It's faster.
Also, LIKE isn't needed in your example since you don't have any wildcards (% or _) in Beatles, use = instead it's much faster.

@Mizery_Made
Great! :)
Advanced Duplicate Find & Fix Find More From Same - Custom Search. | Transfer PlayStat & Copy-Paste Tags/AlbumArt between any tracks.
Tagging Inconsistencies Do you think you have your tags in order? Think again...
Play History & Stats Node Like having your Last-FM account stored locally, but more advanced.
Case & Leading Zero Fixer Works on filenames too!

All My Scripts
Owyn
Posts: 2018
Joined: Fri Mar 21, 2008 10:55 am
Location: Canada

Re: SQL-Viewer 2.3 [Script] 2008-05-15 [MM3]

Post by Owyn »

Bex wrote:@Owyn,
I'm a bit uncertain on what you're trying to accomplish but if you need help, let me know.
Btw, don't use " around text fields, use ' insted. It's faster.
Also, LIKE isn't needed in your example since you don't have any wildcards (% or _) in Beatles, use = instead it's much faster.

@Mizery_Made
Great! :)
Technical Comments:
I used LIKE because (in this example) I could have both "Beatles" and "The Beatles" in Artist. Planning to strip prefix from LIKE seed string in the script.

I used "(double-quote) because I thought that was safer. Some artist names include '(single quote), e.g. Mark O'Connor. The value "Beatles", among others, was used to test the SQL. I tend to be paranoid about what real life data can provide.

PS: The example SQL may not look like much, but, it took 6 tries to get it working. All praise to the Viewer as a tool to develop and test MM3 specific SQL.

General Comments:
I belong to an informal community of cover fans centered around the podcast Coverville and including other music blogs such as Cover Freaks and Cover Lay Down.

What I am trying to accomplish is to build a "Cover Search for Artist" to find known and potential covers as they are added to the library. In my case it is of very immediate need as I am currently in the process of rebuilding my libary (I lost both the library and my primary backup for the library).

In terms of help. What you have provided to date is perfect. Tools to work with and educated comment on efficient use of SQL. Be warned that I may be back for more help. I am trying to learn by doing. Rough rule is that I can not get a particular SQL working withing an hour, then it is probably worth a question.

The SQL I showed is just the starting point. I needed to get the basics working before I added in the fancy stuff. At this point I can start to work on a script to contain and provide the function. And then make it better. With any sort of (good) luck I should be able to publish soon to this forum.

So for now I am back to reading code and identifying functions to re-use.

Hmmm.
One question. What is your development environment for MediaMonkey VBScript, in particular, what is your preferred editor?

Ok. That is enough diarrhea of the fingers for now. Back to the salt mines.
Cogito cogito ergo cogito sum. (Ambrose Bierce)
I drink therefore I am. (Monty Python)
Vista 32bit Home Premium SP2 / MM3.2.1.1297 Gold / Last.Fm 1.0.2.22 / IE8
Dell Inspiron 530 (1.8 Core2 / 2GB)
Skin: Vitreous Blue
Scripts: Add/Remove Playstat|Advanced Duplicate Find & Fix|Album Art Tagger|Backup|Batch Art Finder|Calculate Cover Size|Case&Leading Zero Fixer|DB_Audit|DB_Clean|DB_Schema|Event Logger|Genre Finder|Lyricator|Magic Nodes|MM2VLC|Monkey Rok|MusicIP Tagger|PUID Generator|RegExp Find & Replace|Right Click for Scripts|Scriptreloader|SQL Viewer|Stats(Filtered)|Tagging Inconsistencies
Post Reply