Source: violet-conversations/lib/violetStorePG.js

/* Copyright (c) 2017-present, salesforce.com, inc. All rights reserved */
/* Licensed under BSD 3-Clause - see LICENSE.txt or git.io/sfdc-license */

/**
 * Plugin which makes it easy to store, update, retrieve, and delete data form
 * an underlying Postgres data store.
 * <br><br>
 * You can give the API a SQL query or use one of the easier API's and
 * the plugin will generate the query.
 *
 * @module violetStorePG
 */

// only load is implemented, need to implement store and update
const StorePlugin = require('./storePlugin.js');
var pg = require('pg');

var client = null;
var connectionCB = [];
var _connectToDB = function() {
  if (process.env.DATABASE_URL) {
    client = new pg.Client({
      connectionString: process.env.DATABASE_URL,
      ssl: true,
    });
  } else {
    client = new pg.Client();
  }

  console.log('Connecting to Postgres');
  return client.connect().then(()=>{
    console.log('Connected to Postgres');
    connectionCB.forEach(cb=>{cb()});
    connectionCB = null;
    return Promise.resolve(client);
  });
}
_connectToDB();


/**
 * Implements support for the core operations supported by Violet (for
 * Postgres).
 */
class VioletStorePG extends StorePlugin {

  constructor(violet) {
    super(violet);
    this.defaultPropOfInterest = [];
  }

  /**
   * Retrieves given object from the underlying data store.
   *
   * @example <caption>performing a raw (SQL) query</caption>
   * resolve: function *(response) {
   *   var results = yield response.load({
   *     query: "CreatedDate, Status, Verified FROM Automated_Tests WHERE Status = 'New'  limit 100"
   *   });
   *   response.say(`Found ${results.length} tests`);
   * });
   *
   * @example <caption>query with parameters as needed</caption>
   * violetSFStore.store.propOfInterest = {
   *   'Automated_Tests': ['Name', 'Status', 'Verified']
   * };
   * ...
   * resolve: function *(response) {
   *   var results = yield response.load({
   *     objName: 'Automated_Tests',
   *     keyName: 'Status',
   *     keyVal: 'New'
   *   });
   *   response.say(`Found ${results.length} tests`);
   * });
   *
   * @example <caption>basic query</caption>
   * violetSFStore.store.propOfInterest = {
   *   'Automated_Tests': ['Name', 'Status', 'Verified']
   * };
   * ...
   * resolve: function *(response) {
   *   var results = yield response.load('Automated_Tests', 'Status', 'New');
   *   response.say(`Found ${results.length} tests`);
   * });
   *
   * @param {Object} queryParams - query parameters
   * @param {string} queryParams.objName - the object/table name in the data
   *   store where the give object is to be updated
   * @param {string} queryParams.keyName - the key name to find the object to be updated
   * @param {string} queryParams.keyVal - the key value to find the object to be updated
   * @param {string} queryParams.query - the SQL query, i.e. what gets executed is "SELECT <query>"
   * @param {string} queryParams.filter - additional query results filter - this
   *  is added to the end of the SQL query
   * @param {string} queryParams.queryXtra - additional additional bits to be
   *  added to the end of the query, for example "LIMIT 100"
   * @returns {Promise} Promise that resolves with the data
   */
  load(params) {
    // console.log('this.load: ' + params.objName, this._objProps(params));
    var q = this._buildQuery(params);
    console.log('sql: ' + q);

    return client.query(q).then(function(resp){
      // console.log(resp);
      if (!resp.rows) {
        console.log('no results');
        return;
      } else console.log('found ' + resp.rows.length + ' records');

      return resp.rows;

    }).catch((err)=>{
      console.log('sfdc query err', err);
    });
  }

  /**
   * Adds given object to the underlying data store.
   *
   * @example
   * resolve: function *(response) {
   *   var caseObj = ...
   *   yield response.store('CaseComment', {
   *     'CommentBody': 'Text String',
   *     'ParentId': caseObj.Id
   *   });
   *   response.say(`Case ${caseObj.Subject} has comment added`);
   * }
   *
   * @param {string} objName - the object/table name in the data store where
   *   the give object is to be stored
   * @param {Object} dataToStore - the object to be written (i.e. a set of
   *   key:value pairs)
   * @returns {Promise} Promise that resolves when the data has been written in
   *   the store
   */
  store(objName, dataToStore) {
    var objKeys = Object.keys(dataToStore);
    var objValues =  objKeys.map(k=>{return dataToStore[k]});
    var insertValues = Object.keys(objKeys).map(k=>{return '$'+(parseInt(k)+1)});
    var insertStmt = `insert into ${this._getCompoundNameToStr(objName)} (${objKeys.join(', ')}) values (${insertValues.join(', ')})`;
    console.log('storing: ' + insertStmt, objValues);
    return client.query({text: insertStmt, values: objValues}).then(()=>{
      console.log('Stored');
    }).catch((err)=>{
      console.log('pg store err', err);
    });
  }

  /**
   * Updates given object in the underlying data store.
   *
   * @example
   * resolve: function *(response) {
   *   var caseObj = ...
   *   yield response.update('Case', 'CaseNumber', caseObj.CaseNumber, {
   *       'Priority': response.get('casePriority')
   *   });
   *   response.say( `Case ${caseObj.Subject} has priority updated to [[casePriority]]`);
   * }
   *
   * @param {string} objName - the object/table name in the data store where
   *   the give object is to be updated
   * @param {string} keyName - the key name to find the object to be updated
   * @param {Object} keyVal - the key value to find the object to be updated
   * @param {Object} updateData - the object values to be updated (i.e. a set of
   *   key:value pairs)
   * @returns {Promise} Promise that resolves when the data has been updated in
   *   the store
   */
  update(objName, keyName, keyVal, updateData) {
    // console.log('sfStore.update: ' + objName, sfStore._objProps({objName}));
    var objKeys = Object.keys(updateData);
    var objValues =  objKeys.map(k=>{return updateData[k]});
    var updateValues = Object.keys(objKeys).map(k=>{return '$'+(parseInt(k)+1)});
    var updateStmt = `UPDATE ${this._getCompoundNameToStr(objName)}
              SET (${objKeys.join(', ')}) = (${updateValues.join(', ')})
              WHERE ${this._getCompoundNameToStr(keyName)} = '${keyVal}'`;

    console.log('updating: ' + updateStmt, objValues);
    return client.query({ text: updateStmt, values: objValues }).then(function(){
      console.log('Updated');
    }).catch((err)=>{
      console.log('pg update err', err);
    });
  }

  /**
   * Deletes given object in the underlying data store.
   *
   * @example
   * resolve: function *(response) {
   *   var caseObj = ...
   *   yield response.update('Case', 'CaseNumber', caseObj.CaseNumber);
   *   response.say( `Case ${caseObj.Subject} has been removed`);
   * }
   *
   * @param {string} objName - the object/table name in the data store where
   *   the give object is to be deleted
   * @param {string} keyName - the key name to find the object to be deleted
   * @param {Object} keyVal - the key value to find the object to be deleted
   * @returns {Promise} Promise that resolves when the data has been deleted in
   *   the store
   */
  delete(objName, keyName, keyVal) {
    // console.log('sfStore.update: ' + objName, sfStore._objProps({objName}));
    var deleteStmt = `Delete FROM ${this._getCompoundNameToStr(objName)}
              WHERE ${this._getCompoundNameToStr(keyName)} = '${keyVal}'`;

    console.log('deleting: ' + deleteStmt);
    return client.query(deleteStmt).then(function(){
      console.log('Deleted');
    }).catch((err)=>{
      console.log('pg delete err', err);
    });
  }


};

// module.exports.store = pgStore;
module.exports = function(violet) {
  var pgStore = new VioletStorePG(violet);
  var connectionsCnt = 0;
  return {
    /** Allows access to the store */
    store: pgStore,
    /**
     * Returns a promise which will resolve with the store having access to the
     * Postgres DB
     */
    connect: ()=>{
      connectionsCnt++;
      if (client == null) return _connectToDB();                  // likely someone already cleaned-up
      if (connectionCB == null) return Promise.resolve(client);   // already connected
      return new Promise(function(resolve, reject) {              // trying to connect - add ourselves to the CB
        connectionCB.push(()=>{resolve(client)});
      });
    },
    /**
     * Close connection to the DB
     */
    cleanup: ()=>{
      connectionsCnt--;
      if (connectionsCnt > 0) return;
      console.log('Cleaning connection to Postgres');
      client.end();
      client = null;
    },
  };
};
Documentation generated by JSDoc 3.5.5 on Sat Mar 14 2020 19:55:45 GMT-0400 (EDT)