1use 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
41pub 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#[repr(C)]
60struct CsvTab {
61 base: ffi::sqlite3_vtab,
63 filename: String,
65 has_headers: bool,
66 delimiter: u8,
67 quote: u8,
68 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 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 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#[repr(C)]
261struct CsvTabCursor<'vtab> {
262 base: ffi::sqlite3_vtab_cursor,
264 reader: csv::Reader<File>,
266 row_number: usize,
268 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 fn vtab(&self) -> &CsvTab {
288 unsafe { &*(self.base.pVtab as *const CsvTab) }
289 }
290}
291
292unsafe impl VTabCursor for CsvTabCursor<'_> {
293 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 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}