From f138d383ba38185ce6d8c2f9f8fb83c20d10d7ef Mon Sep 17 00:00:00 2001 From: =?utf8?q?S=C3=A9bastien=20Lesaint?= Date: Mon, 21 May 2018 11:57:35 +0200 Subject: [PATCH] consistent and extensive PK declaration in schema-h2.ddl --- .../org/sonar/db/version/schema-h2.ddl | 133 ++++++++++++------ 1 file changed, 89 insertions(+), 44 deletions(-) diff --git a/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl b/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl index d480b16e24a..e7c04a5723d 100644 --- a/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl +++ b/server/sonar-db-core/src/main/resources/org/sonar/db/version/schema-h2.ddl @@ -1,5 +1,5 @@ CREATE TABLE "ORGANIZATIONS" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "KEE" VARCHAR(32) NOT NULL, "NAME" VARCHAR(64) NOT NULL, "DESCRIPTION" VARCHAR(256), @@ -12,14 +12,17 @@ CREATE TABLE "ORGANIZATIONS" ( "DEFAULT_QUALITY_GATE_UUID" VARCHAR(40) NOT NULL, "NEW_PROJECT_PRIVATE" BOOLEAN NOT NULL, "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_ORGANIZATIONS" PRIMARY KEY ("UUID") ); CREATE UNIQUE INDEX "ORGANIZATION_KEY" ON "ORGANIZATIONS" ("KEE"); CREATE TABLE "ORGANIZATION_MEMBERS" ( "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, "USER_ID" INTEGER NOT NULL, - PRIMARY KEY ("ORGANIZATION_UUID", "USER_ID") + + CONSTRAINT "PK_ORGANIZATION_MEMBERS" PRIMARY KEY ("ORGANIZATION_UUID", "USER_ID") ); CREATE TABLE "GROUPS_USERS" ( @@ -56,14 +59,16 @@ CREATE UNIQUE INDEX "UNIQ_QPROF_KEY" ON "RULES_PROFILES" ("KEE"); CREATE TABLE "ORG_QPROFILES" ( - "UUID" VARCHAR(255) PRIMARY KEY, + "UUID" VARCHAR(255) NOT NULL, "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, "RULES_PROFILE_UUID" VARCHAR(255) NOT NULL, "PARENT_UUID" VARCHAR(255), "LAST_USED" BIGINT, "USER_UPDATED_AT" BIGINT, "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_ORG_QPROFILES" PRIMARY KEY ("UUID") ); CREATE INDEX "ORG_QPROFILES_ORG_UUID" ON "ORG_QPROFILES" ("ORGANIZATION_UUID"); CREATE INDEX "ORG_QPROFILES_RP_UUID" ON "ORG_QPROFILES" ("RULES_PROFILE_UUID"); @@ -75,7 +80,8 @@ CREATE TABLE "DEFAULT_QPROFILES" ( "QPROFILE_UUID" VARCHAR(255) NOT NULL, "CREATED_AT" BIGINT NOT NULL, "UPDATED_AT" BIGINT NOT NULL, - PRIMARY KEY ("ORGANIZATION_UUID", "LANGUAGE") + + CONSTRAINT "PK_DEFAULT_QPROFILES" PRIMARY KEY ("ORGANIZATION_UUID", "LANGUAGE") ); CREATE UNIQUE INDEX "UNIQ_DEFAULT_QPROFILES_UUID" ON "DEFAULT_QPROFILES" ("QPROFILE_UUID"); @@ -89,20 +95,24 @@ CREATE UNIQUE INDEX "UNIQ_PROJECT_QPROFILES" ON "PROJECT_QPROFILES" ("PROJECT_UU CREATE TABLE "QPROFILE_EDIT_USERS" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "USER_ID" INTEGER NOT NULL, "QPROFILE_UUID" VARCHAR(255) NOT NULL, "CREATED_AT" BIGINT, + + CONSTRAINT "PK_QPROFILE_EDIT_USERS" PRIMARY KEY ("UUID") ); CREATE INDEX "QPROFILE_EDIT_USERS_QPROFILE" ON "QPROFILE_EDIT_USERS" ("QPROFILE_UUID"); CREATE UNIQUE INDEX "QPROFILE_EDIT_USERS_UNIQUE" ON "QPROFILE_EDIT_USERS" ("USER_ID", "QPROFILE_UUID"); CREATE TABLE "QPROFILE_EDIT_GROUPS" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "GROUP_ID" INTEGER NOT NULL, "QPROFILE_UUID" VARCHAR(255) NOT NULL, "CREATED_AT" BIGINT, + + CONSTRAINT "PK_QPROFILE_EDIT_GROUPS" PRIMARY KEY ("UUID") ); CREATE INDEX "QPROFILE_EDIT_GROUPS_QPROFILE" ON "QPROFILE_EDIT_GROUPS" ("QPROFILE_UUID"); CREATE UNIQUE INDEX "QPROFILE_EDIT_GROUPS_UNIQUE" ON "QPROFILE_EDIT_GROUPS" ("GROUP_ID", "QPROFILE_UUID"); @@ -159,18 +169,22 @@ CREATE UNIQUE INDEX "UNIQ_GROUP_ROLES" ON "GROUP_ROLES" ("ORGANIZATION_UUID", "G CREATE TABLE "RULE_REPOSITORIES" ( - "KEE" VARCHAR(200) PRIMARY KEY, + "KEE" VARCHAR(200) NOT NULL, "LANGUAGE" VARCHAR(20) NOT NULL, "NAME" VARCHAR(4000) NOT NULL, - "CREATED_AT" BIGINT + "CREATED_AT" BIGINT, + + CONSTRAINT "PK_RULE_REPOSITORIES" PRIMARY KEY ("KEE") ); CREATE TABLE "DEPRECATED_RULE_KEYS" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "RULE_ID" INTEGER NOT NULL, "OLD_REPOSITORY_KEY" VARCHAR(200) NOT NULL, "OLD_RULE_KEY" VARCHAR(255) NOT NULL, - "CREATED_AT" BIGINT + "CREATED_AT" BIGINT, + + CONSTRAINT "PK_DEPRECATED_RULE_KEYS" PRIMARY KEY ("UUID") ); CREATE UNIQUE INDEX "UNIQ_DEPRECATED_RULE_KEYS" ON "DEPRECATED_RULE_KEYS" ("OLD_REPOSITORY_KEY", "OLD_RULE_KEY"); CREATE INDEX "RULE_ID_DEPRECATED_RULE_KEYS" ON "DEPRECATED_RULE_KEYS" ("RULE_ID"); @@ -215,7 +229,8 @@ CREATE TABLE "RULES_METADATA" ( "TAGS" VARCHAR(4000), "CREATED_AT" BIGINT NOT NULL, "UPDATED_AT" BIGINT NOT NULL, - PRIMARY KEY ("RULE_ID", "ORGANIZATION_UUID") + + CONSTRAINT "PK_RULES_METADATA" PRIMARY KEY ("RULE_ID", "ORGANIZATION_UUID") ); CREATE TABLE "EVENTS" ( @@ -258,9 +273,11 @@ CREATE TABLE "QUALITY_GATE_CONDITIONS" ( ); CREATE TABLE "ORG_QUALITY_GATES" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "ORGANIZATION_UUID" VARCHAR(40) NOT NULL, - "QUALITY_GATE_UUID" VARCHAR(40) NOT NULL + "QUALITY_GATE_UUID" VARCHAR(40) NOT NULL, + + CONSTRAINT "PK_ORG_QUALITY_GATES" PRIMARY KEY ("UUID") ); CREATE UNIQUE INDEX "UNIQ_ORG_QUALITY_GATES" ON "ORG_QUALITY_GATES" ("ORGANIZATION_UUID","QUALITY_GATE_UUID"); @@ -278,13 +295,15 @@ CREATE INDEX "PROPERTIES_KEY" ON "PROPERTIES" ("PROP_KEY"); CREATE TABLE "PROJECT_LINKS" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "PROJECT_UUID" VARCHAR(50) NOT NULL, "LINK_TYPE" VARCHAR(20) NOT NULL, "NAME" VARCHAR(128), "HREF" VARCHAR(2048) NOT NULL, "CREATED_AT" BIGINT, - "UPDATED_AT" BIGINT + "UPDATED_AT" BIGINT, + + CONSTRAINT "PK_PROJECT_LINKS" PRIMARY KEY ("UUID") ); CREATE INDEX "PROJECT_LINKS_PROJECT" ON "PROJECT_LINKS" ("PROJECT_UUID"); @@ -303,7 +322,7 @@ CREATE INDEX "DUPLICATION_ANALYSIS_COMPONENT" ON "DUPLICATIONS_INDEX" ("ANALYSIS CREATE TABLE "LIVE_MEASURES" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "PROJECT_UUID" VARCHAR(50) NOT NULL, "COMPONENT_UUID" VARCHAR(50) NOT NULL, "METRIC_ID" INTEGER NOT NULL, @@ -313,7 +332,9 @@ CREATE TABLE "LIVE_MEASURES" ( "MEASURE_DATA" BINARY, "UPDATE_MARKER" VARCHAR(40), "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_LIVE_MEASURES" PRIMARY KEY ("UUID") ); CREATE INDEX "LIVE_MEASURES_PROJECT" ON "LIVE_MEASURES" ("PROJECT_UUID"); CREATE UNIQUE INDEX "LIVE_MEASURES_COMPONENT" ON "LIVE_MEASURES" ("COMPONENT_UUID", "METRIC_ID"); @@ -342,11 +363,13 @@ CREATE INDEX "MEASURES_ANALYSIS_METRIC" ON "PROJECT_MEASURES" ("ANALYSIS_UUID", CREATE TABLE "INTERNAL_PROPERTIES" ( - "KEE" VARCHAR(20) PRIMARY KEY, + "KEE" VARCHAR(20) NOT NULL, "IS_EMPTY" BOOLEAN NOT NULL, "TEXT_VALUE" VARCHAR(4000), "CLOB_VALUE" CLOB, - "CREATED_AT" BIGINT + "CREATED_AT" BIGINT, + + CONSTRAINT "PK_INTERNAL_PROPERTIES" PRIMARY KEY ("KEE") ); @@ -599,12 +622,14 @@ CREATE TABLE "PERM_TEMPLATES_GROUPS" ( CREATE TABLE "QPROFILE_CHANGES" ( - "KEE" VARCHAR(40) PRIMARY KEY, + "KEE" VARCHAR(40) NOT NULL, "RULES_PROFILE_UUID" VARCHAR(255) NOT NULL, "CHANGE_TYPE" VARCHAR(20) NOT NULL, "CREATED_AT" BIGINT NOT NULL, "USER_UUID" VARCHAR(255), - "CHANGE_DATA" CLOB + "CHANGE_DATA" CLOB, + + CONSTRAINT "PK_QPROFILE_CHANGES" PRIMARY KEY ("KEE") ); CREATE INDEX "QP_CHANGES_RULES_PROFILE_UUID" ON "QPROFILE_CHANGES" ("RULES_PROFILE_UUID"); @@ -676,27 +701,33 @@ CREATE INDEX "CE_ACTIVITY_ISLAST_STATUS" ON "CE_ACTIVITY" ("IS_LAST", "STATUS"); CREATE TABLE "CE_TASK_CHARACTERISTICS" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "TASK_UUID" VARCHAR(40) NOT NULL, "KEE" VARCHAR(50) NOT NULL, - "TEXT_VALUE" VARCHAR(4000) + "TEXT_VALUE" VARCHAR(4000), + + CONSTRAINT "PK_CE_TASK_CHARACTERISTICS" PRIMARY KEY ("UUID") ); CREATE INDEX "CE_TASK_CHARACTERISTICS_TASK_UUID" ON "CE_TASK_CHARACTERISTICS" ("TASK_UUID"); CREATE TABLE "CE_TASK_INPUT" ( - "TASK_UUID" VARCHAR(40) PRIMARY KEY, + "TASK_UUID" VARCHAR(40) NOT NULL, "INPUT_DATA" BLOB, "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_CE_TASK_INPUT" PRIMARY KEY ("TASK_UUID") ); CREATE TABLE "CE_SCANNER_CONTEXT" ( - "TASK_UUID" VARCHAR(40) PRIMARY KEY, + "TASK_UUID" VARCHAR(40) NOT NULL, "CONTEXT_DATA" BLOB NOT NULL, "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_CE_SCANNER_CONTEXT" PRIMARY KEY ("TASK_UUID") ); @@ -712,29 +743,33 @@ CREATE UNIQUE INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS" ("USER_UUID", CREATE TABLE "ES_QUEUE" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "DOC_TYPE" VARCHAR(40) NOT NULL, "DOC_ID" VARCHAR(4000) NOT NULL, "DOC_ID_TYPE" VARCHAR(20), "DOC_ROUTING" VARCHAR(4000), - "CREATED_AT" BIGINT NOT NULL + "CREATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_ES_QUEUE" PRIMARY KEY ("UUID") ); CREATE INDEX "ES_QUEUE_CREATED_AT" ON "ES_QUEUE" ("CREATED_AT"); CREATE TABLE "PLUGINS" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "KEE" VARCHAR(200) NOT NULL, "BASE_PLUGIN_KEY" VARCHAR(200), "FILE_HASH" VARCHAR(200) NOT NULL, "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_PLUGINS" PRIMARY KEY ("UUID") ); CREATE UNIQUE INDEX "PLUGINS_KEY" ON "PLUGINS" ("KEE"); CREATE TABLE "PROJECT_BRANCHES" ( - "UUID" VARCHAR(50) PRIMARY KEY, + "UUID" VARCHAR(50) NOT NULL, "PROJECT_UUID" VARCHAR(50) NOT NULL, "KEE" VARCHAR(255) NOT NULL, "KEY_TYPE" VARCHAR(12) NOT NULL, @@ -742,38 +777,44 @@ CREATE TABLE "PROJECT_BRANCHES" ( "MERGE_BRANCH_UUID" VARCHAR(50), "PULL_REQUEST_BINARY" BLOB, "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_PROJECT_BRANCHES" PRIMARY KEY ("UUID") ); CREATE UNIQUE INDEX "PROJECT_BRANCHES_KEE_KEY_TYPE" ON "PROJECT_BRANCHES" ("PROJECT_UUID", "KEE", "KEY_TYPE"); CREATE TABLE "ANALYSIS_PROPERTIES" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "SNAPSHOT_UUID" VARCHAR(40) NOT NULL, "KEE" VARCHAR(512) NOT NULL, "TEXT_VALUE" VARCHAR(4000), "CLOB_VALUE" CLOB, "IS_EMPTY" BOOLEAN NOT NULL, - "CREATED_AT" BIGINT NOT NULL + "CREATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_ANALYSIS_PROPERTIES" PRIMARY KEY ("UUID") ); CREATE INDEX "SNAPSHOT_UUID" ON "ANALYSIS_PROPERTIES" ("SNAPSHOT_UUID"); CREATE TABLE "WEBHOOKS" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "NAME" VARCHAR(100) NOT NULL, "URL" VARCHAR(2000) NOT NULL, "ORGANIZATION_UUID" VARCHAR(40), "PROJECT_UUID" VARCHAR(40), "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_WEBHOOKS" PRIMARY KEY ("UUID") ); CREATE INDEX "ORGANIZATION_WEBHOOK" ON "WEBHOOKS" ("ORGANIZATION_UUID"); CREATE INDEX "PROJECT_WEBHOOK" ON "WEBHOOKS" ("PROJECT_UUID"); CREATE TABLE "WEBHOOK_DELIVERIES" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "WEBHOOK_UUID" VARCHAR(40) NOT NULL, "COMPONENT_UUID" VARCHAR(40) NOT NULL, "ANALYSIS_UUID" VARCHAR(40), @@ -785,7 +826,9 @@ CREATE TABLE "WEBHOOK_DELIVERIES" ( "DURATION_MS" INT NOT NULL, "PAYLOAD" CLOB NOT NULL, "ERROR_STACKTRACE" CLOB, - "CREATED_AT" BIGINT NOT NULL + "CREATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_WEBHOOK_DELIVERIES" PRIMARY KEY ("UUID") ); CREATE INDEX "COMPONENT_UUID" ON "WEBHOOK_DELIVERIES" ("COMPONENT_UUID"); CREATE INDEX "CE_TASK_UUID" ON "WEBHOOK_DELIVERIES" ("CE_TASK_UUID"); @@ -793,18 +836,20 @@ CREATE INDEX "ANALYSIS_UUID" ON "WEBHOOK_DELIVERIES" ("ANALYSIS_UUID"); CREATE TABLE "ALM_APP_INSTALLS" ( - "UUID" VARCHAR(40) PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "ALM_ID" VARCHAR(40) NOT NULL, "OWNER_ID" VARCHAR(4000) NOT NULL, "INSTALL_ID" VARCHAR(4000) NOT NULL, "CREATED_AT" BIGINT NOT NULL, - "UPDATED_AT" BIGINT NOT NULL + "UPDATED_AT" BIGINT NOT NULL, + + CONSTRAINT "PK_ALM_APP_INSTALLS" PRIMARY KEY ("UUID") ); CREATE UNIQUE INDEX "ALM_APP_INSTALLS_OWNER" ON "ALM_APP_INSTALLS" ("ALM_ID", "OWNER_ID"); CREATE UNIQUE INDEX "ALM_APP_INSTALLS_INSTALL" ON "ALM_APP_INSTALLS" ("ALM_ID", "INSTALL_ID"); CREATE TABLE "PROJECT_MAPPINGS" ( - "UUID" VARCHAR(40) NOT NULL PRIMARY KEY, + "UUID" VARCHAR(40) NOT NULL, "KEY_TYPE" VARCHAR(200) NOT NULL, "KEE" VARCHAR(4000) NOT NULL, "PROJECT_UUID" VARCHAR(40) NOT NULL, -- 2.39.5