Re: [sqlite] PRAGMA table_info oddness

[Mike Ashmore]

Well, it's not the prettiest thing in the world, but it definitely  
works. Brilliant!


Many thanks for your help,
-Mike Ashmore

On Jan 23, 2006, at 1:01 PM, Kurt Welgehausen wrote:


Sorry, I didn't read your code carefully enough the first time.
Your approach won't work because origin has no explicit type,
and SQLite will not infer a type from the literal string; the
unknown type defaults to numeric.

As Dennis said, the problem is really with RoR, but you can
work around it by creating two 1-row, 1-column tables. Here's
the idea; this is more or less the A part of your view, so
you'll have to add the union and the B part.

sqlite> pragma table_info(t1);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   a   integer 0   <>  1
1   c   char0   <>  0
sqlite> create temp table originA (a char);
sqlite> insert into originA values ('a');
sqlite> create temp view va as select t1.*, originA.a origin from  
t1, originA;

sqlite> pragma table_info(va);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   a   integer 0   <>  0
1   c   char0   <>  0
2   origin  char0   <>  0


Regards

Re: [sqlite] PRAGMA table_info oddness

[Kurt Welgehausen]

Sorry, I didn't read your code carefully enough the first time.
Your approach won't work because origin has no explicit type,
and SQLite will not infer a type from the literal string; the
unknown type defaults to numeric.

As Dennis said, the problem is really with RoR, but you can
work around it by creating two 1-row, 1-column tables. Here's
the idea; this is more or less the A part of your view, so
you'll have to add the union and the B part.

sqlite> pragma table_info(t1);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   a   integer 0   <>  1 
1   c   char0   <>  0 
sqlite> create temp table originA (a char);
sqlite> insert into originA values ('a');
sqlite> create temp view va as select t1.*, originA.a origin from t1, originA;
sqlite> pragma table_info(va);
cid nametypenotnull dflt_value  pk
--  --  --  --  --  --
0   a   integer 0   <>  0 
1   c   char0   <>  0 
2   origin  char0   <>  0 


Regards

Re: [sqlite] PRAGMA table_info oddness

[Mike Ashmore]

On Jan 22, 2006, at 7:43 PM, Kurt Welgehausen wrote:


There's no string type in SQL. Go to

and read section 2.1.

Regards


Okay, my previous message related to SQL syntax, so I suppose it's  
fair to point out that "string" isn't a SQL type. In fact, I should  
have used the term 'text' most places where the word 'string'  
appeared. But we're missing the point.


What I was trying to get at was, when I create a view based on the  
query "SELECT *, 'a' as origin from a.foo", the origin column in the  
resulting view has (according to PRAGMA table_info(foo)) a 'NUMERIC'  
affinity.


What I'd like is for table_info to report the origin column's  
affinity as TEXT.


So, now that I've got my semantics sorted, does anybody have any  
suggestions for how to make that happen?


Thanks,
-Mike Ashmore

Re: [sqlite] PRAGMA table_info oddness

[Dennis Cote]

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

Re: [sqlite] PRAGMA table_info oddness

[Kurt Welgehausen]

There's no string type in SQL. Go to

and read section 2.1.

Regards

[sqlite] PRAGMA table_info oddness

[Mike Ashmore]

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.