Java tutorial
/* * Copyright 2012 - 2016 Splice Machine, Inc. * * Licensed under the Apache License, Version 2.0 (the "License"); you may not use * this file except in compliance with the License. You may obtain a copy of the * License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, software distributed * under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR * CONDITIONS OF ANY KIND, either express or implied. See the License for the * specific language governing permissions and limitations under the License. */ package com.splicemachine.mrio.api.core; import java.io.IOException; import java.io.ObjectInput; import java.io.ObjectOutput; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; import java.util.List; import com.splicemachine.derby.stream.output.WriteReadUtils; import org.apache.commons.lang.SerializationUtils; import org.apache.log4j.Logger; import com.splicemachine.access.configuration.SQLConfiguration; import com.splicemachine.db.iapi.error.StandardException; import com.splicemachine.db.iapi.services.io.FormatableBitSet; import com.splicemachine.db.iapi.sql.Activation; import com.splicemachine.db.iapi.sql.execute.ExecRow; import com.splicemachine.db.impl.sql.execute.ValueRow; import com.splicemachine.derby.impl.SpliceSpark; import com.splicemachine.derby.impl.sql.execute.LazyDataValueFactory; import com.splicemachine.derby.impl.sql.execute.operations.scanner.TableScannerBuilder; import com.splicemachine.derby.stream.ActivationHolder; import com.splicemachine.derby.stream.iapi.DataSet; import com.splicemachine.derby.stream.iapi.ScanSetBuilder; import com.splicemachine.derby.utils.SpliceAdmin; import com.splicemachine.si.api.txn.Txn; import com.splicemachine.si.api.txn.Txn.IsolationLevel; import com.splicemachine.si.api.txn.TxnView; import com.splicemachine.si.data.HExceptionFactory; import com.splicemachine.si.impl.HOperationFactory; import com.splicemachine.si.impl.SimpleTxnOperationFactory; import com.splicemachine.si.impl.txn.ReadOnlyTxn; import com.splicemachine.storage.DataScan; import com.splicemachine.utils.IntArrays; import com.splicemachine.utils.SpliceLogUtils; public class SMSQLUtil { static final Logger LOG = Logger.getLogger(SMSQLUtil.class); private Connection connect = null; private static SMSQLUtil sqlUtil = null; private String connStr = null; private SMSQLUtil(String connStr) throws Exception { this.connStr = connStr; connect = createConn(); } public static SMSQLUtil getInstance(String connStr) { if (sqlUtil == null) { try { sqlUtil = new SMSQLUtil(connStr); } catch (Exception e) { throw new RuntimeException(e); } } return sqlUtil; } public Connection createConn() throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException { Class.forName(SQLConfiguration.SPLICE_JDBC_DRIVER).newInstance(); return DriverManager.getConnection(connStr); } public void disableAutoCommit(Connection conn) throws SQLException { conn.setAutoCommit(false); } public void commit(Connection conn) throws SQLException { conn.commit(); } public void rollback(Connection conn) throws SQLException { conn.rollback(); } /** * Get primary key from 'tableName' * Return Column Name list : Column Seq list * Column Id here refers to Column Seq, where Id=1 means the first column in primary keys. * @throws SQLException * **/ public List<PKColumnNamePosition> getPrimaryKeys(String tableName) throws SQLException { if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getPrimaryKeys tableName=%s", tableName); ArrayList<PKColumnNamePosition> pkCols = new ArrayList<PKColumnNamePosition>(1); String[] schemaTable = parseTableName(tableName); ResultSet result = null; try { DatabaseMetaData databaseMetaData = connect.getMetaData(); result = databaseMetaData.getPrimaryKeys(null, schemaTable[0], schemaTable[1]); while (result.next()) { // Convert 1-based column index to 0-based index pkCols.add(new PKColumnNamePosition(result.getString(4), result.getInt(5) - 1)); } } finally { if (result != null) result.close(); } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getPrimaryKeys returns=%s", Arrays.toString(pkCols.toArray())); return pkCols.size() != 0 ? pkCols : null; } /** * * Get table structure of 'tableName' * Return Column Name list : Column Type list * @throws SQLException * * */ public List<NameType> getTableStructure(String tableName) throws SQLException { if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getTableStructure tableName=%s", tableName); List<NameType> colType = new ArrayList<NameType>(); ResultSet result = null; try { String[] schemaTableName = parseTableName(tableName); DatabaseMetaData databaseMetaData = connect.getMetaData(); result = databaseMetaData.getColumns(null, schemaTableName[0], schemaTableName[1], null); while (result.next()) { colType.add(new NameType(result.getString(4), result.getInt(5))); } } finally { if (result != null) result.close(); } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getTableStructure returns=%s", Arrays.toString(colType.toArray())); return colType; } public Connection getStaticConnection() { return this.connect; } /** * * Get ConglomID from 'tableName' * Param is Splice tableName with schema, pattern: schema.tableName * Return ConglomID * ConglomID means HBase table Name which maps to the Splice table Name * @throws SQLException * * */ public String getConglomID(String tableName) throws SQLException { String[] schemaTableName = parseTableName(tableName); long[] conglomIds = SpliceAdmin.getConglomNumbers(connect, schemaTableName[0], schemaTableName[1]); StringBuffer str = new StringBuffer(); str.append(conglomIds[0]); return str.toString(); } /** * * Get TransactionID * Each Transaction has a uniq ID * For every map job, there should be a different transactionID. * * */ public String getTransactionID() throws SQLException { String trxId = ""; ResultSet resultSet = null; try { resultSet = connect.createStatement().executeQuery("call SYSCS_UTIL.SYSCS_GET_CURRENT_TRANSACTION()"); while (resultSet.next()) { trxId = String.valueOf(resultSet.getLong(1)); } } finally { if (resultSet != null) resultSet.close(); } return trxId; } public String getTransactionID(Connection conn) throws SQLException { String trxId = null; ResultSet resultSet = null; try { resultSet = conn.createStatement().executeQuery("call SYSCS_UTIL.SYSCS_GET_CURRENT_TRANSACTION()"); while (resultSet.next()) { long txnID = resultSet.getLong(1); trxId = String.valueOf(txnID); } } finally { if (resultSet != null) resultSet.close(); } return trxId; } public long getChildTransactionID(Connection conn, long parentTxsID, String tableName) throws SQLException { PreparedStatement ps = null; ResultSet rs = null; long childTxsID; try { ps = conn.prepareStatement("call SYSCS_UTIL.SYSCS_START_CHILD_TRANSACTION(?,?)"); ps.setLong(1, parentTxsID); ps.setString(2, tableName); ResultSet rs3 = ps.executeQuery(); rs3.next(); childTxsID = rs3.getLong(1); } finally { if (rs != null) rs.close(); if (ps != null) ps.close(); } return childTxsID; } public void commitChildTransaction(Connection conn, long childTxnID) throws SQLException { PreparedStatement ps = conn.prepareStatement("call SYSCS_UTIL.SYSCS_COMMIT_CHILD_TRANSACTION(?)"); ps.setLong(1, childTxnID); ps.execute(); } public void closeConn(Connection conn) throws SQLException { conn.close(); } public void closeQuietly() { try { if (connect != null) { connect.close(); } } catch (Exception e) { e.printStackTrace(); } } public boolean checkTableExists(String tableName) throws SQLException { ResultSet rs = null; try { DatabaseMetaData meta = connect.getMetaData(); String[] schemaTableName = parseTableName(tableName); rs = meta.getTables(null, schemaTableName[0], schemaTableName[1], new String[] { "TABLE" }); while (rs.next()) { return true; } return false; } finally { if (rs != null) rs.close(); } } private String[] parseTableName(String str) throws SQLException { str = str.toUpperCase(); if (str == null || str.trim().equals("")) return null; else { String[] tmp = str.split("\\."); if (tmp.length == 2) { return tmp; } if (tmp.length == 1) { String[] s = new String[2]; s[1] = tmp[0]; s[0] = null; return s; } throw new SQLException("Splice table not known, " + "please specify Splice tableName. " + "pattern: schemaName.tableName"); } } public int[] getRowDecodingMap(List<NameType> nameTypes, List<PKColumnNamePosition> primaryKeys, List<String> columnNames) { if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getRowDecodingMap nameTypes=%s, primaryKeys=%s, columnNames=%s", nameTypes, primaryKeys, columnNames); int[] rowDecodingMap = IntArrays.count(nameTypes.size()); for (int i = 0; i < nameTypes.size(); i++) { NameType nameType = nameTypes.get(i); if (!isPrimaryKeyColumn(primaryKeys, nameType.getName()) && (columnNames.contains(nameType.getName()))) rowDecodingMap[i] = columnNames.indexOf(nameType.getName()); else rowDecodingMap[i] = -1; } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getRowDecodingMap returns=%s", Arrays.toString(rowDecodingMap)); return rowDecodingMap; } private boolean isPrimaryKeyColumn(List<PKColumnNamePosition> primaryKeys, String name) { boolean isPkCol = false; if (primaryKeys != null && primaryKeys.size() > 0) { for (PKColumnNamePosition namePosition : primaryKeys) { if (namePosition.getName().compareToIgnoreCase(name) == 0) { isPkCol = true; break; } } } return isPkCol; } public int[] getKeyColumnEncodingOrder(List<NameType> nameTypes, List<PKColumnNamePosition> primaryKeys) { int[] keyColumnEncodingOrder = new int[0]; if (primaryKeys != null && primaryKeys.size() > 0) { keyColumnEncodingOrder = IntArrays.count(primaryKeys.size()); for (int i = 0; i < primaryKeys.size(); i++) { keyColumnEncodingOrder[primaryKeys.get(i).getPosition()] = locationInNameTypes(nameTypes, primaryKeys.get(i).getName()); } } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getKeyColumnEncodingOrder returns=%s", Arrays.toString(keyColumnEncodingOrder)); return keyColumnEncodingOrder; } public int[] getKeyDecodingMap(int[] keyColumnEncodingOrder, List<String> columnNames, List<NameType> nameTypes) { int[] keyDecodingMap = IntArrays.count(keyColumnEncodingOrder.length); for (int i = 0; i < keyColumnEncodingOrder.length; i++) { keyDecodingMap[i] = columnNames.indexOf(nameTypes.get(keyColumnEncodingOrder[i]).name); } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getKeyDecodingMap returns=%s", Arrays.toString(keyDecodingMap)); return keyDecodingMap; } public int[] getKeyColumnTypes(int[] keyColumnEncodingOrder, List<NameType> nameTypes) throws StandardException { int[] keyColumnTypes = IntArrays.count(keyColumnEncodingOrder.length); for (int i = 0; i < keyColumnEncodingOrder.length; i++) { keyColumnTypes[i] = nameTypes.get(keyColumnEncodingOrder[i]).getTypeFormatId(); } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getKeyColumnTypes returns=%s", Arrays.toString(keyColumnTypes)); return keyColumnTypes; } public int[] getExecRowFormatIds(List<String> columnNames, List<NameType> nameTypes) throws StandardException { int[] execRowFormatIds = new int[columnNames == null ? nameTypes.size() : columnNames.size()]; if (columnNames != null) { for (int i = 0; i < columnNames.size(); i++) { execRowFormatIds[i] = nameTypes.get(locationInNameTypes(nameTypes, columnNames.get(i))) .getTypeFormatId(); } } else { for (int i = 0; i < nameTypes.size(); i++) { execRowFormatIds[i] = nameTypes.get(i).getTypeFormatId(); } } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getExecRowFormatIds returns=%s", execRowFormatIds); return execRowFormatIds; } public static ExecRow getExecRow(int[] execRowFormatIds) throws IOException { ExecRow execRow = new ValueRow(execRowFormatIds == null ? 0 : execRowFormatIds.length); try { for (int i = 0; i < execRow.nColumns(); i++) { execRow.setColumn(i + 1, LazyDataValueFactory.getLazyNull(execRowFormatIds[i])); } } catch (StandardException se) { throw new IOException(se); } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getExecRow returns=%s", execRow); return execRow; } public FormatableBitSet getAccessedKeyColumns(int[] keyColumnEncodingOrder, int[] keyDecodingMap) { FormatableBitSet accessedKeyColumns = new FormatableBitSet(keyColumnEncodingOrder.length); for (int i = 0; i < keyColumnEncodingOrder.length; i++) { accessedKeyColumns.set(i); } if (LOG.isTraceEnabled()) SpliceLogUtils.trace(LOG, "getAccessedKeyColumns returns=%s", accessedKeyColumns); return accessedKeyColumns; } private int locationInNameTypes(List<NameType> nameTypes, String name) { for (int i = 0; i < nameTypes.size(); i++) { if (nameTypes.get(i).name.equals(name)) return i; } throw new RuntimeException("misssing element"); } public ScanSetBuilder getTableScannerBuilder(String tableName, List<String> columnNames) throws SQLException { List<PKColumnNamePosition> primaryKeys = getPrimaryKeys(tableName); List<NameType> nameTypes = getTableStructure(tableName); if (columnNames == null) { columnNames = new ArrayList<>(nameTypes.size()); for (int i = 0; i < nameTypes.size(); i++) { columnNames.add(nameTypes.get(i).name); } } int[] rowDecodingMap = getRowDecodingMap(nameTypes, primaryKeys, columnNames); int[] keyColumnEncodingOrder = getKeyColumnEncodingOrder(nameTypes, primaryKeys); boolean[] keyColumnOrdering = new boolean[keyColumnEncodingOrder.length]; for (int i = 0; i < keyColumnEncodingOrder.length; i++) { keyColumnOrdering[i] = true; } int[] keyDecodingMap = getKeyDecodingMap(keyColumnEncodingOrder, columnNames, nameTypes); int[] keyColumnTypes; int[] execRowFormatIds; try { execRowFormatIds = getExecRowFormatIds(columnNames, nameTypes); keyColumnTypes = getKeyColumnTypes(keyColumnEncodingOrder, nameTypes); } catch (StandardException e) { throw new SQLException(e); } FormatableBitSet accessedKeyColumns = getAccessedKeyColumns(keyColumnEncodingOrder, keyDecodingMap); Txn txn = ReadOnlyTxn.create(Long.parseLong(getTransactionID()), IsolationLevel.SNAPSHOT_ISOLATION, null, HExceptionFactory.INSTANCE); TableScannerBuilder tableScannerBuilder = new SMTableBuilder(); ExecRow template = WriteReadUtils.getExecRowFromTypeFormatIds(execRowFormatIds); return tableScannerBuilder.transaction(txn).scan(createNewScan()).template(template).tableVersion("2.0") .indexName(null).keyColumnEncodingOrder(keyColumnEncodingOrder).keyColumnSortOrder(null) .keyColumnTypes(keyColumnTypes).accessedKeyColumns(accessedKeyColumns) .keyDecodingMap(keyDecodingMap).keyColumnSortOrder(keyColumnOrdering) .rowDecodingMap(rowDecodingMap); } private static class SMTableBuilder extends TableScannerBuilder { public SMTableBuilder() { } @Override public DataSet buildDataSet() throws StandardException { throw new UnsupportedOperationException("We do not build data sets in this context."); } @Override protected DataScan readScan(ObjectInput in) throws IOException { return HOperationFactory.INSTANCE.readScan(in); } @Override public TxnView readTxn(ObjectInput oi) throws IOException { return new SimpleTxnOperationFactory(HExceptionFactory.INSTANCE, HOperationFactory.INSTANCE) .readTxn(oi); } @Override protected void writeScan(ObjectOutput out) throws IOException { HOperationFactory.INSTANCE.writeScan(scan, out); } @Override protected void writeTxn(ObjectOutput out) throws IOException { new SimpleTxnOperationFactory(HExceptionFactory.INSTANCE, HOperationFactory.INSTANCE).writeTxn(txn, out); } } public void close() throws SQLException { if (connect != null) connect.close(); } public static DataScan createNewScan() { DataScan scan = HOperationFactory.INSTANCE.newScan(); scan.returnAllVersions(); return scan; } public Activation getActivation(String sql, TxnView txnView) throws SQLException, StandardException { PreparedStatement ps = connect.prepareStatement("call syscs_util.get_activation(?)"); ps.setString(1, sql); ResultSet rs = ps.executeQuery(); rs.next(); byte[] activationHolderBytes = rs.getBytes(1); try { SpliceSpark.setupSpliceStaticComponents(); } catch (IOException ioe) { StandardException.plainWrapException(ioe); } ActivationHolder ah = (ActivationHolder) SerializationUtils.deserialize(activationHolderBytes); ah.init(txnView); return ah.getActivation(); } }