/[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 2057 by bh, Tue Feb 10 15:51:57 2004 UTC revision 2543 by bh, Fri Jan 21 16:58:31 2005 UTC
# Line 1  Line 1 
1  # Copyright (C) 2003, 2004 by Intevation GmbH  # Copyright (C) 2003, 2004, 2005 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, reference_systems = 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)                               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, reference_systems):      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 225  class PostgreSQLServer: Line 232  class PostgreSQLServer:
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, reference_systems):              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, reference_systems)          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"
# Line 237  class PostgreSQLServer: Line 244  class PostgreSQLServer:
244          tables = [          tables = [
245              # Direct copies of the shapefiles. The shapeids are exactly              # Direct copies of the shapefiles. The shapeids are exactly
246              # the same, except where changed with "gid_offset", of              # the same, except where changed with "gid_offset", of
247              # course              # 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",              ("landmarks", os.path.join("..", "Data", "iceland",
250                                         "cultural_landmark-point.shp"),                                         "cultural_landmark-point.shp"),
251               [("gid_offset", 1000)]),               [("gid_offset", 1000)]),
252              ("political", os.path.join("..", "Data", "iceland",              ("political", os.path.join("..", "Data", "iceland",
253                                               "political.shp")),                                               "political.shp")),
254              ("roads", os.path.join("..", "Data", "iceland",              ("roads_multi", os.path.join("..", "Data", "iceland",
255                                           "roads-line.shp")),                                           "roads-line.shp")),
256    
257                # same as roads-multi but using LINESTRING instead of
258                # MULTILINESTRING
259                ("roads", os.path.join("..", "Data", "iceland",
260                                             "roads-line.shp"),
261                 [("force_wkt_type", "LINESTRING")]),
262    
263              # The polygon data as a MULTIPOLYGON geometry type              # The polygon data as a MULTIPOLYGON geometry type
264              ("political_multi", os.path.join("..", "Data", "iceland",              ("political_multi", os.path.join("..", "Data", "iceland",
265                                               "political.shp"),                                               "political.shp"),
266               [("force_wkt_type", "MULTIPOLYGON")]),               [("force_wkt_type", "MULTIPOLYGON")]),
267    
268              # Copy of landmarks but using an srid              # Copy of landmarks but using an srid != -1
269              ("landmarks_srid", os.path.join("..", "Data", "iceland",              ("landmarks_srid", os.path.join("..", "Data", "iceland",
270                                         "cultural_landmark-point.shp"),                                         "cultural_landmark-point.shp"),
271               [("gid_offset", 1000),               [("gid_offset", 1000),
272                ("srid", 1)]),                ("srid", 1)]),
273    
274                # Copy of landmarks with a gid column called "point_id" instead
275                # of "gid" and using an srid != -1.
276                ("landmarks_point_id", os.path.join("..", "Data", "iceland",
277                                                    "cultural_landmark-point.shp"),
278                 [("gid_offset", 1000),
279                  ("srid", 1),
280                  ("gid_column", "point_id")]),
281              ]              ]
282          return self.get_static_data_db(dbname, tables, srids)          views = [("v_landmarks", "SELECT * FROM landmarks_point_id")]
283            return self.get_static_data_db(dbname, tables, srids, views)
284    
285      def connection_params(self, user):      def connection_params(self, user):
286          """Return the connection parameters for the given user          """Return the connection parameters for the given user
# Line 287  class PostgreSQLServer: Line 310  class PostgreSQLServer:
310          return " ".join(params)          return " ".join(params)
311    
312      def execute_sql(self, dbname, user, sql):      def execute_sql(self, dbname, user, sql):
313          """Execute the sql statament          """Execute the sql statament and return a result for SELECT statements
314    
315          The user parameter us used as in connection_params. The dbname          The user parameter us used as in connection_params. The dbname
316          parameter must be the name of a database in the cluster.          parameter must be the name of a database in the cluster.  The
317            sql parameter is the SQL statement to execute as a string.  If
318            the string starts with 'select' (matched case insensitively) the
319            first row of the result will be returned.  Otherwise the return
320            value is None.
321          """          """
322          conn = psycopg.connect("dbname=%s " % dbname          conn = psycopg.connect("dbname=%s " % dbname
323                                 + self.connection_string(user))                                 + self.connection_string(user))
324          cursor = conn.cursor()          cursor = conn.cursor()
325          cursor.execute(sql)          cursor.execute(sql)
326            if sql.lower().startswith("select"):
327                row = cursor.fetchone()
328            else:
329                row = None
330          conn.commit()          conn.commit()
331          conn.close()          conn.close()
332            return row
333    
334        def server_version(self):
335            """Return the server version as a tuple (major, minor, patch)
336    
337            Each item in the tuple is an int.
338            """
339            result = self.execute_sql("template1", "admin", "SELECT version();")[0]
340            match = re.match(r"PostgreSQL (\d+\.\d+\.\d+)", result)
341            if match:
342                return tuple(map(int, match.group(1).split(".")))
343            else:
344                raise RutimeError("Cannot determine PostgreSQL server version"
345                                  " from %r" % result)
346    
347      def require_authentication(self, required):      def require_authentication(self, required):
348          """Switch authentication requirements on or off          """Switch authentication requirements on or off
# Line 309  class PostgreSQLServer: Line 354  class PostgreSQLServer:
354          corresponding call to switch it off again in the test case'          corresponding call to switch it off again in the test case'
355          tearDown method or in a finally: block.          tearDown method or in a finally: block.
356          """          """
357            # Starting with PostgreSQL 7.3 the pg_hba.conf file has an
358            # additional column with a username.  Query the server version
359            # and generate a file in the correct format.
360            if self.server_version() >= (7, 3):
361                user = "all"
362            else:
363                user = ""
364          if required:          if required:
365              contents = "local all password\n"              contents = "local all %s password\n" % user
366          else:          else:
367              contents = "local all trust\n"              contents = "local all %s trust\n" % user
368          f = open(os.path.join(self.dbdir, "pg_hba.conf"), "w")          f = open(os.path.join(self.dbdir, "pg_hba.conf"), "w")
369          f.write(contents)          f.write(contents)
370          f.close()          f.close()
# Line 336  class PostGISDatabase: Line 388  class PostGISDatabase:
388      """A PostGIS database in a PostgreSQLServer"""      """A PostGIS database in a PostgreSQLServer"""
389    
390      def __init__(self, server, postgis_sql, dbname, tables = None,      def __init__(self, server, postgis_sql, dbname, tables = None,
391                   reference_systems = ()):                   reference_systems = (), views = None):
392          """Initialize the PostGISDatabase          """Initialize the PostGISDatabase
393    
394          Parameters:          Parameters:
# Line 344  class PostGISDatabase: Line 396  class PostGISDatabase:
396              server -- The PostgreSQLServer instance containing the              server -- The PostgreSQLServer instance containing the
397                  database                  database
398    
399              postgis_sql -- Filename of the postgis.sql file with the              postgis_sql -- Filename of the sql file with the postgis
400                  postgis initialization code                  initialization code
401    
402              dbname -- The name of the database              dbname -- The name of the database
403    
# Line 362  class PostGISDatabase: Line 414  class PostGISDatabase:
414                  (srid, params) pairs where srid is the srid defined by                  (srid, params) pairs where srid is the srid defined by
415                  the proj4 paramter string params.  The srid can be given                  the proj4 paramter string params.  The srid can be given
416                  as an extra parameter in the tables list.                  as an extra parameter in the tables list.
417    
418                views -- Optional description of views.  If given it should
419                    be a list of (viewname, select_stmt) pairs where
420                    viewname is the name of the view to be created and
421                    select_stmt is the select statement to use as the basis.
422                    The views will be created after the tables and may refer
423                    to them in the select_stmt.
424          """          """
425          self.server = server          self.server = server
426          self.postgis_sql = postgis_sql          self.postgis_sql = postgis_sql
427          self.dbname = dbname          self.dbname = dbname
428          self.tables = tables          self.tables = tables
429            self.views = views
430          if reference_systems:          if reference_systems:
431              self.reference_systems = reference_systems              self.reference_systems = reference_systems
432          else:          else:
# Line 422  class PostGISDatabase: Line 482  class PostGISDatabase:
482                  tablename, shapefile, kw = unpack(info)                  tablename, shapefile, kw = unpack(info)
483                  upload_shapefile(shapefile, self, tablename, **kw)                  upload_shapefile(shapefile, self, tablename, **kw)
484    
485      def has_data(self, tables, reference_systems):          if self.views is not None:
486                for viewname, select_stmt in self.views:
487                    self.server.execute_sql(self.dbname, "admin",
488                                            "CREATE VIEW %s AS %s" % (viewname,
489                                                                      select_stmt))
490                    self.server.execute_sql(self.dbname, "admin",
491                                            "GRANT SELECT ON %s TO PUBLIC;"
492                                            % viewname)
493    
494        def has_data(self, tables, reference_systems, views):
495          return (self.tables == tables          return (self.tables == tables
496                  and self.reference_systems == reference_systems)                  and self.reference_systems == reference_systems
497                    and self.views == views)
498    
499    
500  def find_postgis_sql():  def find_postgis_sql():
501      """Return the name of the postgis_sql file      """Return the name of the postgis_sql file
502    
503      A postgis installation usually has the postgis_sql file in      A postgis installation usually has the postgis_sql file in
504      PostgreSQL's datadir (i.e. the directory where PostgreSQL keeps      PostgreSQL's $datadir (i.e. the directory where PostgreSQL keeps
505      static files, not the directory containing the databases).      static files, not the directory containing the databases).
506      Unfortunately there's no way to determine the name of this directory      Unfortunately there's no way to determine the name of this directory
507      with pg_config so we assume here that it's      with pg_config so we assume here that it's
508      $bindir/../share/postgresql/.      $bindir/../share/postgresql/.
509    
510        Furthermore, different versions of postgis place the file in
511        slightly different locations or may even use different names.  For
512        instance:
513    
514          postgis 0.7.5        $datadir/contrib/postgis.sql
515          postgis 0.8.1        $datadir/postgis.sql
516          postgis 1.0.0-rc1    $datadir/lwpostgis.sql
517    
518        To support both versions, we look in both places and return the
519        first one found (looking under contrib first).  If the file is not
520        found the return value is None.
521      """      """
522      bindir = run_config_script("pg_config --bindir").strip()      bindir = run_config_script("pg_config --bindir").strip()
523      return os.path.join(bindir, "..", "share", "postgresql",      datadir = os.path.join(bindir, "..", "share", "postgresql")
524                          "contrib", "postgis.sql")      for filename in [os.path.join(datadir, "contrib", "postgis.sql"),
525                         os.path.join(datadir, "postgis.sql"),
526                         os.path.join(datadir, "lwpostgis.sql")]:
527            if os.path.exists(filename):
528                return filename
529    
530    
531  _postgres_server = None  _postgres_server = None
532  def get_test_server():  def get_test_server():
# Line 529  def skip_if_addgeometrycolumn_does_not_u Line 616  def skip_if_addgeometrycolumn_does_not_u
616    
617      The test performed by this function is a bit simplistic because it      The test performed by this function is a bit simplistic because it
618      only tests whether the string 'quote_ident' occurs anywhere in the      only tests whether the string 'quote_ident' occurs anywhere in the
619      postgis.sql file. This will hopefully works because when this was      postgis.sql file. This will hopefully work because when this was
620      fixed in postgis CVS AddGeometryColumn was the first function to use      fixed in postgis CVS AddGeometryColumn was the first function to use
621      quote_ident.      quote_ident.
622      """      """
# Line 551  def coords_to_polygon(coords): Line 638  def coords_to_polygon(coords):
638          poly.append(", ".join(["%r %r" % p for p in ring]))          poly.append(", ".join(["%r %r" % p for p in ring]))
639      return "POLYGON((%s))" % "), (".join(poly)      return "POLYGON((%s))" % "), (".join(poly)
640    
641    def coords_to_linestring(coords):
642        """Return string with a LINESTRING WKT representation of coords"""
643        if len(coords) > 1:
644            raise ValueError("A LINESTRING can only have one arc")
645        return "LINESTRING(%s)" % ", ".join(["%r %r" % p for p in coords[0]])
646    
647  def coords_to_multilinestring(coords):  def coords_to_multilinestring(coords):
648      """Return string with a WKT representation of the arc in coords"""      """Return string with a MULTILINESTRING WKT representation of coords"""
649      poly = []      poly = []
650      for ring in coords:      for ring in coords:
651          poly.append(", ".join(["%r %r" % p for p in ring]))          poly.append(", ".join(["%r %r" % p for p in ring]))
# Line 567  def coords_to_multipolygon(coords): Line 660  def coords_to_multipolygon(coords):
660    
661  wkt_converter = {  wkt_converter = {
662      "POINT": coords_to_point,      "POINT": coords_to_point,
663        "LINESTRING": coords_to_linestring,
664      "MULTILINESTRING": coords_to_multilinestring,      "MULTILINESTRING": coords_to_multilinestring,
665      "POLYGON": coords_to_polygon,      "POLYGON": coords_to_polygon,
666      "MULTIPOLYGON": coords_to_multipolygon,      "MULTIPOLYGON": coords_to_multipolygon,
667      }      }
668    
669  def upload_shapefile(filename, db, tablename, force_wkt_type = None,  def upload_shapefile(filename, db, tablename, force_wkt_type = None,
670                       gid_offset = 0, srid = -1):                       gid_offset = 0, gid_column = "gid", srid = -1):
671      """Upload a shapefile into a new database table      """Upload a shapefile into a new database table
672    
673      Parameters:      Parameters:
# Line 585  def upload_shapefile(filename, db, table Line 679  def upload_shapefile(filename, db, table
679      tablename -- The name of the table to create and into which the data      tablename -- The name of the table to create and into which the data
680                  is to be inserted                  is to be inserted
681    
682      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
683                  of the default that would be chosen based on the type of                  geometry type to use instead of the default that would
684                  the shapefile                  be chosen based on the type of the shapefile
685    
686      gid_offset -- A number to add to the shapeid to get the value for      gid_offset -- A number to add to the shapeid to get the value for
687                  the gid column (default 0)                  the gid column (default 0)
688    
689        gid_column -- The name of the column with the shape ids.  Default
690                      'gid'.  If None, no gid column will be created.  The
691                      name is directly used in SQL statements, so if it
692                      contains unusualy characters the caller should provide
693                      a suitable quoted string.
694    
695      srid -- The srid of the spatial references system used by the table      srid -- The srid of the spatial references system used by the table
696              and the data              and the data
697      """      """
# Line 618  def upload_shapefile(filename, db, table Line 718  def upload_shapefile(filename, db, table
718                 dbflib.FTInteger: "INTEGER",                 dbflib.FTInteger: "INTEGER",
719                 dbflib.FTDouble: "DOUBLE PRECISION"}                 dbflib.FTDouble: "DOUBLE PRECISION"}
720    
721      insert_formats = ["%(gid)s"]      insert_formats = []
722      fields = ["gid INT"]      if gid_column:
723            insert_formats.append("%(gid)s")
724    
725        fields = []
726        fields_decl = []
727        if gid_column:
728            fields.append(gid_column)
729            fields_decl.append("%s INT" % gid_column)
730      for i in range(dbf.field_count()):      for i in range(dbf.field_count()):
731          ftype, name, width, prec = dbf.field_info(i)          ftype, name, width, prec = dbf.field_info(i)
732          fields.append("%s %s" % (name, typemap[ftype]))          fields.append(name)
733            fields_decl.append("%s %s" % (name, typemap[ftype]))
734          insert_formats.append("%%(%s)s" % name)          insert_formats.append("%%(%s)s" % name)
735      stmt = "CREATE TABLE %s (\n    %s\n);" % (tablename,      stmt = "CREATE TABLE %s (\n    %s\n);" % (tablename,
736                                                ",\n    ".join(fields))                                                ",\n    ".join(fields_decl))
737      cursor.execute(stmt)      cursor.execute(stmt)
738      #print stmt      #print stmt
739    
# Line 638  def upload_shapefile(filename, db, table Line 746  def upload_shapefile(filename, db, table
746      cursor.execute("select AddGeometryColumn('%(dbname)s',"      cursor.execute("select AddGeometryColumn('%(dbname)s',"
747                     "'%(tablename)s', 'the_geom', %(srid)d, '%(wkttype)s', 2);"                     "'%(tablename)s', 'the_geom', %(srid)d, '%(wkttype)s', 2);"
748                     % locals())                     % locals())
749        fields.append("the_geom")
750      insert_formats.append("GeometryFromText(%(the_geom)s, %(srid)d)")      insert_formats.append("GeometryFromText(%(the_geom)s, %(srid)d)")
751    
752      insert = ("INSERT INTO %s VALUES (%s)"      insert = ("INSERT INTO %s (%s) VALUES (%s)"
753                % (tablename, ", ".join(insert_formats)))                % (tablename, ", ".join(fields), ", ".join(insert_formats)))
754    
755      for i in range(numshapes):      for i in range(numshapes):
756          data = dbf.read_record(i)          data = dbf.read_record(i)
757          data["tablename"] = tablename          data["tablename"] = tablename
758          data["gid"] = i + gid_offset          if gid_column:
759                data["gid"] = i + gid_offset
760          data["srid"] = srid          data["srid"] = srid
761          data["the_geom"] = convert(shp.read_object(i).vertices())          data["the_geom"] = convert(shp.read_object(i).vertices())
762          #print insert % data          #print insert % data
Legend:
Removed from v.2057  
changed lines
  Added in v.2543
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26