1 /** 2 * @fileOverview 3 * @author <a href="https://www.labkey.org">LabKey</a> (<a href="mailto:info@labkey.com">info@labkey.com</a>) 4 * @license Copyright (c) 2014-2018 LabKey Corporation 5 * <p/> 6 * Licensed under the Apache License, Version 2.0 (the "License"); 7 * you may not use this file except in compliance with the License. 8 * You may obtain a copy of the License at 9 * <p/> 10 * http://www.apache.org/licenses/LICENSE-2.0 11 * <p/> 12 * Unless required by applicable law or agreed to in writing, software 13 * distributed under the License is distributed on an "AS IS" BASIS, 14 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 15 * See the License for the specific language governing permissions and 16 * limitations under the License. 17 * <p/> 18 */ 19 LABKEY.Query = new function(impl, $) { 20 21 // Insert a hidden html FORM into to page, put the JSON into it, and submit it - the server's response 22 // will make the browser pop up a dialog 23 function submitForm(url, formData) { 24 if (!formData['X-LABKEY-CSRF']) 25 formData['X-LABKEY-CSRF'] = LABKEY.CSRF; 26 27 var formId = LABKEY.Utils.generateUUID(); 28 29 var html = []; 30 html.push('<f'); html.push('orm method="POST" id="' + formId + '"action="' + url + '">'); // avoid form tag, it causes skipfish false positive 31 for (var name in formData) 32 { 33 if (!formData.hasOwnProperty(name)) 34 continue; 35 36 var value = formData[name]; 37 if (value === undefined) 38 continue; 39 40 html.push( '<input type="hidden"' + 41 ' name="' + LABKEY.Utils.encodeHtml(name) + '"' + 42 ' value="' + LABKEY.Utils.encodeHtml(value) + '" />'); 43 } 44 html.push("</form>"); 45 46 $('body').append(html.join('')); 47 $('form#' + formId).submit(); 48 } 49 50 /** 51 * Documentation specified in core/Query.js -- search for "@name exportSql" 52 */ 53 impl.exportSql = function(config) { 54 55 var url = LABKEY.ActionURL.buildURL("query", "exportSql", config.containerPath); 56 var formData = { 57 sql: config.sql, 58 schemaName: config.schemaName, 59 format: config.format, 60 containerFilter: config.containerFilter 61 }; 62 63 submitForm(url, formData); 64 }; 65 66 /** 67 * @private Not yet official API 68 * Export a set of tables 69 * @param config An object which contains the following: 70 * @param {String} config.schemas An object with the following structure: 71 * <pre> 72 * { 73 * schemas: { 74 * 75 * // export the named queries from schema "A" using the default view or the named view 76 * "A": [{ 77 * queryName: "a" 78 * filters: [ LABKEY.Filters.create("Name", "bob", LABKEY.Filter.Types.NEQ) ], 79 * sort: "Name" 80 * },{ 81 * queryName: "b", 82 * viewName: "b-view" 83 * }] 84 * 85 * } 86 * } 87 * </pre> 88 * @param {String} [config.headerType] Column header type 89 * 90 */ 91 impl.exportTables = function(config) { 92 93 var formData = {}; 94 95 if (config.headerType) 96 formData.headerType = config.headerType; 97 98 // Create a copy of the schema config that we can mutate 99 var schemas = LABKEY.Utils.merge({}, config.schemas); 100 for (var schemaName in schemas) 101 { 102 if (!schemas.hasOwnProperty(schemaName)) 103 continue; 104 105 var queryList = schemas[schemaName]; 106 for (var i = 0; i < queryList.length; i++) 107 { 108 var querySettings = queryList[i]; 109 var o = LABKEY.Utils.merge({}, querySettings); 110 111 delete o.filter; 112 delete o.filterArray; 113 delete o.sort; 114 115 // Turn the filters array into a filters map similar to LABKEY.QueryWebPart 116 o.filters = LABKEY.Filter.appendFilterParams(null, querySettings.filters || querySettings.filterArray); 117 118 if (querySettings.sort) 119 o.filters["query.sort"] = querySettings.sort; 120 121 queryList[i] = o; 122 } 123 } 124 125 formData.schemas = JSON.stringify(schemas); 126 127 var url = LABKEY.ActionURL.buildURL("query", "exportTables.view"); 128 submitForm(url, formData); 129 }; 130 131 function loadingSelect(select) { 132 select.prop('disabled', true); 133 select.empty().append($('<option>', {text: 'Loading...'})); 134 } 135 136 function populateSelect(select, options, valueProperty, textProperty, initialValue, isRequired, includeBlankOption) { 137 select.empty(); 138 139 // if we have duplicate text options, fall back to displaying the value 140 var textOptions = {}, duplicates = {}; 141 $.each(options, function(i, option) { 142 var textValue = option[textProperty]; 143 if (textOptions[textValue] === undefined) 144 textOptions[textValue] = true; 145 else { 146 option[textProperty] = option[valueProperty]; 147 duplicates[textValue] = true; 148 } 149 }); 150 151 var validInitialValue = false; 152 $.each(options, function (i, option) { 153 var value = valueProperty ? option[valueProperty] : option; 154 var text = textProperty ? option[textProperty] : option; 155 if (duplicates[text] !== undefined) 156 text = value; 157 var selected = initialValue != undefined && value === initialValue; 158 if (selected) 159 validInitialValue = true; 160 select.append($('<option>', { value: value, text: text, selected: selected})); 161 }); 162 163 if (includeBlankOption !== false) { 164 var elem = '<option'; 165 if (isRequired) 166 elem += ' hidden'; 167 if (!validInitialValue) 168 elem += ' selected'; 169 elem += '></option>'; 170 select.prepend($(elem)); 171 } 172 173 select.prop('disabled', false); 174 select.on('change', function(){ 175 if (initialValue !== select.val()) 176 LABKEY.setDirty(true); 177 }); 178 } 179 180 function sortObjectArrayByTitle(a, b){ 181 var aTitle = a.title ? a.title : a.caption; 182 var bTitle = b.title ? b.title : b.caption; 183 return aTitle.localeCompare(bTitle); 184 } 185 186 var SCHEMA_QUERIES_CACHE = {}; // cache of queries by schema 187 function loadQueries(schemaSelect, querySelect, selectedSchema, initialValue, isRequired, includeBlankOption) { 188 schemaSelect.prop('disabled', true); 189 loadingSelect(querySelect); 190 191 if (SCHEMA_QUERIES_CACHE[selectedSchema]) { 192 populateSelect(querySelect, SCHEMA_QUERIES_CACHE[selectedSchema], 'name', 'title', initialValue, isRequired, includeBlankOption); 193 schemaSelect.prop('disabled', false); 194 } 195 else { 196 LABKEY.Query.getQueries({ 197 schemaName: selectedSchema, 198 includeColumns: false, 199 success: function(data) { 200 // add the sorted set of queries for this schema to the cache 201 SCHEMA_QUERIES_CACHE[selectedSchema] = data.queries.sort(sortObjectArrayByTitle); 202 203 populateSelect(querySelect, SCHEMA_QUERIES_CACHE[selectedSchema], 'name', 'title', initialValue, isRequired, includeBlankOption); 204 schemaSelect.prop('disabled', false); 205 206 // if there is a selected query, fire the change event 207 if (querySelect.val()) { 208 querySelect.trigger('change'); 209 } 210 } 211 }); 212 } 213 } 214 215 var QUERY_COLUMNS_CACHE = {}; // cache of columns by schema|query|view 216 function loadQueryColumns(select, schemaName, queryName, viewName, filterFn, initValue, isRequired, includeBlankOption, sortFn) { 217 loadingSelect(select); 218 219 if (viewName === undefined || viewName === null) 220 viewName = ""; //'default' view has an empty string as its name 221 222 var queryKey = schemaName + '|' + queryName + "|" + viewName; 223 if (LABKEY.Utils.isArray(QUERY_COLUMNS_CACHE[queryKey])) { 224 populateColumnsWithFilterFn(select, QUERY_COLUMNS_CACHE[queryKey], filterFn, initValue, isRequired, includeBlankOption, sortFn); 225 LABKEY.Utils.signalWebDriverTest("queryColumnsLoaded"); // used for test 226 } 227 else if (QUERY_COLUMNS_CACHE[queryKey] === 'loading') { 228 setTimeout(loadQueryColumns, 500, select, schemaName, queryName, viewName, filterFn, initValue, isRequired, includeBlankOption, sortFn); 229 } 230 else { 231 QUERY_COLUMNS_CACHE[queryKey] = 'loading'; 232 LABKEY.Query.getQueryDetails({ 233 schemaName: schemaName, 234 queryName: queryName, 235 viewName: "*", 236 success: function(data) { 237 var queryView = null; 238 $.each(data.views, function(i, view) { 239 if (view['name'] === viewName) { 240 queryView = view; 241 return false; 242 } 243 }); 244 245 QUERY_COLUMNS_CACHE[queryKey] = []; 246 if (queryView) { 247 QUERY_COLUMNS_CACHE[queryKey] = queryView.fields.sort(sortObjectArrayByTitle); 248 } 249 250 populateColumnsWithFilterFn(select, QUERY_COLUMNS_CACHE[queryKey], filterFn, initValue, isRequired, includeBlankOption, sortFn); 251 LABKEY.Utils.signalWebDriverTest("queryColumnsLoaded"); // used for test 252 } 253 }); 254 } 255 } 256 257 function populateColumnsWithFilterFn(select, origFields, filterFn, initValue, isRequired, includeBlankOption, sortFn) { 258 var fields = []; 259 $.each(origFields, function(i, field) { 260 var includeField = true; 261 262 // allow for a filter function to be called for each field 263 if (filterFn && LABKEY.Utils.isFunction(filterFn)) { 264 includeField = filterFn.call(this, field); 265 } 266 267 // issue 34203: if the field doesn't have a caption, don't include it 268 if (field.caption == null || field.caption ==='' || field.caption === ' ') { 269 includeField = false; 270 } 271 272 if (includeField) { 273 fields.push($.extend({}, field)); 274 } 275 }); 276 277 if (fields.length > 0) { 278 // allow for a sort function to be called to order fields 279 if (sortFn && LABKEY.Utils.isFunction(sortFn)) { 280 fields.sort(sortFn); 281 } 282 populateSelect(select, fields, 'name', 'caption', initValue, isRequired, includeBlankOption); 283 } 284 else { 285 select.empty().append($('<option>', {text: 'No columns available'})); 286 } 287 } 288 289 var QUERY_VIEWS_CACHE = {}; // cache of columns by schema|query 290 function loadQueryViews(schemaSelect, querySelect, queryViewselect, initValue) { 291 var schemaName = schemaSelect.val(), queryName = querySelect.val(); 292 if (!schemaName || !queryName) 293 return; 294 295 schemaSelect.prop('disabled', true); 296 querySelect.prop('disabled', true); 297 298 loadingSelect(queryViewselect); 299 300 var queryKey = schemaName + '|' + queryName; 301 if (LABKEY.Utils.isArray(QUERY_VIEWS_CACHE[queryKey])) { 302 populateViews(queryViewselect, QUERY_VIEWS_CACHE[queryKey], initValue); 303 schemaSelect.prop('disabled', false); 304 querySelect.prop('disabled', false); 305 } 306 else if (QUERY_VIEWS_CACHE[queryKey] === 'loading') { 307 setTimeout(loadQueryViews, 500, schemaSelect, querySelect, queryViewselect, initValue); 308 } 309 else { 310 QUERY_COLUMNS_CACHE[queryKey] = 'loading'; 311 LABKEY.Query.getQueryViews({ 312 schemaName: schemaName, 313 queryName: queryName, 314 success: function(data) { 315 var views = []; 316 $.each(data.views, function(i, view) { 317 if (!view.hidden) { 318 views.push(view) 319 } 320 }); 321 322 QUERY_VIEWS_CACHE[queryKey] = views; 323 324 schemaSelect.prop('disabled', false); 325 querySelect.prop('disabled', false); 326 populateViews(queryViewselect, QUERY_VIEWS_CACHE[queryKey], initValue); 327 } 328 }) 329 } 330 } 331 332 function populateViews(select, views, initValue) { 333 if (views.length > 0) { 334 populateSelect(select, views, 'name', 'label', initValue, true, false); 335 } 336 else { 337 select.empty().append($('<option>', {text: 'No views available'})); 338 } 339 } 340 341 /** 342 * Documentation specified in core/Query.js -- search for "@name schemaSelectInput" 343 */ 344 impl.schemaSelectInput = function(config) { 345 var SCHEMA_SELECT; 346 347 if (!config || !config.renderTo) { 348 console.error('Invalid config object. Missing renderTo property for the <select> element.'); 349 return; 350 } 351 352 SCHEMA_SELECT = $("select[id='" + config.renderTo + "']"); 353 if (SCHEMA_SELECT.length !== 1) { 354 console.error('Invalid config object. Expect to find exactly one <select> element for the renderTo provided (found: ' + SCHEMA_SELECT.length + ').'); 355 return; 356 } 357 358 loadingSelect(SCHEMA_SELECT); 359 LABKEY.Query.getSchemas({ 360 includeHidden: false, 361 success: function(data) { 362 populateSelect(SCHEMA_SELECT, data.schemas.sort(), null, null, config.initValue, config.isRequired, config.includeBlankOption); 363 364 // if there is a selected schema, fire the change event 365 if (SCHEMA_SELECT.val()) { 366 SCHEMA_SELECT.trigger('change', [SCHEMA_SELECT.val()]); 367 } 368 } 369 }); 370 }; 371 372 /** 373 * Documentation specified in core/Query.js -- search for "@name querySelectInput" 374 */ 375 impl.querySelectInput = function(config) { 376 var SCHEMA_SELECT, QUERY_SELECT; 377 378 if (!config || !config.renderTo || !config.schemaInputId) { 379 var msg = 'Invalid config object. '; 380 if (!config.renderTo) { 381 msg += 'Missing renderTo property for the <select> element. '; 382 } 383 if (!config.schemaInputId) { 384 msg += 'Missing schemaInputId property for the parent <select> element. '; 385 } 386 console.error(msg); 387 return; 388 } 389 390 QUERY_SELECT = $("select[id='" + config.renderTo + "']"); 391 if (QUERY_SELECT.length !== 1) { 392 console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + QUERY_SELECT.length + ').'); 393 return; 394 } 395 396 SCHEMA_SELECT = $("select[id='" + config.schemaInputId + "']"); 397 if (SCHEMA_SELECT.length !== 1) { 398 console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + SCHEMA_SELECT.length + ').'); 399 return; 400 } 401 402 SCHEMA_SELECT.on('change', function (event, schemaName) { 403 loadQueries(SCHEMA_SELECT, QUERY_SELECT, schemaName || event.target.value, config.initValue, config.isRequired, config.includeBlankOption); 404 }); 405 }; 406 407 /** 408 * Documentation specified in core/Query.js -- search for "@name columnSelectInput" 409 */ 410 impl.columnSelectInput = function(config) { 411 var COLUMN_SELECT; 412 413 if (!config || !config.renderTo || !config.schemaName || !config.queryName) { 414 var msg = 'Invalid config object. '; 415 if (!config.renderTo) { 416 msg += 'Missing renderTo property for the <select> element. '; 417 } 418 if (!config.schemaName) { 419 msg += 'Missing schemaName property. '; 420 } 421 if (!config.queryName) { 422 msg += 'Missing queryName property. '; 423 } 424 console.error(msg); 425 return; 426 } 427 428 COLUMN_SELECT = $("select[id='" + config.renderTo + "']"); 429 if (COLUMN_SELECT.length !== 1) { 430 console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + COLUMN_SELECT.length + ').'); 431 return; 432 } 433 434 loadQueryColumns(COLUMN_SELECT, config.schemaName, config.queryName, config.viewName, config.filterFn, config.initValue, config.isRequired, config.includeBlankOption, config.sortFn); 435 }; 436 437 impl.queryViewSelectInput = function(config) { 438 var QUERYVIEW_SELECT, SCHEMA_SELECT, QUERY_SELECT; 439 440 if (!config || !config.renderTo || !config.schemaInputId || !config.queryInputId) { 441 var msg = 'Invalid config object. '; 442 if (!config.renderTo) { 443 msg += 'Missing renderTo property for the <select> element. '; 444 } 445 if (!config.schemaInputId) { 446 msg += 'Missing schemaInputId property for the schema <select> element. '; 447 } 448 if (!config.queryInputId) { 449 msg += 'Missing queryInputId property for the query <select> element. '; 450 } 451 console.error(msg); 452 return; 453 } 454 455 if (!config.initValue) { 456 config.initValue = ''; 457 } 458 459 QUERYVIEW_SELECT = $("select[id='" + config.renderTo + "']"); 460 if (QUERYVIEW_SELECT.length !== 1) { 461 console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + QUERYVIEW_SELECT.length + ').'); 462 return; 463 } 464 465 SCHEMA_SELECT = $("select[id='" + config.schemaInputId + "']"); 466 if (SCHEMA_SELECT.length !== 1) { 467 console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + SCHEMA_SELECT.length + ').'); 468 return; 469 } 470 471 QUERY_SELECT = $("select[id='" + config.queryInputId + "']"); 472 if (QUERY_SELECT.length !== 1) { 473 console.error('Invalid config object. Expect to find exactly one <select> element with the name provided (found: ' + QUERY_SELECT.length + ').'); 474 return; 475 } 476 477 QUERY_SELECT.on('change', function () { 478 loadQueryViews(SCHEMA_SELECT, QUERY_SELECT, QUERYVIEW_SELECT, config.initValue, config.isRequired); //never include a blank option 479 }); 480 }; 481 482 /** 483 * Documentation specified in core/Query.js -- search for "@name importData" 484 */ 485 impl.importData = function(config) { 486 if (!window.FormData) { 487 throw new Error('modern browser required'); 488 } 489 490 var form = new FormData(); 491 492 form.append('schemaName', config.schemaName); 493 form.append('queryName', config.queryName); 494 if (config.text) 495 form.append('text', config.text); 496 if (config.path) 497 form.append('path', config.path); 498 if (config.format) 499 form.append('format', config.format); 500 if (config.module) 501 form.append('module', config.module); 502 if (config.moduleResource) 503 form.append('moduleResource', config.moduleResource); 504 if (config.importIdentity) 505 form.append('importIdentity', config.importIdentity); 506 if (config.importLookupByAlternateKey !== undefined) 507 form.append('importLookupByAlternateKey', config.importLookupByAlternateKey); 508 if (config.saveToPipeline !== undefined) 509 form.append('saveToPipeline', config.saveToPipeline); 510 511 if (config.file) { 512 if (config.file instanceof File) 513 form.append('file', config.file); 514 else if (config.file.tagName === 'INPUT' && config.file.files.length > 0) 515 form.append('file', config.file.files[0]); 516 } 517 518 return LABKEY.Ajax.request({ 519 url: config.importUrl || LABKEY.ActionURL.buildURL('query', 'import.api', config.containerPath), 520 method: 'POST', 521 success: LABKEY.Utils.getCallbackWrapper(LABKEY.Utils.getOnSuccess(config), config.scope, false), 522 failure: LABKEY.Utils.getCallbackWrapper(LABKEY.Utils.getOnFailure(config), config.scope, true), 523 form: form, 524 timeout: config.timeout 525 }); 526 }; 527 528 return impl; 529 530 }(LABKEY.Query || new function() { return {}; }, jQuery); 531