/[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 785 by bh, Wed Apr 30 10:54:03 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 85  class ColumnReference: Line 87  class ColumnReference:
87          self.internal_name = internal_name          self.internal_name = internal_name
88    
89    
90  class TransientTableBase:  class TransientTableBase(table.OldTableInterfaceMixin):
91    
92      """Base class for tables in the transient database"""      """Base class for tables in the transient database"""
93    
# Line 104  class TransientTableBase: Line 106  class TransientTableBase:
106          self.orig_names = []          self.orig_names = []
107          self.internal_to_orig = {}          self.internal_to_orig = {}
108          self.orig_to_internal = {}          self.orig_to_internal = {}
109            self.column_map = {}
110    
111          # Create the column objects and fill various maps and lists          # Create the column objects and fill various maps and lists
112          for col in self.columns:          for index in range(len(self.columns)):
113                col = self.columns[index]
114              self.name_to_column[col.name] = col              self.name_to_column[col.name] = col
115              self.orig_names.append(col.name)              self.orig_names.append(col.name)
116              self.internal_to_orig[col.internal_name] = col.name              self.internal_to_orig[col.internal_name] = col.name
117              self.orig_to_internal[col.name] = col.internal_name              self.orig_to_internal[col.name] = col.internal_name
118                self.column_map[col.name] = col
119                self.column_map[index] = col
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 138  class TransientTableBase: Line 144  class TransientTableBase:
144              self.db.execute(stmt)              self.db.execute(stmt)
145              self.indexed_columns[column] = 1              self.indexed_columns[column] = 1
146    
147      def field_count(self):      def NumColumns(self):
148          return len(self.columns)          return len(self.columns)
149    
150      def field_info(self, i):      def NumRows(self):
         col = self.columns[i]  
         return col.type, col.name, 0, 0  
   
     def field_info_by_name(self, name):  
         for col in self.columns:  
             if col.name == name:  
                 return col.type, col.name, 0, 0  
         else:  
             return None  
   
     def record_count(self):  
151          result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)          result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)
152          return int(result[0])          return int(result[0])
153    
154      def read_record(self, index):      def Columns(self):
155            return self.columns
156    
157        def Column(self, col):
158            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):
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 172  class TransientTableBase: Line 202  class TransientTableBase:
202          assert index >= self.read_record_last_row          assert index >= self.read_record_last_row
203    
204          if index == self.read_record_last_row:          if index == self.read_record_last_row:
205              result = self.read_record_last_result              result = self.read_record_last_result
206          else:          else:
207              for i in range(index - self.read_record_last_row):              for i in range(index - self.read_record_last_row):
208                  result = self.read_record_cursor.fetchone()                  result = self.read_record_cursor.fetchone()
# Line 180  class TransientTableBase: Line 210  class TransientTableBase:
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 field_range(self, colname):      def ReadValue(self, row, col):
214          col = self.name_to_column[colname]          """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):
225            col = self.column_map[col]
226          iname = col.internal_name          iname = col.internal_name
227          min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"          min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"
228                                     % (iname, iname, self.tablename))                                     % (iname, iname, self.tablename))
229          converter = type_converter_map[col.type]          converter = type_converter_map[col.type]
230          return ((converter(min), None), (converter(max), None))          return (converter(min), converter(max))
231    
232      def GetUniqueValues(self, colname):      def UniqueValues(self, col):
233          iname = self.orig_to_internal[colname]          iname = self.column_map[col].internal_name
234          cursor = self.db.cursor()          cursor = self.db.cursor()
235          cursor.execute("SELECT %s FROM %s GROUP BY %s;"          cursor.execute("SELECT %s FROM %s GROUP BY %s;"
236                         % (iname, self.tablename, iname))                         % (iname, self.tablename, iname))
# Line 201  class TransientTableBase: Line 242  class TransientTableBase:
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):
277            """Return the indices of all rows that matching a condition.
278    
279            Parameters:
280               left -- The column object for the left side of the comparison
281    
282               comparison -- The comparison operator as a string. It must be
283                             one of '==', '!=', '<', '<=', '>=', '>'
284    
285               right -- The right hand side of the comparison. It must be
286                        either a column object or a value, i.e. a string,
287                        int or float.
288    
289            The return value is a sorted list of the indices of the rows
290            where the condition is true.
291            """
292            if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
293                raise ValueError("Comparison operator %r not allowed" % comparison)
294    
295            if hasattr(right, "internal_name"):
296                right_template = right.internal_name
297                params = ()
298            else:
299                right_template = "%s"
300                params = (right,)
301    
302            query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
303                    % (self.tablename, left.internal_name, comparison,
304                       right_template)
305    
306            cursor = self.db.cursor()
307            cursor.execute(query, params)
308            result = []
309            while 1:
310                row = cursor.fetchone()
311                if row is None:
312                    break
313                result.append(row[0])
314            return result
315    
316        def Dependencies(self):
317            """Placeholder for a method in a derived class.
318    
319            Return a sequence with the tables and other data objects that
320            self depends on.
321            """
322            raise NotImplementedError
323    
324  class TransientTable(TransientTableBase):  
325    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 213  class TransientTable(TransientTableBase) Line 333  class TransientTable(TransientTableBase)
333          interface.          interface.
334          """          """
335          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
336            TitledObject.__init__(self, table.Title())
337          self.create(table)          self.create(table)
338    
339      def create(self, table):      def create(self, table):
340          columns = []          columns = []
341          for i in range(table.field_count()):          for col in table.Columns():
342              type, name = table.field_info(i)[:2]              columns.append(ColumnReference(col.name, col.type,
             columns.append(ColumnReference(name, type,  
343                                             self.db.new_column_name()))                                             self.db.new_column_name()))
344          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
345    
346          # copy the input table to the transient db          # copy the input table to the transient db
347          insert_template = "INSERT INTO %s (%s) VALUES (%s);" \  
348            # A key to insert to use for the formatting of the insert
349            # statement. The key must not be equal to any of the column
350            # names so we construct one by building a string of x's that is
351            # longer than any of the column names
352            id_key = max([len(col.name) for col in self.columns]) * "x"
353    
354            insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
355                                 % (self.tablename,                                 % (self.tablename,
356                                    ", ".join([col.internal_name                                    ", ".join([col.internal_name
357                                               for col in self.columns]),                                               for col in self.columns]),
358                                      id_key,
359                                    ", ".join(["%%(%s)s" % col.name                                    ", ".join(["%%(%s)s" % col.name
360                                               for col in self.columns]))                                               for col in self.columns]))
361          cursor = self.db.cursor()          cursor = self.db.cursor()
362          for i in range(table.record_count()):          for i in range(table.NumRows()):
363              cursor.execute(insert_template, table.read_record(i))              row = table.ReadRowAsDict(i)
364                row[id_key] = i
365                cursor.execute(insert_template, row)
366          self.db.conn.commit()          self.db.conn.commit()
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)
392          self.left_table = left_table.transient_table()          self.left_table = left_table.transient_table()
393          self.left_field = left_field          self.left_field = left_field
394          self.right_table = right_table.transient_table()          self.right_table = right_table.transient_table()
# Line 266  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.outer_join = outer_join
400    
401            title = "Join of %(left)s and %(right)s" \
402                    % {"left": self.left_table.Title(),
403                       "right": self.right_table.Title()}
404            TitledObject.__init__(self, title)
405    
406          self.create()          self.create()
407    
408      def create(self):      def create(self):
# Line 274  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                  continue              for col in table.Columns():
427              columns.append(col)                  colname = col.name
428          TransientTableBase.create(self, columns)                  # We can't allow multiple columns with the same
429                    # original name, so append '_' to this one until
430                    # it is unique.
431                    # 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          stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \          internal_references = ["%s.%s" % (table, col.internal_name)
444                 % (self.tablename,                                                      for table, col in columns]
445                    ", ".join(internal_names),          if self.outer_join:
446                    ", ".join(internal_names),              join_operator = 'LEFT OUTER JOIN'
447                    self.left_table.tablename,          else:
448                    self.right_table.tablename,              join_operator = 'JOIN'
449                    self.orig_to_internal[self.left_field],          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
450                    self.orig_to_internal[self.right_field])                  " %s %s ON %s.%s = %s.%s;"
451                    % (self.tablename,
452                       ", ".join(newinternal_names),
453                       self.left_table.tablename,
454                       ", ".join(internal_references),
455                       self.left_table.tablename,
456                       join_operator,
457                       self.right_table.tablename,
458                       self.left_table.tablename,
459                       internal_left_col,
460                       self.right_table.tablename,
461                       internal_right_col))
462          self.db.execute(stmt)          self.db.execute(stmt)
463    
464        def Dependencies(self):
465            """Return a tuple with the two tables the join depends on."""
466            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:  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 305  class AutoTransientTable: Line 482  class AutoTransientTable:
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    
490      def record_count(self):      def Columns(self):
491          """Return the number of records"""          return self.table.Columns()
         return self.table.record_count()  
   
     def field_count(self):  
         """Return the number of fields in a record"""  
         return self.table.field_count()  
   
     def field_info(self, field):  
         """Return a tuple (type, name, width, prec) for the field no. field  
   
         type is the data type of the field, name the name, width the  
         field width in characters and prec the decimal precision.  
         """  
         info = self.table.field_info(field)  
         if info:  
             info = info[:2] + (0, 0)  
         return info  
   
     def field_info_by_name(self, fieldName):  
         info = self.table.field_info_by_name(fieldName)  
         if info:  
             info = info[:2] + (0, 0)  
         return info  
492    
493      def read_record(self, record):      def Column(self, col):
494            return self.table.Column(col)
495    
496        def HasColumn(self, col):
497            """Return whether the table has a column with the given name or index
498            """
499            return self.table.HasColumn(col)
500    
501        def NumRows(self):
502            return self.table.NumRows()
503    
504        def NumColumns(self):
505            return self.table.NumColumns()
506    
507        def ReadRowAsDict(self, record):
508          """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
509          """          """
510          if self.t_table is not None:          if self.t_table is not None:
511              return self.t_table.read_record(record)              return self.t_table.ReadRowAsDict(record)
512          else:          else:
513              return self.table.read_record(record)              return self.table.ReadRowAsDict(record)
514    
515      def write_record(self, record, values):      def ReadValue(self, row, col):
516          raise NotImplementedError          """Return the value of the specified row and column
517    
518            The col parameter may be the index of the column or its name.
519            """
520            if self.t_table is not None:
521                return self.t_table.ReadValue(row, col)
522            else:
523                return self.table.ReadValue(row, col)
524    
525      def copy_to_transient(self):      def copy_to_transient(self):
526          """Internal: Create a transient table and copy the data into it"""          """Internal: Create a transient table and copy the data into it"""
# Line 357  class AutoTransientTable: Line 534  class AutoTransientTable:
534              self.copy_to_transient()              self.copy_to_transient()
535          return self.t_table          return self.t_table
536    
537      def field_range(self, colname):      def ValueRange(self, col):
538            if self.t_table is None:
539                self.copy_to_transient()
540            return self.t_table.ValueRange(col)
541    
542        def UniqueValues(self, col):
543          if self.t_table is None:          if self.t_table is None:
544              self.copy_to_transient()              self.copy_to_transient()
545          return self.t_table.field_range(colname)          return self.t_table.UniqueValues(col)
546    
547      def GetUniqueValues(self, colname):      def SimpleQuery(self, left, comparison, right):
548          if self.t_table is None:          if self.t_table is None:
549              self.copy_to_transient()              self.copy_to_transient()
550          return self.t_table.GetUniqueValues(colname)          # Make sure to use the column object of the transient table. The
551            # left argument is always a column object so we can just ask the
552            # t_table for the right object.
553            if hasattr(right, "name"):
554                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
555                                                comparison,
556                                                self.t_table.Column(right.name))
557            else:
558                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
559                                                comparison, right)
560    
561        def Dependencies(self):
562            """Return a tuple containing the original table"""
563            return (self.table,)
564    
565        def Width(self, col):
566            return self.table.Width(col)
Legend:
Removed from v.785  
changed lines
  Added in v.1381
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26