xquery version "3.1";
(:Module Namespace:)
module namespace sqlQueryGenerator="sqlQueryGenerator.xqm";
(:Namespace Declerations:)
declare namespace sql="http://exist-db.org/xquery/sql";
declare namespace xmldb="http://exist-db.org/xquery/xmldb";
declare namespace json="http://www.json.org";
import module namespace server="mod-server" at "xmldb:exist:///db/kwantu-resource/lib/mod-server.xql";
import module namespace SDOtoMySQL="SDOtoMySQL.xqm" at "xmldb:exist:///db/kwantu-resource/etl/couchdbToMySQL/etl_SDOtoMySQL.xqm";
import module namespace format="format" at "xmldb:exist:///db/kwantu-resource/collectorServices/format.xqm";
import module namespace elasticRep="elasticRep" at "xmldb:exist:///db/kwantu-resource/modules/elasticReports.xqm";
import module namespace elasticSearch="elasticSearch.xqm" at "xmldb:exist:///db/kwantu-resource/etl/elasticSearch/elasticSearch.xqm";
import module namespace performance="performance.xqm" at "xmldb:exist:///db/kwantu-resource/etl/elasticSearch/performance/performance.xqm";
import module namespace webhook2="webhook2" at "xmldb:exist:///db/kwantu-resource/modules/webhook2.xqm";
import module namespace couch-gateway="http://www.kwantu.net/api/couchbase/couch-gateway.xqm" at "xmldb:exist:///db/kwantu-resource/api/couchbase/couch-gateway.xqm";
declare variable $sqlQueryGenerator:maxElementLength := 100;
declare function sqlQueryGenerator:getDoc($docId as xs:string, $communityId as xs:string ){
let $useCouchDb := $server:sync-bucket||"_" || $communityId
let $inputDoc1:= couch-gateway:fetchCouchbaseDoc($docId,$useCouchDb)
return $inputDoc1
};
declare function sqlQueryGenerator:getConfig($docId as xs:string){
let $useCouchDb := $server:sync-config-bucket
let $inputDoc1:= couch-gateway:fetchCouchbaseDoc($docId,$useCouchDb)
return $inputDoc1
};
declare function sqlQueryGenerator:getOperator($string as xs:string, $value) {
switch ($string)
case "eq"
return " = '" || $value || "'"
case "ne"
return " != '" || $value || "'"
case "starts"
return " LIKE '" || $value || "%'"
case "gt"
return " > '" || $value || "'"
case "lt"
return " < '" || $value || "'"
case "exists"
return " EXISTS(`" || $value || "`)"
case "notexists"
return " NOT(EXISTS(`" || $value || "`))"
case "contains"
return " LIKE '%" || $value || "%'"
default
return " = '" || $value || "'"
};
declare function sqlQueryGenerator:getJoinType($string as xs:string) {
switch ($string)
case "full"
return " JOIN"
case "left"
return " LEFT JOIN"
case "inner"
return " INNER JOIN"
default
return " JOIN"
};
declare function sqlQueryGenerator:checkViewExists($view as xs:string, $dbName as xs:string, $connection as xs:long) {
let $sql := "SELECT COUNT(*) `FOUND` FROM `information_schema`.`VIEWS` WHERE `TABLE_SCHEMA` = '" || $dbName || "' AND `TABLE_NAME` = '" || $view || "'"
let $execute := server:executeSQL($sql, $connection)
return
if(($execute//sql:row/sql:FOUND/text() != 1) or (data($execute/@isError)eq "true"))
then(false())
else(true())
};
declare function sqlQueryGenerator:strToSQLField($string as xs:string){
"`" || $string || "`"
};
declare function sqlQueryGenerator:getValidSQLElement($string as xs:string, $type as xs:string) {
switch ($string)
case "_profileId"
return "`G_PROFILEUUID`"
case "_subprofileId"
return "`G_SUBPROFILEUUID`"
case "_communityId"
return "`G_COMMUNITYID`"
case "_id"
return "`G_UUID`"
case "_applicationId"
return "`G_APPID`"
case "_seq"
return "`G_SEQ`"
case "_seqKey"
return "`seqKey`"
default
return
if(contains($string, "."))
then(
let $split := tokenize($string, "[.]")
let $log := util:log("warn","debug getValidSQLElement $split: "|| format:map2jsonNoIndent($split))
return
switch ($split[2])
case "code"
return
upper-case("`" || substring($split[1],1,$sqlQueryGenerator:maxElementLength) || "_CODE`")
case "label"
return
upper-case("`" || substring($split[1],1,$sqlQueryGenerator:maxElementLength) || "`")
case "uuid"
return
upper-case("`" || substring($split[1],1,$sqlQueryGenerator:maxElementLength) || "_UUID`")
case "latitude"
return
upper-case("`" || substring(substring-before($string, ".latitude"),1,$sqlQueryGenerator:maxElementLength) || "_LATITUDE`")
case "longitude"
return
upper-case("`" || substring(substring-before($string, ".longitude"),1,$sqlQueryGenerator:maxElementLength) || "_LONGITUDE`")
case "timestamp"
return
upper-case("`" || substring(substring-before($string, ".timestamp"),1,$sqlQueryGenerator:maxElementLength) || "_TIMESTAMP`")
case ""
return
upper-case("`" || substring($string,1,$sqlQueryGenerator:maxElementLength) || "`")
default
return
upper-case("`" || substring($split[1],1,$sqlQueryGenerator:maxElementLength) || "_" || $split[2] || "`")
)
else(upper-case("`" || substring($string,1,$sqlQueryGenerator:maxElementLength) || "`"))
};
declare function sqlQueryGenerator:getXMLFile( $setId as xs:string,
$version as xs:string){
let $configFileName := $setId||"."||$version||".xml"
let $url := $server:configCache||"/"||$configFileName
let $getConfig := doc($url)
return $getConfig
};
declare function sqlQueryGenerator:updateQuery($query as xs:string, $tables as item()*, $dbName as xs:string, $cnt as xs:integer, $max as xs:integer) {
if($cnt eq $max)
then($query)
else(
let $table := $tables[$cnt]
let $viewId := $table?viewId,
$tableName := sqlQueryGenerator:validMySQLElement($dbName) || "." || sqlQueryGenerator:validMySQLElement($table?tableName)
return
let $query := replace($query, "#" || $viewId || "#", $tableName)
return sqlQueryGenerator:updateQuery($query, $tables, $dbName, $cnt + 1, $max)
)
};
declare function sqlQueryGenerator:getDbName($communityId as xs:string, $connection as xs:long) {
let $query := "SELECT `databaseName` FROM `mobile1_8`.`community_hubs` WHERE `communityId` = '" || $communityId || "' "
let $run := server:executeSQL($query, $connection)
return $run//sql:databaseName/text()
};
declare function sqlQueryGenerator:getAppCode($appId as xs:string, $connection as xs:long) {
let $query := "SELECT `app_file_precode` FROM `mobile1_8`.`etl_appcodes` WHERE `uuid` = '" || $appId || "' "
let $run := server:executeSQL($query, $connection)
return $run//sql:app_file_precode/text()
};
declare function sqlQueryGenerator:validMySQLElement($Sql as xs:string){
let $cleaned := "`" || substring($Sql,1,$sqlQueryGenerator:maxElementLength) || "`"
return upper-case($cleaned)
};
declare function local:cleanFieldCode($field as xs:string, $isSearch as xs:boolean) {
let $tokenized := tokenize($field, "-")
let $cleaned := $tokenized[last()]
return
if($isSearch)
then(
if(contains($cleaned, ["label", "code"]))
then(
if(contains($cleaned, "label"))
then(replace($cleaned, "label", ".label"))
else(replace($cleaned,"code",".code"))
)
else($cleaned)
)
else($cleaned)
};
declare function local:cleanFieldCode($field as xs:string) {
let $type :=
if(contains($field, "[.]"))
then("." || tokenize($field, "[.]")[last()])
else("")
let $cleaned := replace($field, $type, "")
let $tokenized := tokenize($cleaned, "[-.]")
let $cleaned := $tokenized[last()]
return $cleaned || $type
};
declare function sqlQueryGenerator:getDataTypes($communityId as xs:string, $tableName as xs:string, $connection as xs:long) {
let $dbName := sqlQueryGenerator:getDbName($communityId, $connection)
let $sql := "SELECT `COLUMN_NAME`, `DATA_TYPE`, `CHARACTER_MAXIMUM_LENGTH` FROM `information_schema`.`COLUMNS` WHERE `TABLE_SCHEMA` = '" || $dbName || "' AND `TABLE_NAME` = '" || $tableName || "'"
let $execute := server:executeSQL($sql, $connection)
return
if($execute/@isError eq "true")
then()
else(
let $data :=
for $row in $execute/sql:row
return map {
"field": data($row/sql:COLUMN_NAME),
"dataType": data($row/sql:DATA_TYPE),
"length": data($row/sql:CHARACTER_MAXIMUM_LENGTH)
}
return $data
)
};
declare function sqlQueryGenerator:getTableName($communityId as xs:string, $viewId as xs:string, $context as xs:string, $dbName as xs:string, $connection as xs:long) {
let $context := if($context eq "HIST")then("")else(lower-case($context || "_"))
let $config := sqlQueryGenerator:getConfig($viewId)?result
return
for $view in $config?view?*
let $setId := $view?categoryId,
$ver := $view?categoryVersion,
$basepath := $view?basepath,
$appId := $view?applicationId
let $appCode := sqlQueryGenerator:getAppCode($appId, $connection)
let $xml := sqlQueryGenerator:getXMLFile($setId, $ver)
let $sql := $xml//component/configuration/indicator/set//etl/mysql
return
if(string-length($basepath) eq 0)
then(
let $tableName :=
if (string-length(data($sql/@tableName)) eq 0)
then ($appCode || "_" || $setId)
else (data($sql/@tableName))
return
map {
"viewId": $viewId,
"tableName": $context || $tableName
}
)
else(
let $repeatId := (tokenize($basepath, "[.]"))[1]
let $tableCode := if (string-length(data($sql/@tableCode)) eq 0) then ("") else (data($sql/@tableCode) || "_")
let $tableName := $appCode ||"_"|| $tableCode || $repeatId
return
map {
"viewId": $viewId,
"tableName": $context || $tableName,
"repeatId": $repeatId
}
)
};
declare function sqlQueryGenerator:generateSubquery($subqueryConfig as item()*, $setId as xs:string) {
let $query :=
switch ($setId)
case "SDOSqlSingleObjectViews"
return
let $select :=
for $field in $subqueryConfig?select?*
let $funcOnField :=
let $fieldName := if(string-length($field?header) eq 0) then(sqlQueryGenerator:strToSQLField($field?field)) else(sqlQueryGenerator:strToSQLField($field?header))
let $fieldId := $field?field
return
switch ($field?agregationDo)
case "None"
return "`1`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || " " || $fieldName
case "Sum"
return "SUM(`1`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "Average"
return "AVG(`1`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "Min"
return "MIN(`1`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "Max"
return "MAX(`1`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "Count"
return "COUNT(`1`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "DistinctCount"
return "COUNT(DISTINCT(`1`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ")) " || $fieldName
default
return "`1`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || " " || $fieldName
return string-join($funcOnField, ", ")
let $where := array {
if(empty($subqueryConfig?where?*))
then()
else(
let $wheres :=
for $row in $subqueryConfig?views?*
let $where := $subqueryConfig?where?*[?viewId eq $row?viewId]
let $operator := sqlQueryGenerator:getOperator($where?operator, $where?value)
return '`1`.' || sqlQueryGenerator:getValidSQLElement($where?field, "") || $operator
return " WHERE " || string-join($wheres, " AND ")
)}
let $group := array {
if(empty($subqueryConfig?group?*))
then()
else(
let $groups :=
for $row in $subqueryConfig?views?*
let $group := $subqueryConfig?group?*[?viewId eq $row?viewId]
return '`1`.' || sqlQueryGenerator:getValidSQLElement($group?field, "")
return " GROUP BY " || string-join($groups, ", ")
)}
let $from :=
for $view at $seq in $subqueryConfig?views?*
return
if($view?isSubquery)
then(
(: Open the config doc for that view and return "query" :)
let $configDoc := sqlQueryGenerator:getConfig($view?viewId)?result
let $setId := substring-before($view?viewId, "-")
let $query := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return " FROM (" || $query || ") `1`"
)
else(
" FROM #" || $view?viewId || "# `1`"
)
return "SELECT " || string-join($select, ", ") || $from || $where || $group
case "SQLReportJoins"
return
let $select :=
for $field in $subqueryConfig?select?*
let $funcOnField :=
let $fieldName := if(string-length($field?header) eq 0) then(sqlQueryGenerator:strToSQLField($field?field)) else(sqlQueryGenerator:strToSQLField($field?header))
let $fieldId := $field?field
return
switch ($field?agregationDo)
case "None"
return "`" || $field?viewSeq || "`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || " " || $fieldName
case "Sum"
return "SUM(`" || $field?viewSeq || "`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "Average"
return "AVG(`" || $field?viewSeq || "`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "Min"
return "MIN(`" || $field?viewSeq || "`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "Max"
return "MAX(`" || $field?viewSeq || "`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "Count"
return "COUNT(`" || $field?viewSeq || "`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ") " || $fieldName
case "DistinctCount"
return "COUNT(DISTINCT(`" || $field?viewSeq || "`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || ")) " || $fieldName
default
return "`" || $field?viewSeq || "`." || sqlQueryGenerator:getValidSQLElement($fieldId, $field?type) || " " || $fieldName
return string-join($funcOnField, ", ")
let $from :=
for $join in $subqueryConfig?joins?*
order by $join?seq
let $leftViewSeq := $subqueryConfig?views?*[?viewId eq $join?leftViewId]?seq
let $rightViewSeq := $subqueryConfig?views?*[?viewId eq $join?rightViewId]?seq
return
if($join?seq eq 1)
then(
if($join?isLeftSubquery)
then(
let $configDoc := sqlQueryGenerator:getConfig($join?leftViewId)?result
let $setId := substring-before($join?leftViewId, "-")
let $leftQuery := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return
if($join?isRightSubquery)
then(
let $configDoc := sqlQueryGenerator:getConfig($join?rightViewId)?result
let $setId := substring-before($join?rightViewId, "-")
let $rightQuery := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return
" FROM (" || $leftQuery || ") " || "`" || $leftViewSeq || "`" || " JOIN " || "(" || $rightQuery || ") " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
else(
" FROM (" || $leftQuery || ") " || "`" || $leftViewSeq || "`" || " JOIN #" || $join?rightViewId || "# " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
)
else(
if($join?isRightSubquery)
then(
let $configDoc := sqlQueryGenerator:getConfig($join?rightViewId)?result
let $setId := substring-before($join?rightViewId, "-")
let $rightQuery := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return
" FROM #" || $join?leftViewId || "# " || "`" || $leftViewSeq || "`" || " JOIN " || "(" || $rightQuery || ") " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
else (
" FROM #" || $join?leftViewId || "# " || "`" || $leftViewSeq || "`" || " JOIN #" || $join?rightViewId || "# " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
)
)
else (
if($join?isRightSubquery)
then(
let $configDoc := sqlQueryGenerator:getConfig($join?rightViewId)?result
let $setId := substring-before($join?rightViewId, "-")
let $rightQuery := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return
" JOIN " || "(" || $rightQuery || ") " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
else(
" JOIN #" || $join?rightViewId || "# " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
)
return "SELECT " || string-join($select, ", ") || string-join($from, " ")
case "SDOsqlDataObjectViewUnion"
return
let $select := "*"
let $from :=
for $view in $subqueryConfig?views?*
return
if($view?isSubquery)
then(
(: Open the config doc for that view and return "query" :)
let $configDoc := sqlQueryGenerator:getConfig($view?viewId)?result
let $setId := substring-before($view?viewId, "-")
let $query := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return " (" || $query || ")"
)
else(
"#" || $view?viewId || "#"
)
return "SELECT " || string-join($select, ", ") || " FROM " || string-join($from, " UNION ")
default
return ""
return $query
};
declare function sqlQueryGenerator:generateModelQuery($modelConfig as item()*) {
let $select :=
for $field in $modelConfig?select?*
let $funcOnField :=
let $fieldName := if(string-length($field?header) eq 0) then(sqlQueryGenerator:strToSQLField($field?field)) else(sqlQueryGenerator:strToSQLField($field?header))
let $fieldId :=
if(contains($field?viewId, "ViewConfig"))
then(sqlQueryGenerator:getValidSQLElement($field?field, ""))
else($field?header)
return
switch ($field?agregationDo)
case "None"
return "`" || $field?viewSeq || "`." || $fieldId || " " || $fieldName
case "Sum"
return "SUM(`" || $field?viewSeq || "`." || $fieldId || ") " || $fieldName
case "Average"
return "AVG(`" || $field?viewSeq || "`." || $fieldId || ") " || $fieldName
case "Min"
return "MIN(`" || $field?viewSeq || "`." || $fieldId || ") " || $fieldName
case "Max"
return "MAX(`" || $field?viewSeq || "`." || $fieldId || ") " || $fieldName
case "Count"
return "COUNT(`" || $field?viewSeq || "`." || $fieldId || ") " || $fieldName
case "DistinctCount"
return "COUNT(DISTINCT(`" || $field?viewSeq || "`." || $fieldId || ")) " || $fieldName
default
return "`" || $field?viewSeq || "`." || $fieldId || " " || $fieldName
return string-join($funcOnField, ", ")
let $from :=
if(empty($modelConfig?joins?*))
then(
" FROM #" || $modelConfig?views?*?viewId || "# `1`"
)
else(
for $join in $modelConfig?joins?*
order by $join?seq
let $leftViewSeq := $modelConfig?views?*[?viewId eq $join?leftViewId]?seq
let $rightViewSeq := $modelConfig?views?*[?viewId eq $join?rightViewId]?seq
return
if($join?seq eq 1)
then(
if($join?isLeftSubquery)
then(
let $configDoc := sqlQueryGenerator:getConfig($join?leftViewId)?result
let $setId := substring-before($join?leftViewId, "-")
let $leftQuery := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return
if($join?isRightSubquery)
then(
let $configDoc := sqlQueryGenerator:getConfig($join?rightViewId)?result
let $setId := substring-before($join?rightViewId, "-")
let $rightQuery := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return
" FROM (" || $leftQuery || ") " || "`" || $leftViewSeq || "`" || sqlQueryGenerator:getJoinType($join?type) || "(" || $rightQuery || ") " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
else(
" FROM (" || $leftQuery || ") " || "`" || $leftViewSeq || "`" || sqlQueryGenerator:getJoinType($join?type) || " #" || $join?rightViewId || "# " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
)
else(
if($join?isRightSubquery)
then(
let $configDoc := sqlQueryGenerator:getConfig($join?rightViewId)?result
let $setId := substring-before($join?rightViewId, "-")
let $rightQuery := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return
" FROM #" || $join?leftViewId || "# " || "`" || $leftViewSeq || "`" || sqlQueryGenerator:getJoinType($join?type) || "(" || $rightQuery || ") " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
else(
" FROM #" || $join?leftViewId || "# " || "`" || $leftViewSeq || "`" || sqlQueryGenerator:getJoinType($join?type) || " #" || $join?rightViewId || "# " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
)
)
else (
if($join?isRightSubquery)
then(
let $configDoc := sqlQueryGenerator:getConfig($join?rightViewId)?result
let $setId := substring-before($join?rightViewId, "-")
let $rightQuery := sqlQueryGenerator:generateSubquery($configDoc, $setId)
return
sqlQueryGenerator:getJoinType($join?type) || "(" || $rightQuery || ") " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
else(
sqlQueryGenerator:getJoinType($join?type) || " #" || $join?rightViewId || "# " || "`" || $rightViewSeq || "`" || " ON " || "`" || $rightViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?rightField, "") || " = " || "`" || $leftViewSeq || "`" || "." || sqlQueryGenerator:getValidSQLElement($join?leftField, "")
)
)
)
let $where := array {
if(empty($modelConfig?where?*))
then()
else(
let $wheres :=
for $row in $modelConfig?views?*
let $where := $modelConfig?where?*[?viewId eq $row?viewId]
let $operator := sqlQueryGenerator:getOperator($where?operator, $where?value)
return sqlQueryGenerator:getValidSQLElement($where?field, "") || $operator
return " WHERE " || string-join($wheres, " AND ")
)}
let $group := array {
if(empty($modelConfig?group?*))
then()
else(
let $groups :=
for $row in $modelConfig?views?*
let $group := $modelConfig?group?*[?viewId eq $row?viewId]
return sqlQueryGenerator:getValidSQLElement($group?field, "")
return " GROUP BY " || string-join($groups, ", ")
)}
let $query := "SELECT " || string-join($select, ", ") || string-join($from, " ") || $where || $group
return $query
};
declare function sqlQueryGenerator:main($modelConfig as item()*, $communityId as xs:string, $viewName as xs:string, $connection as xs:long) {
let $query := $modelConfig?query
let $coreViews := $modelConfig?coreViews
let $dbName := sqlQueryGenerator:getDbName($communityId, $connection)
let $checkViewExists := sqlQueryGenerator:checkViewExists($viewName, $dbName, $connection)
let $modelName := $modelConfig?title
let $tables :=
for $viewId in $coreViews?*
let $context := if(string-length($viewId?context) eq 0)then("MS")else($viewId?context)
return
sqlQueryGenerator:getTableName($communityId, $viewId?uuid, $context, $dbName, $connection)
return
if($checkViewExists)
then(
"ALTER VIEW `" || $dbName || "`.`" || $viewName || "` AS (" || sqlQueryGenerator:updateQuery($query, $tables, $dbName, 1, count($tables)+1) || ")"
)
else(
"CREATE VIEW `" || $dbName || "`.`" || $viewName || "` AS (" || sqlQueryGenerator:updateQuery($query, $tables, $dbName, 1, count($tables)+1) || ")"
)
};