Re: [Savannah-hackers-public] Running GNU Savannah (frontend) locally

[beuc]

Hi,

I believe you're not hacking on the right code base, which is:
https://savannah.nongnu.org/p/savane-cleanup
I don't know why some Savannah Hackers forked it after I left, but
you're missing a few bugfixes that are now in use at https://gna.org/

Also, if you want to recreate a local instance, I highly suggest you
take a look at: http://svn.gna.org/viewcvs/admin/trunk/puppet/ which
automatically recreates the full multi-VM Gna! environment and could
be easily adapted for Savannah.  I already pointed that out, I'd
recommend documenting this somewhere.  This amounts to a solid month
of work.

While I'm at it, let me point out
http://git.savannah.gnu.org/cgit/savane-cleanup.git/tree/sv2ff/ which
is the start of a migration script to move to FusionForge.
I suggested this year that we move to FusionForge since it's an active
similar project, but that's not something I can do alone - are you
interested?

For the data export, you're doing it wrong: to ensure privacy, you
need to specifically extract the data that is safe, and leave out
everything else, that way you cannot disclose private information.  At
first glance I'm not trilled to see the database offered for analysis
to anybody, so I think a discussion with the Savannah _Users_ needs to
happen before any data is made available to more people.

I'm CC:ing savannah-hackers-public since this is of interest for all
Savannah Hackers and Users :)

Cheers!
Sylvain

On Fri, Aug 29, 2014 at 11:41:10AM -0400, Assaf Gordon wrote:
> Hello Sylvain,
> 
> My name is Assaf, I recently joined the GNU Savannah team.
> 
> I've managed to run GNU Savannah locally, which allows debugging and 
> improving the frontend much more easily.
> Karl Berry suggested I write to you and ask for your feedback and suggestions.
> 
> The patch is here:
>   http://files.housegordon.org/gnu-sv/sv-local-run.patch.xz
> With instructions here:
>   http://files.housegordon.org/gnu-sv/HACKING.html
> 
> 
> To make this really useful (and the reason more feedback and review are 
> needed),
> is that I've also managed to make a snapshot of the current GNU Savannah 
> database,
> and have it loaded (almost painlessly) in this locally-running demo.
> 
> This means that once we agree on what can goes in the snapshot database, we 
> can have anyone run a full copy of the GNU Savannah frontend, with all the 
> real data in it.
> It will also allow people to explore statistics on GNU Savannah (like active 
> users, projects, etc.) which is another thing I'm interested it.
> 
> The snapshots (small - 3 projects, medium - ~50 projects, big - all 
> projects/users/bugs/tasks/etc.) are on fencepost.gnu.org - so a GNU account 
> is required to access them.
> Once we agree on the content of the database, we can make them public.
> 
> ===
> The snapshots DO NOT include private users/groups/projects/bugs/tasks, user 
> emails, pw-hashes, skills and other items which I figured are private.
> Please help me review the SQL commands below, to ensure I managed to scrub 
> the database for all private information.
> Please also try to run savannah locally on your computers (if you're 
> interested), and see if you spot anything in the database that shouldn't be 
> there.
> 
> Let me know what you think.
> If you can think of other privacy concerns, let's discuss them.
> 
> Thanks!
>  - Assaf
> 
> 
> Script to scrub database follows:
> =====
> 
> #!/bin/sh
> 
> DB=savane
> 
> ## Group id 5802 is Savane Administration - it must be included.
> ## Group_id 100 has some magical importance - it must be included.
> 
> SIZE=$1
> if [ -z "$SIZE" ] ; then
> echo "missing database export size: big/medium/small" >&2
> exit 1
> fi
> if [ "$SIZE" != "small" ] && [ "$SIZE" != "medium" ] && [ "$SIZE" != "big" ] 
> ; then
> echo "invalid database export size ($SIZE). Use small/big/medium" >&2
> exit 1
> fi
> 
> 
> ##
> ## Which groups/projects to keep ?
> ##
> if [ "$SIZE" = "small" ] ; then
>   # Much shorter list
>   GROUPS_ID_KEEP="100,5802,10247,811"
> elif [ "$SIZE" = "medium" ] ; then
>   ## The Groups/Projects to keep:
>   ##   ~38 GNU projects (arbitraraliy chosen)
>   ##   ~4  Non-GNU Projects (chosen based on recent activity)
>   ##   1 Translation project (chosen based on activity)
>   GROUPS_ID_KEEP="100,25,26,29,37,39,40,56,61,67,68,69,72,77,108,180,499,535,
> 595,811,812,1025,1548,1902,1925,2613,6998,4698,
> 5802,6711,7009,8717,8884,10171,11215,11226,11285,
> 11318,3159,10494,7246,10247,6431,2140,2280"
> else
>   ##
>   ## Keep all groups/projects
>   ##
>   :
> fi
> 
> ##
> ## Delete groups/projects if needed
> ##
> if [ "$SIZE" != "big" ] ; then
>   ## Delete all the groups we don't want to keep
>   SQL="delete from groups where group_id not in ($GROUPS_ID_KEEP);"
>   echo "Deleting Projects..."
>   echo "$SQL" | mysql "$DB" || exit 1
> 
>   ## As a special case, remove most of the old project submissions -
>   ##   as they link to too many users.
>   SQL="delete from task where group_id=5802 and category_id=1 and
>        date < unix_timestamp(date_sub(current_date(),interval 1 year));"
>   echo "Deleting old project submissions..."
>   echo "$SQL" | mysql "$DB" || exit 1
> fi
> 
> ## Privacy cleanups
> SQL="
> DELETE FROM user WHERE status !='A';
> DELETE FROM groups WHERE status != 'A';
> DELETE FROM user_group WHERE admin_flags != 'A' and admin_flags != '';
> UPDATE group_history SET old_value = '';
> DELETE FROM bugs WHERE privacy != 1;
> DELETE FROM task WHERE privacy != 1;
> DELETE FROM cookbook WHERE privacy != 1;
> DELETE FROM support WHERE privacy != 1;
> DELETE FROM patch WHERE privacy != 1;
> DELETE FROM user_group WHERE privacy_flags != 1;
> "
> echo "Privacy Cleanups..."
> echo "$SQL" | mysql "$DB" || exit 1
> 
> ##
> ## Tables to be truncated, with possible private information
> ##
> TABLES_TO_TRUNCATE="
> group_preferences
> forum_monitored_forums
> form
> forum_saved_place
> session
> spam_stats
> trackers_msgid
> trackers_file
> trackers_watcher
> trackers_spamscore
> trackers_notification
> user_bookmarks
> user_lostpw
> user_queue
> user_squad
> user_votes"
> for T in $TABLES_TO_TRUNCATE ; do
>       echo "Truncating table $T..."
>       echo "truncate $T;" | mysql "$DB" || exit 1;
> done
> 
> ##
> ## Remove items (bugs/tasks/patches/etc) which belong to deleted 
> groups/projects
> ##
> GROUP_RELATED_TABLES="
> bugs
> bugs_report
> cookbook
> cookbook_context2recipe
> groups_default_permissions
> group_history
> forum_group_list
> mail_group_list
> news_bytes
> patch
> user_group
> task
> task_report
> "
> for T in $GROUP_RELATED_TABLES ; do
>       SQL="delete t1 from $T t1 left join groups t2 on t1.group_id = 
> t2.group_id where 1 and t2.group_id is null;"
>       echo "Remove stale-project-related items from $T...."
>       echo "$SQL" | mysql "$DB" || exit 1
> done
> 
> ##
> ## Remove history/cc items (bugs/tasks/patches/etc) which belong to deleted 
> bugs/tasks/patches/etc.
> ##
> HISTORY_TABLES="
> bugs
> cookbook
> patch
> support
> task"
> for T in $HISTORY_TABLES ; do
>       for C in _history _cc ; do
>               SQL="delete t1 from ${T}${C} t1 left join $T t2 on t1.bug_id = 
> t2.bug_id where 1 and t2.bug_id is null;"
>               echo "Clearing ${T}${C} table..."
>               echo "$SQL" | mysql "$DB" || exit 1
>       done
> done
> 
> # Delete users without any activity on the database
> # (note: if a user was active on a project/group which was
> #        delete, he/she will be deleted as well).
> if [ "$SIZE" != "big" ] ; then
> SQL="
> create temporary table active_user_ids
> as
> select user_id from user_group
> union distinct
> select submitted_by from bugs
> union distinct
> select submitted_by from cookbook
> union distinct
> select submitted_by from patch
> union distinct
> select submitted_by from support
> union distinct
> select submitted_by from task
> union distinct
> select mod_by from group_history
> union distinct
> select mod_by from task_history
> union distinct
> select mod_by from bugs_history
> union distinct
> select mod_by from patch_history;
> 
> delete t1 from user t1 left join active_user_ids t2
> on t1.user_id = t2.user_id where 1 and t2.user_id is null;"
> echo "Removing non-active users..."
> echo "$SQL" | mysql "$DB" || exit 1
> fi
> 
> SQL="delete t1 from people_skill_inventory t1 left join user t2
> on t1.user_id = t2.user_id where 1 and t2.user_id is null"
> echo "Removing people-skills of deleted users..."
> echo "$SQL" | mysql "$DB" || exit 1
> 
> ##
> ## Cleanup private information
> ##
> CLEANUP_TABLES="
> bugs
> patch
> task
> support"
> for T in $CLEANUP_TABLES ; do
>       SQL="UPDATE $T SET 
> ip=NULL,originator_name=\"\",originator_email=\"\",originator_phone=\"\";"
>       echo "Scrubbing table $T..."
>       echo "$SQL" | mysql "$DB" || exit 1
>       SQL="UPDATE ${T}_history SET ip=NULL;"
>       echo "Scrubbing table ${T}_history..."
>       echo "$SQL" | mysql "$DB" || exit 1
> done
> 
> 
> SQL="UPDATE groups SET bugs_private_exclude_address=NULL, 
> task_private_exclude_address=NULL,    support_private_exclude_address=NULL, 
> patch_private_exclude_address=NULL,cookbook_private_exclude_address=NULL;"
> echo "Scrubbing table groups..."
> echo "$SQL" | mysql "$DB" || exit 1
> 
> ##
> ## Insert two users with known paswords: one admin, one not.
> ##  'localadmin' password: "admin"
> ##  'localuser' password: "12345"
> ## The admin user will be added to the Savannah Administration project
> ## (group ID 5802)
> SQL="
> DELETE FROM user WHERE user_name = 'localadmin' or user_name = 'localuser';
> 
> INSERT INTO user (user_name, user_pw, add_date, status, realname)
> VALUES ('localadmin', 
> '\$6\$vzIAmmEN6zm7td8o\$On4O229qcLwPZ2hCayDu9exc8tPAmrM18aUt07V5RAQPApUUutW3oEngsb1N/fO9KZ5YO7NXao1DrA7GVX2u51',
>  UNIX_TIMESTAMP(NOW()), 'A', 'Local Administrator'),
> ('localuser','\$6\$rounds=5000\$h5r9b50dqPAFk/ta\$qZ3tUpE/9FhLCB4yEs8Mj/NqK7ED6FNI1/pvUONc8KIfPu/QZ.BRSYzVuuj7hAFD.S6S44BE8mdbF.8l7F.9X0',
>  UNIX_TIMESTAMP(NOW()), 'A', 'Local User');
> 
> INSERT INTO user_group (user_id, group_id, admin_flags)
> VALUES ((SELECT user_id FROM user WHERE user_name = 'localadmin'), 5802, 'A');
> "
> echo "Adding Local Users..."
> echo "$SQL" | mysql "$DB" || exit 1