01-bootstrap.sql 2.79 KB
Newer Older
Bertrand Gauthier's avatar
Bertrand Gauthier committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
--
-- BOOTSTRAP
--

--
-- Sources de données SyGAL.
--
delete from SOURCE where CODE = 'SYGAL::sygal'
/
INSERT INTO SOURCE (ID, CODE, LIBELLE, IMPORTABLE)
VALUES (1, 'SYGAL::sygal', 'SyGAL', 0)
/

--
-- Pseudo-utilisateur 'sygal-app'.
--
delete from UTILISATEUR where USERNAME = 'sygal-app'
/
INSERT INTO UTILISATEUR (ID, USERNAME, DISPLAY_NAME, PASSWORD)
VALUES (1, 'sygal-app', 'Application SyGAL', 'ldap')
/

--
-- Rôles multi-établissements.
--
delete from ROLE where SOURCE_CODE in ('ADMIN_TECH', 'OBSERV')
/
INSERT INTO ROLE (ID, CODE, LIBELLE, SOURCE_CODE, SOURCE_ID, ROLE_ID, THESE_DEP, HISTO_CREATEUR_ID, HISTO_MODIFICATEUR_ID)
  VALUES (1, 'ADMIN_TECH', 'Administrateur technique', 'ADMIN_TECH', 1, 'Administrateur technique', 0, 1, 1)
/
INSERT INTO ROLE (ID, CODE, LIBELLE, ROLE_ID, SOURCE_CODE, SOURCE_ID, THESE_DEP, HISTO_CREATEUR_ID, HISTO_MODIFICATEUR_ID)
  VALUES (2 /*241*/, 'OBSERV', 'Observateur', 'Observateur', 'OBSERV', 1, 0, 1, 1)
/

-- drop sequence INDIVIDU_ID_SEQ;
-- drop sequence UTILISATEUR_ID_SEQ;
-- drop sequence STRUCTURE_ID_SEQ;
-- drop sequence ETABLISSEMENT_ID_SEQ;
-- CREATE SEQUENCE  "INDIVIDU_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 NOORDER  NOCYCLE ;
-- CREATE SEQUENCE  "UTILISATEUR_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 NOORDER  NOCYCLE ;
-- CREATE SEQUENCE  "STRUCTURE_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 NOORDER  NOCYCLE ;
-- CREATE SEQUENCE  "ETABLISSEMENT_ID_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 NOORDER  NOCYCLE ;

--
-- L'établissement inconnu.
--
delete from ETABLISSEMENT where SOURCE_CODE  = 'ETAB_INCONNU'
/
delete from STRUCTURE where SOURCE_CODE  = 'ETAB_INCONNU'
/
insert into STRUCTURE(ID, LIBELLE, TYPE_STRUCTURE_ID, HISTO_CREATEUR_ID, HISTO_MODIFICATEUR_ID, SOURCE_ID, SOURCE_CODE, CODE)
select STRUCTURE_ID_SEQ.nextval,
       'Établissement inconnu',
       1, -- type etab
       1, 1,
       1, -- source sygal
       'ETAB_INCONNU', -- source code unique
       'INCONNU' -- code
from dual
/
insert into ETABLISSEMENT(ID, STRUCTURE_ID, HISTO_CREATEUR_ID, HISTO_MODIFICATEUR_ID, SOURCE_ID, SOURCE_CODE)
select ETABLISSEMENT_ID_SEQ.nextval,
       STRUCTURE_ID_SEQ.currval,
       1, 1,
       1, -- source sygal
       'ETAB_INCONNU' -- source code unique, idem structure
from dual
/

--
-- Avance de sequences.
--
declare
  maxid integer;
  seqnextval integer;
begin
  select max(id) into maxid from UTILISATEUR;
  LOOP
    select UTILISATEUR_ID_SEQ.nextval into seqnextval from dual;
    EXIT WHEN seqnextval >= maxid;
  END LOOP;
end;
/
declare
  maxid integer;
  seqnextval integer;
begin
  select max(id) into maxid from ROLE;
  LOOP
    select ROLE_ID_SEQ.nextval into seqnextval from dual;
    EXIT WHEN seqnextval >= maxid;
  END LOOP;
end;
/