FreeBSD package dependency tree

example.txt

$ pkg shell
SQLite version 3.42.0 2023-05-16 12:36:15
Enter ".help" for usage hints.
sqlite> .read tree.sql
deskutils/xfce4-notifyd (1)
 databases/sqlite3 (2)
  devel/libedit (2)
sysutils/xfce4-power-manager (1)
 x11/libXScrnSaver (1)
 x11/libXtst (145)
sysutils/xfce4-settings (1)
 graphics/colord (1)
  devel/libgudev (1)
  devel/libgusb (1)
  graphics/lcms2 (1)
  misc/hwdata (1)
 lang/python39 (1638)
  devel/readline (1638)
  math/mpdecimal (1638)
 sysutils/upower (2)
  devel/dbus-glib (2)
 x11-fonts/fontconfig (1153)
 x11/libXcursor (49)
 x11/libxklavier (2)
  misc/iso-codes (2)
  x11/libxkbfile (4)
  x11/xkbcomp (2)
x11-wm/xfce4-desktop (1)
 devel/libnotify (4)
x11-wm/xfce4-panel (3)
 devel/desktop-file-utils (4)
 devel/libdbusmenu (3)                                                                                                                [41/1837]
  devel/json-glib (3)
 lang/perl5.34 (79)
 sysutils/garcon (5)
 x11/libexo (5)
  net/p5-URI (5)
x11-wm/xfce4-session (1)
 sysutils/consolekit2 (3)
  devel/libevdev (3)
  devel/libudev-devd (4)
  graphics/libdrm (3)
   devel/libpciaccess (3)
  textproc/libxslt (7)
 sysutils/polkit (6)
  lang/duktape-lib (6)
  textproc/expat2 (1317)
 x11/iceauth (1)
 x11/xrdb (1)
  x11-toolkits/libXmu (1)
   x11-toolkits/libXt (1)
x11-wm/xfce4-wm (1)
 graphics/libepoxy (68)
 print/harfbuzz (231)
  graphics/graphite2 (231)
 x11-toolkits/libwnck3 (6)
 x11/libICE (379)
 x11/libSM (189)
 x11/libXcomposite (49)
 x11/libXdamage (49)
 x11/libXext (1251)
  x11/libXau (6136)
 x11/libXfixes (536)
 x11/libXi (339)
 x11/libXinerama (49)
 x11/libXpresent (1)
 x11/libXrandr (52)
 x11/libXrender (684)
 x11/libXres (7)
 x11/libxfce4menu (19)
  devel/libgtop (19)
 x11/libxfce4util (66)
 x11/startup-notification (26)
  x11/xcb-util (26)
 x11/xfce4-conf (28)
x11/lightdm-gtk-greeter (1)
 accessibility/at-spi2-core (144)
  devel/dbus (156)
 devel/gettext-runtime (3262)
  print/indexinfo (8189)
 devel/glib20 (1631)
  converters/libiconv (1631)
  devel/libffi (3269)
  devel/pcre2 (1631)
 graphics/cairo (240)
  graphics/libglvnd (308)
  x11/pixman (240)
 graphics/gdk-pixbuf2 (244)
  graphics/jpeg-turbo (244)
  graphics/png (3020)
  misc/shared-mime-info (244)
  textproc/libxml2 (508)
 x11-toolkits/gtk30 (48)
  graphics/gtk-update-icon-cache (96)
  misc/hicolor-icon-theme (48)
  x11-themes/adwaita-icon-theme (48)
 x11-toolkits/pango (144)
  converters/fribidi (192)
  print/freetype2 (2536)
  x11-fonts/encodings (288)
  x11-fonts/libXft (144)
  x11-fonts/xorg-fonts-truetype (144)
   x11-fonts/dejavu (144)
   x11-fonts/font-bh-ttf (144)
   x11-fonts/font-misc-ethiopic (144)
   x11-fonts/font-misc-meltho (144)
    x11-fonts/mkfontscale (576)
     x11-fonts/libfontenc (576)
 x11/libX11 (4573)
 x11/lightdm (1)
  security/libgcrypt (1)
   security/libgpg-error (1)
  sysutils/accountsservice (1)
  x11/libXdmcp (4867)
   x11/xorgproto (4867)
  x11/libxcb (4840)
sqlite>

tree.sql

WITH RECURSIVE t AS (
SELECT 0 AS i, p.origin || '/' || d.origin AS path, d.origin AS parent
FROM deps d JOIN packages p ON d.package_id = p.id WHERE p.origin = 'x11-wm/xfce4'
UNION ALL
SELECT i + 1, path || '/' || d.origin, d.origin AS parent
FROM t JOIN packages p ON p.origin = t.parent JOIN deps d ON d.package_id = p.id
WHERE i < 100
)
SELECT format('%' || CAST(length(parent) + i AS TEXT) || 's (%i)', parent, count(*)) 
FROM t
GROUP BY parent
ORDER BY path;