/[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 1020 by jan, Fri May 23 13:03:07 2003 UTC
# Line 22  import os Line 22  import os
22  import weakref  import weakref
23  from sqlite import connect  from sqlite import connect
24    
25    from base import TitledObject
26    
27  import table  import table
28    
29  sql_type_map = {  sql_type_map = {
# Line 118  class TransientTableBase(table.OldTableI Line 120  class TransientTableBase(table.OldTableI
120    
121          # Build the CREATE TABLE statement and create the table in the          # Build the CREATE TABLE statement and create the table in the
122          # database          # database
123          table_types = []          table_types = ["id INTEGER PRIMARY KEY"]
124          for col in self.columns:          for col in self.columns:
125              table_types.append("%s %s" % (col.internal_name,              table_types.append("%s %s" % (col.internal_name,
126                                            sql_type_map[col.type]))                                            sql_type_map[col.type]))
# Line 161  class TransientTableBase(table.OldTableI Line 163  class TransientTableBase(table.OldTableI
163          return self.column_map.has_key(col)          return self.column_map.has_key(col)
164    
165      def ReadRowAsDict(self, index):      def ReadRowAsDict(self, index):
166            # Implementation Strategy: Executing a completely new select
167            # statement every time this method is called is too slow. The
168            # most important usage is to read the records more or less
169            # sequentially. This happens e.g. when drawing a layer with a
170            # classification where the shapes are drawn in order of the
171            # shape ids. Another pattern is that the same row is requested
172            # several times in a row. This happens in the table view, for
173            # instance.
174    
175            # We can exploit this to make access faster by having one cursor
176            # open all the time and keeping the last row read around in case
177            # the same row is accessed again the next time and if the row
178            # index is larger than the row we have read last we simply fetch
179            # rows from the cursor until we've reached the requested row. If
180            # the requested row index is smaller then we start a new cursor.
181    
182            # FIXME: So far this scheme seems to work well enough. Obvious
183            # improvements would be to start the cursor at exactly the
184            # requested row (should be efficient and easy to do now that the
185            # id is the primary key) and to perhaps to also start a new
186            # cursor if the requested index is much larger than the last row
187            # so that we don't read and discard lots of the rows.
188    
189            # Check whether we have to start a new cursor
190          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:
191              stmt = ("SELECT %s FROM %s;"              stmt = ("SELECT %s FROM %s;"
192                      % (", ".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 210  class TransientTableBase(table.OldTableI
210          self.read_record_last_row = index          self.read_record_last_row = index
211          return dict(zip(self.orig_names, result))          return dict(zip(self.orig_names, result))
212    
213        def ReadValue(self, row, col):
214            """Return the value of the specified row and column
215    
216            The col parameter may be the index of the column or its name.
217            """
218            # Depending on the actual access patterns of the table data, it
219            # might be a bit faster in some circumstances to not implement
220            # this via ReadRowAsDict, but this simple implementation should
221            # be fast enough for most purposes.
222            return self.ReadRowAsDict(row)[self.column_map[col].name]
223    
224      def ValueRange(self, col):      def ValueRange(self, col):
225          col = self.column_map[col]          col = self.column_map[col]
226          iname = col.internal_name          iname = col.internal_name
# Line 205  class TransientTableBase(table.OldTableI Line 242  class TransientTableBase(table.OldTableI
242              result.append(row[0])              result.append(row[0])
243          return result          return result
244    
245        def SimpleQuery(self, left, comparison, right):
246            """Return the indices of all rows that matching a condition.
247    
248            Parameters:
249               left -- The column object for the left side of the comparison
250    
251               comparison -- The comparison operator as a string. It must be
252                             one of '==', '!=', '<', '<=', '>=', '>'
253    
254               right -- The right hand side of the comparison. It must be
255                        either a column object or a value, i.e. a string,
256                        int or float.
257    
258            The return value is a sorted list of the indices of the rows
259            where the condition is true.
260            """
261            if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
262                raise ValueError("Comparison operator %r not allowed" % comparison)
263    
264            if hasattr(right, "internal_name"):
265                right_template = right.internal_name
266                params = ()
267            else:
268                right_template = "%s"
269                params = (right,)
270    
271            query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
272                    % (self.tablename, left.internal_name, comparison,
273                       right_template)
274    
275            cursor = self.db.cursor()
276            cursor.execute(query, params)
277            result = []
278            while 1:
279                row = cursor.fetchone()
280                if row is None:
281                    break
282                result.append(row[0])
283            return result
284    
285        def Dependencies(self):
286            """Placeholder for a method in a derived class.
287    
288            Return a sequence with the tables and other data objects that
289            self depends on.
290            """
291            raise NotImplementedError
292    
293    
294  class TransientTable(TransientTableBase):  class TransientTable(TitledObject, TransientTableBase):
295    
296      """A Table in a transient DB that starts as the copy of a Thuban Table."""      """A Table in a transient DB that starts as the copy of a Thuban Table."""
297    
# Line 217  class TransientTable(TransientTableBase) Line 302  class TransientTable(TransientTableBase)
302          interface.          interface.
303          """          """
304          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
305            TitledObject.__init__(self, table.Title())
306          self.create(table)          self.create(table)
307    
308      def create(self, table):      def create(self, table):
# Line 227  class TransientTable(TransientTableBase) Line 313  class TransientTable(TransientTableBase)
313          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
314    
315          # copy the input table to the transient db          # copy the input table to the transient db
316          insert_template = "INSERT INTO %s (%s) VALUES (%s);" \  
317            # A key to insert to use for the formatting of the insert
318            # statement. The key must not be equal to any of the column
319            # names so we construct one by building a string of x's that is
320            # longer than any of the column names
321            id_key = max([len(col.name) for col in self.columns]) * "x"
322    
323            insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
324                                 % (self.tablename,                                 % (self.tablename,
325                                    ", ".join([col.internal_name                                    ", ".join([col.internal_name
326                                               for col in self.columns]),                                               for col in self.columns]),
327                                      id_key,
328                                    ", ".join(["%%(%s)s" % col.name                                    ", ".join(["%%(%s)s" % col.name
329                                               for col in self.columns]))                                               for col in self.columns]))
330          cursor = self.db.cursor()          cursor = self.db.cursor()
331          for i in range(table.NumRows()):          for i in range(table.NumRows()):
332              cursor.execute(insert_template, table.ReadRowAsDict(i))              row = table.ReadRowAsDict(i)
333                row[id_key] = i
334                cursor.execute(insert_template, row)
335          self.db.conn.commit()          self.db.conn.commit()
336    
337    
338    
339  class TransientJoinedTable(TransientTableBase):  class TransientJoinedTable(TitledObject, TransientTableBase):
340    
341      """A Table in the transient DB that contains a join of two tables"""      """A Table in the transient DB that contains a join of two tables"""
342    
343      def __init__(self, transient_db, left_table, left_field,      def __init__(self, transient_db, left_table, left_field,
344                   right_table, right_field = None):                   right_table, right_field = None, outer_join = False):
345          """Create a new table in the transient DB as a join of two tables.          """Create a new table in the transient DB as a join of two tables.
346    
347          Both input tables, left_table and right_table must have a          Both input tables, left_table and right_table must have a
348          transient_table method that returns a table object for a table          transient_table method that returns a table object for a table
349          in the trnsient database. The join is performed on the condition          in the transient database. The join is performed on the condition
350          that the value of the left_field column the the left table is          that the value of the left_field column the the left table is
351          equal to the value of the right_field in the right_table.          equal to the value of the right_field in the right_table.
352    
# Line 262  class TransientJoinedTable(TransientTabl Line 358  class TransientJoinedTable(TransientTabl
358          names of the input tables without having to create prefixes.          names of the input tables without having to create prefixes.
359          """          """
360          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
361            self.dependencies = (left_table, right_table)
362          self.left_table = left_table.transient_table()          self.left_table = left_table.transient_table()
363          self.left_field = left_field          self.left_field = left_field
364          self.right_table = right_table.transient_table()          self.right_table = right_table.transient_table()
# Line 269  class TransientJoinedTable(TransientTabl Line 366  class TransientJoinedTable(TransientTabl
366              self.right_field = right_field              self.right_field = right_field
367          else:          else:
368              self.right_field = self.left_field              self.right_field = self.left_field
369            self.outer_join = outer_join
370    
371            title = "Join of %(left)s and %(right)s" \
372                    % {"left": self.left_table.Title(),
373                       "right": self.right_table.Title()}
374            TitledObject.__init__(self, title)
375    
376          self.create()          self.create()
377    
378      def create(self):      def create(self):
# Line 282  class TransientJoinedTable(TransientTabl Line 386  class TransientJoinedTable(TransientTabl
386          columns = []          columns = []
387          for col in self.left_table.columns + self.right_table.columns:          for col in self.left_table.columns + self.right_table.columns:
388              if col.name in visited:              if col.name in visited:
389                    # We can't allow multiple columns with the same original
390                    # name, so omit this one. FIXME: There should be a
391                    # better solution.
392                  continue                  continue
393              columns.append(col)              columns.append(col)
394          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
395    
396          # Copy the joined data to the table.          # Copy the joined data to the table.
397          internal_names = [col.internal_name for col in self.columns]          internal_names = [col.internal_name for col in self.columns]
398          stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \          if self.outer_join:
399                 % (self.tablename,              join_operator = 'LEFT OUTER JOIN'
400                    ", ".join(internal_names),          else:
401                    ", ".join(internal_names),              join_operator = 'JOIN'
402                    self.left_table.tablename,          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
403                    self.right_table.tablename,                  " %s %s ON %s = %s;"
404                    self.orig_to_internal[self.left_field],                  % (self.tablename,
405                    self.orig_to_internal[self.right_field])                     ", ".join(internal_names),
406                       self.left_table.tablename,
407                       ", ".join(internal_names),
408                       self.left_table.tablename,
409                       join_operator,
410                       self.right_table.tablename,
411                       self.orig_to_internal[self.left_field],
412                       self.orig_to_internal[self.right_field]))
413          self.db.execute(stmt)          self.db.execute(stmt)
414    
415        def Dependencies(self):
416            """Return a tuple with the two tables the join depends on."""
417            return self.dependencies
418    
419    
420  class AutoTransientTable(table.OldTableInterfaceMixin):  class AutoTransientTable(TitledObject, table.OldTableInterfaceMixin):
421    
422      """Table that copies data to a transient table on demand.      """Table that copies data to a transient table on demand.
423    
# Line 308  class AutoTransientTable(table.OldTableI Line 426  class AutoTransientTable(table.OldTableI
426      """      """
427    
428      def __init__(self, transient_db, table):      def __init__(self, transient_db, table):
429            TitledObject.__init__(self, table.Title())
430          self.transient_db = transient_db          self.transient_db = transient_db
431          self.table = table          self.table = table
432          self.t_table = None          self.t_table = None
# Line 337  class AutoTransientTable(table.OldTableI Line 456  class AutoTransientTable(table.OldTableI
456          else:          else:
457              return self.table.ReadRowAsDict(record)              return self.table.ReadRowAsDict(record)
458    
459        def ReadValue(self, row, col):
460            """Return the value of the specified row and column
461    
462            The col parameter may be the index of the column or its name.
463            """
464            if self.t_table is not None:
465                return self.t_table.ReadValue(row, col)
466            else:
467                return self.table.ReadValue(row, col)
468    
469      def copy_to_transient(self):      def copy_to_transient(self):
470          """Internal: Create a transient table and copy the data into it"""          """Internal: Create a transient table and copy the data into it"""
471          self.t_table = TransientTable(self.transient_db, self)          self.t_table = TransientTable(self.transient_db, self)
# Line 358  class AutoTransientTable(table.OldTableI Line 487  class AutoTransientTable(table.OldTableI
487          if self.t_table is None:          if self.t_table is None:
488              self.copy_to_transient()              self.copy_to_transient()
489          return self.t_table.UniqueValues(col)          return self.t_table.UniqueValues(col)
490    
491        def SimpleQuery(self, left, comparison, right):
492            if self.t_table is None:
493                self.copy_to_transient()
494            # Make sure to use the column object of the transient table. The
495            # left argument is always a column object so we can just ask the
496            # t_table for the right object.
497            if hasattr(right, "name"):
498                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
499                                                comparison,
500                                                self.t_table.Column(right.name))
501            else:
502                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
503                                                comparison, right)
504    
505        def Dependencies(self):
506            """Return a tuple containing the original table"""
507            return (self.table,)
Legend:
Removed from v.839  
changed lines
  Added in v.1020
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26