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

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