On Thu, Sep 07, 2023 at 12:24:59PM +0200, Marc Espie wrote:
> I've been perusing the results of the build, they aren't good, then
> again portroach isn't quite grabbing the right stuff.
>
> - tweak the meter to use shorter xxxxx/xxxxx so that it fits again on the
> line.
>
> - use fullpkgname instead of distname because that one is *always* defined.
> (and strip v/p accordingly)
>
> - switch to roach_url/roach_sites
>
> - use the portsq cached table instead of the view (this goes MUCH faster
> not surprisingly)
>
> this does require an up-to-date sqlports.
>
> Long term, it would be appropriate to create a temp view based on portsq
> that contains all required fields with sensible names, so that it's
> less painful to rename stuff in every individual query.
And here's the updated patch which does create the temp view.
Index: Makefile
===================================================================
RCS file: /cvs/ports/misc/portroach/Makefile,v
retrieving revision 1.37
diff -u -p -r1.37 Makefile
--- Makefile 21 Jul 2023 10:57:10 -0000 1.37
+++ Makefile 7 Sep 2023 13:31:42 -0000
@@ -3,7 +3,7 @@ COMMENT= OpenBSD ports distfile version
GH_ACCOUNT= jasperla
GH_PROJECT= portroach
GH_TAGNAME= 2.0.11
-REVISION= 4
+REVISION= 5
CATEGORIES= misc
Index: patches/patch-Portroach_DataSrc_Ports_pm
===================================================================
RCS file: patches/patch-Portroach_DataSrc_Ports_pm
diff -N patches/patch-Portroach_DataSrc_Ports_pm
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ patches/patch-Portroach_DataSrc_Ports_pm 7 Sep 2023 13:31:42 -0000
@@ -0,0 +1,40 @@
+Index: Portroach/DataSrc/Ports.pm
+--- Portroach/DataSrc/Ports.pm.orig
++++ Portroach/DataSrc/Ports.pm
+@@ -114,6 +114,8 @@ sub BuildDB
+
+ $dbh = connect_db();
+
++ prepare_sql($sdbh, \%ssths, qw(create_view));
++ $ssths{create_view}->execute or die DBI->errstr;
+ prepare_sql($sdbh, \%ssths, qw(sqlports_count_ports
sqlports_fullpkgpaths_by_maintainer));
+
+ if ($settings{restrict_maintainer}) {
+@@ -131,6 +133,7 @@ sub BuildDB
+ # Query SQLports for all the information we need. We don't care about
+ # restrictions for now as this step basically copies sqlports. Check()
+ # will handle any restrictions instead.
++
+ $ssths{sqlports_count_ports}->execute or die DBI->errstr;
+ $num_ports = $ssths{sqlports_count_ports}->fetchrow_array();
+
+@@ -217,6 +220,10 @@ sub BuildPort
+ $name = fullpkgpathtoport($fullpkgpath);
+
+ $distname = $ports[2];
++ # get rid of version/epoch markers
++ $distname =~ s/v[0-9]+$//;
++ $distname =~ s/p[0-9]+$//;
++
+ foreach my $file (split /\s+/, $ports[3]) {
+ $file =~ s/:[A-Za-z0-9][A-Za-z0-9\,]*$//g;
+ push @distfiles, $file;
+@@ -305,7 +312,7 @@ sub BuildPort
+ }
+ }
+
+- info(1, $fullpkgpath, "($n_port out of $total_ports)");
++ info(1, $fullpkgpath, "($n_port/$total_ports)");
+
+ $ps->AddPort({
+ 'name' => $name,
Index: patches/patch-Portroach_SQL_pm
===================================================================
RCS file: /cvs/ports/misc/portroach/patches/patch-Portroach_SQL_pm,v
retrieving revision 1.4
diff -u -p -r1.4 patch-Portroach_SQL_pm
--- patches/patch-Portroach_SQL_pm 11 Mar 2022 19:38:17 -0000 1.4
+++ patches/patch-Portroach_SQL_pm 7 Sep 2023 13:31:42 -0000
@@ -1,7 +1,93 @@
Index: Portroach/SQL.pm
--- Portroach/SQL.pm.orig
+++ Portroach/SQL.pm
-@@ -311,7 +311,7 @@ $sql{portdata_fullpkgpaths} =
+@@ -71,51 +71,62 @@ $sql{portdata_update} =
+ fullpkgpath = ?
+ WHERE basepkgpath = ?);
+
++
+ # Port.pm:BuildPort()
++
++$sql{create_view} =
++ q(CREATE TEMP VIEW RoachData AS
++ SELECT fullpkgpath, categories, fullpkgname as distname,
++ roach_url as distfiles,
++ roach_sites as master_sites,
++ maintainer, comment, portroach,
++ portroach_comment, homepage
++ FROM portsq);
++
+ $sql{ports_select} =
+ q(SELECT fullpkgpath, categories, distname, distfiles,
+ master_sites, maintainer, comment, portroach,
+ portroach_comment, homepage
+- FROM ports);
++ FROM RoachData);
+
+ $sql{ports_select_count} =
+ q(SELECT COUNT(fullpkgpath)
+- FROM ports);
++ FROM RoachData);
+
+ $sql{ports_restrict_maintainer} =
+ q(SELECT fullpkgpath, categories, distname, distfiles,
+ master_sites, maintainer, comment, portroach,
+ portroach_comment, homepage
+- FROM ports
++ FROM RoachData
+ WHERE maintainer like ?);
+
+ $sql{ports_restrict_maintainer_count} =
+ q(SELECT COUNT(fullpkgpath)
+- FROM ports
++ FROM RoachData
+ WHERE maintainer like ?);
+
+ $sql{ports_restrict_category} =
+- q(SELECT fullpkgpath, categories, distname, distfiles,
++ q(SELECT fullpkgpath, categories, distname, distfiles
+ master_sites, maintainer, comment, portroach,
+ portroach_comment, homepage
+- FROM ports
++ FROM RoachData
+ WHERE categories like ?);
+
+ $sql{ports_restrict_category_count} =
+ q(SELECT COUNT(fullpkgpath)
+- FROM ports
++ FROM RoachData
+ WHERE categories like ?);
+
+ $sql{ports_restrict_port} =
+ q(SELECT fullpkgpath, categories, distname, distfiles,
+ master_sites, maintainer, comment, portroach,
+ portroach_comment, homepage
+- FROM ports
++ FROM RoachData
+ WHERE fullpkgpath like ?);
+
+ $sql{ports_restrict_port_count} =
+ q(SELECT COUNT(fullpkgpath)
+- FROM ports
++ FROM RoachData
+ WHERE fullpkgpath like ?);
+
+ $sql{portdata_insert} =
+@@ -127,11 +138,11 @@ $sql{portdata_insert} =
+
+ $sql{sqlports_fullpkgpaths_by_maintainer} =
+ q(SELECT fullpkgpath
+- FROM Ports
++ FROM RoachData
+ WHERE MAINTAINER LIKE ?);
+
+ $sql{sqlports_count_ports} =
+- q(SELECT COUNT(FULLPKGPATH) FROM Ports);
++ q(SELECT COUNT(FULLPKGPATH) FROM RoachData);
+
+ $sql{portconfig_update} =
+ q(UPDATE portdata
+@@ -311,7 +322,7 @@ $sql{portdata_fullpkgpaths} =
FROM portdata);
$sql{sqlports_check_fullpkgpath} =
Index: patches/patch-Portroach_Util_pm
===================================================================
RCS file: patches/patch-Portroach_Util_pm
diff -N patches/patch-Portroach_Util_pm
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ patches/patch-Portroach_Util_pm 7 Sep 2023 13:31:42 -0000
@@ -0,0 +1,12 @@
+Index: Portroach/Util.pm
+--- Portroach/Util.pm.orig
++++ Portroach/Util.pm
+@@ -652,7 +652,7 @@ sub info
+ my @items = (@_);
+ my ($str, $msg);
+
+- # 60 is chosen as 80 - length of "(xxxx out of xxxx)" (16)" -
separators.
++ # 60 is chosen so that "(xxxxx/xxxxx)" will fit
+ my $width = 30;
+ $width *= 2 if $full;
+
