1mod clipboard;
2mod engine;
3mod input;
4mod output;
5mod summary;
6mod types;
7mod value;
8mod writers;
9
10pub use engine::{
11 run_query, run_query_with_params, run_query_with_params_multi,
12 run_query_with_params_multi_inputs, run_query_with_params_multi_inputs_and_options,
13 run_query_with_params_multi_inputs_and_options_and_normalization,
14};
15pub use input::{SheetData, load_input};
16pub use output::{
17 render_csv, render_html, render_json, render_jsonl, render_markdown, render_text, render_xml,
18 write_csv, write_jsonl, write_text,
19};
20pub use summary::load_table_summaries;
21pub use types::{
22 ColumnInfo, ColumnStats, ExtractionOptions, HeaderCase, InferredType,
23 InputNormalizationOptions, JsonMode, QueryParam, QueryResult, QueryValue, TableSummary,
24 TypeInferenceOptions, WorkbookInput, XmlMode,
25};
26pub use writers::{write_feather, write_parquet, write_xlsx};
27
28#[cfg(test)]
29use anyhow::Result;
30#[cfg(test)]
31use rust_xlsxwriter::Workbook;
32#[cfg(test)]
33use std::path::Path;
34#[cfg(test)]
35mod tests {
36 use std::{
37 fs,
38 path::PathBuf,
39 time::{SystemTime, UNIX_EPOCH},
40 };
41
42 use super::clipboard;
43 use super::*;
44
45 fn temp_path(name: &str) -> PathBuf {
46 let unique = SystemTime::now()
47 .duration_since(UNIX_EPOCH)
48 .expect("time should move forward")
49 .as_nanos();
50 std::env::temp_dir().join(format!("query-forge-{name}-{unique}.xlsx"))
51 }
52
53 fn temp_xml_path(name: &str) -> PathBuf {
54 let unique = SystemTime::now()
55 .duration_since(UNIX_EPOCH)
56 .expect("time should move forward")
57 .as_nanos();
58 std::env::temp_dir().join(format!("query-forge-{name}-{unique}.xml"))
59 }
60
61 fn temp_csv_path(name: &str) -> PathBuf {
62 let unique = SystemTime::now()
63 .duration_since(UNIX_EPOCH)
64 .expect("time should move forward")
65 .as_nanos();
66 std::env::temp_dir().join(format!("query-forge-{name}-{unique}.csv"))
67 }
68
69 fn temp_jsonl_path(name: &str) -> PathBuf {
70 let unique = SystemTime::now()
71 .duration_since(UNIX_EPOCH)
72 .expect("time should move forward")
73 .as_nanos();
74 std::env::temp_dir().join(format!("query-forge-{name}-{unique}.jsonl"))
75 }
76
77 fn temp_json_path(name: &str) -> PathBuf {
78 let unique = SystemTime::now()
79 .duration_since(UNIX_EPOCH)
80 .expect("time should move forward")
81 .as_nanos();
82 std::env::temp_dir().join(format!("query-forge-{name}-{unique}.json"))
83 }
84
85 fn temp_markdown_path(name: &str) -> PathBuf {
86 let unique = SystemTime::now()
87 .duration_since(UNIX_EPOCH)
88 .expect("time should move forward")
89 .as_nanos();
90 std::env::temp_dir().join(format!("query-forge-{name}-{unique}.md"))
91 }
92
93 fn temp_parquet_path(name: &str) -> PathBuf {
94 let unique = SystemTime::now()
95 .duration_since(UNIX_EPOCH)
96 .expect("time should move forward")
97 .as_nanos();
98 std::env::temp_dir().join(format!("query-forge-{name}-{unique}.parquet"))
99 }
100
101 fn temp_feather_path(name: &str) -> PathBuf {
102 let unique = SystemTime::now()
103 .duration_since(UNIX_EPOCH)
104 .expect("time should move forward")
105 .as_nanos();
106 std::env::temp_dir().join(format!("query-forge-{name}-{unique}.feather"))
107 }
108
109 fn write_test_workbook(path: &Path) -> Result<()> {
110 let mut workbook = Workbook::new();
111 let worksheet = workbook.add_worksheet();
112
113 worksheet.write_string(0, 0, "name")?;
114 worksheet.write_string(0, 1, "price")?;
115 worksheet.write_string(0, 2, "active")?;
116 worksheet.write_string(1, 0, "Keyboard")?;
117 worksheet.write_number(1, 1, 12.5)?;
118 worksheet.write_boolean(1, 2, true)?;
119 worksheet.write_string(2, 0, "Cable")?;
120 worksheet.write_number(2, 1, 5.0)?;
121 worksheet.write_boolean(2, 2, false)?;
122
123 workbook.save(path)?;
124 Ok(())
125 }
126
127 fn write_test_workbook_on_sheet(path: &Path, sheet_name: &str) -> Result<()> {
128 let mut workbook = Workbook::new();
129 let worksheet = workbook.add_worksheet();
130 worksheet.set_name(sheet_name)?;
131
132 worksheet.write_string(0, 0, "name")?;
133 worksheet.write_string(0, 1, "price")?;
134 worksheet.write_string(1, 0, "Keyboard")?;
135 worksheet.write_number(1, 1, 12.5)?;
136
137 workbook.save(path)?;
138 Ok(())
139 }
140
141 fn write_test_xml(path: &Path) -> Result<()> {
142 fs::write(
143 path,
144 r#"<?xml version="1.0" encoding="UTF-8"?>
145<data>
146 <row>
147 <name>Keyboard</name>
148 <price>12.5</price>
149 <active>true</active>
150 </row>
151 <row>
152 <name>Cable</name>
153 <price>5</price>
154 <active>false</active>
155 </row>
156</data>
157"#,
158 )?;
159
160 Ok(())
161 }
162
163 fn write_test_xml_with_sections(path: &Path) -> Result<()> {
164 fs::write(
165 path,
166 r#"<?xml version="1.0" encoding="UTF-8"?>
167<root>
168 <Inventory>
169 <row>
170 <name>Keyboard</name>
171 <price>12.5</price>
172 </row>
173 </Inventory>
174 <Archive>
175 <row>
176 <name>Legacy Cable</name>
177 <price>3.5</price>
178 </row>
179 </Archive>
180</root>
181"#,
182 )?;
183
184 Ok(())
185 }
186
187 fn write_test_csv(path: &Path) -> Result<()> {
188 fs::write(
189 path,
190 "name,price,active\nKeyboard,12.5,true\nCable,5,false\n",
191 )?;
192
193 Ok(())
194 }
195
196 fn write_test_csv_with_custom_inference_values(path: &Path) -> Result<()> {
197 fs::write(
198 path,
199 "name,amount,flag,date,note\nKeyboard,\"1,5\",YES,31/12/2025,N/A\nCable,\"2,0\",NO,01/01/2026,ok\n",
200 )?;
201
202 Ok(())
203 }
204
205 fn write_test_csv_no_headers(path: &Path) -> Result<()> {
206 fs::write(path, "Keyboard,12.5,true\nCable,5,false\n")?;
207
208 Ok(())
209 }
210
211 fn write_test_csv_for_normalization(path: &Path) -> Result<()> {
212 fs::write(
213 path,
214 "First Name,First-Name, Notes \n Alice ,Alice, hello \n , , \n",
215 )?;
216
217 Ok(())
218 }
219
220 fn write_test_jsonl(path: &Path) -> Result<()> {
221 fs::write(
222 path,
223 "{\"name\":\"Keyboard\",\"price\":12.5,\"active\":true}\n{\"name\":\"Cable\",\"price\":5,\"active\":false}\n",
224 )?;
225
226 Ok(())
227 }
228
229 fn write_test_jsonl_with_sparse_columns(path: &Path) -> Result<()> {
230 fs::write(
231 path,
232 "{\"name\":\"Keyboard\",\"price\":12.5}\n\n{\"name\":\"Cable\",\"active\":false}\n",
233 )?;
234
235 Ok(())
236 }
237
238 fn write_test_json(path: &Path) -> Result<()> {
239 fs::write(
240 path,
241 "[{\"name\":\"Keyboard\",\"price\":12.5,\"active\":true},{\"name\":\"Cable\",\"price\":5,\"active\":false}]",
242 )?;
243
244 Ok(())
245 }
246
247 fn write_test_json_with_sections(path: &Path) -> Result<()> {
248 fs::write(
249 path,
250 "{\"Inventory\":[{\"name\":\"Keyboard\",\"price\":12.5}],\"Archive\":[{\"name\":\"Legacy Cable\",\"price\":3.5}]}",
251 )?;
252
253 Ok(())
254 }
255
256 fn write_test_markdown_with_tables(path: &Path) -> Result<()> {
257 fs::write(
258 path,
259 r#"# Inventory Report
260
261| name | price | active |
262| --- | ---: | :---: |
263| Keyboard | 12.5 | true |
264| Cable | 5 | false |
265
266## Archive
267
268| name | price |
269| --- | --- |
270| Legacy Cable | 3.5 |
271"#,
272 )?;
273
274 Ok(())
275 }
276
277 fn write_test_markdown_with_headers_only(path: &Path) -> Result<()> {
278 fs::write(
279 path,
280 r#"| name | price |
281| --- | --- |
282"#,
283 )?;
284
285 Ok(())
286 }
287
288 #[test]
289 fn applies_input_normalization_options_before_query() -> Result<()> {
290 let csv_path = temp_csv_path("csv-normalization");
291 write_test_csv_for_normalization(&csv_path)?;
292 let options = InputNormalizationOptions {
293 trim: true,
294 skip_empty_rows: true,
295 normalize_headers: true,
296 header_case: Some(HeaderCase::Snake),
297 dedupe_headers: true,
298 };
299
300 let inputs = [WorkbookInput {
301 path: &csv_path,
302 sheet_name: None,
303 table_name: None,
304 explicit_format: None,
305 }];
306 let result = run_query_with_params_multi_inputs_and_options_and_normalization(
307 &inputs,
308 "SELECT first_name, first_name_2, notes FROM table",
309 &[],
310 &TypeInferenceOptions::default(),
311 &options,
312 &ExtractionOptions::default(),
313 true,
314 )?;
315
316 assert_eq!(result.columns, vec!["first_name", "first_name_2", "notes"]);
317 assert_eq!(
318 result.rows,
319 vec![vec![
320 QueryValue::Text("Alice".into()),
321 QueryValue::Text("Alice".into()),
322 QueryValue::Text("hello".into())
323 ]]
324 );
325
326 fs::remove_file(csv_path)?;
327 Ok(())
328 }
329
330 #[test]
331 fn executes_sql_query_against_sheet() -> Result<()> {
332 let workbook_path = temp_path("query");
333 write_test_workbook(&workbook_path)?;
334
335 let result = run_query(
336 &workbook_path,
337 Some("Sheet1"),
338 "SELECT name, price FROM table WHERE price > 10 ORDER BY price DESC",
339 true,
340 )?;
341
342 assert_eq!(result.columns, vec!["name", "price"]);
343 assert_eq!(
344 result.rows,
345 vec![vec![
346 QueryValue::Text("Keyboard".into()),
347 QueryValue::Real(12.5)
348 ]]
349 );
350
351 fs::remove_file(workbook_path)?;
352 Ok(())
353 }
354
355 #[test]
356 fn executes_sql_query_against_sheet1_alias() -> Result<()> {
357 let workbook_path = temp_path("query-sheet1-alias");
358 write_test_workbook(&workbook_path)?;
359
360 let result = run_query(
361 &workbook_path,
362 Some("Sheet1"),
363 "SELECT name, price FROM table1 WHERE price > 10 ORDER BY price DESC",
364 true,
365 )?;
366
367 assert_eq!(result.columns, vec!["name", "price"]);
368 assert_eq!(
369 result.rows,
370 vec![vec![
371 QueryValue::Text("Keyboard".into()),
372 QueryValue::Real(12.5)
373 ]]
374 );
375
376 fs::remove_file(workbook_path)?;
377 Ok(())
378 }
379
380 #[test]
381 fn executes_sql_query_with_named_parameter() -> Result<()> {
382 let workbook_path = temp_path("query-with-param");
383 write_test_workbook(&workbook_path)?;
384
385 let result = run_query_with_params(
386 &workbook_path,
387 Some("Sheet1"),
388 "SELECT name, price FROM table WHERE price > :min_price ORDER BY price DESC",
389 &[QueryParam {
390 name: "min_price".to_owned(),
391 value: QueryValue::Real(10.0),
392 }],
393 true,
394 )?;
395
396 assert_eq!(
397 result,
398 QueryResult {
399 columns: vec!["name".to_owned(), "price".to_owned()],
400 rows: vec![vec![
401 QueryValue::Text("Keyboard".to_owned()),
402 QueryValue::Real(12.5),
403 ]],
404 }
405 );
406
407 fs::remove_file(&workbook_path)?;
408 Ok(())
409 }
410
411 #[test]
412 fn executes_query_against_multiple_workbooks() -> Result<()> {
413 let workbook_path_1 = temp_path("multi-1");
414 let workbook_path_2 = temp_path("multi-2");
415 write_test_workbook(&workbook_path_1)?;
416 write_test_workbook(&workbook_path_2)?;
417
418 let result = run_query_with_params_multi(
419 &[workbook_path_1.as_path(), workbook_path_2.as_path()],
420 Some("Sheet1"),
421 "SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2",
422 &[],
423 true,
424 )?;
425
426 assert_eq!(result.columns, vec!["total_rows"]);
427 assert_eq!(
428 result.rows,
429 vec![vec![QueryValue::Integer(2)], vec![QueryValue::Integer(2)]]
430 );
431
432 fs::remove_file(&workbook_path_1)?;
433 fs::remove_file(&workbook_path_2)?;
434 Ok(())
435 }
436
437 #[test]
438 fn executes_query_against_multiple_workbooks_with_distinct_sheet_names() -> Result<()> {
439 let workbook_path_1 = temp_path("multi-sheet-1");
440 let workbook_path_2 = temp_path("multi-sheet-2");
441 write_test_workbook_on_sheet(&workbook_path_1, "Consuntivo")?;
442 write_test_workbook_on_sheet(&workbook_path_2, "WKL")?;
443
444 let result = run_query_with_params_multi_inputs(
445 &[
446 WorkbookInput {
447 path: workbook_path_1.as_path(),
448 sheet_name: Some("Consuntivo"),
449 table_name: None,
450 explicit_format: None,
451 },
452 WorkbookInput {
453 path: workbook_path_2.as_path(),
454 sheet_name: Some("WKL"),
455 table_name: None,
456 explicit_format: None,
457 },
458 ],
459 "SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2",
460 &[],
461 true,
462 )?;
463
464 assert_eq!(result.columns, vec!["total_rows"]);
465 assert_eq!(
466 result.rows,
467 vec![vec![QueryValue::Integer(1)], vec![QueryValue::Integer(1)]]
468 );
469
470 fs::remove_file(&workbook_path_1)?;
471 fs::remove_file(&workbook_path_2)?;
472 Ok(())
473 }
474
475 #[test]
476 fn executes_query_with_explicit_table_names() -> Result<()> {
477 let sales_path = temp_path("explicit-name-sales");
478 let costs_path = temp_path("explicit-name-costs");
479 write_test_workbook_on_sheet(&sales_path, "Sheet1")?;
480 write_test_workbook_on_sheet(&costs_path, "Sheet1")?;
481
482 let result = run_query_with_params_multi_inputs(
483 &[
484 WorkbookInput {
485 path: sales_path.as_path(),
486 sheet_name: Some("Sheet1"),
487 table_name: Some("sales"),
488 explicit_format: None,
489 },
490 WorkbookInput {
491 path: costs_path.as_path(),
492 sheet_name: Some("Sheet1"),
493 table_name: Some("costs"),
494 explicit_format: None,
495 },
496 ],
497 "SELECT COUNT(*) AS total_rows FROM sales UNION ALL SELECT COUNT(*) AS total_rows FROM costs",
498 &[],
499 true,
500 )?;
501
502 assert_eq!(result.columns, vec!["total_rows"]);
503 assert_eq!(
504 result.rows,
505 vec![vec![QueryValue::Integer(1)], vec![QueryValue::Integer(1)]]
506 );
507
508 fs::remove_file(&sales_path)?;
509 fs::remove_file(&costs_path)?;
510 Ok(())
511 }
512
513 #[test]
514 fn executes_sql_query_against_whole_xml_file() -> Result<()> {
515 let xml_path = temp_xml_path("xml-whole");
516 write_test_xml(&xml_path)?;
517
518 let result = run_query(
519 &xml_path,
520 None,
521 "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
522 true,
523 )?;
524
525 assert_eq!(result.columns, vec!["name", "price"]);
526 assert_eq!(
527 result.rows,
528 vec![vec![
529 QueryValue::Text("Keyboard".into()),
530 QueryValue::Real(12.5)
531 ]]
532 );
533
534 fs::remove_file(xml_path)?;
535 Ok(())
536 }
537
538 #[test]
539 fn executes_sql_query_against_xml_sheet_tag() -> Result<()> {
540 let xml_path = temp_xml_path("xml-sheet-tag");
541 write_test_xml_with_sections(&xml_path)?;
542
543 let result = run_query(
544 &xml_path,
545 Some("Archive"),
546 "SELECT name, price FROM table",
547 true,
548 )?;
549
550 assert_eq!(result.columns, vec!["name", "price"]);
551 assert_eq!(
552 result.rows,
553 vec![vec![
554 QueryValue::Text("Legacy Cable".into()),
555 QueryValue::Real(3.5)
556 ]]
557 );
558
559 fs::remove_file(xml_path)?;
560 Ok(())
561 }
562
563 #[test]
564 fn executes_query_against_heterogeneous_xlsx_and_xml_inputs() -> Result<()> {
565 let workbook_path = temp_path("mixed-xlsx");
566 let xml_path = temp_xml_path("mixed-xml");
567 write_test_workbook(&workbook_path)?;
568 write_test_xml(&xml_path)?;
569
570 let result = run_query_with_params_multi_inputs(
571 &[
572 WorkbookInput {
573 path: workbook_path.as_path(),
574 sheet_name: Some("Sheet1"),
575 table_name: None,
576 explicit_format: None,
577 },
578 WorkbookInput {
579 path: xml_path.as_path(),
580 sheet_name: None,
581 table_name: None,
582 explicit_format: None,
583 },
584 ],
585 "SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2",
586 &[],
587 true,
588 )?;
589
590 assert_eq!(result.columns, vec!["total_rows"]);
591 assert_eq!(
592 result.rows,
593 vec![vec![QueryValue::Integer(2)], vec![QueryValue::Integer(2)]]
594 );
595
596 fs::remove_file(&workbook_path)?;
597 fs::remove_file(&xml_path)?;
598 Ok(())
599 }
600
601 #[test]
602 fn executes_sql_query_against_csv_file() -> Result<()> {
603 let csv_path = temp_csv_path("csv");
604 write_test_csv(&csv_path)?;
605
606 let result = run_query(
607 &csv_path,
608 None,
609 "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
610 true,
611 )?;
612
613 assert_eq!(result.columns, vec!["name", "price"]);
614 assert_eq!(
615 result.rows,
616 vec![vec![
617 QueryValue::Text("Keyboard".into()),
618 QueryValue::Real(12.5)
619 ]]
620 );
621
622 fs::remove_file(csv_path)?;
623 Ok(())
624 }
625
626 #[test]
627 fn supports_configurable_type_inference_options() -> Result<()> {
628 let csv_path = temp_csv_path("csv-custom-inference");
629 write_test_csv_with_custom_inference_values(&csv_path)?;
630
631 let options = TypeInferenceOptions {
632 infer_types: true,
633 decimal_comma: true,
634 date_format: Some("%d/%m/%Y".to_owned()),
635 null_values: vec!["N/A".to_owned()],
636 true_values: vec!["YES".to_owned()],
637 false_values: vec!["NO".to_owned()],
638 };
639
640 let workbook_inputs = [WorkbookInput {
641 path: csv_path.as_path(),
642 sheet_name: None,
643 table_name: None,
644 explicit_format: None,
645 }];
646 let result = run_query_with_params_multi_inputs_and_options(
647 &workbook_inputs,
648 "SELECT amount, flag, date, note FROM table ORDER BY amount",
649 &[],
650 &options,
651 true,
652 )?;
653
654 assert_eq!(result.columns, vec!["amount", "flag", "date", "note"]);
655 assert_eq!(
656 result.rows,
657 vec![
658 vec![
659 QueryValue::Real(1.5),
660 QueryValue::Integer(1),
661 QueryValue::Text("2025-12-31".into()),
662 QueryValue::Null
663 ],
664 vec![
665 QueryValue::Real(2.0),
666 QueryValue::Integer(0),
667 QueryValue::Text("2026-01-01".into()),
668 QueryValue::Text("ok".into())
669 ]
670 ]
671 );
672
673 fs::remove_file(csv_path)?;
674 Ok(())
675 }
676
677 #[test]
678 fn supports_all_text_mode() -> Result<()> {
679 let csv_path = temp_csv_path("csv-all-text");
680 write_test_csv(&csv_path)?;
681
682 let options = TypeInferenceOptions {
683 infer_types: false,
684 ..TypeInferenceOptions::default()
685 };
686 let workbook_inputs = [WorkbookInput {
687 path: csv_path.as_path(),
688 sheet_name: None,
689 table_name: None,
690 explicit_format: None,
691 }];
692 let result = run_query_with_params_multi_inputs_and_options(
693 &workbook_inputs,
694 "SELECT name, price, active FROM table ORDER BY name DESC",
695 &[],
696 &options,
697 true,
698 )?;
699
700 assert_eq!(
701 result.rows,
702 vec![
703 vec![
704 QueryValue::Text("Keyboard".into()),
705 QueryValue::Text("12.5".into()),
706 QueryValue::Text("true".into())
707 ],
708 vec![
709 QueryValue::Text("Cable".into()),
710 QueryValue::Text("5".into()),
711 QueryValue::Text("false".into())
712 ]
713 ]
714 );
715
716 fs::remove_file(csv_path)?;
717 Ok(())
718 }
719
720 #[test]
721 fn executes_sql_query_against_csv_without_headers() -> Result<()> {
722 let csv_path = temp_csv_path("csv-no-headers");
723 write_test_csv_no_headers(&csv_path)?;
724
725 let result = run_query(
726 &csv_path,
727 None,
728 "SELECT column1, column2 FROM table WHERE column3 = 1 ORDER BY column2 DESC",
729 false,
730 )?;
731
732 assert_eq!(result.columns, vec!["column1", "column2"]);
733 assert_eq!(
734 result.rows,
735 vec![vec![
736 QueryValue::Text("Keyboard".into()),
737 QueryValue::Real(12.5)
738 ]]
739 );
740
741 fs::remove_file(csv_path)?;
742 Ok(())
743 }
744
745 #[test]
746 fn executes_sql_query_against_jsonl_file() -> Result<()> {
747 let jsonl_path = temp_jsonl_path("jsonl");
748 write_test_jsonl(&jsonl_path)?;
749
750 let result = run_query(
751 &jsonl_path,
752 None,
753 "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
754 true,
755 )?;
756
757 assert_eq!(result.columns, vec!["name", "price"]);
758 assert_eq!(
759 result.rows,
760 vec![vec![
761 QueryValue::Text("Keyboard".into()),
762 QueryValue::Real(12.5)
763 ]]
764 );
765
766 fs::remove_file(jsonl_path)?;
767 Ok(())
768 }
769
770 #[test]
771 fn executes_sql_query_against_jsonl_file_with_sparse_columns() -> Result<()> {
772 let jsonl_path = temp_jsonl_path("jsonl-sparse");
773 write_test_jsonl_with_sparse_columns(&jsonl_path)?;
774
775 let result = run_query(
776 &jsonl_path,
777 None,
778 "SELECT name, price, active FROM table ORDER BY name DESC",
779 true,
780 )?;
781
782 assert_eq!(result.columns, vec!["name", "price", "active"]);
783 assert_eq!(
784 result.rows,
785 vec![
786 vec![
787 QueryValue::Text("Keyboard".into()),
788 QueryValue::Real(12.5),
789 QueryValue::Null
790 ],
791 vec![
792 QueryValue::Text("Cable".into()),
793 QueryValue::Null,
794 QueryValue::Integer(0)
795 ]
796 ]
797 );
798
799 fs::remove_file(jsonl_path)?;
800 Ok(())
801 }
802
803 #[test]
804 fn executes_sql_query_against_json_file() -> Result<()> {
805 let json_path = temp_json_path("json");
806 write_test_json(&json_path)?;
807
808 let result = run_query(
809 &json_path,
810 None,
811 "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
812 true,
813 )?;
814
815 assert_eq!(result.columns, vec!["name", "price"]);
816 assert_eq!(
817 result.rows,
818 vec![vec![
819 QueryValue::Text("Keyboard".into()),
820 QueryValue::Real(12.5)
821 ]]
822 );
823
824 fs::remove_file(json_path)?;
825 Ok(())
826 }
827
828 #[test]
829 fn executes_sql_query_against_json_sheet_key() -> Result<()> {
830 let json_path = temp_json_path("json-sheet-key");
831 write_test_json_with_sections(&json_path)?;
832
833 let result = run_query(
834 &json_path,
835 Some("Archive"),
836 "SELECT name, price FROM table",
837 true,
838 )?;
839
840 assert_eq!(result.columns, vec!["name", "price"]);
841 assert_eq!(
842 result.rows,
843 vec![vec![
844 QueryValue::Text("Legacy Cable".into()),
845 QueryValue::Real(3.5)
846 ]]
847 );
848
849 fs::remove_file(json_path)?;
850 Ok(())
851 }
852
853 #[test]
854 fn executes_sql_query_against_first_markdown_table_by_default() -> Result<()> {
855 let markdown_path = temp_markdown_path("markdown-default");
856 write_test_markdown_with_tables(&markdown_path)?;
857
858 let result = run_query(
859 &markdown_path,
860 None,
861 "SELECT name, price FROM table WHERE active = 1",
862 true,
863 )?;
864
865 assert_eq!(result.columns, vec!["name", "price"]);
866 assert_eq!(
867 result.rows,
868 vec![vec![
869 QueryValue::Text("Keyboard".into()),
870 QueryValue::Real(12.5)
871 ]]
872 );
873
874 fs::remove_file(markdown_path)?;
875 Ok(())
876 }
877
878 #[test]
879 fn reports_actionable_error_for_csv_selector() -> Result<()> {
880 let csv_path = temp_csv_path("csv-selector-error");
881 write_test_csv(&csv_path)?;
882
883 let error = run_query(&csv_path, Some("Sheet1"), "SELECT name FROM table", true)
884 .expect_err("CSV selector should fail");
885 let message = error.to_string();
886 assert!(message.contains("does not support selector 'Sheet1'"));
887 assert!(message.contains("Remove ':Sheet1'"));
888
889 fs::remove_file(csv_path)?;
890 Ok(())
891 }
892
893 #[test]
894 fn reports_actionable_error_for_jsonl_selector() -> Result<()> {
895 let jsonl_path = temp_jsonl_path("jsonl-selector-error");
896 write_test_jsonl(&jsonl_path)?;
897
898 let error = run_query(&jsonl_path, Some("Records"), "SELECT name FROM table", true)
899 .expect_err("JSONL selector should fail");
900 let message = error.to_string();
901 assert!(message.contains("does not support selector 'Records'"));
902 assert!(message.contains("Remove ':Records'"));
903
904 fs::remove_file(jsonl_path)?;
905 Ok(())
906 }
907
908 #[test]
909 fn reports_json_key_error_with_available_keys() -> Result<()> {
910 let json_path = temp_json_path("json-missing-key");
911 write_test_json_with_sections(&json_path)?;
912
913 let error = run_query(&json_path, Some("Missing"), "SELECT name FROM table", true)
914 .expect_err("missing JSON key should fail");
915 let message = error.to_string();
916 assert!(message.contains("JSON key 'Missing' not found"));
917 assert!(message.contains("Available keys: Archive, Inventory"));
918
919 fs::remove_file(json_path)?;
920 Ok(())
921 }
922
923 #[test]
924 fn reports_invalid_markdown_selector_with_guidance() -> Result<()> {
925 let markdown_path = temp_markdown_path("markdown-invalid-selector");
926 write_test_markdown_with_tables(&markdown_path)?;
927
928 let error = run_query(&markdown_path, Some("abc"), "SELECT name FROM table", true)
929 .expect_err("non-numeric markdown selector should fail");
930 let message = error.to_string();
931 assert!(message.contains("invalid Markdown table selector 'abc'"));
932 assert!(message.contains("':1'"));
933
934 fs::remove_file(markdown_path)?;
935 Ok(())
936 }
937
938 #[test]
939 fn reports_empty_markdown_table_as_actionable_error() -> Result<()> {
940 let markdown_path = temp_markdown_path("markdown-empty-table");
941 write_test_markdown_with_headers_only(&markdown_path)?;
942
943 let error = run_query(&markdown_path, None, "SELECT name FROM table", true)
944 .expect_err("empty markdown table should fail");
945 let message = error.to_string();
946 assert!(message.contains("is empty (no data rows)"));
947
948 fs::remove_file(markdown_path)?;
949 Ok(())
950 }
951
952 #[test]
953 fn reports_unknown_table_with_inspection_hint() -> Result<()> {
954 let workbook_path = temp_path("unknown-table-query");
955 write_test_workbook(&workbook_path)?;
956
957 let error = run_query(
958 &workbook_path,
959 Some("Sheet1"),
960 "SELECT * FROM missing_table",
961 true,
962 )
963 .expect_err("unknown table should fail");
964 let message = error.to_string();
965 assert!(message.contains("unknown table 'missing_table'"));
966 assert!(message.contains("qf tables --input"));
967
968 fs::remove_file(workbook_path)?;
969 Ok(())
970 }
971
972 #[test]
973 fn reports_unknown_column_with_schema_hint() -> Result<()> {
974 let workbook_path = temp_path("unknown-column-query");
975 write_test_workbook(&workbook_path)?;
976
977 let error = run_query(
978 &workbook_path,
979 Some("Sheet1"),
980 "SELECT missing_column FROM table",
981 true,
982 )
983 .expect_err("unknown column should fail");
984 let message = error.to_string();
985 assert!(message.contains("unknown column 'missing_column'"));
986 assert!(message.contains("qf schema --input"));
987
988 fs::remove_file(workbook_path)?;
989 Ok(())
990 }
991
992 #[test]
993 fn executes_sql_query_against_markdown_table_by_numeric_key() -> Result<()> {
994 let markdown_path = temp_markdown_path("markdown-key");
995 write_test_markdown_with_tables(&markdown_path)?;
996
997 let result = run_query(
998 &markdown_path,
999 Some("2"),
1000 "SELECT name, price FROM table",
1001 true,
1002 )?;
1003
1004 assert_eq!(result.columns, vec!["name", "price"]);
1005 assert_eq!(
1006 result.rows,
1007 vec![vec![
1008 QueryValue::Text("Legacy Cable".into()),
1009 QueryValue::Real(3.5)
1010 ]]
1011 );
1012
1013 fs::remove_file(markdown_path)?;
1014 Ok(())
1015 }
1016
1017 #[test]
1018 fn executes_query_against_heterogeneous_xlsx_xml_csv_jsonl_json_markdown_inputs() -> Result<()>
1019 {
1020 let workbook_path = temp_path("mixed4-xlsx");
1021 let xml_path = temp_xml_path("mixed4-xml");
1022 let csv_path = temp_csv_path("mixed4-csv");
1023 let jsonl_path = temp_jsonl_path("mixed4-jsonl");
1024 let json_path = temp_json_path("mixed4-json");
1025 let markdown_path = temp_markdown_path("mixed4-markdown");
1026 write_test_workbook(&workbook_path)?;
1027 write_test_xml(&xml_path)?;
1028 write_test_csv(&csv_path)?;
1029 write_test_jsonl(&jsonl_path)?;
1030 write_test_json(&json_path)?;
1031 write_test_markdown_with_tables(&markdown_path)?;
1032
1033 let result = run_query_with_params_multi_inputs(
1034 &[
1035 WorkbookInput {
1036 path: workbook_path.as_path(),
1037 sheet_name: Some("Sheet1"),
1038 table_name: None,
1039 explicit_format: None,
1040 },
1041 WorkbookInput {
1042 path: xml_path.as_path(),
1043 sheet_name: None,
1044 table_name: None,
1045 explicit_format: None,
1046 },
1047 WorkbookInput {
1048 path: csv_path.as_path(),
1049 sheet_name: None,
1050 table_name: None,
1051 explicit_format: None,
1052 },
1053 WorkbookInput {
1054 path: jsonl_path.as_path(),
1055 sheet_name: None,
1056 table_name: None,
1057 explicit_format: None,
1058 },
1059 WorkbookInput {
1060 path: json_path.as_path(),
1061 sheet_name: None,
1062 table_name: None,
1063 explicit_format: None,
1064 },
1065 WorkbookInput {
1066 path: markdown_path.as_path(),
1067 sheet_name: None,
1068 table_name: None,
1069 explicit_format: None,
1070 },
1071 ],
1072 "SELECT COUNT(*) AS total_rows FROM table UNION ALL SELECT COUNT(*) AS total_rows FROM table2 UNION ALL SELECT COUNT(*) AS total_rows FROM table3 UNION ALL SELECT COUNT(*) AS total_rows FROM table4 UNION ALL SELECT COUNT(*) AS total_rows FROM table5 UNION ALL SELECT COUNT(*) AS total_rows FROM table6",
1073 &[],
1074 true,
1075 )?;
1076
1077 assert_eq!(result.columns, vec!["total_rows"]);
1078 assert_eq!(
1079 result.rows,
1080 vec![
1081 vec![QueryValue::Integer(2)],
1082 vec![QueryValue::Integer(2)],
1083 vec![QueryValue::Integer(2)],
1084 vec![QueryValue::Integer(2)],
1085 vec![QueryValue::Integer(2)],
1086 vec![QueryValue::Integer(2)]
1087 ]
1088 );
1089
1090 fs::remove_file(&workbook_path)?;
1091 fs::remove_file(&xml_path)?;
1092 fs::remove_file(&csv_path)?;
1093 fs::remove_file(&jsonl_path)?;
1094 fs::remove_file(&json_path)?;
1095 fs::remove_file(&markdown_path)?;
1096 Ok(())
1097 }
1098
1099 #[test]
1100 fn writes_query_result_to_xlsx() -> Result<()> {
1101 let output_path = temp_path("output");
1102 let result = QueryResult {
1103 columns: vec!["item".into(), "total".into()],
1104 rows: vec![vec![
1105 QueryValue::Text("Mouse".into()),
1106 QueryValue::Integer(3),
1107 ]],
1108 };
1109
1110 write_xlsx(&result, &output_path)?;
1111
1112 let written = run_query(
1113 &output_path,
1114 Some("Sheet1"),
1115 "SELECT item, total FROM table",
1116 true,
1117 )?;
1118
1119 assert_eq!(written.columns, vec!["item", "total"]);
1120 assert_eq!(
1121 written.rows,
1122 vec![vec![
1123 QueryValue::Text("Mouse".into()),
1124 QueryValue::Real(3.0)
1125 ]]
1126 );
1127
1128 fs::remove_file(output_path)?;
1129 Ok(())
1130 }
1131
1132 #[test]
1133 fn writes_query_result_to_parquet() -> Result<()> {
1134 let output_path = temp_parquet_path("output");
1135 let result = QueryResult {
1136 columns: vec!["item".into(), "qty".into(), "price".into()],
1137 rows: vec![
1138 vec![
1139 QueryValue::Text("Mouse".into()),
1140 QueryValue::Integer(3),
1141 QueryValue::Real(9.99),
1142 ],
1143 vec![
1144 QueryValue::Text("Keyboard".into()),
1145 QueryValue::Integer(1),
1146 QueryValue::Null,
1147 ],
1148 ],
1149 };
1150
1151 write_parquet(&result, &output_path)?;
1152
1153 let bytes = fs::read(&output_path)?;
1155 assert!(bytes.len() > 8, "parquet file should have content");
1156 assert_eq!(&bytes[..4], b"PAR1", "parquet file should start with PAR1");
1157 assert_eq!(
1158 &bytes[bytes.len() - 4..],
1159 b"PAR1",
1160 "parquet file should end with PAR1"
1161 );
1162
1163 fs::remove_file(output_path)?;
1164 Ok(())
1165 }
1166
1167 #[test]
1168 fn writes_query_result_to_feather() -> Result<()> {
1169 let output_path = temp_feather_path("output");
1170 let result = QueryResult {
1171 columns: vec!["item".into(), "qty".into(), "price".into()],
1172 rows: vec![
1173 vec![
1174 QueryValue::Text("Mouse".into()),
1175 QueryValue::Integer(3),
1176 QueryValue::Real(9.99),
1177 ],
1178 vec![
1179 QueryValue::Text("Keyboard".into()),
1180 QueryValue::Integer(1),
1181 QueryValue::Null,
1182 ],
1183 ],
1184 };
1185
1186 write_feather(&result, &output_path)?;
1187
1188 let bytes = fs::read(&output_path)?;
1189 assert!(bytes.len() > 12, "feather file should have content");
1190 assert_eq!(
1191 &bytes[..6],
1192 b"ARROW1",
1193 "feather file should start with ARROW1"
1194 );
1195 assert_eq!(
1196 &bytes[bytes.len() - 6..],
1197 b"ARROW1",
1198 "feather file should end with ARROW1"
1199 );
1200
1201 fs::remove_file(output_path)?;
1202 Ok(())
1203 }
1204
1205 #[test]
1206 fn executes_sql_query_against_parquet_file() -> Result<()> {
1207 let parquet_path = temp_parquet_path("input");
1208 let result = QueryResult {
1209 columns: vec!["name".into(), "price".into(), "active".into()],
1210 rows: vec![
1211 vec![
1212 QueryValue::Text("Keyboard".into()),
1213 QueryValue::Real(12.5),
1214 QueryValue::Integer(1),
1215 ],
1216 vec![
1217 QueryValue::Text("Cable".into()),
1218 QueryValue::Real(5.0),
1219 QueryValue::Integer(0),
1220 ],
1221 ],
1222 };
1223 write_parquet(&result, &parquet_path)?;
1224
1225 let queried = run_query(
1226 &parquet_path,
1227 None,
1228 "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
1229 true,
1230 )?;
1231
1232 assert_eq!(queried.columns, vec!["name", "price"]);
1233 assert_eq!(
1234 queried.rows,
1235 vec![vec![
1236 QueryValue::Text("Keyboard".into()),
1237 QueryValue::Real(12.5)
1238 ]]
1239 );
1240
1241 fs::remove_file(parquet_path)?;
1242 Ok(())
1243 }
1244
1245 #[test]
1246 fn executes_sql_query_against_feather_file() -> Result<()> {
1247 let feather_path = temp_feather_path("input");
1248 let result = QueryResult {
1249 columns: vec!["name".into(), "price".into(), "active".into()],
1250 rows: vec![
1251 vec![
1252 QueryValue::Text("Keyboard".into()),
1253 QueryValue::Real(12.5),
1254 QueryValue::Integer(1),
1255 ],
1256 vec![
1257 QueryValue::Text("Cable".into()),
1258 QueryValue::Real(5.0),
1259 QueryValue::Integer(0),
1260 ],
1261 ],
1262 };
1263 write_feather(&result, &feather_path)?;
1264
1265 let queried = run_query(
1266 &feather_path,
1267 None,
1268 "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
1269 true,
1270 )?;
1271
1272 assert_eq!(queried.columns, vec!["name", "price"]);
1273 assert_eq!(
1274 queried.rows,
1275 vec![vec![
1276 QueryValue::Text("Keyboard".into()),
1277 QueryValue::Real(12.5)
1278 ]]
1279 );
1280
1281 fs::remove_file(feather_path)?;
1282 Ok(())
1283 }
1284
1285 #[test]
1286 fn reports_actionable_error_for_parquet_selector() -> Result<()> {
1287 let parquet_path = temp_parquet_path("parquet-selector-error");
1288 let result = QueryResult {
1289 columns: vec!["name".into()],
1290 rows: vec![vec![QueryValue::Text("Keyboard".into())]],
1291 };
1292 write_parquet(&result, &parquet_path)?;
1293
1294 let error = run_query(
1295 &parquet_path,
1296 Some("Sheet1"),
1297 "SELECT name FROM table",
1298 true,
1299 )
1300 .expect_err("Parquet selector should fail");
1301 let message = error.to_string();
1302 assert!(message.contains("does not support selector 'Sheet1'"));
1303 assert!(message.contains("Remove ':Sheet1'"));
1304
1305 fs::remove_file(parquet_path)?;
1306 Ok(())
1307 }
1308
1309 #[test]
1310 fn reports_actionable_error_for_feather_selector() -> Result<()> {
1311 let feather_path = temp_feather_path("feather-selector-error");
1312 let result = QueryResult {
1313 columns: vec!["name".into()],
1314 rows: vec![vec![QueryValue::Text("Keyboard".into())]],
1315 };
1316 write_feather(&result, &feather_path)?;
1317
1318 let error = run_query(
1319 &feather_path,
1320 Some("Sheet1"),
1321 "SELECT name FROM table",
1322 true,
1323 )
1324 .expect_err("Feather selector should fail");
1325 let message = error.to_string();
1326 assert!(message.contains("does not support selector 'Sheet1'"));
1327 assert!(message.contains("Remove ':Sheet1'"));
1328
1329 fs::remove_file(feather_path)?;
1330 Ok(())
1331 }
1332
1333 #[test]
1334 fn renders_csv_with_escaping() {
1335 let result = QueryResult {
1336 columns: vec!["name".into(), "notes".into()],
1337 rows: vec![vec![
1338 QueryValue::Text("Mouse".into()),
1339 QueryValue::Text("line1,line2".into()),
1340 ]],
1341 };
1342
1343 assert_eq!(render_csv(&result), "name,notes\nMouse,\"line1,line2\"");
1344 }
1345
1346 #[test]
1347 fn streams_csv_with_escaping() {
1348 let result = QueryResult {
1349 columns: vec!["name".into(), "notes".into()],
1350 rows: vec![vec![
1351 QueryValue::Text("Mouse".into()),
1352 QueryValue::Text("line1,line2".into()),
1353 ]],
1354 };
1355 let mut output = Vec::new();
1356
1357 write_csv(&result, &mut output).expect("streaming csv should succeed");
1358
1359 assert_eq!(
1360 String::from_utf8(output).expect("valid utf-8"),
1361 "name,notes\nMouse,\"line1,line2\""
1362 );
1363 }
1364
1365 #[test]
1366 fn renders_text_with_aligned_columns() {
1367 let result = QueryResult {
1368 columns: vec!["mese".into(), "totale_ore".into()],
1369 rows: vec![
1370 vec![
1371 QueryValue::Text("2026-01-01".into()),
1372 QueryValue::Real(10.5),
1373 ],
1374 vec![
1375 QueryValue::Text("2026-12-01".into()),
1376 QueryValue::Integer(2),
1377 ],
1378 ],
1379 };
1380
1381 assert_eq!(
1382 render_text(&result),
1383 "mese | totale_ore\n-----------+-----------\n2026-01-01 | 10.5 \n2026-12-01 | 2 "
1384 );
1385 }
1386
1387 #[test]
1388 fn streams_text_with_aligned_columns() {
1389 let result = QueryResult {
1390 columns: vec!["mese".into(), "totale_ore".into()],
1391 rows: vec![
1392 vec![
1393 QueryValue::Text("2026-01-01".into()),
1394 QueryValue::Real(10.5),
1395 ],
1396 vec![
1397 QueryValue::Text("2026-12-01".into()),
1398 QueryValue::Integer(2),
1399 ],
1400 ],
1401 };
1402 let mut output = Vec::new();
1403
1404 write_text(&result, &mut output).expect("streaming text should succeed");
1405
1406 assert_eq!(
1407 String::from_utf8(output).expect("valid utf-8"),
1408 "mese | totale_ore\n-----------+-----------\n2026-01-01 | 10.5 \n2026-12-01 | 2 "
1409 );
1410 }
1411
1412 #[test]
1413 fn renders_jsonl() {
1414 let result = QueryResult {
1415 columns: vec!["item".into(), "stock".into(), "note".into()],
1416 rows: vec![vec![
1417 QueryValue::Text("Desk".into()),
1418 QueryValue::Integer(8),
1419 QueryValue::Null,
1420 ]],
1421 };
1422
1423 assert_eq!(
1424 render_jsonl(&result),
1425 "{\"item\":\"Desk\",\"stock\":8,\"note\":null}"
1426 );
1427 }
1428
1429 #[test]
1430 fn streams_jsonl() {
1431 let result = QueryResult {
1432 columns: vec!["item".into(), "stock".into(), "note".into()],
1433 rows: vec![vec![
1434 QueryValue::Text("Desk".into()),
1435 QueryValue::Integer(8),
1436 QueryValue::Null,
1437 ]],
1438 };
1439 let mut output = Vec::new();
1440
1441 write_jsonl(&result, &mut output).expect("streaming jsonl should succeed");
1442
1443 assert_eq!(
1444 String::from_utf8(output).expect("valid utf-8"),
1445 "{\"item\":\"Desk\",\"stock\":8,\"note\":null}"
1446 );
1447 }
1448
1449 #[test]
1450 fn renders_json() {
1451 let result = QueryResult {
1452 columns: vec!["item".into(), "stock".into(), "note".into()],
1453 rows: vec![vec![
1454 QueryValue::Text("Desk".into()),
1455 QueryValue::Integer(8),
1456 QueryValue::Null,
1457 ]],
1458 };
1459
1460 assert_eq!(
1461 render_json(&result),
1462 "[{\"item\":\"Desk\",\"stock\":8,\"note\":null}]"
1463 );
1464 }
1465
1466 #[test]
1467 fn renders_markdown() {
1468 let result = QueryResult {
1469 columns: vec!["category".into(), "total".into()],
1470 rows: vec![vec![
1471 QueryValue::Text("electronics".into()),
1472 QueryValue::Integer(47),
1473 ]],
1474 };
1475
1476 assert_eq!(
1477 render_markdown(&result),
1478 "| category | total |\n| --- | --- |\n| electronics | 47 |"
1479 );
1480 }
1481
1482 fn write_test_json_object(path: &Path) -> Result<()> {
1485 fs::write(path, r#"{"name":"Alice","age":30,"city":"Rome"}"#)?;
1486 Ok(())
1487 }
1488
1489 fn write_test_json_nested(path: &Path) -> Result<()> {
1490 fs::write(
1491 path,
1492 r#"[{"user":{"name":"Alice","address":{"city":"Rome"}},"score":10},{"user":{"name":"Bob","address":{"city":"Paris"}},"score":20}]"#,
1493 )?;
1494 Ok(())
1495 }
1496
1497 #[test]
1498 fn json_object_mode_turns_keys_into_rows() -> Result<()> {
1499 let json_path = temp_json_path("json-object-mode");
1500 write_test_json_object(&json_path)?;
1501
1502 let opts = ExtractionOptions {
1503 json_mode: JsonMode::Object,
1504 ..ExtractionOptions::default()
1505 };
1506 let inputs = [WorkbookInput {
1507 path: &json_path,
1508 sheet_name: None,
1509 table_name: None,
1510 explicit_format: None,
1511 }];
1512 let result = run_query_with_params_multi_inputs_and_options_and_normalization(
1513 &inputs,
1514 "SELECT key, value FROM table ORDER BY key",
1515 &[],
1516 &TypeInferenceOptions::default(),
1517 &InputNormalizationOptions::default(),
1518 &opts,
1519 true,
1520 )?;
1521
1522 assert_eq!(result.columns, vec!["key", "value"]);
1523 assert_eq!(result.rows.len(), 3);
1525 assert_eq!(result.rows[0][0], QueryValue::Text("age".into()));
1526 assert_eq!(result.rows[0][1], QueryValue::Integer(30));
1527 assert_eq!(result.rows[1][0], QueryValue::Text("city".into()));
1528 assert_eq!(result.rows[1][1], QueryValue::Text("Rome".into()));
1529 assert_eq!(result.rows[2][0], QueryValue::Text("name".into()));
1530 assert_eq!(result.rows[2][1], QueryValue::Text("Alice".into()));
1531
1532 fs::remove_file(json_path)?;
1533 Ok(())
1534 }
1535
1536 #[test]
1537 fn json_flatten_mode_expands_nested_objects() -> Result<()> {
1538 let json_path = temp_json_path("json-flatten-mode");
1539 write_test_json_nested(&json_path)?;
1540
1541 let opts = ExtractionOptions {
1542 json_mode: JsonMode::Flatten,
1543 ..ExtractionOptions::default()
1544 };
1545 let inputs = [WorkbookInput {
1546 path: &json_path,
1547 sheet_name: None,
1548 table_name: None,
1549 explicit_format: None,
1550 }];
1551 let result = run_query_with_params_multi_inputs_and_options_and_normalization(
1552 &inputs,
1553 "SELECT \"user.name\", \"user.address.city\", score FROM table ORDER BY score",
1554 &[],
1555 &TypeInferenceOptions::default(),
1556 &InputNormalizationOptions::default(),
1557 &opts,
1558 true,
1559 )?;
1560
1561 assert!(result.columns.contains(&"user.name".to_owned()));
1562 assert!(result.columns.contains(&"user.address.city".to_owned()));
1563 assert_eq!(result.rows.len(), 2);
1564 let alice_col = result
1566 .columns
1567 .iter()
1568 .position(|c| c == "user.name")
1569 .unwrap();
1570 let city_col = result
1571 .columns
1572 .iter()
1573 .position(|c| c == "user.address.city")
1574 .unwrap();
1575 let score_col = result.columns.iter().position(|c| c == "score").unwrap();
1576 assert_eq!(result.rows[0][alice_col], QueryValue::Text("Alice".into()));
1577 assert_eq!(result.rows[0][city_col], QueryValue::Text("Rome".into()));
1578 assert_eq!(result.rows[0][score_col], QueryValue::Integer(10));
1579
1580 fs::remove_file(json_path)?;
1581 Ok(())
1582 }
1583
1584 #[test]
1585 fn json_array_mode_is_default_behavior() -> Result<()> {
1586 let json_path = temp_json_path("json-array-mode-default");
1587 write_test_json(&json_path)?;
1588
1589 let opts = ExtractionOptions {
1590 json_mode: JsonMode::Array,
1591 ..ExtractionOptions::default()
1592 };
1593 let inputs = [WorkbookInput {
1594 path: &json_path,
1595 sheet_name: None,
1596 table_name: None,
1597 explicit_format: None,
1598 }];
1599 let result = run_query_with_params_multi_inputs_and_options_and_normalization(
1600 &inputs,
1601 "SELECT name, price FROM table WHERE active = 1",
1602 &[],
1603 &TypeInferenceOptions::default(),
1604 &InputNormalizationOptions::default(),
1605 &opts,
1606 true,
1607 )?;
1608
1609 assert_eq!(result.columns, vec!["name", "price"]);
1610 assert_eq!(
1611 result.rows,
1612 vec![vec![
1613 QueryValue::Text("Keyboard".into()),
1614 QueryValue::Real(12.5)
1615 ]]
1616 );
1617
1618 fs::remove_file(json_path)?;
1619 Ok(())
1620 }
1621
1622 fn write_test_xml_with_attributes(path: &Path) -> Result<()> {
1625 fs::write(
1626 path,
1627 r#"<?xml version="1.0" encoding="UTF-8"?>
1628<products>
1629 <product id="1" name="Keyboard" price="12.5" active="true"/>
1630 <product id="2" name="Cable" price="5" active="false"/>
1631</products>
1632"#,
1633 )?;
1634 Ok(())
1635 }
1636
1637 fn write_test_xml_leaf_elements(path: &Path) -> Result<()> {
1638 fs::write(
1639 path,
1640 r#"<?xml version="1.0" encoding="UTF-8"?>
1641<config>
1642 <host>localhost</host>
1643 <port>5432</port>
1644 <database>mydb</database>
1645</config>
1646"#,
1647 )?;
1648 Ok(())
1649 }
1650
1651 #[test]
1652 fn xml_attributes_mode_extracts_attributes_as_columns() -> Result<()> {
1653 let xml_path = temp_xml_path("xml-attributes-mode");
1654 write_test_xml_with_attributes(&xml_path)?;
1655
1656 let opts = ExtractionOptions {
1657 xml_mode: XmlMode::Attributes,
1658 ..ExtractionOptions::default()
1659 };
1660 let inputs = [WorkbookInput {
1661 path: &xml_path,
1662 sheet_name: None,
1663 table_name: None,
1664 explicit_format: None,
1665 }];
1666 let result = run_query_with_params_multi_inputs_and_options_and_normalization(
1667 &inputs,
1668 "SELECT id, name, price FROM table ORDER BY id",
1669 &[],
1670 &TypeInferenceOptions::default(),
1671 &InputNormalizationOptions::default(),
1672 &opts,
1673 true,
1674 )?;
1675
1676 assert_eq!(result.rows.len(), 2);
1677 let id_col = result.columns.iter().position(|c| c == "id").unwrap();
1678 let name_col = result.columns.iter().position(|c| c == "name").unwrap();
1679 assert_eq!(result.rows[0][id_col], QueryValue::Integer(1));
1680 assert_eq!(
1681 result.rows[0][name_col],
1682 QueryValue::Text("Keyboard".into())
1683 );
1684 assert_eq!(result.rows[1][id_col], QueryValue::Integer(2));
1685 assert_eq!(result.rows[1][name_col], QueryValue::Text("Cable".into()));
1686
1687 fs::remove_file(xml_path)?;
1688 Ok(())
1689 }
1690
1691 #[test]
1692 fn xml_descendants_mode_collects_leaf_elements_as_tag_value_rows() -> Result<()> {
1693 let xml_path = temp_xml_path("xml-descendants-mode");
1694 write_test_xml_leaf_elements(&xml_path)?;
1695
1696 let opts = ExtractionOptions {
1697 xml_mode: XmlMode::Descendants,
1698 ..ExtractionOptions::default()
1699 };
1700 let inputs = [WorkbookInput {
1701 path: &xml_path,
1702 sheet_name: None,
1703 table_name: None,
1704 explicit_format: None,
1705 }];
1706 let result = run_query_with_params_multi_inputs_and_options_and_normalization(
1707 &inputs,
1708 "SELECT tag, value FROM table ORDER BY tag",
1709 &[],
1710 &TypeInferenceOptions::default(),
1711 &InputNormalizationOptions::default(),
1712 &opts,
1713 true,
1714 )?;
1715
1716 assert_eq!(result.columns, vec!["tag", "value"]);
1717 assert_eq!(result.rows.len(), 3);
1718 assert_eq!(result.rows[0][0], QueryValue::Text("database".into()));
1720 assert_eq!(result.rows[0][1], QueryValue::Text("mydb".into()));
1721 assert_eq!(result.rows[1][0], QueryValue::Text("host".into()));
1722 assert_eq!(result.rows[1][1], QueryValue::Text("localhost".into()));
1723 assert_eq!(result.rows[2][0], QueryValue::Text("port".into()));
1724 assert_eq!(result.rows[2][1], QueryValue::Integer(5432));
1725
1726 fs::remove_file(xml_path)?;
1727 Ok(())
1728 }
1729
1730 #[test]
1731 fn xml_rows_mode_is_default_behavior() -> Result<()> {
1732 let xml_path = temp_xml_path("xml-rows-mode-default");
1733 write_test_xml(&xml_path)?;
1734
1735 let opts = ExtractionOptions {
1736 xml_mode: XmlMode::Rows,
1737 ..ExtractionOptions::default()
1738 };
1739 let inputs = [WorkbookInput {
1740 path: &xml_path,
1741 sheet_name: None,
1742 table_name: None,
1743 explicit_format: None,
1744 }];
1745 let result = run_query_with_params_multi_inputs_and_options_and_normalization(
1746 &inputs,
1747 "SELECT name, price FROM table WHERE active = 1 ORDER BY price DESC",
1748 &[],
1749 &TypeInferenceOptions::default(),
1750 &InputNormalizationOptions::default(),
1751 &opts,
1752 true,
1753 )?;
1754
1755 assert_eq!(result.columns, vec!["name", "price"]);
1756 assert_eq!(
1757 result.rows,
1758 vec![vec![
1759 QueryValue::Text("Keyboard".into()),
1760 QueryValue::Real(12.5)
1761 ]]
1762 );
1763
1764 fs::remove_file(xml_path)?;
1765 Ok(())
1766 }
1767
1768 #[test]
1769 fn executes_query_against_csv_from_clipboard() -> Result<()> {
1770 clipboard::set_test_text("product,price\nKeyboard,12.5\nCable,5.0\n");
1771
1772 let result = run_query(
1773 Path::new("@clipboard.csv"),
1774 None,
1775 "SELECT product FROM table WHERE CAST(price AS REAL) > 10",
1776 true,
1777 )?;
1778
1779 assert_eq!(result.columns, vec!["product"]);
1780 assert_eq!(
1781 result.rows,
1782 vec![vec![QueryValue::Text("Keyboard".to_owned())]]
1783 );
1784 Ok(())
1785 }
1786
1787 #[test]
1788 fn loads_markdown_table_from_clipboard_by_index() -> Result<()> {
1789 clipboard::set_test_text(
1790 "| name | value |\n| --- | --- |\n| first | 1 |\n\n| name | value |\n| --- | --- |\n| second | 2 |\n",
1791 );
1792
1793 let sheet = load_input(
1794 Path::new("@clipboard.md"),
1795 Some("2"),
1796 &TypeInferenceOptions::default(),
1797 &ExtractionOptions::default(),
1798 true,
1799 None,
1800 )?;
1801
1802 assert_eq!(sheet.columns, vec!["name", "value"]);
1803 assert_eq!(
1804 sheet.rows,
1805 vec![vec![
1806 QueryValue::Text("second".to_owned()),
1807 QueryValue::Integer(2),
1808 ]]
1809 );
1810 Ok(())
1811 }
1812}