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.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Locale;
import java.util.concurrent.TimeUnit;
import org.apache.commons.lang.BooleanUtils;
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.exceptions.PEException;
import com.tesora.dve.resultset.ResultColumn;
import com.tesora.dve.resultset.ResultRow;
import com.tesora.dve.server.bootstrap.BootstrapHost;
import com.tesora.dve.sql.util.DBHelperConnectionResource;
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.ResourceResponse;
import com.tesora.dve.sql.util.StorageGroupDDL;
import com.tesora.dve.sql.util.TimestampVariableTestUtils;
import com.tesora.dve.standalone.PETest;
public class CurrentTimestampDefaultValueTest extends SchemaTest {
private static final ProjectDDL checkDDL = new PEDDL("checkdb",
new StorageGroupDDL("check", 2, "checkg"), "schema");
@BeforeClass
public static void setup() throws Exception {
PETest.projectSetup(checkDDL);
PETest.bootHost = BootstrapHost.startServices(PETest.class);
}
protected ProxyConnectionResource conn;
protected DBHelperConnectionResource dbh;
@Before
public void connect() throws Throwable {
conn = new ProxyConnectionResource();
checkDDL.create(conn);
dbh = new DBHelperConnectionResource();
}
@After
public void disconnect() throws Throwable {
if(conn != null)
conn.disconnect();
conn = null;
if(dbh != null)
dbh.disconnect();
dbh=null;
}
@Test
public void test() throws Throwable {
conn.execute("create table `a` (`id` int, `ts` timestamp default current_timestamp, primary key (`id`)) ");
long preTestTime = TimeUnit.MILLISECONDS.toSeconds(System.currentTimeMillis());
conn.execute("insert into `a` (`id`) values (1)");
ResourceResponse resp = conn.fetch("select `ts` from `a` where `id`=1");
List<ResultRow> rows = resp.getResults();
assertEquals("Expected one row only", 1, rows.size());
Long insertedTime = ((Timestamp)(rows.get(0).getResultColumn(1).getColumnValue())).getTime();
assertTrue("Inserted default time must be >= starting time", preTestTime <= insertedTime);
// make sure specified ts still works
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.ENGLISH);
preTestTime = formatter.parse("2012-05-05 01:00:00").getTime();
conn.execute("insert into `a` values (2, '2012-05-05 01:00:00.0')");
resp = conn.fetch("select `ts` from `a` where `id`=2");
rows = resp.getResults();
assertEquals("Expected one row only", 1, rows.size());
insertedTime = ((Timestamp)(rows.get(0).getResultColumn(1).getColumnValue())).getTime();
assertTrue("Inserted time must be = starting time", preTestTime == insertedTime);
}
@Ignore
@Test
public void testAlterAddCurrentTimestampStringAsDefault() throws Throwable {
// make sure user can alter a table and timestamp column default
conn.execute("create table b (id int, ts timestamp default '2012-05-05 01:00:00.0')");
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.ENGLISH);
long preTestTime = formatter.parse("2012-05-05 01:00:00").getTime();
// make sure value inserted is the expected value
conn.execute("insert into b (id) values (1))");
ResourceResponse resp = conn.fetch("select ts from b where id=1");
List<ResultRow> rows = resp.getResults();
assertEquals("Expected one row only", 1, rows.size());
Timestamp insertedValue = (Timestamp)(rows.get(0).getResultColumn(1).getColumnValue());
assertTrue("Inserted time must be = starting time", preTestTime == insertedValue.getTime());
// this fails in native MySQL!
try {
String query = "alter table b alter ts set default current_timestamp";
conn.execute(query);
fail("Expected alter statement to fail: " + query);
} catch (PEException e) {
// expected
}
conn.execute("alter table b alter ts drop default");
conn.execute("insert into b (id) values (2))");
resp = conn.fetch("select ts from b where id=2");
rows = resp.getResults();
assertEquals("Expected one row only", 1, rows.size());
// TODO mysql fails because the ts column has a not null attribute due to the
// default value on create
// we should fail too but we insert the current timestamp...
assertTrue("Inserted value must be null", rows.get(0).getResultColumn(1).getColumnValue()==null);
}
@Test
public void testCurrentTimestampStringAsDefault() throws Throwable {
// make sure user can specify current_timestamp as a literal still
conn.execute("create table c (id int, data varchar(50) default 'current_timestamp')");
conn.execute("insert into c (id) values (1)");
ResourceResponse resp = conn.fetch("select data from c where id=1");
List<ResultRow> rows = resp.getResults();
assertEquals("Expected one row only", 1, rows.size());
String insertedValue = (String)(rows.get(0).getResultColumn(1).getColumnValue());
assertEquals("Default literal must be 'current_timestamp'", "current_timestamp", insertedValue);
}
@Test
public void testSimpleQueriesForTimestampVariable() throws Throwable {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.ENGLISH);
int i = 0;
for (Object[] objects : TimestampVariableTestUtils.getTestValues()) {
String value = (String)objects[0];
Boolean nullable = BooleanUtils.toBoolean((Integer)objects[1]);
String defaultValue = (String)objects[2];
Boolean onUpdate = BooleanUtils.toBoolean((Integer)objects[3]);
Boolean expectedInsertTSVarSet = BooleanUtils.toBoolean((Integer)objects[4]);
Boolean expectedUpdateTSVarSet = BooleanUtils.toBoolean((Integer)objects[5]);
Boolean ignoreTest = BooleanUtils.toBoolean((Integer)objects[6]);
if (ignoreTest) {
continue;
}
String tableName = "ts" + i;
String createTableSQL = TimestampVariableTestUtils.buildCreateTableSQL(tableName, nullable, defaultValue, onUpdate);
String insertSQL = TimestampVariableTestUtils.buildInsertTestSQL(tableName, value, 1, Integer.toString(i));
String updateSQL = TimestampVariableTestUtils.buildUpdateTestSQL(tableName, value, 1, Integer.toString(i)+Integer.toString(i));
++i;
conn.execute(createTableSQL);
long preTestTime = TimeUnit.MILLISECONDS.toSeconds(System.currentTimeMillis());
conn.execute(insertSQL);
ResourceResponse resp = conn.fetch("select ts from " + tableName + " where id=1");
List<ResultRow> rows = resp.getResults();
assertEquals("Expected one row only", 1, rows.size());
ResultColumn rc = rows.get(0).getResultColumn(1);
if (expectedInsertTSVarSet) {
// if we expected to set the timestamp variable then the ts column must contain the current time
Timestamp ts = (Timestamp)(rc.getColumnValue());
assertTrue("Inserted time must be >= starting time", preTestTime <= TimeUnit.MILLISECONDS.toSeconds(ts.getTime()));
} else {
boolean isNull = rc.isNull();
if (!isNull) {
// ts column is not null so let's see if the column value was specified and if it was it should match the ts value
Timestamp ts = (Timestamp)(rc.getColumnValue());
if (StringUtils.contains(value,"2000-01-01 01:02:03")) {
assertTrue(TimeUnit.MILLISECONDS.toSeconds(ts.getTime()) ==
TimeUnit.MILLISECONDS.toSeconds(formatter.parse("2000-01-01 01:02:03").getTime()));
} else if (StringUtils.equals("0",value)) {
assertTrue(TimeUnit.MILLISECONDS.toSeconds(ts.getTime()) ==
TimeUnit.MILLISECONDS.toSeconds(formatter.parse("0000-00-00 00:00:00").getTime()));
} else if (StringUtils.equals("current_timestamp",value)) {
assertTrue("Inserted time must be >= starting time", preTestTime <= TimeUnit.MILLISECONDS.toSeconds(ts.getTime()));
} else {
// if we get here column value is not specified so figure out what default value we need
if (StringUtils.contains(defaultValue,"2000-01-01 01:02:03")) {
assertTrue(TimeUnit.MILLISECONDS.toSeconds(ts.getTime()) ==
TimeUnit.MILLISECONDS.toSeconds(formatter.parse("2000-01-01 01:02:03").getTime()));
} else if (StringUtils.equals("0",defaultValue)) {
assertTrue(TimeUnit.MILLISECONDS.toSeconds(ts.getTime()) ==
TimeUnit.MILLISECONDS.toSeconds(formatter.parse("0000-00-00 00:00:00").getTime()));
} else if (StringUtils.equals("current_timestamp",defaultValue)) {
assertTrue("Inserted time must be >= starting time", preTestTime <= TimeUnit.MILLISECONDS.toSeconds(ts.getTime()));
}
}
} else {
// column better be nullable
assertTrue(nullable);
if (StringUtils.isBlank(value)) {
// column not specified better validate the default value
assertTrue(StringUtils.equals("null", defaultValue) || StringUtils.isBlank(defaultValue));
} else {
// column value was specified as null
assertEquals("null", value);
}
}
}
long updatePreTestTime = TimeUnit.MILLISECONDS.toSeconds(System.currentTimeMillis());
conn.execute(updateSQL);
resp = conn.fetch("select ts from " + tableName + " where id=1");
rows = resp.getResults();
assertEquals("Expected one row only", 1, rows.size());
rc = rows.get(0).getResultColumn(1);
if (expectedUpdateTSVarSet) {
// if we expected to set the timestamp variable then the ts column must contain the current time
Timestamp ts = (Timestamp)(rc.getColumnValue());
assertTrue("Update time must be >= starting time", updatePreTestTime <= TimeUnit.MILLISECONDS.toSeconds(ts.getTime()));
} else {
boolean isNull = rc.isNull();
if (!isNull) {
// ts column is not null so let's see if the column value was specified and if it was it should match the ts value
Timestamp ts = (Timestamp)(rc.getColumnValue());
if (StringUtils.contains(value,"2000-01-01 01:02:03")) {
assertTrue(TimeUnit.MILLISECONDS.toSeconds(ts.getTime()) ==
TimeUnit.MILLISECONDS.toSeconds(formatter.parse("2000-01-01 01:02:03").getTime()));
} else if (StringUtils.equals("0",value)) {
assertTrue(TimeUnit.MILLISECONDS.toSeconds(ts.getTime()) ==
TimeUnit.MILLISECONDS.toSeconds(formatter.parse("0000-00-00 00:00:00").getTime()));
} else if (StringUtils.equals("current_timestamp",value)) {
assertTrue("Inserted time must be >= starting time", preTestTime <= TimeUnit.MILLISECONDS.toSeconds(ts.getTime()));
} else {
// if we get here column value is not specified so figure out what default value we need
if (StringUtils.contains(defaultValue,"2000-01-01 01:02:03")) {
assertTrue(TimeUnit.MILLISECONDS.toSeconds(ts.getTime()) ==
TimeUnit.MILLISECONDS.toSeconds(formatter.parse("2000-01-01 01:02:03").getTime()));
} else if (StringUtils.equals("0",defaultValue)) {
assertTrue(TimeUnit.MILLISECONDS.toSeconds(ts.getTime()) ==
TimeUnit.MILLISECONDS.toSeconds(formatter.parse("0000-00-00 00:00:00").getTime()));
} else if (StringUtils.equals("current_timestamp",defaultValue)) {
assertTrue("Inserted time must be >= starting time", preTestTime <= TimeUnit.MILLISECONDS.toSeconds(ts.getTime()));
}
}
} else {
// column better be nullable
assertTrue(nullable);
if (StringUtils.isBlank(value)) {
// column not specified better validate the default value
// note in one case the default is not specified and no value was specified for the column
// then the column value is null so the blank default is ok
assertTrue(StringUtils.equals("null", defaultValue) || StringUtils.isBlank(defaultValue));
} else {
// column value was specified as null
assertEquals("null", value);
}
}
}
}
}
@Test
public void testTimezoneVariableChange() throws Throwable {
// set timezone to UTC+1
conn.execute("set time_zone='+01:00'");
// insert a row into the test table and get the timestamp value back
conn.execute("create table `tz` (`id` int, `ts` timestamp default current_timestamp, primary key (`id`)) ");
conn.execute("insert into `tz` (`id`) values (1)");
ResourceResponse resp = conn.fetch("select ts from tz where id=1");
List<ResultRow> rows = resp.getResults();
assertEquals("Expected one row only", 1, rows.size());
Timestamp utcValue = (Timestamp)(rows.get(0).getResultColumn(1).getColumnValue());
// change the timezone and do the select again
conn.execute("set time_zone='+02:00'");
resp = conn.fetch("select ts from tz where id=1");
rows = resp.getResults();
assertEquals("Expected one row only", 1, rows.size());
Timestamp newTZValue = (Timestamp)(rows.get(0).getResultColumn(1).getColumnValue());
// there should be a 1 hour difference now between the selected timestamp values
// because of the time_zone change
long timeDiff = TimeUnit.MILLISECONDS.toHours(newTZValue.getTime()-utcValue.getTime());
assertTrue("Changing time_zone variable should alter returned timestamp value.", timeDiff==1);
}
@Test
public void testPE688() throws Throwable {
conn.execute("CREATE TABLE tstest (`id` int, `history` timestamp default current_timestamp on update current_timestamp) broadcast distribute");
conn.execute("insert into tstest (id) values (1),(10)");
Thread.sleep(1000);
conn.execute("insert into tstest (id) values (2),(20)");
ResourceResponse resp1 = conn.fetch("select id, unix_timestamp(history) from tstest where id=1 or id=10");
ResourceResponse resp2 = conn.fetch("select id, unix_timestamp(history) from tstest where id=2 or id=20");
assertEquals("Timestamps for id=1 and 10 must be the same", resp1.getResults().get(0).getResultColumn(2).getColumnValue(), resp1.getResults().get(1).getResultColumn(2).getColumnValue());
assertEquals("Timestamps for id=2 and 20 must be the same", resp2.getResults().get(0).getResultColumn(2).getColumnValue(), resp2.getResults().get(1).getResultColumn(2).getColumnValue());
assertFalse("Timestamps for id=1 and 2 must be different", resp1.getResults().get(0).getResultColumn(2).getColumnValue().equals(resp2.getResults().get(0).getResultColumn(2).getColumnValue()));
assertFalse("Timestamps for id=10 and 20 must be different", resp1.getResults().get(1).getResultColumn(2).getColumnValue().equals(resp2.getResults().get(1).getResultColumn(2).getColumnValue()));
conn.execute("update tstest set history=now() where id=1");
Thread.sleep(1000);
conn.execute("update tstest set history=now() where id=10");
ResourceResponse resp3 = conn.fetch("select id, unix_timestamp(history) from tstest where id=1");
ResourceResponse resp4 = conn.fetch("select id, unix_timestamp(history) from tstest where id=10");
assertFalse("Timestamps for id=1 and 10 must be different", resp3.getResults().get(0).getResultColumn(2).getColumnValue().equals(resp4.getResults().get(0).getResultColumn(2).getColumnValue()));
// this is problematic due to timing need to think of a better way to set this
// conn.assertResults("select id from tstest where history >= now() order by id", br(nr,10));
// Thread.sleep(1000);
// conn.assertResults("select id from tstest where history >= now() order by id", br());
}
}