Provide regular dumps of Trac database

[bmispelon]

A long time ago, database dumps of the Trac tables used to be provided for public consumption but that practice has stopped at some point.

I think we should start doing this again (inspired by a discussion I had on Discord with @ulgens today). It would be useful both for people trying to work on code.djangoproject.com locally, but also those who'd like to extract statistics from Trac.

Some of the technical challenges to figure out:

• Where to host the file (it used to be on the wiki, but maybe there's a better place)?
• How to clean the data? The database has things like session ids which should not be shared under any circumstances, as well as user emails which should stay private.

[ulgens]

Hey @bmispelon , thanks for moving this to an issue.

I was looking for a way to get a dump from Trac to run data analyses on it. I wanted to measure a couple of points, but my big goal was to understand if something like https://clickpy.clickhouse.com/ is doable for Django issues and find ways to improve the dashboard.

• I have no opinion about the hosting solution. Is wiki still alive?
• Did we have anything to process data before the export when those dumps were active? If so, is the script still accessible?
  • I guess we can start by deciding what we want to export/dump. My guess is that the data will be issue-based, so even without any user-related data, it may be useful to some degree. If we want to export state transitions, owner info, commenters, etc., what are the data points that we can share about the user?

[bmispelon]

I have no opinion about the hosting solution. Is wiki still alive?

Depends what you mean by "alive" 😁 . It is technically still working, but it's barely used (I think the fellows update the roadmap page, but that's all I know of).
It could be a good solution for hosting in terms of not having to recreate something that already exist, but it might be harder to automate (I'm not sure how easy it is to interact with the wiki part of Trac programmatically).
Another low-tech solution is to have a fixed location where we upload/save the dump, make sure that location is served by the webserver, and hardcode that link in the templates where we want it.

Did we have anything to process data before the export when those dumps were active? If so, is the script still accessible?

Unfortunately the script (if there ever was one) is not available anymore. I tried to reach out to the person who I thought was uploading the dumps, but never got a reply.

I guess we can start by deciding what we want to export/dump. My guess is that the data will be issue-based, so even without any user-related data, it may be useful to some degree. If we want to export state transitions, owner info, commenters, etc., what are the data points that we can share about the user?

Personally I think we should share as much of the data as possible, while preserving security (session ids for example) and users' privacy (email addresses). Basically if if the information is available publicly in some form, it should be included in the dump. I think that's where the hard part of this issue resides: figuring out which tables/columns are safe to share or not.

Not sure if you've seen already, but we already share a (very limited) dump of the trac database with mostly just the tables and no data: https://github.com/django/djangoproject.com/blob/main/tracdb/trac.sql. If you come up with some scripts or queries you'd like to try out, don't hesitate to get in touch with me and I can run them on the live data.

[thibaudcolas]

I’d find it useful personally as part of searching for things in Trac (have lots of trouble with the default experience), and checking people’s contribution history (for example as part of the Steering Council elections).

Re hosting – is it a question of file size / bandwidth, or automation, or discoverability? 🤔 Depending on the answer, could be some of the DSF’s infrastructure and platforms, or something meant for analysts like Kaggle perhaps.

For cleaning the data – I’d guess a table allowlist, and then anonymization steps for specific tables? See for example django-birdbath, not sure it’s the right fit here but that’s what my employer uses to help sharing database dumps without sharing the personal data that’s meant to stay in production only.

[bmispelon]

At this point it's purely a technical issue about how to clean the data in a reproducible and automated way. I'll take a look at birdbath, thanks 👍🏻

[srishti60-stack]

hey@bmispelon
My suggestion is
For flexible scalability and using cloud storage services like AWS or Google Cloud, for instance, these solutions can be great. Services for access, including automated backups, are given for versioning of files with a cloud storage service such as Amazon S3 or Google Cloud Storage.
Django-specific Hosting: If the focus is on Django developers, then you can host the database dumps in a specific resource dedicated to Django, such as a community-driven site or the central repository like Django Packages if such option exists.
Whatever the case, ensure that it is easy to access for people who might need it while keeping unauthorized access out of it (for instance, through encryption or access control) in case of sensitive information.

2. Data Cleaning How to clean the data?
   Anonymize or remove any session IDs or user emails, because it could be in Trac database. Here are a few approaches for cleaning the data:

a. Remove or Anonymize Sensitive User Data:
Session IDs: Simply replace all session IDs by generic, non traceable identifiers.
User Emails: Replace user emails with a placeholder email address like user{n}@example.com where {n} is a unique number for each user. This prevents any real email addresses from being exposed.
IP Addresses: If there are any IP addresses stored in the database, these should be masked or removed, especially if they are associated with user sessions.
b. Remove or Replace Specific Fields:
Internal Logs: Any internal logs or traces which include sensitive system information should either be truncated or anonymized.
Password Fields: In case there are password hashes, it should be removed or replaced.