[package]
name = "rusqlite"
version = "0.1.0"
edition = "2021"

# See more keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

[dependencies]
[dependencies.rusqlite]
version = "*"
features = ["bundled"]

// ユーザー
user {
    id: number
    name: string
    password: string
}

// ユーザが投稿した記事的なもの
post {
    id: number
    user_id: number
    title: string
    body: string
}

let conn = Connection::open("app.db").unwrap();

conn.execute(
    "CREATE TABLE IF NOT EXISTS user (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        username STRING UNIQUE NOT NULL,
        password STRING NOT NULL
    )",
    ()
).unwrap();
conn.execute(
    "CREATE TABLE IF NOT EXISTS post (
        id      INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        title   STRING,
        body    STRING,
        FOREIGN KEY(user_id) REFERENCES user (id)
    )",
    ()
).unwrap();

conn.execute(
    "INSERT INTO user (username, password) VALUES (?1, ?2)",
    ["osaka", "24"]
).unwrap();

let mut stmt = conn.prepare("INSERT INTO user (username, password) VALUES (?1, ?2)").unwrap();
stmt.execute(["apple", "42"]).unwrap();
/*
  この前に出てきたprepareを使わないコードを実行している場合既にosakaは存在しているのでエラーが出るよ
  でも下の行が無かったら分かりにくいから一応追加してるよ
*/
stmt.execute(["osaka", "24"]).unwrap();

let mut stmt = conn.prepare("INSERT INTO post (user_id, title, body) VALUES (?1, ?2, ?3)").unwrap();
stmt.execute([&1.to_string(), "なぜリンゴはニュートンの前で落ちたのか", "調べた結果分かりませんでした、今後のリンゴの活躍に期待ですね"]).unwrap();
stmt.execute([&1.to_string(), "神はなぜリンゴを知恵の実にしたのか", "旧約聖書にそうした記述はありませんでした、今後のリンゴの活躍に期待ですね"]).unwrap();
stmt.execute([&2.to_string(), "このタイトルに1時間くらい考えた", "一応これも考えたけどappleと違ってosakaはネタが少ないし十分なエビデンスが得られなかったからこれでいく"]).unwrap();

#[derive(Debug)]
struct Post {
    id: i32,
    user_id: i32,
    title: String,
    body: String
}

let post = conn.query_row("
    SELECT id, user_id, title, body FROM post WHERE id = ?1
    ", [&1.to_string()], |row| {
    Ok(Post {
        id: row.get(0)?,
        user_id: row.get(1)?,
        title: row.get(2)?,
        body: row.get(3)?
    })
}).unwrap();
println!("{:#?}", post);

let mut stmt = conn.prepare("SELECT id, user_id, title, body FROM post WHERE user_id == ?1").unwrap();
let posts = stmt.query_map([&1.to_string()], |row| {
    Ok(Post {
        id: row.get(0)?,
        user_id: row.get(1)?,
        title: row.get(2)?,
        body: row.get(3)?
    })
}).unwrap();

let mut posts_vec = Vec::new();
for item in posts {
    if let Ok(item) = item {
        posts_vec.push(item)
    }
}
println!("{:#?}", posts_vec);

conn.execute("UPDATE user SET password = ?1 WHERE id = ?2", ["BigAdminIsWatchingYou", &2.to_string()]).unwrap();

conn.execute("DELETE FROM post WHERE id = ?1", [&3.to_string()]).unwrap();

thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: SqliteFailure(Error { code: ConstraintViolation, extended_code: 787 }, Some("FOREIGN KEY constraint failed"))', src\main.rs:100:70
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
error: process didn't exit successfully: `target\debug\rusqlite.exe` (exit code: 101)

conn.execute("DELETE FROM post WHERE user_id = ?1", [&2.to_string()]).unwrap();
conn.execute("DELETE FROM user WHERE id = ?1", [&2.to_string()]).unwrap();

thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: SqliteFailure(Error { code: ConstraintViolation, extended_code: 2067 }, Some("UNIQUE constraint failed: user.username"))', src\main.rs:42:35
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
error: process didn't exit successfully: `target\debug\rusqlite.exe` (exit code: 101)

CREATE TABLE IF NOT EXISTS user (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    username STRING UNIQUE NOT NULL,
    password STRING NOT NULL
)

let mut stmt = conn.prepare("INSERT INTO user (username, password) VALUES (?1, ?2)").unwrap();
stmt.execute(["apple", "042"]).unwrap();