Skip to main content

clm_rust_core/
db.rs

1use std::env;
2use std::fs;
3use std::io::{Read, Write};
4use std::net::{TcpStream, Shutdown};
5use std::path::{Path, PathBuf};
6use std::time::Duration;
7use rusqlite::{params, Connection};
8use std::cell::RefCell;
9use std::collections::HashMap;
10use crate::error::{ClmError, StorageErrorCode, Result};
11
12thread_local! {
13    pub static TEST_SANDBOX_ACTIVE: std::cell::Cell<bool> = std::cell::Cell::new(false);
14    pub static TEST_SANDBOX_DB_ID: std::cell::RefCell<Option<String>> = std::cell::RefCell::new(None);
15    pub static TEST_SANDBOX_DEPTH: std::cell::Cell<usize> = std::cell::Cell::new(0);
16    static IN_MEMORY_CONNS: RefCell<HashMap<String, Connection>> = RefCell::new(HashMap::new());
17}
18
19pub fn clear_in_memory_connections() {
20    IN_MEMORY_CONNS.with(|conns| conns.borrow_mut().clear());
21}
22
23/// Canonical MCard schema (v3.0.3) embedded at compile-time as single source of truth (SSOT).
24pub const CANONICAL_SCHEMA: &str = include_str!("../types/schemas/mcard_schema.sql");
25
26#[cfg(any(feature = "native", feature = "db"))]
27use std::sync::{Arc, Mutex, OnceLock};
28#[cfg(any(feature = "native", feature = "db"))]
29use r2d2::Pool;
30#[cfg(any(feature = "native", feature = "db"))]
31use r2d2_sqlite::SqliteConnectionManager;
32
33#[cfg(any(feature = "native", feature = "db"))]
34pub mod migrations;
35
36#[cfg(any(feature = "native", feature = "db"))]
37pub mod compaction;
38
39/// High-Concurrency Dual-Pool SQLite Manager.
40///
41/// Provides a dedicated single writer connection alongside an `r2d2` multi-reader
42/// connection pool configured with WAL mode, normal synchronous mode, and busy timeouts.
43#[cfg(any(feature = "native", feature = "db"))]
44#[derive(Clone)]
45pub struct DbPillarPool {
46    writer: Arc<Mutex<Connection>>,
47    reader_pool: Pool<SqliteConnectionManager>,
48    db_path: PathBuf,
49}
50
51#[cfg(any(feature = "native", feature = "db"))]
52impl DbPillarPool {
53    /// Initializes a dual-pool manager for the given pillar database path.
54    pub fn new(pillar: &str, clm_root: &str) -> Result<Self> {
55        let normalized = get_pillar_filename(pillar);
56        let db_path = get_db_path(&normalized, clm_root);
57        let db_path_str = db_path.to_string_lossy();
58
59        if !db_path_str.starts_with("file:") {
60            if let Some(parent) = db_path.parent() {
61                let _ = fs::create_dir_all(parent);
62            }
63        }
64
65        // 1. Initialize Writer Connection with WAL pragma & run migrations
66        let mut writer_conn = Connection::open_with_flags(
67            &db_path,
68            rusqlite::OpenFlags::SQLITE_OPEN_READ_WRITE
69                | rusqlite::OpenFlags::SQLITE_OPEN_CREATE
70                | rusqlite::OpenFlags::SQLITE_OPEN_URI,
71        )?;
72        writer_conn.busy_timeout(std::time::Duration::from_millis(5000))?;
73        let _ = writer_conn.pragma_update(None, "journal_mode", "WAL");
74        let _ = writer_conn.pragma_update(None, "synchronous", "NORMAL");
75        let _ = writer_conn.pragma_update(None, "foreign_keys", "ON");
76
77        // Apply versioned schema migrations on the writer connection
78        let _ = migrations::apply_migrations(&mut writer_conn, &normalized);
79
80        // 2. Initialize Reader Pool
81        let manager = SqliteConnectionManager::file(&db_path)
82            .with_flags(
83                rusqlite::OpenFlags::SQLITE_OPEN_READ_WRITE
84                    | rusqlite::OpenFlags::SQLITE_OPEN_CREATE
85                    | rusqlite::OpenFlags::SQLITE_OPEN_URI,
86            )
87            .with_init(|conn| {
88                let _ = conn.busy_timeout(std::time::Duration::from_millis(5000));
89                let _ = conn.pragma_update(None, "journal_mode", "WAL");
90                let _ = conn.pragma_update(None, "synchronous", "NORMAL");
91                let _ = conn.pragma_update(None, "query_only", true);
92                Ok(())
93            });
94
95        let reader_pool = Pool::builder()
96            .max_size(16)
97            .min_idle(Some(2))
98            .connection_timeout(std::time::Duration::from_millis(5000))
99            .build(manager)
100            .map_err(|e| {
101                ClmError::storage(
102                    StorageErrorCode::DatabaseUnavailable,
103                    format!("Failed to build SQLite reader connection pool for {}: {}", normalized, e),
104                )
105            })?;
106
107        Ok(Self {
108            writer: Arc::new(Mutex::new(writer_conn)),
109            reader_pool,
110            db_path,
111        })
112    }
113
114    /// Returns an `Arc<Mutex<Connection>>` handle to the dedicated writer connection.
115    pub fn writer(&self) -> Arc<Mutex<Connection>> {
116        self.writer.clone()
117    }
118
119    /// Acquires a connection from the multi-reader pool without locking the writer.
120    pub fn reader(&self) -> Result<r2d2::PooledConnection<SqliteConnectionManager>> {
121        self.reader_pool.get().map_err(|e| {
122            ClmError::storage(
123                StorageErrorCode::DatabaseUnavailable,
124                format!("Failed to acquire connection from reader pool: {}", e),
125            )
126        })
127    }
128
129    /// Returns the underlying database path or URI.
130    pub fn db_path(&self) -> &Path {
131        &self.db_path
132    }
133}
134
135#[cfg(any(feature = "native", feature = "db"))]
136static SHARED_POOLS: OnceLock<dashmap::DashMap<String, Arc<DbPillarPool>>> = OnceLock::new();
137
138#[cfg(any(feature = "native", feature = "db"))]
139static SHARED_CONNECTIONS: OnceLock<dashmap::DashMap<String, Arc<Mutex<Connection>>>> = OnceLock::new();
140
141#[cfg(any(feature = "native", feature = "db"))]
142pub fn get_pillar_pool(pillar: &str, clm_root: &str) -> Result<Arc<DbPillarPool>> {
143    let normalized = get_pillar_filename(pillar);
144    let db_path = get_db_path(&normalized, clm_root);
145    let key = format!("{}:{}", normalized, db_path.to_string_lossy());
146    let map = SHARED_POOLS.get_or_init(dashmap::DashMap::new);
147    if let Some(entry) = map.get(&key) {
148        return Ok(entry.clone());
149    }
150    let pool = Arc::new(DbPillarPool::new(&normalized, clm_root)?);
151    map.insert(key, pool.clone());
152    Ok(pool)
153}
154
155#[cfg(any(feature = "native", feature = "db"))]
156pub fn get_writer_connection(pillar: &str, clm_root: &str) -> Result<Arc<Mutex<Connection>>> {
157    let pool = get_pillar_pool(pillar, clm_root)?;
158    Ok(pool.writer())
159}
160
161#[cfg(any(feature = "native", feature = "db"))]
162pub fn get_reader_connection(pillar: &str, clm_root: &str) -> Result<r2d2::PooledConnection<SqliteConnectionManager>> {
163    let pool = get_pillar_pool(pillar, clm_root)?;
164    pool.reader()
165}
166
167#[cfg(any(feature = "native", feature = "db"))]
168pub fn get_shared_connection(pillar: &str, clm_root: &str) -> std::result::Result<Arc<Mutex<Connection>>, String> {
169    get_writer_connection(pillar, clm_root).map_err(|e| e.to_string())
170}
171
172#[cfg(any(feature = "native", feature = "db"))]
173pub fn clear_shared_connections() {
174    if let Some(map) = SHARED_CONNECTIONS.get() {
175        map.clear();
176    }
177    if let Some(pools) = SHARED_POOLS.get() {
178        pools.clear();
179    }
180}
181
182pub fn set_sandbox_depth(depth: usize) {
183    TEST_SANDBOX_DEPTH.with(|d| d.set(depth));
184}
185
186pub fn thread_local_depth_get() -> usize {
187    TEST_SANDBOX_DEPTH.with(|d| d.get())
188}
189
190pub fn thread_local_depth_increment() -> usize {
191    TEST_SANDBOX_DEPTH.with(|d| {
192        let c = d.get() + 1;
193        d.set(c);
194        c
195    })
196}
197
198pub fn thread_local_depth_decrement() {
199    TEST_SANDBOX_DEPTH.with(|d| {
200        let c = d.get();
201        if c > 0 {
202            d.set(c - 1);
203        }
204    })
205}
206
207pub struct DatabaseSandboxGuard {
208    prev_active: bool,
209    prev_db_id: Option<String>,
210}
211
212impl DatabaseSandboxGuard {
213    pub fn activate(db_id: &str) -> Self {
214        let prev_active = TEST_SANDBOX_ACTIVE.with(|active| active.get());
215        let prev_db_id = TEST_SANDBOX_DB_ID.with(|id| id.borrow().clone());
216        
217        TEST_SANDBOX_ACTIVE.with(|active| active.set(true));
218        TEST_SANDBOX_DB_ID.with(|id| {
219            *id.borrow_mut() = Some(db_id.to_string());
220        });
221        
222        DatabaseSandboxGuard {
223            prev_active,
224            prev_db_id,
225        }
226    }
227}
228
229impl Drop for DatabaseSandboxGuard {
230    fn drop(&mut self) {
231        TEST_SANDBOX_ACTIVE.with(|active| active.set(self.prev_active));
232        TEST_SANDBOX_DB_ID.with(|id| {
233            *id.borrow_mut() = self.prev_db_id.clone();
234        });
235        clear_in_memory_connections();
236    }
237}
238
239
240pub fn get_db_path(pillar: &str, clm_root: &str) -> PathBuf {
241    if env::var("CLM_MEM_TRIDB").unwrap_or_default() == "1" {
242        return PathBuf::from(format!("file:memdb_{}?mode=memory&cache=shared", get_pillar_filename(pillar)));
243    }
244    let is_sandbox = TEST_SANDBOX_ACTIVE.with(|active| active.get());
245    if is_sandbox {
246        let db_id = TEST_SANDBOX_DB_ID.with(|id| {
247            id.borrow().clone().unwrap_or_else(|| {
248                format!("{:?}", std::thread::current().id())
249            })
250        });
251        let depth = TEST_SANDBOX_DEPTH.with(|d| d.get());
252        let depth_suffix = if depth > 0 { format!("_depth_{}", depth) } else { "".to_string() };
253        PathBuf::from(format!("file:memdb_{}_{}{}?mode=memory&cache=shared", get_pillar_filename(pillar), db_id, depth_suffix))
254    } else {
255        let zero_fs = crate::utils::is_zero_fs();
256        if zero_fs {
257            let snap_dir = env::var("CLM_MEMBROKER_SNAP_DIR")
258                .unwrap_or_else(|_| "/tmp".to_string());
259            Path::new(&snap_dir).join(format!("{}.db", get_pillar_filename(pillar)))
260        } else {
261            let fname = get_pillar_filename(pillar);
262            let db_dir = Path::new(clm_root).join("db");
263            let target_path = db_dir.join(format!("{}.db", fname));
264            // Backward-compatibility: if mcard_state requested but content_memory.db exists, fall back
265            if fname == "mcard_state" && !target_path.exists() && db_dir.join("content_memory.db").exists() {
266                db_dir.join("content_memory.db")
267            } else if fname == "sovereign_identity" && !target_path.exists() && db_dir.join("agent_identities.db").exists() {
268                db_dir.join("agent_identities.db")
269            } else {
270                target_path
271            }
272        }
273    }
274}
275
276fn get_pillar_filename(pillar: &str) -> String {
277    match pillar {
278        "mcard_state" | "mcard_state.db" => "mcard_state".to_string(),
279        "sovereign_identity" | "sovereign_identity.db" => "sovereign_identity".to_string(),
280        "agent_identity" | "agent_identities" => "agent_identities".to_string(),
281        "content_memory" | "content" | "memory" => "content_memory".to_string(),
282        "execution_log" | "execution" | "log" => "execution_log".to_string(),
283        "evolution" | "evol" => {
284            let now = chrono::Local::now();
285            let iso_week = chrono::Datelike::iso_week(&now);
286            format!("pkc_evol_{:04}_w{:02}", iso_week.year(), iso_week.week())
287        }
288        _ => "content_memory".to_string(),
289    }
290}
291
292/// Check if the membroker backend is active (CLM_DB_BACKEND=membroker and CLM_ZERO_FS=true).
293pub fn is_membroker_backend() -> bool {
294    env::var("CLM_DB_BACKEND").unwrap_or_default() == "membroker"
295        && crate::utils::is_zero_fs()
296}
297
298/// Send a SQL statement to the Python membroker via TCP (framed CLMQ protocol).
299/// Returns the response body on success.
300pub fn membroker_send(pillar: &str, sql: &str) -> Result<String> {
301    let port: u16 = env::var("CLM_MEMBROKER_PORT")
302        .unwrap_or_else(|_| "43740".to_string())
303        .parse()
304        .unwrap_or(43740);
305
306    let addr = format!("127.0.0.1:{}", port);
307    let timeout = Duration::from_secs(5);
308    let socket_addr: std::net::SocketAddr = addr
309        .parse()
310        .map_err(|e| ClmError::Evaluation(format!("Invalid membroker address: {}", e)))?;
311
312    let mut stream = TcpStream::connect_timeout(&socket_addr, timeout)
313        .map_err(|e| ClmError::Evaluation(format!("membroker connect failed: {}", e)))?;
314    stream.set_read_timeout(Some(timeout))?;
315    stream.set_write_timeout(Some(timeout))?;
316
317    let body = sql.as_bytes();
318    let header = format!("CLMQ\t{}\t{}\t\n", pillar, body.len());
319    stream.write_all(header.as_bytes())?;
320    stream.write_all(body)?;
321    stream.shutdown(Shutdown::Write)?;
322
323    let mut response = Vec::new();
324    stream.read_to_end(&mut response)?;
325    let resp_str = String::from_utf8_lossy(&response);
326
327    if resp_str.starts_with("CLMR\tOK\t") {
328        if let Some(pos) = resp_str.find('\n') {
329            Ok(resp_str[pos + 1..].to_string())
330        } else {
331            Ok(String::new())
332        }
333    } else {
334        Err(ClmError::Evaluation(format!("membroker error: {}", resp_str)))
335    }
336}
337
338/// Sync a single INSERT/REPLACE statement to the membroker if the backend is active.
339/// Silently skips if membroker is not available or not in use.
340pub fn membroker_sync(pillar: &str, sql: &str) {
341    if !is_membroker_backend() {
342        return;
343    }
344    let _ = membroker_send(pillar, sql);
345}
346
347pub fn get_connection(pillar: &str, clm_root: &str) -> Result<Connection> {
348    let db_path = get_db_path(pillar, clm_root);
349    // Ensure parent dir exists (only if not in-memory URI starting with file:)
350    let db_path_str = db_path.to_string_lossy();
351    if !db_path_str.starts_with("file:") {
352        if let Some(parent) = db_path.parent() {
353            let _ = fs::create_dir_all(parent);
354        }
355    } else {
356        // Shared memory database. Cache connection in thread-local to ensure persistence
357        let path_str = db_path_str.to_string();
358        let already_registered = IN_MEMORY_CONNS.with(|conns| conns.borrow().contains_key(&path_str));
359        if !already_registered {
360            if let Ok(keep_alive_conn) = Connection::open_with_flags(
361                &db_path,
362                rusqlite::OpenFlags::SQLITE_OPEN_READ_WRITE
363                    | rusqlite::OpenFlags::SQLITE_OPEN_CREATE
364                    | rusqlite::OpenFlags::SQLITE_OPEN_URI,
365            ) {
366                let _ = keep_alive_conn.busy_timeout(std::time::Duration::from_millis(10000));
367                let _ = keep_alive_conn.pragma_update(None, "journal_mode", "WAL");
368                let _ = keep_alive_conn.pragma_update(None, "synchronous", "NORMAL");
369
370                let table_exists: bool = keep_alive_conn
371                    .query_row(
372                        "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='card';",
373                        [],
374                        |row| row.get(0),
375                    )
376                    .unwrap_or(0) > 0;
377                if !table_exists {
378                    let project_path = Path::new(clm_root);
379                    let mut schema_file = project_path.join("types").join("schemas").join("mcard_schema.sql");
380                    if !schema_file.exists() {
381                        schema_file = project_path.join("schema").join("mcard_schema.sql");
382                    }
383                    if !schema_file.exists() {
384                        schema_file = project_path.join("..").join("schema").join("mcard_schema.sql");
385                    }
386                    if schema_file.exists() {
387                        if let Ok(schema_sql) = fs::read_to_string(&schema_file) {
388                            let _ = keep_alive_conn.execute_batch(&schema_sql);
389                        }
390                    }
391                    let _ = keep_alive_conn.execute("ALTER TABLE card ADD COLUMN g_time TEXT;", []);
392                }
393                IN_MEMORY_CONNS.with(|conns| {
394                    conns.borrow_mut().insert(path_str, keep_alive_conn);
395                });
396            }
397        }
398    }
399    let conn = Connection::open_with_flags(
400        &db_path,
401        rusqlite::OpenFlags::SQLITE_OPEN_READ_WRITE
402            | rusqlite::OpenFlags::SQLITE_OPEN_CREATE
403            | rusqlite::OpenFlags::SQLITE_OPEN_URI,
404    )?;
405    // Enable busy timeout to avoid lock contention
406    conn.busy_timeout(std::time::Duration::from_millis(10000))?;
407
408    // Enable WAL mode and normal synchronous mode persistently on the connection
409    let _ = conn.pragma_update(None, "journal_mode", "WAL");
410    let _ = conn.pragma_update(None, "synchronous", "NORMAL");
411
412    // Check if the card table exists, if not, auto-initialize from schema
413    let table_exists: bool = conn
414        .query_row(
415            "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='card';",
416            [],
417            |row| row.get(0),
418        )
419        .unwrap_or(0) > 0;
420    if !table_exists {
421        let _ = conn.execute_batch(CANONICAL_SCHEMA);
422    }
423
424    let _ = conn.execute("ALTER TABLE card ADD COLUMN g_time TEXT;", []);
425    Ok(conn)
426}
427
428pub fn init_tridb(clm_root: &str) -> Result<()> {
429    let project_path = Path::new(clm_root);
430    let mut schema_file = project_path.join("types").join("schemas").join("mcard_schema.sql");
431    if !schema_file.exists() {
432        schema_file = project_path.join("..").join("types").join("schemas").join("mcard_schema.sql");
433    }
434
435    let schema_sql = if schema_file.exists() {
436        fs::read_to_string(&schema_file).unwrap_or_else(|_| CANONICAL_SCHEMA.to_string())
437    } else {
438        CANONICAL_SCHEMA.to_string()
439    };
440
441    let mut version = "unknown".to_string();
442    for line in schema_sql.lines() {
443        if line.contains("Version:") {
444            if let Some(pos) = line.find("Version:") {
445                let suffix = &line[pos + 8..].trim();
446                let parts: Vec<&str> = suffix.split_whitespace().collect();
447                if !parts.is_empty() {
448                    version = parts[0].trim_matches(|c: char| !c.is_numeric() && c != '.').to_string();
449                }
450            }
451            break;
452        }
453    }
454
455    eprintln!("[INIT_TRIDB] Canonical Schema: {}", schema_file.display());
456    eprintln!("[INIT_TRIDB] Schema version:   {}", version);
457    eprintln!();
458
459    let pillars = vec!["agent_identities", "content_memory", "execution_log"];
460
461    for pillar in pillars {
462        let conn = get_connection(pillar, clm_root)
463            .map_err(|e| ClmError::Evaluation(format!("Failed to open DB for {}: {}", pillar, e)))?;
464
465        // Enable WAL mode
466        let _ = conn.pragma_update(None, "journal_mode", "WAL");
467
468        // Split schema into separate statements (SQLite executescript equivalent)
469        // Since sqlite3 handles multiple statements, we can run them in a transaction
470        conn.execute_batch(&schema_sql)
471            .map_err(|e| ClmError::Evaluation(format!("Failed to apply schema for {}: {}", pillar, e)))?;
472
473        // Validate tables exist
474        let mut stmt = conn
475            .prepare("SELECT name FROM sqlite_master WHERE type='table';")?;
476        let table_rows = stmt
477            .query_map([], |row| row.get::<_, String>(0))?;
478
479        let mut has_card = false;
480        let mut has_registry = false;
481        let mut has_history = false;
482
483        for name_res in table_rows {
484            if let Ok(name) = name_res {
485                match name.as_str() {
486                    "card" => has_card = true,
487                    "handle_registry" => has_registry = true,
488                    "handle_history" => has_history = true,
489                    _ => {}
490                }
491            }
492        }
493
494        if !has_card || !has_registry || !has_history {
495            return Err(ClmError::Evaluation(format!("DB {} is missing required tables", pillar)));
496        }
497        
498        // Optional: Schema registration check for self-evolution
499        let schema_handle = format!("db:schema:{}:{}", pillar, version);
500        let schema_hash = crate::fs_utils::compute_sha256_hex(schema_sql.as_bytes());
501        let _ = handle_register_batch(pillar, clm_root, &schema_handle, &crate::hash::prefixed(&schema_hash), &schema_sql);
502    }
503
504    // Load the type lattice eagerly so type validation is always available.
505    // This mirrors the Python TypeRegistry which loads on construction.
506    // The lattice is stored in a global OnceLock for zero-cost repeated access.
507    let mut lattice = crate::type_lattice::TypeLattice::new();
508    lattice.load_registry(clm_root);
509    eprintln!("[INIT_TRIDB] Type lattice loaded: {} types registered", lattice.registry.len());
510
511    Ok(())
512}
513
514pub fn sql_query_parameterized(
515    pillar: &str,
516    clm_root: &str,
517    query: &str,
518    params_vec: Vec<String>,
519    as_json: bool,
520) -> Result<String> {
521    let conn = get_connection(pillar, clm_root)?;
522    
523    // Ensure table structure is present (fallback just in case)
524    let _ = conn.execute("SELECT 1 FROM card LIMIT 1;", []);
525    
526    let query_trimmed = query.trim();
527    let is_batch = query_trimmed.contains("BEGIN;") 
528        || query_trimmed.contains("begin;")
529        || query_trimmed.contains("COMMIT;")
530        || query_trimmed.contains("commit;")
531        || (query_trimmed.contains(';') && !query_trimmed.ends_with(';'))
532        || query_trimmed.matches(';').count() > 1;
533
534    if is_batch {
535        conn.execute_batch(query)?;
536        if as_json {
537            return Ok("[]".to_string());
538        } else {
539            return Ok(String::new());
540        }
541    }
542
543    let mut stmt = conn.prepare(query)?;
544    let col_count = stmt.column_count();
545    let col_names: Vec<String> = stmt
546        .column_names()
547        .into_iter()
548        .map(|s| s.to_string())
549        .collect();
550
551    // Convert Vec<String> to rusqlite params
552    let params_ref: Vec<&dyn rusqlite::ToSql> = params_vec
553        .iter()
554        .map(|s| s as &dyn rusqlite::ToSql)
555        .collect();
556
557    let mut rows = stmt.query(params_ref.as_slice())?;
558    
559    if as_json {
560        let mut results = Vec::new();
561        while let Some(row) = rows.next()? {
562            let mut row_map = serde_json::Map::new();
563            for i in 0..col_count {
564                let name = &col_names[i];
565                let value_ref = row.get_ref(i)?;
566                let json_val = match value_ref {
567                    rusqlite::types::ValueRef::Null => serde_json::Value::Null,
568                    rusqlite::types::ValueRef::Integer(n) => serde_json::Value::Number(n.into()),
569                    rusqlite::types::ValueRef::Real(f) => {
570                        if let Some(num) = serde_json::Number::from_f64(f) {
571                            serde_json::Value::Number(num)
572                        } else {
573                            serde_json::Value::Null
574                        }
575                    }
576                    rusqlite::types::ValueRef::Text(t) => {
577                        let text = String::from_utf8_lossy(t).into_owned();
578                        serde_json::Value::String(text)
579                    }
580                    rusqlite::types::ValueRef::Blob(b) => {
581                        let text = String::from_utf8_lossy(b).into_owned();
582                        serde_json::Value::String(text)
583                    }
584                };
585                row_map.insert(name.clone(), json_val);
586            }
587            results.push(serde_json::Value::Object(row_map));
588        }
589        Ok(serde_json::Value::Array(results).to_string())
590    } else {
591        let mut results = Vec::new();
592        while let Some(row) = rows.next()? {
593            let mut row_parts = Vec::new();
594            for i in 0..col_count {
595                let val_ref = row.get_ref(i)?;
596                let s = match val_ref {
597                    rusqlite::types::ValueRef::Null => "".to_string(),
598                    rusqlite::types::ValueRef::Integer(n) => n.to_string(),
599                    rusqlite::types::ValueRef::Real(f) => f.to_string(),
600                    rusqlite::types::ValueRef::Text(t) => String::from_utf8_lossy(t).into_owned(),
601                    rusqlite::types::ValueRef::Blob(b) => String::from_utf8_lossy(b).into_owned(),
602                };
603                row_parts.push(s);
604            }
605            results.push(row_parts.join("|"));
606        }
607        Ok(results.join("\n"))
608    }
609}
610
611/// Pre-compiled statement query for card content by hash.
612pub fn get_card_cached(conn: &Connection, hash: &str) -> Result<Option<String>> {
613    let mut stmt = conn.prepare_cached("SELECT content FROM card WHERE hash = ?1")?;
614    let mut rows = stmt.query(params![hash])?;
615    if let Some(row) = rows.next()? {
616        let val: rusqlite::types::Value = row.get(0)?;
617        let content = match val {
618            rusqlite::types::Value::Text(s) => s,
619            rusqlite::types::Value::Blob(b) => {
620                String::from_utf8(b).unwrap_or_else(|e| String::from_utf8_lossy(e.as_bytes()).into_owned())
621            }
622            _ => "".to_string(),
623        };
624        Ok(Some(content))
625    } else {
626        Ok(None)
627    }
628}
629
630/// Pre-compiled statement check if a card exists by hash.
631pub fn card_exists_cached(conn: &Connection, hash: &str) -> Result<bool> {
632    let mut stmt = conn.prepare_cached("SELECT count(*) FROM card WHERE hash = ?1")?;
633    let count: i64 = stmt.query_row(params![hash], |row| row.get(0))?;
634    Ok(count > 0)
635}
636
637/// Pre-compiled statement resolution of handle to current hash.
638pub fn resolve_handle_hash_cached(conn: &Connection, handle: &str) -> Result<Option<String>> {
639    let mut stmt = conn.prepare_cached("SELECT current_hash FROM handle_registry WHERE handle = ?1")?;
640    let res: Option<String> = stmt.query_row(params![handle], |row| row.get(0)).ok();
641    Ok(res)
642}
643
644/// Pre-compiled statement resolution of handle to card content.
645pub fn resolve_handle_content_cached(conn: &Connection, handle: &str) -> Result<Option<String>> {
646    if let Some(hash) = resolve_handle_hash_cached(conn, handle)? {
647        get_card_cached(conn, &hash)
648    } else {
649        Ok(None)
650    }
651}
652
653pub fn handle_register_batch(
654    pillar: &str,
655    clm_root: &str,
656    handle: &str,
657    hash_val: &str,
658    content: &str,
659) -> Result<()> {
660    let now = crate::utils::now_iso8601();
661
662    #[cfg(any(feature = "native", feature = "db"))]
663    let created_at_opt = {
664        if let Ok(writer_arc) = get_writer_connection(pillar, clm_root) {
665            let mut conn = writer_arc
666                .lock()
667                .map_err(|e| ClmError::storage(StorageErrorCode::TransactionFailed, e.to_string()))?;
668            let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
669            let mut created_at = now.clone();
670            {
671                let mut stmt_card = tx.prepare_cached(
672                    "INSERT OR IGNORE INTO card (hash, content, g_time) VALUES (?1, ?2, ?3)",
673                )?;
674                stmt_card.execute(params![hash_val, content, now])?;
675
676                let mut stmt_hist = tx.prepare_cached(
677                    "INSERT INTO handle_history (handle, previous_hash, changed_at) \
678                     SELECT hr.handle, hr.current_hash, ?1 FROM handle_registry hr \
679                     JOIN card c ON c.hash = hr.current_hash \
680                     WHERE hr.handle = ?2 AND hr.current_hash != ?3",
681                )?;
682                stmt_hist.execute(params![now, handle, hash_val])?;
683
684                let existing_created_at: Option<String> = {
685                    let mut stmt_chk = tx.prepare_cached(
686                        "SELECT created_at FROM handle_registry WHERE handle = ?1",
687                    )?;
688                    stmt_chk.query_row(params![handle], |row| row.get(0)).ok()
689                };
690                if let Some(ca) = existing_created_at {
691                    created_at = ca;
692                }
693
694                let mut stmt_reg = tx.prepare_cached(
695                    "INSERT OR REPLACE INTO handle_registry (handle, current_hash, created_at, updated_at) \
696                     VALUES (?1, ?2, ?3, ?4)",
697                )?;
698                stmt_reg.execute(params![handle, hash_val, created_at, now])?;
699            }
700            tx.commit()?;
701            Some(created_at)
702        } else {
703            None
704        }
705    };
706
707    #[cfg(not(any(feature = "native", feature = "db")))]
708    let created_at_opt: Option<String> = None;
709
710    let created_at = if let Some(ca) = created_at_opt {
711        ca
712    } else {
713        let mut conn = get_connection(pillar, clm_root)?;
714        let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
715        let mut created_at = now.clone();
716        {
717            let mut stmt_card = tx.prepare_cached(
718                "INSERT OR IGNORE INTO card (hash, content, g_time) VALUES (?1, ?2, ?3)",
719            )?;
720            stmt_card.execute(params![hash_val, content, now])?;
721
722            let mut stmt_hist = tx.prepare_cached(
723                "INSERT INTO handle_history (handle, previous_hash, changed_at) \
724                 SELECT hr.handle, hr.current_hash, ?1 FROM handle_registry hr \
725                 JOIN card c ON c.hash = hr.current_hash \
726                 WHERE hr.handle = ?2 AND hr.current_hash != ?3",
727            )?;
728            stmt_hist.execute(params![now, handle, hash_val])?;
729
730            let existing_created_at: Option<String> = {
731                let mut stmt_chk = tx.prepare_cached(
732                    "SELECT created_at FROM handle_registry WHERE handle = ?1",
733                )?;
734                stmt_chk.query_row(params![handle], |row| row.get(0)).ok()
735            };
736            if let Some(ca) = existing_created_at {
737                created_at = ca;
738            }
739
740            let mut stmt_reg = tx.prepare_cached(
741                "INSERT OR REPLACE INTO handle_registry (handle, current_hash, created_at, updated_at) \
742                 VALUES (?1, ?2, ?3, ?4)",
743            )?;
744            stmt_reg.execute(params![handle, hash_val, created_at, now])?;
745        }
746        tx.commit()?;
747        created_at
748    };
749
750    // Sync to membroker if active
751    if is_membroker_backend() {
752        let esc_hash = hash_val.replace('\'', "''");
753        let esc_content = content.replace('\'', "''");
754        let esc_handle = handle.replace('\'', "''");
755        let esc_now = now.replace('\'', "''");
756        let esc_created = created_at.replace('\'', "''");
757        membroker_sync(pillar, &format!(
758            "INSERT OR IGNORE INTO card (hash, content, g_time) VALUES ('{}', '{}', '{}')",
759            esc_hash, esc_content, esc_now
760        ));
761        membroker_sync(pillar, &format!(
762            "INSERT OR REPLACE INTO handle_registry (handle, current_hash, created_at, updated_at) VALUES ('{}', '{}', '{}', '{}')",
763            esc_handle, esc_hash, esc_created, esc_now
764        ));
765    }
766
767    Ok(())
768}
769
770pub fn handle_delete_batch(pillar: &str, clm_root: &str, handle: &str) -> Result<()> {
771    let now = crate::utils::now_iso8601();
772
773    #[cfg(any(feature = "native", feature = "db"))]
774    {
775        if let Ok(writer_arc) = get_writer_connection(pillar, clm_root) {
776            let mut conn = writer_arc
777                .lock()
778                .map_err(|e| ClmError::storage(StorageErrorCode::TransactionFailed, e.to_string()))?;
779            let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
780            {
781                let mut stmt_hist = tx.prepare_cached(
782                    "INSERT INTO handle_history (handle, previous_hash, changed_at) \
783                     SELECT hr.handle, hr.current_hash, ?1 FROM handle_registry hr \
784                     JOIN card c ON c.hash = hr.current_hash \
785                     WHERE hr.handle = ?2",
786                )?;
787                stmt_hist.execute(params![now, handle])?;
788
789                let mut stmt_del_hist = tx.prepare_cached("DELETE FROM handle_history WHERE handle = ?1")?;
790                stmt_del_hist.execute(params![handle])?;
791
792                let mut stmt_del = tx.prepare_cached("DELETE FROM handle_registry WHERE handle = ?1")?;
793                stmt_del.execute(params![handle])?;
794            }
795            tx.commit()?;
796            return Ok(());
797        }
798    }
799
800    let mut conn = get_connection(pillar, clm_root)?;
801    let tx = conn.transaction_with_behavior(rusqlite::TransactionBehavior::Immediate)?;
802    {
803        let mut stmt_hist = tx.prepare_cached(
804            "INSERT INTO handle_history (handle, previous_hash, changed_at) \
805             SELECT hr.handle, hr.current_hash, ?1 FROM handle_registry hr \
806             JOIN card c ON c.hash = hr.current_hash \
807             WHERE hr.handle = ?2",
808        )?;
809        stmt_hist.execute(params![now, handle])?;
810
811        let mut stmt_del_hist = tx.prepare_cached("DELETE FROM handle_history WHERE handle = ?1")?;
812        stmt_del_hist.execute(params![handle])?;
813
814        let mut stmt_del = tx.prepare_cached("DELETE FROM handle_registry WHERE handle = ?1")?;
815        stmt_del.execute(params![handle])?;
816    }
817    tx.commit()?;
818    Ok(())
819}
820
821pub fn resolve_handle_hash(pillar: &str, clm_root: &str, handle: &str) -> Result<Option<String>> {
822    #[cfg(any(feature = "native", feature = "db"))]
823    {
824        if let Ok(reader) = get_reader_connection(pillar, clm_root) {
825            return resolve_handle_hash_cached(&reader, handle);
826        }
827    }
828    let conn = get_connection(pillar, clm_root)?;
829    resolve_handle_hash_cached(&conn, handle)
830}
831
832pub fn resolve_handle_content(
833    pillar: &str,
834    clm_root: &str,
835    handle: &str,
836) -> Result<Option<String>> {
837    #[cfg(any(feature = "native", feature = "db"))]
838    {
839        if let Ok(reader) = get_reader_connection(pillar, clm_root) {
840            return resolve_handle_content_cached(&reader, handle);
841        }
842    }
843    let conn = get_connection(pillar, clm_root)?;
844    resolve_handle_content_cached(&conn, handle)
845}
846
847pub fn card_exists(pillar: &str, clm_root: &str, hash: &str) -> Result<bool> {
848    #[cfg(any(feature = "native", feature = "db"))]
849    {
850        if let Ok(reader) = get_reader_connection(pillar, clm_root) {
851            return card_exists_cached(&reader, hash);
852        }
853    }
854    let conn = get_connection(pillar, clm_root)?;
855    card_exists_cached(&conn, hash)
856}
857
858pub fn setter_card_batch(
859    pillar: &str,
860    clm_root: &str,
861    hash: &str,
862    content: &str,
863    g_time: &str,
864) -> Result<()> {
865    #[cfg(any(feature = "native", feature = "db"))]
866    {
867        if let Ok(writer_arc) = get_writer_connection(pillar, clm_root) {
868            let conn = writer_arc
869                .lock()
870                .map_err(|e| ClmError::storage(StorageErrorCode::TransactionFailed, e.to_string()))?;
871            let mut stmt = conn.prepare_cached(
872                "INSERT OR IGNORE INTO card (hash, content, g_time) VALUES (?1, ?2, ?3)",
873            )?;
874            stmt.execute(params![hash, content, g_time])?;
875            return Ok(());
876        }
877    }
878    let conn = get_connection(pillar, clm_root)?;
879    let mut stmt = conn.prepare_cached(
880        "INSERT OR IGNORE INTO card (hash, content, g_time) VALUES (?1, ?2, ?3)",
881    )?;
882    stmt.execute(params![hash, content, g_time])?;
883    Ok(())
884}
885
886pub fn emit_exec_mcard(
887    operation: &str,
888    content_hash: &str,
889    extra_yaml: &str,
890    a_hash: &str,
891    c_hash: &str,
892    clm_root: &str,
893) -> Result<String> {
894    let is_sandbox = TEST_SANDBOX_ACTIVE.with(|active| active.get());
895    let zero_fs = is_sandbox || env::var("CLM_ZERO_FS").unwrap_or_default() == "true";
896    let data_root = Path::new(clm_root).join("data");
897    let mcard_dir = if zero_fs {
898        let tmp_dir = env::var("TMPDIR").unwrap_or_else(|_| "/tmp".to_string());
899        Path::new(&tmp_dir).join("data").join("execution_logs")
900    } else {
901        data_root.join("execution_logs")
902    };
903
904    if !zero_fs {
905        let _ = fs::create_dir_all(&mcard_dir);
906    }
907
908    let now = chrono::Local::now();
909    let g_time = now.format("%Y-%m-%dT%H:%M:%S%z").to_string();
910    let unix_ts = now.timestamp();
911
912    let short_hash = if content_hash.len() >= 8 {
913        &content_hash[0..8]
914    } else {
915        content_hash
916    };
917    let mcard_file = mcard_dir.join(format!("exec_{}_{}.yaml", unix_ts, short_hash));
918
919    // Construct MCard content YAML
920    let mut mcard_content = format!(
921        "mcard:\n  g_time: \"{}\"\n  content_hash: \"{}\"\n  operation: \"{}\"\n  agent_did: \"{}\"\n",
922        g_time, content_hash, operation, env::var("ACTIVE_AGENT_DID").unwrap_or_default()
923    );
924
925    if !extra_yaml.is_empty() {
926        for line in extra_yaml.lines() {
927            mcard_content.push_str(&format!("  {}\n", line));
928        }
929    }
930
931    if !a_hash.is_empty() || !c_hash.is_empty() {
932        mcard_content.push_str("  source:\n");
933        if !a_hash.is_empty() {
934            mcard_content.push_str(&format!("    a_hash: \"{}\"\n", a_hash));
935        }
936        if !c_hash.is_empty() {
937            mcard_content.push_str(&format!("    c_hash: \"{}\"\n", c_hash));
938        }
939        mcard_content.push_str(&format!("    operation: \"{}\"\n", operation));
940        
941        if let Ok(runtime_facts) = env::var("CLM_RUNTIME_FACTS") {
942            if !runtime_facts.is_empty() {
943                mcard_content.push_str("    runtime_facts:\n");
944                for line in runtime_facts.lines() {
945                    mcard_content.push_str(&format!("      {}\n", line));
946                }
947            }
948        }
949    } else {
950        if let Ok(runtime_facts) = env::var("CLM_RUNTIME_FACTS") {
951            if !runtime_facts.is_empty() {
952                mcard_content.push_str("  runtime_facts:\n");
953                for line in runtime_facts.lines() {
954                    mcard_content.push_str(&format!("    {}\n", line));
955                }
956            }
957        }
958    }
959
960    if !zero_fs {
961        fs::write(&mcard_file, &mcard_content)?;
962    }
963
964    // Write to execution_log.db using writer connection or fallback
965    #[cfg(any(feature = "native", feature = "db"))]
966    let write_done = {
967        if let Ok(writer_arc) = get_writer_connection("execution_log", clm_root) {
968            if let Ok(conn) = writer_arc.lock() {
969                let mut stmt = conn.prepare_cached(
970                    "INSERT OR REPLACE INTO card (hash, content, g_time) VALUES (?1, ?2, ?3)",
971                )?;
972                stmt.execute(params![content_hash, &mcard_content, &g_time])?;
973                true
974            } else {
975                false
976            }
977        } else {
978            false
979        }
980    };
981    #[cfg(not(any(feature = "native", feature = "db")))]
982    let write_done = false;
983
984    if !write_done {
985        let conn = get_connection("execution_log", clm_root)?;
986        let mut stmt = conn.prepare_cached(
987            "INSERT OR REPLACE INTO card (hash, content, g_time) VALUES (?1, ?2, ?3)",
988        )?;
989        stmt.execute(params![content_hash, &mcard_content, &g_time])?;
990    }
991
992    // Sync execution MCard to membroker (only active in Zero-FS mode)
993    let esc_mcard = mcard_content.replace('\'', "''");
994    let esc_gtime = g_time.replace('\'', "''");
995    membroker_sync("execution_log", &format!(
996        "INSERT OR REPLACE INTO card (hash, content, g_time) VALUES ('{}', '{}', '{}')",
997        content_hash, esc_mcard, esc_gtime
998    ));
999
1000    Ok(mcard_file.to_string_lossy().to_string())
1001}
1002
1003pub fn get_card_content(pillar: &str, clm_root: &str, hash: &str) -> Result<Option<String>> {
1004    #[cfg(any(feature = "native", feature = "db"))]
1005    {
1006        if let Ok(reader) = get_reader_connection(pillar, clm_root) {
1007            return get_card_cached(&reader, hash);
1008        }
1009    }
1010    let conn = get_connection(pillar, clm_root)?;
1011    get_card_cached(&conn, hash)
1012}
1013
1014pub fn backup_db(pillar: &str, clm_root: &str) -> Result<Connection> {
1015    let src = get_connection(pillar, clm_root)?;
1016    let mut dst = Connection::open_in_memory()?;
1017    {
1018        let backup = rusqlite::backup::Backup::new(&src, &mut dst)?;
1019        backup.run_to_completion(10_000, std::time::Duration::ZERO, None)?;
1020    }
1021    Ok(dst)
1022}
1023
1024pub fn restore_db(pillar: &str, clm_root: &str, backup_conn: &Connection) -> Result<()> {
1025    let mut dst = get_connection(pillar, clm_root)?;
1026    {
1027        let backup = rusqlite::backup::Backup::new(backup_conn, &mut dst)?;
1028        backup.run_to_completion(10_000, std::time::Duration::ZERO, None)?;
1029    }
1030    Ok(())
1031}
1032
1033/// Offloads synchronous blocking database operations from Tokio worker threads
1034/// to Tokio's dedicated thread pool, protecting the Satori event loop from starvation.
1035#[cfg(any(feature = "native", all(feature = "db", feature = "async")))]
1036pub async fn query_db_async<F, R>(f: F) -> Result<R>
1037where
1038    F: FnOnce() -> Result<R> + Send + 'static,
1039    R: Send + 'static,
1040{
1041    tokio::task::spawn_blocking(f)
1042        .await
1043        .map_err(|join_err| ClmError::Storage(crate::error::StorageErrorCode::QueryFailed, format!("Tokio worker join error: {}", join_err)))?
1044}
1045
1046#[cfg(test)]
1047mod tests {
1048    use super::*;
1049
1050    #[test]
1051    fn test_cached_statement_queries() {
1052        let mut conn = Connection::open_in_memory().unwrap();
1053        migrations::apply_migrations(&mut conn, "content_memory").unwrap();
1054
1055        // Insert card via cached statement
1056        let mut stmt = conn
1057            .prepare_cached("INSERT OR IGNORE INTO card (hash, content, g_time) VALUES (?1, ?2, ?3)")
1058            .unwrap();
1059        stmt.execute(rusqlite::params!["test_hash_1", "test_content_1", 1000])
1060            .unwrap();
1061
1062        // Retrieve card via helper function using prepared cache
1063        let content = get_card_cached(&conn, "test_hash_1").unwrap();
1064        assert_eq!(content, Some("test_content_1".to_string()));
1065
1066        let exists = card_exists_cached(&conn, "test_hash_1").unwrap();
1067        assert!(exists);
1068
1069        let not_exists = card_exists_cached(&conn, "nonexistent").unwrap();
1070        assert!(!not_exists);
1071
1072        // Insert handle registry
1073        let now = crate::utils::now_iso8601();
1074        let mut stmt_h = conn
1075            .prepare_cached("INSERT OR IGNORE INTO handle_registry (handle, current_hash, created_at, updated_at) VALUES (?1, ?2, ?3, ?4)")
1076            .unwrap();
1077        stmt_h.execute(params!["test_handle_1", "test_hash_1", &now, &now]).unwrap();
1078
1079        let resolved_hash = resolve_handle_hash_cached(&conn, "test_handle_1").unwrap();
1080        assert_eq!(resolved_hash, Some("test_hash_1".to_string()));
1081
1082        let resolved_content = resolve_handle_content_cached(&conn, "test_handle_1").unwrap();
1083        assert_eq!(resolved_content, Some("test_content_1".to_string()));
1084    }
1085
1086    #[cfg(any(feature = "native", feature = "db"))]
1087    #[test]
1088    fn test_concurrent_readers_in_pool() {
1089        let temp_dir = tempfile::tempdir().unwrap();
1090        let clm_root = temp_dir.path().to_str().unwrap();
1091        let pool = DbPillarPool::new("content_memory", clm_root).unwrap();
1092
1093        // Pre-populate with 50 cards using the writer
1094        {
1095            let writer_arc = pool.writer();
1096            let mut conn = writer_arc.lock().unwrap();
1097            let tx = conn.transaction().unwrap();
1098            for i in 0..50 {
1099                let hash = format!("hash_{:04}", i);
1100                let content = format!("content_{:04}", i);
1101                let mut stmt = tx
1102                    .prepare_cached("INSERT INTO card (hash, content, g_time) VALUES (?1, ?2, ?3)")
1103                    .unwrap();
1104                stmt.execute(rusqlite::params![hash, content, i as i64])
1105                    .unwrap();
1106            }
1107            tx.commit().unwrap();
1108        }
1109
1110        // Spawn 16 concurrent reader threads querying simultaneously
1111        let mut handles = Vec::new();
1112        for thread_idx in 0..16 {
1113            let pool_clone = pool.clone();
1114            let h = std::thread::spawn(move || {
1115                let reader = pool_clone.reader().expect("Acquiring reader connection should succeed");
1116                for j in 0..50 {
1117                    let card_idx = (thread_idx * 7 + j) % 50;
1118                    let target_hash = format!("hash_{:04}", card_idx);
1119                    let expected_content = format!("content_{:04}", card_idx);
1120
1121                    let content = get_card_cached(&reader, &target_hash)
1122                        .expect("Read query should not fail");
1123                    assert_eq!(content, Some(expected_content));
1124                }
1125            });
1126            handles.push(h);
1127        }
1128
1129        for h in handles {
1130            h.join().expect("Reader thread should complete without panicking");
1131        }
1132    }
1133
1134    #[tokio::test]
1135    async fn test_query_db_async_isolation() {
1136        let temp_dir = tempfile::tempdir().unwrap();
1137        let clm_root = temp_dir.path().to_str().unwrap();
1138        let pool = DbPillarPool::new("content_memory", clm_root).unwrap();
1139
1140        // Write via writer connection
1141        let pool_writer = pool.clone();
1142        query_db_async(move || {
1143            let writer_arc = pool_writer.writer();
1144            let mut conn = writer_arc.lock().unwrap();
1145            let tx = conn.transaction()?;
1146            {
1147                let mut stmt = tx.prepare_cached("INSERT OR REPLACE INTO card (hash, content, g_time) VALUES (?1, ?2, ?3)")?;
1148                stmt.execute(rusqlite::params!["async_hash", "async_content", 12345])?;
1149            }
1150            tx.commit()?;
1151            Ok(())
1152        })
1153        .await
1154        .unwrap();
1155
1156        // Read concurrently via query_db_async
1157        let pool_reader = pool.clone();
1158        let res = query_db_async(move || {
1159            let reader = pool_reader.reader()?;
1160            let content = get_card_cached(&reader, "async_hash")?;
1161            Ok(content)
1162        })
1163        .await
1164        .unwrap();
1165
1166        assert_eq!(res, Some("async_content".to_string()));
1167    }
1168}
1169
1170
1171