SQLite3 Framework updated to 1.11 - including engine 3.7.4

ab · 2010-12-13 19:48:11

The Synopse SQLite3 Database Framework was just released under version 1.11: - internal SQLite3 database engine is updated to version 3.7.4;
- new TSQLRecordMany to handle "has many" and "has many through" relationships;
- new TSQLRecordFTS4 class, to handle the new FTS4 extension module;
- new RANK() function available in SQL statements for ranking FTS3/FTS4;
- introduces new TSQLite3DB, TSQLite3Statement, TSQLite3Blob, TSQLite3Value and TSQLite3FunctionContext types to clarify SQLite3 internal handle usage;
- new sqlite3_busy_timeout and sqlite3_busy_handler low-level functions, with new TSQLDataBase.BusyTimeout property;
- now handles User Defined Functions, via sqlite3_create_function_v2 and corresponding sqlite3_result_* functions;
- new MOD() and SOUNDEX() functions available in SQL statements;
- a lot of code refactoring, speed improvements, numerous fixes, new methods.

The main SQLite3 engine has been upgraded to the brand new version 3.7.4. Its major change is about the new FTS4 Free Text Search module, which is an enhancement to FTS3.

Internally, our framework has been refactored a lot since version 1.10. We're preparing the upcoming mORMot project... that's it an ORM framework able to easily communicate with other DB than SQLite3. We began a fork of Zeos some days ago, in order to have direct access to a whole range of other database engine.
The "Many to Many" relationships are now handled natively and in an automated matter by our ORM framework (for instance, the pivot table is created and updated automatically).

Here are the modifications, per each unit:

SynCommons.pas

- fix some obscure Delphi 2009 bug according to NativeUInt
- source code modified to be 7 bit Ansi (so will work with all encodings)
- a lot of code refactoring for our internal fork of ZeosLib (e.g. ISO 8601 date time extracted from SQLite3Commons, QuotedStr..)
- new TRawUTF8List class, which is able to emulate a TStringList with our native UTF-8 string type (cross-compiler, from Delphi 6 up to XE)
- new TRawUTF8Stream class, to typecast a RawUTF8 into a TStream
- new IsWow64 and SystemInfo global variables

SQLite3Commons.pas

- update SQLite3 engine to version 3.7.4
- new TSQLRecordProperties class, used internally by TSQLRecord to access to the RTTI via some high-level structures (therefore save memory for each TSQLRecord instance, and make operations faster)
- new TSQLRecordFill class, used internally by TSQLRecord.FillPrepare() to save memory: a TSQLRecord instance has now only 20 bytes of InstanceSize
- TSQLRecord.ID reader has now a GetID() getter which can handle the fact that a published property declared as TSQLRecord (sftID type) contains not a true TSQLRecord instance, but the ID of this record: you can use aProperty.ID method in order to get the idea - but prefered method is to typecast it via PtrInt(aProperty), because GetID() relies on some low-level windows memory mapping trick
- new TSQLRecordMany to handle "has many" and "has many through" relationships
- TSQLRestServer.AfterDeleteForceCoherency now handles specifically TRecordReference, TSQLRecord (fixed) and new 'has many' Source/Dest fields (this is our internal "FOREIGN KEY" implementation - we choose not to rely on the database engine for that, in order to be engine-independent... and SQLite3 introduced FOREIGN KEY in 3.6.19 version only)
- TSQLRestServer.AfterDeleteForceCoherency now synchronizes as expected TSQLRestServerStatic table content
- new TSQLRestServerStatic.SearchField method, for fast retrieval of all IDs matching a field of a TSQLRestServerStatic table (faster than using any OneFieldValues method, which creates a temporary JSON content)
- TSQLRecord.FillRow method has been made virtual, so that some calculated field can be initialize during table content enumeration
- corrected possible GPF error in TSQLRestServer.Retrieve
- sftMany/TSQLRecordMany field type handled as a not simple field
- new TSQLRecord.SimplePropertiesFill() method, to fill the simple properties with a given list of const parameters, following the declared order of published properties of the supplied table
- new TSQLRest.Add(aTable: TSQLRecordClass; const aSimpleFields: array of const) overloaded method to add a record from a supplied list of const parameters
- new TSQLRest.Update(aTable: TSQLRecordClass; aID: integer; const aSimpleFields: array of const) overloaded method to update a record from a supplied list of const parameters
- new class function TSQLRecord.SimpleFieldsCount
- FTS3Match method renamed FTSMatch, in order to be used without hesitation for both FTS3 and FTS4 classes
- new overloaded FTSMatch method, accepting ranking of MATCH, using the new RANK internal function - see http://www.sqlite.org/draft/fts3.html#appendix_a
- new TSQLRecordFTS4 class, to handle new FTS4 extension module - see http://sqlite.org/fts3.html#section_1_1 - which is available since 3.7.4
- new TSQLRecord.FillClose method
- new TSQLRecord.CreateAndFillPrepare() methods, to makes loop into records easier (an hidden TSQLTable is created and released by TSQLRecord.Destroy)
- new overloaded TSQLRestServer.CreateSQLIndex() method, accepting an array of field names
- new TSQLRecord.FillPrepare(const aIDs: TIntegerDynArray) overloaded method, which can be handy to loop into some records via an IDs set
- new TSQLTable.OwnerMustFree property for generic owning of a TSQLTable by a record - used for both CreateAndFillPrepare and TSQLRecordMany.FillMany
- better non-ascii search handling in TSQLTable.SearchValue
- source code modified to be 7 bit Ansi (so will work with all encodings)

SQLite3.pas

- update SQLite3 engine to version 3.7.4
- introduces new TSQLite3DB, TSQLite3Statement, TSQLite3Blob, TSQLite3Value and TSQLite3FunctionContext types to clarify SQLite3 internal handle usage
- new sqlite3_busy_timeout and sqlite3_busy_handler low-level functions
- new TSQLDataBase.BusyTimeout property, to set the database timeout value in milliseconds
- now handles User Defined Functions, via sqlite3_create_function_v2 and corresponding sqlite3_result_* functions: as sample, the MOD() function is defined in any database opened via TSQLDataBase (it was needed to have compatibility with Oracle/MySQL/MSSQL/PostGreSQL engines)
- protect the TSQLDatabase methods called when self is nil, which could occur if the database is not yet initialized (could occur if only a TSQLRestServerStatic exists, like in TTestSQLite3Engine._TSQLRestClientDB)
- new SOUNDEX() function available in SQL statements (calling SoundExUTF8) and associated SOUNDEXFR/SOUNDEXES for french or spanish Soundex variants
- fixed an issue found out by WladiD about all collation functions: If a field with your custom collate ISO8601 is empty '' (not NULL), then SQLite calls the registered collate function with length 0 for s1len or s2len, but the pointers s1 or s2 map to the string of the previous call
- added sqlite3_result_error() call to make wrong parameter count error explicit during SQL statement internal functions calls
- handles new FTS4 extension module - see http://sqlite.org/fts3.html#section_1_1 - which is available since 3.7.4
- new RANK() function available in SQL statements for ranking FTS3/FTS4 with best performance (used by the new TSQLRest.FTSMatch() overloaded method) - see http://www.sqlite.org/fts3.html#appendix_a
- fixed dual memory release in case of FTS3 use, in TSQLDataBase.Destroy
- source code modified to be 7 bit Ansi (so will work with all encodings)

This version compiles from Delphi 6 up to Delphi XE.

The full source code from the framework is available to download from http://synopse.info/files/SynopseSQLite3.zip
All licensed under a MPL/GPL/LGPL tri-license - see http://synopse.info/forum/viewtopic.php?id=27

edwinsn · 2010-12-14 05:11:34

the new FTS4 module is very interesting! However, I downloaded the latest version and tried to compile the SynFile sample project (the main demo), and got the following error, any hints for me?

[Error] FileClient.pas(135): Undeclared identifier: 'fSignatureTime'

ab · 2010-12-14 07:08:56

Indeed, I didn't try to recompile all demos.

Try to replace it with SignatureTime.

ab · 2010-12-14 19:39:14

I've fixed the issue.

Now available in the official .zip archive and the source code repository.

Thanks for the feedback!

edwinsn · 2010-12-15 03:39:11

Thanks ab,

FileMain.RES and Vista.res is still missing, for the fomer I can build it with brcc32.exe from FileMan.rc, but there is no file called vista.rc.

Anyway, I get it compiled and see the DEMO. the SQLITE ENGINE is a great lib, really! But I'm not sure if others are interested in the UI-generation, but obviously I'm not.

BTW, is FTS4 default included in the engine? I mean, do you have to download a separate package or do some manual linking in order to get FTS4? Thanks!

ab · 2010-12-15 07:01:14

You've got the Vista.res file in the source code repository:
see http://synopse.info/fossil/finfo?name=LVCL/vista.RES

UI generation is only one part of the framework. For applications based on tables + reports, it could save a lot of time. And for the user, having such a Ribbon on the top to select the table, then action buttons just below, the list of all records on the left and the current report on the right, is just easy to use. Reports are a good way of showing data: it's very familiar for the user to make the data available as if it was printed no a paper sheet.
I've made some advanced tools with this UI layout, in a few days of coding.

Both FTS3 and FTS4 are included in the engine.
You'll have to inherit your table definition from TSQLRecordFTS3 or TSQLRecordFTS4, depending on the engine you want to use.
But it sounds like TSQLRecordFTS4 is to be used for any new projects, according to official SQLite documentation.

Thanks for your interest!

edwinsn · 2010-12-16 04:49:31

Thanks for the explanation, ab! Excellent work! I would recommend you enhance the document for the SQLITE ENGINE, I thin it's **CRUCIAL**.

ab · 2010-12-16 07:31:55

edwinsn wrote:

Thanks for the explanation, ab! Excellent work! I would recommend you enhance the document for the SQLITE ENGINE, I thin it's **CRUCIAL**.

I'm working on it.
Perhaps with some User guide, detailing how the SynFile main demo works.

mORMot Open Source

#1 2010-12-13 19:48:11

SQLite3 Framework updated to 1.11 - including engine 3.7.4

#2 2010-12-14 05:11:34

Re: SQLite3 Framework updated to 1.11 - including engine 3.7.4

#3 2010-12-14 07:08:56

Re: SQLite3 Framework updated to 1.11 - including engine 3.7.4

#4 2010-12-14 19:39:14

Re: SQLite3 Framework updated to 1.11 - including engine 3.7.4

#5 2010-12-15 03:39:11

Re: SQLite3 Framework updated to 1.11 - including engine 3.7.4

#6 2010-12-15 07:01:14

Re: SQLite3 Framework updated to 1.11 - including engine 3.7.4

#7 2010-12-16 04:49:31

Re: SQLite3 Framework updated to 1.11 - including engine 3.7.4

#8 2010-12-16 07:31:55

Re: SQLite3 Framework updated to 1.11 - including engine 3.7.4

Board footer