From: Thomas Frohwein <tfrohwein@fastmail.com>
Subject: sqlports: show complete DistTuple
To: Marc Espie <espie@openbsd.org>, ports@openbsd.org
Date: Fri, 28 Nov 2025 11:50:41 -0800

Marc, ports@,

It's been irking me for a while that sqlports' DistTuple shows only the
last tuple (set of 5 components) for a package, rather than all of
them. I figured out that the UNIQUE constraint on FullPkgPath for
_DistTuple and the `INSERT OR REPLACE` logic replace rather than add
multiple DIST_TUPLE sets to a package entry.

This is fixed with the attached diff. There are likely more correct or
more elegant ways of doing this, as my experience with sqlite and
sqlports internals is limited.

Old Behaviour:

$ sqlite3 /usr/local/share/sqlports "select * from DistTuple where \
	FullPkgPath = 'audio/furnace';"
525|audio/furnace|github|superctr|adpcm|7736b178f4fb722d594c6ebdfc1ddf1af2ec81f7|extern/adpcm

With Diff Applied:

$ sqlite3 /usr/ports/pobj/sqlports-7.53/sqlports-7.53/sqlports \
	"select * from DistTuple where FullPkgPath = 'audio/furnace';"
525|audio/furnace|github|tildearrow|furnace|v0.6.8.3|.
525|audio/furnace|github|superctr|adpcm|7736b178f4fb722d594c6ebdfc1ddf1af2ec81f7|extern/adpcm

(For more striking examples, look at lang/v, www/nginx,
games/recoil-rts.)

Fixing this will likely help DIST_TUPLE in the future, as makes it
easier to look for and test for regressions.

comments? ok?

Index: Makefile
===================================================================
RCS file: /cvs/ports/databases/sqlports/Makefile,v
diff -u -p -r1.152 Makefile
--- Makefile	3 Nov 2025 14:46:04 -0000	1.152
+++ Makefile	28 Nov 2025 19:46:49 -0000
@@ -1,6 +1,7 @@
 CATEGORIES =		databases
 V =			7.53
 DISTNAME =		sqlports-$V
+REVISION =		0
 DISTFILES =
 COMMENT-main =		sqlite database of ports
 COMMENT-list =		full list of pkgpaths in ports
Index: files/Var.pm
===================================================================
RCS file: /cvs/ports/databases/sqlports/files/Var.pm,v
diff -u -p -r1.76 Var.pm
--- files/Var.pm	3 Nov 2025 14:46:04 -0000	1.76
+++ files/Var.pm	28 Nov 2025 19:46:50 -0000
@@ -150,7 +150,10 @@ sub pathref($)
 
 sub create_table($self, @c)
 {
-	Sql::Create::Table->new($self->table_name($self->table))->add(@c);
+	my $t = Sql::Create::Table->new($self->table_name($self->table))->add(@c);
+	if ($self->table_name($self->table) eq '_DistTuple') {
+		$t->noreplace();
+	}
 	$self->create_keyword_table;
 }
 
@@ -1232,7 +1235,7 @@ sub add($self, $ins)
 sub create_tables($self, $inserter)
 {
 	$self->create_table(
-	    $self->fullpkgpath,
+	    Sql::Column::Integer->new("FullPkgPath")->references("_Paths")->indexed,
 	    Sql::Column::Text->new("Type"),
 	    Sql::Column::Text->new("Account"),
 	    Sql::Column::Text->new("Project"),