"del db.my_table[id]" shortcut not working any more after enabling record versioning

[Horst Horst]

I have a controller function for record deletion which worked reliably for quite some time.

Today I've added

  added auth.enable_record_versioning(db)

to my model file for the first time (my tables had auth.signature fields already).

Since then, I get 

  <type 'exceptions.SyntaxError'> No such record: 230

tickets whenever I call 

  del db.piece[id]

However,

  db(db.piece.id == id).delete()

works fine.

(And yes, the records *do* exist.)

I've found this thread here https://groups.google.com/forum/#!topic/web2py/rNv8oARIttI where someone appears to have a similar problem. (There's no conclusion though.)

[Massimo Di Pierro]

del table[id]

is more picky. Raises an exception if id is not int and if the record does not exist.

[Horst Horst]

Hi Massimo,

del db.piece[int(id)]

doesn't work either. But I can definitely confirm that it depends on the

auth.enable_record_versioning(db)

being enabled in the model file. If I comment out the line, the error does not occur, if I put it in, the error comes back.

[Anthony]

Looks like for now the del db.mytable[id] syntax is incompatible with enable_record_versioning. By default, enable_record_versioning shortcuts the delete process via a _before_delete callback -- rather than deleting the record, it simply sets the is_active field to False. This causes the .delete() method to return 0, which makes the Table.__delitem__ method think the delete failed, leading to the exception.

Not sure of a good way around this. The .delete() method returns a 0 if either the delete fails or if any of the _before_delete callbacks returns a non-falsey value. There also does not appear to be a way to verify for certain that a particular table is being archived via enable_record_versioning.

Anthony

[Horst Horst]

I don't understand the rationale behind the way record versioning is implemented: If you delete a record, it is marked as is_active=False in the original table, but it is also copied to the my_table_archive table.

Why this doubling of data? And /if/ data is doubled, why then not immediately when records are inserted (into both, the regular table and the archive), so that deletion could indeed delete from the original table?

Wouldn't this avoid our problem here, and also this other one: https://groups.google.com/forum/#!topic/web2py/9UwH5uR4uew ?

[Anthony]

On Monday, February 17, 2014 7:24:24 AM UTC-5, Horst Horst wrote:

I don't understand the rationale behind the way record versioning is implemented: If you delete a record, it is marked as is_active=False in the original table, but it is also copied to the my_table_archive table.

Why this doubling of data? And /if/ data is doubled, why then not immediately when records are inserted (into both, the regular table and the archive), so that deletion could indeed delete from the original table?

As mentioned here, deletion is only prevented if the table in question includes an "is_active" field. So, if you don't include an "is_active" field, you will get a complete record deletion after the archiving.

You can also prevent the is_active behavior by using the Table._enable_record_versioning method rather than auth.enable_record_versioning. The former takes an "is_active" argument, which is used to specify the name of the "is_active" field (in case you want to name it something else) -- if you pass in any falsey value for that argument, you don't get the is_active behavior (even if the table does in fact include an "is_active" field).

This could probably be clarified in the book (particularly since record versioning is discussed in two different places).

Anthony

[Niphlod]

the is_active = 0 is actually a good rationale because of relational integrity.
Dropping a row with dependants record on it would mean dropping also the dependant record(s), that can't be then "archived" properly.

[Horst Horst]

Thanks, Anthony. In fact, I was completely missing the other enable_record_versioning section in the DAL chapter.  

[Horst Horst]

I guess I'm too dumb, but if there's a transaction before and after which referential integrity of the DB if given, and the records of all tables involved are backed up on their respective updates, why are inactive records in the original tables needed? I understand that the backup records need to refer to the former IDs in the original tables, but why do the inactive records themselves need to exist?

[Horst Horst]

I've just tried db.piece._enable_record_versioning(is_active = None), but this gives me yet another behavior: Backup records appear in the archive database on updates, but deleting records does not create backups but the opposite: It deletes the record from the original table as well as all backups referring to it.

Am Montag, 17. Februar 2014 14:02:36 UTC+1 schrieb Anthony:

[Anthony]

The problem is that the archive table includes a "current_record" field that references the original table, and because ondelete defaults to "CASCADE", when the original record is deleted, so are all the records that point to it.

It appears the current system is designed to store previous versions of records that still exist, not to archive records that no longer exist. If we want to enable archiving of deleted records, we would have to think about how it should work. In that case, all previous versions would have invalid references, so there would be no way to know they are related to each other. Probably the thing to do would be to archive the final version of the record and then change all previous versions to reference the archived final version. Maybe open a Google Code issue to request this feature.

Anthony