- basic functionality with psql and sqlite

- cli helper tool

11 files changed, 104 insertions, 0 deletions

diff --git a/crates/secd/store/sqlite/sql/find_email_validation.sql b/crates/secd/store/sqlite/sql/find_email_validation.sql
new file mode 100644
index 0000000..a34c149
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_email_validation.sql
@@ -0,0 +1,16 @@
+select
+        ev.email_validation_public_id
+        , i.identity_public_id
+        , e.address
+        , ev.attempts
+        , ev.code
+        , ev.is_validated
+        , 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)
+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
new file mode 100644
index 0000000..bd1654d
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_identity.sql
@@ -0,0 +1,9 @@
+select
+        identity_public_id,
+        data,
+        i.created_at
+from identity       i
+join identity_email ie using (identity_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))
diff --git a/crates/secd/store/sqlite/sql/find_identity_by_code.sql b/crates/secd/store/sqlite/sql/find_identity_by_code.sql
new file mode 100644
index 0000000..e1a6050
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/find_identity_by_code.sql
@@ -0,0 +1,11 @@
+select identity_email_id
+from auth.email_validation
+where email_validation_public_id = ?1;
+--
+select
+        identity_public_id
+        , data
+        , i.created_at
+from auth.identity            i
+left join auth.identity_email ie using (identity_id)
+where ie.identity_email_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
new file mode 100644
index 0000000..0bbafad
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/read_email_raw_id.sql
@@ -0,0 +1 @@
+select email_id from email where address = ?
diff --git a/crates/secd/store/sqlite/sql/read_identity.sql b/crates/secd/store/sqlite/sql/read_identity.sql
new file mode 100644
index 0000000..e69de29
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/read_identity.sql
diff --git a/crates/secd/store/sqlite/sql/read_identity_raw_id.sql b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql
new file mode 100644
index 0000000..552c570
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql
@@ -0,0 +1,2 @@
+select identity_id from identity where identity_public_id = ?;
+--
diff --git a/crates/secd/store/sqlite/sql/read_session.sql b/crates/secd/store/sqlite/sql/read_session.sql
new file mode 100644
index 0000000..4daa352
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/read_session.sql
@@ -0,0 +1,8 @@
+select
+        i.identity_public_id
+        , s.created_at
+        , s.expires_at
+        , s.revoked_at
+from session  s
+join identity i using (identity_id)
+where secret_hash = ?1;
diff --git a/crates/secd/store/sqlite/sql/write_email.sql b/crates/secd/store/sqlite/sql/write_email.sql
new file mode 100644
index 0000000..c127d9c
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_email.sql
@@ -0,0 +1,11 @@
+insert into email (
+       address
+) values (
+       ?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
new file mode 100644
index 0000000..37b13e1
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_email_validation.sql
@@ -0,0 +1,27 @@
+insert into email_validation
+       (
+           email_validation_public_id
+           , identity_email_id
+           , attempts
+           , code
+           , is_validated
+           , created_at
+           , expires_at
+       )
+values (
+           ?1
+           , (
+               select identity_email_id
+               from identity_email
+               where identity_id = ?2
+               and email_id = ?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;
diff --git a/crates/secd/store/sqlite/sql/write_identity.sql b/crates/secd/store/sqlite/sql/write_identity.sql
new file mode 100644
index 0000000..ff54468
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_identity.sql
@@ -0,0 +1 @@
+insert into identity (identity_public_id, data, created_at) values (?1, ?2, ?3);
diff --git a/crates/secd/store/sqlite/sql/write_session.sql b/crates/secd/store/sqlite/sql/write_session.sql
new file mode 100644
index 0000000..3c26986
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/write_session.sql
@@ -0,0 +1,18 @@
+insert into session (
+       identity_id
+       , secret_hash
+       , created_at
+       , touched_at
+       , expires_at
+       , revoked_at
+) values (
+         (select identity_id from identity where identity_public_id = ?1)
+       , ?2
+       , ?3
+       , ?4
+       , ?5
+       , ?6
+) on conflict (secret_hash) do update
+  set touched_at = excluded.touched_at
+      , revoked_at = excluded.revoked_at;
+--