limbo_sqlite3_parser/to_sql_string/stmt/
create_trigger.rs

1use std::fmt::Display;
2
3use crate::{
4    ast::{self, fmt::ToTokens},
5    to_sql_string::ToSqlString,
6};
7
8impl ToSqlString for ast::CreateTrigger {
9    fn to_sql_string<C: crate::to_sql_string::ToSqlContext>(&self, context: &C) -> String {
10        format!(
11            "CREATE{} TRIGGER {}{}{} {} ON {}{}{} BEGIN {} END;",
12            self.temporary.then_some(" TEMP").unwrap_or(""),
13            self.if_not_exists.then_some("IF NOT EXISTS ").unwrap_or(""),
14            self.trigger_name.to_sql_string(context),
15            self.time
16                .map_or("".to_string(), |time| format!(" {}", time)),
17            self.event,
18            self.tbl_name.to_sql_string(context),
19            self.for_each_row.then_some(" FOR EACH ROW").unwrap_or(""),
20            self.when_clause
21                .as_ref()
22                .map_or("".to_string(), |expr| format!(
23                    " WHEN {}",
24                    expr.to_sql_string(context)
25                )),
26            self.commands
27                .iter()
28                .map(|command| format!("{};", command.to_sql_string(context)))
29                .collect::<Vec<_>>()
30                .join(" ")
31        )
32    }
33}
34
35impl Display for ast::TriggerTime {
36    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
37        self.to_fmt(f)
38    }
39}
40
41impl Display for ast::TriggerEvent {
42    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
43        write!(
44            f,
45            "{}",
46            match self {
47                Self::Delete => "DELETE".to_string(),
48                Self::Insert => "INSERT".to_string(),
49                Self::Update => "UPDATE".to_string(),
50                Self::UpdateOf(col_names) => format!(
51                    "UPDATE OF {}",
52                    col_names
53                        .iter()
54                        .map(|name| name.0.clone())
55                        .collect::<Vec<_>>()
56                        .join(", ")
57                ),
58            }
59        )
60    }
61}
62
63impl ToSqlString for ast::TriggerCmd {
64    fn to_sql_string<C: crate::to_sql_string::ToSqlContext>(&self, context: &C) -> String {
65        match self {
66            Self::Delete(delete) => delete.to_sql_string(context),
67            Self::Insert(insert) => insert.to_sql_string(context),
68            Self::Select(select) => select.to_sql_string(context),
69            Self::Update(update) => update.to_sql_string(context),
70        }
71    }
72}
73
74impl ToSqlString for ast::TriggerCmdDelete {
75    fn to_sql_string<C: crate::to_sql_string::ToSqlContext>(&self, context: &C) -> String {
76        // https://sqlite.org/lang_createtrigger.html
77        // TODO: no CTEs and returning clause present in ast for delete
78        // Also for tbl_name it should be a qualified table name with indexed by clauses
79        format!(
80            "DELETE FROM {}{}",
81            self.tbl_name.0,
82            self.where_clause
83                .as_ref()
84                .map_or("".to_string(), |expr| format!(
85                    " WHERE {}",
86                    expr.to_sql_string(context)
87                ))
88        )
89    }
90}
91
92impl ToSqlString for ast::TriggerCmdInsert {
93    fn to_sql_string<C: crate::to_sql_string::ToSqlContext>(&self, context: &C) -> String {
94        // https://sqlite.org/lang_createtrigger.html
95        // FOR TRIGGER SHOULD JUST USE REGULAR INSERT AST
96        // TODO: no ALIAS after table name
97        // TODO: no DEFAULT VALUES
98        format!(
99            "INSERT {}INTO {} {}{}{}{}",
100            self.or_conflict
101                .map_or("".to_string(), |conflict| format!("OR {} ", conflict)),
102            self.tbl_name.0,
103            self.col_names
104                .as_ref()
105                .map_or("".to_string(), |col_names| format!(
106                    "({}) ",
107                    col_names
108                        .iter()
109                        .map(|name| name.0.clone())
110                        .collect::<Vec<_>>()
111                        .join(", ")
112                )),
113            self.select.to_sql_string(context),
114            self.upsert
115                .as_ref()
116                .map_or("".to_string(), |upsert| format!(
117                    " {}",
118                    upsert.to_sql_string(context)
119                )),
120            self.returning
121                .as_ref()
122                .map_or("".to_string(), |returning| format!(
123                    " RETURNING {}",
124                    returning
125                        .iter()
126                        .map(|col| col.to_sql_string(context))
127                        .collect::<Vec<_>>()
128                        .join(", ")
129                ))
130        )
131    }
132}
133
134impl ToSqlString for ast::Upsert {
135    fn to_sql_string<C: crate::to_sql_string::ToSqlContext>(&self, context: &C) -> String {
136        format!(
137            "ON CONFLICT{}{}{}",
138            self.index.as_ref().map_or("".to_string(), |index| format!(
139                "{} ",
140                index.to_sql_string(context)
141            )),
142            self.do_clause.to_sql_string(context),
143            self.next.as_ref().map_or("".to_string(), |next| format!(
144                " {}",
145                next.to_sql_string(context)
146            ))
147        )
148    }
149}
150
151impl ToSqlString for ast::UpsertIndex {
152    fn to_sql_string<C: crate::to_sql_string::ToSqlContext>(&self, context: &C) -> String {
153        format!(
154            "({}){}",
155            self.targets
156                .iter()
157                .map(|target| target.to_sql_string(context))
158                .collect::<Vec<_>>()
159                .join(", "),
160            self.where_clause
161                .as_ref()
162                .map_or("".to_string(), |expr| format!(
163                    " WHERE {}",
164                    expr.to_sql_string(context)
165                ))
166        )
167    }
168}
169
170impl ToSqlString for ast::UpsertDo {
171    fn to_sql_string<C: crate::to_sql_string::ToSqlContext>(&self, context: &C) -> String {
172        match self {
173            Self::Nothing => "DO NOTHING".to_string(),
174            Self::Set { sets, where_clause } => {
175                format!(
176                    "DO UPDATE SET {}{}",
177                    sets.iter()
178                        .map(|set| set.to_sql_string(context))
179                        .collect::<Vec<_>>()
180                        .join(", "),
181                    where_clause.as_ref().map_or("".to_string(), |expr| format!(
182                        " WHERE {}",
183                        expr.to_sql_string(context)
184                    ))
185                )
186            }
187        }
188    }
189}
190
191impl ToSqlString for ast::Set {
192    fn to_sql_string<C: crate::to_sql_string::ToSqlContext>(&self, context: &C) -> String {
193        if self.col_names.len() == 1 {
194            format!(
195                "{} = {}",
196                &self.col_names[0],
197                self.expr.to_sql_string(context)
198            )
199        } else {
200            format!(
201                "({}) = {}",
202                self.col_names
203                    .iter()
204                    .map(|name| name.0.clone())
205                    .collect::<Vec<_>>()
206                    .join(", "),
207                self.expr.to_sql_string(context)
208            )
209        }
210    }
211}
212
213impl ToSqlString for ast::TriggerCmdUpdate {
214    fn to_sql_string<C: crate::to_sql_string::ToSqlContext>(&self, context: &C) -> String {
215        format!(
216            "UPDATE {}{} SET {}{}{}",
217            self.or_conflict
218                .map_or("".to_string(), |conflict| format!("OR {}", conflict)),
219            self.tbl_name.0, // TODO: should be a qualified table name,
220            self.sets
221                .iter()
222                .map(|set| set.to_sql_string(context))
223                .collect::<Vec<_>>()
224                .join(", "),
225            self.from.as_ref().map_or("".to_string(), |from| format!(
226                " {}",
227                from.to_sql_string(context)
228            )),
229            self.where_clause
230                .as_ref()
231                .map_or("".to_string(), |expr| format!(
232                    " WHERE {}",
233                    expr.to_sql_string(context)
234                ))
235        )
236    }
237}
238
239#[cfg(test)]
240mod tests {
241    use crate::to_sql_string_test;
242
243    to_sql_string_test!(
244        test_log_employee_insert,
245        "CREATE TRIGGER log_employee_insert
246         AFTER INSERT ON employees
247         FOR EACH ROW
248         BEGIN
249             INSERT INTO employee_log (action, employee_id, timestamp)
250             VALUES ('INSERT', NEW.id, CURRENT_TIMESTAMP);
251         END;"
252    );
253
254    to_sql_string_test!(
255        test_log_salary_update,
256        "CREATE TRIGGER log_salary_update
257        AFTER UPDATE OF salary ON employees
258        FOR EACH ROW
259        BEGIN
260            INSERT INTO employee_log (action, employee_id, old_value, new_value, timestamp)
261            VALUES ('UPDATE', OLD.id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
262        END;"
263    );
264
265    to_sql_string_test!(
266        test_log_employee_delete,
267        "CREATE TRIGGER log_employee_delete
268         AFTER DELETE ON employees
269         FOR EACH ROW
270         BEGIN
271             INSERT INTO employee_log (action, employee_id, timestamp)
272             VALUES ('DELETE', OLD.id, CURRENT_TIMESTAMP);
273         END;"
274    );
275
276    to_sql_string_test!(
277        test_check_salary_insert,
278        "CREATE TRIGGER check_salary_insert
279         BEFORE INSERT ON employees
280         FOR EACH ROW
281         WHEN NEW.salary < 0
282         BEGIN
283             SELECT RAISE(FAIL, 'Salary cannot be negative');
284         END;"
285    );
286
287    to_sql_string_test!(
288        test_insert_employee_dept,
289        "CREATE TRIGGER insert_employee_dept
290         INSTEAD OF INSERT ON employee_dept
291         FOR EACH ROW
292         BEGIN
293             INSERT INTO departments (name) SELECT NEW.department WHERE NOT EXISTS (SELECT 1 FROM departments WHERE name = NEW.department);
294             INSERT INTO employees (name, department_id) VALUES (NEW.name, (SELECT id FROM departments WHERE name = NEW.department));
295         END;"
296    );
297}
limbo_sqlite3_parser/to_sql_string/stmt/create_trigger.rs

limbo_sqlite3_parser/to_sql_string/stmt/
create_trigger.rs
