Bug 393094 - Restore support for date/time constants in KEXI SQL
Summary: Restore support for date/time constants in KEXI SQL
Status: CLOSED FIXED
Alias: None
Product: KEXI
Classification: Applications
Component: Queries (show other bugs)
Version: 3.1.0
Platform: Compiled Sources All
: HI normal
Target Milestone: 3.2
Assignee: Jarosław Staniek
URL: https://phabricator.kde.org/T10134
Keywords:
Depends on:
Blocks:
 
Reported: 2018-04-13 09:10 UTC by Jarosław Staniek
Modified: 2019-01-04 21:40 UTC (History)
2 users (show)

See Also:
Latest Commit:
Version Fixed In: 3.2.0 Beta


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Jarosław Staniek 2018-04-13 09:10:44 UTC
Previously KEXI (KDb SQL parser) accepted text argument such as '2018-04-13' where date type is expected, e.g. in comparisons. Currently, since ~ 3.x it detects incompatible type, text vs date.

The same applies to date/time and time.

To reproduce: assuming t.d is of date type, enter SQL query such as "SELECT d FROM t WHERE d >= '2018-04-12'.
Result: incompatible types
Expected: any convenient way to write such expressions.

Forum thread:
Based on https://forum.kde.org/viewtopic.php?f=221&t=119757&p=397275#p397275

Restore support for date constants, based on ideas from the thread. Keep the strict type check though.

Visual query designer should support date constants too.
Comment 1 Jarosław Staniek 2018-09-10 21:20:46 UTC
Design page: https://community.kde.org/Kexi/Plugins/Queries/SQL_Constants
Comment 2 Jarosław Staniek 2018-12-03 16:24:27 UTC
Git commit 28e63b42584f441a584cbef35d1ae7161b5fba41 by Jaroslaw Staniek.
Committed on 03/12/2018 at 15:14.
Pushed by staniek into branch '393094-date-constants'.

Add autotests for the KDb date/time classes

GIT_SILENT

M  +1    -0    autotests/CMakeLists.txt
A  +96   -0    autotests/DateTimeTest.cpp     [License: LGPL (v2+)]
A  +42   -0    autotests/DateTimeTest.h     [License: LGPL (v2+)]

https://commits.kde.org/kdb/28e63b42584f441a584cbef35d1ae7161b5fba41
Comment 3 Jarosław Staniek 2018-12-03 16:24:27 UTC
Git commit 42ecae68aa5585042a1b07c270891b3dfe5da520 by Jaroslaw Staniek.
Committed on 03/12/2018 at 15:10.
Pushed by staniek into branch '393094-date-constants'.

Add Date/Time classes for precise type support

M  +2    -0    src/CMakeLists.txt
A  +446  -0    src/KDbDateTime.cpp     [License: LGPL (v2+)]
A  +475  -0    src/KDbDateTime.h     [License: LGPL (v2+)]

https://commits.kde.org/kdb/42ecae68aa5585042a1b07c270891b3dfe5da520
Comment 4 Jarosław Staniek 2018-12-03 16:24:27 UTC
Git commit d69a8f0d80e125b27bae7b66ecfd6a26fd37d96f by Jaroslaw Staniek.
Committed on 03/12/2018 at 16:04.
Pushed by staniek into branch '393094-date-constants'.

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

M  +108  -4    src/KDb.cpp
M  +136  -15   src/KDb.h
M  +16   -18   src/KDbDriver.cpp
M  +43   -3    src/KDbDriver.h
M  +2    -2    src/KDbField.cpp
M  +1    -1    src/KDbQuerySchema.cpp
M  +7    -2    src/KDb_p.h
M  +2    -3    src/drivers/postgresql/PostgresqlCursor.cpp
M  +1    -1    src/drivers/sqlite/SqliteCursor.cpp
M  +2    -2    src/drivers/sqlite/SqlitePreparedStatement.cpp
M  +20   -8    src/expression/KDbConstExpression.cpp
M  +7    -4    src/parser/KDbParser.cpp
M  +1    -1    src/parser/KDbParser.h
M  +150  -2    src/parser/KDbSqlParser.y
M  +52   -1    src/parser/KDbSqlScanner.l
M  +4    -1    src/parser/generate_parser_code.sh
M  +4    -0    src/parser/generated/KDbToken.cpp
M  +5    -1    src/parser/generated/KDbToken.h
M  +566  -356  src/parser/generated/sqlparser.cpp
M  +13   -3    src/parser/generated/sqlparser.h
M  +390  -295  src/parser/generated/sqlscanner.cpp
M  +1    -1    src/tools/KDbUtils.cpp

https://commits.kde.org/kdb/d69a8f0d80e125b27bae7b66ecfd6a26fd37d96f
Comment 5 Jarosław Staniek 2018-12-03 16:24:27 UTC
Git commit 782a84101a71fbbdc2a23a021d4087fa6d963922 by Jaroslaw Staniek.
Committed on 03/12/2018 at 16:04.
Pushed by staniek into branch '393094-date-constants'.

Add autotests for date/time related KDBSQL statements

GIT_SILENT

M  +44   -15   autotests/ExpressionsTest.cpp
M  +247  -2    autotests/parser/data/statements.txt

https://commits.kde.org/kdb/782a84101a71fbbdc2a23a021d4087fa6d963922
Comment 6 Jarosław Staniek 2019-01-04 21:27:17 UTC
Git commit 5b700dfd7c0d266f2da0fc914d1b0c0fe443d287 by Jaroslaw Staniek.
Committed on 04/01/2019 at 21:03.
Pushed by staniek into branch '3.2'.

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

M  +108  -4    src/KDb.cpp
M  +136  -15   src/KDb.h
M  +16   -18   src/KDbDriver.cpp
M  +43   -3    src/KDbDriver.h
M  +2    -2    src/KDbField.cpp
M  +1    -1    src/KDbQuerySchema.cpp
M  +7    -2    src/KDb_p.h
M  +2    -3    src/drivers/postgresql/PostgresqlCursor.cpp
M  +1    -1    src/drivers/sqlite/SqliteCursor.cpp
M  +2    -2    src/drivers/sqlite/SqlitePreparedStatement.cpp
M  +20   -8    src/expression/KDbConstExpression.cpp
M  +7    -4    src/parser/KDbParser.cpp
M  +1    -1    src/parser/KDbParser.h
M  +150  -2    src/parser/KDbSqlParser.y
M  +52   -1    src/parser/KDbSqlScanner.l
M  +4    -1    src/parser/generate_parser_code.sh
M  +4    -0    src/parser/generated/KDbToken.cpp
M  +5    -1    src/parser/generated/KDbToken.h
M  +566  -356  src/parser/generated/sqlparser.cpp
M  +13   -3    src/parser/generated/sqlparser.h
M  +390  -295  src/parser/generated/sqlscanner.cpp
M  +1    -1    src/tools/KDbUtils.cpp

https://commits.kde.org/kdb/5b700dfd7c0d266f2da0fc914d1b0c0fe443d287
Comment 7 Jarosław Staniek 2019-01-04 21:27:17 UTC
Git commit 81ad5897692ccbc77f0b300602cefbf08e3ff070 by Jaroslaw Staniek.
Committed on 04/01/2019 at 21:03.
Pushed by staniek into branch '3.2'.

Add autotests for the KDb date/time classes

GIT_SILENT

M  +1    -0    autotests/CMakeLists.txt
A  +96   -0    autotests/DateTimeTest.cpp     [License: LGPL (v2+)]
A  +42   -0    autotests/DateTimeTest.h     [License: LGPL (v2+)]

https://commits.kde.org/kdb/81ad5897692ccbc77f0b300602cefbf08e3ff070
Comment 8 Jarosław Staniek 2019-01-04 21:27:17 UTC
Git commit c2fcf8a68170cb0df7757375dc35cef0896dcc15 by Jaroslaw Staniek.
Committed on 04/01/2019 at 21:03.
Pushed by staniek into branch '3.2'.

Add autotests for date/time related KDBSQL statements

GIT_SILENT

M  +44   -15   autotests/ExpressionsTest.cpp
M  +247  -2    autotests/parser/data/statements.txt

https://commits.kde.org/kdb/c2fcf8a68170cb0df7757375dc35cef0896dcc15
Comment 9 Jarosław Staniek 2019-01-04 21:27:17 UTC
Git commit 28ade683a351ef0d68c772f1e36b316755370939 by Jaroslaw Staniek.
Committed on 04/01/2019 at 21:06.
Pushed by staniek into branch '3.2'.

Restore support for date/time constants in KEXI SQL

Merge branch '393094-date-constants-3.2' into 3.2

- 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

KDb VERSION -> 3.2.0 Beta 1

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.

2. 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.

{F6453115}

3. Create table with columns of type date, time and date/time and add some records.

4. 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.

{F6453113}

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

Differential Revision: https://phabricator.kde.org/D17336


https://commits.kde.org/kdb/28ade683a351ef0d68c772f1e36b316755370939