From ed778ab2060c6131caf98231a97873d7ea490d5a Mon Sep 17 00:00:00 2001
From: Nathan Ringo <nathan@remexre.com>
Date: Tue, 16 Jan 2024 00:57:41 -0600
Subject: The start of database functionality.

---
 migrations/20240116060938_init_x500s.sql | 27 +++++++++++++++++++++++++++
 1 file changed, 27 insertions(+)
 create mode 100644 migrations/20240116060938_init_x500s.sql

(limited to 'migrations/20240116060938_init_x500s.sql')

diff --git a/migrations/20240116060938_init_x500s.sql b/migrations/20240116060938_init_x500s.sql
new file mode 100644
index 0000000..3d5ca68
--- /dev/null
+++ b/migrations/20240116060938_init_x500s.sql
@@ -0,0 +1,27 @@
+-- We know all the X.500s of students we expect to have.
+CREATE TABLE IF NOT EXISTS student_x500s
+  ( x500 TEXT NOT NULL
+  , UNIQUE(x500)
+  );
+
+-- In the common case, we can just map users to their X.500s.
+CREATE TABLE IF NOT EXISTS uids_to_x500s_clean
+  ( uid  TEXT NOT NULL
+  , x500 TEXT NOT NULL
+  , FOREIGN KEY(x500) REFERENCES student_x500s(x500)
+  , UNIQUE(uid)
+  , UNIQUE(x500)
+  );
+
+-- ...until we see someone change X.500, or try to take someone else's X.500,
+-- or use an unenrolled student's X.500.
+--
+-- These show up as a violation of one of the constraints in
+-- uids_to_x500s_clean. In that case, we still add the failing tuple to
+-- all_seen_uids_to_x500s. This lets us get a list of "suspicious tuples" by
+-- subtracting uids_to_x500s_clean from all_seen_uids_to_x500s.
+CREATE TABLE IF NOT EXISTS all_seen_uids_to_x500s
+  ( uid  TEXT NOT NULL
+  , x500 TEXT NOT NULL
+  , UNIQUE(uid, x500)
+  );
-- 
cgit v1.2.3