147 lines
5.1 KiB
JavaScript
Executable File
147 lines
5.1 KiB
JavaScript
Executable File
var express = require('express');
|
|
var app = express();
|
|
var cors = require('cors');
|
|
var escape = require('escape-html')
|
|
var bodyParser = require('body-parser');
|
|
var sqlite3 = require('sqlite3').verbose();
|
|
var db = new sqlite3.Database('db.sqlite', sqlite3.OPEN_READWRITE | sqlite3.OPEN_CREATE, function (err) {
|
|
if(err) {
|
|
console.log('error opening db:', err);
|
|
} else {
|
|
db.run("CREATE TABLE IF NOT EXISTS images (id INTEGER PRIMARY KEY, src TEXT, dataUri TEXT)");
|
|
db.run("CREATE TABLE IF NOT EXISTS questions (id INTEGER PRIMARY KEY, updated DATETIME DEFAULT CURRENT_TIMESTAMP, text TEXT, explanation TEXT)");
|
|
db.run("ALTER TABLE questions ADD COLUMN explanation TEXT", function (err) {});
|
|
db.run("CREATE TABLE IF NOT EXISTS answers (id INTEGER PRIMARY KEY, aid INTEGER, qid INTEGER, text TEXT, correct INTEGER, checked INTEGER)");
|
|
db.run("CREATE TABLE IF NOT EXISTS answering (id INTEGER PRIMARY KEY, created DATETIME DEFAULT CURRENT_TIMESTAMP, version TEXT, href TEXT)");
|
|
}
|
|
});
|
|
|
|
|
|
app.use( bodyParser.json() );
|
|
app.use(cors());
|
|
app.set('view engine', 'pug');
|
|
|
|
|
|
|
|
|
|
var corsOptions = {
|
|
origin: "*",
|
|
methods: "POST"
|
|
};
|
|
|
|
app.get('/', function (req, res) {
|
|
db.all("SELECT q.id AS qid, q.text AS question, q.explanation AS explanation, i.dataUri AS image, T2.id AS aid, T2.text AS answer, T2.correct FROM questions q " +
|
|
"LEFT JOIN (SELECT qid, MAX(aid) AS maxid FROM answers GROUP BY qid) AS T1 ON q.id = T1.qid " +
|
|
"LEFT JOIN answers AS T2 ON T2.aid = T1.maxid AND T2.qid = q.id " +
|
|
"LEFT JOIN images AS i ON i.id = q.id " +
|
|
"ORDER BY T2.id DESC",
|
|
function (err, rows) {
|
|
//console.log('selected rows:', rows.length);
|
|
if (err) {
|
|
console.log('error:', err);
|
|
res.sendStatus(500);
|
|
}
|
|
var prevQid, index = -1;
|
|
var questions = rows.reduce(function(questions, row) {
|
|
var a = { answer: row.answer, correct: row.correct };
|
|
if (prevQid != row.qid) {
|
|
questions[++index] = { id: row.qid, question: row.question, explanation: row.explanation, image: row.image, answers: [a] };
|
|
} else {
|
|
questions[index].answers.push(a);
|
|
}
|
|
prevQid = row.qid;
|
|
return questions;
|
|
}, []);
|
|
console.log('total questions:', questions.length);
|
|
res.render('index', { questions: questions });
|
|
});
|
|
});
|
|
|
|
app.post('/explanation/:id', function (req, res) {
|
|
var id = req.params.id;
|
|
var data = req.body;
|
|
if (id && data.explanation) {
|
|
console.log('updating', id, 'with', data);
|
|
db.run('UPDATE questions SET explanation = $explanation WHERE id = $id', {
|
|
$id: id,
|
|
$explanation: escape(data.explanation)
|
|
}, function (err) {
|
|
if (err) {
|
|
console.log('error updating explanation for', id, ':', err);
|
|
res.sendStatus(500);
|
|
} else {
|
|
res.sendStatus(200);
|
|
}
|
|
});
|
|
} else {
|
|
console.log('invalid explanation for', id, ':', data);
|
|
res.sendStatus(400);
|
|
}
|
|
});
|
|
|
|
app.post('/qa', cors(corsOptions), function (req, res) {
|
|
var data = req.body;
|
|
console.log('NEW QA DATA:', JSON.stringify(data));
|
|
if (!data.version || !data.data || data.data.length < 15) {
|
|
console.log('invalid qa data', !data.version, !data.data, data.data.length < 15);
|
|
return res.sendStatus(400);
|
|
}
|
|
db.serialize(function() {
|
|
var aid = null;
|
|
db.run("INSERT INTO answering (version, href) VALUES ($version,$href)", {
|
|
$version: data.version,
|
|
$href: data.href
|
|
}, function(err) {
|
|
if (err) {
|
|
console.log('inserting answering failed:', err);
|
|
return;
|
|
}
|
|
console.log('inserted:', this.changes, 'lastID:', this.lastID);
|
|
aid = this.lastID;
|
|
if (aid) {
|
|
var qstmt = db.prepare("REPLACE INTO questions (id, text) VALUES (?,?)");
|
|
var astmt = db.prepare("INSERT INTO answers (aid, qid, text, correct, checked) VALUES (?,?,?,?,?)");
|
|
for (var i = 0; i < data.data.length; i++) {
|
|
var q = data.data[i];
|
|
qstmt.run(q.id, q.question);
|
|
for (var j = 0; j < q.answers.length; j++) {
|
|
var a = q.answers[j];
|
|
astmt.run(aid, q.id, a.text, a.correct, a.checked);
|
|
}
|
|
}
|
|
qstmt.finalize();
|
|
astmt.finalize();
|
|
|
|
res.sendStatus(200);
|
|
} else {
|
|
console.log('no answering id');
|
|
res.sendStatus(400);
|
|
}
|
|
});
|
|
});
|
|
});
|
|
|
|
app.post('/img', cors(corsOptions), function (req, res) {
|
|
var data = req.body;
|
|
if (!data.version || !data.data.id || !data.data.src || !data.data.dataUri) {
|
|
console.log('invalid img data', !data.version, !data.data.id, !data.data.src, !data.data.dataUri);
|
|
return res.sendStatus(400);
|
|
}
|
|
var img = data.data;
|
|
console.log('NEW IMG DATA:', JSON.stringify(data).substring(0,200));
|
|
|
|
var istmt = db.run("REPLACE INTO images (id, src, dataUri) VALUES (?,?,?)", img.id, img.src, img.dataUri, function (err) {
|
|
if (err) {
|
|
console.log('saving image ' + img.id + 'failed:', err);
|
|
res.sendStatus(400);
|
|
} else {
|
|
res.sendStatus(200);
|
|
}
|
|
});
|
|
|
|
});
|
|
|
|
app.listen(3000, function () {
|
|
console.log('Example app listening on port 3000!')
|
|
});
|