Creating a custom dialect to change LIMIT to TOP

[vjpr]

I need to query an new ActiveXObject("ADODB.Connection") data source which requires using TOP instead of LIMIT.

select * from table limit 5 becomes select top 5 from table.

What is the best way to do this from a custom dialect?

[vjpr]

Say I have this:

BaseDialect = require('gesundheit/lib/dialects').base

class @MSAccessDialect extends BaseDialect

  renderLimit: (node) ->
    'hello hello'

and then in dialects.js I add a console.log:

    Visitor.prototype.compile = function(node, allowOverride) {
      var custom, name, string, _ref1, _ref2;
      if (allowOverride == null) {
        allowOverride = true;
      }
      this.path.push(node);
      name = node != null ? (_ref1 = node.__proto__) != null ? (_ref2 = _ref1.constructor) != null ? _ref2.name : void 0 : void 0 : void 0;

      console.log (name, this.dialect.renderLimit) // < ----------------------------

      if (allowOverride && name && (custom = this.dialect['render' + name])) {
        string = custom.call(this, node);
      } else {
        string = node.compile(this, this.path);
      }
      this.path.pop(node);
      return string;
    };

This is what is printed:

SelectQuery function (node) {
      return 'hello hello';
    }
Select undefined
SelectColumnSet undefined
RelationSet undefined
Relation undefined
Join undefined
JoinType undefined
ValueNode undefined
Relation undefined
ValueNode undefined
And undefined
Binary undefined
Column undefined
Relation undefined
Field undefined
Column undefined
Relation undefined
Field undefined
Where undefined
TextNode undefined
Limit undefined

---

this.dialect is being reset to MySQLDialect.

I should also mention that I am using it like this:

    {MSAccessDialect} = require 'config/initializers/gesundheit/dialects'
    dialect = new MSAccessDialect
    q = dialect.compile(q)[0]
   // q is sent to database connection for running...

---

Here is the stack trace from new Visitor(). Seems I shouldn't be using a dialect to compile, rather an engine.

  at new Visitor (xxx/node_modules/gesundheit/lib/dialects.js:102:15)
  at MySQLDialect.BaseDialect.compile (xxx/node_modules/gesundheit/lib/dialects.js:30:15)
  at Engine.compile (xxx/node_modules/gesundheit/lib/engine.js:98:25)
  at SelectQuery.module.exports.BaseQuery.compile (xxx/node_modules/gesundheit/lib/queries/base.js:106:24)
  at Visitor.compile (xxx/node_modules/gesundheit/lib/dialects.js:120:23)
  at MSAccessDialect.BaseDialect.compile (xxx/node_modules/gesundheit/lib/dialects.js:31:20)

[vjpr]

Ok so a workaround I came to was:

    {MSAccessDialect} = require 'config/initializers/gesundheit/dialects'
    dialect = new MSAccessDialect
    fakePool =
      begin: (cb) ->
        if cb then process.nextTick(cb.bind(null, engine))
        engine
      query: (sql, params, cb) ->
        throw new Error("Cannot query with fakeEngine. Do `gesundheit.defaultEngine = gesundheit.engine(url)` before querying")
      close: ->
    engine = new Engine null, null, fakePool, dialect
    q = engine.compile(q)[0]

But unfortunately Engine is not exported, only the factory Engine#create which does not support custom 3rd party engines.

Engine#create should take another parameter to specify a custom dialectType class.

[vjpr]

Ok the problem was that I was using:

g = require 'gesundheit'
db = g.engine('mysql://localhost/test')
g.defaultEngine = db

...and then building the query using g.select(...).

It seems like with this approach queries may be too tightly bound to the engine. It would be good to be able to create queries and then run them on multiple engines when required.

I'm not sure its a real problem though - just too me a while to track down. And I do have a unique use case.

[grncdr]

You can rebind queries with query.bind(engine) but this is not documented or actually supported (e.g. I may remove it in the future).

-Stephen

On 10 November, 2013 at 10:33:51 PM, Vaughan Rouesnel (notifications@github.com) wrote:

Ok the problem was that I was using:

g = require 'gesundheit'
db = g.engine('mysql://localhost/test')
g.defaultEngine = db

...and then building the query using g.select(...).

It seems like with this approach queries may be too tightly bound to the engine. It would be good to be able to create queries and then run them on multiple engines when required.

—
Reply to this email directly or view it on GitHub.

[vjpr]

Cheers. From the original question: What is the best way to change the order of the Limit node?

[vjpr]

I think I found it - just have to modify _nodeOrder.

  renderSelect: (node) ->
    console.log node.constructor
    node.constructor._nodeOrder = [
      'limit',
      'distinct',
      'projections',
      'relations',
      'where',
      'groupBy',
      'having',
      'orderBy',
      'offset'
    ]

    node.compile this, true

[grncdr]

hm, that works, but it's rather ugly. I would definitely be interested in a patch that moves the ordering of Statement child-nodes into BaseDialect. It really does make more sense for the ordering of the output to be controlled by the dialect than the node itself.