Skip to content

Instantly share code, notes, and snippets.

@jankuca
Created January 1, 2011 13:49
Show Gist options
  • Save jankuca/761760 to your computer and use it in GitHub Desktop.
Save jankuca/761760 to your computer and use it in GitHub Desktop.
JS library for converting MongoDB-like selectors to SQL
// Basic usage
mongo2sql({ 'a': 'b', 'c': 'd' });
"[a] = 'b' AND [c] = 'd'"
mongo2sql({ 'a': { $gt: 123 }, 'c': 'd' });
"[a] > 123 AND [c] = 'd'"
mongo2sql({ $or: [{ 'a': 'b'}, { 'c': 'd' }] });
"( ( [a] = 'b' ) OR ( [c] = 'd' ) )"
mongo2sql({ 'a': { 'b': 'c' } });
"[a__b] = 'c'"
mongo2sql({ 'a': { 'b': { $lte: 123} } });
"[a__b] <= 123"
mongo2sql({ 'a': 'b', $or: [{ 'c': 'd' }, { 'e': 'f' }] });
"[a] = 'b' AND ( ( [c] = 'd' ) OR ( [e] = 'f' ) )"
mongo2sql({ 'a': { $or: [{ 'b': 'c' }, { 'd': 'e', 'f': 'g' }] } });
"( ( [a__b] = 'c' ) OR ( [a__d] = 'e' AND [a__f] = 'g' ) )"
// Parametric usage
mongo2sql_parametric({ 'a': 'b', 'c': 'd' });
[
"[a] = ? AND [c] = ?",
[ "b", "d" ]
]
mongo2sql_parametric({ 'a': 'b', $or: [{ 'c': 'd' }, { 'e': 'f' }] });
[
"[a] = ? AND ( ( [c] = ? ) OR ( [e] = ? ) )",
[ "b", "d", "f" ]
]
// In practice, the whole query could be formed like this:
// Basic usage:
"SELECT * FROM [table] WHERE " + mongo2sql({ 'a': 'b' }) + " ORDER BY [a] DESC"
// Parametric usage:
var cond = mongo2sql_parametric({ 'a': 'b' });
// tx is a Web SQL Transaction object
tx.executeSql(
"SELECT * FROM [table] WHERE " + cond[0] + " ORDER BY [a] DESC",
cond[1]
);
goog.provide('mongo2sql');
goog.require('goog.object');
/**
* Separator of a namespace and a keyname within each level (ns-SEP-keyname)
* For instance if the separator were set to "__" the column name for a field
* named "address:city" in the code would be "address__city".
* @type {string}
*/
mongo2sql.NAMESPACE_SEPARATOR = '__';
/**
* Separator of levels (level-SEP-level)
* For instance if the separator were set to "___" the column name
* for a structure named "parent.address:city" in the code would be
* "parent___address__city".
* @type {string}
*/
mongo2sql.LEVEL_SEPARATOR = '___';
/**
* Regular expression for matching valid column names
* If an invalid column name results from the input keys, an Error is thrown.
* @type {RegExp}
*/
mongo2sql.VALID_COLUMN_NAME = /^(\w+|(lower|upper)\(\[\w+\]\))$/;
/**
* Returns a string that can be used as a WHERE clause in SQL queries and its
* parameters. The string features "?" instead of each parameter to prevent
* SQL injection by letting the native codebase handle the substitution.
* @param {!Object} selector
* @param {string=} prefix Key prefix
* Used mostly internally for nested selectors
* @return {{sql: string, params: Array}}
*/
mongo2sql.stringify = function (selector, prefix) {
prefix = prefix ? prefix + mongo2sql.LEVEL_SEPARATOR : '';
var chunks = [];
var params = [];
goog.object.getKeys(selector).forEach(function (key) {
var value = selector[key];
if (key.charAt(0) !== '$') {
key = key.replace(/:/g, mongo2sql.NAMESPACE_SEPARATOR);
key = key.replace(/\./g, mongo2sql.LEVEL_SEPARATOR);
key = prefix + key;
if (!mongo2sql.VALID_COLUMN_NAME.test(key)) {
throw new Error('Invalid column name ' + key);
}
if (typeof value !== 'object') {
chunks.push('AND ' + mongo2sql.normalizeKey_(key) + ' = ?');
params.push(value);
} else {
goog.object.getKeys(value).forEach(function (sub_key) {
var sub_value = value[sub_key];
var sub;
if (sub_key.charAt(0) !== '$') {
sub_key = key + mongo2sql.LEVEL_SEPARATOR + sub_key;
// { key: value = { sub_key: sub_value }}
if (typeof sub_value === 'object') {
sub = mongo2sql.stringify(value, key);
chunks.push('AND', sub.sql);
params = params.concat(sub.params);
} else {
chunks.push('AND ' + mongo2sql.normalizeKey_(sub_key) + ' = ?');
params.push(sub_value);
}
} else {
// { key: value = { $modifier: value }}
sub = mongo2sql.stringifyModifier_(key, sub_key.substr(1), sub_value);
chunks.push('AND', sub.sql);
params = params.concat(sub.params);
}
});
}
} else {
var modifier = key.substr(1);
switch (modifier) {
case 'or':
// { $or: value = [ selector, selector ]}
chunks.push('AND (');
value.forEach(function (option, i) {
var sub = mongo2sql.stringify(option, prefix);
if (i) {
chunks.push('OR');
}
chunks.push('(', sub.sql, ')');
params = params.concat(sub.params);
});
chunks.push(')');
break;
default:
throw new Error('Invalid modifier $' + modifier);
}
}
});
return {
sql: chunks.join(' ').substr(4),
params: params
};
};
/**
* Returns the key in a form that can be safely used as a column name
* @param {string} key The key to normalize
* @return {string}
*/
mongo2sql.normalizeKey_ = function (key) {
return (key.search(/\[/) !== -1) ? key : '[' + key + ']';
};
/**
* Returns a part of a WHERE clause
* @param {string} key The name of the column
* @param {string} modifier The modifier to apply
* @return {{sql: string, params: Array}}
*/
mongo2sql.stringifyModifier_ = function (key, modifier, value) {
key = mongo2sql.normalizeKey_(key);
var chunks = [ key ];
var params = [];
switch (modifier) {
case 'gt':
chunks.push('>', '?');
params.push(value);
break;
case 'gte':
chunks.push('>=', '?');
params.push(value);
break;
case 'lt':
chunks.push('<', '?');
params.push(value);
break;
case 'lte':
chunks.push('<=', '?');
params.push(value);
break;
case 'ne':
chunks.push('!=', '?');
params.push(value);
break;
case 'in':
if (!value.length) {
throw 'Invalid state: No items for the IN operator';
}
chunks.push('IN (', new Array(value.length + 1).join(', '), ')');
params = params.concat(value);
break;
case 'nin':
if (!value[modifier].length) {
throw 'Invalid state: No items for the NOT IN operator';
}
chunks.push('NOT IN (', new Array(value.length + 1).join(', '), ')');
params = params.concat(value);
break;
case 'exists':
chunks.push('IS', value ? 'NOT NULL' : 'NULL');
break;
case 'search':
chunks = [];
value.forEach(function (item, i) {
chunks.push(i ? 'AND (' : '(');
chunks.push(key, "LIKE '" + item.replace("'", "\\'") + "%'");
chunks.push('OR')
chunks.push(key, "LIKE '% " + item.replace("'", "\\'") + "%'");
chunks.push(')');
});
break;
case 'or':
// { key: { $or: value = [ selector, selector ]}}
chunks.push('AND (');
value.forEach(function (option, i) {
var sub = mongo2sql.stringify(option, key);
if (i) {
chunks.push('OR');
}
chunks.push('(', sub.sql, ')');
params = params.concat(sub.params);
});
chunks.push(')');
break;
default:
throw new Error('Invalid modifier $' + modifier);
}
return {
sql: chunks.join(' '),
params: params
};
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment