saola-quaint 0.2.0-alpha.14

An abstraction layer for SQL databases (PostgreSQL, MySQL, SQLite, MSSQL)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134

use super::test_api::*;
use crate::{connector::Queryable, prelude::*};
use quaint_test_macros::test_each_connector;

#[test_each_connector(tags("postgresql", "sqlite"))]
async fn upsert_on_primary_key(api: &mut dyn TestApi) -> crate::Result<()> {
    let table = api.create_temp_table("id int primary key, x int").await?;

    // Insert
    let count = api.conn().execute(upsert_on_primary_key_query(&table)).await?;

    assert_eq!(count, 1);

    let select = Select::from_table(&table);
    let row = api.conn().select(select.clone()).await?.into_single()?;

    assert_eq!(Some(1), row["id"].as_i32());
    assert_eq!(Some(1), row["x"].as_i32());

    // // Update
    let count = api.conn().execute(upsert_on_primary_key_query(&table)).await?;
    assert_eq!(count, 1);

    let row = api.conn().select(select).await?.into_single()?;

    assert_eq!(Some(1), row["id"].as_i32());
    assert_eq!(Some(2), row["x"].as_i32());

    Ok(())
}

fn upsert_on_primary_key_query(table: &str) -> Query<'_> {
    let update = Update::table(table).set("x", 2).so_that((table, "id").equals(1));

    let insert: Insert = Insert::single_into(table).value("id", 1).value("x", 1).into();

    insert
        .on_conflict(OnConflict::Update(update, Vec::from(["id".into()])))
        .into()
}

#[test_each_connector(tags("postgresql", "sqlite"))]
async fn upsert_on_unique_field(api: &mut dyn TestApi) -> crate::Result<()> {
    let table = api.create_temp_table("id int primary key, x int UNIQUE, y int").await?;

    // Insert
    let count = api.conn().execute(upsert_on_unique_field_query(&table)).await?;

    assert_eq!(count, 1);

    let select = Select::from_table(&table);
    let row = api.conn().select(select.clone()).await?.into_single()?;

    assert_eq!(Some(1), row["id"].as_i32());
    assert_eq!(Some(1), row["x"].as_i32());
    assert_eq!(Some(1), row["y"].as_i32());

    // Update
    let count = api.conn().execute(upsert_on_unique_field_query(&table)).await?;
    assert_eq!(count, 1);

    let row = api.conn().select(select).await?.into_single()?;

    assert_eq!(Some(1), row["id"].as_i32());
    assert_eq!(Some(1), row["x"].as_i32());
    assert_eq!(Some(2), row["y"].as_i32());

    Ok(())
}

fn upsert_on_unique_field_query(table: &str) -> Query<'_> {
    let update = Update::table(table).set("y", 2).so_that((table, "id").equals(1));

    let insert: Insert = Insert::single_into(table)
        .value("id", 1)
        .value("x", 1)
        .value("y", 1)
        .into();

    insert
        .on_conflict(OnConflict::Update(update, Vec::from(["x".into()])))
        .into()
}

#[test_each_connector(tags("postgresql", "sqlite"))]
async fn upsert_on_multiple_unique_fields(api: &mut dyn TestApi) -> crate::Result<()> {
    let table = api
        .create_temp_table("id int primary key, x int, y int, CONSTRAINT ux_x_y UNIQUE (x, y)")
        .await?;

    // Insert
    let count = api
        .conn()
        .execute(upsert_on_multiple_unique_fields_query(&table))
        .await?;

    assert_eq!(count, 1);

    let select = Select::from_table(&table);
    let row = api.conn().select(select.clone()).await?.into_single()?;

    assert_eq!(Some(1), row["id"].as_i32());
    assert_eq!(Some(1), row["x"].as_i32());
    assert_eq!(Some(1), row["y"].as_i32());

    // Update
    let count = api
        .conn()
        .execute(upsert_on_multiple_unique_fields_query(&table))
        .await?;
    assert_eq!(count, 1);

    let row = api.conn().select(select).await?.into_single()?;

    assert_eq!(Some(1), row["id"].as_i32());
    assert_eq!(Some(1), row["x"].as_i32());
    assert_eq!(Some(2), row["y"].as_i32());

    Ok(())
}

fn upsert_on_multiple_unique_fields_query(table: &str) -> Query<'_> {
    let update = Update::table(table).set("y", 2).so_that((table, "id").equals(1));

    let insert: Insert = Insert::single_into(table)
        .value("id", 1)
        .value("x", 1)
        .value("y", 1)
        .into();

    insert
        .on_conflict(OnConflict::Update(update, Vec::from(["x".into(), "y".into()])))
        .into()
}