/**
* Copyright (C) 2009-2013 FoundationDB, LLC
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* 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/>.
*/
package com.foundationdb.sql.aisddl;
import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;
import static org.junit.Assert.assertNotNull;
import static org.junit.Assert.assertNull;
import com.foundationdb.ais.model.ForeignKey;
import com.foundationdb.server.error.UnsupportedFunctionInIndexException;
import com.foundationdb.server.error.UnsupportedSQLException;
import org.junit.Test;
import com.foundationdb.ais.model.Column;
import com.foundationdb.ais.model.Index;
import com.foundationdb.ais.model.Table;
import com.foundationdb.ais.model.TableIndex;
import com.foundationdb.server.error.DuplicateSequenceNameException;
import com.foundationdb.server.error.NoSuchTableException;
import com.foundationdb.server.service.servicemanager.GuicedServiceManager;
import java.util.Map;
public class TableDDLIT extends AISDDLITBase {
private static final String DROP_T1 = "DROP TABLE test.t1";
private static final String DROP_T2 = "DROP TABLE test.t2";
@Override
protected GuicedServiceManager.BindingsConfigurationProvider serviceBindingsProvider() {
return super.serviceBindingsProvider();
}
@Override
protected Map<String, String> startupConfigProperties() {
return uniqueStartupConfigProperties(TableDDLIT.class);
}
@Test (expected=NoSuchTableException.class)
public void testDropFail() throws Exception {
String sql = "DROP TABLE test.not_here";
executeDDL(sql);
}
@Test
public void testCreateSimple() throws Exception {
String sqlCreate = "CREATE TABLE test.T1 (c1 integer not null primary key)";
executeDDL(sqlCreate);
assertNotNull (ais().getTable ("test", "t1"));
executeDDL(DROP_T1);
assertNull (ais().getTable("test", "t1"));
}
@Test
public void testCreateIndexes() throws Exception {
String sql = "CREATE TABLE test.t1 (c1 integer not null primary key, " +
"c2 integer not null, " +
"constraint c2 unique (c2))";
executeDDL(sql);
Table table = ais().getTable("test", "t1");
assertNotNull (table);
assertNotNull (table.getPrimaryKey());
assertEquals ("PRIMARY", table.getPrimaryKey().getIndex().getIndexName().getName());
assertEquals (2, table.getIndexes().size());
assertNotNull (table.getIndex("PRIMARY"));
assertNotNull (table.getIndex("c2"));
executeDDL(DROP_T1);
}
@Test
public void testCreateJoin() throws Exception {
int startNo = ais().getJoins().size();
String sql1 = "CREATE TABLE test.t1 (c1 integer not null primary key)";
String sql2 = "CREATE TABLE test.t2 (c1 integer not null primary key, " +
"c2 integer not null, grouping foreign key (c2) references test.t1)";
executeDDL(sql1);
executeDDL(sql2);
Table table = ais().getTable("test", "t2");
assertNotNull (table);
assertEquals (1, ais().getJoins().size() - startNo);
assertNotNull (table.getParentJoin());
executeDDL(DROP_T2);
executeDDL(DROP_T1);
}
@Test
public void testCreateAutoIncrement() throws Exception {
String sql = "CREATE TABLE test.t1 (c1 integer not null primary key generated by default as identity " +
"(start with 1000, increment by 1))";
executeDDL(sql);
Table table = ais().getTable("test", "t1");
assertNotNull(table);
assertEquals("INT", table.getColumn(0).getTypeName());
assertEquals(table.getPrimaryKey().getColumns().get(0), table.getColumn(0));
assertEquals(1000L, table.getColumn(0).getIdentityGenerator().getStartsWith());
}
@Test
public void testCreateInteger() throws Exception {
String sql1 = "CREATE TABLE test.t1 (col1 INTEGER NOT NULL, col2 INTEGER, col3 smallint NOT NULL, "+
"col4 smallint, col5 bigint NOT NULL, col6 bigint, " +
"col7 INTEGER UNSIGNED, col8 smallint unsigned)";
executeDDL(sql1);
Table table = ais().getTable("test", "t1");
assertNotNull (table);
assertEquals ("INT", table.getColumn(0).getTypeName());
assertFalse (table.getColumn(0).getNullable());
assertEquals ("INT", table.getColumn(1).getTypeName());
assertTrue (table.getColumn(1).getNullable());
assertEquals ("SMALLINT", table.getColumn(2).getTypeName());
assertFalse (table.getColumn(2).getNullable());
assertEquals ("SMALLINT", table.getColumn(3).getTypeName());
assertTrue (table.getColumn(3).getNullable());
assertEquals ("BIGINT", table.getColumn(4).getTypeName());
assertFalse (table.getColumn(4).getNullable());
assertEquals ("BIGINT", table.getColumn(5).getTypeName());
assertTrue (table.getColumn(5).getNullable());
assertEquals ("INT UNSIGNED", table.getColumn(6).getTypeName());
assertTrue (table.getColumn(6).getNullable());
assertEquals ("SMALLINT UNSIGNED", table.getColumn(7).getTypeName());
assertTrue (table.getColumn(7).getNullable());
}
@Test
public void testCreateChar() throws Exception {
String sql1 = "CREATE TABLE test.T1 (col10 CHAR(1) NOT NULL, col11 CHAR(1), " +
"col12 VARCHAR(1) NOT NULL, col13 VARCHAR(1), " +
" col14 LONG VARCHAR NOT NULL, col15 LONG VARCHAR) ";
executeDDL(sql1);
Table table = ais().getTable("test", "t1");
assertNotNull (table);
assertEquals ("CHAR", table.getColumn(0).getTypeName());
assertEquals (Long.valueOf(5), table.getColumn(0).getMaxStorageSize());
assertEquals (Long.valueOf(1), table.getColumn(0).getTypeParameter1());
assertEquals ("CHAR", table.getColumn(1).getTypeName());
assertEquals (Long.valueOf(5), table.getColumn(1).getMaxStorageSize());
assertEquals ("VARCHAR", table.getColumn(2).getTypeName());
assertEquals (Long.valueOf(5), table.getColumn(2).getMaxStorageSize());
assertEquals ("VARCHAR", table.getColumn(3).getTypeName());
assertEquals (Long.valueOf(5), table.getColumn(3).getMaxStorageSize());
assertEquals ("LONGTEXT", table.getColumn(4).getTypeName());
assertFalse (table.getColumn(4).getNullable());
assertEquals (Long.valueOf(1047556), table.getColumn(4).getMaxStorageSize());
assertEquals ("LONGTEXT", table.getColumn(5).getTypeName());
}
@Test
public void testCreateTime() throws Exception {
String sql1 = "CREATE TABLE test.t1 (col30 DATE NOT NULL, col31 DATE, "+
"col32 TIME NOT NULL, col33 time, col34 timestamp NOT NULL, " +
"col35 timestamp)";
executeDDL(sql1);
Table table = ais().getTable("test", "t1");
assertNotNull (table);
assertEquals ("DATE", table.getColumn(0).getTypeName());
assertFalse (table.getColumn(0).getNullable());
assertEquals ("DATE", table.getColumn(1).getTypeName());
assertEquals ("TIME", table.getColumn(2).getTypeName());
assertEquals ("TIME", table.getColumn(3).getTypeName());
assertEquals ("DATETIME", table.getColumn(4).getTypeName());
assertEquals ("DATETIME", table.getColumn(5).getTypeName());
}
@Test
public void testCreateLOB() throws Exception {
String sql1 = "CREATE TABLE test.t1 (col40 CLOB NOT NULL, col41 CLOB, col42 BLOB NOT NULL, col43 BLOB)";
executeDDL(sql1);
Table table = ais().getTable("test", "t1");
assertNotNull (table);
assertEquals ("LONGTEXT", table.getColumn(0).getTypeName());
assertEquals ("LONGTEXT", table.getColumn(1).getTypeName());
assertEquals ("BLOB", table.getColumn(2).getTypeName());
assertEquals ("BLOB", table.getColumn(3).getTypeName());
}
@Test
public void testCreateFloat() throws Exception {
String sql1 = "CREATE TABLE test.t1 (col20 FLOAT NOT NULL, col21 FLOAT, "+
"col22 REAL NOT NULL, col23 REAL, col24 DOUBLE NOT NULL, col25 DOUBLE," +
"col26 DOUBLE UNSIGNED, col27 REAL UNSIGNED, col28 NUMERIC)";
executeDDL(sql1);
Table table = ais().getTable("test", "t1");
assertNotNull (table);
assertEquals ("DOUBLE", table.getColumn(0).getTypeName());
assertEquals ("DOUBLE", table.getColumn(1).getTypeName());
assertEquals ("FLOAT", table.getColumn(2).getTypeName());
assertEquals ("FLOAT", table.getColumn(3).getTypeName());
assertEquals ("DOUBLE", table.getColumn(4).getTypeName());
assertEquals ("DOUBLE", table.getColumn(5).getTypeName());
assertEquals ("DOUBLE UNSIGNED", table.getColumn(6).getTypeName());
assertEquals ("FLOAT UNSIGNED", table.getColumn(7).getTypeName());
assertEquals ("DECIMAL", table.getColumn(8).getTypeName());
}
@Test
public void testCreateDecimal () throws Exception {
String sql1 = "CREATE TABLE test.t1 (col50 DECIMAL NOT NULL, col51 DECIMAL,"+
"col52 DECIMAL (1) NOT NULL, "+
"col54 DECIMAL (10) NOT NULL, " +
"col57 DECIMAL (1,1), col58 DECIMAL (10,1) NOT NULL, " +
"col60 DECIMAL (10,10) NOT NULL, " +
"col63 DECIMAL (30,10))";
executeDDL(sql1);
Table table = ais().getTable("test", "t1");
assertNotNull (table);
assertEquals ("DECIMAL", table.getColumn(0).getTypeName());
assertEquals (Long.valueOf(5), table.getColumn(0).getTypeParameter1());
assertEquals (Long.valueOf(0), table.getColumn(0).getTypeParameter2());
assertEquals ("DECIMAL", table.getColumn(1).getTypeName());
assertTrue (table.getColumn(1).getNullable());
assertEquals ("DECIMAL", table.getColumn(2).getTypeName());
assertEquals (Long.valueOf(1), table.getColumn(2).getTypeParameter1());
assertEquals (Long.valueOf(0), table.getColumn(2).getTypeParameter2());
assertEquals ("DECIMAL", table.getColumn(3).getTypeName());
assertEquals (Long.valueOf(10), table.getColumn(3).getTypeParameter1());
assertEquals (Long.valueOf(0), table.getColumn(3).getTypeParameter2());
assertEquals ("DECIMAL", table.getColumn(4).getTypeName());
assertEquals (Long.valueOf(1), table.getColumn(4).getTypeParameter1());
assertEquals (Long.valueOf(1), table.getColumn(4).getTypeParameter2());
assertEquals ("DECIMAL", table.getColumn(5).getTypeName());
assertEquals (Long.valueOf(10), table.getColumn(5).getTypeParameter1());
assertEquals (Long.valueOf(1), table.getColumn(5).getTypeParameter2());
assertEquals ("DECIMAL", table.getColumn(6).getTypeName());
assertEquals (Long.valueOf(10), table.getColumn(6).getTypeParameter1());
assertEquals (Long.valueOf(10), table.getColumn(6).getTypeParameter2());
assertEquals ("DECIMAL", table.getColumn(7).getTypeName());
assertEquals (Long.valueOf(30), table.getColumn(7).getTypeParameter1());
assertEquals (Long.valueOf(10), table.getColumn(7).getTypeParameter2());
}
@Test
public void createCustomers() throws Exception {
String sql1 = "create table test.customers (" +
"customer_id int not null primary key generated by default as identity," +
"customer_name varchar(255) not null,"+
"primary_payment_code char(1) not null default 'C', " +
"payment_status char(4) not null default 'ABCD', " +
"comment varchar(255))";
String sql2 = "create table test.addresses (" +
"customer_id int not null," +
"state varchar(2) not null," +
"zip_code varchar(5) not null," +
"phone varchar (15)," +
"primary key (customer_id, zip_code)," +
"grouping foreign key (customer_id) references customers)";
executeDDL(sql1);
Table table = ais().getTable("test", "customers");
assertNotNull(table);
assertEquals(5, table.getColumns().size());
executeDDL(sql2);
table = ais().getTable("test", "addresses");
assertNotNull(table);
assertEquals(4, table.getColumns().size());
}
@Test
public void createIdentity () throws Exception {
String sql = "CREATE TABLE test.t7 (c1 integer primary key generated by default as identity)";
executeDDL(sql);
Table table = ais().getTable("test", "t7");
assertNotNull(table);
Column column = table.getColumn(0);
assertNotNull(column.getIdentityGenerator());
assertEquals (1, column.getIdentityGenerator().getStartsWith());
assertEquals (1, column.getIdentityGenerator().getIncrement());
assertNotNull(column.getDefaultIdentity());
assertTrue(column.getDefaultIdentity().booleanValue());
}
@Test
public void createAlwaysIdentity() throws Exception {
String sql = "CREATE TABLE test.t9 (c1 integer primary key generated ALWAYS as identity)";
executeDDL(sql);
Table table = ais().getTable("test", "t9");
assertNotNull(table);
Column column = table.getColumn(0);
assertNotNull(column.getIdentityGenerator());
assertEquals (1, column.getIdentityGenerator().getStartsWith());
assertEquals (1, column.getIdentityGenerator().getIncrement());
assertNotNull(column.getDefaultIdentity());
assertFalse(column.getDefaultIdentity().booleanValue());
}
@Test
public void createIdentityValues() throws Exception {
String sql = "CREATE TABLE test.t10 (c1 integer primary key generated by default as identity (start with 11, increment by -1))";
executeDDL(sql);
Table table = ais().getTable("test", "t10");
assertNotNull(table);
Column column = table.getColumn(0);
assertNotNull(column.getIdentityGenerator());
assertEquals (11, column.getIdentityGenerator().getStartsWith());
assertEquals (-1, column.getIdentityGenerator().getIncrement());
assertNotNull(column.getDefaultIdentity());
assertTrue(column.getDefaultIdentity().booleanValue());
}
@Test
public void dropTableWithIdentity() throws Exception {
executeDDL("CREATE TABLE test.initiateSecManager (id INT PRIMARY KEY)");
int startNo = ais().getSequences().size();
String sql = "CREATE TABLE test.t11 (c1 integer primary key generated by default as identity)";
executeDDL(sql);
sql = "CREATE TABLE test.t12 (c1 integer primary key generated by default as identity)";
executeDDL(sql);
assertEquals (2, ais().getSequences().size() - startNo);
sql = "DROP TABLE test.t11";
executeDDL(sql);
assertEquals (1, ais().getSequences().size() - startNo);
sql = "DROP TABLE test.t12";
executeDDL(sql);
assertEquals (0, ais().getSequences().size() - startNo);
}
@Test
public void createSerialTable() throws Exception {
String sql = "CREATE TABLE test.t12 (c1 SERIAL PRIMARY KEY)";
executeDDL(sql);
Table table = ais().getTable("test", "t12");
assertNotNull (table);
Column column = table.getColumn(0);
assertEquals ("INT", column.getTypeName());
assertEquals (column.getNullable(), false);
assertNotNull (column.getIdentityGenerator());
assertEquals (1, column.getIdentityGenerator().getStartsWith());
assertEquals (1, column.getIdentityGenerator().getIncrement());
assertNotNull(column.getDefaultIdentity());
assertTrue(column.getDefaultIdentity());
// No column index auto created
assertNull (table.getIndex("c1"));
// Primary key is
assertNotNull (table.getPrimaryKey());
Index index = table.getPrimaryKey().getIndex();
assertEquals (1, index.getKeyColumns().size());
}
@Test (expected=DuplicateSequenceNameException.class)
public void createDoubleSerialTable() throws Exception {
String sql = "CREATE TABLE test.t13 (c1 SERIAL PRIMARY KEY, c2 SERIAL)";
executeDDL(sql);
}
@Test
public void createIndexTable() throws Exception {
String sql = "CREATE TABLE test.t14 (c1 INT NOT NULL PRIMARY KEY, c2 INT NOT NULL, INDEX (c2))";
executeDDL(sql);
Table table = ais().getTable("test", "t14");
Index index = table.getIndex("c2");
assertNotNull(index);
assertFalse (index.isUnique());
assertEquals(1, index.getKeyColumns().size());
assertFalse(index.isGroupIndex());
assertFalse(index.isSpatial());
}
@Test
public void createNamedIndexTable() throws Exception {
String sql = "CREATE TABLE test.t15 (c1 int NOT NULL, c2 INT NOT NULL, INDEX idx_c2 (c2))";
executeDDL(sql);
Table table = ais().getTable("test", "t15");
Index index = table.getIndex("idx_c2");
assertNotNull(index);
assertFalse (index.isUnique());
assertEquals(1, index.getKeyColumns().size());
assertFalse(index.isGroupIndex());
assertFalse(index.isSpatial());
}
@Test
public void createSpatialIndexTable() throws Exception {
String sql = "CREATE TABLE test.t16 (c1 decimal(11,7), c2 decimal(11,7), INDEX idx1 (geo_lat_lon(c1, c2)))";
executeDDL (sql);
Table table = ais().getTable("test", "t16");
TableIndex index = table.getIndex("idx1");
assertNotNull(index);
assertFalse (index.isUnique());
assertEquals(2, index.getKeyColumns().size());
assertTrue (index.isSpatial());
}
@Test (expected=UnsupportedFunctionInIndexException.class)
public void createIndexWithUnsupportedFunction() throws Exception {
// z_order_lat_lon is no longer supported, use geo_lat_lon instead
String sql = "CREATE TABLE test.t16 (c1 decimal(11,7), c2 decimal(11,7), INDEX idx1 (z_order_lat_lon(c1, c2)))";
executeDDL(sql);
}
@Test
public void testCreateNationalChar() throws Exception {
String sql1 = "CREATE TABLE test.T1 (c1 NATIONAL CHAR(10), c2 NATIONAL CHARACTER VARYING(10), c3 LONG NVARCHAR) ";
executeDDL(sql1);
Table table = ais().getTable("test", "t1");
assertNotNull (table);
assertEquals ("CHAR", table.getColumn(0).getTypeName());
assertEquals ("VARCHAR", table.getColumn(1).getTypeName());
assertEquals ("LONGTEXT", table.getColumn(2).getTypeName());
}
@Test
public void overlappingFKAndGFK() throws Exception {
executeDDL("CREATE TABLE parent(pid INT NOT NULL PRIMARY KEY)");
executeDDL("CREATE TABLE child (cid INT NOT NULL PRIMARY KEY, pid INT," +
" FOREIGN KEY(pid) REFERENCES parent(pid)," +
" GROUPING FOREIGN KEY(pid) REFERENCES parent(pid))");
Table p = ais().getTable("test", "parent");
Table c = ais().getTable("test", "child");
assertNotNull(p);
assertNotNull(c);
assertEquals(p.getGroup(), c.getGroup());
assertEquals(1, p.getReferencedForeignKeys().size());
assertEquals(1, c.getReferencingForeignKeys().size());
}
@Test
public void selfFK() throws Exception {
executeDDL("CREATE TABLE t(id1 INT NOT NULL PRIMARY KEY, id2 INT, FOREIGN KEY(id2) REFERENCES t(id1))");
Table t = ais().getTable("test", "t");
assertNotNull(t);
assertEquals(1, t.getForeignKeys().size());
ForeignKey fk = t.getForeignKeys().iterator().next();
assertEquals(t, fk.getReferencedTable());
assertEquals(fk.getReferencedTable(), fk.getReferencingTable());
}
@Test(expected= UnsupportedSQLException.class)
public void inlineGroupIndex() throws Exception {
executeDDL("CREATE TABLE parent(pid INT PRIMARY KEY, x INT)");
executeDDL("CREATE TABLE child(cid INT PRIMARY KEY, pid INT, y INT, "+
" GROUPING FOREIGN KEY(pid) REFERENCES parent, "+
" INDEX g_i (parent.x, child.y) USING LEFT JOIN)");
}
}