Sharing SQLDB triad: TSQLConnection-TSQLTransaction-TSQLQuery lazarus / 16-Databases ---------------------------------------------------------------------- Leledumbo · 2015-07-03 15:36 I'm writing an ORM for my own needs, which is based on SQLDB. In SQLDB, the triad TSQLConnection-TSQLTransaction-TSQLQuery is what makes the end-to-end communication. I want to make it sharable, so that each model uses the same connection but can have its own query. However, I have no idea whether TSQLTransaction should be part of the model (so each model has its own) or part of the shared TSQLConnection. It's tied to the connection but is also the one which protects the queries, which could confuse if during a transaction another model need to query, too, e.g. in a multi-user web server application . Or is it better not to share at all so that each model has its own triad? ---------------------------------------------------------------------- taazz · 2015-07-03 15:53 Quote from: Leledumbo on July 03, 2015, 05:36:56 pmI'm writing an ORM for my own needs, which is based on SQLDB. In SQLDB, the triad TSQLConnection-TSQLTransaction-TSQLQuery is what makes the end-to-end communication. I want to make it sharable, so that each model uses the same connection but can have its own query. However, I have no idea whether TSQLTransaction should be part of the model (so each model has its own) or part of the shared TSQLConnection. It's tied to the connection but is also the one which protects the queries, which could confuse if during a transaction another model need to query, too, e.g. in a multi-user web server application . Or is it better not to share at all so that each model has its own triad? 1) I wouldn't couple them with sqldb so tightly. 2) transaction/query must be created and deleted per persisting action (insert/delete/update) and only when the data are persisting. Connection must be shared because it is to expensive to have multiple connection so a connection pool should be created. Make sure that the pool will create as many connections as needed with minimum active and maximum active per thread. I think I have somewhere a thread aware connection pool class (with a count for each thread to represent the active connections instead of creating a connection on each call) I can search for it if you want. ---------------------------------------------------------------------- Graeme · 2015-07-03 16:59 Quote from: Leledumbo on July 03, 2015, 05:36:56 pmI'm writing an ORM for my own needs, which is based on SQLDB. Do you know about the tiOPF framework? http://tiopf.sourceforeg.net It has been in use in commercial environments since before 1999, but was open sourced then by an Australian company called TechInsite. It is very well tested with 1600+ unit tests running every 3 hours, 24/7. It automatically shares a single connection between multiple queries, but you have the ability to create your own extra connections and transactions too - but normally isn't necessary. You can also tell tiOPF how many connections to open in the connection pool. It has tons of other very useful features too. eg: You application can use any of the supported database components (SqlDB, Zeos, IBX, ADO, etc), or even text backends (TAB, CSV, XML). There is even a 3-tier backend with supplied Application Server (and embedded HTTP server). Your application can communicate with multiple database servers, and you can switch between all of these (even 3-tier) without changing a single line of code in your application, just a toggle of a compiler define and a recompile. You can also find more details (mainly install and setup with Lazarus IDE) here:   http://wiki.freepascal.org/tiOPF If you want to discuss or ask further question regarding tiOPF and its features, I welcome you to choice the project's support newsgroup. You can connect with any news client (Opera, Thunderbird, XanaNews Outlook Express etc), or use the web interface. NNTP Server: geldenhuys.co.uk NNTP Port:  119 Web Interface if you don't use a news client: http://geldenhuys.co.uk/webnews/ ---------------------------------------------------------------------- Leledumbo · 2015-07-05 22:03 Quote from: taazz on July 03, 2015, 05:53:46 pmtransaction/query must be created and deleted per persisting action (insert/delete/update) and only when the data are persisting OK, assume: c: connectionc.t: connection.transactiont1: transaction1q1: query1q1.db: query1.databasetransaction2 t2q2: query2q2.db: query2.databaseconsider this scenario: db request come, c.t := t1, q1.db := cq1 does queryanother db request come, c.t := t2, q2.db := cq2 does queryq1 processes its result <- will this still be valid? remember no commit[retaining] / applyupdates hereQuote from: Graeme on July 03, 2015, 06:59:00 pmDo you know about the tiOPF framework?Yes, and also dOPF, mORMot and some others. It's just a matter of style, but I want to have as minimum as possible user code, only extending a class and set relationship, no table/field mapping required. Moreover, the last time I tried tiOPF (and others above except mORMot which I haven't really tested) still doesn't support m-n relationship. ---------------------------------------------------------------------- Graeme · 2015-07-05 23:46 Quote from: Leledumbo on July 06, 2015, 12:03:50 amYes, and also dOPF, mORMot and some others. It's just a matter of style, but I want to have as minimum as possible user code, only extending a class and set relationship, no table/field mapping required. Then may I suggest you read up on the Relationship Manager design pattern. The tiOPF repository includes an implementation, but you can obviously start from scratch, or strip out the tiOPF parts from the implementation provided. My article listed below includes an implementation. It is small and very easy to implement. You can model any relationship with it. http://geldenhuys.co.uk/articles/relationship_manager.pdfhttp://www.an dypatterns.com/files/11561231637882AndyBulkaRelationshipManagerPattern .pdf QuoteMoreover, the last time I tried tiOPF (and others above except mORMot which I haven't really tested) still doesn't support m-n relationship. Of course it does - I have no idea who told you otherwise. ---------------------------------------------------------------------- taazz · 2015-07-06 00:06 Quote from: Leledumbo on July 06, 2015, 12:03:50 amQuote from: taazz on July 03, 2015, 05:53:46 pmtransaction/query must be created and deleted per persisting action (insert/delete/update) and only when the data are persisting OK, assume: c: connectionc.t: connection.transactiont1: transaction1q1: query1q1.db: query1.databasetransaction2 t2q2: query2q2.db: query2.databaseconsider this scenario: db request come, c.t := t1, q1.db := cq1 does queryanother db request come, c.t := t2, q2.db := cq2 does queryq1 processes its result <- will this still be valid? remember no commit[retaining] / applyupdates hereYes after all C.T is used only as a means to get a transaction if there is none defined in the query. If you left it empty and set the Qn.T (where n might be 1 or 2 in this example) then you are ok. No need to set the db.transaction at all. eg your example should go along the lines of db request come, q1.transaction := t1, q1.db := cq1 does queryanother db request come, q2.transaction := t2, q2.db := cq2 does queryq1 processes its resultyou should be able to do what ever you want. although I would think that on most cases q1, q2 would not need to stay open together ee after reading a table in to objects in memory there is no need to keep the data both in the query and in the objects so close and free the query. same goes with the rest of the data access. ---------------------------------------------------------------------- Leledumbo · 2015-07-06 03:45 Quote from: Graeme on July 06, 2015, 01:46:24 amQuote from: Leledumbo on July 06, 2015, 12:03:50 amMoreover, the last time I tried tiOPF (and others above except mORMot which I haven't really tested) still doesn't support m-n relationship. Of course it does - I have no idea who told you otherwise. v2 or v3? v2 I tried a couple of years ago doesn't. ---------------------------------------------------------------------- Graeme · 2015-07-06 08:59 Quote from: Leledumbo on July 06, 2015, 05:45:58 amv2 or v3? v2 I tried a couple of years ago doesn't. Both support it, but if you are using the Free Pascal compiler, then you must use tiOPF v2.