Project

General

Profile

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