Mike Ashmore wrote:
Hi folks,
I'm trying to create a composite view from multiple database files,
with an extra field for the origin of a particular record. A sample
scenario:
There's a table, "foo," which exists in two database files, 'a.db3'
and 'b.db3'. Let's define it as:
CREATE TABLE foo (f1 integer, f2 string);
Now, we open up a :memory: database and do the following:
ATTACH 'a.db3' as a;
ATTACH 'b.db3' as b;
CREATE TEMP VIEW foo AS
SELECT *, 'a' AS origin FROM a.foo
UNION
SELECT *, 'b' AS origin FROM b.foo;
PRAGMA table_info(foo);
gives:
0|f1|numeric|0||0
1|f2|string|0||0
2|origin|numeric|0||0
The problem is that I'd like origin to be reported as type string. Is
there something in SQL syntax that I've missed which allows me to
specify this?
I'm trying to integrate the composite view into a Ruby on Rails
application, and RoR seems to rely on the type reported by the
table_info pragma to determine what format to use when updating or
inserting records [1][2].
I've determined this happens with SQLite 3.2.8 and below; I have not
yet tested against the 3.3.x series.
Thanks in advance for any help you can provide with this,
-Mike Ashmore
[1] Of course one can't insert, update, or delete on a view directly;
I have a set of INSTEAD OF triggers which ask a separate process to
modify the tables in their original database files.
[2] If there are any RoR users here who know how to override this
behavior cleanly (manually specifying column types), I'd also love to
know about that mechanism.
Mike,
What the table_info pragma shows you is the type affinity of the column
in the view. SQLite can store any type of value in any table column
because it is not strongly typed. What happens with your view is that
the text constants ('a' and 'b') are stored as text in the column origin
even though it has numeric affinity. You can check this by doing a
simple query on your view:
select typeof(origin) from foo;
All the origin values should return type 'text'.
RoR should be using the sqlite3_column_type() API to determine the type
of the values returned from a query. Other APIs like
sqlite3_column_decltype() and pragma table_info are returning other
information, not the type of the result value.
HTH
Dennis Cote
