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