public void provideAllPrivToAll(Connection dConn, Connection gConn) {
ArrayList<SQLException> exList = new ArrayList<SQLException>();
for (int i = 0; i < tableNames.length; i++) {
StringBuffer sql = new StringBuffer();
sql.append("grant all privileges on " + tableNames[i] + " to " + getAllGrantees());
Log.getLogWriter().info("security statement is " + sql.toString());
if (dConn != null) {
try {
Statement stmt = dConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
dConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleDerbySQLException(se, exList);
}
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
gConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleGFGFXDException(se, exList);
}
} else {
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
gConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
}
}
}
protected synchronized String getProcedureName(Connection dConn, Connection gConn) {
/// avoid #42569
ResultSet rs = null;
try {
rs = gConn.getMetaData().getProcedures(null, null, null);
List procNames = ResultSetHelper.asList(rs, false);
Log.getLogWriter().info("procedure names are " + ResultSetHelper.listToString(procNames));
rs.close();
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
try {
rs = gConn.getMetaData().getFunctions(null, null, null);
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
List funcNames = ResultSetHelper.asList(rs, false);
Log.getLogWriter().info("function names are " + ResultSetHelper.listToString(funcNames));
if (procedureNames == null) {
ArrayList<String> procs = new ArrayList<String>();
procs.addAll(ProcedureDDLStmt.modifyProcNameList);
procs.addAll(ProcedureDDLStmt.nonModifyProcNameList);
procedureNames = new ArrayList<String>(procs);
}
return procedureNames.get(SQLTest.random.nextInt(procedureNames.size()));
}
private void revokeDelegatedPrivilege(Connection dConn, Connection gConn, String tableName) {
int num = SQLTest.random.nextInt(SQLTest.numOfWorkers) + 1;
ArrayList<SQLException> exceptionList = new ArrayList<SQLException>();
String grantees = getGrantees(num);
StringBuffer sql = new StringBuffer();
int whichPriv = SQLTest.random.nextInt(tablePriv.length);
sql.append("revoke " + tablePriv[whichPriv] + " on " + tableName + " from " + grantees);
Log.getLogWriter().info("security statement is " + sql.toString());
try {
Statement stmt = dConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
dConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleDerbySQLException(se, exceptionList);
}
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
gConn.commit();
stmt.close();
} catch (SQLException se) {
SQLHelper.handleGFGFXDException(se, exceptionList);
}
}
public void doDDLOp(Connection dConn, Connection gConn) {
boolean success = false;
int maxNumOfTries = 1;
ArrayList<SQLException> exList = new ArrayList<SQLException>();
String tableName = tableNames[SQLTest.random.nextInt(tableNames.length)];
String routineName = (hasRoutine) ? getRoutineNames(dConn, gConn) : null;
StringBuffer aStr = new StringBuffer();
// grant or revoke
String act = action[SQLTest.random.nextInt(action.length)];
aStr.append(act);
// rest of the authorization stmt
if (routineName == null) aStr.append(getAuthForTables(tableName, act));
else
aStr.append(
SQLTest.random.nextBoolean()
? getAuthForTables(tableName, act)
: getAuthForRoutines(routineName, act));
if (dConn != null) {
try {
success = applySecurityToDerby(dConn, aStr.toString(), exList); // insert to derby table
int count = 0;
while (!success) {
if (count >= maxNumOfTries) {
Log.getLogWriter()
.info("Could not get the lock to finish grant/revoke stmt, abort this operation");
return;
}
exList.clear();
success =
applySecurityToDerby(
dConn, aStr.toString(), exList); // retry insert to derby table
count++;
}
applySecurityToGFE(gConn, aStr.toString(), exList); // insert to gfe table
SQLHelper.handleMissedSQLException(exList);
gConn.commit();
dConn.commit();
} catch (SQLException se) {
SQLHelper.printSQLException(se);
throw new TestException("execute security statement fails " + TestHelper.getStackTrace(se));
} // for verification
} else {
try {
applySecurityToGFE(gConn, aStr.toString()); // insert to gfe table
gConn.commit();
} catch (SQLException se) {
SQLHelper.printSQLException(se);
throw new TestException("execute security statement fails " + TestHelper.getStackTrace(se));
}
} // no verification
}
protected void applySecurityToGFE(Connection gConn, String sql) {
Log.getLogWriter().info("execute authorization statement in GFE");
Log.getLogWriter().info("security statement is: " + sql);
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql); // execute authorization
} catch (SQLException se) {
if (se.getSQLState().equals("42506") && SQLTest.testSecurity)
Log.getLogWriter()
.info("Got the expected exception for authorization," + " continuing tests");
else if (se.getSQLState().equals("42509") && SQLTest.testSecurity)
Log.getLogWriter()
.info(
"Got the expected grant or revoke operation "
+ "is not allowed exception for authorization,"
+ " continuing tests");
else if (se.getSQLState().equals("42Y03") && hasRoutine)
Log.getLogWriter()
.info(
"Got the expected not recognized as "
+ "a function or procedure exception for authorization,"
+ " continuing tests");
else SQLHelper.handleSQLException(se);
}
}
public static void HydraTask_populateMusicDB() {
if (musicHelper == null) {
musicHelper = new MusicHelper();
}
try {
if (hasDerbyServer) {
Connection dConn = musicHelper.getDiscConnection();
MusicPopulator.addToTables(
dConn,
MusicPrms.getNumGeneratedArtists(),
MusicPrms.getNumGeneratedAlbums(),
MusicPrms.getNumGeneratedCopyrightOwners(),
MusicPrms.getNumGeneratedSongs(),
MusicPrms.getNumGeneratedGenres(),
MusicPrms.getNumGeneratedTags(),
MusicPrms.getNumGeneratedTracks());
dConn.close();
}
Connection gConn = musicHelper.getGFEConnection();
MusicPopulator.addToTables(
gConn,
MusicPrms.getNumGeneratedArtists(),
MusicPrms.getNumGeneratedAlbums(),
MusicPrms.getNumGeneratedCopyrightOwners(),
MusicPrms.getNumGeneratedSongs(),
MusicPrms.getNumGeneratedGenres(),
MusicPrms.getNumGeneratedTags(),
MusicPrms.getNumGeneratedTracks());
gConn.close();
} catch (SQLException sqle) {
SQLHelper.handleSQLException(sqle);
}
}
protected boolean applySecurityToDerby(
Connection dConn, String sql, ArrayList<SQLException> exList) {
Log.getLogWriter().info("execute authorization statement in derby");
Log.getLogWriter().info("security statement is: " + sql);
try {
Statement stmt = dConn.createStatement();
stmt.execute(sql); // execute authorization stmt on derby
} catch (SQLException se) {
if (!SQLHelper.checkDerbyException(dConn, se)) { // handles the deadlock of aborting
Log.getLogWriter().info("detected the deadlock, will try it again");
return false;
} else {
SQLHelper.handleDerbySQLException(se, exList);
}
}
return true;
}
protected Statement initialValue() {
Statement s = null;
try {
s = dConn.get().createStatement();
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
return s;
}
protected PreparedStatement initialValue() {
PreparedStatement stmt = null;
try {
stmt = dConn.get().prepareStatement(delete);
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
return stmt;
}
protected void applySecurityToGFE(Connection gConn, String sql, ArrayList<SQLException> exList) {
Log.getLogWriter().info("execute authorization statement in GFE");
Log.getLogWriter().info("security statement is: " + sql);
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql); // execute authorization stmt
} catch (SQLException se) {
SQLHelper.handleGFGFXDException(se, exList);
}
}
protected Connection initialValue() {
Connection dConn = null;
try {
dConn = ClientDiscDBManager.getConnection();
// may need to use url to get the correct db connection
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
return dConn;
}
public static void turnOnGFEAuthorization(Connection conn, String sql) {
Log.getLogWriter().info("turn on authorization statement in GFE");
try {
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
conn.commit();
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
}
public static void turnOnAuthorization(Connection dConn, Connection gConn) {
String sql =
"CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY( "
+ "'derby.database.sqlAuthorization', 'true')";
ArrayList<SQLException> exList = new ArrayList<SQLException>();
if (dConn != null) {
turnOnDerbyAuthorization(dConn, sql, exList);
turnOnGFEAuthorization(gConn, sql, exList);
SQLHelper.handleMissedSQLException(exList);
} else turnOnGFEAuthorization(gConn, sql);
}
public static Connection getDefaultConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:default:connection");
// Log.getLogWriter().info("Connection in - getDefaultConnection " +
// conn.getMetaData().getDriverName());
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
return conn;
}
protected static Connection getDefaultConnection() {
Connection conn = null;
try {
conn = DriverManager.getConnection("jdbc:default:connection");
} catch (SQLException se) {
// TODO, add Asif's work around for ticket #41642
// and use jdbc:default:gemfirexd:connection
SQLHelper.handleSQLException(se);
}
return conn;
}
public void assignSelectToMe(Connection dConn, Connection gConn, int tid) {
boolean success = false;
int maxNumOfTries = 1;
ArrayList<SQLException> exList = new ArrayList<SQLException>();
// for load test
String sql = "grant select on trade.buyorders to thr_" + tid;
if (dConn != null) {
try {
success = applySecurityToDerby(dConn, sql, exList); // insert to derby table
int count = 0;
while (!success) {
if (count >= maxNumOfTries) {
Log.getLogWriter()
.info("Could not get the lock to finish grant/revoke stmt, abort this operation");
return;
}
exList.clear();
success = applySecurityToDerby(dConn, sql, exList); // retry insert to derby table
count++;
}
applySecurityToGFE(gConn, sql, exList); // insert to gfe table
SQLHelper.handleMissedSQLException(exList);
gConn.commit();
dConn.commit();
} catch (SQLException se) {
SQLHelper.printSQLException(se);
throw new TestException("execute security statement fails " + TestHelper.getStackTrace(se));
} // for verification
} else {
try {
applySecurityToGFE(gConn, sql); // insert to gfe table
gConn.commit();
} catch (SQLException se) {
SQLHelper.printSQLException(se);
throw new TestException("execute security statement fails " + TestHelper.getStackTrace(se));
}
} // no verification
}
protected List<Struct> getKeysForQuery(String sql, boolean[] success) {
Connection noneTxConn = (Connection) SQLDistTxTest.gfxdNoneTxConn.get();
try {
Log.getLogWriter().info("executing the following query: " + sql);
ResultSet noneTxGfxdRS = noneTxConn.createStatement().executeQuery(sql);
List<Struct> noneTxGfxdList = ResultSetHelper.asList(noneTxGfxdRS, false);
if (noneTxGfxdList == null && isHATest) {
Log.getLogWriter().info("Testing HA and did not get GFXD result set");
success[0] = false;
} else {
success[0] = true;
}
return noneTxGfxdList;
} catch (SQLException se) {
SQLHelper.handleSQLException(se);
}
return null; // should not hit this as SQLHelper.handleSQLException(se) throws TestException.
}
private void delegateGrantOption(Connection gConn, String tableName) {
int num = SQLTest.random.nextInt(SQLTest.numOfWorkers) + 1;
String grantees = getGrantees(num);
StringBuffer sql = new StringBuffer();
int whichPriv = SQLTest.random.nextInt(tablePriv.length);
sql.append(
"grant " + tablePriv[whichPriv] + " on " + tableName + " to " + grantees + withGrantOption);
Log.getLogWriter().info("security statement is " + sql.toString());
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql.toString()); // execute authorization
gConn.commit();
stmt.close();
} catch (SQLException se) {
if (se.getSQLState().equals("42X01")) {
Log.getLogWriter()
.info(
"Got expected exception as WITH GRANT OPTION "
+ "is not supported yet, continuing test");
} else {
SQLHelper.handleSQLException(se);
}
}
}
public static void turnOnDerbyAuthorization(
Connection conn, String sql, ArrayList<SQLException> exList) {
Log.getLogWriter().info("turn on authorization statement in derby");
try {
Statement stmt = conn.createStatement();
stmt.executeUpdate(sql);
conn.commit();
ResultSet rs =
stmt.executeQuery(
"VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY("
+ "'derby.database.sqlAuthorization')");
rs.next();
Log.getLogWriter().info("Value of sqlAuthorization is " + rs.getString(1));
rs =
stmt.executeQuery(
"VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY("
+ "'derby.database.defaultConnectionMode')");
rs.next();
Log.getLogWriter().info("Value of defaultConnectionMode is " + rs.getString(1));
rs.close();
} catch (SQLException se) {
SQLHelper.handleDerbySQLException(se, exList);
}
}
public boolean queryGfxd(Connection gConn, boolean withDerby) {
if (!withDerby) {
return queryGfxdOnly(gConn);
}
int whichQuery = rand.nextInt(select.length); // randomly select one query sql
if (whichQuery > 3) {
whichQuery = whichQuery - 4; // avoid to hold all the keys to block other tx in the RR tests
}
int sec_id =
rand.nextInt((int) SQLBB.getBB().getSharedCounters().read(SQLBB.tradeSecuritiesPrimary));
String symbol = getSymbol();
BigDecimal price = getPrice();
BigDecimal price1 = price.add(new BigDecimal(rangePrice));
String exchange = getExchange();
int tid = testUniqueKeys ? getMyTid() : getRandomTid();
String sql = null;
ResultSet gfxdRS = null;
SQLException gfxdse = null;
List<Struct> noneTxGfxdList = null;
if (!reproduce39455 && whichQuery == 1)
whichQuery--; // hitting #39455, need to remove this line and uncomment the following once
// #39455 is fixed
try {
gfxdRS = query(gConn, whichQuery, sec_id, symbol, price, exchange, tid);
if (gfxdRS == null) {
if (isHATest) {
Log.getLogWriter().info("Testing HA and did not get GFXD result set");
return true;
} else throw new TestException("Not able to get gfxd result set");
}
} catch (SQLException se) {
if (isHATest && SQLHelper.gotTXNodeFailureException(se)) {
SQLHelper.printSQLException(se);
Log.getLogWriter()
.info("got node failure exception during Tx with HA support, continue testing");
return false; // assume node failure exception causes the tx to rollback
}
SQLHelper.printSQLException(se);
gfxdse = se;
}
List<Struct> gfxdList = ResultSetHelper.asList(gfxdRS, false);
if (gfxdList == null && isHATest) {
Log.getLogWriter().info("Testing HA and did not get GFXD result set");
return true; // do not compare query results as gemfirexd does not get any
}
boolean[] success = new boolean[1];
success[0] = false;
if (whichQuery == 1) { // after #39455 is fixed
// "select avg( distinct price) as avg_distinct_price from trade.securities where tid=? and
// symbol >?",
sql =
"select sec_id from trade.securities where tid=" + tid + " and symbol > '" + symbol + "'";
while (!success[0]) {
noneTxGfxdList = getKeysForQuery(sql, success);
}
} else if (whichQuery == 2) {
// "select price, symbol, exchange from trade.securities where (price<? or price >=?) and tid
// =?",
sql =
"select sec_id from trade.securities where (price<"
+ price
+ " or price >= "
+ price1
+ ") and tid ="
+ tid;
while (!success[0]) {
noneTxGfxdList = getKeysForQuery(sql, success);
}
}
if (whichQuery == 1 || whichQuery == 2) {
Log.getLogWriter().info("noneTxGfxdList size is " + noneTxGfxdList.size());
addReadLockedKeys(noneTxGfxdList);
} else addReadLockedKeys(gfxdList);
addQueryToDerbyTx(whichQuery, sec_id, symbol, price, exchange, tid, gfxdList, gfxdse);
// only the first thread to commit the tx in this round could verify results
// to avoid phantom read
// this is handled in the SQLDistTxTest doDMLOp
return true;
}