Project

General

Profile

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
--