MM 3.0 script changes

[Steegy]

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

[Bex]

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.

[Bex]

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

[Bex]

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?

[Steegy]

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

[Bex]

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:


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:


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.

[Pablo]

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]

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.

[Bex]

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

[rusty]

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]

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]

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?

[DaledeSilva]

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.

[MarineBrat]

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]

For what do you want to use the ODBC driver?