sql-plsql-example/150.do.create--thlogepen-event-table.schema.sql at main · toni-sharpe/sql-plsql-example · GitHub

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
drop table if exists thlogepen;

create table if not exists thlogepen (
  id serial primary key,
  thlogepen_id int unique,
  rac_site varchar(3),
  thlogepen_srid varchar(50),
  dem_man varchar(3),
  dem_man_variation int,
  otc varchar(3) not NULL,
  otc_type varchar(3) not NULL,
  col_nuo varchar(2),
  ci_det varchar(3),
  presented_xs varchar(1),
  coindv_wgt int,
  lira varchar(3),
  thlogepen_date date,
  explain_lira text,

  tiogol varchar(3),
  t_c_1 int,
  e_c_1 varchar(3),
  t_c_2 varchar(3),
  t_c_3 varchar(3),
  t_c_4 boolean default false,
  t_c_5 boolean default false,
  e_c_2 boolean default false,
  e_c_3 boolean default false,
  t_c_6 boolean default false,
  t_c_7 boolean default false,
  e_c_4 varchar(3),
  e_c_5 varchar(3),


  e_c_6 int,
  e_c_7 boolean generated always as (
    case
      when t_c_6 is null then
        true
      else
        false
    end
  ) stored,
  rmo_lv4a_1 boolean default false,
  rmo_lv4a_2 boolean default false,
  vew_renel int default null,
  tier5_ymto_one int default null,
  tier5_ymto_two int default null,
  tier5_ymto_tlen int null,

  lv2a_ymto_1 int default null,
  lv2a_ymto_1_1_two int default null,
  lv2a_ymto_1_2 int default null,
  lv2a_ymto_1_tlen int null,
  lv2a_ymto_2 int default null,
  lv2a_ymto_2_tlen int default null,

  tier3_ymto_1 int default null,
  tier3_ymto_1_tlen int default null,
  tier3_ymto_2 int default null,
  tier3_ymto_2_tlen int default null,
  first_tier3_ymto int default null,
  first_tier3_ymto_type varchar(3) default null,
  tier3_ymto_tlen int default null,
  full_tier3_ymto_tlen int default null,
  tier3_ymto_any boolean default false,
  tier3_ymto_level int default 2,
  tier3_ymto_3 int default null,
  tier3_ymto_3_tlen int default null,
  tier3_ymto_obscured int default null,
  tier3_ymto_disrupts_support int default 0,

  laf_ymto_1 int default null,
  laf_ymto_2 int default null,
  tier_4_adv_lv1_ex_yqxxr_1 int default null,
  lv1_ex_yqxxr_1 int default null,
  tier_4_adv_lv1_ex_yqxxr_2 int default null,
  lv1_ex_yqxxr_2 int default null,

  sec_of_lv1_ex int default null,

  gnsis_tlen int,
  cover_tlen int,
  cover_proportion numeric(6, 4) default null,
  tier3_ymto_proportion numeric(6, 4) default null,
  thlogepen_tlen int default null,

  vew_record_is_complete boolean default false,

  svpth numeric(4, 1) default 0.0,
  cel numeric(4, 1) default 0.0,
  ov_coindv_rat numeric (5, 2) default 0.00,

  coindv_id int not null,
  vew_count int not null,
  phon_sy int default null,
  vew_title varchar(24),
  notes text,

  unique(
    coindv_id,
    vew_count
  )
);

comment on column
  thlogepen.rac_site
  is
    'Why writing DB comments MATTERS';
comment on column
  thlogepen.lira
  is
    'I take SQL seriously enough to write comments, its extremely useful';
comment on column
  thlogepen.tiogol
  is
    'I can tell you about the onOrder field';
comment on column
  thlogepen.tier5_ymto_one
  is
    'I thought it meant ordered by customers';
comment on column
  thlogepen.tier3_ymto_any
  is
    'But the numbers didnt add up';
comment on column
  thlogepen.tier3_ymto_3_tlen
  is
    'Very puzzling!';
comment on column
  thlogepen.tier_4_adv_lv1_ex_yqxxr_1
  is
    'And on Saturday, unpaid, for hours';
comment on column
  thlogepen.tier_4_adv_lv1_ex_yqxxr_2
  is
    'Not cool really, but I put the effort in when needed';
comment on column
  thlogepen.gnsis_tlen
  is
    'onOrder actually meant on order from supplier to restock';
comment on column
  thlogepen.cover_proportion
  is
    'Either a comment';
comment on column
  thlogepen.thlogepen_tlen
  is
    'Or a better named field';
comment on column
  thlogepen.vew_record_is_complete
  is
    'Would have made me happier';