ThumbsPlus

All forums => ThumbsPlus v10 Questions => Topic started by: mschnell on 2017-03-25 04:35:37

Title: Database upgrade
Post by: mschnell on 2017-03-25 04:35:37
Hi Exert(s),

I am in a terrible need, as the old "Access" database engine, our TP7 setup (two users) uses hit the 2GB limit and TP is not usable any more.

Since years, we are planning to upgrade to a more recent version of TP using a more recent database engine. I even tried a Server based database (Postgres on a Linux box) but this was hard to set up and in the end a lot slower than the old "Access" stuff.

Unfortunately (according to some discussions in this forum), TP10 does not yet seem to provide a tool to import a TP7 Access type database to an SQLite Database.

Is there any help (other than installing a network server box with MSSQL) ? Either by upgrading to TP10, or by using TP7 with SQLite ?

-Michael
Title: Database upgrade
Post by: Daan van Rooijen on 2017-03-25 08:25:43
> Is there any help (other than installing a network server box with MSSQL) ? Either by upgrading to TP10, or by using TP7 with SQLite ?

TP7 doesn't support SQLite.

If your images are JPG/TIF/PNG, you could use the TP7 IPTC editor to copy your keywords and annotations into the image files. Then create an SQLite3 database in TP10, thumbnail the images and have all this information copied into the database automatically.

Another (experimental) approach would be to use a generic MDB-to-SQLite3 converter to see if that produces a working database. Several converters appear to be available for free when you Google for them. However, before trying that, you would first have to use TP10 to convert your .TD4 database to the current .TPDB8 format (from which you can then convert it to SQLite3), and you would have to create a new SQLite3 database in TP10 and thumbnail a few images so you can examine its structure, and compare the converted database to it to see if all required tables and field definitions are present in the converted database.
Title: Database upgrade
Post by: mschnell on 2017-03-25 09:17:12
> TP7 doesn't support SQLite.
Bad news. I had assumed that after creating an SQLIte database, TP7 would be able to use it's SQL interface to access it.

>If your images are JPG/TIF/PNG, you could use the TP7 IPTC editor to copy your keywords and annotations into the image files. Then create an SQLite3 database in TP10, thumbnail the images and have all this information copied into the database automatically.

The main purpose to use ThumbsPlus is the Galleries. We have hundreds of those. This information never is stored in the image files themselves.

> Another (experimental) approach would be to use a generic MDB-to-SQLite3 converter to see if that produces a working database.

OK. In fact I could do such a tool myself using some programming language that supports SQL access (e.g. Python).

>However, before trying that, you would first have to use TP10 to convert your .TD4 database to the current .TPDB8 format (from which you can then convert it to SQLite3),

I suppose as well .TD4 as .TPDB8 is for the "Access" engine. AFAIK it's not possible to do convert a "close-to full" TP7 "Access" database to TP 10 format, as some additional information will be stored and burst the 2 GB limit.

So I suppose the only way to go is to directly convert the .TD4 database in the correct TP4 SQLite format. I suppose I can do this by creating an appropriate converter that either accesses both databases to do a direct conversion or do a program that reads the text file exported by TP7 and converts it in a text file that can be imported by TP10. (I suppose TP10 does have a text file import, similar to what TP7 has.)

But to do this, I obviously would need to have a documentation about the fields, the databases use in both versions.

Possible but obviously very nasty reverse-engineering: I could create a small database with either version of TP (maybe for TP10 by converting the TD4 to TPDB8), do an export and compare the generated files, and then do a lot of experimenting. I do hope that Cerious does not intend to force me to do this.

What do you think ?
-Michael
Title: Database upgrade
Post by: mschnell on 2017-03-25 09:23:53
BTW. Thanks a lot for you effort helping me. But exactly at this point we already have been several months ago. I did try to convert the - at that time still a lot smaller - database to TPDB8 format and failed.

Of course the TP10 trial period has long since expired, so that I can't do any further experiments :(

Up til now I did not try to get a license for TP10, as it's not given (or even likely) that we will be able to use it.

-Michael
Title: Database upgrade
Post by: Daan van Rooijen on 2017-03-25 10:10:23
> The main purpose to use ThumbsPlus is the Galleries. We have hundreds of those. This information never is stored in the image files themselves.

There is a work-around, but it would probably cost you a full day to do it: Go into each gallery, and use the IPTC editor to assign a unique keyword to all the files inside it. For instance, if the gallery is named 'Cats', you could use the keyword 'GAL-Cats'. Do this for all your galleries.

Then create a new SQLite database in TP10, have it copy the IPTC keywords to the database while it makes thumbnails, then search for all images with keyword 'GAL-Cats' and create a gallery 'Cats' for them. Voila, that's one gallery moved to the new database! (now for all the other galleries..)

(note: to prevent that the timestamp of the image files is updated in Windows when you write to their IPTC headers, be sure to use Options | Preferences | General | 'Retain original file date/time when quick process turning or editing IPTC')

> > Another (experimental) approach would be to use a generic MDB-to-SQLite3 converter to see if that produces a working database.
 
> OK. In fact I could do such a tool myself using some programming language that supports SQL access (e.g. Python).
 
> >However, before trying that, you would first have to use TP10 to convert your .TD4 database to the current .TPDB8 format (from which you can then convert it to SQLite3)
 
> I suppose as well .TD4 as .TPDB8 is for the "Access" engine.

Yes, they're both Access databases, but they have different formats (tables and field definitions).

> AFAIK it's not possible to do convert a "close-to full" TP7 "Access" database to TP 10 format, as some additional information will be stored and burst the 2 GB limit.

Yes, good point.. :(

> But to do this, I obviously would need to have a documentation about the fields, the databases use in both versions.

In the past, I have made a description of the .TPDB8 (MDB) database structure ([link=https://www.dropbox.com/s/hvckzxujqztu1m6/ThumbsPlus-9-database-structure.pdf?dl=0]here[/link]), but I don't have one for the SQLite variety that you need and it's very likely different, because SQLite uses different field types.

For the TP7 .td4 structure, you could examine the file using [link=http://www.alexnolan.net/software/mdb_viewer_plus.htm]MDB Viewer Plus[/link].

> Possible but obviously very nasty reverse-engineering: I could create a small database with either version of TP (maybe for TP10 by converting the TD4 to TPDB8), do an export and compare the generated files, and then do a lot of experimenting. I do hope that Cerious does not intend to force me to do this.
>
> What do you think ?

If the galleries are all you really need, I think I'd go for the tedious approach of using IPTC keywords (in my first paragraph above). It seems the most simple solution and I don't think much can go wrong with it, except that the repetitive job will drive you nuts :-))
Title: Database upgrade
Post by: mschnell on 2017-03-25 10:23:18
>If the galleries are all you really need, I think I'd go for the tedious approach of using IPTC keywords (in my first paragraph above). It seems the most simple solution and I don't think much can go wrong with it, except that the repetitive job will drive you nuts :-))

I am not sure what else is used but the galleries, but I can't take the risk that something does not work after the conversion.

In fact your approach will loose the sorting of the galleries, so it does not really help :(

-Michael
Title: Database upgrade
Post by: mschnell on 2017-03-25 10:26:09
I wrote as well a mail to Cerious support, as to Laura in person. Maybe they will come up with something workable.

-Michael
Title: Database upgrade
Post by: mschnell on 2017-03-28 16:04:11
Hi Daan.

I did a program that attaches to the TP109 SQLite database and shows the "Table names". Here I get this list:

Host
sqlite_sequence
Username
UserSettings
ActiveUsers
FoundLists
FindCriteria
FoundThumbs
Gallery
Keyword
Volume
Path
Thumbnail
UserFields
UserFieldsInfo
ThumbnailGallery
ThumbnailKeyword
ThumbsPlusDatabase

I suppose I need to find the name of the fields in the tables, as e.g. "Thumbnail" obviously has fields like "width", "height", ... (see below).  


OTOH when exporting the database to a file in TP10 I get the header:

"Volume.label",
"Volume.serialno",
"Volume.vtype",
"Volume.netname",
"Volume.filesystem",

"Path.name",,

"Thumbnail.checksum",
"Thumbnail.width",
"Thumbnail.height",
"Thumbnail.horiz_res",
"Thumbnail.vert_res",
"Thumbnail.colortype",
"Thumbnail.colordepth",
"Thumbnail.gamma",
"Thumbnail.thumbnail_width",
"Thumbnail.thumbnail_height",
"Thumbnail.thumbnail_type",
"Thumbnail.thumbnail_size",
"Thumbnail.name",
"Thumbnail.metric1",
"Thumbnail.pcomment",
"Thumbnail.pbits",

"Keywords.pkeywords",

"Galleries.galleries",


This dos not seem very congruent :(  

And the PDF you pointed me to - showing the "ThumbsPlus v9 .tpdb8 (Access type) database structure" looks very different.

I can't even check whether the exported Text file is correct, as TP10 declines to import it. Supposedly the implementation of the export and/or import function is not yet correct.

Any further comment ?

Thanks a lot,
-Michael
Title: Database upgrade
Post by: Daan van Rooijen on 2017-03-28 17:25:18
Hi Michael,

I wouldn't bother with import/export, it doesn't work correctly (as you've found) and the list of fields that you get is incomplete (it only uses those that are needed to carry thumbnails, keywords and annotations over to a new database, but there are many more fields in a ThumbsPlus database).

> And the PDF you pointed me to - showing the "ThumbsPlus v9 .tpdb8 (Access type) database structure" looks very different.

Not really, your list of tables in the SQLite3 database matches most of what is in the v8/9/10 MDB database. But you're right that you'll also need to find the field definitions within each table. Maybe one of these tools could help you with that:

http://sqlitebrowser.org/
https://sqlitestudio.pl/index.rvt
http://razorsql.com/features/sqlite_table_editor.html
https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/ (for Firefox browser)

When you know the exact structure of a TP10 SQLite3 database, hopefully you can convert your .TD4 database to a .TPDB8 file in TP10, then use an MDB-2-SQLite3 conversion tool to convert the TPDB8 to TPDB8s, and use one of the tools above to work on the converted database so that its structure is correct for TP10. Or, maybe you can write your own conversion program. Either way, the end-result must have the correct tables and field definitions so that TP10 can work with it.
Title: Database upgrade
Post by: mschnell on 2017-03-29 13:04:51
Hi Daan

I was able to do a program that attaches to a TP10 SQLite database and reads the Table names and the columns names:


Host
 - idHost
 - hostname
sqlite_sequence
 - name
 - seq
Username
 - idUser
 - username
UserSettings
 - idUser
 - section
 - key
 - value
ActiveUsers
 - idActive
 - idUser
 - idHost
 - session
 - starttime
 - lasttime
 - lastactive
 - lastquery
FoundLists
 - idList
 - ListName
 - TableName
 - SQLText
FindCriteria
 - idList
 - key
 - value
FoundThumbs
 - idList
 - idThumb
 - flags
Gallery
 - idGallery
 - name
 - sortorder
 - descend
Keyword
 - idKeyword
 - keyword
Volume
 - idVol
 - vtype
 - serialno
 - maxcomplen
 - vchar
 - filesystem
 - label
 - netname
 - alias
Path
 - idPath
 - idVol
 - pathtype
 - name
Thumbnail
 - idThumb
 - idPath
 - idFiletype
 - file_time_iso
 - thumbnail_time_iso
 - taken_time_iso
 - filelength
 - checksum
 - width
 - height
 - horiz_res
 - vert_res
 - colortype
 - colordepth
 - gamma
 - thumbnail_width
 - thumbnail_height
 - thumbnail_type
 - thumbnail_size
 - idUser
 - idHost
 - name
 - rating
 - xmp
 - metric1
 - metric2
 - md5
 - thumbnail
 - annotation
 - metric3
UserFields
 - idThumbUDF
UserFieldsInfo
 - Version
 - FieldName
 - ColName
 - ColType
 - ColLength
 - FieldSeq
ThumbnailGallery
 - idGallery
 - idThumb
 - seq
ThumbnailKeyword
 - idThumb
 - idKeyword
ThumbsPlusDatabase
 - attribute
 - value
Title: Database upgrade
Post by: Daan van Rooijen on 2017-03-31 16:26:44
Yesterday I tried the 'SQLite Manager' plug-in for Firefox and I loaded a ThumbsPlus 10 .TPDB8s database into it. It showed all the tables and their fields (including the types and sizes), and a lot of additional information about the database. It also allows you to edit everything and to import .CSV data.

So, I guess there are two (rather tedious) ways forward:

1) First use TP10 to convert your TP7 database to an Access compatible .TPDB8 database. Then use an MDB-2-SQLite3 converter to convert it to SQLite3 format. And then use something like this SQLite Manager tool to edit the tables and field definitions (where necessary) to make its structure identical to a database that ThumbsPlus itself has created.

Or:

2) Use a tool like this SQLite Manager to open a .TPDB8s database that TP10 has created, and use it to import all your data in .CSV text format.

You can probably skip the thumbnail images themselves, those are easy to regenerate. What I would do to reduce the quantity of data, is first make a copy of the TP7 database, then delete all thumbnail images from it (by selecting them and pressing Shift-Del) and then Compact the database. Then use TP10 to convert it to .TPDB8 format. From there, you can either try to export the data or use a tool like MS Access or MDB Viewer Plus to extract all the tables in CSV format.

Have you heard from Laura yet? Of course, it would be much better if Cerious could provide you with an Access-to-SQLite3 converter for ThumbsPlus databases! :)
Title: Database upgrade
Post by: mschnell on 2017-03-31 17:31:55
>Yesterday I tried the 'SQLite Manager' plug-in for Firefox and I loaded a ThumbsPlus 10 ....

That is a very useful information !

>First use TP10 to convert your TP7 database to an Access compatible .TPDB8 database ...

That is impossible, as the .TPDB8 will be bigger than the .TP4 and the TP4 already is 2 GByte. (We are just trying to remove unnecessary pictures, which is a lot of work.)

If that would be possible, instead of SQLite, we also could use the TP10 feature to hold the thumbnail pictures a separate files with the .TPDB8 database.

Thanks anyway
Title: Database upgrade
Post by: Daan van Rooijen on 2017-03-31 18:22:11
> That is impossible, as the .TPDB8 will be bigger than the .TP4 and the TP4 already is 2 GByte.

You can delete the thumbnail images from the TD4 database and compact it, and then convert it to TPDB8 in TP10.
Title: Database upgrade
Post by: mschnell on 2017-04-01 02:15:26
I know. (Doing so right now...)

Just no idea how many will be necessary to be deleted.

-Michael
Title: Correction!
Post by: Daan van Rooijen on 2017-04-01 11:02:16
> I know. (Doing so right now...)
>
> Just no idea how many will be necessary to be deleted.

Oops! Michael, I made a mistake. I mistakenly thought that when you press Shift-Del, the thumbnail record (with keywords, etc) is preserved but only the thumbnail image is stripped from it. This is NOT true - the whole thumbnail record is deleted.

I don't know where this confusion comes from, I had an old recollection that it was possible to do this, but apparently it is not.

So, I guess the only way to make the database smaller without deleting thumbnail records is to set the thumbnail size to a minimum value (40x40 pixels I believe) and then re-scan the thumbnails. Also run Compact afterwards. That way, you should still get a much smaller database that still holds all the important information.
Title: Correction!
Post by: mschnell on 2017-04-01 12:58:47
>Oops! Michael, I made a mistake. I mistakenly thought that when you press Shift-Del, the thumbnail record (with keywords, etc) is preserved but only the thumbnail image is stripped from it. This is NOT true - the whole thumbnail record is deleted.

I found this out very soon. so no damage done :)

It in fact would be nice function to be able to delete only the selected Tumbnail jpg from the database and leave the rest of the record in place or - even better - convert selected jpg thumbnails to very tightly compressed versions.

But unfortunately AFAIK, TP7 does not have this feature.

This said, a maybe nice idea would be to do an external program working on the TD4 database, and deletes or converts all jpg thumbnails that are assigned to a dedicated gallery. Supposedly this is a lot easier to do than doing a .TP4 to .TPDB8s converter like I initially was up to.

-Michael
Title: Database upgrade
Post by: mschnell on 2017-04-03 19:07:12
Hi Daan

Just for the record.

I tried TP10's TD4 conversion option with creating the thumbnail pictures as separate files. This should result in a very small "Access"-type database.

After more than an hour of creating the pictures the process failed with a "database full" error. Obviously the database - which is copied before starting the conversion - is not compacted before enhancing it by the addition of the new TP10 fields.

-Michael
Title: Database upgrade
Post by: Daan van Rooijen on 2017-04-03 21:37:12
> I tried TP10's TD4 conversion option with creating the thumbnail pictures as separate files. This should result in a very small "Access"-type database.
 
> After more than an hour of creating the pictures the process failed with a "database full" error. Obviously the database - which is copied before starting the conversion - is not compacted before enhancing it by the addition of the new TP10 fields.

Sorry to hear it.. you'd think that size wouldn't be an issue if you choose to use external thumbnails.

You can probably work around this by making a copy of your TD4 database, setting its thumbnail size to 40x40p (the minimum) and rescanning your images. That should greatly reduce its size.
Title: Database upgrade
Post by: mschnell on 2017-04-04 01:15:45
>Sorry to hear it.. you'd think that size wouldn't be an issue if you choose to use external thumbnails.

Don't be. This is exactly what I expected, as the process starts b copying the TD4 database file instead of creating an empty new one.

>You can probably work around this by making a copy of your TD4 database, setting its thumbnail size to 40x40p (the minimum) and rescanning your images. That should greatly reduce its size.

I might give that a try. But to be in better control of this process, right now my strategy is to create a program that attaches to the TD4 database and resizes and/pr recompresses with a very high jpg compression rate only those thumbnails that had been included in a predefined gallery.

OTOH I doubt that the database engine in fact instantly will free the space taken by the overridden BOLBs. I suppose only a "compress" process will decrease the databases size.


-Michael
Title: Database upgrade
Post by: Daan van Rooijen on 2017-04-04 01:50:48
> OTOH I doubt that the database engine in fact instantly will free the space taken by the overridden BOLBs. I suppose only a "compress" process will decrease the databases size.

If I remember correctly, no 'compact' was necessary - but you'll find out soon enough.
Title: Re: Database upgrade
Post by: mschnell on 2021-02-05 06:33:27
After a long holdoff, waiting for confirmation that TP10 with SQLite 3 works decently fast, several Months, ago I did a Python program that performed the upgrade rather perfectly. It has some specific features that I needed but it easily could be modified to work in a more general way.
Here is the procedure:
 - Scan the files with TP10
 - Have the Python program copy the information stored inm the TP7 database to the TP1ß database

The program works something like this:
 - attach to both databases
 - Identify the files between the two  databases
 - move over information like keywords, galleries, Annotation etc partly including certain conversion for differences between TP7 and TP10-
 - the program features a simple GUI that shows states, problems, etc
 - it can be started multiple times with different  tasks to be done.

We work daily with TP10 and the upgraded database with no problems at all.

If anybody is interested please send me a note to mschnell at bschnell dot de.

-Michael