drizzle_mysql/builder/insert.rs
1use super::select::{CompletedSelect, IntoSelectQuery};
2use crate::{traits::MySQLTable, values::MySQLValue};
3use drizzle_core::{
4 IncludesRequired, InsertSelectCompatible, InsertSelectTable, InsertTargetColumns,
5 PartialInsertSelectCompatible, ToSQL,
6};
7
8pub use drizzle_core::builder::{InsertColumnsSet, InsertInitial, InsertValuesSet};
9
10/// Builder state after [`ignore`](InsertBuilder::ignore), before the rows
11/// are given.
12#[derive(Debug, Clone, Copy, Default)]
13pub struct InsertIgnoreSet;
14
15/// Builder state after
16/// [`on_duplicate_key_update`](InsertBuilder::on_duplicate_key_update).
17#[derive(Debug, Clone, Copy, Default)]
18pub struct InsertOnDuplicateKeyUpdateSet;
19
20impl drizzle_core::ExecutableState for InsertOnDuplicateKeyUpdateSet {
21 type Rows<R: drizzle_core::row::RowCount> = drizzle_core::row::ZeroOrMore;
22}
23
24/// An `INSERT` query being built for `MySQL`.
25///
26/// This is [`QueryBuilder`](super::QueryBuilder) in one of the `Insert*`
27/// states. Start it with [`QueryBuilder::insert`](super::QueryBuilder::insert).
28///
29/// # Clause order
30///
31/// 1. Optionally [`ignore`](Self::ignore), for `INSERT IGNORE`.
32/// 2. A row source: [`values`](Self::values) or [`value`](Self::value),
33/// [`select`](Self::select), or [`columns`](Self::columns) followed by
34/// `select`.
35/// 3. Optionally [`on_duplicate_key_update`](Self::on_duplicate_key_update).
36///
37/// `MySQL` has no `RETURNING`; the driver reports the affected row count
38/// and last insert id instead. [`prepare`](Self::prepare) is available once
39/// the rows are given.
40///
41/// # Examples
42///
43/// ```rust
44/// # let _ = r####"
45/// # use drizzle::core::expr::{alias, count, eq, gt};
46/// # use drizzle::mysql::{builder::QueryBuilder, prelude::*};
47/// # #[MySQLTable(NAME = "users")]
48/// # struct Users {
49/// # #[column(PRIMARY, AUTO_INCREMENT)] id: u64,
50/// # #[column(VARCHAR(255))] name: String,
51/// # #[column(DEFAULT = true)] active: bool,
52/// # }
53/// # #[MySQLTable(NAME = "posts")]
54/// # struct Posts {
55/// # #[column(PRIMARY, AUTO_INCREMENT)] id: u64,
56/// # #[column(REFERENCES = Users::id)] user_id: u64,
57/// # title: String,
58/// # }
59/// # #[MySQLIndex] struct UsersNameIdx(Users::name);
60/// # #[MySQLIndex] struct PostsUserIdIdx(Posts::user_id);
61/// # #[derive(MySQLSchema)] struct Schema { users: Users, posts: Posts }
62/// # let builder = QueryBuilder::new::<Schema>();
63/// # let Schema { users, posts } = Schema::new();
64/// let query = builder
65/// .insert(users)
66/// .value(InsertUsers::new("Alice"))
67/// .on_duplicate_key_update(UpdateUsers::default().with_name("Alice"));
68/// assert_eq!(
69/// query.to_sql().sql(),
70/// "INSERT INTO `users` (`name`) VALUES (?) ON DUPLICATE KEY UPDATE `name` = ?"
71/// );
72/// # "####;
73/// ```
74pub type InsertBuilder<'a, Schema, State, Table, Marker = (), Row = ()> =
75 super::QueryBuilder<'a, Schema, State, Table, Marker, Row>;
76
77impl<'a, S, T, M, R> InsertBuilder<'a, S, InsertValuesSet, T, M, R> {
78 /// Renders this statement as a [`PreparedStatement`](drizzle_core::prepared::PreparedStatement)
79 /// whose placeholders are bound in `MySQL`'s positional order.
80 #[must_use]
81 pub fn prepare(&self) -> drizzle_core::prepared::PreparedStatement<'a, MySQLValue<'a>> {
82 self.prepared_statement()
83 }
84
85 /// Adds `ON DUPLICATE KEY UPDATE`, which updates the existing row when
86 /// the new one would duplicate any primary or unique key.
87 ///
88 /// `values` is the table's generated update model. `MySQL` picks the
89 /// conflicting key itself, so there is no conflict target.
90 ///
91 /// # Panics
92 ///
93 /// Panics when `values` sets no column.
94 #[track_caller]
95 pub fn on_duplicate_key_update(
96 self,
97 values: T::Update,
98 ) -> InsertBuilder<'a, S, InsertOnDuplicateKeyUpdateSet, T, M, R>
99 where
100 T: MySQLTable<'a>,
101 {
102 let sql = crate::helpers::on_duplicate_key_update::<T>(&values);
103 drop(values);
104 InsertBuilder::from_sql(self.sql.append(sql))
105 }
106}
107
108impl<'a, S, T, M, R> InsertBuilder<'a, S, InsertOnDuplicateKeyUpdateSet, T, M, R> {
109 /// Renders this statement as a [`PreparedStatement`](drizzle_core::prepared::PreparedStatement)
110 /// whose placeholders are bound in `MySQL`'s positional order.
111 #[must_use]
112 pub fn prepare(&self) -> drizzle_core::prepared::PreparedStatement<'a, MySQLValue<'a>> {
113 self.prepared_statement()
114 }
115}
116
117impl<'a, Schema, Table> InsertBuilder<'a, Schema, InsertInitial, Table>
118where
119 Table: MySQLTable<'a>,
120{
121 /// Turns this statement into `INSERT IGNORE`.
122 ///
123 /// `MySQL` then skips rows that duplicate a key, and also turns many other
124 /// errors (such as out-of-range values) into warnings. Use it only when
125 /// that is what you want; for duplicates alone, prefer
126 /// [`on_duplicate_key_update`](Self::on_duplicate_key_update).
127 ///
128 /// # Examples
129 ///
130 /// ```rust
131 /// # let _ = r####"
132 /// # use drizzle::core::expr::{alias, count, eq, gt};
133 /// # use drizzle::mysql::{builder::QueryBuilder, prelude::*};
134 /// # #[MySQLTable(NAME = "users")]
135 /// # struct Users {
136 /// # #[column(PRIMARY, AUTO_INCREMENT)] id: u64,
137 /// # #[column(VARCHAR(255))] name: String,
138 /// # #[column(DEFAULT = true)] active: bool,
139 /// # }
140 /// # #[MySQLTable(NAME = "posts")]
141 /// # struct Posts {
142 /// # #[column(PRIMARY, AUTO_INCREMENT)] id: u64,
143 /// # #[column(REFERENCES = Users::id)] user_id: u64,
144 /// # title: String,
145 /// # }
146 /// # #[MySQLIndex] struct UsersNameIdx(Users::name);
147 /// # #[MySQLIndex] struct PostsUserIdIdx(Posts::user_id);
148 /// # #[derive(MySQLSchema)] struct Schema { users: Users, posts: Posts }
149 /// # let builder = QueryBuilder::new::<Schema>();
150 /// # let Schema { users, posts } = Schema::new();
151 /// let query = builder.insert(users).ignore().value(InsertUsers::new("Alice"));
152 /// assert_eq!(query.to_sql().sql(), "INSERT IGNORE INTO `users` (`name`) VALUES (?)");
153 /// # "####;
154 /// ```
155 #[must_use]
156 pub fn ignore(self) -> InsertBuilder<'a, Schema, InsertIgnoreSet, Table> {
157 InsertBuilder::from_sql(crate::helpers::insert_ignore(self.sql))
158 }
159}
160
161/// Insert states that still accept a VALUES or SELECT row source.
162#[doc(hidden)]
163pub trait InsertRowSourceState {}
164
165impl InsertRowSourceState for InsertInitial {}
166impl InsertRowSourceState for InsertIgnoreSet {}
167
168impl<'a, Schema, State, Table> InsertBuilder<'a, Schema, State, Table>
169where
170 State: InsertRowSourceState,
171 Table: MySQLTable<'a>,
172{
173 /// Lists the target columns for an `INSERT ... SELECT`.
174 ///
175 /// Pass a tuple of the table's columns, then call
176 /// [`select`](InsertBuilder::select). The list must include every
177 /// required column (one without a default), and the SELECT must produce
178 /// matching types in the same order; both are checked at compile time.
179 ///
180 /// # Examples
181 ///
182 /// ```rust
183 /// # let _ = r####"
184 /// # use drizzle::core::expr::{alias, count, eq, gt};
185 /// # use drizzle::mysql::{builder::QueryBuilder, prelude::*};
186 /// # #[MySQLTable(NAME = "users")]
187 /// # struct Users {
188 /// # #[column(PRIMARY, AUTO_INCREMENT)] id: u64,
189 /// # #[column(VARCHAR(255))] name: String,
190 /// # #[column(DEFAULT = true)] active: bool,
191 /// # }
192 /// # #[MySQLTable(NAME = "posts")]
193 /// # struct Posts {
194 /// # #[column(PRIMARY, AUTO_INCREMENT)] id: u64,
195 /// # #[column(REFERENCES = Users::id)] user_id: u64,
196 /// # title: String,
197 /// # }
198 /// # #[MySQLIndex] struct UsersNameIdx(Users::name);
199 /// # #[MySQLIndex] struct PostsUserIdIdx(Posts::user_id);
200 /// # #[derive(MySQLSchema)] struct Schema { users: Users, posts: Posts }
201 /// # let builder = QueryBuilder::new::<Schema>();
202 /// # let Schema { users, posts } = Schema::new();
203 /// let query = builder
204 /// .insert(posts)
205 /// .columns((posts.user_id, posts.title))
206 /// .select(builder.select((users.id, users.name)).from(users));
207 /// assert_eq!(
208 /// query.to_sql().sql(),
209 /// "INSERT INTO `posts` (`user_id`, `title`) SELECT `users`.`id`, `users`.`name` FROM `users`"
210 /// );
211 /// # "####;
212 /// ```
213 ///
214 /// # Panics
215 ///
216 /// Panics when the same target column appears more than once.
217 pub fn columns<Columns>(
218 self,
219 columns: Columns,
220 ) -> InsertBuilder<'a, Schema, InsertColumnsSet<Columns::Columns>, Table>
221 where
222 Columns: InsertTargetColumns<'a, MySQLValue<'a>, Table>,
223 {
224 InsertBuilder::from_sql(self.sql.append(columns.into_target_columns_sql()))
225 }
226
227 /// Inserts one row. Same as `values([value])`.
228 ///
229 /// `value` is the table's generated insert model (for example
230 /// `InsertUsers`).
231 pub fn value<T>(
232 self,
233 value: Table::Insert<T>,
234 ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table> {
235 self.values([value])
236 }
237
238 /// Inserts one or more rows.
239 ///
240 /// Each item is the table's generated insert model, and all rows share
241 /// one model type. A `with_*` setter given `None` leaves its column out
242 /// of that row, so rows can still set different columns; every row then
243 /// lists all of them, with `DEFAULT` for the ones it leaves out. If no
244 /// row sets a column, this renders `() VALUES ()`.
245 ///
246 /// # Examples
247 ///
248 /// ```rust
249 /// # let _ = r####"
250 /// # use drizzle::core::expr::{alias, count, eq, gt};
251 /// # use drizzle::mysql::{builder::QueryBuilder, prelude::*};
252 /// # #[MySQLTable(NAME = "users")]
253 /// # struct Users {
254 /// # #[column(PRIMARY, AUTO_INCREMENT)] id: u64,
255 /// # #[column(VARCHAR(255))] name: String,
256 /// # #[column(DEFAULT = true)] active: bool,
257 /// # }
258 /// # #[MySQLTable(NAME = "posts")]
259 /// # struct Posts {
260 /// # #[column(PRIMARY, AUTO_INCREMENT)] id: u64,
261 /// # #[column(REFERENCES = Users::id)] user_id: u64,
262 /// # title: String,
263 /// # }
264 /// # #[MySQLIndex] struct UsersNameIdx(Users::name);
265 /// # #[MySQLIndex] struct PostsUserIdIdx(Posts::user_id);
266 /// # #[derive(MySQLSchema)] struct Schema { users: Users, posts: Posts }
267 /// # let builder = QueryBuilder::new::<Schema>();
268 /// # let Schema { users, posts } = Schema::new();
269 /// let query = builder
270 /// .insert(users)
271 /// .values([InsertUsers::new("Alice"), InsertUsers::new("Bob")]);
272 /// assert_eq!(query.to_sql().sql(), "INSERT INTO `users` (`name`) VALUES (?), (?)");
273 /// # "####;
274 /// ```
275 ///
276 /// # Panics
277 ///
278 /// Panics when `values` is empty.
279 #[track_caller]
280 pub fn values<I, T>(self, values: I) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>
281 where
282 I: IntoIterator<Item = Table::Insert<T>>,
283 {
284 InsertBuilder::from_sql(
285 self.sql
286 .append(crate::helpers::values::<'a, Table, T>(values)),
287 )
288 }
289
290 /// Inserts the rows of a SELECT into every insertable column of the
291 /// table.
292 ///
293 /// The SELECT must produce one value per insertable column, in table
294 /// order, with compatible types and nullability; its column references
295 /// and aggregates are checked too. To fill only some columns, call
296 /// [`columns`](Self::columns) first. A WITH clause belongs to the inner
297 /// SELECT, so it renders after `INSERT INTO table (...)`.
298 pub fn select<Q, R, ScopeProof, AggProof>(
299 self,
300 query: Q,
301 ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>
302 where
303 Table: InsertSelectTable,
304 Q: IntoSelectQuery<'a, Schema, R>,
305 Q::Marker: InsertSelectCompatible<'a, MySQLValue<'a>, Table, R>
306 + drizzle_core::MarkerScopeValidFor<ScopeProof>
307 + drizzle_core::MarkerAggValidFor<Q::Grouped, AggProof>,
308 {
309 InsertBuilder::from_sql(
310 self.sql
311 .append(Table::insert_columns_sql::<MySQLValue<'a>>())
312 .append(query.into_select_query().into_select_sql()),
313 )
314 }
315
316 /// Appends any SQL as the row source, without a column list.
317 ///
318 /// Nothing is checked: not the column count, types, nullability, column
319 /// scope or aggregates. Prefer [`select`](Self::select).
320 pub fn select_raw<Q>(self, query: Q) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>
321 where
322 Q: ToSQL<'a, MySQLValue<'a>>,
323 {
324 InsertBuilder::from_sql(self.sql.append(query.into_sql()))
325 }
326}
327
328impl<'a, Schema, Table, Targets> InsertBuilder<'a, Schema, InsertColumnsSet<Targets>, Table>
329where
330 Table: MySQLTable<'a>,
331{
332 /// Inserts the rows of a SELECT into the columns chosen with
333 /// [`columns`](InsertBuilder::columns).
334 ///
335 /// The SELECT must produce one value per chosen column, in the same
336 /// order, with compatible types and nullability.
337 pub fn select<Q, R, RequiredProof, ScopeProof, AggProof>(
338 self,
339 query: Q,
340 ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>
341 where
342 Targets: IncludesRequired<Table::RequiredColumns, RequiredProof>,
343 Table: InsertSelectTable,
344 Q: IntoSelectQuery<'a, Schema, R>,
345 Q::Marker: PartialInsertSelectCompatible<'a, MySQLValue<'a>, Targets>
346 + drizzle_core::MarkerScopeValidFor<ScopeProof>
347 + drizzle_core::MarkerAggValidFor<Q::Grouped, AggProof>,
348 {
349 InsertBuilder::from_sql(self.sql.append(query.into_select_query().into_select_sql()))
350 }
351
352 /// Appends any SQL as the row source for the chosen columns.
353 ///
354 /// Only the column list is checked (it must include every required
355 /// column). The SQL itself is not checked.
356 pub fn select_raw<Q, RequiredProof>(
357 self,
358 query: Q,
359 ) -> InsertBuilder<'a, Schema, InsertValuesSet, Table>
360 where
361 Table: InsertSelectTable,
362 Targets: IncludesRequired<Table::RequiredColumns, RequiredProof>,
363 Q: ToSQL<'a, MySQLValue<'a>>,
364 {
365 InsertBuilder::from_sql(self.sql.append(query.into_sql()))
366 }
367}