Aug 032007
 

This is a starting introduction to the FreshPorts database. Hopefully it will prompt questions. Ask.

The key data component of FreshPorts is the commit. These are stored in the commit_log table. Each commit affects one or more files, known in the database as elements.

The elements table is a self-referencing table and represents the files and directories of the source repository. Here are a few entries from that table:

freshports.org=# select *, element_pathname(id) from element order by id limit 10;
 id |   name   | parent_id | directory_file_flag | status |        element_pathname
----+----------+-----------+---------------------+--------+--------------------------------
  1 | ports    |           | D                   | A      | /ports
  2 | editors  |         1 | D                   | A      | /ports/editors
  3 | yudit    |         2 | D                   | A      | /ports/editors/yudit
  4 | Makefile |         3 | F                   | A      | /ports/editors/yudit/Makefile
  5 | pkg      |         3 | D                   | A      | /ports/editors/yudit/pkg
  6 | DESCR    |         5 | F                   | D      | /ports/editors/yudit/pkg/DESCR
  7 | files    |         3 | D                   | A      | /ports/editors/yudit/files
  8 | md5      |         7 | F                   | D      | /ports/editors/yudit/files/md5
  9 | www      |         1 | D                   | A      | /ports/www
 10 | quanta   |         9 | D                   | D      | /ports/www/quanta
(10 rows)

freshports.org=#

To relate a commit to the files it touches, the commit_log_ports table is used.

Ports are just a special case of abstraction. The Ports table contains data taken from each Makefile for the port in question. For this, “make -V” is used.

Ports are also elements. Using the element ids from the above query, here are the ports that pertain to those elements:

The key data component of FreshPorts is the commit. These are stored in the commit_log table. Each commit affects one or more files, known in the database as elements.

The elements table is a self-referencing table and represents the files and directories of the source repository. Here are a few entries from that table:

freshports.org=# select id, element_id, category_id, short_description from ports
where element_id between 1 and 10;
 id | element_id | category_id |                 short_description
----+------------+-------------+----------------------------------------------------
  1 |          3 |           1 | Multi-lingual unicode text editor with TTF support
  2 |         10 |           2 | Comprehensive html/website development environment
(2 rows)

freshports.org=#

Some commits affect ports. This relationship is maintained in the commit_log_ports table.

The categories table is what you think it is:

freshports.org=# select id, element_id, name from categories order by name;
 id  | element_id |     name
-----+------------+---------------
  85 |     171607 | accessibility
  64 |            | afterstep
  88 |     159346 | arabic
  23 |        350 | archivers
  26 |        410 | astro
  25 |        386 | audio
  42 |       2710 | benchmarks
  36 |        869 | biology
  35 |        830 | cad
  39 |       1660 | chinese
  41 |       2191 | comms
  27 |        423 | converters
  32 |        582 | databases
  33 |        802 | deskutils
  10 |         84 | devel
  84 |     148762 | dns
   1 |          2 | editors
  63 |            | elisp
  22 |        245 | emulators
  54 |     118514 | finance
  47 |      16545 | french
  13 |        140 | ftp
   3 |         18 | games
 118 |            | geography
  44 |       3747 | german
  58 |            | gnome
 101 |            | gnustep
   4 |         29 | graphics
  96 |            | hamradio
  77 |            | haskell
  46 |      11329 | hebrew
  51 |     118517 | hungarian
  62 |            | ipv6
   6 |         39 | irc
  12 |        129 | japanese
  34 |        815 | java
  55 |            | kde
 117 |            | kld
  37 |       1109 | korean
  15 |        171 | lang
  66 |            | linux
  90 |            | lisp
  19 |        201 | mail
  16 |        176 | math
  45 |       6412 | mbone
   7 |         42 | misc
  52 |     118520 | multimedia
   8 |         50 | net
  95 |     229588 | net-im
  92 |     173566 | net-mgmt
  98 |     236506 | net-p2p
  17 |        179 | news
  76 |            | offix
  40 |       2143 | palm
  93 |            | paralell
  68 |            | parallel
  89 |            | pear
  94 |            | perl
  59 |            | perl5
  87 |            | php
  50 |      58316 | picobsd
  69 |            | plan9
  82 |     148764 | polish
 111 |     265340 | ports-mgmt
  53 |     118523 | portuguese
  24 |        360 | print
  57 |            | python
  74 |            | ruby
  97 |            | rubygems
  31 |        577 | russian
  83 |            | scheme
  48 |      56065 | science
   5 |         34 | security
  29 |        465 | shells
 100 |            | spanish
  20 |        218 | sysutils
  70 |            | tcl80
  72 |            | tcl81
  71 |            | tcl82
  60 |            | tcl83
  80 |            | tcl84
  18 |        188 | textproc
  78 |            | tk42
  73 |            | tk80
  61 |            | tk82
  65 |            | tk83
  79 |            | tk84
  75 |            | tkstep80
  49 |      57265 | ukrainian
  11 |         94 | vietnamese
  56 |            | windowmaker
   2 |          9 | www
  21 |        231 | x11
  28 |        428 | x11-clocks
 115 |     278073 | x11-drivers
  30 |        516 | x11-fm
  38 |       1229 | x11-fonts
  43 |       3321 | x11-servers
  91 |     171611 | x11-themes
   9 |         55 | x11-toolkits
  14 |        147 | x11-wm
  81 |            | xfce
  67 |            | zope
(103 rows)

freshports.org=

You will notice that categories, like ports, have an element_id.

One of the key features of FreshPorts is the notification. Users select what they wish to monitor and add it to their watch list. A watch list consists of a watch_list_id and an element_id.

freshports.org=# select * from watch_list_element limit 10;
 watch_list_id | element_id
---------------+------------
          4000 |        916
          4334 |     112921
          4334 |      90346
          4334 |      88170
          4334 |     101671
          3105 |      57670
          3105 |      13030
          3105 |      68999
          3105 |       2487
          3105 |      68994
(10 rows)

freshports.org=#

In turn, each watch list is owned by a user. Users can have multiple watch lists:

freshports.org=# select id, user_id, name from watch_list order by user_id limit 10;
  id   | user_id |  name
-------+---------+--------
 10276 |       1 | stuff
 10275 |       1 | things
  8609 |       2 | main
 10277 |       2 | other
  1247 |       3 | main
  1248 |       4 | main
  1249 |       5 | main
  1250 |       6 | main
  1251 |       7 | main
     4 |       9 | main
(10 rows)

freshports.org=#
Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive