/[thuban]/branches/WIP-pyshapelib-bramz/Thuban/Model/postgisdb.py
ViewVC logotype

Diff of /branches/WIP-pyshapelib-bramz/Thuban/Model/postgisdb.py

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

revision 1662 by bh, Wed Aug 27 13:51:01 2003 UTC revision 2059 by bh, Wed Feb 11 09:05:40 2004 UTC
# Line 1  Line 1 
1  # Copyright (C) 2003 by Intevation GmbH  # Copyright (C) 2003, 2004 by Intevation GmbH
2  # Authors:  # Authors:
3  # Martin Mueller <[email protected]>  # Martin Mueller <[email protected]>
4  # Bernhard Herzog <[email protected]>  # Bernhard Herzog <[email protected]>
# Line 37  if psycopg is not None: Line 37  if psycopg is not None:
37                  (psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]                  (psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]
38    
39    
40    def quote_identifier(ident):
41        """Return a quoted version of the identifier ident.
42    
43        The return value is a string that can be put directly into an SQL
44        statement.  The quoted identifier is surrounded by double quotes and
45        any double quotes already in the input value are converted to two
46        double quotes.  Examples:
47    
48        >>> quote_identifier("abc\"def")
49        '"abc""def"'
50        >>> quote_identifier("abc def")
51        '"abc def"'
52        """
53        return '"' + '""'.join(ident.split('"')) + '"'
54    
55    
56  class ConnectionError(Exception):  class ConnectionError(Exception):
57    
58      """Class for exceptions occurring when establishing a Databse connection"""      """Class for exceptions occurring when establishing a Databse connection"""
# Line 99  class PostGISConnection: Line 115  class PostGISConnection:
115          return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"          return ("postgis://%(user)s@%(host)s:%(port)s/%(dbname)s"
116                  % self.__dict__)                  % self.__dict__)
117    
118        def MatchesParameters(self, parameters):
119            """Return whether the connection matches the dictionary of parameters
120    
121            Return whether instatiating the connection with the given
122            parameters would establish essentially the same connection as
123            self. The connection is essentially the same if the same
124            database (identified by host, port and databasename) is accessed
125            as the same user.
126            """
127            return (parameters["host"] == self.host
128                    and parameters["port"] == self.port
129                    and parameters["dbname"] == self.dbname
130                    and parameters["user"] == self.user)
131    
132      def Close(self):      def Close(self):
133          """Close the database connection"""          """Close the database connection"""
134          self.connection.close()          self.connection.close()
# Line 119  class PostGISConnection: Line 149  class PostGISConnection:
149    
150  class PostGISColumn:  class PostGISColumn:
151    
152      """Column description for a PostGISTable"""      """Column description for a PostGISTable
153    
154        In addition to the normal column object attributes name, type and
155        index, PostGISColumn objects have a quoted_name attribute which
156        contains a quoted version of name for use in SQL statements. The
157        quoted_name attribute is mainly intended for internal use by the
158        PostGISTable class.
159        """
160    
161      def __init__(self, name, type, index):      def __init__(self, name, type, index):
162          self.name = name          self.name = name
163            self.quoted_name = quote_identifier(name)
164          self.type = type          self.type = type
165          self.index = index          self.index = index
166    
167    
168  class PostGISTable:  class PostGISTable:
169    
170      """A Table in a PostGIS database"""      """A Table in a PostGIS database
171    
172        A PostgreSQL table may contain columns with types not (yet)
173        supported by Thuban. Instances of this class ignore those columns
174        and pretend they don't exist, i.e. they won't show up in the column
175        descriptions returned by Columns() and other methods.
176        """
177    
178      def __init__(self, db, tablename):      def __init__(self, db, tablename):
179          """Initialize the PostGISTable.          """Initialize the PostGISTable.
# Line 139  class PostGISTable: Line 183  class PostGISTable:
183          """          """
184          self.db = db          self.db = db
185          self.tablename = tablename          self.tablename = tablename
186            # Tablename quoted for use in SQL statements.
187            self.quoted_tablename = quote_identifier(tablename)
188    
189            # Map column names and indices to column objects.
190          self.column_map = {}          self.column_map = {}
191    
192          self._fetch_table_information()          self._fetch_table_information()
193    
194      def _fetch_table_information(self):      def _fetch_table_information(self):
195          """Internal: Update information about the table"""          """Internal: Update information about the table"""
196          self.columns = []          self.columns = []
197          cursor = self.db.cursor()          cursor = self.db.cursor()
198          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename)          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
199          description = cursor.description          description = cursor.description
200    
201          for i in range(len(description)):          for i in range(len(description)):
202              for pgtyp, tabletyp in type_map:              col = self._create_col_from_description(i, description[i])
203                  if pgtyp == description[i][1]:              if col is not None:
204                      col = PostGISColumn(description[i][0], tabletyp, i)                  self.columns.append(col)
                     break  
             else:  
                 if description[i][1] == self.db.geometry_type:  
                     self.geometry_column = description[i][0]  
                 # No matching table type. Ignore the column.  
                 # FIXME: We should at least print a warning about  
                 # ignored columns  
                 continue  
             self.columns.append(col)  
205    
206          for col in self.columns:          for col in self.columns:
207              self.column_map[col.name] = col              self.column_map[col.name] = col
# Line 169  class PostGISTable: Line 209  class PostGISTable:
209    
210          # Build query string for ReadRowAsDict          # Build query string for ReadRowAsDict
211          self.query_stmt = ("SELECT %s from %s"          self.query_stmt = ("SELECT %s from %s"
212                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.quoted_name
213                                self.tablename))                                           for col in self.columns]),
214                                  self.quoted_tablename))
215    
216        def _create_col_from_description(self, index, description):
217            """Return the column object for the column described by description
218    
219            The parameter index is the index of the column.  The description
220            is a sequence taken from the cursor's description attribute for
221            the column.  That means description[0] is the name of the column
222            and description[1] the type.
223    
224            Return None if the column can't be represented for some reason,
225            e.g. because its type is not yet supported or needs to be
226            treated in some special way.  Derived classes may extend this
227            method.
228            """
229            for pgtyp, tabletyp in type_map:
230                if pgtyp == description[1]:
231                    return PostGISColumn(description[0], tabletyp,
232                                         len(self.columns))
233            return None
234    
235      def DBConnection(self):      def DBConnection(self):
236          """Return the dbconnection used by the table"""          """Return the dbconnection used by the table"""
# Line 206  class PostGISTable: Line 266  class PostGISTable:
266    
267      def NumRows(self):      def NumRows(self):
268          cursor = self.db.cursor()          cursor = self.db.cursor()
269          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
270          return cursor.fetchone()[0]          return cursor.fetchone()[0]
271    
272      def RowIdToOrdinal(self, gid):      def RowIdToOrdinal(self, gid):
273          """Return the row ordinal given its id"""          """Return the row ordinal given its id"""
274          cursor = self.db.cursor()          cursor = self.db.cursor()
275          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
276                         % (self.tablename, gid))                         % (self.quoted_tablename, gid))
277          return cursor.fetchone()[0]          return cursor.fetchone()[0]
278    
279      def RowOrdinalToId(self, num):      def RowOrdinalToId(self, num):
280          """Return the rowid for given its ordinal"""          """Return the rowid for given its ordinal"""
281          cursor = self.db.cursor()          cursor = self.db.cursor()
282          cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"          cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
283                         % (self.tablename, num))                         % (self.quoted_tablename, num))
284          return cursor.fetchone()[0]          return cursor.fetchone()[0]
285    
286      def ReadRowAsDict(self, row, row_is_ordinal = 0):      def ReadRowAsDict(self, row, row_is_ordinal = 0):
# Line 239  class PostGISTable: Line 299  class PostGISTable:
299          cursor = self.db.cursor()          cursor = self.db.cursor()
300          if row_is_ordinal:          if row_is_ordinal:
301              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
302                      (self.column_map[col].name, self.tablename, row))                      (self.column_map[col].quoted_name, self.quoted_tablename,
303                         row))
304          else:          else:
305              stmt = ("SELECT %s FROM %s WHERE gid = %d" %              stmt = ("SELECT %s FROM %s WHERE gid = %d" %
306                      (self.column_map[col].name, self.tablename, row))                      (self.column_map[col].quoted_name, self.quoted_tablename,
307                         row))
308          cursor.execute(stmt)          cursor.execute(stmt)
309          return cursor.fetchone()[0]          return cursor.fetchone()[0]
310    
311      def ValueRange(self, col):      def ValueRange(self, col):
312          cursor = self.db.cursor()          cursor = self.db.cursor()
313          name = self.column_map[col].name          name = self.column_map[col].quoted_name
314          cursor.execute("SELECT min(%s), max(%s) FROM %s" %          cursor.execute("SELECT min(%s), max(%s) FROM %s" %
315                         (name, name, self.tablename))                         (name, name, self.quoted_tablename))
316          return tuple(cursor.fetchone())          return tuple(cursor.fetchone())
317    
318      def UniqueValues(self, col):      def UniqueValues(self, col):
319          cursor = self.db.cursor()          cursor = self.db.cursor()
320          name = self.column_map[col].name          name = self.column_map[col].quoted_name
321          cursor.execute("SELECT %s FROM %s GROUP BY %s" %          cursor.execute("SELECT %s FROM %s GROUP BY %s" %
322                         (name, self.tablename, name))                         (name, self.quoted_tablename, name))
323          return [row[0] for row in cursor.fetchall()]          return [row[0] for row in cursor.fetchall()]
324    
325      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
# Line 268  class PostGISTable: Line 330  class PostGISTable:
330              comparison = "="              comparison = "="
331    
332          if isinstance(right, PostGISColumn):          if isinstance(right, PostGISColumn):
333              right_template = right.name              right_template = right.quoted_name
334              params = ()              params = ()
335          else:          else:
336              right_template = "%s"              right_template = "%s"
337              params = (right,)              params = (right,)
338    
339          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \
340                  % (self.tablename, left.name, comparison, right_template)                  % (self.quoted_tablename, left.quoted_name, comparison,
341                       right_template)
342    
343          cursor = self.db.cursor()          cursor = self.db.cursor()
344          cursor.execute(query, params)          cursor.execute(query, params)
# Line 326  class PostGISShapeStore(PostGISTable): Line 389  class PostGISShapeStore(PostGISTable):
389    
390      """Shapestore interface to a table in a PostGIS database"""      """Shapestore interface to a table in a PostGIS database"""
391    
392        def __init__(self, db, tablename, geometry_column = None):
393            """Initialize the PostGISShapeStore.
394    
395            The db parameter should be an instance of PostGISConnection and
396            tablename the name of a table in the database represented by db.
397    
398            The geometry_column paramter, if given, should be the name of
399            the geometry column to use.  If the name given is not a geometry
400            column, raise a ValueError.
401    
402            If no geometry_column is given, the table must have exactly one
403            geometry column.  If it has more than one and the
404            geometry_column is not given, a ValueError will be raised.
405            """
406            self.geometry_column = geometry_column
407            self.geometry_column_was_given = geometry_column is not None
408            PostGISTable.__init__(self, db, tablename)
409    
410            # For convenience, we have a quoted version of the geometry
411            # column in self.quoted_geo_col
412            self.quoted_geo_col = quote_identifier(self.geometry_column)
413    
414        def _fetch_table_information(self):
415            """Extend inherited method to retrieve the SRID"""
416            PostGISTable._fetch_table_information(self)
417            cursor = self.db.cursor()
418            cursor.execute("SELECT srid FROM geometry_columns"
419                           " WHERE f_table_name = %s AND f_geometry_column=%s",
420                           (self.tablename, self.geometry_column))
421            self.srid = cursor.fetchone()[0]
422    
423        def _create_col_from_description(self, index, description):
424            """Extend the inherited method to find geometry columns
425    
426            If the column indicated by the paramters is a geometry column,
427            record its name in self.geometry_column and a quoted version in
428            self.quoted_geo_col.  In any case return the return value of the
429            inherited method.
430            """
431            col = PostGISTable._create_col_from_description(self, index,
432                                                            description)
433            col_name, col_type = description[:2]
434            if self.geometry_column_was_given:
435                if (col_name == self.geometry_column
436                    and col_type != self.db.geometry_type):
437                    raise TypeError("Column %s in %s is not a geometry column"
438                                    % (self.geometry_column, self.tablename))
439            else:
440                if col is None:
441                    if description[1] == self.db.geometry_type:
442                        # The column is a geometry column.  If the name of
443                        # the geometry column was not given to the
444                        # constructor, and we encounter two geometry
445                        # columns, raise a value error
446                        if self.geometry_column is None:
447                            self.geometry_column = description[0]
448                        else:
449                            raise TypeError("Table %s has two geometry columns"
450                                            " and no column name was given"
451                                            % (self.tablename,))
452            return col
453    
454      def Table(self):      def Table(self):
455          """Return self since a PostGISShapeStore is its own table."""          """Return self since a PostGISShapeStore is its own table."""
456          return self          return self
# Line 339  class PostGISShapeStore(PostGISTable): Line 464  class PostGISShapeStore(PostGISTable):
464          """Return the type of the shapes in the shapestore."""          """Return the type of the shapes in the shapestore."""
465          cursor = self.db.cursor()          cursor = self.db.cursor()
466          cursor.execute("SELECT type FROM geometry_columns WHERE"          cursor.execute("SELECT type FROM geometry_columns WHERE"
467                         " f_table_name=%s", (self.tablename,))                         " f_table_name=%s AND f_geometry_column=%s",
468                           (self.tablename, self.geometry_column))
469          result = cursor.fetchone()[0]          result = cursor.fetchone()[0]
470          cursor.close()          cursor.close()
471          return shapetype_map[result]          return shapetype_map[result]
# Line 368  class PostGISShapeStore(PostGISTable): Line 494  class PostGISShapeStore(PostGISTable):
494              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
495              # calculate the bounding box by hand from that              # calculate the bounding box by hand from that
496              cursor.execute("SELECT extent(%s) FROM %s;"              cursor.execute("SELECT extent(%s) FROM %s;"
497                             % (self.geometry_column, self.tablename))                             % (self.quoted_geo_col, self.quoted_tablename))
498              result = cursor.fetchone()              result = cursor.fetchone()
499              if result:              if result[0]:
500                  (minx, miny), (maxx, maxy) \                  (minx, miny), (maxx, maxy) \
501                        = wellknowntext.parse_wkt_thuban(result[0])[0]                        = wellknowntext.parse_wkt_thuban(result[0])[0]
502                  return (minx, miny, maxx, maxy)                  return (minx, miny, maxx, maxy)
# Line 380  class PostGISShapeStore(PostGISTable): Line 506  class PostGISShapeStore(PostGISTable):
506      def Shape(self, shapeid):      def Shape(self, shapeid):
507          cursor = self.db.cursor()          cursor = self.db.cursor()
508          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
509                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename, shapeid))
510          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
511          cursor.close()          cursor.close()
512          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
# Line 388  class PostGISShapeStore(PostGISTable): Line 514  class PostGISShapeStore(PostGISTable):
514      def AllShapes(self):      def AllShapes(self):
515          cursor = self.db.cursor()          cursor = self.db.cursor()
516          cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"          cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
517                         % (self.geometry_column, self.tablename))                         % (self.quoted_geo_col, self.quoted_tablename))
518          while 1:          while 1:
519              result = cursor.fetchone()              result = cursor.fetchone()
520              if result is None:              if result is None:
# Line 405  class PostGISShapeStore(PostGISTable): Line 531  class PostGISShapeStore(PostGISTable):
531                     left, bottom))                     left, bottom))
532          cursor = self.db.cursor()          cursor = self.db.cursor()
533          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT gid, AsText(%s) FROM %s"
534                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %s && GeometryFromText('%s', %d) ORDER BY gid"
535                         % (self.geometry_column, self.tablename,                         % (self.quoted_geo_col, self.quoted_tablename,
536                            self.geometry_column, geom))                            self.quoted_geo_col, geom, self.srid))
537          while 1:          while 1:
538              result = cursor.fetchone()              result = cursor.fetchone()
539              if result is None:              if result is None:
Legend:
Removed from v.1662  
changed lines
  Added in v.2059
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26