[Savannah-cvs] [DataExport] Simple export of trackers data

[Beuc]

Currently you can export trackers data using the built-in tools ($tracker -> 
Export in the project menu) - if you're a project admin.
It exports data in a ad-hoc XML format.

Actually maybe it would be simpler to just selectively dump the database.
Here's a first draft on how to do that::

 group_id=XXX
 dir=`mktemp -d`
 public_only="AND privacy != 2"
 for i in bugs bugs_cc bugs_dependencies bugs_history trackers_file 
trackers_msgid trackers_spamscore; do       
     (mysql -NBr savane -e "SHOW CREATE TABLE $i" | sed '1s/.*\t//'; echo ";") 
> $dir/$i-structure.sql;
 done
 mysql savane  -NBe "SELECT * FROM bugs WHERE group_id=$group_id $public_only;" 
> $dir/bugs.tsv
 mysql savane  -NBe "SELECT * FROM bugs_cc WHERE bug_id IN (SELECT bug_id FROM 
bugs WHERE group_id=$group_id $public_only);" > $dir/bugs_cc.tsv
 mysql savane  -NBe "SELECT * FROM bugs_dependencies WHERE item_id IN (SELECT 
bug_id FROM bugs WHERE group_id=$group_id $public_only);" > 
$dir/bugs_dependencies.tsv
 mysql savane  -NBe "SELECT * FROM bugs_history WHERE bug_id IN (SELECT bug_id 
FROM bugs WHERE group_id=$group_id $public_only);" > $dir/bugs_history.tsv
 mysql savane  -NBe "SELECT * FROM trackers_file WHERE item_id IN (SELECT 
bug_id FROM bugs WHERE group_id=$group_id $public_only) AND artifact='bugs';" > 
 $dir/trackers_file.tsv
 mysql savane  -NBe "SELECT * FROM trackers_msgid WHERE item_id IN (SELECT 
bug_id FROM bugs WHERE group_id=$group_id $public_only) AND artifact='bugs';" > 
$dir/trackers_msgid.tsv
 # Don't mention who reported on who:
 mysql savane  -NBe "SELECT id, score, artifact, item_id, comment_id FROM 
trackers_spamscore WHERE item_id IN (SELECT bug_id FROM bugs WHERE 
group_id=$group_id $public_only) AND artifact='bugs';" > 
$dir/trackers_spamscore.tsv

(mysql -NB not only produces a format equivalent to SELECT ... INTO OUTFILE, 
but also works remotely)

The first issue to address is to limit the number of queries that users can 
perform.  For example a database export of the Gnash bugs takes 7MB. Either we 
can limit with a cron job, or by limiting the number of queries from logged-in 
users, or IP.

The second issue to address is to import such data back in Savane.  To avoid 
publishing false data (e.g. importing a bug report wrongly attributed to a 
public person to discredit him/her), all user information should be discarded 
or displayed as non-authoritative comment.  The only information that Savannah 
can trust is the one that Savannah itself produced, not the one that is 
imported.

The third issue to address is to export tracker configuration::
 bugs_field
 bugs_field_usage
 bugs_field_value

 bugs_canned_responses
 bugs_report
 bugs_report_field

 trackers_field_transition
 trackers_field_transition_other_field_update
 trackers_notification
 trackers_notification_event
 trackers_notification_role

Other tables shouldn't need to be exported.
--
forwarded from http://savannah.gnu.org/maintenance/address@hidden/maintenance