ZMSQL - TBufDataset SQL enhanced in-memory database

[marcov]

Well, IMO alpha or beta says more about the quality of the existing functionality: unreliable means alpha, more reliable means beta.

IMO

Alpha: feature complete
Beta : core functionality generally works
gamma/production: all functionality should work.
stable : gamma+a period of feedback leading to bugfixes.

[tatamata]

New version (0.1.19) of Zmsql is available for download on Lazarus CCR: http://sourceforge.net/projects/lazarus-ccr/files/zmsql/

Main change is that new component, a visual query builder TZMQueryBuilder is added into Zmsql package.
TZMQueryBuilder is based on Open QBuilder Engine. TZMQueryBuilder uses TOQBEngineZmsql, which is TOQBEngine descendant. TOQBEngineZmsql is in based on code of the Open QBuilder Engine for SQLDB Sources created by Reinier Olislagers. I just modified and adapted for the ZMSQL. It incorporates QBuilder visual query builder(Copyright (c) 1996-2003 Sergey Orlik , Copyright (c) 2003 Fast Reports, Inc.)

Of course, visual query builder does not support yet non-standard janSQL statements, only standard SQL. And, vice versa, janSQL does not support all SQL statements generated by TZMQueryBuilder.

[eldonfsr]

Hello first let me say it is a great job i tested lats week only 2 thinks i can get to work with it, first one is if i put a dbgrid and add zmsql i can add records and edit but always dbgrid is in the first row position how is can insert a record and that go to the las row, or order a table.

second is possible to work with image store in zmsql temporary and later sava to database like sqlite ot mysql.

thanks all for that great job.

[mangakissa]

Code: [Select]

second is possible to work with image store in zmsql temporary and later sava to database like sqlite ot mysql.ZMSQL works with palintext, so how do you want to save an image in a field. It doesn't support blobfields

[tatamata]

Hello first let me say it is a great job i tested lats week only 2 thinks i can get to work with it, first one is if i put a dbgrid and add zmsql i can add records and edit but always dbgrid is in the first row position how is can insert a record and that go to the las row, or order a table.

second is possible to work with image store in zmsql temporary and later sava to database like sqlite ot mysql.

thanks all for that great job.

Hi,

Regarding sorting, ZMQueryDataset has a public function:

Code: [Select]

function SortDataset (const pFieldName:String):Boolean; //Ascending/Descending sorting of memory dataset.     You just have to pass name of the field for which you want dataset to be sorted. You might check source to see what SortDataset actually works . it is just changing index.  If you don't specify one, memory dataset is sorted by default index,  i.e. as loaded from a csv file.

Regarding position of inserting, it is matter of using ZMQueryDataset1.Insert vs, ZMQueryDataset1.Append. Insert inserts a record at the cursor position, while Append adds a record at the end of dataset. If you do it by using DBNavigator and DBGrid, I think that the corresponding button of the DBNavigator is Insert rather than Append. So If you want that the newly inserted record goes to end, then it is probably best solution to add an autoincrement type field (ftAutoInc), apply index to it (by SortDataset) and then any new record will go to end automatically after posting.

Regarding direct export to "real" databases, currently there is no special function. ZMQueryDataset has CopyFromDataSet method that creates memory dataset and imports data from other datasets on the fly, but there is no such straithforward method for exporting.
However, you might implement your own method: since TZMQueryDataset is TBufDataset descendent, this should not be difficult. You might look at the components you use for "real"databases, it is possible that they have importing from other datasets already implemented. If it can import from other datasets, than it surely can import from TZMQueryDataset too.

[tatamata]

Code: [Select]

second is possible to work with image store in zmsql temporary and later sava to database like sqlite ot mysql.ZMSQL works with palintext, so how do you want to save an image in a field. It doesn't support blobfields

Actually, TZMQueryDataset does not enforce loading/saving to csv files. It is just a convenient way to process data exported from spreadsheets. Actually, the main reason is that it is portable, ubiquitously readable format and that the janSQL database engine requires it to be able to process SQL queries. Looking at the source code of janSQL, it is obvious that original developer Jan Verhoeven planned to provide option of executing SQL on memory datasets instead on csv files. It would be great if someone finishes this functionallity  in jansql. In that case, ZMSQL might process SQL on memory dataset objects rather than on CSV files.

Since ZMQueryDataset is TBufDataset descendent, it can do everything that TBufDataset can do, including streamings. I think that TBufDataset can save and load blobfields that way, but I haven't tried it myself.

[tatamata]

I'd like to share my thoughts about possible direction of further zmsql development and how do I see what might be ultimate goal.

As I alredy mentioned, janSQL engine currently executes sql queries on csv files. The ZmQueryDataset than loads the resultset into itself, as in-memory dataset (TBufDataset descendent). However, janSQL might be reworked to query memory datasets instead of csv files. JAn Verhoeven clearly had it in mind, since there are relicts of unfinished work in the janSQL code.

We have great components (fpspreadsheet) to work with spreasheets. Spreadsheets can be easily exported to .csv files. ZMConnection defines zmsql database as a folder containing csv files. It is easy to program iteration through all spreadsheet files in the ZMConnection "database" folder and to export all spreadsheets into corresponding csv files. This way, we can execute sql queries on spreadsheets, indirectly, by intermediate exporting to corresponding csv files.
Actually, I have already done this, but feel uncomfortable with this intermediate step of exporting spreadsheets to csv files, then querying csv files, loading resultset into ZMQUeryDataset then exporting to csv. files then loading to spreadsheet again...It would be much better if spreadsheets would be directly queried inside zmquerydatasets, without intermediate exporting to csv...

So, I see two possible major goals to achieve:

A) to provide pure in-memory relational SQL database, where memory datasets are queried inside JanSQL, rather then querying corresponding csv files.

B) "MS Access"/"MS Excel" hybride - a package that defines database as folder containing spreadsheets, spreadsheets are treated as spreadsheets but can be additionaly  SQL queried, where resultset can be saved as new spreadsheet. This is already possible, but with unnecceessary step involving exporting spreadsheets to corresponding csv files.

I'm particulary excited with the latter idea. Imagine Excel providing SQL capabilities!

[eldonfsr]

Thanks for your answere was great and help full


veat

[mangakissa]

A) to provide pure in-memory relational SQL database, where memory datasets are queried inside JanSQL, rather then querying corresponding csv files.

TBufdataset already has procedures SaveToFile and LoadFromFile. So why providing in-memory relational SQL database?

[tatamata]

A) to provide pure in-memory relational SQL database, where memory datasets are queried inside JanSQL, rather then querying corresponding csv files.

TBufdataset already has procedures SaveToFile and LoadFromFile. So why providing in-memory relational SQL database?

Because of "relational" and "SQL" .

[totya]

New version (0.1.19) of Zmsql is available for download on Lazarus CCR: http://sourceforge.net/projects/lazarus-ccr/files/zmsql/

Hi, thanks for this job!

Slightly wrong, I get errors when I try compile the demo applications: Decimal separator unknow property.

Edit.: and Lazarus freeze very often , if I want to exit from Lazarus/zmsql demo.

[hrayon]

Hi tatamata,
I have problem when using TBufdataset descendants and DBGrid with multiselect.
ZMSql here in my pc too have the problem. The grid don't shows correctly the selected records.
Try it and see.

[tatamata]

Hi tatamata,
I have problem when using TBufdataset descendants and DBGrid with multiselect.
ZMSql here in my pc too have the problem. The grid don't shows correctly the selected records.
Try it and see.

Can you, please, documet the issue you faced? some demo app?

[hrayon]

Sure!
See here:
http://forum.lazarus.freepascal.org/index.php/topic,30492.0.html
Or woud be faster to you change any sample project that use DBGrid and ZMSQL.
Set DBGrid options->dgMultiselect = true.
Compile, execute and try selecting some records at same time.

[tatamata]

Sure!
See here:
http://forum.lazarus.freepascal.org/index.php/topic,30492.0.html
Or woud be faster to you change any sample project that use DBGrid and ZMSQL.
Set DBGrid options->dgMultiselect = true.
Compile, execute and try selecting some records at same time.

Hi! The ZMQueryDataset is a TBufDataset descendent and does not introduce any new behaviour in this regard.
So, this is really a question for guys maintaining TBufDataset. I think it is user "Lacak" on this forum?

Regards