1use crate::quoting::{
2 quote_column_ref, quote_identifier, quote_table_ref, quote_table_reference, quote_table_source,
3};
4use sql_orm_core::{ColumnValue, OrmError, SqlValue};
5use sql_orm_query::{
6 BinaryOp, CompiledQuery, CountQuery, DeleteQuery, Expr, InsertQuery, Join, JoinType, OrderBy,
7 Pagination, Predicate, Query, SelectProjection, SelectQuery, SortDirection, TableRef, UnaryOp,
8 UpdateQuery,
9};
10use std::collections::BTreeSet;
11
12#[derive(Debug, Default)]
13struct ParameterBuilder {
14 params: Vec<SqlValue>,
15}
16
17impl ParameterBuilder {
18 fn push(&mut self, value: SqlValue) -> String {
19 self.params.push(value);
20 format!("@P{}", self.params.len())
21 }
22
23 fn finish(self, sql: String) -> CompiledQuery {
24 CompiledQuery::new(sql, self.params)
25 }
26}
27
28impl crate::SqlServerCompiler {
29 pub fn compile_query(query: &Query) -> Result<CompiledQuery, OrmError> {
30 match query {
31 Query::Select(query) => Self::compile_select(query),
32 Query::Insert(query) => Self::compile_insert(query),
33 Query::Update(query) => Self::compile_update(query),
34 Query::Delete(query) => Self::compile_delete(query),
35 Query::Count(query) => Self::compile_count(query),
36 }
37 }
38
39 pub fn compile_select(query: &SelectQuery) -> Result<CompiledQuery, OrmError> {
40 let mut parameters = ParameterBuilder::default();
41 let projection = compile_projection(&query.projection, &mut parameters)?;
42 let mut sql = format!(
43 "SELECT {projection} FROM {}",
44 quote_table_source(&query.from)?
45 );
46 sql.push_str(&compile_joins(&query.from, &query.joins, &mut parameters)?);
47
48 if let Some(predicate) = &query.predicate {
49 let predicate = compile_predicate(predicate, &mut parameters)?;
50 sql.push_str(" WHERE ");
51 sql.push_str(&predicate);
52 }
53
54 if !query.order_by.is_empty() {
55 sql.push_str(" ORDER BY ");
56 sql.push_str(&compile_order_by(&query.order_by)?);
57 }
58
59 if let Some(pagination) = query.pagination {
60 if query.order_by.is_empty() {
61 return Err(OrmError::new(
62 "SQL Server pagination requires ORDER BY before OFFSET/FETCH",
63 ));
64 }
65
66 sql.push(' ');
67 sql.push_str(&compile_pagination(pagination, &mut parameters));
68 }
69
70 Ok(parameters.finish(sql))
71 }
72
73 pub fn compile_insert(query: &InsertQuery) -> Result<CompiledQuery, OrmError> {
74 if query.values.is_empty() {
75 return Err(OrmError::new(
76 "SQL Server insert compilation requires at least one value",
77 ));
78 }
79
80 let mut parameters = ParameterBuilder::default();
81 let (columns, values) = compile_column_values(&query.values, &mut parameters)?;
82 let sql = format!(
83 "INSERT INTO {} ({columns}) OUTPUT INSERTED.* VALUES ({values})",
84 quote_table_ref(&query.into)?,
85 );
86
87 Ok(parameters.finish(sql))
88 }
89
90 pub fn compile_update(query: &UpdateQuery) -> Result<CompiledQuery, OrmError> {
91 if query.changes.is_empty() {
92 return Err(OrmError::new(
93 "SQL Server update compilation requires at least one change",
94 ));
95 }
96
97 let mut parameters = ParameterBuilder::default();
98 let assignments = compile_assignments(&query.changes, &mut parameters)?;
99 let mut sql = format!(
100 "UPDATE {} SET {assignments} OUTPUT INSERTED.*",
101 quote_table_ref(&query.table)?,
102 );
103
104 if let Some(predicate) = &query.predicate {
105 let predicate = compile_predicate(predicate, &mut parameters)?;
106 sql.push_str(" WHERE ");
107 sql.push_str(&predicate);
108 }
109
110 Ok(parameters.finish(sql))
111 }
112
113 pub fn compile_delete(query: &DeleteQuery) -> Result<CompiledQuery, OrmError> {
114 let mut parameters = ParameterBuilder::default();
115 let mut sql = format!("DELETE FROM {}", quote_table_ref(&query.from)?);
116
117 if let Some(predicate) = &query.predicate {
118 let predicate = compile_predicate(predicate, &mut parameters)?;
119 sql.push_str(" WHERE ");
120 sql.push_str(&predicate);
121 }
122
123 Ok(parameters.finish(sql))
124 }
125
126 pub fn compile_count(query: &CountQuery) -> Result<CompiledQuery, OrmError> {
127 let mut parameters = ParameterBuilder::default();
128 let mut sql = format!(
129 "SELECT COUNT(*) AS {} FROM {}",
130 quote_identifier("count")?,
131 quote_table_source(&query.from)?,
132 );
133
134 if let Some(predicate) = &query.predicate {
135 let predicate = compile_predicate(predicate, &mut parameters)?;
136 sql.push_str(" WHERE ");
137 sql.push_str(&predicate);
138 }
139
140 Ok(parameters.finish(sql))
141 }
142}
143
144fn compile_joins(
145 from: &TableRef,
146 joins: &[Join],
147 parameters: &mut ParameterBuilder,
148) -> Result<String, OrmError> {
149 let mut compiled = String::new();
150 let mut seen_tables = vec![*from];
151
152 for join in joins {
153 if seen_tables.contains(&join.table) {
154 return Err(OrmError::new(
155 "SQL Server join compilation requires aliases for repeated table sources",
156 ));
157 }
158
159 seen_tables.push(join.table);
160 compiled.push(' ');
161 compiled.push_str(match join.join_type {
162 JoinType::Inner => "INNER JOIN ",
163 JoinType::Left => "LEFT JOIN ",
164 });
165 compiled.push_str("e_table_source(&join.table)?);
166 compiled.push_str(" ON ");
167 compiled.push_str(&compile_predicate(&join.on, parameters)?);
168 }
169
170 Ok(compiled)
171}
172
173fn compile_projection(
174 projection: &[SelectProjection],
175 parameters: &mut ParameterBuilder,
176) -> Result<String, OrmError> {
177 if projection.is_empty() {
178 return Ok("*".to_string());
179 }
180
181 let mut aliases = BTreeSet::new();
182 let parts = projection
183 .iter()
184 .map(|projection| {
185 let alias = projection.alias.ok_or_else(|| {
186 OrmError::new("SQL Server projection expressions require an explicit alias")
187 })?;
188 if alias.trim().is_empty() {
189 return Err(OrmError::new("SQL Server projection alias cannot be empty"));
190 }
191 if !aliases.insert(alias) {
192 return Err(OrmError::new(format!(
193 "SQL Server projection alias `{alias}` is duplicated"
194 )));
195 }
196
197 Ok(format!(
198 "{} AS {}",
199 compile_expr(&projection.expr, parameters)?,
200 quote_identifier(alias)?
201 ))
202 })
203 .collect::<Result<Vec<_>, _>>()?;
204 Ok(parts.join(", "))
205}
206
207fn compile_expr(expr: &Expr, parameters: &mut ParameterBuilder) -> Result<String, OrmError> {
208 match expr {
209 Expr::Column(column) => quote_column_ref(column),
210 Expr::Value(value) => Ok(parameters.push(value.clone())),
211 Expr::Binary { left, op, right } => Ok(format!(
212 "({} {} {})",
213 compile_expr(left, parameters)?,
214 compile_binary_op(*op),
215 compile_expr(right, parameters)?,
216 )),
217 Expr::Unary { op, expr } => Ok(format!(
218 "({} {})",
219 compile_unary_op(*op),
220 compile_expr(expr, parameters)?,
221 )),
222 Expr::Function { name, args } => {
223 if name.trim().is_empty() {
224 return Err(OrmError::new("SQL function name cannot be empty"));
225 }
226
227 let args = args
228 .iter()
229 .map(|arg| compile_expr(arg, parameters))
230 .collect::<Result<Vec<_>, _>>()?;
231
232 Ok(format!("{name}({})", args.join(", ")))
233 }
234 }
235}
236
237fn compile_predicate(
238 predicate: &Predicate,
239 parameters: &mut ParameterBuilder,
240) -> Result<String, OrmError> {
241 match predicate {
242 Predicate::Eq(left, right) => compile_comparison(left, "=", right, parameters),
243 Predicate::Ne(left, right) => compile_comparison(left, "<>", right, parameters),
244 Predicate::Gt(left, right) => compile_comparison(left, ">", right, parameters),
245 Predicate::Gte(left, right) => compile_comparison(left, ">=", right, parameters),
246 Predicate::Lt(left, right) => compile_comparison(left, "<", right, parameters),
247 Predicate::Lte(left, right) => compile_comparison(left, "<=", right, parameters),
248 Predicate::Like(left, right) => compile_comparison(left, "LIKE", right, parameters),
249 Predicate::IsNull(expr) => Ok(format!("({} IS NULL)", compile_expr(expr, parameters)?)),
250 Predicate::IsNotNull(expr) => {
251 Ok(format!("({} IS NOT NULL)", compile_expr(expr, parameters)?))
252 }
253 Predicate::And(predicates) => compile_logical("AND", predicates, parameters),
254 Predicate::Or(predicates) => compile_logical("OR", predicates, parameters),
255 Predicate::Not(predicate) => Ok(format!(
256 "(NOT {})",
257 compile_predicate(predicate, parameters)?
258 )),
259 }
260}
261
262fn compile_comparison(
263 left: &Expr,
264 operator: &str,
265 right: &Expr,
266 parameters: &mut ParameterBuilder,
267) -> Result<String, OrmError> {
268 Ok(format!(
269 "({} {operator} {})",
270 compile_expr(left, parameters)?,
271 compile_expr(right, parameters)?,
272 ))
273}
274
275fn compile_logical(
276 operator: &str,
277 predicates: &[Predicate],
278 parameters: &mut ParameterBuilder,
279) -> Result<String, OrmError> {
280 if predicates.is_empty() {
281 return Err(OrmError::new(
282 "logical predicate compilation requires at least one child predicate",
283 ));
284 }
285
286 let compiled = predicates
287 .iter()
288 .map(|predicate| compile_predicate(predicate, parameters))
289 .collect::<Result<Vec<_>, _>>()?;
290
291 Ok(format!("({})", compiled.join(&format!(" {operator} "))))
292}
293
294fn compile_order_by(order_by: &[OrderBy]) -> Result<String, OrmError> {
295 let parts = order_by
296 .iter()
297 .map(|order| {
298 Ok(format!(
299 "{}.{} {}",
300 quote_table_reference(&order.table)?,
301 quote_identifier(order.column_name)?,
302 match order.direction {
303 SortDirection::Asc => "ASC",
304 SortDirection::Desc => "DESC",
305 },
306 ))
307 })
308 .collect::<Result<Vec<_>, OrmError>>()?;
309
310 Ok(parts.join(", "))
311}
312
313fn compile_pagination(pagination: Pagination, parameters: &mut ParameterBuilder) -> String {
314 let offset = parameters.push(SqlValue::I64(pagination.offset as i64));
315 let limit = parameters.push(SqlValue::I64(pagination.limit as i64));
316
317 format!("OFFSET {offset} ROWS FETCH NEXT {limit} ROWS ONLY")
318}
319
320fn compile_column_values(
321 values: &[ColumnValue],
322 parameters: &mut ParameterBuilder,
323) -> Result<(String, String), OrmError> {
324 let mut columns = Vec::with_capacity(values.len());
325 let mut placeholders = Vec::with_capacity(values.len());
326
327 for value in values {
328 columns.push(quote_identifier(value.column_name)?);
329 placeholders.push(parameters.push(value.value.clone()));
330 }
331
332 Ok((columns.join(", "), placeholders.join(", ")))
333}
334
335fn compile_assignments(
336 changes: &[ColumnValue],
337 parameters: &mut ParameterBuilder,
338) -> Result<String, OrmError> {
339 let assignments = changes
340 .iter()
341 .map(|change| {
342 Ok(format!(
343 "{} = {}",
344 quote_identifier(change.column_name)?,
345 parameters.push(change.value.clone()),
346 ))
347 })
348 .collect::<Result<Vec<_>, OrmError>>()?;
349
350 Ok(assignments.join(", "))
351}
352
353fn compile_binary_op(op: BinaryOp) -> &'static str {
354 match op {
355 BinaryOp::Add => "+",
356 BinaryOp::Subtract => "-",
357 BinaryOp::Multiply => "*",
358 BinaryOp::Divide => "/",
359 }
360}
361
362fn compile_unary_op(op: UnaryOp) -> &'static str {
363 match op {
364 UnaryOp::Negate => "-",
365 }
366}
367
368#[cfg(test)]
369mod tests {
370 use super::super::SqlServerCompiler;
371 use sql_orm_core::{
372 Changeset, ColumnMetadata, ColumnValue, Entity, EntityColumn, EntityMetadata,
373 IdentityMetadata, Insertable, PrimaryKeyMetadata, SqlServerType, SqlValue,
374 };
375 use sql_orm_query::{
376 BinaryOp, CountQuery, DeleteQuery, Expr, InsertQuery, OrderBy, Pagination, Predicate,
377 Query, SelectProjection, SelectQuery, TableRef, UnaryOp, UpdateQuery,
378 };
379
380 #[allow(dead_code)]
381 struct Customer;
382
383 #[allow(dead_code)]
384 struct Order;
385
386 static CUSTOMER_COLUMNS: [ColumnMetadata; 4] = [
387 ColumnMetadata {
388 rust_field: "id",
389 column_name: "id",
390 renamed_from: None,
391 sql_type: SqlServerType::BigInt,
392 nullable: false,
393 primary_key: true,
394 identity: Some(IdentityMetadata::new(1, 1)),
395 default_sql: None,
396 computed_sql: None,
397 rowversion: false,
398 insertable: false,
399 updatable: false,
400 max_length: None,
401 precision: None,
402 scale: None,
403 },
404 ColumnMetadata {
405 rust_field: "email",
406 column_name: "email",
407 renamed_from: None,
408 sql_type: SqlServerType::NVarChar,
409 nullable: false,
410 primary_key: false,
411 identity: None,
412 default_sql: None,
413 computed_sql: None,
414 rowversion: false,
415 insertable: true,
416 updatable: true,
417 max_length: Some(160),
418 precision: None,
419 scale: None,
420 },
421 ColumnMetadata {
422 rust_field: "active",
423 column_name: "active",
424 renamed_from: None,
425 sql_type: SqlServerType::Bit,
426 nullable: false,
427 primary_key: false,
428 identity: None,
429 default_sql: Some("1"),
430 computed_sql: None,
431 rowversion: false,
432 insertable: true,
433 updatable: true,
434 max_length: None,
435 precision: None,
436 scale: None,
437 },
438 ColumnMetadata {
439 rust_field: "created_at",
440 column_name: "created_at",
441 renamed_from: None,
442 sql_type: SqlServerType::DateTime2,
443 nullable: false,
444 primary_key: false,
445 identity: None,
446 default_sql: Some("SYSUTCDATETIME()"),
447 computed_sql: None,
448 rowversion: false,
449 insertable: true,
450 updatable: true,
451 max_length: None,
452 precision: None,
453 scale: None,
454 },
455 ];
456
457 static CUSTOMER_METADATA: EntityMetadata = EntityMetadata {
458 rust_name: "Customer",
459 schema: "sales",
460 table: "customers",
461 renamed_from: None,
462 columns: &CUSTOMER_COLUMNS,
463 primary_key: PrimaryKeyMetadata::new(Some("pk_customers"), &["id"]),
464 indexes: &[],
465 foreign_keys: &[],
466 navigations: &[],
467 };
468
469 impl Entity for Customer {
470 fn metadata() -> &'static EntityMetadata {
471 &CUSTOMER_METADATA
472 }
473 }
474
475 static ORDER_COLUMNS: [ColumnMetadata; 3] = [
476 ColumnMetadata {
477 rust_field: "id",
478 column_name: "id",
479 renamed_from: None,
480 sql_type: SqlServerType::BigInt,
481 nullable: false,
482 primary_key: true,
483 identity: Some(IdentityMetadata::new(1, 1)),
484 default_sql: None,
485 computed_sql: None,
486 rowversion: false,
487 insertable: false,
488 updatable: false,
489 max_length: None,
490 precision: None,
491 scale: None,
492 },
493 ColumnMetadata {
494 rust_field: "customer_id",
495 column_name: "customer_id",
496 renamed_from: None,
497 sql_type: SqlServerType::BigInt,
498 nullable: false,
499 primary_key: false,
500 identity: None,
501 default_sql: None,
502 computed_sql: None,
503 rowversion: false,
504 insertable: true,
505 updatable: true,
506 max_length: None,
507 precision: None,
508 scale: None,
509 },
510 ColumnMetadata {
511 rust_field: "total_cents",
512 column_name: "total_cents",
513 renamed_from: None,
514 sql_type: SqlServerType::BigInt,
515 nullable: false,
516 primary_key: false,
517 identity: None,
518 default_sql: None,
519 computed_sql: None,
520 rowversion: false,
521 insertable: true,
522 updatable: true,
523 max_length: None,
524 precision: None,
525 scale: None,
526 },
527 ];
528
529 static ORDER_METADATA: EntityMetadata = EntityMetadata {
530 rust_name: "Order",
531 schema: "sales",
532 table: "orders",
533 renamed_from: None,
534 columns: &ORDER_COLUMNS,
535 primary_key: PrimaryKeyMetadata::new(Some("pk_orders"), &["id"]),
536 indexes: &[],
537 foreign_keys: &[],
538 navigations: &[],
539 };
540
541 impl Entity for Order {
542 fn metadata() -> &'static EntityMetadata {
543 &ORDER_METADATA
544 }
545 }
546
547 #[allow(non_upper_case_globals)]
548 impl Customer {
549 const id: EntityColumn<Customer> = EntityColumn::new("id", "id");
550 const email: EntityColumn<Customer> = EntityColumn::new("email", "email");
551 const active: EntityColumn<Customer> = EntityColumn::new("active", "active");
552 const created_at: EntityColumn<Customer> = EntityColumn::new("created_at", "created_at");
553 }
554
555 #[allow(non_upper_case_globals)]
556 impl Order {
557 const customer_id: EntityColumn<Order> = EntityColumn::new("customer_id", "customer_id");
558 const total_cents: EntityColumn<Order> = EntityColumn::new("total_cents", "total_cents");
559 }
560
561 struct NewCustomer {
562 email: String,
563 active: bool,
564 }
565
566 impl Insertable<Customer> for NewCustomer {
567 fn values(&self) -> Vec<ColumnValue> {
568 vec![
569 ColumnValue::new("email", SqlValue::String(self.email.clone())),
570 ColumnValue::new("active", SqlValue::Bool(self.active)),
571 ]
572 }
573 }
574
575 struct UpdateCustomer {
576 email: Option<String>,
577 active: Option<bool>,
578 }
579
580 impl Changeset<Customer> for UpdateCustomer {
581 fn changes(&self) -> Vec<ColumnValue> {
582 let mut changes = Vec::new();
583
584 if let Some(email) = &self.email {
585 changes.push(ColumnValue::new("email", SqlValue::String(email.clone())));
586 }
587
588 if let Some(active) = self.active {
589 changes.push(ColumnValue::new("active", SqlValue::Bool(active)));
590 }
591
592 changes
593 }
594 }
595
596 #[test]
597 fn compiles_select_with_predicates_order_and_pagination() {
598 let query = SelectQuery::from_entity::<Customer>()
599 .select(vec![Expr::from(Customer::id), Expr::from(Customer::email)])
600 .filter(Predicate::eq(
601 Expr::from(Customer::active),
602 Expr::value(SqlValue::Bool(true)),
603 ))
604 .filter(Predicate::like(
605 Expr::from(Customer::email),
606 Expr::value(SqlValue::String("%@example.com".to_string())),
607 ))
608 .order_by(OrderBy::desc(Customer::created_at))
609 .paginate(Pagination::page(2, 20));
610
611 let compiled = SqlServerCompiler::compile_select(&query).unwrap();
612
613 assert_eq!(
614 compiled.sql,
615 "SELECT [sales].[customers].[id] AS [id], [sales].[customers].[email] AS [email] FROM [sales].[customers] WHERE (([sales].[customers].[active] = @P1) AND ([sales].[customers].[email] LIKE @P2)) ORDER BY [sales].[customers].[created_at] DESC OFFSET @P3 ROWS FETCH NEXT @P4 ROWS ONLY"
616 );
617 assert_eq!(
618 compiled.params,
619 vec![
620 SqlValue::Bool(true),
621 SqlValue::String("%@example.com".to_string()),
622 SqlValue::I64(20),
623 SqlValue::I64(20),
624 ]
625 );
626 }
627
628 #[test]
629 fn compiles_select_without_projection_as_star() {
630 let compiled =
631 SqlServerCompiler::compile_select(&SelectQuery::from_entity::<Customer>()).unwrap();
632
633 assert_eq!(compiled.sql, "SELECT * FROM [sales].[customers]");
634 assert!(compiled.params.is_empty());
635 }
636
637 #[test]
638 fn rejects_pagination_without_order_by() {
639 let error = SqlServerCompiler::compile_select(
640 &SelectQuery::from_entity::<Customer>().paginate(Pagination::page(1, 10)),
641 )
642 .unwrap_err();
643
644 assert_eq!(
645 error.message(),
646 "SQL Server pagination requires ORDER BY before OFFSET/FETCH"
647 );
648 }
649
650 #[test]
651 fn compiles_explicit_joins_to_sql() {
652 let query = SelectQuery::from_entity::<Customer>()
653 .select(vec![
654 Expr::from(Customer::email),
655 Expr::from(Order::total_cents),
656 ])
657 .inner_join::<Order>(Predicate::eq(
658 Expr::from(Customer::id),
659 Expr::from(Order::customer_id),
660 ))
661 .filter(Predicate::gt(
662 Expr::from(Order::total_cents),
663 Expr::value(SqlValue::I64(1000)),
664 ))
665 .order_by(OrderBy::desc(Order::total_cents))
666 .paginate(Pagination::page(1, 10));
667
668 let compiled = SqlServerCompiler::compile_select(&query).unwrap();
669
670 assert_eq!(
671 compiled.sql,
672 "SELECT [sales].[customers].[email] AS [email], [sales].[orders].[total_cents] AS [total_cents] FROM [sales].[customers] INNER JOIN [sales].[orders] ON ([sales].[customers].[id] = [sales].[orders].[customer_id]) WHERE ([sales].[orders].[total_cents] > @P1) ORDER BY [sales].[orders].[total_cents] DESC OFFSET @P2 ROWS FETCH NEXT @P3 ROWS ONLY"
673 );
674 assert_eq!(
675 compiled.params,
676 vec![SqlValue::I64(1000), SqlValue::I64(0), SqlValue::I64(10)]
677 );
678 }
679
680 #[test]
681 fn rejects_duplicate_unaliased_joined_tables() {
682 let error = SqlServerCompiler::compile_select(
683 &SelectQuery::from_entity::<Customer>().inner_join::<Customer>(Predicate::eq(
684 Expr::from(Customer::id),
685 Expr::from(Customer::id),
686 )),
687 )
688 .unwrap_err();
689
690 assert_eq!(
691 error.message(),
692 "SQL Server join compilation requires aliases for repeated table sources"
693 );
694 }
695
696 #[test]
697 fn compiles_aliased_selects_with_repeated_joined_tables() {
698 let query = SelectQuery::from_entity_as::<Customer>("c")
699 .select(vec![
700 Expr::column_as(Customer::email, "c"),
701 Expr::column_as(Order::total_cents, "created_orders"),
702 ])
703 .inner_join_as::<Order>(
704 "created_orders",
705 Predicate::eq(
706 Expr::column_as(Customer::id, "c"),
707 Expr::column_as(Order::customer_id, "created_orders"),
708 ),
709 )
710 .left_join_as::<Order>(
711 "completed_orders",
712 Predicate::gte(
713 Expr::column_as(Order::total_cents, "completed_orders"),
714 Expr::value(SqlValue::I64(5000)),
715 ),
716 )
717 .filter(Predicate::gt(
718 Expr::column_as(Order::total_cents, "created_orders"),
719 Expr::value(SqlValue::I64(1000)),
720 ))
721 .order_by(OrderBy::new(
722 TableRef::for_entity_as::<Order>("completed_orders"),
723 "total_cents",
724 sql_orm_query::SortDirection::Desc,
725 ))
726 .paginate(Pagination::page(1, 10));
727
728 let compiled = SqlServerCompiler::compile_select(&query).unwrap();
729
730 assert_eq!(
731 compiled.sql,
732 "SELECT [c].[email] AS [email], [created_orders].[total_cents] AS [total_cents] FROM [sales].[customers] AS [c] INNER JOIN [sales].[orders] AS [created_orders] ON ([c].[id] = [created_orders].[customer_id]) LEFT JOIN [sales].[orders] AS [completed_orders] ON ([completed_orders].[total_cents] >= @P1) WHERE ([created_orders].[total_cents] > @P2) ORDER BY [completed_orders].[total_cents] DESC OFFSET @P3 ROWS FETCH NEXT @P4 ROWS ONLY"
733 );
734 assert_eq!(
735 compiled.params,
736 vec![
737 SqlValue::I64(5000),
738 SqlValue::I64(1000),
739 SqlValue::I64(0),
740 SqlValue::I64(10),
741 ]
742 );
743 }
744
745 #[test]
746 fn compiles_aliased_count_query() {
747 let query = CountQuery::from_entity_as::<Customer>("c").filter(Predicate::eq(
748 Expr::column_as(Customer::active, "c"),
749 Expr::value(SqlValue::Bool(true)),
750 ));
751
752 let compiled = SqlServerCompiler::compile_count(&query).unwrap();
753
754 assert_eq!(
755 compiled.sql,
756 "SELECT COUNT(*) AS [count] FROM [sales].[customers] AS [c] WHERE ([c].[active] = @P1)"
757 );
758 assert_eq!(compiled.params, vec![SqlValue::Bool(true)]);
759 }
760
761 #[test]
762 fn rejects_empty_table_aliases() {
763 let error = SqlServerCompiler::compile_select(
764 &SelectQuery::from_entity_as::<Customer>("").inner_join_as::<Order>(
765 "o",
766 Predicate::eq(
767 Expr::column_as(Customer::id, ""),
768 Expr::column_as(Order::customer_id, "o"),
769 ),
770 ),
771 )
772 .unwrap_err();
773
774 assert_eq!(error.message(), "SQL Server identifier cannot be empty");
775 }
776
777 #[test]
778 fn compiles_insert_with_output_inserted_and_parameter_order() {
779 let query = InsertQuery::for_entity::<Customer, _>(&NewCustomer {
780 email: "ana@example.com".to_string(),
781 active: true,
782 });
783
784 let compiled = SqlServerCompiler::compile_insert(&query).unwrap();
785
786 assert_eq!(
787 compiled.sql,
788 "INSERT INTO [sales].[customers] ([email], [active]) OUTPUT INSERTED.* VALUES (@P1, @P2)"
789 );
790 assert_eq!(
791 compiled.params,
792 vec![
793 SqlValue::String("ana@example.com".to_string()),
794 SqlValue::Bool(true),
795 ]
796 );
797 }
798
799 #[test]
800 fn compiles_update_with_output_inserted_and_where_clause() {
801 let query = UpdateQuery::for_entity::<Customer, _>(&UpdateCustomer {
802 email: Some("ana.maria@example.com".to_string()),
803 active: Some(false),
804 })
805 .filter(Predicate::eq(
806 Expr::from(Customer::id),
807 Expr::value(SqlValue::I64(7)),
808 ));
809
810 let compiled = SqlServerCompiler::compile_update(&query).unwrap();
811
812 assert_eq!(
813 compiled.sql,
814 "UPDATE [sales].[customers] SET [email] = @P1, [active] = @P2 OUTPUT INSERTED.* WHERE ([sales].[customers].[id] = @P3)"
815 );
816 assert_eq!(
817 compiled.params,
818 vec![
819 SqlValue::String("ana.maria@example.com".to_string()),
820 SqlValue::Bool(false),
821 SqlValue::I64(7),
822 ]
823 );
824 }
825
826 #[test]
827 fn compiles_delete_and_count_queries() {
828 let delete = DeleteQuery::from_entity::<Customer>().filter(Predicate::eq(
829 Expr::from(Customer::id),
830 Expr::value(SqlValue::I64(7)),
831 ));
832 let count = CountQuery::from_entity::<Customer>().filter(Predicate::eq(
833 Expr::from(Customer::active),
834 Expr::value(SqlValue::Bool(true)),
835 ));
836
837 let compiled_delete = SqlServerCompiler::compile_delete(&delete).unwrap();
838 let compiled_count = SqlServerCompiler::compile_count(&count).unwrap();
839
840 assert_eq!(
841 compiled_delete.sql,
842 "DELETE FROM [sales].[customers] WHERE ([sales].[customers].[id] = @P1)"
843 );
844 assert_eq!(compiled_delete.params, vec![SqlValue::I64(7)]);
845 assert_eq!(
846 compiled_count.sql,
847 "SELECT COUNT(*) AS [count] FROM [sales].[customers] WHERE ([sales].[customers].[active] = @P1)"
848 );
849 assert_eq!(compiled_count.params, vec![SqlValue::Bool(true)]);
850 }
851
852 #[test]
853 fn compiles_query_enum_through_single_entry_point() {
854 let query = Query::Count(CountQuery::from_entity::<Customer>().filter(Predicate::eq(
855 Expr::from(Customer::active),
856 Expr::value(SqlValue::Bool(true)),
857 )));
858
859 let compiled = SqlServerCompiler::compile_query(&query).unwrap();
860
861 assert_eq!(
862 compiled.sql,
863 "SELECT COUNT(*) AS [count] FROM [sales].[customers] WHERE ([sales].[customers].[active] = @P1)"
864 );
865 assert_eq!(compiled.params, vec![SqlValue::Bool(true)]);
866 }
867
868 #[test]
869 fn compiles_functions_null_checks_and_unary_binary_exprs() {
870 let query = SelectQuery {
871 from: TableRef::new("sales", "customers"),
872 joins: vec![],
873 projection: vec![SelectProjection::expr_as(
874 Expr::function(
875 "LOWER",
876 vec![Expr::binary(
877 Expr::from(Customer::email),
878 BinaryOp::Add,
879 Expr::value(SqlValue::String("@example.com".to_string())),
880 )],
881 ),
882 "email_lower",
883 )],
884 predicate: Some(Predicate::and(vec![
885 Predicate::is_not_null(Expr::from(Customer::email)),
886 Predicate::negate(Predicate::is_null(Expr::unary(
887 UnaryOp::Negate,
888 Expr::value(SqlValue::I64(1)),
889 ))),
890 ])),
891 order_by: vec![],
892 pagination: None,
893 };
894
895 let compiled = SqlServerCompiler::compile_select(&query).unwrap();
896
897 assert_eq!(
898 compiled.sql,
899 "SELECT LOWER(([sales].[customers].[email] + @P1)) AS [email_lower] FROM [sales].[customers] WHERE (([sales].[customers].[email] IS NOT NULL) AND (NOT ((- @P2) IS NULL)))"
900 );
901 assert_eq!(
902 compiled.params,
903 vec![
904 SqlValue::String("@example.com".to_string()),
905 SqlValue::I64(1),
906 ]
907 );
908 }
909
910 #[test]
911 fn rejects_projection_expression_without_alias() {
912 let error = SqlServerCompiler::compile_select(
913 &SelectQuery::from_entity::<Customer>().select(vec![SelectProjection::expr(
914 Expr::function("LOWER", vec![Expr::from(Customer::email)]),
915 )]),
916 )
917 .unwrap_err();
918
919 assert_eq!(
920 error.message(),
921 "SQL Server projection expressions require an explicit alias"
922 );
923 }
924
925 #[test]
926 fn rejects_empty_or_duplicate_projection_aliases() {
927 let empty_alias_error =
928 SqlServerCompiler::compile_select(&SelectQuery::from_entity::<Customer>().select(
929 vec![SelectProjection::expr_as(Expr::from(Customer::email), "")],
930 ))
931 .unwrap_err();
932
933 assert_eq!(
934 empty_alias_error.message(),
935 "SQL Server projection alias cannot be empty"
936 );
937
938 let duplicate_alias_error = SqlServerCompiler::compile_select(
939 &SelectQuery::from_entity::<Customer>().select(vec![
940 SelectProjection::column(Customer::id),
941 SelectProjection::expr_as(Expr::from(Customer::email), "id"),
942 ]),
943 )
944 .unwrap_err();
945
946 assert_eq!(
947 duplicate_alias_error.message(),
948 "SQL Server projection alias `id` is duplicated"
949 );
950 }
951
952 #[test]
953 fn rejects_empty_updates_and_empty_logical_predicates() {
954 let empty_update = UpdateQuery::for_entity::<Customer, _>(&UpdateCustomer {
955 email: None,
956 active: None,
957 });
958 let update_error = SqlServerCompiler::compile_update(&empty_update).unwrap_err();
959
960 assert_eq!(
961 update_error.message(),
962 "SQL Server update compilation requires at least one change"
963 );
964
965 let predicate_error = SqlServerCompiler::compile_select(
966 &SelectQuery::from_entity::<Customer>().filter(Predicate::and(vec![])),
967 )
968 .unwrap_err();
969
970 assert_eq!(
971 predicate_error.message(),
972 "logical predicate compilation requires at least one child predicate"
973 );
974 }
975}