From owner-freebsd-ports@freebsd.org Wed Aug 15 16:35:11 2018 Return-Path: Delivered-To: freebsd-ports@mailman.ysv.freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2610:1c1:1:606c::19:1]) by mailman.ysv.freebsd.org (Postfix) with ESMTP id 6A5801066D01 for ; Wed, 15 Aug 2018 16:35:11 +0000 (UTC) (envelope-from dan@langille.org) Received: from clavin2.langille.org (clavin2.langille.org [199.233.228.197]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (Client CN "clavin.langille.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by mx1.freebsd.org (Postfix) with ESMTPS id 1C1E495622; Wed, 15 Aug 2018 16:35:11 +0000 (UTC) (envelope-from dan@langille.org) Received: from (clavin2.int.langille.org (clavin2.int.unixathome.org [10.4.7.7]) (Authenticated sender: hidden) with ESMTPSA id 48ABF19CC ; Wed, 15 Aug 2018 16:35:10 +0000 (UTC) From: Dan Langille Message-Id: <6365BBAB-4A4B-4553-827B-BC8A4BBE88F2@langille.org> Content-Type: multipart/signed; boundary="Apple-Mail=_49273CE5-6CE6-4C4D-962D-075F2A125682"; protocol="application/pgp-signature"; micalg=pgp-sha512 Mime-Version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\)) Subject: Re: getting PKGNAME from CONFLICTS Date: Wed, 15 Aug 2018 12:35:01 -0400 In-Reply-To: <16f81513-5324-001f-d1c7-45536835ef15@FreeBSD.org> Cc: freebsd-ports@freebsd.org To: Matthew Seaman References: <286CAC11-E9C6-42C4-8D41-97F51EFF1596@yahoo.com> <9823D065-3FCC-4D69-9EB4-9C4CD01C7778@langille.org> <16f81513-5324-001f-d1c7-45536835ef15@FreeBSD.org> X-Mailer: Apple Mail (2.3445.9.1) X-Content-Filtered-By: Mailman/MimeDel 2.1.27 X-BeenThere: freebsd-ports@freebsd.org X-Mailman-Version: 2.1.27 Precedence: list List-Id: Porting software to FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 15 Aug 2018 16:35:11 -0000 --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 = wrote: >=20 > On 15/08/2018 00:35, Dan Langille wrote: >>> On Aug 14, 2018, at 2:55 PM, Mark Millard via freebsd-ports = 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 = 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--