ANN: wxSQLite3 1.9.8 released

Do you like to promote your wxWidgets based application or component!? Post it here and let's see what the critics have to say. Also, if you found that ONE wx component the world needs to know about, put it here for future reference.
Post Reply
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

ANN: wxSQLite3 1.9.8 released

Post by utelle »

Version 1.9.8 of wxSQLite3 has been released. It supports version 3.6.22 of SQLite.

What's new in this release:

- upgrade to SQLite 3.6.22
- fixed a compile bug when compiling without precompiled headers
- experimental 256 bit AES encryption has been added to the optional encryption extension

The wxSQLite3 file release contains the doxygen generated documentation.

The file release for Windows additionally contains version 3.6.22 of the SQLite DLL in 2 different flavors:

- the original unmodified DLL, and
- a DLL supporting optional database file encryption using 128 bit AES encryption.

Additionally a precompiled SQLite shell with encryption support for Windows is included.

Feedback is welcome.

Regards,

Ulrich
Mojo
Super wx Problem Solver
Super wx Problem Solver
Posts: 401
Joined: Wed Sep 21, 2005 8:17 am
Location: Rostov-on-Don, Southern Russia

Post by Mojo »

Nice work
Win XP HE SP3, Vista
Xubuntu 12.04 LTS
wxWidgets-2.9.5
wxWidgets-3.0.0
Jeisson
Earned a small fee
Earned a small fee
Posts: 22
Joined: Wed Sep 12, 2007 7:35 pm
Location: Costa Rica

Post by Jeisson »

Hi utelle. Thanks so much for sharing your talent with us.

I am trying to compile the wxSQLite3 1.9.8 in DLL debug mode with Visual Studio 2005 against wxWidgets-2.8.10 and I am getting linking errors. First I compile wxWidgets and set the WXWIN environment variable.

Code: Select all

nmake -f makefile.vc BUILD=debug UNICODE=1 SHARED=1 WXUNIV=0 MONOLITHIC=0
Then I compile the SQLite3 amalgamation following these steps with Visual Studio and I get the .lib and .dll. I copy all generated files from the Debug folder to the wxSQLite3-1.9.8\sqlite3\lib folder. Now I try to compile wxSQLite3:

Code: Select all

nmake -f makefile.vc BUILD=debug SHARED=1 UNICODE=1 USE_DYNAMIC_SQLITE3_LOAD=1 WX_SHARED=1 WX_UNICODE=1 WX_DEBUG=1
Compilation process is successful, but linker claims:

Code: Select all

link /DLL /NOLOGO /OUT:..\lib\vc_dll\wxcode_msw28ud_wxsqlite3.dll  /LIBP ATH:L:\hdd\out\wx\2.8.10\lib\vc_dll /DEBUG /pdb:"..\lib\vc_dll\wxcode_msw28ud_wx sqlite3.pdb"  /LIBPATH:..\lib\vc_dll /LIBPATH:..\sqlite3\lib  @C:\DOCUME~1\Yo\LO CALS~1\Temp\nm12E.tmp

Creating library ..\lib\vc_dll\wxcode_msw28ud_wxsqlite3.lib and object ..\lib \vc_dll\wxcode_msw28ud_wxsqlite3.exp

wxsqlite3_dll_wxsqlite3.obj : error LNK2019: unresolved external symbol _sqlite3 _backup_finish referenced in function "public: void __thiscall wxSQLite3Database ::Backup(class wxString const &,class wxMemoryBuffer const &,class wxString cons t &)" (?Backup@wxSQLite3Database@@QAEXABVwxString@@ABVwxMemoryBuffer@@0@Z)

wxsqlite3_dll_wxsqlite3.obj : error LNK2019: unresolved external symbol _sqlite3 _backup_step referenced in function "public: void __thiscall wxSQLite3Database:: Backup(class wxString const &,class wxMemoryBuffer const &,class wxString const &)" (?Backup@wxSQLite3Database@@QAEXABVwxString@@ABVwxMemoryBuffer@@0@Z)

wxsqlite3_dll_wxsqlite3.obj : error LNK2019: unresolved external symbol _sqlite3 _backup_init referenced in function "public: void __thiscall wxSQLite3Database:: Backup(class wxString const &,class wxMemoryBuffer const &,class wxString const &)" (?Backup@wxSQLite3Database@@QAEXABVwxString@@ABVwxMemoryBuffer@@0@Z)

wxsqlite3_dll_wxsqlite3.obj : error LNK2019: unresolved external symbol _sqlite3 _sleep referenced in function "public: void __thiscall wxSQLite3Database::Restor e(class wxString const &,class wxMemoryBuffer const &,class wxString const &)" ( ?Restore@wxSQLite3Database@@QAEXABVwxString@@ABVwxMemoryBuffer@@0@Z)

..\lib\vc_dll\wxcode_msw28ud_wxsqlite3.dll : fatal error LNK1120: 4 unresolved e xternals
I imagine I would add a preprocessor flag while I compile SQLite3 related to BACKUP functionality, but I am unable to find that macro definition. I am not experiencing problems when I compile the release-static version of the three libraries.

Utelle, thank you so much again for your affability.

Best regards
-Jeisson.
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

Hi Jeisson,
Jeisson wrote:I imagine I would add a preprocessor flag while I compile SQLite3 related to BACKUP functionality, but I am unable to find that macro definition. I am not experiencing problems when I compile the release-static version of the three libraries.
It has nothing to do with a missing preprocessor flag. Instead you have discovered a bug in wxSQLite3. In case SQLite should be loaded dynamically there is a small glitch in the header file wxsqlite3dyn.h causing the problems you are experiencing.

I changed the header file - hopefully fixing the bug. You may download it from the wxCode SVN.

Thanks for reporting this issue.

Regards,

Ulrich
Jeisson
Earned a small fee
Earned a small fee
Posts: 22
Joined: Wed Sep 12, 2007 7:35 pm
Location: Costa Rica

Post by Jeisson »

Thanks Ulrich for the fixes.

Six commas are missing in lines 20 to 24 of wxsqlite3dyn.h in order to compile the dll version of wxSQLite3:
DYNFUNC(return, sqlite3_backup*, sqlite3_backup_init, (sqlite3 *pDest, const char *zDestName, sqlite3 *pSource, const char *zSourceName), (pDest, zDestName, pSource, zSourceName));
DYNFUNC(return, int, sqlite3_backup_step, (sqlite3_backup *p, int nPage), (p, nPage));
DYNFUNC(return, int, sqlite3_backup_finish, (sqlite3_backup *p), (p));
DYNFUNC(return, int, sqlite3_backup_remaining, (sqlite3_backup *p), (p));
DYNFUNC(return, int, sqlite3_backup_pagecount, (sqlite3_backup *p), (p));
The fixed file is attached to this message. Compiler also shows two warnings:

Code: Select all

samples\minimal.cpp(87) : warning C4702: unreachable code
samples\minimal.cpp(518) : warning C4701: potentially uninitialized local variable 'pbin' used
Thanks so much again
Best regards
-Jeisson
Attachments
wxsqlite3dyn.h
File fixed adding six commas
(18.97 KiB) Downloaded 161 times
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

Jeisson wrote:Six commas are missing in lines 20 to 24 of wxsqlite3dyn.h in order to compile the dll version of wxSQLite3: [...]

The fixed file is attached to this message.
Thanks for reporting and providing fixes.
Jeisson wrote:Compiler also shows two warnings:

Code: Select all

samples\minimal.cpp(87) : warning C4702: unreachable code
samples\minimal.cpp(518) : warning C4701: potentially uninitialized local variable 'pbin' used
The code where the first warning occurs is intentionally as is - it's only a dumb example of the use of class wxSQLite3Transaction.

The second warning is more serious although in the sample application there won't be a problem. Nevertheless I'll fix this in the next release of wxSQLite3 (probably within the next 1 or 2 weeks).

Regards,

Ulrich
skozlov
Knows some wx things
Knows some wx things
Posts: 26
Joined: Tue Dec 16, 2008 12:54 pm

Post by skozlov »

Hi.

Maybe you know where is problem...

Few days ago I started using wxSQLite 1.9.8.
But something strange happens while using wxSQLite3Table.

Here is the "stack dump":

1) Create DB in the memory:

Code: Select all

  m_db.reset( new wxSQLite3Database() );
  m_db->Open(":memory:");
2) Create table

Code: Select all

    m_db->ExecuteUpdate(
      "CREATE TEMP TABLE tm_log(Time INTEGER, Exchange TEXT, Stock TEXT, "
      "Price REAL, XOpen REAL, Bid REAL, Ask REAL, AddNo INTEGER PRIMARY KEY AUTOINCREMENT); "
      "CREATE INDEX IF NOT EXISTS tm_log_time_idx ON tm_log(Time)"
      );
3) Populate table with ~4500 rows

Code: Select all

  m_queryPrint = m_dbModel->db()->PrepareStatement(
      "INSERT INTO tm_log (Time, Exchange, Stock, Price, XOpen, Bid, Ask) "
      "VALUES (?, ?, ?, ?, ?, ?, ?)");

  // binding
  ...

  m_queryPrint.ExecuteUpdate();
4) Then I use wxSQLite3Table for displaying table in the wxGrid.

Code: Select all

  m_table = m_db->GetTable("SELECT * FROM tm_log ORDER BY AddNo ASC");
After data loading I call wxSQLite3Table::GetTable() and if load another set of data and call wxSQLite3Table::GetTable() then wxSQLite3Table copy constructor will be called. Then wxSQLite3Table::Finalize() will be called, then this method will call sqlite3_free_table(m_results).

And sqlite3_free_table() function will take many time.
In the Debug build it will take ~2 sec. In the release build it will takes ~0.5 (or ~1) sec.

Do you know why sqlite3_free_table() takes so much time?

All SQL queries executes fast..

I'm using wxWidgets 2.8.9 ANSI monolithic DLL, win XP x32/x64.

sqlite was build with simple cmake script:

Code: Select all

cmake_minimum_required(VERSION 2.6)
project(sqlite3)

include_directories(../)
add_definitions(-DTHREADSAFE=1 -DSQLITE_SECURE_DELETE -DNO_TCL)
add_library(sqlite3 ../sqlite3.c)

set_target_properties(sqlite3 PROPERTIES DEBUG_POSTFIX d)
wxSQLite3 was build using distrib project files and also I tried custom cmake buid (result the same).

I know this is not wxSQLite issue, but maybe you can tell something about this situation.

Thanks.

BTW wxSQLite3 is good. I like it)
WinXp, wxWidgets 2.8.9, wxWidgets-svn, MSVC 9
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

skozlov wrote:But something strange happens while using wxSQLite3Table.

Here is the "stack dump":

1) Create DB in the memory:

Code: Select all

  m_db.reset( new wxSQLite3Database() );
  m_db->Open(":memory:");
The first line probably should read

Code: Select all

  m_db = new wxSQLite3Database();
skozlov wrote:4) Then I use wxSQLite3Table for displaying table in the wxGrid.

Code: Select all

  m_table = m_db->GetTable("SELECT * FROM tm_log ORDER BY AddNo ASC");
I'd like to remark that using method GetTable is usually not the recommended way to query the database as underlying SQLite loads the complete resultset into memory and converts every data item into string representation.
skozlov wrote:And sqlite3_free_table() function will take many time.
In the Debug build it will take ~2 sec. In the release build it will takes ~0.5 (or ~1) sec.

Do you know why sqlite3_free_table() takes so much time?
SQLite calls internally malloc to allocate memory for every single item. Thus sqlite3_free_table needs to call free for every item. In your case this means 36.000 calls to free.

Maybe this can explain why freeing the table takes quite long. But I'm only guessing as I'm not an expert for explaining SQLite's internals.
skozlov wrote:I know this is not wxSQLite issue, but maybe you can tell something about this situation.
I'd recommend to avoid using wxSQLite3Table in the first place. At the most you should read the keys of the data rows to be displayed in the wxGrid and load only those rows into memory which are actually visible in the wxGrid.

Regards,

Ulrich
skozlov
Knows some wx things
Knows some wx things
Posts: 26
Joined: Tue Dec 16, 2008 12:54 pm

Post by skozlov »

utelle wrote:
I'd like to remark that using method GetTable is usually not the recommended way to query the database as underlying SQLite loads the complete resultset into memory and converts every data item into string representation.
Well I use wxSQLite3Table in the wxGridTableBase as data source.
I checked wxSQLite3 API and this is the only usefull class for this. For example wxSQLite3ResultSet do not provide number of rows, can move only forward ( NextRow() ).
With wxSQLite3Table we can move at any row using SetRow(). That is why I'm using wxSQLite3Table. And I do not know how to use wxSQLite3ResultSet for this purpose..

Maybe I will create background thread for finalize wxSQLite3Table ...

One question. Where to use wxSQLite3Table?

Thanks.
WinXp, wxWidgets 2.8.9, wxWidgets-svn, MSVC 9
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

skozlov wrote:Well I use wxSQLite3Table in the wxGridTableBase as data source.
I checked wxSQLite3 API and this is the only usefull class for this. For example wxSQLite3ResultSet do not provide number of rows, can move only forward ( NextRow() ).
With wxSQLite3Table we can move at any row using SetRow(). That is why I'm using wxSQLite3Table. And I do not know how to use wxSQLite3ResultSet for this purpose..
Internally sqlite3_get_table (which is called by wxSQLite3 to create a wxSQLite3Table instance) uses a cursor to load the complete result set into memory. This is usually feasible only for relatively small result sets.

Since you create a memory database querying the database should usually be very fast. Therefore in your place I would query the database for a small result set whenever the wxGrid is scrolled up or down. Based on the key associated with the first visible row you could initiate a query like

Code: Select all

select * from table where key >= firstkey limit numberOfVisibleRows
On initializing the wxGrid it would be sufficient to load the list of keys into memory. For this purpose you could use an array into which you transfer the data from the query result set.

Loading the complete list of keys into memory could be avoided, if you limit scrolling of the wxGrid in chunks instead of free scrolling. In that case you could base your query on the key of the first visible row.
skozlov wrote:One question. Where to use wxSQLite3Table
Well, I would use it only for result sets of which I know in advance that they are very small. In my own applications even in that case I usually use a query result set and transfer the data into arrays or other appropriate data structures. This saves unnecessary data type conversions and memory management is often more efficient.

Regards,

Ulrich
skozlov
Knows some wx things
Knows some wx things
Posts: 26
Joined: Tue Dec 16, 2008 12:54 pm

Post by skozlov »

utelle wrote:
skozlov wrote:Well I use wxSQLite3Table in the wxGridTableBase as data source.
I checked wxSQLite3 API and this is the only usefull class for this. For example wxSQLite3ResultSet do not provide number of rows, can move only forward ( NextRow() ).
With wxSQLite3Table we can move at any row using SetRow(). That is why I'm using wxSQLite3Table. And I do not know how to use wxSQLite3ResultSet for this purpose..
Internally sqlite3_get_table (which is called by wxSQLite3 to create a wxSQLite3Table instance) uses a cursor to load the complete result set into memory. This is usually feasible only for relatively small result sets.

Since you create a memory database querying the database should usually be very fast. Therefore in your place I would query the database for a small result set whenever the wxGrid is scrolled up or down. Based on the key associated with the first visible row you could initiate a query like

Code: Select all

select * from table where key >= firstkey limit numberOfVisibleRows
On initializing the wxGrid it would be sufficient to load the list of keys into memory. For this purpose you could use an array into which you transfer the data from the query result set.

Loading the complete list of keys into memory could be avoided, if you limit scrolling of the wxGrid in chunks instead of free scrolling. In that case you could base your query on the key of the first visible row.
skozlov wrote:One question. Where to use wxSQLite3Table
Well, I would use it only for result sets of which I know in advance that they are very small. In my own applications even in that case I usually use a query result set and transfer the data into arrays or other appropriate data structures. This saves unnecessary data type conversions and memory management is often more efficient.

Ulrich
Many thanks for your answers, Ulrich.
I'll try to avoid wxSQLite3Table using..
WinXp, wxWidgets 2.8.9, wxWidgets-svn, MSVC 9
Post Reply