package com.tesora.dve.sql;
/*
* #%L
* Tesora Inc.
* Database Virtualization Engine
* %%
* Copyright (C) 2011 - 2014 Tesora Inc.
* %%
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License, version 3,
* as published by the Free Software Foundation.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
* #L%
*/
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.fail;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.tesora.dve.variables.VariableService;
import org.apache.commons.lang.StringUtils;
import org.junit.After;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Ignore;
import org.junit.Test;
import com.tesora.dve.common.DBHelper;
import com.tesora.dve.common.PEStringUtils;
import com.tesora.dve.errmap.InternalErrors;
import com.tesora.dve.errmap.MySQLErrors;
import com.tesora.dve.exceptions.PECodingException;
import com.tesora.dve.exceptions.PEException;
import com.tesora.dve.resultset.ColumnSet;
import com.tesora.dve.resultset.ResultColumn;
import com.tesora.dve.resultset.ResultRow;
import com.tesora.dve.server.bootstrap.BootstrapHost;
import com.tesora.dve.server.global.HostService;
import com.tesora.dve.singleton.Singletons;
import com.tesora.dve.sql.parser.TimestampVariableUtils;
import com.tesora.dve.sql.schema.VariableScopeKind;
import com.tesora.dve.sql.util.ComparisonOptions;
import com.tesora.dve.sql.util.Functional;
import com.tesora.dve.sql.util.PEDDL;
import com.tesora.dve.sql.util.ProjectDDL;
import com.tesora.dve.sql.util.ProxyConnectionResource;
import com.tesora.dve.sql.util.ProxyConnectionResourceResponse;
import com.tesora.dve.sql.util.ResourceResponse;
import com.tesora.dve.sql.util.StorageGroupDDL;
import com.tesora.dve.standalone.PETest;
import com.tesora.dve.variables.VariableHandler;
import com.tesora.dve.variables.VariableManager;
public class SQLVariableTest extends SchemaTest {
private static final ProjectDDL sysDDL =
new PEDDL("sysdb",
new StorageGroupDDL("sys",2,"sysg"),
"schema");
private static final String nonRootUser = "ben";
private static final String nonRootAccess = "localhost";
@BeforeClass
public static void setup() throws Throwable {
PETest.projectSetup(sysDDL);
PETest.bootHost = BootstrapHost.startServices(PETest.class);
ProxyConnectionResource pcr = new ProxyConnectionResource();
sysDDL.create(pcr);
removeUser(pcr,nonRootUser,nonRootAccess);
pcr.execute(String.format("create user '%s'@'%s' identified by '%s'",nonRootUser,nonRootAccess,nonRootUser));
pcr.disconnect();
}
ProxyConnectionResource conn = null;
@Before
public void before() throws Throwable {
conn = new ProxyConnectionResource();
}
@After
public void after() throws Throwable {
conn.disconnect();
}
@Test
public void testA() throws Throwable {
conn.execute("set names latin1");
conn.assertResults("show session variables like 'character%'",
br(nr,"character_set_client", "latin1",
nr,"character_set_connection", "latin1",
nr,"character_set_database","utf8",
nr,"character_set_results","latin1",
nr,"character_set_server","utf8",
nr,"character_set_system","utf8"));
conn.assertResults("show variables like 'character%'",
br(nr,"character_set_client", "latin1",
nr,"character_set_connection", "latin1",
nr,"character_set_database","utf8",
nr,"character_set_results","latin1",
nr,"character_set_server","utf8",
nr,"character_set_system","utf8"));
conn.execute("set @prevcharset = @@character_set_connection");
conn.execute("set names utf8");
conn.assertResults("show session variables like 'character%'",
br(nr,"character_set_client", "utf8",
nr,"character_set_connection", "utf8",
nr,"character_set_database","utf8",
nr,"character_set_results","utf8",
nr,"character_set_server","utf8",
nr,"character_set_system","utf8"));
conn.assertResults("select @@character_set_client,@@session.character_set_connection,@@character_set_results",br(nr,"utf8","utf8","utf8"));
conn.execute("set @@character_set_connection = @prevcharset");
conn.assertResults("select @@character_set_connection",br(nr,"latin1"));
conn.assertResults("show dve variables like 'version'",br(nr,"version", Singletons.require(HostService.class).getDveServerVersion()));
try {
// this should fail
conn.execute("set version = '1.0'");
fail("SET VERSION command should have thrown an exception");
} catch (PEException re) {
assertException(re,PEException.class,"Variable 'version' not settable as session variable");
}
}
@Test
public void testB() throws Throwable {
conn.execute("use " + sysDDL.getDatabaseName());
conn.execute("create table `vtest` (`id` int, `pl` varchar(32))");
conn.execute("set @default_payload = 'this is some text'");
conn.execute("set @default_id = 15");
conn.assertResults("show variables like 'tx_isolation'",br(nr,"tx_isolation","READ-COMMITTED"));
conn.assertResults("select @@tx_isolation",br(nr,"READ-COMMITTED"));
conn.execute("set transaction isolation level serializable");
conn.assertResults("show variables like 'tx_isolation'", br(nr,"tx_isolation","SERIALIZABLE"));
conn.assertResults("select @@tx_isolation",br(nr,"SERIALIZABLE"));
conn.execute("insert into `vtest` (`id`, `pl`) values (1,'one'),(2,@default_payload),(@default_id, 'three')");
conn.assertResults("select * from vtest where id = 15",br(nr,new Integer(15),"three"));
conn.assertResults("select id from vtest where pl = @default_payload",br(nr,new Integer(2)));
}
@Test
public void testShowVariables() throws Throwable {
ResourceResponse rr = conn.fetch("show variables");
List<ResultRow> results = rr.getResults();
for(ResultRow row : results) {
assertFalse(row.getResultColumn(1).getColumnValue() + " should have a value",row.getResultColumn(2).isNull());
}
// PE-668 needs this for reports to work
rr = conn.fetch("show /*!50000 global */ variables");
results = rr.getResults();
for(ResultRow row : results) {
//skip the aws ones
if (!StringUtils.containsIgnoreCase((String)row.getResultColumn(1).getColumnValue(), "aws")) {
assertFalse(row.getResultColumn(1).getColumnValue() + " should have a value",row.getResultColumn(2).isNull());
}
}
}
// when we actually support the where clause - that's the point at which we enable this again
@Ignore
@Test
public void testShowVariablesFilter() throws Throwable {
ResourceResponse unfiltered = conn.fetch("show variables");
ResourceResponse filtered = conn.fetch("show variables where Variable_name = 'version_comment'");
unfiltered.assertEqualResults("testShowVariablesFilter", filtered, ComparisonOptions.DEFAULT.withIgnoreOrder());
}
@Test
public void setNamesWithVariousQuotes() throws Throwable {
// PE-144 support double quotes for set names
// so support no quotes, single and double quotes
String charSet = "latin1";
conn.execute("set names " + charSet);
conn.assertResults("show session variables like 'character%'",
br(nr,"character_set_client", charSet,
nr,"character_set_connection", charSet,
nr,"character_set_database","utf8",
nr,"character_set_results",charSet,
nr,"character_set_server","utf8",
nr,"character_set_system","utf8"
));
charSet = "utf8";
conn.execute("set names " + "'" + charSet + "'");
conn.assertResults("show session variables like 'character%'",
br(nr,"character_set_client", charSet,
nr,"character_set_connection", charSet,
nr,"character_set_database","utf8",
nr,"character_set_results",charSet,
nr,"character_set_server","utf8",
nr,"character_set_system","utf8"
));
charSet = "ascii";
conn.execute("set names " + "\"" + charSet + "\"");
conn.assertResults("show session variables like 'character%'",
br(nr,"character_set_client", charSet,
nr,"character_set_connection", charSet,
nr,"character_set_database","utf8",
nr,"character_set_results",charSet,
nr,"character_set_server","utf8",
nr,"character_set_system","utf8"
));
}
@Test
public void testPE293() throws Throwable {
// PE-293 support CHARSET, CHARACTER SET and CHAR SET
String charSet = "utf8";
String command = "CHARSET";
conn.execute("set " + command + " " + charSet);
conn.assertResults("show variables like 'charset'",
br(nr,"charset", charSet));
charSet = "latin1";
command = "CHAR SET";
conn.execute("set " + command + " " + charSet);
conn.assertResults("show variables like 'charset'",
br(nr,"charset", charSet));
charSet = "ascii";
command = "CHARACTER SET";
conn.execute("set " + command + " " + charSet);
conn.assertResults("show variables like 'charset'",
br(nr,"charset", charSet));
}
@Test
public void testShowPlugins() throws Throwable {
ProxyConnectionResourceResponse rr = (ProxyConnectionResourceResponse) conn.fetch("show plugins");
// can't check the data returned cause it may vary on different machines
// will check the header and number of columns
String[] colHeaders = new String[] {"Name", "Status", "Type", "Library", "License"};
List<String> returnedColHeaders = new ArrayList<String>();
ColumnSet cs = rr.getColumns();
for (int i = 1; i <= cs.size(); i++) {
returnedColHeaders.add(cs.getColumn(i).getAliasName().trim());
}
for (int i = 0; i < colHeaders.length; i++) {
returnedColHeaders.contains(colHeaders[i]);
assertTrue("Column " + colHeaders[i] + " must be one of the column headers for show plugins",
returnedColHeaders.contains(colHeaders[i]));
}
assertTrue("Must have at least one row from a show plugin", rr.getResults().size() > 0);
}
@Test
public void testExtensionComments() throws Throwable {
conn.execute("/*!40103 set @OLD_TIME_ZONE=@@TIME_ZONE */");
conn.execute("/*!40103 set TIME_ZONE='+00:30' */");
conn.assertResults("select @@TIME_ZONE", br(nr,"+00:30"));
conn.execute("/*!40103 set TIME_ZONE=@OLD_TIME_ZONE */");
conn.assertResults("select /* comment */ @@TIME_ZONE /* another comment */", br(nr,"+00:00"));
}
@Test
public void testDisabledVariables() throws Throwable {
// should not be able to set sql_auto_is_null to 1
new ExpectedSqlErrorTester() {
@Override
public void test() throws Throwable {
conn.execute("set sql_auto_is_null = 1");
}
}.assertError(SchemaException.class, InternalErrors.internalFormatter,
"Internal error: No support for sql_auto_is_null = 1 (planned)");
conn.execute("set sql_auto_is_null = 0");
}
@Test
public void testPE378() throws Throwable {
conn.execute("set sql_safe_updates = 1");
conn.execute("set sql_safe_updates = 0");
conn.execute("set sql_safe_updates = 'can be anything cause we ignore this variable'");
}
@Test
public void testSetTxIsolationLevel() throws Throwable {
final String variableName = "tx_isolation";
assertVariableValue(variableName, "READ-COMMITTED");
conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE");
assertVariableValue(variableName, "SERIALIZABLE");
conn.execute("SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED");
assertVariableValue(variableName, "READ-COMMITTED");
}
@Test
public void testSqlModeDefault() throws Throwable {
final String variableName = "sql_mode";
// Reset the GLOBAL value first to test for PE-1608.
assertVariableValue(variableName, "NO_ENGINE_SUBSTITUTION");
conn.execute("SET GLOBAL sql_mode=default;");
assertVariableValue(variableName, "NO_ENGINE_SUBSTITUTION");
conn.execute("SET sql_mode=ALLOW_INVALID_DATES;");
assertVariableValue(variableName, "NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES");
conn.execute("SET sql_mode=default;");
assertVariableValue(variableName, "NO_ENGINE_SUBSTITUTION");
conn.execute("SET sql_mode=\" NO_AUTO_CREATE_USER , no_engine_substitution , ALLOW_INVALID_DATES \";");
assertVariableValue(variableName, "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ALLOW_INVALID_DATES");
conn.execute("SET sql_mode=\"\";");
assertVariableValue(variableName, "NO_ENGINE_SUBSTITUTION");
}
@Test
public void testPE1586() throws Throwable {
assertVariableValue("lower_case_file_system", "OFF");
}
@Test
public void testPE1587() throws Throwable {
assertVariableValue("have_query_cache", "YES");
}
@Test
public void testPE1589() throws Throwable {
assertVariableValue("have_ssl", "NO");
assertVariableValue("have_openssl", "NO");
}
@Test
public void testPE1590() throws Throwable {
assertVariableValue("thread_handling", "one-thread-per-connection");
}
@Test
public void testPE1609() throws Throwable {
assertVariableValue("time_format", "%H:%i:%s");
}
@Test
public void testPE1610() throws Throwable {
assertVariableValue("thread_concurrency", "10");
}
@Test
public void testPE1611() throws Throwable {
assertVariableValue("log_bin", "OFF");
}
@Test
public void testPE1612() throws Throwable {
assertVariableValue("license", "AGPL");
}
@Test
public void testPE1613() throws Throwable {
final String serverVersion = getVariableValue("version");
assertVariableValue("innodb_version", serverVersion);
}
@Test
public void testPE1614() throws Throwable {
assertVariableValue("ignore_builtin_innodb", "OFF");
}
@Test
public void testPE1615() throws Throwable {
assertVariableValue("have_profiling", "NO");
}
@Test
public void testPE1616() throws Throwable {
assertVariableValue("datetime_format", "%Y-%m-%d %H:%i:%s");
}
@Test
public void testPE1617() throws Throwable {
assertVariableValue("date_format", "%Y-%m-%d");
}
@Test
public void testPE1618() throws Throwable {
assertVariableValue("character_set_system", "utf8");
}
@Test
public void testPE1619() throws Throwable {
assertVariableValue("div_precision_increment", "4");
conn.assertResults("SELECT 1/7", br(nr, BigDecimal.valueOf(0.1429)));
conn.execute("set session div_precision_increment = 12");
conn.assertResults("SELECT 1/7", br(nr, BigDecimal.valueOf(0.142857142857)));
}
@Test
public void testPE1620() throws Throwable {
assertVariableValue("default_week_format", "0");
conn.assertResults("SELECT WEEK('2008-02-20')", br(nr, 7L));
conn.execute("set session default_week_format = 1");
conn.assertResults("SELECT WEEK('2008-02-20')", br(nr, 8L));
conn.assertResults("SELECT WEEK('2008-12-31')", br(nr, 53L));
}
@Test
public void testPE1621() throws Throwable {
assertVariableValue("version_compile_machine", "64-bit");
}
@Test
public void testPE1603() throws Throwable {
assertTimestampValue(2, null);
conn.execute("set session timestamp = 10");
assertTimestampValue(2, 10l);
conn.execute("set session timestamp = 0");
assertTimestampValue(2, null);
conn.execute("set session timestamp = 300000000");
assertTimestampValue(2, 300000000l);
conn.execute("set session timestamp = DEFAULT");
assertTimestampValue(2, null);
new ExpectedSqlErrorTester() {
@Override
public void test() throws Throwable {
conn.execute("set session timestamp = '10'");
}
}.assertError(SchemaException.class, MySQLErrors.wrongTypeForVariable, "timestamp");
new ExpectedSqlErrorTester() {
@Override
public void test() throws Throwable {
conn.execute("set session timestamp = 'DEFAULT'");
}
}.assertError(SchemaException.class, MySQLErrors.wrongTypeForVariable, "timestamp");
}
private void assertTimestampValue(final int waitTimeSec, final Long expected) throws Throwable {
final Long value1 = Long.parseLong(getVariableValue("timestamp"));
Thread.sleep(Long.valueOf(1000 * waitTimeSec));
final Long value2 = Long.parseLong(getVariableValue("timestamp"));
if (expected != null) {
//this timestamp checks are simple, because set timestamp whould fix the returned value of NOW()
conn.assertResults("SELECT UNIX_TIMESTAMP(NOW())", br(nr, expected));
assertEquals(expected, value1);
assertEquals(expected, value2);
} else {
//these timestamp checks have to account for clock and execution drift, since the timestamp is set to DEFAULT.
assertTimestampBetween(value1, value1 + waitTimeSec, value2, /*allowed width*/waitTimeSec+2);//allow for 2 seconds of variation, Thread.sleep(2000) could easily measure as 3 seconds.
long beforeTime = TimestampVariableUtils.getCurrentSystemTime();
ResourceResponse queriedTimestamp = conn.fetch("SELECT UNIX_TIMESTAMP(NOW())");
long afterTime = TimestampVariableUtils.getCurrentSystemTime();
List<ResultRow> rows = queriedTimestamp.getResults();
assertEquals(1,rows.size());
final ResultRow row = rows.get(0);
final List<ResultColumn> resultColumns = row.getRow();
assertEquals(1,resultColumns.size());
ResultColumn col = resultColumns.get(0);
long entry = (Long)col.getColumnValue();
assertTimestampBetween(beforeTime,entry,afterTime, 1);
}
}
private void assertTimestampBetween(long before, long middle, long after, long allowedWidth){
assertTrue("before = "+before +" , middle= "+middle , before <= middle);
assertTrue("middle = " + middle + " , after = " + after, middle <= after);
assertTrue("before = "+before +" , after = " + after +" , allowedWidth = "+allowedWidth, after - before <= allowedWidth);
}
@Test
public void testPE1639() throws Throwable {
assertVariableValue("innodb_adaptive_flushing", "ON");
}
@Test
public void testPE1640() throws Throwable {
assertVariableValue("innodb_fast_shutdown", "1");
}
@Test
public void testPE1641() throws Throwable {
assertVariableValue("innodb_mirrored_log_groups", "1");
}
@Test
public void testPE1642() throws Throwable {
assertVariableValue("innodb_stats_method", "nulls_equal");
}
@Test
public void testPE1643() throws Throwable {
final String tspValue = getVariableValue("innodb_stats_transient_sample_pages");
assertVariableValue("innodb_stats_sample_pages", tspValue);
}
@Test
public void testPE1644() throws Throwable {
assertVariableValue("innodb_stats_transient_sample_pages", "8");
}
@Test
public void testPE1645() throws Throwable {
assertVariableValue("innodb_table_locks", "ON");
}
@Test
public void testPE1646() throws Throwable {
assertVariableValue("myisam_use_mmap", "OFF");
}
@Test
public void testPE1647() throws Throwable {
assertVariableValue("tmp_table_size", "16777216");
}
@Test
public void testPE1649() throws Throwable {
assertVariableValue("skip_networking", "OFF");
}
@Test
public void testPE1650() throws Throwable {
assertVariableValue("protocol_version", "10");
}
@Test
public void testPE1656() throws Throwable {
assertVariableValue("backend_wait_timeout", "28800");
assertVariableValue("wait_timeout", "28800");
conn.execute("set wait_timeout = 14400");
assertVariableValue("backend_wait_timeout", "28800");
assertVariableValue("wait_timeout", "14400");
}
@Test
public void testPE1659() throws Throwable {
new ExpectedSqlErrorTester() {
@Override
public void test() throws Throwable {
conn.execute("set global wait_timeout = 'blah'");
}
}.assertError(SchemaException.class, MySQLErrors.wrongTypeForVariable, "wait_timeout");
new ExpectedSqlErrorTester() {
@Override
public void test() throws Throwable {
conn.execute("set global sql_auto_is_null = 2");
}
}.assertError(SchemaException.class, MySQLErrors.wrongValueForVariable, "sql_auto_is_null", "2");
new ExpectedSqlErrorTester() {
@Override
public void test() throws Throwable {
conn.execute("set global sql_auto_is_null = 'blah'");
}
}.assertError(SchemaException.class, MySQLErrors.wrongValueForVariable, "sql_auto_is_null", "blah");
new ExpectedSqlErrorTester() {
@Override
public void test() throws Throwable {
conn.execute("set global tx_isolation = 'blah'");
}
}.assertError(SchemaException.class, MySQLErrors.wrongValueForVariable, "tx_isolation", "blah");
new ExpectedSqlErrorTester() {
@Override
public void test() throws Throwable {
conn.execute("set global tx_isolation = NULL");
}
}.assertError(SchemaException.class, MySQLErrors.wrongValueForVariable, "tx_isolation", "NULL");
// Test out-of-range value handling.
// They should be replaced by the nearest limit.
// Valid 'div_precision_increment' values are: 0 - 30
assertVariableValue("div_precision_increment", "4");
conn.execute("set div_precision_increment = -1");
assertVariableValue("div_precision_increment", "0");
conn.execute("set div_precision_increment = 35");
assertVariableValue("div_precision_increment", "30");
conn.execute("set div_precision_increment = DEFAULT");
assertVariableValue("div_precision_increment", "4");
}
@Test
public void testPE1662() throws Throwable {
// SESSION values
assertVariableValue("character_set_connection", "utf8");
assertVariableValue("collation_connection", "utf8_general_ci");
conn.execute("set character_set_connection = ascii");
assertVariableValue("character_set_connection", "ascii");
assertVariableValue("collation_connection", "ascii_general_ci");
conn.execute("set collation_connection = utf8_general_ci");
assertVariableValue("character_set_connection", "utf8");
assertVariableValue("collation_connection", "utf8_general_ci");
// GLOBAL values
assertGlobalVariableValue("character_set_connection", "utf8");
assertGlobalVariableValue("collation_connection", "utf8_general_ci");
conn.execute("set global character_set_connection = ascii");
assertGlobalVariableValue("character_set_connection", "ascii");
assertGlobalVariableValue("collation_connection", "ascii_general_ci");
conn.execute("set global collation_connection = utf8_general_ci");
assertGlobalVariableValue("character_set_connection", "utf8");
assertGlobalVariableValue("collation_connection", "utf8_general_ci");
}
private void assertVariableValue(final String variableName, final Object expected) throws Throwable {
conn.assertResults("show variables like '" + variableName + "'", br(nr, variableName, expected));
}
private void assertGlobalVariableValue(final String variableName, final Object expected) throws Throwable {
conn.assertResults("show global variables like '" + variableName + "'", br(nr, variableName, expected));
}
private String getVariableValue(final String variableName) throws Throwable {
final List<ResultRow> rows = conn.fetch("show variables like '" + variableName + "'").getResults();
assertEquals("Exactly one result row expected for variable '" + variableName + "'.", 1, rows.size());
final ResultRow row = rows.get(0);
final List<ResultColumn> resultColumns = row.getRow();
if (resultColumns.size() != 2) {
throw new PECodingException("\"SHOW VARIABLES LIKE ...\" should return exactly two columns.");
}
assertEquals("Wrong variable name returned for '" + variableName + "'.", variableName, resultColumns.get(0).getColumnValue());
return (String) resultColumns.get(1).getColumnValue();
}
// this is a cheesy test
@Test
public void testDynamicAdd() throws Throwable {
String infoQuery = "select * from information_schema.variable_definitions where name like 'fromage'";
conn.assertResults(infoQuery,br());
conn.execute("alter dve add variable fromage scopes='SESSION,GLOBAL' valuetype='varchar' value='blue' options='NULLABLE' description='cheese!'");
conn.assertResults(infoQuery, br(nr,"fromage","blue","varchar","SESSION,GLOBAL","NULLABLE","cheese!"));
ProxyConnectionResource nonRoot = new ProxyConnectionResource(nonRootUser,nonRootUser);
try {
nonRoot.assertResults("show variables like 'fromage'", br(nr,"fromage","blue"));
} finally {
nonRoot.close();
}
}
@Test
public void testDocStrings() throws Throwable {
ResourceResponse rr = conn.execute("select name from information_schema.variable_definitions where description = ''");
List<String> missing = new ArrayList<String>();
for(ResultRow row : rr.getResults()) {
missing.add((String)row.getResultColumn(1).getColumnValue());
}
if (!missing.isEmpty())
fail("Empty variable docstrings: " + Functional.join(missing, ","));
// System.out.println(conn.printResults("select * from information_schema.variable_definitions"));
}
private static String getCurrentGlobalValue(DBHelper helper, String varName) throws Throwable {
ResultSet rs = null;
String out = null;
try {
if (helper.executeQuery("select @@global." + varName)) {
rs = helper.getResultSet();
if (!rs.next())
fail("Variable " + varName + " apparently does not exist on native");
out = rs.getString(1);
}
} finally {
if (rs != null)
rs.close();
}
return out;
}
@SuppressWarnings({ "unchecked", "rawtypes" })
@Test
public void testGlobalPushdown() throws Throwable {
VariableManager vm = Singletons.require(VariableService.class).getVariableManager();
DBHelper helper = null;
String execFormat = "set global %s = %s";
try {
helper = buildHelper();
for(VariableHandler vh : vm.getGlobalHandlers()) {
if (!vh.isEmulatedPassthrough()) continue;
Object defVal = vh.getDefaultOnMissing();
if (defVal == null) continue;
String currentGlobal = getCurrentGlobalValue(helper,vh.getName());
String setTo = vh.toExternal(defVal);
conn.execute(String.format(execFormat,vh.getName(),setTo));
String newGlobal = getCurrentGlobalValue(helper,vh.getName());
assertEquals("should have same value for " + vh.getName(),PEStringUtils.dequote(setTo),newGlobal);
Object oldGlobalConverted = vh.toInternal(currentGlobal);
String oldGlobalExternal = vh.toExternal(oldGlobalConverted);
helper.executeQuery("set global " + vh.getName() + " = " + oldGlobalExternal);
}
} finally {
helper.disconnect();
}
}
@Test
public void testAccess() throws Throwable {
VariableManager vm = Singletons.require(VariableService.class).getVariableManager();
testAccess(vm.lookupMustExist(null,"tx_isolation"),new Values("REPEATABLE-READ","SERIALIZABLE","READ-COMMITTED"));
testAccess(vm.lookupMustExist(null,"adaptive_cleanup_interval"), new Values("1000","5000","10000"));
testAccess(vm.lookupMustExist(null,"cost_based_planning"),new Values("YES","NO"));
testAccess(vm.lookupMustExist(null,"debug_context"), new Values("YES","NO"));
}
private static final VariableRoundTrip[] globals = new VariableRoundTrip[] {
new VariableRoundTrip(
"set global %s = %s",
"show global variables like '%s'") {
public Object[] buildExpectedResults(String varName, String varValue) {
return br(nr,varName,varValue);
}
},
new VariableRoundTrip(
"set @@global.%s = %s",
"select variable_value from information_schema.global_variables where variable_name like '%s'")
};
private static final VariableRoundTrip[] sessions = new VariableRoundTrip[] {
new VariableRoundTrip(
"set %s = %s",
"show session variables like '%s'") {
public Object[] buildExpectedResults(String varName, String varValue) {
return br(nr,varName,varValue);
}
},
new VariableRoundTrip(
"set @@session.%s = %s",
"select variable_value from information_schema.session_variables where variable_name like '%s'"),
new VariableRoundTrip(
"set @@%s = %s",
"select variable_value from information_schema.session_variables where variable_name like '%s'"),
new VariableRoundTrip(
"set @@local.%s = %s",
"select variable_name, variable_value from information_schema.session_variables where variable_name = '%s'") {
public Object[] buildExpectedResults(String varName, String varValue) {
return br(nr,varName,varValue);
}
}
};
private static final String sessQuery =
"select variable_value from information_schema.session_variables where variable_name = '%s'";
@SuppressWarnings("rawtypes")
private void testAccess(VariableHandler handler, Values values) throws Throwable {
ProxyConnectionResource nonRoot = new ProxyConnectionResource(nonRootUser,nonRootUser);
try {
// before we start modifying the global values, save off the session value to validate
// that it does not change
String sessVal = null;
if (handler.getScopes().contains(VariableScopeKind.SESSION))
sessVal = getSessionValue(nonRoot,handler);
if (handler.getScopes().contains(VariableScopeKind.GLOBAL)) {
// make sure all the root versions work
for(VariableRoundTrip vrt : globals) {
roundTrip(conn,handler,vrt,values);
}
// now, for each root version, make sure a nonroot user cannot set it
for(VariableRoundTrip vrt : globals) {
try {
nonRoot.execute(vrt.buildSet(handler.getName(), values.current(), handler.getMetadata().isNumeric()));
fail("non root should not be able to set global value of " + handler.getName());
} catch (PEException pe) {
if (!pe.getMessage().startsWith("Must be root"))
throw pe;
}
}
}
if (handler.getScopes().contains(VariableScopeKind.SESSION)) {
String stillValue = getSessionValue(nonRoot,handler);
assertEquals("sess value should not change",sessVal,stillValue);
// make sure both root and nonroot can modify session values
for(VariableRoundTrip vrt : sessions) {
roundTrip(conn,handler,vrt,values);
}
for(VariableRoundTrip vrt : sessions) {
roundTrip(nonRoot,handler,vrt,values);
}
}
} finally {
nonRoot.disconnect();
}
}
@SuppressWarnings("rawtypes")
private String getSessionValue(ProxyConnectionResource conn, VariableHandler handler) throws Throwable {
ResourceResponse rr = conn.execute(String.format(sessQuery,handler.getName()));
return (String) rr.getResults().get(0).getRow().get(0).getColumnValue();
}
@SuppressWarnings("rawtypes")
private void roundTrip(ProxyConnectionResource proxyConn, VariableHandler handler,
VariableRoundTrip vrt, Values values) throws Throwable {
conn.execute(vrt.buildSet(handler.getName(), values.next(), handler.getMetadata().isNumeric()));
conn.assertResults(vrt.buildQuery(handler.getName()),
vrt.buildExpectedResults(handler.getName(), values.current()));
}
private static class Values {
private final String[] values;
private int index = -1;
public Values(String... myValues) {
values = myValues;
index = -1;
}
public String next() {
if (++index >= values.length)
index = 0;
return values[index];
}
public String current() {
return values[index];
}
}
private static class VariableRoundTrip {
private String setFormat;
private String queryFormat;
public VariableRoundTrip(String setFormat, String queryFormat) {
this.setFormat = setFormat;
this.queryFormat = queryFormat;
}
public String buildSet(String varName, String varValue, boolean isNumeric) {
return String.format(setFormat, varName, (isNumeric) ? varValue : PEStringUtils.singleQuote(varValue));
}
public String buildQuery(String varName) {
return String.format(queryFormat, varName);
}
public Object[] buildExpectedResults(String varName, String varValue) {
return br(nr,varValue);
}
}
}