musq 0.0.4

Musq is an asynchronous SQLite toolkit for Rust.
Documentation 
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
135
136
137

//! Integration tests for modern bundled SQLite SQL features.

mod support;

#[cfg(test)]
mod tests {
    use std::fmt::Debug;

    use musq::{Result as MusqResult, SqliteError, error::ExtendedErrCode, query, query_scalar};

    use crate::support::connection;

    #[tokio::test]
    async fn alter_table_constraint_changes_work() -> anyhow::Result<()> {
        let conn = connection().await?;

        query(
            "CREATE TABLE modern_users( \
                 id INTEGER PRIMARY KEY, \
                 name TEXT, \
                 age INTEGER \
             )",
        )
        .execute(&conn)
        .await?;
        query("INSERT INTO modern_users(id, name, age) VALUES (1, 'alice', 41)")
            .execute(&conn)
            .await?;

        query("ALTER TABLE modern_users ALTER COLUMN name SET NOT NULL")
            .execute(&conn)
            .await?;
        query("ALTER TABLE modern_users ALTER COLUMN name SET NOT NULL")
            .execute(&conn)
            .await?;

        let not_null_error = sqlite_error(
            query("INSERT INTO modern_users(id, name, age) VALUES (2, NULL, 10)")
                .execute(&conn)
                .await,
        );
        assert_eq!(not_null_error.extended, ExtendedErrCode::ConstraintNotNull);

        query("ALTER TABLE modern_users ALTER COLUMN name DROP NOT NULL")
            .execute(&conn)
            .await?;
        query("INSERT INTO modern_users(id, name, age) VALUES (2, NULL, 10)")
            .execute(&conn)
            .await?;
        let null_name_count: i64 =
            query_scalar("SELECT COUNT(*) FROM modern_users WHERE name IS NULL")
                .fetch_one(&conn)
                .await?;
        assert_eq!(null_name_count, 1);

        query("ALTER TABLE modern_users ADD CONSTRAINT age_nonnegative CHECK (age >= 0)")
            .execute(&conn)
            .await?;

        let check_error = sqlite_error(
            query("INSERT INTO modern_users(id, name, age) VALUES (3, 'bob', -1)")
                .execute(&conn)
                .await,
        );
        assert_eq!(check_error.extended, ExtendedErrCode::ConstraintCheck);
        assert!(check_error.message.contains("age_nonnegative"));

        query("ALTER TABLE modern_users DROP CONSTRAINT age_nonnegative")
            .execute(&conn)
            .await?;
        query("INSERT INTO modern_users(id, name, age) VALUES (3, 'bob', -1)")
            .execute(&conn)
            .await?;
        let negative_age_count: i64 =
            query_scalar("SELECT COUNT(*) FROM modern_users WHERE age < 0")
                .fetch_one(&conn)
                .await?;
        assert_eq!(negative_age_count, 1);

        Ok(())
    }

    #[tokio::test]
    async fn reindex_expressions_accepts_expression_indexes() -> anyhow::Result<()> {
        let conn = connection().await?;

        query("CREATE TABLE expression_items(id INTEGER PRIMARY KEY, name TEXT)")
            .execute(&conn)
            .await?;
        query("CREATE INDEX expression_items_lower_name ON expression_items(lower(name))")
            .execute(&conn)
            .await?;
        query("INSERT INTO expression_items(id, name) VALUES (1, 'Alice')")
            .execute(&conn)
            .await?;

        query("REINDEX EXPRESSIONS").execute(&conn).await?;

        let id: i64 =
            query_scalar("SELECT id FROM expression_items WHERE lower(name) = lower('alice')")
                .fetch_one(&conn)
                .await?;
        assert_eq!(id, 1);

        Ok(())
    }

    #[tokio::test]
    async fn json_array_insert_functions_round_trip() -> anyhow::Result<()> {
        let conn = connection().await?;

        let json_text: String = query_scalar("SELECT json_array_insert(?, ?, ?)")
            .bind("[1,2,3]")
            .bind("$[1]")
            .bind("new")
            .fetch_one(&conn)
            .await?;
        assert_eq!(json_text, r#"[1,"new",2,3]"#);

        let jsonb_text: String = query_scalar("SELECT json(jsonb_array_insert(jsonb(?), ?, ?))")
            .bind("[1,2,3]")
            .bind("$[1]")
            .bind("new")
            .fetch_one(&conn)
            .await?;
        assert_eq!(jsonb_text, r#"[1,"new",2,3]"#);

        Ok(())
    }

    fn sqlite_error<T>(result: MusqResult<T>) -> SqliteError
    where
        T: Debug,
    {
        result.unwrap_err().into_sqlite_error().unwrap()
    }
}