diff options
Diffstat (limited to '')
12 files changed, 149 insertions, 0 deletions
diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql new file mode 100644 index 0000000..aa95afc --- /dev/null +++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql @@ -0,0 +1,45 @@ +create table if not exists identity ( + identity_id integer primary key autoincrement + , identity_public_id uuid + , data text + , created_at timestamp not null + , unique(identity_public_id) +); + +create table if not exists email ( + email_id integer primary key autoincrement + , address text not null + , unique(address) +); + +create table if not exists identity_email ( + identity_email_id integer primary key autoincrement + , identity_id integer not null references identity(identity_id) + , email_id integer not null references email(email_id) + , created_at timestamp not null + , deleted_at timestamp +); + +create table if not exists email_validation ( + email_validation_id integer primary key autoincrement + , email_validation_public_id text not null -- uuid + , identity_email_id integer not null references identity_email(identity_email_id) + , attempts integer not null + , code text + , is_validated boolean not null + , created_at timestamp not null + , expires_at timestamp + , revoked_at timestamp + , unique(email_validation_public_id) +); + +create table if not exists session ( + session_id integer primary key autoincrement + , identity_id not null references identity(identity_id) + , secret_hash blob not null + , created_at timestamp not null + , touched_at timestamp not null + , expires_at timestamp + , revoked_at timestamp + , unique(secret_hash) +); 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; +-- |