Skip site navigation (1)Skip section navigation (2)
Date:      Wed, 15 Aug 2018 12:35:01 -0400
From:      Dan Langille <dan@langille.org>
To:        Matthew Seaman <matthew@FreeBSD.org>
Cc:        freebsd-ports@freebsd.org
Subject:   Re: getting PKGNAME from CONFLICTS
Message-ID:  <6365BBAB-4A4B-4553-827B-BC8A4BBE88F2@langille.org>
In-Reply-To: <16f81513-5324-001f-d1c7-45536835ef15@FreeBSD.org>
References:  <286CAC11-E9C6-42C4-8D41-97F51EFF1596@yahoo.com> <9823D065-3FCC-4D69-9EB4-9C4CD01C7778@langille.org> <16f81513-5324-001f-d1c7-45536835ef15@FreeBSD.org>

next in thread | previous in thread | raw e-mail | index | archive | help

--Apple-Mail=_49273CE5-6CE6-4C4D-962D-075F2A125682
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=us-ascii

> On Aug 15, 2018, at 8:46 AM, Matthew Seaman <matthew@FreeBSD.org> =
wrote:
>=20
> 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:
>>>=20
>>>=20
>>> Dan Langille dan at langille.org wrote on
>>> Tue Aug 14 17:54:01 UTC 2018 :
>>>=20
>>>> . . .
>>>> At https://dev.freshports.org/www/p5-CGI/ you can see:
>>>>=20
>>>>  CONFLICTS: p5-CGI.pm-[1-3]*
>>>> . . .
>>>> To extract the PKGNAME values from the CONFLICTS I will need to =
remove everything after the trailing dash.
>>>> . . .
>>>=20
>>> p5-
>>> vs.
>>> p5-CGI.pm-
>>> vs.
>>> p5-CGI.pm-[1-
>>>=20
>>> 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-").
>>=20
>> Agreed.  The hard part is identifying the regex and deleting it from =
consideration.
>>=20
>=20
> If you don't mind spawning a new process, you can just do:
>=20
> % pkg search -qg 'p5-CGI.pm-[1-3]*'
> p5-CGI.pm-3.63_1,1
>=20
> 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=3D# SELECT categoryport(id) FROM (
  SELECT distinct P.id
    FROM ports P JOIN commit_log_ports CLP on P.id =3D 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=3D# 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 =3D =
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#FUNCTION=
S-POSIX-REGEXP =
<https://www.postgresql.org/docs/10/static/functions-matching.html#FUNCTIO=
NS-POSIX-REGEXP>

It seems to be pretty close.

Thank you.

--
Dan Langille - BSDCan / PGCon
dan@langille.org



--Apple-Mail=_49273CE5-6CE6-4C4D-962D-075F2A125682
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
	filename=signature.asc
Content-Type: application/pgp-signature;
	name=signature.asc
Content-Description: Message signed with OpenPGP

-----BEGIN PGP SIGNATURE-----
Comment: GPGTools - http://gpgtools.org

iQGTBAEBCgB9FiEEzqcJ4oeyf8sgTIEBIU09XU2nXtMFAlt0VjVfFIAAAAAALgAo
aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldENF
QTcwOUUyODdCMjdGQ0IyMDRDODEwMTIxNEQzRDVENERBNzVFRDMACgkQIU09XU2n
XtNmaQf/Zi6pudAYElDqpqiKvl6IYT0un7IaHjufspGGXhynz6gcXMCUXkEmgCU+
iXogCXNqu3Dcgu3VTVV9xcIWJ+6OvxljhI30ZQeCYEECWfw3OvqwaMdGIHfd/FC6
Th8ZGFurFYA33/MS3+9INuC0Ifa0ytIc3andohYkI4xejDq8F7xfh9ArpTT6nGww
N4P8whPj0Ub0mFif1rBHGtd1ms1jDM8wDygYZ6qcthXLfxNrmj/iwQ+d07eUTtcs
190+7rwj+m4gCVlScWqjGrzJaVxqvQC5RmJjRL26uYZLYuhw4Nvq0GaT2oOkIibx
xS+6LxJGE4h75yH45UlhWx42yM3xfA==
=rTRw
-----END PGP SIGNATURE-----

--Apple-Mail=_49273CE5-6CE6-4C4D-962D-075F2A125682--



Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?6365BBAB-4A4B-4553-827B-BC8A4BBE88F2>