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: _dateRangeEnd(text); Type: FUNCTION; Schema: py_functions; Owner: bien
41
--
42

    
43
CREATE FUNCTION "_dateRangeEnd"(value text) RETURNS text
44
    LANGUAGE sql IMMUTABLE
45
    AS $_$
46
SELECT (py_functions.parse_date_range($1))[2]
47
$_$;
48

    
49

    
50
ALTER FUNCTION py_functions."_dateRangeEnd"(value text) OWNER TO bien;
51

    
52
--
53
-- Name: _dateRangeStart(); Type: FUNCTION; Schema: py_functions; Owner: bien
54
--
55

    
56
CREATE FUNCTION "_dateRangeStart"() RETURNS trigger
57
    LANGUAGE plpgsql IMMUTABLE
58
    AS $$
59
BEGIN
60
    new.result := py_functions."_dateRangeStart"(new.value);
61
    RETURN new;
62
END;
63
$$;
64

    
65

    
66
ALTER FUNCTION py_functions."_dateRangeStart"() OWNER TO bien;
67

    
68
--
69
-- Name: _dateRangeStart(text); Type: FUNCTION; Schema: py_functions; Owner: bien
70
--
71

    
72
CREATE FUNCTION "_dateRangeStart"(value text) RETURNS text
73
    LANGUAGE sql IMMUTABLE
74
    AS $_$
75
SELECT (py_functions.parse_date_range($1))[1]
76
$_$;
77

    
78

    
79
ALTER FUNCTION py_functions."_dateRangeStart"(value text) OWNER TO bien;
80

    
81
--
82
-- Name: _namePart(); Type: FUNCTION; Schema: py_functions; Owner: bien
83
--
84

    
85
CREATE FUNCTION "_namePart"() RETURNS trigger
86
    LANGUAGE plpgsql IMMUTABLE
87
    AS $$
88
BEGIN
89
    new.result := py_functions."_namePart"(new.first, new.middle, new.last);
90
    RETURN new;
91
END;
92
$$;
93

    
94

    
95
ALTER FUNCTION py_functions."_namePart"() OWNER TO bien;
96

    
97
--
98
-- Name: _namePart(text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien
99
--
100

    
101
CREATE FUNCTION "_namePart"(first text DEFAULT NULL::text, middle text DEFAULT NULL::text, last text DEFAULT NULL::text) RETURNS text
102
    LANGUAGE plpythonu IMMUTABLE
103
    AS $$
104
params = dict(first=first, middle=middle, last=last)
105

    
106
_name_parts_slices_items = [
107
    ('first', slice(None, 1)),
108
    ('middle', slice(1, -1)),
109
    ('last', slice(-1, None)),
110
]
111
name_parts_slices = dict(_name_parts_slices_items)
112
name_parts = [name for name, slice_ in _name_parts_slices_items]
113

    
114
def _name(items):
115
    items = dict(items)
116
    parts = []
117
    for part in name_parts:
118
        if part in items: parts.append(items[part])
119
    return ' '.join(parts)
120

    
121
out_items = []
122
for part, value in params.iteritems():
123
    if value == None: continue
124
    
125
    try: slice_ = name_parts_slices[part]
126
    except KeyError: pass # a non-value column
127
    else: out_items.append((part, ' '.join(value.split(' ')[slice_])))
128

    
129
return _name(out_items)
130
$$;
131

    
132

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

    
135
--
136
-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_functions; Owner: bien
137
--
138

    
139
CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]
140
    LANGUAGE plpythonu IMMUTABLE STRICT
141
    AS $$
142
import re
143

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

    
146
def could_be_year(str_): return str_.isdigit() and len(str_) == 4
147

    
148
def could_be_day(str_): return str_.isdigit() and len(str_) <= 2
149

    
150
range_sep='-'
151
part_sep=' '
152

    
153
default = (str_, None)
154
# range_sep might be used as date part separator instead
155
if str_.find(part_sep) < 0: return default
156

    
157
start, sep, end = str_.partition(range_sep)
158
if sep == '': return default # not a range
159
start, end = (single_space(d).split(part_sep) for d in (start, end))
160

    
161
# Has form M D1-D2 or M D1-D2 Y (not M1 Y1-M2 Y2 or M1 D1-M2 D2)
162
if len(start) == 2 and (len(end) == 1 or (
163
        len(end) == 2 and could_be_day(start[-1]) and could_be_day(end[0])
164
        and could_be_year(end[-1])
165
    )):
166
    end.insert(0, start[0]) # make end fully specified
167
ct_diff = len(end) - len(start)
168
# Has form D1-D2 M Y, M1 D1-M2 D2 Y, M1-M2 Y, etc.
169
if ct_diff > 0: start += end[-ct_diff:] # make start fully specified
170
# Other forms are invalid and will be left as-is
171

    
172
return [part_sep.join(d) for d in (start, end)]
173
$$;
174

    
175

    
176
ALTER FUNCTION py_functions.parse_date_range(str_ text) OWNER TO bien;
177

    
178
SET default_tablespace = '';
179

    
180
SET default_with_oids = false;
181

    
182
--
183
-- Name: _dateRangeEnd; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: 
184
--
185

    
186
CREATE TABLE "_dateRangeEnd" (
187
    result text,
188
    not_null_col boolean DEFAULT true NOT NULL,
189
    value text
190
);
191

    
192

    
193
ALTER TABLE py_functions."_dateRangeEnd" OWNER TO bien;
194

    
195
--
196
-- Name: _dateRangeStart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: 
197
--
198

    
199
CREATE TABLE "_dateRangeStart" (
200
    result text,
201
    not_null_col boolean DEFAULT true NOT NULL,
202
    value text
203
);
204

    
205

    
206
ALTER TABLE py_functions."_dateRangeStart" OWNER TO bien;
207

    
208
--
209
-- Name: _namePart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: 
210
--
211

    
212
CREATE TABLE "_namePart" (
213
    result text,
214
    not_null_col boolean DEFAULT true NOT NULL,
215
    first text,
216
    middle text,
217
    last text
218
);
219

    
220

    
221
ALTER TABLE py_functions."_namePart" OWNER TO bien;
222

    
223
--
224
-- Name: _dateRangeEnd_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
225
--
226

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

    
229

    
230
--
231
-- Name: _dateRangeStart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
232
--
233

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

    
236

    
237
--
238
-- Name: _namePart_first; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
239
--
240

    
241
CREATE INDEX "_namePart_first" ON "_namePart" USING btree ((COALESCE(first, '\\N'::text)));
242

    
243

    
244
--
245
-- Name: _namePart_last; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
246
--
247

    
248
CREATE INDEX "_namePart_last" ON "_namePart" USING btree ((COALESCE(last, '\\N'::text)));
249

    
250

    
251
--
252
-- Name: _namePart_middle; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
253
--
254

    
255
CREATE INDEX "_namePart_middle" ON "_namePart" USING btree ((COALESCE(middle, '\\N'::text)));
256

    
257

    
258
--
259
-- Name: _namePart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
260
--
261

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

    
264

    
265
--
266
-- Name: _dateRangeEnd; Type: TRIGGER; Schema: py_functions; Owner: bien
267
--
268

    
269
CREATE TRIGGER "_dateRangeEnd" BEFORE INSERT OR UPDATE ON "_dateRangeEnd" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeEnd"();
270

    
271

    
272
--
273
-- Name: _dateRangeStart; Type: TRIGGER; Schema: py_functions; Owner: bien
274
--
275

    
276
CREATE TRIGGER "_dateRangeStart" BEFORE INSERT OR UPDATE ON "_dateRangeStart" FOR EACH ROW EXECUTE PROCEDURE "_dateRangeStart"();
277

    
278

    
279
--
280
-- Name: _namePart; Type: TRIGGER; Schema: py_functions; Owner: bien
281
--
282

    
283
CREATE TRIGGER "_namePart" BEFORE INSERT OR UPDATE ON "_namePart" FOR EACH ROW EXECUTE PROCEDURE py_functions."_namePart"();
284

    
285

    
286
--
287
-- PostgreSQL database dump complete
288
--
289

    
(8-8/19)