package net.sourceforge.mayfly.acceptance;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class AutoIncrementTest extends SqlTestCase {
public void testAutoUnderbarIncrement() throws Exception {
// I think MySQL also accepts "primary key auto_increment"
// but mayfly doesn't (at least yet). The grammar in the MySQL
// manual says that auto_increment has to be before "primary key"
// Trying to extrapolate from the SQL92 grammar kind of might
// imply pickiness about the order.
String sql = "create table foo (" +
"x integer auto_increment primary key, " +
"y varchar(255))";
if (dialect.haveAutoUnderbarIncrement()) {
execute(sql);
check(true);
}
else {
expectExecuteFailure(sql, "expected ')' but got auto_increment");
}
}
public void testSerial() throws Exception {
String sql = "create table foo (x serial, y varchar(255))";
if (dialect.haveSequencySerial() || dialect.haveAutoIncrementSerial()) {
assertFalse(dialect.haveSequencySerial() && dialect.haveAutoIncrementSerial());
execute(sql);
check(dialect.haveAutoIncrementSerial());
}
else {
expectExecuteFailure(sql, "expected data type but got serial");
}
}
public void testIdentity() throws Exception {
String sql = "create table foo (x identity, y varchar(255))";
if (dialect.haveIdentity()) {
execute(sql);
// Hypersonic syntax should imply hypersonic semantics, I guess
check(true);
}
else {
expectExecuteFailure(sql, "expected data type but got identity");
}
}
public void testSql2003() throws Exception {
// could also test INCREMENT BY
// not convinced we want CYCLE and MAXVALUE
String sql = "create table foo (x INTEGER GENERATED BY DEFAULT " +
"AS IDENTITY(START WITH 1) PRIMARY KEY, y varchar(255))";
if (dialect.haveSql2003AutoIncrement()) {
execute(sql);
check(sql2003RelativeToLastValue());
}
else {
expectExecuteFailure(sql, "expected ')' but got GENERATED");
}
}
public void testGeneratedByDefaultNoStart() throws Exception {
String sql = "create table foo (x INTEGER GENERATED BY DEFAULT " +
"AS IDENTITY PRIMARY KEY, y varchar(255))";
if (dialect.haveSql2003AutoIncrement()) {
execute(sql);
check(sql2003RelativeToLastValue());
}
else {
expectExecuteFailure(sql, "expected ')' but got GENERATED");
}
}
private boolean sql2003RelativeToLastValue() {
return dialect.sql2003RelativeToLastValue();
}
// Derby also has GENERATED ALWAYS
private void check(boolean relativeToLastValue) throws SQLException {
execute("insert into foo(x, y) values (92, 'a')");
execute("insert into foo(y) values ('b')");
execute("insert into foo(y) values ('c')");
assertResultSet(
relativeToLastValue
?
new String[] {
" 92, 'a' ",
" 93, 'b' ",
" 94, 'c' "
}
:
new String[] {
" 92, 'a' ",
" 1, 'b' ",
" 2, 'c' "
}
,
query("select x, y from foo")
);
}
public void testInsertHighLowThenDefault() throws Exception {
String autoIncrementType = dialect.autoIncrementType();
if (autoIncrementType == null) {
return;
}
execute("create table foo(x " + autoIncrementType +
", y varchar(255))");
execute("insert into foo(x, y) values (92, 'high')");
execute("insert into foo(x, y) values (70, 'low')");
execute("insert into foo(y) values ('default')");
assertResultSet(
new String[] {
" 92, 'high' ",
" 70, 'low' ",
" 93, 'default' "
}
,
query("select x, y from foo")
);
}
public void testInsertHighHigherThenDefault() throws Exception {
String autoIncrementType = dialect.autoIncrementType();
if (autoIncrementType == null) {
return;
}
execute("create table foo(x " + autoIncrementType +
", y varchar(255))");
execute("insert into foo(x, y) values (92, 'high')");
execute("insert into foo(x, y) values (93, 'higher')");
execute("insert into foo(y) values ('default')");
assertResultSet(
new String[] {
" 92, 'high' ",
" 93, 'higher' ",
" 94, 'default' "
}
,
query("select x, y from foo")
);
}
/**
* @internal
* Much like {@link #check(boolean)} but also tests that there
* is no special case for a sequence value which would violate
* a constraint, and the case where a failing command still
* increments the sequence.
*/
public void testConflicting() throws Exception {
String type;
if (dialect.haveSequencySerial()) {
type = "serial";
}
else if (dialect.haveSql2003AutoIncrement()
&& !sql2003RelativeToLastValue()) {
type = "integer generated by default as identity(start with 1)";
}
else {
return;
}
execute("create table foo(x " +
type +
" primary key, y integer)");
execute("insert into foo(x, y) values(1, 5)");
expectExecuteFailure("insert into foo(y) values(6)",
"primary key in table foo, column x: duplicate value 1");
String tryAgain = "insert into foo(y) values(77)";
if (dialect.wishThisWereTrue()) {
execute(tryAgain);
assertResultSet(
new String[] {
" 1, 5 ",
" 2, 77 "
},
query("select x, y from foo"));
}
else {
expectExecuteFailure(tryAgain,
"primary key in table foo, column x: duplicate value 1");
}
}
public void testInsertTwice() throws Exception {
execute("create table foo(x " +
dialect.identityType() +
", y integer)");
execute("insert into foo(x, y) values(33, 5)");
execute("insert into foo(x, y) values(22, 6)");
assertResultSet(new String[] {
" 33, 5 ",
" 22, 6 "
},
query("select x, y from foo"));
}
public void testGetLastIdentityValue() throws Exception {
execute("create table foo(x " +
dialect.identityType() +
", y integer)");
execute("insert into foo(y) values(5)");
assertResultSet(new String[] { "1" },
query(dialect.lastIdentityValueQuery("foo", "x")));
execute("insert into foo(y) values(6)");
assertResultSet(
new String[] { "2" },
query(dialect.lastIdentityValueQuery("foo", "x")));
assertResultSet(
new String[] { "1, 5", "2, 6"},
query("select x, y from foo"));
}
public void testLastIdentityIsPerConnection() throws Exception {
execute("create table foo(x " +
dialect.identityType() +
", y integer)");
execute("insert into foo(y) values(11)");
Connection connection2 = dialect.openAdditionalConnection();
try {
execute("insert into foo(y) values(22)", connection2);
String askForLast = dialect.lastIdentityValueQuery("foo", "x");
assertResultSet(new String[] { "1" }, query(askForLast));
Statement statement = connection2.createStatement();
ResultSet results2 = statement.executeQuery(askForLast);
assertResultSet(new String[] { "2" }, results2);
}
finally {
connection2.close();
}
}
public void testInsertWithoutColumns() throws Exception {
execute("create table foo(x " + dialect.identityType() + ")");
String insertNoValues = "insert into foo() values()";
if (dialect.canInsertNoValues()) {
execute(insertNoValues);
if (dialect.numberOfValuesMustMatchNumberOfColumns()) {
/* MySQL doesn't complain about this */
// expectExecuteFailure("insert into foo() values(5)", "");
expectExecuteFailure("insert into foo(x) values()",
"Too few values.\n" +
"Columns and values were:\n" +
"x (none)\n");
/* This one is allowed by MySQL, but Mayfly treats
foo as being the same as foo(x), and thus
an error */
// expectExecuteFailure("insert into foo values()", "");
}
assertResultList(
new String[] { "1" },
query("select x from foo"));
}
else {
expectExecuteFailure(insertNoValues, "expected value but got ')'");
}
}
public void testDeleteRowAndSeeIfValueIsReused() throws Exception {
/*
* For MySQL 5.1, there is a case (where the auto_increment column
* is part of a multiple-column index) where the auto_increment
* value can be reused. But we don't try to test for that
* case, just the simple case.
*/
String autoIncrementType = dialect.autoIncrementType();
if (autoIncrementType == null) {
return;
}
execute("create table foo(" +
"a " + autoIncrementType + ", " +
"b varchar(80))");
execute("insert into foo(b) values('first')");
execute("insert into foo(b) values('delete me')");
execute("delete from foo where b = 'delete me'");
execute("insert into foo(b) values('after delete')");
assertResultSet(
new String[] { " 1, 'first' ", " 3, 'after delete' " },
query("select a, b from foo")
);
}
// Not valid in MySQL because this isn't a key:
// execute("create table foo (x integer not null auto_increment, y varchar(255))");
// On the other hand postgres does not require a primary key or unique constraint for serial
// serial in postgres does imply not null
// MySQL doesn't allow more than one auto-increment column
// Inserting null implies an auto-generated value (think this is true of hypersonic,mysql. others?
}