Download gists to sqlite

Download-gists.sh

#!/usr/bin/env bash

set -euo pipefail

# Requirements: curl, jq, sqlite3
# Usage: ./download_gists.sh <github_token> <output_db>

GITHUB_TOKEN="$1"
DB_FILE="$2"

# Create SQLite schema
sqlite3 "$DB_FILE" <<EOF
CREATE TABLE IF NOT EXISTS gists (
  id TEXT PRIMARY KEY,
  description TEXT,
  public INTEGER,
  created_at TEXT,
  updated_at TEXT,
  html_url TEXT,
  content TEXT,
  filename TEXT,
  language TEXT
);
EOF

# Pagination setup
PAGE=1
PER_PAGE=100
while :; do
  echo "Fetching page $PAGE..."

  RESPONSE=$(curl -s -H "Authorization: token $GITHUB_TOKEN" \
    "https://api.github.com/gists?per_page=$PER_PAGE&page=$PAGE")

  COUNT=$(echo "$RESPONSE" | jq length)
  if [ "$COUNT" -eq 0 ]; then
    break
  fi

  for row in $(echo "$RESPONSE" | jq -r '.[] | @base64'); do
    _jq() {
      echo "${row}" | base64 --decode | jq -r "$1"
    }

    GIST_ID=$(_jq '.id')
    DESCRIPTION=$(_jq '.description' | sed "s/'/''/g")
    PUBLIC=$(_jq '.public')
    CREATED_AT=$(_jq '.created_at')
    UPDATED_AT=$(_jq '.updated_at')
    HTML_URL=$(_jq '.html_url')

    # Handle multiple files per gist
    FILES=$(_jq '.files | to_entries[] | @base64')
    for file in $FILES; do
      _f_jq() {
        echo "$file" | base64 --decode | jq -r "$1"
      }

      FILENAME=$(_f_jq '.key')
      RAW_URL=$(_f_jq '.value.raw_url')
      LANGUAGE=$(_f_jq '.value.language')

      CONTENT=$(curl -s "$RAW_URL" | sed "s/'/''/g")

      sqlite3 "$DB_FILE" <<EOF
INSERT OR REPLACE INTO gists (id, description, public, created_at, updated_at, html_url, content, filename, language)
VALUES ('$GIST_ID', '$DESCRIPTION', $PUBLIC, '$CREATED_AT', '$UPDATED_AT', '$HTML_URL', '$CONTENT', '$FILENAME', '$LANGUAGE');
EOF
    done
  done

  PAGE=$((PAGE + 1))
done

echo "All gists downloaded into $DB_FILE."