Java tutorial
package com.tesora.dve.client; /* * #%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.testng.Assert.assertEquals; import static org.testng.Assert.assertFalse; import static org.testng.Assert.assertNull; import static org.testng.Assert.assertTrue; import java.io.IOException; import java.io.InputStream; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.Properties; import org.apache.commons.lang.StringEscapeUtils; import org.apache.commons.lang.StringUtils; import org.testng.annotations.AfterClass; import org.testng.annotations.AfterMethod; import org.testng.annotations.BeforeClass; import org.testng.annotations.BeforeMethod; import org.testng.annotations.Test; import com.tesora.dve.common.DBHelper; import com.tesora.dve.common.PEConstants; import com.tesora.dve.common.PEFileUtils; import com.tesora.dve.common.ShowSchema; import com.tesora.dve.common.catalog.TestCatalogHelper; import com.tesora.dve.exceptions.PEException; 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.template.TemplateBuilder; import com.tesora.dve.sql.util.ProxyConnectionResource; import com.tesora.dve.standalone.PETest; import com.tesora.dve.test.simplequery.SimpleQueryTest; @Test(groups = { "NonSmokeTest" }) public class ClientTestNG extends PETest { static DBHelper dbHelper; @BeforeClass public static void startup() throws Throwable { TestCatalogHelper.createTestCatalog(PETest.class); BootstrapHost.startServices(PETest.class); SimpleQueryTest.cleanupSites(6, "TestDB", "d7test"); ProxyConnectionResource pcr = new ProxyConnectionResource(); SimpleQueryTest.createSites(2, pcr); SimpleQueryTest.createGroupAndTestDB(2, pcr); pcr.execute("create table alltypes ( bit_column bit,tinyint_column tinyint,bigint_column bigint," + "longvarb_column long varbinary,varb_column varbinary(200),binary_column binary(10)," + "text_column text, char_column char(10),num_column numeric(10,2),dec_column decimal(5,4)," + "int_column integer,smallint_column smallint,float_column float,double_column double," + "varchar_column varchar(10),date_column date,time_column time, datetime_column datetime," + "intu_column integer unsigned, bigintu_column bigint unsigned " + ") engine=innodb random distribute"); pcr.disconnect(); // populateMetadata(ClientTestNG.class, Singletons.require(HostService.class).getProperties()); populateSites(ClientTestNG.class, Singletons.require(HostService.class).getProperties()); } @AfterClass public static void shutdown() throws Exception { BootstrapHost.stopServices(); } @BeforeMethod public void preTestSetup() throws PEException { Properties peProps = PEFileUtils.loadPropertiesFile(ClientTestNG.class, PEConstants.CONFIG_FILE_NAME); dbHelper = new DBHelper( peProps.getProperty(PEConstants.PROP_JDBC_URL) + "/TestDB?zeroDateTimeBehavior=convertToNull", peProps.getProperty(PEConstants.PROP_JDBC_USER), peProps.getProperty(PEConstants.PROP_JDBC_PASSWORD)); dbHelper.connect(); } @AfterMethod public void postTestTeardown() { dbHelper.disconnect(); } @Test public void SimpleSelectTest1() throws Exception { dbHelper.executeQuery("SELECT * FROM alltypes"); int rows = countResultSetRows(dbHelper.getResultSet()); assertEquals(2, rows); } @Test public void showTypesTest1() throws Exception { int rows = countResultSetRows(dbHelper.getConnection().getMetaData().getTypeInfo()); // TODO this is mysql specific...... // assertEquals(40, rows); } @Test public void createTableTest() throws Exception { assertFalse(dbHelper.executeQuery("CREATE TABLE client_test1 ( col1 int, col2 varchar(10))")); assertTrue(dbHelper.executeQuery("SELECT * from client_test1")); } @Test public void simpleInsertTest() throws Exception { assertFalse(dbHelper.executeQuery("CREATE TABLE client_test2 ( col1 int, col2 varchar(10))")); assertFalse(dbHelper.executeQuery("INSERT INTO client_test2 VALUES (1,'row1 col2')")); assertTrue(dbHelper.executeQuery("SELECT * FROM client_test2")); int rows = countResultSetRows(dbHelper.getResultSet()); assertEquals(1, rows); } @Test public void multipleTupleInsertTest() throws Exception { assertFalse(dbHelper.executeQuery("CREATE TABLE client_test ( col1 int, col2 varchar(10))")); assertFalse(dbHelper.executeQuery("INSERT INTO client_test VALUES (1,'row1 col2'),(2,'row2 col2')")); assertTrue(dbHelper.executeQuery("SELECT * FROM client_test")); int rows = countResultSetRows(dbHelper.getResultSet()); assertEquals(2, rows); } @Test public void aliasingTest() throws Exception { assertFalse(dbHelper .executeQuery("CREATE TABLE atest1 (id int not null, rpart int not null, ipart int not null)")); assertFalse(dbHelper .executeQuery("insert into atest1 (id, rpart, ipart) values (1,1,0), (2,0,1),(3,1,1),(4,0,0)")); verifyMetadata("select * from atest1", new String[] { "id", "id", "rpart", "rpart", "ipart", "ipart" }); verifyMetadata("select id di, rpart trapr, ipart trapi from atest1", new String[] { "id", "di", "rpart", "trapr", "ipart", "trapi" }); verifyMetadata("select id di, rpart from atest1", new String[] { "id", "di", "rpart", "rpart" }); verifyMetadata("select id, rpart + ipart from atest1", new String[] { "id", "id", "rpart + ipart", "rpart + ipart" }); verifyMetadata("select id, rpart + ipart as s from atest1", new String[] { "id", "id", "s", "s" }); verifyMetadata("select atest1.id, atest1.rpart, atest1.ipart as prati from atest1", new String[] { "id", "id", "rpart", "rpart", "ipart", "prati" }); verifyMetadata("select a.id, case a.id when 0 then a.rpart else a.ipart end as cs from atest1 a", new String[] { "id", "id", "cs", "cs" }); } @Test public void templateTest() throws Exception { dbHelper.executeQuery(new TemplateBuilder("drupal7_commons") .withRequirement("create range if not exists block_range (int) persistent group #sg#") .withRequirement("create range if not exists field_range (varchar,int) persistent group #sg#") .toCreateStatement()); assertFalse(dbHelper.executeQuery("CREATE DATABASE d7test USING TEMPLATE drupal7_commons STRICT")); assertTrue(dbHelper.executeQuery("SHOW RANGES")); assertEquals(2, countResultSetRows(dbHelper.getResultSet())); } @Test public void broadcastDeleteTest() throws Exception { assertFalse(dbHelper.executeQuery("CREATE TABLE foo_delete(col1 int) BROADCAST DISTRIBUTE")); assertFalse(dbHelper.executeQuery("INSERT INTO foo_delete VALUES (1)")); assertTrue(dbHelper.executeQuery("SELECT * FROM foo_delete")); assertEquals(1, countResultSetRows(dbHelper.getResultSet())); assertFalse(dbHelper.executeQuery("DELETE FROM foo_delete WHERE col1=1")); assertTrue(dbHelper.executeQuery("SELECT * FROM foo_delete")); assertEquals(0, countResultSetRows(dbHelper.getResultSet())); } @Test public void rollbackAfterStmtFailureTest() throws Exception { // This is to reproduce the // "commitTransaction called when no transaction in progress" // exception that occurs when a txn is inflight and a parser error // occurs. dbHelper.executeQuery("START TRANSACTION"); try { dbHelper.executeQuery("SELECT 1 FROM WHERE cid=null"); } catch (SQLException se) { // expected - ignore } dbHelper.executeQuery("ROLLBACK"); } @Test public void defaultAndAutoIncrInsertTest() throws Exception { StringBuffer query = new StringBuffer().append("CREATE TABLE simpletest_test_id (") .append("`test_id` INT NOT NULL auto_increment,") .append("`last_prefix` VARCHAR(60) NULL DEFAULT '',").append("`null_column` VARCHAR(10) NULL,") .append(" PRIMARY KEY (`test_id`))"); dbHelper.executeQuery(query.toString()); assertFalse(dbHelper.executeQuery("INSERT INTO simpletest_test_id (test_id) VALUES (default)")); assertFalse(dbHelper.executeQuery( "INSERT INTO simpletest_test_id (last_prefix, null_column) VALUES (default, NULL), (default, 'nonull')")); assertFalse(dbHelper.executeQuery( "INSERT INTO simpletest_test_id (test_id, last_prefix) VALUES (10,'hi'), (default,'there'), (default, default)")); assertEquals(3, dbHelper.getLastInsertID()); assertTrue(dbHelper.executeQuery("SELECT * FROM simpletest_test_id")); int rows = countResultSetRows(dbHelper.getResultSet()); assertEquals(6, rows); } @Test public void escapeProcessingTest() throws Exception { StringBuffer query = new StringBuffer().append("CREATE TABLE test_variable (") .append("`name` VARCHAR(128) NOT NULL,").append("`value` LONGBLOB NULL,") .append(" PRIMARY KEY (`name`)) BROADCAST DISTRIBUTE"); dbHelper.executeQuery(query.toString()); assertFalse( dbHelper.executeQuery("INSERT INTO test_variable (name) VALUES('drupal_test_email_collector')")); StringBuilder value = new StringBuilder().append('\\').append('\\').append("''").append("''").append("{`") .append('\\').append('\\').append('\\').append("'").append(";}}"); String valueExp = StringEscapeUtils.unescapeJava(value.toString()).replaceAll("''", "'"); StringBuilder update = new StringBuilder().append("UPDATE test_variable SET value='").append(value) .append("' WHERE ( (name = 'drupal_test_email_collector' ) )"); assertFalse(dbHelper.executeQuery(update.toString())); assertTrue(dbHelper .executeQuery("SELECT value FROM test_variable WHERE name = 'drupal_test_email_collector'")); dbHelper.getResultSet().next(); String valueFromDB = dbHelper.getResultSet().getString(1); assertTrue(StringUtils.equals(valueExp, valueFromDB)); } @Test // Repro for PE-102 public void largeInsertTest() throws Exception { final int NUM_TUPLES = 3300; final String ONE_TUPLE = "(0,' ', 'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')"; StringBuffer query = new StringBuffer().append("CREATE TABLE sbtest (") .append("id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,") .append("k INTEGER UNSIGNED DEFAULT '0' NOT NULL,").append("c char(120) DEFAULT '' NOT NULL,") .append("pad char(60) DEFAULT '' NOT NULL,").append(" PRIMARY KEY (id))"); dbHelper.executeQuery(query.toString()); StringBuffer insert = new StringBuffer().append("INSERT INTO sbtest (k, c, pad) VALUES "); boolean doneFirst = false; for (int i = 0; i < NUM_TUPLES; i++) { if (doneFirst) insert.append(","); else doneFirst = true; insert.append(ONE_TUPLE); } insert.append(";"); dbHelper.executeQuery(insert.toString()); dbHelper.executeQuery("SELECT COUNT(*) FROM sbtest"); dbHelper.getResultSet().next(); assertEquals(NUM_TUPLES, dbHelper.getResultSet().getLong(1)); } // repro for PE-268 @Test public void dateTypeTest() throws Exception { final String dateValue = "2012-10-03"; final String timeValue = "15:49:36"; final String dateTimeValue = dateValue + " " + timeValue + ".0"; // need a .0 for mS on the end of datetime StringBuffer query = new StringBuffer().append("CREATE TABLE test_date (").append("`datecol` date NULL,") .append("`timecol` time NULL, ").append("`datetimecol` datetime NULL)"); dbHelper.executeQuery(query.toString()); query = new StringBuffer().append("INSERT INTO test_date VALUES ('").append(dateValue).append("','") .append(timeValue).append("','").append(dateTimeValue).append("')"); dbHelper.executeQuery(query.toString()); dbHelper.executeQuery("SELECT * FROM test_date"); ResultSet rs = dbHelper.getResultSet(); rs.next(); assertEquals(dateValue, rs.getString(1)); assertEquals(timeValue, rs.getString(2)); assertEquals(dateTimeValue, rs.getString(3)); } // repro for PE-337 @Test public void zeroDateTest() throws Exception { final String zeroDateTime = "0000-00-00 00:00:00"; final String zeroDate = "0000-00-00"; final String zeroTime = "00:00:00"; StringBuffer query = new StringBuffer().append("CREATE TABLE wp_posts (") .append("ID bigint(20) unsigned NOT NULL,").append("post_datetime datetime NOT NULL default '") .append(zeroDateTime).append("',").append("post_date date NOT NULL default '").append(zeroDate) .append("',").append("post_time time NOT NULL default '").append(zeroTime).append("',") .append("post_timestamp timestamp NOT NULL default '").append(zeroDateTime).append("')"); dbHelper.executeQuery(query.toString()); dbHelper.executeQuery("insert into wp_posts (ID) values (1)"); dbHelper.executeQuery("select post_datetime, post_date, post_time, post_timestamp from wp_posts"); ResultSet rs = dbHelper.getResultSet(); rs.next(); assertNull(rs.getString(1)); assertNull(rs.getString(2)); assertEquals(zeroTime, rs.getString(3)); assertNull(rs.getString(4)); } private static void verifyMetadata(String query, String[] namesAndAliases) throws Exception { assertTrue(dbHelper.executeQuery(query)); ResultSetMetaData rsmd = dbHelper.getResultSet().getMetaData(); verifyMetadata(rsmd, namesAndAliases); } private static void verifyMetadata(ResultSetMetaData rsmd, String[] namesAndAliases) throws SQLException { if (namesAndAliases == null) { for (int i = 1; i <= rsmd.getColumnCount(); i++) { System.out.println(rsmd.getColumnName(i) + " as " + rsmd.getColumnLabel(i)); } return; } int ncolumns = namesAndAliases.length / 2; assertEquals(ncolumns, rsmd.getColumnCount()); for (int i = 0; i < ncolumns; i++) { String ename = namesAndAliases[2 * i]; String ealias = namesAndAliases[2 * i + 1]; assertEquals(ename, rsmd.getColumnName(i + 1)); assertEquals(ealias, rsmd.getColumnLabel(i + 1)); } } @Test public void infoSchemaColumnMetadataTest() throws Exception { StringBuffer query = new StringBuffer().append("SHOW DATABASES"); dbHelper.executeQuery(query.toString()); ResultSet rs = dbHelper.getResultSet(); ResultSetMetaData rsmd = rs.getMetaData(); // make sure the column header is not empty assertTrue(!StringUtils.isEmpty(rsmd.getColumnLabel(1))); assertEquals(ShowSchema.Database.NAME, rsmd.getColumnLabel(1)); } private void loadSQLFile(Class<?> testClass, String fileName) throws Exception { InputStream is = testClass.getResourceAsStream(fileName); if (is != null) { logger.info("Reading SQL statements from " + fileName); dbHelper.setDisconnectAfterProcess(false); dbHelper.executeFromStream(is); try { is.close(); } catch (IOException e) { } } } // repro for PE-481 @Test(enabled = false) public void generationAddTest() throws Exception { loadSQLFile(ClientTestNG.class, "large-table-load.sql"); dbHelper.executeQuery("ALTER PERSISTENT GROUP " + PEConstants.DEFAULT_GROUP_NAME + " ADD GENERATION site3,site4,site5,site6"); dbHelper.executeQuery( "SHOW GENERATION SITES WHERE persistent_group='" + PEConstants.DEFAULT_GROUP_NAME + "'"); assertEquals(6, countResultSetRows(dbHelper.getResultSet())); } @Test public void pe1428Test() throws Exception { dbHelper.executeQuery("CREATE RANGE test1range (int) persistent group " + PEConstants.DEFAULT_GROUP_NAME); dbHelper.executeQuery("CREATE TABLE test1(col1 int) RANGE DISTRIBUTE ON (col1) USING test1range"); dbHelper.executeQuery("INSERT INTO test1 VALUES (1),(2),(3),(4),(5)"); dbHelper.executeQuery("SELECT count(*), @dve_sitename FROM test1 GROUP BY 2"); assertEquals(2, countResultSetRows(dbHelper.getResultSet())); dbHelper.executeQuery("DROP TABLE test1"); dbHelper.executeQuery("DROP RANGE test1range"); } }