/[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 1043 by bh, Mon May 26 19:27:15 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 155  class TransientTableBase(table.OldTableI Line 157  class TransientTableBase(table.OldTableI
157      def Column(self, col):      def Column(self, col):
158          return self.column_map[col]          return self.column_map[col]
159    
160        def HasColumn(self, col):
161            """Return whether the table has a column with the given name or index
162            """
163            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 179  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 200  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 Width(self, col):
246            """Return the maximum width of values in the column
247    
248            The return value is the the maximum length of string representation
249            of the values in the column (represented by index or name)."""
250            max = 0
251            
252            type  = self.column_map[col].type
253            iname = self.column_map[col].internal_name
254            cursor = self.db.cursor()
255            cursor.execute("SELECT %s FROM %s;" % (iname, self.tablename))
256            values = [ i[0] for i in cursor.fetchall()]
257            if not values:
258                return None
259    
260            if type == sql_type_map[table.FIELDTYPE_DOUBLE]:
261                format = "%.12f"
262            elif type == sql_type_map[table.FIELDTYPE_INT]:
263                format = "%d"
264            else:
265                format = "%s"
266            for value in values:
267                if value is None: continue
268                l = len(format % value)
269                if l > max:
270                    max = l
271    
272            return max
273    
274        def SimpleQuery(self, left, comparison, right):
275            """Return the indices of all rows that matching a condition.
276    
277            Parameters:
278               left -- The column object for the left side of the comparison
279    
280               comparison -- The comparison operator as a string. It must be
281                             one of '==', '!=', '<', '<=', '>=', '>'
282    
283               right -- The right hand side of the comparison. It must be
284                        either a column object or a value, i.e. a string,
285                        int or float.
286    
287            The return value is a sorted list of the indices of the rows
288            where the condition is true.
289            """
290            if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
291                raise ValueError("Comparison operator %r not allowed" % comparison)
292    
293            if hasattr(right, "internal_name"):
294                right_template = right.internal_name
295                params = ()
296            else:
297                right_template = "%s"
298                params = (right,)
299    
300            query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
301                    % (self.tablename, left.internal_name, comparison,
302                       right_template)
303    
304            cursor = self.db.cursor()
305            cursor.execute(query, params)
306            result = []
307            while 1:
308                row = cursor.fetchone()
309                if row is None:
310                    break
311                result.append(row[0])
312            return result
313    
314  class TransientTable(TransientTableBase):      def Dependencies(self):
315            """Placeholder for a method in a derived class.
316    
317            Return a sequence with the tables and other data objects that
318            self depends on.
319            """
320            raise NotImplementedError
321    
322    
323    class TransientTable(TitledObject, TransientTableBase):
324    
325      """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."""
326    
# Line 212  class TransientTable(TransientTableBase) Line 331  class TransientTable(TransientTableBase)
331          interface.          interface.
332          """          """
333          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
334            TitledObject.__init__(self, table.Title())
335          self.create(table)          self.create(table)
336    
337      def create(self, table):      def create(self, table):
# Line 222  class TransientTable(TransientTableBase) Line 342  class TransientTable(TransientTableBase)
342          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
343    
344          # copy the input table to the transient db          # copy the input table to the transient db
345          insert_template = "INSERT INTO %s (%s) VALUES (%s);" \  
346            # A key to insert to use for the formatting of the insert
347            # statement. The key must not be equal to any of the column
348            # names so we construct one by building a string of x's that is
349            # longer than any of the column names
350            id_key = max([len(col.name) for col in self.columns]) * "x"
351    
352            insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
353                                 % (self.tablename,                                 % (self.tablename,
354                                    ", ".join([col.internal_name                                    ", ".join([col.internal_name
355                                               for col in self.columns]),                                               for col in self.columns]),
356                                      id_key,
357                                    ", ".join(["%%(%s)s" % col.name                                    ", ".join(["%%(%s)s" % col.name
358                                               for col in self.columns]))                                               for col in self.columns]))
359          cursor = self.db.cursor()          cursor = self.db.cursor()
360          for i in range(table.NumRows()):          for i in range(table.NumRows()):
361              cursor.execute(insert_template, table.ReadRowAsDict(i))              row = table.ReadRowAsDict(i)
362                row[id_key] = i
363                cursor.execute(insert_template, row)
364          self.db.conn.commit()          self.db.conn.commit()
365    
366    
367    
368  class TransientJoinedTable(TransientTableBase):  class TransientJoinedTable(TitledObject, TransientTableBase):
369    
370      """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"""
371    
372      def __init__(self, transient_db, left_table, left_field,      def __init__(self, transient_db, left_table, left_field,
373                   right_table, right_field = None):                   right_table, right_field = None, outer_join = False):
374          """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.
375    
376          Both input tables, left_table and right_table must have a          Both input tables, left_table and right_table must have a
377          transient_table method that returns a table object for a table          transient_table method that returns a table object for a table
378          in the trnsient database. The join is performed on the condition          in the transient database. The join is performed on the condition
379          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
380          equal to the value of the right_field in the right_table.          equal to the value of the right_field in the right_table.
381    
# Line 257  class TransientJoinedTable(TransientTabl Line 387  class TransientJoinedTable(TransientTabl
387          names of the input tables without having to create prefixes.          names of the input tables without having to create prefixes.
388          """          """
389          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
390            self.dependencies = (left_table, right_table)
391          self.left_table = left_table.transient_table()          self.left_table = left_table.transient_table()
392          self.left_field = left_field          self.left_field = left_field
393          self.right_table = right_table.transient_table()          self.right_table = right_table.transient_table()
# Line 264  class TransientJoinedTable(TransientTabl Line 395  class TransientJoinedTable(TransientTabl
395              self.right_field = right_field              self.right_field = right_field
396          else:          else:
397              self.right_field = self.left_field              self.right_field = self.left_field
398            self.outer_join = outer_join
399    
400            title = "Join of %(left)s and %(right)s" \
401                    % {"left": self.left_table.Title(),
402                       "right": self.right_table.Title()}
403            TitledObject.__init__(self, title)
404    
405          self.create()          self.create()
406    
407      def create(self):      def create(self):
# Line 277  class TransientJoinedTable(TransientTabl Line 415  class TransientJoinedTable(TransientTabl
415          columns = []          columns = []
416          for col in self.left_table.columns + self.right_table.columns:          for col in self.left_table.columns + self.right_table.columns:
417              if col.name in visited:              if col.name in visited:
418                    # We can't allow multiple columns with the same original
419                    # name, so omit this one. FIXME: There should be a
420                    # better solution.
421                  continue                  continue
422              columns.append(col)              columns.append(col)
423          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
424    
425          # Copy the joined data to the table.          # Copy the joined data to the table.
426          internal_names = [col.internal_name for col in self.columns]          internal_names = [col.internal_name for col in self.columns]
427          stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \          if self.outer_join:
428                 % (self.tablename,              join_operator = 'LEFT OUTER JOIN'
429                    ", ".join(internal_names),          else:
430                    ", ".join(internal_names),              join_operator = 'JOIN'
431                    self.left_table.tablename,          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
432                    self.right_table.tablename,                  " %s %s ON %s = %s;"
433                    self.orig_to_internal[self.left_field],                  % (self.tablename,
434                    self.orig_to_internal[self.right_field])                     ", ".join(internal_names),
435                       self.left_table.tablename,
436                       ", ".join(internal_names),
437                       self.left_table.tablename,
438                       join_operator,
439                       self.right_table.tablename,
440                       self.orig_to_internal[self.left_field],
441                       self.orig_to_internal[self.right_field]))
442          self.db.execute(stmt)          self.db.execute(stmt)
443    
444        def Dependencies(self):
445            """Return a tuple with the two tables the join depends on."""
446            return self.dependencies
447    
448  class AutoTransientTable(table.OldTableInterfaceMixin):  
449    class AutoTransientTable(TitledObject, table.OldTableInterfaceMixin):
450    
451      """Table that copies data to a transient table on demand.      """Table that copies data to a transient table on demand.
452    
# Line 303  class AutoTransientTable(table.OldTableI Line 455  class AutoTransientTable(table.OldTableI
455      """      """
456    
457      def __init__(self, transient_db, table):      def __init__(self, transient_db, table):
458            TitledObject.__init__(self, table.Title())
459          self.transient_db = transient_db          self.transient_db = transient_db
460          self.table = table          self.table = table
461          self.t_table = None          self.t_table = None
# Line 313  class AutoTransientTable(table.OldTableI Line 466  class AutoTransientTable(table.OldTableI
466      def Column(self, col):      def Column(self, col):
467          return self.table.Column(col)          return self.table.Column(col)
468    
469        def HasColumn(self, col):
470            """Return whether the table has a column with the given name or index
471            """
472            return self.table.HasColumn(col)
473    
474      def NumRows(self):      def NumRows(self):
475          return self.table.NumRows()          return self.table.NumRows()
476    
# Line 323  class AutoTransientTable(table.OldTableI Line 481  class AutoTransientTable(table.OldTableI
481          """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
482          """          """
483          if self.t_table is not None:          if self.t_table is not None:
484              return self.t_table.read_record(record)              return self.t_table.ReadRowAsDict(record)
485          else:          else:
486              return self.table.ReadRowAsDict(record)              return self.table.ReadRowAsDict(record)
487    
488        def ReadValue(self, row, col):
489            """Return the value of the specified row and column
490    
491            The col parameter may be the index of the column or its name.
492            """
493            if self.t_table is not None:
494                return self.t_table.ReadValue(row, col)
495            else:
496                return self.table.ReadValue(row, col)
497    
498      def copy_to_transient(self):      def copy_to_transient(self):
499          """Internal: Create a transient table and copy the data into it"""          """Internal: Create a transient table and copy the data into it"""
500          self.t_table = TransientTable(self.transient_db, self)          self.t_table = TransientTable(self.transient_db, self)
# Line 342  class AutoTransientTable(table.OldTableI Line 510  class AutoTransientTable(table.OldTableI
510      def ValueRange(self, col):      def ValueRange(self, col):
511          if self.t_table is None:          if self.t_table is None:
512              self.copy_to_transient()              self.copy_to_transient()
513          (min, row), (max, row) = self.t_table.field_range(col)          return self.t_table.ValueRange(col)
514          return min, max  
515        def UniqueValues(self, col):
516            if self.t_table is None:
517                self.copy_to_transient()
518            return self.t_table.UniqueValues(col)
519    
520      def UniqueValues(self, colname):      def SimpleQuery(self, left, comparison, right):
521          if self.t_table is None:          if self.t_table is None:
522              self.copy_to_transient()              self.copy_to_transient()
523          return self.t_table.GetUniqueValues(colname)          # Make sure to use the column object of the transient table. The
524            # left argument is always a column object so we can just ask the
525            # t_table for the right object.
526            if hasattr(right, "name"):
527                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
528                                                comparison,
529                                                self.t_table.Column(right.name))
530            else:
531                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
532                                                comparison, right)
533    
534        def Dependencies(self):
535            """Return a tuple containing the original table"""
536            return (self.table,)
537    
538        def Width(self, col):
539            return self.table.Width(col)
Legend:
Removed from v.818  
changed lines
  Added in v.1043
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26