TL; DR version: I want to be able to use the Maven Mojo SQL Plugin to create / delete any given table in my database schema (or load data for these tables) by command via mvn . How?
I am a longtime Java developer, but for the most part I live in an ant based world. I like the strength and clarity of ant , and the control that it gives me in everything. However, in my new work there is an impetus to using maven . So I decided to study it using a project that I am working on at home.
One of the things that I created in another personal project is the ability to fully configure and tear down my Postgres database from ant on the command line. I can slice and slice any table, sequence and test integration data that I like, individually or in concert. Of course, this means that I have whole gadget ant targets, but it works very well. I like it; it has served me pretty well for many years.
Learning how to do this in Maven over the weekend, I found Mojo SQL Maven Plugin . After viewing the usage page (and I use this term freely, since it is just one semi-drive without explanation) and an example page , I was able to come up with some changes in my pom.xml file. I fixed some obvious typos in the example (postgressql) and referenced the PostgreSQL JDBC page to make sure the JDBC connection string is correct. I insert all pom.xml (modified to protect the perpetrators) below:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.mycompany.myapp</groupId> <artifactId>myapp</artifactId> <packaging>jar</packaging> <version>1.0-SNAPSHOT</version> <name>myapp</name> <url>http://maven.apache.org</url> <repositories> <repository> <id>JBoss</id> <url>https://repository.jboss.org/nexus/content/groups/public/</url> </repository> </repositories> <dependencies> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.10</version> <scope>test</scope> </dependency> <dependency> <groupId>org.hibernate</groupId> <artifactId>hibernate-core</artifactId> <version>4.0.0.CR7</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.codehaus.mojo</groupId> <artifactId>sql-maven-plugin</artifactId> <version>1.5</version> <dependencies> <dependency> <groupId>postgresql</groupId> <artifactId>postgresql</artifactId> <version>8.3-606.jdbc4</version> </dependency> </dependencies> <configuration> <driver>org.postgresql.Driver</driver> <url>jdbc:postgresql://localhost:5432/myapp</url> <settingsKey>myapp</settingsKey> <skip>${maven.test.skip}</skip> </configuration> <executions> <execution> <id>drop-db-before-test-if-any</id> <phase>process-test-resources</phase> <goals> <goal>execute</goal> </goals> <configuration> <url>jdbc:postgresql://localhost:5432/template1</url> <autocommit>true</autocommit> <sqlCommand>drop database myapp</sqlCommand> <onError>continue</onError> </configuration> </execution> <execution> <id>create-db</id> <phase>process-test-resources</phase> <goals> <goal>execute</goal> </goals> <configuration> <url>jdbc:postgresql://localhost:5432/template1</url> <autocommit>true</autocommit> <sqlCommand>create database myapp</sqlCommand> </configuration> </execution> <execution> <id>create-schema</id> <phase>process-test-resources</phase> <goals> <goal>execute</goal> </goals> <configuration> <autocommit>true</autocommit> <srcFiles> <srcFile>src/main/sql/create_person.sql</srcFile> </srcFiles> </configuration> </execution> <execution> <id>create-data</id> <phase>process-test-resources</phase> <goals> <goal>execute</goal> </goals> <configuration> <orderFile>ascending</orderFile> <fileset> <basedir>${basedir}</basedir> <includes> <include>src/test/sql/person_data.sql</include> </includes> </fileset> </configuration> </execution> <execution> <id>drop-db-after-test</id> <phase>test</phase> <goals> <goal>execute</goal> </goals> <configuration> <url>jdbc:postgresql://localhost:5432/template1</url> <autocommit>true</autocommit> <sqlCommand>drop database myapp</sqlCommand> </configuration> </execution> </executions> </plugin> </plugins> </build> </project>
Now, since I did not create the database, it does not appear in \l on the PG command line:
[ mike@mike myapp]$ psql template1 Welcome to psql 8.3.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit template1=
That way, when I run mvn sql:execute , I expect my database to be created ... Or at least not a crash in the drop-db-before-test-if-any task, as it will continue on error. But of course:
[ mike@mike myapp]$ mvn sql:execute [INFO] Scanning for projects... [INFO] [INFO] ------------------------------------------------------------------------ [INFO] Building myapp 1.0-SNAPSHOT [INFO] ------------------------------------------------------------------------ [INFO] [INFO] --- sql-maven-plugin:1.5:execute (default-cli) @ myapp --- [INFO] ------------------------------------------------------------------------ [INFO] BUILD FAILURE [INFO] ------------------------------------------------------------------------ [INFO] Total time: 1.667s [INFO] Finished at: Mon Dec 05 20:22:17 CST 2011 [INFO] Final Memory: 3M/81M [INFO] ------------------------------------------------------------------------ [ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project myapp: FATAL: database "myapp" does not exist -> [Help 1] [ERROR] [ERROR] To see the full stack trace of the errors, re-run Maven with the -e switch. [ERROR] Re-run Maven using the -X switch to enable full debug logging. [ERROR] [ERROR] For more information about the errors and possible solutions, please read the following articles: [ERROR] [Help 1] http:
The error page shown on the last line does not help; it just tells me that the plugin caused the error, not Maven itself.
So, launch it with the -X switch. I will just post the interesting part of the error:
[ERROR] Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project myapp: FATAL: database "myapp" does not exist -> [Help 1] org.apache.maven.lifecycle.LifecycleExecutionException: Failed to execute goal org.codehaus.mojo:sql-maven-plugin:1.5:execute (default-cli) on project myapp: FATAL: database "myapp" does not exist at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:217) at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:153) at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:145) at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:84) at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject(LifecycleModuleBuilder.java:59) at org.apache.maven.lifecycle.internal.LifecycleStarter.singleThreadedBuild(LifecycleStarter.java:183) at org.apache.maven.lifecycle.internal.LifecycleStarter.execute(LifecycleStarter.java:161) at org.apache.maven.DefaultMaven.doExecute(DefaultMaven.java:319) at org.apache.maven.DefaultMaven.execute(DefaultMaven.java:156) at org.apache.maven.cli.MavenCli.execute(MavenCli.java:537) at org.apache.maven.cli.MavenCli.doMain(MavenCli.java:196) at org.apache.maven.cli.MavenCli.main(MavenCli.java:141) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.codehaus.plexus.classworlds.launcher.Launcher.launchEnhanced(Launcher.java:290) at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:230) at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:409) at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:352) Caused by: org.apache.maven.plugin.MojoExecutionException: FATAL: database "myapp" does not exist at org.codehaus.mojo.sql.SqlExecMojo.execute(SqlExecMojo.java:618) at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo(DefaultBuildPluginManager.java:101) at org.apache.maven.lifecycle.internal.MojoExecutor.execute(MojoExecutor.java:209) ... 19 more Caused by: org.postgresql.util.PSQLException: FATAL: database "myapp" does not exist at org.postgresql.core.v3.ConnectionFactoryImpl.readStartupMessages(ConnectionFactoryImpl.java:444) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:99) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66) at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:124) at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30) at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:29) at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24) at org.postgresql.Driver.makeConnection(Driver.java:386) at org.postgresql.Driver.connect(Driver.java:260) at org.codehaus.mojo.sql.SqlExecMojo.getConnection(SqlExecMojo.java:899) at org.codehaus.mojo.sql.SqlExecMojo.execute(SqlExecMojo.java:612) ... 21 more
But, but, but ... <onError>continue</onError> !
So, to the questions:
- What am I doing wrong? Are these my expectations or my code?
- You will notice that I have a
create-person.sql . I know from examples that I can have several files, for example create-address.sql . But in ant , I have the opportunity to create the address table separately from the person table while I perform ant tasks, keeping in mind the order of referential integrity. Is this possible with maven ? If so, how?
Sorry for the verbosity and in advance for your help.