From b9509422dd5f4c97d5c0746229037a53712ac7fe Mon Sep 17 00:00:00 2001 From: Silas Bartha Date: Tue, 11 Feb 2025 21:54:00 -0500 Subject: switched to sqlite database --- .gitignore | 3 ++ api/.env | 2 - api/api.py | 93 +++++++++++++++++++++++++++++------------- api/forum.db | Bin 0 -> 20480 bytes src/App.jsx | 2 - src/components/chatbubble.jsx | 2 +- src/components/notes.jsx | 2 +- src/components/player.jsx | 2 +- 8 files changed, 71 insertions(+), 35 deletions(-) delete mode 100644 api/.env create mode 100644 api/forum.db 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 Binary files /dev/null and b/api/forum.db differ 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 ; + return ; })} ); } 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())); -- cgit v1.2.3