MM 3.0 script changes

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

Steegy
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm

MM 3.0 script changes

Post by Steegy » Sat Feb 17, 2007 8:41 am

Hi

Devs: In MM 3.0, what will have to be changed to the scripts? Will it only be the database SQL querying, or also other aspects (the ExtractFields script now gives an error when its dialog is closed, is this a bug or a script change?)?

Scripters: It'd be nice if everyone could share his research/findings/... about the changes for SQLite here. E.g. what changes did you have to make, and what are good programs to view the database (I just tried shareware SQLite Analyzer)?

Cheers
Steegy
Extensions: ExternalTools, ExtractFields, SongPreviewer, LinkedTracks, CleanImport, and some other scripts (Need Help with Addons > List of All Scripts).

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

Post by Bex » Sat Feb 17, 2007 9:57 pm

I trying to figure out all the Database changes, will report back when I'm done.

Here's a good site with links to various programs:
http://www.sqlite.org/cvstrac/wiki?p=ManagementTools

Haven't tried any yet.
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

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

Post by Bex » Sun Feb 18, 2007 12:52 pm

Well, I have not had any time to investigate all this in detail. In fact I haven't yet found a query tool that I'm satisfied with. I'm very fond of MS Access since I'm familiar with its interface and "query builder/viewer". I found a SQLite ODBC Driver which let you connect to a SQLite database and view it Access environment. But it didn't fully work, I could only link some tables .... Here it is if someone wants to try:
http://www.ch-werner.de/sqliteodbc/

Questions to Devs:
- What version of SQLite do you use?
- What software do you use to query SQLite?
- Can you provide the current database structure?

The reason for not supplying a convert tool is that the final database structure isn't finally decided.
- What changes could be done to current version?
- Major ones or just some extra fields here and there?

I'd like to convert my existing version 2 db into version 3 but not if you plan to change the database so drastically so it would be useless.

Thanks
/Bex
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

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

Post by Bex » Sun Feb 18, 2007 1:11 pm

Ok, the current version seems to be SQLite 3.2.2 but the latest version is 3.3.13
What's the reason for using such old version?
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

Steegy
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm

Post by Steegy » Sun Feb 18, 2007 1:33 pm

Hey Bex

Looking at the database structure (using SQLite Analyzer or SQLite Expert), it seems like most of the stuff is already in place. However, why not wait for the release version of MM3.0 (and the converter that the devs say they'll provide) and meanwhile test MM3 with a limited songs scan. Or are you such a MM addict that you really need all your music/functionality every day... :wink: (you're probably not alone)

Anyway, I would stick with a native SQLite manager/editor... because ODBC drivers really only deliver a small part of the functionality/speed/stability/... . Some of the editors let you graphically build queries, but you can always write them yourself. I'm also going to miss the goodness of MSAccess, but so be it... it's for the good.

I suppose that changes to our scripts best wait until we're certain that all scripting/database functionality will be complete/frozen.
Extensions: ExternalTools, ExtractFields, SongPreviewer, LinkedTracks, CleanImport, and some other scripts (Need Help with Addons > List of All Scripts).

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

Post by Bex » Sun Feb 18, 2007 6:09 pm

Yeah, you're right Steegy! (Eventhough I'd like to get the stupid ODBC to work...)

Anyway, I figured out the changes now and they seems to be good. All fields that earlier were in AddSongInfoInt and AddSongInfo has been moved to the Songs table which imo is a dramatic improvement. The number of fields in the songs table increased from 45 to 89!

The implementation of multivalues fields works like this:
Lets take artist as an example.
The Artist(s) of a song are stored in the Songs table in the field Artist. If there are multiple artists they are separated with semicolons. (That's how you enter multiple artists to a song.)
The individual artists are as before stored in the table Artists but you don't have the field IDArtists anymore in the Songs table. So you can't link the Artists table to the Songs table any longer. Instead you must link through the new table ArtistsSongs which consists of ID, IDArtists and SongsID.
Like this:
Image

If we now query two songs. One with two artists, Abba and Roxette, and one with one artists, Faithless. We actually get three rows of data:
Image

This leads to that the first song is listed both under Abba and Roxette. But it could lead to other unwanted behavior if you count(*) or sum(*) something. I suspect that the use of count(distinct X) and sum(distinct X) will increase in such SQL's.

Multiple Genres and AlbumArtists works the same way.
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

Pablo
Posts: 554
Joined: Sun Feb 22, 2004 2:59 am

Post by Pablo » Sun Feb 18, 2007 10:26 pm

Thanks for the report Bex :).

The changes make a lot of sense. I look forward to make MagicNodes compatible with MM 3.0. When all is said and done most queries will likely be easier, and incompatibilities between fields will be gone.

Lowlander
Posts: 47519
Joined: Sat Sep 06, 2003 5:53 pm
Location: MediaMonkey 5

Post by Lowlander » Mon Feb 19, 2007 11:22 pm

I also would be nice if the developers can indicate when the database is feature complete. That will allow me to start figuring out how to connect to it from ASP.
Lowlander (MediaMonkey user since 2003)

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

Post by Bex » Tue Feb 20, 2007 1:14 am

@Pablo
Yes, I knew you would like the changes regarding putting all fields in Songs table!
If you want any help with the SQL-part in MN, let me know!

@Lowlander
That would be nice.
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

rusty
Posts: 7306
Joined: Tue Apr 29, 2003 3:39 am
Location: Montreal, Canada

Finalized database schema

Post by rusty » Tue Feb 27, 2007 12:43 pm

fyi, you can be sure that an announcement will come out from Jiri once the schema is finalized.

There are still a couple of schema changes expected in relation to making MM more compatible with Allmusic metadata and possibly in relation to classical music support (depending on the feedback we get).

-Rusty

mattisse
Posts: 65
Joined: Fri Feb 02, 2007 8:07 am

Post by mattisse » Wed Feb 28, 2007 5:41 am

Bex wrote: The implementation of multivalues fields works like this:
Lets take artist as an example.
The Artist(s) of a song are stored in the Songs table in the field Artist. If there are multiple artists they are separated with semicolons. (That's how you enter multiple artists to a song.)
Is there any word whether separation with semicolons is going to be the final way of implmenting multiple value fields?

Let's hope not. I know thats the way WMP handles it, but IMHO this solution really looks ugly and you're unable to distinguish between main performers and featured artists.

The devs really should look at Helium Music Manager's approach which seems to be quite neat.
They introduced a new field (and also a custom ID3 tag) "multiple artists" that divides the single artists by putting "|" around them. Between the artists you can put the form of involvement like "feat.", "and", "with" or dividers like "&", "/" and ",".
A string would e.g. look like:

Code: Select all

|The Beatles|and|The Rolling Stones|feat.|Aerosmith|,|Derek & the Dominos|&|Queen|
The usual "artist" the example string would be stored as:

Code: Select all

The Beatles and The Rolling Stones feat. Aerosmith, Derek & the Dominos & Queen
For display of the artists in playlists etc. the "artist" field would be used. But for linking songs to the individual artists the "multiple artists" field comes in play.

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

Post by Bex » Wed Feb 28, 2007 8:37 am

Yes, I've been thinking of that myself. It isn't the nicest way to display multiple artists with semicolons in between but I'm not sure how to solve the issue so it's compatible with other applications and ID3 standard.
Helium's way seems to be rather nice though.

Perhaps the dev's needs to re-evaluate this and come up with a nicer solution?
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

DaledeSilva
Posts: 893
Joined: Sun May 28, 2006 4:22 am
Location: Australia
Contact:

Post by DaledeSilva » Wed Feb 28, 2007 7:12 pm

well.. I don't see why it can't be stored in the tag as a semicolon divider...

but if the tag is this:
jack johnson;paul simon

it should be displayed in the tracklist as this:
jack johnson | paul simon


and while it may use semicolons in the tag... it doesn't even need to show them to the user in tag editing.. instead a field can be shown with the ability to click a plus and add other artists rather than typing in the semicolon manually.
My Plugins: PrettyPictures [v0.9.2], Forget Crossfade [v1.0], Behind Titlebars [v0.9], Filter Nodes [v1.0.1], View Mode [1.0], BPM.tapper [v1.0]
My Site: http://www.ublik-om.net

MarineBrat
Posts: 489
Joined: Tue Jun 14, 2005 12:12 am
Location: Loony left coast, USA.

Post by MarineBrat » Mon Apr 02, 2007 3:20 pm

Has anyone gotten anything through an ODBC driver yet? The only one I can find http://www.ch-werner.de/sqliteodbc/ hasn't worked at all for me.

RedX
Posts: 366
Joined: Wed Dec 27, 2006 10:32 am
Location: Germany

Post by RedX » Mon Apr 02, 2007 4:40 pm

For what do you want to use the ODBC driver?

Post Reply