Libreoffice as view for sqldb

[Ty Mayn]

  I am slowly learning JPTagger and appreciate coaching from 'Elmar in recent posts

Now I want to get the benefit of seeing the database in table format through Libreoffice

at various stages as I tag a small number of photos.

 

   This will hopefully help me understand what fields and data reside in the database compared to the XMP sidecars

I am using the instructions from Elmar

https://jphototagger.org/usr/open_with_libreoffice_base.html

  I can tell that Libreoffice 5 has slightly different menu for configuring Java and the database

I did add a class path and archive pointing to the file hsqldb.jar in the install  lib directory

 I believe I entered correct path in the sample URL links provided by Elmar

 hoiwever the Test class button failed to find/install the JDBC class path cited as

    

JDBC Driver Class: org.hsqldb.jdbcDriver (Apply Test Button, it should not display an error message)

 

 This failed in both Linux and Windows systems running JPTag and Libreoffice.

 Not sure how much it will help me to visualize the database elements but cant get there without some help

  I also wonder how many databases there are in JPT in addition to the main "database,data"  Aew the thumbnails  some derived sqldb

 with a very simple index.  And the " HsqlDAtabase  in the install directory, is that some sort of backup template for rebuilding?

htanks

Ty

[Elmar Baumann]

Am 10.01.19 um 08:04 schrieb Ty Mayn:

> https://jphototagger.org/usr/open_with_libreoffice_base.html

I updated that page to reflect the current version of LibreOffice and
added screen shots. It works as expected, when the correct hsqldb.jar is
(really) in the class path and the database properties do not contain
errors.

You can see the database layout UML:
https://jphototagger.org/dev/img/database.png

>   I also wonder how many databases there are in JPT in addition to the
> main "database,data"

There are not many databases. The HSQL database consists of multiple
files. The thumbnails are stored separately in a MapDB database
(http://www.mapdb.org/) which is not browsable with LibreOffice or any
other tool I know. But you can browse indirectly the thumbnails in
JPhotoTagger via "Window > Database Browser".

[Ty Mayn]

Thanks for instruction and pictures.  It turns out my spelling and entries were correct for the 3 parts

the class path/archive to the jar file

the URL to working database

the  class label

  I think it was failing because I did not do the restart of Libreoffice properly

This restart seems to do the acti of registering the driver with the " Driver Manager"

Or maybe the  CLass button is doing the registering/  But at least class driver is found when the button is pressed

     It has been helpful to see the parts of the database objects in row fashion and the CHART also helps

I know a little more and curiosity causes me to ask 

   What is the format of the 13 digit Long integer that represents 

[Ty Mayn]

 continuation of post:>>

I know a little more and curiosity causes me to ask 

   What is the format of the 13 digit Long integer that represents

 Last Modified, Last Modified XMP  and also for Exif Last Modified Timestamp

   I checked out the  regular date format fields for last modified and tried converting to  them

Julian dates which were not even close to these mysterious numbers.

   How are these pieces of data used?  Since they are embedded and not subject to change

within JPT then they are good  checks for identity but not helpful for identifying changes to XMP

Thanks again TY

On Thursday, January 10, 2019 at 2:04:06 AM UTC-5, Ty Mayn wrote:

[Elmar Baumann]

Am 11.01.19 um 07:50 schrieb Ty Mayn:

>    What is the format of the 13 digit Long integer that represents
>  Last Modified, Last Modified XMP  and also for Exif Last Modified Timestamp
>    I checked out the  regular date format fields for last modified and
> tried converting to  them
> Julian dates which were not even close to these mysterious numbers.

It's the last modification time of the files, according to the Java documentation:

A long value representing the time the file was last modified, measured in milliseconds since the epoch (00:00:00 GMT, January 1, 1970).

For human readability, the milliseconds from begin of 1970 to the file modification time have to be converted into a timestamp. With JPhotoTagger's HSQLDB version that can be done via:

SELECT
FILENAME,
SIZE_IN_BYTES,
"org.hsqldb.HsqlDateTime.getTimestamp"(LASTMODIFIED) AS image_file_last_modified,
"org.hsqldb.HsqlDateTime.getTimestamp"(XMP_LASTMODIFIED) AS xmp_sidecar_file_last_modified
FROM FILES

When trying to do this with LibreOffice Base, it results in a syntax error message by LibreOffice but not from the database driver. To get proper results, you can use a more advanced tool such as SQuirreL SQL Client (http://squirrel-sql.sourceforge.net/). I attached a screenshot with the result of that query (SQuirreL SQL).

Please note, that accessing JPhotoTagger's database via other clients such as LibreOffice or SQuirreL SQL is not included in JPhotoTagger's support.

[Elmar Baumann]

There is an easy way to execute SQL within JPhotoTagger: Tools > Database SQL Query. There you can run the SQL