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>