OmniSciDB  c07336695a
MetaConnect.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.mapd.metadata;
17 
18 import com.google.common.collect.ImmutableList;
24 import com.mapd.thrift.server.MapD;
25 import com.mapd.thrift.server.TColumnType;
26 import com.mapd.thrift.server.TDatumType;
27 import com.mapd.thrift.server.TEncodingType;
28 import com.mapd.thrift.server.TMapDException;
29 import com.mapd.thrift.server.TTableDetails;
30 import com.mapd.thrift.server.TTypeInfo;
31 
32 import org.apache.calcite.schema.Table;
33 import org.apache.thrift.TException;
34 import org.apache.thrift.protocol.TBinaryProtocol;
35 import org.apache.thrift.protocol.TProtocol;
36 import org.apache.thrift.transport.TServerSocket;
37 import org.apache.thrift.transport.TSocket;
38 import org.apache.thrift.transport.TTransport;
39 import org.apache.thrift.transport.TTransportException;
40 import org.slf4j.Logger;
41 import org.slf4j.LoggerFactory;
42 
43 import java.sql.Connection;
44 import java.sql.DriverManager;
45 import java.sql.ResultSet;
46 import java.sql.SQLException;
47 import java.sql.Statement;
48 import java.util.HashSet;
49 import java.util.List;
50 import java.util.Map;
51 import java.util.Set;
52 import java.util.concurrent.ConcurrentHashMap;
53 
58 public class MetaConnect {
59  final static Logger MAPDLOGGER = LoggerFactory.getLogger(MetaConnect.class);
60  private final String dataDir;
61  private final String db;
62  private final MapDUser currentUser;
63  private final int mapdPort;
64  private Connection catConn;
65  private final MapDParser parser;
66 
67  private static final int KBOOLEAN = 1;
68  private static final int KCHAR = 2;
69  private static final int KVARCHAR = 3;
70  private static final int KNUMERIC = 4;
71  private static final int KDECIMAL = 5;
72  private static final int KINT = 6;
73  private static final int KSMALLINT = 7;
74  private static final int KFLOAT = 8;
75  private static final int KDOUBLE = 9;
76  private static final int KTIME = 10;
77  private static final int KTIMESTAMP = 11;
78  private static final int KBIGINT = 12;
79  private static final int KTEXT = 13;
80  private static final int KDATE = 14;
81  private static final int KARRAY = 15;
82  private static final int KINTERVAL_DAY_TIME = 16;
83  private static final int KINTERVAL_YEAR_MONTH = 17;
84  private static final int KPOINT = 18;
85  private static final int KLINESTRING = 19;
86  private static final int KPOLYGON = 20;
87  private static final int KMULTIPOLYGON = 21;
88  private static final int KTINYINT = 22;
89 
90  private static volatile Map<String, Set<String>> MAPD_DATABASE_TO_TABLES =
91  new ConcurrentHashMap<>();
92  private static volatile Map<List<String>, Table> MAPD_TABLE_DETAILS =
93  new ConcurrentHashMap<>();
95 
96  public MetaConnect(int mapdPort,
97  String dataDir,
98  MapDUser currentMapDUser,
99  MapDParser parser,
101  this.dataDir = dataDir;
102  if (currentMapDUser != null) {
103  this.db = currentMapDUser.getDB();
104  } else {
105  this.db = null;
106  }
107  this.currentUser = currentMapDUser;
108  this.mapdPort = mapdPort;
109  this.parser = parser;
110  this.sock_transport_properties = skT;
111  }
112 
113  private void connectToDBCatalog() {
114  try {
115  // try {
116  Class.forName("org.sqlite.JDBC");
117  } catch (ClassNotFoundException ex) {
118  String err = "Could not find class for metadata connection; DB: '" + db
119  + "' data dir '" + dataDir + "', error was " + ex.getMessage();
120  MAPDLOGGER.error(err);
121  throw new RuntimeException(err);
122  }
123  String connectURL = "jdbc:sqlite:" + dataDir + "/mapd_catalogs/" + db;
124  try {
125  catConn = DriverManager.getConnection(connectURL);
126  } catch (SQLException ex) {
127  String err = "Could not establish a connection for metadata; DB: '" + db
128  + "' data dir '" + dataDir + "', error was " + ex.getMessage();
129  MAPDLOGGER.error(err);
130  throw new RuntimeException(err);
131  }
132  MAPDLOGGER.debug("Opened database successfully");
133  }
134 
135  private void disconnectFromDBCatalog() {
136  try {
137  catConn.close();
138  } catch (SQLException ex) {
139  String err = "Could not disconnect for metadata; DB: '" + db + "' data dir '"
140  + dataDir + "', error was " + ex.getMessage();
141  MAPDLOGGER.error(err);
142  throw new RuntimeException(err);
143  }
144  }
145 
146  public Table getTable(String tableName) {
147  List<String> dbTable = ImmutableList.of(db.toUpperCase(), tableName.toUpperCase());
148  Table cTable = MAPD_TABLE_DETAILS.get(dbTable);
149  if (cTable != null) {
150  return cTable;
151  }
152 
153  TTableDetails td = get_table_details(tableName);
154 
155  if (td.getView_sql() == null || td.getView_sql().isEmpty()) {
156  MAPDLOGGER.debug("Processing a table");
157  Table rTable = new MapDTable(td);
158  MAPD_TABLE_DETAILS.putIfAbsent(dbTable, rTable);
159  return rTable;
160  } else {
161  MAPDLOGGER.debug("Processing a view");
162  Table rTable = new MapDView(getViewSql(tableName), td, parser);
163  MAPD_TABLE_DETAILS.putIfAbsent(dbTable, rTable);
164  return rTable;
165  }
166  }
167 
168  public Set<String> getTables() {
169  Set<String> mSet = MAPD_DATABASE_TO_TABLES.get(db.toUpperCase());
170  if (mSet != null) {
171  return mSet;
172  }
173 
174  if (mapdPort == -1) {
175  // use sql
177  Set<String> ts = getTables_SQL();
179  MAPD_DATABASE_TO_TABLES.putIfAbsent(db.toUpperCase(), ts);
180  return ts;
181  }
182  // use thrift direct to local server
183  try {
184  TProtocol protocol = null;
185  TTransport transport =
186  sock_transport_properties.openClientTransport("localhost", mapdPort);
187  if (!transport.isOpen()) transport.open();
188  protocol = new TBinaryProtocol(transport);
189 
190  MapD.Client client = new MapD.Client(protocol);
191 
192  List<String> tablesList = client.get_tables(currentUser.getSession());
193  Set<String> ts = new HashSet<String>(tablesList.size());
194  for (String tableName : tablesList) {
195  ts.add(tableName);
196  }
197 
198  transport.close();
199  MAPD_DATABASE_TO_TABLES.putIfAbsent(db.toUpperCase(), ts);
200  return ts;
201 
202  } catch (TTransportException ex) {
203  MAPDLOGGER.error("TTransportException on port [" + mapdPort + "]");
204  MAPDLOGGER.error(ex.toString());
205  throw new RuntimeException(ex.toString());
206  } catch (TMapDException ex) {
207  MAPDLOGGER.error(ex.toString());
208  throw new RuntimeException(ex.toString());
209  } catch (TException ex) {
210  MAPDLOGGER.error(ex.toString());
211  throw new RuntimeException(ex.toString());
212  }
213  }
214 
215  private Set<String> getTables_SQL() {
217  Set<String> tableSet = new HashSet<String>();
218  Statement stmt = null;
219  ResultSet rs = null;
220  String sqlText = "";
221  try {
222  stmt = catConn.createStatement();
223 
224  // get the tables
225  rs = stmt.executeQuery("SELECT name FROM mapd_tables ");
226  while (rs.next()) {
227  tableSet.add(rs.getString("name"));
228  /*--*/
229  MAPDLOGGER.debug("Object name = " + rs.getString("name"));
230  }
231  rs.close();
232  stmt.close();
233 
234  } catch (Exception e) {
235  String err = "error trying to get all the tables, error was " + e.getMessage();
236  MAPDLOGGER.error(err);
237  throw new RuntimeException(err);
238  }
240  return tableSet;
241  }
242 
243  public TTableDetails get_table_details(String tableName) {
244  if (mapdPort == -1) {
245  // use sql
247  TTableDetails td = get_table_detail_SQL(tableName);
249  return td;
250  }
251  // use thrift direct to local server
252  try {
253  TProtocol protocol = null;
254 
255  TTransport transport =
256  sock_transport_properties.openClientTransport("localhost", mapdPort);
257  if (!transport.isOpen()) transport.open();
258  protocol = new TBinaryProtocol(transport);
259 
260  MapD.Client client = new MapD.Client(protocol);
261 
262  TTableDetails td =
263  client.get_internal_table_details(currentUser.getSession(), tableName);
264 
265  transport.close();
266 
267  return td;
268 
269  } catch (TTransportException ex) {
270  MAPDLOGGER.error(ex.toString());
271  throw new RuntimeException(ex.toString());
272  } catch (TMapDException ex) {
273  MAPDLOGGER.error(ex.toString());
274  throw new RuntimeException(ex.toString());
275  } catch (TException ex) {
276  MAPDLOGGER.error(ex.toString());
277  throw new RuntimeException(ex.toString());
278  }
279  }
280 
281  public static final int get_physical_cols(int type) {
282  switch (type) {
283  case KPOINT:
284  return 1; // coords
285  case KLINESTRING:
286  return 2; // coords, bounds
287  case KPOLYGON:
288  return 4; // coords, ring_sizes, bounds, render_group
289  case KMULTIPOLYGON:
290  return 5; // coords, ring_sizes, poly_rings, bounds, render_group
291  default:
292  break;
293  }
294  return 0;
295  }
296 
297  public static final boolean is_geometry(int type) {
298  return type == KPOINT || type == KLINESTRING || type == KPOLYGON
299  || type == KMULTIPOLYGON;
300  }
301 
302  private TTableDetails get_table_detail_SQL(String tableName) {
303  TTableDetails td = new TTableDetails();
304  td.getRow_descIterator();
305  int id = getTableId(tableName);
306  if (id == -1) {
307  String err = "Table '" + tableName + "' does not exist for DB '" + db + "'";
308  MAPDLOGGER.error(err);
309  throw new RuntimeException(err);
310  }
311 
312  // read data from table
313  Statement stmt = null;
314  ResultSet rs = null;
315  try {
316  stmt = catConn.createStatement();
317  MAPDLOGGER.debug("table id is " + id);
318  MAPDLOGGER.debug("table name is " + tableName);
319  String query = String.format(
320  "SELECT * FROM mapd_columns where tableid = %d and not is_deletedcol order by columnid;",
321  id);
322  MAPDLOGGER.debug(query);
323  rs = stmt.executeQuery(query);
324  int skip_physical_cols = 0;
325  while (rs.next()) {
326  String colName = rs.getString("name");
327  MAPDLOGGER.debug("name = " + colName);
328  int colType = rs.getInt("coltype");
329  MAPDLOGGER.debug("coltype = " + colType);
330  int colSubType = rs.getInt("colsubtype");
331  MAPDLOGGER.debug("colsubtype = " + colSubType);
332  int colDim = rs.getInt("coldim");
333  MAPDLOGGER.debug("coldim = " + colDim);
334  int colScale = rs.getInt("colscale");
335  MAPDLOGGER.debug("colscale = " + colScale);
336  boolean isNotNull = rs.getBoolean("is_notnull");
337  MAPDLOGGER.debug("is_notnull = " + isNotNull);
338  boolean isSystemCol = rs.getBoolean("is_systemcol");
339  MAPDLOGGER.debug("is_systemcol = " + isSystemCol);
340  boolean isVirtualCol = rs.getBoolean("is_virtualcol");
341  MAPDLOGGER.debug("is_vitrualcol = " + isVirtualCol);
342  MAPDLOGGER.debug("");
343  TColumnType tct = new TColumnType();
344  TTypeInfo tti = new TTypeInfo();
345  TDatumType tdt;
346 
347  if (colType == KARRAY) {
348  tti.is_array = true;
349  tdt = typeToThrift(colSubType);
350  } else {
351  tti.is_array = false;
352  tdt = typeToThrift(colType);
353  }
354 
355  tti.nullable = !isNotNull;
356  tti.encoding = TEncodingType.NONE;
357  tti.type = tdt;
358  tti.scale = colScale;
359  tti.precision = colDim;
360 
361  tct.col_name = colName;
362  tct.col_type = tti;
363 
364  if (skip_physical_cols <= 0) skip_physical_cols = get_physical_cols(colType);
365  if (is_geometry(colType) || skip_physical_cols-- <= 0) td.addToRow_desc(tct);
366  }
367  } catch (Exception e) {
368  String err = "error trying to read from mapd_columns, error was " + e.getMessage();
369  MAPDLOGGER.error(err);
370  throw new RuntimeException(err);
371  } finally {
372  if (rs != null) {
373  try {
374  rs.close();
375  } catch (SQLException ex) {
376  String err = "Could not close resultset, error was " + ex.getMessage();
377  MAPDLOGGER.error(err);
378  throw new RuntimeException(err);
379  }
380  }
381  if (stmt != null) {
382  try {
383  stmt.close();
384  } catch (SQLException ex) {
385  String err = "Could not close stmt, error was " + ex.getMessage();
386  MAPDLOGGER.error(err);
387  throw new RuntimeException(err);
388  }
389  }
390  }
391  if (isView(tableName)) {
392  td.setView_sqlIsSet(true);
393  td.setView_sql(getViewSqlViaSql(id));
394  }
395  return td;
396  }
397 
398  private int getTableId(String tableName) {
399  Statement stmt = null;
400  ResultSet rs = null;
401  int tableId = -1;
402  try {
403  stmt = catConn.createStatement();
404  rs = stmt.executeQuery(String.format(
405  "SELECT tableid FROM mapd_tables where name = '%s' COLLATE NOCASE;",
406  tableName));
407  while (rs.next()) {
408  tableId = rs.getInt("tableid");
409  MAPDLOGGER.debug("tableId = " + tableId);
410  MAPDLOGGER.debug("");
411  }
412  rs.close();
413  stmt.close();
414  } catch (Exception e) {
415  String err = "Error trying to read from metadata table mapd_tables;DB: " + db
416  + " data dir " + dataDir + ", error was " + e.getMessage();
417  MAPDLOGGER.error(err);
418  throw new RuntimeException(err);
419  } finally {
420  if (rs != null) {
421  try {
422  rs.close();
423  } catch (SQLException ex) {
424  String err = "Could not close resultset, error was " + ex.getMessage();
425  MAPDLOGGER.error(err);
426  throw new RuntimeException(err);
427  }
428  }
429  if (stmt != null) {
430  try {
431  stmt.close();
432  } catch (SQLException ex) {
433  String err = "Could not close stmt, error was " + ex.getMessage();
434  MAPDLOGGER.error(err);
435  throw new RuntimeException(err);
436  }
437  }
438  }
439  return (tableId);
440  }
441 
442  private boolean isView(String tableName) {
443  Statement stmt;
444  ResultSet rs;
445  int viewFlag = 0;
446  try {
447  stmt = catConn.createStatement();
448  rs = stmt.executeQuery(String.format(
449  "SELECT isview FROM mapd_tables where name = '%s' COLLATE NOCASE;",
450  tableName));
451  while (rs.next()) {
452  viewFlag = rs.getInt("isview");
453  MAPDLOGGER.debug("viewFlag = " + viewFlag);
454  MAPDLOGGER.debug("");
455  }
456  rs.close();
457  stmt.close();
458  } catch (Exception e) {
459  String err = "error trying to read from mapd_views, error was " + e.getMessage();
460  MAPDLOGGER.error(err);
461  throw new RuntimeException(err);
462  }
463  return (viewFlag == 1);
464  }
465 
466  private String getViewSql(String tableName) {
467  String sqlText;
468  if (mapdPort == -1) {
469  // use sql
471  sqlText = getViewSqlViaSql(getTableId(tableName));
473  } else {
474  // use thrift direct to local server
475  try {
476  TProtocol protocol = null;
477 
478  TTransport transport =
479  sock_transport_properties.openClientTransport("localhost", mapdPort);
480  if (!transport.isOpen()) transport.open();
481  protocol = new TBinaryProtocol(transport);
482 
483  MapD.Client client = new MapD.Client(protocol);
484 
485  TTableDetails td = client.get_table_details(currentUser.getSession(), tableName);
486 
487  transport.close();
488 
489  sqlText = td.getView_sql();
490 
491  } catch (TTransportException ex) {
492  MAPDLOGGER.error(ex.toString());
493  throw new RuntimeException(ex.toString());
494  } catch (TMapDException ex) {
495  MAPDLOGGER.error(ex.toString());
496  throw new RuntimeException(ex.toString());
497  } catch (TException ex) {
498  MAPDLOGGER.error(ex.toString());
499  throw new RuntimeException(ex.toString());
500  }
501  }
502  /* return string without the sqlite's trailing semicolon */
503  if (sqlText.charAt(sqlText.length() - 1) == ';') {
504  return (sqlText.substring(0, sqlText.length() - 1));
505  } else {
506  return (sqlText);
507  }
508  }
509 
510  // we assume there is already a DB connection here
511  private String getViewSqlViaSql(int tableId) {
512  Statement stmt;
513  ResultSet rs;
514  String sqlText = "";
515  try {
516  stmt = catConn.createStatement();
517  rs = stmt.executeQuery(String.format(
518  "SELECT sql FROM mapd_views where tableid = '%s' COLLATE NOCASE;",
519  tableId));
520  while (rs.next()) {
521  sqlText = rs.getString("sql");
522  MAPDLOGGER.debug("View definition = " + sqlText);
523  MAPDLOGGER.debug("");
524  }
525  rs.close();
526  stmt.close();
527  } catch (Exception e) {
528  String err = "error trying to read from mapd_views, error was " + e.getMessage();
529  MAPDLOGGER.error(err);
530  throw new RuntimeException(err);
531  }
532  if (sqlText == null || sqlText.length() == 0) {
533  String err = "No view text found";
534  MAPDLOGGER.error(err);
535  throw new RuntimeException(err);
536  }
537  return sqlText;
538  }
539 
540  private TDatumType typeToThrift(int type) {
541  switch (type) {
542  case KBOOLEAN:
543  return TDatumType.BOOL;
544  case KTINYINT:
545  return TDatumType.TINYINT;
546  case KSMALLINT:
547  return TDatumType.SMALLINT;
548  case KINT:
549  return TDatumType.INT;
550  case KBIGINT:
551  return TDatumType.BIGINT;
552  case KFLOAT:
553  return TDatumType.FLOAT;
554  case KNUMERIC:
555  case KDECIMAL:
556  return TDatumType.DECIMAL;
557  case KDOUBLE:
558  return TDatumType.DOUBLE;
559  case KTEXT:
560  case KVARCHAR:
561  case KCHAR:
562  return TDatumType.STR;
563  case KTIME:
564  return TDatumType.TIME;
565  case KTIMESTAMP:
566  return TDatumType.TIMESTAMP;
567  case KDATE:
568  return TDatumType.DATE;
569  case KINTERVAL_DAY_TIME:
570  return TDatumType.INTERVAL_DAY_TIME;
572  return TDatumType.INTERVAL_YEAR_MONTH;
573  case KPOINT:
574  return TDatumType.POINT;
575  case KLINESTRING:
576  return TDatumType.LINESTRING;
577  case KPOLYGON:
578  return TDatumType.POLYGON;
579  case KMULTIPOLYGON:
580  return TDatumType.MULTIPOLYGON;
581  default:
582  return null;
583  }
584  }
585 
586  public void updateMetaData(String schema, String table) {
587  // Check if table is specified, if not we are dropping an entire DB so need to
588  // remove all
589  // tables for that DB
590  if (table.equals("")) {
591  // Drop db and all tables
592  // iterate through all and remove matching schema
593  Set<List<String>> all = new HashSet<>(MAPD_TABLE_DETAILS.keySet());
594  for (List<String> keys : all) {
595  if (keys.get(0).equals(schema.toUpperCase())) {
596  MAPDLOGGER.debug("removing schema " + keys.get(0) + " table " + keys.get(1));
597  MAPD_TABLE_DETAILS.remove(keys);
598  }
599  }
600  } else {
601  MAPDLOGGER.debug("removing schema " + schema.toUpperCase() + " table "
602  + table.toUpperCase());
603  MAPD_TABLE_DETAILS.remove(
604  ImmutableList.of(schema.toUpperCase(), table.toUpperCase()));
605  }
606  // Invalidate views
607  Set<List<String>> all = new HashSet<>(MAPD_TABLE_DETAILS.keySet());
608  for (List<String> keys : all) {
609  if (keys.get(0).equals(schema.toUpperCase())) {
610  Table ttable = MAPD_TABLE_DETAILS.get(keys);
611  if (ttable instanceof MapDView) {
612  MAPDLOGGER.debug(
613  "removing view in schema " + keys.get(0) + " view " + keys.get(1));
614  MAPD_TABLE_DETAILS.remove(keys);
615  }
616  }
617  }
618  // now remove schema
619  MAPDLOGGER.debug("removing schema " + schema.toUpperCase());
620  MAPD_DATABASE_TO_TABLES.remove(schema.toUpperCase());
621  }
622 }
Table getTable(String tableName)
static final int get_physical_cols(int type)
MetaConnect(int mapdPort, String dataDir, MapDUser currentMapDUser, MapDParser parser, SockTransportProperties skT)
static final int KMULTIPOLYGON
void updateMetaData(String schema, String table)
TTransport openClientTransport(String server_host, int port)
static volatile Map< List< String >, Table > MAPD_TABLE_DETAILS
String getViewSql(String tableName)
static final boolean is_geometry(int type)
String getViewSqlViaSql(int tableId)
TTableDetails get_table_details(String tableName)
static final int KINTERVAL_YEAR_MONTH
static final Logger MAPDLOGGER
final SockTransportProperties sock_transport_properties
mapd::shared_ptr< MapDClient > client
static final int KLINESTRING
TTableDetails get_table_detail_SQL(String tableName)
TDatumType typeToThrift(int type)
int getTableId(String tableName)
static final int KINTERVAL_DAY_TIME
boolean isView(String tableName)
static volatile Map< String, Set< String > > MAPD_DATABASE_TO_TABLES