Discussion :

[fsmrel]

Bonjour Vincent,

J'avais cru comprendre que l'utilisation de schémas permettait de passer outre les déclarations séquentielles...

Il est vrai que c’est une possibilité offerte par la norme SQL-92, il y a donc plus de 20 ans, lors de l’introduction de l’instruction CREATE SCHEMA. Cela dit, ça faisait déjà quelques années à cette époque que j’utilisais DB2 et que nous devions déclarer les tables référencées avant les tables référençantes, à moins de regrouper la définition des clés étrangères dans un paquet d’ALTER TABLE après le paquet des CREATE TABLE. Ayant oublié ce qui était écrit dans la norme, j’ai conservé mes habitudes (on ne se refait pas...), mais de votre côté, vous pouvez évidemment continuer à procéder comme vous avez commencé.

[aras-vbo]

Suite...

Concernant la table T_WEBPAGE_WPG, je comprend l'intérêt d'une clé alternative composée de la clé étrangère OBJ_ID.
Dans ce cas, comment procéder ? Est-ce que je créé simplement une clé primaire composite (WPG_ID, OBJ_ID) ? Est-ce que j'attribue une contrainte UNIQUE à la colonne OBJ_ID de T_WEBPAGE_WPG juste pour assurer une contrainte d'unicité et donc empêcher un possible doublon de page ? Ou encore, est-ce que je ne peux pas carrément transformer la clé étrangère OBJ_ID de T_WEBPAGE_WPG en clé primaire de cette table et supprimer la clé WPG_ID ?


3) J'ai effectivement modifié le script pour coller au MCD concernant l'héritage des tables T_VARIABLE_STAR_VST et T_VAR_PERIODE_VPE

4) L'identification relative dans les associations liant OTA, SPG et SNR à T_TELESCOPE_TLP ont été retirées.

5) Je vais réintégrer la clé OBJ_ID dans la clé primaire de T_SPECTRUM_SPM .
La portion de l'article sur la performance est très intéressant. Vous y parlez des index clusterisés et du gain de performance qu'ils apportent. Etant donné le fait que la table T_SPECTRUM_SPM sera l'une (si ce n'est la) plus imposante de la base et que les requêtes portant sur OBJ_ID sur cette table renverront sans doute plusieurs lignes, n'aurais-je pas intérêt à rendre l'index CLUSTER dans la mesure du possible ?

6) J'ai évacué les clés OTA_ID, SPG_ID et SNR_ID de la table T_SPECTRUM_SPM

7) Je voudrais si possible éviter la bilocation quantique de télescopes.
Toutefois, je ne voudrais pas non plus que la résolution d'un problème que je ne juge pas crucial oblige à la mise en place d'une solution très complexe. Nous n'avons pour le moment jamais eu de problème d'incohérence temporelle de spectres pris avec le même instrument, par la même personne, à deux endroits différents. L'algèbre des intervalles de Allen a l'air bien adapté pour éviter ce genre de risque mais j'ai l'impression de ne pas être capable de l'implémenter correctement.
Dans l'absolu, on pourrait imaginer la mise en place de ce contrôle de cohérence pour d'autres situations comme celle où un instrument donné ne peut pas prendre le spectre de 2 astres en même temps.
Si on part sur le principe des intervalles de temps d'Allen, la clé primaire de la table T_TELESCOPE_SITE_TLS ne devrait-elle pas être le triplet (TLS_ID, EXPOSURE_START_DATETIME, EXPOSURE_TIME) et non le simple doublet (TLS_ID, EXPOSURE_TIME) ? Il me semble qu'une simple durée (EXPOSURE_TIME) ne permet pas de situer de façon absolue une durée dans le temps et qu'il faut donc au moins l'une des bornes de la durée (ici la borne de début, EXPOSURE_START_DATETIME).
A moins qu'il n'y ait eu confusion entre EXPOSURE_TIME (la durée de la pose exprimée en seconde) et EXPOSURE_START_DATETIME (la date et l'heure de début de la pose).

Enfin, avez-vous des exemples d'implémentation des intervalles de Allen dans les bases de données relationnelles ?
La modélisation et la vérification des intervalles seraient idéales mais j'ai peur que cela ne devienne très compliqué à implémenter.

9) J'ai évacué SPS_ID de la clé de la table de liaison T_SPM_SERIE_SPS pour éviter qu'un spectre ne puisse apparaître dans plusieurs séries.

10) J'ai ajouté USR_ID dans la clé de la table T_STATUS_STA

Enfin, dans la table de liaison T_L_USE, la clé primaire est bien {USR_ID,TLS_ID,SPM_EXPOSURE_START_DATETIME}.

Voici donc le script modifié :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
-------------------------------------------------------------
-- ARAS DATABASE v0.0.3 - PostgreSQL
------------------------------------------------------------
 
------------------------------------------------------------
-- SCHEMAS
------------------------------------------------------------
CREATE SCHEMA S_SITE;
CREATE SCHEMA S_USER;
CREATE SCHEMA S_TELESCOPE;
CREATE SCHEMA S_SPECTRUM;
CREATE SCHEMA S_CATALOG;
CREATE SCHEMA S_WEBSITE;
 
------------------------------------------------------------
-- DOMAINS
------------------------------------------------------------
 
-- Code of the observation site (ARAS code)
CREATE DOMAIN D_A_SITECODE
AS CHAR(6)
CONSTRAINT CK_D_A_SITECODE CHECK (VALUE IS NOT NULL AND VALUE = UPPER(VALUE) AND VALUE ~* '#^[A-Z]{3}-[A-Z]{2}$#')
COLLATE SQL_ASCII;
 
-- Latitude of the observation site (BSS_LAT)
CREATE DOMAIN D_N_LATITUDE
AS NUMERIC(10,7)
CONSTRAINT CK_D_N_LATITUDE CHECK (VALUE IS NOT NULL AND VALUE BETWEEN -90 AND +90);
 
-- Longitude of the observation site (BSS_LONG)
CREATE DOMAIN D_N_LONGITUDE
AS NUMERIC(10,7)
CONSTRAINT CK_D_N_LONGITUDE CHECK (VALUE IS NOT NULL AND VALUE BETWEEN 0 AND 360);
 
-- Altitude of the observation site (BSS_ELEV)
CREATE DOMAIN D_N_ALTITUDE
AS NUMERIC(6,2)
CONSTRAINT CK_D_N_ALTITUDE CHECK (VALUE IS NOT NULL AND VALUE BETWEEN 0 AND 9000);
 
-- User Email
CREATE DOMAIN D_A_EMAIL
AS VARCHAR(255)
CONSTRAINT CK_D_A_EMAIL CHECK (VALUE IS NOT NULL AND VALUE = LOWER(VALUE) AND VALUE ~* '(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])')
COLLATE SQL_ASCII;
 
-- User Website
CREATE DOMAIN D_A_WEBSITE
AS VARCHAR(255)
CONSTRAINT CK_D_A_WEBSITE CHECK (VALUE IS NOT NULL AND VALUE = LOWER(VALUE) AND VALUE ~* '@^(http\:\/\/|https\:\/\/)?([a-z0-9][a-z0-9\-]*\.)+[a-z0-9][a-z0-9\-]*$@i')
COLLATE SQL_ASCII;
 
-- User Code (ARAS)
CREATE DOMAIN D_A_USERCODE
AS CHAR(3)
CONSTRAINT CK_D_A_USERCODE CHECK (VALUE IS NOT NULL AND VALUE = UPPER(VALUE) AND VALUE ~* '#^[A-Z]{3}$#')
COLLATE SQL_ASCII;
 
-- Right Ascension of an object
CREATE DOMAIN D_N_RIGHTASCENSION
AS NUMERIC(10,7)
CONSTRAINT CK_D_N_RIGHTASCENSION CHECK (VALUE IS NOT NULL AND VALUE BETWEEN 0 AND 360);
 
-- Declination of an object
CREATE DOMAIN D_N_DECLINATION
AS NUMERIC(8,6)
CONSTRAINT CK_D_N_DECLINATION CHECK (VALUE IS NOT NULL AND VALUE BETWEEN -90 AND 90);
 
-- Magnitude of an object
CREATE DOMAIN D_N_MAGNITUDE
AS NUMERIC(4,2)
CONSTRAINT CK_D_N_MAGNITUDE CHECK (VALUE IS NOT NULL AND VALUE BETWEEN -27 AND 30);
 
-- Spectrum Status
CREATE DOMAIN D_A_SPMSTATUS
AS VARCHAR(50)
CONSTRAINT CK_D_A_SPMSTATUS CHECK (VALUE IS NOT NULL AND VALUE IN ('validated','in progress','refused'))
COLLATE SQL_ASCII;
 
-- Flux calibration
CREATE DOMAIN D_A_FLXVALUE
AS VARCHAR(30)
DEFAULT 'relative'
CONSTRAINT CK_D_A_FLXVALUE CHECK (VALUE IS NOT NULL AND VALUE IN 'relative', 'erg/cm^2/s/A')
COLLATE SQL_ASCII;
 
-- Applied Heliocentric Correction (BSS_VHEL)
CREATE DOMAIN D_N_HELIOCORRECTION
AS DECIMAL(6,3)
DEFAULT 0.0
CONSTRAINT CK_D_N_HELIOCORRECTION CHECK (VALUE IS NOT NULL AND VALUE BETWEEN -200 AND 200);
 
 
 
 
------------------------------------------------------------
-- SCHEMA CATALOG
------------------------------------------------------------
 
------------------------------------------------------------
-- Table OBJECT : T_OBJECT_OBJ
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_OBJECT_OBJ(
	OBJ_ID      SERIAL NOT NULL ,
	OBJ_DISPLAY BOOL  NOT NULL ,
	CONSTRAINT PRK_T_OBJECT_OBJ PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table OBJECT ALIAS : T_OBJECT_ALIAS_OBA
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_OBJECT_ALIAS_OBA(
	OBA_ID      SERIAL NOT NULL ,
	OBA_NAME    VARCHAR (50) NOT NULL ,
	USUAL_ALIAS BOOL  NOT NULL ,
	OBJ_ID      INT  NOT NULL ,
	CONSTRAINT PRK_T_OBJECT_ALIAS_OBA PRIMARY KEY (OBA_ID,OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_OBJECT_ALIAS_OBA
ADD CONSTRAINT FK_T_OBJECT_ALIAS_OBA_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
 
------------------------------------------------------------
-- Table OBJECT COORDINATES : T_COORDINATE_COO
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_COORDINATE_COO(
	COO_ID      SERIAL NOT NULL ,
	COO_RA      D_N_RIGHTASCENSION NOT NULL ,
	COO_DEC     D_N_DECLINATION NOT NULL ,
	COO_EQUINOX FLOAT  NOT NULL ,
	COO_SYS     CHAR (4)  NOT NULL ,
	OBJ_ID      INT  NOT NULL ,
	CONSTRAINT PRK_T_COORDINATE_COO PRIMARY KEY (COO_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_COORDINATE_COO
ADD CONSTRAINT FK_T_COORDINATE_COO_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
 
------------------------------------------------------------
-- Table MAGNITUDE TYPE : T_MAG_TYPE_MGT
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_MAG_TYPE_MGT(
	MGT_ID    SERIAL NOT NULL ,
	MGT_VALUE VARCHAR (50) NOT NULL ,
	CONSTRAINT PRK_T_MAG_TYPE_MGT PRIMARY KEY (MGT_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table MAGNITUDE : T_MAGNITUDE_MAG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_MAGNITUDE_MAG(
	MAG_ID    SERIAL NOT NULL ,
	MAG_VALUE D_N_MAGNITUDE NOT NULL ,
	OBJ_ID    INT  NOT NULL ,
	MGT_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_MAGNITUDE_MAG PRIMARY KEY (MAG_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_MAGNITUDE_MAG
ADD CONSTRAINT FK_T_MAGNITUDE_MAG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
ALTER TABLE S_CATALOG.T_MAGNITUDE_MAG
ADD CONSTRAINT FK_T_MAGNITUDE_MAG_MGT_ID FOREIGN KEY (MGT_ID)
REFERENCES S_CATALOG.T_MAG_TYPE_MGT (MGT_ID);
 
 
------------------------------------------------------------
-- Table héritée STAR : T_STAR_STR
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_STAR_STR(
	OBJ_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_STAR_STR PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_STAR_STR
ADD CONSTRAINT FK_T_STAR_STR_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
 
------------------------------------------------------------
-- Table héritée VARIABLE STAR : T_VARIABLE_STAR_VST
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_VARIABLE_STAR_VST(
	OBJ_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_VARIABLE_STAR_VST PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_VARIABLE_STAR_VST
ADD CONSTRAINT FK_T_VARIABLE_STAR_VST_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_STAR_STR (OBJ_ID);
 
 
------------------------------------------------------------
-- Table VARIABLE PERIODE : T_VAR_PERIODE_VPE
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_VAR_PERIODE_VPE(
	VPE_ID    SERIAL NOT NULL ,
	VPE_VALUE INT  NOT NULL ,
	OBJ_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_VAR_PERIODE_VPE PRIMARY KEY (VPE_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_VAR_PERIODE_VPE
ADD CONSTRAINT FK_T_VAR_PERIODE_VPE_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_VARIABLE_STAR_VST (OBJ_ID);
 
 
------------------------------------------------------------
-- Table CATEGORY : T_CATEGORY_CTG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_CATEGORY_CTG(
	CTG_ID   SERIAL NOT NULL ,
	CTG_NAME VARCHAR (50) NOT NULL ,
	CONSTRAINT PRK_T_CATEGORY_CTG PRIMARY KEY (CTG_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison CTG-CTG : T_L_CTG_CTG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_L_CTG_CTG(
	CTG_ID                INT  NOT NULL ,
	CTG_ID_T_CATEGORY_CTG INT  NOT NULL ,
	CONSTRAINT PRK_T_L_CTG_CTG PRIMARY KEY (CTG_ID,CTG_ID_T_CATEGORY_CTG)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_L_CTG_CTG
ADD CONSTRAINT FK_T_L_CTG_CTG_CTG_ID FOREIGN KEY (CTG_ID)
REFERENCES S_CATALOG.T_CATEGORY_CTG (CTG_ID);
 
ALTER TABLE S_CATALOG.T_L_CTG_CTG
ADD CONSTRAINT FK_T_L_CTG_CTG_CTG_ID_T_CATEGORY_CTG FOREIGN KEY (CTG_ID_T_CATEGORY_CTG)
REFERENCES S_CATALOG.T_CATEGORY_CTG (CTG_ID);
 
 
------------------------------------------------------------
-- Table de liaison OBJECT-CATEGORY : T_L_OBJ_CTG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_L_OBJ_CTG(
	CTG_ID INT  NOT NULL ,
	OBJ_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_L_OBJ_CTG PRIMARY KEY (CTG_ID,OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_L_OBJ_CTG
ADD CONSTRAINT FK_T_L_OBJ_CTG_CTG_ID FOREIGN KEY (CTG_ID)
REFERENCES S_CATALOG.T_CATEGORY_CTG (CTG_ID);
 
ALTER TABLE S_CATALOG.T_L_OBJ_CTG
ADD CONSTRAINT FK_T_L_OBJ_CTG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
 
 
 
------------------------------------------------------------
-- SCHEMA USER
------------------------------------------------------------
 
------------------------------------------------------------
-- Table USER : T_USER_USR
------------------------------------------------------------
CREATE TABLE S_USER.T_USER_USR(
	USR_ID        SERIAL NOT NULL ,
	USR_CODE      D_A_USERCODE NOT NULL UNIQUE,
	USR_LASTNAME  VARCHAR (100) NOT NULL ,
	USR_FIRSTNAME VARCHAR (100) NOT NULL ,
	ROL_ID        INT  NOT NULL ,
	CONSTRAINT PRK_T_USER_USR PRIMARY KEY (USR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_USER_USR
ADD CONSTRAINT FK_T_USER_USR_ROL_ID FOREIGN KEY (ROL_ID)
REFERENCES S_USER.T_ROLE_ROL (ROL_ID);
 
 
------------------------------------------------------------
-- Table USER ALIAS : T_USER_ALIAS_USA
------------------------------------------------------------
CREATE TABLE S_USER.T_USER_ALIAS_USA(
	USA_ID   SERIAL NOT NULL ,
	USA_NAME VARCHAR (255) NOT NULL UNIQUE,
	USR_ID   INT  NOT NULL ,
	CONSTRAINT PRK_T_USER_ALIAS_USA PRIMARY KEY (USA_ID,USR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_USER_ALIAS_USA
ADD CONSTRAINT FK_T_USER_ALIAS_USA_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
 
------------------------------------------------------------
-- Table USER EMAIL : T_EMAIL_EML
------------------------------------------------------------
CREATE TABLE S_USER.T_EMAIL_EML(
	EML_ID    SERIAL NOT NULL ,
	EML_EMAIL D_A_EMAIL NOT NULL UNIQUE,
	USR_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_EMAIL_EML PRIMARY KEY (EML_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_EMAIL_EML
ADD CONSTRAINT FK_T_EMAIL_EML_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
 
------------------------------------------------------------
-- Table USER WEBSITE : T_WEBSITE_WEB
------------------------------------------------------------
CREATE TABLE S_USER.T_WEBSITE_WEB(
	WEB_ID  SERIAL NOT NULL ,
	WEB_URL D_A_WEBSITE NOT NULL ,
	CONSTRAINT PRK_T_WEBSITE_WEB PRIMARY KEY (WEB_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison USER-WEBSITE : T_L_USR_WEB
------------------------------------------------------------
CREATE TABLE S_USER.T_L_USR_WEB(
	USR_ID INT  NOT NULL ,
	WEB_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_L_USR_WEB PRIMARY KEY (USR_ID,WEB_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_L_USR_WEB
ADD CONSTRAINT FK_T_L_USR_WEB_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_USER.T_L_USR_WEB
ADD CONSTRAINT FK_T_L_USR_WEB_WEB_ID FOREIGN KEY (WEB_ID)
REFERENCES S_USER.T_WEBSITE_WEB (WEB_ID);
 
 
------------------------------------------------------------
-- Table de liaison USER-COUNTRY : T_L_USR_CNY
------------------------------------------------------------
CREATE TABLE S_USER.T_L_USR_CNY(
	USR_ID INT  NOT NULL ,
	CNY_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_L_USR_CNY PRIMARY KEY (USR_ID,CNY_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_L_USR_CNY
ADD CONSTRAINT FK_T_L_USR_CNY_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_USER.T_L_USR_CNY
ADD CONSTRAINT FK_T_L_USR_CNY_CNY_ID FOREIGN KEY (CNY_ID)
REFERENCES S_SITE.T_COUNTRY_CNY (CNY_ID);
 
 
------------------------------------------------------------
-- Table USER ROLE : T_ROLE_ROL
------------------------------------------------------------
CREATE TABLE S_USER.T_ROLE_ROL(
	ROL_ID   SERIAL NOT NULL ,
	ROL_NAME VARCHAR (100) NOT NULL UNIQUE,
	CONSTRAINT PRK_T_ROLE_ROL PRIMARY KEY (ROL_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison USER-TELESCOPE-SITE : T_L_USE
------------------------------------------------------------
CREATE TABLE S_USER.T_L_USE(
	T_L_USE_MAIN                BOOL  NOT NULL ,
	USR_ID                      INT  NOT NULL ,
	TLS_ID                      INT  NOT NULL ,
	SPM_EXPOSURE_START_DATETIME TIMESTAMP  NOT NULL ,
	CONSTRAINT PRK_T_L_USE PRIMARY KEY (USR_ID,TLS_ID,SPM_EXPOSURE_START_DATETIME)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_L_USE
ADD CONSTRAINT FK_T_L_USE_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_USER.T_L_USE
ADD CONSTRAINT FK_T_L_USE_TLS_ID FOREIGN KEY (TLS_ID)
REFERENCES S_SITE.T_TELESCOPE_SITE_TLS (TLS_ID);
 
ALTER TABLE S_USER.T_L_USE
ADD CONSTRAINT FK_T_L_USE_SPM_EXPOSURE_START_DATETIME FOREIGN KEY (SPM_EXPOSURE_START_DATETIME)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_EXPOSURE_START_DATETIME);
 
 
 
 
------------------------------------------------------------
-- SCHEMA TELESCOPE
------------------------------------------------------------
 
------------------------------------------------------------
-- Table OPTICAL TUBE : T_OPTICAL_TUBE_OTA
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_OPTICAL_TUBE_OTA(
	OTA_ID   SERIAL NOT NULL ,
	OTA_NAME VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_OPTICAL_TUBE_OTA PRIMARY KEY (OTA_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table SPECTROGRAPH : T_SPECTROGRAPH_SPG
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_SPECTROGRAPH_SPG(
	SPG_ID   SERIAL NOT NULL ,
	SPG_NAME VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_SPECTROGRAPH_SPG PRIMARY KEY (SPG_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table SENSOR : T_SENSOR_SNR
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_SENSOR_SNR(
	SNR_ID   SERIAL NOT NULL ,
	SNR_NAME VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_SENSOR_SNR PRIMARY KEY (SNR_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table TELESCOPE : T_TELESCOPE_TLP
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_TELESCOPE_TLP(
	TLP_ID   SERIAL NOT NULL ,
	TLP_CODE VARCHAR (25)  ,
	TLP_NAME VARCHAR (250) NOT NULL ,
	OTA_ID   INT  NOT NULL ,
	SPG_ID   INT  NOT NULL ,
	SNR_ID   INT  NOT NULL ,
	CONSTRAINT PRK_T_TELESCOPE_TLP PRIMARY KEY (TLP_ID,OTA_ID,SPG_ID,SNR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_TELESCOPE.T_TELESCOPE_TLP
ADD CONSTRAINT FK_T_TELESCOPE_TLP_OTA_ID FOREIGN KEY (OTA_ID)
REFERENCES S_TELESCOPE.T_OPTICAL_TUBE_OTA (OTA_ID);
 
ALTER TABLE S_TELESCOPE.T_TELESCOPE_TLP
ADD CONSTRAINT FK_T_TELESCOPE_TLP_SPG_ID FOREIGN KEY (SPG_ID)
REFERENCES S_TELESCOPE.T_SPECTROGRAPH_SPG (SPG_ID);
 
ALTER TABLE S_TELESCOPE.T_TELESCOPE_TLP
ADD CONSTRAINT FK_T_TELESCOPE_TLP_SNR_ID FOREIGN KEY (SNR_ID)
REFERENCES S_TELESCOPE.T_SENSOR_SNR (SNR_ID);
 
 
 
------------------------------------------------------------
-- SCHEMA SITE
------------------------------------------------------------
 
------------------------------------------------------------
-- Table COUNTRY : T_COUNTRY_CNY
------------------------------------------------------------
CREATE TABLE S_SITE.T_COUNTRY_CNY(
	CNY_ID      SERIAL NOT NULL ,
	CNY_ISOCODE VARCHAR (2) NOT NULL UNIQUE,
	CNY_NAME    VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_COUNTRY_CNY PRIMARY KEY (CNY_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table SITE : T_SITE_SIT
------------------------------------------------------------
CREATE TABLE S_SITE.T_SITE_SIT(
	SIT_ID        SERIAL NOT NULL ,
	SIT_CODE      D_A_SITECODE NOT NULL UNIQUE,
	SIT_LATITUDE  D_N_LATITUDE NOT NULL ,
	SIT_LONGITUDE D_N_LONGITUDE NOT NULL ,
	SIT_ALTITUDE  D_N_ALTITUDE NOT NULL ,
	CNY_ID        INT  NOT NULL ,
	CONSTRAINT PRK_T_SITE_SIT PRIMARY KEY (SIT_ID)
) WITHOUT OIDS;
 
ALTER TABLES_SITE.T_SITE_SIT
ADD CONSTRAINT FK_T_SITE_SIT_CNY_ID FOREIGN KEY (CNY_ID)
REFERENCES S_SITE.T_COUNTRY_CNY (CNY_ID);
 
 
------------------------------------------------------------
-- Table SITE ALIAS : T_SITE_ALIAS_SIA
------------------------------------------------------------
CREATE TABLE S_SITE.T_SITE_ALIAS_SIA(
	SIA_ID   SERIAL NOT NULL ,
	SIA_NAME VARCHAR (100) NOT NULL ,
	SIT_ID   INT  NOT NULL ,
	CONSTRAINT PRK_T_SITE_ALIAS_SIA PRIMARY KEY (SIA_ID,SIT_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SITE.T_SITE_ALIAS_SIA
ADD CONSTRAINT FK_T_SITE_ALIAS_SIA_SIT_ID FOREIGN KEY (SIT_ID)
REFERENCES S_SITE.T_SITE_SIT (SIT_ID);
 
 
------------------------------------------------------------
-- Table TELESCOPE-SITE : T_TELESCOPE_SITE_TLS
------------------------------------------------------------
CREATE TABLE S_SITE.T_TELESCOPE_SITE_TLS(
	TLS_ID                      SERIAL NOT NULL ,
	SIT_ID                      INT  NOT NULL ,
	TLP_ID                      INT  NOT NULL ,
	CONSTRAINT PRK_T_TELESCOPE_SITE_TLS PRIMARY KEY (TLS_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SITE.T_TELESCOPE_SITE_TLS
ADD CONSTRAINT FK_T_TELESCOPE_SITE_TLS_SIT_ID FOREIGN KEY (SIT_ID)
REFERENCES S_SITE.T_SITE_SIT (SIT_ID);
 
ALTER TABLE S_SITE.T_TELESCOPE_SITE_TLS
ADD CONSTRAINT FK_T_TELESCOPE_SITE_TLS_TLP_ID FOREIGN KEY (TLP_ID)
REFERENCES S_TELESCOPE.T_TELESCOPE_TLP (TLP_ID);
 
 
 
 
------------------------------------------------------------
-- SCHEMA SPECTRUM
------------------------------------------------------------
 
------------------------------------------------------------
-- Table SPECTRUM : T_SPECTRUM_SPM
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_SPECTRUM_SPM(
	SPM_ID                      SERIAL NOT NULL ,
	SPM_FITS_FILE               VARCHAR (100) NOT NULL ,
	SPM_HELIO_CORRECTION        D_N_HELIOCORRECTION NOT NULL ,
	SPM_TELLURIC_CORRECTION     CHAR (50)  NOT NULL ,
	SPM_COSMIC_REMOVAL          CHAR (50)  NOT NULL ,
	SPM_NORMALISATION           CHAR (50)  NOT NULL ,
	SPM_DISPLAY                 BOOL  NOT NULL ,
	OBJ_ID                      INT  NOT NULL ,
	EXPOSURE_TIME               INT  NOT NULL ,
	TLS_ID                      INT  NOT NULL ,
	SPM_EXPOSURE_START_DATETIME TIMESTAMP  NOT NULL ,
	CONSTRAINT PRK_T_SPECTRUM_SPM PRIMARY KEY (SPM_ID,OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_SPECTRUM_SPM
ADD CONSTRAINT FK_T_SPECTRUM_SPM_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
ALTER TABLE S_SPECTRUM.T_SPECTRUM_SPM
ADD CONSTRAINT FK_T_SPECTRUM_SPM_TLS_ID FOREIGN KEY (TLS_ID)
REFERENCES S_SITE.T_TELESCOPE_SITE_TLS (TLS_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM RESOLVING POWER : T_RESOLVING_POWER_RPO
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_RESOLVING_POWER_RPO(
	RPO_VALUE DECIMAL (6,2)  NOT NULL ,
	SPM_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_RESOLVING_POWER_RPO PRIMARY KEY (SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_RESOLVING_POWER_RPO
ADD CONSTRAINT FK_T_RESOLVING_POWER_RPO_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM FLUX : T_FLUX_FLX
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_FLUX_FLX(
	FLX_VALUE D_A_FLXVALUE NOT NULL ,
	SPM_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_FLUX_FLX PRIMARY KEY (SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_FLUX_FLX
ADD CONSTRAINT FK_T_FLUX_FLX_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table héritée ECHELLE SPECTRUM : T_ECHELLE_SPECTRUM_ESP
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_ECHELLE_SPECTRUM_ESP(
	ESP_ECHELLE_ORD CHAR (100)  NOT NULL ,
	SPM_ID          INT  NOT NULL ,
	CONSTRAINT PRK_T_ECHELLE_SPECTRUM_ESP PRIMARY KEY (SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_ECHELLE_SPECTRUM_ESP
ADD CONSTRAINT FK_T_ECHELLE_SPECTRUM_ESP_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM SERIES : T_SPM_SERIE_SPS
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_SPM_SERIE_SPS(
	SPS_ID       SERIAL NOT NULL ,
	SPS_ZIP_FILE VARCHAR (255) NOT NULL ,
	CONSTRAINT PRK_T_SPM_SERIE_SPS PRIMARY KEY (SPS_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison SPECTRUM-SERIES : T_L_SPS_SPM
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_L_SPS_SPM(
	SPS_ID 			INT NOT NULL,
	SPM_ID 			INT NOT NULL,
	CONSTRAINT PRK_T_L_SPS_SPM PRIMARY KEY (SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_L_SPS_SPM
ADD CONSTRAINT FK_T_L_SPS_SPM_SPS_ID FOREIGN KEY (SPS_ID)
REFERENCES S_SPECTRUM.T_SPM_SERIE_SPS (SPS_ID);
 
ALTER TABLE S_SPECTRUM.T_L_SPS_SPM
ADD CONSTRAINT FK_T_L_SPS_SPM_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM SUBMISSION : T_L_SUBMIT_SPM
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_L_SUBMIT_SPM(
	SPM_SUBMISSION_DATE DATE  NOT NULL ,
	USR_ID              INT  NOT NULL ,
	SPM_ID              INT  NOT NULL ,
	CONSTRAINT PRK_T_L_SUBMIT_SPM PRIMARY KEY (USR_ID,SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_L_SUBMIT_SPM
ADD CONSTRAINT FK_T_L_SUBMIT_SPM_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_SPECTRUM.T_L_SUBMIT_SPM
ADD CONSTRAINT FK_T_L_SUBMIT_SPM_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM STATUS : T_STATUS_STA
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_STATUS_STA(
	STA_ID                SERIAL NOT NULL ,
	STA_VALUE             D_A_SPMSTATUS NOT NULL ,
	SPM_MODIF_STATUS_DATE DATE  NOT NULL ,
	USR_ID                INT  NOT NULL ,
	SPM_ID                INT  NOT NULL ,
	CONSTRAINT PRK_T_STATUS_STA PRIMARY KEY (STA_ID,USR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_STATUS_STA
ADD CONSTRAINT FK_T_STATUS_STA_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_SPECTRUM.T_STATUS_STA
ADD CONSTRAINT FK_T_STATUS_STA_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
 
 
------------------------------------------------------------
-- SCHEMA WEBSITE
------------------------------------------------------------
 
------------------------------------------------------------
-- Table WEBSITE PAGES : T_WEBPAGE_WPG
------------------------------------------------------------
CREATE TABLE S_WEBSITE.T_WEBPAGE_WPG(
	WPG_ID      SERIAL NOT NULL ,
	WPG_URL     VARCHAR (25) NOT NULL UNIQUE,
	WPG_COMMENT VARCHAR (2000)   ,
	WPG_UPDATE  TIMESTAMP  NOT NULL ,
	WPG_DISPLAY BOOL  NOT NULL ,
	OBJ_ID      INT  NOT NULL ,
	CONSTRAINT PRK_T_WEBPAGE_WPG PRIMARY KEY (WPG_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_WEBSITE.T_WEBPAGE_WPG
ADD CONSTRAINT FK_T_WEBPAGE_WPG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);

Vincent

Bonjour Vincent,

Il est vrai que c’est une possibilité offerte par la norme SQL-92, il y a donc plus de 20 ans, lors de l’introduction de l’instruction CREATE SCHEMA. Cela dit, ça faisait déjà quelques années à cette époque que j’utilisais DB2 et que nous devions déclarer les tables référencées avant les tables référençantes, à moins de regrouper la définition des clés étrangères dans un paquet d’ALTER TABLE après le paquet des CREATE TABLE. Ayant oublié ce qui était écrit dans la norme, j’ai conservé mes habitudes (on ne se refait pas...), mais de votre côté, vous pouvez évidemment continuer à procéder comme vous avez commencé.

François,

JMerise génère le script en plaçant effectivement un paquet d'ALTER TABLE à la fin, comme vous le faisiez, ce qui paraît finalement logique puisqu'il n'y avait aucun schéma dans la modélisation (je n'ai pas trouvé comment faire dans le logiciel, s'il est possible de le faire, d'ailleurs).
Je ne trouve pas cette façon de faire très facile à relire, surtout s'il y a beaucoup de tables. Comme j'utilise des schémas, autant rapprocher les déclaration de clés étrangères des tables qui s'y rapportent.

Vincent

[JPhi33]

Bonsoir Vincent, François,

(... Paul et Les Autres )

Je me permets un petit retour arrière sur le MCD. Dans la version finale du MCD, Vincent, tu n'as pas tenu compte de ma remarque portant sur la modélisation de la magnitude (post #33)

[ ASTRE ]--0,n----( MAGNITUDE )----0,n--[ TYPE_MAGNITUDE ]

L'association MAGNITUDE porte la propriété "magnitude_etoile" (qui devrait probablement être renommée "valeur_magnitude").
La table qui en est dérivée est : MAGNITUDE(id_astre, id_type_magnitude, valeur_magnitude)

Cette modélisation permet toutes les combinaisons possibles.

Est-ce à dire que j'aurais mal compris la problématique ou que cette modélisation ne convient pas ?

[fsmrel]

Bonsoir Vincent,

Concernant la table T_WEBPAGE_WPG, je comprend l'intérêt d'une clé alternative composée de la clé étrangère OBJ_ID.
Dans ce cas, comment procéder ? Est-ce que je créé simplement une clé primaire composite (WPG_ID, OBJ_ID) ? Est-ce que j'attribue une contrainte UNIQUE à la colonne OBJ_ID de T_WEBPAGE_WPG juste pour assurer une contrainte d'unicité et donc empêcher un possible doublon de page ? Ou encore, est-ce que je ne peux pas carrément transformer la clé étrangère OBJ_ID de T_WEBPAGE_WPG en clé primaire de cette table et supprimer la clé WPG_ID ?

Votre MCD représente actuellement ceci :

[T_OBJECT_OBJ]--0,1----------(describe)---------1,1--[T_WEBPAGE_WPG]

En définissant une clé primaire composite {WPG_ID, OBJ_ID} dans le MLD, alors cela revient à changer la cardinalité 0,1 en 0,N dans le MCD :

[T_OBJECT_OBJ]--0,N----------(describe)---------1,1--[T_WEBPAGE_WPG]

Pour éviter tout doublon de page, vous pouvez bien sûr assurer une contrainte d'unicité en définissant une clé alternative {OBJ_ID}. Maintenant, si c’est à la vie à la mort, c'est-à-dire si T_WEBPAGE_WPG ne vit qu’en dépendance totale de T_OBJECT_OBJ, il est beaucoup plus logique de supprimer l’attribut WPG_ID, et la représentation devient (notez l’identification relative) :

[T_OBJECT_OBJ]--0,1----------(describe)---------(1,1)--[T_WEBPAGE_WPG]

Et dans le MLD, la clé primaire de la table se résume au singleton {OBJ_ID}. Comme disait Guillaume d’Ockham, « Pluralitas non est ponenda sine necessitate » (Evitons de multiplier inutilement les choses).

5) Je vais réintégrer la clé OBJ_ID dans la clé primaire de T_SPECTRUM_SPM .
La portion de l'article sur la performance est très intéressant. Vous y parlez des index clusterisés et du gain de performance qu'ils apportent. Etant donné le fait que la table T_SPECTRUM_SPM sera l'une (si ce n'est la) plus imposante de la base et que les requêtes portant sur OBJ_ID sur cette table renverront sans doute plusieurs lignes, n'aurais-je pas intérêt à rendre l'index CLUSTER dans la mesure du possible ?

La table T_SPECTRUM_SPM mérite d’être particulièrement mignotée... J’ai beaucoup fait le DBA avec DB2, et dans le contexte de ce SGBD, je répondrais positivement : cluster pour toutes les tables dans lesquelles est présent l’attribut OBJ_ID, dans la mesure où il est partie prenante dans les requêtes les plus sollicitées. Mais l’efficacité se mesure au moins à l’aide d’un prototype de performance, car dans ce domaine rien n’est jamais acquis, les effets secondaires peuvent se manifester (concurrence de verrouillage en cas de mises à jour intenses par exemple). En ce qui concerne PostgreSQL, je n’ai jamais fait le DBA (au moins professionnellement), mais il n’en reste pas moins qu’il faut prototyper les performances :

J’ai retrouvé des résultats de tests (PostgreSQL 9.3.5) portant sur un SELECT qui est la jointure de 90 tables (d’une base de données que j’ai dû supprimer...), dont celle qui est disons l’équivalent de la table SPECTRE contient un peu plus de 100000 lignes. Cette grande jointure est composée de 6 inner joins, le reste étant des left outer joins. Pour lire les données de l’équivalent de 20 spectres : 0,45 seconde.
Cela dit, je ne me sens pas le courage de recréer la base de données pour refaire des tests de performance divers et variés...

Et en l’occurrence une instruction à bien maîtriser : EXPLAIN. En effet elle nous permet de demander au SGBD comment il se comportera dans l’utilisation des index lors des requêtes qu’on lui soumettra. On pourra en reparler.


De son côté, JMerise ne vas pas dans le sens de la performance et de l’économie...

Prenons le cas des tables T_OBJECT_OBJ et T_OBJECT_ALIAS_OBA. Vous observerez que si l’attribut OBJ_ID peut être à l’origine de l’index cluster de la table S_CATALOG.T_OBJECT_OBJ, il faudra créer un index sur cette même colonne pour la table T_OBJECT_ALIAS_OBA si on veut arriver à synchroniser, rendre efficace le clustering de ces tables. Si on permute les attributs OBA_ID et OBJ_ID dans la clé primaire de la table T_OBJECT_ALIAS_OBA, d’office l’index correspondant pourra être lui -même cluster (à vérifier par EXPLAIN...), sans qu’il soit besoin de créer un index en plus. Maintenant, reste à voir ce qu’il est dit du clustering dans la documentation de PostgreSQL, alors que je raisonne DB2 (des années quatre-vingt-dix).

Structure actuelle, à aménager (clé primaire de T_OBJECT_ALIAS_OBA à réordonner) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
CREATE TABLE S_CATALOG.T_OBJECT_OBJ
(
      OBJ_ID      SERIAL NOT NULL ,
      OBJ_DISPLAY BOOL  NOT NULL ,
    CONSTRAINT PRK_T_OBJECT_OBJ PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
CREATE TABLE S_CATALOG.T_OBJECT_ALIAS_OBA
(
      OBA_ID      SERIAL NOT NULL ,
      OBA_NAME    VARCHAR (50) NOT NULL ,
      USUAL_ALIAS BOOL  NOT NULL ,
      OBJ_ID      INT  NOT NULL ,
    CONSTRAINT PRK_T_OBJECT_ALIAS_OBA PRIMARY KEY (OBA_ID,OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_OBJECT_ALIAS_OBA
ADD CONSTRAINT FK_T_OBJECT_ALIAS_OBA_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);

7) Je voudrais si possible éviter la bilocation quantique de télescopes.
Toutefois, je ne voudrais pas non plus que la résolution d'un problème que je ne juge pas crucial oblige à la mise en place d'une solution très complexe. Si on part sur le principe des intervalles de temps d'Allen, la clé primaire de la table T_TELESCOPE_SITE_TLS ne devrait-elle pas être le triplet (TLS_ID, EXPOSURE_START_DATETIME, EXPOSURE_TIME) et non le simple doublet (TLS_ID, EXPOSURE_TIME) ? Il me semble qu'une simple durée (EXPOSURE_TIME) ne permet pas de situer de façon absolue une durée dans le temps et qu'il faut donc au moins l'une des bornes de la durée (ici la borne de début, EXPOSURE_START_DATETIME).
A moins qu'il n'y ait eu confusion entre EXPOSURE_TIME (la durée de la pose exprimée en seconde) et EXPOSURE_START_DATETIME (la date et l'heure de début de la pose).

Dans mon esprit, le granule de « réservation » concernant un site était la journée (type DATE), mais on peut descendre plus finement au granule horodatage (date et heure), c'est-à-dire utiliser le type TIMESTAMP. Vous trouverez un exemple dans la doc PostgreSQL, où il est montré comment interdire le chevauchement quant à la réservation d’une salle (voyez le paragraphe 8.17.10 in Range Types).

JMerise génère le script en plaçant effectivement un paquet d'ALTER TABLE à la fin, comme vous le faisiez.

Non, je n’ai jamais procédé ainsi ! J’ai vu des gens et des outils le faire, nuance ! En vérité, quand vos codez :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE S_CATALOG.T_OBJECT_ALIAS_OBA(
      OBA_ID      SERIAL NOT NULL ,
      OBA_NAME    VARCHAR (50) NOT NULL ,
      USUAL_ALIAS BOOL  NOT NULL ,
      OBJ_ID      INT  NOT NULL ,
    CONSTRAINT PRK_T_OBJECT_ALIAS_OBA PRIMARY KEY (OBA_ID,OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_OBJECT_ALIAS_OBA
ADD CONSTRAINT FK_T_OBJECT_ALIAS_OBA_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);

De mon côté, je fais l’économie de l’ALTER TABLE :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE S_CATALOG.T_OBJECT_ALIAS_OBA
(
       OBA_ID      SERIAL NOT NULL ,
       OBA_NAME    VARCHAR (50) NOT NULL ,
       USUAL_ALIAS BOOL  NOT NULL ,
       OBJ_ID      INT  NOT NULL ,
     CONSTRAINT PRK_T_OBJECT_ALIAS_OBA PRIMARY KEY (OBJ_ID, OBA_ID)
   , CONSTRAINT FK_T_OBJECT_ALIAS_OBA_OBJ_ID FOREIGN KEY (OBJ_ID)
         REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID)
) ;

A suivre...

[fsmrel]

Un petit test concernant la bilocation :

On crée l’extension btree_gist et la table TELESCOPE_SITE, de clé primaire {TLS_ID, TLS_PERIODE}, avec la contrainte interdisant les chevauchements. Dans l’exemple ci-dessous, à une seconde près on se fait jeter :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SET SCHEMA 'fsmrel' ;
 
DROP EXTENSION IF EXISTS BTREE_GIST CASCADE ;
DROP TABLE IF EXISTS T_TELESCOPE_SITE_TLS CASCADE ;
 
CREATE EXTENSION BTREE_GIST ;
 
CREATE TABLE T_TELESCOPE_SITE_TLS
(
        TLS_ID          SERIAL       NOT NULL
      , TLS_PERIODE     TSRANGE      NOT NULL
      , SIT_ID          INT          NOT NULL
      , TLP_ID          INT          NOT NULL
    , CONSTRAINT TELESCOPE_SITE_TLS_PK PRIMARY KEY (TLS_ID, TLS_PERIODE)
    , EXCLUDE USING GIST (TLS_ID WITH =, TLS_PERIODE WITH &&)
);
 
INSERT INTO T_TELESCOPE_SITE_TLS (TLS_PERIODE, SIT_ID, TLP_ID)
        VALUES ('[2018-05-01 01:00, 2018-05-01 23:59:58]', 1, 1) ;
 
INSERT INTO T_TELESCOPE_SITE_TLS (TLS_ID, TLS_PERIODE, SIT_ID, TLP_ID) 
        VALUES (1, '[2018-05-01 23:59:58, 2018-07-02 09:00]', 2, 1) ;
 
SELECT * FROM T_TELESCOPE_SITE_TLS ;

Le SGBD nous remonte les bretelles :

« ERREUR: la valeur d'une clé en conflit rompt la contrainte d'exclusion « t_telescope_site_tls_tls_id_tls_periode_excl »
DETAIL: La clé (tls_id, tls_periode)=(1, ["2018-05-01 23:59:58","2018-07-02 09:00:00"]) est en conflit avec la clé existante (tls_id, tls_periode)=(1, ["2018-05-01 01:00:00","2018-05-01 23:59:58"]) »

N.B. Dans le 1er INSERT, j’ai laissé PostgreSQL fournir la valeur de TLS_ID, à savoir « 1 ». Dans le 2e INSERT, j’ai court-circuité le type SERIAL et forcé la valeur de TLS_ID à « 1 », sinon PostgreSQL fournit la valeur « 2 » et donc il n’y a pas recouvrement...

J’en conclus qu’on est en train de modéliser un cautère sur une jambe de bois !

Il faut mettre en oeuvre ce que j’avais proposé en son temps, à savoir que la clé primaire de la table T_TELESCOPE_SITE_TLS n’est pas la paire {TLS_ID, TLS_PERIODE}, mais bien la paire {TLP_ID, TLS_PERIODE} :

Well! Il y a là un consensus ! Nos observations (pas du ciel !) sont en phase (pas de la lune !) : à une date donnée l’objet qui sert à pondre les spectres est installé à tel endroit, sans bilocation possible ni comportement quantique quelconque...

L’attribut TLS_ID est rendu sans emploi, et au nom du rasoir d’Ockham, il disparaît ; la structure de la table devient donc :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE TABLE T_TELESCOPE_SITE_TLS
(
        TLP_ID          INT          NOT NULL
      , TLS_PERIODE     TSRANGE      NOT NULL
      , SIT_ID          INT          NOT NULL
    , CONSTRAINT TELESCOPE_SITE_TLS_PK PRIMARY KEY (TLP_ID, TLS_PERIODE)
    , EXCLUDE USING GIST (TLP_ID WITH =, TLS_PERIODE WITH &&)
) ;

Pour tester :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
SET SCHEMA 'fsmrel' ;
 
DROP EXTENSION IF EXISTS BTREE_GIST CASCADE ;
DROP TABLE IF EXISTS T_TELESCOPE_SITE_TLS CASCADE ;
 
CREATE EXTENSION BTREE_GIST ;
 
CREATE TABLE T_TELESCOPE_SITE_TLS
(
        TLP_ID          INT          NOT NULL
      , TLS_PERIODE     TSRANGE      NOT NULL
      , SIT_ID          INT          NOT NULL
    , CONSTRAINT TELESCOPE_SITE_TLS_PK PRIMARY KEY (TLP_ID, TLS_PERIODE)
    , EXCLUDE USING GIST (TLP_ID WITH =, TLS_PERIODE WITH &&)
);
 
INSERT INTO T_TELESCOPE_SITE_TLS (TLP_ID, TLS_PERIODE, SIT_ID)
        VALUES (314, '[2018-05-01 01:00, 2018-05-01 23:59:58]', 1) ;
 
INSERT INTO T_TELESCOPE_SITE_TLS (TLP_ID, TLS_PERIODE, SIT_ID)
        VALUES (314, '[2018-05-01 23:59:58, 2018-07-02 09:00]', 2) ;
 
SELECT * FROM T_TELESCOPE_SITE_TLS ;

Evidemment la clé étrangère des tables branchées sur T_TELESCOPE_SITE_TLS est à modifier en conséquence, cas notamment de T_SPECTRUM_SPM...

[aras-vbo]

Je me permets un petit retour arrière sur le MCD. Dans la version finale du MCD, Vincent, tu n'as pas tenu compte de ma remarque portant sur la modélisation de la magnitude (post #33)
Est-ce à dire que j'aurais mal compris la problématique ou que cette modélisation ne convient pas ?

Bonsoir JPhi33,

En fait, il s'agit d'un oubli de ma part
La solution que tu proposes est tout à fait acceptable et plus souple que celle que j'avais retenue. J'ai donc modifié le MCD en conséquence (et donc le script qui en découle, que je vais insérer dans la réponse à François, ci-dessous).

Voici le MCD, avec les modifications que tu suggères et celles que suggère François sur l'identification relative (suppression de celle des composants d'un télescope et ajout de celle des pages web).



Vincent

[aras-vbo]

François,

Etant donné qu'une page web dépend exclusivement de l'astre qu'elle décrit, j'adopte le principe de la suppression de la clé WPG_ID et je la substitue par OBJ_ID ; je m'aperçois d'ailleurs que j'ai modifié le script sans avoir modifié le MCD publié dans ma réponse à JPhi33... (ce qui m'a été plus ou moins imposé par JMerise qui m'interdit la conversion MCD -> MLD -> script dans le cas d'une relation avec des cardinalités 0,1 -- (1,1) dans la version que j'utilise).

L'essentiel des requêtes porteront sans doute sur les objets (et donc utiliseront la clé OBJ_ID) donc clusteriser toutes les tables où cette clé apparaît me semble une bonne idée.
Suite à vos explications, j'ai donc réorganisé les clés où OBJ_ID apparaît : alias, magnitude, spectres, catégories (quoique cette dernière ne soit pas forcément très pertinente puisque la recherche est plutôt inverse) de façon à ce que l'index soit lui-même cluster.
La difficulté de la clusterisation vient du fait qu'elle n'est pas automatique et qu'il convient de la lancer régulièrement sur les tables concernées (et de relancer aussi un ANALYSE à la suite).

Je découvre pas mal de commandes et de subtilités de SQL, dont EXPLAIN qui semble très puissante (mais qui demanderait sans doute des tutos entiers pour être maîtrisée). Je vais prendre un peu de temps pour me documenter sur le sujet.

Il va aussi falloir que je me plonge dans le type RANGE pour en apprendre un peu plus sur son utilisation.
Vous utilisez TSRANGE mais, ne disposant que de la borne inférieure (de type TIMESTAMP) et du temps d'exposition, et non de la borne supérieure (de type TIMESTAMP), est-ce utilisable quand même ? Est-ce que l'opérateur OVERLAPS ne suffirait pas à vérifier que des périodes de pose avec le même instrument sur le même site ne se chevauchent pas ? Quelles sont les différences entre ces 2 façons de gérer les intervalles de temps ?
Je dois aussi faire avec les contraintes d'hébergement. Si certaines fonctionnalités ne sont pas accessibles (le module btree_gist, par exemple). Tout ceci n'est pas simple.

Le script modifié, sans la prise en compte de la bilocation :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
-------------------------------------------------------------
-- ARAS DATABASE v0.0.4 - PostgreSQL
------------------------------------------------------------
 
------------------------------------------------------------
-- SCHEMAS
------------------------------------------------------------
CREATE SCHEMA S_SITE;
CREATE SCHEMA S_USER;
CREATE SCHEMA S_TELESCOPE;
CREATE SCHEMA S_SPECTRUM;
CREATE SCHEMA S_CATALOG;
CREATE SCHEMA S_WEBSITE;
 
------------------------------------------------------------
-- DOMAINS
------------------------------------------------------------
 
-- Code of the observation site (ARAS code)
CREATE DOMAIN D_A_SITECODE
AS CHAR(6)
CONSTRAINT CK_D_A_SITECODE CHECK (VALUE IS NOT NULL AND
																	VALUE = UPPER(VALUE) AND
																	VALUE ~* '#^[A-Z]{3}-[A-Z]{2}$#')
COLLATE SQL_ASCII;
 
-- Latitude of the observation site (BSS_LAT)
CREATE DOMAIN D_N_LATITUDE
AS NUMERIC(10,7)
CONSTRAINT CK_D_N_LATITUDE CHECK (VALUE IS NOT NULL AND
																	VALUE BETWEEN -90 AND +90);
 
-- Longitude of the observation site (BSS_LONG)
CREATE DOMAIN D_N_LONGITUDE
AS NUMERIC(10,7)
CONSTRAINT CK_D_N_LONGITUDE CHECK (VALUE IS NOT NULL AND
																	VALUE BETWEEN 0 AND 360);
 
-- Altitude of the observation site (BSS_ELEV)
CREATE DOMAIN D_N_ALTITUDE
AS NUMERIC(6,2)
CONSTRAINT CK_D_N_ALTITUDE CHECK (VALUE IS NOT NULL AND
																	VALUE BETWEEN 0 AND 9000);
 
-- User Email
CREATE DOMAIN D_A_EMAIL
AS VARCHAR(255)
CONSTRAINT CK_D_A_EMAIL CHECK (VALUE IS NOT NULL AND
															VALUE = LOWER(VALUE) AND
															VALUE ~* '(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])')
COLLATE SQL_ASCII;
 
-- User Website
CREATE DOMAIN D_A_WEBSITE
AS VARCHAR(255)
CONSTRAINT CK_D_A_WEBSITE CHECK (VALUE IS NOT NULL AND
																VALUE = LOWER(VALUE) AND
																VALUE ~* '@^(http\:\/\/|https\:\/\/)?([a-z0-9][a-z0-9\-]*\.)+[a-z0-9][a-z0-9\-]*$@i')
COLLATE SQL_ASCII;
 
-- User Code (ARAS)
CREATE DOMAIN D_A_USERCODE
AS CHAR(3)
CONSTRAINT CK_D_A_USERCODE CHECK (VALUE IS NOT NULL AND
																	VALUE = UPPER(VALUE) AND
																	VALUE ~* '#^[A-Z]{3}$#')
COLLATE SQL_ASCII;
 
-- Right Ascension of an object
CREATE DOMAIN D_N_RIGHTASCENSION
AS NUMERIC(10,7)
CONSTRAINT CK_D_N_RIGHTASCENSION CHECK (VALUE IS NOT NULL AND
																				VALUE BETWEEN 0 AND 360);
 
-- Declination of an object
CREATE DOMAIN D_N_DECLINATION
AS NUMERIC(8,6)
CONSTRAINT CK_D_N_DECLINATION CHECK (VALUE IS NOT NULL AND
																		VALUE BETWEEN -90 AND 90);
 
-- Magnitude of an object
CREATE DOMAIN D_N_MAGNITUDE
AS NUMERIC(4,2)
CONSTRAINT CK_D_N_MAGNITUDE CHECK (VALUE IS NOT NULL AND
																	VALUE BETWEEN -27 AND 30);
 
-- Spectrum Status
CREATE DOMAIN D_A_SPMSTATUS
AS VARCHAR(50)
CONSTRAINT CK_D_A_SPMSTATUS CHECK (VALUE IS NOT NULL AND
																	VALUE IN ('validated','in progress','refused'))
COLLATE SQL_ASCII;
 
-- Flux calibration
CREATE DOMAIN D_A_FLXVALUE
AS VARCHAR(30)
DEFAULT 'relative'
CONSTRAINT CK_D_A_FLXVALUE CHECK (VALUE IS NOT NULL AND
																	VALUE IN 'relative', 'erg/cm^2/s/A')
COLLATE SQL_ASCII;
 
-- Applied Heliocentric Correction (BSS_VHEL)
CREATE DOMAIN D_N_HELIOCORRECTION
AS DECIMAL(6,3)
DEFAULT 0.0
CONSTRAINT CK_D_N_HELIOCORRECTION CHECK (VALUE IS NOT NULL AND
																				VALUE BETWEEN -200 AND 200);
 
 
 
 
------------------------------------------------------------
-- SCHEMA CATALOG
------------------------------------------------------------
 
------------------------------------------------------------
-- Table OBJECT : T_OBJECT_OBJ
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_OBJECT_OBJ(
	OBJ_ID      SERIAL NOT NULL ,
	OBJ_DISPLAY BOOL  NOT NULL ,
	CONSTRAINT PRK_T_OBJECT_OBJ PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table OBJECT ALIAS : T_OBJECT_ALIAS_OBA
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_OBJECT_ALIAS_OBA(
	OBA_ID      SERIAL NOT NULL ,
	OBA_NAME    VARCHAR (50) NOT NULL ,
	USUAL_ALIAS BOOL  NOT NULL ,
	OBJ_ID      INT  NOT NULL ,
	CONSTRAINT PRK_T_OBJECT_ALIAS_OBA PRIMARY KEY (OBJ_ID,OBA_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_OBJECT_ALIAS_OBA
ADD CONSTRAINT FK_T_OBJECT_ALIAS_OBA_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
 
------------------------------------------------------------
-- Table OBJECT COORDINATES : T_COORDINATE_COO
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_COORDINATE_COO(
	COO_ID      SERIAL NOT NULL ,
	COO_RA      D_N_RIGHTASCENSION NOT NULL ,
	COO_DEC     D_N_DECLINATION NOT NULL ,
	COO_EQUINOX FLOAT  NOT NULL ,
	COO_SYS     CHAR (4)  NOT NULL ,
	OBJ_ID      INT  NOT NULL ,
	CONSTRAINT PRK_T_COORDINATE_COO PRIMARY KEY (COO_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_COORDINATE_COO
ADD CONSTRAINT FK_T_COORDINATE_COO_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
 
------------------------------------------------------------
-- Table MAGNITUDE TYPE : T_MAG_TYPE_MGT
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_MAG_TYPE_MGT(
	MGT_ID   SERIAL NOT NULL ,
	MGT_TYPE VARCHAR (50) NOT NULL ,
	CONSTRAINT PRK_T_MAG_TYPE_MGT PRIMARY KEY (MGT_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table MAGNITUDE : T_L_OBJ_MGT
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_L_OBJ_MGT(
	MAGNITUDE_VALUE D_N_MAGNITUDE NOT NULL ,
	OBJ_ID          INT  NOT NULL ,
	MGT_ID          INT  NOT NULL ,
	CONSTRAINT PRK_T_L_OBJ_MGT PRIMARY KEY (OBJ_ID,MGT_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_L_OBJ_MGT
ADD CONSTRAINT FK_T_L_OBJ_MGT_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
ALTER TABLE S_CATALOG.T_L_OBJ_MGT
ADD CONSTRAINT FK_T_L_OBJ_MGT_MGT_ID FOREIGN KEY (MGT_ID)
REFERENCES S_CATALOG.T_MAG_TYPE_MGT(MGT_ID);
 
 
------------------------------------------------------------
-- Table héritée STAR : T_STAR_STR
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_STAR_STR(
	OBJ_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_STAR_STR PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_STAR_STR
ADD CONSTRAINT FK_T_STAR_STR_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
 
------------------------------------------------------------
-- Table héritée VARIABLE STAR : T_VARIABLE_STAR_VST
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_VARIABLE_STAR_VST(
	OBJ_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_VARIABLE_STAR_VST PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_VARIABLE_STAR_VST
ADD CONSTRAINT FK_T_VARIABLE_STAR_VST_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_STAR_STR (OBJ_ID);
 
 
------------------------------------------------------------
-- Table VARIABLE PERIODE : T_VAR_PERIODE_VPE
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_VAR_PERIODE_VPE(
	VPE_ID    SERIAL NOT NULL ,
	VPE_VALUE INT  NOT NULL ,
	OBJ_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_VAR_PERIODE_VPE PRIMARY KEY (VPE_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_VAR_PERIODE_VPE
ADD CONSTRAINT FK_T_VAR_PERIODE_VPE_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_VARIABLE_STAR_VST (OBJ_ID);
 
 
------------------------------------------------------------
-- Table CATEGORY : T_CATEGORY_CTG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_CATEGORY_CTG(
	CTG_ID   SERIAL NOT NULL ,
	CTG_NAME VARCHAR (50) NOT NULL ,
	CONSTRAINT PRK_T_CATEGORY_CTG PRIMARY KEY (CTG_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison CTG-CTG : T_L_CTG_CTG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_L_CTG_CTG(
	CTG_ID                INT  NOT NULL ,
	CTG_ID_T_CATEGORY_CTG INT  NOT NULL ,
	CONSTRAINT PRK_T_L_CTG_CTG PRIMARY KEY (CTG_ID,CTG_ID_T_CATEGORY_CTG)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_L_CTG_CTG
ADD CONSTRAINT FK_T_L_CTG_CTG_CTG_ID FOREIGN KEY (CTG_ID)
REFERENCES S_CATALOG.T_CATEGORY_CTG (CTG_ID);
 
ALTER TABLE S_CATALOG.T_L_CTG_CTG
ADD CONSTRAINT FK_T_L_CTG_CTG_CTG_ID_T_CATEGORY_CTG FOREIGN KEY (CTG_ID_T_CATEGORY_CTG)
REFERENCES S_CATALOG.T_CATEGORY_CTG (CTG_ID);
 
 
------------------------------------------------------------
-- Table de liaison OBJECT-CATEGORY : T_L_OBJ_CTG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_L_OBJ_CTG(
	CTG_ID INT  NOT NULL ,
	OBJ_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_L_OBJ_CTG PRIMARY KEY (OBJ_ID,CTG_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_L_OBJ_CTG
ADD CONSTRAINT FK_T_L_OBJ_CTG_CTG_ID FOREIGN KEY (CTG_ID)
REFERENCES S_CATALOG.T_CATEGORY_CTG (CTG_ID);
 
ALTER TABLE S_CATALOG.T_L_OBJ_CTG
ADD CONSTRAINT FK_T_L_OBJ_CTG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
 
 
 
------------------------------------------------------------
-- SCHEMA USER
------------------------------------------------------------
 
------------------------------------------------------------
-- Table USER : T_USER_USR
------------------------------------------------------------
CREATE TABLE S_USER.T_USER_USR(
	USR_ID        SERIAL NOT NULL ,
	USR_CODE      D_A_USERCODE NOT NULL UNIQUE,
	USR_LASTNAME  VARCHAR (100) NOT NULL ,
	USR_FIRSTNAME VARCHAR (100) NOT NULL ,
	ROL_ID        INT  NOT NULL ,
	CONSTRAINT PRK_T_USER_USR PRIMARY KEY (USR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_USER_USR
ADD CONSTRAINT FK_T_USER_USR_ROL_ID FOREIGN KEY (ROL_ID)
REFERENCES S_USER.T_ROLE_ROL (ROL_ID);
 
 
------------------------------------------------------------
-- Table USER ALIAS : T_USER_ALIAS_USA
------------------------------------------------------------
CREATE TABLE S_USER.T_USER_ALIAS_USA(
	USA_ID   SERIAL NOT NULL ,
	USA_NAME VARCHAR (255) NOT NULL,
	USR_ID   INT  NOT NULL ,
	CONSTRAINT PRK_T_USER_ALIAS_USA PRIMARY KEY (USA_ID,USR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_USER_ALIAS_USA
ADD CONSTRAINT FK_T_USER_ALIAS_USA_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
 
------------------------------------------------------------
-- Table USER EMAIL : T_EMAIL_EML
------------------------------------------------------------
CREATE TABLE S_USER.T_EMAIL_EML(
	EML_ID    SERIAL NOT NULL ,
	EML_EMAIL D_A_EMAIL NOT NULL UNIQUE,
	EML_ISDISPLAYED BOOL NOT NULL DEFAULT FALSE,
	EML_ISVALIDATED BOOL NOT NULL DEFAULT FALSE,
	USR_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_EMAIL_EML PRIMARY KEY (EML_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_EMAIL_EML
ADD CONSTRAINT FK_T_EMAIL_EML_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
 
------------------------------------------------------------
-- Table USER WEBSITE : T_WEBSITE_WEB
------------------------------------------------------------
CREATE TABLE S_USER.T_WEBSITE_WEB(
	WEB_ID  SERIAL NOT NULL ,
	WEB_URL D_A_WEBSITE NOT NULL ,
	CONSTRAINT PRK_T_WEBSITE_WEB PRIMARY KEY (WEB_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison USER-WEBSITE : T_L_USR_WEB
------------------------------------------------------------
CREATE TABLE S_USER.T_L_USR_WEB(
	USR_ID INT  NOT NULL ,
	WEB_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_L_USR_WEB PRIMARY KEY (USR_ID,WEB_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_L_USR_WEB
ADD CONSTRAINT FK_T_L_USR_WEB_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_USER.T_L_USR_WEB
ADD CONSTRAINT FK_T_L_USR_WEB_WEB_ID FOREIGN KEY (WEB_ID)
REFERENCES S_USER.T_WEBSITE_WEB (WEB_ID);
 
 
------------------------------------------------------------
-- Table de liaison USER-COUNTRY : T_L_USR_CNY
------------------------------------------------------------
CREATE TABLE S_USER.T_L_USR_CNY(
	USR_ID INT  NOT NULL ,
	CNY_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_L_USR_CNY PRIMARY KEY (USR_ID,CNY_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_L_USR_CNY
ADD CONSTRAINT FK_T_L_USR_CNY_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_USER.T_L_USR_CNY
ADD CONSTRAINT FK_T_L_USR_CNY_CNY_ID FOREIGN KEY (CNY_ID)
REFERENCES S_SITE.T_COUNTRY_CNY (CNY_ID);
 
 
------------------------------------------------------------
-- Table USER ROLE : T_ROLE_ROL
------------------------------------------------------------
CREATE TABLE S_USER.T_ROLE_ROL(
	ROL_ID   SERIAL NOT NULL ,
	ROL_NAME VARCHAR (100) NOT NULL UNIQUE,
	CONSTRAINT PRK_T_ROLE_ROL PRIMARY KEY (ROL_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison USER-TELESCOPE-SITE : T_L_USE
------------------------------------------------------------
CREATE TABLE S_USER.T_L_USE(
	T_L_USE_MAIN                BOOL  NOT NULL ,
	USR_ID                      INT  NOT NULL ,
	TLS_ID                      INT  NOT NULL ,
	SPM_EXPOSURE_START_DATETIME TIMESTAMP  NOT NULL ,
	CONSTRAINT PRK_T_L_USE PRIMARY KEY (USR_ID,TLS_ID,SPM_EXPOSURE_START_DATETIME)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_L_USE
ADD CONSTRAINT FK_T_L_USE_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_USER.T_L_USE
ADD CONSTRAINT FK_T_L_USE_TLS_ID FOREIGN KEY (TLS_ID)
REFERENCES S_SITE.T_TELESCOPE_SITE_TLS (TLS_ID);
 
ALTER TABLE S_USER.T_L_USE
ADD CONSTRAINT FK_T_L_USE_SPM_EXPOSURE_START_DATETIME FOREIGN KEY (SPM_EXPOSURE_START_DATETIME)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_EXPOSURE_START_DATETIME);
 
 
 
 
------------------------------------------------------------
-- SCHEMA TELESCOPE
------------------------------------------------------------
 
------------------------------------------------------------
-- Table OPTICAL TUBE : T_OPTICAL_TUBE_OTA
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_OPTICAL_TUBE_OTA(
	OTA_ID   SERIAL NOT NULL ,
	OTA_NAME VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_OPTICAL_TUBE_OTA PRIMARY KEY (OTA_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table SPECTROGRAPH : T_SPECTROGRAPH_SPG
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_SPECTROGRAPH_SPG(
	SPG_ID   SERIAL NOT NULL ,
	SPG_NAME VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_SPECTROGRAPH_SPG PRIMARY KEY (SPG_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table SENSOR : T_SENSOR_SNR
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_SENSOR_SNR(
	SNR_ID   SERIAL NOT NULL ,
	SNR_NAME VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_SENSOR_SNR PRIMARY KEY (SNR_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table TELESCOPE : T_TELESCOPE_TLP
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_TELESCOPE_TLP(
	TLP_ID   SERIAL NOT NULL ,
	TLP_CODE VARCHAR (25)  ,
	TLP_NAME VARCHAR (250) NOT NULL ,
	OTA_ID   INT  NOT NULL ,
	SPG_ID   INT  NOT NULL ,
	SNR_ID   INT  NOT NULL ,
	CONSTRAINT PRK_T_TELESCOPE_TLP PRIMARY KEY (TLP_ID,OTA_ID,SPG_ID,SNR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_TELESCOPE.T_TELESCOPE_TLP
ADD CONSTRAINT FK_T_TELESCOPE_TLP_OTA_ID FOREIGN KEY (OTA_ID)
REFERENCES S_TELESCOPE.T_OPTICAL_TUBE_OTA (OTA_ID);
 
ALTER TABLE S_TELESCOPE.T_TELESCOPE_TLP
ADD CONSTRAINT FK_T_TELESCOPE_TLP_SPG_ID FOREIGN KEY (SPG_ID)
REFERENCES S_TELESCOPE.T_SPECTROGRAPH_SPG (SPG_ID);
 
ALTER TABLE S_TELESCOPE.T_TELESCOPE_TLP
ADD CONSTRAINT FK_T_TELESCOPE_TLP_SNR_ID FOREIGN KEY (SNR_ID)
REFERENCES S_TELESCOPE.T_SENSOR_SNR (SNR_ID);
 
 
 
------------------------------------------------------------
-- SCHEMA SITE
------------------------------------------------------------
 
------------------------------------------------------------
-- Table COUNTRY : T_COUNTRY_CNY
------------------------------------------------------------
CREATE TABLE S_SITE.T_COUNTRY_CNY(
	CNY_ID      SERIAL NOT NULL,
	CNY_ISOALPHA2 CHAR (2) NOT NULL UNIQUE,
	CNY_ISOALPHA3 CHAR (3) NOT NULL UNIQUE,
	CNY_NAME    VARCHAR (255) NOT NULL ,
	CONSTRAINT PRK_T_COUNTRY_CNY PRIMARY KEY (CNY_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table SITE : T_SITE_SIT
------------------------------------------------------------
CREATE TABLE S_SITE.T_SITE_SIT(
	SIT_ID        SERIAL NOT NULL ,
	SIT_CODE      D_A_SITECODE NOT NULL UNIQUE,
	SIT_LATITUDE  D_N_LATITUDE NOT NULL ,
	SIT_LONGITUDE D_N_LONGITUDE NOT NULL ,
	SIT_ALTITUDE  D_N_ALTITUDE NOT NULL ,
	CNY_ID        INT  NOT NULL ,
	CONSTRAINT PRK_T_SITE_SIT PRIMARY KEY (SIT_ID)
) WITHOUT OIDS;
 
ALTER TABLES_SITE.T_SITE_SIT
ADD CONSTRAINT FK_T_SITE_SIT_CNY_ID FOREIGN KEY (CNY_ID)
REFERENCES S_SITE.T_COUNTRY_CNY (CNY_ID);
 
 
------------------------------------------------------------
-- Table SITE ALIAS : T_SITE_ALIAS_SIA
------------------------------------------------------------
CREATE TABLE S_SITE.T_SITE_ALIAS_SIA(
	SIA_ID   SERIAL NOT NULL ,
	SIA_NAME VARCHAR (100) NOT NULL ,
	SIT_ID   INT  NOT NULL ,
	CONSTRAINT PRK_T_SITE_ALIAS_SIA PRIMARY KEY (SIA_ID,SIT_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SITE.T_SITE_ALIAS_SIA
ADD CONSTRAINT FK_T_SITE_ALIAS_SIA_SIT_ID FOREIGN KEY (SIT_ID)
REFERENCES S_SITE.T_SITE_SIT (SIT_ID);
 
 
------------------------------------------------------------
-- Table TELESCOPE-SITE : T_TELESCOPE_SITE_TLS
------------------------------------------------------------
CREATE TABLE S_SITE.T_TELESCOPE_SITE_TLS(
	TLS_ID                      SERIAL NOT NULL ,
	SIT_ID                      INT  NOT NULL ,
	TLP_ID                      INT  NOT NULL ,
	CONSTRAINT PRK_T_TELESCOPE_SITE_TLS PRIMARY KEY (TLS_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SITE.T_TELESCOPE_SITE_TLS
ADD CONSTRAINT FK_T_TELESCOPE_SITE_TLS_SIT_ID FOREIGN KEY (SIT_ID)
REFERENCES S_SITE.T_SITE_SIT (SIT_ID);
 
ALTER TABLE S_SITE.T_TELESCOPE_SITE_TLS
ADD CONSTRAINT FK_T_TELESCOPE_SITE_TLS_TLP_ID FOREIGN KEY (TLP_ID)
REFERENCES S_TELESCOPE.T_TELESCOPE_TLP (TLP_ID);
 
 
 
 
------------------------------------------------------------
-- SCHEMA SPECTRUM
------------------------------------------------------------
 
------------------------------------------------------------
-- Table SPECTRUM : T_SPECTRUM_SPM
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_SPECTRUM_SPM(
	SPM_ID                      SERIAL NOT NULL ,
	SPM_FITS_FILE               VARCHAR (100) NOT NULL ,
	SPM_HELIO_CORRECTION        D_N_HELIOCORRECTION NOT NULL ,
	SPM_TELLURIC_CORRECTION     CHAR (50)  NOT NULL ,
	SPM_COSMIC_REMOVAL          CHAR (50)  NOT NULL ,
	SPM_NORMALISATION           CHAR (50)  NOT NULL ,
	SPM_DISPLAY                 BOOL  NOT NULL ,
	OBJ_ID                      INT  NOT NULL ,
	EXPOSURE_TIME               INT  NOT NULL ,
	TLS_ID                      INT  NOT NULL ,
	SPM_EXPOSURE_START_DATETIME TIMESTAMP  NOT NULL ,
	CONSTRAINT PRK_T_SPECTRUM_SPM PRIMARY KEY (OBJ_ID,SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_SPECTRUM_SPM
ADD CONSTRAINT FK_T_SPECTRUM_SPM_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
ALTER TABLE S_SPECTRUM.T_SPECTRUM_SPM
ADD CONSTRAINT FK_T_SPECTRUM_SPM_TLS_ID FOREIGN KEY (TLS_ID)
REFERENCES S_SITE.T_TELESCOPE_SITE_TLS (TLS_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM RESOLVING POWER : T_RESOLVING_POWER_RPO
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_RESOLVING_POWER_RPO(
	RPO_VALUE DECIMAL (6,2)  NOT NULL ,
	SPM_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_RESOLVING_POWER_RPO PRIMARY KEY (SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_RESOLVING_POWER_RPO
ADD CONSTRAINT FK_T_RESOLVING_POWER_RPO_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM FLUX : T_FLUX_FLX
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_FLUX_FLX(
	FLX_VALUE D_A_FLXVALUE NOT NULL ,
	SPM_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_FLUX_FLX PRIMARY KEY (SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_FLUX_FLX
ADD CONSTRAINT FK_T_FLUX_FLX_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table héritée ECHELLE SPECTRUM : T_ECHELLE_SPECTRUM_ESP
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_ECHELLE_SPECTRUM_ESP(
	ESP_ECHELLE_ORD CHAR (100)  NOT NULL ,
	SPM_ID          INT  NOT NULL ,
	CONSTRAINT PRK_T_ECHELLE_SPECTRUM_ESP PRIMARY KEY (SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_ECHELLE_SPECTRUM_ESP
ADD CONSTRAINT FK_T_ECHELLE_SPECTRUM_ESP_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM SERIES : T_SPM_SERIE_SPS
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_SPM_SERIE_SPS(
	SPS_ID       SERIAL NOT NULL ,
	SPS_ZIP_FILE VARCHAR (255) NOT NULL ,
	CONSTRAINT PRK_T_SPM_SERIE_SPS PRIMARY KEY (SPS_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison SPECTRUM-SERIES : T_L_SPS_SPM
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_L_SPS_SPM(
	SPS_ID 			INT NOT NULL,
	SPM_ID 			INT NOT NULL,
	CONSTRAINT PRK_T_L_SPS_SPM PRIMARY KEY (SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_L_SPS_SPM
ADD CONSTRAINT FK_T_L_SPS_SPM_SPS_ID FOREIGN KEY (SPS_ID)
REFERENCES S_SPECTRUM.T_SPM_SERIE_SPS (SPS_ID);
 
ALTER TABLE S_SPECTRUM.T_L_SPS_SPM
ADD CONSTRAINT FK_T_L_SPS_SPM_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM SUBMISSION : T_L_SUBMIT_SPM
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_L_SUBMIT_SPM(
	SPM_SUBMISSION_DATE DATE  NOT NULL ,
	USR_ID              INT  NOT NULL ,
	SPM_ID              INT  NOT NULL ,
	CONSTRAINT PRK_T_L_SUBMIT_SPM PRIMARY KEY (USR_ID,SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_L_SUBMIT_SPM
ADD CONSTRAINT FK_T_L_SUBMIT_SPM_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_SPECTRUM.T_L_SUBMIT_SPM
ADD CONSTRAINT FK_T_L_SUBMIT_SPM_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM STATUS : T_STATUS_STA
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_STATUS_STA(
	STA_ID                SERIAL NOT NULL ,
	STA_VALUE             D_A_SPMSTATUS NOT NULL ,
	SPM_MODIF_STATUS_DATE DATE  NOT NULL ,
	USR_ID                INT  NOT NULL ,
	SPM_ID                INT  NOT NULL ,
	CONSTRAINT PRK_T_STATUS_STA PRIMARY KEY (STA_ID,USR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_STATUS_STA
ADD CONSTRAINT FK_T_STATUS_STA_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_SPECTRUM.T_STATUS_STA
ADD CONSTRAINT FK_T_STATUS_STA_SPM_ID FOREIGN KEY (SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_ID);
 
 
 
 
------------------------------------------------------------
-- SCHEMA WEBSITE
------------------------------------------------------------
 
------------------------------------------------------------
-- Table WEBSITE PAGES : T_WEBPAGE_WPG
------------------------------------------------------------
CREATE TABLE S_WEBSITE.T_WEBPAGE_WPG(
	WPG_URL     VARCHAR (25) NOT NULL UNIQUE,
	WPG_COMMENT VARCHAR (2000)   ,
	WPG_UPDATE  TIMESTAMP  NOT NULL ,
	WPG_DISPLAY BOOL  NOT NULL ,
	OBJ_ID      INT  NOT NULL ,
	CONSTRAINT PRK_T_WEBPAGE_WPG PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_WEBSITE.T_WEBPAGE_WPG
ADD CONSTRAINT FK_T_WEBPAGE_WPG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);

Vincent

[fsmrel]

Bonsoir Vincent, Jean-Philippe (et les autres itou),

Si deux configurations peuvent être exactement les mêmes, le triplet OTA_ID, SPG_ID et SNR_ID ne peut pas clé candidate, donc virer l’identification relative.

En conséquence de quoi, dans le script SQL, la clé primaire de la table T_TELESCOPE_TLP peut être réduite au singleton {TLP_ID}.


D’accord pour que la paire {OBJ_ID, SPM_ID} soit clé primaire de la table T_SPECTRUM_SPM, mais les tables la référençant doivent avoir une clé étrangère du même métal, {OBJ_ID, SPM_ID}, à savoir T_STATUS_STA (décidément bien chahutée, la pauvre...), T_L_SUBMIT_SPM, etc.


Une fois mise à niveau, la clé de T_L_SUBMIT_SPM sera {OBJ_ID, SPM_ID, USR_ID} ou {USR_ID, OBJ_ID, SPM_ID} : ceci vaut pour T_STATUS_STA qui, au niveau conceptuel, n’est manifestement qu’une association déguisée en entité-type. Comme aurait dit Guillaume, pourquoi dans ces conditions conserver l’attribut STA_ID ?


Attention à la boucle T_SPECTRUM_SPM > T_TELESCOPE_SITE_TLS > T_USER_USR > T_SPECTRUM_SPM : on peut partir de T_SPECTRUM_SPM ave un spectre S1, à destination de T_TELESCOPE_SITE_TLS, etc., et boucler sur T_SPECTRUM_SPM, a priori sans S1 à l’arrivée. A creuser.

Sinon, pas de problème particulier avec le script SQL (sans bilocation) qui est conforme (de ce que j’en ai vu...), aux bricoles près que j’ai signalées.

J’en profite pour passer de l’anatomie à la physiologie : vous avez peut-être noté que la déclaration d’une clé étrangère comporte des « actions de compensation », à savoir ON DELETE (et ON UPDATE) CASCADE, NO ACTION (laissons tomber SET NULL , SET DEFAULT et RESTRICT, je vous renvoie en l’occurrence à la documentation).

Quand vous codez

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER TABLE S_WEBSITE.T_WEBPAGE_WPG
ADD CONSTRAINT FK_T_WEBPAGE_WPG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);

En fait vous utilisez l’action par défaut, à savoir « NO ACTION » :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER TABLE S_WEBSITE.T_WEBPAGE_WPG
ADD CONSTRAINT FK_T_WEBPAGE_WPG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID) ON DELETE NO ACTION ;

Cela veut dire que la suppression d’une ligne L1 de la table T_OBJECT_OBJ sera refusée si cette ligne est référencée par une ligne L2 de la table T_WEBPAGE_WPG, à moins (pour faire au plus simple) qu’avant la fin de la transaction en cours, L2 n’ait été supprimée. En gros, la suppression d’une ligne parente ne peut pas être acceptée tant qu’elle a au moins un enfant. Si on code ON DELETE RESTRICT, le rejet est immédiat si la ligne parente a au moins un enfant, il n’y a aucun délai de grâce contrairement à ce qu’il se passe avec NO ACTION.

Maintenant, quid si vous codez :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER TABLE S_WEBSITE.T_WEBPAGE_WPG
ADD CONSTRAINT FK_T_WEBPAGE_WPG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID)  ON DELETE CASCADE ;

On considère que la ligne L2 ne peut pas s’opposer à la suppression de sa maman L1, la mort de L1 entraîne celle de L2. Vous me direz que c’est atroce, mais en fait qu’est-ce que T_WEBPAGE_WPG, sinon une propriété externalisée de T_OBJECT_OBJ, parce qu’optionnelle ? A moins d’un bon motif que vous opposeriez par ailleurs, au nom de quoi L2 pourrait empêcher sa propre disparition en compagnie de celle de L1 ? A défaut, il faudrait commencer par supprimer L2 avant d’en faire autant pout L1.

ON DELETE CASCADE convient pour certaines tables (par exemple T_RESOLVING_POWER_RPO, T_FLUX_FLX, T_ECHELLE_SPECTRUM_ESP) Sémantique et physiologie se rejoignent...

Supposons maintenant, même si ça n’est pas bien raisonnable, que l’on code :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CONSTRAINT FK_T_TELESCOPE_SITE_TLS_TLP_ID FOREIGN KEY (TLP_ID)
REFERENCES S_TELESCOPE.T_TELESCOPE_TLP (TLP_ID) ON DELETE CASCADE

Vous serez en droit de vous insurger, mais voyons-voir les conséquences d’une demande de suppression d’une ligne L1 de la table T_TELESCOPE_TLP : un stimulus est envoyé à destination de la table T_TELESCOPE_SITE_TLS, laquelle, avant d’accepter cette demande, envoie à son tour un stimulus aux tables T_L_USE et T_SPECTRUM_SPM. Si ces tables n’ont aucune ligne en relation avec L1, ou si elles sont porteuses elles aussi d’un ON DELETE CASCADE, alors la suppression est effective dans T_TELESCOPE_SITE_TLS (et T_L_USE et T_SPECTRUM_SPM). Par contre, si T_SPECTRUM_SPM est porteuse d’un ON DELETE NO ACTION (qui est l’option par défaut, ouf !) et fait référence à L1 (via T_TELESCOPE_SITE_TLS), alors il y a rejet de la demande de suppression de la ligne L1 de la table T_TELESCOPE_TLP.


Au sujet de la bilocation :

Je rappelle que PostgreSQL n’est pas mon SGBD, je sais me dépatouiller à peu près, mais je suis très loin d’en être un spécialiste. Ainsi, je ne me prononce pas quant à btree_gist : j’ai codé, ça a marché, aussi vous pouvez essayer à votre tour, vous avez un code prêt à l’emploi. Evidemment, le fait de disposer d’une durée et non pas d’une date de fin n’arrange pas nos affaires, il faudra peut-être prévoir une fonction permettant de calculer cette date. En attendant, exposez le problème dans le forum PostgreSQL, un caïd de passage sera peut-être à même d’apporter la bonne réponse ?

Pour l’instruction EXPLAIN : il s’agit de déterminer si les requêtes profitent des index en place (et du clustering !), ou provoquent par exemple des produits cartésiens rendant les requêtes interminables. Si vous avez des questions à ce sujet, j’essaierai d’y répondre.

Je serai absent pendant 2 ou 3 jours, mais j’essaierai quand même de vous suivre...

[aras-vbo]

En conséquence de quoi, dans le script SQL, la clé primaire de la table T_TELESCOPE_TLP peut être réduite au singleton {TLP_ID}.

Celle-ci m'avait échappée lors de ma purge. C'est corrigé.

pour que la paire {OBJ_ID, SPM_ID} soit clé primaire de la table T_SPECTRUM_SPM, mais les tables la référençant doivent avoir une clé étrangère du même métal, {OBJ_ID, SPM_ID}, à savoir T_STATUS_STA (décidément bien chahutée, la pauvre...), T_L_SUBMIT_SPM, etc.

Effectivement. J'ai répercuté la clé composite dans toutes les tables où elle apparaissait comme clé étrangère.

Une fois mise à niveau, la clé de T_L_SUBMIT_SPM sera {OBJ_ID, SPM_ID, USR_ID} ou {USR_ID, OBJ_ID, SPM_ID} : ceci vaut pour T_STATUS_STA qui, au niveau conceptuel, n’est manifestement qu’une association déguisée en entité-type. Comme aurait dit Guillaume, pourquoi dans ces conditions conserver l’attribut STA_ID ?

Est-ce que je ne risque pas d'avoir de doublons de clé si je ne mets que USR_ID, OBJ_ID et SPM_ID dans la clé, notamment si je cherche à faire l'historique du statut d'un spectre ? Si le même utilisateur change le statut d'un spectre, je me retrouve avec la même clé. Dans ce cas, je peux peut-être rajouter SPM_MODIF_STATUT_DATE dans la clé pour la rendre unique ?

Attention à la boucle T_SPECTRUM_SPM > T_TELESCOPE_SITE_TLS > T_USER_USR > T_SPECTRUM_SPM : on peut partir de T_SPECTRUM_SPM ave un spectre S1, à destination de T_TELESCOPE_SITE_TLS, etc., et boucler sur T_SPECTRUM_SPM, a priori sans S1 à l’arrivée. A creuser.

J'avoue ne pas trop bien comprendre le problème que cela engendrer...

Vincent

François,

J’en profite pour passer de l’anatomie à la physiologie : vous avez peut-être noté que la déclaration d’une clé étrangère comporte des « actions de compensation », à savoir ON DELETE (et ON UPDATE) CASCADE, NO ACTION (laissons tomber SET NULL , SET DEFAULT et RESTRICT, je vous renvoie en l’occurrence à la documentation).

Quand vous codez

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER TABLE S_WEBSITE.T_WEBPAGE_WPG
ADD CONSTRAINT FK_T_WEBPAGE_WPG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);

En fait vous utilisez l’action par défaut, à savoir « NO ACTION » :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER TABLE S_WEBSITE.T_WEBPAGE_WPG
ADD CONSTRAINT FK_T_WEBPAGE_WPG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID) ON DELETE NO ACTION ;

Cela veut dire que la suppression d’une ligne L1 de la table T_OBJECT_OBJ sera refusée si cette ligne est référencée par une ligne L2 de la table T_WEBPAGE_WPG, à moins (pour faire au plus simple) qu’avant la fin de la transaction en cours, L2 n’ait été supprimée. En gros, la suppression d’une ligne parente ne peut pas être acceptée tant qu’elle a au moins un enfant. Si on code ON DELETE RESTRICT, le rejet est immédiat si la ligne parente a au moins un enfant, il n’y a aucun délai de grâce contrairement à ce qu’il se passe avec NO ACTION.

Maintenant, quid si vous codez :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER TABLE S_WEBSITE.T_WEBPAGE_WPG
ADD CONSTRAINT FK_T_WEBPAGE_WPG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID)  ON DELETE CASCADE ;

On considère que la ligne L2 ne peut pas s’opposer à la suppression de sa maman L1, la mort de L1 entraîne celle de L2. Vous me direz que c’est atroce, mais en fait qu’est-ce que T_WEBPAGE_WPG, sinon une propriété externalisée de T_OBJECT_OBJ, parce qu’optionnelle ? A moins d’un bon motif que vous opposeriez par ailleurs, au nom de quoi L2 pourrait empêcher sa propre disparition en compagnie de celle de L1 ? A défaut, il faudrait commencer par supprimer L2 avant d’en faire autant pout L1.

ON DELETE CASCADE convient pour certaines tables (par exemple T_RESOLVING_POWER_RPO, T_FLUX_FLX, T_ECHELLE_SPECTRUM_ESP) Sémantique et physiologie se rejoignent...

Supposons maintenant, même si ça n’est pas bien raisonnable, que l’on code :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CONSTRAINT FK_T_TELESCOPE_SITE_TLS_TLP_ID FOREIGN KEY (TLP_ID)
REFERENCES S_TELESCOPE.T_TELESCOPE_TLP (TLP_ID) ON DELETE CASCADE

Vous serez en droit de vous insurger, mais voyons-voir les conséquences d’une demande de suppression d’une ligne L1 de la table T_TELESCOPE_TLP : un stimulus est envoyé à destination de la table T_TELESCOPE_SITE_TLS, laquelle, avant d’accepter cette demande, envoie à son tour un stimulus aux tables T_L_USE et T_SPECTRUM_SPM. Si ces tables n’ont aucune ligne en relation avec L1, ou si elles sont porteuses elles aussi d’un ON DELETE CASCADE, alors la suppression est effective dans T_TELESCOPE_SITE_TLS (et T_L_USE et T_SPECTRUM_SPM). Par contre, si T_SPECTRUM_SPM est porteuse d’un ON DELETE NO ACTION (qui est l’option par défaut, ouf !) et fait référence à L1 (via T_TELESCOPE_SITE_TLS), alors il y a rejet de la demande de suppression de la ligne L1 de la table T_TELESCOPE_TLP.

J'ai laissé l'action par défaut (NO ACTION) car je ne peux pas trancher sur les actions à réaliser en cas de suppression/modification d'enregistrement...

Autant le ON DELETE CASCADE a tout son intérêt dans les cas où les tables ont des relations de dépendances évidentes, autant il devient problématique dans certaines cas.

Je peux effectivement mettre ce mécanisme en place pour les tables comme T_WEBPAGE_WPG (les pages liées aux spectres), T_STATUS_STA (les statuts des spectres), toutes les propriétés liées aux spectres (T_RESOLVING_POWER_RPO, T_FLUX_FLX, T_ECHELLE_SPECTRUM_ESP, etc.) ou celles liées aux objets (magnitudes, coordonnées, période, etc.). La disparition de l'enregistrement père doit impérativement se traduire pas la suppression des lignes filles des tables liées.

Par contre, je ne m'y risquerai pas pour les entités comme les télescopes, les sites, les utilisateurs ou les catégories... Un utilisateur qui décide de supprimer son compte est désactivé mais les enregistrements qu'il a publié sont conservés. Il s'agit là surtout d'une question de conservation de la donnée enregistrée (et du maintien de sa cohérence). Si la relation télescope/site se trouve supprimée par une cascade (après la suppression d'un utilisateur, par exemple), un éventuel enregistrement orphelin perdrait purement et simplement toute sa valeur et sa cohérence (un spectre sans auteur ou sans télescope n'a aucun sens).
Je pars du principe que la suppression d'un enregistrement reste exceptionnel (un objet céleste ne disparaît pas du jour au lendemain...quoique).

Je ne le fais pas non plus pour les séries de spectres. La disparition d'une série n'entraîne pas la suppression des spectres qu'elle contient. Réciproquement, la disparition d'un spectre d'une série n'entraîne pas la suppression de la série complète ; la série est seulement amputée du spectre supprimé. Par contre, l'association entre les deux disparaît.

J'ai ajouté ces actions dans le script modifié (version 0.0.5) :

Une petite question à ce sujet ; est ce que l'action ON DELETE CASCADE doit être spécifiée dans le cas de tables héritées (T_STAR_STR ou T_VARIABLE_STAR_VST, dont la clé est héritée de T_OBJECT_OBJ) ?

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
 
-------------------------------------------------------------
-- ARAS DATABASE v0.0.5 - PostgreSQL
------------------------------------------------------------
 
------------------------------------------------------------
-- SCHEMAS
------------------------------------------------------------
CREATE SCHEMA S_SITE;
CREATE SCHEMA S_USER;
CREATE SCHEMA S_TELESCOPE;
CREATE SCHEMA S_SPECTRUM;
CREATE SCHEMA S_CATALOG;
CREATE SCHEMA S_WEBSITE;
 
------------------------------------------------------------
-- DOMAINS
------------------------------------------------------------
 
-- Code of the observation site (ARAS code)
CREATE DOMAIN D_A_SITECODE
AS CHAR(6)
CONSTRAINT CK_D_A_SITECODE CHECK (VALUE IS NOT NULL AND
																	VALUE = UPPER(VALUE) AND
																	VALUE ~* '#^[A-Z]{3}-[A-Z]{2}$#')
COLLATE SQL_ASCII;
 
-- Latitude of the observation site (BSS_LAT)
CREATE DOMAIN D_N_LATITUDE
AS NUMERIC(10,7)
CONSTRAINT CK_D_N_LATITUDE CHECK (VALUE IS NOT NULL AND
																	VALUE BETWEEN -90 AND +90);
 
-- Longitude of the observation site (BSS_LONG)
CREATE DOMAIN D_N_LONGITUDE
AS NUMERIC(10,7)
CONSTRAINT CK_D_N_LONGITUDE CHECK (VALUE IS NOT NULL AND
																	VALUE BETWEEN 0 AND 360);
 
-- Altitude of the observation site (BSS_ELEV)
CREATE DOMAIN D_N_ALTITUDE
AS NUMERIC(6,2)
CONSTRAINT CK_D_N_ALTITUDE CHECK (VALUE IS NOT NULL AND
																	VALUE BETWEEN 0 AND 9000);
 
-- User Email
CREATE DOMAIN D_A_EMAIL
AS VARCHAR(255)
CONSTRAINT CK_D_A_EMAIL CHECK (VALUE IS NOT NULL AND
															VALUE = LOWER(VALUE) AND
															VALUE ~* '(?:[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*|"(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21\x23-\x5b\x5d-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])*")@(?:(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?|\[(?:(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9]))\.){3}(?:(2(5[0-5]|[0-4][0-9])|1[0-9][0-9]|[1-9]?[0-9])|[a-z0-9-]*[a-z0-9]:(?:[\x01-\x08\x0b\x0c\x0e-\x1f\x21-\x5a\x53-\x7f]|\\[\x01-\x09\x0b\x0c\x0e-\x7f])+)\])')
COLLATE SQL_ASCII;
 
-- User Website
CREATE DOMAIN D_A_WEBSITE
AS VARCHAR(255)
CONSTRAINT CK_D_A_WEBSITE CHECK (VALUE IS NOT NULL AND
																VALUE = LOWER(VALUE) AND
																VALUE ~* '@^(http\:\/\/|https\:\/\/)?([a-z0-9][a-z0-9\-]*\.)+[a-z0-9][a-z0-9\-]*$@i')
COLLATE SQL_ASCII;
 
-- User Code (ARAS)
CREATE DOMAIN D_A_USERCODE
AS CHAR(3)
CONSTRAINT CK_D_A_USERCODE CHECK (VALUE IS NOT NULL AND
																	VALUE = UPPER(VALUE) AND
																	VALUE ~* '#^[A-Z]{3}$#')
COLLATE SQL_ASCII;
 
-- Right Ascension of an object
CREATE DOMAIN D_N_RIGHTASCENSION
AS NUMERIC(10,7)
CONSTRAINT CK_D_N_RIGHTASCENSION CHECK (VALUE IS NOT NULL AND
																				VALUE BETWEEN 0 AND 360);
 
-- Declination of an object
CREATE DOMAIN D_N_DECLINATION
AS NUMERIC(8,6)
CONSTRAINT CK_D_N_DECLINATION CHECK (VALUE IS NOT NULL AND
																		VALUE BETWEEN -90 AND 90);
 
-- Magnitude of an object
CREATE DOMAIN D_N_MAGNITUDE
AS NUMERIC(4,2)
CONSTRAINT CK_D_N_MAGNITUDE CHECK (VALUE IS NOT NULL AND
																	VALUE BETWEEN -27 AND 30);
 
-- Spectrum Status
CREATE DOMAIN D_A_SPMSTATUS
AS VARCHAR(50)
CONSTRAINT CK_D_A_SPMSTATUS CHECK (VALUE IS NOT NULL AND
																	VALUE IN ('validated','in progress','refused'))
COLLATE SQL_ASCII;
 
-- Flux calibration
CREATE DOMAIN D_A_FLXVALUE
AS VARCHAR(30)
DEFAULT 'relative'
CONSTRAINT CK_D_A_FLXVALUE CHECK (VALUE IS NOT NULL AND
																	VALUE IN 'relative', 'erg/cm^2/s/A')
COLLATE SQL_ASCII;
 
-- Applied Heliocentric Correction (BSS_VHEL)
CREATE DOMAIN D_N_HELIOCORRECTION
AS DECIMAL(6,3)
DEFAULT 0.0
CONSTRAINT CK_D_N_HELIOCORRECTION CHECK (VALUE IS NOT NULL AND
																				VALUE BETWEEN -200 AND 200);
 
 
 
 
------------------------------------------------------------
-- SCHEMA CATALOG
------------------------------------------------------------
 
------------------------------------------------------------
-- Table OBJECT : T_OBJECT_OBJ
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_OBJECT_OBJ(
	OBJ_ID      SERIAL NOT NULL ,
	OBJ_DISPLAY BOOL  NOT NULL ,
	CONSTRAINT PRK_T_OBJECT_OBJ PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table OBJECT ALIAS : T_OBJECT_ALIAS_OBA
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_OBJECT_ALIAS_OBA(
	OBA_ID      SERIAL NOT NULL ,
	OBA_NAME    VARCHAR (50) NOT NULL ,
	USUAL_ALIAS BOOL  NOT NULL ,
	OBJ_ID      INT  NOT NULL ,
	CONSTRAINT PRK_T_OBJECT_ALIAS_OBA PRIMARY KEY (OBJ_ID,OBA_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_OBJECT_ALIAS_OBA
ADD CONSTRAINT FK_T_OBJECT_ALIAS_OBA_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID) ON DELETE CASCADE;
 
 
------------------------------------------------------------
-- Table OBJECT COORDINATES : T_COORDINATE_COO
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_COORDINATE_COO(
	COO_ID      SERIAL NOT NULL ,
	COO_RA      D_N_RIGHTASCENSION NOT NULL ,
	COO_DEC     D_N_DECLINATION NOT NULL ,
	COO_EQUINOX FLOAT  NOT NULL ,
	COO_SYS     CHAR (4)  NOT NULL ,
	OBJ_ID      INT  NOT NULL ,
	CONSTRAINT PRK_T_COORDINATE_COO PRIMARY KEY (COO_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_COORDINATE_COO
ADD CONSTRAINT FK_T_COORDINATE_COO_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID) ON DELETE CASCADE;
 
 
------------------------------------------------------------
-- Table MAGNITUDE TYPE : T_MAG_TYPE_MGT
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_MAG_TYPE_MGT(
	MGT_ID   SERIAL NOT NULL ,
	MGT_TYPE VARCHAR (50) NOT NULL ,
	CONSTRAINT PRK_T_MAG_TYPE_MGT PRIMARY KEY (MGT_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table MAGNITUDE : T_L_OBJ_MGT
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_L_OBJ_MGT(
	MAGNITUDE_VALUE D_N_MAGNITUDE NOT NULL ,
	OBJ_ID          INT  NOT NULL ,
	MGT_ID          INT  NOT NULL ,
	CONSTRAINT PRK_T_L_OBJ_MGT PRIMARY KEY (OBJ_ID,MGT_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_L_OBJ_MGT
ADD CONSTRAINT FK_T_L_OBJ_MGT_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID) ON DELETE CASCADE;
 
ALTER TABLE S_CATALOG.T_L_OBJ_MGT
ADD CONSTRAINT FK_T_L_OBJ_MGT_MGT_ID FOREIGN KEY (MGT_ID)
REFERENCES S_CATALOG.T_MAG_TYPE_MGT(MGT_ID);
 
 
------------------------------------------------------------
-- Table héritée STAR : T_STAR_STR
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_STAR_STR(
	OBJ_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_STAR_STR PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_STAR_STR
ADD CONSTRAINT FK_T_STAR_STR_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
 
------------------------------------------------------------
-- Table héritée VARIABLE STAR : T_VARIABLE_STAR_VST
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_VARIABLE_STAR_VST(
	OBJ_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_VARIABLE_STAR_VST PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_VARIABLE_STAR_VST
ADD CONSTRAINT FK_T_VARIABLE_STAR_VST_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_STAR_STR (OBJ_ID);
 
 
------------------------------------------------------------
-- Table VARIABLE PERIODE : T_VAR_PERIODE_VPE
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_VAR_PERIODE_VPE(
	VPE_ID    SERIAL NOT NULL ,
	VPE_VALUE INT  NOT NULL ,
	OBJ_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_VAR_PERIODE_VPE PRIMARY KEY (VPE_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_VAR_PERIODE_VPE
ADD CONSTRAINT FK_T_VAR_PERIODE_VPE_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_VARIABLE_STAR_VST (OBJ_ID) ON DELETE CASCADE;
 
 
------------------------------------------------------------
-- Table CATEGORY : T_CATEGORY_CTG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_CATEGORY_CTG(
	CTG_ID   SERIAL NOT NULL ,
	CTG_NAME VARCHAR (50) NOT NULL ,
	CONSTRAINT PRK_T_CATEGORY_CTG PRIMARY KEY (CTG_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison CTG-CTG : T_L_CTG_CTG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_L_CTG_CTG(
	CTG_ID                INT  NOT NULL ,
	CTG_ID_T_CATEGORY_CTG INT  NOT NULL ,
	CONSTRAINT PRK_T_L_CTG_CTG PRIMARY KEY (CTG_ID,CTG_ID_T_CATEGORY_CTG)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_L_CTG_CTG
ADD CONSTRAINT FK_T_L_CTG_CTG_CTG_ID FOREIGN KEY (CTG_ID)
REFERENCES S_CATALOG.T_CATEGORY_CTG (CTG_ID);
 
ALTER TABLE S_CATALOG.T_L_CTG_CTG
ADD CONSTRAINT FK_T_L_CTG_CTG_CTG_ID_T_CATEGORY_CTG FOREIGN KEY (CTG_ID_T_CATEGORY_CTG)
REFERENCES S_CATALOG.T_CATEGORY_CTG (CTG_ID);
 
 
------------------------------------------------------------
-- Table de liaison OBJECT-CATEGORY : T_L_OBJ_CTG
------------------------------------------------------------
CREATE TABLE S_CATALOG.T_L_OBJ_CTG(
	CTG_ID INT  NOT NULL ,
	OBJ_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_L_OBJ_CTG PRIMARY KEY (OBJ_ID,CTG_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_CATALOG.T_L_OBJ_CTG
ADD CONSTRAINT FK_T_L_OBJ_CTG_CTG_ID FOREIGN KEY (CTG_ID)
REFERENCES S_CATALOG.T_CATEGORY_CTG (CTG_ID) ON DELETE CASCADE;
 
ALTER TABLE S_CATALOG.T_L_OBJ_CTG
ADD CONSTRAINT FK_T_L_OBJ_CTG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID) ON DELETE CASCADE;
 
 
 
 
------------------------------------------------------------
-- SCHEMA USER
------------------------------------------------------------
 
------------------------------------------------------------
-- Table USER : T_USER_USR
------------------------------------------------------------
CREATE TABLE S_USER.T_USER_USR(
	USR_ID        SERIAL NOT NULL ,
	USR_CODE      D_A_USERCODE NOT NULL UNIQUE,
	USR_LASTNAME  VARCHAR (100) NOT NULL ,
	USR_FIRSTNAME VARCHAR (100) NOT NULL ,
	ROL_ID        INT  NOT NULL ,
	CONSTRAINT PRK_T_USER_USR PRIMARY KEY (USR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_USER_USR
ADD CONSTRAINT FK_T_USER_USR_ROL_ID FOREIGN KEY (ROL_ID)
REFERENCES S_USER.T_ROLE_ROL (ROL_ID);
 
 
------------------------------------------------------------
-- Table USER ALIAS : T_USER_ALIAS_USA
------------------------------------------------------------
CREATE TABLE S_USER.T_USER_ALIAS_USA(
	USA_ID   SERIAL NOT NULL ,
	USA_NAME VARCHAR (255) NOT NULL,
	USR_ID   INT  NOT NULL ,
	CONSTRAINT PRK_T_USER_ALIAS_USA PRIMARY KEY (USA_ID,USR_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_USER_ALIAS_USA
ADD CONSTRAINT FK_T_USER_ALIAS_USA_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
 
------------------------------------------------------------
-- Table USER EMAIL : T_EMAIL_EML
------------------------------------------------------------
CREATE TABLE S_USER.T_EMAIL_EML(
	EML_ID    SERIAL NOT NULL ,
	EML_EMAIL D_A_EMAIL NOT NULL UNIQUE,
	EML_ISDISPLAYED BOOL NOT NULL DEFAULT FALSE,
	EML_ISVALIDATED BOOL NOT NULL DEFAULT FALSE,
	USR_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_EMAIL_EML PRIMARY KEY (EML_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_EMAIL_EML
ADD CONSTRAINT FK_T_EMAIL_EML_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
 
------------------------------------------------------------
-- Table USER WEBSITE : T_WEBSITE_WEB
------------------------------------------------------------
CREATE TABLE S_USER.T_WEBSITE_WEB(
	WEB_ID  SERIAL NOT NULL ,
	WEB_URL D_A_WEBSITE NOT NULL ,
	CONSTRAINT PRK_T_WEBSITE_WEB PRIMARY KEY (WEB_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison USER-WEBSITE : T_L_USR_WEB
------------------------------------------------------------
CREATE TABLE S_USER.T_L_USR_WEB(
	USR_ID INT  NOT NULL ,
	WEB_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_L_USR_WEB PRIMARY KEY (USR_ID,WEB_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_L_USR_WEB
ADD CONSTRAINT FK_T_L_USR_WEB_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_USER.T_L_USR_WEB
ADD CONSTRAINT FK_T_L_USR_WEB_WEB_ID FOREIGN KEY (WEB_ID)
REFERENCES S_USER.T_WEBSITE_WEB (WEB_ID);
 
 
------------------------------------------------------------
-- Table de liaison USER-COUNTRY : T_L_USR_CNY
------------------------------------------------------------
CREATE TABLE S_USER.T_L_USR_CNY(
	USR_ID INT  NOT NULL ,
	CNY_ID INT  NOT NULL ,
	CONSTRAINT PRK_T_L_USR_CNY PRIMARY KEY (USR_ID,CNY_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_L_USR_CNY
ADD CONSTRAINT FK_T_L_USR_CNY_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_USER.T_L_USR_CNY
ADD CONSTRAINT FK_T_L_USR_CNY_CNY_ID FOREIGN KEY (CNY_ID)
REFERENCES S_SITE.T_COUNTRY_CNY (CNY_ID);
 
 
------------------------------------------------------------
-- Table USER ROLE : T_ROLE_ROL
------------------------------------------------------------
CREATE TABLE S_USER.T_ROLE_ROL(
	ROL_ID   SERIAL NOT NULL ,
	ROL_NAME VARCHAR (100) NOT NULL UNIQUE,
	CONSTRAINT PRK_T_ROLE_ROL PRIMARY KEY (ROL_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison USER-TELESCOPE-SITE : T_L_USE
------------------------------------------------------------
CREATE TABLE S_USER.T_L_USE(
	T_L_USE_MAIN                BOOL  NOT NULL ,
	USR_ID                      INT  NOT NULL ,
	TLS_ID                      INT  NOT NULL ,
	SPM_EXPOSURE_START_DATETIME TIMESTAMP  NOT NULL ,
	CONSTRAINT PRK_T_L_USE PRIMARY KEY (USR_ID,TLS_ID,SPM_EXPOSURE_START_DATETIME)
) WITHOUT OIDS;
 
ALTER TABLE S_USER.T_L_USE
ADD CONSTRAINT FK_T_L_USE_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_USER.T_L_USE
ADD CONSTRAINT FK_T_L_USE_TLS_ID FOREIGN KEY (TLS_ID)
REFERENCES S_SITE.T_TELESCOPE_SITE_TLS (TLS_ID);
 
ALTER TABLE S_USER.T_L_USE
ADD CONSTRAINT FK_T_L_USE_SPM_EXPOSURE_START_DATETIME FOREIGN KEY (SPM_EXPOSURE_START_DATETIME)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (SPM_EXPOSURE_START_DATETIME);
 
 
 
 
------------------------------------------------------------
-- SCHEMA TELESCOPE
------------------------------------------------------------
 
------------------------------------------------------------
-- Table OPTICAL TUBE : T_OPTICAL_TUBE_OTA
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_OPTICAL_TUBE_OTA(
	OTA_ID   SERIAL NOT NULL ,
	OTA_NAME VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_OPTICAL_TUBE_OTA PRIMARY KEY (OTA_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table SPECTROGRAPH : T_SPECTROGRAPH_SPG
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_SPECTROGRAPH_SPG(
	SPG_ID   SERIAL NOT NULL ,
	SPG_NAME VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_SPECTROGRAPH_SPG PRIMARY KEY (SPG_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table SENSOR : T_SENSOR_SNR
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_SENSOR_SNR(
	SNR_ID   SERIAL NOT NULL ,
	SNR_NAME VARCHAR (100) NOT NULL ,
	CONSTRAINT PRK_T_SENSOR_SNR PRIMARY KEY (SNR_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table TELESCOPE : T_TELESCOPE_TLP
------------------------------------------------------------
CREATE TABLE S_TELESCOPE.T_TELESCOPE_TLP(
	TLP_ID   SERIAL NOT NULL ,
	TLP_CODE VARCHAR (25)  ,
	TLP_NAME VARCHAR (250) NOT NULL ,
	OTA_ID   INT  NOT NULL ,
	SPG_ID   INT  NOT NULL ,
	SNR_ID   INT  NOT NULL ,
	CONSTRAINT PRK_T_TELESCOPE_TLP PRIMARY KEY (TLP_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_TELESCOPE.T_TELESCOPE_TLP
ADD CONSTRAINT FK_T_TELESCOPE_TLP_OTA_ID FOREIGN KEY (OTA_ID)
REFERENCES S_TELESCOPE.T_OPTICAL_TUBE_OTA (OTA_ID);
 
ALTER TABLE S_TELESCOPE.T_TELESCOPE_TLP
ADD CONSTRAINT FK_T_TELESCOPE_TLP_SPG_ID FOREIGN KEY (SPG_ID)
REFERENCES S_TELESCOPE.T_SPECTROGRAPH_SPG (SPG_ID);
 
ALTER TABLE S_TELESCOPE.T_TELESCOPE_TLP
ADD CONSTRAINT FK_T_TELESCOPE_TLP_SNR_ID FOREIGN KEY (SNR_ID)
REFERENCES S_TELESCOPE.T_SENSOR_SNR (SNR_ID);
 
 
 
------------------------------------------------------------
-- SCHEMA SITE
------------------------------------------------------------
 
------------------------------------------------------------
-- Table COUNTRY : T_COUNTRY_CNY
------------------------------------------------------------
CREATE TABLE S_SITE.T_COUNTRY_CNY(
	CNY_ID      SERIAL NOT NULL,
	CNY_ISOALPHA2 CHAR (2) NOT NULL UNIQUE,
	CNY_ISOALPHA3 CHAR (3) NOT NULL UNIQUE,
	CNY_NAME    VARCHAR (255) NOT NULL ,
	CONSTRAINT PRK_T_COUNTRY_CNY PRIMARY KEY (CNY_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table SITE : T_SITE_SIT
------------------------------------------------------------
CREATE TABLE S_SITE.T_SITE_SIT(
	SIT_ID        SERIAL NOT NULL ,
	SIT_CODE      D_A_SITECODE NOT NULL UNIQUE,
	SIT_LATITUDE  D_N_LATITUDE NOT NULL ,
	SIT_LONGITUDE D_N_LONGITUDE NOT NULL ,
	SIT_ALTITUDE  D_N_ALTITUDE NOT NULL ,
	CNY_ID        INT  NOT NULL ,
	CONSTRAINT PRK_T_SITE_SIT PRIMARY KEY (SIT_ID)
) WITHOUT OIDS;
 
ALTER TABLES_SITE.T_SITE_SIT
ADD CONSTRAINT FK_T_SITE_SIT_CNY_ID FOREIGN KEY (CNY_ID)
REFERENCES S_SITE.T_COUNTRY_CNY (CNY_ID);
 
 
------------------------------------------------------------
-- Table SITE ALIAS : T_SITE_ALIAS_SIA
------------------------------------------------------------
CREATE TABLE S_SITE.T_SITE_ALIAS_SIA(
	SIA_ID   SERIAL NOT NULL ,
	SIA_NAME VARCHAR (100) NOT NULL ,
	SIT_ID   INT  NOT NULL ,
	CONSTRAINT PRK_T_SITE_ALIAS_SIA PRIMARY KEY (SIA_ID,SIT_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SITE.T_SITE_ALIAS_SIA
ADD CONSTRAINT FK_T_SITE_ALIAS_SIA_SIT_ID FOREIGN KEY (SIT_ID)
REFERENCES S_SITE.T_SITE_SIT (SIT_ID);
 
 
------------------------------------------------------------
-- Table TELESCOPE-SITE : T_TELESCOPE_SITE_TLS
------------------------------------------------------------
CREATE TABLE S_SITE.T_TELESCOPE_SITE_TLS(
	TLS_ID                      SERIAL NOT NULL ,
	SIT_ID                      INT  NOT NULL ,
	TLP_ID                      INT  NOT NULL ,
	CONSTRAINT PRK_T_TELESCOPE_SITE_TLS PRIMARY KEY (TLS_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SITE.T_TELESCOPE_SITE_TLS
ADD CONSTRAINT FK_T_TELESCOPE_SITE_TLS_SIT_ID FOREIGN KEY (SIT_ID)
REFERENCES S_SITE.T_SITE_SIT (SIT_ID);
 
ALTER TABLE S_SITE.T_TELESCOPE_SITE_TLS
ADD CONSTRAINT FK_T_TELESCOPE_SITE_TLS_TLP_ID FOREIGN KEY (TLP_ID)
REFERENCES S_TELESCOPE.T_TELESCOPE_TLP (TLP_ID);
 
 
 
 
------------------------------------------------------------
-- SCHEMA SPECTRUM
------------------------------------------------------------
 
------------------------------------------------------------
-- Table SPECTRUM : T_SPECTRUM_SPM
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_SPECTRUM_SPM(
	SPM_ID                      SERIAL NOT NULL ,
	SPM_FITS_FILE               VARCHAR (100) NOT NULL ,
	SPM_HELIO_CORRECTION        D_N_HELIOCORRECTION NOT NULL ,
	SPM_TELLURIC_CORRECTION     CHAR (50)  NOT NULL ,
	SPM_COSMIC_REMOVAL          CHAR (50)  NOT NULL ,
	SPM_NORMALISATION           CHAR (50)  NOT NULL ,
	SPM_DISPLAY                 BOOL  NOT NULL ,
	OBJ_ID                      INT  NOT NULL ,
	EXPOSURE_TIME               INT  NOT NULL ,
	TLS_ID                      INT  NOT NULL ,
	SPM_EXPOSURE_START_DATETIME TIMESTAMP  NOT NULL ,
	CONSTRAINT PRK_T_SPECTRUM_SPM PRIMARY KEY (OBJ_ID,SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_SPECTRUM_SPM
ADD CONSTRAINT FK_T_SPECTRUM_SPM_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID);
 
ALTER TABLE S_SPECTRUM.T_SPECTRUM_SPM
ADD CONSTRAINT FK_T_SPECTRUM_SPM_TLS_ID FOREIGN KEY (TLS_ID)
REFERENCES S_SITE.T_TELESCOPE_SITE_TLS (TLS_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM RESOLVING POWER : T_RESOLVING_POWER_RPO
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_RESOLVING_POWER_RPO(
	RPO_VALUE DECIMAL (6,2)  NOT NULL ,
	OBJ_ID 		INT  NOT NULL ,
	SPM_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_RESOLVING_POWER_RPO PRIMARY KEY (OBJ_ID,SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_RESOLVING_POWER_RPO
ADD CONSTRAINT FK_T_RESOLVING_POWER_RPO_SPM_ID FOREIGN KEY (OBJ_ID,SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (OBJ_ID,SPM_ID) ON DELETE CASCADE;
 
 
------------------------------------------------------------
-- Table SPECTRUM FLUX : T_FLUX_FLX
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_FLUX_FLX(
	FLX_VALUE D_A_FLXVALUE NOT NULL ,
	OBJ_ID  	INT  NOT NULL ,
	SPM_ID    INT  NOT NULL ,
	CONSTRAINT PRK_T_FLUX_FLX PRIMARY KEY (OBJ_ID,SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_FLUX_FLX
ADD CONSTRAINT FK_T_FLUX_FLX_SPM_ID FOREIGN KEY (OBJ_ID,SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (OBJ_ID,SPM_ID) ON DELETE CASCADE;
 
 
------------------------------------------------------------
-- Table héritée ECHELLE SPECTRUM : T_ECHELLE_SPECTRUM_ESP
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_ECHELLE_SPECTRUM_ESP(
	ESP_ECHELLE_ORD CHAR (100)  NOT NULL ,
	OBJ_ID 					INT  NOT NULL ,
	SPM_ID          INT  NOT NULL ,
	CONSTRAINT PRK_T_ECHELLE_SPECTRUM_ESP PRIMARY KEY (OBJ_ID,SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_ECHELLE_SPECTRUM_ESP
ADD CONSTRAINT FK_T_ECHELLE_SPECTRUM_ESP_SPM_ID FOREIGN KEY (OBJ_ID,SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (OBJ_ID,SPM_ID);
 
 
------------------------------------------------------------
-- Table SPECTRUM SERIES : T_SPM_SERIE_SPS
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_SPM_SERIE_SPS(
	SPS_ID       SERIAL NOT NULL ,
	SPS_ZIP_FILE VARCHAR (255) NOT NULL ,
	CONSTRAINT PRK_T_SPM_SERIE_SPS PRIMARY KEY (SPS_ID)
) WITHOUT OIDS;
 
 
------------------------------------------------------------
-- Table de liaison SPECTRUM-SERIES : T_L_SPS_SPM
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_L_SPS_SPM(
	SPS_ID 			INT NOT NULL,
	OBJ_ID 			INT NOT NULL,
	SPM_ID 			INT NOT NULL,
	CONSTRAINT PRK_T_L_SPS_SPM PRIMARY KEY (OBJ_ID,SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_L_SPS_SPM
ADD CONSTRAINT FK_T_L_SPS_SPM_SPS_ID FOREIGN KEY (SPS_ID)
REFERENCES S_SPECTRUM.T_SPM_SERIE_SPS (SPS_ID) ON DELETE CASCADE;
 
ALTER TABLE S_SPECTRUM.T_L_SPS_SPM
ADD CONSTRAINT FK_T_L_SPS_SPM_SPM_ID FOREIGN KEY (OBJ_ID,SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (OBJ_ID,SPM_ID) ON DELETE CASCADE;
 
 
------------------------------------------------------------
-- Table SPECTRUM SUBMISSION : T_L_SUBMIT_SPM
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_L_SUBMIT_SPM(
	SPM_SUBMISSION_DATE DATE  NOT NULL ,
	USR_ID              INT  NOT NULL ,
	OBJ_ID 							INT  NOT NULL ,
	SPM_ID              INT  NOT NULL ,
	CONSTRAINT PRK_T_L_SUBMIT_SPM PRIMARY KEY (USR_ID,SPM_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_L_SUBMIT_SPM
ADD CONSTRAINT FK_T_L_SUBMIT_SPM_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_SPECTRUM.T_L_SUBMIT_SPM
ADD CONSTRAINT FK_T_L_SUBMIT_SPM_SPM_ID FOREIGN KEY (OBJ_ID,SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (OBJ_ID,SPM_ID) ON DELETE CASCADE;
 
 
------------------------------------------------------------
-- Table SPECTRUM STATUS : T_STATUS_STA
------------------------------------------------------------
CREATE TABLE S_SPECTRUM.T_STATUS_STA(
	STA_VALUE             D_A_SPMSTATUS NOT NULL ,
	SPM_MODIF_STATUS_DATE DATE  NOT NULL ,
	USR_ID                INT  NOT NULL ,
	OBJ_ID								INT  NOT NULL ,
	SPM_ID                INT  NOT NULL ,
	CONSTRAINT PRK_T_STATUS_STA PRIMARY KEY (USR_ID,OBJ_ID,SPM_ID,SPM_MODIF_STATUS_DATE)
) WITHOUT OIDS;
 
ALTER TABLE S_SPECTRUM.T_STATUS_STA
ADD CONSTRAINT FK_T_STATUS_STA_USR_ID FOREIGN KEY (USR_ID)
REFERENCES S_USER.T_USER_USR (USR_ID);
 
ALTER TABLE S_SPECTRUM.T_STATUS_STA
ADD CONSTRAINT FK_T_STATUS_STA_SPM_ID FOREIGN KEY (OBJ_ID,SPM_ID)
REFERENCES S_SPECTRUM.T_SPECTRUM_SPM (OBJ_ID,SPM_ID) ON DELETE CASCADE;
 
 
 
 
------------------------------------------------------------
-- SCHEMA WEBSITE
------------------------------------------------------------
 
------------------------------------------------------------
-- Table WEBSITE PAGES : T_WEBPAGE_WPG
------------------------------------------------------------
CREATE TABLE S_WEBSITE.T_WEBPAGE_WPG(
	WPG_URL     VARCHAR (25) NOT NULL UNIQUE,
	WPG_COMMENT VARCHAR (2000)   ,
	WPG_UPDATE  TIMESTAMP  NOT NULL ,
	WPG_DISPLAY BOOL  NOT NULL ,
	OBJ_ID      INT  NOT NULL ,
	CONSTRAINT PRK_T_WEBPAGE_WPG PRIMARY KEY (OBJ_ID)
) WITHOUT OIDS;
 
ALTER TABLE S_WEBSITE.T_WEBPAGE_WPG
ADD CONSTRAINT FK_T_WEBPAGE_WPG_OBJ_ID FOREIGN KEY (OBJ_ID)
REFERENCES S_CATALOG.T_OBJECT_OBJ (OBJ_ID) ON DELETE CASCADE;

Vincent

[fsmrel]

Bonsoir Vincent,

Est-ce que je ne risque pas d'avoir de doublons de clé si je ne mets que USR_ID, OBJ_ID et SPM_ID dans la clé, notamment si je cherche à faire l'historique du statut d'un spectre ? Si le même utilisateur change le statut d'un spectre, je me retrouve avec la même clé. Dans ce cas, je peux peut-être rajouter SPM_MODIF_STATUT_DATE dans la clé pour la rendre unique ?

Dont acte. SPM_MODIF_STATUT_DATE peut intégrer la clé, mais vous pouvez aussi préférer y conserver STA_ID.

J'avoue ne pas trop bien comprendre le problème que cela engendrer...

C’est un problème qui se pose à chaque fois qu’une boucle est présente dans un MCD. C’est grâce à ça qu’on arrive à mélanger les factures des clients, leurs comptes bancaires, etc. Voyez par exemple la discussion « Gestion Facturation et devis ».

J'ai laissé l'action par défaut (NO ACTION) car je ne peux pas trancher sur les actions à réaliser en cas de suppression/modification d'enregistrement...

Certes. C’est comme le bon vin : utere sed non abutere, de la mesure en toute chose. J’ai voulu vous montrer ce qu’étaient les actions de compensation, mais le jeu peut être dangereux si l’on ne maîtrise pas totalement le sujet. Par exemple, si on codait CASCADE sur les tables contenant les données contentieuses des clients d’une entreprise, les clients seraient certes très heureux de ne plus rien risquer, mais ça pourrait ressembler à de l’inconscience, voire du sabotage de la part du concepteur de la base de données... Ne codez CASCADE que lorsque ça vous paraît évident et sans risque.

Une petite question à ce sujet ; est ce que l'action ON DELETE CASCADE doit être spécifiée dans le cas de tables héritées (T_STAR_STR ou T_VARIABLE_STAR_VST, dont la clé est héritée de T_OBJECT_OBJ) ?

Oui, puisque T_STAR_STR n’est jamais qu’une propriété externalisée de T_OBJECT_OBJ, et sans externalisation, on aurait eu un attribut ad-hoc (n’ayant donc pas son mot à dire en cas de DELETE) dans l’en-tête de T_OBJECT_OBJ, mais nullable...

Même observation pour T_VARIABLE_STAR_VST.

[fsmrel]

Bonsoir Vincent,

Il va aussi falloir que je me plonge dans le type RANGE pour en apprendre un peu plus sur son utilisation.
Vous utilisez TSRANGE mais, ne disposant que de la borne inférieure (de type TIMESTAMP) et du temps d'exposition, et non de la borne supérieure (de type TIMESTAMP), est-ce utilisable quand même ? Est-ce que l'opérateur OVERLAPS ne suffirait pas à vérifier que des périodes de pose avec le même instrument sur le même site ne se chevauchent pas ? Quelles sont les différences entre ces 2 façons de gérer les intervalles de temps ?
Je dois aussi faire avec les contraintes d'hébergement. Si certaines fonctionnalités ne sont pas accessibles (le module btree_gist, par exemple). Tout ceci n'est pas simple.

Effectivement, OVERLAPS suffit pour interdire la bilocation.

Prudemment, vous avez conservé {TLS_ID} comme clé primaire de la table T_TELESCOPE_SITE_TLS : pas de problème, d’autant plus que ça évite d’avoir recours à btree_gist.

Pour arriver à nos fins, partons de la structure que vous avez définie pour la table :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
CREATE TABLE S_SITE.T_TELESCOPE_SITE_TLS
(
           TLS_ID                 SERIAL     NOT NULL ,
           SIT_ID                 INT        NOT NULL ,
           TLP_ID                 INT        NOT NULL ,
         CONSTRAINT PRK_T_TELESCOPE_SITE_TLS PRIMARY KEY (TLS_ID)
) ;

Ajoutons les colonnes correspondant au timestamp de début, ainsi qu’à la durée (en secondes) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
CREATE TABLE S_SITE.T_TELESCOPE_SITE_TLS
(
           TLS_ID                 SERIAL       NOT NULL ,
           SIT_ID                 INT          NOT NULL ,
           TLP_ID                 INT          NOT NULL ,
           TLS_DEBUT              TIMESTAMP    NOT NULL,
           TLS_DUREE              INT          NOT NULL,
         CONSTRAINT PRK_T_TELESCOPE_SITE_TLS PRIMARY KEY (TLS_ID)
) ;

On crée la fonction qui va bien :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
DROP FUNCTION IF EXISTS TELESCOPE_SITE_OVERLAPS_FN CASCADE ;
 
CREATE FUNCTION TELESCOPE_SITE_OVERLAPS_FN(TelescopeId INT, Debut TIMESTAMP, Duree INT)
RETURNS BOOLEAN AS
$Aras$
    BEGIN
        RETURN (SELECT CASE 
                   WHEN (Debut, Debut + CAST(CAST(Duree AS VARCHAR) AS INTERVAL))  
                        OVERLAPS (TLS_DEBUT, TLS_DEBUT  + CAST(CAST(TLS_DUREE AS VARCHAR) AS INTERVAL)) 
                      THEN  
                          FALSE
                   ELSE  
                          TRUE
                   END
                FROM S_SITE.T_TELESCOPE_SITE_TLS WHERE TLP_ID = TelescopeId
               ) ; 
    END  
$Aras$
  LANGUAGE plpgsql ;

On crée la contrainte attachée à la table et faisant appel à la fonction :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
ALTER TABLE S_SITE.T_TELESCOPE_SITE_TLS
   ADD CONSTRAINT SITE_BILOCATION_CHK CHECK (TELESCOPE_SITE_OVERLAPS_FN(TLP_ID, TLS_DEBUT, TLS_DUREE)) ;

Deux ajouts de lignes (la durée est exprimée en secondes, en l’occurrence 82798 secondes) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
INSERT INTO S_SITE.T_TELESCOPE_SITE_TLS (TLP_ID, TLS_DEBUT, TLS_DUREE, SIT_ID)
    VALUES (314, '[2018-02-17 00:00:00]', 82798, 990) ;
 
INSERT INTO S_SITE.T_TELESCOPE_SITE_TLS (TLP_ID, TLS_DEBUT, TLS_DUREE, SIT_ID)
    VALUES (314, '[2018-02-17 22:59:57]', 82798, 991) ;

Et ça rouspète :

ERROR: ERREUR: la nouvelle ligne viole la contrainte de vérification «S_SITE.T_TELESCOPE_SITE_TLS » de la relation « site_bilocation_chk »
DETAIL: La ligne en échec contient (314, 2018-02-17 22:59:57, 82798, 991)

Le 2e INSERT n’est pas passé. A noter que PostgreSQL laisse filer les recouvrements d’une seconde, la requête suivante est acceptée :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
INSERT INTO S_SITE.T_TELESCOPE_SITE_TLS (TLP_ID, TLS_DEBUT, TLS_DUREE, SIT_ID)
    VALUES (314, '[2018-02-17 22:59:58]', 82798, 991) ;

Pour voir :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT TLP_ID, TLS_DEBUT, TLS_DEBUT + CAST(CAST(TLS_DUREE AS VARCHAR) AS INTERVAL)
FROM   S_SITE.T_TELESCOPE_SITE_TLS ;

Si ça vous convient, adaptez tout ça selon vos propres conventions.

J’ai pu laisser traîner des scories dans mes copier/coller de code SQL, vous me direz.

[fsmrel]

On peut améliorer la lecture et l’écriture du code précédent en simplifiant ainsi :

(1) Dans le script de création des tables, utiliser le type INTERVAL plutôt que le type INTEGER pour la durée :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE S_SITE.T_TELESCOPE_SITE_TLS
(
           TLS_ID                 SERIAL                   NOT NULL,
           SIT_ID                 INT                      NOT NULL,
           TLP_ID                 INT                      NOT NULL,
           TLS_DEBUT              TIMESTAMP                NOT NULL,
           TLS_DUREE              INTERVAL SECOND          NOT NULL,
         CONSTRAINT PRK_T_TELESCOPE_SITE_TLS PRIMARY KEY (TLS_ID)
) ;

Comme il faut appeler un chat un chat, j’ai écrit « INTERVAL SECOND », alors que j’aurais pu me contenter de « INTERVAL » : être précis dans le domaine temporel ne me paraît pas inutile... Incidemment, par référence à la norme SQL, l’unité pourrait être MINUTE, HOUR, DAY, MONTH, YEAR. On peut même être précis à la microseconde, mais concernant le temps de Planck, je pense que rien n’a été encore prévu.

(2) L’utilisation du type INTERVAL permet de simplifier le codage de la fonction de validation (la fonction CAST n’a plus lieu d’être) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE FUNCTION TELESCOPE_SITE_OVERLAPS_FN(TelescopeId INT, Debut TIMESTAMP, Duree INTERVAL SECOND)
RETURNS BOOLEAN AS
$Aras$
    BEGIN
        RETURN (SELECT CASE 
                   WHEN (Debut, Debut + Duree) 
                        OVERLAPS (TLS_DEBUT, TLS_DEBUT + TLS_DUREE) 
                      THEN  
                          FALSE
                   ELSE  
                          TRUE
                   END
                FROM S_SITE.T_TELESCOPE_SITE_TLS WHERE TLP_ID = TelescopeId               ) ; 
    END  
$Aras$
  LANGUAGE plpgsql ;

(3) Les deux ajouts de lignes peuvent devenir (la durée peut être exprimée en secondes, ou en une autre unité plus pratique) :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
INSERT INTO S_SITE.T_TELESCOPE_SITE_TLS (TLP_ID, TLS_DEBUT, TLS_DUREE, SIT_ID)
    VALUES (314, '[2018-02-17 00:00:00]', '82798 SECONDS', 990) ;

INSERT INTO S_SITE.T_TELESCOPE_SITE_TLS (TLP_ID, TLS_DEBUT, TLS_DUREE, SIT_ID)
    VALUES (314, '[2018-02-17 22:59:57]',  '48 HOURS', 991) ;

(4) Pour voir :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT TLP_ID, TLS_DEBUT, TLS_DEBUT + TLS_DUREE AS TLS_FIN 
FROM   S_SITE.T_TELESCOPE_SITE_TLS ;

(5) Et avec une vue :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
 
CREATE VIEW S_SITE.V_TELESCOPE_SITE_TLS (TLP_ID, TLS_DEBUT, TLS_FIN)
AS
    SELECT TLP_ID, TLS_DEBUT, TLS_DEBUT + TLS_DUREE AS TLS_FIN
    FROM   S_SITE.T_TELESCOPE_SITE_TLS ;

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
    SELECT TLP_ID, TLS_DEBUT, TLS_FIN
    FROM   S_SITE.V_TELESCOPE_SITE_TLS ;

Là encore, des tests plus poussés sont à réaliser (tout en vérifiant que mes copier/coller n’ont pas semé la zoubia...)

[aras-vbo]

Bonjour François,

Concernant la CASCADE sur les héritages, j'ai modifié le script en conséquence. J'ai adopté la cascade uniquement sur les spectres Echelle (T_ECHELLE_SPECTRUM_ESP) qui héritent des spectres (T_SPECTRUM_SPM). Par contre, je ne l'ai pas fait pour les objets, étoiles et étoiles variables.
La suppression/modification, si elle a lieu, portera surtout les spectres (et les spectres Echelle) et non pas sur les objets célestes ; il y a peu de chance qu'on supprime une étoile d'un catalogue, d'autant moins si des spectres y sont liés.

Votre solution sur la bilocation est exactement ce qu'il faut.

Toutefois, j'ai plusieurs questions "existentielles" sur la durée (EXPOSURE_TIME) et la date de début de pose (SPM_EXPOSURE_START_TIME), que vous renommez TLS_DUREE et TLS_DEBUT, respectivement :

1 - J'ai placé ces deux champs dans la table T_SPECTRUM_SPM car cela me paraissait logique ; le début de la pose et sa durée sont des propriétés du spectre. Votre solution impose t-elle que je les déplace et que je les mette dans T_TELESCOPE_SITE_TLS ou dois-je les dupliquer au travers des 2 nouveaux champs TLS_DUREE et TLS_DEBUT ? A moins que je puisse modifier la fonction TELESCOPE_SITE_OVERLAPS_FN de façon à faire appel aux 2 champs SPM_EXPOSURE_START_TIME et EXPOSURE_TIME de T_SPECTRUM_SPM ou que j'en fasse un déclencheur ?

2 - Certaines incohérences ne seraient sans doute pas détectées.
Par exemple, soit la séquence de spectres suivante, réalisée avec le même télescope :
J'enregistre un premier spectre SP1, le 8/05/2018 à 11h00 TU, sur une durée de 3600s. J'indique qu'il est réalisé sur le site S1.
J'enregistre un second spectre SP2, le 09/05/2018 à 0h02 TU, sur une durée de 3600s. Par erreur, j'indique qu'il est réalisé sur le site S2.
Enfin, j'enregistre un dernier spectre SP3, le 09/05/2018 à 02h00 TU, toujours sur une durée de 3600s. J'indique qu'il est réalisé sur le site S1.

J'ai potentiellement une bilocation mais la base va accepter les 3 spectres car il n'y a pas de superposition d'intervalles alors qu'il m'est impossible de passer du site S1 au site S2 en 2s...

Au fond, je me demande s'il est du ressort de la base ou de l'opérateur qui valide ou non un spectre de s'assurer qu'il n'y a pas de bilocation possible ou d'incohérences spatiales et temporelles. Ceci dit, je vais conserver cette fonction de vérification de bilocation ; deux précautions valent mieux qu'une.

Merci beaucoup pour le temps que vous consacrez à mes problématiques spectroscopiques.

Vincent

[fsmrel]

Avant d'examiner le reste...

J'ai placé ces deux champs dans la table T_SPECTRUM_SPM car cela me paraissait logique ; le début de la pose et sa durée sont des propriétés du spectre. Votre solution impose t-elle que je les déplace et que je les mette dans T_TELESCOPE_SITE_TLS ou dois-je les dupliquer au travers des 2 nouveaux champs TLS_DUREE et TLS_DEBUT ? A moins que je puisse modifier la fonction TELESCOPE_SITE_OVERLAPS_FN de façon à faire appel aux 2 champs SPM_EXPOSURE_START_TIME et EXPOSURE_TIME de T_SPECTRUM_SPM ou que j'en fasse un déclencheur ?

Avant de chercher quelque chose de mieux, pour le moment, je propose ceci :

Eliminer les données temporelles de la structure de la table TELESCOPE_SITE (votre table T_TELESCOPE_SITE_TLS), au bénéfice de celles qui sont présentes dans la structure de la table SPECTRE (votre table T_SPECTRUM_SPM), et ajouter une surclé {TLS_ID, TLP_ID} pour TELESCOPE_SITE, de manière à propager TLP_ID dans la structure de la table SPECTRE :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
CREATE TABLE TELESCOPE_SITE
(
        TLS_ID          INT                NOT NULL
      , TLP_ID          INT                NOT NULL
      , SIT_ID          INT                NOT NULL
    , CONSTRAINT TELESCOPE_SITE_PK PRIMARY KEY (TLS_ID)
    , CONSTRAINT TELESCOPE_SITE_SK UNIQUE (TLS_ID, TLP_ID)
) ;
------------------------------------------------------------
CREATE TABLE SPECTRE
(
        SPM_ID                      SERIAL            NOT NULL
      , TLS_ID                      INT               NOT NULL
      , TLP_ID                      INT               NOT NULL
      , EXPOSURE_START              TIMESTAMP         NOT NULL
      , EXPOSURE_TIME               INTERVAL SECOND   NOT NULL
    , CONSTRAINT SPECTRE_PK PRIMARY KEY (SPM_ID)
    , CONSTRAINT SPECTRE_TELESCOPE_SITE_FK1 FOREIGN KEY (TLS_ID)
          REFERENCES TELESCOPE_SITE (TLS_ID)
    , CONSTRAINT SPECTRE_TELESCOPE_SITE_FK2 FOREIGN KEY (TLS_ID, TLP_ID)
          REFERENCES TELESCOPE_SITE (TLS_ID, TLP_ID)
) ;

Dans ces conditions, le code de la fonction de contrôle devient :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE FUNCTION TELESCOPE_SITE_OVERLAPS_FN(TelescopeId INT, Debut TIMESTAMP, Duree INTERVAL SECOND)
    RETURNS BOOLEAN AS
$Aras$
    BEGIN
        RETURN (SELECT CASE 
                   WHEN (Debut, Debut + Duree) 
                        OVERLAPS (EXPOSURE_START, EXPOSURE_START + EXPOSURE_TIME) 
                      THEN  
                          FALSE
                   ELSE  
                          TRUE
                   END
              FROM SPECTRE WHERE TLP_ID = TelescopeId
             ) ; 
    END  
$Aras$
  LANGUAGE plpgsql ;

Prise en compte de la contrainte :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
ALTER TABLE SPECTRE
   ADD CONSTRAINT SITE_BILOCATION_CHK CHECK (TELESCOPE_SITE_OVERLAPS_FN(TLP_ID, EXPOSURE_START, EXPOSURE_TIME)
);

Des insertions :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
INSERT INTO TELESCOPE_SITE (TLS_ID, TLP_ID, SIT_ID)
    VALUES (1, 314, 990) ;
 
INSERT INTO TELESCOPE_SITE (TLS_ID, TLP_ID, SIT_ID)
    VALUES (2, 314, 991) ;
 
INSERT INTO SPECTRE (TLS_ID, TLP_ID, EXPOSURE_START, EXPOSURE_TIME)
    VALUES (1, 314, '[2018-02-17 00:00:00]', '82798 SECONDS') ;
 
INSERT INTO SPECTRE (TLS_ID, TLP_ID, EXPOSURE_START, EXPOSURE_TIME)
    VALUES (2, 314, '[2018-02-17 22:59:57]', '48 HOURS') ;

Pour voir :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
SELECT TLS_ID, TLP_ID, EXPOSURE_START, EXPOSURE_START + EXPOSURE_TIME AS EXPOSURE_END 
FROM   SPECTRE ;

[fsmrel]

J'ai potentiellement une bilocation mais la base va accepter les 3 spectres car il n'y a pas de superposition d'intervalles alors qu'il m'est impossible de passer du site S1 au site S2 en 2s...

Les 3 spectres seront effectivement acceptés (sans bilocation réelle), mais il est vrai que si le site S1 est près de Strasbourg et le site S2 du côté de Brest, l’utilisation du télescope ne pourra pas être instantanée (à moins d’en passer par l’intrication quantique , mais là il faut faire appel à Alain Aspect...) Maintenant, si l’opérateur affecte le télescope par erreur à S2, soit rien ne se passe, c’est une affectation pour rien, soit il se rend compte de son erreur et remplace la valeur S2 par la valeur S1 (à moins de se planter à nouveau et de l’affecter du côté de Perpignan...)

Cela dit, je ne vois pas ce qu’apporterait l’absence de contrôle par SQL, car l’erreur étant humaine, l’opérateur pourrait se tromper et laisser filer, là où la fonction de contrôle se serait révélée efficace et l’aurait conduit à remettre les choses en ordre. A mon sens, autant utiliser la fonction, et ne l’abandonner que si à l’usage elle s’avérait pénalisante.


Remarques :

La clé étrangère SPECTRE_TELESCOPE_SITE_FK1 peut disparaître, SPECTRE_TELESCOPE_SITE_FK2 remplit la fonction.

Pour la forme, et éviter les recouvrements d'une malheureuse seconde, dans la fonction de contrôle, j’ai remplacé

WHEN (Debut, Debut + Duree)

par

WHEN (Debut - INTERVAL '1 SECOND', Debut + Duree)

J’en ai profité pour injecter une clause DISTINCT (dans le SELECT CASE). La fonction devient :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
CREATE FUNCTION TELESCOPE_SITE_OVERLAPS_FN(TelescopeId INT, Debut TIMESTAMP, Duree INTERVAL SECOND)
    RETURNS BOOLEAN AS
$Aras$
    BEGIN
        RETURN (SELECT DISTINCT CASE 
                       WHEN (Debut - INTERVAL '1 SECOND', Debut + Duree)
                            OVERLAPS (EXPOSURE_START, EXPOSURE_START + EXPOSURE_TIME) 
                         THEN  
                            FALSE
                       ELSE  
                            TRUE
                       END
               FROM SPECTRE WHERE TLP_ID = TelescopeId
              ) ; 
    END  
$Aras$
  LANGUAGE plpgsql ;

J’aurais voulu nous dispenser de la fonction de contrôle (et de la surclé) en définissant pour la table SPECTRE une contrainte CHECK comportant un SELECT FROM TELESCOPE_SITE, mais contrairement à la norme SQL, PostgreSQL ne le permet pas (à ce jour...) :

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. (Voir ici.)

Quant à utiliser un trigger ça pourrait se faire mais en l’occurrence, évitons...

Merci beaucoup pour le temps que vous consacrez à mes problématiques spectroscopiques.

Je vous en prie, ça change des problèmes de mélange des factures et de la location de vidéos...
N’oubliez pas que pour les médailles en chocolat, si vous avez deux minutes, c’est ici.

[fsmrel]

Merci Vincent,

Où en êtes-vous de votre réflexion quant à l’intrication quantique et néanmoins télescopique ?

[aras-vbo]

Bonsoir François,

Désolé mais je n'ai pas trop eu le temps ce week-end de me pencher sur la question ; il faut aussi faire place à ses impératifs familiaux .
J'ai quand même eu le temps de vous attribuer quelques médailles en chocolat . Et commencé à lire votre exposé (somme toute assez épais) concernant la normalisation ; une inépuisable source d'informations et d'inspiration.
Je travaille aussi sur le formatage des données à insérer dans la base ainsi que sur les scripts qui permettront de le faire.
Je suis aussi en train de prospecter pour un hébergement ; visiblement, hors serveur virtuel privé ou serveur dédié, point de salut avec une base PostgreSQL comprenant des fonctions, des déclencheurs et autres fonctionnalités si vitales pour un SGBDR. La politique des hébergeurs à propos des bases de données ne cessera jamais de m'étonner ; c'est le parent pauvre. A croire que la BDD est, à leur yeux, un simple système de gestion de fichier plats dans le lequel l'utilisateur entasse ses données n'importe comment (ce qui est souvent malheureusement la cas pour les développeurs).

Incidemment, j'ai aussi remarqué que vous n'étiez pas fan de la convention d'écriture proposée par SQLPro et que j'utilise dans mon script.
Ce faisant, comme vous n'utilisez pas le nom des tables et des champs de mon script, j'ai parfois quelques difficultés à modifier mon script en fonction de vos remarques... Cette convention ne me satisfait pas pleinement mais elle a le mérite d'exister et de tenter d'uniformiser et universaliser les scripts

Cela dit, J'ai effectué les changements que vous suggérez :
- passage du champ Temps d'Exposition de INT à INTERVAL SECOND
- ajout de la surclé {TLS_ID, TLP_ID} et propagation dans la table des spectres
- suppression de la clé étrangère {TLS_ID} dans la table Spectre, suite à la création de la clé étrangère {TLS_ID, TLP_ID}

J'ai aussi créer un script supplémentaire qui regroupera les fonctions, etc. J'y ai mis votre fonction de test de non-bilocation.

Petites questions concernant les index CLUSTER. Il n'y a aucune déclaration spécifique à faire ? Si je comprends bien, il suffit de lancer une commande CLUSTER nom_table ON nom_index et cela suffit, c'est bien cela ? Dans une fonction ? Dans une tâche cron ? La documentation PostgreSQL suggère d'autres opérations post-clusterisation, telle que ANALYSE
Cette opération n'étant pas anodine en terme de temps et de verrouillage de table, quand doit-elle être faite et selon quelle fréquence ? Est-ce qu'un test de performance avec EXPLAIN se serait pas nécessaire pour vérifier l'intérêt de la clusterisation, comme vous le suggériez plus haut ?

Vincent

[fsmrel]

Bonsoir Vincent,


Merci à nouveau pour les étoiles (terme en fait plus pertinent que celui de médailles dans notre contexte ).
Mon exposé sur la normalisation est effectivement plutôt épais, mais il pourrait être complété, par exemple je n’y parle pas de la SKNF (Superkey Normal Form) et de la RFNF (Redundancy Free Normal Form), dont l’importance est très loin de celle de la BCNF ou encore de la 5NF. A noter que DVP a fait évoluer ses outils de mise au gabarit, avec en conséquence un CASCADE sur mon article : sauts de paragraphe supprimés, code SQL aligné au centre, devenant quasi illisible : je suis en train d’essayer de remettre tout cela d’équerre, mais y a de quoi faire...)

Je comprends que vous vous fassiez du souci quant à l’hébergement des bases de données chez les hébergeurs, mais je ne connais malheureusement rien au sujet.

Concernant la convention d'écriture proposée par SQLpro, je dirais que pendant des décennies j’ai eu à utiliser toutes sortes de conventions chez mes clients : ce sont des normes locales, une de plus, pourquoi pas, du moment que ces conventions ne sont pas dangereuses. Je fais simplement observer que tous les noms de table commencent par la lettre « T » (par exemple T_SPECTRUM_SPM), et que le jour où cet objet deviendra une vue, cette lettre ne pourra pas être remplacée, bien que devenue trompeuse sur la nature de l’objet. En plus, si le DBA ou l’optimiseur du SGBD s’intéressent aux objets commençant par « SPECTRUM », ils devront coder « LIKE ''%SPECTRUM%' » infiniment moins performant que « LIKE 'SPECTRUM%' » (faire passer cette lettre ailleurs qu’au tout début du nom me paraît souhaitable). Depuis des années, telle base de données comporte environ 1500 tables, chahutée par 150 développeurs, avec comme convention de nommage (pour reprendre l’exemple des spectres) : « SPM » comme nom de schéma, puis « SPMnnnn » (nnnn = numérotation) et tout le monde s’y retrouve, y-compris, last but not least, la production informatique sur les épaules de laquelle tout repose désormais, et suffixe le nom des objets selon ses propres conventions.

A propos des index CLUSTER : autant je connaissais bien le sujet du temps où j’utilisais DB2, autant je n’en ai qu’une idée quant à PostgreSQL. Déjà, le mot CLUSTER n’a pas la même signification pour tout le monde. Ainsi, je ne sais pas où en est ORACLE aujourd’hui, mais en 1986, pour lui, clusteriser (pardon pour le barbarisme) c’était par exemple stocker la jointure de T_SPECTRUM_SPM et T_RESOLVING_POWER_RPO pour diminuer (ce qui reste à démontrer) le nombre des entrées/sorties sur le disque. Je connaissais très bien la structure des index DB2, et été amené à examiner ceux de SQL Server pour me rendre compte que l’éditeur du SGBD s’était contenté de reprendre une vieille technologie d’IBM (des années soixante-dix), celle des fichiers VSAM, jugés beaucoup trop frustes pour DB2.

Vous pouvez vous reporter au billet qui compare les index de DB2 et ceux de SQL Server. Si la documentation existe, je regarderai comment sont ficelés ceux de PostgreSQL.

En tout cas, l’objectif reste bien le regroupement dans la même page (enregistrement physique) des données d’une table « fille » pour une table « mère » donnée. Par exemple, si les requêtes les plus fréquentes, les plus sensibles portent sur les spectres d’une étoile donnée, alors l’index cluster de T_SPECTRUM_SPM aura pour clé {OBJ_ID}.

Dans l’optique de l’administration de la base de données et de sa performance, quelques mots clés outre « CLUSTER » :

FILLFACTOR (FREESPACE et FREEPAGE avec DB2) : cette option permet de geler de la place dans les pages des tables et des index. Concernant les tables, avec DB2 cela permet de geler de la place, mais seulement quand on se sert les utilitaires DB2 de chargement ou de réorganisation de sorte que, par la suite, les lignes créées par INSERT puissent loger dans la même page que leurs petites sœurs (par exemple un nouveau spectre pour une étoile donnée). A la lecture de la doc PostgreSQL, j’ai peur que ces lignes se retrouvent dans une autre page (test à faire...) De toute façon, PostgreSQL me paraît a priori très léger par comparaison. Qui plus est, les aspects physiques ne devraient pas être mentionnés dans l’instruction CREATE TABLE, mais dans un objet voué à héberger la table (le TABLESPACE de DB2). J’entends bien que PostgreSQL propose une instruction CREATE TABLESPACE, mais au paramétrage indigent puisque l’essentiel est dégagé dans celui de CREATE TABLE : à mon sens il y a une erreur de casting de la part des concepteurs du SGBD, l’indépendance des concepts n’est pas respectée.

PARTITION BY : ce paramètre permet de saucissonner physiquement une table en fonction de fourchettes de clés. A creuser dans le cas de PostgreSQL.

J’aimerais bien creuser ces aspects physiques, prototyper, mais il faut du temps...

Concernant EXPLAIN, une parfaite connaissance de cette instruction est indispensable, même si à elle seule ne suffit pas pour déclencher des opérations importantes telles que la réorganisation d’une table. Elle permet au moins de savoir quelle stratégie a retenu l’optimiseur pour l’accès aux données. Ainsi, en 1985 j’ai refusé d’utiliser DB2 (V1.R1) en production du fait qu’EXPLAIN n’était pas fourni, j’ai attendu la V1.R2.

Les RRR

Par RRR j’entends REORG (réorganisation), RUNSTATS (collecte des statistiques), REBIND (recalcul de la stratégie d’accès aux données pour chaque programme hébergeant des requêtes statiques SQL). C’est le triplet qui me permettait par exemple d’éviter à mon entreprise (SSII) de prendre des pénalités de la part de nos clients. Pas Anodin ! Exemple : « Allo ! François ? On est dans une m... noire, on va se prendre des pénalités ! Les perfs ne sont pas celles sur lesquelles on s’était engagés ! ». Mais dans cet exemple, j’avais prototypé un an auparavant, avant que ne commencent les développements, et un coup de RRR ne pouvait que remettre les choses en ordre.


A un moment donné, les mises à jour font que les données des tables sont ventilées un peu partout dans l’espace, d’où performances se dégradant. Une réorganisation s’impose. Je ne sais pas ce que PostgreSQL propose à cet effet : j’ai vu qu’il y avait des outils comme pg_repack et pg_reorg : à creuser. La fréquence des réorganisations est à déterminer au doigt mouillé, table par table (voire par partition si les outils condescendent à descendre à ce niveau), avec de préférence (quoi qu’en disent les éditeurs) verrouillage exclusif de l’objet en cours de réorganisation (ceinture, bretelles et épingle à nourrice). Si une table contient 100000 lignes et qu’une partition n’en contient que 5000 ou 10000, la durée de blocage de l’objet est réduit d’autant.

La collecte des statistiques est évidemment importante elle aussi, ne serait-ce que pour avoir une connaissance objective de la situation (avant ou après réorganisation). ANALYSE permettrait manifestement cette collecte.

Il y a du pain sur la planche, mais s’assurer de la performance ne peut pas être traité avec légèreté. Je repense à une certaine application pour laquelle le traitement batch quotidien devait être de 8 heures, et qui a pris 240 heures lors de sa mise en production : clairement, aucun prototypage n’avait été réalisé.

J’ai pas mal de fers au feu, donc pour le moment je ne mets pas en route de prototypage des « perfs » avec PostgreSQL, c’est quand même du lourd, mais de votre côté exercez-vous...

[CinePhil]

Je suis aussi en train de prospecter pour un hébergement ; visiblement, hors serveur virtuel privé ou serveur dédié, point de salut avec une base PostgreSQL comprenant des fonctions, des déclencheurs et autres fonctionnalités si vitales pour un SGBDR.

J'ai un serveur dédié chez Kimsufi pour 10 euros par mois. Il y a même moins cher. Ce n'est pas cher et je suis totalement libre de faire ce que je veux avec. J'y ai installé Postgresql et MariaDB pour deux besoins différents.
Il faut évidemment un peu de compétences en administration système mais, si je me souviens bien, votre projet est pour une association d'astronomie, non ? 10 euros par mois peut probablement rentrer dans le budget.

Si vous n'y arrivez pas avec cette solution, je pourrais même héberger votre BDD et/ou votre site pour un prix moins cher à discuter.