sql-utils

Utilities to assist with SQL script generation.

Installation

npm install @panosoft/sql-utils

Usage

var utils = require('sql-utils');

API

• buildCriteria
• parenthesize
• quote
• quoteList
• parseOrderColumn
• replaceOrderColumns

buildCriteria ( criteriaArrays )

Joins an array into a criteria string delimited by AND's and OR's.

Arguments

• criteriaArrays - An array or an array of arrays to be joined.

Examples

var array = ['a', 'b', 'c'];
var criteria = utils.buildCriteria(array);
console.log(criteria); // '(a AND b AND c)'

var arrays = [
  ['a', 'b', 'c'],
  ['d', 'e', 'f']
];
var criteria = utils.buildCriteria(arrays);
console.log(criteria); // '((a AND b AND c) OR (d AND e AND f))'

parenthesize ( string )

Wraps a string with parenthesis.

Arguments

• string - A string to wrap.

Example

var string = utils.parenthesize('a');
console.log(string); // '(a)'

quote ( string )

Wraps a string with single quotes.

Arguments

• string - A string to wrap.

Example

var string = utils.quote('a');
console.log(string); // "'a'"

quoteList ( strings )

Converts an array of strings to a quoted comma delimited list.

Arguments

• strings - An array of strings to convert.

Example

var string = utils.quoteList(['a', 'b', 'c']);
console.log(string); // "'a','b','c'"

parseOrderColumn ( orderColumn )

Returns an Array with the orderColumn in index 0 and optionally ASC/DESC in index 1.

Arguments

• orderColumn - A string definining a SQL ordering.

Examples

var orderColumn = 'userName ASC';
utils.parseOrderColumn(orderColumn); // ['userName', 'ASC']

var orderColumn = 'refNum';
utils.parseOrderColumn(orderColumn); // ['refNum']

replaceOrderColumns ( orderColumns, componentColumns )

Takes an Array of Order Columns and replace any column that has components in the Object of Component Columns. This will preserve the ASC/DESC suffix in the original Order Column.

This is VERY useful for replacing a name column with its components, i.e. first, last and middle.

Arguments

• orderColumns - An Array of Order Columns.
• componentColumns - An Object of component columns where the key is the order column and the value is an Array of component columns.

Examples

var orderColumns = ['userName ASC', 'clientName DESC', 'refNum'];
var componentColumns = {
	userName: ['usrLastName', 'usrFirstName', 'usrMI'],
	clientName: ['clientLastName', 'clientFirstName', 'clientMI']
};
var columns = utils.replaceOrderColumns(orderColumns, componentColumns);
console.log(columns);
/*[
	'usrLastName ASC',
	'usrFirstName ASC',
	'usrMI ASC',
	'clientLastName DESC',
	'clientFirstName DESC',
	'clientMI DESC',
	'refNum'
]*/```