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

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

Parent Directory | Revision Log | View Patch Patch

-revision 2096 by bh,
Thu Mar 11 13:50:53 2004 UTC
+revision 2623 by bh,
Mon May  9 18:12:12 2005 UTC
 Line 1
- # Copyright (C) 2003, 2004 by Intevation GmbH
+ # Copyright (C) 2003, 2004, 2005 by Intevation GmbH
  # Authors:
  # Martin Mueller <[email protected]>
  # Bernhard Herzog <[email protected]>
 Line 32 
 def psycopg_version():
      return psycopg.__version__
  if psycopg is not None:
+     # type_map maps psycopg type objects.  It's a list of pairs since
+     # the psycopg type objects are unhashable.
      type_map = [(psycopg.STRING, table.FIELDTYPE_STRING),
                  (psycopg.INTEGER, table.FIELDTYPE_INT),
+                 (psycopg.ROWID, table.FIELDTYPE_INT),
                  (psycopg.FLOAT, table.FIELDTYPE_DOUBLE)]
+     # _raw_type_map maps the postgresql type constants to Thuban type
+     # constants.  This is very low level and postgresql specific and
+     # should be used only when necessary.
+     _raw_type_map = {}
+     def _fill_raw_type_map():
+         for psycopg_type, thuban_type in type_map:
+             for value in psycopg_type.values:
+                 _raw_type_map[value] = thuban_type
+     _fill_raw_type_map()
  def quote_identifier(ident):
      """Return a quoted version of the identifier ident.
-Line 94 
 class PostGISConnection:
+Line 107 
 class PostGISConnection:
          except psycopg.OperationalError, val:
              raise ConnectionError(str(val))
+         # Use autocommit mode.  For simple reading of the database it's
+         # sufficient and we don't have to care much about error
+         # handling.  Without autocommit, an errors during a cursor's
+         # execute method requires a rollback on the connection,
+         # otherwise later queries with the same or other cursors sharing
+         # the same connection will lead to further errors ("ERROR:
+         # current transaction is aborted, commands ignored until end of
+         # transaction block")
+         self.connection.autocommit()
          # determine the OID for the geometry type. This is PostGIS
          # specific.
          cursor = self.connection.cursor()
-Line 135 
 class PostGISConnection:
+Line 158 
 class PostGISConnection:
      def GeometryTables(self):
          """Return a list with the names of all tables with a geometry column"""
+         # The query is basically taken from the psql v. 7.2.1.  When
+         # started with -E it prints the queries used for internal
+         # commands such as \d, which does mostly what we need here.
          cursor = self.connection.cursor()
-         cursor.execute("SELECT f_table_name FROM geometry_columns;")
+         cursor.execute("SELECT c.relname FROM pg_class c"
+                        " WHERE c.relkind IN ('r', 'v')"
+                              # Omit the system tables
+                              " AND c.relname !~ '^pg_'"
+                              # Omit the special PostGIS tables
+                              " AND c.relname NOT IN ('geometry_columns',"
+                                                    " 'spatial_ref_sys')"
+                             " AND %d in (SELECT a.atttypid FROM pg_attribute a"
+                                        " WHERE a.attrelid = c.oid)"
+                        " ORDER BY c.relname;", (self.geometry_type,))
          result = [row[0] for row in cursor.fetchall()]
          self.connection.commit()
          return result
+     def table_columns(self, tablename):
+         """Experimental: return information about the columns of a table
+         Return value is a list of (name, type) pairs where name is the
+         name of the column and type either one of the field type columns
+         or the string 'geometry' indicating a geometry column.
+         The intended use of this method is for table selection dialogs
+         which need to determine which columns are usable as id or
+         geometry columns respectively.  Suitable id columns will have
+         type FIELDTYPE_INT and geometry columns will have 'geometry'.
+         """
+         result = []
+         cursor = self.connection.cursor()
+         # This query is taken basically from the \d command of psql
+         # 7.2.1
+         cursor.execute("SELECT a.attname, a.atttypid, a.attnum"
+                        " FROM pg_class c, pg_attribute a"
+                             " WHERE c.relname = %s AND a.attrelid = c.oid"
+                        " ORDER BY a.attnum;", (tablename,))
+         for row in cursor.fetchall():
+             col_name, col_type, col_attnum = row
+             col = None
+             if col_attnum < 1:
+                 # It's a system column.  Only the OID is interesting
+                 # here
+                 if col_name == "oid":
+                     col = (col_name, _raw_type_map[col_type])
+             else:
+                 # If it's an integer
+                 thuban_type = _raw_type_map.get(col_type)
+                 if thuban_type is not None:
+                     col = (col_name, thuban_type)
+                 elif row[1] == self.geometry_type:
+                     col = (col_name, "geometry")
+             if col is not None:
+                 result.append(col)
+         return result
      def cursor(self):
          """Return a DB API 2.0 cursor for the database"""
          return self.connection.cursor()
-Line 175 
 class PostGISTable:
+Line 253 
 class PostGISTable:
      descriptions returned by Columns() and other methods.
      """
-     def __init__(self, db, tablename, id_column = "gid"):
+     def __init__(self, db, tablename, id_column = None):
          """Initialize the PostGISTable.
          The db parameter should be an instance of PostGISConnection and
-Line 184 
 class PostGISTable:
+Line 262 
 class PostGISTable:
          The id_column parameter should be the name of a column in the
          table that can be used to identify rows.  The column must have
          the type integer and be unique and not null.
+         For backwards compatibility reasons, the id_column parameter is
+         optional.  If not given the table must have a column called
+         'gid' which is used as the id_column.  New code should always
+         provide this parameter.
          """
          self.db = db
          self.tablename = tablename
          # Tablename quoted for use in SQL statements.
          self.quoted_tablename = quote_identifier(tablename)
+         if not id_column:
+             id_column = "gid"
          self.id_column = id_column
          # id column name quoted for use in SQL statements.
          self.quoted_id_column = quote_identifier(id_column)
-Line 244 
 class PostGISTable:
+Line 329 
 class PostGISTable:
          """Return the dbconnection used by the table"""
          return self.db
+     def IDColumn(self):
+         """Return the column description object for the id column.
+         If the oid column was used as the id column, the return value is
+         not one of the regular column objects that would be returned by
+         e.g. the Column() method, but it still has meaningful name
+         attribute.
+         """
+         if self.id_column == "oid":
+             return PostGISColumn(self.id_column, table.FIELDTYPE_INT, None)
+         return self.column_map[self.id_column]
      def TableName(self):
          """Return the name of the table in the database"""
          return self.tablename
-Line 391 
 class PostGISShape:
+Line 488 
 class PostGISShape:
  shapetype_map = {"POLYGON": SHAPETYPE_POLYGON,
                   "MULTIPOLYGON": SHAPETYPE_POLYGON,
+                  "LINESTRING": SHAPETYPE_ARC,
                   "MULTILINESTRING": SHAPETYPE_ARC,
                   "POINT": SHAPETYPE_POINT}
-Line 427 
 class PostGISShapeStore(PostGISTable):
+Line 525 
 class PostGISShapeStore(PostGISTable):
          self.quoted_geo_col = quote_identifier(self.geometry_column)
      def _fetch_table_information(self):
-         """Extend inherited method to retrieve the SRID"""
+         """Extend inherited method to retrieve the SRID and shape type"""
          PostGISTable._fetch_table_information(self)
+         # First, try to get it from the geometry_columns table.
          cursor = self.db.cursor()
-         cursor.execute("SELECT srid FROM geometry_columns"
+         cursor.execute("SELECT srid, type FROM geometry_columns"
                         " WHERE f_table_name = %s AND f_geometry_column=%s",
                         (self.tablename, self.geometry_column))
-         self.srid = cursor.fetchone()[0]
+         row = cursor.fetchone()
+         if row is not None:
+             self.srid = row[0]
+             self.shape_type = shapetype_map.get(row[1])
+             return
+         # The table is probably really a view and thus not in
+         # geometry_columns.  Use a different approach
+         cursor = self.db.cursor()
+         cursor.execute("SELECT DISTINCT SRID(%s) FROM %s;" %
+                        (quote_identifier(self.geometry_column),
+                         self.tablename))
+         row = cursor.fetchone()
+         if row is not None:
+             self.srid = row[0]
+             # Try to see whether there's another one
+             row = cursor.fetchone()
+             if row is not None:
+                 # There are at least two different srids.  We don't
+                 # support that
+                 self.srid = None
+         cursor = self.db.cursor()
+         cursor.execute("SELECT DISTINCT GeometryType(%s) FROM %s;"
+                        % (quote_identifier(self.geometry_column),
+                           self.tablename))
+         row = cursor.fetchone()
+         if row is not None:
+             self.shape_type = shapetype_map.get(row[0])
+             # Try to see whether there's another one
+             row = cursor.fetchone()
+             if row is not None:
+                 # There are at least two different srids.  We don't
+                 # support that
+                 self.shape_type = None
      def _create_col_from_description(self, index, description):
          """Extend the inherited method to find geometry columns
-Line 475 
 class PostGISShapeStore(PostGISTable):
+Line 609 
 class PostGISShapeStore(PostGISTable):
          """
          return None
+     def GeometryColumn(self):
+         """Return the column description object for the geometry column
+         There's currently no FIELDTYPE constant for this column, so the
+         return value is not a regular column object that could also be
+         returned from e.g. the Column() method.  Only the name attribute
+         of the return value is meaningful at the moment.
+         """
+         return PostGISColumn(self.geometry_column, None, None)
      def ShapeType(self):
          """Return the type of the shapes in the shapestore."""
-         cursor = self.db.cursor()
+         return self.shape_type
-         cursor.execute("SELECT type FROM geometry_columns WHERE"
-                        " f_table_name=%s AND f_geometry_column=%s",
-                        (self.tablename, self.geometry_column))
-         result = cursor.fetchone()[0]
-         cursor.close()
-         return shapetype_map[result]
      def RawShapeFormat(self):
          """Return the raw data format of the shape data.

 Legend:



Removed from v.2096
 


changed lines


 
Added in v.2623
 Legend:



Removed from v.2096
 


changed lines


 
Added in v.2623
-Removed from v.2096
+Added in v.2623

[email protected]	ViewVC Help
Powered by ViewVC 1.1.26