Overriding default DELETE behavior with logical deletes

Rails
1

Hello!

I am trying to figure out how to implement logical deletes instead of
physical deletes using ActiveRecord. Basically, in many applications
that deal with E-Commerce, you really can’t physically delete almost
anything, because records must be kept for auditing and customer service
tracking purposes.

In the past, I’ve implemented logical deletes as follows:

  1. Oracle implementation:

Each table that must implement logical deletes has a view, eg:

USERS_RAW => real table, has columns:
is_deleted(boolean,default false)
date_deleted(timestamp, default null)

USERS => is a view on USERS_RAW (where isdeleted = false)

Oracle allows updating such a view without any extra triggers, so from
RoR point of view this is completely transparent.

PostgreSQL allows updating views, but with extra triggers, which is ugly
and not what I would like to do.

  1. Hibernate Implementation

In hibernate when you map a table you can specify that any query on this
table must also have an extra clause appended to it, so adding
“is_deleted = false” makes it work out of the box (except for primary
key fetches unfortunately, but this can be overriden in a model
superclass).

  1. Rails Implementation

What I would like is that all finders and by-id look ups automatically
append “is_deleted == false” to all SQL queries for a specific set of
classes (which could subclass eg “LogicallyDeletable” model super
class).

Any pointers on how to implement this elegantly are much appreciated.

Thanks,
Konstantin

2

On Friday, May 19, 2006, at 1:21 AM, Konstantin G. wrote:

  1. Oracle implementation:
    RoR point of view this is completely transparent.
    superclass).
    Thanks,
    Konstantin

–
Posted via http://www.ruby-forum.com/.

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

look for the acts_as_paranoid plugin.

_Kevin

3

Put an on delete do instead rule on the base table. Easy, and not ugly.

4

On 5/19/06, Konstantin G. [email protected] wrote:

Hello!

I am trying to figure out how to implement logical deletes instead of
physical deletes using ActiveRecord.
[snip]
PostgreSQL allows updating views, but with extra triggers, which is ugly
and not what I would like to do.

What would you say about pure pl/pgSQL acts_as_paranoid ? :)))

BEGIN;
CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = ‘{}’ );

CREATE FUNCTION acts_as_paranoid(_table_name text, _view_name text,
_pkey_name text, _is_deleted_column_name text) returns void as $$
DECLARE
_column_list TEXT;
_update_list TEXT;
_insert_list TEXT;
_stmt_text TEXT;
BEGIN
SELECT
array_to_string(col_list, ‘,’) as column_list,
array_to_string(new_list, ‘,’) as insert_list,
array_to_string(upd_list, ‘,’) as update_list
FROM
(SELECT
array_accum(attname) as col_list,
array_accum(‘NEW.’||attname) as new_list,
array_accum(attname || ’ = NEW.'||attname) as upd_list
FROM pg_attribute JOIN pg_class c ON (attrelid = c.oid)
WHERE NOT attisdropped AND attnum > 0 AND relname = _table_name
AND attname <> _is_deleted_column_name ) as A
INTO _column_list, _insert_list, _update_list;

EXECUTE 'CREATE VIEW ’ || _view_name ||
’ AS SELECT ’ || _column_list ||
’ FROM ’ || _table_name || ’ WHERE NOT ’ ||
_is_deleted_column_name;

EXECUTE 'CREATE RULE ’ || _view_name || '_del AS ON DELETE TO '||
_view_name ||
’ DO INSTEAD UPDATE ’ || _table_name ||
’ SET '||_is_deleted_column_name || ’ = true WHERE ‘||
_pkey_name || ’ = OLD.’|| _pkey_name;

EXECUTE 'CREATE RULE ’ || _view_name || '_ins AS ON INSERT TO ‘||
_view_name ||
’ DO INSTEAD INSERT INTO ‘|| _table_name ||
‘(’||_column_list || ‘) VALUES (’||_insert_list||’)’;

EXECUTE 'CREATE RULE ’ || _view_name || '_upd AS ON UPDATE TO '||
_view_name ||
’ DO INSTEAD UPDATE '|| _table_name || ’ SET ‘||_update_list
||
’ WHERE ’ || _pkey_name || ’ = OLD.’ || _pkey_name;

RETURN;
END$$ language ‘plpgsql’;

CREATE FUNCTION acts_as_paranoid (_table_name text, _view_name text)
returns void as $$
BEGIN
PERFORM acts_as_paranoid( $1 , $2, ‘id’, ‘is_deleted’);
RETURN;
END$$ language ‘plpgsql’;

– EXAMPLE OF USAGE
create table a_data (
id serial primary key,
my_data text not null,
is_deleted boolean not null default false
);

SELECT acts_as_paranoid (‘a_data’, ‘data’);

insert into data (id, my_data) values (1, ‘Test 1’);
insert into data (id, my_data) values (2, ‘Test 2’);
insert into data (id, my_data) values (3, ‘Test 3’);
select * from data;
select * from data where id > 2;
delete from data where id > 2;
select * from data;
update data set my_data = my_data || ’ updated ’ || text(id) ;
select * from data;
ROLLBACK;

5

On May 19, 2006, at 12:08 AM, Lugovoi N. wrote:

What would you say about pure pl/pgSQL acts_as_paranoid ? :)))

I’d say “that’s super hot, man!”

jeremy

6

On May 19, 2006, at 11:29 AM, Jeremy K. wrote:

and not what I would like to do.

What would you say about pure pl/pgSQL acts_as_paranoid ? :)))

I’d say “that’s super hot, man!”

That’s super hot, man!

jeremy