/* 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.voltdb.compiler;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.net.URLDecoder;
import junit.framework.TestCase;
import org.voltdb.VoltType;
import org.voltdb.catalog.Column;
import org.voltdb.catalog.Database;
import org.voltdb.catalog.Index;
import org.voltdb.catalog.Table;
import org.voltdb.utils.BuildDirectoryUtils;
public class TestVoltCompilerAlterDropTable extends TestCase {
String testout_jar;
String myproc = "CREATE PROCEDURE MyTableProcedure AS SELECT column2_integer FROM mytable;\n";
@Override
public void setUp() {
testout_jar = BuildDirectoryUtils.getBuildDirectoryPath() + File.pathSeparator + "testout.jar";
}
@Override
public void tearDown() {
File tjar = new File(testout_jar);
tjar.delete();
}
//Check given col type if catalog was compiled.
private void verifyTableColumnType(VoltCompiler compiler, String tableName, String colName, VoltType type) {
Database db = compiler.m_catalog.getClusters().get("cluster").getDatabases().get("database");
Table table = db.getTables().get(tableName);
Column col = table.getColumns().get(colName);
assertNotNull(col);
assertEquals(0, type.compareTo(VoltType.get((byte) col.getType())));
}
//Check given col nullability if catalog was compiled.
private void verifyTableColumnNullable(VoltCompiler compiler, String tableName, String colName, boolean shouldBeNullable) {
Database db = compiler.m_catalog.getClusters().get("cluster").getDatabases().get("database");
Table table = db.getTables().get(tableName);
Column col = table.getColumns().get(colName);
assertNotNull(col);
assertEquals(shouldBeNullable, col.getNullable());
}
//Check given col size
private void verifyTableColumnSize(VoltCompiler compiler, String tableName, String colName, int sz) {
Database db = compiler.m_catalog.getClusters().get("cluster").getDatabases().get("database");
Table table = db.getTables().get(tableName);
Column col = table.getColumns().get(colName);
assertNotNull(col);
assertEquals(sz, col.getSize());
}
//Check given col does exists
private void verifyTableColumnExists(VoltCompiler compiler, String tableName, String colName) {
Database db = compiler.m_catalog.getClusters().get("cluster").getDatabases().get("database");
Table table = db.getTables().get(tableName);
Column col = table.getColumns().get(colName);
assertNotNull(col);
}
//Check given col does not exists
private void verifyTableColumnGone(VoltCompiler compiler, String tableName, String colName) {
Database db = compiler.m_catalog.getClusters().get("cluster").getDatabases().get("database");
Table table = db.getTables().get(tableName);
Column col = table.getColumns().get(colName);
assertNull(col);
}
//Check given index exists
private void verifyIndexExists(VoltCompiler compiler, String tableName, String idxName) {
Database db = compiler.m_catalog.getClusters().get("cluster").getDatabases().get("database");
Table table = db.getTables().get(tableName);
Index idx = table.getIndexes().get(idxName);
assertNotNull(idx);
}
//Check given index exists
private void verifyIndexGone(VoltCompiler compiler, String tableName, String idxName) {
Database db = compiler.m_catalog.getClusters().get("cluster").getDatabases().get("database");
Table table = db.getTables().get(tableName);
if (table == null) {
//Index must be gone.
return;
}
Index idx = table.getIndexes().get(idxName);
assertNull(idx);
}
//Check given table is gone
private void verifyTableGone(VoltCompiler compiler, String tableName) {
Database db = compiler.m_catalog.getClusters().get("cluster").getDatabases().get("database");
Table table = db.getTables().get(tableName);
assertNull(table);
}
//Check given table exists
private void verifyTableExists(VoltCompiler compiler, String tableName) {
Database db = compiler.m_catalog.getClusters().get("cluster").getDatabases().get("database");
Table table = db.getTables().get(tableName);
assertNotNull(table);
}
public void testAlterTable() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "alter table mytable add column newcol varchar(50);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnType(compiler, "mytable", "newcol", VoltType.STRING);
verifyTableColumnSize(compiler, "mytable", "newcol", 50);
}
public void testAlterTableWithProcedure() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "alter table mytable add column newcol varchar(50);\n"
+ myproc;
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnType(compiler, "mytable", "newcol", VoltType.STRING);
verifyTableColumnSize(compiler, "mytable", "newcol", 50);
}
public void testAlterTableWithProcedureUsingWrongColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "alter table mytable drop column column2_integer;\n"
+ myproc;
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//Failed to plan for statement
int foundPlanError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.contains("Failed to plan for statement")) {
foundPlanError++;
}
}
assertEquals(1, foundPlanError);
}
public void testAlterTableWithProcedureUsingDroppableColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "alter table mytable drop column pkey;\n"
+ myproc;
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnGone(compiler, "mytable", "pkey");
}
public void testAlterTableOnView() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "CREATE VIEW v_mytable\n"
+ "(\n"
+ " pkey\n"
+ ", num_cont\n"
+ ")\n"
+ "AS\n"
+ " SELECT pkey\n"
+ " , COUNT(*)\n"
+ " FROM mytable\n"
+ " GROUP BY pkey\n"
+ ";"
+ "alter table v_mytable drop column column2_integer;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//user lacks privilege or object not found: V_MYTABLE
int foundDropError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.contains("user lacks privilege or object not found: V_MYTABLE")) {
foundDropError++;
}
}
assertEquals(1, foundDropError);
}
public void testAlterTableDropUnrelatedColumnWithView() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "CREATE VIEW v_mytable\n"
+ "(\n"
+ " pkey\n"
+ ", num_cont\n"
+ ")\n"
+ "AS\n"
+ " SELECT pkey\n"
+ " , COUNT(*)\n"
+ " FROM mytable\n"
+ " GROUP BY pkey\n"
+ ";"
+ "alter table mytable drop column column2_integer;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnGone(compiler, "mytable", "column2_integer");
}
public void testAlterTableDropColumnWithView() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "CREATE VIEW v_mytable\n"
+ "(\n"
+ " pkey\n"
+ ", num_cont\n"
+ ")\n"
+ "AS\n"
+ " SELECT pkey\n"
+ " , COUNT(*)\n"
+ " FROM mytable\n"
+ " GROUP BY pkey\n"
+ ";"
+ "alter table mytable drop column pkey;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//column is referenced in: PUBLIC.V_MYTABLE
int foundDropError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.contains("column is referenced in: PUBLIC.V_MYTABLE")) {
foundDropError++;
}
}
assertEquals(1, foundDropError);
}
public void testAlterTableDropColumnWithViewCascade() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "CREATE VIEW v_mytable\n"
+ "(\n"
+ " pkey\n"
+ ", num_cont\n"
+ ")\n"
+ "AS\n"
+ " SELECT pkey\n"
+ " , COUNT(*)\n"
+ " FROM mytable\n"
+ " GROUP BY pkey\n"
+ ";"
+ "alter table mytable drop column pkey cascade;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnGone(compiler, "mytable", "pkey");
}
public void testAlterTableAlterColumnWithCountView() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "CREATE VIEW v_mytable\n"
+ "(\n"
+ " pkey\n"
+ ", num_cont\n"
+ ")\n"
+ "AS\n"
+ " SELECT pkey\n"
+ " , COUNT(pkey)\n"
+ " FROM mytable\n"
+ " GROUP BY pkey\n"
+ ";"
+ "alter table mytable alter column pkey VARCHAR(20);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//dependent objects exist
int foundDepError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.toLowerCase().contains("dependent objects exist")) {
foundDepError++;
}
}
assertEquals(1, foundDepError);
}
public void testAlterTableAlterColumnWithSumViewToNonSummableType() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "CREATE VIEW v_mytable\n"
+ "(\n"
+ " pkey\n"
+ ", num_cont\n"
+ ")\n"
+ "AS\n"
+ " SELECT pkey\n"
+ " , SUM(pkey)\n"
+ " FROM mytable\n"
+ " GROUP BY pkey\n"
+ ";"
+ "alter table mytable alter column pkey VARCHAR(20);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//dependent objects exist
int foundDepError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.toLowerCase().contains("dependent objects exist")) {
foundDepError++;
}
}
assertEquals(1, foundDepError);
}
public void testAlterTableAlterColumnPossibleWithSumView() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "CREATE VIEW v_mytable\n"
+ "(\n"
+ " pkey\n"
+ ", num_cont\n"
+ ")\n"
+ "AS\n"
+ " SELECT pkey\n"
+ " , SUM(pkey)\n"
+ " FROM mytable\n"
+ " GROUP BY pkey\n"
+ ";"
+ "alter table mytable alter column pkey smallint;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//dependent objects exist - This should technically be ok as int to smallint with count should work....
int foundDepError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.toLowerCase().contains("dependent objects exist")) {
foundDepError++;
}
}
assertEquals(1, foundDepError);
}
//Index
public void testAlterTableDropColumnWithIndex() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "create unique index pkey_idx on mytable(pkey);\n"
+ "alter table mytable drop column column2_integer;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnGone(compiler, "mytable", "column2_integer");
verifyIndexExists(compiler, "mytable", "pkey_idx");
}
public void testAlterTableDropColumnWithIndexRecreateColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "create unique index pkey_idx on mytable(pkey);\n"
+ "alter table mytable drop column pkey;\n"
+ "alter table mytable add column pkey integer;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnExists(compiler, "mytable", "pkey");
verifyIndexGone(compiler, "mytable", "pkey_idx");
}
public void testAlterTableDropRelatedColumnWithIndex() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "create unique index pkey_idx on mytable(pkey, column2_integer);\n" // This index should go
+ "create unique index pkey_idxx on mytable(column2_integer);\n" //This index should remain
+ "alter table mytable drop column pkey;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnGone(compiler, "mytable", "pkey");
verifyIndexExists(compiler, "mytable", "pkey_idxx");
verifyIndexGone(compiler, "mytable", "pkey_idx");
}
public void testAlterTableBadVarchar() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "alter table mytable add column newcol varchar(0);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//precision or scale out of range
int foundSZError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.toLowerCase().contains("precision or scale out of range")) {
foundSZError++;
}
}
assertEquals(1, foundSZError);
}
public void testAlterTableAddConstraint() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(50), column2_integer integer);\n"
+ "alter table mytable alter column pkey varchar(50) NOT NULL;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
// Check that constraint is now valid.
verifyTableColumnNullable(compiler, "mytable", "pkey", false);
}
public void testAlterTableRemoveConstraint() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(50) NOT NULL, column2_integer integer);\n"
+ "alter table mytable alter column pkey varchar(50) NOT NULL;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
// Check that constraint is still valid.
verifyTableColumnNullable(compiler, "mytable", "pkey", false);
}
public void testAlterTableOverflowVarchar() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "alter table mytable add column newcol varchar(" + String.valueOf(VoltType.MAX_VALUE_LENGTH + 1) + ");\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//> 1048576 char maximum.
int foundSZError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.toLowerCase().contains("> 1048576 char maximum.")) {
foundSZError++;
}
}
assertEquals(1, foundSZError);
}
public void testAlterTableOverflowVarcharExisting() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2 varchar(50));\n"
+ "alter table mytable alter column column2 varchar(" + String.valueOf(VoltType.MAX_VALUE_LENGTH + 1) + ");\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//> 1048576 char maximum.
int foundSZError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.toLowerCase().contains("> 1048576 char maximum.")) {
foundSZError++;
}
}
assertEquals(1, foundSZError);
}
public void testTooManyColumnTable() throws IOException {
String schemaPath = "";
URL url = TestVoltCompilerAlterDropTable.class.getResource("justwidetable-ddl.sql");
schemaPath = URLDecoder.decode(url.getPath(), "UTF-8");
FileReader fr = new FileReader(new File(schemaPath));
BufferedReader br = new BufferedReader(fr);
StringBuilder ddl1 = new StringBuilder("");
String line;
while ((line = br.readLine()) != null) {
ddl1.append(line).append("\n");
}
br.close();
ddl1.append("alter table JUST_ENOUGH_WIDE_COLUMNS add column COL01022 INTEGER DEFAULT '0' NOT NULL;");
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(ddl1.toString(), testout_jar);
assertFalse(success);
//(max is 1024)
int foundSZError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.toLowerCase().contains("(max is 1024)")) {
foundSZError++;
}
}
assertEquals(1, foundSZError);
}
public void testTooManyColumnThenDropBelowLimit() throws IOException {
String schemaPath = "";
URL url = TestVoltCompilerAlterDropTable.class.getResource("toowidetable-ddl.sql");
schemaPath = URLDecoder.decode(url.getPath(), "UTF-8");
FileReader fr = new FileReader(new File(schemaPath));
BufferedReader br = new BufferedReader(fr);
StringBuilder ddl1 = new StringBuilder("");
String line;
while ((line = br.readLine()) != null) {
ddl1.append(line).append("\n");
}
br.close();
ddl1.append("alter table MANY_COLUMNS drop column COL01022;");
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(ddl1.toString(), testout_jar);
assertTrue(success);
verifyTableColumnGone(compiler, "MANY_COLUMNS", "COL01022");
}
public void testAlterTableRowLimit() throws IOException {
//Just enough big row one byte over and we will fail to compile.
String simpleSchema1 = "create table mytable (val1 varchar(1048572), val2 varchar(1048572));\n";
VoltCompiler compiler = new VoltCompiler(false);
boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
//Still over after alter
simpleSchema1 = "create table mytable (val1 varchar(1048576), val2 varchar(1048576));"
+ "alter table mytable alter column val1 varchar(1048572);\n";
compiler = new VoltCompiler(false);
success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//Going under after alter
simpleSchema1 = "create table mytable (val1 varchar(1048576), val2 varchar(1048576));"
+ "alter table mytable alter column val1 varchar(1048572);\n"
+ "alter table mytable alter column val2 varchar(1048572);\n";
compiler = new VoltCompiler(false);
success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
//Going over: after alter going under and back over
simpleSchema1 = "create table mytable (val1 varchar(1048576), val2 varchar(1048576));"
+ "alter table mytable alter column val1 varchar(1048572);\n"
+ "alter table mytable alter column val2 varchar(1048572);\n"
+ "alter table mytable alter column val1 varchar(1048576);\n";
compiler = new VoltCompiler(false);
success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
}
//We will be successful in compiling but a WARN should appear.
public void testAlterTableAddForeignKey() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "create table ftable (pkey integer, column2_integer integer UNIQUE);\n"
+ "ALTER TABLE mytable ADD FOREIGN KEY (column2_integer) REFERENCES ftable(column2_integer);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnType(compiler, "mytable", "pkey", VoltType.INTEGER);
verifyTableColumnType(compiler, "ftable", "pkey", VoltType.INTEGER);
// verify that warnings exist for foreign key
int foundFKWarnings = 0;
for (VoltCompiler.Feedback f : compiler.m_warnings) {
if (f.message.toLowerCase().contains("foreign")) {
foundFKWarnings++;
}
}
assertEquals(1, foundFKWarnings);
}
public void testAlterUnknownTable() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "alter table mytablenonexistent add column newcol varchar(50);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//type not found or user lacks privilege:
int foundMissingError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.contains("user lacks privilege or object not found:")) {
foundMissingError++;
}
}
assertEquals(1, foundMissingError);
}
public void testCreateDropAlterTable() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "drop table mytable;\n"
+ "alter table mytable add column newcol varchar(50);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//type not found or user lacks privilege:
int foundMissingError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.contains("user lacks privilege or object not found:")) {
foundMissingError++;
}
}
assertEquals(1, foundMissingError);
}
public void testCreateDropCreateAlterTable() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "drop table mytable;\n"
+ "create table mytable (pkey integer, column2_integer integer);\n"
+ "alter table mytable add column newcol varchar(50);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnType(compiler, "mytable", "pkey", VoltType.INTEGER);
}
public void testAlterTableBadDowngradeOfPartitionColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer NOT NULL, column2_integer integer);\n"
+ "PARTITION TABLE mytable ON COLUMN pkey;"
+ "alter table mytable alter column pkey NULL;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//type not found or user lacks privilege:
int foundMissingError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.contains("type not found or user lacks privilege:")) {
foundMissingError++;
}
}
assertEquals(1, foundMissingError);
}
public void testAlterTableDropOfPartitionColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer NOT NULL, column2_integer integer);\n"
+ "PARTITION TABLE mytable ON COLUMN pkey;"
+ "alter table mytable drop column pkey;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
}
public void testAlterTableSizeChangeAndConstraintChangeOfPartitionColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20) NOT NULL, column2_integer integer);\n"
+ "PARTITION TABLE mytable ON COLUMN pkey;"
+ "alter table mytable alter column pkey varchar(50);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//Partition columns must be constrained "NOT NULL"
int foundConstraintError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.contains("Partition columns must be constrained \"NOT NULL\"")) {
foundConstraintError++;
}
}
assertEquals(1, foundConstraintError);
}
public void testAlterTableSizeChangeAndKeepConstraintOfPartitionColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20) NOT NULL, column2_integer integer);\n"
+ "PARTITION TABLE mytable ON COLUMN pkey;"
+ "alter table mytable alter column pkey varchar(50) NOT NULL;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
// Check that constraint is still valid.
verifyTableColumnNullable(compiler, "mytable", "pkey", false);
}
public void testAlterTableSizeChangeAndImplicitlyKeepConstraintOfPartitionColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20) NOT NULL, column2_integer integer);\n"
+ "PARTITION TABLE mytable ON COLUMN pkey;"
+ "alter table mytable alter column pkey set data type varchar(50);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
// Check that constraint is still valid.
verifyTableColumnNullable(compiler, "mytable", "pkey", false);
}
public void testAlterTableAddPartitionColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20) NOT NULL, column2_integer integer);\n"
+ "alter table mytable add column pkey2 varchar(500);\n"
+ "PARTITION TABLE mytable ON COLUMN pkey2;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
//Partition columns must be constrained
int foundConstraintError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.contains("Partition columns must be constrained")) {
foundConstraintError++;
}
}
assertEquals(1, foundConstraintError);
}
//This is create procedure on valid column add after alter...should be successful.
public void testAlterTableAddNonNULLPartitionColumn() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20) NOT NULL, column2_integer integer);\n"
+ "alter table mytable add column pkey2 varchar(500) NOT NULL;\n"
+ "PARTITION TABLE mytable ON COLUMN pkey2;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnSize(compiler, "mytable", "pkey2", 500);
// Check that constraint is valid.
verifyTableColumnNullable(compiler, "mytable", "pkey2", false);
}
public void testAlterTableRedefColWithNonNULLThenPartitionOnIt() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20), column2_integer integer);\n"
+ "alter table mytable alter column pkey varchar(20) NOT NULL;\n"
+ "PARTITION TABLE mytable ON COLUMN pkey;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
// Check that constraint is valid.
verifyTableColumnNullable(compiler, "mytable", "pkey", false);
}
public void testAlterTableSetColNonNULLThenPartitionOnIt() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20), column2_integer integer);\n"
+ "alter table mytable alter column pkey set NOT NULL;\n"
+ "PARTITION TABLE mytable ON COLUMN pkey;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
// Check that constraint is valid.
verifyTableColumnNullable(compiler, "mytable", "pkey", false);
}
public void testAlterTableRedundantlySetColNonNULLThenPartitionOnIt() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20) NOT NULL, column2_integer integer);\n"
+ "alter table mytable alter column pkey set NOT NULL;\n"
+ "PARTITION TABLE mytable ON COLUMN pkey;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
// Check that constraint is valid.
verifyTableColumnNullable(compiler, "mytable", "pkey", false);
}
public void testAlterTableRedefColToDropNonNULLThenFailToPartitionOnIt() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20) NOT NULL, column2_integer integer);\n"
+ "alter table mytable alter column pkey varchar(20);\n"
+ "PARTITION TABLE mytable ON COLUMN pkey;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
int foundNullPartitionColError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.toLowerCase().contains("partition columns must be constrained ")) {
foundNullPartitionColError++;
}
}
assertEquals(1, foundNullPartitionColError);
}
public void testAlterTableFailToAddDuplicateColumnName() throws IOException {
final String simpleSchema1
= "create table mytable (pkey varchar(20) NOT NULL, column2_integer integer);\n"
+ "alter table mytable add column pkey varchar(20);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
int foundAlreadyExistsError = 0;
for (VoltCompiler.Feedback f : compiler.m_errors) {
if (f.message.toLowerCase().contains("name already exists ")) {
foundAlreadyExistsError++;
}
}
assertEquals(1, foundAlreadyExistsError);
}
//DROP TABLE TESTS from here
public void testDropTable() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "drop table mytable;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableGone(compiler, "mytable");
}
public void testDropTableThatDoesNotExists() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "drop table mytablefoo;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
}
public void testDropTableIfExists() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "drop table mytablenonexistant if exists;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableColumnType(compiler, "mytable", "pkey", VoltType.INTEGER);
}
public void testDropTableWithIndex() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "create unique index pkey_idx on mytable(pkey);\n"
+ "drop table mytable;\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableGone(compiler, "mytable");
verifyIndexGone(compiler, "mytable", "pkey_idx");
}
public void testDropTableWithIndexAndReCreateTable() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "create unique index pkey_idx on mytable(pkey);\n"
+ "drop table mytable;\n"
+ "create table mytable (pkey integer, column2_integer integer);\n";
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertTrue(success);
verifyTableExists(compiler, "mytable");
verifyIndexGone(compiler, "mytable", "pkey_idx");
}
//Should fail as table should not exist
public void testDropTableWithProcedure() throws IOException {
final String simpleSchema1
= "create table mytable (pkey integer, column2_integer integer);\n"
+ "drop table mytable;\n"
+ myproc;
final VoltCompiler compiler = new VoltCompiler(false);
final boolean success = compiler.compileDDLString(simpleSchema1, testout_jar);
assertFalse(success);
}
}