Project

General

Profile

1 2620 aaronmk
--
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 2623 aaronmk
-- Name: py_functions; Type: SCHEMA; Schema: -; Owner: bien
14 2620 aaronmk
--
15
16
CREATE SCHEMA py_functions;
17
18
19 2623 aaronmk
ALTER SCHEMA py_functions OWNER TO bien;
20
21 2630 aaronmk
SET search_path = py_functions, pg_catalog;
22
23 2620 aaronmk
--
24 2634 aaronmk
-- 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 := (SELECT (py_functions.parse_date_range(new.value))[2]);
32
    RETURN new;
33
END;
34
$$;
35
36
37
ALTER FUNCTION py_functions."_dateRangeEnd"() OWNER TO bien;
38
39
--
40 2630 aaronmk
-- 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 2632 aaronmk
    new.result := (SELECT (py_functions.parse_date_range(new.value))[1]);
48 2630 aaronmk
    RETURN new;
49
END;
50
$$;
51
52
53
ALTER FUNCTION py_functions."_dateRangeStart"() OWNER TO bien;
54
55
--
56 2635 aaronmk
-- Name: _namePart(); Type: FUNCTION; Schema: py_functions; Owner: bien
57
--
58
59
CREATE FUNCTION "_namePart"() RETURNS trigger
60
    LANGUAGE plpythonu IMMUTABLE
61
    AS $$
62
new = TD['new']
63
64
_name_parts_slices_items = [
65
    ('first', slice(None, 1)),
66
    ('middle', slice(1, -1)),
67
    ('last', slice(-1, None)),
68
]
69
name_parts_slices = dict(_name_parts_slices_items)
70
name_parts = [name for name, slice_ in _name_parts_slices_items]
71
72
def _name(items):
73
    items = dict(items)
74
    parts = []
75
    for part in name_parts:
76
        if part in items: parts.append(items[part])
77
    return ' '.join(parts)
78
79
out_items = []
80
for part, value in new.iteritems():
81
    if value == None: continue
82
83
    try: slice_ = name_parts_slices[part]
84
    except KeyError: pass # a non-value column
85
    else: out_items.append((part, ' '.join(value.split(' ')[slice_])))
86
87
new['result'] = _name(out_items)
88
89
return 'MODIFY'
90
$$;
91
92
93
ALTER FUNCTION py_functions."_namePart"() OWNER TO bien;
94
95
--
96 2630 aaronmk
-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_functions; Owner: bien
97
--
98
99
CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]
100
    LANGUAGE plpythonu IMMUTABLE STRICT
101
    AS $$
102
import re
103
104
def single_space(str_): return re.sub(r' {2,}', r' ', str_.strip())
105
106
def could_be_year(str_): return str_.isdigit() and len(str_) == 4
107
108
def could_be_day(str_): return str_.isdigit() and len(str_) <= 2
109
110
range_sep='-'
111
part_sep=' '
112
113
default = (str_, None)
114
# range_sep might be used as date part separator instead
115
if str_.find(part_sep) < 0: return default
116
117
start, sep, end = str_.partition(range_sep)
118
if sep == '': return default # not a range
119
start, end = (single_space(d).split(part_sep) for d in (start, end))
120
121
# Has form M D1-D2 or M D1-D2 Y (not M1 Y1-M2 Y2 or M1 D1-M2 D2)
122
if len(start) == 2 and (len(end) == 1 or (
123
        len(end) == 2 and could_be_day(start[-1]) and could_be_day(end[0])
124
        and could_be_year(end[-1])
125
    )):
126
    end.insert(0, start[0]) # make end fully specified
127
ct_diff = len(end) - len(start)
128
# Has form D1-D2 M Y, M1 D1-M2 D2 Y, M1-M2 Y, etc.
129
if ct_diff > 0: start += end[-ct_diff:] # make start fully specified
130
# Other forms are invalid and will be left as-is
131
132
return [part_sep.join(d) for d in (start, end)]
133
$$;
134
135
136
ALTER FUNCTION py_functions.parse_date_range(str_ text) OWNER TO bien;
137
138
SET default_tablespace = '';
139
140
SET default_with_oids = false;
141
142
--
143 2634 aaronmk
-- Name: _dateRangeEnd; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace:
144
--
145
146
CREATE TABLE "_dateRangeEnd" (
147
    result text,
148
    not_null_col boolean DEFAULT true NOT NULL,
149
    value text
150
);
151
152
153
ALTER TABLE py_functions."_dateRangeEnd" OWNER TO bien;
154
155
--
156 2630 aaronmk
-- Name: _dateRangeStart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace:
157
--
158
159
CREATE TABLE "_dateRangeStart" (
160
    result text,
161
    not_null_col boolean DEFAULT true NOT NULL,
162
    value text
163
);
164
165
166
ALTER TABLE py_functions."_dateRangeStart" OWNER TO bien;
167
168
--
169 2635 aaronmk
-- Name: _namePart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace:
170
--
171
172
CREATE TABLE "_namePart" (
173
    result text,
174
    not_null_col boolean DEFAULT true NOT NULL,
175
    first text,
176
    middle text,
177
    last text
178
);
179
180
181
ALTER TABLE py_functions."_namePart" OWNER TO bien;
182
183
--
184 2634 aaronmk
-- Name: _dateRangeEnd_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
185
--
186
187
CREATE UNIQUE INDEX "_dateRangeEnd_unique" ON "_dateRangeEnd" USING btree (functions.ensure_not_null(value));
188
189
190
--
191 2636 aaronmk
-- Name: _dateRangeEnd_value; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
192
--
193
194
CREATE INDEX "_dateRangeEnd_value" ON "_dateRangeEnd" USING btree (value);
195
196
197
--
198 2630 aaronmk
-- Name: _dateRangeStart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
199
--
200
201
CREATE UNIQUE INDEX "_dateRangeStart_unique" ON "_dateRangeStart" USING btree (functions.ensure_not_null(value));
202
203
204
--
205 2636 aaronmk
-- Name: _dateRangeStart_value; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
206
--
207
208
CREATE INDEX "_dateRangeStart_value" ON "_dateRangeStart" USING btree (value);
209
210
211
--
212
-- Name: _namePart_first; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
213
--
214
215
CREATE INDEX "_namePart_first" ON "_namePart" USING btree (first);
216
217
218
--
219
-- Name: _namePart_last; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
220
--
221
222
CREATE INDEX "_namePart_last" ON "_namePart" USING btree (last);
223
224
225
--
226
-- Name: _namePart_middle; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
227
--
228
229
CREATE INDEX "_namePart_middle" ON "_namePart" USING btree (middle);
230
231
232
--
233 2635 aaronmk
-- Name: _namePart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace:
234
--
235
236
CREATE UNIQUE INDEX "_namePart_unique" ON "_namePart" USING btree (functions.ensure_not_null(first), functions.ensure_not_null(middle), functions.ensure_not_null(last));
237
238
239
--
240 2634 aaronmk
-- Name: _dateRangeEnd; Type: TRIGGER; Schema: py_functions; Owner: bien
241
--
242
243
CREATE TRIGGER "_dateRangeEnd" BEFORE INSERT OR UPDATE ON "_dateRangeEnd" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeEnd"();
244
245
246
--
247 2630 aaronmk
-- Name: _dateRangeStart; Type: TRIGGER; Schema: py_functions; Owner: bien
248
--
249
250
CREATE TRIGGER "_dateRangeStart" BEFORE INSERT OR UPDATE ON "_dateRangeStart" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeStart"();
251
252
253
--
254 2635 aaronmk
-- Name: _namePart; Type: TRIGGER; Schema: py_functions; Owner: bien
255
--
256
257
CREATE TRIGGER "_namePart" BEFORE INSERT OR UPDATE ON "_namePart" FOR EACH ROW EXECUTE PROCEDURE "_namePart"();
258
259
260
--
261 2620 aaronmk
-- PostgreSQL database dump complete
262
--