Skip to main content

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