💾 Archived View for iich.space › src › db › migrations › 1633550337.ts captured on 2022-03-01 at 16:04:04.

View Raw

More Information

⬅️ Previous capture (2021-12-03)

-=-=-=-=-=-=-

import { Migration } from '../migrate';

export const migration: Migration = {
  down: (db) => {
    db.exec(`
DROP VIEW v_posts;

CREATE VIEW v_posts AS
SELECT posts.*,
       ('/' || boards.name || '/threads/' || threads.id) AS path,
       (COALESCE(identities.name, '') || '!' || identities.hash) AS author
FROM posts
INNER JOIN posts AS threads ON posts.threadId = threads.id
AND threads.deleted = 0
INNER JOIN boards ON posts.boardId = boards.id
AND boards.deleted = 0
LEFT JOIN identities ON posts.fingerprint = identities.fingerprint;
`);
  },
  up: (db) => {
    db.exec(`
DROP VIEW v_posts;

CREATE VIEW v_posts AS
SELECT posts.*,
       ('/' || boards.name || '/threads/' || threads.id) AS path,
       (COALESCE(identities.name, '') || '!' || identities.hash) AS author
FROM posts
INNER JOIN posts AS threads ON posts.threadId = threads.id
AND threads.deleted = 0
INNER JOIN boards ON posts.boardId = boards.id
AND boards.deleted = 0
LEFT JOIN identities ON posts.fingerprint = identities.fingerprint
WHERE posts.deleted = 0;
`);
  },
  version: 1633550337,
};