Thuban/Model/transientdb.py

# Copyright (C) 2003 by Intevation GmbH
# Authors:
# Bernhard Herzog <[email protected]>
#
# This program is free software under the GPL (>=v2)
# Read the file COPYING coming with the software for details.

"""Database for transient data

This database is intended for data representations needed during the
lifetime of a Thuban session but which is not permanent. Examples of
this are for instance a join of two DBF files where the DBF files are
the permanent representation of the data and the join only exists in the
Thuban session and is reconstructed when the session is opened.
"""

__version__ = "$Revision$"
# $Source$
# $Id$

import os
import weakref
from sqlite import connect

import table

sql_type_map = {
    table.FIELDTYPE_INT: "INTEGER",
    table.FIELDTYPE_STRING: "VARCHAR",
    table.FIELDTYPE_DOUBLE: "FLOAT",
    }

type_converter_map = {
    table.FIELDTYPE_INT: int,
    table.FIELDTYPE_STRING: str,
    table.FIELDTYPE_DOUBLE: float,
    }

class TransientDatabase:

    def __init__(self, filename):
        self.filename = filename
        self.conn = connect(filename)
        # Counters to produce unique table and column names
        self.num_tables = 0
        self.num_cols = 0
        # Since there's only once process using the SQLite database, we
        # might be able to get a tad more speed with default_synchronous
        # OFF. So far I haven't seen any measurable speedup, though.
        #self.execute("PRAGMA default_synchronous = OFF")

    def __del__(self):
        self.close()

    def close(self):
        if self.conn:
            self.conn.close()

    def new_table_name(self):
        self.num_tables += 1
        return "Table%03d" % self.num_tables

    def new_column_name(self):
        self.num_cols += 1
        return "Col%03d" % self.num_cols

    def execute(self, *args):
        """execute the SQL statement in the database and return the result"""
        cursor = self.conn.cursor()
        cursor.execute(*args)
        result = cursor.fetchone()
        self.conn.commit()
        return result

    def cursor(self):
        return self.conn.cursor()


class ColumnReference:

    def __init__(self, name, type, internal_name):
        self.name = name
        self.type = type
        self.internal_name = internal_name


class TransientTableBase:

    """Base class for tables in the transient database"""

    def __init__(self, transient_db):
        """Initialize the table for use with the given transient db"""
        self.db = transient_db
        self.tablename = self.db.new_table_name()
        self.indexed_columns = {}
        self.read_record_cursor = None
        self.read_record_last_row = None

    def create(self, columns):
        self.columns = columns
        self.name_to_column = {}
        self.orig_names = []
        self.internal_to_orig = {}
        self.orig_to_internal = {}

        # Create the column objects and fill various maps and lists
        for col in self.columns:
            self.name_to_column[col.name] = col
            self.orig_names.append(col.name)
            self.internal_to_orig[col.internal_name] = col.name
            self.orig_to_internal[col.name] = col.internal_name

        # Build the CREATE TABLE statement and create the table in the
        # database
        table_types = []
        for col in self.columns:
            table_types.append("%s %s" % (col.internal_name,
                                          sql_type_map[col.type]))
        table_stmt = "CREATE TABLE %s (\n    %s\n);" % (self.tablename,
                                                   ",\n    ".join(table_types))
        self.db.execute(table_stmt)

    def transient_table(self):
        """
        Return a table whose underlying implementation is in the transient db
        """
        return self

    def ensure_index(self, column):
        """Ensure that there's an index on the given column"""
        if not column in self.indexed_columns:
            internal_name = self.orig_to_internal[column]
            indexname = "Index_%s_%s" % (self.tablename, internal_name)
            stmt = "CREATE INDEX %s ON %s (%s);" % (indexname, self.tablename,
                                                    internal_name)
            self.db.execute(stmt)
            self.indexed_columns[column] = 1

    def field_count(self):
        return len(self.columns)

    def field_info(self, i):
        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):
        result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)
        return int(result[0])

    def read_record(self, index):
        if self.read_record_cursor is None or index <self.read_record_last_row:
            stmt = ("SELECT %s FROM %s;"
                    % (", ".join([c.internal_name for c in self.columns]),
                       self.tablename))
            self.read_record_cursor = self.db.cursor()
            self.read_record_cursor.execute(stmt)
            self.read_record_last_row = -1
        for i in range(index - self.read_record_last_row):
            result = self.read_record_cursor.fetchone()
        self.read_record_last_row = index
        result = dict(zip(self.orig_names, result))
        return result

    def field_range(self, colname):
        col = self.name_to_column[colname]
        iname = col.internal_name
        min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"
                                   % (iname, iname, self.tablename))
        converter = type_converter_map[col.type]
        return ((converter(min), None), (converter(max), None))

    def GetUniqueValues(self, colname):
        iname = self.orig_to_internal[colname]
        cursor = self.db.cursor()
        cursor.execute("SELECT %s FROM %s GROUP BY %s;"
                       % (iname, self.tablename, iname))
        result = []
        while 1:
            row = cursor.fetchone()
            if row is None:
                break
            result.append(row[0])
        return result


class TransientTable(TransientTableBase):

    """A Table in a transient DB that starts as the copy of a Thuban Table."""

    def __init__(self, transient_db, table):
        """Create a new table in the given transient DB as a copy of table

        The table argument can be any object implementing the Table
        interface.
        """
        TransientTableBase.__init__(self, transient_db)
        self.create(table)

    def create(self, table):
        columns = []
        for i in range(table.field_count()):
            type, name = table.field_info(i)[:2]
            columns.append(ColumnReference(name, type,
                                           self.db.new_column_name()))
        TransientTableBase.create(self, columns)

        # copy the input table to the transient db
        insert_template = "INSERT INTO %s (%s) VALUES (%s);" \
                               % (self.tablename,
                                  ", ".join([col.internal_name
                                             for col in self.columns]),
                                  ", ".join(["%%(%s)s" % col.name
                                             for col in self.columns]))
        cursor = self.db.cursor()
        for i in range(table.record_count()):
            cursor.execute(insert_template, table.read_record(i))
        self.db.conn.commit()


class TransientJoinedTable(TransientTableBase):

    """A Table in the transient DB that contains a join of two tables"""

    def __init__(self, transient_db, left_table, left_field,
                 right_table, right_field = None):
        """Create a new table in the transient DB as a join of two tables.

        Both input tables, left_table and right_table must have a
        transient_table method that returns a table object for a table
        in the trnsient database. The join is performed on the condition
        that the value of the left_field column the the left table is
        equal to the value of the right_field in the right_table.

        The joined table contains all columns of the input tables with
        one exception: Any column in the right_table with the same name
        as one of the columns in the left_table will be omitted. This is
        somewhat of an implementation detail, but is done so that the
        column names of the joined table can be the same as the column
        names of the input tables without having to create prefixes.
        """
        TransientTableBase.__init__(self, transient_db)
        self.left_table = left_table.transient_table()
        self.left_field = left_field
        self.right_table = right_table.transient_table()
        if right_field:
            self.right_field = right_field
        else:
            self.right_field = self.left_field
        self.create()

    def create(self):
        """Internal: Create the table with the joined data"""
        self.tablename = self.db.new_table_name()

        self.right_table.ensure_index(self.right_field)

        # Coalesce the column information
        visited = {}
        columns = []
        for col in self.left_table.columns + self.right_table.columns:
            if col.name in visited:
                continue
            columns.append(col)
        TransientTableBase.create(self, columns)

        # Copy the joined data to the table.
        internal_names = [col.internal_name for col in self.columns]
        stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \
               % (self.tablename,
                  ", ".join(internal_names),
                  ", ".join(internal_names),
                  self.left_table.tablename,
                  self.right_table.tablename,
                  self.orig_to_internal[self.left_field],
                  self.orig_to_internal[self.right_field])
        self.db.execute(stmt)


class AutoTransientTable:

    """Table that copies data to a transient table on demand.

    The AutoTransientTable takes another table as input and copies data
    to a table in a TransientDatabase instance on demand.
    """

    def __init__(self, transient_db, table):
        self.transient_db = transient_db
        self.table = table
        self.t_table = None

    def record_count(self):
        """Return the number of records"""
        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

    def read_record(self, record):
        """Return the record no. record as a dict mapping field names to values
        """
        if self.t_table is not None:
            return self.t_table.read_record(record)
        else:
            return self.table.read_record(record)

    def write_record(self, record, values):
        raise NotImplementedError

    def copy_to_transient(self):
        """Internal: Create a transient table and copy the data into it"""
        self.t_table = TransientTable(self.transient_db, self)

    def transient_table(self):
        """
        Return a table whose underlying implementation is in the transient db
        """
        if self.t_table is None:
            self.copy_to_transient()
        return self.t_table

    def field_range(self, colname):
        if self.t_table is None:
            self.copy_to_transient()
        return self.t_table.field_range(colname)

    def GetUniqueValues(self, colname):
        if self.t_table is None:
            self.copy_to_transient()
        return self.t_table.GetUniqueValues(colname)
1	bh	765	# Copyright (C) 2003 by Intevation GmbH
2			# Authors:
3			# Bernhard Herzog <[email protected]>
4			#
5			# This program is free software under the GPL (>=v2)
6			# Read the file COPYING coming with the software for details.
7
8			"""Database for transient data
9
10			This database is intended for data representations needed during the
11			lifetime of a Thuban session but which is not permanent. Examples of
12			this are for instance a join of two DBF files where the DBF files are
13			the permanent representation of the data and the join only exists in the
14			Thuban session and is reconstructed when the session is opened.
15			"""
16
17			__version__ = "$Revision$"
18			# $Source$
19			# $Id$
20
21			import os
22			import weakref
23			from sqlite import connect
24
25			import table
26
27			sql_type_map = {
28			table.FIELDTYPE_INT: "INTEGER",
29			table.FIELDTYPE_STRING: "VARCHAR",
30			table.FIELDTYPE_DOUBLE: "FLOAT",
31			}
32
33			type_converter_map = {
34			table.FIELDTYPE_INT: int,
35			table.FIELDTYPE_STRING: str,
36			table.FIELDTYPE_DOUBLE: float,
37			}
38
39			class TransientDatabase:
40
41			def __init__(self, filename):
42			self.filename = filename
43			self.conn = connect(filename)
44			# Counters to produce unique table and column names
45			self.num_tables = 0
46			self.num_cols = 0
47			# Since there's only once process using the SQLite database, we
48			# might be able to get a tad more speed with default_synchronous
49			# OFF. So far I haven't seen any measurable speedup, though.
50			#self.execute("PRAGMA default_synchronous = OFF")
51
52			def __del__(self):
53			self.close()
54
55			def close(self):
56			if self.conn:
57			self.conn.close()
58
59			def new_table_name(self):
60			self.num_tables += 1
61			return "Table%03d" % self.num_tables
62
63			def new_column_name(self):
64			self.num_cols += 1
65			return "Col%03d" % self.num_cols
66
67			def execute(self, *args):
68			"""execute the SQL statement in the database and return the result"""
69			cursor = self.conn.cursor()
70			cursor.execute(*args)
71			result = cursor.fetchone()
72			self.conn.commit()
73			return result
74
75			def cursor(self):
76			return self.conn.cursor()
77
78
79			class ColumnReference:
80
81			def __init__(self, name, type, internal_name):
82			self.name = name
83			self.type = type
84			self.internal_name = internal_name
85
86
87			class TransientTableBase:
88
89			"""Base class for tables in the transient database"""
90
91			def __init__(self, transient_db):
92			"""Initialize the table for use with the given transient db"""
93			self.db = transient_db
94			self.tablename = self.db.new_table_name()
95			self.indexed_columns = {}
96			self.read_record_cursor = None
97			self.read_record_last_row = None
98
99			def create(self, columns):
100			self.columns = columns
101			self.name_to_column = {}
102			self.orig_names = []
103			self.internal_to_orig = {}
104			self.orig_to_internal = {}
105
106			# Create the column objects and fill various maps and lists
107			for col in self.columns:
108			self.name_to_column[col.name] = col
109			self.orig_names.append(col.name)
110			self.internal_to_orig[col.internal_name] = col.name
111			self.orig_to_internal[col.name] = col.internal_name
112
113			# Build the CREATE TABLE statement and create the table in the
114			# database
115			table_types = []
116			for col in self.columns:
117			table_types.append("%s %s" % (col.internal_name,
118			sql_type_map[col.type]))
119			table_stmt = "CREATE TABLE %s (\n %s\n);" % (self.tablename,
120			",\n ".join(table_types))
121			self.db.execute(table_stmt)
122
123			def transient_table(self):
124			"""
125			Return a table whose underlying implementation is in the transient db
126			"""
127			return self
128
129			def ensure_index(self, column):
130			"""Ensure that there's an index on the given column"""
131			if not column in self.indexed_columns:
132			internal_name = self.orig_to_internal[column]
133			indexname = "Index_%s_%s" % (self.tablename, internal_name)
134			stmt = "CREATE INDEX %s ON %s (%s);" % (indexname, self.tablename,
135			internal_name)
136			self.db.execute(stmt)
137			self.indexed_columns[column] = 1
138
139			def field_count(self):
140			return len(self.columns)
141
142			def field_info(self, i):
143			col = self.columns[i]
144			return col.type, col.name, 0, 0
145
146			def field_info_by_name(self, name):
147			for col in self.columns:
148			if col.name == name:
149			return col.type, col.name, 0, 0
150			else:
151			return None
152
153			def record_count(self):
154			result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)
155			return int(result[0])
156
157			def read_record(self, index):
158			if self.read_record_cursor is None or index <self.read_record_last_row:
159			stmt = ("SELECT %s FROM %s;"
160			% (", ".join([c.internal_name for c in self.columns]),
161			self.tablename))
162			self.read_record_cursor = self.db.cursor()
163			self.read_record_cursor.execute(stmt)
164			self.read_record_last_row = -1
165			for i in range(index - self.read_record_last_row):
166			result = self.read_record_cursor.fetchone()
167			self.read_record_last_row = index
168			result = dict(zip(self.orig_names, result))
169			return result
170
171			def field_range(self, colname):
172			col = self.name_to_column[colname]
173			iname = col.internal_name
174			min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"
175			% (iname, iname, self.tablename))
176			converter = type_converter_map[col.type]
177			return ((converter(min), None), (converter(max), None))
178
179			def GetUniqueValues(self, colname):
180			iname = self.orig_to_internal[colname]
181			cursor = self.db.cursor()
182			cursor.execute("SELECT %s FROM %s GROUP BY %s;"
183			% (iname, self.tablename, iname))
184			result = []
185			while 1:
186			row = cursor.fetchone()
187			if row is None:
188			break
189			result.append(row[0])
190			return result
191
192
193			class TransientTable(TransientTableBase):
194
195			"""A Table in a transient DB that starts as the copy of a Thuban Table."""
196
197			def __init__(self, transient_db, table):
198			"""Create a new table in the given transient DB as a copy of table
199
200			The table argument can be any object implementing the Table
201			interface.
202			"""
203			TransientTableBase.__init__(self, transient_db)
204			self.create(table)
205
206			def create(self, table):
207			columns = []
208			for i in range(table.field_count()):
209			type, name = table.field_info(i)[:2]
210			columns.append(ColumnReference(name, type,
211			self.db.new_column_name()))
212			TransientTableBase.create(self, columns)
213
214			# copy the input table to the transient db
215			insert_template = "INSERT INTO %s (%s) VALUES (%s);" \
216			% (self.tablename,
217			", ".join([col.internal_name
218			for col in self.columns]),
219			", ".join(["%%(%s)s" % col.name
220			for col in self.columns]))
221			cursor = self.db.cursor()
222			for i in range(table.record_count()):
223			cursor.execute(insert_template, table.read_record(i))
224			self.db.conn.commit()
225
226
227
228			class TransientJoinedTable(TransientTableBase):
229
230			"""A Table in the transient DB that contains a join of two tables"""
231
232			def __init__(self, transient_db, left_table, left_field,
233			right_table, right_field = None):
234			"""Create a new table in the transient DB as a join of two tables.
235
236			Both input tables, left_table and right_table must have a
237			transient_table method that returns a table object for a table
238			in the trnsient database. The join is performed on the condition
239			that the value of the left_field column the the left table is
240			equal to the value of the right_field in the right_table.
241
242			The joined table contains all columns of the input tables with
243			one exception: Any column in the right_table with the same name
244			as one of the columns in the left_table will be omitted. This is
245			somewhat of an implementation detail, but is done so that the
246			column names of the joined table can be the same as the column
247			names of the input tables without having to create prefixes.
248			"""
249			TransientTableBase.__init__(self, transient_db)
250			self.left_table = left_table.transient_table()
251			self.left_field = left_field
252			self.right_table = right_table.transient_table()
253			if right_field:
254			self.right_field = right_field
255			else:
256			self.right_field = self.left_field
257			self.create()
258
259			def create(self):
260			"""Internal: Create the table with the joined data"""
261			self.tablename = self.db.new_table_name()
262
263			self.right_table.ensure_index(self.right_field)
264
265			# Coalesce the column information
266			visited = {}
267			columns = []
268			for col in self.left_table.columns + self.right_table.columns:
269			if col.name in visited:
270			continue
271			columns.append(col)
272			TransientTableBase.create(self, columns)
273
274			# Copy the joined data to the table.
275			internal_names = [col.internal_name for col in self.columns]
276			stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \
277			% (self.tablename,
278			", ".join(internal_names),
279			", ".join(internal_names),
280			self.left_table.tablename,
281			self.right_table.tablename,
282			self.orig_to_internal[self.left_field],
283			self.orig_to_internal[self.right_field])
284			self.db.execute(stmt)
285
286
287			class AutoTransientTable:
288
289			"""Table that copies data to a transient table on demand.
290
291			The AutoTransientTable takes another table as input and copies data
292			to a table in a TransientDatabase instance on demand.
293			"""
294
295			def __init__(self, transient_db, table):
296			self.transient_db = transient_db
297			self.table = table
298			self.t_table = None
299
300			def record_count(self):
301			"""Return the number of records"""
302			return self.table.record_count()
303
304			def field_count(self):
305			"""Return the number of fields in a record"""
306			return self.table.field_count()
307
308			def field_info(self, field):
309			"""Return a tuple (type, name, width, prec) for the field no. field
310
311			type is the data type of the field, name the name, width the
312			field width in characters and prec the decimal precision.
313			"""
314			info = self.table.field_info(field)
315			if info:
316			info = info[:2] + (0, 0)
317			return info
318
319			def field_info_by_name(self, fieldName):
320			info = self.table.field_info_by_name(fieldName)
321			if info:
322			info = info[:2] + (0, 0)
323			return info
324
325			def read_record(self, record):
326			"""Return the record no. record as a dict mapping field names to values
327			"""
328			if self.t_table is not None:
329			return self.t_table.read_record(record)
330			else:
331			return self.table.read_record(record)
332
333			def write_record(self, record, values):
334			raise NotImplementedError
335
336			def copy_to_transient(self):
337			"""Internal: Create a transient table and copy the data into it"""
338			self.t_table = TransientTable(self.transient_db, self)
339
340			def transient_table(self):
341			"""
342			Return a table whose underlying implementation is in the transient db
343			"""
344			if self.t_table is None:
345			self.copy_to_transient()
346			return self.t_table
347
348			def field_range(self, colname):
349			if self.t_table is None:
350			self.copy_to_transient()
351			return self.t_table.field_range(colname)
352
353			def GetUniqueValues(self, colname):
354			if self.t_table is None:
355			self.copy_to_transient()
356			return self.t_table.GetUniqueValues(colname)
Name	Value
svn:eol-style	native
svn:keywords	Author Date Id Revision