lemmy/migrations/2020-03-26-192410_add_activitypub_tables/up.sql
Dessalines be1389420b
Adding SQL format checking via pg_format / pgFormatter (#3740)
* SQL format checking, 1.

* SQL format checking, 2.

* SQL format checking, 3.

* SQL format checking, 4.

* SQL format checking, 5.

* Running pg_format

* Getting rid of comment.

* Upping pg_format version.

* Using git ls-files for sql format check.

* Fixing sql lints.

* Addressing PR comments.
2023-08-02 12:44:51 -04:00

35 lines
1.6 KiB
SQL

-- The Activitypub activity table
-- All user actions must create a row here.
CREATE TABLE activity (
id serial PRIMARY KEY,
user_id int REFERENCES user_ ON UPDATE CASCADE ON DELETE CASCADE NOT NULL, -- Ensures that the user is set up here.
data jsonb NOT NULL,
local boolean NOT NULL DEFAULT TRUE,
published timestamp NOT NULL DEFAULT now(),
updated timestamp
);
-- Making sure that id is unique
CREATE UNIQUE INDEX idx_activity_unique_apid ON activity ((data ->> 'id'::text));
-- Add federation columns to the two actor tables
ALTER TABLE user_
-- TODO uniqueness constraints should be added on these 3 columns later
ADD COLUMN actor_id character varying(255) NOT NULL DEFAULT 'http://fake.com', -- This needs to be checked and updated in code, building from the site url if local
ADD COLUMN bio text, -- not on community, already has description
ADD COLUMN local boolean NOT NULL DEFAULT TRUE,
ADD COLUMN private_key text, -- These need to be generated from code
ADD COLUMN public_key text,
ADD COLUMN last_refreshed_at timestamp NOT NULL DEFAULT now() -- Used to re-fetch federated actor periodically
;
-- Community
ALTER TABLE community
ADD COLUMN actor_id character varying(255) NOT NULL DEFAULT 'http://fake.com', -- This needs to be checked and updated in code, building from the site url if local
ADD COLUMN local boolean NOT NULL DEFAULT TRUE,
ADD COLUMN private_key text, -- These need to be generated from code
ADD COLUMN public_key text,
ADD COLUMN last_refreshed_at timestamp NOT NULL DEFAULT now() -- Used to re-fetch federated actor periodically
;
-- Don't worry about rebuilding the views right now.