[JavaScript] [AngularJS] [Cordova] [ngCordova] [$cordovaSQLiteSource] Cordova Data Objects

cordova.database.factory.js

/**
 * @license
 * Copyright (c) 2017-present, Luca Bertolasi. All Rights Reserved.
 *
 * This software is licensed under the terms of the MIT license,
 * a copy of which can be found at https://opensource.org/licenses/MIT
 */

// REQUIRES: http://ngcordova.com/docs/plugins/sqlite/

(() => {

  angular
    .module('CDO')
    .factory('Database', Database);

  /* @ngInject */
  function Database($cordovaSQLite) {
    const MODEL = {};
    //removeIf(production)
    window.model.database = MODEL;
    //endRemoveIf(production)

    const DB_OPTIONS = {
      iosDatabaseLocation: 'default', // NOT backed up by iCloud, NOT visible to iTunes
      key: '<random_string>', // 30 chars
      name: '<random_string>.db' // 30 chars
    }
    const PLACEHOLDER = {
      COUNT: 'count'
    }

    return {
      clearTable,
      countAll,
      countOneColumn,
      createTable,
      db,
      deleteDB,
      deleteOne,
      executeSql,
      init,
      insertMany,
      insertOne,
      selectManyColumn,
      selectOneColumn,
      updateManyColumn,
      updateOneColumn
    };

    function clearTable(table, callback) {
      // column = 'string'
      executeSql(`DELETE FROM ${table}`, [], callback);
    } // clearTable

    function countAll(table, callback) {
      // table = 'string'
      executeSql(`SELECT COUNT(*) AS ${PLACEHOLDER.COUNT} FROM ${table}`, [], callback);
    } // countAll

    function countOneColumn(table, column, callback) {
      // table = 'string'
      // column = 'string'
      executeSql(`SELECT COUNT(${column}) AS ${PLACEHOLDER.COUNT} FROM ${table}`, [], callback);
    } // countOneColumn

    function createTable(table, fields, callback) {
      // table = 'string'
      // fields = [{ column = 'string', type = 'string' }] OR ['string'] // type -> a string representing the appropriate SQLite affinity
      let query = `CREATE TABLE IF NOT EXISTS ${table} (id INTEGER PRIMARY KEY`;
      fields.forEach(field => {
        let column = field.column || field;
        let type = field.type || 'TEXT';
        query = `${query}, ${column} ${type}`;
      });
      query = `${query})`;

      executeSql(query, [], callback);
    } // createTable

    function db(obj) {
      return angular.isObject(obj) || obj === null
      ? MODEL.db = obj
      : MODEL.db;
    } // db

    function deleteDB() {
      $cordovaSQLite.deleteDB(DB_OPTIONS)
    } // deleteDB

    function deleteOne(table, column, value, callback) {
      // table, column = 'string'
      // value = any // depends on the column type
      executeSql(`DELETE FROM ${table} WHERE ${column} = ?`, [value], callback);
    } // deleteOne

    function executeSql(query, valueArr = [], callback) {
      $cordovaSQLite.execute(db(), query, valueArr)
      .then(res => {
        //removeIf(production)
        console.log(`---- executeSql SUCCESS : ${query} -> ${valueArr} -> ${JSON.stringify(res)}`);
        //endRemoveIf(production)

        if (callback) {
          let results = {};

          for (let i = 0; i < res.rows.length; i += 1) {
            // res.rows.item(i) = object containing the i-nth row's columns; values can be null if table admits
            // { column1: value1, column2: value2, ... }
            let row = res.rows.item(i);

            for (let column in row) {
              let value = row[column];

              if (!!results[column]) {
                results[column].push(value);

              } else {
                results[column] = column === PLACEHOLDER.COUNT ? value : [value];
              }
            }
          }

          callback(results);
        }

      }, err => {
        //removeIf(production)
        console.log(`---- executeSql ERROR : ${query} -> ${valueArr} -> ${JSON.stringify(err)}`);
        //endRemoveIf(production)
        // window.fabric.Crashlytics.addLog(`---- executeSql ERROR : ${query} -> ${JSON.stringify(err)}`); // privacy: do not log 'valueArr'
      });
    } // executeSql

    function init(callback) {
      db($cordovaSQLite.openDB(DB_OPTIONS));
      callback();
    } // init

    function insertMany(table, columns, values, callback) {
      // table = 'string'
      // columns = ['string']
      // values = [any] // depends on the column type
      executeSql(`INSERT INTO ${table} (${columns.join(', ')}) VALUES (${'?, '.repeat(columns.length).replace(/,\s*$/, '')})`, values, callback);
    } // insertMany

    function insertOne(table, column, value, callback) {
      // table = 'string'
      // column = 'string'
      // value = any // depends on the column type
      executeSql(`INSERT INTO ${table} (${column}) VALUES (?)`, [value], callback);
    } // insertOne

    function selectManyColumn(table, columns, callback) {
      // table = 'string'
      // columns = ['string']
      executeSql(`SELECT ${columns.join(', ')} FROM ${table}`, [], callback);
    } // selectManyColumn

    function selectOneColumn(table, column, callback) {
      // table = 'string'
      // column = 'string'
      executeSql(`SELECT ${column} FROM ${table}`, [], callback);
    } // selectOneColumn

    function updateManyColumn(table, columns, values, callback) {
      // table = 'string'
      // columns = ['string']
      // values = [any] // depends on the column type
      countAll(table, res => {
        // res.count = 'integer'
        if (res.count === 0) {
          // no values to update, must insert
          insertMany(table, columns, values, callback);
        } else {
          // update values
          executeSql(`UPDATE ${table} SET ${columns.join(' = ?, ')} = ?`, values, callback);
        }
      });
    } // updateManyColumn

    function updateOneColumn(table, column, value, callback) {
      // table, column = 'string'
      // value = any // depends on the co lumn type
      countAll(table, res => {
        // res.count = 'integer'
        if (res.count === 0) {
          // no value to update, must insert
          insertOne(table, column, value, callback);
        } else {
          // update value
          executeSql(`UPDATE ${table} SET ${column} = ?`, [value], callback);
        }
      });
    } // updateOneColumn

  } // Database
})();