1 /* 2 * Copyright (c) 2015-2017 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 console.log(new Date()); 49 var rows = text.split(eol); 50 if ("" === trimRight(rows[rows.length-1])) 51 rows.pop(); 52 53 // names 54 var r=0; 55 var meta = rows[r++].split(sep); 56 var names = rows[r++].split(sep); 57 58 // types 59 var colConverters = []; 60 var types = rows[r++].split(sep); 61 for (var i=0 ; i<types.length ; i++) 62 colConverters[i] = converters[types[i]] || identity; 63 64 // skip all metadata rows 65 rows = rows.slice(meta.length); 66 67 // rows 68 for (r=0 ; r<rows.length ; r++) 69 { 70 var row = rows[r].split(sep); 71 for (var c=0 ; c<row.length ; c++) 72 { 73 var s = row[c]; 74 if ("" === s) 75 row[c] = null; 76 else if (control_chars.bs === s && r>0) 77 row[c] = rows[r-1][c]; 78 else 79 row[c] = colConverters[c](s); 80 } 81 rows[r] = row; 82 } 83 return {names:names, types:types, rows:rows}; 84 } 85 86 87 function asObjects(fields, rows) 88 { 89 var row = function(){}; 90 var p = {}; 91 for (var f=0 ; f<fields.length ; f++) 92 p[fields[f]] = null; 93 row.prototype = p; 94 95 var result = []; 96 for (var r=0 ; r<rows.length ; r++) 97 { 98 var arr = rows[r]; 99 var obj = new row(); 100 var l=Math.min(fields.length,arr.length); 101 for (var c=0 ; c<l ; c++) 102 obj[fields[c]] = arr[c]; 103 result.push(obj); 104 } 105 return result; 106 } 107 108 function trimRight(s) { 109 return s.replace(/[\s\uFEFF\xA0]+$/g, ''); 110 } 111 112 LABKEY.Query.experimental.SQL = new (function() 113 { 114 /* containerPath:"", schema:"", sql:"", parameters:{}, timeout:## */ 115 function execute(config) 116 { 117 if (!config.schema) 118 throw "You must specify a schema!"; 119 120 if (!config.sql) 121 throw "You must specify sql statement!"; 122 123 var sep = config.sep || (control_chars.us + '\t'); 124 var eol = config.eol || (control_chars.us + '\n'); 125 126 var requestConfig = 127 { 128 url : LABKEY.ActionURL.buildURL('sql', 'execute', config.containerPath), 129 method : "POST", 130 success: function(response, request) 131 { 132 var result = parseRows(response.responseText, sep, eol); 133 LABKEY.Utils.getOnSuccess(config)(result); 134 }, 135 failure: LABKEY.Utils.getOnFailure(config), 136 jsonData : 137 { 138 schema:config.schema, 139 sql:config.sql, 140 parameters:config.parameters, 141 sep: sep, 142 eol: eol, 143 compact: 1 144 } 145 }; 146 147 if (LABKEY.Utils.isDefined(config.timeout)) 148 requestConfig.timeout = config.timeout; 149 150 return LABKEY.Ajax.request(requestConfig); 151 } 152 153 return { 154 execute : execute, 155 asObjects : asObjects 156 } 157 }); 158 159 })(LABKEY);