From 001f07780c7dca212fb19edaab30f6068eb2c97b Mon Sep 17 00:00:00 2001
From: kartofen <mladenovnasko0@gmail.com>
Date: Mon, 24 Oct 2022 19:08:52 +0300
Subject: done lol

---
 src/database/postgres.ts | 113 +++++++++++++++++++++++++++++++++++++++++++----
 1 file changed, 104 insertions(+), 9 deletions(-)

(limited to 'src/database')

diff --git a/src/database/postgres.ts b/src/database/postgres.ts
index 2ea19bd..677a106 100644
--- a/src/database/postgres.ts
+++ b/src/database/postgres.ts
@@ -1,10 +1,16 @@
-import { Client } from 'pg';
+import type { Thread, Comment }  from '../models/Thread';
+import { Client, types } from 'pg';
+
 var connectionString = "postgres://scale:secret@db/scale?sslmode=disable";
 let client: Client;
 
 export function InitPostgres() {
     client = new Client(connectionString);
     client.connect();
+
+    types.setTypeParser(types.builtins.INT8, (val) => {
+        return Number(val)
+    });
 }
 
 export function ClosePostgres() {
@@ -31,9 +37,9 @@ where nspname != 'pg_toast' and nspname != 'pg_catalog'
 and nspname != 'public' and nspname != 'information_schema';`; // to remove the default schemas
 
     client.query(query, (err, res) => {
-        let r: { status: number, data: any } = { status: 500, data: undefined };
+        let r: { status: number, data: any } = { status: 404, data: undefined };
         if (err) {
-            r.status = 404; r.data = err.message;
+            r.data = err.message;
         } else {
             r.status = 200; r.data = [];
             for(let board of res.rows)
@@ -46,20 +52,24 @@ and nspname != 'public' and nspname != 'information_schema';`; // to remove the
 export function CreateBoard(name: string, callback: (r: { status: number, data: any }) => void) {
     let query = `
 create schema ${name};
-create table ${name}.threads(
+create table ${name}.thread(
   id serial primary key,
   timestamp bigint,
-  title text,
-  content text,
+  title text NOT NULL,
+  content text NOT NULL,
   image text,
   imagetype text
 );
-create table ${name}.comments(
+create table ${name}.comment(
   id serial primary key,
+  tid int,
   timestamp bigint,
-  content text,
+  content text NOT NULL,
   image text,
-  imagetype text
+  imagetype text,
+  constraints fk_tid
+    foreign key(tid)
+      references ${name}.thread(id)
 );`;
 
     client.query(query, (err, res) => {
@@ -72,3 +82,88 @@ create table ${name}.comments(
         callback(r);
     });
 }
+
+export function GetBoard(board: string, callback: (r: { status: number, data: any }) => void) {
+    let query = `
+select * from ${board}.thread
+order by timestamp desc;`;
+
+    client.query(query, (err, res) => {
+        let r: { status: number, data: any } = { status: 404, data: undefined };
+        if (err) {
+            r.data = err.message;
+        } else {
+            r.status = 200; r.data = res.rows;
+        }
+        callback(r);
+    });
+}
+
+
+export function CreateThread(board: string, t: Thread, callback: (r: { status: number, data: any }) => void) {
+    let query = `
+insert into ${board}.thread (
+  timestamp, title, content, image, imagetype
+) values (
+  ${Math.floor(Date.now() / 1000)},
+  '${t.title}', '${t.content}', '${t.image}', '${t.imagetype}'
+);`;
+
+    client.query(query, (err, res) => {
+        let r: { status: number, data: any } = { status: 404, data: undefined };
+        if (err) {
+            r.data = err.message;
+        } else {
+            r.status = 200;
+        }
+        callback(r);
+    });
+}
+
+export function GetThread(board: string, tid: string, callback: (r: { status: number, data: any }) => void) {
+    // copied from https://www.youtube.com/watch?v=_lLqt5exlMs&t=114s
+    let query = `
+with thread_comments as (
+  select DISTINCT c.tid, (
+    select jsonb_agg(_)
+    from (select id, timestamp, content, image, imagetype
+      from ${board}.comment cc where cc.tid = c.tid)
+    as _ order by c.timestamp asc
+    ) as comment
+  from ${board}.comment c
+)
+select t.*, COALESCE(c.comment, '[]'::jsonb) as comment
+from ${board}.thread t
+left join thread_comments c on (c.tid = t.id)
+where t.id = ${tid};`;
+
+    client.query(query, (err, res) => {
+        let r: { status: number, data: any } = { status: 404, data: undefined };
+        if (err) {
+            r.data = err.message;
+        } else if(res.rows.length != 0) {
+            r.status = 200; r.data = (res.rows as Array<Thread>)[0];
+        }
+        callback(r);
+    });
+}
+
+export function CreateComment(board: string, tid: string, comment: Comment, callback: (r: { status: number, data: any }) => void) {
+    let query = `
+insert into ${board}.comment (
+  tid, timestamp, content, image, imagetype
+) values (
+  ${tid}, ${Math.floor(Date.now() / 1000)},
+  '${comment.content}', '${comment.image}', '${comment.imagetype}'
+);`;
+
+    client.query(query, (err, res) => {
+        let r: { status: number, data: any } = { status: 404, data: undefined };
+        if (err) {
+            r.data = err.message;
+        } else {
+            r.status = 200;
+        }
+        callback(r);
+    });
+}
-- 
cgit v1.2.3