Hi Noel & Thomas,
A patch is included, with updated tests and grammar too.
A problem I had with preparing this was with updating the grammar. The
generated railroad diagram just couldn't format the changed grammar
for ALTER TABLE ADD in a nice fashion. So I changed it slightly to
use
"columnDefinition" instead of
"name dataType [ DEFAULT expression ] [ [ NOT ] NULL ]
[ AUTO_INCREMENT | IDENTITY ]"
I hope this is acceptable.
START OF PATCH:
Index: src/docsrc/help/help.csv
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>MacRoman
===================================================================
--- src/docsrc/help/help.csv (revision 4126)
+++ src/docsrc/help/help.csv (revision )
@@ -211,8 +211,9 @@
"
"Commands (DDL)","ALTER TABLE ADD","
-ALTER TABLE tableName ADD [ IF NOT EXISTS ] name dataType [ DEFAULT
expression ]
-[ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY ] [ BEFORE columnName ]
+ALTER TABLE tableName ADD [ COLUMN ]
+{ [ IF NOT EXISTS ] columnDefinition [ BEFORE columnName ]
+ | ( { columnDefinition } [,...] ) }
","
Adds a new column to a table.
This command commits an open transaction.
Index: src/test/org/h2/test/db/TestAlter.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>MacRoman
===================================================================
--- src/test/org/h2/test/db/TestAlter.java (revision 4126)
+++ src/test/org/h2/test/db/TestAlter.java (revision )
@@ -40,6 +40,7 @@
testAlterTableAlterColumn();
testAlterTableDropIdentityColumn();
testAlterTableAddColumnIfNotExists();
+ testAlterTableAddMultipleColumns();
testAlterTableAlterColumn2();
conn.close();
deleteDb("alter");
@@ -133,6 +134,15 @@
stat.execute("create table t(x varchar) as select 'x'");
stat.execute("alter table t add if not exists y int");
stat.execute("select x, y from t");
+ stat.execute("drop table t");
+ }
+
+ private void testAlterTableAddMultipleColumns() throws
SQLException {
+ stat.execute("create table t(x varchar) as select 'x'");
+ stat.execute("alter table t add (y int, z varchar)");
+ stat.execute("drop table t");
+ stat.execute("create table t(x varchar) as select 'x'");
+ stat.execute("alter table t add (y int)");
stat.execute("drop table t");
}
Index: src/main/org/h2/command/Parser.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>MacRoman
===================================================================
--- src/main/org/h2/command/Parser.java (revision 4126)
+++ src/main/org/h2/command/Parser.java (revision )
@@ -4948,19 +4948,37 @@
private AlterTableAlterColumn parseAlterTableAddColumn(Table
table) {
readIf("COLUMN");
- boolean ifNotExists = readIfNoExists();
Schema schema = table.getSchema();
AlterTableAlterColumn command = new
AlterTableAlterColumn(session, schema);
- command.setIfNotExists(ifNotExists);
command.setType(CommandInterface.ALTER_TABLE_ADD_COLUMN);
command.setTable(table);
+ ArrayList<Column> columnsToAdd = New.arrayList();
+
+ boolean b = readIf("(");
+ if (b) {
+ command.setIfNotExists(false);
+ do{
- String columnName = readColumnIdentifier();
- Column column = parseColumnForTable(columnName, true);
+ String columnName = readColumnIdentifier();
+ Column column = parseColumnForTable(columnName,
true);
- command.setNewColumn(column);
+ columnsToAdd.add(column);
+ } while (readIf(","));
+ read(")");
+ command.setNewColumns(columnsToAdd);
+
+ } else {
+ boolean ifNotExists = readIfNoExists();
+ command.setIfNotExists(ifNotExists);
+ String columnName = readColumnIdentifier();
+ Column column = parseColumnForTable(columnName, true);
+ columnsToAdd.add(column);
- if (readIf("BEFORE")) {
- command.setAddBefore(readColumnIdentifier());
- }
+ if (readIf("BEFORE")) {
+ command.setAddBefore(readColumnIdentifier());
+ }
+
+ }
+ command.setNewColumns(columnsToAdd);
return command;
+
}
private int parseAction() {
Index: src/docsrc/html/changelog.html
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>UTF-8
===================================================================
--- src/docsrc/html/changelog.html (revision 4126)
+++ src/docsrc/html/changelog.html (revision )
@@ -26,6 +26,7 @@
</li><li>CSV tool: new feature to preserve the case sensitivity of
column names
(option caseSensitiveColumnNames).
</li><li>PostgreSQL compatibility: LOG(x) is base 10 in the
PostgreSQL mode.
+</li><li>ALTER TABLE ADD can now add more than one column at a time.
</li></ul>
<h2>Version 1.3.164 (2012-02-03)</h2>
Index: src/main/org/h2/command/ddl/AlterTableAlterColumn.java
IDEA additional info:
Subsystem: com.intellij.openapi.diff.impl.patch.CharsetEP
<+>MacRoman
===================================================================
--- src/main/org/h2/command/ddl/AlterTableAlterColumn.java (revision
4126)
+++ src/main/org/h2/command/ddl/AlterTableAlterColumn.java (revision )
@@ -55,6 +55,7 @@
private Expression newSelectivity;
private String addBefore;
private boolean ifNotExists;
+ private ArrayList<Column> columnsToAdd;
public AlterTableAlterColumn(Session session, Schema schema) {
super(session, schema);
@@ -82,81 +83,89 @@
if (newColumn != null) {
checkDefaultReferencesTable(newColumn.getDefaultExpression());
}
+ if (columnsToAdd != null) {
+ for (Column column : columnsToAdd) {
+
checkDefaultReferencesTable(column.getDefaultExpression());
+ }
+ }
switch (type) {
- case CommandInterface.ALTER_TABLE_ALTER_COLUMN_NOT_NULL: {
- if (!oldColumn.isNullable()) {
- // no change
- break;
- }
- checkNoNullValues();
- oldColumn.setNullable(false);
- db.update(session, table);
- break;
- }
- case CommandInterface.ALTER_TABLE_ALTER_COLUMN_NULL: {
- if (oldColumn.isNullable()) {
- // no change
- break;
- }
- checkNullable();
- oldColumn.setNullable(true);
- db.update(session, table);
- break;
- }
- case CommandInterface.ALTER_TABLE_ALTER_COLUMN_DEFAULT: {
- checkDefaultReferencesTable(defaultExpression);
- oldColumn.setSequence(null);
- oldColumn.setDefaultExpression(session,
defaultExpression);
- removeSequence(sequence);
- db.update(session, table);
- break;
- }
- case CommandInterface.ALTER_TABLE_ALTER_COLUMN_CHANGE_TYPE: {
- // if the change is only increasing the precision, then
we don't
- // need to copy the table because the length is only a
constraint,
- // and does not affect the storage structure.
- if (oldColumn.isWideningConversion(newColumn)) {
- convertAutoIncrementColumn(newColumn);
- oldColumn.copy(newColumn);
- db.update(session, table);
- } else {
- oldColumn.setSequence(null);
- oldColumn.setDefaultExpression(session, null);
- oldColumn.setConvertNullToDefault(false);
- if (oldColumn.isNullable() && !
newColumn.isNullable()) {
- checkNoNullValues();
- } else if (!oldColumn.isNullable() &&
newColumn.isNullable()) {
- checkNullable();
- }
- convertAutoIncrementColumn(newColumn);
- copyData();
- }
- break;
- }
- case CommandInterface.ALTER_TABLE_ADD_COLUMN: {
+ case CommandInterface.ALTER_TABLE_ALTER_COLUMN_NOT_NULL:
{
+ if (!oldColumn.isNullable()) {
+ // no change
+ break;
+ }
+ checkNoNullValues();
+ oldColumn.setNullable(false);
+ db.update(session, table);
+ break;
+ }
+ case CommandInterface.ALTER_TABLE_ALTER_COLUMN_NULL: {
+ if (oldColumn.isNullable()) {
+ // no change
+ break;
+ }
+ checkNullable();
+ oldColumn.setNullable(true);
+ db.update(session, table);
+ break;
+ }
+ case CommandInterface.ALTER_TABLE_ALTER_COLUMN_DEFAULT: {
+ checkDefaultReferencesTable(defaultExpression);
+ oldColumn.setSequence(null);
+ oldColumn.setDefaultExpression(session,
defaultExpression);
+ removeSequence(sequence);
+ db.update(session, table);
+ break;
+ }
+ case
CommandInterface.ALTER_TABLE_ALTER_COLUMN_CHANGE_TYPE: {
+ // if the change is only increasing the precision,
then we don't
+ // need to copy the table because the length is only
a constraint,
+ // and does not affect the storage structure.
+ if (oldColumn.isWideningConversion(newColumn)) {
+ convertAutoIncrementColumn(newColumn);
+ oldColumn.copy(newColumn);
+ db.update(session, table);
+ } else {
+ oldColumn.setSequence(null);
+ oldColumn.setDefaultExpression(session, null);
+ oldColumn.setConvertNullToDefault(false);
+ if (oldColumn.isNullable() && !
newColumn.isNullable()) {
+ checkNoNullValues();
+ } else if (!oldColumn.isNullable() &&
newColumn.isNullable()) {
+ checkNullable();
+ }
+ convertAutoIncrementColumn(newColumn);
+ copyData();
+ }
+ break;
+ }
+ case CommandInterface.ALTER_TABLE_ADD_COLUMN: {
- if (ifNotExists &&
table.doesColumnExist(newColumn.getName())) {
+ // ifNotExists only supported for single column add
+ if (ifNotExists && columnsToAdd.size() == 1 &&
table.doesColumnExist(columnsToAdd.get(0).getName())) {
- break;
- }
+ break;
+ }
- convertAutoIncrementColumn(newColumn);
+ for (Column column : columnsToAdd) {
+ convertAutoIncrementColumn(column);
+ }
- copyData();
- break;
- }
- case CommandInterface.ALTER_TABLE_DROP_COLUMN: {
- if (table.getColumns().length == 1) {
- throw
DbException.get(ErrorCode.CANNOT_DROP_LAST_COLUMN,
oldColumn.getSQL());
- }
- table.dropSingleColumnConstraintsAndIndexes(session,
oldColumn);
- copyData();
- break;
- }
- case CommandInterface.ALTER_TABLE_ALTER_COLUMN_SELECTIVITY: {
- int value =
newSelectivity.optimize(session).getValue(session).getInt();
- oldColumn.setSelectivity(value);
- db.update(session, table);
- break;
- }
- default:
- DbException.throwInternalError("type=" + type);
+ copyData();
+ break;
+ }
+ case CommandInterface.ALTER_TABLE_DROP_COLUMN: {
+ if (table.getColumns().length == 1) {
+ throw
DbException.get(ErrorCode.CANNOT_DROP_LAST_COLUMN,
oldColumn.getSQL());
+ }
+ table.dropSingleColumnConstraintsAndIndexes(session,
oldColumn);
+ copyData();
+ break;
+ }
+ case
CommandInterface.ALTER_TABLE_ALTER_COLUMN_SELECTIVITY: {
+ int value =
newSelectivity.optimize(session).getValue(session).getInt();
+ oldColumn.setSelectivity(value);
+ db.update(session, table);
+ break;
+ }
+ default:
+ DbException.throwInternalError("type=" + type);
}
return 0;
}
@@ -266,7 +275,9 @@
} else {
position = table.getColumn(addBefore).getColumnId();
}
- newColumns.add(position, newColumn);
+ for (Column column : columnsToAdd) {
+ newColumns.add(position++, column);
+ }
} else if (type ==
CommandInterface.ALTER_TABLE_ALTER_COLUMN_CHANGE_TYPE) {
int position = oldColumn.getColumnId();
newColumns.remove(position);
@@ -298,7 +309,7 @@
if (columnList.length() > 0) {
columnList.append(", ");
}
- if (type == CommandInterface.ALTER_TABLE_ADD_COLUMN && nc
== newColumn) {
+ if (type == CommandInterface.ALTER_TABLE_ADD_COLUMN &&
columnsToAdd.contains(nc)) {
Expression def = nc.getDefaultExpression();
columnList.append(def == null ? "NULL" :
def.getSQL());
} else {
@@ -470,4 +481,7 @@
this.ifNotExists = ifNotExists;
}
+ public void setNewColumns(ArrayList<Column> columnsToAdd) {
+ this.columnsToAdd = columnsToAdd;
+ }
}
END OF PATCH
On Feb 21, 9:15 pm, Noel Grandin <[email protected]> wrote:
> If you send us a patch, we can probably include it into the normal H2
> codebase :-)
>
>
>
>
>
>
>
> On Tue, Feb 21, 2012 at 20:21, Steve McLeod <[email protected]> wrote:
> > More on this:
>
> > MySQL also supports multi-column ADD, as per this example:
>
> > ALTER TABLE customer ADD (foo INTEGER, foo2 INTEGER);
>
> > I had a go at adding this feature to H2 as per Oracle's and MySQL's
> > syntax, and I've had success. Now adding four columns at once takes
> > about the same time of previously adding just one column. For my
> > specific use case this is a great win.
>
> > The use case is updating existing user databases that may be Gigaytes
> > in size. A 30 minute update is far better than a 2 hour update.
>
> > Regards,
>
> > Steve
>
> > On Feb 20, 8:25 pm, Steve McLeod <[email protected]> wrote:
> >> Hi Thomas & co,
>
> >> Currently if I want to add 2 or more columns to a table in H2, I need
> >> to execute multiple ALTER TABLE ADD commands:
>
> >> ALTER TABLE customer ADD foo INTEGER;
> >> ALTER TABLE customer ADD foo2 INTEGER;
>
> >> Oracle allows adding two columns at once:
>
> >> ALTER TABLE customer ADD (foo INTEGER, foo2 INTEGER);
>
> >> So does Microsft SQL Server's T-SQL:
>
> >> ALTER TABLE customer ADD foo INTEGER, foo2 INTEGER;
>
> >> I'd like to have a go at adding this to H2. If I do, is this something
> >> you'd be likely to add to the SVN trunk? If so, is the SQL Server
> >> syntax or the Oracle syntax preferable? As far as I can tell, the
> >> SQL-92 standard doesn't cover this scenario.
>
> >> Regards,
>
> >> Steve
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "H2 Database" group.
> > To post to this group, send email to [email protected].
> > To unsubscribe from this group, send email to
> > [email protected].
> > For more options, visit this group
> > athttp://groups.google.com/group/h2-database?hl=en.
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
