From 3a9d42f4075e23448349b2f96c332fe6eae7a55b Mon Sep 17 00:00:00 2001
From: Julien Lancelot <julien.lancelot@sonarsource.com>
Date: Tue, 19 Jan 2016 11:48:05 +0100
Subject: SONAR-6226 Add ext identity columns in USERS table

---
 .../src/main/java/org/sonar/db/user/UserDao.java   | 15 ++++
 .../src/main/java/org/sonar/db/user/UserDto.java   | 20 +++++
 .../main/java/org/sonar/db/user/UserMapper.java    |  3 +
 .../java/org/sonar/db/version/DatabaseVersion.java |  2 +-
 .../org/sonar/db/version/MigrationStepModule.java  |  7 +-
 .../db/version/v54/AddUsersIdentityColumns.java    | 55 +++++++++++++
 .../sonar/db/version/v54/MigrateUsersIdentity.java | 67 ++++++++++++++++
 .../resources/org/sonar/db/user/UserMapper.xml     | 15 +++-
 .../resources/org/sonar/db/version/rows-h2.sql     |  3 +
 .../resources/org/sonar/db/version/schema-h2.ddl   |  4 +
 .../test/java/org/sonar/db/user/UserDaoTest.java   | 28 +++++++
 .../test/java/org/sonar/db/user/UserTesting.java   | 18 +++--
 .../sonar/db/version/MigrationStepModuleTest.java  |  2 +-
 .../version/v54/AddUsersIdentityColumnsTest.java   | 51 ++++++++++++
 .../db/version/v54/MigrateUsersIdentityTest.java   | 91 ++++++++++++++++++++++
 .../UserDaoTest/select_users_by_ext_identity.xml   | 12 +++
 .../v54/AddUsersIdentityColumnsTest/schema.sql     | 14 ++++
 .../MigrateUsersIdentityTest/migrate-result.xml    | 13 ++++
 .../v54/MigrateUsersIdentityTest/migrate.xml       | 13 ++++
 .../v54/MigrateUsersIdentityTest/schema.sql        | 16 ++++
 20 files changed, 437 insertions(+), 12 deletions(-)
 create mode 100644 sonar-db/src/main/java/org/sonar/db/version/v54/AddUsersIdentityColumns.java
 create mode 100644 sonar-db/src/main/java/org/sonar/db/version/v54/MigrateUsersIdentity.java
 create mode 100644 sonar-db/src/test/java/org/sonar/db/version/v54/AddUsersIdentityColumnsTest.java
 create mode 100644 sonar-db/src/test/java/org/sonar/db/version/v54/MigrateUsersIdentityTest.java
 create mode 100644 sonar-db/src/test/resources/org/sonar/db/user/UserDaoTest/select_users_by_ext_identity.xml
 create mode 100644 sonar-db/src/test/resources/org/sonar/db/version/v54/AddUsersIdentityColumnsTest/schema.sql
 create mode 100644 sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/migrate-result.xml
 create mode 100644 sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/migrate.xml
 create mode 100644 sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/schema.sql

(limited to 'sonar-db')

diff --git a/sonar-db/src/main/java/org/sonar/db/user/UserDao.java b/sonar-db/src/main/java/org/sonar/db/user/UserDao.java
index 8172f465b3d..8799754185a 100644
--- a/sonar-db/src/main/java/org/sonar/db/user/UserDao.java
+++ b/sonar-db/src/main/java/org/sonar/db/user/UserDao.java
@@ -20,6 +20,7 @@
 package org.sonar.db.user;
 
 import com.google.common.base.Function;
+import com.google.common.base.Optional;
 import java.util.Collection;
 import java.util.List;
 import javax.annotation.CheckForNull;
@@ -33,6 +34,8 @@ import org.sonar.db.DbSession;
 import org.sonar.db.MyBatis;
 import org.sonar.db.RowNotFoundException;
 
+import static com.google.common.base.Optional.fromNullable;
+
 public class UserDao implements Dao {
 
   private final MyBatis mybatis;
@@ -172,6 +175,18 @@ public class UserDao implements Dao {
     return mapper(session).selectNullableByScmAccountOrLoginOrEmail(scmAccountOrLoginOrEmail, like);
   }
 
+  public Optional<UserDto> selectByExternalIdentity(DbSession session, String extIdentity, String extIdentityProvider){
+    return fromNullable(mapper(session).selectByIdentity(extIdentity, extIdentityProvider));
+  }
+
+  public UserDto selectOrFailByExternalIdentity(DbSession session, String extIdentity, String extIdentityProvider) {
+    Optional<UserDto> user = selectByExternalIdentity(session, extIdentity, extIdentityProvider);
+    if (user.isPresent()) {
+      return user.get();
+    }
+    throw new RowNotFoundException(String.format("User with identity provider '%s' and id '%s' has not been found", extIdentityProvider, extIdentity));
+  }
+
   protected UserMapper mapper(DbSession session) {
     return session.getMapper(UserMapper.class);
   }
diff --git a/sonar-db/src/main/java/org/sonar/db/user/UserDto.java b/sonar-db/src/main/java/org/sonar/db/user/UserDto.java
index 8c2b067dd72..a6983bf1638 100644
--- a/sonar-db/src/main/java/org/sonar/db/user/UserDto.java
+++ b/sonar-db/src/main/java/org/sonar/db/user/UserDto.java
@@ -40,6 +40,8 @@ public class UserDto {
   private String email;
   private boolean active = true;
   private String scmAccounts;
+  private String externalIdentity;
+  private String externalIdentityProvider;
   private String cryptedPassword;
   private String salt;
   private Long createdAt;
@@ -129,6 +131,24 @@ public class UserDto {
     }
   }
 
+  public String getExternalIdentity() {
+    return externalIdentity;
+  }
+
+  public UserDto setExternalIdentity(String authorithy) {
+    this.externalIdentity = authorithy;
+    return this;
+  }
+
+  public String getExternalIdentityProvider() {
+    return externalIdentityProvider;
+  }
+
+  public UserDto setExternalIdentityProvider(String externalIdentityProvider) {
+    this.externalIdentityProvider = externalIdentityProvider;
+    return this;
+  }
+
   public String getCryptedPassword() {
     return cryptedPassword;
   }
diff --git a/sonar-db/src/main/java/org/sonar/db/user/UserMapper.java b/sonar-db/src/main/java/org/sonar/db/user/UserMapper.java
index 7835c44bcec..605998588db 100644
--- a/sonar-db/src/main/java/org/sonar/db/user/UserMapper.java
+++ b/sonar-db/src/main/java/org/sonar/db/user/UserMapper.java
@@ -51,6 +51,9 @@ public interface UserMapper {
 
   List<UserDto> selectByLogins(List<String> logins);
 
+  @CheckForNull
+  UserDto selectByIdentity(@Param("extIdentity") String authorityId, @Param("extIdentityProvider") String authorityProvider);
+
   @CheckForNull
   GroupDto selectGroupByName(String name);
 
diff --git a/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java b/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java
index b5f895b29ab..43be9647d12 100644
--- a/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java
+++ b/sonar-db/src/main/java/org/sonar/db/version/DatabaseVersion.java
@@ -29,7 +29,7 @@ import org.sonar.db.MyBatis;
 
 public class DatabaseVersion {
 
-  public static final int LAST_VERSION = 1010;
+  public static final int LAST_VERSION = 1013;
 
   /**
    * The minimum supported version which can be upgraded. Lower
diff --git a/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java b/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java
index d4f25e25adc..4591717227e 100644
--- a/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java
+++ b/sonar-db/src/main/java/org/sonar/db/version/MigrationStepModule.java
@@ -61,7 +61,9 @@ import org.sonar.db.version.v52.RemoveRuleMeasuresOnIssues;
 import org.sonar.db.version.v52.RemoveSnapshotLibraries;
 import org.sonar.db.version.v53.FixMsSqlCollation;
 import org.sonar.db.version.v53.UpdateCustomDashboardInLoadedTemplates;
+import org.sonar.db.version.v54.AddUsersIdentityColumns;
 import org.sonar.db.version.v54.InsertGateAdminPermissionForEachProfileAdmin;
+import org.sonar.db.version.v54.MigrateUsersIdentity;
 import org.sonar.db.version.v54.RemoveComponentPageProperties;
 
 public class MigrationStepModule extends Module {
@@ -121,6 +123,9 @@ public class MigrationStepModule extends Module {
 
       // 5.4
       InsertGateAdminPermissionForEachProfileAdmin.class,
-      RemoveComponentPageProperties.class);
+      RemoveComponentPageProperties.class,
+      AddUsersIdentityColumns.class,
+      MigrateUsersIdentity.class
+    );
   }
 }
diff --git a/sonar-db/src/main/java/org/sonar/db/version/v54/AddUsersIdentityColumns.java b/sonar-db/src/main/java/org/sonar/db/version/v54/AddUsersIdentityColumns.java
new file mode 100644
index 00000000000..989ae5db81c
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/version/v54/AddUsersIdentityColumns.java
@@ -0,0 +1,55 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.version.v54;
+
+import java.sql.SQLException;
+import org.sonar.db.Database;
+import org.sonar.db.version.AddColumnsBuilder;
+import org.sonar.db.version.DdlChange;
+
+import static org.sonar.db.version.VarcharColumnDef.newVarcharColumnDefBuilder;
+
+/**
+ * Add the following columns to the USERS table :
+ * - external_identity
+ * - external_identity_provider
+ */
+public class AddUsersIdentityColumns extends DdlChange {
+
+  private final Database db;
+
+  public AddUsersIdentityColumns(Database db) {
+    super(db);
+    this.db = db;
+  }
+
+  @Override
+  public void execute(DdlChange.Context context) throws SQLException {
+    context.execute(generateSql());
+  }
+
+  private String generateSql() {
+    return new AddColumnsBuilder(db.getDialect(), "users")
+      .addColumn(newVarcharColumnDefBuilder().setColumnName("external_identity").setLimit(4000).setIsNullable(true).build())
+      .addColumn(newVarcharColumnDefBuilder().setColumnName("external_identity_provider").setLimit(100).setIsNullable(true).build())
+      .build();
+  }
+
+}
diff --git a/sonar-db/src/main/java/org/sonar/db/version/v54/MigrateUsersIdentity.java b/sonar-db/src/main/java/org/sonar/db/version/v54/MigrateUsersIdentity.java
new file mode 100644
index 00000000000..476153feb5c
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/version/v54/MigrateUsersIdentity.java
@@ -0,0 +1,67 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.version.v54;
+
+import java.sql.SQLException;
+import org.sonar.api.utils.System2;
+import org.sonar.db.Database;
+import org.sonar.db.version.BaseDataChange;
+import org.sonar.db.version.MassUpdate;
+import org.sonar.db.version.Select;
+import org.sonar.db.version.SqlStatement;
+
+/**
+ * Update all users to feed external_identity_provider with 'sonarqube' and external_identity with the login
+ */
+public class MigrateUsersIdentity extends BaseDataChange {
+
+  private final System2 system2;
+
+  public MigrateUsersIdentity(Database db, System2 system2) {
+    super(db);
+    this.system2 = system2;
+  }
+
+  @Override
+  public void execute(Context context) throws SQLException {
+    MassUpdate update = context.prepareMassUpdate().rowPluralName("users");
+    update.select("SELECT u.id, u.login FROM users u");
+    update.update("UPDATE users SET external_identity_provider=?, external_identity=?, updated_at=? WHERE id=? " +
+      "AND external_identity_provider IS NULL AND external_identity IS NULL");
+    update.execute(new MigrationHandler(system2.now()));
+  }
+
+  private static class MigrationHandler implements MassUpdate.Handler {
+    private final long now;
+
+    public MigrationHandler(long now) {
+      this.now = now;
+    }
+
+    @Override
+    public boolean handle(Select.Row row, SqlStatement update) throws SQLException {
+      update.setString(1, "sonarqube");
+      update.setString(2, row.getString(2));
+      update.setLong(3, now);
+      update.setLong(4, row.getLong(1));
+      return true;
+    }
+  }
+}
diff --git a/sonar-db/src/main/resources/org/sonar/db/user/UserMapper.xml b/sonar-db/src/main/resources/org/sonar/db/user/UserMapper.xml
index 14e57dcd5df..a4d3b636448 100644
--- a/sonar-db/src/main/resources/org/sonar/db/user/UserMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/user/UserMapper.xml
@@ -12,6 +12,8 @@
     u.scm_accounts as "scmAccounts",
     u.salt as "salt",
     u.crypted_password as "cryptedPassword",
+    u.external_identity as "externalIdentity",
+    u.external_identity_provider as "externalIdentityProvider",
     u.created_at as "createdAt",
     u.updated_at as "updatedAt"
   </sql>
@@ -87,6 +89,15 @@
     ORDER BY u.name
   </select>
 
+  <select id="selectByIdentity" parameterType="map" resultType="User">
+    SELECT <include refid="userColumns"/>
+    FROM users u
+    <where>
+      u.external_identity=#{extIdentity}
+      AND u.external_identity_provider=#{extIdentityProvider}
+    </where>
+  </select>
+
   <select id="selectGroupByName" parameterType="string" resultType="Group">
     SELECT id, name, description, created_at AS "createdAt", updated_at AS "updatedAt"
     FROM groups WHERE name=#{id}
@@ -133,9 +144,9 @@
   </update>
 
   <insert id="insert" parameterType="User" keyColumn="id" useGeneratedKeys="true" keyProperty="id">
-    INSERT INTO users (login, name, email, active, scm_accounts, salt, crypted_password, created_at, updated_at)
+    INSERT INTO users (login, name, email, active, scm_accounts, external_identity, external_identity_provider, salt, crypted_password, created_at, updated_at)
     VALUES (#{login,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{active,jdbcType=BOOLEAN},
-    #{scmAccounts,jdbcType=VARCHAR},
+    #{scmAccounts,jdbcType=VARCHAR}, #{externalIdentity,jdbcType=VARCHAR}, #{externalIdentityProvider,jdbcType=VARCHAR},
     #{salt,jdbcType=VARCHAR}, #{cryptedPassword,jdbcType=VARCHAR}, #{createdAt,jdbcType=BIGINT},
     #{updatedAt,jdbcType=BIGINT})
   </insert>
diff --git a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql
index c629c8113d2..c225c4c782a 100644
--- a/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql
+++ b/sonar-db/src/main/resources/org/sonar/db/version/rows-h2.sql
@@ -372,6 +372,9 @@ INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1007');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1008');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1009');
 INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1010');
+INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1011');
+INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1012');
+INSERT INTO SCHEMA_MIGRATIONS(VERSION) VALUES ('1013');
 
 INSERT INTO USERS(ID, LOGIN, NAME, EMAIL, CRYPTED_PASSWORD, SALT, CREATED_AT, UPDATED_AT, REMEMBER_TOKEN, REMEMBER_TOKEN_EXPIRES_AT) VALUES (1, 'admin', 'Administrator', '', 'a373a0e667abb2604c1fd571eb4ad47fe8cc0878', '48bc4b0d93179b5103fd3885ea9119498e9d161b', '1418215735482', '1418215735482', null, null);
 ALTER TABLE USERS ALTER COLUMN ID RESTART WITH 2;
diff --git a/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl b/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl
index 0db4c0881af..ff5efccc491 100644
--- a/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl
+++ b/sonar-db/src/main/resources/org/sonar/db/version/schema-h2.ddl
@@ -320,6 +320,8 @@ CREATE TABLE "USERS" (
   "REMEMBER_TOKEN_EXPIRES_AT" TIMESTAMP,
   "ACTIVE" BOOLEAN DEFAULT TRUE,
   "SCM_ACCOUNTS" VARCHAR(4000),
+  "EXTERNAL_IDENTITY" VARCHAR(4000),
+  "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(100),
   "CREATED_AT" BIGINT,
   "UPDATED_AT" BIGINT
 );
@@ -679,6 +681,8 @@ CREATE INDEX "ISSUE_FILTER_FAVS_USER" ON "ISSUE_FILTER_FAVOURITES" ("USER_LOGIN"
 
 CREATE UNIQUE INDEX "USERS_LOGIN" ON "USERS" ("LOGIN");
 
+CREATE UNIQUE INDEX "UNIQ_USERS_IDENTITY" ON "USERS" ("EXTERNAL_IDENTITY", "EXTERNAL_IDENTITY_PROVIDER");
+
 CREATE INDEX "USERS_UPDATED_AT" ON "USERS" ("UPDATED_AT");
 
 CREATE INDEX "SNAPSHOTS_ROOT_PROJECT_ID" ON "SNAPSHOTS" ("ROOT_PROJECT_ID");
diff --git a/sonar-db/src/test/java/org/sonar/db/user/UserDaoTest.java b/sonar-db/src/test/java/org/sonar/db/user/UserDaoTest.java
index 69c53d4e6e7..42b14013f4c 100644
--- a/sonar-db/src/test/java/org/sonar/db/user/UserDaoTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/user/UserDaoTest.java
@@ -25,6 +25,7 @@ import java.util.List;
 import org.junit.Rule;
 import org.junit.Test;
 import org.junit.experimental.categories.Category;
+import org.junit.rules.ExpectedException;
 import org.sonar.api.user.UserQuery;
 import org.sonar.api.utils.DateUtils;
 import org.sonar.api.utils.System2;
@@ -35,6 +36,7 @@ import org.sonar.test.DbTests;
 
 import static java.util.Arrays.asList;
 import static org.assertj.core.api.Assertions.assertThat;
+import static org.assertj.guava.api.Assertions.assertThat;
 import static org.junit.Assert.fail;
 import static org.mockito.Mockito.mock;
 import static org.mockito.Mockito.when;
@@ -42,6 +44,9 @@ import static org.mockito.Mockito.when;
 @Category(DbTests.class)
 public class UserDaoTest {
 
+  @Rule
+  public ExpectedException thrown = ExpectedException.none();
+
   System2 system2 = mock(System2.class);
 
   @Rule
@@ -160,6 +165,8 @@ public class UserDaoTest {
       .setActive(true)
       .setSalt("1234")
       .setCryptedPassword("abcd")
+      .setExternalIdentity("johngithub")
+      .setExternalIdentityProvider("github")
       .setCreatedAt(date)
       .setUpdatedAt(date);
     underTest.insert(db.getSession(), userDto);
@@ -175,6 +182,8 @@ public class UserDaoTest {
     assertThat(user.getScmAccounts()).isEqualTo(",jo.hn,john2,");
     assertThat(user.getSalt()).isEqualTo("1234");
     assertThat(user.getCryptedPassword()).isEqualTo("abcd");
+    assertThat(user.getExternalIdentity()).isEqualTo("johngithub");
+    assertThat(user.getExternalIdentityProvider()).isEqualTo("github");
     assertThat(user.getCreatedAt()).isEqualTo(date);
     assertThat(user.getUpdatedAt()).isEqualTo(date);
   }
@@ -314,4 +323,23 @@ public class UserDaoTest {
 
     assertThat(underTest.selectByLogin(session, "unknown")).isNull();
   }
+
+  @Test
+  public void select_user_by_external_identity() {
+    db.prepareDbUnit(getClass(), "select_users_by_ext_identity.xml");
+
+    assertThat(underTest.selectByExternalIdentity(session, "mariusgithub", "github")).isPresent();
+    assertThat(underTest.selectByExternalIdentity(session, "mariusgithub", "google")).isAbsent();
+    assertThat(underTest.selectByExternalIdentity(session, "unknown", "unknown")).isAbsent();
+  }
+
+  @Test
+  public void select_or_fail_by_external_identity() throws Exception {
+    db.prepareDbUnit(getClass(), "select_users_by_ext_identity.xml");
+    assertThat(underTest.selectOrFailByExternalIdentity(session, "mariusgithub", "github")).isNotNull();
+
+    thrown.expect(RowNotFoundException.class);
+    thrown.expectMessage("User with identity provider 'unknown' and id 'unknown' has not been found");
+    underTest.selectOrFailByExternalIdentity(session, "unknown", "unknown");
+  }
 }
diff --git a/sonar-db/src/test/java/org/sonar/db/user/UserTesting.java b/sonar-db/src/test/java/org/sonar/db/user/UserTesting.java
index 84addbc26e6..034e623e756 100644
--- a/sonar-db/src/test/java/org/sonar/db/user/UserTesting.java
+++ b/sonar-db/src/test/java/org/sonar/db/user/UserTesting.java
@@ -19,20 +19,24 @@
  */
 package org.sonar.db.user;
 
-import static org.apache.commons.lang.RandomStringUtils.randomAlphabetic;
 import static org.apache.commons.lang.RandomStringUtils.randomAlphanumeric;
 import static org.apache.commons.lang.math.RandomUtils.nextLong;
 
 public class UserTesting {
 
   public static UserDto newUserDto() {
-    UserDto user = new UserDto()
+    return newUserDto(randomAlphanumeric(30), randomAlphanumeric(30), randomAlphanumeric(30));
+  }
+
+  public static UserDto newUserDto(String login, String name, String email) {
+    return new UserDto()
       .setActive(true)
-      .setName(randomAlphanumeric(30))
-      .setEmail(randomAlphabetic(30))
-      .setLogin(randomAlphanumeric(30));
-    user.setCreatedAt(nextLong())
+      .setName(name)
+      .setEmail(email)
+      .setLogin(login)
+      .setExternalIdentity(login)
+      .setExternalIdentityProvider("sonarqube")
+      .setCreatedAt(nextLong())
       .setUpdatedAt(nextLong());
-    return user;
   }
 }
diff --git a/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java b/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java
index 4d8c997e9b4..4fbf2496389 100644
--- a/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/version/MigrationStepModuleTest.java
@@ -29,6 +29,6 @@ public class MigrationStepModuleTest {
   public void verify_count_of_added_MigrationStep_types() {
     ComponentContainer container = new ComponentContainer();
     new MigrationStepModule().configure(container);
-    assertThat(container.size()).isEqualTo(45);
+    assertThat(container.size()).isEqualTo(47);
   }
 }
diff --git a/sonar-db/src/test/java/org/sonar/db/version/v54/AddUsersIdentityColumnsTest.java b/sonar-db/src/test/java/org/sonar/db/version/v54/AddUsersIdentityColumnsTest.java
new file mode 100644
index 00000000000..4981763b600
--- /dev/null
+++ b/sonar-db/src/test/java/org/sonar/db/version/v54/AddUsersIdentityColumnsTest.java
@@ -0,0 +1,51 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.version.v54;
+
+import org.junit.Before;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.api.utils.System2;
+import org.sonar.db.DbTester;
+import org.sonar.db.version.MigrationStep;
+
+import static java.sql.Types.VARCHAR;
+
+public class AddUsersIdentityColumnsTest {
+
+  @Rule
+  public DbTester db = DbTester.createForSchema(System2.INSTANCE, AddUsersIdentityColumnsTest.class, "schema.sql");
+
+  MigrationStep migration;
+
+  @Before
+  public void setUp() {
+    migration = new AddUsersIdentityColumns(db.database());
+  }
+
+  @Test
+  public void update_columns() throws Exception {
+    migration.execute();
+
+    db.assertColumnDefinition("users", "external_identity_provider", VARCHAR, 100);
+    db.assertColumnDefinition("users", "external_identity", VARCHAR, 4000);
+  }
+
+}
diff --git a/sonar-db/src/test/java/org/sonar/db/version/v54/MigrateUsersIdentityTest.java b/sonar-db/src/test/java/org/sonar/db/version/v54/MigrateUsersIdentityTest.java
new file mode 100644
index 00000000000..e6e87c1a2e6
--- /dev/null
+++ b/sonar-db/src/test/java/org/sonar/db/version/v54/MigrateUsersIdentityTest.java
@@ -0,0 +1,91 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2016 SonarSource SA
+ * mailto:contact AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.version.v54;
+
+import java.util.Map;
+import org.junit.Before;
+import org.junit.Rule;
+import org.junit.Test;
+import org.sonar.api.utils.System2;
+import org.sonar.db.DbTester;
+import org.sonar.db.version.MigrationStep;
+
+import static org.assertj.core.api.Assertions.assertThat;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.when;
+
+public class MigrateUsersIdentityTest {
+
+  static final String TABLE = "users";
+
+  static final long NOW = 1500000000000L;
+
+  final System2 system2 = mock(System2.class);
+
+  @Rule
+  public DbTester db = DbTester.createForSchema(System2.INSTANCE, MigrateUsersIdentityTest.class, "schema.sql");
+
+  MigrationStep migration;
+
+  @Before
+  public void setUp() {
+    db.executeUpdateSql("truncate table " + TABLE);
+    when(system2.now()).thenReturn(NOW);
+    migration = new MigrateUsersIdentity(db.database(), system2);
+  }
+
+  @Test
+  public void migrate_empty_db() throws Exception {
+    migration.execute();
+  }
+
+  @Test
+  public void migrate() throws Exception {
+    db.prepareDbUnit(this.getClass(), "migrate.xml");
+
+    migration.execute();
+
+    assertThat(db.countRowsOfTable(TABLE)).isEqualTo(2);
+    assertUser(101, "john", NOW);
+    assertUser(102, "arthur", NOW);
+  }
+
+  @Test
+  public void nothing_to_do_on_already_migrated_data() throws Exception {
+    db.prepareDbUnit(this.getClass(), "migrate-result.xml");
+
+    migration.execute();
+
+    assertThat(db.countRowsOfTable(TABLE)).isEqualTo(2);
+    assertUser(101, "john", 1418215735485L);
+    assertUser(102, "arthur", 1418215735485L);
+  }
+
+  private void assertUser(long userId, String expectedAuthorityId, long expectedUpdatedAt) {
+    Map<String, Object> result = db.selectFirst("SELECT u.external_identity as \"externalIdentity\", " +
+      "u.external_identity_provider as \"externalIdentityProvider\", " +
+      "u.updated_at as \"updatedAt\" " +
+      "FROM users u WHERE u.id=" + userId);
+    assertThat(result.get("externalIdentity")).isEqualTo(expectedAuthorityId);
+    assertThat(result.get("externalIdentityProvider")).isEqualTo("sonarqube");
+    assertThat(result.get("updatedAt")).isEqualTo(expectedUpdatedAt);
+  }
+
+}
diff --git a/sonar-db/src/test/resources/org/sonar/db/user/UserDaoTest/select_users_by_ext_identity.xml b/sonar-db/src/test/resources/org/sonar/db/user/UserDaoTest/select_users_by_ext_identity.xml
new file mode 100644
index 00000000000..247f132231b
--- /dev/null
+++ b/sonar-db/src/test/resources/org/sonar/db/user/UserDaoTest/select_users_by_ext_identity.xml
@@ -0,0 +1,12 @@
+<dataset>
+
+  <users id="101" login="marius" name="Marius" email="marius@lesbronzes.fr" active="[true]" scm_accounts="&#10;ma&#10;marius33&#10;"
+         external_identity_provider="github" external_identity="mariusgithub"
+         created_at="1418215735482" updated_at="1418215735485"
+         salt="79bd6a8e79fb8c76ac8b121cc7e8e11ad1af8365" crypted_password="650d2261c98361e2f67f90ce5c65a95e7d8ea2fg"/>
+  <users id="102" login="sbrandhof" name="Simon Brandhof" email="marius@lesbronzes.fr" active="[true]" scm_accounts="[null]"
+         external_identity_provider="google" external_identity="mariusgoogle"
+         created_at="1418215735482" updated_at="1418215735485"
+         salt="79bd6a8e79fb8c76ac8b121cc7e8e11ad1af8366" crypted_password="650d2261c98361e2f67f90ce5c65a95e7d8ea2fh"/>
+
+</dataset>
diff --git a/sonar-db/src/test/resources/org/sonar/db/version/v54/AddUsersIdentityColumnsTest/schema.sql b/sonar-db/src/test/resources/org/sonar/db/version/v54/AddUsersIdentityColumnsTest/schema.sql
new file mode 100644
index 00000000000..f2e4e85ae66
--- /dev/null
+++ b/sonar-db/src/test/resources/org/sonar/db/version/v54/AddUsersIdentityColumnsTest/schema.sql
@@ -0,0 +1,14 @@
+CREATE TABLE "USERS" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "LOGIN" VARCHAR(255),
+  "NAME" VARCHAR(200),
+  "EMAIL" VARCHAR(100),
+  "CRYPTED_PASSWORD" VARCHAR(40),
+  "SALT" VARCHAR(40),
+  "REMEMBER_TOKEN" VARCHAR(500),
+  "REMEMBER_TOKEN_EXPIRES_AT" TIMESTAMP,
+  "ACTIVE" BOOLEAN DEFAULT TRUE,
+  "SCM_ACCOUNTS" VARCHAR(4000),
+  "CREATED_AT" BIGINT,
+  "UPDATED_AT" BIGINT
+);
diff --git a/sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/migrate-result.xml b/sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/migrate-result.xml
new file mode 100644
index 00000000000..7f4a709a9c1
--- /dev/null
+++ b/sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/migrate-result.xml
@@ -0,0 +1,13 @@
+<dataset>
+
+  <users id="101" login="john" name="John" email="john@email.com" active="[true]" scm_accounts="&#10;jo&#10;"
+         external_identity="john" external_identity_provider="sonarqube"
+         created_at="1418215735482" updated_at="1418215735485"
+         salt="79bd6a8e79fb8c76ac8b121cc7e8e11ad1af8365" crypted_password="650d2261c98361e2f67f90ce5c65a95e7d8ea2fg"/>
+
+  <users id="102" login="arthur" name="Arthur" email="arthur@email.com" active="[false]" scm_accounts=""
+         external_identity="arthur" external_identity_provider="sonarqube"
+         created_at="1418215735482" updated_at="1418215735485"
+         salt="79bd6a8e79fb8c76ac8b121cc7e8e11ad1af8365" crypted_password="650d2261c98361e2f67f90ce5c65a95e7d8ea2fg"/>
+
+</dataset>
diff --git a/sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/migrate.xml b/sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/migrate.xml
new file mode 100644
index 00000000000..0b839d9ca9e
--- /dev/null
+++ b/sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/migrate.xml
@@ -0,0 +1,13 @@
+<dataset>
+
+  <users id="101" login="john" name="John" email="john@email.com" active="[true]" scm_accounts="&#10;jo&#10;"
+         external_identity="[null]" external_identity_provider="[null]"
+         created_at="1418215735482" updated_at="1418215735485"
+         salt="79bd6a8e79fb8c76ac8b121cc7e8e11ad1af8365" crypted_password="650d2261c98361e2f67f90ce5c65a95e7d8ea2fg"/>
+
+  <users id="102" login="arthur" name="Arthur" email="arthur@email.com" active="[false]" scm_accounts=""
+         external_identity="[null]" external_identity_provider="[null]"
+         created_at="1418215735482" updated_at="1418215735485"
+         salt="79bd6a8e79fb8c76ac8b121cc7e8e11ad1af8365" crypted_password="650d2261c98361e2f67f90ce5c65a95e7d8ea2fg"/>
+
+</dataset>
diff --git a/sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/schema.sql b/sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/schema.sql
new file mode 100644
index 00000000000..b9f36106610
--- /dev/null
+++ b/sonar-db/src/test/resources/org/sonar/db/version/v54/MigrateUsersIdentityTest/schema.sql
@@ -0,0 +1,16 @@
+CREATE TABLE "USERS" (
+  "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
+  "LOGIN" VARCHAR(255),
+  "NAME" VARCHAR(200),
+  "EMAIL" VARCHAR(100),
+  "CRYPTED_PASSWORD" VARCHAR(40),
+  "SALT" VARCHAR(40),
+  "REMEMBER_TOKEN" VARCHAR(500),
+  "REMEMBER_TOKEN_EXPIRES_AT" TIMESTAMP,
+  "ACTIVE" BOOLEAN DEFAULT TRUE,
+  "SCM_ACCOUNTS" VARCHAR(4000),
+  "EXTERNAL_IDENTITY_PROVIDER" VARCHAR(4000),
+  "EXTERNAL_IDENTITY" VARCHAR(100),
+  "CREATED_AT" BIGINT,
+  "UPDATED_AT" BIGINT
+);
-- 
cgit v1.2.3