apisub.sql 2.68 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

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

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

31
/* Table created to collect CAS user surname and firstname */
32
33
34
35
36
37
CREATE TABLE localuser (
  utclogin TEXT PRIMARY KEY,
  surname TEXT,
  firstname TEXT
);

38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
/* Table created to retrieve CSV info from DFP */
CREATE TABLE utcstudent (
  surname TEXT NOT NULL,
  firstname TEXT NOT NULL,
  speciality TEXT NOT NULL,
  level TEXT NOT NULL
);

/*
\COPY utcstudent (firstname,surname,speciality,level) FROM '/home/stc/inscriptions_A18.csv' WITH CSV HEADER DELIMITER ';' QUOTE '"'
*/

CREATE VIEW vlocaluser AS
SELECT l.utclogin, u.surname, u.firstname, MAX(u.speciality) AS speciality, MAX(u.level) AS level,
  CASE WHEN COUNT(*)>1 THEN 'duplication' END AS warning
FROM localuser l
LEFT JOIN utcstudent u ON l.surname=u.surname AND l.firstname=u.firstname
GROUP BY l.utclogin, u.surname, u.firstname;

57
/** Test dataset
Stephane Crozat's avatar
Stephane Crozat committed
58
INSERT INTO api VALUES (
59
  1,'Poésie et ingénierie',2019,'H',TO_DATE('20190121','yyyymmdd'),TO_DATE('20190125','yyyymmdd'),24,2,'stc@utc.fr'
Stephane Crozat's avatar
Stephane Crozat committed
60
61
);
INSERT INTO api VALUES (
62
  2,'Cloud big data blockchain IA',2019,'H',TO_DATE('20190125','yyyymmdd'),TO_DATE('20190131','yyyymmdd'),12,3,'stc@utc.fr'
Stephane Crozat's avatar
Stephane Crozat committed
63
64
65
66
);
INSERT INTO subscribe VALUES (
  'crozatst',1,TO_DATE('20181018','YYYYMMDD')
);
67
**/
Stephane Crozat's avatar
Stephane Crozat committed
68

69
CREATE OR REPLACE VIEW vapi AS
70
71
SELECT
ap.code,
72
CASE WHEN ap.code<10 THEN '000'||ap.code WHEN ap.code>=10 THEN '00'||ap.code END AS normcode,
73
74
ap.name, ap.year, ap.semester,
TO_CHAR(ap.dbegin,'TMday FMDD TMmonth') AS dbegin, TO_CHAR(ap.dend,'TMday FMDD TMmonth') AS dend,
75
76
77
78
ap.size, ap.ects, ap.mail,
COUNT(su.utclogin) AS nbsub
FROM api ap LEFT JOIN subscribe su ON ap.code=su.api
GROUP BY ap.code, normcode, ap.name, ap.year, ap.semester, dbegin, dend, ap.size, ap.ects, ap.mail
79
80
ORDER BY ap.year, ap.semester, ap.dbegin, ap.dend, ap.code;

81
CREATE OR REPLACE VIEW vsubscription AS
82
83
84
85
86
SELECT ap.*, TO_CHAR(su.subdate,'DD/MM/YYYY') AS subdate, su.utclogin, lo.firstname, lo.surname, lo.speciality, lo.level
FROM subscribe su
JOIN vapi ap ON ap.code=su.api
LEFT JOIN vlocaluser lo ON su.utclogin=lo.utclogin
ORDER BY ap.year, ap.semester, ap.dbegin, ap.dend, ap.code, lo.firstname, lo.surname, su.utclogin;