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 licensed under a MPL/GPL/LGPL tri-license.

Feedback and comments are welcome on our forum!