Bug 51779 - support for SQL datatype INTERVAL
Summary: support for SQL datatype INTERVAL
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.5.4 release
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: difficultyInteresting, easyHack, skillCpp, skillSql
Depends on: 51523
Blocks: Database-Import
  Show dependency treegraph
 
Reported: 2012-07-06 02:50 UTC by Lionel Elie Mamane
Modified: 2020-04-18 14:42 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Lionel Elie Mamane 2012-07-06 02:50:47 UTC
+++ This bug was initially created as a clone of Bug #51523 +++

LibreOffice does not support the SQL INTERVAL datatype. That is the datatype to store time *durations*, rather than a point in time (like DATE, TIME, TIMESTAMP).

Our embedded HSQLDB 1.8 also does not support it, but that is no reason not to support it cleanly when connecting to database backends that *do* support it.

Places that need to be changed:

1) offapi/com/sun/star/sdbc/DataType.idl needs a value for INTERVAL

2) connectivity/inc/connectivity/FValue.hxx: class ORowSetValue needs to be
adapted to handle values of that type. Also connectivity
source/commontools/FValue.cxx and undoubtedly many other places, such as
source/commontools/DateConversion.cxx, etc.
Comment 1 Jochen 2012-08-30 07:06:21 UTC Comment hidden (no-value)
Comment 2 Björn Michaelsen 2013-10-04 18:46:40 UTC Comment hidden (no-value)
Comment 3 Alex Thurgood 2015-01-03 17:39:13 UTC Comment hidden (no-value)
Comment 4 Robinson Tryon (qubit) 2015-12-13 13:24:14 UTC Comment hidden (no-value, obsolete)
Comment 5 Robinson Tryon (qubit) 2016-02-18 14:52:14 UTC Comment hidden (no-value, obsolete)
Comment 6 kerem 2016-06-06 17:38:01 UTC
Hi,

I could not add INTERVAL selection to datatypes list. Because I could not find associated .ui file. How can i find? Can you help me?

Thank you.
Comment 7 Lionel Elie Mamane 2016-06-06 17:41:33 UTC
(In reply to kerem from comment #6)

> I could not add INTERVAL selection to datatypes list. Because I could not
> find associated .ui file. How can i find? Can you help me?

Before starting to change UI, it needs to be supported in the backend. See comment 0, "places that need to be changed". Did you do that already?
Comment 8 kerem 2016-06-06 17:52:41 UTC
(In reply to Lionel Elie Mamane from comment #7)

> Before starting to change UI, it needs to be supported in the backend. See
> comment 0, "places that need to be changed". Did you do that already?

Yes, i changed in necessary places. But i am not sure. Because i still haven't find INTERVAL data type. Shall I commit my changes?
Comment 9 Gökay ŞATIR 2020-04-18 11:41:54 UTC
Hello,

Latest versions of HSQLDB seems to support interval data types. Should it be upgraded first?
Comment 10 Buovjaga 2020-04-18 12:14:50 UTC
(In reply to Gökay ŞATIR from comment #9)
> Hello,
> 
> Latest versions of HSQLDB seems to support interval data types. Should it be
> upgraded first?

No, we want to remove embedded HSQLDB completely in favour of Firebird.
Comment 11 Gökay ŞATIR 2020-04-18 13:36:39 UTC
Is there an ongoing discussion about which db system to choose? Like this one:
https://ask.libreoffice.org/en/question/88866/base-firebird-vs-hsqldb-embedded-database/

Or is the decision is made already?

I think SQLite could again be the candidate. Its latest versions support multiple reads at the same time, it handles multiple connections well.

SQLite just needs a wrapper for data types, which can be implemented on top of it.

Also, it has amalgamation, i personally find that feature valuable.
Comment 12 Buovjaga 2020-04-18 14:21:01 UTC
(In reply to Gökay ŞATIR from comment #11)
> Is there an ongoing discussion about which db system to choose? Like this
> one:
> https://ask.libreoffice.org/en/question/88866/base-firebird-vs-hsqldb-
> embedded-database/
> 
> Or is the decision is made already?
> 
> I think SQLite could again be the candidate. Its latest versions support
> multiple reads at the same time, it handles multiple connections well.
> 
> SQLite just needs a wrapper for data types, which can be implemented on top
> of it.
> 
> Also, it has amalgamation, i personally find that feature valuable.

There is no ongoing discussion. The decision was made long ago. Since then, there have been multiple Google Summer of Code projects and also TDF contracts to bring embedded Firebird into a production-ready status.

I highly recommend you to contribute to the meta reports:
https://bugs.documentfoundation.org/showdependencytree.cgi?id=116970&hide_resolved=1
https://bugs.documentfoundation.org/showdependencytree.cgi?id=116968&hide_resolved=1

There are some fundamental data type differences between HSQLDB and Firebird, but the upcoming Firebird 4.0 will solve a couple of these and hopefully a future version will solve more. This would make it easier to migrate existing database files from HSQLDB to Firebird.

https://firebirdsql.org/file/community/conference-2019/1_firebird_on_the_road_from4_to_5.pdf

Hopefully we will see 4.0 during the second quarter of 2020.
Comment 13 Gökay ŞATIR 2020-04-18 14:42:47 UTC
Hello,

Thank you for your comment. I started to read bug reports and will try to debug & contribute to the progress.