/[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 1381 by bh, Tue Jul 8 16:37:46 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
249            representation of the values in the column (represented by index
250            or name).
251            """
252            max = 0
253    
254            type  = self.column_map[col].type
255            iname = self.column_map[col].internal_name
256            cursor = self.db.cursor()
257            cursor.execute("SELECT %s FROM %s;" % (iname, self.tablename))
258            values = [ i[0] for i in cursor.fetchall()]
259            if not values:
260                return None
261    
262            if type == table.FIELDTYPE_DOUBLE:
263                format = "%.12f"
264            elif type == table.FIELDTYPE_INT:
265                format = "%d"
266            else:
267                format = "%s"
268            for value in values:
269                if value is None: continue
270                l = len(format % value)
271                if l > max:
272                    max = l
273    
274            return max
275    
276      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
277          """Return the indices of all rows that matching a condition.          """Return the indices of all rows that matching a condition.
278    
# Line 289  class TransientTableBase(table.OldTableI Line 322  class TransientTableBase(table.OldTableI
322          raise NotImplementedError          raise NotImplementedError
323    
324    
325  class TransientTable(TransientTableBase):  class TransientTable(TitledObject, TransientTableBase):
326    
327      """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."""
328    
# Line 300  class TransientTable(TransientTableBase) Line 333  class TransientTable(TransientTableBase)
333          interface.          interface.
334          """          """
335          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
336          self.title = table.Title()          TitledObject.__init__(self, table.Title())
337          self.create(table)          self.create(table)
338    
     def Title(self):  
         """Return the title of the table.  
   
         The title is the same as that of the original table  
         """  
         return self.title  
   
339      def create(self, table):      def create(self, table):
340          columns = []          columns = []
341          for col in table.Columns():          for col in table.Columns():
# Line 341  class TransientTable(TransientTableBase) Line 367  class TransientTable(TransientTableBase)
367    
368    
369    
370  class TransientJoinedTable(TransientTableBase):  class TransientJoinedTable(TitledObject, TransientTableBase):
371    
372      """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"""
373    
374      def __init__(self, transient_db, left_table, left_field,      def __init__(self, transient_db, left_table, left_field,
375                   right_table, right_field = None):                   right_table, right_field = None, outer_join = False):
376          """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.
377    
378          Both input tables, left_table and right_table must have a          Both input tables, left_table and right_table must have a
379          transient_table method that returns a table object for a table          transient_table method that returns a table object for a table
380          in the trnsient database. The join is performed on the condition          in the transient database. The join is performed on the condition
381          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
382          equal to the value of the right_field in the right_table.          equal to the value of the right_field in the right_table.
383    
384          The joined table contains all columns of the input tables with          The joined table contains all columns of the input tables,
385          one exception: Any column in the right_table with the same name          however, the column names of the right table may be changed
386          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
387          somewhat of an implementation detail, but is done so that the          currently done by appending a sufficient number of underscores
388          column names of the joined table can be the same as the column          ('_').
         names of the input tables without having to create prefixes.  
389          """          """
390          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
391          self.dependencies = (left_table, right_table)          self.dependencies = (left_table, right_table)
# Line 371  class TransientJoinedTable(TransientTabl Line 396  class TransientJoinedTable(TransientTabl
396              self.right_field = right_field              self.right_field = right_field
397          else:          else:
398              self.right_field = self.left_field              self.right_field = self.left_field
399          self.create()          self.outer_join = outer_join
400    
401      def Title(self):          title = "Join of %(left)s and %(right)s" \
402          """Return the title of the joined table"""                  % {"left": self.left_table.Title(),
403          return "Join of %(left)s and %(right)s" \                     "right": self.right_table.Title()}
404                 % {"left": self.left_table.Title(),          TitledObject.__init__(self, title)
405                    "right": self.right_table.Title()}  
406            self.create()
407    
408      def create(self):      def create(self):
409          """Internal: Create the table with the joined data"""          """Internal: Create the table with the joined data"""
# Line 385  class TransientJoinedTable(TransientTabl Line 411  class TransientJoinedTable(TransientTabl
411    
412          self.right_table.ensure_index(self.right_field)          self.right_table.ensure_index(self.right_field)
413    
414            # determine the internal column names to join on before
415            # coalescing the column information because if the external
416            # column names are the same they will be mapped to the same
417            # internal name afterwards.
418            internal_left_col = self.left_table.orig_to_internal[self.left_field]
419            internal_right_col =self.right_table.orig_to_internal[self.right_field]
420    
421          # Coalesce the column information          # Coalesce the column information
422          visited = {}          visited = {}
423          columns = []          columns = []
424          for col in self.left_table.columns + self.right_table.columns:          newcolumns = []
425              if col.name in visited:          for table in (self.left_table, self.right_table):
426                  # We can't allow multiple columns with the same original              for col in table.Columns():
427                  # name, so omit this one. FIXME: There should be a                  colname = col.name
428                  # better solution.                  # We can't allow multiple columns with the same
429                  continue                  # original name, so append '_' to this one until
430              columns.append(col)                  # it is unique.
431          TransientTableBase.create(self, columns)                  # FIXME: There should be a better solution.
432                    while colname in visited:
433                        colname = colname + '_'
434                    columns.append((table.tablename, col))
435                    newcol = ColumnReference(colname, col.type,
436                                                "Col%03d" % (len(newcolumns)+1))
437                    newcolumns.append(newcol)
438                    visited[colname] = 1
439            TransientTableBase.create(self, newcolumns)
440    
441          # Copy the joined data to the table.          # Copy the joined data to the table.
442          internal_names = [col.internal_name for col in self.columns]          newinternal_names = [col.internal_name for col in self.columns]
443            internal_references = ["%s.%s" % (table, col.internal_name)
444                                                        for table, col in columns]
445            if self.outer_join:
446                join_operator = 'LEFT OUTER JOIN'
447            else:
448                join_operator = 'JOIN'
449          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
450                  " JOIN %s ON %s = %s;"                  " %s %s ON %s.%s = %s.%s;"
451                  % (self.tablename,                  % (self.tablename,
452                     ", ".join(internal_names),                     ", ".join(newinternal_names),
453                       self.left_table.tablename,
454                       ", ".join(internal_references),
455                     self.left_table.tablename,                     self.left_table.tablename,
456                     ", ".join(internal_names),                     join_operator,
457                       self.right_table.tablename,
458                     self.left_table.tablename,                     self.left_table.tablename,
459                       internal_left_col,
460                     self.right_table.tablename,                     self.right_table.tablename,
461                     self.orig_to_internal[self.left_field],                     internal_right_col))
                    self.orig_to_internal[self.right_field]))  
462          self.db.execute(stmt)          self.db.execute(stmt)
463    
464      def Dependencies(self):      def Dependencies(self):
465          """Return a tuple with the two tables the join depends on."""          """Return a tuple with the two tables the join depends on."""
466          return self.dependencies          return self.dependencies
467    
468        def JoinType(self):
469            """Return the type of the join (either 'INNER' or 'LEFT OUTER')"""
470            if self.outer_join:
471                return "LEFT OUTER"
472            else:
473                return "INNER"
474    
475    
476  class AutoTransientTable(table.OldTableInterfaceMixin):  class AutoTransientTable(TitledObject, table.OldTableInterfaceMixin):
477    
478      """Table that copies data to a transient table on demand.      """Table that copies data to a transient table on demand.
479    
# Line 425  class AutoTransientTable(table.OldTableI Line 482  class AutoTransientTable(table.OldTableI
482      """      """
483    
484      def __init__(self, transient_db, table):      def __init__(self, transient_db, table):
485            TitledObject.__init__(self, table.Title())
486          self.transient_db = transient_db          self.transient_db = transient_db
487          self.table = table          self.table = table
488          self.t_table = None          self.t_table = None
489    
     def Title(self):  
         """Return the title of the table.  
   
         The title is the same as that of the original table.  
         """  
         return self.table.Title()  
   
490      def Columns(self):      def Columns(self):
491          return self.table.Columns()          return self.table.Columns()
492    
# Line 510  class AutoTransientTable(table.OldTableI Line 561  class AutoTransientTable(table.OldTableI
561      def Dependencies(self):      def Dependencies(self):
562          """Return a tuple containing the original table"""          """Return a tuple containing the original table"""
563          return (self.table,)          return (self.table,)
564    
565        def Width(self, col):
566            return self.table.Width(col)
Legend:
Removed from v.998  
changed lines
  Added in v.1381
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26