database_replicator/sqlite/
reader.rs

1// ABOUTME: SQLite database introspection and data reading
2// ABOUTME: Functions to list tables, count rows, and read table data
3
4use anyhow::{Context, Result};
5use rusqlite::Connection;
6use std::collections::HashMap;
7
8/// List all user tables in a SQLite database
9///
10/// Queries sqlite_master system table for user-created tables.
11/// Excludes:
12/// - sqlite_* system tables (sqlite_sequence, sqlite_stat1, etc.)
13/// - Internal tables
14///
15/// # Arguments
16///
17/// * `conn` - SQLite database connection
18///
19/// # Returns
20///
21/// Sorted vector of table names
22///
23/// # Examples
24///
25/// ```no_run
26/// # use database_replicator::sqlite::{open_sqlite, reader::list_tables};
27/// # fn example() -> anyhow::Result<()> {
28/// let conn = open_sqlite("database.db")?;
29/// let tables = list_tables(&conn)?;
30/// for table in tables {
31///     println!("Table: {}", table);
32/// }
33/// # Ok(())
34/// # }
35/// ```
36pub fn list_tables(conn: &Connection) -> Result<Vec<String>> {
37    tracing::debug!("Listing tables from SQLite database");
38
39    let mut stmt = conn
40        .prepare(
41            "SELECT name FROM sqlite_master \
42             WHERE type='table' \
43             AND name NOT LIKE 'sqlite_%' \
44             ORDER BY name",
45        )
46        .context("Failed to prepare statement to list tables")?;
47
48    let tables = stmt
49        .query_map([], |row| row.get::<_, String>(0))
50        .context("Failed to query table list")?
51        .collect::<Result<Vec<String>, _>>()
52        .context("Failed to collect table names")?;
53
54    tracing::info!("Found {} user tables in SQLite database", tables.len());
55
56    Ok(tables)
57}
58
59/// Get row count for a specific table
60///
61/// Executes COUNT(*) query on the specified table.
62///
63/// # Arguments
64///
65/// * `conn` - SQLite database connection
66/// * `table` - Table name (should be validated with validate_table_name)
67///
68/// # Returns
69///
70/// Number of rows in the table
71///
72/// # Security
73///
74/// IMPORTANT: Table name should be validated before calling this function
75/// to prevent SQL injection. Use crate::jsonb::validate_table_name().
76///
77/// # Examples
78///
79/// ```no_run
80/// # use database_replicator::sqlite::{open_sqlite, reader::get_table_row_count};
81/// # use database_replicator::jsonb::validate_table_name;
82/// # fn example() -> anyhow::Result<()> {
83/// let conn = open_sqlite("database.db")?;
84/// let table = "users";
85/// validate_table_name(table)?;
86/// let count = get_table_row_count(&conn, table)?;
87/// println!("Table {} has {} rows", table, count);
88/// # Ok(())
89/// # }
90/// ```
91pub fn get_table_row_count(conn: &Connection, table: &str) -> Result<usize> {
92    // Validate table name to prevent SQL injection
93    crate::jsonb::validate_table_name(table).context("Invalid table name for row count query")?;
94
95    tracing::debug!("Getting row count for table '{}'", table);
96
97    // Note: table name is validated above, so it's safe to use in SQL
98    let query = format!("SELECT COUNT(*) FROM {}", crate::utils::quote_ident(table));
99
100    let count: i64 = conn
101        .query_row(&query, [], |row| row.get(0))
102        .with_context(|| format!("Failed to count rows in table '{}'", table))?;
103
104    Ok(count as usize)
105}
106
107/// Read all data from a SQLite table
108///
109/// Returns all rows as a vector of HashMaps, where each HashMap maps
110/// column names to their values.
111///
112/// # Arguments
113///
114/// * `conn` - SQLite database connection
115/// * `table` - Table name (should be validated)
116///
117/// # Returns
118///
119/// Vector of rows, each row is a HashMap<column_name, value>
120///
121/// # Security
122///
123/// Table name should be validated before calling this function.
124///
125/// # Performance
126///
127/// Loads all rows into memory. For very large tables, consider pagination
128/// or streaming approaches.
129///
130/// # Examples
131///
132/// ```no_run
133/// # use database_replicator::sqlite::{open_sqlite, reader::read_table_data};
134/// # use database_replicator::jsonb::validate_table_name;
135/// # fn example() -> anyhow::Result<()> {
136/// let conn = open_sqlite("database.db")?;
137/// let table = "users";
138/// validate_table_name(table)?;
139/// let rows = read_table_data(&conn, table)?;
140/// println!("Read {} rows from {}", rows.len(), table);
141/// # Ok(())
142/// # }
143/// ```
144pub fn read_table_data(
145    conn: &Connection,
146    table: &str,
147) -> Result<Vec<HashMap<String, rusqlite::types::Value>>> {
148    // Validate table name to prevent SQL injection
149    crate::jsonb::validate_table_name(table).context("Invalid table name for data reading")?;
150
151    tracing::info!("Reading all data from table '{}'", table);
152
153    // Note: table name is validated above
154    let query = format!("SELECT * FROM {}", crate::utils::quote_ident(table));
155
156    let mut stmt = conn
157        .prepare(&query)
158        .with_context(|| format!("Failed to prepare statement for table '{}'", table))?;
159
160    // Get column names
161    let column_names: Vec<String> = stmt.column_names().iter().map(|s| s.to_string()).collect();
162
163    tracing::debug!(
164        "Table '{}' has {} columns: {:?}",
165        table,
166        column_names.len(),
167        column_names
168    );
169
170    // Read all rows
171    let rows = stmt
172        .query_map([], |row| {
173            let mut row_map = HashMap::new();
174
175            for (idx, col_name) in column_names.iter().enumerate() {
176                // Get value from row
177                // rusqlite::types::Value represents all SQLite types
178                let value: rusqlite::types::Value = row.get(idx)?;
179                row_map.insert(col_name.clone(), value);
180            }
181
182            Ok(row_map)
183        })
184        .with_context(|| format!("Failed to query rows from table '{}'", table))?
185        .collect::<Result<Vec<_>, _>>()
186        .with_context(|| format!("Failed to collect rows from table '{}'", table))?;
187
188    tracing::info!("Read {} rows from table '{}'", rows.len(), table);
189
190    Ok(rows)
191}
192
193#[cfg(test)]
194mod tests {
195    use super::*;
196
197    fn create_test_db() -> (tempfile::TempDir, std::path::PathBuf) {
198        let temp_dir = tempfile::tempdir().unwrap();
199        let db_path = temp_dir.path().join("test.db");
200
201        let conn = Connection::open(&db_path).unwrap();
202
203        // Create test tables
204        conn.execute(
205            "CREATE TABLE users (
206                id INTEGER PRIMARY KEY,
207                name TEXT NOT NULL,
208                email TEXT,
209                age INTEGER
210            )",
211            [],
212        )
213        .unwrap();
214
215        conn.execute(
216            "CREATE TABLE posts (
217                id INTEGER PRIMARY KEY,
218                user_id INTEGER,
219                title TEXT NOT NULL,
220                content TEXT
221            )",
222            [],
223        )
224        .unwrap();
225
226        // Insert test data
227        conn.execute(
228            "INSERT INTO users (id, name, email, age) VALUES (1, 'Alice', 'alice@example.com', 30)",
229            [],
230        )
231        .unwrap();
232        conn.execute(
233            "INSERT INTO users (id, name, email, age) VALUES (2, 'Bob', 'bob@example.com', 25)",
234            [],
235        )
236        .unwrap();
237        conn.execute(
238            "INSERT INTO users (id, name, email) VALUES (3, 'Charlie', 'charlie@example.com')",
239            [],
240        )
241        .unwrap();
242
243        conn.execute(
244            "INSERT INTO posts (id, user_id, title, content) VALUES (1, 1, 'First Post', 'Hello')",
245            [],
246        )
247        .unwrap();
248
249        (temp_dir, db_path)
250    }
251
252    #[test]
253    fn test_list_tables() {
254        let (_temp_dir, db_path) = create_test_db();
255        let conn = Connection::open(db_path).unwrap();
256
257        let tables = list_tables(&conn).unwrap();
258
259        assert_eq!(tables.len(), 2);
260        assert!(tables.contains(&"users".to_string()));
261        assert!(tables.contains(&"posts".to_string()));
262        assert_eq!(tables, vec!["posts", "users"]); // Should be sorted
263    }
264
265    #[test]
266    fn test_list_tables_excludes_system_tables() {
267        let (_temp_dir, db_path) = create_test_db();
268        let conn = Connection::open(&db_path).unwrap();
269
270        // Create a sequence (creates sqlite_sequence table)
271        conn.execute(
272            "CREATE TABLE test_autoincrement (id INTEGER PRIMARY KEY AUTOINCREMENT)",
273            [],
274        )
275        .unwrap();
276
277        let tables = list_tables(&conn).unwrap();
278
279        // Should not include sqlite_sequence
280        assert!(!tables.iter().any(|t| t.starts_with("sqlite_")));
281    }
282
283    #[test]
284    fn test_get_table_row_count() {
285        let (_temp_dir, db_path) = create_test_db();
286        let conn = Connection::open(db_path).unwrap();
287
288        let users_count = get_table_row_count(&conn, "users").unwrap();
289        assert_eq!(users_count, 3);
290
291        let posts_count = get_table_row_count(&conn, "posts").unwrap();
292        assert_eq!(posts_count, 1);
293    }
294
295    #[test]
296    fn test_get_table_row_count_invalid_table() {
297        let (_temp_dir, db_path) = create_test_db();
298        let conn = Connection::open(db_path).unwrap();
299
300        // SQL injection attempt
301        let result = get_table_row_count(&conn, "users; DROP TABLE users;");
302        assert!(result.is_err());
303        assert!(result
304            .unwrap_err()
305            .to_string()
306            .contains("Invalid table name"));
307    }
308
309    #[test]
310    fn test_read_table_data() {
311        let (_temp_dir, db_path) = create_test_db();
312        let conn = Connection::open(db_path).unwrap();
313
314        let rows = read_table_data(&conn, "users").unwrap();
315
316        assert_eq!(rows.len(), 3);
317
318        // Check first row
319        let first_row = &rows[0];
320        assert!(first_row.contains_key("id"));
321        assert!(first_row.contains_key("name"));
322        assert!(first_row.contains_key("email"));
323        assert!(first_row.contains_key("age"));
324
325        // Check data types
326        match &first_row["id"] {
327            rusqlite::types::Value::Integer(_) => (),
328            _ => panic!("id should be INTEGER"),
329        }
330
331        match &first_row["name"] {
332            rusqlite::types::Value::Text(_) => (),
333            _ => panic!("name should be TEXT"),
334        }
335    }
336
337    #[test]
338    fn test_read_table_data_handles_null() {
339        let (_temp_dir, db_path) = create_test_db();
340        let conn = Connection::open(db_path).unwrap();
341
342        let rows = read_table_data(&conn, "users").unwrap();
343
344        // Find Charlie (row with NULL age)
345        let charlie = rows.iter().find(|r| match &r["name"] {
346            rusqlite::types::Value::Text(s) => s == "Charlie",
347            _ => false,
348        });
349
350        assert!(charlie.is_some());
351        let charlie = charlie.unwrap();
352
353        // Age should be NULL
354        match &charlie["age"] {
355            rusqlite::types::Value::Null => (),
356            _ => panic!("Charlie's age should be NULL"),
357        }
358    }
359
360    #[test]
361    fn test_read_table_data_invalid_table() {
362        let (_temp_dir, db_path) = create_test_db();
363        let conn = Connection::open(db_path).unwrap();
364
365        // SQL injection attempt
366        let result = read_table_data(&conn, "users'; DROP TABLE users; --");
367        assert!(result.is_err());
368        assert!(result
369            .unwrap_err()
370            .to_string()
371            .contains("Invalid table name"));
372    }
373}
database_replicator/sqlite/reader.rs

database_replicator/sqlite/
reader.rs
