Restore support for date/time constants in KEXI SQL
ClosedPublic

Authored by staniek on Dec 3 2018, 4:12 PM.

Details

Summary
  • Add Date/Time classes for precise type support
  • Add autotests for the KDb date/time classes
  • Add support for the Date/Time constants in the SQL parser
  • improve lexer too
  • obsolete previous approach for SQL default date support
  • and for SQLite, add new APIs for converting date/times

https://community.kde.org/Kexi/Plugins/Queries/SQL_Constants

  • Add autotests for date/time related KDBSQL statements

BUG:393094

KDb VERSION -> 3.2.0

Test Plan

Precondition: KDb branch: 393094-date-constants against 3.2 branch, KEXI branch 393094-date-constants (D17847)

0. Run KDb autotests
Expected: 100% passes

  1. Open a new design in KEXI Query Designer.

Note: Visual designer does not support these constants, use the SQL view.

  1. Type a number of statements involving Date, Time and Date/Time constants

You can use test cases from autotests/parser/data/statements.txt, sections:

  • Date Constants (KDbSQL EXTENSION)
  • Time Constants (KDbSQL EXTENSION)
  • Date/Time Constants (KDbSQL EXTENSION)

Use the Check Query button to try both valid and invalid constants. For example "SELECT #12:13:01#" should work and "SELECT #2018-11-37#" should be rejected as invalid constant.

Expected: Valid constants should be accepted and then data view should work, there should be warning for invalid constants.
Expected: Saving of both valid and invalid statements should be possible in the SQL view.

  1. Create table with columns of type date, time and date/time and add some records.
  1. Open a new design in Query SQL Designer, create statements involving both constants and columns e.g. SELECT #12:13:01# AS expr1, timecolumn FROM table where table.timecolumn < #2018-11-37#

Expected: valid statement, the Data view works.

  1. Test all of the above for SQLite, pgsql, mysql.

Diff Detail

Repository
R15 KDb
Branch
393094-date-constants
Lint
No Linters Available
Unit
No Unit Test Coverage
Build Status
Buildable 5675
Build 5693: arc lint + arc unit
staniek created this revision.Dec 3 2018, 4:12 PM
Restricted Application added a project: KDb. · View Herald TranscriptDec 3 2018, 4:12 PM
Restricted Application added a subscriber: Kexi-Devel-list. · View Herald Transcript
staniek requested review of this revision.Dec 3 2018, 4:12 PM
staniek edited the test plan for this revision. (Show Details)Dec 3 2018, 4:15 PM
staniek added reviewers: dczechowicz, wkosowicz.
staniek edited the summary of this revision. (Show Details)
staniek edited the test plan for this revision. (Show Details)
staniek updated this revision to Diff 46800.Dec 3 2018, 4:23 PM
  • Add autotests for date/time related KDBSQL statements
staniek edited the test plan for this revision. (Show Details)Dec 3 2018, 4:24 PM
staniek edited the test plan for this revision. (Show Details)Dec 3 2018, 5:36 PM
staniek updated this revision to Diff 47213.Dec 9 2018, 8:18 PM
  • Merge remote-tracking branch 'origin/3.1' into 393094-date-constants
staniek added inline comments.Dec 10 2018, 1:57 PM
src/parser/generated/KDbToken.cpp
182

Please ignore all files in the generated/ folder.

staniek updated this revision to Diff 47515.Dec 13 2018, 1:54 PM
  • Version 3.2.0
  • Merge remote-tracking branch 'origin/3.2' into 393094-date-constants
staniek edited the summary of this revision. (Show Details)Dec 13 2018, 1:54 PM
staniek edited the test plan for this revision. (Show Details)
staniek edited the summary of this revision. (Show Details)
This revision was not accepted when it landed; it landed in state Needs Review.Dec 13 2018, 1:56 PM
Closed by commit R15:7d4dad4efb18: Version 3.2.0 (authored by staniek). · Explain Why
This revision was automatically updated to reflect the committed changes.
staniek reopened this revision.Dec 13 2018, 4:14 PM
staniek updated this revision to Diff 47525.Dec 13 2018, 4:15 PM
  • Merge branch '3.2' into 393094-date-constants
This revision was not accepted when it landed; it landed in state Needs Review.Dec 13 2018, 4:21 PM
This revision was automatically updated to reflect the committed changes.
staniek reopened this revision.Dec 17 2018, 10:47 PM
piggz added a comment.Dec 18 2018, 9:50 PM

I used arc patch D17336 on kdb
I get errors like
org.kde.kdb.core: error: syntax error, unexpected $undefined, expecting $end
org.kde.kdb.core: at character 8 near tooken "#"
org.kde.kdb.core: KDbEscapedString:"select #2018-05-23#"
org.kde.kdb.core: " ^"

staniek updated this revision to Diff 47804.Dec 18 2018, 10:57 PM
  • GIT_SILENT KDb VERSION 3.1.1
  • Add Date/Time classes for precise type support
  • Add autotests for the KDb date/time classes
  • Add support for the Date/Time constants in the SQL parser
  • Add autotests for date/time related KDBSQL statements
  • Merge remote-tracking branch 'origin/3.1' into 393094-date-constants
  • Version 3.2.0
  • Merge remote-tracking branch 'origin/3.2' into 393094-date-constants
  • Merge branch '3.2' into 393094-date-constants

@piggz Sorry, updated now, I had to type arc diff --update D17336 --no-amend.

Feel free to just switch to branch 393094-date-constants which I maintain for this review - no 'arc patch' will be needed.

piggz added a comment.Dec 25 2018, 9:05 PM

I tested a simple expression, such as
select #date# as expr1
and it worked
But a more complex query such as
SELECT mi_band_activity.*, timestamp_dt FROM mi_band_activity where timestamp_dt > #23-May-2018#
does not work, gives the following output:
org.kde.kdb.core: checking variable name: "mi_band_activity.*"
org.kde.kdb.core: checking variable name: "timestamp_dt"
org.kde.kdb.core: checking variable name: "timestamp_dt"
org.kde.kdb.core: error:
org.kde.kdb.core: at character 95 near tooken "#"
org.kde.kdb.core: KDbEscapedString:"SELECT mi_band_activity.*, timestamp_dt FROM mi_band_activity where timestamp_dt > #23-05-2018#"

I tested a simple expression, such as
select #date# as expr1
and it worked
But a more complex query such as
SELECT mi_band_activity.*, timestamp_dt FROM mi_band_activity where timestamp_dt > #23-May-2018#

@piggz

Hi Adam,
From https://community.kde.org/Kexi/Plugins/Queries/SQL_Constants#Date_constants
"MSA's format such as #24 Dec 2014# is not supported"

does not work, gives the following output:
org.kde.kdb.core: checking variable name: "mi_band_activity.*"
org.kde.kdb.core: checking variable name: "timestamp_dt"
org.kde.kdb.core: checking variable name: "timestamp_dt"
org.kde.kdb.core: error:
org.kde.kdb.core: at character 95 near tooken "#"
org.kde.kdb.core: KDbEscapedString:"SELECT mi_band_activity.*, timestamp_dt FROM mi_band_activity where timestamp_dt > #23-05-2018#"

Such things work for me, e.g. :
"SELECT #12:13:01# AS expr1, #2018-11-27# AS expr2, date, time, dt FROM date AS d WHERE d.date < #1998-10-10#"

piggz added a comment.Dec 27 2018, 7:04 PM

Following BT when doing the action:
Open database
Right click on query and select Design
When prompted that cannot open in design view, select option to open in text view
Crash

#0 0x00007ffff530f08b in raise () from /lib64/libc.so.6
#1 0x00007ffff52f84e9 in abort () from /lib64/libc.so.6
#2 0x00007ffff53519a7 in libc_message () from /lib64/libc.so.6
#3 0x00007ffff5357e9c in malloc_printerr () from /lib64/libc.so.6
#4 0x00007ffff5359900 in _int_free () from /lib64/libc.so.6
#5 0x00007ffff7911e6c in KDbObject::~KDbObject (this=0x7a4940,
in_chrg=<optimized out>) at /home/piggz/src/kdb/src/KDbObject.cpp:32
#6 0x00007ffff7d76cec in KexiWindow::Private::setSchemaObject (this=0x74d120, data=0x0) at /home/piggz/src/kexi/src/core/KexiWindow.cpp:74
#7 0x00007ffff7d76c4f in KexiWindow::Private::~Private (this=0x74d120, in_chrg=<optimized out>) at /home/piggz/src/kexi/src/core/KexiWindow.cpp:68
#8 0x00007ffff7d735c9 in KexiWindow::~KexiWindow (this=0xa42c70,
in_chrg=<optimized out>) at /home/piggz/src/kexi/src/core/KexiWindow.cpp:166
#9 0x00007ffff7d73640 in KexiWindow::~KexiWindow (this=0xa42c70, __in_chrg=<optimized out>) at /home/piggz/src/kexi/src/core/KexiWindow.cpp:168
#10 0x00007ffff7d863f0 in KexiPart::Part::openInstance (this=0xa13e30, parent=0x7df120, item=0xbd34b0, viewMode=Kexi::DesignViewMode, staticObjectArgs=0x0)

at /home/piggz/src/kexi/src/core/kexipart.cpp:296

#11 0x00007ffff7d69274 in KexiProject::openObject (this=0xb531a0, parent=0x7df120, item=0xbd34b0, viewMode=Kexi::DesignViewMode, staticObjectArgs=0x0)

at /home/piggz/src/kexi/src/core/kexiproject.cpp:993

#12 0x00007ffff7edf871 in KexiMainWindow::openObject (this=0x556f00, item=0xbd34b0, viewMode=Kexi::DesignViewMode, openingCancelled=0x7fffffffb76f, staticObjectArgs=0x0,

errorMessage=0x0) at /home/piggz/src/kexi/src/main/KexiMainWindow.cpp:3404

#13 0x00007ffff7f4a5e8 in KexiMainWindow::openObject (this=0x556f00, item=0xbd34b0, viewMode=Kexi::DesignViewMode, staticObjectArgs=0x0)

at /home/piggz/src/kexi-build/src/main/keximain_autogen/EWIEGA46WW/../../../../../kexi/src/main/KexiMainWindow.h:495

#14 0x00007ffff7f443fd in KexiMainWindow::qt_static_metacall (_o=0x556f00, _c=QMetaObject::InvokeMetaMethod, _id=59, _a=0x7fffffffba70)

at /home/piggz/src/kexi-build/src/main/keximain_autogen/EWIEGA46WW/moc_KexiMainWindow.cpp:696

#15 0x00007ffff5e7c0ab in QMetaObject::activate(QObject*, int, int, void**) () from /usr/lib64/libQt5Core.so.5
#16 0x00007ffff529d3e0 in KexiProjectNavigator::openItem (this=0xb96c90, _t1=0xbd34b0, _t2=Kexi::DesignViewMode)

at /home/piggz/src/kexi-build/src/widget/kexiextendedwidgets_autogen/RU47RH5TZY/moc_KexiProjectNavigator.cpp:550

#17 0x00007ffff5256ca8 in KexiProjectNavigator::slotDesignObject (this=0xb96c90) at /home/piggz/src/kexi/src/widget/navigator/KexiProjectNavigator.cpp:420
#18 0x00007ffff529cd5a in KexiProjectNavigator::qt_static_metacall (_o=0xb96c90, _c=QMetaObject::InvokeMetaMethod, _id=21, _a=0x7fffffffbcb0)

at /home/piggz/src/kexi-build/src/widget/kexiextendedwidgets_autogen/RU47RH5TZY/moc_KexiProjectNavigator.cpp:420

#19 0x00007ffff5e7c0ab in QMetaObject::activate(QObject*, int, int, void) () from /usr/lib64/libQt5Core.so.5
#20 0x00007ffff6a41ff2 in QAction::triggered(bool) () from /usr/lib64/libQt5Widgets.so.5
#21 0x00007ffff6a445f0 in QAction::activate(QAction::ActionEvent) () from /usr/lib64/libQt5Widgets.so.5
#22 0x00007ffff6bb665c in ?? () from /usr/lib64/libQt5Widgets.so.5
#23 0x00007ffff6bbdc30 in ?? () from /usr/lib64/libQt5Widgets.so.5
#24 0x00007ffff6bbec3b in QMenu::mouseReleaseEvent(QMouseEvent*) () from /usr/lib64/libQt5Widgets.so.5
#25 0x00007ffff6a87c08 in QWidget::event(QEvent*) () from /usr/lib64/libQt5Widgets.so.5
#26 0x00007ffff6bc12bb in QMenu::event(QEvent*) () from /usr/lib64/libQt5Widgets.so.5
#27 0x00007ffff6a48591 in QApplicationPrivate::notify_helper(QObject*, QEvent*) () from /usr/lib64/libQt5Widgets.so.5
#28 0x00007ffff6a4fd97 in QApplication::notify(QObject*, QEvent*) () from /usr/lib64/libQt5Widgets.so.5
#29 0x00007ffff5e52359 in QCoreApplication::notifyInternal2(QObject*, QEvent*) () from /usr/lib64/libQt5Core.so.5
#30 0x00007ffff6a4f0a0 in QApplicationPrivate::sendMouseEvent(QWidget*, QMouseEvent*, QWidget*, QWidget*, QWidget
, QPointer<QWidget>&, bool, bool) ()

from /usr/lib64/libQt5Widgets.so.5

#31 0x00007ffff6aa2b4f in ?? () from /usr/lib64/libQt5Widgets.so.5
#32 0x00007ffff6aa55cf in ?? () from /usr/lib64/libQt5Widgets.so.5
#33 0x00007ffff6a48591 in QApplicationPrivate::notify_helper(QObject*, QEvent*) () from /usr/lib64/libQt5Widgets.so.5
#34 0x00007ffff6a4fb50 in QApplication::notify(QObject*, QEvent*) () from /usr/lib64/libQt5Widgets.so.5
#35 0x00007ffff5e52359 in QCoreApplication::notifyInternal2(QObject*, QEvent*) () from /usr/lib64/libQt5Core.so.5
#36 0x00007ffff641a55d in QGuiApplicationPrivate::processMouseEvent(QWindowSystemInterfacePrivate::MouseEvent*) () from /usr/lib64/libQt5Gui.so.5

piggz added a comment.Dec 27 2018, 7:05 PM

P.S. i got query to work by arranging date as yyyy-mm-dd .... i had used dd-mm-yyyy

Following BT when doing the action:
Open database
Right click on query and select Design
When prompted that cannot open in design view, select option to open in text view
Crash

Confirmed, thanks @piggz.

staniek edited the test plan for this revision. (Show Details)Dec 28 2018, 9:28 PM

@piggz Done in kexi.git: D17847.

Query Design view will still issue error as we have general TODO there to use parser in the criteria cells. But no crash.

piggz added inline comments.Dec 31 2018, 7:09 PM
src/tools/KDbUtils.cpp
147

whats this QDate(0,1,2) ?

piggz accepted this revision.Dec 31 2018, 7:09 PM
This revision is now accepted and ready to land.Dec 31 2018, 7:09 PM
staniek added inline comments.Dec 31 2018, 8:20 PM
src/tools/KDbUtils.cpp
147

It's not part of this patch but here's the answer: https://phabricator.kde.org/source/kdb/browse/master/src/tools/KDbUtils.h$57

From Qt docs:

QTime::QTime()
Constructs a null time object. A null time can be a QTime(0, 0, 0, 0) (i.e., midnight) object, except that isNull() returns true and isValid() returns false.

This commit describes the workaround: https://phabricator.kde.org/R15:21e8a3da84165d610c7ba472e43b60e1fc43b172

Otherwise in quite a few places we would have to check not only isNull but also isValid to know time valid is really null. That would be error-prone.

PS: (related to this entire Date patch): The new classes KDb(Date)(Time) go much further because they are part of the parse tree. They can properly store invalid date/time elements of any kind so there's no loosing of information. This e.g. allows the user to reopen SQL and edit it. With regular Qt classes invalid values, say, in "SELECT #2018-12-37#" would turn to "SELECT NULL" or "SELECT <INVALID_DATE>" when query text is reopened.

staniek closed this revision.Jan 4 2019, 9:27 PM