From a8a990b0170b730b642f44d683376dd710b47110 Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Mon, 10 Apr 2017 17:48:26 +0200 Subject: [PATCH] SONAR-9086 make index on ORGANIZATIONS.KEE unique --- .../db/migration/version/v63/DbVersion63.java | 1 + .../MakeIndexOnOrganizationsKeeUnique.java | 57 +++++++++++++ .../version/v63/DbVersion63Test.java | 2 +- ...nOrganizationsKeeUniqueReentranceTest.java | 81 +++++++++++++++++++ ...MakeIndexOnOrganizationsKeeUniqueTest.java | 80 ++++++++++++++++++ .../organizations_with_unique_index.sql | 16 ++++ .../organizations_with_non_unique_index.sql | 16 ++++ .../org/sonar/db/version/rows-h2.sql | 1 + 8 files changed, 253 insertions(+), 1 deletion(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUnique.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueReentranceTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueReentranceTest/organizations_with_unique_index.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueTest/organizations_with_non_unique_index.sql 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 c2815a97bcf..0e4d36500cc 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 @@ -50,6 +50,7 @@ public class DbVersion63 implements DbVersion { .add(1515, "Unset user root flags", UnsetUserRootFlags.class) .add(1516, "Add ORGANIZATIONS.USER_ID", AddUserIdToOrganizations.class) .add(1517, "Delete PROJECT_MEASURES rows having no value", DeleteMeasuresHavingNoValue.class) + .add(1518, "Make index on ORGANIZATIONS.KEE unique", MakeIndexOnOrganizationsKeeUnique.class) ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUnique.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUnique.java new file mode 100644 index 00000000000..4dde2641f57 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUnique.java @@ -0,0 +1,57 @@ +/* + * 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.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.sql.DropIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class MakeIndexOnOrganizationsKeeUnique extends DdlChange { + + private static final String TABLE_ORGANIZATIONS = "organizations"; + private static final String INDEX_NAME = "organization_key"; + + public MakeIndexOnOrganizationsKeeUnique(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new DropIndexBuilder(getDialect()) + .setTable(TABLE_ORGANIZATIONS) + .setName(INDEX_NAME) + .build()); + + context.execute(new CreateIndexBuilder(getDialect()) + .setTable(TABLE_ORGANIZATIONS) + .setName(INDEX_NAME) + .addColumn(newVarcharColumnDefBuilder() + .setColumnName("kee") + .setLimit(32) + .setIsNullable(false) + .build()) + .setUnique(true) + .build()); + } +} 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 e9c26d7fefa..dca9beeebdb 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, 18); + verifyMigrationCount(underTest, 19); } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueReentranceTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueReentranceTest.java new file mode 100644 index 00000000000..0a00667ef04 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueReentranceTest.java @@ -0,0 +1,81 @@ +/* + * 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.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 MakeIndexOnOrganizationsKeeUniqueReentranceTest { + private static final String TABLE_ORGANIZATIONS = "organizations"; + private static final String INDEX_NAME = "organization_key"; + + @Rule + public DbTester dbTester = DbTester.createForSchema(System2.INSTANCE, MakeIndexOnOrganizationsKeeUniqueReentranceTest.class, "organizations_with_unique_index.sql"); + @Rule + public ExpectedException expectedException = ExpectedException.none(); + + private MakeIndexOnOrganizationsKeeUnique underTest = new MakeIndexOnOrganizationsKeeUnique(dbTester.database()); + + @Test + public void execute_makes_index_unique_on_empty_table() throws SQLException { + dbTester.assertIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + + underTest.execute(); + + dbTester.assertUniqueIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + } + + @Test + public void execute_makes_index_unique_on_non_empty_table_without_duplicates() throws SQLException { + dbTester.assertIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + insert("1", "kee_1"); + insert("2", "kee_2"); + + underTest.execute(); + + dbTester.assertUniqueIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + } + + @Test + public void execute_fails_non_empty_table_with_duplicates() throws SQLException { + dbTester.assertIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + insert("1", "kee_1"); + insert("2", "kee_1"); + + expectedException.expect(IllegalStateException.class); + + underTest.execute(); + } + + private void insert(String uuid, String kee) { + dbTester.executeInsert(TABLE_ORGANIZATIONS, + "UUID", uuid, + "KEE", kee, + "NAME", "name", + "GUARDED", "false", + "CREATED_AT", "1000", + "UPDATED_AT", "2000"); + } + +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueTest.java new file mode 100644 index 00000000000..f1a85b29471 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueTest.java @@ -0,0 +1,80 @@ +/* + * 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.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 MakeIndexOnOrganizationsKeeUniqueTest { + private static final String TABLE_ORGANIZATIONS = "organizations"; + private static final String INDEX_NAME = "organization_key"; + + @Rule + public DbTester dbTester = DbTester.createForSchema(System2.INSTANCE, MakeIndexOnOrganizationsKeeUniqueTest.class, "organizations_with_non_unique_index.sql"); + @Rule + public ExpectedException expectedException = ExpectedException.none(); + + private MakeIndexOnOrganizationsKeeUnique underTest = new MakeIndexOnOrganizationsKeeUnique(dbTester.database()); + + @Test + public void execute_makes_index_unique_on_empty_table() throws SQLException { + dbTester.assertIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + + underTest.execute(); + + dbTester.assertUniqueIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + } + + @Test + public void execute_makes_index_unique_on_non_empty_table_without_duplicates() throws SQLException { + dbTester.assertIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + insert("1", "kee_1"); + insert("2", "kee_2"); + + underTest.execute(); + + dbTester.assertUniqueIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + } + + @Test + public void execute_fails_non_empty_table_with_duplicates() throws SQLException { + dbTester.assertIndex(TABLE_ORGANIZATIONS, INDEX_NAME, "kee"); + insert("1", "kee_1"); + insert("2", "kee_1"); + + expectedException.expect(IllegalStateException.class); + + underTest.execute(); + } + + private void insert(String uuid, String kee) { + dbTester.executeInsert(TABLE_ORGANIZATIONS, + "UUID", uuid, + "KEE", kee, + "NAME", "name", + "GUARDED", "false", + "CREATED_AT", "1000", + "UPDATED_AT", "2000"); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueReentranceTest/organizations_with_unique_index.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueReentranceTest/organizations_with_unique_index.sql new file mode 100644 index 00000000000..12d83796405 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueReentranceTest/organizations_with_unique_index.sql @@ -0,0 +1,16 @@ +CREATE TABLE "ORGANIZATIONS" ( + "UUID" VARCHAR(40) NOT NULL PRIMARY KEY, + "KEE" VARCHAR(32) NOT NULL, + "NAME" VARCHAR(64) NOT NULL, + "DESCRIPTION" VARCHAR(256), + "URL" VARCHAR(256), + "AVATAR_URL" VARCHAR(256), + "GUARDED" BOOLEAN NOT NULL, + "USER_ID" INTEGER, + "DEFAULT_PERM_TEMPLATE_PROJECT" VARCHAR(40), + "DEFAULT_PERM_TEMPLATE_VIEW" VARCHAR(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +CREATE UNIQUE INDEX "PK_ORGANIZATIONS" ON "ORGANIZATIONS" ("UUID"); +CREATE INDEX "ORGANIZATION_KEY" ON "ORGANIZATIONS" ("KEE"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueTest/organizations_with_non_unique_index.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueTest/organizations_with_non_unique_index.sql new file mode 100644 index 00000000000..12d83796405 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v63/MakeIndexOnOrganizationsKeeUniqueTest/organizations_with_non_unique_index.sql @@ -0,0 +1,16 @@ +CREATE TABLE "ORGANIZATIONS" ( + "UUID" VARCHAR(40) NOT NULL PRIMARY KEY, + "KEE" VARCHAR(32) NOT NULL, + "NAME" VARCHAR(64) NOT NULL, + "DESCRIPTION" VARCHAR(256), + "URL" VARCHAR(256), + "AVATAR_URL" VARCHAR(256), + "GUARDED" BOOLEAN NOT NULL, + "USER_ID" INTEGER, + "DEFAULT_PERM_TEMPLATE_PROJECT" VARCHAR(40), + "DEFAULT_PERM_TEMPLATE_VIEW" VARCHAR(40), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +CREATE UNIQUE INDEX "PK_ORGANIZATIONS" ON "ORGANIZATIONS" ("UUID"); +CREATE INDEX "ORGANIZATION_KEY" ON "ORGANIZATIONS" ("KEE"); 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 5892eee83ae..5f903239020 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 @@ -531,6 +531,7 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1514'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1515'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1516'); INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1517'); +INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1518'); 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', false, '1418215735482', '1418215735482'); ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; -- 2.39.5