source.dussan.org Git - sonarqube.git/blob

   1 /*
   2  * SonarQube
   3  * Copyright (C) 2009-2023 SonarSource SA
   4  * mailto:info AT sonarsource DOT com
   5  *
   6  * This program is free software; you can redistribute it and/or
   7  * modify it under the terms of the GNU Lesser General Public
   8  * License as published by the Free Software Foundation; either
   9  * version 3 of the License, or (at your option) any later version.
  10  *
  11  * This program is distributed in the hope that it will be useful,
  12  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  13  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  14  * Lesser General Public License for more details.
  15  *
  16  * You should have received a copy of the GNU Lesser General Public License
  17  * along with this program; if not, write to the Free Software Foundation,
  18  * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
  19  */
  20 package org.sonar.server.platform.db.migration.version.v00;
  21
  22 import java.sql.SQLException;
  23 import org.sonar.db.Database;
  24 import org.sonar.server.platform.db.migration.def.BigIntegerColumnDef;
  25 import org.sonar.server.platform.db.migration.def.BooleanColumnDef;
  26 import org.sonar.server.platform.db.migration.def.ColumnDef;
  27 import org.sonar.server.platform.db.migration.def.IntegerColumnDef;
  28 import org.sonar.server.platform.db.migration.def.TimestampColumnDef;
  29 import org.sonar.server.platform.db.migration.def.TinyIntColumnDef;
  30 import org.sonar.server.platform.db.migration.def.VarcharColumnDef;
  31 import org.sonar.server.platform.db.migration.sql.CreateIndexBuilder;
  32 import org.sonar.server.platform.db.migration.sql.CreateTableBuilder;
  33 import org.sonar.server.platform.db.migration.step.DdlChange;
  34
  35 import static java.util.Arrays.stream;
  36 import static java.util.stream.Stream.concat;
  37 import static java.util.stream.Stream.of;
  38 import static org.sonar.server.platform.db.migration.def.BigIntegerColumnDef.newBigIntegerColumnDefBuilder;
  39 import static org.sonar.server.platform.db.migration.def.BlobColumnDef.newBlobColumnDefBuilder;
  40 import static org.sonar.server.platform.db.migration.def.BooleanColumnDef.newBooleanColumnDefBuilder;
  41 import static org.sonar.server.platform.db.migration.def.ClobColumnDef.newClobColumnDefBuilder;
  42 import static org.sonar.server.platform.db.migration.def.DecimalColumnDef.newDecimalColumnDefBuilder;
  43 import static org.sonar.server.platform.db.migration.def.IntegerColumnDef.newIntegerColumnDefBuilder;
  44 import static org.sonar.server.platform.db.migration.def.TimestampColumnDef.newTimestampColumnDefBuilder;
  45 import static org.sonar.server.platform.db.migration.def.TinyIntColumnDef.newTinyIntColumnDefBuilder;
  46 import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.DESCRIPTION_SECTION_KEY_SIZE;
  47 import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.MAX_SIZE;
  48 import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.USER_UUID_SIZE;
  49 import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.UUID_SIZE;
  50 import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder;
  51
  52 public class CreateInitialSchema extends DdlChange {
  53
  54   /**
  55    * Initially, UUID columns were created with size 50 when only 40 is needed. {@link VarcharColumnDef#UUID_SIZE}
  56    * should be used instead of this constant whenever reducing the column size is possible.
  57    */
  58   private static final int OLD_UUID_VARCHAR_SIZE = 50;
  59
  60   // keep column name constants in alphabetic order
  61   private static final String ANALYSIS_UUID_COL_NAME = "analysis_uuid";
  62   private static final String COMPONENT_UUID_COL_NAME = "component_uuid";
  63   private static final String CREATED_AT_COL_NAME = "created_at";
  64   private static final String DESCRIPTION_COL_NAME = "description";
  65   private static final String GROUP_UUID_COL_NAME = "group_uuid";
  66   private static final String LANGUAGE_COL_NAME = "language";
  67   private static final String METRIC_UUID_COL_NAME = "metric_uuid";
  68   private static final String PROJECT_UUID_COL_NAME = "project_uuid";
  69   private static final String BRANCH_UUID_COL_NAME = "branch_uuid";
  70   public static final String RULE_UUID_COL_NAME = "rule_uuid";
  71   private static final String STATUS_COL_NAME = "status";
  72   private static final String TASK_UUID_COL_NAME = "task_uuid";
  73   private static final String TEMPLATE_UUID_COL_NAME = "template_uuid";
  74   private static final String TEXT_VALUE_COL_NAME = "text_value";
  75   private static final String UPDATED_AT_COL_NAME = "updated_at";
  76   private static final String USER_UUID_COL_NAME = "user_uuid";
  77   private static final String VALUE_COL_NAME = "value";
  78   private static final String PRIVATE_COL_NAME = "private";
  79   private static final String QPROFILE_UUID_COL_NAME = "qprofile_uuid";
  80   private static final String EXPIRATION_DATE_COL_NAME = "expiration_date";
  81   public static final String QUALITY_GATE_UUID_COL_NAME = "quality_gate_uuid";
  82   public static final String CLOB_VALUE_COL_NAME = "clob_value";
  83   public static final String IS_EMPTY_COL_NAME = "is_empty";
  84   private static final String UNIQUE_INDEX_SUFFIX = "_unique";
  85
  86   // usual technical columns
  87   private static final VarcharColumnDef UUID_COL = newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
  88
  89   private static final BigIntegerColumnDef TECHNICAL_CREATED_AT_COL = newBigIntegerColumnDefBuilder().setColumnName(CREATED_AT_COL_NAME).setIsNullable(false).build();
  90   private static final BigIntegerColumnDef NULLABLE_TECHNICAL_CREATED_AT_COL = newBigIntegerColumnDefBuilder().setColumnName(CREATED_AT_COL_NAME).setIsNullable(true).build();
  91   private static final BigIntegerColumnDef TECHNICAL_UPDATED_AT_COL = newBigIntegerColumnDefBuilder().setColumnName(UPDATED_AT_COL_NAME).setIsNullable(false).build();
  92   private static final BigIntegerColumnDef NULLABLE_TECHNICAL_UPDATED_AT_COL = newBigIntegerColumnDefBuilder().setColumnName(UPDATED_AT_COL_NAME).setIsNullable(true).build();
  93   private static final TimestampColumnDef DEPRECATED_TECHNICAL_CREATED_AT_COL = newTimestampColumnDefBuilder().setColumnName(CREATED_AT_COL_NAME).setIsNullable(true).build();
  94   private static final TimestampColumnDef DEPRECATED_TECHNICAL_UPDATED_AT_COL = newTimestampColumnDefBuilder().setColumnName(UPDATED_AT_COL_NAME).setIsNullable(true).build();
  95
  96   public CreateInitialSchema(Database db) {
  97     super(db);
  98   }
  99
 100   @Override
 101   public void execute(Context context) throws SQLException {
 102     createActiveRuleParameters(context);
 103     createActiveRules(context);
 104     createAlmPats(context);
 105     createAlmSettings(context);
 106     createProjectAlmSettings(context);
 107     createAnalysisProperties(context);
 108     createAppBranchProjectBranch(context);
 109     createAppProjects(context);
 110     createAudits(context);
 111     createCeActivity(context);
 112     createCeQueue(context);
 113     createCeScannerContext(context);
 114     createCeTaskCharacteristics(context);
 115     createCeTaskInput(context);
 116     createCeTaskMessage(context);
 117     createComponents(context);
 118     createDefaultQProfiles(context);
 119     createDeprecatedRuleKeys(context);
 120     createDuplicationsIndex(context);
 121     createEsQueue(context);
 122     createEventComponentChanges(context);
 123     createEvents(context);
 124     createFileSources(context);
 125     createGroupRoles(context);
 126     createGroups(context);
 127     createGroupsUsers(context);
 128     createInternalComponentProps(context);
 129     createInternalProperties(context);
 130     createIssueChanges(context);
 131     createIssues(context);
 132     createLiveMeasures(context);
 133     createMetrics(context);
 134     createNewCodePeriods(context);
 135     createNewCodeReferenceIssues(context);
 136     createNotifications(context);
 137     createOrgQProfiles(context);
 138     createPermTemplatesGroups(context);
 139     createPermTemplatesUsers(context);
 140     createPermTemplatesCharacteristics(context);
 141     createPermissionTemplates(context);
 142     createPlugins(context);
 143     createPortfolioProjBranches(context);
 144     createPortfolioProjects(context);
 145     createPortfolioReferences(context);
 146     createPortfolios(context);
 147     createProjectBadgeToken(context);
 148     createProjectBranches(context);
 149     createProjectLinks(context);
 150     createProjectMappings(context);
 151     createProjectMeasures(context);
 152     createProjectQprofiles(context);
 153     createProjects(context);
 154     createProjectQGates(context);
 155     createProperties(context);
 156     createPushEvents(context);
 157     createQGateGroupPermissions(context);
 158     createQGateUserPermissions(context);
 159     createQProfileChanges(context);
 160     createQProfileEditGroups(context);
 161     createQProfileEditUsers(context);
 162     createQualityGateConditions(context);
 163     createQualityGates(context);
 164     createScimUsers(context);
 165     createSessionTokens(context);
 166     createRulesRepository(context);
 167     createRuleDescSections(context);
 168     createRules(context);
 169     createRulesParameters(context);
 170     createRulesProfiles(context);
 171     createSamlMessageIds(context);
 172     createScannerAnalysisCache(context);
 173     createSnapshots(context);
 174     createUserRoles(context);
 175     createUserDismissedMessage(context);
 176     createUserTokens(context);
 177     createUsers(context);
 178     createWebhookDeliveries(context);
 179     createWebhooks(context);
 180   }
 181
 182   private void createActiveRuleParameters(Context context) {
 183     String tableName = "active_rule_parameters";
 184     VarcharColumnDef activeRuleUuidColumnDef = newVarcharColumnDefBuilder("active_rule_uuid").setLimit(UUID_SIZE).setIsNullable(false).build();
 185     VarcharColumnDef rulesParameterUuidColumnDef = newVarcharColumnDefBuilder("rules_parameter_uuid").setLimit(UUID_SIZE).setIsNullable(false).build();
 186     context.execute(
 187       newTableBuilder(tableName)
 188         .addPkColumn(UUID_COL)
 189         .addColumn(newVarcharColumnDefBuilder(VALUE_COL_NAME).setLimit(MAX_SIZE).build())
 190         .addColumn(newVarcharColumnDefBuilder("rules_parameter_key").setLimit(128).build())
 191         .addColumn(activeRuleUuidColumnDef)
 192         .addColumn(rulesParameterUuidColumnDef)
 193         .build());
 194     addIndex(context, tableName, "arp_active_rule_uuid", false, activeRuleUuidColumnDef);
 195   }
 196
 197   private void createActiveRules(Context context) {
 198     VarcharColumnDef profileUuidCol = newVarcharColumnDefBuilder("profile_uuid").setLimit(UUID_SIZE).setIsNullable(false).build();
 199     VarcharColumnDef ruleUuidCol = newVarcharColumnDefBuilder(RULE_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
 200     context.execute(
 201       newTableBuilder("active_rules")
 202         .addPkColumn(UUID_COL)
 203         .addColumn(newIntegerColumnDefBuilder().setColumnName("failure_level").setIsNullable(false).build())
 204         .addColumn(newVarcharColumnDefBuilder("inheritance").setLimit(10).build())
 205         .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL)
 206         .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL)
 207         .addColumn(profileUuidCol)
 208         .addColumn(ruleUuidCol)
 209         .build());
 210     addIndex(context, "active_rules", "uniq_profile_rule_uuids", true, profileUuidCol, ruleUuidCol);
 211   }
 212
 213   private void createAlmPats(Context context) {
 214     String tableName = "alm_pats";
 215     VarcharColumnDef patCol = newVarcharColumnDefBuilder("pat").setIsNullable(false).setLimit(2000).build();
 216     VarcharColumnDef userUuidCol = newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setIsNullable(false).setLimit(256).build();
 217     VarcharColumnDef almSettingUuidCol = newVarcharColumnDefBuilder("alm_setting_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
 218
 219     context.execute(newTableBuilder(tableName)
 220       .addPkColumn(UUID_COL)
 221       .addColumn(patCol)
 222       .addColumn(userUuidCol)
 223       .addColumn(almSettingUuidCol)
 224       .addColumn(TECHNICAL_UPDATED_AT_COL)
 225       .addColumn(TECHNICAL_CREATED_AT_COL)
 226       .build());
 227     addIndex(context, tableName, "uniq_alm_pats", true, userUuidCol, almSettingUuidCol);
 228   }
 229
 230   private void createAlmSettings(Context context) {
 231     String tableName = "alm_settings";
 232     VarcharColumnDef keeCol = newVarcharColumnDefBuilder("kee").setIsNullable(false).setLimit(200).build();
 233
 234     context.execute(newTableBuilder(tableName)
 235       .addPkColumn(UUID_COL)
 236       .addColumn(newVarcharColumnDefBuilder("alm_id").setIsNullable(false).setLimit(UUID_SIZE).build())
 237       .addColumn(keeCol)
 238       .addColumn(newVarcharColumnDefBuilder("url").setIsNullable(true).setLimit(2000).build())
 239       .addColumn(newVarcharColumnDefBuilder("app_id").setIsNullable(true).setLimit(80).build())
 240       .addColumn(newVarcharColumnDefBuilder("private_key").setIsNullable(true).setLimit(2500).build())
 241       .addColumn(newVarcharColumnDefBuilder("pat").setIsNullable(true).setLimit(2000).build())
 242       .addColumn(TECHNICAL_UPDATED_AT_COL)
 243       .addColumn(TECHNICAL_CREATED_AT_COL)
 244       .addColumn(newVarcharColumnDefBuilder("client_id").setIsNullable(true).setLimit(80).build())
 245       .addColumn(newVarcharColumnDefBuilder("client_secret").setIsNullable(true).setLimit(160).build())
 246       .addColumn(newVarcharColumnDefBuilder("webhook_secret").setIsNullable(true).setLimit(160).build())
 247       .build());
 248     addIndex(context, tableName, "uniq_alm_settings", true, keeCol);
 249   }
 250
 251   private void createProjectAlmSettings(Context context) {
 252     String tableName = "project_alm_settings";
 253     VarcharColumnDef almSettingUuidCol = newVarcharColumnDefBuilder("alm_setting_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
 254     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build();
 255     VarcharColumnDef almRepoCol = newVarcharColumnDefBuilder("alm_repo").setIsNullable(true).setLimit(256).build();
 256     VarcharColumnDef almSlugCol = newVarcharColumnDefBuilder("alm_slug").setIsNullable(true).setLimit(256).build();
 257     BooleanColumnDef summaryCommentEnabledCol = newBooleanColumnDefBuilder("summary_comment_enabled").setIsNullable(true).build();
 258     BooleanColumnDef monorepoCol = newBooleanColumnDefBuilder("monorepo").setIsNullable(false).build();
 259
 260     context.execute(newTableBuilder(tableName)
 261       .addPkColumn(UUID_COL)
 262       .addColumn(almSettingUuidCol)
 263       .addColumn(projectUuidCol)
 264       .addColumn(almRepoCol)
 265       .addColumn(almSlugCol)
 266       .addColumn(TECHNICAL_UPDATED_AT_COL)
 267       .addColumn(TECHNICAL_CREATED_AT_COL)
 268       .addColumn(summaryCommentEnabledCol)
 269       .addColumn(monorepoCol)
 270       .build());
 271     addIndex(context, tableName, "uniq_project_alm_settings", true, projectUuidCol);
 272     addIndex(context, tableName, "project_alm_settings_alm", false, almSettingUuidCol);
 273     addIndex(context, tableName, "project_alm_settings_slug", false, almSlugCol);
 274   }
 275
 276   private void createAnalysisProperties(Context context) {
 277     String tableName = "analysis_properties";
 278     VarcharColumnDef snapshotUuidColumn = newVarcharColumnDefBuilder(ANALYSIS_UUID_COL_NAME)
 279       .setIsNullable(false)
 280       .setLimit(UUID_SIZE)
 281       .build();
 282     context.execute(
 283       newTableBuilder(tableName)
 284         .addPkColumn(UUID_COL)
 285         .addColumn(snapshotUuidColumn)
 286         .addColumn(newVarcharColumnDefBuilder("kee").setIsNullable(false).setLimit(512).build())
 287         .addColumn(newVarcharColumnDefBuilder(TEXT_VALUE_COL_NAME).setIsNullable(true).setLimit(MAX_SIZE).build())
 288         .addColumn(newClobColumnDefBuilder().setColumnName(CLOB_VALUE_COL_NAME).setIsNullable(true).build())
 289         .addColumn(newBooleanColumnDefBuilder().setColumnName(IS_EMPTY_COL_NAME).setIsNullable(false).build())
 290         .addColumn(TECHNICAL_CREATED_AT_COL)
 291         .build());
 292     addIndex(context, tableName, "analysis_properties_analysis", false, snapshotUuidColumn);
 293   }
 294
 295   private void createAppBranchProjectBranch(Context context) {
 296     String tableName = "app_branch_project_branch";
 297     VarcharColumnDef applicationBranchUuid = newVarcharColumnDefBuilder("application_branch_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
 298     VarcharColumnDef projectBranchUuid = newVarcharColumnDefBuilder("project_branch_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
 299     VarcharColumnDef applicationUuid = newVarcharColumnDefBuilder("application_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
 300     VarcharColumnDef projectUuid = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build();
 301     context.execute(
 302       newTableBuilder(tableName)
 303         .addPkColumn(UUID_COL)
 304         .addColumn(applicationUuid)
 305         .addColumn(applicationBranchUuid)
 306         .addColumn(projectUuid)
 307         .addColumn(projectBranchUuid)
 308         .addColumn(TECHNICAL_CREATED_AT_COL)
 309         .build());
 310     addIndex(context, tableName, "uniq_app_branch_proj", true, applicationBranchUuid, projectBranchUuid);
 311     addIndex(context, tableName, "idx_abpb_app_uuid", false, applicationUuid);
 312     addIndex(context, tableName, "idx_abpb_app_branch_uuid", false, applicationBranchUuid);
 313     addIndex(context, tableName, "idx_abpb_proj_uuid", false, projectUuid);
 314     addIndex(context, tableName, "idx_abpb_proj_branch_uuid", false, projectBranchUuid);
 315   }
 316
 317   private void createAppProjects(Context context) {
 318     String tableName = "app_projects";
 319     VarcharColumnDef applicationUuid = newVarcharColumnDefBuilder("application_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
 320     VarcharColumnDef projectUuid = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build();
 321     context.execute(
 322       newTableBuilder(tableName)
 323         .addPkColumn(UUID_COL)
 324         .addColumn(applicationUuid)
 325         .addColumn(projectUuid)
 326         .addColumn(TECHNICAL_CREATED_AT_COL)
 327         .build());
 328
 329     addIndex(context, tableName, "uniq_app_projects", true, applicationUuid, projectUuid);
 330     addIndex(context, tableName, "idx_app_proj_application_uuid", false, applicationUuid);
 331     addIndex(context, tableName, "idx_app_proj_project_uuid", false, projectUuid);
 332   }
 333
 334   private void createAudits(Context context) {
 335     String tableName = "audits";
 336
 337     context.execute(newTableBuilder(tableName)
 338       .addPkColumn(UUID_COL)
 339       .addColumn(newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setIsNullable(false).setLimit(USER_UUID_SIZE).build())
 340       .addColumn(newVarcharColumnDefBuilder("user_login").setIsNullable(false).setLimit(USER_UUID_SIZE).build())
 341       .addColumn(newVarcharColumnDefBuilder("category").setIsNullable(false).setLimit(25).build())
 342       .addColumn(newVarcharColumnDefBuilder("operation").setIsNullable(false).setLimit(50).build())
 343       .addColumn(newVarcharColumnDefBuilder("new_value").setIsNullable(true).setLimit(4000).build())
 344       .addColumn(TECHNICAL_CREATED_AT_COL)
 345       .addColumn(newBooleanColumnDefBuilder("user_triggered").setIsNullable(false).setDefaultValue(true).build())
 346       .build());
 347     addIndex(context, tableName, "audits_created_at", false, TECHNICAL_CREATED_AT_COL);
 348   }
 349
 350   private void createCeActivity(Context context) {
 351     String tableName = "ce_activity";
 352     VarcharColumnDef uuidCol = UUID_COL;
 353     VarcharColumnDef mainComponentUuidCol = newVarcharColumnDefBuilder("main_component_uuid").setLimit(UUID_SIZE).setIsNullable(true).build();
 354     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build();
 355     VarcharColumnDef statusCol = newVarcharColumnDefBuilder(STATUS_COL_NAME).setLimit(15).setIsNullable(false).build();
 356     BooleanColumnDef mainIsLastCol = newBooleanColumnDefBuilder().setColumnName("main_is_last").setIsNullable(false).build();
 357     VarcharColumnDef mainIsLastKeyCol = newVarcharColumnDefBuilder("main_is_last_key").setLimit(55).setIsNullable(false).build();
 358     BooleanColumnDef isLastCol = newBooleanColumnDefBuilder().setColumnName("is_last").setIsNullable(false).build();
 359     VarcharColumnDef isLastKeyCol = newVarcharColumnDefBuilder("is_last_key").setLimit(55).setIsNullable(false).build();
 360     context.execute(
 361       newTableBuilder(tableName)
 362         .addPkColumn(uuidCol)
 363         .addColumn(newVarcharColumnDefBuilder("task_type").setLimit(15).setIsNullable(false).build())
 364         .addColumn(mainComponentUuidCol)
 365         .addColumn(componentUuidCol)
 366         .addColumn(statusCol)
 367         .addColumn(mainIsLastCol)
 368         .addColumn(mainIsLastKeyCol)
 369         .addColumn(isLastCol)
 370         .addColumn(isLastKeyCol)
 371         .addColumn(newVarcharColumnDefBuilder("submitter_uuid").setLimit(USER_UUID_SIZE).setIsNullable(true).build())
 372         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("submitted_at").setIsNullable(false).build())
 373         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("started_at").setIsNullable(true).build())
 374         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("executed_at").setIsNullable(true).build())
 375         .addColumn(newIntegerColumnDefBuilder().setColumnName("execution_count").setIsNullable(false).build())
 376         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("execution_time_ms").setIsNullable(true).build())
 377         .addColumn(newVarcharColumnDefBuilder(ANALYSIS_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(true).build())
 378         .addColumn(newVarcharColumnDefBuilder("error_message").setLimit(1_000).setIsNullable(true).build())
 379         .addColumn(newClobColumnDefBuilder().setColumnName("error_stacktrace").setIsNullable(true).build())
 380         .addColumn(newVarcharColumnDefBuilder("error_type").setLimit(20).setIsNullable(true).build())
 381         .addColumn(newVarcharColumnDefBuilder("worker_uuid").setLimit(UUID_SIZE).setIsNullable(true).build())
 382         .addColumn(TECHNICAL_CREATED_AT_COL)
 383         .addColumn(TECHNICAL_UPDATED_AT_COL)
 384         .addColumn(newVarcharColumnDefBuilder("node_name").setLimit(100).setIsNullable(true).build())
 385         .build());
 386     addIndex(context, tableName, "ce_activity_component", false, componentUuidCol);
 387     addIndex(context, tableName, "ce_activity_islast", false, isLastCol, statusCol);
 388     addIndex(context, tableName, "ce_activity_islast_key", false, isLastKeyCol);
 389     addIndex(context, tableName, "ce_activity_main_component", false, mainComponentUuidCol);
 390     addIndex(context, tableName, "ce_activity_main_islast", false, mainIsLastCol, statusCol);
 391     addIndex(context, tableName, "ce_activity_main_islast_key", false, mainIsLastKeyCol);
 392   }
 393
 394   private void createCeQueue(Context context) {
 395     String tableName = "ce_queue";
 396     VarcharColumnDef uuidCol = UUID_COL;
 397     VarcharColumnDef mainComponentUuidCol = newVarcharColumnDefBuilder("main_component_uuid").setLimit(UUID_SIZE).setIsNullable(true).build();
 398     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build();
 399     context.execute(
 400       newTableBuilder(tableName)
 401         .addPkColumn(uuidCol)
 402         .addColumn(newVarcharColumnDefBuilder("task_type").setLimit(15).setIsNullable(false).build())
 403         .addColumn(mainComponentUuidCol)
 404         .addColumn(componentUuidCol)
 405         .addColumn(newVarcharColumnDefBuilder(STATUS_COL_NAME).setLimit(15).setIsNullable(true).build())
 406         .addColumn(newVarcharColumnDefBuilder("submitter_uuid").setLimit(USER_UUID_SIZE).setIsNullable(true).build())
 407         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("started_at").setIsNullable(true).build())
 408         .addColumn(newVarcharColumnDefBuilder("worker_uuid").setLimit(UUID_SIZE).setIsNullable(true).build())
 409         .addColumn(newIntegerColumnDefBuilder().setColumnName("execution_count").setIsNullable(false).build())
 410         .addColumn(TECHNICAL_CREATED_AT_COL)
 411         .addColumn(TECHNICAL_UPDATED_AT_COL)
 412         .build());
 413     addIndex(context, tableName, "ce_queue_main_component", false, mainComponentUuidCol);
 414     addIndex(context, tableName, "ce_queue_component", false, componentUuidCol);
 415   }
 416
 417   private void createCeScannerContext(Context context) {
 418     context.execute(
 419       newTableBuilder("ce_scanner_context")
 420         .addPkColumn(newVarcharColumnDefBuilder(TASK_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build())
 421         .addColumn(newBlobColumnDefBuilder().setColumnName("context_data").setIsNullable(false).build())
 422         .addColumn(TECHNICAL_CREATED_AT_COL)
 423         .addColumn(TECHNICAL_UPDATED_AT_COL)
 424         .build());
 425   }
 426
 427   private void createCeTaskCharacteristics(Context context) {
 428     String tableName = "ce_task_characteristics";
 429     VarcharColumnDef ceTaskUuidColumn = newVarcharColumnDefBuilder(TASK_UUID_COL_NAME)
 430       .setLimit(UUID_SIZE)
 431       .setIsNullable(false)
 432       .build();
 433
 434     context.execute(
 435       newTableBuilder(tableName)
 436         .addPkColumn(UUID_COL)
 437         .addColumn(ceTaskUuidColumn)
 438         .addColumn(newVarcharColumnDefBuilder("kee").setLimit(512).setIsNullable(false).build())
 439         .addColumn(newVarcharColumnDefBuilder(TEXT_VALUE_COL_NAME).setLimit(512).setIsNullable(true).build())
 440         .build());
 441     addIndex(context, tableName, "ce_characteristics_" + ceTaskUuidColumn.getName(), false, ceTaskUuidColumn);
 442   }
 443
 444   private void createCeTaskInput(Context context) {
 445     context.execute(
 446       newTableBuilder("ce_task_input")
 447         .addPkColumn(newVarcharColumnDefBuilder(TASK_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build())
 448         .addColumn(newBlobColumnDefBuilder().setColumnName("input_data").setIsNullable(true).build())
 449         .addColumn(TECHNICAL_CREATED_AT_COL)
 450         .addColumn(TECHNICAL_UPDATED_AT_COL)
 451         .build());
 452   }
 453
 454   private void createCeTaskMessage(Context context) {
 455     String tableName = "ce_task_message";
 456     VarcharColumnDef taskUuidCol = newVarcharColumnDefBuilder(TASK_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build();
 457     VarcharColumnDef messageTypeCol = newVarcharColumnDefBuilder("message_type").setIsNullable(false).setLimit(255).build();
 458     context.execute(newTableBuilder(tableName)
 459       .addPkColumn(UUID_COL)
 460       .addColumn(taskUuidCol)
 461       .addColumn(newVarcharColumnDefBuilder("message").setIsNullable(false).setLimit(MAX_SIZE).build())
 462       .addColumn(TECHNICAL_CREATED_AT_COL)
 463       .addColumn(messageTypeCol)
 464       .build());
 465     addIndex(context, tableName, tableName + "_task", false, taskUuidCol);
 466     addIndex(context, tableName, "ctm_message_type", false, messageTypeCol);
 467   }
 468
 469   private void createComponents(Context context) {
 470     String tableName = "components";
 471     VarcharColumnDef keeCol = newVarcharColumnDefBuilder("kee").setIsNullable(true).setLimit(1000).build();
 472     VarcharColumnDef moduleUuidCol = newVarcharColumnDefBuilder("module_uuid").setIsNullable(true).setLimit(50).build();
 473     VarcharColumnDef branchUuidCol = newVarcharColumnDefBuilder(BRANCH_UUID_COL_NAME).setIsNullable(false).setLimit(50).build();
 474     VarcharColumnDef qualifierCol = newVarcharColumnDefBuilder("qualifier").setIsNullable(true).setLimit(10).build();
 475     VarcharColumnDef rootUuidCol = newVarcharColumnDefBuilder("root_uuid").setIsNullable(false).setLimit(50).build();
 476     VarcharColumnDef uuidCol = newVarcharColumnDefBuilder("uuid").setIsNullable(false).setLimit(50).build();
 477     VarcharColumnDef mainBranchProjectUuidCol = newVarcharColumnDefBuilder("main_branch_project_uuid").setIsNullable(true).setLimit(50).build();
 478
 479     context.execute(newTableBuilder(tableName)
 480       .addColumn(uuidCol)
 481       .addColumn(keeCol)
 482       .addColumn(newVarcharColumnDefBuilder("deprecated_kee").setIsNullable(true).setLimit(400).build())
 483       .addColumn(newVarcharColumnDefBuilder("name").setIsNullable(true).setLimit(2000).build())
 484       .addColumn(newVarcharColumnDefBuilder("long_name").setIsNullable(true).setLimit(2000).build())
 485       .addColumn(newVarcharColumnDefBuilder(DESCRIPTION_COL_NAME).setIsNullable(true).setLimit(2000).build())
 486       .addColumn(newBooleanColumnDefBuilder().setColumnName("enabled").setIsNullable(false).setDefaultValue(true).build())
 487       .addColumn(newVarcharColumnDefBuilder("scope").setIsNullable(true).setLimit(3).build())
 488       .addColumn(qualifierCol)
 489       .addColumn(newBooleanColumnDefBuilder().setColumnName(PRIVATE_COL_NAME).setIsNullable(false).build())
 490       .addColumn(rootUuidCol)
 491       .addColumn(newVarcharColumnDefBuilder(LANGUAGE_COL_NAME).setIsNullable(true).setLimit(20).build())
 492       .addColumn(newVarcharColumnDefBuilder("copy_component_uuid").setIsNullable(true).setLimit(50).build())
 493       .addColumn(newVarcharColumnDefBuilder("path").setIsNullable(true).setLimit(2000).build())
 494       .addColumn(newVarcharColumnDefBuilder("uuid_path").setIsNullable(false).setLimit(1500).build())
 495       .addColumn(branchUuidCol)
 496       .addColumn(moduleUuidCol)
 497       .addColumn(newVarcharColumnDefBuilder("module_uuid_path").setIsNullable(true).setLimit(1500).build())
 498       .addColumn(mainBranchProjectUuidCol)
 499       .addColumn(newBooleanColumnDefBuilder().setColumnName("b_changed").setIsNullable(true).build())
 500       .addColumn(newVarcharColumnDefBuilder("b_name").setIsNullable(true).setLimit(500).build())
 501       .addColumn(newVarcharColumnDefBuilder("b_long_name").setIsNullable(true).setLimit(500).build())
 502       .addColumn(newVarcharColumnDefBuilder("b_description").setIsNullable(true).setLimit(2000).build())
 503       .addColumn(newBooleanColumnDefBuilder().setColumnName("b_enabled").setIsNullable(true).build())
 504       .addColumn(newVarcharColumnDefBuilder("b_qualifier").setIsNullable(true).setLimit(10).build())
 505       .addColumn(newVarcharColumnDefBuilder("b_language").setIsNullable(true).setLimit(20).build())
 506       .addColumn(newVarcharColumnDefBuilder("b_copy_component_uuid").setIsNullable(true).setLimit(50).build())
 507       .addColumn(newVarcharColumnDefBuilder("b_path").setIsNullable(true).setLimit(2000).build())
 508       .addColumn(newVarcharColumnDefBuilder("b_uuid_path").setIsNullable(true).setLimit(1500).build())
 509       .addColumn(newVarcharColumnDefBuilder("b_module_uuid").setIsNullable(true).setLimit(50).build())
 510       .addColumn(newVarcharColumnDefBuilder("b_module_uuid_path").setIsNullable(true).setLimit(1500).build())
 511       .addColumn(newTimestampColumnDefBuilder().setColumnName(CREATED_AT_COL_NAME).setIsNullable(true).build())
 512       .build());
 513
 514     addIndex(context, tableName, "projects_module_uuid", false, moduleUuidCol);
 515     addIndex(context, tableName, "projects_qualifier", false, qualifierCol);
 516     addIndex(context, tableName, "projects_root_uuid", false, rootUuidCol);
 517     addIndex(context, tableName, "idx_main_branch_prj_uuid", false, mainBranchProjectUuidCol);
 518     addIndex(context, tableName, "components_uuid", true, uuidCol);
 519     addIndex(context, tableName, "components_branch_uuid", false, branchUuidCol);
 520     addIndex(context, tableName, "components_kee_branch_uuid", true, keeCol, branchUuidCol);
 521   }
 522
 523   private void createDefaultQProfiles(Context context) {
 524     String tableName = "default_qprofiles";
 525     VarcharColumnDef profileUuidColumn = newVarcharColumnDefBuilder(QPROFILE_UUID_COL_NAME)
 526       .setLimit(255)
 527       .setIsNullable(false)
 528       .build();
 529
 530     context.execute(
 531       newTableBuilder(tableName)
 532         .addPkColumn(newVarcharColumnDefBuilder(LANGUAGE_COL_NAME).setLimit(20).setIsNullable(false).build())
 533         .addColumn(profileUuidColumn)
 534         .addColumn(TECHNICAL_CREATED_AT_COL)
 535         .addColumn(TECHNICAL_UPDATED_AT_COL)
 536         .build());
 537     addIndex(context, tableName, "uniq_default_qprofiles_uuid", true, profileUuidColumn);
 538   }
 539
 540   private void createDeprecatedRuleKeys(Context context) {
 541     String tableName = "deprecated_rule_keys";
 542     VarcharColumnDef ruleUuidCol = newVarcharColumnDefBuilder(RULE_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build();
 543     VarcharColumnDef oldRepositoryKeyCol = newVarcharColumnDefBuilder("old_repository_key").setIsNullable(false).setLimit(255).build();
 544     VarcharColumnDef oldRuleKeyCol = newVarcharColumnDefBuilder("old_rule_key").setIsNullable(false).setLimit(200).build();
 545     context.execute(newTableBuilder(tableName)
 546       .addPkColumn(UUID_COL)
 547       .addColumn(oldRepositoryKeyCol)
 548       .addColumn(oldRuleKeyCol)
 549       .addColumn(TECHNICAL_CREATED_AT_COL)
 550       .addColumn(ruleUuidCol)
 551       .build());
 552     addIndex(context, tableName, "uniq_deprecated_rule_keys", true, oldRepositoryKeyCol, oldRuleKeyCol);
 553     addIndex(context, tableName, "rule_uuid_deprecated_rule_keys", false, ruleUuidCol);
 554   }
 555
 556   private void createDuplicationsIndex(Context context) {
 557     String tableName = "duplications_index";
 558     VarcharColumnDef hashCol = newVarcharColumnDefBuilder("hash").setLimit(50).setIsNullable(false).build();
 559     VarcharColumnDef analysisUuidCol = newVarcharColumnDefBuilder(ANALYSIS_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
 560     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
 561     context.execute(
 562       newTableBuilder(tableName)
 563         .addPkColumn(UUID_COL)
 564         .addColumn(analysisUuidCol)
 565         .addColumn(componentUuidCol)
 566         .addColumn(hashCol)
 567         .addColumn(newIntegerColumnDefBuilder().setColumnName("index_in_file").setIsNullable(false).build())
 568         .addColumn(newIntegerColumnDefBuilder().setColumnName("start_line").setIsNullable(false).build())
 569         .addColumn(newIntegerColumnDefBuilder().setColumnName("end_line").setIsNullable(false).build())
 570         .build());
 571
 572     addIndex(context, tableName, "duplications_index_hash", false, hashCol);
 573     addIndex(context, tableName, "duplication_analysis_component", false, analysisUuidCol, componentUuidCol);
 574   }
 575
 576   private void createEsQueue(Context context) {
 577     String tableName = "es_queue";
 578     BigIntegerColumnDef createdAtCol = TECHNICAL_CREATED_AT_COL;
 579     context.execute(
 580       newTableBuilder(tableName)
 581         .addPkColumn(UUID_COL)
 582         .addColumn(newVarcharColumnDefBuilder("doc_type").setIsNullable(false).setLimit(40).build())
 583         .addColumn(newVarcharColumnDefBuilder("doc_id").setIsNullable(false).setLimit(MAX_SIZE).build())
 584         .addColumn(newVarcharColumnDefBuilder("doc_id_type").setIsNullable(true).setLimit(20).build())
 585         .addColumn(newVarcharColumnDefBuilder("doc_routing").setIsNullable(true).setLimit(MAX_SIZE).build())
 586         .addColumn(createdAtCol)
 587         .build());
 588     addIndex(context, tableName, "es_queue_created_at", false, createdAtCol);
 589   }
 590
 591   private void createEventComponentChanges(Context context) {
 592     String tableName = "event_component_changes";
 593     VarcharColumnDef eventUuidCol = newVarcharColumnDefBuilder("event_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
 594     VarcharColumnDef eventComponentUuidCol = newVarcharColumnDefBuilder("event_component_uuid").setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build();
 595     VarcharColumnDef eventAnalysisUuidCol = newVarcharColumnDefBuilder("event_analysis_uuid").setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build();
 596     VarcharColumnDef changeCategoryCol = newVarcharColumnDefBuilder("change_category").setIsNullable(false).setLimit(12).build();
 597     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build();
 598     context.execute(newTableBuilder(tableName)
 599       .addPkColumn(UUID_COL)
 600       .addColumn(eventUuidCol)
 601       .addColumn(eventComponentUuidCol)
 602       .addColumn(eventAnalysisUuidCol)
 603       .addColumn(changeCategoryCol)
 604       .addColumn(componentUuidCol)
 605       .addColumn(newVarcharColumnDefBuilder("component_key").setIsNullable(false).setLimit(400).build())
 606       .addColumn(newVarcharColumnDefBuilder("component_name").setIsNullable(false).setLimit(2000).build())
 607       .addColumn(newVarcharColumnDefBuilder("component_branch_key").setIsNullable(true).setLimit(255).build())
 608       .addColumn(TECHNICAL_CREATED_AT_COL)
 609       .build());
 610     addIndex(context, tableName, tableName + UNIQUE_INDEX_SUFFIX, true, eventUuidCol, changeCategoryCol, componentUuidCol);
 611     addIndex(context, tableName, "event_cpnt_changes_cpnt", false, eventComponentUuidCol);
 612     addIndex(context, tableName, "event_cpnt_changes_analysis", false, eventAnalysisUuidCol);
 613   }
 614
 615   private void createEvents(Context context) {
 616     String tableName = "events";
 617     VarcharColumnDef uuidCol = UUID_COL;
 618     VarcharColumnDef analysisUuidCol = newVarcharColumnDefBuilder(ANALYSIS_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
 619     VarcharColumnDef componentUuid = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
 620     context.execute(
 621       newTableBuilder(tableName)
 622         .addPkColumn(uuidCol)
 623         .addColumn(analysisUuidCol)
 624         .addColumn(newVarcharColumnDefBuilder("name").setLimit(400).setIsNullable(true).build())
 625         .addColumn(newVarcharColumnDefBuilder("category").setLimit(50).build())
 626         .addColumn(newVarcharColumnDefBuilder(DESCRIPTION_COL_NAME).setLimit(MAX_SIZE).build())
 627         .addColumn(newVarcharColumnDefBuilder("event_data").setLimit(MAX_SIZE).build())
 628         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("event_date").setIsNullable(false).build())
 629         .addColumn(TECHNICAL_CREATED_AT_COL)
 630         .addColumn(componentUuid)
 631         .build());
 632     addIndex(context, tableName, "events_analysis", false, analysisUuidCol);
 633     addIndex(context, tableName, "events_component_uuid", false, componentUuid);
 634   }
 635
 636   private void createFileSources(Context context) {
 637     String tableName = "file_sources";
 638     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
 639     BigIntegerColumnDef updatedAtCol = TECHNICAL_UPDATED_AT_COL;
 640     VarcharColumnDef fileUuidCol = newVarcharColumnDefBuilder("file_uuid").setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
 641     context.execute(
 642       newTableBuilder(tableName)
 643         .addPkColumn(UUID_COL)
 644         .addColumn(projectUuidCol)
 645         .addColumn(fileUuidCol)
 646         .addColumn(newClobColumnDefBuilder().setColumnName("line_hashes").setIsNullable(true).build())
 647         .addColumn(newIntegerColumnDefBuilder().setColumnName("line_hashes_version").setIsNullable(true).build())
 648         .addColumn(newVarcharColumnDefBuilder("data_hash").setLimit(50).setIsNullable(true).build())
 649         .addColumn(newVarcharColumnDefBuilder("src_hash").setLimit(50).setIsNullable(true).build())
 650         .addColumn(newVarcharColumnDefBuilder("revision").setLimit(100).setIsNullable(true).build())
 651         .addColumn(newIntegerColumnDefBuilder().setColumnName("line_count").setIsNullable(false).build())
 652         .addColumn(newBlobColumnDefBuilder().setColumnName("binary_data").setIsNullable(true).build())
 653         .addColumn(TECHNICAL_CREATED_AT_COL)
 654         .addColumn(updatedAtCol)
 655         .build());
 656     addIndex(context, tableName, "file_sources_file_uuid", true, fileUuidCol);
 657     addIndex(context, tableName, "file_sources_project_uuid", false, projectUuidCol);
 658     addIndex(context, tableName, "file_sources_updated_at", false, updatedAtCol);
 659   }
 660
 661   private void createGroupRoles(Context context) {
 662     String tableName = "group_roles";
 663     VarcharColumnDef roleCol = newVarcharColumnDefBuilder("role").setLimit(64).setIsNullable(false).build();
 664     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setIsNullable(true).setLimit(UUID_SIZE).build();
 665     VarcharColumnDef groupUuidCol = newVarcharColumnDefBuilder(GROUP_UUID_COL_NAME).setIsNullable(true).setLimit(UUID_SIZE).build();
 666     context.execute(
 667       newTableBuilder(tableName)
 668         .addPkColumn(UUID_COL)
 669         .addColumn(roleCol)
 670         .addColumn(componentUuidCol)
 671         .addColumn(groupUuidCol)
 672         .build());
 673     addIndex(context, tableName, "group_roles_component_uuid", false, componentUuidCol);
 674     addIndex(context, tableName, "uniq_group_roles", true, groupUuidCol, componentUuidCol, roleCol);
 675   }
 676
 677   private void createGroups(Context context) {
 678     String tableName = "groups";
 679     VarcharColumnDef nameCol = newVarcharColumnDefBuilder("name").setLimit(500).setIsNullable(false).build();
 680     context.execute(
 681       newTableBuilder(tableName)
 682         .addPkColumn(UUID_COL)
 683         .addColumn(nameCol)
 684         .addColumn(newVarcharColumnDefBuilder(DESCRIPTION_COL_NAME).setLimit(200).setIsNullable(true).build())
 685         .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL)
 686         .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL)
 687         .build());
 688     addIndex(context, tableName, "uniq_groups_name", true, nameCol);
 689   }
 690
 691   private void createGroupsUsers(Context context) {
 692     String tableName = "groups_users";
 693     VarcharColumnDef groupUuidCol = newVarcharColumnDefBuilder(GROUP_UUID_COL_NAME).setLimit(40).setIsNullable(false).build();
 694     VarcharColumnDef userUuidCol = newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(255).setIsNullable(false).build();
 695     context.execute(
 696       newTableBuilder(tableName)
 697         .addColumn(groupUuidCol)
 698         .addColumn(userUuidCol)
 699         .build());
 700     addIndex(context, tableName, "index_groups_users_group_uuid", false, groupUuidCol);
 701     addIndex(context, tableName, "index_groups_users_user_uuid", false, userUuidCol);
 702     addIndex(context, tableName, "groups_users_unique", true, userUuidCol, groupUuidCol);
 703   }
 704
 705   private void createInternalComponentProps(Context context) {
 706     String tableName = "internal_component_props";
 707     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build();
 708     VarcharColumnDef keeCol = newVarcharColumnDefBuilder("kee").setIsNullable(false).setLimit(512).build();
 709     context.execute(newTableBuilder(tableName)
 710       .addPkColumn(UUID_COL)
 711       .addColumn(componentUuidCol)
 712       .addColumn(keeCol)
 713       .addColumn(newVarcharColumnDefBuilder(VALUE_COL_NAME).setIsNullable(true).setLimit(MAX_SIZE).build())
 714       .addColumn(TECHNICAL_UPDATED_AT_COL)
 715       .addColumn(TECHNICAL_CREATED_AT_COL)
 716       .build());
 717     addIndex(context, tableName, "unique_component_uuid_kee", true, componentUuidCol, keeCol);
 718   }
 719
 720   private void createInternalProperties(Context context) {
 721     context.execute(
 722       newTableBuilder("internal_properties")
 723         .addPkColumn(newVarcharColumnDefBuilder("kee").setLimit(20).setIsNullable(false).build())
 724         .addColumn(newBooleanColumnDefBuilder().setColumnName(IS_EMPTY_COL_NAME).setIsNullable(false).build())
 725         .addColumn(newVarcharColumnDefBuilder().setColumnName(TEXT_VALUE_COL_NAME).setLimit(MAX_SIZE).setIgnoreOracleUnit(true).build())
 726         .addColumn(newClobColumnDefBuilder().setColumnName(CLOB_VALUE_COL_NAME).setIsNullable(true).build())
 727         .addColumn(TECHNICAL_CREATED_AT_COL)
 728         .build());
 729   }
 730
 731   private void createIssueChanges(Context context) {
 732     String tableName = "issue_changes";
 733     VarcharColumnDef issueKeyCol = newVarcharColumnDefBuilder("issue_key").setLimit(50).setIsNullable(false).build();
 734     VarcharColumnDef keeCol = newVarcharColumnDefBuilder("kee").setLimit(50).build();
 735     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(50).setIsNullable(false).build();
 736     VarcharColumnDef changeTypeCol = newVarcharColumnDefBuilder("change_type").setLimit(20).build();
 737     context.execute(
 738       newTableBuilder(tableName)
 739         .addPkColumn(UUID_COL)
 740         .addColumn(keeCol)
 741         .addColumn(issueKeyCol)
 742         .addColumn(newVarcharColumnDefBuilder("user_login").setLimit(USER_UUID_SIZE).build())
 743         .addColumn(changeTypeCol)
 744         .addColumn(newClobColumnDefBuilder().setColumnName("change_data").build())
 745         .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL)
 746         .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL)
 747         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("issue_change_creation_date").build())
 748         .addColumn(projectUuidCol)
 749         .build());
 750     addIndex(context, tableName, "issue_changes_issue_key", false, issueKeyCol);
 751     addIndex(context, tableName, "issue_changes_kee", false, keeCol);
 752     addIndex(context, tableName, "issue_changes_project_uuid", false, projectUuidCol);
 753     addIndex(context, tableName, "issue_changes_issue_key_type", false, issueKeyCol, changeTypeCol);
 754   }
 755
 756   private void createIssues(Context context) {
 757     var tableName = "issues";
 758     VarcharColumnDef assigneeCol = newVarcharColumnDefBuilder("assignee").setLimit(USER_UUID_SIZE).build();
 759     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setLimit(50).build();
 760     BigIntegerColumnDef issueCreationDateCol = newBigIntegerColumnDefBuilder().setColumnName("issue_creation_date").build();
 761     VarcharColumnDef keeCol = newVarcharColumnDefBuilder("kee").setLimit(50).setIsNullable(false).build();
 762     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(50).build();
 763     VarcharColumnDef resolutionCol = newVarcharColumnDefBuilder("resolution").setLimit(20).build();
 764     VarcharColumnDef ruleUuidCol = newVarcharColumnDefBuilder(RULE_UUID_COL_NAME).setLimit(40).setIsNullable(true).build();
 765     BigIntegerColumnDef updatedAtCol = NULLABLE_TECHNICAL_UPDATED_AT_COL;
 766     context.execute(
 767       newTableBuilder(tableName)
 768         .addPkColumn(keeCol)
 769         .addColumn(ruleUuidCol)
 770         .addColumn(newVarcharColumnDefBuilder("severity").setLimit(10).build())
 771         .addColumn(newBooleanColumnDefBuilder().setColumnName("manual_severity").setIsNullable(false).build())
 772         // unit has been fixed in SonarQube 5.6 (see migration 1151, SONAR-7493)
 773         .addColumn(newVarcharColumnDefBuilder("message").setLimit(MAX_SIZE).build())
 774         .addColumn(newIntegerColumnDefBuilder().setColumnName("line").build())
 775         .addColumn(newDecimalColumnDefBuilder().setColumnName("gap").setPrecision(30).setScale(20).build())
 776         .addColumn(newVarcharColumnDefBuilder(STATUS_COL_NAME).setLimit(20).build())
 777         .addColumn(resolutionCol)
 778         .addColumn(newVarcharColumnDefBuilder("checksum").setLimit(1000).build())
 779         .addColumn(assigneeCol)
 780         .addColumn(newVarcharColumnDefBuilder("author_login").setLimit(255).build())
 781         .addColumn(newIntegerColumnDefBuilder().setColumnName("effort").build())
 782         .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL)
 783         .addColumn(updatedAtCol)
 784         .addColumn(issueCreationDateCol)
 785         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("issue_update_date").build())
 786         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("issue_close_date").build())
 787         .addColumn(newVarcharColumnDefBuilder("tags").setLimit(MAX_SIZE).build())
 788         .addColumn(componentUuidCol)
 789         .addColumn(projectUuidCol)
 790         .addColumn(newBlobColumnDefBuilder().setColumnName("locations").build())
 791         .addColumn(new TinyIntColumnDef.Builder().setColumnName("issue_type").build())
 792         .addColumn(newBooleanColumnDefBuilder().setColumnName("from_hotspot").setIsNullable(true).build())
 793         .addColumn(newBooleanColumnDefBuilder().setColumnName("quick_fix_available").setIsNullable(true).build())
 794         .addColumn(newVarcharColumnDefBuilder("rule_description_context_key").setLimit(50).build())
 795         .addColumn(newBlobColumnDefBuilder().setColumnName("message_formattings").build())
 796         .build());
 797
 798     addIndex(context, tableName, "issues_assignee", false, assigneeCol);
 799     addIndex(context, tableName, "issues_component_uuid", false, componentUuidCol);
 800     addIndex(context, tableName, "issues_creation_date", false, issueCreationDateCol);
 801     addIndex(context, tableName, "issues_project_uuid", false, projectUuidCol);
 802     addIndex(context, tableName, "issues_resolution", false, resolutionCol);
 803     addIndex(context, tableName, "issues_updated_at", false, updatedAtCol);
 804     addIndex(context, tableName, "issues_rule_uuid", false, ruleUuidCol);
 805   }
 806
 807   private void createLiveMeasures(Context context) {
 808     String tableName = "live_measures";
 809     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build();
 810     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build();
 811     VarcharColumnDef metricUuidCol = newVarcharColumnDefBuilder(METRIC_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
 812     context.execute(newTableBuilder(tableName)
 813       .addPkColumn(UUID_COL)
 814       .addColumn(projectUuidCol)
 815       .addColumn(componentUuidCol)
 816       .addColumn(metricUuidCol)
 817       .addColumn(newDecimalColumnDefBuilder().setColumnName(VALUE_COL_NAME).setPrecision(38).setScale(20).build())
 818       .addColumn(newVarcharColumnDefBuilder(TEXT_VALUE_COL_NAME).setIsNullable(true).setLimit(MAX_SIZE).build())
 819       .addColumn(newBlobColumnDefBuilder().setColumnName("measure_data").setIsNullable(true).build())
 820       .addColumn(newVarcharColumnDefBuilder("update_marker").setIsNullable(true).setLimit(UUID_SIZE).build())
 821       .addColumn(TECHNICAL_CREATED_AT_COL)
 822       .addColumn(TECHNICAL_UPDATED_AT_COL)
 823       .build());
 824     addIndex(context, tableName, "live_measures_project", false, projectUuidCol);
 825     addIndex(context, tableName, "live_measures_component", true, componentUuidCol, metricUuidCol);
 826   }
 827
 828   private void createMetrics(Context context) {
 829     String tableName = "metrics";
 830     VarcharColumnDef nameCol = newVarcharColumnDefBuilder("name").setLimit(64).setIsNullable(false).build();
 831     context.execute(
 832       newTableBuilder(tableName)
 833         .addPkColumn(UUID_COL)
 834         .addColumn(nameCol)
 835         .addColumn(newVarcharColumnDefBuilder(DESCRIPTION_COL_NAME).setLimit(255).build())
 836         .addColumn(newIntegerColumnDefBuilder().setColumnName("direction").setIsNullable(false).setDefaultValue(0).build())
 837         .addColumn(newVarcharColumnDefBuilder("domain").setLimit(64).build())
 838         .addColumn(newVarcharColumnDefBuilder("short_name").setLimit(64).build())
 839         .addColumn(newBooleanColumnDefBuilder().setColumnName("qualitative").setDefaultValue(false).setIsNullable(false).build())
 840         .addColumn(newVarcharColumnDefBuilder("val_type").setLimit(8).build())
 841         .addColumn(newBooleanColumnDefBuilder().setColumnName("enabled").setDefaultValue(true).build())
 842         .addColumn(newDecimalColumnDefBuilder().setColumnName("worst_value").setPrecision(38).setScale(20).build())
 843         .addColumn(newDecimalColumnDefBuilder().setColumnName("best_value").setPrecision(38).setScale(20).build())
 844         .addColumn(newBooleanColumnDefBuilder().setColumnName("optimized_best_value").build())
 845         .addColumn(newBooleanColumnDefBuilder().setColumnName("hidden").build())
 846         .addColumn(newBooleanColumnDefBuilder().setColumnName("delete_historical_data").build())
 847         .addColumn(newIntegerColumnDefBuilder().setColumnName("decimal_scale").build())
 848         .build());
 849     addIndex(context, tableName, "metrics_unique_name", true, nameCol);
 850   }
 851
 852   private void createNewCodePeriods(Context context) {
 853     String tableName = "new_code_periods";
 854     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build();
 855     VarcharColumnDef branchUuidCol = newVarcharColumnDefBuilder(BRANCH_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build();
 856     VarcharColumnDef typeCol = newVarcharColumnDefBuilder("type").setLimit(30).setIsNullable(false).build();
 857     VarcharColumnDef valueCol = newVarcharColumnDefBuilder(VALUE_COL_NAME).setLimit(255).setIsNullable(true).build();
 858     context.execute(
 859       newTableBuilder(tableName)
 860         .addPkColumn(UUID_COL)
 861         .addColumn(projectUuidCol)
 862         .addColumn(branchUuidCol)
 863         .addColumn(typeCol)
 864         .addColumn(valueCol)
 865         .addColumn(TECHNICAL_UPDATED_AT_COL)
 866         .addColumn(TECHNICAL_CREATED_AT_COL)
 867         .build());
 868
 869     addIndex(context, tableName, "uniq_new_code_periods", true, projectUuidCol, branchUuidCol);
 870     addIndex(context, tableName, "idx_ncp_type", false, typeCol);
 871     addIndex(context, tableName, "idx_ncp_value", false, valueCol);
 872   }
 873
 874   private void createNewCodeReferenceIssues(Context context) {
 875     String tableName = "new_code_reference_issues";
 876     VarcharColumnDef issueKeyCol = newVarcharColumnDefBuilder("issue_key").setLimit(50).setIsNullable(false).build();
 877     context.execute(
 878       newTableBuilder(tableName)
 879         .addPkColumn(UUID_COL)
 880         .addColumn(issueKeyCol)
 881         .addColumn(TECHNICAL_CREATED_AT_COL)
 882         .build());
 883
 884     addIndex(context, tableName, "uniq_new_code_reference_issues", true, issueKeyCol);
 885   }
 886
 887   private void createNotifications(Context context) {
 888     context.execute(
 889       newTableBuilder("notifications")
 890         .addPkColumn(UUID_COL)
 891         .addColumn(newBlobColumnDefBuilder().setColumnName("data").build())
 892         .addColumn(TECHNICAL_CREATED_AT_COL)
 893         .build());
 894   }
 895
 896   private void createOrgQProfiles(Context context) {
 897     String tableName = "org_qprofiles";
 898     int profileUuidSize = 255;
 899     VarcharColumnDef rulesProfileUuidCol = newVarcharColumnDefBuilder("rules_profile_uuid").setLimit(profileUuidSize).setIsNullable(false).build();
 900     VarcharColumnDef parentUuidCol = newVarcharColumnDefBuilder("parent_uuid").setLimit(profileUuidSize).setIsNullable(true).build();
 901     context.execute(
 902       newTableBuilder(tableName)
 903         .addPkColumn(newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(255).build())
 904         .addColumn(rulesProfileUuidCol)
 905         .addColumn(parentUuidCol)
 906         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("last_used").setIsNullable(true).build())
 907         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("user_updated_at").setIsNullable(true).build())
 908         .addColumn(TECHNICAL_CREATED_AT_COL)
 909         .addColumn(TECHNICAL_UPDATED_AT_COL)
 910         .build());
 911     addIndex(context, tableName, "qprofiles_rp_uuid", false, rulesProfileUuidCol);
 912     addIndex(context, tableName, "org_qprofiles_parent_uuid", false, parentUuidCol);
 913   }
 914
 915   private void createPermTemplatesGroups(Context context) {
 916     context.execute(
 917       newTableBuilder("perm_templates_groups")
 918         .addPkColumn(UUID_COL)
 919         .addColumn(newVarcharColumnDefBuilder("permission_reference").setLimit(64).setIsNullable(false).build())
 920         .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL)
 921         .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL)
 922         .addColumn(newVarcharColumnDefBuilder(TEMPLATE_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build())
 923         .addColumn(newVarcharColumnDefBuilder(GROUP_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build())
 924         .build());
 925   }
 926
 927   private void createPermTemplatesUsers(Context context) {
 928     context.execute(
 929       newTableBuilder("perm_templates_users")
 930         .addPkColumn(UUID_COL)
 931         .addColumn(newVarcharColumnDefBuilder("permission_reference").setLimit(64).setIsNullable(false).build())
 932         .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL)
 933         .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL)
 934         .addColumn(newVarcharColumnDefBuilder(TEMPLATE_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build())
 935         .addColumn(newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(USER_UUID_SIZE).setIsNullable(false).build())
 936         .build());
 937   }
 938
 939   private void createPermTemplatesCharacteristics(Context context) {
 940     String tableName = "perm_tpl_characteristics";
 941     VarcharColumnDef permissionKeyColumn = newVarcharColumnDefBuilder("permission_key").setLimit(64).setIsNullable(false).build();
 942     VarcharColumnDef templateUuidColumn = newVarcharColumnDefBuilder(TEMPLATE_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
 943     context.execute(
 944       newTableBuilder(tableName)
 945         .addPkColumn(UUID_COL)
 946         .addColumn(permissionKeyColumn)
 947         .addColumn(newBooleanColumnDefBuilder().setColumnName("with_project_creator").setIsNullable(false).setDefaultValue(false).build())
 948         .addColumn(TECHNICAL_CREATED_AT_COL)
 949         .addColumn(TECHNICAL_UPDATED_AT_COL)
 950         .addColumn(templateUuidColumn)
 951         .build());
 952
 953     addIndex(context, tableName, "uniq_perm_tpl_charac", true, templateUuidColumn, permissionKeyColumn);
 954   }
 955
 956   private void createPermissionTemplates(Context context) {
 957     context.execute(
 958       newTableBuilder("permission_templates")
 959         .addPkColumn(UUID_COL)
 960         .addColumn(newVarcharColumnDefBuilder("name").setLimit(100).setIsNullable(false).build())
 961         .addColumn(newVarcharColumnDefBuilder(DESCRIPTION_COL_NAME).setLimit(MAX_SIZE).build())
 962         .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL)
 963         .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL)
 964         .addColumn(newVarcharColumnDefBuilder("key_pattern").setLimit(500).build())
 965         .build());
 966   }
 967
 968   private void createPlugins(Context context) {
 969     int pluginKeyMaxSize = 200;
 970     String tableName = "plugins";
 971     VarcharColumnDef keyColumn = newVarcharColumnDefBuilder("kee").setLimit(pluginKeyMaxSize).setIsNullable(false).build();
 972     context.execute(
 973       newTableBuilder(tableName)
 974         .addPkColumn(UUID_COL)
 975         .addColumn(keyColumn)
 976         .addColumn(newVarcharColumnDefBuilder("base_plugin_key").setLimit(pluginKeyMaxSize).setIsNullable(true).build())
 977         .addColumn(newVarcharColumnDefBuilder("file_hash").setLimit(200).setIsNullable(false).build())
 978         .addColumn(TECHNICAL_CREATED_AT_COL)
 979         .addColumn(TECHNICAL_UPDATED_AT_COL)
 980         .addColumn(newVarcharColumnDefBuilder("type").setLimit(10).setIsNullable(false).build())
 981         .addColumn(newBooleanColumnDefBuilder("removed").setDefaultValue(false).setIsNullable(false).build())
 982         .build());
 983     addIndex(context, tableName, "plugins_key", true, keyColumn);
 984   }
 985
 986   private void createPortfolioProjBranches(Context context) {
 987     String tableName = "portfolio_proj_branches";
 988     context.execute(new CreateTableBuilder(getDialect(), tableName)
 989       .addPkColumn(UUID_COL)
 990       .addColumn(newVarcharColumnDefBuilder().setColumnName("portfolio_project_uuid").setIsNullable(false).setLimit(UUID_SIZE).build())
 991       .addColumn(newVarcharColumnDefBuilder().setColumnName(BRANCH_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build())
 992       .addColumn(TECHNICAL_CREATED_AT_COL)
 993       .build());
 994   }
 995
 996   private void createPortfolioProjects(Context context) {
 997     String tableName = "portfolio_projects";
 998     VarcharColumnDef portfolioUuidColumn = newVarcharColumnDefBuilder().setColumnName("portfolio_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
 999     VarcharColumnDef projectUuidColumn = newVarcharColumnDefBuilder().setColumnName(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build();
1000     context.execute(new CreateTableBuilder(getDialect(), tableName)
1001       .addPkColumn(UUID_COL)
1002       .addColumn(portfolioUuidColumn)
1003       .addColumn(projectUuidColumn)
1004       .addColumn(TECHNICAL_CREATED_AT_COL)
1005       .build());
1006     addIndex(context, tableName, "uniq_portfolio_projects", true, portfolioUuidColumn, projectUuidColumn);
1007   }
1008
1009   private void createPortfolioReferences(Context context) {
1010     String tableName = "portfolio_references";
1011     VarcharColumnDef portfolioUuidColumn = newVarcharColumnDefBuilder().setColumnName("portfolio_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
1012     VarcharColumnDef referenceUuidColumn = newVarcharColumnDefBuilder().setColumnName("reference_uuid").setIsNullable(false).setLimit(UUID_SIZE).build();
1013     VarcharColumnDef branchUuidColumn = newVarcharColumnDefBuilder().setColumnName(BRANCH_UUID_COL_NAME).setIsNullable(true).setLimit(255).build();
1014     context.execute(new CreateTableBuilder(getDialect(), tableName)
1015       .addPkColumn(UUID_COL)
1016       .addColumn(portfolioUuidColumn)
1017       .addColumn(referenceUuidColumn)
1018       .addColumn(TECHNICAL_CREATED_AT_COL)
1019       .addColumn(branchUuidColumn)
1020       .build());
1021     addIndex(context, tableName, "uniq_portfolio_references", true, portfolioUuidColumn, referenceUuidColumn, branchUuidColumn);
1022   }
1023
1024   private void createPortfolios(Context context) {
1025     String tableName = "portfolios";
1026     VarcharColumnDef keeColumn = newVarcharColumnDefBuilder().setColumnName("kee").setIsNullable(false).setLimit(400).build();
1027     context.execute(new CreateTableBuilder(getDialect(), tableName)
1028       .addPkColumn(UUID_COL)
1029       .addColumn(keeColumn)
1030       .addColumn(newVarcharColumnDefBuilder().setColumnName("name").setIsNullable(false).setLimit(2000).build())
1031       .addColumn(newVarcharColumnDefBuilder().setColumnName(DESCRIPTION_COL_NAME).setIsNullable(true).setLimit(2000).build())
1032       .addColumn(newVarcharColumnDefBuilder().setColumnName("root_uuid").setIsNullable(false).setLimit(UUID_SIZE).build())
1033       .addColumn(newVarcharColumnDefBuilder().setColumnName("parent_uuid").setIsNullable(true).setLimit(UUID_SIZE).build())
1034       .addColumn(newBooleanColumnDefBuilder(PRIVATE_COL_NAME).setIsNullable(false).build())
1035       .addColumn(newVarcharColumnDefBuilder().setColumnName("selection_mode").setIsNullable(false).setLimit(50).build())
1036       .addColumn(newVarcharColumnDefBuilder().setColumnName("selection_expression").setIsNullable(true).setLimit(4000).build())
1037       .addColumn(TECHNICAL_CREATED_AT_COL)
1038       .addColumn(TECHNICAL_UPDATED_AT_COL)
1039       .addColumn(newVarcharColumnDefBuilder().setColumnName("branch_key").setIsNullable(true).setLimit(255).build())
1040       .build());
1041     addIndex(context, tableName, "uniq_portfolios_kee", true, keeColumn);
1042   }
1043
1044   private void createProjectBadgeToken(Context context) {
1045     String tableName = "project_badge_token";
1046     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build();
1047     context.execute(
1048       newTableBuilder(tableName)
1049         .addPkColumn(UUID_COL)
1050         .addColumn(newVarcharColumnDefBuilder("token").setIsNullable(false).setLimit(255).build())
1051         .addColumn(projectUuidCol)
1052         .addColumn(TECHNICAL_CREATED_AT_COL)
1053         .addColumn(TECHNICAL_UPDATED_AT_COL)
1054         .build());
1055     addIndex(context, tableName, "uniq_project_badge_token", true, projectUuidCol);
1056   }
1057
1058   private void createProjectBranches(Context context) {
1059     String tableName = "project_branches";
1060     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build();
1061     VarcharColumnDef keeCol = newVarcharColumnDefBuilder("kee").setIsNullable(false).setLimit(255).build();
1062     VarcharColumnDef branchTypeCol = newVarcharColumnDefBuilder("branch_type").setIsNullable(false).setLimit(12).build();
1063     context.execute(
1064       newTableBuilder(tableName)
1065         .addPkColumn(newVarcharColumnDefBuilder("uuid").setIsNullable(false).setLimit(OLD_UUID_VARCHAR_SIZE).build())
1066         .addColumn(projectUuidCol)
1067         .addColumn(keeCol)
1068         .addColumn(branchTypeCol)
1069         .addColumn(newVarcharColumnDefBuilder("merge_branch_uuid").setIsNullable(true).setLimit(OLD_UUID_VARCHAR_SIZE).build())
1070         .addColumn(newBlobColumnDefBuilder().setColumnName("pull_request_binary").setIsNullable(true).build())
1071         .addColumn(newVarcharColumnDefBuilder("manual_baseline_analysis_uuid").setIsNullable(true).setLimit(UUID_SIZE).build())
1072         .addColumn(TECHNICAL_CREATED_AT_COL)
1073         .addColumn(TECHNICAL_UPDATED_AT_COL)
1074         .addColumn(newBooleanColumnDefBuilder("exclude_from_purge").setDefaultValue(false).setIsNullable(false).build())
1075         .addColumn(newBooleanColumnDefBuilder("need_issue_sync").setIsNullable(false).build())
1076         .build());
1077     addIndex(context, tableName, "uniq_project_branches", true, branchTypeCol, projectUuidCol, keeCol);
1078   }
1079
1080   private void createProjectLinks(Context context) {
1081     String tableName = "project_links";
1082     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1083     context.execute(newTableBuilder(tableName)
1084       .addPkColumn(UUID_COL)
1085       .addColumn(projectUuidCol)
1086       .addColumn(newVarcharColumnDefBuilder("link_type").setLimit(20).setIsNullable(false).build())
1087       .addColumn(newVarcharColumnDefBuilder("name").setLimit(128).setIsNullable(true).build())
1088       .addColumn(newVarcharColumnDefBuilder("href").setLimit(2048).setIsNullable(false).build())
1089       .addColumn(TECHNICAL_CREATED_AT_COL)
1090       .addColumn(TECHNICAL_UPDATED_AT_COL)
1091       .build());
1092     addIndex(context, tableName, "project_links_project", false, projectUuidCol);
1093   }
1094
1095   private void createProjectMappings(Context context) {
1096     String tableName = "project_mappings";
1097     VarcharColumnDef keyTypeCol = newVarcharColumnDefBuilder("key_type").setIsNullable(false).setLimit(200).build();
1098     VarcharColumnDef keyCol = newVarcharColumnDefBuilder("kee").setIsNullable(false).setLimit(MAX_SIZE).build();
1099     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build();
1100     context.execute(newTableBuilder(tableName)
1101       .addPkColumn(UUID_COL)
1102       .addColumn(keyTypeCol)
1103       .addColumn(keyCol)
1104       .addColumn(projectUuidCol)
1105       .addColumn(TECHNICAL_CREATED_AT_COL)
1106       .build());
1107     addIndex(context, tableName, "key_type_kee", true, keyTypeCol, keyCol);
1108     addIndex(context, tableName, PROJECT_UUID_COL_NAME, false, projectUuidCol);
1109   }
1110
1111   private void createProjectMeasures(Context context) {
1112     String tableName = "project_measures";
1113     IntegerColumnDef personIdCol = newIntegerColumnDefBuilder().setColumnName("person_id").build();
1114     VarcharColumnDef metricUuidCol = newVarcharColumnDefBuilder(METRIC_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1115     VarcharColumnDef analysisUuidCol = newVarcharColumnDefBuilder(ANALYSIS_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
1116     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
1117     context.execute(
1118       newTableBuilder(tableName)
1119         .addPkColumn(UUID_COL)
1120         .addColumn(newDecimalColumnDefBuilder().setColumnName(VALUE_COL_NAME).setPrecision(38).setScale(20).build())
1121         .addColumn(analysisUuidCol)
1122         .addColumn(componentUuidCol)
1123         .addColumn(newVarcharColumnDefBuilder(TEXT_VALUE_COL_NAME).setLimit(MAX_SIZE).build())
1124         .addColumn(newVarcharColumnDefBuilder("alert_status").setLimit(5).build())
1125         .addColumn(newVarcharColumnDefBuilder("alert_text").setLimit(MAX_SIZE).build())
1126         .addColumn(personIdCol)
1127         .addColumn(newBlobColumnDefBuilder().setColumnName("measure_data").build())
1128         .addColumn(metricUuidCol)
1129         .build());
1130     addIndex(context, tableName, "measures_component_uuid", false, componentUuidCol);
1131     addIndex(context, tableName, "measures_analysis_metric", false, analysisUuidCol, metricUuidCol);
1132     addIndex(context, tableName, "project_measures_metric", false, metricUuidCol);
1133   }
1134
1135   private void createProjectQprofiles(Context context) {
1136     String tableName = "project_qprofiles";
1137     VarcharColumnDef projectUuid = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(50).setIsNullable(false).build();
1138     VarcharColumnDef profileKey = newVarcharColumnDefBuilder("profile_key").setLimit(50).setIsNullable(false).build();
1139     context.execute(
1140       newTableBuilder(tableName)
1141         .addPkColumn(UUID_COL)
1142         .addColumn(projectUuid)
1143         .addColumn(profileKey)
1144         .build());
1145     addIndex(context, tableName, "uniq_project_qprofiles", true, projectUuid, profileKey);
1146   }
1147
1148   private void createProjects(Context context) {
1149     String tableName = "projects";
1150     VarcharColumnDef uuidCol = UUID_COL;
1151     VarcharColumnDef keeCol = newVarcharColumnDefBuilder("kee").setLimit(400).setIsNullable(false).build();
1152     VarcharColumnDef qualifierCol = newVarcharColumnDefBuilder("qualifier").setLimit(10).setIsNullable(false).build();
1153     context.execute(
1154       newTableBuilder(tableName)
1155         .addPkColumn(uuidCol)
1156         .addColumn(keeCol)
1157         .addColumn(qualifierCol)
1158         .addColumn(newVarcharColumnDefBuilder("name").setLimit(2_000).setIsNullable(true).build())
1159         .addColumn(newVarcharColumnDefBuilder(DESCRIPTION_COL_NAME).setLimit(2_000).setIsNullable(true).build())
1160         .addColumn(newBooleanColumnDefBuilder().setColumnName(PRIVATE_COL_NAME).setIsNullable(false).build())
1161         .addColumn(newVarcharColumnDefBuilder("tags").setLimit(500).setIsNullable(true).build())
1162         .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL)
1163         .addColumn(TECHNICAL_UPDATED_AT_COL)
1164         .withPkConstraintName("pk_new_projects")
1165         .build());
1166     addIndex(context, tableName, "uniq_projects_kee", true, keeCol);
1167     addIndex(context, tableName, "idx_qualifier", false, qualifierCol);
1168   }
1169
1170   private void createProjectQGates(Context context) {
1171     String tableName = "project_qgates";
1172     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1173     VarcharColumnDef qualityGateUuidCol = newVarcharColumnDefBuilder(QUALITY_GATE_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1174     context.execute(
1175       newTableBuilder(tableName)
1176         .addPkColumn(projectUuidCol)
1177         .addColumn(qualityGateUuidCol)
1178         .build());
1179     addIndex(context, tableName, "uniq_project_qgates", true, projectUuidCol, qualityGateUuidCol);
1180   }
1181
1182   private void createProperties(Context context) {
1183     String tableName = "properties";
1184     VarcharColumnDef propKey = newVarcharColumnDefBuilder("prop_key").setLimit(512).setIsNullable(false).build();
1185     context.execute(newTableBuilder(tableName)
1186       .addPkColumn(UUID_COL)
1187       .addColumn(propKey)
1188       .addColumn(newBooleanColumnDefBuilder().setColumnName(IS_EMPTY_COL_NAME).setIsNullable(false).build())
1189       .addColumn(newVarcharColumnDefBuilder(TEXT_VALUE_COL_NAME).setLimit(MAX_SIZE).build())
1190       .addColumn(newClobColumnDefBuilder().setColumnName(CLOB_VALUE_COL_NAME).setIsNullable(true).build())
1191       .addColumn(TECHNICAL_CREATED_AT_COL)
1192       .addColumn(newVarcharColumnDefBuilder().setColumnName(COMPONENT_UUID_COL_NAME).setIsNullable(true).setLimit(UUID_SIZE).build())
1193       .addColumn(newVarcharColumnDefBuilder().setColumnName(USER_UUID_COL_NAME).setIsNullable(true).setLimit(USER_UUID_SIZE).build())
1194       // table with be renamed to properties in following migration, use final constraint name right away
1195       .withPkConstraintName("pk_properties")
1196       .build());
1197     addIndex(context, tableName, "properties_key", false, propKey);
1198   }
1199
1200   private void createPushEvents(Context context) {
1201     String tableName = "push_events";
1202     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1203     context.execute(newTableBuilder(tableName)
1204       .addPkColumn(UUID_COL)
1205       .addColumn(newVarcharColumnDefBuilder("name").setLimit(40).setIsNullable(false).build())
1206       .addColumn(projectUuidCol)
1207       .addColumn(newBlobColumnDefBuilder().setColumnName("payload").setIsNullable(false).build())
1208       .addColumn(TECHNICAL_CREATED_AT_COL)
1209       .addColumn(newVarcharColumnDefBuilder(LANGUAGE_COL_NAME).setLimit(20).build())
1210       .build());
1211     addIndex(context, tableName, "idx_push_even_crea_uuid_proj", false, TECHNICAL_CREATED_AT_COL, UUID_COL, projectUuidCol);
1212   }
1213
1214   private void createQGateGroupPermissions(Context context) {
1215     String tableName = "qgate_group_permissions";
1216     VarcharColumnDef qualityGateUuidColumn = newVarcharColumnDefBuilder(QUALITY_GATE_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1217     context.execute(newTableBuilder(tableName)
1218       .addPkColumn(UUID_COL)
1219       .addColumn(qualityGateUuidColumn)
1220       .addColumn(newVarcharColumnDefBuilder(GROUP_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build())
1221       .addColumn(TECHNICAL_CREATED_AT_COL)
1222       .build());
1223     addIndex(context, tableName, "qg_groups_uuid_idx", false, qualityGateUuidColumn);
1224   }
1225
1226   private void createQGateUserPermissions(Context context) {
1227     String tableName = "qgate_user_permissions";
1228     VarcharColumnDef qualityGateUuidColumn = newVarcharColumnDefBuilder(QUALITY_GATE_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1229     context.execute(newTableBuilder(tableName)
1230       .addPkColumn(UUID_COL)
1231       .addColumn(qualityGateUuidColumn)
1232       .addColumn(newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build())
1233       .addColumn(TECHNICAL_CREATED_AT_COL)
1234       .build());
1235     addIndex(context, tableName, "quality_gate_uuid_idx", false, qualityGateUuidColumn);
1236   }
1237
1238   private void createQProfileChanges(Context context) {
1239     String tableName = "qprofile_changes";
1240     VarcharColumnDef rulesProfileUuidCol = newVarcharColumnDefBuilder("rules_profile_uuid").setLimit(255).setIsNullable(false).build();
1241     context.execute(newTableBuilder(tableName)
1242       .addPkColumn(newVarcharColumnDefBuilder("kee").setLimit(UUID_SIZE).setIsNullable(false).build())
1243       .addColumn(rulesProfileUuidCol)
1244       .addColumn(newVarcharColumnDefBuilder("change_type").setLimit(20).setIsNullable(false).build())
1245       .addColumn(newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(USER_UUID_SIZE).setIsNullable(true).build())
1246       .addColumn(newClobColumnDefBuilder().setColumnName("change_data").setIsNullable(true).build())
1247       .addColumn(TECHNICAL_CREATED_AT_COL)
1248       .build());
1249     addIndex(context, tableName, "qp_changes_rules_profile_uuid", false, rulesProfileUuidCol);
1250   }
1251
1252   private void createQProfileEditGroups(Context context) {
1253     String tableName = "qprofile_edit_groups";
1254     VarcharColumnDef qProfileUuidCol = newVarcharColumnDefBuilder(QPROFILE_UUID_COL_NAME).setIsNullable(false).setLimit(255).build();
1255     VarcharColumnDef groupUuidCol = newVarcharColumnDefBuilder(GROUP_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1256     context.execute(newTableBuilder(tableName)
1257       .addPkColumn(UUID_COL)
1258       .addColumn(qProfileUuidCol)
1259       .addColumn(TECHNICAL_CREATED_AT_COL)
1260       .addColumn(groupUuidCol)
1261       .build());
1262     addIndex(context, tableName, tableName + "_qprofile", false, qProfileUuidCol);
1263     addIndex(context, tableName, tableName + UNIQUE_INDEX_SUFFIX, true, groupUuidCol, qProfileUuidCol);
1264   }
1265
1266   private void createQProfileEditUsers(Context context) {
1267     String tableName = "qprofile_edit_users";
1268     VarcharColumnDef qProfileUuidCol = newVarcharColumnDefBuilder(QPROFILE_UUID_COL_NAME).setLimit(255).setIsNullable(false).build();
1269     VarcharColumnDef userUuidCol = newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(255).setIsNullable(false).build();
1270     context.execute(newTableBuilder(tableName)
1271       .addPkColumn(UUID_COL)
1272       .addColumn(qProfileUuidCol)
1273       .addColumn(TECHNICAL_CREATED_AT_COL)
1274       .addColumn(userUuidCol)
1275       .build());
1276     addIndex(context, tableName, tableName + "_qprofile", false, qProfileUuidCol);
1277     addIndex(context, tableName, tableName + UNIQUE_INDEX_SUFFIX, true, userUuidCol, qProfileUuidCol);
1278   }
1279
1280   private void createQualityGateConditions(Context context) {
1281     context.execute(
1282       newTableBuilder("quality_gate_conditions")
1283         .addPkColumn(UUID_COL)
1284         .addColumn(newVarcharColumnDefBuilder("operator").setLimit(3).setIsNullable(true).build())
1285         .addColumn(newVarcharColumnDefBuilder("value_error").setLimit(64).setIsNullable(true).build())
1286         .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL)
1287         .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL)
1288         .addColumn(newVarcharColumnDefBuilder(METRIC_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build())
1289         .addColumn(newVarcharColumnDefBuilder("qgate_uuid").setLimit(UUID_SIZE).setIsNullable(false).build())
1290         .build());
1291   }
1292
1293   private void createQualityGates(Context context) {
1294     context.execute(
1295       newTableBuilder("quality_gates")
1296         .addPkColumn(UUID_COL)
1297         .addColumn(newVarcharColumnDefBuilder("name").setLimit(100).setIsNullable(false).build())
1298         .addColumn(newBooleanColumnDefBuilder().setColumnName("is_built_in").setIsNullable(false).build())
1299         .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL)
1300         .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL)
1301         .build());
1302   }
1303
1304   private void createScimUsers(Context context) {
1305     String tableName = "scim_users";
1306     VarcharColumnDef userUuidCol = newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1307     context.execute(
1308       newTableBuilder(tableName)
1309         .addPkColumn(newVarcharColumnDefBuilder().setColumnName("scim_uuid").setIsNullable(false).setLimit(UUID_SIZE).build())
1310         .addColumn(userUuidCol)
1311         .build());
1312
1313     addIndex(context, tableName, "uniq_scim_users_user_uuid", true, userUuidCol);
1314   }
1315
1316   private void createSessionTokens(Context context) {
1317     String tableName = "session_tokens";
1318     VarcharColumnDef userUuidCol = newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(255).setIsNullable(false).build();
1319     context.execute(
1320       newTableBuilder(tableName)
1321         .addPkColumn(UUID_COL)
1322         .addColumn(userUuidCol)
1323         .addColumn(newBigIntegerColumnDefBuilder().setColumnName(EXPIRATION_DATE_COL_NAME).setIsNullable(false).build())
1324         .addColumn(TECHNICAL_CREATED_AT_COL)
1325         .addColumn(TECHNICAL_UPDATED_AT_COL)
1326         .build());
1327
1328     addIndex(context, tableName, "session_tokens_user_uuid", false, userUuidCol);
1329   }
1330
1331   private void createRulesRepository(Context context) {
1332     String tableName = "rule_repositories";
1333     context.execute(newTableBuilder(tableName)
1334       .addPkColumn(newVarcharColumnDefBuilder("kee").setLimit(200).setIsNullable(false).build())
1335       .addColumn(newVarcharColumnDefBuilder(LANGUAGE_COL_NAME).setLimit(20).setIsNullable(false).build())
1336       .addColumn(newVarcharColumnDefBuilder("name").setLimit(4_000).setIsNullable(false).build())
1337       .addColumn(TECHNICAL_CREATED_AT_COL)
1338       .build());
1339   }
1340
1341   private void createRuleDescSections(Context context) {
1342     String tableName = "rule_desc_sections";
1343     VarcharColumnDef ruleUuidCol = newVarcharColumnDefBuilder().setColumnName(RULE_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build();
1344     VarcharColumnDef keeCol = newVarcharColumnDefBuilder().setColumnName("kee").setIsNullable(false).setLimit(DESCRIPTION_SECTION_KEY_SIZE).build();
1345     VarcharColumnDef contextKeyCol = newVarcharColumnDefBuilder().setColumnName("context_key").setIsNullable(true).setLimit(DESCRIPTION_SECTION_KEY_SIZE).build();
1346     context.execute(newTableBuilder(tableName)
1347       .addPkColumn(newVarcharColumnDefBuilder().setColumnName("uuid").setIsNullable(false).setLimit(UUID_SIZE).build())
1348       .addColumn(ruleUuidCol)
1349       .addColumn(keeCol)
1350       .addColumn(newClobColumnDefBuilder().setColumnName("content").setIsNullable(false).build())
1351       .addColumn(contextKeyCol)
1352       .addColumn(newVarcharColumnDefBuilder().setColumnName("context_display_name").setIsNullable(true).setLimit(50).build())
1353       .build());
1354
1355     addIndex(context, tableName, "uniq_rule_desc_sections", true, ruleUuidCol, keeCol, contextKeyCol);
1356   }
1357
1358   private void createRules(Context context) {
1359     VarcharColumnDef pluginRuleKeyCol = newVarcharColumnDefBuilder("plugin_rule_key").setLimit(200).setIsNullable(false).build();
1360     VarcharColumnDef pluginNameCol = newVarcharColumnDefBuilder("plugin_name").setLimit(255).setIsNullable(false).build();
1361     context.execute(
1362       newTableBuilder("rules")
1363         .addPkColumn(UUID_COL)
1364         .addColumn(newVarcharColumnDefBuilder("name").setLimit(200).setIsNullable(true).build())
1365         .addColumn(pluginRuleKeyCol)
1366         .addColumn(newVarcharColumnDefBuilder("plugin_key").setLimit(200).setIsNullable(true).build())
1367         .addColumn(newVarcharColumnDefBuilder("plugin_config_key").setLimit(200).setIsNullable(true).build())
1368         .addColumn(pluginNameCol)
1369         .addColumn(newVarcharColumnDefBuilder("scope").setLimit(20).setIsNullable(false).build())
1370         .addColumn(newIntegerColumnDefBuilder().setColumnName("priority").setIsNullable(true).build())
1371         .addColumn(newVarcharColumnDefBuilder(STATUS_COL_NAME).setLimit(40).setIsNullable(true).build())
1372         .addColumn(newVarcharColumnDefBuilder(LANGUAGE_COL_NAME).setLimit(20).setIsNullable(true).build())
1373         .addColumn(newVarcharColumnDefBuilder("def_remediation_function").setLimit(20).setIsNullable(true).build())
1374         .addColumn(newVarcharColumnDefBuilder("def_remediation_gap_mult").setLimit(20).setIsNullable(true).build())
1375         .addColumn(newVarcharColumnDefBuilder("def_remediation_base_effort").setLimit(20).setIsNullable(true).build())
1376         .addColumn(newVarcharColumnDefBuilder("gap_description").setLimit(MAX_SIZE).setIsNullable(true).build())
1377         .addColumn(newVarcharColumnDefBuilder("system_tags").setLimit(MAX_SIZE).setIsNullable(true).build())
1378         .addColumn(newBooleanColumnDefBuilder().setColumnName("is_template").setIsNullable(false).setDefaultValue(false).build())
1379         .addColumn(newVarcharColumnDefBuilder("description_format").setLimit(20).setIsNullable(true).build())
1380         .addColumn(new TinyIntColumnDef.Builder().setColumnName("rule_type").setIsNullable(true).build())
1381         .addColumn(newVarcharColumnDefBuilder("security_standards").setIsNullable(true).setLimit(4_000).build())
1382         .addColumn(newBooleanColumnDefBuilder().setColumnName("is_ad_hoc").setIsNullable(false).build())
1383         .addColumn(newBooleanColumnDefBuilder().setColumnName("is_external").setIsNullable(false).build())
1384         .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL)
1385         .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL)
1386         .addColumn(newVarcharColumnDefBuilder(TEMPLATE_UUID_COL_NAME).setIsNullable(true).setLimit(UUID_SIZE).build())
1387         .addColumn(newClobColumnDefBuilder().setColumnName("note_data").setIsNullable(true).build())
1388         .addColumn(newVarcharColumnDefBuilder("note_user_uuid").setLimit(USER_UUID_SIZE).setIsNullable(true).build())
1389         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("note_created_at").setIsNullable(true).build())
1390         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("note_updated_at").setIsNullable(true).build())
1391         .addColumn(newVarcharColumnDefBuilder("remediation_function").setLimit(20).setIsNullable(true).build())
1392         .addColumn(newVarcharColumnDefBuilder("remediation_gap_mult").setLimit(20).setIsNullable(true).build())
1393         .addColumn(newVarcharColumnDefBuilder("remediation_base_effort").setLimit(20).setIsNullable(true).build())
1394         .addColumn(newVarcharColumnDefBuilder("tags").setLimit(4_000).setIsNullable(true).build())
1395         .addColumn(newVarcharColumnDefBuilder("ad_hoc_name").setLimit(200).setIsNullable(true).build())
1396         .addColumn(newClobColumnDefBuilder().setColumnName("ad_hoc_description").setIsNullable(true).build())
1397         .addColumn(newVarcharColumnDefBuilder("ad_hoc_severity").setLimit(10).setIsNullable(true).build())
1398         .addColumn(newTinyIntColumnDefBuilder().setColumnName("ad_hoc_type").setIsNullable(true).build())
1399         .addColumn(newVarcharColumnDefBuilder("education_principles").setLimit(255).setIsNullable(true).build())
1400         .build());
1401     addIndex(context, "rules", "rules_repo_key", true, pluginRuleKeyCol, pluginNameCol);
1402   }
1403
1404   private void createRulesParameters(Context context) {
1405     String tableName = "rules_parameters";
1406     VarcharColumnDef ruleUuidCol = newVarcharColumnDefBuilder(RULE_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1407     VarcharColumnDef nameCol = newVarcharColumnDefBuilder("name").setLimit(128).setIsNullable(false).build();
1408     context.execute(
1409       newTableBuilder(tableName)
1410         .addPkColumn(UUID_COL)
1411         .addColumn(nameCol)
1412         .addColumn(newVarcharColumnDefBuilder(DESCRIPTION_COL_NAME).setLimit(MAX_SIZE).setIsNullable(true).build())
1413         .addColumn(newVarcharColumnDefBuilder("param_type").setLimit(512).setIsNullable(false).build())
1414         .addColumn(newVarcharColumnDefBuilder("default_value").setLimit(MAX_SIZE).setIsNullable(true).build())
1415         .addColumn(ruleUuidCol)
1416         .build());
1417     addIndex(context, tableName, "rules_parameters_rule_uuid", false, ruleUuidCol);
1418     addIndex(context, tableName, "rules_parameters_unique", true, ruleUuidCol, nameCol);
1419   }
1420
1421   private void createRulesProfiles(Context context) {
1422     String tableName = "rules_profiles";
1423     context.execute(
1424       newTableBuilder(tableName)
1425         .addPkColumn(UUID_COL)
1426         .addColumn(newVarcharColumnDefBuilder("name").setLimit(100).setIsNullable(false).build())
1427         .addColumn(newVarcharColumnDefBuilder(LANGUAGE_COL_NAME).setLimit(20).setIsNullable(true).build())
1428         .addColumn(newBooleanColumnDefBuilder().setColumnName("is_built_in").setIsNullable(false).build())
1429         .addColumn(newVarcharColumnDefBuilder("rules_updated_at").setLimit(100).setIsNullable(true).build())
1430         .addColumn(DEPRECATED_TECHNICAL_CREATED_AT_COL)
1431         .addColumn(DEPRECATED_TECHNICAL_UPDATED_AT_COL)
1432         .build());
1433   }
1434
1435   private void createSamlMessageIds(Context context) {
1436     String tableName = "saml_message_ids";
1437     VarcharColumnDef messageIdCol = newVarcharColumnDefBuilder("message_id").setLimit(255).setIsNullable(false).build();
1438     context.execute(
1439       newTableBuilder(tableName)
1440         .addPkColumn(UUID_COL)
1441         .addColumn(messageIdCol)
1442         .addColumn(newBigIntegerColumnDefBuilder().setColumnName(EXPIRATION_DATE_COL_NAME).setIsNullable(false).build())
1443         .addColumn(TECHNICAL_CREATED_AT_COL)
1444         .build());
1445     addIndex(context, tableName, "saml_message_ids_unique", true, messageIdCol);
1446   }
1447
1448   private void createScannerAnalysisCache(Context context) {
1449     String tableName = "scanner_analysis_cache";
1450     context.execute(
1451       newTableBuilder(tableName)
1452         .addPkColumn(newVarcharColumnDefBuilder().setColumnName(BRANCH_UUID_COL_NAME).setIsNullable(false).setLimit(UUID_SIZE).build())
1453         .addColumn(newBlobColumnDefBuilder().setColumnName("data").setIsNullable(false).build())
1454         .build());
1455   }
1456
1457   private void createSnapshots(Context context) {
1458     String tableName = "snapshots";
1459     VarcharColumnDef uuidCol = newVarcharColumnDefBuilder("uuid").setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
1460     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setLimit(OLD_UUID_VARCHAR_SIZE).setIsNullable(false).build();
1461     context.execute(
1462       newTableBuilder(tableName)
1463         .addPkColumn(uuidCol)
1464         .addColumn(componentUuidCol)
1465         .addColumn(newVarcharColumnDefBuilder(STATUS_COL_NAME).setLimit(4).setIsNullable(false).setDefaultValue("U").build())
1466         .addColumn(newBooleanColumnDefBuilder().setColumnName("islast").setIsNullable(false).setDefaultValue(false).build())
1467         .addColumn(newVarcharColumnDefBuilder("version").setLimit(500).setIsNullable(true).build())
1468         .addColumn(newIntegerColumnDefBuilder().setColumnName("purge_status").setIsNullable(true).build())
1469         .addColumn(newVarcharColumnDefBuilder("build_string").setLimit(100).setIsNullable(true).build())
1470         .addColumn(newVarcharColumnDefBuilder("revision").setLimit(100).setIsNullable(true).build())
1471         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("build_date").setIsNullable(true).build())
1472         .addColumn(newVarcharColumnDefBuilder("period1_mode").setLimit(100).setIsNullable(true).build())
1473         .addColumn(newVarcharColumnDefBuilder("period1_param").setLimit(100).setIsNullable(true).build())
1474         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("period1_date").setIsNullable(true).build())
1475         .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL)
1476         .build());
1477     addIndex(context, tableName, "snapshot_component", false, componentUuidCol);
1478   }
1479
1480   private void createUserRoles(Context context) {
1481     String tableName = "user_roles";
1482     VarcharColumnDef componentUuidCol = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build();
1483     VarcharColumnDef userUuidCol = newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(USER_UUID_SIZE).setIsNullable(true).build();
1484     context.execute(
1485       newTableBuilder(tableName)
1486         .addPkColumn(UUID_COL)
1487         .addColumn(newVarcharColumnDefBuilder("role").setLimit(64).setIsNullable(false).build())
1488         .addColumn(componentUuidCol)
1489         .addColumn(userUuidCol)
1490         .build());
1491     addIndex(context, tableName, "user_roles_component_uuid", false, componentUuidCol);
1492     addIndex(context, tableName, "user_roles_user", false, userUuidCol);
1493   }
1494
1495   private void createUserDismissedMessage(Context context) {
1496     String tableName = "user_dismissed_messages";
1497     VarcharColumnDef userUuidCol = newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(USER_UUID_SIZE).setIsNullable(false).build();
1498     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1499     VarcharColumnDef messageTypeCol = newVarcharColumnDefBuilder("message_type").setLimit(255).setIsNullable(false).build();
1500     context.execute(
1501       newTableBuilder(tableName)
1502         .addPkColumn(UUID_COL)
1503         .addColumn(userUuidCol)
1504         .addColumn(projectUuidCol)
1505         .addColumn(messageTypeCol)
1506         .addColumn(TECHNICAL_CREATED_AT_COL)
1507         .build());
1508     addIndex(context, tableName, "uniq_user_dismissed_messages", true, userUuidCol, projectUuidCol, messageTypeCol);
1509     addIndex(context, tableName, "udm_project_uuid", false, projectUuidCol);
1510     addIndex(context, tableName, "udm_message_type", false, messageTypeCol);
1511   }
1512
1513   private void createUserTokens(Context context) {
1514     String tableName = "user_tokens";
1515     VarcharColumnDef userUuidCol = newVarcharColumnDefBuilder(USER_UUID_COL_NAME).setLimit(USER_UUID_SIZE).setIsNullable(false).build();
1516     VarcharColumnDef nameCol = newVarcharColumnDefBuilder("name").setLimit(100).setIsNullable(false).build();
1517     VarcharColumnDef tokenHashCol = newVarcharColumnDefBuilder("token_hash").setLimit(255).setIsNullable(false).build();
1518     VarcharColumnDef projectKeyCol = newVarcharColumnDefBuilder("project_key").setLimit(255).setIsNullable(true).build();
1519     VarcharColumnDef typeCol = newVarcharColumnDefBuilder("type").setLimit(100).setIsNullable(false).build();
1520     context.execute(
1521       newTableBuilder(tableName)
1522         .addPkColumn(UUID_COL)
1523         .addColumn(userUuidCol)
1524         .addColumn(nameCol)
1525         .addColumn(tokenHashCol)
1526         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("last_connection_date").setIsNullable(true).build())
1527         .addColumn(TECHNICAL_CREATED_AT_COL)
1528         .addColumn(projectKeyCol)
1529         .addColumn(typeCol)
1530         .addColumn(newBigIntegerColumnDefBuilder().setColumnName(EXPIRATION_DATE_COL_NAME).setIsNullable(true).build())
1531         .build());
1532     addIndex(context, tableName, "user_tokens_user_uuid_name", true, userUuidCol, nameCol);
1533     addIndex(context, tableName, "user_tokens_token_hash", true, tokenHashCol);
1534   }
1535
1536   private void createUsers(Context context) {
1537     String tableName = "users";
1538     VarcharColumnDef loginCol = newVarcharColumnDefBuilder("login").setLimit(255).setIsNullable(false).build();
1539     VarcharColumnDef externalLoginCol = newVarcharColumnDefBuilder("external_login").setLimit(255).setIsNullable(false).build();
1540     VarcharColumnDef externalIdentityProviderCol = newVarcharColumnDefBuilder("external_identity_provider").setLimit(100).setIsNullable(false).build();
1541     VarcharColumnDef externalIdCol = newVarcharColumnDefBuilder("external_id").setLimit(255).setIsNullable(false).build();
1542     context.execute(
1543       newTableBuilder(tableName)
1544         .addPkColumn(newVarcharColumnDefBuilder("uuid").setLimit(USER_UUID_SIZE).setIsNullable(false).build())
1545         .addColumn(loginCol)
1546         .addColumn(newVarcharColumnDefBuilder("name").setLimit(200).setIsNullable(true).build())
1547         .addColumn(newVarcharColumnDefBuilder("email").setLimit(100).setIsNullable(true).build())
1548         .addColumn(newVarcharColumnDefBuilder("crypted_password").setLimit(100).setIsNullable(true).build())
1549         .addColumn(newVarcharColumnDefBuilder("salt").setLimit(40).setIsNullable(true).build())
1550         .addColumn(newVarcharColumnDefBuilder("hash_method").setLimit(10).setIsNullable(true).build())
1551         .addColumn(newBooleanColumnDefBuilder().setColumnName("active").setDefaultValue(true).setIsNullable(true).build())
1552         .addColumn(newVarcharColumnDefBuilder("scm_accounts").setLimit(MAX_SIZE).build())
1553         .addColumn(externalLoginCol)
1554         .addColumn(externalIdentityProviderCol)
1555         .addColumn(externalIdCol)
1556         .addColumn(newBooleanColumnDefBuilder().setColumnName("user_local").setIsNullable(true).build())
1557         .addColumn(newVarcharColumnDefBuilder("homepage_type").setLimit(40).setIsNullable(true).build())
1558         .addColumn(newVarcharColumnDefBuilder("homepage_parameter").setLimit(40).setIsNullable(true).build())
1559         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("last_connection_date").setIsNullable(true).build())
1560         .addColumn(NULLABLE_TECHNICAL_CREATED_AT_COL)
1561         .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL)
1562         .addColumn(newBooleanColumnDefBuilder().setColumnName("reset_password").setIsNullable(false).build())
1563         .addColumn(newBigIntegerColumnDefBuilder().setColumnName("last_sonarlint_connection").setIsNullable(true).build())
1564         .build());
1565     addIndex(context, tableName, "users_login", true, loginCol);
1566     addIndex(context, tableName, "users_updated_at", false, NULLABLE_TECHNICAL_UPDATED_AT_COL);
1567     addIndex(context, tableName, "uniq_external_id", true, externalIdentityProviderCol, externalIdCol);
1568     addIndex(context, tableName, "uniq_external_login", true, externalIdentityProviderCol, externalLoginCol);
1569   }
1570
1571   private void createWebhookDeliveries(Context context) {
1572     String tableName = "webhook_deliveries";
1573     VarcharColumnDef componentUuidColumn = newVarcharColumnDefBuilder(COMPONENT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(false).build();
1574     VarcharColumnDef ceTaskUuidColumn = newVarcharColumnDefBuilder("ce_task_uuid").setLimit(UUID_SIZE).setIsNullable(true).build();
1575     VarcharColumnDef webhookUuidColumn = newVarcharColumnDefBuilder("webhook_uuid").setLimit(UUID_SIZE).setIsNullable(false).build();
1576     context.execute(newTableBuilder(tableName)
1577       .addPkColumn(UUID_COL)
1578       .addColumn(webhookUuidColumn)
1579       .addColumn(componentUuidColumn)
1580       .addColumn(ceTaskUuidColumn)
1581       .addColumn(newVarcharColumnDefBuilder(ANALYSIS_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build())
1582       .addColumn(newVarcharColumnDefBuilder("name").setLimit(100).setIsNullable(false).build())
1583       .addColumn(newVarcharColumnDefBuilder("url").setLimit(2_000).setIsNullable(false).build())
1584       .addColumn(newBooleanColumnDefBuilder().setColumnName("success").setIsNullable(false).build())
1585       .addColumn(newIntegerColumnDefBuilder().setColumnName("http_status").setIsNullable(true).build())
1586       .addColumn(newBigIntegerColumnDefBuilder().setColumnName("duration_ms").setIsNullable(false).build())
1587       .addColumn(newClobColumnDefBuilder().setColumnName("payload").setIsNullable(false).build())
1588       .addColumn(newClobColumnDefBuilder().setColumnName("error_stacktrace").setIsNullable(true).build())
1589       .addColumn(TECHNICAL_CREATED_AT_COL)
1590       .build());
1591     addIndex(context, tableName, COMPONENT_UUID_COL_NAME, false, componentUuidColumn);
1592     addIndex(context, tableName, "ce_task_uuid", false, ceTaskUuidColumn);
1593     addIndex(context, tableName, "idx_wbhk_dlvrs_wbhk_uuid", false, webhookUuidColumn);
1594   }
1595
1596   private void createWebhooks(Context context) {
1597     String tableName = "webhooks";
1598     VarcharColumnDef projectUuidCol = newVarcharColumnDefBuilder(PROJECT_UUID_COL_NAME).setLimit(UUID_SIZE).setIsNullable(true).build();
1599     context.execute(newTableBuilder(tableName)
1600       .addPkColumn(UUID_COL)
1601       .addColumn(projectUuidCol)
1602       .addColumn(newVarcharColumnDefBuilder("name").setLimit(100).setIsNullable(false).build())
1603       .addColumn(newVarcharColumnDefBuilder("url").setLimit(2_000).setIsNullable(false).build())
1604       .addColumn(newVarcharColumnDefBuilder("secret").setLimit(200).setIsNullable(true).build())
1605       .addColumn(TECHNICAL_CREATED_AT_COL)
1606       .addColumn(NULLABLE_TECHNICAL_UPDATED_AT_COL)
1607       .build());
1608   }
1609
1610   private static void addIndex(Context context, String table, String index, boolean unique, ColumnDef firstColumn, ColumnDef... otherColumns) {
1611     CreateIndexBuilder builder = new CreateIndexBuilder()
1612       .setTable(table)
1613       .setName(index)
1614       .setUnique(unique);
1615     concat(of(firstColumn), stream(otherColumns)).forEach(builder::addColumn);
1616     context.execute(builder.build());
1617   }
1618
1619   private CreateTableBuilder newTableBuilder(String tableName) {
1620     return new CreateTableBuilder(getDialect(), tableName);
1621   }
1622 }