added a geometry columns table for geometry metadata with Add/Drop Ge…

…ometryColumn functions
jdeolive · Oct 20, 2010 · dc7ffee · dc7ffee
1 parent abd0041
commit dc7ffee
Show file tree

Hide file tree

Showing 4 changed files with 179 additions and 2 deletions.
diff --git a/core/src/main/java/geodb/GeoDB.java b/core/src/main/java/geodb/GeoDB.java
@@ -78,7 +78,7 @@ public static String GeoToolsVersion() {
      * Returns the internal version of the GeoH2 bindings in order to track upgrades.
      */
     public static String CheckSum() {
-        return "4";
+        return "5";
     }
 
     //
@@ -140,6 +140,11 @@ public static void InitGeoDB(Connection cx) throws SQLException {
                 st.execute("CREATE TABLE IF NOT EXISTS _GEODB (checksum VARCHAR)");
                 st.execute("DELETE FROM _GEODB");
                 st.execute("INSERT INTO _GEODB VALUES (" + CheckSum() + ")" );
+
+                //create the geometry columns table
+                st.execute("CREATE TABLE IF NOT EXISTS geometry_columns (f_table_schema VARCHAR, " +
+                    "f_table_name VARCHAR, f_geometry_column VARCHAR, coord_dimension INT, " +
+                    "srid INT, type VARCHAR(30))");
             }
             finally {
                 st.close();
@@ -150,6 +155,93 @@ public static void InitGeoDB(Connection cx) throws SQLException {
         }
     }
 
+    //
+    // Management functions
+    //
+    /**
+     * Adds a geometry column to a table.
+     * 
+     * @param schema The table schema, may be <code>null</code> to specify default schema
+     * @param table The table name, not null
+     * @param column The geometry column name, not null
+     * @param srid The spatial reference system identifier
+     * @param type The geometry type, one of "POINT", "LINESTRING", "POLYGON", "MULTIPOINT", 
+     *             "MULTILINESTRING", "MULTIPOLYGON", "GEOMETRY", "GEOMETRYCOLLECTION"
+     * @param dim The geometry dimension 
+     */
+    public static void AddGeometryColumn(Connection cx, String schema, String table, 
+        String column, int srid, String type, int dim) throws SQLException {
+
+        type = type.toUpperCase();
+
+        Statement st = cx.createStatement();
+        try {
+            ResultSet rs = 
+                cx.getMetaData().getColumns(null, schema, table, column);
+            try {
+                if (!rs.next()) {
+                    st.execute("ALTER TABLE " + tbl(schema, table) + " ADD " 
+                        + esc(column) + " " + type + " COMMENT '" + type + "'");
+                }
+            }
+            finally {
+                rs.close();
+            }
+
+            schema = schema != null ? schema : "PUBLIC";   
+            if (!"GEOMETRY".equals(type) && !"GEOMETRYCOLLECTION".equals(type)) {
+                st.execute("ALTER TABLE " + tbl(schema, table) + " ADD CONSTRAINT " + 
+                    esc(geotypeConstraint(schema,table,column)) + " CHECK " + esc(column) + 
+                    " IS NULL OR " + "GeometryType(" + esc(column) + ") = '" + type + "'");
+            }
+            st.execute("INSERT INTO geometry_columns VALUES (" + 
+                str(schema) + ", " + str(table) + ", " + str(column) + ", " + 
+                srid + ", " + dim + ", " + str(type) + ")");
+        }
+        finally {
+            st.close();
+        }
+    }
+
+    /**
+     * Drops a geometry column from a table.
+     * 
+     * @param schema The table schema, may be <code>null</code> to specify default schema
+     * @param table The table name, not null
+     * @param column The geometry column name, not null
+     */
+    public static void DropGeometryColumn(Connection cx, String schema, String table, String column) 
+        throws SQLException {
+
+        Statement st = cx.createStatement();
+        try {
+            //check the case of a view
+            boolean isView = false;
+            ResultSet tables = cx.getMetaData().getTables(null, schema, table, new String[]{"VIEW"});
+            try {
+                isView = tables.next();
+            }
+            finally {
+                tables.close();
+            }
+
+            schema = schema != null ? schema : "PUBLIC";
+            st.execute("ALTER TABLE " + tbl(schema, table) + " DROP CONSTRAINT IF EXISTS " 
+                + esc(geotypeConstraint(schema,table,column)));
+
+            if (!isView) {
+                st.execute("ALTER TABLE " + tbl(schema, table) + " DROP COLUMN " + esc(column));
+            }
+
+            st.execute("DELETE FROM geometry_columns WHERE f_table_schema = " + str(schema) + 
+                " AND " + "f_table_name = " + str(table) + " AND f_geometry_column = " + str(column));
+        }
+        finally {
+            st.close();
+        }
+
+    }
+
     //
     // Geometry Outputs
     //
@@ -1247,4 +1339,27 @@ private static byte[] toWKB( Geometry g ) {
             throw new RuntimeException( e );
         }
     }
+
+    //
+    // some encoding helper functions
+    //
+    static String tbl(String schema, String table) {
+        return schema != null ? esc(schema)+"."+esc(table) : esc(table);
+    }
+
+    static String esc(String s) {
+        return "\"" + s + "\"";
+    }
+
+    static String str(String s) {
+        return "'"+s+"'";
+    }
+
+    static String geotypeConstraint(String schema, String table, String column) {
+        String name = table + "_" + column;
+        if (schema != null) {
+            name = schema + "_" + name;
+        }
+        return "ENFORCE_GEOTYPE_" + name;
+    }
 }
diff --git a/core/src/main/resources/geodb/geodb.sql b/core/src/main/resources/geodb/geodb.sql
@@ -1,4 +1,6 @@
+CREATE ALIAS AddGeometryColumn for "geodb.GeoDB.AddGeometryColumn"
 CREATE ALIAS CreateSpatialIndex for "geodb.GeoDB.CreateSpatialIndex"
+CREATE ALIAS DropGeometryColumn for "geodb.GeoDB.DropGeometryColumn"
 CREATE ALIAS DropSpatialIndex for "geodb.GeoDB.DropSpatialIndex"
 CREATE ALIAS EnvelopeAsText for "geodb.GeoDB.EnvelopeAsText"
 CREATE ALIAS GeometryType for "geodb.GeoDB.GeometryType"
@@ -33,3 +35,11 @@ CREATE ALIAS ST_Simplify FOR "geodb.GeoDB.ST_Simplify"
 CREATE ALIAS ST_Touches FOR "geodb.GeoDB.ST_Touches"
 CREATE ALIAS ST_Within FOR "geodb.GeoDB.ST_Within"
 CREATE ALIAS Version FOR "geodb.GeoDB.Version"
+CREATE DOMAIN POINT AS BLOB
+CREATE DOMAIN LINESTRING AS BLOB
+CREATE DOMAIN POLYGON AS BLOB
+CREATE DOMAIN MULTIPOINT AS BLOB
+CREATE DOMAIN MULTILINESTRING AS BLOB
+CREATE DOMAIN MULTIPOLYGON AS BLOB
+CREATE DOMAIN GEOMETRYCOLLECTION AS BLOB
+CREATE DOMAIN GEOMETRY AS BLOB
diff --git a/core/src/test/java/geodb/GeoDBFunctionTest.java b/core/src/test/java/geodb/GeoDBFunctionTest.java
@@ -1,8 +1,12 @@
 package geodb;
 
 import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
 
 import java.sql.ResultSet;
+import java.sql.SQLException;
 import java.sql.Statement;
 
 import org.junit.Before;
@@ -18,6 +22,8 @@ public void setUp() throws Exception {
 
         Statement st = cx.createStatement();
         st.execute("DROP TABLE IF EXISTS spatial");
+        st.execute("DELETE FROM geometry_columns");
+
         st.execute("CREATE TABLE spatial (id INT AUTO_INCREMENT PRIMARY KEY, geom BLOB)");
         st.execute("INSERT INTO spatial (geom) VALUES (ST_GeomFromText('POINT(0 0)', 4326))");
         st.execute("INSERT INTO spatial (geom) VALUES (ST_GeomFromText('POINT(1 1)', 4326))");
@@ -35,4 +41,49 @@ public void testSRID() throws Exception {
         rs.close();
         st.close();
     }
+
+    @Test
+    public void testAddGeometryColumn() throws Exception {
+        Statement st = cx.createStatement();
+
+        st.execute("CALL AddGeometryColumn(NULL,'SPATIAL', 'FOO', -1, 'POINT', 2)");
+
+        ResultSet rs = st.executeQuery("SELECT * FROM geometry_columns WHERE " +
+            " f_table_name = 'SPATIAL' AND f_geometry_column = 'FOO'");
+        assertTrue(rs.next());
+        assertEquals("SPATIAL", rs.getString(2));
+        assertEquals("FOO", rs.getString(3));
+        assertEquals(-1, rs.getInt(4));
+        assertEquals(2, rs.getInt(5));
+        assertEquals("POINT", rs.getString(6));
+
+        assertFalse(rs.next());
+        rs.close();
+
+        st.execute("INSERT INTO spatial (foo) VALUES (ST_GeomFromText('POINT(0 0)',-1))");
+        try {
+            st.execute("INSERT INTO spatial (foo) VALUES (ST_GeomFromText('LINESTRING(0 0, 1 1)',-1))");
+            fail("inserting non point should have failed");
+        }
+        catch(SQLException e) {}
+    }
+
+    @Test
+    public void testDropGeometryColumn() throws Exception {
+        testAddGeometryColumn();
+
+        Statement st = cx.createStatement();
+        st.executeQuery("SELECT foo FROM spatial");
+
+        st.execute("CALL DropGeometryColumn(NULL, 'SPATIAL', 'FOO')");
+        try {
+            st.executeQuery("SELECT foo FROM spatial");
+            fail("column foo should have been deleted");
+        }
+        catch(SQLException e) {}
+
+        ResultSet rs = st.executeQuery("SELECT * FROM geometry_columns WHERE " +
+            " f_table_name = 'SPATIAL' and f_geometry_column = 'foo'");
+        assertFalse(rs.next());
+    }
 }
diff --git a/core/src/test/java/geodb/GeoDBTest.java b/core/src/test/java/geodb/GeoDBTest.java
@@ -3,8 +3,10 @@
 import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertFalse;
 import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
 
 import java.sql.ResultSet;
+import java.sql.SQLException;
 import java.sql.Statement;
 
 import org.junit.Before;
@@ -70,5 +72,4 @@ public void testGetSRID() throws Exception {
 
         st.close();
     }
-
 }