apisub_tables.sql 1.24 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
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
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 (
  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),
  PRIMARY KEY (dbegin, code),
  UNIQUE (dbegin, name),
  UNIQUE (dbegin, description)
);

CREATE TABLE subscribe (
  utclogin TEXT NOT NULL,
  dbegin DATE NOT NULL,
  code INTEGER NOT NULL,
  subdate DATE NOT NULL,
  confirmed BOOLEAN NOT NULL DEFAULT FALSE,
  FOREIGN KEY (utclogin) REFERENCES localuser(utclogin),
  FOREIGN KEY (dbegin,code) REFERENCES api(dbegin,code),
  PRIMARY KEY (utclogin,dbegin,code)
);

/* 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;