Using SQL Maestro as a MM3 DB editor

Download and get help for different MediaMonkey Addons.

Moderators: Peke, Gurus

Using SQL Maestro as a MM3 DB editor

Postby peter_h » Wed Jan 30, 2008 7:46 am

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 WinXP sp3, 2Gig memory, nVidia 8600GT video.
peter_h
 
Posts: 112
Joined: Mon Mar 12, 2007 4:38 pm

Postby Bex » Wed Jan 30, 2008 11:07 am

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

Postby Guest » Wed Jan 30, 2008 7:02 pm

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

Postby jiri » Thu Jan 31, 2008 6:49 am

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
jiri
 
Posts: 5290
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic

Possible solution for IUNICODE in Python 2.5

Postby Sproaticus » Mon Apr 21, 2008 7:50 pm

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: 14
Joined: Mon Apr 21, 2008 7:37 pm

Postby Sproaticus » Sat Jun 07, 2008 12:04 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.:

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.
Sproaticus
 
Posts: 14
Joined: Mon Apr 21, 2008 7:37 pm

Postby Bex » Sun Jun 08, 2008 9:24 am

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

Postby MDominik13 » Sun Jun 08, 2008 5:32 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.
MDominik13
 
Posts: 106
Joined: Mon Jun 27, 2005 2:47 pm

Postby Sproaticus » Sun Jun 08, 2008 8:29 pm

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.
Sproaticus
 
Posts: 14
Joined: Mon Apr 21, 2008 7:37 pm

Postby MDominik13 » Mon Jun 09, 2008 11:39 am

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

IUNICODE collation implementation

Postby petr.vanek » Wed Jun 11, 2008 6:36 am

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?
petr.vanek
 

Postby Bex » Wed Jun 11, 2008 7:00 am

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

Re: IUNICODE collation implementation

Postby MoDementia » Wed Jun 11, 2008 7:44 am

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
MoDementia
 
Posts: 1321
Joined: Thu Jun 15, 2006 3:26 pm
Location: Geelong, Victoria, Australia

Postby Guest » Wed Jun 11, 2008 11:46 am

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?
Guest
 

Postby Sproaticus » Wed Jun 11, 2008 12:13 pm

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:)
Sproaticus
 
Posts: 14
Joined: Mon Apr 21, 2008 7:37 pm

Next

Return to Need Help with Addons?

Who is online

Users browsing this forum: Bing [Bot] and 9 guests