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

lainz
Member
Registered: 2018-03-09
Posts: 14

Embed SQLite C object files into application

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

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

Offline

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

lainz
Member
Registered: 2018-03-09
Posts: 14

Re: Embed SQLite C object files into application

Thanks. If I need further help I will ask.

Offline

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

lainz
Member
Registered: 2018-03-09
Posts: 14

Re: Embed SQLite C object files into application

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)

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Embed SQLite C object files into application

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

Offline

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

lainz
Member
Registered: 2018-03-09
Posts: 14

Re: Embed SQLite C object files into application

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 <<<<)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

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.

Offline

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

lainz
Member
Registered: 2018-03-09
Posts: 14

Re: Embed SQLite C object files into application

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

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.

Offline

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

AOG
Member
Registered: 2014-02-24
Posts: 490

Re: Embed SQLite C object files into application

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.

Offline

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

lainz
Member
Registered: 2018-03-09
Posts: 14

Re: Embed SQLite C object files into application

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

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.

Offline

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

lainz
Member
Registered: 2018-03-09
Posts: 14

Re: Embed SQLite C object files into application

Thanks.

Offline

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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,215

Re: Embed SQLite C object files into application

What a great addition!

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

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


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

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.

Offline

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

sh17
Member
Registered: 2011-08-09
Posts: 26

Re: Embed SQLite C object files into application

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

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.

Offline

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

sh17
Member
Registered: 2011-08-09
Posts: 26

Re: Embed SQLite C object files into application

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

Encryption on Delphi Win64 is not supported yet.

Offline

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

sh17
Member
Registered: 2011-08-09
Posts: 26

Re: Embed SQLite C object files into application

with wxsqlite it works smile but i need a DB Explorer

Thanks

Offline

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

emk
Member
Registered: 2013-10-24
Posts: 96

Re: Embed SQLite C object files into application

Any news on AES Sqlite encryption on Delphi-Win64?

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

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

Offline

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

emk
Member
Registered: 2013-10-24
Posts: 96

Re: Embed SQLite C object files into application

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)

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Embed SQLite C object files into application

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

Offline

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

emk
Member
Registered: 2013-10-24
Posts: 96

Re: Embed SQLite C object files into application

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.

Offline

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

greedy
Member
Registered: 2018-10-26
Posts: 11

Re: Embed SQLite C object files into application

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

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.

Offline

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

emk
Member
Registered: 2013-10-24
Posts: 96

Re: Embed SQLite C object files into application

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.

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Embed SQLite C object files into application

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)

Offline

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

emk
Member
Registered: 2013-10-24
Posts: 96

Re: Embed SQLite C object files into application

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.

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: Embed SQLite C object files into application

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: Embed SQLite C object files into application

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

Offline

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

emk
Member
Registered: 2013-10-24
Posts: 96

Re: Embed SQLite C object files into application

I have sent an email to Maxim Masiutin.

Offline

Board footer

Powered by FluxBB