> On Aug 15, 2018, at 8:46 AM, Matthew Seaman <matt...@freebsd.org> wrote: > > On 15/08/2018 00:35, Dan Langille wrote: >>> On Aug 14, 2018, at 2:55 PM, Mark Millard via freebsd-ports >>> <freebsd-ports@freebsd.org> wrote: >>> >>> >>> Dan Langille dan at langille.org wrote on >>> Tue Aug 14 17:54:01 UTC 2018 : >>> >>>> . . . >>>> At https://dev.freshports.org/www/p5-CGI/ you can see: >>>> >>>> CONFLICTS: p5-CGI.pm-[1-3]* >>>> . . . >>>> To extract the PKGNAME values from the CONFLICTS I will need to remove >>>> everything after the trailing dash. >>>> . . . >>> >>> p5- >>> vs. >>> p5-CGI.pm- >>> vs. >>> p5-CGI.pm-[1- >>> >>> It looks to me like "trailing dash" probably has a >>> complicated definition where some "-"(s) may exist >>> that are to be ignored after the one of interest. >>> In the example I'm guessing that the middle >>> "-" is intended (so "p5-CGI.pm-"). >> >> Agreed. The hard part is identifying the regex and deleting it from >> consideration. >> > > If you don't mind spawning a new process, you can just do: > > % pkg search -qg 'p5-CGI.pm-[1-3]*' > p5-CGI.pm-3.63_1,1 > > This does assume your pkg(8) is configured to use a repository with all > possible packages available. The default FreeBSD repositories are a > good choice in that regard.
They are good, but the FreshPorts database has a collection of PKGNAME
values for every port. I want to query those values.
> Or if you already have a database table with all of the package names
> and versions, then you'll presumably want to change the glob expression
> into a regex match (in this case something like '^p5-CGI\.pm-[1-3].*')
freshports.dev=# SELECT categoryport(id) FROM (
SELECT distinct P.id
FROM ports P JOIN commit_log_ports CLP on P.id = CLP.port_id
WHERE P.package_name || '-' || CLP.port_version ~ 'p5-CGI.pm-[1-3].*') TMP;
categoryport
---------------
www/p5-CGI.pm
This queries all ports, all commits on those ports, and the revisions for each
port from that commit.
It takes about 2 seconds.
This query takes about 1 second:
freshports.dev=# SELECT distinct categoryport(id) FROM
freshports.dev-# (SELECT P.id, P.package_name || '-' || CLP.port_version AS
release
freshports.dev(# FROM ports P JOIN commit_log_ports CLP on P.id =
CLP.port_id) TMP
freshports.dev-# WHERE TMP.release like 'p5-CGI.pm%';
categoryport
---------------
www/p5-CGI.pm
(1 row)
I think this might be the best solution for searching for a distinct match.
Everyone feel free to correct my thinking here:
###
Ideally, the result for this query would be static, never changing. But it can
change as ports are added/removed.
Assuming FreshPorts runs the above query while processing a commit to
www/p5-CGI, then the data
collected *could* be invalidated during the next commit.
But this only affects PKGNAME values. Thus, we only have to requery after a new
port is added or
when a change occurs to the PKGNAME for a port.
###
We can requery in the background as required.
> Unless there's a PG extension that allows using glob(3) to match
> strings? I can't see one after a pretty cursory search. (sqlite has
> glob(3) support, which is what the pkg(8) command above is using under
> the hood.)
I found this:
https://www.postgresql.org/docs/10/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
<https://www.postgresql.org/docs/10/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP>
It seems to be pretty close.
Thank you.
--
Dan Langille - BSDCan / PGCon
d...@langille.org
signature.asc
Description: Message signed with OpenPGP
