JVCL - MemoryDataset lazarus / 16-Databases ---------------------------------------------------------------------- TheBlackSheep · 2011-08-01 21:21 Please find attached a modified version of the old JVCL v2.10 memory dataset - JvMemDs.  It originally dates back to around 2002 and was based on the original RxMemDs from the RXLib suite (circa 1999 I think). I've managed to get this compiled and (hopefully) working in Lazarus and then added a few features which I think set this apart from the other memory datasets available for Lazarus; persistence - SaveToFile/Stream and LoadFromFile/Stream filters - unashamedly borrowed the code from the latest version here although couldn't get the more recent FilterExpression addition to work so that's currently not in here. sorting - secondary field sorting where the first field was blank  didn't seem to work in some of the memds implmentations blob support - blobs, memofields and widestrings fields are supported. Note: in the last one (blob fields) I'm not sure sorting is available - seems to work for widestrings but I've seen it throw a wobbler for Memo fields (it might be necessary to incorporate v3 "CompareFields" code if this is required).  Also, the Widestring support is a bit sketchy - implementations here differ across the libraries (whether to store two bytes or one per character) it seems to work currently but the implementation here might be incorrect for Lazarus/specific platforms/languages). Obviously use it at your own risk.  I've been using it for a month or so and it seems stable enough, although, I might not be using features you need and so I haven't come across any issues that you may come up with. There's an example attached showing some of the features, please feel free to modify the code and let mw know if you see any issues. I can't take the credit for this as I've just pulled bits from various sources to get this to work and hacked the original source to make it more Lazarus friendly/compatible.  There's potential for more conversions of the JVCL 2.10 library as it was less intertwined with the JCL than the latest version is.  Also, it supported a smaller number of compilers and therfore I think a lot more of this code could be ported than the current JVCL (if anyone has any specific requirements here let me know and I'll take a look at converting it and adding it to this library - obviously, there's no point in doing this if we have a better or equivalent component in the LCL [or other library] already). The Black Sheep ---------------------------------------------------------------------- tatamata · 2011-08-04 07:57 Could someone write a comparisson between: TBufDataSetTMemDataSetTRxDataSetJvMemDsWhich of these would be best ancestor class for my zmsql project (http://lazarus.freepascal.org/ind ex.php/topic,13821.msg74474/topicseen.html#new)? ---------------------------------------------------------------------- BigChimp · 2011-08-04 08:53 Very good idea, tatamata - it might even be a good idea to merge some of them together or get rid of some... There already is a wiki page on writing in memory datasets, such a comparison could well be put there. ---------------------------------------------------------------------- mytimeman · 2011-08-04 12:15 Hi TheBlackSheep, Can I start this by asking how does your version differ from the one in the rx components set TRxMemoryData ? I'm interested because I was suggesting we add loadtostream/loadtofile etc functionality to this dataset ? ---------------------------------------------------------------------- TheBlackSheep · 2011-08-04 21:45 hi mytimeman I did look at the TRxMemoryData to make the relevant amendments on but there were a few implementations I came across and I couldn't work out which one was the most recently developed.  This one in particular was one I looked at (obviously this part of a larger attempt to port the RXLib suite and is still in development); http://w7site.ru/wiki/index.php/RxFPC I think I'd read somewhere on the Lazarus forums that there were some issues with the rx conversions that had never been addressed but I don't think anything specific was mentioned. It struck me that the JVCL had been developed after the RX ones (and effectively derived from it) so in theory these would have any "fixes" plus extra functionality. I added the load/save from code mentioned here; http://vowca.chat.ru/Delphi/RxMemDSUtils_eng.html with a small amount of amendments. I plagiarized the filtering from the latest JVCL3 version and I think the sorting was "fixed" in the JvMemDs version (I think some versions of the RxLib sort fine if there's only 1 field in the sort but don't work correctly if you sort on 2 or more fields and the initial one's is blank, i.e. it doesn't sort on the second field in this instance - this may not be true of all the RxLib implementations). The JVCL & RxLib versions both support memo/blob fields but I don't think the other "standard" in-memory tmemdataset and the tbufdataset do  -  although as I mentioned above I'm not totally convinced about the wide string field types - probably best to avoid unless you really need it. You can my previous mention of this here; http://www.lazarus.freepascal.org/index.php/topic,13297.0.html and this is why I was looking at developing more down the RxLib set.  I think if you added the code related to the load/save to that version it would work for the most part - there isn't a huge amount of difference between the two versions although it doesn't support the "standard" filtering which I gather was the biggest critiscm of it (one of the things I had to change was the disbling of filtering to save else it throws an exception). Interestingly the JVCL one supports auto-inc fields, calculated fields (can't remember whether the RxLib one does or doesn't) and probably lookup fields (although haven't tried the latter) these appear to  work but don't really make a lot of sense in an in-memory dataset (or rather are unnecessary) and they really make a mess of loading/saving.  I also added a save/load to/from XML although it was probably better as an external function so it's currently not in there (it did too much field-type saving as well most of which was probably unnecessary). I could certainly add it back in if there was a call for it.  TheBlackSheep ---------------------------------------------------------------------- tatamata · 2011-08-04 21:59 Hello, TheBlackSheep! It would be interesting to see how your component would fit as ancestor for TZMQueryDataset. See http://lazarus.freepascal.org/index.php/topic,13821.msg74492.html# msg74492 I am not very happy with using TBufDataset as ancestor. And bugs such as http://bugs.freepascal.org/view.php?id=19631#bugnotes really frustrate me... No luck with TMemDataset too. If you have time and will, maybe you could try to make JvMemDs based version of TZMQueryDataset? ---------------------------------------------------------------------- mytimeman · 2011-08-05 07:28 TheBlackSheep The rx components including a lot of the original Rxlib are in the lazarus components repository. Which is an extra for lazarus. There is the main lazarus development environment http://sourceforge.net/projects/lazarus/ plus the component repository for components converted to work with lazarus. http://sourceforge.net/projects/lazarus-ccr/ My impression is quite a few people use the existing rx dataset, but I have only really played with it. The grid in the components seems good too which was why I was looking to use it. I posted a possible version of the component with persistance here which may be of use.. http://lazarus.freepascal.org/index.php/topic,14118.0.html ---------------------------------------------------------------------- TheBlackSheep · 2011-08-05 17:58 hi tatamata I did have a quick look and tried initially to replace the tbufdataset with the JVMemDs one but it doesn't look that simple - it might be easier to go the other way and try and build up the functionality on top of the JVCL component.  Must admit generally, if I need SQL functionality in a local datasource or an in-memory one I tend to use SQLite since it's really good.  What does your version give you that Sqlite doesn't? TheBlackSheep ---------------------------------------------------------------------- BigChimp · 2011-08-05 18:09 Just chipping in: recent FPC SVN (since about a week) has the fpXMLXSDExport unit, which can export XML format that Excel, ADO.Net and Access can read. Should be usable with any dataset. Working on Delphi Clientdataset export. There are also other export modules that have been in FPC since about 2007, e.g. for csv, json, ASCII encoded simple XML... Of course, this doesn't give you an import module, but that could conceivably be constructed along the same lines... ---------------------------------------------------------------------- tatamata · 2011-08-05 20:33 Quote from: TheBlackSheep on August 05, 2011, 07:58:54 pmhi tatamata I did have a quick look and tried initially to replace the tbufdataset with the JVMemDs one but it doesn't look that simple - it might be easier to go the other way and try and build up the functionality on top of the JVCL component.  Must admit generally, if I need SQL functionality in a local datasource or an in-memory one I tend to use SQLite since it's really good.  What does your version give you that Sqlite doesn't? TheBlackSheep Well, I am not very familiar with SQLite for in-memory datasets. Can it use SQL when in-memory? Generally speaking, it's obvious that SQLite is mature and superior to zmsql in every respect and that at this moment it is David/Goliat comparisson. However, zmsql is purely written in Pascal, completely embedded into executable, has no .dll or .po files and operates with csv files that can be easily opened and edited with any spreadsheet or text editor. Therefore, I would say that embedment into executable, easy access with external programs (spreadsheet or text editors), total portability and crossplatfomability could be main advantages, at least in future, when it is completed. ---------------------------------------------------------------------- TheBlackSheep · 2011-08-05 22:24 yep you can use sqlite for in-memory just set the database as ":memory:" (without the quotes of course).  Of course as an in-memory database you need to programmatically create your SQL tables but I tend to create a static db first (i.e. a real physical file) and then after I've finished debugging the code and the dataset has been set more or less in stone then I'll get the sqladmin tool to generate the DDL for the tables/indexes etc and hardcode them into a create routine on the application startup (they could easily be added to a string holder component and then executed as a batch query).  The Zeos components are good (probably not the quickest when compared to those that are purely SQLite related but they work really well) - of course the Zeos components give you the added advantage that if you ever scale up to a proper RDBMS (MySQL, PostgreSQL, Firebird, SQLSVR etc) then you're only changing a few config values, adding a different library with your application and a small amount of code for the SQL differences between the implementations (this latter bit might not even be necessary). It's true that for SQLite you would need to add the dll to the folder of the exe (or have it on the path at least) - on Linux you'd need to manually install the development sqlite library which is slightly more inconvenient - unless you've written a package (rpm/deb etc) and configured it to install the SQLite dev libraries as a prerequisite in which case it would be added automatically. For my own use I tend to find I'm only using a single table for in- memory datasets (i.e. no joins to other tables) - it's simply a runtime holder for some data that's been manipulated/viewed/edited on a grid, client side.  If I need the power of SQL (such as aggregating data from multiple sources) then it's a no brainer to use SQLite as you know it's going to work - it's in constant development, is completely tested/debugged, works fully cross-platform, good documentation and has the power of full SQL (triggers, views, joins, unions, commit/rollback facilities, etc.) - there are many good sqlite management tools out there (although I have plans to write my own with extra features such as a decent CSV import/export). To be fair, import is weak on SQLite (CSV import is available but AFAIK it's too simplistic to be useable - e.g. it doesn't support commas within quoted string fields - having said that, M$ support for CSV is poor too - just look at Excel and SMSS for examples - even Borland's simple implementation on TStringList has issues).  In practice, you're almost always writing your own routines to import/export into/from a database to get around these limitations. How do you handle multiple tables in your implementation via TMemDataset/TBufDataset? TheBlackSheep ---------------------------------------------------------------------- tatamata · 2011-08-05 22:40 Any zmquerydataset instance can either load one csv table into memory either load sql query result (on multiple joined csv tables) into memory. Currently, there is master/detail filtration implemented via properties Mastersource:TDataSource and Mastefields:TStrings. I'm working on referential integrity now. It seems it is going to be implemented by new component (TZMReferentialKey) that will have properties: MasterDataset:TZMQueryDataset; SlaveDataset:TZMQueryDataset; ReferentialKind: TReferentialIntegrityKind=set of (rikInsert, rikUpdate, rikDelete); JoinedFields: TStrings -->probably I will use Name=Value pairs, in this case MasterField=SlaveField. ---------------------------------------------------------------------- TheBlackSheep · 2011-08-05 23:37 You could modify the way you've implemented it of course.  Instead of deriving from an in-memory table such as TBufDataset/RXlib/JVCL memory tables you create a holder component that just holds references to these tables - perhaps in a TList, i.e.  you'd derive your component from another component somewhere higher in the LCL such as TComponent and then you'd have a property such as a TList or better still a TCollection.  The collection items would each hold a link to an in-memory dataset (you wouldn't even have to choose which type if it held a parent dataset class that was common to all).  Your joins (and any other related SQL that crossed tables) would then be outside of the in-memory tables and in your "owner" component - you'd be setting filters or master/detail relationships on each of the memory datasets in your collection depending on what SQL was being requested (I presume this is what the JanSQL related pascal inclusion is for - parsing the query and then you interpret this in terms of the in-memory tables). If you did this you might be able to let the user choose at designtime the memory tables they want to use, they'd add the item and then link in the memory table that they'd dropped on the form.  You could create an "alias" for the table as a published property within the collection item and then you could use the alias in the SQL as a table name.  You'd need to be able to handle deletions of the in-memory table from the form, i.e. remove the link on a collection item if it pointed to the deleted in-memory table.  An advantage I can quickly see here is you could iterate over the tables by going though the collection items' links (i.e. treat it like a database in some ways).  TheBlackSheep ---------------------------------------------------------------------- tatamata · 2011-08-19 10:29 TheBlackSheep, Does your JvMemDs supports ftAutoInc field type? I mean, does it have internal autoincrement generator? ---------------------------------------------------------------------- TheBlackSheep · 2011-08-21 13:46 Hi tatamata can't claim it as "mine" really - it's just a plagiarized combination of bits from RxLib, JVCL and code found on the web. However, in answer to your question - yes it does support ftAutoInc but I think this field type will give you issues with persistence.  I can't remember if the streaming method supported it but in the XML persistence method it gave problems (partly why I dropped the code from the class).  If you think about it, on the restoration of the dataset when you recreate your fieldtypes before you populate them with the data what do you choose - you can't choose a field-type of ftAutoInc as then you won't be able to put data into the field manually.  The strategy I planned to use was to make it a field-type of ftInteger initially and then change it after loading the data in to the field but the JVCL dataset doesn't allow that change. Where I've needed a lookup I've avoided using ftAutoInc and just used a hash of another field in the record (usually the lookup value) - on the insert you look for this first if it exists find another hash key (i.e. keep adding 1 until it's unique).  On an amendment of the record use the existing hash-key as the reference for the record (whether it relates to the hash of the lookup value anymore or not). Anyway, good luck with your implementation. TheBlackSheep ---------------------------------------------------------------------- grays · 2012-10-12 09:39 thank you ,TheBlackSheep! it works fine for me! ---------------------------------------------------------------------- BigChimp · 2012-10-12 10:34 As long as we're resurrecting this thread I'd be interested in the results of the FPC dbtestframework of your JvMemds versus FPC bufdataset...