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 internal.sv.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.

Tables creation scripts

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

groups(=projects) and users tables diagram

savannah-database-groups-and-users.png

savannah-database-groups-and-users.dia

group_type table

Structure: /db/mysql/table_group_type.structure.

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 table

Structure: /db/mysql/table_groups.structure.

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 table

Public user information, same as shown at: https://savannah.gnu.org/users/rms.

Structure: /db/mysql/table_user.structure.

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)

TODO:

  • explain user.status='SQD' used for project "squads" (per-project mailing lists. used with sv_aliases on vcs.

user_group table

Project membership of user rms, same as shown at http://savannah.gnu.org/users/rms.

Structure: /db/mysql/table_user_group.structure.

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)