Let mods move topics

[danneu]

This post is outdated. See next comment

Schema changes:

• Add topics.moved_from_forum_id int NULL REFERENCES forums(id)
• Add topics.moved_at timestamptz NULL
• Add topics.latest_post_at timestamptz NULL

TODO: Add casacade TODO: Turn into SQL commands

Run this to update (overwrite) db function:

CREATE OR REPLACE FUNCTION update_latest_post_id() RETURNS trigger AS
$$
  var q = 'UPDATE forums           '+
          'SET latest_post_id = $1 '+
          'WHERE id = (            '+
          '  SELECT forum_id       '+
          '  FROM topics           '+
          '  WHERE topics.id = $2  '+
          ')                       ';

  plv8.execute(q, [NEW.id, NEW.topic_id]);

  q = 'UPDATE topics                                                '+
      'SET latest_post_id      = $2,                                '+
      '    latest_ic_post_id   = COALESCE($3, latest_ic_post_id),   '+
      '    latest_ooc_post_id  = COALESCE($4, latest_ooc_post_id),  '+
      '    latest_char_post_id = COALESCE($5, latest_char_post_id), '+
      '    latest_post_at      = NOW()                              '+
      'WHERE id = $1                                               ';

  // If NonRP, just set the latest_post_id
  if (!NEW.is_roleplay) {
    plv8.execute(q, [NEW.topic_id, NEW.id, null, null, null]);
    return;
  }

  // Since it is a roleplay, update the appropriate cache
  switch(NEW.type) {
    case 'ic':
      plv8.execute(q, [NEW.topic_id, NEW.id, NEW.id, null,   null]);
      return;
    case 'ooc':
      plv8.execute(q, [NEW.topic_id, NEW.id, null,   NEW.id, null]);
      return;
    case 'char':
      plv8.execute(q, [NEW.topic_id, NEW.id, null,   null,   NEW.id]);
      return;
  }
$$ LANGUAGE 'plv8';

DROP TRIGGER IF EXISTS post_created5 ON posts;
CREATE TRIGGER post_created5
    AFTER INSERT ON posts  -- Only on insert
    FOR EACH ROW
    EXECUTE PROCEDURE update_latest_post_id();

[danneu]

ALTER TABLE topics ADD COLUMN moved_from_forum_id int NULL REFERENCES forums(id);
ALTER TABLE topics ADD COLUMN moved_at timestamp with time zone NULL;
ALTER TABLE topics ADD COLUMN latest_post_at timestamp with time zone NULL;

After creating topics.latest_post_at, upsert function+trigger that sets it:

CREATE OR REPLACE FUNCTION update_latest_post_id() RETURNS trigger AS
$$
  var q = 'UPDATE forums           '+
          'SET latest_post_id = $1 '+
          'WHERE id = (            '+
          '  SELECT forum_id       '+
          '  FROM topics           '+
          '  WHERE topics.id = $2  '+
          ')                       ';

  plv8.execute(q, [NEW.id, NEW.topic_id]);

  q = 'UPDATE topics                                                '+
      'SET latest_post_id      = $2,                                '+
      '    latest_ic_post_id   = COALESCE($3, latest_ic_post_id),   '+
      '    latest_ooc_post_id  = COALESCE($4, latest_ooc_post_id),  '+
      '    latest_char_post_id = COALESCE($5, latest_char_post_id), '+
      '    latest_post_at      = NOW()                              '+
      'WHERE id = $1                                                ';

  // If NonRP, just set the latest_post_id
  if (!NEW.is_roleplay) {
    plv8.execute(q, [NEW.topic_id, NEW.id, null, null, null]);
    return;
  }

  // Since it is a roleplay, update the appropriate cache
  switch(NEW.type) {
    case 'ic':
      plv8.execute(q, [NEW.topic_id, NEW.id, NEW.id, null,   null]);
      return;
    case 'ooc':
      plv8.execute(q, [NEW.topic_id, NEW.id, null,   NEW.id, null]);
      return;
    case 'char':
      plv8.execute(q, [NEW.topic_id, NEW.id, null,   null,   NEW.id]);
      return;
  }
$$ LANGUAGE 'plv8';

DROP TRIGGER IF EXISTS post_created5 ON posts;
CREATE TRIGGER post_created5
    AFTER INSERT ON posts  -- Only on insert
    FOR EACH ROW
    EXECUTE PROCEDURE update_latest_post_id();

After creating topics.latest_post_at, run the bulk update:

UPDATE topics
SET latest_post_at = sub.latest_post_at
FROM (
    SELECT 
        t.id "topic_id",
        p.created_at "latest_post_at"
    FROM topics t
    JOIN posts p ON t.latest_post_id = p.id
) sub
WHERE topics.id = sub.topic_id

After the bulk update, add index:

CREATE INDEX topics_moved_at_latest_post_at ON topics (
  COALESCE(moved_at, latest_post_at) DESC
);

[danneu]

Implemented