/[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 1660 by bh, Tue Aug 26 12:54:45 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    
153    
154  class PostGISTable:  class PostGISTable:
155    
156      """A Table in a PostGIS database"""      """A Table in a PostGIS database
157    
158        A PostgreSQL table may contain columns with types not (yet)
159        supported by Thuban. Instances of this class ignore those columns
160        and pretend they don't exist, i.e. they won't show up in the column
161        descriptions returned by Columns() and other methods.
162        """
163    
164      def __init__(self, db, tablename):      def __init__(self, db, tablename):
165          """Initialize the PostGISTable.          """Initialize the PostGISTable.
# Line 139  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)):
188              for pgtyp, tabletyp in type_map:              for pgtyp, tabletyp in type_map:
189                  if pgtyp == description[i][1]:                  if pgtyp == description[i][1]:
190                      col = PostGISColumn(description[i][0], tabletyp, i)                      col = PostGISColumn(description[i][0], tabletyp,
191                                            len(self.columns))
192                      break                      break
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 169  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 206  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 ReadRowAsDict(self, row):      def RowIdToOrdinal(self, gid):
251            """Return the row ordinal given its id"""
252          cursor = self.db.cursor()          cursor = self.db.cursor()
253          cursor.execute(self.query_stmt + " LIMIT 1 OFFSET %d" % row)          cursor.execute("SELECT count(*) FROM %s WHERE gid < %d;"
254                           % (self.quoted_tablename, gid))
255            return cursor.fetchone()[0]
256    
257        def RowOrdinalToId(self, num):
258            """Return the rowid for given its ordinal"""
259            cursor = self.db.cursor()
260            cursor.execute("SELECT gid FROM %s LIMIT 1 OFFSET %d;"
261                           % (self.quoted_tablename, num))
262            return cursor.fetchone()[0]
263    
264        def ReadRowAsDict(self, row, row_is_ordinal = 0):
265            cursor = self.db.cursor()
266            if row_is_ordinal:
267                stmt = self.query_stmt + " LIMIT 1 OFFSET %d" % row
268            else:
269                stmt = self.query_stmt + " WHERE gid = %d" % row
270            cursor.execute(stmt)
271          result = {}          result = {}
272          for col, value in zip(self.columns, cursor.fetchone()):          for col, value in zip(self.columns, cursor.fetchone()):
273              result[col.name] = value              result[col.name] = value
274          return result          return result
275    
276      def ReadValue(self, row, col):      def ReadValue(self, row, col, row_is_ordinal = 0):
277          cursor = self.db.cursor()          cursor = self.db.cursor()
278          cursor.execute("SELECT %s FROM %s LIMIT 1 OFFSET %d" %          if row_is_ordinal:
279                         (self.column_map[col].name, self.tablename, row))              stmt = ("SELECT %s FROM %s LIMIT 1 OFFSET %d" %
280                        (self.column_map[col].quoted_name, self.quoted_tablename,
281                         row))
282            else:
283                stmt = ("SELECT %s FROM %s WHERE gid = %d" %
284                        (self.column_map[col].quoted_name, self.quoted_tablename,
285                         row))
286            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 245  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 345  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 357  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 365  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 383  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.1660  
changed lines
  Added in v.1946
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26