1 /* 2 * Copyright (c) 2015-2018 LabKey Corporation 3 * 4 * Licensed under the Apache License, Version 2.0: http://www.apache.org/licenses/LICENSE-2.0 5 */ 6 (function(LABKEY) 7 { 8 LABKEY.Query.experimental = LABKEY.Query.experimental || {}; 9 10 var identity = function(x) {return x;}; 11 12 var control_chars = 13 { 14 nul : "\x00", 15 bs : "\x08", // backspace 16 rs : "\x1E", // record separator 17 us : "\x1F" // unit separator 18 }; 19 20 var convertDate = function(s) 21 { 22 if (!s) 23 return null; 24 var number; 25 if (0 < s.indexOf("-")) 26 number = Date.parse(s); 27 else 28 number = parseFloat(s); 29 return new Date(!isNaN(number) && isFinite(number) ? number : s); 30 }; 31 32 var converters = 33 { 34 BOOLEAN: parseInt, 35 TINYINT : parseInt, 36 SMALLINT : parseInt, 37 INTEGER : parseInt, 38 BIGINT : parseInt, // parseDouble? 39 DOUBLE : parseFloat, 40 REAL : parseFloat, 41 NUMERIC : parseFloat, 42 TIMESTAMP : convertDate 43 }; 44 45 46 function parseRows(text,sep,eol) 47 { 48 var rows = text.split(eol); 49 if ("" === trimRight(rows[rows.length-1])) 50 rows.pop(); 51 52 // names 53 var r=0; 54 var meta = rows[r++].split(sep); 55 var names = rows[r++].split(sep); 56 57 // types 58 var colConverters = []; 59 var types = rows[r++].split(sep); 60 for (var i=0 ; i<types.length ; i++) 61 colConverters[i] = converters[types[i]] || identity; 62 63 // skip all metadata rows 64 rows = rows.slice(meta.length); 65 66 // rows 67 for (r=0 ; r<rows.length ; r++) 68 { 69 var row = rows[r].split(sep); 70 for (var c=0 ; c<row.length ; c++) 71 { 72 var s = row[c]; 73 if ("" === s) 74 row[c] = null; 75 else if (control_chars.bs === s && r>0) 76 row[c] = rows[r-1][c]; 77 else 78 row[c] = colConverters[c](s); 79 } 80 rows[r] = row; 81 } 82 return {names:names, types:types, rows:rows}; 83 } 84 85 86 function asObjects(fields, rows) 87 { 88 var row = function(){}; 89 var p = {}; 90 for (var f=0 ; f<fields.length ; f++) 91 p[fields[f]] = null; 92 row.prototype = p; 93 94 var result = []; 95 for (var r=0 ; r<rows.length ; r++) 96 { 97 var arr = rows[r]; 98 var obj = new row(); 99 var l=Math.min(fields.length,arr.length); 100 for (var c=0 ; c<l ; c++) 101 obj[fields[c]] = arr[c]; 102 result.push(obj); 103 } 104 return result; 105 } 106 107 function trimRight(s) { 108 return s.replace(/[\s\uFEFF\xA0]+$/g, ''); 109 } 110 111 LABKEY.Query.experimental.SQL = new (function() 112 { 113 /* containerPath:"", schema:"", sql:"", parameters:{}, timeout:## */ 114 function execute(config) 115 { 116 if (!config.schema) 117 throw "You must specify a schema!"; 118 119 if (!config.sql) 120 throw "You must specify sql statement!"; 121 122 var sep = config.sep || (control_chars.us + '\t'); 123 var eol = config.eol || (control_chars.us + '\n'); 124 125 var requestConfig = 126 { 127 url : LABKEY.ActionURL.buildURL('sql', 'execute', config.containerPath), 128 method : "POST", 129 success: function(response, request) 130 { 131 var result = parseRows(response.responseText, sep, eol); 132 LABKEY.Utils.getOnSuccess(config)(result); 133 }, 134 failure: LABKEY.Utils.getOnFailure(config), 135 jsonData : 136 { 137 schema:config.schema, 138 sql:config.sql, 139 parameters:config.parameters, 140 sep: sep, 141 eol: eol, 142 compact: 1 143 } 144 }; 145 146 if (LABKEY.Utils.isDefined(config.timeout)) 147 requestConfig.timeout = config.timeout; 148 149 return LABKEY.Ajax.request(requestConfig); 150 } 151 152 return { 153 execute : execute, 154 asObjects : asObjects 155 } 156 }); 157 158 })(LABKEY);