Project

General

Profile

1
--
2
-- PostgreSQL database dump
3
--
4

    
5
SET statement_timeout = 0;
6
SET client_encoding = 'UTF8';
7
SET standard_conforming_strings = on;
8
SET check_function_bodies = false;
9
SET client_min_messages = warning;
10

    
11
--
12
-- Name: py_functions; Type: SCHEMA; Schema: -; Owner: bien
13
--
14

    
15
CREATE SCHEMA py_functions;
16

    
17

    
18
ALTER SCHEMA py_functions OWNER TO bien;
19

    
20
--
21
-- Name: SCHEMA py_functions; Type: COMMENT; Schema: -; Owner: bien
22
--
23

    
24
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.';
25

    
26

    
27
SET search_path = py_functions, pg_catalog;
28

    
29
--
30
-- Name: _date(timestamp with time zone); Type: FUNCTION; Schema: py_functions; Owner: bien
31
--
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
ALTER FUNCTION py_functions._date(date timestamp with time zone) OWNER TO bien;
41

    
42
--
43
-- Name: _date(text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien
44
--
45

    
46
CREATE FUNCTION _date(year text DEFAULT NULL::text, month text DEFAULT NULL::text, day text DEFAULT NULL::text) RETURNS text
47
    LANGUAGE plpython3u IMMUTABLE
48
    AS $$
49
global date, year, month, day
50

    
51
import datetime
52

    
53
def e_msg(e): return e.args[0].rstrip()
54

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

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

    
65
if month == None: month = 1
66
if day == None: day = 1
67
year, month, day = map(int, (year, month, day))
68

    
69
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

    
80
return str(date)
81
$$;
82

    
83

    
84
ALTER FUNCTION py_functions._date(year text, month text, day text) OWNER TO bien;
85

    
86
--
87
-- Name: _dateRangeEnd(text); Type: FUNCTION; Schema: py_functions; Owner: bien
88
--
89

    
90
CREATE FUNCTION "_dateRangeEnd"(value text) RETURNS text
91
    LANGUAGE sql IMMUTABLE STRICT
92
    AS $_$
93
SELECT (py_functions.parse_date_range($1))[2]
94
$_$;
95

    
96

    
97
ALTER FUNCTION py_functions."_dateRangeEnd"(value text) OWNER TO bien;
98

    
99
--
100
-- Name: _dateRangeEnd(timestamp with time zone); Type: FUNCTION; Schema: py_functions; Owner: bien
101
--
102

    
103
CREATE FUNCTION "_dateRangeEnd"(value timestamp with time zone) RETURNS timestamp with time zone
104
    LANGUAGE sql IMMUTABLE STRICT
105
    AS $_$
106
SELECT $1
107
$_$;
108

    
109

    
110
ALTER FUNCTION py_functions."_dateRangeEnd"(value timestamp with time zone) OWNER TO bien;
111

    
112
--
113
-- Name: _dateRangeStart(text); Type: FUNCTION; Schema: py_functions; Owner: bien
114
--
115

    
116
CREATE FUNCTION "_dateRangeStart"(value text) RETURNS text
117
    LANGUAGE sql IMMUTABLE STRICT
118
    AS $_$
119
SELECT (py_functions.parse_date_range($1))[1]
120
$_$;
121

    
122

    
123
ALTER FUNCTION py_functions."_dateRangeStart"(value text) OWNER TO bien;
124

    
125
--
126
-- Name: _dateRangeStart(timestamp with time zone); Type: FUNCTION; Schema: py_functions; Owner: bien
127
--
128

    
129
CREATE FUNCTION "_dateRangeStart"(value timestamp with time zone) RETURNS timestamp with time zone
130
    LANGUAGE sql IMMUTABLE STRICT
131
    AS $_$
132
SELECT $1
133
$_$;
134

    
135

    
136
ALTER FUNCTION py_functions."_dateRangeStart"(value timestamp with time zone) OWNER TO bien;
137

    
138
--
139
-- Name: _namePart(text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien
140
--
141

    
142
CREATE FUNCTION "_namePart"(first text DEFAULT NULL::text, middle text DEFAULT NULL::text, last text DEFAULT NULL::text) RETURNS text
143
    LANGUAGE plpython3u IMMUTABLE
144
    AS $$
145
params = dict(first=first, middle=middle, last=last)
146

    
147
def none_if(val, *none_vals):
148
    for none_val in none_vals:
149
        if val == none_val: return None
150
    return val
151

    
152
_name_parts_slices_items = [
153
    ('first', slice(None, -1)),
154
    ('middle', slice(1, -1)),
155
    ('last', slice(-1, None)),
156
]
157
name_parts_slices = dict(_name_parts_slices_items)
158
name_parts = [name for name, slice_ in _name_parts_slices_items]
159

    
160
def _name(items):
161
    items = dict(items)
162
    parts = []
163
    for part in name_parts:
164
        if part in items: parts.append(items[part])
165
    return none_if(' '.join(parts), '')
166

    
167
out_items = []
168
for part, value in params.iteritems():
169
    if value == None: continue
170
    
171
    try: slice_ = name_parts_slices[part]
172
    except KeyError: pass # a non-value column
173
    else: out_items.append((part, ' '.join(value.split(' ')[slice_])))
174

    
175
return _name(out_items)
176
$$;
177

    
178

    
179
ALTER FUNCTION py_functions."_namePart"(first text, middle text, last text) OWNER TO bien;
180

    
181
--
182
-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_functions; Owner: bien
183
--
184

    
185
CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]
186
    LANGUAGE plpython3u IMMUTABLE STRICT
187
    AS $$
188
import re
189

    
190
def single_space(str_): return re.sub(r' {2,}', r' ', str_.strip())
191

    
192
def could_be_year(str_): return str_.isdigit() and len(str_) == 4
193

    
194
def could_be_day(str_): return str_.isdigit() and len(str_) <= 2
195

    
196
range_sep='-'
197
part_sep=' '
198

    
199
default = (str_, str_)
200
# range_sep might be used as date part separator instead
201
if str_.find(part_sep) < 0: return default
202

    
203
start, sep, end = str_.partition(range_sep)
204
if sep == '': return default # not a range
205
start, end = (single_space(d).split(part_sep) for d in (start, end))
206

    
207
# Has form M D1-D2 or M D1-D2 Y (not M1 Y1-M2 Y2 or M1 D1-M2 D2)
208
if len(start) == 2 and (len(end) == 1 or (
209
        len(end) == 2 and could_be_day(start[-1]) and could_be_day(end[0])
210
        and could_be_year(end[-1])
211
    )):
212
    end.insert(0, start[0]) # make end fully specified
213
ct_diff = len(end) - len(start)
214
# Has form D1-D2 M Y, M1 D1-M2 D2 Y, M1-M2 Y, etc.
215
if ct_diff > 0: start += end[-ct_diff:] # make start fully specified
216
# Other forms are invalid and will be left as-is
217

    
218
return [part_sep.join(d) for d in (start, end)]
219
$$;
220

    
221

    
222
ALTER FUNCTION py_functions.parse_date_range(str_ text) OWNER TO bien;
223

    
224
--
225
-- PostgreSQL database dump complete
226
--
227

    
(8-8/20)