1-- A table for containing the currently-running database schema. It is expected2-- to only ever contain one row, but this is not guaranteed.3CREATE TABLE schema_version (4 version integer NOT NULL5);67-- A single-row table that stores global server settings.8CREATE TABLE settings (9 id boolean PRIMARY KEY GENERATED ALWAYS AS (true) STORED,10 -- Reddit API parameters11 reddit_client_id text,12 reddit_client_secret text,13 -- The number of posts to fetch by default if nothing is specified in the14 -- request.15 default_num_posts integer NOT NULL,16 -- The number to cap fetched posts at if a client specifies a large number.17 max_num_posts integer NOT NULL,18 -- The time to wait before allowing a site to be fetched again19 site_fetch_wait interval NOT NULL,2021 -- Combined with the boolean id, this ensures there's at most one row in the table.22 CONSTRAINT ck_id_only_true CHECK (id),23 -- Ensure that both reddit parameters are either present or absent.24 CONSTRAINT ck_reddit_client_presence CHECK ((reddit_client_id IS NULL) = (reddit_client_secret IS NULL)),25 -- Keep sanity around default and max post numbers.26 CONSTRAINT ck_max_num_posts_gt_default CHECK (default_num_posts <= max_num_posts)27);2829-- Insert the default settings before locking down the table.30INSERT INTO settings (default_num_posts, max_num_posts, site_fetch_wait) VALUES (21, 70, interval '3 hours');3132-- Ignore any non-update modifications to the settings table.33CREATE RULE settings_ignore_insert AS ON INSERT TO settings DO INSTEAD NOTHING;34CREATE RULE settings_ignore_delete AS ON DELETE TO settings DO INSTEAD NOTHING;3536-- This tables holds information for individual sites or sub-sites (e.g.37-- subreddits).38CREATE TABLE sites (39 -- An arbitrary ID to uniquely identify a site.40 id serial PRIMARY KEY,41 -- The machine-readable name of the site. Each fetcher may use whatever42 -- format it sees fit. For sub-sites, they should be named "site:subsite".43 name text NOT NULL,44 -- The human-readable name of the site, for anywhere that is meant to be45 -- displayed to a user.46 display_name text NOT NULL,47 -- When the site was last fetched and written into the database.48 --49 -- This is mostly useful as a way to avoid fetching the site too frequently.50 last_fetched_time timestamptz,51 -- How many posts were fetched the last time this site was fetched.52 last_fetched_num integer53);5455-- Holds information for posts from all watched sites.56CREATE TABLE posts (57 -- An arbitrary ID to uniquely identify a post.58 id serial PRIMARY KEY,59 -- The unique ID of the post on a particular website (e.g. "t3_j6r2o8" for a60 -- Reddit post).61 site_unique_id text NOT NULL,62 -- The ID of the site this post is from.63 site_id integer NOT NULL,64 -- The user-supplied title of the post.65 title text NOT NULL,66 -- The net score of the post.67 score integer NOT NULL,68 -- When the post was created.69 created timestamptz NOT NULL,70 -- The URL linked to in the post. If null, indicates this is an internal post71 -- and the main link should be to the comments.72 url text,73 -- When the post was last fetched/updated.74 last_fetched timestamptz NOT NULL,7576 CONSTRAINT fk_posts_site_id FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE77);7879-- Keeps track of valid requests.80CREATE TABLE requests (81 -- An arbitrary ID to identify the request.82 id serial PRIMARY KEY,83 -- The host ("addr:port") making the request.84 host text NOT NULL,85 -- The site the request was for.86 site_id integer NOT NULL,87 -- The number of posts actually requested.88 num_posts integer NOT NULL,89 -- We keep this separate because it can change over time.90 default_num_posts boolean NOT NULL,91 -- When the request was made.92 timestamp timestamptz NOT NULL,9394 CONSTRAINT fk_requests_site_id FOREIGN KEY (site_id) REFERENCES sites (id) ON DELETE CASCADE95);9697CREATE UNIQUE INDEX sites_by_name ON sites (name);9899CREATE UNIQUE INDEX posts_by_site_unique_id_unique_per_site ON posts (site_id, site_unique_id);