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 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 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"));
}
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 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 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 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 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 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());
}
/**
* 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 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 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());
}
private String dump(Database aDatabase) {
return new SqlDumper().dump(aDatabase.dataStore());
}
private String dumpData() throws IOException {
StringWriter out = new StringWriter();
new SqlDumper().data(database.dataStore(), out);
return out.toString();
}
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 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());
}
