sis-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From desruisse...@apache.org
Subject [sis] branch geoapi-4.0 updated: Test SQLStore on other database engines.
Date Wed, 18 Jul 2018 17:05:25 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


The following commit(s) were added to refs/heads/geoapi-4.0 by this push:
     new e487e47  Test SQLStore on other database engines.
e487e47 is described below

commit e487e4746c0f928da1f3d0d0d6368ab611b45bb0
Author: Martin Desruisseaux <martin.desruisseaux@geomatys.com>
AuthorDate: Wed Jul 18 19:05:09 2018 +0200

    Test SQLStore on other database engines.
---
 .../sis/internal/metadata/sql/SQLBuilder.java      |  3 +-
 .../java/org/apache/sis/test/sql/TestDatabase.java | 54 +++++++++++++++++-----
 .../referencing/factory/sql/EPSGInstallerTest.java |  2 +-
 storage/sis-sqlstore/pom.xml                       | 10 ++++
 .../apache/sis/internal/sql/feature/Features.java  |  4 +-
 .../org/apache/sis/storage/sql/SQLStoreTest.java   | 44 ++++++++++++++++--
 .../org/apache/sis/storage/sql/Features.sql        | 45 +++++++++---------
 7 files changed, 120 insertions(+), 42 deletions(-)

diff --git a/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/SQLBuilder.java
b/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/SQLBuilder.java
index 21b6c5d..c4ab4c4 100644
--- a/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/SQLBuilder.java
+++ b/core/sis-metadata/src/main/java/org/apache/sis/internal/metadata/sql/SQLBuilder.java
@@ -187,8 +187,9 @@ public class SQLBuilder {
      * @return this builder, for method call chaining.
      */
     public final SQLBuilder appendIdentifier(final String catalog, String schema, final String
identifier) {
-        if (catalog != null) {
+        if (catalog != null && !catalog.isEmpty()) {
             appendIdentifier(catalog);
+            buffer.append('.');
             if (schema == null) {
                 return appendIdentifier("").appendIdentifier(identifier);
             }
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 ee1464b..a3d0255 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
@@ -26,6 +26,7 @@ import java.sql.SQLDataException;
 import org.postgresql.PGProperty;
 import org.postgresql.ds.PGSimpleDataSource;
 import org.hsqldb.jdbc.JDBCDataSource;
+import org.hsqldb.jdbc.JDBCPool;
 import org.apache.derby.jdbc.EmbeddedDataSource;
 import org.apache.sis.internal.metadata.sql.Initializer;
 import org.apache.sis.internal.metadata.sql.ScriptRunner;
@@ -130,23 +131,41 @@ public strictfp class TestDatabase implements AutoCloseable {
     }
 
     /**
-     * Creates a in-memory database on HSQLDB.
+     * Creates a in-memory database on HSQLDB. The database can optionally use a connection
pool.
+     * The test method can set {@code pooled} to {@code true} if it needs the data to survive
when
+     * the connection is closed and re-opened.
      *
-     * @param  name  the database name (without {@code "jdbc:hsqldb:mem:"} prefix).
+     * @param  name    the database name (without {@code "jdbc:hsqldb:mem:"} prefix).
+     * @param  pooled  whether the database should use a connection pool.
      * @return connection to the test database.
      * @throws SQLException if an error occurred while creating the database.
      *
      * @since 1.0
      */
-    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));
+    public static TestDatabase createOnHSQLDB(final String name, final boolean pooled) throws
SQLException {
+        final DataSource ds;
+        final JDBCPool pool;
+        final String url = "jdbc:hsqldb:mem:".concat(name);
+        if (pooled) {
+            pool = new JDBCPool();
+            pool.setDatabaseName("Apache SIS test database");
+            pool.setURL(url);
+            ds = pool;
+        } else {
+            final JDBCDataSource simple = new JDBCDataSource();
+            simple.setDatabaseName("Apache SIS test database");
+            simple.setURL(url);
+            ds = simple;
+            pool = null;
+        }
         return new TestDatabase(ds) {
             @Override public void close() throws SQLException {
                 try (Connection c = ds.getConnection(); Statement s = c.createStatement())
{
                     s.execute("SHUTDOWN");
                 }
+                if (pool != null) {
+                    pool.close(2);
+                }
             }
         };
     }
@@ -214,16 +233,27 @@ public strictfp class TestDatabase implements AutoCloseable {
     }
 
     /**
-     * Executes the SQL statements in the given resource file.
+     * Executes the given SQL statements, or statements from the given resource files.
+     * If an element from the {@code scripts} array begin by {@code "file:"}, then the part
+     * after {@code ":"} will be read as a resource file loaded by the given {@code loader}.
+     * Otherwise the script is executed as a SQL statement. Null element are ignored.
      *
-     * @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.
+     * @param loader   a class in the package of the resource file. This is usually the test
class.
+     * @param scripts  SQL statements or names of the SQL files to load and execute.
+     * @throws IOException if an error occurred while reading a resource file.
      * @throws SQLException if an error occurred while executing a SQL statement.
      */
-    public void executeSQL(final Class<?> loader, final String queryFile) throws IOException,
SQLException {
+    public void executeSQL(final Class<?> loader, final String... scripts) throws IOException,
SQLException {
         try (Connection c = source.getConnection(); ScriptRunner r = new ScriptRunner(c,
1000)) {
-            r.run(loader, queryFile);
+            for (final String sql : scripts) {
+                if (sql != null) {
+                    if (sql.startsWith("file:")) {
+                        r.run(loader, sql.substring(5));
+                    } else {
+                        r.run(sql);
+                    }
+                }
+            }
         }
     }
 
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 8fdf1b3..d680c09 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
@@ -144,7 +144,7 @@ public final strictfp class EPSGInstallerTest extends TestCase {
     @Test
     public void testCreationOnHSQLDB() throws Exception {
         final InstallationScriptProvider scripts = getScripts();            // Needs to be
invoked first.
-        try (TestDatabase db = TestDatabase.createOnHSQLDB("EPSGInstaller")) {
+        try (TestDatabase db = TestDatabase.createOnHSQLDB("EPSGInstaller", false)) {
             createAndTest(db.source, scripts);
         }
         loggings.assertNextLogContains("EPSG", "jdbc:hsqldb:mem:EPSGInstaller");
diff --git a/storage/sis-sqlstore/pom.xml b/storage/sis-sqlstore/pom.xml
index 7deabc0..7041ff1 100644
--- a/storage/sis-sqlstore/pom.xml
+++ b/storage/sis-sqlstore/pom.xml
@@ -118,6 +118,16 @@
       <scope>test</scope>
     </dependency>
     <dependency>
+      <groupId>org.apache.derby</groupId>
+      <artifactId>derby</artifactId>
+      <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/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Features.java
b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Features.java
index 26eddbe..57e9c1f 100644
--- a/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Features.java
+++ b/storage/sis-sqlstore/src/main/java/org/apache/sis/internal/sql/feature/Features.java
@@ -246,7 +246,7 @@ final class Features implements Spliterator<Feature>, Runnable {
             final Relation componentOf = following.get(following.size() - 1);
             String separator = " WHERE ";
             for (String primaryKey : componentOf.getSearchColumns()) {
-                sql.append(separator).append(primaryKey).append("=?");
+                sql.append(separator).appendIdentifier(primaryKey).append("=?");
                 separator = " AND ";
             }
             statement = connection.prepareStatement(sql.toString());
@@ -274,7 +274,7 @@ final class Features implements Spliterator<Feature>, Runnable {
     {
         int columnCount = columnIndices.size();
         if (columnCount != 0) sql.append(',');
-        sql.append(' ').append(column);
+        sql.append(' ').appendIdentifier(column);
         if (columnIndices.put(column, ++columnCount) == null) return columnCount;
         throw new InternalDataStoreException(Resources.format(Resources.Keys.DuplicatedColumn_1,
column));
     }
diff --git a/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java
b/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java
index 649dc06..7269f20 100644
--- a/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java
+++ b/storage/sis-sqlstore/src/test/java/org/apache/sis/storage/sql/SQLStoreTest.java
@@ -48,6 +48,11 @@ import org.opengis.feature.FeatureAssociationRole;
  */
 public final strictfp class SQLStoreTest extends TestCase {
     /**
+     * The schema where will be stored the features to test.
+     */
+    private static final String SCHEMA = "features";
+
+    /**
      * Number of time that the each country has been seen while iterating over the cities.
      */
     private final Map<String,Integer> countryCount = new HashMap<>();
@@ -60,23 +65,54 @@ public final strictfp class SQLStoreTest extends TestCase {
     private Feature canada;
 
     /**
+     * Tests on Derby.
+     *
+     * @throws Exception if an error occurred while testing the database.
+     */
+    @Test
+    public void testOnDerby() throws Exception {
+        test(TestDatabase.create("SQLStore"), true);
+    }
+
+    /**
+     * Tests on HSQLDB.
+     *
+     * @throws Exception if an error occurred while testing the database.
+     */
+    @Test
+    public void testOnHSQLDB() throws Exception {
+        test(TestDatabase.createOnHSQLDB("SQLStore", true), true);
+    }
+
+    /**
      * Tests on PostgreSQL.
      *
      * @throws Exception if an error occurred while testing the database.
      */
     @Test
     public void testOnPostgreSQL() throws Exception {
-        test(TestDatabase.createOnPostgreSQL("features", true));
+        test(TestDatabase.createOnPostgreSQL(SCHEMA, true), false);
     }
 
     /**
      * Tests reading an existing schema. The schema is created and populated by the {@code
Features.sql} script.
+     *
+     * @param  inMemory  where the test database is in memory. If {@code true}, then the
database is presumed
+     *                   initially empty: a schema will be created, and we assume that there
is no ambiguity
+     *                   if we don't specify the schema in {@link SQLStore} constructor.
      */
-    private void test(final TestDatabase database) throws Exception {
+    private void test(final TestDatabase database, final boolean inMemory) throws Exception
{
+        final String[] scripts = {
+            "CREATE SCHEMA " + SCHEMA + ';',
+            "file:Features.sql"
+        };
+        if (!inMemory) {
+            scripts[0] = null;      // Erase the "CREATE SCHEMA" statement if the schema
already exists.
+        }
         try (TestDatabase tmp = database) {
-            tmp.executeSQL(SQLStoreTest.class, "Features.sql");
+            tmp.executeSQL(SQLStoreTest.class, scripts);
             try (SQLStore store = new SQLStore(new SQLStoreProvider(), new StorageConnector(tmp.source),
-                    SQLStoreProvider.createTableName(null, "features", "Cities")))
+                    SQLStoreProvider.createTableName(null, inMemory ? null : SCHEMA, "Cities")))
             {
                 final FeatureSet cities = (FeatureSet) store.findResource("Cities");
                 verifyFeatureType(cities.getType(),
diff --git a/storage/sis-sqlstore/src/test/resources/org/apache/sis/storage/sql/Features.sql
b/storage/sis-sqlstore/src/test/resources/org/apache/sis/storage/sql/Features.sql
index 05364c4..148c076 100644
--- a/storage/sis-sqlstore/src/test/resources/org/apache/sis/storage/sql/Features.sql
+++ b/storage/sis-sqlstore/src/test/resources/org/apache/sis/storage/sql/Features.sql
@@ -11,32 +11,32 @@
 --   "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,
+    "code"         CHARACTER(3)          NOT NULL,
+    "native_name"  CHARACTER VARYING(20) NOT NULL,
 
-    CONSTRAINT "PK_Country" PRIMARY KEY (code)
+    CONSTRAINT "PK_Country" PRIMARY KEY ("code")
 );
 
 
 CREATE TABLE features."Cities" (
-    country      CHARACTER(3)          NOT NULL,
-    native_name  CHARACTER VARYING(20) NOT NULL,
-    english_name CHARACTER VARYING(20),
-    population   INTEGER,
+    "country"      CHARACTER(3)          NOT NULL,
+    "native_name"  CHARACTER VARYING(20) NOT NULL,
+    "english_name" CHARACTER VARYING(20),
+    "population"   INTEGER,
 
-    CONSTRAINT "PK_City"    PRIMARY KEY (country, native_name),
-    CONSTRAINT "FK_Country" FOREIGN KEY (country) REFERENCES features."Countries"(code)
+    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,
-    english_name CHARACTER VARYING(20),
+    "country"      CHARACTER(3)          NOT NULL,
+    "city"         CHARACTER VARYING(20) NOT NULL,
+    "native_name"  CHARACTER VARYING(20) NOT NULL,
+    "english_name" CHARACTER VARYING(20),
 
-    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
+    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
 );
 
 
@@ -49,18 +49,19 @@ 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
+INSERT INTO features."Countries" ("code", "native_name") VALUES
     ('CAN', 'Canada'),
     ('FRA', 'France'),
     ('JPN', '日本');
 
-INSERT INTO features."Cities" (country, native_name, english_name, 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
+-- All numbers in the "population" columns are 2016 or 2017 data.
+INSERT INTO features."Cities" ("country", "native_name", "english_name", "population") VALUES
+    ('CAN', 'Montréal', 'Montreal', 1704694),
+    ('CAN', 'Québec',   'Quebec',    531902),
+    ('FRA', 'Paris',    'Paris',    2206488),
+    ('JPN', '東京',     'Tōkyō',   13622267);
 
-INSERT INTO features."Parks" (country, city, native_name, english_name) VALUES
+INSERT INTO features."Parks" ("country", "city", "native_name", "english_name") VALUES
     ('CAN', 'Montréal', 'Mont Royal',           'Mount Royal'),
     ('FRA', 'Paris',    'Jardin des Tuileries', 'Tuileries Garden'),
     ('FRA', 'Paris',    'Jardin du Luxembourg', 'Luxembourg Garden'),


Mime
View raw message