diff options
34 files changed, 1386 insertions, 1601 deletions
diff --git a/server/sonar-db-core/src/main/java/org/sonar/db/CoreDdlUtils.java b/server/sonar-db-core/src/main/java/org/sonar/db/CoreDdlUtils.java deleted file mode 100644 index a219ff51ce7..00000000000 --- a/server/sonar-db-core/src/main/java/org/sonar/db/CoreDdlUtils.java +++ /dev/null @@ -1,60 +0,0 @@ -/* - * 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.db; - -import java.io.PrintWriter; -import java.sql.Connection; -import org.apache.commons.io.output.NullWriter; -import org.apache.ibatis.io.Resources; -import org.apache.ibatis.jdbc.ScriptRunner; - -/** - * Util class to create Sonar database tables - * - * @since 2.12 - */ -public final class CoreDdlUtils { - - private CoreDdlUtils() { - } - - public static boolean supportsDialect(String dialect) { - return "h2".equals(dialect); - } - - public static void executeScript(Connection connection, String path) { - ScriptRunner scriptRunner = newScriptRunner(connection); - try { - scriptRunner.runScript(Resources.getResourceAsReader(path)); - connection.commit(); - - } catch (Exception e) { - throw new IllegalStateException("Fail to restore: " + path, e); - } - } - - private static ScriptRunner newScriptRunner(Connection connection) { - ScriptRunner scriptRunner = new ScriptRunner(connection); - scriptRunner.setDelimiter(";"); - scriptRunner.setStopOnError(true); - scriptRunner.setLogWriter(new PrintWriter(new NullWriter())); - return scriptRunner; - } -} diff --git a/server/sonar-db-core/src/test/java/org/sonar/db/CoreDbTester.java b/server/sonar-db-core/src/test/java/org/sonar/db/CoreDbTester.java index 6df2eccaba2..ed85f851228 100644 --- a/server/sonar-db-core/src/test/java/org/sonar/db/CoreDbTester.java +++ b/server/sonar-db-core/src/test/java/org/sonar/db/CoreDbTester.java @@ -28,21 +28,19 @@ import org.apache.commons.lang.StringUtils; public class CoreDbTester extends AbstractDbTester<CoreTestDb> { private final DefaultOrganizationTesting defaultOrganizationTesting; - private CoreDbTester(String schemaPath) { - super(CoreTestDb.create(schemaPath)); + private CoreDbTester(CoreTestDb testDb) { + super(testDb); this.defaultOrganizationTesting = new DefaultOrganizationTesting(this); } public static CoreDbTester createForSchema(Class testClass, String filename) { String path = StringUtils.replaceChars(testClass.getCanonicalName(), '.', '/'); String schemaPath = path + "/" + filename; - return new CoreDbTester(schemaPath); + return new CoreDbTester(CoreTestDb.create(schemaPath)); } public static CoreDbTester createEmpty() { - String path = StringUtils.replaceChars(CoreDbTester.class.getCanonicalName(), '.', '/'); - String schemaPath = path + "/empty.sql"; - return new CoreDbTester(schemaPath); + return new CoreDbTester(CoreTestDb.createEmpty()); } @Override diff --git a/server/sonar-db-core/src/test/java/org/sonar/db/CoreDdlUtilsTest.java b/server/sonar-db-core/src/test/java/org/sonar/db/CoreDdlUtilsTest.java deleted file mode 100644 index 20acb793425..00000000000 --- a/server/sonar-db-core/src/test/java/org/sonar/db/CoreDdlUtilsTest.java +++ /dev/null @@ -1,40 +0,0 @@ -/* - * 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.db; - -import org.assertj.core.api.Assertions; -import org.junit.Rule; -import org.junit.Test; -import org.junit.rules.ExpectedException; - -import static org.assertj.core.api.Assertions.assertThat; - -public class CoreDdlUtilsTest { - @Rule - public ExpectedException expectedException = ExpectedException.none(); - - @Test - public void shouldSupportOnlyH2() { - Assertions.assertThat(CoreDdlUtils.supportsDialect("h2")).isTrue(); - assertThat(CoreDdlUtils.supportsDialect("postgresql")).isFalse(); - assertThat(CoreDdlUtils.supportsDialect("oracle")).isFalse(); - assertThat(CoreDdlUtils.supportsDialect("mssql")).isFalse(); - } -} diff --git a/server/sonar-db-core/src/test/java/org/sonar/db/CoreH2Database.java b/server/sonar-db-core/src/test/java/org/sonar/db/CoreH2Database.java index 08c7f2b22ba..f4a78eed82d 100644 --- a/server/sonar-db-core/src/test/java/org/sonar/db/CoreH2Database.java +++ b/server/sonar-db-core/src/test/java/org/sonar/db/CoreH2Database.java @@ -19,20 +19,21 @@ */ package org.sonar.db; +import java.io.PrintWriter; import java.sql.Connection; import java.sql.SQLException; import javax.sql.DataSource; import org.apache.commons.dbcp2.BasicDataSource; -import org.apache.commons.dbutils.DbUtils; +import org.apache.commons.io.output.NullWriter; +import org.apache.ibatis.io.Resources; +import org.apache.ibatis.jdbc.ScriptRunner; import org.sonar.db.dialect.Dialect; import org.sonar.db.dialect.H2; import static java.lang.String.format; /** - * H2 in-memory database, used for unit tests only. - * - * @since 3.2 + * H2 in-memory database, used for unit tests only against an empty DB or a provided H2 SQL script. */ public class CoreH2Database implements Database { private final String name; @@ -63,18 +64,32 @@ public class CoreH2Database implements Database { } public void executeScript(String classloaderPath) { - Connection connection = null; - try { - connection = datasource.getConnection(); - CoreDdlUtils.executeScript(connection, classloaderPath); - + try (Connection connection = datasource.getConnection()) { + executeScript(connection, classloaderPath); } catch (SQLException e) { throw new IllegalStateException("Fail to execute script: " + classloaderPath, e); - } finally { - DbUtils.closeQuietly(connection); } } + private static void executeScript(Connection connection, String path) { + ScriptRunner scriptRunner = newScriptRunner(connection); + try { + scriptRunner.runScript(Resources.getResourceAsReader(path)); + connection.commit(); + + } catch (Exception e) { + throw new IllegalStateException("Fail to restore: " + path, e); + } + } + + private static ScriptRunner newScriptRunner(Connection connection) { + ScriptRunner scriptRunner = new ScriptRunner(connection); + scriptRunner.setDelimiter(";"); + scriptRunner.setStopOnError(true); + scriptRunner.setLogWriter(new PrintWriter(new NullWriter())); + return scriptRunner; + } + @Override public void stop() { try { diff --git a/server/sonar-db-core/src/test/java/org/sonar/db/CoreTestDb.java b/server/sonar-db-core/src/test/java/org/sonar/db/CoreTestDb.java index 6699612c5bb..c7fdb601b1d 100644 --- a/server/sonar-db-core/src/test/java/org/sonar/db/CoreTestDb.java +++ b/server/sonar-db-core/src/test/java/org/sonar/db/CoreTestDb.java @@ -26,7 +26,9 @@ import java.net.URI; import java.util.Map; import java.util.Properties; import java.util.function.BiConsumer; +import java.util.function.Consumer; import java.util.function.Function; +import javax.annotation.Nullable; import org.apache.commons.codec.digest.DigestUtils; import org.apache.commons.io.FileUtils; import org.apache.commons.io.IOUtils; @@ -65,27 +67,35 @@ class CoreTestDb implements TestDb { } static CoreTestDb create(String schemaPath) { + requireNonNull(schemaPath, "schemaPath can't be null"); + return new CoreTestDb().init(schemaPath); } - private CoreTestDb init(String schemaPath) { - requireNonNull(schemaPath, "schemaPath can't be null"); + static CoreTestDb createEmpty() { + return new CoreTestDb().init(null); + } + private CoreTestDb init(@Nullable String schemaPath) { Function<Settings, Database> databaseCreator = settings -> { String dialect = settings.getString("sonar.jdbc.dialect"); if (dialect != null && !"h2".equals(dialect)) { return new DefaultDatabase(new LogbackHelper(), settings); } - return new CoreH2Database("h2Tests-" + DigestUtils.md5Hex(schemaPath)); + return new CoreH2Database("h2Tests-" + (schemaPath == null ? "empty" : DigestUtils.md5Hex(schemaPath))); }; - Function<Database, Boolean> databaseInitializer = database -> { - // will fail if not H2 + Consumer<Database> databaseInitializer = database -> { + if (schemaPath == null) { + return; + } + + // scripts are assumed to be using H2 specific syntax, ignore the test if not on H2 if (!database.getDialect().getId().equals("h2")) { - return false; + database.stop(); + throw new AssumptionViolatedException("This test is intended to be run on H2 only"); } ((CoreH2Database) database).executeScript(schemaPath); - return true; }; BiConsumer<Database, Boolean> noPostStartAction = (db, created) -> { }; @@ -94,20 +104,17 @@ class CoreTestDb implements TestDb { return this; } - protected void init(Function<Settings, Database> databaseSupplier, - Function<Database, Boolean> databaseInitializer, + protected void init(Function<Settings, Database> databaseCreator, + Consumer<Database> databaseInitializer, BiConsumer<Database, Boolean> extendedStart) { if (db == null) { Settings settings = new MapSettings().addProperties(System.getProperties()); loadOrchestratorSettings(settings); logJdbcSettings(settings); - db = databaseSupplier.apply(settings); + db = databaseCreator.apply(settings); db.start(); - if (!databaseInitializer.apply(db)) { - db.stop(); - throw new IllegalStateException("Can't apply init script"); - } + databaseInitializer.accept(db); Loggers.get(getClass()).debug("Test Database: " + db); commands = DatabaseCommands.forDialect(db.getDialect()); diff --git a/server/sonar-db-dao/build.gradle b/server/sonar-db-dao/build.gradle index 18f3fefcfe8..b78b2b6fada 100644 --- a/server/sonar-db-dao/build.gradle +++ b/server/sonar-db-dao/build.gradle @@ -17,6 +17,7 @@ dependencies { compile project(path: ':sonar-plugin-api', configuration: 'shadow') compile project(':server:sonar-db-core') + compile project(':server:sonar-db-migration') compile project(':sonar-core') compileOnly 'com.google.code.findbugs:jsr305' @@ -44,6 +45,12 @@ test { systemProperty 'orchestrator.configUrl', System.getProperty('orchestrator.configUrl') } +task dumpSchema(type:JavaExec) { + main = 'org.sonar.db.dump.DumpSQSchema' + classpath = sourceSets.test.runtimeClasspath +} +tasks.check.dependsOn dumpSchema + task testJar(type: Jar) { classifier = 'tests' from sourceSets.test.output diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/DdlUtils.java b/server/sonar-db-dao/src/main/java/org/sonar/db/DdlUtils.java deleted file mode 100644 index b3bf45b89ae..00000000000 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/DdlUtils.java +++ /dev/null @@ -1,42 +0,0 @@ -/* - * 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.db; - -import java.sql.Connection; - -import static org.sonar.db.CoreDdlUtils.executeScript; - -public class DdlUtils { - - private DdlUtils() { - // prevents instantiation - } - - /** - * The connection is commited in this method but not closed. - */ - public static void createSchema(Connection connection, String dialect, boolean createSchemaMigrations) { - if (createSchemaMigrations) { - executeScript(connection, "org/sonar/db/schema_migrations-" + dialect + ".ddl"); - } - executeScript(connection, "org/sonar/db/schema-" + dialect + ".ddl"); - executeScript(connection, "org/sonar/db/rows-" + dialect + ".sql"); - } -} diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/rows-h2.sql b/server/sonar-db-dao/src/main/resources/org/sonar/db/rows-h2.sql deleted file mode 100644 index fb121156331..00000000000 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/rows-h2.sql +++ /dev/null @@ -1,28 +0,0 @@ -INSERT INTO USERS(ID, UUID, LOGIN, NAME, EMAIL, EXTERNAL_ID, EXTERNAL_LOGIN, EXTERNAL_IDENTITY_PROVIDER, USER_LOCAL, CRYPTED_PASSWORD, SALT, HASH_METHOD, IS_ROOT, ONBOARDED, CREATED_AT, UPDATED_AT) VALUES (1, 'UuidnciQUUs7Zd3KPvFD', 'admin', 'Administrator', null, 'admin', 'admin', 'sonarqube', true, '$2a$12$uCkkXmhW5ThVK8mpBvnXOOJRLd64LJeHTeCkSuB3lfaR2N0AYBaSi', null, 'BCRYPT', false, true, '1418215735482', '1418215735482'); -ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2; - -INSERT INTO GROUPS(ID, ORGANIZATION_UUID, NAME, DESCRIPTION, CREATED_AT, UPDATED_AT) VALUES (1, 'AVdqnciQUUs7Zd3KPvFD', 'sonar-administrators', 'System administrators', '2011-09-26 22:27:51.0', '2011-09-26 22:27:51.0'); -INSERT INTO GROUPS(ID, ORGANIZATION_UUID, NAME, DESCRIPTION, CREATED_AT, UPDATED_AT) VALUES (2, 'AVdqnciQUUs7Zd3KPvFD', 'sonar-users', 'Any new users created will automatically join this group', '2011-09-26 22:27:51.0', '2011-09-26 22:27:51.0'); -ALTER TABLE GROUPS ALTER COLUMN ID RESTART WITH 3; - -INSERT INTO QUALITY_GATES(ID, UUID, NAME, IS_BUILT_IN, CREATED_AT, UPDATED_AT) VALUES (1, 'AWASGWAKYOI_InFKS3UF', 'Sonar way', true, '2011-09-26 22:27:51.0', '2011-09-26 22:27:51.0'); - -INSERT INTO ORGANIZATIONS (UUID, KEE, NAME, GUARDED, NEW_PROJECT_PRIVATE, DEFAULT_GROUP_ID, DEFAULT_QUALITY_GATE_UUID, SUBSCRIPTION, CREATED_AT, UPDATED_AT) VALUES ('AVdqnciQUUs7Zd3KPvFD', 'default-organization', 'Default Organization', true, false, 2, 'AWASGWAKYOI_InFKS3UF', 'SONARQUBE', '1474962596482', '1474962596482'); -INSERT INTO ORG_QUALITY_GATES (UUID, ORGANIZATION_UUID, QUALITY_GATE_UUID) VALUES ('AWAwlGzz-5zzlJtFU9G5', 'AVdqnciQUUs7Zd3KPvFD', 'AWASGWAKYOI_InFKS3UF'); - -INSERT INTO INTERNAL_PROPERTIES (KEE, IS_EMPTY, TEXT_VALUE, CREATED_AT) VALUES ('organization.default', false, 'AVdqnciQUUs7Zd3KPvFD', '1474962596482'); - -INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (1, 'AVdqnciQUUs7Zd3KPvFD', 1, null, 'admin'); -INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (2, 'AVdqnciQUUs7Zd3KPvFD', 1, null, 'profileadmin'); -INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (3, 'AVdqnciQUUs7Zd3KPvFD', 1, null, 'gateadmin'); -INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (4, 'AVdqnciQUUs7Zd3KPvFD', null, null, 'scan'); -INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (5, 'AVdqnciQUUs7Zd3KPvFD', null, null, 'provisioning'); -INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (6, 'AVdqnciQUUs7Zd3KPvFD', 1, null, 'provisioning'); -INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (7, 'AVdqnciQUUs7Zd3KPvFD', 1, null, 'applicationcreator'); -INSERT INTO GROUP_ROLES(ID, ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE) VALUES (8, 'AVdqnciQUUs7Zd3KPvFD', 1, null, 'portfoliocreator'); -ALTER TABLE GROUP_ROLES ALTER COLUMN ID RESTART WITH 9; - -INSERT INTO GROUPS_USERS(USER_ID, GROUP_ID) VALUES (1, 1); -INSERT INTO GROUPS_USERS(USER_ID, GROUP_ID) VALUES (1, 2); - -INSERT INTO ORGANIZATION_MEMBERS(ORGANIZATION_UUID, USER_ID) VALUES ('AVdqnciQUUs7Zd3KPvFD', 1); diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/schema-h2.ddl b/server/sonar-db-dao/src/main/resources/org/sonar/db/schema-h2.ddl deleted file mode 100644 index 8b28fb4c617..00000000000 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/schema-h2.ddl +++ /dev/null @@ -1,976 +0,0 @@ -CREATE TABLE "ORGANIZATIONS" ( - "UUID" VARCHAR(40) NOT NULL, - "KEE" VARCHAR(300) NOT NULL, - "NAME" VARCHAR(300) NOT NULL, - "DESCRIPTION" VARCHAR(256), - "URL" VARCHAR(256), - "AVATAR_URL" VARCHAR(256), - "GUARDED" BOOLEAN, - "DEFAULT_PERM_TEMPLATE_PROJECT" VARCHAR(40), - "DEFAULT_PERM_TEMPLATE_APP" VARCHAR(40), - "DEFAULT_PERM_TEMPLATE_PORT" VARCHAR(40), - "DEFAULT_GROUP_ID" INTEGER, - "DEFAULT_QUALITY_GATE_UUID" VARCHAR(40) NOT NULL, - "NEW_PROJECT_PRIVATE" BOOLEAN NOT NULL, - "SUBSCRIPTION" VARCHAR(40) NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_ORGANIZATIONS" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "ORGANIZATION_KEY" ON "ORGANIZATIONS" ("KEE"); - -CREATE TABLE "ORGANIZATION_MEMBERS" ( - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "USER_ID" INTEGER NOT NULL, - - CONSTRAINT "PK_ORGANIZATION_MEMBERS" PRIMARY KEY ("ORGANIZATION_UUID", "USER_ID") -); -CREATE INDEX "IX_ORG_MEMBERS_ON_USER_ID" ON "ORGANIZATION_MEMBERS" ("USER_ID"); - -CREATE TABLE "GROUPS_USERS" ( - "USER_ID" INTEGER, - "GROUP_ID" INTEGER -); -CREATE INDEX "INDEX_GROUPS_USERS_ON_GROUP_ID" ON "GROUPS_USERS" ("GROUP_ID"); -CREATE INDEX "INDEX_GROUPS_USERS_ON_USER_ID" ON "GROUPS_USERS" ("USER_ID"); -CREATE UNIQUE INDEX "GROUPS_USERS_UNIQUE" ON "GROUPS_USERS" ("GROUP_ID", "USER_ID"); - - -CREATE TABLE "RULES_PARAMETERS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "RULE_ID" INTEGER NOT NULL, - "NAME" VARCHAR(128) NOT NULL, - "PARAM_TYPE" VARCHAR(512) NOT NULL, - "DEFAULT_VALUE" VARCHAR(4000), - "DESCRIPTION" VARCHAR(4000) -); -CREATE INDEX "RULES_PARAMETERS_RULE_ID" ON "RULES_PARAMETERS" ("RULE_ID"); -CREATE UNIQUE INDEX "RULES_PARAMETERS_UNIQUE" ON "RULES_PARAMETERS" ("RULE_ID", "NAME"); - - -CREATE TABLE "RULES_PROFILES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "NAME" VARCHAR(100) NOT NULL, - "LANGUAGE" VARCHAR(20), - "KEE" VARCHAR(255) NOT NULL, - "RULES_UPDATED_AT" VARCHAR(100), - "CREATED_AT" TIMESTAMP, - "UPDATED_AT" TIMESTAMP, - "IS_BUILT_IN" BOOLEAN NOT NULL -); -CREATE UNIQUE INDEX "UNIQ_QPROF_KEY" ON "RULES_PROFILES" ("KEE"); - - -CREATE TABLE "ORG_QPROFILES" ( - "UUID" VARCHAR(255) NOT NULL, - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "RULES_PROFILE_UUID" VARCHAR(255) NOT NULL, - "PARENT_UUID" VARCHAR(255), - "LAST_USED" BIGINT, - "USER_UPDATED_AT" BIGINT, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_ORG_QPROFILES" PRIMARY KEY ("UUID") -); -CREATE INDEX "ORG_QPROFILES_ORG_UUID" ON "ORG_QPROFILES" ("ORGANIZATION_UUID"); -CREATE INDEX "ORG_QPROFILES_RP_UUID" ON "ORG_QPROFILES" ("RULES_PROFILE_UUID"); -CREATE INDEX "ORG_QPROFILES_PARENT_UUID" ON "ORG_QPROFILES" ("PARENT_UUID"); - - -CREATE TABLE "DEFAULT_QPROFILES" ( - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "LANGUAGE" VARCHAR(20) NOT NULL, - "QPROFILE_UUID" VARCHAR(255) NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_DEFAULT_QPROFILES" PRIMARY KEY ("ORGANIZATION_UUID", "LANGUAGE") -); -CREATE UNIQUE INDEX "UNIQ_DEFAULT_QPROFILES_UUID" ON "DEFAULT_QPROFILES" ("QPROFILE_UUID"); - - -CREATE TABLE "PROJECT_QPROFILES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "PROJECT_UUID" VARCHAR(50) NOT NULL, - "PROFILE_KEY" VARCHAR(50) NOT NULL -); -CREATE UNIQUE INDEX "UNIQ_PROJECT_QPROFILES" ON "PROJECT_QPROFILES" ("PROJECT_UUID", "PROFILE_KEY"); - - -CREATE TABLE "QPROFILE_EDIT_USERS" ( - "UUID" VARCHAR(40) NOT NULL, - "USER_ID" INTEGER NOT NULL, - "QPROFILE_UUID" VARCHAR(255) NOT NULL, - "CREATED_AT" BIGINT, - - CONSTRAINT "PK_QPROFILE_EDIT_USERS" PRIMARY KEY ("UUID") -); -CREATE INDEX "QPROFILE_EDIT_USERS_QPROFILE" ON "QPROFILE_EDIT_USERS" ("QPROFILE_UUID"); -CREATE UNIQUE INDEX "QPROFILE_EDIT_USERS_UNIQUE" ON "QPROFILE_EDIT_USERS" ("USER_ID", "QPROFILE_UUID"); - - -CREATE TABLE "QPROFILE_EDIT_GROUPS" ( - "UUID" VARCHAR(40) NOT NULL, - "GROUP_ID" INTEGER NOT NULL, - "QPROFILE_UUID" VARCHAR(255) NOT NULL, - "CREATED_AT" BIGINT, - - CONSTRAINT "PK_QPROFILE_EDIT_GROUPS" PRIMARY KEY ("UUID") -); -CREATE INDEX "QPROFILE_EDIT_GROUPS_QPROFILE" ON "QPROFILE_EDIT_GROUPS" ("QPROFILE_UUID"); -CREATE UNIQUE INDEX "QPROFILE_EDIT_GROUPS_UNIQUE" ON "QPROFILE_EDIT_GROUPS" ("GROUP_ID", "QPROFILE_UUID"); - - -CREATE TABLE "GROUPS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "NAME" VARCHAR(500), - "DESCRIPTION" VARCHAR(200), - "CREATED_AT" TIMESTAMP, - "UPDATED_AT" TIMESTAMP -); - - -CREATE TABLE "SNAPSHOTS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "UUID" VARCHAR(50) NOT NULL, - "CREATED_AT" BIGINT, - "BUILD_DATE" BIGINT, - "COMPONENT_UUID" VARCHAR(50) NOT NULL, - "STATUS" VARCHAR(4) NOT NULL DEFAULT 'U', - "PURGE_STATUS" INTEGER, - "ISLAST" BOOLEAN NOT NULL DEFAULT FALSE, - "VERSION" VARCHAR(500), - "BUILD_STRING" VARCHAR(100), - "PERIOD1_MODE" VARCHAR(100), - "PERIOD1_PARAM" VARCHAR(100), - "PERIOD1_DATE" BIGINT, - "PERIOD2_MODE" VARCHAR(100), - "PERIOD2_PARAM" VARCHAR(100), - "PERIOD2_DATE" BIGINT, - "PERIOD3_MODE" VARCHAR(100), - "PERIOD3_PARAM" VARCHAR(100), - "PERIOD3_DATE" BIGINT, - "PERIOD4_MODE" VARCHAR(100), - "PERIOD4_PARAM" VARCHAR(100), - "PERIOD4_DATE" BIGINT, - "PERIOD5_MODE" VARCHAR(100), - "PERIOD5_PARAM" VARCHAR(100), - "PERIOD5_DATE" BIGINT, - "REVISION" VARCHAR(100) -); -CREATE INDEX "SNAPSHOT_COMPONENT" ON "SNAPSHOTS" ("COMPONENT_UUID"); -CREATE UNIQUE INDEX "ANALYSES_UUID" ON "SNAPSHOTS" ("UUID"); - -CREATE TABLE "GROUP_ROLES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "GROUP_ID" INTEGER, - "RESOURCE_ID" INTEGER, - "ROLE" VARCHAR(64) NOT NULL -); -CREATE INDEX "GROUP_ROLES_RESOURCE" ON "GROUP_ROLES" ("RESOURCE_ID"); -CREATE UNIQUE INDEX "UNIQ_GROUP_ROLES" ON "GROUP_ROLES" ("ORGANIZATION_UUID", "GROUP_ID", "RESOURCE_ID", "ROLE"); - - -CREATE TABLE "RULE_REPOSITORIES" ( - "KEE" VARCHAR(200) NOT NULL, - "LANGUAGE" VARCHAR(20) NOT NULL, - "NAME" VARCHAR(4000) NOT NULL, - "CREATED_AT" BIGINT, - - CONSTRAINT "PK_RULE_REPOSITORIES" PRIMARY KEY ("KEE") -); - -CREATE TABLE "DEPRECATED_RULE_KEYS" ( - "UUID" VARCHAR(40) NOT NULL, - "RULE_ID" INTEGER NOT NULL, - "OLD_REPOSITORY_KEY" VARCHAR(200) NOT NULL, - "OLD_RULE_KEY" VARCHAR(255) NOT NULL, - "CREATED_AT" BIGINT, - - CONSTRAINT "PK_DEPRECATED_RULE_KEYS" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "UNIQ_DEPRECATED_RULE_KEYS" ON "DEPRECATED_RULE_KEYS" ("OLD_REPOSITORY_KEY", "OLD_RULE_KEY"); -CREATE INDEX "RULE_ID_DEPRECATED_RULE_KEYS" ON "DEPRECATED_RULE_KEYS" ("RULE_ID"); - -CREATE TABLE "RULES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "PLUGIN_KEY" VARCHAR(200), - "PLUGIN_RULE_KEY" VARCHAR(200) NOT NULL, - "PLUGIN_NAME" VARCHAR(255) NOT NULL, - "DESCRIPTION" VARCHAR(16777215), - "DESCRIPTION_FORMAT" VARCHAR(20), - "PRIORITY" INTEGER, - "IS_TEMPLATE" BOOLEAN DEFAULT FALSE, - "IS_EXTERNAL" BOOLEAN NOT NULL, - "IS_AD_HOC" BOOLEAN NOT NULL, - "TEMPLATE_ID" INTEGER, - "PLUGIN_CONFIG_KEY" VARCHAR(200), - "NAME" VARCHAR(200), - "STATUS" VARCHAR(40), - "LANGUAGE" VARCHAR(20), - "SCOPE" VARCHAR(20) NOT NULL, - "DEF_REMEDIATION_FUNCTION" VARCHAR(20), - "DEF_REMEDIATION_GAP_MULT" VARCHAR(20), - "DEF_REMEDIATION_BASE_EFFORT" VARCHAR(20), - "GAP_DESCRIPTION" VARCHAR(4000), - "SYSTEM_TAGS" VARCHAR(4000), - "SECURITY_STANDARDS" VARCHAR(4000), - "RULE_TYPE" TINYINT, - "CREATED_AT" BIGINT, - "UPDATED_AT" BIGINT -); -CREATE UNIQUE INDEX "RULES_REPO_KEY" ON "RULES" ("PLUGIN_NAME", "PLUGIN_RULE_KEY"); - -CREATE TABLE "RULES_METADATA" ( - "RULE_ID" INTEGER NOT NULL, - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "NOTE_DATA" CLOB, - "NOTE_USER_UUID" VARCHAR(255), - "NOTE_CREATED_AT" BIGINT, - "NOTE_UPDATED_AT" BIGINT, - "REMEDIATION_FUNCTION" VARCHAR(20), - "REMEDIATION_GAP_MULT" VARCHAR(20), - "REMEDIATION_BASE_EFFORT" VARCHAR(20), - "TAGS" VARCHAR(4000), - "AD_HOC_NAME" VARCHAR(200), - "AD_HOC_DESCRIPTION" VARCHAR(16777215), - "AD_HOC_SEVERITY" VARCHAR(10), - "AD_HOC_TYPE" TINYINT, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_RULES_METADATA" PRIMARY KEY ("RULE_ID", "ORGANIZATION_UUID") -); - -CREATE TABLE "EVENTS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "UUID" VARCHAR(40) NOT NULL, - "NAME" VARCHAR(400), - "ANALYSIS_UUID" VARCHAR(50) NOT NULL, - "COMPONENT_UUID" VARCHAR(50) NOT NULL, - "CATEGORY" VARCHAR(50), - "EVENT_DATE" BIGINT NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - "DESCRIPTION" VARCHAR(4000), - "EVENT_DATA" VARCHAR(4000) -); -CREATE INDEX "EVENTS_ANALYSIS" ON "EVENTS" ("ANALYSIS_UUID"); -CREATE INDEX "EVENTS_COMPONENT_UUID" ON "EVENTS" ("COMPONENT_UUID"); -CREATE UNIQUE INDEX "EVENTS_UUID" ON "EVENTS" ("UUID"); - -CREATE TABLE "EVENT_COMPONENT_CHANGES" ( - "UUID" VARCHAR(40) NOT NULL, - "EVENT_UUID" VARCHAR(40) NOT NULL, - "EVENT_COMPONENT_UUID" VARCHAR(50) NOT NULL, - "EVENT_ANALYSIS_UUID" VARCHAR(50) NOT NULL, - "CHANGE_CATEGORY" VARCHAR(20) NOT NULL, - "COMPONENT_UUID" VARCHAR(50) NOT NULL, - "COMPONENT_KEY" VARCHAR(400) NOT NULL, - "COMPONENT_NAME" VARCHAR(2000) NOT NULL, - "COMPONENT_BRANCH_KEY" VARCHAR(255), - "CREATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_EVENT_COMPONENT_CHANGES" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "EVENT_COMPONENT_CHANGES_UNIQUE" ON "EVENT_COMPONENT_CHANGES" ("EVENT_UUID", "CHANGE_CATEGORY", "COMPONENT_UUID"); -CREATE INDEX "EVENT_CPNT_CHANGES_CPNT" ON "EVENT_COMPONENT_CHANGES" ("EVENT_COMPONENT_UUID"); -CREATE INDEX "EVENT_CPNT_CHANGES_ANALYSIS" ON "EVENT_COMPONENT_CHANGES" ("EVENT_ANALYSIS_UUID"); - - -CREATE TABLE "QUALITY_GATES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "UUID" VARCHAR(40) NOT NULL, - "NAME" VARCHAR(100) NOT NULL, - "IS_BUILT_IN" BOOLEAN NOT NULL, - "CREATED_AT" TIMESTAMP, - "UPDATED_AT" TIMESTAMP, -); -CREATE UNIQUE INDEX "UNIQ_QUALITY_GATES_UUID" ON "QUALITY_GATES" ("UUID"); - - -CREATE TABLE "QUALITY_GATE_CONDITIONS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "QGATE_ID" INTEGER, - "METRIC_ID" INTEGER, - "OPERATOR" VARCHAR(3), - "VALUE_ERROR" VARCHAR(64), - "VALUE_WARNING" VARCHAR(64), - "PERIOD" INTEGER, - "CREATED_AT" TIMESTAMP, - "UPDATED_AT" TIMESTAMP, -); - -CREATE TABLE "ORG_QUALITY_GATES" ( - "UUID" VARCHAR(40) NOT NULL, - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "QUALITY_GATE_UUID" VARCHAR(40) NOT NULL, - - CONSTRAINT "PK_ORG_QUALITY_GATES" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "UNIQ_ORG_QUALITY_GATES" ON "ORG_QUALITY_GATES" ("ORGANIZATION_UUID","QUALITY_GATE_UUID"); - -CREATE TABLE "PROJECT_QGATES" ( -"PROJECT_UUID" VARCHAR(40) NOT NULL, -"QUALITY_GATE_UUID" VARCHAR(40) NOT NULL, - -CONSTRAINT "PK_PROJECT_QGATES" PRIMARY KEY ("PROJECT_UUID") -); -CREATE UNIQUE INDEX "UNIQ_PROJECT_QGATES" ON "PROJECT_QGATES" ("PROJECT_UUID", "QUALITY_GATE_UUID"); - -CREATE TABLE "PROPERTIES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "PROP_KEY" VARCHAR(512) NOT NULL, - "RESOURCE_ID" INTEGER, - "USER_ID" INTEGER, - "IS_EMPTY" BOOLEAN NOT NULL, - "TEXT_VALUE" VARCHAR(4000), - "CLOB_VALUE" CLOB, - "CREATED_AT" BIGINT NOT NULL -); -CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY"); - - -CREATE TABLE "PROJECT_LINKS" ( - "UUID" VARCHAR(40) NOT NULL, - "PROJECT_UUID" VARCHAR(50) NOT NULL, - "LINK_TYPE" VARCHAR(20) NOT NULL, - "NAME" VARCHAR(128), - "HREF" VARCHAR(2048) NOT NULL, - "CREATED_AT" BIGINT, - "UPDATED_AT" BIGINT, - - CONSTRAINT "PK_PROJECT_LINKS" PRIMARY KEY ("UUID") -); -CREATE INDEX "PROJECT_LINKS_PROJECT" ON "PROJECT_LINKS" ("PROJECT_UUID"); - - -CREATE TABLE "DUPLICATIONS_INDEX" ( - "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "ANALYSIS_UUID" VARCHAR(50) NOT NULL, - "COMPONENT_UUID" VARCHAR(50) NOT NULL, - "HASH" VARCHAR(50) NOT NULL, - "INDEX_IN_FILE" INTEGER NOT NULL, - "START_LINE" INTEGER NOT NULL, - "END_LINE" INTEGER NOT NULL -); -CREATE INDEX "DUPLICATIONS_INDEX_HASH" ON "DUPLICATIONS_INDEX" ("HASH"); -CREATE INDEX "DUPLICATION_ANALYSIS_COMPONENT" ON "DUPLICATIONS_INDEX" ("ANALYSIS_UUID", "COMPONENT_UUID"); - - -CREATE TABLE "LIVE_MEASURES" ( - "UUID" VARCHAR(40) NOT NULL, - "PROJECT_UUID" VARCHAR(50) NOT NULL, - "COMPONENT_UUID" VARCHAR(50) NOT NULL, - "METRIC_ID" INTEGER NOT NULL, - "VALUE" DOUBLE, - "TEXT_VALUE" VARCHAR(4000), - "VARIATION" DOUBLE, - "MEASURE_DATA" BINARY, - "UPDATE_MARKER" VARCHAR(40), - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_LIVE_MEASURES" PRIMARY KEY ("UUID") -); -CREATE INDEX "LIVE_MEASURES_PROJECT" ON "LIVE_MEASURES" ("PROJECT_UUID"); -CREATE UNIQUE INDEX "LIVE_MEASURES_COMPONENT" ON "LIVE_MEASURES" ("COMPONENT_UUID", "METRIC_ID"); - - -CREATE TABLE "PROJECT_MEASURES" ( - "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "VALUE" DOUBLE, - "METRIC_ID" INTEGER NOT NULL, - "COMPONENT_UUID" VARCHAR(50) NOT NULL, - "ANALYSIS_UUID" VARCHAR(50) NOT NULL, - "TEXT_VALUE" VARCHAR(4000), - "ALERT_STATUS" VARCHAR(5), - "ALERT_TEXT" VARCHAR(4000), - "DESCRIPTION" VARCHAR(4000), - "PERSON_ID" INTEGER, - "VARIATION_VALUE_1" DOUBLE, - "VARIATION_VALUE_2" DOUBLE, - "VARIATION_VALUE_3" DOUBLE, - "VARIATION_VALUE_4" DOUBLE, - "VARIATION_VALUE_5" DOUBLE, - "MEASURE_DATA" BINARY -); -CREATE INDEX "MEASURES_COMPONENT_UUID" ON "PROJECT_MEASURES" ("COMPONENT_UUID"); -CREATE INDEX "MEASURES_ANALYSIS_METRIC" ON "PROJECT_MEASURES" ("ANALYSIS_UUID", "METRIC_ID"); - - -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 "PROJECTS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "KEE" VARCHAR(400), - "UUID" VARCHAR(50) NOT NULL, - "UUID_PATH" VARCHAR(1500) NOT NULL, - "ROOT_UUID" VARCHAR(50) NOT NULL, - "PROJECT_UUID" VARCHAR(50) NOT NULL, - "MODULE_UUID" VARCHAR(50), - "MODULE_UUID_PATH" VARCHAR(1500), - "MAIN_BRANCH_PROJECT_UUID" VARCHAR(50), - "NAME" VARCHAR(2000), - "DESCRIPTION" VARCHAR(2000), - "PRIVATE" BOOLEAN NOT NULL, - "TAGS" VARCHAR(500), - "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 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"); -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"); - -CREATE TABLE "INTERNAL_COMPONENT_PROPS" ( - "UUID" VARCHAR(40) NOT NULL, - "COMPONENT_UUID" VARCHAR(50) NOT NULL, - "KEE" VARCHAR(512) NOT NULL, - "VALUE" VARCHAR(4000), - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - CONSTRAINT "INTERNAL_COMPONENT_PROPS_UUID" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "UNIQUE_COMPONENT_UUID_KEE" ON "INTERNAL_COMPONENT_PROPS" ("COMPONENT_UUID", "KEE"); - -CREATE TABLE "MANUAL_MEASURES" ( - "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "METRIC_ID" INTEGER NOT NULL, - "COMPONENT_UUID" VARCHAR(50) NOT NULL, - "VALUE" DOUBLE, - "TEXT_VALUE" VARCHAR(4000), - "USER_UUID" VARCHAR(255), - "DESCRIPTION" VARCHAR(4000), - "CREATED_AT" BIGINT, - "UPDATED_AT" BIGINT -); -CREATE INDEX "MANUAL_MEASURES_COMPONENT_UUID" ON "MANUAL_MEASURES" ("COMPONENT_UUID"); - - -CREATE TABLE "ACTIVE_RULES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "PROFILE_ID" INTEGER NOT NULL, - "RULE_ID" INTEGER NOT NULL, - "FAILURE_LEVEL" INTEGER NOT NULL, - "INHERITANCE" VARCHAR(10), - "CREATED_AT" BIGINT, - "UPDATED_AT" BIGINT -); -CREATE UNIQUE INDEX "ACTIVE_RULES_UNIQUE" ON "ACTIVE_RULES" ("PROFILE_ID","RULE_ID"); - - -CREATE TABLE "NOTIFICATIONS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "DATA" BLOB -); - - -CREATE TABLE "USER_ROLES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "USER_ID" INTEGER, - "RESOURCE_ID" INTEGER, - "ROLE" VARCHAR(64) NOT NULL -); -CREATE INDEX "USER_ROLES_RESOURCE" ON "USER_ROLES" ("RESOURCE_ID"); -CREATE INDEX "USER_ROLES_USER" ON "USER_ROLES" ("USER_ID"); - - -CREATE TABLE "ACTIVE_RULE_PARAMETERS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "ACTIVE_RULE_ID" INTEGER NOT NULL, - "RULES_PARAMETER_ID" INTEGER NOT NULL, - "RULES_PARAMETER_KEY" VARCHAR(128), - "VALUE" VARCHAR(4000) -); -CREATE INDEX "IX_ARP_ON_ACTIVE_RULE_ID" ON "ACTIVE_RULE_PARAMETERS" ("ACTIVE_RULE_ID"); - - -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"); - - -CREATE TABLE "METRICS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "NAME" VARCHAR(64) NOT NULL, - "DESCRIPTION" VARCHAR(255), - "DIRECTION" INTEGER NOT NULL DEFAULT 0, - "DOMAIN" VARCHAR(64), - "SHORT_NAME" VARCHAR(64), - "QUALITATIVE" BOOLEAN NOT NULL DEFAULT FALSE, - "VAL_TYPE" VARCHAR(8), - "USER_MANAGED" BOOLEAN DEFAULT FALSE, - "ENABLED" BOOLEAN DEFAULT TRUE, - "WORST_VALUE" DOUBLE, - "BEST_VALUE" DOUBLE, - "OPTIMIZED_BEST_VALUE" BOOLEAN, - "HIDDEN" BOOLEAN, - "DELETE_HISTORICAL_DATA" BOOLEAN, - "DECIMAL_SCALE" INTEGER -); -CREATE UNIQUE INDEX "METRICS_UNIQUE_NAME" ON "METRICS" ("NAME"); - - -CREATE TABLE "ISSUES" ( - "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "KEE" VARCHAR(50) UNIQUE NOT NULL, - "COMPONENT_UUID" VARCHAR(50), - "PROJECT_UUID" VARCHAR(50), - "RULE_ID" INTEGER, - "SEVERITY" VARCHAR(10), - "MANUAL_SEVERITY" BOOLEAN NOT NULL, - "MESSAGE" VARCHAR(4000), - "LINE" INTEGER, - "GAP" DOUBLE, - "EFFORT" INTEGER, - "STATUS" VARCHAR(20), - "RESOLUTION" VARCHAR(20), - "CHECKSUM" VARCHAR(1000), - "REPORTER" VARCHAR(255), - "ASSIGNEE" VARCHAR(255), - "AUTHOR_LOGIN" VARCHAR(255), - "ACTION_PLAN_KEY" VARCHAR(50) NULL, - "ISSUE_ATTRIBUTES" VARCHAR(4000), - "TAGS" VARCHAR(4000), - "ISSUE_CREATION_DATE" BIGINT, - "ISSUE_CLOSE_DATE" BIGINT, - "ISSUE_UPDATE_DATE" BIGINT, - "CREATED_AT" BIGINT, - "UPDATED_AT" BIGINT, - "LOCATIONS" BLOB, - "ISSUE_TYPE" TINYINT, - "FROM_HOTSPOT" BOOLEAN NULL -); -CREATE UNIQUE INDEX "ISSUES_KEE" ON "ISSUES" ("KEE"); -CREATE INDEX "ISSUES_COMPONENT_UUID" ON "ISSUES" ("COMPONENT_UUID"); -CREATE INDEX "ISSUES_PROJECT_UUID" ON "ISSUES" ("PROJECT_UUID"); -CREATE INDEX "ISSUES_RULE_ID" ON "ISSUES" ("RULE_ID"); -CREATE INDEX "ISSUES_RESOLUTION" ON "ISSUES" ("RESOLUTION"); -CREATE INDEX "ISSUES_ASSIGNEE" ON "ISSUES" ("ASSIGNEE"); -CREATE INDEX "ISSUES_CREATION_DATE" ON "ISSUES" ("ISSUE_CREATION_DATE"); -CREATE INDEX "ISSUES_UPDATED_AT" ON "ISSUES" ("UPDATED_AT"); - - -CREATE TABLE "ISSUE_CHANGES" ( - "ID" BIGINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "KEE" VARCHAR(50), - "ISSUE_KEY" VARCHAR(50) NOT NULL, - "USER_LOGIN" VARCHAR(255), - "CHANGE_TYPE" VARCHAR(40), - "CHANGE_DATA" VARCHAR(16777215), - "CREATED_AT" BIGINT, - "UPDATED_AT" BIGINT, - "ISSUE_CHANGE_CREATION_DATE" BIGINT -); -CREATE INDEX "ISSUE_CHANGES_KEE" ON "ISSUE_CHANGES" ("KEE"); -CREATE INDEX "ISSUE_CHANGES_ISSUE_KEY" ON "ISSUE_CHANGES" ("ISSUE_KEY"); - - -CREATE TABLE "PERMISSION_TEMPLATES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "NAME" VARCHAR(100) NOT NULL, - "KEE" VARCHAR(100) NOT NULL, - "DESCRIPTION" VARCHAR(4000), - "KEY_PATTERN" VARCHAR(500), - "CREATED_AT" TIMESTAMP, - "UPDATED_AT" TIMESTAMP -); - - -CREATE TABLE "PERM_TPL_CHARACTERISTICS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "TEMPLATE_ID" INTEGER NOT NULL, - "PERMISSION_KEY" VARCHAR(64) NOT NULL, - "WITH_PROJECT_CREATOR" BOOLEAN NOT NULL DEFAULT FALSE, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL -); -CREATE UNIQUE INDEX "UNIQ_PERM_TPL_CHARAC" ON "PERM_TPL_CHARACTERISTICS" ("TEMPLATE_ID", "PERMISSION_KEY"); - - -CREATE TABLE "PERM_TEMPLATES_USERS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "USER_ID" INTEGER NOT NULL, - "TEMPLATE_ID" INTEGER NOT NULL, - "PERMISSION_REFERENCE" VARCHAR(64) NOT NULL, - "CREATED_AT" TIMESTAMP, - "UPDATED_AT" TIMESTAMP -); - - -CREATE TABLE "PERM_TEMPLATES_GROUPS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "GROUP_ID" INTEGER, - "TEMPLATE_ID" INTEGER NOT NULL, - "PERMISSION_REFERENCE" VARCHAR(64) NOT NULL, - "CREATED_AT" TIMESTAMP, - "UPDATED_AT" TIMESTAMP -); - - -CREATE TABLE "QPROFILE_CHANGES" ( - "KEE" VARCHAR(40) NOT NULL, - "RULES_PROFILE_UUID" VARCHAR(255) NOT NULL, - "CHANGE_TYPE" VARCHAR(20) NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - "USER_UUID" VARCHAR(255), - "CHANGE_DATA" CLOB, - - CONSTRAINT "PK_QPROFILE_CHANGES" PRIMARY KEY ("KEE") -); -CREATE INDEX "QP_CHANGES_RULES_PROFILE_UUID" ON "QPROFILE_CHANGES" ("RULES_PROFILE_UUID"); - - -CREATE TABLE "FILE_SOURCES" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "PROJECT_UUID" VARCHAR(50) NOT NULL, - "FILE_UUID" VARCHAR(50) NOT NULL, - "LINE_HASHES" CLOB, - "LINE_HASHES_VERSION" INTEGER, - "LINE_COUNT" INTEGER NOT NULL, - "BINARY_DATA" BLOB, - "DATA_HASH" VARCHAR(50), - "SRC_HASH" VARCHAR(50), - "REVISION" VARCHAR(100), - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL -); -CREATE INDEX "FILE_SOURCES_PROJECT_UUID" ON "FILE_SOURCES" ("PROJECT_UUID"); -CREATE UNIQUE INDEX "FILE_SOURCES_FILE_UUID" ON "FILE_SOURCES" ("FILE_UUID"); -CREATE INDEX "FILE_SOURCES_UPDATED_AT" ON "FILE_SOURCES" ("UPDATED_AT"); - - -CREATE TABLE "CE_QUEUE" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "UUID" VARCHAR(40) NOT NULL, - "TASK_TYPE" VARCHAR(15) NOT NULL, - "COMPONENT_UUID" VARCHAR(40) NULL, - "MAIN_COMPONENT_UUID" VARCHAR(40) NULL, - "STATUS" VARCHAR(15) NOT NULL, - "SUBMITTER_UUID" VARCHAR(255) NULL, - "WORKER_UUID" VARCHAR(40) NULL, - "EXECUTION_COUNT" INTEGER NOT NULL, - "STARTED_AT" BIGINT NULL, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL -); -CREATE UNIQUE INDEX "CE_QUEUE_UUID" ON "CE_QUEUE" ("UUID"); -CREATE INDEX "CE_QUEUE_COMPONENT" ON "CE_QUEUE" ("COMPONENT_UUID"); -CREATE INDEX "CE_QUEUE_MAIN_COMPONENT" ON "CE_QUEUE" ("MAIN_COMPONENT_UUID"); -CREATE INDEX "CE_QUEUE_STATUS" ON "CE_QUEUE" ("STATUS"); - - -CREATE TABLE "CE_ACTIVITY" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "UUID" VARCHAR(40) NOT NULL, - "TASK_TYPE" VARCHAR(15) NOT NULL, - "COMPONENT_UUID" VARCHAR(40) NULL, - "MAIN_COMPONENT_UUID" VARCHAR(40) NULL, - "ANALYSIS_UUID" VARCHAR(50) NULL, - "STATUS" VARCHAR(15) NOT NULL, - "IS_LAST" BOOLEAN, - "IS_LAST_KEY" VARCHAR(55), - "MAIN_IS_LAST" BOOLEAN, - "MAIN_IS_LAST_KEY" VARCHAR(55), - "SUBMITTER_UUID" VARCHAR(255) NULL, - "WORKER_UUID" VARCHAR(40) NULL, - "EXECUTION_COUNT" INTEGER NOT NULL, - "SUBMITTED_AT" BIGINT NOT NULL, - "STARTED_AT" BIGINT NULL, - "EXECUTED_AT" BIGINT NULL, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - "EXECUTION_TIME_MS" BIGINT NULL, - "ERROR_MESSAGE" VARCHAR(1000), - "ERROR_STACKTRACE" CLOB, - "ERROR_TYPE" VARCHAR(20) -); -CREATE UNIQUE INDEX "CE_ACTIVITY_UUID" ON "CE_ACTIVITY" ("UUID"); -CREATE INDEX "CE_ACTIVITY_COMPONENT" ON "CE_ACTIVITY" ("COMPONENT_UUID"); -CREATE INDEX "CE_ACTIVITY_MAIN_COMPONENT" ON "CE_ACTIVITY" ("COMPONENT_UUID"); -CREATE INDEX "CE_ACTIVITY_ISLAST_KEY" ON "CE_ACTIVITY" ("IS_LAST_KEY"); -CREATE INDEX "CE_ACTIVITY_ISLAST" ON "CE_ACTIVITY" ("IS_LAST", "STATUS"); -CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST_KEY" ON "CE_ACTIVITY" ("MAIN_IS_LAST_KEY"); -CREATE INDEX "CE_ACTIVITY_MAIN_ISLAST" ON "CE_ACTIVITY" ("MAIN_IS_LAST", "STATUS"); - - -CREATE TABLE "CE_TASK_CHARACTERISTICS" ( - "UUID" VARCHAR(40) NOT NULL, - "TASK_UUID" VARCHAR(40) NOT NULL, - "KEE" VARCHAR(50) NOT NULL, - "TEXT_VALUE" VARCHAR(4000), - - CONSTRAINT "PK_CE_TASK_CHARACTERISTICS" PRIMARY KEY ("UUID") -); -CREATE INDEX "CE_TASK_CHARACTERISTICS_TASK_UUID" ON "CE_TASK_CHARACTERISTICS" ("TASK_UUID"); - - -CREATE TABLE "CE_TASK_INPUT" ( - "TASK_UUID" VARCHAR(40) NOT NULL, - "INPUT_DATA" BLOB, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_CE_TASK_INPUT" PRIMARY KEY ("TASK_UUID") -); - - -CREATE TABLE "CE_SCANNER_CONTEXT" ( - "TASK_UUID" VARCHAR(40) NOT NULL, - "CONTEXT_DATA" BLOB NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_CE_SCANNER_CONTEXT" PRIMARY KEY ("TASK_UUID") -); - -CREATE TABLE "CE_TASK_MESSAGE" ( - "UUID" VARCHAR(40) NOT NULL, - "TASK_UUID" VARCHAR(40) NOT NULL, - "MESSAGE" VARCHAR(4000) NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - - CONSTRAINT "CE_TASK_MESSAGE" PRIMARY KEY ("UUID") -); -CREATE INDEX "CE_TASK_MESSAGE_TASK" ON "CE_TASK_MESSAGE" ("TASK_UUID"); - - -CREATE TABLE "USER_TOKENS" ( - "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), - "USER_UUID" VARCHAR(255) NOT NULL, - "NAME" VARCHAR(100) NOT NULL, - "TOKEN_HASH" VARCHAR(255) NOT NULL, - "LAST_CONNECTION_DATE" BIGINT, - "CREATED_AT" BIGINT NOT NULL -); -CREATE UNIQUE INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS" ("TOKEN_HASH"); -CREATE UNIQUE INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS" ("USER_UUID", "NAME"); - - -CREATE TABLE "ES_QUEUE" ( - "UUID" VARCHAR(40) NOT NULL, - "DOC_TYPE" VARCHAR(40) NOT NULL, - "DOC_ID" VARCHAR(4000) NOT NULL, - "DOC_ID_TYPE" VARCHAR(20), - "DOC_ROUTING" VARCHAR(4000), - "CREATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_ES_QUEUE" PRIMARY KEY ("UUID") -); -CREATE INDEX "ES_QUEUE_CREATED_AT" ON "ES_QUEUE" ("CREATED_AT"); - - -CREATE TABLE "PLUGINS" ( - "UUID" VARCHAR(40) NOT NULL, - "KEE" VARCHAR(200) NOT NULL, - "BASE_PLUGIN_KEY" VARCHAR(200), - "FILE_HASH" VARCHAR(200) NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_PLUGINS" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "PLUGINS_KEY" ON "PLUGINS" ("KEE"); - - -CREATE TABLE "PROJECT_BRANCHES" ( - "UUID" VARCHAR(50) NOT NULL, - "PROJECT_UUID" VARCHAR(50) NOT NULL, - "KEE" VARCHAR(255) NOT NULL, - "KEY_TYPE" VARCHAR(12) NOT NULL, - "BRANCH_TYPE" VARCHAR(12), - "MERGE_BRANCH_UUID" VARCHAR(50), - "PULL_REQUEST_BINARY" BLOB, - "MANUAL_BASELINE_ANALYSIS_UUID" VARCHAR(40), - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_PROJECT_BRANCHES" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "PROJECT_BRANCHES_KEE_KEY_TYPE" ON "PROJECT_BRANCHES" ("PROJECT_UUID", "KEE", "KEY_TYPE"); - - -CREATE TABLE "ANALYSIS_PROPERTIES" ( - "UUID" VARCHAR(40) NOT NULL, - "ANALYSIS_UUID" VARCHAR(40) NOT NULL, - "KEE" VARCHAR(512) NOT NULL, - "TEXT_VALUE" VARCHAR(4000), - "CLOB_VALUE" CLOB, - "IS_EMPTY" BOOLEAN NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_ANALYSIS_PROPERTIES" PRIMARY KEY ("UUID") -); -CREATE INDEX "ANALYSIS_PROPERTIES_ANALYSIS" ON "ANALYSIS_PROPERTIES" ("ANALYSIS_UUID"); - - -CREATE TABLE "WEBHOOKS" ( - "UUID" VARCHAR(40) NOT NULL, - "NAME" VARCHAR(100) NOT NULL, - "URL" VARCHAR(2000) NOT NULL, - "ORGANIZATION_UUID" VARCHAR(40), - "PROJECT_UUID" VARCHAR(40), - "SECRET" VARCHAR(200), - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_WEBHOOKS" PRIMARY KEY ("UUID") -); -CREATE INDEX "ORGANIZATION_WEBHOOK" ON "WEBHOOKS" ("ORGANIZATION_UUID"); -CREATE INDEX "PROJECT_WEBHOOK" ON "WEBHOOKS" ("PROJECT_UUID"); - - -CREATE TABLE "WEBHOOK_DELIVERIES" ( - "UUID" VARCHAR(40) NOT NULL, - "WEBHOOK_UUID" VARCHAR(40) NOT NULL, - "COMPONENT_UUID" VARCHAR(40) NOT NULL, - "ANALYSIS_UUID" VARCHAR(40), - "CE_TASK_UUID" VARCHAR(40), - "NAME" VARCHAR(100) NOT NULL, - "URL" VARCHAR(2000) NOT NULL, - "SUCCESS" BOOLEAN NOT NULL, - "HTTP_STATUS" INT, - "DURATION_MS" INT NOT NULL, - "PAYLOAD" CLOB NOT NULL, - "ERROR_STACKTRACE" CLOB, - "CREATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_WEBHOOK_DELIVERIES" PRIMARY KEY ("UUID") -); -CREATE INDEX "COMPONENT_UUID" ON "WEBHOOK_DELIVERIES" ("COMPONENT_UUID"); -CREATE INDEX "CE_TASK_UUID" ON "WEBHOOK_DELIVERIES" ("CE_TASK_UUID"); -CREATE INDEX "ANALYSIS_UUID" ON "WEBHOOK_DELIVERIES" ("ANALYSIS_UUID"); - - -CREATE TABLE "ALM_APP_INSTALLS" ( - "UUID" VARCHAR(40) NOT NULL, - "ALM_ID" VARCHAR(40) NOT NULL, - "OWNER_ID" VARCHAR(4000) NOT NULL, - "IS_OWNER_USER" BOOLEAN NOT NULL, - "INSTALL_ID" VARCHAR(4000) NOT NULL, - "USER_EXTERNAL_ID" VARCHAR(255), - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_ALM_APP_INSTALLS" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "ALM_APP_INSTALLS_OWNER" ON "ALM_APP_INSTALLS" ("ALM_ID", "OWNER_ID"); -CREATE UNIQUE INDEX "ALM_APP_INSTALLS_INSTALL" ON "ALM_APP_INSTALLS" ("ALM_ID", "INSTALL_ID"); -CREATE INDEX "ALM_APP_INSTALLS_EXTERNAL_ID" ON "ALM_APP_INSTALLS" ("USER_EXTERNAL_ID"); - -CREATE TABLE "PROJECT_ALM_BINDINGS" ( - "UUID" VARCHAR(40) NOT NULL, - "ALM_ID" VARCHAR(40) NOT NULL, - "REPO_ID" VARCHAR(256) NOT NULL, - "PROJECT_UUID" VARCHAR(40) NOT NULL, - "GITHUB_SLUG" VARCHAR(256) NULL, - "URL" VARCHAR(2000) NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - CONSTRAINT "PK_PROJECT_ALM_BINDINGS" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "PROJECT_ALM_BINDINGS_ALM_REPO" ON "PROJECT_ALM_BINDINGS" ("ALM_ID", "REPO_ID"); -CREATE UNIQUE INDEX "PROJECT_ALM_BINDINGS_PROJECT" ON "PROJECT_ALM_BINDINGS" ("PROJECT_UUID"); - -CREATE TABLE "PROJECT_MAPPINGS" ( - "UUID" VARCHAR(40) NOT NULL, - "KEY_TYPE" VARCHAR(200) NOT NULL, - "KEE" VARCHAR(4000) NOT NULL, - "PROJECT_UUID" VARCHAR(40) NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - CONSTRAINT "PK_PROJECT_MAPPINGS" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "KEY_TYPE_KEE" ON "PROJECT_MAPPINGS" ("KEY_TYPE", "KEE"); -CREATE INDEX "PROJECT_UUID" ON "PROJECT_MAPPINGS" ("PROJECT_UUID"); - -CREATE TABLE "ORGANIZATION_ALM_BINDINGS" ( - "UUID" VARCHAR(40) NOT NULL, - "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "ALM_APP_INSTALL_UUID" VARCHAR(40) NOT NULL, - "ALM_ID" VARCHAR(40) NOT NULL, - "URL" VARCHAR(2000) NOT NULL, - "USER_UUID" VARCHAR(255) NOT NULL, - "MEMBERS_SYNC_ENABLED" BOOLEAN, - "CREATED_AT" BIGINT NOT NULL, - CONSTRAINT "PK_ORGANIZATION_ALM_BINDINGS" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "ORG_ALM_BINDINGS_ORG" ON "ORGANIZATION_ALM_BINDINGS" ("ORGANIZATION_UUID"); -CREATE UNIQUE INDEX "ORG_ALM_BINDINGS_INSTALL" ON "ORGANIZATION_ALM_BINDINGS" ("ALM_APP_INSTALL_UUID"); - -CREATE TABLE "USER_PROPERTIES" ( - "UUID" VARCHAR(40) NOT NULL, - "USER_UUID" VARCHAR(255) NOT NULL, - "KEE" VARCHAR(100) NOT NULL, - "TEXT_VALUE" VARCHAR(4000) NOT NULL, - "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL, - - CONSTRAINT "PK_USER_PROPERTIES" PRIMARY KEY ("UUID") -); -CREATE UNIQUE INDEX "USER_PROPERTIES_USER_UUID_KEE" ON "USER_PROPERTIES" ("USER_UUID", "KEE"); diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/schema_migrations-h2.ddl b/server/sonar-db-dao/src/main/resources/org/sonar/db/schema_migrations-h2.ddl deleted file mode 100644 index aad2a0750a7..00000000000 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/schema_migrations-h2.ddl +++ /dev/null @@ -1,4 +0,0 @@ -CREATE TABLE "SCHEMA_MIGRATIONS" ( - "VERSION" VARCHAR(256) NOT NULL -); -CREATE INDEX "UNIQUE_SCHEMA_MIGRATIONS" ON "SCHEMA_MIGRATIONS" ("VERSION"); diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl new file mode 100644 index 00000000000..0c1d1f69281 --- /dev/null +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -0,0 +1,947 @@ +############################################################### +#### Description of SonarQube's schema in H2 SQL syntax #### +#### #### +#### This file is autogenerated and stored in SCM to #### +#### conveniently read the SonarQube's schema at any #### +#### point in time. #### +#### #### +#### DO NOT MODIFY THIS FILE DIRECTLY #### +#### use gradle task :server:sonar-db-dao:dumpSchema #### +############################################################### + +CREATE TABLE SCHEMA_MIGRATIONS( + VERSION VARCHAR(255) NOT NULL +); + +CREATE TABLE ACTIVE_RULE_PARAMETERS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + ACTIVE_RULE_ID INTEGER NOT NULL, + RULES_PARAMETER_ID INTEGER NOT NULL, + VALUE VARCHAR(4000), + RULES_PARAMETER_KEY VARCHAR(128) +); +ALTER TABLE ACTIVE_RULE_PARAMETERS ADD CONSTRAINT PK_ACTIVE_RULE_PARAMETERS PRIMARY KEY(ID); +CREATE INDEX IX_ARP_ON_ACTIVE_RULE_ID ON ACTIVE_RULE_PARAMETERS(ACTIVE_RULE_ID); + +CREATE TABLE ACTIVE_RULES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + PROFILE_ID INTEGER NOT NULL, + RULE_ID INTEGER NOT NULL, + FAILURE_LEVEL INTEGER NOT NULL, + INHERITANCE VARCHAR(10), + CREATED_AT BIGINT, + UPDATED_AT BIGINT +); +ALTER TABLE ACTIVE_RULES ADD CONSTRAINT PK_ACTIVE_RULES PRIMARY KEY(ID); +CREATE UNIQUE INDEX UNIQ_PROFILE_RULE_IDS ON ACTIVE_RULES(PROFILE_ID, RULE_ID); + +CREATE TABLE ALM_APP_INSTALLS( + UUID VARCHAR(40) NOT NULL, + ALM_ID VARCHAR(40) NOT NULL, + OWNER_ID VARCHAR(4000) NOT NULL, + INSTALL_ID VARCHAR(4000) NOT NULL, + IS_OWNER_USER BOOLEAN NOT NULL, + USER_EXTERNAL_ID VARCHAR(255), + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE ALM_APP_INSTALLS ADD CONSTRAINT PK_ALM_APP_INSTALLS PRIMARY KEY(UUID); +CREATE UNIQUE INDEX ALM_APP_INSTALLS_OWNER ON ALM_APP_INSTALLS(ALM_ID, OWNER_ID); +CREATE UNIQUE INDEX ALM_APP_INSTALLS_INSTALL ON ALM_APP_INSTALLS(ALM_ID, INSTALL_ID); +CREATE INDEX ALM_APP_INSTALLS_EXTERNAL_ID ON ALM_APP_INSTALLS(USER_EXTERNAL_ID); + +CREATE TABLE ANALYSIS_PROPERTIES( + UUID VARCHAR(40) NOT NULL, + ANALYSIS_UUID VARCHAR(40) NOT NULL, + KEE VARCHAR(512) NOT NULL, + TEXT_VALUE VARCHAR(4000), + CLOB_VALUE CLOB(2147483647), + IS_EMPTY BOOLEAN NOT NULL, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE ANALYSIS_PROPERTIES ADD CONSTRAINT PK_ANALYSIS_PROPERTIES PRIMARY KEY(UUID); +CREATE INDEX ANALYSIS_PROPERTIES_ANALYSIS ON ANALYSIS_PROPERTIES(ANALYSIS_UUID); + +CREATE TABLE CE_ACTIVITY( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + UUID VARCHAR(40) NOT NULL, + TASK_TYPE VARCHAR(15) NOT NULL, + MAIN_COMPONENT_UUID VARCHAR(40), + COMPONENT_UUID VARCHAR(40), + STATUS VARCHAR(15) NOT NULL, + MAIN_IS_LAST BOOLEAN NOT NULL, + MAIN_IS_LAST_KEY VARCHAR(55) NOT NULL, + IS_LAST BOOLEAN NOT NULL, + IS_LAST_KEY VARCHAR(55) NOT NULL, + SUBMITTER_UUID VARCHAR(255), + SUBMITTED_AT BIGINT NOT NULL, + STARTED_AT BIGINT, + EXECUTED_AT BIGINT, + EXECUTION_COUNT INTEGER NOT NULL, + EXECUTION_TIME_MS BIGINT, + ANALYSIS_UUID VARCHAR(50), + ERROR_MESSAGE VARCHAR(1000), + ERROR_STACKTRACE CLOB(2147483647), + ERROR_TYPE VARCHAR(20), + WORKER_UUID VARCHAR(40), + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE CE_ACTIVITY ADD CONSTRAINT PK_CE_ACTIVITY PRIMARY KEY(ID); +CREATE INDEX CE_ACTIVITY_COMPONENT ON CE_ACTIVITY(COMPONENT_UUID); +CREATE INDEX CE_ACTIVITY_ISLAST ON CE_ACTIVITY(IS_LAST, STATUS); +CREATE INDEX CE_ACTIVITY_ISLAST_KEY ON CE_ACTIVITY(IS_LAST_KEY); +CREATE INDEX CE_ACTIVITY_MAIN_COMPONENT ON CE_ACTIVITY(MAIN_COMPONENT_UUID); +CREATE INDEX CE_ACTIVITY_MAIN_ISLAST ON CE_ACTIVITY(MAIN_IS_LAST, STATUS); +CREATE INDEX CE_ACTIVITY_MAIN_ISLAST_KEY ON CE_ACTIVITY(MAIN_IS_LAST_KEY); +CREATE UNIQUE INDEX CE_ACTIVITY_UUID ON CE_ACTIVITY(UUID); + +CREATE TABLE CE_QUEUE( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + UUID VARCHAR(40) NOT NULL, + TASK_TYPE VARCHAR(15) NOT NULL, + MAIN_COMPONENT_UUID VARCHAR(40), + COMPONENT_UUID VARCHAR(40), + STATUS VARCHAR(15), + SUBMITTER_UUID VARCHAR(255), + STARTED_AT BIGINT, + WORKER_UUID VARCHAR(40), + EXECUTION_COUNT INTEGER NOT NULL, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE CE_QUEUE ADD CONSTRAINT PK_CE_QUEUE PRIMARY KEY(ID); +CREATE INDEX CE_QUEUE_MAIN_COMPONENT ON CE_QUEUE(MAIN_COMPONENT_UUID); +CREATE INDEX CE_QUEUE_COMPONENT ON CE_QUEUE(COMPONENT_UUID); +CREATE UNIQUE INDEX CE_QUEUE_UUID ON CE_QUEUE(UUID); + +CREATE TABLE CE_SCANNER_CONTEXT( + TASK_UUID VARCHAR(40) NOT NULL, + CONTEXT_DATA BLOB NOT NULL, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE CE_SCANNER_CONTEXT ADD CONSTRAINT PK_CE_SCANNER_CONTEXT PRIMARY KEY(TASK_UUID); + +CREATE TABLE CE_TASK_CHARACTERISTICS( + UUID VARCHAR(40) NOT NULL, + TASK_UUID VARCHAR(40) NOT NULL, + KEE VARCHAR(512) NOT NULL, + TEXT_VALUE VARCHAR(512) +); +ALTER TABLE CE_TASK_CHARACTERISTICS ADD CONSTRAINT PK_CE_TASK_CHARACTERISTICS PRIMARY KEY(UUID); +CREATE INDEX CE_CHARACTERISTICS_TASK_UUID ON CE_TASK_CHARACTERISTICS(TASK_UUID); + +CREATE TABLE CE_TASK_INPUT( + TASK_UUID VARCHAR(40) NOT NULL, + INPUT_DATA BLOB, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE CE_TASK_INPUT ADD CONSTRAINT PK_CE_TASK_INPUT PRIMARY KEY(TASK_UUID); + +CREATE TABLE CE_TASK_MESSAGE( + UUID VARCHAR(40) NOT NULL, + TASK_UUID VARCHAR(40) NOT NULL, + MESSAGE VARCHAR(4000) NOT NULL, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE CE_TASK_MESSAGE ADD CONSTRAINT PK_CE_TASK_MESSAGE PRIMARY KEY(UUID); +CREATE INDEX CE_TASK_MESSAGE_TASK ON CE_TASK_MESSAGE(TASK_UUID); + +CREATE TABLE DEFAULT_QPROFILES( + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + LANGUAGE VARCHAR(20) NOT NULL, + QPROFILE_UUID VARCHAR(255) NOT NULL, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE DEFAULT_QPROFILES ADD CONSTRAINT PK_DEFAULT_QPROFILES PRIMARY KEY(ORGANIZATION_UUID, LANGUAGE); +CREATE UNIQUE INDEX UNIQ_DEFAULT_QPROFILES_UUID ON DEFAULT_QPROFILES(QPROFILE_UUID); + +CREATE TABLE DEPRECATED_RULE_KEYS( + UUID VARCHAR(40) NOT NULL, + RULE_ID INTEGER NOT NULL, + OLD_REPOSITORY_KEY VARCHAR(255) NOT NULL, + OLD_RULE_KEY VARCHAR(200) NOT NULL, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE DEPRECATED_RULE_KEYS ADD CONSTRAINT PK_DEPRECATED_RULE_KEYS PRIMARY KEY(UUID); +CREATE UNIQUE INDEX UNIQ_DEPRECATED_RULE_KEYS ON DEPRECATED_RULE_KEYS(OLD_REPOSITORY_KEY, OLD_RULE_KEY); +CREATE INDEX RULE_ID_DEPRECATED_RULE_KEYS ON DEPRECATED_RULE_KEYS(RULE_ID); + +CREATE TABLE DUPLICATIONS_INDEX( + ID BIGINT NOT NULL AUTO_INCREMENT (1,1), + ANALYSIS_UUID VARCHAR(50) NOT NULL, + COMPONENT_UUID VARCHAR(50) NOT NULL, + HASH VARCHAR(50) NOT NULL, + INDEX_IN_FILE INTEGER NOT NULL, + START_LINE INTEGER NOT NULL, + END_LINE INTEGER NOT NULL +); +ALTER TABLE DUPLICATIONS_INDEX ADD CONSTRAINT PK_DUPLICATIONS_INDEX PRIMARY KEY(ID); +CREATE INDEX DUPLICATIONS_INDEX_HASH ON DUPLICATIONS_INDEX(HASH); +CREATE INDEX DUPLICATION_ANALYSIS_COMPONENT ON DUPLICATIONS_INDEX(ANALYSIS_UUID, COMPONENT_UUID); + +CREATE TABLE ES_QUEUE( + UUID VARCHAR(40) NOT NULL, + DOC_TYPE VARCHAR(40) NOT NULL, + DOC_ID VARCHAR(4000) NOT NULL, + DOC_ID_TYPE VARCHAR(20), + DOC_ROUTING VARCHAR(4000), + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE ES_QUEUE ADD CONSTRAINT PK_ES_QUEUE PRIMARY KEY(UUID); +CREATE INDEX ES_QUEUE_CREATED_AT ON ES_QUEUE(CREATED_AT); + +CREATE TABLE EVENT_COMPONENT_CHANGES( + UUID VARCHAR(40) NOT NULL, + EVENT_UUID VARCHAR(40) NOT NULL, + EVENT_COMPONENT_UUID VARCHAR(50) NOT NULL, + EVENT_ANALYSIS_UUID VARCHAR(50) NOT NULL, + CHANGE_CATEGORY VARCHAR(12) NOT NULL, + COMPONENT_UUID VARCHAR(50) NOT NULL, + COMPONENT_KEY VARCHAR(400) NOT NULL, + COMPONENT_NAME VARCHAR(2000) NOT NULL, + COMPONENT_BRANCH_KEY VARCHAR(255), + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE EVENT_COMPONENT_CHANGES ADD CONSTRAINT PK_EVENT_COMPONENT_CHANGES PRIMARY KEY(UUID); +CREATE UNIQUE INDEX EVENT_COMPONENT_CHANGES_UNIQUE ON EVENT_COMPONENT_CHANGES(EVENT_UUID, CHANGE_CATEGORY, COMPONENT_UUID); +CREATE INDEX EVENT_CPNT_CHANGES_CPNT ON EVENT_COMPONENT_CHANGES(EVENT_COMPONENT_UUID); +CREATE INDEX EVENT_CPNT_CHANGES_ANALYSIS ON EVENT_COMPONENT_CHANGES(EVENT_ANALYSIS_UUID); + +CREATE TABLE EVENTS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + UUID VARCHAR(40) NOT NULL, + ANALYSIS_UUID VARCHAR(50) NOT NULL, + NAME VARCHAR(400), + CATEGORY VARCHAR(50), + DESCRIPTION VARCHAR(4000), + EVENT_DATA VARCHAR(4000), + EVENT_DATE BIGINT NOT NULL, + CREATED_AT BIGINT NOT NULL, + COMPONENT_UUID VARCHAR(50) NOT NULL +); +ALTER TABLE EVENTS ADD CONSTRAINT PK_EVENTS PRIMARY KEY(ID); +CREATE UNIQUE INDEX EVENTS_UUID ON EVENTS(UUID); +CREATE INDEX EVENTS_ANALYSIS ON EVENTS(ANALYSIS_UUID); +CREATE INDEX EVENTS_COMPONENT_UUID ON EVENTS(COMPONENT_UUID); + +CREATE TABLE FILE_SOURCES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + PROJECT_UUID VARCHAR(50) NOT NULL, + FILE_UUID VARCHAR(50) NOT NULL, + LINE_HASHES CLOB(2147483647), + LINE_HASHES_VERSION INTEGER, + DATA_HASH VARCHAR(50), + SRC_HASH VARCHAR(50), + REVISION VARCHAR(100), + LINE_COUNT INTEGER NOT NULL, + BINARY_DATA BLOB, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE FILE_SOURCES ADD CONSTRAINT PK_FILE_SOURCES PRIMARY KEY(ID); +CREATE UNIQUE INDEX FILE_SOURCES_FILE_UUID ON FILE_SOURCES(FILE_UUID); +CREATE INDEX FILE_SOURCES_PROJECT_UUID ON FILE_SOURCES(PROJECT_UUID); +CREATE INDEX FILE_SOURCES_UPDATED_AT ON FILE_SOURCES(UPDATED_AT); + +CREATE TABLE GROUP_ROLES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + GROUP_ID INTEGER, + RESOURCE_ID INTEGER, + ROLE VARCHAR(64) NOT NULL +); +ALTER TABLE GROUP_ROLES ADD CONSTRAINT PK_GROUP_ROLES PRIMARY KEY(ID); +CREATE UNIQUE INDEX UNIQ_GROUP_ROLES ON GROUP_ROLES(ORGANIZATION_UUID, GROUP_ID, RESOURCE_ID, ROLE); +CREATE INDEX GROUP_ROLES_RESOURCE ON GROUP_ROLES(RESOURCE_ID); + +CREATE TABLE GROUPS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + NAME VARCHAR(500), + DESCRIPTION VARCHAR(200), + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP +); +ALTER TABLE GROUPS ADD CONSTRAINT PK_GROUPS PRIMARY KEY(ID); + +CREATE TABLE GROUPS_USERS( + USER_ID BIGINT, + GROUP_ID BIGINT +); +CREATE INDEX INDEX_GROUPS_USERS_ON_USER_ID ON GROUPS_USERS(USER_ID); +CREATE INDEX INDEX_GROUPS_USERS_ON_GROUP_ID ON GROUPS_USERS(GROUP_ID); +CREATE UNIQUE INDEX GROUPS_USERS_UNIQUE ON GROUPS_USERS(GROUP_ID, USER_ID); + +CREATE TABLE INTERNAL_COMPONENT_PROPS( + UUID VARCHAR(40) NOT NULL, + COMPONENT_UUID VARCHAR(50) NOT NULL, + KEE VARCHAR(512) NOT NULL, + VALUE VARCHAR(4000), + UPDATED_AT BIGINT NOT NULL, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE INTERNAL_COMPONENT_PROPS ADD CONSTRAINT PK_INTERNAL_COMPONENT_PROPS PRIMARY KEY(UUID); +CREATE UNIQUE INDEX UNIQUE_COMPONENT_UUID_KEE ON INTERNAL_COMPONENT_PROPS(COMPONENT_UUID, KEE); + +CREATE TABLE INTERNAL_PROPERTIES( + KEE VARCHAR(20) NOT NULL, + IS_EMPTY BOOLEAN NOT NULL, + TEXT_VALUE VARCHAR(4000), + CLOB_VALUE CLOB(2147483647), + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE INTERNAL_PROPERTIES ADD CONSTRAINT PK_INTERNAL_PROPERTIES PRIMARY KEY(KEE); + +CREATE TABLE ISSUE_CHANGES( + ID BIGINT NOT NULL AUTO_INCREMENT (1,1), + KEE VARCHAR(50), + ISSUE_KEY VARCHAR(50) NOT NULL, + USER_LOGIN VARCHAR(255), + CHANGE_TYPE VARCHAR(20), + CHANGE_DATA CLOB(2147483647), + CREATED_AT BIGINT, + UPDATED_AT BIGINT, + ISSUE_CHANGE_CREATION_DATE BIGINT +); +ALTER TABLE ISSUE_CHANGES ADD CONSTRAINT PK_ISSUE_CHANGES PRIMARY KEY(ID); +CREATE INDEX ISSUE_CHANGES_ISSUE_KEY ON ISSUE_CHANGES(ISSUE_KEY); +CREATE INDEX ISSUE_CHANGES_KEE ON ISSUE_CHANGES(KEE); + +CREATE TABLE ISSUES( + ID BIGINT NOT NULL AUTO_INCREMENT (1,1), + KEE VARCHAR(50) NOT NULL, + RULE_ID INTEGER, + SEVERITY VARCHAR(10), + MANUAL_SEVERITY BOOLEAN NOT NULL, + MESSAGE VARCHAR(4000), + LINE INTEGER, + GAP DOUBLE, + STATUS VARCHAR(20), + RESOLUTION VARCHAR(20), + CHECKSUM VARCHAR(1000), + REPORTER VARCHAR(255), + ASSIGNEE VARCHAR(255), + AUTHOR_LOGIN VARCHAR(255), + ACTION_PLAN_KEY VARCHAR(50), + ISSUE_ATTRIBUTES VARCHAR(4000), + EFFORT INTEGER, + CREATED_AT BIGINT, + UPDATED_AT BIGINT, + ISSUE_CREATION_DATE BIGINT, + ISSUE_UPDATE_DATE BIGINT, + ISSUE_CLOSE_DATE BIGINT, + TAGS VARCHAR(4000), + COMPONENT_UUID VARCHAR(50), + PROJECT_UUID VARCHAR(50), + LOCATIONS BLOB, + ISSUE_TYPE TINYINT, + FROM_HOTSPOT BOOLEAN +); +ALTER TABLE ISSUES ADD CONSTRAINT PK_ISSUES PRIMARY KEY(ID); +CREATE INDEX ISSUES_ASSIGNEE ON ISSUES(ASSIGNEE); +CREATE INDEX ISSUES_COMPONENT_UUID ON ISSUES(COMPONENT_UUID); +CREATE INDEX ISSUES_CREATION_DATE ON ISSUES(ISSUE_CREATION_DATE); +CREATE UNIQUE INDEX ISSUES_KEE ON ISSUES(KEE); +CREATE INDEX ISSUES_PROJECT_UUID ON ISSUES(PROJECT_UUID); +CREATE INDEX ISSUES_RESOLUTION ON ISSUES(RESOLUTION); +CREATE INDEX ISSUES_RULE_ID ON ISSUES(RULE_ID); +CREATE INDEX ISSUES_UPDATED_AT ON ISSUES(UPDATED_AT); + +CREATE TABLE LIVE_MEASURES( + UUID VARCHAR(40) NOT NULL, + PROJECT_UUID VARCHAR(50) NOT NULL, + COMPONENT_UUID VARCHAR(50) NOT NULL, + METRIC_ID INTEGER NOT NULL, + VALUE DOUBLE, + TEXT_VALUE VARCHAR(4000), + VARIATION DOUBLE, + MEASURE_DATA BLOB, + UPDATE_MARKER VARCHAR(40), + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE LIVE_MEASURES ADD CONSTRAINT PK_LIVE_MEASURES PRIMARY KEY(UUID); +CREATE INDEX LIVE_MEASURES_PROJECT ON LIVE_MEASURES(PROJECT_UUID); +CREATE UNIQUE INDEX LIVE_MEASURES_COMPONENT ON LIVE_MEASURES(COMPONENT_UUID, METRIC_ID); + +CREATE TABLE MANUAL_MEASURES( + ID BIGINT NOT NULL AUTO_INCREMENT (1,1), + METRIC_ID INTEGER NOT NULL, + VALUE DOUBLE, + TEXT_VALUE VARCHAR(4000), + USER_UUID VARCHAR(255), + DESCRIPTION VARCHAR(4000), + CREATED_AT BIGINT, + UPDATED_AT BIGINT, + COMPONENT_UUID VARCHAR(50) NOT NULL +); +ALTER TABLE MANUAL_MEASURES ADD CONSTRAINT PK_MANUAL_MEASURES PRIMARY KEY(ID); +CREATE INDEX MANUAL_MEASURES_COMPONENT_UUID ON MANUAL_MEASURES(COMPONENT_UUID); + +CREATE TABLE METRICS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + NAME VARCHAR(64) NOT NULL, + DESCRIPTION VARCHAR(255), + DIRECTION INTEGER DEFAULT 0 NOT NULL, + DOMAIN VARCHAR(64), + SHORT_NAME VARCHAR(64), + QUALITATIVE BOOLEAN DEFAULT FALSE NOT NULL, + VAL_TYPE VARCHAR(8), + USER_MANAGED BOOLEAN DEFAULT FALSE, + ENABLED BOOLEAN DEFAULT TRUE, + WORST_VALUE DOUBLE, + BEST_VALUE DOUBLE, + OPTIMIZED_BEST_VALUE BOOLEAN, + HIDDEN BOOLEAN, + DELETE_HISTORICAL_DATA BOOLEAN, + DECIMAL_SCALE INTEGER +); +ALTER TABLE METRICS ADD CONSTRAINT PK_METRICS PRIMARY KEY(ID); +CREATE UNIQUE INDEX METRICS_UNIQUE_NAME ON METRICS(NAME); + +CREATE TABLE NOTIFICATIONS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + DATA BLOB +); +ALTER TABLE NOTIFICATIONS ADD CONSTRAINT PK_NOTIFICATIONS PRIMARY KEY(ID); + +CREATE TABLE ORG_QPROFILES( + UUID VARCHAR(255) NOT NULL, + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + RULES_PROFILE_UUID VARCHAR(255) NOT NULL, + PARENT_UUID VARCHAR(255), + LAST_USED BIGINT, + USER_UPDATED_AT BIGINT, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE ORG_QPROFILES ADD CONSTRAINT PK_ORG_QPROFILES PRIMARY KEY(UUID); +CREATE INDEX QPROFILES_ORG_UUID ON ORG_QPROFILES(ORGANIZATION_UUID); +CREATE INDEX QPROFILES_RP_UUID ON ORG_QPROFILES(RULES_PROFILE_UUID); +CREATE INDEX ORG_QPROFILES_PARENT_UUID ON ORG_QPROFILES(PARENT_UUID); + +CREATE TABLE ORG_QUALITY_GATES( + UUID VARCHAR(40) NOT NULL, + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + QUALITY_GATE_UUID VARCHAR(40) NOT NULL +); +ALTER TABLE ORG_QUALITY_GATES ADD CONSTRAINT PK_ORG_QUALITY_GATES PRIMARY KEY(UUID); +CREATE UNIQUE INDEX UNIQ_ORG_QUALITY_GATES ON ORG_QUALITY_GATES(ORGANIZATION_UUID, QUALITY_GATE_UUID); + +CREATE TABLE ORGANIZATION_ALM_BINDINGS( + UUID VARCHAR(40) NOT NULL, + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + ALM_APP_INSTALL_UUID VARCHAR(40) NOT NULL, + ALM_ID VARCHAR(40) NOT NULL, + URL VARCHAR(2000) NOT NULL, + USER_UUID VARCHAR(255) NOT NULL, + MEMBERS_SYNC_ENABLED BOOLEAN, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE ORGANIZATION_ALM_BINDINGS ADD CONSTRAINT PK_ORGANIZATION_ALM_BINDINGS PRIMARY KEY(UUID); +CREATE UNIQUE INDEX ORG_ALM_BINDINGS_ORG ON ORGANIZATION_ALM_BINDINGS(ORGANIZATION_UUID); +CREATE UNIQUE INDEX ORG_ALM_BINDINGS_INSTALL ON ORGANIZATION_ALM_BINDINGS(ALM_APP_INSTALL_UUID); + +CREATE TABLE ORGANIZATION_MEMBERS( + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + USER_ID INTEGER NOT NULL +); +ALTER TABLE ORGANIZATION_MEMBERS ADD CONSTRAINT PK_ORGANIZATION_MEMBERS PRIMARY KEY(ORGANIZATION_UUID, USER_ID); +CREATE INDEX IX_ORG_MEMBERS_ON_USER_ID ON ORGANIZATION_MEMBERS(USER_ID); + +CREATE TABLE ORGANIZATIONS( + UUID VARCHAR(40) NOT NULL, + KEE VARCHAR(255) NOT NULL, + NAME VARCHAR(255) NOT NULL, + DESCRIPTION VARCHAR(256), + URL VARCHAR(256), + AVATAR_URL VARCHAR(256), + GUARDED BOOLEAN, + DEFAULT_GROUP_ID INTEGER, + DEFAULT_QUALITY_GATE_UUID VARCHAR(40) NOT NULL, + DEFAULT_PERM_TEMPLATE_PROJECT VARCHAR(40), + DEFAULT_PERM_TEMPLATE_APP VARCHAR(40), + DEFAULT_PERM_TEMPLATE_PORT VARCHAR(40), + NEW_PROJECT_PRIVATE BOOLEAN NOT NULL, + SUBSCRIPTION VARCHAR(40) NOT NULL, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE ORGANIZATIONS ADD CONSTRAINT PK_ORGANIZATIONS PRIMARY KEY(UUID); +CREATE UNIQUE INDEX ORGANIZATION_KEY ON ORGANIZATIONS(KEE); + +CREATE TABLE PERM_TEMPLATES_GROUPS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + GROUP_ID INTEGER, + TEMPLATE_ID INTEGER NOT NULL, + PERMISSION_REFERENCE VARCHAR(64) NOT NULL, + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP +); +ALTER TABLE PERM_TEMPLATES_GROUPS ADD CONSTRAINT PK_PERM_TEMPLATES_GROUPS PRIMARY KEY(ID); + +CREATE TABLE PERM_TEMPLATES_USERS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + USER_ID INTEGER NOT NULL, + TEMPLATE_ID INTEGER NOT NULL, + PERMISSION_REFERENCE VARCHAR(64) NOT NULL, + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP +); +ALTER TABLE PERM_TEMPLATES_USERS ADD CONSTRAINT PK_PERM_TEMPLATES_USERS PRIMARY KEY(ID); + +CREATE TABLE PERM_TPL_CHARACTERISTICS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + TEMPLATE_ID INTEGER NOT NULL, + PERMISSION_KEY VARCHAR(64) NOT NULL, + WITH_PROJECT_CREATOR BOOLEAN DEFAULT FALSE NOT NULL, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE PERM_TPL_CHARACTERISTICS ADD CONSTRAINT PK_PERM_TPL_CHARACTERISTICS PRIMARY KEY(ID); +CREATE UNIQUE INDEX UNIQ_PERM_TPL_CHARAC ON PERM_TPL_CHARACTERISTICS(TEMPLATE_ID, PERMISSION_KEY); + +CREATE TABLE PERMISSION_TEMPLATES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + NAME VARCHAR(100) NOT NULL, + KEE VARCHAR(100) NOT NULL, + DESCRIPTION VARCHAR(4000), + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP, + KEY_PATTERN VARCHAR(500) +); +ALTER TABLE PERMISSION_TEMPLATES ADD CONSTRAINT PK_PERMISSION_TEMPLATES PRIMARY KEY(ID); + +CREATE TABLE PLUGINS( + UUID VARCHAR(40) NOT NULL, + KEE VARCHAR(200) NOT NULL, + BASE_PLUGIN_KEY VARCHAR(200), + FILE_HASH VARCHAR(200) NOT NULL, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE PLUGINS ADD CONSTRAINT PK_PLUGINS PRIMARY KEY(UUID); +CREATE UNIQUE INDEX PLUGINS_KEY ON PLUGINS(KEE); + +CREATE TABLE PROJECT_ALM_BINDINGS( + UUID VARCHAR(40) NOT NULL, + ALM_ID VARCHAR(40) NOT NULL, + REPO_ID VARCHAR(256) NOT NULL, + PROJECT_UUID VARCHAR(40) NOT NULL, + GITHUB_SLUG VARCHAR(256), + URL VARCHAR(2000) NOT NULL, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE PROJECT_ALM_BINDINGS ADD CONSTRAINT PK_PROJECT_ALM_BINDINGS PRIMARY KEY(UUID); +CREATE UNIQUE INDEX PROJECT_ALM_BINDINGS_ALM_REPO ON PROJECT_ALM_BINDINGS(ALM_ID, REPO_ID); +CREATE UNIQUE INDEX PROJECT_ALM_BINDINGS_PROJECT ON PROJECT_ALM_BINDINGS(PROJECT_UUID); + +CREATE TABLE PROJECT_BRANCHES( + UUID VARCHAR(50) NOT NULL, + PROJECT_UUID VARCHAR(50) NOT NULL, + KEE VARCHAR(255) NOT NULL, + BRANCH_TYPE VARCHAR(12), + MERGE_BRANCH_UUID VARCHAR(50), + KEY_TYPE VARCHAR(12) NOT NULL, + PULL_REQUEST_BINARY BLOB, + MANUAL_BASELINE_ANALYSIS_UUID VARCHAR(40), + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE PROJECT_BRANCHES ADD CONSTRAINT PK_PROJECT_BRANCHES PRIMARY KEY(UUID); +CREATE UNIQUE INDEX PROJECT_BRANCHES_KEE_KEY_TYPE ON PROJECT_BRANCHES(PROJECT_UUID, KEE, KEY_TYPE); + +CREATE TABLE PROJECT_LINKS( + UUID VARCHAR(40) NOT NULL, + PROJECT_UUID VARCHAR(40) NOT NULL, + LINK_TYPE VARCHAR(20) NOT NULL, + NAME VARCHAR(128), + HREF VARCHAR(2048) NOT NULL, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE PROJECT_LINKS ADD CONSTRAINT PK_PROJECT_LINKS PRIMARY KEY(UUID); +CREATE INDEX PROJECT_LINKS_PROJECT ON PROJECT_LINKS(PROJECT_UUID); + +CREATE TABLE PROJECT_MAPPINGS( + UUID VARCHAR(40) NOT NULL, + KEY_TYPE VARCHAR(200) NOT NULL, + KEE VARCHAR(4000) NOT NULL, + PROJECT_UUID VARCHAR(40) NOT NULL, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE PROJECT_MAPPINGS ADD CONSTRAINT PK_PROJECT_MAPPINGS PRIMARY KEY(UUID); +CREATE UNIQUE INDEX KEY_TYPE_KEE ON PROJECT_MAPPINGS(KEY_TYPE, KEE); +CREATE INDEX PROJECT_UUID ON PROJECT_MAPPINGS(PROJECT_UUID); + +CREATE TABLE PROJECT_MEASURES( + ID BIGINT NOT NULL AUTO_INCREMENT (1,1), + VALUE DOUBLE, + METRIC_ID INTEGER NOT NULL, + ANALYSIS_UUID VARCHAR(50) NOT NULL, + COMPONENT_UUID VARCHAR(50) NOT NULL, + TEXT_VALUE VARCHAR(4000), + ALERT_STATUS VARCHAR(5), + ALERT_TEXT VARCHAR(4000), + DESCRIPTION VARCHAR(4000), + PERSON_ID INTEGER, + VARIATION_VALUE_1 DOUBLE, + VARIATION_VALUE_2 DOUBLE, + VARIATION_VALUE_3 DOUBLE, + VARIATION_VALUE_4 DOUBLE, + VARIATION_VALUE_5 DOUBLE, + MEASURE_DATA BLOB +); +ALTER TABLE PROJECT_MEASURES ADD CONSTRAINT PK_PROJECT_MEASURES PRIMARY KEY(ID); +CREATE INDEX MEASURES_ANALYSIS_METRIC ON PROJECT_MEASURES(ANALYSIS_UUID, METRIC_ID); +CREATE INDEX MEASURES_COMPONENT_UUID ON PROJECT_MEASURES(COMPONENT_UUID); + +CREATE TABLE PROJECT_QGATES( + PROJECT_UUID VARCHAR(40) NOT NULL, + QUALITY_GATE_UUID VARCHAR(40) NOT NULL +); +ALTER TABLE PROJECT_QGATES ADD CONSTRAINT PK_PROJECT_QGATES PRIMARY KEY(PROJECT_UUID); +CREATE UNIQUE INDEX UNIQ_PROJECT_QGATES ON PROJECT_QGATES(PROJECT_UUID, QUALITY_GATE_UUID); + +CREATE TABLE PROJECT_QPROFILES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + PROJECT_UUID VARCHAR(50) NOT NULL, + PROFILE_KEY VARCHAR(50) NOT NULL +); +ALTER TABLE PROJECT_QPROFILES ADD CONSTRAINT PK_PROJECT_QPROFILES PRIMARY KEY(ID); +CREATE UNIQUE INDEX UNIQ_PROJECT_QPROFILES ON PROJECT_QPROFILES(PROJECT_UUID, PROFILE_KEY); + +CREATE TABLE PROJECTS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + UUID VARCHAR(50) NOT NULL, + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + KEE VARCHAR(400), + DEPRECATED_KEE VARCHAR(400), + NAME VARCHAR(2000), + LONG_NAME VARCHAR(2000), + DESCRIPTION VARCHAR(2000), + ENABLED BOOLEAN DEFAULT TRUE NOT NULL, + SCOPE VARCHAR(3), + QUALIFIER VARCHAR(10), + PRIVATE BOOLEAN NOT NULL, + ROOT_UUID VARCHAR(50) NOT NULL, + LANGUAGE VARCHAR(20), + COPY_COMPONENT_UUID VARCHAR(50), + DEVELOPER_UUID VARCHAR(50), + PATH VARCHAR(2000), + UUID_PATH VARCHAR(1500) NOT NULL, + PROJECT_UUID VARCHAR(50) NOT NULL, + MODULE_UUID VARCHAR(50), + MODULE_UUID_PATH VARCHAR(1500), + AUTHORIZATION_UPDATED_AT BIGINT, + TAGS VARCHAR(500), + MAIN_BRANCH_PROJECT_UUID VARCHAR(50), + B_CHANGED BOOLEAN, + B_NAME VARCHAR(500), + B_LONG_NAME VARCHAR(500), + B_DESCRIPTION VARCHAR(2000), + B_ENABLED BOOLEAN, + B_QUALIFIER VARCHAR(10), + B_LANGUAGE VARCHAR(20), + B_COPY_COMPONENT_UUID VARCHAR(50), + B_PATH VARCHAR(2000), + B_UUID_PATH VARCHAR(1500), + B_MODULE_UUID VARCHAR(50), + B_MODULE_UUID_PATH VARCHAR(1500), + CREATED_AT TIMESTAMP +); +ALTER TABLE PROJECTS ADD CONSTRAINT PK_PROJECTS PRIMARY KEY(ID); +CREATE INDEX PROJECTS_ORGANIZATION ON PROJECTS(ORGANIZATION_UUID); +CREATE UNIQUE INDEX PROJECTS_KEE ON PROJECTS(KEE); +CREATE INDEX PROJECTS_MODULE_UUID ON PROJECTS(MODULE_UUID); +CREATE INDEX PROJECTS_PROJECT_UUID ON PROJECTS(PROJECT_UUID); +CREATE INDEX PROJECTS_QUALIFIER ON PROJECTS(QUALIFIER); +CREATE INDEX PROJECTS_ROOT_UUID ON PROJECTS(ROOT_UUID); +CREATE INDEX PROJECTS_UUID ON PROJECTS(UUID); + +CREATE TABLE PROPERTIES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + PROP_KEY VARCHAR(512) NOT NULL, + RESOURCE_ID BIGINT, + USER_ID BIGINT, + IS_EMPTY BOOLEAN NOT NULL, + TEXT_VALUE VARCHAR(4000), + CLOB_VALUE CLOB(2147483647), + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE PROPERTIES ADD CONSTRAINT PK_PROPERTIES PRIMARY KEY(ID); +CREATE INDEX PROPERTIES_KEY ON PROPERTIES(PROP_KEY); + +CREATE TABLE QPROFILE_CHANGES( + KEE VARCHAR(40) NOT NULL, + RULES_PROFILE_UUID VARCHAR(255) NOT NULL, + CHANGE_TYPE VARCHAR(20) NOT NULL, + USER_UUID VARCHAR(255), + CHANGE_DATA CLOB(2147483647), + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE QPROFILE_CHANGES ADD CONSTRAINT PK_QPROFILE_CHANGES PRIMARY KEY(KEE); +CREATE INDEX QP_CHANGES_RULES_PROFILE_UUID ON QPROFILE_CHANGES(RULES_PROFILE_UUID); + +CREATE TABLE QPROFILE_EDIT_GROUPS( + UUID VARCHAR(40) NOT NULL, + GROUP_ID INTEGER NOT NULL, + QPROFILE_UUID VARCHAR(255) NOT NULL, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE QPROFILE_EDIT_GROUPS ADD CONSTRAINT PK_QPROFILE_EDIT_GROUPS PRIMARY KEY(UUID); +CREATE INDEX QPROFILE_EDIT_GROUPS_QPROFILE ON QPROFILE_EDIT_GROUPS(QPROFILE_UUID); +CREATE UNIQUE INDEX QPROFILE_EDIT_GROUPS_UNIQUE ON QPROFILE_EDIT_GROUPS(GROUP_ID, QPROFILE_UUID); + +CREATE TABLE QPROFILE_EDIT_USERS( + UUID VARCHAR(40) NOT NULL, + USER_ID INTEGER NOT NULL, + QPROFILE_UUID VARCHAR(255) NOT NULL, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE QPROFILE_EDIT_USERS ADD CONSTRAINT PK_QPROFILE_EDIT_USERS PRIMARY KEY(UUID); +CREATE INDEX QPROFILE_EDIT_USERS_QPROFILE ON QPROFILE_EDIT_USERS(QPROFILE_UUID); +CREATE UNIQUE INDEX QPROFILE_EDIT_USERS_UNIQUE ON QPROFILE_EDIT_USERS(USER_ID, QPROFILE_UUID); + +CREATE TABLE QUALITY_GATE_CONDITIONS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + QGATE_ID INTEGER, + METRIC_ID INTEGER, + PERIOD INTEGER, + OPERATOR VARCHAR(3), + VALUE_ERROR VARCHAR(64), + VALUE_WARNING VARCHAR(64), + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP +); +ALTER TABLE QUALITY_GATE_CONDITIONS ADD CONSTRAINT PK_QUALITY_GATE_CONDITIONS PRIMARY KEY(ID); + +CREATE TABLE QUALITY_GATES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + UUID VARCHAR(40) NOT NULL, + NAME VARCHAR(100) NOT NULL, + IS_BUILT_IN BOOLEAN NOT NULL, + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP +); +ALTER TABLE QUALITY_GATES ADD CONSTRAINT PK_QUALITY_GATES PRIMARY KEY(ID); +CREATE UNIQUE INDEX UNIQ_QUALITY_GATES_UUID ON QUALITY_GATES(UUID); + +CREATE TABLE RULE_REPOSITORIES( + KEE VARCHAR(200) NOT NULL, + LANGUAGE VARCHAR(20) NOT NULL, + NAME VARCHAR(4000) NOT NULL, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE RULE_REPOSITORIES ADD CONSTRAINT PK_RULE_REPOSITORIES PRIMARY KEY(KEE); + +CREATE TABLE RULES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + NAME VARCHAR(200), + PLUGIN_RULE_KEY VARCHAR(200) NOT NULL, + PLUGIN_KEY VARCHAR(200), + PLUGIN_CONFIG_KEY VARCHAR(200), + PLUGIN_NAME VARCHAR(255) NOT NULL, + SCOPE VARCHAR(20) NOT NULL, + DESCRIPTION CLOB(2147483647), + PRIORITY INTEGER, + TEMPLATE_ID INTEGER, + STATUS VARCHAR(40), + LANGUAGE VARCHAR(20), + DEF_REMEDIATION_FUNCTION VARCHAR(20), + DEF_REMEDIATION_GAP_MULT VARCHAR(20), + DEF_REMEDIATION_BASE_EFFORT VARCHAR(20), + GAP_DESCRIPTION VARCHAR(4000), + SYSTEM_TAGS VARCHAR(4000), + IS_TEMPLATE BOOLEAN DEFAULT FALSE NOT NULL, + DESCRIPTION_FORMAT VARCHAR(20), + RULE_TYPE TINYINT, + SECURITY_STANDARDS VARCHAR(4000), + IS_AD_HOC BOOLEAN NOT NULL, + IS_EXTERNAL BOOLEAN NOT NULL, + CREATED_AT BIGINT, + UPDATED_AT BIGINT +); +ALTER TABLE RULES ADD CONSTRAINT PK_RULES PRIMARY KEY(ID); +CREATE UNIQUE INDEX RULES_REPO_KEY ON RULES(PLUGIN_RULE_KEY, PLUGIN_NAME); + +CREATE TABLE RULES_METADATA( + RULE_ID INTEGER NOT NULL, + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + NOTE_DATA CLOB(2147483647), + NOTE_USER_UUID VARCHAR(255), + NOTE_CREATED_AT BIGINT, + NOTE_UPDATED_AT BIGINT, + REMEDIATION_FUNCTION VARCHAR(20), + REMEDIATION_GAP_MULT VARCHAR(20), + REMEDIATION_BASE_EFFORT VARCHAR(20), + TAGS VARCHAR(4000), + AD_HOC_NAME VARCHAR(200), + AD_HOC_DESCRIPTION CLOB(2147483647), + AD_HOC_SEVERITY VARCHAR(10), + AD_HOC_TYPE TINYINT, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE RULES_METADATA ADD CONSTRAINT PK_RULES_METADATA PRIMARY KEY(RULE_ID, ORGANIZATION_UUID); + +CREATE TABLE RULES_PARAMETERS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + RULE_ID INTEGER NOT NULL, + NAME VARCHAR(128) NOT NULL, + DESCRIPTION VARCHAR(4000), + PARAM_TYPE VARCHAR(512) NOT NULL, + DEFAULT_VALUE VARCHAR(4000) +); +ALTER TABLE RULES_PARAMETERS ADD CONSTRAINT PK_RULES_PARAMETERS PRIMARY KEY(ID); +CREATE INDEX RULES_PARAMETERS_RULE_ID ON RULES_PARAMETERS(RULE_ID); +CREATE UNIQUE INDEX RULES_PARAMETERS_UNIQUE ON RULES_PARAMETERS(RULE_ID, NAME); + +CREATE TABLE RULES_PROFILES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + NAME VARCHAR(100) NOT NULL, + LANGUAGE VARCHAR(20), + KEE VARCHAR(255) NOT NULL, + IS_BUILT_IN BOOLEAN NOT NULL, + RULES_UPDATED_AT VARCHAR(100), + CREATED_AT TIMESTAMP, + UPDATED_AT TIMESTAMP +); +ALTER TABLE RULES_PROFILES ADD CONSTRAINT PK_RULES_PROFILES PRIMARY KEY(ID); +CREATE UNIQUE INDEX UNIQ_QPROF_KEY ON RULES_PROFILES(KEE); + +CREATE TABLE SNAPSHOTS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + UUID VARCHAR(50) NOT NULL, + COMPONENT_UUID VARCHAR(50) NOT NULL, + STATUS VARCHAR(4) DEFAULT 'U' NOT NULL, + ISLAST BOOLEAN DEFAULT FALSE NOT NULL, + VERSION VARCHAR(500), + PURGE_STATUS INTEGER, + BUILD_STRING VARCHAR(100), + REVISION VARCHAR(100), + BUILD_DATE BIGINT, + PERIOD1_MODE VARCHAR(100), + PERIOD1_PARAM VARCHAR(100), + PERIOD2_MODE VARCHAR(100), + PERIOD2_PARAM VARCHAR(100), + PERIOD3_MODE VARCHAR(100), + PERIOD3_PARAM VARCHAR(100), + PERIOD4_MODE VARCHAR(100), + PERIOD4_PARAM VARCHAR(100), + PERIOD5_MODE VARCHAR(100), + PERIOD5_PARAM VARCHAR(100), + PERIOD1_DATE BIGINT, + PERIOD2_DATE BIGINT, + PERIOD3_DATE BIGINT, + PERIOD4_DATE BIGINT, + PERIOD5_DATE BIGINT, + CREATED_AT BIGINT +); +ALTER TABLE SNAPSHOTS ADD CONSTRAINT PK_SNAPSHOTS PRIMARY KEY(ID); +CREATE UNIQUE INDEX ANALYSES_UUID ON SNAPSHOTS(UUID); +CREATE INDEX SNAPSHOT_COMPONENT ON SNAPSHOTS(COMPONENT_UUID); + +CREATE TABLE USER_PROPERTIES( + UUID VARCHAR(40) NOT NULL, + USER_UUID VARCHAR(255) NOT NULL, + KEE VARCHAR(100) NOT NULL, + TEXT_VALUE VARCHAR(4000) NOT NULL, + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT NOT NULL +); +ALTER TABLE USER_PROPERTIES ADD CONSTRAINT PK_USER_PROPERTIES PRIMARY KEY(UUID); +CREATE UNIQUE INDEX USER_PROPERTIES_USER_UUID_KEE ON USER_PROPERTIES(USER_UUID, KEE); + +CREATE TABLE USER_ROLES( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + ORGANIZATION_UUID VARCHAR(40) NOT NULL, + USER_ID INTEGER, + RESOURCE_ID INTEGER, + ROLE VARCHAR(64) NOT NULL +); +ALTER TABLE USER_ROLES ADD CONSTRAINT PK_USER_ROLES PRIMARY KEY(ID); +CREATE INDEX USER_ROLES_RESOURCE ON USER_ROLES(RESOURCE_ID); +CREATE INDEX USER_ROLES_USER ON USER_ROLES(USER_ID); + +CREATE TABLE USER_TOKENS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + USER_UUID VARCHAR(255) NOT NULL, + NAME VARCHAR(100) NOT NULL, + TOKEN_HASH VARCHAR(255) NOT NULL, + LAST_CONNECTION_DATE BIGINT, + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE USER_TOKENS ADD CONSTRAINT PK_USER_TOKENS PRIMARY KEY(ID); +CREATE UNIQUE INDEX USER_TOKENS_USER_UUID_NAME ON USER_TOKENS(USER_UUID, NAME); +CREATE UNIQUE INDEX USER_TOKENS_TOKEN_HASH ON USER_TOKENS(TOKEN_HASH); + +CREATE TABLE USERS( + ID INTEGER NOT NULL AUTO_INCREMENT (1,1), + UUID VARCHAR(255) NOT NULL, + LOGIN VARCHAR(255) NOT NULL, + ORGANIZATION_UUID VARCHAR(40), + 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_LOGIN VARCHAR(255) NOT NULL, + EXTERNAL_IDENTITY_PROVIDER VARCHAR(100) NOT NULL, + EXTERNAL_ID VARCHAR(255) NOT NULL, + IS_ROOT BOOLEAN NOT NULL, + USER_LOCAL BOOLEAN, + ONBOARDED BOOLEAN NOT NULL, + HOMEPAGE_TYPE VARCHAR(40), + HOMEPAGE_PARAMETER VARCHAR(40), + LAST_CONNECTION_DATE BIGINT, + CREATED_AT BIGINT, + UPDATED_AT BIGINT +); +ALTER TABLE USERS ADD CONSTRAINT PK_USERS PRIMARY KEY(ID); +CREATE UNIQUE INDEX USERS_LOGIN ON USERS(LOGIN); +CREATE INDEX USERS_UPDATED_AT ON USERS(UPDATED_AT); +CREATE UNIQUE INDEX USERS_UUID ON USERS(UUID); +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 TABLE WEBHOOK_DELIVERIES( + UUID VARCHAR(40) NOT NULL, + WEBHOOK_UUID VARCHAR(40) NOT NULL, + COMPONENT_UUID VARCHAR(40) NOT NULL, + CE_TASK_UUID VARCHAR(40), + ANALYSIS_UUID VARCHAR(40), + NAME VARCHAR(100) NOT NULL, + URL VARCHAR(2000) NOT NULL, + SUCCESS BOOLEAN NOT NULL, + HTTP_STATUS INTEGER, + DURATION_MS BIGINT NOT NULL, + PAYLOAD CLOB(2147483647) NOT NULL, + ERROR_STACKTRACE CLOB(2147483647), + CREATED_AT BIGINT NOT NULL +); +ALTER TABLE WEBHOOK_DELIVERIES ADD CONSTRAINT PK_WEBHOOK_DELIVERIES PRIMARY KEY(UUID); +CREATE INDEX COMPONENT_UUID ON WEBHOOK_DELIVERIES(COMPONENT_UUID); +CREATE INDEX CE_TASK_UUID ON WEBHOOK_DELIVERIES(CE_TASK_UUID); + +CREATE TABLE WEBHOOKS( + UUID VARCHAR(40) NOT NULL, + ORGANIZATION_UUID VARCHAR(40), + PROJECT_UUID VARCHAR(40), + NAME VARCHAR(100) NOT NULL, + URL VARCHAR(2000) NOT NULL, + SECRET VARCHAR(200), + CREATED_AT BIGINT NOT NULL, + UPDATED_AT BIGINT +); +ALTER TABLE WEBHOOKS ADD CONSTRAINT PK_WEBHOOKS PRIMARY KEY(UUID); +CREATE INDEX ORGANIZATION_WEBHOOK ON WEBHOOKS(ORGANIZATION_UUID); +CREATE INDEX PROJECT_WEBHOOK ON WEBHOOKS(PROJECT_UUID); diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/DdlUtilsTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/DdlUtilsTest.java deleted file mode 100644 index 0423d6d46c3..00000000000 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/DdlUtilsTest.java +++ /dev/null @@ -1,79 +0,0 @@ -/* - * 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.db; - -import java.sql.Connection; -import java.sql.DriverManager; -import java.sql.ResultSet; -import java.sql.SQLException; -import java.sql.Statement; -import org.h2.Driver; -import org.junit.Test; - -import static org.assertj.core.api.Assertions.assertThat; - -public class DdlUtilsTest { - - @Test - public void shouldCreateSchema_with_schema_migrations() throws SQLException { - DriverManager.registerDriver(new Driver()); - try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:sonar_test")) { - DdlUtils.createSchema(connection, "h2", true); - - int tableCount = countTables(connection); - assertThat(tableCount).isGreaterThan(30); - - verifySchemaMigrationsNotPopulated(connection); - } - } - - @Test - public void shouldCreateSchema_without_schema_migrations() throws SQLException { - DriverManager.registerDriver(new Driver()); - try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:sonar_test2")) { - try (Statement statement = connection.createStatement()) { - statement.execute("create table schema_migrations (version varchar(255) not null)"); - } - DdlUtils.createSchema(connection, "h2", false); - - verifySchemaMigrationsNotPopulated(connection); - } - } - - static int countTables(Connection connection) throws SQLException { - int count = 0; - ResultSet resultSet = connection.getMetaData().getTables("", null, null, new String[] {"TABLE"}); - while (resultSet.next()) { - count++; - } - resultSet.close(); - return count; - } - - private void verifySchemaMigrationsNotPopulated(Connection connection) throws SQLException { - try (Statement statement = connection.createStatement(); - ResultSet resultSet = statement.executeQuery("select count(*) from schema_migrations")) { - assertThat(resultSet.next()).isTrue(); - assertThat(resultSet.getLong(1)).isEqualTo(0); - assertThat(resultSet.next()).isFalse(); - } - } - -} diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/H2Database.java b/server/sonar-db-dao/src/test/java/org/sonar/db/H2Database.java index 44bbaf93dfa..c102ff0b0f6 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/H2Database.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/H2Database.java @@ -21,58 +21,65 @@ package org.sonar.db; import java.sql.Connection; import java.sql.SQLException; +import java.util.List; import javax.sql.DataSource; -import org.apache.commons.dbcp2.BasicDataSource; import org.apache.commons.dbutils.DbUtils; +import org.sonar.api.SonarEdition; +import org.sonar.api.SonarQubeSide; +import org.sonar.api.internal.SonarRuntimeImpl; +import org.sonar.api.utils.System2; +import org.sonar.api.utils.Version; +import org.sonar.api.utils.log.Loggers; +import org.sonar.core.platform.ComponentContainer; +import org.sonar.core.util.UuidFactoryFast; +import org.sonar.core.util.logs.Profiler; import org.sonar.db.dialect.Dialect; import org.sonar.db.dialect.H2; +import org.sonar.server.platform.db.migration.MigrationConfigurationModule; +import org.sonar.server.platform.db.migration.engine.MigrationContainer; +import org.sonar.server.platform.db.migration.engine.MigrationContainerImpl; +import org.sonar.server.platform.db.migration.engine.MigrationContainerPopulator; +import org.sonar.server.platform.db.migration.engine.MigrationContainerPopulatorImpl; +import org.sonar.server.platform.db.migration.history.MigrationHistoryTableImpl; +import org.sonar.server.platform.db.migration.step.MigrationStep; +import org.sonar.server.platform.db.migration.step.MigrationStepExecutionException; +import org.sonar.server.platform.db.migration.step.MigrationSteps; +import org.sonar.server.platform.db.migration.step.MigrationStepsExecutor; +import org.sonar.server.platform.db.migration.step.RegisteredMigrationStep; +import org.sonar.server.platform.db.migration.version.DbVersion; -import static java.lang.String.format; +import static com.google.common.base.Preconditions.checkState; /** - * H2 in-memory database, used for unit tests only. - * - * @since 3.2 + * H2 in-memory database, used for unit tests against an empty DB, a specific script or against SQ schema. */ -public class H2Database implements Database { - private final String name; +public class H2Database extends CoreH2Database { private final boolean createSchema; - private BasicDataSource datasource; /** * IMPORTANT: change DB name in order to not conflict with {@link DefaultDatabaseTest} */ public H2Database(String name, boolean createSchema) { - this.name = name; + super(name); this.createSchema = createSchema; } @Override public void start() { - startDatabase(); + super.start(); if (createSchema) { createSchema(); } } - private void startDatabase() { - try { - datasource = new BasicDataSource(); - datasource.setDriverClassName("org.h2.Driver"); - datasource.setUsername("sonar"); - datasource.setPassword("sonar"); - datasource.setUrl("jdbc:h2:mem:" + name); - } catch (Exception e) { - throw new IllegalStateException("Fail to start H2", e); - } - } - private void createSchema() { Connection connection = null; try { - connection = datasource.getConnection(); - DdlUtils.createSchema(connection, "h2", true); - + connection = getDataSource().getConnection(); + NoopH2Database noopH2Database = new NoopH2Database(); + // create and populate schema + createMigrationHistoryTable(noopH2Database); + executeDbMigrations(noopH2Database); } catch (SQLException e) { throw new IllegalStateException("Fail to create schema", e); } finally { @@ -80,43 +87,101 @@ public class H2Database implements Database { } } - public void executeScript(String classloaderPath) { - Connection connection = null; - try { - connection = datasource.getConnection(); - CoreDdlUtils.executeScript(connection, classloaderPath); - - } catch (SQLException e) { - throw new IllegalStateException("Fail to execute script: " + classloaderPath, e); - } finally { - DbUtils.closeQuietly(connection); + public static final class H2MigrationContainerPopulator extends MigrationContainerPopulatorImpl { + public H2MigrationContainerPopulator(DbVersion... dbVersions) { + super(H2StepExecutor.class, dbVersions); } } - @Override - public void stop() { - try { - datasource.close(); - } catch (SQLException e) { - // Ignore error + public static final class H2StepExecutor implements MigrationStepsExecutor { + private static final String STEP_START_PATTERN = "{}..."; + private static final String STEP_STOP_PATTERN = "{}: {}"; + + private final ComponentContainer componentContainer; + + public H2StepExecutor(ComponentContainer componentContainer) { + this.componentContainer = componentContainer; + } + + @Override + public void execute(List<RegisteredMigrationStep> steps) { + steps.forEach(step -> execute(step, componentContainer)); } - } - public DataSource getDataSource() { - return datasource; + private void execute(RegisteredMigrationStep step, ComponentContainer componentContainer) { + MigrationStep migrationStep = componentContainer.getComponentByType(step.getStepClass()); + checkState(migrationStep != null, "Can not find instance of " + step.getStepClass()); + + execute(step, migrationStep); + } + + private void execute(RegisteredMigrationStep step, MigrationStep migrationStep) { + Profiler stepProfiler = Profiler.create(Loggers.get(H2Database.class)); + stepProfiler.startInfo(STEP_START_PATTERN, step); + boolean done = false; + try { + migrationStep.execute(); + done = true; + } catch (Exception e) { + throw new MigrationStepExecutionException(step, e); + } finally { + if (done) { + stepProfiler.stopInfo(STEP_STOP_PATTERN, step, "success"); + } else { + stepProfiler.stopError(STEP_STOP_PATTERN, step, "failure"); + } + } + } } - public Dialect getDialect() { - return new H2(); + private void executeDbMigrations(NoopH2Database noopH2Database) { + ComponentContainer parentContainer = new ComponentContainer(); + parentContainer.add(noopH2Database); + parentContainer.add(H2MigrationContainerPopulator.class); + MigrationConfigurationModule migrationConfigurationModule = new MigrationConfigurationModule(); + migrationConfigurationModule.configure(parentContainer); + + // dependencies required by DB migrations + parentContainer.add(SonarRuntimeImpl.forSonarQube(Version.create(8, 0), SonarQubeSide.SERVER, SonarEdition.COMMUNITY)); + parentContainer.add(UuidFactoryFast.getInstance()); + parentContainer.add(System2.INSTANCE); + + parentContainer.startComponents(); + + MigrationContainer migrationContainer = new MigrationContainerImpl(parentContainer, parentContainer.getComponentByType(MigrationContainerPopulator.class)); + MigrationSteps migrationSteps = migrationContainer.getComponentByType(MigrationSteps.class); + migrationContainer.getComponentByType(MigrationStepsExecutor.class) + .execute(migrationSteps.readAll()); } - @Override - public void enableSqlLogging(boolean enable) { - throw new UnsupportedOperationException(); + private void createMigrationHistoryTable(NoopH2Database noopH2Database) { + new MigrationHistoryTableImpl(noopH2Database).start(); } - @Override - public String toString() { - return format("H2 Database[%s]", name); + private class NoopH2Database implements Database { + @Override + public DataSource getDataSource() { + return H2Database.this.getDataSource(); + } + + @Override + public Dialect getDialect() { + return new H2(); + } + + @Override + public void enableSqlLogging(boolean enable) { + + } + + @Override + public void start() { + // do nothing + } + + @Override + public void stop() { + // do nothing + } } } diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/H2DatabaseTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/H2DatabaseTest.java index e4dec168893..98a64beb4f7 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/H2DatabaseTest.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/H2DatabaseTest.java @@ -20,6 +20,7 @@ package org.sonar.db; import java.sql.Connection; +import java.sql.ResultSet; import java.sql.SQLException; import org.junit.After; import org.junit.Before; @@ -43,9 +44,19 @@ public class H2DatabaseTest { @Test public void shouldExecuteDdlAtStartup() throws SQLException { Connection connection = db.getDataSource().getConnection(); - int tableCount = DdlUtilsTest.countTables(connection); + int tableCount = countTables(connection); connection.close(); assertThat(tableCount).isGreaterThan(30); } + + private static int countTables(Connection connection) throws SQLException { + int count = 0; + ResultSet resultSet = connection.getMetaData().getTables("", null, null, new String[] {"TABLE"}); + while (resultSet.next()) { + count++; + } + resultSet.close(); + return count; + } } diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/TestDbImpl.java b/server/sonar-db-dao/src/test/java/org/sonar/db/TestDbImpl.java index e5ef68b7fcf..d3a80308f91 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/TestDbImpl.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/TestDbImpl.java @@ -22,6 +22,7 @@ package org.sonar.db; import java.util.HashMap; import java.util.Map; import java.util.function.BiConsumer; +import java.util.function.Consumer; import java.util.function.Function; import javax.annotation.Nullable; import org.apache.commons.codec.digest.DigestUtils; @@ -51,7 +52,7 @@ class TestDbImpl extends CoreTestDb { this.myBatis = myBatis; } - void init(@Nullable String schemaPath, MyBatisConfExtension[] confExtensions) { + private void init(@Nullable String schemaPath, MyBatisConfExtension[] confExtensions) { Function<Settings, Database> databaseCreator = settings -> { String dialect = settings.getString("sonar.jdbc.dialect"); if (dialect != null && !"h2".equals(dialect)) { @@ -59,15 +60,17 @@ class TestDbImpl extends CoreTestDb { } return new H2Database("h2Tests" + DigestUtils.md5Hex(StringUtils.defaultString(schemaPath)), schemaPath == null); }; - Function<Database, Boolean> schemaPathExecutor = database -> { - if (schemaPath != null) { - // will fail if not H2 - if (!database.getDialect().getId().equals("h2")) { - return false; - } - ((H2Database) database).executeScript(schemaPath); + Consumer<Database> schemaPathExecutor = database -> { + if (schemaPath == null) { + return; } - return true; + + // scripts are assumed to be using H2 specific syntax, ignore the test if not on H2 + if (!database.getDialect().getId().equals("h2")) { + database.stop(); + throw new AssumptionViolatedException("This test is intended to be run on H2 only"); + } + ((H2Database) database).executeScript(schemaPath); }; BiConsumer<Database, Boolean> createMyBatis = (db, created) -> myBatis = newMyBatis(db, confExtensions); init(databaseCreator, schemaPathExecutor, createMyBatis); @@ -80,7 +83,7 @@ class TestDbImpl extends CoreTestDb { } static TestDbImpl create(@Nullable String schemaPath, MyBatisConfExtension... confExtensions) { - MyBatisConfExtension[] extensionArray = confExtensions == null || confExtensions.length == 0 ? null : confExtensions; + MyBatisConfExtension[] extensionArray = confExtensions.length == 0 ? null : confExtensions; if (schemaPath == null) { if (defaultSchemaBaseTestDb == null) { defaultSchemaBaseTestDb = new TestDbImpl((String) null); diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/dump/DumpSQSchema.java b/server/sonar-db-dao/src/test/java/org/sonar/db/dump/DumpSQSchema.java new file mode 100644 index 00000000000..49ebe22c378 --- /dev/null +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/dump/DumpSQSchema.java @@ -0,0 +1,54 @@ +/* + * 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.db.dump; + +import java.io.File; +import java.io.IOException; +import java.nio.charset.Charset; +import java.sql.SQLException; +import org.apache.commons.io.FileUtils; + +public class DumpSQSchema { + + public static void main(String[] args) { + SQSchemaDumper dumper = new SQSchemaDumper(); + try { + File targetFile = new File("src/schema/schema-sq.ddl"); + if (!targetFile.exists()) { + System.out.println("Can not find schema dump file: '" + targetFile + "'"); + System.exit(1); + } + + Charset charset = Charset.forName("UTF8"); + String oldContent = FileUtils.readFileToString(targetFile, charset); + String newContent = dumper.dumpToText(); + boolean upToDate = newContent.equals(oldContent); + FileUtils.write(targetFile, newContent, charset); + if (!upToDate) { + System.err.println("SQL Schema dump file has changed. Please review and commit " + targetFile.getAbsolutePath()); + System.exit(137); + } + } catch (SQLException | IOException e) { + e.printStackTrace(); + System.exit(1); + } + } + +} diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/dump/SQSchemaDumper.java b/server/sonar-db-dao/src/test/java/org/sonar/db/dump/SQSchemaDumper.java new file mode 100644 index 00000000000..3dca3902210 --- /dev/null +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/dump/SQSchemaDumper.java @@ -0,0 +1,141 @@ +/* + * 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.db.dump; + +import java.sql.Connection; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.sql.Statement; +import java.util.ArrayList; +import java.util.Comparator; +import java.util.List; +import org.sonar.db.H2Database; + +import static com.google.common.base.Preconditions.checkState; + +class SQSchemaDumper { + private static final String LINE_SEPARATOR = "\n"; + private static final String HEADER = "" + + "###############################################################" + LINE_SEPARATOR + + "#### Description of SonarQube's schema in H2 SQL syntax ####" + LINE_SEPARATOR + + "#### ####" + LINE_SEPARATOR + + "#### This file is autogenerated and stored in SCM to ####" + LINE_SEPARATOR + + "#### conveniently read the SonarQube's schema at any ####" + LINE_SEPARATOR + + "#### point in time. ####" + LINE_SEPARATOR + + "#### ####" + LINE_SEPARATOR + + "#### DO NOT MODIFY THIS FILE DIRECTLY ####" + LINE_SEPARATOR + + "#### use gradle task :server:sonar-db-dao:dumpSchema ####" + LINE_SEPARATOR + + "###############################################################" + LINE_SEPARATOR; + private static final String TABLE_SCHEMA_MIGRATIONS = "SCHEMA_MIGRATIONS"; + private static final Comparator<String> SCHEMA_MIGRATIONS_THEN_NATURAL_ORDER = ((Comparator<String>) (o1, o2) -> { + if (o1.equals(TABLE_SCHEMA_MIGRATIONS)) { + return -1; + } + if (o2.equals(TABLE_SCHEMA_MIGRATIONS)) { + return 1; + } + return 0; + }).thenComparing(String.CASE_INSENSITIVE_ORDER); + + String dumpToText() throws SQLException { + H2Database database = new H2Database("SQSchemaDumper", true); + database.start(); + + try (Connection connection = database.getDataSource().getConnection(); + Statement statement = connection.createStatement()) { + List<String> tableNames = getSortedTableNames(statement); + + StringBuilder res = new StringBuilder(HEADER); + for (String tableName : tableNames) { + res.append(LINE_SEPARATOR); + dumpTable(statement, res, tableName); + } + return res.toString(); + } + } + + /** + * List of all tables in database sorted in natural order except that table {@link #TABLE_SCHEMA_MIGRATIONS SCHEMA_MIGRATIONS} + * will always be first. + */ + private List<String> getSortedTableNames(Statement statement) throws SQLException { + checkState(statement.execute("SHOW TABLES"), "can't list tables"); + List<String> tableNames = new ArrayList<>(); + try (ResultSet rSet = statement.getResultSet()) { + while (rSet.next()) { + tableNames.add(rSet.getString(1)); + } + } + tableNames.sort(SCHEMA_MIGRATIONS_THEN_NATURAL_ORDER); + return tableNames; + } + + private void dumpTable(Statement statement, StringBuilder res, String tableName) throws SQLException { + checkState(statement.execute("SCRIPT NODATA NOSETTINGS TABLE " + tableName), "Can't get schema dump of table %s", tableName); + try (ResultSet resultSet = statement.getResultSet()) { + while (resultSet.next()) { + String sql = resultSet.getString(1); + if (isIgnoredStatement(sql)) { + continue; + } + + String cleanedSql = sql + .replaceAll(" PUBLIC\\.", " ") + .replaceAll(" MEMORY TABLE ", " TABLE "); + if (cleanedSql.startsWith("CREATE TABLE")) { + cleanedSql = fixAutoIncrementIdColumn(cleanedSql); + } + res.append(cleanedSql).append(LINE_SEPARATOR); + } + } + } + + private static boolean isIgnoredStatement(String sql) { + return sql.startsWith("CREATE SEQUENCE") || sql.startsWith("CREATE USER") || sql.startsWith("--"); + } + + /** + * Hacky hacky hack: H2 generates DDL for auto increment column which varies from one run to another and is hardly + * readable for user. + * It's currently reasonable to assume: + * <ul> + * <li>all existing auto increment columns are called ID</li> + * <li>it's not a practice to create auto increment anymore => no new will be added which could have a different name</li> + * </ul> + */ + private String fixAutoIncrementIdColumn(String cleanedSql) { + String res = fixAutoIncrementIdColumn(cleanedSql, "ID INTEGER DEFAULT (NEXT VALUE FOR ", "ID INTEGER NOT NULL AUTO_INCREMENT (1,1)"); + res = fixAutoIncrementIdColumn(res, "ID BIGINT DEFAULT (NEXT VALUE FOR ", "ID BIGINT NOT NULL AUTO_INCREMENT (1,1)"); + return res; + } + + private static String fixAutoIncrementIdColumn(String sql, String src, String tgt) { + int idAutoGenColumn = sql.indexOf(src); + if (idAutoGenColumn < 0) { + return sql; + } + + int comma = sql.indexOf(",", idAutoGenColumn + 1); + checkState(comma > -1, "can't find end of ID column declaration??"); + StringBuilder bar = new StringBuilder(sql); + bar.replace(idAutoGenColumn, comma, tgt); + return bar.toString(); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java index fc1127987c3..b199571a48c 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/MigrationConfigurationModule.java @@ -24,7 +24,7 @@ import org.sonar.server.platform.db.migration.history.MigrationHistoryImpl; import org.sonar.server.platform.db.migration.history.MigrationHistoryMeddler; import org.sonar.server.platform.db.migration.step.MigrationStepRegistryImpl; import org.sonar.server.platform.db.migration.step.MigrationStepsProvider; -import org.sonar.server.platform.db.migration.version.v79.DbVersion79; +import org.sonar.server.platform.db.migration.version.v00.DbVersion00; import org.sonar.server.platform.db.migration.version.v80.DbVersion80; public class MigrationConfigurationModule extends Module { @@ -32,7 +32,7 @@ public class MigrationConfigurationModule extends Module { protected void configureModule() { add( // DbVersion implementations - DbVersion79.class, + DbVersion00.class, DbVersion80.class, // migration steps diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/engine/MigrationContainerPopulatorImpl.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/engine/MigrationContainerPopulatorImpl.java index 432ce8664a1..2c89a904094 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/engine/MigrationContainerPopulatorImpl.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/engine/MigrationContainerPopulatorImpl.java @@ -24,6 +24,7 @@ import java.util.HashSet; import java.util.Set; import org.sonar.server.platform.db.migration.step.MigrationStep; import org.sonar.server.platform.db.migration.step.MigrationSteps; +import org.sonar.server.platform.db.migration.step.MigrationStepsExecutor; import org.sonar.server.platform.db.migration.step.MigrationStepsExecutorImpl; import org.sonar.server.platform.db.migration.version.DbVersion; @@ -38,14 +39,20 @@ import org.sonar.server.platform.db.migration.version.DbVersion; */ public class MigrationContainerPopulatorImpl implements MigrationContainerPopulator { private final DbVersion[] dbVersions; + private final Class<? extends MigrationStepsExecutor> executorType; public MigrationContainerPopulatorImpl(DbVersion... dbVersions) { + this(MigrationStepsExecutorImpl.class, dbVersions); + } + + protected MigrationContainerPopulatorImpl(Class<? extends MigrationStepsExecutor> executorType, DbVersion... dbVersions) { this.dbVersions = dbVersions; + this.executorType = executorType; } @Override public void populateContainer(MigrationContainer container) { - container.add(MigrationStepsExecutorImpl.class); + container.add(executorType); populateFromDbVersion(container); populateFromMigrationSteps(container); } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DdlChange.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DdlChange.java index c4620a40d80..e84746ec219 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DdlChange.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/step/DdlChange.java @@ -41,7 +41,7 @@ public abstract class DdlChange implements MigrationStep { @Override public final void execute() throws SQLException { try (Connection writeConnection = createDdlConnection()) { - Context context = new Context(writeConnection); + Context context = new ContextImpl(writeConnection); execute(context); } } @@ -62,22 +62,31 @@ public abstract class DdlChange implements MigrationStep { return db.getDialect(); } - public static class Context { + public interface Context { + void execute(String sql); + + void execute(String... sqls); + + void execute(List<String> sqls); + } + + private static class ContextImpl implements Context { private static final int ERROR_HANDLING_THRESHOLD = 10; // the tricky regexp is required to match "NULL" but not "NOT NULL" private final Pattern nullPattern = Pattern.compile("\\h?(?<!NOT )NULL"); private final Pattern notNullPattern = Pattern.compile("\\h?NOT NULL"); private final Connection writeConnection; - public Context(Connection writeConnection) { + private ContextImpl(Connection writeConnection) { this.writeConnection = writeConnection; } - public void execute(String sql) throws SQLException { + @Override + public void execute(String sql) { execute(sql, sql, 0); } - public void execute(String original, String sql, int errorCount) throws SQLException { + private void execute(String original, String sql, int errorCount) { try (Statement stmt = writeConnection.createStatement()) { stmt.execute(sql); writeConnection.commit(); @@ -108,11 +117,13 @@ public abstract class DdlChange implements MigrationStep { } } - public void execute(String... sqls) throws SQLException { + @Override + public void execute(String... sqls) { execute(asList(sqls)); } - public void execute(List<String> sqls) throws SQLException { + @Override + public void execute(List<String> sqls) { for (String sql : sqls) { execute(sql); } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/CreateInitialSchema.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java index b6e36d57228..a999c797d2e 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/CreateInitialSchema.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java @@ -17,7 +17,7 @@ * 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; +package org.sonar.server.platform.db.migration.version.v00; import java.sql.SQLException; import org.sonar.db.Database; 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/v00/DbVersion00.java index 741aa24b9d5..fc1b14b473b 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/v00/DbVersion00.java @@ -17,12 +17,12 @@ * 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; +package org.sonar.server.platform.db.migration.version.v00; import org.sonar.server.platform.db.migration.step.MigrationStepRegistry; import org.sonar.server.platform.db.migration.version.DbVersion; -public class DbVersion79 implements DbVersion { +public class DbVersion00 implements DbVersion { @Override public void addSteps(MigrationStepRegistry registry) { registry diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/PopulateInitialSchema.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchema.java index 4d18e3bfa84..66c3ff3af84 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v79/PopulateInitialSchema.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchema.java @@ -17,7 +17,7 @@ * 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; +package org.sonar.server.platform.db.migration.version.v00; import java.sql.SQLException; import java.util.Arrays; 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/v00/package-info.java index 1bcbde154dd..539483a3ef5 100644 --- 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/v00/package-info.java @@ -18,7 +18,7 @@ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. */ @ParametersAreNonnullByDefault -package org.sonar.server.platform.db.migration.version.v79; +package org.sonar.server.platform.db.migration.version.v00; import javax.annotation.ParametersAreNonnullByDefault; diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/charset/SqlExecutorTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/charset/SqlExecutorTest.java index dcf576234aa..f7f1788d7e0 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/charset/SqlExecutorTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/charset/SqlExecutorTest.java @@ -22,14 +22,11 @@ package org.sonar.server.platform.db.migration.charset; import java.sql.Connection; import java.util.List; import java.util.Map; -import org.junit.Before; import org.junit.Rule; import org.junit.Test; import org.sonar.db.CoreDbTester; -import org.sonar.db.dialect.H2; import static org.assertj.core.api.Assertions.assertThat; -import static org.junit.Assume.assumeTrue; public class SqlExecutorTest { @@ -43,14 +40,6 @@ public class SqlExecutorTest { @Rule public CoreDbTester dbTester = CoreDbTester.createForSchema(SqlExecutorTest.class, "users_table.sql"); - @Before - public void disableIfNotH2() { - // TODO dbTester.selectFirst() returns keys with different case - // depending on target db (lower-case for MySQL but upper-case for H2). - // It has to be fixed in order to reactive this test for all dbs. - assumeTrue(dbTester.database().getDialect().getId().equals(H2.ID)); - } - @Test public void executeSelect_executes_PreparedStatement() throws Exception { dbTester.executeInsert(USERS_DB_TABLE, LOGIN_DB_COLUMN, "login1", NAME_DB_COLUMN, "name one", IS_ROOT_DB_COLUMN, false); diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/CreateInitialSchemaTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchemaTest.java index af0b1b77415..1f4e830047b 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/CreateInitialSchemaTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchemaTest.java @@ -17,7 +17,7 @@ * 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; +package org.sonar.server.platform.db.migration.version.v00; import java.sql.Connection; import java.sql.ResultSet; 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/v00/DbVersion00Test.java index 51fa25d635b..15ccec939f6 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/v00/DbVersion00Test.java @@ -17,7 +17,7 @@ * 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; +package org.sonar.server.platform.db.migration.version.v00; import org.junit.Test; @@ -25,8 +25,8 @@ import static org.assertj.core.api.Assertions.assertThat; import static org.sonar.server.platform.db.migration.version.DbVersionTestUtils.verifyMigrationCount; import static org.sonar.server.platform.db.migration.version.DbVersionTestUtils.verifyMinimumMigrationNumber; -public class DbVersion79Test { - private DbVersion79 underTest = new DbVersion79(); +public class DbVersion00Test { + private DbVersion00 underTest = new DbVersion00(); @Test public void verify_no_support_component() { diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/PopulateInitialSchemaTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchemaTest.java index 7817f522149..166d955374a 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v79/PopulateInitialSchemaTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchemaTest.java @@ -17,7 +17,7 @@ * 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; +package org.sonar.server.platform.db.migration.version.v00; import java.sql.SQLException; import java.util.Date; diff --git a/server/sonar-db-core/src/test/resources/org/sonar/db/CoreDbTester/empty.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchemaTest/empty.sql index e69de29bb2d..e69de29bb2d 100644 --- a/server/sonar-db-core/src/test/resources/org/sonar/db/CoreDbTester/empty.sql +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchemaTest/empty.sql diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v79/PopulateInitialSchemaTest/v79.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchemaTest/v79.sql index 26147e6f907..26147e6f907 100644 --- a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v79/PopulateInitialSchemaTest/v79.sql +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchemaTest/v79.sql diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v79/CreateInitialSchemaTest/empty.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v79/CreateInitialSchemaTest/empty.sql deleted file mode 100644 index e69de29bb2d..00000000000 --- a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v79/CreateInitialSchemaTest/empty.sql +++ /dev/null diff --git a/server/sonar-server/src/main/java/org/sonar/server/platform/db/migration/AutoDbMigration.java b/server/sonar-server/src/main/java/org/sonar/server/platform/db/migration/AutoDbMigration.java index a54d45ec9e0..7327cacb0f2 100644 --- a/server/sonar-server/src/main/java/org/sonar/server/platform/db/migration/AutoDbMigration.java +++ b/server/sonar-server/src/main/java/org/sonar/server/platform/db/migration/AutoDbMigration.java @@ -19,158 +19,34 @@ */ package org.sonar.server.platform.db.migration; -import com.google.common.annotations.VisibleForTesting; -import java.sql.Connection; -import java.sql.PreparedStatement; -import java.sql.SQLException; -import org.apache.commons.dbutils.DbUtils; import org.picocontainer.Startable; -import org.sonar.api.SonarRuntime; -import org.sonar.api.utils.System2; import org.sonar.api.utils.log.Loggers; -import org.sonar.db.DbClient; -import org.sonar.db.DbSession; -import org.sonar.db.DdlUtils; -import org.sonar.db.dialect.Dialect; -import org.sonar.db.dialect.H2; import org.sonar.server.platform.DefaultServerUpgradeStatus; import org.sonar.server.platform.db.migration.engine.MigrationEngine; -import org.sonar.server.platform.db.migration.step.MigrationSteps; -import static org.sonar.server.property.InternalProperties.INSTALLATION_DATE; -import static org.sonar.server.property.InternalProperties.INSTALLATION_VERSION; - -/** - * FIXME fix this class to remove use of CoreDdlUtils.createSchema - */ public class AutoDbMigration implements Startable { private final DefaultServerUpgradeStatus serverUpgradeStatus; - private final DbClient dbClient; private final MigrationEngine migrationEngine; - private final MigrationSteps migrationSteps; - private final SonarRuntime sonarRuntime; - private final System2 system2; - public AutoDbMigration(DefaultServerUpgradeStatus serverUpgradeStatus, DbClient dbClient, MigrationEngine migrationEngine, MigrationSteps migrationSteps, - SonarRuntime sonarRuntime, System2 system2) { + public AutoDbMigration(DefaultServerUpgradeStatus serverUpgradeStatus, MigrationEngine migrationEngine) { this.serverUpgradeStatus = serverUpgradeStatus; - this.dbClient = dbClient; this.migrationEngine = migrationEngine; - this.migrationSteps = migrationSteps; - this.sonarRuntime = sonarRuntime; - this.system2 = system2; - } - - private static void populateSchemaMigration(Connection connection, long maxMigrationNumber) { - try (PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES (?)")) { - batchExecute( - 0, maxMigrationNumber + 1, - preparedStatement, connection, - (statement, counter) -> statement.setString(1, String.valueOf(counter))); - } catch (SQLException e) { - throw new RuntimeException("Failed to insert rows into table SCHEMA_MIGRATIONS", e); - } - } - - /** - * see SONAR-8586 - */ - private static void hackFixForProjectMeasureTreeQueries(Connection connection) { - int metricId = 1; - try (PreparedStatement preparedStatement = connection.prepareStatement("insert into PROJECT_MEASURES (METRIC_ID,COMPONENT_UUID,ANALYSIS_UUID) values (?,?,?);")) { - batchExecute( - 1, 1000, - preparedStatement, connection, - (stmt, counter) -> { - preparedStatement.setInt(1, metricId); - preparedStatement.setString(2, "foo_" + counter); - preparedStatement.setString(3, "bar_" + counter); - }); - } catch (SQLException e) { - throw new RuntimeException("Failed to insert fake rows into table PROJECT_MEASURES", e); - } - } - - /** - * @param start included - * @param end excluded - */ - private static void batchExecute(long start, long end, - PreparedStatement preparedStatement, Connection connection, - Preparer preparer) throws SQLException { - for (long i = start; i < end; i++) { - preparer.prepare(preparedStatement, i); - preparedStatement.addBatch(); - if (i % 250 == 0) { - preparedStatement.executeBatch(); - connection.commit(); - } - } - preparedStatement.executeBatch(); - connection.commit(); } @Override public void start() { if (serverUpgradeStatus.isFreshInstall()) { Loggers.get(getClass()).info("Automatically perform DB migration on fresh install"); - Dialect dialect = dbClient.getDatabase().getDialect(); - if (H2.ID.equals(dialect.getId())) { - installH2(); - } else { - migrationEngine.execute(); - } + migrationEngine.execute(); } else if (serverUpgradeStatus.isUpgraded() && serverUpgradeStatus.isBlueGreen()) { Loggers.get(getClass()).info("Automatically perform DB migration on blue/green deployment"); migrationEngine.execute(); } } - @VisibleForTesting - void installH2() { - Connection connection = null; - try (DbSession session = dbClient.openSession(false)) { - connection = session.getConnection(); - createH2Schema(connection, dbClient.getDatabase().getDialect().getId()); - } finally { - DbUtils.closeQuietly(connection); - } - } - - @VisibleForTesting - protected void createH2Schema(Connection connection, String dialectId) { - DdlUtils.createSchema(connection, dialectId, false); - populateInstallDateAndVersion(connection); - populateSchemaMigration(connection, migrationSteps.getMaxMigrationNumber()); - hackFixForProjectMeasureTreeQueries(connection); - } - - private void populateInstallDateAndVersion(Connection connection) { - insertInternalProperty(connection, INSTALLATION_DATE, String.valueOf(system2.now())); - insertInternalProperty(connection, INSTALLATION_VERSION, sonarRuntime.getApiVersion().toString()); - } - - private void insertInternalProperty(Connection connection, String key, String value) { - try (PreparedStatement preparedStatementDate = connection - .prepareStatement("insert into internal_properties (kee, is_empty, text_value, clob_value, created_at) values (?, ?, ?, ?, ?)")) { - preparedStatementDate.setString(1, key); - preparedStatementDate.setBoolean(2, false); - preparedStatementDate.setString(3, value); - preparedStatementDate.setString(4, null); - preparedStatementDate.setLong(5, system2.now()); - preparedStatementDate.execute(); - } catch (SQLException e) { - throw new RuntimeException("Failed to insert internal properties " + key, e); - } - } - @Override public void stop() { // nothing to do } - @FunctionalInterface - private interface Preparer { - void prepare(PreparedStatement statement, long counter) throws SQLException; - } } diff --git a/server/sonar-server/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test.java b/server/sonar-server/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test.java deleted file mode 100644 index 991eaf56f0b..00000000000 --- a/server/sonar-server/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationH2Test.java +++ /dev/null @@ -1,84 +0,0 @@ -/* - * 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; - -import java.sql.Connection; -import java.sql.ResultSet; -import java.sql.SQLException; -import org.assertj.core.api.Assertions; -import org.junit.Test; -import org.mockito.Mockito; -import org.sonar.api.SonarRuntime; -import org.sonar.api.utils.System2; -import org.sonar.api.utils.Version; -import org.sonar.db.DbClient; -import org.sonar.db.DbSession; -import org.sonar.db.H2Database; -import org.sonar.db.dialect.H2; -import org.sonar.server.platform.DefaultServerUpgradeStatus; -import org.sonar.server.platform.db.migration.engine.MigrationEngine; -import org.sonar.server.platform.db.migration.history.MigrationHistoryTableImpl; -import org.sonar.server.platform.db.migration.step.MigrationSteps; -import org.sonar.server.property.InternalProperties; - -import static org.mockito.ArgumentMatchers.anyBoolean; -import static org.mockito.Mockito.mock; -import static org.mockito.Mockito.when; - -public class AutoDbMigrationH2Test { - - private DbClient dbClient = mock(DbClient.class, Mockito.RETURNS_DEEP_STUBS); - private DefaultServerUpgradeStatus serverUpgradeStatus = mock(DefaultServerUpgradeStatus.class); - private MigrationEngine migrationEngine = mock(MigrationEngine.class); - private MigrationSteps migrationSteps = mock(MigrationSteps.class); - private SonarRuntime sonarRuntime = mock(SonarRuntime.class); - private System2 system2 = mock(System2.class); - - private AutoDbMigration underTest = new AutoDbMigration(serverUpgradeStatus, dbClient, migrationEngine, migrationSteps, sonarRuntime, system2); - - - @Test - public void testInstallH2() throws SQLException { - DbSession dbSession = mock(DbSession.class); - when(dbClient.getDatabase().getDialect()).thenReturn(new H2()); - when(dbClient.openSession(anyBoolean())).thenReturn(dbSession); - when(system2.now()).thenReturn(123456789L); - H2Database db = new H2Database("sonar", false); - db.start(); - Connection connection = db.getDataSource().getConnection(); - when(dbSession.getConnection()).thenReturn(connection); - Version version = Version.create(7, 9, 0); - when(sonarRuntime.getApiVersion()).thenReturn(version); - new MigrationHistoryTableImpl(db).start(); - - underTest.installH2(); - - String selectInstallVersion = "select text_value from internal_properties where kee = '" + InternalProperties.INSTALLATION_VERSION + "'"; - ResultSet resultSetVersion = db.getDataSource().getConnection().prepareStatement(selectInstallVersion).executeQuery(); - resultSetVersion.next(); - Assertions.assertThat(resultSetVersion.getString(1)).isEqualTo("7.9"); - - String selectInstallDate = "select text_value from internal_properties where kee = '" + InternalProperties.INSTALLATION_DATE + "'"; - ResultSet resultSetDate = db.getDataSource().getConnection().prepareStatement(selectInstallDate).executeQuery(); - resultSetDate.next(); - Assertions.assertThat(resultSetDate.getString(1)).isEqualTo("123456789"); - } - -} diff --git a/server/sonar-server/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationTest.java b/server/sonar-server/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationTest.java index 5fd2bbdf847..1c8d50cd7f5 100644 --- a/server/sonar-server/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationTest.java +++ b/server/sonar-server/src/test/java/org/sonar/server/platform/db/migration/AutoDbMigrationTest.java @@ -19,17 +19,13 @@ */ package org.sonar.server.platform.db.migration; -import java.sql.Connection; import org.junit.Rule; import org.junit.Test; import org.junit.rules.ExpectedException; import org.mockito.Mockito; -import org.sonar.api.SonarRuntime; -import org.sonar.api.utils.System2; import org.sonar.api.utils.log.LogTester; import org.sonar.api.utils.log.LoggerLevel; import org.sonar.db.DbClient; -import org.sonar.db.DbSession; import org.sonar.db.dialect.Dialect; import org.sonar.db.dialect.H2; import org.sonar.db.dialect.MsSql; @@ -37,13 +33,10 @@ import org.sonar.db.dialect.Oracle; import org.sonar.db.dialect.PostgreSql; import org.sonar.server.platform.DefaultServerUpgradeStatus; import org.sonar.server.platform.db.migration.engine.MigrationEngine; -import org.sonar.server.platform.db.migration.step.MigrationSteps; import static org.assertj.core.api.Assertions.assertThat; import static org.mockito.Mockito.mock; -import static org.mockito.Mockito.spy; import static org.mockito.Mockito.verify; -import static org.mockito.Mockito.verifyNoMoreInteractions; import static org.mockito.Mockito.verifyZeroInteractions; import static org.mockito.Mockito.when; @@ -56,28 +49,11 @@ public class AutoDbMigrationTest { private DbClient dbClient = mock(DbClient.class, Mockito.RETURNS_DEEP_STUBS); private DefaultServerUpgradeStatus serverUpgradeStatus = mock(DefaultServerUpgradeStatus.class); private MigrationEngine migrationEngine = mock(MigrationEngine.class); - private MigrationSteps migrationSteps = mock(MigrationSteps.class); - private SonarRuntime sonarRuntime = mock(SonarRuntime.class); - private System2 system2 = mock(System2.class); - private AutoDbMigration underTest = new AutoDbMigration(serverUpgradeStatus, dbClient, migrationEngine, migrationSteps, sonarRuntime, system2); - - private AutoDbMigration noRealH2Creation = spy(new AutoDbMigration(serverUpgradeStatus, dbClient, migrationEngine, migrationSteps, sonarRuntime, system2) { - @Override - protected void createH2Schema(Connection connection, String dialectId) { - // do nothing - } - }); + private AutoDbMigration underTest = new AutoDbMigration(serverUpgradeStatus, migrationEngine); @Test - public void start_creates_schema_on_h2_if_fresh_install() { - mockDialect(new H2()); - mockDbClientOpenSession(); - mockFreshInstall(true); - - noRealH2Creation.start(); - - verify(noRealH2Creation).installH2(); - verifyInfoLog(); + public void start_runs_MigrationEngine_on_h2_if_fresh_install() { + start_runs_MigrationEngine_for_dialect_if_fresh_install(new H2()); } @Test @@ -109,10 +85,8 @@ public class AutoDbMigrationTest { public void start_does_nothing_if_not_fresh_install() { mockFreshInstall(false); - noRealH2Creation.start(); + underTest.start(); - verify(noRealH2Creation).start(); - verifyNoMoreInteractions(noRealH2Creation); verifyZeroInteractions(migrationEngine); assertThat(logTester.logs(LoggerLevel.INFO)).isEmpty(); } @@ -154,13 +128,6 @@ public class AutoDbMigrationTest { when(dbClient.getDatabase().getDialect()).thenReturn(dialect); } - private void mockDbClientOpenSession() { - Connection connection = mock(Connection.class); - DbSession session = mock(DbSession.class); - when(session.getConnection()).thenReturn(connection); - when(dbClient.openSession(false)).thenReturn(session); - } - private void verifyInfoLog() { assertThat(logTester.logs()).hasSize(1); assertThat(logTester.logs(LoggerLevel.INFO)).containsExactly("Automatically perform DB migration on fresh install"); |