Skip to main content

uqa_sql/
copy.rs

1//
2// Unified Query Algebra
3//
4// Copyright (c) 2023-2026 Cognica, Inc.
5//
6
7//! `PostgreSQL` `COPY` statement envelopes and stream codecs.
8//!
9//! `COPY FROM STDIN` and `COPY TO STDOUT` carry their byte stream outside the
10//! SQL statement. The regular [`crate::compile`] entry point therefore cannot
11//! execute one by itself; callers use [`compile_copy`] to validate the SQL
12//! envelope and pair it with the stream through the engine COPY API.
13
14use std::collections::BTreeSet;
15
16use pg_query::NodeEnum;
17
18use crate::SQLError;
19
20mod codec;
21mod output;
22
23pub use codec::{decode_copy_input, encode_copy_result, encode_copy_result_with_engine};
24pub use output::{write_copy_result, CopyEncoder};
25
26/// COPY stream direction.
27#[derive(Debug, Clone, Copy, PartialEq, Eq)]
28pub enum CopyDirection {
29    From,
30    To,
31}
32
33/// COPY data encoding implemented by the embedded stream API.
34#[derive(Debug, Clone, Copy, PartialEq, Eq)]
35pub enum CopyFormat {
36    Text,
37    Csv,
38    Binary,
39}
40
41/// `PostgreSQL`'s three `HEADER` states.
42#[derive(Debug, Clone, Copy, PartialEq, Eq)]
43pub enum CopyHeader {
44    False,
45    True,
46    Match,
47}
48
49/// Parsed COPY options after format-dependent defaults have been applied.
50#[derive(Debug, Clone, PartialEq, Eq)]
51pub struct CopyOptions {
52    pub format: CopyFormat,
53    pub delimiter: u8,
54    pub null: String,
55    pub header: CopyHeader,
56    pub quote: u8,
57    pub escape: u8,
58}
59
60/// The relation or query supplying COPY rows.
61#[derive(Debug, Clone, PartialEq, Eq)]
62pub enum CopyTarget {
63    Relation {
64        name: String,
65        qualifier: String,
66        columns: Vec<String>,
67    },
68    Query(String),
69}
70
71/// Where `PostgreSQL` expects the COPY stream to be connected.
72#[derive(Debug, Clone, PartialEq, Eq)]
73pub enum CopyEndpoint {
74    Stdio,
75    File(String),
76    Program(String),
77}
78
79/// Fully validated COPY statement envelope.
80#[derive(Debug, Clone, PartialEq, Eq)]
81pub struct CopyStatement {
82    pub direction: CopyDirection,
83    pub target: CopyTarget,
84    pub endpoint: CopyEndpoint,
85    pub options: CopyOptions,
86}
87
88/// One decoded COPY field. `None` is SQL NULL; strings remain in `PostgreSQL`'s
89/// textual input representation so the target column's normal input coercion
90/// remains authoritative.
91pub type CopyInputField = Option<String>;
92
93/// Parse exactly one `PostgreSQL` COPY statement.
94pub fn compile_copy(sql: &str) -> Result<CopyStatement, SQLError> {
95    let parsed = crate::parser::parse(sql)?;
96    if parsed.protobuf.stmts.len() != 1 {
97        return Err(SQLError::Routine {
98            sqlstate: "42601".into(),
99            message: "COPY stream API accepts exactly one COPY statement".into(),
100        });
101    }
102    let node = parsed.protobuf.stmts[0]
103        .stmt
104        .as_deref()
105        .and_then(|node| node.node.as_ref())
106        .ok_or_else(|| SQLError::Internal("parser returned an empty COPY statement".into()))?;
107    let NodeEnum::CopyStmt(statement) = node else {
108        return Err(SQLError::Routine {
109            sqlstate: "42601".into(),
110            message: "COPY stream API requires a COPY statement".into(),
111        });
112    };
113    let direction = if statement.is_from {
114        CopyDirection::From
115    } else {
116        CopyDirection::To
117    };
118    if statement.where_clause.is_some() {
119        return Err(SQLError::Unsupported(
120            "COPY FROM WHERE is not implemented".into(),
121        ));
122    }
123    let target = match (&statement.relation, &statement.query) {
124        (Some(relation), None) => {
125            if !relation.catalogname.is_empty() {
126                return Err(SQLError::Unsupported(
127                    "COPY across databases is not supported".into(),
128                ));
129            }
130            let name = if relation.schemaname.is_empty() {
131                quote_relation_component(&relation.relname)
132            } else {
133                format!(
134                    "{}.{}",
135                    quote_relation_component(&relation.schemaname),
136                    quote_relation_component(&relation.relname)
137                )
138            };
139            let columns = statement
140                .attlist
141                .iter()
142                .map(copy_column_name)
143                .collect::<Result<Vec<_>, _>>()?;
144            CopyTarget::Relation {
145                name,
146                qualifier: relation.relname.clone(),
147                columns,
148            }
149        }
150        (None, Some(query)) if direction == CopyDirection::To => CopyTarget::Query(
151            query
152                .deparse()
153                .map_err(|error| SQLError::Parse(error.to_string()))?,
154        ),
155        (None, Some(_)) => {
156            return Err(SQLError::Routine {
157                sqlstate: "42601".into(),
158                message: "COPY FROM does not accept a query".into(),
159            });
160        }
161        _ => {
162            return Err(SQLError::Internal(
163                "COPY statement has neither one relation nor one query".into(),
164            ));
165        }
166    };
167    let endpoint = if statement.is_program {
168        CopyEndpoint::Program(statement.filename.clone())
169    } else if statement.filename.is_empty() {
170        CopyEndpoint::Stdio
171    } else {
172        CopyEndpoint::File(statement.filename.clone())
173    };
174    let options = compile_copy_options(&statement.options)?;
175    if direction == CopyDirection::To && options.header == CopyHeader::Match {
176        return Err(copy_option_error(
177            "COPY HEADER MATCH is only valid for COPY FROM",
178        ));
179    }
180    Ok(CopyStatement {
181        direction,
182        target,
183        endpoint,
184        options,
185    })
186}
187
188fn quote_relation_component(component: &str) -> String {
189    crate::expr::quote_ident(component)
190}
191
192fn copy_column_name(node: &pg_query::Node) -> Result<String, SQLError> {
193    match node.node.as_ref() {
194        Some(NodeEnum::String(value)) => Ok(value.sval.clone()),
195        other => Err(SQLError::Internal(format!(
196            "COPY column list contains malformed node {other:?}"
197        ))),
198    }
199}
200
201fn compile_copy_options(nodes: &[pg_query::Node]) -> Result<CopyOptions, SQLError> {
202    let raw = nodes
203        .iter()
204        .map(|node| {
205            let Some(NodeEnum::DefElem(option)) = node.node.as_ref() else {
206                return Err(SQLError::Internal(
207                    "COPY option list contains a malformed node".into(),
208                ));
209            };
210            Ok((
211                option.defname.to_ascii_lowercase(),
212                copy_option_value(option)?,
213            ))
214        })
215        .collect::<Result<Vec<_>, SQLError>>()?;
216    let mut names = BTreeSet::new();
217    for (name, _) in &raw {
218        if !names.insert(name.clone()) {
219            return Err(SQLError::Routine {
220                sqlstate: "42601".into(),
221                message: format!("COPY option \"{name}\" specified more than once"),
222            });
223        }
224    }
225    let format = raw.iter().find(|(name, _)| name == "format").map_or(
226        Ok(CopyFormat::Text),
227        |(_, value)| match value.to_ascii_lowercase().as_str() {
228            "text" => Ok(CopyFormat::Text),
229            "csv" => Ok(CopyFormat::Csv),
230            "binary" => Ok(CopyFormat::Binary),
231            other => Err(copy_option_error(format!(
232                "COPY format \"{other}\" not recognized"
233            ))),
234        },
235    )?;
236    let mut options = CopyOptions {
237        format,
238        delimiter: if format == CopyFormat::Csv {
239            b','
240        } else {
241            b'\t'
242        },
243        null: if format == CopyFormat::Csv {
244            String::new()
245        } else {
246            "\\N".into()
247        },
248        header: CopyHeader::False,
249        quote: b'"',
250        escape: b'"',
251    };
252    let quote_explicit = names.contains("quote");
253    let escape_explicit = names.contains("escape");
254    for (name, value) in raw {
255        match name.as_str() {
256            "format" => {}
257            "delimiter" => options.delimiter = copy_single_byte_option("DELIMITER", &value)?,
258            "null" => options.null = value,
259            "header" => {
260                options.header = if value.eq_ignore_ascii_case("match") {
261                    CopyHeader::Match
262                } else if copy_boolean_option("HEADER", &value)? {
263                    CopyHeader::True
264                } else {
265                    CopyHeader::False
266                };
267            }
268            "quote" => options.quote = copy_single_byte_option("QUOTE", &value)?,
269            "escape" => options.escape = copy_single_byte_option("ESCAPE", &value)?,
270            "encoding" if matches!(value.to_ascii_lowercase().as_str(), "utf8" | "utf-8") => {}
271            "encoding" => {
272                return Err(SQLError::Unsupported(format!(
273                    "COPY ENCODING {value} is not implemented"
274                )));
275            }
276            other => {
277                return Err(SQLError::Unsupported(format!(
278                    "COPY option `{other}` is not implemented"
279                )));
280            }
281        }
282    }
283    validate_copy_options(&options, quote_explicit, escape_explicit)?;
284    Ok(options)
285}
286
287fn copy_option_value(option: &pg_query::protobuf::DefElem) -> Result<String, SQLError> {
288    match option
289        .arg
290        .as_deref()
291        .and_then(|argument| argument.node.as_ref())
292    {
293        None => Ok("true".into()),
294        Some(NodeEnum::String(value)) => Ok(value.sval.clone()),
295        Some(NodeEnum::Integer(value)) => Ok(value.ival.to_string()),
296        Some(NodeEnum::Float(value)) => Ok(value.fval.clone()),
297        Some(NodeEnum::Boolean(value)) => Ok(value.boolval.to_string()),
298        other => Err(copy_option_error(format!(
299            "COPY option \"{}\" requires a scalar value, got {other:?}",
300            option.defname
301        ))),
302    }
303}
304
305fn copy_boolean_option(name: &str, value: &str) -> Result<bool, SQLError> {
306    match value.to_ascii_lowercase().as_str() {
307        "true" | "on" | "yes" | "1" => Ok(true),
308        "false" | "off" | "no" | "0" => Ok(false),
309        _ => Err(copy_option_error(format!(
310            "COPY {name} requires a Boolean value"
311        ))),
312    }
313}
314
315fn copy_single_byte_option(name: &str, value: &str) -> Result<u8, SQLError> {
316    if value.len() != 1 {
317        return Err(copy_option_error(format!(
318            "COPY {name} must be a single one-byte character"
319        )));
320    }
321    let byte = value.as_bytes()[0];
322    if matches!(byte, 0 | b'\n' | b'\r') {
323        return Err(copy_option_error(format!(
324            "COPY {name} cannot be newline or carriage return"
325        )));
326    }
327    Ok(byte)
328}
329
330fn validate_copy_options(
331    options: &CopyOptions,
332    quote_explicit: bool,
333    escape_explicit: bool,
334) -> Result<(), SQLError> {
335    if options.null.contains(['\n', '\r']) {
336        return Err(copy_option_error(
337            "COPY null representation cannot use newline or carriage return",
338        ));
339    }
340    if options.format == CopyFormat::Text && options.delimiter == b'\\' {
341        return Err(copy_option_error("COPY delimiter cannot be backslash"));
342    }
343    if options.format != CopyFormat::Csv && (quote_explicit || escape_explicit) {
344        return Err(copy_option_error(
345            "COPY quote or escape available only in CSV mode",
346        ));
347    }
348    if options.format == CopyFormat::Binary && options.header != CopyHeader::False {
349        return Err(copy_option_error(
350            "COPY HEADER is not available in binary mode",
351        ));
352    }
353    if options.format == CopyFormat::Csv && options.delimiter == options.quote {
354        return Err(copy_option_error(
355            "COPY delimiter and quote must be different",
356        ));
357    }
358    if options.null.as_bytes().contains(&options.delimiter) {
359        return Err(copy_option_error(
360            "COPY delimiter character must not appear in the NULL specification",
361        ));
362    }
363    if options.format == CopyFormat::Csv && options.null.as_bytes().contains(&options.quote) {
364        return Err(copy_option_error(
365            "CSV quote character must not appear in the NULL specification",
366        ));
367    }
368    Ok(())
369}
370
371fn copy_option_error(message: impl Into<String>) -> SQLError {
372    SQLError::Routine {
373        sqlstate: "22023".into(),
374        message: message.into(),
375    }
376}
377
378#[cfg(test)]
379mod tests {
380    use super::*;
381
382    #[test]
383    fn copy_parser_preserves_relation_query_and_format_contracts() {
384        let from = compile_copy(
385            "COPY app.items (bucket, payload) FROM STDIN WITH (FORMAT csv, HEADER MATCH)",
386        )
387        .unwrap();
388        assert_eq!(from.direction, CopyDirection::From);
389        assert_eq!(from.endpoint, CopyEndpoint::Stdio);
390        assert_eq!(from.options.format, CopyFormat::Csv);
391        assert_eq!(from.options.header, CopyHeader::Match);
392        assert_eq!(
393            from.target,
394            CopyTarget::Relation {
395                name: "app.items".into(),
396                qualifier: "items".into(),
397                columns: vec!["bucket".into(), "payload".into()]
398            }
399        );
400
401        let to = compile_copy("COPY (SELECT * FROM ONLY items ORDER BY id) TO STDOUT").unwrap();
402        assert_eq!(to.direction, CopyDirection::To);
403        let CopyTarget::Query(query) = to.target else {
404            panic!("expected COPY query target");
405        };
406        assert!(query.contains("ONLY items"));
407
408        let quoted =
409            compile_copy("COPY \"copy.schema\".\"Odd.Table\" (\"Odd Column\") FROM STDIN").unwrap();
410        assert_eq!(
411            quoted.target,
412            CopyTarget::Relation {
413                name: "\"copy.schema\".\"Odd.Table\"".into(),
414                qualifier: "Odd.Table".into(),
415                columns: vec!["Odd Column".into()],
416            }
417        );
418
419        for invalid in [
420            r"COPY items FROM STDIN WITH (FORMAT text, DELIMITER E'\\')",
421            "COPY items FROM STDIN WITH (FORMAT text, QUOTE '\"')",
422            "COPY items FROM STDIN WITH (FORMAT binary, ESCAPE '\"')",
423            "COPY items FROM STDIN WITH (FORMAT binary, HEADER)",
424            "COPY items FROM STDIN WITH (DELIMITER '|', NULL 'a|b')",
425            "COPY items FROM STDIN WITH (FORMAT csv, NULL 'a\"b')",
426        ] {
427            assert_eq!(compile_copy(invalid).unwrap_err().sqlstate(), Some("22023"));
428        }
429    }
430
431    #[test]
432    fn text_codec_distinguishes_null_escapes_and_end_marker() {
433        let options = compile_copy("COPY t FROM STDIN").unwrap().options;
434        let columns = vec!["a".into(), "b".into()];
435        let rows = decode_copy_input(
436            b"\\N\tempty\\tvalue\n\\\\N\tline\\nvalue\n\\.\nignored\tx\n",
437            &options,
438            &columns,
439        )
440        .unwrap();
441        assert_eq!(rows.len(), 2);
442        assert_eq!(rows[0][0], None);
443        assert_eq!(rows[0][1].as_deref(), Some("empty\tvalue"));
444        assert_eq!(rows[1][0].as_deref(), Some("\\N"));
445        assert_eq!(rows[1][1].as_deref(), Some("line\nvalue"));
446
447        let custom = compile_copy("COPY t FROM STDIN WITH (DELIMITER '|')")
448            .unwrap()
449            .options;
450        let escaped_delimiter =
451            decode_copy_input(b"left\\|right|tail\n", &custom, &columns).unwrap();
452        assert_eq!(
453            escaped_delimiter[0],
454            vec![Some("left|right".into()), Some("tail".into())]
455        );
456        assert_eq!(
457            decode_copy_input(b"\\000\tvalue\n", &options, &columns)
458                .unwrap_err()
459                .sqlstate(),
460            Some("22021")
461        );
462
463        let with_header = compile_copy("COPY t FROM STDIN WITH (HEADER MATCH)")
464            .unwrap()
465            .options;
466        assert_eq!(
467            decode_copy_input(b"a\tb\n1\t2\n", &with_header, &columns).unwrap(),
468            vec![vec![Some("1".into()), Some("2".into())]]
469        );
470    }
471
472    #[test]
473    fn csv_codec_preserves_quoted_empty_and_embedded_newline() {
474        let options = compile_copy("COPY t FROM STDIN WITH (FORMAT csv)")
475            .unwrap()
476            .options;
477        let columns = vec!["a".into(), "b".into()];
478        let rows =
479            decode_copy_input(b",\"\"\n\"a,b\",\"line\nvalue\"\n", &options, &columns).unwrap();
480        assert_eq!(rows[0], vec![None, Some(String::new())]);
481        assert_eq!(rows[1][0].as_deref(), Some("a,b"));
482        assert_eq!(rows[1][1].as_deref(), Some("line\nvalue"));
483
484        let permissive = decode_copy_input(b"\"a\"b,c\n\"a\" ,b\n", &options, &columns).unwrap();
485        assert_eq!(permissive[0], vec![Some("ab".into()), Some("c".into())]);
486        assert_eq!(permissive[1], vec![Some("a ".into()), Some("b".into())]);
487    }
488}
489
490pub mod stream;