keelson_sqlite/
function.rs

1use std::borrow::Cow;
2
3use keelson_core::clause::{HasOrderBy, OrderBy, Window};
4use keelson_core::expr::{Expr, IntoExpr, IntoExprList};
5use keelson_core::{Expression, Mod, SqlWriter};
6
7/// A SQLite function call, with the decorations SQLite's grammar hangs off one.
8///
9/// From <https://www.sqlite.org/syntax/aggregate-function-invocation.html> and
10/// <https://www.sqlite.org/syntax/window-function-invocation.html>:
11///
12/// ```text
13/// name ( [ DISTINCT ] expr [, ...] [ ORDER BY ordering-term [, ...] ] | * )
14///     [ FILTER ( WHERE expr ) ]
15/// name ( expr [, ...] | * ) [ FILTER ( WHERE expr ) ]
16///     OVER ( window-defn | window-name )
17/// ```
18///
19/// That is the whole list, and it is shorter than PostgreSQL's. SQLite has **no**
20/// `WITHIN GROUP (ORDER BY …)` — an ordered-set aggregate is not a thing it has —
21/// and **no** column-definition list on a table-valued function, so neither
22/// appears here. The aggregate `ORDER BY` inside the argument list needs SQLite
23/// 3.44 or later.
24///
25/// ```
26/// use keelson_sqlite::{f, quote, window};
27///
28/// // count(*) OVER (PARTITION BY "user_id")
29/// let e = f("count", "*").over(window::partition_by(quote("user_id")));
30/// ```
31#[derive(Debug, Clone, Default)]
32pub struct Function {
33    name: Cow<'static, str>,
34    args: Vec<Expr>,
35    distinct: bool,
36    order_by: OrderBy,
37    filter: Vec<Expr>,
38    over: Option<OverClause>,
39}
40
41/// What follows `OVER`.
42///
43/// The two forms are not interchangeable. `OVER window-name` **references** an
44/// entry of the statement's `WINDOW` clause; `OVER ( … )` is a definition, and a
45/// definition that starts with a base window name *copies* it — which SQLite
46/// refuses when the base window has a frame specification. See
47/// [`Function::over_name`].
48#[derive(Debug, Clone)]
49enum OverClause {
50    /// `OVER "w"`.
51    Name(Cow<'static, str>),
52    /// `OVER ( … )`.
53    Definition(Window),
54}
55
56impl Function {
57    /// A call to `name` with `args`.
58    pub fn new(name: impl Into<Cow<'static, str>>, args: impl IntoExprList) -> Function {
59        Function {
60            name: name.into(),
61            args: args.into_expr_list(),
62            ..Function::default()
63        }
64    }
65
66    /// `DISTINCT`, for an aggregate that should see each distinct input once.
67    #[must_use]
68    pub fn distinct(mut self) -> Function {
69        self.distinct = true;
70        self
71    }
72
73    /// Add a sort key to the aggregate's own `ORDER BY`, inside the argument list:
74    /// `group_concat("name" ORDER BY "id")`.
75    ///
76    /// SQLite 3.44 and later.
77    #[must_use]
78    pub fn order_by(mut self, order: impl IntoExpr) -> Function {
79        self.order_by.append_order(order);
80        self
81    }
82
83    /// Add a condition to `FILTER (WHERE …)`. Several are `AND`-joined.
84    #[must_use]
85    pub fn filter(mut self, condition: impl IntoExpr) -> Function {
86        self.filter.push(condition.into_expr());
87        self
88    }
89
90    /// Attach `OVER (…)`, built from [`window`](crate::window) and
91    /// [`frame`](crate::frame) mods.
92    ///
93    /// Ends the builder, because `OVER` is the last thing in the grammar. `over(())`
94    /// gives the legal `OVER ()`, which means the whole partition.
95    ///
96    /// To *reference* a window declared in the statement's `WINDOW` clause use
97    /// [`over_name`](Self::over_name), not `over(window::based_on(..))` — the
98    /// parenthesised form is the copying one.
99    #[must_use]
100    pub fn over(mut self, mods: impl Mod<Window>) -> Expr {
101        let mut w = Window::default();
102        mods.apply(&mut w);
103        self.over = Some(OverClause::Definition(w));
104        self.into_expr()
105    }
106
107    /// Attach `OVER "w"` — a reference to a window in the statement's `WINDOW`
108    /// clause.
109    ///
110    /// Unparenthesised, which is what makes it a reference rather than a copy.
111    #[must_use]
112    pub fn over_name(mut self, name: impl Into<Cow<'static, str>>) -> Expr {
113        self.over = Some(OverClause::Name(name.into()));
114        self.into_expr()
115    }
116
117    /// `f(…) AS "alias"` — the result-column alias.
118    ///
119    /// Ends the builder for the same reason
120    /// [`Chain::as_`](keelson_core::expr::Chain::as_) does: an alias is not an
121    /// operand.
122    #[must_use]
123    pub fn as_(self, alias: impl Into<Cow<'static, str>>) -> Expr {
124        use keelson_core::expr::Chain as _;
125        self.into_expr().as_(alias.into())
126    }
127}
128
129impl HasOrderBy for Function {
130    fn order_by_mut(&mut self) -> &mut OrderBy {
131        &mut self.order_by
132    }
133}
134
135impl Expression for Function {
136    fn write_sql(&self, w: &mut SqlWriter<'_>) {
137        if self.name.is_empty() {
138            // A call with no name is not a fragment of anything.
139            w.record_error(keelson_core::Error::Incomplete("the name of a function"));
140            return;
141        }
142
143        w.push_str(&self.name);
144        w.push_str("(");
145        if self.distinct {
146            w.push_str("DISTINCT ");
147        }
148        w.write_slice(&self.args, "", ", ", "");
149        // `group_concat(x ORDER BY y)`: the separator is only needed when there is
150        // an argument in front of it, and `f(ORDER BY x)` is not a thing.
151        w.write_if(
152            !self.order_by.is_empty() && !self.args.is_empty(),
153            " ",
154            &self.order_by,
155            "",
156        );
157        w.push_str(")");
158
159        w.write_slice(&self.filter, " FILTER (WHERE ", " AND ", ")");
160
161        match &self.over {
162            None => {}
163            Some(OverClause::Name(name)) => {
164                w.push_str(" OVER ");
165                w.push_quoted(&[name]);
166            }
167            Some(OverClause::Definition(window)) => {
168                w.push_str(" OVER (");
169                w.write_expr(window);
170                w.push_str(")");
171            }
172        }
173    }
174}
175
176impl IntoExpr for Function {
177    fn into_expr(self) -> Expr {
178        Expr::custom(self)
179    }
180}
181
182impl IntoExprList for Function {
183    fn into_expr_list(self) -> Vec<Expr> {
184        vec![self.into_expr()]
185    }
186}
187
188#[cfg(test)]
189mod tests {
190    use super::*;
191    use crate::{Sqlite, arg, f, frame, quote, window};
192    use keelson_core::build;
193
194    fn sql(e: impl Expression) -> String {
195        build(&Sqlite, &e).expect("render").0
196    }
197
198    #[test]
199    fn a_plain_call_is_just_a_call() {
200        assert_eq!(sql(f("date", ())), "date()");
201        assert_eq!(sql(f("count", "*")), "count(*)");
202    }
203
204    /// <https://www.sqlite.org/syntax/aggregate-function-invocation.html>:
205    /// `DISTINCT` and the aggregate's `ORDER BY` are both inside the parentheses.
206    #[test]
207    fn distinct_and_order_by_stay_inside_the_argument_list() {
208        assert_eq!(
209            sql(f("count", quote("id")).distinct()),
210            r#"count(DISTINCT "id")"#
211        );
212        assert_eq!(
213            sql(f("group_concat", quote("name")).order_by(quote("id"))),
214            r#"group_concat("name" ORDER BY "id")"#
215        );
216    }
217
218    #[test]
219    fn filter_conditions_are_and_joined_inside_one_where() {
220        assert_eq!(
221            sql(f("count", "*").filter(quote("a")).filter(quote("b"))),
222            r#"count(*) FILTER (WHERE "a" AND "b")"#
223        );
224    }
225
226    /// <https://www.sqlite.org/syntax/window-function-invocation.html>:
227    /// `FILTER` precedes `OVER`.
228    #[test]
229    fn filter_is_written_before_over() {
230        assert_eq!(
231            sql(f("count", "*")
232                .filter(quote("a"))
233                .over(window::partition_by(quote("b")))),
234            r#"count(*) FILTER (WHERE "a") OVER (PARTITION BY "b")"#
235        );
236    }
237
238    #[test]
239    fn over_takes_a_definition_a_name_or_nothing() {
240        assert_eq!(sql(f("row_number", ()).over(())), "row_number() OVER ()");
241        assert_eq!(
242            sql(f("avg", quote("views")).over_name("w")),
243            r#"avg("views") OVER "w""#
244        );
245        // The copying form: legal, and refused by SQLite when "w" has a frame.
246        assert_eq!(
247            sql(f("avg", quote("views")).over(window::based_on("w"))),
248            r#"avg("views") OVER ("w")"#
249        );
250        assert_eq!(
251            sql(f("sum", quote("views")).over((
252                window::partition_by(quote("user_id")),
253                window::order_by(quote("id")),
254                frame::rows(),
255                frame::from_current_row(),
256                frame::to_unbounded_following(),
257            ))),
258            r#"sum("views") OVER (PARTITION BY "user_id" ORDER BY "id" ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)"#
259        );
260    }
261
262    #[test]
263    fn arguments_are_numbered_across_the_whole_call() {
264        let (sql, args) = build(
265            &Sqlite,
266            &f("max", (arg(1i32), arg(2i32))).filter(quote("a")).over(()),
267        )
268        .unwrap();
269        assert_eq!(sql, r#"max(?1, ?2) FILTER (WHERE "a") OVER ()"#);
270        assert_eq!(args.len(), 2);
271    }
272
273    #[test]
274    fn an_unnamed_call_is_a_recorded_failure() {
275        let err = build(&Sqlite, &Function::default()).unwrap_err();
276        // The substring names the SQL concept (a function's name), not the
277        // message wording.
278        assert!(
279            matches!(&err, keelson_core::Error::Incomplete(what) if what.contains("function")),
280            "got: {err}"
281        );
282    }
283}
keelson_sqlite/function.rs

keelson_sqlite/
function.rs
