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	# 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