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

[sqlite] Can I create this view more efficient

[Cecil Westerhof]

At the moment I have the following table:
CREATE TABLE memUsageLine(
className   TEXT NOT NULL,
pid INT  NOT NULL,
timeChecked INT  NOT NULL DEFAULT (strftime('%s')),

freeAfter   INT NOT NULL,
freeBefore  INT NOT NULL,
maxMemory   INT  NOT NULL, -- Is probably not necessary
totalAfter  INT NOT NULL,
totalBefore INT NOT NULL,

PRIMARY KEY (className, PID, timeChecked)
);

​and I create the following view:
CREATE VIEW memUsageLineExtended AS
SELECT   className
,pid
,strftime('%Y-%m-%d %H:%M', timeChecked, 'unixepoch',
'localtime') as timeChecked
,freeAfter
,freeBefore
,maxMemory
,totalAfter
,totalBefore
,totalAfter  -
freeAfter   as usedAfter
,totalBefore -
freeBefore  as usedBefore
,CAST(totalAfter AS REAL) / (totalAfter  -
freeAfter)  as ratioTotalUsed
FROM memUsageLine
;

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?

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