Update Remote Postgres #3
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| name: Update Remote Postgres | |
| on: | |
| schedule: | |
| - cron: '0 2 * * *' | |
| workflow_dispatch: | |
| jobs: | |
| update_nuget_stats: | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: Install DB driver | |
| run: | | |
| python -m pip install --upgrade pip | |
| pip install psycopg2-binary requests | |
| - name: Run Nuget Update | |
| env: | |
| DB_URL: ${{ secrets.DB_URL }} | |
| run: | | |
| python - <<EOF | |
| import os | |
| import requests | |
| import psycopg2 | |
| from datetime import date | |
| def parse_db_url(raw): | |
| """Accept PostgreSQL URI or .NET key=value;... connection string, return psycopg2 dsn.""" | |
| raw = raw.strip() | |
| if raw.startswith("postgresql://") or raw.startswith("postgres://"): | |
| return raw # already a URI, psycopg2 handles it natively | |
| # .NET style: Host=...;Port=...;Database=...;Userfname=...;Password=... | |
| key_map = { | |
| "host": "host", "server": "host", | |
| "port": "port", | |
| "database": "dbname", "initial catalog": "dbname", | |
| "username": "user", "user id": "user", "uid": "user", | |
| "password": "password", "pwd": "password", | |
| "ssl mode": "sslmode", "sslmode": "sslmode", | |
| } | |
| parts = {} | |
| for pair in raw.split(";"): | |
| pair = pair.strip() | |
| if "=" not in pair: | |
| continue | |
| k, v = pair.split("=", 1) | |
| mapped = key_map.get(k.strip().lower()) | |
| if mapped: | |
| parts[mapped] = v.strip() | |
| return " ".join(f"{k}={v}" for k, v in parts.items()) | |
| db_dsn = parse_db_url(os.getenv("DB_URL", "")) | |
| date_to_record = date.today().isoformat() | |
| # Packages to track: (nuget_package_id, db_package_name) | |
| packages = [ | |
| ("Ivy", "Ivy"), | |
| ("Ivy.Tendril", "Ivy.Tendril"), | |
| ] | |
| results = {} | |
| for nuget_id, db_name in packages: | |
| try: | |
| resp = requests.get(f"https://azuresearch-usnc.nuget.org/query?q=packageid:{nuget_id}").json() | |
| dl = resp['data'][0]['totalDownloads'] | |
| results[db_name] = dl | |
| print(f"Fetched {nuget_id}: {dl} downloads") | |
| except Exception as e: | |
| print(f"Error fetching {nuget_id}: {e}") | |
| if not results: | |
| print("No download data fetched. Exiting.") | |
| exit(1) | |
| try: | |
| conn = psycopg2.connect(db_dsn) | |
| cur = conn.cursor() | |
| # Ensure table exists with package_name column | |
| cur.execute(""" | |
| CREATE TABLE IF NOT EXISTS nuget_history ( | |
| id SERIAL PRIMARY KEY, | |
| package_name TEXT, | |
| downloads BIGINT, | |
| date DATE DEFAULT CURRENT_DATE, | |
| UNIQUE(package_name, date) | |
| ); | |
| """) | |
| for db_name, downloads in results.items(): | |
| if downloads > 0: | |
| cur.execute(""" | |
| INSERT INTO nuget_history (package_name, downloads, date) | |
| VALUES (%s, %s, %s) | |
| ON CONFLICT (package_name, date) | |
| DO UPDATE SET downloads = EXCLUDED.downloads; | |
| """, (db_name, downloads, date_to_record)) | |
| print(f"Success! {db_name}: {downloads} downloads added/updated in database.") | |
| else: | |
| print(f"Skipping {db_name}: 0 downloads returned.") | |
| conn.commit() | |
| cur.close() | |
| conn.close() | |
| except Exception as e: | |
| print(f"Database error: {e}") | |
| exit(1) | |
| EOF | |
| update_github_stats: | |
| runs-on: ubuntu-latest | |
| steps: | |
| - name: Install DB driver | |
| run: | | |
| python -m pip install --upgrade pip | |
| pip install psycopg2-binary requests | |
| - name: Run GitHub Update | |
| env: | |
| DB_URL: ${{ secrets.DB_URL }} | |
| run: | | |
| python - <<EOF | |
| import os | |
| import requests | |
| import psycopg2 | |
| from datetime import date | |
| def parse_db_url(raw): | |
| raw = raw.strip() | |
| if raw.startswith("postgresql://") or raw.startswith("postgres://"): | |
| return raw | |
| key_map = { | |
| "host": "host", "server": "host", | |
| "port": "port", | |
| "database": "dbname", "initial catalog": "dbname", | |
| "username": "user", "user id": "user", "uid": "user", | |
| "password": "password", "pwd": "password", | |
| "ssl mode": "sslmode", "sslmode": "sslmode", | |
| } | |
| parts = {} | |
| for pair in raw.split(";"): | |
| pair = pair.strip() | |
| if "=" not in pair: | |
| continue | |
| k, v = pair.split("=", 1) | |
| mapped = key_map.get(k.strip().lower()) | |
| if mapped: | |
| parts[mapped] = v.strip() | |
| return " ".join(f"{k}={v}" for k, v in parts.items()) | |
| db_dsn = parse_db_url(os.getenv("DB_URL", "")) | |
| REPOS = [ | |
| "Ivy-Interactive/Ivy-Framework", | |
| "Ivy-Interactive/Ivy-Tendril", | |
| ] | |
| headers = {'User-Agent': 'python-request-script'} | |
| date_to_record = date.today().isoformat() | |
| any_success = False | |
| try: | |
| conn = psycopg2.connect(db_dsn) | |
| cur = conn.cursor() | |
| cur.execute(""" | |
| CREATE TABLE IF NOT EXISTS github_stars_history ( | |
| id SERIAL PRIMARY KEY, | |
| repo_name TEXT, | |
| stars BIGINT, | |
| date DATE DEFAULT CURRENT_DATE, | |
| UNIQUE(repo_name, date) | |
| ); | |
| """) | |
| for repo in REPOS: | |
| stars = 0 | |
| try: | |
| repo_data = requests.get(f"https://api.github.com/repos/{repo}", headers=headers).json() | |
| stars = repo_data.get('stargazers_count', 0) | |
| except Exception as e: | |
| print(f"Error fetching GitHub stats for {repo}: {e}") | |
| continue | |
| if stars > 0: | |
| cur.execute(""" | |
| INSERT INTO github_stars_history (repo_name, stars, date) | |
| VALUES (%s, %s, %s) | |
| ON CONFLICT (repo_name, date) | |
| DO UPDATE SET stars = EXCLUDED.stars; | |
| """, (repo, stars, date_to_record)) | |
| print(f"Success! {repo}: {stars} stars added/updated in database.") | |
| any_success = True | |
| else: | |
| print(f"No stars found for {repo} (fetch returned 0).") | |
| if any_success: | |
| conn.commit() | |
| cur.close() | |
| conn.close() | |
| except Exception as e: | |
| print(f"Database error: {e}") | |
| exit(1) | |
| if not any_success: | |
| print("No GitHub star counts were updated for any repo.") | |
| exit(1) | |
| EOF | |
| update_github_stargazers: | |
| runs-on: ubuntu-latest | |
| needs: update_github_stats | |
| steps: | |
| - name: Install DB driver | |
| run: | | |
| python -m pip install --upgrade pip | |
| pip install psycopg2-binary requests | |
| - name: Run GitHub Stargazers Update | |
| env: | |
| DB_URL: ${{ secrets.DB_URL }} | |
| run: | | |
| python - <<EOF | |
| import os | |
| import requests | |
| import psycopg2 | |
| from datetime import date, datetime, timezone | |
| def parse_db_url(raw): | |
| raw = raw.strip() | |
| if raw.startswith("postgresql://") or raw.startswith("postgres://"): | |
| return raw | |
| key_map = { | |
| "host": "host", "server": "host", | |
| "port": "port", | |
| "database": "dbname", "initial catalog": "dbname", | |
| "username": "user", "user id": "user", "uid": "user", | |
| "password": "password", "pwd": "password", | |
| "ssl mode": "sslmode", "sslmode": "sslmode", | |
| } | |
| parts = {} | |
| for pair in raw.split(";"): | |
| pair = pair.strip() | |
| if "=" not in pair: | |
| continue | |
| k, v = pair.split("=", 1) | |
| mapped = key_map.get(k.strip().lower()) | |
| if mapped: | |
| parts[mapped] = v.strip() | |
| return " ".join(f"{k}={v}" for k, v in parts.items()) | |
| db_dsn = parse_db_url(os.getenv("DB_URL", "")) | |
| REPOS = [ | |
| "Ivy-Interactive/Ivy-Framework", | |
| "Ivy-Interactive/Ivy-Tendril", | |
| ] | |
| def fetch_all_stargazers(repo): | |
| """Fetch all stargazers with their starred_at timestamps.""" | |
| result = [] | |
| page = 1 | |
| headers = { | |
| "Accept": "application/vnd.github.star+json", | |
| "User-Agent": "ivy-stars-tracker" | |
| } | |
| while True: | |
| resp = requests.get( | |
| f"https://api.github.com/repos/{repo}/stargazers", | |
| headers=headers, | |
| params={"per_page": 100, "page": page} | |
| ) | |
| resp.raise_for_status() | |
| data = resp.json() | |
| if not data: | |
| break | |
| for item in data: | |
| user = item.get("user") or {} | |
| login = user.get("login") | |
| starred_at = item.get("starred_at") | |
| if login: | |
| result.append((login, starred_at)) | |
| page += 1 | |
| return result | |
| try: | |
| conn = psycopg2.connect(db_dsn) | |
| cur = conn.cursor() | |
| cur.execute(""" | |
| CREATE TABLE IF NOT EXISTS github_stargazers ( | |
| repo_name TEXT, | |
| user_login TEXT, | |
| starred_at TIMESTAMPTZ, | |
| unstarred_at TIMESTAMPTZ, | |
| PRIMARY KEY (repo_name, user_login) | |
| ); | |
| ALTER TABLE github_stargazers ADD COLUMN IF NOT EXISTS starred_at TIMESTAMPTZ; | |
| ALTER TABLE github_stargazers ADD COLUMN IF NOT EXISTS unstarred_at TIMESTAMPTZ; | |
| """) | |
| for REPO in REPOS: | |
| try: | |
| stargazers = fetch_all_stargazers(REPO) | |
| except Exception as e: | |
| print(f"Error fetching stargazers for {REPO}: {e}") | |
| raise SystemExit(1) | |
| print(f"Fetched {len(stargazers)} stargazers for {REPO}.") | |
| if not stargazers: | |
| print(f"No stargazers returned for {REPO}; skipping.") | |
| continue | |
| current = {login for login, _ in stargazers} | |
| starred_at_map = {login: starred_at for login, starred_at in stargazers} | |
| cur.execute(""" | |
| SELECT user_login FROM github_stargazers | |
| WHERE repo_name = %s AND unstarred_at IS NULL; | |
| """, (REPO,)) | |
| previous = {row[0] for row in cur.fetchall()} | |
| new_users = current - previous | |
| lost_users = previous - current | |
| for login, starred_at in stargazers: | |
| if not starred_at: | |
| continue | |
| cur.execute(""" | |
| UPDATE github_stargazers | |
| SET starred_at = COALESCE(starred_at, %s) | |
| WHERE repo_name = %s AND user_login = %s; | |
| """, (starred_at, REPO, login)) | |
| for login in new_users: | |
| starred_at = starred_at_map.get(login) | |
| cur.execute(""" | |
| INSERT INTO github_stargazers (repo_name, user_login, starred_at, unstarred_at) | |
| VALUES (%s, %s, %s, NULL) | |
| ON CONFLICT (repo_name, user_login) | |
| DO NOTHING; | |
| """, (REPO, login, starred_at)) | |
| reactivate_count = 0 | |
| for login in current: | |
| cur.execute(""" | |
| UPDATE github_stargazers SET unstarred_at = NULL | |
| WHERE repo_name = %s AND user_login = %s AND unstarred_at IS NOT NULL; | |
| """, (REPO, login)) | |
| if cur.rowcount > 0: | |
| reactivate_count += 1 | |
| now = datetime.now(timezone.utc) | |
| for login in lost_users: | |
| cur.execute(""" | |
| UPDATE github_stargazers SET unstarred_at = %s | |
| WHERE repo_name = %s AND user_login = %s AND unstarred_at IS NULL; | |
| """, (now, REPO, login)) | |
| print(f"{REPO}: New: {len(new_users)}, Unstarred: {len(lost_users)}, Reactivated: {reactivate_count}. Total active: {len(current)}.") | |
| if new_users: | |
| print("New stargazers since last run:") | |
| for login in sorted(new_users): | |
| print(f" + {login}") | |
| else: | |
| print("No new stargazers since last run.") | |
| if lost_users: | |
| print("Users who unstarred since last run:") | |
| for login in sorted(lost_users): | |
| print(f" - {login}") | |
| else: | |
| print("No users unstarred since last run.") | |
| if reactivate_count > 0: | |
| print(f"Users who re-starred since last run: {reactivate_count}") | |
| conn.commit() | |
| cur.close() | |
| conn.close() | |
| except Exception as e: | |
| print(f"Database error while updating stargazers: {e}") | |
| raise SystemExit(1) | |
| EOF | |