From b08f64f4c8caddf15b274ddde51de17ea388f4d3 Mon Sep 17 00:00:00 2001 From: =?utf8?q?David=20=E2=80=98Bombe=E2=80=99=20Roden?= Date: Sun, 6 Apr 2025 11:41:23 +0200 Subject: [PATCH] =?utf8?q?=F0=9F=97=83=EF=B8=8F=20Add=20Flyway=20for=20dat?= =?utf8?q?abase=20migrations?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- build.gradle | 1 + .../migration/V202504051058_MigratePreferences.kt | 69 +++++++ .../net/pterodactylus/sone/main/DatabaseModule.kt | 47 +++++ .../V202504051058_MigratePreferencesTest.kt | 198 +++++++++++++++++++++ .../pterodactylus/sone/main/DatabaseModuleTest.kt | 82 +++++++++ 5 files changed, 397 insertions(+) create mode 100644 src/main/kotlin/net/pterodactylus/sone/database/migration/V202504051058_MigratePreferences.kt create mode 100644 src/main/kotlin/net/pterodactylus/sone/main/DatabaseModule.kt create mode 100644 src/test/kotlin/net/pterodactylus/sone/database/migration/V202504051058_MigratePreferencesTest.kt create mode 100644 src/test/kotlin/net/pterodactylus/sone/main/DatabaseModuleTest.kt diff --git a/build.gradle b/build.gradle index 4e452a9..d0b73ff 100644 --- a/build.gradle +++ b/build.gradle @@ -57,6 +57,7 @@ dependencies { implementation group: 'org.jsoup', name: 'jsoup', version: '1.18.1' implementation group: 'io.dropwizard.metrics', name: 'metrics-core', version: '4.2.27' implementation group: 'jakarta.activation', name: 'jakarta.activation-api', version: '2.1.3' + implementation group: 'org.flywaydb', name: 'flyway-core', version: '9.22.3' implementation group: 'com.h2database', name: 'h2', version: '1.4.200' implementation group: 'org.jooq', name: 'jooq', version: '3.14.16' diff --git a/src/main/kotlin/net/pterodactylus/sone/database/migration/V202504051058_MigratePreferences.kt b/src/main/kotlin/net/pterodactylus/sone/database/migration/V202504051058_MigratePreferences.kt new file mode 100644 index 0000000..152888f --- /dev/null +++ b/src/main/kotlin/net/pterodactylus/sone/database/migration/V202504051058_MigratePreferences.kt @@ -0,0 +1,69 @@ +package net.pterodactylus.sone.database.migration + +import jakarta.inject.Inject +import java.sql.Connection +import net.pterodactylus.sone.database.h2.jooq.tables.references.OPTIONS +import net.pterodactylus.util.config.Configuration +import net.pterodactylus.util.config.ConfigurationException +import org.flywaydb.core.api.MigrationVersion +import org.flywaydb.core.api.migration.BaseJavaMigration +import org.flywaydb.core.api.migration.Context +import org.flywaydb.core.api.migration.JavaMigration +import org.jooq.DSLContext +import org.jooq.Migration +import org.jooq.SQLDialect +import org.jooq.impl.DSL + +/** + * This migration will read + */ +@Suppress("unused", "ClassName") +class V202504051058_MigratePreferences @Inject constructor(private val configuration: Configuration, private val sqlDialect: SQLDialect) : JavaMigration { + + override fun getVersion(): MigrationVersion = MigrationVersion.fromVersion("202504051058") + + override fun getDescription() = "MigratePreferences" + + override fun getChecksum() = 0 + + override fun canExecuteInTransaction() = true + + override fun migrate(context: Context) { + DSL.using(context.connection, sqlDialect).transaction { configuration -> + copyIntegerValueFromPreferencesToDatabase(configuration.dsl(), "Option/InsertionDelay", "InsertionDelay") + copyIntegerValueFromPreferencesToDatabase(configuration.dsl(), "Option/PostsPerPage", "PostsPerPage") + copyIntegerValueFromPreferencesToDatabase(configuration.dsl(), "Option/ImagesPerPage", "ImagesPerPage") + copyIntegerValueFromPreferencesToDatabase(configuration.dsl(), "Option/CharactersPerPost", "CharactersPerPost") + copyIntegerValueFromPreferencesToDatabase(configuration.dsl(), "Option/PostCutOffLength", "PostCutOffLength") + copyBooleanValueFromPreferencesToDatabase(configuration.dsl(), "Option/RequireFullAccess", "RequireFullAccess") + copyBooleanValueFromPreferencesToDatabase(configuration.dsl(), "Option/ActivateFcpInterface", "FcpInterfaceActive") + copyIntegerValueFromPreferencesToDatabase(configuration.dsl(), "Option/FcpFullAccessRequired", "FcpFullAccessRequired") + copyBooleanValueFromPreferencesToDatabase(configuration.dsl(), "Option/StrictFiltering", "StrictFiltering") + } + } + + private fun copyIntegerValueFromPreferencesToDatabase(dslContext: DSLContext, preferencesName: String, databaseOptionName: String) { + val preferenceValue = try { + configuration.getIntValue(preferencesName).value + } catch (e: ConfigurationException) { + null + } + dslContext.insertInto(OPTIONS).columns(OPTIONS.KEY, OPTIONS.VALUE).values(databaseOptionName, preferenceValue) + .onDuplicateKeyUpdate().set(OPTIONS.VALUE, preferenceValue) + .execute() + } + + private fun copyBooleanValueFromPreferencesToDatabase(dslContext: DSLContext, preferencesName: String, databaseOptionName: String) { + val preferenceValue = try { + configuration.getBooleanValue(preferencesName).value + } catch (e: ConfigurationException) { + null + } + dslContext.insertInto(OPTIONS).columns(OPTIONS.KEY, OPTIONS.VALUE).values(databaseOptionName, preferenceValue?.asInt) + .onDuplicateKeyUpdate().set(OPTIONS.VALUE, preferenceValue?.asInt) + .execute() + } + +} + +private val Boolean.asInt: Int get() = if (this) 1 else 0 diff --git a/src/main/kotlin/net/pterodactylus/sone/main/DatabaseModule.kt b/src/main/kotlin/net/pterodactylus/sone/main/DatabaseModule.kt new file mode 100644 index 0000000..b8f2b01 --- /dev/null +++ b/src/main/kotlin/net/pterodactylus/sone/main/DatabaseModule.kt @@ -0,0 +1,47 @@ +package net.pterodactylus.sone.main + +import com.google.inject.AbstractModule +import com.google.inject.Provides +import com.google.inject.multibindings.ProvidesIntoSet +import freenet.pluginmanager.PluginRespirator +import jakarta.inject.Named +import jakarta.inject.Singleton +import javax.sql.DataSource +import net.pterodactylus.sone.database.h2.JdbcPreferences +import net.pterodactylus.sone.database.migration.V202504051058_MigratePreferences +import net.pterodactylus.util.config.Configuration +import org.flywaydb.core.Flyway +import org.flywaydb.core.api.migration.JavaMigration +import org.h2.jdbcx.JdbcDataSource +import org.jooq.SQLDialect + +class DatabaseModule : AbstractModule() { + + override fun configure() { + bind(JdbcPreferences::class.java).`in`(Singleton::class.java) + } + + @Provides + fun getSqlDialect() = + SQLDialect.H2 + + @Provides + @Singleton + fun getDataSource(@Named("NodeUserDir") nodeUserDir: String): DataSource = JdbcDataSource().apply { + setUrl("jdbc:h2:file:$nodeUserDir/sone") + } + + @Provides + @Singleton + fun getFlyway(dataSource: DataSource, javaMigrations: Set): Flyway = + Flyway.configure() + .javaMigrations(*javaMigrations.toTypedArray()) + .dataSource(dataSource) + .locations("classpath:net/pterodactylus/sone/database/migration") + .load() + + @ProvidesIntoSet + fun getPreferencesMigration(configuration: Configuration, sqlDialect: SQLDialect): JavaMigration = + V202504051058_MigratePreferences(configuration, sqlDialect) + +} diff --git a/src/test/kotlin/net/pterodactylus/sone/database/migration/V202504051058_MigratePreferencesTest.kt b/src/test/kotlin/net/pterodactylus/sone/database/migration/V202504051058_MigratePreferencesTest.kt new file mode 100644 index 0000000..6df4327 --- /dev/null +++ b/src/test/kotlin/net/pterodactylus/sone/database/migration/V202504051058_MigratePreferencesTest.kt @@ -0,0 +1,198 @@ +package net.pterodactylus.sone.database.migration + +import com.google.inject.Guice.createInjector +import kotlin.test.Test +import net.pterodactylus.sone.database.h2.jooq.tables.references.OPTIONS +import net.pterodactylus.sone.test.getInstance +import net.pterodactylus.sone.test.getMigrationSql +import net.pterodactylus.sone.test.isProvidedBy +import net.pterodactylus.sone.test.jOOQ +import net.pterodactylus.sone.test.mock +import net.pterodactylus.sone.test.randomInMemoryDataSource +import net.pterodactylus.util.config.Configuration +import net.pterodactylus.util.config.MapConfigurationBackend +import org.flywaydb.core.api.MigrationVersion +import org.flywaydb.core.api.migration.Context +import org.hamcrest.Matcher +import org.hamcrest.MatcherAssert.assertThat +import org.hamcrest.Matchers.equalTo +import org.hamcrest.Matchers.nullValue +import org.jooq.SQLDialect +import org.junit.Before + +@Suppress("ClassName") +class V202504051058_MigratePreferencesTest { + + @Test + fun `migration can be created by guice`() { + createInjector( + Configuration::class.isProvidedBy(configuration), + SQLDialect::class.isProvidedBy(SQLDialect.H2) + ).getInstance() + } + + @Test + fun `migration returns correct version number`() { + assertThat(migration.version, equalTo(MigrationVersion.fromVersion("202504051058"))) + } + + @Test + fun `migration transfers null for insertion delay as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/InsertionDelay", null, "InsertionDelay", nullValue()) + } + + @Test + fun `migration transfers insertion delay`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/InsertionDelay", "1", "InsertionDelay", equalTo(1)) + } + + @Test + fun `migration transfers invalid insertion delay as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/InsertionDelay", "invalid", "InsertionDelay", nullValue()) + } + + @Test + fun `migration transfers null for posts per page as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/PostsPerPage", null, "PostsPerPage", nullValue()) + } + + @Test + fun `migration transfers posts per page`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/PostsPerPage", "2", "PostsPerPage", equalTo(2)) + } + + @Test + fun `migration transfers invalid posts per page as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/PostsPerPage", "invalid", "PostsPerPage", nullValue()) + } + + @Test + fun `migration transfers null for images per page as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/ImagesPerPage", null, "ImagesPerPage", nullValue()) + } + + @Test + fun `migration transfers images per page`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/ImagesPerPage", "3", "ImagesPerPage", equalTo(3)) + } + + @Test + fun `migration transfers invalid images per page as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/ImagesPerPage", "invalid", "ImagesPerPage", nullValue()) + } + + @Test + fun `migration transfers null for characters per post as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/CharactersPerPost", null, "CharactersPerPost", nullValue()) + } + + @Test + fun `migration transfers characters per post`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/CharactersPerPost", "4", "CharactersPerPost", equalTo(4)) + } + + @Test + fun `migration transfers invalid characters per post as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/CharactersPerPost", "invalid", "CharactersPerPost", nullValue()) + } + + @Test + fun `migration transfers null for post cut-off length as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/PostCutOffLength", null, "PostCutOffLength", nullValue()) + } + + @Test + fun `migration transfers post cut-off length`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/PostCutOffLength", "5", "PostCutOffLength", equalTo(5)) + } + + @Test + fun `migration transfers invalid post cut-off length as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/PostCutOffLength", "invalid", "PostCutOffLength", nullValue()) + } + + @Test + fun `migration transfers null for require full access as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/RequireFullAccess", null, "RequireFullAccess", nullValue()) + } + + @Test + fun `migration transfers require full access`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/RequireFullAccess", "true", "RequireFullAccess", equalTo(1)) + } + + @Test + fun `migration transfers invalid require full access as false`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/RequireFullAccess", "invalid", "RequireFullAccess", equalTo(0)) + } + + @Test + fun `migration transfers null for fcp interface active as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/ActivateFcpInterface", null, "FcpInterfaceActive", nullValue()) + } + + @Test + fun `migration transfers fcp interface active`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/ActivateFcpInterface", "true", "FcpInterfaceActive", equalTo(1)) + } + + @Test + fun `migration transfers invalid fcp interface active as false`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/ActivateFcpInterface", "invalid", "FcpInterfaceActive", equalTo(0)) + } + + @Test + fun `migration transfers null for fcp full access required as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/FcpFullAccessRequired", null, "FcpFullAccessRequired", nullValue()) + } + + @Test + fun `migration transfers fcp full access required`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/FcpFullAccessRequired", "2", "FcpFullAccessRequired", equalTo(2)) + } + + @Test + fun `migration transfers invalid fcp full access required as false`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/FcpFullAccessRequired", "invalid", "FcpFullAccessRequired", nullValue()) + } + + @Test + fun `migration transfers null for strict filtering as null`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/StrictFiltering", null, "StrictFiltering", nullValue()) + } + + @Test + fun `migration transfers strict filtering`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/StrictFiltering", "true", "StrictFiltering", equalTo(1)) + } + + @Test + fun `migration transfers invalid strict filtering as false`() { + storeValueInConfigurationAndVerifyTransferredDatabaseValue("Option/StrictFiltering", "invalid", "StrictFiltering", equalTo(0)) + } + + private fun storeValueInConfigurationAndVerifyTransferredDatabaseValue(nameInConfiguration: String, stringValue: String?, optionName: String, intMatcher: Matcher?) { + configurationMap[nameInConfiguration] = stringValue + migration.migrate(context) + val migratedValue = jOOQ.select(OPTIONS.VALUE).from(OPTIONS).where(OPTIONS.KEY.eq(optionName)).fetchOne(OPTIONS.VALUE) + assertThat(migratedValue, intMatcher) + } + + @Before + fun runAllRequiredMigrations() = + jOOQ.transaction { transaction -> + getMigrationSql("V202501202137__create_sone_schema", "V202501221927__create_options_table") + .forEach { sql -> transaction.dsl().execute(sql) } + } + + private val dataSource = randomInMemoryDataSource + private val jOOQ = jOOQ(dataSource) + private val context: Context = object : Context { + override fun getConfiguration() = mock() + override fun getConnection() = dataSource.connection + } + private val configurationMap = mutableMapOf() + private val configuration by lazy { Configuration(MapConfigurationBackend(configurationMap)) } + private val migration by lazy { V202504051058_MigratePreferences(configuration, SQLDialect.H2) } + +} diff --git a/src/test/kotlin/net/pterodactylus/sone/main/DatabaseModuleTest.kt b/src/test/kotlin/net/pterodactylus/sone/main/DatabaseModuleTest.kt new file mode 100644 index 0000000..75dc548 --- /dev/null +++ b/src/test/kotlin/net/pterodactylus/sone/main/DatabaseModuleTest.kt @@ -0,0 +1,82 @@ +package net.pterodactylus.sone.main + +import com.google.inject.Guice.createInjector +import javax.sql.DataSource +import kotlin.test.Test +import net.pterodactylus.sone.database.h2.JdbcPreferences +import net.pterodactylus.sone.test.getInstance +import net.pterodactylus.sone.test.isProvidedByMock +import net.pterodactylus.sone.test.verifySingletonInstance +import net.pterodactylus.sone.test.withNameIsProvidedBy +import net.pterodactylus.util.config.Configuration +import org.flywaydb.core.Flyway +import org.h2.jdbcx.JdbcDataSource +import org.hamcrest.MatcherAssert.assertThat +import org.hamcrest.Matchers.containsInAnyOrder +import org.hamcrest.Matchers.containsString +import org.hamcrest.Matchers.equalTo +import org.hamcrest.Matchers.instanceOf +import org.jooq.SQLDialect +import org.junit.Rule +import org.junit.rules.TemporaryFolder + +class DatabaseModuleTest { + + @Test + fun `module provides SQL dialect for jOOQ`() { + assertThat(createInjector().getInstance(), equalTo(SQLDialect.H2)) + } + + @Test + fun `module provides data source`() { + createInjector("/node/user-dir").getInstance().let { dataSource -> + assertThat(dataSource, instanceOf(JdbcDataSource::class.java)) + assertThat((dataSource as JdbcDataSource).getUrl(), containsString("/node/user-dir")) + } + } + + @Test + fun `provided data source is a singleton`() { + createInjector().verifySingletonInstance() + } + + @Test + fun `module provides flyway`() { + createInjector().getInstance() + } + + @Test + fun `flyway has migrations loaded from classpath`() { + val flyway = createInjector().getInstance() + assertThat(flyway.info().all().map { it.version.version }, containsInAnyOrder(*migrations)) + } + + @Test + fun `provided flyway is a singleton`() { + createInjector().verifySingletonInstance() + } + + @Test + fun `module provides jdbc preferences`() { + createInjector().getInstance() + } + + @Test + fun `module provides jdbc preferences as singleton`() { + createInjector().verifySingletonInstance() + } + + private fun createInjector(databasePath: String = tempFolder.newFolder().path) = + createInjector(DatabaseModule(), Configuration::class.isProvidedByMock(), String::class.withNameIsProvidedBy(databasePath, "NodeUserDir")) + + @Rule + @JvmField + val tempFolder = TemporaryFolder() + +} + +private val migrations = arrayOf( + "202501202137", + "202501221927", + "202504051058", +) -- 2.7.4