ZMSQL - TBufDataset SQL enhanced in-memory database lazarus / 16-Databases ---------------------------------------------------------------------- tatamata · 2011-07-13 17:40 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 · 2011-07-14 10:51 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 · 2011-07-14 11:00 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 · 2011-07-14 19:44 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 · 2011-07-15 04:36 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 · 2011-07-15 04:50 Also, jansql currently always uses dots as decimal separator. It should be enabled to use localized OS settings for such things. ---------------------------------------------------------------------- BigChimp · 2011-07-15 06:57 Quote from: tatamata on July 14, 2011, 09:44:08 pmRegarding 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  Quote from: tatamataAlso, 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 · 2011-07-17 07:41 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 · 2011-07-19 07:25 Quote from: BigChimp on July 15, 2011, 08:57:11 amQuote from: tatamata on July 14, 2011, 09:44:08 pmRegarding 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 · 2011-07-27 08:54 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_app lications_in_Lazarus/FPC#Enforcing_Referential_Integrity But, how to implement master/detail inside zmsql? Any hints? ---------------------------------------------------------------------- BigChimp · 2011-07-27 12:11 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 · 2011-07-28 07:20 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 · 2011-08-03 21:57 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 · 2011-08-04 07:24 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 · 2011-08-04 07:45 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? ---------------------------------------------------------------------- tatamata · 2011-08-04 08:01 Testers, please check whether you can use MasterFields TStrings property from Lazarus IDE in design-time! For some reason (can't see what is wrong) I am not able to use it (Lazarus 0.9.30/fpc 2.4.2, Linux), because it always crashes, though it works through code in run-time... ---------------------------------------------------------------------- BigChimp · 2011-08-04 08:57 Regarding multiline support Quote from: tatamata on August 04, 2011, 09:45:20 amBigChimp, 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? Tatamata, I'll give it a go, but don't know when I'll be able to get to it. I'll let you know. I'll then also test if I can use the MasterFields TStrings property from Lazarus IDE in design-time. Thanks, BigChimp PS: If you're using a version control system such as svn, git or mercurial, could you perhaps upload the code to github, bitbucket, google code or sourceforge? Or maybe even better, to Lazarus-CCR? This will allow people to easier generate patches etc. ---------------------------------------------------------------------- tatamata · 2011-08-04 09:56 BigChimp, currently I don't use any But, after I finish referential integrity, I will put it to some of those you have enlisted. As the matter affects, I would gladly leave this project to be coordinated by someone more experienced than me (I'm not a programer, I'm just a pharmacist)... At this moment I just want to demonstrate proof of concept, to prove it is possible to create lite but real referential DBMS based on CSV, in-memory dataset and pure Lazarus/FPC, without depending on external C/C++ libraries. ---------------------------------------------------------------------- tatamata · 2011-08-08 14:25 I am happy to announce new version of zmsql, version 0.1.4, that successfully implements REFERENTIAL INTEGRITY! You can download it from: http://www.4shared.com/file/1rrec25v/TZMSQL-014.html A simple test project is provided inside the package folder and demostrates new features from zmsql 0.1.3 and 0.1.4: queries with joined tablesmaster/detail filtration/synchronizationreferential integrity (insert/update/delete)parameterized queries Referential integrity is basically implemented by new component TZMReferentialKey that represents a referential key. It connects master and slave dataset and defines linking fields and kind of referential integrity (set of (rkInsert, rkUpdate, rkDelete). Important to note, master/detail filtratio and referential integrity are implemented as separate independent features. From my point of view, this is successful proof of concept that it is possible to create real relational, SQL, embedded, cross-platform, in- memory database completely written in Pascal. I have completed this package during my holidays that just ended today  and I'm afraid that I will not be able to continue intensive development in following weeks and months, since I am going to be very busy in my daily job... Therefore, I would be very happy if there is an interested passionate experienced developer that could take-over this project and continue this effort . ---------------------------------------------------------------------- BigChimp · 2011-08-08 15:29 tatamata, Congrats on your achievements! Hope you had a nice holiday doing this... I'm not really experienced, and haven't seen the code yet. I'll first have a look before getting back to you. To be honest, my mind is spinning a bit with all the various mem datasets floating around, each with some deficiencies...  Apart from that, it's not easy to find documentation for a Delphi newbie like me... ---------------------------------------------------------------------- tatamata · 2011-08-12 13:03 Hello everybody. TZMSQL version 0.1.5 is here. Can be downloaded from: http://www.4shared.com/file/B8SDbOQ1/TZMSQL-015.html Two small demo projects are included in the zip. First one demonstrates referential integrity, master/detail filtration, parameterized queries and difference between using predefined FieldDefs and FieldDefs created on the fly. The second one demostrates loading and saving to CSV table. Improvements: ZMSQL now enables decimal separator to be chosen (new property in TZMConnection) for ftFloat field type. It does not influence default CSV save format, but only data representation. Caution: Changing value of a ZMConnection.DecimalSeparator will change SysUtils.DefaultFormatSettings as well, so this will influence on the whole application! If you leave ZMConnection.DecimalalSeparator empty, default format settings are going to be used Added overloaded procedure, procedure SaveToCSV (pDecimalSeparator);overload; so that you can save a dataset with custom decimal separator. The regular SaveToCSV uses dot as decimal separator, because JanSQL database engine uses that format. ---------------------------------------------------------------------- fredycc · 2011-08-12 14:22 Great job tatamata, clear and good examples  Thanks Regards ---------------------------------------------------------------------- tatamata · 2011-08-12 14:39 Thank you for comment, fredycc, I'm glad you like it! Now, next issue is to enable ftAutoInc... It is read-only in BufDataSet... The question is where is it declared to be read-only? in db or bufdataset or...? And how to override it to become editable? From where to start? Any hints? ---------------------------------------------------------------------- BigChimp · 2011-08-13 06:47 Don't know much about ftautoinc but isn't it an automatically incrementing integer? In that case it isn't strange it's read only for the developer. However, I think in the current bufdataset it just isn't initialised - at least it doesn't return any values when I read it. FYI, on the fpc mailing list, there's been some effort to improve bufdataset: http://lists.freepascal.org/lists/fpc-pascal/2011-August/029963.html The patch mentioned doesn't include ftAutoInc in the list of supported fields in BufDataset... ---------------------------------------------------------------------- joseme · 2011-08-27 15:59 ZMSQL is very useful for portable apps. Thanks a lot! ---------------------------------------------------------------------- Leledumbo · 2011-08-27 18:01 I think I'm interested in this package, off to download... * Need to wait for 500 seconds on 4shared, you really need a better place to upload. Choose google code or sourceforge instead, that could also promote Lazarus/FPC. ---------------------------------------------------------------------- tatamata · 2011-08-28 09:10 Hi, Leledumbo. Actually, I would prefer if someone experienced like you take-over this project and put it on sourceforge or what ever...and continue its development. ---------------------------------------------------------------------- Leledumbo · 2011-08-28 09:30 Unfortunately I have no background in DBMS implementation, only in its usage (although it shouldn't be hard to learn). Maybe I could create a google code or sourceforge page, but that's all I can do for now. Anyway, you could remove binaries from the package, and create a separate download. This adds unnecessary size to the download. ---------------------------------------------------------------------- 99Percent · 2011-09-02 00:31 This looks really interesting, with huge potential. I agree that binaries should be removed and the project put up on sourceforge or google code preferrably with git access. ---------------------------------------------------------------------- Takeda · 2011-09-02 03:54 Quote from: Leledumbo on August 27, 2011, 08:01:31 pm...... Need to wait for 500 seconds on 4shared, you really need a better place to upload.... Yes, I feel the same too.. Why Tatamata you not upload it into Mediafire or other fast place?? Or you can share it into Softpedia.. Regards, Takeda. ---------------------------------------------------------------------- BigChimp · 2011-09-02 06:34 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... ---------------------------------------------------------------------- BigChimp · 2011-09-02 07:25 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: QuoteThe 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 QuoteThe source code of the Free Pascal Runtime Libraries and packages arewith Quotezmbufdataset.pas isMight 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: QuoteWhy 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 ---------------------------------------------------------------------- tatamata · 2011-09-02 11:53 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 · 2011-09-02 13:22 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  . 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  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 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  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 ---------------------------------------------------------------------- BigChimp · 2011-09-03 15:33 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. ---------------------------------------------------------------------- tatamata · 2011-09-04 05:25 It's OK. Thanks, BigChimp! ---------------------------------------------------------------------- tatamata · 2011-09-04 06:28 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. ---------------------------------------------------------------------- BigChimp · 2011-09-04 08:07 Hi tatamata, Thanks, changes uploaded, see https://bitbucket.org/reiniero/zmsql for the new juicy goodness.... ---------------------------------------------------------------------- Vincent Snijders · 2011-09-20 09:10 Uploaded to Lazarus-CCR: http://sourceforge.net/projects/lazarus-ccr/files/zmsql/ ---------------------------------------------------------------------- BigChimp · 2011-09-30 13:25 Thanks, Vincent, created a wiki page for it at: http://wiki.lazarus.freepascal.org/ZMSQL ---------------------------------------------------------------------- tatamata · 2011-11-18 16:13 I hoped there would be more interest to contribute and improve this code... ---------------------------------------------------------------------- tatamata · 2011-12-28 17:07 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... ---------------------------------------------------------------------- BigChimp · 2011-12-28 17:42 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? ---------------------------------------------------------------------- tatamata · 2011-12-28 20:39 You are right, those files are not needed ---------------------------------------------------------------------- tatamata · 2012-01-08 19:09 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 ---------------------------------------------------------------------- tatamata · 2012-01-21 09:27 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 · 2012-01-21 09:52 Thanks for your hard work with this. We really need this. Wish I could help with testing. Good luck with it. ---------------------------------------------------------------------- tatamata · 2012-02-06 05:52 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 · 2012-02-06 06:37 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 · 2012-02-06 21:29 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 · 2012-02-07 06:17 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 · 2012-02-07 21:35 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 ---------------------------------------------------------------------- almsoares · 2012-05-23 19:04 I start use ZMSQL and I saw that you are doing a good job in this component. So I found a kind of error in the function TZMQueryDataSet.InspectFieldDefs:Boolean; the line is:    sdOtherDataset:vNewFieldNames:=vNewFieldNames FSdfDatasetImport.FieldDefs.Name ';';    and the correct is:    sdOtherDataset:vNewFieldNames:=vNewFieldNames FOtherDatasetImport.FieldDefs.Name ';';    Thanks ---------------------------------------------------------------------- fredycc · 2012-05-27 23:19 QuoteSince I don't have any database server currently installed on my machine, could someone test this CopyFromDataset method with different database servers (PostgreSQL, Firebird....)? I'll try to test it in my Firebird database  Regards ---------------------------------------------------------------------- hrayon · 2012-06-17 14:02 Hello! I found a small bug in "destructor TZMQueryDataSet.Destroy;" If "FRecordsetIndex" was not initialized, then after to delete a "ZMQueryDataSet" from a form, an error is generated. The initialization is in "procedure TZMQueryDataSet.QueryExecute;". Maybe, initializing in "constructor TZMQueryDataSet.Create(AOwner: TComponent);" and checking if it was initialized before executing: "ZMConnection.JanSQLInstance.ReleaseRecordset(FRecordsetIndex);" helps. Regards ---------------------------------------------------------------------- tatamata · 2012-06-17 19:37 @almsoares and @hrayon, thanks for reporting the bugs. Currently i don't work actively on new version, if I do I will take care if these reported issues. In meantime, you are all free to prepare new zmsql version your own and upload it to Lazarus CCR. Just send the zipped source code to Vincent... ---------------------------------------------------------------------- teos · 2012-08-20 14:05 I wonder if someone has a ZMSQL management tool and if folks are working on this database. I was about starting to develop a client-server database for Lazarus, but maybe this is a bit more interesting. What is required to take over the project? (as experienced Delphi developer, also with knowledge in TDataset) ---------------------------------------------------------------------- tatamata · 2012-08-20 19:50 Quote from: teonieuwlande on August 20, 2012, 04:05:24 pmI wonder if someone has a ZMSQL management tool and if folks are working on this database. I was about starting to develop a client-server database for Lazarus, but maybe this is a bit more interesting. What is required to take over the project? (as experienced Delphi developer, also with knowledge in TDataset) Hi! What do you mean by "ZMSQL Management tool"? If you are willing to continue where I stopped, you are welcome to take it over! Just download the source-code and play with it and yo can ask Vincent to upload your newversion of zmsql. Or you can ask Vincent to allow you to upload directly to sourceforge. Ask Vincent! I used to send him zipped folder with new zmsql version and he would upload it to Sourceforge... ---------------------------------------------------------------------- tatamata · 2012-08-20 20:17 Quote from: tatamata on August 20, 2012, 09:50:22 pmQuote from: teonieuwlande on August 20, 2012, 04:05:24 pmI wonder if someone has a ZMSQL management tool and if folks are working on this database. I was about starting to develop a client-server database for Lazarus, but maybe this is a bit more interesting. What is required to take over the project? (as experienced Delphi developer, also with knowledge in TDataset) Hi! What do you mean by "ZMSQL Management tool"? If you are willing to continue where I stopped, you are welcome to take it over! Just download the source-code and play with it and yo can ask Vincent to upload your newversion of zmsql. Or you can ask Vincent to allow you to upload directly to sourceforge. Ask Vincent! Maybe by management tool you mean something like my simple MightyQuery? Download setup files: http://matalab.freehostia.com/MightyQuery-1.0.9.exe (Win 32) http://matalab.freehostia.com/MightyQuery-1.0.9.pet (Puppy Linux) Download portable binaries for Linux and Windows: http://matalab.freehostia.com/MightyQuery-Binary.zip (Just unzip the folder. Both Linux and Windows binaries are inside the folder.) Source code is available for download, too: http://matalab.freehostia.com/MightyQuery-Source.zip ---------------------------------------------------------------------- teos · 2012-08-22 11:17 Yes, That is what I ment with database management tool.  I will give the database a try first, see what it is and how it works. ---------------------------------------------------------------------- BigChimp · 2012-09-24 15:23 @Tatamata & zeosmsql users: if you think sdfdataset has bugs, please feel free to add test cases for those bugs in tcsdfdata.pp see http://lazarus.freepascal.org/index.php/topic,18224.msg103314.html#msg 103314 for details If these test cases are accepted into FPC trunk, adapting sdfdataset to match these test cases will be justified. Thanks, BigChimp Edit: sdfdataset is probably going to support RFC4180 CSV format instead of SDF. See http://www.mail-archive.com/fpc- pascal@lists.freepascal.org/msg30087.html ---------------------------------------------------------------------- irw10062 · 2012-10-05 03:21 is ZMSQL required library / dependency ?? i see http://wiki.freepascal.org/ZMSQL QuoteDependencies / System Requirements None Status: Alpha i test use TDbf and TBufDataset is success compile but using TZMConnection, TZMQueryDataset this i get error in message arm-linux QuoteC:\codetyphon\fpc\units\arm-linux\rtl\cprt0.o: In function `_start': (.text+0x4c): undefined reference to `__libc_start_main' C:\codetyphon\fpc\units\arm-linux\rtl\cprt0.o: In function `_haltproc_eabi': (.text+0x88): undefined reference to `_fini' C:\codetyphon\fpc\units\arm-linux\rtl\cprt0.o: In function `_haltproc_eabi': (.text+0x90): undefined reference to `_init' C:\codetyphon\fpc\units\arm-linux\rtl\clocale.o: In function `CLOCALE$ _$GETFORMATSETTINGS$TFORMATSETTINGS_$$_GETLOCALECHAR$LONGINT$$CHAR': clocale.pp:(.text.n_clocale$_$getformatsettings$tformatsettings_$$_get localechar$longint$$char+0x10): undefined reference to `nl_langinfo' C:\codetyphon\fpc\units\arm-linux\rtl\clocale.o: In function `CLOCALE$ _$GETFORMATSETTINGS$TFORMATSETTINGS_$$_GETLOCALESTR$LONGINT$$SHORTSTRI NG': clocale.pp:(.text.n_clocale$_$getformatsettings$tformatsettings_$$_get localestr$longint$$shortstring+0x40): undefined reference to `nl_langinfo' smartmobile.lpr(22) Error: Error while linking smartmobile.lpr(22) Fatal: There were 1 errors compiling module, stopping ---------------------------------------------------------------------- marcov · 2012-10-05 07:58 Quote from: irw10062 on October 05, 2012, 05:21:24 am QuoteC:\codetyphon\fpc\units\arm-linux\rtl\cprt0.o: In function `_start': (.text+0x4c): undefined reference to `__libc_start_main' C:\codetyphon\fpc\units\arm-linux\rtl\cprt0.o: In function `_haltproc_eabi': (.text+0x88): undefined reference to `_fini' C:\codetyphon\fpc\units\arm-linux\rtl\cprt0.o: In function `_haltproc_eabi': (.text+0x90): undefined reference to `_init' C:\codetyphon\fpc\units\arm-linux\rtl\clocale.o: In function `CLOCALE$ _$GETFORMATSETTINGS$TFORMATSETTINGS_$$_GETLOCALECHAR$LONGINT$$CHAR': clocale.pp:(.text.n_clocale$_$getformatsettings$tformatsettings_$$_get localechar$longint$$char+0x10): undefined reference to `nl_langinfo' C:\codetyphon\fpc\units\arm-linux\rtl\clocale.o: In function `CLOCALE$ _$GETFORMATSETTINGS$TFORMATSETTINGS_$$_GETLOCALESTR$LONGINT$$SHORTSTRI NG': clocale.pp:(.text.n_clocale$_$getformatsettings$tformatsettings_$$_get localestr$longint$$shortstring+0x40): undefined reference to `nl_langinfo' smartmobile.lpr(22) Error: Error while linking smartmobile.lpr(22) Fatal: There were 1 errors compiling module, stopping These errors are related to your croscompiling setup ( link the proper ARM-linux libraries), not anything database related. ---------------------------------------------------------------------- tatamata · 2012-11-20 11:06 Hello guys, anybody interested to continue development of zmsql package? ---------------------------------------------------------------------- tatamata · 2013-01-13 12:45 Hello everybody, ZMSQL version 0.1.13 is uploaded to Lazarus CCR and available for download. This version works with FPC 2.7.1 in CodeTyphon v 3.10 Lazarus distribution. I didn't test it with older FPC versions, for which it might not work correctly... Changelog: ZMSQL version 0.1.13, 13.01.2013: by Zlatko Matić * Few bug fixes. * TZMbufdataset replaced with current TBufDataset as ancestor. Tested with current FPC 2.7.1 in CodeTyphon v. 3.10. If this does not work for your FPC version, then replace TBufDataSet with TZMBufDataset as ancestor. The TZMBufDataset will stay present latent inside the package, just in case that further development od TBufDataSet goes in direction incompatible with zmsql. *Added procedure InternalRefresh;override; TBufDataSet's InternalRefresh does troubles, so it is overriden to do nothing. It seems that all functionalities which ZMQueryDataset needs are implemented inside TDataSet's Refresh method and that InternalRefresh is not needed at all. ---------------------------------------------------------------------- teos · 2013-01-19 20:17 Quote from: tatamata on July 13, 2011, 07:40:06 pm UPDATE: TZMReferentialKey provides a link between master and slave dataset, enabling referential integrity (update/insert/delete) between them. As far as I know, you describe the "TMasterdatalink" of TDataset. I wonder if you derived from that of maybe it's wise to consider. ---------------------------------------------------------------------- tatamata · 2013-01-19 21:16 Quote from: teos on January 19, 2013, 09:17:35 pmQuote from: tatamata on July 13, 2011, 07:40:06 pm UPDATE: TZMReferentialKey provides a link between master and slave dataset, enabling referential integrity (update/insert/delete) between them. As far as I know, you describe the "TMasterdatalink" of TDataset. I wonder if you derived from that of maybe it's wise to consider. Hm, interesting. If I had known for TMasterdatalink existence, I would probably ulitilize it, but no, I was not aware of it at the time I was writing TZMReferentialKey. Actually, this is the very first time I heard of it. http://www.freepascal.org/docs-html/fcl/db/tmasterdatalink.html. So, the answer is that TZMReferentialKey has been written from scratch. There are probably many possible ways to accomplish master/detail relationship.... You could try to accomplish TMasterdatalink way in zmsql package if you wish. Anyway, zmsql is free and open-source, so you can freely experiment....and even piyk some ideas to use in your Hashdataset project.... ---------------------------------------------------------------------- teos · 2013-01-19 21:52 The Hasdataset is not mine. I posted my remark because I can imagine that there are standard ways that are wise to be followed to keep everything working like it should. I don't doubt that there are more than one way. Only.. the closer you stay to the "standard" ways, the easier it is for others to follow and to help. I have my own database project and get that finished first. ---------------------------------------------------------------------- Vincent Snijders · 2013-02-15 17:27 Is this component still in alpha status, as is mentioned on the wiki or has it been matured in the last 18 months? ---------------------------------------------------------------------- tatamata · 2013-02-18 20:54 Well, it is quite usable already, I use it for a material requirements planning oftware. But there are many things that could be improved. For example, SQL language does not support outer joins, which can be overcomed though by splitting a query in two separated queries and using resultset of the first one as input set for the next one. Visual query builder might be integrated, parmeters implementation is basically a string replacing in SQL text prior execution, etc, etc. So, I would conservatively say that the project is still in alpha. However, I think this project is unique in it's goal and combining flat files storage, in-memory database and SQL. ---------------------------------------------------------------------- zbyna · 2013-09-23 19:12 Quote from: tatamata on January 13, 2013, 01:45:44 pmHello everybody, ZMSQL version 0.1.13 is uploaded to Lazarus CCR and available for download. This version works with FPC 2.7.1 in CodeTyphon v 3.10 Lazarus distribution. I didn't test it with older FPC versions, for which it might not work correctly... Changelog: ZMSQL version 0.1.13, 13.01.2013: by Zlatko Matić * Few bug fixes. * TZMbufdataset replaced with current TBufDataset as ancestor. Tested with current FPC 2.7.1 in CodeTyphon v. 3.10. If this does not work for your FPC version, then replace TBufDataSet with TZMBufDataset as ancestor. The TZMBufDataset will stay present latent inside the package, just in case that further development od TBufDataSet goes in direction incompatible with zmsql. *Added procedure InternalRefresh;override; TBufDataSet's InternalRefresh does troubles, so it is overriden to do nothing. It seems that all functionalities which ZMQueryDataset needs are implemented inside TDataSet's Refresh method and that InternalRefresh is not needed at all.                                            Hi all, ZMSQL version 0.1.13 in CodeTyphon v 4.5  - for me works only after replacing TBufDataSet with TZMBufDataset as ancestor. Wise decesion to keep  TZMBufDataset ---------------------------------------------------------------------- tatamata · 2013-12-25 13:24 I wanted to implement aautincrementing for ftAutoinc fields in overriden DoNewRecord method; Code: [Select]procedure TZMQueryDataSet.DoOnNewRecord; var i:integer; begin   inherited DoOnNewRecord;  {New behavior}   //Increase value of autoincrement fields   for i:=0 to self.FieldCount-1 do begin    if self.Fields[i].DataType=ftAutoInc then begin      if self.Fields[i].IsNull then self.Fields[i].AsInteger:=0 else        self.Fields[i].AsInteger:=self.Fields[i].AsInteger+1;    end;  end; end;        But this doesn't work, field of type ftAutoInc can' accept integer values this way. What woulkd be the way to implement ftAutoInc? ---------------------------------------------------------------------- Leledumbo · 2013-12-25 15:36 QuoteWhat woulkd be the way to implement ftAutoInc?SQLite3 way is to maintain a per autoincrement field counter, which will be incremented upon each insert. Just take a look at their source code. ---------------------------------------------------------------------- taazz · 2013-12-25 16:04 well based on the code in the TAutoIncField.SetAsLong you can set the value if and I quote Code: [Select]  if not(FDataSet.State in [dsFilter,dsSetKey,dsInsert]) then     DataBaseError(SCantSetAutoIncFields); In simple terms that means that onNewRecord might not be the correct event to do what you want to, is the dataset in dsInsert state when the event is fired? Try the same code in OnBeforePost event and see if that is going to help. ---------------------------------------------------------------------- tatamata · 2014-01-01 21:18 Hello, everybody. Please, find attached prerelease of zmsql 0.1.14. Please, test it and report if you find bugs, before uploaded to ccr. Main changes:  * Internal optimizations.  * Based on TZMBufDataset instead TBufDataset, because problems with current TBufDataset version.  * ZMQueryDataset now loads from and saves to files with .csv extension, instead of .txt extension.  * Added ManageFields method, which decides what to do with Fields and FieldDefs.  * Reworked InspectFields procedure, better deduction of FieldDefs and Fields status.  * Enabled automatic creation of persistent fields objects from predefined fielddefs.  * New published properties: DynamicFieldsCreated, PeristentFieldsCreated, MemoryDataSetOpened,  * New public methods: CreateDynamicFieldsFromFieldDefs, CreatePersistentFieldsFromFieldDefs; Here are declarations of public methods and published properties (besides those inherited from TBufDataset), along with explainations: Code: [Select]  public     { Public declarations }     procedure QueryExecute; //Executes SQL query defined in SQL property, on .csv files that are placed in folder defined in ZMConnection property. Resultset of select query is loaded into the the zmquerydataset (self).     procedure PrepareQuery; //Prepares parameterized queries for execution: replaces parameters with parameter values for parameterized queries.     procedure EmptyDataSet; //Deletes all records from dataset.     procedure ClearDataSet; //Deletes records, fields and fielddefs.     procedure CopyFromDataset (pDataset:TDataSet); //Copies schema and data from any TDataset.     function SortDataset (const pFieldName:String):Boolean; //Ascending/Descending sorting of memory dataset.     procedure LoadFromTable; //Loads data (or data and schema) from a .csv file (TableName.csv), set in property TableName, from path specified in ZMConnection property.     procedure SaveToTable;overload; //Saves data and schema to a .csv file (TableName.csv), set in Tablename property, in path specified in ZMConnection property.     procedure SaveToTable(pDecimaSeparator:Char);overload; //Saves data and schema to a .csv file (TableName.csv), set in Tablename property, in path specified in ZMConnection property.     procedure CreateDynamicFieldsFromFieldDefs; // Creates fields from predefined fielddefs. To be used in design-time or run-time for memory dataset creation according to predefined fielddefs.     procedure CreatePersistentFieldsFromFieldDefs; // Creates PERSISTENT fields from predefined fielddefs. To be used in design-time only.     procedure MemoryDataSetOpen; //Executes CreateDynamicFieldsFromFieldDefs and set dataset to Active.     constructor Create(AOwner: TComponent); override;     destructor  Destroy; override;     //Properties needed for master/detail and referential integrity     property MasterDataSetTo:TList read FMasterDataSetTo write SetMasterDataSetTo; // Defines master/detail relationship for purpose of master/detail filtration     property MasterReferentialKeys:TList read FMasterReferentialKeys write SetMasterReferentialKeys;//Defines master/detail relationship for purpose of referential integrity     property SlaveReferentialKeys:TList read FSlaveReferentialKeys write SetSlaveReferentialKeys; //Defines master/detail relationship for purpose of referential integrity     property DisableMasterDetailFiltration:Boolean read FDisableMasterDetailFiltration write SetDisableMasterDetailFiltration; //Master/detail filrtation should be temporarily desabled during bulk inserts or updates...     property OldRecord:TZMBufDataSet {TBufDataSet} read FOldRecord; //Last delete/insert/edit is preserved in this property.   published     { Published declarations }     property ZMConnection:TZMConnection read FZMConnection write SetConnection; //Defines "database" folder path where .csv tables are placed. Instantiates JanSQL database engine.     property SQL:TStrings read FSQL write SetSQL; //Unprepared SQL query text.     property QueryExecuted:Boolean read FQueryExecuted write SetQueryExecuted;  //"True" executes QueryExecute and loads resultset into dataset.     property TableName:String read FTableName write SetTableName; //Name of .csv file (without extension) from which is data loaded by LoadFromTable and to which is data and schema saved by SaveToTable.     property TableLoaded:Boolean read FTableLoaded write SetTableLoaded; //"True" executes LoadFromTable and loads resultset into dataset.     property TableSaved:Boolean read FTableSaved write SetTableSaved; //"True" executes SaveToTable and saves dataset to .csv file defined in TableName property, placed in folder specified by ZMConnection property.     property DynamicFieldsCreated:Boolean read FDynamicFieldsCreated write SetDynamicFieldsCreated; //"True" executes CreateDynamicFieldsFromFieldDefs, which creates fields from predefined fielddefs.     property PeristentFieldsCreated:Boolean read FPersistentFieldsCreated write SetPersistentFieldsCreated; //"True" executes CreatePersistentFieldsFromFieldDefs, which creates PERSISTENT fields from predefined fielddefs.     property MemoryDataSetOpened:Boolean read FMemoryDataSetOpened write SetMemoryDataSetOpened; //"True" executes CreateDynamicFieldsFromFieldDefs and activates dataset for editing.     property PersistentSave:Boolean read FPersistentSave write SetPersistentSave; //If "True", insert/delete/edit will immediately be written to underlying .csv file. If "False", then dataset is only in- memory.     property Parameters: TParams read FParameters write SetParameters; //Parameters for parameterized SQL text.     //Master/detail filtration     property MasterFields: TStrings read FMasterFields write SetMasterFields; //Fields in masterdatasource, (separated by ";") to be used for master/detail filtration.     property MasterSource: TDataSource read FMasterSource  write SetMasterSource;//Master datasource for master/detail filtration. ---------------------------------------------------------------------- tatamata · 2014-01-03 14:30 Since it seems that zmsql users will have to choose for themselves whether they want to compile TZMQueryDataset based on current TBufDataset or based on TZMBufDataset, I put the decision into compiler directive:  {$DEFINE TZMBufDataset}. Use "$DEFINE ZMBufDataset" compiler directive to base TZMQueryDataset on TZMBufDataset or use "$OFF DEFINE ZMBufDataset" compiler directive to base TZMQueryDataset on TBufDataset. The option {$DEFINE ZMBufDataset} is set in zmsql package (actually in pl_zmsql package, since I use CodeTyphon) under: Options/Compiler Options/Other/Conditionals/Custom Options/Defines. if you switch it on, TZMBufDataset is ancestor, if you switch it off, TBufDataset is ancestor. So, now you can check it easy for yourself, which option suites you well on your Lazarus/fpc installation. Please, find attached current zmsql 0.1.14 pre-release version and do your tests. If someone wants to help, these are the issues I would like to be solved before official 0.1.14 release: - implementation of autoincrement fields ---> it seems that TBufDataset intentionally does not support autoincrement fields. In ZMQueryDataset, this should be implemented, maybe similar to way how it is implemented in TDbf. If someone knows how to do it, this would be nice... - to detect whyZMQueryDataset does not work correctly with recent TBufDataset as ancestor (at least on my CodeTyphon v. 4.60.) Other points of interest: - How to implement left and right outer joins in JanSQL engine? Though you can somehow resolve this limitation, by using non-standard "ASSIGN TO variable" statement to perform sub-queries and then include resultset to main query, it would be nice if outer joins are implemented in the JanSQL engine... - How to make another component, similar to ZMQueryDataset, that would perform SQL queries on TDataset instances, in-memory, rather than on .csv files? Imagine a component that performes in-memory SQL queries where "tables" are not files, but rather TDataset instances....I think this could have interesting usage... ---------------------------------------------------------------------- tatamata · 2014-01-04 18:53 zmsql package version 0.1.14 is uploaded to Lazarus ccr: http://sourceforge.net/projects/lazarus-ccr/files/zmsql/ For those who use CodeTyphon, the reowrked pl_zmsql package is attached. ---------------------------------------------------------------------- tatamata · 2014-01-19 15:28 Hello, everybody. Please, find attached draft zmsql package of incoming version 0.1.15. New version brings: *implemented autoincrement fields (ftAutoInc). *Improved visibility of TDataset methods and properties. *zmquerydataset works again with TBufDataset as ancestor (as in CodeTyphon v.4.70). In meantime, if someone can investigate why persistent fields created with following method bring different kind of errors, such as: "Class "TStringField" not found"? Namely, persistent fields seem to be correctly created, they are present in .lfm and are visible in object inspector, but during run- time, different errors occur... It seems that persistent fields created through editor don't have such problems? The idea was to enable automatic creation of all persistent fields, from previously defined fielddefs, by setting property PersistentFieldsCreated to True. Obviously, something is not OK with the following code: Code: [Select]procedure TZMQueryDataSet.DoCreatePersistentFieldsFromFieldDefs; var   NewField: TField;   FieldDef: TFieldDef;   i: integer;   function FieldNameToPascalIdentifer(const AName: string): string;    var      i : integer;    begin      Result := '';      // FieldName is an ansistring      for i := 1 to Length(AName) do        if AName[i] in ['0'..'9','a'..'z','A'..'Z','_'] then          Result := Result + AName[i];      if (Length(Result) > 0) and (not (Result[1] in ['0'..'9'])) then          Exit;      if Assigned(FieldDef.FieldClass) then      begin        Result := FieldDef.FieldClass.ClassName + Result;        if Copy(Result, 1, 1) = 'T' then          Result := Copy(Result, 2, Length(Result) - 1);      end      else        Result := 'Field' + Result;    end;   function CreateFieldName(Owner: TComponent; const AName: string): string;   var     j:integer;      begin        for j := 0 to Owner.ComponentCount - 1 do        begin          if CompareText(Owner.Components[j].Name, AName) = 0 then          begin            Result := FormEditingHook.CreateUniqueComponentName(NewField);            exit;          end;        end;        Result := AName;      end;  begin   //This procedure creates PERSISTENT Fields from predefined FieldDefs.   for I:=0 to fielddefs.Count-1 do   with Fielddefs.Items[I] do begin     FieldDef := Fielddefs.Items[I];     if DataType<>ftUnknown then       begin       //Create new field and set it's unique name.       NewField:=CreateField(self.Owner); //self.Owner ---> this makes created field to be persistent and visible in object inspector.       NewField.Name := CreateFieldName(self.Owner, self.Name + FieldNameToPascalIdentifer(NewField.FieldName));       end;       //Set initial properties of the field.       NewField.FieldKind:=fkData;       NewField.SetFieldType(FieldDef.DataType);       NewField.Size:=FieldDef.Size;     end;     self.BindFields(True); end; ---------------------------------------------------------------------- taazz · 2014-01-19 15:57 Its the streaming mechanism that knows nothing about those classes. I do not know where or how and if you find it please point it out to me too, but TStringfield and friends must be registered in order for the streaming to work correctly (ee registerClass). I have no idea how the streaming mecahnism does it at this point though. ---------------------------------------------------------------------- tatamata · 2014-01-19 22:11 Hi, taaz, thanks. So I put in zmquery an initialization section, (as suggested to me by Zeljan and you): Code: [Select]initialization RegisterClasses ( [{ ftUnknown} Tfield,     { ftString} TStringField,     { ftSmallint} TSmallIntField,     { ftInteger} TLongintField,     { ftWord} TWordField,     { ftBoolean} TBooleanField,     { ftFloat} TFloatField,     { ftCurrency} TCurrencyField,     { ftBCD} TBCDField,     { ftDate} TDateField,     { ftTime} TTimeField,     { ftDateTime} TDateTimeField,     { ftBytes} TBytesField,     { ftVarBytes} TVarBytesField,     { ftAutoInc} TAutoIncField,     { ftBlob} TBlobField,     { ftMemo} TMemoField,     { ftGraphic} TGraphicField,     { ftFmtMemo} TBlobField,     { ftParadoxOle} TBlobField,     { ftDBaseOle} TBlobField,     { ftTypedBinary} TBlobField,     { ftFixedChar} TStringField,     { ftWideString} TWideStringField,     { ftLargeint} TLargeIntField,     { ftOraBlob} TBlobField,     { ftOraClob} TMemoField,     { ftVariant} TVariantField,     { ftGuid} TGuidField,     { ftFMTBcd} TFMTBCDField,     { ftFixedWideString} TWideStringField,     { ftWideMemo} TWideMemoField             ]);     Now there are no errors due to not found classes anymore, but now it is complainig about duplicate names of persistent fields. It seems that during loading it enters twice in DoCreatePersistentFieldsFromFieldDefs; Continuing tommorow... ---------------------------------------------------------------------- tatamata · 2014-01-20 08:43 OK, it seems that persistent fields objects (classes) are now created during run, but there are no underlying database fields (Fields.Count=0). So, if LoadFromTable or QueryExecute is executed, error "Operation cannot be performed on inactive dataset" apears. Setting Active=True raise error too... If CreateDatasete is executed before LoadFromTable or QueryExecute, then error like this appears: "Duplicate fieldname:ProductsId". So, I don't get it, it seems that persistent field object is created but underlying Field is not. The loaded field object has the fieldname so prevents creating dynamic field by CreateDataset method.... Does anybody has an idea how to solve this? ---------------------------------------------------------------------- tatamata · 2014-01-20 22:17 Hello, everybody...it seems that the problem with persistent fields is not in ZMQueryDataset, but in TBufDataset. I tried creating persistent fields manually in editor and the same errors appear like when I do it all at once in code. An the errors are: "Missing (compatible) underlying dataset, can not open.", "Operation cannot be performed on an inactive dataset". Going through methods involved, I can't see what could be the cause in code, I don't see the major difference in what TBufDatase.CreateDataset does when creating dynamic fields...It's CreateFields use the sam looping through fielddefs and executing CreateField(AOWner).... If someone want to check this, the current draft component and the test project are attached. The test projects show two master/detail datasets in both cases: Left side is how it behaves when dynamic fields, right side when persistent fields. And the code is: Quoteprocedure TZMQueryDataSet.SetPersistentFieldsCreated(AValue: Boolean);  { TODO : To solve problems with persistent fields } begin   if FPersistentFieldsCreated=AValue then exit;   if AValue=True then     begin       try         //In design-time only, because, in run-time persistent fields should be streamed from .lfm?         if (csDesigning in ComponentState)             and not (csLoading in ComponentState)             and not (csReading in ComponentState)           then begin             ShowMessage('I am going to create persisten fields from fielddefs.');             CreatePersistentFieldsFromFieldDefs;           end;         {FPersistentFieldsCreated:=AValue; //Removed to CreatePersistentFieldsFromFieldDefs procedure.}         { TODO : Setting FPersistentFieldsCreated to True is temporarily disabled, because if PersistentFieldsCreated is True, then persistent fields will be loaded twice (once from stream and second time here) when project loading in design-time.... }          FPersistentFieldsCreated:=False; //POOR SOLUTION       except         FPersistentFieldsCreated:=False;         self.Active:=False;       end;     end;   if AValue=False then  { TODO : To reconsider what to do on SetPersistentFieldsCreated=False. Currently set to do nothing. }     FPersistentFieldsCreated:=AValue; end;                                      procedure TZMQueryDataSet.CreatePersistentFieldsFromFieldDefs; //This procedure is used to create PERSISTENT Fields from FieldDefs. {var strMsg:String;} begin   with self do begin     FSourceData:=sdInternal;     FFieldCount:=self.FieldDefs.Count;     try       Close;       // Create PERSISTENT fields from FieldDefs       { TODO : To investigate BindFields(False) and DefaultFields in ZMBufDataset and TBufDataset. In Delphi, BindFields(False) disconnects  fields object from underlying fields, but it seems that currently this does not work here? Also, DefaultFields should be False in case of persistent fields and True in case of dynamic fields. However, it seems that sometimes it is False even if only dynamic fields are created.}       {       if InspectFields=ifDoNothing {This means that there are already created corresponding persistent fields.} then begin         ShowMessage('InspectFields=ifDoNothing');         if self.DefaultFields=False {DefaultFields=False means Persistent Fields exist} then begin           ShowMessage('self.DefaultFields=False');           Exit;         end;       end;       }       Fields.Clear;       DoCreatePersistentFieldsFromFieldDefs;       BindFields(True); //Connect persistent Fields objects to underlying Fields.       //If everything goes ok, set the property accordingly.       FPersistentFieldsCreated:=True;       //Deal mutually exclusive property       FDynamicFieldsCreated:=False;     except       ShowMessage('I can not create persistent fields!');       FPersistentFieldsCreated:=False;       self.Active:=False;     end;   end; end;                              procedure TZMQueryDataSet.DoCreatePersistentFieldsFromFieldDefs; var   NewField: TField;   FieldDef: TFieldDef;   i: integer;   function FieldNameToPascalIdentifer(const AName: string): string;    var      i : integer;    begin      Result := '';      // FieldName is an ansistring      for i := 1 to Length(AName) do        if AName in ['0'..'9','a'..'z','A'..'Z','_'] then          Result := Result + AName;      if (Length(Result) > 0) and (not (Result[1] in ['0'..'9'])) then          Exit;      if Assigned(FieldDef.FieldClass) then      begin        Result := FieldDef.FieldClass.ClassName + Result;        if Copy(Result, 1, 1) = 'T' then          Result := Copy(Result, 2, Length(Result) - 1);      end      else        Result := 'Field' + Result;    end;   function CreateFieldName(Owner: TComponent; const AName: string): string;   var     j:integer;      begin        for j := 0 to Owner.ComponentCount - 1 do        begin          if CompareText(Owner.Components[j].Name, AName) = 0 then          begin            Result := FormEditingHook.CreateUniqueComponentName(NewField);            exit;          end;        end;        Result := AName;      end;  begin    //This procedure creates PERSISTENT Fields from predefined FieldDefs.   for I:=0 to fielddefs.Count-1 do   with Fielddefs.Items do begin     FieldDef := Fielddefs.Items;     if DataType<>ftUnknown then       begin       //Create new field and set it's unique name.       NewField:=CreateField(self.Owner); //self.Owner ---> this makes created field to be persistent and visible in object inspector.       NewField.Name := CreateFieldName(self.Owner, self.Name + FieldNameToPascalIdentifer(NewField.FieldName));       end;       //Set initial properties of the field.       NewField.FieldKind:=fkData;       NewField.SetFieldType(FieldDef.DataType);       NewField.Size:=FieldDef.Size;     end;     self.BindFields(True); end; ---------------------------------------------------------------------- HeDiBo · 2014-01-22 16:45 Quoteif FPersistentFieldsCreated=AValue then exit; I think your trouble stems from the fact that this condition is never true. And if you make it true by setting the property, the call to this function comes too early (namely when streaming in components). I made it work by setting PersistentFieldsCreated to False at design time and setting them to True when the Load data button is clicked: Code: [Select]procedure TForm1.Button2Click(Sender: TObject); begin   DataSource_Products_Per.Enabled:=True;   DataSource_Boms_Per.Enabled:=True;   Products_Per.PeristentFieldsCreated := True;   Boms_Per.PeristentFieldsCreated := True;   Products_Per.LoadFromTable;   //Products_Per.QueryExecute;   Boms_Per.LoadFromTable;   //Boms_Per.QueryExecute; end; So, I think you must find a way to set this property after all components are streamed in. Good luck ---------------------------------------------------------------------- tatamata · 2014-01-23 18:12 Quote from: HeDiBo on January 22, 2014, 05:45:36 pmQuoteif FPersistentFieldsCreated=AValue then exit; I think your trouble stems from the fact that this condition is never true. And if you make it true by setting the property, the call to this function comes too early (namely when streaming in components). I made it work by setting PersistentFieldsCreated to False at design time and setting them to True when the Load data button is clicked: Code: [Select]procedure TForm1.Button2Click(Sender: TObject); begin   DataSource_Products_Per.Enabled:=True;   DataSource_Boms_Per.Enabled:=True;   Products_Per.PeristentFieldsCreated := True;   Boms_Per.PeristentFieldsCreated := True;   Products_Per.LoadFromTable;   //Products_Per.QueryExecute;   Boms_Per.LoadFromTable;   //Boms_Per.QueryExecute; end; So, I think you must find a way to set this property after all components are streamed in. Good luck  Dear HeDIBO, thank you for your effort and testing. However, I could not reproduce what you are suggesting. It does not work on my computer, with CodeTyphon 4.70. The same errors appear... Could somebody else try this solution, as HeDIBO proposed? Thanks. ---------------------------------------------------------------------- HeDiBo · 2014-01-23 21:27 Did you try this with the PeristentFieldsCreated property set to False at design time? What kind of error do you receive? ---------------------------------------------------------------------- tatamata · 2014-01-23 21:39 Quote from: HeDiBo on January 23, 2014, 10:27:40 pmDid you try this with the PeristentFieldsCreated property set to False at design time? What kind of error do you receive? Yes. The error is: Project project1 raised exception class 'EdatabaseError' with message: Missing (compatible) underlying dataset, can not open Regards ---------------------------------------------------------------------- HeDiBo · 2014-01-24 11:57 Quote from: tatamata on January 23, 2014, 10:39:36 pmThe error is: Project project1 raised exception class 'EdatabaseError' with message: Missing (compatible) underlying dataset, can not open Could it be I'm still on CT 4.6 but anyhow using TBufDataset i.s.o. TZMBufDataset? ---------------------------------------------------------------------- HeDiBo · 2014-01-24 14:39 I don't have CT 4.7 yet. The difference must be in TBufDataset. The version for CT 4.6 does work! (But may have other problems, I don't know). I tried to go to TZMBufDataset but that fails now because the property MaxIndexesCount is missing. So, if you want to keep the possibility of TZMBufDataset open, that needs some work. ---------------------------------------------------------------------- HeDiBo · 2014-01-24 15:01 The folllowing piece of code from TBufDataset is the only place where the database error "Missing (compatible) underlying dataset, can not open." is generated anywhere in fpc. Since I do not have an error, you may be able to deduce why in your source the error does occur. Code: [Select]procedure TCustomBufDataset.InternalOpen; var IndexNr : integer;     i : integer; begin   FAutoIncField:=nil;   if not Assigned(FDatasetReader) and (FileName<>'') then     begin     FFileStream := TFileStream.Create(FileName,fmOpenRead);     FDatasetReader := GetPacketReader(dfAny, FFileStream);     end;   if assigned(FDatasetReader) then     begin     FReadFromFile := True;     IntLoadFielddefsFromFile;     end;   // This is to check if the dataset is actually created (By calling CreateDataset,   // reading from a stream in some other way implemented by a descendent)   // If there are less fields then FieldDefs we know for sure that the dataset   // is not (correctly) created.   // If there are constant expressions in the select statement (for PostgreSQL)   // they are of type ftUnknown (in FieldDefs), and are not created (in Fields).   // So Fields.Count < FieldDefs.Count in this case   // See mantis #22030   //  if Fields.Count-1) and (Fields[i] is TAutoIncField) and not assigned(FAutoIncField) then       FAutoIncField := TAutoIncField(Fields[i]); Because I don't have your bug, I cannot be of further assistence I'm afreaid. Hope you find it! ---------------------------------------------------------------------- tatamata · 2014-01-24 15:15 Quote from: HeDiBo on January 24, 2014, 03:39:11 pmI don't have CT 4.7 yet. The difference must be in TBufDataset. The version for CT 4.6 does work! (But may have other problems, I don't know). I tried to go to TZMBufDataset but that fails now because the property MaxIndexesCount is missing. So, if you want to keep the possibility of TZMBufDataset open, that needs some work. Hi!. Please find attached the current (work in progress) ZMSQL package. I set {$DEFINE ZMBufDataset} and this version of ZMBufDataset is actually BufDataset from CodeTyphon v 4.70. So, you might test it. At least we will know whether the problem is inside BufDataset. Thanks. ---------------------------------------------------------------------- HeDiBo · 2014-01-24 15:46 Quote from: tatamata on January 24, 2014, 04:15:17 pmSo, you might test it. At least we will know whether the problem is inside BufDataset. It must be something in your system. Rebuilding your test application with the new TZMBufDataset still works OK. I had to replace the two occurrences of TZMZMBufDatasetParser into TZMBufDatasetParser. To be sure we're testing with the same test project, I'm attaching mine ---------------------------------------------------------------------- tatamata · 2014-01-24 18:28 HeDIBo, sorry, it's my fault, now I see I introduced a bug in meantime... Well, you were right, in your first post - if CreatePersistentFields is called explicetely then it works. However, it makes no sence, since persistent fields should be created only in design-time... So...back to investigation... ---------------------------------------------------------------------- tatamata · 2014-01-25 11:51 HeDIBo, Yes, you are right, the problem is in TBufDataset's InternalOpen procedure. Code: [Select]  // If there is a field with FieldNo=0 then the fields are not found to the   // FieldDefs which is a sign that there is no dataset created. (Calculated and   // lookup fields have FieldNo=-1)   for i := 0 to Fields.Count-1 do     if Fields[i].FieldNo=0 then       begin         { TODO : To find a way to set FieldNo for persistent fields. }         //Here is the problem with persisten fields,  because FieldNo is read-only property and is not published, neither streamed....         DatabaseError(SErrNoDataset);}       end     else       if (FAutoIncValue>-1) and (Fields[i] is TAutoIncField) and not assigned(FAutoIncField) then         FAutoIncField := TAutoIncField(Fields[i]);Property FieldNo is 0. And FieldNo is read-only property that is not saved in .lfm, so all persistent fields are initiated with FieldNo=0. If this is commented in T(ZM)BufDataset, than what happens is that all fields are loaded as the last one field in Fields. Since FieldNo can't be set in ZMQueryDataset as it is read-only, defined deeply in DB unit...I really have no idea how to solve this. If someone knows how to set FieldNo after persistent fields are loaded from .lfm? Current zmsql is attached. ---------------------------------------------------------------------- HeDiBo · 2014-01-25 16:44 I think there's a bug. I could trace the creation of the fields from fielddefs in CreatePersistentFieldsFromFieldDefs. In DoCreatePersistentFieldsFromFieldDefs at the end there's a Self.BindFields(True); However directly after the call in CreatePersistentFieldsFromFieldDefs, there's another BindFields(True); This is not the cause of your problem, but I thought I mention it anyway. ---------------------------------------------------------------------- tatamata · 2014-01-25 18:31 Hi again. I think I solved it, with a trick though. I introduced a method InitializePersistentFields which recreates persistent fields so that original names and properties are copied to new persistent fields while old persistent fields are deleted. New zmsql version is attached. Please, could you test and confirm that it works? Thanks. ---------------------------------------------------------------------- tatamata · 2014-01-25 20:31 In zmsql wiki page, added chapter explainig JanSQL SQL dialect: http://wiki.lazarus.freepascal.org/ZMSQL#JanSQL_SQL_language ---------------------------------------------------------------------- HeDiBo · 2014-01-27 15:36 Quote from: tatamata on January 25, 2014, 07:31:02 pmPlease, could you test and confirm that it works? First of all: it works. But very strangely. The property PersistentFieldsCreated won't stay True. So, it will always be False when loaded for the first time. Therefore, it serves no purpose anymore, does it? Unless you want the designer to set it True every time the project ios loaded. The purpose of creating persistent fields at design time was to improve speed, if I'm correct. I wonder what speed you're trying to improve when the dataset itself uses JanSQL which does only sequential searches as far as I know. Isn't that a bit penny wise and pound foolish? Using a fixed type of CSV format is not very nice. The purpose of ZMSQL is to serve a single user. But that user will produce CSV files as output from a spreadsheet, for instance. He may not have the possibility to specify the output format. Also, more than half of the world uses a comma for the decimal "point". That cannot be specified either. Someone suggested to make that a programmer's task. That is not a valid proposition. The programmer should not have to translate the written table back to native notation. He should ask the environment about the decimal point, the thousands separator and the list separator and have a component like ZMSQL where he can pass that specification. Maybe you can replace JanSQL by a better component. I don't know. Keep up the good work! ---------------------------------------------------------------------- tatamata · 2014-01-27 23:41 QuoteFirst of all: it works. But very strangely. The property PersistentFieldsCreated won't stay True. So, it will always be False when loaded for the first time. Therefore, it serves no purpose anymore, does it? Unless you want the designer to set it True every time the project ios loaded. This was intentionally. It creates persistent fields and revert property value to false, but hey, persistent fields are there :-) Why? Well, just because I couldn't figure out how to prevent it that when loading it does not create persistent fields twice (once from stream and second time from setting this property to true... Anyway, the property itself is not important, the intention was just to enable programmer in design-time to create all persistent fields from fielddefs at once and then just set additional properties...All that can be done by using editor, but this should be faster and you don't miss any fields by mistake... QuoteThe purpose of creating persistent fields at design time was to improve speed, if I'm correct. I wonder what speed you're trying to improve when the dataset itself uses JanSQL which does only sequential searches as far as I know. Isn't that a bit penny wise and pound foolish?No, speed was not in my mind. Only ability to set some additional properties for fields in design-time, that you can't set for Fielddefs - this can be useful when multiple data controls (DBGrids for example) connected to the same dataset. Otherwise, you have to set it for any data control separately... Regarding jansql, it is only one option for loading data into zmquerydataset - there are all other possiblities inherited from TBufDataset like streaming from files, copying from other datasets, loading from .csv files... And you can combine all dataset's methods with jansql...it is not excluding one another... QuoteUsing a fixed type of CSV format is not very nice. The purpose of ZMSQL is to serve a single user. But that user will produce CSV files as output from a spreadsheet, for instance. He may not have the possibility to specify the output format. Also, more than half of the world uses a comma for the decimal "point". That cannot be specified either. Someone suggested to make that a programmer's task. That is not a valid proposition. The programmer should not have to translate the written table back to native notation. He should ask the environment about the decimal point, the thousands separator and the list separator and have a component like ZMSQL where he can pass that specification. Actually, I was just working on those issues last few days First of all, there is SysUtils.DefaultFormatSettings.DecimalSeparator and SysUtils.DefaultFormatSettings.ThousandSeparator, which you can read and write in your program. In Windows system settings are deduced correctly automatically, in Linux, clocale unit must be in uses. So I put it in zmsql. Also, I have just changed in zmsql that system setting for decimal separator is used always (both in zmquerydataset and jansql, so that these two are consistent with each other). Also, I added piece of code that enables correct loading of float values, no matter what decimal separator was in the .csv file. The function is the following: Code: [Select]function TZMQueryDataSet.FormatStringToFloat(pFloatString: string):Double; //Transform float value inside a string with adequate decimal separator. var   fs:TFormatSettings;   vFloatString, vLeftPart, vRightPart:String;   vFloatValue:Double;   vDelimiterPos:Integer; begin   fs.DecimalSeparator := SysUtils.DefaultFormatSettings.DecimalSeparator;   {fs.ThousandSeparator := SysUtils.DefaultFormatSettings.ThousandSeparator;}   {   ShowMessage('DecimalSeparator: '+SysUtils.DefaultFormatSettings.DecimalSeparator);   ShowMessage('ThousandSeparator: '+SysUtils.DefaultFormatSettings.ThousandSeparator);   }   case SysUtils.DefaultFormatSettings.DecimalSeparator of      '.':        begin          //Replace decimal separator          vFloatString:=StringReplace(pFloatString,',','.',[rfReplaceAll, rfIgnoreCase]);        end;      ',':        begin          //Replace decimal separator          vFloatString:=StringReplace(pFloatString,'.',',',[rfReplaceAll, rfIgnoreCase]);        end;   end;   //Aditional check for remaining thousand separators. If they exist, they should be removed.   vDelimiterPos:=Rpos(SysUtils.DefaultFormatSettings.DecimalSeparator, vFloatString);   vLeftPart:=AnsiLeftStr(vFloatString,vDelimiterPos-1);   vRightPart:=AnsiRightStr(vFloatString,Length(vFloatString)- vDelimiterPos+1);   if AnsiContainsStr(vLeftPart,SysUtils.DefaultFormatSettings.DecimalS eparator) then begin     vLeftPart:=AnsiReplaceStr(vLeftPart,SysUtils.DefaultFormatSettings .DecimalSeparator,'');     vFloatString:=vLeftPart+vRightPart;   end;   //Get result.   vFloatValue:=StrToFloat(vFloatString, fs);   Result:=vFloatValue; end;So, by using this function, zmquerydataset will be able to load correctly any float value, whether "." or "," was decimal separator. It will even load if someone left thousand separator in CSV file... So, only ";" column separator in .CSV files will remain mandatory for the CSV file format. That's because jansql requires that. Although, this could be propably easily changed too...For instance, this is a function from Lazarus Book, that can be used for automatic separator detection in a CSV file: Code: [Select]function DetermineSeparator(AFileName: String; var HasFieldNames: Boolean): Char; const Seps: array[1..5] of Char = (',',';',#9,'@','#'); var F:TextFile;    S,S2,T:String;    I:Integer; begin   AssignFile(F,AFileName);   Reset(F);   try     Readln(F,S);   finally     CloseFile(F);   end;   Result:=#0;   //Scan the line for the separator character:   I:=0;   while (Result=#0) and (I<5) do   begin     Inc(I);     if Pos(Seps[i],S)<>0 then Result:=Seps[i];   end;   //Try and detect the presence of fieldnames:   //no spaces or double separator:   if Result<>#0 then     HasFieldNames:=(Pos('',S)=0) and                    (Pos(Result+Result,S)=0); end; Regarding jansql SQL engine...I hoped someone more experienced programmer from Lazarus community will improve it. after all, I'm just a hobbiest ---------------------------------------------------------------------- HeDiBo · 2014-01-28 12:16 Quote from: tatamata on January 28, 2014, 12:41:24 amThis was intentionally. It creates persistent fields and revert property value to false, but hey, persistent fields are there :-) Why? Well, just because I couldn't figure out how to prevent it that when loading it does not create persistent fields twice (once from stream and second time from setting this property to true... Anyway, the property itself is not important, the intention was just to enable programmer in design-time to create all persistent fields from fielddefs at once and then just set additional properties...All that can be done by using editor, but this should be faster and you don't miss any fields by mistake... I'm not sure I understand this. Persistent fields are there and then setting the property they are created again. Why is this a good thing? Quote from: tatamata on January 28, 2014, 12:41:24 amNo, speed was not in my mind. Only ability to set some additional properties for fields in design-time, that you can't set for Fielddefs - this can be useful when multiple data controls (DBGrids for example) connected to the same dataset. Otherwise, you have to set it for any data control separately...Again, you lost me. Quote from: tatamata on January 28, 2014, 12:41:24 amRegarding jansql, it is only one option for loading data into zmquerydataset - there are all other possiblities inherited from TBufDataset like streaming from files, copying from other datasets, loading from .csv files... And you can combine all dataset's methods with jansql...it is not excluding one another...But correct me if I'm wrong, searching the ZMSQL dataset is a sequential search, isn't it? Because if it is, that's way too slow for datasets of considerable size. QuoteAlso, I have just changed in zmsql that system setting for decimal separator is used always (both in zmquerydataset and jansql, so that these two are consistent with each other). That's OK for defaults, but not for definitive implementation. The input file may come from elsewhere, for instance, where the decimal separator is different. Or the file may come from a single minded program that can only produce files with one type of representation. The DetermineSeparator function you give for finding the column separator is totally inadequate. Try it with this line: 1234,56;5432,10;"abcdef" (the comma's are decimal comma's) Quoteafter all, I'm just a hobbiest It doesn't show  Congrats! ---------------------------------------------------------------------- HeDiBo · 2014-02-11 11:37 I came across this old article http://edn.embarcadero.com/article/29056 about the TClientDataset. There you can find how they handle a persistent index. Quite interesting, I thought ---------------------------------------------------------------------- tatamata · 2014-03-08 10:43 Hello, everybody. With help of Mario Ferrari (mario.ferrari@edis.it), as constributor (thanks, Mario!), new version (0.1.17) of zmsql is out. Download from: http://sourceforge.net/projects/lazarus-ccr/files/zmsql/ See changes here: http://wiki.lazarus.freepascal.org/ZMSQL#Change_Log ---------------------------------------------------------------------- tatamata · 2014-04-10 07:06 Hello everybody. zmsql 0.1.18 is just released http://sourceforge.net/projects/lazarus-ccr/files/zmsql/ This is bugfix release. There was a silly bug in zmquerydataset destroy method, that caused creating phantom copies of underlying csv files...It is solved now. ---------------------------------------------------------------------- BigChimp · 2014-04-10 11:59 Thanks! Is the status of the release still Alpha as the wiki page says? Perhaps it's time to make it at least beta if enough users have tested it/if it is stable enough... ---------------------------------------------------------------------- tatamata · 2014-04-10 12:07 Quote from: BigChimp on April 10, 2014, 01:59:40 pmThanks! Is the status of the release still Alpha as the wiki page says? Perhaps it's time to make it at least beta if enough users have tested it/if it is stable enough... Hi, BigChimp! I don't know. I think it deserves beta status in it's overall functionality, but the janSQL engine itself has not been improved. If someone could improve jansql engine so it has outer left and outer right joins, then I would surely say: "yes". ---------------------------------------------------------------------- BigChimp · 2014-04-10 12:12 Well, IMO alpha or beta says more about the quality of the existing functionality: unreliable means alpha, more reliable means beta. Once all needed functionality is there and working ok, it's no longer beta but production/gamma. The reason why I asked is because alpha quality software is regarded with (justified) suspicion by many programmers; I think it would be unfair to zmsql if it is working ok, just missing some features. ---------------------------------------------------------------------- marcov · 2014-04-10 12:26 Quote from: BigChimp on April 10, 2014, 02:12:57 pmWell, 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 · 2015-02-08 21:28 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 · 2015-02-22 04:25 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 · 2015-02-22 08:09 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 · 2015-02-22 09:56 Quote from: eldonfsr on February 22, 2015, 05:25:08 amHello 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 · 2015-02-22 10:10 Quote from: mangakissa on February 22, 2015, 09:09:13 amCode: [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 · 2015-02-22 11:15 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 · 2015-02-22 21:06 Thanks for your answere was great and help full veat ---------------------------------------------------------------------- mangakissa · 2015-02-23 09:11 QuoteA) 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 · 2015-02-23 17:22 Quote from: mangakissa on February 23, 2015, 10:11:10 amQuoteA) 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 · 2015-04-16 14:41 Quote from: tatamata on February 08, 2015, 10:28:26 pmNew 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 · 2015-11-24 14:13 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 · 2015-11-24 19:46 Quote from: hrayon link=topic=13821.msg194111#msg19411apple?date=1448374437Hi 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 · 2015-11-24 21:43 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 · 2015-11-25 07:50 Quote from: hrayon on November 24, 2015, 10:43:15 pmSure! 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 ---------------------------------------------------------------------- tatamata · 2023-02-21 19:50 After long time, I have dedicated some time to work on improving ZMSQL. Link for download: https://matasoft.hr/pl_ZMSQL_v7.6.2.7z (I hope it will be updated in CodeTyphon and Lazarus CCR, but for now you can download CodeTyphon version here) I have done many changes, of which most important are:       *ZMQueryDataset is now descendant of TRxMemoryData by defualt, but you can always switch back to TBufDataset, by compiler directive.        Reason for this decision is because TBufDataset cannot load data from large csv files.       *ZMBufDataset removed from package.       *Removed dependency on TSdfDataset. TCSVdataset is now used for importing data from CSV files.       *When importing data from CSV files, via TCSVDataset, FieldDef.Size and Field.Size properties are now determined by inspecting actual values through all rows.        Rason for this is to decrease memory consumption for loading large csv files.        Note that TCSVDataset does setup Size property always to Size=255, which is not optimal...       *CopyFromDataset is renamed to CopyFromADataset, in order not to hide CopyFromDataset coming from TBuFDataset       *Added function DetermineSeparator(AFileName: String; var HasFieldNames: Boolean): Char; to be used for automatic determination of field delimiter in a csv file.       *Resolved bug that was preventing multiple consecutive loading of different csv files.       *Addded procedure GetFieldsListFromCSVFile(List: TStrings);  This is fast method to determine field names in the csv file registered as TableName.        Corresponding change made in unit vsb_Zmsql, in order for TZMQueryBuilder can load field information fast.       *Added procedure CopyDataFromDataset(DataSet: TDataSet);       *Various other minor changes and bugfixes ---------------------------------------------------------------------- RayoGlauco · 2024-03-10 11:37 Hello, tatamata. I think this is a very interesting project. I'm looking at the possibility of handling some small databases without using a database engine.