Allow to reference/import additional sql scripts inside an sql script with something like PostgreSQL psql "\i" command. This allows for more granularity/reuse of multiple sql scripts.

For example:

agents.sql

-- insert agents
INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore');
INSERT INTO AGENTS VALUES ('A003', 'Alex ', 'London');
INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York');

customers.sql

-- Import agents.sql
\i agents.sql

-- insert customers with references to agents
INSERT INTO CUSTOMER VALUES ('C00013', 'Holmes', 'London', 'A003');
INSERT INTO CUSTOMER VALUES ('C00001', 'Micheal', 'New York', 'A008');
INSERT INTO CUSTOMER VALUES ('C00020', 'Albert', 'New York', 'A008');
INSERT INTO CUSTOMER VALUES ('C00025', 'Ravindran', 'Bangalore', 'A007');

Then you could populate your test database referencing only customers.sql that imports agents.sql (that could be referenced/imported in other scripts too).

Basically the suggested change should be:

  • Add property to ResourceDatabasePopulator: private String importPrefix = ScriptUtils.DEFAULT_IMPORT_PREFIX;
  • Add constant to ScriptUtils: public static final String DEFAULT_IMPORT_PREFIX = "\\\\i";
  • Refactor splitSqlScript from ScriptUtils so when a new line with importPrefix is found the referenced script is read and parsed (could be a "relative path" or support spring resource prefixes...)

Comment From: rubensa

For the record: In my use case I have multiple @SpringBootTest and each @Test method is annotated with @Sql in a "complex" database schema.

I'd like to define multiple script each one to insert lists of elements of single entity and be able to import the dependent scripts that insert the referenced entities lists (same script should be re-used in many script, as the schema is a bit complicated). This allows to "not repeat your self" writing the same inserts command multiple times in multiple files and import only the relevant sql script in the test (I can import all the dependent script once after other, but for that I must inspect the content of the script to analyze dependencies recursively to know with other scripts are needed and the list could the huge - with this solution, the dependencies are "auto recursively solved")

Comment From: sluk3r

please assign this issue to me? @sbrannen

Comment From: snicoll

Thanks for the suggestion, but we'd like our parser to remain manageable. I understand that advanced parses, such as the one from postgres, can provide additional features but it's not an end goal for ours.

Comment From: rubensa

Disappointed to see the proposal rejected almost three years later.