Lazarus

Programming => Operating Systems => Windows CE => Topic started by: vincococka on June 23, 2014, 08:59:29 pm

Title: SQLite3 + encryption for WinCE (arm4i)
Post by: vincococka on June 23, 2014, 08:59:29 pm
Hi all,

let me share with you following compiled versions of SQLite for Windows CE 5/6 (arm4i).

Download URL:
  SQLite 3.9.2 (+ version with AES-128 enc.) (http://itm8.sk/sqlite3ce/3.9.2/)
  MD5 checksums:
  db83043f404e866dbdf4b9c4554fb6ca sqlite3ce.dll
  8b27b589c91fb6eadf8fc5fb9e22098f  sqlite3ce-aes.dll


Built with following preprocessor macros:
Code: [Select]
_UNICODE
UNICODE
NO_TCL
SQLITE_DEFAULT_FOREIGN_KEYS=1
SQLITE_DEFAULT_MMAP_SIZE=2097152
SQLITE_DISABLE_LFS
SQLITE_OMIT_TCL_VARIABLE
SQLITE_OMIT_TRACE
SQLITE_HAS_CODEC
CODEC_TYPE=CODEC_TYPE_AES128
SQLITE_CORE;THREADSAFE=0
SQLITE_THREADSAFE=0
SQLITE_USER_AUTHENTICATION
SQLITE_ENABLE_EXTFUNC

Download URL:
  SQLite 3.11.1.0 (with AES-128 encryption & threadsafe=1) (http://itm8.sk/sqlite3ce/)

Built with following preprocessor macros:
Code: [Select]
_UNICODE;UNICODE;NO_TCL;SQLITE_DEFAULT_FOREIGN_KEYS=1;SQLITE_DEFAULT_MMAP_SIZE=2097152;SQLITE_DISABLE_LFS;SQLITE_OMIT_TCL_VARIABLE;SQLITE_OMIT_TRACE;SQLITE_HAS_CODEC;CODEC_TYPE=CODEC_TYPE_AES128;SQLITE_CORE;THREADSAFE=1;SQLITE_THREADSAFE=1;SQLITE_USER_AUTHENTICATION;SQLITE_ENABLE_EXTFUNC

To encrypt database in FreePascal / Lazarus / Delphi you have to first initialize the database with new password via sqlite3_rekey. After encrypting it is enough to use sqlite3_open following with sqlite3_key call...

// Encryption support
//
function sqlite3_key(db: PSQLite3; const pKey: PAnsiChar; nKey: Integer): Integer; cdecl; external 'sqlite3ce-aes.dll';
function sqlite3_rekey(db: PSQLite3; const pKey: PAnsiChar; nKey: Integer): Integer; cdecl; external 'sqlite3ce-aes.dll';

Before opening database it is good to check first 6 bytes of file to compare them with "SQLite" - this means db is unencrypted so you dont have to call sqlite3_key after sqlite3_open.

Examples:

1, opening and encrypting DB file
Code: [Select]
sqlite3_open('test.db', db);
sqlite3_rekey(db, 'test', Length('test'));
// then use db as usual
2, opening already encrypted DB
Code: [Select]
sqlite3_open('test.db', db);
sqlite3_key(db, 'test', Length('test'));
// then use db as usual
3, decrypt database file
Code: [Select]
sqlite3_open('test.db', db);
sqlite3_key(db, 'test', Length('test'));
sqlite3_rekey(db, '', 0); // length 0 will decrypt database file
// then use db as usual

Regards,
  Jan
Title: Re: SQLite3 3.8.5 for WinCE (arm4i)
Post by: bambamns on June 24, 2014, 04:18:01 am
Thank You  ;)

First test shows positive results.

I'll put it in production very soon.
Title: Re: SQLite3 3.8.5 for WinCE (arm4i)
Post by: vincococka on June 24, 2014, 01:19:42 pm
Hi,

I`m glad that it is working for you.
I was in need of having current SQLite for on small project running on WinCE, so I decided to fire up Visual Studio and compile it for myself :).

Just consider that it is compiled with O3 optimization and it was optimized for size (not for speed, because file has 750KB) + with following DEFINES:
  SQLITE_ENABLE_COLUMN_METADATA
  NO_TCL
  SQLITE_OMIT_TRACE

When new version comes out I`ll probably compile it again and put it here somewhere in "clouds" :).

Greets,
  Vince
Title: Re: SQLite3 3.8.5 for WinCE (arm4i)
Post by: bambamns on June 25, 2014, 06:16:55 am
This is great !

If you don't mind , it will be very helpful to compile another version with speed optimization, so we can test if there are noticeable differences in performance.

Any way - thx
Title: Re: SQLite3 3.8.5 for WinCE (arm4i)
Post by: vincococka on June 25, 2014, 10:23:32 am
Hi,

I`ve uploaded freshly compiled build that has optimization preference set to speed (not size):
  http://www.filedropper.com/sqlite3-385-arm4i-speed
Title: Re: SQLite3 3.8.6 for WinCE (arm4i)
Post by: vincococka on August 17, 2014, 09:39:44 pm
Hi all,

I`ve uploaded new version for you.. Enjoy!

Compiled without COLUMN_METADATA and TRACE, optimization preference is for speed (not size).
Title: Re: SQLite3 3.8.6 for WinCE (arm4i)
Post by: bambamns on August 28, 2014, 04:07:15 am
Thx,

Can you explain :
Quote
Compiled without COLUMN_METADATA and TRACE...
Title: Re: SQLite3 3.8.6 for WinCE (arm4i)
Post by: vincococka on August 29, 2014, 01:09:36 am
Hi,

I`m really sorry for confusion.

Here are MACROS that I`ve set before compilation of release 3.8.6 (what a significant version :D)
NO_TCL
SQLITE_OMIT_TRACE
SQLITE_DEFAULT_FOREIGN_KEYS=1

V.
Title: Re: SQLite3 3.8.7 for WinCE (arm4i)
Post by: vincococka on October 19, 2014, 11:05:08 pm
Hi all,

here we go again with new release of SQLite library 3.8.7:

http://www.filedropper.com/sqlite3ce (http://www.filedropper.com/sqlite3ce)
Title: Re: SQLite3 3.8.7 for WinCE (arm4i)
Post by: bambamns on October 25, 2014, 03:34:39 am
Nice,

Since you put :
SQLITE_DEFAULT_FOREIGN_KEYS=1

sqlite finally  take care for foreign keys.

Thx
Title: Re: SQLite3 3.8.7 for WinCE (arm4i)
Post by: vincococka on October 26, 2014, 02:04:59 am
Hi,

yeah, referential integrity is a must.
Btw, does it work as expected?

Regards,
   V.
Title: Re: SQLite3 3.8.7 for WinCE (arm4i)
Post by: bambamns on October 29, 2014, 05:25:33 am
Ye it works like it has to be.
Title: Re: SQLite3 3.8.7 for WinCE (arm4i)
Post by: bambamns on November 07, 2014, 05:59:23 am
I've noticed some problems with this version so be careful when you use it.
It has some issue with unfinished transaction so the final result is started but not finished transaction and no access to database.

I'm back on production with 3.8.5 - 3.8.6 and 3.8.7 are on test.
Title: Re: SQLite3 3.8.7.1 for WinCE (arm4i)
Post by: vincococka on November 09, 2014, 11:10:24 pm
Hi all,

for those who are interested I`ve compiled current 3.8.7.1 version of SQLite for WinCE platform..
Enjoy!
Title: Re: SQLite3 3.8.7.1 for WinCE (arm4i)
Post by: bambamns on November 10, 2014, 05:29:36 am
Nice - but where is the link ?
Title: Re: SQLite3 3.8.7.1 for WinCE (arm4i)
Post by: vincococka on November 10, 2014, 05:31:59 pm
Hi,

link is in the first post... sorry for not mentioning this 8-).

But just in case:
http://www.filedropper.com/sqlite3ce_1 (http://www.filedropper.com/sqlite3ce_1)
Title: Re: SQLite3 3.8.7.1 for WinCE (arm4i)
Post by: swierzbicki on November 17, 2014, 03:57:29 pm
Hello,

It look like the file is no more available for downloading.

Thanks
Title: Re: SQLite3 3.8.7.1 for WinCE (arm4i)
Post by: vincococka on November 18, 2014, 03:39:10 pm
Hi,

here is new download link:
http://s000.tinyupload.com/index.php?file_id=02472018078786708111
Title: Re: SQLite3 3.8.7.1 for WinCE (arm4i)
Post by: Never on November 18, 2014, 03:52:17 pm
nod32 reported a virus
Title: Re: SQLite3 3.8.7.1 for WinCE (arm4i)
Post by: vincococka on November 18, 2014, 03:56:52 pm
sorry for that but seems to be false positive, please check MD5 checksum (made with total commander 8.01):
9afaf80f4f805aecc6d733bb77b8a0c0 *sqlite3ce.dll

Just another mirror:
sqlite3ce.dll (http://wikisend.com/download/450878/sqlite3ce.dll)
Title: Re: SQLite3 3.8.7.1 for WinCE (arm4i)
Post by: BigChimp on November 18, 2014, 03:59:10 pm
nod32 reported a virus
FYI: you can use e.g. http://virusscan.jotti.org/en to let multiple virus scanners scan for viruses. Often you do see false positives for some scanners...
Title: Re: SQLite3 3.8.7.1 for WinCE (arm4i)
Post by: Never on November 18, 2014, 04:02:00 pm
the second link is ok
the file is ok from the second link
but something is wrong with tinyupload

Edit***@ BigChimp
Quote
FYI: you can use e.g. http://virusscan.jotti.org/en to let multiple virus scanners scan for viruses. Often you do see false positives for some scanners...
i prefer virus@total
Title: Re: SQLite3 3.8.7.2 for WinCE (arm4i)
Post by: vincococka on November 28, 2014, 01:54:44 pm
Hello,

if anybody`s interested, I`ve compiled version 3.8.7.2 for Windows CE.

Enjoy!

Title: Re: SQLite3 3.8.7.2 for WinCE (arm4i)
Post by: swierzbicki on November 28, 2014, 02:27:03 pm
Yes, am I :D
Title: Re: SQLite3 3.8.7.2 for WinCE (arm4i)
Post by: vincococka on November 28, 2014, 03:32:06 pm
Hi,

I`m glad that somebody finds it useful for his work, and the effort put inside was worth of it.
Keep in mind that both versions (3.8.7.1 & 3.8.7.2) were compiled with following DEFINEs:
Title: Re: SQLite3 3.8.7.2 for WinCE (arm4i)
Post by: swierzbicki on December 01, 2014, 10:59:18 am
I really appreciate your efforts.
I have one question, is SQLITE compile with SQLITE_THREADSAFE=1 (1 is the default value ?)
Title: Re: SQLite3 3.8.7.2 for WinCE (arm4i)
Post by: vincococka on December 01, 2014, 09:19:22 pm
Hi,

only flags that I`ve mentioned in previuos post were used.
Everything else was left in its default value.

Anyway, you can test it with following library call:
  https://www.sqlite.org/c3ref/threadsafe.html
Title: Re: SQLite3 3.8.7.2 for WinCE (arm4i)
Post by: bambamns on January 03, 2015, 06:39:15 am
Hi,

As you can see this thread has been read almost 2000 times, so there are people who are interested for this.

I have tried all the link to download 3.8.7.2 but they are dead or my antivirus block access.
Title: Re: SQLite3 3.8.7.2 for WinCE (arm4i)
Post by: vincococka on January 03, 2015, 02:14:11 pm
Hi all,

here we have new version available:
SQLite 3.8.7.4 for Windows CE : http://www.datafilehost.com/d/2ed275a9

@bambamns: thanks for replying - your post forced me to recompile new version and publish it :).
Title: Re: SQLite3 3.8.8.1 for WinCE (arm4i)
Post by: vincococka on January 30, 2015, 10:53:24 am
Hi all,

I`ve compiled version 3.8.8.1 of SQLite for WinCE... see first post with details.
Tested with emulator and Trimble Recon device (WinCE6).
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: vincococka on March 02, 2015, 11:20:56 am
Hi all,

in first post you can find link to DLL of SQLite 3.8.7.4 compiled with AES/128 encryption.
Library was built from wxSqlite3 3.2.0 codebase (thanks Ullrich - KUDOZ !).
Tested on emulator and 2 real devices ;-).

I hope that you`ll find it usefull.

Regards, 
  Jan
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: vincococka on November 26, 2015, 08:20:45 am
Update to version 3.9.2.
See first post for download links.

Enjoy :-)
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: bambamns on February 20, 2016, 05:10:50 am
Hi,

All links are dead - can you mak them alive ?

Thx
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: vincococka on March 09, 2016, 12:15:33 am
In case somebody is interested, I`ve recompiled SQLite 3.11.1 with wxSqlite AES 128bit encryption enabled + set THREADSAFE=1. Thanks Ulrich for your great work!

SQLite3ce: http://itm8.sk/sqlite3ce/ (http://itm8.sk/sqlite3ce/)
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: ertank on April 12, 2016, 10:34:47 pm
I am sorry if it is already posted.

Is there any sqlite3 dll that is supposed to be faster than others (all compiler optimizations at max & all necessary parameters ON of sqlite)

I am not an sqlite guy, I do not know correct parameters. I am lacking information as to how to compile it for WinCE platform, too. Or else I would gladly do it.

Thanks.
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: vincococka on April 28, 2016, 12:15:39 am
Hi ,

sorry for replying so late but I was pretty busy.

I think that there is not any king of magic bullet available for this kind of requirement.
SQLite has lot of compile-time IFDEFs, also Visual C++ compiler offers some nice optimizations (but your library will grow in binary size).
Most of the time engine will spent processing data so you have to think of more about how to properly squeeze maximum performance from engine via queries and think about every one you type. Even best compilers in the world won`t solve developer lazyness.

One example from life: there was a report (data are in Oracle DB 11.2) that was running for 11-12 hours till it finishes. After some SQL queries tuning it`s now finished 3 minutes :).
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: ertank on May 06, 2016, 09:55:18 am
Is there any simple 3.11.1 sqlite dll for wince? Or, this AES128 encryption can open regular sqlite3 databases? Database which created using regular sqlite3. I constantly get "database malformed" or "file is encrypted or is not a database" error messages when I switched using 3.11.1 with aes128 and thread safe version.
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: vincococka on May 10, 2016, 09:46:33 pm
This 3.11.1 version that I`ve compiled is fully able to operate on encrypted/non-encrypted files.
Please see first post - I`ve written there some hints how to work with encryption.

V.
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: ertank on May 18, 2016, 06:10:14 pm
Hi,

Is it possible that we get a new version of DLL compiled for wince, please? If possible a simple DLL with no encryption and another one with encryption is appreciated.

There is some kind of a bug in 3.11.1 DLL version.

1) When I use a code like below:
Code: [Select]
Transaction.Action := caCommit;
Transaction.Options := [stoUseImplicit];

Transaction.StartTransaction;
[... do some insert/delete/update ...]
Transaction.Commit;

I cannot see changes until a database close/open, or application complete close and start again. On the other hand, same application runs without any problem with version 3.9.2 DLL.

2) I have a database with 4 million records inside. Database size is about 1.3GB. There is no problem opening and using it with 3.9.2 DLL, and 3.11.1 simply says database corrupt, or bad or something like that and cannot even open that database.

Thanks.
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: bambamns on December 16, 2017, 08:35:34 pm
Hi,

Link on first post is dead - can you make it alive ?

Thx
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: ertank on December 19, 2017, 09:57:26 pm
Trying to attach in a message here. Version 3.21.0.0
Title: Re: SQLite3 + encryption for WinCE (arm4i)
Post by: ertank on May 21, 2019, 09:21:55 pm
Here is a DLL of sqlite3 version 3.28 compiled for WinCE ARMv4