Closed elimelt closed 1 year ago
Here's how I am THINKING we will set up the schema for the DB, although formatting is turning out to be a huge pain because we are inserting Java code snippet strings, many of which contain quotes.
CREATE DATABASE syntext;
USE syntext;
CREATE TABLE snippets ( id INT PRIMARY KEY, type VARCHAR(25) NOT NULL, length VARCHAR(25) NOT NULL, data LONGTEXT NOT NULL );
Right now, it looks like a big challenge that the backend will face is formatting the code snippets both pre insertion and after retrieval. There are a few solutions I can think of (ie. escaping with back-slash and removing them after querying, or even just replacing them outright with a rare character before inserting and then fixing it server-side), but there is no way I am going to finish this by Tuesday.
The JSON_ARRAY()
function in MySQL seems pretty handy for storing an array of Strings, but this isn't really a "good practice" according to people on Stack Overflow. Apparently, it is better to create a separate table and store each element of the Array as a tuple, but this would mean making a new table for every single snippet. Maybe that is feasible once we are able to programmatically insert snippets into our DB, but until then JSON_ARRAY()
might be worth using.
Resolved! The entire app is now containerized, including the database used for local development. For details on getting MySQL setup with docker, take a look at syntext/server/documentation/db-docker-setup.md
There is a little bit of work to do here in terms of testing on everyone's machines, but I will make a seperate issue for making sure everyone gets docker up and running. Note that we probably won't be deploying our database with docker, but it is nice that you don't have to install MySQL to do front-end work
create a local MySQL DB for developing backend. schema should match the MVP spec.