Rails - May 2006 - Overriding default DELETE behavior with logical deletes

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.

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

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



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

On Friday, May 19, 2006, at 1:21 AM, Konstantin Gredeskoul
wrote:
>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.
>
>2. 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).
>
>3. 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
>
>
>
>--
>Posted via http://www.ruby-forum.com/.
>_______________________________________________
>Rails mailing list
>Rails@lists.rubyonrails.org
>http://lists.rubyonrails.org/mailman/listinfo/rails
look for the acts_as_paranoid plugin.

_Kevin

-- 
Posted with http://DevLists.com.  Sign up and save your mailbox.

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

On 5/18/06, Konstantin Gredeskoul <kigsteronline@mac.com>
wrote:
> 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.
>
> 2. 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).
>
> 3. 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
>
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On 5/19/06, Konstantin Gredeskoul <kigsteronline@mac.com>
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;

On May 19, 2006, at 12:08 AM, Lugovoi Nikolai wrote:
> On 5/19/06, Konstantin Gredeskoul <kigsteronline@mac.com> 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 ? :)))
I''d say "that''s super hot, man!"

jeremy

On May 19, 2006, at 11:29 AM, Jeremy Kemper wrote:
> On May 19, 2006, at 12:08 AM, Lugovoi Nikolai wrote:
>> On 5/19/06, Konstantin Gredeskoul <kigsteronline@mac.com> 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 ? :)))
>
> I''d say "that''s super hot, man!"
That''s super hot, man!

jeremy