Database format changed

This forum is for questions / discussions regarding development of addons / tweaks for MediaMonkey.

Moderator: Gurus

Postby Bex » Thu Apr 05, 2007 10:38 am

SQLite should have better speed than Access on big libraries. It also supports more functions and can do more stuff. (e.g. it supports Insrrev and Replace)

But I didn't know that it would be a problem to update the data outside MM. That's a big downside.

Btw, what is IUNICODE?
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 jiri » Thu Apr 05, 2007 12:33 pm

There are several very good reasons why we moved to SQLite, speed was just one of them and not the most important.

IUNICODE is our collation that's there in order to support Unicode sorting - which SQLite can't do internally.

Jiri
jiri
 
Posts: 5110
Joined: Tue Aug 14, 2001 7:00 pm
Location: Czech Republic

Postby Steegy » Thu Apr 05, 2007 1:56 pm

I'm sure that we won't really have to worry about editing the database from outside MediaMonkey in the future. Database editors for SQLite will improve over time, and as long as the queries work (through MediaMonkey or not) I don't see a real problem. I liked the Access database and the advanced way to view/edit it too, but we were spoiled with that.

SQLite means speed, more independance (better for Wine/Linux, no MDAC/MsJet problems anymore), smaller size, ...
Extensions: ExternalTools, ExtractFields, SongPreviewer, LinkedTracks, CleanImport, and some other scripts (Need Help with Addons > List of All Scripts).
Steegy
 
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm
Location: Belgium

Postby Geeno » Tue Apr 10, 2007 2:08 am

Steegy, I don't think that it's SQLite editors fault.

As Jiri said, they had to add a collation by code to the database (that's the IUNICODE thing that's causing us problems), and now the various SQLite editors can't touch the database anymore.
So there won't be any improvement in the future on this side.

And I don't think there won't be any necessity to access the MediaMonkey DB by different means than the application itself. If you can access the DB you can do things with your data that the developer haven't dreamt of or thought unnecessary.

Isn't there the possibility that the developers give us a utility or whatever it takes that makes the DB accessible by external tools like SQL Expert?
Geeno
 
Posts: 4
Joined: Thu Apr 05, 2007 4:31 am

Postby Peke » Tue Apr 10, 2007 2:15 pm

Have someone tried SQLite To ODBC?
Best regards,
Pavle
MM Core Developer and Fan (PLEASE DONATE HAPPYMONKEYING Site)
Image
Image
Peke
 
Posts: 8143
Joined: Tue Jun 10, 2003 7:21 pm
Location: Serbia

Postby GSV3MiaC » Tue Apr 10, 2007 3:48 pm

Steegy wrote:I'm sure that we won't really have to worry about editing the database from outside MediaMonkey in the future. Database editors for SQLite will improve over time, and as long as the queries work (through MediaMonkey or not) I don't see a real problem. I liked the Access database and the advanced way to view/edit it too, but we were spoiled with that.

SQLite means speed, more independance (better for Wine/Linux, no MDAC/MsJet problems anymore), smaller size, ...


I like the Access database A LOT, because it enables me to write queries to do things like 'rate a CD' (based on the tracks it contains, and whether they are duplicated elsewhere), or 'rate an artist' (and then produce a ranked lis of artists, which I can print).

I can also go in and patch it manually when something daft happens (VCD link gets screwed up because someone moved the folder or whatever).

I guess folks who don't have/use MSAccess don't have the facilities anyway (and even Microsoft seem intent on shafting Access in future - c.f. the 'Vistafied version', and the continual bleating if you use VBA code to manipulate records).
GSV3MiaC
 
Posts: 444
Joined: Wed Jul 06, 2005 5:32 pm
Location: Shropshire, England

Postby johnny2678 » Thu Jan 10, 2008 11:47 am

Hope it's ok to rehash an old thread like this...

I finally made the switch to MM3 and love the improvement. The ONLY downside that I can see so far is the change to SQL lite. I do understand the devs reasoning for doing so, but I was able to sync my song history from my windows mobile device to mediamonkey using some simple msaccess macros and now i can't anymore ;(

Has anyone on this thread figured out a way to get at MM.DB from within access? I tried ODBC but kept running into the UINICODE issue.

-Thanks
Image
johnny2678
 
Posts: 15
Joined: Sun Aug 21, 2005 1:27 am

Postby Nova5 » Fri Jan 11, 2008 6:49 pm

Geeno wrote:Jiri, as far as I know, most software companies don't like customers to mess with their application database at all, and they would rather use good old custom format files were it not so much easier to use a database.

So your comment stating that you don't like users being unable to tamper with the MM database is surely welcome.

As I said earlier, I would be more than satisfied if there was a way to move all songs to a new disk/root path in one fell swoop, but now I understand that a lot of users like to manipulate the database contents.

So please, even assuming that SQLite is far superior to MS Access, don't lock us out of the database.


There IS a way to move all the songs in one shot. use the Auto Organize Function. once you lay out your path structure your golden.

I use \\LIGHTWAVE\Music\Artist\Album\Artist - Track Title.mp3

If i need to move them to H: (to get them off the network) i just run Auto Organize and change the PC Lan Name to a drive letter since i want the rest of the path to remain the same.

this lays out the music as
\\LIGHTWAVE\Music\Eagles\Long Road Out Of Eden\Busy Being Fabulous.mp3
\\LIGHTWAVE\Music\Bad Company\The 'Original' Bad Co. Anthology\Bad Company - Honey Child.mp3

or after a reorg
H:\Music\Eagles\Long Road Out Of Eden\Busy Being Fabulous.mp3
H:\Music\Bad Company\The 'Original' Bad Co. Anthology\Bad Company - Honey Child.mp3

I did it from H to Lightwave to put it on the network. It took some time as the system had to move every single file out through the network and back due to it staying on the same PC. just opening it up for a network sharing setup. Works very well, few things are left behind. usually Thumbs.DB which you can always delete. a few album arts are left behind, but 99.9% of the time the program moves them or offers to move them.

This Moves them it does NOT Copy them.
Nova5
 
Posts: 191
Joined: Fri Mar 10, 2006 11:33 pm

Accessing DB to tell MM files have moved to new drive

Postby rhand » Sat Mar 29, 2008 4:40 am

The MediaMonkey knowledgebase includes an entry on what to do if your files have moved to a new drive, but I didn't feel safe monkeying around with the drive serial number - I didn't know what might break! So instead I came up with this alternate method.

I've whipped up a quick web page, complete with screen shots and a Javascript disk serial number hex->decimal calculator. Also it describes using the free SQLite Database Browser to look at your MediaMonkey data, so it may be of interest to those of you who want to see the raw MM data even if you haven't changed drives.

Hope this helps someone!

-Roger
rhand
 
Posts: 9
Joined: Sun Aug 12, 2007 4:13 am

Any news on an IUNICODE fix/workaround?

Postby Rob K » Sun Mar 30, 2008 3:26 am

Hi,

Any news on the IUNICODE problem? Are there any IDEs out there than can support the MM.DB format?

I've used the SQL-Viewer script but it's just not going to be as feature rich (or convenient) as say SQLiteSpy. It's a real shame that simple queries like "select * from Songs where SongTitle = 'ABC'" aren't viable.

Jiri: What do you guys use for manipulating the DB whilst developing?
Rob K
 

Workaround to IUNICODE problem

Postby rhand » Sun Mar 30, 2008 5:52 pm

Using the free SQLite Database Browser I did this:

    1. Opened a copy of the db.
    2. Exported the data to a SQL file.
    3. Deleted all the "COLLATE IUNICODE" strings.
    4. Imported this SQL file to a new DB file.


Then queries such as
select * from Songs WHERE SongTitle = 'Good Times'
work fine.

Of course having to export/import each time is a drag. Perhaps this could be somewhat automated using the sqlite3 command line tool but I haven't looked into that.
rhand
 
Posts: 9
Joined: Sun Aug 12, 2007 4:13 am

Re: Database format changed

Postby acondios » Thu Jan 01, 2009 2:59 pm

select Artist from Artists Order By Artist collate nocase desc
acondios
 

Re: move files in one shot.

Postby Rojer » Sat Jan 24, 2009 6:34 pm

I believe there is a better way to move all your files in one truly single shot : from the library node, just drag and drop the parent node (if you were wise enough to setup one) to the new drive/folder.

The new drive will have to be referenced in database already, which means that at list one file on that drive must have been catalogued prior to this operation.

If you don't have a parent directory, let's hope you have few ones, like genres or something, in which case you can still do the drag and drop thing, only one by one.

MM will ask if wether you want to move the library content or all the folders content. This features makes this option superior to the auto-organize thing, as :

- Some content might not be cataloged, even as linked files
- A single auto-organize scheme is not likely to be desirable, depending on a lot of things. it surely is NOT in my case.

Something I would find useful, would be for MM to have an option to IGNORE the fact that some files can't be found. Just update tags according to the move operation. That would make the disk serial number problem, pretty much a thing of the past.

Only thing that bugs me with this : process is too long, just like the auto-organize feature, while it is only a rename operation. In window explorer, moving a folder is pretty much instantaneous. Moving from either the library or the computer node behaves like moving the files one by one. Even weirder for the computer node, as no fields are updated in db and there is no reason whatsoever to move the files one my one on a folder move operation.

I guess I know too little about MM to make any assumptions here.

I you kept your paths clean the best way still seem to be the "move device content script", which has been working flawlessly for me a number of times. I make life hard on hard disks. The newer, faster and cheaper they get, the less they can take it. Hence this problem will only become worse with time.

Alternate thoughts:
Two years ago I decided to go for a safer route on a friends computer. As he needed to swap dive and even computers, and she actually WORKS with media monkey, I went for the "mount" option. One out of three mirrored mp3 dedicated drives will be mounted on the system drive, meaning she can swap for a mirror, either external or internal when she needs it. Plus she can share the drives between a desktop and a laptop (which have the same system disk serial number). Only drawback, she had to learn to mount a drive in windows, which is a bit weirder than in linux. Plus she had to master a mirroring script I made for her. No big deals it appears.

Of course, MM doesn't see a thing since only the system drive shows in the MEDIAS table... This should work in MM3 too, despite the addition of the drive letter.

This is the best method I found to circumvent this scary and weird problem and I plan to implement it on my own database one of these days; probably next time I swap for a new system, so that I won't have to bother until next new computer; like in 2016 or something...

Sorry to be long. But this problem has been bugging me for so long that I thought that might be worth it.
Let me know what you think.
Rojer

Nova5 wrote:
Geeno wrote:Jiri, as far as I know, most software companies don't like customers to mess with their application database at all, and they would rather use good old custom format files were it not so much easier to use a database.

So your comment stating that you don't like users being unable to tamper with the MM database is surely welcome.

As I said earlier, I would be more than satisfied if there was a way to move all songs to a new disk/root path in one fell swoop, but now I understand that a lot of users like to manipulate the database contents.

So please, even assuming that SQLite is far superior to MS Access, don't lock us out of the database.


There IS a way to move all the songs in one shot. use the Auto Organize Function. once you lay out your path structure your golden.

I use \\LIGHTWAVE\Music\Artist\Album\Artist - Track Title.mp3

If i need to move them to H: (to get them off the network) i just run Auto Organize and change the PC Lan Name to a drive letter since i want the rest of the path to remain the same.

this lays out the music as
\\LIGHTWAVE\Music\Eagles\Long Road Out Of Eden\Busy Being Fabulous.mp3
\\LIGHTWAVE\Music\Bad Company\The 'Original' Bad Co. Anthology\Bad Company - Honey Child.mp3

or after a reorg
H:\Music\Eagles\Long Road Out Of Eden\Busy Being Fabulous.mp3
H:\Music\Bad Company\The 'Original' Bad Co. Anthology\Bad Company - Honey Child.mp3

I did it from H to Lightwave to put it on the network. It took some time as the system had to move every single file out through the network and back due to it staying on the same PC. just opening it up for a network sharing setup. Works very well, few things are left behind. usually Thumbs.DB which you can always delete. a few album arts are left behind, but 99.9% of the time the program moves them or offers to move them.

This Moves them it does NOT Copy them.
Rojer
 
Posts: 65
Joined: Tue Aug 22, 2006 5:06 am

Re: Database format changed

Postby Bex » Mon Jan 26, 2009 5:06 pm

To all which stumble upon this thread looking for a solution to query/update MM's database without any errors.
Here is a working solution: SQL-Viewer
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:

Postby ZvezdanD » Tue Jan 27, 2009 3:39 am

Bex wrote:It also supports more functions and can do more stuff. (e.g. it supports Insrrev and Replace)

:D What a joke :lol: Sorry, I saw this thread and your post just now.
Magic Nodes 4.2 (2011-07-01) RegExp Find & Replace 4.3 (2011-07-06)  Invert Selection/Select None 1.5 (2012-02-04)  Export/Create Playlists for Child Nodes 4.0.4 (2013-08-03)  Expand Child Nodes/Expand All 1.1.1 (2012-02-13)  Event Logger 2.4.1 (2012-02-06)  Filtered Statistics Report 1.5.1 (2009-10-09)  Track Redirection & Synchronization 3.4 (2012-10-08)  Restore/Synchronize Database 3.1.2 (2013-08-31)  Find Currently Playing Track 1.3 (2013-11-09)  Queue List 1.2 (2012-02-06)  Add to Library on Play 1.0 (2010-10-20)  Tree Report for Child Nodes 1.1 (2010-11-04)  Update Location of Files in Database 1.3.3 (2012-06-12)  Inherit Child Playlists 1.0.1 (2013-07-10)  Add Currently Playing/Selected Track(s) to Playlist 1.1.1 (2012-02-06)
ZvezdanD
 
Posts: 2708
Joined: Thu Jun 08, 2006 7:40 pm

PreviousNext

Return to Addons developer forum

Who is online

Users browsing this forum: No registered users and 1 guest

cron