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.
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.
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
