Make friends CI, unit tests and database

import groovy.json.JsonOutput
import groovy.json.JsonSlurper
import groovy.sql.Sql
import org.apache.tools.ant.filters.ReplaceTokens
import org.liquibase.gradle.LiquibaseTask
plugins {
    id 'java'
    id 'org.liquibase.gradle' version '2.0.1'
}
configurations {
    driver
}
repositories {
    jcenter()
    mavenCentral()
    maven {
        url = "http://www.datanucleus.org/downloads/maven2/"
    }
}
dependencies {
    implementation 'com.google.guava:guava:27.0.1-jre'
    implementation 'org.springframework:spring-core:5.1.7.RELEASE'
    implementation 'org.springframework:spring-context:5.1.7.RELEASE'
    implementation 'org.springframework:spring-jdbc:5.1.7.RELEASE'
    testImplementation 'junit:junit:4.12'
    testImplementation 'org.springframework:spring-test:5.1.7.RELEASE'
    testRuntime 'oracle:ojdbc6:11.+'
    liquibaseRuntime 'org.liquibase:liquibase-core:3.6.1'
    liquibaseRuntime 'oracle:ojdbc6:11.+'
    liquibaseRuntime 'org.yaml:snakeyaml:1.24'
    driver group: "oracle", name: "ojdbc6", version: "11.+"
}
project.ext.localConfig = new Properties()
localConfig.load(file("${rootDir}/local.properties").newReader())
project.ext.isCiBuild = { Boolean.parseBoolean(localConfig['pool.db.enabled'] as String) }
project.ext.doRollbackTest = { Boolean.parseBoolean(localConfig['test.rollback.enabled'] as String) }
task configLiquibase {
    doLast {
        liquibase {
            activities {
                testdb {
                    changeLogFile 'changelog.yaml'
                    url localConfig['app.db.url']
                    driver localConfig['app.db.driverClass']
                    username localConfig['app.db.username']
                    password localConfig['app.db.password']
                    logLevel 'debug'
                    classpath "${project.projectDir}/db"
                    contexts 'main'
                }
                runList = 'testdb'
            }
        }
    }
}
task initDriver {
    doLast {
        ClassLoader loader = GroovyObject.class.classLoader
        configurations.driver.each { File file ->
            loader.addURL(file.toURL())
        }
    }
}
project.ext.createSqlInstance = {
    return Sql.newInstance(
            url: localConfig["pool.db.url"],
            user: localConfig["pool.db.username"],
            password: localConfig["pool.db.password"],
            driver: localConfig["pool.db.driverClass"])
}
task initDbPool {
    dependsOn initDriver
    doLast {
        Integer poolSize = 10
        Sql sql = createSqlInstance() as Sql
        String tableName = localConfig["pool.db.referenceTable"]
        String baseName = localConfig["pool.db.baseName"]
        String basePass = localConfig["pool.db.basePass"]
        String token = "{id}"
        List tableExists = sql.rows("select table_name from all_tables where table_name=?", [tableName])
        assert tableExists.isEmpty()
        sql.execute("""
            CREATE TABLE ${tableName} (
            ID NUMBER(2) NOT NULL PRIMARY KEY,
            METADATA VARCHAR2(200) NOT NULL,
            PROCESSED TIMESTAMP NULL,
            GUID VARCHAR2(36) NULL)
        """, [])
        for (Integer i = 0 ; i < poolSize ; i++) {
            String username = baseName.replace(token, i.toString())
            String password = basePass.replace(token, i.toString())
            sql.execute("""
                CREATE USER ${username}
                IDENTIFIED BY "${password}"
                DEFAULT TABLESPACE USERS
                TEMPORARY TABLESPACE TEMP
                PROFILE DEFAULT
                QUOTA UNLIMITED ON USERS
            """, [])
            sql.execute("grant connect to ${username}", [])
            sql.execute("grant create sequence to ${username}", [])
            sql.execute("grant create session to ${username}", [])
            sql.execute("grant create table to ${username}", [])
            String metadata = JsonOutput.toJson([
                "app.db.driverClass": localConfig["pool.db.driverClass"],
                "app.db.url": localConfig["pool.db.url"],
                "app.db.username": username,
                "app.db.password": password
                    ])
            sql.execute("""
                INSERT INTO ${tableName} (id, metadata)
                values (?, ?)
            """, [i, metadata])
        }
    }
}
task lockDb {
    dependsOn initDriver
    onlyIf isCiBuild
    doLast {
        project.ext.lockUid = UUID.randomUUID().toString()
        String tableName = localConfig["pool.db.referenceTable"]
        Sql sql = createSqlInstance() as Sql
        sql.executeUpdate("""UPDATE ${tableName} SET GUID = ?, PROCESSED = SYSDATE
                WHERE ID IN (
                    SELECT ID FROM (
                        SELECT ID, ROW_NUMBER() OVER (ORDER BY PROCESSED) AS RN
                        FROM ${tableName} WHERE GUID IS NULL OR PROCESSED < (SYSDATE - NUMTODSINTERVAL(?, 'MINUTE'))
                    ) WHERE RN = 1
                )
                """, [lockUid, 15])
        def meta = sql.firstRow("SELECT METADATA FROM ${tableName} WHERE GUID = ?", [lockUid])
        assert meta != null, "No free databases in pool"
        def slurper = new JsonSlurper()
        Map metadata = slurper.parseText(meta["METADATA"] as String) as Map
        localConfig.putAll(metadata)
        logger.info("Database locked, {}", metadata)
    }
}
task unlockDb {
    dependsOn lockDb // init lockUid
    onlyIf isCiBuild
    doLast {
        try {
            String tableName = localConfig["pool.db.referenceTable"]
            Sql sql = createSqlInstance() as Sql
            sql.executeUpdate("UPDATE ${tableName} SET GUID = NULL WHERE GUID = ?",
                    [lockUid])
            logger.info("Database unlocked")
        } catch (ignored) {
            logger.error(ignored)
        }
    }
}
configure([processResources, processTestResources]) { Task t ->
    if (project.ext.isCiBuild()) {
        t.outputs.upToDateWhen { false }
    }
    t.filesMatching('**/*.properties') {
        filter(ReplaceTokens, tokens: localConfig, beginToken: '${', endToken: '}')
    }
}
task restoreAfterRollbackTest(type: LiquibaseTask) {
    command = 'update'
}
task rollbackTest(type: LiquibaseTask) {
    dependsOn lockDb
    command = 'rollback'
    requiresValue = true
    doFirst {
        project.ext.liquibaseCommandValue = localConfig['test.rollback.tag']
    }
    doLast {
        project.ext.liquibaseCommandValue = null
    }
}
configure([project]) {
    tasks.withType(LiquibaseTask.class) { LiquibaseTask t ->
        logger.info("Liquibase task {} must run after {}", t.getName(), configLiquibase.getName())
        (t as Task).dependsOn configLiquibase
        if (isCiBuild()) {
            logger.info("Liquibase task {} must run after {}", t.getName(), lockDb.getName())
            (t as Task).dependsOn lockDb
            (t as Task).finalizedBy unlockDb
        }
    }
    // На этапе CI:
    // 1. Чистим БД в 0 (dropAll)
    // 2. Обновляем БД для проверки rollback (update)
    // 3. Проверяем, что работает откат до изначального состояния (rollback tag)
    // 4. Обновляем БД для прогона тестов (update)
    // 5. Прогоняем тесты на БД
    if (doRollbackTest()) {
        def setTaskOrdering = { List lst ->
            for (int i = 0; i < lst.size() - 1; i++) {
                logger.info("Task {} must run after {}", lst[i + 1].getName(), lst[i].getName())
                lst[i + 1].dependsOn lst[i]
            }
        }
        setTaskOrdering([
                lockDb,
                configLiquibase,
                dropAll,
                update,
                rollbackTest,
                restoreAfterRollbackTest,
                processTestResources,
                test,
        ])
        lockDb.finalizedBy unlockDb
        test.finalizedBy unlockDb
    }
}

pool.db.enabled=false
test.rollback.enabled=true
pool.db.driverClass=oracle.jdbc.driver.OracleDriver
pool.db.url=jdbc:oracle:thin:@localhost:1527:ORCLCDB
pool.db.username=SYSTEM
pool.db.password=Oradoc_db1
pool.db.referenceTable=c##test_user1.REF_TABLE
pool.db.baseName=C##CI_SCHEMA_{id}
pool.db.basePass=CI_SCHEMA_{id}_PASS
app.db.driverClass=
app.db.url=
app.db.username=
app.db.password=
test.rollback.tag=version_1