Using SQL Maestro as a MM3 DB editor

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

Moderators: Peke, Gurus

peter_h
Posts: 118
Joined: Mon Mar 12, 2007 4:38 pm

Using SQL Maestro as a MM3 DB editor

Post by 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 :)
:) Pete, from Wellington, New Zealand.

Running MM4 4.1.31.1919, on Win7 SP1, 16Gig memory, on Lenovo W530 (Intel i7) laptop
**If you're wondering why I'm still on MM4: It still has more useful-to-me plugins; and I prefer the GUI's responsiveness, panes spreadable over multi-monitors flexibility, and predictability (all coz it's more Windows-native). I also hate "flat design" for its visual inefficiency. For me, MM4's benefits are still > MM5, and MM5's downsides < MM4.
Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

Sounds great. But I guess you have to address your question to the developers.
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
Guest

Post by Guest »

I'm assuming one of the developers will read this? If not, how else do I contact them?
jiri
Posts: 5417
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic
Contact:

Post by 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
Posts: 19
Joined: Mon Apr 21, 2008 7:37 pm

Possible solution for IUNICODE in Python 2.5

Post by 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
Posts: 19
Joined: Mon Apr 21, 2008 7:37 pm

Post by 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
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by 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.
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
MDominik13
Posts: 106
Joined: Mon Jun 27, 2005 2:47 pm

Post by 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
Posts: 19
Joined: Mon Apr 21, 2008 7:37 pm

Post by Sproaticus »

MDominik13 wrote: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
Posts: 106
Joined: Mon Jun 27, 2005 2:47 pm

Post by 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

IUNICODE collation implementation

Post by 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
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

I don't think it's stored in a separate dll. Can't you use this info?
jiri wrote: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
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
MoDementia
Posts: 1321
Joined: Thu Jun 15, 2006 3:26 pm
Location: Geelong, Victoria, Australia

Re: IUNICODE collation implementation

Post by MoDementia »

petr.vanek wrote:hi all,

I'm solving this issue for Sqliteman (propably based on bugreport from somebody from MediaMonkey community):
***Shyly Puts Hand Up*** :P
Guest

Post by Guest »

Bex wrote:I don't think it's stored in a separate dll. Can't you use this info?
jiri wrote: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
Posts: 19
Joined: Mon Apr 21, 2008 7:37 pm

Post by Sproaticus »

Anonymous wrote:
Bex wrote:I don't think it's stored in a separate dll. Can't you use this info?
jiri wrote: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! :wink:)
Post Reply