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 is not None:
            self.conn.close()
            self.conn = None

    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	bh	777	if self.conn is not None:
57	bh	765	self.conn.close()
58	bh	777	self.conn = None
59	bh	765
60			def new_table_name(self):
61			self.num_tables += 1
62			return "Table%03d" % self.num_tables
63
64			def new_column_name(self):
65			self.num_cols += 1
66			return "Col%03d" % self.num_cols
67
68			def execute(self, *args):
69			"""execute the SQL statement in the database and return the result"""
70			cursor = self.conn.cursor()
71			cursor.execute(*args)
72			result = cursor.fetchone()
73			self.conn.commit()
74			return result
75
76			def cursor(self):
77			return self.conn.cursor()
78
79
80			class ColumnReference:
81
82			def __init__(self, name, type, internal_name):
83			self.name = name
84			self.type = type
85			self.internal_name = internal_name
86
87
88			class TransientTableBase:
89
90			"""Base class for tables in the transient database"""
91
92			def __init__(self, transient_db):
93			"""Initialize the table for use with the given transient db"""
94			self.db = transient_db
95			self.tablename = self.db.new_table_name()
96			self.indexed_columns = {}
97			self.read_record_cursor = None
98			self.read_record_last_row = None
99
100			def create(self, columns):
101			self.columns = columns
102			self.name_to_column = {}
103			self.orig_names = []
104			self.internal_to_orig = {}
105			self.orig_to_internal = {}
106
107			# Create the column objects and fill various maps and lists
108			for col in self.columns:
109			self.name_to_column[col.name] = col
110			self.orig_names.append(col.name)
111			self.internal_to_orig[col.internal_name] = col.name
112			self.orig_to_internal[col.name] = col.internal_name
113
114			# Build the CREATE TABLE statement and create the table in the
115			# database
116			table_types = []
117			for col in self.columns:
118			table_types.append("%s %s" % (col.internal_name,
119			sql_type_map[col.type]))
120			table_stmt = "CREATE TABLE %s (\n %s\n);" % (self.tablename,
121			",\n ".join(table_types))
122			self.db.execute(table_stmt)
123
124			def transient_table(self):
125			"""
126			Return a table whose underlying implementation is in the transient db
127			"""
128			return self
129
130			def ensure_index(self, column):
131			"""Ensure that there's an index on the given column"""
132			if not column in self.indexed_columns:
133			internal_name = self.orig_to_internal[column]
134			indexname = "Index_%s_%s" % (self.tablename, internal_name)
135			stmt = "CREATE INDEX %s ON %s (%s);" % (indexname, self.tablename,
136			internal_name)
137			self.db.execute(stmt)
138			self.indexed_columns[column] = 1
139
140			def field_count(self):
141			return len(self.columns)
142
143			def field_info(self, i):
144			col = self.columns[i]
145			return col.type, col.name, 0, 0
146
147			def field_info_by_name(self, name):
148			for col in self.columns:
149			if col.name == name:
150			return col.type, col.name, 0, 0
151			else:
152			return None
153
154			def record_count(self):
155			result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)
156			return int(result[0])
157
158			def read_record(self, index):
159			if self.read_record_cursor is None or index <self.read_record_last_row:
160			stmt = ("SELECT %s FROM %s;"
161			% (", ".join([c.internal_name for c in self.columns]),
162			self.tablename))
163			self.read_record_cursor = self.db.cursor()
164			self.read_record_cursor.execute(stmt)
165			self.read_record_last_row = -1
166			for i in range(index - self.read_record_last_row):
167			result = self.read_record_cursor.fetchone()
168			self.read_record_last_row = index
169			result = dict(zip(self.orig_names, result))
170			return result
171
172			def field_range(self, colname):
173			col = self.name_to_column[colname]
174			iname = col.internal_name
175			min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"
176			% (iname, iname, self.tablename))
177			converter = type_converter_map[col.type]
178			return ((converter(min), None), (converter(max), None))
179
180			def GetUniqueValues(self, colname):
181			iname = self.orig_to_internal[colname]
182			cursor = self.db.cursor()
183			cursor.execute("SELECT %s FROM %s GROUP BY %s;"
184			% (iname, self.tablename, iname))
185			result = []
186			while 1:
187			row = cursor.fetchone()
188			if row is None:
189			break
190			result.append(row[0])
191			return result
192
193
194			class TransientTable(TransientTableBase):
195
196			"""A Table in a transient DB that starts as the copy of a Thuban Table."""
197
198			def __init__(self, transient_db, table):
199			"""Create a new table in the given transient DB as a copy of table
200
201			The table argument can be any object implementing the Table
202			interface.
203			"""
204			TransientTableBase.__init__(self, transient_db)
205			self.create(table)
206
207			def create(self, table):
208			columns = []
209			for i in range(table.field_count()):
210			type, name = table.field_info(i)[:2]
211			columns.append(ColumnReference(name, type,
212			self.db.new_column_name()))
213			TransientTableBase.create(self, columns)
214
215			# copy the input table to the transient db
216			insert_template = "INSERT INTO %s (%s) VALUES (%s);" \
217			% (self.tablename,
218			", ".join([col.internal_name
219			for col in self.columns]),
220			", ".join(["%%(%s)s" % col.name
221			for col in self.columns]))
222			cursor = self.db.cursor()
223			for i in range(table.record_count()):
224			cursor.execute(insert_template, table.read_record(i))
225			self.db.conn.commit()
226
227
228
229			class TransientJoinedTable(TransientTableBase):
230
231			"""A Table in the transient DB that contains a join of two tables"""
232
233			def __init__(self, transient_db, left_table, left_field,
234			right_table, right_field = None):
235			"""Create a new table in the transient DB as a join of two tables.
236
237			Both input tables, left_table and right_table must have a
238			transient_table method that returns a table object for a table
239			in the trnsient database. The join is performed on the condition
240			that the value of the left_field column the the left table is
241			equal to the value of the right_field in the right_table.
242
243			The joined table contains all columns of the input tables with
244			one exception: Any column in the right_table with the same name
245			as one of the columns in the left_table will be omitted. This is
246			somewhat of an implementation detail, but is done so that the
247			column names of the joined table can be the same as the column
248			names of the input tables without having to create prefixes.
249			"""
250			TransientTableBase.__init__(self, transient_db)
251			self.left_table = left_table.transient_table()
252			self.left_field = left_field
253			self.right_table = right_table.transient_table()
254			if right_field:
255			self.right_field = right_field
256			else:
257			self.right_field = self.left_field
258			self.create()
259
260			def create(self):
261			"""Internal: Create the table with the joined data"""
262			self.tablename = self.db.new_table_name()
263
264			self.right_table.ensure_index(self.right_field)
265
266			# Coalesce the column information
267			visited = {}
268			columns = []
269			for col in self.left_table.columns + self.right_table.columns:
270			if col.name in visited:
271			continue
272			columns.append(col)
273			TransientTableBase.create(self, columns)
274
275			# Copy the joined data to the table.
276			internal_names = [col.internal_name for col in self.columns]
277			stmt = "INSERT INTO %s (%s) SELECT %s FROM %s JOIN %s ON %s = %s;" \
278			% (self.tablename,
279			", ".join(internal_names),
280			", ".join(internal_names),
281			self.left_table.tablename,
282			self.right_table.tablename,
283			self.orig_to_internal[self.left_field],
284			self.orig_to_internal[self.right_field])
285			self.db.execute(stmt)
286
287
288			class AutoTransientTable:
289
290			"""Table that copies data to a transient table on demand.
291
292			The AutoTransientTable takes another table as input and copies data
293			to a table in a TransientDatabase instance on demand.
294			"""
295
296			def __init__(self, transient_db, table):
297			self.transient_db = transient_db
298			self.table = table
299			self.t_table = None
300
301			def record_count(self):
302			"""Return the number of records"""
303			return self.table.record_count()
304
305			def field_count(self):
306			"""Return the number of fields in a record"""
307			return self.table.field_count()
308
309			def field_info(self, field):
310			"""Return a tuple (type, name, width, prec) for the field no. field
311
312			type is the data type of the field, name the name, width the
313			field width in characters and prec the decimal precision.
314			"""
315			info = self.table.field_info(field)
316			if info:
317			info = info[:2] + (0, 0)
318			return info
319
320			def field_info_by_name(self, fieldName):
321			info = self.table.field_info_by_name(fieldName)
322			if info:
323			info = info[:2] + (0, 0)
324			return info
325
326			def read_record(self, record):
327			"""Return the record no. record as a dict mapping field names to values
328			"""
329			if self.t_table is not None:
330			return self.t_table.read_record(record)
331			else:
332			return self.table.read_record(record)
333
334			def write_record(self, record, values):
335			raise NotImplementedError
336
337			def copy_to_transient(self):
338			"""Internal: Create a transient table and copy the data into it"""
339			self.t_table = TransientTable(self.transient_db, self)
340
341			def transient_table(self):
342			"""
343			Return a table whose underlying implementation is in the transient db
344			"""
345			if self.t_table is None:
346			self.copy_to_transient()
347			return self.t_table
348
349			def field_range(self, colname):
350			if self.t_table is None:
351			self.copy_to_transient()
352			return self.t_table.field_range(colname)
353
354			def GetUniqueValues(self, colname):
355			if self.t_table is None:
356			self.copy_to_transient()
357			return self.t_table.GetUniqueValues(colname)
Name	Value
svn:eol-style	native
svn:keywords	Author Date Id Revision