OmniSciDB  a987f07e93
 All Classes Namespaces Files Functions Variables Typedefs Enumerations Enumerator Friends Macros Groups Pages
HeavyAIStatement.java
Go to the documentation of this file.
1 /*
2  * Copyright 2022 HEAVY.AI, 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 ai.heavy.jdbc;
17 
18 import org.apache.thrift.TException;
19 import org.slf4j.LoggerFactory;
20 
21 import java.sql.Connection;
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.sql.SQLWarning;
25 import java.util.regex.Matcher;
26 import java.util.regex.Pattern;
27 
28 import ai.heavy.thrift.server.Heavy;
29 import ai.heavy.thrift.server.TDBException;
30 import ai.heavy.thrift.server.TQueryResult;
31 
32 public class HeavyAIStatement implements java.sql.Statement {
33  final static org.slf4j.Logger logger = LoggerFactory.getLogger(HeavyAIStatement.class);
34  public SQLWarning rootWarning = null;
35 
36  private String session;
37  private Heavy.Client client;
39  private ResultSet currentRS = null;
40  private TQueryResult sqlResult = null;
41  private int maxRows; // add limit to unlimited queries
42  private boolean escapeProcessing = false;
43  private boolean isClosed = false;
44 
45  HeavyAIStatement(String tsession, HeavyAIConnection tconnection) {
46  session = tsession;
47  connection = tconnection;
48  client = connection.client;
49  maxRows = Integer.parseInt(connection.cP.getProperty(Options.max_rows));
50  }
51 
52  static Pattern top_pattern =
53  Pattern.compile("select top\\s+([0-9]+)\\s+", Pattern.CASE_INSENSITIVE);
54 
55  @Override
56  public ResultSet executeQuery(String sql)
57  throws SQLException { // logger.debug("Entered");
58  checkClosed();
59  // @TODO: we can and probably should use "first_n" parameter of the
60  // sql_execute()
61  // endpoint to force the limit on the query, instead of rewriting it here.
62  if (maxRows >= 0) {
63  // add limit to sql call if it doesn't already have one and is a select
64  String[] tokens = sql.toLowerCase().split(" ", 3);
65  if (tokens[0].equals("select")) {
66  if (sql.toLowerCase().contains("limit")) {
67  // do nothing -
68  } else {
69  // Some applications add TOP <number> to limit the
70  // select statement rather than limit. Remove TOP and keep
71  // the number it used as the limit.
72  Matcher matcher = top_pattern.matcher(sql);
73  // Take "select TOP nnnn <rest ot sql>" and translate to select <reset of sql:
74  // limit nnnn
75  if (matcher.find()) {
76  maxRows = Integer.parseInt(matcher.group(1));
77  sql = top_pattern.matcher(sql).replaceAll("select ");
78  }
79 
80  sql = sql + " LIMIT " + maxRows;
81  logger.debug("Added LIMIT of " + maxRows);
82  }
83  }
84  }
85 
86  logger.debug("Before HeavyAIEscapeParser [" + sql + "]");
87  // The order of these to SQL re-writes is important.
88  // EscapeParse needs to come first.
89  String afterEscapeParseSQL = HeavyAIEscapeParser.parse(sql);
90  String afterSimpleParse = simplisticDateTransform(afterEscapeParseSQL);
91  logger.debug("After HeavyAIEscapeParser [" + afterSimpleParse + "]");
92  try {
93  sqlResult = client.sql_execute(session, afterSimpleParse + ";", true, null, -1, -1);
94  } catch (TDBException ex) {
95  throw new SQLException(
96  "Query failed : " + HeavyAIExceptionText.getExceptionDetail(ex));
97  } catch (TException ex) {
98  throw new SQLException(
99  "Query failed : " + HeavyAIExceptionText.getExceptionDetail(ex));
100  }
101 
103  return currentRS;
104  }
105 
106  @Override
107  public void cancel() throws SQLException { // logger.debug("Entered");
108  checkClosed();
109  HeavyAIConnection alternate_connection = null;
110  try {
111  alternate_connection = connection.getAlternateConnection();
112  // Note alternate_connection shares a session with original connection
113  alternate_connection.client.interrupt(session, session);
114  } catch (TDBException ttE) {
115  throw new SQLException("Thrift transport connection failed - "
117  ttE);
118  } catch (TException tE) {
119  throw new SQLException(
120  "Thrift failed - " + HeavyAIExceptionText.getExceptionDetail(tE), tE);
121  } finally {
122  // Note closeConnection only closes the underlying thrft connection
123  // not the logical db session connection
124  alternate_connection.closeConnection();
125  }
126  }
127 
128  @Override
129  public int executeUpdate(String sql) throws SQLException { // logger.debug("Entered");
130  checkClosed();
131  try {
132  // remove " characters if it is a CREATE statement
133  if (sql.trim().substring(0, 6).compareToIgnoreCase("CREATE") == 0) {
134  sql = sql.replace('"', ' ');
135  }
136  sqlResult = client.sql_execute(session, sql + ";", true, null, -1, -1);
137  } catch (TDBException ex) {
138  throw new SQLException("Query failed : sql was '" + sql + "' "
140  ex);
141  } catch (TException ex) {
142  throw new SQLException(
143  "Query failed : " + HeavyAIExceptionText.getExceptionDetail(ex), ex);
144  }
145 
146  // TODO: OmniSciDB supports updates, inserts and deletes, but
147  // there is no way to get number of affected rows at the moment
148  return -1;
149  }
150 
151  @Override
152  public void close() throws SQLException { // logger.debug("Entered");
153  if (currentRS != null) {
154  currentRS.close();
155  }
156  isClosed = true;
157  }
158 
159  @Override
160  public int getMaxFieldSize() throws SQLException { // logger.debug("Entered");
161  throw new UnsupportedOperationException("Not supported yet,"
162  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
163  + " class:" + new Throwable().getStackTrace()[0].getClassName()
164  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
165  }
166 
167  @Override
168  public void setMaxFieldSize(int max) throws SQLException { // logger.debug("Entered");
169  throw new UnsupportedOperationException("Not supported yet,"
170  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
171  + " class:" + new Throwable().getStackTrace()[0].getClassName()
172  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
173  }
174 
175  @Override
176  public int getMaxRows() throws SQLException { // logger.debug("Entered");
177  return maxRows;
178  }
179 
180  @Override
181  public void setMaxRows(int max) throws SQLException { // logger.debug("Entered");
182  maxRows = max;
183  }
184 
185  @Override
186  public void setEscapeProcessing(boolean enable)
187  throws SQLException { // logger.debug("Entered");
188  escapeProcessing = enable;
189  }
190 
191  @Override
192  public int getQueryTimeout() throws SQLException { // logger.debug("Entered");
193  return 0;
194  }
195 
196  // used by benchmarking to get internal execution times
198  throws SQLException { // logger.debug("Entered");
199  return (int) sqlResult.execution_time_ms;
200  }
201 
202  @Override
203  public void setQueryTimeout(int seconds)
204  throws SQLException { // logger.debug("Entered");
205  SQLWarning warning = new SQLWarning(
206  "Query timeouts are not supported. Substituting a value of zero.");
207  if (rootWarning == null) {
208  rootWarning = warning;
209  } else {
210  rootWarning.setNextWarning(warning);
211  }
212  }
213 
214  @Override
215  public SQLWarning getWarnings() throws SQLException { // logger.debug("Entered");
216  return (rootWarning);
217  }
218 
219  @Override
220  public void clearWarnings() throws SQLException { // logger.debug("Entered");
221  rootWarning = null;
222  }
223 
224  @Override
225  public void setCursorName(String name) throws SQLException { // logger.debug("Entered");
226  throw new UnsupportedOperationException("Not supported yet,"
227  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
228  + " class:" + new Throwable().getStackTrace()[0].getClassName()
229  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
230  }
231 
232  @Override
233  public boolean execute(String sql) throws SQLException { // logger.debug("Entered");
234  ResultSet rs = executeQuery(sql);
235  if (rs != null) {
236  return true;
237  } else {
238  return false;
239  }
240  }
241 
242  @Override
243  public ResultSet getResultSet() throws SQLException { // logger.debug("Entered");
244  checkClosed();
245  return currentRS;
246  }
247 
248  @Override
249  public int getUpdateCount() throws SQLException { // logger.debug("Entered");
250  checkClosed();
251  // TODO: OmniSciDB supports updates, inserts and deletes, but
252  // there is no way to get number of affected rows at the moment
253  return -1;
254  }
255 
256  @Override
257  public boolean getMoreResults() throws SQLException { // logger.debug("Entered");
258  checkClosed();
259  // TODO MAT this needs to be fixed for complex queries
260  return false;
261  }
262 
263  @Override
264  public void setFetchDirection(int direction)
265  throws SQLException { // logger.debug("Entered");
266  throw new UnsupportedOperationException("Not supported yet,"
267  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
268  + " class:" + new Throwable().getStackTrace()[0].getClassName()
269  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
270  }
271 
272  @Override
273  public int getFetchDirection() throws SQLException { // logger.debug("Entered");
274  return ResultSet.FETCH_FORWARD;
275  }
276 
277  @Override
278  public void setFetchSize(int rows) throws SQLException { // logger.debug("Entered");
279  SQLWarning warning = new SQLWarning(
280  "Query FetchSize are not supported. Substituting a value of zero.");
281  if (rootWarning == null) {
282  rootWarning = warning;
283  } else {
284  rootWarning.setNextWarning(warning);
285  }
286  }
287 
288  @Override
289  public int getFetchSize() throws SQLException { // logger.debug("Entered");
290  throw new UnsupportedOperationException("Not supported yet,"
291  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
292  + " class:" + new Throwable().getStackTrace()[0].getClassName()
293  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
294  }
295 
296  @Override
297  public int getResultSetConcurrency() throws SQLException { // logger.debug("Entered");
298  throw new UnsupportedOperationException("Not supported yet,"
299  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
300  + " class:" + new Throwable().getStackTrace()[0].getClassName()
301  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
302  }
303 
304  @Override
305  public int getResultSetType() throws SQLException { // logger.debug("Entered");
306  throw new UnsupportedOperationException("Not supported yet,"
307  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
308  + " class:" + new Throwable().getStackTrace()[0].getClassName()
309  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
310  }
311 
312  @Override
313  public void addBatch(String sql) throws SQLException { // logger.debug("Entered");
314  throw new UnsupportedOperationException("Not supported yet,"
315  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
316  + " class:" + new Throwable().getStackTrace()[0].getClassName()
317  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
318  }
319 
320  @Override
321  public void clearBatch() throws SQLException { // logger.debug("Entered");
322  throw new UnsupportedOperationException("Not supported yet,"
323  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
324  + " class:" + new Throwable().getStackTrace()[0].getClassName()
325  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
326  }
327 
328  @Override
329  public int[] executeBatch() throws SQLException { // logger.debug("Entered");
330  throw new UnsupportedOperationException("Not supported yet,"
331  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
332  + " class:" + new Throwable().getStackTrace()[0].getClassName()
333  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
334  }
335 
336  @Override
337  public Connection getConnection() throws SQLException { // logger.debug("Entered");
338  return connection;
339  }
340 
341  @Override
342  public boolean getMoreResults(int current)
343  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 ResultSet getGeneratedKeys() throws SQLException { // logger.debug("Entered");
352  throw new UnsupportedOperationException("Not supported yet,"
353  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
354  + " class:" + new Throwable().getStackTrace()[0].getClassName()
355  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
356  }
357 
358  @Override
359  public int executeUpdate(String sql, int autoGeneratedKeys)
360  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[] columnIndexes)
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, String[] columnNames)
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 boolean execute(String sql, int autoGeneratedKeys)
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[] columnIndexes)
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, String[] columnNames)
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 int getResultSetHoldability() throws SQLException { // logger.debug("Entered");
414  throw new UnsupportedOperationException("Not supported yet,"
415  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
416  + " class:" + new Throwable().getStackTrace()[0].getClassName()
417  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
418  }
419 
420  @Override
421  public boolean isClosed() throws SQLException { // logger.debug("Entered");
422  return isClosed;
423  }
424 
425  @Override
426  public void setPoolable(boolean poolable)
427  throws SQLException { // logger.debug("Entered");
428  throw new UnsupportedOperationException("Not supported yet,"
429  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
430  + " class:" + new Throwable().getStackTrace()[0].getClassName()
431  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
432  }
433 
434  @Override
435  public boolean isPoolable() throws SQLException { // logger.debug("Entered");
436  throw new UnsupportedOperationException("Not supported yet,"
437  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
438  + " class:" + new Throwable().getStackTrace()[0].getClassName()
439  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
440  }
441 
442  @Override
443  public void closeOnCompletion() throws SQLException { // logger.debug("Entered");
444  throw new UnsupportedOperationException("Not supported yet,"
445  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
446  + " class:" + new Throwable().getStackTrace()[0].getClassName()
447  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
448  }
449 
450  @Override
451  public boolean isCloseOnCompletion() throws SQLException { // logger.debug("Entered");
452  throw new UnsupportedOperationException("Not supported yet,"
453  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
454  + " class:" + new Throwable().getStackTrace()[0].getClassName()
455  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
456  }
457 
458  @Override
459  public <T> T unwrap(Class<T> iface) throws SQLException { // logger.debug("Entered");
460  throw new UnsupportedOperationException("Not supported yet,"
461  + " line:" + new Throwable().getStackTrace()[0].getLineNumber()
462  + " class:" + new Throwable().getStackTrace()[0].getClassName()
463  + " method:" + new Throwable().getStackTrace()[0].getMethodName());
464  }
465 
466  @Override
467  public boolean isWrapperFor(Class<?> iface)
468  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  private static final Pattern QUARTER = Pattern.compile(
476  "\\sQUARTER\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
477  private static final Pattern DAYOFYEAR = Pattern.compile(
478  "\\sDAYOFYEAR\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
479  private static final Pattern DAYOFWEEK = Pattern.compile(
480  "\\sDAYOFWEEK\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
481  private static final Pattern WEEK = Pattern.compile(
482  "\\sWEEK\\(([^\\{]*?)", Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
483 
484  /*
485  * CURRENTDATE should match CURRENT_DATE
486  * and CURRENT_DATE() where the two strings are 'joined' to either white space,
487  * punctuation or some kind of brackets. if they are joined to
488  * any alpha numeric For example 'CURRENT_TIME)' is okay while a string
489  * like CURRENT_DATE_NOW isn't
490  *
491  * Note we've include the non standard version with parenthesis to align with
492  * third
493  * party software.
494  *
495  * Breaking down the components of the pattern
496  * (?<![\\w.]) The pattern can not be preceded by any word character or a '.'
497  * (?:\\(\\))? pattern can end in zero or one '()' - note non capture group
498  * (?![\\w.]) the pattern can not be followed by a word character or a '.'
499  * Note - word characters include '_'
500  */
501  ;
502  private static final Pattern CURRENTDATE =
503  Pattern.compile("(?<![\\w.])CURRENT_DATE(?:\\(\\))?(?![\\w.])",
504  Pattern.DOTALL | Pattern.CASE_INSENSITIVE);
505 
506  public static String simplisticDateTransform(String sql) {
507  // need to iterate as each reduction of string opens up a anew match
508  String start;
509  do {
510  // Example transform - select quarter(val) from table;
511  // will become select extract(quarter from val) from table;
512  // will also replace all CURRENT_TIME and CURRENT_DATE with a call to now().
513  start = sql;
514  sql = QUARTER.matcher(sql).replaceAll(" EXTRACT(QUARTER FROM $1");
515  } while (!sql.equals(start));
516 
517  do {
518  start = sql;
519  sql = DAYOFYEAR.matcher(sql).replaceAll(" EXTRACT(DOY FROM $1");
520  } while (!sql.equals(start));
521 
522  do {
523  start = sql;
524  sql = DAYOFWEEK.matcher(sql).replaceAll(" EXTRACT(ISODOW FROM $1");
525  } while (!sql.equals(start));
526 
527  do {
528  start = sql;
529  sql = WEEK.matcher(sql).replaceAll(" EXTRACT(WEEK FROM $1");
530  } while (!sql.equals(start));
531 
532  do {
533  start = sql;
534  sql = CURRENTDATE.matcher(sql).replaceAll(" cast(now() as date) ");
535  } while (!sql.equals(start));
536 
537  return sql;
538  }
539 
540  private void checkClosed() throws SQLException {
541  if (isClosed) {
542  throw new SQLException("Statement is closed.");
543  }
544  }
545 }
boolean execute(String sql, int[] columnIndexes)
public< T > T unwrap(Class< T > iface)
boolean isWrapperFor(Class<?> iface)
int executeUpdate(String sql, String[] columnNames)
static String simplisticDateTransform(String sql)
boolean execute(String sql, String[] columnNames)
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
void setFetchDirection(int direction)
void setPoolable(boolean poolable)
boolean execute(String sql, int autoGeneratedKeys)
int executeUpdate(String sql, int[] columnIndexes)
HeavyAIStatement(String tsession, HeavyAIConnection tconnection)
static String getExceptionDetail(Exception ex)
static final org.slf4j.Logger logger
boolean getMoreResults(int current)
ResultSet executeQuery(String sql)
string name
Definition: setup.in.py:72
void setEscapeProcessing(boolean enable)
int executeUpdate(String sql, int autoGeneratedKeys)