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.
On 2012-02-22 13:29, Steve McLeod wrote:
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 [email protected].
> > To unsubscribe from this group, send email
[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.
--
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.
