/[thuban]/branches/WIP-pyshapelib-bramz/test/postgissupport.py

Diff of /branches/WIP-pyshapelib-bramz/test/postgissupport.py

Parent Directory | Revision Log | View Patch Patch

-revision 1634 by bh,
Fri Aug 22 16:55:19 2003 UTC
+revision 2589 by bh,
Tue Mar 29 18:36:53 2005 UTC
 Line 1
- # Copyright (C) 2003 by Intevation GmbH
+ # Copyright (C) 2003, 2004, 2005 by Intevation GmbH
  # Authors:
  # Bernhard Herzog <[email protected]>
  #
 Line 17 
 import time
  import popen2
  import shutil
  import traceback
+ import re
  import support
-Line 184 
 class PostgreSQLServer:
+Line 185 
 class PostgreSQLServer:
              raise RuntimeError("postmaster didn't start")
      def is_running(self):
-         """Return true a postmaster process is running on self.dbdir
+         """Return whether a postmaster process is running on self.dbdir
-         This method runs pg_ctl status on the dbdir so even if the
+         This method runs pg_ctl status on the dbdir and returns True if
-         object has just been created it is possible that this method
+         that command succeeds and False otherwise.
-         returns true if there's still a postmaster process running for
-         self.dbdir.
+         Note that it is possible that this method returns true even if
+         the PostgreSQLServer instance has just been created and
+         createdb() has not been called yet.  This can happen, for
+         instance, if the server has been started manually for debugging
+         purposes after a test suite run.
          """
          return run_boolean_command(["pg_ctl", "-D", self.dbdir, "status"])
-Line 198 
 class PostgreSQLServer:
+Line 203 
 class PostgreSQLServer:
          run_command(["pg_ctl", "-m", "fast", "-D", self.dbdir, "stop"],
                      os.path.join(self.dbdir, "pg_ctl-stop.log"))
-     def new_postgis_db(self, dbname, tables = None):
+     def new_postgis_db(self, dbname, tables = None, reference_systems = None,
+                        views = None):
          """Create and return a new PostGISDatabase object using self as server
          """
-         db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables)
+         db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables,
+                              reference_systems = reference_systems,
+                              views = views)
          db.initdb()
          self.known_dbs[dbname] = db
          return db
-     def get_static_data_db(self, dbname, tables):
+     def get_static_data_db(self, dbname, tables, reference_systems, views):
          """Return a PostGISDatabase for a database with the given static data
          If no databasse of the name dbname exists, create a new one via
-Line 216 
 class PostgreSQLServer:
+Line 224 
 class PostgreSQLServer:
          indicated data, return that. If the already existing db uses
          different data raise a value error.
-         The tables argument should be a sequence of table specifications
+         If the database doesn't exist, create a new one via
-         where each specifications is a (tablename, shapefilename) pair.
+         self.new_postgis_db.
+         The parameters tables and reference_systems have the same
+         meaning as for new_postgis_db.
          """
          db = self.known_dbs.get(dbname)
          if db is not None:
-             if db.has_data(tables):
+             if db.has_data(tables, reference_systems, views):
                  return db
              raise ValueError("PostGISDatabase named %r doesn't have tables %r"
                               % (dbname, tables))
-         return self.new_postgis_db(dbname, tables)
+         return self.new_postgis_db(dbname, tables, reference_systems, views)
      def get_default_static_data_db(self):
          dbname = "PostGISStaticTests"
-         tables = [("landmarks", os.path.join("..", "Data", "iceland",
+         srids = [(1, "proj=longlat datum=WGS84")]
-                                              "cultural_landmark-point.shp")),
+         tables = [
-                   ("political", os.path.join("..", "Data", "iceland",
+             # Direct copies of the shapefiles. The shapeids are exactly
+             # the same, except where changed with "gid_offset", of
+             # course.  Note that the test implementation requires that
+             # all the landmard tables use an gid_offset of 1000.
+             ("landmarks", os.path.join("..", "Data", "iceland",
+                                        "cultural_landmark-point.shp"),
+              [("gid_offset", 1000)]),
+             ("political", os.path.join("..", "Data", "iceland",
                                               "political.shp")),
-                   ("roads", os.path.join("..", "Data", "iceland",
+             ("roads_multi", os.path.join("..", "Data", "iceland",
-                                          "roads-line.shp"))]
+                                          "roads-line.shp")),
-         return self.get_static_data_db(dbname, tables)
+             # same as roads-multi but using LINESTRING instead of
+             # MULTILINESTRING
+             ("roads", os.path.join("..", "Data", "iceland",
+                                          "roads-line.shp"),
+              [("force_wkt_type", "LINESTRING")]),
+             # The polygon data as a MULTIPOLYGON geometry type
+             ("political_multi", os.path.join("..", "Data", "iceland",
+                                              "political.shp"),
+              [("force_wkt_type", "MULTIPOLYGON")]),
+             # Copy of landmarks but using an srid != -1
+             ("landmarks_srid", os.path.join("..", "Data", "iceland",
+                                        "cultural_landmark-point.shp"),
+              [("gid_offset", 1000),
+               ("srid", 1)]),
+             # Copy of landmarks with a gid column called "point_id" instead
+             # of "gid" and using an srid != -1.
+             ("landmarks_point_id", os.path.join("..", "Data", "iceland",
+                                                 "cultural_landmark-point.shp"),
+              [("gid_offset", 1000),
+               ("srid", 1),
+               ("gid_column", "point_id")]),
+             ]
+         views = [("v_landmarks", "SELECT * FROM landmarks_point_id")]
+         return self.get_static_data_db(dbname, tables, srids, views)
      def connection_params(self, user):
          """Return the connection parameters for the given user
-Line 265 
 class PostgreSQLServer:
+Line 310 
 class PostgreSQLServer:
          return " ".join(params)
      def execute_sql(self, dbname, user, sql):
-         """Execute the sql statament
+         """Execute the sql statament and return a result for SELECT statements
          The user parameter us used as in connection_params. The dbname
-         parameter must be the name of a database in the cluster.
+         parameter must be the name of a database in the cluster.  The
+         sql parameter is the SQL statement to execute as a string.  If
+         the string starts with 'select' (matched case insensitively) the
+         first row of the result will be returned.  Otherwise the return
+         value is None.
          """
          conn = psycopg.connect("dbname=%s " % dbname
                                 + self.connection_string(user))
          cursor = conn.cursor()
          cursor.execute(sql)
+         if sql.lower().startswith("select"):
+             row = cursor.fetchone()
+         else:
+             row = None
          conn.commit()
          conn.close()
+         return row
+     def server_version(self):
+         """Return the server version as a tuple (major, minor, patch)
+         Each item in the tuple is an int.
+         """
+         result = self.execute_sql("template1", "admin", "SELECT version();")[0]
+         match = re.match(r"PostgreSQL (\d+\.\d+\.\d+)", result)
+         if match:
+             return tuple(map(int, match.group(1).split(".")))
+         else:
+             raise RutimeError("Cannot determine PostgreSQL server version"
+                               " from %r" % result)
      def require_authentication(self, required):
          """Switch authentication requirements on or off
-Line 287 
 class PostgreSQLServer:
+Line 354 
 class PostgreSQLServer:
          corresponding call to switch it off again in the test case'
          tearDown method or in a finally: block.
          """
+         # Starting with PostgreSQL 7.3 the pg_hba.conf file has an
+         # additional column with a username.  Query the server version
+         # and generate a file in the correct format.
+         if self.server_version() >= (7, 3):
+             user = "all"
+         else:
+             user = ""
          if required:
-             contents = "local all password\n"
+             contents = "local all %s password\n" % user
          else:
-             contents = "local all trust\n"
+             contents = "local all %s trust\n" % user
          f = open(os.path.join(self.dbdir, "pg_hba.conf"), "w")
          f.write(contents)
          f.close()
-Line 313 
 class PostGISDatabase:
+Line 387 
 class PostGISDatabase:
      """A PostGIS database in a PostgreSQLServer"""
-     def __init__(self, server, postgis_sql, dbname, tables = None):
+     def __init__(self, server, postgis_sql, dbname, tables = None,
+                  reference_systems = (), views = None):
+         """Initialize the PostGISDatabase
+         Parameters:
+             server -- The PostgreSQLServer instance containing the
+                 database
+             postgis_sql -- Filename of the sql file with the postgis
+                 initialization code
+             dbname -- The name of the database
+             tables -- Optional description of tables to create in the
+                 new database. If given it should be a list of
+                 (tablename, shapefilename) pairs meaning that a table
+                 tablename will be created with the contents of the given
+                 shapefile or (tablename, shapefilename, extraargs)
+                 triples. The extraargs should be a list of key, value
+                 pairs to use as keyword arguments to upload_shapefile.
+             reference_systems -- Optional description of spatial
+                 reference systems.  If given, it should be a sequence of
+                 (srid, params) pairs where srid is the srid defined by
+                 the proj4 paramter string params.  The srid can be given
+                 as an extra parameter in the tables list.
+             views -- Optional description of views.  If given it should
+                 be a list of (viewname, select_stmt) pairs where
+                 viewname is the name of the view to be created and
+                 select_stmt is the select statement to use as the basis.
+                 The views will be created after the tables and may refer
+                 to them in the select_stmt.
+         """
          self.server = server
          self.postgis_sql = postgis_sql
          self.dbname = dbname
          self.tables = tables
+         self.views = views
+         if reference_systems:
+             self.reference_systems = reference_systems
+         else:
+             # Make sure that it's a sequence we can iterate over even if
+             # the parameter's None
+             self.reference_systems = ()
      def initdb(self):
          """Remove the old db directory and create and initialize a new database
-Line 343 
 class PostGISDatabase:
+Line 458 
 class PostGISDatabase:
          self.server.execute_sql(self.dbname, "admin",
                                  "GRANT SELECT ON geometry_columns TO PUBLIC;")
+         self.server.execute_sql(self.dbname, "admin",
+                                 "GRANT SELECT ON spatial_ref_sys TO PUBLIC;")
+         for srid, params in self.reference_systems:
+             self.server.execute_sql(self.dbname, "admin",
+                                     "INSERT INTO spatial_ref_sys VALUES"
+                                     " (%d, '', %d, '', '%s');"
+                                     % (srid, srid, params))
          if self.tables is not None:
-             for tablename, shapefile in self.tables:
+             def unpack(item):
-                 upload_shapefile(shapefile, self, tablename)
+                 extra = {"force_wkt_type": None, "gid_offset": 0,
+                          "srid": -1}
-     def has_data(self, tables):
+                 if len(info) == 2:
-         return self.tables == tables
+                     tablename, shapefile = info
+                 else:
+                     tablename, shapefile, kw = info
+                     for key, val in kw:
+                         extra[key] = val
+                 return tablename, shapefile, extra
+             for info in self.tables:
+                 tablename, shapefile, kw = unpack(info)
+                 upload_shapefile(shapefile, self, tablename, **kw)
+         if self.views is not None:
+             for viewname, select_stmt in self.views:
+                 self.server.execute_sql(self.dbname, "admin",
+                                         "CREATE VIEW %s AS %s" % (viewname,
+                                                                   select_stmt))
+                 self.server.execute_sql(self.dbname, "admin",
+                                         "GRANT SELECT ON %s TO PUBLIC;"
+                                         % viewname)
+     def has_data(self, tables, reference_systems, views):
+         return (self.tables == tables
+                 and self.reference_systems == reference_systems
+                 and self.views == views)
  def find_postgis_sql():
      """Return the name of the postgis_sql file
      A postgis installation usually has the postgis_sql file in
-     PostgreSQL's datadir (i.e. the directory where PostgreSQL keeps
+     PostgreSQL's $datadir (i.e. the directory where PostgreSQL keeps
      static files, not the directory containing the databases).
      Unfortunately there's no way to determine the name of this directory
      with pg_config so we assume here that it's
      $bindir/../share/postgresql/.
+     Furthermore, different versions of postgis place the file in
+     slightly different locations or may even use different names.  For
+     instance:
+       postgis 0.7.5        $datadir/contrib/postgis.sql
+       postgis 0.8.1        $datadir/postgis.sql
+       postgis 1.0.0-rc1    $datadir/lwpostgis.sql
+       postgis 1.0.0-rc4    $datadir/contrib/lwpostgis.sql
+     To support both versions, we look in both places and return the
+     first one found (looking under contrib first).  If the file is not
+     found the return value is None.
      """
      bindir = run_config_script("pg_config --bindir").strip()
-     return os.path.join(bindir, "..", "share", "postgresql",
+     datadir = os.path.join(bindir, "..", "share", "postgresql")
-                         "contrib", "postgis.sql")
+     for filename in [os.path.join(datadir, "contrib", "postgis.sql"),
+                      os.path.join(datadir, "postgis.sql"),
+                      os.path.join(datadir, "lwpostgis.sql"),
+                      os.path.join(datadir, "contrib", "lwpostgis.sql")]:
+         if os.path.exists(filename):
+             return filename
  _postgres_server = None
  def get_test_server():
-Line 408 
 def reason_for_not_running_tests():
+Line 572 
 def reason_for_not_running_tests():
         The name of the postgis_sql file is determined by find_postgis_sql()
       - psycopg can be imported successfully.
      """
+     # run_command currently uses Popen4 which is not available under
+     # Windows, for example.
+     if not hasattr(popen2, "Popen4"):
+         return "Can't run PostGIS test because popen2.Popen4 does not exist"
      try:
          run_command(["pg_ctl", "--help"], None)
      except RuntimeError:
-Line 440 
 def skip_if_no_postgis():
+Line 609 
 def skip_if_no_postgis():
      if _cannot_run_postgis_tests:
          raise support.SkipTest(_cannot_run_postgis_tests)
- def point_to_wkt(coords):
+ def skip_if_addgeometrycolumn_does_not_use_quote_ident():
+     """Skip a test if the AddGeometryColumn function doesn't use quote_ident
+     If the AddGeometryColumn function doesn't use quote_ident it doesn't
+     support unusual table or column names properly, that is, it will
+     fail with errors for names that contain spaces or double quotes.
+     The test performed by this function is a bit simplistic because it
+     only tests whether the string 'quote_ident' occurs anywhere in the
+     postgis.sql file. This will hopefully work because when this was
+     fixed in postgis CVS AddGeometryColumn was the first function to use
+     quote_ident.
+     """
+     f = file(find_postgis_sql())
+     content = f.read()
+     f.close()
+     if content.find("quote_ident") < 0:
+         raise support.SkipTest("AddGeometryColumn doesn't use quote_ident")
+ def coords_to_point(coords):
      """Return string with a WKT representation of the point in coords"""
      x, y = coords[0]
      return "POINT(%r %r)" % (x, y)
- def polygon_to_wkt(coords):
+ def coords_to_polygon(coords):
      """Return string with a WKT representation of the polygon in coords"""
      poly = []
      for ring in coords:
          poly.append(", ".join(["%r %r" % p for p in ring]))
      return "POLYGON((%s))" % "), (".join(poly)
- def arc_to_wkt(coords):
+ def coords_to_linestring(coords):
-     """Return string with a WKT representation of the arc in coords"""
+     """Return string with a LINESTRING WKT representation of coords"""
+     if len(coords) > 1:
+         raise ValueError("A LINESTRING can only have one arc")
+     return "LINESTRING(%s)" % ", ".join(["%r %r" % p for p in coords[0]])
+ def coords_to_multilinestring(coords):
+     """Return string with a MULTILINESTRING WKT representation of coords"""
      poly = []
      for ring in coords:
          poly.append(", ".join(["%r %r" % p for p in ring]))
      return "MULTILINESTRING((%s))" % "), (".join(poly)
- def upload_shapefile(filename, db, tablename):
+ def coords_to_multipolygon(coords):
+     """Return string with a WKT representation of the polygon in coords"""
+     poly = []
+     for ring in coords:
+         poly.append(", ".join(["%r %r" % p for p in ring]))
+     return "MULTIPOLYGON(((%s)))" % ")), ((".join(poly)
+ wkt_converter = {
+     "POINT": coords_to_point,
+     "LINESTRING": coords_to_linestring,
+     "MULTILINESTRING": coords_to_multilinestring,
+     "POLYGON": coords_to_polygon,
+     "MULTIPOLYGON": coords_to_multipolygon,
+     }
+ def upload_shapefile(filename, db, tablename, force_wkt_type = None,
+                      gid_offset = 0, gid_column = "gid", srid = -1):
+     """Upload a shapefile into a new database table
+     Parameters:
+     filename -- The name of the shapefile
+     db -- The PostGISDatabase instance representing the database
+     tablename -- The name of the table to create and into which the data
+                 is to be inserted
+     force_wkt_type -- If given and not None, this is used as the WKT
+                 geometry type to use instead of the default that would
+                 be chosen based on the type of the shapefile
+     gid_offset -- A number to add to the shapeid to get the value for
+                 the gid column (default 0)
+     gid_column -- The name of the column with the shape ids.  Default
+                   'gid'.  If None, no gid column will be created.  The
+                   name is directly used in SQL statements, so if it
+                   contains unusualy characters the caller should provide
+                   a suitable quoted string.
+     srid -- The srid of the spatial references system used by the table
+             and the data
+     """
      import dbflib, shapelib
+     # We build this map here because we need shapelib which can only be
+     # imported after support.initthuban has been called which we can't
+     # easily do in this module because it's imported by support.
+     shp_to_wkt = {
+         shapelib.SHPT_POINT: "POINT",
+         shapelib.SHPT_ARC: "MULTILINESTRING",
+         shapelib.SHPT_POLYGON: "POLYGON",
+         }
      server = db.server
      dbname = db.dbname
      conn = psycopg.connect("dbname=%s " % dbname
-Line 474 
 def upload_shapefile(filename, db, table
+Line 720 
 def upload_shapefile(filename, db, table
                 dbflib.FTInteger: "INTEGER",
                 dbflib.FTDouble: "DOUBLE PRECISION"}
-     insert_formats = ["%(gid)s"]
+     insert_formats = []
-     fields = ["gid INT"]
+     if gid_column:
+         insert_formats.append("%(gid)s")
+     fields = []
+     fields_decl = []
+     if gid_column:
+         fields.append(gid_column)
+         fields_decl.append("%s INT" % gid_column)
      for i in range(dbf.field_count()):
          ftype, name, width, prec = dbf.field_info(i)
-         fields.append("%s %s" % (name, typemap[ftype]))
+         fields.append(name)
+         fields_decl.append("%s %s" % (name, typemap[ftype]))
          insert_formats.append("%%(%s)s" % name)
      stmt = "CREATE TABLE %s (\n    %s\n);" % (tablename,
-                                               ",\n    ".join(fields))
+                                               ",\n    ".join(fields_decl))
      cursor.execute(stmt)
      #print stmt
      numshapes, shapetype, mins, maxs = shp.info()
-     if shapetype == shapelib.SHPT_POINT:
+     wkttype =  shp_to_wkt[shapetype]
-         convert = point_to_wkt
+     if force_wkt_type:
-         wkttype = "POINT"
+         wkttype = force_wkt_type
-     elif shapetype == shapelib.SHPT_POLYGON:
+     convert = wkt_converter[wkttype]
-         convert = polygon_to_wkt
-         wkttype = "POLYGON"
-     elif shapetype == shapelib.SHPT_ARC:
-         convert = arc_to_wkt
-         wkttype = "MULTILINESTRING"
-     else:
-         raise ValueError("Unsupported Shapetype %r" % shapetype)
      cursor.execute("select AddGeometryColumn('%(dbname)s',"
-                    "'%(tablename)s', 'the_geom', '-1', '%(wkttype)s', 2);"
+                    "'%(tablename)s', 'the_geom', %(srid)d, '%(wkttype)s', 2);"
                     % locals())
+     fields.append("the_geom")
+     insert_formats.append("GeometryFromText(%(the_geom)s, %(srid)d)")
-     insert_formats.append("GeometryFromText(%(the_geom)s, -1)")
+     insert = ("INSERT INTO %s (%s) VALUES (%s)"
+               % (tablename, ", ".join(fields), ", ".join(insert_formats)))
-     insert = ("INSERT INTO %s VALUES (%s)"
-               % (tablename, ", ".join(insert_formats)))
      for i in range(numshapes):
          data = dbf.read_record(i)
          data["tablename"] = tablename
-         data["gid"] = i
+         if gid_column:
+             data["gid"] = i + gid_offset
+         data["srid"] = srid
          data["the_geom"] = convert(shp.read_object(i).vertices())
          #print insert % data
          cursor.execute(insert, data)

 Legend:



Removed from v.1634
 


changed lines


 
Added in v.2589
 Legend:



Removed from v.1634
 


changed lines


 
Added in v.2589
-Removed from v.1634
+Added in v.2589

[email protected]	ViewVC Help
Powered by ViewVC 1.1.26