use mysql::*;
use mysql::prelude::*;

#[derive(Debug, PartialEq, Eq)]
struct Payment {
    customer_id: i32,
    amount: i32,
    account_name: Option<String>,
}

let url = "mysql://root:password@localhost:3307/db_name";

let pool = Pool::new(url)?;

let mut conn = pool.get_conn()?;

// Let's create a table for payments.
conn.query_drop(
    r"CREATE TEMPORARY TABLE payment (
        customer_id int not null,
        amount int not null,
        account_name text
    )")?;

let payments = vec![
    Payment { customer_id: 1, amount: 2, account_name: None },
    Payment { customer_id: 3, amount: 4, account_name: Some("foo".into()) },
    Payment { customer_id: 5, amount: 6, account_name: None },
    Payment { customer_id: 7, amount: 8, account_name: None },
    Payment { customer_id: 9, amount: 10, account_name: Some("bar".into()) },
];

// Now let's insert payments to the database
conn.exec_batch(
    r"INSERT INTO payment (customer_id, amount, account_name)
      VALUES (:customer_id, :amount, :account_name)",
    payments.iter().map(|p| params! {
        "customer_id" => p.customer_id,
        "amount" => p.amount,
        "account_name" => &p.account_name,
    })
)?;

// Let's select payments from database. Type inference should do the trick here.
let selected_payments = conn
    .query_map(
        "SELECT customer_id, amount, account_name from payment",
        |(customer_id, amount, account_name)| {
            Payment { customer_id, amount, account_name }
        },
    )?;

// Let's make sure, that `payments` equals to `selected_payments`.
// Mysql gives no guaranties on order of returned rows
// without `ORDER BY`, so assume we are lucky.
assert_eq!(payments, selected_payments);
println!("Yay!");

let _ = Opts::from_url("mysql://localhost/some_db")?;
let _ = Opts::from_url("mysql://[::1]/some_db")?;
let _ = Opts::from_url("mysql://user:pass%[email protected]:3307/some_db?")?;

let opts = OptsBuilder::new()
    .user(Some("foo"))
    .db_name(Some("bar"));
let _ = Conn::new(opts)?;

use mysql::*;
use mysql::prelude::*;

let pool = Pool::new(get_opts())?;
let mut conn = pool.get_conn()?;

let mut tx = conn.start_transaction(TxOpts::default())?;
tx.query_drop("CREATE TEMPORARY TABLE tmp (TEXT a)")?;
tx.exec_drop("INSERT INTO tmp (a) VALUES (?)", ("foo",))?;
let val: Option<String> = tx.query_first("SELECT a from tmp")?;
assert_eq!(val.unwrap(), "foo");
// Note, that transaction will be rolled back implicitly on Drop, if not committed.
tx.rollback();

let val: Option<String> = conn.query_first("SELECT a from tmp")?;
assert_eq!(val, None);

use mysql::*;
use mysql::prelude::*;

use std::thread::spawn;

let pool = Pool::new(get_opts())?;

let handles = (0..4).map(|i| {
    spawn({
        let pool = pool.clone();
        move || {
            let mut conn = pool.get_conn()?;
            conn.exec_first::<u32, _, _>("SELECT ? * 10", (i,))
                .map(Option::unwrap)
        }
    })
});

let result: Result<Vec<u32>> = handles.map(|handle| handle.join().unwrap()).collect();

assert_eq!(result.unwrap(), vec![0, 10, 20, 30]);

use mysql::*;
use mysql::prelude::*;

let pool = Pool::new(get_opts())?;
let mut conn = pool.get_conn()?;

let stmt = conn.prep("DO ?")?;

// The prepared statement will return no columns.
assert!(stmt.columns().is_empty());

// The prepared statement have one parameter.
let param = stmt.params().get(0).unwrap();
assert_eq!(param.schema_str(), "");
assert_eq!(param.table_str(), "");
assert_eq!(param.name_str(), "?");

use mysql::*;
use mysql::prelude::*;

let via_test_protocol: u32 = from_value(Value::Bytes(b"65536".to_vec()));
let via_bin_protocol: u32 = from_value(Value::UInt(65536));
assert_eq!(via_test_protocol, via_bin_protocol);

let unknown_val = // ...

// Maybe it is a float?
let unknown_val = match from_value_opt::<f64>(unknown_val) {
    Ok(float) => {
        println!("A float value: {}", float);
        return Ok(());
    }
    Err(FromValueError(unknown_val)) => unknown_val,
};

// Or a string?
let unknown_val = match from_value_opt::<String>(unknown_val) {
    Ok(string) => {
        println!("A string value: {}", string);
        return Ok(());
    }
    Err(FromValueError(unknown_val)) => unknown_val,
};

// Screw this, I'll simply match on it
match unknown_val {
    val @ Value::NULL => {
        println!("An empty value: {:?}", from_value::<Option<u8>>(val))
    },
    val @ Value::Bytes(..) => {
        // It's non-utf8 bytes, since we already tried to convert it to String
        println!("Bytes: {:?}", from_value::<Vec<u8>>(val))
    }
    val @ Value::Int(..) => {
        println!("A signed integer: {}", from_value::<i64>(val))
    }
    val @ Value::UInt(..) => {
        println!("An unsigned integer: {}", from_value::<u64>(val))
    }
    Value::Float(..) => unreachable!("already tried"),
    val @ Value::Double(..) => {
        println!("A double precision float value: {}", from_value::<f64>(val))
    }
    val @ Value::Date(..) => {
        use mysql::chrono::NaiveDateTime;
        println!("A date value: {}", from_value::<NaiveDateTime>(val))
    }
    val @ Value::Time(..) => {
        use std::time::Duration;
        println!("A time value: {:?}", from_value::<Duration>(val))
    }
}

use mysql::*;
use mysql::prelude::*;

let mut conn = Conn::new(get_opts())?;

// Single-column row can be converted to a singular value:
let val: Option<String> = conn.query_first("SELECT 'foo'")?;
assert_eq!(val.unwrap(), "foo");

// Example of a mutli-column row conversion to an inferred type:
let row = conn.query_first("SELECT 255, 256")?;
assert_eq!(row, Some((255u8, 256u16)));

// The FromRow trait does not support to-tuple conversion for rows with more than 12 columns,
// but you can do this by hand using row indexing or `Row::take` method:
let row: Row = conn.exec_first("select 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12", ())?.unwrap();
for i in 0..row.len() {
    assert_eq!(row[i], Value::Int(i as i64));
}

// Some unknown row
let row: Row = conn.query_first(
    // ...
    # "SELECT 255, Null",
)?.unwrap();

for column in row.columns_ref() {
    // Cells in a row can be indexed by numeric index or by column name
    let column_value = &row[column.name_str().as_ref()];

    println!(
        "Column {} of type {:?} with value {:?}",
        column.name_str(),
        column.column_type(),
        column_value,
    );
}

use mysql::*;
use mysql::prelude::*;

let mut conn = Conn::new(get_opts())?;

// Singular tuple requires extra comma:
let row: Option<u8> = conn.exec_first("SELECT ?", (0,))?;
assert_eq!(row.unwrap(), 0);

// More than 12 parameters:
let row: Option<u8> = conn.exec_first(
    "SELECT CONVERT(? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ? + ?, UNSIGNED)",
    (0..16).collect::<Vec<_>>(),
)?;
assert_eq!(row.unwrap(), 120);

use mysql::*;
use mysql::prelude::*;

/// Serializable structure.
#[derive(Debug, PartialEq, Serialize, Deserialize)]
struct Example {
    foo: u32,
}

// Value::from for Serialized will emit json string.
let value = Value::from(Serialized(Example { foo: 42 }));
assert_eq!(value, Value::Bytes(br#"{"foo":42}"#.to_vec()));

// from_value for Deserialized will parse json string.
let structure: Deserialized<Example> = from_value(value);
assert_eq!(structure, Deserialized(Example { foo: 42 }));

use mysql::*;
use mysql::prelude::*;

let mut conn = Conn::new(get_opts())?;

// This query will emit two result sets.
let mut result = conn.query_iter("SELECT 1, 2; SELECT 3, 3.14;")?;

let mut sets = 0;
while let Some(result_set) = result.next_set() {
    let result_set = result_set?;
    sets += 1;

    println!("Result set columns: {:?}", result_set.columns());
    println!(
        "Result set meta: {}, {:?}, {} {}",
        result_set.affected_rows(),
        result_set.last_insert_id(),
        result_set.warnings(),
        result_set.info_str(),
    );

    for row in result_set {
        match sets {
            1 => {
                // First result set will contain two numbers.
                assert_eq!((1_u8, 2_u8), from_row(row?));
            }
            2 => {
                // Second result set will contain a number and a float.
                assert_eq!((3_u8, 3.14), from_row(row?));
            }
            _ => unreachable!(),
        }
    }
}

assert_eq!(sets, 2);

let pool = Pool::new(get_opts())?;
let val = pool.get_conn()?.query_first("SELECT POW(2, 16)")?;

// Text protocol returns bytes even though the result of POW
// is actually a floating point number.
assert_eq!(val, Some(Value::Bytes("65536".as_bytes().to_vec())));

use mysql::*;
use mysql::prelude::*;

fn iter(pool: &Pool) -> Result<impl Iterator<Item=Result<u32>>> {
    let result = "SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3".run(pool)?;
    Ok(result.map(|row| row.map(from_row)))
}

let pool = Pool::new(get_opts())?;

let it = iter(&pool)?;

assert_eq!(it.collect::<Result<Vec<u32>>>()?, vec![1, 2, 3]);

let pool = Pool::new(get_opts())?;
let val = pool.get_conn()?.exec_first("SELECT POW(?, ?)", (2, 16))?;

assert_eq!(val, Some(Value::Double(65536.0)));

let pool = Pool::new(get_opts())?;

let mut conn_1 = pool.get_conn()?;
let mut conn_2 = pool.get_conn()?;

let stmt_1 = conn_1.prep("SELECT ?")?;

// stmt_1 is for the conn_1, ..
assert!(stmt_1.connection_id() == conn_1.connection_id());
assert!(stmt_1.connection_id() != conn_2.connection_id());

// .. so stmt_1 will execute only on conn_1
assert!(conn_1.exec_drop(&stmt_1, ("foo",)).is_ok());
assert!(conn_2.exec_drop(&stmt_1, ("foo",)).is_err());

let pool = Pool::new(get_opts())?;

let mut conn = pool.get_conn()?;
let stmt = conn.prep("SELECT :foo, :bar, :foo")?;

let foo = 42;

let val_13 = conn.exec_first(&stmt, params! { "foo" => 13, "bar" => foo })?.unwrap();
// Short syntax is available when param name is the same as variable name:
let val_42 = conn.exec_first(&stmt, params! { foo, "bar" => 13 })?.unwrap();

assert_eq!((foo, 13, foo), val_42);
assert_eq!((13, foo, 13), val_13);

use mysql::*;
use mysql::prelude::*;

let pool = Pool::new(get_opts())?;

let result: Option<(u8, u8, u8)> = "SELECT ?, ?, ?"
    .with((1, 2, 3)) // <- WithParams::with will construct an instance of QueryWithParams
    .first(&pool)?;  // <- QueryWithParams is executed on the given pool

assert_eq!(result.unwrap(), (1, 2, 3));

use mysql::*;
use mysql::prelude::*;

let pool = Pool::new(get_opts())?;
let mut conn = pool.get_conn()?;

"CREATE TEMPORARY TABLE batch (x INT)".run(&mut conn)?;
"INSERT INTO batch (x) VALUES (?)"
    .with((0..3).map(|x| (x,))) // <- QueryWithParams constructed with an iterator
    .batch(&mut conn)?;         // <- batch execution is preformed here

let result: Vec<u8> = "SELECT x FROM batch".fetch(conn)?;

assert_eq!(result, vec![0, 1, 2]);