public void testDumpDefinition() throws Exception {
database.execute("create table foo(a int)");
database.execute("insert into foo(a) values(5)");
StringWriter out = new StringWriter();
new SqlDumper().definition(database.dataStore(), out);
assertEquals("CREATE TABLE foo(\n a INTEGER\n);\n\n", out.toString());
}
public void testSelfReference() throws Exception {
database.execute(
"create table foo(id integer,"
+ "parent integer,"
+ "foreign key(parent) references foo(id))");
checkRoundTrip(database.dataStore());
}
public void testRowSatisfiesItsOwnConstraint() throws Exception {
database.execute(
"create table self(id integer primary key,"
+ "parent integer,"
+ "foreign key(parent) references self(id))");
database.execute("insert into self(id, parent) values(1, 1)");
checkRoundTrip(database.dataStore());
}
public void testSeveralColumns() throws Exception {
database.execute("create table foo(a integer, b integer)");
database.execute("insert into foo(a, b) values(5, 8)");
assertEquals(
"CREATE TABLE foo(\n a INTEGER,\n b INTEGER\n);\n\n"
+ "INSERT INTO foo(a, b) VALUES(5, 8);\n\n",
dump());
}
public void testNullAndDefault() throws Exception {
database.execute("create table foo(a integer default 5, b integer)");
database.execute("insert into foo() values()");
assertEquals(
"CREATE TABLE foo(\n a INTEGER DEFAULT 5,\n b INTEGER\n);\n\n"
+ "INSERT INTO foo(a, b) VALUES(5, null);\n\n",
dump());
}
public void testTwoForeignKeysFromBarToFoo() throws Exception {
database.execute("create table foo(a integer unique, b integer unique)");
database.execute(
"create table bar(a_id integer,"
+ "foreign key(a_id) references foo(a),"
+ "b_id integer,"
+ "foreign key(b_id) references foo(b))");
checkRoundTrip(database.dataStore());
}
public void testPrimaryKey() throws Exception {
database.execute("create table foo(a integer primary key)");
database.execute("create table bar(" + "a integer, b integer, primary key(a, b))");
assertEquals(
"CREATE TABLE bar(\n"
+ " a INTEGER,\n b INTEGER,\n PRIMARY KEY(a, b)\n);\n\n"
+ "CREATE TABLE foo(\n a INTEGER,\n PRIMARY KEY(a)\n);\n\n",
dump());
}
public void testBinaryData() throws Exception {
database.execute("create table foo(a blob)");
PreparedStatement statement =
database.openConnection().prepareStatement("insert into foo(a) values(?)");
statement.setBytes(1, new byte[] {0, 1, 2, 127, 77, (byte) 200, (byte) 255, 0});
statement.executeUpdate();
assertEquals("INSERT INTO foo(a) VALUES(x'0001027f4dc8ff00');\n\n", dumpData());
}
public void testMoveToEndOnAddForeignKeysDoesNotSuffice() throws Exception {
/* bb refers to aa, cc refers to bb */
/* The simple algorithm of just moving bb to after aa, or to
* the end, would not suffice.
*/
database.execute("create table order1_bb(" + "a_id integer, b integer primary key)");
database.execute(
"create table order2_cc(b_id integer," + "foreign key(b_id) references order1_bb(b))");
database.execute("create table order3_aa(a integer primary key)");
database.execute("alter table order1_bb add foreign key(a_id) references order3_aa(a)");
checkRoundTrip(database.dataStore());
}
public void testDataOfVariousTypes() throws Exception {
database.execute(
"create table foo(a bigint, b decimal(23,1)," + "c varchar(255), d date, e timestamp)");
database.execute(
"insert into foo values("
+ "888111222333, 999888111222333.5, 'c''est', '2004-11-04',"
+ " '2000-02-29 13:45:01' )");
assertEquals(
"INSERT INTO foo(a, b, c, d, e) VALUES("
+ "888111222333, 999888111222333.5, 'c''est', '2004-11-04', "
+ "'2000-02-29 13:45:01');\n\n",
dumpData());
}
/**
* From a datastore, dump it, then load from that dump, dump again, and compare the two dumps.
*
* <p>This is a somewhat weak test in that if the dump does something wrong, it quite possibly
* will do the same thing wrong in both dumps. But it does catch things like dump files which
* won't load because tables/rows are not in the order which will work with foreign keys.
*/
private static void checkRoundTrip(DataStore inputStore) {
String dump = new SqlDumper().dump(inputStore);
Database database2 = new Database();
try {
database2.executeScript(new StringReader(dump));
} catch (MayflyException e) {
throw new RuntimeException(
"failure in command: " + e.failingCommand() + "\ndump was:\n" + dump, e);
}
String dump2 = new SqlDumper().dump(database2.dataStore());
assertEquals(dump, dump2);
}
public void testCircularForeignKeys() throws Exception {
database.execute("create table bb(a_id integer, b integer primary key)");
database.execute("create table aa(b_id integer, a integer primary key)");
database.execute("alter table bb add foreign key(a_id) references aa(a)");
database.execute("alter table aa add foreign key(b_id) references bb(b)");
try {
new SqlDumper().dump(database.dataStore());
fail();
} catch (MayflyException e) {
assertEquals(
// "cannot dump: circular reference between tables bb and aa",
"cannot dump: circular foreign key references between tables", e.getMessage());
}
}
public void testForeignKeyOnDelete() throws Exception {
database.execute("create table refd(a integer primary key)");
database.execute(
"create table refr(d integer,"
+ "foreign key(d) references refd(a) "
+ "on delete set null on update no action)");
assertEquals(
"CREATE TABLE refd(\n a INTEGER,\n PRIMARY KEY(a)\n);\n\n"
+ "CREATE TABLE refr(\n d INTEGER,\n"
+ " CONSTRAINT refr_ibfk_1 FOREIGN KEY(d) REFERENCES refd(a) "
+ "ON DELETE SET NULL\n"
+ ");\n\n",
dump());
}
/** See {@link #testAutoIncrementNoNextValue()} for the motivation. */
public void testSql2003IdentityNoNextValue() throws Exception {
database.execute(
"create table incr5(" + "a integer generated by default as identity(start with 7))");
database.execute("insert into incr5() values()");
database.execute("delete from incr5");
assertEquals(
"CREATE TABLE incr5(\n a INTEGER "
+ "GENERATED BY DEFAULT AS IDENTITY(START WITH 8)\n);\n\n",
new SqlDumper().dump(database.dataStore()));
assertEquals(
"CREATE TABLE incr5(\n a INTEGER " + "GENERATED BY DEFAULT AS IDENTITY\n);\n\n",
new SqlDumper(false).dump(database.dataStore()));
}
public void testForeignKeyOnUpdate() throws Exception {
database.execute("create table refd(a integer primary key)");
try {
database.execute(
"create table refr(d integer,"
+ "foreign key(d) references refd(a) "
+ "on update set default)");
// Here's where we'd dump it and assert it came out right.
fail();
} catch (MayflyException e) {
assertEquals("ON UPDATE SET DEFAULT not implemented", e.getMessage());
}
}
public void testTwoIndexes() throws Exception {
database.execute("create table foo(a integer, b integer)");
database.execute("create index a_index on foo(a)");
database.execute("create index b_index on foo(b)");
database.execute("insert into foo(a, b) values(5, 7)");
assertEquals(
"CREATE TABLE foo(\n"
+ " a INTEGER,\n"
+ " b INTEGER\n"
+ ");\n"
+ "CREATE INDEX a_index ON foo(a);\n"
+ "CREATE INDEX b_index ON foo(b);\n\n"
+ "INSERT INTO foo(a, b) VALUES(5, 7);\n\n",
dump());
checkRoundTrip(database.dataStore());
}
public void testConstraintNames() throws Exception {
database.execute("create table name(a integer primary key)");
database.execute(
"create table name2("
+ "a integer, b integer, c integer,"
+ "constraint a_key foreign key(a) references name(a),"
+ "constraint b_key primary key(b),"
+ "constraint c_uniq unique(c))");
assertEquals(
"CREATE TABLE name(\n a INTEGER,\n PRIMARY KEY(a)\n);\n\n"
+ "CREATE TABLE name2(\n a INTEGER,\n b INTEGER,\n c INTEGER,\n"
+ " CONSTRAINT a_key FOREIGN KEY(a) REFERENCES name(a),\n"
+ " CONSTRAINT b_key PRIMARY KEY(b),\n"
+ " CONSTRAINT c_uniq UNIQUE(c)\n);\n\n",
dump());
}
public void testIntegerDataTypes() throws Exception {
database.execute(
"create table Foo("
+ "a integer,"
+ "b int ,"
+ "c tinyint,"
+ "d smallint,"
+ "e bigint,"
+ "f identity,"
+ "g serial"
+ ")");
assertEquals(
"CREATE TABLE Foo(\n"
+ " a INTEGER,\n"
+
// The prevailing concept here mostly seems to be to canonicalize.
" b INTEGER,\n"
+ " c TINYINT,\n"
+ " d SMALLINT,\n"
+ " e BIGINT,\n"
+ " f INTEGER AUTO_INCREMENT,\n"
+ " g INTEGER GENERATED BY DEFAULT AS IDENTITY\n"
+ ");\n\n",
dump());
}
public void testAutoIncrementDoesNotDumpDefaultOnNewTable() throws Exception {
database.execute("create table incr(a integer auto_increment not null," + "b varchar(255))");
assertEquals(
"CREATE TABLE incr(\n"
+ " a INTEGER AUTO_INCREMENT NOT NULL,\n"
+ " b VARCHAR(255)\n"
+ ");\n\n",
dump());
database.execute("insert into incr(b) values('short-lived')");
database.execute("delete from incr");
assertEquals(
"CREATE TABLE incr(\n"
+ " a INTEGER DEFAULT 2 AUTO_INCREMENT NOT NULL,\n"
+ " b VARCHAR(255)\n"
+ ");\n\n",
dump());
}
public void testUnique() throws Exception {
database.execute(
"create table foo(a integer, b integer, c integer," + "unique(a), unique(b, c))");
assertEquals(
"CREATE TABLE foo(\n a INTEGER,\n b INTEGER,\n c INTEGER,\n"
+ " UNIQUE(a),\n UNIQUE(b, c)\n);\n\n",
dump());
}
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"));
}
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 testCircularRowsWithForeignKeys() throws Exception {
database.execute(
"create table aa(a integer primary key, parent integer,"
+ "foreign key(parent) references aa(a))");
database.execute("insert into aa(a, parent) values(1, null)");
database.execute("insert into aa(a, parent) values(2, 1)");
database.execute("update aa set parent = 2 where a = 1");
try {
new SqlDumper().dump(database.dataStore());
fail();
} catch (MayflyException e) {
assertEquals(
// Would be nice to say something about which rows...
// "cannot dump: circular reference between " +
// "rows with a 1 and 2 in table aa",
"cannot dump: circular reference between rows in table aa", e.getMessage());
}
}
public void testUniqueIndex() throws Exception {
database.execute("create table foo(a integer, b integer, c integer)");
database.execute("create unique index x on foo(b, c)");
/* Need to distinguish a unique index from an index and a constraint
so that DROP INDEX can drop the constraint for the former but not
for the latter. */
assertEquals(
"CREATE TABLE foo(\n"
+ " a INTEGER,\n"
+ " b INTEGER,\n"
+ " c INTEGER\n"
+ ");\n"
+ "CREATE UNIQUE INDEX x ON foo(b, c);\n\n",
dump());
checkRoundTrip(database.dataStore());
}
public void testCheckConstraint() throws Exception {
database.execute("create table chk(a integer, check(a <> 55))");
try {
assertEquals("CREATE TABLE chk(\n a INTEGER,\n CHECK(a <> 55)\n);\n\n", dump());
fail("Hmm, looks like dumping check constaints got implemented");
} catch (UnimplementedException expected) {
}
}
public void testRowsForSeveralTables() throws Exception {
database.execute("create table foo(a integer)");
database.execute("create table empty(a integer)");
database.execute("create table bar(a integer)");
database.execute("insert into foo(a) values(5)");
database.execute("insert into bar(a) values(51)");
database.execute("insert into bar(a) values(52)");
assertEquals(
"CREATE TABLE bar(\n a INTEGER\n);\n\n"
+ "CREATE TABLE empty(\n a INTEGER\n);\n\n"
+ "CREATE TABLE foo(\n a INTEGER\n);\n\n"
+ "INSERT INTO bar(a) VALUES(51);\n"
+ "INSERT INTO bar(a) VALUES(52);\n"
+ "\n"
+ "INSERT INTO foo(a) VALUES(5);\n"
+ "\n",
dump());
}
public void testDifferentConstraintsImplyDifferentOrders() throws Exception {
// A particularly odd type of cycle.
database.execute(
"create table foo(a integer unique,"
+ "b integer unique,"
+ "a_ref integer,"
+ "foreign key(a_ref) references foo(a),"
+ "b_ref integer,"
+ "foreign key(b_ref) references foo(b)"
+ ")");
database.execute("insert into foo(a, b, a_ref, b_ref) values (1, 2, null, null)");
database.execute("insert into foo(a, b, a_ref, b_ref) values (8, 9, 1, null)");
database.execute("update foo set b_ref = 9 where a = 1");
try {
dump();
fail();
} catch (MayflyException e) {
assertEquals("cannot dump: circular reference between rows in table foo", e.getMessage());
}
}
public void testAutoIncrementDoesDumpDefaultOnAlteredTable() throws Exception {
database.execute("create table incr(a integer not null," + "b varchar(255))");
database.execute("insert into incr(a, b) values(1, 'a row')");
database.execute("alter table incr modify column " + "a integer auto_increment not null");
assertEquals(
"CREATE TABLE incr(\n"
+ " a INTEGER DEFAULT 2 AUTO_INCREMENT NOT NULL,\n"
+ " b VARCHAR(255)\n"
+ ");\n\n"
+ "INSERT INTO incr(a, b) VALUES(1, 'a row');\n\n",
dump());
database.execute("delete from incr");
assertEquals(
"CREATE TABLE incr(\n"
+ " a INTEGER DEFAULT 2 AUTO_INCREMENT NOT NULL,\n"
+ " b VARCHAR(255)\n"
+ ");\n\n",
dump());
}
public void testAlterTableUsingAfterDump() throws Exception {
database.execute("create table foo(a integer, b integer, c integer)");
database.execute("insert into foo values(1, 2, 3)");
String before = new SqlDumper().dump(database.dataStore());
database.execute("alter table foo drop column b");
database.execute("alter table foo add column b integer after a");
database.execute("update foo set b = 2");
String after = new SqlDumper().dump(database.dataStore());
assertEquals(before, after);
}
public void testRowOrderWithForeignKeys() throws Exception {
database.execute(
"create table aa(a integer primary key, parent integer,"
+ "foreign key(parent) references aa(a))");
database.execute("insert into aa(a, parent) values(31, null)");
database.execute("insert into aa(a, parent) values(1000, null)");
database.execute("insert into aa(a, parent) values(11, 1000)");
database.execute("insert into aa(a, parent) values(12, 1000)");
database.execute("insert into aa(a, parent) values(24, 11)");
database.execute("update aa set parent = 24 where a = 31");
checkRoundTrip(database.dataStore());
}