/[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 1002 by frank, Thu May 22 19:41:57 2003 UTC revision 1364 by bh, Thu Jul 3 13:09:43 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
# Line 355  class TransientJoinedTable(TransientTabl Line 379  class TransientJoinedTable(TransientTabl
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    
382          The joined table contains all columns of the input tables with          The joined table contains all columns of the input tables,
383          one exception: Any column in the right_table with the same name          however, the column names of the right table may be changed
384          as one of the columns in the left_table will be omitted. This is          slightly to make them unique in the joined table. This is
385          somewhat of an implementation detail, but is done so that the          currently done by appending a sufficient number of underscores
386          column names of the joined table can be the same as the column          ('_').
         names of the input tables without having to create prefixes.  
387          """          """
388          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
389          self.dependencies = (left_table, right_table)          self.dependencies = (left_table, right_table)
# Line 371  class TransientJoinedTable(TransientTabl Line 394  class TransientJoinedTable(TransientTabl
394              self.right_field = right_field              self.right_field = right_field
395          else:          else:
396              self.right_field = self.left_field              self.right_field = self.left_field
397          self.create()          self.outer_join = outer_join
398    
399      def Title(self):          title = "Join of %(left)s and %(right)s" \
400          """Return the title of the joined table"""                  % {"left": self.left_table.Title(),
401          return "Join of %(left)s and %(right)s" \                     "right": self.right_table.Title()}
402                 % {"left": self.left_table.Title(),          TitledObject.__init__(self, title)
403                    "right": self.right_table.Title()}  
404            self.create()
405    
406      def create(self):      def create(self):
407          """Internal: Create the table with the joined data"""          """Internal: Create the table with the joined data"""
# Line 385  class TransientJoinedTable(TransientTabl Line 409  class TransientJoinedTable(TransientTabl
409    
410          self.right_table.ensure_index(self.right_field)          self.right_table.ensure_index(self.right_field)
411    
412            # determine the internal column names to join on before
413            # coalescing the column information because if the external
414            # column names are the same they will be mapped to the same
415            # internal name afterwards.
416            internal_left_col = self.left_table.orig_to_internal[self.left_field]
417            internal_right_col =self.right_table.orig_to_internal[self.right_field]
418    
419          # Coalesce the column information          # Coalesce the column information
420          visited = {}          visited = {}
421          columns = []          columns = []
422          for col in self.left_table.columns + self.right_table.columns:          newcolumns = []
423              if col.name in visited:          for table in (self.left_table, self.right_table):
424                  # We can't allow multiple columns with the same original              for col in table.Columns():
425                  # name, so omit this one. FIXME: There should be a                  colname = col.name
426                  # better solution.                  # We can't allow multiple columns with the same
427                  continue                  # original name, so append '_' to this one until
428              columns.append(col)                  # it is unique.
429          TransientTableBase.create(self, columns)                  # FIXME: There should be a better solution.
430                    while colname in visited:
431                        colname = colname + '_'
432                    columns.append((table.tablename, col))
433                    newcol = ColumnReference(colname, col.type,
434                                                "Col%03d" % (len(newcolumns)+1))
435                    newcolumns.append(newcol)
436                    visited[colname] = 1
437            TransientTableBase.create(self, newcolumns)
438    
439          # Copy the joined data to the table.          # Copy the joined data to the table.
440          internal_names = [col.internal_name for col in self.columns]          newinternal_names = [col.internal_name for col in self.columns]
441            internal_references = ["%s.%s" % (table, col.internal_name)
442                                                        for table, col in columns]
443            if self.outer_join:
444                join_operator = 'LEFT OUTER JOIN'
445            else:
446                join_operator = 'JOIN'
447          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
448                  " JOIN %s ON %s = %s;"                  " %s %s ON %s.%s = %s.%s;"
449                  % (self.tablename,                  % (self.tablename,
450                     ", ".join(internal_names),                     ", ".join(newinternal_names),
451                     self.left_table.tablename,                     self.left_table.tablename,
452                     ", ".join(internal_names),                     ", ".join(internal_references),
453                     self.left_table.tablename,                     self.left_table.tablename,
454                       join_operator,
455                     self.right_table.tablename,                     self.right_table.tablename,
456                     self.orig_to_internal[self.left_field],                     self.left_table.tablename,
457                     self.orig_to_internal[self.right_field]))                     internal_left_col,
458                       self.right_table.tablename,
459                       internal_right_col))
460          self.db.execute(stmt)          self.db.execute(stmt)
461    
462      def Dependencies(self):      def Dependencies(self):
# Line 416  class TransientJoinedTable(TransientTabl Line 464  class TransientJoinedTable(TransientTabl
464          return self.dependencies          return self.dependencies
465    
466    
467  class AutoTransientTable(table.OldTableInterfaceMixin):  class AutoTransientTable(TitledObject, table.OldTableInterfaceMixin):
468    
469      """Table that copies data to a transient table on demand.      """Table that copies data to a transient table on demand.
470    
# Line 425  class AutoTransientTable(table.OldTableI Line 473  class AutoTransientTable(table.OldTableI
473      """      """
474    
475      def __init__(self, transient_db, table):      def __init__(self, transient_db, table):
476            TitledObject.__init__(self, table.Title())
477          self.transient_db = transient_db          self.transient_db = transient_db
478          self.table = table          self.table = table
479          self.t_table = None          self.t_table = None
480    
     def Title(self):  
         """Return the title of the table.  
   
         The title is the same as that of the original table.  
         """  
         return self.table.Title()  
   
481      def Columns(self):      def Columns(self):
482          return self.table.Columns()          return self.table.Columns()
483    
# Line 510  class AutoTransientTable(table.OldTableI Line 552  class AutoTransientTable(table.OldTableI
552      def Dependencies(self):      def Dependencies(self):
553          """Return a tuple containing the original table"""          """Return a tuple containing the original table"""
554          return (self.table,)          return (self.table,)
555    
556        def Width(self, col):
557            return self.table.Width(col)
Legend:
Removed from v.1002  
changed lines
  Added in v.1364
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26