betsmartmedia / gesundheit Goto Github PK

AbstractAlias doesn't handle query parameters/placeholders. It's easy enough to demonstrate, like so:

var Sql = require('gesundheit');
var q1 = Sql.select('table1');
q1.field(Sql.text('count(if(col1=$0, 1, null))', ['test']));
console.log(q1.compile());
// Output: [ 'SELECT count(if(col1=?, 1, null)) FROM table1', [ 'test' ] ]
var q2 = Sql.select('table2');
q2.field(Sql.text('count(if(col1=$0, 1, null))', ['test']).as('foo'));
console.log(q2.compile());
// Output: [ 'SELECT (count(if(col1=?, 1, null))) AS foo FROM table2', [] ]

Note that the first has a complete set of parameters and the second does not.

Something like this would do the trick:

params: -> if @obj.params then @obj.params(d) else []

This should be as simple as where({column_name: null}), but I just spent 5 minutes constructing this monstronsity to make it work:
this.where(this.project('table', 'column_name').compare('IS', g.NULL))

Travis CI has MySQL, Postgres, and SQLite3 available, so there should be integration tests against all of them.

Hi,
I have found one more issue

    var g = qbuilder;
    var subselect = g.select('t1', [g.text('1')]).where({id: 3});
    var source = g.select('t1', [g.text("3, 'C', 'Z'")])
        .where(g.notExists(subselect));
    var ins = g.insert('t1', ['id', 'f1', 'f2']).from(source);
    console.log(ins.compile());

   ---> results 
[ 'INSERT INTO "t1" ("id", "f1", "f2") SELECT 3, \'C\', \'Z\' FROM "t1" WHERE NOT EXISTS (SELECT 1 FROM "t1" WHERE "t1"."id" = $1)',
  [ 3 ] ]

This is not a valid Postgres query because of SELECT 3, \'C\', \'Z\' FROM "t1" shouldn't contain FROM "t1", otherwise it gives a duplication error.
I have also tried to change t1 to another table name to try avoiding conflicts but nothing changes. If I remove from clause it works perfectly.

Thankyou,
Bdk

Is there a way to render the SQL string with substituted parameters, i.e. the string that will be sent to the db? I'm using sequelize and they don't support raw query parameter substitution.

This would also be advantageous for logging.

Add a node type that is a raw string + parameters. Similar to the SQL node type from Storm ORM.

From Brian Johnson:

Is there a way to build a query like this:

SELECT * FROM t WHERE (a = 1 OR (b = 2 AND c = 3))

I can't seem to find an example in the tests or the documentation.

Using MySQL, 'name' is not a keyword, but it is listed in the sql_keywords.txt file. This causes SQL to be generated as such:

q = db.update('users');
q.set(safeUser);
q.where({id: user.id});
console.log(q.compile());

'UPDATE users SET "name" = ?, email = ?, mobile = ? WHERE users.id = ?',

The quotation marks around the name are invalid MySQL syntax.

MySQL uses backticks to escape column names.

Was looking to create a complex SQL and seems that I need to use the nodes module but the link on gesundheit's docs: http://betsmartmedia.github.io/gesundheit/#select which links to http://betsmartmedia.github.io/gesundheit/Nodes is returning 404.

Hi,

I find myself writing all the same the table name beside the field name when joining.

It will be great if I can turn this on.

fields: [{car_owner_name: 'name'}, {car_owner_email: 'email'}, {car_owner_phone_number: 'phone_number'}]}

fields: [{car_info_make: 'make'}, {car_info_make: 'model'}]

etc.

select.join('car_owner',.... fields: ['name'], fieldWithTableName: true});

car_owner_name

What do you think? Good or bad?

Is there support for HAVING clauses (for GROUP BY)? I didn't see it. If not, what is the cleanest workaround? Thanks!

[Moved to a new issue]

(I have removed my comment from issue #21 and added a new one here, with more details on the error)

Sorry,
I have another issue using parameters and text.
Mixing text function and bindVals in subqueries I obtained following result:

    var g = qbuilder;
    var subselect = g.select('t1', [g.text('1')]).where({id: 3, f2: 'Z', k: 1, l: 2, p: 9, a: 3});
    var source = g.select('t1', [g.text("3, 'C', 'Z'")])
        .where(g.notExists(subselect));
    var ins = g.insert('t1', ['id', 'f1', 'f2']).from(source);
    console.log(ins.compile());

   ---> results 
   [ 'INSERT INTO "t1" ("id", "f1", "f2") SELECT $1,$2,$3 FROM "t1" WHERE NOT EXISTS (SELECT 1 FROM "t1" WHERE "t1"."id" = $1 AND "t1"."f2" = $2 AND "t1"."k" = $3 AND "t1"."l" = $4 AND "t1"."p" = $5 AND "t1"."a" = $6)',
  [ ] ]

There are no binded values, but there should be values from where statement in subquery.
Is this due to a misunderstunding of mine in using text function or it is a real issue?

Thankyou very much for your support

If I try to copy a query with q.where(text(...)), gesundheit fails with "Don't know how to copy [object Object]". It seems like this case should not present a problem. (text() is sometimes unavoidable, e.g. for the postgres regex operator ~ or for various other unusual cases)

What is the best way to do a select query with the FOR UPDATE clause?

Thanks,
Seth

It's currently not possible to do a recursive join such as SELECT foo1.bar FROM foo foo1, foo foo2 WHERE foo1.baz = foo2.baz. You can't add foo1 or foo2 as a join, since the other relation doesn't exist yet. I've implemented this with the following code -- not submitting as a pull request because I'm not sure this is really the right way to do it, plus there's a namespace clash issue with Select. But maybe this will lead to a proper solution.

SelectQuery.prototype.select = function(table, fields) {
  var rel = toRelation(table);

  if (!this.q.relations.get(rel.ref(), false)) {
    this.q.relations.addNode(new Select(rel));

    if (fields) {
      this.fields.apply(this, fields);    
    }
  }

  return this;
};

Select = (function(_super) {
  __extends(Select, _super);

  function Select(relation) {
    var nodes;
    this.relation = relation;
    nodes = [this.relation];
    Select.__super__.constructor.call(this, nodes);
  }

  Select.prototype.ref = function() {
    return this.relation.ref();
  };

  Select.prototype.render = function(dialect) {
    return ", " + Select.__super__.render.apply(this, arguments);
  };

  return Select;

})(nodes.FixedNodeSet);

Using this I can now write the above query as follows:

var q = db.select({foo1: 'foo'}, ['bar']}).select({foo2: 'foo'});
q = q.where(q.p('foo1','baz').eq(q.p('foo2','baz'));

in my codebase, i have the following code as part of building a particular query. it worked fine in the past. after picking up the latest drop of gesundheit (0.7.9; i was on 0.7.4 previously), it broke.

.having(db.text('min(job.job_state_id) >= $state', {state:queue.jobState.succeeded}));

the whole object {state:val} gets passed as the parameter to the database, instead of dereferencing by name.

using params by ordinal still works ok (at this for this case it did):

.having(db.text('min(job.job_state_id) >= $0', [queue.jobState.succeeded]));

see #9 -- would be awesome to have RETURNING support for UPDATE and DELETE as well.

A couple of thoughts on how to support transactions

A "transaction" object that delegates to insert/select/update/delete ensuring that all queries are bound to the same connection. It would be used something like this:

transaction(function (err, t) {
  t.select(...)
  ...
  t.commit() // or t.rollback()
}

Need to see if it makes sense to try and attach this to domains to guarantee rollback.

I build my queries dynamic, so I don't have reference to the table.

Example
.join("users", {
on: {id: light_recliners.column('table.id')},
fields: ['name']
})

This work, but if I change

light_recliners.column('table.id')
to
'table.id'

Then the SQL that is generated is something like

[ 'SELECT "....ner" ON ("users"."id" = $1)',
[ 'table.id ] ]

throws error [message: "invalid input syntax for integer: 'tableid'"]

Please forgive me for not fully understanding the node structure, but how might I go about adding a RETURNING statement to insert or update statements. Is a better way to do it than this:

gesundheit.insert 'shirts', { "foo": "bar"}, -> 
   @q.returning.nodes.push('*')
.compile()

# [ 'INSERT INTO shirts (foo) VALUES (?) RETURNING *', 
# [ 'bar' ] ]

...I assume there is a better way.

It would be great to support IN clauses, with variable numbers of parameters. For example, if i pass an array of values, maybe something like this:

q.where({empId:{in:[17, 45, 92]}})

it would generate the parameterized SQL:

WHERE empId IN ($1, $2, $3)

Can we remove that please? thanks!

If the last argument to a query factory is a function, it should be passed to this.visit to enable slick query construction like so:

select('table', ['column1', 'column2'], function () {
  this.join('other_table', {on: {table_id: this.project('table', 'id')}})
})

The sqlite3 module uses prepared statements even for INSERT statement.
The following statement is issued when calling addRows or calling addRow multiple times:

INSERT INTO foo (id, foo, bar) VALUES (?, ?, ?), (?, ?, ?)

Sqlite rightfully refuses to compile this as the second set of placeholder is redundant with the concept of prepared statements.
As of now sqlite3 does not provide another way to query the database, the only to fix I can see is to compile the previous query to

INSERT INTO foo (id, foo, bar) VALUES (?, ?, ?)

And to run it n times, n being the number of rows to insert.

PS: This issue my arise with other type of queries.

This works.

q.where(q.c('job.offers_count').lte(2).and(g.text('job.offers_count IS NULL')));

but this doesn't work.

q.where(q.c('job.offers_count').lte(2).or(g.text('job.offers_count IS NULL')));

ReferenceError: args is not defined

What do you think :) ?

Hi,

How do I use the pooling object with gesundheit object?

var connection = anyDB.createPool(dbURL, {min: 2, max: 40});
gesundheit.defaultEngine = connection;

is not working...?

Thank you for your help :)

It would be nice to have arbitrary support for ordering by text node, e.g. to order by a postgres hstore value. As far as I can tell this is just a simple addition to sud.js.

Is there a way to do this?

(btw, gesundheit plus anyDB is a very capable stack, thanks for making it available!)

Sorry for asking this outright, but I'm having the darndest time trying to get transactions working. I've been cross-referencing the any-db docs, but still can't seem to actually get the thing commited (I'm able to view the changes within the transaction's connection--as the tests test--but not actually seeing them stick when I requery the database outside the transaction). Below is the code that I feel like should work, but the commit() statement at the end is throwing an error Cannot call method 'query' of undefined:

var gesundheit = require('gesundheit');
var db = gesundheit.engine(conString, {min: 2, max: 5});

// Take an array of new shirt objects as an argument
// Set all existing shirts in the table to `today = false`
// Set the new shirts to be `today = true`
function setToday(shirts, callback) {
  var vendorId = shirts[0].vendor; // all shirts should come from the same vendor
  var shirtIds = _.pluck(shirts, 'id');

  var shirts; // store the updated shirts between async operations

  // begin our transaction
  var tx = db.begin();
  tx.on('error', function(err) {
    callback(err);
  });

  //set all the shirts' today to false
  tx.update('shirts')
  .set({ today: false })
  .where({ 
    vendor: vendorId,
    today: true
  }).execute();

  // set the new shirts today to true, returning them
  tx.update('shirts', function() { 
    this.q.returning.nodes.push('*'); 
  })
  .set({ today: true })
  .where({ 
    id: { in: shirtIds }
  }).execute(function(err, result) {
    // store the results
    shirts = result.rows;
  });

  tx.commit(function(err) {
    if (!err) {
      callback(null, shirts); // pass the new shirts on to the callback
    }
  });
}

What am I doing wrong? Thanks, and sorry for the huge block of code.

When using a default engine, it would be great to be able to do gesundheit.query() similar to how you can do .select, .insert, etc. Right now you have to do gesundheit.defaultEngine.query().

select.js line 171 is a "debugger;" statement; this is causing the debugger to of course trap there every time it executes from my calling code, which makes my own debugging more painful.

Can we remove this? Thanks!

Hi,

first of all I find Gesundheit the most powerful query builder of node.
I have a problem using it with Postgres, because of missing quotes for CamelCase table names.

I have a table named Accounts that should be quotes as "Accounts" in a INSERT query, but in BaseDialect quotation is generic for all dbs and there is no override for Postgres dialect.

How can I solve this?
I'm using this ovverriding method as a temporary solution

PostgresDialect.prototype.quote = function(s) {
    if (s != null) {
      return '"' + s.replace(new RegExp('"', 'g'), '\\"') + '"';
    } else {
      return s;
    }
};

and modified also BaseDialog.renderString to force quotation

  BaseDialect.prototype.renderString = function(s) {
    return this.quote(s);
  };

I hope in a better solution from you :-)

I think that there is an issue in BaseDialect.quote method.
This line:

return '"' + s.replace('"', '\\"') + '"'; // only replaces first occurrence of " character.

It should be replaced by

return '"' + s.replace(new RegExp('"', 'g'), '\\"') + '"';

Thankyou,

Bdk

Hello,
is there any way to build following query?

INSERT INTO table (id, field, field2)
       SELECT 3, 'C', 'Z'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

I've not found any solution for this.

Thankyou,
Bind

As far as I can tell, it's currently not possible to order by SQL functions, e.g. ORDER BY lower(col). This would be really useful if it could be supported.

It is unfortunate that different database engines implement the "get_last_insert_id()" function in very different ways. The only engine that currently supports this with the query generation API is Postgresql, via the returning() clause. For the others it is necessary to send a hardcoded SQL query, because it seems these cannot be generated via the select() function.

Would you consider it within the scope of your project to create an engine agnostic function to retrieve this id?

I could come up with two possible implementations:

1. Using the returning() clause. Postgresql already has it, sqlite3 and MySQL will need this clause simulated with a SELECT query.
2. Return the last inserted id in the callback. Not sure how this would work when inserting multiple rows at once, maybe it only works when inserting one.

Let me know what you think!

In queries that support project, it should also be aliased as 'p'

Some libraries use ?, while it seems this lib only support $n.

For sql query string generation use case.

I need to return a runtime-dependent set of columns from some queries, built via .returning() or .fields(). These two functions take a variable length list of args today, one arg per column name.

I may be misunderstanding, but it looks like that would make me have to use .apply() to put a variable length set of column names in, which is clunky.

It would be sweet to have these be able to take an array of column names directly. e.g., .returning(['id', 'name', 'cost']). Or is there another way around this?

Thanks for all your work on this Stephen!
Seth

Hi there,

First of all, sorry but I can't provide a pull request for this despite it being a simple typo. I cannot fork the repo for some reason.

Anyway src/engines.coffee:91:6 calls EventEmitter, but it is not required in this file. You need to add

EventEmitter = require('events').EventEmitter

to fix that

Documentation needs to be updated in a few places as project is now deprecated.

DEPRECATED:  project has been renamed to .column or .c and will be removed in a future release.

men_with_light_recliners = select('chairs', ['chair_type', 'size'], ->
  @where chair_type: 'recliner', weight: {lt: 25}
  @join "people",
    on: {chair_id: @project('chairs', 'id')},
    fields: ['name']
  @where gender: 'M'
  @focus 'chairs'
  @order weight: 'ASC
  @limit 5

...and a few more.

Hi,

How would I create

column IS NOT NULL

Do I use the text node?

It would be cool with...

q.where(q.c('column').is(NULL));
q.where(q.c('column').is_not(NULL));

Any example on:

column IS NOT NULL

Thank you :D

What is the best way to write this query:

SELECT id 
  FROM groups
 WHERE LOWER(name)=LOWER('Administrator')

I tried doing the following query:

db.insert(table, cols).addRows(rows);

But I got the error:

Error: Wrong number of values in array, expected [object Object],[object Object],[object Object]

rows is an array of objects with the columns specified as keys. So as far as I can tell it should work. Something seems to be going wrong with:

rows = 1 <= arguments.length ? __slice.call(arguments, 0) : [];

In 0.7.9 it looks like the .agg() and .fun() methods are no longer returning the query object, so you can't chain other calls after them. This was definitely working on 0.7.4.

For example, this throws an error now, on the second line:

var q = db.select(tableName);
q.agg('sum', [q.p(valueColumnName)]).where({and:[q.p(filterColumnName).eq(filterValue), {datadate:{gte:dateStart}}, {datadate:{lt:dateEnd}}]});

This works ok:

var q = db.select(tableName);
q.agg('sum', [q.p(valueColumnName)]);
q.where({and:[q.p(filterColumnName).eq(filterValue), {datadate:{gte:dateStart}}, {datadate:{lt:dateEnd}}]});

What might be the cleanest way for me to wrap gesundheit (without forking) so that every time a query is executed, i can see the compiled sql string plus parameters (so that i can output them into my log for example to aid debugging)?

I could try to wrap the execute() call, but because that is a function on the query object, which comes from several places (main obj, transaction, ...) at first glance i would have to patch several additional calls too i think. Sorry, just wrapping my head around the codebase still.

thanks!

.project() is still called various places in gesundheit, which means that lots of deprecation warnings are emitted even if the caller's code always uses q.c(). For example:

var q = db.select(table, [a,b,c]);

will emit a deprecation warning for each column.

It's not currently possible to do queries such as SELECT * FROM foo(bar). I tried using sqlFunction for this, but it can't be used as a relation and so doesn't work. It would be great to have support for this -- it's a common use case in my code.

There is a common use situation in my code where I need to do queries of the form SELECT * FROM foo WHERE (bar,baz) IN (($1,$2),($3,$4),...). It's fine to generate the bound parameter side, but the only way currently to generate (bar,baz) is to call sqlFunction with a function of '' -- this works, so I suppose that's OK, but it would be nice to have proper support for tuples.

I saw here: https://github.com/BetSmartMedia/gesundheit/blob/master/src/queries/base.coffee#L70 that the BaseQuery is already an EventEmitter

Can it be made such that events are emitted before and after query execution? The arguments can just be the query object itself.

This would be extremely useful for two things:

• Logging, obviously
• Performance timing/tuning and...
• Debugging problematic statements. (i.e. which query in that 10-statement transaction caused that error?)

Did this using my own wrapper for the moment (with some caveats, of course) but this definitely belongs in gesundheit and not in my app code.

Will try to do a PR if time allows, but definitely not in the near future, thus this request : )

I am using a different library to execute against a db, only using this lib to generate strings at the moment.

Is there a way to specify a dialect to use when compiling query strings without specifying the url?