-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathmv_mysql.sql
2555 lines (2324 loc) · 80.6 KB
/
mv_mysql.sql
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
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
-- data_file_type_lu is a look-up table containing information about the different types
-- of MET output data files. Each data file that is loaded into the DATABASE is
-- represented by a record in the data_file table, which points at one of the data file
-- types. The file type indicates which DATABASE tables store the data in the file.
DROP TABLE IF EXISTS data_file_lu;
CREATE TABLE data_file_lu
(
data_file_lu_id INT UNSIGNED NOT NULL,
type_name VARCHAR(32),
type_desc VARCHAR(128),
PRIMARY KEY (data_file_lu_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- data_file_id stores information about files that have been parsed and loaded into the
-- DATABASE. Each record represents a single file of a particular MET output data file
-- type (point_stat, mode, etc.). Each data_file record points at its file type in the
-- data_file_type_lu table via the data_file_type_lu_id field.
DROP TABLE IF EXISTS data_file;
CREATE TABLE data_file
(
data_file_id INT UNSIGNED NOT NULL,
data_file_lu_id INT UNSIGNED NOT NULL,
filename VARCHAR(110),
path VARCHAR(120),
load_date DATETIME,
mod_date DATETIME,
PRIMARY KEY (data_file_id),
CONSTRAINT data_file_unique_pk
UNIQUE INDEX (filename, path),
CONSTRAINT stat_header_data_file_lu_id_pk
FOREIGN KEY (data_file_lu_id)
REFERENCES data_file_lu (data_file_lu_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- stat_header contains the forecast and observation bookkeeping information, except for
-- the valid and init times, for a verification case. Statistics tables point at a
-- single stat_header record, which indicate the circumstances under which they were
-- calculated.
DROP TABLE IF EXISTS stat_header;
CREATE TABLE stat_header
(
stat_header_id INT UNSIGNED NOT NULL,
version VARCHAR(8),
model VARCHAR(80),
descr VARCHAR(40) DEFAULT 'NA',
fcst_var VARCHAR(50),
fcst_units VARCHAR(100) DEFAULT 'NA',
fcst_lev VARCHAR(100),
obs_var VARCHAR(50),
obs_units VARCHAR(100) DEFAULT 'NA',
obs_lev VARCHAR(100),
obtype VARCHAR(20),
vx_mask VARCHAR(100),
interp_mthd VARCHAR(20),
interp_pnts INT UNSIGNED,
fcst_thresh VARCHAR(100),
obs_thresh VARCHAR(100),
PRIMARY KEY (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
CREATE INDEX stat_header_unique_pk ON stat_header (
model,
fcst_var(20),
fcst_lev (10),
obs_var(20),
obs_lev(10),
obtype(10),
vx_mask(20),
interp_mthd,
interp_pnts,
fcst_thresh(20),
obs_thresh(20)
);
-- line_data_fho contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_fho;
CREATE TABLE line_data_fho
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
f_rate DOUBLE,
h_rate DOUBLE,
o_rate DOUBLE,
CONSTRAINT line_data_fho_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_fho_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_ctc contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_ctc;
CREATE TABLE line_data_ctc
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
fy_oy INT UNSIGNED,
fy_on INT UNSIGNED,
fn_oy INT UNSIGNED,
fn_on INT UNSIGNED,
ec_value DOUBLE DEFAULT 0.5,
CONSTRAINT line_data_ctc_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_ctc_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_cts contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_cts;
CREATE TABLE line_data_cts
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
alpha DOUBLE,
total INT UNSIGNED,
baser DOUBLE,
baser_ncl DOUBLE,
baser_ncu DOUBLE,
baser_bcl DOUBLE,
baser_bcu DOUBLE,
fmean DOUBLE,
fmean_ncl DOUBLE,
fmean_ncu DOUBLE,
fmean_bcl DOUBLE,
fmean_bcu DOUBLE,
acc DOUBLE,
acc_ncl DOUBLE,
acc_ncu DOUBLE,
acc_bcl DOUBLE,
acc_bcu DOUBLE,
fbias DOUBLE,
fbias_bcl DOUBLE,
fbias_bcu DOUBLE,
pody DOUBLE,
pody_ncl DOUBLE,
pody_ncu DOUBLE,
pody_bcl DOUBLE,
pody_bcu DOUBLE,
podn DOUBLE,
podn_ncl DOUBLE,
podn_ncu DOUBLE,
podn_bcl DOUBLE,
podn_bcu DOUBLE,
pofd DOUBLE,
pofd_ncl DOUBLE,
pofd_ncu DOUBLE,
pofd_bcl DOUBLE,
pofd_bcu DOUBLE,
far DOUBLE,
far_ncl DOUBLE,
far_ncu DOUBLE,
far_bcl DOUBLE,
far_bcu DOUBLE,
csi DOUBLE,
csi_ncl DOUBLE,
csi_ncu DOUBLE,
csi_bcl DOUBLE,
csi_bcu DOUBLE,
gss DOUBLE,
gss_bcl DOUBLE,
gss_bcu DOUBLE,
hk DOUBLE,
hk_ncl DOUBLE,
hk_ncu DOUBLE,
hk_bcl DOUBLE,
hk_bcu DOUBLE,
hss DOUBLE,
hss_bcl DOUBLE,
hss_bcu DOUBLE,
odds DOUBLE,
odds_ncl DOUBLE,
odds_ncu DOUBLE,
odds_bcl DOUBLE,
odds_bcu DOUBLE,
lodds DOUBLE DEFAULT -9999,
lodds_ncl DOUBLE DEFAULT -9999,
lodds_ncu DOUBLE DEFAULT -9999,
lodds_bcl DOUBLE DEFAULT -9999,
lodds_bcu DOUBLE DEFAULT -9999,
orss DOUBLE DEFAULT -9999,
orss_ncl DOUBLE DEFAULT -9999,
orss_ncu DOUBLE DEFAULT -9999,
orss_bcl DOUBLE DEFAULT -9999,
orss_bcu DOUBLE DEFAULT -9999,
eds DOUBLE DEFAULT -9999,
eds_ncl DOUBLE DEFAULT -9999,
eds_ncu DOUBLE DEFAULT -9999,
eds_bcl DOUBLE DEFAULT -9999,
eds_bcu DOUBLE DEFAULT -9999,
seds DOUBLE DEFAULT -9999,
seds_ncl DOUBLE DEFAULT -9999,
seds_ncu DOUBLE DEFAULT -9999,
seds_bcl DOUBLE DEFAULT -9999,
seds_bcu DOUBLE DEFAULT -9999,
edi DOUBLE DEFAULT -9999,
edi_ncl DOUBLE DEFAULT -9999,
edi_ncu DOUBLE DEFAULT -9999,
edi_bcl DOUBLE DEFAULT -9999,
edi_bcu DOUBLE DEFAULT -9999,
sedi DOUBLE DEFAULT -9999,
sedi_ncl DOUBLE DEFAULT -9999,
sedi_ncu DOUBLE DEFAULT -9999,
sedi_bcl DOUBLE DEFAULT -9999,
sedi_bcu DOUBLE DEFAULT -9999,
bagss DOUBLE DEFAULT -9999,
bagss_bcl DOUBLE DEFAULT -9999,
bagss_bcu DOUBLE DEFAULT -9999,
hss_ec DOUBLE DEFAULT -9999,
hss_ec_bcl DOUBLE DEFAULT -9999,
hss_ec_bcu DOUBLE DEFAULT -9999,
ec_value DOUBLE DEFAULT 0.5,
CONSTRAINT line_data_cts_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_cts_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- CREATE INDEX line_data_cts_fcst_lead_pk ON line_data_cts (fcst_lead);
-- CREATE INDEX line_data_cts_fcst_valid_beg_pk ON line_data_cts (fcst_valid_beg);
-- CREATE INDEX line_data_cts_fcst_init_beg_pk ON line_data_cts (fcst_init_beg);
-- line_data_cnt contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_cnt;
CREATE TABLE line_data_cnt
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
alpha DOUBLE,
total INT UNSIGNED,
fbar DOUBLE,
fbar_ncl DOUBLE,
fbar_ncu DOUBLE,
fbar_bcl DOUBLE,
fbar_bcu DOUBLE,
fstdev DOUBLE,
fstdev_ncl DOUBLE,
fstdev_ncu DOUBLE,
fstdev_bcl DOUBLE,
fstdev_bcu DOUBLE,
obar DOUBLE,
obar_ncl DOUBLE,
obar_ncu DOUBLE,
obar_bcl DOUBLE,
obar_bcu DOUBLE,
ostdev DOUBLE,
ostdev_ncl DOUBLE,
ostdev_ncu DOUBLE,
ostdev_bcl DOUBLE,
ostdev_bcu DOUBLE,
pr_corr DOUBLE,
pr_corr_ncl DOUBLE,
pr_corr_ncu DOUBLE,
pr_corr_bcl DOUBLE,
pr_corr_bcu DOUBLE,
sp_corr DOUBLE,
dt_corr DOUBLE,
ranks INT UNSIGNED,
frank_ties INT,
orank_ties INT,
me DOUBLE,
me_ncl DOUBLE,
me_ncu DOUBLE,
me_bcl DOUBLE,
me_bcu DOUBLE,
estdev DOUBLE,
estdev_ncl DOUBLE,
estdev_ncu DOUBLE,
estdev_bcl DOUBLE,
estdev_bcu DOUBLE,
mbias DOUBLE,
mbias_bcl DOUBLE,
mbias_bcu DOUBLE,
mae DOUBLE,
mae_bcl DOUBLE,
mae_bcu DOUBLE,
mse DOUBLE,
mse_bcl DOUBLE,
mse_bcu DOUBLE,
bcmse DOUBLE,
bcmse_bcl DOUBLE,
bcmse_bcu DOUBLE,
rmse DOUBLE,
rmse_bcl DOUBLE,
rmse_bcu DOUBLE,
e10 DOUBLE,
e10_bcl DOUBLE,
e10_bcu DOUBLE,
e25 DOUBLE,
e25_bcl DOUBLE,
e25_bcu DOUBLE,
e50 DOUBLE,
e50_bcl DOUBLE,
e50_bcu DOUBLE,
e75 DOUBLE,
e75_bcl DOUBLE,
e75_bcu DOUBLE,
e90 DOUBLE,
e90_bcl DOUBLE,
e90_bcu DOUBLE,
iqr DOUBLE DEFAULT -9999,
iqr_bcl DOUBLE DEFAULT -9999,
iqr_bcu DOUBLE DEFAULT -9999,
mad DOUBLE DEFAULT -9999,
mad_bcl DOUBLE DEFAULT -9999,
mad_bcu DOUBLE DEFAULT -9999,
anom_corr DOUBLE DEFAULT -9999,
anom_corr_ncl DOUBLE DEFAULT -9999,
anom_corr_ncu DOUBLE DEFAULT -9999,
anom_corr_bcl DOUBLE DEFAULT -9999,
anom_corr_bcu DOUBLE DEFAULT -9999,
me2 DOUBLE DEFAULT -9999,
me2_bcl DOUBLE DEFAULT -9999,
me2_bcu DOUBLE DEFAULT -9999,
msess DOUBLE DEFAULT -9999,
msess_bcl DOUBLE DEFAULT -9999,
msess_bcu DOUBLE DEFAULT -9999,
rmsfa DOUBLE DEFAULT -9999,
rmsfa_bcl DOUBLE DEFAULT -9999,
rmsfa_bcu DOUBLE DEFAULT -9999,
rmsoa DOUBLE DEFAULT -9999,
rmsoa_bcl DOUBLE DEFAULT -9999,
rmsoa_bcu DOUBLE DEFAULT -9999,
anom_corr_uncntr DOUBLE DEFAULT -9999,
anom_corr_uncntr_bcl DOUBLE DEFAULT -9999,
anom_corr_uncntr_bcu DOUBLE DEFAULT -9999,
si DOUBLE DEFAULT -9999,
si_bcl DOUBLE DEFAULT -9999,
si_bcu DOUBLE DEFAULT -9999,
CONSTRAINT line_data_cnt_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_cnt_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_ecnt contains stat data for a Continuous Ensemble Statistics.
DROP TABLE IF EXISTS line_data_ecnt;
CREATE TABLE line_data_ecnt
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
n_ens INT,
crps DOUBLE,
crpss DOUBLE,
ign DOUBLE,
me DOUBLE,
rmse DOUBLE,
spread DOUBLE,
me_oerr DOUBLE,
rmse_oerr DOUBLE,
spread_oerr DOUBLE,
spread_plus_oerr DOUBLE,
crpscl DOUBLE,
crps_emp DOUBLE,
crpscl_emp DOUBLE,
crpss_emp DOUBLE,
crps_emp_fair DOUBLE DEFAULT -9999,
spread_md DOUBLE,
mae DOUBLE DEFAULT -9999,
mae_oerr DOUBLE,
bias_ratio DOUBLE,
n_ge_obs INT,
me_ge_obs DOUBLE,
n_lt_obs INT,
me_lt_obs DOUBLE,
CONSTRAINT line_data_ecnt_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_ecnt_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_mctc contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_mctc;
CREATE TABLE line_data_mctc
(
line_data_id INT UNSIGNED NOT NULL,
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
n_cat INT UNSIGNED,
ec_value DOUBLE DEFAULT -9999,
PRIMARY KEY (line_data_id),
CONSTRAINT line_data_mctc_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_mctc_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_mctc_cnt contains count data for a particular line_data_mctc record. The
-- number of counts is determined by assuming a square contingency table and stored in
-- the line_data_mctc field n_cat.
DROP TABLE IF EXISTS line_data_mctc_cnt;
CREATE TABLE line_data_mctc_cnt
(
line_data_id INT UNSIGNED NOT NULL,
i_value INT UNSIGNED NOT NULL,
j_value INT UNSIGNED NOT NULL,
fi_oj INT UNSIGNED NOT NULL,
PRIMARY KEY (line_data_id, i_value, j_value)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_mcts contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_mcts;
CREATE TABLE line_data_mcts
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
alpha DOUBLE,
total INT UNSIGNED,
n_cat INT UNSIGNED,
acc DOUBLE,
acc_ncl DOUBLE,
acc_ncu DOUBLE,
acc_bcl DOUBLE,
acc_bcu DOUBLE,
hk DOUBLE,
hk_bcl DOUBLE,
hk_bcu DOUBLE,
hss DOUBLE,
hss_bcl DOUBLE,
hss_bcu DOUBLE,
ger DOUBLE,
ger_bcl DOUBLE,
ger_bcu DOUBLE,
hss_ec DOUBLE DEFAULT -9999,
hss_ec_bcl DOUBLE DEFAULT -9999,
hss_ec_bcu DOUBLE DEFAULT -9999,
ec_value DOUBLE DEFAULT -9999,
CONSTRAINT line_data_mcts_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_mcts_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_pct contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_pct;
CREATE TABLE line_data_pct
(
line_data_id INT UNSIGNED NOT NULL,
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
cov_thresh VARCHAR(32),
total INT UNSIGNED,
n_thresh INT UNSIGNED,
PRIMARY KEY (line_data_id),
CONSTRAINT line_data_pct_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_pct_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_pct_thresh contains threshold data for a particular line_data_pct record and
-- threshold. The number of thresholds stored is given by the line_data_pct field n_thresh.
DROP TABLE IF EXISTS line_data_pct_thresh;
CREATE TABLE line_data_pct_thresh
(
line_data_id INT UNSIGNED NOT NULL,
i_value INT UNSIGNED NOT NULL,
thresh_i DOUBLE,
oy_i INT UNSIGNED,
on_i INT UNSIGNED,
PRIMARY KEY (line_data_id, i_value)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_pstd contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_pstd;
CREATE TABLE line_data_pstd
(
line_data_id INT UNSIGNED NOT NULL,
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
cov_thresh VARCHAR(32),
alpha DOUBLE,
total INT UNSIGNED,
n_thresh INT UNSIGNED,
baser DOUBLE,
baser_ncl DOUBLE,
baser_ncu DOUBLE,
reliability DOUBLE,
resolution DOUBLE,
uncertainty DOUBLE,
roc_auc DOUBLE,
brier DOUBLE,
brier_ncl DOUBLE,
brier_ncu DOUBLE,
briercl DOUBLE DEFAULT -9999,
briercl_ncl DOUBLE DEFAULT -9999,
briercl_ncu DOUBLE DEFAULT -9999,
bss DOUBLE DEFAULT -9999,
bss_smpl DOUBLE DEFAULT -9999,
PRIMARY KEY (line_data_id),
CONSTRAINT line_data_pstd_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_pstd_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_pstd_thresh contains threshold data for a particular line_data_pstd record and
-- threshold. The number of thresholds stored is given by the line_data_pstd field n_thresh.
DROP TABLE IF EXISTS line_data_pstd_thresh;
CREATE TABLE line_data_pstd_thresh
(
line_data_id INT UNSIGNED NOT NULL,
i_value INT UNSIGNED NOT NULL,
thresh_i DOUBLE,
PRIMARY KEY (line_data_id, i_value)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_pjc contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_pjc;
CREATE TABLE line_data_pjc
(
line_data_id INT UNSIGNED NOT NULL,
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
cov_thresh VARCHAR(32),
total INT UNSIGNED,
n_thresh INT UNSIGNED,
PRIMARY KEY (line_data_id),
CONSTRAINT line_data_pjc_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_pjc_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_pjc_thresh contains threshold data for a particular line_data_pjc record and
-- threshold. The number of thresholds stored is given by the line_data_pjc field n_thresh.
DROP TABLE IF EXISTS line_data_pjc_thresh;
CREATE TABLE line_data_pjc_thresh
(
line_data_id INT UNSIGNED NOT NULL,
i_value INT UNSIGNED NOT NULL,
thresh_i DOUBLE,
oy_tp_i DOUBLE,
on_tp_i DOUBLE,
calibration_i DOUBLE,
refinement_i DOUBLE,
likelihood_i DOUBLE,
baser_i DOUBLE,
PRIMARY KEY (line_data_id, i_value)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_prc contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_prc;
CREATE TABLE line_data_prc
(
line_data_id INT UNSIGNED NOT NULL,
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
cov_thresh VARCHAR(32),
total INT UNSIGNED,
n_thresh INT UNSIGNED,
PRIMARY KEY (line_data_id),
CONSTRAINT line_data_prc_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_prc_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_prc_thresh contains threshold data for a particular line_data_prc record and
-- threshold. The number of thresholds stored is given by the line_data_prc field n_thresh.
DROP TABLE IF EXISTS line_data_prc_thresh;
CREATE TABLE line_data_prc_thresh
(
line_data_id INT UNSIGNED NOT NULL,
i_value INT UNSIGNED NOT NULL,
thresh_i DOUBLE,
pody_i DOUBLE,
pofd_i DOUBLE,
PRIMARY KEY (line_data_id, i_value)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_sl1l2 contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_sl1l2;
CREATE TABLE line_data_sl1l2
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
fbar DOUBLE,
obar DOUBLE,
fobar DOUBLE,
ffbar DOUBLE,
oobar DOUBLE,
mae DOUBLE DEFAULT -9999,
CONSTRAINT line_data_sl1l2_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_sl1l2_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
DROP TABLE IF EXISTS line_data_grad;
CREATE TABLE line_data_grad
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
fgbar DOUBLE,
ogbar DOUBLE,
mgbar DOUBLE,
egbar DOUBLE,
s1 DOUBLE,
s1_og DOUBLE DEFAULT -9999,
fgog_ratio DOUBLE DEFAULT -9999,
dx INT DEFAULT -9999,
dy INT DEFAULT -9999,
CONSTRAINT line_data_grad_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_grad_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_sal1l2 contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_sal1l2;
CREATE TABLE line_data_sal1l2
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
fabar DOUBLE,
oabar DOUBLE,
foabar DOUBLE,
ffabar DOUBLE,
ooabar DOUBLE,
mae DOUBLE DEFAULT -9999,
CONSTRAINT line_data_sal2l1_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_sal2l1_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_vl1l2 contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_vl1l2;
CREATE TABLE line_data_vl1l2
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
ufbar DOUBLE,
vfbar DOUBLE,
uobar DOUBLE,
vobar DOUBLE,
uvfobar DOUBLE,
uvffbar DOUBLE,
uvoobar DOUBLE,
f_speed_bar DOUBLE DEFAULT -9999,
o_speed_bar DOUBLE DEFAULT -9999,
CONSTRAINT line_data_vl1l2_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_vl1l2_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_val1l2 contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_val1l2;
CREATE TABLE line_data_val1l2
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
ufabar DOUBLE,
vfabar DOUBLE,
uoabar DOUBLE,
voabar DOUBLE,
uvfoabar DOUBLE,
uvffabar DOUBLE,
uvooabar DOUBLE,
fa_speed_bar DOUBLE DEFAULT -9999,
oa_speed_bar DOUBLE DEFAULT -9999,
CONSTRAINT line_data_val1l2_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_val1l2_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)
) ENGINE = MyISAM
CHARACTER SET = latin1;
-- line_data_mpr contains stat data for a particular stat_header record, which it points
-- at via the stat_header_id field.
DROP TABLE IF EXISTS line_data_mpr;
CREATE TABLE line_data_mpr
(
stat_header_id INT UNSIGNED NOT NULL,
data_file_id INT UNSIGNED NOT NULL,
line_num INT UNSIGNED,
fcst_lead INT,
fcst_valid_beg DATETIME,
fcst_valid_end DATETIME,
fcst_init_beg DATETIME,
obs_lead INT UNSIGNED,
obs_valid_beg DATETIME,
obs_valid_end DATETIME,
total INT UNSIGNED,
mp_index INT UNSIGNED,
obs_sid VARCHAR(32),
obs_lat DOUBLE,
obs_lon DOUBLE,
obs_lvl DOUBLE,
obs_elv DOUBLE,
mpr_fcst DOUBLE,
mpr_obs DOUBLE,
mpr_climo DOUBLE,
obs_qc VARCHAR(32),
climo_mean DOUBLE,
climo_stdev DOUBLE,
climo_cdf DOUBLE,
CONSTRAINT line_data_mpr_data_file_id_pk
FOREIGN KEY (data_file_id)
REFERENCES data_file (data_file_id),
CONSTRAINT line_data_mpr_stat_header_id_pk
FOREIGN KEY (stat_header_id)
REFERENCES stat_header (stat_header_id),
INDEX stat_header_id_idx (stat_header_id)