From 0a87b29e830ae08e65abb90a947431f3fd3cceeb Mon Sep 17 00:00:00 2001 From: Duarte Meneses Date: Tue, 7 Sep 2021 11:39:58 -0500 Subject: [PATCH] SONAR-15259 Add DB indexes --- server/sonar-db-dao/src/schema/schema-sq.ddl | 3 ++ .../v91/CreateIndexForPortfolioProjects.java | 51 ++++++++++++++++++ .../CreateIndexForPortfolioReferences.java | 52 ++++++++++++++++++ .../v91/CreateIndexOnKeeForPortfolios.java | 50 +++++++++++++++++ .../db/migration/version/v91/DbVersion91.java | 9 ++-- .../CreateIndexForPortfolioProjectsTest.java | 53 +++++++++++++++++++ ...CreateIndexForPortfolioReferencesTest.java | 53 +++++++++++++++++++ .../CreateIndexOnKeeForPortfoliosTest.java | 53 +++++++++++++++++++ .../version/v91/DbVersion91Test.java | 2 +- .../schema.sql | 30 +++++++++++ .../schema.sql | 30 +++++++++++ .../schema.sql | 14 +++++ 12 files changed, 396 insertions(+), 4 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjects.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferences.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfolios.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjectsTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferencesTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfoliosTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjectsTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferencesTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfoliosTest/schema.sql diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index 2913cd77daf..a2b0728d00e 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -569,6 +569,7 @@ CREATE TABLE "PORTFOLIO_PROJECTS"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "PORTFOLIO_PROJECTS" ADD CONSTRAINT "PK_PORTFOLIO_PROJECTS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PORTFOLIO_PROJECTS" ON "PORTFOLIO_PROJECTS"("PORTFOLIO_UUID", "PROJECT_UUID"); CREATE TABLE "PORTFOLIO_REFERENCES"( "UUID" VARCHAR(40) NOT NULL, @@ -577,6 +578,7 @@ CREATE TABLE "PORTFOLIO_REFERENCES"( "CREATED_AT" BIGINT NOT NULL ); ALTER TABLE "PORTFOLIO_REFERENCES" ADD CONSTRAINT "PK_PORTFOLIO_REFERENCES" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PORTFOLIO_REFERENCES" ON "PORTFOLIO_REFERENCES"("PORTFOLIO_UUID", "REFERENCE_UUID"); CREATE TABLE "PORTFOLIOS"( "UUID" VARCHAR(40) NOT NULL, @@ -592,6 +594,7 @@ CREATE TABLE "PORTFOLIOS"( "UPDATED_AT" BIGINT NOT NULL ); ALTER TABLE "PORTFOLIOS" ADD CONSTRAINT "PK_PORTFOLIOS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "UNIQ_PORTFOLIOS_KEE" ON "PORTFOLIOS"("KEE"); CREATE TABLE "PROJECT_ALM_SETTINGS"( "UUID" VARCHAR(40) NOT NULL, diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjects.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjects.java new file mode 100644 index 00000000000..bb546b3f934 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjects.java @@ -0,0 +1,51 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v91; + +import java.sql.Connection; +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class CreateIndexForPortfolioProjects extends DdlChange { + private static final String TABLE_NAME = "portfolio_projects"; + private static final String INDEX_NAME = "uniq_portfolio_projects"; + + public CreateIndexForPortfolioProjects(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection c = getDatabase().getDataSource().getConnection()) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, c)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn("portfolio_uuid") + .addColumn("project_uuid") + .setUnique(true) + .build()); + } + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferences.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferences.java new file mode 100644 index 00000000000..334d7d1abe3 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferences.java @@ -0,0 +1,52 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v91; + +import java.sql.Connection; +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class CreateIndexForPortfolioReferences extends DdlChange { + private static final String TABLE_NAME = "portfolio_references"; + private static final String INDEX_NAME = "uniq_portfolio_references"; + + public CreateIndexForPortfolioReferences(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection c = getDatabase().getDataSource().getConnection()) { + + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, c)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn("portfolio_uuid") + .addColumn("reference_uuid") + .setUnique(true) + .build()); + } + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfolios.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfolios.java new file mode 100644 index 00000000000..7492ee16b0c --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfolios.java @@ -0,0 +1,50 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v91; + +import java.sql.Connection; +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.db.DatabaseUtils; +import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class CreateIndexOnKeeForPortfolios extends DdlChange { + private static final String TABLE_NAME = "portfolios"; + private static final String INDEX_NAME = "uniq_portfolios_kee"; + + public CreateIndexOnKeeForPortfolios(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + try (Connection c = getDatabase().getDataSource().getConnection()) { + if (!DatabaseUtils.indexExistsIgnoreCase(TABLE_NAME, INDEX_NAME, c)) { + context.execute(new CreateIndexBuilder() + .setTable(TABLE_NAME) + .setName(INDEX_NAME) + .addColumn("kee") + .setUnique(true) + .build()); + } + } + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91.java index 6a000293a25..f7b27125deb 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91.java @@ -37,9 +37,12 @@ public class DbVersion91 implements DbVersion { .add(6009, "Alter column 'client_secret' of 'alm_settings' table to length 160", AlterClientSecretColumnLengthOfAlmSettingsTable.class) .add(6010, "Alter column 'private_key' of 'alm_settings' table to length 2500", AlterPrivateKeyColumnLengthOfAlmSettingsTable.class) .add(6011, "Create 'portfolios' table", CreatePortfoliosTable.class) - .add(6012, "Create 'portfolio_references' table", CreatePortfolioReferencesTable.class) - .add(6013, "Create 'portfolio_projects' table", CreatePortfolioProjectsTable.class) - .add(6014, "Migrate portfolios to new tables", MigratePortfoliosToNewTables.class) + .add(6012, "Create unique index for 'kee' in 'portfolios'", CreateIndexOnKeeForPortfolios.class) + .add(6013, "Create 'portfolio_references' table", CreatePortfolioReferencesTable.class) + .add(6014, "Create unique index for 'portfolio_references'", CreateIndexForPortfolioReferences.class) + .add(6015, "Create 'portfolio_projects' table", CreatePortfolioProjectsTable.class) + .add(6016, "Create unique index for 'portfolio_projects'", CreateIndexForPortfolioProjects.class) + .add(6017, "Migrate portfolios to new tables", MigratePortfoliosToNewTables.class) ; } } diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjectsTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjectsTest.java new file mode 100644 index 00000000000..154236ef664 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjectsTest.java @@ -0,0 +1,53 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v91; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +public class CreateIndexForPortfolioProjectsTest { + private final static String TABLE_NAME = "portfolio_projects"; + private final static String INDEX_NAME = "uniq_portfolio_projects"; + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateIndexForPortfolioProjectsTest.class, "schema.sql"); + + private final CreateIndexForPortfolioProjects underTest = new CreateIndexForPortfolioProjects(db.database()); + + @Test + public void should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + underTest.execute(); + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, "portfolio_uuid", "project_uuid"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + + underTest.execute(); + //re-entrant + underTest.execute(); + + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, "portfolio_uuid", "project_uuid"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferencesTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferencesTest.java new file mode 100644 index 00000000000..d66daf581f2 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferencesTest.java @@ -0,0 +1,53 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v91; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +public class CreateIndexForPortfolioReferencesTest { + private final static String TABLE_NAME = "portfolio_references"; + private final static String INDEX_NAME = "uniq_portfolio_references"; + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateIndexForPortfolioReferencesTest.class, "schema.sql"); + + private final CreateIndexForPortfolioReferences underTest = new CreateIndexForPortfolioReferences(db.database()); + + @Test + public void should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + underTest.execute(); + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, "portfolio_uuid", "reference_uuid"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + + underTest.execute(); + //re-entrant + underTest.execute(); + + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, "portfolio_uuid", "reference_uuid"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfoliosTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfoliosTest.java new file mode 100644 index 00000000000..6979c87cfd0 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfoliosTest.java @@ -0,0 +1,53 @@ +/* + * SonarQube + * Copyright (C) 2009-2021 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.v91; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; + +public class CreateIndexOnKeeForPortfoliosTest { + private final static String TABLE_NAME = "portfolios"; + private final static String INDEX_NAME = "uniq_portfolios_kee"; + + @Rule + public final CoreDbTester db = CoreDbTester.createForSchema(CreateIndexOnKeeForPortfoliosTest.class, "schema.sql"); + + private final CreateIndexOnKeeForPortfolios underTest = new CreateIndexOnKeeForPortfolios(db.database()); + + @Test + public void should_create_index() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + underTest.execute(); + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, "kee"); + } + + @Test + public void migration_should_be_reentrant() throws SQLException { + db.assertIndexDoesNotExist(TABLE_NAME, INDEX_NAME); + + underTest.execute(); + //re-entrant + underTest.execute(); + + db.assertUniqueIndex(TABLE_NAME, INDEX_NAME, "kee"); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91Test.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91Test.java index 0a361304f1b..23abf039d73 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91Test.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v91/DbVersion91Test.java @@ -41,7 +41,7 @@ public class DbVersion91Test { @Test public void verify_migration_count() { - verifyMigrationCount(underTest, 14); + verifyMigrationCount(underTest, 17); } } diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjectsTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjectsTest/schema.sql new file mode 100644 index 00000000000..87551c2fc78 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioProjectsTest/schema.sql @@ -0,0 +1,30 @@ +CREATE TABLE "PORTFOLIOS"( + "UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400) NOT NULL, + "NAME" VARCHAR(2000) NOT NULL, + "DESCRIPTION" VARCHAR(2000), + "ROOT_UUID" VARCHAR(40) NOT NULL, + "PARENT_UUID" VARCHAR(40), + "PRIVATE" BOOLEAN NOT NULL, + "SELECTION_MODE" VARCHAR(50) NOT NULL, + "SELECTION_EXPRESSION" VARCHAR(50), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "PORTFOLIOS" ADD CONSTRAINT "PK_PORTFOLIOS" PRIMARY KEY("UUID"); + +CREATE TABLE "PORTFOLIO_PROJECTS"( + "UUID" VARCHAR(40) NOT NULL, + "PORTFOLIO_UUID" VARCHAR(40) NOT NULL, + "PROJECT_UUID" VARCHAR(40) NOT NULL, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "PORTFOLIO_PROJECTS" ADD CONSTRAINT "PK_PORTFOLIO_PROJECTS" PRIMARY KEY("UUID"); + +CREATE TABLE "PORTFOLIO_REFERENCES"( + "UUID" VARCHAR(40) NOT NULL, + "PORTFOLIO_UUID" VARCHAR(40) NOT NULL, + "REFERENCE_UUID" VARCHAR(40) NOT NULL, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "PORTFOLIO_REFERENCES" ADD CONSTRAINT "PK_PORTFOLIO_REFERENCES" PRIMARY KEY("UUID"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferencesTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferencesTest/schema.sql new file mode 100644 index 00000000000..87551c2fc78 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexForPortfolioReferencesTest/schema.sql @@ -0,0 +1,30 @@ +CREATE TABLE "PORTFOLIOS"( + "UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400) NOT NULL, + "NAME" VARCHAR(2000) NOT NULL, + "DESCRIPTION" VARCHAR(2000), + "ROOT_UUID" VARCHAR(40) NOT NULL, + "PARENT_UUID" VARCHAR(40), + "PRIVATE" BOOLEAN NOT NULL, + "SELECTION_MODE" VARCHAR(50) NOT NULL, + "SELECTION_EXPRESSION" VARCHAR(50), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "PORTFOLIOS" ADD CONSTRAINT "PK_PORTFOLIOS" PRIMARY KEY("UUID"); + +CREATE TABLE "PORTFOLIO_PROJECTS"( + "UUID" VARCHAR(40) NOT NULL, + "PORTFOLIO_UUID" VARCHAR(40) NOT NULL, + "PROJECT_UUID" VARCHAR(40) NOT NULL, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "PORTFOLIO_PROJECTS" ADD CONSTRAINT "PK_PORTFOLIO_PROJECTS" PRIMARY KEY("UUID"); + +CREATE TABLE "PORTFOLIO_REFERENCES"( + "UUID" VARCHAR(40) NOT NULL, + "PORTFOLIO_UUID" VARCHAR(40) NOT NULL, + "REFERENCE_UUID" VARCHAR(40) NOT NULL, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "PORTFOLIO_REFERENCES" ADD CONSTRAINT "PK_PORTFOLIO_REFERENCES" PRIMARY KEY("UUID"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfoliosTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfoliosTest/schema.sql new file mode 100644 index 00000000000..8c3568d7b40 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v91/CreateIndexOnKeeForPortfoliosTest/schema.sql @@ -0,0 +1,14 @@ +CREATE TABLE "PORTFOLIOS"( + "UUID" VARCHAR(40) NOT NULL, + "KEE" VARCHAR(400) NOT NULL, + "NAME" VARCHAR(2000) NOT NULL, + "DESCRIPTION" VARCHAR(2000), + "ROOT_UUID" VARCHAR(40) NOT NULL, + "PARENT_UUID" VARCHAR(40), + "PRIVATE" BOOLEAN NOT NULL, + "SELECTION_MODE" VARCHAR(50) NOT NULL, + "SELECTION_EXPRESSION" VARCHAR(50), + "CREATED_AT" BIGINT NOT NULL, + "UPDATED_AT" BIGINT NOT NULL +); +ALTER TABLE "PORTFOLIOS" ADD CONSTRAINT "PK_PORTFOLIOS" PRIMARY KEY("UUID"); -- 2.39.5