Hi Michael,
On Sun, 2006-09-03 at 10:58 -0400, Michael Bayer wrote:
> child records in a relationship are only deleted when the "delete" cascade
> rule is set (which is roughly equivalent to the old "private=True" flag).
>
> if the parent record is deleted without the "delete" cascade rule (the
> default cascade is only "save-update"), then the foreign key column in the
> child record (if any) get set to NULL before the parent record is deleted,
> and the row is not removed.
Is there a way to specify "do nothing"? The documentation on cascade
rules seems to imply that there isn't (or would cascade=None do it?).
> for this particular problem, since the activity records are associated
> with the account record via a foreign key relationship, the log records
> become a lot less useful when the parent record is deleted since they lose
> a lot of information about themselves. typically when a table stores
> logging history about some item for which it has a direct foreign key
> relationship, the item is either never deleted (and is instead marked with
> a 'deleted' flag) or it is moved to some kind of "history" table.
With tables like "order" and "order_items" I agree that information
would be lost if the "order" entry went away. However this particular
case is "Account" for which implementing a deleted flag has security
and mild performance implications. A "history" table would only
be useful if there was a substantial amount of information useful
after an account is deleted. For the most part adding additional
"event_data" fields in the activity table could obviate the need for
a "history" table. A correctly implemented activity table (assuming
it's an audit trail) would only allow insert operations.
A corner use case perhaps however a needed one.
(thank you James for bringing this up, I'm in the process of
defining the architecture for an audit process of a financial
application so your question permitted an elucidation of
a portion of the problem space)
Cheers,
William.
> William K. Volkman wrote:
> > A follow on question I have is if the relationship is specified
> > this way can you prevent the deletion of the activity
> > records when the account record is deleted?
> >
> > On Sat, 2006-09-02 at 11:25 -0400, Michael Bayer wrote:
> >> since your AccountActivity does have an actual relationship to the
> >> Account
> >> table, then yes, to go totally ORM would imply that youd have a relation
> >> on the Account mapping.
> >>
> >> mapper(Account, accounts, properties={
> >> 'activity':relation(AccountActivity, lazy=True)
> >> }
> >> )
> >> mapper(AccountActivity, accountactivity)
> >>
> >> when you create a relation as above, it gets a default "cascade" rule of
> >> "save-update", which indicates that the "save" operation on the parent
> >> Account object will result automatically in a "save" operation on the
> >> child AccountActivity object.
> >>
> >> if you have potentially many AccountActivity objects for a particular
> >> Account, and you would like to be able to append AccountActivity
> >> instances
> >> without forcing a load of all the other objects, you can specify
> >> "lazy=None" on the relationship, which means the relationship will never
> >> automatically load its contents.
> >>
> >>
> >> James Tauber wrote:
> >> >
> >> > I have a table for user accounts[1] and another one[2] for logging
> >> > all account activity (signup, activation, login, logout, etc)
> >> >
> >> > Not surprisingly, the account activity table has a foreign key of
> >> > account id relating it to the account table.
> >> >
> >> > I have an Account class defined:
> >> >
> >> > class Account(object):
> >> >
> >> > def __init__(self, email_address, full_name, password):
> >> > self.email_address = email_address
> >> > self.full_name = full_name
> >> > self.password_sha = sha.sha(password).hexdigest()
> >> >
> >> > which is mapped to the table with:
> >> >
> >> > accounts_mapper = mapper(Account, accounts)
> >> >
> >> > So I can create new accounts easily with:
> >> >
> >> > new_account = Account("[EMAIL PROTECTED]", "James Tauber",
> >> > "mypassword")
> >> > session.save(new_account)
> >> > session.flush()
> >> >
> >> > However, I'm wondering the best way to log the account creation (i.e.
> >> > signup). I can do it after the fact with something like:
> >> >
> >> > activity = AccountActivity()
> >> > activity.account_id = new_account.account_id
> >> > activity.time_stamp = time.time()
> >> > activity.activity_type = "SIGNUP"
> >> > session.save(activity)
> >> > session.flush()
> >> >
> >> >
> >> > but I'm just wondering if there's a good way I can do it as part of
> >> > the Account creation itself.
> >> >
> >> > I have done this sort of thing previously in PostgreSQL with triggers
> >> > but I'd like a more DB-neutral approach (that will also work with
> >> > sqlite) using SQLAlchemy.
> >> >
> >> > Any tips?
> >> >
> >> > Would a better approach be to add a property:
> >> >
> >> > accounts_mapper.add_property("activity", relation(AccountActivity))
> >> >
> >> > and then just do a self.activity.append(AccountActivity(...))?
> >> >
> >> >
> >> >
> >> > James
> >> >
> >> >
> >> > [1]
> >> >
> >> > accounts = Table("accounts", meta,
> >> > Column("account_id", Integer, primary_key = True),
> >> > Column("email_address", String(255), unique = True, nullable =
> >> > False),
> >> > Column("full_name", String(255)),
> >> > Column("password_sha", String(40), nullable = False),
> >> > Column("status", String(6), default = "NEW", nullable = False),
> >> > # NEW, ACTIVE, LOCKED
> >> > )
> >> >
> >> >
> >> > [2]
> >> >
> >> > account_activity = Table("account_activity", meta,
> >> > Column("account_activity_id", Integer, primary_key = True),
> >> > Column("account_id", Integer, ForeignKey("accounts.account_id"),
> >> > nullable = False),
> >> > Column("time_stamp", DateTime, nullable = False),
> >> > Column("activity_type", String(8), nullable = False), # SIGNUP,
> >> > ACTIVATE, RESET, LOGIN, LOGOUT
> >> > )
-------------------------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
