<jupyter_start><jupyter_text>Crawl License Information<jupyter_code># based on https://github.com/madelonhulsebos/gittables/blob/main/gittables/table_annotator.py
    
USER = "" # add user here
TOKEN = "" # add valid token here

import requests
import time
def get_table_license(url: str):
    
    repository_url = url.split("blob")[0]
    owner = repository_url.split("/")[-3]
    repo = repository_url.split("/")[-2]

    try:
        response = requests.get(
            f"https://api.github.com/repos/{owner}/{repo}/license",
            headers={"accept": "application/vnd.github.v3+json"},
            auth=(USER, TOKEN)
        )
        if response.status_code == 200:
            table_license = response.json()["license"]
            if table_license["name"] == "Other":
                table_license = None
            else:
                table_license = {"license": table_license}
        elif response.status_code == 404:
            # In this case, the repository is not associated with a license.
            table_license = None
        elif response.status_code == 403:
            # In this case, we likely reached the API limit.
            waiting_time = float(response.headers["X-RateLimit-Reset"]) - time.time()
            if waiting_time < 0:
                # We will not waiting for nothing, there was something else wrong.
                table_license = None
            else:
                msg = f"Reached limit on owner {owner}, repo {repo}, waiting for {waiting_time} s"
                print(msg)
                time.sleep(waiting_time)
                table_license = get_table_license(url)
        else:
            # In this case, we encountered another error.
            code = response.status_code
            msg = f"Ran into another issue, with status code {code}"
            print(msg)
            table_license = None
    except Exception as e:
        msg = f"Ran into exception {e}"
        print(msg)
        table_license = None

    return table_license
import pickle

checkpoint = []
start_idx = 0

if os.path.exists('../../data/license_checkpoint.pckg'):
    checkpoint = pickle.load(open('../../data/license_checkpoint.pckg','rb'))
    start_idx = max([v[0] for v in checkpoint]) + 1

with open("../../data/sqlfiles_urls.csv") as f:
    line = f.readline()
    while line:
        idx = int(line[0:6])
        if idx < start_idx:
            line = f.readline()
            continue
            
        url = line[7:]
        license = get_table_license(url)
        license_key = license['license']['key'] if license and 'license' in license.keys() and 'key' in license['license'].keys() else "None"
        checkpoint.append([idx, url, license_key])
        
        if idx % 100 == 0:
            print(f"checkpointed at {idx}")
            pickle.dump(checkpoint, open('../../data/license_checkpoint.pckg','wb'))
        
        line = f.readline()<jupyter_output><empty_output><jupyter_text>Evaluate License Information<jupyter_code>import os
from collections import Counter
if os.path.exists('../../data/license_checkpoint.pckg'):
    checkpoint = pickle.load(open('../../data/license_checkpoint.pckg','rb'))
    counter = Counter([v[2] for v in checkpoint])
    counter = dict(counter)
sorted(counter.items(), key=lambda x: -x[1])
# percentage with lincense
sum([(counter[k] if (k is not None and k != 'None') else 0) for k in counter])/sum(counter.values())<jupyter_output><empty_output><jupyter_text>Add License Information to Metadata<jupyter_code># load license information
licenses = pickle.load(open('../../data/license_checkpoint.pckg','rb'))

licenses_dict = {}
for license_info in licenses:
    url = license_info[1]
    license = license_info[2]
    licenses_dict[url] = license
# load metadata
import json
with open('../../data/metadata_postgres.json', 'r') as f:
    metadata = json.load(f)
# add license information   
for key in metadata.keys():
    url = metadata[key]["INFO"]["url"]
    license = None
    if url in licenses_dict: 
        license = licenses_dict[url]
    metadata[key]["INFO"]["license"] = license if license is not None else "None"
# save metadata with license information
with open('../../data/metadata_postgres_with_licenses.json', 'w') as f:
    json.dump(metadata, f)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1294746629474663, 'fraction_numerical': 0.006741050674105067, 'mean_word_length': 2.603852596314908, 'pattern_counts': {'":': 3, '<': 15, '<?xml version=': 0, '>': 13, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '377063', 'license': 'CC0-1.0', 'n_tokens_mistral': 1300, 'n_tokens_neox': 1267, 'n_words': 324, 'path': 'src/analysis/crawl_licenses.ipynb', 'repo_name': 'tdoehmen/github-db-schemas', 'size': '4302'}