diff options
| -rw-r--r-- | README.md | 263 | ||||
| -rw-r--r-- | food.js | 1 | ||||
| -rw-r--r-- | server.js | 9 | ||||
| -rwxr-xr-x | setup.sh | 14 | ||||
| -rw-r--r-- | update.js | 10 |
5 files changed, 149 insertions, 148 deletions
@@ -1,132 +1,140 @@ -# Installation instructions -Follow these steps to install everything required for the project to run. - -## Install the FoodJS repository -Just clone it from github. -``` -git clone "https://github.com/JoelHMikael/FoodJS.git" -``` - -## Install node.js -(16.x, the one in Ubuntus package repositories is outdated) -``` -sudo apt install curl -curl -fsSL https://deb.nodesource.com/setup_16.x | sudo -E bash - -sudo apt-get install -y nodejs -``` -Install npm packages required for project -``` -npm install mysql2 -``` +# Installing -## Install MySQL server & configure it -``` -sudo apt install mysql-server -sudo mysql_secure_installation -``` -> Note: If you want to update the databases remotely, you can allow logging in from the local network. In this case you of course have to use a strong password. +Follow these steps to install everything required for the project to run. -## Initializing the database -If you don't have a backup of the database and need to initialize it, log into mysql and run the following to initialize the tables: -``` -CREATE DATABASE lyllruoka; -USE lyllruoka; +There is a script `setup.sh` on this repository. It will walk you through installing everything. It's not foolproof, so read carefully its prompts and give sensible answers. + +The script will declare the installation to have succeeded in the end. If you don't see a message confirming the script's success, something has likely gone wrong. + +After running the script there should be a new directory in the path you specified, named LYLLRuoka. This will be referenced to as the "BASE DIRECTORY" from now on. + +> `setup.sh` has only been tested on Ubuntu 20.04 and requires `apt-get` to be available. + +## Details about the prompts in setup.sh + +Below is more information about the information `setup.sh` will ask you. Take a look here if you are unsure what you should type in. + +* Install directory: this is the directory where LYLLRuoka should be installed. Supply an absolute path, ie. a path starting with `/`. You should have permissions to read, write and execute in the directory you give. The script will create the "BASE DIRECTORY" in the path you supply, and that directory will contain a folder with the server code and some things needed to run the server. If you are unsure, $HOME (supply as `/home/[username]` is a good choice. +* MySQL username: `setup.sh` will install and configure a MySQL server. On the go it will create a user the server will use to access the `lyllruoka`-database created on the MySQL server. Here you should supply the desired username. Anything goes, really, as long as it is a valid MySQL username. Don't include any quotations or the hostname: these are added automatically. +* TLS/SSL certificate: If you choose to let the script generate a self-signed certificate, it will ask a few questions detailed below. Remember to change the self-signed certificate to a certificate signed by a certificate authority. The certificate's private and public key should be placed in `["BASE DIRECTORY"]/Certificate/key.pem` and `["BASE DIRECTORY"]/cert.pem` respectively. + * Common name: insert here the domain name where the server will server content, eg. `food.example.com`. Don't include the protocol (eg. `http://`) or any path (eg. `example.com/lorem`), + * Email address: should be self explanatory. + + +# Required data + +The server actually needs to know the food shifts somehow before it can actually serve them. After running the installation script mentioned above there should be a subdirectory called `Updation` in the "BASE DIRECTORY". It should contain three files: `shifts.txt`, `vanhalops.csv` and `uusilops.csv`. These files are empty, and you will have to populate them. This is data that should be updated in the beginning of every period. + +## shifts.txt + +1. Open Wilma: <https://lohja.inschool.fi> +2. Go to the message section. +3. Search the newest message with a header like "Periodin [some number] työpäivän keskimmäisen oppitunnin ruokailuvuorot ja oppitunti [date] alkaen" +4. Copy all the message contents starting from "MAANANTAISIN". +5. Paste it into `shifts.txt` +6. Profit. + +## uusilops.csv and vanhalops.csv + +If there are students studying two separate curriculums, one newer and one older, you will have to do this for both files. If not, using either one will suffice. +1. Get the Kurssitarjotin for the appropriate curriculum from junu or somewhere else as a spreadsheet file +2. Open the file +3. Search the period we are in +4. Copy and paste all the data about that period into uusilops.csv or vanhalops.csv. You know which one. +5. The output should look something like this: +``` +t1 t2 t3 t4 t5 t6 t7 t8 +UE11 KE11 BI11 GE11 RUB121 MAA31 ENA121 SAB321 +TEKE SAKO LAMI PAHO MAOI JUMA VIHU +B101R A101R B101R B103R A204R B202R A203R TH +SAB221 BI12 KE31 UE12 RUB122 MAA32 FY33 RAB322 +SABE PAHO MATI TEKE ANSU SAKO SALE +A202R B103R A101R B105R B109R B203R B101R TH +RUB123 MAA33 LI31 MU13 KU13 RAB321 HI52 VEB321 +ANSU RIHO JUHO MAMY REOJ MASI JAJU +B109R B202R B201R A201R B210R B105R TH +HI42 MAB22 GE14 KU14 ÄI44 LI17 RUB124 +JUSA JOTO PAHO REOJ VETU JUHO KAHU +B106R B104R B103R A201R B107R B209R +UE15 MAA34 TE15 BI15 RUB125 LI15 KU71 +SATU MATI SATU HEIH KAHU ANSA REOJ +B108R B203R B108R B110R B103R A201R +FI81 PS22 FI16 KE16 UE16 RUB126 LI18 +ALMA SATU ALMA MATI ALMA MAOI ANSA +B105R B108R B105R A101R B105R A204R +FY31 MAA35 KU17 RUB127 ÄI47 ENA37 +ESRI SALE REOJ ANSU HAPA SABE +B102R B101R A201R B109R B108R B106R +MU81 ENA122 MU18 EAB331 RUA21 MAB21 KE18 +MAMY VIHU MAMY VIHU SASA SAHE SAHE +B201R A203R B201R A203R A202R B101R A101R + FY111 LI31 FY32 GE18 MAB24 + ESRI ANSA OLNU HEIH SAKO + B102R B104R A102R B203R + AT31 BI18 + RIHO HEIH + A201R A102R + + + +P E R I O D I IVB +t1 t2 t3 t4 t5 t6 t7 t8 +UE11 KE21 BI11 GE11 RUB121 MAA41 ENA121 SAB321 +TEKE SAKO LAMI PAHO MAOI JUMA VIHU +B101R A101R B101R B103R A204R B202R A203R TH +SAB221 BI12 KE31 UE12 RUB122 MAA42 FY33 RAB322 +SABE PAHO MATI TEKE ANSU SAKO SALE +A202R B103R A101R B105R B109R B203R B101R TH +RUB123 MAA43 LI31 MU13 KU13 RAB321 HI52 VEB321 +ANSU RIHO JUHO MAMY REOJ MASI JAJU +B109R B202R B201R A201R B210R B105R TH +HI42 MAB22 GE14 KU14 ÄI44 LI17 RUB124 +JUSA JOTO PAHO REOJ VETU JUHO KAHU +B106R B104R B103R A201R B107R B209R +UE15 MAA44 TE15 BI15 RUB125 LI15 KU71 +SATU MATI SATU HEIH KAHU ANSA REOJ +B108R B203R B108R B110R B103R A201R +FI81 PS22 FI16 KE26 UE16 RUB126 LI18 +ALMA SATU ALMA MATI ALMA MAOI ANSA +B105R B108R B105R A101R B105R A204R +FY31 MAA45 KU17 RUB127 ÄI47 ENA37 +ESRI SALE REOJ ANSU HAPA SABE +B102R B101R A201R B109R B108R B106R +MU81 ENA122 MU18 EAB331 RUA21 MAB21 KE28 +MAMY VIHU MAMY VIHU SASA SAHE SAHE +B201R A203R B201R A203R A202R B101R A101R + FY111 LI32 FY32 GE18 MAB24 + ESRI ANSA OLNU HEIH SAKO + B102R B104R A102R B203R + KU151 BI18 + REOJ HEIH + A201R A102R +``` +The server isn't very picky about what area you have copy-pasted here. The only requirement is that there should be no data from other periods visible. Otherwise the server will use that data too. + +# Running + +Now that you have given all required data to the server, you can run it. In the "BASE DIRECTORY" there should be a directory named `LYLLRuoka` (yes, there are two nested directories with the same name). Enter that directory. See the file `server.js`? Good. Now, in this directory, run as root `node server.js`. The output should look like this: +``` +Shifts and classes updated. +Foods updated. +Servers Up And Running! +``` +Great! Now you have the server running. If you restart the server or close the terminal window or anything else, you will obviously have to restart the server. + +You can make the server run automatically on reboot. This hasn't been tested, though... -CREATE TABLE shiftnames ( - day INT, - id INT, - name VARCHAR(128) NOT NULL, - PRIMARY KEY (day, id) -); -CREATE TABLE classes ( - course VARCHAR(6) PRIMARY KEY, - class VARCHAR(4) -); -CREATE TABLE shifts ( - day INT, - shift INT, - course VARCHAR(6), - teacher VARCHAR(4), - class VARCHAR(4), - PRIMARY KEY (day, course) -); -CREATE TABLE devs ( - id INT PRIMARY KEY AUTO_INCREMENT, - name VARCHAR(30) NOT NULL, - description VARCHAR(128), - contact VARCHAR(40) DEFAULT '' -); -CREATE TABLE stats ( - start DATE PRIMARY KEY, - uptime INT, - requests INT, - requests_per_day INT -); -CREATE TABLE exams ( - start DATE, - end DATE, - message VARCHAR(256), - PRIMARY KEY (start, end) -); -CREATE TABLE foods ( - week INT, - day INT, - vegetarian TINYINT, - header VARCHAR(15), - dateString VARCHAR(13), - food VARCHAR(256) -); -``` -> Note that if you had some information in a former database that you don't update manually, it will be lost. - -## Give the server the credentials, keys & other required things -* MySQL credentials in `../dblogin.txt` - * You probably should [create a user](https://dev.mysql.com/doc/refman/8.0/en/create-user.html) and [grant privileges to it](https://dev.mysql.com/doc/refman/8.0/en/grant.html) for this. For me logging in as root didn't work out of the box, except of course combined with `sudo`, which does neither work out of the box with node.js. - * The credentials should be in json format. For instance: - ``` - { - "host": "localhost", - "user": "exampleuser", - "password": "password123", - "database": "lyllruoka" - } - ``` -* SSL certificate in `../Certificate/key.pem` and `../Certificate/cert.pem` - * As on the [website of node.js](https://nodejs.org/en/knowledge/HTTP/servers/how-to-create-a-HTTPS-server/), you can create a self-signed certificate (for testing purposes) as following: - ``` - openssl genrsa -out key.pem - openssl req -new -key key.pem -out csr.pem - openssl x509 -req -days 9999 -in csr.pem -signkey key.pem -out cert.pem - rm csr.pem - ``` - ---- - -# Updating the tables - -## Shifts and classes -This is an example on how to update the shifts and classes to the database, so that the server can serve them to the clients. - -Lets assume the following filesystem that contains also all of the server code: +Add to **roots** crontab (run `sudo crontab -e`): ``` -shifts.txt -Classes -| classes.txt +@reboot cd ["BASE DIRECTORY"]/LYLLRuoka && node server.js +# For example: +# @reboot cd /home/foobar/LYLLRuoka/LYLLRuoka && node server.js ``` -Where shifts.txt contains the shifts and `classes.txt` contains the classes. -You can get the shifts from junu's food shift message through Wilma. The `classes` should be a tab delimited text file. You can get it easily by copy-pasting its contents from eg. LibreOffice from "Kurssitarjottimet". Provide only the classes of one period, not all of them. You can give several class files, if needed. - -Then just run the following code in node.js: -``` -const updateDB = require("./update.js"); -const openFile = require("./Functions/open.js").file; -const dbcredentials = await openFile("../dblogin.txt"); -await updateDB.update(dbcredentials, "./shifts.txt", "./Classes/classes.txt"); -``` -If you have several files with classes, just append them to the parameters of `updateDB.update`. +# Adding cool data that isn't required for the server to run ## Notifying of unusual food shifts (eg. during exams) + Currently the notifications have to be added manually to the MySQL database. Here's an example: ``` USE lyllruoka; @@ -135,21 +143,10 @@ INSERT INTO exams VALUES ('2021-11-22', '2021-11-30', '<h2>Koeviikko</h2><br>22. The first value in the parenthesis is the start date of the notification, the second the end date of the notification and the third value is the message to display. HTML is supported. The message will override the food shift search. ## Updating the developer table + Updating the developer table is pretty straightforward. You just need to provide the name of the developer, a description (eg. "Improved the performance of the server") and contact information: ``` INSERT INTO devs (name, description, contact) VALUES ('[name]', '[description]', '[contact]'); ``` > Insert the values in the quotation marks, don't change the text before the `VALUES` keyword. -# Automatic server code updates -You can make the server update itself from github. The code that updates everything is in the FoodJS folder (top-level of repository) in the file `init.sh`. It should run at reboot and it should have the environment variable PATH_TO_LYLLRUOKA set pointing to the FoodJS folder **without** a trailing backslash. - -Add to **roots** crontab (run `sudo crontab -e`): -``` -@reboot PATH_TO_LYLLRUOKA=[the path] [the path]/init.sh -# For example: -# @reboot PATH_TO_LYLLRUOKA='/home/foobar/FoodJS' /home/foobar/FoodJS/init.sh - -0 0 * * 7 /sbin/shutdown -r -``` -If you need to troubleshoot the initialization, you can find both normal and error logs in /tmp/slogs (text file) @@ -48,7 +48,6 @@ async function buildFoods(DB) } } await Promise.all(foodInitOperations); - console.log("Foods built"); } function getFoodLink(week) @@ -8,6 +8,7 @@ const DBPARSE = require("./dbparse.js"); const open = require("./Functions/open.js"); const strFuncs = require("./Functions/stringFuncs.js"); const dateFuncs = require("./Functions/dateFuncs.js"); +const updateDB = require("./update.js"); async function init() @@ -44,7 +45,11 @@ async function init() // get the MySQL DB connection const SQLDB = new SQL_DBS.Database(JSON.parse(dbcredentials)); - // Add the foods to the database + // Update... + // ...shifts and classes + await updateDB.update(SQLDB, "../Updation/shifts.txt", "../Updation/vanhalops.csv", "../Updation/uusilops.csv"); + console.log("Shifts and classes updated."); + // ...foods await food.build(SQLDB); setInterval( () => @@ -53,6 +58,7 @@ async function init() }, 7 * 24 * 60 * 60 * 1000 ); + console.log("Foods updated.") // server code async function server(req, res) @@ -100,6 +106,7 @@ async function init() // start servers const httpsServer = https.createServer(httpsOpts, server).listen(443); const httpServer = http.createServer(server).listen(80); + console.log("Servers Up And Running!") // stop server async function closeServers() { @@ -13,18 +13,16 @@ if [ "$(whoami)" == root ]; then fi fi -echo -e "This interactive script will install most of the things needed to run LYLLRuoka, except for:\n\t* The actual data to serve on the database\n\t* A SSL/TLS certificate\n\t* uhh\nWhen finished, \e[31mTHE INSTALL WILL TELL THAT IT SUCCEEDED\e[0m. If you don't see a message confirming the success of the installation, the installation \e[31mHAS FAILED. Remember to check that.\e[0m\n" +echo -e "This interactive script will install most of the things needed to run LYLLRuoka, except for:\n\t* The actual data to serve on the database\n\t* A SSL/TLS certificate\nWhen finished, \e[31mTHE INSTALL WILL TELL THAT IT SUCCEEDED\e[0m. If you don't see a message confirming the success of the installation, the installation \e[31mHAS FAILED. Remember to check that.\e[0m\n" set -e echo 'The script will now ask a few questions that are going to be needed during the installation.' -echo -e '\nInstall directory for LYLLRuoka? (absolute path in which you have all permissions)' +echo -e '\nInstall directory for LYLLRuoka? (absolute path which you have all permissions for)' read path mkdir -p "$path/LYLLRuoka" cd "$path/LYLLRuoka" -echo -e '\nChoose the mysql user hostname. (type localhost, if unsure)' -read host -echo 'Choose the mysql username.' +echo "Choose the mysql username (don't supply the host)." read name echo 'Choose the mysql password.' read -s passw @@ -65,12 +63,12 @@ echo 'Installing MySQL...' sudo apt-get -y install mysql-server > /dev/null echo -e 'Done!\n' -echo "Now that MySQL has installed you can run the program 'mysql_secure_installation'. This is recommended, but there has been (read: I have had) problems with it sometimes. Try running it now as root. If it just doesn't seem to work, you can do without it, if you just make sure your server has a firewall that blocks requests from other computers to MySQL and have only trusted persons have access to the server computer. Press enter when you are ready..." +echo "Now that MySQL has installed you can run the program 'mysql_secure_installation'. This is recommended, but there has been (read: I have had) problems with it sometimes, as it hasn't been able to do something and hasn't allowed stopping it with ^Z, ^C or ^D. Try running it now as root in another terminal. If it just doesn't seem to work, you can do without it, if you just make sure your server has a firewall that blocks requests from other computers to MySQL and have only trusted persons have access to the server computer. Press enter when you are ready..." read echo 'Setting up MySQL database...' -echo -e "CREATE DATABASE lyllruoka;\nUSE lyllruoka;\n\nCREATE TABLE shiftnames (\n\tday INT,\n\tid INT,\n\tname VARCHAR(128) NOT NULL,\n\tPRIMARY KEY (day, id)\n);\nCREATE TABLE classes (\n\tcourse VARCHAR(6) PRIMARY KEY,\n\tclass VARCHAR(4)\n);\nCREATE TABLE shifts (\n\tday INT,\n\tshift INT,\n\tcourse VARCHAR(6),\n\tteacher VARCHAR(4),\n\tclass VARCHAR(4),\n\tPRIMARY KEY (day, course)\n);\nCREATE TABLE devs (\n\tid INT PRIMARY KEY AUTO_INCREMENT,\n\tname VARCHAR(30) NOT NULL,\n\tdescription VARCHAR(128),\n\tcontact VARCHAR(40) DEFAULT ''\n);\nCREATE TABLE stats (\n start DATE PRIMARY KEY,\n uptime INT,\n requests INT,\n requests_per_day INT\n);\nCREATE TABLE exams (\n\tstart DATE,\n\tend DATE,\n\tmessage VARCHAR(256),\n\tPRIMARY KEY (start, end)\n);\nCREATE TABLE foods ( \n week INT,\n day INT,\n vegetarian TINYINT,\n header VARCHAR(15),\n dateString VARCHAR(13),\n food VARCHAR(256)\n);\nCREATE USER '$name'@'$host' IDENTIFIED BY '$passw';\nGRANT ALL ON lyllruoka.* TO '$name'@'$host';\n" | sudo mysql > /dev/null -echo -e "{\n\t\"host\": \"$host\",\n\t\"user\": \"$name\",\n\t\"password\": \"$passw\",\n\t\"database\": \"lyllruoka\"\n}" > dblogin.txt +echo -e "CREATE DATABASE lyllruoka;\nUSE lyllruoka;\n\nCREATE TABLE shiftnames (\n\tday INT,\n\tid INT,\n\tname VARCHAR(128) NOT NULL,\n\tPRIMARY KEY (day, id)\n);\nCREATE TABLE classes (\n\tcourse VARCHAR(6) PRIMARY KEY,\n\tclass VARCHAR(4)\n);\nCREATE TABLE shifts (\n\tday INT,\n\tshift INT,\n\tcourse VARCHAR(6),\n\tteacher VARCHAR(4),\n\tclass VARCHAR(4),\n\tPRIMARY KEY (day, course)\n);\nCREATE TABLE devs (\n\tid INT PRIMARY KEY AUTO_INCREMENT,\n\tname VARCHAR(30) NOT NULL,\n\tdescription VARCHAR(128),\n\tcontact VARCHAR(40) DEFAULT ''\n);\nCREATE TABLE stats (\n start DATE PRIMARY KEY,\n uptime INT,\n requests INT,\n requests_per_day INT\n);\nCREATE TABLE exams (\n\tstart DATE,\n\tend DATE,\n\tmessage VARCHAR(256),\n\tPRIMARY KEY (start, end)\n);\nCREATE TABLE foods ( \n week INT,\n day INT,\n vegetarian TINYINT,\n header VARCHAR(15),\n dateString VARCHAR(13),\n food VARCHAR(256)\n);\nCREATE USER '$name'@'localhost' IDENTIFIED BY '$passw';\nGRANT ALL ON lyllruoka.* TO '$name'@'localhost';\n" | sudo mysql > /dev/null +echo -e "{\n\t\"host\": \"localhost\",\n\t\"user\": \"$name\",\n\t\"password\": \"$passw\",\n\t\"database\": \"lyllruoka\"\n}" > dblogin.txt echo -e 'Done!\n' echo -e '\n==============================\nInstall finished successfully!\n==============================' @@ -1,17 +1,15 @@ -const database = require("./database.js"); const openFile = require("./Functions/open.js").file; const parseClasses = require("./parseClasses.js").classes; const parse = require("./dbparse.js"); // Run this if you want to build the database from text files -async function buildDB(dbcredentials, shiftPath, ...classfiles) +async function buildDB(dbconnection, shiftPath, ...classfiles) { - const DB = new database.Database(JSON.parse(dbcredentials)); let shiftCont = await openFile(shiftPath); shiftCont = shiftCont.toString("utf-8").replaceAll("\r", ""); // \r because of the \r\n newline on windows which may create problems - await parseClasses(DB, ...classfiles), - await parse.build(shiftCont, DB) + await parseClasses(dbconnection, ...classfiles), + await parse.build(shiftCont, dbconnection) return 0; } @@ -19,6 +17,8 @@ exports.update = buildDB; // Example call: /* const openFile = require("./Functions/open.js").file; +const database = require("./database.js"); const dbcredentials = await openFile("../dblogin.txt"); +const DB = new database.Database(JSON.parse(dbcredentials)); await updateDB.update(dbcredentials, "./shifts.txt", "./Kurssitarjottimet/2016Classes.txt", "./Kurssitarjottimet/NewClasses.txt"); */ |