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// - `min_max` API for value-coloring and value-sizing.
21//
22// - row expand/collapse in the datagrid needs datamodel support, this is likely
23//   a "collapsed" boolean column in the temp table we `UPDATE`.
24//
25// - `on_update` real-time support will be method which takes sa view name and a
26//   handler and calls the handler when the view needs to be recalculated.
27//
28// Nice to have:
29//
30// - Optional `view_change` method can be implemented for engine optimization,
31//   defaulting to just delete & recreate (as Perspective engine does now).
32//
33// - Would like to add a metadata API so that e.g. Viewer debug panel could show
34//   internal generated SQL.
35
36mod table_make_view;
37
38#[cfg(test)]
39mod tests;
40
41use std::fmt;
42
43use indexmap::IndexMap;
44use serde::Deserialize;
45
46use crate::config::{FilterTerm, GroupRollupMode, Scalar, Sort, SortDir, ViewConfig};
47use crate::proto::{ColumnType, ViewPort};
48use crate::virtual_server::generic_sql_model::table_make_view::ViewQueryContext;
49
50/// Error type for SQL generation operations.
51#[derive(Debug, Clone)]
52pub enum GenericSQLError {
53    /// A required column was not found in the schema.
54    ColumnNotFound(String),
55    /// An invalid configuration was provided.
56    InvalidConfig(String),
57    /// An unsupported operation was requested.
58    UnsupportedOperation(String),
59}
60
61impl fmt::Display for GenericSQLError {
62    fn fmt(&self, f: &mut fmt::Formatter<'_>) -> fmt::Result {
63        match self {
64            Self::ColumnNotFound(col) => write!(f, "Column not found: {}", col),
65            Self::InvalidConfig(msg) => write!(f, "Invalid configuration: {}", msg),
66            Self::UnsupportedOperation(msg) => write!(f, "Unsupported operation: {}", msg),
67        }
68    }
69}
70
71impl std::error::Error for GenericSQLError {}
72
73/// Result type alias for SQL operations.
74pub type GenericSQLResult<T> = Result<T, GenericSQLError>;
75
76#[derive(Clone, Debug, Deserialize, Default)]
77pub struct GenericSQLVirtualServerModelArgs {
78    create_entity: Option<String>,
79    grouping_fn: Option<String>,
80}
81
82/// A stateless SQL query builder virtual server operations.
83///
84/// This struct generates SQL query strings without executing them, allowing
85/// the caller to execute the queries against a SQL connection.
86#[derive(Debug, Default, Clone)]
87pub struct GenericSQLVirtualServerModel(GenericSQLVirtualServerModelArgs);
88
89impl GenericSQLVirtualServerModel {
90    /// Creates a new `GenericSQLVirtualServerModel` instance.
91    pub fn new(args: GenericSQLVirtualServerModelArgs) -> Self {
92        Self(args)
93    }
94
95    /// Returns the SQL query to list all hosted tables.
96    ///
97    /// # Returns
98    /// SQL: `SHOW ALL TABLES`
99    pub fn get_hosted_tables(&self) -> GenericSQLResult<String> {
100        Ok("SHOW ALL TABLES".to_string())
101    }
102
103    /// Returns the SQL query to describe a table's schema.
104    ///
105    /// # Arguments
106    /// * `table_id` - The identifier of the table to describe.
107    ///
108    /// # Returns
109    /// SQL: `DESCRIBE {table_id}`
110    pub fn table_schema(&self, table_id: &str) -> GenericSQLResult<String> {
111        Ok(format!("DESCRIBE {}", table_id))
112    }
113
114    /// Returns the SQL query to get the row count of a table.
115    ///
116    /// # Arguments
117    /// * `table_id` - The identifier of the table.
118    ///
119    /// # Returns
120    /// SQL: `SELECT COUNT(*) FROM {table_id}`
121    pub fn table_size(&self, table_id: &str) -> GenericSQLResult<String> {
122        Ok(format!("SELECT COUNT(*) FROM {}", table_id))
123    }
124
125    /// Returns the SQL query to get the column count of a view.
126    ///
127    /// # Arguments
128    /// * `view_id` - The identifier of the view.
129    ///
130    /// # Returns
131    /// SQL: `SELECT COUNT(*) FROM (DESCRIBE {view_id})`
132    pub fn view_column_size(&self, view_id: &str) -> GenericSQLResult<String> {
133        Ok(format!("SELECT COUNT(*) FROM (DESCRIBE {})", view_id))
134    }
135
136    /// Returns the SQL query to validate an expression against a table.
137    ///
138    /// # Arguments
139    /// * `table_id` - The identifier of the table.
140    /// * `expression` - The SQL expression to validate.
141    ///
142    /// # Returns
143    /// SQL: `DESCRIBE (SELECT {expression} FROM {table_id})`
144    pub fn table_validate_expression(
145        &self,
146        table_id: &str,
147        expression: &str,
148    ) -> GenericSQLResult<String> {
149        Ok(format!(
150            "DESCRIBE (SELECT {} FROM {})",
151            expression, table_id
152        ))
153    }
154
155    /// Returns the SQL query to delete a view.
156    ///
157    /// # Arguments
158    /// * `view_id` - The identifier of the view to delete.
159    ///
160    /// # Returns
161    /// SQL: `DROP TABLE IF EXISTS {view_id}`
162    pub fn view_delete(&self, view_id: &str) -> GenericSQLResult<String> {
163        Ok(format!("DROP TABLE IF EXISTS {}", view_id))
164    }
165
166    /// Returns the SQL query to create a view from a table with the given
167    /// configuration.
168    ///
169    /// # Arguments
170    /// * `table_id` - The identifier of the source table.
171    /// * `view_id` - The identifier for the new view.
172    /// * `config` - The view configuration specifying columns, group_by,
173    ///   split_by, etc.
174    ///
175    /// # Returns
176    /// SQL: `CREATE TABLE {view_id} AS (...)`
177    pub fn table_make_view(
178        &self,
179        table_id: &str,
180        view_id: &str,
181        config: &ViewConfig,
182    ) -> GenericSQLResult<String> {
183        let ctx = ViewQueryContext::new(self, table_id, config);
184        let query = ctx.build_query();
185        let template = self.0.create_entity.as_deref().unwrap_or("TABLE");
186        Ok(format!("CREATE {} {} AS ({})", template, view_id, query))
187    }
188
189    /// Returns the SQL query to fetch data from a view with the given viewport.
190    ///
191    /// # Arguments
192    /// * `view_id` - The identifier of the view.
193    /// * `config` - The view configuration.
194    /// * `viewport` - The viewport specifying row/column ranges.
195    /// * `schema` - The schema of the view (column names to types).
196    ///
197    /// # Returns
198    /// SQL: `SELECT ... FROM {view_id} LIMIT ... OFFSET ...`
199    pub fn view_get_data(
200        &self,
201        view_id: &str,
202        config: &ViewConfig,
203        viewport: &ViewPort,
204        schema: &IndexMap<String, ColumnType>,
205    ) -> GenericSQLResult<String> {
206        let group_by = &config.group_by;
207        let sort = &config.sort;
208        let start_col = viewport.start_col.unwrap_or(0) as usize;
209        let end_col = viewport.end_col.map(|x| x as usize);
210        let start_row = viewport.start_row.unwrap_or(0);
211        let end_row = viewport.end_row;
212        let limit_clause = if let Some(end) = end_row {
213            format!("LIMIT {} OFFSET {}", end - start_row, start_row)
214        } else {
215            String::new()
216        };
217
218        let mut data_columns: Vec<&String> = schema
219            .keys()
220            .filter(|col_name| !col_name.starts_with("__"))
221            .collect();
222
223        let col_sort_dir = sort.iter().find_map(|Sort(_, dir)| match dir {
224            SortDir::ColAsc | SortDir::ColAscAbs => Some(true),
225            SortDir::ColDesc | SortDir::ColDescAbs => Some(false),
226            _ => None,
227        });
228
229        if let Some(ascending) = col_sort_dir {
230            if ascending {
231                data_columns.sort();
232            } else {
233                data_columns.sort_by(|a, b| b.cmp(a));
234            }
235        }
236
237        let data_columns: Vec<&String> = data_columns
238            .into_iter()
239            .skip(start_col)
240            .take(end_col.map(|e| e - start_col).unwrap_or(usize::MAX))
241            .collect();
242
243        let mut group_by_cols: Vec<String> = Vec::new();
244        if !group_by.is_empty() {
245            if config.group_rollup_mode != GroupRollupMode::Flat {
246                group_by_cols.push("\"__GROUPING_ID__\"".to_string());
247            }
248            for idx in 0..group_by.len() {
249                group_by_cols.push(format!("\"__ROW_PATH_{}__\"", idx));
250            }
251        }
252
253        let all_columns: Vec<String> = group_by_cols
254            .into_iter()
255            .chain(data_columns.iter().map(|col| format!("\"{}\"", col)))
256            .collect();
257
258        Ok(format!(
259            "SELECT {} FROM {} {}",
260            all_columns.join(", "),
261            view_id,
262            limit_clause
263        )
264        .trim()
265        .to_string())
266    }
267
268    /// Returns the SQL query to describe a view's schema.
269    ///
270    /// # Arguments
271    /// * `view_id` - The identifier of the view.
272    ///
273    /// # Returns
274    /// SQL: `DESCRIBE {view_id}`
275    pub fn view_schema(&self, view_id: &str) -> GenericSQLResult<String> {
276        Ok(format!("DESCRIBE {}", view_id))
277    }
278
279    /// Returns the SQL query to get the row count of a view.
280    ///
281    /// # Arguments
282    /// * `view_id` - The identifier of the view.
283    ///
284    /// # Returns
285    /// SQL: `SELECT COUNT(*) FROM {view_id}`
286    pub fn view_size(&self, view_id: &str) -> GenericSQLResult<String> {
287        Ok(format!("SELECT COUNT(*) FROM {}", view_id))
288    }
289
290    fn filter_term_to_sql(term: &FilterTerm) -> Option<String> {
291        match term {
292            FilterTerm::Scalar(scalar) => Self::scalar_to_sql(scalar),
293            FilterTerm::Array(scalars) => {
294                let values: Vec<String> = scalars.iter().filter_map(Self::scalar_to_sql).collect();
295                if values.is_empty() {
296                    None
297                } else {
298                    Some(format!("({})", values.join(", ")))
299                }
300            },
301        }
302    }
303
304    fn scalar_to_sql(scalar: &Scalar) -> Option<String> {
305        match scalar {
306            Scalar::Null => None,
307            Scalar::Bool(b) => Some(if *b { "TRUE" } else { "FALSE" }.to_string()),
308            Scalar::Float(f) => Some(f.to_string()),
309            Scalar::String(s) => Some(format!("'{}'", s.replace('\'', "''"))),
310        }
311    }
312}
perspective_client/virtual_server/generic_sql_model.rs

perspective_client/virtual_server/
generic_sql_model.rs
