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=#
