ZMSQL

From Lazarus wiki
Revision as of 18:08, 4 October 2011 by Chronos (talk | contribs) (category)
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

Latest change: 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

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