/[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 998 by bh, Thu May 22 19:29:39 2003 UTC revision 1333 by frank, Tue Jul 1 15:41:08 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 240  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):      def SimpleQuery(self, left, comparison, right):
275          """Return the indices of all rows that matching a condition.          """Return the indices of all rows that matching a condition.
276    
# Line 289  class TransientTableBase(table.OldTableI Line 320  class TransientTableBase(table.OldTableI
320          raise NotImplementedError          raise NotImplementedError
321    
322    
323  class TransientTable(TransientTableBase):  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 300  class TransientTable(TransientTableBase) Line 331  class TransientTable(TransientTableBase)
331          interface.          interface.
332          """          """
333          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
334          self.title = table.Title()          TitledObject.__init__(self, table.Title())
335          self.create(table)          self.create(table)
336    
     def Title(self):  
         """Return the title of the table.  
   
         The title is the same as that of the original table  
         """  
         return self.title  
   
337      def create(self, table):      def create(self, table):
338          columns = []          columns = []
339          for col in table.Columns():          for col in table.Columns():
# Line 341  class TransientTable(TransientTableBase) Line 365  class TransientTable(TransientTableBase)
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 371  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.create()          self.outer_join = outer_join
399    
400      def Title(self):          title = "Join of %(left)s and %(right)s" \
401          """Return the title of the joined table"""                  % {"left": self.left_table.Title(),
402          return "Join of %(left)s and %(right)s" \                     "right": self.right_table.Title()}
403                 % {"left": self.left_table.Title(),          TitledObject.__init__(self, title)
404                    "right": self.right_table.Title()}  
405            self.create()
406    
407      def create(self):      def create(self):
408          """Internal: Create the table with the joined data"""          """Internal: Create the table with the joined data"""
# Line 385  class TransientJoinedTable(TransientTabl Line 410  class TransientJoinedTable(TransientTabl
410    
411          self.right_table.ensure_index(self.right_field)          self.right_table.ensure_index(self.right_field)
412    
413            # determine the internal column names to join on before
414            # coalescing the column information because if the external
415            # column names are the same they will be mapped to the same
416            # internal name afterwards.
417            internal_left_col = self.left_table.orig_to_internal[self.left_field]
418            internal_right_col = self.right_table.orig_to_internal[self.right_field]
419    
420          # Coalesce the column information          # Coalesce the column information
421          visited = {}          visited = {}
422          columns = []          columns = []
423          for col in self.left_table.columns + self.right_table.columns:          newcolumns = []
424            for table, col in (
425                [ (self.left_table.tablename, c) for c in self.left_table.columns ]
426                + [ (self.right_table.tablename, c) for c in self.right_table.columns]):
427              if col.name in visited:              if col.name in visited:
428                  # We can't allow multiple columns with the same original                  if col.name == self.left_field:
429                  # name, so omit this one. FIXME: There should be a                      continue
430                  # better solution.                  else:
431                  continue                      # We can't allow multiple columns with the same original
432              columns.append(col)                      # name, so append '_' to this one until it is unique.
433          TransientTableBase.create(self, columns)                      # FIXME: There should be a better solution.
434                        while col.name in visited:
435                            col.name = col.name + '_'
436                columns.append((table, col))
437                newcol = ColumnReference(col.name, col.type,
438                                            "Col%03d" % (len(newcolumns)+1))
439                newcolumns.append(newcol)
440                visited[col.name] = 1
441            TransientTableBase.create(self, newcolumns)
442    
443          # Copy the joined data to the table.          # Copy the joined data to the table.
444          internal_names = [col.internal_name for col in self.columns]          newinternal_names = [col.internal_name for col in self.columns]
445            internal_references = ["%s.%s" % (table, col.internal_name)
446                                                        for table, col in columns]
447            if self.outer_join:
448                join_operator = 'LEFT OUTER JOIN'
449            else:
450                join_operator = 'JOIN'
451          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
452                  " JOIN %s ON %s = %s;"                  " %s %s ON %s.%s = %s.%s;"
453                  % (self.tablename,                  % (self.tablename,
454                     ", ".join(internal_names),                     ", ".join(newinternal_names),
455                     self.left_table.tablename,                     self.left_table.tablename,
456                     ", ".join(internal_names),                     ", ".join(internal_references),
457                     self.left_table.tablename,                     self.left_table.tablename,
458                       join_operator,
459                     self.right_table.tablename,                     self.right_table.tablename,
460                     self.orig_to_internal[self.left_field],                     self.left_table.tablename,
461                     self.orig_to_internal[self.right_field]))                     internal_left_col,
462                       self.right_table.tablename,
463                       internal_right_col))
464          self.db.execute(stmt)          self.db.execute(stmt)
465    
466      def Dependencies(self):      def Dependencies(self):
# Line 416  class TransientJoinedTable(TransientTabl Line 468  class TransientJoinedTable(TransientTabl
468          return self.dependencies          return self.dependencies
469    
470    
471  class AutoTransientTable(table.OldTableInterfaceMixin):  class AutoTransientTable(TitledObject, table.OldTableInterfaceMixin):
472    
473      """Table that copies data to a transient table on demand.      """Table that copies data to a transient table on demand.
474    
# Line 425  class AutoTransientTable(table.OldTableI Line 477  class AutoTransientTable(table.OldTableI
477      """      """
478    
479      def __init__(self, transient_db, table):      def __init__(self, transient_db, table):
480            TitledObject.__init__(self, table.Title())
481          self.transient_db = transient_db          self.transient_db = transient_db
482          self.table = table          self.table = table
483          self.t_table = None          self.t_table = None
484    
     def Title(self):  
         """Return the title of the table.  
   
         The title is the same as that of the original table.  
         """  
         return self.table.Title()  
   
485      def Columns(self):      def Columns(self):
486          return self.table.Columns()          return self.table.Columns()
487    
# Line 510  class AutoTransientTable(table.OldTableI Line 556  class AutoTransientTable(table.OldTableI
556      def Dependencies(self):      def Dependencies(self):
557          """Return a tuple containing the original table"""          """Return a tuple containing the original table"""
558          return (self.table,)          return (self.table,)
559    
560        def Width(self, col):
561            return self.table.Width(col)
Legend:
Removed from v.998  
changed lines
  Added in v.1333
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26