Hi Noel,
I'm attaching a patch that extends the query parser to support "ALTER TABLE
CHANGE [COLUMN] *old_col_name* *column_definition*", "ALTER TABLE DROP
INDEX index_name" and "ALTER TABLE DROP FOREIGN KEY fk_symbol" (as
documented in http://dev.mysql.com/doc/refman/5.5/en/alter-table.html).
ALTER TABLE CHANGE is limited insofar as MySQL supports complete column
redefinitions whereas the patch only enabled column renames.
I've extended the test script but didn't add any other testing code.
I'd be happy about any feedback regarding the patch.
Thanks,
Peter
Am Freitag, 14. März 2014 08:17:19 UTC+1 schrieb Noel Grandin:
>
>
> Other than reading the source code, unfortunately not.
>
> However, we are always open to patches to improve the situation :-)
>
> On 2014-03-13 12:51, Peter Jentsch wrote:
> > Hi,
> >
> > we're currently using H2 as embedded database for unit tests for as
> system that runs on MySQL in production. The DDL is
> > versioned using FlyWay. We haven't faced any compatibility issues as
> long as we only added to the schema. Recently we
> > started to modify the schema and found it hard to find compatible
> options for some statements (column rename, drop
> > constraint). Is there any comprehensive overview of which MySQL DDL
> statements are supported by H2 in MySQL
> > compatibility mode?
>
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
Index: src/test/org/h2/test/testScript.sql
===================================================================
--- src/test/org/h2/test/testScript.sql (revision 5532)
+++ src/test/org/h2/test/testScript.sql (working copy)
@@ -828,6 +828,35 @@
alter table test alter column id int auto_increment;
> ok
+create table othertest(id int primary key, name varchar);
+> ok
+
+alter table othertest add constraint fk foreign key(id) references test(id);
+> ok
+
+alter table othertest drop foreign key fk;
+> ok
+
+create unique index idx on othertest(name);
+> ok
+
+alter table othertest drop index idx;
+> ok
+
+drop table othertest;
+> ok
+
+insert into test(id) values(1);
+> update count: 1
+
+alter table test change column id id2 int;
+> ok
+
+select id2 from test;
+> ID2
+> ---
+> 1
+
drop table test;
> ok
Index: src/main/org/h2/command/Parser.java
===================================================================
--- src/main/org/h2/command/Parser.java (revision 5532)
+++ src/main/org/h2/command/Parser.java (working copy)
@@ -5302,6 +5302,21 @@
session, getSchema(), ifExists);
command.setConstraintName(constraintName);
return command;
+ } else if (readIf("FOREIGN")) {
+ read("KEY");
+ String constraintName = readIdentifierWithSchema(table
+ .getSchema().getName());
+ checkSchema(table.getSchema());
+ AlterTableDropConstraint command = new AlterTableDropConstraint(
+ session, getSchema(), false);
+ command.setConstraintName(constraintName);
+ return command;
+ } else if (readIf("INDEX")) {
+ // MySQL compatibility
+ String indexName = readIdentifierWithSchema();
+ DropIndex command = new DropIndex(session, getSchema());
+ command.setIndexName(indexName);
+ return command;
} else if (readIf("PRIMARY")) {
read("KEY");
Index idx = table.getPrimaryKey();
@@ -5322,6 +5337,20 @@
command.setOldColumn(table.getColumn(columnName));
return command;
}
+ } else if (readIf("CHANGE")) {
+ // MySQL compatibility
+ readIf("COLUMN");
+ String columnName = readColumnIdentifier();
+ Column column = table.getColumn(columnName);
+ String newColumnName = readColumnIdentifier();
+ // new column type ignored. RENAME and MODIFY are
+ // a single command in MySQL but two different commands in H2.
+ parseColumnForTable(newColumnName, column.isNullable());
+ AlterTableRenameColumn command = new AlterTableRenameColumn(session);
+ command.setTable(table);
+ command.setColumn(column);
+ command.setNewColumnName(newColumnName);
+ return command;
} else if (readIf("MODIFY")) {
// MySQL compatibility
readIf("COLUMN");
