/[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 839 by bh, Tue May 6 15:54:18 2003 UTC revision 849 by bh, Wed May 7 11:55:31 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 161  class TransientTableBase(table.OldTableI Line 161  class TransientTableBase(table.OldTableI
161          return self.column_map.has_key(col)          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 184  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 205  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    
284  class TransientTable(TransientTableBase):  class TransientTable(TransientTableBase):
285    
# Line 227  class TransientTable(TransientTableBase) Line 302  class TransientTable(TransientTableBase)
302          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
303    
304          # copy the input table to the transient db          # copy the input table to the transient db
305          insert_template = "INSERT INTO %s (%s) VALUES (%s);" \  
306            # A key to insert to use for the formatting of the insert
307            # statement. The key must not be equal to any of the column
308            # names so we construct one by building a string of x's that is
309            # longer than any of the column names
310            id_key = max([len(col.name) for col in self.columns]) * "x"
311    
312            insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
313                                 % (self.tablename,                                 % (self.tablename,
314                                    ", ".join([col.internal_name                                    ", ".join([col.internal_name
315                                               for col in self.columns]),                                               for col in self.columns]),
316                                      id_key,
317                                    ", ".join(["%%(%s)s" % col.name                                    ", ".join(["%%(%s)s" % col.name
318                                               for col in self.columns]))                                               for col in self.columns]))
319          cursor = self.db.cursor()          cursor = self.db.cursor()
320          for i in range(table.NumRows()):          for i in range(table.NumRows()):
321              cursor.execute(insert_template, table.ReadRowAsDict(i))              row = table.ReadRowAsDict(i)
322                row[id_key] = i
323                cursor.execute(insert_template, row)
324          self.db.conn.commit()          self.db.conn.commit()
325    
326    
# Line 282  class TransientJoinedTable(TransientTabl Line 367  class TransientJoinedTable(TransientTabl
367          columns = []          columns = []
368          for col in self.left_table.columns + self.right_table.columns:          for col in self.left_table.columns + self.right_table.columns:
369              if col.name in visited:              if col.name in visited:
370                    # We can't allow multiple columns with the same original
371                    # name, so omit this one. FIXME: There should be a
372                    # better solution.
373                  continue                  continue
374              columns.append(col)              columns.append(col)
375          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
376    
377          # Copy the joined data to the table.          # Copy the joined data to the table.
378          internal_names = [col.internal_name for col in self.columns]          internal_names = [col.internal_name for col in self.columns]
379          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"
380                 % (self.tablename,                  " JOIN %s ON %s = %s;"
381                    ", ".join(internal_names),                  % (self.tablename,
382                    ", ".join(internal_names),                     ", ".join(internal_names),
383                    self.left_table.tablename,                     self.left_table.tablename,
384                    self.right_table.tablename,                     ", ".join(internal_names),
385                    self.orig_to_internal[self.left_field],                     self.left_table.tablename,
386                    self.orig_to_internal[self.right_field])                     self.right_table.tablename,
387                       self.orig_to_internal[self.left_field],
388                       self.orig_to_internal[self.right_field]))
389          self.db.execute(stmt)          self.db.execute(stmt)
390    
391    
# Line 337  class AutoTransientTable(table.OldTableI Line 427  class AutoTransientTable(table.OldTableI
427          else:          else:
428              return self.table.ReadRowAsDict(record)              return self.table.ReadRowAsDict(record)
429    
430        def ReadValue(self, row, col):
431            """Return the value of the specified row and column
432    
433            The col parameter may be the index of the column or its name.
434            """
435            if self.t_table is not None:
436                return self.t_table.ReadValue(row, col)
437            else:
438                return self.table.ReadValue(row, col)
439    
440      def copy_to_transient(self):      def copy_to_transient(self):
441          """Internal: Create a transient table and copy the data into it"""          """Internal: Create a transient table and copy the data into it"""
442          self.t_table = TransientTable(self.transient_db, self)          self.t_table = TransientTable(self.transient_db, self)
# Line 358  class AutoTransientTable(table.OldTableI Line 458  class AutoTransientTable(table.OldTableI
458          if self.t_table is None:          if self.t_table is None:
459              self.copy_to_transient()              self.copy_to_transient()
460          return self.t_table.UniqueValues(col)          return self.t_table.UniqueValues(col)
461    
462        def SimpleQuery(self, left, comparison, right):
463            if self.t_table is None:
464                self.copy_to_transient()
465            # Make sure to use the column object of the transient table. The
466            # left argument is always a column object so we can just ask the
467            # t_table for the right object.
468            return self.t_table.SimpleQuery(self.t_table.Column(left.name),
469                                            comparison, right)
Legend:
Removed from v.839  
changed lines
  Added in v.849
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26