ZMSQL

From Lazarus wiki
Jump to navigationJump to search

About

ZMSQL is a TBufDataset SQL enhanced in-memory database.

ZMSQL is an open source, SQL enhanced in-memory database for FreePascal (FPC), operating with semicolon-separated values flat text tables. Completely written in Pascal, it has no dependencies on external libraries.

It offers:

  • Cross-platform flat text storage
  • Use of SQL to query the data
  • Option to predefine fielddefs
  • Master/detail filtering
  • Referential integrity
  • Parameterized queries

The download contains the source code, some demo applications illustrating the features of the component as well as a readme.

Author

Zlatko Matic; includes code from Jan Verhoeven and Martin Waldenburg.

License

Modified LGPL and MPL 1.1 license: static and dynamic linkin both allowed, even in commercial applications. Please see the individual licenses in the source files for details.

Contains: ZMSQL units, including zmbufdataset.pas, zmconnection.pas, zmquerydataset.pas, zmreferentialkey.pas: FPC modified LPGL

JanSQL units, including JanSQL.pas, janSQLExpression2.pas, janSQLStrings.pas, janSQLTokenizer.pas by Jan Verhoeven: Mozilla Public License Version 1.1 (MPL)

mwStringHashList.pas by Martin Waldenburg: Mozilla Public License Version 1.1 (MPL)

Download

The latest stable release can be found on http://sourceforge.net/projects/lazarus-ccr/files/zmsql/.

Change Log

For more details, see zmquerydataset.pas

Current version 0.1.8: 08 January 2012

ZMSQL version 0.1.8, 08 January 2012: by Zlatko Matić

  • Since there was no visible improvement in BufDataSet regarding "Refresh bug" for a very long time,

and it seems that TBufDataset is not maintained successfully any more,

I have decided to base zmsql on last stable TBufDataset version that performed Refresh method correctly,

which appears to be present in fpc 2.2.4.

Therefore, new zmbufdataset.pas and zmbufdatasetparser.pp units are now actually downgraded to fpc 2.2.4.

This choise will not change anymore and this is starting point for further independent development of zmbufdataset unit

as direct ancestor of zmquerydataset.

We can always come back to TBufDataset as direct ancestor of zmquerydataset (while preserving zmbufdataset as an alternative)

if following criteria is met:

 1. DoFilterRecord method is virtual and protected.
 2. Refresh method works correctly 
 3. Filtering works correctly

Until then, zmbufdataset remains direct ancestor of zmquerydataset and will fork from TBufDataset development.

  • Reconnecting of ZMConnection is added in QueryExecute and LoadFromCSV methods, each time data is loaded by query or import from csv.

This prevents peculiar inconsistences observed during query execution in some circumstances.

This odd behavior of jansql has to be throughly investigated...

  • "if not Acceptable then exit;" added After "inherited DoFilterRecord(Acceptable);",

in order to preserve normal filtering functionality with Filter and Filtered properties.

ZMSQL version 0.1.7, 01 January 2012

  • FDoReferentialUpdate:Boolean field added to signalize dsEdit state in DoBeforePost, which is then used in DoAfterPost as signal to perform referential update. This solved bug that caused referential update of all records in case of insert.
  • Added public read-only property OldRecord. This can be useful during run-time.

ZMSQL version 0.1.6, 28 December 2011

  • Referential update logic moved from DoBeforePost to DoAfterPost procedure, in order to solve problems with multilevel referential integrity.
  • UpdateFOldRecord triggering moved from DoAfterScroll and DoAfterPost to DoBeforeInsert, DoBeforeDelete and DoBeforeEdit.
  • Inspecting and matching of referential conditions for referential update changed from custom filtering to iteration and inspection for referential conditions for every record of SlaveDataset.
  • Bugs in PrepareQuery and DoFilterRecord, as well as few other small bugs are solved.
  • ZMBufDataSet updated with recent bufdataset changes.
  • ZMBufDataSetParser added to package.
  • Bug in Disconnect procedure solved.

Change commit: 4 September 2011

  • Added some things to JanSQL tokenizer and expression evaluator. Still needs implementation of those expressions (outer joins, select distinct etc.)

Version 0.1.5: 12 August 2011

  • Two small demo projects are included in the zip. First one demonstrates referential integrity, master/detail filtration, parameterized queries and difference between using predefined FieldDefs and FieldDefs created on the fly. The second one demostrates loading and saving to CSV table.
  • Improvements: ZMSQL now enables decimal separator to be chosen (new property in TZMConnection) for ftFloat field type. It does not influence default CSV save format, but only data representation.
    • Caution: Changing value of a ZMConnection.DecimalSeparator will change SysUtils.DefaultFormatSettings as well, so this will influence on the whole application!
  • If you leave ZMConnection.DecimalalSeparator empty, default format settings are going to be used
  • Added overloaded procedure, procedure SaveToCSV (pDecimalSeparator);overload; so that you can save a dataset with custom decimal separator. The regular SaveToCSV uses dot as decimal separator, because JanSQL database engine uses that format.

Version 0.1.4: 8 August 2011

  • Successfully implements REFERENTIAL INTEGRITY!
  • A simple test project is provided inside the package folder and demostrates new features from zmsql 0.1.3 and 0.1.4:
    • queries with joined tables
    • master/detail filtration/synchronization
    • referential integrity (insert/update/delete)
    • parameterized queries

Version 0.1.3: 2 August 2011

  • This version brings master/detail synchronization.
  • The test project now also demonstrates master/detail synchronization and parameterized queries.

Version 0.1.2: 28 July 2011

  • This version brings initial support for parameterized queries (it borrows TParams collection from SQLDB).
  • Currently, this support is very basic, parameters must be passed by name (ParamByName) and their values are passed as string literals to SQL string before query execution.
  • There is a Test project included in the package, where you can see how parameters are used.

Version 0.1.1: 26 July 2011

  • QueryExecute method fixed. SqlText TStrings property must be transformed to String prior to passing it JanSQL engine and spaces must be inserted between Sqltext lines.

Version 0.1.0: 13 July 2011

Dependencies / System Requirements

  • None

Status: Alpha

Open issues/improvement ideas

Below is a list of open issues (or bugs, if you want) and improvement ideas. If you have any patches/fixes for the code (not limited to these issues), please let use know via the ZMSQL thread on the forum: [1] Of course, ZMSQL is part of the Lazarus CCR repository, so you can post bug reports/patches on Mantis using the CCR category.

1. TBufDataset bug Currently TBufDataSet (ancestor of TZMQueryDataSet) has a bug (http://bugs.freepascal.org/view.php?id=19631 is solved!) that causes Refresh method to delete all records

2. Slow query execution in JanSQL Extremely slow query execution when more than one table joined in query when there is an additional where clause in the query. It can be overcome with the "ASSIGN TO variable" non-standard expression First execute query on a table with where clasue, assign resultset to a variable and then use the variable in a second query (instead of the table).

Thus, instead of: "SELECT ordrs.ordr AS order, ordrs.ordr_type AS order type,ordrs.prdct AS product,ordrs.prdct_dscr AS product description,ordrs.prdct_targ_qty AS order quantity,ordrs.prdct_targ_qty_unt AS order unit,rqrmts.cmpnt AS component,rqrmts.cmpnt_dscr AS component description,rqrmts.cmpnt_rqrd AS required quantity of component FROM ordrs,rqrmts WHERE ordrs.ordr=rqrmts.ordr AND ordrs.prdct=10010356;", use this non-standard SQL expression: "ASSIGN TO temp_ordrs SELECT * from ordrs WHERE prdct=10010356; SELECT temp_ordrs.ordr AS order, temp_ordrs.ordr_type AS order type,temp_ordrs.prdct AS product,temp_ordrs.prdct_dscr AS product description,temp_ordrs.prdct_targ_qty AS order quantity,temp_ordrs.prdct_targ_qty_unt AS order unit,rqrmts.cmpnt AS component,rqrmts.cmpnt_dscr AS component description,rqrmts.cmpnt_rqrd AS required quantity of component FROM temp_ordrs,rqrmts WHERE temp_ordrs.ordr=rqrmts.ordr;"

3. JanSQL has problems with typecasts.

4. Parameter support is currently quite limited. Basically, named parameters must be used and they are replaced by its values as literal strings. You must enclose parameter identifiers in SQL string by quotes!

5. Multiline support Investigate setting the sdfdata.AllowMultiline property to true when required to allow CSV data that contains line breaks. See http://bugs.freepascal.org/view.php?id=17285 Not sure whether JanSQL database engine is able to handle this? The parameters set for SdfDataset in zmsql corresponds to the csv format that JanSQL uses

Installation

  • Unpack
  • Compile

The Demo Applications

Installation

  • Open the applications in TestProject and TestProject1
  • compile
  • run