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);