rbatis/crud.rs
1///PySql: gen insert/insert_batch, select_by_map, update_by_map, delete_by_map methods
2///```rust
3/// use rbs::value;
4/// use rbatis::{Error, RBatis, rbdc::db::ExecResult};
5///
6/// #[derive(serde::Serialize, serde::Deserialize)]
7/// pub struct MockTable{
8/// pub id: Option<String>
9/// }
10/// rbatis::crud!(MockTable{}); //or crud!(MockTable{},"mock_table");
11///
12/// //use
13/// async fn test_use(rb:&RBatis) -> Result<(),Error>{
14/// let table = MockTable{id: Some("1".to_string())};
15/// let result:ExecResult = MockTable::insert(rb, &table).await?;
16/// let result:ExecResult = MockTable::insert_batch(rb, std::slice::from_ref(&table),10).await?;
17///
18/// let tables:Vec<MockTable> = MockTable::select_by_map(rb,value!{"id":"1"}).await?;
19/// let tables:Vec<MockTable> = MockTable::select_by_map(rb,value!{"id":["1","2","3"]}).await?;
20/// let tables:Vec<MockTable> = MockTable::select_by_map(rb,value!{"id":"1", "column": ["id", "name"]}).await?;
21///
22/// let result:ExecResult = MockTable::update_by_map(rb, &table, value!{"id":"1"}).await?;
23/// let result:ExecResult = MockTable::delete_by_map(rb, value!{"id":"1"}).await?;
24/// Ok(())
25/// }
26///
27///
28/// ```
29#[macro_export]
30macro_rules! crud {
31 ($table:ty{}) => {
32 $crate::crud!($table {}, "");
33 };
34 ($table:ty{},$table_name:expr) => {
35 // insert
36 impl $table {
37 /// batch insert records
38 ///
39 /// sql: `INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...), (value1, value2, ...), ...`
40 pub async fn insert_batch(
41 executor: &dyn $crate::executor::Executor,
42 tables: &[$table],
43 batch_size: u64,
44 ) -> std::result::Result<$crate::rbdc::db::ExecResult, $crate::rbdc::Error> {
45 use $crate::crud_traits::ColumnSet;
46 #[$crate::py_sql(
47 "`insert into ${table_name} `
48 trim ',':
49 bind columns = tables.column_sets():
50 for idx,table in tables:
51 if idx == 0:
52 `(`
53 trim ',':
54 for _,v in columns:
55 ${v},
56 `) VALUES `
57 (
58 trim ',':
59 for _,v in columns:
60 #{table[v]},
61 ),
62 "
63 )]
64 async fn insert_batch(
65 executor: &dyn $crate::executor::Executor,
66 tables: &[$table],
67 table_name: &str,
68 ) -> std::result::Result<$crate::rbdc::db::ExecResult, $crate::rbdc::Error>
69 {
70 impled!()
71 }
72 if tables.is_empty() {
73 return Err($crate::rbdc::Error::from(
74 "insert can not insert empty array tables!",
75 ));
76 }
77 let mut table_name = $table_name.to_string();
78 if table_name.is_empty(){
79 #[$crate::snake_name($table)]
80 fn snake_name(){}
81 table_name = snake_name();
82 }
83 let mut result = $crate::rbdc::db::ExecResult {
84 rows_affected: 0,
85 last_insert_id: rbs::Value::Null,
86 };
87 let ranges = $crate::plugin::Page::<()>::make_ranges(tables.len() as u64, batch_size);
88 for (offset, limit) in ranges {
89 let exec_result = insert_batch(
90 executor,
91 &tables[offset as usize..limit as usize],
92 table_name.as_str(),
93 )
94 .await?;
95 result.rows_affected += exec_result.rows_affected;
96 result.last_insert_id = exec_result.last_insert_id;
97 }
98 Ok(result)
99 }
100
101 /// insert a single record
102 ///
103 /// sql: `INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...)`
104 pub async fn insert(
105 executor: &dyn $crate::executor::Executor,
106 table: &$table,
107 ) -> std::result::Result<$crate::rbdc::db::ExecResult, $crate::rbdc::Error> {
108 <$table>::insert_batch(executor, std::slice::from_ref(table), 1).await
109 }
110 }
111 // select
112 impl $table {
113 /// select records by condition map.
114 /// supports "column" key in condition to select specific columns, e.g. `value!{"col1":"val1", "column": ["col1", "col2"]}`
115 ///
116 /// sql: `SELECT col1, col2, ... FROM table_name WHERE col1 = ? and col2 in (?, ?, ...)`
117 ///
118 /// condition map -> where sql:
119 /// - `value!{"col1": "val1"}` -> `WHERE col1 = 'val1'`
120 /// - `value!{"col1": 1, "col2": "val2"}` -> `WHERE col1 = 1 and col2 = 'val2'`
121 /// - `value!{"col1": ["v1", "v2", "v3"]}` -> `WHERE col1 in ('v1', 'v2', 'v3')`
122 /// - `value!{"col1": "val1", "col2": ["a", "b"]}` -> `WHERE col1 = 'val1' and col2 in ('a', 'b')`
123 /// - null values are skipped
124 pub async fn select_by_map(executor: &dyn $crate::executor::Executor, mut condition: rbs::Value) -> std::result::Result<Vec<$table>, $crate::rbdc::Error> {
125 use rbatis::crud_traits::ValueOperatorSql;
126 // Extract column specification and remove it from condition
127 let table_column = {
128 let mut columns = String::new();
129 let mut clean_map = rbs::value::map::ValueMap::with_capacity(condition.len());
130 for (k, v) in condition {
131 match k.as_str() {
132 Some("column") => {
133 columns = match v {
134 rbs::Value::String(s) => s.clone(),
135 rbs::Value::Array(arr) => {
136 let cols: Vec<&str> = arr.iter()
137 .filter_map(|v| v.as_str())
138 .collect();
139 if cols.is_empty() { "*".to_string() } else { cols.join(", ") }
140 }
141 _ => "*".to_string(),
142 };
143 }
144 _ => { clean_map.insert(k.clone(), v.clone()); }
145 }
146 }
147 if columns.is_empty() { columns = "*".to_string(); }
148 condition = rbs::Value::Map(clean_map);
149 columns
150 };
151
152 #[$crate::py_sql(
153 "`select ${table_column} from ${table_name}`
154 trim end=' where ':
155 ` where `
156 trim ' and ': for key,item in condition:
157 if item == null:
158 continue:
159 if !item.is_array():
160 ` and ${key.operator_sql()}#{item}`
161 if item.is_array():
162 ` and ${key} in (`
163 trim ',': for _,item_array in item:
164 #{item_array},
165 `)`
166 ")]
167 async fn select_by_map(
168 executor: &dyn $crate::executor::Executor,
169 table_name: String,
170 table_column: &str,
171 condition: &rbs::Value
172 ) -> std::result::Result<Vec<$table>, $crate::rbdc::Error> {
173 for (_,v) in condition {
174 if v.is_array() && v.is_empty(){
175 return Ok(vec![]);
176 }
177 }
178 impled!()
179 }
180 let mut table_name = $table_name.to_string();
181 if table_name.is_empty(){
182 #[$crate::snake_name($table)]
183 fn snake_name(){}
184 table_name = snake_name();
185 }
186 select_by_map(executor, table_name, &table_column, &condition).await
187 }
188 }
189 // update
190 impl $table {
191 /// update records by condition map.
192 /// supports "column" key in condition to update specific columns, e.g. `value!{"col1":"val1", "column": ["col2", "col3"]}`
193 ///
194 /// sql: `UPDATE table_name SET col1 = ?, col2 = ?, ... WHERE col1 = ? and col2 in (?, ?, ...)`
195 /// note: skips null fields by default, skips 'id' field always
196 ///
197 /// condition map -> where sql:
198 /// - `value!{"col1": "val1"}` -> `WHERE col1 = 'val1'`
199 /// - `value!{"col1": 1, "col2": "val2"}` -> `WHERE col1 = 1 and col2 = 'val2'`
200 /// - `value!{"col1": ["v1", "v2", "v3"]}` -> `WHERE col1 in ('v1', 'v2', 'v3')`
201 /// - `value!{"col1": "val1", "col2": ["a", "b"]}` -> `WHERE col1 = 'val1' and col2 in ('a', 'b')`
202 /// - null values are skipped
203 pub async fn update_by_map(
204 executor: &dyn $crate::executor::Executor,
205 table: &$table,
206 mut condition: rbs::Value
207 ) -> std::result::Result<$crate::rbdc::db::ExecResult, $crate::rbdc::Error> {
208 use rbatis::crud_traits::{ValueOperatorSql, FilterByColumns};
209
210 // Extract column list for selective updates - implements GitHub issue #591
211 let set_columns = {
212 let mut columns = rbs::Value::Null;
213 let mut clean_map = rbs::value::map::ValueMap::with_capacity(condition.len());
214 for (k, v) in condition {
215 match k.as_str() {
216 Some("column") => {
217 columns = match v {
218 rbs::Value::String(s) => {
219 rbs::Value::Array(vec![rbs::Value::String(s.clone())])
220 }
221 rbs::Value::Array(arr) => {
222 let filtered_array: Vec<rbs::Value> = arr.iter()
223 .filter(|v| v.as_str().is_some())
224 .cloned()
225 .collect();
226 if filtered_array.is_empty() {
227 rbs::Value::Null
228 } else {
229 rbs::Value::Array(filtered_array)
230 }
231 }
232 _ => rbs::Value::Null,
233 };
234 }
235 _ => { clean_map.insert(k.clone(), v.clone()); }
236 }
237 }
238 condition = rbs::Value::Map(clean_map);
239 columns
240 };
241 #[$crate::py_sql(
242 "`update ${table_name}
243 if skip_null == false:
244 set collection='table',skips='id',skip_null=false:
245 if skip_null == true:
246 set collection='table',skips='id':
247 trim end=' where ':
248 ` where `
249 trim ' and ': for key,item in condition:
250 if item == null:
251 continue:
252 if !item.is_array():
253 ` and ${key.operator_sql()}#{item}`
254 if item.is_array():
255 ` and ${key} in (`
256 trim ',': for _,item_array in item:
257 #{item_array},
258 `)`
259 "
260 )]
261 async fn update_by_map_internal(
262 executor: &dyn $crate::executor::Executor,
263 table_name: String,
264 table: &rbs::Value,
265 condition: &rbs::Value,
266 skip_null: bool,
267 ) -> std::result::Result<$crate::rbdc::db::ExecResult, $crate::rbdc::Error> {
268 for (_,v) in condition {
269 if v.is_array() && v.is_empty(){
270 return Ok($crate::rbdc::db::ExecResult::default());
271 }
272 }
273 impled!()
274 }
275 let mut table_name = $table_name.to_string();
276 if table_name.is_empty(){
277 #[$crate::snake_name($table)]
278 fn snake_name(){}
279 table_name = snake_name();
280 }
281 let table_value = rbs::value!(table);
282 let mut skip_null = true;
283 let table = if set_columns != rbs::Value::Null {
284 skip_null = false;
285 table_value.filter_by_columns(&set_columns)
286 } else {
287 table_value
288 };
289 update_by_map_internal(executor, table_name, &table, &condition, skip_null).await
290 }
291 }
292 // delete
293 impl $table {
294 /// delete records by condition map
295 ///
296 /// sql: `DELETE FROM table_name WHERE col1 = ? and col2 in (?, ?, ...)`
297 ///
298 /// condition map -> where sql:
299 /// - `value!{"col1": "val1"}` -> `WHERE col1 = 'val1'`
300 /// - `value!{"col1": 1, "col2": "val2"}` -> `WHERE col1 = 1 and col2 = 'val2'`
301 /// - `value!{"col1": ["v1", "v2", "v3"]}` -> `WHERE col1 in ('v1', 'v2', 'v3')`
302 /// - `value!{"col1": "val1", "col2": ["a", "b"]}` -> `WHERE col1 = 'val1' and col2 in ('a', 'b')`
303 /// - null values are skipped
304 pub async fn delete_by_map(executor: &dyn $crate::executor::Executor, condition: rbs::Value) -> std::result::Result<$crate::rbdc::db::ExecResult, $crate::rbdc::Error> {
305 use rbatis::crud_traits::ValueOperatorSql;
306 #[$crate::py_sql(
307 "`delete from ${table_name}`
308 trim end=' where ':
309 ` where `
310 trim ' and ': for key,item in condition:
311 if item == null:
312 continue:
313 if !item.is_array():
314 ` and ${key.operator_sql()}#{item}`
315 if item.is_array():
316 ` and ${key} in (`
317 trim ',': for _,item_array in item:
318 #{item_array},
319 `)`
320 ")]
321 async fn delete_by_map(
322 executor: &dyn $crate::executor::Executor,
323 table_name: String,
324 condition: &rbs::Value
325 ) -> std::result::Result<$crate::rbdc::db::ExecResult, $crate::rbdc::Error> {
326 for (_,v) in condition {
327 if v.is_array() && v.is_empty(){
328 return Ok($crate::rbdc::db::ExecResult::default());
329 }
330 }
331 impled!()
332 }
333 let mut table_name = $table_name.to_string();
334 if table_name.is_empty(){
335 #[$crate::snake_name($table)]
336 fn snake_name(){}
337 table_name = snake_name();
338 }
339 delete_by_map(executor, table_name, &condition).await
340 }
341 }
342 };
343}
344
345/// impl html_sql select page.
346///
347/// you must deal with 3 param:
348/// (do_count:bool,page_no:u64,page_size:u64)
349///
350/// you must deal with sql:
351/// return Vec<Record>（if param do_count = false）
352/// return u64（if param do_count = true）
353///
354/// you can see ${page_no} = (page_no -1) * page_size;
355/// you can see ${page_size} = page_size;
356///
357/// just like this example:
358/// ```html
359/// <select id="select_page_data">
360/// `select `
361/// <if test="do_count == true">
362/// `count(1) from table`
363/// </if>
364/// <if test="do_count == false">
365/// `* from table limit ${page_no},${page_size}`
366/// </if>
367/// </select>
368/// ```
369/// ```
370/// #[derive(serde::Serialize, serde::Deserialize)]
371/// pub struct MockTable{}
372/// //rbatis::htmlsql_select_page!(select_page_data(name: &str) -> MockTable => "example.html");
373/// rbatis::htmlsql_select_page!(select_page_data(name: &str) -> MockTable => r#"
374/// <select id="select_page_data">
375/// `select * from table where id > 1 limit ${page_no},${page_size} `
376/// </select>"#);
377///
378/// rbatis::pysql_select_page!(pysql_select_page(name:&str) -> MockTable =>
379/// r#"`select * from activity where delete_flag = 0`
380/// if name != '':
381/// ` and name=#{name}`
382/// ` limit ${page_no},${page_size}`
383/// "#);
384/// ```
385#[macro_export]
386macro_rules! htmlsql_select_page {
387 ($fn_name:ident($($param_key:ident:$param_type:ty$(,)?)*) -> $table:ty => $($html_file:expr$(,)?)*) => {
388 pub async fn $fn_name(executor: &dyn $crate::executor::Executor, page_request: &dyn $crate::plugin::IPageRequest, $($param_key:$param_type,)*) -> std::result::Result<$crate::plugin::Page<$table>, $crate::rbdc::Error> {
389 #[$crate::html_sql($($html_file,)*)]
390 pub async fn $fn_name(executor: &dyn $crate::executor::Executor,do_count:bool,page_no:u64,page_size:u64,$($param_key: &$param_type,)*) -> std::result::Result<rbs::Value, $crate::rbdc::Error>{
391 $crate::impled!()
392 }
393 let mut executor = executor;
394 let mut conn = None;
395 if executor.name().eq($crate::executor::Executor::name(executor.rb_ref())){
396 conn = Some(executor.rb_ref().acquire().await?);
397 match &conn {
398 Some(c) => {
399 executor = c;
400 }
401 None => {}
402 }
403 }
404 let mut total = 0;
405 let mut intercept = executor.rb_ref().get_intercept::<$crate::plugin::intercept_page::PageIntercept>().ok_or_else(|| $crate::rbdc::Error::from("PageIntercept not found"))?;
406 if page_request.do_count() {
407 intercept.count_ids.insert(executor.id(),$crate::plugin::PageRequest::new(page_request.page_no(), page_request.page_size()));
408 let total_value = $fn_name(executor, true, page_request.offset(), page_request.page_size(), $(&$param_key,)*).await?;
409 total = $crate::decode(total_value).unwrap_or(0);
410 }
411 intercept.select_ids.insert(executor.id(),$crate::plugin::PageRequest::new(page_request.page_no(), page_request.page_size()));
412 let mut page = $crate::plugin::Page::<$table>::new(page_request.page_no(), page_request.page_size(), total,vec![]);
413 let records_value = $fn_name(executor, false, page_request.offset(), page_request.page_size(), $(&$param_key,)*).await?;
414 page.records = rbs::from_value(records_value)?;
415 Ok(page)
416 }
417 }
418}
419
420/// impl py_sql select page.
421///
422/// you must deal with 3 param:
423/// (do_count:bool,page_no:u64,page_size:u64)
424///
425/// you must deal with sql:
426/// return Vec<Record>（if param do_count = false）
427/// return u64（if param do_count = true）·
428///
429/// you can see ${page_no} = (page_no -1) * page_size;
430/// you can see ${page_size} = page_size;
431///
432/// just like this example:
433/// ```py
434/// `select * from activity where delete_flag = 0`
435/// if name != '':
436/// ` and name=#{name}`
437/// if !ids.is_empty():
438/// ` and id in `
439/// ${ids.sql()}
440/// ```
441/// ```
442/// #[derive(serde::Serialize, serde::Deserialize)]
443/// pub struct MockTable{}
444/// rbatis::pysql_select_page!(pysql_select_page(name:&str) -> MockTable =>
445/// r#"`select * from activity where delete_flag = 0`
446/// if name != '':
447/// ` and name=#{name}`
448/// ` limit ${page_no},${page_size}`
449/// "#);
450/// ```
451#[macro_export]
452macro_rules! pysql_select_page {
453 ($fn_name:ident($($param_key:ident:$param_type:ty$(,)?)*) -> $table:ty => $($py_file:expr$(,)?)*) => {
454 pub async fn $fn_name(executor: &dyn $crate::executor::Executor, page_request: &dyn $crate::plugin::IPageRequest, $($param_key:$param_type,)*) -> std::result::Result<$crate::plugin::Page<$table>, $crate::rbdc::Error> {
455 #[$crate::py_sql($($py_file,)*)]
456 pub async fn $fn_name(executor: &dyn $crate::executor::Executor,do_count:bool,page_no:u64,page_size:u64,$($param_key: &$param_type,)*) -> std::result::Result<rbs::Value, $crate::rbdc::Error>{
457 $crate::impled!()
458 }
459 let mut executor = executor;
460 let mut conn = None;
461 if executor.name().eq($crate::executor::Executor::name(executor.rb_ref())){
462 conn = Some(executor.rb_ref().acquire().await?);
463 match &conn {
464 Some(c) => {
465 executor = c;
466 }
467 None => {}
468 }
469 }
470 let mut intercept = executor.rb_ref().get_intercept::<$crate::plugin::intercept_page::PageIntercept>().ok_or_else(|| $crate::rbdc::Error::from("PageIntercept not found"))?;
471 let mut total = 0;
472 if page_request.do_count() {
473 intercept.count_ids.insert(executor.id(),$crate::plugin::PageRequest::new(page_request.page_no(), page_request.page_size()));
474 let total_value = $fn_name(executor, true, page_request.offset(), page_request.page_size(), $(&$param_key,)*).await?;
475 total = $crate::decode(total_value).unwrap_or(0);
476 }
477 intercept.select_ids.insert(executor.id(),$crate::plugin::PageRequest::new(page_request.page_no(), page_request.page_size()));
478 let mut page = $crate::plugin::Page::<$table>::new(page_request.page_no(), page_request.page_size(), total,vec![]);
479 let records_value = $fn_name(executor, false, page_request.offset(), page_request.page_size(), $(&$param_key,)*).await?;
480 page.records = rbs::from_value(records_value)?;
481 Ok(page)
482 }
483 }
484}
485
486/// use macro wrapper #[sql]
487/// for example:
488/// ```rust
489/// use rbatis::executor::Executor;
490/// rbatis::raw_sql!(test_same_id(rb: &dyn Executor, id: &u64) -> Result<rbs::Value, rbatis::Error> =>
491/// "select * from table where id = ?"
492/// );
493/// ```
494#[macro_export]
495macro_rules! raw_sql {
496 ($fn_name:ident($($param_key:ident:$param_type:ty$(,)?)*) -> $return_type:ty => $sql_file:expr) => {
497 #[$crate::sql($sql_file)]
498 pub async fn $fn_name($($param_key: $param_type,)*) -> $return_type{
499 impled!()
500 }
501 }
502}
503
504/// use macro wrapper #[py_sql]
505/// for query example:
506/// ```rust
507/// use rbatis::executor::Executor;
508/// rbatis::pysql!(test_same_id(rb: &dyn Executor, id: &u64) -> Result<rbs::Value, rbatis::Error> =>
509/// "select * from table where ${id} = 1
510/// if id != 0:
511/// `id = #{id}`"
512/// );
513/// ```
514/// for exec example:
515/// ```rust
516/// use rbatis::executor::Executor;
517/// use rbdc::db::ExecResult;
518/// rbatis::pysql!(test_same_id(rb: &dyn Executor, id: &u64) -> Result<ExecResult, rbatis::Error> =>
519/// "`update activity set name = '1' where id = #{id}`"
520/// );
521/// ```
522#[macro_export]
523macro_rules! pysql {
524 ($fn_name:ident($($param_key:ident:$param_type:ty$(,)?)*) -> $return_type:ty => $py_file:expr) => {
525 #[$crate::py_sql($py_file)]
526 pub async fn $fn_name($($param_key: $param_type,)*) -> $return_type{
527 impled!()
528 }
529 }
530}
531
532/// use macro wrapper #[html_sql]
533/// for example query rbs::Value:
534/// ```rust
535/// use rbatis::executor::Executor;
536/// rbatis::htmlsql!(test_select(rb: &dyn Executor, id: &u64) -> Result<rbs::Value, rbatis::Error> => r#"
537/// <mapper>
538/// <select id="test_same_id">
539/// `select ${id} from my_table`
540/// </select>
541/// </mapper>"#);
542/// ```
543/// exec (from file)
544/// ```rust
545/// use rbatis::executor::Executor;
546/// use rbdc::db::ExecResult;
547/// rbatis::htmlsql!(update_by_id(rb: &dyn Executor, id: &u64) -> Result<ExecResult, rbatis::Error> => "example/example.html");
548/// ```
549/// query
550/// ```rust
551/// use rbatis::executor::Executor;
552/// #[derive(serde::Serialize, serde::Deserialize)]
553/// pub struct MyTable{
554/// pub id:Option<u64>,
555/// pub name:Option<String>,
556/// }
557/// rbatis::htmlsql!(test_select_table(rb: &dyn Executor, id: &u64) -> Result<Vec<MyTable>, rbatis::Error> => r#"
558/// <mapper>
559/// <select id="test_same_id">
560/// `select * from my_table`
561/// </select>
562/// </mapper>"#);
563/// ```
564#[macro_export]
565macro_rules! htmlsql {
566 ($fn_name:ident($($param_key:ident:$param_type:ty$(,)?)*) -> $return_type:ty => $html_file:expr) => {
567 #[$crate::html_sql($html_file)]
568 pub async fn $fn_name($($param_key: $param_type,)*) -> $return_type{
569 impled!()
570 }
571 }
572}