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