package net.sourceforge.mayfly.acceptance; public class IndexTest extends SqlTestCase { public void testMysqlSyntax() throws Exception { String sql = "create table foo(a integer, index(a))"; if (dialect.createTableCanContainIndex()) { execute(sql); execute("create table bar(b integer, index named_index(b))"); } else { expectExecuteFailure(sql, "expected data type but got '('"); } } public void testMysqlSyntaxTwoColumns() throws Exception { String sql = "create table foo(a integer, b integer, index(b, a))"; if (dialect.createTableCanContainIndex()) { execute(sql); } else { expectExecuteFailure(sql, "expected data type but got '('"); } } /* TODO: Might want to insist that the index be on a NOT NULL column the way that MySQL 5.1 does (apparently these tests just pass with MySQL 5.0). */ public void testCreateIndexSyntax() throws Exception { execute("create table foo(a integer)"); assertEquals(0, execute("create index an_index_name on foo(a)")); } public void testTwoColumns() throws Exception { execute("create table foo(a integer, b integer, c integer)"); execute("create index an_index_name on foo(c, b)"); } public void testNotUniqueByDefault() throws Exception { execute("create table foo(a integer, b varchar(80))"); execute("create index an_index_name on foo(a)"); execute("insert into foo(a, b) values(4, 'one')"); execute("insert into foo(a, b) values(4, 'two')"); assertResultSet(new String[] { " 4, 'one' ", " 4, 'two' "}, query("select a, b from foo")); } public void testUnique() throws Exception { execute("create table foo(a integer, b varchar(80))"); execute("create unique index an_index_name on foo(a)"); // Also acts as a constraint execute("insert into foo(a, b) values(4, 'one')"); expectExecuteFailure("insert into foo(a, b) values(4, 'two')", "unique constraint in table foo, column a: duplicate value 4"); } public void testUniqueAndExistingRows() throws Exception { execute("create table foo(a integer, b varchar(80))"); execute("insert into foo(a, b) values(4, 'one')"); execute("insert into foo(a, b) values(4, 'two')"); expectExecuteFailure( "create unique index an_index_name on foo(a)", "unique constraint in table foo, column a: duplicate value 4"); execute("create index an_index_name on foo(a)"); } public void testDuplicateName() throws Exception { execute("create table foo(f integer)"); execute("create table bar(b integer)"); execute("create index an_index_name on foo(f)"); String duplicate = "create index an_index_name on bar(b)"; if (dialect.indexNamesArePerTable()) { execute(duplicate); } else { expectExecuteFailure(duplicate, "table foo already has an index an_index_name"); } } public void testIndexNamesAreCaseInsensitive() throws Exception { execute("create table foo(a integer, b integer)"); execute("create index an_index_name on foo(a)"); String duplicate = "create index an_iNdEx_name on foo(b)"; if (false) { execute(duplicate); } else { expectExecuteFailure(duplicate, "table foo already has an index an_iNdEx_name"); } } public void testIndexOnPartOfColumn() throws Exception { execute("create table foo(a varchar(255))"); String sql = "create index my_index on foo(a(10))"; if (dialect.canIndexPartOfColumn()) { execute(sql); } else { expectExecuteFailure(sql, "expected ')' but got '('"); } } public void testDropIndex() throws Exception { execute("create table foo(a integer, b integer)"); execute("create index an_index on foo(a)"); String tryToCreateIndexWithSameName = "create index an_index on foo(b)"; expectExecuteFailure(tryToCreateIndexWithSameName, "table foo already has an index an_index"); String dropWithoutGivingTable = "drop index an_index"; if (dialect.indexNamesArePerTable()) { expectExecuteFailure(dropWithoutGivingTable, "expected ON but got end of file"); execute("drop index an_index on foo"); } else { execute(dropWithoutGivingTable); } execute(tryToCreateIndexWithSameName); } public void testDropIndexBadName() throws Exception { execute("create table foo(a integer)"); expectExecuteFailure( dropIndexCommand("no_such", "foo"), "no index no_such" ); } public void testDropIndexWithWrongTable() throws Exception { execute("create table foo(a integer, b integer)"); execute("create table bar(a integer)"); execute("create index an_index on foo(a)"); String dropIndexOn = "drop index an_index on bar"; if (dialect.canDropIndexGivingWrongTable()) { execute(dropIndexOn); // check it is really gone execute("create index an_index on foo(b)"); } else { /* Could be syntax error, or something like "no index an_index", or the Mayfly expectation of telling exactly what is happening. */ expectExecuteFailure(dropIndexOn, "attempt to drop index an_index from table bar " + "although the index is on table foo"); } } public void testDropIndexWithCorrectTable() throws Exception { execute("create table foo(a integer, b integer)"); execute("create table bar(a integer)"); execute("create index an_index on foo(a)"); String dropIndexOn = "drop index an_index on foo"; if (dialect.haveDropIndexOn()) { execute(dropIndexOn); // Check that it is really gone execute("create index an_index on foo(b)"); } else { expectExecuteFailure(dropIndexOn, "expected end of file but got ON"); } } public void testDroppingUniqueIndexDropsConstraint() throws Exception { execute("create table foo(a integer, b varchar(20))"); execute("create unique index an_index on foo(a)"); dropIndex("an_index", "foo"); dialect.checkDump( "CREATE TABLE foo(\n" + " a INTEGER,\n" + " b VARCHAR(20)\n" + ");\n\n"); execute("insert into foo(a, b) values(6, 'first')"); execute("insert into foo(a, b) values(6, 'second')"); } public void testDroppingIndexDoesNotAffectConstraint() throws Exception { String createTable = "create table foo(a integer, b varchar(20), unique(a))"; if (dialect.uniqueColumnMayBeNullable()) { execute(createTable); } else { expectExecuteFailure(createTable, "cannot combine nullable column and unique constraint: table foo, column a"); } if (dialect instanceof DerbyDialect) { // I couldn't get the rest of this test to work in Derby. // Dropping the index is failing, saying no such index, and // I'm not sure sure why. return; } execute("create index an_index on foo(a)"); dropIndex("an_index", "foo"); dialect.checkDump( "CREATE TABLE foo(\n" + " a INTEGER,\n" + " b VARCHAR(20),\n" + " UNIQUE(a)\n" + ");\n\n"); execute("insert into foo(a, b) values(6, 'first')"); expectExecuteFailure("insert into foo(a, b) values(6, 'second')", "unique constraint in table foo, column a: duplicate value 6"); } public void mysql_only_testAlterTableDropIndex() throws Exception { // another syntax, as an alternative to the DROP INDEX one execute("create table foo(a integer, b integer)"); execute("create index an_index on foo(a)"); String tryToCreateIndexWithSameName = "create index an_index on foo(b)"; expectExecuteFailure(tryToCreateIndexWithSameName, "duplicate index an_index"); execute("alter table foo drop index an_index"); execute(tryToCreateIndexWithSameName); } /* Another case for duplicates is: execute("create index an_index on foo(a)"); execute("create index an_index on foo(a)"); Derby seems to like that one, but hypersonic and postgres don't. */ }