/[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 1946 by bh, Thu Nov 13 18:56:41 2003 UTC
# 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 alread 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 119  class PostGISConnection: Line 135  class PostGISConnection:
135    
136  class PostGISColumn:  class PostGISColumn:
137    
138      """Column description for a PostGISTable"""      """Column description for a PostGISTable
139    
140        In addition to the normal column object attributes name, type and
141        index, PostGISColumn objects have a quoted_name attribute which
142        contains a quoted version of name for use in SQL statements. The
143        quoted_name attribute is mainly intended for internal use by the
144        PostGISTable class.
145        """
146    
147      def __init__(self, name, type, index):      def __init__(self, name, type, index):
148          self.name = name          self.name = name
149            self.quoted_name = quote_identifier(name)
150          self.type = type          self.type = type
151          self.index = index          self.index = index
152    
# Line 145  class PostGISTable: Line 169  class PostGISTable:
169          """          """
170          self.db = db          self.db = db
171          self.tablename = tablename          self.tablename = tablename
172            # Tablename quoted for use in SQL statements.
173            self.quoted_tablename = quote_identifier(tablename)
174    
175            # Map column names and indices to column objects.
176          self.column_map = {}          self.column_map = {}
177    
178          self._fetch_table_information()          self._fetch_table_information()
179    
180      def _fetch_table_information(self):      def _fetch_table_information(self):
181          """Internal: Update information about the table"""          """Internal: Update information about the table"""
182          self.columns = []          self.columns = []
183          cursor = self.db.cursor()          cursor = self.db.cursor()
184          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.tablename)          cursor.execute("SELECT * FROM %s WHERE 0=1" % self.quoted_tablename)
185          description = cursor.description          description = cursor.description
186    
187          for i in range(len(description)):          for i in range(len(description)):
# Line 164  class PostGISTable: Line 193  class PostGISTable:
193              else:              else:
194                  if description[i][1] == self.db.geometry_type:                  if description[i][1] == self.db.geometry_type:
195                      self.geometry_column = description[i][0]                      self.geometry_column = description[i][0]
196                        self.quoted_geo_col =quote_identifier(self.geometry_column)
197                  # No matching table type. Ignore the column.                  # No matching table type. Ignore the column.
198                  # FIXME: We should at least print a warning about                  # FIXME: We should at least print a warning about
199                  # ignored columns                  # ignored columns
# Line 176  class PostGISTable: Line 206  class PostGISTable:
206    
207          # Build query string for ReadRowAsDict          # Build query string for ReadRowAsDict
208          self.query_stmt = ("SELECT %s from %s"          self.query_stmt = ("SELECT %s from %s"
209                             % (", ".join([col.name for col in self.columns]),                             % (", ".join([col.quoted_name
210                                self.tablename))                                           for col in self.columns]),
211                                  self.quoted_tablename))
212    
213      def DBConnection(self):      def DBConnection(self):
214          """Return the dbconnection used by the table"""          """Return the dbconnection used by the table"""
# Line 213  class PostGISTable: Line 244  class PostGISTable:
244    
245      def NumRows(self):      def NumRows(self):
246          cursor = self.db.cursor()          cursor = self.db.cursor()
247          cursor.execute("SELECT count(*) FROM %s" % self.tablename)          cursor.execute("SELECT count(*) FROM %s" % self.quoted_tablename)
248          return cursor.fetchone()[0]          return cursor.fetchone()[0]
249    
250      def RowIdToOrdinal(self, gid):      def RowIdToOrdinal(self, gid):
251          """Return the row ordinal given its id"""          """Return the row ordinal given its id"""
252          cursor = self.db.cursor()          cursor = self.db.cursor()
253          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
254                         % (self.tablename, gid))                         % (self.quoted_tablename, gid))
255          return cursor.fetchone()[0]          return cursor.fetchone()[0]
256    
257      def RowOrdinalToId(self, num):      def RowOrdinalToId(self, num):
258          """Return the rowid for given its ordinal"""          """Return the rowid for given its ordinal"""
259          cursor = self.db.cursor()          cursor = self.db.cursor()
260          cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"          cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
261                         % (self.tablename, num))                         % (self.quoted_tablename, num))
262          return cursor.fetchone()[0]          return cursor.fetchone()[0]
263    
264      def ReadRowAsDict(self, row, row_is_ordinal = 0):      def ReadRowAsDict(self, row, row_is_ordinal = 0):
# Line 246  class PostGISTable: Line 277  class PostGISTable:
277          cursor = self.db.cursor()          cursor = self.db.cursor()
278          if row_is_ordinal:          if row_is_ordinal:
279              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
280                      (self.column_map[col].name, self.tablename, row))                      (self.column_map[col].quoted_name, self.quoted_tablename,
281                         row))
282          else:          else:
283              stmt = ("SELECT %s FROM %s WHERE gid = %d" %              stmt = ("SELECT %s FROM %s WHERE gid = %d" %
284                      (self.column_map[col].name, self.tablename, row))                      (self.column_map[col].quoted_name, self.quoted_tablename,
285                         row))
286          cursor.execute(stmt)          cursor.execute(stmt)
287          return cursor.fetchone()[0]          return cursor.fetchone()[0]
288    
289      def ValueRange(self, col):      def ValueRange(self, col):
290          cursor = self.db.cursor()          cursor = self.db.cursor()
291          name = self.column_map[col].name          name = self.column_map[col].quoted_name
292          cursor.execute("SELECT min(%s), max(%s) FROM %s" %          cursor.execute("SELECT min(%s), max(%s) FROM %s" %
293                         (name, name, self.tablename))                         (name, name, self.quoted_tablename))
294          return tuple(cursor.fetchone())          return tuple(cursor.fetchone())
295    
296      def UniqueValues(self, col):      def UniqueValues(self, col):
297          cursor = self.db.cursor()          cursor = self.db.cursor()
298          name = self.column_map[col].name          name = self.column_map[col].quoted_name
299          cursor.execute("SELECT %s FROM %s GROUP BY %s" %          cursor.execute("SELECT %s FROM %s GROUP BY %s" %
300                         (name, self.tablename, name))                         (name, self.quoted_tablename, name))
301          return [row[0] for row in cursor.fetchall()]          return [row[0] for row in cursor.fetchall()]
302    
303      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
# Line 275  class PostGISTable: Line 308  class PostGISTable:
308              comparison = "="              comparison = "="
309    
310          if isinstance(right, PostGISColumn):          if isinstance(right, PostGISColumn):
311              right_template = right.name              right_template = right.quoted_name
312              params = ()              params = ()
313          else:          else:
314              right_template = "%s"              right_template = "%s"
315              params = (right,)              params = (right,)
316    
317          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \          query = "SELECT gid FROM %s WHERE %s %s %s ORDER BY gid;" \
318                  % (self.tablename, left.name, comparison, right_template)                  % (self.quoted_tablename, left.quoted_name, comparison,
319                       right_template)
320    
321          cursor = self.db.cursor()          cursor = self.db.cursor()
322          cursor.execute(query, params)          cursor.execute(query, params)
# Line 375  class PostGISShapeStore(PostGISTable): Line 409  class PostGISShapeStore(PostGISTable):
409              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and              # like "SELECT AsText(Envelope(the_geom)) FROM mytable;" and
410              # calculate the bounding box by hand from that              # calculate the bounding box by hand from that
411              cursor.execute("SELECT extent(%s) FROM %s;"              cursor.execute("SELECT extent(%s) FROM %s;"
412                             % (self.geometry_column, self.tablename))                             % (self.quoted_geo_col, self.quoted_tablename))
413              result = cursor.fetchone()              result = cursor.fetchone()
414              if result:              if result:
415                  (minx, miny), (maxx, maxy) \                  (minx, miny), (maxx, maxy) \
# Line 387  class PostGISShapeStore(PostGISTable): Line 421  class PostGISShapeStore(PostGISTable):
421      def Shape(self, shapeid):      def Shape(self, shapeid):
422          cursor = self.db.cursor()          cursor = self.db.cursor()
423          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"          cursor.execute("SELECT AsText(%s) FROM %s WHERE gid=%d"
424                         % (self.geometry_column, self.tablename, shapeid))                         % (self.quoted_geo_col, self.quoted_tablename, shapeid))
425          wkt = cursor.fetchone()[0]          wkt = cursor.fetchone()[0]
426          cursor.close()          cursor.close()
427          return PostGISShape(shapeid, wkt)          return PostGISShape(shapeid, wkt)
# Line 395  class PostGISShapeStore(PostGISTable): Line 429  class PostGISShapeStore(PostGISTable):
429      def AllShapes(self):      def AllShapes(self):
430          cursor = self.db.cursor()          cursor = self.db.cursor()
431          cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"          cursor.execute("SELECT gid, AsText(%s) FROM %s ORDER BY gid"
432                         % (self.geometry_column, self.tablename))                         % (self.quoted_geo_col, self.quoted_tablename))
433          while 1:          while 1:
434              result = cursor.fetchone()              result = cursor.fetchone()
435              if result is None:              if result is None:
# Line 413  class PostGISShapeStore(PostGISTable): Line 447  class PostGISShapeStore(PostGISTable):
447          cursor = self.db.cursor()          cursor = self.db.cursor()
448          cursor.execute("SELECT gid, AsText(%s) FROM %s"          cursor.execute("SELECT gid, AsText(%s) FROM %s"
449                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"                       " WHERE %s && GeometryFromText('%s', -1) ORDER BY gid"
450                         % (self.geometry_column, self.tablename,                         % (self.quoted_geo_col, self.quoted_tablename,
451                            self.geometry_column, geom))                            self.quoted_geo_col, geom))
452          while 1:          while 1:
453              result = cursor.fetchone()              result = cursor.fetchone()
454              if result is None:              if result is None:
Legend:
Removed from v.1693  
changed lines
  Added in v.1946
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26