SONAR-9302 built-in profiles must always be present

The profiles that have been deleted in the past by
administrators should be re-created.

17 files changed, 162 insertions, 251 deletions

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/loadedtemplate/LoadedTemplateDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/loadedtemplate/LoadedTemplateDto.java
index 0fecf4d728e..b8646499c6c 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/loadedtemplate/LoadedTemplateDto.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/loadedtemplate/LoadedTemplateDto.java
@@ -23,13 +23,8 @@ import java.util.Objects;
 
 public final class LoadedTemplateDto {
 
-  public static final String DASHBOARD_TYPE = "DASHBOARD";
-  public static final String FILTER_TYPE = "FILTER";
-  public static final String QUALITY_PROFILE_TYPE = "QUALITY_PROFILE";
-  public static final String PERMISSION_TEMPLATE_TYPE = "PERM_TEMPLATE";
   public static final String QUALITY_GATE_TYPE = "QUALITY_GATE";
   public static final String ONE_SHOT_TASK_TYPE = "ONE_SHOT_TASK";
-  public static final String ISSUE_FILTER_TYPE = "ISSUE_FILTER";
 
   private Long id;
   private String key;
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/organization/OrganizationDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/organization/OrganizationDao.java
index 1a43599dd1a..20dcba038c1 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/organization/OrganizationDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/organization/OrganizationDao.java
@@ -75,8 +75,8 @@ public class OrganizationDao implements Dao {
     return getMapper(dbSession).selectByPermission(userId, permission);
   }
 
-  public List<OrganizationDto> selectOrganizationsWithoutLoadedTemplate(DbSession dbSession, String loadedTemplateType, Pagination pagination) {
-    return getMapper(dbSession).selectOrganizationsWithoutLoadedTemplate(loadedTemplateType, pagination);
+  public List<OrganizationDto> selectWithoutQualityProfile(DbSession dbSession, String profileLanguage, String profileName) {
+    return getMapper(dbSession).selectWithoutQualityProfile(profileLanguage, profileName);
   }
 
   /**
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/organization/OrganizationMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/organization/OrganizationMapper.java
index 5f891da130a..4617fec29fb 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/organization/OrganizationMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/organization/OrganizationMapper.java
@@ -42,12 +42,8 @@ public interface OrganizationMapper {
 
   List<OrganizationDto> selectByPermission(@Param("userId") Integer userId, @Param("permission") String permission);
 
-  /**
-   * Assuming the key of the loaded template with the specified type is an organization's UUID, select all organizations
-   * which does not have a row in table LOADED_TEMPLATES with the specified type.
-   */
-  List<OrganizationDto> selectOrganizationsWithoutLoadedTemplate(@Param("loadedTemplateType") String type,
-    @Param("pagination") Pagination pagination);
+  List<OrganizationDto> selectWithoutQualityProfile(@Param("profileLanguage") String profileLanguage,
+    @Param("profileName") String profileName);
 
   DefaultTemplates selectDefaultTemplatesByUuid(@Param("uuid") String uuid);
 
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/organization/OrganizationMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/organization/OrganizationMapper.xml
index 45127429f1a..feacf5db7a3 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/organization/OrganizationMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/organization/OrganizationMapper.xml
@@ -129,57 +129,17 @@
       and gu.user_id = #{userId,jdbcType=INTEGER}
   </select>
 
-  <select id="selectOrganizationsWithoutLoadedTemplate" parameterType="map" resultType="Organization">
+  <select id="selectWithoutQualityProfile" parameterType="map" resultType="Organization">
     select
-      <include refid="selectColumns"/>
-    <include refid="sqlSelectOrganizationsWithoutLoadedTemplate"/>
-    order by
-      org.created_at asc
-    limit #{pagination.pageSize,jdbcType=INTEGER} offset #{pagination.offset,jdbcType=INTEGER}
-  </select>
-
-  <select id="selectOrganizationsWithoutLoadedTemplate" parameterType="map" resultType="Organization" databaseId="mssql">
-    select * from (
-      select
-        row_number() over(order by org.created_at asc) as number,
-        <include refid="selectColumns"/>
-      <include refid="sqlSelectOrganizationsWithoutLoadedTemplate"/>
-    ) as query
-    where
-      query.number between #{pagination.startRowNumber,jdbcType=INTEGER} and #{pagination.endRowNumber,jdbcType=INTEGER}
-    order by
-      query.createdAt asc
-  </select>
-
-  <select id="selectOrganizationsWithoutLoadedTemplate" parameterType="map" resultType="Organization" databaseId="oracle">
-    select * from (
-      select rownum as rn, t.* from (
-        select
-          <include refid="selectColumns"/>
-        <include refid="sqlSelectOrganizationsWithoutLoadedTemplate"/>
-        order by
-          org.created_at asc
-      ) t
-    ) t
-    where
-      t.rn between #{pagination.startRowNumber,jdbcType=INTEGER} and #{pagination.endRowNumber,jdbcType=INTEGER}
+    <include refid="selectColumns"/>
+    from organizations org
+    where not exists (
+      select 1 from rules_profiles p
+      where p.organization_uuid = org.uuid
+      and p.language = #{profileLanguage, jdbcType=VARCHAR}
+      and p.name = #{profileName, jdbcType=VARCHAR}
+    )
   </select>
-
-  <sql id="sqlSelectOrganizationsWithoutLoadedTemplate">
-    from
-      organizations org
-    where
-      not exists (
-        select
-          1
-        from
-          loaded_templates lt
-        where
-          lt.template_type=#{loadedTemplateType,jdbcType=VARCHAR}
-          and lt.kee=org.uuid
-      )
-  </sql>
-
   <select id="selectDefaultGroupIdByUuid" resultType="Integer">
     select org.default_group_id
     from organizations org
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/organization/OrganizationDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/organization/OrganizationDaoTest.java
index 9912aede16c..31c968c6846 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/organization/OrganizationDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/organization/OrganizationDaoTest.java
@@ -29,7 +29,6 @@ import java.util.Locale;
 import java.util.Map;
 import java.util.Optional;
 import java.util.Random;
-import java.util.stream.IntStream;
 import javax.annotation.Nullable;
 import org.apache.ibatis.exceptions.PersistenceException;
 import org.assertj.core.util.Lists;
@@ -37,13 +36,12 @@ import org.junit.Rule;
 import org.junit.Test;
 import org.junit.rules.ExpectedException;
 import org.sonar.api.utils.System2;
-import org.sonar.api.utils.internal.AlwaysIncreasingSystem2;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
 import org.sonar.db.dialect.Dialect;
 import org.sonar.db.dialect.Oracle;
-import org.sonar.db.loadedtemplate.LoadedTemplateDto;
+import org.sonar.db.qualityprofile.QualityProfileDto;
 import org.sonar.db.user.GroupDto;
 import org.sonar.db.user.GroupTesting;
 import org.sonar.db.user.UserDto;
@@ -54,7 +52,6 @@ import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.core.api.Assertions.tuple;
 import static org.mockito.Mockito.mock;
 import static org.mockito.Mockito.when;
-import static org.sonar.db.Pagination.all;
 import static org.sonar.db.Pagination.forPage;
 import static org.sonar.db.organization.OrganizationQuery.newOrganizationQueryBuilder;
 import static org.sonar.db.organization.OrganizationQuery.returnAll;
@@ -877,85 +874,16 @@ public class OrganizationDaoTest {
   }
 
   @Test
-  public void selectOrganizationsWithoutLoadedTemplate_returns_empty_if_there_is_no_organization() {
-    List<OrganizationDto> organizationDtos = underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "type1", all());
+  public void selectWithoutQualityProfile_returns_() {
+    OrganizationDto orgWithoutAnyProfiles = dbTester.organizations().insert();
+    OrganizationDto orgWithProfiles = dbTester.organizations().insert();
+    QualityProfileDto profile = dbTester.qualityProfiles().insert(orgWithProfiles);
 
-    assertThat(organizationDtos).isEmpty();
-  }
-
-  @Test
-  public void selectOrganizationsWithoutLoadedTemplate_returns_all_organizations_if_loaded_template_table_is_empty() {
-    int organizationCount = Math.abs(new Random().nextInt(20)) + 1;
-    String[] organizationUuids = IntStream.range(0, organizationCount).mapToObj(i -> "uuid_" + i).toArray(String[]::new);
-    Arrays.stream(organizationUuids).forEach(uuid -> dbTester.organizations().insertForUuid(uuid));
-
-    List<OrganizationDto> organizationDtos = underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "type1", all());
-
-    assertThat(organizationDtos)
-      .extracting(OrganizationDto::getUuid)
-      .containsOnly(organizationUuids);
-  }
+    assertThat(underTest.selectWithoutQualityProfile(dbSession, "js", "foo"))
+      .extracting(OrganizationDto::getUuid).containsExactlyInAnyOrder(orgWithoutAnyProfiles.getUuid(), orgWithProfiles.getUuid());
 
-  @Test
-  public void selectOrganizationsWithoutLoadedTemplate_returns_all_organizations_but_those_with_loaded_template_with_specified_type_and_org_uuid_as_key() {
-    int organizationCount = Math.abs(new Random().nextInt(20)) + 5;
-    String[] organizationUuids = IntStream.range(0, organizationCount).mapToObj(i -> "uuid_" + i).toArray(String[]::new);
-    Arrays.stream(organizationUuids).forEach(uuid -> dbTester.organizations().insertForUuid(uuid));
-    String loadedTemplateType = "type1";
-    dbTester.getDbClient().loadedTemplateDao().insert(new LoadedTemplateDto(organizationUuids[0], loadedTemplateType), dbSession);
-    dbTester.getDbClient().loadedTemplateDao().insert(new LoadedTemplateDto(organizationUuids[1], "foo"), dbSession);
-    // matching is case sensitive
-    dbTester.getDbClient().loadedTemplateDao().insert(new LoadedTemplateDto(organizationUuids[2], loadedTemplateType.toUpperCase()), dbSession);
-    dbTester.getDbClient().loadedTemplateDao().insert(new LoadedTemplateDto(organizationUuids[3], loadedTemplateType), dbSession);
-    dbTester.getDbClient().loadedTemplateDao().insert(new LoadedTemplateDto(organizationUuids[4] + " not exactly the uuid", loadedTemplateType), dbSession);
-    dbTester.getDbClient().loadedTemplateDao().insert(new LoadedTemplateDto("foo", loadedTemplateType), dbSession);
-    dbTester.getDbClient().loadedTemplateDao().insert(new LoadedTemplateDto("", loadedTemplateType), dbSession);
-    dbTester.commit();
-
-    List<OrganizationDto> organizationDtos = underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, loadedTemplateType, all());
-
-    assertThat(organizationDtos)
-      .extracting(OrganizationDto::getUuid)
-      .containsOnly(
-        Arrays.stream(organizationUuids)
-          .filter(s -> !s.equals(organizationUuids[0]) && !s.equals(organizationUuids[3]))
-          .toArray(String[]::new));
-  }
-
-  @Test
-  public void selectOrganizationsWithoutLoadedTemplate_is_paginated() {
-    AlwaysIncreasingSystem2 alwaysIncreasingSystem2 = new AlwaysIncreasingSystem2(500);
-    when(system2.now()).thenAnswer(t -> alwaysIncreasingSystem2.now());
-    IntStream.range(1, 31).forEach(i -> dbTester.organizations().insertForUuid("" + i));
-
-    assertThat(underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "foo", all()))
-      .extracting(dto -> Integer.valueOf(dto.getUuid()))
-      .hasSize(30)
-      .allMatch(i -> i > 0 && i <= 30);
-    assertThat(underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "foo", forPage(1).andSize(30)))
-      .extracting(dto -> Integer.valueOf(dto.getUuid()))
-      .hasSize(30)
-      .allMatch(i -> i > 0 && i <= 30);
-    assertThat(underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "foo", forPage(1).andSize(10)))
-      .extracting(dto -> Integer.valueOf(dto.getUuid()))
-      .hasSize(10)
-      .allMatch(i -> i > 0 && i <= 10);
-    assertThat(underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "foo", forPage(2).andSize(10)))
-      .extracting(dto -> Integer.valueOf(dto.getUuid()))
-      .hasSize(10)
-      .allMatch(i -> i > 10 && i <= 20);
-    assertThat(underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "foo", forPage(5).andSize(5)))
-      .extracting(dto -> Integer.valueOf(dto.getUuid()))
-      .hasSize(5)
-      .allMatch(i -> i > 20 && i <= 25);
-    assertThat(underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "foo", forPage(6).andSize(5)))
-      .extracting(dto -> Integer.valueOf(dto.getUuid()))
-      .hasSize(5)
-      .allMatch(i -> i > 25 && i <= 30);
-    assertThat(underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "foo", forPage(7).andSize(5)))
-      .isEmpty();
-    assertThat(underTest.selectOrganizationsWithoutLoadedTemplate(dbSession, "foo", forPage(2).andSize(50)))
-      .isEmpty();
+    assertThat(underTest.selectWithoutQualityProfile(dbSession, profile.getLanguage(), profile.getName()))
+      .extracting(OrganizationDto::getUuid).containsExactlyInAnyOrder(orgWithoutAnyProfiles.getUuid());
   }
 
   private void expectDtoCanNotBeNull() {
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java
index 1b6ddd78046..52bd522c806 100644
--- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65.java
@@ -17,7 +17,6 @@
  * 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.v65;
 
 import org.sonar.server.platform.db.migration.step.MigrationStepRegistry;
@@ -44,6 +43,7 @@ public class DbVersion65 implements DbVersion {
       .add(1714, "Purge developer data", PurgeDeveloperData.class)
       .add(1715, "Add rules_profiles.is_built_in", AddBuiltInFlagToRulesProfiles.class)
       .add(1716, "Set rules_profiles.is_built_in to false", SetRulesProfilesIsBuiltInToFalse.class)
-      .add(1717, "Make rules_profiles.is_built_in not null", MakeRulesProfilesIsBuiltInNotNullable.class);
+      .add(1717, "Make rules_profiles.is_built_in not null", MakeRulesProfilesIsBuiltInNotNullable.class)
+      .add(1718, "Delete unused loaded_templates on quality profiles", DeleteLoadedTemplatesOnQProfiles.class);
   }
 }
diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DeleteLoadedTemplatesOnQProfiles.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DeleteLoadedTemplatesOnQProfiles.java
new file mode 100644
index 00000000000..0e7067aa0db
--- /dev/null
+++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v65/DeleteLoadedTemplatesOnQProfiles.java
@@ -0,0 +1,45 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 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.v65;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.server.platform.db.migration.step.DataChange;
+import org.sonar.server.platform.db.migration.step.MassUpdate;
+
+public class DeleteLoadedTemplatesOnQProfiles extends DataChange {
+
+  public DeleteLoadedTemplatesOnQProfiles(Database db) {
+    super(db);
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    MassUpdate massUpdate = context.prepareMassUpdate();
+    massUpdate.select("select id from loaded_templates where template_type like 'QUALITY_PROFILE.%'");
+    massUpdate.rowPluralName("loaded_templates");
+    massUpdate.update("delete from loaded_templates where id=?");
+    massUpdate.execute((row, update) -> {
+      update.setLong(1, row.getLong(1));
+      return true;
+    });
+  }
+
+}
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java
index a8dad8dd5b5..8cd51244d00 100644
--- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DbVersion65Test.java
@@ -35,6 +35,6 @@ public class DbVersion65Test {
 
   @Test
   public void verify_migration_count() {
-    verifyMigrationCount(underTest, 18);
+    verifyMigrationCount(underTest, 19);
   }
 }
diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DeleteLoadedTemplatesOnQProfilesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DeleteLoadedTemplatesOnQProfilesTest.java
new file mode 100644
index 00000000000..79cb3d39d95
--- /dev/null
+++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v65/DeleteLoadedTemplatesOnQProfilesTest.java
@@ -0,0 +1,73 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 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.v65;
+
+import java.sql.SQLException;
+import java.util.List;
+import org.junit.Rule;
+import org.junit.Test;
+import org.junit.rules.ExpectedException;
+import org.sonar.core.util.stream.MoreCollectors;
+import org.sonar.db.CoreDbTester;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class DeleteLoadedTemplatesOnQProfilesTest {
+
+  @Rule
+  public ExpectedException expectedException = ExpectedException.none();
+
+  @Rule
+  public CoreDbTester db = CoreDbTester.createForSchema(DeleteLoadedTemplatesOnQProfilesTest.class, "initial.sql");
+
+  private DeleteLoadedTemplatesOnQProfiles underTest = new DeleteLoadedTemplatesOnQProfiles(db.database());
+
+  @Test
+  public void does_nothing_if_table_is_empty() throws SQLException {
+    underTest.execute();
+
+    assertThat(db.countRowsOfTable("loaded_templates")).isEqualTo(0);
+  }
+
+  @Test
+  public void deletes_rows_with_qprofile_type() throws SQLException {
+    insertRow("ORG_UUID_1", "QUALITY_PROFILE.HASH_OF_ORG_UUID_1");
+    insertRow("ORG_UUID_2", "QUALITY_PROFILE.HASH_OF_ORG_UUID_2");
+    insertRow("foo", "QUALITY_GATE");
+
+    underTest.execute();
+
+    assertThat(selectAllKeys()).containsExactly("foo");
+  }
+
+  private void insertRow(String key, String type) {
+    db.executeInsert(
+      "LOADED_TEMPLATES",
+      "KEE", key,
+      "TEMPLATE_TYPE", type);
+  }
+
+  private List<String> selectAllKeys() {
+    return db.select("select kee as TEMPLATE_KEY from loaded_templates")
+      .stream()
+      .map(e -> (String)e.get("TEMPLATE_KEY"))
+      .collect(MoreCollectors.toList());
+  }
+}
diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/DeleteLoadedTemplatesOnQProfilesTest/initial.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/DeleteLoadedTemplatesOnQProfilesTest/initial.sql
new file mode 100644
index 00000000000..d3c72d43b4d
--- /dev/null
+++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v65/DeleteLoadedTemplatesOnQProfilesTest/initial.sql
@@ -0,0 +1,6 @@
+CREATE TABLE "LOADED_TEMPLATES" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "KEE" VARCHAR(200),
+  "TEMPLATE_TYPE" VARCHAR(64) NOT NULL
+);
+CREATE INDEX "IX_LOADED_TEMPLATES_TYPE" ON "LOADED_TEMPLATES" ("TEMPLATE_TYPE");
diff --git a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfile.java b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfile.java
index f5397c1bf4b..291856361f2 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfile.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfile.java
@@ -20,18 +20,11 @@
 package org.sonar.server.qualityprofile;
 
 import com.google.common.collect.ImmutableList;
-import java.security.MessageDigest;
 import java.util.ArrayList;
 import java.util.List;
 import javax.annotation.concurrent.Immutable;
 import org.sonar.api.profiles.ProfileDefinition;
 
-import static java.lang.String.format;
-import static java.nio.charset.StandardCharsets.UTF_8;
-import static org.apache.commons.codec.binary.Hex.encodeHexString;
-import static org.apache.commons.lang.StringUtils.lowerCase;
-import static org.sonar.db.loadedtemplate.LoadedTemplateDto.QUALITY_PROFILE_TYPE;
-
 /**
  * Represent a Quality Profile as computed from {@link ProfileDefinition} provided by installed plugins.
  */
@@ -39,21 +32,14 @@ import static org.sonar.db.loadedtemplate.LoadedTemplateDto.QUALITY_PROFILE_TYPE
 public final class BuiltInQProfile {
   private final QProfileName qProfileName;
   private final boolean isDefault;
-  private final String loadedTemplateType;
   private final List<org.sonar.api.rules.ActiveRule> activeRules;
 
-  private BuiltInQProfile(Builder builder, MessageDigest messageDigest) {
+  private BuiltInQProfile(Builder builder) {
     this.qProfileName = new QProfileName(builder.language, builder.getName());
     this.isDefault = builder.declaredDefault || builder.computedDefault;
-    this.loadedTemplateType = computeLoadedTemplateType(this.qProfileName, messageDigest);
     this.activeRules = ImmutableList.copyOf(builder.activeRules);
   }
 
-  private static String computeLoadedTemplateType(QProfileName qProfileName, MessageDigest messageDigest) {
-    String qpIdentifier = lowerCase(qProfileName.getLanguage()) + ":" + qProfileName.getName();
-    return format("%s.%s", QUALITY_PROFILE_TYPE, encodeHexString(messageDigest.digest(qpIdentifier.getBytes(UTF_8))));
-  }
-
   public String getName() {
     return qProfileName.getName();
   }
@@ -70,10 +56,6 @@ public final class BuiltInQProfile {
     return isDefault;
   }
 
-  public String getLoadedTemplateType() {
-    return loadedTemplateType;
-  }
-
   public List<org.sonar.api.rules.ActiveRule> getActiveRules() {
     return activeRules;
   }
@@ -118,8 +100,8 @@ public final class BuiltInQProfile {
       return this;
     }
 
-    BuiltInQProfile build(MessageDigest messageDigest) {
-      return new BuiltInQProfile(this, messageDigest);
+    BuiltInQProfile build() {
+      return new BuiltInQProfile(this);
     }
   }
 }
diff --git a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfileInsertImpl.java b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfileInsertImpl.java
index 948914437f1..bbe30e9593e 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfileInsertImpl.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfileInsertImpl.java
@@ -42,7 +42,6 @@ import org.sonar.core.util.UuidFactory;
 import org.sonar.core.util.stream.MoreCollectors;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
-import org.sonar.db.loadedtemplate.LoadedTemplateDto;
 import org.sonar.db.organization.OrganizationDto;
 import org.sonar.db.qualityprofile.ActiveRuleDto;
 import org.sonar.db.qualityprofile.ActiveRuleKey;
@@ -83,8 +82,6 @@ public class BuiltInQProfileInsertImpl implements BuiltInQProfileInsert {
       .collect(MoreCollectors.toList());
 
     localChanges.forEach(change -> dbClient.qProfileChangeDao().insert(batchSession, change.toDto(null)));
-
-    insertTemplate(session, builtInQProfile, organization);
   }
 
   private void initRuleRepository(DbSession session) {
@@ -161,11 +158,6 @@ public class BuiltInQProfileInsertImpl implements BuiltInQProfileInsert {
     return value;
   }
 
-  private void insertTemplate(DbSession session, BuiltInQProfile qualityProfile, OrganizationDto organization) {
-    LoadedTemplateDto template = new LoadedTemplateDto(organization.getUuid(), qualityProfile.getLoadedTemplateType());
-    dbClient.loadedTemplateDao().insert(template, session);
-  }
-
   public static class RuleRepository {
     private final Map<RuleKey, RuleDefinitionDto> ruleDefinitions;
     private final Map<RuleKey, Set<RuleParamDto>> ruleParams;
diff --git a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfileRepositoryImpl.java b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfileRepositoryImpl.java
index d80f12d2f9b..35e11e070e9 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfileRepositoryImpl.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/BuiltInQProfileRepositoryImpl.java
@@ -23,7 +23,6 @@ import com.google.common.collect.ArrayListMultimap;
 import com.google.common.collect.ImmutableList;
 import com.google.common.collect.ListMultimap;
 import com.google.common.collect.Multimaps;
-import java.security.MessageDigest;
 import java.util.Collection;
 import java.util.LinkedHashMap;
 import java.util.List;
@@ -32,7 +31,6 @@ import java.util.Map;
 import java.util.Optional;
 import java.util.Set;
 import javax.annotation.Nullable;
-import org.apache.commons.codec.digest.DigestUtils;
 import org.sonar.api.profiles.ProfileDefinition;
 import org.sonar.api.profiles.RulesProfile;
 import org.sonar.api.resources.Languages;
@@ -200,9 +198,8 @@ public class BuiltInQProfileRepositoryImpl implements BuiltInQProfileRepository
         builders.iterator().next().setComputedDefault(true);
       }
     }
-    MessageDigest md5Digest = DigestUtils.getMd5Digest();
     return builders.stream()
-      .map(builder -> builder.build(md5Digest))
+      .map(BuiltInQProfile.Builder::build)
       .collect(MoreCollectors.toList(builders.size()));
   }
 }
diff --git a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/RegisterQualityProfiles.java b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/RegisterQualityProfiles.java
index c70466bfd67..544dd0385be 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/RegisterQualityProfiles.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/RegisterQualityProfiles.java
@@ -27,12 +27,10 @@ import org.sonar.api.utils.log.Loggers;
 import org.sonar.api.utils.log.Profiler;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
-import org.sonar.db.Pagination;
 import org.sonar.db.organization.OrganizationDto;
 import org.sonar.server.qualityprofile.index.ActiveRuleIndexer;
 
 import static java.lang.String.format;
-import static org.sonar.db.Pagination.forPage;
 
 /**
  * Synchronize Quality profiles during server startup
@@ -41,7 +39,6 @@ import static org.sonar.db.Pagination.forPage;
 public class RegisterQualityProfiles {
 
   private static final Logger LOGGER = Loggers.get(RegisterQualityProfiles.class);
-  private static final Pagination PROCESSED_ORGANIZATIONS_BATCH_SIZE = forPage(1).andSize(2000);
 
   private final BuiltInQProfileRepository builtInQProfileRepository;
   private final DbClient dbClient;
@@ -75,23 +72,17 @@ public class RegisterQualityProfiles {
     qualityProfiles.forEach(qp -> registerPerQualityProfile(session, batchSession, qp));
   }
 
-  private void registerPerQualityProfile(DbSession dbSession, DbSession batchSession, BuiltInQProfile qualityProfile) {
-    LOGGER.info("Register profile {}", qualityProfile.getQProfileName());
+  private void registerPerQualityProfile(DbSession dbSession, DbSession batchSession, BuiltInQProfile builtInProfile) {
+    LOGGER.info("Register profile {}", builtInProfile.getQProfileName());
 
     Profiler profiler = Profiler.create(Loggers.get(getClass()));
-    renameOutdatedProfiles(dbSession, qualityProfile);
-    List<OrganizationDto> organizationDtos;
-    while (!(organizationDtos = getOrganizationsWithoutQP(dbSession, qualityProfile)).isEmpty()) {
-      organizationDtos.forEach(organization -> registerPerQualityProfileAndOrganization(dbSession, batchSession, qualityProfile, organization, profiler));
-    }
-  }
+    renameOutdatedProfiles(dbSession, builtInProfile);
 
-  private List<OrganizationDto> getOrganizationsWithoutQP(DbSession session, BuiltInQProfile qualityProfile) {
-    return dbClient.organizationDao().selectOrganizationsWithoutLoadedTemplate(session,
-      qualityProfile.getLoadedTemplateType(), PROCESSED_ORGANIZATIONS_BATCH_SIZE);
+    dbClient.organizationDao().selectWithoutQualityProfile(dbSession, builtInProfile.getLanguage(), builtInProfile.getName()).forEach(
+      organization -> registerProfileOnOrganization(dbSession, batchSession, builtInProfile, organization, profiler));
   }
 
-  private void registerPerQualityProfileAndOrganization(DbSession session, DbSession batchSession,
+  private void registerProfileOnOrganization(DbSession session, DbSession batchSession,
     BuiltInQProfile builtInQProfile, OrganizationDto organization, Profiler profiler) {
     profiler.start();
 
diff --git a/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/BuiltInQProfileRepositoryRule.java b/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/BuiltInQProfileRepositoryRule.java
index 6bb2427bf92..5418ec34658 100644
--- a/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/BuiltInQProfileRepositoryRule.java
+++ b/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/BuiltInQProfileRepositoryRule.java
@@ -26,7 +26,6 @@ import java.util.HashMap;
 import java.util.List;
 import java.util.Map;
 import java.util.stream.Stream;
-import org.apache.commons.codec.digest.DigestUtils;
 import org.junit.rules.ExternalResource;
 import org.sonar.api.resources.Language;
 import org.sonar.core.util.stream.MoreCollectors;
@@ -89,7 +88,7 @@ public class BuiltInQProfileRepositoryRule extends ExternalResource implements B
       .setLanguage(language.getKey())
       .setName(profileName)
       .setDeclaredDefault(isDefault)
-        .addRules(Arrays.asList(rules))
-      .build(DigestUtils.getMd5Digest());
+      .addRules(Arrays.asList(rules))
+      .build();
   }
 }
diff --git a/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesMediumTest.java b/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesMediumTest.java
index 55c107ed8e4..ad452ca172f 100644
--- a/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesMediumTest.java
+++ b/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesMediumTest.java
@@ -20,7 +20,6 @@
 package org.sonar.server.qualityprofile;
 
 import java.util.Map;
-import org.apache.commons.codec.digest.DigestUtils;
 import org.junit.After;
 import org.junit.Test;
 import org.sonar.api.profiles.ProfileDefinition;
@@ -49,12 +48,8 @@ import org.sonar.server.rule.index.RuleQuery;
 import org.sonar.server.tester.ServerTester;
 
 import static com.google.common.collect.Lists.newArrayList;
-import static java.lang.String.format;
-import static java.nio.charset.StandardCharsets.UTF_8;
-import static org.apache.commons.lang.StringUtils.lowerCase;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.guava.api.Assertions.assertThat;
-import static org.sonar.db.loadedtemplate.LoadedTemplateDto.QUALITY_PROFILE_TYPE;
 import static org.sonar.server.qualityprofile.QProfileTesting.getDefaultOrganization;
 
 // TODO replace this MediumTest by DbTester and EsTester
@@ -230,28 +225,6 @@ public class RegisterQualityProfilesMediumTest {
     verifyDefaultProfile(organization, "xoo", "two");
   }
 
-  /**
-   * Probably for db migration
-   */
-  @Test
-  public void clean_up_profiles_if_missing_loaded_template() {
-    tester = new ServerTester().withEsIndexes().addXoo().addComponents(XooRulesDefinition.class, XooProfileDefinition.class);
-    tester.start();
-    DbClient dbClient = dbClient();
-    dbSession = dbClient().openSession(false);
-    OrganizationDto organization = getDefaultOrganization(tester, dbClient, dbSession);
-
-    String loadedTemplateType = computeLoadedTemplateType(new QProfileName("xoo", "Basic"));
-    dbClient().loadedTemplateDao().delete(dbSession, loadedTemplateType, organization.getUuid());
-    dbSession.commit();
-    assertThat(dbClient().loadedTemplateDao().countByTypeAndKey(loadedTemplateType, organization.getUuid(), dbSession)).isEqualTo(0);
-    dbSession.close();
-
-    tester.get(Platform.class).restart();
-
-    // do not fail
-  }
-
   private void verifyDefaultProfile(OrganizationDto organization, String language, String name) {
     dbSession = dbClient().openSession(false);
     QualityProfileDto defaultProfile = dbClient().qualityProfileDao().selectDefaultProfile(dbSession, organization, language);
@@ -318,9 +291,4 @@ public class RegisterQualityProfilesMediumTest {
       return profile;
     }
   }
-
-  private String computeLoadedTemplateType(QProfileName qProfileName) {
-    String qpIdentifier = lowerCase(qProfileName.getLanguage()) + ":" + qProfileName.getName();
-    return format("%s.%s", QUALITY_PROFILE_TYPE, DigestUtils.md5Hex(qpIdentifier.getBytes(UTF_8)));
-  }
 }
diff --git a/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesTest.java b/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesTest.java
index 0cf2adf102e..625922048e2 100644
--- a/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesTest.java
+++ b/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesTest.java
@@ -32,7 +32,6 @@ import org.sonar.api.utils.log.LoggerLevel;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
-import org.sonar.db.loadedtemplate.LoadedTemplateDto;
 import org.sonar.db.organization.OrganizationDto;
 import org.sonar.db.qualityprofile.QualityProfileDto;
 import org.sonar.server.language.LanguageTesting;
@@ -75,7 +74,7 @@ public class RegisterQualityProfilesTest {
   }
 
   @Test
-  public void start_creates_qps_for_every_organization_in_DB_when_LoadedTemplate_table_is_empty() {
+  public void create_built_in_profile_on_organizations_that_dont_have_it() {
     OrganizationDto organization1 = dbTester.organizations().insert();
     OrganizationDto organization2 = dbTester.organizations().insert();
     BuiltInQProfile builtInQProfile = builtInQProfileRepositoryRule.add(FOO_LANGUAGE, "foo1");
@@ -91,22 +90,6 @@ public class RegisterQualityProfilesTest {
   }
 
   @Test
-  public void start_creates_qps_only_for_organizations_in_DB_without_loaded_template() {
-    OrganizationDto org1 = dbTester.organizations().insert();
-    OrganizationDto org2 = dbTester.organizations().insert();
-    BuiltInQProfile builtInQProfile = builtInQProfileRepositoryRule.add(FOO_LANGUAGE, "foo1");
-    dbClient.loadedTemplateDao().insert(new LoadedTemplateDto(dbTester.getDefaultOrganization().getUuid(), builtInQProfile.getLoadedTemplateType()), dbTester.getSession());
-    dbClient.loadedTemplateDao().insert(new LoadedTemplateDto(org1.getUuid(), builtInQProfile.getLoadedTemplateType()), dbTester.getSession());
-    dbTester.commit();
-    builtInQProfileRepositoryRule.initialize();
-
-    underTest.start();
-
-    assertThat(builtInQProfileCreation.getCallLogs())
-      .containsExactly(callLog(builtInQProfile, org2));
-  }
-
-  @Test
   public void start_creates_different_qps_and_their_loaded_templates_if_several_profile_has_same_name_for_different_languages() {
     String name = "doh";
 
@@ -143,10 +126,6 @@ public class RegisterQualityProfilesTest {
     @Override
     public void create(DbSession session, DbSession batchSession, BuiltInQProfile qualityProfile, OrganizationDto organization) {
       callLogs.add(callLog(qualityProfile, organization));
-
-      // RegisterQualityProfiles relies on the fact that BuiltInQProfileCreation populates table LOADED_TEMPLATE each time create is called
-      // to not loop infinitely
-      dbClient.loadedTemplateDao().insert(new LoadedTemplateDto(organization.getUuid(), qualityProfile.getLoadedTemplateType()), session);
     }
 
     List<CallLog> getCallLogs() {