Using SQL Maestro as a MM3 DB editor

[peter_h]

I've made an enquiry with the "SQL Maestro" developers, as to the possibility of using their product with MM3's IUNICODE-collated database.

Vadim has written back and asked for a database sample and a collation description.

Anyone like to provide one...as I'm out of my depth here with MM3's collations & SQLite (a new db for me)...?

Great to hear they're at least interested to take a look!

I'll email him back and point him to this thread, so everyone's involved

[Bex]

Sounds great. But I guess you have to address your question to the developers.

[Guest]

I'm assuming one of the developers will read this? If not, how else do I contact them?

[jiri]

The way MM implemented IUNICODE collation could be easily described as WinAPI call:

Code: Select all

CompareStringW( 0x0409 /*US English*/, NORM_IGNORECASE | SORT_STRINGSORT, str1, len1/2, str2, len2/2) - 2;

Jiri

[Sproaticus]

I haven't tested this against the behavior of MM's IUNICODE collation, but this seems to work in Python 2.5 using the sqlite3 module:

Code: Select all

import sqlite3

# define IUNICODE collation function
def iUnicodeCollate(s1, s2):
    return cmp(s1.lower(), s2.lower())

# connect to database
conn = sqlite3.connect('MM.DB')

# register our custom IUNICODE collation function
conn.create_collation('IUNICODE', iUnicodeCollate)

# run your query, business as usual
cursor = conn.execute('select * from Songs')
# ...etc...

The important bits are the definition of the iUnicodeCollate() function, and using conn.create_collation() to apply it to the db connection.

And like I said, I don't know if this behavior matches that of the one implemented in MediaMonkey. It does work without any errors, and behaves how I think it should behave, which is good enough for me.

If the Sqlite API in VB lets you define custom collations, this trick may work there as well. Unfortunately, I'm not well-versed in VB, and since my technique doesn't use ODBC I'm not even sure it'll translate to VB.

Some relevant links:

Sqlite docs on defining new collating sequences

Python API docs for Sqlite connection objects

Forum post by jiri in "sqlite db query error" which pointed me to the SQLite3_CreateCollation16() function.

[Sproaticus]

Replying to my own post...

You can bypass the IUNICODE collation using just plain SQL, making off-the-shelf SQLite tools like SQLiteSpy useful for MM hacking. There's no need to create a custom collation. The drawback is that collation-dependent queries, e.g. anything using an ORDER BY, will behave differently than MM. My guess is that this is largely restricted to issues involving sorting and grouping and whatnot, but I'm still pretty green with SQLite and probably wrong.

Anyway.

In order to bypass IUNICODE, you need to tag every expression in your SQL query with the COLLATE operator, e.g.:

Code: Select all

SELECT Songs.ID, Songs.SongTitle, Songs.Artist
FROM PodcastEpisodes, Songs
WHERE PodcastEpisodes.IDTrack = Songs.ID COLLATE BINARY
  AND Songs.Genre = 'Boring' COLLATE BINARY
ORDER BY Songs.ID;

The important bits there are the COLLATE BINARY after each expression.

I'm still using ORDER BY even though I've changed the collation - I'm making an assumption that the SYSTEM collation won't behave too differently than the IUNICODE collation when it comes to sorting numbers.

[Bex]

Well, COLLATE NOCASE is better since it doesn't care about the case. It is also only needed to apply it on TEXT fields (i.e COLLATE IUNICODE fields) and functions which is based on such fields. However, this doesn't make the SQL to always return expected result.
e.g. COLLATE NOCASE treats Unicode upper vs lower case characters as different characters.
Also, update/insert statements involving text fields is not possible in any external tool.


Btw, did you see the SQL-Viewer script?
http://www.mediamonkey.com/forum/viewtopic.php?t=24841
Perhaps it's not as fancy as the a "real" SQLite db editor but it doesn't have any of their drawbacks.

[MDominik13]

For any Firefox users, there's also an add-on that seems to work pretty well with the MM DB: SQLite Manager

I still prefer using the MM SQL script by Bex but the Firefox add-on does offer some nice features.

[Sproaticus]

For any Firefox users, there's also an add-on that seems to work pretty well with the MM DB: SQLite Manager

I still prefer using the MM SQL script by Bex but the Firefox add-on does offer some nice features.

I like this add-on, and it's what I use on Linux where SQLiteSpy won't work well. But SQLite Manager has the same problem that SQLiteSpy has with the MM database, with the IUNICODE collation. How do you get around that using SQLite Manager?

Bex, I did see that script, and thank you. I've installed it now, but haven't played with it yet.

[MDominik13]

Guess I should have kept my mouth shut on this one... I have been using the Firefox add-on for the import and schema features (no queries). I've been using the MM SQL Script for any SELECT queries.

[petr.vanek]

hi all,

I'm solving this issue for Sqliteman (propably based on bugreport from somebody from MediaMonkey community):

http://sqliteman.com/bugtracker/view.php?id=134

Can you point me where is your IUNICODE collation stored, please? I mean in which DLL file?

[Bex]

I don't think it's stored in a separate dll. Can't you use this info?

The way MM implemented IUNICODE collation could be easily described as WinAPI call:

Code: Select all

CompareStringW( 0x0409 /*US English*/, NORM_IGNORECASE | SORT_STRINGSORT, str1, len1/2, str2, len2/2) - 2;

Jiri

[MoDementia]

hi all,

I'm solving this issue for Sqliteman (propably based on bugreport from somebody from MediaMonkey community):

***Shyly Puts Hand Up***

[Guest]

I don't think it's stored in a separate dll. Can't you use this info?

The way MM implemented IUNICODE collation could be easily described as WinAPI call:

Code: Select all

CompareStringW( 0x0409 /*US English*/, NORM_IGNORECASE | SORT_STRINGSORT, str1, len1/2, str2, len2/2) - 2;

Jiri

but how you forced it into sqlite db? You have to load it somehow with sqlite3 api commands, haven't you?

[Sproaticus]

I don't think it's stored in a separate dll. Can't you use this info?

The way MM implemented IUNICODE collation could be easily described as WinAPI call:

Code: Select all

CompareStringW( 0x0409 /*US English*/, NORM_IGNORECASE | SORT_STRINGSORT, str1, len1/2, str2, len2/2) - 2;

Jiri

but how you forced it into sqlite db? You have to load it somehow with sqlite3 api commands, haven't you?

It's a sqlite API call - sqlite3_create_collation() and its cousins sqlite3_create_collation_v2() and sqlite3_create_collation16() - see http://www.sqlite.org/c3ref/create_collation.html for the docs.

It's also what I used in my Python example earlier in this thread. What would be really cool is if you could integrate SpiderMonkey (or whatever ECMAScript engine QT3 recommends), or whatever scripting language you prefer, and allow your end-users to do this sort of thing themselves. (A tall order, I admit! )