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