This is an automated email from the ASF dual-hosted git repository. jdaugherty pushed a commit to branch database-cleanup-feature in repository https://gitbox.apache.org/repos/asf/grails-core.git
commit 67c5794562b00680487d5adfccc032dc8c1b45f0 Author: Test <[email protected]> AuthorDate: Sat Feb 21 23:09:06 2026 -0500 Add postgresql clean up support --- .../src/en/guide/testing/integrationTesting.adoc | 27 +- grails-testing-support-cleanup-core/README.md | 60 ++++ grails-testing-support-cleanup-h2/README.md | 33 +++ .../README.md | 80 +++++ .../build.gradle | 67 +++++ .../postgresql/PostgresDatabaseCleaner.groovy | 193 ++++++++++++ .../PostgresDatabaseCleanupHelper.groovy | 128 ++++++++ .../postgresql/PostgresContainerHolder.groovy | 66 +++++ .../PostgresDatabaseCleanerFunctionalSpec.groovy | 325 +++++++++++++++++++++ .../postgresql/PostgresDatabaseCleanerSpec.groovy | 106 +++++++ .../PostgresDatabaseCleanupHelperSpec.groovy | 83 ++++++ settings.gradle | 1 + 12 files changed, 1167 insertions(+), 2 deletions(-) diff --git a/grails-doc/src/en/guide/testing/integrationTesting.adoc b/grails-doc/src/en/guide/testing/integrationTesting.adoc index f9448adaf0..732f7be74e 100644 --- a/grails-doc/src/en/guide/testing/integrationTesting.adoc +++ b/grails-doc/src/en/guide/testing/integrationTesting.adoc @@ -148,7 +148,13 @@ If you do have a series of tests that will share state you can remove the `Rollb As an alternative to `@Rollback`, the `@DatabaseCleanup` annotation truncates all database tables after each test method, rather than rolling back the transaction. This is useful when your tests need data to actually be committed to the database (for example, to test behavior that depends on committed data or to verify transactional boundaries). -To use `@DatabaseCleanup`, add the `grails-testing-support-cleanup-h2` dependency (or the appropriate cleanup module for your database) to your integration test configuration: +===== Supported Databases + +Database cleanup is automatically discovered and applied based on your datasource configuration. The following database implementations are available: + +====== H2 Database + +To use `@DatabaseCleanup` with H2 databases, add the `grails-testing-support-cleanup-h2` dependency: [source,groovy] ---- @@ -157,7 +163,24 @@ dependencies { } ---- -The annotation can be applied at the class level to clean up after every test: +This module will clean up schema the specified schema by default, otherwise it will purge from all schemas. + +====== PostgreSQL + +To use `@DatabaseCleanup` with PostgreSQL databases, add the `grails-testing-support-cleanup-postgresql` dependency: + +[source,groovy] +---- +dependencies { + integrationTestImplementation 'org.apache.grails:grails-testing-support-cleanup-postgresql' +} +---- + +This module will clean up the schema specified by `currentSchema` in the jdbc url, or it will clean up all non-system schemas. + +===== Using DatabaseCleanup + +To use `@DatabaseCleanup`, add the appropriate cleanup module for your database to your integration test configuration. The annotation can be applied at the class level to clean up after every test: [source,groovy] ---- diff --git a/grails-testing-support-cleanup-core/README.md b/grails-testing-support-cleanup-core/README.md index f40440fe49..140a24e265 100644 --- a/grails-testing-support-cleanup-core/README.md +++ b/grails-testing-support-cleanup-core/README.md @@ -17,3 +17,63 @@ limitations under the License. ## grails-testing-support-cleanup-core Provides the core database cleanup testing support for Grails integration tests, including the `@DatabaseCleanup` annotation and the `DatabaseCleaner` SPI. + +### Supported Database Implementations + +Database cleanup is automatically discovered and applied based on your datasource configuration. The following database implementations are available: + +#### H2 Database (`grails-testing-support-cleanup-h2`) + +**Supported Driver**: H2 Database Engine + +**Libraries**: +- `org.apache.groovy:groovy-sql` - SQL DSL for Groovy +- `com.h2database:h2` (test scope) + +**Features**: +- Automatic table truncation for H2-backed tests +- Works with in-memory and file-based databases +- Uses `SET REFERENTIAL_INTEGRITY FALSE` for constraint handling + +**JDBC URL Format**: +``` +jdbc:h2:mem:testdb +jdbc:h2:file:/tmp/testdb +``` + +#### PostgreSQL (`grails-testing-support-cleanup-postgresql`) + +**Supported Driver**: PostgreSQL JDBC Driver + +**Libraries**: +- `org.apache.groovy:groovy-sql` - SQL DSL for Groovy +- `org.postgresql:postgresql` - PostgreSQL JDBC Driver +- `org.testcontainers:postgresql:1.20.1` (test scope) +- `org.testcontainers:testcontainers:1.20.1` (test scope) + +**Features**: +- Automatic table truncation for PostgreSQL-backed tests +- Schema-aware cleanup (single schema or all non-system schemas) +- Uses `session_replication_role = replica` for efficient constraint handling +- Supports both local and containerized PostgreSQL instances + +**JDBC URL Format**: +``` +jdbc:postgresql://localhost/testdb +jdbc:postgresql://localhost:5432/testdb?currentSchema=myschema +jdbc:postgresql://db.example.com/prod?user=postgres&password=secret +``` + +**Schema Cleanup Behavior**: +- **If `currentSchema` is set in JDBC URL**: Only tables in the specified schema are cleaned +- **If `currentSchema` is not set**: All non-system schemas are cleaned (excluding `pg_catalog`, `information_schema`, `pg_toast`) + +### Adding Support for Additional Databases + +To add database cleanup support for a new database: + +1. Create a new module: `grails-testing-support-cleanup-{database}` +2. Implement the `DatabaseCleaner` SPI interface +3. Register via ServiceLoader (place in `META-INF/services/org.apache.grails.testing.cleanup.core.DatabaseCleaner`) +4. Write unit tests (mocking datasource behavior) +5. Write functional tests (using TestContainers for real database instances) diff --git a/grails-testing-support-cleanup-h2/README.md b/grails-testing-support-cleanup-h2/README.md index 4a20b807f8..3d553ab308 100644 --- a/grails-testing-support-cleanup-h2/README.md +++ b/grails-testing-support-cleanup-h2/README.md @@ -17,3 +17,36 @@ limitations under the License. ## grails-testing-support-cleanup-h2 Provides the H2 database cleanup implementation for the `DatabaseCleaner` SPI, enabling automatic table truncation in H2-backed integration tests. + +### Required Libraries + +To use H2 database cleanup in your integration tests, add the following dependencies: + +```gradle +// H2 JDBC Driver +testImplementation 'com.h2database:h2' + +// Groovy SQL DSL (for H2DatabaseCleaner) +implementation 'org.apache.groovy:groovy-sql' +``` + +### How It Works + +The H2 cleanup implementation: +1. Detects H2 databases via JDBC URL pattern: `jdbc:h2:*` +2. Disables referential integrity: `SET REFERENTIAL_INTEGRITY FALSE` +3. Truncates all tables in the database +4. Re-enables referential integrity: `SET REFERENTIAL_INTEGRITY TRUE` +5. Records table row counts before cleanup + +### Example JDBC URLs + +``` +jdbc:h2:mem:testdb +jdbc:h2:file:./target/testdb +jdbc:h2:tcp://localhost/testdb +``` + +### Automatic Discovery + +This implementation is automatically discovered and applied when H2 is used as the datasource. No manual registration is required. diff --git a/grails-testing-support-cleanup-postgresql/README.md b/grails-testing-support-cleanup-postgresql/README.md new file mode 100644 index 0000000000..f022def871 --- /dev/null +++ b/grails-testing-support-cleanup-postgresql/README.md @@ -0,0 +1,80 @@ +<!-- +SPDX-License-Identifier: Apache-2.0 + +Licensed under the Apache License, Version 2.0 (the "License"); +you may not use this file except in compliance with the License. +You may obtain a copy of the License at + + https://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, software +distributed under the License is distributed on an "AS IS" BASIS, +WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +See the License for the specific language governing permissions and +limitations under the License. +--> + +## grails-testing-support-cleanup-postgresql + +Provides the PostgreSQL database cleanup implementation for the `DatabaseCleaner` SPI, enabling automatic table truncation in PostgreSQL-backed integration tests. + +### Required Libraries + +To use PostgreSQL database cleanup in your integration tests, add the following dependencies: + +```gradle +// PostgreSQL JDBC Driver +testImplementation 'org.postgresql:postgresql' + +// Groovy SQL DSL (for PostgresDatabaseCleaner) +implementation 'org.apache.groovy:groovy-sql' + +// Optional: For functional tests with Docker containers +testImplementation 'org.testcontainers:postgresql:1.20.1' +testImplementation 'org.testcontainers:testcontainers:1.20.1' +``` + +### How It Works + +The PostgreSQL cleanup implementation: +1. Detects PostgreSQL databases via JDBC URL pattern: `jdbc:postgresql:*` +2. Determines the schema to clean (from `currentSchema` parameter or all non-system schemas) +3. Disables all triggers and constraints at session level: `SET session_replication_role = replica` +4. Truncates all tables in the target schema(s) +5. Re-enables triggers and constraints: `SET session_replication_role = DEFAULT` +6. Records table row counts before cleanup + +This approach is more efficient than using `TRUNCATE TABLE ... CASCADE` because: +- Single session-level command disables all constraints at once +- No need to compute cascade order for foreign keys +- Handles complex relationships (self-referencing FKs, circular dependencies) seamlessly + +### Schema Cleanup Behavior + +- **If `currentSchema` is set in JDBC URL**: Only tables in the specified schema are cleaned +- **If `currentSchema` is not set**: All non-system schemas are cleaned (excluding `pg_catalog`, `information_schema`, `pg_toast`, `pg_temp_*`) + +### Example JDBC URLs + +``` +jdbc:postgresql://localhost/testdb +jdbc:postgresql://localhost:5432/testdb?currentSchema=myschema +jdbc:postgresql://db.example.com/prod?user=postgres&password=secret¤tSchema=test +``` + +### Functional Tests + +Functional tests requiring Docker can be run with: + +```bash +./gradlew test --tests "PostgresDatabaseCleanerFunctionalSpec" +``` + +These tests use TestContainers to start a real PostgreSQL database and verify cleanup behavior with: +- Actual foreign key constraints +- Multiple schemas +- Complex relationship hierarchies (self-referencing, circular dependencies) + +### Automatic Discovery + +This implementation is automatically discovered and applied when PostgreSQL is used as the datasource. No manual registration is required. diff --git a/grails-testing-support-cleanup-postgresql/build.gradle b/grails-testing-support-cleanup-postgresql/build.gradle new file mode 100644 index 0000000000..f384ad709e --- /dev/null +++ b/grails-testing-support-cleanup-postgresql/build.gradle @@ -0,0 +1,67 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * https://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +plugins { + id 'groovy' + id 'java-library' +} + +apply plugin: 'org.apache.grails.buildsrc.properties' +apply plugin: 'org.apache.grails.buildsrc.compile' +apply plugin: 'org.apache.grails.gradle.grails-code-style' + + +version = projectVersion +group = 'org.apache.grails' + +ext { + pomTitle = 'Grails Testing Cleanup Support for PostgreSQL' + pomDescription = 'Clean up PostgreSQL database after tests' +} + +dependencies { + implementation platform(project(':grails-bom')) + + api project(':grails-testing-support-cleanup-core') { + // api: DatabaseCleanupInterceptor + } + + implementation 'org.slf4j:slf4j-api' + implementation('org.apache.groovy:groovy-sql') + + api 'org.spockframework:spock-core', { + // api: IGlobalExtension, IMethodInterceptor, IMethodInvocation + } + + compileOnly 'org.apache.groovy:groovy', { + // comp: CompileStatic, CompileDynamic, TypeCheckingMode + } + + testImplementation 'org.apache.groovy:groovy' + testImplementation 'org.postgresql:postgresql' + testImplementation 'org.testcontainers:postgresql:1.20.1' + testImplementation 'org.testcontainers:testcontainers:1.20.1' + testImplementation 'org.slf4j:slf4j-simple' + testRuntimeOnly 'net.bytebuddy:byte-buddy' +} + +apply { + from rootProject.layout.projectDirectory.file('gradle/test-config.gradle') + from rootProject.layout.projectDirectory.file('gradle/docs-config.gradle') +} diff --git a/grails-testing-support-cleanup-postgresql/src/main/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleaner.groovy b/grails-testing-support-cleanup-postgresql/src/main/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleaner.groovy new file mode 100644 index 0000000000..51d456223e --- /dev/null +++ b/grails-testing-support-cleanup-postgresql/src/main/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleaner.groovy @@ -0,0 +1,193 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * https://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.grails.testing.cleanup.postgresql + +import java.sql.Connection +import java.sql.DatabaseMetaData + +import javax.sql.DataSource + +import groovy.sql.GroovyResultSet +import groovy.sql.Sql +import groovy.transform.CompileDynamic +import groovy.transform.CompileStatic +import groovy.util.logging.Slf4j + +import org.springframework.context.ApplicationContext +import org.springframework.util.ClassUtils + +import org.apache.grails.testing.cleanup.core.DatabaseCleaner +import org.apache.grails.testing.cleanup.core.DatabaseCleanupStats + +/** + * {@link DatabaseCleaner} implementation for PostgreSQL databases. Truncates all tables in the + * current schema (or all non-system schemas if no currentSchema is set) and optionally evicts the + * Hibernate second-level cache. + * + * <p>This cleaner identifies PostgreSQL databases by inspecting the JDBC URL of the datasource's + * connection metadata. It supports both local and remote PostgreSQL instances.</p> + * + * <p>Schema cleanup behavior: + * <ul> + * <li>If the JDBC URL contains a {@code currentSchema} parameter, only that schema is cleaned</li> + * <li>If no {@code currentSchema} is set, all non-system schemas are cleaned (excluding + * {@code pg_catalog}, {@code information_schema}, and {@code pg_toast})</li> + * </ul> + * </p> + */ +@Slf4j +@CompileStatic +class PostgresDatabaseCleaner implements DatabaseCleaner { + + private static final String DATABASE_TYPE = 'postgresql' + private static final List<String> SYSTEM_SCHEMAS = ['pg_catalog', 'information_schema', 'pg_toast', 'pg_temp_1'] + + @Override + String databaseType() { + DATABASE_TYPE + } + + @Override + boolean supports(DataSource dataSource) { + Connection connection = null + try { + connection = dataSource.getConnection() + DatabaseMetaData metaData = connection.getMetaData() + String url = metaData.getURL() + return url && url.startsWith('jdbc:postgresql:') + } + catch (Exception e) { + log.debug('Could not determine if datasource is PostgreSQL', e) + return false + } + finally { + if (connection) { + try { + connection.close() + } + catch (Exception ignored) { + // ignore + } + } + } + } + + @SuppressWarnings('SqlNoDataSourceInspection') + @Override + DatabaseCleanupStats cleanup(ApplicationContext applicationContext, DataSource dataSource) { + DatabaseCleanupStats stats = new DatabaseCleanupStats() + + Sql sql = null + try { + sql = new Sql(dataSource) + + // Disable all triggers and referential integrity checks for this session + // This is more efficient than using CASCADE on each truncate + sql.execute('SET session_replication_role = replica') + + String currentSchema = PostgresDatabaseCleanupHelper.resolveCurrentSchema(dataSource) + + if (currentSchema) { + log.debug('Cleaning schema: {}', currentSchema) + cleanupSchema(sql, currentSchema, stats) + } + else { + log.debug('No currentSchema parameter found, cleaning all non-system schemas') + cleanupNonSystemSchemas(sql, stats) + } + + cleanupCacheLayer(applicationContext) + } + catch (Exception e) { + log.warn('Error during database cleanup', e) + } + finally { + if (sql) { + try { + // Re-enable triggers and referential integrity checks + sql.execute('SET session_replication_role = DEFAULT') + sql.close() + } + catch (e) { + log.error('Error closing SQL connection after cleanup', e) + } + } + } + + stats + } + + @SuppressWarnings('SqlNoDataSourceInspection') + private void cleanupSchema(Sql sql, String schemaName, DatabaseCleanupStats stats) { + String query = """ + SELECT tablename FROM pg_tables + WHERE schemaname = '${schemaName}' + AND tablename NOT LIKE 'pg_%' + """ as String + + sql.eachRow(query) { GroovyResultSet row -> + String tableName = row['tablename'] as String + truncateTable(sql, schemaName, tableName, stats) + } + } + + @SuppressWarnings('SqlNoDataSourceInspection') + private void cleanupNonSystemSchemas(Sql sql, DatabaseCleanupStats stats) { + String query = """ + SELECT schemaname FROM pg_namespace + WHERE schemaname NOT IN (${SYSTEM_SCHEMAS.collect { "'$it'" }.join(',')}) + """ as String + + sql.eachRow(query) { GroovyResultSet row -> + String schemaName = row['schemaname'] as String + cleanupSchema(sql, schemaName, stats) + } + } + + @SuppressWarnings('SqlNoDataSourceInspection') + private void truncateTable(Sql sql, String schemaName, String tableName, DatabaseCleanupStats stats) { + String qualifiedTableName = "\"${schemaName}\".\"${tableName}\"" + try { + // Get row count before truncate + String countQuery = "SELECT COUNT(*) AS cnt FROM ${qualifiedTableName}" as String + Long rowCount = sql.firstRow(countQuery)?.cnt as Long ?: 0L + + if (rowCount > 0) { + log.debug('Truncating table: {}', qualifiedTableName) + // Since session_replication_role is set to 'replica', foreign keys are effectively disabled + String truncateQuery = "TRUNCATE TABLE ${qualifiedTableName}" as String + sql.execute(truncateQuery) + stats.tableRowCounts[tableName] += rowCount + } + } + catch (Exception e) { + log.warn('Failed to truncate table {}', qualifiedTableName, e) + } + } + + @CompileDynamic + private void cleanupCacheLayer(ApplicationContext applicationContext) { + // Clear the 2nd layer cache if it exists + if (ClassUtils.isPresent('org.hibernate.SessionFactory', this.class.classLoader)) { + def sessionFactory = applicationContext.getBean('sessionFactory', Class.forName('org.hibernate.SessionFactory')) + sessionFactory?.cache?.evictAllRegions() + } + } +} diff --git a/grails-testing-support-cleanup-postgresql/src/main/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanupHelper.groovy b/grails-testing-support-cleanup-postgresql/src/main/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanupHelper.groovy new file mode 100644 index 0000000000..44a4aedb18 --- /dev/null +++ b/grails-testing-support-cleanup-postgresql/src/main/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanupHelper.groovy @@ -0,0 +1,128 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * https://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.grails.testing.cleanup.postgresql + +import java.sql.Connection +import java.sql.DatabaseMetaData + +import javax.sql.DataSource + +import groovy.transform.CompileStatic +import groovy.util.logging.Slf4j + +/** + * Helper utility for PostgreSQL database cleanup operations. Provides PostgreSQL-specific logic such as + * resolving the current schema from a {@link DataSource} by inspecting JDBC connection metadata + * and parsing PostgreSQL JDBC URLs. + */ +@Slf4j +@CompileStatic +class PostgresDatabaseCleanupHelper { + + /** + * Resolves the current schema for the given PostgreSQL datasource by inspecting the JDBC connection metadata. + * If the JDBC URL contains a `currentSchema` parameter, returns that schema. + * Otherwise, returns the connection's current schema. + * + * @param dataSource the datasource to resolve the schema for + * @return the schema name, or {@code null} if it cannot be determined + */ + static String resolveCurrentSchema(DataSource dataSource) { + Connection connection = null + try { + connection = dataSource.getConnection() + String schema = connection.getSchema() + if (schema) { + log.debug('Resolved current schema from connection: {}', schema) + return schema + } + + // Fallback: try to get the schema from the database metadata URL + DatabaseMetaData metaData = connection.getMetaData() + String url = metaData.getURL() + if (url) { + schema = extractCurrentSchemaFromUrl(url) + if (schema) { + log.debug('Resolved current schema from URL {}: {}', url, schema) + return schema + } + } + } + catch (Exception e) { + log.warn('Failed to resolve current schema from datasource', e) + } + finally { + if (connection) { + try { + connection.close() + } + catch (Exception ignored) { + // ignore + } + } + } + null + } + + /** + * Extracts the current schema from a PostgreSQL JDBC URL by looking for the + * {@code currentSchema} parameter. Returns null if the parameter is not set. + * + * <p>Examples: + * <ul> + * <li>{@code jdbc:postgresql://localhost/testdb?currentSchema=myschema} → {@code myschema}</li> + * <li>{@code jdbc:postgresql://localhost/testdb} → {@code null}</li> + * <li>{@code jdbc:postgresql://localhost/testdb?currentSchema=myschema&other=value} → {@code myschema}</li> + * </ul> + * + * @param url the JDBC URL + * @return the current schema, or {@code null} if not set or URL format is not recognized + */ + static String extractCurrentSchemaFromUrl(String url) { + if (!url) { + return null + } + + // Look for currentSchema parameter in query string + int questionIdx = url.indexOf('?') + if (questionIdx < 0) { + return null + } + + String queryString = url.substring(questionIdx + 1) + String[] params = queryString.split('&') + for (String param : params) { + if (param.startsWith('currentSchema=')) { + String schema = param.substring('currentSchema='.length()) + // URL decode if needed (handle common cases) + if (schema) { + // Remove any trailing parameters if present + int ampIdx = schema.indexOf('&') + if (ampIdx >= 0) { + schema = schema.substring(0, ampIdx) + } + return schema ?: null + } + } + } + + null + } +} diff --git a/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresContainerHolder.groovy b/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresContainerHolder.groovy new file mode 100644 index 0000000000..618c74847a --- /dev/null +++ b/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresContainerHolder.groovy @@ -0,0 +1,66 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * https://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.grails.testing.cleanup.postgresql + +import groovy.transform.PackageScope + +import org.slf4j.LoggerFactory +import org.testcontainers.containers.GenericContainer +import org.testcontainers.containers.PostgreSQLContainer +import org.testcontainers.containers.output.Slf4jLogConsumer +import org.testcontainers.utility.DockerImageName + +/** + * Manages the lifecycle of PostgreSQL test containers. Ensures a single container + * is started per thread and can be reused across multiple tests. + */ +class PostgresContainerHolder { + + private ThreadLocal<GenericContainer> containers = new ThreadLocal<GenericContainer>() + final DockerImageName desiredImage + + PostgresContainerHolder(DockerImageName desiredImage) { + this.desiredImage = desiredImage + } + + @PackageScope + static GenericContainer startPostgresContainer(DockerImageName dockerImageName) { + GenericContainer dbContainer = new PostgreSQLContainer(dockerImageName) + dbContainer.start() + dbContainer.followOutput(new Slf4jLogConsumer(LoggerFactory.getLogger('testcontainers'))) + dbContainer + } + + GenericContainer getContainer() { + GenericContainer foundContainer = containers.get() + if (foundContainer) { + return foundContainer + } + + GenericContainer startedContainer = startPostgresContainer(desiredImage) + containers.set(startedContainer) + startedContainer + } + + void stop() { + containers.get()?.stop() + containers.remove() + } +} diff --git a/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanerFunctionalSpec.groovy b/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanerFunctionalSpec.groovy new file mode 100644 index 0000000000..e90b62db8f --- /dev/null +++ b/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanerFunctionalSpec.groovy @@ -0,0 +1,325 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * https://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.grails.testing.cleanup.postgresql + +import groovy.sql.Sql + +import org.postgresql.ds.PGSimpleDataSource +import org.springframework.context.ApplicationContext +import org.testcontainers.containers.GenericContainer +import org.testcontainers.containers.PostgreSQLContainer +import org.testcontainers.utility.DockerImageName + +import spock.lang.AutoCleanup +import spock.lang.Shared +import spock.lang.Specification + +import org.apache.grails.testing.cleanup.core.DatabaseCleanupStats + +/** + * Functional tests for PostgreSQL database cleanup using TestContainers. + * + * These tests require Docker to be available on the system. + * + * Run with: {@code ./gradlew test --tests "PostgresDatabaseCleanerFunctionalSpec"} + */ +class PostgresDatabaseCleanerFunctionalSpec extends Specification { + + @Shared + PostgresContainerHolder containerHolder + + @Shared + GenericContainer postgresContainer + + static PostgresContainerHolder setupContainerHolder() { + DockerImageName image = DockerImageName.parse('postgres:15-alpine') + new PostgresContainerHolder(image) + } + + def setupSpec() { + containerHolder = setupContainerHolder() + postgresContainer = containerHolder.container + } + + def cleanupSpec() { + containerHolder.stop() + } + + private PGSimpleDataSource createDataSourceWithSchema(String currentSchema = null) { + PostgreSQLContainer postgres = postgresContainer as PostgreSQLContainer + PGSimpleDataSource dataSource = new PGSimpleDataSource() + dataSource.serverName = postgres.host + dataSource.portNumber = postgres.firstMappedPort + dataSource.databaseName = postgres.databaseName + dataSource.user = postgres.username + dataSource.password = postgres.password + + if (currentSchema) { + // For PostgreSQL driver, use setProperty for currentSchema + dataSource.setProperty('currentSchema', currentSchema) + } + + dataSource + } + + def "test cleanup with currentSchema parameter"() { + given: + PGSimpleDataSource dataSource = createDataSourceWithSchema('testschema') + Sql sql = new Sql(dataSource) + + try { + // Create schema and tables + sql.execute('CREATE SCHEMA IF NOT EXISTS testschema') + sql.execute('CREATE TABLE IF NOT EXISTS testschema.users (id SERIAL PRIMARY KEY, name VARCHAR(255))') + sql.execute('CREATE TABLE IF NOT EXISTS testschema.books (id SERIAL PRIMARY KEY, title VARCHAR(255), user_id INTEGER)') + + // Insert data + sql.execute("INSERT INTO testschema.users (name) VALUES ('Alice')") + sql.execute("INSERT INTO testschema.users (name) VALUES ('Bob')") + sql.execute("INSERT INTO testschema.books (title, user_id) VALUES ('Book 1', 1)") + + ApplicationContext applicationContext = Stub(ApplicationContext) + PostgresDatabaseCleaner cleaner = new PostgresDatabaseCleaner() + + when: + DatabaseCleanupStats stats = cleaner.cleanup(applicationContext, dataSource) + + then: + stats.tableRowCounts['users'] == 2L + stats.tableRowCounts['books'] == 1L + + and: 'tables are now empty' + sql.firstRow('SELECT COUNT(*) AS cnt FROM testschema.users').cnt == 0 + sql.firstRow('SELECT COUNT(*) AS cnt FROM testschema.books').cnt == 0 + } + finally { + sql.execute('DROP TABLE IF EXISTS testschema.books') + sql.execute('DROP TABLE IF EXISTS testschema.users') + sql.execute('DROP SCHEMA IF EXISTS testschema') + sql.close() + } + } + + def "test cleanup without currentSchema cleans all non-system schemas"() { + given: + PGSimpleDataSource dataSource = createDataSourceWithSchema() + Sql sql = new Sql(dataSource) + + try { + // Create multiple schemas and tables + sql.execute('CREATE SCHEMA IF NOT EXISTS schema1') + sql.execute('CREATE SCHEMA IF NOT EXISTS schema2') + sql.execute('CREATE TABLE IF NOT EXISTS schema1.items (id SERIAL PRIMARY KEY, name VARCHAR(255))') + sql.execute('CREATE TABLE IF NOT EXISTS schema2.orders (id SERIAL PRIMARY KEY, description VARCHAR(255))') + + // Insert data + sql.execute("INSERT INTO schema1.items (name) VALUES ('Item 1')") + sql.execute("INSERT INTO schema1.items (name) VALUES ('Item 2')") + sql.execute("INSERT INTO schema2.orders (description) VALUES ('Order A')") + + ApplicationContext applicationContext = Stub(ApplicationContext) + PostgresDatabaseCleaner cleaner = new PostgresDatabaseCleaner() + + when: + DatabaseCleanupStats stats = cleaner.cleanup(applicationContext, dataSource) + + then: + stats.tableRowCounts['items'] == 2L + stats.tableRowCounts['orders'] == 1L + + and: 'all tables in all schemas are empty' + sql.firstRow('SELECT COUNT(*) AS cnt FROM schema1.items').cnt == 0 + sql.firstRow('SELECT COUNT(*) AS cnt FROM schema2.orders').cnt == 0 + } + finally { + sql.execute('DROP TABLE IF EXISTS schema2.orders') + sql.execute('DROP TABLE IF EXISTS schema1.items') + sql.execute('DROP SCHEMA IF EXISTS schema1') + sql.execute('DROP SCHEMA IF EXISTS schema2') + sql.close() + } + } + + def "test supports method identifies PostgreSQL databases"() { + given: + PGSimpleDataSource dataSource = createDataSourceWithSchema() + PostgresDatabaseCleaner cleaner = new PostgresDatabaseCleaner() + + expect: + cleaner.supports(dataSource) + cleaner.databaseType() == 'postgresql' + } + + def "test cleanup handles foreign key constraints with CASCADE"() { + given: + PGSimpleDataSource dataSource = createDataSourceWithSchema('fktest') + Sql sql = new Sql(dataSource) + + try { + sql.execute('CREATE SCHEMA IF NOT EXISTS fktest') + sql.execute('CREATE TABLE IF NOT EXISTS fktest.categories (id SERIAL PRIMARY KEY, name VARCHAR(255))') + sql.execute('CREATE TABLE IF NOT EXISTS fktest.products (id SERIAL PRIMARY KEY, name VARCHAR(255), category_id INTEGER REFERENCES fktest.categories(id))') + + sql.execute("INSERT INTO fktest.categories (name) VALUES ('Electronics')") + sql.execute("INSERT INTO fktest.products (name, category_id) VALUES ('Laptop', 1)") + + ApplicationContext applicationContext = Stub(ApplicationContext) + PostgresDatabaseCleaner cleaner = new PostgresDatabaseCleaner() + + when: + DatabaseCleanupStats stats = cleaner.cleanup(applicationContext, dataSource) + + then: + stats.tableRowCounts['categories'] == 1L + stats.tableRowCounts['products'] == 1L + + and: 'both tables are empty' + sql.firstRow('SELECT COUNT(*) AS cnt FROM fktest.categories').cnt == 0 + sql.firstRow('SELECT COUNT(*) AS cnt FROM fktest.products').cnt == 0 + } + finally { + sql.execute('DROP TABLE IF EXISTS fktest.products') + sql.execute('DROP TABLE IF EXISTS fktest.categories') + sql.execute('DROP SCHEMA IF EXISTS fktest') + sql.close() + } + } + + def "test cleanup with complex foreign key relationships"() { + given: + PGSimpleDataSource dataSource = createDataSourceWithSchema('fkcomplex') + Sql sql = new Sql(dataSource) + + try { + sql.execute('CREATE SCHEMA IF NOT EXISTS fkcomplex') + // Create parent table + sql.execute('CREATE TABLE IF NOT EXISTS fkcomplex.departments (id SERIAL PRIMARY KEY, name VARCHAR(255))') + // Create child table with FK to departments + sql.execute('CREATE TABLE IF NOT EXISTS fkcomplex.employees (id SERIAL PRIMARY KEY, name VARCHAR(255), dept_id INTEGER REFERENCES fkcomplex.departments(id))') + // Create grandchild table with FK to employees + sql.execute('CREATE TABLE IF NOT EXISTS fkcomplex.projects (id SERIAL PRIMARY KEY, title VARCHAR(255), employee_id INTEGER REFERENCES fkcomplex.employees(id))') + + // Insert data with FK relationships + sql.execute("INSERT INTO fkcomplex.departments (name) VALUES ('Engineering')") + sql.execute("INSERT INTO fkcomplex.departments (name) VALUES ('Sales')") + sql.execute("INSERT INTO fkcomplex.employees (name, dept_id) VALUES ('Alice', 1)") + sql.execute("INSERT INTO fkcomplex.employees (name, dept_id) VALUES ('Bob', 2)") + sql.execute("INSERT INTO fkcomplex.projects (title, employee_id) VALUES ('Project X', 1)") + sql.execute("INSERT INTO fkcomplex.projects (title, employee_id) VALUES ('Project Y', 2)") + + ApplicationContext applicationContext = Stub(ApplicationContext) + PostgresDatabaseCleaner cleaner = new PostgresDatabaseCleaner() + + when: + DatabaseCleanupStats stats = cleaner.cleanup(applicationContext, dataSource) + + then: 'all data before cleanup is recorded' + stats.tableRowCounts['departments'] == 2L + stats.tableRowCounts['employees'] == 2L + stats.tableRowCounts['projects'] == 2L + + and: 'all tables are truncated despite complex FK relationships' + sql.firstRow('SELECT COUNT(*) AS cnt FROM fkcomplex.departments').cnt == 0 + sql.firstRow('SELECT COUNT(*) AS cnt FROM fkcomplex.employees').cnt == 0 + sql.firstRow('SELECT COUNT(*) AS cnt FROM fkcomplex.projects').cnt == 0 + + and: 'sequences are reset for next test' + sql.firstRow('SELECT nextval(\'fkcomplex.departments_id_seq\') AS next_id').next_id == 3L + } + finally { + sql.execute('DROP TABLE IF EXISTS fkcomplex.projects') + sql.execute('DROP TABLE IF EXISTS fkcomplex.employees') + sql.execute('DROP TABLE IF EXISTS fkcomplex.departments') + sql.execute('DROP SCHEMA IF EXISTS fkcomplex') + sql.close() + } + } + + def "test cleanup verifies foreign key constraints are disabled during cleanup"() { + given: + PGSimpleDataSource dataSource = createDataSourceWithSchema('fkreplica') + Sql sql = new Sql(dataSource) + + try { + sql.execute('CREATE SCHEMA IF NOT EXISTS fkreplica') + sql.execute('CREATE TABLE IF NOT EXISTS fkreplica.authors (id SERIAL PRIMARY KEY, name VARCHAR(255))') + sql.execute('CREATE TABLE IF NOT EXISTS fkreplica.books (id SERIAL PRIMARY KEY, title VARCHAR(255), author_id INTEGER NOT NULL REFERENCES fkreplica.authors(id))') + + // Insert valid data + sql.execute("INSERT INTO fkreplica.authors (name) VALUES ('Author 1')") + sql.execute("INSERT INTO fkreplica.books (title, author_id) VALUES ('Book 1', 1)") + + ApplicationContext applicationContext = Stub(ApplicationContext) + PostgresDatabaseCleaner cleaner = new PostgresDatabaseCleaner() + + when: 'cleanup is executed' + DatabaseCleanupStats stats = cleaner.cleanup(applicationContext, dataSource) + + then: 'cleanup succeeds despite FK constraints' + stats.tableRowCounts['authors'] == 1L + stats.tableRowCounts['books'] == 1L + + and: 'all tables are truncated' + sql.firstRow('SELECT COUNT(*) AS cnt FROM fkreplica.authors').cnt == 0 + sql.firstRow('SELECT COUNT(*) AS cnt FROM fkreplica.books').cnt == 0 + } + finally { + sql.execute('DROP TABLE IF EXISTS fkreplica.books') + sql.execute('DROP TABLE IF EXISTS fkreplica.authors') + sql.execute('DROP SCHEMA IF EXISTS fkreplica') + sql.close() + } + } + + def "test cleanup with self-referencing foreign key"() { + given: + PGSimpleDataSource dataSource = createDataSourceWithSchema('fkself') + Sql sql = new Sql(dataSource) + + try { + sql.execute('CREATE SCHEMA IF NOT EXISTS fkself') + // Create table with self-referencing FK + sql.execute('CREATE TABLE IF NOT EXISTS fkself.nodes (id SERIAL PRIMARY KEY, name VARCHAR(255), parent_id INTEGER REFERENCES fkself.nodes(id))') + + // Insert hierarchical data + sql.execute("INSERT INTO fkself.nodes (name, parent_id) VALUES ('Root', NULL)") + sql.execute("INSERT INTO fkself.nodes (name, parent_id) VALUES ('Child 1', 1)") + sql.execute("INSERT INTO fkself.nodes (name, parent_id) VALUES ('Child 2', 1)") + sql.execute("INSERT INTO fkself.nodes (name, parent_id) VALUES ('Grandchild', 2)") + + ApplicationContext applicationContext = Stub(ApplicationContext) + PostgresDatabaseCleaner cleaner = new PostgresDatabaseCleaner() + + when: + DatabaseCleanupStats stats = cleaner.cleanup(applicationContext, dataSource) + + then: 'cleanup handles self-referencing FK' + stats.tableRowCounts['nodes'] == 4L + + and: 'table is empty after cleanup' + sql.firstRow('SELECT COUNT(*) AS cnt FROM fkself.nodes').cnt == 0 + } + finally { + sql.execute('DROP TABLE IF EXISTS fkself.nodes') + sql.execute('DROP SCHEMA IF EXISTS fkself') + sql.close() + } + } +} diff --git a/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanerSpec.groovy b/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanerSpec.groovy new file mode 100644 index 0000000000..a50022bc6e --- /dev/null +++ b/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanerSpec.groovy @@ -0,0 +1,106 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * https://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.grails.testing.cleanup.postgresql + +import org.springframework.context.ApplicationContext + +import spock.lang.Specification + +class PostgresDatabaseCleanerSpec extends Specification { + + def "databaseType returns 'postgresql'"() { + given: + def cleaner = new PostgresDatabaseCleaner() + + expect: + cleaner.databaseType() == 'postgresql' + } + + def "supports returns true for PostgreSQL datasource"() { + given: + def cleaner = new PostgresDatabaseCleaner() + def postgresDataSource = Mock(javax.sql.DataSource) { + getConnection() >> Mock(java.sql.Connection) { + getMetaData() >> Mock(java.sql.DatabaseMetaData) { + getURL() >> 'jdbc:postgresql://localhost/testdb' + } + } + } + + expect: + cleaner.supports(postgresDataSource) + } + + def "supports returns true for PostgreSQL datasource with port"() { + given: + def cleaner = new PostgresDatabaseCleaner() + def postgresDataSource = Mock(javax.sql.DataSource) { + getConnection() >> Mock(java.sql.Connection) { + getMetaData() >> Mock(java.sql.DatabaseMetaData) { + getURL() >> 'jdbc:postgresql://localhost:5432/mydb' + } + } + } + + expect: + cleaner.supports(postgresDataSource) + } + + def "supports returns false for non-PostgreSQL datasource"() { + given: + def cleaner = new PostgresDatabaseCleaner() + def mysqlDataSource = Mock(javax.sql.DataSource) { + getConnection() >> Mock(java.sql.Connection) { + getMetaData() >> Mock(java.sql.DatabaseMetaData) { + getURL() >> 'jdbc:mysql://localhost/testdb' + } + } + } + + expect: + !cleaner.supports(mysqlDataSource) + } + + def "supports returns false when connection fails"() { + given: + def cleaner = new PostgresDatabaseCleaner() + def badDataSource = Mock(javax.sql.DataSource) { + getConnection() >> { throw new RuntimeException('Cannot connect') } + } + + expect: + !cleaner.supports(badDataSource) + } + + def "cleanup returns empty stats when schema cannot be resolved"() { + given: + def applicationContext = Stub(ApplicationContext) + def cleaner = new PostgresDatabaseCleaner() + def badDataSource = Mock(javax.sql.DataSource) { + getConnection() >> { throw new RuntimeException('Cannot connect') } + } + + when: + def stats = cleaner.cleanup(applicationContext, badDataSource) + + then: + stats.tableRowCounts.isEmpty() + } +} diff --git a/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanupHelperSpec.groovy b/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanupHelperSpec.groovy new file mode 100644 index 0000000000..2343a691db --- /dev/null +++ b/grails-testing-support-cleanup-postgresql/src/test/groovy/org/apache/grails/testing/cleanup/postgresql/PostgresDatabaseCleanupHelperSpec.groovy @@ -0,0 +1,83 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * https://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, + * software distributed under the License is distributed on an + * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY + * KIND, either express or implied. See the License for the + * specific language governing permissions and limitations + * under the License. + */ + +package org.apache.grails.testing.cleanup.postgresql + +import spock.lang.Specification + +class PostgresDatabaseCleanupHelperSpec extends Specification { + + def "extractCurrentSchemaFromUrl returns null when URL has no query string"() { + given: + String url = 'jdbc:postgresql://localhost/testdb' + + expect: + PostgresDatabaseCleanupHelper.extractCurrentSchemaFromUrl(url) == null + } + + def "extractCurrentSchemaFromUrl extracts schema from currentSchema parameter"() { + given: + String url = 'jdbc:postgresql://localhost/testdb?currentSchema=myschema' + + expect: + PostgresDatabaseCleanupHelper.extractCurrentSchemaFromUrl(url) == 'myschema' + } + + def "extractCurrentSchemaFromUrl extracts schema with other parameters"() { + given: + String url = 'jdbc:postgresql://localhost/testdb?user=postgres¤tSchema=testschema&password=secret' + + expect: + PostgresDatabaseCleanupHelper.extractCurrentSchemaFromUrl(url) == 'testschema' + } + + def "extractCurrentSchemaFromUrl returns null for empty schema"() { + given: + String url = 'jdbc:postgresql://localhost/testdb?currentSchema=' + + expect: + PostgresDatabaseCleanupHelper.extractCurrentSchemaFromUrl(url) == null + } + + def "extractCurrentSchemaFromUrl returns null for null input"() { + expect: + PostgresDatabaseCleanupHelper.extractCurrentSchemaFromUrl(null) == null + } + + def "extractCurrentSchemaFromUrl returns null for empty input"() { + expect: + PostgresDatabaseCleanupHelper.extractCurrentSchemaFromUrl('') == null + } + + def "extractCurrentSchemaFromUrl returns null when currentSchema is not present"() { + given: + String url = 'jdbc:postgresql://localhost/testdb?user=postgres&password=secret' + + expect: + PostgresDatabaseCleanupHelper.extractCurrentSchemaFromUrl(url) == null + } + + def "extractCurrentSchemaFromUrl handles multiple query parameters correctly"() { + given: + String url = 'jdbc:postgresql://localhost:5432/testdb?sslmode=require¤tSchema=custom&application_name=app' + + expect: + PostgresDatabaseCleanupHelper.extractCurrentSchemaFromUrl(url) == 'custom' + } +} diff --git a/settings.gradle b/settings.gradle index a7de8bb4d5..1357cc651b 100644 --- a/settings.gradle +++ b/settings.gradle @@ -173,6 +173,7 @@ include( 'grails-testing-support-datamapping', 'grails-testing-support-cleanup-core', 'grails-testing-support-cleanup-h2', + 'grails-testing-support-cleanup-postgresql', // Test Report 'grails-data-test-report',
