{"action":"created","issue":{"url":"https://api.github.com/repos/goodybag/mongo-sql/issues/105","repository_url":"https://api.github.com/repos/goodybag/mongo-sql","labels_url":"https://api.github.com/repos/goodybag/mongo-sql/issues/105/labels{/name}","comments_url":"https://api.github.com/repos/goodybag/mongo-sql/issues/105/comments","events_url":"https://api.github.com/repos/goodybag/mongo-sql/issues/105/events","html_url":"https://github.com/goodybag/mongo-sql/issues/105","id":39497090,"number":105,"title":"Proposal for multiple dialect support","user":{"login":"sudhakar","id":6782,"avatar_url":"https://avatars.githubusercontent.com/u/6782?v=3","gravatar_id":"","url":"https://api.github.com/users/sudhakar","html_url":"https://github.com/sudhakar","followers_url":"https://api.github.com/users/sudhakar/followers","following_url":"https://api.github.com/users/sudhakar/following{/other_user}","gists_url":"https://api.github.com/users/sudhakar/gists{/gist_id}","starred_url":"https://api.github.com/users/sudhakar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sudhakar/subscriptions","organizations_url":"https://api.github.com/users/sudhakar/orgs","repos_url":"https://api.github.com/users/sudhakar/repos","events_url":"https://api.github.com/users/sudhakar/events{/privacy}","received_events_url":"https://api.github.com/users/sudhakar/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":10,"created_at":"2014-08-05T08:43:59Z","updated_at":"2016-11-09T19:16:38Z","closed_at":null,"body":"Currently mosql has excellent support for pgsql covering most of the pgsql statements (which again is close to ANSI standard). It also provides helper methods to expand on the pgsql dialect. So new query-types, helper-methods & conditionals can be added to expand mosql capabilities (which BTW is a awesome feature).\n\nMy usecase for mosql is to use it for other DBs like MySQL, Vertica, HiveQL & Aerospike. It is possible to add additional query-types using `mosql.queryTypes.add`, but unfortunately it gets added to the global namespace. \n\nI am not an expert on SQL's DSL, but from what I have explored it looks like these DBs support either subset of query-types or a superset with additional query-type. So if I want to add a `select` query type for HiveQL, then I may have to prefix it as `hive-select` & so on. Also features like `row_to_json` are [specific to pgsql](https://blogs.oracle.com/svetasmirnova/entry/mysql_and_postgresql_json_functions) & its error to use it in mysql or in other dialects. It would be nice if each dialect is placed in their own namespace, so that all SQL like dialects can be supported easily.\n\nTo accommodate this, I propose following api changes.\n1. Allow user to specify the dialect while importing mosql like\n   \n   ``` js\n   \n   var pgsql = require('mongo-sql')('pg');\n   pgsql.registerQueryType(\n     'select-one-user'\n   , 'select {columns} from users {joins} {where} limit 1'\n   );\n   ```\n   \n   This will add `'select-one-user'` to pgsql dialect\n2. Throw an exception if either requested query-type or helper-method is not registered for that dialect.\n3. Allow query-types, helpers & conditionals in each dialect to introspect itself. For example,\n   \n   ``` js\n     var pgsql = require('mongo-sql')('pg');\n   \n     // return list of all supported query-types\n     pgsql.queryTypes.list\n   \n     // return list of all supported query-helpers\n     pgsql.queryHelpers.list \n   \n     // return list of query-helpers supported by 'alter-table' \n     // ie ['ifExists', 'only', 'table', 'action']\n     pgsql.queryTypes.get('alter-table').queryHelpers \n   ```\n   \n   This will allow other libraries to interactively suggest the query-helpers based on query-type.\n4. Move `helpers` directory to `lib/pg-helpers` & `index.js` to `lib/mosql.js` (So that gulp configuration can be simplified). New helpers can then be added to `lib/hive-helpers` & so on.\n\nI am yet to get fully accustomed with codebase. So feel free to suggest changes & alternate approaches. Would love to hear your thoughts on this.\n"},"comment":{"url":"https://api.github.com/repos/goodybag/mongo-sql/issues/comments/259500141","html_url":"https://github.com/goodybag/mongo-sql/issues/105#issuecomment-259500141","issue_url":"https://api.github.com/repos/goodybag/mongo-sql/issues/105","id":259500141,"user":{"login":"wmertens","id":54934,"avatar_url":"https://avatars.githubusercontent.com/u/54934?v=3","gravatar_id":"","url":"https://api.github.com/users/wmertens","html_url":"https://github.com/wmertens","followers_url":"https://api.github.com/users/wmertens/followers","following_url":"https://api.github.com/users/wmertens/following{/other_user}","gists_url":"https://api.github.com/users/wmertens/gists{/gist_id}","starred_url":"https://api.github.com/users/wmertens/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/wmertens/subscriptions","organizations_url":"https://api.github.com/users/wmertens/orgs","repos_url":"https://api.github.com/users/wmertens/repos","events_url":"https://api.github.com/users/wmertens/events{/privacy}","received_events_url":"https://api.github.com/users/wmertens/received_events","type":"User","site_admin":false},"created_at":"2016-11-09T19:16:38Z","updated_at":"2016-11-09T19:16:38Z","body":"I suppose that a truly generic solution would be a convertor between a (simple) AST and SQL. Then each dialect can define the grammar of the dialect, and most json representations would be very similar. So no effort would be made to present a common language, but most SQL would look the same anyway. You could even write SQL, have it get converted to AST and pass it to other code that augments the SQL somehow before converting it back.\r\n\r\nI say simple AST because whitespace and exact quoting is not important. `SELECT count(*) as \"c\" FROM bar;` would maybe become `{type: \"select\", columns: [{type: \"fn\", name: \"count\", args: [{type: \"star\"}], alias: \"c\"}], from: {type: \"id\", name: \"bar\"}}`. A bit uglier than the current representation, but still easy to process/generate, especially with helper functions and maybe syntactic sugar (the same example might become `{$select: {$cols: [{$fn: {count: '*'}, alias: 'c'}], $from: \"bar\"}}`.\r\n\r\nSee https://www.sqlite.org/syntaxdiagrams.html for getting a feeling for the size of that AST."}}
{"actor":{"display_login":"wmertens"}}