apisub.sql 2.9 KB
Newer Older
Stephane Crozat's avatar
Fix #9    
Stephane Crozat committed
1
2
BEGIN;

Stephane Crozat's avatar
Stephane Crozat committed
3
DROP TABLE IF EXISTS subscribe CASCADE;
4
DROP TABLE IF EXISTS api CASCADE;
Stephane Crozat's avatar
Fix #9    
Stephane Crozat committed
5
6
DROP TABLE IF EXISTS localuser CASCADE;
DROP TABLE IF EXISTS utcstudent;
Stephane Crozat's avatar
Stephane Crozat committed
7
8
9
10

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

28
/* Table created to collect CAS user surname and firstname */
29
30
31
CREATE TABLE localuser (
  utclogin TEXT PRIMARY KEY,
  surname TEXT,
Stephane Crozat's avatar
Fix #9    
Stephane Crozat committed
32
33
34
35
36
37
38
39
40
41
42
43
  firstname TEXT,
  explanation TEXT
);
/* MAJ Apisub H19
ALTER TABLE localuser ADD explanation TEXT;
*/

CREATE TABLE subscribe (
  utclogin TEXT NOT NULL REFERENCES localuser(utclogin),
  api INTEGER REFERENCES api(code),
  subdate DATE NOT NULL,
  PRIMARY KEY (utclogin,api)
44
);
Stephane Crozat's avatar
Fix #9    
Stephane Crozat committed
45
46
47
/* MAJ Apisub H19
ALTER TABLE subscribe ADD FOREIGN KEY (utclogin) REFERENCES localuser(utclogin);
*/
48

49
50
51
52
53
54
55
56
57
58
59
60
/* 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 '"'
*/

61
62
63
64
65
66
67
68
69
70
71
72
73
74
CREATE OR REPLACE FUNCTION unaccent_string(TEXT)
RETURNS TEXT
IMMUTABLE
STRICT
LANGUAGE SQL
AS $$
SELECT translate(
    $1,
    'âãäåÁÂÃÄÅèééêëÈÉÉÊËìíîïìÌÍÎÏÌóôõöÒÓÔÕÖùúûüÙÚÛÜ',
    'aaaaAAAAAeeeeeEEEEEiiiiiIIIIIooooOOOOOuuuuUUUU'
);
$$;

CREATE OR REPLACE VIEW vlocaluser AS
75
SELECT l.utclogin, u.surname, u.firstname, MAX(u.speciality) AS speciality, MAX(u.level) AS level,
76
77
  CASE WHEN COUNT(*)>1 THEN 'duplication' END AS warning,
  l.utclogin || 'etu.utc.fr' AS utcmail
78
FROM localuser l
79
LEFT JOIN utcstudent u ON l.surname=u.surname AND l.firstname=unaccent_string(u.firstname)
80
81
GROUP BY l.utclogin, u.surname, u.firstname;

82
CREATE OR REPLACE VIEW vapi AS
83
84
SELECT
ap.code,
85
CASE WHEN ap.code<10 THEN '000'||ap.code WHEN ap.code>=10 THEN '00'||ap.code END AS normcode,
86
87
ap.name, ap.year, ap.semester,
TO_CHAR(ap.dbegin,'TMday FMDD TMmonth') AS dbegin, TO_CHAR(ap.dend,'TMday FMDD TMmonth') AS dend,
88
ap.mail, ap.ects, ap.size,
89
90
91
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
92
93
ORDER BY ap.year, ap.semester, ap.dbegin, ap.dend, ap.code;

94
CREATE OR REPLACE VIEW vsubscription AS
95
SELECT ap.*, TO_CHAR(su.subdate,'DD/MM/YYYY') AS subdate, su.utclogin AS sublogin, lo.*
96
97
98
99
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;
Stephane Crozat's avatar
Fix #9    
Stephane Crozat committed
100
101

COMMIT;