search_where and other searches

[prev] [thread] [next] [Date index for 2004/07/06]

In some of my tables, I don't delete rows by actually deleting
them, but rather by setting a "deleted" column to a non-null
value, and all searches on this table then have the added
"WHERE deleted IS NULL" clause. I achieved this (Tony had
made a similar suggestion on this list to someone asking a
similar question) by adding the following to my relevant class:

------
# override CDBI delete and retrieve methods, for special "delete"
__PACKAGE__->set_sql(Retrieve => <<'');
        SELECT __ESSENTIAL__
        FROM   __TABLE__
        WHERE  DELETED IS NULL
        AND    %s

__PACKAGE__->set_sql(RetrieveAll => <<'');
        SELECT __ESSENTIAL__
        FROM   __TABLE__
        WHERE  DELETED IS NULL

__PACKAGE__->set_sql(retrieve_all_sorted => <<'');
        SELECT __ESSENTIAL__
        FROM __TABLE__
        WHERE DELETED IS NULL
        ORDER BY %s

__PACKAGE__->set_sql(DeleteMe => <<"");
       UPDATE __TABLE__
       SET deleted = NOW()
       WHERE  __IDENTIFIER__
--------

This has worked so far, but I recently started to use the
search_where method from CDBI::AbstractSearch. This seems
not to lend itself to such an override, and in order to use
this system I'd have to manually alter my generated queries
in the application code. Is there any easy way to accomplish
this in the driver class?

Thanks.

Jesse Sheidlower