💾 Archived View for iich.space › src › db › admin › queries.ts captured on 2021-12-03 at 14:04:38.

View Raw

More Information

➡️ Next capture (2022-01-08)

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

import { AdminPost, Ban, Board, BoardType, PostStats } from '../models';
import { getBoardByName } from '../queries';
import { sql } from '../sql';

const queryAdminBoards = sql`
SELECT boards.*,
       COALESCE(t.threadCount, 0) AS threadCount,
       COALESCE(r.replyCount, 0) AS replyCount
FROM boards
LEFT JOIN
  (SELECT boardId,
          COUNT(*) AS threadCount
   FROM posts
   WHERE TYPE = 0
   GROUP BY boardId) AS t ON t.boardId = boards.id
LEFT JOIN
  (SELECT boardId,
          COUNT(*) AS replyCount
   FROM posts
   WHERE TYPE = 1
   GROUP BY boardId) AS r ON r.boardId = boards.id
GROUP BY boards.id
ORDER BY deleted ASC,
         name ASC;
`;

export const getAdminBoards = (): Array<Board & PostStats> =>
  queryAdminBoards.all();

const queryAdminPosts = sql<[number]>`
SELECT posts.*,
       identities.name,
       ('/' || boards.name || '/threads/' || posts.threadId) AS PATH,
       (bans.origin IS NOT NULL) AS banned
FROM posts
INNER JOIN boards ON boards.id = posts.boardId
LEFT JOIN identities ON identities.fingerprint = posts.fingerprint
LEFT JOIN bans ON posts.origin = bans.origin
ORDER BY posts.id DESC
LIMIT 10
OFFSET ?
`;

export const getAdminPosts = (page = 0): Array<AdminPost> =>
  queryAdminPosts.all(page * 10);

const insertBoard = sql<[string]>`
INSERT INTO boards (name)
VALUES (?)
`;

export const createBoard = (name: string): Board => {
  insertBoard.run(name).lastInsertRowid as number;

  return getBoardByName(name);
};

const updateBoardName = sql<[string, number]>`
UPDATE boards
SET name = ?
WHERE boards.id = ?
`;

export const setBoardName = (id: number, name: string): void => {
  updateBoardName.run(name, id);
};

const updateBoardDescription = sql<[string, number]>`
UPDATE boards
SET description = ?
WHERE boards.id = ?
`;

export const setBoardDescription = (id: number, description: string): void => {
  updateBoardDescription.run(description, id);
};

const updateBoardType = sql<[number, number]>`
UPDATE boards
SET TYPE = ?
WHERE boards.id = ?
`;

export const setBoardType = (id: number, type: BoardType): void => {
  updateBoardType.run(type, id);
};

const updateBoardDeleted = sql<[number, number]>`
UPDATE boards
SET deleted = ?
WHERE boards.id = ?
`;

export const setBoardDeleted = (id: number, deleted: boolean): void => {
  updateBoardDeleted.run(deleted ? 1 : 0, id);
};

const updatePostDeleted = sql<[number, number]>`
UPDATE posts
SET deleted = ?
WHERE posts.id = ?
`;

export const setPostDeleted = (id: number, deleted: boolean): void => {
  updatePostDeleted.run(deleted ? 1 : 0, id);
};

const updatePostLocked = sql<[number, number]>`
UPDATE posts
SET locked = ?
WHERE posts.id = ?
`;

export const setPostLocked = (id: number, locked: boolean): void => {
  updatePostLocked.run(locked ? 1 : 0, id);
};

const updatePostMuted = sql<[number, number]>`
UPDATE posts
SET muted = ?
WHERE posts.id = ?
`;

export const setPostMuted = (id: number, muted: boolean): void => {
  updatePostMuted.run(muted ? 1 : 0, id);
};

const insertBan = sql<[string]>`
INSERT OR IGNORE INTO bans (origin)
VALUES (?)
`;

export const createBan = (origin: string): void => {
  insertBan.run(origin);
};

const deleteBan = sql<[string]>`
DELETE
FROM bans
WHERE origin = ?
`;

export const removeBan = (origin: string): void => {
  deleteBan.run(origin);
};

const queryBans = sql`
SELECT * FROM bans
`;

export const getBans = (): Array<Ban> => queryBans.all();