import("system.translate");
import("system.vars");
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 conditions
* @example //TODO: add missing example
*/
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;
}
/**
* append with SQL-and; no paranthesize of existing conditions is done
* @param {String} cond the condition string which shall be appended
* @return {Object} current SqlCondition-object
* @memberof SqlCondition
*/
SqlCondition.prototype.and = function(cond){
if (!cond)
return this;
if (this._sqlStorage)
this._sqlStorage += " and ";
this._sqlStorage += cond;
return this;
}
/**
* same as the "and"-function but with preparedStatement functionality
* @param {String} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
* @param {String} value the value that shall be set into the prepared statement
* @param {String} cond opt 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} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
* @return {Object} current SqlCondition-object
*/
SqlCondition.prototype.andPrepare = function(field, value, cond, fieldType){
cond = this._prepare(field, value, cond, fieldType);
return this.and(cond);
}
/**
* append with SQL-or; Also paranthesize the existing conditions
* @param {String} cond the condition string which shall be appended
* @return {Object} current SqlCondition-object
*/
SqlCondition.prototype.or = function(cond){
if (!cond)
return this;
if (this._sqlStorage)
this._sqlStorage = "(" + this._sqlStorage + ") or (" + cond + ")";
else
this._sqlStorage = cond;
return this;
}
/**
* same as the "or"-function but with preparedStatement functionality
* @param {String} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
* @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} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
* @return {Object} 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 with validation of adito-variables functionality
* @param {String} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
* @param {String} variable the adito-variable that shall be set into the prepared statement
* @param {String} cond opt 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} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
* @return {Object} 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} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
* @param {String} variable the adito-variable that shall be set into the prepared statement
* @param {String} cond opt 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} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
* @return {Object} 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;
}
/**
* ready to use string; does not contain a where keyword at the beginning
* @param {String} alternativeCond opt condition that is returned when nothing has been appended. Default is "1 = 1".
* @return {String} concatenated SQL-condition; empty string if nothing has been appended or - if passed - the alternativeCond
*/
SqlCondition.prototype.toString = function(alternativeCond){
if (alternativeCond == undefined) { alternativeCond = "1 = 1" }
if (!this._sqlStorage && alternativeCond)
return alternativeCond
else
return this._sqlStorage;
}
/**
* ready to use string; does contain a where keyword at the beginning
* @param {String} alternativeCond opt condition that is returned when nothing has been appended. Default is "1 = 1".
* @return {String} 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 opt Condition that is returned when nothing has been appended. Default is "1 = 1".
* @return {Array[][][]} Prepared condition with [condition, [[field1, type1], [field2, type2]]]
*/
SqlCondition.prototype.build = function(alternativeCond){
return [cond.toString(alternativeCond), cond.preparedValues];
}
/**
* ready to use prepared select
* @param {String} beforeCondition Part of the sql before the condition without where (e.g. "select FIRSTNAME from PERS")
* @param {String} alternativeCond opt Condition that is returned when nothing has been appended. Default is "1 = 1".
* @param {String} afterCondition opt Part of the sql after the condition (e.g. "order by FIRSTNAME"). Default is "".
* @return {Array[][][]} Prepared condition with [condition, [[field1, type1], [field2, type2]]]
*/
SqlCondition.prototype.buildSelect = function(beforeCondition, alternativeCond, afterCondition){
if (afterCondition == undefined) { afterCondition = "" };
return [beforeCondition + " " + cond.toWhereString(alternativeCond) + " " + afterCondition, cond.preparedValues];
}
/**
* Check if (adito-)variable exists and vars.getString is not empty
* @param {String} variable the variable name (e.g. "$field.RELATION_ID")
* @return {String | Boolean} The value of the field as string OR false if it doesn't exist.
*/
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} field the database field as "tablename.columnname"; e.g. "ORG.NAME"
* @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} [fieldType] SQL-column-type; if the fieldType is not given it's loaded automatically;
* please note that this can be a performace issue if it happens a lot of times (e.g. in aloop)
* @return {String} the replaced SQL-condition string (replace # by the fieldname)
*/
SqlCondition.prototype._prepare = function(field, value, cond, fieldType){
if (cond == undefined) { cond = "# = ?" }
var type;
//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(field, {"$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 || SqlUtils.getSingleColumnType(field, undefined, this.alias);
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 null;
}
/**
* hidden function for initializing all properties for the sql conditions
* @return {null}
*/
SqlCondition.prototype._init = function(){
//init only wraps the clear function to avoid confusion in the constructor (and provide better extensibility)
return this.clear();
}
/**
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
*/
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;
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 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;
}
/**
* 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
*
* @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){
//TODO: use callbacks for different handling?
/* 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:
//TODO: throw error if(targetLength > 254)? https://db.apache.org/derby/docs/10.14/ref/rrefsqlj13733.html
sqlDataType = "char";
break;
case SQLTYPES.DECIMAL:
case SQLTYPES.INTEGER:
case SQLTYPES.DATE:
sqlDataType = _mapDefaults(dataType);
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("sqlDataType");//TODO: add usefull message
}
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, "varchar", targetLength);
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();//ToDo: add message
}
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 = " || ";
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:
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
concatSql = " + ";
break;
case db.DBTYPE_DERBY10:
break;
default:
throw new Error();//TODO: add Message
}
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]);
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;
}
function SqlUtils(){
}
SqlUtils.getSingleColumnType = function(fieldOrTableName, columnName, alias) {
var tableName, fieldVarType;
if (columnName == undefined){
fieldVarType = typeof(fieldOrTableName);
if (fieldVarType == "string")
fieldOrTableName = text.split(fieldOrTableName, "\\.");
else if (fieldVarType != "object"){
throw new TypeError();//TODO: add message
}
if (fieldOrTableName.hasOwnProperty("length") && fieldOrTableName.length == 2)
{
tableName = fieldOrTableName[0];
columnName = fieldOrTableName[1];
}
else
throw new TypeError();//TODO: add message
}
else
tableName = fieldOrTableName;
if (typeof(columnName) != "string")
throw new TypeError();//TODO: add message
if (typeof(tableName) != "string")
throw new TypeError();//TODO: add message
if (alias == undefined)
alias = db.getCurrentAlias();
return db.getColumnTypes(tableName, [columnName], alias)[0];
}
/**
*Class containing utilities for SQL
*@deprecated use SqlMaskingUtils
*@class
*/
function LegacySqlUtils()
{
var that = this;
/**
* builds a condition out of multiple conditions
*
* @param {Array} pArray req Array containing the conditions
* @param {String} pOperator req Operator that concatenates the conditions (AND/OR)
*
* @return {String} concatenated Condition
*/
this.concatConditions = function(pArray, pOperator)
{
var resultCondition = "";
for(var i = 0; i < pArray.length; i++)
{
if(pArray[i] != null && pArray[i] != '')
{
if(resultCondition.length > 0)
resultCondition += (" " + pOperator + " ");
resultCondition += pArray[i];
}
}
return resultCondition;
}
/**
* Checks if a new entry already exists
*
* @param {String} pTable req Databasetable(z.B. "comm")
* @param {Array} pColumns req colums, like sqlInsert
* @param {Array} pTypes req die datatypes, like sqlInsert
* @param {Array} pValues req values, like sqlInsert
* @param {Array} pExcludeFields opt columns, that should not be checked
* @param {String} pAlias opt Database alias
*
* @return {Integer}
*/
this.isDuplicat = function(pTable, pColumns, pTypes, pValues, pExcludeFields, pAlias)
{
var col = new Array();
var typ = new Array();
var val = new Array();
var excludefields = ["DATE_NEW" ,"DATE_EDIT" ,"USER_NEW" ,"USER_EDIT", "KEYVALUE", "KEYSORT", pTable.toUpperCase() + "ID"];
if(pExcludeFields != undefined)
excludefields = excludefields.concat(pExcludeFields);
if(pAlias == undefined)
pAlias = vars.getString("$sys.dbalias");
for(var i = 0; i < pColumns.length; i++)
{
if(!hasElement(excludefields, pColumns[i], true) && pValues[i] != "" && pTypes[i] != SQLTYPES.LONGVARCHAR && pTypes[i] != SQLTYPES.CLOB)
{
col.push(pColumns[i]);
typ.push(pTypes[i]);
val.push(pValues[i]);
}
}
var count = db.getRowCount(pTable, col, typ, val, pAlias);
return count;
}
/**
* returns the day of a date
*
* @param {Datetime} pDate req the date
* @param {String} pAlias req database alias
*
* @return {String}
*/
this.dayFromDate = function(pDate, pAlias)
{
var usedAlias = pAlias;
if(pAlias == undefined)
usedAlias = vars.getString("$sys.dbalias");
var dbAlias = db.getDatabaseType(usedAlias);
var string = "";
switch(Number(dbAlias))
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
string = "to_char(" + pDate + ",'dd')";
break;
case db.DBTYPE_DERBY10:
case db.DBTYPE_SQLSERVER2000:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
string = "DAY(" + pDate + ")";
break;
case db.DBTYPE_POSTGRESQL8:
string = "EXTRACT (DAY from " + pDate + ")";
break;
}
return string;
}
/**
* returns the month of a date
*
* @param {Datetime} pDate req the date
* @param {String} pAlias req database alias
*
* @return {String}
*/
this.monthFromDate = function(pDate, pAlias)
{
var usedAlias = pAlias;
if(pAlias == undefined)
usedAlias = vars.getString("$sys.dbalias");
var dbAlias = db.getDatabaseType(usedAlias);
var string = "";
switch(Number(dbAlias))
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
string = "to_char(" + pDate + ",'MM')";
break;
case db.DBTYPE_DERBY10:
case db.DBTYPE_SQLSERVER2000:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
string = "MONTH(" + pDate + ")";
break;
case db.DBTYPE_POSTGRESQL8:
string = "EXTRACT (MONTH FROM " + pDate + ")";
break;
}
return string;
}
/**
* returns the year of a date
*
* @param {Datetime} pDate req the date
* @param {String} pAlias req database alias
*
* @return {String}
*/
this.yearFromDate = function(pDate, pAlias)
{
var usedAlias = pAlias;
if(pAlias == undefined)
usedAlias = vars.getString("$sys.dbalias");
var dbAlias = db.getDatabaseType(usedAlias);
var string = "";
switch(Number(dbAlias))
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
string = "to_char(" + pDate + ",'yyyy')";
break;
case db.DBTYPE_DERBY10:
case db.DBTYPE_SQLSERVER2000:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
string = "YEAR(" + pDate + ")";
break;
case db.DBTYPE_POSTGRESQL8:
string = "EXTRACT (YEAR FROM " + pDate + ")";
break;
}
return string;
}
/**
* returns the function for current date depending on database
*
* @return {String} expression
*/
this.currentDate = function()
{
var dbtype = db.getDatabaseType(vars.getString("$sys.dbalias"));
var expression = "";
switch (Number(dbtype))
{
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_THIN:
case db.DBTYPE_ORACLE10_OCI:
case db.DBTYPE_DERBY10:
expression = "CURRENT_DATE";
break;
case db.DBTYPE_SQLSERVER2000:
expression = "GETDATE()";
break;
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
expression = "NOW()";
break;
}
return expression;
}
/**
* returns the current search string incl placeholders
*
* @param {String} pfield req the search field
* @param {String} pfind req the search string
* @param {String} pIgnoreCase opt (true/false)
* @param {String} pPlaceHolder opt (Platzhalter config)
*
* @return {String}
*/
this.getPlacerholderCondition = function( pfield, pfind, pIgnoreCase, pPlaceHolder )
{
var user = tools.getCurrentUser();
var IgCa;
var PlHo;
//wenn optoinal IgnoreCase und PlaceHolder vorhanden, dann diese verwenden
if(pIgnoreCase != undefined)
IgCa = pIgnoreCase;
else
IgCa = user[tools.PARAMS][tools.SELECTION_IGNORECASE];
if(pPlaceHolder != undefined)
PlHo = pPlaceHolder;
else
PlHo = user[tools.PARAMS][tools.SELECTION_PLACEHOLDER];
if ( pfind )
{
pfind = pfind.replace( new RegExp("\\'", "g"), "''");
pfind = pfind.replace( new RegExp("\\*", "g"), "%");
var ic = (IgCa == "true" ? "UPPER" : "");
var cond = "";
switch( PlHo )
{
case "1":
cond = ic + "(" + pfield + ") like " + ic + "('" + pfind + "%')";
break;
case "2":
cond = ic + "(" + pfield + ") like " + ic + "('%" + pfind + "')";
break;
case "3":
cond = ic + "(" + pfield + ") like " + ic + "('%" + pfind + "%')";
break;
case "4":
cond = ic + "(" + pfield + ") like " + ic + "('" + pfind + "')";
break;
default:
cond = ic + "(" + pfield + ") = " + ic + "('" + pfind + "')";
break;
}
}
return cond;
}
/**
* returns SQLSystax for a date.
*
* @param {String} pColumn req Column name
* @param {String} pAlias opt Database alias
* @param {Boolean} pWithTime opt if true, then add time hh:mm:ss
* @param {String} pFormat opt type of the format, e.g. yyyy-MM-dd; possible placeholers are: -dd -MM -yyyy
*
*@return {String} sqlstr, fully functional select for sql for different types of databases
*/
this.getSqlFormattedDate = function(pColumn, pAlias, pWithTime, pFormat)
{
if(pAlias == undefined)
pAlias = vars.getString("$sys.dbalias");
var pDatabaseType = db.getDatabaseType(pAlias);
if (pFormat == undefined)
pFormat = translate.text("yyyy-MM-dd");
var str = "";
switch(Number(pDatabaseType))
{
case db.DBTYPE_SQLSERVER2000:
day = "right('0' + cast(day(" + pColumn + ") as varchar(2)) , 2)";
month = "right('0' + cast(month(" + pColumn + ") as varchar(2)) , 2)";
year = "cast(year(" + pColumn + ") as char(4))";
time = pWithTime == true? that.concat(["' '","cast(cast(" + pColumn + " as time) as char(8))"], "", pAlias) : "";
break;
case db.DBTYPE_POSTGRESQL8:
day = "extract(day from " + pColumn + ")";
month = "extract(month from " + pColumn + ")";
year = "extract(year from " + pColumn + ")";
time = pWithTime == true? that.concat(["' '","extract(time from" + pColumn + ")"], "", pAlias) : "";
break;
case db.DBTYPE_DERBY10:
case db.DBTYPE_MYSQL4:
case db.DBTYPE_MARIADB10:
// concat will try to have a leading blank space if the number is => 10. This is why we had to use substr.
day = that.substring(that.concat(["case when day(" + pColumn + ") <= 9 then '00' else '0' end "
, " trim(cast(day(" + pColumn + ") as char(2)))"], "", pAlias), 2, 2, pAlias);
month = that.substring(that.concat(["case when month(" + pColumn + ") <= 9 then '00' else '0' end "
, "trim(cast(month(" + pColumn + ") as char(2)))"], "", pAlias), 2, 2, pAlias);
year = "trim(cast(year(" + pColumn + ") as char(4)))";
time = pWithTime == true? that.concat(["cast(' ' as char(1))", "trim(cast(time(" + pColumn + ") as char(8)))"], "", pAlias) : "";
break;
case db.DBTYPE_ORACLE10_CLUSTER:
case db.DBTYPE_ORACLE10_OCI:
case db.DBTYPE_ORACLE10_THIN:
day = "to_char(" + pColumn + ", 'dd') ";
month = "to_char(" + pColumn + ", 'MM') ";
year = "to_char(" + pColumn + ", 'yyyy') ";
time = pWithTime == true ? " to_char(" + pColumn + ", ' hh24:mi:ss')" : "";
break;
default:
str = "cast(" + pColumn + " as varchar (10))";
return str;
break;
}
var re = /(dd)|(MM)|(yyyy)/g // Regex to check the date
var matchResult;
var endOfLastMatch = 0;
var res = [];
while ((matchResult = re.exec(pFormat)) !== null)
{
if( endOfLastMatch != matchResult.index)
{
res.push("'" + db.quote(pFormat.substring(endOfLastMatch, matchResult.index), pAlias) + "'"); // making sure we get the correct amount of quotations
}
switch(matchResult[0])
{
case "dd":
res.push(day);
break;
case "MM":
res.push(month);
break;
case "yyyy":
res.push(year);
break;
}
endOfLastMatch = re.lastIndex;
}
// making sure we get the correct amount of quotations
// allows us to add custom strings behind the format which will be shown in the output
// e.g. "yyyy-MM-dd 00:00", "date: MM/dd/yyyy"
res.push("'" + db.quote(pFormat.slice(endOfLastMatch), pAlias) + "'");
if(time != "")
res.push(time);
str = concat(res, "", pAlias);
return str;
}
/**
* returns a SQL operator depending on an integer value, i.e. $local.operator
*
* @param {int} pVal
*
* @return {string}
*/
this.getSQLOperator = function(pVal)
{
var retval = "";
switch(Number(pVal))
{
case 1:
retval = "=";
break; //equals
case 2:
retval = "<>";
break; //not equal
case 3:
retval = ">";
break; //greater
case 4:
retval = "<";
break; //lesser
case 5:
retval = "<=";
break; //lesser or equal
case 6:
retval = ">=";
break; //greater or equal
case 7:
retval = "like";
break; //contains
case 8:
retval = "not like";
break; //contains not
case 9:
retval = "";
break;
case 10:
retval = "";
break;
case 11:
retval = "is not null";
break;
case 12:
retval = "is null";
break;
}
return retval;
}
/**
* returns the function which determines the length of binary data, depending on db type
*
* @param {String} pField name of the checked field
*
* @return {String}
*/
this.binDataLength = function(pField)
{
var dbtype = db.getDatabaseType(vars.getString("$sys.dbalias"));
var length;
switch(Number(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:
length = "LENGTH("+pField+")";
break;
case db.DBTYPE_SQLSERVER2000:
length = "DATALENGTH("+pField+")";
break;
}
return length;
}
/**
* returns the concat symbol depending on database type
*
* @param {String} pAlias opt database alias
*
* @return {String} Concat Symbol
*/
this.getConcatSymbol = function(pAlias)
{
if(pAlias == undefined || pAlias == "")
pAlias = vars.getString("$sys.dbalias");
var dbtype = db.getDatabaseType(pAlias);
var concatSymbol = " ";
switch(Number(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;
}
/**
* 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.RELATIONID
* @param {String[]|String[][]} pData req Data as ID Array
* @param {String} pQuoteSymbol opt symbol for quoting values,
* Strings i.e.: ' default is no symbol
*
* @return {String} SQL condition: where VALS in (1,2,3)
*/
this.getSqlInStatement = function(pFieldname, pData, pQuoteSymbol)
{
if (pData.length == 0)
return " 1 = 2 ";
var res = "";
var qs = pQuoteSymbol || "";
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 ";
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 + ")";
return res;
}
/**
* Setzt eine Condition zusammen und liefert sie zurück
* builds a conditions and returns it
*
* @param {Object} pValue req Filtervalue
* @param {String} pCondition req variable in which the condition should be written
* @param {String} pWhere req additional condition
* @param {Integer} pSQLType opt SQLTYPES type of pValue
* @param {Array} pPreparedValues opt Value for the condition, if it's a prepared statement
*
* @return {String}
*/
this.makeCondition = function( pValue, pCondition, pWhere, pSQLType, pPreparedValues)
{
if ( pValue != "" )
{
if ( pCondition != "" )
pCondition += " and ";
pCondition += pWhere;
if(pPreparedValues != undefined)
{
pPreparedValues.push([pValue, pSQLType]);
}
}
return pCondition;
}
/**
* returns a type of column in the database
*
* @param {String} pTableName req name of a table (e.g. "EVENT") OR if pColumnName is not passed table.column (e.g. "EVENT.STATUS")
* @param {String} pColumnName opt name of column (e.g. "STATUS") if in pTableName only tablename is passed
* @param {String} pAlias opt Alias to the database where the type should be loaded; default is current alias
*
* @return {String} type of column such as SQLTYPES.xyz
*/
this.getSingleColumnType = function(pTableName, pColumnName, pAlias)
{
if (pColumnName == undefined)
{
pColumnName = pTableName.substring(pTableName.indexOf(".") + 1);
pTableName = pTableName.substring(0, pTableName.indexOf("."));
}
if (pAlias == undefined)
pAlias = db.getCurrentAlias();
return db.getColumnTypes(pTableName, [pColumnName], 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} pSql req 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} pBlockSize req Amount of records that shall be read per block. (you need to specify an ORDER BY in your SQL-query)
* "0" <=> all records
* @param {Object (function)} pCallback req 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} pDbAlias opt Database-Aliasname, where the SQL-Statement shall be executed; default is the current dbalias
* @param {Number} pTimeout opt Timeout in milliseconds; When it's reached the SQL-Statement will abort; default is in PREFERENCES configured
* @param {Number} pStartOffset opt 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 ORG";
* var blockSize = 5 * 1000;
*
* var allRows = +db.cell("select count(*) from ORG");
*
* sqlPageData(sql, blockSize, function (pData, pRunNo){
* var j = pData.length;//pData is the current block with data
* logging.log(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);
* logging.log("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;
* logging.log("count:" + count);//you cannot overwrite a variable of 'sqlPageData' by accident
* });
*
* logging.show(letValues);//contains orgnames
* logging.show(varValues);//contains orgnames
*/
this.sqlPageData = function(pSql, pBlockSize, pCallback, pDbAlias, pTimeout, pStartOffset)
{
if (pDbAlias == undefined)
pDbAlias = db.getCurrentAlias();
if (pStartOffset == undefined)
pStartOffset = 0;
let count = 0;
while (pStartOffset > -1)
{
let data;
if (pTimeout == undefined)
data = db.tablePage(pSql, pDbAlias, pStartOffset, pBlockSize);
else
data = db.tablePage(pSql, pDbAlias, pStartOffset, pBlockSize, pTimeout);
pStartOffset += pBlockSize;
//this happens when all-records % pBlockSize == 0
//we do not want to call the callback-fn
if (data.length == 0)
return true;
else if (data.length < pBlockSize || pBlockSize == 0)//blocksize 0 is everything
pStartOffset = -1;//call callback the last time
if (pCallback.call(this, data, ++count) === false)
return false;//callback can return false to manually stop the paging-process
}
return true;
}
}