141 lines
4.7 KiB
PowerShell
Executable File
141 lines
4.7 KiB
PowerShell
Executable File
# ==============================================================================
|
||
# Database Sample Export Script (WINDOWS VERSİYONU)
|
||
# Bu script önemli tablolardan örnek veri çeker ve mds/DATABASE_SAMPLES.md'ye yazar
|
||
# AI asistanların veritabanı yapısını anlaması için kullanılır
|
||
# ==============================================================================
|
||
|
||
# Kullanım:
|
||
# 1. Önce SSM port forwarding başlat: dbconnect
|
||
# 2. Yeni PowerShell aç ve çalıştır: .\scripts\export-db-samples.ps1
|
||
|
||
$OutputFile = "mds\DATABASE_SAMPLES.md"
|
||
$DbHost = "localhost"
|
||
$DbPort = "15432"
|
||
$DbUser = "suggestbet"
|
||
$DbName = "boilerplate_db"
|
||
$env:PGPASSWORD = "SuGGesT2026SecuRe"
|
||
|
||
# psql yolunu kontrol et
|
||
$psqlPath = "psql"
|
||
if (-not (Get-Command $psqlPath -ErrorAction SilentlyContinue)) {
|
||
# PostgreSQL kurulu değilse yaygın yolları dene
|
||
$possiblePaths = @(
|
||
"C:\Program Files\PostgreSQL\18\bin\psql.exe",
|
||
"C:\Program Files\PostgreSQL\17\bin\psql.exe",
|
||
"C:\Program Files\PostgreSQL\16\bin\psql.exe",
|
||
"C:\Program Files\PostgreSQL\15\bin\psql.exe"
|
||
)
|
||
foreach ($path in $possiblePaths) {
|
||
if (Test-Path $path) {
|
||
$psqlPath = $path
|
||
break
|
||
}
|
||
}
|
||
}
|
||
|
||
# Veritabanı bağlantı kontrolü
|
||
try {
|
||
$test = & $psqlPath -h $DbHost -p $DbPort -U $DbUser -d $DbName -c "SELECT 1" 2>&1
|
||
if ($LASTEXITCODE -ne 0) { throw "Baglanti hatasi" }
|
||
} catch {
|
||
Write-Host "X Veritabanina baglanilamadi!" -ForegroundColor Red
|
||
Write-Host "Once SSM port forwarding baslat: dbconnect" -ForegroundColor Yellow
|
||
Write-Host "PostgreSQL kurulu oldugundan emin ol" -ForegroundColor Yellow
|
||
exit 1
|
||
}
|
||
|
||
Write-Host "Veritabani ornekleri cekiliyor..." -ForegroundColor Cyan
|
||
|
||
$currentDate = Get-Date -Format "yyyy-MM-dd HH:mm"
|
||
|
||
# Helper function
|
||
function Run-Query($query) {
|
||
& $psqlPath -h $DbHost -p $DbPort -U $DbUser -d $DbName -t -c $query 2>$null
|
||
}
|
||
|
||
# Build content as array of lines
|
||
$lines = @()
|
||
$lines += "# Database Sample Data"
|
||
$lines += ""
|
||
$lines += "Bu dosya AI asistanlarin veritabani yapisini anlamasi icin ornek veriler icerir."
|
||
$lines += "**Son Guncelleme:** $currentDate"
|
||
$lines += ""
|
||
$lines += "> Bu dosya otomatik olusturulmustur. Elle duzenlemeyin."
|
||
$lines += "> Script: ``scripts/export-db-samples.ps1``"
|
||
$lines += ""
|
||
$lines += "---"
|
||
$lines += ""
|
||
$lines += "## Tablo Istatistikleri"
|
||
$lines += ""
|
||
$lines += "| Tablo | Kayit Sayisi |"
|
||
$lines += "|-------|--------------|"
|
||
|
||
# Tablo sayıları
|
||
$tables = @("countries", "leagues", "teams", "players", "matches", "predictions", "odd_categories", "odd_selections", "match_team_stats", "live_matches", "users", "app_settings")
|
||
foreach ($table in $tables) {
|
||
$count = (Run-Query "SELECT COUNT(*) FROM $table;").Trim()
|
||
if ($count) {
|
||
$lines += "| $table | $count |"
|
||
}
|
||
}
|
||
|
||
$lines += ""
|
||
$lines += "---"
|
||
$lines += ""
|
||
$lines += "## Matches (Son 5 Mac)"
|
||
$lines += '```json'
|
||
$matchesJson = Run-Query "SELECT json_agg(t) FROM (SELECT id, match_name, sport, score_home, score_away, state, to_timestamp(mst_utc/1000) as match_time FROM matches ORDER BY mst_utc DESC LIMIT 5) t;"
|
||
$lines += $matchesJson
|
||
$lines += '```'
|
||
$lines += ""
|
||
|
||
$lines += "## Leagues (Ilk 10)"
|
||
$lines += '```json'
|
||
$leaguesJson = Run-Query "SELECT json_agg(t) FROM (SELECT id, name, sport, country_id FROM leagues LIMIT 10) t;"
|
||
$lines += $leaguesJson
|
||
$lines += '```'
|
||
$lines += ""
|
||
|
||
$lines += "## Teams (Ilk 10)"
|
||
$lines += '```json'
|
||
$teamsJson = Run-Query "SELECT json_agg(t) FROM (SELECT id, name, sport, logo_url FROM teams LIMIT 10) t;"
|
||
$lines += $teamsJson
|
||
$lines += '```'
|
||
$lines += ""
|
||
|
||
$lines += "## Countries (Ilk 10)"
|
||
$lines += '```json'
|
||
$countriesJson = Run-Query "SELECT json_agg(t) FROM (SELECT id, name, flag_url FROM countries LIMIT 10) t;"
|
||
$lines += $countriesJson
|
||
$lines += '```'
|
||
$lines += ""
|
||
|
||
$lines += "## Predictions (Son 5)"
|
||
$lines += '```json'
|
||
$predictionsJson = Run-Query "SELECT json_agg(t) FROM (SELECT match_id, created_at FROM predictions ORDER BY created_at DESC LIMIT 5) t;"
|
||
$lines += $predictionsJson
|
||
$lines += '```'
|
||
$lines += ""
|
||
|
||
$lines += "## Match Team Stats (Ornek 5)"
|
||
$lines += '```json'
|
||
$statsJson = Run-Query "SELECT json_agg(t) FROM (SELECT match_id, team_id, possession_percentage, shots_on_target, shots_off_target FROM match_team_stats LIMIT 5) t;"
|
||
$lines += $statsJson
|
||
$lines += '```'
|
||
$lines += ""
|
||
|
||
$lines += "## App Settings"
|
||
$lines += '```json'
|
||
$settingsJson = Run-Query "SELECT json_agg(t) FROM (SELECT key, value FROM app_settings) t;"
|
||
$lines += $settingsJson
|
||
$lines += '```'
|
||
$lines += ""
|
||
$lines += "---"
|
||
$lines += ""
|
||
$lines += "_Bu dosya scripts/export-db-samples.ps1 tarafindan olusturulmustur._"
|
||
|
||
# Dosyaya yaz
|
||
$lines | Out-File -FilePath $OutputFile -Encoding utf8
|
||
|
||
Write-Host "Export tamamlandi: $OutputFile" -ForegroundColor Green
|