feat(web/converse): Import repository r/2440

Imports the converse forum software I wrote a few years ago. I want to
clean this up a bit and try using Hotwire with it.

Note: The original repository was AGPL-3.0 licensed. I'm the copyright
holder and have relicensed it to GPL-3.0 in the commit that is being
merged.

Imported from: https://github.com/tazjin/converse

git-subtree-dir: web/converse
git-subtree-mainline: 386afdc794eefd5bcbc47a3fd7b898a07f69f978
git-subtree-split: 09168021e7405f6b83798d0b43aa6e69e744ae87
Change-Id: Ia8b587db5174ef5b3c52910d3d027199150c58e0

25 files changed, 356 insertions, 0 deletions

diff --git a/web/converse/migrations/.gitkeep b/web/converse/migrations/.gitkeep
new file mode 100644
index 000000000000..e69de29bb2d1
--- /dev/null
+++ b/web/converse/migrations/.gitkeep
diff --git a/web/converse/migrations/00000000000000_diesel_initial_setup/down.sql b/web/converse/migrations/00000000000000_diesel_initial_setup/down.sql
new file mode 100644
index 000000000000..a9f526091194
--- /dev/null
+++ b/web/converse/migrations/00000000000000_diesel_initial_setup/down.sql
@@ -0,0 +1,6 @@
+-- This file was automatically created by Diesel to setup helper functions
+-- and other internal bookkeeping. This file is safe to edit, any future
+-- changes will be added to existing projects as new migrations.
+
+DROP FUNCTION IF EXISTS diesel_manage_updated_at(_tbl regclass);
+DROP FUNCTION IF EXISTS diesel_set_updated_at();
diff --git a/web/converse/migrations/00000000000000_diesel_initial_setup/up.sql b/web/converse/migrations/00000000000000_diesel_initial_setup/up.sql
new file mode 100644
index 000000000000..d68895b1a7b7
--- /dev/null
+++ b/web/converse/migrations/00000000000000_diesel_initial_setup/up.sql
@@ -0,0 +1,36 @@
+-- This file was automatically created by Diesel to setup helper functions
+-- and other internal bookkeeping. This file is safe to edit, any future
+-- changes will be added to existing projects as new migrations.
+
+
+
+
+-- Sets up a trigger for the given table to automatically set a column called
+-- `updated_at` whenever the row is modified (unless `updated_at` was included
+-- in the modified columns)
+--
+-- # Example
+--
+-- ```sql
+-- CREATE TABLE users (id SERIAL PRIMARY KEY, updated_at TIMESTAMP NOT NULL DEFAULT NOW());
+--
+-- SELECT diesel_manage_updated_at('users');
+-- ```
+CREATE OR REPLACE FUNCTION diesel_manage_updated_at(_tbl regclass) RETURNS VOID AS $$
+BEGIN
+    EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s
+                    FOR EACH ROW EXECUTE PROCEDURE diesel_set_updated_at()', _tbl);
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION diesel_set_updated_at() RETURNS trigger AS $$
+BEGIN
+    IF (
+        NEW IS DISTINCT FROM OLD AND
+        NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at
+    ) THEN
+        NEW.updated_at := current_timestamp;
+    END IF;
+    RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
diff --git a/web/converse/migrations/2018-04-08-133240_create_posts/down.sql b/web/converse/migrations/2018-04-08-133240_create_posts/down.sql
new file mode 100644
index 000000000000..8ebcef8e131a
--- /dev/null
+++ b/web/converse/migrations/2018-04-08-133240_create_posts/down.sql
@@ -0,0 +1,2 @@
+DROP TABLE posts;
+DROP TABLE threads;
diff --git a/web/converse/migrations/2018-04-08-133240_create_posts/up.sql b/web/converse/migrations/2018-04-08-133240_create_posts/up.sql
new file mode 100644
index 000000000000..7cf601fa2c54
--- /dev/null
+++ b/web/converse/migrations/2018-04-08-133240_create_posts/up.sql
@@ -0,0 +1,13 @@
+CREATE TABLE threads (
+  id SERIAL PRIMARY KEY,
+  title VARCHAR NOT NULL,
+  body TEXT NOT NULL,
+  posted TIMESTAMPTZ NOT NULL
+);
+
+CREATE TABLE posts (
+  id SERIAL PRIMARY KEY,
+  thread SERIAL REFERENCES threads (id),
+  body TEXT NOT NULL,
+  posted TIMESTAMPTZ NOT NULL
+);
diff --git a/web/converse/migrations/2018-04-08-161017_joinable_posts/down.sql b/web/converse/migrations/2018-04-08-161017_joinable_posts/down.sql
new file mode 100644
index 000000000000..cc7874b410ee
--- /dev/null
+++ b/web/converse/migrations/2018-04-08-161017_joinable_posts/down.sql
@@ -0,0 +1 @@
+ALTER TABLE posts RENAME COLUMN thread_id TO thread;
diff --git a/web/converse/migrations/2018-04-08-161017_joinable_posts/up.sql b/web/converse/migrations/2018-04-08-161017_joinable_posts/up.sql
new file mode 100644
index 000000000000..9713de6cfc4b
--- /dev/null
+++ b/web/converse/migrations/2018-04-08-161017_joinable_posts/up.sql
@@ -0,0 +1 @@
+ALTER TABLE posts RENAME COLUMN thread TO thread_id;
diff --git a/web/converse/migrations/2018-04-08-172739_default_posted/down.sql b/web/converse/migrations/2018-04-08-172739_default_posted/down.sql
new file mode 100644
index 000000000000..64dc07e0666a
--- /dev/null
+++ b/web/converse/migrations/2018-04-08-172739_default_posted/down.sql
@@ -0,0 +1,2 @@
+ALTER TABLE threads ALTER COLUMN posted DROP DEFAULT;
+ALTER TABLE posts ALTER COLUMN posted DROP DEFAULT;
diff --git a/web/converse/migrations/2018-04-08-172739_default_posted/up.sql b/web/converse/migrations/2018-04-08-172739_default_posted/up.sql
new file mode 100644
index 000000000000..afca8181cc56
--- /dev/null
+++ b/web/converse/migrations/2018-04-08-172739_default_posted/up.sql
@@ -0,0 +1,2 @@
+ALTER TABLE threads ALTER COLUMN posted SET DEFAULT (NOW() AT TIME ZONE 'UTC');
+ALTER TABLE posts ALTER COLUMN posted SET DEFAULT (NOW() AT TIME ZONE 'UTC');
diff --git a/web/converse/migrations/2018-04-08-182319_add_authors/down.sql b/web/converse/migrations/2018-04-08-182319_add_authors/down.sql
new file mode 100644
index 000000000000..8ad817908065
--- /dev/null
+++ b/web/converse/migrations/2018-04-08-182319_add_authors/down.sql
@@ -0,0 +1,5 @@
+ALTER TABLE threads DROP COLUMN author_name;
+ALTER TABLE threads DROP COLUMN author_email;
+
+ALTER TABLE posts DROP COLUMN author_name;
+ALTER TABLE posts DROP COLUMN author_email;
diff --git a/web/converse/migrations/2018-04-08-182319_add_authors/up.sql b/web/converse/migrations/2018-04-08-182319_add_authors/up.sql
new file mode 100644
index 000000000000..ad5beb9f6785
--- /dev/null
+++ b/web/converse/migrations/2018-04-08-182319_add_authors/up.sql
@@ -0,0 +1,10 @@
+-- This migration adds an 'author' column to the thread & post table.
+-- Authors don't currently exist as independent objects in the
+-- database as most user management is simply delegated to the OIDC
+-- provider.
+
+ALTER TABLE threads ADD COLUMN author_name VARCHAR NOT NULL DEFAULT 'anonymous';
+ALTER TABLE threads ADD COLUMN author_email VARCHAR NOT NULL DEFAULT 'unknown@example.org';
+
+ALTER TABLE posts ADD COLUMN author_name VARCHAR NOT NULL DEFAULT 'anonymous';
+ALTER TABLE posts ADD COLUMN author_email VARCHAR NOT NULL DEFAULT 'unknown@example.org';
diff --git a/web/converse/migrations/2018-04-14-140818_posts_only_in_posts/down.sql b/web/converse/migrations/2018-04-14-140818_posts_only_in_posts/down.sql
new file mode 100644
index 000000000000..bb6528f33d78
--- /dev/null
+++ b/web/converse/migrations/2018-04-14-140818_posts_only_in_posts/down.sql
@@ -0,0 +1 @@
+ALTER TABLE threads ADD COLUMN body TEXT NOT NULL DEFAULT '';
diff --git a/web/converse/migrations/2018-04-14-140818_posts_only_in_posts/up.sql b/web/converse/migrations/2018-04-14-140818_posts_only_in_posts/up.sql
new file mode 100644
index 000000000000..07ff9a1196f6
--- /dev/null
+++ b/web/converse/migrations/2018-04-14-140818_posts_only_in_posts/up.sql
@@ -0,0 +1,6 @@
+-- Instead of storing the thread OP in the thread table, this will
+-- make it a post as well.
+-- At the time at which this migration was created no important data
+-- existed in any converse instances, so data is not moved.
+
+ALTER TABLE threads DROP COLUMN body;
diff --git a/web/converse/migrations/2018-04-14-153202_add_stickies_improve_index/down.sql b/web/converse/migrations/2018-04-14-153202_add_stickies_improve_index/down.sql
new file mode 100644
index 000000000000..a67ada3d4a57
--- /dev/null
+++ b/web/converse/migrations/2018-04-14-153202_add_stickies_improve_index/down.sql
@@ -0,0 +1,2 @@
+DROP VIEW thread_index;
+ALTER TABLE threads DROP COLUMN sticky;
diff --git a/web/converse/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql b/web/converse/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql
new file mode 100644
index 000000000000..74a559e35d00
--- /dev/null
+++ b/web/converse/migrations/2018-04-14-153202_add_stickies_improve_index/up.sql
@@ -0,0 +1,21 @@
+-- Add support for stickies in threads
+ALTER TABLE threads ADD COLUMN sticky BOOLEAN NOT NULL DEFAULT FALSE;
+
+-- CREATE a simple view that returns the list of threads ordered by
+-- the last post that occured in the thread.
+CREATE VIEW thread_index AS
+  SELECT t.id AS thread_id,
+         t.title AS title,
+         t.author_name AS thread_author,
+         t.posted AS created,
+         t.sticky AS sticky,
+         p.id AS post_id,
+         p.author_name AS post_author,
+         p.posted AS posted
+    FROM threads t
+    JOIN (SELECT DISTINCT ON (thread_id)
+           id, thread_id, author_name, posted
+          FROM posts
+          ORDER BY thread_id, id DESC) AS p
+    ON t.id = p.thread_id
+    ORDER BY t.sticky DESC, p.id DESC;
diff --git a/web/converse/migrations/2018-04-14-170750_search-index/down.sql b/web/converse/migrations/2018-04-14-170750_search-index/down.sql
new file mode 100644
index 000000000000..c57e66290201
--- /dev/null
+++ b/web/converse/migrations/2018-04-14-170750_search-index/down.sql
@@ -0,0 +1,2 @@
+DROP INDEX idx_fts_search;
+DROP MATERIALIZED VIEW search_index;
diff --git a/web/converse/migrations/2018-04-14-170750_search-index/up.sql b/web/converse/migrations/2018-04-14-170750_search-index/up.sql
new file mode 100644
index 000000000000..6b7d90eca63c
--- /dev/null
+++ b/web/converse/migrations/2018-04-14-170750_search-index/up.sql
@@ -0,0 +1,21 @@
+-- Prepare a materialised view containing the tsvector data for all
+-- threads and posts. This view is indexed using a GIN-index to enable
+-- performant full-text searches.
+--
+-- For now the query language is hardcoded to be English.
+
+CREATE MATERIALIZED VIEW search_index AS
+  SELECT p.id AS post_id,
+         p.author_name AS author,
+         t.id AS thread_id,
+         t.title AS title,
+         p.body AS body,
+         setweight(to_tsvector('english', t.title), 'B') ||
+         setweight(to_tsvector('english', p.body), 'A') ||
+         setweight(to_tsvector('simple', t.author_name), 'C') ||
+         setweight(to_tsvector('simple', p.author_name), 'C') AS document
+    FROM posts p
+    JOIN threads t
+    ON t.id = p.thread_id;
+
+CREATE INDEX idx_fts_search ON search_index USING gin(document);
diff --git a/web/converse/migrations/2018-05-01-141548_add-users/down.sql b/web/converse/migrations/2018-05-01-141548_add-users/down.sql
new file mode 100644
index 000000000000..61fd222e18cf
--- /dev/null
+++ b/web/converse/migrations/2018-05-01-141548_add-users/down.sql
@@ -0,0 +1,63 @@
+-- First restore the old columns:
+ALTER TABLE threads ADD COLUMN author_name VARCHAR;
+ALTER TABLE threads ADD COLUMN author_email VARCHAR;
+ALTER TABLE posts ADD COLUMN author_name VARCHAR;
+ALTER TABLE posts ADD COLUMN author_email VARCHAR;
+
+-- Then select the data back into them:
+UPDATE threads SET author_name = users.name,
+                   author_email = users.email
+  FROM users
+  WHERE threads.user_id = users.id;
+
+UPDATE posts SET author_name = users.name,
+                 author_email = users.email
+  FROM users
+  WHERE posts.user_id = users.id;
+
+-- add the constraints back:
+ALTER TABLE threads ALTER COLUMN author_name SET NOT NULL;
+ALTER TABLE threads ALTER COLUMN author_email SET NOT NULL;
+ALTER TABLE posts ALTER COLUMN author_name SET NOT NULL;
+ALTER TABLE posts ALTER COLUMN author_email SET NOT NULL;
+
+-- reset the index view:
+CREATE OR REPLACE VIEW thread_index AS
+  SELECT t.id AS thread_id,
+         t.title AS title,
+         t.author_name AS thread_author,
+         t.posted AS created,
+         t.sticky AS sticky,
+         p.id AS post_id,
+         p.author_name AS post_author,
+         p.posted AS posted
+    FROM threads t
+    JOIN (SELECT DISTINCT ON (thread_id)
+           id, thread_id, author_name, posted
+          FROM posts
+          ORDER BY thread_id, id DESC) AS p
+    ON t.id = p.thread_id
+    ORDER BY t.sticky DESC, p.id DESC;
+
+-- reset the search view:
+DROP MATERIALIZED VIEW search_index;
+CREATE MATERIALIZED VIEW search_index AS
+  SELECT p.id AS post_id,
+         p.author_name AS author,
+         t.id AS thread_id,
+         t.title AS title,
+         p.body AS body,
+         setweight(to_tsvector('english', t.title), 'B') ||
+         setweight(to_tsvector('english', p.body), 'A') ||
+         setweight(to_tsvector('simple', t.author_name), 'C') ||
+         setweight(to_tsvector('simple', p.author_name), 'C') AS document
+    FROM posts p
+    JOIN threads t
+    ON t.id = p.thread_id;
+
+CREATE INDEX idx_fts_search ON search_index USING gin(document);
+
+-- and drop the users table and columns:
+ALTER TABLE posts DROP COLUMN user_id;
+ALTER TABLE threads DROP COLUMN user_id;
+DROP TABLE users;
diff --git a/web/converse/migrations/2018-05-01-141548_add-users/up.sql b/web/converse/migrations/2018-05-01-141548_add-users/up.sql
new file mode 100644
index 000000000000..fcb7133e8e41
--- /dev/null
+++ b/web/converse/migrations/2018-05-01-141548_add-users/up.sql
@@ -0,0 +1,83 @@
+-- This query creates a users table and migrates the existing user
+-- information (from the posts table) into it.
+
+CREATE TABLE users (
+  id SERIAL PRIMARY KEY,
+  email VARCHAR NOT NULL UNIQUE,
+  name VARCHAR NOT NULL,
+  admin BOOLEAN NOT NULL DEFAULT false
+);
+
+-- Insert the 'anonymous' user explicitly:
+INSERT INTO users (name, email)
+  VALUES ('Anonymous', 'anonymous@nothing.org');
+
+INSERT INTO users (id, email, name)
+  SELECT nextval('users_id_seq'),
+         author_email AS email,
+         author_name AS name
+  FROM posts
+  WHERE author_email != 'anonymous@nothing.org'
+  GROUP BY name, email;
+
+-- Create the 'user_id' column in the relevant tables (initially
+-- without a not-null constraint) and populate it with the data
+-- selected above:
+ALTER TABLE posts ADD COLUMN user_id INTEGER REFERENCES users (id);
+UPDATE posts SET user_id = users.id
+  FROM users
+  WHERE users.email = posts.author_email;
+
+ALTER TABLE threads ADD COLUMN user_id INTEGER REFERENCES users (id);
+UPDATE threads SET user_id = users.id
+  FROM users
+  WHERE users.email = threads.author_email;
+
+-- Add the constraints:
+ALTER TABLE posts ALTER COLUMN user_id SET NOT NULL;
+ALTER TABLE threads ALTER COLUMN user_id SET NOT NULL;
+
+-- Update the index view:
+CREATE OR REPLACE VIEW thread_index AS
+  SELECT t.id AS thread_id,
+         t.title AS title,
+         ta.name AS thread_author,
+         t.posted AS created,
+         t.sticky AS sticky,
+         p.id AS post_id,
+         pa.name AS post_author,
+         p.posted AS posted
+    FROM threads t
+    JOIN (SELECT DISTINCT ON (thread_id)
+           id, thread_id, user_id, posted
+          FROM posts
+          ORDER BY thread_id, id DESC) AS p
+    ON t.id = p.thread_id
+    JOIN users ta ON ta.id = t.user_id
+    JOIN users pa ON pa.id = p.user_id
+    ORDER BY t.sticky DESC, p.id DESC;
+
+-- Update the search view:
+DROP MATERIALIZED VIEW search_index;
+CREATE MATERIALIZED VIEW search_index AS
+  SELECT p.id AS post_id,
+         pa.name AS author,
+         t.id AS thread_id,
+         t.title AS title,
+         p.body AS body,
+         setweight(to_tsvector('english', t.title), 'B') ||
+         setweight(to_tsvector('english', p.body), 'A') ||
+         setweight(to_tsvector('simple', ta.name), 'C') ||
+         setweight(to_tsvector('simple', pa.name), 'C') AS document
+    FROM posts p
+    JOIN threads t ON t.id = p.thread_id
+    JOIN users ta ON ta.id = t.user_id
+    JOIN users pa ON pa.id = p.user_id;
+
+CREATE INDEX idx_fts_search ON search_index USING gin(document);
+
+-- And drop the old fields:
+ALTER TABLE posts DROP COLUMN author_name;
+ALTER TABLE posts DROP COLUMN author_email;
+ALTER TABLE threads DROP COLUMN author_name;
+ALTER TABLE threads DROP COLUMN author_email;
diff --git a/web/converse/migrations/2018-05-01-183232_simplified-post-view/down.sql b/web/converse/migrations/2018-05-01-183232_simplified-post-view/down.sql
new file mode 100644
index 000000000000..0f14732f3845
--- /dev/null
+++ b/web/converse/migrations/2018-05-01-183232_simplified-post-view/down.sql
@@ -0,0 +1 @@
+DROP VIEW simple_posts;
diff --git a/web/converse/migrations/2018-05-01-183232_simplified-post-view/up.sql b/web/converse/migrations/2018-05-01-183232_simplified-post-view/up.sql
new file mode 100644
index 000000000000..280fef87003e
--- /dev/null
+++ b/web/converse/migrations/2018-05-01-183232_simplified-post-view/up.sql
@@ -0,0 +1,11 @@
+-- Creates a view for listing posts akin to the post table before
+-- splitting out users. This exists to avoid having to do joining
+-- logic and such inside of the application.
+
+CREATE VIEW simple_posts AS
+  SELECT p.id AS id,
+         thread_id, body, posted, user_id,
+         users.name AS author_name,
+         users.email AS author_email
+  FROM posts p
+  JOIN users ON users.id = p.user_id;
diff --git a/web/converse/migrations/2018-05-25-160648_add_closed_column/down.sql b/web/converse/migrations/2018-05-25-160648_add_closed_column/down.sql
new file mode 100644
index 000000000000..fb2a98c0af42
--- /dev/null
+++ b/web/converse/migrations/2018-05-25-160648_add_closed_column/down.sql
@@ -0,0 +1 @@
+ALTER TABLE threads DROP COLUMN closed;
diff --git a/web/converse/migrations/2018-05-25-160648_add_closed_column/up.sql b/web/converse/migrations/2018-05-25-160648_add_closed_column/up.sql
new file mode 100644
index 000000000000..d7d4c44da201
--- /dev/null
+++ b/web/converse/migrations/2018-05-25-160648_add_closed_column/up.sql
@@ -0,0 +1 @@
+ALTER TABLE threads ADD COLUMN closed BOOLEAN NOT NULL DEFAULT false;
diff --git a/web/converse/migrations/2018-05-25-161939_add_closed_to_index/down.sql b/web/converse/migrations/2018-05-25-161939_add_closed_to_index/down.sql
new file mode 100644
index 000000000000..1063fdc88224
--- /dev/null
+++ b/web/converse/migrations/2018-05-25-161939_add_closed_to_index/down.sql
@@ -0,0 +1,30 @@
+-- Update the index view:
+DROP VIEW thread_index;
+CREATE VIEW thread_index AS
+  SELECT t.id AS thread_id,
+         t.title AS title,
+         ta.name AS thread_author,
+         t.posted AS created,
+         t.sticky AS sticky,
+         p.id AS post_id,
+         pa.name AS post_author,
+         p.posted AS posted
+    FROM threads t
+    JOIN (SELECT DISTINCT ON (thread_id)
+           id, thread_id, user_id, posted
+          FROM posts
+          ORDER BY thread_id, id DESC) AS p
+    ON t.id = p.thread_id
+    JOIN users ta ON ta.id = t.user_id
+    JOIN users pa ON pa.id = p.user_id
+    ORDER BY t.sticky DESC, p.id DESC;
+
+-- Update the post view:
+DROP VIEW simple_posts;
+CREATE VIEW simple_posts AS
+  SELECT p.id AS id,
+         thread_id, body, posted, user_id,
+         users.name AS author_name,
+         users.email AS author_email
+  FROM posts p
+  JOIN users ON users.id = p.user_id;
diff --git a/web/converse/migrations/2018-05-25-161939_add_closed_to_index/up.sql b/web/converse/migrations/2018-05-25-161939_add_closed_to_index/up.sql
new file mode 100644
index 000000000000..87580a2f3f9e
--- /dev/null
+++ b/web/converse/migrations/2018-05-25-161939_add_closed_to_index/up.sql
@@ -0,0 +1,35 @@
+-- Update the index view:
+DROP VIEW thread_index;
+CREATE VIEW thread_index AS
+  SELECT t.id AS thread_id,
+         t.title AS title,
+         ta.name AS thread_author,
+         t.posted AS created,
+         t.sticky AS sticky,
+         t.closed AS closed,
+         p.id AS post_id,
+         pa.name AS post_author,
+         p.posted AS posted
+    FROM threads t
+    JOIN (SELECT DISTINCT ON (thread_id)
+           id, thread_id, user_id, posted
+          FROM posts
+          ORDER BY thread_id, id DESC) AS p
+    ON t.id = p.thread_id
+    JOIN users ta ON ta.id = t.user_id
+    JOIN users pa ON pa.id = p.user_id
+    ORDER BY t.sticky DESC, p.id DESC;
+
+-- Update post view:
+DROP VIEW simple_posts;
+CREATE VIEW simple_posts AS
+  SELECT p.id AS id,
+         thread_id, body,
+         p.posted AS posted,
+         p.user_id AS user_id,
+         threads.closed AS closed,
+         users.name AS author_name,
+         users.email AS author_email
+  FROM posts p
+  JOIN users ON users.id = p.user_id
+  JOIN threads ON threads.id = p.thread_id;