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(table.OldTableInterfaceMixin):

    """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
        self.read_record_last_result = None

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

        # Create the column objects and fill various maps and lists
        for index in range(len(self.columns)):
            col = self.columns[index]
            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
            self.column_map[col.name] = col
            self.column_map[index] = col

        # Build the CREATE TABLE statement and create the table in the
        # database
        table_types = ["id INTEGER PRIMARY KEY"]
        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 NumColumns(self):
        return len(self.columns)

    def NumRows(self):
        result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)
        return int(result[0])

    def Columns(self):
        return self.columns

    def Column(self, col):
        return self.column_map[col]

    def HasColumn(self, col):
        """Return whether the table has a column with the given name or index
        """
        return self.column_map.has_key(col)

    def ReadRowAsDict(self, index):
        # Implementation Strategy: Executing a completely new select
        # statement every time this method is called is too slow. The
        # most important usage is to read the records more or less
        # sequentially. This happens e.g. when drawing a layer with a
        # classification where the shapes are drawn in order of the
        # shape ids. Another pattern is that the same row is requested
        # several times in a row. This happens in the table view, for
        # instance.

        # We can exploit this to make access faster by having one cursor
        # open all the time and keeping the last row read around in case
        # the same row is accessed again the next time and if the row
        # index is larger than the row we have read last we simply fetch
        # rows from the cursor until we've reached the requested row. If
        # the requested row index is smaller then we start a new cursor.

        # FIXME: So far this scheme seems to work well enough. Obvious
        # improvements would be to start the cursor at exactly the
        # requested row (should be efficient and easy to do now that the
        # id is the primary key) and to perhaps to also start a new
        # cursor if the requested index is much larger than the last row
        # so that we don't read and discard lots of the rows.

        # Check whether we have to start a new cursor
        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
            self.read_record_last_result = None

        # Now we should have a cursor at a position less than or equal
        # to the index so the following if statement will always set
        # result to a suitable value
        assert index >= self.read_record_last_row

        if index == self.read_record_last_row:
            result = self.read_record_last_result
        else:
            for i in range(index - self.read_record_last_row):
                result = self.read_record_cursor.fetchone()
                self.read_record_last_result = result
        self.read_record_last_row = index
        return dict(zip(self.orig_names, result))

    def ReadValue(self, row, col):
        """Return the value of the specified row and column

        The col parameter may be the index of the column or its name.
        """
        # Depending on the actual access patterns of the table data, it
        # might be a bit faster in some circumstances to not implement
        # this via ReadRowAsDict, but this simple implementation should
        # be fast enough for most purposes.
        return self.ReadRowAsDict(row)[self.column_map[col].name]

    def ValueRange(self, col):
        col = self.column_map[col]
        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), converter(max))

    def UniqueValues(self, col):
        iname = self.column_map[col].internal_name
        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

    def SimpleQuery(self, left, comparison, right):
        """Return the indices of all rows that matching a condition.

        Parameters:
           left -- The column object for the left side of the comparison

           comparison -- The comparison operator as a string. It must be
                         one of '==', '!=', '<', '<=', '>=', '>'

           right -- The right hand side of the comparison. It must be
                    either a column object or a value, i.e. a string,
                    int or float.

        The return value is a sorted list of the indices of the rows
        where the condition is true.
        """
        if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
            raise ValueError("Comparison operator %r not allowed" % comparison)

        if hasattr(right, "internal_name"):
            right_template = right.internal_name
            params = ()
        else:
            right_template = "%s"
            params = (right,)

        query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
                % (self.tablename, left.internal_name, comparison,
                   right_template)

        cursor = self.db.cursor()
        cursor.execute(query, params)
        result = []
        while 1:
            row = cursor.fetchone()
            if row is None:
                break
            result.append(row[0])
        return result

    def Dependencies(self):
        """Placeholder for a method in a derived class.

        Return a sequence with the tables and other data objects that
        self depends on.
        """
        raise NotImplementedError


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.title = table.Title()
        self.create(table)

    def Title(self):
        """Return the title of the table.

        The title is the same as that of the original table
        """
        return self.title

    def create(self, table):
        columns = []
        for col in table.Columns():
            columns.append(ColumnReference(col.name, col.type,
                                           self.db.new_column_name()))
        TransientTableBase.create(self, columns)

        # copy the input table to the transient db

        # A key to insert to use for the formatting of the insert
        # statement. The key must not be equal to any of the column
        # names so we construct one by building a string of x's that is
        # longer than any of the column names
        id_key = max([len(col.name) for col in self.columns]) * "x"

        insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
                               % (self.tablename,
                                  ", ".join([col.internal_name
                                             for col in self.columns]),
                                  id_key,
                                  ", ".join(["%%(%s)s" % col.name
                                             for col in self.columns]))
        cursor = self.db.cursor()
        for i in range(table.NumRows()):
            row = table.ReadRowAsDict(i)
            row[id_key] = i
            cursor.execute(insert_template, row)
        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, outer_join = False):
        """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 transient 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.dependencies = (left_table, right_table)
        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.outer_join = outer_join
        self.create()

    def Title(self):
        """Return the title of the joined table"""
        return "Join of %(left)s and %(right)s" \
               % {"left": self.left_table.Title(),
                  "right": self.right_table.Title()}

    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:
                # We can't allow multiple columns with the same original
                # name, so omit this one. FIXME: There should be a
                # better solution.
                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]
        if self.outer_join:
            join_operator = 'LEFT OUTER JOIN'
        else:
            join_operator = 'JOIN'
        stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
                " %s %s ON %s = %s;"
                % (self.tablename,
                   ", ".join(internal_names),
                   self.left_table.tablename,
                   ", ".join(internal_names),
                   self.left_table.tablename,
                   join_operator,
                   self.right_table.tablename,
                   self.orig_to_internal[self.left_field],
                   self.orig_to_internal[self.right_field]))
        self.db.execute(stmt)

    def Dependencies(self):
        """Return a tuple with the two tables the join depends on."""
        return self.dependencies


class AutoTransientTable(table.OldTableInterfaceMixin):

    """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 Title(self):
        """Return the title of the table.

        The title is the same as that of the original table.
        """
        return self.table.Title()

    def Columns(self):
        return self.table.Columns()

    def Column(self, col):
        return self.table.Column(col)

    def HasColumn(self, col):
        """Return whether the table has a column with the given name or index
        """
        return self.table.HasColumn(col)

    def NumRows(self):
        return self.table.NumRows()

    def NumColumns(self):
        return self.table.NumColumns()

    def ReadRowAsDict(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.ReadRowAsDict(record)
        else:
            return self.table.ReadRowAsDict(record)

    def ReadValue(self, row, col):
        """Return the value of the specified row and column

        The col parameter may be the index of the column or its name.
        """
        if self.t_table is not None:
            return self.t_table.ReadValue(row, col)
        else:
            return self.table.ReadValue(row, col)

    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 ValueRange(self, col):
        if self.t_table is None:
            self.copy_to_transient()
        return self.t_table.ValueRange(col)

    def UniqueValues(self, col):
        if self.t_table is None:
            self.copy_to_transient()
        return self.t_table.UniqueValues(col)

    def SimpleQuery(self, left, comparison, right):
        if self.t_table is None:
            self.copy_to_transient()
        # Make sure to use the column object of the transient table. The
        # left argument is always a column object so we can just ask the
        # t_table for the right object.
        if hasattr(right, "name"):
            return self.t_table.SimpleQuery(self.t_table.Column(left.name),
                                            comparison, 
                                            self.t_table.Column(right.name))
        else:
            return self.t_table.SimpleQuery(self.t_table.Column(left.name),
                                            comparison, right)

    def Dependencies(self):
        """Return a tuple containing the original table"""
        return (self.table,)
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	bh	818	class TransientTableBase(table.OldTableInterfaceMixin):
89	bh	765
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	bh	785	self.read_record_last_result = None
100	bh	765
101			def create(self, columns):
102			self.columns = columns
103			self.name_to_column = {}
104			self.orig_names = []
105			self.internal_to_orig = {}
106			self.orig_to_internal = {}
107	bh	818	self.column_map = {}
108	bh	765
109			# Create the column objects and fill various maps and lists
110	bh	818	for index in range(len(self.columns)):
111			col = self.columns[index]
112	bh	765	self.name_to_column[col.name] = col
113			self.orig_names.append(col.name)
114			self.internal_to_orig[col.internal_name] = col.name
115			self.orig_to_internal[col.name] = col.internal_name
116	bh	818	self.column_map[col.name] = col
117			self.column_map[index] = col
118	bh	765
119			# Build the CREATE TABLE statement and create the table in the
120			# database
121	bh	841	table_types = ["id INTEGER PRIMARY KEY"]
122	bh	765	for col in self.columns:
123			table_types.append("%s %s" % (col.internal_name,
124			sql_type_map[col.type]))
125			table_stmt = "CREATE TABLE %s (\n %s\n);" % (self.tablename,
126			",\n ".join(table_types))
127			self.db.execute(table_stmt)
128
129			def transient_table(self):
130			"""
131			Return a table whose underlying implementation is in the transient db
132			"""
133			return self
134
135			def ensure_index(self, column):
136			"""Ensure that there's an index on the given column"""
137			if not column in self.indexed_columns:
138			internal_name = self.orig_to_internal[column]
139			indexname = "Index_%s_%s" % (self.tablename, internal_name)
140			stmt = "CREATE INDEX %s ON %s (%s);" % (indexname, self.tablename,
141			internal_name)
142			self.db.execute(stmt)
143			self.indexed_columns[column] = 1
144
145	bh	818	def NumColumns(self):
146	bh	765	return len(self.columns)
147
148	bh	818	def NumRows(self):
149	bh	765	result = self.db.execute("SELECT count(*) FROM %s;" % self.tablename)
150			return int(result[0])
151
152	bh	818	def Columns(self):
153			return self.columns
154
155			def Column(self, col):
156			return self.column_map[col]
157
158	bh	839	def HasColumn(self, col):
159			"""Return whether the table has a column with the given name or index
160			"""
161			return self.column_map.has_key(col)
162
163	bh	818	def ReadRowAsDict(self, index):
164	bh	849	# Implementation Strategy: Executing a completely new select
165			# statement every time this method is called is too slow. The
166			# most important usage is to read the records more or less
167			# sequentially. This happens e.g. when drawing a layer with a
168			# classification where the shapes are drawn in order of the
169			# shape ids. Another pattern is that the same row is requested
170			# several times in a row. This happens in the table view, for
171			# instance.
172
173			# We can exploit this to make access faster by having one cursor
174			# open all the time and keeping the last row read around in case
175			# the same row is accessed again the next time and if the row
176			# index is larger than the row we have read last we simply fetch
177			# rows from the cursor until we've reached the requested row. If
178			# the requested row index is smaller then we start a new cursor.
179
180			# FIXME: So far this scheme seems to work well enough. Obvious
181			# improvements would be to start the cursor at exactly the
182			# requested row (should be efficient and easy to do now that the
183			# id is the primary key) and to perhaps to also start a new
184			# cursor if the requested index is much larger than the last row
185			# so that we don't read and discard lots of the rows.
186
187			# Check whether we have to start a new cursor
188	bh	765	if self.read_record_cursor is None or index <self.read_record_last_row:
189			stmt = ("SELECT %s FROM %s;"
190			% (", ".join([c.internal_name for c in self.columns]),
191			self.tablename))
192			self.read_record_cursor = self.db.cursor()
193			self.read_record_cursor.execute(stmt)
194			self.read_record_last_row = -1
195	bh	785	self.read_record_last_result = None
196
197			# Now we should have a cursor at a position less than or equal
198			# to the index so the following if statement will always set
199			# result to a suitable value
200			assert index >= self.read_record_last_row
201
202			if index == self.read_record_last_row:
203	bh	818	result = self.read_record_last_result
204	bh	785	else:
205			for i in range(index - self.read_record_last_row):
206			result = self.read_record_cursor.fetchone()
207			self.read_record_last_result = result
208	bh	765	self.read_record_last_row = index
209	bh	785	return dict(zip(self.orig_names, result))
210	bh	765
211	bh	849	def ReadValue(self, row, col):
212			"""Return the value of the specified row and column
213
214			The col parameter may be the index of the column or its name.
215			"""
216			# Depending on the actual access patterns of the table data, it
217			# might be a bit faster in some circumstances to not implement
218			# this via ReadRowAsDict, but this simple implementation should
219			# be fast enough for most purposes.
220			return self.ReadRowAsDict(row)[self.column_map[col].name]
221
222	bh	818	def ValueRange(self, col):
223			col = self.column_map[col]
224	bh	765	iname = col.internal_name
225			min, max = self.db.execute("SELECT min(%s), max(%s) FROM %s;"
226			% (iname, iname, self.tablename))
227			converter = type_converter_map[col.type]
228	bh	818	return (converter(min), converter(max))
229	bh	765
230	bh	818	def UniqueValues(self, col):
231			iname = self.column_map[col].internal_name
232	bh	765	cursor = self.db.cursor()
233			cursor.execute("SELECT %s FROM %s GROUP BY %s;"
234			% (iname, self.tablename, iname))
235			result = []
236			while 1:
237			row = cursor.fetchone()
238			if row is None:
239			break
240			result.append(row[0])
241			return result
242
243	bh	841	def SimpleQuery(self, left, comparison, right):
244			"""Return the indices of all rows that matching a condition.
245	bh	765
246	bh	841	Parameters:
247			left -- The column object for the left side of the comparison
248
249			comparison -- The comparison operator as a string. It must be
250			one of '==', '!=', '<', '<=', '>=', '>'
251
252			right -- The right hand side of the comparison. It must be
253			either a column object or a value, i.e. a string,
254			int or float.
255
256			The return value is a sorted list of the indices of the rows
257			where the condition is true.
258			"""
259			if comparison not in ("==", "!=", "<", "<=", ">=", ">"):
260			raise ValueError("Comparison operator %r not allowed" % comparison)
261
262			if hasattr(right, "internal_name"):
263			right_template = right.internal_name
264			params = ()
265			else:
266			right_template = "%s"
267			params = (right,)
268
269			query = "SELECT id FROM %s WHERE %s %s %s ORDER BY id;" \
270			% (self.tablename, left.internal_name, comparison,
271			right_template)
272
273			cursor = self.db.cursor()
274			cursor.execute(query, params)
275			result = []
276			while 1:
277			row = cursor.fetchone()
278			if row is None:
279			break
280			result.append(row[0])
281			return result
282
283	bh	984	def Dependencies(self):
284			"""Placeholder for a method in a derived class.
285	bh	841
286	bh	984	Return a sequence with the tables and other data objects that
287			self depends on.
288			"""
289			raise NotImplementedError
290
291
292	bh	765	class TransientTable(TransientTableBase):
293
294			"""A Table in a transient DB that starts as the copy of a Thuban Table."""
295
296			def __init__(self, transient_db, table):
297			"""Create a new table in the given transient DB as a copy of table
298
299			The table argument can be any object implementing the Table
300			interface.
301			"""
302			TransientTableBase.__init__(self, transient_db)
303	bh	998	self.title = table.Title()
304	bh	765	self.create(table)
305
306	bh	998	def Title(self):
307			"""Return the title of the table.
308
309			The title is the same as that of the original table
310			"""
311			return self.title
312
313	bh	765	def create(self, table):
314			columns = []
315	bh	818	for col in table.Columns():
316			columns.append(ColumnReference(col.name, col.type,
317	bh	765	self.db.new_column_name()))
318			TransientTableBase.create(self, columns)
319
320			# copy the input table to the transient db
321	bh	841
322			# A key to insert to use for the formatting of the insert
323			# statement. The key must not be equal to any of the column
324			# names so we construct one by building a string of x's that is
325			# longer than any of the column names
326			id_key = max([len(col.name) for col in self.columns]) * "x"
327
328			insert_template = "INSERT INTO %s (id, %s) VALUES (%%(%s)s, %s);" \
329	bh	765	% (self.tablename,
330			", ".join([col.internal_name
331			for col in self.columns]),
332	bh	841	id_key,
333	bh	765	", ".join(["%%(%s)s" % col.name
334			for col in self.columns]))
335			cursor = self.db.cursor()
336	bh	818	for i in range(table.NumRows()):
337	bh	841	row = table.ReadRowAsDict(i)
338			row[id_key] = i
339			cursor.execute(insert_template, row)
340	bh	765	self.db.conn.commit()
341
342
343
344			class TransientJoinedTable(TransientTableBase):
345
346			"""A Table in the transient DB that contains a join of two tables"""
347
348			def __init__(self, transient_db, left_table, left_field,
349	frank	1009	right_table, right_field = None, outer_join = False):
350	bh	765	"""Create a new table in the transient DB as a join of two tables.
351
352			Both input tables, left_table and right_table must have a
353			transient_table method that returns a table object for a table
354	frank	1002	in the transient database. The join is performed on the condition
355	bh	765	that the value of the left_field column the the left table is
356			equal to the value of the right_field in the right_table.
357
358			The joined table contains all columns of the input tables with
359			one exception: Any column in the right_table with the same name
360			as one of the columns in the left_table will be omitted. This is
361			somewhat of an implementation detail, but is done so that the
362			column names of the joined table can be the same as the column
363			names of the input tables without having to create prefixes.
364			"""
365			TransientTableBase.__init__(self, transient_db)
366	bh	984	self.dependencies = (left_table, right_table)
367	bh	765	self.left_table = left_table.transient_table()
368			self.left_field = left_field
369			self.right_table = right_table.transient_table()
370			if right_field:
371			self.right_field = right_field
372			else:
373			self.right_field = self.left_field
374	frank	1009	self.outer_join = outer_join
375	bh	765	self.create()
376
377	bh	998	def Title(self):
378			"""Return the title of the joined table"""
379			return "Join of %(left)s and %(right)s" \
380			% {"left": self.left_table.Title(),
381			"right": self.right_table.Title()}
382
383	bh	765	def create(self):
384			"""Internal: Create the table with the joined data"""
385			self.tablename = self.db.new_table_name()
386
387			self.right_table.ensure_index(self.right_field)
388
389			# Coalesce the column information
390			visited = {}
391			columns = []
392			for col in self.left_table.columns + self.right_table.columns:
393			if col.name in visited:
394	bh	841	# We can't allow multiple columns with the same original
395			# name, so omit this one. FIXME: There should be a
396			# better solution.
397	bh	765	continue
398			columns.append(col)
399			TransientTableBase.create(self, columns)
400
401			# Copy the joined data to the table.
402			internal_names = [col.internal_name for col in self.columns]
403	frank	1009	if self.outer_join:
404			join_operator = 'LEFT OUTER JOIN'
405			else:
406			join_operator = 'JOIN'
407	bh	841	stmt = ("INSERT INTO %s (id, %s) SELECT %s.id, %s FROM %s"
408	frank	1009	" %s %s ON %s = %s;"
409	bh	841	% (self.tablename,
410			", ".join(internal_names),
411			self.left_table.tablename,
412			", ".join(internal_names),
413			self.left_table.tablename,
414	frank	1009	join_operator,
415	bh	841	self.right_table.tablename,
416			self.orig_to_internal[self.left_field],
417			self.orig_to_internal[self.right_field]))
418	bh	765	self.db.execute(stmt)
419
420	bh	984	def Dependencies(self):
421			"""Return a tuple with the two tables the join depends on."""
422			return self.dependencies
423	bh	765
424	bh	984
425	bh	818	class AutoTransientTable(table.OldTableInterfaceMixin):
426	bh	765
427			"""Table that copies data to a transient table on demand.
428
429			The AutoTransientTable takes another table as input and copies data
430			to a table in a TransientDatabase instance on demand.
431			"""
432
433			def __init__(self, transient_db, table):
434			self.transient_db = transient_db
435			self.table = table
436			self.t_table = None
437
438	bh	998	def Title(self):
439			"""Return the title of the table.
440
441			The title is the same as that of the original table.
442			"""
443			return self.table.Title()
444
445	bh	818	def Columns(self):
446			return self.table.Columns()
447	bh	765
448	bh	818	def Column(self, col):
449			return self.table.Column(col)
450	bh	765
451	bh	839	def HasColumn(self, col):
452			"""Return whether the table has a column with the given name or index
453			"""
454			return self.table.HasColumn(col)
455
456	bh	818	def NumRows(self):
457			return self.table.NumRows()
458	bh	765
459	bh	818	def NumColumns(self):
460			return self.table.NumColumns()
461	bh	765
462	bh	818	def ReadRowAsDict(self, record):
463	bh	765	"""Return the record no. record as a dict mapping field names to values
464			"""
465			if self.t_table is not None:
466	bh	839	return self.t_table.ReadRowAsDict(record)
467	bh	765	else:
468	bh	818	return self.table.ReadRowAsDict(record)
469	bh	765
470	bh	849	def ReadValue(self, row, col):
471			"""Return the value of the specified row and column
472
473			The col parameter may be the index of the column or its name.
474			"""
475			if self.t_table is not None:
476			return self.t_table.ReadValue(row, col)
477			else:
478			return self.table.ReadValue(row, col)
479
480	bh	765	def copy_to_transient(self):
481			"""Internal: Create a transient table and copy the data into it"""
482			self.t_table = TransientTable(self.transient_db, self)
483
484			def transient_table(self):
485			"""
486			Return a table whose underlying implementation is in the transient db
487			"""
488			if self.t_table is None:
489			self.copy_to_transient()
490			return self.t_table
491
492	bh	818	def ValueRange(self, col):
493	bh	765	if self.t_table is None:
494			self.copy_to_transient()
495	bh	839	return self.t_table.ValueRange(col)
496	bh	765
497	bh	839	def UniqueValues(self, col):
498	bh	765	if self.t_table is None:
499			self.copy_to_transient()
500	bh	839	return self.t_table.UniqueValues(col)
501	bh	844
502			def SimpleQuery(self, left, comparison, right):
503			if self.t_table is None:
504			self.copy_to_transient()
505			# Make sure to use the column object of the transient table. The
506			# left argument is always a column object so we can just ask the
507			# t_table for the right object.
508	jonathan	933	if hasattr(right, "name"):
509			return self.t_table.SimpleQuery(self.t_table.Column(left.name),
510			comparison,
511			self.t_table.Column(right.name))
512			else:
513			return self.t_table.SimpleQuery(self.t_table.Column(left.name),
514			comparison, right)
515	bh	984
516			def Dependencies(self):
517			"""Return a tuple containing the original table"""
518			return (self.table,)
Name	Value
svn:eol-style	native
svn:keywords	Author Date Id Revision