/[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

trunk/thuban/Thuban/Model/transientdb.py revision 1662 by bh, Wed Aug 27 13:51:01 2003 UTC branches/WIP-pyshapelib-bramz/Thuban/Model/transientdb.py revision 2734 by bramz, Thu Mar 1 12:42:59 2007 UTC
# Line 18  __version__ = "$Revision$" Line 18  __version__ = "$Revision$"
18  # $Source$  # $Source$
19  # $Id$  # $Id$
20    
21  from sqlite import connect  # Pysqlite version 1. and 2. behaves quiet differently
22    # Pysqlite uses a different paramstyle.  The older version
23    # support format and pyformat while pysqlite2 supports only qmark
24    # and named.
25    # The sqlite2 boolean variable is used to manage specific part of the code
26    try:
27          # Using SQLITE 2.x
28        sqlite2 = True
29        from pysqlite2 import dbapi2 as sqlite
30    except ImportError:
31          # Using SQLITE 1.x
32        sqlite2 = False
33        import sqlite
34        
35    
36  from base import TitledObject  from base import TitledObject
37    
# Line 40  class TransientDatabase: Line 53  class TransientDatabase:
53    
54      def __init__(self, filename):      def __init__(self, filename):
55          self.filename = filename          self.filename = filename
56          self.conn = connect(filename)          self.conn = sqlite.connect(filename)
57          # Counters to produce unique table and column names          # Counters to produce unique table and column names
58          self.num_tables = 0          self.num_tables = 0
59          self.num_cols = 0          self.num_cols = 0
# Line 85  class ColumnReference: Line 98  class ColumnReference:
98          self.internal_name = internal_name          self.internal_name = internal_name
99    
100    
101  class TransientTableBase(table.OldTableInterfaceMixin):  class TransientTableBase:
102    
103      """Base class for tables in the transient database"""      """Base class for tables in the transient database"""
104    
# Line 248  class TransientTableBase(table.OldTableI Line 261  class TransientTableBase(table.OldTableI
261          return self.ReadRowAsDict(row)[self.column_map[col].name]          return self.ReadRowAsDict(row)[self.column_map[col].name]
262    
263      def ValueRange(self, col):      def ValueRange(self, col):
264            # Performance notes:
265            #
266            # In sqlite 2.8.6 the min and max aggregate functions can use an
267            # index but only when used as the only expression in the select
268            # statement (i.e. 'select min(col), max(col) from tbl;' will not
269            # use the index but 'select min(col) from tbl;' will) so we
270            # query the minimum and maximum separately.
271            #
272            # With the separate statements we can take advantage of an index
273            # if it exists. If the index doesn't exist, creating it first
274            # and then using it in the query is slower than the queries
275            # without an index. Creating the index is only an advantage if
276            # the queries are performed multiple times. With the current use
277            # patterns where ValueRange is only used occasionally by the
278            # classification generation dialog creating the index only for
279            # this usage is not really worth it, so we don't.
280          col = self.column_map[col]          col = self.column_map[col]
281          iname = col.internal_name          iname = col.internal_name
282          min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"          min = self.db.execute("SELECT min(%s) FROM %s;"
283                                     % (iname, iname, self.tablename))                                % (iname, self.tablename))[0]
284            max = self.db.execute("SELECT max(%s) FROM %s;"
285                                  % (iname, self.tablename))[0]
286          converter = type_converter_map[col.type]          converter = type_converter_map[col.type]
287          return (converter(min), converter(max))          return (converter(min), converter(max))
288    
289      def UniqueValues(self, col):      def UniqueValues(self, col):
290            # Performance notes:
291            #
292            # In sqlite 2.8.6 there doesn't seem to be a way to query the
293            # unique items that uses an index. I've tried
294            #
295            #   SELECT col FROM tbl GROUP BY col;
296            #
297            # and
298            #
299            #   SELECT DISTINCT col FROM tbl;
300            #
301            # and in both cases the index is not used. If the index isn't
302            # used it doesn't make sense to call self.ensure_index.
303          iname = self.column_map[col].internal_name          iname = self.column_map[col].internal_name
304          cursor = self.db.cursor()          cursor = self.db.cursor()
305          cursor.execute("SELECT %s FROM %s GROUP BY %s;"          cursor.execute("SELECT %s FROM %s GROUP BY %s;"
# Line 322  class TransientTableBase(table.OldTableI Line 366  class TransientTableBase(table.OldTableI
366              right_template = right.internal_name              right_template = right.internal_name
367              params = ()              params = ()
368          else:          else:
369              right_template = "%s"              if sqlite2:
370                    right_template = "?"
371                else: right_template = "%s"
372              params = (right,)              params = (right,)
373    
374          query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \          query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
# Line 377  class TransientTable(TitledObject, Trans Line 423  class TransientTable(TitledObject, Trans
423          # longer than any of the column names          # longer than any of the column names
424          id_key = max([len(col.name) for col in self.columns]) * "x"          id_key = max([len(col.name) for col in self.columns]) * "x"
425    
426          insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \          if sqlite2:
427                insert_template = "INSERT INTO %s (id, %s) VALUES (%s, %s);" \
428                                   % (self.tablename,
429                                      ", ".join([col.internal_name
430                                                 for col in self.columns]),
431                                      '?',
432                                      ", ".join(["?" for col in self.columns]))
433    
434            else:
435                insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
436                                 % (self.tablename,                                 % (self.tablename,
437                                    ", ".join([col.internal_name                                    ", ".join([col.internal_name
438                                               for col in self.columns]),                                               for col in self.columns]),
# Line 388  class TransientTable(TitledObject, Trans Line 443  class TransientTable(TitledObject, Trans
443          for i in range(table.NumRows()):          for i in range(table.NumRows()):
444              row = table.ReadRowAsDict(i)              row = table.ReadRowAsDict(i)
445              row[id_key] = i              row[id_key] = i
446              cursor.execute(insert_template, row)              if sqlite2:
447                    params = [i]
448                    for col in self.columns:
449                      params.append(row[col.name])
450                    cursor.execute(insert_template, params)
451                else:  
452                    cursor.execute(insert_template, row)
453          self.db.conn.commit()          self.db.conn.commit()
454    
455    
# Line 499  class TransientJoinedTable(TitledObject, Line 560  class TransientJoinedTable(TitledObject,
560              return "INNER"              return "INNER"
561    
562    
563  class AutoTransientTable(TitledObject, table.OldTableInterfaceMixin):  class AutoTransientTable(TitledObject):
564    
565      """Table that copies data to a transient table on demand.      """Table that copies data to a transient table on demand.
566    
# Line 579  class AutoTransientTable(TitledObject, t Line 640  class AutoTransientTable(TitledObject, t
640          return self.t_table          return self.t_table
641    
642      def ValueRange(self, col):      def ValueRange(self, col):
643          if self.t_table is None:          # Performance of sqlite vs. DBF for this method:
644              self.copy_to_transient()          #
645          return self.t_table.ValueRange(col)          # If the table has been copied to the sqlite database it's
646            # faster to use it even if there is no index on that column.
647            # Otherwise it's faster to simply loop through all rows in the
648            # DBF file. Copying the data to the sqlite database can take
649            # very long for large amounts of data
650            #
651            # Of course if the table is not a DBF file the issue could be
652            # different, although copying the data into sqlite first will
653            # likely always be slower than simply querying the non-sqlite
654            # table directly. Currently only DBFfiles and memory tables are
655            # used as the underlying non-sqlite table, though.
656            if self.t_table is not None:
657                return self.t_table.ValueRange(col)
658            else:
659                return self.table.ValueRange(col)
660    
661      def UniqueValues(self, col):      def UniqueValues(self, col):
662          if self.t_table is None:          # The performance trade-offs for this method are basically the
663              self.copy_to_transient()          # same as for ValueRange except that currently there doesn't
664          return self.t_table.UniqueValues(col)          # seem to be a way to take advantage of indexes in this case in
665            # sqlite. However, but it's still faster to query the transient
666            # table if it already exists.
667            if self.t_table is not None:
668                return self.t_table.UniqueValues(col)
669            else:
670                return self.table.UniqueValues(col)
671    
672      def SimpleQuery(self, left, comparison, right):      def SimpleQuery(self, left, comparison, right):
673          if self.t_table is None:          if self.t_table is None:
# Line 608  class AutoTransientTable(TitledObject, t Line 689  class AutoTransientTable(TitledObject, t
689    
690      def Width(self, col):      def Width(self, col):
691          return self.table.Width(col)          return self.table.Width(col)
692    
693        def write_record(self, row, values):
694            """Write the values to the given row.
695    
696            This is a very experimental feature which doesn't work in all
697            cases, so you better know what you're doing when calling this
698            method.
699            """
700            self.table.write_record(row, values)
Legend:
Removed from v.1662  
changed lines
  Added in v.2734
[email protected]
 ViewVC Help
Powered by ViewVC 1.1.26