User forums now online! Please see the latest post in the Announcements forum for more information.

Author Topic: Database upgrade  (Read 1127 times)

0 Members and 1 Guest are viewing this topic.

mschnell

  • Full Member
  • ***
  • Posts: 97
    • View Profile
Database upgrade
« 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

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 813
    • View Profile
Database upgrade
« Reply #1 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.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

mschnell

  • Full Member
  • ***
  • Posts: 97
    • View Profile
Database upgrade
« Reply #2 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

mschnell

  • Full Member
  • ***
  • Posts: 97
    • View Profile
Database upgrade
« Reply #3 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

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 813
    • View Profile
Database upgrade
« Reply #4 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 :-))
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

mschnell

  • Full Member
  • ***
  • Posts: 97
    • View Profile
Database upgrade
« Reply #5 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

mschnell

  • Full Member
  • ***
  • Posts: 97
    • View Profile
Database upgrade
« Reply #6 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

mschnell

  • Full Member
  • ***
  • Posts: 97
    • View Profile
Database upgrade
« Reply #7 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

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 813
    • View Profile
Database upgrade
« Reply #8 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.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

mschnell

  • Full Member
  • ***
  • Posts: 97
    • View Profile
Database upgrade
« Reply #9 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

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 813
    • View Profile
Database upgrade
« Reply #10 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! :)
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

mschnell

  • Full Member
  • ***
  • Posts: 97
    • View Profile
Database upgrade
« Reply #11 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

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 813
    • View Profile
Database upgrade
« Reply #12 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.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

mschnell

  • Full Member
  • ***
  • Posts: 97
    • View Profile
Database upgrade
« Reply #13 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

Daan van Rooijen

  • Global Moderator
  • Sr. Member
  • *****
  • Posts: 813
    • View Profile
Correction!
« Reply #14 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.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.