(oauth2 + email added): a mess that may or may not really work and needs to be refactored...

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;
 --