/* This file is part of VoltDB.
* Copyright (C) 2008-2017 VoltDB Inc.
*
* Permission is hereby granted, free of charge, to any person obtaining
* a copy of this software and associated documentation files (the
* "Software"), to deal in the Software without restriction, including
* without limitation the rights to use, copy, modify, merge, publish,
* distribute, sublicense, and/or sell copies of the Software, and to
* permit persons to whom the Software is furnished to do so, subject to
* the following conditions:
*
* The above copyright notice and this permission notice shall be
* included in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
* IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
* OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
* ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
* OTHER DEALINGS IN THE SOFTWARE.
*/
package org.hsqldb_voltpatches;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringReader;
import java.io.UnsupportedEncodingException;
import java.net.URL;
import java.net.URLDecoder;
import java.util.ArrayList;
import org.apache.commons.lang3.tuple.Pair;
import org.hsqldb_voltpatches.HSQLInterface.HSQLParseException;
import org.xml.sax.ErrorHandler;
import org.xml.sax.SAXException;
import org.xml.sax.SAXParseException;
import junit.framework.TestCase;
public class TestHSQLDB extends TestCase {
class VoltErrorHandler implements ErrorHandler {
@Override
public void error(SAXParseException exception) throws SAXException {
throw exception;
}
@Override
public void fatalError(SAXParseException exception) throws SAXException {
}
@Override
public void warning(SAXParseException exception) throws SAXException {
throw exception;
}
}
public class StringInputStream extends InputStream {
StringReader sr;
public StringInputStream(String value) { sr = new StringReader(value); }
@Override
public int read() throws IOException { return sr.read(); }
}
public void testCatalogRead() {
HSQLInterface hsql = HSQLInterface.loadHsqldb();
try {
hsql.runDDLCommand("create table test (cash integer default 23);");
} catch (HSQLParseException e1) {
e1.printStackTrace();
fail();
}
VoltXMLElement xml;
try {
xml = hsql.getXMLFromCatalog();
assertNotNull(xml);
} catch (HSQLParseException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
fail();
}
try {
xml = hsql.getXMLCompiledStatement("select * from test;");
assertNotNull(xml);
} catch (HSQLParseException e) {
e.printStackTrace();
fail();
}
//* enable to debug */ System.out.println(xml);
}
public HSQLInterface setupTPCCDDL() {
HSQLInterface hsql = HSQLInterface.loadHsqldb();
URL url = getClass().getResource("hsqltest-ddl.sql");
try {
hsql.runDDLFile(URLDecoder.decode(url.getPath(), "UTF-8"));
} catch (HSQLParseException e) {
e.printStackTrace();
fail();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
fail();
}
return hsql;
}
/*public void testWithTPCCDDL() throws HSQLParseException {
HSQLInterface hsql = setupTPCCDDL();
String xmlCatalog = hsql.getXMLFromCatalog();
//* enable to debug *-/ System.out.println(xmlCatalog);
StringInputStream xmlStream = new StringInputStream(xmlCatalog);
Document doc = null;
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
factory.setValidating(true);
try {
DocumentBuilder builder = factory.newDocumentBuilder();
doc = builder.parse(xmlStream);
assertNotNull(doc);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
public void testDMLFromTPCCNewOrder() {
HSQLInterface hsql = setupTPCCDDL();
URL url = getClass().getResource("hsqltest-dml.sql");
HSQLFileParser.Statement[] stmts = null;
try {
String dmlPath = URLDecoder.decode(url.getPath(), "UTF-8");
stmts = HSQLFileParser.getStatements(dmlPath);
assertNotNull(stmts);
} catch (HSQLParseException e) {
e.printStackTrace();
fail();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
fail();
}
for (HSQLFileParser.Statement stmt : stmts) {
//* enable to debug *-/ System.out.println(stmt.statement);
String xml = null;
try {
xml = hsql.getXMLCompiledStatement(stmt.statement);
} catch (HSQLParseException e1) {
e1.printStackTrace();
}
assertNotNull(xml);
//* enable to debug *-/ System.out.println(xml);
StringInputStream xmlStream = new StringInputStream(xml);
Document doc = null;
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
factory.setValidating(true);
try {
DocumentBuilder builder = factory.newDocumentBuilder();
builder.setErrorHandler(new VoltErrorHandler());
doc = builder.parse(xmlStream);
} catch (Exception e) {
e.printStackTrace();
fail();
}
assertNotNull(doc);
}
}*/
private static void expectFailStmt(HSQLInterface hsql, String stmt, String errorPart) {
try {
VoltXMLElement xml = hsql.getXMLCompiledStatement(stmt);
System.out.println(xml.toString());
fail();
}
catch (Exception e) {
assertTrue(e.getMessage().contains(errorPart));
}
}
public void testSqlInToXML() throws HSQLParseException {
HSQLInterface hsql = setupTPCCDDL();
VoltXMLElement stmt;
// The next few statements should work, also with a trivial test
stmt = hsql.getXMLCompiledStatement("select * from new_order");
assertTrue(stmt.toString().contains("NO_W_ID"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id = 5");
assertTrue(stmt.toString().contains("equal"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id in (5,7);");
assertTrue(stmt.toString().contains("vector"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id in (?);");
assertTrue(stmt.toString().contains("vector"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id in (?,5,3,?);");
assertTrue(stmt.toString().contains("vector"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id not in (?,5,3,?);");
assertTrue(stmt.toString().contains("vector"));
stmt = hsql.getXMLCompiledStatement("select * from warehouse where w_name not in (?, 'foo');");
assertTrue(stmt.toString().contains("vector"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id in (no_d_id, no_o_id, ?, 7);");
assertTrue(stmt.toString().contains("vector"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id in (abs(-1), ?, 17761776);");
assertTrue(stmt.toString().contains("vector"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id in (abs(17761776), ?, 17761776) and no_d_id in (abs(-1), ?, 17761776);");
assertTrue(stmt.toString().contains("vector"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id in ?;");
assertTrue(stmt.toString().contains("vector"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where no_w_id in (select w_id from warehouse);");
assertTrue(stmt.toString().contains("tablesubquery"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where exists (select w_id from warehouse);");
assertTrue(stmt.toString().contains("tablesubquery"));
stmt = hsql.getXMLCompiledStatement("select * from new_order where not exists (select w_id from warehouse);");
assertTrue(stmt.toString().contains("tablesubquery"));
// The ones below here should continue to give sensible errors
expectFailStmt(hsql, "select * from new_order where no_w_id <> (5, 7, 8);",
"row column count mismatch");
// Fixed as ENG-9869 bogus plan when ORDER BY of self-join uses wrong table alias for GROUP BY key
expectFailStmt(hsql, "select x.no_w_id from new_order x, new_order y group by x.no_w_id order by y.no_w_id;",
"expression not in aggregate or GROUP BY columns");
}
public void testVarbinary() {
HSQLInterface hsql = HSQLInterface.loadHsqldb();
URL url = getClass().getResource("hsqltest-varbinaryddl.sql");
try {
hsql.runDDLFile(URLDecoder.decode(url.getPath(), "UTF-8"));
} catch (Exception e) {
e.printStackTrace();
fail();
}
String sql = "SELECT * FROM BT;";
VoltXMLElement xml = null;
try {
xml = hsql.getXMLCompiledStatement(sql);
assertNotNull(xml);
} catch (HSQLParseException e1) {
e1.printStackTrace();
fail();
}
//* enable to debug */ System.out.println(xml);
sql = "INSERT INTO BT VALUES (?, ?, ?);";
xml = null;
try {
xml = hsql.getXMLCompiledStatement(sql);
assertNotNull(xml);
} catch (HSQLParseException e1) {
e1.printStackTrace();
fail();
}
//* enable to debug */ System.out.println(xml);
}
public void testInsertIntoSelectFrom() {
HSQLInterface hsql = setupTPCCDDL();
String sql = "INSERT INTO new_order (NO_O_ID, NO_D_ID, NO_W_ID) SELECT O_ID, O_D_ID+1, CAST(? AS INTEGER) FROM ORDERS;";
VoltXMLElement xml = null;
try {
xml = hsql.getXMLCompiledStatement(sql);
assertNotNull(xml);
} catch (HSQLParseException e1) {
e1.printStackTrace();
fail();
}
}
public void testSumStarFails() {
HSQLInterface hsql = HSQLInterface.loadHsqldb();
assertNotNull(hsql);
// The elements of this ArrayList tells us the statement to
// execute, and whether we expect an exception when we execute
// the statement. If the first element of the pair begins
// with the string "Expected", we expect an error. If
// the First begins with something else, we don't expect errors.
// In either case and the string tells what to complain about.
ArrayList<Pair<String, String>> allddl = new ArrayList<Pair<String, String>>();
allddl.add(Pair.of("Unexpected Table Creation Failure.", "CREATE TABLE t (i INTEGER, j INTEGER);"));
allddl.add(Pair.of("Unexpected count(*) call failure.", "CREATE VIEW vw1 (sm) as SELECT count(*) from t group by i;"));
allddl.add(Pair.of("Expected sum(*) call failure.", "CREATE VIEW vw (sm) AS SELECT sum(*) from t group by i;"));
for (Pair<String, String> ddl : allddl) {
boolean sawException = false;
try {
hsql.runDDLCommand(ddl.getRight());
} catch (HSQLParseException e1) {
sawException = true;
}
assertEquals(ddl.getLeft(), ddl.getLeft().startsWith("Expected", 0), sawException);
}
}
/*public void testSimpleSQL() {
HSQLInterface hsql = setupTPCCDDL();
String sql = "SELECT O_ID, O_CARRIER_ID, O_ENTRY_D FROM ORDERS WHERE O_W_ID = ? AND O_D_ID = ? AND O_C_ID = ? ORDER BY O_ID DESC LIMIT 1;";
System.out.println(sql);
String xml = null;
try {
xml = hsql.getXMLCompiledStatement(sql);
assertNotNull(xml);
} catch (HSQLParseException e1) {
e1.printStackTrace();
fail();
}
//* enable to debug *-/ System.out.println(xml);
StringInputStream xmlStream = new StringInputStream(xml);
Document doc = null;
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
factory.setValidating(true);
try {
DocumentBuilder builder = factory.newDocumentBuilder();
builder.setErrorHandler(new VoltErrorHandler());
doc = builder.parse(xmlStream);
assertNotNull(doc);
} catch (Exception e) {
e.printStackTrace();
fail();
}
}*/
/*public void testDeleteIssue() {
HSQLInterface hsql = setupTPCCDDL();
//String stmt = "delete from NEW_ORDER where NO_O_ID = 1;";
//String stmt = "delete from NEW_ORDER where NO_O_ID = 1 and NO_D_ID = 1 and NO_W_ID = 1;";
String stmt = "delete from NEW_ORDER where NO_O_ID = 1 and NO_D_ID = 1 and NO_W_ID = 1 and NO_W_ID = 3;";
String xml = null;
try {
xml = hsql.getXMLCompiledStatement(stmt);
assertNotNull(xml);
} catch (HSQLParseException e1) {
e1.printStackTrace();
fail();
}
//* enable to debug *-/ System.out.println(xml);
}*/
public static void main(String args[]) {
//new TestHSQLDB().testWithTPCCDDL();
}
}