Implement topic/RP-tagging system

[danneu]

Schema changes:

CREATE TABLE tag_groups (
  id serial PRIMARY KEY,
  title text NOT NULL,
  -- Constraints
  UNIQUE(title)
);

CREATE TABLE tags (
  id serial PRIMARY KEY,
  tag_group_id int NOT NULL REFERENCES tag_groups(id) ON DELETE CASCADE,
  title text NOT NULL,
  description text NULL,
  created_at timestamp with time zone NOT NULL DEFAULT NOW(),
  -- Constraints
  UNIQUE(title)
);

CREATE INDEX ON tags (tag_group_id);

CREATE TABLE tags_topics (
  topic_id int NOT NULL REFERENCES topics(id) ON DELETE CASCADE,
  tag_id   int NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  -- Constraints
  UNIQUE(topic_id, tag_id)
);

-- FK lookups
CREATE INDEX ON tags_topics (topic_id);
CREATE INDEX ON tags_topics (tag_id);

----

ALTER TABLE forums ADD COLUMN tag_id int NULL REFERENCES tags(id) ON DELETE SET NULL;
ALTER TABLE forums ADD COLUMN has_tags_enabled boolean NOT NULL DEFAULT false;

TODO: has_tags_enabled would be better implemented as enabled_tag_groups::int[] but I don't feel like doing that

Seed it:

INSERT INTO tag_groups (id, title)
VALUES
(1, 'Writing Style'), 
(2, 'Party Size'), 
(3, 'Genre');

INSERT INTO tags (tag_group_id, id, title, description)
VALUES 
-- Writing style
(1, 1, 'Free', 'One-liners, few-liners, or speed-posting; shallow depth'),
(1, 2, 'Casual', 'A few paragraphs; medium depth.'),
(1, 3, 'Advanced', 'Many paragraphs; full depth, lore, development'),
-- Party size
(2, 4, '1x1', 'Two players'),
(2, 5, 'Small Group', '3-4 players'),
(2, 6, 'Large Group', '5+ players'),
-- Genres
(3, 7, 'Modern', 'Based in our present world or what''s trending in it'),
(3, 8, 'Sci-Fi', 'Exploring innovations and possibly their impact on the future'),
(3, 9, 'High/Epic Fantasy', 'Fantastical elements fated to save the world from an ultimate evil'),
(3, 10, 'Slice of Life', 'Some focus on elements on real life'),
(3, 11, 'School', 'Involves a school, academy, high-school, university, Hogwarts, ...'),
(3, 12, 'Historical', 'Events of the past or an alternate one'),
(3, 13, 'Tabletop', 'Narrative driven by game mechanics, character stats, games like DnD'),
(3, 14, 'Nation', 'Players control nations, tribes, factions, ...'),
(3, 15, 'Arena', 'PvP, players fighting/competiting against each other'),
(3, 16, 'Military', 'Involving military activity or war'),
(3, 17, 'Romance', 'Emphasis on love or its rejections and failures')
;

-- Give forums a tag_id for legacy compatibility

UPDATE forums SET has_tags_enabled = true, tag_id = 1 WHERE id IN (3, 12); -- Free: 3, 12
UPDATE forums SET has_tags_enabled = true, tag_id = 2 WHERE id IN (4, 13); -- Casual: 4, 13
UPDATE forums SET has_tags_enabled = true, tag_id = 3 WHERE id IN (5, 14); -- Advanced: 5, 14
UPDATE forums SET has_tags_enabled = true, tag_id = 15 WHERE id IN (6, 15); -- Arena: 6, 15
UPDATE forums SET has_tags_enabled = true, tag_id = 13 WHERE id IN (39, 40); -- Tabletop: 39, 40
UPDATE forums SET has_tags_enabled = true, tag_id = 14 WHERE id IN (42, 43); -- Nation: 42, 43
UPDATE forums SET has_tags_enabled = true, tag_id = 4 WHERE id IN (7, 16); -- 1x1: 7, 16

Now give all RPs/Checks a tag for the forum they're in.

process.env.DEBUG = '*';
// 3rd party
var co = require('co');
var _ = require('lodash');
var debug = require('debug')('app:sandbox');
var coParallel = require('co-parallel');
var m = require('multiline');
var RegexTrie = require('regex-trie');
var request = require('co-request');
// 1st party
var db = require('./db');
var belt = require('./belt');
var config = require('./config');
var ether = require('./ether');

function* run() {
  // Returns only the forums and topics that have not yet been processed
  var sql = m(function() {/*
SELECT
  f.id            forum_id,
  f.tag_id        tag_id,
  array_agg(t.id) topic_ids
FROM topics t
JOIN forums f ON t.forum_id = f.id
WHERE 
  f.tag_id IS NOT NULL 
  AND NOT EXISTS (SELECT 1 FROM tags_topics WHERE topic_id = t.id)
GROUP BY f.id
  */});

  // Array of {forum_id: Int, topic_ids: [Int], tag_id: Int }
  var rows = (yield db.query(sql)).rows;

  var thunks = [];
  sql = m(function() {/*
INSERT INTO tags_topics (topic_id, tag_id)
VALUES ($1, $2)
  */});
  rows.forEach(function(row) {
    // row.forum_id :: Int, row.topic_ids :: [Int], row.tag_id :: Int
    row.topic_ids.forEach(function(topicId) {
      thunks.push(db.query(sql, [topicId, row.tag_id]));
    });
  });

  var concurrency = 5;
  debug('Running %s thunks, %s at a time', thunks.length, concurrency);
  yield coParallel(thunks, concurrency);
}

co(run).then(
  function() { console.log('OK'); },
  function(ex) { console.log(ex, ex.stack); }
);

[danneu]

Implemented