Skip to content

Instantly share code, notes, and snippets.

@Sdy603
Last active October 30, 2024 16:23
Show Gist options
  • Save Sdy603/0b96d838bb171ff17d9c7edc8ba2fbca to your computer and use it in GitHub Desktop.
Save Sdy603/0b96d838bb171ff17d9c7edc8ba2fbca to your computer and use it in GitHub Desktop.
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