Bug 51779 - support for SQL datatype INTERVAL
Summary: support for SQL datatype INTERVAL
Status: ASSIGNED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.5.4 release
Hardware: All All
: medium enhancement
Assignee: Libreoffice user SSO
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: 2022-05-02 11:37 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:
Regression By:


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.
Comment 14 Libreoffice user SSO 2022-03-05 07:49:52 UTC
I would like to work on this easyhack!
Comment 15 Buovjaga 2022-03-05 07:58:29 UTC
(In reply to Libreoffice user SSO from comment #14)
> I would like to work on this easyhack!

Feel free to assign to yourself.
Comment 16 Lionel Elie Mamane 2022-03-05 08:52:37 UTC
Welcome. Start with comment 0, and let me know of any specific question you have. Feel free to add me as reviewer to patches on gerrit.

Note that this is "difficultyInteresting", meaning it is classified as one of the more difficult ones, and not an "easy first task" if you have never worked on LibreOffice Base.
Comment 17 Libreoffice user SSO 2022-03-05 15:53:29 UTC
Okay.
Comment 18 Libreoffice user SSO 2022-03-10 06:44:43 UTC
(In reply to Lionel Elie Mamane from comment #16)
> Welcome. Start with comment 0, and let me know of any specific question you
> have. Feel free to add me as reviewer to patches on gerrit.
> 
> Note that this is "difficultyInteresting", meaning it is classified as one
> of the more difficult ones, and not an "easy first task" if you have never
> worked on LibreOffice Base.

Hi! I am having trouble finding the files mentioned in the comment 0 listed under "Places that need to be changed: 2)" Can you help me in figuring out the places that needs changes with respect to the current code base ?
Comment 19 Buovjaga 2022-03-10 06:57:41 UTC
(In reply to Libreoffice user SSO from comment #18)
> (In reply to Lionel Elie Mamane from comment #16)
> > Welcome. Start with comment 0, and let me know of any specific question you
> > have. Feel free to add me as reviewer to patches on gerrit.
> > 
> > Note that this is "difficultyInteresting", meaning it is classified as one
> > of the more difficult ones, and not an "easy first task" if you have never
> > worked on LibreOffice Base.
> 
> Hi! I am having trouble finding the files mentioned in the comment 0 listed
> under "Places that need to be changed: 2)" Can you help me in figuring out
> the places that needs changes with respect to the current code base ?

You did not specify which files you were having trouble with, but I checked and some had incorrect (changed over time?) paths. Keep in mind that you can always use the find command, like

find . -name 'FValue.hxx'

Correct paths for the mentioned files:

include/connectivity/FValue.hxx
connectivity/source/commontools/FValue.cxx
connectivity/source/commontools/DateConversion.cxx
Comment 20 Lionel Elie Mamane 2022-03-13 19:39:22 UTC
Pragat, your email provider rejects my answer to your email as suspected spam... Here's your answer:

offapi/com/sun/star/sdbc/DataType.idl just defines constants to refer to a type, that the rest of the code uses to refer to the datatype. These constants are then used in the C++ code to refer to that type, usually with a switch() structure. It is up to the each code snippet making use of these constants to actually make sense of it, there is no central place that does the mapping. Each place where it is used has to "understand" that DATE is a date and how to treat a date, that NUMERIC is a numeric and how to treat a numeric, etc.

See for example
connectivity/source$ emacs commontools/FValue.cxx

Just pick an unused constant, that is unused both in Java's JDBC
https://docs.oracle.com/javase/8/docs/api/java/sql/JDBCType.html
and in LibreOffice's current DataType.idl
Comment 21 Libreoffice user SSO 2022-03-14 04:51:34 UTC
Thanks a lot. Now this is very clear. By which nickname you go in the IRC? It'll be easier to communicate there ?
Comment 22 Buovjaga 2022-03-14 06:22:41 UTC
Pragat: you could try this that should make Lionel's emails reach you: https://www.lifewire.com/how-to-whitelist-a-sender-or-domain-in-gmail-1172106
Comment 23 Libreoffice user SSO 2022-03-14 08:24:38 UTC
I have added a filter to never send any emails from Lionel's email ID to spam. Now, Lionel's emails should reach me without any issue.