"Sanjay" <[EMAIL PROTECTED]> writes:
> Consider this requirement - a company has many employees, and one among
> them is the CEO.
>
> The database structure would be
>
> Company(company_id, ceo_employee_id)
> Employee(employee_id, company_id (is the foreign key to Company))
>
> Steps needed to create a new company with an employee:
>
> ceo = Employee(...)
> company = Company(...)
> comany.employees.append(ceo)
> # Flush so that you get ceo.employee_id generated
> session.flush()
> company.ceo_employee_id = ceo.employee_id
>
> In this type of case flush is necessary in the middle. I tried to avoid
> using circular foreign keys etc. but some problem came.
Why is it necessary? All of what happens inside a transaction should be
visible for everything inside such a transaction.
BEGIN;
SELECT balance FROM account; -- lets suppose it was 100
UPDATE account SET balance = balance + 100; -- 200 now...
UPDATE account SET balance = balance - 50; -- 150 now...
ROLLBACK;
SELECT balance FROM account; -- should be 100.
This is basic and I believe everyone agrees with that. But this is also
basic:
BEGIN;
INSERT INTO account (id, name, balance) VALUES (100, 'John Doe', 100.0);
INSERT INTO account (id, name, balance) VALUES (150, 'Jane Doe', 10.0);
INSERT INTO new_accounts (account_id, start_balance) VALUES
((SELECT id FROM account WHERE name = 'John Doe'),
(SELECT balance FROM account WHERE name = 'John Doe'));
INSERT INTO new_accounts (account_id, start_balance) VALUES
((SELECT id FROM account WHERE name = 'Jane Doe'),
(SELECT balance FROM account WHERE name = 'Jane Doe'));
COMMIT;
And should work the same. I.e., I can see the new IDs and data within the
transaction *before* committing it.
The same goes for more complex operations.
> In summary, as I see, in certain cases you can't avoid flushing in the
> middle. What I need is a suitable pattern to handle these cases so that
> the entire operation is either committed or rolled back. If a concrete
> example can explain things clearer, can I create a sample application
> and attach to a new ticket?
Depending on the complexity of the operation you'd better go with triggers and
stored procedures / functions on the database.
But be aware of the rules for those before using them extensively so you don't
get bitten by how they work (e.g. with regards to data visibility).
--
Jorge Godoy <[EMAIL PROTECTED]>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"TurboGears" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/turbogears?hl=en
-~----------~----~----~----~------~----~------~--~---
