Sqlite and Harbour

1,007 views
Skip to first unread message

Jayadev U

unread,
Dec 12, 2014, 1:38:08 AM12/12/14
to harbou...@googlegroups.com

Hi,

 

Has anybody used Sqlite database and Harbour in real life in MULTI USER environment.  What is the experience ?

 

Have you used the sddsqlt3 available in \hb32\contrib\sddsqlt3.

 

Please share your experience.

 

Warm regards,

 

Jayadev

 

 

Disclaimer: The information in this email is for the use of the individual or entity to whom it is addressed and others authorized to receive it. It may contain confidential or legally privileged information. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or taking any action relying on the contents of this information is strictly prohibited; it may be unlawful. If you have received this email by mistake, please notify me immediately by replying to this email and then deleting it from your system. I am neither liable for the proper and complete transmission of the information contained in this email nor for any delay in its receipt

 

Qatan

unread,
Dec 12, 2014, 2:46:26 AM12/12/14
to harbou...@googlegroups.com
Hello Jayadev,
 
I did some tests but not in multi user environment.
It is not made for multi user with a lot of concurrency but it can be used to a certain level. The good is that it is “self-contained, serveless, zero-configuration, transactional SQL database” ( http://www.sqlite.org/ )
SQLite is fast and reliable. It is not difficult to develop an application using it.
Using with SQLCipher makes a very secure database fully encrypted 256-bit AES: https://www.zetetic.net/sqlcipher/
I didn’t use sddsqlt3. I used hbsqlit3 instead (I prefer like that...)
I suggest you to connect with HMG Forum to know about other experiences. That forum is one of the most actives in terms of SQLite x Harbour that I know. http://www.hmgforum.com
I hope it will help you.
 
Qatan
--
--
You received this message because you are subscribed to the Google
Groups "Harbour Users" group.
Unsubscribe: harbour-user...@googlegroups.com
Web: http://groups.google.com/group/harbour-users

---
You received this message because you are subscribed to the Google Groups "Harbour Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to harbour-user...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Jayadev U

unread,
Dec 12, 2014, 3:12:32 AM12/12/14
to harbou...@googlegroups.com

Hi Qatan,

 

Many thanks for your reply.

 

>>I didn’t use sddsqlt3. I used hbsqlit3 instead (I prefer like that...)

 

Did you use browse ?, if yes, then how did you handle it.  Did you do an array browse using tBrowse class ?

 

>>I suggest you to connect with HMG Forum to know about other experiences. That forum is one of the most actives in terms of SQLite x Harbour that I know. http://www.hmgforum.com

 

Yes, I am aware of HmgForum.

 

>>Using with SQLCipher makes a very secure database fully encrypted 256-bit AES: https://www.zetetic.net/sqlcipher/

 

Where you able to port SqlCipher to Harbour ? If yes, could you list down the steps.

 

My good friend, Mr. Rathinagiri from HmgForum had ported the Sqlcipher lib for use with Harbour.  However, as I understand now, there is a version difference hence the request for steps.

 

Warm regards,

 

Jayadev

Qatan

unread,
Dec 12, 2014, 4:03:57 AM12/12/14
to harbou...@googlegroups.com
Hello Jayadev,
 

>Where you able to port SqlCipher to Harbour ? If yes, could you list down the steps.

 >

>My good friend, Mr. Rathinagiri from HmgForum had ported the Sqlcipher lib for use with Harbour.  However, as I understand now, there is a version >difference hence the request for steps.

 

 
It all started when I saw this commit on the Changelog:
2010-12-09 12:10 UTC+0200 Petr Chornyj (myorg63 at mail.ru)
  * contrib/3rd/sqlite3/sqlite3.c
  * contrib/3rd/sqlite3/sqlite3.h
    * Update sqlite to 3.7.4.
  + contrib/3rd/sqlite3/sqlite3ext.h
    + Added sqlite3ext.h
      It's needed for some 3rd sqlite extension, f.e. ICU
  * contrib/3rd/sqlite3/sqlite3.hbp
    * Update sqlite to 3.7.4.
    + Added -cflag=-DSQLITE_ENABLE_COLUMN_METADATA
  * contrib/hbsqlit3/hbsqlit3.c
    + Added sqlite3_blob_reopen(), sqlite3_stmt_readonly()
    + sqlite3_enable_load_extension() function guarded with SQLITE_OMIT_LOAD_EXTENSION
      It's needed for some 3rd sqlite extension,
      f.e. SQLCIPHER for security reasons
 
  + contrib/hblzf
  + contrib/hblzf/3rd
  + contrib/hblzf/3rd/liblzf
  + contrib/hblzf/3rd/liblzf/Changes
  + contrib/hblzf/3rd/liblzf/crc32.h
  + contrib/hblzf/3rd/liblzf/LICENSE
  + contrib/hblzf/3rd/liblzf/lzf.h
  + contrib/hblzf/3rd/liblzf/lzf.hbc
  + contrib/hblzf/3rd/liblzf/lzf.hbp
  + contrib/hblzf/3rd/liblzf/lzf_c.c
  + contrib/hblzf/3rd/liblzf/lzf_d.c
  + contrib/hblzf/3rd/liblzf/lzfP.h
  + contrib/hblzf/3rd/liblzf/README
  + contrib/hblzf/hblzf.c
  + contrib/hblzf/hblzf.hbc
  + contrib/hblzf/hblzf.hbp
  + contrib/hblzf/tests
  + contrib/hblzf/tests/hbmk.hbm
  + contrib/hblzf/tests/test.prg
  + contrib/hbplist
  * contrib/hbplist
  * INSTALL
    + added wrapper to liblzf library (LZF compression).
 
 
 
Then I asked Petr Chornyj about SQLCipher and he answered (09/Dec/2010):
Very easy to build SQLCIPHER with MinGW + MSYS
So all what you need to do it's:
1) Download mingw-get installer from http://sourceforge.net/projects/mingw/ to the C:\MinGW directory.
Add C:\MinGW\bin to the PATH environment variable.
Install MinGW Compiler Suite (C, C++) and  MinGW Developer ToolKit.
2) Download and install Tcl (I use ActiveTcl from http://www.activestate.com/activetcl/downloads)
3) Download SQLCIPHER and extract  the files to the C:\MinGW\msys\1.0\home\__YourUserName__\sqlcipher
4) Download OpenSSL for Windows binary (direct link is http://www.slproweb.com/download/Win32OpenSSL-1_0_0c.exe).
and install to (f.e.) C:\OpenSSL
5) Run msys.bat. and execute the command (you can use copy/paste)
cd sqlcipher
./configure --disable-tcl --disable-amalgamation CFLAGS="-DSQLITE_HAS_CODEC -DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_OMIT_DEPRECATED  -I/c/OpenSSL/include /c/OpenSSL/libeay32.dll"
make
make dll
make install
6) Rebuild hbsqlit3 with -cflag=-DSQLITE_OMIT_LOAD_EXTENSION
7) With hbmk2 create import library for OpenSSL dll's

If you use Harbour with HB_WITH_OPENSSL you already have OpenSSL binary and import libraries.
 
Regards,
Petr
 
 
 
Here is what Rathinagiri did to port basing on the original message from Petr, http://www.hmgforum.com/viewtopic.php?f=37&t=2275&hilit=sqlcipher 
[harbour-u​sers] SQLCipher ported to Harbour (Windows - MingW)
rathinagiri subbiah <srathi...@gmail.com> Unsubscribe
2/9/12
 
I am happy that I could port SQLCipher to Harbour (Windows)
successfully:
 
Here are the steps I have followed to port it to Harbour: (My System
is Vista Ultimate, HMG 3.0.40)
 
If you do not want to know about the build procedure and straightaway
use SQLCipher you can go to step 12.
 
1. Downloaded SQLCipher Source code from here.
based on SQLite version 3.7.9) in c:\sqlcipher
 
2. Downloaded and installed MingW + Msys latest version from
in c:\mingw
 
3. Downloaded and installed ActiveState TCL from
TCL is not required for SQLite as such. But MingW & MSys requires TCL
to generate some c codes. It requires AWK too. But MingW installation
includes AWK. So, there is no problem once you install Mingw + MSys.
 
4. Downloaded and installed OpenSSL from http://www.slproweb.com/products/Win32OpenSSL.html
in c:\openssl
 
5. Copied c:\openssl\include\openssl folder to c:\mingw\include
\openssl
 
6. Copied c:\openssl\lib\mingw\libeay32.a to c:\mingw\msys\1.0 (This
is not required, but I have done for easy path)
 
7. Opened Command prompt. Set path to mingw and msys using the
following commands, changed directory to SQLCipher and opened shell:
[code]
c:\>path=%path%;c:\mingw\bin;c:\mingw\msys\1.0\bin
c:\>cd sqlcipher
c:\sqlcipher>sh
[code]
 
7. Run configure script as mentioned in SQLCipher site as below in the
shell prompt as instructed in SQLCipher.net:
[code]
./configure --enable-tempstore=yes CFLAGS="-DSQLITE_HAS_CODEC"
LDFLAGS="libeay32.a"
[/code]
 
8. Once the configuration is over, type make
[/code]
make
[/code]
 
This command would create many files including amalgamated single
sqlite3.c and sqlite3.h
 
The make command gave many errors, I could not trace out but we need
not worry about this as we require only sqlite3.c and sqlite3.h as
above.
 
9. Created a directory sqlcipher inside Harbour folder. Copied
sqlite3.c and sqlite3.h to this folder. Copied c:\openssl\lib
\libeay32.a to c:\hmg.3.0.40\lib folder for having openssl at the time
of application building.
 
10. Created the following hbc and hbp files:
 
sqlcipher.hbp file.
[code]
#
# $Id: sqlite3.hbp 16257 2011-02-09 12:47:35Z vszakats $
#
 
-stop{hbdyn}
 
-stop{poccarm}
# NOTE: old msvcarm can't cope with some PP directives. [vszakats]
-stop{msvcarm&HB_COMP_VER!='1200'&HB_COMP_VER!='1300'&HB_COMP_VER!
='1310')}
# NOTE: dos based watcom runs out of memory. [vszakats]
-stop{HB_HOST_PLAT='dos'&watcom}
# NOTE: disable *nix builds on non-*nix platforms; [vszakats]
#       except for cygwin-on-win
-stop{!(HB_HOST_PLAT='win'&cygwin)&HB_HOST_PLAT_UNIX=''&unix}
 
-hblib
-inc
 
-o${hb_targetname}
 
-warn=low
-cpp=no
 
-cflag=-DSQLITE_HAS_CODEC
-cflag=-DSQLITE_OMIT_DEPRECATED
-cflag=-DSQLITE_ENABLE_COLUMN_METADATA
-cflag=-D_WIN32_WCE{wce}
# DJGPP and OpenWatcom in DOS aren't correctly recognized by SQLite,
# so we're forcing the next best available option. This will cause
missing
# externals though. [vszakats]
-cflag=-DSQLITE_OS_OTHER{dos}
# Watcom Linux builds cannot use system header files
-cflag=-DSQLITE_OS_OTHER{linux&watcom}
 
-cflag=-DSQLITE_THREADSAFE=0{minix}
-cflag=-DSQLITE_OMIT_LOAD_EXTENSION=1{minix}
 
sqlite3.c
 
[/code]
 
sqlcipher.hbc
[code]
#
# $Id: sqlite3.hbc 16215 2011-02-05 15:53:17Z vszakats $
#
 
libs=${hb_name}
 
cflags=-DSQLITE_OMIT_LOAD_EXTENSION=1{minix}
[/code]
 
11. Created buildlib.bat as below and run in the command prompt:
[code]
SET HMGPATH=\hmg.3.0.40
 
SET PATH=%HMGPATH%\harbour\bin;%HMGPATH%\mingw\bin;%PATH%
 
hbmk2 sqlcipher.hbp -i%hmgpath%\include
[/code]
 
File libsqlcipher.a is created by the above step. Copied this file to
c:\hmg.3.0.40\lib folder.
 
12. Created a sample hmg application to find out whether everything is
fine:
sample.prg
[code]
#include <hmg.ch>
 
Function Main
    local oDB := nil
    local cKey := 'password123'
    local cFile := 'sample.sqlite'
    local aTable := {}
 
    if file( cFile )
       oDB := connect2db( 'sample.sqlite', .f. )
       if oDB == Nil
          msgstop( 'Database File can not be connected' )
       else
          msginfo( iif( miscsql( oDB, 'pragma key = ' +
c2sql( cKey ) ), 'Encryption Key is set', 'Encryption key can not be
set' ) )
          aTable := sql( oDB, 'select name, city from master' )
          if len( aTable ) > 0
             msginfo( 'Name:' + aTable[ 1, 1 ] + ' City:' + aTable[ 1,
2 ] )
          endif
       endif
    else
       oDB := connect2db( 'sample.sqlite', .t. )
       if oDB == Nil
          msgstop( 'Database File can not be connected' )
       else
          msginfo( iif( miscsql( oDB, 'pragma key = ' +
c2sql( cKey ) ), 'Encryption Key is set', 'Encryption key can not be
set!' ) )
          msginfo( iif( miscsql( oDB, 'create table master (name,
city)' ), 'Master table is created successfully!', 'Table can not be
created!' ) )
          msginfo( iif( miscsql( oDB, 'insert into master ( name,
city ) values ( ' + c2sql( 'Name1' ) + ', ' + c2sql( 'City1' ) +
' )' ), 'Sample Data updated', 'sample data can not be updated!' ) )
       endif
    endif
Return nil
[/code]
 
13. Compiled using the following HMG-IDE configuration:
[code]
inc=yes
head=native
libs=hmgsqlite
libs=sqlcipher
libs=eay32
[/code]
 
14. Note: All our applications created using the above libsqlcipher.a
library requires libeay32.dll at the runtime. It can be kept in the
same directory as application file or can be kept at c:\windows
\system32 folder too.
 
You can download sqlcipher compiled library from here (http://
www.rathinagiri.in/sqlcipher.zip). Kindly test with your applications
and give your feedback.
 
Enjoy HMGing!
 
 
Long time ago I struggled to do all that and I got very close but I never completed the whole process and I had to change my focus to some other pressing needs so I gave up and just used the lib Rathinagiri generated (last one in Aug/2014) - although I am still interested in building the lib myself.
 
You will find many details about my struggle here: http://www.hmgforum.com/viewtopic.php?f=37&t=2275&hilit=sqlcipher 
 
 
I hope it will help you...
 
Qatan
 
 

Jayadev U

unread,
Dec 12, 2014, 4:43:50 AM12/12/14
to harbou...@googlegroups.com

Wow, I seem to have missed the HMG post.

 

Ok, for the time being, I shall download Mr Rathinagiri’s lib and proceed.

 

Warm regards,

 

Jayadev

 

 

 

 

From: harbou...@googlegroups.com [mailto:harbou...@googlegroups.com] On Behalf Of Qatan
Sent: Friday, December 12, 2014 2:34 PM
To: harbou...@googlegroups.com
Subject: Re: [harbour-users] Sqlite and Harbour

 

Hello Jayadev,

--

Qatan

unread,
Dec 12, 2014, 5:55:15 AM12/12/14
to harbou...@googlegroups.com
Hello Jayadev,
 

>Wow, I seem to have missed the HMG post.

>Ok, for the time being, I shall download Mr Rathinagiri’s lib and proceed.

 

When I have time I will try to build it again...
What makes it kind of difficult or complicated is the quantity of tools, steps and configurations involved but I believe that once you overcome the first time than it gets much easier for next builds.
Other aspect is the version... it is a bit more complex because SQLCipher is made for a specific version of SQLite and the developers don’t update it so often (after all it is for free so you can not expect too much unless you are willing to pay for a special build).
To avoid problems you have to pay attention to mach Harbour, SQLite, SQLCipher and C compiler versions...
 
I am just mentioning all that in case you want to try the adventure... but do not get scared... this monster is not so dangerous Winking smile
 
Regards,
 
Qatan
 
wlEmoticon-winkingsmile[1].png
Reply all
Reply to author
Forward
0 new messages