apisub_tables.sql 1.21 KB
Newer Older
Stephane Crozat's avatar
Stephane Crozat committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
BEGIN;

DROP TABLE IF EXISTS subscribe CASCADE;
DROP TABLE IF EXISTS api CASCADE;
DROP TABLE IF EXISTS localuser CASCADE;
DROP TABLE IF EXISTS utcstudent;

CREATE TABLE localuser (
  utclogin TEXT PRIMARY KEY,
  email TEXT,
  surname TEXT,
  firstname TEXT,
  speciality TEXT,
  level TEXT
);

CREATE TABLE api (
18
  id UUID PRIMARY KEY,
Stephane Crozat's avatar
Stephane Crozat committed
19
20
21
22
23
24
25
26
27
28
29
30
31
  dbegin DATE NOT NULL,
  code INTEGER NOT NULL,
  name TEXT NOT NULL,
  description TEXT,
  duration INTEGER,
  size INTEGER,
  ects INTEGER NOT NULL,
  resplogin TEXT NOT NULL,
  CHECK (code>0),
  CHECK (duration BETWEEN 1 AND 5),
  CHECK (size>0),
  CHECK (ects BETWEEN 1 AND 3),
  FOREIGN KEY (resplogin) REFERENCES localuser (utclogin),
32
  UNIQUE (dbegin, code),
Stephane Crozat's avatar
Stephane Crozat committed
33
34
35
36
37
38
  UNIQUE (dbegin, name),
  UNIQUE (dbegin, description)
);

CREATE TABLE subscribe (
  utclogin TEXT NOT NULL,
39
  api UUID NOT NULL,
Stephane Crozat's avatar
Stephane Crozat committed
40
  subdate DATE NOT NULL,
41
42
  validation BOOLEAN,
  validationdate DATE,
Stephane Crozat's avatar
Stephane Crozat committed
43
  FOREIGN KEY (utclogin) REFERENCES localuser(utclogin),
44
45
  FOREIGN KEY (api) REFERENCES api(id),
  PRIMARY KEY (utclogin,api)
Stephane Crozat's avatar
Stephane Crozat committed
46
47
48
49
50
51
52
53
54
55
56
);

/* Table created to retrieve CSV info from DFP (À supprimer) */
CREATE TABLE utcstudent (
  surname TEXT NOT NULL,
  firstname TEXT NOT NULL,
  speciality TEXT NOT NULL,
  level TEXT NOT NULL
);

COMMIT;