nu_command/stor/
insert.rs

1use crate::database::{MEMORY_DB, SQLiteDatabase, values_to_sql};
2use nu_engine::command_prelude::*;
3use nu_protocol::Signals;
4use nu_protocol::shell_error::generic::GenericError;
5use rusqlite::params_from_iter;
6
7#[derive(Clone)]
8pub struct StorInsert;
9
10impl Command for StorInsert {
11    fn name(&self) -> &str {
12        "stor insert"
13    }
14
15    fn signature(&self) -> Signature {
16        Signature::build("stor insert")
17            .input_output_types(vec![
18                (Type::Nothing, Type::table()),
19                (Type::record(), Type::table()),
20                (Type::table(), Type::table()),
21                // FIXME Type::Any input added to disable pipeline input type checking, as run-time checks can raise undesirable type errors
22                // which aren't caught by the parser. see https://github.com/nushell/nushell/pull/14922 for more details
23                (Type::Any, Type::table()),
24            ])
25            .required_named(
26                "table-name",
27                SyntaxShape::String,
28                "Name of the table you want to insert into.",
29                Some('t'),
30            )
31            .named(
32                "data-record",
33                SyntaxShape::Record(vec![]),
34                "A record of column names and column values to insert into the specified table.",
35                Some('d'),
36            )
37            .allow_variants_without_examples(true)
38            .category(Category::Database)
39    }
40
41    fn description(&self) -> &str {
42        "Insert information into a specified table in the in-memory sqlite database."
43    }
44
45    fn search_terms(&self) -> Vec<&str> {
46        vec!["sqlite", "storing", "table", "saving"]
47    }
48
49    fn examples(&self) -> Vec<Example<'_>> {
50        vec![
51            Example {
52                description: "Insert data in the in-memory sqlite database using a data-record of column-name and column-value pairs",
53                example: "stor insert --table-name nudb --data-record {bool1: true, int1: 5, float1: 1.1, str1: fdncred, datetime1: 2023-04-17}",
54                result: None,
55            },
56            Example {
57                description: "Insert data through pipeline input as a record of column-name and column-value pairs",
58                example: "{bool1: true, int1: 5, float1: 1.1, str1: fdncred, datetime1: 2023-04-17} | stor insert --table-name nudb",
59                result: None,
60            },
61            Example {
62                description: "Insert data through pipeline input as a table literal",
63                example: "[[bool1 int1 float1]; [true 5 1.1], [false 8 3.14]] | stor insert --table-name nudb",
64                result: None,
65            },
66            Example {
67                description: "Insert ls entries",
68                example: "ls | stor insert --table-name files",
69                result: None,
70            },
71            Example {
72                description: "Insert nu records as json data",
73                example: "ls -l | each {{file: $in.name, metadata: ($in | reject name)}} | stor insert --table-name files_with_md",
74                result: None,
75            },
76        ]
77    }
78
79    fn run(
80        &self,
81        engine_state: &EngineState,
82        stack: &mut Stack,
83        call: &Call,
84        input: PipelineData,
85    ) -> Result<PipelineData, ShellError> {
86        let span = call.head;
87        let table_name: Option<String> = call.get_flag(engine_state, stack, "table-name")?;
88        let data_record: Option<Record> = call.get_flag(engine_state, stack, "data-record")?;
89        // let config = stack.get_config(engine_state);
90        let db = Box::new(SQLiteDatabase::new(
91            std::path::Path::new(MEMORY_DB),
92            Signals::empty(),
93        ));
94
95        let records = handle(span, data_record, input)?;
96
97        for record in records {
98            process(engine_state, table_name.clone(), span, &db, record)?;
99        }
100
101        Ok(Value::custom(db, span).into_pipeline_data())
102    }
103}
104
105fn handle(
106    span: Span,
107    data_record: Option<Record>,
108    input: PipelineData,
109) -> Result<Vec<Record>, ShellError> {
110    // Check for conflicting use of both pipeline input and flag
111    if let Some(record) = data_record {
112        if !matches!(input, PipelineData::Empty) {
113            return Err(ShellError::Generic(GenericError::new(
114                "Pipeline and Flag both being used",
115                "Use either pipeline input or '--data-record' parameter",
116                span,
117            )));
118        }
119        return Ok(vec![record]);
120    }
121
122    // Handle the input types
123    let values = match input {
124        PipelineData::Empty => {
125            return Err(ShellError::MissingParameter {
126                param_name: "requires a table or a record".into(),
127                span,
128            });
129        }
130        PipelineData::ListStream(stream, ..) => stream.into_iter().collect::<Vec<_>>(),
131        PipelineData::Value(Value::List { vals, .. }, ..) => vals,
132        PipelineData::Value(val, ..) => vec![val],
133        _ => {
134            return Err(ShellError::OnlySupportsThisInputType {
135                exp_input_type: "list or record".into(),
136                wrong_type: "".into(),
137                dst_span: span,
138                src_span: span,
139            });
140        }
141    };
142
143    values
144        .into_iter()
145        .map(|val| match val {
146            Value::Record { val, .. } => Ok(val.into_owned()),
147            other => Err(ShellError::OnlySupportsThisInputType {
148                exp_input_type: "record".into(),
149                wrong_type: other.get_type().to_string(),
150                dst_span: span,
151                src_span: other.span(),
152            }),
153        })
154        .collect()
155}
156
157fn process(
158    engine_state: &EngineState,
159    table_name: Option<String>,
160    span: Span,
161    db: &SQLiteDatabase,
162    record: Record,
163) -> Result<(), ShellError> {
164    if table_name.is_none() {
165        return Err(ShellError::MissingParameter {
166            param_name: "requires at table name".into(),
167            span,
168        });
169    }
170    let new_table_name = table_name.unwrap_or("table".into());
171
172    let mut create_stmt = format!("INSERT INTO {new_table_name} (");
173    let mut column_placeholders: Vec<String> = Vec::new();
174
175    let cols = record.columns();
176    cols.for_each(|col| {
177        column_placeholders.push(col.to_string());
178    });
179
180    create_stmt.push_str(&column_placeholders.join(", "));
181
182    // Values are set as placeholders.
183    create_stmt.push_str(") VALUES (");
184    let mut value_placeholders: Vec<String> = Vec::new();
185    for (index, _) in record.columns().enumerate() {
186        value_placeholders.push(format!("?{}", index + 1));
187    }
188    create_stmt.push_str(&value_placeholders.join(", "));
189    create_stmt.push(')');
190
191    // dbg!(&create_stmt);
192
193    // Get the params from the passed values
194    let params = values_to_sql(engine_state, record.values().cloned(), span)?;
195
196    if let Ok(conn) = db.open_connection() {
197        conn.execute(&create_stmt, params_from_iter(params))
198            .map_err(|err| {
199                ShellError::Generic(GenericError::new_internal(
200                    "Failed to insert using the SQLite connection in memory from insert.rs.",
201                    err.to_string(),
202                ))
203            })?;
204    };
205    // dbg!(db.clone());
206    Ok(())
207}
208
209#[cfg(test)]
210mod test {
211    use chrono::DateTime;
212
213    use super::*;
214
215    #[test]
216    fn test_examples() -> nu_test_support::Result {
217        nu_test_support::test().examples(StorInsert)
218    }
219
220    #[test]
221    fn test_process_with_simple_parameters() {
222        let db = Box::new(SQLiteDatabase::new(
223            std::path::Path::new(MEMORY_DB),
224            Signals::empty(),
225        ));
226        let create_stmt = "CREATE TABLE test_process_with_simple_parameters (
227            int_column INTEGER,
228            real_column REAL,
229            str_column VARCHAR(255),
230            bool_column BOOLEAN,
231            date_column DATETIME DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
232        )";
233
234        let conn = db
235            .open_connection()
236            .expect("Test was unable to open connection.");
237        conn.execute(create_stmt, [])
238            .expect("Failed to create table as part of test.");
239        let table_name = Some("test_process_with_simple_parameters".to_string());
240        let span = Span::test_data();
241        let mut columns = Record::new();
242        columns.insert("int_column".to_string(), Value::test_int(42));
243        columns.insert("real_column".to_string(), Value::test_float(3.1));
244        columns.insert(
245            "str_column".to_string(),
246            Value::test_string("SimpleString".to_string()),
247        );
248        columns.insert("bool_column".to_string(), Value::test_bool(true));
249        columns.insert(
250            "date_column".to_string(),
251            Value::test_date(
252                DateTime::parse_from_str("2021-12-30 00:00:00 +0000", "%Y-%m-%d %H:%M:%S %z")
253                    .expect("Date string should parse."),
254            ),
255        );
256
257        let result = process(&EngineState::new(), table_name, span, &db, columns);
258
259        assert!(result.is_ok());
260    }
261
262    #[test]
263    fn test_process_string_with_space() {
264        let db = Box::new(SQLiteDatabase::new(
265            std::path::Path::new(MEMORY_DB),
266            Signals::empty(),
267        ));
268        let create_stmt = "CREATE TABLE test_process_string_with_space (
269            str_column VARCHAR(255)
270        )";
271
272        let conn = db
273            .open_connection()
274            .expect("Test was unable to open connection.");
275        conn.execute(create_stmt, [])
276            .expect("Failed to create table as part of test.");
277        let table_name = Some("test_process_string_with_space".to_string());
278        let span = Span::test_data();
279        let mut columns = Record::new();
280        columns.insert(
281            "str_column".to_string(),
282            Value::test_string("String With Spaces".to_string()),
283        );
284
285        let result = process(&EngineState::new(), table_name, span, &db, columns);
286
287        assert!(result.is_ok());
288    }
289
290    #[test]
291    fn test_no_errors_when_string_too_long() {
292        let db = Box::new(SQLiteDatabase::new(
293            std::path::Path::new(MEMORY_DB),
294            Signals::empty(),
295        ));
296        let create_stmt = "CREATE TABLE test_errors_when_string_too_long (
297            str_column VARCHAR(8)
298        )";
299
300        let conn = db
301            .open_connection()
302            .expect("Test was unable to open connection.");
303        conn.execute(create_stmt, [])
304            .expect("Failed to create table as part of test.");
305        let table_name = Some("test_errors_when_string_too_long".to_string());
306        let span = Span::test_data();
307        let mut columns = Record::new();
308        columns.insert(
309            "str_column".to_string(),
310            Value::test_string("ThisIsALongString".to_string()),
311        );
312
313        let result = process(&EngineState::new(), table_name, span, &db, columns);
314        // SQLite uses dynamic typing, making any length acceptable for a varchar column
315        assert!(result.is_ok());
316    }
317
318    #[test]
319    fn test_no_errors_when_param_is_wrong_type() {
320        let db = Box::new(SQLiteDatabase::new(
321            std::path::Path::new(MEMORY_DB),
322            Signals::empty(),
323        ));
324        let create_stmt = "CREATE TABLE test_errors_when_param_is_wrong_type (
325            int_column INT
326        )";
327
328        let conn = db
329            .open_connection()
330            .expect("Test was unable to open connection.");
331        conn.execute(create_stmt, [])
332            .expect("Failed to create table as part of test.");
333        let table_name = Some("test_errors_when_param_is_wrong_type".to_string());
334        let span = Span::test_data();
335        let mut columns = Record::new();
336        columns.insert(
337            "int_column".to_string(),
338            Value::test_string("ThisIsTheWrongType".to_string()),
339        );
340
341        let result = process(&EngineState::new(), table_name, span, &db, columns);
342        // SQLite uses dynamic typing, making any type acceptable for a column
343        assert!(result.is_ok());
344    }
345
346    #[test]
347    fn test_errors_when_column_doesnt_exist() {
348        let db = Box::new(SQLiteDatabase::new(
349            std::path::Path::new(MEMORY_DB),
350            Signals::empty(),
351        ));
352        let create_stmt = "CREATE TABLE test_errors_when_column_doesnt_exist (
353            int_column INT
354        )";
355
356        let conn = db
357            .open_connection()
358            .expect("Test was unable to open connection.");
359        conn.execute(create_stmt, [])
360            .expect("Failed to create table as part of test.");
361        let table_name = Some("test_errors_when_column_doesnt_exist".to_string());
362        let span = Span::test_data();
363        let mut columns = Record::new();
364        columns.insert(
365            "not_a_column".to_string(),
366            Value::test_string("ThisIsALongString".to_string()),
367        );
368
369        let result = process(&EngineState::new(), table_name, span, &db, columns);
370
371        assert!(result.is_err());
372    }
373
374    #[test]
375    fn test_errors_when_table_doesnt_exist() {
376        let db = Box::new(SQLiteDatabase::new(
377            std::path::Path::new(MEMORY_DB),
378            Signals::empty(),
379        ));
380
381        let table_name = Some("test_errors_when_table_doesnt_exist".to_string());
382        let span = Span::test_data();
383        let mut columns = Record::new();
384        columns.insert(
385            "str_column".to_string(),
386            Value::test_string("ThisIsALongString".to_string()),
387        );
388
389        let result = process(&EngineState::new(), table_name, span, &db, columns);
390
391        assert!(result.is_err());
392    }
393
394    #[test]
395    fn test_insert_json() {
396        let db = Box::new(SQLiteDatabase::new(
397            std::path::Path::new(MEMORY_DB),
398            Signals::empty(),
399        ));
400
401        let create_stmt = "CREATE TABLE test_insert_json (
402            json_field JSON,
403            jsonb_field JSONB 
404        )";
405
406        let conn = db
407            .open_connection()
408            .expect("Test was unable to open connection.");
409        conn.execute(create_stmt, [])
410            .expect("Failed to create table as part of test.");
411
412        let mut record = Record::new();
413        record.insert("x", Value::test_int(89));
414        record.insert("y", Value::test_int(12));
415        record.insert(
416            "z",
417            Value::test_list(vec![
418                Value::test_string("hello"),
419                Value::test_string("goodbye"),
420            ]),
421        );
422
423        let mut row = Record::new();
424        row.insert("json_field", Value::test_record(record.clone()));
425        row.insert("jsonb_field", Value::test_record(record));
426
427        let result = process(
428            &EngineState::new(),
429            Some("test_insert_json".to_owned()),
430            Span::test_data(),
431            &db,
432            row,
433        );
434
435        assert!(result.is_ok());
436    }
437}
nu_command/stor/insert.rs

nu_command/stor/
insert.rs
