Uploaded image for project: 'SonarQube'
  1. SonarQube
  2. SONAR-13221

Replace all AUTO_INCREMENT columns by UUID

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 8.4
    • Component/s: Database
    • Labels:
      None
    • Edition:
      Community
    • Production Notes:
      None

      Description

      List ID columns to be removed :

      • ACTIVE_RULE_PARAMETERS#ID (No UUID)
      • ACTIVE_RULES#ID (No UUID)
        • ACTIVE_RULE_PARAMETERS#ACTIVE_RULE_ID
      • CE_ACTIVITY#ID (UUID exists)
      • CE_QUEUE#ID (UUID exists)
      • COMPONENTS#ID (UUID exists)
        • GROUP_ROLES#RESOURCE_ID
        • PROPERTIES#RESOURCE_ID
        • USER_ROLES#RESOURCE_ID
      • DUPLICATIONS_INDEX#ID (No UUID)
      • EVENTS#ID (UUID exists)
      • FILE_SOURCES#ID (existing fileUuid)
      • GROUP_ROLES#ID (No UUID)
      • GROUPS#ID (No UUID)
        • GROUP_ROLES#GROUP_ID
        • GROUPS_USERS#GROUP_ID
        • ORGANIZATIONS#DEFAULT_GROUP_ID
        • PERM_TEMPLATES_GROUPS#GROUP_ID
        • QPROFILE_EDIT_GROUPS#GROUP_ID
      • ISSUE_CHANGES#ID (No UUID but KEE)
      • ISSUES#ID (No UUID but KEE)
      • MANUAL_MEASURES#ID (No UUID)
      • METRICS#ID (No UUID but NAME)
        • PROJECT_MEASURES#METRIC_ID
        • QUALITY_GATE_CONDITIONS#METRIC_ID
        • LIVE_MEASURES#METRIC_ID
        • MANUAL_MEASURES#METRIC_ID
        • PROJECT_MEASURES#METRIC_ID
      • NOTIFICATIONS#ID (No UUID)
      • PERM_TPL_CHARACTERISTICS#ID (no UUID)
      • PERMISSION_TEMPLATES#ID (no UUID)
      • PROJECT_MEASURES#ID (no UUID)
      • PROJECT_QPROFILES#ID (no UUID)
      • PROPERTIES#ID (no UUID)
      • QUALITY_GATE_CONDITIONS#ID (no UUID)
      • QUALITY_GATES#ID (UUID exist)
        • QUALITY_GATE_CONDITIONS#QGATE_ID
      • RULES#ID (no UUID)
        • ACTIVE_RULES#RULE_ID
        • DEPRECATED_RULE_KEYS#RULE_ID
        • ISSUES#RULE_ID
        • RULES_METADATA#RULE_ID
        • RULES_PARAMETERS#RULE_ID
      • RULES_PARAMETERS#ID (no UUID)
      • RULES_PROFILES#ID (no UUID)
        • ACTIVE_RULES#PROFILE_ID
      • SNAPSHOTS#ID (UUID exists)
      • USER_ROLES#ID (NO UUID)
      • USER_TOKENS#ID (no UUID)
      • USERS#ID (UUID exists)
        • GROUPS_USERS#USER_ID
        • ORGANIZATION_MEMBERS#USER_ID
        • PERM_TEMPLATES_USERS#USER_ID
        • PROPERTIES#USER_ID
        • QPROFILE_EDIT_USERS#USER_ID
        • USER_ROLES#USER_ID

      Remove a Primary Key based on an auto-increment column (id) and add the PK on a regular column (KEE)

      ORACLE :
      DROP TRIGGER NEMO.ISSUES_IDT; --drop the trigger
      DROP SEQUENCE NEMO.ISSUES_SEQ; – drop the sequence
      ALTER TABLE NEMO.ISSUES DROP CONSTRAINT ISSUES_PK; --drop the old PK
      ALTER TABLE NEMO.ISSUES ADD CONSTRAINT PK_ISSUE PRIMARY KEY (KEE); --add the new constraint PK on the KEE column (uniqueness enforces by the index)

      POSTGRESQL :
      ALTER TABLE public.issues ALTER COLUMN id DROP DEFAULT; – drop the auto-increment
      DROP SEQUENCE public.issues_id_seq; – drop the sequence
      ALTER TABLE public.issues DROP CONSTRAINT issues_pkey;
      ALTER TABLE NEMO.ISSUES ADD CONSTRAINT PK_ISSUE PRIMARY KEY (KEE);

      SQL SERVER : (it's not possible to remove the identity column)
      ALTER TABLE dbo.issues DROP CONSTRAINT PK_issues_3213E83F37C3316A --ask the catalog to have the constraint name
      ALTER TABLE dbo.issues ADD CONSTRAINT PK__issues PRIMARY KEY (kee) --add a new cPK
      ALTER TABLE dbo.issues DROP COLUMN id --only way to remove the identity column. 

       

       

       

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              jacek.poreda Jacek Poreda
              Reporter:
              pierre.guillot Pierre Guillot
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Due:
                Created:
                Updated:
                Resolved: