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

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

Parent Directory | Revision Log | View Patch Patch

-revision 785 by bh,
Wed Apr 30 10:54:03 2003 UTC
+revision 1020 by jan,
Fri May 23 13:03:07 2003 UTC
 Line 22 
 import os
  import weakref
  from sqlite import connect
+ from base import TitledObject
  import table
  sql_type_map = {
-Line 85 
 class ColumnReference:
+Line 87 
 class ColumnReference:
          self.internal_name = internal_name
- class TransientTableBase:
+ class TransientTableBase(table.OldTableInterfaceMixin):
      """Base class for tables in the transient database"""
-Line 104 
 class TransientTableBase:
+Line 106 
 class TransientTableBase:
          self.orig_names = []
          self.internal_to_orig = {}
          self.orig_to_internal = {}
+         self.column_map = {}
          # Create the column objects and fill various maps and lists
-         for col in self.columns:
+         for index in range(len(self.columns)):
+             col = self.columns[index]
              self.name_to_column[col.name] = col
              self.orig_names.append(col.name)
              self.internal_to_orig[col.internal_name] = col.name
              self.orig_to_internal[col.name] = col.internal_name
+             self.column_map[col.name] = col
+             self.column_map[index] = col
          # Build the CREATE TABLE statement and create the table in the
          # database
-         table_types = []
+         table_types = ["id INTEGER PRIMARY KEY"]
          for col in self.columns:
              table_types.append("%s %s" % (col.internal_name,
                                            sql_type_map[col.type]))
-Line 138 
 class TransientTableBase:
+Line 144 
 class TransientTableBase:
              self.db.execute(stmt)
              self.indexed_columns[column] = 1
-     def field_count(self):
+     def NumColumns(self):
          return len(self.columns)
-     def field_info(self, i):
+     def NumRows(self):
-         col = self.columns[i]
-         return col.type, col.name, 0, 0
-     def field_info_by_name(self, name):
-         for col in self.columns:
-             if col.name == name:
-                 return col.type, col.name, 0, 0
-         else:
-             return None
-     def record_count(self):
          result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)
          return int(result[0])
-     def read_record(self, index):
+     def Columns(self):
+         return self.columns
+     def Column(self, col):
+         return self.column_map[col]
+     def HasColumn(self, col):
+         """Return whether the table has a column with the given name or index
+         """
+         return self.column_map.has_key(col)
+     def ReadRowAsDict(self, index):
+         # Implementation Strategy: Executing a completely new select
+         # statement every time this method is called is too slow. The
+         # most important usage is to read the records more or less
+         # sequentially. This happens e.g. when drawing a layer with a
+         # classification where the shapes are drawn in order of the
+         # shape ids. Another pattern is that the same row is requested
+         # several times in a row. This happens in the table view, for
+         # instance.
+         # We can exploit this to make access faster by having one cursor
+         # open all the time and keeping the last row read around in case
+         # the same row is accessed again the next time and if the row
+         # index is larger than the row we have read last we simply fetch
+         # rows from the cursor until we've reached the requested row. If
+         # the requested row index is smaller then we start a new cursor.
+         # FIXME: So far this scheme seems to work well enough. Obvious
+         # improvements would be to start the cursor at exactly the
+         # requested row (should be efficient and easy to do now that the
+         # id is the primary key) and to perhaps to also start a new
+         # cursor if the requested index is much larger than the last row
+         # so that we don't read and discard lots of the rows.
+         # Check whether we have to start a new cursor
          if self.read_record_cursor is None or index <self.read_record_last_row:
              stmt = ("SELECT %s FROM %s;"
                      % (", ".join([c.internal_name for c in self.columns]),
-Line 172 
 class TransientTableBase:
+Line 202 
 class TransientTableBase:
          assert index >= self.read_record_last_row
          if index == self.read_record_last_row:
              result = self.read_record_last_result
          else:
              for i in range(index - self.read_record_last_row):
                  result = self.read_record_cursor.fetchone()
-Line 180 
 class TransientTableBase:
+Line 210 
 class TransientTableBase:
          self.read_record_last_row = index
          return dict(zip(self.orig_names, result))
-     def field_range(self, colname):
+     def ReadValue(self, row, col):
-         col = self.name_to_column[colname]
+         """Return the value of the specified row and column
+         The col parameter may be the index of the column or its name.
+         """
+         # Depending on the actual access patterns of the table data, it
+         # might be a bit faster in some circumstances to not implement
+         # this via ReadRowAsDict, but this simple implementation should
+         # be fast enough for most purposes.
+         return self.ReadRowAsDict(row)[self.column_map[col].name]
+     def ValueRange(self, col):
+         col = self.column_map[col]
          iname = col.internal_name
          min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"
                                     % (iname, iname, self.tablename))
          converter = type_converter_map[col.type]
-         return ((converter(min), None), (converter(max), None))
+         return (converter(min), converter(max))
-     def GetUniqueValues(self, colname):
+     def UniqueValues(self, col):
-         iname = self.orig_to_internal[colname]
+         iname = self.column_map[col].internal_name
          cursor = self.db.cursor()
          cursor.execute("SELECT %s FROM %s GROUP BY %s;"
                         % (iname, self.tablename, iname))
-Line 201 
 class TransientTableBase:
+Line 242 
 class TransientTableBase:
              result.append(row[0])
          return result
+     def SimpleQuery(self, left, comparison, right):
+         """Return the indices of all rows that matching a condition.
+         Parameters:
+            left -- The column object for the left side of the comparison
- class TransientTable(TransientTableBase):
+            comparison -- The comparison operator as a string. It must be
+                          one of '==', '!=', '<', '<=', '>=', '>'
+            right -- The right hand side of the comparison. It must be
+                     either a column object or a value, i.e. a string,
+                     int or float.
+         The return value is a sorted list of the indices of the rows
+         where the condition is true.
+         """
+         if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
+             raise ValueError("Comparison operator %r not allowed" % comparison)
+         if hasattr(right, "internal_name"):
+             right_template = right.internal_name
+             params = ()
+         else:
+             right_template = "%s"
+             params = (right,)
+         query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
+                 % (self.tablename, left.internal_name, comparison,
+                    right_template)
+         cursor = self.db.cursor()
+         cursor.execute(query, params)
+         result = []
+         while 1:
+             row = cursor.fetchone()
+             if row is None:
+                 break
+             result.append(row[0])
+         return result
+     def Dependencies(self):
+         """Placeholder for a method in a derived class.
+         Return a sequence with the tables and other data objects that
+         self depends on.
+         """
+         raise NotImplementedError
+ class TransientTable(TitledObject, TransientTableBase):
      """A Table in a transient DB that starts as the copy of a Thuban Table."""
-Line 213 
 class TransientTable(TransientTableBase)
+Line 302 
 class TransientTable(TransientTableBase)
          interface.
          """
          TransientTableBase.__init__(self, transient_db)
+         TitledObject.__init__(self, table.Title())
          self.create(table)
      def create(self, table):
          columns = []
-         for i in range(table.field_count()):
+         for col in table.Columns():
-             type, name = table.field_info(i)[:2]
+             columns.append(ColumnReference(col.name, col.type,
-             columns.append(ColumnReference(name, type,
                                             self.db.new_column_name()))
          TransientTableBase.create(self, columns)
          # copy the input table to the transient db
-         insert_template = "INSERT INTO %s (%s) VALUES (%s);" \
+         # A key to insert to use for the formatting of the insert
+         # statement. The key must not be equal to any of the column
+         # names so we construct one by building a string of x's that is
+         # longer than any of the column names
+         id_key = max([len(col.name) for col in self.columns]) * "x"
+         insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
                                 % (self.tablename,
                                    ", ".join([col.internal_name
                                               for col in self.columns]),
+                                   id_key,
                                    ", ".join(["%%(%s)s" % col.name
                                               for col in self.columns]))
          cursor = self.db.cursor()
-         for i in range(table.record_count()):
+         for i in range(table.NumRows()):
-             cursor.execute(insert_template, table.read_record(i))
+             row = table.ReadRowAsDict(i)
+             row[id_key] = i
+             cursor.execute(insert_template, row)
          self.db.conn.commit()
- class TransientJoinedTable(TransientTableBase):
+ class TransientJoinedTable(TitledObject, TransientTableBase):
      """A Table in the transient DB that contains a join of two tables"""
      def __init__(self, transient_db, left_table, left_field,
-                  right_table, right_field = None):
+                  right_table, right_field = None, outer_join = False):
          """Create a new table in the transient DB as a join of two tables.
          Both input tables, left_table and right_table must have a
          transient_table method that returns a table object for a table
-         in the trnsient database. The join is performed on the condition
+         in the transient database. The join is performed on the condition
          that the value of the left_field column the the left table is
          equal to the value of the right_field in the right_table.
-Line 259 
 class TransientJoinedTable(TransientTabl
+Line 358 
 class TransientJoinedTable(TransientTabl
          names of the input tables without having to create prefixes.
          """
          TransientTableBase.__init__(self, transient_db)
+         self.dependencies = (left_table, right_table)
          self.left_table = left_table.transient_table()
          self.left_field = left_field
          self.right_table = right_table.transient_table()
-Line 266 
 class TransientJoinedTable(TransientTabl
+Line 366 
 class TransientJoinedTable(TransientTabl
              self.right_field = right_field
          else:
              self.right_field = self.left_field
+         self.outer_join = outer_join
+         title = "Join of %(left)s and %(right)s" \
+                 % {"left": self.left_table.Title(),
+                    "right": self.right_table.Title()}
+         TitledObject.__init__(self, title)
          self.create()
      def create(self):
-Line 279 
 class TransientJoinedTable(TransientTabl
+Line 386 
 class TransientJoinedTable(TransientTabl
          columns = []
          for col in self.left_table.columns + self.right_table.columns:
              if col.name in visited:
+                 # We can't allow multiple columns with the same original
+                 # name, so omit this one. FIXME: There should be a
+                 # better solution.
                  continue
              columns.append(col)
          TransientTableBase.create(self, columns)
          # Copy the joined data to the table.
          internal_names = [col.internal_name for col in self.columns]
-         stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \
+         if self.outer_join:
-                % (self.tablename,
+             join_operator = 'LEFT OUTER JOIN'
-                   ", ".join(internal_names),
+         else:
-                   ", ".join(internal_names),
+             join_operator = 'JOIN'
-                   self.left_table.tablename,
+         stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
-                   self.right_table.tablename,
+                 " %s %s ON %s = %s;"
-                   self.orig_to_internal[self.left_field],
+                 % (self.tablename,
-                   self.orig_to_internal[self.right_field])
+                    ", ".join(internal_names),
+                    self.left_table.tablename,
+                    ", ".join(internal_names),
+                    self.left_table.tablename,
+                    join_operator,
+                    self.right_table.tablename,
+                    self.orig_to_internal[self.left_field],
+                    self.orig_to_internal[self.right_field]))
          self.db.execute(stmt)
+     def Dependencies(self):
+         """Return a tuple with the two tables the join depends on."""
+         return self.dependencies
- class AutoTransientTable:
+ class AutoTransientTable(TitledObject, table.OldTableInterfaceMixin):
      """Table that copies data to a transient table on demand.
-Line 305 
 class AutoTransientTable:
+Line 426 
 class AutoTransientTable:
      """
      def __init__(self, transient_db, table):
+         TitledObject.__init__(self, table.Title())
          self.transient_db = transient_db
          self.table = table
          self.t_table = None
-     def record_count(self):
+     def Columns(self):
-         """Return the number of records"""
+         return self.table.Columns()
-         return self.table.record_count()
+     def Column(self, col):
-     def field_count(self):
+         return self.table.Column(col)
-         """Return the number of fields in a record"""
-         return self.table.field_count()
-     def field_info(self, field):
-         """Return a tuple (type, name, width, prec) for the field no. field
-         type is the data type of the field, name the name, width the
-         field width in characters and prec the decimal precision.
-         """
-         info = self.table.field_info(field)
-         if info:
-             info = info[:2] + (0, 0)
-         return info
-     def field_info_by_name(self, fieldName):
-         info = self.table.field_info_by_name(fieldName)
-         if info:
-             info = info[:2] + (0, 0)
-         return info
-     def read_record(self, record):
+     def HasColumn(self, col):
+         """Return whether the table has a column with the given name or index
+         """
+         return self.table.HasColumn(col)
+     def NumRows(self):
+         return self.table.NumRows()
+     def NumColumns(self):
+         return self.table.NumColumns()
+     def ReadRowAsDict(self, record):
          """Return the record no. record as a dict mapping field names to values
          """
          if self.t_table is not None:
-             return self.t_table.read_record(record)
+             return self.t_table.ReadRowAsDict(record)
          else:
-             return self.table.read_record(record)
+             return self.table.ReadRowAsDict(record)
-     def write_record(self, record, values):
+     def ReadValue(self, row, col):
-         raise NotImplementedError
+         """Return the value of the specified row and column
+         The col parameter may be the index of the column or its name.
+         """
+         if self.t_table is not None:
+             return self.t_table.ReadValue(row, col)
+         else:
+             return self.table.ReadValue(row, col)
      def copy_to_transient(self):
          """Internal: Create a transient table and copy the data into it"""
-Line 357 
 class AutoTransientTable:
+Line 478 
 class AutoTransientTable:
              self.copy_to_transient()
          return self.t_table
-     def field_range(self, colname):
+     def ValueRange(self, col):
          if self.t_table is None:
              self.copy_to_transient()
-         return self.t_table.field_range(colname)
+         return self.t_table.ValueRange(col)
-     def GetUniqueValues(self, colname):
+     def UniqueValues(self, col):
          if self.t_table is None:
              self.copy_to_transient()
-         return self.t_table.GetUniqueValues(colname)
+         return self.t_table.UniqueValues(col)
+     def SimpleQuery(self, left, comparison, right):
+         if self.t_table is None:
+             self.copy_to_transient()
+         # Make sure to use the column object of the transient table. The
+         # left argument is always a column object so we can just ask the
+         # t_table for the right object.
+         if hasattr(right, "name"):
+             return self.t_table.SimpleQuery(self.t_table.Column(left.name),
+                                             comparison,
+                                             self.t_table.Column(right.name))
+         else:
+             return self.t_table.SimpleQuery(self.t_table.Column(left.name),
+                                             comparison, right)
+     def Dependencies(self):
+         """Return a tuple containing the original table"""
+         return (self.table,)

 Legend:



Removed from v.785
 


changed lines


 
Added in v.1020
 Legend:



Removed from v.785
 


changed lines


 
Added in v.1020
-Removed from v.785
+Added in v.1020

[email protected]	ViewVC Help
Powered by ViewVC 1.1.26