Explore GHTorrent 2004 Schema for Contextual Data

[splimon]

https://github.com/sailuh/sentiment_classifier

Purpose

Explore the GHTorrent 2004 schema to identify all tables and columns that can provide contextual information about the sentiment-labeled comments from the GitHub Gold Standard dataset.

Background

This work connects the GitHub Gold Standard sentiment dataset to the GHTorrent 2004 database to add contextual information (e.g., project data, user data, timestamps). The contextual data will enable Kaiaulu to re-download 2004-2025 project data for temporal expansion.

Related: See Kaiaulu issue #226 for the comment schema structure that Kaiaulu expects.

Current Schema

What we're starting with:

• Github Gold Standard dataset loaded into MySQL as comment_sentiment table (per SQL queries provided by @carlosparadis in Discussion Post #6)
• Has 7,122 sentiment-labeled comments
• Columns: ID (comment ID), Polarity (sentiment), Text (comments)
• Key connection: comment_sentiment.ID maps to commit_comments.comment_id in GHTorrent database

Here are the first few rows from the GitHub Gold Standard dataset:

What we're connecting to:

• GHTorrent has project/user/timestamp data spread across multiple tables
• Current JOIN path: comment_sentiment → commit_comments → commits → projects

This is the GHTorrent schema structure (retrieved here):

What we need to produce:

• Kaiaulu-compatible format (see issue #226)
• Contextual dataset with enough info for Kaiaulu to re-download project data

Process

1. Examine table structures, relationships, and sample data in the GHTorrent 2004 database
2. Focus on tables that can link to comment data (projects, users, commits, issues, pull requests)
3. Analyze join behavior (INNER vs LEFT JOIN)
4. Document findings in the sentiment_github_dataset repository
5. Iterate with @carlosparadis to determine which columns are most relevant

Task List

• Investigate row count reduction (priority for this week):
• Confirm if INNER JOINs reduces sentiment-labeled rows
• Use LEFT JOINs to identify which comments have no GHTorrent dataset matches
• Document which contextual data is NULL/missing
• Query and document the GHTorrent 2004 schema structure (relevant tables, columns, data types)
• Identify all comment-related tables (commit_comments, issue_comments, pull_request_comments)
• Identify project/repository tables (projects, repositories)
• Identify user tables (users) and their relationship to comments
• Create schema mapping document showing GHTorrent → Kaiaulu format
• Share findings with @carlosparadis for feedback

[carlosparadis]

Thanks! Could you also add an image of the schema directly in your issue description as this directly ties to it?

[splimon]

@carlosparadis, you asked me to check on the number of rows from the manually labeled sentiment data reducing when I run your queries to inner join (Issue #10). Here's what I found:

• Original comment_sentiment table: 7,110 rows
• After INNER JOINs: 4,306 rows

I ran a LEFT JOIN to see which sentiment comments aren't matching anything in the GHTorrent database:

SELECT COUNT(*) FROM comment_sentiment s
LEFT JOIN commit_comments cc ON s.ID = cc.comment_id
WHERE cc.comment_id IS NULL;

After doing this, it says that 2,804 rows have NULL. So, those sentiment comments don't exist in the commit_comments table in the GHTorrent 2004 database, which is why INNER JOIN is dropping them.

Questions:

1. You mentioned in Issue #10 the CSV had ~4k rows, but I'm seeing 7,110 in the comment_sentiment table. This is the Github Gold Standard CSV I downloaded. Just to confirm, is this the correct dataset?
2. Should I explore the database with LEFT JOINs and RIGHT JOINs to figure out what additional information is N/A?

[carlosparadis]

Thank you for running these tests so quickly for me. And also for putting the project schemas here, now we have all in one place which makes easier to think through! Also note I added the word sql in your ``` to make the code highlight.

So we are losing nearly half the data currently, this is not great. I think that's around when I ran out of time to figure out the why.

In the paper associated to this work, I believe they said they used two different types of data to manually anotate.

46_github_sentiment_work_3_class.pdf

I am attaching the paper here with my markings, but please don't go read the whole thing. The main point here is that you can cross-check the counts you have or know what to look table-wise in the schema.

Original comment_sentiment table: 7,110 rows

This does not sound right. In the comment_sentiment.csv on my computer, I have 7122 rows. Weirdly the file does not work to open in excel, I have to open in a text editor. Could you double check?

Note the paper also matches my number (see GitHub, disregard the rest):

This is also from where I got the .csv I shared with you:

The dataset can be downloaded from: https://doi.org/10.6084/m9.figshare.11604597

So you see, you should only care about this paper to the extent that helps you sanity check your data. The why, hows and motivations are beyond the scope of your project (although you are welcome to ask me if curious).

Now, the relevant part from the work that I also want to make you aware is this:

As a third dataset, we manually labeled over 7K comments from GitHub pull requests and commits

So that means when you are doing this SQL query:

SELECT COUNT(*) FROM comment_sentiment s
LEFT JOIN commit_comments cc ON s.ID = cc.comment_id
WHERE cc.comment_id IS NULL;

You are only getting the commit portion. Looking at the schema on your issue description (now you see why I ask you to also add here), I also see a pull_request_comments. Could you try what you just did on that table as well to see if you find our missing data points? Ideally the sum of the inner joined rows between those two tables result in the full 7122 (which you need to double check why you're lacking a few).

I am re-attaching mine with 7122 just in case.

comment_sentiment.csv

[carlosparadis]

So, to continue on this week's goal:

The first thing we must try to do is try as much as possible trace these IDs to these tables in this schema. Hopefully the above suffice, but if not we need to scrunitize it further. E.g. maybe it is worth checking the issue_comments table to see if we find any matches there.

The Kaiaulu part I should save you time on where you can make the bridge so you can focus on more interesting parts to advance this project forward.

Reconstructing Commit Comments in Kaiaulu

Looking at this schema table from the database dump:

SELECT COUNT(*) FROM comment_sentiment s
LEFT JOIN commit_comments cc ON s.ID = cc.comment_id
WHERE cc.comment_id IS NULL;

If you do this, then the commit_comment table is now "within reach". From the diagram, we know that there is a 1 to N relationship between commits and commit_comments, and we also know there is a 1 to N relationship between projects and `commits.

What you need to get to bridge to Kaiaulu is a) projects.(id,name,url, b) commits.(id,sha,author_id,comitter_id), and c) pull_request_comments.(user_id,comment_id,commit_id)

If you could give me a few screenshots of each of the 3 tables before doing anything that would also help me double check this suffice.

For example, we can take one comment you found from the .csv that had a match to commit id. Then you can check its project URL. You can then go on your browser to said github page, and then search for the commit sha. If you find it on the browser, then Kaiaulu can also get it.

The same is true for pill request information. I suspect the pull_request_comments.comment_id is the actual PR comment id. If you could find it via browser, then Kaiaulu is also able to get it.

Hope this makes sense. Let me know if you have questions.

[splimon]

@carlosparadis,

Thank you for sending the paper and the additional context.

This does not sound right. In the comment_sentiment.csv on my computer, I have 7122 rows. Weirdly the file does not work to open in excel, I have to open in a text editor. Could you double check?

I'm also unable to open the CSV in Excel. I double-checked the CSV file in a text editor - it has 7,122 data rows (7,123 lines including header), which matches both the paper (https://doi.org/10.6084/m9.figshare.11604597) and the CSV you provided.

However, when initially loading comment_sentiment.csv into MySQL and following the steps from Discussion Post #6 using LOAD DATA INFILE, only 7,110 rows were inserted (missing 12 rows), which is where I got that initial row number from.

I tried another approach importing the CSV using MySQL's "Table Data Import Wizard," but I got this error:

Solution:
In the end, I had to create a separate Python script (import_csv_to_mysql.py) using Python's CSV parser to successfully import all 7,122 rows into MySQL.

Note: The script requiresmysql-connector-python

Could you try what you just did on that table as well to see if you find our missing data points? Ideally the sum of the inner joined rows between those two tables result in the full 7122.

1. Count sentiment comments in commit_comments:

SELECT COUNT(*) FROM comment_sentiment s
INNER JOIN commit_comments cc ON s.ID = cc.comment_id;

Result: 4,317

2. Count sentiment comments in pull_request_comments:

SELECT COUNT(*) FROM comment_sentiment s
INNER JOIN pull_request_comments prc ON s.ID = prc.comment_id;

Result: 2,890

Total: 4,317 + 2,890 = 7,207

Since this total doesn't match up with the expected 7,122 total, I also checked which comments overlapped from both tables:

SELECT COUNT(*) as both_tables FROM comment_sentiment s
INNER JOIN commit_comments cc ON s.ID = cc.comment_id
INNER JOIN pull_request_comments prc ON s.ID = prc.comment_id;

Result: 85

Breakdown:

• Commit-only: 4,317 - 85 = 4,232
• PR-only: 2,890 - 85 = 2,805
• Both tables (overlap): 85
• Total: 4,232 + 2,805 + 85 = 7,122

All 7,122 sentiment comments exist in the GHTorrent database, split between commit and pull request comments as the paper indicated.

If you could give me a few screenshots of each of the 3 tables before doing anything that would also help me double check this suffice.

1. Projects table (id, name, url):

2. Commits table (id, sha, author_id, committer_id):

3. Pull request comments table (user_id, comment_id, commit_id):

[carlosparadis]

Nice work!

Here's what I suggest you do next:

1. Try to pinpoint the problematic rows that were not loading originally in the data. Take a look at them. Is there anything that is corrupting those rows? I suspect there may something in the "body" column that SQL and excel is not liking. This will ensure when you are getting the data in nothing is getting lost.

You can open the .csv on a text editor to see the 7122. I recommend you open on Visual Studio Code.

2. Same idea for these 85 rows that are both commit and pull request, this is a bit weird. What sets them apart?

3. Using the project table, try to find the URL of the project on GitHub today using your browser, so we can use Kaiaulu.

Some of these may not be so easy. Here's one I tried with akka: https://github.com/orgs/akka/repositories?q=akka As you can see akka is now so large it has sub-akka projects.

That said, when I pasted the URL of the project above: https://api.github.com/repos/akka/akka on my browser, it took me to a JSON. In there I saw that the project is now called "akka-core".

You should be able to search for a commit hash using https://github.com/username/repository/commit/35e32b6a00dec02ae7d7c45c6b7106779a124685. If you can, try pasting these screenshots as the actual table in this page. They will auto convert to table format, and will let I and others in the future copy and paste.

For the pull request id, these two digit ids are are clearly local. So you will want to inner join that table to the other tables in the schema to get the actual authors, commit hash etc.

4. You want to try doing a test run of what we discussed on getting the data for Kaiaulu. After you do a few browse checks to make sure the commit messages can be found in the repo, and that the pull requests can also be found (Recommend doing this via browser just to quick test), you can use this Kaiaulu Notebook: http://itm0.shidler.hawaii.edu/kaiaulu/articles/gitlog_showcase.html

To parse the table of a git repo (git clone the file to your local machine, then run the Notebook. Note you will also need to install Perceval, the tool, for this). The parse_gitlog() table will show commit hashes, so you can double check you have the right thing.

Note this will parse all the .git which may take awhile. If it takes forever, I suggest you look up how to revert a git log permanently to a previous commit hash (so you could reduce the git log size to around 2004), and then parse again.

For the pull request data you would want the GitHub Notebook in Kaiaulu: http://itm0.shidler.hawaii.edu/kaiaulu/articles/download_github_pull_request_comments.html

If the above is too daunting, you can run these two notebooks in Kaiaulu itself first (as we have both a .git and pull requests!) to test things out. Kaiaulu is much smaller than Akka, so it will run relatively fast!

5. As a last note statistic: Of the 7122 rows that are manually labeled, can you do a group by to check how many labels we have per project (include the project name / URL in said table of counts). This will help us get a sense if it is at all viable to train the model per project or if we are stuck with the 7122 rows for your sentiment work.

---

If you are able to go through items 1 through 4, you will have already proof you reconstructed the original dataset but this time downloading from Kaiaulu. As you can see, from the get go after step 4 you already achieve for that project both horizontal and temporal expansions of the dataset.

At that point, you will have in essence "recreated" the data from Kaiaulu framework, allowing us to expand it in various ways.

Thank you!

[splimon]

@carlosparadis ,

An update on my progress on the tasks you mentioned:

1. Investigating the 12 missing rows

I compared the original CSV file in VS Code (7,122 rows) against what loaded into MySQL (7,110 rows) to identify which rows failed. The issue with these rows is that their text fields are not enclosed in double quotes.

For example, row 30 (ID 1357344) has unquoted text that spans multiple lines, which confuses MySQL's parser.

1357344;negative;Yeah, I was going to enhance the performance of the augmentWidthorHeight function by doing exactly what you have there. Having to call getComputedStyle multiple times sucks. I need it to also grab the box-sizing as well, so it needs to be a bit less naive

Problematic Row IDs:
245530, 315650, 405175, 851557, 1206985, 1357344, 1927988, 2272574, 2424631, 2889938, 3037108, 4139362

Solution: I was able to create a Python script (import_csv_to_mysql.py) using Python's CSV parser, which successfully imported all 7,122 rows into MySQL.

2. Analyzing the 85 Overlapping Comments

I investigated the 85 comments that appear in both commit_comments and pull_request_comments tables:

SELECT s.ID, s.Text, cc.commit_id as commit_table_commit_id, 
prc.commit_id as pr_table_commit_id, 
prc.pull_request_id
FROM comment_sentiment s
INNER JOIN commit_comments cc ON s.ID = cc.comment_id
INNER JOIN pull_request_comments prc ON s.ID = prc.comment_id

This showed:

It seems like the 85 comments have different commit IDs in each table. These are pull request comments associated with multiple commits within the same PR (e.g., comment 8041 references commit 353005 in commit_comments but commit 89722 in pull_request_comments, both part of PR 10712)

Next Steps

I'll continue with:

• Step 3: Check if dataset projects still exist on GitHub and verify commit accessibility
• Step 4: Test retrieving commit and PR data using Kaiaulu notebooks
• Step 5: Count labeled comments per project to assess training data distribution

I'll update you with my findings as I progress through these steps.

[splimon]

2/6/26 Capstone Meeting Notes (Sentiment Classifier)

Hi @carlosparadis,

Here are the notes from today's meeting.

Next Steps added to M1

1. Verify GitHub Project Accessibility

a) Query projects with labeled comments

• Run SQL query to get projects that have sentiment-labeled comments from the database
• Prioritize projects with the most labeled comments for testing
• Extract project name, URL, and comment count

b) Manual browser verification

• Test if project URLs still exist on GitHub (check for 404s, renames, or moves to organizations)
• Example: https://github.com/akka/akka may redirect or have been renamed
• Use GitHub API to verify current project status: https://api.github.com/repos/{owner}/{repo}
• Check if projects have been renamed (e.g., akka → akka-core)

c) Verify commit SHA accessibility

• Query database to get a commit SHA from a matched comment
• Construct GitHub URL: https://github.com/{owner}/{repo}/commit/{sha}
• Open in browser to verify commit page loads
• Check if the comment text appears on that commit page

d) Documentation

• Post project data as actual Markdown tables (not screenshots) so others can copy/paste
• Create table with: Project Name | Original URL | Still Exists? | Notes

2. Test Kaiaulu Data Retrieval

a) Manual validation (browser)

• Pick a few matched comments from the database
• Trace path: comment_sentiment → commit_comments → commits → projects
• Get project URL and commit SHA
• Open https://github.com/{owner}/{repo}/commit/{sha} in browser
• Verify the comment text exists on GitHub
• Repeat for pull request comments using PR URLs

b) Test Kaiaulu gitlog notebook

• Clone a GitHub repository locally (start with Kaiaulu's own repo - smaller and faster)
• Run Kaiaulu's gitlog_showcase notebook
• Use parse_gitlog() to parse the .git folder
• Verify commit SHAs from database appear in Kaiaulu's parsed output
• If parsing takes too long on large repos: revert git history to ~2004 to reduce size

c) Test Kaiaulu GitHub PR comments notebook

• Run Kaiaulu's GitHub pull request comments notebook (download_github_pull_request_comments.Rmd)
• Fetch PR comments via GitHub API
• Verify pull request comments from database can be retrieved (PR comment IDs in database are local, so inner join to other tables to get authors, commit hashes, etc.)

d) Apply to dataset projects

• Once validated on Kaiaulu repo, apply same process to actual dataset projects
• Document which projects successfully parse and which have issues

3. Generate Project-Level Statistics

a) Count labeled comments per project

• Run SQL query with GROUP BY to count sentiment labels per project
• Include project name and URL in output
• Order by comment count (descending) to see distribution

b) Analyze training data viability

• Determine if there's enough labeled data per project for project-specific models
• Assess whether to train one global model (7,122 rows) or multiple project-specific models

c) Documentation

• Post results as table with project names, URLs, and comment counts

[carlosparadis]

Thanks for moving this here. Just so I am clear, what are you currently working on this week? I understand what was planned to the above but just want to be clear on the current target.

Thanks!

[splimon]

Hi @carlosparadis ,

This week, I'm working on tasks 3-5 mentioned here.

• Step 3: Verifying that GitHub projects from the 2004 dataset are still accessible
• Step 4: Testing Kaiaulu's gitlog and PR comments notebooks on a sample project
• Step 5: Generating project-level statistics for the labeled comments

My current target is to validate that the GitHub Gold Standard dataset can be successfully recreated using Kaiaulu.

I'll update the thread once I have results from the verification step!

Thank you,
Samantha

[carlosparadis]

Looks good, thank you!

[splimon]

Hi @carlosparadis ,

An update on my progress of Task 3 mentioned in this comment:

3. Verify GitHub Project Accessibility

I ran this SQL query to get this list of projects with the most sentiment-labeled comments:

SELECT p.id, p.name, p.url, COUNT(DISTINCT s.ID) as labeled_comment_count
FROM projects p
INNER JOIN commits c ON p.id = c.project_id
INNER JOIN commit_comments cc ON c.id = cc.commit_id
INNER JOIN comment_sentiment s ON cc.comment_id = s.ID
GROUP BY p.id, p.name, p.url
ORDER BY labeled_comment_count DESC

Projects by labeled comment count (top 10):

Project Name	Github Project URL	API URL	Labeled Comments	Status
TrinityCore	https://github.com/TrinityCore/TrinityCore	https://api.github.com/repos/TrinityCore/TrinityCore	800	Active
MaNGOS	https://github.com/mangos/MaNGOS	https://api.github.com/repos/mangos/MaNGOS	622	Active
rails	https://github.com/rails/rails	https://api.github.com/repos/rails/rails	448	Active
CraftBukkit	https://github.com/github/dmca/blob/master/2014/2014-09-05-CraftBukkit.md	https://api.github.com/repos/Bukkit/CraftBukkit	357	Unavailable due to DMCA
jquery	https://github.com/jquery/jquery	https://api.github.com/repos/jquery/jquery	235	Active
diaspora	https://github.com/diaspora/diaspora	https://api.github.com/repos/diaspora/diaspora	160	Active
xbmc	https://github.com/xbmc/xbmc	https://api.github.com/repos/xbmc/xbmc	130	Active
netty	https://github.com/netty/netty	https://api.github.com/repos/netty/netty	80	Active
html5-boilerplate	https://github.com/h5bp/html5-boilerplate	https://api.github.com/repos/h5bp/html5-boilerplate	80	Active
node	https://github.com/nodejs/node-v0.x-archive	https://api.github.com/repos/joyent/node	65	Archived

I then verified that specific commits with labeled comments are still accessible by running this query for each project:

SELECT c.sha, p.url, p.name, s.ID as comment_id, s.Text as comment_text
FROM commits c
INNER JOIN projects p ON c.project_id = p.id
INNER JOIN commit_comments cc ON c.id = cc.commit_id
INNER JOIN comment_sentiment s ON cc.comment_id = s.ID
WHERE p.url = 'https://api.github.com/repos/{owner}/{repo}'; --- Replaced with actual project URL for each test

Manual Verification Results:

Project Name	Commit SHA	Accessible?	Comment Found?
TrinityCore	3e45640135bd24886e76eea393a706bc5b6a86a4	Yes	Yes
MaNGOS	abfc99ef522b8b6353d051a002d026530ec7d253	Yes	Yes
rails	a41669563b960d604068013a5b808476391b1cb9	Yes	Yes
CraftBukkit	5ebee02e4ac51bf8df11c75584d4dbd161666581	No	No
jquery	f6e86c3ca4d527d5453a0b5b9591ef38b5d3c000	Yes	Yes
diaspora	3ff94b0312d5cc655fceac5651c7d78bf3334c0a	Yes	Yes
xbmc	dc0edf03c7fe069d810265a60ebbfc41b4aa12db	Yes	Yes
netty	1fee1ef74ed8ac515c19a7f8eebd16f41a37b7b6	Yes	Yes
html5-boilerplate	1c30c5a6602c708cca718572eaa80b52110d3f81	Yes	Yes
node	61935bc167cc2de57c6417bd12493775dc9c1b81	Yes	Yes

9 out of 10 projects from this sample list have accessible commits with visible comments. The one exception (CraftBukkit) was taken down due to a DMCA notice. This validates that the 2004 dataset can be successfully recreated using Kaiaulu for the majority of projects.

Now I'll continue working on Task 4: Testing Kaiaulu's gitlog and PR comments notebooks on one of these sample projects.

[carlosparadis]

Could you give me another comment just like the one above but with the statistics for pull requests too? Sometimes projects migrate their issue tracker structure, so it is a good idea to check if that is the case and if the amount of labeled comments on our second type of data source change rankings.

Great update message, has everything I needed to remember, understand and assess your progress!

[splimon]

Hi @carlosparadis ,

I ran the same analysis for pull request comments. Here's the query I ran:

SELECT p.id, p.name, p.url, COUNT(DISTINCT s.ID) as labeled_comment_count
FROM projects p
INNER JOIN pull_requests pr ON p.id = pr.base_repo_id
INNER JOIN pull_request_comments prc ON pr.id = prc.pull_request_id
INNER JOIN comment_sentiment s ON prc.comment_id = s.ID
GROUP BY p.id, p.name, p.url
ORDER BY labeled_comment_count DESC

Projects by labeled PR comment count (top 10):

Project Name	Github Project URL	API URL	Labeled Comments	Status
akka	https://github.com/akka/akka-core	https://api.github.com/repos/akka/akka	404	Active
symfony	https://github.com/symfony/symfony	https://api.github.com/repos/symfony/symfony	315	Active
rails	https://github.com/rails/rails	https://api.github.com/repos/rails/rails	177	Active
node	https://github.com/nodejs/node-v0.x-archive	https://api.github.com/repos/joyent/node	163	Archived
zf2	https://github.com/zendframework/zendframework	https://api.github.com/repos/zendframework/zf2	154	Archived
jquery	https://github.com/jquery/jquery	https://api.github.com/repos/jquery/jquery	150	Active
homebrew	https://github.com/Homebrew/legacy-homebrew	https://api.github.com/repos/mxcl/homebrew	138	Archived
libgit2	https://github.com/libgit2/libgit2	https://api.github.com/repos/libgit2/libgit2	122	Active
cakephp	https://github.com/cakephp/cakephp	https://api.github.com/repos/cakephp/cakephp	111	Active
scala	https://github.com/scala/scala	https://api.github.com/repos/scala/scala	110	Active

Comparison: Commit Comments vs PR Comments
Projects appearing in both top 10 lists:

Project Name	Commit Rank	Commit Count	PR Rank	PR Count	Pattern
rails	#3	448	#3	177	Balanced
node	#10	65	#4	163	PR heavy
jquery	#5	235	#6	150	Balanced

Projects only in commit comments top 10:

• TrinityCore
• MaNGOS
• CraftBukkit
• diaspora
• html5-boilerplate

Projects only in PR comments top 10:

• akka
• symfony
• zf2
• homebrew
• libgit2
• cakephp
• scala

Key Findings

• Only 3 projects appear in both top 10 lists
• Different projects favor different comment types (commit vs PR)
• To recreate the complete dataset, Kaiaulu must extract from both commit comments and PR comments (e.g., using only commit comments would miss akka/symfony data, while using only PR comments would miss TrinityCore/MaNGOS data)

[carlosparadis]

@splimon Thank you for this and the conclusions are on point. Do you know which Notebook in Kaiaulu you need?