Database conversion

[David Dyer-Bennet]

I've got an existing Thumbs.tpdb8 file of 1.762 GB (limit is 2GB, right?); so I'm not out of space yet but I'm getting worried.  

I believe I have quite a few keywords in the db that aren't embedded in the original files -- I think that's what I ended up with when keywording RAW formats that TP doesn't know how to modify for example.  So I can't just trash my database, create a new one, and let it do a massive reindex.  There might I suppose also be other data not embedded in the files, I can't prove otherwise.

Is there any other way to convert to SQlite?

Alternatively, is there a way to check whether in fact the database reflects only file data?  Or is there a way to extract from the database a list of keywords at least that *aren't* also embedded in the files?  

As a desperation move I suppose I could export a complete set of keyword / image file pairs, rebuild the database, and then add back all those pairs (which would be a waste of time for the ones already found in the image files, but would fill in the missing ones. I suspect it would take a while, too.  Also not completely sure how I could export such a list, I don't have Access.

Any other ideas?  Is there  a free DB tool, or a Perl or Python library, or something, that will read the .tpdb8 file format (I think that's a version of Access, but whatever it really is)?

[Daan van Rooijen]

> Is there any other way to convert to SQlite?

I've never tried, but I read about a freeware tool called 'SQLite2009 Pro Enterprise Manager' (http://sqlite2009pro.azurewebsites.net) that is said to offer import from MDB into SQLite databases. It might be worth a shot (and I'd be curious to hear about your findings).
 
> Alternatively, is there a way to check whether in fact the database reflects only file data?  

Not directly, but maybe you could make a new database, configure TP to copy any embedded keywords from your images into its database (Options | Preferences | Metadata) when it makes thumbnails, and then compare the results to what you currently have in your real database.

One way to get a list of filenames and their keywords (and I'm afraid much more) is to Export the database to a text file using Database | Advanced | Export (don't include the actual thumbnail data in the export if you decide to do this, or you'd get a monstrously large file).

> As a desperation move I suppose I could export a complete set of keyword / image file pairs, rebuild the database, and then add back all those pairs (which would be a waste of time for the ones already found in the image files, but would fill in the missing ones. I suspect it would take a while, too.

Theoratically that would be a good solution and you don't have to include thumbnail data in an export/import; ThumbsPlus can re-generate those if the files are available. That would speed things up. However, the export/import functions in ThumbsPlus are quite unreliable due to file format confusion and they tend to stumble over the smallest things (like multi-line annotations). Still, it could be worth trying.

> Is there  a free DB tool, or a Perl or Python library, or something, that will read the .tpdb8 file format (I think that's a version of Access, but whatever it really is)?

They're regular .MDB files. Try the excellent and free [link=http://www.alexnolan.net/software/mdb_viewer_plus.htm]MDB Viewer Plus[/link] by Alex Nolan. Type *.tpdb8 in its File Open dialog to find and open your ThumbsPlus database files (by default it only shows .mdb files).

[David Dyer-Bennet]

.mdb is older access, 2003 and earlier; is .tpdb8 that, and not the later .accdb (Access 2007)?  I was having failures earlier tonight using Libre Office database functions to read it (actually, to read a much smaller test db I created, indexing just one directory of photos), and this might explain it.

I'll also try the reader product you suggest.

I've done things with text exports before, and yeah, in fact it screwed up a bunch of my keywords back then.  However, text export and then parsing it carefully might be another way for me to get the data that may be lost in a reindexing.

[David Dyer-Bennet]

MDB viewer plus appears to hang whenever I try to touch the Thumbnail table (including in an SQL query), or just click on that tab, so that's not working out.

[David Dyer-Bennet]

I let it run for a long time and it finally reached a "system resources exhausted" error; it apparently can't cope with a Thumbnail table the size of mine.

[Daan van Rooijen]

> .mdb is older access, 2003 and earlier; is .tpdb8 that, and not the later .accdb (Access 2007)?

Yes. The default format is MS Access 2000 Jet to be precise.

Sorry to hear about MDB Viewer, I've never had that problem here.

Are you familiar with the Boxer text editor (www.boxersoftware.com)? It's great with comma separated text databases. You can tell it to treat commas as tabs, aligning all fields into columns, which makes it easy to spot errors. From there you can also convert tabs to spaces to 'freeze' this layout and get a file that's easier to edit. It also sorts and aligns columns, edits columns (over multiple lines), etc. It does not support unicode, but other than that it's a great editor.

[mschnell]

There had been database conversion options coming with TP (v8 or so), but they seem not to be supported any more.

Moreover, I read that the currently used version of SQLite is said to be a lot slower than the "Jet" engine (aka Access database) that you currently use, making TP rather sluggy in certain actions.

Still moreover, here in the Forum there had been discussions of users testing the next SQLite version and confirming it to be **much** faster than the previous one (maybe even faster than Jet and obviously no 2GB Limit). But using this version is not absolutely straight forward and seems to need manual interaction whenever an update of TP is installed.

So I conclude (and hope) that one day soon the next TP version will come with the most recent SQLite engine perfectly integrated out of the box, and will provide appropriate conversion Python scripts as well for tpdb8 files, as for old TP7 td4 files (which I still use) and finally allow (me and many more addicted TP7 users with huge databases ) to convert to a more recent TP version.

-Michael