On Wed, 14 Aug 2013 10:03:53 +0200, Thomas Krueger <tom.krue...@gmail.com> wrote:
>Hi All, > >I noticed a difference in the output that pragma table_info gives for >tables and views. It seems, that not null conditions aren't properly >returned for views: > >create table atab ( id int not null primary key, withnulls text, >withoutnulls text NOT NULL ); >create view aview as select * from atab; >pragma table_info(atab); >pragma table_info(aview); > >Please notice that table_info(aview) returns always 0 in the not_null >column, whereas the same column is properly reported for pragma >table_info(atab). > >Is this known and intentional? Is there plans to correct that behavior? I >have to deal with any database schema, wanted to use table_info as a means >to extract the column definitions of views. NOT NULL is a check constraint, which is only effective for INSERT or UPDATE operations. A view is a SELECT query and it is not inserted into or updated ever, except when an INSTEAD OF trigger is created, in which case any modification triggers the check constraints of the underlying table[s]. So, to me, not returning values for the notnull properties appears to be proper behaviour (but I understand your wish). For a generic tool, it probably is not overly difficult to parse the column list and source tables of a simple view, and refer back to the appropriate table_info() results to derive the NOT NULL constraint. >I understand that reporting the pk-ness of a column is likely to be >intentionally not correct for views. Indeed. >Thomas -- Groet, Cordialement, Pozdrawiam, Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
