Database format changed

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

Moderators: Gurus, Addon Administrators

Bex
Posts: 6316
Joined: Fri May 21, 2004 5:44 am
Location: Sweden

Post by Bex »

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

Post by jiri »

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
Steegy
Posts: 3452
Joined: Sat Nov 05, 2005 7:17 pm

Post by Steegy »

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).
Geeno
Posts: 4
Joined: Thu Apr 05, 2007 4:31 am

Post by Geeno »

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?
Peke
Posts: 17446
Joined: Tue Jun 10, 2003 7:21 pm
Location: Earth
Contact:

Post by Peke »

Have someone tried SQLite To ODBC?
Best regards,
Peke
MediaMonkey Team lead QA/Tech Support guru
Admin of Free MediaMonkey addon Site HappyMonkeying
Image
Image
Image
How to attach PICTURE/SCREENSHOTS to forum posts
GSV3MiaC
Posts: 455
Joined: Wed Jul 06, 2005 5:32 pm
Location: Shropshire, England

Post by GSV3MiaC »

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).
johnny2678
Posts: 15
Joined: Sun Aug 21, 2005 1:27 am

Post by johnny2678 »

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
Nova5
Posts: 193
Joined: Fri Mar 10, 2006 11:33 pm

Post by Nova5 »

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.
rhand
Posts: 10
Joined: Sun Aug 12, 2007 4:13 am

Accessing DB to tell MM files have moved to new drive

Post by rhand »

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
Rob K

Any news on an IUNICODE fix/workaround?

Post by Rob K »

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?
rhand
Posts: 10
Joined: Sun Aug 12, 2007 4:13 am

Workaround to IUNICODE problem

Post by rhand »

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.
acondios

Re: Database format changed

Post by acondios »

select Artist from Artists Order By Artist collate nocase desc
Rojer
Posts: 65
Joined: Tue Aug 22, 2006 5:06 am

Re: move files in one shot.

Post by Rojer »

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

Re: Database format changed

Post by Bex »

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
ZvezdanD
Posts: 3257
Joined: Thu Jun 08, 2006 7:40 pm

Re:

Post by ZvezdanD »

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.3.3 / 5.2 RegExp Find & Replace 4.4.9 / 5.2  Invert Selection/Select None 1.5.1  Export/Create Playlists for Child Nodes 4.1.1 / 5.4.1  Expand Child Nodes/Expand All 1.1.2  Event Logger 2.7  Filtered Statistics Report 1.6  Track Redirection & Synchronization 3.4.2  Restore/Synchronize Database 3.1.8 / 4.0.1  Find Currently Playing Track 1.3.2  Queue List 1.2.1  Add to Library on Play 1.0.1  Tree Report for Child Nodes 1.1.1  Update Location of Files in Database 1.4.5 / 2.3  Inherit Child Playlists 1.0.3  Add Currently Playing/Selected Track(s) to Playlist 1.2
Post Reply