32_add_trash_table.up.sql 1.1 KB

123456789101112131415161718192021222324252627282930
  1. CREATE TABLE IF NOT EXISTS trash
  2. (
  3. file_id BIGINT NOT NULL,
  4. user_id BIGINT NOT NULL,
  5. collection_id BIGINT NOT NULL,
  6. -- is_deleted true indicates file has been deleted and cannot be restored.
  7. is_deleted bool NOT NULL DEFAULT false,
  8. -- true indicates file was moved to trash but user restored it before deletion.
  9. is_restored bool NOT NULL default false,
  10. created_at bigint NOT NULL DEFAULT now_utc_micro_seconds(),
  11. updated_at bigint NOT NULL DEFAULT now_utc_micro_seconds(),
  12. delete_by bigint NOT NULL,
  13. PRIMARY KEY (file_id),
  14. CONSTRAINT fk_trash_keys_collection_files
  15. FOREIGN KEY (file_id, collection_id)
  16. REFERENCES collection_files (file_id, collection_id)
  17. ON DELETE NO ACTION
  18. );
  19. CREATE INDEX IF NOT EXISTS trash_updated_at_time_index ON trash (updated_at);
  20. ALTER TABLE trash
  21. ADD CONSTRAINT trash_state_constraint CHECK (is_deleted is FALSE or is_restored is FALSE);
  22. CREATE TRIGGER update_trash_updated_at
  23. BEFORE UPDATE
  24. ON trash
  25. FOR EACH ROW
  26. EXECUTE PROCEDURE
  27. trigger_updated_at_microseconds_column();