package net.sourceforge.mayfly.dump;
import junit.framework.TestCase;
import net.sourceforge.mayfly.Database;
import net.sourceforge.mayfly.MayflyException;
import net.sourceforge.mayfly.MayflyInternalException;
import net.sourceforge.mayfly.UnimplementedException;
import net.sourceforge.mayfly.acceptance.SqlTestCase;
import net.sourceforge.mayfly.datastore.DataStore;
import java.io.IOException;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.PreparedStatement;
public class SqlDumperTest extends TestCase {
private Database database;
@Override
protected void setUp() throws Exception {
database = new Database();
}
public void testEmpty() throws Exception {
assertEquals("", new SqlDumper().dump(new Database().dataStore()));
}
public void testWriter() throws Exception {
StringWriter out = new StringWriter();
new SqlDumper().dump(new DataStore(), out);
assertEquals("", out.toString());
}
public void testTable() throws Exception {
database.execute("create table foo(a integer)");
assertEquals("CREATE TABLE foo(\n a INTEGER\n);\n\n",
dump());
}
public void testTwoColumns() throws Exception {
database.execute("create table foo(a integer, B Integer)");
assertEquals("CREATE TABLE foo(\n" +
" a INTEGER,\n" +
" B INTEGER\n" +
");\n\n",
dump());
}
public void testTwoTables() throws Exception {
database.execute("create table foo(a integer)");
database.execute("create table bar(b integer)");
assertEquals(
"CREATE TABLE bar(\n" +
" b INTEGER\n" +
");\n" +
"\n" +
"CREATE TABLE foo(\n" +
" a INTEGER\n" +
");\n" +
"\n",
dump());
}
public void testDataTypes() throws Exception {
database.execute("create table Foo(" +
"b varchar ( 0243 ) ," +
"c timestamp," +
"d date," +
"e text," +
"f decimal ( 7 , 5 ), " +
"g blob ( 32800)," +
"h blob" +
")");
assertEquals(
"CREATE TABLE Foo(\n" +
" b VARCHAR(243),\n" +
" c TIMESTAMP,\n" +
" d DATE,\n" +
" e TEXT,\n" +
" f DECIMAL(7,5),\n" +
" g BLOB(32800),\n" +
" h BLOB\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 testRow() throws Exception {
database.execute("create table foo(a integer)");
database.execute("insert into foo(a) values(5)");
assertEquals("CREATE TABLE foo(\n a INTEGER\n);\n\n" +
"INSERT INTO foo(a) VALUES(5);\n\n",
dump());
}
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 testSeveralRows() throws Exception {
database.execute("create table foo(a integer)");
database.execute("insert into foo(a) values(5)");
database.execute("insert into foo(a) values(6)");
assertEquals("CREATE TABLE foo(\n a INTEGER\n);\n\n" +
"INSERT INTO foo(a) VALUES(5);\n" +
"INSERT INTO foo(a) VALUES(6);\n\n",
dump());
}
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 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());
}
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 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 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 testNotNull() throws Exception {
database.execute("create table foo(a integer not null)");
assertEquals(
"CREATE TABLE foo(\n a INTEGER NOT NULL\n);\n\n",
dump());
}
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 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 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 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 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()
);
}
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 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 testIndex() throws Exception {
database.execute("create table foo(a integer)");
database.execute("create index i on foo(a)");
assertEquals(
"CREATE TABLE foo(\n a INTEGER\n);\n" +
"CREATE INDEX i ON foo(a);\n\n",
dump()
);
checkRoundTrip(database.dataStore());
}
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 testMysqlSyntaxIndex() throws Exception {
database.execute("create table foo(a integer, index(a))");
assertEquals(
"CREATE TABLE foo(\n a INTEGER\n);\n" +
"CREATE INDEX an_index ON foo(a);\n\n",
dump()
);
}
public void testOnUpdateValue() throws Exception {
database.execute("create table onup(a integer on update 5)");
assertEquals("CREATE TABLE onup(\n a INTEGER ON UPDATE 5\n);\n\n",
dump());
}
public void testAutoIncrementNoData() throws Exception {
database.execute("create table incr2(a integer auto_increment)");
assertEquals(
"CREATE TABLE incr2(\n a INTEGER AUTO_INCREMENT\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 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 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 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 testAutoIncrementNoNextValue() throws Exception {
database.execute("create table incr4(a integer auto_increment)");
String before = new SqlDumper(false).dump(database.dataStore());
database.execute("insert into incr4(a) values(5)");
database.execute("delete from incr4");
assertEquals(
"CREATE TABLE incr4(\n a INTEGER DEFAULT 6 AUTO_INCREMENT\n);\n\n",
new SqlDumper().dump(database.dataStore()));
String after = new SqlDumper(false).dump(database.dataStore());
assertEquals(
"CREATE TABLE incr4(\n a INTEGER AUTO_INCREMENT\n);\n\n",
after);
assertEquals(before, after);
}
/**
* 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 testCurrentTimestamp() throws Exception {
database.execute("create table nowish(" +
"a timestamp default current_timestamp " +
"on update current_timestamp " +
")");
assertEquals(
"CREATE TABLE nowish(\n" +
" a TIMESTAMP DEFAULT CURRENT_TIMESTAMP " +
"ON UPDATE CURRENT_TIMESTAMP\n" +
");\n\n",
dump());
}
public void testQuotedIdentifiers() throws Exception {
database.execute("create table \"join\" (" +
"\"null\" integer, \"=\" integer, \"\u00a1\" integer," +
"\"nonquote\" integer)");
assertEquals("CREATE TABLE \"join\"(\n" +
" \"null\" INTEGER,\n" +
" \"=\" INTEGER,\n" +
" \"\u00a1\" INTEGER,\n" +
" nonquote INTEGER\n" +
");\n\n",
dump());
}
public void testIdentifier() throws Exception {
assertEquals("\"integer\"", identifier("integer"));
assertEquals("foo", identifier("foo"));
assertEquals("\"<\"", identifier("<"));
assertEquals("\"0foo\"", identifier("0foo"));
assertEquals("foo7", identifier("foo7"));
assertEquals("foo_bar", identifier("foo_bar"));
assertEquals("\"foo&bar\"", identifier("foo&bar"));
try {
identifier("");
fail();
}
catch (MayflyInternalException e) {
assertEquals("shouldn't have empty string as identifier",
e.getMessage());
}
try {
identifier("\"");
fail();
}
catch (MayflyException e) {
assertEquals("don't know how to dump identifier " +
"containing a double quote", e.getMessage());
}
}
private String identifier(String in) throws IOException {
StringWriter out = new StringWriter();
SqlDumper.identifier(in, out);
return out.toString();
}
public void testRoundTrip() throws Exception {
database.execute("create table foo(a integer default 5," +
"b varchar(255) not null," +
"c bigint default 88 not null," +
"d decimal(7,1)," +
"e timestamp default current_timestamp," +
"primary key(b, c)," +
"unique(d))");
database.execute("insert into foo(b) values('hi')");
database.execute("create table bar(a bigint, b decimal(23,1)," +
"c varchar(255), d date, e timestamp)");
database.execute("insert into bar values(" +
"888111222333, 999888111222333.5, 'c''est', '2004-11-04'," +
" '2000-02-29 13:45:01' )");
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))");
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)");
database.execute("create table onup(" +
"a integer default 7 on update 5 not null)");
database.execute("create table binary_table(a blob)");
database.execute(
"insert into binary_table(a) values(x'0001027f4dc8ff00')");
database.execute("create table \"join\" (" +
"\"null\" integer, \"=\" integer, \"\u00a1\" integer," +
"\"nonquote\" integer)");
database.execute("create table incr(a integer auto_increment not null," +
"b varchar(255))");
database.execute("insert into incr(a, b) values(7, 'seven')");
database.execute("insert into incr(b) values('before dump')");
database.execute("create table incr_seq(" +
"a integer generated by default as identity not null," +
"b varchar(255))");
database.execute("insert into incr_seq(a, b) values(7, 'seven')");
database.execute("insert into incr_seq(b) values('before dump')");
database.execute("create table nowish(" +
"a timestamp default current_timestamp " +
"on update current_timestamp " +
")");
database.execute("create table incr4(a integer auto_increment)");
database.execute("insert into incr4(a) values(5)");
database.execute("delete from incr4");
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");
database.execute("create table indexed(b varchar(255), c integer)");
database.execute("create unique index index1 on indexed(b, c)");
// Optionally load the large SQL file of your choice here
checkRoundTrip(database.dataStore());
}
public void testOrderOfForeignKeys() throws Exception {
database.execute("create table aa_refr(a_id integer)");
database.execute("create table bb_refd(a integer primary key)");
database.execute(
"alter table aa_refr add foreign key(a_id) references bb_refd(a)");
checkRoundTrip(database.dataStore());
}
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 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 testSelfReference() throws Exception {
database.execute("create table foo(id integer," +
"parent integer," +
"foreign key(parent) references foo(id))");
checkRoundTrip(database.dataStore());
}
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 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());
}
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 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 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 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 testCompare() throws Exception {
assertCompareEqual("create TABLE\n aa(a integer)",
"CREATE table aa ( a int ) ");
}
public void testCompareCase() throws Exception {
String lowercase = dump(load("create TABLE\n aa(a integer)"));
assertEquals("CREATE TABLE aa(\n a INTEGER\n);\n\n", lowercase);
String uppercase = dump(load("CREATE table AA ( a int ) "));
assertEquals("CREATE TABLE AA(\n a INTEGER\n);\n\n", uppercase);
}
public void testTableOrder() throws Exception {
assertCompareEqual(
"create table aa(a integer);" +
"create table bb(b integer);",
"create table bb(b integer);" +
"create table aa(a integer)");
}
public void testCaseDoesNotAffectTableOrder() throws Exception {
TableNode aa = new TableNode("aa");
TableNode bb = new TableNode("BB");
TableNode upperAa = new TableNode("AA");
assertTrue(aa.backupOrdering(bb) < 0);
assertTrue(bb.backupOrdering(aa) > 0);
assertTrue(upperAa.backupOrdering(bb) < 0);
assertTrue(bb.backupOrdering(upperAa) > 0);
/* We don't find it interesting how aa compares to upperAa,
because they won't both be in the same schema.
*/
}
public void testRowOrder() throws Exception {
assertCompareEqual(
"create table aa(a integer);" +
"insert into aa(a) values(7);" +
"insert into aa(a) values(4);" +
"insert into aa(a) values(5);" +
"insert into aa(a) values(2);" +
"insert into aa(a) values(3);" +
"insert into aa(a) values(1);",
"create table aa(a integer);" +
"insert into aa(a) values(5);" +
"insert into aa(a) values(1);" +
"insert into aa(a) values(3);" +
"insert into aa(a) values(7);" +
"insert into aa(a) values(4);" +
"insert into aa(a) values(2);"
);
}
public void testIdenticalRows() throws Exception {
try {
dump(load(
"create table aa(a integer, b varchar(255));" +
"insert into aa(a, b) values(7, 'hi');" +
"insert into aa(a, b) values(7, 'hi');"
));
fail();
}
catch (MayflyException e) {
assertEquals("cannot dump: table aa has duplicate rows",
e.getMessage());
}
}
private void assertCompareEqual(String first, String second) {
assertEquals(dump(load(first)), dump(load(second)));
}
private Database load(String second) {
Database aDatabase = new Database();
aDatabase.executeScript(new StringReader(second));
return aDatabase;
}
/**
* From a datastore, dump it, then load from that dump,
* dump again, and compare the two dumps.
*
* 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);
}
private String dumpData() throws IOException {
StringWriter out = new StringWriter();
new SqlDumper().data(database.dataStore(), out);
return out.toString();
}
private String dump() {
return dump(database);
}
private String dump(Database aDatabase) {
return new SqlDumper().dump(aDatabase.dataStore());
}
}