sea_query/query/on_conflict.rs
1use crate::{ConditionHolder, DynIden, IntoCondition, IntoIden, SimpleExpr};
2
3#[derive(Debug, Clone, Default, PartialEq)]
4pub struct OnConflict {
5 pub(crate) targets: Vec<OnConflictTarget>,
6 pub(crate) target_where: ConditionHolder,
7 pub(crate) action: Option<OnConflictAction>,
8 pub(crate) action_where: ConditionHolder,
9}
10
11/// Represents ON CONFLICT (upsert) targets
12#[derive(Debug, Clone, PartialEq)]
13pub enum OnConflictTarget {
14 /// A column
15 ConflictColumn(DynIden),
16 /// An expression `(LOWER(column), ...)`
17 ConflictExpr(SimpleExpr),
18}
19
20/// Represents ON CONFLICT (upsert) actions
21#[derive(Debug, Clone, PartialEq)]
22pub enum OnConflictAction {
23 /// Do nothing
24 DoNothing(Vec<DynIden>),
25 /// Update column value of existing row
26 Update(Vec<OnConflictUpdate>),
27}
28
29/// Represents strategies to update column in ON CONFLICT (upsert) actions
30#[derive(Debug, Clone, PartialEq)]
31pub enum OnConflictUpdate {
32 /// Update column value of existing row with inserting value
33 Column(DynIden),
34 /// Update column value of existing row with expression
35 Expr(DynIden, SimpleExpr),
36}
37
38impl OnConflict {
39 /// Create a ON CONFLICT expression without target column,
40 /// a special method designed for MySQL
41 pub fn new() -> Self {
42 Default::default()
43 }
44
45 /// Set ON CONFLICT target column
46 pub fn column<C>(column: C) -> Self
47 where
48 C: IntoIden,
49 {
50 Self::columns([column])
51 }
52
53 /// Set ON CONFLICT target columns
54 pub fn columns<I, C>(columns: I) -> Self
55 where
56 C: IntoIden,
57 I: IntoIterator<Item = C>,
58 {
59 Self {
60 targets: columns
61 .into_iter()
62 .map(|c| OnConflictTarget::ConflictColumn(c.into_iden()))
63 .collect(),
64 target_where: ConditionHolder::new(),
65 action: None,
66 action_where: ConditionHolder::new(),
67 }
68 }
69
70 /// Set ON CONFLICT target expression
71 ///
72 /// # Examples
73 ///
74 /// ```
75 /// use sea_query::{tests_cfg::*, *};
76 ///
77 /// let query = Query::insert()
78 /// .into_table(Glyph::Table)
79 /// .columns([Glyph::Aspect, Glyph::Image])
80 /// .values_panic(["abcd".into(), 3.1415.into()])
81 /// .on_conflict(
82 /// OnConflict::new()
83 /// .expr(Expr::col(Glyph::Id))
84 /// .update_column(Glyph::Aspect)
85 /// .value(Glyph::Image, Expr::val(1).add(2))
86 /// .to_owned(),
87 /// )
88 /// .to_owned();
89 ///
90 /// assert_eq!(
91 /// query.to_string(MysqlQueryBuilder),
92 /// [
93 /// r#"INSERT INTO `glyph` (`aspect`, `image`)"#,
94 /// r#"VALUES ('abcd', 3.1415)"#,
95 /// r#"ON DUPLICATE KEY UPDATE `aspect` = VALUES(`aspect`), `image` = 1 + 2"#,
96 /// ]
97 /// .join(" ")
98 /// );
99 /// assert_eq!(
100 /// query.to_string(PostgresQueryBuilder),
101 /// [
102 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
103 /// r#"VALUES ('abcd', 3.1415)"#,
104 /// r#"ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = 1 + 2"#,
105 /// ]
106 /// .join(" ")
107 /// );
108 /// assert_eq!(
109 /// query.to_string(SqliteQueryBuilder),
110 /// [
111 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
112 /// r#"VALUES ('abcd', 3.1415)"#,
113 /// r#"ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = 1 + 2"#,
114 /// ]
115 /// .join(" ")
116 /// );
117 /// ```
118 pub fn expr<T>(&mut self, expr: T) -> &mut Self
119 where
120 T: Into<SimpleExpr>,
121 {
122 Self::exprs(self, [expr])
123 }
124
125 /// Set multiple target expressions for ON CONFLICT. See [`OnConflict::expr`]
126 pub fn exprs<I, T>(&mut self, exprs: I) -> &mut Self
127 where
128 T: Into<SimpleExpr>,
129 I: IntoIterator<Item = T>,
130 {
131 self.targets.append(
132 &mut exprs
133 .into_iter()
134 .map(|e: T| OnConflictTarget::ConflictExpr(e.into()))
135 .collect(),
136 );
137 self
138 }
139
140 /// Set ON CONFLICT do nothing.
141 ///
142 /// Please use [`Self::do_nothing_on()`] and provide primary keys if you are using MySQL.
143 ///
144 /// # Examples
145 ///
146 /// ```
147 /// use sea_query::{tests_cfg::*, *};
148 ///
149 /// let query = Query::insert()
150 /// .into_table(Glyph::Table)
151 /// .columns([Glyph::Aspect, Glyph::Image])
152 /// .values_panic(["abcd".into(), 3.1415.into()])
153 /// .on_conflict(
154 /// OnConflict::columns([Glyph::Id, Glyph::Aspect])
155 /// .do_nothing()
156 /// .to_owned(),
157 /// )
158 /// .to_owned();
159 ///
160 /// // Sadly this is not valid today.
161 /// assert_eq!(
162 /// query.to_string(MysqlQueryBuilder),
163 /// [
164 /// r#"INSERT INTO `glyph` (`aspect`, `image`)"#,
165 /// r#"VALUES ('abcd', 3.1415)"#,
166 /// r#"ON DUPLICATE KEY IGNORE"#,
167 /// ]
168 /// .join(" ")
169 /// );
170 /// assert_eq!(
171 /// query.to_string(PostgresQueryBuilder),
172 /// [
173 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
174 /// r#"VALUES ('abcd', 3.1415)"#,
175 /// r#"ON CONFLICT ("id", "aspect") DO NOTHING"#,
176 /// ]
177 /// .join(" ")
178 /// );
179 /// assert_eq!(
180 /// query.to_string(SqliteQueryBuilder),
181 /// [
182 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
183 /// r#"VALUES ('abcd', 3.1415)"#,
184 /// r#"ON CONFLICT ("id", "aspect") DO NOTHING"#,
185 /// ]
186 /// .join(" ")
187 /// );
188 /// ```
189 pub fn do_nothing(&mut self) -> &mut Self {
190 self.action = Some(OnConflictAction::DoNothing(vec![]));
191 self
192 }
193
194 /// Set ON CONFLICT do nothing, but with MySQL specific polyfill.
195 ///
196 /// # Examples
197 ///
198 /// ```
199 /// use sea_query::{tests_cfg::*, *};
200 ///
201 /// let query = Query::insert()
202 /// .into_table(Glyph::Table)
203 /// .columns([Glyph::Aspect, Glyph::Image])
204 /// .values_panic(["abcd".into(), 3.1415.into()])
205 /// .on_conflict(
206 /// OnConflict::columns([Glyph::Id, Glyph::Aspect])
207 /// .do_nothing_on([Glyph::Id])
208 /// .to_owned(),
209 /// )
210 /// .to_owned();
211 ///
212 /// assert_eq!(
213 /// query.to_string(MysqlQueryBuilder),
214 /// [
215 /// r#"INSERT INTO `glyph` (`aspect`, `image`)"#,
216 /// r#"VALUES ('abcd', 3.1415)"#,
217 /// r#"ON DUPLICATE KEY UPDATE `id` = `id`"#,
218 /// ]
219 /// .join(" ")
220 /// );
221 /// assert_eq!(
222 /// query.to_string(PostgresQueryBuilder),
223 /// [
224 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
225 /// r#"VALUES ('abcd', 3.1415)"#,
226 /// r#"ON CONFLICT ("id", "aspect") DO NOTHING"#,
227 /// ]
228 /// .join(" ")
229 /// );
230 /// assert_eq!(
231 /// query.to_string(SqliteQueryBuilder),
232 /// [
233 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
234 /// r#"VALUES ('abcd', 3.1415)"#,
235 /// r#"ON CONFLICT ("id", "aspect") DO NOTHING"#,
236 /// ]
237 /// .join(" ")
238 /// );
239 /// ```
240 pub fn do_nothing_on<C, I>(&mut self, pk_cols: I) -> &mut Self
241 where
242 C: IntoIden,
243 I: IntoIterator<Item = C>,
244 {
245 self.action = Some(OnConflictAction::DoNothing(
246 pk_cols.into_iter().map(IntoIden::into_iden).collect(),
247 ));
248 self
249 }
250
251 /// Set ON CONFLICT update column
252 ///
253 /// # Examples
254 ///
255 /// ```
256 /// use sea_query::{tests_cfg::*, *};
257 ///
258 /// let query = Query::insert()
259 /// .into_table(Glyph::Table)
260 /// .columns([Glyph::Aspect, Glyph::Image])
261 /// .values_panic([
262 /// "abcd".into(),
263 /// 3.1415.into(),
264 /// ])
265 /// .on_conflict(
266 /// OnConflict::columns([Glyph::Id, Glyph::Aspect])
267 /// .update_column(Glyph::Aspect)
268 /// .value(Glyph::Image, Expr::val(1).add(2))
269 /// .to_owned()
270 /// )
271 /// .to_owned();
272 ///
273 /// assert_eq!(
274 /// query.to_string(MysqlQueryBuilder),
275 /// [
276 /// r#"INSERT INTO `glyph` (`aspect`, `image`)"#,
277 /// r#"VALUES ('abcd', 3.1415)"#,
278 /// r#"ON DUPLICATE KEY UPDATE `aspect` = VALUES(`aspect`), `image` = 1 + 2"#,
279 /// ]
280 /// .join(" ")
281 /// );
282 /// assert_eq!(
283 /// query.to_string(PostgresQueryBuilder),
284 /// [
285 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
286 /// r#"VALUES ('abcd', 3.1415)"#,
287 /// r#"ON CONFLICT ("id", "aspect") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = 1 + 2"#,
288 /// ]
289 /// .join(" ")
290 /// );
291 /// assert_eq!(
292 /// query.to_string(SqliteQueryBuilder),
293 /// [
294 /// r#"INSERT INTO "glyph" ("aspect", "image")"#,
295 /// r#"VALUES ('abcd', 3.1415)"#,
296 /// r#"ON CONFLICT ("id", "aspect") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = 1 + 2"#,
297 /// ]
298 /// .join(" ")
299 /// );
300 /// ```
301 pub fn update_column<C>(&mut self, column: C) -> &mut Self
302 where
303 C: IntoIden,
304 {
305 self.update_columns([column])
306 }
307
308 /// Set ON CONFLICT update columns
309 ///
310 /// # Examples
311 ///
312 /// ```
313 /// use sea_query::{tests_cfg::*, *};
314 ///
315 /// let query = Query::insert()
316 /// .into_table(Glyph::Table)
317 /// .columns([Glyph::Aspect, Glyph::Image])
318 /// .values_panic([
319 /// 2.into(),
320 /// 3.into(),
321 /// ])
322 /// .on_conflict(
323 /// OnConflict::column(Glyph::Id)
324 /// .update_columns([Glyph::Aspect, Glyph::Image])
325 /// .to_owned(),
326 /// )
327 /// .to_owned();
328 ///
329 /// assert_eq!(
330 /// query.to_string(MysqlQueryBuilder),
331 /// r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `aspect` = VALUES(`aspect`), `image` = VALUES(`image`)"#
332 /// );
333 /// assert_eq!(
334 /// query.to_string(PostgresQueryBuilder),
335 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = "excluded"."image""#
336 /// );
337 /// assert_eq!(
338 /// query.to_string(SqliteQueryBuilder),
339 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "aspect" = "excluded"."aspect", "image" = "excluded"."image""#
340 /// );
341 /// ```
342 pub fn update_columns<C, I>(&mut self, columns: I) -> &mut Self
343 where
344 C: IntoIden,
345 I: IntoIterator<Item = C>,
346 {
347 let mut update_strats: Vec<OnConflictUpdate> = columns
348 .into_iter()
349 .map(|x| OnConflictUpdate::Column(IntoIden::into_iden(x)))
350 .collect();
351
352 match &mut self.action {
353 Some(OnConflictAction::Update(v)) => {
354 v.append(&mut update_strats);
355 }
356 Some(OnConflictAction::DoNothing(_)) | None => {
357 self.action = Some(OnConflictAction::Update(update_strats));
358 }
359 };
360 self
361 }
362
363 /// Set ON CONFLICT update exprs
364 ///
365 /// # Examples
366 ///
367 /// ```
368 /// use sea_query::{tests_cfg::*, *};
369 ///
370 /// let query = Query::insert()
371 /// .into_table(Glyph::Table)
372 /// .columns([Glyph::Aspect, Glyph::Image])
373 /// .values_panic([
374 /// 2.into(),
375 /// 3.into(),
376 /// ])
377 /// .on_conflict(
378 /// OnConflict::column(Glyph::Id)
379 /// .value(Glyph::Image, Expr::val(1).add(2))
380 /// .to_owned()
381 /// )
382 /// .to_owned();
383 ///
384 /// assert_eq!(
385 /// query.to_string(MysqlQueryBuilder),
386 /// r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `image` = 1 + 2"#
387 /// );
388 /// assert_eq!(
389 /// query.to_string(PostgresQueryBuilder),
390 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "image" = 1 + 2"#
391 /// );
392 /// assert_eq!(
393 /// query.to_string(SqliteQueryBuilder),
394 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "image" = 1 + 2"#
395 /// );
396 /// ```
397 pub fn values<C, I>(&mut self, values: I) -> &mut Self
398 where
399 C: IntoIden,
400 I: IntoIterator<Item = (C, SimpleExpr)>,
401 {
402 let mut update_exprs: Vec<OnConflictUpdate> = values
403 .into_iter()
404 .map(|(c, e)| OnConflictUpdate::Expr(c.into_iden(), e))
405 .collect();
406
407 match &mut self.action {
408 Some(OnConflictAction::Update(v)) => {
409 v.append(&mut update_exprs);
410 }
411 Some(OnConflictAction::DoNothing(_)) | None => {
412 self.action = Some(OnConflictAction::Update(update_exprs));
413 }
414 };
415 self
416 }
417
418 /// Set ON CONFLICT update value
419 pub fn value<C, T>(&mut self, col: C, value: T) -> &mut Self
420 where
421 C: IntoIden,
422 T: Into<SimpleExpr>,
423 {
424 self.values([(col, value.into())])
425 }
426
427 /// Set target WHERE
428 ///
429 /// # Examples
430 ///
431 /// ```
432 /// use sea_query::{tests_cfg::*, *};
433 ///
434 /// let query = Query::insert()
435 /// .into_table(Glyph::Table)
436 /// .columns([Glyph::Aspect, Glyph::Image])
437 /// .values_panic([
438 /// 2.into(),
439 /// 3.into(),
440 /// ])
441 /// .on_conflict(
442 /// OnConflict::column(Glyph::Id)
443 /// .value(Glyph::Image, Expr::val(1).add(2))
444 /// .target_and_where(Expr::col((Glyph::Table, Glyph::Aspect)).is_null())
445 /// .to_owned()
446 /// )
447 /// .to_owned();
448 ///
449 /// assert_eq!(
450 /// query.to_string(MysqlQueryBuilder),
451 /// r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `image` = 1 + 2"#
452 /// );
453 /// assert_eq!(
454 /// query.to_string(PostgresQueryBuilder),
455 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") WHERE "glyph"."aspect" IS NULL DO UPDATE SET "image" = 1 + 2"#
456 /// );
457 /// assert_eq!(
458 /// query.to_string(SqliteQueryBuilder),
459 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") WHERE "glyph"."aspect" IS NULL DO UPDATE SET "image" = 1 + 2"#
460 /// );
461 /// ```
462 pub fn target_and_where(&mut self, other: SimpleExpr) -> &mut Self {
463 self.target_cond_where(other)
464 }
465
466 /// Set target WHERE
467 pub fn target_and_where_option(&mut self, other: Option<SimpleExpr>) -> &mut Self {
468 if let Some(other) = other {
469 self.target_cond_where(other);
470 }
471 self
472 }
473
474 /// Set target WHERE
475 pub fn target_cond_where<C>(&mut self, condition: C) -> &mut Self
476 where
477 C: IntoCondition,
478 {
479 self.target_where.add_condition(condition.into_condition());
480 self
481 }
482
483 /// Set action WHERE
484 ///
485 /// # Examples
486 ///
487 /// ```
488 /// use sea_query::{tests_cfg::*, *};
489 ///
490 /// let query = Query::insert()
491 /// .into_table(Glyph::Table)
492 /// .columns([Glyph::Aspect, Glyph::Image])
493 /// .values_panic([
494 /// 2.into(),
495 /// 3.into(),
496 /// ])
497 /// .on_conflict(
498 /// OnConflict::column(Glyph::Id)
499 /// .value(Glyph::Image, Expr::val(1).add(2))
500 /// .action_and_where(Expr::col((Glyph::Table, Glyph::Aspect)).is_null())
501 /// .to_owned()
502 /// )
503 /// .to_owned();
504 ///
505 /// assert_eq!(
506 /// query.to_string(MysqlQueryBuilder),
507 /// r#"INSERT INTO `glyph` (`aspect`, `image`) VALUES (2, 3) ON DUPLICATE KEY UPDATE `image` = 1 + 2"#
508 /// );
509 /// assert_eq!(
510 /// query.to_string(PostgresQueryBuilder),
511 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "image" = 1 + 2 WHERE "glyph"."aspect" IS NULL"#
512 /// );
513 /// assert_eq!(
514 /// query.to_string(SqliteQueryBuilder),
515 /// r#"INSERT INTO "glyph" ("aspect", "image") VALUES (2, 3) ON CONFLICT ("id") DO UPDATE SET "image" = 1 + 2 WHERE "glyph"."aspect" IS NULL"#
516 /// );
517 /// ```
518 pub fn action_and_where(&mut self, other: SimpleExpr) -> &mut Self {
519 self.action_cond_where(other)
520 }
521
522 /// Set action WHERE
523 pub fn action_and_where_option(&mut self, other: Option<SimpleExpr>) -> &mut Self {
524 if let Some(other) = other {
525 self.action_cond_where(other);
526 }
527 self
528 }
529
530 /// Set action WHERE
531 pub fn action_cond_where<C>(&mut self, condition: C) -> &mut Self
532 where
533 C: IntoCondition,
534 {
535 self.action_where.add_condition(condition.into_condition());
536 self
537 }
538}