Re: portgen: use sqlports cache table

On Sat, Nov 23, 2019 at 01:48:16PM -0800, Andrew Hewus Fresh wrote:
> On Sat, Nov 23, 2019 at 01:25:16PM +0100, Marc Espie wrote:
> > Woah. That code is horrible. It also spends a lot of time
> > connecting/disconnecting.
> >
> > - The database connection should be a singleton
> > - sqlports should always be opened ReadOnly
> > - The query should be prepared.
> >
> > Apart from that, PortsQ will help, but the basic code should be taken out
> > and shot.


I think this is a slight improvement, building on the changes sthen@
made. The benefit here is that the query to the sqlports database
should be able to figure out if what we're looking for exists without
looping over a bunch of possibilities.

I think there is still room for future improvement, for example, being
able to find "perltidy" from "p5-Perl-Tidy" by looking without the
prefix, removing any non-alphanumeric and continuing to convert to
lowercase. But, that could make it find things that aren't actually
what we're looking for.

I'm a bit worried about python3 only ports, so if someone can make sure
things work the way they expect with those, I'd appreciate it.

Comments? OK?


Index: infrastructure/lib/OpenBSD/PortGen/Utils.pm
===================================================================
RCS file: /cvs/ports/infrastructure/lib/OpenBSD/PortGen/Utils.pm,v
retrieving revision 1.5
diff -u -p -r1.5 Utils.pm
--- infrastructure/lib/OpenBSD/PortGen/Utils.pm 23 Nov 2019 14:59:39 -0000 1.5
+++ infrastructure/lib/OpenBSD/PortGen/Utils.pm 30 Nov 2019 22:56:23 -0000
@@ -18,6 +18,7 @@ package OpenBSD::PortGen::Utils;

use 5.012;
use warnings;
+use feature qw( state );

use parent qw( Exporter );

@@ -47,12 +48,8 @@ sub ports_dir { $ENV{PORTSDIR} || '/usr/

sub base_dir { ports_dir() . '/mystuff' }

-sub module_in_ports
+sub _module_sth
{
- my ( $module, $prefix ) = @_;
-
- return unless $module and $prefix;
-
my $dbfile = '/usr/local/share/sqlports';
die "install databases/sqlports and databases/p5-DBD-SQLite\n"
unless -e $dbfile;
@@ -64,22 +61,34 @@ sub module_in_ports
RaiseError => 1,
} ) or die "failed to connect to database: $DBI::errstr";

- my @results = @{ $dbh->selectcol_arrayref(
- "SELECT _paths.fullpkgpath FROM _paths JOIN _paths p1 ON p1.pkgpath=_paths.id
- JOIN _ports ON _ports.fullpkgpath=p1.id WHERE _ports.distname LIKE ?",
- {}, "$module%"
- ) };
-
- $dbh->disconnect();
-
- # just returning the shortest one that's a module of the same ecosystem
- # this should be improved
- @results = sort { length $a <=> length $b } @results;
-
- # this works well enough in practice, but can't rely on it
- # see devel/perltidy
- for (@results) {
- return $_ if /\/$prefix/;
+ return $dbh->prepare(q{
+ SELECT _Paths.FullPkgPath FROM _Paths
+ JOIN _Ports ON _Paths.PkgPath = _Ports.FullPkgPath
+ WHERE PKGSTEM = ?
+ AND _Paths.Id = _Paths.PkgPath
+ ORDER BY LENGTH(_Paths.FullPkgPath)
+ });
+}
+
+sub module_in_ports
+{
+ my ( $module, $prefix ) = @_;
+ return unless $module and $prefix;
+
+ state $sth = _module_sth();
+ END { undef $sth }; # Bus error if destroyed during global destruction
+
+ my @stems = ( $prefix . $module );
+
+ # We commonly convert the port to lowercase
+ push @stems, $prefix . lc($module) if $module =~ /\p{Upper}/;
+
+ foreach my $stem (@stems) {
+ $sth->execute($stem);
+ my ($path, @extra) = map {@$_} @{ $sth->fetchall_arrayref };
+ warn "Found paths other than $path: @extra\n"
+ if @extra;
+ return $path if $path;
}

# Many ports, in particular python ports, start with $prefix,
Index: infrastructure/lib/OpenBSD/PortGen/Port/PyPI.pm
===================================================================
RCS file: /cvs/ports/infrastructure/lib/OpenBSD/PortGen/Port/PyPI.pm,v
retrieving revision 1.17
diff -u -p -r1.17 PyPI.pm
--- infrastructure/lib/OpenBSD/PortGen/Port/PyPI.pm 15 Jul 2019 13:35:35 -0000 1.17
+++ infrastructure/lib/OpenBSD/PortGen/Port/PyPI.pm 30 Nov 2019 22:56:23 -0000
@@ -54,11 +54,20 @@ sub name_new_port
{
my ( $self, $di ) = @_;

- my $name = ref $di ? $di->{info}{name} : $di;
- $name =~ s/^python-/py-/;
+ my $module = ref $di ? $di->{info}{name} : $di;
+ $module =~ s/^python-/py-/;

- $name = $self->SUPER::name_new_port($name);
- $name = "pypi/$name" unless $name =~ m{/};
+ my $name = $self->SUPER::name_new_port($module);
+
+ # Try for a py3 only version if we didn't find something ported
+ unless ( $name =~ m{/} ) {
+ if ( my $p = module_in_ports( $name, 'py3-' ) ) {
+ $name = $p;
+ }
+ else {
+ $name = "pypi/$name"
+ }
+ }

return $name;
}
@@ -171,7 +180,8 @@ sub get_deps

next if @plat and join( " ", @plat ) !~ /OpenBSD/i;

- my $port = module_in_ports( $name, 'py-' );
+ my $port = module_in_ports( $name, 'py-' )
+ || module_in_ports( $name, 'py3-' );
my $dep_dir;

if ($port) {