When setting up a new FreshPorts ingress node, the repositories must be configured with tags so FreshPorts knows the last commit it has processed. This is the starting point each time FreshPorts goes to search for new commits.
This post about Processing commits on branches with git might help with background.
Those tags must be set or FreshPorts will not extract any commits. It is OK to process commits a second time; they will be ignored and the existing commit in the database will not be affected.
But first, the repos, we’ll come back to them
For processing incoming commits, these are the repos used:
[dev-ingress01 dan ~ingress/repos] % ls -l total 8 drwxr-xr-x 7 ingress ingress 11 2021.06.05 18:51 doc/ drwxr-xr-x 70 ingress ingress 81 2022.12.05 15:20 ports/ drwxr-xr-x 27 ingress ingress 44 2021.06.27 01:00 src/
There is another repo at /jails/freshports/usr/ports but that is used for extracting commit details and is not used to store FreshPorts tags.
Grabbing dates from the database
freshports.org=# select * from repo order by name, repo_hostname; id | name | description | repo_hostname | path_to_repo | repository | db_root_prefix ----+-------+------------------------+--------------------+--------------+------------+---------------- 8 | doc | The FreeBSD doc tree | cgit.freebsd.org | /doc | git | /doc 2 | doc | The FreeBSD doc tree | svnweb.freebsd.org | /doc | subversion | 7 | ports | The FreeBSD Ports tree | cgit.freebsd.org | /ports | git | /ports 1 | ports | The FreeBSD Ports tree | svnweb.freebsd.org | /ports | subversion | 9 | src | The FreeBSD src tree | cgit.freebsd.org | /src | git | /base 3 | src | The FreeBSD src tree | svnweb.freebsd.org | /base | subversion | (6 rows)
We are dealing with git so we are only interested in lines 4,7, and 9.
What are the most recents commits for those repos?
freshports.org=# select max(commit_date), repo_id from commit_log where repo_id in
(select id from repo where repository = 'git') group by repo_id order by repo_id;
max | repo_id
------------------------+---------
2022-12-16 01:44:49+00 | 7
2022-12-15 19:59:09+00 | 8
2022-12-15 20:06:26+00 | 9
(3 rows)
I am typing this on 2023-01-02 and working with a copy of production from mid-December.
Let’s get the commit hashes for those commit dates:
freshports.org=# select commit_date, message_id from commit_log where commit_date in (select max(commit_date)
from commit_log where repo_id in (select id from repo where repository = 'git')
group by repo_id order by repo_id);
commit_date | message_id
------------------------+------------------------------------------
2022-12-15 19:59:09+00 | 54313f16d8a8f9113b1cc1596159d8fd602fc8c3
2022-12-15 20:06:26+00 | 69542f26820b7edb8351398b36edda5299c1db56
2022-12-16 01:44:49+00 | 47cc96f7fe91bd63dbe6e46026161454d9e59f34
(3 rows)
But that’s quite enough. We also process commits on branches. Let’s get those:
freshports.org=# select commit_date, message_id, branch_name from commit_log CL
join commit_log_branches CLB
on CL.id = CLB.commit_log_id
join system_branch SB on SB.id = CLB.branch_id
where commit_date in (select max(commit_date) from commit_log CL join commit_log_branches CLB
on CL.id = CLB.commit_log_id
join system_branch SB on SB.id = CLB.branch_id
where repo_id in (select id from repo where repository = 'git') group by repo_id, branch_name order by repo_id, branch_name) order by branch_name;
commit_date | message_id | branch_name
------------------------+----------------------------------------------+-------------
2014-03-29 12:14:26+00 | 5f4d6e1d6b07bb34e5cbf866b4ef81e8ccdcb2da | 2014Q1
2014-06-28 13:14:00+00 | a3377806e58e030668a0406a35c434394a9333e1 | 2014Q2
2014-10-01 03:35:40+00 | a0ccd6f83108ca7e16e64b6fd690dacefc5d0d58 | 2014Q3
2015-11-15 21:14:57+00 | ce9f5d32567600a981517b7e35b2542a1114ece0 | 2014Q4
2015-04-09 19:27:31+00 | 5bd325869bdeccff8037a64e2089b799abe798e0 | 2015Q1
2015-07-23 16:24:25+00 | 7d7c2271f6c957574221e8746e5a356435cd114f | 2015Q2
2015-10-03 18:26:21+00 | a69940fb27f798fd49cfecb02cac3785412b76d0 | 2015Q3
2015-12-30 10:20:19+00 | 38269ac51865d5d9bbaa23f6afcc2e6a6db5e9b1 | 2015Q4
2016-03-31 18:39:28+00 | 847fef4d2acd28d745c9fe57194624dafc02e927 | 2016Q1
2016-06-30 22:36:53+00 | 859c6d655ba8ec51549be3d8f52a302e7023d4ad | 2016Q2
2016-10-01 17:37:07+00 | dbe3dbb507e6a2068dee12ee7844edc3797e7dea | 2016Q3
2017-01-06 02:43:17+00 | 201701060243.v062hHhC027394@repo.freebsd.org | 2016Q4
2017-01-06 02:43:17+00 | e113cbca02fc10dfb61a77d9c547550718cc7190 | 2016Q4
2017-04-01 10:09:24+00 | 995d6409b4234b211b27bd14989a9c467f4d4b1a | 2017Q1
2017-04-01 10:09:24+00 | 201704011009.v31A9Oao076062@repo.freebsd.org | 2017Q1
2017-07-20 09:21:03+00 | ee7b50bc8addebc46b27f99488b0f2c3a2ef6b94 | 2017Q2
2017-07-20 09:21:03+00 | 201707200921.v6K9L3bp039228@repo.freebsd.org | 2017Q2
2017-10-08 13:11:17+00 | a16d9e9f97932862f4433c03ee35493ec26fd29d | 2017Q3
2018-01-02 23:31:56+00 | f204b02d6dcefb91393ebeac39b3b6741b9ae5b1 | 2017Q4
2018-06-10 10:30:41+00 | 8e67d346991ca584ffbe6ad23380c597e10d0ddf | 2018Q1
2018-07-01 14:06:28+00 | 06d6d83e775282797de36344c649a2c33a70861d | 2018Q2
2018-07-01 14:06:28+00 | 201807011406.w61E6SnC004420@repo.freebsd.org | 2018Q2
2018-10-01 01:59:34+00 | 20f7d7640bc5014692574a4e09a7191dc021ecce | 2018Q3
2019-01-01 00:12:01+00 | 6948b132a446b2a84e021a6dd26b5ac7ea8e575a | 2018Q4
2019-03-30 22:06:36+00 | d2f5722e633ae238daba15792d4949b15d22c174 | 2019Q1
2019-07-03 23:51:34+00 | ab797ee3a93f4bb4a3b22900257b9cbd91349887 | 2019Q2
2019-10-01 06:00:59+00 | 201910010600.x9160xTh022892@repo.freebsd.org | 2019Q3
2019-10-01 06:00:59+00 | d5d60820925216fc48c025190e0c30a79f52bf3b | 2019Q3
2019-12-31 16:06:20+00 | 201912311606.xBVG6K5g046485@repo.freebsd.org | 2019Q4
2019-12-31 16:06:20+00 | f24745656dbbc619a6ee0360a5cababea241813c | 2019Q4
2020-03-31 17:46:10+00 | 28b4da349ddcc9e8493e8c3d013eb2fc4107098a | 2020Q1
2020-03-31 17:46:10+00 | 202003311746.02VHkAWa065169@repo.freebsd.org | 2020Q1
2020-07-02 02:57:44+00 | 202007020257.0622viW4017065@repo.freebsd.org | 2020Q2
2020-07-02 02:57:44+00 | 22e399d695717c14566f60255c72aae4c8c3d08c | 2020Q2
2020-09-30 21:44:10+00 | c0d44897151cceb0c4e63f2f7763b030f3019b66 | 2020Q3
2021-01-03 03:56:17+00 | 202101030356.1033uHBj074526@repo.freebsd.org | 2020Q4
2021-01-03 03:56:17+00 | c9104cb760e6d462d2e0a768cbbf0d95968012fa | 2020Q4
2021-04-10 12:53:34+00 | df5930d0c9fef97d429b0a43dad7d2168061b224 | 2021Q1
2021-07-03 10:01:24+00 | d1da14bab7a800be62786aeb321b781179ea8b3f | 2021Q2
2021-10-03 00:38:11+00 | 5d6eb9d394c16d3e3ff4816090470feb64b39239 | 2021Q3
2022-01-01 07:21:16+00 | 9f533ce8386ddc4b43d8b178e7c60f393f919a31 | 2021Q4
2022-04-01 01:19:29+00 | 2c3e2897e25c082c3aade1f48af806a3aaf3839b | 2022Q1
2022-07-02 00:34:44+00 | ecc6a37c0d3d10f3a9212be604f70f720cf4b9d6 | 2022Q2
2022-09-30 21:21:15+00 | bf76dbbb8eab3e8022705a7a56250b01b58f6d2f | 2022Q3
2022-12-15 17:48:39+00 | 3194a15871c26b6995584fcb018c2d3a2d0e3858 | 2022Q4
2022-12-16 01:44:49+00 | 47cc96f7fe91bd63dbe6e46026161454d9e59f34 | head
2022-12-15 19:59:09+00 | 54313f16d8a8f9113b1cc1596159d8fd602fc8c3 | head
2022-12-15 20:06:26+00 | 69542f26820b7edb8351398b36edda5299c1db56 | head
2021-04-06 15:32:13+00 | 713410cb2ca0a7dcdfd00fab203a16a0515b596f | main
2021-04-06 15:15:04+00 | f2f1ab39c04088ce53287528549e652cf68cee09 | main
(50 rows)
freshports.org=#
We have some duplicate commits, where we have both the subversion and the git versions of a commit. The subversion commits have message_id ending in freebsd.org. This command removes the subversion commits and displays the repo name:
freshports.testgit=# select commit_date, message_id, branch_name, R.name from commit_log CL
join commit_log_branches CLB
on CL.id = CLB.commit_log_id
join system_branch SB on SB.id = CLB.branch_id
join repo R on CL.repo_id = R.id
where commit_date in (select max(commit_date) from commit_log CL join commit_log_branches CLB
on CL.id = CLB.commit_log_id
join system_branch SB on SB.id = CLB.branch_id
where repo_id in (select id from repo where repository = 'git') group by repo_id, branch_name order by repo_id, branch_name) and message_id not like '%repo.freebsd.org' order by branch_name;
commit_date | message_id | branch_name | name
------------------------+------------------------------------------+-------------+-------
2014-03-29 12:14:26+00 | 5f4d6e1d6b07bb34e5cbf866b4ef81e8ccdcb2da | 2014Q1 | ports
2014-06-28 13:14:00+00 | a3377806e58e030668a0406a35c434394a9333e1 | 2014Q2 | ports
2014-10-01 03:35:40+00 | a0ccd6f83108ca7e16e64b6fd690dacefc5d0d58 | 2014Q3 | ports
2015-11-15 21:14:57+00 | ce9f5d32567600a981517b7e35b2542a1114ece0 | 2014Q4 | ports
2015-04-09 19:27:31+00 | 5bd325869bdeccff8037a64e2089b799abe798e0 | 2015Q1 | ports
2015-07-23 16:24:25+00 | 7d7c2271f6c957574221e8746e5a356435cd114f | 2015Q2 | ports
2015-10-03 18:26:21+00 | a69940fb27f798fd49cfecb02cac3785412b76d0 | 2015Q3 | ports
2015-12-30 10:20:19+00 | 38269ac51865d5d9bbaa23f6afcc2e6a6db5e9b1 | 2015Q4 | ports
2016-03-31 18:39:28+00 | 847fef4d2acd28d745c9fe57194624dafc02e927 | 2016Q1 | ports
2016-06-30 22:36:53+00 | 859c6d655ba8ec51549be3d8f52a302e7023d4ad | 2016Q2 | ports
2016-10-01 17:37:07+00 | dbe3dbb507e6a2068dee12ee7844edc3797e7dea | 2016Q3 | ports
2017-01-06 02:43:17+00 | e113cbca02fc10dfb61a77d9c547550718cc7190 | 2016Q4 | ports
2017-04-01 10:09:24+00 | 995d6409b4234b211b27bd14989a9c467f4d4b1a | 2017Q1 | ports
2017-07-20 09:21:03+00 | ee7b50bc8addebc46b27f99488b0f2c3a2ef6b94 | 2017Q2 | ports
2017-10-08 13:11:17+00 | a16d9e9f97932862f4433c03ee35493ec26fd29d | 2017Q3 | ports
2018-01-02 23:31:56+00 | f204b02d6dcefb91393ebeac39b3b6741b9ae5b1 | 2017Q4 | ports
2018-06-10 10:30:41+00 | 8e67d346991ca584ffbe6ad23380c597e10d0ddf | 2018Q1 | ports
2018-07-01 14:06:28+00 | 06d6d83e775282797de36344c649a2c33a70861d | 2018Q2 | ports
2018-10-01 01:59:34+00 | 20f7d7640bc5014692574a4e09a7191dc021ecce | 2018Q3 | ports
2019-01-01 00:12:01+00 | 6948b132a446b2a84e021a6dd26b5ac7ea8e575a | 2018Q4 | ports
2019-03-30 22:06:36+00 | d2f5722e633ae238daba15792d4949b15d22c174 | 2019Q1 | ports
2019-07-03 23:51:34+00 | ab797ee3a93f4bb4a3b22900257b9cbd91349887 | 2019Q2 | ports
2019-10-01 06:00:59+00 | d5d60820925216fc48c025190e0c30a79f52bf3b | 2019Q3 | ports
2019-12-31 16:06:20+00 | f24745656dbbc619a6ee0360a5cababea241813c | 2019Q4 | ports
2020-03-31 17:46:10+00 | 28b4da349ddcc9e8493e8c3d013eb2fc4107098a | 2020Q1 | ports
2020-07-02 02:57:44+00 | 22e399d695717c14566f60255c72aae4c8c3d08c | 2020Q2 | ports
2020-09-30 21:44:10+00 | c0d44897151cceb0c4e63f2f7763b030f3019b66 | 2020Q3 | ports
2021-01-03 03:56:17+00 | c9104cb760e6d462d2e0a768cbbf0d95968012fa | 2020Q4 | ports
2021-04-10 12:53:34+00 | df5930d0c9fef97d429b0a43dad7d2168061b224 | 2021Q1 | ports
2021-07-03 10:01:24+00 | d1da14bab7a800be62786aeb321b781179ea8b3f | 2021Q2 | ports
2021-10-03 00:38:11+00 | 5d6eb9d394c16d3e3ff4816090470feb64b39239 | 2021Q3 | ports
2022-01-01 07:21:16+00 | 9f533ce8386ddc4b43d8b178e7c60f393f919a31 | 2021Q4 | ports
2022-04-01 01:19:29+00 | 2c3e2897e25c082c3aade1f48af806a3aaf3839b | 2022Q1 | ports
2022-07-02 00:34:44+00 | ecc6a37c0d3d10f3a9212be604f70f720cf4b9d6 | 2022Q2 | ports
2022-09-30 21:21:15+00 | bf76dbbb8eab3e8022705a7a56250b01b58f6d2f | 2022Q3 | ports
2022-12-31 00:28:03+00 | 78bbde9c877db24e936f3cdd9c371de2dae4ffcc | 2022Q4 | ports
2023-01-02 04:17:27+00 | a1f28ec729f7491da8607e8eeaee1b0f547c60d0 | head | src
2023-01-02 16:37:13+00 | 06c71031f8cff7d40944080acd69355df8b22f8d | head | ports
2023-01-02 01:37:05+00 | 1a4668dc587f92c4a37bf5897fd9a43954d8b48e | head | doc
2021-04-06 20:54:18+00 | 2c1a6b3977da8446ba2232eb0d86fe713cbba65a | main | doc
2021-04-06 21:27:16+00 | ac503c194cd8e9dbef5c120e87f9521e1a89003a | main | src
(41 rows)
freshports.testgit=#
What is the objective?
What I want is commands like this:
git tag -m 'last known commit of origin/main' -f freshports/origin/main 47cc96f7fe91bd63dbe6e46026161454d9e59f34
This should accomplish that:
freshports.org=# select 'git tag -m ''last known commit of freshports/origin/' || branch_name || ''' -f freshports/origin/' || branch_name || ' ' || message_id from commit_log CL
join commit_log_branches CLB
on CL.id = CLB.commit_log_id
join system_branch SB on SB.id = CLB.branch_id
join repo R on CL.repo_id = R.id
where commit_date in (select max(commit_date) from commit_log CL join commit_log_branches CLB
on CL.id = CLB.commit_log_id
join system_branch SB on SB.id = CLB.branch_id
where repo_id in (select id from repo where repository = 'git') group by repo_id, branch_name order by repo_id, branch_name) and message_id not like '%repo.freebsd.org' order by branch_name;
?column?
---------------------------------------------------------------------------------------------------------------------------------
git tag -m 'last known commit of freshports/origin/2014Q1' -f freshports/origin/2014Q1 5f4d6e1d6b07bb34e5cbf866b4ef81e8ccdcb2da
git tag -m 'last known commit of freshports/origin/2014Q2' -f freshports/origin/2014Q2 a3377806e58e030668a0406a35c434394a9333e1
git tag -m 'last known commit of freshports/origin/2014Q3' -f freshports/origin/2014Q3 a0ccd6f83108ca7e16e64b6fd690dacefc5d0d58
git tag -m 'last known commit of freshports/origin/2014Q4' -f freshports/origin/2014Q4 ce9f5d32567600a981517b7e35b2542a1114ece0
git tag -m 'last known commit of freshports/origin/2015Q1' -f freshports/origin/2015Q1 5bd325869bdeccff8037a64e2089b799abe798e0
git tag -m 'last known commit of freshports/origin/2015Q2' -f freshports/origin/2015Q2 7d7c2271f6c957574221e8746e5a356435cd114f
git tag -m 'last known commit of freshports/origin/2015Q3' -f freshports/origin/2015Q3 a69940fb27f798fd49cfecb02cac3785412b76d0
git tag -m 'last known commit of freshports/origin/2015Q4' -f freshports/origin/2015Q4 38269ac51865d5d9bbaa23f6afcc2e6a6db5e9b1
git tag -m 'last known commit of freshports/origin/2016Q1' -f freshports/origin/2016Q1 847fef4d2acd28d745c9fe57194624dafc02e927
git tag -m 'last known commit of freshports/origin/2016Q2' -f freshports/origin/2016Q2 859c6d655ba8ec51549be3d8f52a302e7023d4ad
git tag -m 'last known commit of freshports/origin/2016Q3' -f freshports/origin/2016Q3 dbe3dbb507e6a2068dee12ee7844edc3797e7dea
git tag -m 'last known commit of freshports/origin/2016Q4' -f freshports/origin/2016Q4 e113cbca02fc10dfb61a77d9c547550718cc7190
git tag -m 'last known commit of freshports/origin/2017Q1' -f freshports/origin/2017Q1 995d6409b4234b211b27bd14989a9c467f4d4b1a
git tag -m 'last known commit of freshports/origin/2017Q2' -f freshports/origin/2017Q2 ee7b50bc8addebc46b27f99488b0f2c3a2ef6b94
git tag -m 'last known commit of freshports/origin/2017Q3' -f freshports/origin/2017Q3 a16d9e9f97932862f4433c03ee35493ec26fd29d
git tag -m 'last known commit of freshports/origin/2017Q4' -f freshports/origin/2017Q4 f204b02d6dcefb91393ebeac39b3b6741b9ae5b1
git tag -m 'last known commit of freshports/origin/2018Q1' -f freshports/origin/2018Q1 8e67d346991ca584ffbe6ad23380c597e10d0ddf
git tag -m 'last known commit of freshports/origin/2018Q2' -f freshports/origin/2018Q2 06d6d83e775282797de36344c649a2c33a70861d
git tag -m 'last known commit of freshports/origin/2018Q3' -f freshports/origin/2018Q3 20f7d7640bc5014692574a4e09a7191dc021ecce
git tag -m 'last known commit of freshports/origin/2018Q4' -f freshports/origin/2018Q4 6948b132a446b2a84e021a6dd26b5ac7ea8e575a
git tag -m 'last known commit of freshports/origin/2019Q1' -f freshports/origin/2019Q1 d2f5722e633ae238daba15792d4949b15d22c174
git tag -m 'last known commit of freshports/origin/2019Q2' -f freshports/origin/2019Q2 ab797ee3a93f4bb4a3b22900257b9cbd91349887
git tag -m 'last known commit of freshports/origin/2019Q3' -f freshports/origin/2019Q3 d5d60820925216fc48c025190e0c30a79f52bf3b
git tag -m 'last known commit of freshports/origin/2019Q4' -f freshports/origin/2019Q4 f24745656dbbc619a6ee0360a5cababea241813c
git tag -m 'last known commit of freshports/origin/2020Q1' -f freshports/origin/2020Q1 28b4da349ddcc9e8493e8c3d013eb2fc4107098a
git tag -m 'last known commit of freshports/origin/2020Q2' -f freshports/origin/2020Q2 22e399d695717c14566f60255c72aae4c8c3d08c
git tag -m 'last known commit of freshports/origin/2020Q3' -f freshports/origin/2020Q3 c0d44897151cceb0c4e63f2f7763b030f3019b66
git tag -m 'last known commit of freshports/origin/2020Q4' -f freshports/origin/2020Q4 c9104cb760e6d462d2e0a768cbbf0d95968012fa
git tag -m 'last known commit of freshports/origin/2021Q1' -f freshports/origin/2021Q1 df5930d0c9fef97d429b0a43dad7d2168061b224
git tag -m 'last known commit of freshports/origin/2021Q2' -f freshports/origin/2021Q2 d1da14bab7a800be62786aeb321b781179ea8b3f
git tag -m 'last known commit of freshports/origin/2021Q3' -f freshports/origin/2021Q3 5d6eb9d394c16d3e3ff4816090470feb64b39239
git tag -m 'last known commit of freshports/origin/2021Q4' -f freshports/origin/2021Q4 9f533ce8386ddc4b43d8b178e7c60f393f919a31
git tag -m 'last known commit of freshports/origin/2022Q1' -f freshports/origin/2022Q1 2c3e2897e25c082c3aade1f48af806a3aaf3839b
git tag -m 'last known commit of freshports/origin/2022Q2' -f freshports/origin/2022Q2 ecc6a37c0d3d10f3a9212be604f70f720cf4b9d6
git tag -m 'last known commit of freshports/origin/2022Q3' -f freshports/origin/2022Q3 bf76dbbb8eab3e8022705a7a56250b01b58f6d2f
git tag -m 'last known commit of freshports/origin/2022Q4' -f freshports/origin/2022Q4 3194a15871c26b6995584fcb018c2d3a2d0e3858
git tag -m 'last known commit of freshports/origin/head' -f freshports/origin/head 54313f16d8a8f9113b1cc1596159d8fd602fc8c3
git tag -m 'last known commit of freshports/origin/head' -f freshports/origin/head 47cc96f7fe91bd63dbe6e46026161454d9e59f34
git tag -m 'last known commit of freshports/origin/head' -f freshports/origin/head 69542f26820b7edb8351398b36edda5299c1db56
git tag -m 'last known commit of freshports/origin/main' -f freshports/origin/main 713410cb2ca0a7dcdfd00fab203a16a0515b596f
git tag -m 'last known commit of freshports/origin/main' -f freshports/origin/main f2f1ab39c04088ce53287528549e652cf68cee09
(41 rows)
freshports.org=#
That might just work.
