<commit_before>/**
 * Things Gateway Database.
 *
 * Stores a list of Things connected to the gateway.
 *
 * This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
 */

'use strict';

const config = require('config');
const sqlite3 = require('sqlite3').verbose();
const fs = require('fs');
const path = require('path');
const Passwords = require('./passwords');
const assert = require('assert');

// Imported as a module so we use a relative path.
const ThingsData = require('../static/things.json');

const TABLES = [
  'users',
  'jsonwebtokens',
  'things',
  'settings',
  'pushSubscriptions',
];

const DEBUG = false || (process.env.NODE_ENV === 'test');

const Database = {
  /**
   * SQLite3 Database object.
   */
  db: null,

  /**
   * Open the database.
   */
  open: function() {
    // If the database is already open, just return.
    if (this.db) {
      return;
    }

    // Don't pull this from user-profile.js, because that would cause a
    // circular dependency.
    let filename;
    let exists = false;
    if (process.env.NODE_ENV === 'test') {
      filename = ':memory:';
    } else {
      filename = path.join(config.get('profileDir'), 'config', 'db.sqlite3');

      const removeBeforeOpen = config.get('database.removeBeforeOpen');

      // Check if database already exists
      exists = fs.existsSync(filename);
      if (exists && removeBeforeOpen) {
        fs.unlinkSync(filename);
        exists = false;
      }
    }

    console.log(exists ? 'Opening' : 'Creating', 'database:', filename);
    // Open database or create it if it doesn't exist
    this.db = new sqlite3.Database(filename);

    // Set a timeout in case the database is locked. 10 seconds is a bit long,
    // but it's better than crashing.
    this.db.configure('busyTimeout', 10000);

    this.db.serialize(() => {
      this.createTables();
      this.migrate();
      // If database newly created, populate with default data
      if (!exists) {
        this.populate();
      }
    });
  },

  createTables: function() {
    // Create Things table
    this.db.run('CREATE TABLE IF NOT EXISTS things (' +
      'id TEXT PRIMARY KEY,' +
      'description TEXT' +
    ');');

    // Create Users table
    this.db.run('CREATE TABLE IF NOT EXISTS users (' +
      'id INTEGER PRIMARY KEY ASC,' +
      'email TEXT UNIQUE,' +
      'password TEXT,' +
      'name TEXT' +
    ');');

    /**
     * This really should have a foreign key constraint but it does not work
     * with our version of node-sqlite / sqlite.
     *
     * https://github.com/mapbox/node-sqlite3/pull/660
     *
     * Instead, the INTEGER user is either the id of the user or -1 if NULL
     */
    this.db.run('CREATE TABLE IF NOT EXISTS jsonwebtokens (' +
      'id INTEGER PRIMARY KEY ASC,' +
      'keyId TEXT UNIQUE,' + // public id (kid in JWT terms).
      'user INTEGER,' +
      'issuedAt DATE,' +
      'publicKey TEXT,' +
      'payload TEXT' +
    ');');

    // Create Settings table
    this.db.run('CREATE TABLE IF NOT EXISTS settings (' +
      'key TEXT PRIMARY KEY,' +
      'value TEXT' +
    ');');

    this.db.run(`CREATE TABLE IF NOT EXISTS pushSubscriptions (
      id INTEGER PRIMARY KEY,
      subscription TEXT
    );`);
  },

  /**
   * Do anything necessary to migrate from old database schemas.
   */
  migrate: function() {
    this.db.run('DROP TABLE IF EXISTS jsonwebtoken_to_user');
  },

  /**
   * Populate the database with default data.
   */
  populate: function() {
    console.log('Populating database with default things...');
    // Populate Things table
    const insertSQL = this.db.prepare(
      'INSERT INTO things (id, description) VALUES (?, ?)');
    for (const thing of ThingsData) {
      const thingId = thing.id;
      delete thing.id;
      insertSQL.run(thingId, JSON.stringify(thing));<commit_msg>subscription should be unique (#1359)<commit_after>/**
 * Things Gateway Database.
 *
 * Stores a list of Things connected to the gateway.
 *
 * This Source Code Form is subject to the terms of the Mozilla Public
 * License, v. 2.0. If a copy of the MPL was not distributed with this
 * file, You can obtain one at http://mozilla.org/MPL/2.0/.
 */

'use strict';

const config = require('config');
const sqlite3 = require('sqlite3').verbose();
const fs = require('fs');
const path = require('path');
const Passwords = require('./passwords');
const assert = require('assert');

// Imported as a module so we use a relative path.
const ThingsData = require('../static/things.json');

const TABLES = [
  'users',
  'jsonwebtokens',
  'things',
  'settings',
  'pushSubscriptions',
];

const DEBUG = false || (process.env.NODE_ENV === 'test');

const Database = {
  /**
   * SQLite3 Database object.
   */
  db: null,

  /**
   * Open the database.
   */
  open: function() {
    // If the database is already open, just return.
    if (this.db) {
      return;
    }

    // Don't pull this from user-profile.js, because that would cause a
    // circular dependency.
    let filename;
    let exists = false;
    if (process.env.NODE_ENV === 'test') {
      filename = ':memory:';
    } else {
      filename = path.join(config.get('profileDir'), 'config', 'db.sqlite3');

      const removeBeforeOpen = config.get('database.removeBeforeOpen');

      // Check if database already exists
      exists = fs.existsSync(filename);
      if (exists && removeBeforeOpen) {
        fs.unlinkSync(filename);
        exists = false;
      }
    }

    console.log(exists ? 'Opening' : 'Creating', 'database:', filename);
    // Open database or create it if it doesn't exist
    this.db = new sqlite3.Database(filename);

    // Set a timeout in case the database is locked. 10 seconds is a bit long,
    // but it's better than crashing.
    this.db.configure('busyTimeout', 10000);

    this.db.serialize(() => {
      this.createTables();
      this.migrate();
      // If database newly created, populate with default data
      if (!exists) {
        this.populate();
      }
    });
  },

  createTables: function() {
    // Create Things table
    this.db.run('CREATE TABLE IF NOT EXISTS things (' +
      'id TEXT PRIMARY KEY,' +
      'description TEXT' +
    ');');

    // Create Users table
    this.db.run('CREATE TABLE IF NOT EXISTS users (' +
      'id INTEGER PRIMARY KEY ASC,' +
      'email TEXT UNIQUE,' +
      'password TEXT,' +
      'name TEXT' +
    ');');

    /**
     * This really should have a foreign key constraint but it does not work
     * with our version of node-sqlite / sqlite.
     *
     * https://github.com/mapbox/node-sqlite3/pull/660
     *
     * Instead, the INTEGER user is either the id of the user or -1 if NULL
     */
    this.db.run('CREATE TABLE IF NOT EXISTS jsonwebtokens (' +
      'id INTEGER PRIMARY KEY ASC,' +
      'keyId TEXT UNIQUE,' + // public id (kid in JWT terms).
      'user INTEGER,' +
      'issuedAt DATE,' +
      'publicKey TEXT,' +
      'payload TEXT' +
    ');');

    // Create Settings table
    this.db.run('CREATE TABLE IF NOT EXISTS settings (' +
      'key TEXT PRIMARY KEY,' +
      'value TEXT' +
    ');');

    this.db.run(`CREATE TABLE IF NOT EXISTS pushSubscriptions (
      id INTEGER PRIMARY KEY,
      subscription TEXT UNIQUE
    );`);
  },

  /**
   * Do anything necessary to migrate from old database schemas.
   */
  migrate: function() {
    this.db.run('DROP TABLE IF EXISTS jsonwebtoken_to_user');
  },

  /**
   * Populate the database with default data.
   */
  populate: function() {
    console.log('Populating database with default things...');
    // Populate Things table
    const insertSQL = this.db.prepare(
      'INSERT INTO things (id, description) VALUES (?, ?)');
    for (const thing of ThingsData) {
      const thingId = thing.id;
      delete thing.id;
      insertSQL.run(thingId, JSON.stringify(thing));
{'fraction_non_alphanumeric': 0.13342503438789546, 'fraction_numerical': 0.005752157058897087, 'mean_word_length': 2.5202464788732395, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '587830', 'n_tokens_mistral': 2498, 'n_tokens_neox': 2356, 'n_words': 788}