If you want to make sure the strftime function is only called once why not say:
CREATE VIEW latest_aliases as select a.name, a.assignedTo, a.validFrom from
aliases as a join (select name, max(validfrom) as mvf from aliases where
validFrom <= (select strftime('%s', 'now')) group by name) as b on
a.name=b.name and a.validFrom=b.mvf;
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
> -----Original Message-----
> From: [email protected] [mailto:sqlite-users-
> [email protected]] On Behalf Of Kevin Martin
> Sent: Sunday, 22 July, 2012 09:17
> To: General Discussion of SQLite Database
> Subject: [sqlite] Current Time in WHEN clause.
>
> Hi,
>
> I have the following schema:
>
> > CREATE TABLE aliases (name text not null, assignedTo text, validFrom
> integer not null);
> > CREATE TABLE services (name text primary key, url text not null, type text
> not null);
> > CREATE VIEW latest_aliases as select a.name, a.assignedTo, a.validFrom from
> aliases as a join (select name, max(validfrom) as mvf from aliases where
> validFrom <= strftime('%s', 'now') group by name) as b on a.name=b.name and
> a.validFrom=b.mvf;
> > CREATE VIEW alias_info as select a.name, s.url, s.type from latest_aliases
> as a join services as s on a.assignedTo = s.name;
>
> The aliases table maps names to services at different times. So for example
> with the following data:
>
> > sqlite> select * from services;
> > svc1|http://somewhere|type1
> > svc2|http://somewhere.else|type1
>
> > sqlite> select *,datetime(validFrom, 'unixepoch') from aliases;
> > env1|svc1|1342967110|2012-07-22 14:25:10
> > env1|svc2|1342967185|2012-07-22 14:26:25
>
> I want env1 to be mapped to svc1 after 14:25:10, until 14:26:25 after which
> point I want it to be mapped to svc2.
>
> This is done with the latest_aliases view, alias_info just joins
> latest_aliases to the services table to get the connection info.
>
> However, I'm quite concerned about executing strftime('%s', 'now') inside the
> subquery, specifically does it execute multiple times? I don't want this to
> happen because it could cause a rare bug when the list of services returned
> is partly pre and partly post an update.
>
> I'm trying to convince myself that the subquery in latest_aliases only
> executes once, and also that alias_info only runs latest_aliases once.
> However, I'm not doing very well at convincing myself. Can someone confirm
> this is true, or suggest a nice solution to get the current time function out
> of the when clause.
>
> Thanks,
> Kevin Martin.
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
