chaoss / chaoss/CollectOSS

Legacy Data Issue: adding all contributors.canonical_email that are not in contributor_alias.alias_email

Open
#270 0 comments 0 reactions 0 assignees View on GitHub
database
Dominant language
Python
Stars
13
Forks
17
Avg merge
6h 59m
Merged PRs (30d)
1

Description

Now, collectoss adds all emails from the facade task to the contributor_alias.alias_email column. For databases that existed before collectoss there needs to be row added for all contributors.canonical_email that are not in the contributor_alias.alias_email.

Note: this is different from #269 as that one is for contributor_alias.canonical_email and this one is for contributors.canonical_email

How many are not in the alias table:

`SELECT COUNT(*)
FROM augur_data.contributors c
WHERE NOT EXISTS (
SELECT 1
FROM augur_data.contributors_aliases ca
WHERE ca.alias_email = c.cntrb_canonical
)
AND c.cntrb_canonical IS NOT NULL;`

Query to add to the alias table:

```INSERT INTO augur_data.contributors_aliases (
alias_email,
canonical_email,
cntrb_active,
cntrb_last_modified,
tool_source,
tool_version,
data_source,
data_collection_date,
cntrb_id
)
SELECT
c.cntrb_canonical AS alias_email,
c.cntrb_canonical AS canonical_email,
1 AS cntrb_active,
NOW() AS cntrb_last_modified,
'Manual' AS tool_source,
'0.0.1' AS tool_version,
'Contributors Table' AS data_source,
NOW() AS data_collection_date,
c.cntrb_id
FROM augur_data.contributors c
WHERE NOT EXISTS (
SELECT 1
FROM augur_data.contributors_aliases ca
WHERE ca.alias_email = c.cntrb_canonical
)
AND c.cntrb_canonical IS NOT NULL;
```

If email is added manually it may need to get removed from the unresolved table"

```
DELETE FROM augur_data.unresolved_commit_emails
WHERE email IN (
SELECT alias_email
FROM augur_data.contributors_aliases
);
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.