Recent

Author Topic: ZMSQL - TBufDataset SQL enhanced in-memory database  (Read 100141 times)

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #30 on: September 02, 2011, 08:34:06 am »
I've uploaded release 0.1.5 source files to a Mercurial repository on
https://bitbucket.org/reiniero/zmsql

You can download the source as a zip/bz/gz2 via
https://bitbucket.org/reiniero/zmsql/downloads

I'm now writing a readme.txt based on tatamata's posts and will upload that soon.

Hope this helps people to download the files faster...
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #31 on: September 02, 2011, 09:25:38 am »
Hi tatamata & everyone,

I've added a readme file based on tatamata's forum posts and his remarks in the source code.

tatamata, could you check if it is correct? (It is attached here, as well).

Some questions/remarks:
1. I'm assuming your work is MPL licensed? I could add this to the readme.
2. What about zmbufdataset.pas?
bufdataset.pas in my 2.7.1 svn download links to copying.fpc which says:
Quote
The source code of the Free Pascal Runtime Libraries and packages are
distributed under the Library GNU General Public License
(see the file COPYING) with the following modification:

As a special exception, the copyright holders of this library give you
permission to link this library with independent modules to produce an
executable, regardless of the license terms of these independent modules,
and to copy and distribute the resulting executable under terms of your choice,
provided that you also meet, for each linked independent module, the terms
and conditions of the license of that module.
So I could add that to the readme, replacing
Quote
The source code of the Free Pascal Runtime Libraries and packages are
with
Quote
zmbufdataset.pas is
Might be polite to also add something like this to zmbufdataset.pas ...

Also while reviewing tatamata's posts/source code comments I see a possibility for improvement:

Quote
Why use 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 field types in the resultset are, and it will create ftString fields.
Therefore, if you know what field types should be used and want to further process ZMQueryDataset data, it is better to set fielddefs during designtime.

Enhancement request 1:
Implement an algorithm that tries to find out whether something is a date, text, currency, numeric field by going through the data on startup and writing a control file in the database directory containing:
1. the table name (i.e. reference to the csv file)
2. the field names
3. the field data types
This might be a tough task, but is independent from enhancement request 2.

Enhancement request 2:
If a control file as specified in request 1 exists (either written via request 1 or the end user), load the data types into JanSQL, probably similar to the way predefined field types are used.

Thanks,
BigChimp
« Last Edit: September 02, 2011, 09:34:23 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #32 on: September 02, 2011, 01:53:49 pm »
Hi, guys, thank you for interest and thanks BigChimp for putting code at bitbucket.
Regarding the licence, I have intentionally left it without any licence, though I suppose LGPL would be most appropriate. I am not sure however whether it is compatible with MPL 1.1 by which the JanSQL is licenced.
ZMBufDataSet is a temporary solution. If TBufDataset is going to be modified in a way to enable overriding some methods, it might become obsolete.
The priority should be JanSQL improving. I have done some modifications in Tokenizer and expression evaluator  and will share it these days, as soon as I catch some extra time. Things like outer joins and select distinct should be enabled.
BigChimp, you are absolutely free to try implement your suggestions regarding field types recognition etc.
We will have to see how to coordinate changes. Who will coordinate everything?

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #33 on: September 02, 2011, 03:22:00 pm »
Ok.

If you mean modified FPC LGPL (such as the bufdataset license), I think that would be fine - at least it won't create a third license in one package to deal with  :D. A quick look at http://en.wikipedia.org/wiki/Mozilla_Public_License seems to confirm FPC modified LGPL and MPL 1.1 are compatible.

Ok. If you have patches for bufdataset, please feel free to put them in Mantis... seems like you already have built a strong use case for them  :D If you need help for that, give me a shout.

I'll have a look at those things I suggested, but don't know when - so changes from your end shouldn't bite :D I'll post on the forum when I'm going to work on it.

I'm willing to coordinate stuff. For now it might be easiest, if you're willing, to send patches or changed files. If you're comfortable with mercurial, I can give you write access so you can edit your own files  8-)

Sooner rather than later, I think this should go into the Lazarus-CCR repository to avoid yet another repository. And while I'm happy to help with the mercurial repository, I haven't even fully run the code yet  :)
Maybe it's a good idea if I look into getting tatamata and me access to Lazarus CCR svn?

Finally: I take it the readme is ok?

Thanks
« Last Edit: September 02, 2011, 03:37:14 pm by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #34 on: September 03, 2011, 05:33:12 pm »
I've changed tatamata's sources to include a modified LGPL statement (same as bufdataset.pas), added license info in bufdataset.pas, and readme.txt
tatamata, if this is not ok, please let me know & I'll change it.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #35 on: September 04, 2011, 07:25:34 am »
It's OK.
Thanks, BigChimp!

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #36 on: September 04, 2011, 08:28:44 am »
Guys, my initial work on modifying JanSQL is attached. I have added some things to tokenizer and expression evaluator. Haven't progress a lot though :(
Now, if someone can implement those expressions (outer joins select distinct etc.) to JanSQL it would be great.
« Last Edit: September 04, 2011, 10:00:28 am by tatamata »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #37 on: September 04, 2011, 10:07:36 am »
Hi tatamata,

Thanks, changes uploaded, see
https://bitbucket.org/reiniero/zmsql
for the new juicy goodness....
« Last Edit: September 04, 2011, 10:30:06 am by BigChimp »
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Vincent Snijders

  • Administrator
  • Hero Member
  • *
  • Posts: 2660
    • My Lazarus wiki user page
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #38 on: September 20, 2011, 11:10:09 am »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #39 on: September 30, 2011, 03:25:14 pm »
Thanks, Vincent, created a wiki page for it at:
http://wiki.lazarus.freepascal.org/ZMSQL
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #40 on: November 18, 2011, 05:13:26 pm »
I hoped there would be more interest to contribute and improve this code...

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #41 on: December 28, 2011, 06:07:51 pm »
I have worked recently on improving some parts of zmsql.
Here is the link for new version (0.1.6):

http://www.4shared.com/zip/62W8zx0M/TZMSQL-016_2.html

Changes are following:
- 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 of 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.

Basically, I have been experienced problems with master-detail-detail...relationships, which forced me to move referential update logic from DoBeforePost to DoAfterPost.

BigChimp, I would kindly ask you to upload it to Lazarus CCR, if you agree with changes...

« Last Edit: December 31, 2011, 12:39:52 am by tatamata »

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #42 on: December 28, 2011, 06:42:13 pm »
Tatamata, as I haven't really studied the code, of course I agree with your suggestions. I had intended to look at ZMSQL but given my current projects/ideas and time, I'll probably be focusing more on sqlite and/or Firebird embedded..

It might actually be easier if you ask Vincent Snijders directly (I don't have SVN access), but I'vel sent him an email with the new zip for upload...

Some remarks regarding the files: there are some .bak files that could be removed I presume, as well as .compiled files in the lib directories for demo projects.
Also there's zmbufdatasetOLD.pas. Is that still needed?
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #43 on: December 28, 2011, 09:39:11 pm »
You are right, those files are not needed ;)

tatamata

  • Hero Member
  • *****
  • Posts: 693
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #44 on: January 08, 2012, 08:09:33 pm »
Recent changes in zmsql package:

ZMSQL version 0.1.8, 08 January 2012

* 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.
However, 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
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...

* In previous versions regular filtering was broken by DoFilterRecord everriding. It is solved now by adding:"if not Acceptable then exit;" after "inherited DoFilterRecord(Acceptable);". this way normal filtering functionality with Filter and Filtered properties is preserved.

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.)


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