From 51f65c64f983be924cdd90598a68e34f433ab927 Mon Sep 17 00:00:00 2001 From: Jacek Date: Mon, 14 Jun 2021 13:40:25 +0200 Subject: [PATCH] SONAR-14792 cleanup CreateInitialSchema to be 9.0 --- .../version/v00/CreateInitialSchema.java | 605 ++++++++++++------ .../version/v00/PopulateInitialSchema.java | 85 +-- .../version/v00/CreateInitialSchemaTest.java | 11 + 3 files changed, 450 insertions(+), 251 deletions(-) diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java index e64ac94c90a..8c2ec6dcbf4 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchema.java @@ -46,8 +46,8 @@ import static org.sonar.server.platform.db.migration.def.TinyIntColumnDef.newTin import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.MAX_SIZE; import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.USER_UUID_SIZE; import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_VARCHAR_SIZE; import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; -import static org.sonar.server.platform.db.migration.sql.CreateTableBuilder.PRIMARY_KEY_PREFIX; import static org.sonar.server.platform.db.migration.sql.CreateTableBuilder.ColumnFlag.AUTO_INCREMENT; public class CreateInitialSchema extends DdlChange { @@ -71,7 +71,6 @@ public class CreateInitialSchema extends DdlChange { private static final String USER_UUID_COL_NAME = "user_uuid"; // usual technical columns - private static final IntegerColumnDef ID_COL = newIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(); private static final VarcharColumnDef UUID_COL = newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); private static final BigIntegerColumnDef TECHNICAL_CREATED_AT_COL = newBigIntegerColumnDefBuilder().setColumnName(CREATED_AT_COL_NAME).setIsNullable(false).build(); @@ -89,13 +88,19 @@ public class CreateInitialSchema extends DdlChange { public void execute(Context context) throws SQLException { createActiveRuleParameters(context); createActiveRules(context); + createAlmPats(context); + createAlmSettings(context); + createProjectAlmSettings(context); createAnalysisProperties(context); + createAppBranchProjectBranch(context); + createAppProjects(context); createCeActivity(context); createCeQueue(context); createCeScannerContext(context); createCeTaskCharacteristics(context); createCeTaskInput(context); createCeTaskMessage(context); + createComponents(context); createDefaultQProfiles(context); createDeprecatedRuleKeys(context); createDuplicationsIndex(context); @@ -113,6 +118,7 @@ public class CreateInitialSchema extends DdlChange { createLiveMeasures(context); createManualMeasures(context); createMetrics(context); + createNewCodePeriods(context); createNotifications(context); createOrgQProfiles(context); createPermTemplatesGroups(context); @@ -126,20 +132,24 @@ public class CreateInitialSchema extends DdlChange { createProjectMeasures(context); createProjectQprofiles(context); createProjects(context); + createProjectQGates(context); createProperties(context); createQProfileChanges(context); createQProfileEditGroups(context); createQProfileEditUsers(context); createQualityGateConditions(context); createQualityGates(context); + createSessionTokens(context); createRulesRepository(context); createRules(context); createRulesMetadata(context); createRulesParameters(context); createRulesProfiles(context); + createSamlMessageIds(context); createSnapshots(context); createUserProperties(context); createUserRoles(context); + createUserDismissedMessage(context); createUserTokens(context); createUsers(context); createWebhookDeliveries(context); @@ -148,37 +158,100 @@ public class CreateInitialSchema extends DdlChange { private void createActiveRuleParameters(Context context) { String tableName = "active_rule_parameters"; - IntegerColumnDef activeRuleIdColumnDef = newIntegerColumnDefBuilder().setColumnName("active_rule_id").setIsNullable(false).build(); + VarcharColumnDef activeRuleUuidColumnDef = newVarcharColumnBuilder("active_rule_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); + VarcharColumnDef rulesParameterUuidColumnDef = newVarcharColumnBuilder("rules_parameter_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(activeRuleIdColumnDef) - .addColumn(newIntegerColumnDefBuilder().setColumnName("rules_parameter_id").setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder("value").setLimit(MAX_SIZE).build()) .addColumn(newLenientVarcharBuilder("rules_parameter_key").setLimit(128).build()) + .addPkColumn(UUID_COL) + .addColumn(activeRuleUuidColumnDef) + .addColumn(rulesParameterUuidColumnDef) .build()); - addIndex(context, tableName, "ix_arp_on_active_rule_id", false, activeRuleIdColumnDef); + addIndex(context, tableName, "arp_active_rule_uuid", false, activeRuleUuidColumnDef); } private void createActiveRules(Context context) { - IntegerColumnDef profileIdCol = newIntegerColumnDefBuilder().setColumnName("profile_id").setIsNullable(false).build(); - IntegerColumnDef ruleIdCol = newIntegerColumnDefBuilder().setColumnName("rule_id").setIsNullable(false).build(); + VarcharColumnDef profileUuidCol = newVarcharColumnBuilder("profile_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); + VarcharColumnDef ruleUuidCol = newVarcharColumnBuilder("rule_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); context.execute( newTableBuilder("active_rules") - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(profileIdCol) - .addColumn(ruleIdCol) .addColumn(newIntegerColumnDefBuilder().setColumnName("failure_level").setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder("inheritance").setLimit(10).build()) .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL) .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL) + .addPkColumn(UUID_COL) + .addColumn(profileUuidCol) + .addColumn(ruleUuidCol) .build()); - addIndex(context, "active_rules", "uniq_profile_rule_ids", true, profileIdCol, ruleIdCol); + addIndex(context, "active_rules", "uniq_profile_rule_uuids", true, profileUuidCol, ruleUuidCol); + } + + private void createAlmPats(Context context) { + String tableName = "alm_pats"; + VarcharColumnDef patCol = newVarcharColumnBuilder("pat").setIsNullable(false).setLimit(2000).build(); + VarcharColumnDef userUuidCol = newVarcharColumnBuilder("user_uuid").setIsNullable(false).setLimit(256).build(); + VarcharColumnDef almSettingUuidCol = newVarcharColumnBuilder("alm_setting_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + + context.execute(newTableBuilder(tableName) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(patCol) + .addColumn(userUuidCol) + .addColumn(almSettingUuidCol) + .addColumn(TECHNICAL_UPDATED_AT_COL) + .addColumn(TECHNICAL_CREATED_AT_COL) + .build()); + addIndex(context, tableName, "uniq_alm_pats", true, userUuidCol, almSettingUuidCol); + } + + private void createAlmSettings(Context context) { + String tableName = "alm_settings"; + VarcharColumnDef keeCol = newVarcharColumnBuilder("kee").setIsNullable(false).setLimit(200).build(); + + context.execute(newTableBuilder(tableName) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(newVarcharColumnBuilder("alm_id").setIsNullable(false).setLimit(UUID_SIZE).build()) + .addColumn(keeCol) + .addColumn(newVarcharColumnBuilder("url").setIsNullable(true).setLimit(2000).build()) + .addColumn(newVarcharColumnBuilder("app_id").setIsNullable(true).setLimit(80).build()) + .addColumn(newVarcharColumnBuilder("private_key").setIsNullable(true).setLimit(2000).build()) + .addColumn(newVarcharColumnBuilder("pat").setIsNullable(true).setLimit(2000).build()) + .addColumn(TECHNICAL_UPDATED_AT_COL) + .addColumn(TECHNICAL_CREATED_AT_COL) + .addColumn(newVarcharColumnBuilder("client_id").setIsNullable(true).setLimit(80).build()) + .addColumn(newVarcharColumnBuilder("client_secret").setIsNullable(true).setLimit(80).build()) + .build()); + addIndex(context, tableName, "uniq_alm_settings", true, keeCol); + } + + private void createProjectAlmSettings(Context context) { + String tableName = "project_alm_settings"; + VarcharColumnDef almSettingUuidCol = newVarcharColumnBuilder("alm_setting_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + VarcharColumnDef projectUuidCol = newVarcharColumnBuilder("project_uuid").setIsNullable(false).setLimit(UUID_VARCHAR_SIZE).build(); + VarcharColumnDef almRepoCol = newVarcharColumnBuilder("alm_repo").setIsNullable(true).setLimit(256).build(); + VarcharColumnDef almSlugCol = newVarcharColumnBuilder("alm_slug").setIsNullable(true).setLimit(256).build(); + BooleanColumnDef summaryCommentEnabledCol = newBooleanColumnBuilder("summary_comment_enabled").setIsNullable(true).build(); + BooleanColumnDef monorepoCol = newBooleanColumnBuilder("monorepo").setIsNullable(false).build(); + + context.execute(newTableBuilder(tableName) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(almSettingUuidCol) + .addColumn(projectUuidCol) + .addColumn(almRepoCol) + .addColumn(almSlugCol) + .addColumn(TECHNICAL_UPDATED_AT_COL) + .addColumn(TECHNICAL_CREATED_AT_COL) + .addColumn(summaryCommentEnabledCol) + .addColumn(monorepoCol) + .build()); + addIndex(context, tableName, "uniq_project_alm_settings", true, projectUuidCol); + addIndex(context, tableName, "project_alm_settings_alm", false, almSettingUuidCol); + addIndex(context, tableName, "project_alm_settings_slug", false, almSlugCol); } private void createAnalysisProperties(Context context) { String tableName = "analysis_properties"; - VarcharColumnDef snapshotUuidColumn = newVarcharColumnBuilder("snapshot_uuid") + VarcharColumnDef snapshotUuidColumn = newVarcharColumnBuilder("analysis_uuid") .setIsNullable(false) .setLimit(UUID_SIZE) .build(); @@ -192,7 +265,46 @@ public class CreateInitialSchema extends DdlChange { .addColumn(newBooleanColumnDefBuilder().setColumnName("is_empty").setIsNullable(false).build()) .addColumn(TECHNICAL_CREATED_AT_COL) .build()); - addIndex(context, tableName, "ix_snapshot_uuid", false, snapshotUuidColumn); + addIndex(context, tableName, "analysis_properties_analysis", false, snapshotUuidColumn); + } + + private void createAppBranchProjectBranch(Context context) { + String tableName = "app_branch_project_branch"; + VarcharColumnDef applicationBranchUuid = newVarcharColumnBuilder("application_branch_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + VarcharColumnDef projectBranchUuid = newVarcharColumnBuilder("project_branch_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + VarcharColumnDef applicationUuid = newVarcharColumnBuilder("application_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + VarcharColumnDef projectUuid = newVarcharColumnBuilder("project_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + context.execute( + newTableBuilder(tableName) + .addPkColumn(UUID_COL) + .addColumn(applicationUuid) + .addColumn(applicationBranchUuid) + .addColumn(projectUuid) + .addColumn(projectBranchUuid) + .addColumn(TECHNICAL_CREATED_AT_COL) + .build()); + addIndex(context, tableName, "uniq_app_branch_proj", true, applicationBranchUuid, projectBranchUuid); + addIndex(context, tableName, "idx_abpb_app_uuid", false, applicationUuid); + addIndex(context, tableName, "idx_abpb_app_branch_uuid", false, applicationBranchUuid); + addIndex(context, tableName, "idx_abpb_proj_uuid", false, projectUuid); + addIndex(context, tableName, "idx_abpb_proj_branch_uuid", false, projectBranchUuid); + } + + private void createAppProjects(Context context) { + String tableName = "app_projects"; + VarcharColumnDef applicationUuid = newVarcharColumnBuilder("application_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + VarcharColumnDef projectUuid = newVarcharColumnBuilder("project_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); + context.execute( + newTableBuilder(tableName) + .addPkColumn(UUID_COL) + .addColumn(applicationUuid) + .addColumn(projectUuid) + .addColumn(TECHNICAL_CREATED_AT_COL) + .build()); + + addIndex(context, tableName, "uniq_app_projects", true, applicationUuid, projectUuid); + addIndex(context, tableName, "idx_app_proj_application_uuid", false, applicationUuid); + addIndex(context, tableName, "idx_app_proj_project_uuid", false, projectUuid); } private void createCeActivity(Context context) { @@ -201,14 +313,13 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef mainComponentUuidCol = newVarcharColumnBuilder("main_component_uuid").setLimit(UUID_SIZE).setIsNullable(true).build(); VarcharColumnDef componentUuidCol = newVarcharColumnBuilder(COMPONENT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build(); VarcharColumnDef statusCol = newLenientVarcharBuilder("status").setLimit(15).setIsNullable(false).build(); - BooleanColumnDef isLastCol = newBooleanColumnDefBuilder().setColumnName("is_last").setIsNullable(false).build(); - VarcharColumnDef isLastKeyCol = newLenientVarcharBuilder("is_last_key").setLimit(55).setIsNullable(false).build(); BooleanColumnDef mainIsLastCol = newBooleanColumnDefBuilder().setColumnName("main_is_last").setIsNullable(false).build(); VarcharColumnDef mainIsLastKeyCol = newLenientVarcharBuilder("main_is_last_key").setLimit(55).setIsNullable(false).build(); + BooleanColumnDef isLastCol = newBooleanColumnDefBuilder().setColumnName("is_last").setIsNullable(false).build(); + VarcharColumnDef isLastKeyCol = newLenientVarcharBuilder("is_last_key").setLimit(55).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(uuidCol) + .addPkColumn(uuidCol) .addColumn(newLenientVarcharBuilder("task_type").setLimit(15).setIsNullable(false).build()) .addColumn(mainComponentUuidCol) .addColumn(componentUuidCol) @@ -247,8 +358,7 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef componentUuidCol = newLenientVarcharBuilder(COMPONENT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(uuidCol) + .addPkColumn(uuidCol) .addColumn(newLenientVarcharBuilder("task_type").setLimit(15).setIsNullable(false).build()) .addColumn(mainComponentUuidCol) .addColumn(componentUuidCol) @@ -262,7 +372,6 @@ public class CreateInitialSchema extends DdlChange { .build()); addIndex(context, tableName, "ce_queue_main_component", false, mainComponentUuidCol); addIndex(context, tableName, "ce_queue_component", false, componentUuidCol); - addIndex(context, tableName, "ce_queue_uuid", true, uuidCol); } private void createCeScannerContext(Context context) { @@ -305,13 +414,70 @@ public class CreateInitialSchema extends DdlChange { private void createCeTaskMessage(Context context) { String tableName = "ce_task_message"; VarcharColumnDef taskUuidCol = newVarcharColumnBuilder(TASK_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build(); + VarcharColumnDef messageTypeCol = newVarcharColumnBuilder("message_type").setIsNullable(false).setLimit(255).build(); context.execute(newTableBuilder(tableName) .addPkColumn(newVarcharColumnBuilder("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) .addColumn(taskUuidCol) .addColumn(newVarcharColumnBuilder("message").setIsNullable(false).setLimit(MAX_SIZE).build()) .addColumn(TECHNICAL_CREATED_AT_COL) + .addColumn(messageTypeCol) .build()); addIndex(context, tableName, tableName + "_task", false, taskUuidCol); + addIndex(context, tableName, "ctm_message_type", false, messageTypeCol); + } + + private void createComponents(Context context) { + String tableName = "components"; + VarcharColumnDef keeCol = newVarcharColumnBuilder("kee").setIsNullable(true).setLimit(400).build(); + VarcharColumnDef moduleUuidCol = newVarcharColumnBuilder("module_uuid").setIsNullable(true).setLimit(50).build(); + VarcharColumnDef projectUuidCol = newVarcharColumnBuilder("project_uuid").setIsNullable(false).setLimit(50).build(); + VarcharColumnDef qualifierCol = newVarcharColumnBuilder("qualifier").setIsNullable(true).setLimit(10).build(); + VarcharColumnDef rootUuidCol = newVarcharColumnBuilder("root_uuid").setIsNullable(false).setLimit(50).build(); + VarcharColumnDef uuidCol = newVarcharColumnBuilder("uuid").setIsNullable(false).setLimit(50).build(); + VarcharColumnDef mainBranchProjectUuidCol = newVarcharColumnBuilder("main_branch_project_uuid").setIsNullable(true).setLimit(50).build(); + + context.execute(newTableBuilder(tableName) + .addColumn(uuidCol) + .addColumn(keeCol) + .addColumn(newVarcharColumnBuilder("deprecated_kee").setIsNullable(true).setLimit(400).build()) + .addColumn(newVarcharColumnBuilder("name").setIsNullable(true).setLimit(2000).build()) + .addColumn(newVarcharColumnBuilder("long_name").setIsNullable(true).setLimit(2000).build()) + .addColumn(newVarcharColumnBuilder("description").setIsNullable(true).setLimit(2000).build()) + .addColumn(newBooleanColumnDefBuilder().setColumnName("enabled").setIsNullable(false).setDefaultValue(true).build()) + .addColumn(newVarcharColumnBuilder("scope").setIsNullable(true).setLimit(3).build()) + .addColumn(qualifierCol) + .addColumn(newBooleanColumnDefBuilder().setColumnName("private").setIsNullable(false).build()) + .addColumn(rootUuidCol) + .addColumn(newVarcharColumnBuilder("language").setIsNullable(true).setLimit(20).build()) + .addColumn(newVarcharColumnBuilder("copy_component_uuid").setIsNullable(true).setLimit(50).build()) + .addColumn(newVarcharColumnBuilder("path").setIsNullable(true).setLimit(2000).build()) + .addColumn(newVarcharColumnBuilder("uuid_path").setIsNullable(false).setLimit(1500).build()) + .addColumn(projectUuidCol) + .addColumn(moduleUuidCol) + .addColumn(newVarcharColumnBuilder("module_uuid_path").setIsNullable(true).setLimit(1500).build()) + .addColumn(mainBranchProjectUuidCol) + .addColumn(newBooleanColumnDefBuilder().setColumnName("b_changed").setIsNullable(true).build()) + .addColumn(newVarcharColumnBuilder("b_name").setIsNullable(true).setLimit(500).build()) + .addColumn(newVarcharColumnBuilder("b_long_name").setIsNullable(true).setLimit(500).build()) + .addColumn(newVarcharColumnBuilder("b_description").setIsNullable(true).setLimit(2000).build()) + .addColumn(newBooleanColumnDefBuilder().setColumnName("b_enabled").setIsNullable(true).build()) + .addColumn(newVarcharColumnBuilder("b_qualifier").setIsNullable(true).setLimit(10).build()) + .addColumn(newVarcharColumnBuilder("b_language").setIsNullable(true).setLimit(20).build()) + .addColumn(newVarcharColumnBuilder("b_copy_component_uuid").setIsNullable(true).setLimit(50).build()) + .addColumn(newVarcharColumnBuilder("b_path").setIsNullable(true).setLimit(2000).build()) + .addColumn(newVarcharColumnBuilder("b_uuid_path").setIsNullable(true).setLimit(1500).build()) + .addColumn(newVarcharColumnBuilder("b_module_uuid").setIsNullable(true).setLimit(50).build()) + .addColumn(newVarcharColumnBuilder("b_module_uuid_path").setIsNullable(true).setLimit(1500).build()) + .addColumn(newTimestampColumnDefBuilder().setColumnName(CREATED_AT_COL_NAME).setIsNullable(true).build()) + .build()); + + addIndex(context, tableName, "projects_kee", true, keeCol); + addIndex(context, tableName, "projects_module_uuid", false, moduleUuidCol); + addIndex(context, tableName, "projects_project_uuid", false, projectUuidCol); + addIndex(context, tableName, "projects_qualifier", false, qualifierCol); + addIndex(context, tableName, "projects_root_uuid", false, rootUuidCol); + addIndex(context, tableName, "projects_uuid", false, uuidCol); + addIndex(context, tableName, "idx_main_branch_prj_uuid", false, mainBranchProjectUuidCol); } private void createDefaultQProfiles(Context context) { @@ -323,7 +489,6 @@ public class CreateInitialSchema extends DdlChange { context.execute( newTableBuilder(tableName) - .addPkColumn(newLenientVarcharBuilder(ORGANIZATION_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build()) .addPkColumn(newLenientVarcharBuilder("language").setLimit(20).setIsNullable(false).build()) .addColumn(profileUuidColumn) .addColumn(TECHNICAL_CREATED_AT_COL) @@ -334,18 +499,18 @@ public class CreateInitialSchema extends DdlChange { private void createDeprecatedRuleKeys(Context context) { String tableName = "deprecated_rule_keys"; - IntegerColumnDef ruleIdCol = newIntegerColumnDefBuilder().setColumnName("rule_id").setIsNullable(false).build(); + VarcharColumnDef ruleUuidCol = newVarcharColumnBuilder("rule_uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); VarcharColumnDef oldRepositoryKeyCol = newVarcharColumnBuilder("old_repository_key").setIsNullable(false).setLimit(255).build(); VarcharColumnDef oldRuleKeyCol = newVarcharColumnBuilder("old_rule_key").setIsNullable(false).setLimit(200).build(); context.execute(newTableBuilder(tableName) .addPkColumn(newVarcharColumnBuilder("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) - .addColumn(ruleIdCol) .addColumn(oldRepositoryKeyCol) .addColumn(oldRuleKeyCol) .addColumn(TECHNICAL_CREATED_AT_COL) + .addColumn(ruleUuidCol) .build()); addIndex(context, tableName, "uniq_deprecated_rule_keys", true, oldRepositoryKeyCol, oldRuleKeyCol); - addIndex(context, tableName, "rule_id_deprecated_rule_keys", true, ruleIdCol); + addIndex(context, tableName, "rule_uuid_deprecated_rule_keys", false, ruleUuidCol); } private void createDuplicationsIndex(Context context) { @@ -355,13 +520,13 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef componentUuidCol = newLenientVarcharBuilder(COMPONENT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) .addColumn(analysisUuidCol) .addColumn(componentUuidCol) .addColumn(hashCol) .addColumn(newIntegerColumnDefBuilder().setColumnName("index_in_file").setIsNullable(false).build()) .addColumn(newIntegerColumnDefBuilder().setColumnName("start_line").setIsNullable(false).build()) .addColumn(newIntegerColumnDefBuilder().setColumnName("end_line").setIsNullable(false).build()) + .addPkColumn(UUID_COL) .build()); addIndex(context, tableName, "duplications_index_hash", false, hashCol); @@ -414,8 +579,7 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef componentUuid = newLenientVarcharBuilder(COMPONENT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(uuidCol) + .addPkColumn(uuidCol) .addColumn(analysisUuidCol) .addColumn(newLenientVarcharBuilder("name").setLimit(400).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("category").setLimit(50).build()) @@ -437,7 +601,6 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef fileUuidCol = newLenientVarcharBuilder("file_uuid").setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) .addColumn(projectUuidCol) .addColumn(fileUuidCol) .addColumn(newClobColumnDefBuilder().setColumnName("line_hashes").setIsNullable(true).build()) @@ -449,6 +612,7 @@ public class CreateInitialSchema extends DdlChange { .addColumn(newBlobColumnDefBuilder().setColumnName("binary_data").setIsNullable(true).build()) .addColumn(TECHNICAL_CREATED_AT_COL) .addColumn(updatedAtCol) + .addPkColumn(UUID_COL) .build()); addIndex(context, tableName, "file_sources_file_uuid", true, fileUuidCol); addIndex(context, tableName, "file_sources_project_uuid", false, projectUuidCol); @@ -457,43 +621,46 @@ public class CreateInitialSchema extends DdlChange { private void createGroupRoles(Context context) { String tableName = "group_roles"; - IntegerColumnDef groupIdCol = newIntegerColumnDefBuilder().setColumnName("group_id").setIsNullable(true).build(); - IntegerColumnDef resourceIdCol = newIntegerColumnDefBuilder().setColumnName("resource_id").setIsNullable(true).build(); VarcharColumnDef roleCol = newLenientVarcharBuilder("role").setLimit(64).setIsNullable(false).build(); + VarcharColumnDef componentUuidCol = newVarcharColumnBuilder(COMPONENT_UUID_COL_NAME).setIsNullable(true).setLimit(UUID_SIZE).build(); + VarcharColumnDef groupUuidCol = newVarcharColumnBuilder("group_uuid").setIsNullable(true).setLimit(UUID_SIZE).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(groupIdCol) - .addColumn(resourceIdCol) .addColumn(roleCol) + .addColumn(componentUuidCol) + .addPkColumn(UUID_COL) + .addColumn(groupUuidCol) .build()); - addIndex(context, tableName, "uniq_group_roles", true, groupIdCol, resourceIdCol, roleCol); - addIndex(context, tableName, "group_roles_resource", false, resourceIdCol); + addIndex(context, tableName, "group_roles_component_uuid", false, componentUuidCol); + addIndex(context, tableName, "uniq_group_roles", true, groupUuidCol, componentUuidCol, roleCol); } private void createGroups(Context context) { + String tableName = "groups"; + VarcharColumnDef nameCol = newLenientVarcharBuilder("name").setLimit(500).setIsNullable(false).build(); context.execute( - newTableBuilder("groups") - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(newLenientVarcharBuilder("name").setLimit(500).setIsNullable(true).build()) + newTableBuilder(tableName) + .addColumn(nameCol) .addColumn(newLenientVarcharBuilder(DESCRIPTION_COL_NAME).setLimit(200).setIsNullable(true).build()) .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL) .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL) + .addPkColumn(UUID_COL) .build()); + addIndex(context, tableName, "uniq_groups_name", true, nameCol); } private void createGroupsUsers(Context context) { String tableName = "groups_users"; - BigIntegerColumnDef userIdCol = newBigIntegerColumnDefBuilder().setColumnName("user_id").setIsNullable(true).build(); - BigIntegerColumnDef groupIdCol = newBigIntegerColumnDefBuilder().setColumnName("group_id").setIsNullable(true).build(); + VarcharColumnDef groupUuidCol = newVarcharColumnBuilder("group_uuid").setLimit(40).setIsNullable(false).build(); + VarcharColumnDef userUuidCol = newVarcharColumnBuilder("user_uuid").setLimit(255).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addColumn(userIdCol) - .addColumn(groupIdCol) + .addColumn(groupUuidCol) + .addColumn(userUuidCol) .build()); - addIndex(context, tableName, "index_groups_users_on_user_id", false, userIdCol); - addIndex(context, tableName, "index_groups_users_on_group_id", false, groupIdCol); - addIndex(context, tableName, "groups_users_unique", true, groupIdCol, userIdCol); + addIndex(context, tableName, "index_groups_users_group_uuid", false, groupUuidCol); + addIndex(context, tableName, "index_groups_users_user_uuid", false, userUuidCol); + addIndex(context, tableName, "groups_users_unique", true, userUuidCol, groupUuidCol); } private void createInternalComponentProps(Context context) { @@ -526,9 +693,10 @@ public class CreateInitialSchema extends DdlChange { String tableName = "issue_changes"; VarcharColumnDef issueKeyCol = newLenientVarcharBuilder("issue_key").setLimit(50).setIsNullable(false).build(); VarcharColumnDef keeCol = newLenientVarcharBuilder("kee").setLimit(50).build(); + VarcharColumnDef projectUuidCol = newVarcharColumnBuilder("project_uuid").setLimit(50).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) + .addPkColumn(UUID_COL) .addColumn(keeCol) .addColumn(issueKeyCol) .addColumn(newLenientVarcharBuilder("user_login").setLimit(USER_UUID_SIZE).build()) @@ -537,9 +705,11 @@ public class CreateInitialSchema extends DdlChange { .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL) .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL) .addColumn(newBigIntegerColumnDefBuilder().setColumnName("issue_change_creation_date").build()) + .addColumn(projectUuidCol) .build()); addIndex(context, tableName, "issue_changes_issue_key", false, issueKeyCol); addIndex(context, tableName, "issue_changes_kee", false, keeCol); + addIndex(context, tableName, "issue_changes_project_uuid", false, projectUuidCol); } private void createIssues(Context context) { @@ -549,13 +719,12 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef keeCol = newLenientVarcharBuilder("kee").setLimit(50).setIsNullable(false).build(); VarcharColumnDef projectUuidCol = newLenientVarcharBuilder(PROJECT_UUID_COL_NAME).setLimit(50).build(); VarcharColumnDef resolutionCol = newLenientVarcharBuilder("resolution").setLimit(20).build(); - IntegerColumnDef ruleIdCol = newIntegerColumnDefBuilder().setColumnName("rule_id").build(); + VarcharColumnDef ruleUuidCol = newVarcharColumnBuilder("rule_uuid").setLimit(40).setIsNullable(true).build(); BigIntegerColumnDef updatedAtCol = NULLABLE_TECHNICAL_UPDATED_AT_COL; context.execute( newTableBuilder("issues") - .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) - .addColumn(keeCol) - .addColumn(ruleIdCol) + .addPkColumn(keeCol) + .addColumn(ruleUuidCol) .addColumn(newLenientVarcharBuilder("severity").setLimit(10).build()) .addColumn(newBooleanColumnDefBuilder().setColumnName("manual_severity").setIsNullable(false).build()) // unit has been fixed in SonarQube 5.6 (see migration 1151, SONAR-7493) @@ -583,26 +752,27 @@ public class CreateInitialSchema extends DdlChange { .addColumn(new TinyIntColumnDef.Builder().setColumnName("issue_type").build()) .addColumn(newBooleanColumnDefBuilder().setColumnName("from_hotspot").setIsNullable(true).build()) .build()); + addIndex(context, "issues", "issues_assignee", false, assigneeCol); addIndex(context, "issues", "issues_component_uuid", false, componentUuidCol); addIndex(context, "issues", "issues_creation_date", false, issueCreationDateCol); addIndex(context, "issues", "issues_kee", true, keeCol); addIndex(context, "issues", "issues_project_uuid", false, projectUuidCol); addIndex(context, "issues", "issues_resolution", false, resolutionCol); - addIndex(context, "issues", "issues_rule_id", false, ruleIdCol); addIndex(context, "issues", "issues_updated_at", false, updatedAtCol); + addIndex(context, "issues", "issues_rule_uuid", false, ruleUuidCol); } private void createLiveMeasures(Context context) { String tableName = "live_measures"; VarcharColumnDef projectUuidCol = newVarcharColumnBuilder(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build(); VarcharColumnDef componentUuidCol = newVarcharColumnBuilder(COMPONENT_UUID_COL_NAME).setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build(); - IntegerColumnDef metricIdCol = newIntegerColumnDefBuilder().setColumnName(METRIC_ID_COL_NAME).setIsNullable(false).build(); + VarcharColumnDef metricUuidCol = newVarcharColumnBuilder("metric_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); context.execute(newTableBuilder(tableName) .addPkColumn(newVarcharColumnBuilder("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) .addColumn(projectUuidCol) .addColumn(componentUuidCol) - .addColumn(metricIdCol) + .addColumn(metricUuidCol) .addColumn(newDecimalColumnDefBuilder().setColumnName("value").setPrecision(38).setScale(20).build()) .addColumn(newVarcharColumnBuilder("text_value").setIsNullable(true).setLimit(MAX_SIZE).build()) .addColumn(newDecimalColumnDefBuilder().setColumnName("variation").setPrecision(38).setScale(20).build()) @@ -612,7 +782,7 @@ public class CreateInitialSchema extends DdlChange { .addColumn(TECHNICAL_UPDATED_AT_COL) .build()); addIndex(context, tableName, "live_measures_project", false, projectUuidCol); - addIndex(context, tableName, "live_measures_component", true, componentUuidCol, metricIdCol); + addIndex(context, tableName, "live_measures_component", true, componentUuidCol, metricUuidCol); } private void createManualMeasures(Context context) { @@ -620,8 +790,6 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef componentUuidCol = newLenientVarcharBuilder(COMPONENT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) - .addColumn(newIntegerColumnDefBuilder().setColumnName(METRIC_ID_COL_NAME).setIsNullable(false).build()) .addColumn(newDecimalColumnDefBuilder().setColumnName("value").setPrecision(38).setScale(20).build()) .addColumn(newLenientVarcharBuilder("text_value").setLimit(MAX_SIZE).build()) .addColumn(newLenientVarcharBuilder(USER_UUID_COL_NAME).setLimit(USER_UUID_SIZE).build()) @@ -629,6 +797,8 @@ public class CreateInitialSchema extends DdlChange { .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL) .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL) .addColumn(componentUuidCol) + .addPkColumn(UUID_COL) + .addColumn(newVarcharColumnBuilder("metric_uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) .build()); addIndex(context, tableName, "manual_measures_component_uuid", false, componentUuidCol); } @@ -638,7 +808,6 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef nameCol = newLenientVarcharBuilder("name").setLimit(64).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) .addColumn(nameCol) .addColumn(newLenientVarcharBuilder(DESCRIPTION_COL_NAME).setLimit(255).build()) .addColumn(newIntegerColumnDefBuilder().setColumnName("direction").setIsNullable(false).setDefaultValue(0).build()) @@ -654,28 +823,50 @@ public class CreateInitialSchema extends DdlChange { .addColumn(newBooleanColumnDefBuilder().setColumnName("hidden").build()) .addColumn(newBooleanColumnDefBuilder().setColumnName("delete_historical_data").build()) .addColumn(newIntegerColumnDefBuilder().setColumnName("decimal_scale").build()) + .addPkColumn(UUID_COL) .build()); addIndex(context, tableName, "metrics_unique_name", true, nameCol); } + private void createNewCodePeriods(Context context) { + String tableName = "new_code_periods"; + VarcharColumnDef projectUuidCol = newVarcharColumnBuilder("project_uuid").setLimit(UUID_SIZE).setIsNullable(true).build(); + VarcharColumnDef branchUuidCol = newVarcharColumnBuilder("branch_uuid").setLimit(UUID_SIZE).setIsNullable(true).build(); + VarcharColumnDef typeCol = newVarcharColumnBuilder("type").setLimit(30).setIsNullable(false).build(); + VarcharColumnDef valueCol = newVarcharColumnBuilder("value").setLimit(255).setIsNullable(true).build(); + context.execute( + newTableBuilder(tableName) + .addPkColumn(UUID_COL) + .addColumn(projectUuidCol) + .addColumn(branchUuidCol) + .addColumn(typeCol) + .addColumn(valueCol) + .addColumn(TECHNICAL_UPDATED_AT_COL) + .addColumn(TECHNICAL_CREATED_AT_COL) + .build()); + + addIndex(context, tableName, "uniq_new_code_periods", true, projectUuidCol, branchUuidCol); + addIndex(context, tableName, "idx_ncp_type", false, typeCol); + addIndex(context, tableName, "idx_ncp_value", false, valueCol); + } + private void createNotifications(Context context) { context.execute( newTableBuilder("notifications") - .addPkColumn(ID_COL, AUTO_INCREMENT) .addColumn(newBlobColumnDefBuilder().setColumnName("data").build()) + .addPkColumn(UUID_COL) + .addColumn(TECHNICAL_CREATED_AT_COL) .build()); } private void createOrgQProfiles(Context context) { String tableName = "org_qprofiles"; int profileUuidSize = 255; - VarcharColumnDef organizationCol = newLenientVarcharBuilder(ORGANIZATION_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build(); VarcharColumnDef rulesProfileUuidCol = newLenientVarcharBuilder("rules_profile_uuid").setLimit(profileUuidSize).setIsNullable(false).build(); VarcharColumnDef parentUuidCol = newLenientVarcharBuilder("parent_uuid").setLimit(profileUuidSize).setIsNullable(true).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(profileUuidSize).setIsNullable(false).build()) - .addColumn(organizationCol) + .addPkColumn(newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(255).build()) .addColumn(rulesProfileUuidCol) .addColumn(parentUuidCol) .addColumn(newBigIntegerColumnDefBuilder().setColumnName("last_used").setIsNullable(true).build()) @@ -683,7 +874,6 @@ public class CreateInitialSchema extends DdlChange { .addColumn(TECHNICAL_CREATED_AT_COL) .addColumn(TECHNICAL_UPDATED_AT_COL) .build()); - addIndex(context, tableName, "qprofiles_org_uuid", false, organizationCol); addIndex(context, tableName, "qprofiles_rp_uuid", false, rulesProfileUuidCol); addIndex(context, tableName, "org_qprofiles_parent_uuid", false, parentUuidCol); } @@ -691,55 +881,53 @@ public class CreateInitialSchema extends DdlChange { private void createPermTemplatesGroups(Context context) { context.execute( newTableBuilder("perm_templates_groups") - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(newIntegerColumnDefBuilder().setColumnName("group_id").build()) - .addColumn(newIntegerColumnDefBuilder().setColumnName("template_id").setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder("permission_reference").setLimit(64).setIsNullable(false).build()) .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL) .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(newVarcharColumnBuilder("template_uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(newVarcharColumnBuilder("group_uuid").setLimit(UUID_SIZE).setIsNullable(true).build()) .build()); } private void createPermTemplatesUsers(Context context) { context.execute( newTableBuilder("perm_templates_users") - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(newIntegerColumnDefBuilder().setColumnName("user_id").setIsNullable(false).build()) - .addColumn(newIntegerColumnDefBuilder().setColumnName("template_id").setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder("permission_reference").setLimit(64).setIsNullable(false).build()) .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL) .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(newVarcharColumnBuilder("template_uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(newVarcharColumnBuilder("user_uuid").setLimit(USER_UUID_SIZE).setIsNullable(false).build()) .build()); } private void createPermTemplatesCharacteristics(Context context) { String tableName = "perm_tpl_characteristics"; - IntegerColumnDef templateIdColumn = newIntegerColumnDefBuilder().setColumnName("template_id").setIsNullable(false).build(); VarcharColumnDef permissionKeyColumn = newLenientVarcharBuilder("permission_key").setLimit(64).setIsNullable(false).build(); + VarcharColumnDef templateUuidColumn = newVarcharColumnBuilder("template_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(templateIdColumn) .addColumn(permissionKeyColumn) .addColumn(newBooleanColumnDefBuilder().setColumnName("with_project_creator").setIsNullable(false).setDefaultValue(false).build()) .addColumn(TECHNICAL_CREATED_AT_COL) .addColumn(TECHNICAL_UPDATED_AT_COL) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(templateUuidColumn) .build()); - addIndex(context, tableName, "uniq_perm_tpl_charac", true, templateIdColumn, permissionKeyColumn); + addIndex(context, tableName, "uniq_perm_tpl_charac", true, templateUuidColumn, permissionKeyColumn); } private void createPermissionTemplates(Context context) { context.execute( newTableBuilder("permission_templates") - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(newLenientVarcharBuilder(ORGANIZATION_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build()) .addColumn(newLenientVarcharBuilder("name").setLimit(100).setIsNullable(false).build()) - .addColumn(newLenientVarcharBuilder("kee").setLimit(100).setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder(DESCRIPTION_COL_NAME).setLimit(MAX_SIZE).build()) .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL) .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL) .addColumn(newLenientVarcharBuilder("key_pattern").setLimit(500).build()) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) .build()); } @@ -755,6 +943,7 @@ public class CreateInitialSchema extends DdlChange { .addColumn(newVarcharColumnBuilder("file_hash").setLimit(200).setIsNullable(false).build()) .addColumn(TECHNICAL_CREATED_AT_COL) .addColumn(TECHNICAL_UPDATED_AT_COL) + .addColumn(newVarcharColumnBuilder("type").setLimit(10).setIsNullable(false).build()) .build()); addIndex(context, tableName, "plugins_key", true, keyColumn); } @@ -763,21 +952,22 @@ public class CreateInitialSchema extends DdlChange { String tableName = "project_branches"; VarcharColumnDef projectUuidCol = newVarcharColumnBuilder(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build(); VarcharColumnDef keeCol = newVarcharColumnBuilder("kee").setIsNullable(false).setLimit(255).build(); - VarcharColumnDef keyTypeCol = newVarcharColumnBuilder("key_type").setIsNullable(false).setLimit(12).build(); + VarcharColumnDef branchTypeCol = newVarcharColumnBuilder("branch_type").setIsNullable(false).setLimit(12).build(); context.execute( newTableBuilder(tableName) .addPkColumn(newVarcharColumnBuilder("uuid").setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build()) .addColumn(projectUuidCol) .addColumn(keeCol) - .addColumn(newVarcharColumnBuilder("branch_type").setIsNullable(true).setLimit(12).build()) + .addColumn(branchTypeCol) .addColumn(newVarcharColumnBuilder("merge_branch_uuid").setIsNullable(true).setLimit(OLD_UUID_VARCHAR_SIZE).build()) - .addColumn(keyTypeCol) .addColumn(newBlobColumnDefBuilder().setColumnName("pull_request_binary").setIsNullable(true).build()) .addColumn(newVarcharColumnBuilder("manual_baseline_analysis_uuid").setIsNullable(true).setLimit(UUID_SIZE).build()) .addColumn(TECHNICAL_CREATED_AT_COL) .addColumn(TECHNICAL_UPDATED_AT_COL) + .addColumn(newBooleanColumnBuilder("exclude_from_purge").setDefaultValue(false).setIsNullable(false).build()) + .addColumn(newBooleanColumnBuilder("need_issue_sync").setIsNullable(false).build()) .build()); - addIndex(context, tableName, "project_branches_kee_key_type", true, projectUuidCol, keeCol, keyTypeCol); + addIndex(context, tableName, "uniq_project_branches", true, branchTypeCol, projectUuidCol, keeCol); } private void createProjectLinks(Context context) { @@ -814,30 +1004,26 @@ public class CreateInitialSchema extends DdlChange { private void createProjectMeasures(Context context) { String tableName = "project_measures"; IntegerColumnDef personIdCol = newIntegerColumnDefBuilder().setColumnName("person_id").build(); - IntegerColumnDef metricIdCol = newIntegerColumnDefBuilder().setColumnName(METRIC_ID_COL_NAME).setIsNullable(false).build(); + VarcharColumnDef metricUuidCol = newLenientVarcharBuilder("metric_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); VarcharColumnDef analysisUuidCol = newLenientVarcharBuilder(ANALYSIS_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build(); VarcharColumnDef componentUuidCol = newLenientVarcharBuilder(COMPONENT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(newBigIntegerColumnDefBuilder().setColumnName("id").setIsNullable(false).build(), AUTO_INCREMENT) .addColumn(newDecimalColumnDefBuilder().setColumnName("value").setPrecision(38).setScale(20).build()) - .addColumn(metricIdCol) .addColumn(analysisUuidCol) .addColumn(componentUuidCol) .addColumn(newLenientVarcharBuilder("text_value").setLimit(MAX_SIZE).build()) .addColumn(newLenientVarcharBuilder("alert_status").setLimit(5).build()) .addColumn(newLenientVarcharBuilder("alert_text").setLimit(MAX_SIZE).build()) - .addColumn(newLenientVarcharBuilder(DESCRIPTION_COL_NAME).setLimit(MAX_SIZE).build()) .addColumn(personIdCol) .addColumn(newDecimalColumnDefBuilder().setColumnName("variation_value_1").setPrecision(38).setScale(20).build()) - .addColumn(newDecimalColumnDefBuilder().setColumnName("variation_value_2").setPrecision(38).setScale(20).build()) - .addColumn(newDecimalColumnDefBuilder().setColumnName("variation_value_3").setPrecision(38).setScale(20).build()) - .addColumn(newDecimalColumnDefBuilder().setColumnName("variation_value_4").setPrecision(38).setScale(20).build()) - .addColumn(newDecimalColumnDefBuilder().setColumnName("variation_value_5").setPrecision(38).setScale(20).build()) .addColumn(newBlobColumnDefBuilder().setColumnName("measure_data").build()) + .addPkColumn(UUID_COL) + .addColumn(metricUuidCol) .build()); - addIndex(context, tableName, "measures_analysis_metric", false, analysisUuidCol, metricIdCol); addIndex(context, tableName, "measures_component_uuid", false, componentUuidCol); + addIndex(context, tableName, "measures_analysis_metric", false, analysisUuidCol, metricUuidCol); + addIndex(context, tableName, "project_measures_metric", false, metricUuidCol); } private void createProjectQprofiles(Context context) { @@ -846,70 +1032,45 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef profileKey = newLenientVarcharBuilder("profile_key").setLimit(50).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) .addColumn(projectUuid) .addColumn(profileKey) + .addPkColumn(UUID_COL) .build()); addIndex(context, tableName, "uniq_project_qprofiles", true, projectUuid, profileKey); } private void createProjects(Context context) { String tableName = "projects"; - VarcharColumnDef uuidCol = newLenientVarcharBuilder("uuid").setLimit(50).setIsNullable(false).build(); - VarcharColumnDef organizationUuidCol = newVarcharColumnBuilder(ORGANIZATION_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build(); - VarcharColumnDef keeCol = newLenientVarcharBuilder("kee").setLimit(400).setIsNullable(true).build(); - VarcharColumnDef qualifierCol = newLenientVarcharBuilder("qualifier").setLimit(10).setIsNullable(true).build(); - VarcharColumnDef rootUuidCol = newVarcharColumnBuilder("root_uuid").setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build(); - VarcharColumnDef projectUuidCol = newLenientVarcharBuilder(PROJECT_UUID_COL_NAME).setLimit(50).setIsNullable(false).build(); - VarcharColumnDef moduleUuidCol = newLenientVarcharBuilder("module_uuid").setLimit(50).setIsNullable(true).build(); + VarcharColumnDef uuidCol = newLenientVarcharBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); + VarcharColumnDef keeCol = newLenientVarcharBuilder("kee").setLimit(400).setIsNullable(false).build(); + VarcharColumnDef qualifierCol = newLenientVarcharBuilder("qualifier").setLimit(10).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(uuidCol) - .addColumn(organizationUuidCol) + .addPkColumn(uuidCol) .addColumn(keeCol) - .addColumn(newLenientVarcharBuilder("deprecated_kee").setLimit(400).setIsNullable(true).build()) + .addColumn(qualifierCol) .addColumn(newLenientVarcharBuilder("name").setLimit(2_000).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("long_name").setLimit(2_000).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder(DESCRIPTION_COL_NAME).setLimit(2_000).setIsNullable(true).build()) - .addColumn(newBooleanColumnDefBuilder().setColumnName("enabled").setDefaultValue(true).setIsNullable(false).build()) - .addColumn(newLenientVarcharBuilder("scope").setLimit(3).setIsNullable(true).build()) - .addColumn(qualifierCol) .addColumn(newBooleanColumnDefBuilder().setColumnName("private").setIsNullable(false).build()) - .addColumn(rootUuidCol) - .addColumn(newLenientVarcharBuilder("language").setLimit(20).setIsNullable(true).build()) - .addColumn(newVarcharColumnBuilder("copy_component_uuid").setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(true).build()) - .addColumn(newVarcharColumnBuilder("developer_uuid").setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("path").setLimit(2_000).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("uuid_path").setLimit(1_500).setIsNullable(false).build()) - .addColumn(projectUuidCol) - .addColumn(moduleUuidCol) - .addColumn(newLenientVarcharBuilder("module_uuid_path").setLimit(1_500).setIsNullable(true).build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("authorization_updated_at").setIsNullable(true).build()) .addColumn(newVarcharColumnBuilder("tags").setLimit(500).setIsNullable(true).build()) - .addColumn(newVarcharColumnBuilder("main_branch_project_uuid").setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(true).build()) - .addColumn(newBooleanColumnDefBuilder().setColumnName("b_changed").setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_name").setLimit(500).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_long_name").setLimit(500).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_description").setLimit(2_000).setIsNullable(true).build()) - .addColumn(newBooleanColumnDefBuilder().setColumnName("b_enabled").setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_qualifier").setLimit(10).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_language").setLimit(20).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_copy_component_uuid").setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_path").setLimit(2_000).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_uuid_path").setLimit(1_500).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_module_uuid").setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("b_module_uuid_path").setLimit(1_500).setIsNullable(true).build()) - .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL) + .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL) + .addColumn(TECHNICAL_UPDATED_AT_COL) + .withPkConstraintName("pk_new_projects") .build()); - addIndex(context, tableName, "projects_organization", false, organizationUuidCol); - addIndex(context, tableName, "projects_kee", true, keeCol); - addIndex(context, tableName, "projects_module_uuid", false, moduleUuidCol); - addIndex(context, tableName, "projects_project_uuid", false, projectUuidCol); - addIndex(context, tableName, "projects_qualifier", false, qualifierCol); - addIndex(context, tableName, "projects_root_uuid", false, rootUuidCol); - // see SONAR-12341, index projects_uuid should actually be unique - addIndex(context, tableName, "projects_uuid", false, uuidCol); + addIndex(context, tableName, "uniq_projects_kee", true, keeCol); + addIndex(context, tableName, "idx_qualifier", false, qualifierCol); + } + + private void createProjectQGates(Context context) { + String tableName = "project_qgates"; + VarcharColumnDef projectUuidCol = newVarcharColumnBuilder("project_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); + VarcharColumnDef qualityGateUuidCol = newVarcharColumnBuilder("quality_gate_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); + context.execute( + newTableBuilder(tableName) + .addPkColumn(projectUuidCol) + .addColumn(qualityGateUuidCol) + .build()); + addIndex(context, tableName, "uniq_project_qgates", true, projectUuidCol, qualityGateUuidCol); } private void createProperties(Context context) { @@ -917,13 +1078,13 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef propKey = newLenientVarcharBuilder("prop_key").setLimit(512).setIsNullable(false).build(); context.execute(newTableBuilder(tableName) .addColumn(propKey) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("resource_id").setIsNullable(true).build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("user_id").setIsNullable(true).build()) .addColumn(newBooleanColumnDefBuilder().setColumnName("is_empty").setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder("text_value").setLimit(MAX_SIZE).build()) .addColumn(newClobColumnDefBuilder().setColumnName("clob_value").setIsNullable(true).build()) - .addColumn(UUID_COL) .addColumn(TECHNICAL_CREATED_AT_COL) + .addColumn(newVarcharColumnDefBuilder().setColumnName(COMPONENT_UUID_COL_NAME).setIsNullable(true).setLimit(UUID_SIZE).build()) + .addPkColumn(UUID_COL) + .addColumn(newVarcharColumnDefBuilder().setColumnName(USER_UUID_COL_NAME).setIsNullable(true).setLimit(USER_UUID_SIZE).build()) // table with be renamed to properties in following migration, use final constraint name right away .withPkConstraintName("pk_properties") .build()); @@ -946,60 +1107,69 @@ public class CreateInitialSchema extends DdlChange { private void createQProfileEditGroups(Context context) { String tableName = "qprofile_edit_groups"; - IntegerColumnDef groupCol = newIntegerColumnDefBuilder().setColumnName("group_id").setIsNullable(false).build(); VarcharColumnDef qProfileUuidCol = newVarcharColumnBuilder("qprofile_uuid").setIsNullable(false).setLimit(255).build(); + VarcharColumnDef groupUuidCol = newVarcharColumnBuilder("group_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); context.execute(newTableBuilder(tableName) .addPkColumn(newVarcharColumnBuilder("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) - .addColumn(groupCol) .addColumn(qProfileUuidCol) .addColumn(TECHNICAL_CREATED_AT_COL) + .addColumn(groupUuidCol) .build()); addIndex(context, tableName, tableName + "_qprofile", false, qProfileUuidCol); - addIndex(context, tableName, tableName + "_unique", true, groupCol, qProfileUuidCol); + addIndex(context, tableName, tableName + "_unique", true, groupUuidCol, qProfileUuidCol); } private void createQProfileEditUsers(Context context) { String tableName = "qprofile_edit_users"; - IntegerColumnDef userIdCol = newIntegerColumnDefBuilder().setColumnName("user_id").setIsNullable(false).build(); - VarcharColumnDef qProfileUuidCol = newVarcharColumnBuilder("qprofile_uuid").setIsNullable(false).setLimit(255).build(); + VarcharColumnDef qProfileUuidCol = newVarcharColumnBuilder("qprofile_uuid").setLimit(255).setIsNullable(false).build(); + VarcharColumnDef userUuidCol = newVarcharColumnBuilder("user_uuid").setLimit(255).setIsNullable(false).build(); context.execute(newTableBuilder(tableName) - .addPkColumn(newVarcharColumnBuilder("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) - .addColumn(userIdCol) + .addPkColumn(UUID_COL) .addColumn(qProfileUuidCol) .addColumn(TECHNICAL_CREATED_AT_COL) + .addColumn(userUuidCol) .build()); addIndex(context, tableName, tableName + "_qprofile", false, qProfileUuidCol); - addIndex(context, tableName, tableName + "_unique", true, userIdCol, qProfileUuidCol); + addIndex(context, tableName, tableName + "_unique", true, userUuidCol, qProfileUuidCol); } private void createQualityGateConditions(Context context) { context.execute( newTableBuilder("quality_gate_conditions") - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(newIntegerColumnDefBuilder().setColumnName("qgate_id").setIsNullable(true).build()) - .addColumn(newIntegerColumnDefBuilder().setColumnName(METRIC_ID_COL_NAME).setIsNullable(true).build()) - .addColumn(newIntegerColumnDefBuilder().setColumnName("period").setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("operator").setLimit(3).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("value_error").setLimit(64).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("value_warning").setLimit(64).setIsNullable(true).build()) .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL) .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL) + .addPkColumn(newVarcharColumnBuilder("uuid").setIsNullable(false).setLimit(UUID_SIZE).build()) + .addColumn(newVarcharColumnBuilder("metric_uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(newVarcharColumnBuilder("qgate_uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) .build()); } private void createQualityGates(Context context) { - String tableName = "quality_gates"; - VarcharColumnDef uuidCol = newVarcharColumnBuilder("uuid").setIsNullable(false).setLimit(UUID_SIZE).build(); context.execute( - newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(uuidCol) + newTableBuilder("quality_gates") + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder("name").setLimit(100).setIsNullable(false).build()) .addColumn(newBooleanColumnDefBuilder().setColumnName("is_built_in").setIsNullable(false).build()) .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL) .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL) .build()); - addIndex(context, tableName, "uniq_quality_gates_uuid", true, uuidCol); + } + + private void createSessionTokens(Context context) { + String tableName = "session_tokens"; + VarcharColumnDef userUuidCol = newVarcharColumnBuilder("user_uuid").setLimit(255).setIsNullable(false).build(); + context.execute( + newTableBuilder(tableName) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(userUuidCol) + .addColumn(newBigIntegerColumnDefBuilder().setColumnName("expiration_date").setIsNullable(false).build()) + .addColumn(TECHNICAL_CREATED_AT_COL) + .addColumn(TECHNICAL_UPDATED_AT_COL) + .build()); + + addIndex(context, tableName, "session_tokens_user_uuid", false, userUuidCol); } private void createRulesRepository(Context context) { @@ -1017,7 +1187,6 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef pluginNameCol = newLenientVarcharBuilder("plugin_name").setLimit(255).setIsNullable(false).build(); context.execute( newTableBuilder("rules") - .addPkColumn(ID_COL, AUTO_INCREMENT) .addColumn(newLenientVarcharBuilder("name").setLimit(200).setIsNullable(true).build()) .addColumn(pluginRuleKeyCol) .addColumn(newVarcharColumnBuilder("plugin_key").setLimit(200).setIsNullable(true).build()) @@ -1026,7 +1195,6 @@ public class CreateInitialSchema extends DdlChange { .addColumn(newVarcharColumnBuilder("scope").setLimit(20).setIsNullable(false).build()) .addColumn(newClobColumnDefBuilder().setColumnName(DESCRIPTION_COL_NAME).setIsNullable(true).build()) .addColumn(newIntegerColumnDefBuilder().setColumnName("priority").setIsNullable(true).build()) - .addColumn(newIntegerColumnDefBuilder().setColumnName("template_id").setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("status").setLimit(40).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("language").setLimit(20).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("def_remediation_function").setLimit(20).setIsNullable(true).build()) @@ -1042,6 +1210,8 @@ public class CreateInitialSchema extends DdlChange { .addColumn(newBooleanColumnDefBuilder().setColumnName("is_external").setIsNullable(false).build()) .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL) .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL) + .addPkColumn(UUID_COL) + .addColumn(newVarcharColumnBuilder("template_uuid").setIsNullable(true).setLimit(UUID_SIZE).build()) .build()); addIndex(context, "rules", "rules_repo_key", true, pluginRuleKeyCol, pluginNameCol); } @@ -1049,8 +1219,6 @@ public class CreateInitialSchema extends DdlChange { private void createRulesMetadata(Context context) { String tableName = "rules_metadata"; context.execute(newTableBuilder(tableName) - .addPkColumn(newIntegerColumnDefBuilder().setColumnName("rule_id").setIsNullable(false).build()) - .addPkColumn(newVarcharColumnBuilder(ORGANIZATION_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build()) .addColumn(newClobColumnDefBuilder().setColumnName("note_data").setIsNullable(true).build()) .addColumn(newVarcharColumnBuilder("note_user_uuid").setLimit(USER_UUID_SIZE).setIsNullable(true).build()) .addColumn(newBigIntegerColumnDefBuilder().setColumnName("note_created_at").setIsNullable(true).build()) @@ -1065,41 +1233,53 @@ public class CreateInitialSchema extends DdlChange { .addColumn(newTinyIntColumnDefBuilder().setColumnName("ad_hoc_type").setIsNullable(true).build()) .addColumn(TECHNICAL_CREATED_AT_COL) .addColumn(TECHNICAL_UPDATED_AT_COL) - .withPkConstraintName(PRIMARY_KEY_PREFIX + tableName) + .addPkColumn(newVarcharColumnBuilder("rule_uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .withPkConstraintName("pk_rules_metadata") .build()); } private void createRulesParameters(Context context) { String tableName = "rules_parameters"; - IntegerColumnDef ruleIdCol = newIntegerColumnDefBuilder().setColumnName("rule_id").setIsNullable(false).build(); + VarcharColumnDef ruleUuidCol = newVarcharColumnBuilder("rule_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); VarcharColumnDef nameCol = newLenientVarcharBuilder("name").setLimit(128).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(ruleIdCol) .addColumn(nameCol) .addColumn(newLenientVarcharBuilder(DESCRIPTION_COL_NAME).setLimit(MAX_SIZE).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("param_type").setLimit(512).setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder("default_value").setLimit(MAX_SIZE).setIsNullable(true).build()) + .addPkColumn(UUID_COL) + .addColumn(ruleUuidCol) .build()); - addIndex(context, tableName, "rules_parameters_rule_id", false, ruleIdCol); - addIndex(context, tableName, "rules_parameters_unique", true, ruleIdCol, nameCol); + addIndex(context, tableName, "rules_parameters_rule_uuid", false, ruleUuidCol); + addIndex(context, tableName, "rules_parameters_unique", true, ruleUuidCol, nameCol); } private void createRulesProfiles(Context context) { - VarcharColumnDef keeCol = newLenientVarcharBuilder("kee").setLimit(255).setIsNullable(false).build(); + String tableName = "rules_profiles"; context.execute( - newTableBuilder("rules_profiles") - .addPkColumn(ID_COL, AUTO_INCREMENT) + newTableBuilder(tableName) .addColumn(newLenientVarcharBuilder("name").setLimit(100).setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder("language").setLimit(20).setIsNullable(true).build()) - .addColumn(keeCol) .addColumn(newBooleanColumnDefBuilder().setColumnName("is_built_in").setIsNullable(false).build()) .addColumn(newLenientVarcharBuilder("rules_updated_at").setLimit(100).setIsNullable(true).build()) .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL) .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL) + .addPkColumn(UUID_COL) + .build()); + } + + private void createSamlMessageIds(Context context) { + String tableName = "saml_message_ids"; + VarcharColumnDef messageIdCol = newLenientVarcharBuilder("message_id").setLimit(255).setIsNullable(false).build(); + context.execute( + newTableBuilder(tableName) + .addPkColumn(UUID_COL) + .addColumn(messageIdCol) + .addColumn(newBigIntegerColumnDefBuilder().setColumnName("expiration_date").setIsNullable(false).build()) + .addColumn(TECHNICAL_CREATED_AT_COL) .build()); - addIndex(context, "rules_profiles", "uniq_qprof_key", true, keeCol); + addIndex(context, tableName, "saml_message_ids_unique", true, messageIdCol); } private void createSnapshots(Context context) { @@ -1108,8 +1288,7 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef componentUuidCol = newLenientVarcharBuilder(COMPONENT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(uuidCol) + .addPkColumn(uuidCol) .addColumn(componentUuidCol) .addColumn(newLenientVarcharBuilder("status").setLimit(4).setIsNullable(false).setDefaultValue("U").build()) .addColumn(newBooleanColumnDefBuilder().setColumnName("islast").setIsNullable(false).setDefaultValue(false).build()) @@ -1120,19 +1299,7 @@ public class CreateInitialSchema extends DdlChange { .addColumn(newBigIntegerColumnDefBuilder().setColumnName("build_date").setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("period1_mode").setLimit(100).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("period1_param").setLimit(100).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("period2_mode").setLimit(100).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("period2_param").setLimit(100).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("period3_mode").setLimit(100).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("period3_param").setLimit(100).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("period4_mode").setLimit(100).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("period4_param").setLimit(100).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("period5_mode").setLimit(100).setIsNullable(true).build()) - .addColumn(newLenientVarcharBuilder("period5_param").setLimit(100).setIsNullable(true).build()) .addColumn(newBigIntegerColumnDefBuilder().setColumnName("period1_date").setIsNullable(true).build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("period2_date").setIsNullable(true).build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("period3_date").setIsNullable(true).build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("period4_date").setIsNullable(true).build()) - .addColumn(newBigIntegerColumnDefBuilder().setColumnName("period5_date").setIsNullable(true).build()) .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL) .build()); addIndex(context, tableName, "analyses_uuid", true, uuidCol); @@ -1156,18 +1323,35 @@ public class CreateInitialSchema extends DdlChange { private void createUserRoles(Context context) { String tableName = "user_roles"; - IntegerColumnDef userIdCol = newIntegerColumnDefBuilder().setColumnName("user_id").setIsNullable(true).build(); - IntegerColumnDef resourceIdCol = newIntegerColumnDefBuilder().setColumnName("resource_id").setIsNullable(true).build(); + VarcharColumnDef componentUuidCol = newVarcharColumnBuilder(COMPONENT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build(); + VarcharColumnDef userUuidCol = newVarcharColumnBuilder("user_uuid").setLimit(USER_UUID_SIZE).setIsNullable(true).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(newLenientVarcharBuilder(ORGANIZATION_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build()) - .addColumn(userIdCol) - .addColumn(resourceIdCol) .addColumn(newLenientVarcharBuilder("role").setLimit(64).setIsNullable(false).build()) + .addColumn(componentUuidCol) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(userUuidCol) + .build()); + addIndex(context, tableName, "user_roles_component_uuid", false, componentUuidCol); + addIndex(context, tableName, "user_roles_user", false, userUuidCol); + } + + private void createUserDismissedMessage(Context context) { + String tableName = "user_dismissed_messages"; + VarcharColumnDef userUuidCol = newVarcharColumnBuilder("user_uuid").setLimit(USER_UUID_SIZE).setIsNullable(false).build(); + VarcharColumnDef projectUuidCol = newVarcharColumnBuilder("project_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); + VarcharColumnDef messageTypeCol = newVarcharColumnBuilder("message_type").setLimit(255).setIsNullable(false).build(); + context.execute( + newTableBuilder(tableName) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(userUuidCol) + .addColumn(projectUuidCol) + .addColumn(messageTypeCol) + .addColumn(TECHNICAL_CREATED_AT_COL) .build()); - addIndex(context, tableName, "user_roles_resource", false, resourceIdCol); - addIndex(context, tableName, "user_roles_user", false, userIdCol); + addIndex(context, tableName, "uniq_user_dismissed_messages", true, userUuidCol, projectUuidCol, messageTypeCol); + addIndex(context, tableName, "udm_project_uuid", false, projectUuidCol); + addIndex(context, tableName, "udm_message_type", false, messageTypeCol); } private void createUserTokens(Context context) { @@ -1177,12 +1361,12 @@ public class CreateInitialSchema extends DdlChange { VarcharColumnDef tokenHashCol = newVarcharColumnBuilder("token_hash").setLimit(255).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) .addColumn(userUuidCol) .addColumn(nameCol) .addColumn(tokenHashCol) .addColumn(newBigIntegerColumnDefBuilder().setColumnName("last_connection_date").setIsNullable(true).build()) .addColumn(TECHNICAL_CREATED_AT_COL) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) .build()); addIndex(context, tableName, "user_tokens_user_uuid_name", true, userUuidCol, nameCol); addIndex(context, tableName, "user_tokens_token_hash", true, tokenHashCol); @@ -1190,18 +1374,14 @@ public class CreateInitialSchema extends DdlChange { private void createUsers(Context context) { String tableName = "users"; - VarcharColumnDef uuidCol = newVarcharColumnBuilder("uuid").setLimit(USER_UUID_SIZE).setIsNullable(false).build(); VarcharColumnDef loginCol = newLenientVarcharBuilder("login").setLimit(255).setIsNullable(false).build(); - BigIntegerColumnDef updatedAtCol = NULLABLE_TECHNICAL_UPDATED_AT_COL; VarcharColumnDef externalLoginCol = newLenientVarcharBuilder("external_login").setLimit(255).setIsNullable(false).build(); VarcharColumnDef externalIdentityProviderCol = newLenientVarcharBuilder("external_identity_provider").setLimit(100).setIsNullable(false).build(); VarcharColumnDef externalIdCol = newVarcharColumnBuilder("external_id").setLimit(255).setIsNullable(false).build(); context.execute( newTableBuilder(tableName) - .addPkColumn(ID_COL, AUTO_INCREMENT) - .addColumn(uuidCol) + .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(USER_UUID_SIZE).setIsNullable(false).build()) .addColumn(loginCol) - .addColumn(newVarcharColumnBuilder(ORGANIZATION_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("name").setLimit(200).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("email").setLimit(100).setIsNullable(true).build()) .addColumn(newLenientVarcharBuilder("crypted_password").setLimit(100).setIsNullable(true).build()) @@ -1219,11 +1399,12 @@ public class CreateInitialSchema extends DdlChange { .addColumn(newVarcharColumnBuilder("homepage_parameter").setLimit(40).setIsNullable(true).build()) .addColumn(newBigIntegerColumnDefBuilder().setColumnName("last_connection_date").setIsNullable(true).build()) .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL) - .addColumn(updatedAtCol) + .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL) + .addColumn(newBooleanColumnDefBuilder().setColumnName("reset_password").setIsNullable(false).build()) + .addColumn(newBigIntegerColumnDefBuilder().setColumnName("last_sonarlint_connection").setIsNullable(true).build()) .build()); addIndex(context, tableName, "users_login", true, loginCol); - addIndex(context, tableName, "users_updated_at", false, updatedAtCol); - addIndex(context, tableName, "users_uuid", true, uuidCol); + addIndex(context, tableName, "users_updated_at", false, NULLABLE_TECHNICAL_UPDATED_AT_COL); addIndex(context, tableName, "uniq_external_id", true, externalIdentityProviderCol, externalIdCol); addIndex(context, tableName, "uniq_external_login", true, externalIdentityProviderCol, externalLoginCol); } @@ -1232,9 +1413,10 @@ public class CreateInitialSchema extends DdlChange { String tableName = "webhook_deliveries"; VarcharColumnDef componentUuidColumn = newLenientVarcharBuilder(COMPONENT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build(); VarcharColumnDef ceTaskUuidColumn = newLenientVarcharBuilder("ce_task_uuid").setLimit(UUID_SIZE).setIsNullable(true).build(); + VarcharColumnDef webhookUuidColumn = newVarcharColumnBuilder("webhook_uuid").setLimit(UUID_SIZE).setIsNullable(false).build(); context.execute(newTableBuilder(tableName) .addPkColumn(newLenientVarcharBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) - .addColumn(newVarcharColumnBuilder("webhook_uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) + .addColumn(webhookUuidColumn) .addColumn(componentUuidColumn) .addColumn(ceTaskUuidColumn) .addColumn(newVarcharColumnBuilder(ANALYSIS_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build()) @@ -1249,15 +1431,14 @@ public class CreateInitialSchema extends DdlChange { .build()); addIndex(context, tableName, COMPONENT_UUID_COL_NAME, false, componentUuidColumn); addIndex(context, tableName, "ce_task_uuid", false, ceTaskUuidColumn); + addIndex(context, tableName, "idx_wbhk_dlvrs_wbhk_uuid", false, webhookUuidColumn); } private void createWebhooks(Context context) { String tableName = "webhooks"; - VarcharColumnDef organizationUuidCol = newVarcharColumnBuilder(ORGANIZATION_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build(); VarcharColumnDef projectUuidCol = newVarcharColumnBuilder(PROJECT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build(); context.execute(newTableBuilder(tableName) .addPkColumn(newVarcharColumnBuilder("uuid").setLimit(UUID_SIZE).setIsNullable(false).build()) - .addColumn(organizationUuidCol) .addColumn(projectUuidCol) .addColumn(newVarcharColumnBuilder("name").setLimit(100).setIsNullable(false).build()) .addColumn(newVarcharColumnBuilder("url").setLimit(2_000).setIsNullable(false).build()) @@ -1265,8 +1446,6 @@ public class CreateInitialSchema extends DdlChange { .addColumn(TECHNICAL_CREATED_AT_COL) .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL) .build()); - addIndex(context, tableName, "organization_webhook", false, organizationUuidCol); - addIndex(context, tableName, "project_webhook", false, projectUuidCol); } private static void addIndex(Context context, String table, String index, boolean unique, ColumnDef firstColumn, ColumnDef... otherColumns) { @@ -1286,6 +1465,10 @@ public class CreateInitialSchema extends DdlChange { return newVarcharColumnDefBuilder().setColumnName(column); } + private static BooleanColumnDef.Builder newBooleanColumnBuilder(String column) { + return newBooleanColumnDefBuilder().setColumnName(column); + } + private CreateTableBuilder newTableBuilder(String tableName) { return new CreateTableBuilder(getDialect(), tableName); } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchema.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchema.java index 45b03fd572d..319ba16bbeb 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchema.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v00/PopulateInitialSchema.java @@ -57,38 +57,39 @@ public class PopulateInitialSchema extends DataChange { @Override public void execute(Context context) throws SQLException { - int adminUserId = insertAdminUser(context); + String adminUserUuid = insertAdminUser(context); Groups groups = insertGroups(context); String defaultQGUuid = insertQualityGate(context); insertInternalProperty(context); insertProperties(context, defaultQGUuid); insertGroupRoles(context, groups); - insertGroupUsers(context, adminUserId, groups); + insertGroupUsers(context, adminUserUuid, groups); } - private int insertAdminUser(Context context) throws SQLException { + private String insertAdminUser(Context context) throws SQLException { truncateTable(context, "users"); long now = system2.now(); context.prepareUpsert("insert into users " + - "(uuid, login, name, email, external_id, external_login, external_identity_provider, user_local, crypted_password, salt, hash_method, is_root, onboarded, " + + "(uuid, login, name, email, external_id, external_login, external_identity_provider, user_local, crypted_password, salt, hash_method, is_root, onboarded, reset_password, " + "created_at, updated_at)" + " values " + - "(?, ?, 'Administrator', null, 'admin', 'admin', 'sonarqube', ?, ?, null, 'BCRYPT', ?, ?, ?, ?)") + "(?, ?, 'Administrator', null, 'admin', 'admin', 'sonarqube', ?, ?, null, 'BCRYPT', ?, ?, ?, ?, ?)") .setString(1, uuidFactory.create()) .setString(2, ADMIN_USER) .setBoolean(3, true) .setString(4, ADMIN_CRYPTED_PASSWORD) .setBoolean(5, false) .setBoolean(6, true) - .setLong(7, now) + .setBoolean(7, true) .setLong(8, now) + .setLong(9, now) .execute() .commit(); - Integer res = context.prepareSelect("select id from users where login=?") + String res = context.prepareSelect("select uuid from users where login=?") .setString(1, ADMIN_USER) - .get(t -> t.getInt(1)); + .get(t -> t.getString(1)); return requireNonNull(res); } @@ -157,30 +158,32 @@ public class PopulateInitialSchema extends DataChange { Date now = new Date(system2.now()); Upsert upsert = context.prepareUpsert(createInsertStatement( "groups", - "name", "description", "created_at", "updated_at")); + "uuid", "name", "description", "created_at", "updated_at")); upsert - .setString(1, ADMINS_GROUP) - .setString(2, "System administrators") - .setDate(3, now) + .setString(1, uuidFactory.create()) + .setString(2, ADMINS_GROUP) + .setString(3, "System administrators") .setDate(4, now) + .setDate(5, now) .addBatch(); upsert - .setString(1, USERS_GROUP) - .setString(2, "Any new users created will automatically join this group") - .setDate(3, now) + .setString(1, uuidFactory.create()) + .setString(2, USERS_GROUP) + .setString(3, "Any new users created will automatically join this group") .setDate(4, now) + .setDate(5, now) .addBatch(); upsert .execute() .commit(); - return new Groups(getGroupId(context, ADMINS_GROUP), getGroupId(context, USERS_GROUP)); + return new Groups(getGroupUuid(context, ADMINS_GROUP), getGroupUuid(context, USERS_GROUP)); } - private static int getGroupId(Context context, String groupName) throws SQLException { - Integer res = context.prepareSelect("select id from groups where name=?") + private static String getGroupUuid(Context context, String groupName) throws SQLException { + String res = context.prepareSelect("select uuid from groups where name=?") .setString(1, groupName) - .get(t -> t.getInt(1)); + .get(t -> t.getString(1)); return requireNonNull(res); } @@ -201,37 +204,39 @@ public class PopulateInitialSchema extends DataChange { } private static final class Groups { - private final int adminGroupId; - private final int userGroupId; + private final String adminGroupUuid; + private final String userGroupUuid; - private Groups(int adminGroupId, int userGroupId) { - this.adminGroupId = adminGroupId; - this.userGroupId = userGroupId; + private Groups(String adminGroupUuid, String userGroupUuid) { + this.adminGroupUuid = adminGroupUuid; + this.userGroupUuid = userGroupUuid; } - public int getAdminGroupId() { - return adminGroupId; + public String getAdminGroupUuid() { + return adminGroupUuid; } - public int getUserGroupId() { - return userGroupId; + public String getUserGroupUuid() { + return userGroupUuid; } } - private static void insertGroupRoles(Context context, Groups groups) throws SQLException { + private void insertGroupRoles(Context context, Groups groups) throws SQLException { truncateTable(context, "group_roles"); - Upsert upsert = context.prepareUpsert(createInsertStatement("group_roles", "group_id", "role")); + Upsert upsert = context.prepareUpsert(createInsertStatement("group_roles", "uuid","group_uuid", "role")); for (String adminRole : ADMIN_ROLES) { upsert - .setInt(1, groups.getAdminGroupId()) - .setString(2, adminRole) + .setString(1, uuidFactory.create()) + .setString(2, groups.getAdminGroupUuid()) + .setString(3, adminRole) .addBatch(); } for (String anyoneRole : Arrays.asList("scan", "provisioning")) { upsert - .setInt(1, null) - .setString(2, anyoneRole) + .setString(1, uuidFactory.create()) + .setString(2, null) + .setString(3, anyoneRole) .addBatch(); } upsert @@ -239,17 +244,17 @@ public class PopulateInitialSchema extends DataChange { .commit(); } - private static void insertGroupUsers(Context context, int adminUserId, Groups groups) throws SQLException { + private static void insertGroupUsers(Context context, String adminUserUuid, Groups groups) throws SQLException { truncateTable(context, "groups_users"); - Upsert upsert = context.prepareUpsert(createInsertStatement("groups_users", "user_id", "group_id")); + Upsert upsert = context.prepareUpsert(createInsertStatement("groups_users", "user_uuid", "group_uuid")); upsert - .setInt(1, adminUserId) - .setInt(2, groups.getUserGroupId()) + .setString(1, adminUserUuid) + .setString(2, groups.getUserGroupUuid()) .addBatch(); upsert - .setInt(1, adminUserId) - .setInt(2, groups.getAdminGroupId()) + .setString(1, adminUserUuid) + .setString(2, groups.getAdminGroupUuid()) .addBatch(); upsert .execute() diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchemaTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchemaTest.java index b5a2c2a1c53..53541c2506a 100644 --- a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchemaTest.java +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v00/CreateInitialSchemaTest.java @@ -52,6 +52,11 @@ public class CreateInitialSchemaTest { assertThat(tables).containsOnly( "active_rules", "active_rule_parameters", + "app_branch_project_branch", + "alm_pats", + "app_projects", + "alm_settings", + "project_alm_settings", "analysis_properties", "ce_activity", "ce_queue", @@ -59,6 +64,7 @@ public class CreateInitialSchemaTest { "ce_task_characteristics", "ce_task_input", "ce_task_message", + "components", "default_qprofiles", "deprecated_rule_keys", "duplications_index", @@ -76,6 +82,7 @@ public class CreateInitialSchemaTest { "live_measures", "manual_measures", "metrics", + "new_code_periods", "notifications", "org_qprofiles", "permission_templates", @@ -89,6 +96,7 @@ public class CreateInitialSchemaTest { "project_mappings", "project_measures", "project_qprofiles", + "project_qgates", "properties", "qprofile_changes", "qprofile_edit_groups", @@ -100,8 +108,11 @@ public class CreateInitialSchemaTest { "rules_parameters", "rules_profiles", "rule_repositories", + "saml_message_ids", + "session_tokens", "snapshots", "users", + "user_dismissed_messages", "user_properties", "user_roles", "user_tokens", -- 2.39.5