git: 9cbf77aba0aa - main - Tools/scripts: add installed-ports-by-origin.sh

The branch main has been updated by fuz:

URL: https://cgit.FreeBSD.org/ports/commit/?id=9cbf77aba0aa6f3e8f88b21fa04b5fc8fc060810

commit 9cbf77aba0aa6f3e8f88b21fa04b5fc8fc060810
Author:     Ralf van der Enden <tremere@cainites.net>
AuthorDate: 2025-10-28 21:49:34 +0000
Commit:     Robert Clausecker <fuz@FreeBSD.org>
CommitDate: 2025-10-31 17:14:47 +0000

    Tools/scripts: add installed-ports-by-origin.sh
    
    This script generates a list of all installed ports by origin.  This is
    similar pkg info -qoa, but with flavors.  This is useful for example to
    create a list of ports for Poudriere to build.
---
 Tools/scripts/installed-ports-by-origin.sh | 21 +++++++++++++++++++++
 1 file changed, 21 insertions(+)

diff --git a/Tools/scripts/installed-ports-by-origin.sh b/Tools/scripts/installed-ports-by-origin.sh
new file mode 100755
index 000000000000..96375ed1fd1a
--- /dev/null
+++ b/Tools/scripts/installed-ports-by-origin.sh
@@ -0,0 +1,21 @@
+#!/bin/sh
+
+# Constributed by Ralf van der Enden <tremere@cainites.net>
+# Public domain.
+
+# print a list of all installed ports by origin with flavour.
+# this is similar to pkg info -qoa, but prints a flavour if the
+# package has one.
+
+sqlite3 /var/db/pkg/local.sqlite \
+	"select origin || coalesce('@' || (
+		select t4.annotation as annotation
+		from packages t1
+		    inner join pkg_annotation t2 on t1.id = t2.package_id
+		    inner join annotation t3 on t2.tag_id = t3.annotation_id
+			and t3.annotation = 'flavor'
+		    inner join annotation t4 on t2.value_id = t4.annotation_id
+			and t2.tag_id = t3.annotation_id
+			and t3.annotation = 'flavor'
+		where t1.origin = t0.origin), '')
+	from packages t0;"