Patch to allow indexes on local temporary tables

[Matt]

I took a look at the h2 code and found out why I was having trouble
creating indexes on local temporary tables on multiple connections.
The index creation code didn't have any logic to create session local
indexes for local temporary tables. This is a very rough patch to add
the notion of session local indexes that are created when an index is
created on a local temporary table. Let me know if I should post this
somewhere else.

Matt


Index: src/main/org/h2/table/TableData.java
===================================================================
--- src/main/org/h2/table/TableData.java (revision 928)
+++ src/main/org/h2/table/TableData.java (working copy)
@@ -219,7 +219,11 @@
index.setTemporary(temporary);

if (index.getCreateSQL() != null) {

index.setComment(indexComment);

- database.addSchemaObject(session, index);

+ if (temporary && !getGlobalTemporary()) {

+ session.addLocalTempTableIndex(index);

+ } else {

+ database.addSchemaObject(session, index);

+ }

// Need to update, because maybe the index is rebuilt at
startup,

// and so the head pos may have changed, which needs to
be stored now.

// addSchemaObject doesn't update the sys table at
startup

Index: src/main/org/h2/table/Table.java
===================================================================
--- src/main/org/h2/table/Table.java (revision 928)
+++ src/main/org/h2/table/Table.java (working copy)
@@ -382,6 +382,13 @@
constraints.remove(0);

database.removeSchemaObject(session, constraint);

}

+ if(getTemporary()&&!getGlobalTemporary()) {

+ while (getIndexes() != null && getIndexes().size() > 0) {

+ Index index = (Index) getIndexes().get(0);

+ getIndexes().remove(0);

+ database.removeSchemaObject(session, index);

+ }

+ }

ObjectArray rights = database.getAllRights();

for (int i = 0; i < rights.size(); i++) {

Right right = (Right) rights.get(i);

Index: src/main/org/h2/engine/Database.java
===================================================================
--- src/main/org/h2/engine/Database.java (revision 928)
+++ src/main/org/h2/engine/Database.java (working copy)
@@ -21,6 +21,7 @@
import org.h2.command.dml.SetTypes;

import org.h2.constant.ErrorCode;

import org.h2.constant.SysProperties;

+import org.h2.engine.DbObject;

import org.h2.index.Cursor;

import org.h2.index.Index;

import org.h2.index.IndexType;

@@ -1565,6 +1566,13 @@
return;

}

}

+ if (obj.getType() == DbObject.INDEX) {

+ Index index = (Index) obj;

+ if (index.getTable().getTemporary() && !
index.getTable().getGlobalTemporary()) {

+ session.removeLocalTempTableIndex(index);

+ return;

+ }

+ }

checkWritingAllowed();

Comment comment = findComment(obj);

if (comment != null) {

Index: src/main/org/h2/engine/Session.java
===================================================================
--- src/main/org/h2/engine/Session.java (revision 928)
+++ src/main/org/h2/engine/Session.java (working copy)
@@ -20,6 +20,7 @@
import org.h2.command.dml.SetTypes;

import org.h2.constant.ErrorCode;

import org.h2.constant.SysProperties;

+import org.h2.index.Index;

import org.h2.jdbc.JdbcConnection;

import org.h2.log.InDoubtTransaction;

import org.h2.log.LogSystem;

@@ -65,6 +66,7 @@
private HashMap savepoints;

private Exception stackTrace = new Exception();

private HashMap localTempTables;

+ private HashMap localTempTablesIndexes;

private int throttle;

private long lastThrottle;

private Command currentCommand;

@@ -204,6 +206,59 @@
table.removeChildrenAndResources(this);

}



+ /**

+ * Get the local temporary index if one exists with that name, or
null if not.

+ *

+ * @param name

+ * the table name

+ * @return the table, or null

+ */

+ public Index findLocalTempTableIndex(String name) {

+ Index t = null;

+ if (localTempTablesIndexes != null) {

+ t = (Index) localTempTablesIndexes.get(name);

+ }

+ return t;

+ }

+

+ public ObjectArray getLocalTempTablesIndexes() {

+ if (localTempTablesIndexes == null) {

+ return new ObjectArray();

+ }

+ ObjectArray list = new
ObjectArray(localTempTablesIndexes.values());

+ return list;

+ }

+

+ /**

+ * Add a local temporary index to this session.

+ *

+ * @param index

+ * the index to add

+ * @throws SQLException

+ * if a index with this name already exists

+ */

+ public void addLocalTempTableIndex(Index index) throws
SQLException {

+ if (localTempTablesIndexes == null) {

+ localTempTablesIndexes = new HashMap();

+ }

+ if (localTempTablesIndexes.get(index.getName()) != null) {

+ throw
Message.getSQLException(ErrorCode.INDEX_ALREADY_EXISTS_1,
index.getSQL());

+ }

+ localTempTablesIndexes.put(index.getName(), index);

+ }

+

+ /**

+ * Drop and remove the given local temporary index from this
session.

+ *

+ * @param index

+ * the index

+ */

+ public void removeLocalTempTableIndex(Index index) throws
SQLException {

+ localTempTablesIndexes.remove(index.getName());

+ index.removeChildrenAndResources(this);

+ }

+

+ @Override

protected void finalize() {

if (!SysProperties.runFinalize) {

return;

[Thomas Mueller]

Hi,

Thanks a lot for your patch! I merged it now, it looks very good! It
is committed to the trunk at

http://code.google.com/p/h2database/source/checkout

There were some problems: the changes in Table.java don't seem to be
necessary. Also some test cases didn't work so I had to do some more
changes. I have added a new test case in TestTempTables.java, if you
have more test cases that would be great of course.

Patches: Unfortunately the formatting is lost when you send the patch
by email. You should be able to upload it as a file to
http://groups.google.com/group/h2-database/files

Regards,
Thomas

[Matt]

Excellent! Thanks Thomas. I'll make sure to upload my patches as
files if I need to do so in the future.
Matt