Something went wrong on our end
-
Sebastian Listl authoredSebastian Listl authored
process.js 190.48 KiB
import("system.logging");
import("system.translate");
import("system.vars");
import("system.util");
import("system.db");
import("system.datetime");
import("system.tools");
import("system.SQLTYPES");
import("system.text");
import("Util_lib");
/**
* object for easier handling of conditions;
* With this object you do not have to check if the string is empty or not;
* you don't need to append a "1=1" condition or similar;
* this objects gains most benefit if you have a lot of conditions that are added (or not) depending on tons of JDito-conditions
*
* You can also use SqlCondition.begin(alias) for simpler object creation without new and without the need for an extra variable to save the object.
*
* @class
* @param {String} [alias=the current alias] the database alias where the condition shall be executed later (important for column types of preparedStatements)
* @example
* see others/guide/HowToSqlConditionLib.adoc
*
* @deprecated The SqlCondition will be removed in version >= 2020.x
* Use the SqlBuilder instead.
* For SqlBuilder usage see the documentation-property of the Sql_lib.
*/
function SqlCondition(alias) {
// setting null is only needed to provide autocomplete for the ADITO-designer
this.preparedValues = null;
this._init(); // the properties are initalized in an extra function because init is nearly the same as resetting (clearing) the SqlConditions
this.alias = alias;
// save, if the last condition was an OR. For better bracket-placement
this._lastWasOr = false;
}
/**
* Alternative possibility to crate a new condition.
* With this you don't need new SqlCondition and you can use the object directly after it's creation
* --> cleaner code
*
* It is very usefull for the orSqlCondition() and andSqlCondition() because now you can create a new condition inline.
* You can also use it for simple selects without the need to save the conditionObject in an extra variable.
* See Examples!
*
* @param {String} [alias=the current alias] the database alias where the condition shall be executed later (important for column types of preparedStatements)
* @return {SqlCondition} the new SqlCondition-object
*
* @example
* vars mySelect = SqlCondition.begin(alias)
* .and("MYID = '123'")
* .and(SqlCondition.begin()
* .or("NAME = 'Max'")
* .or("NAME = 'Bob'")
* )
* .buildSql("select * from MYTABLE");
*
* // Or use it for simple selects:
* var sum = db.cell(SqlCondition.begin()
* .andPrepared("STOCK.PRODUCT_ID", pid)
* .buildSql("select sum(QUANTITY * IN_OUT) from STOCK"));
*
* @deprecated The SqlCondition will be removed in version >= 2020.x
* Use the SqlBuilder instead.
* For SqlBuilder usage see the documentation-property of the Sql_lib."
*/
SqlCondition.begin = function(alias) {
return new SqlCondition(alias);}
/**
* checks if conditions have been added to the object
* @return {Boolean} true if conditions have been added, false when not
*/
SqlCondition.prototype.isSet = function() {
if (this._sqlStorage)
return true;
return false;
}
/**
* append with SQL-and; no paranthesize of existing conditions is done
* @param {String} cond the condition string which shall be appended
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.and = function(cond) {
if (!cond)
return this;
if (this.isSet())
this._sqlStorage += " and ";
this._sqlStorage += cond;
return this;
}
/**
* append with SQL-or; Also paranthesize the existing conditions
* @param {String} cond the condition string which shall be appended
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.or = function(cond) {
if (!cond)
return this;
if (this.isSet() && !this._lastWasOr) {
this._sqlStorage = "(" + this._sqlStorage + ") or (" + cond + ")";
this._lastWasOr = true;
} else if (this.isSet() && this._lastWasOr) {
this._sqlStorage = this._sqlStorage + " or (" + cond + ")";
this._lastWasOr = true;
} else {
this._sqlStorage = cond;
}
return this;
}
/**
* append a prepared-array to this sql condition with SQL-and
* @param {Array} preparedObj a prepared condition-array
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.andAttachPrepared = function(preparedObj) {
if (preparedObj)
{
this.preparedValues = this.preparedValues.concat(preparedObj[1]);
return this.and(preparedObj[0]);
}
return this;
}
/**
* append a prepared-array to this sql condition with SQL-or
* @param {Array} preparedObj a prepared condition-array
* @return {SqlCondition} current SqlCondition-object
*/SqlCondition.prototype.orAttachPrepared = function(preparedObj) {
if (preparedObj)
{
this.preparedValues = this.preparedValues.concat(preparedObj[1]);
return this.or(preparedObj[0]);
}
return this;
}
/**
* append another condition with SQL-and
*
* @param {SqlCondition} cond the condition which shall be appended
* @param {String} [alternativeCond=""] condition if the given SqlCondition has none
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.andSqlCondition = function(cond, alternativeCond) {
if (!cond)
return this
var otherCondition = cond.toString(alternativeCond);
if (otherCondition.trim() != "")
{
this.and(" ( " + cond.toString(alternativeCond) + " ) ");
if (cond.preparedValues) {
this.preparedValues = this.preparedValues.concat(cond.preparedValues);
}
}
return this;
}
/**
* append another condition with SQL-or; Also paranthesize the existing conditions
*
* @param {SqlCondition} cond the condition which shall be appended
* @param {String} [alternativeCond=""] condition if the given SqlCondition has none
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.orSqlCondition = function(cond, alternativeCond) {
var otherCondition = cond.toString(alternativeCond);
if (otherCondition.trim() != "")
{
this.or(" ( " + cond.toString(alternativeCond) + " ) ");
if (cond.preparedValues) {
this.preparedValues = this.preparedValues.concat(cond.preparedValues);
}
}
return this;
}
/**
* append an condition that uses a subQuery with SQL-and
*
* @param {SqlBuilder} subQuery the SqlBuilder object that will be used as a subquery
* @param {String} [cond="exists"] condition that is used (e. g. exists, not exists, COLUMN = any, COLUMN in, ...)
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.andSqlBuilder = function(subQuery, cond) {
if (!cond)
cond = "exists";
var preparedObj = subQuery.build();
preparedObj[0] = cond + " ( " + preparedObj[0] + " ) ";
this.andAttachPrepared(preparedObj);
return this;
}
/** * append an condition that uses a subQuery with SQL-or
*
* @param {SqlBuilder} subQuery the SqlBuilder object that will be used as a subquery
* @param {String} [cond="exists"] condition that is used (e. g. exists, not exists, COLUMN = any, COLUMN in, ...)
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.orSqlBuilder = function(subQuery, cond) {
if (!cond)
cond = "exists";
var preparedObj = subQuery.build();
preparedObj[0] = cond + " ( " + preparedObj[0] + " ) ";
this.orAttachPrepared(preparedObj);
return this;
}
/**
* same as the "and"-function but with preparedStatement functionality
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String} value the value that shall be set into the prepared statement
* @param {String} [cond="# = ?"] the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname;
* e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.andPrepare = function(field, value, cond, fieldType) {
cond = this._prepare(field, value, cond, fieldType);
return this.and(cond);
}
/**
* same as the "or"-function but with preparedStatement functionality
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String} value the value that shall be set into the prepared statement
* @param {String} [cond="# = ?"] the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname;
* e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.orPrepare = function(field, value, cond, fieldType) {
cond = this._prepare(field, value, cond, fieldType);
return this.or(cond);
}
/**
* same as the "andPrepare"-function but only applied if the passed "value" is truely
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String} value the value that shall be set into the prepared statement
* @param {String} [cond="# = ?"] the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname;
* e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.andPrepareIfSet = function(field, value, cond, fieldType) { if (value)
return this.andPrepare(field, value, cond, fieldType);
return this;
}
/**
* same as the "orPrepare"-function but only applied if the passed "value" is truely
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String} value the value that shall be set into the prepared statement
* @param {String} [cond="# = ?"] the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname;
* e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.orPrepareIfSet = function(field, value, cond, fieldType) {
if (value)
return this.orPrepare(field, value, cond, fieldType);
return this;
}
/**
* same as the "andPrepare"-function but with validation of adito-variables functionality
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String} variable the adito-variable that shall be set into the prepared statement
* @param {String} [cond = "# = ?" ] the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname;
* e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.andPrepareVars = function(field, variable, cond, fieldType) {
variable = this._checkVars(variable)
if (variable) {
return this.andPrepare(field, variable, cond, fieldType);
}
return this;
}
/**
* same as the "orPrepare"-function but with validation of adito-variables functionality
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String} variable the adito-variable that shall be set into the prepared statement
* @param {String} [cond="# = ?"] the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname;
* e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.orPrepareVars = function(field, variable, cond, fieldType) {
variable = this._checkVars(variable)
if (variable) {
return this.orPrepare(field, variable, cond, fieldType);
}
return this;
}
/** * creates a IN-statement out of a field and an array of values.
* Be carefull with a big number of values. This may have a bad performance.
*
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String[]} values the value that shall be set into the prepared statement
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @param {Boolean} [not = undefined] if true, add not before in
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.andIn = function(field, values, fieldType, not) {
return this.andAttachPrepared(this._in(field, values, fieldType, not));
}
/**
* creates a IN-statement out of a field and an array of values.
* Be carefull with a big number of values. This may have a bad performance.
*
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String[]} values the value that shall be set into the prepared statement
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @param {Boolean} [not = undefined] if true, add not before in
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype.orIn = function(field, values, fieldType, not) {
return this.orAttachPrepared(this._in(field, values, fieldType, not));
}
/**
* creates a IN-statement out of a field and an array of values.
* Be carefull with a big number of values. This may have a bad performance.
*
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String[]} values the value that shall be set into the prepared statement
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @param {Boolean} [not = undefined] if true, add not before in
* @return {SqlCondition} current SqlCondition-object
*/
SqlCondition.prototype._in = function(field, values, fieldType, not) {
if (values && values.length > 0)
{
if (fieldType == undefined)
fieldType = SqlUtils.getSingleColumnType(field, undefined, this.alias);
preparedStatement = SqlUtils.getSqlInStatement(field, values, undefined, true, fieldType);
if (not)
preparedStatement[0] = " not " + preparedStatement[0];
return preparedStatement;
}
return null;
}
/**
* ready to use string; does not contain a where keyword at the beginning
* @param {String} [alternativeCond=""] condition that is returned when nothing has been appended. * @return {String} concatenated SQL-condition; empty string if nothing has been appended or - if passed - the alternativeCond
*/
SqlCondition.prototype.toString = function(alternativeCond) {
if (!this.isSet() && alternativeCond)
return alternativeCond
else
return this._sqlStorage;
}
/**
* ready to use string; does contain a where keyword at the beginning
* @param {String} [alternativeCond=""] condition that is returned when nothing has been appended.
* @return {SqlCondition} concatenated SQL-condition; empty string if nothing has been appended or - if passed - the alternativeCond
*/
SqlCondition.prototype.toWhereString = function(alternativeCond) {
var cond = this.toString(alternativeCond);
if (cond)
return " where " + cond;
else
return cond;
}
/**
* ready to use prepared condition; does not contain a where keyword at the beginning
* @param {String} [alternativeCond=""] Condition that is returned when nothing has been appended.
* @return {Array[][][]} Prepared condition with [condition, [[field1, type1], [field2, type2]]]
*/
SqlCondition.prototype.build = function(alternativeCond) {
return [this.toString(alternativeCond), this.preparedValues];
}
/**
* ready to use prepared select
* @param {String} pBeforeCondition Part of the sql before the condition without where (e.g. "select FIRSTNAME from PERSON")
* @param {String} [pAlternativeCond=""] Condition that is returned when nothing has been appended.
* @param {String} [pAfterCondition=""] Part of the sql after the condition (e.g. "order by FIRSTNAME").
* @param {Boolean} [pWithWere=true] true if where should be added to the bginning
* @return {Array[][][]} Prepared condition with [condition, [[field1, type1], [field2, type2]]]
*/
SqlCondition.prototype.buildSql = function(pBeforeCondition, pAlternativeCond, pAfterCondition, pWithWere) {
if (pAfterCondition == undefined)
pAfterCondition = "";
if (pWithWere == undefined)
pWithWere = true;
return [pBeforeCondition + " " +
(pWithWere ? this.toWhereString(pAlternativeCond) : this.toString(pAlternativeCond)) +
" " + pAfterCondition, this.preparedValues];
}
/**
* translates SqlCondition to plain SQL. Use this if prepared statements are not supported.
* It resolves all prepared values.
* @param {String} pAlternativeCond used if the SqlCondition does not contain any condition.
* @return {String} plain SQL condition
*/
SqlCondition.prototype.translate = function(pAlternativeCond)
{
return SqlUtils.translateConditionWithQuotes(this.build(pAlternativeCond, this.alias));
}
/**
* Check if (adito-)variable exists and vars.getString is not empty
* @param {String} variable the variable name (e.g. "$field.CONTACT_ID")
* @return {String | Boolean} The value of the field as string OR false if it doesn't exist.
*
* @ignore
*/
SqlCondition.prototype._checkVars = function(variable) { if (vars.exists(variable)) {
var value = vars.getString(variable);
if (value) {
return value;
}
}
return false;
}
/**
* hidden function for composing preparedStatements
* @param {String | String[]} field the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String} value the value that shall be set into the prepared statement
* @param {String} cond the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname;
* e.g. "# > ?"; escaping the number sign is possible with a backslash "\"
* Default is "# = ?"
* @param {Numeric | Boolean} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* The loaded type is cached if no type is given. So it is also safe to use this in a loop.
* e.g.
* for (...) {
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?")
* }
* @return {String} the replaced SQL-condition string (replace # by the fieldname)
* @ignore
*/
SqlCondition.prototype._prepare = function(field, value, cond, fieldType) {
if (value == undefined)
{
throw new Error(translate.withArguments("${SQL_LIB_UNDEFINED_VALUE} field: %0", [field]));
}
if (cond == undefined) {
cond = "# = ?"
}
var alias;
if (typeof field === 'string')
{
var pointPos = field.indexOf(".");
if (pointPos > 0 && pointPos < field.length-1)
{
alias = field;
}
else
{
throw new Error(translate.text("${SQL_LIB_FIELD_WRONG_FORMAT}") + field + translate.withArguments("${SQL_LIB_FIELD_WRONG_FORMAT} field: %0", [field]));
}
}
else
{
if (field.length == 3)
{
alias = field[2] + "." + field[1];
field = field[0] + "." + field[1];
}
else
{
throw new Error(translate.text("${SQL_LIB_FIELD_WRONG_FORMAT}") + field.toSource() + translate.withArguments("${SQL_LIB_FIELD_WRONG_FORMAT} field: %0", [field.toSource()]));
}
}
var type;
if (fieldType == undefined)
fieldType = SqlUtils.getSingleColumnType(field, undefined, this.alias);
//this function looks more complex (and slower) than it actually is
/* the following regex looks like this after javascript-escaping of the backslash: (?<!\\)((?:\\\\)*)# the regexp searches for the unescaped character and these characters are replaced by the field name
examples:
---------------------
| # --match |
| \# --no-match |
| \\# --match |
| \\\# --no-match |
| \\\\# --match |
---------------------
*/
//use replaceAll because it's faster and supports negative lookbehinds
cond = text.replaceAll(cond, {
//manually readd the replaced backslashes by using a group reference, because they a part of the match and therefore replaced by "replaceAll"
//since the field COULD contain already a group reference (I think this is extremely uncommon;
//probably that never happens but better stay save): escape that references within the fieldname
"(?<!\\\\)((?:\\\\\\\\)*)#": "$1" + text.replaceAll(alias, {
"$1": "\\$1"
}),
//now that we've replaced the correct field placeholder let's replace the escaped number sign "\#" to a normal number sign "#"
"\\\\#": "#"
});
type = fieldType
this.preparedValues.push([value.toString(), type]);
return cond;
}
/**
* function that resets the current SqlCondition as if no conditions would have been added
* this is usefull if you want to reuse the same object over and over
* @return {null}
*/
SqlCondition.prototype.clear = function() {
this._sqlStorage = "";
this.preparedValues = [];
return this;
}
/**
* hidden function for initializing all properties for the sql conditions
* @return {null}
*
* @ignore
*/
SqlCondition.prototype._init = function() {
//init only wraps the clear function to avoid confusion in the constructor (and provide better extensibility)
return this.clear();
}
// some static functions for often used tasks. They are only provided for very simple tasks.
/**
* pField = pValue
* @param {String} pField the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME"
* @param {String} pValue the value that shall be set into the prepared statement
* @param {String} [pAlternativeCond=""] Condition that is returned when nothing has been appended.
* @param {String} [pAlias=the current alias] the database alias where the condition shall be executed later (important for column types of preparedStatements)
*
* @return {Array[][][]} Prepared condition with [condition, [[field, type]]]
*
* @deprecated
*/
SqlCondition.equals = function(pField, pValue, pAlternativeCond, pAlias) {
return SqlCondition["begin"](pAlias).andPrepare(pField, pValue).build(pAlternativeCond);
}
/**
* pField <> pValue
* @param {String} pField the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME"
* @param {String} pValue the value that shall be set into the prepared statement
* @param {String} [pAlternativeCond=""] Condition that is returned when nothing has been appended.
* @param {String} [pAlias=the current alias] the database alias where the condition shall be executed later (important for column types of preparedStatements)
*
* @return {Array[][][]} Prepared condition with [condition, [[field, type]]]
*
* @deprecated
*/
SqlCondition.equalsNot = function(pField, pValue, pAlternativeCond, pAlias) {
return SqlCondition["begin"](pAlias).andPrepare(pField, pValue, "# <> ?").build(pAlternativeCond);
}
// see Documentation property of this lib for further explanation
/**
* Creates a new SqlBuilder object and sets the select clause of the sql.
*
* @param {String|Array|SqlBuilder} pFields You can pass:<br/>
* - A String is just used AS IT IS. (e.g. "FIRSTNAME, LASTNAME")<br/>
* - SqlBuilder is used as Subquery<br/>
* - The array can also contain Strings, SqlBuilder which are just concatenated (e.g. ["FIRSTNAME", "LASTNAME", someSqlBuilderContainingFullSelect])<br/>
* Please see .select() for more information and examples.
* @param {String} [pAlias=currentAlias] This alias is used for fetching the ColumnTypes and also for the .table, .cell, .updateData, ... -functions
* @return {SqlBuilder} A new SqlBuilder object already containing the provided fields
*
* @example
* var lastname = "Huber";
*
* var persons = newSelect("FIRSTNAME")
* .from("PERSON")
* .where("PERSON.LASTNAME", lastname)
* .arrayColumn();
*/
function newSelect(pFields, pAlias)
{
return new SqlBuilder(pAlias).select(pFields);
}
/**
* Creates a new SqlBuilder object and calls .where on it.<br/>
* This is very useful if you just need a condition as you can pass the first condition directly.<br/>
* Note: Even if you ommit all parameters, you do not have to call .where again. You can directly write .and / .or after newWhere().<br/>
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray} [pFieldOrCond] If this is the only parameter, it is used as Subselect <br/>
* else it is used as Field. <br/>
* Please see .where() for more information and examples.
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value whitch is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* Please see .where() for more information and examples.
* @param {String} [pCondition="# = ?"] This is the condition which should be used.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue<br/>
* <strong>IMPORTANT: the # has to be before the ?</strong><br/>
* Please see .where() for more information and examples.
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement<br/>
* In most cases you don't need this.<br/>
* Please see .where() for more information and examples.
* @param {String} [pAlias=currentAlias] This alias is used for fetching the ColumnTypes and also for the .table, .cell, .updateData, ... -functions
* @return {SqlBuilder} A new SqlBuilder object which already called .where
*
* @example
* // examples, how where / whereIfSet could be used in a conditionProcess.
* //////Example 1/////
* var cond = newWhereIfSet("CONTACT.PERSON_ID", "$param.PersonId_param")
* .andIfSet("CONTACT.PERSON_ID", JSON.parse(vars.getString("$param.BlacklistPersons_param")), SqlBuilder.NOT_IN())
*
* result.string(cond.toString()); *
* //////Example 2/////
* var cond = newWhere();
*
* // note: we can use .and* now without an extra .where
* if (SOMECHECKS)
* cond.andIfSet(...)
*
* if (SOME_MORE_CHECKS)
* cond.and(...)
*
* result.string(cond.toString());
*/
function newWhere(pFieldOrCond, pValue, pCondition, pFieldType, pAlias)
{
return new SqlBuilder(pAlias).where(pFieldOrCond, pValue, pCondition, pFieldType);
}
/**
* Creates a new SqlBuilder object and calls .whereIfSet on it.<br/>
* This is very useful if you just need a condition as you can pass the first condition directly.<br/>
* Note: Even if you ommit all parameters, you do not have to call .where again. You can directly write .and / .or after newWhere().
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray} [pFieldOrCond] If this is the only parameter, it is used as Subselect <br/>
* else it is used as Field. <br/>
* Please see .whereIfSet() for more information and examples.
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value whitch is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* Please see .whereIfSet() for more information and examples.
* @param {String} [pCondition="# = ?"] This is the condition which should be used.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue<br/>
* <strong>IMPORTANT: the # has to be before the ?</strong><br/>
* Please see .whereIfSet() for more information and examples.
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement<br/>
* In most cases you don't need this.<br/>
* Please see .whereIfSet() for more information and examples.
* @param {String} [pAlias=currentAlias] This alias is used for fetching the ColumnTypes and also for the .table, .cell, .updateData, ... -functions
* @return {SqlBuilder} A new SqlBuilder object which already called .whereIfSet
*
* @example
* // examples, how where / whereIfSet could be used in a conditionProcess.
* //////Example 1/////
* var cond = newWhereIfSet("CONTACT.PERSON_ID", "$param.PersonId_param")
* .andIfSet("CONTACT.PERSON_ID", JSON.parse(vars.getString("$param.BlacklistPersons_param")), SqlBuilder.NOT_IN())
*
* result.string(cond.toString());
*
* //////Example 2/////
* var cond = newWhere();
*
* // note: we can use .and* now without an extra .where
* if (SOMECHECKS)
* cond.andIfSet(...)
*
* if (SOME_MORE_CHECKS)
* cond.and(...)
*
* result.string(cond.toString());
*/
function newWhereIfSet(pFieldOrCond, pValue, pCondition, pFieldType, pAlias)
{
return new SqlBuilder(pAlias).whereIfSet(pFieldOrCond, pValue, pCondition, pFieldType);
}
/**
* Object for building sqls. The main purpose of this is to make it easy to use prepared statements.<br/>
* You should ALWAYS use prepared statemnts for Security and maybe also for performance reasons.<br/>
* If you are not used to use prepared statements all the time you WILL forget to use it, when it's really needed. (eg. direct user input, not just ids)<br/>
* <br/> * This can also be useful to build complex sqls where parts should be added<br/>
* dynamically while keeping the code clean.<br/>
* <br/>
* There exist some shortcut funtions<br/>
* - if you need a full select use newSelect(...)<br/>
* - if you need only a condition use newWhere(...) or newWhereIfSet(...)
*
* @param {String} [pAlias=currentAlias] This alias is used for fetching the ColumnTypes and also for the .table, .cell, .updateData, ... -functions
* @class
*/
function SqlBuilder (pAlias)
{
if(!(this instanceof SqlBuilder))
throw SqlBuilder._ERROR_INSTANCIATE_WITH_NEW();
this._select = null;
this._from = null;
this._tableName = null; //for insert/update/delete
this._joins = [];
this._groupBy = null;
this._having = null;
this._orderBy = null;
this._unions = [];
this.alias = pAlias;
//for paging
this._startRow = null;
this._pageSize = null;
this._hasMoreRows = true;
this._subselectAlias = null;
this._where = {};
this._initWhere();
SqlBuilder.defineCanBuildSql(this);
}
/**
* @return {Symbol}
*/
SqlBuilder.getCanBuildSqlSymbol = function ()
{
return Symbol["for"]("canBuildSql");
}
SqlBuilder.defineCanBuildSql = function (pObject)
{
pObject[SqlBuilder.getCanBuildSqlSymbol()] = true;
}
SqlBuilder.checkCanBuildSql = function (pObject)
{
return pObject[SqlBuilder.getCanBuildSqlSymbol()];
}
/**
* Deep copies the SqlBuilder object and returns a new one.<br/>
* Use this if you want to add for example add additional parameters without modifying the current builder.
* @return a full copy of the current SqlBuilder
*/
SqlBuilder.prototype.copy = function()
{
var newBuilder = _deepCopyByJson(this, new SqlBuilder());
return newBuilder;
// NOTE: this works only with simple data types.
// Here we only use strings, arrays, booleans and null, so this should work
function _deepCopyByJson(pObject, pNewObject)
{
// deep copy by using json var deepCopied = JSON.parse(JSON.stringify(pObject));
// set the props of the new object to the deepCopied ones.
// without this all functions would be lost
for (let prop in deepCopied)
{
pNewObject[prop] = deepCopied[prop]
}
return pNewObject;
}
}
// errors which are thrown by the SqlBuilder
SqlBuilder._ERROR_INSTANCIATE_WITH_NEW = function()
{
return new Error(translate.text("SqlBuilder must be instanciated with 'new' or one of the factory methods (newSelect, newWhere, newWhereIfSet)"));
}
SqlBuilder._ERROR_INVALID_CONDITION_VALUE_TYPE = function()
{
return new Error(translate.text("SqlBuilder: invalid value-type for pCondition"));
}
SqlBuilder._ERROR_NO_CONDITION = function()
{
return new Error(translate.text("SqlBuilder: if you use a subQuery (e.g. SqlBuilder) you have to provide pCondition (e.g. \"exists ?\")"));
}
SqlBuilder._ERROR_INVALID_SUBQUERY_TYPE = function()
{
return new Error(translate.text("SqlBuilder: invalid value-type for pFieldOrCond. It can be a fully qualified SqlBuilder (e.g. select, from, ... have to be set) or an jdito-prepared-statement array"));
}
SqlBuilder._ERROR_VALUE_IS_MANDATORY = function()
{
return new Error(translate.text("SqlBuilder: pValue (or pFieldOrCond if only one param) is not allowed to be null, undefined or []. (use *IfSet functions if you need optional conditions which are just ignored if value is null or undefined)"));
}
SqlBuilder._ERROR_VALUE_IS_MANDATORY_JDITO_VAR = function()
{
return new Error(translate.text("SqlBuilder: pValue has to be a jdito variable which returns something different than null. (use *IfSet functions if you need optional conditions which are just ignored if value is null or undefined)"));
}
SqlBuilder._ERROR_UNSUPPORTED_PARAMETER_COMBINATION = function()
{
return new Error(translate.text("SqlBuilder: unsupportet parameter combination"));
}
SqlBuilder._ERROR_NO_TABLE = function()
{
return new Error(translate.text("SqlBuilder.deleteDat/updateData: You have to specify a tablename"));
}
SqlBuilder._ERROR_NO_PARAMETER_PROVIDED = function()
{
return new Error(translate.text("SqlBuilder: You have to specify at least one parameter"));
}
SqlBuilder._ERROR_WHERE_NOT_FIRST = function()
{
return new Error(translate.text("SqlBuilder: .where has to be called before following and/or."));
}
SqlBuilder._ERROR_ONLY_ONE_WHERE = function()
{
return new Error(translate.text("SqlBuilder: .where has to be called only one time. Use and/or for further conditions."));
}
SqlBuilder._ERROR_INCOMPLETE_SELECT = function (){
return new Error(translate.text("SqlBuilder: select and from were expected, but not provided."));
}
SqlBuilder._ERROR_CONDITION_IS_MANDATORY = function ()
{
return new Error(translate.text("SqlBuilder: You have to provide a subquery as SqlBuilder, prepared-array or string"));
}
SqlBuilder._ERROR_SUBSELECT_AS_FIELD_NOT_COMPLETE = function ()
{
return new Error(translate.text("SqlBuilder: If pFieldOrCond is a SqlBuilder & pValue is provided, pFieldOrCond has to be a full SqlBuilder which will be used as subselect"));
}
SqlBuilder._ERROR_SUBSELECT_AS_FIELD_NO_FIELD_TYPE = function ()
{
return new Error(translate.text("SqlBuilder: If pFieldOrCond is a SqlBuilder & pValue is provided, you have to provide also pFieldType, as the type cannot be calculated from pFieldOrCond because it is a subselect"));
}
SqlBuilder._ERROR_CONDITION_WRONG_FORMAT = function ()
{
return new Error(translate.text("SqlBuilder: The '#' in pCondition has to occur before the '?' and '?' has to occur 1 time, '#' has to occur 1 or 0 times."));
}
SqlBuilder._ERROR_NOT_BOOLEAN = function ()
{
return new Error(translate.text("pExecuteOnlyIfConditionExists has to be of type boolean. This parameter controls what happens if the condition is empty (select / delete all or nothing)"));
}
SqlBuilder._ERROR_UPDATE_VALUES_INVALID = function ()
{
return new Error(translate.text("SqlBuilder: The provided values object for updateFields is invalid or is not an object."));
}
SqlBuilder._ERROR_PAGESIZE_INVALID = function ()
{
return new Error(translate.text("SqlBuilder: The pagesize is not set or is not a number."));
}
SqlBuilder._ERROR_NOT_A_FUNCTION = function ()
{
return new Error(translate.text("SqlBuilder: The provided callback function is not a function."));
}
/**
* Alternative way of creating a new SqlBuilder object that allows to use
* methods on it directly without having to put brackets around it
*
* @return {SqlBuilder} a new SqlBuilder object
*
* @example
* var query = SqlBuilder.begin()
* .select("ORGANISATION.NAME, FIRSTNAME, LASTNAME")
* .from("PERSON")
* .join("CONTACT", "CONTACT.PERSON_ID = PERSON.PERSONID")
* .leftJoin("ORGANISATION", SqlCondition.begin()
* .and("CONTACT.ORGANISATION_ID = ORGANISATION.ORGANISATIONID")
* .andPrepare("ORGANISATION.NAME", "S%", "# like ?")
* .build("1=2"))
* .where(SqlCondition.begin()
* .andPrepare("CONTACT.STATUS", $KeywordRegistry.contactStatus$active())
* .build("1=2"));
*
* if (getCountry) //changing and adding parts
* {
* query.select("ORGANISATION.NAME, FIRSTNAME, LASTNAME, COUNTRY");
* query.leftJoin("ADDRESS", "CONTACT.ADDRESS_ID = ADDRESS.ADDRESSID");
* }
*
* var data = db.table(query.build());
* * @deprecated using .begin is deprecated as it's now possible to write "new SqlBuilder().select(...).from(...)....
You can now use "newSelect(...)", "newWhere(...)", "newWhereIfSet(...)" or "new SqlBuilder()" to create a new SqlBuilder instance.
For further SqlBuilder usage see the documentation-property of the Sql_lib.
*/
SqlBuilder.begin = function ()
{
return new SqlBuilder();
}
/**
* Builds the sql and uses SqlUtils.translateXXXWithQuotes to make a string out of it.
* @param {String} [pDefaultConditionIfNone=""] the default condition string to use if the SqlBuilder contains no condition. In most cases you won't need this
* @param {Boolean} [pForceAsStatement=false] forces the use of SqlUtils.translateStatementWithQuotes even if it's no full statement. This is needed for example if you do not want brakets around the generated statement
* @return {String} the sql as string
*/
SqlBuilder.prototype.toString = function(pDefaultConditionIfNone, pForceAsStatement)
{
var built = this.build(pDefaultConditionIfNone)
if (built[0] !== "")
{
if (!pForceAsStatement && !this.isFullSelect() && (this.hasCondition() || pDefaultConditionIfNone))
return SqlUtils.translateConditionWithQuotes(built, this.alias);
else
return SqlUtils.translateStatementWithQuotes(built, this.alias);
}
return "";
}
/**
* Sets the select clause of the sql.
* @param {String|Array|SqlBuilder} pFields You can pass:<br/>
* - A String is just used AS IT IS. (e.g. "FIRSTNAME, LASTNAME")<br/>
* - SqlBuilder is used as Subquery<br/>
* - The array can also contain Strings, SqlBuilder which are just concatenated (e.g. ["FIRSTNAME", "LASTNAME", someSqlBuilderContainingFullSelect])
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.select = function(pFields)
{
this._select = SqlBuilder._getStatement(pFields, "select", undefined, true, true);
return this;
}
/**
* Sets the select clause of the sql with distinct.
* @param {String|String[]} pFields
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.selectDistinct = function (pFields)
{
this._select = SqlBuilder._getStatement(pFields, "select distinct", undefined, true, true);
return this;
}
/**
* sets an alias-name which is added at some places if this SqlBuilder is used as subselect (e.g. in .select(), .join(), .from(), ...)
* @param {String} pSubselectAlias
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.subselectAlias = function(pSubselectAlias)
{
this._subselectAlias = pSubselectAlias;
return this;
}
/** * Sets the table that is used for insert/update/delete functions.
*
* @param {String} pTable
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.tableName = function (pTable)
{
this._tableName = pTable;
return this;
}
/**
* Sets the from clause of the sql.<br/>
* <br/>
* Note: It is recommended to add joins via the .join functions.<br/>
* But in some cases you may already get a full from clause including the joins. In this case it is also possible to include them in the from-string.<br/>
*
* @param {String|SqlBuilder} pTable if it is a String, it is used as it is as table<br/>
* if it is a SqlBuilder, it is used as subselect: e.g. select * from (select FIRSTNAME from PERSON)
* @param {String} [pTableAlias] table alias
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.from = function(pTable, pTableAlias)
{
this._from = SqlBuilder._getStatement(pTable, "from", pTableAlias, false, (pTableAlias ? false : true));
if (typeof(pTable) === "string")
this._tableName = pTable;
return this;
}
/**
* Adds a join clause to the sql.
* @param {String|SqlBuilder} pTable if it is a String, it is used as it is as table<br/>
* if it is a SqlBuilder, it is used as subselect: e.g. select * from Table1 join (select FIRSTNAME from PERSON) on ...
* @param {String|SqlBuilder} [pCondition] The where condition. This can be<br/>
* - a string (without the where keyword)<br/>
* - a SqlBuilder NOTE: only the condition is used from it
*
* @param {String} [pTableAlias] This alias is used to add an alias to the tablename
* @param {String} [pPrefix] string before the join, for example "left", "right"
* @param {String} [pReplacementForWordJoin] if this is set, this is used instead of the word "join". Needed for e.g. OUTER APPLY in MSSQL
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.join = function(pTable, pCondition, pTableAlias, pPrefix, pReplacementForWordJoin)
{
// support for deprecated SqlCondition
if (pCondition instanceof SqlCondition)
{
pCondition = newWhere(pCondition);
logging.log("Warning: using .where with a SqlCondition as pFieldOrCond is deprecated. The SqlCondition will be removed in version >= 2020.x\n"
+ "For SqlBuilder usage see the documentation-property of the Sql_lib.");
}
var prefix = (pReplacementForWordJoin ? pReplacementForWordJoin : "join");
if (pPrefix)
prefix = pPrefix + " " + prefix;
var postfix = pCondition ? "on" : "";
if (pTableAlias)
postfix = pTableAlias + " " + postfix;
else if (pTable instanceof SqlBuilder && pTable._subselectAlias)
postfix = pTable._subselectAlias + " " + postfix;
var joinPart = SqlBuilder._getStatement(pTable, prefix, postfix.trim());
if (pCondition)
{
if (pCondition instanceof SqlBuilder)
pCondition = [pCondition._where.sqlStorage, pCondition._where.preparedValues]
var conditionPart = SqlBuilder._getStatement(pCondition);
joinPart.sqlStorage += " " + conditionPart.sqlStorage;
joinPart.preparedValues = joinPart.preparedValues.concat(conditionPart.preparedValues);
}
this._joins.push(joinPart)
return this;
}
/**
* Adds a left join clause to the sql.
*
* @param {String|SqlBuilder} pTable if it is a String, it is used as it is as table<br/>
* if it is a SqlBuilder, it is used as subselect: e.g. select * from Table1 join (select FIRSTNAME from PERSON) on ...
* @param {String|SqlBuilder} [pCondition] The where condition. This can be<br/>
* - a string (without the where keyword)<br/>
* - a SqlBuilder NOTE: only the condition is used from it
*
* @param {String} [pTableAlias] This alias is used to add an alias to the tablename
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.leftJoin = function(pTable, pCondition, pTableAlias)
{
return this.join(pTable, pCondition, pTableAlias, "left");
}
/**
* Adds a right join clause to the sql.
*
* @param {String|SqlBuilder} pTable if it is a String, it is used as it is as table<br/>
* if it is a SqlBuilder, it is used as subselect: e.g. select * from Table1 join (select FIRSTNAME from PERSON) on ...
* @param {String|SqlBuilder} [pCondition] The where condition. This can be<br/>
* - a string (without the where keyword)<br/>
* - a SqlBuilder NOTE: only the condition is used from it
*
* @param {String} [pTableAlias] This alias is used to add an alias to the tablename
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.rightJoin = function(pTable, pCondition, pTableAlias)
{
return this.join(pTable, pCondition, pTableAlias, "right");
}
/**
* Throws an error if pValue is null, undefined or a SqlBuilder without condition (or if pValue is a $-variable: error if the result of it is null or undefined)<br/>
* Also throws an error if pFieldOrCond is the only parameter and it is null<br/>
* <br/>
* Starts the where clause of the SQL. You may pass the first condition with it.<br/>
* But you can also call this function without any parameter and add the conditions with subsequent .and / .or<br/>
* <br/>
* This method exists mainly for semantic reasons and can only be callled once.<br/>
* As shourtcut you could use the newWhere(...) function.<br/>
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} [pFieldOrCond] If this is the only parameter, it is used as Condition <br/>
* else it is used as Field.<br/>
* <br/>
* If you use it as Subselect (the only param), it can be:<br/>
* - a string: just added as it is<br/>
* - a PreparedSqlArray: an Array in this form: [sqlStr, [[value1, type1], [valueN, typeN]]]<br/>
* the sql is just used as it is.<br/>
* - a SqlBuilder: ONLY THE CONDITION is used from it<br/>
* <br/>
* If you use it as a Field (at least pValue has to be filled), this param provides the field information to<br/>
* load the SQLTYPE for this condition. <br/>
* It can be provided in the following ways:<br/>
* - a string: ONLY in this form: "TABLENAME.COLUMNNAME" <br/>
* Note1: you may have problems with names containing a '.' Use the next variant (as array) in this case<br/>
* Note2: if you need a table alias use the next variant (as array)<br/> * - a array: ["TABLENAME", "COLUMNNAME", "tableAlias"] OR ["TABLENAME", "COLUMNNAME"]<br/>
* - a SqlBuilder: the full select is used as subselect and compared with pValue. <br/>
* (e.g. select * from PERSON where (select "NAME" from ORGANISATION where ... ) = ?)<br/>
* Note: for this you have to provide pFieldType as the type cannot be calculated from the subselect!<br/>
* Note: this can also be null if you don't need the field and use a pCondition without a #
*
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value which is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* - String, etc: is just used as value for the prepared statement. Of course it has to fit the type of the db-column<br/>
* - String starting with '$' is treated as jdito-variable: is loaded with vars.getString("$..."). <br/>
* Note: Use 2 '$' to escape the $ if you don't want it to be treated as JditoVar
*
* @param {String} [pCondition="# = ?"] This is the condition which should be used to compare the field with the value.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue<br/>
* <strong>IMPORTANT: the # has to be before the ?</strong>
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement <br/>
* In most cases you don't need this.<br/>
* This is helpful if you for example have a pCondition "year(#) = ?"<br/>
* then the db-field is DATETIME, but the value is INTEGER. In this case you can overwrite the type.
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.where = function(pFieldOrCond, pValue, pCondition, pFieldType)
{
// support for deprecated SqlCondition
if (pFieldOrCond instanceof SqlCondition && pValue === undefined && pCondition === undefined && pFieldType === undefined)
{
let copiedCondition = newWhere();
copiedCondition._where.preparedValues = pFieldOrCond.preparedValues;
copiedCondition._where._lastWasOr = pFieldOrCond._lastWasOr;
copiedCondition._where.sqlStorage = pFieldOrCond.sqlStorage;
pFieldOrCond = copiedCondition;
logging.log("Warning: using .where with a SqlCondition as pFieldOrCond is deprecated. The SqlCondition will be removed in version >= 2020.x\n"
+ "For SqlBuilder usage see the documentation-property of the Sql_lib.");
}
return this._setWhere(pFieldOrCond, pValue, pCondition, pFieldType, this.or);
}
/**
* Difference to where(): where throws errors on invalid values, whereIfSet just ignores the condition and does nothing (usefull e.g. for the parameter variables ("$param.ddd") in conditionProcesses.)<br/>
* <br/>
* Starts the whereIfSet clause of the SQL. You may pass the first condition with it.<br/>
* But you can also call this function without any parameter and add the conditions with subsequent .and / .or<br/>
* <br/>
* This method exists mainly for semantic reasons and can only be callled once.<br/>
* As shourtcut you could use the newWhereIfSet(...) function.
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} [pFieldOrCond] If this is the only parameter, it is used as Condition <br/>
* else it is used as Field.<br/>
* <br/>
* If you use it as Subselect (the only param), it can be:<br/>
* - a string: just added as it is<br/>
* - a PreparedSqlArray: an Array in this form: [sqlStr, [[value1, type1], [valueN, typeN]]]<br/>
* the sql is just used as it is.<br/>
* - a SqlBuilder: ONLY THE CONDITION is used from it<br/>
* <br/>
* If you use it as a Field (at least pValue has to be filled), this param provides the field information to<br/>
* load the SQLTYPE for this condition. <br/>
* It can be provided in the following ways:<br/>
* - a string: ONLY in this form: "TABLENAME.COLUMNNAME" <br/>
* Note1: you may have problems with names containing a '.' Use the next variant (as array) in this case<br/>
* Note2: if you need a table alias use the next variant (as array)<br/>
* - a array: ["TABLENAME", "COLUMNNAME", "tableAlias"] OR ["TABLENAME", "COLUMNNAME"]<br/>
* - a SqlBuilder: the full select is used as subselect and compared with pValue. <br/>
* (e.g. select * from PERSON where (select "NAME" from ORGANISATION where ... ) = ?)<br/> * Note: for this you have to provide pFieldType as the type cannot be calculated from the subselect!<br/>
* Note: this can also be null if you don't need the field and use a pCondition without a #
*
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value which is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* - String, etc: is just used as value for the prepared statement. Of course it has to fit the type of the db-column<br/>
* - String starting with '$' is treated as jdito-variable: is loaded with vars.getString("$..."). <br/>
* Note: Use 2 '$' to escape the $ if you don't want it to be treated as JditoVar
*
* @param {String} [pCondition="# = ?"] This is the condition which should be used to compare the field with the value.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue<br/>
* <strong>IMPORTANT: the # has to be before the ?</strong>
*
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement <br/>
* In most cases you don't need this.<br/>
* This is helpful if you for example have a pCondition "year(#) = ?"<br/>
* then the db-field is DATETIME, but the value is INTEGER. In this case you can overwrite the type.
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.whereIfSet = function(pFieldOrCond, pValue, pCondition, pFieldType)
{
return this._setWhere(pFieldOrCond, pValue, pCondition, pFieldType, this.orIfSet);
}
/**
* helper function for .where and .whereIfSet because they do almost the same<br/>
* See .where() for further explanations
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} [pFieldOrCond]
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue]
* @param {String} [pCondition="# = ?"] <strong>IMPORTANT: the # has to be before the ?</strong><br/>
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType]
* @param {SQLTYPES|Numeric} pAddCondFn=AutomaticallyLoadedType This is a callback which is called if a condition should be added (needs to have same parameters as .or()
*
* @return {SqlBuilder} current SqlBuilder object
* @ignore
*/
SqlBuilder.prototype._setWhere = function (pFieldOrCond, pValue, pCondition, pFieldType, pAddCondFn)
{
// allow where-call without parameter to just enable where mode
if (pFieldOrCond === undefined && pValue === undefined && pCondition === undefined && pFieldType === undefined)
{
this._where._whereWasCalled = true;
return this;
}
// where has to be called before all other and / or
if (this.hasCondition())
throw SqlBuilder._ERROR_WHERE_NOT_FIRST();
// only one where call is allowed
if (this._where._whereWasCalled)
throw SqlBuilder._ERROR_ONLY_ONE_WHERE();
this._where._whereWasCalled = true;
return pAddCondFn.call(this, pFieldOrCond, pValue, pCondition, pFieldType);
}
/**
* helper function which adds a condition
*
* @param {String|SqlBuilder|PreparedSqlArray} pCondition the condition to add
* @param {Boolean} [pMandatory=true] if true: throws error on SqlBuilder without conditon or PreparedSqlArray with empty string. Else: just does nothing
* @param {CallbackFunction} pAddPreparedConditionCallback A Callback funtion which receives a PreparedSqlArray as parameter
* @param {Boolean} pBrackets if true, Brackets are added in some cases
*
* @return {SqlBuilder} current SqlBuilder object
* @ignore */
SqlBuilder.prototype._whereCondition = function(pCondition, pMandatory, pAddPreparedConditionCallback, pBrackets)
{
if (pCondition === undefined)
return this;
if (pMandatory === undefined)
pMandatory = true;
var sql = pCondition;
var typeofSql = typeof sql;
// the field is a simple string -> just add the string, no prepared statement
if (typeofSql == "string")
{
pAddPreparedConditionCallback(this, [sql, []]);
return this;
}
// the field is an array -> it is a prepared condition
if (Array.isArray(sql))
{
if (sql[0])
{
this._where.preparedValues = this._where.preparedValues.concat(sql[1]);
// add only brackets if needed
if (pBrackets)
sql[0] = " ( " + sql[0] + " ) ";
pAddPreparedConditionCallback(this, [sql[0], []], pBrackets)
return this;
}
else if (pMandatory)
throw SqlBuilder._ERROR_CONDITION_IS_MANDATORY();
return this;
}
// the field is a SqlBuilder -> it is a SqlBuilder which contains a condition -> the condition of the SqlBuilder is added.
if (sql instanceof SqlBuilder)
{
// add only brackets if needed
var sqlString = sql._where.sqlStorage;
var condString = sqlString;
if (condString.trim() != "")
{
if (pBrackets)
condString = " ( " + condString + " ) ";
pAddPreparedConditionCallback(this, [condString, sql._where.preparedValues], pBrackets);
return this;
}
else if (pMandatory)
throw SqlBuilder._ERROR_CONDITION_IS_MANDATORY();
return this;
}
throw SqlBuilder._ERROR_INVALID_CONDITION_VALUE_TYPE();
}
/**
* helper function which adds a Subquery-condition
*
* @param {SqlBuilder|PreparedSqlArray} pSubquery the subquery to add
* @param {Boolean} [pMandatory=true] if true: throws error on SqlBuilder without conditon or PreparedSqlArray with empty string. Else: just does nothing
* @param {Boolean} pCondition the condition to be used: e.g. "exists(?)" the ? is replaced by the subquery * @param {CallbackFunction} pAddPreparedConditionCallback A Callback funtion which receives a PreparedSqlArray as parameter
*
* @return {SqlBuilder} current SqlBuilder object
* @ignore
*/
SqlBuilder.prototype._whereSubquery = function(pSubquery, pMandatory, pCondition, pAddPreparedConditionCallback)
{
if (pSubquery === undefined)
return this;
if (pMandatory === undefined)
pMandatory = true;
var sql = pSubquery;
// the field is an array -> it is a prepared statement which already SHOULD contain exists or another condition
// Both can be handled by _prepare
if (Array.isArray(sql))
{
if (sql[0])
pAddPreparedConditionCallback(this, this._prepare(undefined, sql, pCondition));
else if (pMandatory)
throw SqlBuilder._ERROR_VALUE_IS_MANDATORY();
return this;
}
// the field is a SqlBuilder -> it is a SqlBuilder which contains a condition -> the condition of the SqlBuilder is added.
if (sql instanceof SqlBuilder)
{
var subQuery = pSubquery;
// Without condition this function cannot be used with SqlBuilder object as it cannot contain a condition
if (!pCondition)
throw SqlBuilder._ERROR_NO_CONDITION();
if (subQuery.isFullSelect())
{
var preparedObj = subQuery.build();
pAddPreparedConditionCallback(this, this._prepare(undefined, preparedObj, pCondition));
}
else if (pMandatory)
throw SqlBuilder._ERROR_VALUE_IS_MANDATORY();
return this;
}
throw SqlBuilder._ERROR_INVALID_SUBQUERY_TYPE();
}
/**
* helper function which adds a condition to the where
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} pFieldOrCond see .where()
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} pValue see .where()
* @param {Boolean} [pMandatory=true] if true: throw error if pValue is null, undefined, SqlBuilder without condition, etc... else just ignore the condition
* @param {String} [pCondition="# = ?"] see .where()
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] see .where()
* @param {CallbackFunction} pAddPreparedConditionCallback A Callback funtion which receives a PreparedSqlArray as parameter
*
* @return {SqlBuilder} current SqlBuilder object
* @ignore
*/
SqlBuilder.prototype._addWhere = function(pFieldOrCond, pValue, pMandatory, pCondition, pFieldType, pAddPreparedConditionCallback)
{
//In a special case, pCondition can be a function. It will be called with the alias as argument and
//must return an array of the condition string and (optionally) the required sql field type.
//alternatively the function may return a string only to make the usage more bulletproof and convenient, so both SqlBuilder.EQUAL()
//and SqlBuilder.EQUAL work equally
if (pCondition && typeof pCondition === "function") {
var resCond = pCondition(this.alias);
if (Array.isArray(resCond))
{
pCondition = resCond[0];
pFieldType = pFieldType || resCond[1];
}
else if(Utils.isString(pCondition))
{
pCondition = resCond;
}
}
if (pCondition && !SqlUtils.checkConditionFormat(pCondition))
throw SqlBuilder._ERROR_CONDITION_WRONG_FORMAT();
if (pMandatory === undefined)
pMandatory = true;
if (!this._where._whereWasCalled)
throw SqlBuilder._ERROR_WHERE_NOT_FIRST();
if (!pMandatory && pFieldOrCond === undefined && pValue === undefined && pCondition === undefined && pFieldType === undefined)
return this;
if (pFieldOrCond === undefined && pValue === undefined && pCondition === undefined && pFieldType === undefined)
throw SqlBuilder._ERROR_NO_PARAMETER_PROVIDED();
// Special case: if only pFieldOrCond is set and we can identify it as a valid field-string (e.g. "Table.Field") we assume that it is not just a condition string.
// --> we can check pValue for undefined and also allow simple string-conditions
// --> this only works if isFullFieldQualifier() can detect if the supplied string is a valid field-string or if it is some sql.
// currently it checks for some special characters which should not exist in any field-string but in conditions.
// If there is a special case missing -> add it to the regexp in isFullFieldQualifier()
if (pValue === undefined && pCondition === undefined && pFieldType === undefined && typeof pFieldOrCond == "string" && SqlUtils.isFullFieldQualifier(pFieldOrCond))
{
if (pMandatory)
throw SqlBuilder._ERROR_VALUE_IS_MANDATORY();
else
return this;
}
// just call the andCondition function if it is only a Condition
if (pFieldOrCond !== undefined && pValue === undefined && pCondition === undefined && pFieldType === undefined)
return this._whereCondition(pFieldOrCond, pMandatory, pAddPreparedConditionCallback, true);
// Subselects containing full select can be used as field, if pValue and pFieldType are provided.
if (pFieldOrCond instanceof SqlBuilder)
{
if (!pFieldOrCond.isFullSelect())
{
throw SqlBuilder._ERROR_SUBSELECT_AS_FIELD_NOT_COMPLETE();
}
if (!pFieldType)
throw SqlBuilder._ERROR_SUBSELECT_AS_FIELD_NO_FIELD_TYPE();
var tmpCond = newWhere(this.alias)
._addWhere("SQL_LIB_DUMMY_TABLE.SQL_LIB_DUMMY_COLUMN", pValue, pMandatory, pCondition, pFieldType, pAddPreparedConditionCallback);
var subSqlPrepared = pFieldOrCond.build();
tmpCond._where.sqlStorage = SqlUtils.replaceConditionTemplate(tmpCond._where.sqlStorage, 'SQL_LIB_DUMMY_TABLE.SQL_LIB_DUMMY_COLUMN', "( " + subSqlPrepared[0] + " )");
tmpCond._where.preparedValues = subSqlPrepared[1].concat(tmpCond._where.preparedValues)
this._whereCondition(tmpCond, pMandatory, pAddPreparedConditionCallback, true)
return this;
}
// first check the default-mandatory-cases: null or undefined. everything else such as checking $-variables is done later
if (pMandatory && (pValue === null || pValue === undefined)) throw SqlBuilder._ERROR_VALUE_IS_MANDATORY();
// a field is string or array -> normal case
// OR !pFieldOrCond and pValue and pCondition is given -> preparedSQL/SqlBuilder can be used without field if pCondition is set (e.g. with "exists ?")
if(((typeof pFieldOrCond == "string" || Array.isArray(pFieldOrCond) || (!pFieldOrCond && pFieldType)) || (!pFieldOrCond && (pCondition && pValue instanceof SqlBuilder || !(pValue instanceof SqlBuilder)))))
{
var field = pFieldOrCond;
var typeofValue = typeof pValue;
// ... a string starting with $ -> jdito varable which has to be resolved
if (typeofValue == "string" && pValue.length >= 2 && pValue[0] == "$" && pValue[1] != "$") // escape $ if using two $
{
//important: just overwrite the value because some $local variables may contain an array and then the default handling of arrays (which
//is generating an IN-statement) should apply
pValue = vars.get(pValue);
if (pMandatory && pValue === null)
throw SqlBuilder._ERROR_VALUE_IS_MANDATORY_JDITO_VAR();
typeofValue = typeof pValue;
}
// remove the first $ if there are two $
if (typeofValue == "string" && pValue.length >= 2 && pValue[0] == "$" && pValue[1] == "$")
pValue = pValue.slice(1);
//support for Set by converting to Array
if (pValue instanceof Set)
pValue = Array.from(pValue);
// pValue can be...
// ... a SqlBuilder / Prepared statement array -> it is a SqlBuilder containing a complete subquery or an simple array (in statement)
if (pValue instanceof SqlBuilder || Array.isArray(pValue) || (typeofValue == "string" && (pFieldOrCond == undefined || pFieldOrCond == null)))
{
// check if the array is really a value-array for an in and not a prepared statement
if (Array.isArray(pValue) && (pValue.length <= 1 || !Array.isArray(pValue[1])))
{
if (pValue.length == 0)
{
if (pMandatory)
throw SqlBuilder._ERROR_VALUE_IS_MANDATORY();
return this;
}
// if it is null -> ignore it. -> the pCondition should not contain a # in this case
if (field != null)
{
var [alias, parsedField] = SqlUtils.parseField(field)
if (pFieldType === undefined || pFieldType === null)
pFieldType = SqlUtils.getSingleColumnType(parsedField, undefined, this.alias);
}
//overwrite condition to set a default behaviour
if (pCondition == undefined)
pCondition = SqlBuilder.IN();
// value-array -> convert it to a prepared statement ["(?, ?, ?)", [[val1, type1], [val2, type2], [val3, type3]]]
this._whereCondition(this._prepare(field, SqlUtils.getSqlInStatement(undefined, pValue, undefined, true, pFieldType), pCondition, pFieldType, false), undefined, pAddPreparedConditionCallback, true);
return this;
}
if (pFieldOrCond !== null && pFieldOrCond !== undefined)
{
if (!pCondition)
pCondition = SqlBuilder.EQUAL();
pCondition = SqlUtils.replaceConditionTemplate(pCondition, '#', SqlUtils.parseField(pFieldOrCond)[0])
}
else
{
if (!pCondition)
pCondition = "?"
}
// _whereSubquery can handle SqlBuilder and prepared statements as value
return this._whereSubquery(pValue, pMandatory, pCondition, pAddPreparedConditionCallback);
}
if (!pCondition)
pCondition = SqlBuilder.EQUAL();
// ... everything else -> just pass it
if (pValue === false || pValue === 0 || pValue === "" || pValue)
{
let prep = this._prepare(field, pValue, pCondition, pFieldType)
this._whereCondition(prep, undefined, pAddPreparedConditionCallback);
}
return this;
}
throw SqlBuilder._ERROR_UNSUPPORTED_PARAMETER_COMBINATION();
}
/**
* helper function to add a condition via "and"
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} pFieldOrCond see .where()
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} pValue see .where()
* @param {Boolean} [pMandatory=true] if true: throw error if pValue is null, undefined, SqlBuilder without condition, etc... else just ignore the condition
* @param {String} [pCondition="# = ?"] see .where()
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] see .where()
*
* @ignore
*/
SqlBuilder.prototype._and = function(pFieldOrCond, pValue, pMandatory, pCondition, pFieldType)
{
return this._addWhere(pFieldOrCond, pValue, pMandatory, pCondition, pFieldType, function(that, pPreparedCondition)
{
that._where._previouslyOnlyOr = false;
if (pPreparedCondition.length == 2 && typeof pPreparedCondition[0] == "string" && pPreparedCondition[0] != "" && Array.isArray(pPreparedCondition[1]))
{
if (that.hasCondition())
that._where.sqlStorage += " and ";
that._where.sqlStorage += pPreparedCondition[0];
that._where.preparedValues = that._where.preparedValues.concat(pPreparedCondition[1]);
}
});
}
/**
* helper function to add a condition via "or"
* The callback inside of this function adds brackets where needed.
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} pFieldOrCond see .where()
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} pValue see .where()
* @param {Boolean} [pMandatory=true] if true: throw error if pValue is null, undefined, SqlBuilder without condition, etc... else just ignore the condition
* @param {String} [pCondition="# = ?"] see .where()
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] see .where()
*
* @ignore
*/
SqlBuilder.prototype._or = function(pFieldOrCond, pValue, pMandatory, pCondition, pFieldType)
{
return this._addWhere(pFieldOrCond, pValue, pMandatory, pCondition, pFieldType, function(that, pPreparedCondition, pAlreadySurroundedByBrackets)
{
if (pPreparedCondition.length == 2 && typeof pPreparedCondition[0] == "string" && pPreparedCondition[0] != "" && Array.isArray(pPreparedCondition[1]))
{
if (that._where._previouslyOnlyOr)
{
that._where.sqlStorage = that._where.sqlStorage + " or " + pPreparedCondition[0]; that._where._lastWasOr = true;
}
else if (that.hasCondition())
{
let cond = pPreparedCondition[0];
if (!pAlreadySurroundedByBrackets)
cond = "(" + cond + ")";
if (that._where._lastWasOr)
that._where.sqlStorage = that._where.sqlStorage + " or " + cond;
else
that._where.sqlStorage = "(" + that._where.sqlStorage + ") or " + cond;
that._where._lastWasOr = true;
}
else
{
if (!that.hasCondition())
that._where._previouslyOnlyOr = true;
that._where.sqlStorage = pPreparedCondition[0];
}
that._where.preparedValues = that._where.preparedValues.concat(pPreparedCondition[1]);
}
});
}
/**
* Constant-like function which provides a value for pCondition if you need a "not in" statement.
*
* @return {String}
*
* @example
* var cond = newWhere("PERSON.FIRSTNAME", ["Fritz"], SqlBuilder.NOT_IN())
*/
SqlBuilder.NOT_IN = function()
{
return "# not in ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "in" statement.
*
* @return {String}
*
* @example
* var cond = newWhere("PERSON.FIRSTNAME", ["Fritz"], SqlBuilder.IN())
*/
SqlBuilder.IN = function()
{
return "# in ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "exists" statement.
*
* @return {String}
*
* @example
* var cond = newWhere(null, mySubSqlBuilder, SqlBuilder.EXISTS())
*/
SqlBuilder.EXISTS = function()
{
return "exists ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "not exists" statement.
* * @return {String}
*
* @example
* var cond = newWhere(null, mySubSqlBuilder, SqlBuilder.NOT_EXISTS())
*/
SqlBuilder.NOT_EXISTS = function()
{
return "not exists ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "year(#) = ?" statement.
* If you use this, the default pFieldType will be SQLTYPES.INTEGER.
*
* @return {Function}
*
* @example
* var cond = newWhere("FORECAST.DATE_START", DateUtils.getCurrentYear(), SqlBuilder.YEAR_EQUALS());
*/
SqlBuilder.YEAR_EQUALS = function ()
{
//function will be called later so it can use the alias of the SqlBuilder
return function (pAlias) {return [(new SqlMaskingUtils(pAlias).yearFromDate("#")) + " = ?", SQLTYPES.INTEGER];};
}
/**
* Constant-like function which provides a value for pCondition if you need a "# = ?" statement.
* This is the default for the pCondition parameter, so it can be omitted.
*
* @return {String}
*
* @example
* var cond = newWhere("PERSON.FIRSTNAME", "Fritz", SqlBuilder.EQUAL())
*/
SqlBuilder.EQUAL = function ()
{
return "# = ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "# <> ?" statement.
*
* @return {String}
*
* @example
* var cond = newWhere("PERSON.FIRSTNAME", "Fritz", SqlBuilder.NOT_EQUALS())
*/
SqlBuilder.NOT_EQUAL = function ()
{
return "# <> ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "# like ?" statement.
*
* @return {String}
*
* @example
* var cond = newWhere("PERSON.FIRSTNAME", "F%", SqlBuilder.LIKE())
*/
SqlBuilder.LIKE = function ()
{
return "# like ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "# like ?" statement.
*
* @return {String}
* * @example
* var cond = newWhere("PERSON.FIRSTNAME", "F%", SqlBuilder.NOT_LIKE())
*/
SqlBuilder.NOT_LIKE = function ()
{
return "# not like ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "# > ?" statement.
*
* @return {String}
*/
SqlBuilder.GREATER = function ()
{
return "# > ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "# < ?" statement.
*
* @return {String}
*/
SqlBuilder.LESS = function ()
{
return "# < ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "# >= ?" statement.
*
* @return {String}
*/
SqlBuilder.GREATER_OR_EQUAL = function ()
{
return "# >= ?";
}
/**
* Constant-like function which provides a value for pCondition if you need a "# <= ?" statement.
*
* @return {String}
*/
SqlBuilder.LESS_OR_EQUAL = function ()
{
return "# <= ?";
}
/**
* Constant-like function which returns an impossible condition ("1 = 2").
*
* @return {String}
*/
SqlBuilder.NORESULT_CONDITION = function ()
{
return "1 = 2";
}
/**
* Object providing constant-like functions for sql-any-conditions.
*/
SqlBuilder.ANY = {
/**
* Constant-like function that returns a "# = any ?" statement.
*/
EQUAL : function () {return "# = any ?";},
/**
* Constant-like function that returns a "# <> any ?" statement.
*/
NOT_EQUAL : function () {return "# <> any ?";}, /**
* Constant-like function that returns a "# > any ?" statement.
*/
GREATER : function () {return "# > any ?";},
/**
* Constant-like function that returns a "# >= any ?" statement.
*/
GREATER_OR_EQUAL : function () {return "# >= any ?";},
/**
* Constant-like function that returns a "# < any ?" statement.
*/
LESS : function () {return "# < any ?";},
/**
* Constant-like function that returns a "# <= any ?" statement.
*/
LESS_OR_EQUAL : function () {return "# <= any ?";}
}
/**
* Object providing constant-like functions for sql-all-conditions.
*/
SqlBuilder.ALL = {
/**
* Constant-like function that returns a "# = all ?" statement.
*/
EQUAL : function () {return "# = all ?";},
/**
* Constant-like function that returns a "# <> all ?" statement.
*/
NOT_EQUAL : function () {return "# <> all ?";},
/**
* Constant-like function that returns a "# > all ?" statement.
*/
GREATER : function () {return "# > all ?";},
/**
* Constant-like function that returns a "# >= all ?" statement.
*/
GREATER_OR_EQUAL : function () {return "# >= all ?";},
/**
* Constant-like function that returns a "# < all ?" statement.
*/
LESS : function () {return "# < all ?";},
/**
* Constant-like function that returns a "# <= all ?" statement.
*/
LESS_OR_EQUAL : function () {return "# <= all ?";}
}
/**
* Throws an error if pValue is null, undefined or a SqlBuilder without condition (or if pValue is a $-variable: error if the result of it is null or undefined)<br/>
* Also throws an error if pFieldOrCond is the only parameter and it is null<br/>
* <br/>
* Adds a condition by using "or" to the Sql.<br/>
* Note: You have to call .where before using .and / .or (this is mainly for semantic reasons)
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} [pFieldOrCond] If this is the only parameter, it is used as Condition <br/>
* else it is used as Field.<br/>
* <br/>
* If you use it as Subselect (the only param), it can be:<br/>
* - a string: just added as it is<br/>
* - a PreparedSqlArray: an Array in this form: [sqlStr, [[value1, type1], [valueN, typeN]]]<br/>
* the sql is just used as it is.<br/>
* - a SqlBuilder: ONLY THE CONDITION is used from it<br/>
* <br/>
* If you use it as a Field (at least pValue has to be filled), this param provides the field information to<br/>
* load the SQLTYPE for this condition. <br/>
* It can be provided in the following ways:<br/>
* - a string: ONLY in this form: "TABLENAME.COLUMNNAME" <br/>
* Note1: you may have problems with names containing a '.' Use the next variant (as array) in this case<br/>
* Note2: if you need a table alias use the next variant (as array)<br/> * - a array: ["TABLENAME", "COLUMNNAME", "tableAlias"] OR ["TABLENAME", "COLUMNNAME"]<br/>
* Note: this can also be null if you don't need the field and use a pCondition without a #
*
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value which is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* - String, etc: is just used as value for the prepared statement. Of course it has to fit the type of the db-column<br/>
* - String starting with '$' is treated as jdito-variable: is loaded with vars.getString("$..."). <br/>
* Note: Use 2 '$' to escape the $ if you don't want it to be treated as JditoVar
*
* @param {String} [pCondition="# = ?"] This is the condition which should be used to compare the field with the value.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue
*
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement <br/>
* In most cases you don't need this.<br/>
* This is helpful if you for example have a pCondition "year(#) = ?"<br/>
* then the db-field is DATETIME, but the value is INTEGER. In this case you can overwrite the type.
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.or = function(pFieldOrCond, pValue, pCondition, pFieldType)
{
return this._or(pFieldOrCond, pValue, true, pCondition, pFieldType);
}
/**
* Difference to or(): or() throws errors on invalid values, orIfSet just ignores the condition and does nothing (usefull e.g. for the parameter variables ("$param.ddd") in conditionProcesses.)<br/>
* <br/>
* Adds a condition by using "or" to the Sql.<br/>
* Note: You have to call .where before using .and / .or (this is mainly for semantic reasons)
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} [pFieldOrCond] If this is the only parameter, it is used as Condition <br/>
* else it is used as Field.<br/>
* <br/>
* If you use it as Subselect (the only param), it can be:<br/>
* - a string: just added as it is<br/>
* - a PreparedSqlArray: an Array in this form: [sqlStr, [[value1, type1], [valueN, typeN]]]<br/>
* the sql is just used as it is.<br/>
* - a SqlBuilder: ONLY THE CONDITION is used from it<br/>
* <br/>
* If you use it as a Field (at least pValue has to be filled), this param provides the field information to<br/>
* load the SQLTYPE for this condition. <br/>
* It can be provided in the following ways:<br/>
* - a string: ONLY in this form: "TABLENAME.COLUMNNAME" <br/>
* Note1: you may have problems with names containing a '.' Use the next variant (as array) in this case<br/>
* Note2: if you need a table alias use the next variant (as array)<br/>
* - a array: ["TABLENAME", "COLUMNNAME", "tableAlias"] OR ["TABLENAME", "COLUMNNAME"]<br/>
* Note: this can also be null if you don't need the field and use a pCondition without a #
*
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value which is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* - String, etc: is just used as value for the prepared statement. Of course it has to fit the type of the db-column<br/>
* - String starting with '$' is treated as jdito-variable: is loaded with vars.getString("$..."). <br/>
* Note: Use 2 '$' to escape the $ if you don't want it to be treated as JditoVar
*
* @param {String} [pCondition="# = ?"] This is the condition which should be used to compare the field with the value.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue
*
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement <br/>
* In most cases you don't need this.<br/>
* This is helpful if you for example have a pCondition "year(#) = ?"<br/>
* then the db-field is DATETIME, but the value is INTEGER. In this case you can overwrite the type.
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.orIfSet = function(pFieldOrCond, pValue, pCondition, pFieldType)
{
return this._or(pFieldOrCond, pValue, false, pCondition, pFieldType);
}
/**
* Throws an error if pValue is null, undefined or a SqlBuilder without condition (or if pValue is a $-variable: error if the result of it is null or undefined)<br/>
* Also throws an error if pFieldOrCond is the only parameter and it is null<br/>
* <br/>
* Adds a condition by using "and" to the Sql.<br/>
* Note: You have to call .where before using .and / .or (this is mainly for semantic reasons)
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} [pFieldOrCond] If this is the only parameter, it is used as Condition <br/>
* else it is used as Field.<br/>
* <br/>
* If you use it as Subselect (the only param), it can be:<br/>
* - a string: just added as it is<br/>
* - a PreparedSqlArray: an Array in this form: [sqlStr, [[value1, type1], [valueN, typeN]]]<br/>
* the sql is just used as it is.<br/>
* - a SqlBuilder: ONLY THE CONDITION is used from it<br/>
* <br/>
* If you use it as a Field (at least pValue has to be filled), this param provides the field information to<br/>
* load the SQLTYPE for this condition. <br/>
* It can be provided in the following ways:<br/>
* - a string: ONLY in this form: "TABLENAME.COLUMNNAME" <br/>
* Note1: you may have problems with names containing a '.' Use the next variant (as array) in this case<br/>
* Note2: if you need a table alias use the next variant (as array)<br/>
* - a array: ["TABLENAME", "COLUMNNAME", "tableAlias"] OR ["TABLENAME", "COLUMNNAME"]<br/>
* Note: this can also be null if you don't need the field and use a pCondition without a #
*
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value which is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* - String, etc: is just used as value for the prepared statement. Of course it has to fit the type of the db-column<br/>
* - String starting with '$' is treated as jdito-variable: is loaded with vars.getString("$..."). <br/>
* Note: Use 2 '$' to escape the $ if you don't want it to be treated as JditoVar
*
* @param {String} [pCondition="# = ?"] This is the condition which should be used to compare the field with the value.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue
*
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement <br/>
* In most cases you don't need this.<br/>
* This is helpful if you for example have a pCondition "year(#) = ?"<br/>
* then the db-field is DATETIME, but the value is INTEGER. In this case you can overwrite the type.
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.and = function(pFieldOrCond, pValue, pCondition, pFieldType)
{
return this._and(pFieldOrCond, pValue, true, pCondition, pFieldType);
}
/**
* Difference to and(): and() throws errors on invalid values, andIfSet just ignores the condition and does nothing (usefull e.g. for the parameter variables ("$param.ddd") in conditionProcesses.)<br/>
* <br/>
* Adds a condition by using "and" to the Sql.<br/>
* Note: You have to call .where before using .and / .or (this is mainly for semantic reasons)
*
* @param {String|String[]|SqlBuilder|PreparedSqlArray|null} [pFieldOrCond] If this is the only parameter, it is used as Condition <br/>
* else it is used as Field.<br/>
* <br/>
* If you use it as Subselect (the only param), it can be:<br/>
* - a string: just added as it is<br/>
* - a PreparedSqlArray: an Array in this form: [sqlStr, [[value1, type1], [valueN, typeN]]]<br/>
* the sql is just used as it is.<br/>
* - a SqlBuilder: ONLY THE CONDITION is used from it<br/>
* <br/>
* If you use it as a Field (at least pValue has to be filled), this param provides the field information to<br/>
* load the SQLTYPE for this condition. <br/>
* It can be provided in the following ways:<br/>
* - a string: ONLY in this form: "TABLENAME.COLUMNNAME" <br/>
* Note1: you may have problems with names containing a '.' Use the next variant (as array) in this case<br/>
* Note2: if you need a table alias use the next variant (as array)<br/>
* - a array: ["TABLENAME", "COLUMNNAME", "tableAlias"] OR ["TABLENAME", "COLUMNNAME"]<br/> * Note: this can also be null if you don't need the field and use a pCondition without a #
*
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value which is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* - String, etc: is just used as value for the prepared statement. Of course it has to fit the type of the db-column<br/>
* - String starting with '$' is treated as jdito-variable: is loaded with vars.getString("$..."). <br/>
* Note: Use 2 '$' to escape the $ if you don't want it to be treated as JditoVar
*
* @param {String} [pCondition="# = ?"] This is the condition which should be used to compare the field with the value.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue
*
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement <br/>
* In most cases you don't need this.<br/>
* This is helpful if you for example have a pCondition "year(#) = ?"<br/>
* then the db-field is DATETIME, but the value is INTEGER. In this case you can overwrite the type.
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.andIfSet = function(pFieldOrCond, pValue, pCondition, pFieldType)
{
return this._and(pFieldOrCond, pValue, false, pCondition, pFieldType);
}
/**
* Sets the order by clause of the sql.
*
* @param {String|String[]} pOrderBy a string is added as it is, a array is concatenated by ', '
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.orderBy = function(pOrderBy)
{
this._orderBy = SqlBuilder._getStatement(pOrderBy, "order by", undefined, true);
return this;
}
/**
* Sets the group by clause of the sql.
*
* @param {String|String[]} pFields a string is added as it is, a array is concatenated by ', '
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.groupBy = function(pFields)
{
this._groupBy = SqlBuilder._getStatement(pFields, "group by", undefined, true);
return this;
}
/**
* Adds another SqlBuilder object or select string with union.
*
* @param {SqlBuilder|String} pSelect
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.union = function(pSelect)
{
this._unions.push(SqlBuilder._getStatement(pSelect, "union"));
return this;
}
/**
* Adds another SqlBuilder object or select string with union all.
*
* @param {SqlBuilder|String} pSelect
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.unionAll = function(pSelect)
{
this._unions.push(SqlBuilder._getStatement(pSelect, "union all"));
return this;}
/**
* Adds a having clause to the sql.
*
* @param {String|SqlBuilder} [pCondition] The having condition. This can be
* - a string (without the where keyword)
* - a SqlBuilder NOTE: only the condition is used from it
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.having = function(pCondition)
{
this._having = SqlBuilder._getStatement(pCondition, "having");
return this;
}
/**
* checks if conditions have been added to the object
* @return {Boolean} true if conditions have been added, false when not
*/
SqlBuilder.prototype.hasCondition = function() {
if (this._where.sqlStorage)
return true;
return false;
}
/**
* checks if conditions have been added to the object
* Note: this does not nessecarily mean that hasCondition() is true
* @return {Boolean} true if .where was already called
*/
SqlBuilder.prototype.whereWasCalled = function() {
return this._where._whereWasCalled;
}
/**
* checks if all mandatory parts to execute the select have been added to the object
* ("select" and "from" parts)
* @return {Boolean} true if select and from have been added, false if not
*/
SqlBuilder.prototype.isFullSelect = function()
{
if (!this._select || !this._from)
return false;
return true;
}
/**
* Function that resets the current where-condition as if no conditions would have been added
* this is usefull if you want to reuse the same Builder over and over again with a different condition.
* This also resets whether where was already called, so you have to use .where to add a condition after this.
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.clearWhere = function()
{
this._initWhere();
return this;
}
/**
* function that initializes the properties of the ._where object, this is used in the
* constructor and .clearWhere
*/
SqlBuilder.prototype._initWhere = function ()
{
//TODO: maybe put conditions in an object/array for better internal object structure
this._where.sqlStorage = ""; this._where.preparedValues = [];
this._where._lastWasOr = false; // save, if the last condition was an OR. For better bracket-placement
this._where._previouslyOnlyOr = false; // also for better bracket-placement
this._where._whereWasCalled = false; // where has always to be called first for a better semantic
}
/**
* helper function for composing preparedStatements <br/>
* <br/>
* see .where for more information about the parameters
*
* @param {String | String[]} pField the database field as "tablename.columnname"; e.g. "ORGANISATION.NAME" or as array with column-alias: ["ORGANISATION", "NAME", "myorgAlias"]
* @param {String} pValue the value that shall be set into the prepared statement
* @param {String} pCondition the strucutre of the SQL condition as preparedString, you can use a number sign "#" as placeholder for you fieldname; <br/>
* e.g. "# > ?"; escaping the number sign is possible with a backslash "\" <br/>
* Default is "# = ?"
* @param {Numeric | Boolean} [pFieldType] SQL-column-type; if the fieldType is not given it's loaded automatically; <br/>
* The loaded type is cached if no type is given. So it is also safe to use this in a loop. <br/>
* e.g. <br/>
* for (...) { <br/>
* cond.andPrepare("SALESPROJECT_CLASSIFICATION.TYPE", entry, "# <> ?") <br/>
* }
* @param {Boolean} pSubselectBrackets if true, brackets are added to subselects
* @return {PreparedSqlArray} a preparedSqlArray built out of the given parameters
*
* @ignore
*/
SqlBuilder.prototype._prepare = function(pField, pValue, pCondition, pFieldType, pSubselectBrackets)
{
if (pSubselectBrackets == undefined)
pSubselectBrackets = true;
if (pValue == undefined)
throw new Error(translate.withArguments("${SQL_LIB_UNDEFINED_VALUE} field: %0", [pField]));
if (pCondition == undefined)
pCondition = SqlBuilder.EQUAL();
var alias, field;
if (pField != null)
{
[alias, field] = SqlUtils.parseField(pField)
if (pFieldType == undefined)
pFieldType = SqlUtils.getSingleColumnType(pField, undefined, this.alias);
var table = SqlUtils._parseFieldQualifier(pField).table;
//Set the table for update/delete if it isn't already set, so you don't need to specify the table if the where-condition contains it
if (table && !this._tableName)
this._tableName = table;
}
var values = [];
// If subselect: replace '?' with the subselect
if (Array.isArray(pValue))
{
pCondition = SqlUtils.replaceConditionTemplate(pCondition, "\\?", (pSubselectBrackets ? " ( " : " ") + pValue[0] + (pSubselectBrackets ? " ) " : " "));
values = pValue[1];
}
else
{
var type = pFieldType;
values = [[pValue.toString(), type]];
}
if (pField != null)
pCondition = SqlUtils.replaceConditionTemplate(pCondition, "#", alias);
return [pCondition, values];
}
/**
* generates a part of the sql
*
* @param {String|String[]|SqlBuilder} pElement the element to append
* @param {String} [pPrefix] string to be added before pElement
* @param {String} [pPostfix] string to be added after pElement
* @param {Boolean} [pAutoJoin=false] if this is true and pElement is an array, it will be automatically <br/>
* joined together to a string
* @param {Boolean} [pUseSubselectAlias=false] if true the subselectAlias is added if the element is a subquery
*
* @ignore
*/
SqlBuilder._getStatement = function (pElement, pPrefix, pPostfix, pAutoJoin, pUseSubselectAlias)
{
var preparedValues = [];
if (typeof pElement !== "string")
{
if (Array.isArray(pElement) && pElement.length !== undefined && pAutoJoin) //array of fields
{
for (let i = 0; i < pElement.length; i++)
{
if (typeof pElement[i] !== "string")
pElement[i] = _getElement(pElement[i]);
}
pElement = ArrayUtils.joinNonEmptyFields(pElement, ", ");
}
else
{
pElement = _getElement(pElement);
}
}
if (pPrefix && pElement)
pElement = pPrefix + " " + pElement;
if (pPostfix && pElement)
pElement += " " + pPostfix;
return {
preparedValues: preparedValues,
sqlStorage: pElement.toString()
};
function _getElement (element)
{
var isSubQuery = false;
var subselectAlias = "";
if (SqlBuilder.checkCanBuildSql(element))
{
if (element instanceof SqlBuilder && element.isFullSelect())
{
isSubQuery = true;
if (pUseSubselectAlias && element._subselectAlias)
subselectAlias = " " + element._subselectAlias;
}
element = element.build();
}
preparedValues = preparedValues.concat(element[1]);
if (isSubQuery || pAutoJoin)
return "(" + element[0] + ")" + subselectAlias;
return element[0];
}
}
/**
* builds a prepared condition out of the object. Only the condition is used. Select, from, ... are ignored.
*
* @return {PreparedSqlArray} prepared condition */
SqlBuilder.prototype.buildCondition = function()
{
return [this._where.sqlStorage, this._where.preparedValues];
}
/**
* builds a prepared statement out of the object. If a part doesn't exit, it's just ignored.
*
* @param {String} [pDefaultConditionIfNone=""] a default condition string which should be used if the SqlBuilder doesn't have any condition
* @return {PreparedSqlArray} prepared statement
*/
SqlBuilder.prototype.build = function(pDefaultConditionIfNone)
{
var wherePrefix = "";
if (this.isFullSelect())
{
if (this._where.sqlStorage)
wherePrefix = "where ";
}
var whereSql = this._where.sqlStorage;
if (!this.hasCondition() && pDefaultConditionIfNone)
whereSql = wherePrefix + pDefaultConditionIfNone;
var whereObj = {
sqlStorage : wherePrefix + whereSql,
preparedValues : this._where.preparedValues
}
var allParts = [
this._select,
this._from,
].concat(this._joins).concat([
whereObj,
this._groupBy,
this._having,
this._orderBy
]).concat(this._unions);
var sqlStr = "";
var preparedVals = [];
for (let i = 0, l = allParts.length; i < l; i++)
{
let part = allParts[i];
if (part)
{
if (sqlStr && part.sqlStorage)
sqlStr += " ";
sqlStr += part.sqlStorage;
if (part.preparedValues.length)
preparedVals = preparedVals.concat(part.preparedValues);
}
}
return [sqlStr, preparedVals];
}
/**
* Updates data in the database.<br/>
* Note: the default for pExecuteOnlyIfConditionExists is true to prevent updating all rows if the SqlBuilder has no condition.
*
* @param {Boolean} [pExecuteOnlyIfConditionExists=true] If true, the update is only done if there is a condition.<br/>
* <strong>IMPORTANT: If this is set to false and there is no condition, every row in the table will be updated!</strong>
* @param {String} [pTableName] The table for updating data. If undefined, the from part of the SqlBuilder will be used (works only if it is a tablename). If no from is set,
* the table of the first where-condition is used.
* @param {String[]} pColumns The columns where you want to update.
* @param {SQLTYPES[]} [pColumnTypes=null] normally you can set this to null as the types are calculated if not provided * @param {String[]} pValues The values to be updated.
* @param {Number} [pTimeout=-1]
* @return {Number} the number of rows affected
* @throws {Error} if no table is defined
*/
SqlBuilder.prototype.updateData = function(pExecuteOnlyIfConditionExists, pTableName, pColumns, pColumnTypes, pValues, pTimeout)
{
if (this._checkForUpdate(pExecuteOnlyIfConditionExists))
{
if (!pTableName && !this._tableName)
throw SqlBuilder._ERROR_NO_TABLE();
if (!pColumns)
pColumns = null;
return db.updateData(
(pTableName ? pTableName : this._tableName),
pColumns,
pColumnTypes,
pValues,
this.buildCondition(),
(this.alias ? this.alias : db.getCurrentAlias()),
(pTimeout ? pTimeout : -1));
}
return 0;
}
/**
* Updates data in the database. This function calls SqlBuilder.prototype.updateData, but provides a shorter syntax to
* improve the readability.
*
* @param {Object|Map} pFieldValues Object with the columns to update as keys mapped to their values
* @param {String} [pTableName] The table for updating data. If undefined, the from part of the SqlBuilder will be used (works only if it is a tablename). If no from is set,
* the table of the first where-condition is used.
* @return {Number} the number of rows affected
* @example
* newWhere("SALESORDER.SALESORDERID", "$field.SALESORDERID")
* .updateFields({"ORDERSTATUS" : "1"}); //pTableName can be omitted here since it's clearly defined by the given condition
*/
SqlBuilder.prototype.updateFields = function (pFieldValues, pTableName)
{
if (!pFieldValues || typeof(pFieldValues) !== "object")
throw SqlBuilder._ERROR_UPDATE_VALUES_INVALID;
var columnValues = SqlBuilder._columnsValuesFromObject(pFieldValues, true);
if (columnValues.columns.length === 0)
return 0;
return this.updateData(true, pTableName, columnValues.columns, null, columnValues.values);
}
/**
* Builds an array containing the table and condition for an update.
*
* @param {Object|Map} pFieldValues Object with the columns to update as keys mapped to their values
* @param {String} [pTableName] The table for updating data. If undefined, the from part of the SqlBuilder will be used (works only if it is a tablename). If no from is set,
* the table of the first where-condition is used.
* @return {Array} array of [tableName, columns, columnTypes, values, preparedCondition], like it is required by db.updates or null if there is no condition
* @example
* var updateStatements = [];
* updateStatements.push(newWhere("PERSON.PERSONID", pPersonId).buildUpdateStatement({"FIRSTNAME" : firstName}));
* updateStatements.push(newWhere("ORGANISATION.ORGANISATIONID", pOrganisationId).buildUpdateStatement({"NAME" : organisationName}));
* db.updates(updateStatements);
*/
SqlBuilder.prototype.buildUpdateStatement = function (pFieldValues, pTableName)
{
if (!pFieldValues || typeof(pFieldValues) !== "object")
throw SqlBuilder._ERROR_UPDATE_VALUES_INVALID;
var columnValues = SqlBuilder._columnsValuesFromObject(pFieldValues, true); if (columnValues.columns.length !== 0 && this._checkForUpdate())
{
if (!pTableName && !this._tableName)
throw SqlBuilder._ERROR_NO_TABLE();
return [
(pTableName ? pTableName : this._tableName),
columnValues.columns,
null,
columnValues.values,
this.buildCondition()
];
}
return null;
}
/**
* Builds an array containing the data for an insert.
*
* @param {Object|Map} pFieldValues Object with the columns to insert into as keys mapped to their values
* @param {String} [pTableName] The table for updating data. If undefined, the from part of the SqlBuilder will be used (works only if it is a tablename). If no from is set,
* the table of the first where-condition is used.
* @param {String} [pAutoUidField] UID column that should be filled with a random UUID
* @return {Array} array of [tableName, columns, columnTypes, values, preparedCondition], like it is required by db.updates or null if there is no condition
*/
SqlBuilder.prototype.buildInsertStatement = function (pFieldValues, pTableName, pAutoUidField)
{
if (!pFieldValues || !Utils.isObject(pFieldValues))
throw SqlBuilder._ERROR_UPDATE_VALUES_INVALID;
if (pAutoUidField)
pFieldValues[pAutoUidField] = util.getNewUUID();
var columnValues = SqlBuilder._columnsValuesFromObject(pFieldValues);
if (columnValues.columns.length !== 0)
{
if (!pTableName && !this._tableName)
throw SqlBuilder._ERROR_NO_TABLE();
return [
(pTableName ? pTableName : this._tableName),
columnValues.columns,
null,
columnValues.values
];
}
return null;
}
/**
* Inserts data in the database. This function doesn't require any where-condition, it is intended to be called right after 'new SqlBuilder()'. <br/>
*
* @param {String} [pTableName] The table for inserting data. If undefined, the from part of the SqlBuilder will be used (works only if it is a tablename). If no from is set,
* the table of the first where-condition is used.
* @param {String[]} pColumns The columns where you want to insert into.
* @param {SQLTYPES[]} [pColumnTypes=null] normally you can set this to null as the types are calculated if not provided
* @param {String[]} pValues The values to be inserted.
* @param {Number} [pTimeout=-1]
* @return {Number} the number of rows affected
* @throws {Error} if no table is defined
*/
SqlBuilder.prototype.insertData = function(pTableName, pColumns, pColumnTypes, pValues, pTimeout)
{
if (!pTableName && !this._tableName)
throw SqlBuilder._ERROR_NO_TABLE();
if (!pColumns)
pColumns = null;
return db.insertData( (pTableName ? pTableName : this._tableName),
pColumns,
pColumnTypes,
pValues,
(this.alias ? this.alias : db.getCurrentAlias()),
(pTimeout ? pTimeout : -1));
}
/**
* Inserts data in the database. This function calls SqlBuilder.prototype.insertData, but provides a shorter syntax to
* improve the readability.
*
* @param {Object|Map} pFieldValues Object with the columns to update as keys mapped to their values
* @param {String} [pTableName] The table for updating data. If undefined, the from part of the SqlBuilder will be used (works only if it is a tablename). If no from is set,
* the table of the first where-condition is used.
* @param {String} [pAutoUidField] UID column that should be filled with a random UUID
* @return {Number} the number of rows affected
* @example
* new SqlBuilder().insertFields({
* "ACTIVITY_ID" : pActivityId,
* "OBJECT_ROWID" : pRowId,
* "OBJECT_TYPE" : pObjectType
* }, "ACTIVITYLINK", "ACTIVITYLINKID");
*/
SqlBuilder.prototype.insertFields = function (pFieldValues, pTableName, pAutoUidField)
{
if (!pFieldValues || typeof(pFieldValues) !== "object")
throw SqlBuilder._ERROR_UPDATE_VALUES_INVALID;
if (pAutoUidField)
pFieldValues[pAutoUidField] = util.getNewUUID();
var columnValues = SqlBuilder._columnsValuesFromObject(pFieldValues);
if (columnValues.columns.length === 0)
return 0;
return this.insertData(pTableName, columnValues.columns, null, columnValues.values);
}
SqlBuilder._columnsValuesFromObject = function (pFieldValues, pIncludeNullValues)
{
var columns = [];
var values = [];
if (Utils.isMap(pFieldValues))
{
pFieldValues.forEach(function (value, key)
{
if (pIncludeNullValues || (value !== undefined && value !== null))
{
columns.push(key);
values.push(_valueToString(value));
}
});
}
else
{
for (let field in pFieldValues)
{
if (pIncludeNullValues || (pFieldValues[field] !== undefined && pFieldValues[field] !== null))
{
columns.push(field);
values.push(_valueToString(pFieldValues[field]));
}
}
}
return {
columns: columns,
values: values
};
function _valueToString (pValue) {
if (pValue === undefined || pValue === null)
return "";
return pValue.toString();
}
}
/**
* Deletes data from the database.<br/>
* Note: the default for pExecuteOnlyIfConditionExists is true to prevent updating all rows if the SqlBuilder has no condition.
* @param {Boolean} [pExecuteOnlyIfConditionExists=true] If true, the deletion is only done if there is a condition.<br/>
* <strong>IMPORTANT: If this is set to false and there is no condition, every row in the table will be deleted!</strong>
* @param {String} [pTableName] The table for deleting data. If undefined, the from part of the SqlBuilder will be used. If no from is set,
* the table of the first where-condition is used.
* @param {Number} [pTimeout=-1]
* @return {Number} the number of rows affected
* @throws {Error} if no table is defined
* @example
* newWhere("AB_ATTRIBUTE.AB_ATTRIBUTEID", "$local.uid")
* .deleteData(); //pTableName can be omitted here since it's clearly defined by the given condition
*/
SqlBuilder.prototype.deleteData = function(pExecuteOnlyIfConditionExists, pTableName, pTimeout)
{
if (this._checkForUpdate(pExecuteOnlyIfConditionExists))
{
if (!pTableName && !this._tableName)
throw SqlBuilder._ERROR_NO_TABLE();
return db.deleteData(
(pTableName ? pTableName : this._tableName),
this.buildCondition(),
(this.alias ? this.alias : db.getCurrentAlias()),
(pTimeout ? pTimeout : -1));
}
else
{
return 0;
}
}
/**
* Builds an array containing the table and condition for a delete.
*
* @param {Boolean} [pOnlyIfConditionExists=true] If true and there is no condition, null is returned.<br/>
* <strong>IMPORTANT: If this is set to false and there is no condition, every row in the table will be deleted!</strong>
* @param {String} [pTableName] The table for deleting data. If undefined, the from part of the SqlBuilder will be used. If no from is set,
* the table of the first where-condition is used.
* @return {Array} array of [tableName, preparedCondition], like it is required by db.deletes
* @example
* var deleteStatements = [];
* deleteStatements.push(newWhere("PERSON.PERSONID", pPersonId).buildDeleteStatement());
* deleteStatements.push(newWhere("CONTACT.CONTACTID", pContactId).buildDeleteStatement());
* db.deletes(deleteStatements);
*/
SqlBuilder.prototype.buildDeleteStatement = function(pOnlyIfConditionExists, pTableName)
{
if (this._checkForUpdate(pOnlyIfConditionExists))
{
if (!pTableName && !this._tableName)
throw SqlBuilder._ERROR_NO_TABLE();
return [
(pTableName ? pTableName : this._tableName),
this.buildCondition()
];
}
else
return null;
}
/**
* Executes the SQL using db.cell and returns the result.<br/>
* Note: the default for pExecuteOnlyIfConditionExists is false becausse it is more natural to select all rows if no condition exists.
*
* @param {Boolean} [pExecuteOnlyIfConditionExists=false] if true and there is no condition, "" or the provided FallbackValue is returned
* @param {AnyValue} [pFallbackValue=""] here you can provide a fallback value if pExecuteOnlyIfConditionExists is true and the SqlBuilder has no condition.<br/>
* This is intended for e.g. select count(*) from ... because there a default value of "0" is more helpful
* @return {String} the result of the query
*/
SqlBuilder.prototype.cell = function(pExecuteOnlyIfConditionExists, pFallbackValue)
{
if (this._checkForSelect(pExecuteOnlyIfConditionExists))
{
return db.cell(this.build(),
(this.alias ? this.alias : db.getCurrentAlias()));
}
else
{
return (pFallbackValue ? pFallbackValue : "");
}
}
/**
* Executes the SQL using db.array(db.ROW, ...) and returns the result.<br/>
* Note: the default for pExecuteOnlyIfConditionExists is false becausse it is more natural to select all rows if no condition exists.
*
* @param {Boolean} [pExecuteOnlyIfConditionExists=false] if true and there is no condition, [] is returned
* @param {Number} [pMaxRows=0]
* @param {Number} [pTimeout=-1]
* @return {String[]} the result of the query
*/
SqlBuilder.prototype.arrayRow = function (pExecuteOnlyIfConditionExists, pMaxRows, pTimeout)
{
return this.array(db.ROW, pExecuteOnlyIfConditionExists, pMaxRows, pTimeout);
}
/**
* Executes the SQL using db.array(db.COLUMN, ...) and returns the result.<br/>
* Note: the default for pExecuteOnlyIfConditionExists is false becausse it is more natural to select all rows if no condition exists.
*
* @param {Boolean} [pExecuteOnlyIfConditionExists=false] if true and there is no condition, [] is returned
* @param {Number} [pMaxRows=0]
* @param {Number} [pTimeout=-1]
* @return {String[]} the result of the query
*/
SqlBuilder.prototype.arrayColumn = function (pExecuteOnlyIfConditionExists, pMaxRows, pTimeout)
{
return this.array(db.COLUMN, pExecuteOnlyIfConditionExists, pMaxRows, pTimeout);
}
/**
* Executes the SQL using db.array and returns the result.<br/>
* Note: the default for pExecuteOnlyIfConditionExists is false becausse it is more natural to select all rows if no condition exists.
*
* @param {Number} pType db.ROW or db.COLUMN
* @param {Boolean} [pExecuteOnlyIfConditionExists=false] if true and there is no condition, [] is returned
* @param {Number} [pMaxRows=0]
* @param {Number} [pTimeout=-1]
* @return {String[]} the result of the query
*/
SqlBuilder.prototype.array = function(pType, pExecuteOnlyIfConditionExists, pMaxRows, pTimeout)
{
if (this._checkForSelect(pExecuteOnlyIfConditionExists))
{
return db.array(pType, this.build(),
(this.alias ? this.alias : db.getCurrentAlias()),
(pMaxRows ? pMaxRows : 0),
(pTimeout ? pTimeout : -1));
} else
{
return [];
}
}
/**
* Executes the SQL using db.arrayPage and returns the result.<br/>
* Note: the default for pExecuteOnlyIfConditionExists is false becausse it is more natural to select all rows if no condition exists.
*
* @param {Number} pType db.ROW or db.COLUMN
* @param {Number} pStartIndex
* @param {Number} pRowCount
* @param {Boolean} [pExecuteOnlyIfConditionExists=false] if true and there is no condition, [] is returned
* @param {Number} [pTimeout=-1]
* @return {String[]} the result of the query
*/
SqlBuilder.prototype.arrayPage = function(pType, pStartIndex, pRowCount, pExecuteOnlyIfConditionExists, pTimeout)
{
if (this._checkForSelect(pExecuteOnlyIfConditionExists))
{
return db.arrayPage(pType, this.build(),
(this.alias ? this.alias : db.getCurrentAlias()),
pStartIndex === undefined ? this._startRow : pStartIndex,
pRowCount === undefined ? this._pageSize : pRowCount,
(pTimeout ? pTimeout : -1));
}
else
{
return [];
}
}
/**
* Executes the SQL using db.table and returns the result.<br/>
* Note: the default for pExecuteOnlyIfConditionExists is false becausse it is more natural to select all rows if no condition exists.
*
* @param {Boolean} [pExecuteOnlyIfConditionExists=false] if true and there is no condition, [] is returned
* @param {Number} [pMaxRows=0]
* @param {Number} [pTimeout=-1]
* @return {String[][]} the result of the query
*/
SqlBuilder.prototype.table = function(pExecuteOnlyIfConditionExists, pMaxRows, pTimeout)
{
if (this._checkForSelect(pExecuteOnlyIfConditionExists))
{
return db.table(this.build(),
(this.alias ? this.alias : db.getCurrentAlias()),
(pMaxRows ? pMaxRows : 0),
(pTimeout ? pTimeout : -1));
}
else
{
return [];
}
}
/**
* Executes the SQL using db.tablePage and returns the result.<br/>
* Note: the default for pExecuteOnlyIfConditionExists is false becausse it is more natural to select all rows if no condition exists.
*
* @param {Number} pStartIndex
* @param {Number} pRowCount
* @param {Boolean} [pExecuteOnlyIfConditionExists=false] if true and there is no condition, [] is returned
* @param {Number} [pTimeout=-1]
* @return {String[][]} the result of the query
*/
SqlBuilder.prototype.tablePage = function(pStartIndex, pRowCount, pExecuteOnlyIfConditionExists, pTimeout)
{
if (this._checkForSelect(pExecuteOnlyIfConditionExists)) {
return db.tablePage(this.build(),
(this.alias ? this.alias : db.getCurrentAlias()),
pStartIndex === undefined ? this._startRow : pStartIndex,
pRowCount === undefined ? this._pageSize : pRowCount,
(pTimeout ? pTimeout : -1));
}
else
{
return [];
}
}
/**
* Sets the pagesize for paging
*
* @param {Number} pPageSize
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.pageSize = function (pPageSize)
{
this._pageSize = pPageSize;
return this;
}
/**
* Sets the start row for paging
*
* @param {Number} pStartRow
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.startRow = function (pStartRow)
{
this._startRow = pStartRow;
return this;
}
/**
* Executes the SQL and returns the result. The startRow for paging will be increased by the pageSize, so you can use this method
* for iterating over the table pages. You can use SqlBuilder.prototype.hasMoreRows() to check if the end of rows was reached.
*
* @param {Boolean} [pExecuteOnlyIfConditionExists=false] if true and there is no condition, [] is returned
* @param {Number} [pTimeout=-1]
* @return {String[][]} the result of the query
*/
SqlBuilder.prototype.nextTablePage = function (pExecuteOnlyIfConditionExists, pTimeout)
{
if (this._pageSize == null || isNaN(this._pageSize))
throw SqlBuilder._ERROR_PAGESIZE_INVALID();
if (this._startRow == null)
this._startRow = 0;
if (this._hasMoreRows && this._checkForSelect(pExecuteOnlyIfConditionExists))
{
var data = this.tablePage(this._startRow, this._pageSize, pExecuteOnlyIfConditionExists, pTimeout);
if (data.length < this._pageSize)
this._hasMoreRows = false;
this._startRow += this._pageSize;
return data;
}
else
{
this._hasMoreRows = false;
return [];
}
}
/**
* @return {Boolean} whether there are rows left for paging */
SqlBuilder.prototype.hasMoreRows = function ()
{
return this._hasMoreRows;
}
/**
* Executes the SQL with paging and executes the given callback-function for every resultset until the last row has been reached or the function
* returns false.
*
* @param {Function} pCallBackFn CallBack-Function to execute for every page. If the function returns false, the execution will be stopped.
* @param {Boolean} [pExecuteOnlyIfConditionExists=false] if true and there is no condition, [] is returned
* @param {Number} [pTimeout=-1]
*/
SqlBuilder.prototype.forEachPage = function (pCallBackFn, pExecuteOnlyIfConditionExists, pTimeout)
{
if (typeof pCallBackFn !== "function")
throw SqlBuilder._ERROR_NOT_A_FUNCTION();
var run = true;
var idx = 0;
while (run && this.hasMoreRows())
{
run = pCallBackFn.call(null, this.nextTablePage(pExecuteOnlyIfConditionExists, pTimeout), idx++) != false;
}
}
/**
* Sets an impossible where-condition, so that the query won't return any rows.
*
* @return {SqlBuilder} current object
*/
SqlBuilder.prototype.noResult = function ()
{
return this.clearWhere().where(SqlBuilder.NORESULT_CONDITION());
}
/**
* checks if an update /delete statement should be called or not
* @return {Boolean}
* @private
*/
SqlBuilder.prototype._checkForUpdate = function(pExecuteOnlyIfConditionExists)
{
if (pExecuteOnlyIfConditionExists === undefined)
pExecuteOnlyIfConditionExists = true;
if (typeof pExecuteOnlyIfConditionExists !== "boolean")
throw SqlBuilder._ERROR_NOT_BOOLEAN();
return !pExecuteOnlyIfConditionExists || this.hasCondition();
}
/**
* checks if a select statement should be called or not
* @return {Boolean}
* @private
*/
SqlBuilder.prototype._checkForSelect = function(pExecuteOnlyIfConditionExists)
{
if (pExecuteOnlyIfConditionExists == undefined)
pExecuteOnlyIfConditionExists = false;
if (typeof pExecuteOnlyIfConditionExists !== "boolean")
throw SqlBuilder._ERROR_NOT_BOOLEAN();
if (this.isFullSelect())
{
return !pExecuteOnlyIfConditionExists || this.hasCondition();
} else
{
throw SqlBuilder._ERROR_INCOMPLETE_SELECT();
}
}
/**
* generates a part of the sql
*
* @param {String|String[]|SqlBuilder|SqlCondition} pElement the element to append
* @param {String} [pPrefix] string to be added before pElement
* @param {Boolean} [pAutoJoin] if this is true and pElement is an array, it will be automatically
* joined together to a string
*
* @private
* @deprecated this method is only needed by deprecated methods
*/
SqlBuilder.prototype._getClause = function (pElement, pPrefix, pAutoJoin)
{
var preparedValues = [];
if (typeof pElement !== "string")
{
if (pElement.length !== undefined && pAutoJoin) //array of fields
{
for (let i = 0, l = pElement.length; i < l; i++)
{
if (typeof pElement[i] !== "string")
pElement[i] = _getElement(pElement[i]);
}
pElement = pElement.join(", ");
}
else
{
pElement = _getElement(pElement);
}
}
if (pPrefix && pElement)
pElement = pPrefix + " " + pElement;
return [pElement.toString(), preparedValues];
function _getElement (element)
{
if (element instanceof SqlBuilder || element instanceof SqlCondition)
element = element.build();
preparedValues = preparedValues.concat(element[1]);
if (element instanceof SqlBuilder || pAutoJoin)
return "(" + element[0] + ")";
return element[0];
}
}
/**
* translates SqlBuilder to plain SQL. Use this if prepared statements are not supported.
* For the db-functions (db.table, db.cell, etc.) use ".build()" as they support prepared statements.
* It resolves all prepared values.
* @param {String} [pAlias=undefined] the alias to use for db.translateStatement
* @return {String} plain SQL statement
*
* @deprecated use .toString()
*/
SqlBuilder.prototype.translate = function(pAlias)
{
return SqlUtils.translateStatementWithQuotes(this.build(), pAlias);
}
/**
* Creates an object for building a case-when statement.
* * @param {String|String[]|SqlBuilder|PreparedSqlArray} [pFieldOrCond] If this is the only parameter, it is used as Subselect <br/>
* else it is used as Field. <br/>
* Please see .where() for more information and examples.
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value whitch is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* Please see .where() for more information and examples.
* @param {String} [pCondition="# = ?"] This is the condition which should be used.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue<br/>
* <strong>IMPORTANT: the # has to be before the ?</strong><br/>
* Please see .where() for more information and examples.
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement<br/>
* In most cases you don't need this.<br/>
* Please see .where() for more information and examples.
*
* @return {SqlBuilder._CaseWhen}
*/
SqlBuilder.caseWhen = function (pFieldOrCond, pValue, pCondition, pFieldType)
{
return new SqlBuilder._CaseStatement().when(pFieldOrCond, pValue, pCondition, pFieldType);
}
/**
* @return {SqlBuilder._CaseStatement}
*/
SqlBuilder.caseStatement = function ()
{
return new SqlBuilder._CaseStatement();
}
/**
* Represents a case-when statement
*/
SqlBuilder._CaseStatement = function ()
{
this._whenCondition = null;
this._whenThens = [];
this._elseValue = null;
this._afterWhenMask = new SqlBuilder._CaseWhen(this);
SqlBuilder.defineCanBuildSql(this);
}
/**
* @param {String|String[]|SqlBuilder|PreparedSqlArray} [pFieldOrCond] If this is the only parameter, it is used as Subselect <br/>
* else it is used as Field. <br/>
* Please see .where() for more information and examples.
* @param {String|SqlBuilder|PreparedSqlArray|Array|OtherTypes} [pValue] This is the value whitch is used for the condition.<br/>
* Basically it can be nearly everything you need.<br/>
* Please see .where() for more information and examples.
* @param {String} [pCondition="# = ?"] This is the condition which should be used.<br/>
* # will be replaced by the field (pFieldOrCond) If pFieldOrCond is null, you can ommit #<br/>
* ? will be replaced by pValue<br/>
* <strong>IMPORTANT: the # has to be before the ?</strong><br/>
* Please see .where() for more information and examples.
* @param {SQLTYPES|Numeric} [pFieldType=AutomaticallyLoadedType] You can specify which datatype should be used for the prepared statement<br/>
* In most cases you don't need this.<br/>
* Please see .where() for more information and examples.
*
* @return {SqlBuilder._CaseWhen}
*/
SqlBuilder._CaseStatement.prototype.when = function (pFieldOrCond, pValue, pCondition, pFieldType)
{
this._whenCondition = newWhere(pFieldOrCond, pValue, pCondition, pFieldType);
return this._afterWhenMask;
}
/**
* Sets the expression used for the else-part
*
* @param {String|SqlBuilder} pValue else-value * @return {SqlBuilder._CaseStatement}
*/
SqlBuilder._CaseStatement.prototype.elseValue = function (pValue)
{
this._elseValue = pValue;
return this;
}
/**
* Sets the value used for the else-part, but wraps the value in ''
*
* @param {String} pValue else-value
* @return {SqlBuilder._CaseStatement}
*/
SqlBuilder._CaseStatement.prototype.elseString = function (pValue)
{
return this.elseValue("'" + pValue + "'");
}
/**
* @return {String} the case-when expression
*/
SqlBuilder._CaseStatement.prototype.toString = function (pAlias)
{
return db.translateStatement(this.build(), pAlias || db.getCurrentAlias());
}
SqlBuilder._CaseStatement.prototype.build = function (pParameters)
{
var caseStatement = ["case"];
var preparedValues = [];
this._whenThens.forEach(function (whenThen)
{
var when = SqlBuilder._getStatement(whenThen.condition, "when");
var then = SqlBuilder._getStatement(whenThen.thenValue, "then");
caseStatement.push(when.sqlStorage);
caseStatement.push(then.sqlStorage);
preparedValues = preparedValues.concat(when.preparedValues, then.preparedValues);
});
if (this._elseValue)
{
let elseStatement = SqlBuilder._getStatement(this._elseValue, "else");
caseStatement.push(elseStatement.sqlStorage);
preparedValues = preparedValues.concat(elseStatement.preparedValues);
}
caseStatement.push("end");
return [
caseStatement.join(" "),
preparedValues
];
}
/**
* Object providing the then-methods for the case-when expression. It can be only be accessed after calling .when to ensure a 'then'
* can only be added after a 'when'.
*/
SqlBuilder._CaseWhen = function (pCaseStatement)
{
this._caseStatement = pCaseStatement;
}
/**
* Sets the expression for the then
*
* @param {String|SqlBuilder} pValue then-value
* @return {SqlBuilder._CaseStatement}
*/
SqlBuilder._CaseWhen.prototype.then = function (pValue)
{ var condition = this._caseStatement._whenCondition;
this._caseStatement._whenCondition = null;
this._caseStatement._whenThens.push({condition: condition, thenValue: pValue});
return this._caseStatement;
}
/**
* Sets the value for the then, but wraps the value in ''
*
* @param {String} pValue then-value
* @return {SqlBuilder._CaseStatement}
*/
SqlBuilder._CaseWhen.prototype.thenString = function (pValue)
{
return this.then("'" + pValue + "'");
}
/**
*provides functions for masking sql functions
*
* @param {String} [pAlias=currentAlias] database alias, you can specify null if you have no alias available and you can manually set the dbType property
*
* @class
*/
function SqlMaskingUtils (pAlias)
{
this.alias = null;
Object.defineProperty(this, "alias", {
set: function(v){
this._alias = v;
if (v != null)
this._dbType = db.getDatabaseType(this._alias);
},
get: function(){
return this._alias;
}
});
this.dbType = null;
//provide the possibility to just set dbType (e.g. for testing) with no association to an alias
Object.defineProperty(this, "dbType", {
set: function(v){
this._alias = null;
this._dbType = v;
},
get: function(){
return this._dbType;
}
});
if (pAlias === undefined)
this.alias = vars.getString("$sys.dbalias");
else
this.alias = pAlias;
}
/**
* returns the concat symbol depending on database type
* @return {String} Concat Symbol
*/
SqlMaskingUtils.prototype.getConcatSymbol = function()
{
switch(this.dbType)
{
case db.DBTYPE_SQLSERVER2000:
return " + ";
case db.DBTYPE_MARIADB10:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI: case db.DBTYPE_POSTGRESQL8:
case db.DBTYPE_DERBY10:
default:
return " || ";
}
}
/**
* Returns the trim function, which removes the<br>
* leading and trailing spaces in a string, depending<br>
* on the database behind the given alias note that<br>
* this function does not verifiy where the types of<br>
* your expression are trimable or not.<br>
*
* @param {String} pField <p>
* Expression that shall be trimmed.<br>
* @return {String} <p>
* Returns the trimmed string.<br>
*/
SqlMaskingUtils.prototype.trim = function (pField)
{
if (this.dbType == db.DBTYPE_SQLSERVER2000)
return "ltrim(rtrim(" + pField + "))";
return "trim(" + pField + ")";
}
/**
* returns the max-value sql expressions depending on the database behind the given alias
* note that this function does not verifiy if the field (and type) usage is valid at all
*
* @param {String} pField expression
*
* @return {String} sql-part that can be used in a select
*/
SqlMaskingUtils.prototype.max = function (pField)
{
return "max(" + pField + ")";
}
/**
* returns the min-value sql expressions depending on the database behind the given alias
* note that this function does not verifiy if the field (and type) usage is valid at all
*
* @param {String} pField expression
*
* @return {String} sql-part that can be used in a select
*/
SqlMaskingUtils.prototype.min = function (pField)
{
return "min(" + pField + ")";
}
/**
* masks the function cast of standard sql
* please note that this function does not do any validation if it's possible to cast the expression's datatype you pass to the function in every supported DBMS
*
* Problems:
* Derby has problems with casting to CHAR({> 254}) https://db.apache.org/derby/docs/10.14/ref/rrefsqlj13733.html
*
* @param {String} pField name of the database field that shall be castet
* @param {String} [pTargetDatatype] a SQLTYPES-value of the following: SQLTYPES.CHAR, SQLTYPES.VARCHAR, SQLTYPES.INTEGER,
* SQLTYPES.DECIMAL, SQLTYPES.DATE
* @param {int|int[]} pTargetLength specifies the length of the target data type;
* <br/>- char/varchar: length
* <br/>- decimal: [length, decimals]
*
* @return {String} sql part to be included in sql-statements
*/
SqlMaskingUtils.prototype.cast = function (pField, pTargetDatatype, pTargetLength)
{ /* Some informations if you want to add supported databaseTypes or dataTypes:
* You should consider using the _mapDefaults function-expression (details in the functions doc)
* However you shouldn't use the function in a "default"-Block of a switch-case because of the following behaviour:
* If a datatype is not supported you just have to NOT specify "sqlDataType" (leave it "undefined") -> an error is then raised
* Therefore you should explicitly define which Data-type is supported and which is not
*/
var sqlDataType;
var functionName = "cast";//overwrite this in the "switch (dbType)" if needed with your DBMS
/**
* handles default-scenarios for mapping input-targetDatatype to a string for a sql-statement
* e.g. SQLTYPES.INTEGER --> int
* @param {Number} dataType input as a value of "SQLTYPES." that will be mapped to a string
* @return {String} the mapped dataType for using in a sql-statement
*/
var _mapDefaults = function (dataType)
{
switch (dataType)
{
case SQLTYPES.CHAR:
return "char";
case SQLTYPES.VARCHAR:
return "char";
case SQLTYPES.NVARCHAR:
return "nvarchar";
case SQLTYPES.INTEGER:
return "int";
case SQLTYPES.DECIMAL:
return "decimal";
case SQLTYPES.DATE:
return "date";
}
return null;
}
switch (this.dbType)
{
case db.DBTYPE_DERBY10:
switch(pTargetDatatype)
{
case SQLTYPES.NVARCHAR:
case SQLTYPES.VARCHAR:
// Because of a Derby bug, you can't cast INTEGER into VARCHAR
// Therefor first cast to char then to varchar
// https://issues.apache.org/jira/browse/DERBY-2072
// This cast to char is only done if the length is not bigger than 254,
// otherwise the additional cast would result in a different error
if (pTargetLength <= 254)
pField = "rtrim(" + this.cast(pField, SQLTYPES.CHAR, pTargetLength) + ")";
sqlDataType = "varchar";
break;
case SQLTYPES.CHAR:
sqlDataType = "char";
break;
case SQLTYPES.DECIMAL:
case SQLTYPES.INTEGER:
case SQLTYPES.DATE:
sqlDataType = _mapDefaults(pTargetDatatype);
break;
}
break;
case db.DBTYPE_MARIADB10:
case db.DBTYPE_MYSQL4:
switch(pTargetDatatype)
{
case SQLTYPES.VARCHAR:
case SQLTYPES.CHAR:
case SQLTYPES.INTEGER:
case SQLTYPES.DECIMAL:
case SQLTYPES.DATE: sqlDataType = _mapDefaults(pTargetDatatype);
break;
}
break;
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
switch(pTargetDatatype)
{
case SQLTYPES.VARCHAR:
sqlDataType = "varchar2";
break;
case SQLTYPES.NVARCHAR:
sqlDataType = "nvarchar2";
break;
case SQLTYPES.INTEGER:
sqlDataType = "number";
pTargetLength = "10"
break;
case SQLTYPES.CHAR:
case SQLTYPES.DECIMAL:
case SQLTYPES.DATE:
sqlDataType = _mapDefaults(pTargetDatatype);
break;
}
break;
case db.DBTYPE_POSTGRESQL8:
switch(pTargetDatatype)
{
case SQLTYPES.DATE:
case SQLTYPES.DECIMAL:
case SQLTYPES.INTEGER:
case SQLTYPES.CHAR:
case SQLTYPES.VARCHAR:
sqlDataType = _mapDefaults(pTargetDatatype);
break;
}
break;
case db.DBTYPE_SQLSERVER2000:
case SQLTYPES.DATE:
case SQLTYPES.DECIMAL:
case SQLTYPES.INTEGER:
case SQLTYPES.CHAR:
case SQLTYPES.VARCHAR:
sqlDataType = _mapDefaults(pTargetDatatype);
break;
//TODO: firebird support?
}
if (sqlDataType == undefined)
throw new Error(translate.withArguments("${SQL_LIB_UNSUPPORTED_DBTYPE} function: %0", ["SqlMaskingUtils.prototype.cast._mapDefaults"]));
if (pTargetLength == undefined)
pTargetLength = "";
else if (pTargetLength != "")
{
if (Array.isArray(pTargetLength))
pTargetLength = "(" + pTargetLength.join(", ") + ")";
else
pTargetLength = "(" + pTargetLength + ")";
}
return functionName + "(" + pField + " as " + sqlDataType + pTargetLength + ")";
}
/**
* masks the cast function for lob datatypes(clob, blob) into varchar or similar
*
* @param {String} pField expression that shall be casted
* @param {Number|Number[]} pTargetLength desired length of the datatype * decimal: [length, decimals]
*
* @return {String} part of sql-expression that can be used
*/
SqlMaskingUtils.prototype.castLob = function (pField, pTargetLength)
{
switch (this.dbType)
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
return "DBMS_LOB.SUBSTR(" + pField + ", " + pTargetLength + ", 1)";
default:
return this.cast(pField, SQLTYPES.VARCHAR, pTargetLength);
}
}
/**
* returns the function which determines the length of binary data
*
* @param {String} pField name of the checked field
*
* @return {String}
*/
SqlMaskingUtils.prototype.binDataLength = function (pField)
{
switch (this.dbType)
{
case db.DBTYPE_MARIADB10:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
case db.DBTYPE_POSTGRESQL8:
case db.DBTYPE_DERBY10:
return "length(" + pField + ")";
case db.DBTYPE_SQLSERVER2000:
return "datalength(" + pField + ")";
default:
throw new Error(translate.withArguments("${SQL_LIB_UNSUPPORTED_DBTYPE} function: %0", ["SqlMaskingUtils.prototype.binDataLength"]));
}
}
/**
* masks the sql function substring
*
* @param {String } pField the expression that shall be substringed
* @param {Number} pStartPos posistion where the substring starts
* @param {Number} pLength amount of characters of the expression will be returned by the sql function
*
* @return {String} part of sql-expression that can be used for substringing
*/
SqlMaskingUtils.prototype.substring = function (pField, pStartPos, pLength)
{
var sqlFnName;
switch (this.dbType)
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
sqlFnName = "substr";
break;
case db.DBTYPE_DERBY10:
sqlFnName = "substr";
break;
case db.DBTYPE_POSTGRESQL8:
sqlFnName = "substr";
break;
case db.DBTYPE_SQLSERVER2000:
sqlFnName = "substring"; break;
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
sqlFnName = "substring";
break;
default:
throw new Error(translate.withArguments("${SQL_LIB_UNSUPPORTED_DBTYPE} function: %0", ["SqlMaskingUtils.prototype.substring"]));
}
return sqlFnName + "(" + pField + ", " + pStartPos + ", " + pLength + ")";
}
/**
* masks the function concat
* if a sql field is empty no separator will be added
* note that this function will often create a lot of sql-code
*
* @param {Array} pFields fields (or expressions) that should be concatenated
* @param {String} [pSeparator=space-character] character for separating the fields
* @param {String} [pAutoTrimFields=true] autoTrimFields if true the expressions are always trimmed, false no change will be applied
*
* @return {String} part of SQL-querey
*/
SqlMaskingUtils.prototype.concat = function (pFields, pSeparator, pAutoTrimFields)
{
if (pFields.length === 0)
return "''";
if (pFields.length === 1)
return pFields[0];
if (pAutoTrimFields == undefined)
pAutoTrimFields = true;
if (pSeparator === null || pSeparator === undefined)
pSeparator = "' '";
else if (pSeparator)
pSeparator = "'" + db.quote(pSeparator, this.alias) + "'";
var doEmptyStringCheck = true;
switch (this.dbType)
{
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
case db.DBTYPE_POSTGRESQL8:
if (pAutoTrimFields)
pFields = pFields.map(this.trim, this);
return " concat_ws(" + pSeparator + ", " + pFields.join(", ") + ")";
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
doEmptyStringCheck = false; //empty strings are changed to DB-null-values internally in oracle; by specifing JS-null we disable this check
break;
case db.DBTYPE_SQLSERVER2000:
//MS SQL Server supports "concat_ws" (and ignoring null values) from version SQL Server 2017 and newer:
//https://docs.microsoft.com/de-de/sql/t-sql/functions/concat-ws-transact-sql?view=sql-server-2017
break;
case db.DBTYPE_DERBY10:
break;
default:
throw new Error(translate.withArguments("${SQL_LIB_UNSUPPORTED_DBTYPE} function: %0", ["SqlMaskingUtils.prototype.concat"]));
}
var concatCharacter = this.getConcatSymbol();
var concatSql = "";
for (let i = 0; i < pFields.length; i++)
{
let field = pFields[i];
let isLast = i + 1 === pFields.length;
if (!_isFixedValue(field))
concatSql += (pAutoTrimFields ? this.trim(this.isNull(field)) : this.isNull(field));
else
concatSql += (pAutoTrimFields ? "'" + field.slice(1, -1).trim() + "'" : field);
if (!isLast)
{
concatSql += concatCharacter;
let nextField = pFields[i+1];
if (pSeparator && _isFixedValue(nextField))
{
if (nextField.slice(1, -1).trim())
concatSql += pSeparator + concatCharacter;
}
else if (pSeparator)
{
let nextFieldTrimmed = pAutoTrimFields ? this.trim(nextField) : nextField;
let nextNotNullCondition = nextField + " is not null ";
if (doEmptyStringCheck || pAutoTrimFields)
nextNotNullCondition += " and " + nextFieldTrimmed + " != '' ";
concatSql += "case when " + nextNotNullCondition + " then " + pSeparator + " else '' end " + concatCharacter;
}
}
}
return concatSql;
function _isFixedValue (pSqlField)
{
return pSqlField.startsWith("'") && pSqlField.endsWith("'") && !pSqlField.slice(1, -1).includes("'");
}
}
/**
* returns the function for replacing a null value
*
* @param {String} pField expression that shall be checked for a null value
* @param {String} [pReplacement=empty string] expression that shall be used if the field contains null
*
* @return {string}
*/
SqlMaskingUtils.prototype.isNull = function (pField, pReplacement)
{
if (pReplacement == undefined)
pReplacement = "''";
switch (this.dbType)
{
case db.DBTYPE_SQLSERVER2000:
return "isnull(" + pField + ", " + pReplacement + ")";
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_OCI:
case db.DBTYPE_ORACLE10_THIN :
return "nvl(" + pField + ", " + pReplacement + ")";
case db.DBTYPE_POSTGRESQL8:
case db.DBTYPE_DERBY10:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
default:
return "coalesce(" + pField + ", " + pReplacement + ")";
}
}
/**
* gets the day from a timestamp
*
* @param {String} pField timestamp to get the day from
*
* @return {String} sql expression that extracts the day from a timestamp */
SqlMaskingUtils.prototype.dayFromDate = function (pField)
{
switch (this.dbType)
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
return "to_char(" + pField + ",'dd')";
case db.DBTYPE_DERBY10:
case db.DBTYPE_SQLSERVER2000:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
return "day(" + pField + ")";
case db.DBTYPE_POSTGRESQL8:
return "extract (day from " + pField + ")";
}
}
/**
* gets the month from a timestamp
*
* @param {String} pField timestamp to get the month from
*
* @return {String} sql expression that extracts the month from a timestamp
*/
SqlMaskingUtils.prototype.monthFromDate = function (pField)
{
switch (this.dbType)
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
return "to_char(" + pField + ",'MM')";
case db.DBTYPE_DERBY10:
case db.DBTYPE_SQLSERVER2000:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
return "month(" + pField + ")";
case db.DBTYPE_POSTGRESQL8:
return "extract (month from " + pField + ")";
}
}
/**
* gets the year from a timestamp
*
* @param {String} pField timestamp to get the year from
*
* @return {String} sql expression that extracts the year from a timestamp
*/
SqlMaskingUtils.prototype.yearFromDate = function(pField)
{
switch (this.dbType)
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
return "to_char(" + pField + ",'yyyy')";
case db.DBTYPE_DERBY10:
case db.DBTYPE_SQLSERVER2000:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
return "YEAR(" + pField + ")";
case db.DBTYPE_POSTGRESQL8:
return "EXTRACT (YEAR FROM " + pField + ")";
}
}
/** * functions for various Sql-actions
* Do not create an instance of this!
*
* @class
* @static
*/
function SqlUtils() {}
/**
* parses given name of table and name of column to clearly find out the tablename and columnanme
*
* @param {String|Array} pFieldOrTableName you've got several possibilites to pass here:
* <br/> 1. the name of the table if also a pColumnName is specified
* <br/> 2. the name of the table and columname as "tablename.columnname" (e.g. "ORGANISATION.NAME") if no pColumnName is specified
* <br/> 3. an array with 2 elements: [tablename, columnname] (e.g. ["ORGANISATION", "NAME"]) if no pColumnName is specified
* <br/> 4. an array with 3 elements: [tablename, columnname, tablealias] (e.g. ["ORGANISATION", "NAME", "org"]) if no pColumnName is specified
* <br/> Everything else will raise an error
*
* @param {String} [pColumnName] depending on pFieldOrTableName this should be undefined/null or the name of a column
*
* @return {Object|TypeError} TypeError if something wrong has been passed or returns a object with these properties:
* 1. "table" which is the tablename
* 2. "column" which is the columnname
* 3. "tableAlias" which is the tableAlias if it exists. else it is also the table
* e.g. {table: "ORGANISATION", column: "NAME", tableAlias: "org"}
*
*
*/
SqlUtils._parseFieldQualifier = function(pFieldOrTableName, pColumnName)
{
var fnName = "SqlUtils._parseFieldQualifier";//for return errors
if (typeof pFieldOrTableName == "string")
{
if (pFieldOrTableName.search(/[\s=\(\)<>!]/) != -1)
return new TypeError(translate.withArguments("[%0]%1 has to be a string without empty spaces, (, ), =, <, > but it contains at least one of them", [fnName, "pFieldOrTableName"]));
}
var tableName, columnName, tableAlias;
if (pColumnName != undefined)
{
tableName = pFieldOrTableName;
columnName = pColumnName;
}
else
{
var fieldVarType = typeof(pFieldOrTableName);
if (fieldVarType == "string")
{
pFieldOrTableName = text.split(pFieldOrTableName, "\\.");
}
else if (fieldVarType != "object") //check for object since there exists JavaArrays and JavaScript arrays which are both valid
{
return new TypeError(translate.withArguments("[%0]%1 has to be a string or array but it is %2", [fnName, "pFieldOrTableName",
fieldVarType]));
}
if (Array.isArray(pFieldOrTableName))
{
if (pFieldOrTableName.length == 3)
{
tableName = pFieldOrTableName[0];
columnName = pFieldOrTableName[1];
tableAlias = pFieldOrTableName[2];
}
else if (pFieldOrTableName.length == 2)
{
tableName = pFieldOrTableName[0];
columnName = pFieldOrTableName[1];
} else
{
return new TypeError(translate.withArguments("[%0]has now an incorrect length; estimated 2 or 3 elements but got %1", [fnName, pFieldOrTableName.length ]));
}
}
else //check for object happens since there exists JavaArrays and JavaScript arrays which are both valid
return new TypeError(translate.withArguments("[%0]%1 is an object but seems not to be a valid array or array-like", [
fnName, "pFieldOrTableName"]));
}
if (!tableAlias)
tableAlias = tableName;
if (typeof(columnName) != "string")
return new TypeError(translate.withArguments("[%0]the columnName is not a string after interpreting", [fnName]));
if (typeof(tableName) != "string")
return new TypeError(translate.withArguments("[%0]the tableName is not a string after interpreting", [fnName]));
if (typeof(tableAlias) != "string")
return new TypeError(translate.withArguments("[%0]the tableAlias is not a string after interpreting", [fnName]));
return {
table: tableName,
column: columnName,
tableAlias: tableAlias
};
};
/**
* determines if given values match a full field qualifier (name of table and name of column)
*
* @param {String|Array} pFieldOrTableName you've got several possibilites to pass here:
* <br/> 1. the name of the table if also a pColumnName is specified
* <br/> 2. the name of the table and columname as "tablename.columnname" (e.g. "ORGANISATION.NAME") if no pColumnName is specified
* <br/> 3. an array with 2 elements: [tablename, columnname] (e.g. ["ORGANISATION", "NAME"]) if no pColumnName is specified
* <br/> 4. an array with 3 elements: [tablename, columnname, tablealias] (e.g. ["ORGANISATION", "NAME", "org"]) if no pColumnName is specified
* <br/> Everything else will raise an error
*
* @param {String} [pColumnName] depending on pFieldOrTableName this should be undefined/null or the name of a column
*
* @return {Boolean} returns true if it's a full qualifier or false if not
*
*/
SqlUtils.isFullFieldQualifier = function(pFieldOrTableName, pColumnName)
{
var parsed = SqlUtils._parseFieldQualifier(pFieldOrTableName, pColumnName);
if (parsed instanceof TypeError)
return false;
return true;
};
/**
* determines the type of a single database column in a table; if you want to get several columntypes at once use db.getColumnTypes instead
*
* @param {String|Array} pFieldOrTableName you've got several possibilites to pass here:
* <br/> 1. the name of the table if also a pColumnName is specified
* <br/> 2. the name of the table and columname as "tablename.columnname" (e.g. "ORGANISATION.NAME") if no pColumnName is specified
* <br/> 3. an array with 2 elements: [tablename, columnname] (e.g. ["ORGANISATION", "NAME"]) if no pColumnName is specified
* <br/> 4. an array with 3 elements: [tablename, columnname, tablealias] (e.g. ["ORGANISATION", "NAME", "org"]) if no pColumnName is specified
* <br/> Everything else will raise an error
*
* @param {String} [pColumnName] depending on pFieldOrTableName this should be undefined/null or the name of a column
* @param {String} [pAlias=the current alias] Database-Aliasname, where the SQL-Statement shall be executed; default is the current dbalias
*
* @throws TypeError if a wrong format is passed as table/column-combination
*
* @return {Number} returns the corresponding SQLTYPES-value
*
*/
SqlUtils.getSingleColumnType = function(pFieldOrTableName, pColumnName, pAlias)
{ var fields = SqlUtils._parseFieldQualifier(pFieldOrTableName, pColumnName);
if (fields instanceof TypeError)
throw fields;
if (pAlias == undefined)
pAlias = db.getCurrentAlias();
return db.getColumnTypes(fields.table, [fields.column], pAlias)[0];
};
/**
* calls a given function for N blocks of sql-data as long as records are available or the paging-process is manually canceled
*
* @param {Object|String} sqlStatement the sql statement that shall be executed
* String: SQL-query in a simple text form
* Object: prepared-sql-query: [sqlStr, [[value1, type1], [valueN, typeN]]]
* @param {Number} blockSize Amount of records that shall be read per block. (you need to specify an ORDER BY in your SQL-query)
* "0" <=> all records
* @param {Function} callbackFn a callback-function that is called for every block and has the following params:
* myCallback(myDataBlockAs2Darray, myLoopCountThatStartsWith1)
* If "false" is returned sqlPageData will abort the paging process and return false
* @param {String} [dbAlias=the current alias] Database-Aliasname, where the SQL-Statement shall be executed; default is the current dbalias
* @param {Number} [timeout=configured dbTimeout in Preferences] Timeout in milliseconds; When it's reached the SQL-Statement will abort; default is in PREFERENCES configured
* @param {Number} [startOffset=0] Position where to begin with the data-reading-process; default is 0
*
*
* @return {bool} returns whether the function read all available data or not:
* false if the callback-function returned false, otherwise true
*
* @example
* var varValues = [];//you've got access to variables declared with 'var'
* let letValues = [];//you've got access to variables declared with 'let'
* var count = 0;//you cannot overwrite a variable of 'sqlPageData' by accident
*
* var sql = "select ORGNAME from ORGANISATION";
* var blockSize = 5 * 1000;
*
* var allRows = +db.cell("select count(*) from ORGANISATION");
*
* sqlPageData(sql, blockSize, function(pData, pRunNo){
* var j = pData.length;//pData is the current block with data
* logMsg(pRunNo.toString() + "#" + j);//pRunNo is the amount how often the func. has been already called
* //you can calculate the progress easily by: progress = (blockSize* (pRunNo-1) + pData.length) / (allRows - startOffset)
* //example in per cent:
* var startOffset = 0;//we did not pass any startOffset to sqlPageData - this is equivalent to zero
* var progress = (blockSize* (pRunNo-1) + pData.length) / (allRows - startOffset);
* logMsg("progess: " + eMath.roundDec(progress * 100, 2, eMath.ROUND_CEILING) + "%");
*
* for (var i = 0; i < j; i++)
* {
* varValues.push(pData[i][0]);
* letValues.push(pData[i][0]);
* }
*
* count += pRunNo * 100;
* logMsg("count:" + count);//you cannot overwrite a variable of 'sqlPageData' by accident
* });
*
* logging.show(letValues);//contains orgnames
* logging.show(varValues);//contains orgnames
*/
SqlUtils.pageTableData = function(sqlStatement, blockSize, callbackFn, dbAlias, timeout, startOffset)
{
return SqlUtils._pageData(null, sqlStatement, blockSize, callbackFn, dbAlias, timeout, startOffset);
};
/**
* calls a given function for N blocks of sql-data as long as records are available or the paging-process is manually canceled
*
* @param {Object|String} sqlStatement the sql statement that shall be executed* String: SQL-query in a simple text form
* Object: prepared-sql-query: [sqlStr, [[value1, type1], [valueN, typeN]]]
* @param {Number} blockSize Amount of records that shall be read per block. (you need to specify an ORDER BY in your SQL-query)
* "0" <=> all records
* @param {Function} callbackFn a callback-function that is called for every block and has the following params:
* myCallback(myColumnDataBlockAsArray, myLoopCountThatStartsWith1)
* If "false" is returned sqlPageData will abort the paging process and return false
* @param {String} [dbAlias=the current alias] Database-Aliasname, where the SQL-Statement shall be executed; default is the current dbalias
* @param {Number} [timeout=configured dbTimeout in Preferences] Timeout in milliseconds; When it's reached the SQL-Statement will abort; default is in PREFERENCES configured
* @param {Number} [startOffset=0] Position where to begin with the data-reading-process; default is 0
*
*
* @return {bool} returns whether the function read all available data or not:
* false if the callback-function returned false, otherwise true
*
* @example
* similar to sqlTablePageData -> take a look at the example there
*/
SqlUtils.pageColumnData = function(sqlStatement, blockSize, callbackFn, dbAlias, timeout, startOffset)
{
return SqlUtils._pageData(db.COLUMN, sqlStatement, blockSize, callbackFn, dbAlias, timeout, startOffset);
};
//internal function for paging through data; for description take a look at sqlArrayPageData
SqlUtils._pageData = function(sqlType ,sqlStatement, blockSize, callbackFn, dbAlias, timeout, startOffset)
{
if (dbAlias == undefined)
dbAlias = db.getCurrentAlias();
if (startOffset == undefined)
startOffset = 0;
var count = 0;
while (startOffset > -1)
{
var data;
if (sqlType == null)
{
if (timeout == undefined)
data = db.tablePage(sqlStatement, dbAlias, startOffset, blockSize);
else
data = db.tablePage(sqlStatement, dbAlias, startOffset, blockSize, timeout);
}
else
{
if (timeout == undefined)
data = db.arrayPage(sqlType, sqlStatement, dbAlias, startOffset, blockSize);
else
data = db.arrayPage(sqlType, sqlStatement, dbAlias, startOffset, blockSize, timeout);
}
startOffset += blockSize;
//this happens when all-records % blockSize == 0
//we do not want to call the callback-fn
if (data.length == 0)
return true;
else if (data.length < blockSize || blockSize == 0)//blocksize 0 is everything
startOffset = -1;//call callback the last time
if (callbackFn.call(this, data, ++count) === false)
return false;//callback can return false to manually stop the paging-process
}
return true;
}
/**
* @return the alias for table asys_binaries
*/
SqlUtils.getBinariesAlias = function()
{ return SqlUtils.getSystemAlias();
}
/**
* @return the sytemalias
*/
SqlUtils.getSystemAlias = function()
{
return "_____SYSTEMALIAS";
}
/**
* Builds a SQL IN condition, while accounting for the 1000 elements maximum
* Single conditions are concatenated with OR, which can be devastating for performance!
*
* @param {String} pFieldname req name of the field with table alias
* z.B ORGREL.CONTACTID
* @param {String[]|String[][]} pData req Data as ID Array
* @param {String} [pQuoteSymbol=""] symbol for quoting values,
* Strings i.e.: ' default is no symbol
* @param {Boolean} [pAsPrepared=undefined] true if result should be returned as prepared condition
* @param {Boolean} [pPreparedDbType=undefined] if pAsPrepared is true, this param has to be filld with the correct db type
*
* @return {String|Array} SQL condition: where VALS in (1,2,3) OR as prepared Statement if pAsPrepared is true ["VALS in (1,2,3)", [...]
*/
SqlUtils.getSqlInStatement = function(pFieldname, pData, pQuoteSymbol, pAsPrepared, pPreparedDbType)
{
var MAX_COUNT = 1000;
if (pData.length > 1000)
logging.log(translate.text("SqlUtils.getSqlInStatement: WARNING: You should not create in-statements with more than 1000 values. As this has a very bad performance."))
if (pData.length == 0)
return " 1 = 2 ";
var res = "";
var qs = pQuoteSymbol || "";
var preparedValues;
if (pAsPrepared)
{
preparedValues = [];
if (!pPreparedDbType)
throw new Error(translate.text("SqlUtils.getSqlInStatement: if pAsPrepared is true, pPreparedDbType has to be filld with the correct db type"));
}
//pData.length -1 um für den Fall, dass MAX_COUNT == pData.length ist trotzdem nur einen Aufruf
//zu machen
var count = ((pData.length -1) / MAX_COUNT) >> 0;//aus kommazahl eine ganzzahl machen
//<= verwenden, da bei einer Länge von "126" der Vorgang einmal ausgeführt werden soll
for (var i = 0; i <= count; i++)
{
if (i > 0)
res += " or ";
if (pAsPrepared)
{
res += (pFieldname ? pFieldname + " in " : "") + "(";
var subData = pData.slice(i * MAX_COUNT, i * MAX_COUNT + MAX_COUNT);
subData.forEach(function(pVal, pIndex) {
res += "?";
preparedValues.push([pVal, pPreparedDbType])
if (pIndex != subData.length-1)
res += ", ";
});
res += ")"
}
else
{
res += (pFieldname ? pFieldname + " in " : "") + "(" + qs + pData.slice(i * MAX_COUNT, i * MAX_COUNT + MAX_COUNT) .join(qs + ", " + qs) + qs + ") ";
}
}
//wenn mehrere Zeilen mit "or" verknüpft wurden nochmal klammern
if (count > 0)
res = "(" + res + ")";
if (pAsPrepared)
return [res, preparedValues];
else
return res;
}
/**
* resolves key-value pairs (of strings) into a case when expression
*
* @param {String[][]} pKeyValueArray you've to pass a 2D-Array where each element has at pos0 the key and pos1 the value
* @param {String} pDbFieldName name fo the database field where the KEY-value is stored
* @param {String} [pLocale=current client language] specifies the locale for translating the title; can be false if nothing shalle be translated
*
* @return {String} a SQL-expression (case-when-statement) that resolves the KEYID into the title -> as preparedSatement-elements
*/
SqlUtils.getResolvingCaseWhen = function(pKeyValueArray, pDbFieldName, pLocale)
{
var keyData = pKeyValueArray;
if (keyData.length == 0)
return ["''", []];
//a helper function for easy translation
var translateValue = function(value){
if (pLocale === false)
return value;
else if (pLocale)
return translate.text(value, pLocale);
else
return translate.text(value);
};
//!SqlBuilder
var resSql = "case ", preparedValues = [];
var colTypeKeyId = SQLTYPES.CHAR;
var colTypeTitle = SQLTYPES.NVARCHAR;
for (var i = 0, l = keyData.length; i < l; i++)
{
var translatedTitle = translateValue(keyData[i][1]);
resSql += " when " + pDbFieldName + " = ? then ? "
preparedValues.push([keyData[i][0], colTypeKeyId]);
preparedValues.push([translatedTitle, colTypeTitle]);
}
resSql += " else '' end ";
resSql = [resSql, preparedValues];
return resSql;
};
/**
* resolves an array of key-value pairs (of strings) into a sql case when expression<br/>
* This is useful for results of entities.getRows for example.
*
* @param {Array} pKeyValueObject <p/>you've to pass a 2D-Array where each element has to be an object with at least one key: value-pair, e.g.:
* <br/>[{uid: "uid1", value: "value1"}, {uid: "uidN", value: "valueN"}]
* @param {String} pUid <p/>name of the key where the rawvalue (the uid) is located in the object
* @param {String} pTranslatedValue <p/>name of the key where the already translated value is located in the object
* @param {String} pDbFieldName <p/>name fo the database field where the KEY-value is stored
*
* @return {String} <p/>a SQL-expression (case-when-statement) that resolves the KEYID into the title -> as
* preparedSatement-elements
* <br/>The else-value is "unassigned".
*
* @example
* var exampleDataStack = [* {keyVal: "PHONE", titleOriginal: "Phone", titleTranslated: "Telefon", origin: "MetaImporter"},
* {keyVal: "EMAIL", titleOriginal: "email", titleTranslated: "E-Mail", origin: "MetaImporter"}
* ];
*
* var sqlExpr = SqlUtils.getResolvingCaseWhenFromObject(exampleDataStack, "keyVal", "titleTranslated", "FORM.COMMUNICATION");
* //results in a sql case when as prepared statement that is resolvedas following:
* //case when FORM.COMMUNICATION = 'PHONE' then 'Telefon' when FORM.COMMUNICATION = 'EMAIL' then 'E-Mail' else 'nicht zugeordnet' end
*/
SqlUtils.getResolvingCaseWhenFromObject = function(pKeyValueObject, pUid, pTranslatedValue, pDbFieldName)
{
var keyData = pKeyValueObject;
if (keyData.length == 0)
return ["''", []];
var translateValue = pTranslatedValue;
var uid = pUid;
var unassigned = translate.text("unassigned")
var resSql = "case ", preparedValues = [];
var colTypeKeyId = SQLTYPES.CHAR;
var colTypeTitle = SQLTYPES.NVARCHAR;
for (var i = 0, l = keyData.length; i < l; i++)
{
var translatedTitle = keyData[i][translateValue];
resSql += " when " + pDbFieldName + " = ? then ? "
preparedValues.push([keyData[i][pUid], colTypeKeyId]);
preparedValues.push([translatedTitle, colTypeTitle]);
}
resSql += " else '"+ unassigned +"' end ";
resSql = [resSql, preparedValues];
return resSql;
};
/**
* Will quote all prepared statement values from the given statement.
*
* @param {PreparedSqlArray} pStatement Same as first paraemter of db.translateStatement.
* @param {Callback} pExecutionCallback (PreparedSqlArray) => String A function which must return the final SQL.
* @return The SQL, same as the result of db.translateStatement.
*/
SqlUtils.translateWithQuotes = function(pStatement, pExecutionCallback)
{
// Validate type of incoming paramter.
if (!Array.isArray(pStatement))
return null;
// The second element of the array has to be an array.
if (!Array.isArray(pStatement[1]))
return null;
// As the second element represents the prepared statements we need to map it...
var preparedStatements = pStatement[1].map(function(pValue)
{
// Just in case as a fallback value..
if (!(Array.isArray(pValue)))
return pValue;
// As the first element represents the value it will be quoted here.
return [db.quote(pValue[0]), pValue[1]];
});
return pExecutionCallback([pStatement[0], preparedStatements]);
}
/**
* Will quote all prepared statement values from the given statement.
*
* @param {PreparedSqlArray} pStatement Same as the first parameter of db.translateStatement.
* @param {String} [pAlias] the alias which should be used for db.translateStatement() * @returns {String} The SQL, same as the result of db.translateStatement.
*/
SqlUtils.translateStatementWithQuotes = function(pStatement, pAlias)
{
return SqlUtils.translateWithQuotes(pStatement, function(pValue)
{
if (pAlias)
return db.translateStatement(pValue, pAlias)
else
return db.translateStatement(pValue)
});
}
/**
* Will quote all prepared statement values from the given statement.
*
* @param {PreparedSqlArray} pStatement Same as the first parameter of db.translateCondition.
* @param {String} [pAlias] the alias which should be used for db.translateStatement()
* @returns {String} The SQL, same as the result of db.translateCondition.
*/
SqlUtils.translateConditionWithQuotes = function(pStatement, pAlias)
{
return SqlUtils.translateWithQuotes(pStatement, function(pValue)
{
if (pAlias)
return db.translateCondition(pValue, pAlias)
else
return db.translateCondition(pValue)
});
}
SqlUtils.parseField = function(pField)
{
var alias = "";
if (typeof pField === 'string')
{
var pointPos = pField.indexOf(".");
if (pointPos > 0 && pointPos < pField.length-1)
alias = pField;
else
throw new Error(translate.text("${SQL_LIB_FIELD_WRONG_FORMAT}") + pField + translate.withArguments("${SQL_LIB_FIELD_WRONG_FORMAT} field: %0", [pField]));
}
else
{
if (pField.length == 2)
pField.push(pField[0]);
if (pField.length == 3)
{
alias = pField[2] + "." + pField[1];
pField = pField[0] + "." + pField[1];
}
else
throw new Error(translate.text("${SQL_LIB_FIELD_WRONG_FORMAT}") + field.toSource() + translate.withArguments("${SQL_LIB_FIELD_WRONG_FORMAT} field: %0", [field.toSource()]));
}
return [alias, pField]
}
SqlUtils.replaceConditionTemplate = function(pCondition, pPlaceholder, pReplacement)
{
//SqlUtils.replaceConditionTemplate(pCondition, '#', SqlUtils.parseField(pFieldOrCond).join("."))
//this function looks more complex (and slower) than it actually is
/* the following regex looks like this after javascript-escaping of the backslash: (?<!\\)((?:\\\\)*)#
the regexp searches for the unescaped character and these characters are replaced by the field name
examples:
---------------------
| # --match | | \# --no-match |
| \\# --match |
| \\\# --no-match |
| \\\\# --match |
---------------------
*/
//use replaceAll because it's faster and supports negative lookbehinds
var replacements = {};
//manually readd the replaced backslashes by using a group reference, because they a part of the match and therefore replaced by "replaceAll"
//since the field COULD contain already a group reference (I think this is extremely uncommon;
//probably that never happens but better stay save): escape that references within the fieldname
replacements["(?<!\\\\)((?:\\\\\\\\)*)" + pPlaceholder] = "$1" + text.replaceAll(pReplacement, {
"$1": "\\$1"
})
//now that we've replaced the correct field placeholder let's replace the escaped placeholder sign "\#" to a normal placeholder sign "#"
replacements["\\\\" + pPlaceholder] = pPlaceholder
return text.replaceAll(pCondition, replacements);
}
/**
* Checks if the '#' is 0 or 1 time in pCondition, '?' has to be 1 time in pCondition.
* Also checks if '#' is before '?'
* @param {String} pCondition
*
* @return {Boolean} true if the format is ok
*/
SqlUtils.checkConditionFormat = function(pCondition)
{
// replace by {@NUMBERSIGN@} / {@QUESTIONSIGN@} as the js-regexp cannot do lookbehind (needed by the regexp used in replaceConditionTemplate to check escapes)
// so we just use replaceConditionTemplate to replace by something which never should occur anywhere (it uses text.replaceAll which supports lookbehind because it uses java)
pCondition = SqlUtils.replaceConditionTemplate(pCondition, "#", "{@NUMBERSIGN@}")
pCondition = SqlUtils.replaceConditionTemplate(pCondition, "\\?", "{@QUESTIONSIGN@}")
var indexOfNumberSign = pCondition.indexOf("{@NUMBERSIGN@}");
var indexOfQuestionSign = pCondition.indexOf("{@QUESTIONSIGN@}");
return !(indexOfQuestionSign == -1 || indexOfNumberSign > indexOfQuestionSign || indexOfNumberSign != pCondition.lastIndexOf("{@NUMBERSIGN@}") || indexOfQuestionSign != pCondition.lastIndexOf("{@QUESTIONSIGN@}"))
}
/**
* Escapes a jdito variable for the value of a SqlBuilder condition. SqlBuilder.prototype.where/and/or/... automatically resolve the value as a jdito
* variable if it starts with a single '$', so you can use this function to make sure the value is used as it is.
* <br>
* Note: The main purpose of this is to prevent errors resulting from unexpected user inputs. But if you are loading the input from a jdito variable
* anyways, you can just wite the variable name as the condition value and it will be safe.
*
* @param {String} pValue the value
* @return {String} the escaped string
* @example
*
* var sqlCond = newWhere("TABLE.COLUMN", SqlUtils.escapeVars(userInput)); //userInput could start with '$'
*/
SqlUtils.escapeVars = function (pValue)
{
if (typeof(pValue) == "string" && pValue.charAt(0) == "$")
return "$" + pValue;
return pValue;
}