[Savannah-cvs] [137] more details about savannah internals.

[Assaf Gordon]

Revision: 137
          
http://svn.sv.gnu.org/viewvc/?view=rev&root=administration&revision=137
Author:   agn
Date:     2015-02-14 04:17:55 +0000 (Sat, 14 Feb 2015)
Log Message:
-----------
more details about savannah internals.

* SavannahInternals: more information
* SavannahDatabaseSchema: new page.

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

Added Paths:
-----------
    trunk/sviki/SavannahDatabaseSchema.mdwn
    trunk/sviki/savannah-database-groups-and-users.dia
    trunk/sviki/savannah-database-groups-and-users.png

Added: trunk/sviki/SavannahDatabaseSchema.mdwn
===================================================================
--- trunk/sviki/SavannahDatabaseSchema.mdwn                             (rev 0)
+++ trunk/sviki/SavannahDatabaseSchema.mdwn     2015-02-14 04:17:55 UTC (rev 
137)
@@ -0,0 +1,125 @@
+# 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](http://git.savannah.gnu.org/cgit/administration/savane.git)
+  source code repository.
+
+## Tables creation scripts
+
+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).
+
+## groups(=projects) and users tables 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
+
+
+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                               |
++---------+------------------------------------+
+|       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](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
++-----------------+---------------------+
+| 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](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' ;
++---------+---------------------+-------------------------+
+| user_id | realname            | member since            |
++---------+---------------------+-------------------------+
+|     144 | Richard M. Stallman | 2001-01-28 18:13:31     |
++---------+---------------------+-------------------------+
+1 row in set (0.02 sec)
+```
+
+## `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](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
+             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)
+```
+
+
+

Modified: trunk/sviki/SavannahInternals.mdwn
===================================================================
--- trunk/sviki/SavannahInternals.mdwn  2015-02-12 23:05:03 UTC (rev 136)
+++ trunk/sviki/SavannahInternals.mdwn  2015-02-14 04:17:55 UTC (rev 137)
@@ -111,12 +111,14 @@
 11. Project members with write access can push code to the repositories on
     `vcs` using SSH. See 'vcs' section in [[SavannahServices]] for
     configuration details. See [[CvsGettingStarted]],[[Svn]],[[UsingHg]],
-    [[UsingBze]],[[UsingGit]] for usage information.
+    [[UsingBzr]],[[UsingGit]] for usage information.
     See [[UserAuthentcation]] for SSH authentication details.
 
 
 ### User Registration (frontend)
 
+Anyone with a valid email account can register on GNU Savannah.
+
 * Public URL: <https://savannah.gnu.org/account/register.php>
 * Source code: 
[/frontend/php/account/register.php](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/account/register.php)
 * The `register.php` creates a new record in the database `user` table, with
@@ -128,6 +130,10 @@
 
 ### Project submission (frontend)
 
+Any savannah user can submit a submit a new project request. This procedure is
+for non-gnu projects only (for gnu projects see here:
+<http://www.gnu.org/help/evaluation.html>).
+
 * Public URL: <https://savannah.gnu.org/register/>
 * Source code: 
[/frontend/php/register/index.php](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/register/index.php)
 (and see all files in
   
[/frontend/php/register/](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/register/)
 ).
@@ -141,6 +147,11 @@
 
 ### Project approval, creation (frontend)
 
+Savannah admins approve and create projects after manual review
+(the manul review process is outside the scope of savannah). Project creation
+is the same for GNU projects as well (except manual review is done by other GNU
+members, not savannah admins).
+
 * Public URL: 
<https://savannah.nongnu.org/siteadmin/groupedit.php?group_id=NNNNNN>
   (only available to savanah admins in 'super-user' mode).
 * The URL is display as 'group administration' link when viewing a
@@ -176,8 +187,53 @@
 * FIXME: `triggerscreation.php` mentions `"site-specific triggers executed"` -
   which ones are these and where are they executed?
 
+### Project administration - general information (frontend)
+
+Project admins can modify various items for their projects using the savannah
+web interface.
+
+* Name, Short/Long descriptions
+    * public URL: 
<https://savannah.nongnu.org/project/admin/editgroupinfo.php?group=XXXXX>
+    * source code: 
[/frontend/php/project/admin/editgroupinfo.php](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/project/admin/editgroupinfo.php).
+    * The script modifies the `group` table.
+* Project members (additional savannah users with write-access):
+    * public URL: 
<https://savannah.nongnu.org/project/admin/useradmin.php?group=XXXXXX>
+    * source code: 
[/frontend/php/project/admin/useradmin.php](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/project/admin/useradmin.php)
+    * modifications are stored in the `user_group` table (a many-to-many
+      relation table between `user` and `groups` tables).
+    * uses code from 
[/frontend/php/include/member.php](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/include/member.php).
+* Project squads
+    * FIXME: a per-project list of multiple savannah users?
+    * public URL: 
<https://savannah.nongnu.org/project/admin/squadadmin.php?group=XXXXX>
+    * Source code: 
[/frontend/php/project/admin/squadadmin.php](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/project/admin/squadadmin.php).
+* Permissions
+    * per-user permissions for website features (e.g. tracker management)
+    * Public URL: 
<https://savannah.nongnu.org/project/admin/userperms.php?group=XXXXX>
+    * Source code: 
[/frontend/php/project/admin/userperms.php](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/project/admin/userperms.php).
+* Jobs
+    * '*jobs*' are posting which will appear in the
+      [Contributers Wanted](https://savannah.nongnu.org/people/) page.
+    * Public URL: 
<https://savannah.nongnu.org/people/createjob.php?group=XXXXX>
+    * Source code: 
[/frontend/php/people/createjob.php](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/people/createjob.php).
+* Select Features
+    * See "feature selection" section below.
+* Configure Mailing lists
+    * See "mailing lists" section below.
+* Configure Cookbook
+    * FIXME: document/explain cookbooks
+* Set notifications:
+    * Select savannah users which will be notified upon tracker activity
+      (e.g. new task, new support ticket, new bug report, etc.).
+    * Public URL: 
<https://savannah.nongnu.org/project/admin/editgroupnotifications.php?group=XXXXX>
+    * Source code: 
[/frontend/php/project/admin/editgroupnotification.php](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/frontend/php/project/admin/editgroupnotifications.php).
+    * FIXME: document/explain trackers
+
+
 ### Project administration - feature selection (frontend)
 
+Project admins can enable/disable features for their projects, using the
+savannah web interface.
+
 * *features* in this context are the facilities used by the project, such as
 git, cvs, homepage, downloads, news, bugs/support/patches/tasks trackers,
 mailing lists, etc.  
@@ -210,6 +266,9 @@
 
 ### Project administration - mailing lists (frontend)
 
+Project admins can add/configure/delete mailing lists for their projects using
+the savannah web interface.
+
 * Mailing list require special handling: the mailing list server (running
   GNU Mailman) is a GNU server and is not administrated by Savannah admins.
 * sepcial configuration on `lists.gnu.org` allows remote execution of
@@ -227,14 +286,49 @@
   the database with the result of the change.  
   See 
[/backend/mail/sv_mailman.in](http://git.savannah.gnu.org/cgit/administration/savane.git/tree/backend/mail/sv_mailman.in).
 
 
-### Project administration - general information
-
 ### MySQL Database
 
+See [[SavannahDatabaseSchema]] and [[SavannahDatabaseExamples]].
+
 ### Cron job on vcs
 
-### Cron jobs on downloads
+FIXME: work-in-progress
 
+Stored in `vcs:/etc/cron.d/sv`:
+
+```
+# remake user list for outgoing mail.
+*/10 * * * *    root    nice -n 11 sv_aliases --cron && sed -e '/# Savannah 
include start/,/# Savannah include end/s/\([^:]\+\): .*/\1: address@hidden/' 
/etc/email-addresses -e 's,This is /etc/email-addresses,DO NOT EDIT - GENERATED 
FROM email-addresses.,' > /etc/email-addresses_SENDER
+
+# remake list of git repositories in each project.
+*/15 * * * *   root    nice -n 11 /usr/src/infra/git/refresh-project-list.sh 
&& /usr/src/infra/git/sv_cgit.pl
+
+# remake CVSROOT/{commit,log}/info (for cvs commit email?).
+31 * * * *     root    nice -n 11 /usr/src/infra/cvs/generate_log_accum.pl
+
+# remake list of groups for the vcs.
+35,05 * * * *  root    nice -n 11 sv_groups --cron --only-cvs --only-homepage 
--only-svn --only-git --only-hg --only-bzr
+```
+
+### Cron jobs on download
+
+FIXME: work-in-progress
+
+stored in `download:/etc/cron.d/sv`:
+
+```
+*/30 * * * *   root    sv_groups --cron --only-download --only-arch
+```
+
+### Cron jobs on Fronend
+
+FIXME: work-in-progress
+
+stored in `frontend:/etc/cron.d/sv_{export,list_groups}` - what are these for?
+
+FIXME: where's the script which creates lists on `lists.gnu.org` (
+calls `sv_mailman`)?
+
 ### www.gnu.org update mechanism
 
 ### Mailing list configuration

Added: trunk/sviki/savannah-database-groups-and-users.dia
===================================================================
(Binary files differ)


Property changes on: trunk/sviki/savannah-database-groups-and-users.dia
___________________________________________________________________
Added: svn:mime-type
   + application/octet-stream

Added: trunk/sviki/savannah-database-groups-and-users.png
===================================================================
(Binary files differ)


Property changes on: trunk/sviki/savannah-database-groups-and-users.png
___________________________________________________________________
Added: svn:mime-type
   + application/octet-stream