ZMSQL - TBufDataset SQL enhanced in-memory database

[tatamata]

ZMSql is is an SQL enhanced in-memory database, operating with flat textual (csv) tables. Completely written in Pascal, it has no dependencies on external libraries.
You can download it from:
http://www.4shared.com/file/B8SDbOQ1/TZMSQL-015.html

UPDATE:

Last version can be downloaded from:
http://sourceforge.net/projects/lazarus-ccr/files/zmsql/
Wiki:
http://wiki.lazarus.freepascal.org/ZMSQL

ZMSql package is based on TBufDataset and TJanSql components. It consists of two components: ZMConnection and ZMQueryDataset. UPDATE: the third component (TZMReferentialKey) added.

TZMConnection defines folder containing csv (comma-separated) tables with .txt extension. In ZMSql, "database" is a folder containing csv tables.

TZMQueryDataset is a TBufDataset descendent, using TJanSQL database engine for executing SQL queries on csv tables, SdfDataset for loading data from csv tables and Dbexporter for exporting its data to csv table.

UPDATE: TZMReferentialKey provides a link between master and slave dataset, enabling referential integrity (update/insert/delete) between them.

ZMQueryDataset can load data both from csv table and from executed sql query. It can also copy data from some other dataset.
It introduces some new properties (SQLText, QueryExecuted, TableName, TableLoaded, TableSaved, PersistentSave) and methods(QueryExecute, EmptyDataSet, ClearDataSet, CopyFromDataset, SortDataset, LoadFromCSV, SaveToCSV) to its TBufDataset ancestor properties and methods.

Future plans:
- adding master/detail filtering -->UPDATE: completed!
- enforcing refferential integrity-->UPDATE: completed!
- improving JanSQL database engine
- enabling parameterized queries-->UPDATE: completed!
- to enable autoincrement field type

[BigChimp]

Hi tatamata,

Very good idea. I've just started to look into the demo project.

Am I right that the dataset is not comma-delimited but semicolon-delimited?
See ZMQueryDataset, line 385:
      FSdfDatasetImport.Delimiter:=';';

In any documentation, you might also mention that the data files are expected to have field names as first line (firstlineasschema).

When finished, it seems like it might be a very good fit with the other database components in the core Lazarus app - having a proper CSV type "database" would make it much easier for interoperability (import/export)!

I'll keep on investigating the software, especially with regards to my pet peeve with SDF as implemented in FPC: lack of multiline fields (i.e. carriage return/linefeed in quoted field ends the record as far as FPC is concerned),

Thanks for your work!

BigChimp

[BigChimp]

As I feared: line endings embedded in fields don't work due to the design of the SDFDataset in FPC.

if you have this file:
ID;Firstname;Initial;Lastname
4;Bobby;;"DROP
EMPLOYEES;"

(i.e. Bobby's last name has a line ending embedded), you'll get this output:
Row 1:
ID=4, Firstname=Bobby, Initial=NULL, Lastname="DROP
Row 2:
ID=EMPLOYEES, Initial=", Lastname=NULL
 

[tatamata]

BigChimp, yes, it is actually ";" delimited:)
The CSV format for TSdf and CSVExporter is set to follow predefined CSV specification of TJanSQL engine.
This way, you can immediately query such tables. For example, you execute a query, then save result dataset as csv, and then include it in some other query...
Regarding the line ending embedded, why do you need to have it inside a field? Some long memo text?

[tatamata]

The JanSQL database engine should be improved. It has its issues. For example, queries with joined tables with some filtering in WHERE clause mostly don't work as expected. They are extremly slow. This can be overcomed by using non-standard ASSIGN TO expression for filtering prior joining. Thus, insted 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;"
So, one of priorities should be to debbug jansql.

[tatamata]

Also, jansql currently always uses dots as decimal separator. It should be enabled to use localized OS settings for such things.

[BigChimp]

Regarding the line ending embedded, why do you need to have it inside a field? Some long memo text?

Yep, I tried to use sdfdataset to export records containing formatted results of a test script,
https://bitbucket.org/reiniero/db2securityscript/
(program code included there as well)
e.g. something like this:

Code: [Select]

--TestID:
Name
--TestDescription:
Informational: unique name for the tested server.
--Explanation:
Helps distinguish output from multiple scripts.
Combines host and instance information.
--Norm:
None
--Results:
db2server
db2inst1
--RecordEndshould give as schema:
TestID;TestDescription;Explanation;Norm;Results
The data in the explanation and results field is problematic for sdfdata....
That program uses a dbf file to store intermediate results; it exports to csv etc using FPC's fpdataexporter/fpstdexports, but as that uses sdfdata as well, multiline isn't supported...
With multiline support, I might use your dataset instead of DBF, so I have no need to export to csv...

I'm looking into writing a patch for enabling multiline support in sdfdata (adding a new property AllowMultiLine); hope I can get it working. If so, I'll submit it to the bug tracker...
If not, I'll yell for help here 

Also, jansql currently always uses dots as decimal separator. It should be enabled to use localized OS settings for such things.

I would strongly advise you to leave it as-is if jansql is used in storing data or writing sql queries, but you can change it if it is used in presenting data to the user.
I guess it is used for SQL queries. Then please leave it as . then so we can share snippets of SQL between various locales (including use in programs as "standard queries" for users to select) without problems.

I hear you about the SQL; haven't tried it yet. I do seem to remember that there is SQL parse code somewhere in FPC; I think I saw an announcement by one of the devs about that.... Maybe it's useful to stea.. use some of that.
(Sorry if this is doesn't make sense; haven't looked at SQL parsing yet).

[tatamata]

Interesting stuff with zmsql is about fielddefs. You can use predefined fielddefs (and their field types) created durign design-time or zmsql will create fielddefs on-the-fly during run-time.
If you predefine fielddefs during design-time and their number (TODO: to inspect matching of field names as well) matches number of fields in loading JanSQL dataset or csv dataset, zmsql will use existing fielddefs. Otherwise, it will delete previous fielddefs and recreate new fielddefs.
Bare in mind that you have to manually refresh DBGirds in order to refresh columns. Do it by refereshing datasource component, eg. Datasource1.Enabled:=False; Datasource1.Enabled:=True;
Why using design-time defined fielddefs? Simply because it is more efficient. JanSQL database engine is typeless. Everything is basically a string. Thus, zmsql can't know what are field types in resultset as well, it will create ftString fields as well.
Therefore, if you know what field types should be and want to furtherly process ZMQueryDataset, it is better to set fielddefs during design-time.

[BigChimp]

Regarding the line ending embedded, why do you need to have it inside a field? Some long memo text?

Yep, I tried to use sdfdataset to export records containing formatted results of a test script
...
I'm looking into writing a patch for enabling multiline support in sdfdata (adding a new property AllowMultiLine); hope I can get it working. If so, I'll submit it to the bug tracker...

[/quote]
SDFData multiline patch with test script added as bug 17285:
http://bugs.freepascal.org/bug_view_page.php?bug_id=17285

[tatamata]

It seems I want to make something similar to commercial SQLMemTable for Delphi :
http://www.aidaim.co/in-memory_sql_database_delphi.htm

I'm searching internet to find any hints how to implement master/detail relationship and cascade referential integrity. MasterDatasource, MasterFields...
I know how to do referential integrity externally, with bufdataset:
http://wiki.lazarus.freepascal.org/How_to_write_in-memory_database_applications_in_Lazarus/FPC#Enforcing_Referential_Integrity
But, how to implement master/detail inside zmsql?

Any hints?

[BigChimp]

Had a quick look at the bufdataset wiki section. Seems you're relying on keeping the master dataset in sync with the detail dataset...

Hadn't thought about it that way, my (maybe naive) thoughts would be:

On insert/update of detail (in some event): check whether the master foreign key stored in the detail table exists somewhere in the master table (***warning: air code*** SELECT count(id) from MASTER WHERE MASTER.ID=**parameter filled with current detail masterfk***).
If not, generate a referential integrity exception.

Cascading deletes: on delete of master, use an event to just execute DELETE FROM detail WHERE DETAIL.MASTERFK=***parameter filled with current master id***

But... seems as though you must already know this.... Or are you looking for an event to plug this detection code into? (I haven't looked into zmsql yet)

[tatamata]

ZMSQL 0.1.2 released. 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.
You can download from:
http://www.4shared.com/file/1BjyMSm_/TZMSQL-012.html
There is a Test project included in the package, where you can see how parameters are used.

[tatamata]

ZMSQL-0.1.3 is out:
http://www.4shared.com/file/fXEWOZAV/TZMSQL-013.html.
This version brings master/detail synchronization.
The zipped file contains the package with a test project included. The simple test project demonstrates master/detail synchronization and parameterized queries.

Next thing to solve is referential integrity.

[BigChimp]

Wow tatamata, you're cooking with gas here!

Congratulations on your progress!

By the way, my sdfdata multiline patch is committed to FPC:
http://bugs.freepascal.org/view.php?id=17285
You might want to enable support for it by setting the sdfdata.AllowMultiline property to true when required...

[tatamata]

BigChimp, gladly, but I am 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...
Could you test it and patch zmsql-0.1.3 if it works?