perspective_client/virtual_server/
generic_sql_model.rs

1// ┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
2// ┃ ██████ ██████ ██████       █      █      █      █      █ █▄  ▀███ █       ┃
3// ┃ ▄▄▄▄▄█ █▄▄▄▄▄ ▄▄▄▄▄█  ▀▀▀▀▀█▀▀▀▀▀ █ ▀▀▀▀▀█ ████████▌▐███ ███▄  ▀█ █ ▀▀▀▀▀ ┃
4// ┃ █▀▀▀▀▀ █▀▀▀▀▀ █▀██▀▀ ▄▄▄▄▄ █ ▄▄▄▄▄█ ▄▄▄▄▄█ ████████▌▐███ █████▄   █ ▄▄▄▄▄ ┃
5// ┃ █      ██████ █  ▀█▄       █ ██████      █      ███▌▐███ ███████▄ █       ┃
6// ┣━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┫
7// ┃ Copyright (c) 2017, the Perspective Authors.                              ┃
8// ┃ ╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌╌ ┃
9// ┃ This file is part of the Perspective library, distributed under the terms ┃
10// ┃ of the [Apache License 2.0](https://www.apache.org/licenses/LICENSE-2.0). ┃
11// ┗━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┛
12
13//! SQL query builder for virtual server operations.
14//!
15//! This module provides a stateless SQL query generator that produces
16//! generic SQL strings for perspective virtual server operations.
17
18// TODO(texodus): Missing these features
19//
20// - `on_update` real-time support will be method which takes sa view name and a
21//   handler and calls the handler when the view needs to be recalculated.
22//
23// Nice to have:
24//
25// - Optional `view_change` method can be implemented for engine optimization,
26//   defaulting to just delete & recreate (as Perspective engine does now).
27//
28// - Would like to add a metadata API so that e.g. Viewer debug panel could show
29//   internal generated SQL.
30
31mod table_make_view;
32
33#[cfg(test)]
34mod tests;
35
36use std::fmt;
37
38use indexmap::IndexMap;
39use serde::Deserialize;
40
41use crate::config::{
42    FilterTerm, GroupRollupMode, Scalar, Sort, SortDir, SplitRollupMode, ViewConfig,
43};
44use crate::proto::{ColumnType, ViewPort};
45use crate::virtual_server::generic_sql_model::table_make_view::{
46    ViewQueryContext, entity_ref, quote_ident,
47};
48
49/// Error type for SQL generation operations.
50#[derive(Debug, Clone)]
51pub enum GenericSQLError {
52    /// A required column was not found in the schema.
53    ColumnNotFound(String),
54    /// An invalid configuration was provided.
55    InvalidConfig(String),
56    /// An unsupported operation was requested.
57    UnsupportedOperation(String),
58}
59
60fn sql_type(ty: ColumnType) -> &'static str {
61    match ty {
62        ColumnType::String => "VARCHAR",
63        ColumnType::Integer => "INTEGER",
64        ColumnType::Float => "DOUBLE PRECISION",
65        ColumnType::Boolean => "BOOLEAN",
66        ColumnType::Date => "DATE",
67        ColumnType::Datetime => "TIMESTAMP",
68    }
69}
70
71impl fmt::Display for GenericSQLError {
72    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
73        match self {
74            Self::ColumnNotFound(col) => write!(f, "Column not found: {}", col),
75            Self::InvalidConfig(msg) => write!(f, "Invalid configuration: {}", msg),
76            Self::UnsupportedOperation(msg) => write!(f, "Unsupported operation: {}", msg),
77        }
78    }
79}
80
81impl std::error::Error for GenericSQLError {}
82
83/// Result type alias for SQL operations.
84pub type GenericSQLResult<T> = Result<T, GenericSQLError>;
85
86#[derive(Clone, Debug, Deserialize, Default)]
87pub struct GenericSQLVirtualServerModelArgs {
88    create_entity: Option<String>,
89
90    /// Entity keyword for `view_delete`'s `DROP {drop_entity} IF EXISTS`.
91    /// Must agree with `create_entity` — Postgres rejects `DROP TABLE` on a
92    /// view (`"VIEW"`), while DuckDB's temp tables take the default
93    /// (`"TABLE"`).
94    drop_entity: Option<String>,
95
96    grouping_fn: Option<String>,
97
98    /// Expression for the dialect's natural row identity, used for unsorted
99    /// view order and natural-order window frames — `rowid` (DuckDB, the
100    /// default) or `ctid` (Postgres). Dialects with no such pseudo-column
101    /// (ClickHouse) should advertise `unordered` instead.
102    row_id_expr: Option<String>,
103
104    /// Separator joining `split_by` values and the column name in pivoted
105    /// view column names, e.g. `"CA|Sales"` for separator `"|"`. Perspective's
106    /// column-path separator is `"|"`, so any other value produces views the
107    /// client will not interpret as column paths.
108    column_separator: Option<String>,
109
110    /// Escape character emitted as an `ESCAPE` clause after generated
111    /// `ILIKE` patterns (Perspective's `begins with` / `contains` /
112    /// `ends with` filter ops and their negations). Dialects with no
113    /// default `LIKE` escape character (DuckDB) must pass `"\\"`; dialects
114    /// where backslash escaping is implicit and the `ESCAPE` clause is
115    /// unsupported (ClickHouse) must omit it.
116    like_escape_clause: Option<String>,
117
118    /// Whether the dialect's string literal parser consumes C-style
119    /// backslash escapes (ClickHouse), requiring backslashes in emitted
120    /// literals to be doubled. Dialects with standard-conforming literals
121    /// (DuckDB) omit it.
122    backslash_escaped_literals: Option<bool>,
123
124    /// Name of the dialect's partial-match regex function, emitted as
125    /// `{regex_fn}("col", 'pattern')` for the `matches` / `not matches`
126    /// filter ops — `"regexp_matches"` for DuckDB, `"match"` for
127    /// ClickHouse (both RE2, matching the engine's semantics). When
128    /// omitted, regex filter clauses are dropped.
129    regex_fn: Option<String>,
130
131    /// Template wrapping a `SELECT` so the dialect PLANS it and reports its
132    /// result columns without executing it; `{}` is the query.
133    describe_template: Option<String>,
134
135    /// Whether views carry a `__HIDDEN__` column, `UPDATE`d in place by
136    /// [`GenericSQLVirtualServerModel::view_expand`] and friends, which
137    /// requires `create_entity` be a table whose scan order is its insertion
138    /// order (DuckDB's default).
139    expand_collapse: Option<bool>,
140}
141
142/// Recovers the source column of a pivoted view column name — the longest
143/// `config.columns` entry that is a strict suffix of `name` — with its index
144/// in `config.columns`. Requires no separator knowledge, so it works at
145/// protocol boundaries where the SQL model's `column_separator` is unknown.
146/// Returns `None` for non-path names (e.g. flat-view columns, which equal a
147/// `config.columns` entry exactly rather than strictly containing one).
148pub(crate) fn column_path_source<'a>(
149    name: &str,
150    config: &'a ViewConfig,
151) -> Option<(usize, &'a str)> {
152    if config.split_by.is_empty() {
153        return None;
154    }
155
156    let rollup = config.split_rollup_mode == SplitRollupMode::Rollup;
157
158    let mut best: Option<(usize, &'a str)> = None;
159    for (idx, col) in config.columns.iter().flatten().enumerate() {
160        if (name.len() > col.len() || rollup)
161            && name.ends_with(col.as_str())
162            && best.is_none_or(|(_, b)| col.len() > b.len())
163        {
164            best = Some((idx, col));
165        }
166    }
167
168    best
169}
170
171/// Sorts pivoted view column names into Perspective's column-path order:
172/// `split_by` value paths ascending, then `config.columns` order within each
173/// path (e.g. `CA|price, CA|qty, NY|price, NY|qty`).
174///
175/// The per-column `PIVOT` join in [`ViewQueryContext`] emits columns grouped
176/// by source column instead, so every egress of view column names re-sorts
177/// with this. Internal `__`-prefixed columns sort first, unmatched names
178/// last, both preserving relative order.
179pub(crate) fn sort_column_paths<T: AsRef<str>>(names: &mut [T], config: &ViewConfig) {
180    names.sort_by_cached_key(|name| {
181        let name = name.as_ref();
182        if name.starts_with("__") {
183            return (0u8, String::new(), 0usize);
184        }
185
186        match column_path_source(name, config) {
187            Some((idx, col)) => (1, name[..name.len() - col.len()].to_string(), idx),
188            None => (2, String::new(), 0),
189        }
190    });
191}
192
193/// A stateless SQL query builder virtual server operations.
194///
195/// This struct generates SQL query strings without executing them, allowing
196/// the caller to execute the queries against a SQL connection.
197#[derive(Debug, Default, Clone)]
198pub struct GenericSQLVirtualServerModel(GenericSQLVirtualServerModelArgs);
199
200impl GenericSQLVirtualServerModel {
201    /// Creates a new `GenericSQLVirtualServerModel` instance.
202    pub fn new(args: GenericSQLVirtualServerModelArgs) -> Self {
203        Self(args)
204    }
205
206    fn is_expand_collapse(&self) -> bool {
207        self.0.expand_collapse.unwrap_or(false)
208    }
209
210    /// The `WHERE` clause excluding collapsed rows from a view's reads.
211    fn visible_rows_sql(&self) -> &'static str {
212        if self.is_expand_collapse() {
213            " WHERE NOT \"__HIDDEN__\""
214        } else {
215            ""
216        }
217    }
218
219    /// A row's `group_by` path length, derived from its `__GROUPING_ID__`.
220    fn row_depth_sql(&self, config: &ViewConfig, alias: &str) -> String {
221        let levels = config.group_by.len();
222        let whens = (0..=levels)
223            .map(|k| format!("WHEN {} THEN {}", (1u64 << k) - 1, levels - k))
224            .collect::<Vec<_>>()
225            .join(" ");
226
227        format!("(CASE {}\"__GROUPING_ID__\" {} END)", alias, whens)
228    }
229
230    fn check_expandable(&self, config: &ViewConfig) -> GenericSQLResult<()> {
231        if !self.is_expand_collapse()
232            || config.group_by.is_empty()
233            || config.group_rollup_mode != GroupRollupMode::Rollup
234        {
235            return Err(GenericSQLError::UnsupportedOperation(
236                "expand/collapse requires `expand_collapse` and a \"rollup\" `group_by` view"
237                    .to_string(),
238            ));
239        }
240
241        Ok(())
242    }
243
244    /// A one-row relation `r` of the `row_index`-th visible row's `lo` row id,
245    /// `depth`, and the row id `hi` just past its subtree.
246    fn expansion_target_sql(
247        &self,
248        view_id: &str,
249        config: &ViewConfig,
250        row_index: u32,
251    ) -> GenericSQLResult<String> {
252        self.check_expandable(config)?;
253        let row_id = self.0.row_id_expr.as_deref().unwrap_or("rowid");
254        let view_id = entity_ref(view_id);
255        Ok(format!(
256            "(SELECT t.idx AS lo, t.depth AS depth, COALESCE(MIN(n.{row_id}), (SELECT \
257             MAX({row_id}) + 1 FROM {view_id})) AS hi FROM (SELECT {row_id} AS idx, {depth} AS \
258             depth FROM {view_id} WHERE NOT \"__HIDDEN__\" ORDER BY {row_id} LIMIT 1 OFFSET \
259             {row_index}) AS t LEFT JOIN {view_id} AS n ON n.{row_id} > t.idx AND {n_depth} <= \
260             t.depth GROUP BY t.idx, t.depth) AS r",
261            depth = self.row_depth_sql(config, ""),
262            n_depth = self.row_depth_sql(config, "n."),
263        ))
264    }
265
266    /// Returns the `UPDATE` showing the children of the `row_index`-th visible
267    /// row, each collapsed, whose affected-row count is the rows shown.
268    ///
269    /// # Returns
270    /// SQL: `UPDATE {view_id} SET "__HIDDEN__" = FALSE FROM (...) AS r WHERE
271    /// ...`
272    pub fn view_expand(
273        &self,
274        view_id: &str,
275        config: &ViewConfig,
276        row_index: u32,
277    ) -> GenericSQLResult<String> {
278        let row_id = self.0.row_id_expr.as_deref().unwrap_or("rowid");
279        Ok(format!(
280            "UPDATE {view_ref} AS v SET \"__HIDDEN__\" = FALSE FROM {target} WHERE v.{row_id} > \
281             r.lo AND v.{row_id} < r.hi AND {depth} = r.depth + 1 AND v.\"__HIDDEN__\"",
282            view_ref = entity_ref(view_id),
283            target = self.expansion_target_sql(view_id, config, row_index)?,
284            depth = self.row_depth_sql(config, "v."),
285        ))
286    }
287
288    /// Returns the `UPDATE` hiding every descendant of the `row_index`-th
289    /// visible row, whose affected-row count is the rows hidden.
290    ///
291    /// # Returns
292    /// SQL: `UPDATE {view_id} SET "__HIDDEN__" = TRUE FROM (...) AS r WHERE
293    /// ...`
294    pub fn view_collapse(
295        &self,
296        view_id: &str,
297        config: &ViewConfig,
298        row_index: u32,
299    ) -> GenericSQLResult<String> {
300        let row_id = self.0.row_id_expr.as_deref().unwrap_or("rowid");
301        Ok(format!(
302            "UPDATE {view_ref} AS v SET \"__HIDDEN__\" = TRUE FROM {target} WHERE v.{row_id} > \
303             r.lo AND v.{row_id} < r.hi AND NOT v.\"__HIDDEN__\"",
304            view_ref = entity_ref(view_id),
305            target = self.expansion_target_sql(view_id, config, row_index)?,
306        ))
307    }
308
309    /// Returns the `UPDATE` expanding exactly the rows whose path is no longer
310    /// than `depth`.
311    ///
312    /// # Returns
313    /// SQL: `UPDATE {view_id} SET "__HIDDEN__" = ({depth expr} > {depth + 1})`
314    pub fn view_set_depth(
315        &self,
316        view_id: &str,
317        config: &ViewConfig,
318        depth: u32,
319    ) -> GenericSQLResult<String> {
320        self.check_expandable(config)?;
321        Ok(format!(
322            "UPDATE {} SET \"__HIDDEN__\" = ({} > {})",
323            entity_ref(view_id),
324            self.row_depth_sql(config, ""),
325            depth as u64 + 1
326        ))
327    }
328
329    /// Returns the SQL query to list all hosted tables.
330    ///
331    /// # Returns
332    /// SQL: `SHOW ALL TABLES`
333    pub fn get_hosted_tables(&self) -> GenericSQLResult<String> {
334        Ok("SHOW ALL TABLES".to_string())
335    }
336
337    /// Returns the SQL query to describe a table's schema.
338    ///
339    /// # Arguments
340    /// * `table_id` - The identifier of the table to describe.
341    ///
342    /// # Returns
343    /// SQL: `DESCRIBE {table_id}`
344    pub fn table_schema(&self, table_id: &str) -> GenericSQLResult<String> {
345        Ok(format!("DESCRIBE {}", entity_ref(table_id)))
346    }
347
348    /// Returns the SQL query to get the row count of a table.
349    ///
350    /// # Arguments
351    /// * `table_id` - The identifier of the table.
352    ///
353    /// # Returns
354    /// SQL: `SELECT COUNT(*) FROM {table_id}`
355    pub fn table_size(&self, table_id: &str) -> GenericSQLResult<String> {
356        Ok(format!("SELECT COUNT(*) FROM {}", entity_ref(table_id)))
357    }
358
359    /// Returns the SQL query to get the column count of a view.
360    ///
361    /// # Arguments
362    /// * `view_id` - The identifier of the view.
363    ///
364    /// # Returns
365    /// SQL: `SELECT COUNT(*) FROM (DESCRIBE {view_id})`
366    pub fn view_column_size(&self, view_id: &str) -> GenericSQLResult<String> {
367        Ok(format!(
368            "SELECT COUNT(*) FROM (DESCRIBE {})",
369            entity_ref(view_id)
370        ))
371    }
372
373    fn describe_wrap(&self, query: &str) -> String {
374        self.0
375            .describe_template
376            .as_deref()
377            .unwrap_or("DESCRIBE ({})")
378            .replacen("{}", query, 1)
379    }
380
381    /// Returns the SQL query that plans every expression of `config` at once
382    /// against `table_id`.
383    ///
384    /// # Returns
385    /// SQL: `DESCRIBE (SELECT {expr} AS "{alias}", ... FROM {table_id})`
386    pub fn expressions_describe(
387        &self,
388        table_id: &str,
389        config: &ViewConfig,
390    ) -> GenericSQLResult<Option<String>> {
391        if config.expressions.is_empty() {
392            return Ok(None);
393        }
394
395        let mut names = config.expressions.keys().collect::<Vec<_>>();
396        names.sort();
397        let selects = names
398            .iter()
399            .map(|name| {
400                format!(
401                    "{} AS \"{}\"",
402                    config.expressions.0[*name],
403                    quote_ident(name)
404                )
405            })
406            .collect::<Vec<_>>()
407            .join(", ");
408
409        Ok(Some(self.describe_wrap(&format!(
410            "SELECT {} FROM {}",
411            selects,
412            entity_ref(table_id)
413        ))))
414    }
415
416    /// Returns the SQL query that plans one expression against a table.
417    ///
418    /// # Returns
419    /// SQL: `DESCRIBE (SELECT {expression} FROM {table_id})`
420    pub fn expression_describe(
421        &self,
422        table_id: &str,
423        expression: &str,
424    ) -> GenericSQLResult<String> {
425        Ok(self.describe_wrap(&format!(
426            "SELECT {} FROM {}",
427            expression,
428            entity_ref(table_id)
429        )))
430    }
431
432    /// Returns the SQL query that plans the view `config` would build against
433    /// `table_id`, without materializing it.
434    ///
435    /// # Returns
436    /// SQL: `DESCRIBE (SELECT ... GROUP BY ...)`
437    pub fn table_describe(
438        &self,
439        table_id: &str,
440        config: &ViewConfig,
441        schema: &IndexMap<String, ColumnType>,
442    ) -> GenericSQLResult<Option<String>> {
443        if config.columns.iter().flatten().next().is_none() {
444            return Ok(None);
445        }
446
447        let mut folded = config.clone();
448        folded.sort.clear();
449        let split_by = std::mem::take(&mut folded.split_by);
450        if !split_by.is_empty() && folded.group_rollup_mode != GroupRollupMode::Total {
451            if folded.group_by.is_empty() {
452                folded.aggregates.clear();
453            }
454
455            folded.group_by.extend(split_by);
456        }
457
458        let ctx = ViewQueryContext::new(self, table_id, &folded, schema)?;
459        Ok(Some(self.describe_wrap(&ctx.build_query())))
460    }
461
462    /// Returns the SQL query to delete a view.
463    ///
464    /// # Arguments
465    /// * `view_id` - The identifier of the view to delete.
466    ///
467    /// # Returns
468    /// SQL: `DROP {drop_entity} IF EXISTS {view_id}`
469    pub fn view_delete(&self, view_id: &str) -> GenericSQLResult<String> {
470        let entity = self.0.drop_entity.as_deref().unwrap_or("TABLE");
471        Ok(format!("DROP {} IF EXISTS {}", entity, entity_ref(view_id)))
472    }
473
474    /// Returns the SQL query to create a view from a table with the given
475    /// configuration.
476    ///
477    /// # Arguments
478    /// * `table_id` - The identifier of the source table.
479    /// * `view_id` - The identifier for the new view.
480    /// * `config` - The view configuration specifying columns, group_by,
481    ///   split_by, etc.
482    /// * `schema` - The schema of the source table (column names to types).
483    ///
484    /// # Returns
485    /// SQL: `CREATE TABLE {view_id} AS (...)`
486    pub fn table_make_view(
487        &self,
488        table_id: &str,
489        view_id: &str,
490        config: &ViewConfig,
491        schema: &IndexMap<String, ColumnType>,
492    ) -> GenericSQLResult<String> {
493        let ctx = ViewQueryContext::new(self, table_id, config, schema)?;
494        let mut query = ctx.build_query();
495        if self.is_expand_collapse() {
496            query = format!(
497                "SELECT *, FALSE AS \"__HIDDEN__\" FROM ({}) AS __PSP_EXPAND_SRC__",
498                query
499            );
500        }
501
502        let template = self.0.create_entity.as_deref().unwrap_or("TABLE");
503        Ok(format!(
504            "CREATE {} {} AS ({})",
505            template,
506            entity_ref(view_id),
507            query
508        ))
509    }
510
511    /// Returns the SQL query to create a table from a view, with the view's
512    /// row path unrolled into key columns named after their `group_by`
513    /// columns and aggregates of those columns qualified by their aggregate.
514    ///
515    /// # Arguments
516    /// * `view_id` - The identifier of the source view.
517    /// * `table_id` - The identifier for the new table.
518    /// * `config` - The configuration the view was created with.
519    /// * `view_schema` - The schema of the view's relation.
520    /// * `schema` - The columns of the new table, in place of the view's.
521    ///
522    /// # Returns
523    /// SQL: `CREATE TABLE {table_id} AS (SELECT ... FROM {view_id})`
524    pub fn view_make_table(
525        &self,
526        view_id: &str,
527        table_id: &str,
528        config: &ViewConfig,
529        view_schema: &IndexMap<String, ColumnType>,
530        schema: Option<&IndexMap<String, ColumnType>>,
531    ) -> GenericSQLResult<String> {
532        let key_types: IndexMap<&str, ColumnType> = config
533            .group_by
534            .iter()
535            .enumerate()
536            .filter_map(|(i, col)| {
537                view_schema
538                    .get(&format!("__ROW_PATH_{}__", i))
539                    .map(|ty| (col.as_str(), *ty))
540            })
541            .collect();
542
543        let mut selectable: IndexMap<String, (String, Option<ColumnType>)> = config
544            .group_by
545            .iter()
546            .enumerate()
547            .map(|(i, col)| (col.clone(), (format!("\"__ROW_PATH_{}__\"", i), None)))
548            .collect();
549
550        for (name, ty) in view_schema
551            .iter()
552            .filter(|(name, _)| !name.starts_with("__"))
553        {
554            let readable = config.readable_column_path(name, |leaf| {
555                key_types.get(leaf).copied().unwrap_or(ColumnType::String)
556            });
557
558            selectable.insert(readable, (format!("\"{}\"", name), Some(*ty)));
559        }
560
561        let select = |readable: &str, expr: &str| {
562            if expr == format!("\"{}\"", readable) {
563                expr.to_owned()
564            } else {
565                format!("{} AS \"{}\"", expr, readable)
566            }
567        };
568
569        let clauses: Vec<String> = match schema {
570            None => selectable
571                .iter()
572                .map(|(readable, (expr, _))| select(readable, expr))
573                .collect(),
574            Some(schema) => schema
575                .iter()
576                .map(|(name, ty)| match selectable.get(name) {
577                    Some((_, Some(actual))) if actual != ty => {
578                        Err(GenericSQLError::InvalidConfig(format!(
579                            "Column \"{}\" does not have the type of the View's column",
580                            name
581                        )))
582                    },
583                    Some((expr, _)) => Ok(select(name, expr)),
584                    None => Ok(format!("CAST(NULL AS {}) AS \"{}\"", sql_type(*ty), name)),
585                })
586                .collect::<GenericSQLResult<Vec<_>>>()?,
587        };
588
589        let template = self.0.create_entity.as_deref().unwrap_or("TABLE");
590        Ok(format!(
591            "CREATE {} {} AS (SELECT {} FROM {})",
592            template,
593            entity_ref(table_id),
594            clauses.join(", "),
595            entity_ref(view_id)
596        ))
597    }
598
599    /// Returns the SQL query to fetch data from a view with the given viewport.
600    ///
601    /// # Arguments
602    /// * `view_id` - The identifier of the view.
603    /// * `config` - The view configuration.
604    /// * `viewport` - The viewport specifying row/column ranges.
605    /// * `schema` - The schema of the view (column names to types).
606    ///
607    /// # Returns
608    /// SQL: `SELECT ... FROM {view_id} LIMIT ... OFFSET ...`
609    pub fn view_get_data(
610        &self,
611        view_id: &str,
612        config: &ViewConfig,
613        viewport: &ViewPort,
614        schema: &IndexMap<String, ColumnType>,
615    ) -> GenericSQLResult<String> {
616        let group_by = &config.group_by;
617        let sort = &config.sort;
618        let start_col = viewport.start_col.unwrap_or(0) as usize;
619        let end_col = viewport.end_col.map(|x| x as usize);
620        let start_row = viewport.start_row.unwrap_or(0);
621        let end_row = viewport.end_row;
622        let limit_clause = if let Some(end) = end_row {
623            format!("LIMIT {} OFFSET {}", end - start_row, start_row)
624        } else {
625            String::new()
626        };
627
628        let mut data_columns: Vec<&String> = schema
629            .keys()
630            .filter(|col_name| !col_name.starts_with("__"))
631            .collect();
632
633        let col_sort_dir = sort.iter().find_map(|Sort(_, dir)| match dir {
634            SortDir::ColAsc | SortDir::ColAscAbs => Some(true),
635            SortDir::ColDesc | SortDir::ColDescAbs => Some(false),
636            _ => None,
637        });
638
639        if let Some(ascending) = col_sort_dir {
640            if ascending {
641                data_columns.sort();
642            } else {
643                data_columns.sort_by(|a, b| b.cmp(a));
644            }
645        } else if !config.split_by.is_empty() {
646            sort_column_paths(&mut data_columns, config);
647        }
648
649        let data_columns: Vec<&String> = data_columns
650            .into_iter()
651            .skip(start_col)
652            .take(end_col.map(|e| e - start_col).unwrap_or(usize::MAX))
653            .collect();
654
655        let mut group_by_cols: Vec<String> = Vec::new();
656        if !group_by.is_empty() {
657            if config.group_rollup_mode != GroupRollupMode::Flat {
658                group_by_cols.push("\"__GROUPING_ID__\"".to_string());
659            }
660            for idx in 0..group_by.len() {
661                group_by_cols.push(format!("\"__ROW_PATH_{}__\"", idx));
662            }
663        }
664
665        let all_columns: Vec<String> = group_by_cols
666            .into_iter()
667            .chain(
668                data_columns
669                    .iter()
670                    .map(|col| format!("\"{}\"", quote_ident(col))),
671            )
672            .collect();
673
674        Ok(format!(
675            "SELECT {} FROM {}{} {}",
676            all_columns.join(", "),
677            entity_ref(view_id),
678            self.visible_rows_sql(),
679            limit_clause
680        )
681        .trim()
682        .to_string())
683    }
684
685    /// Returns the SQL query to describe a view's schema.
686    ///
687    /// # Arguments
688    /// * `view_id` - The identifier of the view.
689    ///
690    /// # Returns
691    /// SQL: `DESCRIBE {view_id}`
692    pub fn view_schema(&self, view_id: &str) -> GenericSQLResult<String> {
693        Ok(format!("DESCRIBE {}", entity_ref(view_id)))
694    }
695
696    /// Returns the SQL query to get the (visible) row count of a view.
697    ///
698    /// # Arguments
699    /// * `view_id` - The identifier of the view.
700    ///
701    /// # Returns
702    /// SQL: `SELECT COUNT(*) FROM {view_id}`
703    pub fn view_size(&self, view_id: &str) -> GenericSQLResult<String> {
704        Ok(format!(
705            "SELECT COUNT(*) FROM {}{}",
706            entity_ref(view_id),
707            self.visible_rows_sql()
708        ))
709    }
710
711    /// Returns the SQL query to get the min and max values of a column.
712    ///
713    /// # Arguments
714    /// * `view_id` - The identifier of the view.
715    /// * `column_name` - The name of the column.
716    /// * `config` - The view configuration.
717    ///
718    /// # Returns
719    /// SQL: `SELECT MIN("column_name"), MAX("column_name") FROM {view_id}`
720    /// When the view uses ROLLUP grouping (non-flat mode with group_by),
721    /// a `WHERE __GROUPING_ID__ = 0` clause is added to exclude non-leaf rows.
722    pub fn view_get_min_max(
723        &self,
724        view_id: &str,
725        column_name: &str,
726        config: &ViewConfig,
727    ) -> GenericSQLResult<String> {
728        let has_grouping_id =
729            !config.group_by.is_empty() && config.group_rollup_mode != GroupRollupMode::Flat;
730        let where_clause = if has_grouping_id {
731            " WHERE \"__GROUPING_ID__\" = 0"
732        } else {
733            ""
734        };
735
736        let column_name = quote_ident(column_name);
737        Ok(format!(
738            "SELECT MIN(\"{}\"), MAX(\"{}\") FROM {}{}",
739            column_name,
740            column_name,
741            entity_ref(view_id),
742            where_clause
743        ))
744    }
745
746    fn filter_term_to_sql(term: &FilterTerm, backslash_escaped: bool) -> Option<String> {
747        match term {
748            FilterTerm::Scalar(scalar) => Self::scalar_to_sql(scalar, backslash_escaped),
749            FilterTerm::Array(scalars) => {
750                let values: Vec<String> = scalars
751                    .iter()
752                    .filter_map(|x| Self::scalar_to_sql(x, backslash_escaped))
753                    .collect();
754                if values.is_empty() {
755                    None
756                } else {
757                    Some(format!("({})", values.join(", ")))
758                }
759            },
760        }
761    }
762
763    fn scalar_to_sql(scalar: &Scalar, backslash_escaped: bool) -> Option<String> {
764        match scalar {
765            Scalar::Null => None,
766            Scalar::Bool(b) => Some(if *b { "TRUE" } else { "FALSE" }.to_string()),
767            Scalar::Float(f) => Some(f.to_string()),
768            Scalar::String(s) => Some(table_make_view::string_literal(s, backslash_escaped)),
769        }
770    }
771}
perspective_client/virtual_server/generic_sql_model.rs

perspective_client/virtual_server/
generic_sql_model.rs
