How to use sqlite - 10 common examples

htest('sqlite test shutdown', t => {
    db.close()
      .then(() => {
        console.log('shut down sqlite')
        t.ok(true, 'finished db.close')
        t.end()
        // And exit on next tick:
        setImmediate(() => { process.exit(0) })
      })
  })
}

const main = async () => {
  try {
    const hrProcStart = process.hrtime()
    let hrQueryStart = 0
    await db.open(':memory:')
    await csvimport.importSqlite(testPath, ',', {noHeaderRow: false})
    // await db.open('/Users/antony/data/testdb.sqlite')
    const [es, ens] = process.hrtime(hrProcStart)
    console.info('runQuery: import completed in %ds %dms', es, ens / 1e6)
    // console.log('table import complete: ', md.tableName)
    console.log('running query:\n', tq)
    hrQueryStart = process.hrtime()

    const rows = await db.all(tq)
    const [qes, qens] = process.hrtime(hrQueryStart)
    console.log('read rows from sqlite table.')
    console.table(rows)
    console.info('runQuery: evaluated query in %ds %dms', qes, qens / 1e6)
    await db.close()
  } catch (err) {
    console.error('caught exception running query: ', err, err.stack)
  }
}

let hrQueryStart = 0
    await db.open(':memory:')
    await csvimport.importSqlite(testPath, ',', {noHeaderRow: false})
    // await db.open('/Users/antony/data/testdb.sqlite')
    const [es, ens] = process.hrtime(hrProcStart)
    console.info('runQuery: import completed in %ds %dms', es, ens / 1e6)
    // console.log('table import complete: ', md.tableName)
    console.log('running query:\n', tq)
    hrQueryStart = process.hrtime()

    const rows = await db.all(tq)
    const [qes, qens] = process.hrtime(hrQueryStart)
    console.log('read rows from sqlite table.')
    console.table(rows)
    console.info('runQuery: evaluated query in %ds %dms', qes, qens / 1e6)
    await db.close()
  } catch (err) {
    console.error('caught exception running query: ', err, err.stack)
  }
}

email
    posts {
      id
      body
      numComments
      comments {
        id
        body
        authorId
        archived
      }
    }
  }
}`

db.open(path.join(__dirname, '..', 'db', 'test1-data.sl3'))
.then(() => graphql(schema, query))
.then(res => {
  console.log(require('util').inspect(res, { depth: 10 })) // eslint-disable-line
})
.catch(console.error) // eslint-disable-line

this.on('ready', this.onReady())
			.on('commandPrefixChange', this.onCommandPrefixChange())
			.on('error', console.error)
			.on('warn', console.warn)
			//.on('debug', console.log)
			.on('disconnect', this.onDisconnect())
			.on('reconnecting', this.onReconnect())
			.on('commandError', this.onCmdErr())
			.on('commandBlocked', this.onCmdBlock())
			.on('commandStatusChange', this.onCmdStatusChange())
			.on('groupStatusChange', this.onGroupStatusChange())
			.on('message', this.onMessage());

		// set provider sqlite so we can actually save our config permanently
		this.setProvider(
			sqlite.open(path.join(__dirname.slice(0, -3), 'data/BotSettings.sqlite3')).then(db => new Commando.SQLiteProvider(db))
		).catch(console.error);

		// first we register groups and commands
		this.registry
			.registerDefaultGroups()
			.registerGroups([
				['ombi', 'Ombi'],
				['sonarr', 'Sonarr'],
				['radarr', 'Radarr'],
				['tautulli', 'Tautulli']
			])
			.registerDefaultTypes()
			.registerDefaultCommands({
				'help': true,
				'prefix': true,
				'ping': true,

[QUEUE_REMOVE]: async (sock, { payload }, acknowledge) => {
    let whereClause = sql`queueId = ${payload.queueId} AND roomId = ${sock.user.roomId}`

    // admins can remove any
    if (!sock.user.isAdmin) {
      whereClause += sql` AND userId = ${sock.user.userId}`
    }

    const query = sql`
      DELETE FROM queue
      WHERE ${whereClause}
    `
    const res = await db.run(String(query), query.parameters)

    if (!res.stmt.changes) {
      return acknowledge({
        type: QUEUE_REMOVE + '_ERROR',
        error: 'Could not remove queueId: ' + payload.queueId,
      })
    }

    // success
    acknowledge({ type: QUEUE_REMOVE + '_SUCCESS' })

    // tell room
    sock.server.to(sock.user.roomId).emit('action', {
      type: QUEUE_PUSH,
      payload: await Queue.get(sock.user.roomId)
    })

*
* Copyright (C) 2011, The Locker Project
* All rights reserved.
*
* Please see the LICENSE file for more information.
*
*/

var IJOD = require('../../Common/node/ijod').IJOD;
var sqlite = require('sqlite');

// var INDEXED_FIELDS = [{fieldName:'timeStamp', fieldType:'REAL'}, {fieldName:'data.id', fieldType:'REAL'}];

var people = {};
var statuses = {};
var currentDB = new sqlite.Database();
exports.init = function(callback) {
    if(!people.followers && ! people.friends) {
        people.followers = new IJOD('followers');
        people.friends = new IJOD('friends');
        statuses.home_timeline = new IJOD('home_timeline');
        statuses.user_timeline = new IJOD('user_timeline');
        statuses.mentions = new IJOD('mentions');
        people.followers.init(function() {
            people.friends.init(function() {
                statuses.home_timeline.init(function() {
                    statuses.user_timeline.init(function() {
                        statuses.mentions.init(function() {
                            openDB(callback);
                        });
                    });
                });

/*
*
* Copyright (C) 2011, The Locker Project
* All rights reserved.
*
* Please see the LICENSE file for more information.
*
*/

var sys    = require('sys'),
    sqlite = require('sqlite');

var db = new sqlite.Database();

// open the database for reading if file exists
// create new database file if not

var fs = require('fs');
var exec  = require('child_process').exec;

fs.mkdir('my', 0755);

function copyAndExtract(accountID) {
    child = exec('cp ' + process.env.HOME + '/Library/Application\\ Support/Firefox/Profiles/7v61pmci.default/places.sqlite places.sqlite', function(error, stdout, stderr) {
        fs.mkdir('my/' + accountID, 0755);
        var stream = fs.createWriteStream('my/' +accountID + '/history.json');
        db.open('places.sqlite', function (error) {
          if (error) {
              console.log("Tonight. You.");

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');