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

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

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 1656 by bh, Mon Aug 25 18:26:54 2003 UTC revision 2460 by bh, Wed Dec 15 14:01:04 2004 UTC
# Line 1  Line 1 
1  # Copyright (C) 2003 by Intevation GmbH  # Copyright (C) 2003, 2004 by Intevation GmbH
2  # Authors:  # Authors:
3  # Bernhard Herzog <[email protected]>  # Bernhard Herzog <[email protected]>
4  #  #
# Line 17  import time Line 17  import time
17  import popen2  import popen2
18  import shutil  import shutil
19  import traceback  import traceback
20    import re
21    
22  import support  import support
23    
# Line 184  class PostgreSQLServer: Line 185  class PostgreSQLServer:
185              raise RuntimeError("postmaster didn't start")              raise RuntimeError("postmaster didn't start")
186    
187      def is_running(self):      def is_running(self):
188          """Return true a postmaster process is running on self.dbdir          """Return whether a postmaster process is running on self.dbdir
189    
190          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
191          object has just been created it is possible that this method          that command succeeds and False otherwise.
192          returns true if there's still a postmaster process running for  
193          self.dbdir.          Note that it is possible that this method returns true even if
194            the PostgreSQLServer instance has just been created and
195            createdb() has not been called yet.  This can happen, for
196            instance, if the server has been started manually for debugging
197            purposes after a test suite run.
198          """          """
199          return run_boolean_command(["pg_ctl", "-D", self.dbdir, "status"])          return run_boolean_command(["pg_ctl", "-D", self.dbdir, "status"])
200    
# Line 198  class PostgreSQLServer: Line 203  class PostgreSQLServer:
203          run_command(["pg_ctl", "-m", "fast", "-D", self.dbdir, "stop"],          run_command(["pg_ctl", "-m", "fast", "-D", self.dbdir, "stop"],
204                      os.path.join(self.dbdir, "pg_ctl-stop.log"))                      os.path.join(self.dbdir, "pg_ctl-stop.log"))
205    
206      def new_postgis_db(self, dbname, tables = None):      def new_postgis_db(self, dbname, tables = None, reference_systems = None,
207                           views = None):
208          """Create and return a new PostGISDatabase object using self as server          """Create and return a new PostGISDatabase object using self as server
209          """          """
210          db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables)          db = PostGISDatabase(self, self.postgis_sql, dbname, tables = tables,
211                                 reference_systems = reference_systems,
212                                 views = views)
213          db.initdb()          db.initdb()
214          self.known_dbs[dbname] = db          self.known_dbs[dbname] = db
215          return db          return db
216    
217      def get_static_data_db(self, dbname, tables):      def get_static_data_db(self, dbname, tables, reference_systems, views):
218          """Return a PostGISDatabase for a database with the given static data          """Return a PostGISDatabase for a database with the given static data
219    
220          If no databasse of the name dbname exists, create a new one via          If no databasse of the name dbname exists, create a new one via
# Line 216  class PostgreSQLServer: Line 224  class PostgreSQLServer:
224          indicated data, return that. If the already existing db uses          indicated data, return that. If the already existing db uses
225          different data raise a value error.          different data raise a value error.
226    
227          The tables argument should be a sequence of table specifications          If the database doesn't exist, create a new one via
228          where each specifications is a (tablename, shapefilename) pair.          self.new_postgis_db.
229    
230            The parameters tables and reference_systems have the same
231            meaning as for new_postgis_db.
232          """          """
233          db = self.known_dbs.get(dbname)          db = self.known_dbs.get(dbname)
234          if db is not None:          if db is not None:
235              if db.has_data(tables):              if db.has_data(tables, reference_systems, views):
236                  return db                  return db
237              raise ValueError("PostGISDatabase named %r doesn't have tables %r"              raise ValueError("PostGISDatabase named %r doesn't have tables %r"
238                               % (dbname, tables))                               % (dbname, tables))
239          return self.new_postgis_db(dbname, tables)          return self.new_postgis_db(dbname, tables, reference_systems, views)
240    
241      def get_default_static_data_db(self):      def get_default_static_data_db(self):
242          dbname = "PostGISStaticTests"          dbname = "PostGISStaticTests"
243          tables = [("landmarks", os.path.join("..", "Data", "iceland",          srids = [(1, "proj=longlat datum=WGS84")]
244                                               "cultural_landmark-point.shp")),          tables = [
245                    ("political", os.path.join("..", "Data", "iceland",              # Direct copies of the shapefiles. The shapeids are exactly
246                # the same, except where changed with "gid_offset", of
247                # course.  Note that the test implementation requires that
248                # all the landmard tables use an gid_offset of 1000.
249                ("landmarks", os.path.join("..", "Data", "iceland",
250                                           "cultural_landmark-point.shp"),
251                 [("gid_offset", 1000)]),
252                ("political", os.path.join("..", "Data", "iceland",
253                                               "political.shp")),                                               "political.shp")),
254                ("roads", os.path.join("..", "Data", "iceland",
255                                             "roads-line.shp")),
256    
257                    # The polygon data as a MULTIPOLYGON geometry type              # The polygon data as a MULTIPOLYGON geometry type
258                    ("political_multi", os.path.join("..", "Data", "iceland",              ("political_multi", os.path.join("..", "Data", "iceland",
259                                               "political.shp"),                                               "political.shp"),
260                     "MULTIPOLYGON"),               [("force_wkt_type", "MULTIPOLYGON")]),
261    
262                    ("roads", os.path.join("..", "Data", "iceland",              # Copy of landmarks but using an srid != -1
263                                           "roads-line.shp"))]              ("landmarks_srid", os.path.join("..", "Data", "iceland",
264          return self.get_static_data_db(dbname, tables)                                         "cultural_landmark-point.shp"),
265                 [("gid_offset", 1000),
266                  ("srid", 1)]),
267    
268                # Copy of landmarks with a gid column called "point_id" instead
269                # of "gid" and using an srid != -1.
270                ("landmarks_point_id", os.path.join("..", "Data", "iceland",
271                                                    "cultural_landmark-point.shp"),
272                 [("gid_offset", 1000),
273                  ("srid", 1),
274                  ("gid_column", "point_id")]),
275                ]
276            views = [("v_landmarks", "SELECT * FROM landmarks_point_id")]
277            return self.get_static_data_db(dbname, tables, srids, views)
278    
279      def connection_params(self, user):      def connection_params(self, user):
280          """Return the connection parameters for the given user          """Return the connection parameters for the given user
# Line 271  class PostgreSQLServer: Line 304  class PostgreSQLServer:
304          return " ".join(params)          return " ".join(params)
305    
306      def execute_sql(self, dbname, user, sql):      def execute_sql(self, dbname, user, sql):
307          """Execute the sql statament          """Execute the sql statament and return a result for SELECT statements
308    
309          The user parameter us used as in connection_params. The dbname          The user parameter us used as in connection_params. The dbname
310          parameter must be the name of a database in the cluster.          parameter must be the name of a database in the cluster.  The
311            sql parameter is the SQL statement to execute as a string.  If
312            the string starts with 'select' (matched case insensitively) the
313            first row of the result will be returned.  Otherwise the return
314            value is None.
315          """          """
316          conn = psycopg.connect("dbname=%s " % dbname          conn = psycopg.connect("dbname=%s " % dbname
317                                 + self.connection_string(user))                                 + self.connection_string(user))
318          cursor = conn.cursor()          cursor = conn.cursor()
319          cursor.execute(sql)          cursor.execute(sql)
320            if sql.lower().startswith("select"):
321                row = cursor.fetchone()
322            else:
323                row = None
324          conn.commit()          conn.commit()
325          conn.close()          conn.close()
326            return row
327    
328        def server_version(self):
329            """Return the server version as a tuple (major, minor, patch)
330    
331            Each item in the tuple is an int.
332            """
333            result = self.execute_sql("template1", "admin", "SELECT version();")[0]
334            match = re.match(r"PostgreSQL (\d+\.\d+\.\d+)", result)
335            if match:
336                return tuple(map(int, match.group(1).split(".")))
337            else:
338                raise RutimeError("Cannot determine PostgreSQL server version"
339                                  " from %r" % result)
340    
341      def require_authentication(self, required):      def require_authentication(self, required):
342          """Switch authentication requirements on or off          """Switch authentication requirements on or off
# Line 293  class PostgreSQLServer: Line 348  class PostgreSQLServer:
348          corresponding call to switch it off again in the test case'          corresponding call to switch it off again in the test case'
349          tearDown method or in a finally: block.          tearDown method or in a finally: block.
350          """          """
351            # Starting with PostgreSQL 7.3 the pg_hba.conf file has an
352            # additional column with a username.  Query the server version
353            # and generate a file in the correct format.
354            if self.server_version() >= (7, 3):
355                user = "all"
356            else:
357                user = ""
358          if required:          if required:
359              contents = "local all password\n"              contents = "local all %s password\n" % user
360          else:          else:
361              contents = "local all trust\n"              contents = "local all %s trust\n" % user
362          f = open(os.path.join(self.dbdir, "pg_hba.conf"), "w")          f = open(os.path.join(self.dbdir, "pg_hba.conf"), "w")
363          f.write(contents)          f.write(contents)
364          f.close()          f.close()
# Line 319  class PostGISDatabase: Line 381  class PostGISDatabase:
381    
382      """A PostGIS database in a PostgreSQLServer"""      """A PostGIS database in a PostgreSQLServer"""
383    
384      def __init__(self, server, postgis_sql, dbname, tables = None):      def __init__(self, server, postgis_sql, dbname, tables = None,
385                     reference_systems = (), views = None):
386            """Initialize the PostGISDatabase
387    
388            Parameters:
389    
390                server -- The PostgreSQLServer instance containing the
391                    database
392    
393                postgis_sql -- Filename of the postgis.sql file with the
394                    postgis initialization code
395    
396                dbname -- The name of the database
397    
398                tables -- Optional description of tables to create in the
399                    new database. If given it should be a list of
400                    (tablename, shapefilename) pairs meaning that a table
401                    tablename will be created with the contents of the given
402                    shapefile or (tablename, shapefilename, extraargs)
403                    triples. The extraargs should be a list of key, value
404                    pairs to use as keyword arguments to upload_shapefile.
405    
406                reference_systems -- Optional description of spatial
407                    reference systems.  If given, it should be a sequence of
408                    (srid, params) pairs where srid is the srid defined by
409                    the proj4 paramter string params.  The srid can be given
410                    as an extra parameter in the tables list.
411    
412                views -- Optional description of views.  If given it should
413                    be a list of (viewname, select_stmt) pairs where
414                    viewname is the name of the view to be created and
415                    select_stmt is the select statement to use as the basis.
416                    The views will be created after the tables and may refer
417                    to them in the select_stmt.
418            """
419          self.server = server          self.server = server
420          self.postgis_sql = postgis_sql          self.postgis_sql = postgis_sql
421          self.dbname = dbname          self.dbname = dbname
422          self.tables = tables          self.tables = tables
423            self.views = views
424            if reference_systems:
425                self.reference_systems = reference_systems
426            else:
427                # Make sure that it's a sequence we can iterate over even if
428                # the parameter's None
429                self.reference_systems = ()
430    
431      def initdb(self):      def initdb(self):
432          """Remove the old db directory and create and initialize a new database          """Remove the old db directory and create and initialize a new database
# Line 349  class PostGISDatabase: Line 452  class PostGISDatabase:
452    
453          self.server.execute_sql(self.dbname, "admin",          self.server.execute_sql(self.dbname, "admin",
454                                  "GRANT SELECT ON geometry_columns TO PUBLIC;")                                  "GRANT SELECT ON geometry_columns TO PUBLIC;")
455            self.server.execute_sql(self.dbname, "admin",
456                                    "GRANT SELECT ON spatial_ref_sys TO PUBLIC;")
457    
458            for srid, params in self.reference_systems:
459                self.server.execute_sql(self.dbname, "admin",
460                                        "INSERT INTO spatial_ref_sys VALUES"
461                                        " (%d, '', %d, '', '%s');"
462                                        % (srid, srid, params))
463          if self.tables is not None:          if self.tables is not None:
464              for info  in self.tables:              def unpack(item):
465                    extra = {"force_wkt_type": None, "gid_offset": 0,
466                             "srid": -1}
467                  if len(info) == 2:                  if len(info) == 2:
468                      tablename, shapefile = info                      tablename, shapefile = info
                     wkt_type = None  
469                  else:                  else:
470                      tablename, shapefile, wkt_type = info                      tablename, shapefile, kw = info
471                  upload_shapefile(shapefile, self, tablename,                      for key, val in kw:
472                                   force_wkt_type = wkt_type)                          extra[key] = val
473                    return tablename, shapefile, extra
474      def has_data(self, tables):  
475          return self.tables == tables              for info in self.tables:
476                    tablename, shapefile, kw = unpack(info)
477                    upload_shapefile(shapefile, self, tablename, **kw)
478    
479            if self.views is not None:
480                for viewname, select_stmt in self.views:
481                    self.server.execute_sql(self.dbname, "admin",
482                                            "CREATE VIEW %s AS %s" % (viewname,
483                                                                      select_stmt))
484                    self.server.execute_sql(self.dbname, "admin",
485                                            "GRANT SELECT ON %s TO PUBLIC;"
486                                            % viewname)
487    
488        def has_data(self, tables, reference_systems, views):
489            return (self.tables == tables
490                    and self.reference_systems == reference_systems
491                    and self.views == views)
492    
493    
494  def find_postgis_sql():  def find_postgis_sql():
# Line 420  def reason_for_not_running_tests(): Line 547  def reason_for_not_running_tests():
547         The name of the postgis_sql file is determined by find_postgis_sql()         The name of the postgis_sql file is determined by find_postgis_sql()
548       - psycopg can be imported successfully.       - psycopg can be imported successfully.
549      """      """
550        # run_command currently uses Popen4 which is not available under
551        # Windows, for example.
552        if not hasattr(popen2, "Popen4"):
553            return "Can't run PostGIS test because popen2.Popen4 does not exist"
554    
555      try:      try:
556          run_command(["pg_ctl", "--help"], None)          run_command(["pg_ctl", "--help"], None)
557      except RuntimeError:      except RuntimeError:
# Line 452  def skip_if_no_postgis(): Line 584  def skip_if_no_postgis():
584      if _cannot_run_postgis_tests:      if _cannot_run_postgis_tests:
585          raise support.SkipTest(_cannot_run_postgis_tests)          raise support.SkipTest(_cannot_run_postgis_tests)
586    
587    def skip_if_addgeometrycolumn_does_not_use_quote_ident():
588        """Skip a test if the AddGeometryColumn function doesn't use quote_ident
589    
590        If the AddGeometryColumn function doesn't use quote_ident it doesn't
591        support unusual table or column names properly, that is, it will
592        fail with errors for names that contain spaces or double quotes.
593    
594        The test performed by this function is a bit simplistic because it
595        only tests whether the string 'quote_ident' occurs anywhere in the
596        postgis.sql file. This will hopefully work because when this was
597        fixed in postgis CVS AddGeometryColumn was the first function to use
598        quote_ident.
599        """
600        f = file(find_postgis_sql())
601        content = f.read()
602        f.close()
603        if content.find("quote_ident") < 0:
604            raise support.SkipTest("AddGeometryColumn doesn't use quote_ident")
605    
606  def coords_to_point(coords):  def coords_to_point(coords):
607      """Return string with a WKT representation of the point in coords"""      """Return string with a WKT representation of the point in coords"""
608      x, y = coords[0]      x, y = coords[0]
# Line 485  wkt_converter = { Line 636  wkt_converter = {
636      "MULTIPOLYGON": coords_to_multipolygon,      "MULTIPOLYGON": coords_to_multipolygon,
637      }      }
638    
639  def upload_shapefile(filename, db, tablename, force_wkt_type = None):  def upload_shapefile(filename, db, tablename, force_wkt_type = None,
640                         gid_offset = 0, gid_column = "gid", srid = -1):
641        """Upload a shapefile into a new database table
642    
643        Parameters:
644    
645        filename -- The name of the shapefile
646    
647        db -- The PostGISDatabase instance representing the database
648    
649        tablename -- The name of the table to create and into which the data
650                    is to be inserted
651    
652        force_wkt_type -- If given the real WKT geometry type to use instead
653                    of the default that would be chosen based on the type of
654                    the shapefile
655    
656        gid_offset -- A number to add to the shapeid to get the value for
657                    the gid column (default 0)
658    
659        gid_column -- The name of the column with the shape ids.  Default
660                      'gid'.  If None, no gid column will be created.  The
661                      name is directly used in SQL statements, so if it
662                      contains unusualy characters the caller should provide
663                      a suitable quoted string.
664    
665        srid -- The srid of the spatial references system used by the table
666                and the data
667        """
668      import dbflib, shapelib      import dbflib, shapelib
669    
670      # We build this map here because we need shapelib which can only be      # We build this map here because we need shapelib which can only be
# Line 509  def upload_shapefile(filename, db, table Line 688  def upload_shapefile(filename, db, table
688                 dbflib.FTInteger: "INTEGER",                 dbflib.FTInteger: "INTEGER",
689                 dbflib.FTDouble: "DOUBLE PRECISION"}                 dbflib.FTDouble: "DOUBLE PRECISION"}
690    
691      insert_formats = ["%(gid)s"]      insert_formats = []
692      fields = ["gid INT"]      if gid_column:
693            insert_formats.append("%(gid)s")
694    
695        fields = []
696        fields_decl = []
697        if gid_column:
698            fields.append(gid_column)
699            fields_decl.append("%s INT" % gid_column)
700      for i in range(dbf.field_count()):      for i in range(dbf.field_count()):
701          ftype, name, width, prec = dbf.field_info(i)          ftype, name, width, prec = dbf.field_info(i)
702          fields.append("%s %s" % (name, typemap[ftype]))          fields.append(name)
703            fields_decl.append("%s %s" % (name, typemap[ftype]))
704          insert_formats.append("%%(%s)s" % name)          insert_formats.append("%%(%s)s" % name)
705      stmt = "CREATE TABLE %s (\n    %s\n);" % (tablename,      stmt = "CREATE TABLE %s (\n    %s\n);" % (tablename,
706                                                ",\n    ".join(fields))                                                ",\n    ".join(fields_decl))
707      cursor.execute(stmt)      cursor.execute(stmt)
708      #print stmt      #print stmt
709    
# Line 527  def upload_shapefile(filename, db, table Line 714  def upload_shapefile(filename, db, table
714      convert = wkt_converter[wkttype]      convert = wkt_converter[wkttype]
715    
716      cursor.execute("select AddGeometryColumn('%(dbname)s',"      cursor.execute("select AddGeometryColumn('%(dbname)s',"
717                     "'%(tablename)s', 'the_geom', '-1', '%(wkttype)s', 2);"                     "'%(tablename)s', 'the_geom', %(srid)d, '%(wkttype)s', 2);"
718                     % locals())                     % locals())
719        fields.append("the_geom")
720        insert_formats.append("GeometryFromText(%(the_geom)s, %(srid)d)")
721    
722      insert_formats.append("GeometryFromText(%(the_geom)s, -1)")      insert = ("INSERT INTO %s (%s) VALUES (%s)"
723                  % (tablename, ", ".join(fields), ", ".join(insert_formats)))
     insert = ("INSERT INTO %s VALUES (%s)"  
               % (tablename, ", ".join(insert_formats)))  
724    
725      for i in range(numshapes):      for i in range(numshapes):
726          data = dbf.read_record(i)          data = dbf.read_record(i)
727          data["tablename"] = tablename          data["tablename"] = tablename
728          data["gid"] = i          if gid_column:
729                data["gid"] = i + gid_offset
730            data["srid"] = srid
731          data["the_geom"] = convert(shp.read_object(i).vertices())          data["the_geom"] = convert(shp.read_object(i).vertices())
732          #print insert % data          #print insert % data
733          cursor.execute(insert, data)          cursor.execute(insert, data)
Legend:
Removed from v.1656  
changed lines
  Added in v.2460
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26