2018-02-09 0:05 GMT+01:00 Danny Milosavljevic <dannym@scratchpost.org>:

> Hi Ludo,
>
> On Thu, 08 Feb 2018 23:21:58 +0100
> ludo@gnu.org (Ludovic Courtès) wrote:
>
> > > from a security standpoint - except for db-get-builds, which I'm
> amending
> > > right now.
> >
> > Oh sorry, I think I did the same thing as you were sending this message:
> >
> >   https://git.savannah.gnu.org/cgit/guix/guix-cuirass.git/commit/?id=
> 8c7c93922bbe0513ff4c4ff3a6e554e3a72635b6
>
> > WDYT?
>
> I'd prefer not to have so many different SQL statements, we get a
> combinatorial explosion if we aren't careful (whether we cache or not,
> the relational database management system is going to hate us anyway
> when we do that).
>
> But I guess there are not that many yet.
>
> If we are fine in not being able to search for literal NULL we can use
> NULL as
> "anything" marker and have a static WHERE statement (this way is
> customary).
>
> Also, I've asked on the sqlite mailing list - ORDER BY cannot support "?",
> so
> those are unavoidable (also, we can't usefully do the ORDER BY ourselves
> by sorting the result - because of the LIMIT clause)
>
> Anyway as long as we are under 10000 statements it should be fine :P
>
> > Indeed!  Should we change ‘sqlite-finalize’ to a noop when called on a
> > cached statement?  (Otherwise users would have to keep track of whether
> > or not a statement is cached.)
>
> Hmm maybe that's a good way.  But its a little magic.
>
> If you are not finalizing the statement, it will be reused anyway the next
> time
> you use the same SQL text.  The user just shouldn't call finalize - which
> sounds
> simple enough for him not to do.
>
> I think having sqlite-exec detect literal SQL text is a nice middle way.
>
> If the SQL text is a literal it means it's right there in the source code
> and is probably not going to change - how would it?
>
> Otherwise err on the side of caution and finalize the statement - it's
> a little slower but safer that way.  I think that would pretty much only
> mean db-get-builds.
>
> Do you think that's too much magic?  Or more than the other way?  I
> wonder...
>
> I think that if we do this magic we do it right there in the cuirass
> database.scm
> module and it's never going to move into guile-sqlite3 :)
>
> On the other hand, if we special-cached sqlite-finalize, we'd have to
> provide
> sqlite-finalize* or something that does the freeing anyway...
>
> > Besides, on the big database on berlin, the initial:
> >
> >   (db-get-builds db '((status pending)))
> >
> > call takes a lot of time and memory.  I guess we’re doing something
> > wrong, but I’m not sure what.  The same query in the ‘sqlite3’ CLI is
> > snappy and does not consume much memory.
>
> WTF.  I'll have a look.
>
> > One of the things we’re doing wrong is that ‘Outputs’ table: each
> > ‘db-format-build’ call triggers a lookup in that table.  We should
> > instead probably simply store output lists in the ‘Derivations’ table.
>
> Definitely.  That's one of the things we should inline into db-get-builds.
> Relational databases are good at joins, let's not to their work for them.
>
> > Which also means we should have schema versioning and a way to upgrade…
>
> Yeah.
>
> I've used this: http://sqitch.org/ for a few projects, if you see it fits
the bill
I can help to get this working. I liked it, because it supports different
databases,
it was a big plus for me. It is also nice to be able to declare
dependencies of changes.

If you have other preferred method for this, the I would like to hear about
that.
I use these kind of things regulary in my work, it would be nice to get to
know new
methods. Thanks.


> > > I've also reintroduced sqlite-bind-args in a nicer version, please
> pull:
> > > https://notabug.org/civodul/guile-sqlite3/pulls/3 .
> >
> > It is OK with you to write it like this:
>
> Yes, looks good!  Thanks!
>
>