PreparedStatement getStatement(String sql, Map<String, PreparedStatement> statementCache)
throws SQLException {
PreparedStatement pst = null;
if (statementCache != null) {
if (statementCache.containsKey(sql)) {
pst = statementCache.get(sql);
} else {
pst = jcbcStorageClientConnection.getConnection().prepareStatement(sql);
inc("cachedStatement");
statementCache.put(sql, pst);
}
} else {
pst = jcbcStorageClientConnection.getConnection().prepareStatement(sql);
}
return pst;
}
private boolean startBlock() throws SQLException {
Connection connection = jcbcStorageClientConnection.getConnection();
boolean autoCommit = connection.getAutoCommit();
connection.setAutoCommit(false);
if (storageClientListener != null) {
storageClientListener.begin();
}
return autoCommit;
}
private void endBlock(boolean autoCommit) throws SQLException {
if (autoCommit) {
Connection connection = jcbcStorageClientConnection.getConnection();
connection.commit();
connection.setAutoCommit(autoCommit);
if (storageClientListener != null) {
storageClientListener.commit();
}
}
}
public void close() {
if (closed == null) {
try {
closed = new Exception("Connection Closed Traceback");
shutdownConnection();
jcbcStorageClientConnection.releaseClient(this);
} catch (Throwable t) {
LOGGER.error("Failed to close connection ", t);
}
}
}
private void abandonBlock(boolean autoCommit) {
if (autoCommit) {
try {
Connection connection = jcbcStorageClientConnection.getConnection();
connection.rollback();
connection.setAutoCommit(autoCommit);
if (storageClientListener != null) {
storageClientListener.rollback();
}
} catch (SQLException e) {
LOGGER.warn(e.getMessage(), e);
}
}
}
public boolean validate() throws StorageClientException {
checkClosed();
Statement statement = null;
try {
statement = jcbcStorageClientConnection.getConnection().createStatement();
inc("vaidate");
statement.execute(getSql(SQL_VALIDATE));
return true;
} catch (SQLException e) {
LOGGER.warn("Failed to validate connection ", e);
return false;
} finally {
try {
statement.close();
dec("vaidate");
} catch (Throwable e) {
LOGGER.debug("Failed to close statement in validate ", e);
}
}
}
/**
* Get a prepared statement, potentially optimized and sharded.
*
* @param keySpace
* @param columnFamily
* @param sqlSelectStringRow
* @param rid
* @param statementCache
* @return
* @throws SQLException
*/
PreparedStatement getStatement(
String keySpace,
String columnFamily,
String sqlSelectStringRow,
String rid,
Map<String, PreparedStatement> statementCache)
throws SQLException {
String shard = rid.substring(0, 1);
String[] keys =
new String[] {
sqlSelectStringRow + "." + keySpace + "." + columnFamily + "._" + shard,
sqlSelectStringRow + "." + columnFamily + "._" + shard,
sqlSelectStringRow + "." + keySpace + "._" + shard,
sqlSelectStringRow + "._" + shard,
sqlSelectStringRow + "." + keySpace + "." + columnFamily,
sqlSelectStringRow + "." + columnFamily,
sqlSelectStringRow + "." + keySpace,
sqlSelectStringRow
};
for (String k : keys) {
if (sqlConfig.containsKey(k)) {
LOGGER.debug("Using Statement {} ", sqlConfig.get(k));
if (statementCache != null && statementCache.containsKey(k)) {
return statementCache.get(k);
} else {
PreparedStatement pst =
jcbcStorageClientConnection
.getConnection()
.prepareStatement((String) sqlConfig.get(k));
if (statementCache != null) {
inc("cachedStatement");
statementCache.put(k, pst);
}
return pst;
}
}
}
return null;
}
public DisposableIterator<SparseRow> listAll(String keySpace, final String columnFamily)
throws StorageClientException {
String[] keys =
new String[] {
"list-all." + keySpace + "." + columnFamily, "list-all." + columnFamily, "list-all"
};
String sql = null;
for (String statementKey : keys) {
sql = getSql(statementKey);
if (sql != null) {
break;
}
}
if (sql == null) {
throw new StorageClientException(
"Cant find sql statement for one of " + Arrays.toString(keys));
}
PreparedStatement tpst = null;
ResultSet trs = null;
try {
LOGGER.debug("Preparing {} ", sql);
tpst = jcbcStorageClientConnection.getConnection().prepareStatement(sql);
inc("iterator");
tpst.clearParameters();
long qtime = System.currentTimeMillis();
trs = tpst.executeQuery();
qtime = System.currentTimeMillis() - qtime;
if (qtime > slowQueryThreshold && qtime < verySlowQueryThreshold) {
SQL_LOGGER.warn("Slow Query {}ms {} params:[{}]", new Object[] {qtime, sql});
} else if (qtime > verySlowQueryThreshold) {
SQL_LOGGER.error("Very Slow Query {}ms {} params:[{}]", new Object[] {qtime, sql});
}
inc("iterator r");
LOGGER.debug("Executed ");
// pass control to the iterator.
final PreparedStatement pst = tpst;
final ResultSet rs = trs;
tpst = null;
trs = null;
return registerDisposable(
new PreemptiveIterator<SparseRow>() {
private SparseRow nextValue = null;
private boolean open = true;
@Override
protected SparseRow internalNext() {
return nextValue;
}
@Override
protected boolean internalHasNext() {
try {
while (open && rs.next()) {
try {
Map<String, Object> values = Maps.newHashMap();
String rid = rs.getString(1);
Types.loadFromStream(rid, values, rs.getBinaryStream(2), columnFamily);
nextValue = new SparseMapRow(rid, values);
return true;
} catch (IOException e) {
LOGGER.error(e.getMessage(), e);
nextValue = null;
}
}
close();
nextValue = null;
LOGGER.debug("End of Set ");
return false;
} catch (SQLException e) {
LOGGER.error(e.getMessage(), e);
close();
nextValue = null;
return false;
}
}
@Override
public void close() {
if (open) {
open = false;
try {
if (rs != null) {
rs.close();
dec("iterator r");
}
} catch (SQLException e) {
LOGGER.warn(e.getMessage(), e);
}
try {
if (pst != null) {
pst.close();
dec("iterator");
}
} catch (SQLException e) {
LOGGER.warn(e.getMessage(), e);
}
super.close();
}
}
});
} catch (SQLException e) {
LOGGER.error(e.getMessage(), e);
throw new StorageClientException(e.getMessage() + " SQL Statement was " + sql, e);
} finally {
// trs and tpst will only be non null if control has not been passed
// to the iterator.
try {
if (trs != null) {
trs.close();
dec("iterator r");
}
} catch (SQLException e) {
LOGGER.warn(e.getMessage(), e);
}
try {
if (tpst != null) {
tpst.close();
dec("iterator");
}
} catch (SQLException e) {
LOGGER.warn(e.getMessage(), e);
}
}
}
protected Connection getConnection() throws StorageClientException, SQLException {
checkClosed();
return jcbcStorageClientConnection.getConnection();
}
public void checkSchema(String[] clientConfigLocations)
throws ClientPoolException, StorageClientException {
checkClosed();
Statement statement = null;
try {
statement = jcbcStorageClientConnection.getConnection().createStatement();
try {
statement.execute(getSql(SQL_CHECKSCHEMA));
inc("schema");
LOGGER.info("Schema Exists");
return;
} catch (SQLException e) {
LOGGER.info("Schema does not exist {}", e.getMessage());
}
for (String clientSQLLocation : clientConfigLocations) {
String clientDDL = clientSQLLocation + ".ddl";
InputStream in = this.getClass().getClassLoader().getResourceAsStream(clientDDL);
if (in != null) {
try {
BufferedReader br = new BufferedReader(new InputStreamReader(in, "UTF8"));
int lineNo = 1;
String line = br.readLine();
StringBuilder sqlStatement = new StringBuilder();
while (line != null) {
line = StringUtils.stripEnd(line, null);
if (!line.isEmpty()) {
if (line.startsWith(SQL_COMMENT)) {
LOGGER.info("Comment {} ", line);
} else if (line.endsWith(SQL_EOL)) {
sqlStatement.append(line.substring(0, line.length() - 1));
String ddl = sqlStatement.toString();
try {
statement.executeUpdate(ddl);
LOGGER.info("SQL OK {}:{} {} ", new Object[] {clientDDL, lineNo, ddl});
} catch (SQLException e) {
LOGGER.warn(
"SQL ERROR {}:{} {} {} ",
new Object[] {clientDDL, lineNo, ddl, e.getMessage()});
}
sqlStatement = new StringBuilder();
} else {
sqlStatement.append(line);
}
}
line = br.readLine();
lineNo++;
}
br.close();
LOGGER.info("Schema Created from {} ", clientDDL);
break;
} catch (Throwable e) {
LOGGER.error("Failed to load Schema from {}", clientDDL, e);
} finally {
try {
in.close();
} catch (IOException e) {
LOGGER.error("Failed to close stream from {}", clientDDL, e);
}
}
} else {
LOGGER.info("No Schema found at {} ", clientDDL);
}
}
} catch (SQLException e) {
LOGGER.info("Failed to create schema ", e);
throw new ClientPoolException("Failed to create schema ", e);
} finally {
try {
statement.close();
dec("schema");
} catch (Throwable e) {
LOGGER.debug("Failed to close statement in validate ", e);
}
}
}
public long allCount(String keySpace, String columnFamily) throws StorageClientException {
String[] keys =
new String[] {
"list-all-count." + keySpace + "." + columnFamily,
"list-all-count." + columnFamily,
"list-all-count"
};
String sql = null;
for (String statementKey : keys) {
sql = getSql(statementKey);
if (sql != null) {
break;
}
}
if (sql == null) {
throw new StorageClientException(
"Cant find sql statement for one of " + Arrays.toString(keys));
}
PreparedStatement tpst = null;
ResultSet trs = null;
try {
LOGGER.debug("Preparing {} ", sql);
tpst = jcbcStorageClientConnection.getConnection().prepareStatement(sql);
inc("iterator");
tpst.clearParameters();
long qtime = System.currentTimeMillis();
trs = tpst.executeQuery();
qtime = System.currentTimeMillis() - qtime;
if (qtime > slowQueryThreshold && qtime < verySlowQueryThreshold) {
SQL_LOGGER.warn("Slow Query {}ms {} params:[{}]", new Object[] {qtime, sql});
} else if (qtime > verySlowQueryThreshold) {
SQL_LOGGER.error("Very Slow Query {}ms {} params:[{}]", new Object[] {qtime, sql});
}
inc("iterator r");
LOGGER.debug("Executed ");
if (trs.next()) {
return trs.getLong(1);
}
return 0;
} catch (SQLException e) {
LOGGER.error(e.getMessage(), e);
throw new StorageClientException(e.getMessage() + " SQL Statement was " + sql, e);
} finally {
try {
if (trs != null) {
trs.close();
dec("iterator r");
}
} catch (SQLException e) {
LOGGER.warn(e.getMessage(), e);
}
try {
if (tpst != null) {
tpst.close();
dec("iterator");
}
} catch (SQLException e) {
LOGGER.warn(e.getMessage(), e);
}
}
}
public Map<String, String> syncIndexColumns() throws StorageClientException, SQLException {
checkClosed();
String selectColumns = getSql(SQL_INDEX_COLUMN_NAME_SELECT);
String insertColumns = getSql(SQL_INDEX_COLUMN_NAME_INSERT);
String updateTable = getSql("alter-widestring-table");
String updateIndexes = getSql("index-widestring-table");
if (selectColumns == null || insertColumns == null) {
LOGGER.warn("Using Key Value Pair Tables for indexing ");
LOGGER.warn(" This will cause scalability problems eventually, please see KERN-1957 ");
LOGGER.warn(" To fix, port your SQL Configuration file to use a wide index table. ");
return null; // no wide column support in this JDBC config.
}
PreparedStatement selectColumnsPst = null;
PreparedStatement insertColumnsPst = null;
ResultSet rs = null;
Connection connection = jcbcStorageClientConnection.getConnection();
Statement statement = null;
try {
selectColumnsPst = connection.prepareStatement(selectColumns);
insertColumnsPst = connection.prepareStatement(insertColumns);
statement = connection.createStatement();
rs = selectColumnsPst.executeQuery();
Map<String, String> cnames = Maps.newHashMap();
Set<String> usedColumns = Sets.newHashSet();
while (rs.next()) {
String columnFamily = rs.getString(1);
String column = rs.getString(2);
String columnName = rs.getString(3);
cnames.put(columnFamily + ":" + column, columnName);
usedColumns.add(columnFamily + ":" + columnName);
}
// maxCols contiains the max col number for each cf.
// cnames contains a map of column Families each containing a map of columns with numbers.
for (String k : Sets.union(indexColumns, AUTO_INDEX_COLUMNS)) {
String[] cf = StringUtils.split(k, ":", 2);
if (!cnames.containsKey(k)) {
String cv = makeNameSafeSQL(cf[1], sqlNamePadding, maxNameLength);
if (usedColumns.contains(cf[0] + ":" + cv)) {
LOGGER.info(
"Column already exists, please provide explicit mapping indexing {} already used column {} ",
k,
cv);
throw new StorageClientException(
"Column already exists, please provide explicit mapping indexing ["
+ k
+ "] already used column ["
+ cv
+ "]");
}
insertColumnsPst.clearParameters();
insertColumnsPst.setString(1, cf[0]);
insertColumnsPst.setString(2, cf[1]);
insertColumnsPst.setString(3, cv);
insertColumnsPst.executeUpdate();
cnames.put(k, cv);
usedColumns.add(cf[0] + ":" + cv);
try {
statement.executeUpdate(MessageFormat.format(updateTable, cf[0], cv));
LOGGER.info(
"Added Index Column OK {} Table:{} Column:{} ", new Object[] {k, cf[0], cv});
} catch (SQLException e) {
LOGGER.warn(
"Added Index Column Error {} Table:{} Column:{} Cause:{} ",
new Object[] {k, cf[0], cv, e.getMessage()});
LOGGER.warn("SQL is {} ", MessageFormat.format(updateTable, cf[0], cv));
throw new StorageClientException(e.getMessage(), e);
}
try {
statement.executeUpdate(MessageFormat.format(updateIndexes, cf[0], cv));
LOGGER.info(
"Added Index Column OK {} Table:{} Column:{} ", new Object[] {k, cf[0], cv});
} catch (SQLException e) {
LOGGER.warn(
"Added Index Column Error {} Table:{} Column:{} Cause:{} ",
new Object[] {k, cf[0], cv, e.getMessage()});
LOGGER.warn("SQL is {} ", MessageFormat.format(updateIndexes, cf[0], cv));
throw new StorageClientException(e.getMessage(), e);
}
}
}
// sync done, now create a quick lookup table to extract the storage column for any column
// name,
Builder<String, String> b = ImmutableMap.builder();
for (Entry<String, String> e : cnames.entrySet()) {
b.put(e.getKey(), e.getValue());
LOGGER.info("Column Config {} maps to {} ", e.getKey(), e.getValue());
}
return b.build();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
LOGGER.debug(e.getMessage(), e);
}
}
if (selectColumnsPst != null) {
try {
selectColumnsPst.close();
} catch (SQLException e) {
LOGGER.debug(e.getMessage(), e);
}
}
if (insertColumnsPst != null) {
try {
insertColumnsPst.close();
} catch (SQLException e) {
LOGGER.debug(e.getMessage(), e);
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
LOGGER.debug(e.getMessage(), e);
}
}
}
}