Recent

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

tatamata

  • Hero Member
  • *****
  • Posts: 757
    • ZMSQL - SQL enhanced in-memory database
ZMSQL - TBufDataset SQL enhanced in-memory database
« on: July 13, 2011, 07:40:06 pm »
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
« Last Edit: January 09, 2012, 06:46:59 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 #1 on: July 14, 2011, 12:51:53 pm »
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
« Last Edit: July 14, 2011, 12:53:41 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 #2 on: July 14, 2011, 01:00:27 pm »
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
 :(
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: 757
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #3 on: July 14, 2011, 09:44:08 pm »
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

  • Hero Member
  • *****
  • Posts: 757
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #4 on: July 15, 2011, 06:36:35 am »
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

  • Hero Member
  • *****
  • Posts: 757
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #5 on: July 15, 2011, 06:50:26 am »
Also, jansql currently always uses dots as decimal separator. It should be enabled to use localized OS settings for such things.

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 #6 on: July 15, 2011, 08:57:11 am »
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
--RecordEnd
should 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  :D

Quote from: tatamata
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).
« Last Edit: July 15, 2011, 09:01:30 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: 757
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #7 on: July 17, 2011, 09:41:40 am »
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.
« Last Edit: July 17, 2011, 09:45:39 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 #8 on: July 19, 2011, 09:25:44 am »
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
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: 757
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #9 on: July 27, 2011, 10:54:49 am »
It seems I want to make something similar to commercial SQLMemTable for Delphi :D:
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?
« Last Edit: July 27, 2011, 11:13:46 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 #10 on: July 27, 2011, 02:11:26 pm »
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)
« Last Edit: July 27, 2011, 02:14:05 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

tatamata

  • Hero Member
  • *****
  • Posts: 757
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #11 on: July 28, 2011, 09:20:00 am »
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.
« Last Edit: July 28, 2011, 09:23:52 am by tatamata »

tatamata

  • Hero Member
  • *****
  • Posts: 757
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #12 on: August 03, 2011, 11:57:01 pm »
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.
« Last Edit: August 04, 2011, 09:42:27 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 #13 on: August 04, 2011, 09:24:25 am »
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...
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: 757
    • ZMSQL - SQL enhanced in-memory database
Re: ZMSQL - TBufDataset SQL enhanced in-memory database
« Reply #14 on: August 04, 2011, 09:45:20 am »
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?

 

TinyPortal © 2005-2018