Skip to main content

rusqlite/vtab/
csvtab.rs

1//! CSV Virtual Table.
2//!
3//! Port of [csv](https://sqlite.org/src/file/ext/misc/csv.c) C
4//! extension: `https://www.sqlite.org/csv.html`
5//!
6//! # Example
7//!
8//! ```rust,no_run
9//! # use rusqlite::{Connection, Result};
10//! fn example() -> Result<()> {
11//!     // Note: This should be done once (usually when opening the DB).
12//!     let db = Connection::open_in_memory()?;
13//!     rusqlite::vtab::csvtab::load_module(&db)?;
14//!     // Assume my_csv.csv
15//!     let schema = "
16//!         CREATE VIRTUAL TABLE my_csv_data
17//!         USING csv(filename = 'my_csv.csv')
18//!     ";
19//!     db.execute_batch(schema)?;
20//!     // Now the `my_csv_data` (virtual) table can be queried as normal...
21//!     Ok(())
22//! }
23//! ```
24use std::borrow::Cow;
25use std::ffi::{CStr, CString, c_int};
26use std::fs::File;
27use std::marker::PhantomData;
28use std::path::Path;
29use std::str;
30
31use crate::ffi;
32use crate::types::Null;
33use crate::vtab::{
34    Context, CreateVTab, Filters, IndexInfo, Module, VTab, VTabConfig, VTabConnection, VTabCursor,
35    VTabKind, escape_double_quote, parse_boolean,
36};
37use crate::{Connection, Error, Result};
38
39const MODULE_NAME: &CStr = c"csv";
40
41/// Register the "csv" module.
42/// ```sql
43/// CREATE VIRTUAL TABLE vtab USING csv(
44///   filename=FILENAME -- Name of file containing CSV content
45///   [, schema=SCHEMA] -- Alternative CSV schema. 'CREATE TABLE x(col1 TEXT NOT NULL, col2 INT, ...);'
46///   [, header=YES|NO] -- First row of CSV defines the names of columns if "yes". Default "no".
47///   [, columns=N] -- Assume the CSV file contains N columns.
48///   [, delimiter=C] -- CSV delimiter. Default ','.
49///   [, quote=C] -- CSV quote. Default '"'. 0 means no quote.
50/// );
51/// ```
52pub fn load_module(conn: &Connection) -> Result<()> {
53    const MODULE: Module<CsvTab> = Module::read_only_module();
54    let aux: Option<()> = None;
55    conn.create_module(MODULE_NAME, &MODULE, aux)
56}
57
58/// An instance of the CSV virtual table
59#[repr(C)]
60struct CsvTab {
61    /// Base class. Must be first
62    base: ffi::sqlite3_vtab,
63    /// Name of the CSV file
64    filename: String,
65    has_headers: bool,
66    delimiter: u8,
67    quote: u8,
68    /// Offset to start of data
69    offset_first_row: csv::Position,
70}
71
72impl CsvTab {
73    fn reader(&self) -> Result<csv::Reader<File>, csv::Error> {
74        csv::ReaderBuilder::new()
75            .has_headers(self.has_headers)
76            .delimiter(self.delimiter)
77            .quote(self.quote)
78            .from_path(&self.filename)
79    }
80
81    fn parse_byte(arg: &str) -> Option<u8> {
82        if arg.len() == 1 {
83            arg.bytes().next()
84        } else {
85            None
86        }
87    }
88}
89
90unsafe impl<'vtab> VTab<'vtab> for CsvTab {
91    type Aux = ();
92    type Cursor = CsvTabCursor<'vtab>;
93
94    fn connect(
95        db: &mut VTabConnection,
96        aux: Option<&()>,
97        module_name: &[u8],
98        _database_name: &[u8],
99        _table_name: &[u8],
100        args: &[&[u8]],
101    ) -> Result<(Cow<'static, CStr>, Self)> {
102        debug_assert_eq!(aux, None);
103        debug_assert_eq!(module_name, MODULE_NAME.to_bytes());
104        if args.is_empty() {
105            return Err(Error::ModuleError("no CSV file specified".to_owned()));
106        }
107
108        let mut vtab = Self {
109            base: ffi::sqlite3_vtab::default(),
110            filename: String::new(),
111            has_headers: false,
112            delimiter: b',',
113            quote: b'"',
114            offset_first_row: csv::Position::new(),
115        };
116        let mut schema = None;
117        let mut n_col = None;
118
119        for c_slice in args {
120            let (param, value) = super::parameter(c_slice)?;
121            let value = value.as_ref();
122            match param {
123                "filename" => {
124                    if !Path::new(value).exists() {
125                        return Err(Error::ModuleError(format!("file '{value}' does not exist")));
126                    }
127                    value.clone_into(&mut vtab.filename);
128                }
129                "schema" => {
130                    schema = Some(CString::new(value)?);
131                }
132                "columns" => {
133                    if let Ok(n) = value.parse::<u16>() {
134                        if n_col.is_some() {
135                            return Err(Error::ModuleError(
136                                "more than one 'columns' parameter".to_owned(),
137                            ));
138                        } else if n == 0 {
139                            return Err(Error::ModuleError(
140                                "must have at least one column".to_owned(),
141                            ));
142                        }
143                        n_col = Some(n);
144                    } else {
145                        return Err(Error::ModuleError(format!(
146                            "unrecognized argument to 'columns': {value}"
147                        )));
148                    }
149                }
150                "header" => {
151                    if let Some(b) = parse_boolean(value) {
152                        vtab.has_headers = b;
153                    } else {
154                        return Err(Error::ModuleError(format!(
155                            "unrecognized argument to 'header': {value}"
156                        )));
157                    }
158                }
159                "delimiter" => {
160                    if let Some(b) = Self::parse_byte(value) {
161                        vtab.delimiter = b;
162                    } else {
163                        return Err(Error::ModuleError(format!(
164                            "unrecognized argument to 'delimiter': {value}"
165                        )));
166                    }
167                }
168                "quote" => {
169                    if let Some(b) = Self::parse_byte(value) {
170                        if b == b'0' {
171                            vtab.quote = 0;
172                        } else {
173                            vtab.quote = b;
174                        }
175                    } else {
176                        return Err(Error::ModuleError(format!(
177                            "unrecognized argument to 'quote': {value}"
178                        )));
179                    }
180                }
181                _ => {
182                    return Err(Error::ModuleError(format!(
183                        "unrecognized parameter '{param}'"
184                    )));
185                }
186            }
187        }
188
189        if vtab.filename.is_empty() {
190            return Err(Error::ModuleError("no CSV file specified".to_owned()));
191        }
192
193        let mut cols: Vec<String> = Vec::new();
194        if vtab.has_headers || (n_col.is_none() && schema.is_none()) {
195            let mut reader = vtab.reader()?;
196            if vtab.has_headers {
197                {
198                    let headers = reader.headers()?;
199                    // headers ignored if cols is not empty
200                    if n_col.is_none() && schema.is_none() {
201                        cols = headers
202                            .into_iter()
203                            .map(|header| escape_double_quote(header).into_owned())
204                            .collect();
205                    }
206                }
207                vtab.offset_first_row = reader.position().clone();
208            } else {
209                let mut record = csv::ByteRecord::new();
210                if reader.read_byte_record(&mut record)? {
211                    for (i, _) in record.iter().enumerate() {
212                        cols.push(format!("c{i}"));
213                    }
214                }
215            }
216        } else if let Some(n_col) = n_col {
217            for i in 0..n_col {
218                cols.push(format!("c{i}"));
219            }
220        }
221
222        if cols.is_empty() && schema.is_none() {
223            return Err(Error::ModuleError("no column specified".to_owned()));
224        }
225
226        if schema.is_none() {
227            let mut sql = String::from("CREATE TABLE x(");
228            for (i, col) in cols.iter().enumerate() {
229                sql.push('"');
230                sql.push_str(col);
231                sql.push_str("\" TEXT");
232                if i == cols.len() - 1 {
233                    sql.push_str(");");
234                } else {
235                    sql.push_str(", ");
236                }
237            }
238            schema = Some(CString::new(sql)?);
239        }
240        db.config(VTabConfig::DirectOnly)?;
241        Ok((Cow::Owned(schema.unwrap()), vtab))
242    }
243
244    // Only a forward full table scan is supported.
245    fn best_index(&self, info: &mut IndexInfo) -> Result<bool> {
246        info.set_estimated_cost(1_000_000.);
247        Ok(true)
248    }
249
250    fn open(&mut self) -> Result<CsvTabCursor<'_>> {
251        Ok(CsvTabCursor::new(self.reader()?))
252    }
253}
254
255impl CreateVTab<'_> for CsvTab {
256    const KIND: VTabKind = VTabKind::Default;
257}
258
259/// A cursor for the CSV virtual table
260#[repr(C)]
261struct CsvTabCursor<'vtab> {
262    /// Base class. Must be first
263    base: ffi::sqlite3_vtab_cursor,
264    /// The CSV reader object
265    reader: csv::Reader<File>,
266    /// Current cursor position used as rowid
267    row_number: usize,
268    /// Values of the current row
269    cols: csv::StringRecord,
270    eof: bool,
271    phantom: PhantomData<&'vtab CsvTab>,
272}
273
274impl CsvTabCursor<'_> {
275    fn new<'vtab>(reader: csv::Reader<File>) -> CsvTabCursor<'vtab> {
276        CsvTabCursor {
277            base: ffi::sqlite3_vtab_cursor::default(),
278            reader,
279            row_number: 0,
280            cols: csv::StringRecord::new(),
281            eof: false,
282            phantom: PhantomData,
283        }
284    }
285
286    /// Accessor to the associated virtual table.
287    fn vtab(&self) -> &CsvTab {
288        unsafe { &*(self.base.pVtab as *const CsvTab) }
289    }
290}
291
292unsafe impl VTabCursor for CsvTabCursor<'_> {
293    // Only a full table scan is supported.  So `filter` simply rewinds to
294    // the beginning.
295    fn filter(
296        &mut self,
297        _idx_num: c_int,
298        _idx_str: Option<&str>,
299        _args: &Filters<'_>,
300    ) -> Result<()> {
301        {
302            let offset_first_row = self.vtab().offset_first_row.clone();
303            self.reader.seek(offset_first_row)?;
304        }
305        self.row_number = 0;
306        self.next()
307    }
308
309    fn next(&mut self) -> Result<()> {
310        {
311            self.eof = self.reader.is_done();
312            if self.eof {
313                return Ok(());
314            }
315
316            self.eof = !self.reader.read_record(&mut self.cols)?;
317        }
318
319        self.row_number += 1;
320        Ok(())
321    }
322
323    fn eof(&self) -> bool {
324        self.eof
325    }
326
327    fn column(&self, ctx: &mut Context, col: c_int) -> Result<()> {
328        if col < 0 || col as usize >= self.cols.len() {
329            return Err(Error::ModuleError(format!(
330                "column index out of bounds: {col}"
331            )));
332        }
333        if self.cols.is_empty() {
334            return ctx.set_result(Null);
335        }
336        // TODO Affinity
337        ctx.set_result(&self.cols[col as usize])
338    }
339
340    fn rowid(&self) -> Result<i64> {
341        Ok(self.row_number as i64)
342    }
343}
344
345impl From<csv::Error> for Error {
346    #[cold]
347    fn from(err: csv::Error) -> Self {
348        Self::ModuleError(err.to_string())
349    }
350}
351
352#[cfg(all(test, not(miri)))]
353mod test {
354    #[cfg(all(target_family = "wasm", target_os = "unknown"))]
355    use wasm_bindgen_test::wasm_bindgen_test as test;
356
357    use crate::vtab::csvtab;
358    use crate::{Connection, Result};
359    use fallible_iterator::FallibleIterator as _;
360
361    #[cfg_attr(
362        all(target_family = "wasm", target_os = "unknown"),
363        ignore = "no filesystem on this platform"
364    )]
365    #[test]
366    fn test_csv_module() -> Result<()> {
367        let db = Connection::open_in_memory()?;
368        csvtab::load_module(&db)?;
369        db.execute_batch(
370            "CREATE VIRTUAL TABLE vtab USING csv(filename = 'test.csv', header = yes)",
371        )?;
372
373        {
374            let mut s = db.prepare("SELECT rowid, * FROM vtab")?;
375            {
376                let headers = s.column_names().collect::<Vec<_>>();
377                assert_eq!(vec!["rowid", "colA", "colB", "colC"], headers);
378            }
379
380            let ids: Result<Vec<i32>> = s.query([])?.map(|row| row.get::<_, i32>(0)).collect();
381            let sum = ids?.iter().sum::<i32>();
382            assert_eq!(sum, 15);
383        }
384        db.execute_batch("DROP TABLE vtab")
385    }
386
387    #[cfg_attr(
388        all(target_family = "wasm", target_os = "unknown"),
389        ignore = "no filesystem on this platform"
390    )]
391    #[test]
392    fn test_csv_cursor() -> Result<()> {
393        let db = Connection::open_in_memory()?;
394        csvtab::load_module(&db)?;
395        db.execute_batch("CREATE VIRTUAL TABLE vtab USING csv(filename='test.csv', header=yes)")?;
396
397        {
398            let mut s = db.prepare(
399                "SELECT v1.rowid, v1.* FROM vtab v1 NATURAL JOIN vtab v2 WHERE \
400                     v1.rowid < v2.rowid",
401            )?;
402
403            let mut rows = s.query([])?;
404            let row = rows.next()?.unwrap();
405            assert_eq!(row.get_unwrap::<_, i32>(0), 2);
406        }
407        db.execute_batch("DROP TABLE vtab")
408    }
409}