blob: 3d5ca682d233d9016443e05e49f531e0183886fb (
plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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)
);
|
