exarrow_rs/query/
export.rs

1//! Export query builder for generating EXPORT SQL statements.
2//!
3//! This module provides the `ExportQuery` builder for constructing Exasol EXPORT statements
4//! that export data to an HTTP endpoint via CSV format.
5//!
6//! # Example
7//!
8
9/// Row separator options for CSV export.
10#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
11pub enum RowSeparator {
12    /// Line feed (Unix-style)
13    #[default]
14    LF,
15    /// Carriage return (old Mac-style)
16    CR,
17    /// Carriage return + line feed (Windows-style)
18    CRLF,
19}
20
21impl RowSeparator {
22    /// Get the SQL representation of the row separator.
23    pub fn as_sql(&self) -> &'static str {
24        match self {
25            RowSeparator::LF => "LF",
26            RowSeparator::CR => "CR",
27            RowSeparator::CRLF => "CRLF",
28        }
29    }
30}
31
32/// Compression options for export files.
33#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
34pub enum Compression {
35    /// No compression
36    #[default]
37    None,
38    /// Gzip compression (.gz extension)
39    Gzip,
40    /// Bzip2 compression (.bz2 extension)
41    Bzip2,
42}
43
44impl Compression {
45    /// Get the file extension for the compression type.
46    pub fn extension(&self) -> &'static str {
47        match self {
48            Compression::None => "",
49            Compression::Gzip => ".gz",
50            Compression::Bzip2 => ".bz2",
51        }
52    }
53}
54
55/// Delimit mode for CSV export.
56#[derive(Debug, Clone, Copy, PartialEq, Eq, Default)]
57pub enum DelimitMode {
58    /// Automatically detect when delimiters are needed
59    #[default]
60    Auto,
61    /// Always use delimiters
62    Always,
63    /// Never use delimiters
64    Never,
65}
66
67impl DelimitMode {
68    /// Get the SQL representation of the delimit mode.
69    pub fn as_sql(&self) -> &'static str {
70        match self {
71            DelimitMode::Auto => "AUTO",
72            DelimitMode::Always => "ALWAYS",
73            DelimitMode::Never => "NEVER",
74        }
75    }
76}
77
78/// Source for the export: either a table or a query.
79#[derive(Debug, Clone)]
80pub enum ExportSource {
81    /// Export from a table.
82    Table {
83        /// Schema name (optional).
84        schema: Option<String>,
85        /// Table name.
86        name: String,
87        /// Columns to export (optional, empty means all columns).
88        columns: Vec<String>,
89    },
90    /// Export from a query.
91    Query {
92        /// SQL query to export results from.
93        sql: String,
94    },
95}
96
97/// Builder for constructing EXPORT SQL statements.
98///
99/// Exasol EXPORT statements transfer data from tables or queries to external destinations
100/// via HTTP transport using CSV format.
101#[derive(Debug, Clone)]
102pub struct ExportQuery {
103    /// Source of the export (table or query).
104    source: ExportSource,
105    /// HTTP address for the export destination.
106    address: String,
107    /// Public key fingerprint for TLS (optional).
108    public_key: Option<String>,
109    /// File name for the export.
110    file_name: String,
111    /// Column separator character.
112    column_separator: char,
113    /// Column delimiter character.
114    column_delimiter: char,
115    /// Row separator.
116    row_separator: RowSeparator,
117    /// Character encoding.
118    encoding: String,
119    /// Custom NULL representation (optional).
120    null_value: Option<String>,
121    /// Delimit mode.
122    delimit_mode: DelimitMode,
123    /// Compression type.
124    compression: Compression,
125    /// Whether to include column names in the output.
126    with_column_names: bool,
127}
128
129impl ExportQuery {
130    /// Create an export query from a table.
131    ///
132    /// # Arguments
133    ///
134    /// * `table` - The name of the table to export from.
135    ///
136    /// # Example
137    ///
138    pub fn from_table(table: &str) -> Self {
139        Self {
140            source: ExportSource::Table {
141                schema: None,
142                name: table.to_string(),
143                columns: Vec::new(),
144            },
145            address: String::new(),
146            public_key: None,
147            file_name: "001.csv".to_string(),
148            column_separator: ',',
149            column_delimiter: '"',
150            row_separator: RowSeparator::default(),
151            encoding: "UTF-8".to_string(),
152            null_value: None,
153            delimit_mode: DelimitMode::default(),
154            compression: Compression::default(),
155            with_column_names: false,
156        }
157    }
158
159    /// Create an export query from a SQL query.
160    ///
161    /// # Arguments
162    ///
163    /// * `sql` - The SQL query whose results to export.
164    ///
165    /// # Example
166    ///
167    pub fn from_query(sql: &str) -> Self {
168        Self {
169            source: ExportSource::Query {
170                sql: sql.to_string(),
171            },
172            address: String::new(),
173            public_key: None,
174            file_name: "001.csv".to_string(),
175            column_separator: ',',
176            column_delimiter: '"',
177            row_separator: RowSeparator::default(),
178            encoding: "UTF-8".to_string(),
179            null_value: None,
180            delimit_mode: DelimitMode::default(),
181            compression: Compression::default(),
182            with_column_names: false,
183        }
184    }
185
186    /// Set the schema for a table export.
187    ///
188    /// This method only has an effect when exporting from a table, not from a query.
189    ///
190    /// # Arguments
191    ///
192    /// * `schema` - The schema name.
193    pub fn schema(mut self, schema: &str) -> Self {
194        if let ExportSource::Table {
195            schema: ref mut s, ..
196        } = self.source
197        {
198            *s = Some(schema.to_string());
199        }
200        self
201    }
202
203    /// Set the columns to export from a table.
204    ///
205    /// This method only has an effect when exporting from a table, not from a query.
206    ///
207    /// # Arguments
208    ///
209    /// * `cols` - The column names to export.
210    pub fn columns(mut self, cols: Vec<&str>) -> Self {
211        if let ExportSource::Table {
212            columns: ref mut c, ..
213        } = self.source
214        {
215            *c = cols.into_iter().map(String::from).collect();
216        }
217        self
218    }
219
220    /// Set the HTTP address for the export destination.
221    ///
222    /// The protocol (http:// or https://) will be determined automatically
223    /// based on whether a public key is set.
224    ///
225    /// # Arguments
226    ///
227    /// * `addr` - The address in the format "host:port".
228    pub fn at_address(mut self, addr: &str) -> Self {
229        self.address = addr.to_string();
230        self
231    }
232
233    /// Set the public key fingerprint for TLS encryption.
234    ///
235    /// When set, the export will use HTTPS and include a PUBLIC KEY clause.
236    ///
237    /// # Arguments
238    ///
239    /// * `fingerprint` - The SHA-256 fingerprint of the public key.
240    pub fn with_public_key(mut self, fingerprint: &str) -> Self {
241        self.public_key = Some(fingerprint.to_string());
242        self
243    }
244
245    /// Set the output file name.
246    ///
247    /// Default is "001.csv". The compression extension will be appended automatically
248    /// if compression is enabled.
249    ///
250    /// # Arguments
251    ///
252    /// * `name` - The file name.
253    pub fn file_name(mut self, name: &str) -> Self {
254        self.file_name = name.to_string();
255        self
256    }
257
258    /// Set the column separator character.
259    ///
260    /// Default is ','.
261    ///
262    /// # Arguments
263    ///
264    /// * `sep` - The separator character.
265    pub fn column_separator(mut self, sep: char) -> Self {
266        self.column_separator = sep;
267        self
268    }
269
270    /// Set the column delimiter character.
271    ///
272    /// Default is '"'.
273    ///
274    /// # Arguments
275    ///
276    /// * `delim` - The delimiter character.
277    pub fn column_delimiter(mut self, delim: char) -> Self {
278        self.column_delimiter = delim;
279        self
280    }
281
282    /// Set the row separator.
283    ///
284    /// Default is LF (Unix-style line endings).
285    ///
286    /// # Arguments
287    ///
288    /// * `sep` - The row separator.
289    pub fn row_separator(mut self, sep: RowSeparator) -> Self {
290        self.row_separator = sep;
291        self
292    }
293
294    /// Set the character encoding.
295    ///
296    /// Default is "UTF-8".
297    ///
298    /// # Arguments
299    ///
300    /// * `enc` - The encoding name.
301    pub fn encoding(mut self, enc: &str) -> Self {
302        self.encoding = enc.to_string();
303        self
304    }
305
306    /// Set a custom NULL value representation.
307    ///
308    /// By default, NULL values are exported as empty strings.
309    ///
310    /// # Arguments
311    ///
312    /// * `val` - The string to use for NULL values.
313    pub fn null_value(mut self, val: &str) -> Self {
314        self.null_value = Some(val.to_string());
315        self
316    }
317
318    /// Set the delimit mode.
319    ///
320    /// Default is Auto.
321    ///
322    /// # Arguments
323    ///
324    /// * `mode` - The delimit mode.
325    pub fn delimit_mode(mut self, mode: DelimitMode) -> Self {
326        self.delimit_mode = mode;
327        self
328    }
329
330    /// Set the compression type.
331    ///
332    /// The appropriate file extension will be added automatically.
333    ///
334    /// # Arguments
335    ///
336    /// * `compression` - The compression type.
337    pub fn compressed(mut self, compression: Compression) -> Self {
338        self.compression = compression;
339        self
340    }
341
342    /// Set whether to include column names in the output.
343    ///
344    /// Default is false.
345    ///
346    /// # Arguments
347    ///
348    /// * `include` - Whether to include column names.
349    pub fn with_column_names(mut self, include: bool) -> Self {
350        self.with_column_names = include;
351        self
352    }
353
354    /// Build the EXPORT SQL statement.
355    ///
356    /// # Returns
357    ///
358    /// The complete EXPORT SQL statement as a string.
359    pub fn build(&self) -> String {
360        let mut sql = String::new();
361
362        // Build the source part
363        match &self.source {
364            ExportSource::Table {
365                schema,
366                name,
367                columns,
368            } => {
369                sql.push_str("EXPORT ");
370                if let Some(s) = schema {
371                    sql.push_str(s);
372                    sql.push('.');
373                }
374                sql.push_str(name);
375                if !columns.is_empty() {
376                    sql.push_str(" (");
377                    sql.push_str(&columns.join(", "));
378                    sql.push(')');
379                }
380            }
381            ExportSource::Query { sql: query } => {
382                sql.push_str("EXPORT (");
383                sql.push_str(query);
384                sql.push(')');
385            }
386        }
387
388        // Build the destination part
389        sql.push_str("\nINTO CSV AT '");
390
391        // Determine protocol based on public key
392        if self.public_key.is_some() {
393            sql.push_str("https://");
394        } else {
395            sql.push_str("http://");
396        }
397        sql.push_str(&self.address);
398        sql.push('\'');
399
400        // Add PUBLIC KEY clause if set
401        if let Some(ref fingerprint) = self.public_key {
402            sql.push_str(" PUBLIC KEY '");
403            sql.push_str(fingerprint);
404            sql.push('\'');
405        }
406
407        // Add file name with compression extension
408        sql.push_str("\nFILE '");
409        sql.push_str(&self.file_name);
410        sql.push_str(self.compression.extension());
411        sql.push('\'');
412
413        // Add format options
414        sql.push_str("\nENCODING = '");
415        sql.push_str(&self.encoding);
416        sql.push('\'');
417
418        sql.push_str("\nCOLUMN SEPARATOR = '");
419        sql.push(self.column_separator);
420        sql.push('\'');
421
422        sql.push_str("\nCOLUMN DELIMITER = '");
423        sql.push(self.column_delimiter);
424        sql.push('\'');
425
426        sql.push_str("\nROW SEPARATOR = '");
427        sql.push_str(self.row_separator.as_sql());
428        sql.push('\'');
429
430        // Add NULL value if set
431        if let Some(ref null_val) = self.null_value {
432            sql.push_str("\nNULL = '");
433            sql.push_str(null_val);
434            sql.push('\'');
435        }
436
437        // Add WITH COLUMN NAMES if enabled
438        if self.with_column_names {
439            sql.push_str("\nWITH COLUMN NAMES");
440        }
441
442        // Add DELIMIT mode
443        sql.push_str("\nDELIMIT = ");
444        sql.push_str(self.delimit_mode.as_sql());
445
446        sql
447    }
448}
449
450#[cfg(test)]
451mod tests {
452    use super::*;
453
454    // Test basic export statement generation from table
455    #[test]
456    fn test_export_from_table_basic() {
457        let sql = ExportQuery::from_table("users")
458            .at_address("192.168.1.100:8080")
459            .build();
460
461        assert!(sql.starts_with("EXPORT users"));
462        assert!(sql.contains("INTO CSV AT 'http://192.168.1.100:8080'"));
463        assert!(sql.contains("FILE '001.csv'"));
464        assert!(sql.contains("ENCODING = 'UTF-8'"));
465        assert!(sql.contains("COLUMN SEPARATOR = ','"));
466        assert!(sql.contains("COLUMN DELIMITER = '\"'"));
467        assert!(sql.contains("ROW SEPARATOR = 'LF'"));
468        assert!(sql.contains("DELIMIT = AUTO"));
469    }
470
471    #[test]
472    fn test_export_from_table_with_schema() {
473        let sql = ExportQuery::from_table("users")
474            .schema("my_schema")
475            .at_address("192.168.1.100:8080")
476            .build();
477
478        assert!(sql.starts_with("EXPORT my_schema.users"));
479    }
480
481    #[test]
482    fn test_export_from_table_with_columns() {
483        let sql = ExportQuery::from_table("users")
484            .columns(vec!["id", "name", "email"])
485            .at_address("192.168.1.100:8080")
486            .build();
487
488        assert!(sql.starts_with("EXPORT users (id, name, email)"));
489    }
490
491    #[test]
492    fn test_export_from_table_with_schema_and_columns() {
493        let sql = ExportQuery::from_table("users")
494            .schema("my_schema")
495            .columns(vec!["id", "name"])
496            .at_address("192.168.1.100:8080")
497            .build();
498
499        assert!(sql.starts_with("EXPORT my_schema.users (id, name)"));
500    }
501
502    // Test export from query
503    #[test]
504    fn test_export_from_query() {
505        let sql = ExportQuery::from_query("SELECT * FROM users WHERE active = true")
506            .at_address("192.168.1.100:8080")
507            .build();
508
509        assert!(sql.starts_with("EXPORT (SELECT * FROM users WHERE active = true)"));
510        assert!(sql.contains("INTO CSV AT 'http://192.168.1.100:8080'"));
511    }
512
513    #[test]
514    fn test_export_from_query_complex() {
515        let sql = ExportQuery::from_query(
516            "SELECT u.id, u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name",
517        )
518        .at_address("192.168.1.100:8080")
519        .build();
520
521        assert!(sql.contains("EXPORT (SELECT u.id, u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name)"));
522    }
523
524    // Test all format options
525    #[test]
526    fn test_export_with_all_format_options() {
527        let sql = ExportQuery::from_table("data")
528            .at_address("192.168.1.100:8080")
529            .column_separator(';')
530            .column_delimiter('\'')
531            .row_separator(RowSeparator::CRLF)
532            .encoding("ISO-8859-1")
533            .null_value("NULL")
534            .delimit_mode(DelimitMode::Always)
535            .build();
536
537        assert!(sql.contains("COLUMN SEPARATOR = ';'"));
538        assert!(sql.contains("COLUMN DELIMITER = '''"));
539        assert!(sql.contains("ROW SEPARATOR = 'CRLF'"));
540        assert!(sql.contains("ENCODING = 'ISO-8859-1'"));
541        assert!(sql.contains("NULL = 'NULL'"));
542        assert!(sql.contains("DELIMIT = ALWAYS"));
543    }
544
545    #[test]
546    fn test_export_row_separator_cr() {
547        let sql = ExportQuery::from_table("data")
548            .at_address("192.168.1.100:8080")
549            .row_separator(RowSeparator::CR)
550            .build();
551
552        assert!(sql.contains("ROW SEPARATOR = 'CR'"));
553    }
554
555    #[test]
556    fn test_export_delimit_mode_never() {
557        let sql = ExportQuery::from_table("data")
558            .at_address("192.168.1.100:8080")
559            .delimit_mode(DelimitMode::Never)
560            .build();
561
562        assert!(sql.contains("DELIMIT = NEVER"));
563    }
564
565    // Test with encryption (PUBLIC KEY)
566    #[test]
567    fn test_export_with_public_key() {
568        let fingerprint = "AB:CD:EF:12:34:56:78:90:AB:CD:EF:12:34:56:78:90";
569        let sql = ExportQuery::from_table("users")
570            .at_address("192.168.1.100:8080")
571            .with_public_key(fingerprint)
572            .build();
573
574        assert!(sql.contains("INTO CSV AT 'https://192.168.1.100:8080'"));
575        assert!(sql.contains(&format!("PUBLIC KEY '{}'", fingerprint)));
576    }
577
578    #[test]
579    fn test_export_without_public_key_uses_http() {
580        let sql = ExportQuery::from_table("users")
581            .at_address("192.168.1.100:8080")
582            .build();
583
584        assert!(sql.contains("INTO CSV AT 'http://192.168.1.100:8080'"));
585        assert!(!sql.contains("PUBLIC KEY"));
586    }
587
588    // Test WITH COLUMN NAMES
589    #[test]
590    fn test_export_with_column_names() {
591        let sql = ExportQuery::from_table("users")
592            .at_address("192.168.1.100:8080")
593            .with_column_names(true)
594            .build();
595
596        assert!(sql.contains("WITH COLUMN NAMES"));
597    }
598
599    #[test]
600    fn test_export_without_column_names() {
601        let sql = ExportQuery::from_table("users")
602            .at_address("192.168.1.100:8080")
603            .with_column_names(false)
604            .build();
605
606        assert!(!sql.contains("WITH COLUMN NAMES"));
607    }
608
609    // Test compression file extensions
610    #[test]
611    fn test_export_no_compression() {
612        let sql = ExportQuery::from_table("users")
613            .at_address("192.168.1.100:8080")
614            .compressed(Compression::None)
615            .build();
616
617        assert!(sql.contains("FILE '001.csv'"));
618        assert!(!sql.contains(".gz"));
619        assert!(!sql.contains(".bz2"));
620    }
621
622    #[test]
623    fn test_export_gzip_compression() {
624        let sql = ExportQuery::from_table("users")
625            .at_address("192.168.1.100:8080")
626            .compressed(Compression::Gzip)
627            .build();
628
629        assert!(sql.contains("FILE '001.csv.gz'"));
630    }
631
632    #[test]
633    fn test_export_bzip2_compression() {
634        let sql = ExportQuery::from_table("users")
635            .at_address("192.168.1.100:8080")
636            .compressed(Compression::Bzip2)
637            .build();
638
639        assert!(sql.contains("FILE '001.csv.bz2'"));
640    }
641
642    #[test]
643    fn test_export_custom_file_name_with_compression() {
644        let sql = ExportQuery::from_table("users")
645            .at_address("192.168.1.100:8080")
646            .file_name("export_data.csv")
647            .compressed(Compression::Gzip)
648            .build();
649
650        assert!(sql.contains("FILE 'export_data.csv.gz'"));
651    }
652
653    // Test complete export statement
654    #[test]
655    fn test_export_complete_statement() {
656        let sql = ExportQuery::from_table("orders")
657            .schema("sales")
658            .columns(vec!["order_id", "customer_id", "total"])
659            .at_address("10.0.0.1:3000")
660            .with_public_key("SHA256:fingerprint123")
661            .file_name("orders_export.csv")
662            .column_separator('|')
663            .column_delimiter('"')
664            .row_separator(RowSeparator::LF)
665            .encoding("UTF-8")
666            .null_value("\\N")
667            .with_column_names(true)
668            .delimit_mode(DelimitMode::Always)
669            .compressed(Compression::Gzip)
670            .build();
671
672        // Verify all parts of the statement
673        assert!(sql.starts_with("EXPORT sales.orders (order_id, customer_id, total)"));
674        assert!(sql.contains("INTO CSV AT 'https://10.0.0.1:3000'"));
675        assert!(sql.contains("PUBLIC KEY 'SHA256:fingerprint123'"));
676        assert!(sql.contains("FILE 'orders_export.csv.gz'"));
677        assert!(sql.contains("ENCODING = 'UTF-8'"));
678        assert!(sql.contains("COLUMN SEPARATOR = '|'"));
679        assert!(sql.contains("COLUMN DELIMITER = '\"'"));
680        assert!(sql.contains("ROW SEPARATOR = 'LF'"));
681        assert!(sql.contains("NULL = '\\N'"));
682        assert!(sql.contains("WITH COLUMN NAMES"));
683        assert!(sql.contains("DELIMIT = ALWAYS"));
684    }
685
686    // Test enum helper methods
687    #[test]
688    fn test_row_separator_as_sql() {
689        assert_eq!(RowSeparator::LF.as_sql(), "LF");
690        assert_eq!(RowSeparator::CR.as_sql(), "CR");
691        assert_eq!(RowSeparator::CRLF.as_sql(), "CRLF");
692    }
693
694    #[test]
695    fn test_compression_extension() {
696        assert_eq!(Compression::None.extension(), "");
697        assert_eq!(Compression::Gzip.extension(), ".gz");
698        assert_eq!(Compression::Bzip2.extension(), ".bz2");
699    }
700
701    #[test]
702    fn test_delimit_mode_as_sql() {
703        assert_eq!(DelimitMode::Auto.as_sql(), "AUTO");
704        assert_eq!(DelimitMode::Always.as_sql(), "ALWAYS");
705        assert_eq!(DelimitMode::Never.as_sql(), "NEVER");
706    }
707
708    // Test default values
709    #[test]
710    fn test_default_values() {
711        assert_eq!(RowSeparator::default(), RowSeparator::LF);
712        assert_eq!(Compression::default(), Compression::None);
713        assert_eq!(DelimitMode::default(), DelimitMode::Auto);
714    }
715
716    // Test that schema/columns methods are no-op for query source
717    #[test]
718    fn test_schema_columns_ignored_for_query() {
719        let sql = ExportQuery::from_query("SELECT 1")
720            .schema("ignored_schema")
721            .columns(vec!["ignored_col"])
722            .at_address("192.168.1.100:8080")
723            .build();
724
725        assert!(sql.starts_with("EXPORT (SELECT 1)"));
726        assert!(!sql.contains("ignored_schema"));
727        assert!(!sql.contains("ignored_col"));
728    }
729}
exarrow_rs/query/export.rs

exarrow_rs/query/
export.rs
