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 or http://www.apache.org/licenses/LICENSE-2.0)
• MIT license (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:

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

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.

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:

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;