Using SQL Maestro as a MM3 DB editor
Using SQL Maestro as a MM3 DB editor
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
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.
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.
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
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
The way MM implemented IUNICODE collation could be easily described as WinAPI call:
Jiri
Code: Select all
CompareStringW( 0x0409 /*US English*/, NORM_IGNORECASE | SORT_STRINGSORT, str1, len1/2, str2, len2/2) - 2;
-
- Posts: 19
- Joined: Mon Apr 21, 2008 7:37 pm
Possible solution for IUNICODE in Python 2.5
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:
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.
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...
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.
-
- Posts: 19
- Joined: Mon Apr 21, 2008 7:37 pm
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.:
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.
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;
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.
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.
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
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
-
- Posts: 106
- Joined: Mon Jun 27, 2005 2:47 pm
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 still prefer using the MM SQL script by Bex but the Firefox add-on does offer some nice features.
-
- Posts: 19
- Joined: Mon Apr 21, 2008 7:37 pm
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?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.
Bex, I did see that script, and thank you. I've installed it now, but haven't played with it yet.
-
- Posts: 106
- Joined: Mon Jun 27, 2005 2:47 pm
IUNICODE collation implementation
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?
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?
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:
JiriCode: Select all
CompareStringW( 0x0409 /*US English*/, NORM_IGNORECASE | SORT_STRINGSORT, str1, len1/2, str2, len2/2) - 2;
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
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
-
- Posts: 1321
- Joined: Thu Jun 15, 2006 3:26 pm
- Location: Geelong, Victoria, Australia
Re: IUNICODE collation implementation
***Shyly Puts Hand Up***petr.vanek wrote:hi all,
I'm solving this issue for Sqliteman (propably based on bugreport from somebody from MediaMonkey community):
but how you forced it into sqlite db? You have to load it somehow with sqlite3 api commands, haven't you?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:
JiriCode: Select all
CompareStringW( 0x0409 /*US English*/, NORM_IGNORECASE | SORT_STRINGSORT, str1, len1/2, str2, len2/2) - 2;
-
- Posts: 19
- Joined: Mon Apr 21, 2008 7:37 pm
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.Anonymous wrote:but how you forced it into sqlite db? You have to load it somehow with sqlite3 api commands, haven't you?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:
JiriCode: Select all
CompareStringW( 0x0409 /*US English*/, NORM_IGNORECASE | SORT_STRINGSORT, str1, len1/2, str2, len2/2) - 2;
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! )