From 49592e832489cf99d163581cd3567bc0c9e6a3c7 Mon Sep 17 00:00:00 2001 From: Pierre Date: Fri, 3 Apr 2020 15:08:26 +0200 Subject: [PATCH] SONAR-13221 replace auto-incremented ID by UUID for user tokens --- .../java/org/sonar/db/user/UserTokenDao.java | 8 ++ .../java/org/sonar/db/user/UserTokenDto.java | 9 ++ .../org/sonar/db/user/UserTokenMapper.xml | 5 +- server/sonar-db-dao/src/schema/schema-sq.ddl | 6 +- .../org/sonar/db/user/UserTokenDaoTest.java | 1 + .../db/migration/version/v83/DbVersion83.java | 21 ++++ .../PopulateNotificationUuidAndCreatedAt.java | 2 +- ...imaryKeyOnUuidColumnOfUserTokensTable.java | 38 ++++++++ .../usertokens/AddUuidColumnToUserTokens.java | 52 ++++++++++ .../DropIdColumnOfUserTokensTable.java | 41 ++++++++ ...PrimaryKeyOnIdColumnOfUserTokensTable.java | 41 ++++++++ .../MakeUserTokensUuidNotNullable.java | 52 ++++++++++ .../usertokens/PopulateUserTokensUuid.java | 52 ++++++++++ ...yKeyOnUuidColumnOfUserTokensTableTest.java | 49 ++++++++++ .../AddUuidColumnToUserTokensTest.java | 63 ++++++++++++ .../DropIdColumnOfUserTokensTableTest.java | 49 ++++++++++ ...aryKeyOnIdColumnOfUserTokensTableTest.java | 55 +++++++++++ .../MakeUserTokensUuidNotNullableTest.java | 43 ++++++++ .../PopulateUserTokensUuidTest.java | 97 +++++++++++++++++++ .../schema.sql | 11 +++ .../AddUuidColumnToUserTokensTest/schema.sql | 11 +++ .../schema.sql | 12 +++ .../schema.sql | 11 +++ .../schema.sql | 12 +++ .../PopulateUserTokensUuidTest/schema.sql | 12 +++ 25 files changed, 748 insertions(+), 5 deletions(-) create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokens.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullable.java create mode 100644 server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuid.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokensTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullableTest.java create mode 100644 server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuidTest.java create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokensTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullableTest/schema.sql create mode 100644 server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuidTest/schema.sql diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDao.java index 297f889263b..2a15e21b403 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDao.java @@ -24,6 +24,7 @@ import java.util.HashMap; import java.util.List; import java.util.Map; import javax.annotation.CheckForNull; +import org.sonar.core.util.UuidFactory; import org.sonar.db.Dao; import org.sonar.db.DbSession; @@ -32,7 +33,14 @@ import static org.sonar.db.DatabaseUtils.executeLargeInputs; public class UserTokenDao implements Dao { + private UuidFactory uuidFactory; + + public UserTokenDao(UuidFactory uuidFactory) { + this.uuidFactory = uuidFactory; + } + public void insert(DbSession dbSession, UserTokenDto userTokenDto) { + userTokenDto.setUuid(uuidFactory.create()); mapper(dbSession).insert(userTokenDto); } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDto.java index 21d1cbce2b9..d831a1bae40 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDto.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/user/UserTokenDto.java @@ -26,6 +26,7 @@ import static org.sonar.db.user.UserTokenValidator.checkTokenHash; public class UserTokenDto { + private String uuid; private String userUuid; private String name; private String tokenHash; @@ -38,6 +39,14 @@ public class UserTokenDto { private Long createdAt; + public String getUuid() { + return uuid; + } + + public void setUuid(String uuid) { + this.uuid = uuid; + } + public String getUserUuid() { return userUuid; } diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserTokenMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserTokenMapper.xml index 5692445455d..7befbd422ff 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserTokenMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/user/UserTokenMapper.xml @@ -4,6 +4,7 @@ + t.uuid as "uuid", t.user_uuid as "userUuid", t.name as "name", t.token_hash as "tokenHash", @@ -11,13 +12,15 @@ t.created_at as "createdAt" - + insert into user_tokens ( + uuid, user_uuid, name, token_hash, created_at ) values ( + #{uuid, jdbcType=VARCHAR}, #{userUuid, jdbcType=VARCHAR}, #{name, jdbcType=VARCHAR}, #{tokenHash, jdbcType=VARCHAR}, diff --git a/server/sonar-db-dao/src/schema/schema-sq.ddl b/server/sonar-db-dao/src/schema/schema-sq.ddl index 5853db5ddb5..9b1a5c03908 100644 --- a/server/sonar-db-dao/src/schema/schema-sq.ddl +++ b/server/sonar-db-dao/src/schema/schema-sq.ddl @@ -930,14 +930,14 @@ CREATE INDEX "USER_ROLES_USER" ON "USER_ROLES"("USER_ID"); CREATE INDEX "USER_ROLES_COMPONENT_UUID" ON "USER_ROLES"("COMPONENT_UUID"); CREATE TABLE "USER_TOKENS"( - "ID" INTEGER NOT NULL AUTO_INCREMENT (1,1), "USER_UUID" VARCHAR(255) NOT NULL, "NAME" VARCHAR(100) NOT NULL, "TOKEN_HASH" VARCHAR(255) NOT NULL, "LAST_CONNECTION_DATE" BIGINT, - "CREATED_AT" BIGINT NOT NULL + "CREATED_AT" BIGINT NOT NULL, + "UUID" VARCHAR(40) NOT NULL ); -ALTER TABLE "USER_TOKENS" ADD CONSTRAINT "PK_USER_TOKENS" PRIMARY KEY("ID"); +ALTER TABLE "USER_TOKENS" ADD CONSTRAINT "PK_USER_TOKENS" PRIMARY KEY("UUID"); CREATE UNIQUE INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS"("USER_UUID", "NAME"); CREATE UNIQUE INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS"("TOKEN_HASH"); diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/user/UserTokenDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/user/UserTokenDaoTest.java index 5626c777662..cb1b6039e9b 100644 --- a/server/sonar-db-dao/src/test/java/org/sonar/db/user/UserTokenDaoTest.java +++ b/server/sonar-db-dao/src/test/java/org/sonar/db/user/UserTokenDaoTest.java @@ -49,6 +49,7 @@ public class UserTokenDaoTest { UserTokenDto userTokenFromDb = underTest.selectByTokenHash(db.getSession(), userToken.getTokenHash()); assertThat(userTokenFromDb).isNotNull(); + assertThat(userTokenFromDb.getUuid()).isEqualTo(userToken.getUuid()); assertThat(userTokenFromDb.getName()).isEqualTo(userToken.getName()); assertThat(userTokenFromDb.getCreatedAt()).isEqualTo(userToken.getCreatedAt()); assertThat(userTokenFromDb.getTokenHash()).isEqualTo(userToken.getTokenHash()); diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/DbVersion83.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/DbVersion83.java index dbf194d7ffd..dd717115eed 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/DbVersion83.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/DbVersion83.java @@ -61,6 +61,12 @@ import org.sonar.server.platform.db.migration.version.v83.projectmeasures.Popula import org.sonar.server.platform.db.migration.version.v83.snapshots.issues.AddPrimaryKeyOnUuidColumnOfSnapshotsTable; import org.sonar.server.platform.db.migration.version.v83.snapshots.issues.DropIdColumnOfSnapshotsTable; import org.sonar.server.platform.db.migration.version.v83.snapshots.issues.DropPrimaryKeyOnIdColumnOfSnapshotsTable; +import org.sonar.server.platform.db.migration.version.v83.usertokens.AddPrimaryKeyOnUuidColumnOfUserTokensTable; +import org.sonar.server.platform.db.migration.version.v83.usertokens.AddUuidColumnToUserTokens; +import org.sonar.server.platform.db.migration.version.v83.usertokens.DropIdColumnOfUserTokensTable; +import org.sonar.server.platform.db.migration.version.v83.usertokens.DropPrimaryKeyOnIdColumnOfUserTokensTable; +import org.sonar.server.platform.db.migration.version.v83.usertokens.MakeUserTokensUuidNotNullable; +import org.sonar.server.platform.db.migration.version.v83.usertokens.PopulateUserTokensUuid; public class DbVersion83 implements DbVersion { @Override @@ -136,6 +142,21 @@ public class DbVersion83 implements DbVersion { .add(3437, "Drop primary key on 'ID' column of 'PROJECT_MEASURES' table", DropPrimaryKeyOnIdColumnOfProjectMeasuresTable.class) .add(3438, "Add primary key on 'UUID' column of 'PROJECT_MEASURES' table", AddPrimaryKeyOnUuidColumnOfProjectMeasuresTable.class) .add(3439, "Drop column 'ID' of 'PROJECT_MEASURES' table", DropIdColumnOfProjectMeasuresTable.class) + + // Migration of USER_TOKENS table + .add(3440, "Add 'UUID' column on 'USER_TOKENS' table", AddUuidColumnToUserTokens.class) + .add(3441, "Populate 'uuid' for 'USER_TOKENS'", PopulateUserTokensUuid.class) + .add(3442, "Make 'uuid' column not nullable for user_tokens", MakeUserTokensUuidNotNullable.class) + .add(3443, "Drop primary key on 'ID' column of 'USER_TOKENS' table", DropPrimaryKeyOnIdColumnOfUserTokensTable.class) + .add(3444, "Add primary key on 'UUID' column of 'USER_TOKENS' table", AddPrimaryKeyOnUuidColumnOfUserTokensTable.class) + .add(3445, "Drop column 'ID' of 'USER_TOKENS' table", DropIdColumnOfUserTokensTable.class) + + + + + + + ; } } diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/notifications/PopulateNotificationUuidAndCreatedAt.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/notifications/PopulateNotificationUuidAndCreatedAt.java index a34eab55afb..0c5eeb43b28 100644 --- a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/notifications/PopulateNotificationUuidAndCreatedAt.java +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/notifications/PopulateNotificationUuidAndCreatedAt.java @@ -42,7 +42,7 @@ public class PopulateNotificationUuidAndCreatedAt extends DataChange { protected void execute(Context context) throws SQLException { MassUpdate massUpdate = context.prepareMassUpdate(); - massUpdate.select("select id from notifications order by id asc"); + massUpdate.select("select id from notifications where uuid is null order by id asc"); massUpdate.update("update notifications set uuid = ?, created_at = ? where id = ?"); // now - 7 days, to have previous notification in the past diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTable.java new file mode 100644 index 00000000000..803987a1483 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTable.java @@ -0,0 +1,38 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DdlChange; +import org.sonar.server.platform.db.migration.version.v83.util.AddPrimaryKeyBuilder; + +public class AddPrimaryKeyOnUuidColumnOfUserTokensTable extends DdlChange { + + public AddPrimaryKeyOnUuidColumnOfUserTokensTable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new AddPrimaryKeyBuilder("user_tokens", "uuid").build()); + } + +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokens.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokens.java new file mode 100644 index 00000000000..14c763b3ba8 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokens.java @@ -0,0 +1,52 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.def.BigIntegerColumnDef; +import org.sonar.server.platform.db.migration.def.VarcharColumnDef; +import org.sonar.server.platform.db.migration.sql.AddColumnsBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.BigIntegerColumnDef.newBigIntegerColumnDefBuilder; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class AddUuidColumnToUserTokens extends DdlChange { + private static final String TABLE = "user_tokens"; + + private static final VarcharColumnDef uuidColumnDefinition = newVarcharColumnDefBuilder() + .setColumnName("uuid") + .setIsNullable(true) + .setDefaultValue(null) + .setLimit(VarcharColumnDef.UUID_SIZE) + .build(); + + public AddUuidColumnToUserTokens(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new AddColumnsBuilder(getDialect(), TABLE) + .addColumn(uuidColumnDefinition) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTable.java new file mode 100644 index 00000000000..5bd6df1a061 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTable.java @@ -0,0 +1,41 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.sql.DropColumnsBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +public class DropIdColumnOfUserTokensTable extends DdlChange { + + private Database db; + + public DropIdColumnOfUserTokensTable(Database db) { + super(db); + this.db = db; + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new DropColumnsBuilder(db.getDialect(), "user_tokens", "id").build()); + } + +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTable.java new file mode 100644 index 00000000000..bc6c354d63a --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTable.java @@ -0,0 +1,41 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DdlChange; +import org.sonar.server.platform.db.migration.version.v83.util.DropPrimaryKeySqlGenerator; + +public class DropPrimaryKeyOnIdColumnOfUserTokensTable extends DdlChange { + + private final DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator; + + public DropPrimaryKeyOnIdColumnOfUserTokensTable(Database db, DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator) { + super(db); + this.dropPrimaryKeySqlGenerator = dropPrimaryKeySqlGenerator; + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(dropPrimaryKeySqlGenerator.generate("user_tokens", "user_tokens", "id")); + } + +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullable.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullable.java new file mode 100644 index 00000000000..07505fa5b96 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullable.java @@ -0,0 +1,52 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.def.BigIntegerColumnDef; +import org.sonar.server.platform.db.migration.def.VarcharColumnDef; +import org.sonar.server.platform.db.migration.sql.AlterColumnsBuilder; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.sonar.server.platform.db.migration.def.BigIntegerColumnDef.newBigIntegerColumnDefBuilder; +import static org.sonar.server.platform.db.migration.def.VarcharColumnDef.newVarcharColumnDefBuilder; + +public class MakeUserTokensUuidNotNullable extends DdlChange { + private static final String TABLE = "user_tokens"; + + private static final VarcharColumnDef uuidColumnDefinition = newVarcharColumnDefBuilder() + .setColumnName("uuid") + .setIsNullable(false) + .setDefaultValue(null) + .setLimit(VarcharColumnDef.UUID_SIZE) + .build(); + + public MakeUserTokensUuidNotNullable(Database db) { + super(db); + } + + @Override + public void execute(Context context) throws SQLException { + context.execute(new AlterColumnsBuilder(getDialect(), TABLE) + .updateColumn(uuidColumnDefinition) + .build()); + } +} diff --git a/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuid.java b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuid.java new file mode 100644 index 00000000000..a6933029229 --- /dev/null +++ b/server/sonar-db-migration/src/main/java/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuid.java @@ -0,0 +1,52 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import java.util.concurrent.atomic.AtomicLong; +import org.sonar.api.utils.System2; +import org.sonar.core.util.UuidFactory; +import org.sonar.db.Database; +import org.sonar.server.platform.db.migration.step.DataChange; +import org.sonar.server.platform.db.migration.step.MassUpdate; + +public class PopulateUserTokensUuid extends DataChange { + + private final UuidFactory uuidFactory; + + public PopulateUserTokensUuid(Database db, UuidFactory uuidFactory) { + super(db); + this.uuidFactory = uuidFactory; + } + + @Override + protected void execute(Context context) throws SQLException { + MassUpdate massUpdate = context.prepareMassUpdate(); + + massUpdate.select("select id from user_tokens where uuid is null order by id asc"); + massUpdate.update("update user_tokens set uuid = ? where id = ?"); + + massUpdate.execute((row, update) -> { + update.setString(1, uuidFactory.create()); + update.setLong(2, row.getLong(1)); + return true; + }); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTableTest.java new file mode 100644 index 00000000000..a04284e6375 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTableTest.java @@ -0,0 +1,49 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.MigrationStep; + +import static org.assertj.core.api.Assertions.assertThatThrownBy; + +public class AddPrimaryKeyOnUuidColumnOfUserTokensTableTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(AddPrimaryKeyOnUuidColumnOfUserTokensTableTest.class, "schema.sql"); + + private MigrationStep underTest = new AddPrimaryKeyOnUuidColumnOfUserTokensTable(db.database()); + + @Test + public void execute() throws SQLException { + underTest.execute(); + + db.assertPrimaryKey("user_tokens", "pk_user_tokens", "uuid"); + } + + @Test + public void migration_is_not_re_entrant() throws SQLException { + underTest.execute(); + + assertThatThrownBy(() -> underTest.execute()).isInstanceOf(IllegalStateException.class); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokensTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokensTest.java new file mode 100644 index 00000000000..5435864c12a --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokensTest.java @@ -0,0 +1,63 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import java.sql.Types; +import org.junit.Before; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DdlChange; + +import static org.assertj.core.api.Assertions.assertThat; + +public class AddUuidColumnToUserTokensTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(AddUuidColumnToUserTokensTest.class, "schema.sql"); + + private DdlChange underTest = new AddUuidColumnToUserTokens(db.database()); + + @Before + public void setup() { + insertUserToken(1L, "user1", "name1", "token1"); + insertUserToken(2L, "user2", "name2", "token2"); + insertUserToken(3L, "user3", "name3", "token3"); + } + + @Test + public void add_uuid_column_to_user_tokens() throws SQLException { + underTest.execute(); + + db.assertColumnDefinition("user_tokens", "uuid", Types.VARCHAR, 40, true); + + assertThat(db.countSql("select count(id) from user_tokens")) + .isEqualTo(3); + } + + private void insertUserToken(Long id, String userUuid, String name, String tokenHash) { + db.executeInsert("user_tokens", + "id", id, + "USER_UUID", userUuid, + "NAME", name, + "TOKEN_HASH", tokenHash, + "CREATED_AT", 0L); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTableTest.java new file mode 100644 index 00000000000..7ccd4c4b7ca --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTableTest.java @@ -0,0 +1,49 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.MigrationStep; + +import static org.assertj.core.api.Assertions.assertThatThrownBy; + +public class DropIdColumnOfUserTokensTableTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DropIdColumnOfUserTokensTableTest.class, "schema.sql"); + + private MigrationStep underTest = new DropIdColumnOfUserTokensTable(db.database()); + + @Test + public void execute() throws SQLException { + underTest.execute(); + + db.assertColumnDoesNotExist("user_tokens", "id"); + } + + @Test + public void migration_is_not_re_entrant() throws SQLException { + underTest.execute(); + + assertThatThrownBy(() -> underTest.execute()).isInstanceOf(IllegalStateException.class); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTableTest.java new file mode 100644 index 00000000000..a58a4a3e1dc --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTableTest.java @@ -0,0 +1,55 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.MigrationStep; +import org.sonar.server.platform.db.migration.version.v83.util.DropPrimaryKeySqlGenerator; +import org.sonar.server.platform.db.migration.version.v83.util.SqlHelper; + +import static org.assertj.core.api.Assertions.assertThatThrownBy; + +public class DropPrimaryKeyOnIdColumnOfUserTokensTableTest { + + private static final String TABLE_NAME = "user_tokens"; + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(DropPrimaryKeyOnIdColumnOfUserTokensTableTest.class, "schema.sql"); + + private DropPrimaryKeySqlGenerator dropPrimaryKeySqlGenerator = new DropPrimaryKeySqlGenerator(db.database(), new SqlHelper(db.database())); + + private MigrationStep underTest = new DropPrimaryKeyOnIdColumnOfUserTokensTable(db.database(), dropPrimaryKeySqlGenerator); + + @Test + public void execute() throws SQLException { + underTest.execute(); + + db.assertNoPrimaryKey(TABLE_NAME); + } + + @Test + public void migration_is_not_re_entrant() throws SQLException { + underTest.execute(); + + assertThatThrownBy(() -> underTest.execute()).isInstanceOf(IllegalStateException.class); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullableTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullableTest.java new file mode 100644 index 00000000000..929af5554a6 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullableTest.java @@ -0,0 +1,43 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.MigrationStep; + +import static java.sql.Types.VARCHAR; + +public class MakeUserTokensUuidNotNullableTest { + + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(MakeUserTokensUuidNotNullableTest.class, "schema.sql"); + + private MigrationStep underTest = new MakeUserTokensUuidNotNullable(db.database()); + + @Test + public void created_at_and_uuid_columns_are_not_null() throws SQLException { + underTest.execute(); + + db.assertColumnDefinition("user_tokens", "uuid", VARCHAR, null, false); + } +} diff --git a/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuidTest.java b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuidTest.java new file mode 100644 index 00000000000..e0a967fb5a6 --- /dev/null +++ b/server/sonar-db-migration/src/test/java/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuidTest.java @@ -0,0 +1,97 @@ +/* + * SonarQube + * Copyright (C) 2009-2020 SonarSource SA + * mailto:info 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.server.platform.db.migration.version.v83.usertokens; + +import java.sql.SQLException; +import java.util.List; +import java.util.Objects; +import java.util.stream.Collectors; +import org.junit.Rule; +import org.junit.Test; +import org.sonar.core.util.UuidFactory; +import org.sonar.core.util.UuidFactoryFast; +import org.sonar.db.CoreDbTester; +import org.sonar.server.platform.db.migration.step.DataChange; + +import static org.assertj.core.api.Assertions.assertThat; + +public class PopulateUserTokensUuidTest { + @Rule + public CoreDbTester db = CoreDbTester.createForSchema(PopulateUserTokensUuidTest.class, "schema.sql"); + + private UuidFactory uuidFactory = UuidFactoryFast.getInstance(); + private DataChange underTest = new PopulateUserTokensUuid(db.database(), uuidFactory); + + @Test + public void populate_uuids() throws SQLException { + insertUserToken(1L, "user1", "name1", "token1"); + insertUserToken(2L, "user2", "name2", "token2"); + insertUserToken(3L, "user3", "name3", "token3"); + + underTest.execute(); + + verifyUuidsAreNotNull(); + } + + @Test + public void migration_is_reentrant() throws SQLException { + insertUserToken(1L, "user1", "name1", "token1"); + insertUserToken(2L, "user2", "name2", "token2"); + insertUserToken(3L, "user3", "name3", "token3"); + + underTest.execute(); + verifyUuidsAreNotNull(); + insertUserToken(4L, "user4", "name4", "token4"); + insertUserToken(5L, "user5", "name5", "token5"); + + List uuids = db.select("select uuid from user_tokens where id = 1 or id = 2 or id = 3") + .stream() + .map(row -> (String) row.get("UUID")) + .collect(Collectors.toList()); + + // re-entrant + underTest.execute(); + verifyUuidsAreNotNull(); + + // verify that uuid set during the first migration have not been updated during the second migration + assertThat(db.select("select uuid from user_tokens") + .stream() + .map(row -> (String) row.get("UUID")) + .collect(Collectors.toList())) + .containsAll(uuids); + } + + private void verifyUuidsAreNotNull() { + assertThat(db.select("select uuid from user_tokens") + .stream() + .map(row -> row.get("UUID")) + .filter(Objects::isNull) + .collect(Collectors.toList())).isEmpty(); + } + + private void insertUserToken(Long id, String userUuid, String name, String tokenHash) { + db.executeInsert("user_tokens", + "id", id, + "USER_UUID", userUuid, + "NAME", name, + "TOKEN_HASH", tokenHash, + "CREATED_AT", 0L); + } +} diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTableTest/schema.sql new file mode 100644 index 00000000000..ca5001f995a --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/AddPrimaryKeyOnUuidColumnOfUserTokensTableTest/schema.sql @@ -0,0 +1,11 @@ +CREATE TABLE "USER_TOKENS"( + "ID" INTEGER NOT NULL, + "UUID" VARCHAR(40) NOT NULL, + "USER_UUID" VARCHAR(255) NOT NULL, + "NAME" VARCHAR(100) NOT NULL, + "TOKEN_HASH" VARCHAR(255) NOT NULL, + "LAST_CONNECTION_DATE" BIGINT, + "CREATED_AT" BIGINT NOT NULL +); +CREATE UNIQUE INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS"("USER_UUID", "NAME"); +CREATE UNIQUE INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS"("TOKEN_HASH"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokensTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokensTest/schema.sql new file mode 100644 index 00000000000..485c3d4f80c --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/AddUuidColumnToUserTokensTest/schema.sql @@ -0,0 +1,11 @@ +CREATE TABLE "USER_TOKENS"( + "ID" INTEGER NOT NULL AUTO_INCREMENT (1,1), + "USER_UUID" VARCHAR(255) NOT NULL, + "NAME" VARCHAR(100) NOT NULL, + "TOKEN_HASH" VARCHAR(255) NOT NULL, + "LAST_CONNECTION_DATE" BIGINT, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "USER_TOKENS" ADD CONSTRAINT "PK_USER_TOKENS" PRIMARY KEY("ID"); +CREATE UNIQUE INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS"("USER_UUID", "NAME"); +CREATE UNIQUE INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS"("TOKEN_HASH"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTableTest/schema.sql new file mode 100644 index 00000000000..894ee4b0fc4 --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/DropIdColumnOfUserTokensTableTest/schema.sql @@ -0,0 +1,12 @@ +CREATE TABLE "USER_TOKENS"( + "ID" INTEGER NOT NULL, + "UUID" VARCHAR(40) NOT NULL, + "USER_UUID" VARCHAR(255) NOT NULL, + "NAME" VARCHAR(100) NOT NULL, + "TOKEN_HASH" VARCHAR(255) NOT NULL, + "LAST_CONNECTION_DATE" BIGINT, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "USER_TOKENS" ADD CONSTRAINT "PK_USER_TOKENS" PRIMARY KEY("UUID"); +CREATE UNIQUE INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS"("USER_UUID", "NAME"); +CREATE UNIQUE INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS"("TOKEN_HASH"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTableTest/schema.sql new file mode 100644 index 00000000000..485c3d4f80c --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/DropPrimaryKeyOnIdColumnOfUserTokensTableTest/schema.sql @@ -0,0 +1,11 @@ +CREATE TABLE "USER_TOKENS"( + "ID" INTEGER NOT NULL AUTO_INCREMENT (1,1), + "USER_UUID" VARCHAR(255) NOT NULL, + "NAME" VARCHAR(100) NOT NULL, + "TOKEN_HASH" VARCHAR(255) NOT NULL, + "LAST_CONNECTION_DATE" BIGINT, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "USER_TOKENS" ADD CONSTRAINT "PK_USER_TOKENS" PRIMARY KEY("ID"); +CREATE UNIQUE INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS"("USER_UUID", "NAME"); +CREATE UNIQUE INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS"("TOKEN_HASH"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullableTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullableTest/schema.sql new file mode 100644 index 00000000000..df2072a73cb --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/MakeUserTokensUuidNotNullableTest/schema.sql @@ -0,0 +1,12 @@ +CREATE TABLE "USER_TOKENS"( + "ID" INTEGER NOT NULL, + "UUID" VARCHAR(40), + "USER_UUID" VARCHAR(255) NOT NULL, + "NAME" VARCHAR(100) NOT NULL, + "TOKEN_HASH" VARCHAR(255) NOT NULL, + "LAST_CONNECTION_DATE" BIGINT, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "USER_TOKENS" ADD CONSTRAINT "PK_USER_TOKENS" PRIMARY KEY("ID"); +CREATE UNIQUE INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS"("USER_UUID", "NAME"); +CREATE UNIQUE INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS"("TOKEN_HASH"); diff --git a/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuidTest/schema.sql b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuidTest/schema.sql new file mode 100644 index 00000000000..b4869dc5a9c --- /dev/null +++ b/server/sonar-db-migration/src/test/resources/org/sonar/server/platform/db/migration/version/v83/usertokens/PopulateUserTokensUuidTest/schema.sql @@ -0,0 +1,12 @@ +CREATE TABLE "USER_TOKENS"( + "ID" INTEGER NOT NULL AUTO_INCREMENT (1,1), + "UUID" VARCHAR(40), + "USER_UUID" VARCHAR(255) NOT NULL, + "NAME" VARCHAR(100) NOT NULL, + "TOKEN_HASH" VARCHAR(255) NOT NULL, + "LAST_CONNECTION_DATE" BIGINT, + "CREATED_AT" BIGINT NOT NULL +); +ALTER TABLE "USER_TOKENS" ADD CONSTRAINT "PK_USER_TOKENS" PRIMARY KEY("ID"); +CREATE UNIQUE INDEX "USER_TOKENS_USER_UUID_NAME" ON "USER_TOKENS"("USER_UUID", "NAME"); +CREATE UNIQUE INDEX "USER_TOKENS_TOKEN_HASH" ON "USER_TOKENS"("TOKEN_HASH"); -- 2.39.5