To determine the type of columns in a view I use SELECT typeof(column) FROM viewname LIMIT something;
Unfortunately if most of the column data is NULL then you can end up having to scan the entire table. I'm not sure how SQlite calculates these types, but this simple workaround has been OK for me so far. On my simple views I always see homogenous column types. Ben -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Stefan Keller Sent: 08 May 2010 04:29 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas? Right, I don't want to lessen scalability of my application. But I also don't want to introcude redundancy just because some columns on the the view lack a type. I assume SQLite wants to adhere to the relational model which states: The result of a select statement is another relation. And "A view is just a relation (a table), but stores a definition, rather than a set of tuples." (from chapter fundamentals of "Database Management Systems" by Ramakrishnan & Gehrke, 2002). So, for the "consumer" a view should behave like a table. This has the following advances: * Decoupling: Rename physical column names without breaking code which reads views. * Security: One can grant read permission on a view without granting any permission to the underlying table. * Simplicity: It's easier to write queries. * Helps to avoud redundancy: Views can have calculated columns, like age (from birthdate) or tax or rebate. So its pretty clear to me that views should have the option to return types. CAST could be a solution. Determintation of the return type of a calculation could be another step. That's from the basics of computer languages. -S. 2010/5/7 Pavel Ivanov <paiva...@gmail.com>: >> To Pavel: My application reads the column types out in order to pretty >> print the values - as mentioned by Tom - but also to generate a dialog >> for entering new data (of course combined with INSTEAD OF TRIGGERs). > > So as I see it: you have some universal code for displaying and > inputing data. And you don't want to lessen scalability of your > application by hard-coding the relation between column names and their > data types. So you can create additional table that will contain this > information. And I think this solution is better than just relying on > declared type of columns - more straightforward and more > understandable by somebody coming to your project in the future. > > > Pavel > > On Thu, May 6, 2010 at 7:11 PM, Stefan Keller <sfkel...@gmail.com> wrote: >> Thank you, Tom and Dan, for your constructive answers. >> >> To Pavel: My application reads the column types out in order to pretty >> print the values - as mentioned by Tom - but also to generate a dialog >> for entering new data (of course combined with INSTEAD OF TRIGGERs). >> >> I understand that it's difficult to implement result-types for >> expressions in general. To me it would be enough if there would be at >> least one way to assign result-types with a CAST "wrapper" as I have >> mentioned before. >> >> Does anybody know whom to present this proposal in order remedy this >> current inconsistency in SQLite? sqlite-dev? >> >> In the meantime I thought of a hack and to assign the result-type by >> hand in the data dictionary after having created the VIEW. >> Would this be a feasible work around? >> >> Yours, S. >> >> 2010/5/6 BareFeetWare <list....@tandb.com.au>: >>> On 06/05/2010, at 2:51 PM, Dan Bishop wrote: >>> >>>> BareFeetWare wrote: >>>>> >>> >>>>> I've had the same issue. In the end I had to parse my view functions in >>>>> my own code and look for functions that give a particular type of result. >>>>> So, for instance, round() gives an integer, round(..., 2) gives a real, >>>>> concat() gives text. I also look for a cast(... as type) to use that >>>>> declared type. It's fiddly and I would have hoped SQLite would have at >>>>> least declared the type if a cast was present, but it seems not. >>>>> >>> >>>> A CAST expression could be assigned a declared type >>> >>> Yes, that should be fairly easy to do in SQLite, simpler than my adding it >>> in my own code (which then requires my own parser - not a trivial exercise). >>> >>>> but it would be difficult to implement it for expressions in general. Not >>>> only would you have to declare a type for every function >>> >>> Yes, probably half the functions don't have a consistently returned type >>> (eg length, concat). The ones that don't (eg coalesce) should just be left >>> to return a null (unknown) type. The schema developer can simply wrap those >>> expressions in a cast if they want a predetermined output type. >>> >>>> but you'd have to do it for operators as well, and what type should A * B >>>> be if A is "SMALLINT" and B is "UINT"? >>> >>> I hadn't thought of operators. As far as I know, mathematical operators (* >>> / + -) only work with integers and reals, Perhaps the result type could be >>> set to "numeric" or some other superclass. Or, to get real fancy, if the >>> two operands are declared integers, then the result is an integer; if one >>> is real then the result is real. AFAIK SMALLINT and UINT are both treated >>> by SQLite as integer, so the result would be an integer. >>> >>>> Should relational operators have a declared type of BOOLEAN? >>> >>> Good idea. >>> >>> Tom >>> BareFeetWare >>> >>> -- >>> Comparison of SQLite GUI tools: >>> http://www.tandb.com.au/sqlite/compare/?ml >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
