@Test public void shouldHaveColumns() { assertThat(table.retrieveColumnNames()).containsExactly("C1", "C2", "C3", "C4"); assertThat(table.columns()).containsExactly(c1, c2, c3, c4); }
@Test public void shouldParseCreateViewStatementColumnAliasInnerSelect() { String ddl = "CREATE TABLE foo ( " + System.lineSeparator() + " c1 INTEGER NOT NULL AUTO_INCREMENT, " + System.lineSeparator() + " c2 VARCHAR(22) " + System.lineSeparator() + "); " + System.lineSeparator(); String ddl2 = "CREATE VIEW fooView(w1) AS (SELECT foo2.c2 as w1 FROM (SELECT c1 as c2 FROM foo) AS foo2)" + System.lineSeparator(); parser = new MysqlDdlParserWithSimpleTestListener(listener, true); parser.parse(ddl, tables); parser.parse(ddl2, tables); assertThat(tables.size()).isEqualTo(2); Table foo = tables.forTable(new TableId(null, null, "fooView")); assertThat(foo).isNotNull(); assertThat(foo.retrieveColumnNames()).containsExactly("w1"); assertThat(foo.primaryKeyColumnNames()).isEmpty(); assertColumn(foo, "w1", "INTEGER", Types.INTEGER, -1, -1, false, true, true); }
@Test public void shouldParseCreateTableStatementWithSignedTypes() { String ddl = "CREATE TABLE foo ( " + System.lineSeparator() + " c1 BIGINT SIGNED NOT NULL, " + System.lineSeparator() + " c2 INT UNSIGNED NOT NULL " + System.lineSeparator() + "); " + System.lineSeparator(); parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table foo = tables.forTable(new TableId(null, null, "foo")); assertThat(foo).isNotNull(); assertThat(foo.retrieveColumnNames()).containsExactly("c1", "c2"); assertThat(foo.primaryKeyColumnNames()).isEmpty(); assertColumn(foo, "c1", "BIGINT SIGNED", Types.BIGINT, -1, -1, false, false, false); assertColumn(foo, "c2", "INT UNSIGNED", Types.INTEGER, -1, -1, false, false, false); }
@Test public void shouldParseCreateViewStatementColumnAlias() { String ddl = "CREATE TABLE foo ( " + System.lineSeparator() + " c1 INTEGER NOT NULL AUTO_INCREMENT, " + System.lineSeparator() + " c2 VARCHAR(22) " + System.lineSeparator() + "); " + System.lineSeparator(); String ddl2 = "CREATE VIEW fooView(w1) AS (SELECT c2 as w1 FROM foo)" + System.lineSeparator(); parser = new MysqlDdlParserWithSimpleTestListener(listener, true); parser.parse(ddl, tables); parser.parse(ddl2, tables); assertThat(tables.size()).isEqualTo(2); Table foo = tables.forTable(new TableId(null, null, "fooView")); assertThat(foo).isNotNull(); assertThat(foo.retrieveColumnNames()).containsExactly("w1"); assertThat(foo.primaryKeyColumnNames()).isEmpty(); assertColumn(foo, "w1", "VARCHAR", Types.VARCHAR, 22, -1, true, false, false); }
@Test public void shouldParseCreateTableStatementWithSingleGeneratedAndPrimaryKeyColumn() { String ddl = "CREATE TABLE foo ( " + System.lineSeparator() + " c1 INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, " + System.lineSeparator() + " c2 VARCHAR(22) " + System.lineSeparator() + "); " + System.lineSeparator(); parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table foo = tables.forTable(new TableId(null,null,"foo")); assertThat(foo).isNotNull(); assertThat(foo.retrieveColumnNames()).containsExactly("c1","c2"); assertThat(foo.primaryKeyColumnNames()).containsExactly("c1"); assertColumn(foo,"c1","INTEGER",Types.INTEGER,-1,-1,false,true,true); assertColumn(foo,"c2","VARCHAR",Types.VARCHAR,22,-1,true,false,false); }
@Test public void shouldParseCreateTableStatementWithSingleGeneratedAndPrimaryKeyColumn() { String ddl = "CREATE TABLE foo ( " + System.lineSeparator() + " c1 INTEGER NOT NULL AUTO_INCREMENT, " + System.lineSeparator() + " c2 VARCHAR(22) " + System.lineSeparator() + "); " + System.lineSeparator(); parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table foo = tables.forTable(new TableId(null, null, "foo")); assertThat(foo).isNotNull(); assertThat(foo.retrieveColumnNames()).containsExactly("c1", "c2"); assertThat(foo.primaryKeyColumnNames()).isEmpty(); assertColumn(foo, "c1", "INTEGER", Types.INTEGER, -1, -1, false, true, true); assertColumn(foo, "c2", "VARCHAR", Types.VARCHAR, 22, -1, true, false, false); }
@Test public void shouldParseCreateViewStatementStartSelect() { String ddl = "CREATE TABLE foo ( " + System.lineSeparator() + " c1 INTEGER NOT NULL AUTO_INCREMENT, " + System.lineSeparator() + " c2 VARCHAR(22) " + System.lineSeparator() + "); " + System.lineSeparator(); String ddl2 = "CREATE VIEW fooView AS (SELECT * FROM foo)" + System.lineSeparator(); parser = new MysqlDdlParserWithSimpleTestListener(listener, true); parser.parse(ddl, tables); parser.parse(ddl2, tables); assertThat(tables.size()).isEqualTo(2); Table foo = tables.forTable(new TableId(null, null, "fooView")); assertThat(foo).isNotNull(); assertThat(foo.retrieveColumnNames()).containsExactly("c1", "c2"); assertThat(foo.primaryKeyColumnNames()).isEmpty(); assertColumn(foo, "c1", "INTEGER", Types.INTEGER, -1, -1, false, true, true); assertColumn(foo, "c2", "VARCHAR", Types.VARCHAR, 22, -1, true, false, false); }
@Test public void shouldParseCreateTableStatementWithSingleGeneratedColumnAsPrimaryKey() { String ddl = "CREATE TABLE my.foo ( " + System.lineSeparator() + " c1 INTEGER NOT NULL AUTO_INCREMENT, " + System.lineSeparator() + " c2 VARCHAR(22), " + System.lineSeparator() + " PRIMARY KEY (c1)" + System.lineSeparator() + "); " + System.lineSeparator(); parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table foo = tables.forTable(new TableId("my", null, "foo")); assertThat(foo).isNotNull(); assertThat(foo.retrieveColumnNames()).containsExactly("c1", "c2"); assertThat(foo.primaryKeyColumnNames()).containsExactly("c1"); assertColumn(foo, "c1", "INTEGER", Types.INTEGER, -1, -1, false, true, true); assertColumn(foo, "c2", "VARCHAR", Types.VARCHAR, 22, -1, true, false, false); parser.parse("DROP TABLE my.foo", tables); assertThat(tables.size()).isEqualTo(0); }
@Test public void shouldParseCreateTableStatementWithSingleGeneratedColumnAsPrimaryKey() { String ddl = "CREATE TABLE my.foo ( " + System.lineSeparator() + " c1 INTEGER GENERATED ALWAYS AS IDENTITY NOT NULL, " + System.lineSeparator() + " c2 VARCHAR(22), " + System.lineSeparator() + " PRIMARY KEY (c1)" + System.lineSeparator() + "); " + System.lineSeparator(); parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table foo = tables.forTable(new TableId("my",null,"foo")); assertThat(foo).isNotNull(); assertThat(foo.retrieveColumnNames()).containsExactly("c1","c2"); assertThat(foo.primaryKeyColumnNames()).containsExactly("c1"); assertColumn(foo,"c1","INTEGER",Types.INTEGER,-1,-1,false,true,true); assertColumn(foo,"c2","VARCHAR",Types.VARCHAR,22,-1,true,false,false); parser.parse("DROP TABLE my.foo", tables); assertThat(tables.size()).isEqualTo(0); }
@Test public void shouldParseCreateTableStatementWithCharacterSetForColumns() { String ddl = "CREATE TABLE t ( col1 VARCHAR(25) CHARACTER SET greek ); "; parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table t = tables.forTable(new TableId(null, null, "t")); assertThat(t).isNotNull(); assertThat(t.retrieveColumnNames()).containsExactly("col1"); assertThat(t.primaryKeyColumnNames()).isEmpty(); assertColumn(t, "col1", "VARCHAR", Types.VARCHAR, 25, -1, true, false, false); }
@Test public void parseTableWithPageChecksum() { String ddl = "CREATE TABLE t (id INT NOT NULL, PRIMARY KEY (`id`)) PAGE_CHECKSUM=1;" + "ALTER TABLE t PAGE_CHECKSUM=0;"; parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table t = tables.forTable(new TableId(null, null, "t")); assertThat(t).isNotNull(); assertThat(t.retrieveColumnNames()).containsExactly("id"); assertThat(t.primaryKeyColumnNames()).hasSize(1); assertColumn(t, "id", "INT", Types.INTEGER, -1, -1, false, false, false); }
@Test public void shouldParseCreateUserTable() { String ddl = "CREATE TABLE IF NOT EXISTS user ( Host char(60) binary DEFAULT '' NOT NULL, User char(32) binary DEFAULT '' NOT NULL, Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Event_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL, ssl_cipher BLOB NOT NULL, x509_issuer BLOB NOT NULL, x509_subject BLOB NOT NULL, max_questions int(11) unsigned DEFAULT 0 NOT NULL, max_updates int(11) unsigned DEFAULT 0 NOT NULL, max_connections int(11) unsigned DEFAULT 0 NOT NULL, max_user_connections int(11) unsigned DEFAULT 0 NOT NULL, plugin char(64) DEFAULT 'mysql_native_password' NOT NULL, authentication_string TEXT, password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, password_last_changed timestamp NULL DEFAULT NULL, password_lifetime smallint unsigned NULL DEFAULT NULL, account_locked ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, PRIMARY KEY Host (Host,User) ) engine=MyISAM CHARACTER SET utf8 COLLATE utf8_bin comment='Users and global privileges';"; parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table foo = tables.forTable(new TableId(null, null, "user")); assertThat(foo).isNotNull(); assertThat(foo.retrieveColumnNames()).contains("Host", "User", "Select_priv"); assertColumn(foo, "Host", "CHAR BINARY", Types.BINARY, 60, -1, false, false, false); parser.parse("DROP TABLE user", tables); assertThat(tables.size()).isEqualTo(0); }
@Test @FixFor("DBZ-429") public void parseTableWithNegativeDefault() { String ddl = "CREATE TABLE t (id INT NOT NULL, myvalue INT DEFAULT -10, PRIMARY KEY (`id`));"; parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table t = tables.forTable(new TableId(null, null, "t")); assertThat(t).isNotNull(); assertThat(t.retrieveColumnNames()).containsExactly("id", "myvalue"); assertThat(t.primaryKeyColumnNames()).hasSize(1); assertColumn(t, "myvalue", "INT", Types.INTEGER, -1, -1, true, false, false); }
@FixFor("DBZ-160") @Test public void shouldParseCreateTableWithEnumDefault() { String ddl = "CREATE TABLE t ( c1 ENUM('a','b','c') NOT NULL DEFAULT 'b', c2 ENUM('a', 'b', 'c') NOT NULL DEFAULT 'a');"; parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table t = tables.forTable(new TableId(null, null, "t")); assertThat(t).isNotNull(); assertThat(t.retrieveColumnNames()).containsExactly("c1", "c2"); assertThat(t.primaryKeyColumnNames()).isEmpty(); assertColumn(t, "c1", "ENUM", Types.CHAR, 1, -1, false, false, false); assertColumn(t, "c2", "ENUM", Types.CHAR, 1, -1, false, false, false); }
@FixFor("DBZ-160") @Test public void shouldParseCreateTableWithBitDefault() { String ddl = "CREATE TABLE t ( c1 Bit(2) NOT NULL DEFAULT b'1', c2 Bit(2) NOT NULL);"; parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table t = tables.forTable(new TableId(null, null, "t")); assertThat(t).isNotNull(); assertThat(t.retrieveColumnNames()).containsExactly("c1", "c2"); assertThat(t.primaryKeyColumnNames()).isEmpty(); assertColumn(t, "c1", "BIT", Types.BIT, 2, -1, false, false, false); assertColumn(t, "c2", "BIT", Types.BIT, 2, -1, false, false, false); }
@Test public void parseDdlForDecAndFixed() { String ddl = "CREATE TABLE t ( c1 DEC(2) NOT NULL, c2 FIXED(1,0) NOT NULL, c3 NUMERIC(3) NOT NULL);"; parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table t = tables.forTable(new TableId(null, null, "t")); assertThat(t).isNotNull(); assertThat(t.retrieveColumnNames()).containsExactly("c1", "c2", "c3"); assertThat(t.primaryKeyColumnNames()).isEmpty(); assertColumn(t, "c1", "DEC", Types.DECIMAL, 2, 0, false, false, false); assertColumn(t, "c2", "FIXED", Types.DECIMAL, 1, 0, false, false, false); assertColumn(t, "c3", "NUMERIC", Types.NUMERIC, 3, 0, false, false, false); }
@Test @FixFor({"DBZ-615", "DBZ-727"}) public void parseDdlForUnscaledDecAndFixed() { String ddl = "CREATE TABLE t ( c1 DEC NOT NULL, c2 FIXED(3) NOT NULL, c3 NUMERIC NOT NULL);"; parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table t = tables.forTable(new TableId(null, null, "t")); assertThat(t).isNotNull(); assertThat(t.retrieveColumnNames()).containsExactly("c1", "c2", "c3"); assertThat(t.primaryKeyColumnNames()).isEmpty(); assertColumn(t, "c1", "DEC", Types.DECIMAL, 10, 0, false, false, false); assertColumn(t, "c2", "FIXED", Types.DECIMAL, 3, 0, false, false, false); assertColumn(t, "c3", "NUMERIC", Types.NUMERIC, 10, 0, false, false, false); }
@Test public void shouldFindGeneratedColumns() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).generated(true).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).generated(true).create(); editor.addColumns(c1, c2, c3); editor.setPrimaryKeyNames("C1"); table = editor.create(); assertThat(table.retrieveColumnNames()).containsExactly("C1", "C2", "C3"); table.columns().forEach(col -> { assertThat(table.isGenerated(col.name())).isEqualTo(col.isGenerated()); }); assertValidPositions(editor); }
@Test public void shouldFindAutoIncrementedColumns() { editor.tableId(id); Column c1 = columnEditor.name("C1").type("VARCHAR").jdbcType(Types.VARCHAR).length(10).position(1).create(); Column c2 = columnEditor.name("C2").type("NUMBER").jdbcType(Types.NUMERIC).length(5).autoIncremented(true).create(); Column c3 = columnEditor.name("C3").type("DATE").jdbcType(Types.DATE).autoIncremented(true).create(); editor.addColumns(c1, c2, c3); editor.setPrimaryKeyNames("C1"); table = editor.create(); assertThat(table.retrieveColumnNames()).containsExactly("C1", "C2", "C3"); table.columns().forEach(col -> { assertThat(table.isAutoIncremented(col.name())).isEqualTo(col.isAutoIncremented()); }); assertValidPositions(editor); }
@Test public void shouldParseCreateTableWithEnumAndSetColumns() { String ddl = "CREATE TABLE t ( c1 ENUM('a','b','c') NOT NULL, c2 SET('a','b','c') NULL);"; parser.parse(ddl, tables); assertThat(tables.size()).isEqualTo(1); Table t = tables.forTable(new TableId(null, null, "t")); assertThat(t).isNotNull(); assertThat(t.retrieveColumnNames()).containsExactly("c1", "c2"); assertThat(t.primaryKeyColumnNames()).isEmpty(); assertColumn(t, "c1", "ENUM", Types.CHAR, 1, -1, false, false, false); assertColumn(t, "c2", "SET", Types.CHAR, 5, -1, true, false, false); assertThat(t.columnWithName("c1").position()).isEqualTo(1); assertThat(t.columnWithName("c2").position()).isEqualTo(2); }