How to use the sqlite/legacy.all function in sqlite
To help you get started, we’ve selected a few sqlite examples, based on popular ways it is used in public projects.
Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
cbuntain / collabortweet / server.js View on Github 
ORDER BY l.taskId", taskId);
taskDetails["labelOptions"] = labelOptions;
var labelDetails = db.all("SELECT e.elementId AS eId, e.elementText AS eText, el.elementLabelId AS elId, u.userId AS uId, u.screenname AS screenname, l.labelId AS lId, l.labelText AS lText \
FROM elements e \
JOIN elementLabels el ON e.elementId = el.elementId \
JOIN labels l ON el.labelId = l.labelId \
JOIN users u ON u.userId = el.userId \
WHERE e.taskId = ? \
ORDER BY e.elementId", taskId);
taskDetails["labels"] = labelDetails;
// Get the users who have labeled this task
var userLabelDetails = db.all("SELECT u.userId AS uId, u.fname AS fname, u.lname AS lname, COUNT(*) AS count \
FROM users u \
JOIN elementLabels el ON u.userId=el.userId \
JOIN elements e ON el.elementId=e.elementId \
WHERE e.taskId = ? \
GROUP BY u.userId", taskId);
taskDetails["userDetails"] = userLabelDetails;
} else {
console.log("Unknown task type in taskStats/...");
taskDetails.push({ empty : true });
}
return Promise.props(taskDetails);
})
.then(function(taskInfoMap) {app.get('/item', function(req, res) {
// Pull the task from the session
var requestedTask = req.session.taskId
console.log("New item requested!");
var localUser = req.session.user;
console.log("Local User:");
console.log(localUser);
// Get a set of candidate elements
db.all('SELECT elementId, elementText \
FROM elements e \
WHERE taskId = ? AND \
(SELECT COUNT(*) \
FROM elementLabels el \
WHERE el.elementId = e.elementId \
AND el.userId = ?) == 0 \
LIMIT 10', [requestedTask, localUser.userId])
.then(function(elements) {
if ( elements.length > 0 ) {
var targetElement = getRandomElement(elements);
console.log("Target Element: " + targetElement);
console.log("\t" + targetElement["elementId"]);
console.log("\t" + targetElement["elementText"]);
res.setHeader('Content-Type', 'application/json');app.get('/pair', function(req, res) {
// Pull the task from the session
var requestedTask = req.session.taskId
console.log("New pair requested!");
var localUser = req.session.user;
console.log("Local User:");
console.log(localUser);
// Find a pair we haven't seen before
db.all('SELECT pairId FROM pairs prs \
WHERE \
prs.taskId = ? AND \
( \
SELECT COUNT(*) \
FROM comparisons cps \
WHERE cps.pairId = prs.pairId \
AND cps.userId = ? \
) = 0 \
LIMIT 100', [requestedTask, localUser.userId])
.then(function(pairs) {
if ( pairs.length > 0 ) {
var pairList = pairs.map(function(x) {
return x["pairId"]
});.then(function(taskData) {
var taskDetails = {
taskInfo: taskData
};
if ( taskData.taskType == 1 ) {
var compDetails = db.all("SELECT c.decision, \
e1.elementId AS lId, e1.elementText AS lText, e1.externalId AS lExt, \
e2.elementId AS rId, e2.elementText AS rText, e2.externalId AS rExt \
FROM pairs p \
JOIN elements AS e1 ON e1.elementId = p.leftElement \
JOIN elements AS e2 ON e2.elementId = p.rightElement \
JOIN comparisons c ON p.pairId = c.pairId \
WHERE p.taskId = ?", taskId);
taskDetails["labels"] = compDetails;
// Get the users who have labeled this task
var userLabelDetails = db.all("SELECT u.fname AS fname, u.lname AS lname, COUNT(*) as count \
FROM users u \
JOIN comparisons c ON u.userId=c.userId \
JOIN pairs p ON p.pairId=c.pairId \
WHERE p.taskId = ? \taskDetails["userDetails"] = userLabelDetails;
// Pairwise comparisons don't have label options, so null this
taskDetails["labelOptions"] = null;
} else if ( taskData.taskType == 2 ) {
var labelOptions = db.all("SELECT l.labelId AS lId, l.labelText AS lText \
FROM labels l \
WHERE l.taskId = ? \
ORDER BY l.taskId", taskId);
taskDetails["labelOptions"] = labelOptions;
var labelDetails = db.all("SELECT e.elementId AS eId, e.elementText AS eText, el.elementLabelId AS elId, u.userId AS uId, u.screenname AS screenname, l.labelId AS lId, l.labelText AS lText \
FROM elements e \
JOIN elementLabels el ON e.elementId = el.elementId \
JOIN labels l ON el.labelId = l.labelId \
JOIN users u ON u.userId = el.userId \
WHERE e.taskId = ? \
ORDER BY e.elementId", taskId);
taskDetails["labels"] = labelDetails;
// Get the users who have labeled this task
var userLabelDetails = db.all("SELECT u.userId AS uId, u.fname AS fname, u.lname AS lname, COUNT(*) AS count \
FROM users u \
JOIN elementLabels el ON u.userId=el.userId \
JOIN elements e ON el.elementId=e.elementId \
WHERE e.taskId = ? \
GROUP BY u.userId", taskId);if ( taskData.taskType == 1 ) {
var compDetails = db.all("SELECT c.decision, \
e1.elementId AS lId, e1.elementText AS lText, e1.externalId AS lExt, \
e2.elementId AS rId, e2.elementText AS rText, e2.externalId AS rExt \
FROM pairs p \
JOIN elements AS e1 ON e1.elementId = p.leftElement \
JOIN elements AS e2 ON e2.elementId = p.rightElement \
JOIN comparisons c ON p.pairId = c.pairId \
WHERE p.taskId = ?", taskId);
taskDetails["labels"] = compDetails;
// Get the users who have labeled this task
var userLabelDetails = db.all("SELECT u.fname AS fname, u.lname AS lname, COUNT(*) as count \
FROM users u \
JOIN comparisons c ON u.userId=c.userId \
JOIN pairs p ON p.pairId=c.pairId \
WHERE p.taskId = ? \
GROUP BY u.userId", taskId);
taskDetails["userDetails"] = userLabelDetails;
// Pairwise comparisons don't have label options, so null this
taskDetails["labelOptions"] = null;
} else if ( taskData.taskType == 2 ) {
var labelOptions = db.all("SELECT l.labelId AS lId, l.labelText AS lText \
FROM labels l \
WHERE l.taskId = ? \app.get('/taskView', function(req, res) {
db.all('SELECT taskId, taskName, question, taskType FROM tasks ORDER BY taskId')
.then(function(taskData) {
dataMap = {
tasks: taskData,
authorized: req.session.user ? true : false,
user: req.session.user,
}
res.render('taskView', dataMap)
});
})app.get('/taskStats', function(req, res) {
db.all("SELECT t.taskId, t.taskName, t.question, COUNT(c.compareId) AS counter \
FROM tasks t \
LEFT OUTER JOIN pairs p ON t.taskId = p.taskId \
LEFT OUTER JOIN comparisons c ON p.pairId = c.pairId \
WHERE t.taskType == 1 \
GROUP BY t.taskId \
ORDER BY t.taskId")
.then(function(pairTaskData) {
var labelTaskData = db.all("SELECT t.taskId, t.taskName, t.question, COUNT(DISTINCT(e.elementId)) AS eCount, COUNT(el.elementLabelId) AS labelCount \
FROM tasks t \
LEFT OUTER JOIN elements e ON t.taskId = e.taskId \
LEFT OUTER JOIN elementLabels el ON e.elementId = el.elementId \
WHERE t.taskType == 2 \
GROUP BY t.taskId \
ORDER BY t.taskId");.then(function(pairTaskData) {
var labelTaskData = db.all("SELECT t.taskId, t.taskName, t.question, COUNT(DISTINCT(e.elementId)) AS eCount, COUNT(el.elementLabelId) AS labelCount \
FROM tasks t \
LEFT OUTER JOIN elements e ON t.taskId = e.taskId \
LEFT OUTER JOIN elementLabels el ON e.elementId = el.elementId \
WHERE t.taskType == 2 \
GROUP BY t.taskId \
ORDER BY t.taskId");
return Promise.all([
pairTaskData,
labelTaskData
]);
})
.then(function(taskData) {.then(function(taskData) {
taskMap = {
taskId: requestedTask,
taskName: taskData.taskName,
question: taskData.question,
}
return Promise.all([
taskMap,
db.all('SELECT labelId, labelText FROM labels WHERE taskId = ?', requestedTask)
]);
})
.then(function(labelData) {sqlite
SQLite client for Node.js applications with SQL-based migrations API written in Typescript
