--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET SESSION AUTHORIZATION 'bien';

--
-- Name: py_functions; Type: SCHEMA; Schema: -; Owner: bien
--

CREATE SCHEMA py_functions;


SET search_path = py_functions, pg_catalog;

--
-- Name: __dateRangeEnd(); Type: FUNCTION; Schema: py_functions; Owner: bien
--

CREATE FUNCTION "__dateRangeEnd"() RETURNS trigger
    LANGUAGE plpgsql IMMUTABLE
    AS $$
BEGIN
    new.result := py_functions."_dateRangeEnd"(new.value);
    RETURN new;
END;
$$;


--
-- Name: __dateRangeStart(); Type: FUNCTION; Schema: py_functions; Owner: bien
--

CREATE FUNCTION "__dateRangeStart"() RETURNS trigger
    LANGUAGE plpgsql IMMUTABLE
    AS $$
BEGIN
    new.result := py_functions."_dateRangeStart"(new.value);
    RETURN new;
END;
$$;


--
-- Name: __namePart(); Type: FUNCTION; Schema: py_functions; Owner: bien
--

CREATE FUNCTION "__namePart"() RETURNS trigger
    LANGUAGE plpgsql IMMUTABLE
    AS $$
BEGIN
    new.result := py_functions."_namePart"(new.first, new.middle, new.last);
    RETURN new;
END;
$$;


--
-- Name: _date(text, text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien
--

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
    LANGUAGE plpythonu IMMUTABLE
    AS $$
global date, year, month, day

import datetime
import dateutil.tz

def e_str(e):
    msg = e.__class__.__name__+': '+e.args[0]
    return msg.rstrip()

class ExceptionWithCause(Exception):
    def __init__(self, msg, cause):
        msg += ': '+e_str(cause)
        Exception.__init__(self, msg)

utc = dateutil.tz.tzutc()

def naive2utc(datetime_):
    assert datetime_.tzinfo == None
    return datetime_.replace(tzinfo=utc)

def from_timestamp(utc_timestamp):
    return naive2utc(datetime.datetime.utcfromtimestamp(utc_timestamp))

epoch = from_timestamp(0)

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

class FormatException(ExceptionWithCause):
    def __init__(self, cause):
        ExceptionWithCause.__init__(self, 'Invalid input value', cause)

if date != None:
    str_ = date
    try: year = float(str_)
    except ValueError:
        try: date = strtotime(str_)
        except ImportError: return str_
        except ValueError, e: raise FormatException(e)
    else: date = (datetime.date(int(year), 1, 1) +
        datetime.timedelta(round((year % 1.)*365)))
else:
    # Year is required
    if year == None:
        if month == None and day == None: return None # entire date is empty
        else: raise FormatException(ValueError('Year is required'))
    
    # Convert month name to number
    if month != None and not month.isdigit(): # month is name
        try: month = str(strtotime(month).month)
        except ValueError, e: raise FormatException(e)
    
    if month == None: month = 1
    if day == None: day = 1
    year, month, day = map(int, (year, month, day))
    
    for try_num in xrange(2):
        try:
            date = datetime.date(year, month, day)
            break
        except ValueError, e:
            if try_num > 0: raise FormatException(e)
                # exception still raised after retry
            msg = e_str(e)
            if msg == 'month must be in 1..12': # try swapping month and day
                month, day = day, month
            else: raise FormatException(e)

return str(date)
$$;


--
-- Name: _dateRangeEnd(text); Type: FUNCTION; Schema: py_functions; Owner: bien
--

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


--
-- Name: _dateRangeStart(text); Type: FUNCTION; Schema: py_functions; Owner: bien
--

CREATE FUNCTION "_dateRangeStart"(value text) RETURNS text
    LANGUAGE sql IMMUTABLE
    AS $_$
SELECT (py_functions.parse_date_range($1))[1]
$_$;


--
-- Name: _namePart(text, text, text); Type: FUNCTION; Schema: py_functions; Owner: bien
--

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

_name_parts_slices_items = [
    ('first', slice(None, 1)),
    ('middle', slice(1, -1)),
    ('last', slice(-1, None)),
]
name_parts_slices = dict(_name_parts_slices_items)
name_parts = [name for name, slice_ in _name_parts_slices_items]

def _name(items):
    items = dict(items)
    parts = []
    for part in name_parts:
        if part in items: parts.append(items[part])
    return ' '.join(parts)

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

return _name(out_items)
$$;


--
-- Name: parse_date_range(text); Type: FUNCTION; Schema: py_functions; Owner: bien
--

CREATE FUNCTION parse_date_range(str_ text) RETURNS text[]
    LANGUAGE plpythonu IMMUTABLE STRICT
    AS $$
import re

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

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

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

range_sep='-'
part_sep=' '

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

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

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

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


SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: _dateRangeEnd; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: 
--

CREATE TABLE "_dateRangeEnd" (
    result text,
    not_null_col boolean DEFAULT true NOT NULL,
    value text
);


--
-- Name: _dateRangeStart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: 
--

CREATE TABLE "_dateRangeStart" (
    result text,
    not_null_col boolean DEFAULT true NOT NULL,
    value text
);


--
-- Name: _namePart; Type: TABLE; Schema: py_functions; Owner: bien; Tablespace: 
--

CREATE TABLE "_namePart" (
    result text,
    not_null_col boolean DEFAULT true NOT NULL,
    first text,
    middle text,
    last text
);


--
-- Name: _dateRangeEnd_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
--

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


--
-- Name: _dateRangeStart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
--

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


--
-- Name: _namePart_unique; Type: INDEX; Schema: py_functions; Owner: bien; Tablespace: 
--

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


--
-- Name: _dateRangeEnd; Type: TRIGGER; Schema: py_functions; Owner: bien
--

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


--
-- Name: _dateRangeStart; Type: TRIGGER; Schema: py_functions; Owner: bien
--

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


--
-- Name: _namePart; Type: TRIGGER; Schema: py_functions; Owner: bien
--

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


--
-- PostgreSQL database dump complete
--