OmniSciDB  ab4938a6a3
OmniSciStatement.java
Go to the documentation of this file.
1 /*
2  * Copyright 2017 MapD Technologies, Inc.
3  *
4  * Licensed under the Apache License, Version 2.0 (the "License");
5  * you may not use this file except in compliance with the License.
6  * You may obtain a copy of the License at
7  *
8  * http://www.apache.org/licenses/LICENSE-2.0
9  *
10  * Unless required by applicable law or agreed to in writing, software
11  * distributed under the License is distributed on an "AS IS" BASIS,
12  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13  * See the License for the specific language governing permissions and
14  * limitations under the License.
15  */
16 package com.omnisci.jdbc;
17 
18 import com.omnisci.thrift.server.OmniSci;
19 import com.omnisci.thrift.server.TOmniSciException;
20 import com.omnisci.thrift.server.TQueryResult;
21 
22 import org.apache.thrift.TException;
23 import org.apache.thrift.transport.TTransportException;
24 import org.slf4j.LoggerFactory;
25 
26 import java.sql.Connection;
27 import java.sql.ResultSet;
28 import java.sql.SQLException;
29 import java.sql.SQLWarning;
30 import java.util.regex.Matcher;
31 import java.util.regex.Pattern;
32 
37 public class OmniSciStatement implements java.sql.Statement {
38  final static org.slf4j.Logger logger = LoggerFactory.getLogger(OmniSciStatement.class);
39  public SQLWarning rootWarning = null;
40 
41  private String session;
42  private OmniSci.Client client;
44  private ResultSet currentRS = null;
45  private TQueryResult sqlResult = null;
46  private int maxRows = 100000; // add limit to unlimited queries
47  private boolean escapeProcessing = false;
48 
49  OmniSciStatement(String tsession, OmniSciConnection tconnection) {
50  session = tsession;
51  connection = tconnection;
52  client = connection.client;
53  }
54 
55  OmniSciStatement(String tsession, OmniSci.Client tclient) {
56  session = tsession;
57  client = tclient;
58  }
59 
60  static Pattern top_pattern =
61  Pattern.compile("select top\\s+([0-9]+)\\s+", Pattern.CASE_INSENSITIVE);
62 
63  @Override
64  public ResultSet executeQuery(String sql)
65  throws SQLException { // logger.debug("Entered");
66  if (maxRows >= 0) {
67  // add limit to sql call if it doesn't already have one and is a select
68  String[] tokens = sql.toLowerCase().split(" ", 3);
69  if (tokens[0].equals("select")) {
70  if (sql.toLowerCase().contains("limit")) {
71  // do nothing -
72  } else {
73  // Some applications add TOP <number> to limit the
74  // select statement rather than limit. Remove TOP and keep
75  // the number it used as the limit.
76  Matcher matcher = top_pattern.matcher(sql);
77  // Take "select TOP nnnn <rest ot sql>" and translate to select <reset of sql:
78  // limit nnnn
79  if (matcher.find()) {
80  maxRows = Integer.parseInt(matcher.group(1));
81  sql = top_pattern.matcher(sql).replaceAll("select ");
82  }
83 
84  sql = sql + " LIMIT " + maxRows;
85  logger.debug("Added LIMIT of " + maxRows);
86  }
87  }
88  }
89 
90  logger.debug("Before OmniSciEscapeParser [" + sql + "]");
91  // The order of these to SQL re-writes is important.
92  // EscapeParse needs to come first.
93  String afterEscapeParseSQL = OmniSciEscapeParser.parse(sql);
94  String afterSimpleParse = simplisticDateTransform(afterEscapeParseSQL);
95  logger.debug("After OmniSciEscapeParser [" + afterSimpleParse + "]");
96  try {
97  sqlResult = client.sql_execute(session, afterSimpleParse + ";", true, null, -1, -1);
98  } catch (TOmniSciException ex) {
99  throw new SQLException(
100  "Query failed : " + OmniSciExceptionText.getExceptionDetail(ex));
101  } catch (TException ex) {
102  throw new SQLException(
103  "Query failed : " + OmniSciExceptionText.getExceptionDetail(ex));
104  }
105 
106  currentRS = new OmniSciResultSet(sqlResult, sql);
107  return currentRS;
108  }
109 
110  @Override
111  public void cancel() throws SQLException { // logger.debug("Entered");
112  OmniSciConnection alternate_connection = null;
113  try {
114  alternate_connection = connection.getAlternateConnection();
115  // Note alternate_connection shares a session with original connection
116  alternate_connection.client.interrupt(session, session);
117  } catch (TOmniSciException ttE) {
118  throw new SQLException("Thrift transport connection failed - "
120  ttE);
121  } catch (TException tE) {
122  throw new SQLException(
123  "Thrift failed - " + OmniSciExceptionText.getExceptionDetail(tE), tE);
124  } finally {
125  // Note closeConnection only closes the underlying thrft connection
126  // not the logical db session connection
127  alternate_connection.closeConnection();
128  }
129  }
130 
131  @Override
132  public int executeUpdate(String sql) throws SQLException { // logger.debug("Entered");
133  try {
134  // remove " characters if it is a CREATE statement
135  if (sql.trim().substring(0, 6).compareToIgnoreCase("CREATE") == 0) {
136  sql = sql.replace('"', ' ');
137  }
138  sqlResult = client.sql_execute(session, sql + ";", true, null, -1, -1);
139  } catch (TOmniSciException ex) {
140  throw new SQLException("Query failed : sql was '" + sql + "' "
142  ex);
143  } catch (TException ex) {
144  throw new SQLException(
145  "Query failed : " + OmniSciExceptionText.getExceptionDetail(ex), ex);
146  }
147 
148  return sqlResult.row_set.columns.size();
149  }
150 
151  @Override
152  public void close() throws SQLException { // logger.debug("Entered");
153 
154  // clean up after -- nothing to do
155  }
156 
157  @Override
158  public int getMaxFieldSize() throws SQLException { // logger.debug("Entered");
159  throw new UnsupportedOperationException("Not supported yet,"
160  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
161  + " class:" + new Throwable().getStackTrace()[0].getClassName()
162  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
163  }
164 
165  @Override
166  public void setMaxFieldSize(int max) throws SQLException { // logger.debug("Entered");
167  throw new UnsupportedOperationException("Not supported yet,"
168  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
169  + " class:" + new Throwable().getStackTrace()[0].getClassName()
170  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
171  }
172 
173  @Override
174  public int getMaxRows() throws SQLException { // logger.debug("Entered");
175  return maxRows;
176  }
177 
178  @Override
179  public void setMaxRows(int max) throws SQLException { // logger.debug("Entered");
180  maxRows = max;
181  }
182 
183  @Override
184  public void setEscapeProcessing(boolean enable)
185  throws SQLException { // logger.debug("Entered");
186  escapeProcessing = enable;
187  }
188 
189  @Override
190  public int getQueryTimeout() throws SQLException { // logger.debug("Entered");
191  return 0;
192  }
193 
194  // used by benchmarking to get internal execution times
196  throws SQLException { // logger.debug("Entered");
197  return (int) sqlResult.execution_time_ms;
198  }
199 
200  @Override
201  public void setQueryTimeout(int seconds)
202  throws SQLException { // logger.debug("Entered");
203  SQLWarning warning = new SQLWarning(
204  "Query timeouts are not supported. Substituting a value of zero.");
205  if (rootWarning == null) {
206  rootWarning = warning;
207  } else {
208  rootWarning.setNextWarning(warning);
209  }
210  }
211 
212  @Override
213  public SQLWarning getWarnings() throws SQLException { // logger.debug("Entered");
214  return (rootWarning);
215  }
216 
217  @Override
218  public void clearWarnings() throws SQLException { // logger.debug("Entered");
219  rootWarning = null;
220  }
221 
222  @Override
223  public void setCursorName(String name) throws SQLException { // logger.debug("Entered");
224  throw new UnsupportedOperationException("Not supported yet,"
225  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
226  + " class:" + new Throwable().getStackTrace()[0].getClassName()
227  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
228  }
229 
230  @Override
231  public boolean execute(String sql) throws SQLException { // logger.debug("Entered");
232 
233  ResultSet rs = executeQuery(sql);
234  if (rs != null) {
235  return true;
236  } else {
237  return false;
238  }
239  }
240 
241  @Override
242  public ResultSet getResultSet() throws SQLException { // logger.debug("Entered");
243  return currentRS;
244  }
245 
246  @Override
247  public int getUpdateCount() throws SQLException { // logger.debug("Entered");
248  // TODO MAT fix update count
249  return 0;
250  }
251 
252  @Override
253  public boolean getMoreResults() throws SQLException { // logger.debug("Entered");
254  // TODO MAT this needs to be fixed for complex queries
255  return false;
256  }
257 
258  @Override
259  public void setFetchDirection(int direction)
260  throws SQLException { // logger.debug("Entered");
261  throw new UnsupportedOperationException("Not supported yet,"
262  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
263  + " class:" + new Throwable().getStackTrace()[0].getClassName()
264  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
265  }
266 
267  @Override
268  public int getFetchDirection() throws SQLException { // logger.debug("Entered");
269  throw new UnsupportedOperationException("Not supported yet,"
270  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
271  + " class:" + new Throwable().getStackTrace()[0].getClassName()
272  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
273  }
274 
275  @Override
276  public void setFetchSize(int rows) throws SQLException { // logger.debug("Entered");
277  SQLWarning warning = new SQLWarning(
278  "Query FetchSize are not supported. Substituting a value of zero.");
279  if (rootWarning == null) {
280  rootWarning = warning;
281  } else {
282  rootWarning.setNextWarning(warning);
283  }
284  }
285 
286  @Override
287  public int getFetchSize() throws SQLException { // logger.debug("Entered");
288  throw new UnsupportedOperationException("Not supported yet,"
289  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
290  + " class:" + new Throwable().getStackTrace()[0].getClassName()
291  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
292  }
293 
294  @Override
295  public int getResultSetConcurrency() throws SQLException { // logger.debug("Entered");
296  throw new UnsupportedOperationException("Not supported yet,"
297  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
298  + " class:" + new Throwable().getStackTrace()[0].getClassName()
299  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
300  }
301 
302  @Override
303  public int getResultSetType() throws SQLException { // logger.debug("Entered");
304  throw new UnsupportedOperationException("Not supported yet,"
305  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
306  + " class:" + new Throwable().getStackTrace()[0].getClassName()
307  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
308  }
309 
310  @Override
311  public void addBatch(String sql) throws SQLException { // logger.debug("Entered");
312  throw new UnsupportedOperationException("Not supported yet,"
313  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
314  + " class:" + new Throwable().getStackTrace()[0].getClassName()
315  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
316  }
317 
318  @Override
319  public void clearBatch() throws SQLException { // logger.debug("Entered");
320  throw new UnsupportedOperationException("Not supported yet,"
321  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
322  + " class:" + new Throwable().getStackTrace()[0].getClassName()
323  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
324  }
325 
326  @Override
327  public int[] executeBatch() throws SQLException { // logger.debug("Entered");
328  throw new UnsupportedOperationException("Not supported yet,"
329  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
330  + " class:" + new Throwable().getStackTrace()[0].getClassName()
331  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
332  }
333 
334  @Override
335  public Connection getConnection() throws SQLException { // logger.debug("Entered");
336  throw new UnsupportedOperationException("Not supported yet,"
337  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
338  + " class:" + new Throwable().getStackTrace()[0].getClassName()
339  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
340  }
341 
342  @Override
343  public boolean getMoreResults(int current)
344  throws SQLException { // logger.debug("Entered");
345  throw new UnsupportedOperationException("Not supported yet,"
346  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
347  + " class:" + new Throwable().getStackTrace()[0].getClassName()
348  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
349  }
350 
351  @Override
352  public ResultSet getGeneratedKeys() throws SQLException { // logger.debug("Entered");
353  throw new UnsupportedOperationException("Not supported yet,"
354  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
355  + " class:" + new Throwable().getStackTrace()[0].getClassName()
356  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
357  }
358 
359  @Override
360  public int executeUpdate(String sql, int autoGeneratedKeys)
361  throws SQLException { // logger.debug("Entered");
362  throw new UnsupportedOperationException("Not supported yet,"
363  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
364  + " class:" + new Throwable().getStackTrace()[0].getClassName()
365  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
366  }
367 
368  @Override
369  public int executeUpdate(String sql, int[] columnIndexes)
370  throws SQLException { // logger.debug("Entered");
371  throw new UnsupportedOperationException("Not supported yet,"
372  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
373  + " class:" + new Throwable().getStackTrace()[0].getClassName()
374  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
375  }
376 
377  @Override
378  public int executeUpdate(String sql, String[] columnNames)
379  throws SQLException { // logger.debug("Entered");
380  throw new UnsupportedOperationException("Not supported yet,"
381  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
382  + " class:" + new Throwable().getStackTrace()[0].getClassName()
383  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
384  }
385 
386  @Override
387  public boolean execute(String sql, int autoGeneratedKeys)
388  throws SQLException { // logger.debug("Entered");
389  throw new UnsupportedOperationException("Not supported yet,"
390  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
391  + " class:" + new Throwable().getStackTrace()[0].getClassName()
392  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
393  }
394 
395  @Override
396  public boolean execute(String sql, int[] columnIndexes)
397  throws SQLException { // logger.debug("Entered");
398  throw new UnsupportedOperationException("Not supported yet,"
399  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
400  + " class:" + new Throwable().getStackTrace()[0].getClassName()
401  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
402  }
403 
404  @Override
405  public boolean execute(String sql, String[] columnNames)
406  throws SQLException { // logger.debug("Entered");
407  throw new UnsupportedOperationException("Not supported yet,"
408  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
409  + " class:" + new Throwable().getStackTrace()[0].getClassName()
410  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
411  }
412 
413  @Override
414  public int getResultSetHoldability() throws SQLException { // logger.debug("Entered");
415  throw new UnsupportedOperationException("Not supported yet,"
416  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
417  + " class:" + new Throwable().getStackTrace()[0].getClassName()
418  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
419  }
420 
421  @Override
422  public boolean isClosed() throws SQLException { // logger.debug("Entered");
423  throw new UnsupportedOperationException("Not supported yet,"
424  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
425  + " class:" + new Throwable().getStackTrace()[0].getClassName()
426  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
427  }
428 
429  @Override
430  public void setPoolable(boolean poolable)
431  throws SQLException { // logger.debug("Entered");
432  throw new UnsupportedOperationException("Not supported yet,"
433  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
434  + " class:" + new Throwable().getStackTrace()[0].getClassName()
435  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
436  }
437 
438  @Override
439  public boolean isPoolable() throws SQLException { // logger.debug("Entered");
440  throw new UnsupportedOperationException("Not supported yet,"
441  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
442  + " class:" + new Throwable().getStackTrace()[0].getClassName()
443  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
444  }
445 
446  @Override
447  public void closeOnCompletion() throws SQLException { // logger.debug("Entered");
448  throw new UnsupportedOperationException("Not supported yet,"
449  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
450  + " class:" + new Throwable().getStackTrace()[0].getClassName()
451  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
452  }
453 
454  @Override
455  public boolean isCloseOnCompletion() throws SQLException { // logger.debug("Entered");
456  throw new UnsupportedOperationException("Not supported yet,"
457  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
458  + " class:" + new Throwable().getStackTrace()[0].getClassName()
459  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
460  }
461 
462  @Override
463  public <T> T unwrap(Class<T> iface) throws SQLException { // logger.debug("Entered");
464  throw new UnsupportedOperationException("Not supported yet,"
465  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
466  + " class:" + new Throwable().getStackTrace()[0].getClassName()
467  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
468  }
469 
470  @Override
471  public boolean isWrapperFor(Class<?> iface)
472  throws SQLException { // logger.debug("Entered");
473  throw new UnsupportedOperationException("Not supported yet,"
474  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
475  + " class:" + new Throwable().getStackTrace()[0].getClassName()
476  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
477  }
478 
479  private static final Pattern QUARTER = Pattern.compile(
480  "\\sQUARTER\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
481  private static final Pattern DAYOFYEAR = Pattern.compile(
482  "\\sDAYOFYEAR\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
483  private static final Pattern DAYOFWEEK = Pattern.compile(
484  "\\sDAYOFWEEK\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
485  private static final Pattern WEEK = Pattern.compile(
486  "\\sWEEK\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
487 
488  /*
489  * CURRENTDATE should match CURRENT_DATE
490  * and CURRENT_DATE() where the two strings are 'joined' to either white space,
491  * punctuation or some kind of brackets. if they are joined to
492  * any alpha numeric For example 'CURRENT_TIME)' is okay while a string
493  * like CURRENT_DATE_NOW isn't
494  *
495  * Note we've include the non standard version with parenthesis to align with third
496  * party software.
497  *
498  * Breaking down the components of the pattern
499  * (?<![\\w.]) The pattern can not be preceded by any word character or a '.'
500  * (?:\\(\\))? pattern can end in zero or one '()' - note non capture group
501  * (?![\\w.]) the pattern can not be followed by a word character or a '.'
502  * Note - word characters include '_'
503  */
504  ;
505  private static final Pattern CURRENTDATE =
506  Pattern.compile("(?<![\\w.])CURRENT_DATE(?:\\(\\))?(?![\\w.])",
507  Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
508  public static String simplisticDateTransform(String sql) {
509  // need to iterate as each reduction of string opens up a anew match
510  String start;
511  do {
512  // Example transform - select quarter(val) from table;
513  // will become select extract(quarter from val) from table;
514  // will also replace all CURRENT_TIME and CURRENT_DATE with a call to now().
515  start = sql;
516  sql = QUARTER.matcher(sql).replaceAll(" EXTRACT(QUARTER FROM $1");
517  } while (!sql.equals(start));
518 
519  do {
520  start = sql;
521  sql = DAYOFYEAR.matcher(sql).replaceAll(" EXTRACT(DOY FROM $1");
522  } while (!sql.equals(start));
523 
524  do {
525  start = sql;
526  sql = DAYOFWEEK.matcher(sql).replaceAll(" EXTRACT(ISODOW FROM $1");
527  } while (!sql.equals(start));
528 
529  do {
530  start = sql;
531  sql = WEEK.matcher(sql).replaceAll(" EXTRACT(WEEK FROM $1");
532  } while (!sql.equals(start));
533 
534  do {
535  start = sql;
536  sql = CURRENTDATE.matcher(sql).replaceAll(" cast(now() as date) ");
537  } while (!sql.equals(start));
538 
539  return sql;
540  }
541 }
static String simplisticDateTransform(String sql)
static String getExceptionDetail(Exception ex)
boolean isWrapperFor(Class<?> iface)
public< T > T unwrap(Class< T > iface)
OmniSciStatement(String tsession, OmniSciConnection tconnection)
OmniSciStatement(String tsession, OmniSci.Client tclient)
int executeUpdate(String sql, String[] columnNames)
boolean execute(String sql, int[] columnIndexes)
int executeUpdate(String sql, int[] columnIndexes)
boolean execute(String sql, int autoGeneratedKeys)
void setEscapeProcessing(boolean enable)
boolean execute(String sql, String[] columnNames)
int executeUpdate(String sql, int autoGeneratedKeys)