From: Stuart Henderson <stu@spacehopper.org>
Subject: Re: sqlports: show complete DistTuple
To: Thomas Frohwein <tfrohwein@fastmail.com>
Cc: Marc Espie <espie@openbsd.org>, ports@openbsd.org
Date: Fri, 28 Nov 2025 20:34:51 +0000

On 2025/11/28 11:50, Thomas Frohwein wrote:
> 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?
> 

this change makes a lot of sense to me.

> 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

I'd bump V rather than REVISION.

>  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"),