rich_data/
sql.rs

1//! SQL-shaped result sets (#235): a query's rows as a database shell shows
2//! them.
3//!
4//! A [`ResultSet`] is column names, their types (a [`Schema`], the model a
5//! row source carries) and rows, rendered with:
6//!
7//! - **typed alignment**: integers, floats and decimals right-justified,
8//!   booleans centred, text, dates, timestamps and everything else left. A
9//!   column with no type in the schema is right-justified when every
10//!   sampled non-null cell is a number.
11//! - **NULL styling**: a null cell reads `NULL` in the `table.null` style
12//!   (dim italic), so it never looks like an empty string, which stays
13//!   empty.
14//! - **a row count**: `(3 rows)`, `(1 row)`, with the elapsed time when the
15//!   caller gives one: `(3 rows, 12ms)`.
16//!
17//! There is no database connection: rows come from an adapter ([`Rows`],
18//! a [`RowWindow`] of any [`RowSource`]) or from the caller through
19//! [`VirtualRows`]. Rendering goes through `rich_ext`'s
20//! [`VirtualTable`], so a large result shows one window
21//! ([`limit`](ResultSet::limit) rows from [`offset`](ResultSet::offset),
22//! default the first [`DEFAULT_LIMIT`]) with its position above the count.
23//!
24//! ```
25//! use std::time::Duration;
26//!
27//! use rich::Console;
28//! use rich_data::sql::ResultSet;
29//! use rich_data::{DataType, Field, Rows, Schema, Value};
30//!
31//! let schema = Schema::new([
32//!     Field::new("id", DataType::Integer),
33//!     Field::new("name", DataType::String),
34//!     Field::new("active", DataType::Boolean),
35//!     Field::new("balance", DataType::decimal()),
36//! ]);
37//! let mut rows = Rows::new(["id", "name", "active", "balance"]).with_schema(schema);
38//! rows.push([Value::Int(1), "ada".into(), "true".into(), Value::Float(12.5)]);
39//! rows.push([Value::Int(2), "".into(), "false".into(), Value::Null]);
40//! rows.push([Value::Int(10), Value::Null, "true".into(), Value::Float(-3.0)]);
41//!
42//! let result = ResultSet::new(rows).elapsed(Duration::from_millis(12));
43//! let out = Console::builder().width(50).build().render_export(&result);
44//! assert_eq!(
45//!     out,
46//!     "\
47//! ┏━━━━┳━━━━━━┳━━━━━━━━┳━━━━━━━━━┓
48//! ┃ id ┃ name ┃ active ┃ balance ┃
49//! ┡━━━━╇━━━━━━╇━━━━━━━━╇━━━━━━━━━┩
50//! │  1 │ ada  │  true  │    12.5 │
51//! │  2 │      │ false  │    NULL │
52//! │ 10 │ NULL │  true  │      -3 │
53//! └────┴──────┴────────┴─────────┘
54//! (3 rows, 12ms)
55//! "
56//! );
57//! ```
58
59use std::time::Duration;
60
61use rich::{Console, ConsoleOptions, Justify, Renderable, Segment};
62use rich_ext::table::virtualized::{DEFAULT_SAMPLE, MAX_HEIGHT};
63use rich_ext::table::{Column, VirtualRows, VirtualTable};
64
65use crate::window::RowWindow;
66use crate::{DataError, DataType, Row, RowSource, Rows, Schema, Value};
67
68/// Rows a [`ResultSet`] shows by default.
69pub const DEFAULT_LIMIT: usize = 1_000;
70
71/// How a column of `data_type` is justified: numbers right, booleans
72/// centred, everything else left.
73pub fn alignment(data_type: &DataType) -> Justify {
74    match data_type {
75        DataType::Integer | DataType::Float | DataType::Decimal { .. } => Justify::Right,
76        DataType::Boolean => Justify::Center,
77        _ => Justify::Left,
78    }
79}
80
81/// A column per name, justified by its type in `schema` (by name, else by
82/// position), or, without one, right-justified when every non-null cell of
83/// `sample` in that column is a number.
84pub fn typed_columns(columns: &[String], schema: Option<&Schema>, sample: &[Row]) -> Vec<Column> {
85    columns
86        .iter()
87        .enumerate()
88        .map(|(index, name)| {
89            let field = schema.and_then(|s| s.field(name).or_else(|| s.fields().get(index)));
90            let justify = match field.map(|f| f.data_type()) {
91                Some(DataType::Any | DataType::Unknown) | None => {
92                    let mut cells = sample
93                        .iter()
94                        .filter_map(|row| row.get(index))
95                        .filter(|v| !matches!(v, Value::Null))
96                        .peekable();
97                    let numeric = cells.peek().is_some()
98                        && cells.all(|v| matches!(v, Value::Int(_) | Value::Float(_)));
99                    if numeric {
100                        Justify::Right
101                    } else {
102                        Justify::Left
103                    }
104                }
105                Some(data_type) => alignment(data_type),
106            };
107            Column::new(name.clone()).justify(justify)
108        })
109        .collect()
110}
111
112/// A query result: columns, their types and rows; see the
113/// [module docs](self).
114#[derive(Clone, Debug)]
115pub struct ResultSet<S = Rows> {
116    columns: Vec<String>,
117    schema: Option<Schema>,
118    source: S,
119    offset: usize,
120    limit: usize,
121    elapsed: Option<Duration>,
122    null: String,
123    title: Option<String>,
124}
125
126impl ResultSet<Rows> {
127    /// Rows held in memory, typed by their schema.
128    pub fn new(rows: Rows) -> Self {
129        let columns = rows.columns().to_vec();
130        let schema = rows.schema().cloned();
131        ResultSet::from_parts(columns, schema, rows)
132    }
133}
134
135impl ResultSet<RowWindow> {
136    /// One pass over `source`, keeping the `limit` rows from `offset` and
137    /// counting the rest: any length of input in one window of memory.
138    pub fn read(source: impl RowSource, offset: usize, limit: usize) -> Result<Self, DataError> {
139        Ok(ResultSet::window(RowWindow::read(source, offset, limit)?))
140    }
141
142    /// A window already read.
143    pub fn window(window: RowWindow) -> Self {
144        let columns = window.columns().to_vec();
145        let schema = window.schema().cloned();
146        let (offset, limit) = (window.start(), window.rows().len());
147        ResultSet::from_parts(columns, schema, window)
148            .offset(offset)
149            .limit(limit)
150    }
151}
152
153impl<S: VirtualRows> ResultSet<S> {
154    /// Rows from any source under `columns`, typed by `schema` (one field
155    /// per column, matched by name, else by position).
156    pub fn from_parts(
157        columns: impl IntoIterator<Item = impl Into<String>>,
158        schema: Option<Schema>,
159        source: S,
160    ) -> Self {
161        ResultSet {
162            columns: columns.into_iter().map(Into::into).collect(),
163            schema,
164            source,
165            offset: 0,
166            limit: DEFAULT_LIMIT,
167            elapsed: None,
168            null: "NULL".to_string(),
169            title: None,
170        }
171    }
172
173    /// Show rows from `offset` (0-based).
174    #[must_use]
175    pub fn offset(mut self, offset: usize) -> Self {
176        self.offset = offset;
177        self
178    }
179
180    /// Show at most `rows` rows (default [`DEFAULT_LIMIT`], at most
181    /// `MAX_HEIGHT`).
182    #[must_use]
183    pub fn limit(mut self, rows: usize) -> Self {
184        self.limit = rows.min(MAX_HEIGHT);
185        self
186    }
187
188    /// How long the query took, for the footer.
189    #[must_use]
190    pub fn elapsed(mut self, elapsed: Duration) -> Self {
191        self.elapsed = Some(elapsed);
192        self
193    }
194
195    /// The marker for null cells (default `NULL`).
196    #[must_use]
197    pub fn null_marker(mut self, marker: impl Into<String>) -> Self {
198        self.null = marker.into();
199        self
200    }
201
202    /// A title above the table (console markup).
203    #[must_use]
204    pub fn title(mut self, title: impl Into<String>) -> Self {
205        self.title = Some(title.into());
206        self
207    }
208
209    /// The column names.
210    pub fn columns(&self) -> &[String] {
211        &self.columns
212    }
213
214    /// The schema the columns are typed by.
215    pub fn schema(&self) -> Option<&Schema> {
216        self.schema.as_ref()
217    }
218
219    /// The source.
220    pub fn source(&self) -> &S {
221        &self.source
222    }
223
224    /// How many rows the result has, when the source knows.
225    pub fn row_count(&self) -> Option<usize> {
226        self.source.row_count()
227    }
228
229    /// The footer: `(3 rows)`, `(1 row)`, `(1,001+ rows)` when the source
230    /// cannot count past the window, with the elapsed time when given
231    /// (`(3 rows, 12ms)`).
232    pub fn footer(&self, ascii: bool) -> String {
233        let count = match self.row_count() {
234            Some(count) => rows(count, ""),
235            None => {
236                let page = self.table(true).page();
237                match page.total {
238                    Some(total) => rows(total, ""),
239                    None => rows(page.range().end + usize::from(page.more), "+"),
240                }
241            }
242        };
243        match self.elapsed {
244            Some(elapsed) => format!(
245                "({count}, {})",
246                rich_ext::format::duration_with(elapsed, ascii)
247            ),
248            None => format!("({count})"),
249        }
250    }
251
252    /// Whether the table shows only part of the rows.
253    pub fn is_windowed(&self) -> bool {
254        match self.row_count() {
255            Some(count) => (self.offset > 0 && count > 0) || count > self.limit,
256            None => true,
257        }
258    }
259
260    /// The result as a virtualised table (without the footer), typed and
261    /// with nulls marked; change its frame with its builders.
262    pub fn to_virtual_table(&self) -> VirtualTable<&S> {
263        self.table(self.is_windowed())
264    }
265
266    fn table(&self, position: bool) -> VirtualTable<&S> {
267        let sample = self.source.rows(0, DEFAULT_SAMPLE);
268        let mut table = VirtualTable::new(
269            typed_columns(&self.columns, self.schema.as_ref(), &sample),
270            &self.source,
271        )
272        .offset(self.offset)
273        .height(self.limit)
274        .null_marker(self.null.clone())
275        .fit_window(true)
276        .show_position(position);
277        if let Some(title) = &self.title {
278            table = table.title(title.clone());
279        }
280        table
281    }
282}
283
284/// `3 rows`, `1 row`, with a suffix after the number.
285fn rows(count: usize, suffix: &str) -> String {
286    let number = rich_ext::format::number(i64::try_from(count).unwrap_or(i64::MAX));
287    let noun = if count == 1 && suffix.is_empty() {
288        "row"
289    } else {
290        "rows"
291    };
292    format!("{number}{suffix} {noun}")
293}
294
295impl<S: VirtualRows> Renderable for ResultSet<S> {
296    fn rich_render(&self, console: &Console, options: &ConsoleOptions) -> Vec<Segment> {
297        self.to_virtual_table()
298            .footnote(self.footer(console.ascii_only()))
299            .rich_render(console, options)
300    }
301
302    fn measure(&self, console: &Console, options: &ConsoleOptions) -> rich::measure::Measurement {
303        self.to_virtual_table()
304            .footnote(self.footer(console.ascii_only()))
305            .measure(console, options)
306    }
307}
308
309#[cfg(test)]
310mod tests {
311    use rich_ext::table::FnRows;
312
313    use super::*;
314    use crate::Field;
315
316    fn render(result: &impl Renderable, width: usize) -> String {
317        Console::builder()
318            .width(width)
319            .color_system(None)
320            .build()
321            .render_export(result)
322    }
323
324    #[test]
325    fn counts_read_as_words() {
326        let one = ResultSet::new({
327            let mut rows = Rows::new(["x"]);
328            rows.push([Value::Int(1)]);
329            rows
330        });
331        assert_eq!(one.footer(false), "(1 row)");
332        assert_eq!(ResultSet::new(Rows::new(["x"])).footer(false), "(0 rows)");
333        let many = ResultSet::from_parts(["x"], None, FnRows::new(Some(1_234_567), |_| None));
334        assert_eq!(many.footer(false), "(1,234,567 rows)");
335    }
336
337    #[test]
338    fn columns_without_types_align_by_their_cells() {
339        let sample = vec![
340            vec![Value::Int(1), "a".into(), Value::Null, Value::Null],
341            vec![Value::Float(2.5), "b".into(), Value::Null, Value::Int(3)],
342        ];
343        let names: Vec<String> = ["n", "s", "empty", "mixed"].map(String::from).to_vec();
344        let columns = typed_columns(&names, None, &sample);
345        let justify: Vec<Justify> = columns.iter().map(|c| c.column_options().justify).collect();
346        assert_eq!(
347            justify,
348            [Justify::Right, Justify::Left, Justify::Left, Justify::Right]
349        );
350    }
351
352    #[test]
353    fn schema_types_win_over_cells_and_match_by_name() {
354        let schema = Schema::new([
355            Field::new("b", DataType::Integer),
356            Field::new("a", DataType::Date),
357        ]);
358        let sample = vec![vec![Value::Int(1), "x".into()]];
359        let names: Vec<String> = ["a", "b"].map(String::from).to_vec();
360        let columns = typed_columns(&names, Some(&schema), &sample);
361        assert_eq!(columns[0].column_options().justify, Justify::Left);
362        assert_eq!(columns[1].column_options().justify, Justify::Right);
363    }
364
365    #[test]
366    fn a_large_result_shows_its_window_and_count() {
367        let source = FnRows::new(Some(1_000_000), |i| Some(vec![Value::from(i)]));
368        let result = ResultSet::from_parts(["i"], None, source)
369            .offset(10)
370            .limit(2);
371        let out = render(&result, 40);
372        assert!(
373            out.ends_with("│ 10 │\n│ 11 │\n└────┘\nrows 11–12 of 1,000,000\n(1,000,000 rows)\n"),
374            "{out}"
375        );
376    }
377
378    #[test]
379    fn an_uncounted_window_says_at_least() {
380        let mut rows = Rows::new(["n"]);
381        for n in 0..50 {
382            rows.push([Value::Int(n)]);
383        }
384        let window = RowWindow::reader()
385            .len(5)
386            .count(false)
387            .read(rows.into_source())
388            .unwrap();
389        let result = ResultSet::window(window);
390        assert_eq!(result.footer(false), "(6+ rows)");
391        let out = render(&result, 30);
392        assert!(out.ends_with("rows 1–5 of 6+\n(6+ rows)\n"), "{out}");
393    }
394
395    #[test]
396    fn the_measurement_covers_the_footer() {
397        let mut rows = Rows::new(["n"]);
398        rows.push([Value::Int(1)]);
399        let result = ResultSet::new(rows).elapsed(Duration::from_millis(12));
400        let console = Console::builder().width(40).build();
401        let measured = rich::measure::Measurement::get(&console, &console.options(), &result);
402        assert_eq!(measured.maximum, "(1 row, 12ms)".len());
403    }
404
405    #[test]
406    fn a_short_result_has_no_position_line() {
407        let mut rows = Rows::new(["a"]);
408        rows.push([Value::from("")]);
409        let out = render(&ResultSet::new(rows).title("q"), 30);
410        assert!(!out.contains("rows 1"), "{out}");
411        assert!(out.ends_with("(1 row)\n"), "{out}");
412    }
413}
rich_data/sql.rs

rich_data/
sql.rs
