Something went wrong. Try again.
A lexicon-driven AppView for ATProto. happyview.dev
backfill firehose jetstream atproto appview oauth lexicon
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365use regex::Regex;use std::collections::HashSet;use std::fs;use std::path::{Path, PathBuf};
/// A single SQL transformation with location info for reporting.struct SqlChange { file: PathBuf, line: usize, original: String, converted: String, warnings: Vec<String>,}
fn main() { let args: Vec<String> = std::env::args().collect();
let mut dir = std::env::current_dir().expect("cannot determine current directory"); let mut dry_run = false; let mut report_only = false;
let mut i = 1; while i < args.len() { match args[i].as_str() { "--dry-run" => dry_run = true, "--report" => report_only = true, "--help" | "-h" => { eprintln!("Usage: migrate-lua-sql [OPTIONS] [DIRECTORY]"); eprintln!(); eprintln!("Converts Postgres SQL in Lua db.raw() calls to SQLite syntax."); eprintln!(); eprintln!("Options:"); eprintln!(" --dry-run Show what would change without modifying files"); eprintln!(" --report Only report changes and warnings (implies --dry-run)"); eprintln!(" -h, --help Show this help"); std::process::exit(0); } other => { dir = PathBuf::from(other); } } i += 1; }
if report_only { dry_run = true; }
let lua_files = find_lua_files(&dir); if lua_files.is_empty() { eprintln!("No .lua files found in {}", dir.display()); std::process::exit(0); }
let mut total_changes = 0; let mut total_warnings = 0; let mut files_modified = 0;
for path in &lua_files { let content = match fs::read_to_string(path) { Ok(c) => c, Err(e) => { eprintln!("Warning: cannot read {}: {}", path.display(), e); continue; } };
let (new_content, changes) = transform_file(path, &content);
if changes.is_empty() { continue; }
files_modified += 1;
for change in &changes { total_changes += 1; total_warnings += change.warnings.len();
if report_only || dry_run { println!("--- {}:{}", change.file.display(), change.line); println!(" Original: {}", change.original.trim()); println!(" Converted: {}", change.converted.trim()); for w in &change.warnings { println!(" WARNING: {}", w); } println!(); } }
if !dry_run && let Err(e) = fs::write(path, &new_content) { eprintln!("Error writing {}: {}", path.display(), e); } }
let mode = if report_only { "Report" } else if dry_run { "Dry run" } else { "Applied" };
println!( "{}: {} changes across {} files ({} warnings)", mode, total_changes, files_modified, total_warnings );}
fn find_lua_files(dir: &Path) -> Vec<PathBuf> { let mut files = Vec::new(); find_lua_files_recursive(dir, &mut files); files.sort(); files}
fn find_lua_files_recursive(dir: &Path, files: &mut Vec<PathBuf>) { let entries = match fs::read_dir(dir) { Ok(e) => e, Err(_) => return, }; for entry in entries.flatten() { let path = entry.path(); if path.is_dir() { find_lua_files_recursive(&path, files); } else if path.extension().and_then(|s| s.to_str()) == Some("lua") { files.push(path); } }}
/// Check if a string looks like it contains SQL./// Requires at least one strong SQL keyword (statement-level), not just connectors.fn looks_like_sql(s: &str) -> bool { let upper = s.to_uppercase(); let strong_keywords = [ "SELECT ", "INSERT ", "UPDATE ", "DELETE ", "FROM ", "WHERE ", "JOIN ", "ORDER BY", "GROUP BY", "HAVING ", "LIMIT ", "VALUES", "ON CONFLICT", ]; strong_keywords.iter().any(|kw| upper.contains(kw))}
/// Transform an entire file, returning the new content and a list of changes.////// Scans every quoted string literal on each line. If the string looks like SQL/// (contains common SQL keywords), it applies Postgres-to-SQLite transformations./// This handles both inline db.raw("...") calls and SQL built via variable/// assignment with `..` concatenation.fn transform_file(path: &Path, content: &str) -> (String, Vec<SqlChange>) { let mut changes = Vec::new(); let mut result = String::with_capacity(content.len());
// Match double-quoted and single-quoted string literals let sql_string_re = Regex::new(r#"("(?:[^"\\]|\\.)*"|'(?:[^'\\]|\\.)*')"#).unwrap();
for (line_idx, line) in content.lines().enumerate() { let mut new_line = line.to_string(); let mut line_changed = false;
// Find all string literals on this line let matches: Vec<_> = sql_string_re.find_iter(line).collect();
// Process matches in reverse order so byte offsets remain valid for m in matches.into_iter().rev() { let full_match = m.as_str(); let quote_char = full_match.chars().next().unwrap(); let inner = &full_match[1..full_match.len() - 1];
// Only process strings that look like SQL if !looks_like_sql(inner) { continue; }
let (converted, warnings) = convert_sql(inner);
if converted != inner || !warnings.is_empty() { let replacement = format!("{}{}{}", quote_char, converted, quote_char); new_line = format!( "{}{}{}", &new_line[..m.start()], replacement, &new_line[m.end()..] ); line_changed = true;
changes.push(SqlChange { file: path.to_path_buf(), line: line_idx + 1, original: inner.to_string(), converted: converted.clone(), warnings, }); } }
if line_changed { result.push_str(&new_line); } else { result.push_str(line); } result.push('\n'); }
// Preserve trailing newline behavior of original if !content.ends_with('\n') && result.ends_with('\n') { result.pop(); }
(result, changes)}
/// Convert a single SQL string from Postgres to SQLite syntax./// Returns (converted_sql, warnings).fn convert_sql(sql: &str) -> (String, Vec<String>) { let mut result = sql.to_string(); let mut warnings = Vec::new();
// Check for unsupported patterns first (before any transformations) check_unsupported_patterns(&result, &mut warnings);
// 1. JSON operators: must be done before $N replacement since -> uses > char result = convert_json_operators(&result);
// 2. $1, $2, ... -> ? (in order) result = convert_placeholders(&result);
// 3. ILIKE -> LIKE (SQLite LIKE is already case-insensitive for ASCII) let ilike_re = Regex::new(r"(?i)\bILIKE\b").unwrap(); result = ilike_re.replace_all(&result, "LIKE").to_string();
// 4. NOW() +/- INTERVAL '...' -> datetime('now', '...') // Must be done before bare NOW() replacement result = convert_now_interval(&result);
// 5. Bare NOW() -> datetime('now') let now_re = Regex::new(r"(?i)\bNOW\(\)").unwrap(); result = now_re.replace_all(&result, "datetime('now')").to_string();
// 6. Boolean literals: true -> 1, false -> 0 let true_re = Regex::new(r"\btrue\b").unwrap(); let false_re = Regex::new(r"\bfalse\b").unwrap(); result = true_re.replace_all(&result, "1").to_string(); result = false_re.replace_all(&result, "0").to_string();
(result, warnings)}
/// Convert $1, $2, etc. to ? in order.fn convert_placeholders(sql: &str) -> String { let re = Regex::new(r"\$(\d+)").unwrap();
// Track which placeholder numbers exist (for potential future validation) let mut _seen: HashSet<u32> = HashSet::new(); for cap in re.captures_iter(sql) { let n: u32 = cap[1].parse().unwrap_or(0); _seen.insert(n); }
// Replace all $N with ? re.replace_all(sql, "?").to_string()}
/// Convert Postgres JSON operators to json_extract().////// Handles chains like:/// col->>'key' => json_extract(col, '$.key')/// col->'a'->'b'->>'c' => json_extract(col, '$.a.b.c')/// col::jsonb->'a'->>'b' => json_extract(col, '$.a.b')fn convert_json_operators(sql: &str) -> String { // Match a chain of -> / ->> operators starting from an identifier // The identifier may have an optional ::jsonb cast which we strip // Use a non-capturing group for the repetition so we capture the ENTIRE chain let chain_re = Regex::new(r"(\b[a-zA-Z_][a-zA-Z0-9_.]*)(::jsonb)?((?:\s*->>?\s*'[^']*')+)").unwrap();
chain_re .replace_all(sql, |caps: ®ex::Captures| { let col = &caps[1]; // caps[2] is optional ::jsonb cast — we strip it let chain_str = &caps[3]; // now captures the full chain
// Parse each arrow and key from the chain let arrow_re = Regex::new(r"(->>?)\s*'([^']*)'").unwrap(); let mut keys: Vec<String> = Vec::new();
for arrow_cap in arrow_re.captures_iter(chain_str) { keys.push(arrow_cap[2].to_string()); }
let json_path = format!("$.{}", keys.join(".")); format!("json_extract({}, '{}')", col, json_path) }) .to_string()}
/// Convert NOW() +/- INTERVAL 'X unit' to datetime('now', '+/-X unit').fn convert_now_interval(sql: &str) -> String { let re = Regex::new(r"(?i)\bNOW\(\)\s*([+-])\s*INTERVAL\s*'([^']+)'").unwrap();
re.replace_all(sql, |caps: ®ex::Captures| { let sign = &caps[1]; let interval = &caps[2]; format!("datetime('now', '{}{}')", sign, interval) }) .to_string()}
/// Check for patterns that cannot be auto-converted and add warnings.fn check_unsupported_patterns(sql: &str, warnings: &mut Vec<String>) { // JSONB ? operator (contains-key): record->'approvedGames' ? $1 let jsonb_contains_re = Regex::new(r"'\s+\?\s+\$\d+").unwrap(); if jsonb_contains_re.is_match(sql) { warnings.push( "JSONB '?' (contains-key) operator detected -- no direct SQLite equivalent. \ Consider using json_each() with an EXISTS subquery." .to_string(), ); }
// make_interval() if sql.to_lowercase().contains("make_interval") { warnings .push("make_interval() is Postgres-specific -- needs manual conversion.".to_string()); }
// SIMILAR TO let similar_re = Regex::new(r"(?i)\bSIMILAR\s+TO\b").unwrap(); if similar_re.is_match(sql) { warnings.push("SIMILAR TO is Postgres-specific -- use LIKE or GLOB instead.".to_string()); }
// ANY() / ALL() array operators let any_re = Regex::new(r"(?i)\bANY\s*\(").unwrap(); let all_re = Regex::new(r"(?i)\bALL\s*\(").unwrap(); if any_re.is_match(sql) { warnings.push("ANY() array operator detected -- no direct SQLite equivalent.".to_string()); } if all_re.is_match(sql) { warnings.push("ALL() array operator detected -- no direct SQLite equivalent.".to_string()); }
// ::type casts other than ::jsonb let cast_re = Regex::new(r"::[a-zA-Z_]+").unwrap(); for m in cast_re.find_iter(sql) { let cast = m.as_str(); if cast != "::jsonb" { warnings.push(format!( "Type cast '{}' detected -- may need manual conversion.", cast )); } }}