(function() { var is_common_js = typeof exports != 'undefined'; var _ = is_common_js ? require('underscore') : window._; var sql = is_common_js ? require('../sql-bricks.js') : window.SqlBricks; if (is_common_js) { var args = process.argv; if (args[args.length - 1] == '--empty-extension') { console.log('tests configured with an empty extension'); sql = sql._extension(); } } var assert; if (is_common_js) { assert = require('assert'); } else { assert = function(condition, message) { if (!condition) throw new Error(message); }; assert.equal = function(actual, expected) { if (actual != expected) throw new Error(JSON.stringify(actual) + ' == ' + JSON.stringify(expected)); }; assert.deepEqual = function(actual, expected) { if (!_.isEqual(actual, expected)) throw new Error(JSON.stringify(actual) + ' == ' + JSON.stringify(expected)); }; assert.throws = function(fn) { try { fn(); } catch(ex) { return true; } throw new Error('The function passed to assert.throws() did not throw'); } } var select = sql.select, insertInto = sql.insertInto, insert = sql.insert, update = sql.update, del = sql.delete; var and = sql.and, or = sql.or, like = sql.like, not = sql.not, $in = sql.in, isNull = sql.isNull, isNotNull = sql.isNotNull, equal = sql.equal, eq = sql.eq, notEq = sql.notEq, lt = sql.lt, lte = sql.lte, gt = sql.gt, gte = sql.gte, between = sql.between, exists = sql.exists, eqAny = sql.eqAny, notEqAny = sql.notEqAny, union = sql.union; var alias_expansions = {'usr': 'user', 'psn': 'person', 'addr': 'address'}; var table_to_alias = _.invert(alias_expansions); sql.aliasExpansions(alias_expansions); sql.joinCriteria(function(left_tbl, left_alias, right_tbl, right_alias) { var criteria = {}; criteria[left_alias + '.' + table_to_alias[right_tbl] + '_fk'] = right_alias + '.pk'; return criteria; }); describe('SQL Bricks', function() { describe('mini-templating lang', function() { it('should find content of if', function() { var result = sql.templ('{{#if test}}Hi there!{{/if}}', {'test': true}); assert.equal(result, 'Hi there!'); }); it('should display content on both sides of it', function() { var result = sql.templ('before{{#if test}}inside{{/if}}after', {'test': false}); assert.equal(result, 'beforeafter'); }); it('should handle multiple values', function() { var result = sql.templ('before{{val}}between{{val2}}after', {'val': 'value 1', 'val2': 'value 2'}); assert.equal(result, 'beforevalue 1betweenvalue 2after'); }); it('should handle multiple if chunks', function() { var result = sql.templ('before{{#if test}}first{{/if}}between{{#if test}}second{{/if}}after', {'test': true}); assert.equal(result, 'beforefirstbetweensecondafter'); }); it('should throw on mismatched if', function() { assert.throws(function() { sql.templ('{{#if test}}Hi there!', {'test': true}); }); }); it('should handle nested if', function() { var result = sql.templ('{{#if oneThing}} and {{#if anotherThing}}Test{{/if}}{{/if}}', {'oneThing': true, 'anotherThing': true}); assert.equal(result, ' and Test'); }); }); describe('parameterized sql', function() { it('should generate for insert statements', function() { var values = {'first_name': 'Fred', 'last_name': 'Flintstone'}; checkParams(insert('user', values), 'INSERT INTO "user" (first_name, last_name) VALUES ($1, $2)', ['Fred', 'Flintstone']); }); it('should generate for UPDATEs', function() { var values = {'first_name': 'Fred', 'last_name': 'Flintstone'}; checkParams(update('user', values), 'UPDATE "user" SET first_name = $1, last_name = $2', ['Fred', 'Flintstone']); }); it('should generate for WHERE clauses', function() { checkParams(select().from('user').where({ 'removed': 0, 'name': 'Fred Flintstone' }), 'SELECT * FROM "user" WHERE removed = $1 AND name = $2', [0, 'Fred Flintstone']); }); it('should generate WHERE clauses with arbitrary SQL', function() { var stmt = select().from('time_limits'); stmt.where(sql( "tsrange(start_date_time, end_date_time, '[]') @> tsrange($1, $2, '[]')")); check(stmt, "SELECT * FROM time_limits WHERE tsrange(start_date_time, end_date_time, '[]') @> tsrange($1, $2, '[]')"); }); it('should generate WHERE clauses with arbitrary SQL and parameters', function() { var stmt = select().from('time_limits'); var time1 = '2014-12-06T22:35:00'; var time2 = '2014-12-06T22:36:00'; stmt.where(sql( "tsrange(start_date_time, end_date_time, '[]') @> tsrange($, $, '[]')", time1, time2)); checkParams(stmt, "SELECT * FROM time_limits WHERE tsrange(start_date_time, end_date_time, '[]') @> tsrange($1, $2, '[]')", ['2014-12-06T22:35:00', '2014-12-06T22:36:00']); }); it('should handle WHERE clauses with sql() and a null check', function() { check(select().from('test').where(sql('my_col_val()'), null), "SELECT * FROM test WHERE my_col_val() IS NULL"); }); it('should not escape single quotes in the values returned by toParams()', function() { checkParams(update('user', {'name': "Muad'Dib"}), 'UPDATE "user" SET name = $1', ["Muad'Dib"]); }); it('should not attempt to serialize arrays (should be done by subsequent layer)', function() { checkParams(update('user', {'name': ["Paul", "Muad'Dib"]}), 'UPDATE "user" SET name = $1', [["Paul","Muad'Dib"]]); }); it('should not attempt to serialize dates', function() { var dt = new Date(); checkParams(update('user', {'birthdate': dt}), 'UPDATE "user" SET birthdate = $1', [dt]); }); it('should generate node-sqlite3 style params', function() { var values = {'first_name': 'Fred', 'last_name': 'Flintstone'}; var result = insert('user', values).toParams({'placeholder': '?%d'}); assert.equal(result.text, 'INSERT INTO "user" (first_name, last_name) VALUES (?1, ?2)'); assert.deepEqual(result.values, ['Fred', 'Flintstone']); }); it('should generate node-mysql style params', function() { var values = {'first_name': 'Fred', 'last_name': 'Flintstone'}; var result = insert('user', values).toParams({'placeholder': '?'}); assert.equal(result.text, 'INSERT INTO "user" (first_name, last_name) VALUES (?, ?)'); assert.deepEqual(result.values, ['Fred', 'Flintstone']); }); it('should output non-numeric params in SQL order', function() { var result = select().from('user').where($in(sql.val(5), [3, 5, 10])).toParams({'placeholder': '?'}); assert.equal(result.text, 'SELECT * FROM "user" WHERE ? IN (?, ?, ?)'); assert.deepEqual(result.values, [5, 3, 5, 10]); }); it('should properly parameterize subqueries', function() { var values = {'first_name': 'Fred'}; checkParams(select(select('last_name').from('user').where(values)), 'SELECT (SELECT last_name FROM "user" WHERE first_name = $1)', ['Fred']); }); it('should properly parameterize subqueries with a join', function() { var values = {'first_name': 'Fred'}; var query = select().from(select().from('user').where(values).as('subuser')) .join('other_table', {'other_table.id': 'subuser.id'}); checkParams(query, 'SELECT * FROM (SELECT * FROM "user" WHERE first_name = $1) subuser INNER JOIN other_table ON other_table.id = subuser.id', ['Fred']); }); it('should properly parameterize subqueries in updates', function() { var addr_id_for_usr = select('id').from('address').where('usr_id', sql('"user".id')).and('active', true); checkParams(update('user').set('addr_id', addr_id_for_usr), 'UPDATE "user" SET addr_id = (SELECT id FROM address WHERE usr_id = "user".id AND active = $1)', [true]) }); }); describe('value handling', function() { it('should escape single quotes when toString() is used', function() { check(update('user', {'name': "Muad'Dib"}), "UPDATE \"user\" SET name = 'Muad''Dib'"); }); it('should escape multiple single quotes in the same string', function() { check(update('address', {'city': "Liu'e, Hawai'i"}), "UPDATE address SET city = 'Liu''e, Hawai''i'"); }); it('should support sql.val() to pass in values where columns are expected', function() { check(select().from('user').where(sql.val('Fred'), sql('first_name')), "SELECT * FROM \"user\" WHERE 'Fred' = first_name"); }); describe('.toString() value conversions', function() { it('should, by default, convert arrays to SQL Array syntax', function() { check(update('user', {'name': ["Paul", "Muad'Dib"]}), "UPDATE \"user\" SET name = {'Paul', 'Muad''Dib'}"); }); it('should, by default, convert dates to SQL TIMESTAMP WITH TIME ZONE format', function() { check(update('user', {'birthdate': new Date('1980-01-01T00:00:00Z')}), "UPDATE \"user\" SET birthdate = TIMESTAMP WITH TIME ZONE '1980-01-01 00:00:00.000+00:00'"); }); it('should support user-supplied conversions', function() { var orig_bool = sql.conversions.Boolean; sql.conversions.Boolean = function(bool) { return bool ? '1' : '0'; }; var str = del('user').where('active', false).toString(); sql.conversions.Boolean = orig_bool; assert.equal(str, 'DELETE FROM "user" WHERE active = 0'); }); }); }); it('should expand abbreviations in FROM and JOINs', function() { check(select().from('usr').join('psn', {'usr.psn_fk': 'psn.pk'}), 'SELECT * FROM "user" usr INNER JOIN person psn ON usr.psn_fk = psn.pk'); }); it('should expand left_tbl on all joins', function() { var left_tbls = []; var orig = sql.joinCriteria(); sql.joinCriteria(function(left_tbl) { left_tbls.push(left_tbl); return orig.apply(this, arguments); }); select().from('test').join('psn').join('usr').toString(); sql.joinCriteria(orig); assert(left_tbls.indexOf('person') > -1 && left_tbls.indexOf('psn') == -1, 'left_tbl is not expanded: [' + left_tbls.join(',') + ']'); }); it('should support aliases', function() { check(select().from('user usr2').join('address addr2'), 'SELECT * FROM "user" usr2 INNER JOIN address addr2 ON usr2.addr_fk = addr2.pk'); }); it('should auto-generate join criteria using supplied joinCriteria() function', function() { check(select().from('usr').join('psn'), 'SELECT * FROM "user" usr INNER JOIN person psn ON usr.psn_fk = psn.pk'); }); it('should auto-generate join criteria to multiple tables', function() { check(select().from('usr').join('psn').join('addr'), 'SELECT * FROM "user" usr ' + 'INNER JOIN person psn ON usr.psn_fk = psn.pk ' + 'INNER JOIN address addr ON psn.addr_fk = addr.pk'); }); it('should auto-generate join criteria from a single table to multiple tables', function() { check(select().from('usr').join('psn', 'addr'), 'SELECT * FROM "user" usr ' + 'INNER JOIN person psn ON usr.psn_fk = psn.pk ' + 'INNER JOIN address addr ON usr.addr_fk = addr.pk'); }); it('should handle unions', function() { check(select().from('usr').where({'name': 'Roy'}) .union(select().from('usr').where({'name': 'Moss'})) .union(select().from('usr').where({'name': 'The elders of the internet'})), "SELECT * FROM \"user\" usr WHERE name = 'Roy'" + " UNION SELECT * FROM \"user\" usr WHERE name = 'Moss'" + " UNION SELECT * FROM \"user\" usr WHERE name = 'The elders of the internet'"); }); it('should handle chained unions', function() { check(select().from('usr').where({'name': 'Roy'}) .union().select().from('usr').where({'name': 'blurns'}), "SELECT * FROM \"user\" usr WHERE name = 'Roy'" + " UNION SELECT * FROM \"user\" usr WHERE name = 'blurns'"); }); it('should handle chained unions with params', function() { checkParams(select().from('usr').where({'name': 'Roy'}) .union().select().from('usr').where({'name': 'Moss'}), "SELECT * FROM \"user\" usr WHERE name = $1" + " UNION SELECT * FROM \"user\" usr WHERE name = $2", ['Roy', 'Moss']); }); it('should handle unions with params', function() { checkParams(select().from('usr').where({'name': 'Roy'}) .union(select().from('usr').where({'name': 'Moss'})) .union(select().from('usr').where({'name': 'The elders of the internet'})), 'SELECT * FROM "user" usr WHERE name = $1' + ' UNION SELECT * FROM "user" usr WHERE name = $2' + ' UNION SELECT * FROM "user" usr WHERE name = $3', ['Roy', 'Moss', 'The elders of the internet']); }); describe('UPDATE statements', function() { it('should handle .set() with (key, value)', function() { check(update('user').set('name', 'Fred'), "UPDATE \"user\" SET name = 'Fred'"); }); it('should handle .values() with an object literal', function() { check(update('user').values({'name': 'Fred'}), "UPDATE \"user\" SET name = 'Fred'"); }); it('should handle multiple .set()s with object literals', function() { check(update('user').set({'name': 'Fred'}).set({'last_name': 'Flintstone'}), "UPDATE \"user\" SET name = 'Fred', last_name = 'Flintstone'"); }); it('should handle multiple .values() with (key, value)', function() { check(update('user').values('name', 'Fred').values('last_name', 'Flintstone'), "UPDATE \"user\" SET name = 'Fred', last_name = 'Flintstone'"); }); it('should handle values argument', function() { check(update('user', {'name': 'Fred'}), "UPDATE \"user\" SET name = 'Fred'"); }); }); describe('INSERT statements', function() { it('should take an object of column/value pairs', function() { check(insert('user', {'id': 33, 'name': 'Fred'}), "INSERT INTO \"user\" (id, name) VALUES (33, 'Fred')"); }); it('should insert a null for undefined', function() { check(insert('user', {'id': 33, 'name': undefined}), "INSERT INTO \"user\" (id, name) VALUES (33, null)"); }); it('should insert a null for null', function() { check(insert('user', {'id': 33, 'name': null}), "INSERT INTO \"user\" (id, name) VALUES (33, null)"); }); it('should take an array of columns & values', function() { check(insert('user', ['id', 'name']).values([33, 'Fred']), "INSERT INTO \"user\" (id, name) VALUES (33, 'Fred')"); }); it('should take multiple parameters of columns & values', function() { check(insert('user', 'id', 'name').values(33, 'Fred'), "INSERT INTO \"user\" (id, name) VALUES (33, 'Fred')"); }); it('should take an array of objects', function() { check(insert('user', [{'id': 33, 'name': 'Fred'}, {'id': 34, 'name': 'Wilma'}]), "INSERT INTO \"user\" (id, name) VALUES (33, 'Fred'), (34, 'Wilma')"); }); describe('.values()', function() { it('should take an array of arrays', function() { check(insert('user', ['id', 'name']).values([[33, 'Fred'], [34, 'Wilma']]), "INSERT INTO \"user\" (id, name) VALUES (33, 'Fred'), (34, 'Wilma')"); }); }); describe('.into()', function() { it('should take an object of column/value pairs', function() { check(insert().into('user', {'id': 33, 'name': 'Fred'}), "INSERT INTO \"user\" (id, name) VALUES (33, 'Fred')"); }); it('should take an array of columns & values', function() { check(insert().into('user', ['id', 'name']).values([33, 'Fred']), "INSERT INTO \"user\" (id, name) VALUES (33, 'Fred')"); }); it('should take multiple parameters of columns & values', function() { check(insert().into('user', 'id', 'name').values(33, 'Fred'), "INSERT INTO \"user\" (id, name) VALUES (33, 'Fred')"); }); }); }); describe('SELECT clause', function() { it('should handle an array', function() { check(select(['one', 'order']).from('user'), 'SELECT one, "order" FROM "user"'); }); it('should handle multiple args', function() { check(select('one', 'order').from('user'), 'SELECT one, "order" FROM "user"'); }); it('should default to *', function() { check(select().from('user'), 'SELECT * FROM "user"'); }); it('should handle a comma-delimited str', function() { check(select('one, order').from('user'), 'SELECT one, "order" FROM "user"'); }); it('should handle being called multiple times', function() { check(select('one, order').select(['two', 'desc']).select('three', 'four').from('user'), 'SELECT one, "order", two, "desc", three, four FROM "user"'); }); it('should support DISTINCT', function() { check(select('one, order').distinct('two, desc').from('user'), 'SELECT DISTINCT one, "order", two, "desc" FROM "user"'); }); it('should support FOR UPDATE', function() { check(select().from('user').forUpdate(), 'SELECT * FROM "user" FOR UPDATE'); }); it('should support FOR UPDATE w/ col list', function() { check(select().from('user').forUpdate().of('user'), 'SELECT * FROM "user" FOR UPDATE OF "user"'); }); it('should support FOR UPDATE OF ... NO WAIT', function() { check(select().from('user').forUpdate().of('user').noWait(), 'SELECT * FROM "user" FOR UPDATE OF "user" NO WAIT'); }); }); describe('.from()', function() { it('should handle an array', function() { check(select().from(['one', 'two', 'usr']), 'SELECT * FROM one, two, "user" usr'); }); it('should handle multiple args', function() { check(select().from('one', 'two', 'usr'), 'SELECT * FROM one, two, "user" usr'); }); it('should handle a comma-delimited string', function() { check(select().from('one, two, usr'), 'SELECT * FROM one, two, "user" usr'); }); it('should handle being called multiple times', function() { check(select().from('one', 'usr').from(['two', 'psn']).from('three, addr'), 'SELECT * FROM one, "user" usr, two, person psn, three, address addr'); }); it('should handle subquery object', function() { check(select().from(select().from('user').as('subq')), 'SELECT * FROM (SELECT * FROM "user") subq'); }); it('should handle subquery string', function() { check(select().from('(SELECT * FROM "user") subq'), 'SELECT * FROM (SELECT * FROM "user") subq'); }); }); describe('select().into() should insert into a new table', function() { it('.into()', function() { check(select().into('new_user').from('user'), 'SELECT * INTO new_user FROM "user"'); }); it('.intoTable()', function() { check(select().intoTable('new_user').from('user'), 'SELECT * INTO new_user FROM "user"'); }); it('intoTemp()', function() { check(select().intoTemp('new_user').from('user'), 'SELECT * INTO TEMP new_user FROM "user"'); }); it('intoTempTable()', function() { check(select().intoTempTable('new_user').from('user'), 'SELECT * INTO TEMP new_user FROM "user"'); }); }); describe('insert().into() should insert into a preexisting table', function() { it('insert().into().select()', function() { check(insert().into('new_user', 'id', 'addr_id') .select('id', 'addr_id').from('user'), 'INSERT INTO new_user (id, addr_id) SELECT id, addr_id FROM "user"'); }); it('insert().into().select() with no columns', function() { check(insert().into('new_user') .select('id', 'addr_id').from('user'), 'INSERT INTO new_user SELECT id, addr_id FROM "user"'); }); it('insert().select()', function() { check(insert('new_user', 'id', 'addr_id') .select('id', 'addr_id').from('user'), 'INSERT INTO new_user (id, addr_id) SELECT id, addr_id FROM "user"'); }); it('insert().select() with params', function() { check(insert('new_user', 'id', 'addr_id') .select('id', 'addr_id').from('user').where({active: true}).toParams().text, 'INSERT INTO new_user (id, addr_id) SELECT id, addr_id FROM "user" WHERE active = $1'); }); }); describe('GROUP BY clause', function() { it('should support single group by', function() { check(select().from('user').groupBy('last_name'), 'SELECT * FROM "user" GROUP BY last_name'); }); it('should support multiple groupBy() args w/ reserved words quoted', function() { check(select().from('user').groupBy('last_name', 'order'), 'SELECT * FROM "user" GROUP BY last_name, "order"'); }); it('should support .groupBy().groupBy()', function() { check(select().from('user').groupBy('last_name').groupBy('order'), 'SELECT * FROM "user" GROUP BY last_name, "order"'); }); it('should support an array', function() { check(select().from('user').groupBy(['last_name', 'order']), 'SELECT * FROM "user" GROUP BY last_name, "order"'); }); }); describe('.order() / .orderBy()', function() { it('should support .orderBy(arg1, arg2)', function() { check(select().from('user').orderBy('last_name', 'order'), 'SELECT * FROM "user" ORDER BY last_name, "order"'); }); it('should support an array', function() { check(select().from('user').orderBy(['last_name', 'order']), 'SELECT * FROM "user" ORDER BY last_name, "order"'); }); it('should support being called multiple times', function() { check(select().from('user').orderBy('last_name').orderBy('order'), 'SELECT * FROM "user" ORDER BY last_name, "order"'); }); }); describe('joins', function() { it('.join() should accept a comma-delimited string', function() { check(select().from('usr').join('psn, addr'), 'SELECT * FROM "user" usr ' + 'INNER JOIN person psn ON usr.psn_fk = psn.pk ' + 'INNER JOIN address addr ON usr.addr_fk = addr.pk'); }); it('.leftJoin() should generate a left join', function() { check(select().from('usr').leftJoin('addr'), 'SELECT * FROM "user" usr ' + 'LEFT JOIN address addr ON usr.addr_fk = addr.pk'); }); it('.leftOuterJoin() should generate a left join', function() { check(select().from('usr').leftOuterJoin('addr'), 'SELECT * FROM "user" usr ' + 'LEFT JOIN address addr ON usr.addr_fk = addr.pk'); }); it('.rightJoin() should generate a right join', function() { check(select().from('usr').rightJoin('addr'), 'SELECT * FROM "user" usr ' + 'RIGHT JOIN address addr ON usr.addr_fk = addr.pk'); }); it('.rightOuterJoin() should generate a right join', function() { check(select().from('usr').rightOuterJoin('addr'), 'SELECT * FROM "user" usr ' + 'RIGHT JOIN address addr ON usr.addr_fk = addr.pk'); }); it('.fullJoin() should generate a full join', function() { check(select().from('usr').fullJoin('addr'), 'SELECT * FROM "user" usr ' + 'FULL JOIN address addr ON usr.addr_fk = addr.pk'); }); it('.fullOuterJoin() should generate a full join', function() { check(select().from('usr').fullOuterJoin('addr'), 'SELECT * FROM "user" usr ' + 'FULL JOIN address addr ON usr.addr_fk = addr.pk'); }); it('.crossJoin() should generate a cross join', function() { check(select().from('usr').crossJoin('addr'), 'SELECT * FROM "user" usr ' + 'CROSS JOIN address addr'); }); it('join() should accept an expression for the on argument', function() { check(select().from('usr').join('addr', eq('usr.addr_id', sql('addr.id'))), 'SELECT * FROM "user" usr INNER JOIN address addr ON usr.addr_id = addr.id'); }); it('join() should accept an array for the on argument (for JOIN USING)', function() { check(select().from('usr').join('addr', ['addr_id', 'country_id']), 'SELECT * FROM "user" usr INNER JOIN address addr USING (addr_id, country_id)'); }); }); describe('.on()', function() { it('should accept an object literal', function() { check(select().from('usr').join('addr').on({'usr.addr_id': 'addr.id'}), 'SELECT * FROM "user" usr ' + 'INNER JOIN address addr ON usr.addr_id = addr.id'); }); it('should accept a (key, value) pair', function() { check(select().from('usr').join('addr').on('usr.addr_id', 'addr.id'), 'SELECT * FROM "user" usr ' + 'INNER JOIN address addr ON usr.addr_id = addr.id'); }); it('can be called multiple times', function() { check(select().from('usr', 'psn').join('addr').on({'usr.addr_id': 'addr.id'}) .on('psn.addr_id', 'addr.id'), 'SELECT * FROM "user" usr, person psn ' + 'INNER JOIN address addr ON usr.addr_id = addr.id AND psn.addr_id = addr.id'); }); it('can be called multiple times w/ an object literal', function() { check(select().from('usr', 'psn').join('addr').on({'usr.addr_id': 'addr.id'}) .on({'psn.addr_id': 'addr.id'}), 'SELECT * FROM "user" usr, person psn ' + 'INNER JOIN address addr ON usr.addr_id = addr.id AND psn.addr_id = addr.id'); }); it('should accept an expression', function() { check(select().from('usr').join('addr').on(eq('usr.addr_id', sql('addr.id'))), 'SELECT * FROM "user" usr INNER JOIN address addr ON usr.addr_id = addr.id'); }); it('should not proceed if .using() has already been used', function() { assert.throws(function () { select().from('t1').join('t2').using('id').on({'t1.t2_id':'t2.id'}); }); }); }); describe('.using()', function() { it('should accept a comma-delimited string', function() { check(select().from('usr').join('addr').using('addr_id, contrived_id'), 'SELECT * FROM "user" usr ' + 'INNER JOIN address addr USING (addr_id, contrived_id)'); }); it('should accept multiple arguments', function() { check(select().from('usr').join('addr').using('addr_id', 'contrived_id'), 'SELECT * FROM "user" usr ' + 'INNER JOIN address addr USING (addr_id, contrived_id)'); }); it('should accept an array literal', function() { check(select().from('usr').join('addr').using(['addr_id', 'contrived_id']), 'SELECT * FROM "user" usr ' + 'INNER JOIN address addr USING (addr_id, contrived_id)'); }); it('should not proceed if .on() has already been used', function() { assert.throws(function () { select().from('t1').join('t2').on({'t1.t2_id':'t2.id'}).using('id'); }); }); }); describe('natural joins', function() { it('.naturalJoin() should accept a comma-delimited string', function() { check(select().from('usr').naturalJoin('psn, addr'), 'SELECT * FROM "user" usr ' + 'NATURAL INNER JOIN person psn ' + 'NATURAL INNER JOIN address addr'); }); it('.naturalLeftJoin() should generate a natural left join', function() { check(select().from('usr').naturalLeftJoin('addr'), 'SELECT * FROM "user" usr ' + 'NATURAL LEFT JOIN address addr'); }); it('.naturalLeftOuterJoin() should generate a natural left join', function() { check(select().from('usr').naturalLeftOuterJoin('addr'), 'SELECT * FROM "user" usr ' + 'NATURAL LEFT JOIN address addr'); }); it('.naturalRightJoin() should generate a natural right join', function() { check(select().from('usr').naturalRightJoin('addr'), 'SELECT * FROM "user" usr ' + 'NATURAL RIGHT JOIN address addr'); }); it('.naturalRightOuterJoin() should generate a natural right join', function() { check(select().from('usr').naturalRightOuterJoin('addr'), 'SELECT * FROM "user" usr ' + 'NATURAL RIGHT JOIN address addr'); }); it('.naturalFullJoin() should generate a natural full join', function() { check(select().from('usr').naturalFullJoin('addr'), 'SELECT * FROM "user" usr ' + 'NATURAL FULL JOIN address addr'); }); it('.naturalFullOuterJoin() should generate a natural full join', function() { check(select().from('usr').naturalFullOuterJoin('addr'), 'SELECT * FROM "user" usr ' + 'NATURAL FULL JOIN address addr'); }); }); describe('WHERE clauses', function() { it('should AND multiple where() criteria by default', function() { check(select().from('user').where({ 'first_name': 'Fred', 'last_name': 'Flintstone' }), "SELECT * FROM \"user\" WHERE first_name = 'Fred' AND last_name = 'Flintstone'"); }); it('should AND multiple where()s by default', function() { check(select().from('user').where({'first_name': 'Fred'}) .where({'last_name': 'Flintstone'}), "SELECT * FROM \"user\" WHERE first_name = 'Fred' AND last_name = 'Flintstone'"); }); it('should handle explicit .and() with (key, value) args', function() { check(select().from('user').where('first_name', 'Fred') .and('last_name', 'Flintstone'), "SELECT * FROM \"user\" WHERE first_name = 'Fred' AND last_name = 'Flintstone'"); }); it('should handle nested and(or())', function() { check(select().from('user').where(and({'last_name': 'Flintstone'}, or({'first_name': 'Fred'}, {'first_name': 'Wilma'}))), "SELECT * FROM \"user\" WHERE last_name = 'Flintstone' AND (first_name = 'Fred' OR first_name = 'Wilma')"); }); it('should handle or([...])', function() { check(select().from('table').where({ this: 'test' }).and(or(['test1', 'test2'].map(function(val) { return eq('that', val); }))), "SELECT * FROM \"table\" WHERE this = 'test' AND (that = 'test1' OR that = 'test2')"); }); it('and() should be implicit', function() { check(select().from('user').where({'last_name': 'Flintstone'}, or({'first_name': 'Fred'}, {'first_name': 'Wilma'})), "SELECT * FROM \"user\" WHERE last_name = 'Flintstone' AND (first_name = 'Fred' OR first_name = 'Wilma')"); }); it('should handle like()', function() { check(select().from('user').where(like('last_name', 'Flint%')), "SELECT * FROM \"user\" WHERE last_name LIKE 'Flint%'"); }); it('should accept a 3rd escape param to like()', function() { check(select().from('user').where(like('percent', '100\\%', '\\')), "SELECT * FROM \"user\" WHERE percent LIKE '100\\%' ESCAPE '\\'") }); it('should handle not()', function() { check(select().from('user').where(not({'first_name': 'Fred'})), "SELECT * FROM \"user\" WHERE NOT first_name = 'Fred'"); }); it('should handle in()', function() { check(select().from('user').where($in('first_name', ['Fred', 'Wilma'])), "SELECT * FROM \"user\" WHERE first_name IN ('Fred', 'Wilma')"); }); it('should handle .in() with multiple args', function() { check(select().from('user').where($in('name', 'Jimmy', 'Owen')), "SELECT * FROM \"user\" WHERE name IN ('Jimmy', 'Owen')"); }); it('should handle .in() with a subquery', function() { check(select().from('user').where($in('addr_id', select('id').from('address'))), 'SELECT * FROM "user" WHERE addr_id IN (SELECT id FROM address)'); }); it('should handle exists() with a subquery', function() { check(select().from('user').where(exists(select().from('address').where({'user.addr_id': sql('address.id')}))), 'SELECT * FROM "user" WHERE EXISTS (SELECT * FROM address WHERE "user".addr_id = address.id)'); }); it('should handle exists() with a subquery, parameterized', function() { checkParams(select().from('user').where('active', true).where(exists(select().from('address').where({'user.addr_id': 37}))), 'SELECT * FROM "user" WHERE active = $1 AND EXISTS (SELECT * FROM address WHERE "user".addr_id = $2)', [true, 37]); }); it('should handle isNull()', function() { check(select().from('user').where(isNull('first_name')), 'SELECT * FROM "user" WHERE first_name IS NULL'); }); it('should handle isNotNull()', function() { check(select().from('user').where(isNotNull('first_name')), 'SELECT * FROM "user" WHERE first_name IS NOT NULL'); }); it('should automatically generate IS NULL', function() { check(select().from('user').where({'first_name': null}), 'SELECT * FROM "user" WHERE first_name IS NULL'); }); it('should handle explicit equal()', function() { check(select().from('user').where(equal('first_name', 'Fred')), "SELECT * FROM \"user\" WHERE first_name = 'Fred'"); }); it('should automatically generate IS NOT NULL', function() { check(select().from('user').where(notEq('first_name', null)), "SELECT * FROM \"user\" WHERE first_name IS NOT NULL"); }); it('should handle lt()', function() { check(select().from('user').where(lt('order', 5)), 'SELECT * FROM "user" WHERE "order" < 5') }); it('should handle lte()', function() { check(select().from('user').where(lte('order', 5)), 'SELECT * FROM "user" WHERE "order" <= 5') }); it('should handle gt()', function() { check(select().from('user').where(gt('order', 5)), 'SELECT * FROM "user" WHERE "order" > 5') }); it('should handle gte()', function() { check(select().from('user').where(gte('order', 5)), 'SELECT * FROM "user" WHERE "order" >= 5'); }); it('should handle between()', function() { check(select().from('user').where(between('name', 'Frank', 'Fred')), "SELECT * FROM \"user\" WHERE name BETWEEN 'Frank' AND 'Fred'") }); it('should do nothing for null, undefined, {}', function() { check(select().from('user').where(), "SELECT * FROM \"user\""); check(select().from('user').where(null), "SELECT * FROM \"user\""); check(select().from('user').where({}), "SELECT * FROM \"user\""); check(select().from('user').where(undefined), "SELECT * FROM \"user\""); }); it('should not ignore the 2nd arg if the first is {}', function() { check(select().from('user').where({}, {'name': 'Fred'}), "SELECT * FROM \"user\" WHERE name = 'Fred'"); }); describe('customizations', function() { it('should support customized criteria', function() { check(select().from('table').where(arraysToOrs({ this: 'test', that: ['test1', 'test2']})), "SELECT * FROM \"table\" WHERE this = 'test' AND (that = 'test1' OR that = 'test2')"); }); it('should support changing the default array handling', function() { var proto = sql.select.prototype; var orig = proto.where; proto.where = function(criteria) { return orig.call(this, arraysToOrs(criteria)); }; check(select().from('table').where({ this: 'test', that: ['test1', 'test2']}), "SELECT * FROM \"table\" WHERE this = 'test' AND (that = 'test1' OR that = 'test2')"); proto.where = orig; }); function arraysToOrs(criteria) { var where = and(); for (var col in criteria) { var val = criteria[col]; var expr; if (_.isArray(val)) expr = or(val.map(function(val) { return eq(col, val); })); else expr = eq(col, val); where.expressions.push(expr); } return where; } }); }); describe('should quote column names with capitals in them', function() { it('in SELECT', function() { check(select('Name').from('user'), 'SELECT "Name" FROM "user"'); }); it('in SELECT, after tbl prefix, before AS suffix', function() { check(select('user.Name AS UserName').from('user'), 'SELECT "user"."Name" AS UserName FROM "user"'); }); it('in SELECT, after tbl prefix, with the optional "AS" omitted', function() { check(select('user.Name UserName').from('user'), 'SELECT "user"."Name" UserName FROM "user"'); }); it('in SELECT, with the optional "AS" omitted', function() { check(select('Name UserName').from('user'), 'SELECT "Name" UserName FROM "user"'); }); }); describe('should quote table names that are reserved words or have capitals', function() { it('in joins', function() { check(select('Name UserName').from('person').join('user', {'person.usr_id': 'user.id'}), 'SELECT "Name" UserName FROM person INNER JOIN "user" ON person.usr_id = "user".id'); }); it('unless they are wrapped in sql() literals (to query reserved system tables)', function() { check(select(sql('user.id')).from(sql('user')), 'SELECT user.id FROM user'); }); }); describe('should quote reserved words in column names', function() { it('in ORDER BY', function() { check(select().from('usr').orderBy('order'), 'SELECT * FROM "user" usr ORDER BY "order"'); }); it('in SELECT', function() { check(select('desc').from('usr'), 'SELECT "desc" FROM "user" usr'); }); it('in JOIN ONs', function() { check(select().from('usr').join('psn', {'usr.order': 'psn.order'}), 'SELECT * FROM "user" usr INNER JOIN person psn ON usr."order" = psn."order"'); }); it('in JOIN USINGs', function() { check(select().from('usr').join('psn').using('order'), 'SELECT * FROM "user" usr INNER JOIN person psn USING ("order")'); }); it('in INSERT', function() { check(insert('user').values({'order': 1}), 'INSERT INTO "user" ("order") VALUES (1)'); }); it('in alternative insert() API', function() { check(insert('user', 'order').values(1), 'INSERT INTO "user" ("order") VALUES (1)'); }); it('with a db and table prefix and a suffix', function() { check(select('db.person.desc AS psn_desc').from('person'), 'SELECT db.person."desc" AS psn_desc FROM person'); }); it('should quote sqlite reserved words', function() { check(select('action').from('user'), 'SELECT "action" FROM "user"'); }); it('should not quote reserved words in SELECT expressions', function() { check(select("CASE WHEN name = 'Fred' THEN 1 ELSE 0 AS security_level").from('user'), "SELECT CASE WHEN name = 'Fred' THEN 1 ELSE 0 AS security_level FROM \"user\""); }); }); describe('subqueries in <, >, etc', function() { it('should support a subquery in >', function() { var count_addrs_for_usr = select('count(*)').from('address').where({'user.addr_id': sql('address.id')}); check(select().from('user').where(gt(count_addrs_for_usr, 5)), 'SELECT * FROM "user" WHERE (SELECT count(*) FROM address WHERE "user".addr_id = address.id) > 5'); }); it('should support a subquery in <=', function() { var count_addrs_for_usr = select('count(*)').from('address').where({'user.addr_id': sql('address.id')}); check(select().from('user').where(lte(count_addrs_for_usr, 5)), 'SELECT * FROM "user" WHERE (SELECT count(*) FROM address WHERE "user".addr_id = address.id) <= 5'); }); it('should support = ANY (subquery) quantifier', function() { check(select().from('user').where(eqAny('user.id', select('user_id').from('address'))), 'SELECT * FROM "user" WHERE "user".id = ANY (SELECT user_id FROM address)'); }); it('should support <> ANY (subquery) quantifier', function() { check(select().from('user').where(notEqAny('user.id', select('user_id').from('address'))), 'SELECT * FROM "user" WHERE "user".id <> ANY (SELECT user_id FROM address)'); }); }); describe('deep Statement.clone()', function() { it('should deep clone WHERE expressions', function() { var sel = select().from('user').where({'first_name': 'Fred'}); sel.clone().where({'last_name': 'Flintstone'}); check(sel, "SELECT * FROM \"user\" WHERE first_name = 'Fred'"); }); it('should deep clone .order()', function() { var sel = select().from('user').order('name'); sel.clone().order('last_name'); check(sel, 'SELECT * FROM "user" ORDER BY name'); }); it('should deep clone .join()', function() { var sel = select().from('user').join('addr'); sel.clone().join('psn'); check(sel, 'SELECT * FROM "user" INNER JOIN address addr ON "user".addr_fk = addr.pk'); }); it('should clone values', function() { var ins = insert('user', {'first_name': 'Fred'}); ins.clone().values({'last_name': 'Flintstone'}); check(ins, "INSERT INTO \"user\" (first_name) VALUES ('Fred')"); }); }); describe('the AS keyword', function() { it('should not generate invalid SQL', function() { check(select().from('user AS usr').join('addr'), 'SELECT * FROM "user" AS usr INNER JOIN address addr ON usr.addr_fk = addr.pk'); }); }); describe('delete()', function() { it('should generate a DELETE statement', function() { check(del('user'), 'DELETE FROM "user"'); }); it('should support .from()', function() { check(del().from('user'), 'DELETE FROM "user"'); }); it('should generate a DELETE statement with a WHERE clause', function() { check(del('user').where('first_name', 'Fred'), "DELETE FROM \"user\" WHERE first_name = 'Fred'") }); }); describe('_extension()', function() { it('should shield base', function() { var ext = sql._extension(); ext.prop = 1; assert.equal(sql.prop, undefined); ext.select.prop = 1; assert.equal(sql.select.returning, undefined); assert(ext.select.prototype.clauses !== sql.select.prototype.clauses) }); }); }); function check(stmt, expected) { assert.equal(stmt.toString(), expected); } function checkParams(stmt, expectedSQL, expectedValues) { var result = stmt.toParams(); assert.equal(result.text, expectedSQL); assert.deepEqual(result.values, expectedValues); } })();