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