From db3a220350bc79d2cd3381750654de8658025e22 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Thu, 5 Jan 2017 11:09:32 +0100 Subject: [PATCH] SONAR-8474 add index on PROJECTS.ORGANIZATION_UUID --- .../AddIndexOnOrganizationUuidOfProjects.java | 47 ++++++++++++++++ .../db/migration/version/v63/DbVersion63.java | 2 +- ...IndexOnOrganizationUuidOfProjectsTest.java | 53 +++++++++++++++++++ .../version/v63/DbVersion63Test.java | 2 +- .../projects.sql | 42 +++++++++++++++ .../org/sonar/db/version/rows-h2.sql | 1 + .../org/sonar/db/version/schema-h2.ddl | 1 + 7 files changed, 146 insertions(+), 2 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjects.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjectsTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjectsTest/projects.sql diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjects.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjects.java new file mode 100644 index 00000000000..b4b4bf49649 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjects.java @@ -0,0 +1,47 @@ +/* + * SonarQube + * Copyright (C) 2009-2016 SonarSource SA + * mailto:contact 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.v63; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class AddIndexOnOrganizationUuidOfProjects extends DdlChange { + public AddIndexOnOrganizationUuidOfProjects(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new CreateIndexBuilder(getDialect()) + .setTable("projects") + .setName("projects_organization") + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("organization_uuid") + .setIsNullable(false) + .setLimit(UUID_SIZE) + .build()) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63.java index 5971b949c28..b0f0cfca80a 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63.java @@ -38,6 +38,6 @@ public class DbVersion63 implements DbVersion { .add(1503, "Add PROJECTS.ORGANIZATION_UUID", AddOrganizationUuidToProjects.class) .add(1504, "Populate PROJECTS.ORGANIZATION_UUID", PopulateOrganizationUuidToProjects.class) .add(1505, "Make PROJECTS.ORGANIZATION_UUID not nullable", MakeOrganizationUuidOfProjectsNotNullable.class) - ; + .add(1506, "Add index on PROJECTS.ORGANIZATION_UUID", AddIndexOnOrganizationUuidOfProjects.class); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjectsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjectsTest.java new file mode 100644 index 00000000000..b88d4f79cd7 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjectsTest.java @@ -0,0 +1,53 @@ +/* + * SonarQube + * Copyright (C) 2009-2016 SonarSource SA + * mailto:contact 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.v63; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.junit.rules.ExpectedException; +import org.sonar.api.utils.System2; +import org.sonar.db.DbTester; + +public class AddIndexOnOrganizationUuidOfProjectsTest { + + @Rule + public final DbTester dbTester = DbTester.createForSchema(System2.INSTANCE, AddIndexOnOrganizationUuidOfProjectsTest.class, "projects.sql"); + @Rule + public ExpectedException expectedException = ExpectedException.none(); + + private AddIndexOnOrganizationUuidOfProjects underTest = new AddIndexOnOrganizationUuidOfProjects(dbTester.database()); + + @Test + public void execute_adds_index_on_ORGANIZATION_UUID() throws SQLException { + underTest.execute(); + + dbTester.assertIndex("projects", "projects_organization", "organization_uuid"); + } + + @Test + public void execute_is_not_reentrant() throws SQLException { + underTest.execute(); + + expectedException.expect(IllegalStateException.class); + + underTest.execute(); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63Test.java index e901ff5f9e1..6f430efcf52 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/DbVersion63Test.java @@ -41,7 +41,7 @@ public class DbVersion63Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 6); + verifyMigrationCount(underTest, 7); } } diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjectsTest/projects.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjectsTest/projects.sql new file mode 100644 index 00000000000..2851fc8cf65 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/AddIndexOnOrganizationUuidOfProjectsTest/projects.sql @@ -0,0 +1,42 @@ +CREATE TABLE "PROJECTS" ( + "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), + "KEE" VARCHAR(400), + "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, + "UUID" VARCHAR(50) NOT NULL, + "UUID_PATH" VARCHAR(1500) NOT NULL, + "ROOT_UUID" VARCHAR(50) NOT NULL, + "PROJECT_UUID" VARCHAR(50), + "MODULE_UUID" VARCHAR(50), + "MODULE_UUID_PATH" VARCHAR(1500), + "NAME" VARCHAR(2000), + "DESCRIPTION" VARCHAR(2000), + "ENABLED" BOOLEAN NOT NULL DEFAULT TRUE, + "SCOPE" VARCHAR(3), + "QUALIFIER" VARCHAR(10), + "DEPRECATED_KEE" VARCHAR(400), + "PATH" VARCHAR(2000), + "LANGUAGE" VARCHAR(20), + "COPY_COMPONENT_UUID" VARCHAR(50), + "LONG_NAME" VARCHAR(2000), + "DEVELOPER_UUID" VARCHAR(50), + "CREATED_AT" TIMESTAMP, + "AUTHORIZATION_UPDATED_AT" BIGINT, + "B_CHANGED" BOOLEAN, + "B_COPY_COMPONENT_UUID" VARCHAR(50), + "B_DESCRIPTION" VARCHAR(2000), + "B_ENABLED" BOOLEAN, + "B_UUID_PATH" VARCHAR(1500), + "B_LANGUAGE" VARCHAR(20), + "B_LONG_NAME" VARCHAR(500), + "B_MODULE_UUID" VARCHAR(50), + "B_MODULE_UUID_PATH" VARCHAR(1500), + "B_NAME" VARCHAR(500), + "B_PATH" VARCHAR(2000), + "B_QUALIFIER" VARCHAR(10) +); +CREATE UNIQUE INDEX "PROJECTS_KEE" ON "PROJECTS" ("KEE"); +CREATE INDEX "PROJECTS_ROOT_UUID" ON "PROJECTS" ("ROOT_UUID"); +CREATE UNIQUE INDEX "PROJECTS_UUID" ON "PROJECTS" ("UUID"); +CREATE INDEX "PROJECTS_PROJECT_UUID" ON "PROJECTS" ("PROJECT_UUID"); +CREATE INDEX "PROJECTS_MODULE_UUID" ON "PROJECTS" ("MODULE_UUID"); +CREATE INDEX "PROJECTS_QUALIFIER" ON "PROJECTS" ("QUALIFIER"); diff --git a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql index 0efcfc5411e..7bfcdc34def 100644 --- a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql +++ b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql @@ -519,6 +519,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1502'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1503'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1504'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1505'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1506'); INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, EXTERNAL_IDENTITY, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, IS_ROOT, CREATED_AT, UPDATED_AT) VALUES (1, 'admin', 'Administrator', '', 'admin', 'sonarqube', true, 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', true, '1418215735482', '1418215735482'); ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; diff --git a/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl b/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl index 9c2788e4b62..d5e8379eec4 100644 --- a/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl +++ b/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl @@ -290,6 +290,7 @@ CREATE TABLE "PROJECTS" ( "B_PATH" VARCHAR(2000), "B_QUALIFIER" VARCHAR(10) ); +CREATE INDEX "PROJECTS_ORGANIZATION" ON "PROJECTS" ("ORGANIZATION_UUID"); CREATE UNIQUE INDEX "PROJECTS_KEE" ON "PROJECTS" ("KEE"); CREATE INDEX "PROJECTS_ROOT_UUID" ON "PROJECTS" ("ROOT_UUID"); CREATE UNIQUE INDEX "PROJECTS_UUID" ON "PROJECTS" ("UUID"); -- 2.39.5