test('order by clauses', function* ()
{
yield TestUtils.getPlayer({ name: 'Sam', rating: 50 });
yield TestUtils.getPlayer({ name: 'Ann', rating: 50 });
yield TestUtils.getPlayer({ name: 'Fred', rating: 25 });
var sql = Sql.fromComment(function ()
{/*!
select
p.*
from
players p
--[SqlBuilder: order by]
*/
});
var builder = new SqlBuilder(),
template = builder.newTemplate(sql);
builder.orderBy('p.rating');
var q = yield template.query();
assert.deepEqual(q.rows.map(function(row) { return row.rating; }), [25, 50, 50]);
builder.orderBy('p.name desc');
q = yield template.query();
assert.deepEqual(q.rows.map(function(row) { return row.name; }), ['Fred','Sam','Ann']);
});
test('simplified single template with initial @parameters', function*()
{
var p = yield TestUtils.getPlayer({ name: 'Sam', rating: 50 });
yield TestUtils.getPlayer({ name: 'Ann', rating: 50 });
yield TestUtils.getPlayer({ name: 'Fred', rating: 25 });
var sql = Sql.fromComment(function ()
{/*!
select
p.id
from
players p
where
p.name = @name
and p.rating > @rating
*/
});
var b = new SqlBuilder(sql, { name: 'Sam', rating: 40 }),
q = yield b.query(),
r = q.rows[0];
assert.equal(r.id, p.id);
});
test('limit clauses', function*()
{
yield TestUtils.getPlayer();
yield TestUtils.getPlayer();
yield TestUtils.getPlayer();
var sql = Sql.fromComment(function ()
{/*!
select
p.*
from
players p
--[SqlBuilder: limit]
*/
});
var builder = new SqlBuilder(),
template = builder.newTemplate(sql);
builder.limit('@two', { two: 2 });
var q = yield template.query();
assert.equal(q.rows.length, 2, 'should only have two rows');
});
test('where clauses', function* ()
{
yield TestUtils.getGame();
var sql = Sql.fromComment(function ()
{/*!
select
count(*) as game_count
from
games g
--[SqlBuilder: where]
*/
});
var builder = new SqlBuilder(),
template = builder.newTemplate(sql);
builder.where('g.id > 0');
var q = yield template.query();
assert.equal(q.rows[0].game_count, 1, 'incorrect count after one where clause');
builder.where('g.match_id is null');
q = yield template.query();
assert.equal(q.rows[0].game_count, 1, 'incorrect count after two where clauses');
// now the TOTALLY AWESOME, WHY DON'T YOU HAVE IT, POSTGRES? @parameter syntax
builder.where('g.game_type_id = @singles', { singles: Game.types.singles });
q = yield template.query();
assert.equal(q.rows[0].game_count, 1, 'incorrect count after three where clauses');
var lastYear = new Date();
lastYear.setFullYear(lastYear.getFullYear() - 1);
builder.where('g.created_date > @lastYear', { lastYear: lastYear });
q = yield template.query();
assert.equal(q.rows[0].game_count, 1, 'incorrect count after four where clauses');
});