/[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 1693 by bh, Mon Sep 1 11:23:26 2003 UTC revision 2057 by bh, Tue Feb 10 15:51:57 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    
# Line 145  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)):
# Line 164  class PostGISTable: Line 207  class PostGISTable:
207              else:              else:
208                  if description[i][1] == self.db.geometry_type:                  if description[i][1] == self.db.geometry_type:
209                      self.geometry_column = description[i][0]                      self.geometry_column = description[i][0]
210                        self.quoted_geo_col =quote_identifier(self.geometry_column)
211                  # No matching table type. Ignore the column.                  # No matching table type. Ignore the column.
212                  # FIXME: We should at least print a warning about                  # FIXME: We should at least print a warning about
213                  # ignored columns                  # ignored columns
# Line 176  class PostGISTable: Line 220  class PostGISTable:
220    
221          # Build query string for ReadRowAsDict          # Build query string for ReadRowAsDict
222          self.query_stmt = ("SELECT %s from %s"          self.query_stmt = ("SELECT %s from %s"
223                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.quoted_name
224                                self.tablename))                                           for col in self.columns]),
225                                  self.quoted_tablename))
226    
227      def DBConnection(self):      def DBConnection(self):
228          """Return the dbconnection used by the table"""          """Return the dbconnection used by the table"""
# Line 213  class PostGISTable: Line 258  class PostGISTable:
258    
259      def NumRows(self):      def NumRows(self):
260          cursor = self.db.cursor()          cursor = self.db.cursor()
261          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
262          return cursor.fetchone()[0]          return cursor.fetchone()[0]
263    
264      def RowIdToOrdinal(self, gid):      def RowIdToOrdinal(self, gid):
265          """Return the row ordinal given its id"""          """Return the row ordinal given its id"""
266          cursor = self.db.cursor()          cursor = self.db.cursor()
267          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
268                         % (self.tablename, gid))                         % (self.quoted_tablename, gid))
269          return cursor.fetchone()[0]          return cursor.fetchone()[0]
270    
271      def RowOrdinalToId(self, num):      def RowOrdinalToId(self, num):
272          """Return the rowid for given its ordinal"""          """Return the rowid for given its ordinal"""
273          cursor = self.db.cursor()          cursor = self.db.cursor()
274          cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"          cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
275                         % (self.tablename, num))                         % (self.quoted_tablename, num))
276          return cursor.fetchone()[0]          return cursor.fetchone()[0]
277    
278      def ReadRowAsDict(self, row, row_is_ordinal = 0):      def ReadRowAsDict(self, row, row_is_ordinal = 0):
# Line 246  class PostGISTable: Line 291  class PostGISTable:
291          cursor = self.db.cursor()          cursor = self.db.cursor()
292          if row_is_ordinal:          if row_is_ordinal:
293              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
294                      (self.column_map[col].name, self.tablename, row))                      (self.column_map[col].quoted_name, self.quoted_tablename,
295                         row))
296          else:          else:
297              stmt = ("SELECT %s FROM %s WHERE gid = %d" %              stmt = ("SELECT %s FROM %s WHERE gid = %d" %
298                      (self.column_map[col].name, self.tablename, row))                      (self.column_map[col].quoted_name, self.quoted_tablename,
299                         row))
300          cursor.execute(stmt)          cursor.execute(stmt)
301          return cursor.fetchone()[0]          return cursor.fetchone()[0]
302    
303      def ValueRange(self, col):      def ValueRange(self, col):
304          cursor = self.db.cursor()          cursor = self.db.cursor()
305          name = self.column_map[col].name          name = self.column_map[col].quoted_name
306          cursor.execute("SELECT min(%s), max(%s) FROM %s" %          cursor.execute("SELECT min(%s), max(%s) FROM %s" %
307                         (name, name, self.tablename))                         (name, name, self.quoted_tablename))
308          return tuple(cursor.fetchone())          return tuple(cursor.fetchone())
309    
310      def UniqueValues(self, col):      def UniqueValues(self, col):
311          cursor = self.db.cursor()          cursor = self.db.cursor()
312          name = self.column_map[col].name          name = self.column_map[col].quoted_name
313          cursor.execute("SELECT %s FROM %s GROUP BY %s" %          cursor.execute("SELECT %s FROM %s GROUP BY %s" %
314                         (name, self.tablename, name))                         (name, self.quoted_tablename, name))
315          return [row[0] for row in cursor.fetchall()]          return [row[0] for row in cursor.fetchall()]
316    
317      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
# Line 275  class PostGISTable: Line 322  class PostGISTable:
322              comparison = "="              comparison = "="
323    
324          if isinstance(right, PostGISColumn):          if isinstance(right, PostGISColumn):
325              right_template = right.name              right_template = right.quoted_name
326              params = ()              params = ()
327          else:          else:
328              right_template = "%s"              right_template = "%s"
329              params = (right,)              params = (right,)
330    
331          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \
332                  % (self.tablename, left.name, comparison, right_template)                  % (self.quoted_tablename, left.quoted_name, comparison,
333                       right_template)
334    
335          cursor = self.db.cursor()          cursor = self.db.cursor()
336          cursor.execute(query, params)          cursor.execute(query, params)
# Line 333  class PostGISShapeStore(PostGISTable): Line 381  class PostGISShapeStore(PostGISTable):
381    
382      """Shapestore interface to a table in a PostGIS database"""      """Shapestore interface to a table in a PostGIS database"""
383    
384        def _fetch_table_information(self):
385            """Extend inherited method to retrieve the SRID"""
386            PostGISTable._fetch_table_information(self)
387            cursor = self.db.cursor()
388            cursor.execute("SELECT srid FROM geometry_columns"
389                           " WHERE f_table_name = %s", (self.tablename,))
390            self.srid = cursor.fetchone()[0]
391    
392      def Table(self):      def Table(self):
393          """Return self since a PostGISShapeStore is its own table."""          """Return self since a PostGISShapeStore is its own table."""
394          return self          return self
# Line 375  class PostGISShapeStore(PostGISTable): Line 431  class PostGISShapeStore(PostGISTable):
431              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
432              # calculate the bounding box by hand from that              # calculate the bounding box by hand from that
433              cursor.execute("SELECT extent(%s) FROM %s;"              cursor.execute("SELECT extent(%s) FROM %s;"
434                             % (self.geometry_column, self.tablename))                             % (self.quoted_geo_col, self.quoted_tablename))
435              result = cursor.fetchone()              result = cursor.fetchone()
436              if result:              if result[0]:
437                  (minx, miny), (maxx, maxy) \                  (minx, miny), (maxx, maxy) \
438                        = wellknowntext.parse_wkt_thuban(result[0])[0]                        = wellknowntext.parse_wkt_thuban(result[0])[0]
439                  return (minx, miny, maxx, maxy)                  return (minx, miny, maxx, maxy)
# Line 387  class PostGISShapeStore(PostGISTable): Line 443  class PostGISShapeStore(PostGISTable):
443      def Shape(self, shapeid):      def Shape(self, shapeid):
444          cursor = self.db.cursor()          cursor = self.db.cursor()
445          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
446                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename, shapeid))
447          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
448          cursor.close()          cursor.close()
449          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
# Line 395  class PostGISShapeStore(PostGISTable): Line 451  class PostGISShapeStore(PostGISTable):
451      def AllShapes(self):      def AllShapes(self):
452          cursor = self.db.cursor()          cursor = self.db.cursor()
453          cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"          cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
454                         % (self.geometry_column, self.tablename))                         % (self.quoted_geo_col, self.quoted_tablename))
455          while 1:          while 1:
456              result = cursor.fetchone()              result = cursor.fetchone()
457              if result is None:              if result is None:
# Line 412  class PostGISShapeStore(PostGISTable): Line 468  class PostGISShapeStore(PostGISTable):
468                     left, bottom))                     left, bottom))
469          cursor = self.db.cursor()          cursor = self.db.cursor()
470          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT gid, AsText(%s) FROM %s"
471                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %s && GeometryFromText('%s', %d) ORDER BY gid"
472                         % (self.geometry_column, self.tablename,                         % (self.quoted_geo_col, self.quoted_tablename,
473                            self.geometry_column, geom))                            self.quoted_geo_col, geom, self.srid))
474          while 1:          while 1:
475              result = cursor.fetchone()              result = cursor.fetchone()
476              if result is None:              if result is None:
Legend:
Removed from v.1693  
changed lines
  Added in v.2057
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26