diff options
Diffstat (limited to 'crates/secd/store/sqlite/sql')
14 files changed, 180 insertions, 48 deletions
diff --git a/crates/secd/store/sqlite/sql/find_email_validation.sql b/crates/secd/store/sqlite/sql/find_email_validation.sql index a34c149..d7f311c 100644 --- a/crates/secd/store/sqlite/sql/find_email_validation.sql +++ b/crates/secd/store/sqlite/sql/find_email_validation.sql @@ -2,15 +2,17 @@ select ev.email_validation_public_id , i.identity_public_id , e.address - , ev.attempts , ev.code - , ev.is_validated + , ev.is_oauth_derived , ev.created_at - , ev.expires_at - , ev.revoked_at -from email_validation ev -join identity_email ie using (identity_email_id) -join email e using (email_id) -join identity i using (identity_id) + , ev.expired_at + , ev.validated_at + , iev.revoked_at + , iev.deleted_at +from email_validation ev +join email e using (email_id) +left join identity_email_validation iev using (email_validation_id) +left join identity i using (identity_id) where ((?1 is null) or (email_validation_public_id = ?1)) and ((?2 is null) or (code = ?2)); +-- diff --git a/crates/secd/store/sqlite/sql/find_identity.sql b/crates/secd/store/sqlite/sql/find_identity.sql index bd1654d..f94e7b1 100644 --- a/crates/secd/store/sqlite/sql/find_identity.sql +++ b/crates/secd/store/sqlite/sql/find_identity.sql @@ -1,9 +1,11 @@ select - identity_public_id, - data, - i.created_at + identity_public_id + , data + , i.created_at + , i.deleted_at from identity i -join identity_email ie using (identity_id) -join email e using (email_id) +join identity_email_validation iev using (identity_id) +join email_validation ev using (email_validation_id) +join email e using (email_id) where ((?1 is null) or (i.identity_public_id = ?1)) -and ((?2 is null) or (e.address = ?2)) +and ((?2 is null) or (e.address = ?2)); diff --git a/crates/secd/store/sqlite/sql/find_identity_by_code.sql b/crates/secd/store/sqlite/sql/find_identity_by_code.sql index 77844ff..b70a13a 100644 --- a/crates/secd/store/sqlite/sql/find_identity_by_code.sql +++ b/crates/secd/store/sqlite/sql/find_identity_by_code.sql @@ -1,11 +1,11 @@ -select identity_email_id -from secd.email_validation -where email_validation_public_id = ?1; +select identity_email_validation_id +from email_validation +where email_validation_public_id = $1::uuid -- select identity_public_id , data , i.created_at -from secd.identity i -left join secd.identity_email ie using (identity_id) -where ie.identity_email_id = ?1; +from identity i +left join identity_email ie using (identity_id) +where ie.identity_email_validation_id = ?1; diff --git a/crates/secd/store/sqlite/sql/read_email_raw_id.sql b/crates/secd/store/sqlite/sql/read_email_raw_id.sql index 0bbafad..a65c717 100644 --- a/crates/secd/store/sqlite/sql/read_email_raw_id.sql +++ b/crates/secd/store/sqlite/sql/read_email_raw_id.sql @@ -1 +1 @@ -select email_id from email where address = ? +select email_id from email where address = ?1 diff --git a/crates/secd/store/sqlite/sql/read_identity_raw_id.sql b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql index 552c570..2bdb718 100644 --- a/crates/secd/store/sqlite/sql/read_identity_raw_id.sql +++ b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql @@ -1,2 +1,2 @@ -select identity_id from identity where identity_public_id = ?; +select identity_id from identity where identity_public_id = ?1; -- diff --git a/crates/secd/store/sqlite/sql/read_oauth_provider.sql b/crates/secd/store/sqlite/sql/read_oauth_provider.sql new file mode 100644 index 0000000..5c33cf0 --- /dev/null +++ b/crates/secd/store/sqlite/sql/read_oauth_provider.sql @@ -0,0 +1,12 @@ +select flow + , base_url + , response_type + , default_scope + , client_id + , client_secret + , redirect_url + , created_at + , deleted_at +from oauth_provider +where name = ?1 +and flow = ?2; diff --git a/crates/secd/store/sqlite/sql/read_oauth_validation.sql b/crates/secd/store/sqlite/sql/read_oauth_validation.sql new file mode 100644 index 0000000..75f5a94 --- /dev/null +++ b/crates/secd/store/sqlite/sql/read_oauth_validation.sql @@ -0,0 +1,23 @@ +select oauth_validation_public_id + , i.identity_public_id + , ov.access_token + , ov.raw_response + , ov.created_at + , ov.validated_at + , iov.revoked_at + , iov.deleted_at + , op.name as oauth_provider_name + , op.flow as oauth_provider_flow + , op.base_url as oauth_provider_base_url + , op.response_type as oauth_provider_response_type + , op.default_scope as oauth_provider_default_scope + , op.client_id as oauth_provider_client_id + , op.client_secret as oauth_provider_client_secret + , op.redirect_url as oauth_provider_redirect_url + , op.created_at as oauth_provider_created_at + , op.deleted_at as oauth_provider_deleted_at +from oauth_validation ov +join oauth_provider op using(oauth_provider_id) +left join identity_oauth_validation iov using(oauth_validation_id) +left join identity i using(identity_id) +where oauth_validation_public_id = ?1; diff --git a/crates/secd/store/sqlite/sql/read_validation_type.sql b/crates/secd/store/sqlite/sql/read_validation_type.sql new file mode 100644 index 0000000..cc02ead --- /dev/null +++ b/crates/secd/store/sqlite/sql/read_validation_type.sql @@ -0,0 +1,7 @@ +select 'Email' +from email_validation +where email_validation_public_id = ?1 +union +select 'Oauth' +from oauth_validation +where oauth_validation_public_id = ?1; diff --git a/crates/secd/store/sqlite/sql/write_email.sql b/crates/secd/store/sqlite/sql/write_email.sql index c127d9c..a64aed4 100644 --- a/crates/secd/store/sqlite/sql/write_email.sql +++ b/crates/secd/store/sqlite/sql/write_email.sql @@ -1,11 +1,6 @@ insert into email ( address ) values ( - ?1 + $1 ) on conflict (address) do nothing returning email_id; --- -select email_id from email where email = ?1; --- -insert into identity_email (identity_id, email_id, created_at) values (?1, ?2, ?3); --- diff --git a/crates/secd/store/sqlite/sql/write_email_validation.sql b/crates/secd/store/sqlite/sql/write_email_validation.sql index 37b13e1..d839310 100644 --- a/crates/secd/store/sqlite/sql/write_email_validation.sql +++ b/crates/secd/store/sqlite/sql/write_email_validation.sql @@ -1,27 +1,43 @@ insert into email_validation ( email_validation_public_id - , identity_email_id - , attempts + , email_id , code - , is_validated + , is_oauth_derived , created_at - , expires_at + , validated_at + , expired_at ) values ( ?1 - , ( - select identity_email_id - from identity_email - where identity_id = ?2 - and email_id = ?3 - ) + , ?2 + , ?3 , ?4 , ?5 , ?6 , ?7 - , ?8 ) on conflict (email_validation_public_id) do update - set attempts = excluded.attempts - , is_validated = excluded.is_validated - , expires_at = excluded.expires_at; + set validated_at = excluded.validated_at + , expired_at = excluded.expired_at; +-- +insert into identity_email_validation ( + identity_id + , email_validation_id + , revoked_at + , deleted_at +) values ( + ( + select identity_id + from identity + where identity_public_id = ?1 + ) + , ( + select email_validation_id + from email_validation + where email_validation_public_id = ?2 + ) + , ?3 + , ?4 +) on conflict (identity_id, email_validation_id) do update + set revoked_at = excluded.revoked_at + , deleted_at = excluded.deleted_at; diff --git a/crates/secd/store/sqlite/sql/write_identity.sql b/crates/secd/store/sqlite/sql/write_identity.sql index ff54468..8cf46c5 100644 --- a/crates/secd/store/sqlite/sql/write_identity.sql +++ b/crates/secd/store/sqlite/sql/write_identity.sql @@ -1 +1,11 @@ -insert into identity (identity_public_id, data, created_at) values (?1, ?2, ?3); +insert into identity ( + identity_public_id, + data, + created_at +) values ( + ?1, + ?2, + ?3 +) on conflict(identity_public_id) do update + set data = excluded.data + , deleted_at = excluded.deleted_at; diff --git a/crates/secd/store/sqlite/sql/write_oauth_provider.sql b/crates/secd/store/sqlite/sql/write_oauth_provider.sql new file mode 100644 index 0000000..421caf7 --- /dev/null +++ b/crates/secd/store/sqlite/sql/write_oauth_provider.sql @@ -0,0 +1,23 @@ +insert into oauth_provider ( + name + , flow + , base_url + , response_type + , default_scope + , client_id + , client_secret + , redirect_url + , created_at + , deleted_at +) values ( + ?1 + , ?2 + , ?3 + , ?4 + , ?5 + , ?6 + , ?7 + , ?8 + , ?9 + , ?10 +) on conflict (name, flow) do nothing; diff --git a/crates/secd/store/sqlite/sql/write_oauth_validation.sql b/crates/secd/store/sqlite/sql/write_oauth_validation.sql new file mode 100644 index 0000000..ccb11aa --- /dev/null +++ b/crates/secd/store/sqlite/sql/write_oauth_validation.sql @@ -0,0 +1,45 @@ +insert into oauth_validation ( + oauth_validation_public_id + , oauth_provider_id + , access_token + , raw_response + , created_at + , validated_at +) values ( + ?1 + , ( + select oauth_provider_id + from oauth_provider + where name = ?2 + and flow = ?3 + ) + , ?4 + , ?5 + , ?6 + , ?7 +) on conflict (oauth_validation_public_id) do update + set access_token = excluded.access_token + , validated_at = excluded.validated_at + , raw_response = excluded.raw_response; +-- +insert into identity_oauth_validation ( + identity_id + , oauth_validation_id + , revoked_at + , deleted_at +) values ( + ( + select identity_id + from identity + where identity_public_id = ?1 + ) + , ( + select oauth_validation_id + from oauth_validation + where oauth_validation_public_id = ?2 + ) + , ?3 + , ?4 +) on conflict (identity_id, oauth_validation_id) do update + set revoked_at = excluded.revoked_at + , deleted_at = excluded.deleted_at; diff --git a/crates/secd/store/sqlite/sql/write_session.sql b/crates/secd/store/sqlite/sql/write_session.sql index 3c26986..480af54 100644 --- a/crates/secd/store/sqlite/sql/write_session.sql +++ b/crates/secd/store/sqlite/sql/write_session.sql @@ -2,8 +2,7 @@ insert into session ( identity_id , secret_hash , created_at - , touched_at - , expires_at + , expired_at , revoked_at ) values ( (select identity_id from identity where identity_public_id = ?1) @@ -11,8 +10,6 @@ insert into session ( , ?3 , ?4 , ?5 - , ?6 ) on conflict (secret_hash) do update - set touched_at = excluded.touched_at - , revoked_at = excluded.revoked_at; + set revoked_at = excluded.revoked_at; -- |