drizzle 0.1.7

//! PostgreSQL condition expression tests

#![cfg(any(feature = "postgres-sync", feature = "tokio-postgres"))]

use crate::common::schema::postgres::*;
use drizzle::core::expr::*;
use drizzle::postgres::prelude::*;

#[allow(dead_code)]
#[cfg(feature = "uuid")]
#[derive(Debug, PostgresFromRow)]
struct PgComplexResult {
    id: uuid::Uuid,
    name: String,
    email: Option<String>,
    age: Option<i32>,
    active: bool,
}

#[drizzle::test]
fn condition_eq(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let stmt = db
        .insert(simple)
        .values([InsertSimple::new("Alice"), InsertSimple::new("Bob")]);
    stmt.execute();

    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(eq(simple.name, "Alice"));
    let results: Vec<SelectSimple> = stmt.all();

    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "Alice");
}

#[drizzle::test]
fn condition_neq(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let stmt = db
        .insert(simple)
        .values([InsertSimple::new("Alice"), InsertSimple::new("Bob")]);
    stmt.execute();

    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(neq(simple.name, "Alice"));
    let results: Vec<SelectSimple> = stmt.all();

    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "Bob");
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn condition_gt_lt(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let stmt = db.insert(complex).values([
        InsertComplex::new("Young", true, Role::User).with_age(20),
        InsertComplex::new("Middle", true, Role::User).with_age(40),
        InsertComplex::new("Senior", true, Role::User).with_age(60),
    ]);
    stmt.execute();

    // Test gt
    let stmt = db.select(()).from(complex).r#where(gt(complex.age, 30));
    let results: Vec<PgComplexResult> = stmt.all();
    assert_eq!(results.len(), 2);

    // Test lt
    let stmt = db.select(()).from(complex).r#where(lt(complex.age, 50));
    let results: Vec<PgComplexResult> = stmt.all();
    assert_eq!(results.len(), 2);

    // Test gte
    let stmt = db.select(()).from(complex).r#where(gte(complex.age, 40));
    let results: Vec<PgComplexResult> = stmt.all();
    assert_eq!(results.len(), 2);

    // Test lte
    let stmt = db.select(()).from(complex).r#where(lte(complex.age, 40));
    let results: Vec<PgComplexResult> = stmt.all();
    assert_eq!(results.len(), 2);
}

#[drizzle::test]
fn condition_in_array(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let stmt = db.insert(simple).values([
        InsertSimple::new("Alice"),
        InsertSimple::new("Bob"),
        InsertSimple::new("Charlie"),
        InsertSimple::new("David"),
    ]);
    stmt.execute();

    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(in_array(simple.name, ["Alice", "Charlie"]));
    let results: Vec<SelectSimple> = stmt.all();

    assert_eq!(results.len(), 2);
    let names: Vec<&str> = results.iter().map(|r| r.name.as_str()).collect();
    assert!(names.contains(&"Alice"));
    assert!(names.contains(&"Charlie"));
}

#[drizzle::test]
fn condition_not_in_array(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let stmt = db.insert(simple).values([
        InsertSimple::new("Alice"),
        InsertSimple::new("Bob"),
        InsertSimple::new("Charlie"),
    ]);
    stmt.execute();

    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(not_in_array(simple.name, ["Alice"]));
    let results: Vec<SelectSimple> = stmt.all();

    assert_eq!(results.len(), 2);
    let names: Vec<&str> = results.iter().map(|r| r.name.as_str()).collect();
    assert!(names.contains(&"Bob"));
    assert!(names.contains(&"Charlie"));
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn condition_is_null(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    // Separate inserts due to type state differences
    let stmt = db.insert(complex).values([
        InsertComplex::new("With Email", true, Role::User).with_email("test@example.com")
    ]);
    stmt.execute();

    let stmt = db
        .insert(complex)
        .values([InsertComplex::new("No Email", true, Role::User)]);
    stmt.execute();

    let stmt = db.select(()).from(complex).r#where(is_null(complex.email));
    let results: Vec<PgComplexResult> = stmt.all();

    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "No Email");
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn condition_is_not_null(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    // Separate inserts due to type state differences
    let stmt = db.insert(complex).values([
        InsertComplex::new("With Email", true, Role::User).with_email("test@example.com")
    ]);
    stmt.execute();

    let stmt = db
        .insert(complex)
        .values([InsertComplex::new("No Email", true, Role::User)]);
    stmt.execute();

    let stmt = db
        .select(())
        .from(complex)
        .r#where(is_not_null(complex.email));
    let results: Vec<PgComplexResult> = stmt.all();

    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "With Email");
}

#[drizzle::test]
fn condition_like(db: &mut TestDb<SimpleSchema>) {
    let SimpleSchema { simple } = schema;

    let stmt = db.insert(simple).values([
        InsertSimple::new("test_one"),
        InsertSimple::new("test_two"),
        InsertSimple::new("other"),
    ]);
    stmt.execute();

    // Prefix match
    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(like(simple.name, "test%"));
    let results: Vec<SelectSimple> = stmt.all();
    assert_eq!(results.len(), 2);

    // Contains match
    let stmt = db
        .select((simple.id, simple.name))
        .from(simple)
        .r#where(like(simple.name, "%o%"));
    let results: Vec<SelectSimple> = stmt.all();
    assert_eq!(results.len(), 3); // test_one, test_two, other all contain 'o'
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn condition_between(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let stmt = db.insert(complex).values([
        InsertComplex::new("Teen", true, Role::User).with_age(15),
        InsertComplex::new("Young", true, Role::User).with_age(25),
        InsertComplex::new("Adult", true, Role::User).with_age(45),
        InsertComplex::new("Senior", true, Role::User).with_age(75),
    ]);
    stmt.execute();

    let stmt = db
        .select(())
        .from(complex)
        .r#where(between(complex.age, 20, 50));
    let results: Vec<PgComplexResult> = stmt.all();

    assert_eq!(results.len(), 2);
    let names: Vec<&str> = results.iter().map(|r| r.name.as_str()).collect();
    assert!(names.contains(&"Young"));
    assert!(names.contains(&"Adult"));
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn condition_and(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let stmt = db.insert(complex).values([
        InsertComplex::new("Active Young", true, Role::User).with_age(25),
        InsertComplex::new("Inactive Young", false, Role::User).with_age(25),
        InsertComplex::new("Active Old", true, Role::User).with_age(60),
    ]);
    stmt.execute();

    let stmt = db
        .select(())
        .from(complex)
        .r#where(and(eq(complex.active, true), lt(complex.age, 30)));
    let results: Vec<PgComplexResult> = stmt.all();

    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "Active Young");
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn condition_or(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let stmt = db.insert(complex).values([
        InsertComplex::new("Admin", true, Role::Admin),
        InsertComplex::new("Moderator", true, Role::Moderator),
        InsertComplex::new("User", true, Role::User),
    ]);
    stmt.execute();

    let stmt = db.select(()).from(complex).r#where(or(
        eq(complex.role, Role::Admin),
        eq(complex.role, Role::Moderator),
    ));
    let results: Vec<PgComplexResult> = stmt.all();

    assert_eq!(results.len(), 2);
    let names: Vec<&str> = results.iter().map(|r| r.name.as_str()).collect();
    assert!(names.contains(&"Admin"));
    assert!(names.contains(&"Moderator"));
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn condition_nested_and_or(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let stmt = db.insert(complex).values([
        InsertComplex::new("Active Admin", true, Role::Admin).with_age(30),
        InsertComplex::new("Inactive Admin", false, Role::Admin).with_age(30),
        InsertComplex::new("Active User Young", true, Role::User).with_age(20),
        InsertComplex::new("Active User Old", true, Role::User).with_age(50),
    ]);
    stmt.execute();

    // (Admin OR Moderator) AND active AND age > 25
    let stmt = db.select(()).from(complex).r#where(and(
        or(
            eq(complex.role, Role::Admin),
            eq(complex.role, Role::Moderator),
        ),
        and(eq(complex.active, true), gt(complex.age, 25)),
    ));
    let results: Vec<PgComplexResult> = stmt.all();

    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "Active Admin");
}

#[cfg(feature = "uuid")]
#[drizzle::test]
fn condition_not(db: &mut TestDb<ComplexSchema>) {
    let ComplexSchema { complex, .. } = schema;

    let stmt = db.insert(complex).values([
        InsertComplex::new("Active", true, Role::User),
        InsertComplex::new("Inactive", false, Role::User),
    ]);
    stmt.execute();

    let stmt = db
        .select(())
        .from(complex)
        .r#where(not(eq(complex.active, true)));
    let results: Vec<PgComplexResult> = stmt.all();

    assert_eq!(results.len(), 1);
    assert_eq!(results[0].name, "Inactive");
}