OmniSciDB  6686921089
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups 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; // 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  maxRows = Integer.parseInt(connection.cP.getProperty(Options.max_rows));
55  }
56 
57  static Pattern top_pattern =
58  Pattern.compile("select top\\s+([0-9]+)\\s+", Pattern.CASE_INSENSITIVE);
59 
60  @Override
61  public ResultSet executeQuery(String sql)
62  throws SQLException { // logger.debug("Entered");
63  checkClosed();
64  // @TODO: we can and probably should use "first_n" parameter of the sql_execute()
65  // endpoint to force the limit on the query, instead of rewriting it here.
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 
107  return currentRS;
108  }
109 
110  @Override
111  public void cancel() throws SQLException { // logger.debug("Entered");
112  checkClosed();
113  OmniSciConnection alternate_connection = null;
114  try {
115  alternate_connection = connection.getAlternateConnection();
116  // Note alternate_connection shares a session with original connection
117  alternate_connection.client.interrupt(session, session);
118  } catch (TOmniSciException ttE) {
119  throw new SQLException("Thrift transport connection failed - "
121  ttE);
122  } catch (TException tE) {
123  throw new SQLException(
124  "Thrift failed - " + OmniSciExceptionText.getExceptionDetail(tE), tE);
125  } finally {
126  // Note closeConnection only closes the underlying thrft connection
127  // not the logical db session connection
128  alternate_connection.closeConnection();
129  }
130  }
131 
132  @Override
133  public int executeUpdate(String sql) throws SQLException { // logger.debug("Entered");
134  checkClosed();
135  try {
136  // remove " characters if it is a CREATE statement
137  if (sql.trim().substring(0, 6).compareToIgnoreCase("CREATE") == 0) {
138  sql = sql.replace('"', ' ');
139  }
140  sqlResult = client.sql_execute(session, sql + ";", true, null, -1, -1);
141  } catch (TOmniSciException ex) {
142  throw new SQLException("Query failed : sql was '" + sql + "' "
144  ex);
145  } catch (TException ex) {
146  throw new SQLException(
147  "Query failed : " + OmniSciExceptionText.getExceptionDetail(ex), ex);
148  }
149 
150  // TODO: OmniSciDB supports updates, inserts and deletes, but
151  // there is no way to get number of affected rows at the moment
152  return -1;
153  }
154 
155  @Override
156  public void close() throws SQLException { // logger.debug("Entered");
157  if (currentRS != null) {
158  currentRS.close();
159  }
160  isClosed = true;
161  }
162 
163  @Override
164  public int getMaxFieldSize() throws SQLException { // logger.debug("Entered");
165  throw new UnsupportedOperationException("Not supported yet,"
166  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
167  + " class:" + new Throwable().getStackTrace()[0].getClassName()
168  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
169  }
170 
171  @Override
172  public void setMaxFieldSize(int max) throws SQLException { // logger.debug("Entered");
173  throw new UnsupportedOperationException("Not supported yet,"
174  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
175  + " class:" + new Throwable().getStackTrace()[0].getClassName()
176  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
177  }
178 
179  @Override
180  public int getMaxRows() throws SQLException { // logger.debug("Entered");
181  return maxRows;
182  }
183 
184  @Override
185  public void setMaxRows(int max) throws SQLException { // logger.debug("Entered");
186  maxRows = max;
187  }
188 
189  @Override
190  public void setEscapeProcessing(boolean enable)
191  throws SQLException { // logger.debug("Entered");
192  escapeProcessing = enable;
193  }
194 
195  @Override
196  public int getQueryTimeout() throws SQLException { // logger.debug("Entered");
197  return 0;
198  }
199 
200  // used by benchmarking to get internal execution times
202  throws SQLException { // logger.debug("Entered");
203  return (int) sqlResult.execution_time_ms;
204  }
205 
206  @Override
207  public void setQueryTimeout(int seconds)
208  throws SQLException { // logger.debug("Entered");
209  SQLWarning warning = new SQLWarning(
210  "Query timeouts are not supported. Substituting a value of zero.");
211  if (rootWarning == null) {
212  rootWarning = warning;
213  } else {
214  rootWarning.setNextWarning(warning);
215  }
216  }
217 
218  @Override
219  public SQLWarning getWarnings() throws SQLException { // logger.debug("Entered");
220  return (rootWarning);
221  }
222 
223  @Override
224  public void clearWarnings() throws SQLException { // logger.debug("Entered");
225  rootWarning = null;
226  }
227 
228  @Override
229  public void setCursorName(String name) throws SQLException { // logger.debug("Entered");
230  throw new UnsupportedOperationException("Not supported yet,"
231  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
232  + " class:" + new Throwable().getStackTrace()[0].getClassName()
233  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
234  }
235 
236  @Override
237  public boolean execute(String sql) throws SQLException { // logger.debug("Entered");
238  ResultSet rs = executeQuery(sql);
239  if (rs != null) {
240  return true;
241  } else {
242  return false;
243  }
244  }
245 
246  @Override
247  public ResultSet getResultSet() throws SQLException { // logger.debug("Entered");
248  checkClosed();
249  return currentRS;
250  }
251 
252  @Override
253  public int getUpdateCount() throws SQLException { // logger.debug("Entered");
254  checkClosed();
255  // TODO: OmniSciDB supports updates, inserts and deletes, but
256  // there is no way to get number of affected rows at the moment
257  return -1;
258  }
259 
260  @Override
261  public boolean getMoreResults() throws SQLException { // logger.debug("Entered");
262  checkClosed();
263  // TODO MAT this needs to be fixed for complex queries
264  return false;
265  }
266 
267  @Override
268  public void setFetchDirection(int direction)
269  throws SQLException { // logger.debug("Entered");
270  throw new UnsupportedOperationException("Not supported yet,"
271  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
272  + " class:" + new Throwable().getStackTrace()[0].getClassName()
273  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
274  }
275 
276  @Override
277  public int getFetchDirection() throws SQLException { // logger.debug("Entered");
278  return ResultSet.FETCH_FORWARD;
279  }
280 
281  @Override
282  public void setFetchSize(int rows) throws SQLException { // logger.debug("Entered");
283  SQLWarning warning = new SQLWarning(
284  "Query FetchSize are not supported. Substituting a value of zero.");
285  if (rootWarning == null) {
286  rootWarning = warning;
287  } else {
288  rootWarning.setNextWarning(warning);
289  }
290  }
291 
292  @Override
293  public int getFetchSize() throws SQLException { // logger.debug("Entered");
294  throw new UnsupportedOperationException("Not supported yet,"
295  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
296  + " class:" + new Throwable().getStackTrace()[0].getClassName()
297  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
298  }
299 
300  @Override
301  public int getResultSetConcurrency() throws SQLException { // logger.debug("Entered");
302  throw new UnsupportedOperationException("Not supported yet,"
303  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
304  + " class:" + new Throwable().getStackTrace()[0].getClassName()
305  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
306  }
307 
308  @Override
309  public int getResultSetType() throws SQLException { // logger.debug("Entered");
310  throw new UnsupportedOperationException("Not supported yet,"
311  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
312  + " class:" + new Throwable().getStackTrace()[0].getClassName()
313  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
314  }
315 
316  @Override
317  public void addBatch(String sql) throws SQLException { // logger.debug("Entered");
318  throw new UnsupportedOperationException("Not supported yet,"
319  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
320  + " class:" + new Throwable().getStackTrace()[0].getClassName()
321  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
322  }
323 
324  @Override
325  public void clearBatch() throws SQLException { // logger.debug("Entered");
326  throw new UnsupportedOperationException("Not supported yet,"
327  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
328  + " class:" + new Throwable().getStackTrace()[0].getClassName()
329  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
330  }
331 
332  @Override
333  public int[] executeBatch() throws SQLException { // logger.debug("Entered");
334  throw new UnsupportedOperationException("Not supported yet,"
335  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
336  + " class:" + new Throwable().getStackTrace()[0].getClassName()
337  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
338  }
339 
340  @Override
341  public Connection getConnection() throws SQLException { // logger.debug("Entered");
342  return connection;
343  }
344 
345  @Override
346  public boolean getMoreResults(int current)
347  throws SQLException { // logger.debug("Entered");
348  throw new UnsupportedOperationException("Not supported yet,"
349  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
350  + " class:" + new Throwable().getStackTrace()[0].getClassName()
351  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
352  }
353 
354  @Override
355  public ResultSet getGeneratedKeys() throws SQLException { // logger.debug("Entered");
356  throw new UnsupportedOperationException("Not supported yet,"
357  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
358  + " class:" + new Throwable().getStackTrace()[0].getClassName()
359  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
360  }
361 
362  @Override
363  public int executeUpdate(String sql, int autoGeneratedKeys)
364  throws SQLException { // logger.debug("Entered");
365  throw new UnsupportedOperationException("Not supported yet,"
366  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
367  + " class:" + new Throwable().getStackTrace()[0].getClassName()
368  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
369  }
370 
371  @Override
372  public int executeUpdate(String sql, int[] columnIndexes)
373  throws SQLException { // logger.debug("Entered");
374  throw new UnsupportedOperationException("Not supported yet,"
375  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
376  + " class:" + new Throwable().getStackTrace()[0].getClassName()
377  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
378  }
379 
380  @Override
381  public int executeUpdate(String sql, String[] columnNames)
382  throws SQLException { // logger.debug("Entered");
383  throw new UnsupportedOperationException("Not supported yet,"
384  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
385  + " class:" + new Throwable().getStackTrace()[0].getClassName()
386  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
387  }
388 
389  @Override
390  public boolean execute(String sql, int autoGeneratedKeys)
391  throws SQLException { // logger.debug("Entered");
392  throw new UnsupportedOperationException("Not supported yet,"
393  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
394  + " class:" + new Throwable().getStackTrace()[0].getClassName()
395  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
396  }
397 
398  @Override
399  public boolean execute(String sql, int[] columnIndexes)
400  throws SQLException { // logger.debug("Entered");
401  throw new UnsupportedOperationException("Not supported yet,"
402  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
403  + " class:" + new Throwable().getStackTrace()[0].getClassName()
404  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
405  }
406 
407  @Override
408  public boolean execute(String sql, String[] columnNames)
409  throws SQLException { // logger.debug("Entered");
410  throw new UnsupportedOperationException("Not supported yet,"
411  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
412  + " class:" + new Throwable().getStackTrace()[0].getClassName()
413  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
414  }
415 
416  @Override
417  public int getResultSetHoldability() throws SQLException { // logger.debug("Entered");
418  throw new UnsupportedOperationException("Not supported yet,"
419  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
420  + " class:" + new Throwable().getStackTrace()[0].getClassName()
421  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
422  }
423 
424  @Override
425  public boolean isClosed() throws SQLException { // logger.debug("Entered");
426  return isClosed;
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 
542  private void checkClosed() throws SQLException {
543  if (isClosed) {
544  throw new SQLException("Statement is closed.");
545  }
546  }
547 }
static String simplisticDateTransform(String sql)
static String getExceptionDetail(Exception ex)
string name
Definition: setup.in.py:72
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)
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)
int executeUpdate(String sql, int autoGeneratedKeys)