SQL scripts to create and update GitSense Hacktoberfest tables
Creates the following tables
- hf_topics
- hf_labels
Once these tables have been created, they will not be updated if you execute this script again. If you want to change the topics and/or labels, you will need to drop the table and run the script again.
Creates and updates the hf_pr_repos table
Creates and updates the hf_pr_authors table
Creates and updates the hf_pr_labels table
Creates and updates the hf_pr_langs table
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
topic | text | | |
Contains the GitHub repo topics associated with hacktoberfest repos. Currently we only consider 'hacktoberfest' but it might make sense to add 'hactoberfest' and other possible misspelled variations.
Column | Type | Collation | Nullable | Default
--------+----------------+-----------+----------+---------
type | hf_label_types | | not null |
label | text | | |
Valid types include 'all', 'spam' and 'accepted'.
Column | Type | Collation | Nullable | Default
------------+--------------------------------+-----------+----------+---------
type | hf_label_types | | not null |
day | character varying(12) | | not null |
repo_id | integer | | not null |
owner | text | | not null |
name | text | | not null |
stars | integer | | |
requests | integer | | |
authors | integer | | |
latest_pr | timestamp(3) without time zone | | |
updated_at | timestamp(3) without time zone | | not null |
The hf_pr_repos table contains all repos with a valid hacktoberfest topic and at least one pull request with a valid hacktoberfest label. If a repo has a valid hacktoberfest topic but no valid pull requests, it won't be included in this table.
Valid types include 'all', 'spam' and 'accepted'
Day as YYYY-MM-DD.
Repository id
Repository owner
Repository name
Repository stars
Number of pull requests
Number of unique pull request authors
Latest created pull request time
When this row was updated
Column | Type | Collation | Nullable | Default
------------+--------------------------------+-----------+----------+---------
type | hf_label_types | | not null |
day | character varying(12) | | not null |
author | text | | not null |
orgs | integer | | not null |
repos | integer | | not null |
requests | integer | | not null |
latest_pr | timestamp(3) without time zone | | |
updated_at | timestamp(3) without time zone | | not null |
hf_pr_authors contains all pull request authors that has one or more valid hacktoberfest pull request.
Label type with valid values bing 'all', 'spam' and 'accepted'
Day as YYYY-MM-DD
Pull request author
Number of unique orgs containing one or more pull requests
Number of unique repos containing one or more pull requests
Number of pull requests owned by author
Latest created pull request time
When this row was updated
Column | Type | Collation | Nullable | Default
------------+--------------------------------+-----------+----------+---------
day | character varying(12) | | not null |
label | text | | not null |
orgs | integer | | not null |
repos | integer | | not null |
requests | integer | | not null |
authors | integer | | not null |
updated_at | timestamp(3) without time zone | | not null |
hf_pr_labels contains analytics for hacktoberfest labels as defined in the hf_labels table.
Day as YYYY-MM-DD
Hacktoberfest label
Number of orgs associated with this label
Number of repos associated with this label
Number of pull requests with this label
Number of unique authors that has this label associated with their pull request
When this row was updated
Column | Type | Collation | Nullable | Default
------------+--------------------------------+-----------+----------+---------
type | hf_label_types | | not null |
day | character varying(12) | | not null |
lang | text | | not null |
orgs | integer | | not null |
repos | integer | | not null |
requests | integer | | not null |
authors | integer | | not null |
updated_at | timestamp(3) without time zone | | not null |
hf_pr_langs contains stats for all languages that were changed by pull requests with a valid hacktoberfest label.
Label type with valid values being 'all', 'spam' and 'accepted'
Day as YYYY-MM-DD
Language of file for files changed by a hacktoberfest pull request
Number of orgs associated with this lang
Number of repos associated with this lang
Number of pull requests with this lang
Number of authors associated with this lang