db.serialize(function(){
count += 1
db.run(incCount, count)
})
}).then(() => {
db.close()
})
app.get('/api/v0/posts', function(req, res) {
var id = parseInt(req.params.id, 10);
db.all('SELECT id, title, body FROM Posts', function (err, posts) {
res.json(posts);
});
});
'use strict';
const sqlite3 = require('sqlite3');
const db = new sqlite3.Database('./db/chinook.sqlite');
console.log('# of invoices per country');
db.all(`
SELECT COUNT(*) AS count,
BillingCountry AS country
FROM Invoice
GROUP BY BillingCountry
ORDER BY count DESC`,
(err, res) => {
if (err) throw err;
console.log(res);
}
);
const readline = require('readline')
const fs = require('fs')
const Promise = require('bluebird')
const sqlite3 = require("sqlite3")
const deltaParser = require('../delta_parser')
const inputFile = process.argv[2] || (console.log('Input file needed!'), process.exit(5))
const databaseFile = process.argv[3]
const db = new sqlite3.Database(databaseFile || 'test_db_'+new Date().getTime()+'.sqlite');
Promise.promisifyAll(db)
let initQuery = 'PRAGMA journal_mode=WAL;'
initQuery += 'PRAGMA foreign_keys = ON;'
if (!databaseFile) {
initQuery += 'CREATE TABLE vessels (id INTEGER PRIMARY KEY, vessel TEXT UNIQUE);'
initQuery += 'CREATE TABLE paths (id INTEGER PRIMARY KEY, path TEXT UNIQUE);'
initQuery += 'CREATE TABLE entries (time INTEGER, vessel_id INTEGER NOT NULL, path_id INTEGER NOT NULL, value TEXT, ' +
'FOREIGN KEY(vessel_id) REFERENCES vessels(id), FOREIGN KEY(path_id) REFERENCES paths(id));'
initQuery += 'CREATE INDEX vessel_path_time ON entries (vessel_id, path_id, time);'
}
initQuery += "BEGIN;"
db.execAsync(initQuery).then(() => {
console.log('Opening', inputFile)
return new Promise(resolve => {
const rl = readline.createInterface({
input: fs.createReadStream(inputFile)
})
function insert_to_db(user_info, callback) {
// compute the blob and an input string for each field
var strings = 'INSERT INTO people (';
var values_str = ') VALUES (';
var values = []
var blob = '';
var first = true;
var field_count = 0;
fields.forEach(function(f) {
var field = f[0];
if (!(field in user_info)) {
return;
}
var value = user_info[field];
field_count += 1;
if (field == 'twitter' && value && value[0] != '@') {
value = '@' + value;
}
if (field == 'notes') {
// Convert notes from markdown to html.
value = marked(value);
} else if (f[1] && value.length > 0) {
blob += value + "\n"
}
if (first) {
first = false;
} else {
strings += ', ';
values_str += ', ';
}
strings += field;
values_str += "?";
values.push(value);
});
strings += ', blob';
values_str += ", ?";
values.push(blob);
strings += values_str + ');';
// Write everything to the DB
var db = new sqlite.Database("rustaceans.db");
db.serialize(function() {
db.run('DELETE FROM people WHERE username=?;', user_info['username'], err_handler);
db.run('DELETE FROM people_channels WHERE person=?;', user_info['username'], err_handler);
if (field_count <= 1) {
return;
}
db.run(strings, values, err_handler);
// irc channels go into a separate table
var irc_string = 'INSERT INTO people_channels (person, channel) VALUES (?, ?);'
var channels = user_info['irc_channels'];
channels.forEach(function(ch) {
db.run(irc_string, user_info['username'], ch, err_handler);
});
callback();
});
db.close();
}
var _ = require('underscore')._ ;
var sq3 = require('sqlite3') ;
var db= new sq3.Database('data/test.sqlite3') ;
var setup= 0 ;
function create_done()
{
++ setup ;
console.log("step " + setup) ;
if ( 2 == setup ) {
db.run("INSERT INTO log (time, msg) VALUES (datetime('now'), ?)", [ 'test startup' ]) ;
}
}
db.run("CREATE TABLE IF NOT EXISTS list ( id PRIMARY KEY, val )", create_done) ;
db.run("CREATE TABLE IF NOT EXISTS log ( id INTEGER PRIMARY KEY AUTOINCREMENT, time, msg )", create_done) ;
// _.each(['ab', 'cd', 'ef'], function(x) { console.log("output _ " + x) ; }) ;
},function(){
db.run("INSERT INTO auth VALUES ($user, $session)",{
$user:req.payload.user,
$session:id
});
});
var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3');
var db = new sqlite3.Database('databases/words.sqlite');
db.run("PRAGMA case_sensitive_like = true");
router.get('/', function(req, res, next) {
var count = 0;
db.get("SELECT COUNT(*) AS tot FROM words", function(err,row){
var respText = "Words API: " + row.tot + " words online.";
res.send(respText);
});
});
// We'll implement our API here...
router.get('/count/:abbrev', function(req, res, next) { var abbrev = req.params.abbrev;
var alen = abbrev.length;
var dataArray = [];
var sql = "SELECT substr(word,1," + alen + "+1) AS abbr, "
+" count(*) AS wordcount FROM words " +" WHERE word LIKE '" + abbrev + "%'"
+" GROUP BY substr(word,1," + alen + "+1)"
db.all(sql, function(err,rows){
for (var i=0;i<rows.length;i++) {
dataArray[i] = { abbrev: rows[i].abbr, count: rows[i].wordcount } }
res.send(dataArray); //Express will stringify data, set Content-type
});
// var data = {};
// var sql = "SELECT COUNT(*) AS wordcount FROM words WHERE word LIKE '" + abbrev + "%'" ;
// db.get(sql, function(err,row){
// data.abbrev = abbrev; data.count = row.wordcount; res.send(data);
// });
});
router.get('/search/:abbrev', function(req, res, next) {
app.put('/upvote/topics/:topic_id', function(req, res){
var id = req.params.topic_id;
db.run("UPDATE topics SET votes=votes+1 WHERE id=" + id + ";");
res.redirect('/');
});
db.all("SELECT * FROM topics WHERE topic='" + req.body.threadName + "';", {}, function(err, innerData){
var topicID = innerData[0].id;
db.run("INSERT INTO comments (topic_ID, user_ID, comment, location) VALUES (" + topicID + ", " + userID + ", '" + marked(req.body.postBody) + "', '" + userLocation + "');");
});
app.delete('/topics/:topic_id/comments/:comment_id', function(req, res){
var id = req.params.comment_id;
db.run("DELETE FROM comments WHERE id=" + id + ";");
res.redirect('/topics/' + req.params.topic_id);
});
db.all("SELECT * FROM users WHERE name='" + username + "';", {}, function(err, data){
var userID = data[0].id;
db.run("INSERT INTO comments (topic_ID, user_ID, comment, location) VALUES (" + topicID + ", " + userID + ", '" + marked(newComment) + "', '" + userLocation + "');");
});
function shutdown() {
logger.info('Shutdown detected. Closing db and sending LOW signal to IO_PIN');
rpio.write(IO_PIN, rpio.LOW);
db.close();
process.exit();
}
// See comments on gender model!
function computeAgeModel(modelReadyCallback){
var pollingData = [];
var censusData = [];
var keys = ["18-29","30-39","40-49","50-64","65 or older"]
function go() {
if(pollingData.length == 0 || censusData.length == 0){
console.log("still waiting on data");
return;
}
console.log("ready to go")
// first decide for the undecided
var popIndex = decideForUndecided(pollingData);
// now apply how we think people will vote to each district
var voteResults = []
for(var i = 0; i < demVoters.length; i++) {
var numberOfVoters = demVoters[i]; // for this district
var cenDatum = censusData[i];
var totalPeople = 0
for(n in cenDatum) { // total people in this district
totalPeople += cenDatum[n]
}
var r18To29 = (cenDatum.range1519*.4+cenDatum.range2024+cenDatum.range2534*.5)/totalPeople;
var r30To39 = (cenDatum.range2534*.5+cenDatum.range3544*.5)/totalPeople;
var r40To49 = (cenDatum.range3544*.5+cenDatum.range4554*.5)/totalPeople;
var r50To64 = (cenDatum.range4554*.5+cenDatum.range5559+cenDatum.range6064)/totalPeople;
var r65Plus = (cenDatum.range6574+cenDatum.range7584+cenDatum.range85plus)/totalPeople;
var agePercents = [r18To29,r30To39,r40To49,r50To64,r65Plus]
var bernieVotes = 0;
for(var j = 0; j < agePercents.length; j++) {
var pollDatum = popIndex[keys[j]];
bernieVotes+=numberOfVoters*agePercents[j]*pollDatum.bernie;
}
bernieVotes = Math.round(bernieVotes);
var hillaryVotes = numberOfVoters - bernieVotes;
voteResults.push({hVotes:hillaryVotes, bVotes:bernieVotes});
}
modelReadyCallback(voteResults);
}
var columns = "population='"+keys.join("' OR population='")+"'";
pollsDB.all("SELECT * FROM Polls WHERE "+columns,
function(error, thing) {
if(error) console.log(error);
pollingData = thing;
go();
})
censusDB.all("SELECT range1519,range2024,range2534, range3544,range4554,range5559,range6064,range6574,range7584,range85plus FROM Census",
function(error, thing){
if(error) console.log(error);
censusData = thing;
go();
})
}
}, function(err, resultCount) {
stream.end();
client.close();
});
var sqlite3 = require('sqlite3')
//set db equal to my db file
var db = new sqlite3.Database('./wikidata.db', function(err, data) {
console.log("DB linked!!");
})
db.run("INSERT INTO authors (author, email) VALUES ('Anna', '*****@*****.**');")
db.run("INSERT INTO authors (author, email) VALUES ('Banana', '*****@*****.**');")
db.run("INSERT INTO authors (author, email) VALUES ('Fofanna', '*****@*****.**');")
db.run("INSERT INTO articles (author_id, title, timestamp, content) VALUES (1, 'THINGS', '2014-05-09', 'LDGLFLJDHFDJLSFGDLSFGLSJDFGLDKFGLDSFGLDSFGLISUDFGLSIDFGLIDKSJGFLKJDSBFLKSDJFSDGKG');")
db.run("INSERT INTO articles (author_id, title, timestamp, content) VALUES (2, 'STUFF', '2014-05-09', 'LDGLFLJDHFDJLSFGDLSFGLSJDFGLDKFGLDSFGLDSFGLISUDFGLSIDFGLIDKSJGFLKJDSBFLKSDJFSDGKG');")
db.run("INSERT INTO articles (author_id, title, timestamp, content) VALUES (1, 'UGH', '2014-05-09', 'LDGLFLJDHFDJLSFGDLSFGLSJDFGLDKFGLDSFGLDSFGLISUDFGLSIDFGLIDKSJGFLKJDSBFLKSDJFSDGKG');")
db.run("INSERT INTO categories (category) VALUES ('apples');")
db.run("INSERT INTO categories (category) VALUES ('oranges');")
db.run("INSERT INTO categories (category) VALUES ('bananas');")
db.run("INSERT INTO tags (article_id, category_id) VALUES (1,3);")
db.run("INSERT INTO tags (article_id, category_id) VALUES (1,2);")
db.run("INSERT INTO tags (article_id, category_id) VALUES (2,1);")
db.run("INSERT INTO tags (article_id, category_id) VALUES (2,2);")
db.run("INSERT INTO tags (article_id, category_id) VALUES (3,3);")
db.run("INSERT INTO changelog (article_id, author, title, timestamp, content) VALUES (1, 'ME', 'THINGS', '2014-05-09', 'LDGLFLJDHFDJLSFGDLSFGLSJDFGLDKFGLDSFGLDSFGLISUDFGLSIDFGLIDKSJGFLKJDSBFLKSDJFSDGKG');")
db.run("INSERT INTO changelog (article_id, author, title, timestamp, content) VALUES (2, 'ME', 'STUFF', '2014-05-09', 'LDGLFLJDHFDJLSFGDLSFGLSJDFGLDKFGLDSFGLDSFGLISUDFGLSIDFGLIDKSJGFLKJDSBFLKSDJFSDGKG');")
db.run("INSERT INTO changelog (article_id, author, title, timestamp, content) VALUES (2, 'ME', 'UGH', '2014-05-09', 'LDGLFLJDHFDJLSFGDLSFGLSJDFGLDKFGLDSFGLDSFGLISUDFGLSIDFGLIDKSJGFLKJDSBFLKSDJFSDGKG');")
db.run("drop table if exists " + TABLE_NAME, function () {
db.run("create table " + TABLE_NAME +
" (id integer primary key autoincrement, title text, text text)",
function () { db.close(); });
});
var db = new sqlite3.Database(dbPath, function(err) {
if (err) {
throw err;
}
var createTablesSql = 'create table if not exists Tasks (title text, done int);';
db.run(createTablesSql, function() {
//create an express application
var app = express();
//use the JSON parser from bodyParser
app.use(bodyParser.json());
//serve static files from the /static sub-directory
app.use(express.static(__dirname + '/static'));
//create routes for our REST API
//GET /api/tasks - returns all tasks
app.get('/api/tasks', function(req, res, next) {
db.all('select rowid, title, done from Tasks where done=0', function(err, rows) {
if(err) {
return next(err);
}
res.json(rows);
});
});
//POST /api/tasks - inserts a new task
app.post('/api/tasks', function(req, res, next) {
db.run('insert into Tasks (title, done) values(?,0)', req.body.title, function(err) {
if (!req.body.title || req.body.title.trim().length == 0) {
turn.next({statusCode: 400, message: 'You must supply a Title!'});
}
if (err) {
return next(err);
}
res.json({rowid: this.lastID});
});
});
//GET /api/tasks/:id - gets a particular task
app.get('api/tasks/:id', function(req, res, next) {
db.get('select rowid, title, done from Tasks where rowid=?', req.params.id, function(err, row) {
if (err) {
return next(err);
}
if (row) {
res.json(row);
} else {
next({statusCode: 404, message: 'Invalid task id!'})
}
res.json(row);
});
})
//PUT /api/tasks/:id - updates a particular task
app.put('/api/tasks/:id', function(req, res) {
db.run('update Tasks set done=? where rowid=?', req.body.done, req.params.id, function(err) {
if (err) {
return next(err);
}
res.json({rowsAffected: this.changes});
});
});
//finally, add an error handler that sends back the error info as JSON
app.use(function(err, req, res, next) {
if (undefined == err.statusCode || 500 == err.statusCode) {
console.error(err);
}
res.status(err.statusCode || 500).json({message: err.message || err.toString()});
});
//start the web server
var server = app.listen(8080, function() {
console.log('listening for requests sent to http://localhost:%s', server.address().port);
});
//listen for the SIGINT signal (Ctrl+C) and shut down the database gracefully
process.on('SIGINT', function() {
console.log('closing database...');
db.close(function(err) {
if (err) {
console.log('error closing database! ' + err);
process.exit(1);
}
else {
console.log('database is safely closed.');
process.exit(0);
}
}); //db.close()
}); //on SIGINT
}); //create tables
}); //open database
function () { db.close(); });
process.on('exit', function() {
db.close();
});
var http = require("http");
var sqlite = require("sqlite3");
var db = new sqlite.Database("tbd.sqlite3");
var fs = require("fs");
var index = fs.readFileSync("./index.html").toString()
db.get("SELECT name FROM sqlite_master WHERE name='tbd'", function(err,row) {
if(row == undefined) {
console.log("Missing database table 'tbd', attempting to create");
db.run("CREATE TABLE tbd (tbd_id char(128), ts timestamp, blob text)",function(e) {
if(e == null) {
console.log("Created new database table 'tbd'");
db.run("CREATE INDEX tbd_index ON tbd(tbd_id,ts);",function(e2) {
if(e2 == null) {
console.log("Created new database index on 'tbd(tbd_id,ts)'");
} else {
console.log("Unable to create database index on 'tbd(tbd_id,ts)', exiting plugin");
}
});
} else {
console.log("Unable to create database table 'tbd', exiting plugin");
}
});
}
});
var server = http.createServer();
server.on("request",function(req,res){
if(req.method.match(/get/i)) {
var id = req.url.replace(/^\/(\d+)\/?$/,"$1");
if(!id.match(/^\d+$/)) {
exports.openKeyDatabase = function (db_params, options) {
if (options.debug) {
sqlite3.verbose();
}
var db = new sqlite3.Database(db_params);
db.createKey = function(key, callback) {
params = {
$kid: key.kid,
$ek: key.ek,
$kekId: key.kekId,
$info: key.info,
$contentId: key.contentId,
$expiration: null
}
if (key.expiration) {
if (typeof key.expiration == 'string') {
try {
var date = new Date(key.expiration).getTime()/1000;
if (!isNaN(date)) {
params.$expiration = date;
}
} catch (err) {
}
}
}
this.run('INSERT INTO Keys (kid, ek, kekId, info, contentId, expiration, lastUpdate) VALUES ($kid, $ek, $kekId, $info, $contentId, $expiration, strftime("%s", "now"))', params, function(err, result) {
if (err) {
err = mapError(err);
}
callback(err, result);
});
}
db.getKeys = function (kids, progressCallback, completionCallback) {
var localProgressCallback = function(err, result) {
if (err) {
err = mapError(err);
} else {
if (Array.isArray(result)) {
for (var i=0; i<result.length; i++) {
normalizeKey(result[i]);
}
} else {
normalizeKey(result);
}
if (kids && kids.length > 1) {
// reorder the result to match the KID order
var indexed = {};
for (var i=0; i<result.length; i++) {
indexed[result[i].kid] = result[i];
}
var reordered = [];
for (var i=0; i<kids.length; i++) {
reordered[i] = indexed[kids[i]];
}
result = reordered;
}
}
progressCallback(err, result);
}
var localCompletionCallback = function(err, result) {
if (err) {
err = mapError(err);
}
completionCallback(err, result);
}
if (kids) {
this.all('SELECT * FROM Keys WHERE '+kidPlaceholders(kids.length), kids, localProgressCallback);
} else {
// get all keys
this.each('SELECT * FROM Keys', localProgressCallback, localCompletionCallback);
}
}
db.putKey = function (kid, key, callback) {
sql = [];
params = [];
if (key.ek) {
sql.push('ek = ?');
params.push(key.ek);
}
if (key.kekId !== undefined) {
sql.push(' kekId = ?');
params.push(key.kekId);
}
if (key.info !== undefined) {
sql.push(' info = ?');
params.push(key.info);
}
if (key.contentId !== undefined) {
sql.push(' contentId = ?');
params.push(key.contentId);
}
if (sql.length == 0) {
console.log("nothing to update");
httpErrorResponse(response, 400, ERR_INVALID_PARAMETERS, 'invalid parameters');
return;
}
params.push(kid);
db.run('UPDATE Keys SET ' + sql.join(',') + ' WHERE kid = ?', params, function(err, result) {
if (err) {
err = mapError(err);
} else {
if (this.changes == 0) {
err = results.NOT_FOUND;
}
}
callback(err, result);
});
}
db.deleteKeys = function (kids, callback) {
this.run('DELETE FROM Keys WHERE '+kidPlaceholders(kids.length), kids, function(err, result) {
if (err) {
err = mapError(err);
}
callback(err, result);
});
}
return db;
};
var subsets = require('random-subsets')
var fs = require('fs')
var sqlite = require('sqlite3')
var path = './app/db/sample.sqlite'
try {
if (fs.readFileSync(path)) fs.unlink(path)
} catch (err) {}
var db = new sqlite.Database(path)
var titles = ['biology', 'awesome', 'science', 'discovery', 'brain', 'genome', 'dna', 'virus', 'code', 'bacteria']
var authors = ['hubel', 'wiesel', 'watson', 'franklin', 'lovelace', 'turing']
db.serialize(function() {
db.run("CREATE VIRTUAL TABLE Papers USING fts4(id INT, author TEXT, title TEXT)")
var stmt = db.prepare("INSERT INTO Papers (id, author, title) VALUES (:id, :author, :title)");
for (var i = 0; i < 500; i++) {
stmt.run(
i,
subsets(authors, parseInt(Math.random() * 5) + 1)[0].join(' '),
subsets(titles, parseInt(Math.random() * 2) + 2)[0].join(' ')
)
}
stmt.finalize()
})
}, {concurrency: 1}).then(() => {
console.log("Saving to sqlite took", new Date() - start, "ms")
return db.execAsync("COMMIT;")
}).then(() => {
var sqlite3 = require("sqlite3");
var db_name = "feedback.db";
var dbpath = "databases/"+db_name;
var db = new sqlite3.Database(dbpath, function(err){
if (err) console.log("DB error: ",err);
});
db.run("CREATE TABLE IF NOT EXISTS feedback (id INTEGER PRIMARY KEY AUTOINCREMENT, body TEXT NOT NULL, staff TEXT, customer TEXT)");
module.exports = db;
app.get('/api/v0/posts/:id', function(req, res) {
var id = parseInt(req.params.id, 10);
db.get('SELECT title, body FROM Posts WHERE id = ?', id, function (err, post) {
res.json(post);
});
});
db.serialize(function() {
db.run("CREATE TABLE testa (id INTEGER PRIMARY KEY AUTOINCREMENT, a TEXT, b TEXT, c INTEGER, e DATE)");
});
function(){
db.close();
callback(null, solarData);
});
exports.getPostTime = function(id, cb){
db.all('select time from contributions as c where c.cid = ?',
[id],
cb);
};