vapor_cli/
db.rs

1use anyhow::{Context, Result};
2use prettytable::{row, Table};
3use rusqlite::{Connection, params};
4use std::fs;
5use std::path::Path;
6use std::time::Duration;
7
8/// Initialize a new SQLite database with enhanced error handling
9pub fn init_database(name: &str) -> Result<()> {
10    let db_path = if name.ends_with(".db") {
11        name.to_string()
12    } else {
13        format!("{}.db", name)
14    };
15
16    // Check if the database already exists
17    if Path::new(&db_path).exists() {
18        println!("Database '{}' already exists.", db_path);
19        // Verify it's a valid SQLite database
20        verify_database_integrity(&db_path)?;
21        return Ok(());
22    }
23
24    // Create the database directory if it doesn't exist
25    if let Some(parent) = Path::new(&db_path).parent() {
26        if !parent.exists() {
27            println!("Creating directory: {:?}", parent);
28            fs::create_dir_all(parent)
29                .with_context(|| format!("Failed to create directory: {:?}. Check permissions and disk space.", parent))?;
30        }
31    }
32
33    // Create a new SQLite database with retry logic
34    let _conn = create_connection_with_retry(&db_path, 3)?;
35
36    // Verify the database was created successfully
37    verify_database_integrity(&db_path)?;
38
39    println!("Successfully created database: {}", db_path);
40    // Connection will be automatically dropped when it goes out of scope
41
42    Ok(())
43}
44
45/// Connect to an existing SQLite database with comprehensive validation
46pub fn connect_database(path: &str) -> Result<()> {
47    // Check if the database exists
48    if !Path::new(path).exists() {
49        anyhow::bail!(
50            "Database '{}' does not exist. Use 'vapor-cli init --name {}' to create it.",
51            path,
52            path.trim_end_matches(".db")
53        );
54    }
55
56    // Check if it's a file (not a directory)
57    let metadata = fs::metadata(path)
58        .with_context(|| format!("Cannot read file metadata for '{}'", path))?;
59    
60    if metadata.is_dir() {
61        anyhow::bail!("'{}' is a directory, not a database file", path);
62    }
63
64    // Try to connect to the database with retry logic
65    let _conn = create_connection_with_retry(path, 3)?;
66
67    // Verify database integrity
68    verify_database_integrity(path)?;
69
70    println!("Successfully connected to database: {}", path);
71    // Connection will be automatically dropped when it goes out of scope
72
73    Ok(())
74}
75
76/// Create a new table in the connected database with enhanced validation
77pub fn create_table(db_path: &str, table_name: &str, columns: &str) -> Result<()> {
78    // Validate database exists and is accessible
79    if !Path::new(db_path).exists() {
80        anyhow::bail!(
81            "Database '{}' does not exist. Use 'vapor-cli init --name {}' to create it.",
82            db_path,
83            db_path.trim_end_matches(".db")
84        );
85    }
86
87    // Connect to the database with retry logic
88    let conn = create_connection_with_retry(db_path, 3)?;
89
90    // Check if table already exists
91    let table_exists = check_table_exists(&conn, table_name)?;
92    if table_exists {
93        println!("Table '{}' already exists in database: {}", table_name, db_path);
94        return Ok(());
95    }
96
97    // Validate column definition syntax
98    validate_column_syntax(columns)?;
99
100    // Create the table with proper error handling
101    let create_table_sql = format!("CREATE TABLE {} ({})", table_name, columns);
102    
103    conn.execute(&create_table_sql, params![])
104        .with_context(|| {
105            format!(
106                "Failed to create table '{}'. Check column syntax: {}",
107                table_name, columns
108            )
109        })?;
110
111    // Verify table was created successfully
112    let table_exists_after = check_table_exists(&conn, table_name)?;
113    if !table_exists_after {
114        anyhow::bail!("Table creation appeared to succeed but table '{}' is not found", table_name);
115    }
116
117    println!("Successfully created table '{}' in database: {}", table_name, db_path);
118    // Connection will be automatically dropped when it goes out of scope
119
120    Ok(())
121}
122
123/// List all tables in the connected database with enhanced error handling
124pub fn list_tables(db_path: &str) -> Result<Vec<String>> {
125    // Validate database exists and is accessible
126    if !Path::new(db_path).exists() {
127        anyhow::bail!(
128            "Database '{}' does not exist. Use 'vapor-cli init --name {}' to create it.",
129            db_path,
130            db_path.trim_end_matches(".db")
131        );
132    }
133
134    // Connect to the database with retry logic
135    let conn = create_connection_with_retry(db_path, 3)?;
136
137    // Query for all tables with error handling
138    let mut stmt = conn.prepare("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
139        .context("Failed to prepare statement for listing tables. Database may be corrupted.")?;
140
141    let table_names = stmt.query_map(params![], |row| row.get::<_, String>(0))
142        .context("Failed to execute query for listing tables")?;
143
144    // Create a pretty table for display
145    let mut table = Table::new();
146    table.add_row(row!["Table Name"]);
147
148    let mut has_tables = false;
149    let mut table_count = 0;
150    let mut tables = Vec::new();
151    
152    for table_name_result in table_names {
153        let name = table_name_result
154            .with_context(|| "Failed to read table name from database result")?;
155        table.add_row(row![&name]);
156        tables.push(name);
157        has_tables = true;
158        table_count += 1;
159    }
160
161    if has_tables {
162        println!("Tables in database '{}':", db_path);
163        table.printstd();
164        println!("Total: {} table(s)", table_count);
165    } else {
166        println!("No tables found in database: {}", db_path);
167        println!("Use 'create-table' command to create your first table.");
168    }
169
170    // Connection will be automatically dropped when it goes out of scope
171    Ok(tables)
172}
173
174/// Create a database connection with retry logic for handling temporary issues
175fn create_connection_with_retry(db_path: &str, max_retries: u32) -> Result<Connection> {
176    let mut last_error = None;
177    
178    for attempt in 1..=max_retries {
179        match Connection::open(db_path) {
180            Ok(conn) => {
181                if attempt > 1 {
182                    println!("Connection succeeded on attempt {}", attempt);
183                }
184                return Ok(conn);
185            }
186            Err(e) => {
187                last_error = Some(e);
188                if attempt < max_retries {
189                    println!("Connection attempt {} failed, retrying...", attempt);
190                    std::thread::sleep(Duration::from_millis(100 * attempt as u64));
191                }
192            }
193        }
194    }
195    
196    Err(last_error.unwrap())
197        .with_context(|| format!(
198            "Failed to connect to database '{}' after {} attempts. Database may be locked or corrupted.",
199            db_path, max_retries
200        ))
201}
202
203/// Verify database integrity
204fn verify_database_integrity(db_path: &str) -> Result<()> {
205    let conn = Connection::open(db_path)
206        .with_context(|| format!("Cannot open database '{}' for integrity check", db_path))?;
207    
208    // Run a simple integrity check
209    let integrity_result: String = conn.query_row("PRAGMA integrity_check", [], |row| row.get(0))
210        .with_context(|| format!("Database '{}' failed integrity check", db_path))?;
211    
212    if integrity_result != "ok" {
213        anyhow::bail!("Database integrity check failed: {}", integrity_result);
214    }
215    
216    // Test basic functionality
217    let test_result: i32 = conn.query_row("SELECT 1", [], |row| row.get(0))
218        .with_context(|| format!("Database '{}' failed basic functionality test", db_path))?;
219    
220    if test_result != 1 {
221        anyhow::bail!("Basic functionality test failed: expected 1, got {}", test_result);
222    }
223    
224    Ok(())
225}
226
227/// Check if a table exists in the database
228fn check_table_exists(conn: &Connection, table_name: &str) -> Result<bool> {
229    let mut stmt = conn.prepare("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name=?1")
230        .context("Failed to prepare table existence check query")?;
231    
232    let count: i64 = stmt.query_row(params![table_name], |row| row.get(0))
233        .with_context(|| format!("Failed to check if table '{}' exists", table_name))?;
234    
235    Ok(count > 0)
236}
237
238/// Validate column definition syntax
239fn validate_column_syntax(columns: &str) -> Result<()> {
240    let columns = columns.trim();
241    
242    // Check for basic SQL injection patterns
243    let dangerous_patterns = ["DROP", "DELETE", "INSERT", "UPDATE", "EXEC"];
244    let columns_upper = columns.to_uppercase();
245    
246    for pattern in &dangerous_patterns {
247        if columns_upper.contains(pattern) {
248            anyhow::bail!("Column definition contains potentially dangerous SQL keyword: {}", pattern);
249        }
250    }
251    
252    // Check for balanced parentheses
253    let open_parens = columns.chars().filter(|&c| c == '(').count();
254    let close_parens = columns.chars().filter(|&c| c == ')').count();
255    
256    if open_parens != close_parens {
257        anyhow::bail!("Column definition has unbalanced parentheses");
258    }
259    
260    // Check for at least one column definition
261    if !columns.contains(' ') && !columns.contains(',') {
262        anyhow::bail!("Column definition appears incomplete. Example: 'id INTEGER PRIMARY KEY, name TEXT'");
263    }
264    
265    Ok(())
266}
vapor_cli/db.rs

vapor_cli/
db.rs
