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