What is cooking in SQLite's kitchen?

Leslie7 · 2020-07-15 19:59:34

ab,

by looking at the repository comments it seems decimal arithmetic is finally coming.
Strangely it is described as an extension for doing decimal arithmetic on strings - so it seems there is no proper decimal data type. (Or in SQLite's world "decimal affinity".)

Currently it is built into the shell, but is an optional add-on for the library. It is not included in the amalgamation.
If this remains the case, will this be a part of the mORMot engine?

Last edited by Leslie7 (2020-07-15 20:15:18)

Leslie7 · 2020-08-17 16:59:46

v3.33 is out with decimal support. Will this be compiled into mORMot's sqlite?

Last edited by Leslie7 (2020-08-17 17:00:01)

ab · 2020-08-17 17:12:55

We will wait a few days, because almost everytime there is a #.#.1 revision which fixes some regression, discovered just after the official release.

Note that this "decimal" is not a new SQLite3 type, comparable to the decimal types of other RDBMS - it is just a way to access numbers stored with text columns.
This extension is not part of the amalgmation yet, so I don't think it is a good idea to enable it by default: it will need external c source files, which may not be in synch with the main amalgmation sqlite3.c file.

Leslie7 · 2020-08-18 15:21:33

It makes total sense to be cautious with the fresh releases.

Hopefully the end goal is to create a proper decimal type, but this is a step to the right direction. It is very much needed. I have never understood why was this not a priority for a long time. AFAIK the since cancelled v4 branch had proper decimal support. I assume this made the developers avoid the subject for v3 even longer.

I have no idea how difficult it is to add the extra source file(s) for mORMot, but it would be a little surprising if the source was not synced.

Last edited by Leslie7 (2020-08-18 21:23:54)

ab · 2020-09-02 07:53:34

3.33 has been included.
They didn't include any 3.33.# after-release patch this time!

Check https://synopse.info/fossil/info/0cb9b2f9c3
And don't forget to download the latest .o/.obj files too (no need from github).

Without the decimal functions support yet.

Junior/RO · 2020-09-02 13:21:47

@ab sqlite encryption is gone?

ab · 2020-09-02 17:05:50

It is still there.
The https://synopse.info/forum/viewtopic.ph … 327#p32327 new way of encrypting data is working as expected with 3.33.
No change about the encryption. Only XE4-Win64 is not working yet, due to a Delphi linker bug in this version. But seems fine from Delphi 6 to 10.x.

Leslie7 · 2020-09-11 05:37:22

ab wrote:

3.33 has been included.
They didn't include any 3.33.# after-release patch this time!

Take an umbrella to scare away the rain.

Leslie7 · 2020-12-03 10:59:00

AB,

the new version of SQLite is out. There is no mention about the decimal support this time. Is it now included in the amalgamation?

ab · 2020-12-03 13:26:25

Thanks for the update!
As usual, we will probably wait a few days before including it this new version.
In the past, some last minute bugs were discovered and a minor release was made.

I didn't see anything new about decimal support in the amalgamation.
There is still no native decimal type at storage. Only integer, blob, double or text can still be used, if I have seen correctly.

ab · 2020-12-03 16:50:02

I have compiled, tested and uploaded the latest 3.34.0 revision binaries, on all supported platforms.

Any feedback is welcome!

Leslie7 · 2020-12-04 07:03:52

Wow, you are really quick!

There may not be a dedicated Decimal data type for a long time. But with the Decimal extension the functionality is at least available, even though it requires a different syntax because it is still a text field. It is not optimal, mORMot's way mapping it to int64 is better, but the main advantage for me would be the possibility to use decimal_sum.

Last edited by Leslie7 (2020-12-04 10:43:01)

Leslie7 · 2020-12-04 10:42:24

You may not have the time or desire to work on including this specific extension, but in more general it would be useful to have a simple way to recompile sqlite with all the mORMot goodies and selected extensions for ourselves.

Maybe fpcupdeluxe could be extended with this feature. I assume beside AB Alf is the best qualified for this sort of stuff.

ab · 2020-12-04 14:45:33

Which extensions do you expect to be available?
Since the extensions are usually pretty small in size, I could add them to the amalgamation folder I guess.

About decimal support, what if we added some curr_add() cur_sub() and cur_mul() functions, not working with arbitrary decimal length as decimal.c (which makes it somewhat slow), but using internally a pascal currency storage (i.e. 64-bit with 4 fixed decimals), and working from any kind of input: integer, double, and text.

Leslie7 · 2020-12-05 12:54:28

ab wrote:

Which extensions do you expect to be available?

I am not familiar with sqlite's source, but for now it seems to be decimal.c only .

About decimal support, what if we added some curr_add() cur_sub() and cur_mul() functions, not working with arbitrary decimal length as decimal.c (which makes it somewhat slow), but using internally a pascal currency storage (i.e. 64-bit with 4 fixed decimals), and working from any kind of input: integer, double, and text.

It would be definitely an improvement on mORMots currency support to have "operators" on sql level.

The big question for me are aggregate functions on the sql level.

#This belongs more to an SQLite forum, but it has relevance for mORMot development too.#
I do not know if such ideas have ever been discussed for SQLite development, but the best solution I can think of if SQLite had an interface to assign a callback function for every aggregate based on the actual name of the the datatype.
This would allow developers to tell the engine if a certain aggregate is valid for the given datatype, and define how it is done.

Taking the extensibility even further: an interface to define datatypes
- their relations and conversion to other datatypes
- setter&getter functions for storage
- comparison functions
...

I know this is no small job for the developers (probably v4 level), but the flexibility it provides would be a huge benefit for the usability of the engine.
It would make extending the supported datatypes by SQLite itself very easy as well.
It would allow mORMot to make it's currency implementation work like a native SQLite datatype.

Going down on this road even further probably many limitations on virtual tables could be solved. Even creating and updating VTs structure by SQL could be made possible if the developers can provide the necessary functionality via interfaces.

Last edited by Leslie7 (2020-12-05 13:06:50)

ab · 2020-12-05 13:36:19

Since PostgreSQL allows this custom types since a lot of time, I am quite sure the SQLite3 developpers considered it.
They use PostgreSQL as their reference, in terms of both SQL dialect, and features.
But the datatypes are hardly casted in the current SQLite3 source code, so introducing custom data types, stored as blob at RTree level, may be feasible but it would be a huge work, especially about testing.

Leslie7 · 2020-12-05 14:00:30

When they cancelled he development of the former v4 branch it did not feel right. Even if it was fundamentally faulty in some ways it should have been redesigned. Trial and error is the only way to get to the goal.

mORMot Open Source

#1 2020-07-15 19:59:34

What is cooking in SQLite's kitchen?

#2 2020-08-17 16:59:46

Re: What is cooking in SQLite's kitchen?

#3 2020-08-17 17:12:55

Re: What is cooking in SQLite's kitchen?

#4 2020-08-18 15:21:33

Re: What is cooking in SQLite's kitchen?

#5 2020-09-02 07:53:34

Re: What is cooking in SQLite's kitchen?

#6 2020-09-02 13:21:47

Re: What is cooking in SQLite's kitchen?

#7 2020-09-02 17:05:50

Re: What is cooking in SQLite's kitchen?

#8 2020-09-11 05:37:22

Re: What is cooking in SQLite's kitchen?

#9 2020-12-03 10:59:00

Re: What is cooking in SQLite's kitchen?

#10 2020-12-03 13:26:25

Re: What is cooking in SQLite's kitchen?

#11 2020-12-03 16:50:02

Re: What is cooking in SQLite's kitchen?

#12 2020-12-04 07:03:52

Re: What is cooking in SQLite's kitchen?

#13 2020-12-04 10:42:24

Re: What is cooking in SQLite's kitchen?

#14 2020-12-04 14:45:33

Re: What is cooking in SQLite's kitchen?

#15 2020-12-05 12:54:28

Re: What is cooking in SQLite's kitchen?

#16 2020-12-05 13:36:19

Re: What is cooking in SQLite's kitchen?

#17 2020-12-05 14:00:30

Re: What is cooking in SQLite's kitchen?

Board footer