so youd like to leave a row in "account_activity", containing the
primary key identity of a row in "account" which was deleted ?
this is not an issue of cacade from the ORM perspective, the "keeping
referential integrity" is a separate feature...theres a
DependencyProcessor that keeps the relationships intact, and it
doesnt work in just certain directions, it works in all of them (i.e.
child added to parent, child removed from parent, parent deleted,
etc.). i dont think theres a clean hook for this, youd have to
patch a modified DependencyProcessor into the PropertyLoader or
something like that.
since youre not really using a normal database relationship, youd
probably want to forego using a relation() altogether and implement
your logging via a MapperExtension that inserts log rows into
account_activity manually as Accounts are saved or updated.
On Sep 3, 2006, at 6:39 PM, William K. Volkman wrote:
> 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
-------------------------------------------------------------------------
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
