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: __dateRangeEnd(); Type: FUNCTION; Schema: py_functions; Owner: bien
25
--
26

    
27
CREATE FUNCTION "__dateRangeEnd"() RETURNS trigger
28
    LANGUAGE plpgsql IMMUTABLE
29
    AS $$
30
BEGIN
31
    new.result := py_functions."_dateRangeEnd"(new.value);
32
    RETURN new;
33
END;
34
$$;
35

    
36

    
37
ALTER FUNCTION py_functions."__dateRangeEnd"() OWNER TO bien;
38

    
39
--
40
-- Name: __dateRangeStart(); Type: FUNCTION; Schema: py_functions; Owner: bien
41
--
42

    
43
CREATE FUNCTION "__dateRangeStart"() RETURNS trigger
44
    LANGUAGE plpgsql IMMUTABLE
45
    AS $$
46
BEGIN
47
    new.result := py_functions."_dateRangeStart"(new.value);
48
    RETURN new;
49
END;
50
$$;
51

    
52

    
53
ALTER FUNCTION py_functions."__dateRangeStart"() OWNER TO bien;
54

    
55
--
56
-- Name: __namePart(); Type: FUNCTION; Schema: py_functions; Owner: bien
57
--
58

    
59
CREATE FUNCTION "__namePart"() RETURNS trigger
60
    LANGUAGE plpgsql IMMUTABLE
61
    AS $$
62
BEGIN
63
    new.result := py_functions."_namePart"(new.first, new.middle, new.last);
64
    RETURN new;
65
END;
66
$$;
67

    
68

    
69
ALTER FUNCTION py_functions."__namePart"() OWNER TO bien;
70

    
71
--
72
-- Name: _date(text, text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien
73
--
74

    
75
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
76
    LANGUAGE plpythonu IMMUTABLE
77
    AS $$
78
global date, year, month, day
79

    
80
import datetime
81
import dateutil.tz
82

    
83
def e_msg(e): return e.args[0].rstrip()
84

    
85
def e_str(e): return e.__class__.__name__+': '+e_msg(e)
86

    
87
def raise_invalid_value(cause, value):
88
    raise ValueError('invalid input value: "'+str(value)+'"\nDETAIL:  '
89
        +e_str(cause))
90

    
91
utc = dateutil.tz.tzutc()
92

    
93
def naive2utc(datetime_):
94
    assert datetime_.tzinfo == None
95
    return datetime_.replace(tzinfo=utc)
96

    
97
def from_timestamp(utc_timestamp):
98
    return naive2utc(datetime.datetime.utcfromtimestamp(utc_timestamp))
99

    
100
epoch = from_timestamp(0)
101

    
102
def strtotime(str_, default=epoch):
103
    import dateutil.parser
104
    return dateutil.parser.parse(str_, default=default)
105

    
106
if date != None:
107
    str_ = date
108
    try: year = float(str_)
109
    except ValueError:
110
        try: date = strtotime(str_)
111
        except ImportError: return str_
112
        except ValueError, e: raise_invalid_value(e, str_)
113
    else: date = (datetime.date(int(year), 1, 1) +
114
        datetime.timedelta(round((year % 1.)*365)))
115
else:
116
    # Year is required
117
    if year == None:
118
        if month == None and day == None: return None # entire date is empty
119
        else: raise AssertionError(
120
            'null value in column "year" violates not-null constraint')
121
    
122
    # Convert month name to number
123
    if month != None and not month.isdigit(): # month is name
124
        month = str(strtotime(month).month)
125
    
126
    if month == None: month = 1
127
    if day == None: day = 1
128
    year, month, day = map(int, (year, month, day))
129
    
130
    for try_num in xrange(2):
131
        try:
132
            date = datetime.date(year, month, day)
133
            break
134
        except ValueError, e:
135
            if try_num > 0: raise # exception still raised after retry
136
            msg = e_msg(e)
137
            if msg == 'month must be in 1..12': # try swapping month and day
138
                month, day = day, month
139
            else: raise
140

    
141
return str(date)
142
$$;
143

    
144

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

    
147
--
148
-- Name: _dateRangeEnd(text); Type: FUNCTION; Schema: py_functions; Owner: bien
149
--
150

    
151
CREATE FUNCTION "_dateRangeEnd"(value text) RETURNS text
152
    LANGUAGE sql IMMUTABLE
153
    AS $_$
154
SELECT (py_functions.parse_date_range($1))[2]
155
$_$;
156

    
157

    
158
ALTER FUNCTION py_functions."_dateRangeEnd"(value text) OWNER TO bien;
159

    
160
--
161
-- Name: _dateRangeStart(text); Type: FUNCTION; Schema: py_functions; Owner: bien
162
--
163

    
164
CREATE FUNCTION "_dateRangeStart"(value text) RETURNS text
165
    LANGUAGE sql IMMUTABLE
166
    AS $_$
167
SELECT (py_functions.parse_date_range($1))[1]
168
$_$;
169

    
170

    
171
ALTER FUNCTION py_functions."_dateRangeStart"(value text) OWNER TO bien;
172

    
173
--
174
-- Name: _namePart(text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien
175
--
176

    
177
CREATE FUNCTION "_namePart"(first text DEFAULT NULL::text, middle text DEFAULT NULL::text, last text DEFAULT NULL::text) RETURNS text
178
    LANGUAGE plpythonu IMMUTABLE
179
    AS $$
180
params = dict(first=first, middle=middle, last=last)
181

    
182
_name_parts_slices_items = [
183
    ('first', slice(None, 1)),
184
    ('middle', slice(1, -1)),
185
    ('last', slice(-1, None)),
186
]
187
name_parts_slices = dict(_name_parts_slices_items)
188
name_parts = [name for name, slice_ in _name_parts_slices_items]
189

    
190
def _name(items):
191
    items = dict(items)
192
    parts = []
193
    for part in name_parts:
194
        if part in items: parts.append(items[part])
195
    return ' '.join(parts)
196

    
197
out_items = []
198
for part, value in params.iteritems():
199
    if value == None: continue
200
    
201
    try: slice_ = name_parts_slices[part]
202
    except KeyError: pass # a non-value column
203
    else: out_items.append((part, ' '.join(value.split(' ')[slice_])))
204

    
205
return _name(out_items)
206
$$;
207

    
208

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

    
211
--
212
-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_functions; Owner: bien
213
--
214

    
215
CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]
216
    LANGUAGE plpythonu IMMUTABLE STRICT
217
    AS $$
218
import re
219

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

    
222
def could_be_year(str_): return str_.isdigit() and len(str_) == 4
223

    
224
def could_be_day(str_): return str_.isdigit() and len(str_) <= 2
225

    
226
range_sep='-'
227
part_sep=' '
228

    
229
default = (str_, None)
230
# range_sep might be used as date part separator instead
231
if str_.find(part_sep) < 0: return default
232

    
233
start, sep, end = str_.partition(range_sep)
234
if sep == '': return default # not a range
235
start, end = (single_space(d).split(part_sep) for d in (start, end))
236

    
237
# Has form M D1-D2 or M D1-D2 Y (not M1 Y1-M2 Y2 or M1 D1-M2 D2)
238
if len(start) == 2 and (len(end) == 1 or (
239
        len(end) == 2 and could_be_day(start[-1]) and could_be_day(end[0])
240
        and could_be_year(end[-1])
241
    )):
242
    end.insert(0, start[0]) # make end fully specified
243
ct_diff = len(end) - len(start)
244
# Has form D1-D2 M Y, M1 D1-M2 D2 Y, M1-M2 Y, etc.
245
if ct_diff > 0: start += end[-ct_diff:] # make start fully specified
246
# Other forms are invalid and will be left as-is
247

    
248
return [part_sep.join(d) for d in (start, end)]
249
$$;
250

    
251

    
252
ALTER FUNCTION py_functions.parse_date_range(str_ text) OWNER TO bien;
253

    
254
SET default_tablespace = '';
255

    
256
SET default_with_oids = false;
257

    
258
--
259
-- Name: _dateRangeEnd; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: 
260
--
261

    
262
CREATE TABLE "_dateRangeEnd" (
263
    result text,
264
    not_null_col boolean DEFAULT true NOT NULL,
265
    value text
266
);
267

    
268

    
269
ALTER TABLE py_functions."_dateRangeEnd" OWNER TO bien;
270

    
271
--
272
-- Name: _dateRangeStart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: 
273
--
274

    
275
CREATE TABLE "_dateRangeStart" (
276
    result text,
277
    not_null_col boolean DEFAULT true NOT NULL,
278
    value text
279
);
280

    
281

    
282
ALTER TABLE py_functions."_dateRangeStart" OWNER TO bien;
283

    
284
--
285
-- Name: _namePart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: 
286
--
287

    
288
CREATE TABLE "_namePart" (
289
    result text,
290
    not_null_col boolean DEFAULT true NOT NULL,
291
    first text,
292
    middle text,
293
    last text
294
);
295

    
296

    
297
ALTER TABLE py_functions."_namePart" OWNER TO bien;
298

    
299
--
300
-- Name: _dateRangeEnd_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
301
--
302

    
303
CREATE UNIQUE INDEX "_dateRangeEnd_unique" ON "_dateRangeEnd" USING btree ((COALESCE(value, '\\N'::text)));
304

    
305

    
306
--
307
-- Name: _dateRangeStart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
308
--
309

    
310
CREATE UNIQUE INDEX "_dateRangeStart_unique" ON "_dateRangeStart" USING btree ((COALESCE(value, '\\N'::text)));
311

    
312

    
313
--
314
-- Name: _namePart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
315
--
316

    
317
CREATE UNIQUE INDEX "_namePart_unique" ON "_namePart" USING btree ((COALESCE(first, '\\N'::text)), (COALESCE(middle, '\\N'::text)), (COALESCE(last, '\\N'::text)));
318

    
319

    
320
--
321
-- Name: _dateRangeEnd; Type: TRIGGER; Schema: py_functions; Owner: bien
322
--
323

    
324
CREATE TRIGGER "_dateRangeEnd" BEFORE INSERT OR UPDATE ON "_dateRangeEnd" FOR EACH ROW EXECUTE PROCEDURE "__dateRangeEnd"();
325

    
326

    
327
--
328
-- Name: _dateRangeStart; Type: TRIGGER; Schema: py_functions; Owner: bien
329
--
330

    
331
CREATE TRIGGER "_dateRangeStart" BEFORE INSERT OR UPDATE ON "_dateRangeStart" FOR EACH ROW EXECUTE PROCEDURE "__dateRangeStart"();
332

    
333

    
334
--
335
-- Name: _namePart; Type: TRIGGER; Schema: py_functions; Owner: bien
336
--
337

    
338
CREATE TRIGGER "_namePart" BEFORE INSERT OR UPDATE ON "_namePart" FOR EACH ROW EXECUTE PROCEDURE "__namePart"();
339

    
340

    
341
--
342
-- PostgreSQL database dump complete
343
--
344

    
(8-8/19)