public void testSql2003AutoIncrement() throws Exception {
database.execute(
"create table incr("
+ "a integer generated by default as identity not null,"
+ "b varchar(255))");
database.execute("insert into incr(a, b) values(7, 'seven')");
database.execute("insert into incr(b) values('before dump')");
String dump = dump();
assertEquals(
"CREATE TABLE incr(\n"
+ " a INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 2) NOT NULL,\n"
+ " b VARCHAR(255)\n"
+ ");\n\n"
+ "INSERT INTO incr(a, b) VALUES(1, 'before dump');\n"
+ "INSERT INTO incr(a, b) VALUES(7, 'seven');\n\n",
dump);
Database database2 = load(dump);
database.execute("insert into incr(b) values('after dump')");
database2.execute("insert into incr(b) values('after dump')");
String dump1 = dump();
String dump2 = new SqlDumper().dump(database2.dataStore());
assertEquals(dump1, dump2);
SqlTestCase.assertResultSet(
new String[] {"1, 'before dump' ", "2, 'after dump' ", "7, 'seven' "},
database.query("select a, b from incr"));
}
public void testAutoIncrement() throws Exception {
database.execute("create table incr(a integer auto_increment not null," + "b varchar(255))");
database.execute("insert into incr(b) values('before dump')");
database.execute("insert into incr(a, b) values(7, 'seven')");
String dump0 = dump();
assertEquals(
"CREATE TABLE incr(\n"
+ " a INTEGER DEFAULT 8 AUTO_INCREMENT NOT NULL,\n"
+ " b VARCHAR(255)\n"
+ ");\n\n"
+ "INSERT INTO incr(a, b) VALUES(1, 'before dump');\n"
+ "INSERT INTO incr(a, b) VALUES(7, 'seven');\n\n",
dump0);
Database database2 = load(dump0);
database.execute("insert into incr(b) values('after dump')");
database2.execute("insert into incr(b) values('after dump')");
String dump1 = dump();
String dump2 = new SqlDumper().dump(database2.dataStore());
assertEquals(dump1, dump2);
SqlTestCase.assertResultSet(
new String[] {"1, 'before dump' ", "8, 'after dump' ", "7, 'seven' "},
database.query("select a, b from incr"));
}