Best/Right approch for master/Detail

[louis]

Hello,
Until today, with Zeos 7.2.0b, I programmed a master/Detail scenario using only with "DataSource" property of detail ZQuery and manually I managed alla statements to Post, Delete, Cancel without any automatism managed by Zeos code.

Now due to larges modify made by version 8.0 the approch I used is no longer viable.

I thing that the best/Right approch is using:
for master table the property: all property as default.

for detail table the property:

• CachedUpdates = True. So I can Modify a large details and cancel/Apply updates with an alone command on master query.

• Datasource = the Datasource of the Master Table. So I can have use the fields of master table in SQL statement of detail quey as parameters so I reduce at minimum lan traffic. Normally I have few Master with large details.

• MasterSource = Datasource of the Master Table; So the managing of statements: post/Edit/Delete/Cancel are made by Zeos code.

• MasterFields and LinkedField in according to link the master with the detail Query.

You things this approch si right?

Thanks

[marsupilami]

Hello Louis,

it really depends. I don't use the master detail link in situations like that. Usually I do everything in the AfterOpen, AfterScroll and possibly AfterClose events manually.

Please don't use cached updates. Use a transaction instead. CachedUpdates will not combine the updates into one query. It will just generate a lot of queries at one point in time. If an error happens you will not know which row causes the error. If you do the modifications immediately, you will know the error as soon as you try to save the record because Zeos will execure the change immediately. CachedUpdates is more like a poor solution for databases that don't support transactions at all.

Best regards,

Jan

[louis]

Please don't use cached updates. Use a transaction instead.

Can I have a little example of use transaction instead a CachedUpdates?
Normally I have few master records with large details per single master row.

CachedUpdates will not combine the updates into one query. It will just generate a lot of queries at one point in time. If an error happens you will not know which row causes the error. If you do the modifications immediately, you will know the error as soon as you try to save the record because Zeos will execure the change immediately.

You tells that with transaction I commit every detail rows immediately?
That's not usefull because I will have a chance of cancel all updates for the rows affect in the document that I edit, if I commit row by row for details I will not revert in the original document. I undestand right?

Thanks

[marsupilami]

Honestly - this is quite basic SQL.

You do a ZConnection.StartTransaction before you start your changes. When you are finished with your changes, you either do a ZConnection.Commit to make them permanent in the database or you do a ZConnection.Rollback if you want to cancel them and don't want them to be permanent. If you are unsure about transactions, it makes sense to read up on them.

Best regards,

Jan

[louis]

Honestly - this is quite basic SQL.

True, I come from paradox and until now I didn't give much importance to transactions.
I realize that I need to go deeper but the texts I find is in English and I have a poor english.

If I can ask You:
a) Please, can you suggest a good text to learn about transactions.
b) If I start a transaction and the LAN was down who rollback the transaction?
b) If I start a transaction because I start a new document and then go on my lunch break do I cause problems for other users?

Thanks.

[marsupilami]

True, I come from paradox and until now I didn't give much importance to transactions.
I realize that I need to go deeper but the texts I find is in English and I have a poor english.

Don't worry. Nobody knows everything. But with SQL databases transactions really are essential because they get used a lot.

a) Please, can you suggest a good text to learn about transactions.

Duh - good question. I learned about them a long time ago. Firebird links to the following document when it comes to transactions:
http://www.ibphoenix.com/resources/documents/how_to/doc_400
The short explanation for the thing that is interesting to you: A transaction wraps up multiple changes to the database in one operation. It either completes and writes all changes to the database or it fails and writes no changes at all to the database.

b) If I start a transaction and the LAN was down who rollback the transaction?

Usually servers rollback transactions automatically as soon as they register that a connection got disconnected.

b) If I start a transaction because I start a new document and then go on my lunch break do I cause problems for other users?

That really depends on the implementation and what you do. With modern databases you normally don't disturb anyone. There are limitations though. But usually these limitations are more of a problem in the program design than in the database.

[louis]

You do a ZConnection.StartTransaction before you start your changes. When you are finished with your changes, you either do a ZConnection.Commit to make them permanent in the database or you do a ZConnection.Rollback.

Please, be patient.

With property Autocommit = true of ZConnection I cannot gain this behaviour?

Or I must use the component TZTransaction?
For Example: With Zeos, if I have a Datamodule with three ZQuery (1 master and 2 detail), I must insert into Datamodule an component TZTransaction and connect it into property "Transaction" of the three ZQuery (Master + Detail1+Detail2) to have the update into the same transaction?

If I do the same into all datamodule I will have a different Transaction for every documents I Insert or Edit. Is right?

Thanks

[DPStano]

@marsupilami you are kidding, right? you just telling him to use long-living transactions instead of short ones, maybe on localhost, but on a local network or over the internet it's the worst he can do.

believe me, I spend last years rewriting every long transaction to a short one to prevent daily crashes from terminated connections, I had like 80% of all crash reports from connection lost inside a transaction (>100k/year) and rewrote a huge part of zeos 7.x to prevent that.

see https://stackoverflow.com/questions/196 ... acceptable

[marsupilami]

@marsupilami you are kidding, right?

I am kidding all the time. But why do you think that this is a good way to start a post that will make me take your opinion seriously?

you just telling him to use long-living transactions instead of short ones,

No - what I am saying is that using transactions is better than using the cached updates feature of Zeos.

you just telling him to use long-living transactions instead of short ones, maybe on localhost, but on a local network or over the internet it's the worst he can do.

So what I am missing is exactly _why_ having long running transactions on a local network is a bad idea. And then there is the fact that we are on Firebird. You do realize that with Firebird there always is an transaction? Even if it is an autocommit transaction? And finally what I really said is that using long running transactions is better than using the cached updates feature of Zeos without using explicit transactions.
Having Zeos do cached updates on an autocommit transaction really means that there will be no atomicity. But most people want to use cached updates to get exactly that - atomicity.

believe me, I spend last years rewriting every long transaction to a short one to prevent daily crashes from terminated connections, I had like 80% of all crash reports from connection lost inside a transaction (>100k/year)

I have no reason to believe you. And why exactly does your experience apply to this problem? What makes a connection loss inside of a transaction worse than a connection loss outside of a transaction? Why don't you just fix up your networks if a connection losses happen that often? And finally - what is your suggestion?
Just to make that clear: Connection losses of applications have not been a concern for any of the applications I have been developing. So it seems that your experience is different from mine.

and rewrote a huge part of zeos 7.x to prevent that.

You simply didn't. Either that or Egonhugeist has been committing your work without mentioning you. I doubt that.

see https://stackoverflow.com/questions/196 ... acceptable

So - some people have written down some problems that can arise with long running transactions. But why do you think that any of these problems will be a problem in louis application? It really depends on the number of people using that application and on the way the database and its contents gets used.
There are ways to overcome long running transactions and their problems. And most of them will mean that you trade them in for other problems. There is no "one size fits all solution". One has to think about these tradeoffs before doing design decisions.

[marsupilami]

You do a ZConnection.StartTransaction before you start your changes. When you are finished with your changes, you either do a ZConnection.Commit to make them permanent in the database or you do a ZConnection.Rollback.

Please, be patient.

Sure

With property Autocommit = true of ZConnection I cannot gain this behaviour?

You can - just use the methods StartTransaction, Commit and Rollback of TZConnection. Maybe this wiki enty about TZConnection.Autocommit can help in understanding autocommit and transactions.

Or I must use the component TZTransaction?
For Example: With Zeos, if I have a Datamodule with three ZQuery (1 master and 2 detail), I must insert into Datamodule an component TZTransaction and connect it into property "Transaction" of the three ZQuery (Master + Detail1+Detail2) to have the update into the same transaction?

No - you don't need to do that. TZConenction always has a transaction inside.

If I do the same into all datamodule I will have a different Transaction for every documents I Insert or Edit. Is right?

TZTransaction is quite new in the Zeos world. If you assign a TZTransaction component to a Query, it will do its work using that transaction.
On Firebird TZTRansaction really should be a transaction. On other RDBMS that don't support multiple transactions in their APIs it usually translates into a new connection that has the same characteristics as the original connection.

[DPStano]

No - what I am saying is that using transactions is better than using the cached updates feature of Zeos.

what about starting with his topology and how his users are gonna use his application.

Just to make that clear: Connection losses of applications have not been a concern for any of the applications I have been developing. So it seems that your experience is different from mine.

it seems so, 7? versions of lib without proper connection lost handling :/ we were begging to fix just small parts like AVs from resource releasing after connection lost, but without any response for years, so we wrote our own connection and transaction handling, connection pooling, and dataset interaction.

Firebird there always is an transaction

i cant find any info that isc_attach_database starts transaction

You simply didn't. Either that or Egonhugeist has been committing your work without mentioning you. I doubt that.

LGPL, our code is not open, svn, slow changes, difficult communication, this project was dead multiple times during last 10 years, something like that

And finally what I really said is that using long running transactions is better than using the cached updates feature of Zeos without using explicit transactions.

but you didn't say that he can have cached updates in explicit transactions, I really like to know why somebody would use cached updates in auto-commit transaction, it does not make any sense to me.

[marsupilami]

No - what I am saying is that using transactions is better than using the cached updates feature of Zeos.

what about starting with his topology and how his users are gonna use his application.

You are free to do exactly that. Why don't you do it instead of trying to shame me? Don't push your expectations on me.

Just to make that clear: Connection losses of applications have not been a concern for any of the applications I have been developing. So it seems that your experience is different from mine.

it seems so, 7? versions of lib without proper connection lost handling :/

No - we simply don't have disconnects. Our clients networks seem to be in a good condition. Even the few people who use one of my products over the internet don't experience many connection losses. Even more they don't ask me to fix connection losses. They ask me to add new features. So what do you think how much of a concern a connection loss is to them?

we were begging to fix just small parts like AVs from resource releasing after connection lost, but without any response for years,

You were begging? I didn't see you beg anywhere and even more, I didn't see you supply a patch or a fix anywhere. Also I didn't see you offering anybody any pay to fix your problems for you. Basically this means you were trying to get others to fix your problems for free. Why should I care? It isn't my duty to fix your problems.

so we rewrote connection and transaction handling, connection pooling, and dataset interaction.

And why should I care? Basically this means you took something that you could get for free but decided to not share the improvements with other users.
In the mean time Egonhugeist has done the work necessary to handle connection losses. I didn't see so much as a thumbs up from you anywhere on his work. Also imagine what he could have achieved for Zeos if you had decided to share your work.

i cant find any info that isc_attach_database starts transaction

Phew - now you got me. Or maybe not. Is there anything you can do with a database attachment without starting an transaction? So what sense does it make to have an attachment without a transaction? And how does it help with connection losses to have a database attachment but no transaction?

LGPL, our code is not open,

So - again why should I care? If you decide to keep changes to yourself, you cannot claim to have been working on "Zeos". You didn't. You were working on an internal project that is based on Zeos.

svn, slow changes, difficult communication,

Now you must be kidding. You didn't even try. You never submitted a patch or even tried to. But since you seem to not like SVN: There is a git repository on github: https://github.com/marsupilami79/zeoslib
You are free to submit pull requests there.

this project was dead multiple times during last 10 years, something like that

That is bullshit again. I just had a look. I joined this forum on 2011-01-17. mdaems was active at that time and helped users. It didn't take long for Egonhugeist to join the project afterwards. His first commit was in 20011 and from 2012 on he started to do commits on a regular basis. mdaems was still active when I took over the board administration from him. And finally: What did you do to make sure the project doesn't die? Where is your commitment?

but you didn't say that he can have cached updates in explicit transactions,

And why didn't you do that instead of trying to shame me? Why don't you give support for people for once instead of telling me how you tink I should give support? See - if you gave support I could be doing other things. Like writing the release notes for Zeos 8.0 which currently is the biggest show stopper for a proper release.
This approach to use cached updates with transactions has downsides too. If you get an error message while doing an ApplyUpdates you don't get to know which record change provoked the error. When submitting each change to the database when it happens you get to know which record caused the error. And last but not least there have been errors in AutoCommit with transactions that were only solved in the last 12 months or so. CachedUpdates is a feature that doesn't get used very often.

I really like to know why somebody would use cached updates in auto-commit transaction, it does not make any sense to me.

There are engines out there that don't support transactions. Microsofts ACE for example. Cached Updates are the closest thing one can get to transactions on these beasts.

[louis]

Sure

Thank you and sorry for the flame that was generated in the other posts.

You can - just use the methods StartTransaction, Commit and Rollback of TZConnection. Maybe this wiki enty about TZConnection.Autocommit can help in understanding autocommit and transactions.

Firebird is ever in transaction, Ok.

No - you don't need to do that. TZConenction always has a transaction inside.

With Autocommit=True Zeos do a Commit every Post in every query but in Master/detail I thing is best do a explicit StartTransaction after insert in Zquery master and do a Commit after the Post in Query master and in this case even the details are saved in database all at once?
Zeos do this with the transaction inside the zconnection, Is right?

But in a document Master/detail, example an Invoice, during insert of data there my be the need to insert customer data in table customers or Item in a items table, in that case user suspend the invoice and pass to customer's form and insert the data, in this case if I start a new transaction (because I need to insert the customer data) i need to do a commit for that transaction for only customer data, zeos commit only customer data or at alla customer data and invoice data? Or in this circumstance is best using the component TZTransaction?

Thanks a lot for your time to spend to me. Learning, hope I can reciprocate

[marsupilami]

Thank you and sorry for the flame that was generated in the other posts.

Don't worry

You can - just use the methods StartTransaction, Commit and Rollback of TZConnection. Maybe this wiki enty about TZConnection.Autocommit can help in understanding autocommit and transactions.

Firebird is ever in transaction, Ok.

Yes - but also there is a scheme between being in Autocommit mode and having no automatic commit

With Autocommit=True Zeos do a Commit every Post in every query

Yes - more or less. Usually it is the database that is doing the autocommit.

but in Master/detail I thing is best do a explicit StartTransaction after insert in Zquery master and do a Commit after the Post in Query master and in this case even the details are saved in database all at once?
Zeos do this with the transaction inside the zconnection, Is right?

Yes - you can replace Cached Updates with a StartTransaction/Commit cycle. All changes will be posted to the database immediately. After the modifications you do a commit to make it permanent. If you don't want the changes for some reason, you will Rollback your changes.

But in a document Master/detail, example an Invoice, during insert of data there my be the need to insert customer data in table customers or Item in a items table, in that case user suspend the invoice and pass to customer's form and insert the data, in this case if I start a new transaction (because I need to insert the customer data) i need to do a commit for that transaction for only customer data, zeos commit only customer data or at alla customer data and invoice data? Or in this circumstance is best using the component TZTransaction?

If you want to separate the changes you could use TZTransaction.

One more word though: I don't know your requirements. In most programs that we wrote for generating invoices we found out that using transactions is overkill. We generate the header (Master) but leave the invoice number empty. This marks the invoice as being not finished. Then we have the user generate the items on the invoice (Detail). Every item and every modification gets written to the database immediately. Usually our clients don't want or need that all or nothing approach. Once they finish the invoice, they add the invoice number and print it. We usually do all this in autocommit mode, without needing transactions. THe only thing that we wrap up in an transaction is the assignment of the invoice number because there were some special requireements. But even here that is not necessary anymore.

[aehimself]

LGPL, our code is not open

Just a quick question... I'm far away from completely understanding all conditions of licenses (or being a lawyer) but according to LGPL changes made to the original source must be made public, no?