Незавершенный сценарий инициализации долларовой котировки с тестовыми контейнерами

У меня есть сценарий инициализации, который будет выполняться при запуске testcontainers (postgres).
Мои настройки:

  • весенний ботинок
  • весенние данные jdbc
  • тестконтейнеры на докере
  • postgresql

Все фреймворки актуальны на сегодняшний день.

Это отрывок из сценария:

create or replace function check_registration_limits() returns trigger
    language plpgsql
as $$
BEGIN
    IF(SELECT count(*) FROM applicationusersubjectregistration ausr WHERE NEW.campus = ausr.campus and NEW.subject = ausr.subject)
        >= (SELECT s.maxparticipants FROM subject s WHERE s.subjectid = NEW.subject) THEN
        RAISE EXCEPTION 'Registration limits reached! No more voting allowed!';
    END IF;
    RETURN NEW;
END
$$;

Когда я запускаю свой тест, возникает эта трассировка стека:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourceInitializer' defined in de.thd.awp.TestDataSourceConfiguration: Invocation of init method failed; nested exception is org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #30 of class path resource [schema.sql]: create or replace function check_registration_limits() returns trigger language plpgsql as $$ BEGIN IF(SELECT count(*) FROM applicationusersubjectregistration ausr WHERE NEW.campus = ausr.campus and NEW.subject = ausr.subject) >= (SELECT s.maxparticipants FROM subject s WHERE s.subjectid = NEW.subject) THEN RAISE EXCEPTION 'Registration limits reached! No more voting allowed!'; nested exception is org.postgresql.util.PSQLException: Unterminated dollar quote started at position 91 in SQL create or replace function check_registration_limits() returns trigger language plpgsql as $$ BEGIN IF(SELECT count(*) FROM applicationusersubjectregistration ausr WHERE NEW.campus = ausr.campus and NEW.subject = ausr.subject) >= (SELECT s.maxparticipants FROM subject s WHERE s.subjectid = NEW.subject) THEN RAISE EXCEPTION 'Registration limits reached! No more voting allowed!'. Expected terminating $$
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1786) ~[spring-beans-5.3.6.jar:5.3.6]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:602) ~[spring-beans-5.3.6.jar:5.3.6]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:524) ~[spring-beans-5.3.6.jar:5.3.6]
    at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:335) ~[spring-beans-5.3.6.jar:5.3.6]
    at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:234) ~[spring-beans-5.3.6.jar:5.3.6]
    at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:333) ~[spring-beans-5.3.6.jar:5.3.6]
    at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:208) ~[spring-beans-5.3.6.jar:5.3.6]
    at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:944) ~[spring-beans-5.3.6.jar:5.3.6]
    at org.springframework.context.support.AbstractApplicationContext.finishBeanFactoryInitialization(AbstractApplicationContext.java:918) ~[spring-context-5.3.6.jar:5.3.6]
    at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:583) ~[spring-context-5.3.6.jar:5.3.6]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:782) ~[spring-boot-2.4.5.jar:2.4.5]
    at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:774) ~[spring-boot-2.4.5.jar:2.4.5]
    at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:439) ~[spring-boot-2.4.5.jar:2.4.5]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:339) ~[spring-boot-2.4.5.jar:2.4.5]
    at org.springframework.boot.test.context.SpringBootContextLoader.loadContext(SpringBootContextLoader.java:123) ~[spring-boot-test-2.4.5.jar:2.4.5]
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContextInternal(DefaultCacheAwareContextLoaderDelegate.java:99) ~[spring-test-5.3.6.jar:5.3.6]
    at org.springframework.test.context.cache.DefaultCacheAwareContextLoaderDelegate.loadContext(DefaultCacheAwareContextLoaderDelegate.java:124) ~[spring-test-5.3.6.jar:5.3.6]
    at org.springframework.test.context.support.DefaultTestContext.getApplicationContext(DefaultTestContext.java:124) ~[spring-test-5.3.6.jar:5.3.6]
    at org.springframework.test.context.web.ServletTestExecutionListener.setUpRequestContextIfNecessary(ServletTestExecutionListener.java:190) ~[spring-test-5.3.6.jar:5.3.6]
    at org.springframework.test.context.web.ServletTestExecutionListener.prepareTestInstance(ServletTestExecutionListener.java:132) ~[spring-test-5.3.6.jar:5.3.6]
    at org.springframework.test.context.TestContextManager.prepareTestInstance(TestContextManager.java:244) ~[spring-test-5.3.6.jar:5.3.6]
    at org.springframework.test.context.junit.jupiter.SpringExtension.postProcessTestInstance(SpringExtension.java:138) ~[spring-test-5.3.6.jar:5.3.6]
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.lambda$invokeTestInstancePostProcessors$6(ClassBasedTestDescriptor.java:350) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.executeAndMaskThrowable(ClassBasedTestDescriptor.java:355) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.lambda$invokeTestInstancePostProcessors$7(ClassBasedTestDescriptor.java:350) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[na:na]
    at java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:177) ~[na:na]
    at java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[na:na]
    at java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[na:na]
    at java.base/java.util.stream.StreamSpliterators$WrappingSpliterator.forEachRemaining(StreamSpliterators.java:312) ~[na:na]
    at java.base/java.util.stream.Streams$ConcatSpliterator.forEachRemaining(Streams.java:735) ~[na:na]
    at java.base/java.util.stream.Streams$ConcatSpliterator.forEachRemaining(Streams.java:734) ~[na:na]
    at java.base/java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:658) ~[na:na]
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.invokeTestInstancePostProcessors(ClassBasedTestDescriptor.java:349) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.lambda$instantiateAndPostProcessTestInstance$4(ClassBasedTestDescriptor.java:270) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.instantiateAndPostProcessTestInstance(ClassBasedTestDescriptor.java:269) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.lambda$testInstancesProvider$2(ClassBasedTestDescriptor.java:259) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at java.base/java.util.Optional.orElseGet(Optional.java:362) ~[na:na]
    at org.junit.jupiter.engine.descriptor.ClassBasedTestDescriptor.lambda$testInstancesProvider$3(ClassBasedTestDescriptor.java:258) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.jupiter.engine.execution.TestInstancesProvider.getTestInstances(TestInstancesProvider.java:31) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$prepare$0(TestMethodTestDescriptor.java:101) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.prepare(TestMethodTestDescriptor.java:100) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.prepare(TestMethodTestDescriptor.java:65) ~[junit-jupiter-engine-5.7.1.jar:5.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$prepare$1(NodeTestTask.java:111) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.prepare(NodeTestTask.java:111) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:79) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1511) ~[na:na]
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:143) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:129) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:137) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:127) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:126) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:84) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51) ~[junit-platform-engine-1.7.1.jar:1.7.1]
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:108) ~[junit-platform-launcher-1.7.1.jar:1.7.1]
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:88) ~[junit-platform-launcher-1.7.1.jar:1.7.1]
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:54) ~[junit-platform-launcher-1.7.1.jar:1.7.1]
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:67) ~[junit-platform-launcher-1.7.1.jar:1.7.1]
    at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:52) ~[junit-platform-launcher-1.7.1.jar:1.7.1]
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:96) ~[junit-platform-launcher-1.7.1.jar:1.7.1]
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:75) ~[junit-platform-launcher-1.7.1.jar:1.7.1]
    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:71) ~[junit5-rt.jar:na]
    at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:33) ~[junit-rt.jar:na]
    at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:221) ~[junit-rt.jar:na]
    at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54) ~[junit-rt.jar:na]
Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement #30 of class path resource [schema.sql]: create or replace function check_registration_limits() returns trigger language plpgsql as $$ BEGIN IF(SELECT count(*) FROM applicationusersubjectregistration ausr WHERE NEW.campus = ausr.campus and NEW.subject = ausr.subject) >= (SELECT s.maxparticipants FROM subject s WHERE s.subjectid = NEW.subject) THEN RAISE EXCEPTION 'Registration limits reached! No more voting allowed!'; nested exception is org.postgresql.util.PSQLException: Unterminated dollar quote started at position 91 in SQL create or replace function check_registration_limits() returns trigger language plpgsql as $$ BEGIN IF(SELECT count(*) FROM applicationusersubjectregistration ausr WHERE NEW.campus = ausr.campus and NEW.subject = ausr.subject) >= (SELECT s.maxparticipants FROM subject s WHERE s.subjectid = NEW.subject) THEN RAISE EXCEPTION 'Registration limits reached! No more voting allowed!'. Expected terminating $$
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:622) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.jdbc.datasource.init.DataSourceInitializer.execute(DataSourceInitializer.java:111) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.jdbc.datasource.init.DataSourceInitializer.afterPropertiesSet(DataSourceInitializer.java:96) ~[spring-jdbc-5.3.6.jar:5.3.6]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1845) ~[spring-beans-5.3.6.jar:5.3.6]
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1782) ~[spring-beans-5.3.6.jar:5.3.6]
    ... 83 common frames omitted
Caused by: org.postgresql.util.PSQLException: Unterminated dollar quote started at position 91 in SQL create or replace function check_registration_limits() returns trigger language plpgsql as $$ BEGIN IF(SELECT count(*) FROM applicationusersubjectregistration ausr WHERE NEW.campus = ausr.campus and NEW.subject = ausr.subject) >= (SELECT s.maxparticipants FROM subject s WHERE s.subjectid = NEW.subject) THEN RAISE EXCEPTION 'Registration limits reached! No more voting allowed!'. Expected terminating $$
    at org.postgresql.core.Parser.checkParsePosition(Parser.java:1328) ~[postgresql-42.2.19.jar:42.2.19]
    at org.postgresql.core.Parser.parseSql(Parser.java:1227) ~[postgresql-42.2.19.jar:42.2.19]
    at org.postgresql.core.Parser.replaceProcessing(Parser.java:1179) ~[postgresql-42.2.19.jar:42.2.19]
    at org.postgresql.core.CachedQueryCreateAction.create(CachedQueryCreateAction.java:43) ~[postgresql-42.2.19.jar:42.2.19]
    at org.postgresql.core.QueryExecutorBase.createQueryByKey(QueryExecutorBase.java:337) ~[postgresql-42.2.19.jar:42.2.19]
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:300) ~[postgresql-42.2.19.jar:42.2.19]
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284) ~[postgresql-42.2.19.jar:42.2.19]
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279) ~[postgresql-42.2.19.jar:42.2.19]
    at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:601) ~[spring-jdbc-5.3.6.jar:5.3.6]
    ... 89 common frames omitted

Может быть, это какая-то ошибка экранирования-сборки-вуду-скрипта?
Или мне нужно экранировать некоторые части скрипта?
Заранее спасибо

EDIT
Я пытаюсь инициализировать базу данных docker/testcontainers/postgres с помощью тестовых ресурсов Spring Boot schema.sql и DataSourceInitializer:

@Bean
    @Primary
    public DataSourceInitializer testDataSourceInitializer() {
        ResourceDatabasePopulator resourceDatabasePopulator = new ResourceDatabasePopulator();
        resourceDatabasePopulator.addScript(new ClassPathResource("/schema.sql"));

        DataSourceInitializer dataSourceInitializer = new DataSourceInitializer();
        dataSourceInitializer.setDataSource(testDataSource());
        dataSourceInitializer.setDatabasePopulator(resourceDatabasePopulator);
        return dataSourceInitializer;
    }

postgresql spring-data-jpa testcontainers spring-data-jdbc
person Thomas Lang    schedule 27.04.2021    source источник
comment
где, когда и как вы запускаете этот скрипт? это файл sql в проекте или какой-то метод инициализации с вызовом jdbc или что-то еще?   -  person Yuriy Tsarkov    schedule 27.04.2021
comment
судя по логу, почему-то не весь текст функции распознан как скрипт для выполнения   -  person Yuriy Tsarkov    schedule 27.04.2021
comment
Спасибо. Я отредактировал свой пост. Надеюсь это поможет. Я думаю, что $$ - это какая-то проблема для интерпретатора скриптов. Но это всего лишь предложение   -  person Thomas Lang    schedule 27.04.2021
comment
Юрий Царьков прав, ResourceDatabasePopulator разбивает ваш большой sql-скрипт на части с помощью разделителя ; и пытается выполнить их одну за другой. Это нарушает ваше объявление процедуры хранения. Взгляните на этот метод: docs.spring.io/spring-framework/docs/current/javadoc-api/org/   -  person yuppie-flu    schedule 28.04.2021
comment
По этому поводу уже есть еще один вопрос: stackoverflow.com/questions /35911065/ Итак, я попробую оба решения. Поместите файл в docker initdb и используйте double ;; как разделитель.   -  person Thomas Lang    schedule 29.04.2021
comment
Отвечает ли это на ваш вопрос? Сохраненная процедура Spring и MySQL   -  person Jens Schauder    schedule 03.05.2021
comment
Спасибо. Я выбрал решение со встроенным сценарием инициализации testcontainer. Работает идеально!   -  person Thomas Lang    schedule 06.05.2021

Ответы (3)


arrow_upward
1
arrow_downward

Поскольку вы используете postgresql, вы можете попробовать поместить сценарий инициализации в /docker-entrypoint-initdb.d, как описано здесь

person Gonzalo Muñoz    schedule 27.04.2021

arrow_upward
1
arrow_downward

Я не упомянул вышеприведенный bean-компонент DataSourceInitializer, так как testcontainers также предоставляют сценарий инициализации.
Поэтому мое решение заключается в правильной настройке этого сценария инициализации с помощью контейнера postgres testcontainer:

@Bean
        @Primary
        DataSource testDataSource() {

            if (POSTGRESQL_CONTAINER == null) {
                PostgreSQLContainer<?> container = new PostgreSQLContainer<>();
                container.withInitScript("schema.sql");
                container.start();
                POSTGRESQL_CONTAINER = container;
            }
            PGSimpleDataSource dataSource = new PGSimpleDataSource();
            dataSource.setUrl(POSTGRESQL_CONTAINER.getJdbcUrl());
            dataSource.setUser(POSTGRESQL_CONTAINER.getUsername());
            dataSource.setPassword(POSTGRESQL_CONTAINER.getPassword());

            return dataSource;


        }

container.withInitScript("schema.sql"); выполняет свою работу. Обратите внимание, что schema.sql должно быть помещено в .\src\test\resources

person Thomas Lang    schedule 30.04.2021

arrow_upward
0
arrow_downward

При добавлении второго триггера все пошло очень странно:

create or replace function check_registration_limits() returns trigger
    language plpgsql
as $$
BEGIN
    IF(SELECT count(*) FROM applicationusersubjectregistration ausr WHERE NEW.campus = ausr.campus and NEW.subject = ausr.subject)
        >= (SELECT s.maxparticipants FROM subject s WHERE s.subjectid = NEW.subject) THEN
        RAISE EXCEPTION 'Registration limits reached! No more voting allowed!';
    END IF;
    RETURN NEW;
END
$$;

Это приводит к другому Unterminated dollar quote, даже если я использую ту же нотацию с первым триггером, где все работает нормально.

Итак, нашел этот ответ здесь: https://stackoverflow.com/a/55596701/7320372

Моя проблема решена после использования ' в качестве замены $$ следующим образом:

create or replace function check_registration_limits() returns trigger
as
'
BEGIN
    IF (SELECT count(*)
        FROM applicationusersubjectregistration ausr
        WHERE NEW.campus = ausr.campus
          and NEW.subject = ausr.subject)
        >= (SELECT s.maxparticipants FROM subject s WHERE s.subjectid = NEW.subject) THEN
        RAISE EXCEPTION ''Registration limits reached! No more voting allowed!'';
    END IF;
    RETURN NEW;
END
' language plpgsql;
person Thomas Lang    schedule 01.06.2021