Savannah Database Schema

This page describes the MySQL database schema used on GNU Savannah.

See also: SavannahArchitecture, SavannahInternals, UserAuthentication, SavannahDatabaseExamples.

Overview

• The MySQL database server runs on internal0.savannah.gnu.org, and is accessible from other VMs (e.g. vcs, frontend, download, etc.).
• file reference in on this page refer to the administration/savane.git source code repository.

The most important tables are:

user

basic account data

user_preferences

auxiliary account data

groups

basic group data

group_preferences

additional group data, including GnuPG keys and Git browsing settings

mail_group_list

mailing list data

user_group

group membership and permissions for given user

bugs, patch, task, support

tracker items, including original submissions

bugs_history, patch_history, task_history, support_history

tracker follow-ups: comments, file additions and other changes

bugs_report, bugs_report_field, patch_report, patch_report_field, task_report, task_report_field, support_report, support_report_field

tracker query definitions

group_type

descriptions and capacities of various group types

Table creation scripts

Tables are defined in the db/mysql directory. Each table has a corresponding table_NAME.structure and table_NAME.initvalues files. Example: the groups table (containing all groups on Savannah) is defined in db/mysql/table_groups.structure.

As of 2022, these table definitions aren't actively maintained, though.

group and user table diagram

savannah-database-groups-and-users.dia

group_type

mysql> select type_id, name from group_type ;
+---------+------------------------------------+
| type_id | name                               |
+---------+------------------------------------+
|       1 | Official GNU software              |
|       2 | non-GNU software and documentation |
|       3 | www.gnu.org portions               |
|       4 | GUG                                |
|       6 | www.gnu.org translation teams      |
+---------+------------------------------------+
5 rows in set (0.00 sec)

groups

mysql> select unix_group_name, group_name
       from groups
       where status="A" and is_public=1 and type=1
+-----------------+---------------------+
| unix_group_name | group_name          |
+-----------------+---------------------+
| aspell          | GNU Aspell          |
| autoconf        | Autoconf            |
| autogen         | autogen             |
| automake        | Automake            |
| bison           | bison               |
| chess           | GNU Chess           |
| cim             | GNU Cim             |
| classpath       | classpath           |
| commoncpp       | commoncpp           |
| complexity      | Complexity Measure  |
| coreutils       | GNU Core Utilities  |
| cpio            | GNU cpio            |
      (list truncated for brevity)

user

Basic user information shown at https://savannah.gnu.org/users/rms.

mysql> select user_id, realname, from_unixtime(add_date) as 'member since'
       from user where status="A" and user_name='rms' ;
+---------+---------------------+-------------------------+
| user_id | realname            | member since            |
+---------+---------------------+-------------------------+
|     144 | Richard M. Stallman | 2001-01-28 18:13:31     |
+---------+---------------------+-------------------------+
1 row in set (0.02 sec)

Users with status = 'SQD' are "squads" (per-group user teams defined in the user_squad table).

user_group

Group membership of user rms shown at https://savannah.gnu.org/users/rms, plus permission bits.

mysql> select groups.group_name
       from groups, user, user_group
       where user.user_name = 'rms' and
             user_group.user_id = user.user_id and
             user_group.group_id = groups.group_id and
             groups.status="A" and groups.is_public=1;
+----------------------------------+
| group_name                       |
+----------------------------------+
| www.gnu.org                      |
| Free Software Directory          |
| emacs                            |
| bison                            |
| gnulib - GNU portability library |
| GNU Coding Standards             |
| Free Software History            |
| GNU Press non-technical books    |
+----------------------------------+
8 rows in set (0.01 sec)