ZMSQL - TBufDataset SQL enhanced in-memory database

[tatamata]

I'm just working on new version (0.1.10) of zmsql. I have rewritten CopyFromDataset method and few other things related.
One interesting possible usage of zmsql is to load data from "real" databases such as PostgreSQL, Firebird etc. into ZMQueryDatasets, perform some editing and then append results into real database again.
Once I had programmed such application in MS Access+PostgreSQL, in which case MS Access database was holding temporary data being transactionally appended to PostgreSQL. This is handy for complicated formulars that should have contain predefined values queried from database and input values in the same row. So you have to query database to create formulars (data input forms) from some predefined data matrix, while only one field is for user data input. When user finishes input, all rows are transactionally transferred to PostgreSQL database.
ZMQueryDataset could do the same.
Since I don't have any database server currently installed on my machine, could someone test this CopyFromDataset method with different database servers (PostgreSQL, Firebird....)?
The testing zmsql version is here:
http://www.4shared.com/zip/u1ww-Gv_/TZMSQL-0110.html


Thanks!

[CaptBill]

Thanks for your hard work with this. We really need this.
Wish I could help with testing.

Good luck with it.

[tatamata]

New version of zmsql, v. 0.1.11 is released.
See change log here:
http://wiki.lazarus.freepascal.org/ZMSQL#Change_Log
Download here: http://sourceforge.net/projects/lazarus-ccr/files/zmsql/

I must say I am very happy with this release, since now zmsql does not rely on American decimal separator any more.
Now zmsql recognizes system decimal and thousand separator and uses it for both loading data from csv tables and for sql queries.
Moreover, you can explicitly set the decimal separator in ZMConnection. In that case, this will override system settings.
Interestingly, if you load or query csv tables that have float values with dot decimal separator, but you set "," as decimal separator, when loading values, zmquerydataset will replace "." with ","...and vice versa...

[BigChimp]

Mmmm, yes, I have seen the release notes on the wiki page. Guess you and I disagree on using decimal points and commas, but that's fine as it's your project,... and you provide a way to retain the old behaviour.

Keep up the good work!

[tatamata]

Well, BigChimp, it's a practical issue.
I use zmsql for a material requirements planning for manufacturing. The program relies on data exported from SAP ERP, in .xls format. It is formatted with local decimal separator, in this case ",".
Previously I had to save .xls in .csv, then manually replace all "," with ".", then import csv into zmsql, then do calculation, then export results into .csv and manually replace "." with "," again....Too much complication for users...
I supppose most zmsql users will use it for similar purposes and it has sense to enable flexibility in choosing formatting and especially to use local formatting by default.

[BigChimp]

  I see your point, thought it would be something like that. Wouldn't agree with the local formatting by default (if everybody else is writing wrong output should we, too) but... I'm glad the flexibility is there.

[tatamata]

So, I think most of basic goals are acomplished:
- integration of SQL database engine into TBUfDataset descendant in-memory database
- flat textual database storage
- optional implicit or explicit save to underlying csv text file
- loading data from csv files (with .txt extension)
- loading data from sql queries perforemed on csv files (with .txt extension)
- loading from other datasets (any dataset)
- referential integrity between zmquery datasets (referential insert/update/delete)
- master/detail filtration
- parameterized queries (limited at the moment)
- flexibility in choosing decimal separator (local format by default, optionally overidden by explicitly set decimal separator)
- smart creation and usage of indexes
- explicit (by fielddefs definition) or on-the-fly implicit fields creation (during loading from csv or SQL query)

However, there is lot to improve:
- improve jansql database engine
- refactoring and optimization of existing code
- improving parameterized queries implementation
- improving zmbufdataset unit
- improving persistent save. Currently, if PersistentSave is enabled, on every post the whole dataset will be saved to csv file. It should be improved to be row-based, instead of saving the whole table.
- implemetation of AutoInc field type