Changeset 7912

Timestamp:

Feb 18, 2021, 8:02:06 AM (2 years ago)

Author:

Nicklas Nordborg

Message:

References #2237: Implement extension mechanism for query filtering

Implemented functionality for creating a temporary table with ID values that should improve speed when executing queries with IN or NOT IN having a large list of values. The IdListRestriction has been updated to use this functionality when the list if bigger than 1000 items.

Location:

trunk/src/core/net/sf/basedb

Files:

• core/DbControl.java (modified) (4 diffs)
• core/dbengine/DbEngine.java (modified) (1 diff)
• core/dbengine/PostgresDbEngine.java (modified) (1 diff)
• core/query/IdListRestriction.java (modified) (2 diffs)
• util/FileUtil.java (modified) (1 diff)

trunk/src/core/net/sf/basedb/core/DbControl.java

@@ -32 +32 @@
 import net.sf.basedb.core.data.GroupData;
 import net.sf.basedb.core.data.UserData;
+import net.sf.basedb.core.dbengine.DbEngine;
 import net.sf.basedb.core.hibernate.SessionWrapper;
 import net.sf.basedb.core.hibernate.StatelessSessionWrapper;
@@ -39 +40 @@
 import net.sf.basedb.core.log.ManualLogEntry;
 import net.sf.basedb.util.ClassUtil;
+import net.sf.basedb.util.FileUtil;
 import net.sf.basedb.util.extensions.ClientContext;
 import net.sf.basedb.util.extensions.ExtensionsInvoker;
@@ -59 +61 @@
 import java.lang.reflect.Constructor;
 import java.lang.reflect.InvocationTargetException;
+import java.sql.PreparedStatement;
+import java.sql.SQLException;
 
 /**
@@ -1273 +1277 @@
   
   /**
+    Create a temporary table for holding given list of ID values.
+    The intention is that this should be used in a query to speed 
+    up the execution. Note that not all databases may support this
+    feature and that alternate solutions are needed. The temporary 
+    table is automatically deleted when the transaction ends.
+    
+    @param idList List of ID values that should be inserted
+    @return The name of the temporary table or null if not supported
+    @since 3.18
+  */
+  public String createTempIdTable(Set<Integer> idList)
+  {
+    String tableName = "Tmp"+uniqueRandom();
+    DbEngine dbEngine = HibernateUtil.getDbEngine();
+    String createSql = dbEngine.getCreateTemporaryIdTable(tableName);
+    if (createSql == null) return null;
+    
+    PreparedStatement insert = null;
+    try
+    {
+      org.hibernate.Session session = getHibernateSession();
+      HibernateUtil.createSqlQuery(session, createSql).executeUpdate();
+      
+      String insertSql = "INSERT INTO " + dbEngine.getQuotedName(tableName) +
+        " ("+dbEngine.getQuotedName("id")+") VALUES (?)";
+      insert = HibernateUtil.getConnection(session).prepareStatement(insertSql);
+      
+      for (Integer id : idList)
+      {
+        insert.setInt(1, id);
+        insert.addBatch();
+      }
+      insert.executeBatch();
+    }
+    catch (SQLException ex)
+    {
+      throw new DatabaseException(ex);
+    }
+    finally
+    {
+      FileUtil.close(insert);
+    }
+    return tableName;
+  }
+  
+  /**
     Main purpose is to write a warning to the log file in case some user code that
     created a DbControl instance never called the close() method before the DbControl

trunk/src/core/net/sf/basedb/core/dbengine/DbEngine.java

@@ -303 +303 @@
   */
   public String makeSafeCreateTable(String sql, String catalog, String schema, String table);
+  
+  /**
+    Generate SQL for creating a temporary table with a single 'id' column that is
+    the primary key. The table need to be automatically deleted
+    when the transaction ends. If the database doesn't support this
+    null should be returned. 
+    Note! This method is experiment and may change in a future API.
+    A default implementation that return null is provided.
+    
+    @param table The name of the temporary table
+    @return SQL statement or null
+    @since 3.18
+  */
+  public default String getCreateTemporaryIdTable(String table)
+  {
+    return null;
+  }
+  
   
   /**

trunk/src/core/net/sf/basedb/core/dbengine/PostgresDbEngine.java

@@ -255 +255 @@
     return "ANALYZE " + schema + getQuotedName(table);
   }
+  
+  /**
+    CREATE TEMPORARY TABLE <table> (id integer PRIMARY KEY) ON COMMIT DROP
+    @since 3.18
+  */
+  @Override
+  public String getCreateTemporaryIdTable(String table)
+  {
+    return "CREATE TEMPORARY TABLE " + getQuotedName(table) +
+        " (" + getQuotedName("id") + " integer PRIMARY KEY) ON COMMIT DROP";
+  }
+
+  
   /**
     Returns FALSE.

trunk/src/core/net/sf/basedb/core/query/IdListRestriction.java

@@ -46 +46 @@
   private Set<Integer> idList;
   private final Expression idExpression;
+  private String tmpTable;
   
   public IdListRestriction()
@@ -78 +79 @@
   public String toQl(Query query, DbControl dc) 
   {
-    String idString = idList.size() == 0 ? "0" : Values.getString(idList, ", ", true);
-    return "(" + idExpression.toQl(query, dc) + (notIn ? " NOT IN " : " IN ") +"(" + idString + "))";
+    if (tmpTable == null && idList.size() > 1000)
+    {
+      tmpTable = dc.createTempIdTable(idList);
+    }
+    String hql = idExpression.toQl(query, dc) + (notIn ? " NOT IN (" : " IN (");
+    if (tmpTable != null)
+    {
+      hql += "native('select [id] from [" + tmpTable + "]')";
+    }
+    else
+    {
+      hql += idList.size() == 0 ? "0" : Values.getString(idList, ", ", true);
+    }
+    return "("+ hql + "))";
   }
 

trunk/src/core/net/sf/basedb/util/FileUtil.java

@@ -243 +243 @@
   }
 
+  /**
+    Close a {@link AutoCloseable} without throwing an exception.
+    @param c The object to close
+    @since 3.18
+  */
+  public static void close(AutoCloseable c)
+  {
+    if (c == null) return;
+    try
+    {
+      c.close();
+    }
+    catch (Throwable t)
+    {}
+  }
   
   /**