[277660] [DB] Use column names in INSERT INTO statements

https://bugs.eclipse.org/bugs/show_bug.cgi?id=277660

8 files changed, 123 insertions, 69 deletions

diff --git a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/db/mapping/ITypeMapping.java b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/db/mapping/ITypeMapping.java
index 4be4bb6198..29fda89ea8 100644
--- a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/db/mapping/ITypeMapping.java
+++ b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/db/mapping/ITypeMapping.java
@@ -109,30 +109,30 @@ public interface ITypeMapping
   public void setValueFromRevision(PreparedStatement stmt, int index, InternalCDORevision value) throws SQLException;
 
   /**
-   * Read a value from a {@link ResultSet} and convert it from the DB to the CDO representation.
+   * Read the value from a {@link ResultSet} and convert it from the DB to the CDO representation. The resultSet field
+   * to read from is determined automatically by the internal {@link #getField()} name.
    * 
    * @param resultSet
    *          the result set to read from
-   * @param i
-   *          the column index in the result set to read from
    * @return the read value
    * @throws SQLException
    *           if reading the value throws an SQLException
+   * @since 3.0
    */
-  public Object readValue(ResultSet resultSet, int i) throws SQLException;
+  public Object readValue(ResultSet resultSet) throws SQLException;
 
   /**
    * Read a value from a {@link ResultSet}, convert it from the DB to the CDO representation and set it to the feature
-   * of the revision. The feature is determined by getFeature()
+   * of the revision. The feature is determined by getFeature() The resultSet field to read from is determined
+   * automatically by the internal {@link #getField()} name.
    * 
    * @param resultSet
    *          the result set to read from
-   * @param i
-   *          the column index in the result set to read from
    * @param revision
    *          the revision to which the value should be set.
    * @throws SQLException
    *           if reading the value throws an SQLException
+   * @since 3.0
    */
-  public void readValueToRevision(ResultSet resultSet, int i, InternalCDORevision revision) throws SQLException;
+  public void readValueToRevision(ResultSet resultSet, InternalCDORevision revision) throws SQLException;
 }
diff --git a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/TypeMapping.java b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/TypeMapping.java
index 41c1653160..dac547598b 100644
--- a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/TypeMapping.java
+++ b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/TypeMapping.java
@@ -125,16 +125,15 @@ public abstract class TypeMapping implements ITypeMapping
     return field;
   }
 
-  public final void readValueToRevision(ResultSet resultSet, int index, InternalCDORevision revision)
-      throws SQLException
+  public final void readValueToRevision(ResultSet resultSet, InternalCDORevision revision) throws SQLException
   {
-    Object value = readValue(resultSet, index);
+    Object value = readValue(resultSet);
     revision.setValue(getFeature(), value);
   }
 
-  public final Object readValue(ResultSet resultSet, int index) throws SQLException
+  public final Object readValue(ResultSet resultSet) throws SQLException
   {
-    Object value = getResultSetValue(resultSet, index);
+    Object value = getResultSetValue(resultSet);
     if (resultSet.wasNull())
     {
       value = null;
@@ -190,7 +189,7 @@ public abstract class TypeMapping implements ITypeMapping
     return type == DBType.VARCHAR ? 32672 : IDBField.DEFAULT;
   }
 
-  protected abstract Object getResultSetValue(ResultSet resultSet, int column) throws SQLException;
+  protected abstract Object getResultSetValue(ResultSet resultSet) throws SQLException;
 
   /**
    * @author Eike Stepper
@@ -203,10 +202,10 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
       // see Bug 271941
-      return resultSet.getInt(column);
+      return resultSet.getInt(getField().getName());
       // EEnum type = (EEnum)getFeature().getEType();
       // int value = resultSet.getInt(column);
       // return type.getEEnumLiteral(value);
@@ -230,9 +229,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getString(column);
+      return resultSet.getString(getField().getName());
     }
   }
 
@@ -247,15 +246,14 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getShort(column);
+      return resultSet.getShort(getField().getName());
     }
   }
 
   /**
    * @author Eike Stepper <br>
-   *         TODO add mapping/unmapping calls for external references here
    */
   public static class TMObject extends TypeMapping
   {
@@ -265,9 +263,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      long id = resultSet.getLong(column);
+      long id = resultSet.getLong(getField().getName());
       if (resultSet.wasNull())
       {
         return null;
@@ -307,9 +305,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getLong(column);
+      return resultSet.getLong(getField().getName());
     }
   }
 
@@ -324,9 +322,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getInt(column);
+      return resultSet.getInt(getField().getName());
     }
   }
 
@@ -341,9 +339,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getFloat(column);
+      return resultSet.getFloat(getField().getName());
     }
   }
 
@@ -358,9 +356,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getDouble(column);
+      return resultSet.getDouble(getField().getName());
     }
   }
 
@@ -375,9 +373,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getTimestamp(column);
+      return resultSet.getTimestamp(getField().getName());
     }
 
     @Override
@@ -398,9 +396,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      String str = resultSet.getString(column);
+      String str = resultSet.getString(getField().getName());
       if (resultSet.wasNull())
       {
         return null;
@@ -427,9 +425,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getByte(column);
+      return resultSet.getByte(getField().getName());
     }
   }
 
@@ -444,9 +442,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getBytes(column);
+      return resultSet.getBytes(getField().getName());
     }
   }
 
@@ -461,9 +459,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    public Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    public Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      return resultSet.getBoolean(column);
+      return resultSet.getBoolean(getField().getName());
     }
   }
 
@@ -478,9 +476,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    protected Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    protected Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      String val = resultSet.getString(column);
+      String val = resultSet.getString(getField().getName());
 
       if (resultSet.wasNull())
       {
@@ -508,9 +506,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    protected Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    protected Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      String val = resultSet.getString(column);
+      String val = resultSet.getString(getField().getName());
 
       if (resultSet.wasNull())
       {
@@ -551,9 +549,9 @@ public abstract class TypeMapping implements ITypeMapping
     }
 
     @Override
-    protected Object getResultSetValue(ResultSet resultSet, int column) throws SQLException
+    protected Object getResultSetValue(ResultSet resultSet) throws SQLException
     {
-      String val = resultSet.getString(column);
+      String val = resultSet.getString(getField().getName());
       if (resultSet.wasNull())
       {
         return null;
diff --git a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractFeatureMapTableMapping.java b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractFeatureMapTableMapping.java
index a89ccad671..14169804e0 100644
--- a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractFeatureMapTableMapping.java
+++ b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractFeatureMapTableMapping.java
@@ -360,9 +360,7 @@ public abstract class AbstractFeatureMapTableMapping implements IListMapping
       while ((listChunk == CDORevision.UNCHUNKED || --listChunk >= 0) && resultSet.next())
       {
         Long tag = resultSet.getLong(1);
-        String column = getColumnName(tag);
-        int fieldIndex = resultSet.findColumn(column);
-        Object value = getTypeMapping(tag).readValue(resultSet, fieldIndex);
+        Object value = getTypeMapping(tag).readValue(resultSet);
 
         if (TRACER.isEnabled())
         {
@@ -508,9 +506,7 @@ public abstract class AbstractFeatureMapTableMapping implements IListMapping
       while (resultSet.next())
       {
         Long tag = resultSet.getLong(1);
-        String column = getColumnName(tag);
-        int fieldIndex = resultSet.findColumn(column);
-        Object value = getTypeMapping(tag).readValue(resultSet, fieldIndex);
+        Object value = getTypeMapping(tag).readValue(resultSet);
 
         if (chunk == null)
         {
diff --git a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractHorizontalClassMapping.java b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractHorizontalClassMapping.java
index 4a511e1f96..accd732704 100644
--- a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractHorizontalClassMapping.java
+++ b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractHorizontalClassMapping.java
@@ -179,19 +179,18 @@ public abstract class AbstractHorizontalClassMapping implements IClassMapping
         return false;
       }
 
-      int i = 1;
-      revision.setVersion(resultSet.getInt(i++));
-      revision.setCreated(resultSet.getLong(i++));
-      revision.setRevised(resultSet.getLong(i++));
+      revision.setVersion(resultSet.getInt(CDODBSchema.ATTRIBUTES_VERSION));
+      revision.setCreated(resultSet.getLong(CDODBSchema.ATTRIBUTES_CREATED));
+      revision.setRevised(resultSet.getLong(CDODBSchema.ATTRIBUTES_REVISED));
       revision.setResourceID(CDODBUtil.convertLongToCDOID(getExternalReferenceManager(), accessor, resultSet
-          .getLong(i++)));
+          .getLong(CDODBSchema.ATTRIBUTES_RESOURCE)));
       revision.setContainerID(CDODBUtil.convertLongToCDOID(getExternalReferenceManager(), accessor, resultSet
-          .getLong(i++)));
-      revision.setContainingFeatureID(resultSet.getInt(i++));
+          .getLong(CDODBSchema.ATTRIBUTES_CONTAINER)));
+      revision.setContainingFeatureID(resultSet.getInt(CDODBSchema.ATTRIBUTES_FEATURE));
 
       for (ITypeMapping mapping : valueMappings)
       {
-        mapping.readValueToRevision(resultSet, i++, revision);
+        mapping.readValueToRevision(resultSet, revision);
       }
 
       return true;
diff --git a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractListTableMapping.java b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractListTableMapping.java
index a90c8405f6..7d800b840d 100644
--- a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractListTableMapping.java
+++ b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/AbstractListTableMapping.java
@@ -191,7 +191,18 @@ public abstract class AbstractListTableMapping implements IListMapping
     // ----------------- INSERT - reference entry -----------------
     builder = new StringBuilder("INSERT INTO "); //$NON-NLS-1$
     builder.append(tableName);
-    builder.append(" VALUES ("); //$NON-NLS-1$
+    builder.append("("); //$NON-NLS-1$
+
+    for (int i = 0; i < fields.length; i++)
+    {
+      builder.append(fields[i].getName());
+      builder.append(", "); //$NON-NLS-1$
+    }
+
+    builder.append(CDODBSchema.LIST_IDX);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.LIST_VALUE);
+    builder.append(") VALUES ("); //$NON-NLS-1$
     for (int i = 0; i < fields.length; i++)
     {
       builder.append("?, "); //$NON-NLS-1$
@@ -272,7 +283,7 @@ public abstract class AbstractListTableMapping implements IListMapping
 
       while ((listChunk == CDORevision.UNCHUNKED || --listChunk >= 0) && resultSet.next())
       {
-        Object value = typeMapping.readValue(resultSet, 1);
+        Object value = typeMapping.readValue(resultSet);
         if (TRACER.isEnabled())
         {
           TRACER.format("Read value for index {0} from result set: {1}", list.size(), value); //$NON-NLS-1$
@@ -406,7 +417,7 @@ public abstract class AbstractListTableMapping implements IListMapping
 
       while (resultSet.next())
       {
-        Object value = typeMapping.readValue(resultSet, 1);
+        Object value = typeMapping.readValue(resultSet);
         resultCounter++;
 
         if (chunk == null)
diff --git a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/HorizontalAuditClassMapping.java b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/HorizontalAuditClassMapping.java
index b60f8bbb0d..ac645fe2ea 100644
--- a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/HorizontalAuditClassMapping.java
+++ b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/HorizontalAuditClassMapping.java
@@ -125,8 +125,32 @@ public class HorizontalAuditClassMapping extends AbstractHorizontalClassMapping
     builder = new StringBuilder();
     builder.append("INSERT INTO "); //$NON-NLS-1$
     builder.append(getTable().getName());
-    builder.append(" VALUES (?, ?, "); //$NON-NLS-1$
-    builder.append("?, ?, ?, ?, ?, ?"); //$NON-NLS-1$
+
+    builder.append("("); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_ID);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_VERSION);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_CLASS);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_CREATED);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_REVISED);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_RESOURCE);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_CONTAINER);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_FEATURE);
+
+    for (ITypeMapping singleMapping : getValueMappings())
+    {
+      builder.append(", "); //$NON-NLS-1$
+      builder.append(singleMapping.getField().getName());
+    }
+
+    builder.append(") VALUES (?, ?, ?, ?, ?, ?, ?, ?"); //$NON-NLS-1$
+
     for (int i = 0; i < getValueMappings().size(); i++)
     {
       builder.append(", ?"); //$NON-NLS-1$
diff --git a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/HorizontalNonAuditClassMapping.java b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/HorizontalNonAuditClassMapping.java
index 77b44ce6f9..9488926599 100644
--- a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/HorizontalNonAuditClassMapping.java
+++ b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/HorizontalNonAuditClassMapping.java
@@ -128,7 +128,31 @@ public class HorizontalNonAuditClassMapping extends AbstractHorizontalClassMappi
     builder = new StringBuilder();
     builder.append("INSERT INTO "); //$NON-NLS-1$
     builder.append(getTable().getName());
-    builder.append(" VALUES (?, ?, "); //$NON-NLS-1$
+
+    builder.append("("); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_ID);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_VERSION);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_CLASS);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_CREATED);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_REVISED);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_RESOURCE);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_CONTAINER);
+    builder.append(", "); //$NON-NLS-1$
+    builder.append(CDODBSchema.ATTRIBUTES_FEATURE);
+
+    for (ITypeMapping singleMapping : getValueMappings())
+    {
+      builder.append(", "); //$NON-NLS-1$
+      builder.append(singleMapping.getField().getName());
+    }
+
+    builder.append(") VALUES (?, ?, "); //$NON-NLS-1$
     builder.append("?, ?, ?, ?, ?, ?"); //$NON-NLS-1$
     for (int i = 0; i < getValueMappings().size(); i++)
     {
diff --git a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/ObjectTypeCache.java b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/ObjectTypeCache.java
index bf6379cf45..1ecf21fd4e 100644
--- a/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/ObjectTypeCache.java
+++ b/plugins/org.eclipse.emf.cdo.server.db/src/org/eclipse/emf/cdo/server/internal/db/mapping/horizontal/ObjectTypeCache.java
@@ -123,7 +123,7 @@ public class ObjectTypeCache extends Lifecycle implements IObjectTypeCache
 
       if (result != 1)
       {
-        throw new DBException("Object type could not be deleted: " + id); //$NON-NLS-1$
+        throw new DBException("Object type could not be inserted: " + id); //$NON-NLS-1$
       }
     }
     catch (SQLException ex)
@@ -208,7 +208,9 @@ public class ObjectTypeCache extends Lifecycle implements IObjectTypeCache
     }
 
     sqlSelect = "SELECT " + typeField.getName() + " FROM " + table.getName() + " WHERE " + idField.getName() + " = ?"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$
-    sqlInsert = "INSERT INTO " + table.getName() + " VALUES (?,?)"; //$NON-NLS-1$ //$NON-NLS-2$
+    sqlInsert = "INSERT INTO " + table.getName() + "(" //$NON-NLS-1$ //$NON-NLS-2$
+        + idField.getName() + "," + typeField.getName() //$NON-NLS-1$  
+        + ") VALUES (?,?)"; //$NON-NLS-1$ 
     sqlDelete = "DELETE FROM " + table.getName() + " WHERE " + idField.getName() + " = ?"; //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
   }