sis-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From desruisse...@apache.org
Subject [sis] 02/02: Regroup the creation of temporary database or temporary schema in a single "TestDatabase" class. First draft of a SQLStoreTest class using this mechanism for testing on PostgreSQL.
Date Tue, 10 Jul 2018 16:12:22 GMT
This is an automated email from the ASF dual-hosted git repository.

desruisseaux pushed a commit to branch geoapi-4.0
in repository https://gitbox.apache.org/repos/asf/sis.git

commit f07593e775e2caadaf3808a240df6a3079c132e3
Author: Martin Desruisseaux <martin.desruisseaux@geomatys.com>
AuthorDate: Tue Jul 10 18:10:32 2018 +0200

    Regroup the creation of temporary database or temporary schema in a single "TestDatabase"
class.
    First draft of a SQLStoreTest class using this mechanism for testing on PostgreSQL.
---
 core/sis-metadata/pom.xml                          |   5 +
 .../sis/internal/metadata/sql/ScriptRunner.java    |   4 +-
 .../internal/metadata/sql/ScriptRunnerTest.java    |  10 +-
 .../sis/metadata/sql/IdentifierGeneratorTest.java  |  11 +-
 .../sis/metadata/sql/MetadataSourceTest.java       |  10 +-
 .../sis/metadata/sql/MetadataWriterTest.java       |  47 +++---
 .../java/org/apache/sis/test/sql/TestDatabase.java | 176 +++++++++++++++++----
 .../referencing/factory/sql/EPSGInstallerTest.java |  38 ++---
 .../factory/sql/epsg/DataScriptFormatter.java      |   8 +-
 ide-project/NetBeans/build.xml                     |   3 +
 ide-project/NetBeans/nbproject/project.properties  |   1 -
 storage/sis-sql/pom.xml                            |   7 +
 .../org/apache/sis/storage/sql/SQLStoreTest.java   |  49 ++++++
 .../org/apache/sis/storage/sql/Features.sql        |  68 ++++++++
 14 files changed, 327 insertions(+), 110 deletions(-)

diff --git a/core/sis-metadata/pom.xml b/core/sis-metadata/pom.xml
index 37b400d..29e89d0 100644
--- a/core/sis-metadata/pom.xml
+++ b/core/sis-metadata/pom.xml
@@ -164,6 +164,11 @@
       <scope>test</scope>
     </dependency>
     <dependency>
+      <groupId>org.hsqldb</groupId>
+      <artifactId>hsqldb</artifactId>
+      <scope>test</scope>
+    </dependency>
+    <dependency>
       <groupId>org.postgresql</groupId>
       <artifactId>postgresql</artifactId>
       <scope>test</scope>
diff --git a/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/ScriptRunner.java
b/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/ScriptRunner.java
index 326fa3e..963178e 100644
--- a/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/ScriptRunner.java
+++ b/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/ScriptRunner.java
@@ -281,7 +281,7 @@ public class ScriptRunner implements AutoCloseable {
      * @param  maxRowsPerInsert  maximum number of rows per {@code "INSERT INTO"} statement.
      * @throws SQLException if an error occurred while creating a SQL statement.
      */
-    protected ScriptRunner(final Connection connection, int maxRowsPerInsert) throws SQLException
{
+    public ScriptRunner(final Connection connection, int maxRowsPerInsert) throws SQLException
{
         ArgumentChecks.ensureNonNull("connection", connection);
         ArgumentChecks.ensurePositive("maxRowsPerInsert", maxRowsPerInsert);
         final DatabaseMetaData metadata = connection.getMetaData();
@@ -456,7 +456,7 @@ public class ScriptRunner implements AutoCloseable {
      * @param  loader    the class to use for loading the SQL script.
      * @param  filename  the SQL script filename, relative to the {@code loader} package.
      * @return the number of rows added or modified as a result of the statement execution.
-     * @throws IOException if an error occurred while reading the input (should never happen).
+     * @throws IOException if an error occurred while reading the input.
      * @throws SQLException if an error occurred while executing a SQL statement.
      */
     public final int run(final Class<?> loader, final String filename) throws IOException,
SQLException {
diff --git a/core/sis-metadata/src/test/java/org/apache/sis/internal/metadata/sql/ScriptRunnerTest.java
b/core/sis-metadata/src/test/java/org/apache/sis/internal/metadata/sql/ScriptRunnerTest.java
index 57421ed..cd64463 100644
--- a/core/sis-metadata/src/test/java/org/apache/sis/internal/metadata/sql/ScriptRunnerTest.java
+++ b/core/sis-metadata/src/test/java/org/apache/sis/internal/metadata/sql/ScriptRunnerTest.java
@@ -18,7 +18,6 @@ package org.apache.sis.internal.metadata.sql;
 
 import java.sql.Connection;
 import java.sql.SQLException;
-import javax.sql.DataSource;
 import org.apache.sis.test.TestCase;
 import org.apache.sis.test.TestStep;
 import org.apache.sis.test.sql.TestDatabase;
@@ -31,7 +30,7 @@ import static org.junit.Assert.*;
  * Tests {@link ScriptRunner}.
  *
  * @author  Martin Desruisseaux (Geomatys)
- * @version 0.8
+ * @version 1.0
  * @since   0.8
  * @module
  */
@@ -44,13 +43,12 @@ public final strictfp class ScriptRunnerTest extends TestCase {
      */
     @Test
     public void testOnDerby() throws SQLException {
-        final DataSource ds = TestDatabase.create("ScriptRunner");
-        try (Connection c = ds.getConnection()) {
+        try (TestDatabase db = TestDatabase.create("ScriptRunner");
+             Connection c = db.source.getConnection())
+        {
             final ScriptRunner sr = new ScriptRunner(c, 3);
             testSupportedFlags(sr);
             testRegularExpressions(sr);
-        } finally {
-            TestDatabase.drop(ds);
         }
     }
 
diff --git a/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/IdentifierGeneratorTest.java
b/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/IdentifierGeneratorTest.java
index 7759826..cfe57b1 100644
--- a/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/IdentifierGeneratorTest.java
+++ b/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/IdentifierGeneratorTest.java
@@ -18,7 +18,6 @@ package org.apache.sis.metadata.sql;
 
 import java.sql.Statement;
 import java.sql.SQLException;
-import javax.sql.DataSource;
 import org.apache.sis.internal.metadata.sql.SQLBuilder;
 import org.apache.sis.test.sql.TestDatabase;
 import org.apache.sis.metadata.MetadataStandard;
@@ -32,7 +31,7 @@ import static org.junit.Assert.*;
  * Creates an empty database and insert automatically-generated keys.
  *
  * @author  Martin Desruisseaux (Geomatys)
- * @version 0.8
+ * @version 1.0
  * @since   0.8
  * @module
  */
@@ -59,9 +58,9 @@ public final strictfp class IdentifierGeneratorTest extends TestCase {
      */
     @Test
     public void testSequence() throws Exception {
-        final DataSource ds = TestDatabase.create("IdentifierGenerator");
-        try {
-            final MetadataSource source = new MetadataSource(MetadataStandard.ISO_19115,
ds, null, null);
+        try (TestDatabase db = TestDatabase.create("IdentifierGenerator");
+             MetadataSource source = new MetadataSource(MetadataStandard.ISO_19115, db.source,
null, null))
+        {
             synchronized (source) {
                 stmt = source.connection().createStatement();
                 stmt.executeUpdate("CREATE TABLE \"" + TABLE + "\" (ID VARCHAR(6) NOT NULL
PRIMARY KEY)");
@@ -81,8 +80,6 @@ public final strictfp class IdentifierGeneratorTest extends TestCase {
                 generator.close();
                 source.close();
             }
-        } finally {
-            TestDatabase.drop(ds);
         }
     }
 
diff --git a/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/MetadataSourceTest.java
b/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/MetadataSourceTest.java
index 9bff85b..e8697b6 100644
--- a/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/MetadataSourceTest.java
+++ b/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/MetadataSourceTest.java
@@ -17,7 +17,6 @@
 package org.apache.sis.metadata.sql;
 
 import java.util.Collections;
-import javax.sql.DataSource;
 import org.opengis.metadata.citation.Citation;
 import org.opengis.metadata.distribution.Format;
 import org.apache.sis.metadata.MetadataStandard;
@@ -38,7 +37,7 @@ import static org.apache.sis.test.TestUtilities.getSingleton;
  * Tests {@link MetadataSource}.
  *
  * @author  Martin Desruisseaux (Geomatys)
- * @version 0.8
+ * @version 1.0
  * @since   0.8
  * @module
  */
@@ -52,13 +51,12 @@ public final strictfp class MetadataSourceTest extends TestCase {
      */
     @Test
     public void testOnDerby() throws Exception {
-        final DataSource ds = TestDatabase.create("MetadataSource");
-        try (MetadataSource source = new MetadataSource(MetadataStandard.ISO_19115, ds, "metadata",
null)) {
+        try (TestDatabase db = TestDatabase.create("MetadataSource");
+             MetadataSource source = new MetadataSource(MetadataStandard.ISO_19115, db.source,
"metadata", null))
+        {
             source.install();
             verifyFormats(source);
             testSearch(source);
-        } finally {
-            TestDatabase.drop(ds);
         }
     }
 
diff --git a/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/MetadataWriterTest.java
b/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/MetadataWriterTest.java
index b2e49a5..ed867e1 100644
--- a/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/MetadataWriterTest.java
+++ b/core/sis-metadata/src/test/java/org/apache/sis/metadata/sql/MetadataWriterTest.java
@@ -17,8 +17,6 @@
 package org.apache.sis.metadata.sql;
 
 import java.util.Collections;
-import javax.sql.DataSource;
-import org.postgresql.ds.PGSimpleDataSource;
 import org.opengis.metadata.citation.Contact;
 import org.opengis.metadata.citation.Citation;
 import org.opengis.metadata.citation.PresentationForm;
@@ -33,7 +31,6 @@ import org.apache.sis.metadata.MetadataStandard;
 import org.apache.sis.test.TestUtilities;
 import org.apache.sis.test.TestCase;
 import org.apache.sis.test.DependsOn;
-import org.junit.Ignore;
 import org.junit.Test;
 
 import static org.junit.Assert.*;
@@ -47,7 +44,7 @@ import org.opengis.metadata.citation.Responsibility;
  * Creates a metadata database, stores a few elements and read them back.
  *
  * @author  Martin Desruisseaux (Geomatys)
- * @version 0.8
+ * @version 1.0
  * @since   0.8
  * @module
  */
@@ -68,16 +65,16 @@ public final strictfp class MetadataWriterTest extends TestCase {
      */
     @Test
     public void testDerby() throws Exception {
-        final DataSource ds = TestDatabase.create("MetadataWriter");
-        source = new MetadataWriter(MetadataStandard.ISO_19115, ds, null, null);
-        try {
-            write();
-            search();
-            read();
-            readWriteDeprecated();
-            source.close();
-        } finally {
-            TestDatabase.drop(ds);
+        try (final TestDatabase db = TestDatabase.create("MetadataWriter")) {
+            source = new MetadataWriter(MetadataStandard.ISO_19115, db.source, null, null);
+            try {
+                write();
+                search();
+                read();
+                readWriteDeprecated();
+            } finally {
+                source.close();
+            }
         }
     }
 
@@ -88,19 +85,17 @@ public final strictfp class MetadataWriterTest extends TestCase {
      * @throws Exception if an error occurred while writing or reading the database.
      */
     @Test
-    @Ignore("This test need to be run manually on a machine having a local PostgreSQL database.")
     public void testPostgreSQL() throws Exception {
-        final PGSimpleDataSource ds = new PGSimpleDataSource();
-        ds.setServerName("localhost");
-        ds.setDatabaseName("SpatialMetadataTest");
-        source = new MetadataWriter(MetadataStandard.ISO_19115, ds, "metadata", null);
-        try {
-            write();
-            search();
-            read();
-            readWriteDeprecated();
-        } finally {
-            source.close();
+        try (final TestDatabase db = TestDatabase.createOnPostgreSQL("MetadataWriter", true))
{
+            source = new MetadataWriter(MetadataStandard.ISO_19115, db.source, "MetadataWriter",
null);
+            try {
+                write();
+                search();
+                read();
+                readWriteDeprecated();
+            } finally {
+                source.close();
+            }
         }
     }
 
diff --git a/core/sis-metadata/src/test/java/org/apache/sis/test/sql/TestDatabase.java b/core/sis-metadata/src/test/java/org/apache/sis/test/sql/TestDatabase.java
index 2feb808..ee1464b 100644
--- a/core/sis-metadata/src/test/java/org/apache/sis/test/sql/TestDatabase.java
+++ b/core/sis-metadata/src/test/java/org/apache/sis/test/sql/TestDatabase.java
@@ -16,17 +16,28 @@
  */
 package org.apache.sis.test.sql;
 
+import java.io.IOException;
 import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.Statement;
+import java.sql.ResultSet;
 import java.sql.SQLException;
-import java.sql.SQLFeatureNotSupportedException;
+import java.sql.SQLDataException;
+import org.postgresql.PGProperty;
+import org.postgresql.ds.PGSimpleDataSource;
+import org.hsqldb.jdbc.JDBCDataSource;
 import org.apache.derby.jdbc.EmbeddedDataSource;
 import org.apache.sis.internal.metadata.sql.Initializer;
+import org.apache.sis.internal.metadata.sql.ScriptRunner;
+import org.apache.sis.test.TestCase;
 import org.apache.sis.util.Debug;
 
+import static org.junit.Assume.*;
+
 
 /**
  * Utility methods for creating temporary databases for testing purpose.
- * The databases are in-memory only when the database supports this mode.
+ * The databases are in-memory when the database engine supports this mode.
  *
  * <div class="section">Inspecting the Derby database content in a debugger</div>
  * Make sure that the classpath contains the {@code derbynet.jar} file in addition to {@code
derby.jar}.
@@ -56,7 +67,7 @@ import org.apache.sis.util.Debug;
  * @since   0.7
  * @module
  */
-public final strictfp class TestDatabase {
+public strictfp class TestDatabase implements AutoCloseable {
     /**
      * Data source to an alternative database to use for testing purpose.
      * If {@code null}, an in-memory Derby database will be used.
@@ -69,53 +80,160 @@ public final strictfp class TestDatabase {
     private static final DataSource TEST_DATABASE = null;
 
     /**
-     * Do not allow (for now) instantiation of this class.
+     * Name of the database to use for testing purpose. This is used only when running tests
on database engine
+     * that do not support in-memory database, like PostgreSQL.
      */
-    private TestDatabase() {
+    private static final String NAME = "SpatialMetadataTest";
+
+    /**
+     * The data source for the test database.
+     */
+    public final DataSource source;
+
+    /**
+     * Creates a new test database for the given data source.
+     */
+    private TestDatabase(final DataSource source) {
+        this.source = source;
     }
 
     /**
-     * Creates a Derby database in memory. If no Derby driver is not found,
-     * then the test will be interrupted by an {@code org.junit.Assume} statement.
+     * Creates a temporary database. This method creates a Derby in-memory database by default,
+     * but this default can be changed by setting the {@link #TEST_DATABASE} hard-coded value.
      *
      * @param  name  the database name (without {@code "memory:"} prefix).
-     * @return the data source.
+     * @return connection to the test database (usually on Apache Derby).
      * @throws SQLException if an error occurred while creating the database.
      */
-    public static DataSource create(final String name) throws SQLException {
+    public static TestDatabase create(final String name) throws SQLException {
         if (TEST_DATABASE != null) {
-            return TEST_DATABASE;
+            return new TestDatabase(TEST_DATABASE);
         }
         final EmbeddedDataSource ds = new EmbeddedDataSource();
         ds.setDatabaseName("memory:" + name);
-        ds.setDataSourceName("Apache SIS spatial metadata");
+        ds.setDataSourceName("Apache SIS test database");
         ds.setCreateDatabase("create");
-        return ds;
+        return new TestDatabase(ds) {
+            @Override public void close() throws SQLException {
+                final EmbeddedDataSource ds = (EmbeddedDataSource) source;
+                ds.setCreateDatabase("no");
+                ds.setConnectionAttributes("drop=true");
+                try {
+                    ds.getConnection().close();
+                } catch (SQLException e) {                          // This is the expected
exception.
+                    if (!Initializer.isSuccessfulShutdown(e)) {
+                        throw e;
+                    }
+                }
+            }
+        };
     }
 
     /**
-     * Drops an in-memory Derby database after usage.
+     * Creates a in-memory database on HSQLDB.
      *
-     * @param  ds  the data source created by {@link #create(String)}.
-     * @throws SQLException if an error occurred while dropping the database.
+     * @param  name  the database name (without {@code "jdbc:hsqldb:mem:"} prefix).
+     * @return connection to the test database.
+     * @throws SQLException if an error occurred while creating the database.
+     *
+     * @since 1.0
      */
-    public static void drop(final DataSource ds) throws SQLException {
-        if (ds == TEST_DATABASE) {
-            return;
+    public static TestDatabase createOnHSQLDB(final String name) throws SQLException {
+        final JDBCDataSource ds = new JDBCDataSource();
+        ds.setDatabaseName("Apache SIS test database");
+        ds.setURL("jdbc:hsqldb:mem:".concat(name));
+        return new TestDatabase(ds) {
+            @Override public void close() throws SQLException {
+                try (Connection c = ds.getConnection(); Statement s = c.createStatement())
{
+                    s.execute("SHUTDOWN");
+                }
+            }
+        };
+    }
+
+    /**
+     * Creates a connection to an existing PostgreSQL database.
+     * This method returns only if all the following conditions are true:
+     *
+     * <ol>
+     *   <li>{@link TestCase#RUN_EXTENSIVE_TESTS} is {@code true} (for reducing the
risk of messing with user installation).</li>
+     *   <li>A PostgreSQL server is running on the local host and listening to the
default port.</li>
+     *   <li>A database named {@value #NAME} exists.</li>
+     *   <li>The database does not contain any schema of the given name.</li>
+     * </ol>
+     *
+     * If the {@code create} argument is {@code false}, then the callers is responsible for
creating the schema
+     * soon after this method call. That schema will be deleted by {@link #close()}.
+     *
+     * @param  schema  temporary schema to create. Shall not contain {@code '_'} or {@code
'%'} characters.
+     * @param  create  whether the schema should be created by this method.
+     * @return connection to a PostgreSQL database
+     * @throws SQLException if an error occurred while connecting to the database or creating
the schema.
+     *
+     * @since 1.0
+     */
+    public static TestDatabase createOnPostgreSQL(final String schema, final boolean create)
throws SQLException {
+        assumeTrue("Extensive tests not enabled.", TestCase.RUN_EXTENSIVE_TESTS);
+        final PGSimpleDataSource ds = new PGSimpleDataSource();
+        ds.setServerName("localhost");
+        ds.setDatabaseName(NAME);
+        ds.setApplicationName("Apache SIS test database");
+        ds.setCurrentSchema(schema);
+        ds.setProperty(PGProperty.LOGGER_LEVEL, "OFF");   // For avoiding warning when no
PostgreSQL server is running.
+        /*
+         * Current version does not use pooling on the assumption
+         * that connections to local host are fast enough.
+         */
+        try (Connection c = ds.getConnection()) {
+            try (ResultSet reflect = c.getMetaData().getSchemas(null, schema)) {
+                if (reflect.next()) {
+                    throw new SQLDataException("Schema \"" + schema + "\" already exists
in \"" + NAME + "\".");
+                }
+            }
+            if (create) {
+                try (Statement s = c.createStatement()) {
+                    s.execute("CREATE SCHEMA \"" + schema + '"');
+                }
+            }
+        } catch (SQLException e) {
+            final String state = e.getSQLState();
+            assumeFalse("This test needs a PostgreSQL server running on the local host.",
"08001".equals(state));
+            assumeFalse("This test needs a PostgreSQL database named \"" + NAME + "\".",
 "3D000".equals(state));
+            throw e;
         }
-        if (ds instanceof EmbeddedDataSource) {
-            final EmbeddedDataSource db = (EmbeddedDataSource) ds;
-            db.setCreateDatabase("no");
-            db.setConnectionAttributes("drop=true");
-            try {
-                ds.getConnection().close();
-            } catch (SQLException e) {                          // This is the expected exception.
-                if (!Initializer.isSuccessfulShutdown(e)) {
-                    throw e;
+        return new TestDatabase(ds) {
+            @Override public void close() throws SQLException {
+                final PGSimpleDataSource ds = (PGSimpleDataSource) source;
+                try (Connection c = ds.getConnection()) {
+                    try (Statement s = c.createStatement()) {
+                        s.execute("DROP SCHEMA \"" + ds.getCurrentSchema() + "\" CASCADE");
+                    }
                 }
             }
-        } else {
-            throw new SQLFeatureNotSupportedException("Unknown data source: " + ds.getClass());
+        };
+    }
+
+    /**
+     * Executes the SQL statements in the given resource file.
+     *
+     * @param loader     a class in the package of the resource file. This is usually the
test class.
+     * @param queryFile  name of the SQL file to load and execute.
+     * @throws IOException if an error occurred while reading the input.
+     * @throws SQLException if an error occurred while executing a SQL statement.
+     */
+    public void executeSQL(final Class<?> loader, final String queryFile) throws IOException,
SQLException {
+        try (Connection c = source.getConnection(); ScriptRunner r = new ScriptRunner(c,
1000)) {
+            r.run(loader, queryFile);
         }
     }
+
+    /**
+     * Drops the test schema (PostgreSQL) or the test database (Derby) after usage.
+     *
+     * @throws SQLException if an error occurred while dropping the test data.
+     */
+    @Override
+    public void close() throws SQLException {
+        // To be overriden by anonymous classes.
+    }
 }
diff --git a/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/EPSGInstallerTest.java
b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/EPSGInstallerTest.java
index 223c392..8fdf1b3 100644
--- a/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/EPSGInstallerTest.java
+++ b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/EPSGInstallerTest.java
@@ -24,11 +24,8 @@ import java.util.regex.Pattern;
 import java.io.IOException;
 import javax.sql.DataSource;
 import java.sql.Connection;
-import java.sql.Statement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
-import org.hsqldb.jdbc.JDBCDataSource;
-import org.postgresql.ds.PGSimpleDataSource;
 import org.opengis.util.FactoryException;
 import org.opengis.referencing.crs.GeographicCRS;
 import org.opengis.referencing.crs.ProjectedCRS;
@@ -45,7 +42,6 @@ import org.apache.sis.test.LoggingWatcher;
 import org.apache.sis.test.DependsOn;
 import org.apache.sis.test.TestCase;
 import org.junit.After;
-import org.junit.Ignore;
 import org.junit.Rule;
 import org.junit.Test;
 
@@ -62,7 +58,7 @@ import static org.junit.Assume.assumeTrue;
  * This class does not write anything to disk (except maybe some temporary files).</p>
  *
  * @author  Martin Desruisseaux (Geomatys)
- * @version 0.8
+ * @version 1.0
  * @since   0.7
  * @module
  */
@@ -132,11 +128,8 @@ public final strictfp class EPSGInstallerTest extends TestCase {
     @Test
     public void testCreationOnDerby() throws Exception {
         final InstallationScriptProvider scripts = getScripts();            // Needs to be
invoked first.
-        final DataSource ds = TestDatabase.create("EPSGInstaller");
-        try {
-            createAndTest(ds, scripts);
-        } finally {
-            TestDatabase.drop(ds);
+        try (TestDatabase db = TestDatabase.create("EPSGInstaller")) {
+            createAndTest(db.source, scripts);
         }
         loggings.assertNextLogContains("EPSG", "jdbc:derby:memory:EPSGInstaller");
         loggings.assertNoUnexpectedLog();
@@ -151,38 +144,27 @@ public final strictfp class EPSGInstallerTest extends TestCase {
     @Test
     public void testCreationOnHSQLDB() throws Exception {
         final InstallationScriptProvider scripts = getScripts();            // Needs to be
invoked first.
-        final JDBCDataSource ds = new JDBCDataSource();
-        ds.setURL("jdbc:hsqldb:mem:EPSGInstaller");
-        try {
-            createAndTest(ds, scripts);
-        } finally {
-            try (Connection c = ds.getConnection(); Statement s = c.createStatement()) {
-                s.execute("SHUTDOWN");
-            }
+        try (TestDatabase db = TestDatabase.createOnHSQLDB("EPSGInstaller")) {
+            createAndTest(db.source, scripts);
         }
         loggings.assertNextLogContains("EPSG", "jdbc:hsqldb:mem:EPSGInstaller");
         loggings.assertNoUnexpectedLog();
     }
 
     /**
-     * Tests the creation of an EPSG database on PostgreSQL. This test is disabled by default.
-     * To run this test, the tester needs to launch on {@code "localhost"} a PostgreSQL server
-     * having an empty database named {@code "SpatialMetadataTest"}. After the test completion,
-     * one can verify the {@code "EPSG"} schema created by the test, then delete that schema
for future test executions
-     * (this test does <strong>not</strong> delete by itself the schema that
it created).
+     * Tests the creation of an EPSG database on PostgreSQL. This test requires a PostgreSQL
server
+     * running on {@code "localhost"} with an empty database named {@code "SpatialMetadataTest"}.
      *
      * @throws Exception if an error occurred while creating the database.
      *
      * @since 0.8
      */
     @Test
-    @Ignore("This test need to be run manually on a machine having a local PostgreSQL database.")
     public void testCreationOnPostgreSQL() throws Exception {
         final InstallationScriptProvider scripts = getScripts();            // Needs to be
invoked first.
-        final PGSimpleDataSource ds = new PGSimpleDataSource();
-        ds.setServerName("localhost");
-        ds.setDatabaseName("SpatialMetadataTest");
-        createAndTest(ds, scripts);
+        try (TestDatabase db = TestDatabase.createOnPostgreSQL("EPSG", false)) {
+            createAndTest(db.source, scripts);
+        }
         loggings.assertNextLogContains("EPSG", "jdbc:postgresql://localhost/SpatialMetadataTest");
         loggings.assertNoUnexpectedLog();
     }
diff --git a/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/epsg/DataScriptFormatter.java
b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/epsg/DataScriptFormatter.java
index 5161196..82d2dc6 100644
--- a/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/epsg/DataScriptFormatter.java
+++ b/core/sis-referencing/src/test/java/org/apache/sis/referencing/factory/sql/epsg/DataScriptFormatter.java
@@ -30,7 +30,6 @@ import java.io.IOException;
 import java.nio.charset.StandardCharsets;
 import java.sql.Connection;
 import java.sql.SQLException;
-import javax.sql.DataSource;
 import java.util.regex.Matcher;
 import java.util.regex.Pattern;
 import org.apache.sis.util.Workaround;
@@ -74,12 +73,11 @@ public final class DataScriptFormatter extends ScriptRunner {
             System.err.println("Expected two arguments: source SQL file and target SQL file.");
             return;
         }
-        final DataSource ds = TestDatabase.create("dummy");
-        try (Connection c = ds.getConnection()) {
+        try (TestDatabase db = TestDatabase.create("dummy");
+             Connection c = db.source.getConnection())
+        {
             final DataScriptFormatter f = new DataScriptFormatter(c);
             f.run(new File(arguments[0]), new File(arguments[1]));
-        } finally {
-            TestDatabase.drop(ds);
         }
     }
 
diff --git a/ide-project/NetBeans/build.xml b/ide-project/NetBeans/build.xml
index f16fe03..64e5de0 100644
--- a/ide-project/NetBeans/build.xml
+++ b/ide-project/NetBeans/build.xml
@@ -248,6 +248,9 @@
         <include name="**/*.gpx"/>
         <include name="**/*.xml"/>
       </fileset>
+      <fileset dir="${project.root}/storage/sis-sql/src/test/resources">
+        <include name="**/*.sql"/>
+      </fileset>
       <fileset dir="${project.root}/storage/sis-earth-observation/src/test/resources">
         <include name="**/*.txt"/>
       </fileset>
diff --git a/ide-project/NetBeans/nbproject/project.properties b/ide-project/NetBeans/nbproject/project.properties
index e3f775c..930d36b 100644
--- a/ide-project/NetBeans/nbproject/project.properties
+++ b/ide-project/NetBeans/nbproject/project.properties
@@ -140,7 +140,6 @@ javac.classpath=\
     ${maven.repository}/javax/javaee-api/${jee.version}/javaee-api-${jee.version}.jar:\
     ${maven.repository}/edu/ucar/cdm/${netcdf.version}/cdm-${netcdf.version}.jar:\
     ${maven.repository}/org/osgi/org.osgi.core/${osgi.version}/org.osgi.core-${osgi.version}.jar:\
-    ${maven.repository}/org/postgresql/postgresql/${postgresql.version}/postgresql-${postgresql.version}.jar:\
     ${maven.repository}/com/googlecode/jaxb-namespaceprefixmapper-interfaces/JAXBNamespacePrefixMapper/${jaxb-ns-mapper}/JAXBNamespacePrefixMapper-${jaxb-ns-mapper}.jar
 javac.processorpath=\
     ${javac.classpath}
diff --git a/storage/sis-sql/pom.xml b/storage/sis-sql/pom.xml
index e7e1cb1..aba863a 100644
--- a/storage/sis-sql/pom.xml
+++ b/storage/sis-sql/pom.xml
@@ -111,6 +111,13 @@
       <version>${project.version}</version>
     </dependency>
     <dependency>
+      <groupId>org.apache.sis.core</groupId>
+      <artifactId>sis-metadata</artifactId>
+      <version>${project.version}</version>
+      <type>test-jar</type>
+      <scope>test</scope>
+    </dependency>
+    <dependency>
       <groupId>org.postgresql</groupId>
       <artifactId>postgresql</artifactId>
       <scope>test</scope>
diff --git a/storage/sis-sql/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java b/storage/sis-sql/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java
new file mode 100644
index 0000000..a9edca5
--- /dev/null
+++ b/storage/sis-sql/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java
@@ -0,0 +1,49 @@
+/*
+ * 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
+ *
+ *     http://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.sis.storage.sql;
+
+import java.sql.Connection;
+import org.apache.sis.internal.sql.feature.Database;
+import org.apache.sis.test.TestCase;
+import org.apache.sis.test.sql.TestDatabase;
+import org.junit.Test;
+
+
+/**
+ * Tests {@link SQLStore}.
+ *
+ * @author  Martin Desruisseaux (Geomatys)
+ * @version 1.0
+ * @since   1.0
+ * @module
+ */
+public final strictfp class SQLStoreTest extends TestCase {
+    /**
+     * Tests reading an existing schema. The schema is created and populated by the {@code
Features.sql} script.
+     *
+     * @throws Exception if an error occurred while testing the database.
+     */
+    @Test
+    public void testReadStructure() throws Exception {
+        try (TestDatabase tmp = TestDatabase.createOnPostgreSQL("features", true)) {
+            tmp.executeSQL(SQLStoreTest.class, "Features.sql");
+            try (Connection c = tmp.source.getConnection()) {
+                final Database db = new Database(null, c, null, "features", new String[]
{"Cities"});
+            }
+        }
+    }
+}
diff --git a/storage/sis-sql/src/test/resources/org/apache/sis/storage/sql/Features.sql b/storage/sis-sql/src/test/resources/org/apache/sis/storage/sql/Features.sql
new file mode 100644
index 0000000..1c8adab
--- /dev/null
+++ b/storage/sis-sql/src/test/resources/org/apache/sis/storage/sql/Features.sql
@@ -0,0 +1,68 @@
+
+-- 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.
+
+
+-- Create a temporary database on PostgreSQL for testing SQL store.
+-- The main table is "Cities", with associations to two other tables:
+--
+--   "Countries" through imported keys ("Cities" references "Countries")
+--   "Parks" through exported keys ("Cities" is referenced by "Parks").
+
+CREATE TABLE features."Countries" (
+    code           CHARACTER(3)          NOT NULL,
+    "native name"  CHARACTER VARYING(20) NOT NULL,
+
+    CONSTRAINT "PK_Country" PRIMARY KEY (code)
+);
+
+
+CREATE TABLE features."Cities" (
+    country        CHARACTER(3)          NOT NULL,
+    "native name"  CHARACTER VARYING(20) NOT NULL,
+    "translation"  CHARACTER VARYING(20) NOT NULL,
+    population     INTEGER,
+
+    CONSTRAINT "PK_City"    PRIMARY KEY (country, "native name"),
+    CONSTRAINT "FK_Country" FOREIGN KEY (country) REFERENCES features."Countries"(code)
+);
+
+
+CREATE TABLE features."Parks" (
+    country        CHARACTER(3)          NOT NULL,
+    city           CHARACTER VARYING(20) NOT NULL,
+    "native name"  CHARACTER VARYING(20) NOT NULL,
+    "translation"  CHARACTER VARYING(20) NOT NULL,
+
+    CONSTRAINT "PK_Park" PRIMARY KEY (country, city, "native name"),
+    CONSTRAINT "FK_City" FOREIGN KEY (country, city) REFERENCES features."Cities"(country,
"native name") ON DELETE CASCADE
+);
+
+
+COMMENT ON TABLE features."Cities"    IS 'The main table for this test.';
+COMMENT ON TABLE features."Countries" IS 'Countries in which a city is located.';
+COMMENT ON TABLE features."Parks"     IS 'Parks in cities.';
+
+
+
+-- Add enough data for having at least two parks for a city.
+-- The data intentionally use ideograms for testing encoding.
+
+INSERT INTO features."Countries" (code, "native name") VALUES
+    ('CAN', 'Canada'),
+    ('FRA', 'France'),
+    ('JPN', '日本');
+
+INSERT INTO features."Cities" (country, "native name", "translation", population) VALUES
+    ('CAN', 'Montréal', 'Montreal', 1704694),       -- Population in 2016
+    ('CAN', 'Québec',   'Quebec',    531902),       -- Population in 2016
+    ('FRA', 'Paris',    'Paris',    2206488),       -- Population in 2017
+    ('JPN', '東京',     'Tōkyō',   13622267);       -- Population in 2016
+
+INSERT INTO features."Parks" (country, city, "native name", "translation") VALUES
+    ('CAN', 'Montréal', 'Mont Royal',           'Mount Royal'),
+    ('FRA', 'Paris',    'Jardin des Tuileries', 'Tuileries Garden'),
+    ('FRA', 'Paris',    'Jardin du Luxembourg', 'Luxembourg Garden'),
+    ('JPN', '東京',     '代々木公園',           'Yoyogi-kōen'),
+    ('JPN', '東京',     '新宿御苑',             'Shinjuku Gyoen');


Mime
View raw message