Feb 102021
By request FreeBSD 14 is being added to the list of build packages. Looking at the main freebsd pkg mirror, we can see FreeBSD:14 is listed.
These are the items in question:
- FreeBSD:14:i386
- FreeBSD:14:amd64
- FreeBSD:14:aarch64
- FreeBSD:14:armv6
- FreeBSD:14:armv7
- FreeBSD:14:mips
- FreeBSD:14:mips64
- FreeBSD:14:powerpc64
The SQL is:
insert in abi (name) values (....
But why type all this, code it.
#!/bin/sh
echo 'insert into abi (name) values '
ARCHS="aarch64 amd64 armv6 armv7 i386 mips mips64 powerpc64"
for ARCH in $ARCHS
do
echo -n "('FreeBSD:14:$ARCH'), "
done
echo ';'
Which produces:
[dan@pg02:~/bin] $ sh ./generate_abi_insert_sql
insert into abi (name) values
('FreeBSD:14:aarch64'), ('FreeBSD:14:amd64'), ('FreeBSD:14:armv6'), ('FreeBSD:14:armv7'), ('FreeBSD:14:i386'), ('FreeBSD:14:mips'), ('FreeBSD:14:mips64'), ('FreeBSD:14:powerpc64'), ;
[dan@pg02:~/bin] $
Note the extra trailing comma in the SQL. It is easier to remove that manually than to update the code.
Running that SQL:
freshports.dev=# begin;
BEGIN
freshports.dev=# insert into abi (name) values
('FreeBSD:14:aarch64'), ('FreeBSD:14:amd64'), ('FreeBSD:14:armv6'), ('FreeBSD:14:armv7'), ('FreeBSD:14:i386'), ('FreeBSD:14:mips'), ('FreeBSD:14:mips64'), ('FreeBSD:14:powerpc64') ;
INSERT 0 8
freshports.dev=# select * from abi;
id | name | active
----+----------------------+--------
1 | FreeBSD:12:amd64 | t
2 | FreeBSD:13:aarch64 | t
4 | FreeBSD:11:i386 | t
6 | FreeBSD:11:amd64 | t
7 | FreeBSD:11:aarch64 | t
8 | FreeBSD:12:i386 | t
9 | FreeBSD:12:aarch64 | t
10 | FreeBSD:13:i386 | t
11 | FreeBSD:13:amd64 | t
12 | FreeBSD:11:armv6 | t
13 | FreeBSD:11:mips | t
14 | FreeBSD:11:mips64 | t
15 | FreeBSD:12:armv6 | t
16 | FreeBSD:12:armv7 | t
17 | FreeBSD:12:mips | t
18 | FreeBSD:12:mips64 | t
19 | FreeBSD:12:powerpc64 | t
20 | FreeBSD:13:armv6 | t
21 | FreeBSD:13:armv7 | t
22 | FreeBSD:13:mips | t
23 | FreeBSD:13:mips64 | t
24 | FreeBSD:13:powerpc64 | t
25 | FreeBSD:14:aarch64 | t
26 | FreeBSD:14:amd64 | t
27 | FreeBSD:14:armv6 | t
28 | FreeBSD:14:armv7 | t
29 | FreeBSD:14:i386 | t
30 | FreeBSD:14:mips | t
31 | FreeBSD:14:mips64 | t
32 | FreeBSD:14:powerpc64 | t
(30 rows)
We can see the new rows, and thanks to a trigger, we also have new rows in the packages_last_checked table:
freshports.dev=# select * from packages_last_checked;
abi_id | last_checked | repo_date | import_date | package_set | processed_date
--------+------------------------------+------------------------+-------------------------------+-------------+-------------------------------
8 | 2021-02-10 17:00:13.20563+00 | 2021-02-10 01:12:07+00 | 2021-02-10 11:21:34.044251+00 | quarterly | 2021-02-10 11:22:23.725106+00
17 | 2021-02-10 17:00:13.20563+00 | 2018-10-16 11:04:52+00 | 2020-07-17 14:00:45.505818+00 | latest | 2020-07-17 14:04:11.704782+00
17 | 2021-02-10 17:00:13.20563+00 | 2020-10-01 04:01:46+00 | 2020-10-01 05:00:20.235932+00 | quarterly | 2020-10-01 05:00:24.858447+00
18 | 2021-02-10 17:00:13.20563+00 | 2018-10-16 09:49:08+00 | 2020-07-17 14:00:52.969214+00 | latest | 2020-07-17 14:04:20.85262+00
18 | 2021-02-10 17:00:13.20563+00 | 2020-08-04 15:33:36+00 | 2020-08-04 16:00:29.240376+00 | quarterly | 2020-08-04 16:00:33.420489+00
19 | 2021-02-10 17:00:13.20563+00 | | | latest |
19 | 2021-02-10 17:00:13.20563+00 | 2021-01-26 04:27:25+00 | 2021-01-26 12:00:29.770876+00 | quarterly | 2021-01-26 12:00:45.577918+00
2 | 2021-02-10 17:00:13.20563+00 | 2021-01-27 02:49:44+00 | 2021-01-28 08:00:18.188754+00 | latest | 2021-01-28 08:00:24.905832+00
2 | 2021-02-10 17:00:13.20563+00 | 2021-02-09 11:53:51+00 | 2021-02-10 11:21:40.79661+00 | quarterly | 2021-02-10 11:22:35.063951+00
11 | 2021-02-10 17:00:13.20563+00 | 2021-02-10 02:50:43+00 | 2021-02-10 11:21:46.811564+00 | latest | 2021-02-10 11:22:46.756966+00
11 | 2021-02-10 17:00:13.20563+00 | 2021-02-09 14:50:17+00 | 2021-02-10 11:21:53.824806+00 | quarterly | 2021-02-10 11:23:01.600253+00
20 | 2021-02-10 17:00:13.20563+00 | 2021-01-25 12:43:34+00 | 2021-01-25 15:00:21.52586+00 | latest | 2021-01-25 15:00:40.013898+00
20 | 2021-02-10 17:00:13.20563+00 | 2021-01-30 10:31:09+00 | 2021-01-30 22:00:20.164502+00 | quarterly | 2021-01-30 22:00:24.297362+00
21 | 2021-02-10 17:00:13.20563+00 | 2021-01-25 13:03:16+00 | 2021-01-25 15:00:35.365614+00 | latest | 2021-01-25 15:00:52.742863+00
21 | 2021-02-10 17:00:13.20563+00 | 2021-01-31 00:34:34+00 | 2021-01-31 12:00:21.788735+00 | quarterly | 2021-01-31 12:00:30.494576+00
10 | 2021-02-10 17:00:13.20563+00 | 2021-02-10 02:22:42+00 | 2021-02-10 11:22:01.40289+00 | latest | 2021-02-10 11:23:09.115036+00
10 | 2021-02-10 17:00:13.20563+00 | 2021-02-09 01:06:47+00 | 2021-02-09 21:00:29.817299+00 | quarterly | 2021-02-09 21:00:53.748818+00
22 | 2021-02-10 17:00:13.20563+00 | 2020-01-25 12:29:08+00 | 2020-07-17 14:01:34.059862+00 | latest | 2020-07-17 14:05:11.163848+00
22 | 2021-02-10 17:00:13.20563+00 | | | quarterly |
23 | 2021-02-10 17:00:13.20563+00 | 2021-01-24 02:59:02+00 | 2021-01-24 04:00:19.382673+00 | latest | 2021-01-24 04:00:26.718195+00
23 | 2021-02-10 17:00:13.20563+00 | 2021-02-09 10:21:23+00 | 2021-02-09 11:00:17.626645+00 | quarterly | 2021-02-09 11:00:20.605528+00
24 | 2021-02-10 17:00:13.20563+00 | 2021-01-22 06:18:08+00 | 2021-01-22 21:00:29.352318+00 | latest | 2021-01-22 21:00:42.08494+00
24 | 2021-02-10 17:00:13.20563+00 | | | quarterly |
25 | | | | latest |
25 | | | | quarterly |
26 | | | | latest |
26 | | | | quarterly |
27 | | | | latest |
27 | | | | quarterly |
28 | | | | latest |
28 | | | | quarterly |
29 | | | | latest |
29 | | | | quarterly |
30 | | | | latest |
30 | | | | quarterly |
31 | | | | latest |
31 | | | | quarterly |
32 | | | | latest |
32 | | | | quarterly |
7 | 2021-02-10 17:00:13.20563+00 | 2018-09-29 04:24:41+00 | 2020-10-04 15:00:12.762803+00 | latest | 2020-10-04 15:02:18.094703+00
7 | 2021-02-10 17:00:13.20563+00 | 2020-08-22 06:40:07+00 | 2020-10-04 15:00:27.026864+00 | quarterly | 2020-10-04 15:02:39.132243+00
6 | 2021-02-10 17:00:13.20563+00 | 2021-02-08 21:21:07+00 | 2021-02-09 05:00:13.733719+00 | latest | 2021-02-09 05:00:27.510551+00
6 | 2021-02-10 17:00:13.20563+00 | 2021-02-09 05:32:54+00 | 2021-02-09 07:00:12.877082+00 | quarterly | 2021-02-09 07:00:26.142752+00
12 | 2021-02-10 17:00:13.20563+00 | 2016-06-30 00:51:31+00 | 2020-10-04 15:00:47.024474+00 | latest | 2020-10-04 15:03:19.615638+00
12 | 2021-02-10 17:00:13.20563+00 | 2020-10-02 04:35:39+00 | 2020-10-04 15:00:52.178755+00 | quarterly | 2020-10-04 15:03:29.339387+00
4 | 2021-02-10 17:00:13.20563+00 | 2021-02-08 20:27:27+00 | 2021-02-09 03:00:16.235524+00 | latest | 2021-02-09 03:00:22.4292+00
4 | 2021-02-10 17:00:13.20563+00 | 2021-02-09 05:09:37+00 | 2021-02-09 07:00:19.26035+00 | quarterly | 2021-02-09 07:00:34.037177+00
13 | 2021-02-10 17:00:13.20563+00 | 2016-07-18 00:33:40+00 | 2020-10-04 15:01:15.285386+00 | latest | 2020-10-04 15:04:03.027721+00
13 | 2021-02-10 17:00:13.20563+00 | 2020-10-01 02:36:15+00 | 2020-10-04 15:01:19.911912+00 | quarterly | 2020-10-04 15:04:11.059194+00
14 | 2021-02-10 17:00:13.20563+00 | 2016-07-25 04:20:42+00 | 2020-10-04 15:01:24.426796+00 | latest | 2020-10-04 15:04:17.827801+00
14 | 2021-02-10 17:00:13.20563+00 | 2020-10-01 08:20:18+00 | 2021-02-04 11:00:15.371228+00 | quarterly | 2021-02-04 11:00:22.551944+00
9 | 2021-02-10 17:00:13.20563+00 | 2018-10-11 05:47:38+00 | 2020-10-04 15:01:35.379828+00 | latest | 2020-10-04 15:04:32.751276+00
9 | 2021-02-10 17:00:13.20563+00 | 2021-02-07 11:06:07+00 | 2021-02-07 13:00:14.959275+00 | quarterly | 2021-02-07 13:00:23.122086+00
1 | 2021-02-10 17:00:13.20563+00 | 2021-02-08 01:42:33+00 | 2021-02-09 21:00:17.873407+00 | latest | 2021-02-09 21:00:35.348509+00
1 | 2021-02-10 17:00:13.20563+00 | 2021-02-10 01:03:25+00 | 2021-02-10 11:21:25.104526+00 | quarterly | 2021-02-10 11:22:10.609837+00
15 | 2021-02-10 17:00:13.20563+00 | 2018-10-10 14:02:29+00 | 2020-10-04 15:02:04.197145+00 | latest | 2020-10-04 15:05:20.600131+00
15 | 2021-02-10 17:00:13.20563+00 | 2020-12-01 08:59:46+00 | 2020-12-01 10:00:28.46244+00 | quarterly | 2020-12-01 10:01:00.107119+00
16 | 2021-02-10 17:00:13.20563+00 | 2018-10-15 02:25:58+00 | 2020-07-17 14:00:24.261982+00 | latest | 2020-07-17 14:03:44.976904+00
16 | 2021-02-10 17:00:13.20563+00 | 2020-12-01 08:43:59+00 | 2020-12-01 10:00:34.626725+00 | quarterly | 2020-12-01 10:01:13.981269+00
8 | 2021-02-10 17:00:13.20563+00 | 2021-02-07 08:09:21+00 | 2021-02-08 22:00:15.707564+00 | latest | 2021-02-08 22:00:29.591642+00
(60 rows)
This is a good example of how a result set is not ordered. Those rows without any timestamps, those are our new rows. The abi_id column contains duplicate values because there is one for quarterly (e.g. 2021Q1) and one for latest (i.e. head).
Now I wait and watch the logs at the top of the hour.
later
I checked the logs, reproduced here with formatting to ease reading:
Feb 10 18:00:16 dev-ingress01 check_repos_for_new_stuff.py[69136]: There are 5 new repos ready for import: [ 'FreeBSD:14:amd64/latest : Sun, 07 Feb 2021 06:15:49 GMT', 'FreeBSD:14:armv6/latest : Thu, 04 Feb 2021 01:25:36 GMT', 'FreeBSD:14:armv7/latest : Fri, 05 Feb 2021 11:48:09 GMT', 'FreeBSD:14:i386/latest : Sun, 07 Feb 2021 06:44:35 GMT', 'FreeBSD:14:mips64/latest : Sun, 07 Feb 2021 10:37:05 GMT' ]
Looking at the tables, with slightly different SQL, I found:
freshports.dev=# select abi.name, packages_last_checked.* from abi, packages_last_checked where abi.id = packages_last_checked.abi_id and abi.name like 'FreeBSD:14:%' order by name;
name | abi_id | last_checked | repo_date | import_date | package_set | processed_date
----------------------+--------+------------------------------+------------------------+-------------------------------+-------------+-------------------------------
FreeBSD:14:aarch64 | 25 | 2021-02-10 18:00:10.09146+00 | | | quarterly |
FreeBSD:14:aarch64 | 25 | 2021-02-10 18:00:10.09146+00 | | | latest |
FreeBSD:14:amd64 | 26 | 2021-02-10 18:00:10.09146+00 | 2021-02-07 06:15:49+00 | 2021-02-10 18:00:17.632469+00 | latest | 2021-02-10 18:00:39.25916+00
FreeBSD:14:amd64 | 26 | 2021-02-10 18:00:10.09146+00 | | | quarterly |
FreeBSD:14:armv6 | 27 | 2021-02-10 18:00:10.09146+00 | | | quarterly |
FreeBSD:14:armv6 | 27 | 2021-02-10 18:00:10.09146+00 | 2021-02-04 01:25:36+00 | 2021-02-10 18:00:23.779767+00 | latest | 2021-02-10 18:00:53.136252+00
FreeBSD:14:armv7 | 28 | 2021-02-10 18:00:10.09146+00 | 2021-02-05 11:48:09+00 | 2021-02-10 18:00:27.640483+00 | latest | 2021-02-10 18:00:59.62607+00
FreeBSD:14:armv7 | 28 | 2021-02-10 18:00:10.09146+00 | | | quarterly |
FreeBSD:14:i386 | 29 | 2021-02-10 18:00:10.09146+00 | 2021-02-07 06:44:35+00 | 2021-02-10 18:00:31.501863+00 | latest | 2021-02-10 18:01:06.671366+00
FreeBSD:14:i386 | 29 | 2021-02-10 18:00:10.09146+00 | | | quarterly |
FreeBSD:14:mips | 30 | 2021-02-10 18:00:10.09146+00 | | | quarterly |
FreeBSD:14:mips | 30 | 2021-02-10 18:00:10.09146+00 | | | latest |
FreeBSD:14:mips64 | 31 | 2021-02-10 18:00:10.09146+00 | 2021-02-07 10:37:05+00 | 2021-02-10 18:00:36.896226+00 | latest | 2021-02-10 18:01:15.138216+00
FreeBSD:14:mips64 | 31 | 2021-02-10 18:00:10.09146+00 | | | quarterly |
FreeBSD:14:powerpc64 | 32 | 2021-02-10 18:00:10.09146+00 | | | latest |
FreeBSD:14:powerpc64 | 32 | 2021-02-10 18:00:10.09146+00 | | | quarterly |
(16 rows)
freshports.dev=#
There you go. That’s how difficult it is to add new ABI.
