SONAR-11304 add date and version of initial installation to telemetry (#1751)

SONAR-11304 add date and version of initial installation to telemetry

6 files changed, 280 insertions, 2 deletions

diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/DbVersion79.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/DbVersion79.java
index 37242753450..ba6201566de 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/DbVersion79.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/DbVersion79.java
@@ -27,6 +27,7 @@ public class DbVersion79 implements DbVersion {
   public void addSteps(MigrationStepRegistry registry) {
     registry
       .add(2800, "Truncate environment variables and system properties from existing scanner reports",
-        TruncateEnvAndSystemVarsFromScannerContext.class);
+        TruncateEnvAndSystemVarsFromScannerContext.class)
+      .add(2801, "populate install version and install date internal properties", PopulateInstallDateAndVersion.class);
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/PopulateInstallDateAndVersion.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/PopulateInstallDateAndVersion.java
new file mode 100644
index 00000000000..c967150ec49
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/PopulateInstallDateAndVersion.java
@@ -0,0 +1,88 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2019 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v79;
+
+import java.sql.SQLException;
+import org.sonar.api.SonarRuntime;
+import org.sonar.api.utils.System2;
+import org.sonar.api.utils.log.Logger;
+import org.sonar.api.utils.log.Loggers;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DataChange;
+
+public class PopulateInstallDateAndVersion extends DataChange {
+
+  private static final Logger LOG = Loggers.get(PopulateInstallDateAndVersion.class);
+  private static final String INSTALLATION_DATE = "installation.date";
+  private static final String INSTALLATION_VERSION = "installation.version";
+  private final SonarRuntime sonarRuntime;
+  private final System2 system2;
+
+  public PopulateInstallDateAndVersion(Database db, SonarRuntime sonarRuntime, System2 system2) {
+    super(db);
+    this.sonarRuntime = sonarRuntime;
+    this.system2 = system2;
+  }
+
+  @Override
+  protected void execute(Context context) throws SQLException {
+    removeProperties(context);
+    Long createdAt = context.prepareSelect("select min(created_at) from users where created_at is not null")
+      .get(row -> row.getLong(1));
+    if (createdAt != null && createdAt != 0) {
+      populateInstallationDate(context, createdAt);
+      populateInstallationVersion(context, createdAt);
+    }
+  }
+
+  private void populateInstallationDate(Context context, Long createdAt) throws SQLException {
+    insertInternalProperty(context, INSTALLATION_DATE, String.valueOf(createdAt));
+  }
+
+  private void populateInstallationVersion(Context context, Long createdAt) throws SQLException {
+    if (Math.abs(system2.now() - createdAt) / 60 / 60 / 1000 <= 24) {
+      String apiVersion = sonarRuntime.getApiVersion().toString();
+      insertInternalProperty(context, INSTALLATION_VERSION, apiVersion);
+    } else {
+      // if the difference between now and smallest account creation date is more than a day, we consider that this is a
+      // start with an existing SQ, and not a fresh start. in this case, we do not populate the internalProperty,
+      // as there is no way to know the original SQ installation version.
+      LOG.warn("skipping " + INSTALLATION_VERSION + " because we cannot determine what is the installation version.");
+    }
+  }
+
+  private void insertInternalProperty(Context context, String key, String value) throws SQLException {
+    context.prepareUpsert("insert into internal_properties (kee, is_empty, text_value, clob_value, created_at) VALUES (?, ?, ?, ?, ?)")
+      .setString(1, key)
+      .setBoolean(2, false)
+      .setString(3, value)
+      .setString(4, null)
+      .setLong(5, system2.now())
+      .execute().commit().close();
+  }
+
+  private static void removeProperties(Context context) throws SQLException {
+    context.prepareUpsert("delete from internal_properties where kee = ? or kee = ?")
+      .setString(1, INSTALLATION_DATE)
+      .setString(2, INSTALLATION_VERSION)
+      .execute().commit().close();
+
+  }
+}
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/package-info.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/package-info.java
new file mode 100644
index 00000000000..1bcbde154dd
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/package-info.java
@@ -0,0 +1,24 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2019 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+@ParametersAreNonnullByDefault
+package org.sonar.server.platform.db.migration.version.v79;
+
+import javax.annotation.ParametersAreNonnullByDefault;
+
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/DbVersion79Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/DbVersion79Test.java
index dc06cf84f8a..ad2680616cc 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/DbVersion79Test.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/DbVersion79Test.java
@@ -35,7 +35,7 @@ public class DbVersion79Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 1);
+    verifyMigrationCount(underTest, 2);
   }
 
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/PopulateInstallDateAndVersionTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/PopulateInstallDateAndVersionTest.java
new file mode 100644
index 00000000000..5944bb29f01
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/PopulateInstallDateAndVersionTest.java
@@ -0,0 +1,126 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2019 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.server.platform.db.migration.version.v79;
+
+import java.sql.SQLException;
+import java.util.HashMap;
+import java.util.Map;
+import java.util.function.Function;
+import org.apache.commons.lang.math.RandomUtils;
+import org.junit.Before;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.api.SonarRuntime;
+import org.sonar.api.utils.System2;
+import org.sonar.api.utils.Version;
+import org.sonar.db.CoreDbTester;
+import org.sonar.server.platform.db.migration.step.DataChange;
+
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.core.groups.Tuple.tuple;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+
+public class PopulateInstallDateAndVersionTest {
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(PopulateInstallDateAndVersionTest.class, "schema.sql");
+
+  private System2 system2 = mock(System2.class);
+  private SonarRuntime sonarRuntime = mock(SonarRuntime.class);
+
+  private DataChange underTest = new PopulateInstallDateAndVersion(db.database(), sonarRuntime, system2);
+
+  private Function<Map<String, Object>, Object> field(String name) {
+    return m -> m.get(name);
+  }
+
+  @Before
+  public void before() {
+    Version version = Version.create(7, 9, 0);
+    when(sonarRuntime.getApiVersion()).thenReturn(version);
+    when(system2.now()).thenReturn(RandomUtils.nextLong());
+    truncateUsers();
+    truncateInternalProperties();
+  }
+
+  @Test
+  public void migrateFreshInstall() throws SQLException {
+    Long createdAt = system2.now() - (23 * 60 * 60 * 1000);
+    insertAdminUser(createdAt);
+
+    underTest.execute();
+
+    assertThat(db.select("select * from internal_properties")).extracting(
+      field("CREATED_AT"), field("CLOB_VALUE"),
+      field("KEE"), field("TEXT_VALUE"), field("IS_EMPTY"))
+      .containsExactlyInAnyOrder(
+        tuple(system2.now(), null, "installation.date", String.valueOf(createdAt), false),
+        tuple(system2.now(), null, "installation.version", "7.9", false));
+  }
+
+  @Test
+  public void migrateOldInstance() throws SQLException {
+    Long createdAt = system2.now() - (25 * 60 * 60 * 1000);
+    insertAdminUser(createdAt);
+
+    underTest.execute();
+
+    assertThat(db.select("select * from internal_properties")).extracting(
+      field("CREATED_AT"), field("CLOB_VALUE"),
+      field("KEE"), field("TEXT_VALUE"), field("IS_EMPTY"))
+      .containsExactlyInAnyOrder(
+        tuple(system2.now(), null, "installation.date", String.valueOf(createdAt), false));
+  }
+
+  @Test
+  public void migrateNoUsers() throws SQLException {
+    underTest.execute();
+
+    assertThat(db.select("select * from internal_properties").stream().count()).isEqualTo(0);
+  }
+
+  private void insertAdminUser(long createdAt) {
+    Map<String, Object> values = new HashMap<>();
+    values.put("UUID", "UUID");
+    values.put("login", "admin");
+    values.put("name", "Administrator");
+    values.put("email", null);
+    values.put("EXTERNAL_ID", "admin");
+    values.put("EXTERNAL_LOGIN", "admin");
+    values.put("external_identity_provider", "sonarqube");
+    values.put("user_local", true);
+    values.put("crypted_password", "a373a0e667abb2604c1fd571eb4ad47fe8cc0878");
+    values.put("salt", "48bc4b0d93179b5103fd3885ea9119498e9d161b");
+    values.put("created_at", createdAt);
+    values.put("updated_at", createdAt);
+    values.put("IS_ROOT", true);
+    values.put("ONBOARDED", false);
+    db.executeInsert("users", values);
+  }
+
+  private void truncateUsers() {
+    db.executeUpdateSql("truncate table users");
+  }
+
+  private void truncateInternalProperties() {
+    db.executeUpdateSql("truncate table internal_properties");
+  }
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v79/PopulateInstallDateAndVersionTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v79/PopulateInstallDateAndVersionTest/schema.sql
new file mode 100644
index 00000000000..67390214cd8
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v79/PopulateInstallDateAndVersionTest/schema.sql
@@ -0,0 +1,39 @@
+CREATE TABLE "INTERNAL_PROPERTIES" (
+  "KEE" VARCHAR(20) NOT NULL,
+  "IS_EMPTY" BOOLEAN NOT NULL,
+  "TEXT_VALUE" VARCHAR(4000),
+  "CLOB_VALUE" CLOB,
+  "CREATED_AT" BIGINT,
+
+  CONSTRAINT "PK_INTERNAL_PROPERTIES" PRIMARY KEY ("KEE")
+);
+
+CREATE TABLE "USERS" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "UUID" VARCHAR(255) NOT NULL,
+  "LOGIN" VARCHAR(255) NOT NULL,
+  "NAME" VARCHAR(200),
+  "EMAIL" VARCHAR(100),
+  "CRYPTED_PASSWORD" VARCHAR(100),
+  "SALT" VARCHAR(40),
+  "HASH_METHOD" VARCHAR(10),
+  "ACTIVE" BOOLEAN DEFAULT TRUE,
+  "SCM_ACCOUNTS" VARCHAR(4000),
+  "EXTERNAL_ID" VARCHAR(255) NOT NULL,
+  "EXTERNAL_LOGIN" VARCHAR(255) NOT NULL,
+  "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100) NOT NULL,
+  "IS_ROOT" BOOLEAN NOT NULL,
+  "USER_LOCAL" BOOLEAN,
+  "ONBOARDED" BOOLEAN NOT NULL,
+  "HOMEPAGE_TYPE" VARCHAR(40),
+  "HOMEPAGE_PARAMETER" VARCHAR(40),
+  "ORGANIZATION_UUID" VARCHAR(40),
+  "LAST_CONNECTION_DATE" BIGINT,
+  "CREATED_AT" BIGINT,
+  "UPDATED_AT" BIGINT
+);
+CREATE UNIQUE INDEX "USERS_UUID" ON "USERS" ("UUID");
+CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS" ("LOGIN");
+CREATE UNIQUE INDEX "UNIQ_EXTERNAL_ID" ON "USERS" ("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_ID");
+CREATE UNIQUE INDEX "UNIQ_EXTERNAL_LOGIN" ON "USERS" ("EXTERNAL_IDENTITY_PROVIDER", "EXTERNAL_LOGIN");
+CREATE INDEX "USERS_UPDATED_AT" ON "USERS" ("UPDATED_AT");