apisub_tables.sql 1012 Bytes
Newer Older
Stephane Crozat's avatar
Stephane Crozat committed
1 2 3 4 5 6 7 8 9 10 11
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,
12
  firstname TEXT  
Stephane Crozat's avatar
Stephane Crozat committed
13 14 15
);

CREATE TABLE api (
16
  id UUID PRIMARY KEY,
Stephane Crozat's avatar
Stephane Crozat committed
17 18 19 20 21 22 23 24 25 26 27 28 29
  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),
30
  UNIQUE (dbegin, code),
Stephane Crozat's avatar
Stephane Crozat committed
31 32 33 34 35 36
  UNIQUE (dbegin, name),
  UNIQUE (dbegin, description)
);

CREATE TABLE subscribe (
  utclogin TEXT NOT NULL,
37
  api UUID NOT NULL,
Stephane Crozat's avatar
Stephane Crozat committed
38
  subdate DATE NOT NULL,
39 40
  validation BOOLEAN,
  validationdate DATE,
Stephane Crozat's avatar
Stephane Crozat committed
41
  FOREIGN KEY (utclogin) REFERENCES localuser(utclogin),
42 43
  FOREIGN KEY (api) REFERENCES api(id),
  PRIMARY KEY (utclogin,api)
Stephane Crozat's avatar
Stephane Crozat committed
44 45 46
);

COMMIT;