1 module.exports = { 2 default : "wscub64a", 3 dbconfigs : { 4 wscub32: { 5 dbname : "wscub32", 6 user : "root", 7 password : "8May2K051", 8 host : "localhost", 9 database : "ccdb", 10 dbtype : "mysql" }, 11 wscub64a: { 12 dbname : "wscub64a", 13 user : "root", 14 password : "8May2K051", 15 host : "localhost", 16 database : "CCDB", 17 dbtype : "mysql" }, 18 zpvp : { 19 dbtype : "oracledb", 20 dbname : "zpvp", 21 user : "zpv", 22 enc_password : "yysno\"0", // encrypted like "I'm sorry I can't open the door, Dave" 23 connectString : "//zpvdb.sozvers.at:1521/zpvp" }, 24 zpvtt2 : { 25 dbtype : "oracledb", 26 dbname : "zpvtt2", 27 user : "zpv", 28 password : "xxzpvx", 29 connectString : "//zpvtt2.sozvers.at:1521/zpvtt2" }, 30 zpvmig22 : { 31 dbtype : "oracledb", 32 dbname : "zpvmig22", 33 user : "zpv", 34 password : "xxzpvx", 35 connectString : "//zpvmig22.sozvers.at:1521/zpvmig22" } 36 }, 37 prefs_initializer : { 38 adminsession: { 39 allauthoverride: "YES" 40 }, 41 http_proxy_host : "proxy.sozvers.at", 42 http_proxy_port : 8080, 43 macros: { 44 load_xfile_memory: { 45 aexpression: " console.log('-------- MACRO: load_xfile_memory ---- load dump file to memory ------');" 46 +"\n load_file(that.session,null," 47 +"\n function(err,result) { " 48 +"\n if (err) {" 49 +"\n aux.error_callback(that,err,\"Error DUMP workbook\",result);" 50 +"\n return;" 51 +"\n }" 52 +"\n if ((result.resulttype!='XLSWorkbook') || (!result.xlsworkbook)) {" 53 +"\n aux.error_callback(that,null,'File content of '+that.filepath+' is not an XLS workbook',result);" 54 +"\n return;" 55 +"\n }" 56 +"\n that.memoryfiledata = result; " 57 +"\n that(); " 58 +"\n }.bind(that)," 59 +"\n { rawfile:true," 60 +"\n mfileid:'LOAD_XFILE_MEMORY',mfiletype:'dbdump',mfilename:'dbdump_loadb.xlsx',mfilepath: that.filepath," 61 +"\n disposition:'memorize'});" 62 }, 63 branch: { 64 qexpression: "that.do_branch(that.query[that.curstep+'_p1']);" 65 }, 66 branch_if_true: { 67 qexpression: "if (that.condition) that.do_branch(that.query[that.curstep+'_p1']);" 68 }, 69 exec_sql: { 70 aexpression: " if (!(that.sql)) {" 71 +"\n that(new Error('SQL not given'),null);" 72 +"\n } else {" 73 +"\n that.credbr = new db.Request({info: 'flowsequence macro exec_sql '+that.curstep," 74 +"\n reqid: '__NOXLATE_RQID_MACEXECSQL'," 75 +"\n sql: that.sql," 76 +"\n options: {notranslate: true}});" 77 +"\n db.select(that.credbr,function(err,result) {" 78 +"\n console.log(' --- select callback --- ');" 79 +"\n if (err) {" 80 +"\n console.log(' ---- got error: ',err);" 81 +"\n lerr = new Error('Error executing SQL');" 82 +"\n that.errnum++;" 83 +"\n that.reslist.push({error: lerr.message});" 84 +"\n console.log(' ---- '+that.reslist.length+' results logged');" 85 +"\n } else {" 86 +"\n console.log(' ---- no error.');" 87 +"\n that.reslist.push({status: 'SQL execute OK'});" 88 +"\n that.oknum++; " 89 +"\n } " 90 +"\n that();" 91 +"\n }.bind(that)," 92 +"\n that.pcallback);" 93 +"\n }" 94 }, 95 log_summary: { 96 qexpression: " console.log('-------- summary ----');" 97 +"\n console.log('RESULTS: '+that.oknum+' OK, '+that.errnum+' errors. ');" 98 +"\n console.log(' ---- '+that.reslist.length+' results logged:');" 99 +"\n for (var ei=0; ei<that.reslist.length; ei++) { " 100 +"\n if (that.reslist[ei]) " 101 +"\n console.log(' ------ '+ei+' ------- ');" 102 +"\n console.log(that.reslist[ei]);" 103 +"\n }", 104 } 105 }, 106 actions: { 107 // 108 // action "loadb" populates a new empty CCDB database from the dump file ./uploads/dbdump_20160127124158016.xlsx 109 // - the file is expected to be an Excel Workbook 110 // - sheet TABLES must contain table definitions; these tables are then created 111 // - for each table in TABLES there must be a sheet with the same name as the table containing the contents of the table, this is loaded 112 // - there must be a sheet VIEWS containing database view definitions; these are then created 113 // 114 loadb: { 115 cmd: "dquery", 116 query: { 117 donotquerydescriptor: "YES", 118 dataname: "loadb", 119 querytype: "jsexpr", 120 aexpression: "that.query.prefs = aux.getGlobalNamedObject(\"PREFS\"); " 121 +"functions.flowsequence(that.session,that.query,that,that.pcallback)", 122 flowsequence: "loadb01,loadb10,loadb15,loadb20,loadb30,loadb40,loadb50,loadb51,loadb60,loadb70,loadb71,loadb80", 123 loadb01_steptype: "jsexpr", 124 loadb01_qexpression: " console.log('----------- LOADB starting -----------------');" 125 +"\n that.filepath = \"./uploads/dbdump_20160127124158016.xlsx\";" 126 +"\n that.reslist = new Array();" 127 +"\n that.oknum = 0;" 128 +"\n that.errnum = 0;", 129 loadb10_steptype: "jsexpr", 130 loadb10_aexpression: " console.log('-------- LOADB10 ---- load dump file to memory ------');" 131 +"\n load_file(that.session,null," 132 +"\n function(err,result) { " 133 +"\n if (err) {" 134 +"\n aux.error_callback(that,err,\"Error DUMP workbook\",result);" 135 +"\n return;" 136 +"\n }" 137 +"\n if ((result.resulttype!='XLSWorkbook') || (!result.xlsworkbook)) {" 138 +"\n aux.error_callback(that,null,'File content of '+that.filepath+' is not an XLS workbook',result);" 139 +"\n return;" 140 +"\n }" 141 +"\n that.memoryfiledata = result; " 142 +"\n that(); " 143 +"\n }.bind(that)," 144 +"\n {rawfile:true," 145 +"\n mfileid:'LOADB_VMFILE',mfiletype:'dbdump',mfilename:'dbdump_loadb.xlsx',mfilepath: that.filepath," 146 +"\n disposition:'memorize'});", 147 loadb15_steptype: "jsexpr", 148 loadb15_aexpression: "\n console.log('-------- LOADB15 ---- get table list -------'); " 149 +"\n load_file(that.session,null," 150 +"\n function(err,result) { " 151 +"\n if (err) {" 152 +"\n aux.error_callback(that,err,\"Error reading TABLES\",result);" 153 +"\n return;" 154 +"\n }" 155 +"\n that.tablist = result; " 156 +"\n that(); " 157 +"\n }.bind(that)," 158 +"\n {rawfile:true," 159 +"\n mfileid:'LOADB_VMFILE',mfiletype:'dbdump',mfilename:'dbdump_loadb.xlsx',mfilepath: that.filepath," 160 +"\n sheetname: 'TABLES', memoryfiledata: that.memoryfiledata, " 161 +"\n disposition:'view'});", 162 loadb20_steptype: "jsexpr", 163 loadb20_aexpression: "\n console.log('-------- LOADB20 ---- get list of table columns -------'); " 164 +"\n load_file(that.session,null," 165 +"\n function(err,result) { " 166 +"\n if (err) {" 167 +"\n aux.error_callback(that,err,'Error reading COLUMNS',result);" 168 +"\n return;" 169 +"\n }" 170 +"\n that.coldefs = result;" 171 +"\n that.tabnum = 0;" 172 +"\n that(); " 173 +"\n }.bind(that)," 174 +"\n {rawfile: true," 175 +"\n mfileid: 'LOADB_VMFILE',mfiletype:'dbdump',mfilename:'dbdump_loadb.xlsx',mfilepath: that.filepath," 176 +"\n sheetname: 'COLUMNS', memoryfiledata: that.memoryfiledata, " 177 +"\n disposition:'view'});", 178 loadb30_steptype: "jsexpr", 179 loadb30_aexpression: "\n that.actab = that.tablist.rows[that.tabnum][1]; console.log('---------- LOADB30 ---- create table '+that.actab+' -----'); " 180 +"\n that.actres = {};" 181 +"\n create_table(" 182 +"\n that.actab,that.coldefs.rows," 183 +"\n function(err,result) { " 184 +"\n if (err) { " 185 +"\n that.actres.error = new Error(\"Error creating table \"+that.actab+\": \"+err.message);" 186 +"\n that.errnum++;" 187 +"\n } else { " 188 +"\n that.oknum++; " 189 +"\n } " 190 +"\n that.actres.result = result;" 191 +"\n that.reslist.push(that.actres);" 192 +"\n that.tabnum++; " 193 +"\n if (that.tabnum<that.tablist.rows.length) { that.do_branch('__STAY'); }" 194 +"\n that();" 195 +"\n }.bind(that)" 196 +"\n );", 197 loadb40_steptype: "jsexpr", 198 loadb40_qexpression: "\n console.log('---------- LOADB40 ---- table creation summary report -----'); " 199 +"\n console.log('RESULTS: '+that.oknum+' tables created OK, '+that.errnum+' errors. ');" 200 +"\n that.tabnum = 0;" 201 +"\n that.oknum = 0;" 202 +"\n that.errnum = 0;" 203 +"\n that.loadreslist = new Array();", 204 loadb50_steptype: "jsexpr", 205 loadb50_qexpression: " console.log('---------- LOADB50 ---- load tables from '+that.filepath+' -------')", 206 loadb51_steptype: "jsexpr", 207 loadb51_aexpression: " that.actnam = that.tablist.rows[that.tabnum][1]; " 208 +"\n console.log('---------- LOADB51 ---- loading table '+that.actnam); " 209 +"\n that.actres = {};" 210 +"\n load_file(that.session,null," 211 +"\n function(err,result) { " 212 +"\n if (err) {" 213 +"\n that.actres.error = new Error('Error loading table '+that.actnam+': '+err.message); " 214 +"\n that.errnum++;" 215 +"\n } else {" 216 +"\n that.oknum++;" 217 +"\n }" 218 +"\n that.actres.result = result;" 219 +"\n that.loadreslist.push(that.actres);" 220 +"\n that.tabnum++; " 221 +"\n if (that.tabnum<that.tablist.rows.length) { that.do_branch('__STAY'); }" 222 +"\n that(); " 223 +"\n }.bind(that)," 224 +"\n {rawfile: true,mfileid: 'LOADB_VMFILE',mfiletype:'dbdump',mfilename:'dbdump_loadb.xlsx',mfilepath: that.filepath," 225 +"\n sheetname: that.actnam, tablename: that.actnam, memoryfiledata: that.memoryfiledata, " 226 +"\n disposition:'load'}, " 227 +"\n that.pcallback " 228 +"\n );", 229 loadb60_steptype: "jsexpr", 230 loadb60_qexpression: " console.log('-------- LOADB60 ---- data load summary report ----');" 231 +"\n console.log('RESULTS: '+that.oknum+' tables loaded OK, '+that.errnum+' errors. ');" 232 +"\n for (var ei=0; ei<that.loadreslist.length; ei++) { " 233 +"\n if (that.loadreslist[ei].error) " 234 +"\n console.log(aux.cutString(that.loadreslist[ei].error.message,80));" 235 +"\n }", 236 loadb70_steptype: "jsexpr", 237 loadb70_qexpression: " console.log('-------- LOADB70 ---- create views -----');" 238 +"\n that.vln = 1;" 239 +"\n that.okviewnum = 0;" 240 +"\n that.errviewnum = 0;" 241 +"\n that.creviewreslist = new Array();" 242 +"\n that.viewsheet = that.memoryfiledata.xlsworkbook.Sheets['VIEWS'];" 243 +"\n if (that.viewsheet) {" 244 +"\n that.viewlist = aux.excelTableToArrayOfArrays(that.viewsheet);" 245 +"\n if (that.viewlist && (that.viewlist.length>1) && that.viewlist[0].length>1) {" 246 +"\n that.res = {status: 'ok'};" 247 +"\n } else {" 248 +"\n that.res = {error: new Error('malformed viewlist got from sheet VIEWS in '+this.filepath), errorctx: that.viewlist}" 249 +"\n }" 250 +"\n } else {" 251 +"\n that.res = {error: new Error('Sheet VIEWS does not exist in '+that.filepath), errctx: that.memoryfiledata.xlsworkbook.Sheets}" 252 +"\n }" 253 +"\n that.res", 254 loadb71_steptype: "jsexpr", 255 loadb71_aexpression: " if (!(that.viewlist) || (that.viewlist.length<2) || (that.viewlist[that.vln].length<2)) {" 256 +"\n that(new Error('malformed viewlist'),{info: 'Error creating view #'+that.lvn, ctxobj: that.viewlist});" 257 +"\n } else {" 258 +"\n that.curview = that.viewlist[that.vln][0];" 259 +"\n that.curviewdef = 'CREATE OR REPLACE VIEW '+that.curview+' AS '+that.viewlist[that.vln][2];" 260 +"\n console.log('-------- LOADB71 ---- create view '+that.curview);" 261 +"\n that.credbr = new db.Request({info: 'create view '+that.curview," 262 +"\n reqid: '__NOXLATE_RQID_CREVIEW_'+that.curview," 263 +"\n sql: that.curviewdef," 264 +"\n options: {notranslate: true}});" 265 +"\n console.log(' ---- DBR created, SQL='+that.credbr.sql);" 266 +"\n console.log(' ---- db.select: ',db.select);" 267 +"\n db.select(that.credbr,function(err,result) {" 268 +"\n console.log(' --- select callback --- ');" 269 +"\n if (err) {" 270 +"\n console.log(' ---- got error: ',err);" 271 +"\n lerr = new Error('Error creating view '+that.curview+': '+err.message);" 272 +"\n that.errviewnum++;" 273 +"\n that.creviewreslist.push({error: lerr.message});" 274 +"\n console.log(' ---- '+that.creviewreslist.length+' results logged');" 275 +"\n } else {" 276 +"\n console.log(' ---- no error.');" 277 +"\n that.creviewreslist.push({status: 'View '+that.curview+' successfully created'});" 278 +"\n that.okviewnum++; " 279 +"\n } " 280 +"\n that.vln++;" 281 +"\n if (that.vln<that.viewlist.length) { that.do_branch('__STAY'); }" 282 +"\n console.log(' ---- will callback.');" 283 +"\n that();" 284 +"\n }.bind(that)," 285 +"\n that.pcallback);" 286 +"\n }", 287 loadb80_steptype: "jsexpr", 288 loadb80_qexpression: " console.log('-------- LOADB80 ---- view creation summary ----');" 289 +"\n console.log('RESULTS: '+that.okviewnum+' views created OK, '+that.errviewnum+' errors. ');" 290 +"\n console.log(' ---- '+that.creviewreslist.length+' results logged:');" 291 +"\n for (var ei=0; ei<that.creviewreslist.length; ei++) { " 292 +"\n if (that.creviewreslist[ei]) " 293 +"\n console.log(' ------ '+ei+' ------- ');" 294 +"\n console.log(that.creviewreslist[ei]);" 295 +"\n }", 296 } 297 }, 298 // 299 // action "dview" creates views as defined in file "./uploads/viewdefs.xlsx" in sheet "VIEWDEFMYSQL" 300 // - the sheet "VEWDEFMYSQL" must contain one line per view to be created, each having 2 columns: 301 // column 0: name of the view to be created 302 // column 1: SQL select statement for the view definitions 303 // 304 dview: { 305 cmd: "dquery", 306 query: { 307 donotquerydescriptor: "YES", 308 dataname: "dview", 309 querytype: "jsexpr", 310 aexpression: "functions.flowsequence(that.session,that.query,that,that.pcallback)", 311 flowsequence: "dview10,dview20,dview30,dview40,dview50,dview60,dview70,dview80", 312 dview10_steptype: "jsexpr", 313 dview10_qexpression: " console.log('---------- DVIEW starting ----------');" 314 +"\n that.filepath = './uploads/viewdefs.xlsx';" 315 +"\n that.reslist = new Array();" 316 +"\n that.oknum = 0;" 317 +"\n that.errnum = 0;", 318 dview20_steptype: "macro", 319 dview20_macro: "load_xfile_memory", 320 dview30_steptype: "jsexpr", 321 dview30_qexpression: " console.log('---------- DVIEW30 ---- read view definitions from memory file -----');" 322 +"\n that.wb = that.memoryfiledata.xlsworkbook;" 323 +"\n if (!that.wb) {" 324 +"\n console.error('Viewdef workbook not present');" 325 +"\n that.jsexpres = {error: 'Viewdef workbook not present'};" 326 +"\n } else {" 327 +"\n that.vdefs = aux.excelTableToArrayOfArrays(that.wb.Sheets['VIEWDEFMYSQL']);" 328 +"\n console.log('Sheet VIEWDEFMYSQL has '+that.vdefs.length+' rows');" 329 +"\n }" 330 +"\n that.curdefnum = 0;", 331 dview40_steptype: "jsexpr", 332 dview40_qexpression: " that.curdefnum++;" 333 +"\n that.condition = (that.curdefnum>=that.vdefs.length);" 334 +"\n if (!that.condition) {" 335 +"\n that.sql = 'CREATE OR REPLACE VIEW '+that.vdefs[that.curdefnum][0]+' AS '+that.vdefs[that.curdefnum][1];" 336 +"\n }", 337 dview50_steptype: "macro", 338 dview50_macro: "branch_if_true", 339 dview50_p1: "dview80", 340 dview60_steptype: "macro", 341 dview60_macro: "exec_sql", 342 dview70_steptype: "macro", 343 dview70_macro: "branch", 344 dview70_p1: "dview40", 345 dview80_steptype: "macro", 346 dview80_macro: "log_summary" 347 } 348 } 349 } 350 }, // end of prefs_initializer 351 sqlstrings : [ 352 { 353 dbtype : "oracledb", 354 reqid : "RQID_GETTABCOLNAM", 355 sql : "SELECT COLUMN_NAME,"+ 356 " (CASE "+ 357 " WHEN DATA_TYPE='NUMBER' THEN DATA_PRECISION "+ 358 " ELSE CHAR_LENGTH END) as COL_WIDTH from USER_TAB_COLUMNS where table_name=:TABLE_NAME" 359 }, 360 { 361 dbtype : "mysql", 362 reqid : "RQID_GETTABCOLNAM", 363 sql : "SELECT COLUMN_NAME, "+ 364 " (CASE DATA_TYPE"+ 365 " WHEN 'decimal' THEN NUMERIC_PRECISION "+ 366 " WHEN 'varchar' THEN CHARACTER_MAXIMUM_LENGTH "+ 367 " WHEN 'timestamp' THEN 32 "+ 368 " WHEN 'text' THEN CHARACTER_MAXIMUM_LENGTH "+ 369 " WHEN 'mediumtext' THEN CHARACTER_MAXIMUM_LENGTH "+ 370 " WHEN 'longtext' THEN CHARACTER_MAXIMUM_LENGTH "+ 371 " ELSE 255 END) AS COL_WIDTH "+ 372 " FROM information_schema.COLUMNS "+ 373 " WHERE TABLE_NAME=?" 374 }, 375 { 376 dbtype : "oracledb", 377 reqid : "RQID_GETAUTHINFO", 378 sql : "SELECT RWNAME,RWVALUE,RWDESC from CCRW where RWTYP='ACTAUTHRULE' and RWPARENT='AUTH' and RWNAME IN (:ACTNAME,'GENERAL')" 379 }, 380 { 381 dbtype : "mysql", 382 reqid : "RQID_GETAUTHINFO", 383 sql : "SELECT RWNAME,RWVALUE,RWDESC from CCRW where RWTYP='ACTAUTHRULE' and RWPARENT='AUTH' and RWNAME IN (?,'GENERAL')" 384 }, 385 { 386 dbtype : "oracledb", 387 reqid : "RQID_GETQRYDESC", 388 sql : "SELECT RWNAME,RWVALUE,RWDESC FROM CCRW WHERE RWTYP='DQUERYPARAM' and RWPARENT=:DATANAME", 389 390 }, 391 { 392 dbtype : "mysql", 393 reqid : "RQID_GETQRYDESC", 394 sql : "SELECT RWNAME,RWVALUE,RWDESC FROM CCRW WHERE RWTYP='DQUERYPARAM' and RWPARENT=?", 395 396 }, 397 { 398 dbtype : "oracledb", 399 reqid : "RQID_SCANTABLES", 400 sql : "select TABLE_NAME from ALL_TABLES where TABLE_NAME like 'CC%'" 401 }, 402 { 403 dbtype : "mysql", 404 reqid : "RQID_SCANTABLES", 405 sql : "select TABLE_NAME, CREATE_TIME from information_schema.TABLES where TABLE_SCHEMA='ccdb'" 406 }, 407 { 408 dbtype : "oracledb", 409 reqid : "RQID_SCANCOLS", 410 sql : "select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from ALL_TAB_COLUMNS where TABLE_NAME like 'CC%'" 411 }, 412 { 413 dbtype : "mysql", 414 reqid : "RQID_SCANCOLS", 415 sql : "select TABLE_NAME,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH from information_schema.COLUMNS where TABLE_SCHEMA='ccdb'" 416 }, 417 { 418 dbtype : "oracledb", 419 reqid : "RQID_SCANVIEWS", 420 sql : "select VIEW_NAME from ALL_VIEWS where VIEW_NAME like 'CC%'" 421 }, 422 { 423 dbtype : "mysql", 424 reqid : "RQID_SCANVIEWS", 425 sql : "select TABLE_NAME from information_schema.VIEWS where TABLE_SCHEMA='ccdb'" 426 }, 427 { 428 dbtype : "mysql", 429 reqid : "RQID_CRECCRW", 430 sql : "create table CCRW (RWTYP varchar(32), RWPARENT varchar(32), RWNAME varchar(32), RWVALUE varchar(255) RWDESC varchar(255)) character set = utf8" 431 }, 432 { 433 dbtype : "mysql", 434 reqid : "RQID_CRECCUS", 435 sql : "create table CCUS (USKURZZ varchar(32), USVORNAME varchar(255), USFAMILIENNAME varchar(255), USZPVFSART_CCFS varchar(5), USZPVFS varchar(32), USWIKIPAGENAME varchar(256), USWIKIPAGEURL varchar(256), USSTATUSCCUSSTAT varchar(5)) character set = utf8" 436 }, 437 { 438 dbtype : "mysql", 439 reqid : "RQID_GETMFILDAT", 440 sql : "select MFILEID,MFILETYPE,MFILENAME,MFILEPATH,MFILEATTRIBS from CCMFILES where MFILEID=?" 441 }, 442 { 443 dbtype : "oracledb", 444 reqid : "RQID_GETYPINF", 445 sql : "SELECT RWNAME,RWVALUE,RWDESC from CCRW where RWTYP='TYPEATTRIB' and RWPARENT=:TYPENAME "+ 446 " UNION ALL "+ 447 "SELECT 'typetext' AS RWNAME, RWVALUE,RWDESC from CCRW where RWTYP='TYPE' and RWPARENT='TYPES' and RWNAME=:TYPENAME "+ 448 " UNION ALL "+ 449 "SELECT 'typedesc' AS RWNAME, RWDESC as RWVALUE, 'Type Description' as RWDESC from CCRW "+ 450 " where RWTYP='TYPE' and RWPARENT='TYPES' and RWNAME=:TYPENAME " 451 }, 452 { 453 dbtype : "mysql", 454 reqid : "RQID_GETYPINF", 455 sql : "SELECT RWNAME,RWVALUE,RWDESC from CCRW where RWTYP='TYPEATTRIB' and RWPARENT=? "+ 456 " UNION ALL "+ 457 "SELECT 'typetext' AS RWNAME, RWVALUE,RWDESC from CCRW where RWTYP='TYPE' and RWPARENT='TYPES' and RWNAME=? "+ 458 " UNION ALL "+ 459 "SELECT 'typedesc' AS RWNAME, RWDESC as RWVALUE, 'Type Description' as RWDESC from CCRW "+ 460 " where RWTYP='TYPE' and RWPARENT='TYPES' and RWNAME=? " 461 }, 462 { 463 dbtype : "oracledb", 464 reqid : "RQID_GETUSRDATA", 465 sql : "SELECT us.USKURZZ, us.USVORNAME, us.USFAMILIENNAME, upw.ATTVALUE as HASHED_PASSWORD, ups.ATTVALUE as PASSWORD_SALT, "+ 466 " wm_concat(DISTINCT ur.ATTVALUE) || ',' || wm_concat(DISTINCT gr.ATTVALUE) as RIGHTSLIST "+ 467 " FROM CCVUS us "+ 468 " left outer join CCATT upw on upw.ATTPARKURZ=us.USKURZZ and upw.ATTARCHTYP='USPWD' and upw.ATTNAME='HASHED_PASSWORD' and upw.ATTPARTYPE='CCUS' "+ 469 " left outer join CCATT ups on ups.ATTPARKURZ=us.USKURZZ and ups.ATTARCHTYP='USPWD' and ups.ATTNAME='PASSWORD_SALT' and ups.ATTPARTYPE='CCUS' "+ 470 " left outer join CCUSAS ua on ua.USKURZZ=us.USKURZZ "+ 471 " left outer join CCATT ur on ur.ATTARCHTYP='AUTHORIGHT' and ur.ATTPARTYPE='CCUS' and ur.ATTPARKURZ=us.USKURZZ and ur.ATTNAME='RIGHTSLIST' "+ 472 " left outer join CCATT gr on gr.ATTARCHTYP='AUTHORIGHT' and gr.ATTPARTYPE='CCGRP' and gr.ATTPARKURZ=ua.GRP and gr.ATTNAME='RIGHTSLIST' "+ 473 " WHERE us.USKURZZ=:USERNAME "+ 474 " GROUP by us.USKURZZ,us.USVORNAME,us.USFAMILIENNAME, upw.ATTVALUE, ups.ATTVALUE" 475 }, 476 { 477 dbtype : "mysql", 478 reqid : "RQID_GETUSRDATA", 479 sql : "SELECT us.USKURZZ, us.USVORNAME, us.USFAMILIENNAME, upw.ATTVALUE as HASHED_PASSWORD, ups.ATTVALUE as PASSWORD_SALT, "+ 480 " CONCAT(group_concat(DISTINCT ur.ATTVALUE),',',group_concat(DISTINCT gr.ATTVALUE)) as RIGHTSLIST "+ 481 " FROM CCUS us "+ 482 " left outer join CCATT upw on upw.ATTPARKURZ=us.USKURZZ and upw.ATTARCHTYP='USPWD' and upw.ATTNAME='HASHED_PASSWORD' and upw.ATTPARTYPE='CCUS' "+ 483 " left outer join CCATT ups on ups.ATTPARKURZ=us.USKURZZ and ups.ATTARCHTYP='USPWD' and ups.ATTNAME='PASSWORD_SALT' and ups.ATTPARTYPE='CCUS' "+ 484 " left outer join CCUSAS ua on ua.USKURZZ=us.USKURZZ "+ 485 " left outer join CCATT ur on ur.ATTARCHTYP='AUTHORIGHT' and ur.ATTPARTYPE='CCUS' and ur.ATTPARKURZ=us.USKURZZ and ur.ATTNAME='RIGHTSLIST' "+ 486 " left outer join CCATT gr on gr.ATTARCHTYP='AUTHORIGHT' and gr.ATTPARTYPE='CCGRP' and gr.ATTPARKURZ=ua.GRP and gr.ATTNAME='RIGHTSLIST' "+ 487 " WHERE us.USKURZZ=? "+ 488 " GROUP by us.USKURZZ,us.USVORNAME,us.USFAMILIENNAME, upw.ATTVALUE, ups.ATTVALUE" 489 }, 490 { 491 dbtype : "mysql", 492 reqid : "RQID_INSMFILE", 493 sql : "insert into CCMFILES(mfiletype, mfileid, mfilename, mfilepath, mfileattribs) "+ 494 " values(?,?,?,?,?)" 495 }, 496 { 497 dbtype : "mysql", 498 reqid : "RQID_CDRGDTLODNAM", 499 sql : "select count(*) from CCDCRES where LOADNAME=?" 500 }, 501 { 502 dbtype : "mysql", 503 reqid : "RQID_GETSEQDESC", 504 sql : "SELECT RWNAME,RWVALUE,RWDESC FROM CCRW WHERE RWTYP='DSEQPARAM' and RWPARENT=?" 505 }, 506 { 507 dbtype : "mysql", 508 reqid : "RQID_CREMFILE", 509 sql : "INSERT INTO CCMFILES(MFILEID,MFILETYPE,MFILENAME,MFILEPATH,MFILEATTRIBS) values(?,?,?,?,?)" 510 }, 511 { 512 dbtype : "mysql", 513 reqid : "RQID_DELMFILE", 514 sql : "delete from CCMFILES where mfileid=?", 515 } 516 ] 517 };