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

[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

[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.

[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?

[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?

[Owyn]

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

Going to work with both.

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.

[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.

[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.

[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]

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.

[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]

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))

[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.

[Mizery_Made]

Looks to have done the trick, Bex.

[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!

[Owyn]

@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.