dialtone_sqlx 0.1.0

Dialtone SQLx Back-End
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

# dialtone_sqlx

The sqlx crate that integrates a dialtone server with PostgreSQL.

### About dialtone

Dialtone is an un-imagined Activity Pub system. What does "un-imagined" mean? Basically, there is no intent to silo this software into a specific vertical "marketplace". Instead, emergent use cases will be addressed.

This software is under active, initial development and is far from feature complete or ready for use.

### License

Licensed under either of

 * Apache License, Version 2.0 ([LICENSE-APACHE](LICENSE-APACHE) or http://www.apache.org/licenses/LICENSE-2.0)
 * MIT license ([LICENSE-MIT](LICENSE-MIT) or http://opensource.org/licenses/MIT)
  at your option.

### Contribution

Unless you explicitly state otherwise, any contribution intentionally
submitted for inclusion in the work by you, as defined in the
Apache-2.0 license, shall be dual licensed as above, without any
additional terms or conditions.


## Some notes....

Did you know you can embed your migrations in your application binary?
On startup, after creating your database connection or pool, add:

sqlx::migrate!().run(<&your_pool OR &mut your_connection>).await?;

Note that the compiler won't pick up new migrations if no Rust source files have changed.
You can create a Cargo build script to work around this with `sqlx migrate build-script`.

See: https://docs.rs/sqlx/0.5/sqlx/macro.migrate.html

## Implementation Notes

Test table:

```postgresql
create table user_test (
                                acct varchar not null primary key, -- user_name@host_name
                                login_data jsonb
);
```

```postgresql
insert into user_test (acct, login_data) 
values ('test', '[]'::jsonb );
```

this will update the json data but cap the number of entries in the
array to the last 5.

````postgresql
update user_test 
set login_data = jsonb_path_query_array('"foo"'::jsonb || login_data, '$[0 to 4]')
where acct = 'test';
````

Checking for valid login while logging the attempt with a CTE:

```postgresql
with pcheck as (
  select exists (
    select true from user_principal
    where
      acct = 'test8@test4.example'
    and
      status = 'Active'
    and
      (auth_data->'password_auth'->'bcrypt_password'->>'crypted_password') = 
        '$2b$12$x6ot7Gd6mvQBZF1p6iWbROyRIABByIBSwGbOkcRP8NHTSpI8S42Ou'
  ) as success
)
update user_principal
  set last_login_data = 
    jsonb_path_query_array(
      jsonb_build_object('from', '$FROM', 'at', '$AT', 'success', pcheck.success)
        || last_login_data, '$[0 to 10]')
  from pcheck
  where
    acct = 'test8@test4.example'
  returning last_login_data;
```