apisub.sql 1.34 KB
Newer Older
Stephane Crozat's avatar
Stephane Crozat committed
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS subscribe CASCADE;
DROP TABLE IF EXISTS api;

CREATE TABLE api (
  code INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  year INTEGER NOT NULL,
  semester CHAR(1) NOT NULL,
9
10
  dbegin DATE NOT NULL,
  dend DATE NOT NULL,
Stephane Crozat's avatar
Stephane Crozat committed
11
12
13
  CHECK (code>0),
  CHECK (year>2018 AND year<2100),
  CHECK (semester IN ('H','E')),
14
  CHECK (dbegin<=dend),
Stephane Crozat's avatar
Stephane Crozat committed
15
16
17
18
19
20
21
22
23
24
25
26
  UNIQUE (name,year,semester)
);

CREATE TABLE subscribe (
  utclogin TEXT NOT NULL,
  api INTEGER REFERENCES api(code),
  subdate DATE NOT NULL,
  PRIMARY KEY (utclogin,api)
);

/** Test dataset **/
INSERT INTO api VALUES (
27
  1,'Poésie et ingénierie',2019,'H',TO_DATE('20190121','yyyymmdd'),TO_DATE('20190125','yyyymmdd')
Stephane Crozat's avatar
Stephane Crozat committed
28
29
);
INSERT INTO api VALUES (
30
  2,'Cloud big data blockchain IA',2019,'H',TO_DATE('20190125','yyyymmdd'),TO_DATE('20190131','yyyymmdd')
Stephane Crozat's avatar
Stephane Crozat committed
31
32
33
34
35
);
INSERT INTO subscribe VALUES (
  'crozatst',1,TO_DATE('20181018','YYYYMMDD')
);

36
CREATE OR REPLACE VIEW vApi AS
37
SELECT ap.code, ap.name, ap.year, ap.semester, TO_CHAR(ap.dbegin,'TMday FMDD TMmonth') AS dbegin, TO_CHAR(ap.dend,'TMday FMDD TMmonth') AS dend
38
39
40
FROM api ap
ORDER BY ap.year, ap.semester, ap.dbegin, ap.dend, ap.code;

Stephane Crozat's avatar
Stephane Crozat committed
41
CREATE OR REPLACE VIEW vSubscription AS
42
43
44
SELECT ap.*, TO_CHAR(su.subdate,'DD/MM/YYYY') AS subdate, su.utclogin
FROM subscribe su JOIN vApi ap ON ap.code=su.api
ORDER BY ap.year, ap.semester, ap.dbegin, ap.dend, ap.code, su.utclogin;