compile/utils/build.js

'use strict';

/**
 * #### Database Building Utility Functions
 *
 * These functions can be used to:
 * - create a new table based on a supplied Right Track Table Schema
 * - load the table with the data from the specified source file
 * - add a specified set of initial data to a table
 * @module compile/utils/build
 */

const rl = require('readline');
const fs = require('fs');
const path = require('path');
const log = require('../../helpers/log.js');
const errors = require('../../helpers/errors.js');


/**
 * Initialize the Table in the database.  This will drop an existing table,
 * create a new one (along with any specified indices and/or foreign key
 * relationships) and import the data from the table's source file.
 * @param {object} db SQLite Database being built
 * @param {RTTableSchema} table The Right Track Table Schema
 * @param {AgencyOptions} agencyOptions The Agency Build Options
 * @param {buildTableCallback} callback Callback function called when init is finished
 */
function init(db, table, agencyOptions, callback) {

  // Create the Table
  create(db, table, function() {

    // Load the source file into the table
    load(db, table, agencyOptions, callback);

  });

}


/**
 * Create the table in the database, along with indices and foreign keys
 * @param {object} db SQLite Database being built
 * @param {RTTableSchema} table The Right Track Table Schema
 * @param {buildTableCallback} callback Callback function called when create is finished
 */
function create(db, table, callback) {
  log("        ... Creating " + table.name);

  // Drop Table
  let drop = "DROP TABLE IF EXISTS " + table.name + ";";

  // Create Table
  let create = "CREATE TABLE IF NOT EXISTS " + table.name + " (";
  let indices = "";
  let foreignKeys = "";

  // Loop through each of the table's fields
  for ( let i = 0; i < table.fields.length; i++ ) {
    let field = table.fields[i];

    // Add to create statement
    create += field.name + " " + field.type;
    if ( field.attributes !== undefined ) {
      create += " " + field.attributes;
    }
    if ( i < table.fields.length-1 ) {
      create += ", ";
    }

    // Add Index
    if ( field.index ) {
      indices += "CREATE INDEX IF NOT EXISTS " + field.name + "_index ON " + table.name + " ('" + field.name + "'); ";
    }

    // Add foreign key
    if ( field.foreignKey ) {
      foreignKeys += ", FOREIGN KEY (" + field.name + ") REFERENCES " + field.foreignKey.table + " (" + field.foreignKey.field + ")";
    }

  }
  create += foreignKeys;
  create += ");";

  // Run SQL Statements
  db.serialize(function() {
    db.exec(drop);
    db.exec(create);
    db.exec(indices, function() {
      callback();
    });
  });

}


/**
 * Load the source file into the database
 * @param {object} db SQLite database being built
 * @param {RTTableSchema} table The Right Track Table Schema
 * @param {AgencyOptions} agencyOptions Agency Build Options
 * @param {buildTableCallback} callback Callback function called when load is finished
 */
function load(db, table, agencyOptions, callback) {

  // Skip when no source file provided
  if ( table.sourceFile === undefined && table.sourceDirectory === undefined ) {
    return callback();
  }

  // Make sure source directory is defined if source file provided
  if ( table.sourceFile !== undefined && table.sourceDirectory === undefined ) {
    let msg = "Source directory and/or file are not defined for table " + table.name;
    log.warning("        WARNING: " + msg);
    errors.warning(msg, "Directory: " + table.sourceDirectory + " and File: " + table.sourceFile, agencyOptions.agency.id);
    return callback();
  }

  // Determine source file
  let sourceDirectory = table.sourceDirectory;
  let sourceFile = table.sourceFile;

  // Add agency module directory to relative paths
  if ( _isRelativePath(sourceDirectory) ) {
    sourceDirectory = path.normalize(agencyOptions.agency.moduleDirectory + "/" + sourceDirectory);
  }

  // Build File Path
  let file = path.normalize(sourceDirectory + "/" + sourceFile);

  // Make sure file actually exists
  if ( !fs.existsSync(file) ) {
    let msg = "Source file does not exist (" + sourceFile + ")";
    log.warning("        WARNING: " + msg);
    errors.warning(msg, "File Location: " + file, agencyOptions.agency.id);
    return callback();
  }



  // Start the import...
  log("        ... Importing " + path.basename(file));


  // Source File Headers
  let readHeaders = false;
  let headers = [];

  // Start Transaction
  db.exec("BEGIN TRANSACTION;");

  // Setup readline interface
  let rd = rl.createInterface({
    input: fs.createReadStream(file),
    crlfDelay: Infinity
  });

  // Read each line of the source file
  rd.on('line', function(line) {
    if ( line.toString().length > 0 ) {
      if ( !readHeaders ) {
        headers = _split(line, table.separator);
        readHeaders = true;
      }
      else {
        let items = _split(line, table.separator);

        // Start column and value data strings
        let columns = "(";
        let values = "(";

        // Loop through each value
        for ( let i = 0; i < items.length; i++ ) {
          let header = headers[i];
          let value = items[i];
          let field = _findField(header, table.fields);

          // Add field name and value based on field information
          if ( field !== undefined ) {

            // Add field name
            columns += field.name;

            // Add field value
            if ( field.type.toUpperCase() === 'TEXT' ) {
              values += "'" + value + "'";
            }
            else {
              if ( value === '' ) {
                values += 'NULL';
              }
              else {
                values += value;
              }
            }
          }

          // Add default field name and value
          else {
            columns += header;
            values += "'" + value + "'";
          }

          // Add commas
          if ( i < items.length - 1 ) {
            columns += ", ";
            values += ", ";
          }
        }

        // End columns and values strings
        columns += ")";
        values += ")";

        // Build INSERT statement
        let sql = "INSERT INTO " + table.name + " " + columns + " VALUES " + values + ";";

        // Execute the INSERT
        db.exec(sql);
      }
    }
  });

  rd.on('close', function() {
    db.exec("COMMIT;", function() {
      callback();
    });
  });
}


/**
 * Add the specified values into the table
 * @param {object} db SQLite database being built
 * @param {RTTableSchema} table The Right Track Table Schema
 * @param {object[]} values List of data to add to table.  Each object is a set
 * of data keypairs where the property name is the column header name and the
 * value is the data value to add.  Property names must match field names as
 * specified in the `RTTableSchema`.
 * @param {buildTableCallback} callback Callback function called when the data has been added
 */
function add(db, table, values, callback) {
  log("        ... Adding data to " + table.name);
  db.serialize(function() {
    db.exec("BEGIN TRANSACTION");

    // Loop through all of the values to add
    for ( let i = 0; i < values.length; i++ ) {
      let value = values[i];
      let columns = "";
      let data = "";

      // Loop through value's properties
      for ( let property in value ) {
        if ( value.hasOwnProperty(property) ) {

          // Check field is set in table schema
          let field = _findField(property, table.fields);

          if ( field !== undefined ) {
            columns += field.name + ", ";
            if ( field.type.toUpperCase() === "TEXT" ) {
              data += "'" + value[property] + "', ";
            }
            else {
              data += value[property] + ", ";
            }
          }
          else {
            let msg = "Field " + property + " not found in table schema for table " + table.name;
            log.warning("        WARNING: " + msg);
            errors.warning(msg, "DB File: " + db.filename, undefined);
          }

        }
      }

      // Trim last commas and spaces
      if ( columns.indexOf(", ") > -1 ) {
        columns = columns.substring(0, columns.length - 2);
      }
      if ( data.indexOf(", ") > -1 ) {
        data = data.substring(0, data.length - 2);
      }

      // Build and run SQL statement
      let sql = "INSERT INTO " + table.name + " (" + columns + ") VALUES (" + data + ");";
      db.exec(sql);
    }

    db.exec("COMMIT", function() {
      callback();
    });
  });
}




// ==== HELPER FUNCTIONS ===== //


/**
 * Check if the directory is a relative path (begins with './' or '../')
 * @param {string} directory Path to directory
 * @return {boolean} True if the directory is a relative path
 * @private
 */
function _isRelativePath(directory) {
  if ( typeof directory === 'string' ) {
    if ( directory.charAt(0) === '.' ) {
      if ( directory.charAt(1) === '/' ) {
        return true;
      }
      if ( directory.charAt(1) === '.' ) {
        if ( directory.charAt(2) === '/' ) {
          return true;
        }
      }
    }
    return false;
  }
  else {
    return false;
  }
}


/**
 * Split a line from a source file into separate fields.  Remove all
 * double and single quotes from the field
 * @param {string} str The line from the source file
 * @param {string} [sep=,] The field separator
 * @returns {Array} List of parsed fields
 * @private
 */
function _split(str, sep=',') {
  let items = str.split(sep);
  for ( let i = 0; i < items.length; i++ ) {
    let item = items[i];
    if ( item.indexOf("\"") > -1 ) {
      item = item.replace(/"/g, "");
    }
    if ( item.indexOf("'") > -1 ) {
      item = item.replace(/'/g, "");
    }
    items[i] = item;
  }
  return items;
}


/**
 * Find the data field in the table schema based on the
 * name or source_name of the field
 * @param {string} name Lookup table field name
 * @param {Object[]} fields RT Table Schema fields
 * @returns {object|undefined} RT Table Schema for matching field
 * or undefined if no match found.
 * @private
 */
function _findField(name, fields) {
  for ( let i = 0; i < fields.length; i++ ) {
    let field = fields[i];
    if ( field.name === name ) {
      return field;
    }
    else if ( field.source_name === name ) {
      return field;
    }
  }
  return undefined;
}




module.exports = {
  init: init,
  create: create,
  load: load,
  add: add
};