Something went wrong on our end
-
Andre Loreth authoredAndre Loreth authored
process.js 72.86 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
*/
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"));
*/
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 db.translateCondition(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.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.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]]]
*/
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]]]
*/
SqlCondition.equalsNot = function(pField, pValue, pAlternativeCond, pAlias) { return SqlCondition.begin(pAlias).andPrepare(pField, pValue, "# <> ?").build(pAlternativeCond);
}
/**
* Object for building sqls. The main purpose of this is to make it
* possible to use SqlCondition objects inside join conditions or sub sqls.
* This can also be useful to build complex sqls where parts should be added
* dynamically while keeping the code clean.
*
* @class
*/
function SqlBuilder ()
{
if(!(this instanceof SqlBuilder))
throw new Error(translate.text("SqlBuilder must be instanciated with 'new'"));
this._sqlStr = "";
this._select = null;
this._from = null;
this._joins = [];
this._where = null;
this._groupBy = null;
this._having = null;
this._orderBy = null;
this._unions = [];
}
/**
* 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());
*/
SqlBuilder.begin = function ()
{
return new SqlBuilder();
}
/**
* Builds the sql and uses db.translateStatement to make a string out of it.
* @return {String} the sql as string
*/
SqlBuilder.prototype.toString = function ()
{
return db.translateStatement(this.build());
}
/**
* Sets the select clause of the sql. * @param {String|String[]} pFields
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.select = function (pFields)
{
this._select = this._getClause(pFields, "select", 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 = this._getClause(pFields, "select distinct", true);
return this;
}
/**
* Sets the from clause of the sql.
* @param {String} pTable
* @param {String} [pAlias] table alias
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.from = function (pTable, pAlias)
{
if (pAlias)
pTable += " " + pAlias;
this._from = this._getClause(pTable, "from");
return this;
}
/**
* Adds a join clause to the sql.
* @param {String} pTable
* @param {String|String[]|SqlCondition} pCondition The where condition. This can be
* a string (without the where keyword), a SqlCondition or an array (for prepared queries).
* @param {String} [pAlias] table alias
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.join = function (pTable, pCondition, pAlias)
{
var joinStr = "join " + pTable;
if (pAlias)
joinStr += " " + pAlias;
this._joins.push(this._getClause(pCondition, joinStr + " on"));
return this;
}
/**
* Adds a left join clause to the sql.
* @param {String} pTable
* @param {String|String[]|SqlCondition} pCondition The where condition. This can be
* a string (without the where keyword), a SqlCondition or an array (for prepared queries).
* @param {String} [pAlias] table alias
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.leftJoin = function (pTable, pCondition, pAlias)
{
var joinStr = "left join " + pTable;
if (pAlias)
joinStr += " " + pAlias;
this._joins.push(this._getClause(pCondition, joinStr + " on"));
return this;
}
/**
* Sets the where clause of the sql. *
* @param {String|String[]|SqlCondition} pCondition The where condition. This can be
* a string (without the where keyword), a SqlCondition or an array (for prepared queries).
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.where = function (pCondition)
{
this._where = this._getClause(pCondition, "where");
return this;
}
/**
* Sets the order by clause of the sql.
* @param {String} pOrderBy
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.orderBy = function (pOrderBy)
{
this.orderBy = this._getClause(pOrderBy, "order by");
return this;
}
/**
* Sets the group by clause of the sql.
* @param {String|String[]} pFields
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.groupBy = function (pFields)
{
this._groupBy = this._getClause(pFields, "group by", 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(this._getClause(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(this._getClause(pSelect, "union all"));
return this;
}
/**
* Adds a having clause to the sql.
*
* @param {String|String[]|SqlCondition} pCondition The where condition. This can be
* a string (without the where keyword), a SqlCondition or an array (for prepared queries).
*
* @return {SqlBuilder} current SqlBuilder object
*/
SqlBuilder.prototype.having = function (pCondition)
{
this._having = this._getClause(pCondition, "having");
return this;
}
/**
* 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
*/
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];
}
}
/**
* builds a prepared statement out of the object
*
* @return {String[]} prepared statement
*/
SqlBuilder.prototype.build = function ()
{
if (!this._select)
throw new Error(translate.text("SqlBuilder must contain a select clause!"));
if (!this._from)
throw new Error(translate.text("SqlBuilder must contain a from clause!"));
var sqlStr = "";
var preparedVals = [];
var allParts = [
this._select,
this._from
].concat(this._joins).concat([
this._where,
this._groupBy,
this._having,
this._orderBy
]).concat(this._unions);
for (let i = 0, l = allParts.length; i < l; i++)
{
let part = allParts[i];
if (part && part.length)
{
if (sqlStr)
sqlStr += " ";
sqlStr += part[0];
preparedVals = preparedVals.concat(part[1]);
}
}
return [sqlStr, preparedVals];
}
/**
* 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
*/
SqlBuilder.prototype.translate = function(pAlias)
{
return db.translateStatement(this.build(), pAlias);
}
/**
*provides functions for masking sql functions
*
* @param {String} [alias=currentAlias] database alias, you can specify null if you have no alias available and you can manually set the dbType property
*
* @class
*/
function SqlMaskingUtils(alias) {
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 (alias === undefined)
this.alias = vars.getString("$sys.dbalias");
else
this.alias = alias;
}
/**
* returns the concat symbol depending on database type
* @return {String} Concat Symbol
*/
SqlMaskingUtils.prototype.getConcatSymbol = function() {
var concatSymbol; switch(Number(this.dbType)) {
case db.DBTYPE_SQLSERVER2000:
concatSymbol = " + ";
break;
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:
concatSymbol = " || ";
break;
}
return concatSymbol;
}
/**
* returns the trim function depending on the database behin the given alias
* note that this function does not verifiy where the types of your expression are trimable or not
*
* @param {String} field expression that shall be trimmed
*
* @return {String}
*/
SqlMaskingUtils.prototype.trim = function(field) {
var dbType, resultStr;
dbType = this.dbType;
switch(dbType) {
case db.DBTYPE_SQLSERVER2000:
resultStr = "ltrim(rtrim(" + field + "))";
break;
default:
resultStr = "trim(" + field + ")"
break;
}
return resultStr;
}
/**
* 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} field expression
*
* @return {String} sql-part that can be used in a select
*/
SqlMaskingUtils.prototype.max = function(field)
{
return "max(" + field + ")";
}
/**
* 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} field expression
*
* @return {String} sql-part that can be used in a select
*/
SqlMaskingUtils.prototype.min = function(field)
{
return "min(" + field + ")";
}
/**
* 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} field name of the database field that shall be castet
* @param {String} [targetDatatype] a SQLTYPES-value of the following: SQLTYPES.CHAR, SQLTYPES.VARCHAR, SQLTYPES.INTEGER,
* SQLTYPES.DECIMAL, SQLTYPES.DATE
* @param {int|int[]} targetLength 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 (field, targetDatatype, targetLength) {
/* 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 dbType, functionName, sqlPart, sqlDataType, _mapDefaults;
dbType = this.dbType;
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
*/
_mapDefaults = function (dataType) {
var res;
switch(dataType) {
case SQLTYPES.CHAR:
res = "char";
break;
case SQLTYPES.VARCHAR:
res = "char";
break;
case SQLTYPES.INTEGER:
res = "int";
break;
case SQLTYPES.DECIMAL:
res = "decimal";
break;
case SQLTYPES.DATE:
res = "date";
break;
}
return res;
}
switch (dbType) {
case db.DBTYPE_DERBY10:
switch(targetDatatype) {
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
field = "rtrim(" + this.cast(field, SQLTYPES.CHAR, targetLength) + ")";
sqlDataType = "varchar";
break;
case SQLTYPES.CHAR:
sqlDataType = "char";
break;
case SQLTYPES.DECIMAL:
case SQLTYPES.INTEGER:
case SQLTYPES.DATE:
sqlDataType = _mapDefaults(targetDatatype);
break;
} break;
case db.DBTYPE_MARIADB10:
case db.DBTYPE_MYSQL4:
switch(targetDatatype) {
case SQLTYPES.VARCHAR:
case SQLTYPES.CHAR:
case SQLTYPES.INTEGER:
case SQLTYPES.DECIMAL:
case SQLTYPES.DATE:
sqlDataType = _mapDefaults(targetDatatype);
break;
}
break;
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
switch(targetDatatype)
{
case SQLTYPES.VARCHAR:
datatype = "varchar2";
break;
case SQLTYPES.INTEGER:
datatype = "number";
targetLength = "10"
break;
case SQLTYPES.CHAR:
case SQLTYPES.DECIMAL:
case SQLTYPES.DATE:
sqlDataType = _mapDefaults(targetDatatype);
break;
}
break;
case db.DBTYPE_POSTGRESQL8:
switch(targetDatatype)
{
case SQLTYPES.DATE:
case SQLTYPES.DECIMAL:
case SQLTYPES.INTEGER:
case SQLTYPES.CHAR:
case SQLTYPES.VARCHAR:
sqlDataType = _mapDefaults(targetDatatype);
break;
}
break;
case db.DBTYPE_SQLSERVER2000:
case SQLTYPES.DATE:
case SQLTYPES.DECIMAL:
case SQLTYPES.INTEGER:
case SQLTYPES.CHAR:
case SQLTYPES.VARCHAR:
sqlDataType = _mapDefaults(targetDatatype);
break;
case db.DBTYPE_FIREBIRD250:
//TODO: firebird support?
break;
}
if (sqlDataType == undefined) {
throw new Error(translate.withArguments("${SQL_LIB_UNSUPPORTED_DBTYPE} function: %0", ["SqlMaskingUtils.prototype.cast._mapDefaults"]));
}
if(targetLength == undefined)
targetLength = "";
else if(targetLength != "")
{
if(typeof(targetLength == "object") && (targetLength instanceof Array))
targetLength = "(" + targetLength.join(", ") + ")";
else
targetLength = "(" + targetLength + ")";
}
sqlPart = functionName + "(" + field + " as " + sqlDataType + targetLength + ")";
return sqlPart;
}
/**
* masks the cast function for lob datatypes(clob, blob) into varchar or similar
*
* @param {String} field expression that shall be casted
* @param {Integer|Interger[]} targetLength dessired length of the datatype
* decimal: [length, decimals]
*
* @return {String} part of sql-expression that can be used
*/
SqlMaskingUtils.prototype.castLob = function(field, targetLength) {
var res;
switch(this.dbType) {
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
res = "DBMS_LOB.SUBSTR(" + field + ", " + targetLength + ", 1)";
break;
default:
res = this.cast(field, SQLTYPES.VARCHAR, targetLength);
break;
}
return res;
}
/**
* returns the function which determines the length of binary data
*
* @param {String} fieldName name of the checked field
*
* @return {String}
*/
SqlMaskingUtils.prototype.binDataLength = function(fieldName) {
var res;
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:
res = "LENGTH(" + fieldName + ")";
break;
case db.DBTYPE_SQLSERVER2000:
res = "DATALENGTH(" + fieldName + ")";
break;
}
return res;
}
/**
* masks the sql function substring
*
* @param {String } field the expression that shall be substringed
* @param {Number} start posistion where the substring starts
* @param {Number} length 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(field, start, length) {
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 + "(" + field + ", " + start + ", " + length + ")";
}
/**
* 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} fields req fields (or expressions) that should be concatenated
* @param {String} [separatorCharacter=space-character] character for separating the fields; warning: the character will not be quoted
* @param {String} [autoTrimFields=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(fields, separatorCharacter, autoTrimFields) {
var i, concatSql, retSql, isNotEmptyStrSql, isNotNullSql, separatorSql, _isNotEmpty, _trimIfAutoTrimEnabled;
if (fields.length == 0)
return "''";
else if (fields.length == 1)
return fields[0];
concatSql = this.getConcatSymbol();
isNotEmptyStrSql = " != '' ";
isNotNullSql = " is not null ";
separatorSql = separatorCharacter == undefined ? " " : separatorCharacter;
retSql = "";
if (autoTrimFields == undefined)
autoTrimFields = true;
switch(this.dbType)
{
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
case db.DBTYPE_POSTGRESQL8:
retSql = " concat_ws( '" + separatorSql + "'";
for (i = 0; i < fields.length; i++) {
retSql += ", " + fields[i];
}
return retSql + ") ";
break;
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
isNotEmptyStrSql = null; //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"]));
}
separatorSql = concatSql + "'" + separatorSql + "'";
_trimIfAutoTrimEnabled = function(f){
if (autoTrimFields)
return this.trim(f);
else
return f;
}
_trimIfAutoTrimEnabled = _trimIfAutoTrimEnabled.bind(this);
_isNotEmpty = function(f){
return _trimIfAutoTrimEnabled(f) + isNotEmptyStrSql + " and " + f + isNotNullSql;
}
_isNotEmpty = _isNotEmpty.bind(this);
for (i = 0; i < fields.length; i++) {
if (retSql != "")
retSql += concatSql;
retSql += "case when " + _isNotEmpty(fields[i]) + " then ";
if ( i < fields.length - 1 ) //Prüfen, ob ein nachfolgender Wert kommt, sonst braucht man keinen Separator
retSql += " case when " + _isNotEmpty(fields[i + 1]) + " then " + _trimIfAutoTrimEnabled(fields[i]) + separatorSql + " else " + _trimIfAutoTrimEnabled(fields[i]) + " end ";
else
retSql += _trimIfAutoTrimEnabled(fields[i]);
//this blank is used just as in the old concat function which means this concat function has the same (wrong) behaviour
//TODO: find way to fix the case when separator is not a whitepsace (e.g. space)
//this concat-function does not work properly if you concat [<<value>>, <<null>>, <<value>>] by comma
retSql += " else ' ' end ";
}
return retSql;
}
/**
* returns the function for replacing a null value
*
* @param {String} field expression that shall be checked for a null value
* @param {String} [replaceWith=empty string] expression that shall be used if the field contains null
*
* @return {string}
*/
SqlMaskingUtils.prototype.isNull = function(field, replaceWith) {
var retSql;
if (replaceWith == undefined)
replaceWith = "''";
switch(this.dbType) {
case db.DBTYPE_SQLSERVER2000:
retSql = "isnull(" + field + ", " + replaceWith + ")";
break;
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_OCI:
case db.DBTYPE_ORACLE10_THIN :
retSql = "nvl(" + field + ", " + replaceWith + ")";
break;
case db.DBTYPE_POSTGRESQL8:
case db.DBTYPE_DERBY10:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
default:
retSql = "coalesce(" + field + ", " + replaceWith + ")";
break;
}
return retSql;
}
/**
* 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)
{
var retSql = "";
switch (this.dbType)
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
retSql = "to_char(" + pField + ",'dd')";
break;
case db.DBTYPE_DERBY10:
case db.DBTYPE_SQLSERVER2000:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
retSql = "DAY(" + pField + ")";
break;
case db.DBTYPE_POSTGRESQL8:
retSql = "EXTRACT (DAY from " + pField + ")";
break;
}
return retSql;
}
/**
* 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)
{
var retSql = "";
switch (this.dbType)
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
retSql = "to_char(" + pField + ",'MM')";
break;
case db.DBTYPE_DERBY10:
case db.DBTYPE_SQLSERVER2000:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
retSql = "MONTH(" + pField + ")";
break;
case db.DBTYPE_POSTGRESQL8:
retSql = "EXTRACT (MONTH FROM " + pField + ")";
break;
}
return retSql;
}
/**
* 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)
{
var retSql = "";
switch (this.dbType)
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
retSql = "to_char(" + pField + ",'yyyy')";
break;
case db.DBTYPE_DERBY10:
case db.DBTYPE_SQLSERVER2000:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
retSql = "YEAR(" + pField + ")";
break;
case db.DBTYPE_POSTGRESQL8:
retSql = "EXTRACT (YEAR FROM " + pField + ")";
break;
}
return retSql;
}
/**
* 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/> 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
* e.g. {table: "ORGANISATION", column: "NAME"}
*
*
*/
SqlUtils._parseFieldQualifier = function(pFieldOrTableName, pColumnName)
{
var tableName, columnName;
if (pColumnName != undefined)
{
tableName = pFieldOrTableName;
columnName = pColumnName;
}
else
{
var fnName = "SqlUtils._parseFieldQualifier";//for return errors
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 (pFieldOrTableName.hasOwnProperty("length"))
{
if (pFieldOrTableName.length != 2)
return new TypeError(translate.withArguments("[%0]has now an incorrect length; estimated 2 elements but got %1", [
fnName, pFieldOrTableName.length ]));
tableName = pFieldOrTableName[0];
columnName = pFieldOrTableName[1];
}
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 (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]));
return {
table: tableName,
column: columnName
};
};
/**
* 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/> 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/> 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) {
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"));
}
}
var MAX_COUNT = 1000;
//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 + " 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 + " 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);
};
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;
};
/**
* Will quote all prepared statement values from the given statement.
* @param {[String, String[]]} pStatement Same as first paraemter of db.translateStatement.
* @param {([String, String[]]) => String} pExecutionCallback 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 (!(pStatement instanceof Array))
return null;
// The second element of the array has to be an array.
if (!(pStatement[1] instanceof Array))
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 (!(pValue instanceof Array))
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 {[String, String[]]} pStatement Same as the first parameter of db.translateStatement.
* @returns {String} The SQL, same as the result of db.translateStatement.
*/
SqlUtils.translateStatementWithQuotes = function (pStatement) {
return SqlUtils.translateWithQuotes(pStatement, db.translateStatement);
}
/**
* Will quote all prepared statement values from the given statement.
* @param {[String, String[]]} pStatement Same as the first parameter of db.translateCondition.
* @returns {String} The SQL, same as the result of db.translateCondition.
*/
SqlUtils.translateConditionWithQuotes = function (pStatement) {
return SqlUtils.translateWithQuotes(pStatement, db.translateCondition);
}