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.
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.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 withsv_aliases
onvcs
.
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)