/[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 1679 by bh,
Thu Aug 28 14:23:08 2003 UTC
+revision 2543 by bh,
Fri Jan 21 16:58:31 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"
+         srids = [(1, "proj=longlat datum=WGS84")]
          tables = [
              # Direct copies of the shapefiles. The shapeids are exactly
-             # the same.
+             # 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")),
+             # 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")]),
              ]
-         return self.get_static_data_db(dbname, tables)
+         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 275 
 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 297 
 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 323 
 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:
-Line 331 
 class PostGISDatabase:
+Line 396 
 class PostGISDatabase:
              server -- The PostgreSQLServer instance containing the
                  database
-             postgis_sql -- Filename of the postgis.sql file with the
+             postgis_sql -- Filename of the sql file with the postgis
-                 postgis initialization code
+                 initialization code
              dbname -- The name of the database
-Line 343 
 class PostGISDatabase:
+Line 408 
 class PostGISDatabase:
                  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 373 
 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:
              def unpack(item):
-                 extra = {"force_wkt_type": None, "gid_offset": 0}
+                 extra = {"force_wkt_type": None, "gid_offset": 0,
+                          "srid": -1}
                  if len(info) == 2:
                      tablename, shapefile = info
                  else:
-Line 389 
 class PostGISDatabase:
+Line 482 
 class PostGISDatabase:
                  tablename, shapefile, kw = unpack(info)
                  upload_shapefile(shapefile, self, tablename, **kw)
-     def has_data(self, tables):
+         if self.views is not None:
-         return self.tables == tables
+             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
+     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")]:
+         if os.path.exists(filename):
+             return filename
  _postgres_server = None
  def get_test_server():
-Line 486 
 def skip_if_no_postgis():
+Line 607 
 def skip_if_no_postgis():
      if _cannot_run_postgis_tests:
          raise support.SkipTest(_cannot_run_postgis_tests)
+ 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]
-Line 498 
 def coords_to_polygon(coords):
+Line 638 
 def coords_to_polygon(coords):
          poly.append(", ".join(["%r %r" % p for p in ring]))
      return "POLYGON((%s))" % "), (".join(poly)
+ def coords_to_linestring(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 WKT representation of the arc in 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]))
-Line 514 
 def coords_to_multipolygon(coords):
+Line 660 
 def coords_to_multipolygon(coords):
  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_offset = 0, gid_column = "gid", srid = -1):
      """Upload a shapefile into a new database table
      Parameters:
-Line 532 
 def upload_shapefile(filename, db, table
+Line 679 
 def upload_shapefile(filename, db, table
      tablename -- The name of the table to create and into which the data
                  is to be inserted
-     force_wkt_type -- If given the real WKT geometry type to use instead
+     force_wkt_type -- If given and not None, this is used as the WKT
-                 of the default that would be chosen based on the type of
+                 geometry type to use instead of the default that would
-                 the shapefile
+                 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
-Line 562 
 def upload_shapefile(filename, db, table
+Line 718 
 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
-Line 580 
 def upload_shapefile(filename, db, table
+Line 744 
 def upload_shapefile(filename, db, table
      convert = wkt_converter[wkttype]
      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 + gid_offset
+         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.1679
 


changed lines


 
Added in v.2543
 Legend:



Removed from v.1679
 


changed lines


 
Added in v.2543
-Removed from v.1679
+Added in v.2543

[email protected]	ViewVC Help
Powered by ViewVC 1.1.26