ee-query-builder
Advanced tools
ee-query-builder - npm Package Compare versions
Comparing version 0.2.10 to 0.3.0
| module.exports = require('./lib/QueryBuilder'); | ||
| module.exports = { | ||
| QueryBuilder: require('./lib/QueryBuilder') | ||
| , Functions: require('./lib/Functions') | ||
| } |
| !function(){ | ||
| 'use strict'; | ||
| var Class = require('ee-class') | ||
| , log = require('ee-log') | ||
| , EventEmitter = require('ee-event-emitter') | ||
| , type = require('ee-types'); | ||
@@ -10,4 +13,7 @@ | ||
| module.exports = new Class({ | ||
| aggregateFunctions: { | ||
| inherits: EventEmitter | ||
| // known aggregate functions | ||
| , aggregateFunctions: { | ||
| avg : 'AVG' | ||
@@ -21,6 +27,10 @@ , sum : 'SUM' | ||
| /** | ||
| * class constructor | ||
| * | ||
| * @param <Object> options | ||
| */ | ||
| , init: function(options){ | ||
| this._escapeId = options.escapeId; | ||
| this._escape = options.escape; | ||
| this._type = options.type; | ||
| this._queryBuilder = options.queryBuilder; | ||
@@ -30,2 +40,7 @@ } | ||
| /** | ||
| * select functions | ||
| * | ||
| * @param <Object> options | ||
| */ | ||
| , renderSelectFunction: function(tablename, command) { | ||
@@ -36,2 +51,10 @@ return this.aggregateFunctions[command.fn]+'('+this._escapeId(tablename)+'.'+this._escapeId(command.value)+')'+(command.alias ? ' AS '+this._escapeId(command.alias) : ''); | ||
| /** | ||
| * reference on other tables | ||
| * | ||
| * @param <Object> instruction | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| */ | ||
| , reference: function(command, paramaters) { | ||
@@ -42,2 +65,9 @@ return '= '+this._escapeId(command.entity)+'.'+this._escapeId(command.column); | ||
| /** | ||
| * SQL in statement | ||
| * | ||
| * @param <Object> instruction | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| */ | ||
| , in: function(command, paramaters) { | ||
@@ -48,17 +78,23 @@ var values = command.values; | ||
| //if (command.values.length) { | ||
| if (type.function(values.isQuery)) { | ||
| return ' IN ('+this._queryBuilder._render('query', this._queryBuilder._prepareQuery(values), paramaters).SQLString+')'; | ||
| } | ||
| else { | ||
| if (values.length) { | ||
| return ' IN (' +values.map(function(value) { | ||
| return this._escape(value); | ||
| }.bind(this)).join(', ') +')'; | ||
| } else return undefined; | ||
| } | ||
| //} | ||
| if (type.function(values.isQuery)) { | ||
| return ' IN ('+this._queryBuilder._renderSubQuery(values, paramaters)+')'; | ||
| } | ||
| else { | ||
| if (values.length) { | ||
| return ' IN (' +values.map(function(value) { | ||
| return this._escape(value); | ||
| }.bind(this)).join(', ') +')'; | ||
| } else return undefined; | ||
| } | ||
| } | ||
| /** | ||
| * SQL not in statement | ||
| * | ||
| * @param <Object> instruction | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| */ | ||
| , notIn: function(command, paramaters) { | ||
@@ -70,3 +106,3 @@ var values = command.values; | ||
| if (type.function(values.isQuery)) { | ||
| return ' NOT IN ('+this._queryBuilder._render('query', this._queryBuilder._prepareQuery(values), paramaters).SQLString+')'; | ||
| return ' NOT IN ('+this._queryBuilder._renderSubQuery(values, paramaters)+')'; | ||
| } | ||
@@ -82,10 +118,22 @@ else { | ||
| /** | ||
| * SQL like statement | ||
| * | ||
| * @param <Object> instruction | ||
| */ | ||
| , like: function(command) { | ||
| return ' '+(this._type === 'postgres' ? 'I' : '')+'LIKE '+this._escape(command.value); | ||
| return ' ILIKE '+this._escape(command.value); | ||
| } | ||
| /** | ||
| * SQL not like statement | ||
| * | ||
| * @param <Object> instruction | ||
| */ | ||
| , notLike: function(command) { | ||
| return ' NOT '+(this._type === 'postgres' ? 'I' : '')+'LIKE '+this._escape(command.value); | ||
| return ' NOT ILIKE '+this._escape(command.value); | ||
| } | ||
@@ -95,2 +143,6 @@ | ||
| /** | ||
| * SQL is null | ||
| */ | ||
| , 'null': function(){ | ||
@@ -101,2 +153,6 @@ return ' is null'; | ||
| /** | ||
| * SQL is not null | ||
| */ | ||
| , 'notNull': function(){ | ||
@@ -103,0 +159,0 @@ return ' is not null'; |
| !function(){ | ||
| 'use strict'; | ||
| var Class = require('ee-class') | ||
@@ -10,6 +12,10 @@ , log = require('ee-log') | ||
| /** | ||
| * takes a query object and returns a sql string | ||
| */ | ||
| module.exports = new Class({ | ||
| // some avialbel operators in filters | ||
| _operators: { | ||
@@ -37,12 +43,14 @@ '=': '=' | ||
| * | ||
| * @param <Object> connection options | ||
| * @param <Object> contains the esacpe and escapeid function | ||
| */ | ||
| , init: function init(options) { | ||
| , init: function init(options, FunctionsConstructor) { | ||
| this._escapeId = options.escapeId; | ||
| this._escape = options.escape; | ||
| this._type = options.type; | ||
| options.queryBuilder = this; | ||
| this._functions = new Functions(options); | ||
| // get an instance of the filter renderer foir advanced functions | ||
| this._functions = new (FunctionsConstructor || Functions)({ | ||
| escapeId : options.escapeId | ||
| , escape : options.escape | ||
| , querybuilder : this | ||
| }); | ||
| } | ||
@@ -55,3 +63,6 @@ | ||
| * | ||
| * @param <Object> query | ||
| * @param <String> query mode, defines which query should be built | ||
| * @param <Object> a query object, definition of the query | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| */ | ||
@@ -88,6 +99,14 @@ , _render: function(mode, query, parameters) { | ||
| /** | ||
| * creates an SQL delete statement | ||
| * | ||
| * @param <Object> a query object, definition of the query | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| */ | ||
| , _buildDeleteQuery: function(query, parameters) { | ||
| var SQLString = 'DELETE FROM ' | ||
| , keys = [] | ||
| , values = []; | ||
| var SQLString = 'DELETE FROM ' | ||
| , keys = [] | ||
| , values = []; | ||
@@ -117,3 +136,9 @@ // from | ||
| /** | ||
| * creates an SQL insert statement | ||
| * | ||
| * @param <Object> a query object, definition of the query | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| */ | ||
| , _buildInsertQuery: function(query, parameters) { | ||
@@ -135,12 +160,11 @@ var SQLString = 'INSERT INTO ' | ||
| // values or default values | ||
| if (keys.length) SQLString += ' ('+keys.join(', ')+') VALUES ('+values.join(', ')+')'; | ||
| else if (this._type === "postgres") SQLString += ' DEFAULT VALUES'; | ||
| else SQLString += this._insertWithoutValues(); | ||
| // return values | ||
| if (query.returning && query.returning.length) SQLString += this._returningColumns(query.returning); | ||
| if (this._type === 'postgres' && query.returning && query.returning.length) { | ||
| SQLString += ' RETURNING '+ query.returning.map(function(key){ | ||
| return this._escapeId(key); | ||
| }.bind(this)).join(', '); | ||
| } | ||
| return {SQLString: SQLString, parameters: parameters}; | ||
@@ -150,7 +174,37 @@ } | ||
| /** | ||
| * define which columns return after an insert | ||
| * | ||
| * @param <Array> values to return | ||
| */ | ||
| , _returningColumns: function(dictionary) { | ||
| return ' RETURNING '+(dictionary || []).map(function(key) { | ||
| return this._escapeId(key); | ||
| }.bind(this)).join(', '); | ||
| } | ||
| /** | ||
| * build an insert query without values to | ||
| * insert (table defaults) | ||
| */ | ||
| , _insertWithoutValues: function() { | ||
| return ' DEFAULT VALUES'; | ||
| } | ||
| /** | ||
| * creates an SQL update statement | ||
| * | ||
| * @param <Object> a query object, definition of the query | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| */ | ||
| , _buildUpdateQuery: function(query, parameters) { | ||
| var SQLString = 'UPDATE ' | ||
| , updates = []; | ||
| var SQLString = 'UPDATE ' | ||
| , updates = []; | ||
@@ -162,3 +216,3 @@ // from | ||
| Object.keys(query.values).forEach(function(key) { | ||
| updates.push(this._renderValue(parameters, key, query.values[key])); | ||
| updates.push(this._renderUpdateValue(parameters, key, query.values[key])); | ||
| }.bind(this)); | ||
@@ -187,6 +241,14 @@ | ||
| /* | ||
| * render a value used in a nupdate query | ||
| /** | ||
| * add update specific commands | ||
| * | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| * @param <String> column to modify | ||
| * @param <mixed> the actual value or instruction | ||
| */ | ||
| , _renderValue: function(parameters, filedName, value) { | ||
| , _renderUpdateValue: function(parameters, filedName, value) { | ||
| var id = this._getParameterName(parameters, filedName) | ||
@@ -220,2 +282,10 @@ , fn; | ||
| /** | ||
| * creates an SQL select statement | ||
| * | ||
| * @param <Object> a query object, definition of the query | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| */ | ||
| , _buildSelectQuery: function(query, parameters){ | ||
@@ -255,3 +325,7 @@ var SQLString = '' | ||
| /** | ||
| * creates an SQL group statement | ||
| * | ||
| * @param <Array> array of fields to use in the group statement | ||
| */ | ||
| , _renderGroup: function(group) { | ||
@@ -266,2 +340,8 @@ return group.map(function(grouping){ | ||
| /** | ||
| * creates an SQL join statement | ||
| * | ||
| * @param <String> the database to add the joins on | ||
| * @param <Array> the join instructions | ||
| */ | ||
| , _renderJoin: function(database, joins){ | ||
@@ -282,3 +362,2 @@ var SQLString = ''; | ||
| throw new Error('Unknown join type «'+join.type+'»!').setName('InvalidJoinTypeException'); | ||
| } | ||
@@ -296,2 +375,10 @@ | ||
| /** | ||
| * creates an SQL from statement | ||
| * | ||
| * @param <String> the database | ||
| * @param <String> the table | ||
| */ | ||
| , _renderFrom: function(database, table) { | ||
@@ -302,2 +389,9 @@ return this._escapeId(database || 'undefined') + '.' + this._escapeId(table || 'undefined'); | ||
| /** | ||
| * creates an SQL order statement | ||
| * | ||
| * @param <Array> the order instructions | ||
| */ | ||
| , _renderOrder: function(order) { | ||
@@ -317,23 +411,3 @@ var instructions = []; | ||
| case 'object': | ||
| if (instruction.byArray) { | ||
| var statement | ||
| , index; | ||
| // ordering by custom values | ||
| if (this._type === 'postgres') { | ||
| statement = 'CASE'; | ||
| index = 0; | ||
| (instruction.desc ? instruction.byArray.reverse() : instruction.byArray).forEach(function(item) { | ||
| statement += ' WHEN '+this._escapeId(instruction.entity)+'.'+this._escapeId(instruction.property)+' = '+this._escape(item)+' THEN '+(++index) | ||
| }.bind(this)); | ||
| statement += ' ELSE '+(++index)+' END ' | ||
| instructions.push(statement); | ||
| } | ||
| else if (this._type === 'mysql') { | ||
| instructions.push('FIELD('+this._escapeId(instruction.entity)+'.'+this._escapeId(instruction.property)+', '+(instruction.desc ? instruction.byArray.reverse() : instruction.byArray).join(', ')+')'); | ||
| } | ||
| else throw new Error('byArray ordering on databases of type «'+this._type+'» not implemented!'); | ||
| } | ||
| if (instruction.byArray) this._renderOrderByValue(instruction, instructions); | ||
| instructions.push(this._escapeId(instruction.entity)+'.'+this._escapeId(instruction.property) + (instruction.desc ? ' DESC' : ' ASC')); | ||
@@ -352,9 +426,35 @@ break; | ||
| /** | ||
| instructions.push('FIELD('+this._escapeId(instruction.entity)+'.'+this._escapeId(instruction.property)+', '+(instruction.desc ? instruction.byArray.reverse() : instruction.byArray).join(', ')+')'); | ||
| */ | ||
| /** | ||
| * the _renderFilter() method creates an sql where statement from | ||
| * order by specific values | ||
| * | ||
| * @param <Object> query parameters | ||
| * @param <Object> select tree | ||
| * @param <Object> the instruction | ||
| * @param <Array> array of rendered instructions | ||
| */ | ||
| , _renderOrderByValue: function(instruction, instructions) { | ||
| var statement = 'CASE' | ||
| , index = 0; | ||
| (instruction.desc ? instruction.byArray.reverse() : instruction.byArray).forEach(function(item) { | ||
| statement += ' WHEN '+this._escapeId(instruction.entity)+'.'+this._escapeId(instruction.property)+' = '+this._escape(item)+' THEN '+(++index) | ||
| }.bind(this)); | ||
| statement += ' ELSE '+(++index)+' END '; | ||
| instructions.push(statement); | ||
| } | ||
| /** | ||
| * creates an SQL select statement | ||
| * | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| * @param <Arary> select instructions | ||
| * @param <String> table to select on | ||
| */ | ||
| , _renderSelect: function(parameters, select, tablename) { | ||
@@ -404,11 +504,14 @@ var selects = [] | ||
| /** | ||
| * the _renderFilter() method creates an sql where statement from | ||
| * creates an SQL where statement including aubqueries | ||
| * | ||
| * @param <Object> query parameters | ||
| * @param <Object> filter tree | ||
| * @param <String> name of the current property | ||
| * @param <String> name of the current entity | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| * @param <mixed> filter instructions | ||
| * @param <String> property to apply the current filter on | ||
| * @param <String> entity to apply the current filter on | ||
| */ | ||
| , _renderFilter: function(parameters, filter, property, entity){ | ||
| , _renderFilter: function(parameters, filter, property, entity) { | ||
| var items = [] | ||
@@ -477,17 +580,15 @@ , result | ||
| /* | ||
| * compile a query | ||
| */ | ||
| , _prepareQuery: function(query){ | ||
| var resource = query.getRootResource(); | ||
| resource.setSelectMode(); | ||
| resource.prepare(null, true); | ||
| return resource.query | ||
| } | ||
| , _renderCommand: function(property, command, parameters, entity){ | ||
| /** | ||
| * handles filter statements that are functions, this is usually | ||
| * some advanced functionality like subqueries | ||
| * | ||
| * @param <String> property to apply the current filter on | ||
| * @param <Object> the command extracted from the function | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| * @param <String> entity to apply the current filter on | ||
| */ | ||
| , _renderCommand: function(property, command, parameters, entity) { | ||
| var id, result; | ||
@@ -507,3 +608,3 @@ | ||
| else if (command && command.value && type.function(command.value.isQuery) && command.value.isQuery()) { | ||
| return entity+this._escapeId(property)+' '+this._operators[command.operator]+ ' (' + this._render('query', this._prepareQuery(command.value), parameters).SQLString +')'; | ||
| return entity+this._escapeId(property)+' '+this._operators[command.operator]+ ' (' + this._renderSubQuery(command.value, parameters) +')'; | ||
| } | ||
@@ -530,3 +631,42 @@ else { | ||
| , _getParameterName: function(parameters, name){ | ||
| /** | ||
| * renders a complete query and returns it | ||
| * | ||
| * @param <Object> a queryBuilder object | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| */ | ||
| , _renderSubQuery: function(QueryBuilder, parameters) { | ||
| return this._render('query', this._prepareQuery(QueryBuilder), parameters).SQLString; | ||
| } | ||
| /** | ||
| * prepare s ausbquery for execution | ||
| * | ||
| * @param <Object> queryBuilder instance | ||
| */ | ||
| , _prepareQuery: function(queryBuilder) { | ||
| var resource = queryBuilder.getRootResource(); | ||
| resource.setSelectMode(); | ||
| resource.prepare(null, true); | ||
| return resource.query; | ||
| } | ||
| /** | ||
| * returns an unique playeholder used for safe value replacement | ||
| * in sql strings | ||
| * | ||
| * @param <Object> parameters object, values that must be | ||
| * escaped an inserted into the SQL | ||
| * @param <String> name prefeix for the parameter | ||
| */ | ||
| , _getParameterName: function(parameters, name) { | ||
| var i = 0; | ||
@@ -539,8 +679,8 @@ while(parameters[name+i]) i++; | ||
| /** | ||
| * the _toString() converts types to db compatible string types | ||
| * converts types to db interpretable representations | ||
| * | ||
| * @param <Mixed> input | ||
| */ | ||
| , _toString: function(input){ | ||
| switch(type(input)){ | ||
| , _toString: function(input) { | ||
| switch(type(input)) { | ||
| case 'number': | ||
@@ -569,13 +709,3 @@ return isNaN(input) ? 'null' : ''+input; | ||
| } | ||
| /** | ||
| * the _toType() converts db string types to js types | ||
| * | ||
| * @param <String> input | ||
| */ | ||
| , _toType: function(input){ | ||
| } | ||
| }); | ||
| }(); |
| { | ||
| "name" : "ee-query-builder" | ||
| , "description" : "query builder for ee-orm" | ||
| , "version" : "0.2.10" | ||
| , "description" : "SQL query builder for the ee-orm package. Implents the postgres syntax." | ||
| , "version" : "0.3.0" | ||
| , "homepage" : "https://github.com/eventEmitter/ee-query-builder" | ||
@@ -27,11 +27,7 @@ , "author" : "Michael van der Weg <michael@eventemitter.com> (http://eventemitter.com/)" | ||
| } | ||
| , "devDependencies": { | ||
| "mocha" : "1.18.x" | ||
| , "ee-travis" : "0.1.x" | ||
| } | ||
| , "devDependencies": {} | ||
| , "optionalDependencies": {} | ||
| , "keywords" : [] | ||
| , "scripts": { | ||
| "test" : "./node_modules/mocha/bin/mocha --reporter spec" | ||
| } | ||
| } | ||
| } |
| # ee-query-builder | ||
| description | ||
| SQL query builder for the ee-orm package. Implents the postgres syntax. | ||
| See the following packages for db specific packages: | ||
| - ee-postgres-query-builder | ||
| - ee-mysql-query-builder | ||
| ## installation | ||
| ## build status | ||
| [](https://travis-ci.org/eventEmitter/ee-query-builder) | ||
| ## usage | ||
| npm install ee-query-builder |
Sorry, the diff of this file is not supported yet
Sorry, the diff of this file is not supported yet
New alerts
License Policy Violation
LicenseThis package is not allowed per your license policy. Review the package's license to ensure compliance.
Found 1 instance in 1 package
Fixed alerts
License Policy Violation
LicenseThis package is not allowed per your license policy. Review the package's license to ensure compliance.
Found 1 instance in 1 package
Improved metrics
- Total package byte prevSize
- increased by14.13%
26156
- Dev dependency count
- decreased by-100%
0
- Lines of code
- increased by20.87%
637
Worsened metrics
- Number of package files
- decreased by-11.11%
8
- Number of lines in readme file
- decreased by-14.29%
12
No dependency changes