public void TestAddGeometryColumnWithCatalog()
{
using (var db = new PostGisTestDataConnection(TestDatabaseConnectionString))
{
var result = db.Select(() => TableManagementFunctions.AddGeometryColumn("postgistest", "public", "test_geometry_ddl", "geom3", 4326, "POLYGON", 2, true));
Assert.AreEqual("public.test_geometry_ddl.geom3 SRID:4326 TYPE:POLYGON DIMS:2 ", result);
}
}
public void TestUpdateGeometrySRIDCatalog()
{
using (var db = new PostGisTestDataConnection(TestDatabaseConnectionString))
{
var updateGeomText = db.Select(() => TableManagementFunctions.UpdateGeometrySRID("postgistest", "public", "test_geometry_ddl", "geom", 4326));
var srid = db.Select(() => TableManagementFunctions.FindSrid("public", "test_geometry_ddl", "geom"));
Assert.AreEqual(4326, srid.GetValueOrDefault());
}
}
public void TestFindSrid()
{
using (var db = new PostGisTestDataConnection(TestDatabaseConnectionString))
{
var srid = db.Select(() => TableManagementFunctions.FindSrid("public", "test_geometry_ddl", "geom"));
Assert.IsNotNull(srid);
Assert.AreEqual(0, srid);
}
}
public void TestDropGeometryColumnCatalog()
{
using (var db = new PostGisTestDataConnection(TestDatabaseConnectionString))
{
var result = db.Select(() => TableManagementFunctions.DropGeometryColumn("postgis", "public", "test_geometry_ddl", "geom"));
using (var cmd = db.CreateCommand())
{
cmd.CommandText = "select count(1) from information_schema.columns WHERE table_schema = 'public' and table_name = 'test_geometry_ddl' and column_name = 'geom';";
var countText = cmd.ExecuteScalar().ToString();
Assert.AreEqual("0", countText);
}
}
}
public void TestDropGeometryTableCatalog()
{
using (var db = new PostGisTestDataConnection(TestDatabaseConnectionString))
{
var result = db.SelectQuery(() => TableManagementFunctions.DropGeometryTable("postgistest", "public", "test_geometry_ddl")).Single();
using (var cmd = db.CreateCommand())
{
cmd.CommandText = @"
select count(*) from pg_class where relname = 'test_geometry_ddl';";
var tableCount = (long)cmd.ExecuteScalar();
Assert.AreEqual(0, tableCount);
}
}
}
public void TestPopulateGeometryColumns()
{
var expectedConstrainNames = new[] { "enforce_dims_geom", "enforce_geotype_geom", "enforce_srid_geom", };
var expectedCheckClause = new[] { "((st_ndims(geom) = 3))", "((geometrytype(geom) = 'LINESTRING'::text))", "((st_srid(geom) = 4326))" };
//insert geometry data for populating then get table oid
int oid = 0;
using (var conn = new NpgsqlConnection(TestDatabaseConnectionString))
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "INSERT INTO test_geometry_ddl(id,geom) VALUES(1,ST_GeomFromText('LINESTRING(1 2 1, 3 4 1)',4326) );";
cmd.ExecuteNonQuery();
cmd.CommandText = "select oid::int4 from pg_class where oid = 'test_geometry_ddl'::regclass";
var reader = cmd.ExecuteReader();
reader.Read();
oid = reader.GetInt32(0);
}
}
using (var db = new PostGisTestDataConnection(TestDatabaseConnectionString))
{
db.Select(() => TableManagementFunctions.PopulateGeometryColumns(oid, false));
using (var cmd = db.CreateCommand())
{
cmd.CommandText = @"SELECT
tc.table_schema,
tc.TABLE_NAME,
string_agg ( col.COLUMN_NAME, ', ' ) AS COLUMNS,
tc.CONSTRAINT_NAME,
cc.check_clause
FROM
information_schema.table_constraints tc
JOIN information_schema.check_constraints cc ON tc.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = cc.
CONSTRAINT_NAME JOIN pg_namespace nsp ON nsp.nspname = cc.
CONSTRAINT_SCHEMA JOIN pg_constraint pgc ON pgc.conname = cc.CONSTRAINT_NAME
AND pgc.connamespace = nsp.oid
AND pgc.contype = 'c'
JOIN information_schema.COLUMNS col ON col.table_schema = tc.table_schema
AND col.TABLE_NAME = tc.TABLE_NAME
AND col.ordinal_position = ANY ( pgc.conkey )
WHERE
tc.CONSTRAINT_SCHEMA NOT IN ( 'pg_catalog', 'information_schema' ) and tc.TABLE_NAME = 'test_geometry_ddl'
GROUP BY
tc.table_schema,
tc.TABLE_NAME,
tc.CONSTRAINT_NAME,
cc.check_clause
ORDER BY
tc.table_schema,
tc.TABLE_NAME";
var reader = cmd.ExecuteReader();
int constraintCount = 0;
while (reader.Read())
{
var constraintName = reader.GetString(3);
var check = reader.GetString(4);
Assert.True(expectedConstrainNames.Contains(constraintName));
Assert.True(expectedCheckClause.Contains(check));
constraintCount++;
}
Assert.AreEqual(3, constraintCount);
}
}
}
