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;
    SELECT *, 'a' AS origin FROM a.foo
    SELECT *, 'b' AS origin FROM b.foo;

PRAGMA table_info(foo);

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.


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.

Dennis Cote

Reply via email to