1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
# Installation instructions
Follow these steps to install everything required for the project to run.
## Install the FoodJS repository
Just clone it from github. If you want the server to be able to update itself automatically without modifying `init.sh`, you will have to do this in your home folder.
```
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
```
## 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.
## 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;
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:
```
shifts.txt
Classes
| classes.txt
```
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`.
## 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. To make this work, move `~/FoodJS/init.sh` to `~/init.sh`:
```
mv ~/FoodJS/init.sh ~/init.sh
```
This script should run at reboot, so next up you will have to set up some cronjobs:
To your crontab:
```
@reboot ~/init.sh
```
To the crontab of **root** (you may have to update the shutdown path):
```
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)
|
