Embed SQLite C object files into application

lainz · 2018-03-09 14:55:13

Hi, from the Lazarus forum they sent me to here, this is the original message
http://forum.lazarus.freepascal.org/ind … opic=40408

Hi, according to this file
ftp://ftp.freepascal.org/fpc/docs-pdf/CinFreePascal.pdf
I can use, instead of a .DLL, the object files .o into FPC project.
This is the library I'm using
https://github.com/utelle/wxsqlite3
And instead of using the .dll, I want to incorporate that library into the executable.
My questions:
* There is a wrapping unit already made I can use to do that, I say the unit having all .o files listed, or I need to make it by myself?
* The .o files need to be compiled in each OS I want to deploy my application. Say .o files from Windows are not the same .o files from Mac / Linux?
* About the license of this fork of SQLite, doing that in a commercial application is right? maybe I need to ask in the project, but I also add this here if someone knows.
Thanks.

I've seen that you have a SQLite3 folder with a lot of stuff there.
https://github.com/synopse/mORMot/tree/master/SQLite3

I can in any form copy that and use with Lazarus?

What I need is to embed the SQLite .o files into an application made with Lazarus, for Linux, Mac and Windows.

Any ideas on how I can do that?

Thanks.

ab · 2018-03-09 18:48:50

This is possible.
Just use the SynSQLite3Static.pas unit, and the .o files included in our fpc-* sub-folders of the repository.

lainz · 2018-03-09 19:26:44

Thanks. If I need further help I will ask.

lainz · 2018-03-09 19:56:54

Hi again, well I was testing it and seems that this method is missing:

sqlite3_exec
sqlite3_free

And also constant

SQLITE_OK

Maybe I'm missing something.

And another thing I notices is that I need to do Run > Build in order to compile.

If I just use the Run button it gives me some errors, for example that can't find some .a files.

Also I have another question, this SQLite you provide comes with the encryption support?

Last edited by lainz (2018-03-09 20:00:26)

mpv · 2018-03-09 22:22:58

About encryption, there is a topic https://synopse.info/forum/viewtopic.php?id=373&p=1

lainz · 2018-03-09 22:25:20

mpv wrote:

About encryption, there is a topic https://synopse.info/forum/viewtopic.php?id=373&p=1

I mean this encryption, because the databases are already encrypted
https://github.com/utelle/wxsqlite3

wxSQLite3 - SQLite3 database wrapper for wxWidgets (>>>> including SQLite3 encryption extension <<<<)

ab · 2018-03-10 10:00:06

If you want to use this encyption, use wxSQLite3 dll itself.
Note that this encryption is proprietary, i.e. specific to the wsSQLite3 library.
We compiled the raw sqlite3.c with standard extensions (FTS, JSON) as static, and propose a simple encryption scheme.

We would like to add a SynCrypto based encryption scheme, in the close future.
Our current encryption is less powerful than this one.

About missing SQLITE_OK, sqlite3_exec or sqlite3_free, please take a few minutes to read the unit source and documentation.
SQLITE_OK is defined in SynSQlite3.pas which is common to static or dynamic wrapper.
sqlite3.free_ is to be used as sqlite3_free.

Then Sqlite3 doc states: The sqlite3_exec() interface is a convenience wrapper around sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize(), that allows an application to run multiple statements of SQL without having to use a lot of C code.
We have a much powerful wrapper with the TSQLDataBase class. So we didn't define those on purpose.

lainz · 2018-03-10 12:58:06

ab wrote:

If you want to use this encyption, use wxSQLite3 dll itself.
Note that this encryption is proprietary, i.e. specific to the wsSQLite3 library.
We compiled the raw sqlite3.c with standard extensions (FTS, JSON) as static, and propose a simple encryption scheme.

Ok.

ab wrote:

We would like to add a SynCrypto based encryption scheme, in the close future.
Our current encryption is less powerful than this one.

I see. The thing is I not choose the dll to be used, it was choosen long time ago on the company by the original author of the application.

I think your model is easier, less powerful, but it saves us of the dll hell.

I can not change since the customers already have made local databases that rely on the model of wxSQLite3.

So maybe we can't change to this dll less system.

ab wrote:

About missing SQLITE_OK, sqlite3_exec or sqlite3_free, please take a few minutes to read the unit source and documentation.
SQLITE_OK is defined in SynSQlite3.pas which is common to static or dynamic wrapper.
sqlite3.free_ is to be used as sqlite3_free.

Ok. I did not read it fully. My fault. I did of course a quick search in the github repo and find that was in that unit you say. I asked because I wish to acomplish the no dll usage, and I badly think that these unit was only for static.

ab wrote:

Then Sqlite3 doc states: The sqlite3_exec() interface is a convenience wrapper around sqlite3_prepare_v2(), sqlite3_step(), and sqlite3_finalize(), that allows an application to run multiple statements of SQL without having to use a lot of C code.
We have a much powerful wrapper with the TSQLDataBase class. So we didn't define those on purpose.

Ok. Thanks. Is a good reason. Of course if it was possible to use your excelent package, we should change these methods by a better alternative, like the one you say.

Last edited by lainz (2018-03-10 13:00:31)

ab · 2018-03-12 15:52:40

Also check http://blog.synopse.info/post/2018/03/1 … encryption

This encryption is much faster than the one used by wxsqlite3.
You can also change the AES key size, and the hashing algorithm if needed. Note that our hashing use regular PBKDF2 pattern, and not some wxsqlite3 unproven design.
We used wxsqlite3 patch, but redesigned the whole raw encryption part.

AOG · 2018-03-12 16:04:30

Hi Ab,

This is a very welcome and very important feature !
This feature would also validate an announcement on the official Lazarus forum I think !!

For me, using a non-encrypted database has always been very tricky in case of client apps.

lainz · 2018-03-12 16:58:56

Hi, the important thing for me is if it's compatible with our existing databases? We can't simply migrate all databases to the new format.

Sorry if it's already answered, but I'm a newby on this matter.

ab · 2018-03-12 21:15:03

In the blog article, I stated that you could use IsOldSQLEncryptTable and OldSQLEncryptTablePassWordToPlain functions to migrate databases encrypted with the old layout at startup.

If you want to enable AES to an existing plain database, just call

ChangeSQLEncryptTablePassWord(aDBFileName, '', aNewPassword);

Migration will be made at low-level file level, so it will be very fast (no SQL nor ORM is involved here).
It will perform at high speed, the disk being the only bottleneck.

lainz · 2018-03-12 23:41:17

Thanks.

edwinsn · 2018-03-13 09:43:53

What a great addition!

And the diff of this commit must also shows the idea for implementing a **compression** extension

Last edited by edwinsn (2018-03-13 09:44:09)

ab · 2018-03-13 10:27:09

The compression extension will need not a codec, which works at page level before accessing the disk, but a VFS.
See https://www.sqlite.org/zipvfs/doc/trunk … works.wiki

So it is much more work, with a diverse implementation.
But we would like to introduce it, since, especially with SynLZ or Lizard compression (in pages are big), it could lead to wonders.
Anyway, making it ACID for the transactions may be particularly tricky and difficult...

ab · 2018-06-16 07:41:14

Update: We tuned the implementation algorithm used for encryption, to harden it, by ensuring the IV is really unpredictable.
See https://synopse.info/fossil/info/5b677a7d3d181a79

This is a breaking change but you can set ForceSQLite3LegacyAES=true to convert your existing databases to the new safer format.

sh17 · 2018-08-15 13:01:48

For the 64bit variant I have to take the SQLite DLL from https://github.com/utelle/wxsqlite3/releases, right?

Is there a database viewer that supports this encryption?

ab · 2018-08-15 14:04:59

No there is our version - see the documentation.
But it doesn't support the encryption.

For FPC, no need for the external DLL.
And it supports the encryption.

Our SynDBExplorer tool handles the encryption.

sh17 · 2018-08-16 07:09:22

i need encryption in Delphi Win64, so i need the external DLL.

ab · 2018-08-16 08:40:45

Encryption on Delphi Win64 is not supported yet.

sh17 · 2018-08-16 08:46:48

with wxsqlite it works but i need a DB Explorer

Thanks

emk · 2018-12-30 13:23:59

Any news on AES Sqlite encryption on Delphi-Win64?

And as a curiosity why Delphi-Win64 was not supported from the beginning?

ab · 2018-12-30 15:02:51

Because Delphi Win64 compiler isn't compatible with regular sqlite3.o file.

emk · 2018-12-31 10:57:22

ab, Thank you for your great work! and definitely to maintain so many compatibilities is a titanic task.

It seems that FPC+Win64 is the way to go if I want to use AES Sqlite encryption and also USEZLIBSSE.

I never used FPC, so regarding mORMot:
FPC+Win64 has any drawbacks against Delphi-Win32, or Delphi-Win64?
I know that Delphi-Win32 has a lot of assembler optimizations, it's still the case with FPC-Win64?
FastMM4 will "feel" the same under FPC+Win64?
FPC+Win64 will be the "same" with Delphi-Win64 in regard to fast http.sys?

Thank you.

Last edited by emk (2018-12-31 12:34:39)

mpv · 2019-01-01 16:39:34

emk wrote:

FastMM4 will "feel" the same under FPC+Win64?

No, FastMM not work correctly with FPC x64 (even with FastMM trunk) and should not be used (FPC_FASTMM4 is disabled by default for SynDprUses.inc )
We try cmem - uses less memory compared to build-in FPC memory manager, but a little slower on multi-thread (at last in my case)

emk wrote:

FPC+Win64 will be the "same" with Delphi-Win64 in regard to fast http.sys?

Yes, for sure

emk · 2019-01-02 22:51:23

Thank you for your response.

Maybe I'm misjudging but regarding mORMot on Windows, seems old good Delphi+Win32 has the most optimizations:
Delphi+Win64 doesn't have Sqlite AES encryption and most of the routines are PUREPASCAL.
FPC+Win64 doesn't have FastMM4..

I would like to use Win64 because my design involves multi-tentant databases, but also encryption is very important. So, I have to choose between 2 strategies:
1. Use FPC+Win64, but I'm a little concerned about speed, about not having FastMM4 and other novelties..
2. Use Delphi+Win32 and every Sqlite database will have smaller cache than default (reside more on OS cache), every sqlite database connection will expire&close when it's not used more than 15min and eventually, if is needed, running many mormot servers on the same machine but different ports and to allocate fewer threads to every mormot server.

I'm glad to here pro and cons for every way. Thank you.

greedy · 2019-01-03 10:51:30

mpv wrote:

emk wrote:
FastMM4 will "feel" the same under FPC+Win64?
No, FastMM not work correctly with FPC x64 (even with FastMM trunk) and should not be used (FPC_FASTMM4 is disabled by default for SynDprUses.inc )
We try cmem - uses less memory compared to build-in FPC memory manager, but a little slower on multi-thread (at last in my case)

Can you post any further information about the issue(s)?

emk wrote:

old good Delphi+Win32 has the most optimizations

FPC is open-source, you can add all of them to it.

emk wrote:

Delphi+Win64 doesn't have Sqlite AES encryption and most of the routines are PUREPASCAL.

Routines in PUREPASCAL doesn't need to be slower than some ancient asm code as the compiler could generate more modern and thus faster code.

emk wrote:

FPC+Win64 doesn't have FastMM4

as written above, I'm not sure why it should not work with FPC x64. I'm using it on FPC x64 for linux...
Btw there is also a newer version which might benefit from modern cpus: https://github.com/pleriche/FastMM4/pull/38

emk wrote:

Use FPC+Win64, but I'm a little concerned about speed, about not having FastMM4 and other novelties..

Which novelties do you mean? As already said, FPC is open-source and you can add all missing features you might need.
And about speed, I'm pretty sure you won't feel much difference and I think FastMM4 isn't a must-have because it is already some years old and less maintained.

emk · 2019-01-03 12:24:23

greedy wrote:

FPC is open-source, you can add all of them to it.

I'm not assembler guy neither CPU-extensions guy (ex SSE). This is the remarkable work of @ab.

greedy wrote:

as written above, I'm not sure why it should not work with FPC x64. I'm using it on FPC x64 for linux...
Btw there is also a newer version which might benefit from modern cpus: https://github.com/pleriche/FastMM4/pull/38

I know about FastMM4-AVX. In description says: "support for Lazarus 1.6.4 with FreePascal (the original FastMM4 4.992 requires modifications, it doesn't work under Lazarus 1.6.4 with FreePascal out-of-the-box, also tested under Lazarus 1.8.2 / FPC 3.0.4 with Win32 target;"

I think FastMM4-AVX supports only FPC-Win32.

mpv · 2019-01-03 14:45:15

greedy wrote:

mpv wrote:
emk wrote:
FastMM4 will "feel" the same under FPC+Win64?
No, FastMM not work correctly with FPC x64 (even with FastMM trunk) and should not be used (FPC_FASTMM4 is disabled by default for SynDprUses.inc )
We try cmem - uses less memory compared to build-in FPC memory manager, but a little slower on multi-thread (at last in my case)
Can you post any further information about the issue(s)?

Sorry for the incorrect - FastMM not work correctly with FPC x64 under Linux. Don't know about Windows.

Edited: I remembered - FastMM error is described here

Last edited by mpv (2019-01-03 14:54:48)

emk · 2019-01-03 21:07:00

I see now that FastMM4-AVX is compatible with FPC-64bit:

Didn't compile under 64-bit version of FreePascal

@mpv
Can you test if the problem described here is still reproducible with latest FastMM4-AVX?
If is still reproducible, I can contact Maxim Masiutin (author of FastMM4-AVX) to look over it.

mpv · 2019-01-04 10:34:18

FastMM4-AVX not compiled under Linux (FPC, x64) (errors: identifiers BOOL, Min not found). So I think it's not verified under Linux

ab · 2019-01-04 11:58:23

Last time I checked, even when fixing the FPC compilation, FastMM4-AVX made a lot of Access Violations on perfectly valid code...
So not an option by now.

emk · 2019-01-04 19:36:41

I have sent an email to Maxim Masiutin.

mORMot Open Source

#1 2018-03-09 14:55:13

Embed SQLite C object files into application

#2 2018-03-09 18:48:50

Re: Embed SQLite C object files into application

#3 2018-03-09 19:26:44

Re: Embed SQLite C object files into application

#4 2018-03-09 19:56:54

Re: Embed SQLite C object files into application

#5 2018-03-09 22:22:58

Re: Embed SQLite C object files into application

#6 2018-03-09 22:25:20

Re: Embed SQLite C object files into application

#7 2018-03-10 10:00:06

Re: Embed SQLite C object files into application

#8 2018-03-10 12:58:06

Re: Embed SQLite C object files into application

#9 2018-03-12 15:52:40

Re: Embed SQLite C object files into application

#10 2018-03-12 16:04:30

Re: Embed SQLite C object files into application

#11 2018-03-12 16:58:56

Re: Embed SQLite C object files into application

#12 2018-03-12 21:15:03

Re: Embed SQLite C object files into application

#13 2018-03-12 23:41:17

Re: Embed SQLite C object files into application

#14 2018-03-13 09:43:53

Re: Embed SQLite C object files into application

#15 2018-03-13 10:27:09

Re: Embed SQLite C object files into application

#16 2018-06-16 07:41:14

Re: Embed SQLite C object files into application

#17 2018-08-15 13:01:48

Re: Embed SQLite C object files into application

#18 2018-08-15 14:04:59

Re: Embed SQLite C object files into application

#19 2018-08-16 07:09:22

Re: Embed SQLite C object files into application

#20 2018-08-16 08:40:45

Re: Embed SQLite C object files into application

#21 2018-08-16 08:46:48

Re: Embed SQLite C object files into application

#22 2018-12-30 13:23:59

Re: Embed SQLite C object files into application

#23 2018-12-30 15:02:51

Re: Embed SQLite C object files into application

#24 2018-12-31 10:57:22

Re: Embed SQLite C object files into application

#25 2019-01-01 16:39:34

Re: Embed SQLite C object files into application

#26 2019-01-02 22:51:23

Re: Embed SQLite C object files into application

#27 2019-01-03 10:51:30

Re: Embed SQLite C object files into application

#28 2019-01-03 12:24:23

Re: Embed SQLite C object files into application

#29 2019-01-03 14:45:15

Re: Embed SQLite C object files into application

#30 2019-01-03 21:07:00

Re: Embed SQLite C object files into application

#31 2019-01-04 10:34:18

Re: Embed SQLite C object files into application

#32 2019-01-04 11:58:23

Re: Embed SQLite C object files into application

#33 2019-01-04 19:36:41

Re: Embed SQLite C object files into application

Board footer