sqly 0.5.0

use anyhow::{anyhow, Result};
use sqly::derive::*;

/*

Limited support for dynamic SQL is provided through the
#[sqly(optional)] attribute. The documentation further
explains the usage, details and a few caveats.

An optional field is only included in the generated query
if its runtime value resolves to an `Option::Some`,
otherwise it will behave as if #[sqly(skip)] was applied.

This enables optional insert, update and filter expressions
for queries, and is particularly useful in the case of
Option<Option<_>> types as well as some inserts.

*/

#[derive(Insert)]
#[sqly(table = "c33", dynamic)]
struct C33Insert {
    first_name: &'static str,
    last_name: &'static str,
    #[sqly(optional)]
    full_name: Option<&'static str>,
}

async fn c33_insert(obj: C33Insert, db: &sqlx::PgPool) -> Result<()> {
    // due to sqlx limitations the query must be constructed as:
    let (sql, args) = obj.insert_sql();
    let query = C33Insert::insert_from((&sql, args));
    // let query = obj.insert(); // instead of this
    query.execute(db).await?;
    Ok(())
}

#[derive(Update)]
#[sqly(table = "c33", dynamic, optional)]
struct C33Update {
    #[sqly(key)]
    id: i32,
    mother: Option<Option<i32>>,
    father: Option<Option<i32>>,
}

async fn c33_update(obj: C33Update, db: &sqlx::PgPool) -> Result<()> {
    // an option is returned since all update values are optional
    if let Some((sql, args)) = obj.update_sql() {
        // only perform the update if any fields are being changed
        let query = C33Update::update_from((&sql, args));
        query.execute(db).await?;
        Ok(())
    } else {
        Err(anyhow!("cannot perform update without fields"))
    }
}

/*

Alternatives to consider:
- Optional update
  #[sqly(update = "COALESCE($i, $column)")]
- Optional filter
  #[sqly(filter = "$i IS NULL OR $column = $i")]
These are often a far better solution.

When to use #[sqly(optional)]:
- Optional insert DEFAULT
  #[sqly(insert = "COALESCE($i, DEFAULT)")] is unfortunately not valid SQL
- Option<Option<_>> types
  SQL cannot make a distinction between None and Some(None), both are NULL
In these cases dynamic SQL might be required.

*/

#[test]
fn c33_optional_args() {
    let obj = C33Insert {
        first_name: "one",
        last_name: "two",
        full_name: None, // do not insert a full_name, it can be generated by SQL
    };
    let (sql, args) = obj.insert_sql();
    assert_eq!(
        sql,
        r#"
INSERT INTO c33 AS "self" (
	"first_name",
	"last_name"
) VALUES (
	$1,
	$2
)
	"#
        .trim_ascii()
    );

    let obj = C33Insert {
        first_name: "one",
        last_name: "two",
        full_name: Some("twelve"), // insert a custom full_name instead
    };
    let (sql, args) = obj.insert_sql();
    assert_eq!(
        sql,
        r#"
INSERT INTO c33 AS "self" (
	"first_name",
	"last_name",
	"full_name"
) VALUES (
	$1,
	$2,
	$3
)
	"#
        .trim_ascii()
    );

    let obj = C33Update {
        id: 1,
        mother: None, // do not update the mother
        father: None, // do not update the father
    };
    // cannot generate a query which does not update any fields
    assert!(obj.update_sql().is_none());

    let obj = C33Update {
        id: 1,
        mother: None,            // do not update the mother
        father: Some(Some(123)), // set the father
    };
    let (sql, args) = obj.update_sql().unwrap();
    assert_eq!(
        sql,
        r#"
UPDATE c33 AS "self"
SET
	"father" = $1
WHERE
	"id" = $2
	"#
        .trim_ascii()
    );

    let obj = C33Update {
        id: 1,
        mother: Some(Some(321)), // set the mother
        father: Some(None),      // set the father to NULL
    };
    let (sql, args) = obj.update_sql().unwrap();
    assert_eq!(
        sql,
        r#"
UPDATE c33 AS "self"
SET
	"mother" = $1,
	"father" = $2
WHERE
	"id" = $3
	"#
        .trim_ascii()
    );
}