switchy_database 0.5.0

Switchy database package
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

#![cfg(all(feature = "sqlite-rusqlite", feature = "schema"))]

use std::sync::Arc;
use switchy_database::{
    Database,
    query::{JoinCondition, qualified_column},
    rusqlite::RusqliteDatabase,
    schema::{Column, DataType},
};

#[tokio::test]
async fn typed_join_preserves_equality_and_left_join_semantics() {
    let connection = rusqlite::Connection::open_in_memory().unwrap();
    let db = RusqliteDatabase::new(vec![Arc::new(switchy_async::sync::Mutex::new(connection))]);
    for table in ["parents", "children"] {
        db.create_table(table)
            .column(Column {
                name: "id".into(),
                data_type: DataType::BigInt,
                nullable: false,
                auto_increment: false,
                default: None,
            })
            .execute(&db)
            .await
            .unwrap();
    }
    for id in [1, 2] {
        db.insert("parents")
            .value("id", id)
            .execute(&db)
            .await
            .unwrap();
    }
    db.insert("children")
        .value("id", 1)
        .execute(&db)
        .await
        .unwrap();
    for (left, expected) in [(false, 1), (true, 2)] {
        let condition = (
            qualified_column("parents", "id"),
            qualified_column("children", "id"),
        );
        let query = db.select("parents");
        let query = if left {
            query.left_join("children", condition)
        } else {
            query.join("children", condition)
        };
        let rows = query.execute(&db).await.unwrap();
        assert_eq!(rows.len(), expected);
    }
    // A second ON condition must not filter unmatched parents out of a LEFT JOIN.
    let rows = db
        .select("parents")
        .left_join(
            "children",
            JoinCondition::And(vec![
                (
                    qualified_column("parents", "id"),
                    qualified_column("children", "id"),
                )
                    .into(),
                (
                    qualified_column("parents", "id"),
                    qualified_column("children", "rowid"),
                )
                    .into(),
            ]),
        )
        .execute(&db)
        .await
        .unwrap();
    assert_eq!(rows.len(), 2);
    for name in ["id OR 1=1 --", "id\" = 1 OR 1=1 --", "children.id"] {
        assert!(
            db.select("parents")
                .join(
                    "children",
                    (
                        qualified_column("parents", "id"),
                        qualified_column("children", name),
                    ),
                )
                .execute(&db)
                .await
                .is_err()
        );
    }
    for table in ["children ON 1=1 --", "children\" ON 1=1 --"] {
        assert!(
            db.select("parents")
                .left_join(
                    table,
                    (
                        qualified_column("parents", "id"),
                        qualified_column("children", "id"),
                    ),
                )
                .execute(&db)
                .await
                .is_err()
        );
    }
    db.close().await.unwrap();
}