Rerunning the filesystems commit to create the new category

I’ve updated the code (look for pseduo code in Welcome to the new category: filesystems) and now I’m ready to test that code.

I have a copy of the database from before the commit. I’ve configured my test environment I can rollback the filesystem and get to a known good point.

Am I doing this right?

Just before I started typing this, I noticed a flaw in my testing approach.

Every commit processing starts with a list of current categories. It is obtained by this script:

[16:01 dvl-ingress01 dvl ~/scripts] % ./get-list-of-current-categories.sh  
accessibility arabic archivers astro audio benchmarks biology cad chinese comms converters databases deskutils devel distfiles dns editors 
emulators finance french ftp games german graphics hebrew hungarian irc japanese java korean lang mail math misc multimedia net net-im net-mgmt 
net-p2p news polish ports-mgmt portuguese print russian science security shells sysutils textproc ukrainian vietnamese www x11 x11-clocks 
x11-drivers x11-fm x11-fonts x11-servers x11-themes x11-toolkits x11-wm

NOTE: the output has been manually wrapped so you can read it easier. Normally, that’s all on one line.

Note that filesystems does not appear in the above output. That is critical to this testing.

Confirm my local ports tree

Checking the ports tree in question (I’m on the host/jail dvl-ingress01, and the ports tree is in a jail called freshports (a jail within a jail)).

[16:04 dvl-ingress01 dvl /jails/freshports/usr/ports] % git status                                                          
HEAD detached at 1b19391b2c14
nothing to commit, working tree clean
[16:04 dvl-ingress01 dvl /jails/freshports/usr/ports] % git log
commit 1b19391b2c147986b510fb883671b44f1ddd98e2 (HEAD, origin/2024Q4)
Author: Dirk Meyer 
Date:   Fri Nov 8 18:48:33 2024 +0100

    net/x11vnc: patch CVE-2020-29074
    
    Security: CVE-2020-29074
    MFC: 2024Q4
    (cherry picked from commit 78dcb38f4af7e4b50df83976aefb81596eaafc80)

commit e86680e50289016b45ccadfb854b1af204b1088a
Author: Dirk Meyer 
Date:   Fri Nov 8 18:45:36 2024 +0100

    comms/lrzsz: patch CVE-2018-10195
    
    Security: CVE-2018-10195
    MFC: 2024Q4
    (cherry picked from commit a2e118fa2128c7cb7f1bf961c62ac4f4ce809a0c)

That’s not at the right point for me.

I am sure I want the commit right before the filesystems category is created.

Finding the right commit

Looking at this page in github, I want the math/sdpa: speed up build commit.

This might be easier to read on this FreshPorts page for 2024-11-06

The commit hash is 432f2ebe088377708fb08514ea86f2c19a5b0ad5

Repositioning the tree

First, I check the branch

[16:05 dvl-ingress01 dvl /jails/freshports/usr/ports] % git branch
* (HEAD detached at 1b19391b2c14)
  main

That seems OK, let’s fetch:

[16:05 dvl-ingress01 dvl /jails/freshports/usr/ports] % git fetch
error: cannot open '.git/FETCH_HEAD': Permission denied

Oh yes, ports tree manipulation is only done by root. The freshports user uses sudo to operate on the tree.

[16:10 dvl-ingress01 dvl /jails/freshports/usr/ports] % sudo git fetch
remote: Enumerating objects: 2078, done.
remote: Counting objects: 100% (1674/1674), done.
remote: Compressing objects: 100% (722/722), done.
remote: Total 943 (delta 517), reused 372 (delta 216), pack-reused 0
Receiving objects: 100% (943/943), 167.58 KiB | 3.16 MiB/s, done.
Resolving deltas: 100% (517/517), completed with 323 local objects.
From https://git.FreeBSD.org/ports
   1b19391b2c14..048072727661  2024Q4     -> origin/2024Q4
   0b6f281593d6..a9fd6f36b402  main       -> origin/main

This checks out the tree to the required commit:

[16:10 dvl-ingress01 dvl /jails/freshports/usr/ports] % sudo git checkout 432f2ebe088377708fb08514ea86f2c19a5b0ad5
Updating files: 100% (6569/6569), done.
Previous HEAD position was 1b19391b2c14 net/x11vnc: patch CVE-2020-29074
HEAD is now at 432f2ebe0883 math/sdpa: speed up build
[16:11 dvl-ingress01 dvl /jails/freshports/usr/ports] % git status
HEAD detached at 432f2ebe0883
nothing to commit, working tree clean
[16:11 dvl-ingress01 dvl /jails/freshports/usr/ports] %

Still no filesystems category

One last test:

[16:15 dvl-ingress01 dvl ~/scripts] % sh ./get-list-of-current-categories.sh                              
accessibility arabic archivers astro audio benchmarks biology cad chinese comms converters databases deskutils devel distfiles dns editors emulators finance 
french ftp games german graphics hebrew hungarian irc japanese java korean lang mail math misc multimedia net net-im net-mgmt net-p2p news polish ports-mgmt 
portuguese print russian science security shells sysutils textproc ukrainian vietnamese www x11 x11-clocks x11-drivers x11-fm x11-fonts x11-servers x11-themes 
x11-toolkits x11-wm

Still looks good.

Start the test

This command places the processed commit back into the incoming queue:

[16:19 dvl-ingress01 dvl ~] % cd /var/db/freshports/message-queues/recent
[16:19 dvl-ingress01 dvl ~freshports/message-queues/recent] % 
[16:19 dvl-ingress01 dvl ~freshports/message-queues/recent] % sudo mv -i 2024.11.06.15.17.35.000008.6e2da9672f79f44048d597f0f61e4646cdeade9d.xml ~ingress/message-queues/incoming
[16:19 dvl-ingress01 dvl ~freshports/message-queues/recent] % ls ~ingress/message-queues/incoming                                 
2024.11.06.15.17.35.000008.6e2da9672f79f44048d597f0f61e4646cdeade9d.xml

It is the only commit in the queue.

Queue processing is initiated with this command:

[16:20 dvl-ingress01 dvl ~] % sudo service freshports start
Cannot ‘start’ freshports. Set freshports_enable to YES in /etc/rc.conf or use ‘onestart’ instead of ‘start’.
[16:20 dvl-ingress01 dvl ~] % sudo service freshports onestart
Starting freshports.
[16:20 dvl-ingress01 dvl ~] %

I see failure. I see success.

Nov  9 16:21:14 dvl-ingress01 FreshPorts[64047]: creating new category Mk (/usr/local/libexec/freshports) 
Nov  9 16:21:14 dvl-ingress01 FreshPorts[64047]: creating new category filesystems (/usr/local/libexec/freshports)

However, the list of categories seems correct, given the above. See lines 4 and 28 below, with such a large value for element_id. This make sense.

freshports.dvl=# select * from categories order by name;
 id  | is_primary | element_id |       name       |                        description                         
-----+------------+------------+------------------+------------------------------------------------------------
 138 | t          |    1374669 | Mk               | 
  85 | t          |     171607 | accessibility    | Ports to help disabled users.
  64 | f          |            | afterstep        | Ports to support the AfterStep window manager.
  88 | t          |     159346 | arabic           | Ported software for the Arabic market.
  23 | t          |        350 | archivers        | Utilities for archiving and unarchiving data.
  26 | t          |        410 | astro            | Applications related to astronomy.
  25 | t          |        386 | audio            | Audio utilities - most require a supported sound card.
  42 | t          |       2710 | benchmarks       | Utilities for measuring system performance.
  36 | t          |        869 | biology          | Software related to biology.
 136 | f          |            | budgie           | no description supplied
  35 | t          |        830 | cad              | Computer Aided Design utilities.
  39 | t          |       1660 | chinese          | Ported software for the Chinese market.
  41 | t          |       2191 | comms            | Communications utilities.
  27 | t          |        423 | converters       | Format conversion utilities.
  32 | t          |        582 | databases        | Database software.
  33 | t          |        802 | deskutils        | Various Desktop utilities.
  10 | t          |         84 | devel            | Software development utilities and libraries.
  84 | t          |     148762 | dns              | DNS client and server utilities.
 118 | f          |            | docs             | no description supplied
   1 | t          |          2 | editors          | Common text editors.
 135 | f          |            | education        | no description supplied
  63 | f          |            | elisp            | Things related to Emacs Lisp.
  22 | t          |        245 | emulators        | Utilities for emulating other OS types.
 119 | f          |            | enlightenment    | no description supplied
 125 | f          |            | erlang           | no description supplied
 139 | t          |    1374670 | filesystems      | 
  54 | t          |     118514 | finance          | Monetary, financial and related applications.
  47 | t          |      16545 | french           | Ported software for French countries.
  13 | t          |        140 | ftp              | FTP client and server utilities.
   3 | t          |         18 | games            | Various and sundry amusements.
 115 | f          |            | geography        | Geography related ports.
  44 | t          |       3747 | german           | Ported software for Germanic countries.
  58 | f          |            | gnome            | Components of the Gnome Desktop environment.
 101 | f          |            | gnustep          | Software for GNUstep desktop environment.
   4 | t          |         29 | graphics         | Graphics libraries and utilities.
  96 | f          |            | hamradio         | Software for amateur radio.
  77 | f          |            | haskell          | Software related to the Haskell language.
  46 | t          |      11329 | hebrew           | Ported software for Hebrew language.
  51 | t          |     118517 | hungarian        | Ported software for the Hungarian market.
  62 | f          |            | ipv6             | IPv6 related software.
   6 | t          |         39 | irc              | Internet Relay Chat utilities.
  12 | t          |        129 | japanese         | Ported software for the Japanese market.
  34 | t          |        815 | java             | Java language support.
  55 | f          |            | kde              | Software for the K Desktop Environment.
 131 | f          |            | kde-applications | no description supplied
 137 | f          |            | kde-devel        | no description supplied
 129 | f          |            | kde-frameworks   | no description supplied
 128 | f          |            | kde-kde4         | no description supplied
 133 | f          |            | kde-plasma       | no description supplied
 114 | f          |            | kld              | Kernel loadable modules.
  37 | t          |       1109 | korean           | Ported software for the Korean market.
  15 | t          |        171 | lang             | Computer languages.
  66 | f          |            | linux            | Linux programs that can be run under binary compatibility.
  90 | f          |            | lisp             | Things related to pure lisp.
  19 | t          |        201 | mail             | Electronic mail packages and utilities.
 124 | f          |            | mate             | no description supplied
  16 | t          |        176 | math             | Mathematical computation software.
  45 | t          |       6412 | mbone            | Applications and utilities for the MBONE.
   7 | t          |         42 | misc             | Miscellaneous utilities.
  52 | t          |     118520 | multimedia       | Multimedia software.
   8 | t          |         50 | net              | Networking utilities.
  95 | t          |     229588 | net-im           | Instant messaging software.
  92 | t          |     173566 | net-mgmt         | Network management utilities.
  98 | t          |     236506 | net-p2p          | Peer to peer networking software.
 134 | f          |            | net-vpn          | no description supplied
  17 | t          |        179 | news             | USENET News support software.
  76 | f          |            | offix            | This is a virtual category. No description is available.
  40 | t          |       2143 | palm             | Software support for the Palm(tm) series.
  93 | f          |            | paralell         | This is a virtual category. No description is available.
  68 | f          |            | parallel         | Applications dealing with parallelism in computing.
  89 | f          |            | pear             | Utilities/modules that fall into the PEAR system.
  94 | f          |            | perl             | This is a virtual category. No description is available.
  59 | f          |            | perl5            | Utilities/modules for the PERL5 language.
  87 | f          |            | php              | This is a virtual category. No description is available.
  50 | t          |      58316 | picobsd          | Software support for the 3Com Palm(tm) series
  69 | f          |            | plan9            | Software from the Plan9 operating system.
  82 | t          |     148764 | polish           | Ported software for the Polish market.
 102 | t          |     265343 | ports-mgmt       | Utilities for managing ports and packages.
  53 | t          |     118523 | portuguese       | Ported software for the Portuguese market.
  24 | t          |        360 | print            | Utilities for dealing with printing.
  57 | f          |            | python           | Software related to the Python language.
 126 | f          |            | python:2.7       | no description supplied
  74 | f          |            | ruby             | Software related to the Ruby language.
  97 | f          |            | rubygems         | Packages for the RubyGems system.
  31 | t          |        577 | russian          | Ported software for the Russian market.
  83 | f          |            | scheme           | Software related to the Scheme language.
  48 | t          |      56065 | science          | Scientific software.
   5 | t          |         34 | security         | System security software.
  29 | t          |        465 | shells           | Various shells (tcsh, bash, etc).
 100 | f          |            | spanish          | Ported software for the Spanish market.
  20 | t          |        218 | sysutils         | Various system utilities.
 117 | f          |            | tcl              | no description supplied
  70 | f          |            | tcl80            | TCL v8.0 and packages which depend on it.
  72 | f          |            | tcl81            | This is a virtual category. No description is available.
  71 | f          |            | tcl82            | TCL v8.2 and packages which depend on it.
  60 | f          |            | tcl83            | TCL v8.3 and packages which depend on it.
  80 | f          |            | tcl84            | TCL v8.4 and packages which depend on it.
  18 | t          |        188 | textproc         | Text processing/search utilities.
 116 | f          |            | tk               | no description supplied
  78 | f          |            | tk42             | This is a virtual category. No description is available.
  73 | f          |            | tk80             | Tk8.0 and packages which depend on it.
  61 | f          |            | tk82             | Tk8.2 and packages which depend on it.
  65 | f          |            | tk83             | Tk8.3 and packages which depend on it.
  79 | f          |            | tk84             | Tk8.4 and packages which depend on it.
  75 | f          |            | tkstep80         | tkstep wm and packages which depend on it.
  49 | t          |      57265 | ukrainian        | Ported software for the Ukrainian market.
  11 | t          |         94 | vietnamese       | Ported software for the Vietnamese market.
 130 | f          |            | wayland          | no description supplied
  56 | f          |            | windowmaker      | Ports to support the WindowMaker window manager.
   2 | t          |          9 | www              | WEB utilities (browsers, HTTP servers, etc).
  21 | t          |        231 | x11              | X Window System based utilities.
  28 | t          |        428 | x11-clocks       | X Window System based clocks.
 113 | t          |     278044 | x11-drivers      | X Window System drivers.
  30 | t          |        516 | x11-fm           | X Window System based file managers.
  38 | t          |       1229 | x11-fonts        | X Window System fonts and font utilities.
  43 | t          |       3321 | x11-servers      | X Window System servers.
  91 | t          |     171611 | x11-themes       | X Window System themes.
   9 | t          |         55 | x11-toolkits     | X Window System based development toolkits.
  14 | t          |        147 | x11-wm           | X Window System window managers.
  81 | f          |            | xfce             | Ports to support the Xfce desktop environment.
  67 | f          |            | zope             | Software related to the Zope platform.
(121 rows)

This update didconvert Mk/Scripts into a port. Good thing I have zfs rollback, because I would not want to clean up this database by hand.

The new code added Mk/Scripts to the ports table.

This run also converted Mk/Uses into a port.

To be fair, if this was the first commit to Mk, how could FreshPorts know if it was a valid category? I think /usr/ports/Makefile has it:

[16:59 mydev dvl /usr/ports] % make -V SUBDIR    
accessibility arabic archivers astro audio benchmarks biology cad chinese comms converters databases deskutils devel dns editors emulators finance french 
filesystems ftp games german graphics hebrew hungarian irc japanese java korean lang mail math misc multimedia net net-im net-mgmt net-p2p news polish ports-mgmt 
portuguese print russian science security shells sysutils textproc ukrainian vietnamese www x11 x11-clocks x11-drivers x11-fm x11-fonts x11-servers x11-themes 
x11-toolkits x11-wm

Let’s not go with that solution. However, it may become the definitive source one day. Since typing the above, I’ve redone the code.

Perhaps:

The list of lower case directories in /usr/ports is the list of categories.
If we find the category in that list, we next check the database for that category. Create it if not found.
This means we need the ports tree updated for this commit before we start processing.
That change seems to be minor

I am sure that by the time we get into this particular code, the ports tree is checked out. Turns out, it was not. We were saving all the changes to the ports tree (creating new ports, marking them as deleted, etc) all without having the commit checked out.

Second test prep

To prepare for the test:

In the jail:

[18:17 pg02 dvl ~] % sudo service postgresql stop
[18:17 pg02 dvl ~] %

On the host:

[18:17 r730-01 dvl ~] % zfs list | grep pg02 
data02/jails/pg02                                                   14.6G   571G  13.1G  /jails/pg02
data03/pg02                                                         62.2G  6.00T    88K  none
data03/pg02/postgres                                                62.2G  6.00T  26.5G  /jails/pg02/var/db/postgres
data03/pg02/rsyncer                                                  872K  6.00T   128K  /jails/pg02/usr/home/rsyncer/backups

[18:17 r730-01 dvl ~] % zfs list -r -t snapshot data03/pg02/postgres | grep -v auto
NAME                                                           USED  AVAIL  REFER  MOUNTPOINT
data03/pg02/postgres@before-filesystems-category              54.5M      -  26.7G  -


[18:18 r730-01 dvl ~] % sudo zfs rollback data03/pg02/postgres@before-filesystems-category
cannot rollback to 'data03/pg02/postgres@before-filesystems-category': more recent snapshots or bookmarks exist
use '-r' to force deletion of the following snapshots and bookmarks:
data03/pg02/postgres@autosnap_2024-11-09_04:00:10_hourly
data03/pg02/postgres@autosnap_2024-11-09_16:00:05_hourly
data03/pg02/postgres@autosnap_2024-11-09_07:00:06_hourly
data03/pg02/postgres@autosnap_2024-11-09_06:00:12_hourly
data03/pg02/postgres@autosnap_2024-11-09_12:00:03_hourly
data03/pg02/postgres@autosnap_2024-11-08_23:00:04_hourly
data03/pg02/postgres@autosnap_2024-11-09_05:02:14_hourly
data03/pg02/postgres@autosnap_2024-11-08_22:00:09_hourly
data03/pg02/postgres@autosnap_2024-11-08_19:00:11_hourly
data03/pg02/postgres@autosnap_2024-11-09_02:00:02_hourly
data03/pg02/postgres@autosnap_2024-11-09_17:15:06_frequently
data03/pg02/postgres@autosnap_2024-11-09_16:45:04_frequently
data03/pg02/postgres@autosnap_2024-11-09_18:00:07_hourly
data03/pg02/postgres@autosnap_2024-11-09_17:45:05_frequently
data03/pg02/postgres@autosnap_2024-11-08_18:00:06_hourly
data03/pg02/postgres@autosnap_2024-11-08_21:00:05_hourly
data03/pg02/postgres@autosnap_2024-11-09_00:00:07_daily
data03/pg02/postgres@autosnap_2024-11-09_18:00:07_frequently
data03/pg02/postgres@autosnap_2024-11-09_17:30:02_frequently
data03/pg02/postgres@autosnap_2024-11-08_17:00:06_hourly
data03/pg02/postgres@autosnap_2024-11-09_08:00:03_hourly
data03/pg02/postgres@autosnap_2024-11-09_17:00:04_hourly
data03/pg02/postgres@autosnap_2024-11-08_16:00:06_hourly
data03/pg02/postgres@autosnap_2024-11-09_17:00:04_frequently
data03/pg02/postgres@autosnap_2024-11-08_20:00:01_hourly
data03/pg02/postgres@autosnap_2024-11-09_03:00:05_hourly
data03/pg02/postgres@autosnap_2024-11-09_13:00:06_hourly
data03/pg02/postgres@autosnap_2024-11-09_18:15:04_frequently
data03/pg02/postgres@autosnap_2024-11-09_00:00:07_hourly
data03/pg02/postgres@autosnap_2024-11-09_11:00:04_hourly
data03/pg02/postgres@autosnap_2024-11-09_10:00:04_hourly
data03/pg02/postgres@autosnap_2024-11-09_01:00:07_hourly
Output limited to 32 snapshots/bookmarks
[18:19 r730-01 dvl ~] % sudo zfs rollback -r data03/pg02/postgres@before-filesystems-category
[18:19 r730-01 dvl ~] %

Back on the database server jail:

[18:20 pg02 dvl ~] % sudo service postgresql start
2024-11-09 18:20:02.467 UTC [32564] LOG:  ending log output to stderr
2024-11-09 18:20:02.467 UTC [32564] HINT:  Future log output will go to log destination "syslog".
[18:23 pg02 dvl ~] % sudo su -l postgres        
$ psql -l
                                                               List of databases
              Name               |  Owner   | Encoding  | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges   
---------------------------------+----------+-----------+-----------------+---------+---------+------------+-----------+-----------------------
 freshports.dvl-category-testing | postgres | SQL_ASCII | libc            | C       | C.UTF-8 |            |           | 
 postgres                        | postgres | UTF8      | libc            | C       | C.UTF-8 |            |           | 
 template0                       | postgres | UTF8      | libc            | C       | C.UTF-8 |            |           | =c/postgres          +
                                 |          |           |                 |         |         |            |           | postgres=CTc/postgres
 template1                       | postgres | UTF8      | libc            | C       | C.UTF-8 |            |           | =c/postgres          +
                                 |          |           |                 |         |         |            |           | postgres=CTc/postgres
(4 rows)

$ psql 
psql (16.4)
Type "help" for help.

postgres=# alter database "freshports.dvl-category-testing" rename to "freshports.dvl";
ALTER DATABASE
postgres=#

Second test

[18:21 dvl-ingress01 dvl ~freshports/message-queues/recent] % sudo mv -i 2024.11.06.15.17.35.000008.6e2da9672f79f44048d597f0f61e4646cdeade9d.xml ~ingress/message-queues/incoming 
[18:22 dvl-ingress01 dvl ~freshports/message-queues/recent] %

That went smoothly. No rogue categories created.

Or was there.

I found this later:

freshports.dvl=# select *, element_pathname(id) from element where name = 'filesystems' order by element_pathname;
...
 1374669 | filesystems |    464086 | D                   | A      | /ports/filesystems
 1373780 | filesystems |    464087 | D                   | A      | /ports/head/filesystems
...

Only the latter should be created. The former is what the system created as a new category. The former is the actual category created. It’s wrong. /ports/head/filesystems is the real category. See blow.

freshports.dvl=# select * from elementGet('/ports/head/filesystems');
   id    |    name     | type | status | iscategory | isport |    element_pathname     
---------+-------------+------+--------+------------+--------+-------------------------
 1373780 | filesystems | D    | A      | f          | f      | /ports/head/filesystems
(1 row)

freshports.dvl=# select * from elementGet('/ports/filesystems');
   id    |    name     | type | status | iscategory | isport |  element_pathname  
---------+-------------+------+--------+------------+--------+--------------------
 1374669 | filesystems | D    | A      | t          | f      | /ports/filesystems
(1 row)

freshports.dvl=# select *, element_pathname(element_id) from categories where name = 'filesystems';
 id  | is_primary | element_id |    name     |            description             |  element_pathname  
-----+------------+------------+-------------+------------------------------------+--------------------
 138 | t          |    1374669 | filesystems | File systems and related utilities | /ports/filesystems
(1 row)

freshports.dvl=#

In that database, only one has iscategory set to true.

The fix I did for this:


freshports.dvl=# select * from categories where name = 'filesystems';
 id  | is_primary | element_id |    name     |            description             
-----+------------+------------+-------------+------------------------------------
 138 | t          |    1374669 | filesystems | File systems and related utilities
(1 row)

freshports.dvl=# begin;
BEGIN
freshports.dvl=*# 
freshports.dvl=*# update categories set element_id = 1373780 where id = 138;
UPDATE 1
freshports.dvl=*# select * from elementGet('/ports/head/filesystems');
   id    |    name     | type | status | iscategory | isport |    element_pathname     
---------+-------------+------+--------+------------+--------+-------------------------
 1373780 | filesystems | D    | A      | t          | f      | /ports/head/filesystems
(1 row)

freshports.dvl=*# commit;
COMMIT



freshports.dvl=# begin;
BEGIN
freshports.dvl=*# delete from element where id = 1374669;
DELETE 1
freshports.dvl=*# commit;

In the first update, I adjust the category to have the correct pathname.

In the second update, I remove the wrong path by deleting it entirely.

Categories aren’t branch aware

Branch in FreshPorts aren’t like a repo. You can’t check out a whole database for a branch. Compromises were made.

Look at category creation, I found the issue. It matches exactly with the above pathnames.

It’s right here in the comments. The diff shows the changes:

[20:47 dvl-ingress01 dvl ~/modules] % svn di category.pm 
Index: category.pm
===================================================================
--- category.pm	(revision 6118)
+++ category.pm	(working copy)
@@ -53,12 +53,13 @@
 	#
 	# if id is supplied, we are updating. otherwise we are inserting.
 	# if element_id is supplied, it will be used.  Otherwise, it will
-	# be derived from name based on /ports/.
+	# be derived from name based on /ports/head/.
+	# Categories are always on head, never a branch.
 	# A new element will be created if necessary.
 	#
 	# For new categories:
 	# description will be obtained from the contents of
-	# /ports//pkg/COMMENT
+	# /ports/head//pkg/COMMENT
 	#

But the code for creating the new category exists in a stored procedure, called by the above code:

[20:45 pg03 dvl ~/src/freshports/database-schema] % svn di sp.txt     
Index: sp.txt
===================================================================
--- sp.txt	(revision 6103)
+++ sp.txt	(working copy)
@@ -302,7 +302,7 @@
     category_id := nextval('categories_id_seq');
 
     IF category_is_primary THEN
-        pathname            := 'ports/' || category_name;
+        pathname            := 'ports/head/' || category_name;
         category_element_id := Pathname_ID(pathname);
 
         IF category_element_id is NULL THEN
[20:50 pg03 dvl ~/src/freshports/database-schema] %

Time to test again.

Test #3

For my own notes, this is what I do to prepare the snapshot’d database:

sudo service postgresql start
sudo su -l postgres
psql template1
alter database "freshports.dvl-category-testing" rename to "freshports.dvl";
\c freshports.dvl
CREATE OR REPLACE FUNCTION CreateCategory(text, text, bool) returns int4 AS $$
   DECLARE 
      category_name        ALIAS for $1;
      category_description ALIAS for $2;
      category_is_primary  ALIAS for $3;

      pathname             text;
      category_element_id  int4;
      category_id          int4;

   BEGIN
    category_id := nextval('categories_id_seq');

    IF category_is_primary THEN
        pathname            := 'ports/head/' || category_name;
        category_element_id := Pathname_ID(pathname);

        IF category_element_id is NULL THEN
                category_element_id := Element_Add(pathname, 'D');
        END IF;

        INSERT INTO categories (id,          is_primary,          element_id,          name,          description)
                    values     (category_id, category_is_primary, category_element_id, category_name, category_description);
    ELSE
         INSERT INTO categories (id,          is_primary,          name,          description)
                    values     (category_id, category_is_primary, category_name, category_description);
    END IF;

    return category_id;

   END;
$$  LANGUAGE 'plpgsql';
select *, element_pathname(id) from element where name = 'filesystems' order by element_pathname;

Shortly after restarting the test, I saw only the proper category being created.

What’s next?

The recent code changes are related only to creation of new categories. But we’ll see how they go with some burn-in.

Next I need to:

Review corrections to category table and apply to other databases/websites – DONE
Commit code – DONE
Promote that code through to dev and perhaps test – DONE