switched to sqlite database

8 files changed, 71 insertions, 35 deletions

diff --git a/.gitignore b/.gitignore
index ced9945..943c103 100644
--- a/.gitignore
+++ b/.gitignore
@@ -26,3 +26,6 @@ dist-ssr
 # Flask stuff
 venv
 __pycache__
+
+# Sqlite
+*.db
diff --git a/api/.env b/api/.env
deleted file mode 100644
index 3c25e5e..0000000
--- a/api/.env
+++ /dev/null
@@ -1,2 +0,0 @@
-DB_URI=mongodb://127.0.0.1
-DB_NAME=forum
diff --git a/api/api.py b/api/api.py
index f26e96f..d694933 100644
--- a/api/api.py
+++ b/api/api.py
@@ -1,52 +1,84 @@
 from http import HTTPStatus
 from flask import Flask, Response, config, json, jsonify, request, request_started
-from dotenv import dotenv_values
-from pymongo import MongoClient
-from bson.json_util import dumps
-from bson import ObjectId
 from uuid import uuid4
+import sqlite3
+import numbers
+from flask import g
 
-config = dotenv_values(".env")
+DATABASE = './forum.db'
+
+def get_db():
+    db = getattr(g, '__database', None)
+    if db is None:
+        db = g.__database = sqlite3.connect(DATABASE)
+    return db
 
 app = Flask(__name__)
 
-mongoclient = MongoClient(config["DB_URI"])
-db = mongoclient[config["DB_NAME"]]
-print("Connected to MongoDB database")
+@app.teardown_appcontext
+def close_connection(_):
+    db = getattr(g, '__database', None)
+    if db is not None:
+        db.close()
+
+
+print("Connected to SQLite database")
 
 @app.route('/api/message')
 def get_messages():
-    messages = dumps(list(db["message"].find(limit=100)))
+    cur = get_db().cursor()
+    res = cur.execute("SELECT message_id, position, message FROM message")
+    messages = jsonify(list(map(lambda m: {'message_id': m[0], 'position': json.loads(m[1]), 'message': m[2]}, res.fetchall())))
     return messages
 
 @app.route('/api/new_message', methods=['POST'])
 def new_message():
+    db = get_db()
+    cur = db.cursor()
     position = list(json.loads(request.form['position']))
-    message = request.form['message']
+    if len(position) != 3:
+        return Response(status=HTTPStatus.BAD_REQUEST)
+    for elem in position:
+        if not isinstance(elem, numbers.Number):
+            return Response(status=HTTPStatus.BAD_REQUEST)
+    position = json.dumps(position)
+    message = str(request.form['message'])
     token = request.form['token']
-    user = db['user'].find_one({'token': token})
-    if user is not None:
-        db["message"].insert_one({'position': position, 'message': message, 'userId': user['_id']})
+    res = cur.execute(f"SELECT user_id FROM user WHERE token='{token}'")
+    (user_id,) = res.fetchone()
+    if user_id is not None:
+        id = str(uuid4())
+        cur.execute("INSERT INTO message VALUES(?, ?, ?, ?)", (id, user_id, position, message));
+        db.commit()
         return Response(status=HTTPStatus.NO_CONTENT)
     else:
         return Response(status=HTTPStatus.BAD_REQUEST)
 
 @app.route('/api/gen_token')
 def gen_token():
+    db = get_db()
+    cur = db.cursor()
+    id = str(uuid4())
     token = str(uuid4())
-    db["user"].insert_one({'token': token})
+    cur.execute("INSERT INTO user VALUES(?, ?)", (id, token));
+    db.commit()
     return { 'token': token }
 
 @app.route('/api/remove_message', methods=['DELETE'])
 def remove_message():
+    db = get_db()
+    cur = db.cursor()
     token = request.form['token']
-    message_id = ObjectId(request.form['message_id'])
-    user = db['user'].find_one({'token':token})
-    if user is not None:
-        message = db['message'].find_one({'_id': message_id})
-        if message is not None:
-            if message['userId'] == user['_id']:
-                db['message'].delete_one({'_id':message_id})
+    message_id = request.form['message_id']
+    res = cur.execute(f"SELECT user_id FROM user WHERE token='{token}'")
+    (user_id,) = res.fetchone()
+    if user_id is not None:
+        res = cur.execute(f"SELECT message_id, user_id FROM message WHERE message_id='{message_id}'")
+        (message_id, message_user_id) = res.fetchone()
+        if message_id is not None:
+            if message_user_id == user_id:
+                cur.execute(f"DELETE FROM message WHERE message_id='{message_id}'")
+                db.commit()
                 return Response(status=HTTPStatus.NO_CONTENT)
             else:
                 return Response(status=HTTPStatus.UNAUTHORIZED)
@@ -57,15 +89,20 @@ def remove_message():
 
 @app.route('/api/edit_message', methods=['PUT'])
 def edit_message():
+    db = get_db()
+    cur = db.cursor()
     token = request.form['token']
-    message_id = ObjectId(request.form['message_id'])
-    user = db['user'].find_one({'token':token})
+    message_id = request.form['message_id']
     new_message = request.form['message']
-    if user is not None and new_message is not None:
-        message = db['message'].find_one({'_id': message_id})
-        if message is not None:
-            if message['userId'] == user['_id']:
-                db['message'].update_one({'_id':message_id}, {'$set': { 'message': new_message }})
+    res = cur.execute(f"SELECT user_id FROM user WHERE token='{token}'")
+    (user_id,) = res.fetchone()
+    if user_id is not None and new_message is not None:
+        res = cur.execute(f"SELECT message_id, user_id FROM message WHERE message_id='{message_id}'")
+        (message_id, message_user_id) = res.fetchone()
+        if message_id is not None:
+            if message_user_id == user_id:
+                cur.execute(f"UPDATE message SET message = '{new_message}' WHERE message_id='{message_id}'");
+                db.commit()
                 return Response(status=HTTPStatus.NO_CONTENT)
             else:
                 return Response(status=HTTPStatus.UNAUTHORIZED)
diff --git a/api/forum.db b/api/forum.db
new file mode 100644
index 0000000..5f8761e
--- /dev/null
+++ b/api/forum.db
diff --git a/src/App.jsx b/src/App.jsx
index fb72f6e..81cd893 100644
--- a/src/App.jsx
+++ b/src/App.jsx
@@ -49,11 +49,9 @@ function App() {
                 apiToken.current = data.token;
             });
         } else {
-            console.log("setting token");
             apiToken.current = token;
         }
         fetch('/api/message').then((res) => res.json()).then((data) => {
-            console.log(data);
             setMessages(data);
         });
     },[]);
diff --git a/src/components/chatbubble.jsx b/src/components/chatbubble.jsx
index 01dc6e5..a9c0c85 100644
--- a/src/components/chatbubble.jsx
+++ b/src/components/chatbubble.jsx
@@ -53,7 +53,7 @@ export default function ChatBubble({ id, position, text }) {
             }
         }
         if(keysPressed.current.includes('KeyR') && activatable) {
-            const newMesssage = prompt();
+            const newMesssage = prompt("edit message");
             if(newMesssage) {
                 let data = new FormData();
                 data.append("token", apiToken.current);
diff --git a/src/components/notes.jsx b/src/components/notes.jsx
index 5f57b7f..d1bc454 100644
--- a/src/components/notes.jsx
+++ b/src/components/notes.jsx
@@ -6,7 +6,7 @@ export default function Notes() {
     const { messages } = useContext(AppContext);
     return (<>
         {messages.map((chatbubble) =>  {
-            return <ChatBubble key={chatbubble._id.$oid} id={chatbubble._id.$oid} position={chatbubble.position} text={chatbubble.message}/>;
+            return <ChatBubble key={chatbubble.message_id} id={chatbubble.message_id} position={chatbubble.position} text={chatbubble.message}/>;
         })}
     </>);
 }
diff --git a/src/components/player.jsx b/src/components/player.jsx
index f0c1e3e..b6a1ca3 100644
--- a/src/components/player.jsx
+++ b/src/components/player.jsx
@@ -29,7 +29,7 @@ export default function Player() {
         raycaster.current.setFromCamera(pointer, camera.current);
         const intersect = raycaster.current.intersectObjects(scene.children).at(0);
         if (intersect && intersect.object.name === 'ground' && intersect.distance < 10 && controlsRef.current.isLocked) {
-            const message = prompt();
+            const message = prompt("new message");
             if (message) {
                 let data = new FormData();
                 data.append("position", JSON.stringify(intersect.point.toArray()));