DAO design patterns with master-details tables... lazarus / 16-Databases ---------------------------------------------------------------------- gelinp · 2016-10-08 04:44 Hi, I'm using a simple DAO design pattern with single table without problems. But now with a Master-Details tables I can't use two separate DAO, I need to link details ID with master recording. I think to use decorator design pattern... But if the detail list is very big, I can't instantiate all in memory... So what about more complexe designs patterns with database ? Do you know a book ? I doesn' want to use TiOPF library because I've not found yet a good documentation... By exemple, I can use Query with DAO pattern with lazarus use also dataset to connect graphical compoenents of the interface. I know about MVC pattern but here there is both DAO and DATASET models to synchronise with user interface and other objets of the programm... So what is the best way to include query, dataset and dao components togethers ? Also I notice that query can use multiples tables. But apply update is not always possible with a multitables query... (I'm using zeos). But it is a very convenient way to use multitable query, either I need to build an other schema with my application objects. It can't think to do all this stuff.. Thank you for your help. ---------------------------------------------------------------------- Leledumbo · 2016-10-08 07:21 Quote from: gelinp on October 08, 2016, 06:44:21 amBut now with a Master-Details tables I can't use two separate DAO, I need to link details ID with master recording. I think to use decorator design pattern... But if the detail list is very big, I can't instantiate all in memory... So what about more complexe designs patterns with database ? I use single level caching method. Properties which are linked to list of other objects will only be instantiated on the first getter call. This way, I can implement mutual references without ending in infinite recursion. Quote from: gelinp on October 08, 2016, 06:44:21 amBy exemple, I can use Query with DAO pattern with lazarus use also dataset to connect graphical compoenents of the interface. I know about MVC pattern but here there is both DAO and DATASET models to synchronise with user interface and other objets of the programm... So what is the best way to include query, dataset and dao components togethers ? Use ORM/OPF. Quote from: gelinp on October 08, 2016, 06:44:21 amAlso I notice that query can use multiples tables. But apply update is not always possible with a multitables query... (I'm using zeos). But it is a very convenient way to use multitable query, either I need to build an other schema with my application objects. It can't think to do all this stuff.. I don't use ZEOS, but SQLdb works just fine for updating multiple tables. I don't think ZEOS would be incapable of doing that, given its long time existence. It's quite basic functionality. ---------------------------------------------------------------------- gelinp · 2016-10-08 09:27 Thank you for your response. You said "Use ORM/OPF." I think your are talking about Synopse mORMot framework. Yes, there is at least 2000 pages documentation... Thank you, great thing ! What about development with such a framework. BAsicaly, if I update databse frequently, do you think it 'll be complicated to update application code ? I note your tips to use only one level dao. What about performances with a lot of details if response repeat master each time ? Thank you again ! ---------------------------------------------------------------------- marcov · 2016-10-08 10:25 Quote from: Leledumbo on October 08, 2016, 09:21:00 amI don't use ZEOS, but SQLdb works just fine for updating multiple tables. I don't think ZEOS would be incapable of doing that, given its long time existence. It's quite basic functionality. Use a tzupdatesql object and assign it to the original query. You can enter the relevant sql there, multiple statements. ---------------------------------------------------------------------- Leledumbo · 2016-10-08 19:20 Quote from: gelinp on October 08, 2016, 11:27:23 amThank you for your response. You said "Use ORM/OPF." I think your are talking about Synopse mORMot framework. No, I talk about the generic term. The implementation is up to you(r choice). Be it tiOPF, dOPF, mORMot, EntityDAC (once it has support for FPC or FPC supports Delphi features they exploit) or any other implementations. I currently use dOPF due to its very basic features, so I can extend it easily to my liking. Quote from: gelinp on October 08, 2016, 11:27:23 amBAsicaly, if I update databse frequently, do you think it 'll be complicated to update application code ? Depends on the chosen framework. But generally, none the ones I know offer automatic client code generation and maintenance. So you'll need to update your class structure to reflect schema changes manually. Complicated or not, depends on the changes for sure. Quote from: gelinp on October 08, 2016, 11:27:23 amI note your tips to use only one level dao. What about performances with a lot of details if response repeat master each time ? Just as if you do it by direct query, I don't think mapping will have noticeable performance penalty. ---------------------------------------------------------------------- Graeme · 2016-10-08 23:26 Quote from: gelinp on October 08, 2016, 06:44:21 amI do not want to use TiOPF library because I've not found yet a good documentation... The by all means join the official tiOPF support newsgroup and ask questions that the online documentation or demos don't give you. The tiopf.support newsgroup itself is a goldmine of useful information (articles never expire - for that exact reason). Even I search it regularly.   http://tiopf.sourceforge.net/Support.shtml ---------------------------------------------------------------------- gelinp · 2016-10-09 05:45 Yes marcov  . ZEOSDBO documentation (2014 nov. 13)  on developpez.com said p. 30 that a query multitable is by defaut readonly according standard SQL92. So I could get write mode with TZUpdateSQL, it's OK ! But what about a legacy tables ?  Because a chield table inherite keys from a parent table ! So I need to split multitable query with legacy table because it can't get id from parent's table to assign it to the child's one into a same insert ! So I'll need 2 steps to insert legacy table... The parent table use automatic incrementation of the key, and the child table assign the same value to it's one key... Are you agree or do you know an other way ? About newsgroup for tiOPF : Newsgroup is not documentation ! it's only conversations... A documentation is a document with a summary, page numbers, and the content is consistent with a large publishing efforts... When I'm looking for information into newsgroup or forum I'm making the job like of a crumbs collector... ---------------------------------------------------------------------- Graeme · 2016-10-09 09:40 Quote from: gelinp on October 09, 2016, 07:45:35 amAbout newsgroup for tiOPF : Newsgroup is not documentation ! it's only conversations... I consider it 15+ years of knowledge base. Thousands of questions and answers. We don't talk about the weather or your last beach holiday in the support newsgroup. Everything is related to the tiOPF project, and most are about usage questions. eg: How do I ...? QuoteA documentation is a document with a summary, page numbers, and the content is consistent with a large publishing efforts... Page numbers? Where have you been for the last 20 years? Web published content has done away with "page numbers". If you haven't yet, please visit the tiOPF homepage (http://www.tiopf.com), there are loads of documentation, howtos, getting started guides etc. But I guess most developers these days only like YouTube videos, being spoon fed everything, and just don't @#$% read! ---------------------------------------------------------------------- DonAlfredo · 2016-10-09 10:56 @Leledumbo The mORMot has automatic client code generation through Mustache templates. ---------------------------------------------------------------------- Leledumbo · 2016-10-09 11:06 Quote from: DonAlfredo on October 09, 2016, 12:56:31 pm@Leledumbo The mORMot has automatic client code generation through Mustache templates. I hope you don't misunderstand client code of object pascal classes representing database tables and their schema (what I said) with client code of rendered template as output to be sent as response (what you said). ---------------------------------------------------------------------- DonAlfredo · 2016-10-09 11:15 @Leledumbo. Yes, in that case it is a mis-undestanding. ---------------------------------------------------------------------- gelinp · 2016-10-09 12:18 I've just read in ZEOSDBO documentation that I can add multiples statements in TZQuery and TZUpdateSQL seprated by ';' character. And I can use old_field value, by exemple to get the old value before a field update. This is close to what I want. But I need not the old value of a field, but the new value affected to an automatic calculated key into previous statement... This implies that previuous statement was completely executed before to get the result of second one, even if transaction will not be closed... Do you know if this is possible with multiple statement with TZUpdateSQL or do I need to definitly split my statements ?What about mORMot library and legacy tables (tables with legacy of the key) ?Do you use data controls connected with Dataset with mORMot or other framework to manage persistence ?Patrick ---------------------------------------------------------------------- marcov · 2016-10-09 18:37 Quote from: gelinp on October 09, 2016, 07:45:35 amYes marcov  . ZEOSDBO documentation (2014 nov. 13)  on developpez.com said p. 30 that a query multitable is by defaut readonly according standard SQL92. So I could get write mode with TZUpdateSQL, it's OK ! But what about a legacy tables ?  Because a chield table inherite keys from a parent table ! So I need to split multitable query with legacy table because it can't get id from parent's table to assign it to the child's one into a same insert ! So I'll need 2 steps to insert legacy table... The parent table use automatic incrementation of the key, and the child table assign the same value to it's one key... Are you agree or do you know an other way ? Yes, that is an old problem with "generic" SQL.  Some databases allow you to return the ID, and some others have different solutions. I only used postgres with Zeos, and there hooking up a tzsequence to the autoincrement counter and use that fixes that. You can then use currval('sequence_name') to get the last inserted id from that sequence. Afaik it is safe as long as possible other inserts use their own transaction ---------------------------------------------------------------------- DonAlfredo · 2016-10-09 19:20 @gelinp You can use virtual tables to access existing external tables with the mORMot. See: http://synopse.info/files/html/Synopse%20mORMot%20Framework%20SAD%201. 18.html#TITLE_164 http://synopse.info/files/html/Synopse%20mORMot%20Framework%20SAD%201. 18.html#TITL_120 And yes, I use data controls to display data. The mORMot has some dedicated classes (units) for this. See: https://github.com/synopse/mORMot/tree/master/SQLite3/Samples/17%20- %20TClientDataset%20use https://github.com/synopse/mORMot/tree/master/SQLite3/Samples/ThirdPar tyDemos/EMartin/TSynRestDataset ---------------------------------------------------------------------- DonAlfredo · 2016-10-09 19:30 Sidenote about client side code. At the moment, I have a mORMot server running a database for tvplanit (just for testing). Client side code is generated on the fly with help of RTTI, based on the database design. Can be found here (remember: generated on the fly, so dynamic): http://54.194.211.233:8888/root/wrapper Have a look for yourself.