Using "ALTER TABLE ADD..." to add multiple columns

[Steve McLeod]

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

[Steve McLeod]

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

[Noel Grandin]

If you send us a patch, we can probably include it into the normal H2
codebase :-)

> --
> You received this message because you are subscribed to the Google Groups "H2 Database" group.
> To post to this group, send email to h2-da...@googlegroups.com.
> To unsubscribe from this group, send email to h2-database...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
>

[Steve McLeod]

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

[Noel Grandin]

Hi Steve

Thanks for the patch. Please can you:

(1) Resend this patch as an attachment, the mailer seems to have corrupted the whitespace and broken lines in some places, which makes it tricky to apply.

(2) Cconfirm that you are contributing this patch under the terms of the H2 license.
http://www.h2database.com/html/license.html

Thanks, Noel.

[Steve McLeod]

Hi Noel,

The patch is attached.

I wrote the code, it's mine, and I'm contributing it to H2 for distribution multiple-licensed under the H2 License, version 1.0, and under the Eclipse Public License, version 1.0 (http://h2database.com/html/license.html).

Regards,

Steve

> > To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.

> > 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 h2-da...@googlegroups.com

.
To unsubscribe from this group, send email to h2-database+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Noel Grandin]

Thanks, patch committed.