All SQLite3 library API calls

okoba · 2021-02-25 09:44:23

I needed to get sqlite3_normalized_sql and sqlite3_expanded_sql of a statement, but I could not find it in the TSqlite3Library.
It seems TSqlite3Library has not all the SQLite3 API, although it has a comment stating that "wrapper around all SQLite3 library API calls".
ab, do you like to have more of the API in the wrapper or just the ones used by mORMot? If all, I will be happy to give it a try or at least add more.

ab · 2021-02-25 13:17:49

Yes, you could add some for sure.

We only defined what we needed for our SynSQLite3 and mORMot work.

okoba · 2021-02-25 14:45:51

As a test, I've added and checked these:
sqlite3_stmt_busy
sqlite3_stmt_isexplain
sqlite3_sql
sqlite3_expanded_sql
sqlite3_normalized_sql
Although your provided build has not sqlite3_normalized_sql. Can you add it?
I like to add more like scanstatus* next.
Here is the result patches: https://gist.github.com/OkobaPatino/76e … aa741255e9

Last edited by okoba (2021-02-25 14:48:16)

okoba · 2021-02-25 15:17:15

One question, some defined function in the static unit returning PAnsiChar, but you used PUtf8Char most of the time. Is there a particular reason?

ab · 2021-02-25 15:47:26

There should all be PUtf8Char everywhere, I guess, since we use the UTF-8 API.
But PAnsiChar won't hurt in practice. Just not very consistent.

You are right, SQLITE_ENABLE_NORMALIZE conditional should be enabled when compiling SQLite3.
It will be the case for the next compilation cycle.

I have noticed three set of missing SQLite3 APIs from SQLite3\sqlite3mc.c :

#define SQLITE_ENABLE_RBU 1
// enable "Resumable Bulk Update" (or OTA) https://www.sqlite.org/rbu.html
// - not used/published yet in pascal code

#define SQLITE_ENABLE_SESSION
#define SQLITE_ENABLE_PREUPDATE_HOOK
// enable Sessions https://sqlite.org/sessionintro.html
// - not used/published yet in pascal code

#define SQLITE_ENABLE_NORMALIZE
// enable all https://sqlite.org/c3ref/expanded_sql.html functions
// - not used/published yet in pascal code

Perhaps it would be the good occasion to include all three functions sets.

BTW your gist integration is just great.

okoba · 2021-02-25 15:50:39

I will fix the PUtf8Char cases and add more of the API and send you the patch.

okoba · 2021-03-02 16:58:38

I've updated the sqlite3 units and added almost all the remaining methods from the API that seems useful.
There are 280 SQLite methods in the API, and we now have 171 (previously 91). Only VFS methods are remaining for now, other ones have a reason to not be included.

Not included ones:
These are the one that are not added and the reason. I will add any of them if you like.
- All the _16 methods as I do not see any use for them (many SQLite functions like sqlite3_bind_parameter_name return UTF8 anyway). The only one already existing is column_text16.
- Most of *64 methods like malloc64, status64, blob64 and text64. I do not see much use for them right now but SQLite says they are preferred.
- Legacy or deprecated methods like sqlite3_get_table
- Some _vX methods that has a more complete one already like sqlite3_trace
- Prohibited methods like sqlite3_os_end
- Deprecated flags like SQLITE_PREPARE_NORMALIZE
- Methods that already have a more complete option (eg sqlite3_result_error_nomem has sqlite3_result_error)
- Systemic methods like sleep or randomness as we have them already in Pascal code.
- String methods as we have them as or more powerful already in Pascal and mORMot like Format (eg sqlite3_stricmp) or can be achieved with a simple query (eg sqlite3_strlike).
- sqlite3_str_append
- sqlite3_str_appendall
- sqlite3_str_appendchar
- sqlite3_str_appendf
- sqlite3_str_errcode
- sqlite3_str_finish
- sqlite3_str_length
- sqlite3_str_new
- sqlite3_str_reset
- sqlite3_str_value
- sqlite3_str_vappendf
- sqlite3_stricmp
- sqlite3_strlike
- sqlite3_strnicmp
- sqlite3_vmprintf
- sqlite3_vsnprintf
- sqlite3_mprintf
- sqlite3_snprintf
- sqlite3_strglob
- Mutex methods as we have more powerful options already in Pascal and mORMot.
- sqlite3_mutex_alloc
- sqlite3_mutex_enter
- sqlite3_mutex_free
- sqlite3_mutex_held
- sqlite3_mutex_leave
- sqlite3_mutex_notheld
- sqlite3_mutex_try
- sqlite3_db_mutex

Todos:
- If you need or like to have some of the "Not included ones".
- Session and RBU and maybe more extension are good to add, but it is better to have them in the build beforehand so we can test and verify. Also maybe a structure to add extensions is a good idea as SQLite has many good options that can be useful.
- https://www.sqlite.org/rbu.html
- https://sqlite.org/sessionintro.html
- Missing VFS methods. I did not know how to check or use them.
- sqlite3_vfs_find
- sqlite3_vfs_register
- sqlite3_vfs_unregister
- sqlite3_free_filename
- sqlite3_filename_database
- sqlite3_filename_journal
- sqlite3_filename_wal
- sqlite3_win32_set_directory
- sqlite3_win32_set_directory16
- sqlite3_win32_set_directory8
- sqlite3_uri_boolean
- sqlite3_uri_int64
- sqlite3_uri_key
- sqlite3_uri_parameter
- sqlite3_file_control
- sqlite3_create_filename
- sqlite3_database_file_object

Notes:
- sqlite3_status64 is added and it seems there is no need for memory_used and memory_highwater anymore.
- I did not change the order of methods, but it may be a good idea to sort them better to match SQLite. Let me know if you like that.
- prepare_v3 is added, maybe it is a good idea to remove the v2, as v3 does the extract same thing more option.
- libversion_number is added so it is a good idea to change the Create method.
- extended_result_codes and Extended Result Code are added, maybe you like to enable it by default and use it in mORMot.
- create_collation_v2 is added, maybe it is a good idea to remove the v1, as v2 does the extact same thing with an optional destroy.

Finally these are the flags needed to be added to the build. All the missing methods are commented in TSqlite3LibraryStatic.Create, need to uncomment after adding.
- YYTRACKMAXSTACKDEPTH for SQLITE_STATUS_PARSER_STACK
- SQLITE_ENABLE_COLUMN_METADATA for column_database_name, column_table_name and
- column_origin_name
- SQLITE_ENABLE_STMT_SCANSTATUS for stmt_scanstatus and stmt_scanstatus_reset
- SQLITE_ENABLE_PREUPDATE_HOOK for sqlite3_preupdate_*_
- SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION for sqlite3_*_extension
- SQLITE_ENABLE_SNAPSHOT for sqlite3_snapshot*
- SQLITE_ENABLE_UNLOCK_NOTIFY for sqlite3_unlock_notify
- sqlite3_progress_handler is missed from the build

I would like to know your comments on the Todos and Notes.
And thank you for the clean structure, I tried to follow that and checked most of them.

Here is the result patches: https://gist.github.com/OkobaPatino/23e … 86003a094b

ab · 2021-03-02 18:37:15

Great!

Sorrting the methods in "SQLite natural order" is a good idea.

Could you make a pull request in Github so that it would be easier to review and merge, and you would be mentioned as contributor to the project!
There is now a lot to merge - which is great!

Thanks a lot.

okoba · 2021-03-02 19:05:57

Done, please check.

Leslie7 · 2021-03-03 07:03:43

Okokba,

can you see a way to include the "decimal" extention too? It is not part of the SQLite amalgamation, so it may be tricikier.

ab · 2021-03-03 07:04:33

Thanks!

Side note: the merge of this pull request broke the Delphi compilation.
I will fix it.

okoba · 2021-03-03 07:16:50

Thanks ab.
Please let me know when the needed flags are added so we can uncomment deactivated methods and maybe rearrange them in "SQLite natural order". Also some cleanups maybe, see "Notes" on the changelog for more info.

Leslie7, that is an interesting extension. First it needs to be added to the build by ab like other ones.

ab · 2021-03-03 08:28:27

Also included the new binaries including all extensions in https://github.com/synopse/mORMot2/rele … ite.3.34.1

okoba · 2021-03-03 08:39:01

I saw your fix, why changing PUtf8Char made the problem?
Thanks for the new build, I checked it and now all methods can compile except progress_handler. More info: https://www.sqlite.org/capi3ref.html#sq … ss_handler
Shall I make a pull request on uncommenting deactivated methods in TSqlite3LibraryStatic.Create or you will?

ab · 2021-03-03 13:24:06

For Delphi, PAnsiChar is really <> PUtf8Char so it refuses to compile, whereas FPC is more relaxed.

With my latest commits, progress_handler should compile.
See https://github.com/synopse/mORMot2/comm … 3737cc5c2b

okoba · 2021-03-03 13:38:18

Yes it is.
threadsafe is removed. Is there a reason as you did not remove it from TSqlite3Library?
I will rearrange and cleanup methods.

ab · 2021-03-03 15:03:04

Please do the arrangement.

okoba · 2021-03-03 16:23:37

Done, please check.

Leslie7 · 2021-03-04 06:56:22

Maybe this was overlooked ...

Leslie7 wrote:

Okokba,
can you see a way to include the "decimal" extention too? It is not part of the SQLite amalgamation, so it may be tricikier.

okoba · 2021-03-04 07:45:04

I answered.

okoba wrote:

Leslie7, that is an interesting extension. First it needs to be added to the build by ab like other ones.

Leslie7 · 2021-03-04 14:05:59

Yes, I can see now. It was at the end of a comment for AB.

mORMot Open Source

#1 2021-02-25 09:44:23

All SQLite3 library API calls

#2 2021-02-25 13:17:49

Re: All SQLite3 library API calls

#3 2021-02-25 14:45:51

Re: All SQLite3 library API calls

#4 2021-02-25 15:17:15

Re: All SQLite3 library API calls

#5 2021-02-25 15:47:26

Re: All SQLite3 library API calls

#6 2021-02-25 15:50:39

Re: All SQLite3 library API calls

#7 2021-03-02 16:58:38

Re: All SQLite3 library API calls

#8 2021-03-02 18:37:15

Re: All SQLite3 library API calls

#9 2021-03-02 19:05:57

Re: All SQLite3 library API calls

#10 2021-03-03 07:03:43

Re: All SQLite3 library API calls

#11 2021-03-03 07:04:33

Re: All SQLite3 library API calls

#12 2021-03-03 07:16:50

Re: All SQLite3 library API calls

#13 2021-03-03 08:28:27

Re: All SQLite3 library API calls

#14 2021-03-03 08:39:01

Re: All SQLite3 library API calls

#15 2021-03-03 13:24:06

Re: All SQLite3 library API calls

#16 2021-03-03 13:38:18

Re: All SQLite3 library API calls

#17 2021-03-03 15:03:04

Re: All SQLite3 library API calls

#18 2021-03-03 16:23:37

Re: All SQLite3 library API calls

#19 2021-03-04 06:56:22

Re: All SQLite3 library API calls

#20 2021-03-04 07:45:04

Re: All SQLite3 library API calls

#21 2021-03-04 14:05:59

Re: All SQLite3 library API calls

Board footer