stage 8

portsoc · Mar 8, 2024 · 5a5c90e · 5a5c90e
1 parent 424c780
commit 5a5c90e
Show file tree

Hide file tree

Showing 4 changed files with 74 additions and 40 deletions.
diff --git a/stages/client/message.js b/stages/client/message.js
@@ -47,7 +47,6 @@ async function sendMessage() {
     el.message.value = '';
     const updatedMessages = await response.json();
     showMessage(updatedMessages, el.messagelist);
-    history.go(-1);
   } else {
     console.log('failed to send message', response);
   }

diff --git a/stages/messageboard.js b/stages/messageboard.js
@@ -1,42 +1,54 @@
+import { open } from 'sqlite';
+import sqlite3 from 'sqlite3';
 import uuid from 'uuid-random';
 
-let messages = [
-  { id: 'xnshfdsafasd', msg: 'these are three default messages', time: 'an hour ago' },
-  { id: 'dskjdshkjhsd', msg: 'delivered from the server', time: 'yesterday' },
-  { id: 'vcxbxcvfggzv', msg: 'using a custom route', time: 'last week' },
-];
+async function init() {
+  const db = await open({
+    filename: './database.sqlite',
+    driver: sqlite3.Database,
+    verbose: true
+  });
+  await db.migrate({ migrationsPath: './migrations-sqlite' });
+  return db;
+}
+
+const dbConn = init();
 
-export function listMessages() {
-  return messages;
+export async function listMessages() {
+  const db = await dbConn;
+  return db.all('SELECT * FROM Messages ORDER BY time DESC LIMIT 10');
 }
 
-export function findMessage(id) {
-  for (const message of messages) {
-    if (message.id === id) {
-      return message;
-    }
-  }
-  return null;
+export async function findMessage(id) {
+  const db = await dbConn;
+  return db.get('SELECT * FROM Messages WHERE id = ?', id);
 }
 
-export function addMessage(msg) {
-  if (msg.trim() === '') return messages;
-  const newMessage = {
-    id: uuid(),
-    time: Date(),
-    msg,
-  };
-  messages = [newMessage, ...messages.slice(0, 9)];
-  return messages;
+function currentTime() {
+  return new Date().toISOString();
 }
 
-export function editMessage(updatedMessage) {
-  const storedMessage = findMessage(updatedMessage.id);
-  if (storedMessage == null) throw new Error('message not found');
+export async function addMessage(msg) {
+  if (msg.trim() === '') return listMessages();
+  const db = await dbConn;
+  const id = uuid();
+  const time = currentTime();
+  await db.run('INSERT INTO Messages VALUES (?, ?, ?)', [id, msg, time]);
+
+  return listMessages();
+}
+
+export async function editMessage(updatedMessage) {
+  const db = await dbConn;
+
+  const id = updatedMessage.id;
+  const time = currentTime();
+  const msg = updatedMessage.msg;
+
+  const statement = await db.run('UPDATE Messages SET msg = ? , time = ? WHERE id = ?', [msg, time, id]);
 
-  // update old message in place
-  storedMessage.time = Date();
-  storedMessage.msg = updatedMessage.msg;
+  // if nothing was updated, the ID doesn't exist
+  if (statement.changes === 0) throw new Error('message not found');
 
-  return storedMessage;
+  return findMessage(id);
 }
diff --git a/stages/migrations-sqlite/001-initial.sql b/stages/migrations-sqlite/001-initial.sql
@@ -0,0 +1,23 @@
+-- Up
+
+CREATE TABLE Messages (
+  id   CHAR(36) PRIMARY KEY,
+  msg  TEXT     NOT NULL,
+  time DATETIME
+);
+
+INSERT INTO Messages (id, msg, time) VALUES
+( 'xnshfdsafasd',
+  'these are three default messages',
+  datetime('now')),
+( 'dskjdshkjhsd',
+  'delivered from the server',
+  datetime('now')),
+( 'vcxbxcvfggzv',
+  'using a custom route',
+  datetime('now'));
+
+
+-- Down
+
+DROP TABLE Messages;
diff --git a/stages/svr.js b/stages/svr.js
@@ -1,33 +1,33 @@
 import * as mb from './messageboard.js';
 
 // message board app
-// stage 7: add css
+// stage 8: Data belongs in a database
 import express from 'express';
 
 const app = express();
 
 app.use(express.static('client', { extensions: ['html'] }));
 
-function getMessages(req, res) {
-  res.json(mb.listMessages());
+async function getMessages(req, res) {
+  res.json(await mb.listMessages());
 }
 
-function getMessage(req, res) {
-  const result = mb.findMessage(req.params.id);
+async function getMessage(req, res) {
+  const result = await mb.findMessage(req.params.id);
   if (result) {
     res.json(result);
   } else {
     res.status(404).send('No match for that ID.');
   }
 }
 
-function postMessage(req, res) {
-  const messages = mb.addMessage(req.body.msg);
+async function postMessage(req, res) {
+  const messages = await mb.addMessage(req.body.msg);
   res.json(messages);
 }
 
-function putMessage(req, res) {
-  const message = mb.editMessage(req.body);
+async function putMessage(req, res) {
+  const message = await mb.editMessage(req.body);
   res.json(message);
 }