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()); }