OmniSciDB  95562058bd
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Pages
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  private boolean isClosed = false;
49 
50  OmniSciStatement(String tsession, OmniSciConnection tconnection) {
51  session = tsession;
52  connection = tconnection;
53  client = connection.client;
54  }
55 
56  OmniSciStatement(String tsession, OmniSci.Client tclient) {
57  session = tsession;
58  client = tclient;
59  }
60 
61  static Pattern top_pattern =
62  Pattern.compile("select top\\s+([0-9]+)\\s+", Pattern.CASE_INSENSITIVE);
63 
64  @Override
65  public ResultSet executeQuery(String sql)
66  throws SQLException { // logger.debug("Entered");
67  checkClosed();
68  if (maxRows >= 0) {
69  // add limit to sql call if it doesn't already have one and is a select
70  String[] tokens = sql.toLowerCase().split(" ", 3);
71  if (tokens[0].equals("select")) {
72  if (sql.toLowerCase().contains("limit")) {
73  // do nothing -
74  } else {
75  // Some applications add TOP <number> to limit the
76  // select statement rather than limit. Remove TOP and keep
77  // the number it used as the limit.
78  Matcher matcher = top_pattern.matcher(sql);
79  // Take "select TOP nnnn <rest ot sql>" and translate to select <reset of sql:
80  // limit nnnn
81  if (matcher.find()) {
82  maxRows = Integer.parseInt(matcher.group(1));
83  sql = top_pattern.matcher(sql).replaceAll("select ");
84  }
85 
86  sql = sql + " LIMIT " + maxRows;
87  logger.debug("Added LIMIT of " + maxRows);
88  }
89  }
90  }
91 
92  logger.debug("Before OmniSciEscapeParser [" + sql + "]");
93  // The order of these to SQL re-writes is important.
94  // EscapeParse needs to come first.
95  String afterEscapeParseSQL = OmniSciEscapeParser.parse(sql);
96  String afterSimpleParse = simplisticDateTransform(afterEscapeParseSQL);
97  logger.debug("After OmniSciEscapeParser [" + afterSimpleParse + "]");
98  try {
99  sqlResult = client.sql_execute(session, afterSimpleParse + ";", true, null, -1, -1);
100  } catch (TOmniSciException ex) {
101  throw new SQLException(
102  "Query failed : " + OmniSciExceptionText.getExceptionDetail(ex));
103  } catch (TException ex) {
104  throw new SQLException(
105  "Query failed : " + OmniSciExceptionText.getExceptionDetail(ex));
106  }
107 
109  return currentRS;
110  }
111 
112  @Override
113  public void cancel() throws SQLException { // logger.debug("Entered");
114  checkClosed();
115  OmniSciConnection alternate_connection = null;
116  try {
117  alternate_connection = connection.getAlternateConnection();
118  // Note alternate_connection shares a session with original connection
119  alternate_connection.client.interrupt(session, session);
120  } catch (TOmniSciException ttE) {
121  throw new SQLException("Thrift transport connection failed - "
123  ttE);
124  } catch (TException tE) {
125  throw new SQLException(
126  "Thrift failed - " + OmniSciExceptionText.getExceptionDetail(tE), tE);
127  } finally {
128  // Note closeConnection only closes the underlying thrft connection
129  // not the logical db session connection
130  alternate_connection.closeConnection();
131  }
132  }
133 
134  @Override
135  public int executeUpdate(String sql) throws SQLException { // logger.debug("Entered");
136  checkClosed();
137  try {
138  // remove " characters if it is a CREATE statement
139  if (sql.trim().substring(0, 6).compareToIgnoreCase("CREATE") == 0) {
140  sql = sql.replace('"', ' ');
141  }
142  sqlResult = client.sql_execute(session, sql + ";", true, null, -1, -1);
143  } catch (TOmniSciException ex) {
144  throw new SQLException("Query failed : sql was '" + sql + "' "
146  ex);
147  } catch (TException ex) {
148  throw new SQLException(
149  "Query failed : " + OmniSciExceptionText.getExceptionDetail(ex), ex);
150  }
151 
152  // TODO: OmniSciDB supports updates, inserts and deletes, but
153  // there is no way to get number of affected rows at the moment
154  return -1;
155  }
156 
157  @Override
158  public void close() throws SQLException { // logger.debug("Entered");
159  if (currentRS != null) {
160  currentRS.close();
161  }
162  isClosed = true;
163  }
164 
165  @Override
166  public int getMaxFieldSize() 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 void setMaxFieldSize(int max) throws SQLException { // logger.debug("Entered");
175  throw new UnsupportedOperationException("Not supported yet,"
176  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
177  + " class:" + new Throwable().getStackTrace()[0].getClassName()
178  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
179  }
180 
181  @Override
182  public int getMaxRows() throws SQLException { // logger.debug("Entered");
183  return maxRows;
184  }
185 
186  @Override
187  public void setMaxRows(int max) throws SQLException { // logger.debug("Entered");
188  maxRows = max;
189  }
190 
191  @Override
192  public void setEscapeProcessing(boolean enable)
193  throws SQLException { // logger.debug("Entered");
194  escapeProcessing = enable;
195  }
196 
197  @Override
198  public int getQueryTimeout() throws SQLException { // logger.debug("Entered");
199  return 0;
200  }
201 
202  // used by benchmarking to get internal execution times
204  throws SQLException { // logger.debug("Entered");
205  return (int) sqlResult.execution_time_ms;
206  }
207 
208  @Override
209  public void setQueryTimeout(int seconds)
210  throws SQLException { // logger.debug("Entered");
211  SQLWarning warning = new SQLWarning(
212  "Query timeouts are not supported. Substituting a value of zero.");
213  if (rootWarning == null) {
214  rootWarning = warning;
215  } else {
216  rootWarning.setNextWarning(warning);
217  }
218  }
219 
220  @Override
221  public SQLWarning getWarnings() throws SQLException { // logger.debug("Entered");
222  return (rootWarning);
223  }
224 
225  @Override
226  public void clearWarnings() throws SQLException { // logger.debug("Entered");
227  rootWarning = null;
228  }
229 
230  @Override
231  public void setCursorName(String name) throws SQLException { // logger.debug("Entered");
232  throw new UnsupportedOperationException("Not supported yet,"
233  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
234  + " class:" + new Throwable().getStackTrace()[0].getClassName()
235  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
236  }
237 
238  @Override
239  public boolean execute(String sql) throws SQLException { // logger.debug("Entered");
240  ResultSet rs = executeQuery(sql);
241  if (rs != null) {
242  return true;
243  } else {
244  return false;
245  }
246  }
247 
248  @Override
249  public ResultSet getResultSet() throws SQLException { // logger.debug("Entered");
250  checkClosed();
251  return currentRS;
252  }
253 
254  @Override
255  public int getUpdateCount() throws SQLException { // logger.debug("Entered");
256  checkClosed();
257  // TODO: OmniSciDB supports updates, inserts and deletes, but
258  // there is no way to get number of affected rows at the moment
259  return -1;
260  }
261 
262  @Override
263  public boolean getMoreResults() throws SQLException { // logger.debug("Entered");
264  checkClosed();
265  // TODO MAT this needs to be fixed for complex queries
266  return false;
267  }
268 
269  @Override
270  public void setFetchDirection(int direction)
271  throws SQLException { // logger.debug("Entered");
272  throw new UnsupportedOperationException("Not supported yet,"
273  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
274  + " class:" + new Throwable().getStackTrace()[0].getClassName()
275  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
276  }
277 
278  @Override
279  public int getFetchDirection() throws SQLException { // logger.debug("Entered");
280  return ResultSet.FETCH_FORWARD;
281  }
282 
283  @Override
284  public void setFetchSize(int rows) throws SQLException { // logger.debug("Entered");
285  SQLWarning warning = new SQLWarning(
286  "Query FetchSize are not supported. Substituting a value of zero.");
287  if (rootWarning == null) {
288  rootWarning = warning;
289  } else {
290  rootWarning.setNextWarning(warning);
291  }
292  }
293 
294  @Override
295  public int getFetchSize() 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 getResultSetConcurrency() 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 int getResultSetType() 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 addBatch(String sql) 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 void clearBatch() 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 int[] executeBatch() 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 Connection getConnection() throws SQLException { // logger.debug("Entered");
344  throw new UnsupportedOperationException("Not supported yet,"
345  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
346  + " class:" + new Throwable().getStackTrace()[0].getClassName()
347  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
348  }
349 
350  @Override
351  public boolean getMoreResults(int current)
352  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 ResultSet getGeneratedKeys() throws SQLException { // logger.debug("Entered");
361  throw new UnsupportedOperationException("Not supported yet,"
362  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
363  + " class:" + new Throwable().getStackTrace()[0].getClassName()
364  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
365  }
366 
367  @Override
368  public int executeUpdate(String sql, int autoGeneratedKeys)
369  throws SQLException { // logger.debug("Entered");
370  throw new UnsupportedOperationException("Not supported yet,"
371  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
372  + " class:" + new Throwable().getStackTrace()[0].getClassName()
373  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
374  }
375 
376  @Override
377  public int executeUpdate(String sql, int[] columnIndexes)
378  throws SQLException { // logger.debug("Entered");
379  throw new UnsupportedOperationException("Not supported yet,"
380  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
381  + " class:" + new Throwable().getStackTrace()[0].getClassName()
382  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
383  }
384 
385  @Override
386  public int executeUpdate(String sql, String[] columnNames)
387  throws SQLException { // logger.debug("Entered");
388  throw new UnsupportedOperationException("Not supported yet,"
389  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
390  + " class:" + new Throwable().getStackTrace()[0].getClassName()
391  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
392  }
393 
394  @Override
395  public boolean execute(String sql, int autoGeneratedKeys)
396  throws SQLException { // logger.debug("Entered");
397  throw new UnsupportedOperationException("Not supported yet,"
398  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
399  + " class:" + new Throwable().getStackTrace()[0].getClassName()
400  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
401  }
402 
403  @Override
404  public boolean execute(String sql, int[] columnIndexes)
405  throws SQLException { // logger.debug("Entered");
406  throw new UnsupportedOperationException("Not supported yet,"
407  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
408  + " class:" + new Throwable().getStackTrace()[0].getClassName()
409  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
410  }
411 
412  @Override
413  public boolean execute(String sql, String[] columnNames)
414  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 int getResultSetHoldability() 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 boolean isClosed() throws SQLException { // logger.debug("Entered");
431  return isClosed;
432  }
433 
434  @Override
435  public void setPoolable(boolean poolable)
436  throws SQLException { // logger.debug("Entered");
437  throw new UnsupportedOperationException("Not supported yet,"
438  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
439  + " class:" + new Throwable().getStackTrace()[0].getClassName()
440  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
441  }
442 
443  @Override
444  public boolean isPoolable() throws SQLException { // logger.debug("Entered");
445  throw new UnsupportedOperationException("Not supported yet,"
446  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
447  + " class:" + new Throwable().getStackTrace()[0].getClassName()
448  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
449  }
450 
451  @Override
452  public void closeOnCompletion() throws SQLException { // logger.debug("Entered");
453  throw new UnsupportedOperationException("Not supported yet,"
454  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
455  + " class:" + new Throwable().getStackTrace()[0].getClassName()
456  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
457  }
458 
459  @Override
460  public boolean isCloseOnCompletion() throws SQLException { // logger.debug("Entered");
461  throw new UnsupportedOperationException("Not supported yet,"
462  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
463  + " class:" + new Throwable().getStackTrace()[0].getClassName()
464  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
465  }
466 
467  @Override
468  public <T> T unwrap(Class<T> iface) throws SQLException { // logger.debug("Entered");
469  throw new UnsupportedOperationException("Not supported yet,"
470  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
471  + " class:" + new Throwable().getStackTrace()[0].getClassName()
472  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
473  }
474 
475  @Override
476  public boolean isWrapperFor(Class<?> iface)
477  throws SQLException { // logger.debug("Entered");
478  throw new UnsupportedOperationException("Not supported yet,"
479  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
480  + " class:" + new Throwable().getStackTrace()[0].getClassName()
481  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
482  }
483 
484  private static final Pattern QUARTER = Pattern.compile(
485  "\\sQUARTER\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
486  private static final Pattern DAYOFYEAR = Pattern.compile(
487  "\\sDAYOFYEAR\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
488  private static final Pattern DAYOFWEEK = Pattern.compile(
489  "\\sDAYOFWEEK\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
490  private static final Pattern WEEK = Pattern.compile(
491  "\\sWEEK\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
492 
493  /*
494  * CURRENTDATE should match CURRENT_DATE
495  * and CURRENT_DATE() where the two strings are 'joined' to either white space,
496  * punctuation or some kind of brackets. if they are joined to
497  * any alpha numeric For example 'CURRENT_TIME)' is okay while a string
498  * like CURRENT_DATE_NOW isn't
499  *
500  * Note we've include the non standard version with parenthesis to align with third
501  * party software.
502  *
503  * Breaking down the components of the pattern
504  * (?<![\\w.]) The pattern can not be preceded by any word character or a '.'
505  * (?:\\(\\))? pattern can end in zero or one '()' - note non capture group
506  * (?![\\w.]) the pattern can not be followed by a word character or a '.'
507  * Note - word characters include '_'
508  */
509  ;
510  private static final Pattern CURRENTDATE =
511  Pattern.compile("(?<![\\w.])CURRENT_DATE(?:\\(\\))?(?![\\w.])",
512  Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
513  public static String simplisticDateTransform(String sql) {
514  // need to iterate as each reduction of string opens up a anew match
515  String start;
516  do {
517  // Example transform - select quarter(val) from table;
518  // will become select extract(quarter from val) from table;
519  // will also replace all CURRENT_TIME and CURRENT_DATE with a call to now().
520  start = sql;
521  sql = QUARTER.matcher(sql).replaceAll(" EXTRACT(QUARTER FROM $1");
522  } while (!sql.equals(start));
523 
524  do {
525  start = sql;
526  sql = DAYOFYEAR.matcher(sql).replaceAll(" EXTRACT(DOY FROM $1");
527  } while (!sql.equals(start));
528 
529  do {
530  start = sql;
531  sql = DAYOFWEEK.matcher(sql).replaceAll(" EXTRACT(ISODOW FROM $1");
532  } while (!sql.equals(start));
533 
534  do {
535  start = sql;
536  sql = WEEK.matcher(sql).replaceAll(" EXTRACT(WEEK FROM $1");
537  } while (!sql.equals(start));
538 
539  do {
540  start = sql;
541  sql = CURRENTDATE.matcher(sql).replaceAll(" cast(now() as date) ");
542  } while (!sql.equals(start));
543 
544  return sql;
545  }
546 
547  private void checkClosed() throws SQLException {
548  if (isClosed) {
549  throw new SQLException("Statement is closed.");
550  }
551  }
552 }
static String simplisticDateTransform(String sql)
static String getExceptionDetail(Exception ex)
boolean isWrapperFor(Class<?> iface)
public< T > T unwrap(Class< T > iface)
std::vector< std::string > split(std::string_view str, std::string_view delim, std::optional< size_t > maxsplit)
split apart a string into a vector of substrings
OmniSciStatement(String tsession, OmniSciConnection tconnection)
OmniSciStatement(String tsession, OmniSci.Client tclient)
static final org.slf4j.Logger logger
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)
string name
Definition: setup.py:35
int executeUpdate(String sql, int autoGeneratedKeys)