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.
