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

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

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 818 by bh, Mon May 5 17:18:31 2003 UTC revision 984 by bh, Thu May 22 16:37:48 2003 UTC
# Line 118  class TransientTableBase(table.OldTableI Line 118  class TransientTableBase(table.OldTableI
118    
119          # Build the CREATE TABLE statement and create the table in the          # Build the CREATE TABLE statement and create the table in the
120          # database          # database
121          table_types = []          table_types = ["id INTEGER PRIMARY KEY"]
122          for col in self.columns:          for col in self.columns:
123              table_types.append("%s %s" % (col.internal_name,              table_types.append("%s %s" % (col.internal_name,
124                                            sql_type_map[col.type]))                                            sql_type_map[col.type]))
# Line 155  class TransientTableBase(table.OldTableI Line 155  class TransientTableBase(table.OldTableI
155      def Column(self, col):      def Column(self, col):
156          return self.column_map[col]          return self.column_map[col]
157    
158        def HasColumn(self, col):
159            """Return whether the table has a column with the given name or index
160            """
161            return self.column_map.has_key(col)
162    
163      def ReadRowAsDict(self, index):      def ReadRowAsDict(self, index):
164            # Implementation Strategy: Executing a completely new select
165            # statement every time this method is called is too slow. The
166            # most important usage is to read the records more or less
167            # sequentially. This happens e.g. when drawing a layer with a
168            # classification where the shapes are drawn in order of the
169            # shape ids. Another pattern is that the same row is requested
170            # several times in a row. This happens in the table view, for
171            # instance.
172    
173            # We can exploit this to make access faster by having one cursor
174            # open all the time and keeping the last row read around in case
175            # the same row is accessed again the next time and if the row
176            # index is larger than the row we have read last we simply fetch
177            # rows from the cursor until we've reached the requested row. If
178            # the requested row index is smaller then we start a new cursor.
179    
180            # FIXME: So far this scheme seems to work well enough. Obvious
181            # improvements would be to start the cursor at exactly the
182            # requested row (should be efficient and easy to do now that the
183            # id is the primary key) and to perhaps to also start a new
184            # cursor if the requested index is much larger than the last row
185            # so that we don't read and discard lots of the rows.
186    
187            # Check whether we have to start a new cursor
188          if self.read_record_cursor is None or index <self.read_record_last_row:          if self.read_record_cursor is None or index <self.read_record_last_row:
189              stmt = ("SELECT %s FROM %s;"              stmt = ("SELECT %s FROM %s;"
190                      % (", ".join([c.internal_name for c in self.columns]),                      % (", ".join([c.internal_name for c in self.columns]),
# Line 179  class TransientTableBase(table.OldTableI Line 208  class TransientTableBase(table.OldTableI
208          self.read_record_last_row = index          self.read_record_last_row = index
209          return dict(zip(self.orig_names, result))          return dict(zip(self.orig_names, result))
210    
211        def ReadValue(self, row, col):
212            """Return the value of the specified row and column
213    
214            The col parameter may be the index of the column or its name.
215            """
216            # Depending on the actual access patterns of the table data, it
217            # might be a bit faster in some circumstances to not implement
218            # this via ReadRowAsDict, but this simple implementation should
219            # be fast enough for most purposes.
220            return self.ReadRowAsDict(row)[self.column_map[col].name]
221    
222      def ValueRange(self, col):      def ValueRange(self, col):
223          col = self.column_map[col]          col = self.column_map[col]
224          iname = col.internal_name          iname = col.internal_name
# Line 200  class TransientTableBase(table.OldTableI Line 240  class TransientTableBase(table.OldTableI
240              result.append(row[0])              result.append(row[0])
241          return result          return result
242    
243        def SimpleQuery(self, left, comparison, right):
244            """Return the indices of all rows that matching a condition.
245    
246            Parameters:
247               left -- The column object for the left side of the comparison
248    
249               comparison -- The comparison operator as a string. It must be
250                             one of '==', '!=', '<', '<=', '>=', '>'
251    
252               right -- The right hand side of the comparison. It must be
253                        either a column object or a value, i.e. a string,
254                        int or float.
255    
256            The return value is a sorted list of the indices of the rows
257            where the condition is true.
258            """
259            if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
260                raise ValueError("Comparison operator %r not allowed" % comparison)
261    
262            if hasattr(right, "internal_name"):
263                right_template = right.internal_name
264                params = ()
265            else:
266                right_template = "%s"
267                params = (right,)
268    
269            query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
270                    % (self.tablename, left.internal_name, comparison,
271                       right_template)
272    
273            cursor = self.db.cursor()
274            cursor.execute(query, params)
275            result = []
276            while 1:
277                row = cursor.fetchone()
278                if row is None:
279                    break
280                result.append(row[0])
281            return result
282    
283        def Dependencies(self):
284            """Placeholder for a method in a derived class.
285    
286            Return a sequence with the tables and other data objects that
287            self depends on.
288            """
289            raise NotImplementedError
290    
291    
292  class TransientTable(TransientTableBase):  class TransientTable(TransientTableBase):
293    
# Line 222  class TransientTable(TransientTableBase) Line 310  class TransientTable(TransientTableBase)
310          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
311    
312          # copy the input table to the transient db          # copy the input table to the transient db
313          insert_template = "INSERT INTO %s (%s) VALUES (%s);" \  
314            # A key to insert to use for the formatting of the insert
315            # statement. The key must not be equal to any of the column
316            # names so we construct one by building a string of x's that is
317            # longer than any of the column names
318            id_key = max([len(col.name) for col in self.columns]) * "x"
319    
320            insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
321                                 % (self.tablename,                                 % (self.tablename,
322                                    ", ".join([col.internal_name                                    ", ".join([col.internal_name
323                                               for col in self.columns]),                                               for col in self.columns]),
324                                      id_key,
325                                    ", ".join(["%%(%s)s" % col.name                                    ", ".join(["%%(%s)s" % col.name
326                                               for col in self.columns]))                                               for col in self.columns]))
327          cursor = self.db.cursor()          cursor = self.db.cursor()
328          for i in range(table.NumRows()):          for i in range(table.NumRows()):
329              cursor.execute(insert_template, table.ReadRowAsDict(i))              row = table.ReadRowAsDict(i)
330                row[id_key] = i
331                cursor.execute(insert_template, row)
332          self.db.conn.commit()          self.db.conn.commit()
333    
334    
# Line 257  class TransientJoinedTable(TransientTabl Line 355  class TransientJoinedTable(TransientTabl
355          names of the input tables without having to create prefixes.          names of the input tables without having to create prefixes.
356          """          """
357          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
358            self.dependencies = (left_table, right_table)
359          self.left_table = left_table.transient_table()          self.left_table = left_table.transient_table()
360          self.left_field = left_field          self.left_field = left_field
361          self.right_table = right_table.transient_table()          self.right_table = right_table.transient_table()
# Line 277  class TransientJoinedTable(TransientTabl Line 376  class TransientJoinedTable(TransientTabl
376          columns = []          columns = []
377          for col in self.left_table.columns + self.right_table.columns:          for col in self.left_table.columns + self.right_table.columns:
378              if col.name in visited:              if col.name in visited:
379                    # We can't allow multiple columns with the same original
380                    # name, so omit this one. FIXME: There should be a
381                    # better solution.
382                  continue                  continue
383              columns.append(col)              columns.append(col)
384          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
385    
386          # Copy the joined data to the table.          # Copy the joined data to the table.
387          internal_names = [col.internal_name for col in self.columns]          internal_names = [col.internal_name for col in self.columns]
388          stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
389                 % (self.tablename,                  " JOIN %s ON %s = %s;"
390                    ", ".join(internal_names),                  % (self.tablename,
391                    ", ".join(internal_names),                     ", ".join(internal_names),
392                    self.left_table.tablename,                     self.left_table.tablename,
393                    self.right_table.tablename,                     ", ".join(internal_names),
394                    self.orig_to_internal[self.left_field],                     self.left_table.tablename,
395                    self.orig_to_internal[self.right_field])                     self.right_table.tablename,
396                       self.orig_to_internal[self.left_field],
397                       self.orig_to_internal[self.right_field]))
398          self.db.execute(stmt)          self.db.execute(stmt)
399    
400        def Dependencies(self):
401            """Return a tuple with the two tables the join depends on."""
402            return self.dependencies
403    
404    
405  class AutoTransientTable(table.OldTableInterfaceMixin):  class AutoTransientTable(table.OldTableInterfaceMixin):
406    
# Line 313  class AutoTransientTable(table.OldTableI Line 421  class AutoTransientTable(table.OldTableI
421      def Column(self, col):      def Column(self, col):
422          return self.table.Column(col)          return self.table.Column(col)
423    
424        def HasColumn(self, col):
425            """Return whether the table has a column with the given name or index
426            """
427            return self.table.HasColumn(col)
428    
429      def NumRows(self):      def NumRows(self):
430          return self.table.NumRows()          return self.table.NumRows()
431    
# Line 323  class AutoTransientTable(table.OldTableI Line 436  class AutoTransientTable(table.OldTableI
436          """Return the record no. record as a dict mapping field names to values          """Return the record no. record as a dict mapping field names to values
437          """          """
438          if self.t_table is not None:          if self.t_table is not None:
439              return self.t_table.read_record(record)              return self.t_table.ReadRowAsDict(record)
440          else:          else:
441              return self.table.ReadRowAsDict(record)              return self.table.ReadRowAsDict(record)
442    
443        def ReadValue(self, row, col):
444            """Return the value of the specified row and column
445    
446            The col parameter may be the index of the column or its name.
447            """
448            if self.t_table is not None:
449                return self.t_table.ReadValue(row, col)
450            else:
451                return self.table.ReadValue(row, col)
452    
453      def copy_to_transient(self):      def copy_to_transient(self):
454          """Internal: Create a transient table and copy the data into it"""          """Internal: Create a transient table and copy the data into it"""
455          self.t_table = TransientTable(self.transient_db, self)          self.t_table = TransientTable(self.transient_db, self)
# Line 342  class AutoTransientTable(table.OldTableI Line 465  class AutoTransientTable(table.OldTableI
465      def ValueRange(self, col):      def ValueRange(self, col):
466          if self.t_table is None:          if self.t_table is None:
467              self.copy_to_transient()              self.copy_to_transient()
468          (min, row), (max, row) = self.t_table.field_range(col)          return self.t_table.ValueRange(col)
469          return min, max  
470        def UniqueValues(self, col):
471            if self.t_table is None:
472                self.copy_to_transient()
473            return self.t_table.UniqueValues(col)
474    
475      def UniqueValues(self, colname):      def SimpleQuery(self, left, comparison, right):
476          if self.t_table is None:          if self.t_table is None:
477              self.copy_to_transient()              self.copy_to_transient()
478          return self.t_table.GetUniqueValues(colname)          # Make sure to use the column object of the transient table. The
479            # left argument is always a column object so we can just ask the
480            # t_table for the right object.
481            if hasattr(right, "name"):
482                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
483                                                comparison,
484                                                self.t_table.Column(right.name))
485            else:
486                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
487                                                comparison, right)
488    
489        def Dependencies(self):
490            """Return a tuple containing the original table"""
491            return (self.table,)
Legend:
Removed from v.818  
changed lines
  Added in v.984
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26