Lines
100 %
Functions
50 %
Branches
68.77 %
#[cfg(feature = "mysql")]
use crate::constants::MYSQL_DS;
#[cfg(feature = "mssql")]
use crate::constants::SQL_SERVER_DS;
// Tests for the QueryBuilder available operations within Canyon.
///
// QueryBuilder are the way of obtain more flexibility that with
// the default generated queries, essentially for build the queries
// with the SQL filters
use canyon_sql::{
crud::CrudOperations,
query::{operators::Comp, operators::Like, ops::QueryBuilder},
};
use crate::tests_models::league::*;
use crate::tests_models::player::*;
use crate::tests_models::tournament::*;
/// Builds a new SQL statement for retrieves entities of the `T` type, filtered
/// with the parameters that modifies the base SQL to SELECT * FROM <entity>
#[canyon_sql::macros::canyon_tokio_test]
fn test_generated_sql_by_the_select_querybuilder() {
let mut select_with_joins = League::select_query();
select_with_joins
.inner_join("tournament", "league.id", "tournament.league_id")
.left_join("team", "tournament.id", "player.tournament_id")
.r#where(LeagueFieldValue::id(&7), Comp::Gt)
.and(LeagueFieldValue::name(&"KOREA"), Comp::Eq)
.and_values_in(LeagueField::name, &["LCK", "STRANGER THINGS"]);
// .query()
// .await;
// NOTE: We don't have in the docker the generated relationships
// with the joins, so for now, we are just going to check that the
// generated SQL by the SelectQueryBuilder<T> is the spected
assert_eq!(
select_with_joins.read_sql(),
"SELECT * FROM league INNER JOIN tournament ON league.id = tournament.league_id LEFT JOIN team ON tournament.id = player.tournament_id WHERE id > $1 AND name = $2 AND name IN ($2, $3)"
)
}
#[cfg(feature = "postgres")]
fn test_crud_find_with_querybuilder() {
// Find all the leagues with ID less or equals that 7
// and where it's region column value is equals to 'Korea'
let filtered_leagues_result: Result<Vec<League>, _> = League::select_query()
.r#where(LeagueFieldValue::id(&50), Comp::LtEq)
.and(LeagueFieldValue::region(&"KOREA"), Comp::Eq)
.query()
.await;
let filtered_leagues: Vec<League> = filtered_leagues_result.unwrap();
assert!(!filtered_leagues.is_empty());
let league_idx_0 = filtered_leagues.get(0).unwrap();
assert_eq!(league_idx_0.id, 34);
assert_eq!(league_idx_0.region, "KOREA");
fn test_crud_find_with_querybuilder_and_fulllike() {
// Find all the leagues with "LC" in their name
let mut filtered_leagues_result = League::select_query();
filtered_leagues_result.r#where(LeagueFieldValue::name(&"LC"), Like::Full);
filtered_leagues_result.read_sql(),
"SELECT * FROM league WHERE name LIKE CONCAT('%', CAST($1 AS VARCHAR) ,'%')"
fn test_crud_find_with_querybuilder_and_fulllike_datasource_mssql() {
let mut filtered_leagues_result = League::select_query_datasource(SQL_SERVER_DS);
fn test_crud_find_with_querybuilder_and_fulllike_datasource_mysql() {
let mut filtered_leagues_result = League::select_query_datasource(MYSQL_DS);
"SELECT * FROM league WHERE name LIKE CONCAT('%', CAST($1 AS CHAR) ,'%')"
fn test_crud_find_with_querybuilder_and_leftlike() {
// Find all the leagues whose name ends with "CK"
filtered_leagues_result.r#where(LeagueFieldValue::name(&"CK"), Like::Left);
"SELECT * FROM league WHERE name LIKE CONCAT('%', CAST($1 AS VARCHAR))"
fn test_crud_find_with_querybuilder_and_leftlike_datasource_mssql() {
fn test_crud_find_with_querybuilder_and_leftlike_datasource_mysql() {
"SELECT * FROM league WHERE name LIKE CONCAT('%', CAST($1 AS CHAR))"
fn test_crud_find_with_querybuilder_and_rightlike() {
// Find all the leagues whose name starts with "LC"
filtered_leagues_result.r#where(LeagueFieldValue::name(&"LC"), Like::Right);
"SELECT * FROM league WHERE name LIKE CONCAT(CAST($1 AS VARCHAR) ,'%')"
fn test_crud_find_with_querybuilder_and_rightlike_datasource_mssql() {
fn test_crud_find_with_querybuilder_and_rightlike_datasource_mysql() {
"SELECT * FROM league WHERE name LIKE CONCAT(CAST($1 AS CHAR) ,'%')"
/// Same than the above but with the specified datasource
fn test_crud_find_with_querybuilder_datasource_mssql() {
// Find all the players where its ID column value is greater that 50
let filtered_find_players = Player::select_query_datasource(SQL_SERVER_DS)
.r#where(PlayerFieldValue::id(&50), Comp::Gt)
assert!(!filtered_find_players.unwrap().is_empty());
fn test_crud_find_with_querybuilder_datasource_mysql() {
let filtered_find_players = Player::select_query_datasource(MYSQL_DS)
/// Updates the values of the range on entries defined by the constraint parameters
/// in the database entity
fn test_crud_update_with_querybuilder() {
let mut q = League::update_query();
q.set(&[
(LeagueField::slug, "Updated with the QueryBuilder"),
(LeagueField::name, "Random"),
])
.r#where(LeagueFieldValue::id(&1), Comp::Gt)
.and(LeagueFieldValue::id(&8), Comp::Lt);
/* NOTE: Family of QueryBuilders are clone, useful in case of need to read the generated SQL
let qpr = q.clone();
println!("PSQL: {:?}", qpr.read_sql());
*/
// We can now back to the original an throw the query
q.query()
.await
.expect("Failed to update records with the querybuilder");
let found_updated_values = League::select_query()
.and(LeagueFieldValue::id(&7), Comp::Lt)
.expect("Failed to retrieve database League entries with the querybuilder");
found_updated_values
.iter()
.for_each(|league| assert_eq!(league.slug, "Updated with the QueryBuilder"));
/// Same as above, but with the specified datasource
fn test_crud_update_with_querybuilder_datasource_mssql() {
let mut q = Player::update_query_datasource(SQL_SERVER_DS);
(PlayerField::summoner_name, "Random updated player name"),
(PlayerField::first_name, "I am an updated first name"),
.r#where(PlayerFieldValue::id(&1), Comp::Gt)
.and(PlayerFieldValue::id(&8), Comp::Lt)
let found_updated_values = Player::select_query_datasource(SQL_SERVER_DS)
.and(PlayerFieldValue::id(&7), Comp::LtEq)
found_updated_values.iter().for_each(|player| {
assert_eq!(player.summoner_name, "Random updated player name");
assert_eq!(player.first_name, "I am an updated first name");
});
fn test_crud_update_with_querybuilder_datasource_mysql() {
let mut q = Player::update_query_datasource(MYSQL_DS);
let found_updated_values = Player::select_query_datasource(MYSQL_DS)
/// Deletes entries from the mapped entity `T` that are in the ranges filtered
/// with the QueryBuilder
/// Note if the database is persisted (not created and destroyed on every docker or
/// GitHub Action wake up), it won't delete things that already have been deleted,
/// but this isn't an error. They just don't exists.
fn test_crud_delete_with_querybuilder() {
Tournament::delete_query()
.r#where(TournamentFieldValue::id(&14), Comp::Gt)
.and(TournamentFieldValue::id(&16), Comp::Lt)
.expect("Error connecting with the database on the delete operation");
assert_eq!(Tournament::find_by_pk(&15).await.unwrap(), None);
/// Same as the above delete, but with the specified datasource
fn test_crud_delete_with_querybuilder_datasource_mssql() {
Player::delete_query_datasource(SQL_SERVER_DS)
.r#where(PlayerFieldValue::id(&120), Comp::Gt)
.and(PlayerFieldValue::id(&130), Comp::Lt)
.expect("Error connecting with the database when we are going to delete data! :)");
assert!(Player::select_query_datasource(SQL_SERVER_DS)
.r#where(PlayerFieldValue::id(&122), Comp::Eq)
.unwrap()
.is_empty());
fn test_crud_delete_with_querybuilder_datasource_mysql() {
Player::delete_query_datasource(MYSQL_DS)
assert!(Player::select_query_datasource(MYSQL_DS)
/// Tests for the generated SQL query after use the
/// WHERE clause
fn test_where_clause() {
let mut l = League::select_query();
l.r#where(LeagueFieldValue::name(&"LEC"), Comp::Eq);
assert_eq!(l.read_sql(), "SELECT * FROM league WHERE name = $1")
/// AND clause
fn test_and_clause() {
l.r#where(LeagueFieldValue::name(&"LEC"), Comp::Eq)
.and(LeagueFieldValue::id(&10), Comp::LtEq);
l.read_sql().trim(),
"SELECT * FROM league WHERE name = $1 AND id <= $2"
fn test_and_clause_with_in_constraint() {
.and_values_in(LeagueField::id, &[1, 7, 10]);
"SELECT * FROM league WHERE name = $1 AND id IN ($1, $2, $3)"
fn test_or_clause() {
.or(LeagueFieldValue::id(&10), Comp::LtEq);
"SELECT * FROM league WHERE name = $1 OR id <= $2"
fn test_or_clause_with_in_constraint() {
.or_values_in(LeagueField::id, &[1, 7, 10]);
l.read_sql(),
"SELECT * FROM league WHERE name = $1 OR id IN ($1, $2, $3)"
fn test_order_by_clause() {
.order_by(LeagueField::id, false);
"SELECT * FROM league WHERE name = $1 ORDER BY id"