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

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,
11
12
  size INTEGER,
  ects INTEGER NOT NULL,
Stephane Crozat's avatar
Stephane Crozat committed
13
14
15
  CHECK (code>0),
  CHECK (year>2018 AND year<2100),
  CHECK (semester IN ('H','E')),
16
  CHECK (dbegin<=dend),
17
18
  CHECK (size>0),
  CHECK (ects>0),
Stephane Crozat's avatar
Stephane Crozat committed
19
20
21
22
23
24
25
26
27
28
  UNIQUE (name,year,semester)
);

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

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

41
CREATE OR REPLACE VIEW vApi AS
42
43
44
45
46
47
SELECT
ap.code,
CASE WHEN ap.code<10 THEN '000'||ap.code WHEN ap.code>=10 THEN '00'||ap.code END AS normcode, 
ap.name, ap.year, ap.semester,
TO_CHAR(ap.dbegin,'TMday FMDD TMmonth') AS dbegin, TO_CHAR(ap.dend,'TMday FMDD TMmonth') AS dend,
size, ects
48
49
50
FROM api ap
ORDER BY ap.year, ap.semester, ap.dbegin, ap.dend, ap.code;

Stephane Crozat's avatar
Stephane Crozat committed
51
CREATE OR REPLACE VIEW vSubscription AS
52
53
54
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;