1 |
2620
|
aaronmk
|
--
|
2 |
|
|
-- PostgreSQL database dump
|
3 |
|
|
--
|
4 |
|
|
|
5 |
|
|
SET statement_timeout = 0;
|
6 |
|
|
SET client_encoding = 'UTF8';
|
7 |
6213
|
aaronmk
|
SET standard_conforming_strings = on;
|
8 |
2620
|
aaronmk
|
SET check_function_bodies = false;
|
9 |
|
|
SET client_min_messages = warning;
|
10 |
|
|
|
11 |
|
|
--
|
12 |
8183
|
aaronmk
|
-- Name: py_util; Type: SCHEMA; Schema: -; Owner: bien
|
13 |
2620
|
aaronmk
|
--
|
14 |
|
|
|
15 |
8183
|
aaronmk
|
CREATE SCHEMA py_util;
|
16 |
2620
|
aaronmk
|
|
17 |
|
|
|
18 |
8183
|
aaronmk
|
ALTER SCHEMA py_util OWNER TO bien;
|
19 |
3430
|
aaronmk
|
|
20 |
4982
|
aaronmk
|
--
|
21 |
8183
|
aaronmk
|
-- Name: SCHEMA py_util; Type: COMMENT; Schema: -; Owner: bien
|
22 |
4982
|
aaronmk
|
--
|
23 |
|
|
|
24 |
8183
|
aaronmk
|
COMMENT ON SCHEMA py_util IS 'IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.';
|
25 |
4982
|
aaronmk
|
|
26 |
|
|
|
27 |
8183
|
aaronmk
|
SET search_path = py_util, pg_catalog;
|
28 |
2630
|
aaronmk
|
|
29 |
2620
|
aaronmk
|
--
|
30 |
8183
|
aaronmk
|
-- Name: _date(timestamp with time zone); Type: FUNCTION; Schema: py_util; Owner: bien
|
31 |
4549
|
aaronmk
|
--
|
32 |
|
|
|
33 |
|
|
CREATE FUNCTION _date(date timestamp with time zone) RETURNS timestamp with time zone
|
34 |
|
|
LANGUAGE sql IMMUTABLE STRICT
|
35 |
|
|
AS $_$
|
36 |
|
|
SELECT $1
|
37 |
|
|
$_$;
|
38 |
|
|
|
39 |
|
|
|
40 |
8183
|
aaronmk
|
ALTER FUNCTION py_util._date(date timestamp with time zone) OWNER TO bien;
|
41 |
4549
|
aaronmk
|
|
42 |
|
|
--
|
43 |
8183
|
aaronmk
|
-- Name: _date(text, text, text); Type: FUNCTION; Schema: py_util; Owner: bien
|
44 |
3415
|
aaronmk
|
--
|
45 |
|
|
|
46 |
6218
|
aaronmk
|
CREATE FUNCTION _date(year text DEFAULT NULL::text, month text DEFAULT NULL::text, day text DEFAULT NULL::text) RETURNS text
|
47 |
6204
|
aaronmk
|
LANGUAGE plpython3u IMMUTABLE
|
48 |
3415
|
aaronmk
|
AS $$
|
49 |
|
|
global date, year, month, day
|
50 |
|
|
|
51 |
|
|
import datetime
|
52 |
|
|
|
53 |
3553
|
aaronmk
|
def e_msg(e): return e.args[0].rstrip()
|
54 |
3415
|
aaronmk
|
|
55 |
6218
|
aaronmk
|
# Year is required
|
56 |
|
|
if year == None:
|
57 |
|
|
if month == None and day == None: return None # entire date is empty
|
58 |
|
|
else: raise AssertionError(
|
59 |
|
|
'null value in column "year" violates not-null constraint')
|
60 |
3415
|
aaronmk
|
|
61 |
6218
|
aaronmk
|
# Convert month name to number
|
62 |
|
|
if month != None and not month.isdigit(): # month is name
|
63 |
|
|
month = str(datetime.datetime.strptime(month, '%b').month)
|
64 |
3553
|
aaronmk
|
|
65 |
6218
|
aaronmk
|
if month == None: month = 1
|
66 |
|
|
if day == None: day = 1
|
67 |
|
|
year, month, day = map(int, (year, month, day))
|
68 |
3415
|
aaronmk
|
|
69 |
6218
|
aaronmk
|
for try_num in range(2):
|
70 |
|
|
try:
|
71 |
|
|
date = datetime.date(year, month, day)
|
72 |
|
|
break
|
73 |
|
|
except ValueError as e:
|
74 |
|
|
if try_num > 0: raise # exception still raised after retry
|
75 |
|
|
msg = e_msg(e)
|
76 |
|
|
if msg == 'month must be in 1..12': # try swapping month and day
|
77 |
|
|
month, day = day, month
|
78 |
|
|
else: raise
|
79 |
3415
|
aaronmk
|
|
80 |
3416
|
aaronmk
|
return str(date)
|
81 |
3415
|
aaronmk
|
$$;
|
82 |
|
|
|
83 |
|
|
|
84 |
8183
|
aaronmk
|
ALTER FUNCTION py_util._date(year text, month text, day text) OWNER TO bien;
|
85 |
3430
|
aaronmk
|
|
86 |
3415
|
aaronmk
|
--
|
87 |
8183
|
aaronmk
|
-- Name: _date(integer, integer, integer); Type: FUNCTION; Schema: py_util; Owner: bien
|
88 |
7725
|
aaronmk
|
--
|
89 |
|
|
|
90 |
|
|
CREATE FUNCTION _date(year integer DEFAULT NULL::integer, month integer DEFAULT NULL::integer, day integer DEFAULT NULL::integer) RETURNS text
|
91 |
|
|
LANGUAGE sql IMMUTABLE STRICT
|
92 |
|
|
AS $_$
|
93 |
8183
|
aaronmk
|
SELECT py_util._date($1::text, $2::text, $3::text)
|
94 |
7725
|
aaronmk
|
$_$;
|
95 |
|
|
|
96 |
|
|
|
97 |
8183
|
aaronmk
|
ALTER FUNCTION py_util._date(year integer, month integer, day integer) OWNER TO bien;
|
98 |
7725
|
aaronmk
|
|
99 |
|
|
--
|
100 |
8183
|
aaronmk
|
-- Name: _dateRangeEnd(text); Type: FUNCTION; Schema: py_util; Owner: bien
|
101 |
2951
|
aaronmk
|
--
|
102 |
|
|
|
103 |
|
|
CREATE FUNCTION "_dateRangeEnd"(value text) RETURNS text
|
104 |
3634
|
aaronmk
|
LANGUAGE sql IMMUTABLE STRICT
|
105 |
2951
|
aaronmk
|
AS $_$
|
106 |
8183
|
aaronmk
|
SELECT (py_util.parse_date_range($1))[2]
|
107 |
2951
|
aaronmk
|
$_$;
|
108 |
|
|
|
109 |
|
|
|
110 |
8183
|
aaronmk
|
ALTER FUNCTION py_util."_dateRangeEnd"(value text) OWNER TO bien;
|
111 |
3430
|
aaronmk
|
|
112 |
2951
|
aaronmk
|
--
|
113 |
8183
|
aaronmk
|
-- Name: _dateRangeEnd(timestamp with time zone); Type: FUNCTION; Schema: py_util; Owner: bien
|
114 |
4542
|
aaronmk
|
--
|
115 |
|
|
|
116 |
|
|
CREATE FUNCTION "_dateRangeEnd"(value timestamp with time zone) RETURNS timestamp with time zone
|
117 |
|
|
LANGUAGE sql IMMUTABLE STRICT
|
118 |
|
|
AS $_$
|
119 |
|
|
SELECT $1
|
120 |
|
|
$_$;
|
121 |
|
|
|
122 |
|
|
|
123 |
8183
|
aaronmk
|
ALTER FUNCTION py_util."_dateRangeEnd"(value timestamp with time zone) OWNER TO bien;
|
124 |
4542
|
aaronmk
|
|
125 |
|
|
--
|
126 |
8183
|
aaronmk
|
-- Name: _dateRangeStart(text); Type: FUNCTION; Schema: py_util; Owner: bien
|
127 |
2951
|
aaronmk
|
--
|
128 |
|
|
|
129 |
|
|
CREATE FUNCTION "_dateRangeStart"(value text) RETURNS text
|
130 |
3634
|
aaronmk
|
LANGUAGE sql IMMUTABLE STRICT
|
131 |
2951
|
aaronmk
|
AS $_$
|
132 |
8183
|
aaronmk
|
SELECT (py_util.parse_date_range($1))[1]
|
133 |
2951
|
aaronmk
|
$_$;
|
134 |
|
|
|
135 |
|
|
|
136 |
8183
|
aaronmk
|
ALTER FUNCTION py_util."_dateRangeStart"(value text) OWNER TO bien;
|
137 |
3430
|
aaronmk
|
|
138 |
2951
|
aaronmk
|
--
|
139 |
8183
|
aaronmk
|
-- Name: _dateRangeStart(timestamp with time zone); Type: FUNCTION; Schema: py_util; Owner: bien
|
140 |
4542
|
aaronmk
|
--
|
141 |
|
|
|
142 |
|
|
CREATE FUNCTION "_dateRangeStart"(value timestamp with time zone) RETURNS timestamp with time zone
|
143 |
|
|
LANGUAGE sql IMMUTABLE STRICT
|
144 |
|
|
AS $_$
|
145 |
|
|
SELECT $1
|
146 |
|
|
$_$;
|
147 |
|
|
|
148 |
|
|
|
149 |
8183
|
aaronmk
|
ALTER FUNCTION py_util."_dateRangeStart"(value timestamp with time zone) OWNER TO bien;
|
150 |
4542
|
aaronmk
|
|
151 |
|
|
--
|
152 |
8183
|
aaronmk
|
-- Name: _namePart(text, text, text); Type: FUNCTION; Schema: py_util; Owner: bien
|
153 |
2952
|
aaronmk
|
--
|
154 |
|
|
|
155 |
|
|
CREATE FUNCTION "_namePart"(first text DEFAULT NULL::text, middle text DEFAULT NULL::text, last text DEFAULT NULL::text) RETURNS text
|
156 |
6204
|
aaronmk
|
LANGUAGE plpython3u IMMUTABLE
|
157 |
2635
|
aaronmk
|
AS $$
|
158 |
2952
|
aaronmk
|
params = dict(first=first, middle=middle, last=last)
|
159 |
2635
|
aaronmk
|
|
160 |
4983
|
aaronmk
|
def none_if(val, *none_vals):
|
161 |
|
|
for none_val in none_vals:
|
162 |
|
|
if val == none_val: return None
|
163 |
|
|
return val
|
164 |
|
|
|
165 |
2635
|
aaronmk
|
_name_parts_slices_items = [
|
166 |
4948
|
aaronmk
|
('first', slice(None, -1)),
|
167 |
2635
|
aaronmk
|
('middle', slice(1, -1)),
|
168 |
|
|
('last', slice(-1, None)),
|
169 |
|
|
]
|
170 |
|
|
name_parts_slices = dict(_name_parts_slices_items)
|
171 |
|
|
name_parts = [name for name, slice_ in _name_parts_slices_items]
|
172 |
|
|
|
173 |
|
|
def _name(items):
|
174 |
|
|
items = dict(items)
|
175 |
|
|
parts = []
|
176 |
|
|
for part in name_parts:
|
177 |
|
|
if part in items: parts.append(items[part])
|
178 |
4983
|
aaronmk
|
return none_if(' '.join(parts), '')
|
179 |
2635
|
aaronmk
|
|
180 |
|
|
out_items = []
|
181 |
2952
|
aaronmk
|
for part, value in params.iteritems():
|
182 |
2635
|
aaronmk
|
if value == None: continue
|
183 |
|
|
|
184 |
|
|
try: slice_ = name_parts_slices[part]
|
185 |
|
|
except KeyError: pass # a non-value column
|
186 |
|
|
else: out_items.append((part, ' '.join(value.split(' ')[slice_])))
|
187 |
|
|
|
188 |
2952
|
aaronmk
|
return _name(out_items)
|
189 |
2635
|
aaronmk
|
$$;
|
190 |
|
|
|
191 |
|
|
|
192 |
8183
|
aaronmk
|
ALTER FUNCTION py_util."_namePart"(first text, middle text, last text) OWNER TO bien;
|
193 |
3430
|
aaronmk
|
|
194 |
2635
|
aaronmk
|
--
|
195 |
8183
|
aaronmk
|
-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_util; Owner: bien
|
196 |
2630
|
aaronmk
|
--
|
197 |
|
|
|
198 |
|
|
CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]
|
199 |
6204
|
aaronmk
|
LANGUAGE plpython3u IMMUTABLE STRICT
|
200 |
2630
|
aaronmk
|
AS $$
|
201 |
|
|
import re
|
202 |
|
|
|
203 |
|
|
def single_space(str_): return re.sub(r' {2,}', r' ', str_.strip())
|
204 |
|
|
|
205 |
|
|
def could_be_year(str_): return str_.isdigit() and len(str_) == 4
|
206 |
|
|
|
207 |
|
|
def could_be_day(str_): return str_.isdigit() and len(str_) <= 2
|
208 |
|
|
|
209 |
|
|
range_sep='-'
|
210 |
|
|
part_sep=' '
|
211 |
|
|
|
212 |
4019
|
aaronmk
|
default = (str_, str_)
|
213 |
7107
|
aaronmk
|
if str_.find(':') >= 0: return default
|
214 |
2630
|
aaronmk
|
# range_sep might be used as date part separator instead
|
215 |
|
|
if str_.find(part_sep) < 0: return default
|
216 |
|
|
|
217 |
|
|
start, sep, end = str_.partition(range_sep)
|
218 |
|
|
if sep == '': return default # not a range
|
219 |
|
|
start, end = (single_space(d).split(part_sep) for d in (start, end))
|
220 |
|
|
|
221 |
|
|
# Has form M D1-D2 or M D1-D2 Y (not M1 Y1-M2 Y2 or M1 D1-M2 D2)
|
222 |
|
|
if len(start) == 2 and (len(end) == 1 or (
|
223 |
|
|
len(end) == 2 and could_be_day(start[-1]) and could_be_day(end[0])
|
224 |
|
|
and could_be_year(end[-1])
|
225 |
|
|
)):
|
226 |
|
|
end.insert(0, start[0]) # make end fully specified
|
227 |
|
|
ct_diff = len(end) - len(start)
|
228 |
|
|
# Has form D1-D2 M Y, M1 D1-M2 D2 Y, M1-M2 Y, etc.
|
229 |
|
|
if ct_diff > 0: start += end[-ct_diff:] # make start fully specified
|
230 |
|
|
# Other forms are invalid and will be left as-is
|
231 |
|
|
|
232 |
|
|
return [part_sep.join(d) for d in (start, end)]
|
233 |
|
|
$$;
|
234 |
|
|
|
235 |
|
|
|
236 |
8183
|
aaronmk
|
ALTER FUNCTION py_util.parse_date_range(str_ text) OWNER TO bien;
|
237 |
3430
|
aaronmk
|
|
238 |
2630
|
aaronmk
|
--
|
239 |
2620
|
aaronmk
|
-- PostgreSQL database dump complete
|
240 |
|
|
--
|