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 = off;
8
SET check_function_bodies = false;
9
SET client_min_messages = warning;
10
SET escape_string_warning = off;
11

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

    
16
CREATE SCHEMA py_functions;
17

    
18

    
19
ALTER SCHEMA py_functions OWNER TO bien;
20

    
21
SET search_path = py_functions, pg_catalog;
22

    
23
--
24
-- Name: _date(timestamp with time zone); Type: FUNCTION; Schema: py_functions; Owner: bien
25
--
26

    
27
CREATE FUNCTION _date(date timestamp with time zone) RETURNS timestamp with time zone
28
    LANGUAGE sql IMMUTABLE STRICT
29
    AS $_$
30
SELECT $1
31
$_$;
32

    
33

    
34
ALTER FUNCTION py_functions._date(date timestamp with time zone) OWNER TO bien;
35

    
36
--
37
-- Name: _date(text, text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien
38
--
39

    
40
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
41
    LANGUAGE plpythonu IMMUTABLE
42
    AS $$
43
global date, year, month, day
44

    
45
import datetime
46
import dateutil.tz
47

    
48
def e_msg(e): return e.args[0].rstrip()
49

    
50
def e_str(e): return e.__class__.__name__+': '+e_msg(e)
51

    
52
def raise_invalid_value(cause, value):
53
    raise ValueError('invalid input value: "'+str(value)+'"\nDETAIL:  '
54
        +e_str(cause))
55

    
56
utc = dateutil.tz.tzutc()
57

    
58
def naive2utc(datetime_):
59
    assert datetime_.tzinfo == None
60
    return datetime_.replace(tzinfo=utc)
61

    
62
def from_timestamp(utc_timestamp):
63
    return naive2utc(datetime.datetime.utcfromtimestamp(utc_timestamp))
64

    
65
epoch = from_timestamp(0)
66

    
67
def strtotime(str_, default=epoch):
68
    import dateutil.parser
69
    return dateutil.parser.parse(str_, default=default)
70

    
71
if date != None:
72
    str_ = date
73
    try: year = float(str_)
74
    except ValueError:
75
        try: date = strtotime(str_)
76
        except ImportError: return str_
77
        except ValueError, e: raise_invalid_value(e, str_)
78
    else: date = (datetime.date(int(year), 1, 1) +
79
        datetime.timedelta(round((year % 1.)*365)))
80
else:
81
    # Year is required
82
    if year == None:
83
        if month == None and day == None: return None # entire date is empty
84
        else: raise AssertionError(
85
            'null value in column "year" violates not-null constraint')
86
    
87
    # Convert month name to number
88
    if month != None and not month.isdigit(): # month is name
89
        month = str(strtotime(month).month)
90
    
91
    if month == None: month = 1
92
    if day == None: day = 1
93
    year, month, day = map(int, (year, month, day))
94
    
95
    for try_num in xrange(2):
96
        try:
97
            date = datetime.date(year, month, day)
98
            break
99
        except ValueError, e:
100
            if try_num > 0: raise # exception still raised after retry
101
            msg = e_msg(e)
102
            if msg == 'month must be in 1..12': # try swapping month and day
103
                month, day = day, month
104
            else: raise
105

    
106
return str(date)
107
$$;
108

    
109

    
110
ALTER FUNCTION py_functions._date(date text, year text, month text, day text) OWNER TO bien;
111

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

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

    
122

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

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

    
129
CREATE FUNCTION "_dateRangeEnd"(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."_dateRangeEnd"(value timestamp with time zone) OWNER TO bien;
137

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

    
142
CREATE FUNCTION "_dateRangeStart"(value text) RETURNS text
143
    LANGUAGE sql IMMUTABLE STRICT
144
    AS $_$
145
SELECT (py_functions.parse_date_range($1))[1]
146
$_$;
147

    
148

    
149
ALTER FUNCTION py_functions."_dateRangeStart"(value text) OWNER TO bien;
150

    
151
--
152
-- Name: _dateRangeStart(timestamp with time zone); Type: FUNCTION; Schema: py_functions; Owner: bien
153
--
154

    
155
CREATE FUNCTION "_dateRangeStart"(value timestamp with time zone) RETURNS timestamp with time zone
156
    LANGUAGE sql IMMUTABLE STRICT
157
    AS $_$
158
SELECT $1
159
$_$;
160

    
161

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

    
164
--
165
-- Name: _namePart(text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien
166
--
167

    
168
CREATE FUNCTION "_namePart"(first text DEFAULT NULL::text, middle text DEFAULT NULL::text, last text DEFAULT NULL::text) RETURNS text
169
    LANGUAGE plpythonu IMMUTABLE
170
    AS $$
171
params = dict(first=first, middle=middle, last=last)
172

    
173
_name_parts_slices_items = [
174
    ('first', slice(None, 1)),
175
    ('middle', slice(1, -1)),
176
    ('last', slice(-1, None)),
177
]
178
name_parts_slices = dict(_name_parts_slices_items)
179
name_parts = [name for name, slice_ in _name_parts_slices_items]
180

    
181
def _name(items):
182
    items = dict(items)
183
    parts = []
184
    for part in name_parts:
185
        if part in items: parts.append(items[part])
186
    return ' '.join(parts)
187

    
188
out_items = []
189
for part, value in params.iteritems():
190
    if value == None: continue
191
    
192
    try: slice_ = name_parts_slices[part]
193
    except KeyError: pass # a non-value column
194
    else: out_items.append((part, ' '.join(value.split(' ')[slice_])))
195

    
196
return _name(out_items)
197
$$;
198

    
199

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

    
202
--
203
-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_functions; Owner: bien
204
--
205

    
206
CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]
207
    LANGUAGE plpythonu IMMUTABLE STRICT
208
    AS $$
209
import re
210

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

    
213
def could_be_year(str_): return str_.isdigit() and len(str_) == 4
214

    
215
def could_be_day(str_): return str_.isdigit() and len(str_) <= 2
216

    
217
range_sep='-'
218
part_sep=' '
219

    
220
default = (str_, str_)
221
# range_sep might be used as date part separator instead
222
if str_.find(part_sep) < 0: return default
223

    
224
start, sep, end = str_.partition(range_sep)
225
if sep == '': return default # not a range
226
start, end = (single_space(d).split(part_sep) for d in (start, end))
227

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

    
239
return [part_sep.join(d) for d in (start, end)]
240
$$;
241

    
242

    
243
ALTER FUNCTION py_functions.parse_date_range(str_ text) OWNER TO bien;
244

    
245
--
246
-- PostgreSQL database dump complete
247
--
248

    
(8-8/20)