[Savannah-cvs] [599] proofread, update and augment

[ineiev]

Revision: 599
          
http://svn.savannah.gnu.org/viewvc/?view=rev&root=administration&revision=599
Author:   ineiev
Date:     2023-02-28 02:37:40 -0500 (Tue, 28 Feb 2023)
Log Message:
-----------
proofread, update and augment

Modified Paths:
--------------
    trunk/sviki/SavannahDatabaseSchema.mdwn

Modified: trunk/sviki/SavannahDatabaseSchema.mdwn
===================================================================
--- trunk/sviki/SavannahDatabaseSchema.mdwn     2023-02-27 08:22:45 UTC (rev 
598)
+++ trunk/sviki/SavannahDatabaseSchema.mdwn     2023-02-28 07:37:40 UTC (rev 
599)
@@ -13,27 +13,40 @@
   
[administration/savane.git](https://git.savannah.gnu.org/cgit/administration/savane.git)
   source code repository.
 
-## Tables creation scripts
+The most important tables are:
 
-Tables are defined in the
-[/db/mysql](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/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](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/db/mysql/table_groups.structure).
+<dl>
+<dt>user</dt><dd>basic account data</dd>
+<dt>user_preferences</dt><dd>auxiliary account data, including GnuPG keys</dd>
+<dt>groups</dt><dd>basic group data</dd>
+<dt>user_group</dt><dd>group membership and permissions for given user</dd>
+<dt>bugs, patch, task, support</dt><dd>tracker items, including original 
submissions</dd>
+<dt>bugs_history, patch_history, task_history, support_history</dt>
+<dd>tracker follow-ups: comments, file additions and other changes</dd> 
+<dt>bugs_report, bugs_report_field, patch_report, patch_report_field,
+task_report, task_report_field, support_report, support_report_field</dt>
+<dd>tracker query definitions</dd>
+<dt>group_type</dt><dd>descriptions and capacities of various group types</dd>
+</dl>
 
-## groups(=projects) and users tables diagram
+## 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.png](/maintenance/savannah-database-groups-and-users.png)
 
 
[savannah-database-groups-and-users.dia](/maintenance/savannah-database-groups-and-users.dia)
 
-## `group_type` table
+## group_type
 
-
-Structure:
-[/db/mysql/table_group_type.structure](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/db/mysql/table_group_type.structure).
-
     mysql> select type_id, name from group_type ;
     +---------+------------------------------------+
     | type_id | name                               |
@@ -46,11 +59,8 @@
     +---------+------------------------------------+
     5 rows in set (0.00 sec)
 
-## `groups` table
+## groups
 
-Structure:
-[/db/mysql/table_groups.structure](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/db/mysql/table_groups.structure).
-
     mysql> select unix_group_name, group_name
            from groups
            where status="A" and is_public=1 and type=1
@@ -72,14 +82,11 @@
           (list truncated for brevity)
 
 
-## `user` table
+## user
 
-Public user information, same as shown at:
+Basic user information shown at
 <https://savannah.gnu.org/users/rms>.
 
-Structure:
-[/db/mysql/table_user.structure](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/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' ;
     +---------+---------------------+-------------------------+
@@ -89,19 +96,14 @@
     +---------+---------------------+-------------------------+
     1 row in set (0.02 sec)
 
-TODO:
+Users with `status = 'SQD'` are "squads" (per-group user teams defined
+in the `user_squad` table).
 
-* explain `user.status='SQD'` used for project "squads" (per-project
-  mailing lists. used with `sv_aliases` on `vcs`.
+## user_group
 
-## `user_group` table
+Group membership of user `rms` shown at
+<https://savannah.gnu.org/users/rms>, plus permission bits.
 
-Project membership of user `rms`, same as shown at
-<http://savannah.gnu.org/users/rms>.
-
-Structure:
-[/db/mysql/table_user_group.structure](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/db/mysql/table_user_group.structure).
-
     mysql> select groups.group_name
            from groups, user, user_group
            where user.user_name = 'rms' and