ANN: wxSQLite3 1.9.4 released

[utelle]

Version 1.9.4 of wxSQLite3 has been released. It supports version 3.6.10 of SQLite. The component is compatible with wxWidgets version 2.8.9.

What's new in this release:

Besides the upgrade to SQLite 3.6.10 support for savepoints, introduced with SQLite 3.6.8, has been added. A method IsOk has been added to the classes wxSQLite3Statement, wxSQLite3Table and wxSQLite3ResultSet, thus instances of these classes can be checked whether the associated SQLite database or statement are valid without throwing an exception.

The wxSQLite3 file release contains the doxygen generated documentation. The file release for Windows additionally contains version 3.6.10 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.

In all DLL versions the FTS3 and rtree extension modules are internally enabled.

SQLite link libraries for MinGW on Windows are included, too. Additionally a precompiled SQLite shell with encryption support for Windows is included.

Feedback is welcome.

Regards,

Ulrich

[doublemax]

Hi.

I'd like to make two feature requests. Maybe you could consider those for future versions:

a) implementation of sqlite REGEXP operator

b) transparent memorydb mode
Explanation:
i know by passing ":memory:" i can create a database in memory. But i'd like to use a normal database on disk which is transparently copied into a memory-database. During the lifetime of the database object, all operations work on the in-memory database. When the database objects is closed the memory-database should be copied back to the file (optional, see below).

Possible implementation:
For this wxSQLite3Database::Open() gets another parameter, e.g. memory_db_mode.
Possible values:
MEMORY_DB_NONE = default, current behavior

MEMORY_DB_READONLY = database is copied to memory-database, but when closing, the changes are discarded (useful when original database files lies on CD media)

MEMORY_DB_READWRITE = database is copied to memory-database, when closed, memory-database is copied back to the file

Purpose for this is not speed, i know that in-memory databases are not necessarily faster. Main purpose is to avoid harddisk waking up from sleep-mode on portable devices.

[utelle]

I'd like to make two feature requests. Maybe you could consider those for future versions:

a) implementation of sqlite REGEXP operator

No big deal if a user defined function based on wxWidgets and incorporated into wxSQLite3 itself is sufficient. BUT such an implementation would not be available in most stand-alone SQLite management tools.

Another possibility would be to create a (loadable) extension for SQLite, but this would require a separate regexp implementation (duplicating code in wxWidgets).

The former could be part of the next version of wxSQLite3. The latter would take more time (and wouldn't get high priority on my to-do list I have to admit).

b) transparent memorydb mode

The latest version of SQLite, namely version 3.6.11, introduces an Online Backup API. I guess it provides just the functionality you are looking for. Although this new API is flagged as "experimental" I plan to add access to it to the next version of wxSQLite3, probably as methods Backup and Restore of the wxSQLite3Database class.

A version of wxSQLite3 supporting SQLite 3.6.11 will be available within the next couple of weeks.

Regards,

Ulrich

[doublemax]

I'd like to make two feature requests. Maybe you could consider those for future versions:

a) implementation of sqlite REGEXP operator

No big deal if a user defined function based on wxWidgets and incorporated into wxSQLite3 itself is sufficient. BUT such an implementation would not be available in most stand-alone SQLite management tools.

that would be totally sufficient for me

Another possibility would be to create a (loadable) extension for SQLite, but this would require a separate regexp implementation (duplicating code in wxWidgets).

Sounds like too much effort

b) transparent memorydb mode

The latest version of SQLite, namely version 3.6.11, introduces an Online Backup API. I guess it provides just the functionality you are looking for.

Interesting, i didn't know about that function. I once wrote some code that copies a database by reading the table and index information from the sqlite_master table, but it felt a bit of a hack and i'm not sure if it really works in all cases.

[utelle]

a) implementation of sqlite REGEXP operator

No big deal if a user defined function based on wxWidgets and incorporated into wxSQLite3 itself is sufficient. [...]

that would be totally sufficient for me

Ok, I'm going to implement it for the next version of wxSQLite3. Would it be sufficient to provide a class derived from wxSQLite3ScalarFunction which can then be passed to method CreateFunction of a wxSQLite3Database instance? Or should there be a mechanism to enable user defined functions provided by wxSQLite3 itself automatically - and if so how should it work?

b) transparent memorydb mode

The latest version of SQLite, namely version 3.6.11, introduces an Online Backup API. I guess it provides just the functionality you are looking for.

Interesting, i didn't know about that function. I once wrote some code that copies a database by reading the table and index information from the sqlite_master table, but it felt a bit of a hack and i'm not sure if it really works in all cases.

Fortunately this new API turned up at the right time. Without it backing up a memory database and restoring it wouldn't be a simple task.

Regards,

Ulrich

[doublemax]

a) implementation of sqlite REGEXP operator

No big deal if a user defined function based on wxWidgets and incorporated into wxSQLite3 itself is sufficient. [...]

that would be totally sufficient for me

Ok, I'm going to implement it for the next version of wxSQLite3. Would it be sufficient to provide a class derived from wxSQLite3ScalarFunction which can then be passed to method CreateFunction of a wxSQLite3Database instance? Or should there be a mechanism to enable user defined functions provided by wxSQLite3 itself automatically - and if so how should it work?

my initial idea was to just silently implement it, but i totally forgot the possibility that someone might have defined that function already

Having a predefined class so that enabling it is a one-liner sounds like a good idea.

Thanks.

[utelle]

A new version of wxSQLite3 supporting backup and restore (using the online backup API introduced by SQLite 3.6.11) and providing a user defined function class for the REGEXP operator is now available in the wxCode SVN repository.

Depending on the comments I get an official release will follow later this week.

Regards,

Ulrich

[doublemax]

great, thanks. But i won't have time to test it before tuesday. I'll report back then.

[doublemax]

i haven't done a real-life test of the backup/restore functionality yet.

But regexp works just fine.

Thanks again.

[utelle]

i haven't done a real-life test of the backup/restore functionality yet.

Essentially I took the code for methods Backup and Restore from the SQLite shell program, so I hope it will work under most circumstances.

But regexp works just fine.

Currently the REGEXP operator class caches the last regular expression. For queries with a single REGEXP operation this should work quite efficiently, but if more than one REGEXP operator is involved the current implementation might get inefficient. If this is an issue more than one regular expression could be cached at the expense of some overhead for managing the cache.

Regards,

Ulrich