Tony CHEMIT pushed to branch develop at ultreiaio / ird-observe Commits: 9f27b93b by Tony Chemit at 2022-10-27T19:35:37+02:00 tms_version va-t-elle rester dans le schéma public ? - Closes #2218 - - - - - 12 changed files: - core/persistence/test/src/test/java/org/nuiton/topia/persistence/jdbc/JdbcHelperH2Test.java - core/persistence/test/src/test/java/org/nuiton/topia/persistence/jdbc/JdbcSecurityHelperH2Test.java - core/persistence/test/src/test/java/org/nuiton/topia/persistence/security/SecurityScriptHelperTest.java - toolkit/persistence/src/main/java/org/nuiton/topia/persistence/jdbc/JdbcHelper.java - toolkit/persistence/src/main/java/org/nuiton/topia/persistence/jdbc/JdbcSecurityHelper.java - toolkit/persistence/src/main/java/org/nuiton/topia/persistence/jdbc/JdbcSecurityHelperH2.java - toolkit/persistence/src/main/java/org/nuiton/topia/persistence/jdbc/JdbcSecurityHelperPostgres.java - toolkit/persistence/src/main/java/org/nuiton/topia/service/migration/TopiaMigrationServiceContext.java - toolkit/persistence/src/main/java/org/nuiton/topia/service/migration/TopiaMigrationServiceImpl.java - toolkit/persistence/src/main/java/org/nuiton/topia/service/migration/version/MigrationServiceSqlHelper.java - toolkit/persistence/src/main/java/org/nuiton/topia/service/migration/version/TMSVersion.java - toolkit/persistence/src/main/java/org/nuiton/topia/service/sql/internal/consumer/AddVersionTableConsumer.java Changes: ===================================== core/persistence/test/src/test/java/org/nuiton/topia/persistence/jdbc/JdbcHelperH2Test.java ===================================== @@ -30,6 +30,7 @@ import org.junit.Assert; import org.junit.Before; import org.junit.ClassRule; import org.junit.Test; +import org.nuiton.topia.service.migration.version.MigrationServiceSqlHelper; import java.sql.SQLException; @@ -54,20 +55,17 @@ public class JdbcHelperH2Test extends PersistenceTestSupportWrite { @Test public void isTableExist() throws SQLException { - boolean actual = jdbcHelper.isTableExist("Public", "Tms_version"); - Assert.assertTrue(actual); + Assert.assertTrue(jdbcHelper.isTableExist(MigrationServiceSqlHelper.CURRENT.schemaName(), MigrationServiceSqlHelper.CURRENT.tableName())); + Assert.assertTrue(jdbcHelper.isTableExist(MigrationServiceSqlHelper.CURRENT.schemaName().toUpperCase(), MigrationServiceSqlHelper.CURRENT.tableName())); + Assert.assertTrue(jdbcHelper.isTableExist(MigrationServiceSqlHelper.CURRENT.schemaName().toUpperCase(), MigrationServiceSqlHelper.CURRENT.tableName().toUpperCase())); - actual = jdbcHelper.isTableExist("PUBLIC", "tms_version-fake"); - Assert.assertFalse(actual); + Assert.assertFalse(jdbcHelper.isTableExist(MigrationServiceSqlHelper.CURRENT.schemaName(), MigrationServiceSqlHelper.CURRENT.tableName()+"-Fake")); } @Test public void isSchemaExist() throws SQLException { - boolean actual = jdbcHelper.isSchemaExist("Public"); - Assert.assertTrue(actual); - - actual = jdbcHelper.isSchemaExist("PUBLIC-fake"); - Assert.assertFalse(actual); + Assert.assertTrue(jdbcHelper.isSchemaExist("Public")); + Assert.assertFalse(jdbcHelper.isSchemaExist("PUBLIC-fake")); } } ===================================== core/persistence/test/src/test/java/org/nuiton/topia/persistence/jdbc/JdbcSecurityHelperH2Test.java ===================================== @@ -31,6 +31,7 @@ import org.junit.Assert; import org.junit.Before; import org.junit.ClassRule; import org.junit.Test; +import org.nuiton.topia.service.migration.version.MigrationServiceSqlHelper; import java.util.List; import java.util.Set; @@ -62,10 +63,9 @@ public class JdbcSecurityHelperH2Test extends PersistenceTestSupportWrite { Assert.assertNotNull(actual); Assert.assertEquals(0, actual.size()); - actual = securityHelper.getTables(Set.of("PUbLIC"), Set.of()); + actual = securityHelper.getTables(Set.of(MigrationServiceSqlHelper.CURRENT.schemaName()), Set.of()); Assert.assertNotNull(actual); - Assert.assertEquals(1, actual.size()); - Assert.assertEquals(List.of(Pair.of("PUBLIC", "TMS_VERSION")), actual); + Assert.assertTrue(actual.contains(Pair.of(MigrationServiceSqlHelper.CURRENT.schemaName().toUpperCase(), MigrationServiceSqlHelper.CURRENT.tableName().toUpperCase()))); } @Test @@ -90,7 +90,7 @@ public class JdbcSecurityHelperH2Test extends PersistenceTestSupportWrite { @Test public void getTablePrivileges() { - Set<String> actual = securityHelper.getTablePrivileges("PUbLIC", "Tms_VERSION"); + Set<String> actual = securityHelper.getTablePrivileges(MigrationServiceSqlHelper.CURRENT.schemaName(), MigrationServiceSqlHelper.CURRENT.tableName()); Assert.assertNotNull(actual); Assert.assertEquals(0, actual.size()); } ===================================== core/persistence/test/src/test/java/org/nuiton/topia/persistence/security/SecurityScriptHelperTest.java ===================================== @@ -37,6 +37,7 @@ import org.junit.ClassRule; import org.junit.Test; import org.nuiton.topia.persistence.jdbc.JdbcHelper; import org.nuiton.topia.persistence.jdbc.JdbcSecurityHelper; +import org.nuiton.topia.service.migration.version.MigrationServiceSqlHelper; import java.sql.SQLException; import java.util.Map; @@ -92,7 +93,7 @@ public class SecurityScriptHelperTest extends PersistenceTestSupportWrite { SecurityScriptHelper securityScriptHelper = localTestMethodResource.getTopiaApplicationContext().newSecurityScriptHelper(); securityScriptHelper.applySecurity(users); - Set<String> actual = securityHelper.getTablePrivileges("PUBLIC", "TMS_VERSION"); + Set<String> actual = securityHelper.getTablePrivileges(MigrationServiceSqlHelper.CURRENT.schemaName().toUpperCase(), MigrationServiceSqlHelper.CURRENT.tableName().toUpperCase()); Assert.assertNotNull(actual); Assert.assertEquals(4, actual.size()); Assert.assertEquals(Set.of("DELETE", "INSERT", "UPDATE", "SELECT"), actual); ===================================== toolkit/persistence/src/main/java/org/nuiton/topia/persistence/jdbc/JdbcHelper.java ===================================== @@ -34,6 +34,8 @@ import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; +import java.util.LinkedList; +import java.util.List; import java.util.Objects; /** @@ -116,17 +118,38 @@ public abstract class JdbcHelper { } public String runSelectOnString(String sql) throws SQLException { - return runSelect(sql, resultSet -> resultSet.next() ? resultSet.getString(1) : null); + return runSelect(sql, resultSet -> resultSet.getString(1)); } public <T> T runSelect(String sql, SqlFunction<ResultSet, T> function) throws SQLException { try (Connection connection = openConnection()) { try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) { try (ResultSet resultSet = preparedStatement.executeQuery()) { - return function.apply(resultSet); + if (resultSet.next()) { + return function.apply(resultSet); + } + return null; + } + } + } + } + + public <T> List<T> runMultipleSelect(String sql, SqlFunction<ResultSet, T> function) throws SQLException { + List<T> result = new LinkedList<>(); + try (Connection connection = openConnection()) { + try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) { + try (ResultSet resultSet = preparedStatement.executeQuery()) { + while (resultSet.next()) { + + T row = function.apply(resultSet); + if (row != null) { + result.add(row); + } + } } } } + return result; } public <T> T runOnMetadata(SqlFunction<DatabaseMetaData, T> function) throws SQLException { ===================================== toolkit/persistence/src/main/java/org/nuiton/topia/persistence/jdbc/JdbcSecurityHelper.java ===================================== @@ -161,14 +161,14 @@ public interface JdbcSecurityHelper { return getLegacyVersion(); } try { - return MigrationServiceSqlHelper.DEFAULT.getVersion(jdbcHelper()).map(TMSVersion::toVersion).orElse(Version.VZERO); + return MigrationServiceSqlHelper.DEFAULT.getVersion(jdbcHelper()).map(TMSVersion::getVersion).orElse(Version.VZERO); } catch (Exception e) { - // try on legacy + // try on legacy (migration may not been applied) return getLegacyVersion(); } } private Version getLegacyVersion() { - return MigrationServiceSqlHelper.LEGACY.getVersion(jdbcHelper()).map(TMSVersion::toVersion).orElse(Version.VZERO); + return MigrationServiceSqlHelper.LEGACY.getVersion(jdbcHelper()).map(TMSVersion::getVersion).orElse(Version.VZERO); } } ===================================== toolkit/persistence/src/main/java/org/nuiton/topia/persistence/jdbc/JdbcSecurityHelperH2.java ===================================== @@ -31,7 +31,6 @@ import org.nuiton.topia.persistence.security.SecurityScriptHelper; import java.sql.ResultSet; import java.sql.SQLException; -import java.util.ArrayList; import java.util.Collection; import java.util.Collections; import java.util.HashSet; @@ -162,24 +161,21 @@ public class JdbcSecurityHelperH2 implements JdbcSecurityHelper { Set<String> safeExtraTables = extraTables.stream().map(String::toLowerCase).collect(Collectors.toSet()); try { - return jdbcHelper.runSelect("SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", resultSet -> { - List<Pair<String, String>> result = new ArrayList<>(); - while (resultSet.next()) { - String schemaName = resultSet.getString(1); - String tableName = resultSet.getString(2); - if (!safeExtraTables.contains(tableName.toLowerCase())) { - // if (POSTGIS_TABLES.contains(tableName)) { - // continue; - // } - if (schemaName == null || !safeSchemas.contains(schemaName.toLowerCase())) { - continue; - } + List<Pair<String, String>> pairs = jdbcHelper.runMultipleSelect("SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'TABLE'", resultSet -> { + String schemaName = resultSet.getString(1); + String tableName = resultSet.getString(2); + if (!safeExtraTables.contains(tableName.toLowerCase())) { + // if (POSTGIS_TABLES.contains(tableName)) { + // continue; + // } + if (schemaName == null || !safeSchemas.contains(schemaName.toLowerCase())) { + return null; } - result.add(Pair.of(schemaName, tableName)); } - Collections.sort(result); - return result; + return Pair.of(schemaName, tableName); }); + Collections.sort(pairs); + return pairs; } catch (Exception e) { throw new TopiaException(e); } @@ -188,14 +184,8 @@ public class JdbcSecurityHelperH2 implements JdbcSecurityHelper { @Override public Set<String> getRoles() { try { - return jdbcHelper.runSelect("SELECT name FROM INFORMATION_SCHEMA.USERS;", resultSet -> { - Set<String> users = new LinkedHashSet<>(); - while (resultSet.next()) { - String name = resultSet.getString(1); - users.add(name); - } - return users; - }); + List<String> roles = jdbcHelper.runMultipleSelect("SELECT name FROM INFORMATION_SCHEMA.USERS;", resultSet -> resultSet.getString(1)); + return Set.copyOf(roles); } catch (SQLException e) { throw new TopiaException(e); } @@ -204,7 +194,7 @@ public class JdbcSecurityHelperH2 implements JdbcSecurityHelper { @Override public boolean isOwner() { try { - return jdbcHelper.runSelect("SELECT t.* FROM INFORMATION_SCHEMA.SCHEMATA t WHERE SCHEMA_NAME = 'PUBLIC' AND SCHEMA_OWNER = CURRENT_USER", ResultSet::next); + return jdbcHelper.runSelect("SELECT t.* FROM INFORMATION_SCHEMA.SCHEMATA t WHERE SCHEMA_NAME = 'PUBLIC' AND SCHEMA_OWNER = CURRENT_USER", r-> true); } catch (SQLException e) { throw new TopiaException(e); } @@ -213,7 +203,7 @@ public class JdbcSecurityHelperH2 implements JdbcSecurityHelper { @Override public boolean isSuperUser() { try { - return jdbcHelper.runSelect("SELECT admin FROM INFORMATION_SCHEMA.USERS WHERE name = CURRENT_USER", resultSet -> resultSet.next() && resultSet.getBoolean(1)); + return jdbcHelper.runSelect("SELECT admin FROM INFORMATION_SCHEMA.USERS WHERE name = CURRENT_USER", resultSet -> resultSet.getBoolean(1)); } catch (SQLException e) { throw new TopiaException(e); } ===================================== toolkit/persistence/src/main/java/org/nuiton/topia/persistence/jdbc/JdbcSecurityHelperPostgres.java ===================================== @@ -253,7 +253,7 @@ public class JdbcSecurityHelperPostgres implements JdbcSecurityHelper { @Override public boolean isOwner() { try { - return jdbcHelper.runSelect("SELECT pg_catalog.pg_get_userbyid(d.datdba) FROM pg_catalog.pg_database d WHERE d.datname = (SELECT current_database())", resultSet -> resultSet.next() && Objects.equals(jdbcHelper.getJdbcConnectionUser(), resultSet.getString(1))); + return jdbcHelper.runSelect("SELECT pg_catalog.pg_get_userbyid(d.datdba) FROM pg_catalog.pg_database d WHERE d.datname = (SELECT current_database())", resultSet -> Objects.equals(jdbcHelper.getJdbcConnectionUser(), resultSet.getString(1))); } catch (SQLException e) { throw new TopiaException(e); } @@ -262,7 +262,7 @@ public class JdbcSecurityHelperPostgres implements JdbcSecurityHelper { @Override public boolean isSuperUser() { try { - return jdbcHelper.runSelect("SELECT usesuper FROM pg_user WHERE usename = CURRENT_USER", resultSet -> resultSet.next() && resultSet.getBoolean(1)); + return jdbcHelper.runSelect("SELECT usesuper FROM pg_user WHERE usename = CURRENT_USER", resultSet -> resultSet.getBoolean(1)); } catch (SQLException e) { throw new TopiaException(e); } @@ -275,14 +275,8 @@ public class JdbcSecurityHelperPostgres implements JdbcSecurityHelper { " FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)" + " WHERE ns.nspname = 'public' AND p.proname ILIKE '%s%%';", functionPattern); try { - return jdbcHelper.runSelect(sql, resultSet -> { - Set<String> result = new LinkedHashSet<>(); - while (resultSet.next()) { - String functionPrototype = resultSet.getString(1); - result.add(functionPrototype); - } - return result; - }); + List<String> result = jdbcHelper.runMultipleSelect(sql, resultSet -> resultSet.getString(1)); + return Set.copyOf(result); } catch (SQLException e) { throw new TopiaException(e); } ===================================== toolkit/persistence/src/main/java/org/nuiton/topia/service/migration/TopiaMigrationServiceContext.java ===================================== @@ -38,6 +38,9 @@ import org.nuiton.topia.service.migration.version.TMSVersion; import java.io.IOException; import java.nio.file.Files; import java.nio.file.Path; +import java.util.Date; +import java.util.List; +import java.util.Objects; import java.util.Optional; /** @@ -56,11 +59,10 @@ public class TopiaMigrationServiceContext { * Service configuration. */ protected final TopiaMigrationServiceConfiguration configuration; - protected final MigrationServiceSqlHelper sqlHelper; /** - * Jdbc helper used to perform some sql code. + * Migration sql helper (could be legacy or default). */ - private final JdbcHelper jdbcHelper; + protected final MigrationServiceSqlHelper sqlHelper; /** * Available migration resources found in class-path. */ @@ -74,17 +76,25 @@ public class TopiaMigrationServiceContext { */ protected final boolean dbNotVersioned; /** - * Is legacy TMSVersion table exists? + * Is legacy migration table exists? (only used with default mode) */ protected final boolean legacyVersionTableExist; /** - * Is TMSVersion schema exists? + * Is default migration schema exists? + */ + protected final boolean versionSchemaExist; + /** + * Is default migration table exists? */ - protected boolean versionSchemaExist; + protected final boolean versionTableExist; /** - * Is TMSVersion table exists? + * Can we apply migration versions? */ - protected boolean versionTableExist; + protected final boolean canApplyMigrationVersions; + /** + * Migration versions to apply. + */ + protected final List<Version> versionsToApply; /** * Current database version. */ @@ -114,10 +124,11 @@ public class TopiaMigrationServiceContext { try { versionTableExist = sqlHelper.isTableExist(jdbcHelper); if (versionTableExist) { + log.info("Detected standard migration table {}.", sqlHelper.gav()); versionSchemaExist = true; Optional<TMSVersion> tmsVersion = sqlHelper.getVersion(jdbcHelper); if (tmsVersion.isPresent()) { - v = tmsVersion.get().toVersion(); + v = tmsVersion.get().getVersion(); } if (v == null) { log.warn(String.format("Version not found on table %s", sqlHelper.gav())); @@ -126,9 +137,10 @@ public class TopiaMigrationServiceContext { versionSchemaExist = sqlHelper.isSchemaExist(jdbcHelper); legacyVersionTableExist = legacySqlHelper.isTableExist(jdbcHelper); if (legacyVersionTableExist) { + log.info("Detected legacy migration table {}.", sqlHelper.gav()); Optional<TMSVersion> tmsVersion = legacySqlHelper.getVersion(jdbcHelper); if (tmsVersion.isPresent()) { - v = tmsVersion.get().toVersion(); + v = tmsVersion.get().getVersion(); } if (v == null) { log.warn(String.format("Version not found on legacy table %s", legacySqlHelper.gav())); @@ -137,6 +149,7 @@ public class TopiaMigrationServiceContext { } } finally { if (v == null) { + //FIXME Is this can really happen? // la base dans ce cas n'est pas versionee. // On dit que la version de la base est 0 // et les schema de cette version 0 doivent @@ -151,7 +164,6 @@ public class TopiaMigrationServiceContext { } return new TopiaMigrationServiceContext(configuration, sqlHelper, - jdbcHelper, legacyVersionTableExist, versionSchemaExist, versionTableExist, @@ -175,9 +187,10 @@ public class TopiaMigrationServiceContext { try { versionTableExist = sqlHelper.isTableExist(jdbcHelper); if (versionTableExist) { + log.info("Detected legacy migration table {}.", sqlHelper.gav()); Optional<TMSVersion> tmsVersion = sqlHelper.getVersion(jdbcHelper); if (tmsVersion.isPresent()) { - v = tmsVersion.get().toVersion(); + v = tmsVersion.get().getVersion(); } if (v == null) { log.warn(String.format("Version not found on table %s", sqlHelper.gav())); @@ -199,7 +212,6 @@ public class TopiaMigrationServiceContext { } return new TopiaMigrationServiceContext(configuration, sqlHelper, - jdbcHelper, false, true, versionTableExist, @@ -211,15 +223,14 @@ public class TopiaMigrationServiceContext { protected TopiaMigrationServiceContext(TopiaMigrationServiceConfiguration configuration, MigrationServiceSqlHelper sqlHelper, - JdbcHelper jdbcHelper, - boolean legacyVersionTableExist, boolean versionSchemaExist, + boolean legacyVersionTableExist, + boolean versionSchemaExist, boolean versionTableExist, boolean dbNotVersioned, Version dbVersion, MigrationVersionResourceProvider resources) { this.configuration = configuration; this.sqlHelper = sqlHelper; - this.jdbcHelper = jdbcHelper; this.versionSchemaExist = versionSchemaExist; this.versionTableExist = versionTableExist; this.dbNotVersioned = dbNotVersioned; @@ -231,26 +242,24 @@ public class TopiaMigrationServiceContext { } catch (IOException e) { throw new IllegalStateException("Can't create scripts path", e); } - } - public Path getScriptPath() { - return scriptPath; - } + List<Version> allVersions = getResources().getAvailableVersions(); + log.info(String.format("Detected available versions: %1$s", allVersions)); - public boolean isVersionSchemaExist() { - return versionSchemaExist; - } + versionsToApply = getResources().getVersionsAfter(dbVersion); + log.info(String.format("Detected versions to apply: %1$s", versionsToApply)); - public boolean isVersionTableExist() { - return versionTableExist; - } + Optional<TopiaMigrationServiceAskUserToMigrate> askUserToMigrate = Optional.ofNullable(configuration.getCallback()); - public boolean isLegacyVersionTableExist() { - return legacyVersionTableExist; + // ask to perform the migration + canApplyMigrationVersions = askUserToMigrate.map(c -> c.canIMigrate(getDbVersion(), versionsToApply)).orElse(true); + if (!canApplyMigrationVersions) { + log.warn("Detected that we can not migrate versions (maybe version of database is too old?)."); + } } - public boolean isDbNotVersioned() { - return dbNotVersioned; + public Path getScriptPath() { + return scriptPath; } public Version getModelVersion() { @@ -265,35 +274,65 @@ public class TopiaMigrationServiceContext { return resources; } - public void createSchemaIfNotExist() { + public boolean adaptVersionTable(TopiaSqlSupport sqlSupport) { + + Version modelVersion = getModelVersion(); + boolean dbVersionEqualsModelVersion = Objects.equals(dbVersion, modelVersion); + if (!versionSchemaExist) { - sqlHelper.createSchema(jdbcHelper); - versionSchemaExist = true; + log.info("Create default migration schema."); + sqlHelper.createSchema(sqlSupport); } - } - - public void createTableIfNotExist() { if (!versionTableExist) { - sqlHelper.createTable(jdbcHelper); - versionTableExist = true; + log.info("Create default migration table."); + sqlHelper.createTable(sqlSupport); + } else { + if (dbVersionEqualsModelVersion) { + log.info("Database is up to date, no migration needed."); + return false; + } + } + if (legacyVersionTableExist) { + log.info("Drop legacy migration table."); + MigrationServiceSqlHelper.LEGACY.dropTable(sqlSupport); + if (dbVersionEqualsModelVersion) { + log.info("Database is up to date (version: {}), but was coming from legacy migration table, fill new migration table.", modelVersion); + saveVersion(sqlSupport, modelVersion, null); + return false; + } + log.info("Fill version table {} from legacy table.", dbVersion); + saveVersion(sqlSupport, dbVersion, null); + } else if (dbNotVersioned) { + log.info("Database is empty, no migration needed, set "); + saveVersion(sqlSupport, modelVersion, new Date()); + return false; } + // In all other cases, we can try to perform migration + return true; } - public void saveModelVersion() { - saveVersion(null, getModelVersion()); - } + public List<Version> getVersionsToApply() { - public Optional<TopiaMigrationServiceAskUserToMigrate> getAskUserToMigrate() { - return Optional.ofNullable(configuration.getCallback()); - } + if (versionsToApply.isEmpty()) { + log.info("No version to apply, no migration needed."); + // No migration, so nothing to save +// context.saveModelVersion(); + return null; + } - protected void saveVersion(TopiaSqlSupport sqlSupport, Version version) { - log.info(String.format("[ Version %s ] Saving new database version.", version)); - if (sqlSupport != null) { - sqlHelper.save(sqlSupport, version.getVersion()); - } else { - sqlHelper.save(jdbcHelper, version.getVersion()); + // ask to perform the migration + + if (!canApplyMigrationVersions) { + // user cancel migration + log.warn("Migration was cancelled by migration callback (maybe version of database is too old?)."); + return null; } + return versionsToApply; + } + + protected void saveVersion(TopiaSqlSupport sqlSupport, Version version, Date date) { + log.info(String.format("[ Version %s ] Saving new database version (date: %s).", version, date)); + sqlHelper.save(sqlSupport, version.getVersion(), date); dbVersion = version; } @@ -309,8 +348,4 @@ public class TopiaMigrationServiceContext { return new MigrationVersionResourceExecutor(migrationVersionResource, sqlSupport, configuration.getClassifier(), getScriptPath(), migrationVersionResource.getScriptVariables()); } - public void dropLegacyTable() { - MigrationServiceSqlHelper.LEGACY.dropTable(jdbcHelper); - } - } ===================================== toolkit/persistence/src/main/java/org/nuiton/topia/service/migration/TopiaMigrationServiceImpl.java ===================================== @@ -34,10 +34,10 @@ import org.nuiton.topia.service.migration.resources.MigrationVersionResource; import org.nuiton.topia.service.migration.resources.MigrationVersionResourceExecutor; import java.io.IOException; +import java.util.Date; import java.util.List; import java.util.Map; import java.util.Objects; -import java.util.Optional; /** * Topia migration service default implementation. @@ -68,81 +68,38 @@ public class TopiaMigrationServiceImpl implements TopiaMigrationService { @Override public void runSchemaMigration() throws TopiaMigrationServiceException { + log.info("Migration - Prepare internal states."); TopiaMigrationServiceContext context = TopiaMigrationServiceContext.create(configuration); - Version modelVersion = context.getModelVersion(); - Version dbVersion = context.getDbVersion(); - boolean dbNotVersioned = context.isDbNotVersioned(); - boolean legacyVersionTableExist = context.isLegacyVersionTableExist(); - boolean versionSchemaExist = context.isVersionSchemaExist(); - boolean versionTableExist = context.isVersionTableExist(); - - log.info(String.format("Starting Topia Migration Service - Model version : %s, Database version : %s", modelVersion, dbVersion)); - - if (!versionSchemaExist) { - log.info("Create migration schema."); - context.createSchemaIfNotExist(); - } - if (!versionTableExist) { - log.info("Create migration table."); - context.createTableIfNotExist(); - } - if (legacyVersionTableExist) { - log.info("Drop legacy migration table."); - context.dropLegacyTable(); - if (dbVersion.equals(modelVersion)) { - log.info("Database is up to date, but was coming from legacy migration table, fill new migration table."); - context.saveModelVersion(); + log.info("Migration - Prepare internal states done."); + + try (TopiaPersistenceContext persistenceContext = context.newPersistenceContext()) { + log.info("Migration - Adapting migration table."); + boolean canContinue = adaptVersionTable(persistenceContext, context); + log.info("Migration - Adapting migration table done."); + if (!canContinue) { + log.info(String.format("Migration - End - db version: %s (nothing more to do).", context.getDbVersion())); + return; + } + log.info("Migration - Getting migration versions to apply."); + List<Version> versionsToApply = context.getVersionsToApply(); + log.info("Migration - Getting migration versions to apply done."); + if (versionsToApply == null) { + log.info(String.format("Migration - End - db version: %s (nothing more to do).", context.getDbVersion())); return; } - } - - if (versionTableExist && dbVersion.equals(modelVersion)) { - log.info("Database is up to date, no migration needed."); - return; - } - - if (versionTableExist && dbNotVersioned) { - log.info("Database is empty, no migration needed."); - context.saveModelVersion(); - return; - } - - List<Version> allVersions = context.getResources().getAvailableVersions(); - log.info(String.format("Available versions: %1$s", allVersions)); - - List<Version> versionsToApply = context.getResources().getVersionsAfter(dbVersion); - - if (versionsToApply.isEmpty()) { - log.info("No version to apply, no migration needed."); - context.saveModelVersion(); - return; - } - - log.info(String.format("Versions to apply: %1$s", versionsToApply)); - - Optional<TopiaMigrationServiceAskUserToMigrate> askUserToMigrate = context.getAskUserToMigrate(); - - // ask to perform the migration - boolean performMigration = askUserToMigrate.map(c -> c.canIMigrate(dbVersion, versionsToApply)).orElse(true); - - log.debug("Handler choose : " + performMigration); - if (!performMigration) { - // user cancel migration - return; - } + log.info(String.format("Migration - Will apply versions: %1$s.", versionsToApply)); - long statementCount = 0; - try (TopiaPersistenceContext persistenceContext = context.newPersistenceContext()) { + long statementCount = 0; for (Version version : versionsToApply) { long t0 = TimeLog.getTime(); long versionCount = migrateVersion(persistenceContext, version, context); statementCount += versionCount; TIME_LOG.log(t0, "migrationVersion", version.toString() + " - " + versionCount + " sql statements"); } + log.info(String.format("Migration - End - db version: %s - consume %d sql statement(s)", context.getDbVersion(), statementCount)); } - log.info(String.format("Ends migration - db version: %s - consume %d sql statement(s)", context.getDbVersion(), statementCount)); } @Override @@ -150,6 +107,17 @@ public class TopiaMigrationServiceImpl implements TopiaMigrationService { configuration = null; } + protected boolean adaptVersionTable(TopiaPersistenceContext persistenceContext, TopiaMigrationServiceContext context) { + try { + boolean canContinue = context.adaptVersionTable(persistenceContext.getSqlSupport()); + persistenceContext.commit(); + return canContinue; + } catch (Exception e) { + persistenceContext.rollback(); + throw new TopiaMigrationServiceException("Exception during adapting migration table", e); + } + } + protected long migrateVersion(TopiaPersistenceContext persistenceContext, Version version, TopiaMigrationServiceContext context) { MigrationVersionResource resource = context.getResource(version); @@ -174,14 +142,15 @@ public class TopiaMigrationServiceImpl implements TopiaMigrationService { } } // here we use the sqlSupport to save version, to stay on the very same transaction - context.saveVersion(sqlSupport, version); + context.saveVersion(sqlSupport, version, new Date()); + // do commit, this migration version is now completed + persistenceContext.commit(); } catch (Exception e) { // Exception, rollback transaction persistenceContext.rollback(); throw new TopiaMigrationServiceException("Exception during schema migration on version: " + version, e); } - persistenceContext.commit(); return outStatementCount; } ===================================== toolkit/persistence/src/main/java/org/nuiton/topia/service/migration/version/MigrationServiceSqlHelper.java ===================================== @@ -27,6 +27,10 @@ import org.nuiton.topia.persistence.jdbc.JdbcHelper; import org.nuiton.topia.persistence.support.TopiaSqlSupport; import java.sql.SQLException; +import java.sql.Timestamp; +import java.util.Comparator; +import java.util.Date; +import java.util.List; import java.util.Optional; /** @@ -38,12 +42,8 @@ import java.util.Optional; * @since 9.0.7 */ public interface MigrationServiceSqlHelper { - String GET_VERSION_TABLE_STATEMENT = "SELECT version FROM %s;"; - String CREATE_VERSION_SCHEMA_STATEMENT = "CREATE SCHEMA %s;"; - String CREATE_VERSION_TABLE_STATEMENT = "CREATE TABLE %s(version VARCHAR(255) NOT NULL, PRIMARY KEY (version));"; - String DELETE_VERSION_TABLE_STATEMENT = "DELETE FROM %s;"; + String CREATE_VERSION_SCHEMA_STATEMENT = "CREATE SCHEMA IF NOT EXISTS %s;"; String DROP_VERSION_TABLE_STATEMENT = "DROP TABLE %s;"; - String FILL_VERSION_TABLE_STATEMENT = "INSERT INTO %s(version) VALUES('%s');"; /** * Legacy layout. @@ -51,6 +51,10 @@ public interface MigrationServiceSqlHelper { MigrationServiceSqlHelper LEGACY = new MigrationServiceSqlHelper() { public static final String SCHEMA_NAME = "public"; public static final String TABLE_NAME = "tms_version"; + public static final String CREATE_VERSION_TABLE_STATEMENT = "CREATE TABLE %s(version VARCHAR(255) NOT NULL, PRIMARY KEY(version));"; + public static final String FILL_VERSION_TABLE_STATEMENT = "INSERT INTO %s(version) VALUES('%s');"; + public static final String GET_VERSION_TABLE_STATEMENT = "SELECT version FROM %s;"; + public static final String DELETE_VERSION_TABLE_STATEMENT = "DELETE FROM %s;"; @Override public String schemaName() { @@ -61,15 +65,46 @@ public interface MigrationServiceSqlHelper { public String tableName() { return TABLE_NAME; } + + @Override + public String createTableSql() { + return String.format(CREATE_VERSION_TABLE_STATEMENT, gav()); + } + + @Override + public String getVersionSql() { + return String.format(GET_VERSION_TABLE_STATEMENT, gav()); + } + + @Override + public Optional<TMSVersion> getVersion(JdbcHelper jdbcHelper) { + try { + try { + TMSVersion result = jdbcHelper.runSelect(getVersionSql(), r -> new TMSVersion(r.getString(1), null)); + return Optional.ofNullable(result); + } catch (Exception e) { + throw new TopiaException("Could not obtain version", e); + } + } catch (Exception e) { + throw new TopiaException("Could not obtain version", e); + } + } + + @Override + public String saveTableSql(String version, Date date) { + return String.format(DELETE_VERSION_TABLE_STATEMENT, gav()) + String.format(FILL_VERSION_TABLE_STATEMENT, gav(), version); + } }; /** * Default layout. */ MigrationServiceSqlHelper DEFAULT = new MigrationServiceSqlHelper() { - //FIXME Find a better name public static final String SCHEMA_NAME = "common"; //FIXME Find a better name - public static final String TABLE_NAME = "tms_version"; + public static final String TABLE_NAME = "database_version"; + public static final String CREATE_VERSION_TABLE_STATEMENT = "CREATE TABLE %s(version VARCHAR(255) NOT NULL, date TIMESTAMP, PRIMARY KEY(version));"; + public static final String FILL_VERSION_TABLE_STATEMENT = "INSERT INTO %s(version, date) VALUES('%s', %s);"; + public static final String GET_VERSION_TABLE_STATEMENT = "SELECT version, date FROM %s;"; @Override public String schemaName() { @@ -80,6 +115,35 @@ public interface MigrationServiceSqlHelper { public String tableName() { return TABLE_NAME; } + + @Override + public String createTableSql() { + return String.format(CREATE_VERSION_TABLE_STATEMENT, gav()); + } + + @Override + public String getVersionSql() { + return String.format(GET_VERSION_TABLE_STATEMENT, gav()); + } + + @Override + public Optional<TMSVersion> getVersion(JdbcHelper jdbcHelper) { + try { + List<TMSVersion> result = jdbcHelper.runMultipleSelect(getVersionSql(), r -> new TMSVersion(r.getString(1), r.getTimestamp(2))); + if (result.isEmpty()) { + return Optional.empty(); + } + result.sort(Comparator.comparing(TMSVersion::getVersion).reversed()); + return Optional.of(result.get(0)); + } catch (Exception e) { + throw new TopiaException("Could not obtain version", e); + } + } + + @Override + public String saveTableSql(String version, Date date) { + return String.format(FILL_VERSION_TABLE_STATEMENT, gav(), version, date == null ? "NULL" : ("'" + new Timestamp(date.getTime()) + "'::timestamp")); + } }; /** @@ -89,7 +153,7 @@ public interface MigrationServiceSqlHelper { * <p> * We will need even after that to keep the legacy definition, to be able to migration older database using it. */ - MigrationServiceSqlHelper CURRENT = LEGACY; + MigrationServiceSqlHelper CURRENT = DEFAULT; static boolean isLegacy() { return CURRENT.equals(LEGACY); @@ -112,59 +176,22 @@ public interface MigrationServiceSqlHelper { return schemaName() + "." + tableName(); } - default Optional<TMSVersion> getVersion(JdbcHelper jdbcHelper) { - try { - String version = jdbcHelper.runSelectOnString(getVersionSql()); - return Optional.ofNullable(version == null ? null : new TMSVersion(version)); - } catch (Exception e) { - throw new TopiaException("Could not obtain version", e); - } - } - - default String getVersionSql() { - return String.format(GET_VERSION_TABLE_STATEMENT, gav()); - } + Optional<TMSVersion> getVersion(JdbcHelper jdbcHelper); - default String createSchemaSql() { - return String.format(CREATE_VERSION_SCHEMA_STATEMENT, gav()); - } + String getVersionSql(); - default String createTableSql() { - return String.format(CREATE_VERSION_TABLE_STATEMENT, gav()); - } + String createTableSql(); - default String deleteTableSql() { - return String.format(DELETE_VERSION_TABLE_STATEMENT, gav()); - } + String saveTableSql(String version, Date date); - default String fillVersionSql(String version) { - return String.format(FILL_VERSION_TABLE_STATEMENT, gav(), version); + default String createSchemaSql() { + return String.format(CREATE_VERSION_SCHEMA_STATEMENT, schemaName()); } default String dropTableSql() { return String.format(DROP_VERSION_TABLE_STATEMENT, gav()); } - default String saveTableSql(String version) { - return deleteTableSql() + fillVersionSql(version); - } - - default void save(JdbcHelper jdbcHelper, String version) { - try { - jdbcHelper.runUpdate(saveTableSql(version)); - } catch (Exception e) { - throw new TopiaException(String.format("Could not save version %s", version), e); - } - } - - default void save(TopiaSqlSupport sqlSupport, String version) { - try { - sqlSupport.executeSql(saveTableSql(version)); - } catch (Exception e) { - throw new TopiaException(String.format("Could not save version %s", version), e); - } - } - default boolean isTableExist(JdbcHelper jdbcHelper) { try { return jdbcHelper.isTableExist(schemaName(), tableName()); @@ -181,27 +208,35 @@ public interface MigrationServiceSqlHelper { } } - default void createSchema(JdbcHelper jdbcHelper) { + default void createSchema(TopiaSqlSupport sqlSupport) { try { - jdbcHelper.runUpdate(createSchemaSql()); + sqlSupport.executeSql(createSchemaSql()); } catch (Exception e) { - throw new TopiaException(String.format("Could not create version table %s", gav()), e); + throw new TopiaException(String.format("Could not create version schema %s", gav()), e); } } - default void createTable(JdbcHelper jdbcHelper) { + default void createTable(TopiaSqlSupport sqlSupport) { try { - jdbcHelper.runUpdate(createTableSql()); + sqlSupport.executeSql(createTableSql()); } catch (Exception e) { throw new TopiaException(String.format("Could not create version table %s", gav()), e); } } - default void dropTable(JdbcHelper jdbcHelper) { + default void dropTable(TopiaSqlSupport sqlSupport) { try { - jdbcHelper.runUpdate(dropTableSql()); + sqlSupport.executeSql(dropTableSql()); } catch (Exception e) { throw new TopiaException(String.format("Could not drop version table %s", gav()), e); } } + + default void save(TopiaSqlSupport sqlSupport, String version, Date date) { + try { + sqlSupport.executeSql(saveTableSql(version, date)); + } catch (Exception e) { + throw new TopiaException(String.format("Could not save version %s", version), e); + } + } } ===================================== toolkit/persistence/src/main/java/org/nuiton/topia/service/migration/version/TMSVersion.java ===================================== @@ -25,6 +25,7 @@ package org.nuiton.topia.service.migration.version; import io.ultreia.java4all.util.Version; import java.io.Serializable; +import java.util.Date; /** * @author Tony Chemit - dev@tchemit.fr @@ -33,27 +34,23 @@ public class TMSVersion implements Serializable { private static final long serialVersionUID = 1L; - private String version; + private final Version version; + private final Date date; - public TMSVersion() { - } - - public TMSVersion(String version) { + public TMSVersion(String version, Date date) { if (version == null || version.isEmpty()) { throw new IllegalArgumentException("version parameter can not be null nor empty."); } - this.version = version; + this.version = Version.valueOf(version); + this.date = date; } - public String getVersion() { + public Version getVersion() { return version; } - public void setVersion(String version) { - this.version = version; + public Date getDate() { + return date; } - public Version toVersion() { - return version == null || version.isEmpty() ? null : Version.valueOf(version); - } } ===================================== toolkit/persistence/src/main/java/org/nuiton/topia/service/sql/internal/consumer/AddVersionTableConsumer.java ===================================== @@ -29,6 +29,8 @@ import org.nuiton.topia.service.sql.internal.SqlRequestConsumer; import org.nuiton.topia.service.sql.internal.SqlRequestSetConsumerContext; import org.nuiton.topia.service.sql.internal.request.AddVersionTableRequest; +import java.util.Date; + /** * Created on 21/02/2021. * @@ -41,8 +43,9 @@ public class AddVersionTableConsumer implements SqlRequestConsumer<AddVersionTab public void consume(AddVersionTableRequest request, SqlRequestSetConsumerContext context) { try { SqlScriptWriter writer = context.getWriter(); + writer.writeSql(MigrationServiceSqlHelper.CURRENT.createSchemaSql()); writer.writeSql(MigrationServiceSqlHelper.CURRENT.createTableSql()); - writer.writeSql(MigrationServiceSqlHelper.CURRENT.fillVersionSql(request.getDbVersion().getVersion())); + writer.writeSql(MigrationServiceSqlHelper.CURRENT.saveTableSql(request.getDbVersion().getVersion(), new Date())); } catch (Exception e) { throw new TopiaException(String.format("Could not add version table for reason: %s", e.getMessage()), e); } View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/9f27b93b8b44a72a1405273cce... -- View it on GitLab: https://gitlab.com/ultreiaio/ird-observe/-/commit/9f27b93b8b44a72a1405273cce... You're receiving this email because of your account on gitlab.com.