<commit_before>databaseChangeLog:
  ## UUID
  - property:
      name: uuid_function
      value: NEWID()
      dbms: mssql
  - property:
      name: uuid_function
      value: uuid()
      dbms: mariadb, mysql
  - property:
      name: uuid_function
      value: uuid_generate_v4()
      dbms: postgresql
  ## LENGTH function
  - property:
      name: length
      value: len
      dbms: mssql
  - property:
      name: length
      value: length
      dbms: mariadb, mysql, postgresql

  - changeSet:
      id: 3.0.0
      author: GraviteeSource Team
      changes:
        ## roles, permissions & memberships migration ##
        # roles_permissions
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('29',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '10%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('30',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '11%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('17',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '12%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('31',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '13%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('32',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '14%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('26',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '15%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('33',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '16%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('34',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '17%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('35',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '18%'
        - sql:
            sql: update role_permissions set role_scope = 'ORGANIZATION', permission = CONCAT('12',substring(permission, 3, ${length}(permission))) where role_scope = 'MANAGEMENT' and permission like '16%'
        - sql:
            sql: update role_permissions set role_scope = 'ORGANIZATION', permission = CONCAT('10',substring(permission, 3, ${length}(permission))) where role_scope = 'MANAGEMENT' and permission like '21%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT' where role_scope = 'MANAGEMENT' and permission like '17%' and not exists (select * from (select * from role_permissions) rp2 where rp2.role_scope = 'ENVIRONMENT' and rp2.permission like '17%' and rp2.role_name = role_name)
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT' where role_scope = 'MANAGEMENT' and permission like '26%' and not exists (select * from (select * from role_permissions) rp2 where rp2.role_scope = 'ENVIRONMENT' and rp2.permission like '26%' and rp2.role_name = role_name)
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT' where role_scope = 'MANAGEMENT' and permission not like '17%' and permission not like '26%'
        - sql:
            sql: delete from role_permissions where role_scope = 'MANAGEMENT'
        - addColumn:
            tableName: role_permissions
            columns:
              - column:
                  name: role_id
                  type: nvarchar(64)
                  constraints:
                    nullable: true
        - sql:
            sql: update role_permissions set role_id = CONCAT(role_scope,'_',role_name)
        - addNotNullConstraint:
            columnDataType: nvarchar(64)
            columnName: role_id
            tableName: role_permissions
        - dropPrimaryKey:
            constraintName: pk_role_permissions
            tableName: role_permissions
        - addPrimaryKey:
            constraintName: pk_role_permissions
            columnNames: role_id, permission
            tableName: role_permissions
        - dropColumn:
            columnName: role_scope
            tableName: role_permissions
        - dropColumn:
            columnName: role_name
            tableName: role_permissions

        # roles
        - sql:
            sql: update roles set scope = 'ENVIRONMENT' where scope = 'PORTAL'
        - sql:
            sql: insert into roles (scope, name, description, default_role, system, created_at, updated_at) select 'ORGANIZATION', r.name, r.description, r.default_role, r.system, r.created_at, r.updated_at from roles r where r.scope = 'MANAGEMENT' and exists (select 1 from role_permissions rp where rp.role_id = CONCAT('ORGANIZATION_',r.name))
        - sql:
            sql: update roles set scope = 'ENVIRONMENT' where scope = 'MANAGEMENT' and not exists (select 1 from (select * from roles) r2 where r2.scope = 'ENVIRONMENT' and r2.name = name)
        - sql:
            sql: delete from roles where scope = 'MANAGEMENT'
        - addColumn:
            tableName: roles
            columns:
              - column:
                  name: id
                  type: nvarchar(64)
                  constraints:
                    nullable: true
        - sql:
            sql: update roles set id = CONCAT(scope,'_',name)
        - addNotNullConstraint:
            columnDataType: nvarchar(64)
            columnName: id
            tableName: roles
        - dropPrimaryKey:
            constraintName: pk_roles
            tableName: roles
        - addPrimaryKey:
            constraintName: pk_roles
            columnNames: id
            tableName: roles

        # membership_roles
        - dropPrimaryKey:
            constraintName: pk_membership_roles
            tableName: membership_roles
        - modifyDataType:
            columnName: role_scope
            newDataType: nvarchar(64)
            tableName: membership_roles
        - sql:
            sql: update membership_roles set role_scope = 'MANAGEMENT' where role_scope = '1';
        - sql:
            sql: update membership_roles set role_scope = 'PORTAL' where role_scope = '2';
        - sql:
            sql: update membership_roles set role_scope = 'API' where role_scope = '3';
        - sql:
            sql: update membership_roles set role_scope = 'APPLICATION' where role_scope = '4';
        - sql:
            sql: update membership_roles set role_scope = 'GROUP' where role_scope = '5';
        - sql:
            sql: update membership_roles set reference_type = 'ENVIRONMENT', role_scope = 'ENVIRONMENT' WHERE reference_type = 'PORTAL' and role_scope = 'PORTAL'
        - sql:
            sql: insert into membership_roles (user_id, reference_id, reference_type, role_scope, role_name) select user_id, reference_id, 'ORGANIZATION', 'ORGANIZATION', role_name from membership_roles mr where mr.reference_type = 'MANAGEMENT' and mr.role_scope = 'MANAGEMENT' and exists (select 1 from roles r where r.id = CONCAT('ORGANIZATION_',mr.role_name))
        - sql:
            sql: update membership_roles set reference_type = 'ENVIRONMENT', role_scope = 'ENVIRONMENT' where reference_type = 'MANAGEMENT' and role_scope = 'MANAGEMENT' and not exists (select 1 from (select * from membership_roles) mr2 where mr2.reference_type = 'ENVIRONMENT' and mr2.role_scope = 'ENVIRONMENT' and mr2.role_name = role_name and mr2.user_id = user_id)
        - sql:
            sql: delete from membership_roles where reference_type = 'MANAGEMENT' and role_scope = 'MANAGEMENT'

        # memberships
        - dropPrimaryKey:
            constraintName: pk_memberships
            tableName: memberships
        - sql:
            sql: update memberships set reference_type = 'ENVIRONMENT' WHERE reference_type = 'PORTAL'
        - sql:
            sql: insert into memberships (user_id, reference_id, reference_type, created_at, updated_at) select user_id, reference_id, 'ORGANIZATION', created_at, updated_at from memberships m where m.reference_type = 'MANAGEMENT' and exists (select 1 from membership_roles mr where mr.user_id = m.user_id and mr.reference_id = m.reference_id and mr.reference_type = 'ORGANIZATION')
        - sql:
            sql: update memberships set reference_type = 'ENVIRONMENT' where reference_type = 'MANAGEMENT' and not exists (select 1 from (select * from memberships) m2 where m2.user_id = user_id and reference_id = m2.reference_id and m2.reference_type = 'ENVIRONMENT')
        - sql:
            sql: delete from memberships where reference_type = 'MANAGEMENT'
        - addColumn:
            tableName: memberships
            columns:
              - column:
                  name: id
                  type: nvarchar(64)
                  constraints:
                    nullable: true
              - column:
                  name: member_id
                  type: nvarchar(64)
                  constraints:
                    nullable: true
              - column:
                  name: member_type
                  type: nvarchar(64)
                  constraints:
                    nullable: true
              - column:
                  name: role_id
                  type: nvarchar(64)
                  constraints:
                    nullable: true

        ## init uuid generator for Postgresql
        - sql:
            dbms: postgresql
            sql: create extension "uuid-ossp"
        
        - dropNotNullConstraint:
            columnDataType: nvarchar(64)
            columnName: user_id
            tableName: memberships
           
        - sql:
            sql: insert into memberships (id, member_id, member_type, reference_id, reference_type, role_id, created_at, updated_at) select ${uuid_function} as id, m.user_id, 'USER', m.reference_id, m.reference_type, CONCAT(mr.role_scope,'_',mr.role_name) as role_id, m.created_at, m.updated_at from memberships m inner join membership_roles mr on mr.user_id = m.user_id and mr.reference_id = m.reference_id and mr.reference_type = m.reference_type
        - sql:<commit_msg>fix(jdbc): don't need uuid_ossp extension to generate a V4 UUID<commit_after>databaseChangeLog:
  ## UUID
  - property:
      name: uuid_function
      value: NEWID()
      dbms: mssql
  - property:
      name: uuid_function
      value: uuid()
      dbms: mariadb, mysql
  - property:
      name: uuid_function
      value: md5(random()::text || clock_timestamp()::text)::uuid
      dbms: postgresql
  ## LENGTH function
  - property:
      name: length
      value: len
      dbms: mssql
  - property:
      name: length
      value: length
      dbms: mariadb, mysql, postgresql

  - changeSet:
      id: 3.0.0
      author: GraviteeSource Team
      changes:
        ## roles, permissions & memberships migration ##
        # roles_permissions
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('29',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '10%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('30',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '11%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('17',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '12%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('31',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '13%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('32',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '14%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('26',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '15%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('33',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '16%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('34',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '17%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT', permission = CONCAT('35',substring(permission, 3, ${length}(permission))) where role_scope = 'PORTAL' and permission like '18%'
        - sql:
            sql: update role_permissions set role_scope = 'ORGANIZATION', permission = CONCAT('12',substring(permission, 3, ${length}(permission))) where role_scope = 'MANAGEMENT' and permission like '16%'
        - sql:
            sql: update role_permissions set role_scope = 'ORGANIZATION', permission = CONCAT('10',substring(permission, 3, ${length}(permission))) where role_scope = 'MANAGEMENT' and permission like '21%'
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT' where role_scope = 'MANAGEMENT' and permission like '17%' and not exists (select * from (select * from role_permissions) rp2 where rp2.role_scope = 'ENVIRONMENT' and rp2.permission like '17%' and rp2.role_name = role_name)
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT' where role_scope = 'MANAGEMENT' and permission like '26%' and not exists (select * from (select * from role_permissions) rp2 where rp2.role_scope = 'ENVIRONMENT' and rp2.permission like '26%' and rp2.role_name = role_name)
        - sql:
            sql: update role_permissions set role_scope = 'ENVIRONMENT' where role_scope = 'MANAGEMENT' and permission not like '17%' and permission not like '26%'
        - sql:
            sql: delete from role_permissions where role_scope = 'MANAGEMENT'
        - addColumn:
            tableName: role_permissions
            columns:
              - column:
                  name: role_id
                  type: nvarchar(64)
                  constraints:
                    nullable: true
        - sql:
            sql: update role_permissions set role_id = CONCAT(role_scope,'_',role_name)
        - addNotNullConstraint:
            columnDataType: nvarchar(64)
            columnName: role_id
            tableName: role_permissions
        - dropPrimaryKey:
            constraintName: pk_role_permissions
            tableName: role_permissions
        - addPrimaryKey:
            constraintName: pk_role_permissions
            columnNames: role_id, permission
            tableName: role_permissions
        - dropColumn:
            columnName: role_scope
            tableName: role_permissions
        - dropColumn:
            columnName: role_name
            tableName: role_permissions

        # roles
        - sql:
            sql: update roles set scope = 'ENVIRONMENT' where scope = 'PORTAL'
        - sql:
            sql: insert into roles (scope, name, description, default_role, system, created_at, updated_at) select 'ORGANIZATION', r.name, r.description, r.default_role, r.system, r.created_at, r.updated_at from roles r where r.scope = 'MANAGEMENT' and exists (select 1 from role_permissions rp where rp.role_id = CONCAT('ORGANIZATION_',r.name))
        - sql:
            sql: update roles set scope = 'ENVIRONMENT' where scope = 'MANAGEMENT' and not exists (select 1 from (select * from roles) r2 where r2.scope = 'ENVIRONMENT' and r2.name = name)
        - sql:
            sql: delete from roles where scope = 'MANAGEMENT'
        - addColumn:
            tableName: roles
            columns:
              - column:
                  name: id
                  type: nvarchar(64)
                  constraints:
                    nullable: true
        - sql:
            sql: update roles set id = CONCAT(scope,'_',name)
        - addNotNullConstraint:
            columnDataType: nvarchar(64)
            columnName: id
            tableName: roles
        - dropPrimaryKey:
            constraintName: pk_roles
            tableName: roles
        - addPrimaryKey:
            constraintName: pk_roles
            columnNames: id
            tableName: roles

        # membership_roles
        - dropPrimaryKey:
            constraintName: pk_membership_roles
            tableName: membership_roles
        - modifyDataType:
            columnName: role_scope
            newDataType: nvarchar(64)
            tableName: membership_roles
        - sql:
            sql: update membership_roles set role_scope = 'MANAGEMENT' where role_scope = '1';
        - sql:
            sql: update membership_roles set role_scope = 'PORTAL' where role_scope = '2';
        - sql:
            sql: update membership_roles set role_scope = 'API' where role_scope = '3';
        - sql:
            sql: update membership_roles set role_scope = 'APPLICATION' where role_scope = '4';
        - sql:
            sql: update membership_roles set role_scope = 'GROUP' where role_scope = '5';
        - sql:
            sql: update membership_roles set reference_type = 'ENVIRONMENT', role_scope = 'ENVIRONMENT' WHERE reference_type = 'PORTAL' and role_scope = 'PORTAL'
        - sql:
            sql: insert into membership_roles (user_id, reference_id, reference_type, role_scope, role_name) select user_id, reference_id, 'ORGANIZATION', 'ORGANIZATION', role_name from membership_roles mr where mr.reference_type = 'MANAGEMENT' and mr.role_scope = 'MANAGEMENT' and exists (select 1 from roles r where r.id = CONCAT('ORGANIZATION_',mr.role_name))
        - sql:
            sql: update membership_roles set reference_type = 'ENVIRONMENT', role_scope = 'ENVIRONMENT' where reference_type = 'MANAGEMENT' and role_scope = 'MANAGEMENT' and not exists (select 1 from (select * from membership_roles) mr2 where mr2.reference_type = 'ENVIRONMENT' and mr2.role_scope = 'ENVIRONMENT' and mr2.role_name = role_name and mr2.user_id = user_id)
        - sql:
            sql: delete from membership_roles where reference_type = 'MANAGEMENT' and role_scope = 'MANAGEMENT'

        # memberships
        - dropPrimaryKey:
            constraintName: pk_memberships
            tableName: memberships
        - sql:
            sql: update memberships set reference_type = 'ENVIRONMENT' WHERE reference_type = 'PORTAL'
        - sql:
            sql: insert into memberships (user_id, reference_id, reference_type, created_at, updated_at) select user_id, reference_id, 'ORGANIZATION', created_at, updated_at from memberships m where m.reference_type = 'MANAGEMENT' and exists (select 1 from membership_roles mr where mr.user_id = m.user_id and mr.reference_id = m.reference_id and mr.reference_type = 'ORGANIZATION')
        - sql:
            sql: update memberships set reference_type = 'ENVIRONMENT' where reference_type = 'MANAGEMENT' and not exists (select 1 from (select * from memberships) m2 where m2.user_id = user_id and reference_id = m2.reference_id and m2.reference_type = 'ENVIRONMENT')
        - sql:
            sql: delete from memberships where reference_type = 'MANAGEMENT'
        - addColumn:
            tableName: memberships
            columns:
              - column:
                  name: id
                  type: nvarchar(64)
                  constraints:
                    nullable: true
              - column:
                  name: member_id
                  type: nvarchar(64)
                  constraints:
                    nullable: true
              - column:
                  name: member_type
                  type: nvarchar(64)
                  constraints:
                    nullable: true
              - column:
                  name: role_id
                  type: nvarchar(64)
                  constraints:
                    nullable: true

        - dropNotNullConstraint:
            columnDataType: nvarchar(64)
            columnName: user_id
            tableName: memberships
           
        - sql:
            sql: insert into memberships (id, member_id, member_type, reference_id, reference_type, role_id, created_at, updated_at) select ${uuid_function} as id, m.user_id, 'USER', m.reference_id, m.reference_type, CONCAT(mr.role_scope,'_',mr.role_name) as role_id, m.created_at, m.updated_at from memberships m inner join membership_roles mr on mr.user_id = m.user_id and mr.reference_id = m.reference_id and mr.reference_type = m.reference_type
        - sql:
{'fraction_non_alphanumeric': 0.08752991974468484, 'fraction_numerical': 0.011498568545548411, 'mean_word_length': 2.3859844271412682, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '2366519', 'n_tokens_mistral': 6176, 'n_tokens_neox': 6022, 'n_words': 1725}