/[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 839 by bh, Tue May 6 15:54:18 2003 UTC revision 844 by bh, Tue May 6 18:05:04 2003 UTC
# Line 118  class TransientTableBase(table.OldTableI Line 118  class TransientTableBase(table.OldTableI
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 205  class TransientTableBase(table.OldTableI Line 205  class TransientTableBase(table.OldTableI
205              result.append(row[0])              result.append(row[0])
206          return result          return result
207    
208        def SimpleQuery(self, left, comparison, right):
209            """Return the indices of all rows that matching a condition.
210    
211            Parameters:
212               left -- The column object for the left side of the comparison
213    
214               comparison -- The comparison operator as a string. It must be
215                             one of '==', '!=', '<', '<=', '>=', '>'
216    
217               right -- The right hand side of the comparison. It must be
218                        either a column object or a value, i.e. a string,
219                        int or float.
220    
221            The return value is a sorted list of the indices of the rows
222            where the condition is true.
223            """
224            if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
225                raise ValueError("Comparison operator %r not allowed" % comparison)
226    
227            if hasattr(right, "internal_name"):
228                right_template = right.internal_name
229                params = ()
230            else:
231                right_template = "%s"
232                params = (right,)
233    
234            query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
235                    % (self.tablename, left.internal_name, comparison,
236                       right_template)
237    
238            cursor = self.db.cursor()
239            cursor.execute(query, params)
240            result = []
241            while 1:
242                row = cursor.fetchone()
243                if row is None:
244                    break
245                result.append(row[0])
246            return result
247    
248    
249  class TransientTable(TransientTableBase):  class TransientTable(TransientTableBase):
250    
# Line 227  class TransientTable(TransientTableBase) Line 267  class TransientTable(TransientTableBase)
267          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
268    
269          # copy the input table to the transient db          # copy the input table to the transient db
270          insert_template = "INSERT INTO %s (%s) VALUES (%s);" \  
271            # A key to insert to use for the formatting of the insert
272            # statement. The key must not be equal to any of the column
273            # names so we construct one by building a string of x's that is
274            # longer than any of the column names
275            id_key = max([len(col.name) for col in self.columns]) * "x"
276    
277            insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
278                                 % (self.tablename,                                 % (self.tablename,
279                                    ", ".join([col.internal_name                                    ", ".join([col.internal_name
280                                               for col in self.columns]),                                               for col in self.columns]),
281                                      id_key,
282                                    ", ".join(["%%(%s)s" % col.name                                    ", ".join(["%%(%s)s" % col.name
283                                               for col in self.columns]))                                               for col in self.columns]))
284          cursor = self.db.cursor()          cursor = self.db.cursor()
285          for i in range(table.NumRows()):          for i in range(table.NumRows()):
286              cursor.execute(insert_template, table.ReadRowAsDict(i))              row = table.ReadRowAsDict(i)
287                row[id_key] = i
288                cursor.execute(insert_template, row)
289          self.db.conn.commit()          self.db.conn.commit()
290    
291    
# Line 282  class TransientJoinedTable(TransientTabl Line 332  class TransientJoinedTable(TransientTabl
332          columns = []          columns = []
333          for col in self.left_table.columns + self.right_table.columns:          for col in self.left_table.columns + self.right_table.columns:
334              if col.name in visited:              if col.name in visited:
335                    # We can't allow multiple columns with the same original
336                    # name, so omit this one. FIXME: There should be a
337                    # better solution.
338                  continue                  continue
339              columns.append(col)              columns.append(col)
340          TransientTableBase.create(self, columns)          TransientTableBase.create(self, columns)
341    
342          # Copy the joined data to the table.          # Copy the joined data to the table.
343          internal_names = [col.internal_name for col in self.columns]          internal_names = [col.internal_name for col in self.columns]
344          stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \          stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
345                 % (self.tablename,                  " JOIN %s ON %s = %s;"
346                    ", ".join(internal_names),                  % (self.tablename,
347                    ", ".join(internal_names),                     ", ".join(internal_names),
348                    self.left_table.tablename,                     self.left_table.tablename,
349                    self.right_table.tablename,                     ", ".join(internal_names),
350                    self.orig_to_internal[self.left_field],                     self.left_table.tablename,
351                    self.orig_to_internal[self.right_field])                     self.right_table.tablename,
352                       self.orig_to_internal[self.left_field],
353                       self.orig_to_internal[self.right_field]))
354          self.db.execute(stmt)          self.db.execute(stmt)
355    
356    
# Line 358  class AutoTransientTable(table.OldTableI Line 413  class AutoTransientTable(table.OldTableI
413          if self.t_table is None:          if self.t_table is None:
414              self.copy_to_transient()              self.copy_to_transient()
415          return self.t_table.UniqueValues(col)          return self.t_table.UniqueValues(col)
416    
417        def SimpleQuery(self, left, comparison, right):
418            if self.t_table is None:
419                self.copy_to_transient()
420            # Make sure to use the column object of the transient table. The
421            # left argument is always a column object so we can just ask the
422            # t_table for the right object.
423            return self.t_table.SimpleQuery(self.t_table.Column(left.name),
424                                            comparison, right)
Legend:
Removed from v.839  
changed lines
  Added in v.844
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26