commit 2ff07c8befe399d2333932028aff9b711b949775
author Hector Oswaldo Caballero <hector.caballero@ericsson.com> Mon Jul 23 14:20:59 2018 +0000
committer Gerrit Code Review <noreply-gerritcodereview@google.com> Mon Jul 23 14:20:59 2018 +0000
tree 874347d3272421d0495357c13b9255a3ecbb10b7
parent 19bb9f90cdaaa741baf9b4f644036ddc99c969c4
parent 3bcb1492aa32cc5034d71f8c473f4a3ff9fa7e3b

Merge "Add indexes to speed up queries" into stable-2.14

src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLClient.java

diff --git a/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLClient.java b/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLClient.java
index 760bb65..2db990a 100644
--- a/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLClient.java
+++ b/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLClient.java
@@ -111,6 +111,7 @@
   void createDBIfNotCreated() throws SQLException {
     boolean postgresql = ds.getDriverClassName().contains("postgresql");
     execute(SQLTable.createTableQuery(postgresql));
+    execute(SQLTable.createIndexes(postgresql));
   }
 
   /**

src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLTable.java

diff --git a/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLTable.java b/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLTable.java
index f25b618..9210529 100644
--- a/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLTable.java
+++ b/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLTable.java
@@ -23,10 +23,27 @@
   static final String DATE_ENTRY = "date_created";
   static final String EVENT_ENTRY = "event_info";
 
+  private static final String CREATED_INDEX = "created_idx";
+  private static final String PROJECT_INDEX = "project_idx";
+  private static final String H2_INDEX_CREATION_FORMAT = "CREATE INDEX IF NOT EXISTS %s ON %s (%s)";
+  private static final String POSTGRESQL_INDEX_CREATION_FORMAT =
+      "DO $$\n"
+          + "BEGIN\n"
+          + "IF NOT EXISTS (\n"
+          + "    SELECT 1\n"
+          + "    FROM   pg_class c\n"
+          + "    JOIN   pg_namespace n ON n.oid = c.relnamespace\n"
+          + "    WHERE  c.relname = '%s'\n"
+          + "    AND    n.nspname = 'public'\n"
+          + "    ) THEN\n"
+          + "    CREATE INDEX %s ON %s (%s);\n"
+          + "END IF;\n"
+          + "END$$;";
+
   private SQLTable() {}
 
   static String createTableQuery(boolean postgresql) {
-    StringBuilder query = new StringBuilder();
+    StringBuilder query = new StringBuilder(140);
     query.append(format("CREATE TABLE IF NOT EXISTS %s(", TABLE_NAME));
     if (postgresql) {
       query.append(format("%s SERIAL PRIMARY KEY,", PRIMARY_ENTRY));
@@ -38,4 +55,32 @@
     query.append(format("%s TEXT)", EVENT_ENTRY));
     return query.toString();
   }
+
+  static String createIndexes(boolean postgresql) {
+    return postgresql ? getPostgresqlQuery() : getH2Query();
+  }
+
+  private static String getPostgresqlQuery() {
+    StringBuilder query = new StringBuilder(540);
+    query.append(
+        format(
+            POSTGRESQL_INDEX_CREATION_FORMAT, TABLE_NAME, CREATED_INDEX, TABLE_NAME, DATE_ENTRY));
+    query.append("\n;\n");
+    query.append(
+        format(
+            POSTGRESQL_INDEX_CREATION_FORMAT,
+            TABLE_NAME,
+            PROJECT_INDEX,
+            TABLE_NAME,
+            PROJECT_ENTRY));
+    return query.toString();
+  }
+
+  private static String getH2Query() {
+    StringBuilder query = new StringBuilder();
+    query.append(format(H2_INDEX_CREATION_FORMAT, CREATED_INDEX, TABLE_NAME, DATE_ENTRY));
+    query.append(";");
+    query.append(format(H2_INDEX_CREATION_FORMAT, PROJECT_INDEX, TABLE_NAME, PROJECT_ENTRY));
+    return query.toString();
+  }
 }