Posted by Richard Lynch on 03/02/05 00:56

My current employer has designed a sort of CMS (except it has so many
site-specific hard-coded features that it's not a CMS at all) where things
happen such as:

If you are in the middle of adding a new user, their name appears with a
yellow background, and only after you fill out the other pages and hit
"Submit" on the last page, does the user really become active.

If you change a user's status, that checkbox appears in a yellow
background until they hit "submit" on the next page.

They've also got "Cancel" buttons that cancel out of your "current" action
which is stacked on top of some other action... EG: Adding a new "group"
and then adding a new "user" to that group, you can cancel out of adding
the new "user" and end up back at the new group's management page, with
the new "group" still not really created.

This behaviour is all over the place in a zillion different fields/tables
in a database I didn't design, and would just as soon not try to mess with
as much as possible.

So I'm trying to think of a Modular and consistent way to handle this...

One idea I'm pondering goes like this:

Create a session_action table, which has:
id
session_id
rank (order of operation)
query (text)

Then, at the top of each page, start a transaction which consists of ALL
the queries so far that they WOULD execute if they were on the page where
they could hit the Submit button, and they did hit the submit button.

Then, at the end of each script, ROLLBACK the transaction.

Of course, when they do hit the Submit button, do a COMMIT.

Then I'd need to either:
A) Be able to ask the database for a query to be run OUTSIDE the context
of the transaction, even though I am inside that context, OR
B) Run the queries for a page both before and after the partial
transaction, and compare result sets.

A) Sounds real nice, but I've never seen that in the MySQL manual, or any
other SQL manual... What am I supposed to Google for here?

B) is do-able, but gonna get ugly real fast in comparing result sets...

Has anybody done this in PHP (w/ MySQL) and have any hard-won experience?

Anybody got a better idea for handling this sort of design in any
reasonable fashion?

My predecessor has a zillion "temp_xyz" tables where stuff that's not yet
"submitted" is stored, and then he did funky things to work out what to
show to any given user, and I can't even figure it out, much less work
with it...

And adding a temp_xyz table for every single table in the database would
drive me nuts anyway.

I'm also considering adding a table:
create pending_actions(
user_id (who sees this pending action)
table_name (what other table will change)
action (enum{insert, delete, update})
field (name of field to change [or ID to delete/insert])
value (value to change to)

and somehow trying to do a UNION or something with that for each query.

Ugh!

Can you tell I'm not real happy with this "design" ? :-v

Open to any ideas. (or good job offers, at this point in my day :-^)

--
Like Music?
http://l-i-e.com/artists.htm

[Back to original message]