/[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 1605 by bh,
Tue Aug 19 11:00:40 2003 UTC
+revision 2459 by bh,
Wed Dec 15 11:12:11 2004 UTC
 Line 1
- # Copyright (C) 2003 by Intevation GmbH
+ # Copyright (C) 2003, 2004 by Intevation GmbH
  # Authors:
  # Bernhard Herzog <[email protected]>
  #
 Line 115 
 class PostgreSQLServer:
          self.socket_dir = socket_dir
          # For the client side the socket directory can be used as the
-         # host the name starts with a slash.
+         # host if the name starts with a slash.
          self.host = os.path.abspath(socket_dir)
+         # name and password for the admin and an unprivileged user
+         self.admin_name = "postgres"
+         self.admin_password = "postgres"
+         self.user_name = "observer"
+         self.user_password = "telescope"
          # Map db names to db objects
          self.known_dbs = {}
-Line 135 
 class PostgreSQLServer:
+Line 141 
 class PostgreSQLServer:
              shutil.rmtree(self.dbdir)
          os.mkdir(self.dbdir)
-         run_command(["initdb", self.dbdir],
+         run_command(["initdb", "-D", self.dbdir, "-U", self.admin_name],
                      os.path.join(self.dbdir, "initdb.log"))
          extra_opts = "-p %d" % self.port
-Line 150 
 class PostgreSQLServer:
+Line 156 
 class PostgreSQLServer:
          # server ourselves
          self.wait_for_postmaster()
+         self.alter_user(self.admin_name, self.admin_password)
+         self.create_user(self.user_name, self.user_password)
      def wait_for_postmaster(self):
          """Return when the database server is running
-Line 161 
 class PostgreSQLServer:
+Line 170 
 class PostgreSQLServer:
          while count < max_count:
              try:
                  run_command(["psql", "-l", "-p", str(self.port),
-                              "-h", self.host],
+                              "-h", self.host, "-U", self.admin_name],
                              os.path.join(self.dbdir, "psql-%d.log" % count))
-             except:
+             except RuntimeError:
                  pass
+             except:
+                 traceback.print_exc()
              else:
                  break
              time.sleep(0.5)
-Line 173 
 class PostgreSQLServer:
+Line 184 
 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 and returns True if
+         that command succeeds and False otherwise.
-         This method runs pg_ctl status on the dbdir so even if the
+         Note that it is possible that this method returns true even if
-         object has just been created it is possible that this method
+         the PostgreSQLServer instance has just been created and
-         returns true if there's still a postmaster process running for
+         createdb() has not been called yet.  This can happen, for
-         self.dbdir.
+         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 187 
 class PostgreSQLServer:
+Line 202 
 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 205 
 class PostgreSQLServer:
+Line 223 
 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", os.path.join("..", "Data", "iceland",
-                                          "roads-line.shp"))]
+                                          "roads-line.shp")),
-         return self.get_static_data_db(dbname, tables)
+             # 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
+         The return value is a dictionary suitable as keyword argument
+         list to PostGISConnection. The user parameter may be either
+         'admin' to connect as admin or 'user' to connect as an
+         unprivileged user.
+         """
+         return {"host": self.host, "port": self.port,
+                 "user": getattr(self, user + "_name"),
+                 "password": getattr(self, user + "_password")}
+     def connection_string(self, user):
+         """Return (part of) the connection string to pass to psycopg.connect
+         The string contains host, port, user and password. The user
+         parameter must be either 'admin' or 'user', as for
+         connection_params.
+         """
+         params = []
+         for key, value in self.connection_params(user).items():
+             # FIXME: this doesn't do quiting correctly but that
+             # shouldn't be much of a problem (people shouldn't be using
+             # single quotes in filenames anyway :) )
+             params.append("%s='%s'" % (key, value))
+         return " ".join(params)
+     def execute_sql(self, dbname, user, sql):
+         """Execute the sql statament
+         The user parameter us used as in connection_params. The dbname
+         parameter must be the name of a database in the cluster.
+         """
+         conn = psycopg.connect("dbname=%s " % dbname
+                                + self.connection_string(user))
+         cursor = conn.cursor()
+         cursor.execute(sql)
+         conn.commit()
+         conn.close()
+     def require_authentication(self, required):
+         """Switch authentication requirements on or off
+         When started for the first time no passwords are required. Some
+         tests want to explicitly test whether Thuban's password
+         infrastructure works and switch password authentication on
+         explicitly. When switching it on, there should be a
+         corresponding call to switch it off again in the test case'
+         tearDown method or in a finally: block.
+         """
+         if required:
+             contents = "local all password\n"
+         else:
+             contents = "local all trust\n"
+         f = open(os.path.join(self.dbdir, "pg_hba.conf"), "w")
+         f.write(contents)
+         f.close()
+         run_command(["pg_ctl", "-D", self.dbdir, "reload"],
+                     os.path.join(self.dbdir, "pg_ctl-reload.log"))
+     def create_user(self, username, password):
+         """Create user username with password in the database"""
+         self.execute_sql("template1", "admin",
+                          "CREATE USER %s PASSWORD '%s';" % (username,password))
+     def alter_user(self, username, password):
+         """Change the user username's password in the database"""
+         self.execute_sql("template1", "admin",
+                          "ALTER USER %s PASSWORD '%s';" % (username,password))
  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 postgis.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
          """
          run_command(["createdb", "-p", str(self.server.port),
-                      "-h", self.server.host, self.dbname],
+                      "-h", self.server.host, "-U", self.server.admin_name,
+                      self.dbname],
                      os.path.join(self.server.dbdir, "createdb.log"))
          run_command(["createlang", "-p", str(self.server.port),
-                      "-h", self.server.host, "plpgsql", self.dbname],
+                      "-h", self.server.host,  "-U", self.server.admin_name,
+                      "plpgsql", self.dbname],
                      os.path.join(self.server.dbdir, "createlang.log"))
          # for some reason psql doesn't exit with an error code if the
          # file given as -f doesn't exist, so we check manually by trying
-Line 255 
 class PostGISDatabase:
+Line 416 
 class PostGISDatabase:
          f.close()
          del f
          run_command(["psql", "-f", self.postgis_sql, "-d", self.dbname,
-                      "-p", str(self.server.port), "-h", self.server.host],
+                      "-p", str(self.server.port), "-h", self.server.host,
+                      "-U", self.server.admin_name],
                       os.path.join(self.server.dbdir, "psql.log"))
+         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():
-Line 322 
 def reason_for_not_running_tests():
+Line 517 
 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 354 
 def skip_if_no_postgis():
+Line 554 
 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_multilinestring(coords):
      """Return string with a WKT representation of the arc in 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,
+     "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 the real 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("host=%s port=%s dbname=%s"
+     conn = psycopg.connect("dbname=%s " % dbname
-                            % (server.host, server.port, dbname))
+                            + db.server.connection_string("admin"))
      cursor = conn.cursor()
      shp = shapelib.ShapeFile(filename)
-Line 388 
 def upload_shapefile(filename, db, table
+Line 658 
 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)
+     cursor.execute("GRANT SELECT ON %s TO PUBLIC;" % tablename)
      conn.commit()

 Legend:



Removed from v.1605
 


changed lines


 
Added in v.2459
 Legend:



Removed from v.1605
 


changed lines


 
Added in v.2459
-Removed from v.1605
+Added in v.2459

[email protected]	ViewVC Help
Powered by ViewVC 1.1.26