37_public_collection.up.sql 3.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778
  1. CREATE TABLE IF NOT EXISTS public_collection_tokens
  2. (
  3. id bigint primary key generated always as identity,
  4. collection_id BIGINT NOT NULL,
  5. access_token TEXT NOT NULL,
  6. is_disabled bool not null DEFAULT FALSE,
  7. -- 0 value for valid_till indicates that the link never expires.
  8. valid_till bigint not null DEFAULT 0,
  9. -- 0 device limit indicates no limit
  10. device_limit int not null DEFAULT 0,
  11. created_at bigint NOT NULL DEFAULT now_utc_micro_seconds(),
  12. updated_at bigint NOT NULL DEFAULT now_utc_micro_seconds(),
  13. CONSTRAINT fk_public_tokens_collection_id
  14. FOREIGN KEY (collection_id)
  15. REFERENCES collections (collection_id)
  16. ON DELETE CASCADE
  17. );
  18. CREATE UNIQUE INDEX IF NOT EXISTS public_active_collection_unique_idx ON public_collection_tokens (collection_id, is_disabled) WHERE is_disabled = FALSE;
  19. CREATE UNIQUE INDEX IF NOT EXISTS public_access_tokens_unique_idx ON public_collection_tokens (access_token);
  20. CREATE TABLE IF NOT EXISTS public_collection_access_history
  21. (
  22. share_id bigint,
  23. ip text not null,
  24. user_agent text not null,
  25. created_at bigint NOT NULL DEFAULT now_utc_micro_seconds(),
  26. CONSTRAINT unique_access_sid_ip_ua UNIQUE (share_id, ip, user_agent),
  27. CONSTRAINT fk_public_history_token_id
  28. FOREIGN KEY (share_id)
  29. REFERENCES public_collection_tokens (id)
  30. ON DELETE CASCADE
  31. );
  32. CREATE INDEX IF NOT EXISTS public_access_share_id_idx ON public_collection_access_history (share_id);
  33. CREATE TABLE IF NOT EXISTS public_abuse_report
  34. (
  35. share_id bigint,
  36. ip text not null,
  37. user_agent text not null,
  38. url text not null,
  39. reason text not null,
  40. u_comment varchar(10000) not null DEFAULT '',
  41. created_at bigint NOT NULL DEFAULT now_utc_micro_seconds(),
  42. CONSTRAINT unique_report_sid_ip_ua UNIQUE (share_id, ip, user_agent),
  43. CONSTRAINT fk_public_abuse_report_token_id
  44. FOREIGN KEY (share_id)
  45. REFERENCES public_collection_tokens (id)
  46. ON DELETE CASCADE
  47. );
  48. CREATE INDEX IF NOT EXISTS public_abuse_share_id_idx ON public_abuse_report (share_id);
  49. CREATE TRIGGER update_public_collection_tokens_updated_at
  50. BEFORE UPDATE
  51. ON public_collection_tokens
  52. FOR EACH ROW
  53. EXECUTE PROCEDURE
  54. trigger_updated_at_microseconds_column();
  55. CREATE OR REPLACE FUNCTION fn_update_collections_updation_time_using_update_at() RETURNS TRIGGER AS $$
  56. BEGIN
  57. --
  58. IF (TG_OP = 'UPDATE' OR TG_OP = 'INSERT') THEN
  59. UPDATE collections SET updation_time = NEW.updated_at where collection_id = new.collection_id and
  60. updation_time < New.updated_at;
  61. RETURN NEW;
  62. END IF;
  63. END;
  64. $$ LANGUAGE plpgsql;
  65. CREATE TRIGGER trigger_collection_updation_time_on_collection_tokens_updated
  66. AFTER INSERT OR UPDATE
  67. ON public_collection_tokens
  68. FOR EACH ROW
  69. EXECUTE PROCEDURE
  70. fn_update_collections_updation_time_using_update_at();