/[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 765 by bh, Tue Apr 29 12:42:14 2003 UTC revision 1009 by frank, Fri May 23 08:26:58 2003 UTC
# Line 53  class TransientDatabase: Line 53  class TransientDatabase:
53          self.close()          self.close()
54    
55      def close(self):      def close(self):
56          if self.conn:          if self.conn is not None:
57              self.conn.close()              self.conn.close()
58                self.conn = None
59    
60      def new_table_name(self):      def new_table_name(self):
61          self.num_tables += 1          self.num_tables += 1
# Line 84  class ColumnReference: Line 85  class ColumnReference:
85          self.internal_name = internal_name          self.internal_name = internal_name
86    
87    
88  class TransientTableBase:  class TransientTableBase(table.OldTableInterfaceMixin):
89    
90      """Base class for tables in the transient database"""      """Base class for tables in the transient database"""
91    
# Line 95  class TransientTableBase: Line 96  class TransientTableBase:
96          self.indexed_columns = {}          self.indexed_columns = {}
97          self.read_record_cursor = None          self.read_record_cursor = None
98          self.read_record_last_row = None          self.read_record_last_row = None
99            self.read_record_last_result = None
100    
101      def create(self, columns):      def create(self, columns):
102          self.columns = columns          self.columns = columns
# Line 102  class TransientTableBase: Line 104  class TransientTableBase:
104          self.orig_names = []          self.orig_names = []
105          self.internal_to_orig = {}          self.internal_to_orig = {}
106          self.orig_to_internal = {}          self.orig_to_internal = {}
107            self.column_map = {}
108    
109          # Create the column objects and fill various maps and lists          # Create the column objects and fill various maps and lists
110          for col in self.columns:          for index in range(len(self.columns)):
111                col = self.columns[index]
112              self.name_to_column[col.name] = col              self.name_to_column[col.name] = col
113              self.orig_names.append(col.name)              self.orig_names.append(col.name)
114              self.internal_to_orig[col.internal_name] = col.name              self.internal_to_orig[col.internal_name] = col.name
115              self.orig_to_internal[col.name] = col.internal_name              self.orig_to_internal[col.name] = col.internal_name
116                self.column_map[col.name] = col
117                self.column_map[index] = col
118    
119          # Build the CREATE TABLE statement and create the table in the          # Build the CREATE TABLE statement and create the table in the
120          # database          # database
121          table_types = []          table_types = ["id INTEGER PRIMARY KEY"]
122          for col in self.columns:          for col in self.columns:
123              table_types.append("%s %s" % (col.internal_name,              table_types.append("%s %s" % (col.internal_name,
124                                            sql_type_map[col.type]))                                            sql_type_map[col.type]))
# Line 136  class TransientTableBase: Line 142  class TransientTableBase:
142              self.db.execute(stmt)              self.db.execute(stmt)
143              self.indexed_columns[column] = 1              self.indexed_columns[column] = 1
144    
145      def field_count(self):      def NumColumns(self):
146          return len(self.columns)          return len(self.columns)
147    
148      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):  
149          result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)          result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)
150          return int(result[0])          return int(result[0])
151    
152      def read_record(self, index):      def Columns(self):
153            return self.columns
154    
155        def Column(self, col):
156            return self.column_map[col]
157    
158        def HasColumn(self, col):
159            """Return whether the table has a column with the given name or index
160            """
161            return self.column_map.has_key(col)
162    
163        def ReadRowAsDict(self, index):
164            # Implementation Strategy: Executing a completely new select
165            # statement every time this method is called is too slow. The
166            # most important usage is to read the records more or less
167            # sequentially. This happens e.g. when drawing a layer with a
168            # classification where the shapes are drawn in order of the
169            # shape ids. Another pattern is that the same row is requested
170            # several times in a row. This happens in the table view, for
171            # instance.
172    
173            # We can exploit this to make access faster by having one cursor
174            # open all the time and keeping the last row read around in case
175            # the same row is accessed again the next time and if the row
176            # index is larger than the row we have read last we simply fetch
177            # rows from the cursor until we've reached the requested row. If
178            # the requested row index is smaller then we start a new cursor.
179    
180            # FIXME: So far this scheme seems to work well enough. Obvious
181            # improvements would be to start the cursor at exactly the
182            # requested row (should be efficient and easy to do now that the
183            # id is the primary key) and to perhaps to also start a new
184            # cursor if the requested index is much larger than the last row
185            # so that we don't read and discard lots of the rows.
186    
187            # Check whether we have to start a new cursor
188          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:
189              stmt = ("SELECT %s FROM %s;"              stmt = ("SELECT %s FROM %s;"
190                      % (", ".join([c.internal_name for c in self.columns]),                      % (", ".join([c.internal_name for c in self.columns]),
# Line 162  class TransientTableBase: Line 192  class TransientTableBase:
192              self.read_record_cursor = self.db.cursor()              self.read_record_cursor = self.db.cursor()
193              self.read_record_cursor.execute(stmt)              self.read_record_cursor.execute(stmt)
194              self.read_record_last_row = -1              self.read_record_last_row = -1
195          for i in range(index - self.read_record_last_row):              self.read_record_last_result = None
196              result = self.read_record_cursor.fetchone()  
197            # Now we should have a cursor at a position less than or equal
198            # to the index so the following if statement will always set
199            # result to a suitable value
200            assert index >= self.read_record_last_row
201    
202            if index == self.read_record_last_row:
203                result = self.read_record_last_result
204            else:
205                for i in range(index - self.read_record_last_row):
206                    result = self.read_record_cursor.fetchone()
207                    self.read_record_last_result = result
208          self.read_record_last_row = index          self.read_record_last_row = index
209          result = dict(zip(self.orig_names, result))          return dict(zip(self.orig_names, result))
210          return result  
211        def ReadValue(self, row, col):
212            """Return the value of the specified row and column
213    
214            The col parameter may be the index of the column or its name.
215            """
216            # Depending on the actual access patterns of the table data, it
217            # might be a bit faster in some circumstances to not implement
218            # this via ReadRowAsDict, but this simple implementation should
219            # be fast enough for most purposes.
220            return self.ReadRowAsDict(row)[self.column_map[col].name]
221    
222      def field_range(self, colname):      def ValueRange(self, col):
223          col = self.name_to_column[colname]          col = self.column_map[col]
224          iname = col.internal_name          iname = col.internal_name
225          min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"          min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"
226                                     % (iname, iname, self.tablename))                                     % (iname, iname, self.tablename))
227          converter = type_converter_map[col.type]          converter = type_converter_map[col.type]
228          return ((converter(min), None), (converter(max), None))          return (converter(min), converter(max))
229    
230      def GetUniqueValues(self, colname):      def UniqueValues(self, col):
231          iname = self.orig_to_internal[colname]          iname = self.column_map[col].internal_name
232          cursor = self.db.cursor()          cursor = self.db.cursor()
233          cursor.execute("SELECT %s FROM %s GROUP BY %s;"          cursor.execute("SELECT %s FROM %s GROUP BY %s;"
234                         % (iname, self.tablename, iname))                         % (iname, self.tablename, iname))
# Line 189  class TransientTableBase: Line 240  class TransientTableBase:
240              result.append(row[0])              result.append(row[0])
241          return result          return result
242    
243        def SimpleQuery(self, left, comparison, right):
244            """Return the indices of all rows that matching a condition.
245    
246            Parameters:
247               left -- The column object for the left side of the comparison
248    
249               comparison -- The comparison operator as a string. It must be
250                             one of '==', '!=', '<', '<=', '>=', '>'
251    
252               right -- The right hand side of the comparison. It must be
253                        either a column object or a value, i.e. a string,
254                        int or float.
255    
256            The return value is a sorted list of the indices of the rows
257            where the condition is true.
258            """
259            if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
260                raise ValueError("Comparison operator %r not allowed" % comparison)
261    
262            if hasattr(right, "internal_name"):
263                right_template = right.internal_name
264                params = ()
265            else:
266                right_template = "%s"
267                params = (right,)
268    
269            query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
270                    % (self.tablename, left.internal_name, comparison,
271                       right_template)
272    
273            cursor = self.db.cursor()
274            cursor.execute(query, params)
275            result = []
276            while 1:
277                row = cursor.fetchone()
278                if row is None:
279                    break
280                result.append(row[0])
281            return result
282    
283        def Dependencies(self):
284            """Placeholder for a method in a derived class.
285    
286            Return a sequence with the tables and other data objects that
287            self depends on.
288            """
289            raise NotImplementedError
290    
291    
292  class TransientTable(TransientTableBase):  class TransientTable(TransientTableBase):
293    
# Line 201  class TransientTable(TransientTableBase) Line 300  class TransientTable(TransientTableBase)
300          interface.          interface.
301          """          """
302          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
303            self.title = table.Title()
304          self.create(table)          self.create(table)
305    
306        def Title(self):
307            """Return the title of the table.
308    
309            The title is the same as that of the original table
310            """
311            return self.title
312    
313      def create(self, table):      def create(self, table):
314          columns = []          columns = []
315          for i in range(table.field_count()):          for col in table.Columns():
316              type, name = table.field_info(i)[:2]              columns.append(ColumnReference(col.name, col.type,
             columns.append(ColumnReference(name, type,  
317                                             self.db.new_column_name()))                                             self.db.new_column_name()))
318          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
319    
320          # copy the input table to the transient db          # copy the input table to the transient db
321          insert_template = "INSERT INTO %s (%s) VALUES (%s);" \  
322            # A key to insert to use for the formatting of the insert
323            # statement. The key must not be equal to any of the column
324            # names so we construct one by building a string of x's that is
325            # longer than any of the column names
326            id_key = max([len(col.name) for col in self.columns]) * "x"
327    
328            insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
329                                 % (self.tablename,                                 % (self.tablename,
330                                    ", ".join([col.internal_name                                    ", ".join([col.internal_name
331                                               for col in self.columns]),                                               for col in self.columns]),
332                                      id_key,
333                                    ", ".join(["%%(%s)s" % col.name                                    ", ".join(["%%(%s)s" % col.name
334                                               for col in self.columns]))                                               for col in self.columns]))
335          cursor = self.db.cursor()          cursor = self.db.cursor()
336          for i in range(table.record_count()):          for i in range(table.NumRows()):
337              cursor.execute(insert_template, table.read_record(i))              row = table.ReadRowAsDict(i)
338                row[id_key] = i
339                cursor.execute(insert_template, row)
340          self.db.conn.commit()          self.db.conn.commit()
341    
342    
# Line 230  class TransientJoinedTable(TransientTabl Line 346  class TransientJoinedTable(TransientTabl
346      """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"""
347    
348      def __init__(self, transient_db, left_table, left_field,      def __init__(self, transient_db, left_table, left_field,
349                   right_table, right_field = None):                   right_table, right_field = None, outer_join = False):
350          """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.
351    
352          Both input tables, left_table and right_table must have a          Both input tables, left_table and right_table must have a
353          transient_table method that returns a table object for a table          transient_table method that returns a table object for a table
354          in the trnsient database. The join is performed on the condition          in the transient database. The join is performed on the condition
355          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
356          equal to the value of the right_field in the right_table.          equal to the value of the right_field in the right_table.
357    
# Line 247  class TransientJoinedTable(TransientTabl Line 363  class TransientJoinedTable(TransientTabl
363          names of the input tables without having to create prefixes.          names of the input tables without having to create prefixes.
364          """          """
365          TransientTableBase.__init__(self, transient_db)          TransientTableBase.__init__(self, transient_db)
366            self.dependencies = (left_table, right_table)
367          self.left_table = left_table.transient_table()          self.left_table = left_table.transient_table()
368          self.left_field = left_field          self.left_field = left_field
369          self.right_table = right_table.transient_table()          self.right_table = right_table.transient_table()
# Line 254  class TransientJoinedTable(TransientTabl Line 371  class TransientJoinedTable(TransientTabl
371              self.right_field = right_field              self.right_field = right_field
372          else:          else:
373              self.right_field = self.left_field              self.right_field = self.left_field
374            self.outer_join = outer_join
375          self.create()          self.create()
376    
377        def Title(self):
378            """Return the title of the joined table"""
379            return "Join of %(left)s and %(right)s" \
380                   % {"left": self.left_table.Title(),
381                      "right": self.right_table.Title()}
382    
383      def create(self):      def create(self):
384          """Internal: Create the table with the joined data"""          """Internal: Create the table with the joined data"""
385          self.tablename = self.db.new_table_name()          self.tablename = self.db.new_table_name()
# Line 267  class TransientJoinedTable(TransientTabl Line 391  class TransientJoinedTable(TransientTabl
391          columns = []          columns = []
392          for col in self.left_table.columns + self.right_table.columns:          for col in self.left_table.columns + self.right_table.columns:
393              if col.name in visited:              if col.name in visited:
394                    # We can't allow multiple columns with the same original
395                    # name, so omit this one. FIXME: There should be a
396                    # better solution.
397                  continue                  continue
398              columns.append(col)              columns.append(col)
399          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
400    
401          # Copy the joined data to the table.          # Copy the joined data to the table.
402          internal_names = [col.internal_name for col in self.columns]          internal_names = [col.internal_name for col in self.columns]
403          stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \          if self.outer_join:
404                 % (self.tablename,              join_operator = 'LEFT OUTER JOIN'
405                    ", ".join(internal_names),          else:
406                    ", ".join(internal_names),              join_operator = 'JOIN'
407                    self.left_table.tablename,          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
408                    self.right_table.tablename,                  " %s %s ON %s = %s;"
409                    self.orig_to_internal[self.left_field],                  % (self.tablename,
410                    self.orig_to_internal[self.right_field])                     ", ".join(internal_names),
411                       self.left_table.tablename,
412                       ", ".join(internal_names),
413                       self.left_table.tablename,
414                       join_operator,
415                       self.right_table.tablename,
416                       self.orig_to_internal[self.left_field],
417                       self.orig_to_internal[self.right_field]))
418          self.db.execute(stmt)          self.db.execute(stmt)
419    
420        def Dependencies(self):
421            """Return a tuple with the two tables the join depends on."""
422            return self.dependencies
423    
424    
425  class AutoTransientTable:  class AutoTransientTable(table.OldTableInterfaceMixin):
426    
427      """Table that copies data to a transient table on demand.      """Table that copies data to a transient table on demand.
428    
# Line 297  class AutoTransientTable: Line 435  class AutoTransientTable:
435          self.table = table          self.table = table
436          self.t_table = None          self.t_table = None
437    
438      def record_count(self):      def Title(self):
439          """Return the number of records"""          """Return the title of the table.
440          return self.table.record_count()  
441            The title is the same as that of the original table.
442      def field_count(self):          """
443          """Return the number of fields in a record"""          return self.table.Title()
444          return self.table.field_count()  
445        def Columns(self):
446      def field_info(self, field):          return self.table.Columns()
447          """Return a tuple (type, name, width, prec) for the field no. field  
448        def Column(self, col):
449          type is the data type of the field, name the name, width the          return self.table.Column(col)
450          field width in characters and prec the decimal precision.  
451          """      def HasColumn(self, col):
452          info = self.table.field_info(field)          """Return whether the table has a column with the given name or index
453          if info:          """
454              info = info[:2] + (0, 0)          return self.table.HasColumn(col)
455          return info  
456        def NumRows(self):
457      def field_info_by_name(self, fieldName):          return self.table.NumRows()
458          info = self.table.field_info_by_name(fieldName)  
459          if info:      def NumColumns(self):
460              info = info[:2] + (0, 0)          return self.table.NumColumns()
         return info  
461    
462      def read_record(self, record):      def ReadRowAsDict(self, record):
463          """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
464          """          """
465          if self.t_table is not None:          if self.t_table is not None:
466              return self.t_table.read_record(record)              return self.t_table.ReadRowAsDict(record)
467          else:          else:
468              return self.table.read_record(record)              return self.table.ReadRowAsDict(record)
469    
470      def write_record(self, record, values):      def ReadValue(self, row, col):
471          raise NotImplementedError          """Return the value of the specified row and column
472    
473            The col parameter may be the index of the column or its name.
474            """
475            if self.t_table is not None:
476                return self.t_table.ReadValue(row, col)
477            else:
478                return self.table.ReadValue(row, col)
479    
480      def copy_to_transient(self):      def copy_to_transient(self):
481          """Internal: Create a transient table and copy the data into it"""          """Internal: Create a transient table and copy the data into it"""
# Line 345  class AutoTransientTable: Line 489  class AutoTransientTable:
489              self.copy_to_transient()              self.copy_to_transient()
490          return self.t_table          return self.t_table
491    
492      def field_range(self, colname):      def ValueRange(self, col):
493            if self.t_table is None:
494                self.copy_to_transient()
495            return self.t_table.ValueRange(col)
496    
497        def UniqueValues(self, col):
498          if self.t_table is None:          if self.t_table is None:
499              self.copy_to_transient()              self.copy_to_transient()
500          return self.t_table.field_range(colname)          return self.t_table.UniqueValues(col)
501    
502      def GetUniqueValues(self, colname):      def SimpleQuery(self, left, comparison, right):
503          if self.t_table is None:          if self.t_table is None:
504              self.copy_to_transient()              self.copy_to_transient()
505          return self.t_table.GetUniqueValues(colname)          # Make sure to use the column object of the transient table. The
506            # left argument is always a column object so we can just ask the
507            # t_table for the right object.
508            if hasattr(right, "name"):
509                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
510                                                comparison,
511                                                self.t_table.Column(right.name))
512            else:
513                return self.t_table.SimpleQuery(self.t_table.Column(left.name),
514                                                comparison, right)
515    
516        def Dependencies(self):
517            """Return a tuple containing the original table"""
518            return (self.table,)
Legend:
Removed from v.765  
changed lines
  Added in v.1009
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26