Last active
October 30, 2024 16:23
-
-
Save Sdy603/0b96d838bb171ff17d9c7edc8ba2fbca to your computer and use it in GitHub Desktop.
This file contains 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
using System; | |
using System.Collections.Generic; | |
using System.Data; | |
using System.Net.Http; | |
using System.Text.Json; | |
using System.Threading.Tasks; | |
using Npgsql; | |
using System.IdentityModel.Tokens.Jwt; | |
using Microsoft.IdentityModel.Tokens; | |
using System.Security.Cryptography; | |
class Program | |
{ | |
private static HttpClient _httpClient = new HttpClient(); | |
static async Task Main(string[] args) | |
{ | |
string dbConnectionString = "postgres://<ConnectionStringFromDX>.db.getdx.net/client"; | |
string privateKeyPath = Environment.GetEnvironmentVariable("GITHUB_RSA_PRIVATE_KEY_PATH"); | |
string appId = Environment.GetEnvironmentVariable("GITHUB_APP_ID"); | |
string installationId = Environment.GetEnvironmentVariable("GITHUB_INSTALLATION_ID"); | |
bool verbose = Environment.GetEnvironmentVariable("VERBOSE")?.ToLower() == "true"; | |
if (string.IsNullOrEmpty(privateKeyPath) || string.IsNullOrEmpty(appId)) | |
throw new Exception("GitHub App RSA private key path or App ID not found."); | |
string privateKey = System.IO.File.ReadAllText(privateKeyPath); | |
string jwtToken = CreateJwt(appId, privateKey); | |
string accessToken = await GetInstallationToken(jwtToken, installationId); | |
_httpClient.DefaultRequestHeaders.Add("Authorization", $"Bearer {accessToken}"); | |
_httpClient.DefaultRequestHeaders.Add("Accept", "application/vnd.github+json"); | |
var repos = QueryDatabase(dbConnectionString); | |
foreach (var repo in repos) | |
{ | |
string url = $"https://api.github.com/repos/{repo.Login}/{repo.Name}/properties/values"; | |
if (verbose) Console.WriteLine($"Requesting properties for Repo ID: {repo.RepoId} - URL: {url}"); | |
try | |
{ | |
var response = await _httpClient.GetAsync(url); | |
if (await HandleRateLimit(response)) | |
response = await _httpClient.GetAsync(url); | |
if (response.IsSuccessStatusCode) | |
{ | |
var content = await response.Content.ReadAsStringAsync(); | |
var properties = JsonSerializer.Deserialize<List<Dictionary<string, string>>>(content); | |
await InsertCustomPropertiesIntoDatabase(dbConnectionString, repo, properties, verbose); | |
} | |
else | |
{ | |
Console.WriteLine($"Failed request for {repo.Name}. Status: {response.StatusCode}"); | |
} | |
} | |
catch (Exception ex) | |
{ | |
Console.WriteLine($"Error for Repo ID {repo.RepoId}: {ex.Message}"); | |
} | |
} | |
} | |
static async Task InsertCustomPropertiesIntoDatabase( | |
string connectionString, Repo repo, List<Dictionary<string, string>> properties, bool verbose) | |
{ | |
using var conn = new NpgsqlConnection(connectionString); | |
await conn.OpenAsync(); | |
foreach (var property in properties) | |
{ | |
string name = property.GetValueOrDefault("property_name", "N/A").Replace("'", "''"); | |
string value = property.GetValueOrDefault("value", "N/A").Replace("'", "''"); | |
// Check if the record already exists | |
bool exists = await CheckIfRecordExists(conn, repo.RepoId, name, value); | |
if (exists) | |
{ | |
if (verbose) Console.WriteLine($"Property '{name}' with value '{value}' for repo '{repo.Name}' already exists. Skipping insert."); | |
continue; | |
} | |
// Insert the new record if it does not exist | |
string sql = $@" | |
INSERT INTO github_custom_repo_properties | |
(repo_name, repository_id, custom_property_name, custom_property_value, retrieved_at) | |
VALUES ('{repo.Name.Replace("'", "''")}', {repo.RepoId}, '{name}', '{value}', '{DateTime.UtcNow:O}'); | |
"; | |
using var cmd = new NpgsqlCommand(sql, conn); | |
try | |
{ | |
await cmd.ExecuteNonQueryAsync(); | |
Console.WriteLine($"Inserted property '{name}' for repo '{repo.Name}'."); | |
} | |
catch (Exception ex) | |
{ | |
Console.WriteLine($"Error inserting property '{name}' for repo '{repo.Name}': {ex.Message}"); | |
} | |
} | |
} | |
static async Task<bool> CheckIfRecordExists(NpgsqlConnection conn, int repoId, string propertyName, string propertyValue) | |
{ | |
string sql = @" | |
SELECT 1 FROM github_custom_repo_properties | |
WHERE repository_id = @repoId | |
AND custom_property_name = @propertyName | |
AND custom_property_value = @propertyValue | |
LIMIT 1; | |
"; | |
using var cmd = new NpgsqlCommand(sql, conn); | |
cmd.Parameters.AddWithValue("repoId", repoId); | |
cmd.Parameters.AddWithValue("propertyName", propertyName); | |
cmd.Parameters.AddWithValue("propertyValue", propertyValue); | |
var result = await cmd.ExecuteScalarAsync(); | |
return result != null; | |
} | |
static List<Repo> QueryDatabase(string connectionString) | |
{ | |
var repos = new List<Repo>(); | |
using (var conn = new NpgsqlConnection(connectionString)) | |
{ | |
conn.Open(); | |
using (var cmd = new NpgsqlCommand("SELECT id AS repo_ID, organization AS login, name FROM repos", conn)) | |
{ | |
using (var reader = cmd.ExecuteReader()) | |
{ | |
while (reader.Read()) | |
{ | |
repos.Add(new Repo | |
{ | |
RepoId = reader.GetInt32(0), | |
Login = reader.GetString(1), | |
Name = reader.GetString(2) | |
}); | |
} | |
} | |
} | |
} | |
return repos; | |
} | |
static string CreateJwt(string appId, string privateKey) | |
{ | |
var rsa = RSA.Create(); | |
rsa.ImportFromPem(privateKey); | |
var credentials = new SigningCredentials(new RsaSecurityKey(rsa), SecurityAlgorithms.RsaSha256); | |
var handler = new JwtSecurityTokenHandler(); | |
var tokenDescriptor = new SecurityTokenDescriptor | |
{ | |
Issuer = appId, | |
Expires = DateTime.UtcNow.AddMinutes(10), | |
SigningCredentials = credentials | |
}; | |
var token = handler.CreateToken(tokenDescriptor); | |
return handler.WriteToken(token); | |
} | |
static async Task<string> GetInstallationToken(string jwtToken, string installationId) | |
{ | |
var request = new HttpRequestMessage(HttpMethod.Post, | |
$"https://api.github.com/app/installations/{installationId}/access_tokens"); | |
request.Headers.Add("Authorization", $"Bearer {jwtToken}"); | |
request.Headers.Add("Accept", "application/vnd.github+json"); | |
var response = await _httpClient.SendAsync(request); | |
if (response.IsSuccessStatusCode) | |
{ | |
var content = await response.Content.ReadAsStringAsync(); | |
var json = JsonSerializer.Deserialize<Dictionary<string, string>>(content); | |
return json["token"]; | |
} | |
else | |
{ | |
throw new Exception($"Failed to get installation token. Status: {response.StatusCode}"); | |
} | |
} | |
static async Task<bool> HandleRateLimit(HttpResponseMessage response) | |
{ | |
if ((int)response.StatusCode == 403 && | |
response.Headers.Contains("X-RateLimit-Remaining") && | |
response.Headers.Contains("X-RateLimit-Reset")) | |
{ | |
var remaining = int.Parse(response.Headers.GetValues("X-RateLimit-Remaining").FirstOrDefault() ?? "0"); | |
if (remaining == 0) | |
{ | |
var reset = int.Parse(response.Headers.GetValues("X-RateLimit-Reset").FirstOrDefault() ?? "0"); | |
var waitTime = reset - DateTimeOffset.UtcNow.ToUnixTimeSeconds(); | |
Console.WriteLine($"Rate limit exceeded. Waiting {waitTime} seconds."); | |
await Task.Delay(waitTime * 1000); | |
return true; | |
} | |
} | |
return false; | |
} | |
} | |
class Repo | |
{ | |
public int RepoId { get; set; } | |
public string Login { get; set; } | |
public string Name { get; set; } | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment