Re: [sqlite] Can I create this view more efficient

[Cecil Westerhof]

That is a fast reply. :-D

2016-08-12 23:48 GMT+02:00 Simon Slavin :

>
> On 12 Aug 2016, at 10:34pm, Cecil Westerhof 
> wrote:
>
> > In the past I worked (I think) with a database (not SQLite) where I could
> > usedAfter in the definition for ratioTotalUsed. It is not a very big
> > problem, but is something like that possible with SQLite?
>
> Sorry, but you cannot do this in SQLite.  You cannot rely on usedAfter
> being already defined when you are defining ratioTotalUsed.  You must
> define ratioTotalUsed in terms of the memUsageLine table.
>

​I was afraid of that, but just wanted to be sure. Thanks.​




> There is a way to do it.  You define a first VIEW which calculates
> usedAfter and includes some other fields from the TABLE, when you define a
> second VIEW which takes its values from the first VIEW.
>
> In your example this is not worth the extra processing since it is easy to
> calculate (totalAfter-freeAfter), but it can be useful when aggregate
> functions are used.
>

​Exactly my thoughts.


​It is very handy to get the information out of a SQLite database instead
of from several​ log files. And with views I get the extra information for
free. It was some work, but that is only once. I am going to use SQLite
more often I think. ;-)

-- 
Cecil Westerhof
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can I create this view more efficient

[Simon Slavin]

On 12 Aug 2016, at 10:34pm, Cecil Westerhof  wrote:

> In the past I worked (I think) with a database (not SQLite) where I could
> usedAfter in the definition for ratioTotalUsed. It is not a very big
> problem, but is something like that possible with SQLite?

Sorry, but you cannot do this in SQLite.  You cannot rely on usedAfter being 
already defined when you are defining ratioTotalUsed.  You must define 
ratioTotalUsed in terms of the memUsageLine table.

There is a way to do it.  You define a first VIEW which calculates usedAfter 
and includes some other fields from the TABLE, when you define a second VIEW 
which takes its values from the first VIEW.

In your example this is not worth the extra processing since it is easy to 
calculate (totalAfter-freeAfter), but it can be useful when aggregate functions 
are used.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users