Sharing SQLDB triad: TSQLConnection-TSQLTransaction-TSQLQuery

[Leledumbo]

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]

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?

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]

I'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]

transaction/query must be created and deleted per persisting action (insert/delete/update) and only when the data are persisting

OK, assume:

• c: connection
• c.t: connection.transaction
• t1: transaction1
• q1: query1
• q1.db: query1.database
• transaction2 t2
• q2: query2
• q2.db: query2.database

consider this scenario:

• db request come, c.t := t1, q1.db := c
• q1 does query
• another db request come, c.t := t2, q2.db := c
• q2 does query
• q1 processes its result <- will this still be valid? remember no commit[retaining] / applyupdates here

Do 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]

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.

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.pdf
• http://www.andypatterns.com/files/11561231637882AndyBulkaRelationshipManagerPattern.pdf

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.

Of course it does - I have no idea who told you otherwise.

[taazz]

transaction/query must be created and deleted per persisting action (insert/delete/update) and only when the data are persisting

OK, assume:

• c: connection
• c.t: connection.transaction
• t1: transaction1
• q1: query1
• q1.db: query1.database
• transaction2 t2
• q2: query2
• q2.db: query2.database

consider this scenario:

• db request come, c.t := t1, q1.db := c
• q1 does query
• another db request come, c.t := t2, q2.db := c
• q2 does query
• q1 processes its result <- will this still be valid? remember no commit[retaining] / applyupdates here

Yes 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 := c
• q1 does query
• another db request come, q2.transaction := t2, q2.db := c
• q2 does query
• q1 processes its result

you 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]

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.

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]

v2 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.