Skip to content

Instantly share code, notes, and snippets.

@nepalez
Last active August 26, 2023 05:15
  • Star 6 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Embed
What would you like to do?
# Clean the database
DROP TABLE IF EXISTS _orders CASCADE;
DROP TABLE IF EXISTS _users CASCADE;
DROP TABLE IF EXISTS orders CASCADE;
DROP TABLE IF EXISTS users CASCADE;
# Build the database (for hard deletion)
CREATE TABLE users (
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
user_id integer NOT NULL,
number text NOT NULL,
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);
# Populate it with some data
INSERT INTO users (name) VALUES ('Andrew'), ('Vladimir'), ('Sara');
INSERT INTO orders (user_id, number) VALUES (1, 'A1'), (1, 'A2'), (2, 'V1'), (2, 'V2'), (3, 'S3');
# Add columns for soft deletion
ALTER TABLE users ADD COLUMN deleted boolean NOT NULL DEFAULT false;
ALTER TABLE orders ADD COLUMN deleted boolean NOT NULL DEFAULT false;
# Hide tables on behind of views
ALTER TABLE users RENAME TO _users;
CREATE VIEW users AS SELECT * FROM _users WHERE NOT deleted;
ALTER TABLE orders RENAME TO _orders;
CREATE VIEW orders AS SELECT * FROM _orders WHERE NOT deleted;
# Add rewriting rules for views
CREATE RULE _soft_deletion AS ON DELETE TO orders DO INSTEAD (
UPDATE _orders SET deleted = true WHERE id = old.id
);
CREATE RULE _soft_deletion AS ON DELETE TO users DO INSTEAD (
UPDATE _users SET deleted = true WHERE id = old.id
);
# Add rewriting rules for associated table
CREATE RULE _delete_orders AS ON UPDATE TO _users
WHERE NOT old.deleted AND new.deleted
DO ALSO UPDATE _orders SET deleted = true WHERE user_id = old.id;
# Then check the results
DELETE FROM users WHERE name = 'Andrew';
SELECT * FROM users;
# Should not contain 'Andrew'
SELECT * FROM _users;
# Should have all records, including 'Andrew' marked as deleted
SELECT * FROM orders;
# Should not contain 'A1' and 'A2'
SELECT * FROM _orders;
# Should have all records, including 'A1' and 'A2' marked as deleted
@xiaohui-zhangxh
Copy link

try set_config

begin;
  select set_config('rules.soft_deletion', 'on', true);
  select current_setting('rules.soft_deletion');
end;

@xiaohui-zhangxh
Copy link

I fixed the destroy callback issue with this patch:

# patch lib/active_record/persistence.rb
ActiveRecord::Persistence.class_eval do
  def destroy_row
    affected_rows = _delete_row
    self.class._soft_deletion ? 1 : affected_rows
  end
end

module ActsAsLowLevelSoftDeletion
  extend ActiveSupport::Concern

  included do
    class_attribute :_soft_deletion, instance_accessor: false, instance_predicate: false
    self._soft_deletion = false
  end

  class_methods do
    def acts_as_low_level_soft_deletion
      self._soft_deletion = true
    end
  end
end

ActiveSupport.on_load(:active_record) { include ActsAsLowLevelSoftDeletion }

class Order < ActiveRecord::Base
  acts_as_low_level_soft_deletion
end

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment