From 0a0b9b0feb369d53af4ca7ffdd183db786f1127d Mon Sep 17 00:00:00 2001 From: Michael Orlitzky Date: Mon, 7 Oct 2013 10:21:19 -0400 Subject: [PATCH] Add a test suite which just creates/deletes databases for now. --- test/mailshears.test.conf.yml | 37 + test/rm_account_test.rb | 82 + test/sql/agendav.sql | 157 ++ test/sql/davical.sql | 4371 +++++++++++++++++++++++++++++++++ test/sql/postfixadmin.sql | 737 ++++++ test/sql/roundcube.sql | 608 +++++ 6 files changed, 5992 insertions(+) create mode 100644 test/mailshears.test.conf.yml create mode 100644 test/rm_account_test.rb create mode 100644 test/sql/agendav.sql create mode 100644 test/sql/davical.sql create mode 100644 test/sql/postfixadmin.sql create mode 100644 test/sql/roundcube.sql diff --git a/test/mailshears.test.conf.yml b/test/mailshears.test.conf.yml new file mode 100644 index 0000000..d02d89e --- /dev/null +++ b/test/mailshears.test.conf.yml @@ -0,0 +1,37 @@ +i_mean_business: false + +postfixadmin_dbhost: localhost +postfixadmin_dbport: 5432 +postfixadmin_dbopts: +postfixadmin_dbtty: +postfixadmin_dbuser: postgres +postfixadmin_dbpass: +postfixadmin_dbname: postfixadmin_test + +plugins: [postfixadmin, roundcube, agendav, davical] + +mail_root: /tmp + +roundcube_dbhost: localhost +roundcube_dbport: 5432 +roundcube_dbopts: +roundcube_dbtty: +roundcube_dbuser: postgres +roundcube_dbpass: +roundcube_dbname: roundcube_test + +agendav_dbhost: localhost +agendav_dbport: 5432 +agendav_dbopts: +agendav_dbtty: +agendav_dbuser: postgres +agendav_dbpass: +agendav_dbname: agendav_test + +davical_dbhost: localhost +davical_dbport: 5432 +davical_dbopts: +davical_dbtty: +davical_dbuser: postgres +davical_dbpass: +davical_dbname: davical_test diff --git a/test/rm_account_test.rb b/test/rm_account_test.rb new file mode 100644 index 0000000..b2d6174 --- /dev/null +++ b/test/rm_account_test.rb @@ -0,0 +1,82 @@ +require 'pg' +require 'test/unit' +require 'common/configuration' + +class RmAccountTest < Test::Unit::TestCase + + TESTCONF_PATH = 'test/mailshears.test.conf.yml' + + def connect_superuser() + db_host = 'localhost' + db_port = 5432 + db_opts = nil + db_tty = nil + db_name = 'postgres' + db_user = 'postgres' + db_pass = nil + + connection = PGconn.connect(db_host, + db_port, + db_opts, + db_tty, + db_name, + db_user, + db_pass) + + return connection + end + + + def test_dummy + assert(true) + end + + def setup + # Create databases using from the test configuration file. + cfg = Configuration.new(TESTCONF_PATH) + connection = connect_superuser() + + cfg.plugins.each do |plugin| + plugin_dbname = cfg.send("#{plugin}_dbname") + query = "CREATE DATABASE #{plugin_dbname};" + connection.query(query) + + plugin_dbhost = cfg.send("#{plugin}_dbhost") + plugin_dbport = cfg.send("#{plugin}_dbport") + plugin_dbopts = cfg.send("#{plugin}_dbopts") + plugin_dbtty = cfg.send("#{plugin}_dbtty") + plugin_dbuser = cfg.send("#{plugin}_dbuser") + plugin_dbpass = cfg.send("#{plugin}_dbpass") + + plugin_conn = PGconn.connect(plugin_dbhost, + plugin_dbport, + plugin_dbopts, + plugin_dbtty, + plugin_dbname, + plugin_dbuser, + plugin_dbpass) + + sql = File.open("test/sql/#{plugin}.sql").read() + plugin_conn.query(sql) + plugin_conn.close() + end + + connection.close() + end + + + def teardown + # Destroy databases using from the test configuration file. + cfg = Configuration.new(TESTCONF_PATH) + connection = connect_superuser() + + cfg.plugins.each do |plugin| + plugin_dbname = cfg.send("#{plugin}_dbname") + query = "DROP DATABASE #{plugin_dbname};" + connection.query(query) + end + + connection.close() + end + +end diff --git a/test/sql/agendav.sql b/test/sql/agendav.sql new file mode 100644 index 0000000..e9458a9 --- /dev/null +++ b/test/sql/agendav.sql @@ -0,0 +1,157 @@ +-- +-- PostgreSQL database dump +-- + +SET statement_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SET check_function_bodies = false; +SET client_min_messages = warning; + +-- +-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: +-- + +CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; + + +-- +-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: +-- + +COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; + + +SET search_path = public, pg_catalog; + +SET default_tablespace = ''; + +SET default_with_oids = false; + +-- +-- Name: migrations; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE migrations ( + version integer NOT NULL +); + + +ALTER TABLE public.migrations OWNER TO postgres; + +-- +-- Name: prefs; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE prefs ( + username character varying(255) NOT NULL, + options text NOT NULL +); + + +ALTER TABLE public.prefs OWNER TO postgres; + +-- +-- Name: sessions; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE sessions ( + session_id character varying(40) DEFAULT 0 NOT NULL, + ip_address character varying(16) DEFAULT 0 NOT NULL, + user_agent character varying(120) DEFAULT NULL::character varying, + last_activity bigint DEFAULT 0 NOT NULL, + user_data text NOT NULL +); + + +ALTER TABLE public.sessions OWNER TO postgres; + +-- +-- Name: shared_sid_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE shared_sid_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.shared_sid_seq OWNER TO postgres; + +-- +-- Name: shared; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE shared ( + sid integer DEFAULT nextval('shared_sid_seq'::regclass) NOT NULL, + user_from character varying(255) NOT NULL, + calendar character varying(255) NOT NULL, + user_which character varying(255) NOT NULL, + options text DEFAULT ''::text NOT NULL, + write_access boolean DEFAULT false NOT NULL +); + + +ALTER TABLE public.shared OWNER TO postgres; + +-- +-- Name: prefs_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY prefs + ADD CONSTRAINT prefs_pkey PRIMARY KEY (username); + + +-- +-- Name: sessions_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY sessions + ADD CONSTRAINT sessions_pkey PRIMARY KEY (session_id); + + +-- +-- Name: shared_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY shared + ADD CONSTRAINT shared_pkey PRIMARY KEY (sid); + + +-- +-- Name: last_activity_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX last_activity_idx ON sessions USING btree (last_activity); + + +-- +-- Name: sharedwithidx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX sharedwithidx ON shared USING btree (user_which); + + +-- +-- Name: shareidx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX shareidx ON shared USING btree (user_from, calendar); + + +-- +-- Name: public; Type: ACL; Schema: -; Owner: postgres +-- + +REVOKE ALL ON SCHEMA public FROM PUBLIC; +REVOKE ALL ON SCHEMA public FROM postgres; +GRANT ALL ON SCHEMA public TO postgres; +GRANT ALL ON SCHEMA public TO PUBLIC; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/test/sql/davical.sql b/test/sql/davical.sql new file mode 100644 index 0000000..1cbcf2a --- /dev/null +++ b/test/sql/davical.sql @@ -0,0 +1,4371 @@ +-- +-- PostgreSQL database dump +-- + +SET statement_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SET check_function_bodies = false; +SET client_min_messages = warning; + +-- +-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: +-- + +CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; + + +-- +-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: +-- + +COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; + + +SET search_path = public, pg_catalog; + +-- +-- Name: rrule_instance; Type: TYPE; Schema: public; Owner: postgres +-- + +CREATE TYPE rrule_instance AS ( + dtstart timestamp with time zone, + rrule text, + instance timestamp with time zone +); + + +ALTER TYPE public.rrule_instance OWNER TO postgres; + +-- +-- Name: rrule_parts; Type: TYPE; Schema: public; Owner: postgres +-- + +CREATE TYPE rrule_parts AS ( + base timestamp with time zone, + until timestamp with time zone, + freq text, + count integer, + "interval" integer, + bysecond integer[], + byminute integer[], + byhour integer[], + bymonthday integer[], + byyearday integer[], + byweekno integer[], + byday text[], + bymonth integer[], + bysetpos integer[], + wkst text +); + + +ALTER TYPE public.rrule_parts OWNER TO postgres; + +-- +-- Name: alarm_changed(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION alarm_changed() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + oldcomponent TEXT; + newcomponent TEXT; +BEGIN + -- in case we trigger on other events in future + IF TG_OP = 'UPDATE' THEN + IF NEW.component != OLD.component THEN + UPDATE caldav_data + SET caldav_data = replace( caldav_data, OLD.component, NEW.component ), + dav_etag = md5(replace( caldav_data, OLD.component, NEW.component )) + WHERE caldav_data.dav_id = NEW.dav_id; + END IF; + END IF; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.alarm_changed() OWNER TO postgres; + +-- +-- Name: apply_month_byday(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION apply_month_byday(timestamp with time zone, text) RETURNS timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + in_time ALIAS FOR $1; + byday ALIAS FOR $2; + weeks INT; + dow INT; + temp_txt TEXT; + dd INT; + mm INT; + yy INT; + our_dow INT; + our_answer TIMESTAMP WITH TIME ZONE; +BEGIN + dow := position(substring( byday from '..$') in 'SUMOTUWETHFRSA') / 2; + temp_txt := substring(byday from '([0-9]+)'); + weeks := temp_txt::int; + + -- RAISE NOTICE 'DOW: %, Weeks: %(%s)', dow, weeks, temp_txt; + + IF substring(byday for 1) = '-' THEN + -- Last XX of month, or possibly second-to-last, but unlikely + mm := extract( 'month' from in_time); + yy := extract( 'year' from in_time); + + -- Start with the last day of the month + our_answer := (yy::text || '-' || (mm+1)::text || '-01')::timestamp - '1 day'::interval; + dd := extract( 'dow' from our_answer); + dd := dd - dow; + IF dd < 0 THEN + dd := dd + 7; + END IF; + + -- Having calculated the right day of the month, we now apply that back to in_time + -- which contains the otherwise-unobtainable timezone detail (and the time) + our_answer = our_answer - (dd::text || 'days')::interval; + dd := extract( 'day' from our_answer) - extract( 'day' from in_time); + our_answer := in_time + (dd::text || 'days')::interval; + + IF weeks > 1 THEN + weeks := weeks - 1; + our_answer := our_answer - (weeks::text || 'weeks')::interval; + END IF; + + ELSE + + -- Shift our date to the correct day of week.. + our_dow := extract( 'dow' from in_time); + our_dow := our_dow - dow; + dd := extract( 'day' from in_time); + IF our_dow >= dd THEN + our_dow := our_dow - 7; + END IF; + our_answer := in_time - (our_dow::text || 'days')::interval; + dd = extract( 'day' from our_answer); + + -- Shift the date to the correct week... + dd := weeks - ((dd+6) / 7); + IF dd != 0 THEN + our_answer := our_answer + ((dd::text || 'weeks')::interval); + END IF; + + END IF; + + RETURN our_answer; + +END; +$_$; + + +ALTER FUNCTION public.apply_month_byday(timestamp with time zone, text) OWNER TO postgres; + +-- +-- Name: bits_to_legacy_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION bits_to_legacy_privilege(bit) RETURNS text + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + in_bits ALIAS FOR $1; + out_priv TEXT; +BEGIN + out_priv := ''; + IF in_bits = (~ 0::BIT(24)) THEN + out_priv = 'A'; + RETURN out_priv; + END IF; + + -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. + -- 1 DAV:read + -- 512 CalDAV:read-free-busy + -- 4096 CALDAV:schedule-query-freebusy + IF (in_bits & 4609::BIT(24)) != 0::BIT(24) THEN + IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN + out_priv := 'R'; + ELSE + out_priv := 'F'; + END IF; + END IF; + + -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content + -- 2 DAV:write-properties + -- 4 DAV:write-content + -- 64 DAV:bind + -- 128 DAV:unbind + IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN + IF (in_bits & 6::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || 'W'; + ELSE + IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || 'B'; + END IF; + IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || 'U'; + END IF; + END IF; + END IF; + + RETURN out_priv; +END +$_$; + + +ALTER FUNCTION public.bits_to_legacy_privilege(bit) OWNER TO postgres; + +-- +-- Name: bits_to_privilege(bit); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION bits_to_privilege(bit) RETURNS text[] + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + in_bits ALIAS FOR $1; + out_priv TEXT[]; +BEGIN + IF in_bits = (~ 0::BIT(24)) THEN + out_priv := out_priv || ARRAY['DAV:all']; + END IF; + + IF (in_bits & 513::BIT(24)) != 0::BIT(24) THEN + IF (in_bits & 1::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:read']; + END IF; + IF (in_bits & 512::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['caldav:read-free-busy']; + END IF; + END IF; + + IF (in_bits & 198::BIT(24)) != 0::BIT(24) THEN + IF (in_bits & 198::BIT(24)) = 198::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:write']; + ELSE + IF (in_bits & 2::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:write-properties']; + END IF; + IF (in_bits & 4::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:write-content']; + END IF; + IF (in_bits & 64::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:bind']; + END IF; + IF (in_bits & 128::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:unbind']; + END IF; + END IF; + END IF; + + IF (in_bits & 8::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:unlock']; + END IF; + + IF (in_bits & 16::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:read-acl']; + END IF; + + IF (in_bits & 32::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:read-current-user-privilege-set']; + END IF; + + IF (in_bits & 256::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['DAV:write-acl']; + END IF; + + IF (in_bits & 7168::BIT(24)) != 0::BIT(24) THEN + IF (in_bits & 7168::BIT(24)) = 7168::BIT(24) THEN + out_priv := out_priv || ARRAY['caldav:schedule-deliver']; + ELSE + IF (in_bits & 1024::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['caldav:schedule-deliver-invite']; + END IF; + IF (in_bits & 2048::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['caldav:schedule-deliver-reply']; + END IF; + IF (in_bits & 4096::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['caldav:schedule-query-freebusy']; + END IF; + END IF; + END IF; + + IF (in_bits & 57344::BIT(24)) != 0::BIT(24) THEN + IF (in_bits & 57344::BIT(24)) = 57344::BIT(24) THEN + out_priv := out_priv || ARRAY['caldav:schedule-send']; + ELSE + IF (in_bits & 8192::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['caldav:schedule-send-invite']; + END IF; + IF (in_bits & 16384::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['caldav:schedule-send-reply']; + END IF; + IF (in_bits & 32768::BIT(24)) != 0::BIT(24) THEN + out_priv := out_priv || ARRAY['caldav:schedule-send-freebusy']; + END IF; + END IF; + END IF; + + RETURN out_priv; +END +$_$; + + +ALTER FUNCTION public.bits_to_privilege(bit) OWNER TO postgres; + +-- +-- Name: calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text) RETURNS timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + earliest ALIAS FOR $1; + basedate ALIAS FOR $2; + repeatrule ALIAS FOR $3; + frequency TEXT; + temp_txt TEXT; + length INT; + count INT; + byday TEXT; + bymonthday INT; + basediff INTERVAL; + past_repeats INT8; + units TEXT; + dow TEXT; + our_answer TIMESTAMP WITH TIME ZONE; + loopcount INT; +BEGIN + IF basedate > earliest THEN + RETURN basedate; + END IF; + + temp_txt := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)'); + IF temp_txt IS NOT NULL AND temp_txt::timestamp with time zone < earliest THEN + RETURN NULL; + END IF; + + frequency := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)'); + IF frequency IS NULL THEN + RETURN NULL; + END IF; + + past_repeats = 0; + length = 1; + temp_txt := substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)'); + IF temp_txt IS NOT NULL THEN + length := temp_txt::int; + basediff := earliest - basedate; + + -- RAISE NOTICE 'Frequency: %, Length: %(%), Basediff: %', frequency, length, temp_txt, basediff; + + -- Calculate the number of past periods between our base date and our earliest date + IF frequency = 'WEEKLY' OR frequency = 'DAILY' THEN + past_repeats := extract('epoch' from basediff)::INT8 / 86400; + -- RAISE NOTICE 'Days: %', past_repeats; + IF frequency = 'WEEKLY' THEN + past_repeats := past_repeats / 7; + END IF; + ELSE + past_repeats = extract( 'years' from basediff ); + IF frequency = 'MONTHLY' THEN + past_repeats = (past_repeats *12) + extract( 'months' from basediff ); + END IF; + END IF; + IF length IS NOT NULL THEN + past_repeats = (past_repeats / length) + 1; + END IF; + END IF; + + -- Check that we have not exceeded the COUNT= limit + temp_txt := substring(repeatrule from 'COUNT=([0-9]+)(;|$)'); + IF temp_txt IS NOT NULL THEN + count := temp_txt::int; + -- RAISE NOTICE 'Periods: %, Count: %(%), length: %', past_repeats, count, temp_txt, length; + IF ( count <= past_repeats ) THEN + RETURN NULL; + END IF; + ELSE + count := NULL; + END IF; + + temp_txt := substring(repeatrule from 'BYSETPOS=([0-9-]+)(;|$)'); + byday := substring(repeatrule from 'BYDAY=([0-9A-Z,]+-)(;|$)'); + IF byday IS NOT NULL AND frequency = 'MONTHLY' THEN + -- Since this could move the date around a month we go back one + -- period just to be extra sure. + past_repeats = past_repeats - 1; + + IF temp_txt IS NOT NULL THEN + -- Crudely hack the BYSETPOS onto the front of BYDAY. While this + -- is not as per rfc2445, RRULE syntax is so complex and overblown + -- that nobody correctly uses comma-separated BYDAY or BYSETPOS, and + -- certainly not within a MONTHLY RRULE. + byday := temp_txt || byday; + END IF; + END IF; + + past_repeats = past_repeats * length; + + units := CASE + WHEN frequency = 'DAILY' THEN 'days' + WHEN frequency = 'WEEKLY' THEN 'weeks' + WHEN frequency = 'MONTHLY' THEN 'months' + WHEN frequency = 'YEARLY' THEN 'years' + END; + + temp_txt := substring(repeatrule from 'BYMONTHDAY=([0-9,]+)(;|$)'); + bymonthday := temp_txt::int; + + -- With all of the above calculation, this date should be close to (but less than) + -- the target, and we should only loop once or twice. + our_answer := basedate + (past_repeats::text || units)::interval; + + IF our_answer IS NULL THEN + RAISE EXCEPTION 'our_answer IS NULL! basedate:% past_repeats:% units:%', basedate, past_repeats, units; + END IF; + + + loopcount := 500; -- Desirable to stop an infinite loop if there is something we cannot handle + LOOP + -- RAISE NOTICE 'Testing date: %', our_answer; + IF frequency = 'DAILY' THEN + IF byday IS NOT NULL THEN + LOOP + dow = substring( to_char( our_answer, 'DY' ) for 2); + EXIT WHEN byday ~* dow; + -- Increment for our next time through the loop... + our_answer := our_answer + (length::text || units)::interval; + END LOOP; + END IF; + ELSIF frequency = 'WEEKLY' THEN + -- Weekly repeats are only on specific days + -- This is really not right, since a WEEKLY on MO,WE,FR should + -- occur three times each week and this will only be once a week. + dow = substring( to_char( our_answer, 'DY' ) for 2); + ELSIF frequency = 'MONTHLY' THEN + IF byday IS NOT NULL THEN + -- This works fine, except that maybe there are multiple BYDAY + -- components. e.g. 1TU,3TU might be 1st & 3rd tuesdays. + our_answer := apply_month_byday( our_answer, byday ); + ELSE + -- If we did not get a BYDAY= then we kind of have to assume it is the same day each month + our_answer := our_answer + '1 month'::interval; + END IF; + ELSIF bymonthday IS NOT NULL AND frequency = 'MONTHLY' AND bymonthday < 1 THEN + -- We do not deal with this situation at present + RAISE NOTICE 'The case of negative BYMONTHDAY is not handled yet.'; + END IF; + + EXIT WHEN our_answer >= earliest; + + -- Give up if we have exceeded the count + IF ( count IS NOT NULL AND past_repeats > count ) THEN + RETURN NULL; + ELSE + past_repeats := past_repeats + 1; + END IF; + + loopcount := loopcount - 1; + IF loopcount < 0 THEN + RAISE NOTICE 'Giving up on repeat rule "%" - after 100 increments from % we are still not after %', repeatrule, basedate, earliest; + RETURN NULL; + END IF; + + -- Increment for our next time through the loop... + our_answer := our_answer + (length::text || units)::interval; + + END LOOP; + + RETURN our_answer; + +END; +$_$; + + +ALTER FUNCTION public.calculate_later_timestamp(timestamp with time zone, timestamp with time zone, text) OWNER TO postgres; + +-- +-- Name: caldav_data_modified(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION caldav_data_modified() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + coll_id caldav_data.collection_id%TYPE; +BEGIN + IF TG_OP = 'UPDATE' THEN + IF NEW.caldav_data = OLD.caldav_data AND NEW.collection_id = OLD.collection_id THEN + -- Nothing for us to do + RETURN NEW; + END IF; + END IF; + + IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN + -- On insert or update modified, we set the NEW collection tag to the md5 of the + -- etag of the updated row which gives us something predictable for our regression + -- tests, but something different from the actual etag of the new event. + UPDATE collection + SET modified = current_timestamp, dav_etag = md5(NEW.dav_etag) + WHERE collection_id = NEW.collection_id; + IF TG_OP = 'INSERT' THEN + RETURN NEW; + END IF; + END IF; + + IF TG_OP = 'DELETE' THEN + -- On delete we set the OLD collection tag to the md5 of the old path & the old + -- etag, which again gives us something predictable for our regression tests. + UPDATE collection + SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag) + WHERE collection_id = OLD.collection_id; + RETURN OLD; + END IF; + + IF NEW.collection_id != OLD.collection_id THEN + -- If we've switched the collection_id of this event, then we also need to update + -- the etag of the old collection - as we do for delete. + UPDATE collection + SET modified = current_timestamp, dav_etag = md5(OLD.dav_name::text||OLD.dav_etag) + WHERE collection_id = OLD.collection_id; + END IF; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.caldav_data_modified() OWNER TO postgres; + +-- +-- Name: check_db_revision(integer, integer, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION check_db_revision(integer, integer, integer) RETURNS boolean + LANGUAGE plpgsql + AS $_$ + DECLARE + major ALIAS FOR $1; + minor ALIAS FOR $2; + patch ALIAS FOR $3; + matching INT; + BEGIN + SELECT COUNT(*) INTO matching FROM awl_db_revision + WHERE (schema_major = major AND schema_minor = minor AND schema_patch > patch) + OR (schema_major = major AND schema_minor > minor) + OR (schema_major > major) + ; + IF matching >= 1 THEN + RAISE EXCEPTION 'Database revisions after %.%.% have already been applied.', major, minor, patch; + RETURN FALSE; + END IF; + SELECT COUNT(*) INTO matching FROM awl_db_revision + WHERE schema_major = major AND schema_minor = minor AND schema_patch = patch; + IF matching >= 1 THEN + RETURN TRUE; + END IF; + RAISE EXCEPTION 'Database has not been upgraded to %.%.%', major, minor, patch; + RETURN FALSE; + END; +$_$; + + +ALTER FUNCTION public.check_db_revision(integer, integer, integer) OWNER TO postgres; + +-- +-- Name: collection_modified(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION collection_modified() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE +BEGIN + -- in case we trigger on other events in future + IF TG_OP = 'UPDATE' THEN + IF NEW.dav_name != OLD.dav_name THEN + UPDATE caldav_data + SET dav_name = replace( dav_name, OLD.dav_name, NEW.dav_name), + user_no = NEW.user_no + WHERE substring(dav_name from 1 for char_length(OLD.dav_name)) = OLD.dav_name; + END IF; + END IF; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.collection_modified() OWNER TO postgres; + +-- +-- Name: collections_within(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION collections_within(integer, integer) RETURNS SETOF integer + LANGUAGE plpgsql + AS $_$ +DECLARE + in_collection_id ALIAS FOR $1; + in_depth ALIAS FOR $2; + resource_id INT; + found_some BOOLEAN; +BEGIN + in_depth := in_depth - 1; + found_some = FALSE; + FOR resource_id IN SELECT b.bound_source_id FROM dav_binding b + JOIN collection pc ON (b.parent_container = pc.dav_name) + WHERE pc.collection_id = in_collection_id + LOOP + found_some = TRUE; + RETURN NEXT resource_id; + IF in_depth > 0 THEN + FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP + RETURN NEXT resource_id; + END LOOP; + END IF; + END LOOP; + FOR resource_id IN SELECT c.collection_id FROM collection c + JOIN collection pc ON (c.parent_container = pc.dav_name) + WHERE pc.collection_id = in_collection_id + LOOP + found_some = TRUE; + RETURN NEXT resource_id; + IF in_depth > 0 THEN + FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP + RETURN NEXT resource_id; + END LOOP; + END IF; + END LOOP; + IF found_some THEN + RETURN; + END IF; + FOR resource_id IN SELECT c.collection_id FROM collection c + JOIN dav_principal pc ON (c.parent_container = pc.dav_name) + WHERE pc.principal_id = in_collection_id + LOOP + RETURN NEXT resource_id; + IF in_depth > 0 THEN + FOR resource_id IN SELECT * FROM collections_within( resource_id, in_depth ) LOOP + RETURN NEXT resource_id; + END LOOP; + END IF; + END LOOP; +END; +$_$; + + +ALTER FUNCTION public.collections_within(integer, integer) OWNER TO postgres; + +-- +-- Name: daily_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION daily_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + after ALIAS FOR $1; + rrule ALIAS FOR $2; +BEGIN + + IF rrule.bymonth IS NOT NULL AND NOT date_part('month',after) = ANY ( rrule.bymonth ) THEN + RETURN; + END IF; + + IF rrule.byweekno IS NOT NULL AND NOT date_part('week',after) = ANY ( rrule.byweekno ) THEN + RETURN; + END IF; + + IF rrule.byyearday IS NOT NULL AND NOT date_part('doy',after) = ANY ( rrule.byyearday ) THEN + RETURN; + END IF; + + IF rrule.bymonthday IS NOT NULL AND NOT date_part('day',after) = ANY ( rrule.bymonthday ) THEN + RETURN; + END IF; + + IF rrule.byday IS NOT NULL AND NOT substring( to_char( after, 'DY') for 2 from 1) = ANY ( rrule.byday ) THEN + RETURN; + END IF; + + -- Since we don't do BYHOUR, BYMINUTE or BYSECOND yet this becomes a trivial + RETURN NEXT after; + +END; +$_$; + + +ALTER FUNCTION public.daily_set(timestamp with time zone, rrule_parts) OWNER TO postgres; + +-- +-- Name: event_has_exceptions(text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION event_has_exceptions(text) RETURNS boolean + LANGUAGE sql IMMUTABLE STRICT + AS $_$ + SELECT $1 ~ E'\nRECURRENCE-ID(;TZID=[^:]+)?:[[:space:]]*[[:digit:]]{8}(T[[:digit:]]{6})?' +$_$; + + +ALTER FUNCTION public.event_has_exceptions(text) OWNER TO postgres; + +-- +-- Name: event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION event_instances(timestamp with time zone, text) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + basedate ALIAS FOR $1; + repeatrule ALIAS FOR $2; + maxdate TIMESTAMP WITH TIME ZONE; +BEGIN + maxdate := current_date + '10 years'::interval; + RETURN QUERY SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d; +END; +$_$; + + +ALTER FUNCTION public.event_instances(timestamp with time zone, text) OWNER TO postgres; + +-- +-- Name: expand_members(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION expand_members(bigint, integer) RETURNS SETOF bigint + LANGUAGE sql STABLE STRICT + AS $_$ + SELECT member_id FROM group_member WHERE group_id = $1 + UNION + SELECT expanded.m_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_members( member_id, $2 - 1) END AS m_id + FROM group_member WHERE group_id = $1) AS expanded + WHERE expanded.m_id IS NOT NULL; +$_$; + + +ALTER FUNCTION public.expand_members(bigint, integer) OWNER TO postgres; + +-- +-- Name: expand_memberships(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION expand_memberships(bigint, integer) RETURNS SETOF bigint + LANGUAGE sql STABLE STRICT + AS $_$ + SELECT group_id FROM group_member WHERE member_id = $1 + UNION + SELECT expanded.g_id FROM (SELECT CASE WHEN $2 > 0 THEN expand_memberships( group_id, $2 - 1) END AS g_id + FROM group_member WHERE member_id = $1) AS expanded + WHERE expanded.g_id IS NOT NULL; +$_$; + + +ALTER FUNCTION public.expand_memberships(bigint, integer) OWNER TO postgres; + +-- +-- Name: get_group_role_no(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION get_group_role_no() RETURNS integer + LANGUAGE sql IMMUTABLE + AS $$ + SELECT role_no FROM roles WHERE role_name = 'Group' +$$; + + +ALTER FUNCTION public.get_group_role_no() OWNER TO postgres; + +-- +-- Name: get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION get_permissions(integer, integer) RETURNS text + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + in_from ALIAS FOR $1; + in_to ALIAS FOR $2; + out_confers TEXT; + bit_confers BIT(24); + group_role_no INT; + tmp_txt TEXT; + dbg TEXT DEFAULT ''; + r RECORD; + counter INT; +BEGIN + -- Self can always have full access + IF in_from = in_to THEN + RETURN 'A'; + END IF; + + -- dbg := 'S-'; + SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 + WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group'); + IF FOUND THEN + RETURN dbg || out_confers; + END IF; + -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to; + + SELECT bit_or(r1.confers & r2.confers) INTO bit_confers + FROM relationship r1 + JOIN relationship r2 ON r1.to_user=r2.from_user + WHERE r1.from_user=in_from AND r2.to_user=in_to + AND r2.from_user IN (SELECT user_no FROM roles LEFT JOIN role_member USING(role_no) WHERE role_name='Group'); + IF bit_confers != 0::BIT(24) THEN + RETURN dbg || bits_to_legacy_privilege(bit_confers); + END IF; + + RETURN ''; + -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to; + + SELECT bits_to_legacy_privilege(r1.confers) INTO out_confers FROM relationship r1 LEFT OUTER JOIN relationship r2 ON(r1.to_user = r2.to_user) + WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user + AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) ; + + IF FOUND THEN + -- dbg := 'H-'; + -- RAISE NOTICE 'Permissions to shared group % ', out_confers; + RETURN dbg || out_confers; + END IF; + + -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; + + RETURN ''; +END; +$_$; + + +ALTER FUNCTION public.get_permissions(integer, integer) OWNER TO postgres; + +-- +-- Name: get_usr_setting(integer, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION get_usr_setting(integer, text) RETURNS text + LANGUAGE sql + AS $_$SELECT setting_value FROM usr_setting + WHERE usr_setting.user_no = $1 + AND usr_setting.setting_name = $2 $_$; + + +ALTER FUNCTION public.get_usr_setting(integer, text) OWNER TO postgres; + +-- +-- Name: grants_modified(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION grants_modified() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + old_to_principal INT8; + new_is_group BOOL; +BEGIN + -- in case we trigger on other events in future + IF TG_OP = 'INSERT' THEN + old_to_principal := NULL; + ELSE + old_to_principal := OLD.to_principal; + END IF; + IF TG_OP = 'INSERT' OR NEW.to_principal != old_to_principal THEN + SELECT (type_id = 3) INTO new_is_group FROM principal WHERE principal_id = NEW.to_principal; + IF NEW.is_group != new_is_group THEN + NEW.is_group := new_is_group; + END IF; + END IF; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.grants_modified() OWNER TO postgres; + +-- +-- Name: grants_proxy_access_from_p(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION grants_proxy_access_from_p(bigint, integer) RETURNS SETOF bigint + LANGUAGE sql STABLE STRICT + AS $_$ + SELECT DISTINCT by_principal + FROM grants + WHERE by_collection IS NULL AND by_principal != $1 + AND by_principal IN (SELECT expand_members(g2.to_principal,$2) FROM grants g2 WHERE g2.by_principal = $1) + ; +$_$; + + +ALTER FUNCTION public.grants_proxy_access_from_p(bigint, integer) OWNER TO postgres; + +-- +-- Name: has_legacy_privilege(integer, text, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION has_legacy_privilege(integer, text, integer) RETURNS boolean + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + in_from ALIAS FOR $1; + in_legacy_privilege ALIAS FOR $2; + in_to ALIAS FOR $3; + in_confers BIT(24); + group_role_no INT; +BEGIN + -- Self can always have full access + IF in_from = in_to THEN + RETURN TRUE; + END IF; + + SELECT get_group_role_no() INTO group_role_no; + SELECT legacy_privilege_to_bits(in_legacy_privilege) INTO in_confers; + + IF EXISTS(SELECT 1 FROM relationship WHERE from_user = in_from AND to_user = in_to + AND (in_confers & confers) = in_confers + AND NOT EXISTS(SELECT 1 FROM role_member WHERE to_user = user_no AND role_no = group_role_no) ) THEN + -- A direct relationship from A to B that grants sufficient + -- RAISE NOTICE 'Permissions directly granted'; + RETURN TRUE; + END IF; + + IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.from_user + WHERE (in_confers & r1.confers & r2.confers) = in_confers + AND r1.from_user=in_from AND r2.to_user=in_to + AND r2.from_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) ) THEN + -- An indirect relationship from A to B via group G that grants sufficient + -- RAISE NOTICE 'Permissions mediated via group'; + RETURN TRUE; + END IF; + + IF EXISTS( SELECT 1 FROM relationship r1 JOIN relationship r2 ON r1.to_user=r2.to_user + WHERE (in_confers & r1.confers & r2.confers) = in_confers + AND r1.from_user=in_from AND r2.from_user=in_to + AND r2.to_user IN (SELECT user_no FROM role_member WHERE role_no=group_role_no) + AND NOT EXISTS(SELECT 1 FROM relationship WHERE from_user=r2.to_user) ) THEN + -- An indirect reflexive relationship from both A & B to group G which grants sufficient + -- RAISE NOTICE 'Permissions to shared group'; + RETURN TRUE; + END IF; + + -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; + + RETURN FALSE; +END; +$_$; + + +ALTER FUNCTION public.has_legacy_privilege(integer, text, integer) OWNER TO postgres; + +-- +-- Name: has_members_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION has_members_list(bigint) RETURNS text + LANGUAGE plpgsql STRICT + AS $_$ +DECLARE + in_member_id ALIAS FOR $1; + m RECORD; + mlist TEXT; +BEGIN + mlist := ''; + FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (member_id = principal_id) + WHERE group_id = in_member_id + LOOP + mlist := mlist + || CASE WHEN mlist = '' THEN '' ELSE ', ' END + || COALESCE( m.displayname, m.group_id::text); + END LOOP; + RETURN mlist; +END; +$_$; + + +ALTER FUNCTION public.has_members_list(bigint) OWNER TO postgres; + +-- +-- Name: icalendar_interval_to_sql(text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION icalendar_interval_to_sql(text) RETURNS interval + LANGUAGE sql IMMUTABLE STRICT + AS $_$ + SELECT CASE WHEN substring($1,1,1) = '-' THEN -1 ELSE 1 END * regexp_replace( regexp_replace($1, '[PT-]', '', 'g'), '([A-Z])', E'\\1 ', 'g')::interval; +$_$; + + +ALTER FUNCTION public.icalendar_interval_to_sql(text) OWNER TO postgres; + +-- +-- Name: is_member_of_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION is_member_of_list(bigint) RETURNS text + LANGUAGE plpgsql STRICT + AS $_$ +DECLARE + in_member_id ALIAS FOR $1; + m RECORD; + mlist TEXT; +BEGIN + mlist := ''; + FOR m IN SELECT displayname, group_id FROM group_member JOIN principal ON (group_id = principal_id) + WHERE member_id = in_member_id + LOOP + mlist := mlist + || CASE WHEN mlist = '' THEN '' ELSE ', ' END + || COALESCE( m.displayname, m.group_id::text); + END LOOP; + RETURN mlist; +END; +$_$; + + +ALTER FUNCTION public.is_member_of_list(bigint) OWNER TO postgres; + +-- +-- Name: legacy_get_permissions(integer, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION legacy_get_permissions(integer, integer) RETURNS text + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + in_from ALIAS FOR $1; + in_to ALIAS FOR $2; + out_confers TEXT; + tmp_confers1 TEXT; + tmp_confers2 TEXT; + tmp_txt TEXT; + dbg TEXT DEFAULT ''; + r RECORD; + counter INT; +BEGIN + -- Self can always have full access + IF in_from = in_to THEN + RETURN 'A'; + END IF; + + -- dbg := 'S-'; + SELECT rt1.confers INTO out_confers FROM relationship r1 JOIN relationship_type rt1 USING ( rt_id ) + WHERE r1.from_user = in_from AND r1.to_user = in_to AND NOT usr_is_role(r1.to_user,'Group'); + IF FOUND THEN + RETURN dbg || out_confers; + END IF; + -- RAISE NOTICE 'No simple relationships between % and %', in_from, in_to; + + out_confers := ''; + FOR r IN SELECT rt1.confers AS r1, rt2.confers AS r2 FROM relationship r1 JOIN relationship_type rt1 USING(rt_id) + JOIN relationship r2 ON r1.to_user=r2.from_user JOIN relationship_type rt2 ON r2.rt_id=rt2.rt_id + WHERE r1.from_user=in_from AND r2.to_user=in_to + AND EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.to_user AND roles.role_name='Group') + AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r2.to_user AND roles.role_name='Group') + AND NOT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=r1.from_user AND roles.role_name='Group') + LOOP + -- RAISE NOTICE 'Permissions to group % from group %', r.r1, r.r2; + -- FIXME: This is an oversimplification + -- dbg := 'C-'; + tmp_confers1 := r.r1; + tmp_confers2 := r.r2; + IF tmp_confers1 != tmp_confers2 THEN + IF tmp_confers1 ~* 'A' THEN + -- Ensure that A is expanded to all supported privs before being used as a mask + tmp_confers1 := 'AFBRWU'; + END IF; + IF tmp_confers2 ~* 'A' THEN + -- Ensure that A is expanded to all supported privs before being used as a mask + tmp_confers2 := 'AFBRWU'; + END IF; + -- RAISE NOTICE 'Expanded permissions to group % from group %', tmp_confers1, tmp_confers2; + tmp_txt = ''; + FOR counter IN 1 .. length(tmp_confers2) LOOP + IF tmp_confers1 ~* substring(tmp_confers2,counter,1) THEN + tmp_txt := tmp_txt || substring(tmp_confers2,counter,1); + END IF; + END LOOP; + tmp_confers2 := tmp_txt; + END IF; + FOR counter IN 1 .. length(tmp_confers2) LOOP + IF NOT out_confers ~* substring(tmp_confers2,counter,1) THEN + out_confers := out_confers || substring(tmp_confers2,counter,1); + END IF; + END LOOP; + END LOOP; + IF out_confers ~* 'A' OR (out_confers ~* 'B' AND out_confers ~* 'F' AND out_confers ~* 'R' AND out_confers ~* 'W' AND out_confers ~* 'U') THEN + out_confers := 'A'; + END IF; + IF out_confers != '' THEN + RETURN dbg || out_confers; + END IF; + + -- RAISE NOTICE 'No complex relationships between % and %', in_from, in_to; + + SELECT rt1.confers INTO out_confers, tmp_confers1 FROM relationship r1 JOIN relationship_type rt1 ON ( r1.rt_id = rt1.rt_id ) + LEFT OUTER JOIN relationship r2 ON ( rt1.rt_id = r2.rt_id ) + WHERE r1.from_user = in_from AND r2.from_user = in_to AND r1.from_user != r2.from_user AND r1.to_user = r2.to_user + AND NOT EXISTS( SELECT 1 FROM relationship r3 WHERE r3.from_user = r1.to_user ) + AND usr_is_role(r1.to_user,'Group'); + + IF FOUND THEN + -- dbg := 'H-'; + -- RAISE NOTICE 'Permissions to shared group % ', out_confers; + RETURN dbg || out_confers; + END IF; + + -- RAISE NOTICE 'No common group relationships between % and %', in_from, in_to; + + RETURN ''; +END; +$_$; + + +ALTER FUNCTION public.legacy_get_permissions(integer, integer) OWNER TO postgres; + +-- +-- Name: legacy_privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION legacy_privilege_to_bits(text) RETURNS bit + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + in_priv ALIAS FOR $1; + out_bits BIT(24); +BEGIN + out_bits := 0::BIT(24); + IF in_priv ~* 'A' THEN + out_bits = ~ out_bits; + RETURN out_bits; + END IF; + + -- The CALDAV:read-free-busy privilege MUST be aggregated in the DAV:read privilege. + -- 1 DAV:read + -- 512 CalDAV:read-free-busy + -- 4096 CALDAV:schedule-query-freebusy + IF in_priv ~* 'R' THEN + out_bits := out_bits | 4609::BIT(24); + END IF; + + -- DAV:write => DAV:write MUST contain DAV:bind, DAV:unbind, DAV:write-properties and DAV:write-content + -- 2 DAV:write-properties + -- 4 DAV:write-content + -- 64 DAV:bind + -- 128 DAV:unbind + IF in_priv ~* 'W' THEN + out_bits := out_bits | 198::BIT(24); + END IF; + + -- 64 DAV:bind + IF in_priv ~* 'B' THEN + out_bits := out_bits | 64::BIT(24); + END IF; + + -- 128 DAV:unbind + IF in_priv ~* 'U' THEN + out_bits := out_bits | 128::BIT(24); + END IF; + + -- 512 CalDAV:read-free-busy + -- 4096 CALDAV:schedule-query-freebusy + IF in_priv ~* 'F' THEN + out_bits := out_bits | 4608::BIT(24); + END IF; + + RETURN out_bits; +END +$_$; + + +ALTER FUNCTION public.legacy_privilege_to_bits(text) OWNER TO postgres; + +-- +-- Name: max_roles(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION max_roles() RETURNS integer + LANGUAGE sql + AS $$SELECT max(role_no) FROM roles$$; + + +ALTER FUNCTION public.max_roles() OWNER TO postgres; + +-- +-- Name: max_session(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION max_session() RETURNS integer + LANGUAGE sql + AS $$SELECT max(session_id) FROM session$$; + + +ALTER FUNCTION public.max_session() OWNER TO postgres; + +-- +-- Name: max_usr(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION max_usr() RETURNS integer + LANGUAGE sql + AS $$SELECT max(user_no) FROM usr$$; + + +ALTER FUNCTION public.max_usr() OWNER TO postgres; + +-- +-- Name: monthly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION monthly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + after ALIAS FOR $1; + rrule ALIAS FOR $2; + valid_date TIMESTAMP WITH TIME ZONE; + curse REFCURSOR; + setpos INT; + i INT; +BEGIN + + /** + * Need to investigate whether it is legal to set both of these, and whether + * we are correct to UNION the results, or whether we should INTERSECT them. + * So at this point, we refer to the specification, which grants us this + * wonderfully enlightening vision: + * + * If multiple BYxxx rule parts are specified, then after evaluating the + * specified FREQ and INTERVAL rule parts, the BYxxx rule parts are + * applied to the current set of evaluated occurrences in the following + * order: BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, + * BYMINUTE, BYSECOND and BYSETPOS; then COUNT and UNTIL are evaluated. + * + * My guess is that this means 'INTERSECT' + */ + IF rrule.byday IS NOT NULL AND rrule.bymonthday IS NOT NULL THEN + OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r + INTERSECT SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r + ORDER BY 1; + ELSIF rrule.bymonthday IS NOT NULL THEN + OPEN curse SCROLL FOR SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r ORDER BY 1; + ELSE + OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r ORDER BY 1; + END IF; + + RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d; + +END; +$_$; + + +ALTER FUNCTION public.monthly_set(timestamp with time zone, rrule_parts) OWNER TO postgres; + +-- +-- Name: new_db_revision(integer, integer, integer, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION new_db_revision(integer, integer, integer, text) RETURNS void + LANGUAGE plpgsql + AS $_$ + DECLARE + major ALIAS FOR $1; + minor ALIAS FOR $2; + patch ALIAS FOR $3; + blurb ALIAS FOR $4; + new_id INT; + BEGIN + SELECT MAX(schema_id) + 1 INTO new_id FROM awl_db_revision; + IF NOT FOUND OR new_id IS NULL THEN + new_id := 1; + END IF; + INSERT INTO awl_db_revision (schema_id, schema_major, schema_minor, schema_patch, schema_name) + VALUES( new_id, major, minor, patch, blurb ); + RETURN; + END; +$_$; + + +ALTER FUNCTION public.new_db_revision(integer, integer, integer, text) OWNER TO postgres; + +-- +-- Name: new_sync_token(bigint, bigint); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION new_sync_token(bigint, bigint) RETURNS bigint + LANGUAGE plpgsql STRICT + AS $_$ +DECLARE + in_old_sync_token ALIAS FOR $1; + in_collection_id ALIAS FOR $2; + tmp_int INT8; + new_token sync_tokens.sync_token%TYPE; + old_modification_time sync_tokens.modification_time%TYPE; +BEGIN + IF in_old_sync_token > 0 THEN + SELECT modification_time INTO old_modification_time FROM sync_tokens + WHERE sync_token = in_old_sync_token AND collection_id = in_collection_id; + IF NOT FOUND THEN + -- They are in an inconsistent state: we return NULL so they can re-start the process + RETURN NULL; + END IF; + END IF; + + -- Find the most recent sync_token + SELECT sync_token, modification_time INTO new_token, old_modification_time FROM sync_tokens + WHERE collection_id = in_collection_id ORDER BY modification_time DESC LIMIT 1; + IF FOUND THEN + SELECT 1 INTO tmp_int FROM sync_changes WHERE collection_id = in_collection_id AND sync_time > old_modification_time LIMIT 1; + IF NOT FOUND THEN + -- Return the latest sync_token we have for this collection, since there are no changes. + RETURN new_token; + END IF; + END IF; + + -- Looks like we need a new sync_token for this collection... + SELECT nextval('sync_tokens_sync_token_seq') INTO new_token; + INSERT INTO sync_tokens(collection_id, sync_token) VALUES( in_collection_id, new_token ); + + -- Having created our new token we do some clean-up of old tokens + SELECT modification_time, sync_token INTO old_modification_time, tmp_int FROM sync_tokens + WHERE collection_id = in_collection_id AND modification_time < (current_timestamp - '7 days'::interval) + ORDER BY collection_id, modification_time DESC; + DELETE FROM sync_changes WHERE collection_id = in_collection_id AND sync_time < old_modification_time; + DELETE FROM sync_tokens WHERE collection_id = in_collection_id AND sync_token < tmp_int; + + -- Returning the new token + RETURN new_token; +END +$_$; + + +ALTER FUNCTION public.new_sync_token(bigint, bigint) OWNER TO postgres; + +-- +-- Name: p_has_proxy_access_to(bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION p_has_proxy_access_to(bigint, integer) RETURNS SETOF bigint + LANGUAGE sql STABLE STRICT + AS $_$ + SELECT by_principal + FROM ( + SELECT by_principal FROM grants + WHERE to_principal IN (SELECT $1 UNION SELECT expand_memberships($1,$2)) + AND (privileges & 5::BIT(24)) != 0::BIT(24) + AND by_collection IS NULL + AND by_principal != $1 + UNION + SELECT principal_id AS by_principal FROM principal + WHERE (default_privileges & 5::BIT(24)) != 0::BIT(24) + AND principal_id != $1 + ) subquery; +$_$; + + +ALTER FUNCTION public.p_has_proxy_access_to(bigint, integer) OWNER TO postgres; + +-- +-- Name: parse_rrule_parts(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION parse_rrule_parts(timestamp with time zone, text) RETURNS rrule_parts + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + basedate ALIAS FOR $1; + repeatrule ALIAS FOR $2; + result rrule_parts%ROWTYPE; + tempstr TEXT; +BEGIN + result.base := basedate; + result.until := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)'); + result.freq := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)'); + result.count := substring(repeatrule from 'COUNT=([0-9]+)(;|$)'); + result.interval := COALESCE(substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)')::int, 1); + result.wkst := substring(repeatrule from 'WKST=(MO|TU|WE|TH|FR|SA|SU)(;|$)'); + + result.byday := string_to_array( substring(repeatrule from 'BYDAY=(([+-]?[0-9]{0,2}(MO|TU|WE|TH|FR|SA|SU),?)+)(;|$)'), ','); + + result.byyearday := string_to_array(substring(repeatrule from 'BYYEARDAY=([0-9,+-]+)(;|$)'), ','); + result.byweekno := string_to_array(substring(repeatrule from 'BYWEEKNO=([0-9,+-]+)(;|$)'), ','); + result.bymonthday := string_to_array(substring(repeatrule from 'BYMONTHDAY=([0-9,+-]+)(;|$)'), ','); + result.bymonth := string_to_array(substring(repeatrule from 'BYMONTH=(([+-]?[0-1]?[0-9],?)+)(;|$)'), ','); + result.bysetpos := string_to_array(substring(repeatrule from 'BYSETPOS=(([+-]?[0-9]{1,3},?)+)(;|$)'), ','); + + result.bysecond := string_to_array(substring(repeatrule from 'BYSECOND=([0-9,]+)(;|$)'), ','); + result.byminute := string_to_array(substring(repeatrule from 'BYMINUTE=([0-9,]+)(;|$)'), ','); + result.byhour := string_to_array(substring(repeatrule from 'BYHOUR=([0-9,]+)(;|$)'), ','); + + RETURN result; +END; +$_$; + + +ALTER FUNCTION public.parse_rrule_parts(timestamp with time zone, text) OWNER TO postgres; + +-- +-- Name: path_privs(bigint, text, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION path_privs(bigint, text, integer) RETURNS bit + LANGUAGE plpgsql STABLE STRICT + AS $_$ +DECLARE + in_accessor ALIAS FOR $1; + in_path ALIAS FOR $2; + in_depth ALIAS FOR $3; + + alt1_path TEXT; + alt2_path TEXT; + grantor_collection INT8; + grantor_principal INT8; + collection_path TEXT; + collection_privileges BIT(24); + out_conferred BIT(24); +BEGIN + out_conferred := 0::BIT(24); + + IF in_path ~ '^/?$' THEN + -- RAISE NOTICE 'Collection is root: Collection: %', in_path; + RETURN 1; -- basic read privileges on root directory + END IF; + + -- We need to canonicalise the path, so: + -- If it matches '/' + some characters (+ optional '/') => a principal URL + IF in_path ~ '^/[^/]+/?$' THEN + alt1_path := replace(in_path, '/', ''); + SELECT pprivs(in_accessor,principal_id, in_depth) INTO out_conferred FROM usr JOIN principal USING(user_no) WHERE username = alt1_path; + -- RAISE NOTICE 'Path is Principal: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; + RETURN out_conferred; + END IF; + + -- Otherwise look for the longest segment matching up to the last '/', or if we append one, or if we replace a final '.ics' with one. + alt1_path := in_path; + IF alt1_path ~ E'\\.ics$' THEN + alt1_path := substr(alt1_path, 1, length(alt1_path) - 4) || '/'; + END IF; + alt2_path := regexp_replace( in_path, '[^/]*$', ''); + SELECT collection.collection_id, grantor.principal_id, collection.dav_name, collection.default_privileges + INTO grantor_collection, grantor_principal, collection_path, collection_privileges + FROM collection JOIN principal grantor USING (user_no) + WHERE dav_name = in_path || '/' OR dav_name = alt1_path OR dav_name = alt2_path + ORDER BY LENGTH(collection.dav_name) DESC LIMIT 1; + + -- Self will always need full access to their own collections! + IF grantor_principal = in_accessor THEN + -- RAISE NOTICE 'Principal IS owner: Principal: %, Collection: %', in_accessor, in_path; + RETURN ~ out_conferred; + END IF; + + SELECT bit_or(privileges) INTO out_conferred FROM grants + WHERE by_collection = grantor_collection + AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth))); + + IF out_conferred IS NULL THEN + IF collection_privileges IS NULL THEN + IF grantor_principal IS NULL THEN + alt1_path := regexp_replace( in_path, '/[^/]+/?$', '/'); + SELECT path_privs(in_accessor,alt1_path,in_depth) INTO out_conferred; + -- RAISE NOTICE 'Collection is NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; + ELSE + SELECT pprivs(in_accessor,grantor_principal,in_depth) INTO out_conferred; + -- RAISE NOTICE 'Collection priveleges are NULL: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; + END IF; + ELSE + out_conferred := collection_privileges; + -- RAISE NOTICE 'Default Collection priveleges apply: Principal: %, Collection: %, Permissions: %', in_accessor, in_path, out_conferred; + END IF; + END IF; + + RETURN out_conferred; +END; +$_$; + + +ALTER FUNCTION public.path_privs(bigint, text, integer) OWNER TO postgres; + +-- +-- Name: pprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION pprivs(bigint, bigint, integer) RETURNS bit + LANGUAGE plpgsql STABLE STRICT + AS $_$ +DECLARE + in_accessor ALIAS FOR $1; + in_grantor ALIAS FOR $2; + in_depth ALIAS FOR $3; + out_conferred BIT(24); +BEGIN + out_conferred := 0::BIT(24); + -- Self can always have full access + IF in_grantor = in_accessor THEN + RETURN ~ out_conferred; + END IF; + + SELECT bit_or(subquery.privileges) INTO out_conferred FROM + ( + SELECT privileges FROM grants WHERE by_principal=in_grantor AND by_collection IS NULL + AND (to_principal=in_accessor OR to_principal IN (SELECT expand_memberships(in_accessor,in_depth))) + UNION + SELECT bit_or(sq2.privileges) FROM + ( + SELECT 32::BIT(24) AS privileges FROM expand_memberships(in_accessor,in_depth) WHERE expand_memberships = in_grantor + UNION + SELECT default_privileges AS privileges FROM principal WHERE principal_id = in_grantor + ) AS sq2 + ) AS subquery ; + + IF out_conferred IS NULL THEN + SELECT default_privileges INTO out_conferred FROM principal WHERE principal_id = in_grantor; + END IF; + + RETURN out_conferred; +END; +$_$; + + +ALTER FUNCTION public.pprivs(bigint, bigint, integer) OWNER TO postgres; + +-- +-- Name: principal_modified(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION principal_modified() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE +BEGIN + -- in case we trigger on other events in future + IF TG_OP = 'UPDATE' THEN + IF NEW.type_id != OLD.type_id THEN + UPDATE grants + SET is_group = (NEW.type_id = 3) + WHERE grants.to_principal = NEW.principal_id; + END IF; + END IF; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.principal_modified() OWNER TO postgres; + +-- +-- Name: privilege_to_bits(text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION privilege_to_bits(text) RETURNS bit + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + raw_priv ALIAS FOR $1; + in_priv TEXT; +BEGIN + in_priv := trim(lower(regexp_replace(raw_priv, '^.*:', ''))); + IF in_priv = 'all' THEN + RETURN ~ 0::BIT(24); + END IF; + + RETURN (CASE + WHEN in_priv = 'read' THEN 4609 -- 1 + 512 + 4096 + WHEN in_priv = 'write' THEN 198 -- 2 + 4 + 64 + 128 + WHEN in_priv = 'write-properties' THEN 2 + WHEN in_priv = 'write-content' THEN 4 + WHEN in_priv = 'unlock' THEN 8 + WHEN in_priv = 'read-acl' THEN 16 + WHEN in_priv = 'read-current-user-privilege-set' THEN 32 + WHEN in_priv = 'bind' THEN 64 + WHEN in_priv = 'unbind' THEN 128 + WHEN in_priv = 'write-acl' THEN 256 + WHEN in_priv = 'read-free-busy' THEN 4608 -- 512 + 4096 + WHEN in_priv = 'schedule-deliver' THEN 7168 -- 1024 + 2048 + 4096 + WHEN in_priv = 'schedule-deliver-invite' THEN 1024 + WHEN in_priv = 'schedule-deliver-reply' THEN 2048 + WHEN in_priv = 'schedule-query-freebusy' THEN 4096 + WHEN in_priv = 'schedule-send' THEN 57344 -- 8192 + 16384 + 32768 + WHEN in_priv = 'schedule-send-invite' THEN 8192 + WHEN in_priv = 'schedule-send-reply' THEN 16384 + WHEN in_priv = 'schedule-send-freebusy' THEN 32768 + ELSE 0 END)::BIT(24); +END +$_$; + + +ALTER FUNCTION public.privilege_to_bits(text) OWNER TO postgres; + +-- +-- Name: privilege_to_bits(text[]); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION privilege_to_bits(text[]) RETURNS bit + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + raw_privs ALIAS FOR $1; + in_priv TEXT; + out_bits BIT(24); + i INT; + all_privs BIT(24); + start INT; + finish INT; +BEGIN + out_bits := 0::BIT(24); + all_privs := ~ out_bits; + SELECT array_lower(raw_privs,1) INTO start; + SELECT array_upper(raw_privs,1) INTO finish; + FOR i IN start .. finish LOOP + SELECT out_bits | privilege_to_bits(raw_privs[i]) INTO out_bits; + IF out_bits = 65535::BIT(24) THEN + RETURN all_privs; + END IF; + END LOOP; + RETURN out_bits; +END +$_$; + + +ALTER FUNCTION public.privilege_to_bits(text[]) OWNER TO postgres; + +-- +-- Name: privileges_list(bit); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION privileges_list(bit) RETURNS text + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + in_privileges ALIAS FOR $1; + privileges TEXT[]; + plist TEXT; + start INT; + finish INT; + i INT; +BEGIN + plist := ''; + + privileges := bits_to_privilege(in_privileges); + SELECT array_lower(privileges,1) INTO start; + IF start IS NOT NULL THEN + SELECT array_upper(privileges,1) INTO finish; + FOR i IN start .. finish LOOP + plist := plist + || CASE WHEN plist = '' THEN '' ELSE ', ' END + || privileges[i]; + END LOOP; + END IF; + RETURN plist; +END; +$_$; + + +ALTER FUNCTION public.privileges_list(bit) OWNER TO postgres; + +-- +-- Name: real_path_exists(text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION real_path_exists(text) RETURNS boolean + LANGUAGE plpgsql + AS $_$ +DECLARE + in_path ALIAS FOR $1; + tmp BOOLEAN; +BEGIN + IF in_path = '/' THEN + RETURN TRUE; + END IF; + IF in_path ~ '^/[^/]+/$' THEN + SELECT TRUE INTO tmp FROM usr WHERE username = substring( in_path from 2 for length(in_path) - 2); + IF FOUND THEN + RETURN TRUE; + END IF; + ELSE + IF in_path ~ '^/.*/$' THEN + SELECT TRUE INTO tmp FROM collection WHERE dav_name = in_path; + IF FOUND THEN + RETURN TRUE; + END IF; + END IF; + END IF; + RETURN FALSE; +END; +$_$; + + +ALTER FUNCTION public.real_path_exists(text) OWNER TO postgres; + +-- +-- Name: relationship_list(bigint); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION relationship_list(bigint) RETURNS text + LANGUAGE plpgsql + AS $_$ +DECLARE + user ALIAS FOR $1; + r RECORD; + rlist TEXT; +BEGIN + rlist := ''; + FOR r IN SELECT rt_name, fullname FROM relationship + LEFT JOIN relationship_type USING(rt_id) LEFT JOIN usr tgt ON to_user = tgt.user_no + WHERE from_user = user + LOOP + rlist := rlist + || CASE WHEN rlist = '' THEN '' ELSE ', ' END + || r.rt_name || '(' || r.fullname || ')'; + END LOOP; + RETURN rlist; +END; +$_$; + + +ALTER FUNCTION public.relationship_list(bigint) OWNER TO postgres; + +-- +-- Name: rrule_bysetpos_filter(refcursor, integer[]); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION rrule_bysetpos_filter(refcursor, integer[]) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE + AS $_$ +DECLARE + curse ALIAS FOR $1; + bysetpos ALIAS FOR $2; + valid_date TIMESTAMP WITH TIME ZONE; + i INT; +BEGIN + + IF bysetpos IS NULL THEN + LOOP + FETCH curse INTO valid_date; + EXIT WHEN NOT FOUND; + RETURN NEXT valid_date; + END LOOP; + ELSE + FOR i IN 1..366 LOOP + EXIT WHEN bysetpos[i] IS NULL; + IF bysetpos[i] > 0 THEN + FETCH ABSOLUTE bysetpos[i] FROM curse INTO valid_date; + ELSE + MOVE LAST IN curse; + FETCH RELATIVE (bysetpos[i] + 1) FROM curse INTO valid_date; + END IF; + IF valid_date IS NOT NULL THEN + RETURN NEXT valid_date; + END IF; + END LOOP; + END IF; + CLOSE curse; +END; +$_$; + + +ALTER FUNCTION public.rrule_bysetpos_filter(refcursor, integer[]) OWNER TO postgres; + +-- +-- Name: rrule_event_instances(timestamp with time zone, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION rrule_event_instances(timestamp with time zone, text) RETURNS SETOF rrule_instance + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + basedate ALIAS FOR $1; + repeatrule ALIAS FOR $2; + maxdate TIMESTAMP WITH TIME ZONE; + current TIMESTAMP WITH TIME ZONE; + result rrule_instance%ROWTYPE; +BEGIN + maxdate := current_date + '10 years'::interval; + + result.dtstart := basedate; + result.rrule := repeatrule; + + FOR current IN SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d LOOP + result.instance := current; + RETURN NEXT result; + END LOOP; + +END; +$_$; + + +ALTER FUNCTION public.rrule_event_instances(timestamp with time zone, text) OWNER TO postgres; + +-- +-- Name: rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + basedate ALIAS FOR $1; + repeatrule ALIAS FOR $2; + mindate ALIAS FOR $3; + maxdate ALIAS FOR $4; + max_count ALIAS FOR $5; + loopmax INT; + loopcount INT; + base_day TIMESTAMP WITH TIME ZONE; + current_base TIMESTAMP WITH TIME ZONE; + current TIMESTAMP WITH TIME ZONE; + rrule rrule_parts%ROWTYPE; +BEGIN + loopcount := 0; + + SELECT * INTO rrule FROM parse_rrule_parts( basedate, repeatrule ); + + IF rrule.count IS NOT NULL THEN + loopmax := rrule.count; + ELSE + -- max_count is pretty arbitrary, so we scale it somewhat here depending on the frequency. + IF rrule.freq = 'DAILY' THEN + loopmax := max_count * 20; + ELSIF rrule.freq = 'WEEKLY' THEN + loopmax := max_count * 10; + ELSE + loopmax := max_count; + END IF; + END IF; + + current_base := basedate; + base_day := date_trunc('day',basedate); + WHILE loopcount < loopmax AND current_base <= maxdate LOOP + IF rrule.freq = 'DAILY' THEN + FOR current IN SELECT d FROM daily_set(current_base,rrule) d WHERE d >= base_day LOOP +-- IF test_byday_rule(current,rrule.byday) AND test_bymonthday_rule(current,rrule.bymonthday) AND test_bymonth_rule(current,rrule.bymonth) THEN + EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; + IF current >= mindate THEN + RETURN NEXT current; + END IF; + loopcount := loopcount + 1; + EXIT WHEN loopcount >= loopmax; +-- END IF; + END LOOP; + current_base := current_base + (rrule.interval::text || ' days')::interval; + ELSIF rrule.freq = 'WEEKLY' THEN + FOR current IN SELECT w FROM weekly_set(current_base,rrule) w WHERE w >= base_day LOOP + IF test_byyearday_rule(current,rrule.byyearday) + AND test_bymonthday_rule(current,rrule.bymonthday) + AND test_bymonth_rule(current,rrule.bymonth) + THEN + EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; + IF current >= mindate THEN + RETURN NEXT current; + END IF; + loopcount := loopcount + 1; + EXIT WHEN loopcount >= loopmax; + END IF; + END LOOP; + current_base := current_base + (rrule.interval::text || ' weeks')::interval; + ELSIF rrule.freq = 'MONTHLY' THEN + FOR current IN SELECT m FROM monthly_set(current_base,rrule) m WHERE m >= base_day LOOP +-- IF /* test_byyearday_rule(current,rrule.byyearday) +-- AND */ test_bymonth_rule(current,rrule.bymonth) +-- THEN + EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; + IF current >= mindate THEN + RETURN NEXT current; + END IF; + loopcount := loopcount + 1; + EXIT WHEN loopcount >= loopmax; +-- END IF; + END LOOP; + current_base := current_base + (rrule.interval::text || ' months')::interval; + ELSIF rrule.freq = 'YEARLY' THEN + FOR current IN SELECT y FROM yearly_set(current_base,rrule) y WHERE y >= base_day LOOP + EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; + IF current >= mindate THEN + RETURN NEXT current; + END IF; + loopcount := loopcount + 1; + EXIT WHEN loopcount >= loopmax; + END LOOP; + current_base := current_base + (rrule.interval::text || ' years')::interval; + ELSE + RAISE NOTICE 'A frequency of "%" is not handled', rrule.freq; + RETURN; + END IF; + EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; + END LOOP; + -- RETURN QUERY; +END; +$_$; + + +ALTER FUNCTION public.rrule_event_instances_range(timestamp with time zone, text, timestamp with time zone, timestamp with time zone, integer) OWNER TO postgres; + +-- +-- Name: rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone) RETURNS boolean + LANGUAGE plpgsql IMMUTABLE + AS $_$ +DECLARE + dtstart ALIAS FOR $1; + dtend ALIAS FOR $2; + repeatrule ALIAS FOR $3; + in_mindate ALIAS FOR $4; + in_maxdate ALIAS FOR $5; + base_date TIMESTAMP WITH TIME ZONE; + mindate TIMESTAMP WITH TIME ZONE; + maxdate TIMESTAMP WITH TIME ZONE; +BEGIN + + IF dtstart IS NULL THEN + RETURN NULL; + END IF; + IF dtend IS NULL THEN + base_date := dtstart; + ELSE + base_date := dtend; + END IF; + + IF in_mindate IS NULL THEN + mindate := current_date - '10 years'::interval; + ELSE + mindate := in_mindate; + END IF; + + IF in_maxdate IS NULL THEN + maxdate := current_date + '10 years'::interval; + ELSE + -- If we add the duration onto the event, then an overlap occurs if dtend <= increased end of range. + maxdate := in_maxdate + (base_date - dtstart); + END IF; + + IF repeatrule IS NULL THEN + RETURN (dtstart <= maxdate AND base_date >= mindate); + END IF; + + SELECT d INTO mindate FROM rrule_event_instances_range( base_date, repeatrule, mindate, maxdate, 60 ) d LIMIT 1; + RETURN FOUND; + +END; +$_$; + + +ALTER FUNCTION public.rrule_event_overlaps(timestamp with time zone, timestamp with time zone, text, timestamp with time zone, timestamp with time zone) OWNER TO postgres; + +-- +-- Name: rrule_month_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION rrule_month_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE + AS $_$ +DECLARE + in_time ALIAS FOR $1; + byday ALIAS FOR $2; + dayrule TEXT; + i INT; + dow INT; + index INT; + first_dow INT; + each_day TIMESTAMP WITH TIME ZONE; + this_month INT; + results TIMESTAMP WITH TIME ZONE[]; +BEGIN + + IF byday IS NULL THEN + -- We still return the single date as a SET + RETURN NEXT in_time; + RETURN; + END IF; + + i := 1; + dayrule := byday[i]; + WHILE dayrule IS NOT NULL LOOP + dow := position(substring( dayrule from '..$') in 'SUMOTUWETHFRSA') / 2; + each_day := date_trunc( 'month', in_time ) + (in_time::time)::interval; + this_month := date_part( 'month', in_time ); + first_dow := date_part( 'dow', each_day ); + + -- Coerce each_day to be the first 'dow' of the month + each_day := each_day - ( first_dow::text || 'days')::interval + + ( dow::text || 'days')::interval + + CASE WHEN dow < first_dow THEN '1 week'::interval ELSE '0s'::interval END; + + -- RAISE NOTICE 'From "%", for % finding dates. dow=%, this_month=%, first_dow=%', each_day, dayrule, dow, this_month, first_dow; + IF length(dayrule) > 2 THEN + index := (substring(dayrule from '^[0-9-]+'))::int; + + IF index = 0 THEN + RAISE NOTICE 'Ignored invalid BYDAY rule part "%".', bydayrule; + ELSIF index > 0 THEN + -- The simplest case, such as 2MO for the second monday + each_day := each_day + ((index - 1)::text || ' weeks')::interval; + ELSE + each_day := each_day + '5 weeks'::interval; + WHILE date_part('month', each_day) != this_month LOOP + each_day := each_day - '1 week'::interval; + END LOOP; + -- Note that since index is negative, (-2 + 1) == -1, for example + index := index + 1; + IF index < 0 THEN + each_day := each_day + (index::text || ' weeks')::interval ; + END IF; + END IF; + + -- Sometimes (e.g. 5TU or -5WE) there might be no such date in some months + IF date_part('month', each_day) = this_month THEN + results[date_part('day',each_day)] := each_day; + -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule; + END IF; + + ELSE + -- Return all such days that are within the given month + WHILE date_part('month', each_day) = this_month LOOP + results[date_part('day',each_day)] := each_day; + each_day := each_day + '1 week'::interval; + -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule; + END LOOP; + END IF; + + i := i + 1; + dayrule := byday[i]; + END LOOP; + + FOR i IN 1..31 LOOP + IF results[i] IS NOT NULL THEN + RETURN NEXT results[i]; + END IF; + END LOOP; + + RETURN; + +END; +$_$; + + +ALTER FUNCTION public.rrule_month_byday_set(timestamp with time zone, text[]) OWNER TO postgres; + +-- +-- Name: rrule_month_bymonthday_set(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION rrule_month_bymonthday_set(timestamp with time zone, integer[]) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + in_time ALIAS FOR $1; + bymonthday ALIAS FOR $2; + month_start TIMESTAMP WITH TIME ZONE; + daysinmonth INT; + i INT; +BEGIN + + month_start := date_trunc( 'month', in_time ) + (in_time::time)::interval; + daysinmonth := date_part( 'days', (month_start + interval '1 month') - interval '1 day' ); + + FOR i IN 1..31 LOOP + EXIT WHEN bymonthday[i] IS NULL; + + CONTINUE WHEN bymonthday[i] > daysinmonth; + CONTINUE WHEN bymonthday[i] < (-1 * daysinmonth); + + IF bymonthday[i] > 0 THEN + RETURN NEXT month_start + ((bymonthday[i] - 1)::text || 'days')::interval; + ELSIF bymonthday[i] < 0 THEN + RETURN NEXT month_start + ((daysinmonth + bymonthday[i])::text || 'days')::interval; + ELSE + RAISE NOTICE 'Ignored invalid BYMONTHDAY part "%".', bymonthday[i]; + END IF; + END LOOP; + + RETURN; + +END; +$_$; + + +ALTER FUNCTION public.rrule_month_bymonthday_set(timestamp with time zone, integer[]) OWNER TO postgres; + +-- +-- Name: rrule_week_byday_set(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION rrule_week_byday_set(timestamp with time zone, text[]) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE + AS $_$ +DECLARE + in_time ALIAS FOR $1; + byday ALIAS FOR $2; + dayrule TEXT; + dow INT; + our_day TIMESTAMP WITH TIME ZONE; + i INT; +BEGIN + + IF byday IS NULL THEN + -- We still return the single date as a SET + RETURN NEXT in_time; + RETURN; + END IF; + + our_day := date_trunc( 'week', in_time ) + (in_time::time)::interval; + + i := 1; + dayrule := byday[i]; + WHILE dayrule IS NOT NULL LOOP + dow := position(dayrule in 'SUMOTUWETHFRSA') / 2; + RETURN NEXT our_day + ((dow - 1)::text || 'days')::interval; + i := i + 1; + dayrule := byday[i]; + END LOOP; + + RETURN; + +END; +$_$; + + +ALTER FUNCTION public.rrule_week_byday_set(timestamp with time zone, text[]) OWNER TO postgres; + +-- +-- Name: rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + after ALIAS FOR $1; + rrule ALIAS FOR $2; + current_base TIMESTAMP WITH TIME ZONE; + rr rrule_parts; + i INT; +BEGIN + + IF rrule.bymonth IS NOT NULL THEN + -- Ensure we don't pass BYSETPOS down + rr := rrule; + rr.bysetpos := NULL; + FOR i IN 1..12 LOOP + EXIT WHEN rr.bymonth[i] IS NULL; + current_base := date_trunc( 'year', after ) + ((rr.bymonth[i] - 1)::text || ' months')::interval + (after::time)::interval; + RETURN QUERY SELECT r FROM monthly_set(current_base,rr) r; + END LOOP; + ELSE + -- We don't yet implement byweekno, byblah + RETURN NEXT after; + END IF; + +END; +$_$; + + +ALTER FUNCTION public.rrule_yearly_bymonth_set(timestamp with time zone, rrule_parts) OWNER TO postgres; + +-- +-- Name: set_dav_property(text, integer, text, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION set_dav_property(text, integer, text, text) RETURNS boolean + LANGUAGE plpgsql STRICT + AS $_$ +DECLARE + path ALIAS FOR $1; + change_user ALIAS FOR $2; + key ALIAS FOR $3; + value ALIAS FOR $4; +BEGIN + -- Check that there is either a resource, collection or user at this location. + IF NOT EXISTS( SELECT 1 FROM caldav_data WHERE dav_name = path + UNION SELECT 1 FROM collection WHERE dav_name = path + UNION SELECT 1 FROM dav_principal WHERE dav_name = path + UNION SELECT 1 FROM dav_binding WHERE dav_name = path + ) THEN + RETURN FALSE; + END IF; + PERFORM true FROM property WHERE dav_name = path AND property_name = key; + IF FOUND THEN + UPDATE property SET changed_by=change_user::integer, changed_on=current_timestamp, property_value=value WHERE dav_name = path AND property_name = key; + ELSE + INSERT INTO property ( dav_name, changed_by, changed_on, property_name, property_value ) VALUES( path, change_user::integer, current_timestamp, key, value ); + END IF; + RETURN TRUE; +END; +$_$; + + +ALTER FUNCTION public.set_dav_property(text, integer, text, text) OWNER TO postgres; + +-- +-- Name: sync_dav_id(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION sync_dav_id() RETURNS trigger + LANGUAGE plpgsql + AS $$ + DECLARE + BEGIN + + IF TG_OP = 'DELETE' THEN + -- Just let the ON DELETE CASCADE handle this case + RETURN OLD; + END IF; + + IF NEW.dav_id IS NULL THEN + NEW.dav_id = nextval('dav_id_seq'); + END IF; + + IF TG_OP = 'UPDATE' THEN + IF OLD.dav_id != NEW.dav_id OR OLD.collection_id != NEW.collection_id + OR OLD.user_no != NEW.user_no OR OLD.dav_name != NEW.dav_name THEN + UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no, + collection_id = NEW.collection_id, dav_name = NEW.dav_name + WHERE dav_name = OLD.dav_name OR dav_id = OLD.dav_id; + END IF; + RETURN NEW; + END IF; + + UPDATE calendar_item SET dav_id = NEW.dav_id, user_no = NEW.user_no, + collection_id = NEW.collection_id, dav_name = NEW.dav_name + WHERE dav_name = NEW.dav_name OR dav_id = NEW.dav_id; + + RETURN NEW; + + END +$$; + + +ALTER FUNCTION public.sync_dav_id() OWNER TO postgres; + +-- +-- Name: test_byday_rule(timestamp with time zone, text[]); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION test_byday_rule(timestamp with time zone, text[]) RETURNS boolean + LANGUAGE plpgsql IMMUTABLE + AS $_$ +DECLARE + testme ALIAS FOR $1; + byday ALIAS FOR $2; +BEGIN + -- Note that this doesn't work for MONTHLY/YEARLY BYDAY clauses which might have numbers prepended + -- so don't call it that way... + IF byday IS NOT NULL THEN + RETURN ( substring( to_char( testme, 'DY') for 2 from 1) = ANY (byday) ); + END IF; + RETURN TRUE; +END; +$_$; + + +ALTER FUNCTION public.test_byday_rule(timestamp with time zone, text[]) OWNER TO postgres; + +-- +-- Name: test_bymonth_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION test_bymonth_rule(timestamp with time zone, integer[]) RETURNS boolean + LANGUAGE plpgsql IMMUTABLE + AS $_$ +DECLARE + testme ALIAS FOR $1; + bymonth ALIAS FOR $2; +BEGIN + IF bymonth IS NOT NULL THEN + RETURN ( date_part( 'month', testme) = ANY (bymonth) ); + END IF; + RETURN TRUE; +END; +$_$; + + +ALTER FUNCTION public.test_bymonth_rule(timestamp with time zone, integer[]) OWNER TO postgres; + +-- +-- Name: test_bymonthday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION test_bymonthday_rule(timestamp with time zone, integer[]) RETURNS boolean + LANGUAGE plpgsql IMMUTABLE + AS $_$ +DECLARE + testme ALIAS FOR $1; + bymonthday ALIAS FOR $2; +BEGIN + IF bymonthday IS NOT NULL THEN + RETURN ( date_part( 'day', testme) = ANY (bymonthday) ); + END IF; + RETURN TRUE; +END; +$_$; + + +ALTER FUNCTION public.test_bymonthday_rule(timestamp with time zone, integer[]) OWNER TO postgres; + +-- +-- Name: test_byyearday_rule(timestamp with time zone, integer[]); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION test_byyearday_rule(timestamp with time zone, integer[]) RETURNS boolean + LANGUAGE plpgsql IMMUTABLE + AS $_$ +DECLARE + testme ALIAS FOR $1; + byyearday ALIAS FOR $2; +BEGIN + IF byyearday IS NOT NULL THEN + RETURN ( date_part( 'doy', testme) = ANY (byyearday) ); + END IF; + RETURN TRUE; +END; +$_$; + + +ALTER FUNCTION public.test_byyearday_rule(timestamp with time zone, integer[]) OWNER TO postgres; + +-- +-- Name: to_ical_utc(timestamp with time zone); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION to_ical_utc(timestamp with time zone) RETURNS text + LANGUAGE sql IMMUTABLE STRICT + AS $_$ + SELECT to_char( $1 at time zone 'UTC', 'YYYYMMDD"T"HH24MISS"Z"' ) +$_$; + + +ALTER FUNCTION public.to_ical_utc(timestamp with time zone) OWNER TO postgres; + +-- +-- Name: uprivs(bigint, bigint, integer); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION uprivs(bigint, bigint, integer) RETURNS bit + LANGUAGE plpgsql STABLE STRICT + AS $_$ +DECLARE + in_accessor ALIAS FOR $1; + in_grantor ALIAS FOR $2; + in_depth ALIAS FOR $3; + out_conferred BIT(24); +BEGIN + out_conferred := 0::BIT(24); + -- Self can always have full access + IF in_grantor = in_accessor THEN + RETURN ~ out_conferred; + END IF; + + SELECT pprivs( p1.principal_id, p2.principal_id, in_depth ) INTO out_conferred + FROM principal p1, principal p2 + WHERE p1.user_no = in_accessor AND p2.user_no = in_grantor; + + RETURN out_conferred; +END; +$_$; + + +ALTER FUNCTION public.uprivs(bigint, bigint, integer) OWNER TO postgres; + +-- +-- Name: usr_is_role(integer, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION usr_is_role(integer, text) RETURNS boolean + LANGUAGE sql IMMUTABLE STRICT + AS $_$ + SELECT EXISTS( SELECT 1 FROM role_member JOIN roles USING(role_no) WHERE role_member.user_no=$1 AND roles.role_name=$2 ) +$_$; + + +ALTER FUNCTION public.usr_is_role(integer, text) OWNER TO postgres; + +-- +-- Name: usr_modified(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION usr_modified() RETURNS trigger + LANGUAGE plpgsql + AS $$ +DECLARE + oldpath TEXT; + newpath TEXT; +BEGIN + -- in case we trigger on other events in future + IF TG_OP = 'UPDATE' THEN + IF NEW.username != OLD.username THEN + oldpath := '/' || OLD.username || '/'; + newpath := '/' || NEW.username || '/'; + UPDATE collection + SET parent_container = replace( parent_container, oldpath, newpath), + dav_name = replace( dav_name, oldpath, newpath) + WHERE substring(dav_name from 1 for char_length(oldpath)) = oldpath; + END IF; + END IF; + RETURN NEW; +END; +$$; + + +ALTER FUNCTION public.usr_modified() OWNER TO postgres; + +-- +-- Name: weekly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION weekly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + after ALIAS FOR $1; + rrule ALIAS FOR $2; + valid_date TIMESTAMP WITH TIME ZONE; + curse REFCURSOR; + weekno INT; + i INT; +BEGIN + + IF rrule.byweekno IS NOT NULL THEN + weekno := date_part('week',after); + IF NOT weekno = ANY ( rrule.byweekno ) THEN + RETURN; + END IF; + END IF; + + OPEN curse SCROLL FOR SELECT r FROM rrule_week_byday_set(after, rrule.byday ) r; + RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d; + +END; +$_$; + + +ALTER FUNCTION public.weekly_set(timestamp with time zone, rrule_parts) OWNER TO postgres; + +-- +-- Name: write_sync_change(bigint, integer, text); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION write_sync_change(bigint, integer, text) RETURNS boolean + LANGUAGE plpgsql STRICT + AS $_$ +DECLARE + in_collection_id ALIAS FOR $1; + in_status ALIAS FOR $2; + in_dav_name ALIAS FOR $3; + tmp_int INT8; +BEGIN + SELECT 1 INTO tmp_int FROM sync_tokens + WHERE collection_id = in_collection_id + LIMIT 1; + IF NOT FOUND THEN + RETURN FALSE; + END IF; + SELECT dav_id INTO tmp_int FROM caldav_data WHERE dav_name = in_dav_name; + INSERT INTO sync_changes ( collection_id, sync_status, dav_id, dav_name) + VALUES( in_collection_id, in_status, tmp_int, in_dav_name); + RETURN TRUE; +END +$_$; + + +ALTER FUNCTION public.write_sync_change(bigint, integer, text) OWNER TO postgres; + +-- +-- Name: yearly_set(timestamp with time zone, rrule_parts); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION yearly_set(timestamp with time zone, rrule_parts) RETURNS SETOF timestamp with time zone + LANGUAGE plpgsql IMMUTABLE STRICT + AS $_$ +DECLARE + after ALIAS FOR $1; + rrule ALIAS FOR $2; + current_base TIMESTAMP WITH TIME ZONE; + curse REFCURSOR; + curser REFCURSOR; + i INT; +BEGIN + + IF rrule.bymonth IS NOT NULL THEN + OPEN curse SCROLL FOR SELECT r FROM rrule_yearly_bymonth_set(after, rrule ) r; + FOR current_base IN SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d LOOP + current_base := date_trunc( 'day', current_base ) + (after::time)::interval; + RETURN NEXT current_base; + END LOOP; + ELSE + -- We don't yet implement byweekno, byblah + RETURN NEXT after; + END IF; +END; +$_$; + + +ALTER FUNCTION public.yearly_set(timestamp with time zone, rrule_parts) OWNER TO postgres; + +SET default_tablespace = ''; + +SET default_with_oids = false; + +-- +-- Name: access_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE access_ticket ( + ticket_id text NOT NULL, + dav_owner_id bigint NOT NULL, + privileges bit(24), + target_collection_id bigint NOT NULL, + target_resource_id bigint, + expires timestamp without time zone +); + + +ALTER TABLE public.access_ticket OWNER TO postgres; + +-- +-- Name: addressbook_address_adr; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE addressbook_address_adr ( + dav_id bigint NOT NULL, + type text, + box_no text, + unit_no text, + street_address text, + locality text, + region text, + postcode text, + country text, + property text +); + + +ALTER TABLE public.addressbook_address_adr OWNER TO postgres; + +-- +-- Name: addressbook_address_email; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE addressbook_address_email ( + dav_id bigint NOT NULL, + type text, + email text, + property text +); + + +ALTER TABLE public.addressbook_address_email OWNER TO postgres; + +-- +-- Name: addressbook_address_tel; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE addressbook_address_tel ( + dav_id bigint NOT NULL, + type text, + tel text, + property text +); + + +ALTER TABLE public.addressbook_address_tel OWNER TO postgres; + +-- +-- Name: addressbook_resource; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE addressbook_resource ( + dav_id bigint NOT NULL, + version text, + uid text, + nickname text, + fn text, + n text, + note text, + org text, + url text, + fburl text, + caladruri text, + caluri text +); + + +ALTER TABLE public.addressbook_resource OWNER TO postgres; + +-- +-- Name: awl_db_revision; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE awl_db_revision ( + schema_id integer, + schema_major integer, + schema_minor integer, + schema_patch integer, + schema_name text, + applied_on timestamp with time zone DEFAULT now() +); + + +ALTER TABLE public.awl_db_revision OWNER TO postgres; + +-- +-- Name: dav_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE dav_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.dav_id_seq OWNER TO postgres; + +-- +-- Name: caldav_data; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE caldav_data ( + user_no integer NOT NULL, + dav_name text NOT NULL, + dav_etag text, + created timestamp with time zone, + modified timestamp with time zone, + caldav_data text, + caldav_type text, + logged_user integer, + dav_id bigint DEFAULT nextval('dav_id_seq'::regclass), + collection_id bigint, + weak_etag text +); + + +ALTER TABLE public.caldav_data OWNER TO postgres; + +-- +-- Name: calendar_alarm; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE calendar_alarm ( + dav_id bigint NOT NULL, + action text, + trigger text, + summary text, + description text, + next_trigger timestamp with time zone, + component text, + trigger_state character(1) DEFAULT 'N'::bpchar +); + + +ALTER TABLE public.calendar_alarm OWNER TO postgres; + +-- +-- Name: calendar_attendee; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE calendar_attendee ( + dav_id bigint NOT NULL, + status text, + partstat text, + cn text, + attendee text NOT NULL, + role text, + rsvp boolean, + property text, + attendee_state text, + weak_etag text +); + + +ALTER TABLE public.calendar_attendee OWNER TO postgres; + +-- +-- Name: calendar_item; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE calendar_item ( + user_no integer NOT NULL, + dav_name text NOT NULL, + dav_etag text, + uid text, + created timestamp without time zone, + last_modified timestamp without time zone, + dtstamp timestamp without time zone, + dtstart timestamp with time zone, + dtend timestamp with time zone, + due timestamp with time zone, + summary text, + location text, + description text, + priority integer, + class text, + transp text, + rrule text, + url text, + percent_complete numeric(7,2), + tz_id text, + status text, + completed timestamp with time zone, + dav_id bigint, + collection_id bigint, + first_instance_start timestamp without time zone, + last_instance_end timestamp without time zone +); + + +ALTER TABLE public.calendar_item OWNER TO postgres; + +-- +-- Name: collection; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE collection ( + user_no integer, + parent_container text, + dav_name text, + dav_etag text, + dav_displayname text, + is_calendar boolean, + created timestamp with time zone, + modified timestamp with time zone, + public_events_only boolean DEFAULT false NOT NULL, + publicly_readable boolean DEFAULT false NOT NULL, + collection_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL, + default_privileges bit(24), + is_addressbook boolean DEFAULT false, + resourcetypes text DEFAULT ''::text, + schedule_transp text DEFAULT 'opaque'::text, + timezone text, + description text DEFAULT ''::text +); + + +ALTER TABLE public.collection OWNER TO postgres; + +-- +-- Name: dav_binding; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE dav_binding ( + bind_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL, + bound_source_id bigint, + access_ticket_id text, + dav_owner_id bigint NOT NULL, + parent_container text NOT NULL, + dav_name text NOT NULL, + dav_displayname text, + external_url text, + type text, + CONSTRAINT dav_name_does_not_exist CHECK ((NOT real_path_exists(dav_name))) +); + + +ALTER TABLE public.dav_binding OWNER TO postgres; + +-- +-- Name: principal; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE principal ( + principal_id bigint DEFAULT nextval('dav_id_seq'::regclass) NOT NULL, + type_id bigint NOT NULL, + user_no bigint, + displayname text, + default_privileges bit(24) +); + + +ALTER TABLE public.principal OWNER TO postgres; + +-- +-- Name: usr; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE usr ( + user_no integer NOT NULL, + active boolean DEFAULT true, + email_ok timestamp with time zone, + joined timestamp with time zone DEFAULT now(), + updated timestamp with time zone, + last_used timestamp with time zone, + username text NOT NULL, + password text, + fullname text, + email text, + config_data text, + date_format_type text DEFAULT 'E'::text, + locale text +); + + +ALTER TABLE public.usr OWNER TO postgres; + +-- +-- Name: dav_principal; Type: VIEW; Schema: public; Owner: postgres +-- + +CREATE VIEW dav_principal AS + SELECT principal.user_no, usr.active AS user_active, usr.joined AS created, usr.updated AS modified, usr.username, usr.password, usr.fullname, usr.email, usr.email_ok, usr.date_format_type, usr.locale, principal.principal_id, principal.type_id, principal.displayname, principal.default_privileges, true AS is_principal, false AS is_calendar, principal.principal_id AS collection_id, false AS is_addressbook, (('/'::text || usr.username) || '/'::text) AS dav_name, ''::text AS resourcetypes FROM (usr JOIN principal USING (user_no)); + + +ALTER TABLE public.dav_principal OWNER TO postgres; + +-- +-- Name: freebusy_ticket; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE freebusy_ticket ( + ticket_id text NOT NULL, + user_no integer NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE public.freebusy_ticket OWNER TO postgres; + +-- +-- Name: grants; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE grants ( + by_principal bigint, + by_collection bigint, + to_principal bigint, + privileges bit(24), + is_group boolean +); + + +ALTER TABLE public.grants OWNER TO postgres; + +-- +-- Name: group_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE group_member ( + group_id bigint, + member_id bigint +); + + +ALTER TABLE public.group_member OWNER TO postgres; + +-- +-- Name: locks; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE locks ( + dav_name text, + opaquelocktoken text NOT NULL, + type text, + scope text, + depth integer, + owner text, + timeout interval, + start timestamp without time zone DEFAULT now() +); + + +ALTER TABLE public.locks OWNER TO postgres; + +-- +-- Name: principal_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE principal_type ( + principal_type_id integer NOT NULL, + principal_type_desc text +); + + +ALTER TABLE public.principal_type OWNER TO postgres; + +-- +-- Name: principal_type_principal_type_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE principal_type_principal_type_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.principal_type_principal_type_id_seq OWNER TO postgres; + +-- +-- Name: principal_type_principal_type_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE principal_type_principal_type_id_seq OWNED BY principal_type.principal_type_id; + + +-- +-- Name: property; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE property ( + dav_name text NOT NULL, + property_name text NOT NULL, + property_value text, + changed_on timestamp without time zone DEFAULT now(), + changed_by integer +); + + +ALTER TABLE public.property OWNER TO postgres; + +-- +-- Name: relationship; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE relationship ( + from_user integer NOT NULL, + to_user integer NOT NULL, + rt_id integer NOT NULL, + confers bit(24) DEFAULT privilege_to_bits(ARRAY['DAV::read'::text, 'DAV::write'::text]) +); + + +ALTER TABLE public.relationship OWNER TO postgres; + +-- +-- Name: relationship_type; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE relationship_type ( + rt_id integer NOT NULL, + rt_name text, + rt_togroup boolean, + confers text DEFAULT 'RW'::text, + rt_fromgroup boolean, + bit_confers bit(24) DEFAULT privilege_to_bits(ARRAY['DAV::read'::text, 'DAV::write'::text]) +); + + +ALTER TABLE public.relationship_type OWNER TO postgres; + +-- +-- Name: relationship_type_rt_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE relationship_type_rt_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.relationship_type_rt_id_seq OWNER TO postgres; + +-- +-- Name: relationship_type_rt_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE relationship_type_rt_id_seq OWNED BY relationship_type.rt_id; + + +-- +-- Name: role_member; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE role_member ( + role_no integer, + user_no integer +); + + +ALTER TABLE public.role_member OWNER TO postgres; + +-- +-- Name: roles; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE roles ( + role_no integer NOT NULL, + role_name text +); + + +ALTER TABLE public.roles OWNER TO postgres; + +-- +-- Name: roles_role_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE roles_role_no_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.roles_role_no_seq OWNER TO postgres; + +-- +-- Name: roles_role_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE roles_role_no_seq OWNED BY roles.role_no; + + +-- +-- Name: session; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE session ( + session_id integer NOT NULL, + user_no integer, + session_start timestamp with time zone DEFAULT now(), + session_end timestamp with time zone DEFAULT now(), + session_key text, + session_config text +); + + +ALTER TABLE public.session OWNER TO postgres; + +-- +-- Name: session_session_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE session_session_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.session_session_id_seq OWNER TO postgres; + +-- +-- Name: session_session_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE session_session_id_seq OWNED BY session.session_id; + + +-- +-- Name: supported_locales; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE supported_locales ( + locale text NOT NULL, + locale_name_en text, + locale_name_locale text +); + + +ALTER TABLE public.supported_locales OWNER TO postgres; + +-- +-- Name: sync_changes; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE sync_changes ( + sync_time timestamp with time zone DEFAULT now(), + collection_id bigint, + sync_status integer, + dav_id bigint, + dav_name text +); + + +ALTER TABLE public.sync_changes OWNER TO postgres; + +-- +-- Name: sync_tokens; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE sync_tokens ( + sync_token integer NOT NULL, + collection_id bigint, + modification_time timestamp with time zone DEFAULT now() +); + + +ALTER TABLE public.sync_tokens OWNER TO postgres; + +-- +-- Name: sync_tokens_sync_token_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE sync_tokens_sync_token_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.sync_tokens_sync_token_seq OWNER TO postgres; + +-- +-- Name: sync_tokens_sync_token_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE sync_tokens_sync_token_seq OWNED BY sync_tokens.sync_token; + + +-- +-- Name: timezones; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE timezones ( + our_tzno integer NOT NULL, + tzid text NOT NULL, + olson_name text, + active boolean, + last_modified timestamp without time zone DEFAULT now(), + etag text, + vtimezone text +); + + +ALTER TABLE public.timezones OWNER TO postgres; + +-- +-- Name: timezones_our_tzno_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE timezones_our_tzno_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.timezones_our_tzno_seq OWNER TO postgres; + +-- +-- Name: timezones_our_tzno_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE timezones_our_tzno_seq OWNED BY timezones.our_tzno; + + +-- +-- Name: tmp_password; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE tmp_password ( + user_no integer, + password text, + valid_until timestamp with time zone DEFAULT (now() + '1 day'::interval) +); + + +ALTER TABLE public.tmp_password OWNER TO postgres; + +-- +-- Name: tz_aliases; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE tz_aliases ( + our_tzno bigint, + tzalias text NOT NULL +); + + +ALTER TABLE public.tz_aliases OWNER TO postgres; + +-- +-- Name: tz_localnames; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE tz_localnames ( + our_tzno bigint, + locale text NOT NULL, + localised_name text NOT NULL, + preferred boolean DEFAULT true +); + + +ALTER TABLE public.tz_localnames OWNER TO postgres; + +-- +-- Name: usr_setting; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE usr_setting ( + user_no integer NOT NULL, + setting_name text NOT NULL, + setting_value text +); + + +ALTER TABLE public.usr_setting OWNER TO postgres; + +-- +-- Name: usr_user_no_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE usr_user_no_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.usr_user_no_seq OWNER TO postgres; + +-- +-- Name: usr_user_no_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE usr_user_no_seq OWNED BY usr.user_no; + + +-- +-- Name: principal_type_id; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY principal_type ALTER COLUMN principal_type_id SET DEFAULT nextval('principal_type_principal_type_id_seq'::regclass); + + +-- +-- Name: rt_id; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY relationship_type ALTER COLUMN rt_id SET DEFAULT nextval('relationship_type_rt_id_seq'::regclass); + + +-- +-- Name: role_no; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY roles ALTER COLUMN role_no SET DEFAULT nextval('roles_role_no_seq'::regclass); + + +-- +-- Name: session_id; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY session ALTER COLUMN session_id SET DEFAULT nextval('session_session_id_seq'::regclass); + + +-- +-- Name: sync_token; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY sync_tokens ALTER COLUMN sync_token SET DEFAULT nextval('sync_tokens_sync_token_seq'::regclass); + + +-- +-- Name: our_tzno; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY timezones ALTER COLUMN our_tzno SET DEFAULT nextval('timezones_our_tzno_seq'::regclass); + + +-- +-- Name: user_no; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY usr ALTER COLUMN user_no SET DEFAULT nextval('usr_user_no_seq'::regclass); + + +-- +-- Name: access_ticket_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY access_ticket + ADD CONSTRAINT access_ticket_pkey PRIMARY KEY (ticket_id); + + +-- +-- Name: addressbook_resource_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY addressbook_resource + ADD CONSTRAINT addressbook_resource_pkey PRIMARY KEY (dav_id); + + +-- +-- Name: caldav_data_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY caldav_data + ADD CONSTRAINT caldav_data_dav_id_key UNIQUE (dav_id); + + +-- +-- Name: caldav_data_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY caldav_data + ADD CONSTRAINT caldav_data_pkey PRIMARY KEY (user_no, dav_name); + + +-- +-- Name: calendar_attendee_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY calendar_attendee + ADD CONSTRAINT calendar_attendee_pkey PRIMARY KEY (dav_id, attendee); + + +-- +-- Name: calendar_item_dav_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY calendar_item + ADD CONSTRAINT calendar_item_dav_id_key UNIQUE (dav_id); + + +-- +-- Name: calendar_item_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY calendar_item + ADD CONSTRAINT calendar_item_pkey PRIMARY KEY (user_no, dav_name); + + +-- +-- Name: collection_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY collection + ADD CONSTRAINT collection_pkey PRIMARY KEY (collection_id); + + +-- +-- Name: collection_user_no_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY collection + ADD CONSTRAINT collection_user_no_dav_name_key UNIQUE (user_no, dav_name); + + +-- +-- Name: dav_binding_dav_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY dav_binding + ADD CONSTRAINT dav_binding_dav_name_key UNIQUE (dav_name); + + +-- +-- Name: dav_binding_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY dav_binding + ADD CONSTRAINT dav_binding_pkey PRIMARY KEY (bind_id); + + +-- +-- Name: freebusy_ticket_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY freebusy_ticket + ADD CONSTRAINT freebusy_ticket_pkey PRIMARY KEY (ticket_id); + + +-- +-- Name: locks_opaquelocktoken_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY locks + ADD CONSTRAINT locks_opaquelocktoken_key UNIQUE (opaquelocktoken); + + +-- +-- Name: principal_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY principal + ADD CONSTRAINT principal_pkey PRIMARY KEY (principal_id); + + +-- +-- Name: principal_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY principal_type + ADD CONSTRAINT principal_type_pkey PRIMARY KEY (principal_type_id); + + +-- +-- Name: property_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY property + ADD CONSTRAINT property_pkey PRIMARY KEY (dav_name, property_name); + + +-- +-- Name: relationship_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY relationship + ADD CONSTRAINT relationship_pkey PRIMARY KEY (from_user, to_user, rt_id); + + +-- +-- Name: relationship_type_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY relationship_type + ADD CONSTRAINT relationship_type_pkey PRIMARY KEY (rt_id); + + +-- +-- Name: roles_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY roles + ADD CONSTRAINT roles_pkey PRIMARY KEY (role_no); + + +-- +-- Name: session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY session + ADD CONSTRAINT session_pkey PRIMARY KEY (session_id); + + +-- +-- Name: supported_locales_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY supported_locales + ADD CONSTRAINT supported_locales_pkey PRIMARY KEY (locale); + + +-- +-- Name: sync_tokens_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY sync_tokens + ADD CONSTRAINT sync_tokens_pkey PRIMARY KEY (sync_token); + + +-- +-- Name: timezones_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY timezones + ADD CONSTRAINT timezones_pkey PRIMARY KEY (our_tzno); + + +-- +-- Name: timezones_tzid_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY timezones + ADD CONSTRAINT timezones_tzid_key UNIQUE (tzid); + + +-- +-- Name: unique_path; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY collection + ADD CONSTRAINT unique_path UNIQUE (dav_name); + + +-- +-- Name: unique_user; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY principal + ADD CONSTRAINT unique_user UNIQUE (user_no); + + +-- +-- Name: usr_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY usr + ADD CONSTRAINT usr_pkey PRIMARY KEY (user_no); + + +-- +-- Name: usr_setting_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY usr_setting + ADD CONSTRAINT usr_setting_pkey PRIMARY KEY (user_no, setting_name); + + +-- +-- Name: caldav_data_collection_id_fkey; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX caldav_data_collection_id_fkey ON caldav_data USING btree (collection_id); + + +-- +-- Name: calendar_item_collection_id_fkey; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX calendar_item_collection_id_fkey ON calendar_item USING btree (collection_id); + + +-- +-- Name: grants_pk1; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE UNIQUE INDEX grants_pk1 ON grants USING btree (by_principal, to_principal); + + +-- +-- Name: grants_pk2; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE UNIQUE INDEX grants_pk2 ON grants USING btree (by_collection, to_principal); + + +-- +-- Name: group_member_pk; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE UNIQUE INDEX group_member_pk ON group_member USING btree (group_id, member_id); + + +-- +-- Name: group_member_sk; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX group_member_sk ON group_member USING btree (member_id); + + +-- +-- Name: locks_dav_name_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX locks_dav_name_idx ON locks USING btree (dav_name); + + +-- +-- Name: properties_dav_name_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX properties_dav_name_idx ON property USING btree (dav_name); + + +-- +-- Name: sync_processing_index; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX sync_processing_index ON sync_changes USING btree (collection_id, dav_id, sync_time); + + +-- +-- Name: usr_sk1_unique_username; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE UNIQUE INDEX usr_sk1_unique_username ON usr USING btree (lower(username)); + + +-- +-- Name: dav_principal_delete; Type: RULE; Schema: public; Owner: postgres +-- + +CREATE RULE dav_principal_delete AS ON DELETE TO dav_principal DO INSTEAD (DELETE FROM usr WHERE (usr.user_no = old.user_no); DELETE FROM principal WHERE (principal.principal_id = old.principal_id); ); + + +-- +-- Name: dav_principal_insert; Type: RULE; Schema: public; Owner: postgres +-- + +CREATE RULE dav_principal_insert AS ON INSERT TO dav_principal DO INSTEAD (INSERT INTO usr (user_no, active, joined, updated, username, password, fullname, email, email_ok, date_format_type, locale) VALUES (COALESCE(new.user_no, nextval('usr_user_no_seq'::regclass)), COALESCE(new.user_active, true), COALESCE(new.created, now()), COALESCE(new.modified, now()), new.username, new.password, COALESCE(new.fullname, new.displayname), new.email, new.email_ok, COALESCE(new.date_format_type, 'E'::text), new.locale); INSERT INTO principal (user_no, principal_id, type_id, displayname, default_privileges) VALUES (COALESCE(new.user_no, currval('usr_user_no_seq'::regclass)), COALESCE(new.principal_id, nextval('dav_id_seq'::regclass)), new.type_id, COALESCE(new.displayname, new.fullname), COALESCE(new.default_privileges, (0)::bit(24))); ); + + +-- +-- Name: dav_principal_update; Type: RULE; Schema: public; Owner: postgres +-- + +CREATE RULE dav_principal_update AS ON UPDATE TO dav_principal DO INSTEAD (UPDATE usr SET user_no = new.user_no, active = new.user_active, updated = now(), username = new.username, password = new.password, fullname = new.fullname, email = new.email, email_ok = new.email_ok, date_format_type = new.date_format_type, locale = new.locale WHERE (usr.user_no = old.user_no); UPDATE principal SET principal_id = new.principal_id, type_id = new.type_id, displayname = new.displayname, default_privileges = new.default_privileges WHERE (principal.principal_id = old.principal_id); ); + + +-- +-- Name: alarm_changed; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER alarm_changed AFTER UPDATE ON calendar_alarm FOR EACH ROW EXECUTE PROCEDURE alarm_changed(); + + +-- +-- Name: caldav_data_modified; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER caldav_data_modified AFTER INSERT OR DELETE OR UPDATE ON caldav_data FOR EACH ROW EXECUTE PROCEDURE caldav_data_modified(); + + +-- +-- Name: caldav_data_sync_dav_id; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data FOR EACH ROW EXECUTE PROCEDURE sync_dav_id(); + + +-- +-- Name: collection_modified; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER collection_modified AFTER UPDATE ON collection FOR EACH ROW EXECUTE PROCEDURE collection_modified(); + + +-- +-- Name: grants_modified; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER grants_modified AFTER INSERT OR UPDATE ON grants FOR EACH ROW EXECUTE PROCEDURE grants_modified(); + + +-- +-- Name: principal_modified; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER principal_modified AFTER UPDATE ON principal FOR EACH ROW EXECUTE PROCEDURE principal_modified(); + + +-- +-- Name: usr_modified; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER usr_modified AFTER UPDATE ON usr FOR EACH ROW EXECUTE PROCEDURE usr_modified(); + + +-- +-- Name: access_ticket_dav_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY access_ticket + ADD CONSTRAINT access_ticket_dav_owner_id_fkey FOREIGN KEY (dav_owner_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: access_ticket_target_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY access_ticket + ADD CONSTRAINT access_ticket_target_collection_id_fkey FOREIGN KEY (target_collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: access_ticket_target_resource_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY access_ticket + ADD CONSTRAINT access_ticket_target_resource_id_fkey FOREIGN KEY (target_resource_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: addressbook_address_adr_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY addressbook_address_adr + ADD CONSTRAINT addressbook_address_adr_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: addressbook_address_email_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY addressbook_address_email + ADD CONSTRAINT addressbook_address_email_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: addressbook_address_tel_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY addressbook_address_tel + ADD CONSTRAINT addressbook_address_tel_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: addressbook_resource_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY addressbook_resource + ADD CONSTRAINT addressbook_resource_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: caldav_data_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY caldav_data + ADD CONSTRAINT caldav_data_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: caldav_data_logged_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY caldav_data + ADD CONSTRAINT caldav_data_logged_user_fkey FOREIGN KEY (logged_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE SET DEFAULT DEFERRABLE; + + +-- +-- Name: caldav_data_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY caldav_data + ADD CONSTRAINT caldav_data_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: caldav_exists; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY calendar_item + ADD CONSTRAINT caldav_exists FOREIGN KEY (user_no, dav_name) REFERENCES caldav_data(user_no, dav_name) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: calendar_alarm_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY calendar_alarm + ADD CONSTRAINT calendar_alarm_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: calendar_attendee_dav_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY calendar_attendee + ADD CONSTRAINT calendar_attendee_dav_id_fkey FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: calendar_item_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY calendar_item + ADD CONSTRAINT calendar_item_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: calendar_item_tz_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY calendar_item + ADD CONSTRAINT calendar_item_tz_id_fkey FOREIGN KEY (tz_id) REFERENCES timezones(tzid); + + +-- +-- Name: calendar_item_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY calendar_item + ADD CONSTRAINT calendar_item_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: collection_timezone_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY collection + ADD CONSTRAINT collection_timezone_fkey FOREIGN KEY (timezone) REFERENCES timezones(tzid) ON UPDATE CASCADE ON DELETE SET NULL; + + +-- +-- Name: collection_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY collection + ADD CONSTRAINT collection_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: dav_binding_access_ticket_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY dav_binding + ADD CONSTRAINT dav_binding_access_ticket_id_fkey FOREIGN KEY (access_ticket_id) REFERENCES access_ticket(ticket_id) ON UPDATE CASCADE ON DELETE SET NULL; + + +-- +-- Name: dav_binding_bound_source_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY dav_binding + ADD CONSTRAINT dav_binding_bound_source_id_fkey FOREIGN KEY (bound_source_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: dav_binding_dav_owner_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY dav_binding + ADD CONSTRAINT dav_binding_dav_owner_id_fkey FOREIGN KEY (dav_owner_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: freebusy_ticket_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY freebusy_ticket + ADD CONSTRAINT freebusy_ticket_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: grants_by_collection_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY grants + ADD CONSTRAINT grants_by_collection_fkey FOREIGN KEY (by_collection) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: grants_by_principal_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY grants + ADD CONSTRAINT grants_by_principal_fkey FOREIGN KEY (by_principal) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: grants_to_principal_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY grants + ADD CONSTRAINT grants_to_principal_fkey FOREIGN KEY (to_principal) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: group_member_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY group_member + ADD CONSTRAINT group_member_group_id_fkey FOREIGN KEY (group_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: group_member_member_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY group_member + ADD CONSTRAINT group_member_member_id_fkey FOREIGN KEY (member_id) REFERENCES principal(principal_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: principal_type_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY principal + ADD CONSTRAINT principal_type_id_fkey FOREIGN KEY (type_id) REFERENCES principal_type(principal_type_id) ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE; + + +-- +-- Name: principal_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY principal + ADD CONSTRAINT principal_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE; + + +-- +-- Name: property_changed_by_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY property + ADD CONSTRAINT property_changed_by_fkey FOREIGN KEY (changed_by) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE SET DEFAULT; + + +-- +-- Name: relationship_from_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY relationship + ADD CONSTRAINT relationship_from_user_fkey FOREIGN KEY (from_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: relationship_rt_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY relationship + ADD CONSTRAINT relationship_rt_id_fkey FOREIGN KEY (rt_id) REFERENCES relationship_type(rt_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: relationship_to_user_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY relationship + ADD CONSTRAINT relationship_to_user_fkey FOREIGN KEY (to_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: role_member_role_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY role_member + ADD CONSTRAINT role_member_role_no_fkey FOREIGN KEY (role_no) REFERENCES roles(role_no); + + +-- +-- Name: role_member_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY role_member + ADD CONSTRAINT role_member_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON DELETE CASCADE; + + +-- +-- Name: session_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY session + ADD CONSTRAINT session_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON DELETE CASCADE; + + +-- +-- Name: sync_changes_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY sync_changes + ADD CONSTRAINT sync_changes_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: sync_tokens_collection_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY sync_tokens + ADD CONSTRAINT sync_tokens_collection_id_fkey FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: tmp_password_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY tmp_password + ADD CONSTRAINT tmp_password_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no); + + +-- +-- Name: tz_aliases_our_tzno_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY tz_aliases + ADD CONSTRAINT tz_aliases_our_tzno_fkey FOREIGN KEY (our_tzno) REFERENCES timezones(our_tzno); + + +-- +-- Name: tz_localnames_our_tzno_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY tz_localnames + ADD CONSTRAINT tz_localnames_our_tzno_fkey FOREIGN KEY (our_tzno) REFERENCES timezones(our_tzno); + + +-- +-- Name: usr_setting_user_no_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY usr_setting + ADD CONSTRAINT usr_setting_user_no_fkey FOREIGN KEY (user_no) REFERENCES usr(user_no) ON DELETE CASCADE; + + +-- +-- Name: public; Type: ACL; Schema: -; Owner: postgres +-- + +REVOKE ALL ON SCHEMA public FROM PUBLIC; +REVOKE ALL ON SCHEMA public FROM postgres; +GRANT ALL ON SCHEMA public TO postgres; +GRANT ALL ON SCHEMA public TO PUBLIC; + + +-- +-- Name: access_ticket; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE access_ticket FROM PUBLIC; +REVOKE ALL ON TABLE access_ticket FROM postgres; +GRANT ALL ON TABLE access_ticket TO postgres; + + +-- +-- Name: addressbook_address_adr; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE addressbook_address_adr FROM PUBLIC; +REVOKE ALL ON TABLE addressbook_address_adr FROM postgres; +GRANT ALL ON TABLE addressbook_address_adr TO postgres; + + +-- +-- Name: addressbook_address_email; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE addressbook_address_email FROM PUBLIC; +REVOKE ALL ON TABLE addressbook_address_email FROM postgres; +GRANT ALL ON TABLE addressbook_address_email TO postgres; + + +-- +-- Name: addressbook_address_tel; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE addressbook_address_tel FROM PUBLIC; +REVOKE ALL ON TABLE addressbook_address_tel FROM postgres; +GRANT ALL ON TABLE addressbook_address_tel TO postgres; + + +-- +-- Name: addressbook_resource; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE addressbook_resource FROM PUBLIC; +REVOKE ALL ON TABLE addressbook_resource FROM postgres; +GRANT ALL ON TABLE addressbook_resource TO postgres; + + +-- +-- Name: awl_db_revision; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE awl_db_revision FROM PUBLIC; +REVOKE ALL ON TABLE awl_db_revision FROM postgres; +GRANT ALL ON TABLE awl_db_revision TO postgres; + + +-- +-- Name: dav_id_seq; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON SEQUENCE dav_id_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE dav_id_seq FROM postgres; +GRANT ALL ON SEQUENCE dav_id_seq TO postgres; + + +-- +-- Name: caldav_data; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE caldav_data FROM PUBLIC; +REVOKE ALL ON TABLE caldav_data FROM postgres; +GRANT ALL ON TABLE caldav_data TO postgres; + + +-- +-- Name: calendar_alarm; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE calendar_alarm FROM PUBLIC; +REVOKE ALL ON TABLE calendar_alarm FROM postgres; +GRANT ALL ON TABLE calendar_alarm TO postgres; + + +-- +-- Name: calendar_attendee; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE calendar_attendee FROM PUBLIC; +REVOKE ALL ON TABLE calendar_attendee FROM postgres; +GRANT ALL ON TABLE calendar_attendee TO postgres; + + +-- +-- Name: calendar_item; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE calendar_item FROM PUBLIC; +REVOKE ALL ON TABLE calendar_item FROM postgres; +GRANT ALL ON TABLE calendar_item TO postgres; + + +-- +-- Name: collection; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE collection FROM PUBLIC; +REVOKE ALL ON TABLE collection FROM postgres; +GRANT ALL ON TABLE collection TO postgres; + + +-- +-- Name: dav_binding; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE dav_binding FROM PUBLIC; +REVOKE ALL ON TABLE dav_binding FROM postgres; +GRANT ALL ON TABLE dav_binding TO postgres; + + +-- +-- Name: principal; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE principal FROM PUBLIC; +REVOKE ALL ON TABLE principal FROM postgres; +GRANT ALL ON TABLE principal TO postgres; + + +-- +-- Name: usr; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE usr FROM PUBLIC; +REVOKE ALL ON TABLE usr FROM postgres; +GRANT ALL ON TABLE usr TO postgres; + + +-- +-- Name: dav_principal; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE dav_principal FROM PUBLIC; +REVOKE ALL ON TABLE dav_principal FROM postgres; +GRANT ALL ON TABLE dav_principal TO postgres; + + +-- +-- Name: freebusy_ticket; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE freebusy_ticket FROM PUBLIC; +REVOKE ALL ON TABLE freebusy_ticket FROM postgres; +GRANT ALL ON TABLE freebusy_ticket TO postgres; + + +-- +-- Name: grants; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE grants FROM PUBLIC; +REVOKE ALL ON TABLE grants FROM postgres; +GRANT ALL ON TABLE grants TO postgres; + + +-- +-- Name: group_member; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE group_member FROM PUBLIC; +REVOKE ALL ON TABLE group_member FROM postgres; +GRANT ALL ON TABLE group_member TO postgres; + + +-- +-- Name: locks; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE locks FROM PUBLIC; +REVOKE ALL ON TABLE locks FROM postgres; +GRANT ALL ON TABLE locks TO postgres; + + +-- +-- Name: principal_type; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE principal_type FROM PUBLIC; +REVOKE ALL ON TABLE principal_type FROM postgres; +GRANT ALL ON TABLE principal_type TO postgres; + + +-- +-- Name: principal_type_principal_type_id_seq; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON SEQUENCE principal_type_principal_type_id_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE principal_type_principal_type_id_seq FROM postgres; +GRANT ALL ON SEQUENCE principal_type_principal_type_id_seq TO postgres; + + +-- +-- Name: property; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE property FROM PUBLIC; +REVOKE ALL ON TABLE property FROM postgres; +GRANT ALL ON TABLE property TO postgres; + + +-- +-- Name: relationship; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE relationship FROM PUBLIC; +REVOKE ALL ON TABLE relationship FROM postgres; +GRANT ALL ON TABLE relationship TO postgres; + + +-- +-- Name: relationship_type; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE relationship_type FROM PUBLIC; +REVOKE ALL ON TABLE relationship_type FROM postgres; +GRANT ALL ON TABLE relationship_type TO postgres; + + +-- +-- Name: relationship_type_rt_id_seq; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON SEQUENCE relationship_type_rt_id_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE relationship_type_rt_id_seq FROM postgres; +GRANT ALL ON SEQUENCE relationship_type_rt_id_seq TO postgres; + + +-- +-- Name: role_member; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE role_member FROM PUBLIC; +REVOKE ALL ON TABLE role_member FROM postgres; +GRANT ALL ON TABLE role_member TO postgres; + + +-- +-- Name: roles; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE roles FROM PUBLIC; +REVOKE ALL ON TABLE roles FROM postgres; +GRANT ALL ON TABLE roles TO postgres; + + +-- +-- Name: roles_role_no_seq; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON SEQUENCE roles_role_no_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE roles_role_no_seq FROM postgres; +GRANT ALL ON SEQUENCE roles_role_no_seq TO postgres; + + +-- +-- Name: session; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE session FROM PUBLIC; +REVOKE ALL ON TABLE session FROM postgres; +GRANT ALL ON TABLE session TO postgres; + + +-- +-- Name: session_session_id_seq; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON SEQUENCE session_session_id_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE session_session_id_seq FROM postgres; +GRANT ALL ON SEQUENCE session_session_id_seq TO postgres; + + +-- +-- Name: supported_locales; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE supported_locales FROM PUBLIC; +REVOKE ALL ON TABLE supported_locales FROM postgres; +GRANT ALL ON TABLE supported_locales TO postgres; + + +-- +-- Name: sync_changes; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE sync_changes FROM PUBLIC; +REVOKE ALL ON TABLE sync_changes FROM postgres; +GRANT ALL ON TABLE sync_changes TO postgres; + + +-- +-- Name: sync_tokens; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE sync_tokens FROM PUBLIC; +REVOKE ALL ON TABLE sync_tokens FROM postgres; +GRANT ALL ON TABLE sync_tokens TO postgres; + + +-- +-- Name: sync_tokens_sync_token_seq; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON SEQUENCE sync_tokens_sync_token_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE sync_tokens_sync_token_seq FROM postgres; +GRANT ALL ON SEQUENCE sync_tokens_sync_token_seq TO postgres; + + +-- +-- Name: timezones; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE timezones FROM PUBLIC; +REVOKE ALL ON TABLE timezones FROM postgres; +GRANT ALL ON TABLE timezones TO postgres; + + +-- +-- Name: timezones_our_tzno_seq; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON SEQUENCE timezones_our_tzno_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE timezones_our_tzno_seq FROM postgres; +GRANT ALL ON SEQUENCE timezones_our_tzno_seq TO postgres; + + +-- +-- Name: tmp_password; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE tmp_password FROM PUBLIC; +REVOKE ALL ON TABLE tmp_password FROM postgres; +GRANT ALL ON TABLE tmp_password TO postgres; + + +-- +-- Name: tz_aliases; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE tz_aliases FROM PUBLIC; +REVOKE ALL ON TABLE tz_aliases FROM postgres; +GRANT ALL ON TABLE tz_aliases TO postgres; + + +-- +-- Name: tz_localnames; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE tz_localnames FROM PUBLIC; +REVOKE ALL ON TABLE tz_localnames FROM postgres; +GRANT ALL ON TABLE tz_localnames TO postgres; + + +-- +-- Name: usr_setting; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE usr_setting FROM PUBLIC; +REVOKE ALL ON TABLE usr_setting FROM postgres; +GRANT ALL ON TABLE usr_setting TO postgres; + + +-- +-- Name: usr_user_no_seq; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON SEQUENCE usr_user_no_seq FROM PUBLIC; +REVOKE ALL ON SEQUENCE usr_user_no_seq FROM postgres; +GRANT ALL ON SEQUENCE usr_user_no_seq TO postgres; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/test/sql/postfixadmin.sql b/test/sql/postfixadmin.sql new file mode 100644 index 0000000..f139a4f --- /dev/null +++ b/test/sql/postfixadmin.sql @@ -0,0 +1,737 @@ +-- +-- PostgreSQL database dump +-- + +SET statement_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SET check_function_bodies = false; +SET client_min_messages = warning; + +-- +-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: +-- + +CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; + + +-- +-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: +-- + +COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; + + +SET search_path = public, pg_catalog; + +-- +-- Name: merge_quota(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION merge_quota() RETURNS trigger + LANGUAGE plpgsql + AS $$ + BEGIN + UPDATE quota SET current = NEW.current + current WHERE username = NEW.username AND path = NEW.path; + IF found THEN + RETURN NULL; + ELSE + RETURN NEW; + END IF; + END; + $$; + + +ALTER FUNCTION public.merge_quota() OWNER TO postgres; + +-- +-- Name: merge_quota2(); Type: FUNCTION; Schema: public; Owner: postgres +-- + +CREATE FUNCTION merge_quota2() RETURNS trigger + LANGUAGE plpgsql + AS $$ + BEGIN + IF NEW.messages < 0 OR NEW.messages IS NULL THEN + -- ugly kludge: we came here from this function, really do try to insert + IF NEW.messages IS NULL THEN + NEW.messages = 0; + ELSE + NEW.messages = -NEW.messages; + END IF; + return NEW; + END IF; + + LOOP + UPDATE quota2 SET bytes = bytes + NEW.bytes, + messages = messages + NEW.messages + WHERE username = NEW.username; + IF found THEN + RETURN NULL; + END IF; + + BEGIN + IF NEW.messages = 0 THEN + INSERT INTO quota2 (bytes, messages, username) VALUES (NEW.bytes, NULL, NEW.username); + ELSE + INSERT INTO quota2 (bytes, messages, username) VALUES (NEW.bytes, -NEW.messages, NEW.username); + END IF; + return NULL; + EXCEPTION WHEN unique_violation THEN + -- someone just inserted the record, update it + END; + END LOOP; + END; + $$; + + +ALTER FUNCTION public.merge_quota2() OWNER TO postgres; + +SET default_tablespace = ''; + +SET default_with_oids = true; + +-- +-- Name: admin; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE admin ( + username character varying(255) NOT NULL, + password character varying(255) DEFAULT ''::character varying NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.admin OWNER TO postgres; + +-- +-- Name: TABLE admin; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE admin IS 'Postfix Admin - Virtual Admins'; + + +-- +-- Name: alias; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE alias ( + address character varying(255) NOT NULL, + goto text NOT NULL, + domain character varying(255) NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.alias OWNER TO postgres; + +-- +-- Name: TABLE alias; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE alias IS 'Postfix Admin - Virtual Aliases'; + + +SET default_with_oids = false; + +-- +-- Name: alias_domain; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE alias_domain ( + alias_domain character varying(255) NOT NULL, + target_domain character varying(255) NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.alias_domain OWNER TO postgres; + +-- +-- Name: TABLE alias_domain; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE alias_domain IS 'Postfix Admin - Domain Aliases'; + + +SET default_with_oids = true; + +-- +-- Name: config; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE config ( + id integer NOT NULL, + name character varying(20) NOT NULL, + value character varying(20) NOT NULL +); + + +ALTER TABLE public.config OWNER TO postgres; + +-- +-- Name: config_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE config_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.config_id_seq OWNER TO postgres; + +-- +-- Name: config_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE config_id_seq OWNED BY config.id; + + +-- +-- Name: domain; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE domain ( + domain character varying(255) NOT NULL, + description character varying(255) DEFAULT ''::character varying NOT NULL, + aliases integer DEFAULT 0 NOT NULL, + mailboxes integer DEFAULT 0 NOT NULL, + maxquota bigint DEFAULT 0 NOT NULL, + quota bigint DEFAULT 0 NOT NULL, + transport character varying(255), + backupmx boolean DEFAULT false NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.domain OWNER TO postgres; + +-- +-- Name: TABLE domain; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE domain IS 'Postfix Admin - Virtual Domains'; + + +-- +-- Name: domain_admins; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE domain_admins ( + username character varying(255) NOT NULL, + domain character varying(255) NOT NULL, + created timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL +); + + +ALTER TABLE public.domain_admins OWNER TO postgres; + +-- +-- Name: TABLE domain_admins; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE domain_admins IS 'Postfix Admin - Domain Admins'; + + +-- +-- Name: fetchmail; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE fetchmail ( + id integer NOT NULL, + mailbox character varying(255) DEFAULT ''::character varying NOT NULL, + src_server character varying(255) DEFAULT ''::character varying NOT NULL, + src_auth character varying(15) NOT NULL, + src_user character varying(255) DEFAULT ''::character varying NOT NULL, + src_password character varying(255) DEFAULT ''::character varying NOT NULL, + src_folder character varying(255) DEFAULT ''::character varying NOT NULL, + poll_time integer DEFAULT 10 NOT NULL, + fetchall boolean DEFAULT false NOT NULL, + keep boolean DEFAULT false NOT NULL, + protocol character varying(15) NOT NULL, + extra_options text, + returned_text text, + mda character varying(255) DEFAULT ''::character varying NOT NULL, + date timestamp with time zone DEFAULT now(), + usessl boolean DEFAULT false NOT NULL, + CONSTRAINT fetchmail_protocol_check CHECK (((((((protocol)::text = 'POP3'::text) OR ((protocol)::text = 'IMAP'::text)) OR ((protocol)::text = 'POP2'::text)) OR ((protocol)::text = 'ETRN'::text)) OR ((protocol)::text = 'AUTO'::text))), + CONSTRAINT fetchmail_src_auth_check CHECK (((((((((((((src_auth)::text = 'password'::text) OR ((src_auth)::text = 'kerberos_v5'::text)) OR ((src_auth)::text = 'kerberos'::text)) OR ((src_auth)::text = 'kerberos_v4'::text)) OR ((src_auth)::text = 'gssapi'::text)) OR ((src_auth)::text = 'cram-md5'::text)) OR ((src_auth)::text = 'otp'::text)) OR ((src_auth)::text = 'ntlm'::text)) OR ((src_auth)::text = 'msn'::text)) OR ((src_auth)::text = 'ssh'::text)) OR ((src_auth)::text = 'any'::text))) +); + + +ALTER TABLE public.fetchmail OWNER TO postgres; + +-- +-- Name: fetchmail_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE fetchmail_id_seq + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.fetchmail_id_seq OWNER TO postgres; + +-- +-- Name: fetchmail_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres +-- + +ALTER SEQUENCE fetchmail_id_seq OWNED BY fetchmail.id; + + +-- +-- Name: log; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE log ( + "timestamp" timestamp with time zone DEFAULT now(), + username character varying(255) DEFAULT ''::character varying NOT NULL, + domain character varying(255) DEFAULT ''::character varying NOT NULL, + action character varying(255) DEFAULT ''::character varying NOT NULL, + data text DEFAULT ''::text NOT NULL +); + + +ALTER TABLE public.log OWNER TO postgres; + +-- +-- Name: TABLE log; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE log IS 'Postfix Admin - Log'; + + +-- +-- Name: mailbox; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE mailbox ( + username character varying(255) NOT NULL, + password character varying(255) DEFAULT ''::character varying NOT NULL, + name character varying(255) DEFAULT ''::character varying NOT NULL, + maildir character varying(255) DEFAULT ''::character varying NOT NULL, + quota bigint DEFAULT 0 NOT NULL, + created timestamp with time zone DEFAULT now(), + modified timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL, + domain character varying(255), + local_part character varying(255) NOT NULL +); + + +ALTER TABLE public.mailbox OWNER TO postgres; + +-- +-- Name: TABLE mailbox; Type: COMMENT; Schema: public; Owner: postgres +-- + +COMMENT ON TABLE mailbox IS 'Postfix Admin - Virtual Mailboxes'; + + +SET default_with_oids = false; + +-- +-- Name: quota; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE quota ( + username character varying(255) NOT NULL, + path character varying(100) NOT NULL, + current bigint +); + + +ALTER TABLE public.quota OWNER TO postgres; + +-- +-- Name: quota2; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE quota2 ( + username character varying(100) NOT NULL, + bytes bigint DEFAULT 0 NOT NULL, + messages integer DEFAULT 0 NOT NULL +); + + +ALTER TABLE public.quota2 OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: vacation; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE vacation ( + email character varying(255) NOT NULL, + subject character varying(255) NOT NULL, + body text DEFAULT ''::text NOT NULL, + created timestamp with time zone DEFAULT now(), + active boolean DEFAULT true NOT NULL, + domain character varying(255) +); + + +ALTER TABLE public.vacation OWNER TO postgres; + +-- +-- Name: vacation_notification; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE vacation_notification ( + on_vacation character varying(255) NOT NULL, + notified character varying(255) NOT NULL, + notified_at timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE public.vacation_notification OWNER TO postgres; + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY config ALTER COLUMN id SET DEFAULT nextval('config_id_seq'::regclass); + + +-- +-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY fetchmail ALTER COLUMN id SET DEFAULT nextval('fetchmail_id_seq'::regclass); + + +-- +-- Name: admin_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY admin + ADD CONSTRAINT admin_key PRIMARY KEY (username); + + +-- +-- Name: alias_domain_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY alias_domain + ADD CONSTRAINT alias_domain_pkey PRIMARY KEY (alias_domain); + + +-- +-- Name: alias_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY alias + ADD CONSTRAINT alias_key PRIMARY KEY (address); + + +-- +-- Name: config_name_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY config + ADD CONSTRAINT config_name_key UNIQUE (name); + + +-- +-- Name: config_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY config + ADD CONSTRAINT config_pkey PRIMARY KEY (id); + + +-- +-- Name: domain_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY domain + ADD CONSTRAINT domain_key PRIMARY KEY (domain); + + +-- +-- Name: fetchmail_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY fetchmail + ADD CONSTRAINT fetchmail_pkey PRIMARY KEY (id); + + +-- +-- Name: mailbox_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY mailbox + ADD CONSTRAINT mailbox_key PRIMARY KEY (username); + + +-- +-- Name: quota2_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY quota2 + ADD CONSTRAINT quota2_pkey PRIMARY KEY (username); + + +-- +-- Name: quota_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY quota + ADD CONSTRAINT quota_pkey PRIMARY KEY (username, path); + + +-- +-- Name: vacation_notification_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY vacation_notification + ADD CONSTRAINT vacation_notification_pkey PRIMARY KEY (on_vacation, notified); + + +-- +-- Name: vacation_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY vacation + ADD CONSTRAINT vacation_pkey PRIMARY KEY (email); + + +-- +-- Name: alias_address_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX alias_address_active ON alias USING btree (address, active); + + +-- +-- Name: alias_domain_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX alias_domain_active ON alias_domain USING btree (alias_domain, active); + + +-- +-- Name: alias_domain_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX alias_domain_idx ON alias USING btree (domain); + + +-- +-- Name: domain_domain_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX domain_domain_active ON domain USING btree (domain, active); + + +-- +-- Name: mailbox_domain_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX mailbox_domain_idx ON mailbox USING btree (domain); + + +-- +-- Name: mailbox_username_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX mailbox_username_active ON mailbox USING btree (username, active); + + +-- +-- Name: vacation_email_active; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX vacation_email_active ON vacation USING btree (email, active); + + +-- +-- Name: mergequota; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER mergequota BEFORE INSERT ON quota FOR EACH ROW EXECUTE PROCEDURE merge_quota(); + + +-- +-- Name: mergequota2; Type: TRIGGER; Schema: public; Owner: postgres +-- + +CREATE TRIGGER mergequota2 BEFORE INSERT ON quota2 FOR EACH ROW EXECUTE PROCEDURE merge_quota2(); + + +-- +-- Name: alias_domain_alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY alias_domain + ADD CONSTRAINT alias_domain_alias_domain_fkey FOREIGN KEY (alias_domain) REFERENCES domain(domain) ON DELETE CASCADE; + + +-- +-- Name: alias_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY alias + ADD CONSTRAINT alias_domain_fkey FOREIGN KEY (domain) REFERENCES domain(domain); + + +-- +-- Name: alias_domain_target_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY alias_domain + ADD CONSTRAINT alias_domain_target_domain_fkey FOREIGN KEY (target_domain) REFERENCES domain(domain) ON DELETE CASCADE; + + +-- +-- Name: domain_admins_domain_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY domain_admins + ADD CONSTRAINT domain_admins_domain_fkey FOREIGN KEY (domain) REFERENCES domain(domain); + + +-- +-- Name: mailbox_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY mailbox + ADD CONSTRAINT mailbox_domain_fkey1 FOREIGN KEY (domain) REFERENCES domain(domain); + + +-- +-- Name: vacation_domain_fkey1; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY vacation + ADD CONSTRAINT vacation_domain_fkey1 FOREIGN KEY (domain) REFERENCES domain(domain); + + +-- +-- Name: vacation_notification_on_vacation_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY vacation_notification + ADD CONSTRAINT vacation_notification_on_vacation_fkey FOREIGN KEY (on_vacation) REFERENCES vacation(email) ON DELETE CASCADE; + + +-- +-- Name: public; Type: ACL; Schema: -; Owner: postgres +-- + +REVOKE ALL ON SCHEMA public FROM PUBLIC; +REVOKE ALL ON SCHEMA public FROM postgres; +GRANT ALL ON SCHEMA public TO postgres; +GRANT ALL ON SCHEMA public TO PUBLIC; + + +-- +-- Name: admin; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE admin FROM PUBLIC; +REVOKE ALL ON TABLE admin FROM postgres; +GRANT ALL ON TABLE admin TO postgres; + + +-- +-- Name: alias; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE alias FROM PUBLIC; +REVOKE ALL ON TABLE alias FROM postgres; +GRANT ALL ON TABLE alias TO postgres; + + +-- +-- Name: config; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE config FROM PUBLIC; +REVOKE ALL ON TABLE config FROM postgres; +GRANT ALL ON TABLE config TO postgres; + + +-- +-- Name: domain; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE domain FROM PUBLIC; +REVOKE ALL ON TABLE domain FROM postgres; +GRANT ALL ON TABLE domain TO postgres; + + +-- +-- Name: domain_admins; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE domain_admins FROM PUBLIC; +REVOKE ALL ON TABLE domain_admins FROM postgres; +GRANT ALL ON TABLE domain_admins TO postgres; + + +-- +-- Name: fetchmail; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE fetchmail FROM PUBLIC; +REVOKE ALL ON TABLE fetchmail FROM postgres; +GRANT ALL ON TABLE fetchmail TO postgres; + + +-- +-- Name: log; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE log FROM PUBLIC; +REVOKE ALL ON TABLE log FROM postgres; +GRANT ALL ON TABLE log TO postgres; + + +-- +-- Name: mailbox; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE mailbox FROM PUBLIC; +REVOKE ALL ON TABLE mailbox FROM postgres; +GRANT ALL ON TABLE mailbox TO postgres; + + +-- +-- Name: vacation; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE vacation FROM PUBLIC; +REVOKE ALL ON TABLE vacation FROM postgres; +GRANT ALL ON TABLE vacation TO postgres; + + +-- +-- Name: vacation_notification; Type: ACL; Schema: public; Owner: postgres +-- + +REVOKE ALL ON TABLE vacation_notification FROM PUBLIC; +REVOKE ALL ON TABLE vacation_notification FROM postgres; +GRANT ALL ON TABLE vacation_notification TO postgres; + + +-- +-- PostgreSQL database dump complete +-- + diff --git a/test/sql/roundcube.sql b/test/sql/roundcube.sql new file mode 100644 index 0000000..36e0a6c --- /dev/null +++ b/test/sql/roundcube.sql @@ -0,0 +1,608 @@ +-- +-- PostgreSQL database dump +-- + +SET statement_timeout = 0; +SET client_encoding = 'UTF8'; +SET standard_conforming_strings = on; +SET check_function_bodies = false; +SET client_min_messages = warning; + +-- +-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: +-- + +CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; + + +-- +-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: +-- + +COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; + + +SET search_path = public, pg_catalog; + +SET default_tablespace = ''; + +SET default_with_oids = true; + +-- +-- Name: cache; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE cache ( + user_id integer NOT NULL, + cache_key character varying(128) DEFAULT ''::character varying NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.cache OWNER TO postgres; + +SET default_with_oids = false; + +-- +-- Name: cache_index; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE cache_index ( + user_id integer NOT NULL, + mailbox character varying(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + valid smallint DEFAULT 0 NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.cache_index OWNER TO postgres; + +-- +-- Name: cache_messages; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE cache_messages ( + user_id integer NOT NULL, + mailbox character varying(255) NOT NULL, + uid integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL, + flags integer DEFAULT 0 NOT NULL +); + + +ALTER TABLE public.cache_messages OWNER TO postgres; + +-- +-- Name: cache_thread; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE cache_thread ( + user_id integer NOT NULL, + mailbox character varying(255) NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.cache_thread OWNER TO postgres; + +-- +-- Name: contact_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE contact_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.contact_ids OWNER TO postgres; + +-- +-- Name: contactgroupmembers; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE contactgroupmembers ( + contactgroup_id integer NOT NULL, + contact_id integer NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE public.contactgroupmembers OWNER TO postgres; + +-- +-- Name: contactgroups; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE contactgroups ( + contactgroup_id integer DEFAULT nextval(('contactgroups_ids'::text)::regclass) NOT NULL, + user_id integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + del smallint DEFAULT 0 NOT NULL, + name character varying(128) DEFAULT ''::character varying NOT NULL +); + + +ALTER TABLE public.contactgroups OWNER TO postgres; + +-- +-- Name: contactgroups_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE contactgroups_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.contactgroups_ids OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: contacts; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE contacts ( + contact_id integer DEFAULT nextval(('contact_ids'::text)::regclass) NOT NULL, + user_id integer NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + del smallint DEFAULT 0 NOT NULL, + name character varying(128) DEFAULT ''::character varying NOT NULL, + email text DEFAULT ''::character varying NOT NULL, + firstname character varying(128) DEFAULT ''::character varying NOT NULL, + surname character varying(128) DEFAULT ''::character varying NOT NULL, + vcard text, + words text +); + + +ALTER TABLE public.contacts OWNER TO postgres; + +SET default_with_oids = false; + +-- +-- Name: dictionary; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE dictionary ( + user_id integer, + language character varying(5) NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.dictionary OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: identities; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE identities ( + identity_id integer DEFAULT nextval(('identity_ids'::text)::regclass) NOT NULL, + user_id integer NOT NULL, + del smallint DEFAULT 0 NOT NULL, + standard smallint DEFAULT 0 NOT NULL, + name character varying(128) NOT NULL, + organization character varying(128), + email character varying(128) NOT NULL, + "reply-to" character varying(128), + bcc character varying(128), + signature text, + html_signature integer DEFAULT 0 NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL +); + + +ALTER TABLE public.identities OWNER TO postgres; + +-- +-- Name: identity_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE identity_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.identity_ids OWNER TO postgres; + +-- +-- Name: search_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE search_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.search_ids OWNER TO postgres; + +SET default_with_oids = false; + +-- +-- Name: searches; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE searches ( + search_id integer DEFAULT nextval(('search_ids'::text)::regclass) NOT NULL, + user_id integer NOT NULL, + type smallint DEFAULT 0 NOT NULL, + name character varying(128) NOT NULL, + data text NOT NULL +); + + +ALTER TABLE public.searches OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: session; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE session ( + sess_id character varying(128) DEFAULT ''::character varying NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL, + changed timestamp with time zone DEFAULT now() NOT NULL, + ip character varying(41) NOT NULL, + vars text NOT NULL +); + + +ALTER TABLE public.session OWNER TO postgres; + +SET default_with_oids = false; + +-- +-- Name: system; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE system ( + name character varying(64) NOT NULL, + value text +); + + +ALTER TABLE public.system OWNER TO postgres; + +-- +-- Name: user_ids; Type: SEQUENCE; Schema: public; Owner: postgres +-- + +CREATE SEQUENCE user_ids + START WITH 1 + INCREMENT BY 1 + NO MINVALUE + NO MAXVALUE + CACHE 1; + + +ALTER TABLE public.user_ids OWNER TO postgres; + +SET default_with_oids = true; + +-- +-- Name: users; Type: TABLE; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE TABLE users ( + user_id integer DEFAULT nextval(('user_ids'::text)::regclass) NOT NULL, + username character varying(128) DEFAULT ''::character varying NOT NULL, + mail_host character varying(128) DEFAULT ''::character varying NOT NULL, + created timestamp with time zone DEFAULT now() NOT NULL, + last_login timestamp with time zone, + language character varying(5), + preferences text DEFAULT ''::text NOT NULL +); + + +ALTER TABLE public.users OWNER TO postgres; + +-- +-- Name: cache_index_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY cache_index + ADD CONSTRAINT cache_index_pkey PRIMARY KEY (user_id, mailbox); + + +-- +-- Name: cache_messages_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY cache_messages + ADD CONSTRAINT cache_messages_pkey PRIMARY KEY (user_id, mailbox, uid); + + +-- +-- Name: cache_thread_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY cache_thread + ADD CONSTRAINT cache_thread_pkey PRIMARY KEY (user_id, mailbox); + + +-- +-- Name: contactgroupmembers_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY contactgroupmembers + ADD CONSTRAINT contactgroupmembers_pkey PRIMARY KEY (contactgroup_id, contact_id); + + +-- +-- Name: contactgroups_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY contactgroups + ADD CONSTRAINT contactgroups_pkey PRIMARY KEY (contactgroup_id); + + +-- +-- Name: contacts_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY contacts + ADD CONSTRAINT contacts_pkey PRIMARY KEY (contact_id); + + +-- +-- Name: dictionary_user_id_language_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY dictionary + ADD CONSTRAINT dictionary_user_id_language_key UNIQUE (user_id, language); + + +-- +-- Name: identities_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY identities + ADD CONSTRAINT identities_pkey PRIMARY KEY (identity_id); + + +-- +-- Name: searches_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY searches + ADD CONSTRAINT searches_pkey PRIMARY KEY (search_id); + + +-- +-- Name: searches_user_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY searches + ADD CONSTRAINT searches_user_id_key UNIQUE (user_id, type, name); + + +-- +-- Name: session_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY session + ADD CONSTRAINT session_pkey PRIMARY KEY (sess_id); + + +-- +-- Name: system_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY system + ADD CONSTRAINT system_pkey PRIMARY KEY (name); + + +-- +-- Name: users_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY users + ADD CONSTRAINT users_pkey PRIMARY KEY (user_id); + + +-- +-- Name: users_username_key; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: +-- + +ALTER TABLE ONLY users + ADD CONSTRAINT users_username_key UNIQUE (username, mail_host); + + +-- +-- Name: cache_created_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_created_idx ON cache USING btree (created); + + +-- +-- Name: cache_index_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_index_changed_idx ON cache_index USING btree (changed); + + +-- +-- Name: cache_messages_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_messages_changed_idx ON cache_messages USING btree (changed); + + +-- +-- Name: cache_thread_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_thread_changed_idx ON cache_thread USING btree (changed); + + +-- +-- Name: cache_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX cache_user_id_idx ON cache USING btree (user_id, cache_key); + + +-- +-- Name: contactgroupmembers_contact_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX contactgroupmembers_contact_id_idx ON contactgroupmembers USING btree (contact_id); + + +-- +-- Name: contactgroups_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX contactgroups_user_id_idx ON contactgroups USING btree (user_id, del); + + +-- +-- Name: contacts_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX contacts_user_id_idx ON contacts USING btree (user_id, del); + + +-- +-- Name: identities_email_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX identities_email_idx ON identities USING btree (email, del); + + +-- +-- Name: identities_user_id_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX identities_user_id_idx ON identities USING btree (user_id, del); + + +-- +-- Name: session_changed_idx; Type: INDEX; Schema: public; Owner: postgres; Tablespace: +-- + +CREATE INDEX session_changed_idx ON session USING btree (changed); + + +-- +-- Name: cache_index_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY cache_index + ADD CONSTRAINT cache_index_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: cache_messages_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY cache_messages + ADD CONSTRAINT cache_messages_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: cache_thread_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY cache_thread + ADD CONSTRAINT cache_thread_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: cache_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY cache + ADD CONSTRAINT cache_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: contactgroupmembers_contact_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY contactgroupmembers + ADD CONSTRAINT contactgroupmembers_contact_id_fkey FOREIGN KEY (contact_id) REFERENCES contacts(contact_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: contactgroupmembers_contactgroup_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY contactgroupmembers + ADD CONSTRAINT contactgroupmembers_contactgroup_id_fkey FOREIGN KEY (contactgroup_id) REFERENCES contactgroups(contactgroup_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: contactgroups_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY contactgroups + ADD CONSTRAINT contactgroups_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: contacts_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY contacts + ADD CONSTRAINT contacts_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: dictionary_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY dictionary + ADD CONSTRAINT dictionary_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: identities_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY identities + ADD CONSTRAINT identities_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: searches_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres +-- + +ALTER TABLE ONLY searches + ADD CONSTRAINT searches_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(user_id) ON UPDATE CASCADE ON DELETE CASCADE; + + +-- +-- Name: public; Type: ACL; Schema: -; Owner: postgres +-- + +REVOKE ALL ON SCHEMA public FROM PUBLIC; +REVOKE ALL ON SCHEMA public FROM postgres; +GRANT ALL ON SCHEMA public TO postgres; +GRANT ALL ON SCHEMA public TO PUBLIC; + + +-- +-- PostgreSQL database dump complete +-- + -- 2.44.2