UPDATE RemoteProviderAuthenticationField
SET filterable = 0
WHERE name in ('API Key','API Secret')
AND applicationId = (SELECT id FROM RemoteProviderType WHERE name = 'Veracode');
  
ALTER TABLE RemoteProviderAuthenticationField DROP FOREIGN KEY FKDF097F6B4E5D4B9B;
ALTER TABLE RemoteProviderAuthenticationEntity DROP FOREIGN KEY fk_RPAuthenticationEntity_RPAuthenticationField;
  
DELETE rpae.* FROM RemoteProviderAuthenticationEntity AS rpae
JOIN RemoteProviderAuthenticationField AS rpaf ON rpae.remoteProviderAuthenticationFieldId = rpaf.id
JOIN RemoteProviderType AS rpt ON rpt.id = rpaf.applicationId
WHERE rpaf.name = 'Organization' AND rpt.name = 'SonarQube';
  
DELETE rpae.* FROM RemoteProviderAuthenticationEntity AS rpae
JOIN RemoteProviderEntity AS rpe ON rpae.remoteProviderEntityId = rpe.id
JOIN RemoteProviderType AS rpt ON rpe.remoteProviderTypeId = rpt.id
WHERE rpt.name = 'SonarQube'
AND rpae.remoteProviderEntityId IN (SELECT * FROM (SELECT remoteProviderEntityId
FROM RemoteProviderAuthenticationEntity 
WHERE value = 'v7 (7.9)') as rpeids);
  
DELETE rpaf.* FROM RemoteProviderAuthenticationField AS rpaf
JOIN RemoteProviderType AS rpt ON rpt.id = rpaf.applicationId
WHERE rpt.name = 'SonarQube' AND rpaf.name = 'Organization';
  
DELETE rpafsj.* FROM RemoteProviderAuthenticationField_SelectOption_Join AS rpafsj
JOIN RemoteProviderAuthenticationField AS rpaf ON rpafsj.remoteProviderAuthenticationField_id = rpaf.id
JOIN SelectOption AS so ON rpafsj.selectOptions_id = so.id
WHERE so.value = 'v7 (7.9)';
  
DELETE so.* FROM SelectOption AS so WHERE so.value = 'v7 (7.9)';
  
ALTER TABLE RemoteProviderAuthenticationEntity
    ADD CONSTRAINT fk_RPAuthenticationEntity_RPAuthenticationField FOREIGN KEY (remoteProviderAuthenticationFieldId) REFERENCES RemoteProviderAuthenticationField (id);
  
ALTER TABLE RemoteProviderAuthenticationField
    ADD CONSTRAINT FKDF097F6B4E5D4B9B FOREIGN KEY (applicationId) REFERENCES RemoteProviderType (id);
  
ALTER TABLE User MODIFY title varchar(128);
  
DROP PROCEDURE IF EXISTS dropForeignKeyAndIndexIfExists;
DELIMITER $$
CREATE PROCEDURE dropForeignKeyAndIndexIfExists(tableName varchar(100), refTableName varchar(100))
BEGIN
    DECLARE constraintName varchar(100);
    DECLARE columnName varchar(100);
    DECLARE indexName varchar(100);
  
    SELECT constraint_name, column_name INTO constraintName, columnName FROM information_schema.key_column_usage
    WHERE referenced_table_name = refTableName
    AND table_name = tableName
    AND table_schema = DATABASE();
  
    IF (constraintName IS NOT NULL) THEN
      SET @s1 = CONCAT('ALTER TABLE `', tableName, '` DROP FOREIGN KEY `', constraintName, '`;');
      PREPARE statement FROM @s1;
      EXECUTE statement;
  
      IF ((SELECT COUNT(*) FROM information_schema.statistics WHERE index_name = constraintName AND table_name = tableName AND TABLE_SCHEMA = DATABASE()) > 0) THEN
        SET @s2 = CONCAT('ALTER TABLE `', tableName, '` DROP INDEX `', constraintName, '`;');
        PREPARE statement FROM @s2;
        EXECUTE statement;
      END IF;
    END IF;
END$$
 
DELIMITER ;
  
CALL dropForeignKeyAndIndexIfExists('User_Group', 'User');
CALL dropForeignKeyAndIndexIfExists('User_Group', 'Groups');
  
ALTER TABLE User_Group CHANGE User_Id temp int(11) NOT NULL;
ALTER TABLE User_Group CHANGE Group_Id User_Id int(11) NOT NULL;
ALTER TABLE User_Group CHANGE temp Group_Id int(11) NOT NULL;
  
ALTER TABLE User_Group
    ADD CONSTRAINT FK_UserGroup_GroupId FOREIGN KEY (Group_Id) REFERENCES Groups (id),
    ADD CONSTRAINT FK_UserGroup_UserId FOREIGN KEY (User_Id) REFERENCES User (id);
DROP PROCEDURE IF EXISTS dropForeignKeyAndIndexIfExists;

UPDATE RemoteProviderAuthenticationField
SET filterable = 0
WHERE name in ('API Key','API Secret')
AND applicationId = (SELECT id FROM RemoteProviderType WHERE name = 'Veracode');
GO
 
ALTER TABLE RemoteProviderAuthenticationField DROP CONSTRAINT FKDF097F6B4E5D4B9B;
ALTER TABLE RemoteProviderAuthenticationEntity DROP CONSTRAINT fk_RPAuthenticationEntity_RPAuthenticationField;
 
DELETE rpae FROM RemoteProviderAuthenticationEntity rpae 
JOIN RemoteProviderAuthenticationField rpaf ON rpae.remoteProviderAuthenticationFieldId = rpaf.id
JOIN RemoteProviderType rpt ON rpt.id = rpaf.applicationId
WHERE rpaf.name = 'Organization' AND rpt.name = 'SonarQube';
 
DELETE rpae FROM RemoteProviderAuthenticationEntity rpae
JOIN RemoteProviderEntity rpe ON rpae.remoteProviderEntityId = rpe.id
JOIN RemoteProviderType rpt ON rpe.remoteProviderTypeId = rpt.id
WHERE rpt.name = 'SonarQube'
AND rpae.remoteProviderEntityId IN (SELECT * FROM (SELECT remoteProviderEntityId
FROM RemoteProviderAuthenticationEntity  
WHERE value = 'v7 (7.9)') as rpeids);
 
DELETE rpaf FROM RemoteProviderAuthenticationField rpaf
JOIN RemoteProviderType rpt ON rpt.id = rpaf.applicationId
WHERE rpt.name = 'SonarQube' AND rpaf.name = 'Organization';
 
DELETE rpafsj FROM RemoteProviderAuthenticationField_SelectOption_Join rpafsj
JOIN RemoteProviderAuthenticationField rpaf ON rpafsj.remoteProviderAuthenticationField_id = rpaf.id
JOIN SelectOption so ON rpafsj.selectOptions_id = so.id
WHERE so.value = 'v7 (7.9)';
 
DELETE so FROM SelectOption so WHERE so.value = 'v7 (7.9)';
 
ALTER TABLE RemoteProviderAuthenticationEntity
    ADD CONSTRAINT fk_RPAuthenticationEntity_RPAuthenticationField FOREIGN KEY (remoteProviderAuthenticationFieldId) REFERENCES RemoteProviderAuthenticationField (id);
 
ALTER TABLE RemoteProviderAuthenticationField
    ADD CONSTRAINT FKDF097F6B4E5D4B9B FOREIGN KEY (applicationId) REFERENCES RemoteProviderType (id);
 
ALTER TABLE [User] ALTER COLUMN [title] varchar(128);
 
IF (OBJECT_ID('removeForeignKeyConstraint', 'P') IS NOT NULL)
DROP PROCEDURE removeForeignKeyConstraint
GO
 
CREATE PROCEDURE removeForeignKeyConstraint(@table_name varchar(100), @ref_table_name varchar(100))
    AS
    DECLARE @constraint varchar(200);
    SELECT @constraint = (SELECT so.name
                          FROM sysobjects so
                                   inner join sysobjects c on so.parent_obj = c.id
                                   inner join sysreferences r on so.id = r.constid
                                   inner join sysobjects p on r.rkeyid = p.id
                                   inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
                                   inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
                          WHERE so.type = 'F'
                            AND p.name = @ref_table_name
                            AND c.name = @table_name);
    IF @constraint IS NOT NULL
    BEGIN
        EXEC('ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + @constraint);
    END
GO
 
BEGIN TRANSACTION
 
EXEC removeForeignKeyConstraint @table_name = 'User_Group', @ref_table_name = 'user';
EXEC removeForeignKeyConstraint @table_name = 'User_Group', @ref_table_name = 'groups';
 
 
EXEC sp_RENAME 'User_Group.User_Id' , 'temp', 'COLUMN';
EXEC sp_RENAME 'User_Group.Group_Id' , 'User_Id', 'COLUMN';
EXEC sp_RENAME 'User_Group.temp' , 'Group_Id', 'COLUMN';
 
ALTER TABLE User_Group ADD CONSTRAINT FK_UserGroup_GroupId FOREIGN KEY (Group_Id) REFERENCES [Groups](id);
ALTER TABLE User_Group ADD CONSTRAINT FK_UserGroup_UserId FOREIGN KEY (User_Id) REFERENCES [User](id);
 
COMMIT;
GO
 
IF (OBJECT_ID('removeForeignKeyConstraint', 'P') IS NOT NULL)
DROP PROCEDURE removeForeignKeyConstraint
GO