Skip to main content

clm_rust_core/
db_connection.rs

1#![allow(unused_imports)]
2use super::*;
3
4pub fn get_connection(pillar: &str, clm_root: &str) -> Result<Connection> {
5    let db_path = get_db_path(pillar, clm_root);
6    // Ensure parent dir exists (only if not in-memory URI starting with file:)
7    let db_path_str = db_path.to_string_lossy();
8    if !db_path_str.starts_with("file:") {
9        if let Some(parent) = db_path.parent() {
10            let _ = fs::create_dir_all(parent);
11        }
12    } else {
13        // Shared memory database. Cache connection in thread-local to ensure persistence
14        let path_str = db_path_str.to_string();
15        let already_registered = IN_MEMORY_CONNS.with(|conns| conns.borrow().contains_key(&path_str));
16        if !already_registered
17            && let Ok(keep_alive_conn) = Connection::open_with_flags(
18                &db_path,
19                rusqlite::OpenFlags::SQLITE_OPEN_READ_WRITE
20                    | rusqlite::OpenFlags::SQLITE_OPEN_CREATE
21                    | rusqlite::OpenFlags::SQLITE_OPEN_URI,
22            ) {
23                let _ = keep_alive_conn.busy_timeout(std::time::Duration::from_millis(10000));
24                let _ = keep_alive_conn.pragma_update(None, "journal_mode", "WAL");
25                let _ = keep_alive_conn.pragma_update(None, "synchronous", "NORMAL");
26
27                let table_exists: bool = keep_alive_conn
28                    .query_row(
29                        "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='card';",
30                        [],
31                        |row| row.get(0),
32                    )
33                    .unwrap_or(0) > 0;
34                if !table_exists {
35                    let project_path = Path::new(clm_root);
36                    let mut schema_file = project_path.join("types").join("schemas").join("mcard_schema.sql");
37                    if !schema_file.exists() {
38                        schema_file = project_path.join("schema").join("mcard_schema.sql");
39                    }
40                    if !schema_file.exists() {
41                        schema_file = project_path.join("..").join("schema").join("mcard_schema.sql");
42                    }
43                    if schema_file.exists()
44                        && let Ok(schema_sql) = fs::read_to_string(&schema_file) {
45                            let sql_to_use = if schema_sql.contains("CREATE TABLE") {
46                                &schema_sql
47                            } else {
48                                CANONICAL_SCHEMA
49                            };
50                            let _ = keep_alive_conn.execute_batch(sql_to_use);
51                        }
52                    let _ = keep_alive_conn.execute("ALTER TABLE card ADD COLUMN g_time TEXT;", []);
53                }
54                IN_MEMORY_CONNS.with(|conns| {
55                    conns.borrow_mut().insert(path_str, keep_alive_conn);
56                });
57            }
58    }
59
60    #[cfg(unix)]
61    {
62        use std::os::unix::fs::PermissionsExt;
63        if let Ok(meta) = fs::metadata(&db_path) {
64            let current_mode = meta.permissions().mode();
65            if current_mode & 0o200 == 0 {
66                let mut perms = meta.permissions();
67                perms.set_mode(current_mode | 0o664);
68                let _ = fs::set_permissions(&db_path, perms);
69            }
70        }
71        for ext in &["-shm", "-wal"] {
72            let aux_path = format!("{}{}", db_path.display(), ext);
73            if let Ok(meta) = fs::metadata(&aux_path) {
74                let current_mode = meta.permissions().mode();
75                if current_mode & 0o200 == 0 {
76                    let mut perms = meta.permissions();
77                    perms.set_mode(current_mode | 0o664);
78                    let _ = fs::set_permissions(&aux_path, perms);
79                }
80            }
81        }
82    }
83
84    let conn = Connection::open_with_flags(
85        &db_path,
86        rusqlite::OpenFlags::SQLITE_OPEN_READ_WRITE
87            | rusqlite::OpenFlags::SQLITE_OPEN_CREATE
88            | rusqlite::OpenFlags::SQLITE_OPEN_URI,
89    )?;
90    // Enable busy timeout to avoid lock contention
91    conn.busy_timeout(std::time::Duration::from_millis(10000))?;
92
93    // Enable WAL mode and normal synchronous mode persistently on the connection
94    let _ = conn.pragma_update(None, "journal_mode", "WAL");
95    let _ = conn.pragma_update(None, "synchronous", "NORMAL");
96
97    // Check if the card table exists, if not, auto-initialize from schema
98    let table_exists: bool = conn
99        .query_row(
100            "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='card';",
101            [],
102            |row| row.get(0),
103        )
104        .unwrap_or(0) > 0;
105    if !table_exists {
106        let _ = conn.execute_batch(CANONICAL_SCHEMA);
107    }
108
109    let _ = conn.execute("ALTER TABLE card ADD COLUMN g_time TEXT;", []);
110    Ok(conn)
111}
112
113pub fn init_tridb(clm_root: &str) -> Result<()> {
114    let project_path = Path::new(clm_root);
115    let mut schema_file = project_path.join("types").join("schemas").join("mcard_schema.sql");
116    if !schema_file.exists() {
117        schema_file = project_path.join("..").join("types").join("schemas").join("mcard_schema.sql");
118    }
119
120    let schema_sql = if schema_file.exists() {
121        let content = fs::read_to_string(&schema_file).unwrap_or_else(|_| CANONICAL_SCHEMA.to_string());
122        if content.contains("CREATE TABLE") {
123            content
124        } else {
125            CANONICAL_SCHEMA.to_string()
126        }
127    } else {
128        CANONICAL_SCHEMA.to_string()
129    };
130
131    let mut version = "unknown".to_string();
132    for line in schema_sql.lines() {
133        if line.contains("Version:") {
134            if let Some(pos) = line.find("Version:") {
135                let suffix = &line[pos + 8..].trim();
136                let parts: Vec<&str> = suffix.split_whitespace().collect();
137                if !parts.is_empty() {
138                    version = parts[0].trim_matches(|c: char| !c.is_numeric() && c != '.').to_string();
139                }
140            }
141            break;
142        }
143    }
144
145    eprintln!("[INIT_TRIDB] Canonical Schema: {}", schema_file.display());
146    eprintln!("[INIT_TRIDB] Schema version:   {}", version);
147    eprintln!();
148
149    let pillars = vec!["agent_identities", "content_memory", "execution_log"];
150
151    for pillar in pillars {
152        let conn = get_connection(pillar, clm_root)
153            .map_err(|e| ClmError::Evaluation(format!("Failed to open DB for {}: {}", pillar, e)))?;
154
155        // Enable WAL mode
156        let _ = conn.pragma_update(None, "journal_mode", "WAL");
157
158        // Split schema into separate statements (SQLite executescript equivalent)
159        // Since sqlite3 handles multiple statements, we can run them in a transaction
160        conn.execute_batch(&schema_sql)
161            .map_err(|e| ClmError::Evaluation(format!("Failed to apply schema for {}: {}", pillar, e)))?;
162
163        // Validate tables exist
164        let mut stmt = conn
165            .prepare("SELECT name FROM sqlite_master WHERE type='table';")?;
166        let table_rows = stmt
167            .query_map([], |row| row.get::<_, String>(0))?;
168
169        let mut has_card = false;
170        let mut has_registry = false;
171        let mut has_history = false;
172
173        for name in table_rows.flatten() {
174            match name.as_str() {
175                "card" => has_card = true,
176                "handle_registry" => has_registry = true,
177                "handle_history" => has_history = true,
178                _ => {}
179            }
180        }
181
182        if !has_card || !has_registry || !has_history {
183            return Err(ClmError::Evaluation(format!("DB {} is missing required tables", pillar)));
184        }
185        
186        // Optional: Schema registration check for self-evolution
187        let schema_handle = format!("db:schema:{}:{}", pillar, version);
188        let schema_hash = crate::fs_utils::compute_sha256_hex(schema_sql.as_bytes());
189        let _ = handle_register_batch(pillar, clm_root, &schema_handle, &crate::hash::prefixed(&schema_hash), &schema_sql);
190    }
191
192    // Load the type lattice eagerly so type validation is always available.
193    // This mirrors the Python TypeRegistry which loads on construction.
194    // The lattice is stored in a global OnceLock for zero-cost repeated access.
195    let mut lattice = crate::type_lattice::TypeLattice::new();
196    lattice.load_registry(clm_root);
197    eprintln!("[INIT_TRIDB] Type lattice loaded: {} types registered", lattice.registry.len());
198
199    Ok(())
200}
201
202pub fn sql_query_parameterized(
203    pillar: &str,
204    clm_root: &str,
205    query: &str,
206    params_vec: Vec<String>,
207    as_json: bool,
208) -> Result<String> {
209    let conn = get_connection(pillar, clm_root)?;
210    
211    // Ensure table structure is present (fallback just in case)
212    let _ = conn.execute("SELECT 1 FROM card LIMIT 1;", []);
213    
214    let query_trimmed = query.trim();
215    let is_batch = query_trimmed.contains("BEGIN;") 
216        || query_trimmed.contains("begin;")
217        || query_trimmed.contains("COMMIT;")
218        || query_trimmed.contains("commit;")
219        || (query_trimmed.contains(';') && !query_trimmed.ends_with(';'))
220        || query_trimmed.matches(';').count() > 1;
221
222    if is_batch {
223        conn.execute_batch(query)?;
224        if as_json {
225            return Ok("[]".to_string());
226        } else {
227            return Ok(String::new());
228        }
229    }
230
231    let mut stmt = conn.prepare(query)?;
232    let col_count = stmt.column_count();
233    let col_names: Vec<String> = stmt
234        .column_names()
235        .into_iter()
236        .map(|s| s.to_string())
237        .collect();
238
239    // Convert Vec<String> to rusqlite params
240    let params_ref: Vec<&dyn rusqlite::ToSql> = params_vec
241        .iter()
242        .map(|s| s as &dyn rusqlite::ToSql)
243        .collect();
244
245    let mut rows = stmt.query(params_ref.as_slice())?;
246    
247    if as_json {
248        let mut results = Vec::new();
249        while let Some(row) = rows.next()? {
250            let mut row_map = serde_json::Map::new();
251            for i in 0..col_count {
252                let name = &col_names[i];
253                let value_ref = row.get_ref(i)?;
254                let json_val = match value_ref {
255                    rusqlite::types::ValueRef::Null => serde_json::Value::Null,
256                    rusqlite::types::ValueRef::Integer(n) => serde_json::Value::Number(n.into()),
257                    rusqlite::types::ValueRef::Real(f) => {
258                        if let Some(num) = serde_json::Number::from_f64(f) {
259                            serde_json::Value::Number(num)
260                        } else {
261                            serde_json::Value::Null
262                        }
263                    }
264                    rusqlite::types::ValueRef::Text(t) => {
265                        let text = String::from_utf8_lossy(t).into_owned();
266                        serde_json::Value::String(text)
267                    }
268                    rusqlite::types::ValueRef::Blob(b) => {
269                        let text = String::from_utf8_lossy(b).into_owned();
270                        serde_json::Value::String(text)
271                    }
272                };
273                row_map.insert(name.clone(), json_val);
274            }
275            results.push(serde_json::Value::Object(row_map));
276        }
277        Ok(serde_json::Value::Array(results).to_string())
278    } else {
279        let mut results = Vec::new();
280        while let Some(row) = rows.next()? {
281            let mut row_parts = Vec::new();
282            for i in 0..col_count {
283                let val_ref = row.get_ref(i)?;
284                let s = match val_ref {
285                    rusqlite::types::ValueRef::Null => "".to_string(),
286                    rusqlite::types::ValueRef::Integer(n) => n.to_string(),
287                    rusqlite::types::ValueRef::Real(f) => f.to_string(),
288                    rusqlite::types::ValueRef::Text(t) => String::from_utf8_lossy(t).into_owned(),
289                    rusqlite::types::ValueRef::Blob(b) => String::from_utf8_lossy(b).into_owned(),
290                };
291                row_parts.push(s);
292            }
293            results.push(row_parts.join("|"));
294        }
295        Ok(results.join("\n"))
296    }
297}
298
299/// Pre-compiled statement query for card content by hash.
300pub fn get_card_cached(conn: &Connection, hash: &str) -> Result<Option<String>> {
301    let mut stmt = conn.prepare_cached("SELECT content FROM card WHERE hash = ?1")?;
302    let mut rows = stmt.query(params![hash])?;
303    if let Some(row) = rows.next()? {
304        let val: rusqlite::types::Value = row.get(0)?;
305        let content = match val {
306            rusqlite::types::Value::Text(s) => s,
307            rusqlite::types::Value::Blob(b) => {
308                String::from_utf8(b).unwrap_or_else(|e| String::from_utf8_lossy(e.as_bytes()).into_owned())
309            }
310            _ => "".to_string(),
311        };
312        Ok(Some(content))
313    } else {
314        Ok(None)
315    }
316}
317
318/// Pre-compiled statement check if a card exists by hash.
319pub fn card_exists_cached(conn: &Connection, hash: &str) -> Result<bool> {
320    let mut stmt = conn.prepare_cached("SELECT count(*) FROM card WHERE hash = ?1")?;
321    let count: i64 = stmt.query_row(params![hash], |row| row.get(0))?;
322    Ok(count > 0)
323}
324
325/// Pre-compiled statement resolution of handle to current hash.
326pub fn resolve_handle_hash_cached(conn: &Connection, handle: &str) -> Result<Option<String>> {
327    let mut stmt = conn.prepare_cached("SELECT current_hash FROM handle_registry WHERE handle = ?1")?;
328    let res: Option<String> = stmt.query_row(params![handle], |row| row.get(0)).ok();
329    Ok(res)
330}
331
332/// Pre-compiled statement resolution of handle to card content.
333pub fn resolve_handle_content_cached(conn: &Connection, handle: &str) -> Result<Option<String>> {
334    if let Some(hash) = resolve_handle_hash_cached(conn, handle)? {
335        get_card_cached(conn, &hash)
336    } else {
337        Ok(None)
338    }
339}
340