Project

General

Profile

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

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

    
12
--
13
-- Name: py_util; Type: SCHEMA; Schema: -; Owner: bien
14
--
15

    
16
CREATE SCHEMA py_util;
17

    
18

    
19
ALTER SCHEMA py_util OWNER TO bien;
20

    
21
--
22
-- Name: SCHEMA py_util; Type: COMMENT; Schema: -; Owner: bien
23
--
24

    
25
COMMENT ON SCHEMA py_util IS '
26
IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.
27
';
28

    
29

    
30
SET search_path = py_util, pg_catalog;
31

    
32
--
33
-- Name: _date(timestamp with time zone); Type: FUNCTION; Schema: py_util; Owner: bien
34
--
35

    
36
CREATE FUNCTION _date(date timestamp with time zone) RETURNS timestamp with time zone
37
    LANGUAGE sql IMMUTABLE STRICT
38
    AS $_$
39
SELECT $1
40
$_$;
41

    
42

    
43
ALTER FUNCTION py_util._date(date timestamp with time zone) OWNER TO bien;
44

    
45
--
46
-- Name: _date(integer, integer, integer); Type: FUNCTION; Schema: py_util; Owner: bien
47
--
48

    
49
CREATE FUNCTION _date(year integer DEFAULT NULL::integer, month integer DEFAULT NULL::integer, day integer DEFAULT NULL::integer) RETURNS text
50
    LANGUAGE sql IMMUTABLE STRICT
51
    AS $_$
52
SELECT py_util._date($1::text, $2::text, $3::text)
53
$_$;
54

    
55

    
56
ALTER FUNCTION py_util._date(year integer, month integer, day integer) OWNER TO bien;
57

    
58
--
59
-- Name: _date(text, text, text); Type: FUNCTION; Schema: py_util; Owner: bien
60
--
61

    
62
CREATE FUNCTION _date(year text DEFAULT NULL::text, month text DEFAULT NULL::text, day text DEFAULT NULL::text) RETURNS text
63
    LANGUAGE plpython3u IMMUTABLE
64
    AS $$
65
global date, year, month, day
66

    
67
import datetime
68

    
69
def e_msg(e): return e.args[0].rstrip()
70

    
71
# Year is required
72
if year == None:
73
    if month == None and day == None: return None # entire date is empty
74
    else: raise AssertionError(
75
        'null value in column "year" violates not-null constraint')
76

    
77
# Convert month name to number
78
if month != None and not month.isdigit(): # month is name
79
    month = str(datetime.datetime.strptime(month, '%b').month)
80

    
81
if month == None: month = 1
82
if day == None: day = 1
83
year, month, day = map(int, (year, month, day))
84

    
85
for try_num in range(2):
86
    try:
87
        date = datetime.date(year, month, day)
88
        break
89
    except ValueError as e:
90
        if try_num > 0: raise # exception still raised after retry
91
        msg = e_msg(e)
92
        if msg == 'month must be in 1..12': # try swapping month and day
93
            month, day = day, month
94
        else: raise
95

    
96
return str(date)
97
$$;
98

    
99

    
100
ALTER FUNCTION py_util._date(year text, month text, day text) OWNER TO bien;
101

    
102
--
103
-- Name: _dateRangeEnd(text); Type: FUNCTION; Schema: py_util; Owner: bien
104
--
105

    
106
CREATE FUNCTION "_dateRangeEnd"(value text) RETURNS text
107
    LANGUAGE sql IMMUTABLE STRICT
108
    AS $_$
109
SELECT (py_util.parse_date_range($1))[2]
110
$_$;
111

    
112

    
113
ALTER FUNCTION py_util."_dateRangeEnd"(value text) OWNER TO bien;
114

    
115
--
116
-- Name: _dateRangeEnd(timestamp with time zone); Type: FUNCTION; Schema: py_util; Owner: bien
117
--
118

    
119
CREATE FUNCTION "_dateRangeEnd"(value timestamp with time zone) RETURNS timestamp with time zone
120
    LANGUAGE sql IMMUTABLE STRICT
121
    AS $_$
122
SELECT $1
123
$_$;
124

    
125

    
126
ALTER FUNCTION py_util."_dateRangeEnd"(value timestamp with time zone) OWNER TO bien;
127

    
128
--
129
-- Name: _dateRangeStart(text); Type: FUNCTION; Schema: py_util; Owner: bien
130
--
131

    
132
CREATE FUNCTION "_dateRangeStart"(value text) RETURNS text
133
    LANGUAGE sql IMMUTABLE STRICT
134
    AS $_$
135
SELECT (py_util.parse_date_range($1))[1]
136
$_$;
137

    
138

    
139
ALTER FUNCTION py_util."_dateRangeStart"(value text) OWNER TO bien;
140

    
141
--
142
-- Name: _dateRangeStart(timestamp with time zone); Type: FUNCTION; Schema: py_util; Owner: bien
143
--
144

    
145
CREATE FUNCTION "_dateRangeStart"(value timestamp with time zone) RETURNS timestamp with time zone
146
    LANGUAGE sql IMMUTABLE STRICT
147
    AS $_$
148
SELECT $1
149
$_$;
150

    
151

    
152
ALTER FUNCTION py_util."_dateRangeStart"(value timestamp with time zone) OWNER TO bien;
153

    
154
--
155
-- Name: _namePart(text, text, text); Type: FUNCTION; Schema: py_util; Owner: bien
156
--
157

    
158
CREATE FUNCTION "_namePart"(first text DEFAULT NULL::text, middle text DEFAULT NULL::text, last text DEFAULT NULL::text) RETURNS text
159
    LANGUAGE plpython3u IMMUTABLE
160
    AS $$
161
params = dict(first=first, middle=middle, last=last)
162

    
163
def none_if(val, *none_vals):
164
    for none_val in none_vals:
165
        if val == none_val: return None
166
    return val
167

    
168
_name_parts_slices_items = [
169
    ('first', slice(None, -1)),
170
    ('middle', slice(1, -1)),
171
    ('last', slice(-1, None)),
172
]
173
name_parts_slices = dict(_name_parts_slices_items)
174
name_parts = [name for name, slice_ in _name_parts_slices_items]
175

    
176
def _name(items):
177
    items = dict(items)
178
    parts = []
179
    for part in name_parts:
180
        if part in items: parts.append(items[part])
181
    return none_if(' '.join(parts), '')
182

    
183
out_items = []
184
for part, value in params.iteritems():
185
    if value == None: continue
186
    
187
    try: slice_ = name_parts_slices[part]
188
    except KeyError: pass # a non-value column
189
    else: out_items.append((part, ' '.join(value.split(' ')[slice_])))
190

    
191
return _name(out_items)
192
$$;
193

    
194

    
195
ALTER FUNCTION py_util."_namePart"(first text, middle text, last text) OWNER TO bien;
196

    
197
--
198
-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_util; Owner: bien
199
--
200

    
201
CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]
202
    LANGUAGE plpython3u IMMUTABLE STRICT
203
    AS $$
204
import re
205

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

    
208
def could_be_year(str_): return str_.isdigit() and len(str_) == 4
209

    
210
def could_be_day(str_): return str_.isdigit() and len(str_) <= 2
211

    
212
range_sep='-'
213
part_sep=' '
214

    
215
default = (str_, str_)
216
if str_.find(':') >= 0: return default
217
# range_sep might be used as date part separator instead
218
if str_.find(part_sep) < 0: return default
219

    
220
start, sep, end = str_.partition(range_sep)
221
if sep == '': return default # not a range
222
start, end = (single_space(d).split(part_sep) for d in (start, end))
223

    
224
# Has form M D1-D2 or M D1-D2 Y (not M1 Y1-M2 Y2 or M1 D1-M2 D2)
225
if len(start) == 2 and (len(end) == 1 or (
226
        len(end) == 2 and could_be_day(start[-1]) and could_be_day(end[0])
227
        and could_be_year(end[-1])
228
    )):
229
    end.insert(0, start[0]) # make end fully specified
230
ct_diff = len(end) - len(start)
231
# Has form D1-D2 M Y, M1 D1-M2 D2 Y, M1-M2 Y, etc.
232
if ct_diff > 0: start += end[-ct_diff:] # make start fully specified
233
# Other forms are invalid and will be left as-is
234

    
235
return [part_sep.join(d) for d in (start, end)]
236
$$;
237

    
238

    
239
ALTER FUNCTION py_util.parse_date_range(str_ text) OWNER TO bien;
240

    
241
--
242
-- PostgreSQL database dump complete
243
--
244

    
(16-16/29)