csp_format/
database.rs

1//! Access to the SQLite database embedded in .clip files (and .cmc / .sut,
2//! which are plain SQLite files).
3//!
4//! CSP creates tables and columns only when a file first needs them, so
5//! readers look columns up by name and treat missing ones as absent.
6
7use std::collections::HashMap;
8use std::sync::Arc;
9
10use rusqlite::types::Value;
11use rusqlite::{Connection, OpenFlags, Params};
12
13use crate::error::Result;
14
15pub use rusqlite::types::Value as SqlValue;
16
17/// Open a database image as an in-memory connection (changes stay in memory).
18pub fn open_bytes(image: &[u8]) -> Result<Connection> {
19    let mut connection = Connection::open_in_memory_with_flags(OpenFlags::SQLITE_OPEN_READ_WRITE)?;
20    connection.deserialize_read_exact("main", image, image.len(), false)?;
21    Ok(connection)
22}
23
24/// The database image of `connection` (what goes into CHNKSQLi).
25pub fn serialize(connection: &Connection) -> Result<Vec<u8>> {
26    Ok(connection.serialize("main")?.to_vec())
27}
28
29/// One row read with `SELECT *`, columns in table order.
30#[derive(Debug, Clone, Default, PartialEq)]
31pub struct Row {
32    columns: Arc<Vec<String>>,
33    index: Arc<HashMap<String, usize>>,
34    values: Vec<Value>,
35}
36
37impl Row {
38    /// A row with these columns and values (same length and order).
39    pub fn new(columns: Vec<String>, values: Vec<Value>) -> Self {
40        let index = columns.iter().enumerate().map(|(i, name)| (name.clone(), i)).collect();
41        Row { columns: Arc::new(columns), index: Arc::new(index), values }
42    }
43
44    /// Column names, in table order.
45    pub fn columns(&self) -> &[String] {
46        &self.columns
47    }
48
49    /// Values, in column order.
50    pub fn values(&self) -> &[Value] {
51        &self.values
52    }
53
54    /// (column, value) pairs in table order.
55    pub fn iter(&self) -> impl Iterator<Item = (&str, &Value)> {
56        self.columns.iter().map(String::as_str).zip(self.values.iter())
57    }
58
59    /// The value of a column; None when the table has no such column.
60    pub fn get(&self, column: &str) -> Option<&Value> {
61        self.index.get(column).map(|i| &self.values[*i])
62    }
63
64    /// The column exists in this row's table (it may still be NULL).
65    pub fn has(&self, column: &str) -> bool {
66        self.index.contains_key(column)
67    }
68
69    /// Not NULL and present.
70    pub fn is_set(&self, column: &str) -> bool {
71        !matches!(self.get(column), None | Some(Value::Null))
72    }
73
74    /// Integer value; NULL, missing and non-numeric read as 0.
75    pub fn int(&self, column: &str) -> i64 {
76        self.opt_int(column).unwrap_or(0)
77    }
78
79    /// Integer value (reals are truncated); None when NULL, missing or not a number.
80    pub fn opt_int(&self, column: &str) -> Option<i64> {
81        match self.get(column)? {
82            Value::Integer(value) => Some(*value),
83            Value::Real(value) => Some(*value as i64),
84            _ => None,
85        }
86    }
87
88    /// Real value; NULL, missing and non-numeric read as 0.0.
89    pub fn real(&self, column: &str) -> f64 {
90        self.opt_real(column).unwrap_or(0.0)
91    }
92
93    /// Real value (integers converted); None when NULL, missing or not a number.
94    pub fn opt_real(&self, column: &str) -> Option<f64> {
95        match self.get(column)? {
96            Value::Integer(value) => Some(*value as f64),
97            Value::Real(value) => Some(*value),
98            _ => None,
99        }
100    }
101
102    /// Text value; UTF-8 blobs are decoded too. None when NULL or missing.
103    pub fn text(&self, column: &str) -> Option<String> {
104        match self.get(column)? {
105            Value::Text(value) => Some(value.clone()),
106            Value::Blob(value) => Some(String::from_utf8_lossy(value).into_owned()),
107            _ => None,
108        }
109    }
110
111    /// Blob value (text as its UTF-8 bytes); None when NULL or missing.
112    pub fn blob(&self, column: &str) -> Option<&[u8]> {
113        match self.get(column)? {
114            Value::Blob(value) => Some(value),
115            Value::Text(value) => Some(value.as_bytes()),
116            _ => None,
117        }
118    }
119
120    /// A value that is not NULL, 0 or empty.
121    pub fn truthy(&self, column: &str) -> bool {
122        match self.get(column) {
123            Some(Value::Integer(value)) => *value != 0,
124            Some(Value::Real(value)) => *value != 0.0,
125            Some(Value::Text(value)) => !value.is_empty(),
126            Some(Value::Blob(value)) => !value.is_empty(),
127            _ => false,
128        }
129    }
130
131    /// Replace a value (the column must exist).
132    pub fn set(&mut self, column: &str, value: Value) -> bool {
133        match self.index.get(column) {
134            Some(i) => {
135                self.values[*i] = value;
136                true
137            }
138            None => false,
139        }
140    }
141}
142
143/// Run a query and return its rows with column names.
144pub fn select(connection: &Connection, sql: &str, params: impl Params) -> Result<Vec<Row>> {
145    let mut statement = connection.prepare(sql)?;
146    let columns: Vec<String> = statement.column_names().into_iter().map(str::to_owned).collect();
147    let index: Arc<HashMap<String, usize>> =
148        Arc::new(columns.iter().enumerate().map(|(i, name)| (name.clone(), i)).collect());
149    let columns = Arc::new(columns);
150    let count = columns.len();
151    let rows = statement.query_map(params, |row| {
152        let mut values = Vec::with_capacity(count);
153        for i in 0..count {
154            values.push(row.get::<_, Value>(i)?);
155        }
156        Ok(values)
157    })?;
158    let mut result = Vec::new();
159    for values in rows {
160        result.push(Row { columns: columns.clone(), index: index.clone(), values: values? });
161    }
162    Ok(result)
163}
164
165/// Every row of a table (empty when the table does not exist).
166pub fn select_all(connection: &Connection, table: &str) -> Result<Vec<Row>> {
167    if !table_exists(connection, table)? {
168        return Ok(Vec::new());
169    }
170    select(connection, &format!("SELECT * FROM {} ORDER BY rowid", quote(table)), [])
171}
172
173/// Whether the database has a table with this name.
174pub fn table_exists(connection: &Connection, table: &str) -> Result<bool> {
175    let count: i64 = connection.query_row(
176        "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = ?1",
177        [table],
178        |row| row.get(0),
179    )?;
180    Ok(count > 0)
181}
182
183/// Names of all tables, in creation order.
184pub fn tables(connection: &Connection) -> Result<Vec<String>> {
185    let mut statement = connection.prepare("SELECT name FROM sqlite_master WHERE type = 'table' ORDER BY rowid")?;
186    let names = statement.query_map([], |row| row.get::<_, String>(0))?;
187    Ok(names.collect::<std::result::Result<_, _>>()?)
188}
189
190/// Column (name, declared type) pairs of a table, in order.
191pub fn columns(connection: &Connection, table: &str) -> Result<Vec<(String, String)>> {
192    let mut statement = connection.prepare(&format!("PRAGMA table_info({})", quote(table)))?;
193    let names = statement.query_map([], |row| Ok((row.get::<_, String>(1)?, row.get::<_, String>(2)?)))?;
194    Ok(names.collect::<std::result::Result<_, _>>()?)
195}
196
197/// Whether `table` has a column named `column`.
198pub fn has_column(connection: &Connection, table: &str, column: &str) -> Result<bool> {
199    Ok(columns(connection, table)?.iter().any(|(name, _)| name == column))
200}
201
202/// Same as [`table_exists`].
203pub fn has_table(connection: &Connection, table: &str) -> Result<bool> {
204    let count: i64 = connection.query_row(
205        "SELECT COUNT(*) FROM sqlite_master WHERE type = 'table' AND name = ?1",
206        [table],
207        |row| row.get(0),
208    )?;
209    Ok(count > 0)
210}
211
212/// Quote an identifier for SQL.
213pub fn quote(identifier: &str) -> String {
214    format!("\"{}\"", identifier.replace('"', "\"\""))
215}
216
217/// Read an id column that CSP stores either as TEXT or as BLOB.
218pub fn value_as_text(value: &Value) -> Option<String> {
219    match value {
220        Value::Text(text) => Some(text.clone()),
221        Value::Blob(blob) => Some(String::from_utf8_lossy(blob).into_owned()),
222        _ => None,
223    }
224}
225
226#[cfg(test)]
227mod tests {
228    use super::*;
229
230    #[test]
231    fn rows_look_up_columns_by_name() {
232        let connection = Connection::open_in_memory().unwrap();
233        connection
234            .execute_batch(
235                "CREATE TABLE T(_PW_ID INTEGER PRIMARY KEY AUTOINCREMENT, A INTEGER, B REAL, C TEXT, D BLOB);
236                 INSERT INTO T(A, B, C, D) VALUES (5, 1.5, 'x', x'6869');",
237            )
238            .unwrap();
239        let image = serialize(&connection).unwrap();
240        let reopened = open_bytes(&image).unwrap();
241        let rows = select_all(&reopened, "T").unwrap();
242        assert_eq!(rows.len(), 1);
243        let row = &rows[0];
244        assert_eq!(row.int("A"), 5);
245        assert_eq!(row.real("B"), 1.5);
246        assert_eq!(row.text("C").as_deref(), Some("x"));
247        assert_eq!(row.text("D").as_deref(), Some("hi"));
248        assert!(!row.has("Missing"));
249        assert_eq!(row.int("Missing"), 0);
250        assert!(select_all(&reopened, "Nope").unwrap().is_empty());
251        assert_eq!(columns(&reopened, "T").unwrap()[1], ("A".to_string(), "INTEGER".to_string()));
252    }
253}
csp_format/database.rs

csp_format/
database.rs
