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 STRICT
|
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 STRICT
|
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
|
|