File: /volume1/@appstore/Contacts/etc/sql/1.sql
-- Database generated with pgModeler (PostgreSQL Database Modeler).
-- pgModeler version: 0.9.2-beta
-- PostgreSQL version: 9.3
-- Project Site: pgmodeler.io
-- Model Author: ---
SET check_function_bodies = false;
-- ddl-end --
-- -- object: "Contacts" | type: ROLE --
-- -- DROP ROLE IF EXISTS "Contacts";
-- CREATE ROLE "Contacts" WITH
-- LOGIN;
-- -- ddl-end --
--
-- Database creation must be done outside a multicommand file.
-- These commands were put in this file only as a convenience.
-- -- object: "Contacts" | type: DATABASE --
-- -- DROP DATABASE IF EXISTS "Contacts";
-- CREATE DATABASE "Contacts"
-- OWNER = "Contacts";
-- -- ddl-end --
--
-- object: public.principal | type: TABLE --
-- DROP TABLE IF EXISTS public.principal CASCADE;
CREATE TABLE public.principal (
id bigserial NOT NULL,
owner_id bigint NOT NULL,
type smallint NOT NULL,
display_name varchar(1024),
account_name varchar(1024),
status smallint DEFAULT 0,
created_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
disabled_time bigint DEFAULT 0,
modified_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
CONSTRAINT principal_pk PRIMARY KEY (id),
CONSTRAINT principal_id_owner_id_uq UNIQUE (id,owner_id)
);
-- ddl-end --
COMMENT ON TABLE public.principal IS 'principal uris are same as user/group''s name';
-- ddl-end --
COMMENT ON COLUMN public.principal.owner_id IS 'Based on principal type:
system principals:
-1: Everyone
user/group:
uid/gid';
-- ddl-end --
COMMENT ON COLUMN public.principal.type IS '1: user
2: group';
-- ddl-end --
COMMENT ON COLUMN public.principal.display_name IS 'SYNO_USER_FULLNAME_UTF8_MAX: 64*6+1';
-- ddl-end --
COMMENT ON COLUMN public.principal.account_name IS 'SYNO_USERNAME_UTF8_MAX: 492';
-- ddl-end --
COMMENT ON COLUMN public.principal.disabled_time IS 'if a user/group is removed in dsm, the corresponding principal is set to disabled instead of being deleted';
-- ddl-end --
ALTER TABLE public.principal OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.principal (owner_id,type,display_name,account_name) VALUES (-1,2,'syno.contacts.internal.everyone','syno.contacts.internal.everyone');
-- ddl-end --
-- object: public.addressbook | type: TABLE --
-- DROP TABLE IF EXISTS public.addressbook CASCADE;
CREATE TABLE public.addressbook (
id bigserial NOT NULL,
uri varchar(255) NOT NULL,
display_name varchar(255) NOT NULL,
color varchar(255) NOT NULL,
description varchar(255),
type smallint DEFAULT 1,
created_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
modified_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
CONSTRAINT addressbook_pk PRIMARY KEY (id),
CONSTRAINT addressbook_uri_uq UNIQUE (uri)
);
-- ddl-end --
COMMENT ON COLUMN public.addressbook.uri IS 'use uuid4';
-- ddl-end --
COMMENT ON COLUMN public.addressbook.type IS '0: public
1: normal
2: suggested
3: default
4: public_default';
-- ddl-end --
ALTER TABLE public.addressbook OWNER TO "Contacts";
-- ddl-end --
-- object: public.addressbook_object | type: TABLE --
-- DROP TABLE IF EXISTS public.addressbook_object CASCADE;
CREATE TABLE public.addressbook_object (
id bigserial NOT NULL,
uri varchar(255) NOT NULL,
vcard_text text NOT NULL,
kind smallint NOT NULL,
md5 char(32) NOT NULL,
is_hidden boolean DEFAULT false,
created_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
modified_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
id_addressbook bigint,
usage_frequency bigint NOT NULL DEFAULT 0,
CONSTRAINT addressbook_object_pk PRIMARY KEY (id),
CONSTRAINT addressbook_object_uri_uq UNIQUE (uri)
);
-- ddl-end --
COMMENT ON COLUMN public.addressbook_object.uri IS 'use uuid4';
-- ddl-end --
COMMENT ON COLUMN public.addressbook_object.kind IS '0: person
1: group';
-- ddl-end --
ALTER TABLE public.addressbook_object OWNER TO "Contacts";
-- ddl-end --
-- object: public.label | type: TABLE --
-- DROP TABLE IF EXISTS public.label CASCADE;
CREATE TABLE public.label (
id bigserial NOT NULL,
id_principal bigint,
display_name varchar(255) NOT NULL,
color varchar(255) NOT NULL,
type smallint DEFAULT 0,
CONSTRAINT label_pk PRIMARY KEY (id)
);
-- ddl-end --
COMMENT ON COLUMN public.label.type IS '0: normal
1: starred';
-- ddl-end --
ALTER TABLE public.label OWNER TO "Contacts";
-- ddl-end --
-- object: public.principal_many_group_has_many_member | type: TABLE --
-- DROP TABLE IF EXISTS public.principal_many_group_has_many_member CASCADE;
CREATE TABLE public.principal_many_group_has_many_member (
id_group bigint NOT NULL,
id_member bigint NOT NULL,
CONSTRAINT principal_many_group_has_many_member_pk PRIMARY KEY (id_group,id_member)
);
-- ddl-end --
ALTER TABLE public.principal_many_group_has_many_member OWNER TO "Contacts";
-- ddl-end --
-- object: public.addressbook_object_many_group_has_many_member | type: TABLE --
-- DROP TABLE IF EXISTS public.addressbook_object_many_group_has_many_member CASCADE;
CREATE TABLE public.addressbook_object_many_group_has_many_member (
id_group bigint NOT NULL,
id_member bigint NOT NULL,
CONSTRAINT addressbook_object_many_group_has_many_member_pk PRIMARY KEY (id_group,id_member)
);
-- ddl-end --
ALTER TABLE public.addressbook_object_many_group_has_many_member OWNER TO "Contacts";
-- ddl-end --
-- object: public.addressbook_object_revision | type: TABLE --
-- DROP TABLE IF EXISTS public.addressbook_object_revision CASCADE;
CREATE TABLE public.addressbook_object_revision (
id bigserial NOT NULL,
id_principal bigint NOT NULL,
id_addressbook bigint NOT NULL,
uri varchar(255) NOT NULL,
is_deleted boolean NOT NULL,
modified_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
CONSTRAINT addressbook_object_revision_pk PRIMARY KEY (id)
);
-- ddl-end --
COMMENT ON COLUMN public.addressbook_object_revision.uri IS 'same as uri in addressbook_object';
-- ddl-end --
ALTER TABLE public.addressbook_object_revision OWNER TO "Contacts";
-- ddl-end --
-- object: public.config | type: TABLE --
-- DROP TABLE IF EXISTS public.config CASCADE;
CREATE TABLE public.config (
key varchar(255) NOT NULL,
value text NOT NULL,
CONSTRAINT config_pk PRIMARY KEY (key)
);
-- ddl-end --
ALTER TABLE public.config OWNER TO "Contacts";
-- ddl-end --
-- object: public.token_to_tsvector_immutable_function | type: FUNCTION --
-- DROP FUNCTION IF EXISTS public.token_to_tsvector_immutable_function(IN varchar) CASCADE;
CREATE FUNCTION public.token_to_tsvector_immutable_function (IN token varchar)
RETURNS tsvector
LANGUAGE plpgsql
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
result tsvector;
BEGIN
SELECT to_tsvector(token) INTO result;
RETURN result;
END
$$;
-- ddl-end --
ALTER FUNCTION public.token_to_tsvector_immutable_function(IN varchar) OWNER TO "Contacts";
-- ddl-end --
-- object: public.directory_object | type: TABLE --
-- DROP TABLE IF EXISTS public.directory_object CASCADE;
CREATE TABLE public.directory_object (
id bigint NOT NULL,
json_data json DEFAULT '{}'::json,
CONSTRAINT directory_object_pk PRIMARY KEY (id)
);
-- ddl-end --
ALTER TABLE public.directory_object OWNER TO "Contacts";
-- ddl-end --
-- object: public.directory_object_custom_setting | type: TABLE --
-- DROP TABLE IF EXISTS public.directory_object_custom_setting CASCADE;
CREATE TABLE public.directory_object_custom_setting (
json_data json DEFAULT '{}'::json,
id_directory_object bigint NOT NULL,
CONSTRAINT directory_object_custom_setting_pk PRIMARY KEY (id_directory_object)
);
-- ddl-end --
ALTER TABLE public.directory_object_custom_setting OWNER TO "Contacts";
-- ddl-end --
-- object: public.id_directory_object_to_id_addressbook_object | type: TABLE --
-- DROP TABLE IF EXISTS public.id_directory_object_to_id_addressbook_object CASCADE;
CREATE TABLE public.id_directory_object_to_id_addressbook_object (
id_directory_object bigint NOT NULL,
id_addressbook_object bigint NOT NULL,
CONSTRAINT id_directory_object_to_id_addressbook_object_pk PRIMARY KEY (id_directory_object,id_addressbook_object)
);
-- ddl-end --
ALTER TABLE public.id_directory_object_to_id_addressbook_object OWNER TO "Contacts";
-- ddl-end --
-- object: principal_fk | type: CONSTRAINT --
-- ALTER TABLE public.label DROP CONSTRAINT IF EXISTS principal_fk CASCADE;
ALTER TABLE public.label ADD CONSTRAINT principal_fk FOREIGN KEY (id_principal)
REFERENCES public.principal (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: addressbook_fk | type: CONSTRAINT --
-- ALTER TABLE public.addressbook_object DROP CONSTRAINT IF EXISTS addressbook_fk CASCADE;
ALTER TABLE public.addressbook_object ADD CONSTRAINT addressbook_fk FOREIGN KEY (id_addressbook)
REFERENCES public.addressbook (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: directory_object_fk | type: CONSTRAINT --
-- ALTER TABLE public.directory_object_custom_setting DROP CONSTRAINT IF EXISTS directory_object_fk CASCADE;
ALTER TABLE public.directory_object_custom_setting ADD CONSTRAINT directory_object_fk FOREIGN KEY (id_directory_object)
REFERENCES public.directory_object (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: directory_object_custom_setting_uq | type: CONSTRAINT --
-- ALTER TABLE public.directory_object_custom_setting DROP CONSTRAINT IF EXISTS directory_object_custom_setting_uq CASCADE;
ALTER TABLE public.directory_object_custom_setting ADD CONSTRAINT directory_object_custom_setting_uq UNIQUE (id_directory_object);
-- ddl-end --
-- object: public.update_modified_time_trigger_function | type: FUNCTION --
-- DROP FUNCTION IF EXISTS public.update_modified_time_trigger_function() CASCADE;
CREATE FUNCTION public.update_modified_time_trigger_function ()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
New.modified_time = extract(epoch from now())::bigint;
RETURN NEW;
END
$$;
-- ddl-end --
ALTER FUNCTION public.update_modified_time_trigger_function() OWNER TO "Contacts";
-- ddl-end --
-- object: update_modified_time_trigger | type: TRIGGER --
-- DROP TRIGGER IF EXISTS update_modified_time_trigger ON public.addressbook CASCADE;
CREATE TRIGGER update_modified_time_trigger
BEFORE UPDATE
ON public.addressbook
FOR EACH ROW
EXECUTE PROCEDURE public.update_modified_time_trigger_function();
-- ddl-end --
-- object: update_modified_time_trigger | type: TRIGGER --
-- DROP TRIGGER IF EXISTS update_modified_time_trigger ON public.addressbook_object_revision CASCADE;
CREATE TRIGGER update_modified_time_trigger
BEFORE UPDATE
ON public.addressbook_object_revision
FOR EACH ROW
EXECUTE PROCEDURE public.update_modified_time_trigger_function();
-- ddl-end --
-- object: update_modified_time_trigger | type: TRIGGER --
-- DROP TRIGGER IF EXISTS update_modified_time_trigger ON public.addressbook_object CASCADE;
CREATE TRIGGER update_modified_time_trigger
BEFORE UPDATE
ON public.addressbook_object
FOR EACH ROW
EXECUTE PROCEDURE public.update_modified_time_trigger_function();
-- ddl-end --
-- object: public.addressbook_object_metadata | type: TABLE --
-- DROP TABLE IF EXISTS public.addressbook_object_metadata CASCADE;
CREATE TABLE public.addressbook_object_metadata (
id_addressbook_object bigint NOT NULL,
full_name varchar(1024),
company varchar(1024),
department varchar(1024),
job_title varchar(1024),
email varchar(1024),
telephone varchar(1024),
date varchar(1024),
birthday varchar(1024),
photo text,
CONSTRAINT addressbook_object_metadata_pk PRIMARY KEY (id_addressbook_object)
);
-- ddl-end --
ALTER TABLE public.addressbook_object_metadata OWNER TO "Contacts";
-- ddl-end --
-- object: addressbook_object_fk | type: CONSTRAINT --
-- ALTER TABLE public.addressbook_object_metadata DROP CONSTRAINT IF EXISTS addressbook_object_fk CASCADE;
ALTER TABLE public.addressbook_object_metadata ADD CONSTRAINT addressbook_object_fk FOREIGN KEY (id_addressbook_object)
REFERENCES public.addressbook_object (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: addressbook_object_metadata_uq | type: CONSTRAINT --
-- ALTER TABLE public.addressbook_object_metadata DROP CONSTRAINT IF EXISTS addressbook_object_metadata_uq CASCADE;
ALTER TABLE public.addressbook_object_metadata ADD CONSTRAINT addressbook_object_metadata_uq UNIQUE (id_addressbook_object);
-- ddl-end --
-- object: public.addressbook_object_with_metadata_view | type: VIEW --
-- DROP VIEW IF EXISTS public.addressbook_object_with_metadata_view CASCADE;
CREATE VIEW public.addressbook_object_with_metadata_view
AS
SELECT
ABO.*, M.photo, M.full_name, M.company, M.department, M.job_title, M.email, M.telephone, M.date, M.birthday
FROM
public.addressbook_object as ABO
INNER JOIN public.addressbook_object_metadata as M ON ABO.id = M.id_addressbook_object;
-- ddl-end --
ALTER VIEW public.addressbook_object_with_metadata_view OWNER TO "Contacts";
-- ddl-end --
-- object: public.many_principal_has_many_addressbook | type: TABLE --
-- DROP TABLE IF EXISTS public.many_principal_has_many_addressbook CASCADE;
CREATE TABLE public.many_principal_has_many_addressbook (
id_principal bigint NOT NULL,
mode smallint NOT NULL,
status smallint NOT NULL,
id_addressbook bigint NOT NULL,
CONSTRAINT many_principal_has_many_addressbook_pk PRIMARY KEY (id_principal,id_addressbook)
);
-- ddl-end --
COMMENT ON COLUMN public.many_principal_has_many_addressbook.mode IS '3: own
2: manage
1: write
0: read';
-- ddl-end --
COMMENT ON COLUMN public.many_principal_has_many_addressbook.status IS '2: accepted
1: declined
0: invited';
-- ddl-end --
ALTER TABLE public.many_principal_has_many_addressbook OWNER TO "Contacts";
-- ddl-end --
-- object: principal_fk | type: CONSTRAINT --
-- ALTER TABLE public.many_principal_has_many_addressbook DROP CONSTRAINT IF EXISTS principal_fk CASCADE;
ALTER TABLE public.many_principal_has_many_addressbook ADD CONSTRAINT principal_fk FOREIGN KEY (id_principal)
REFERENCES public.principal (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: addressbook_fk | type: CONSTRAINT --
-- ALTER TABLE public.many_principal_has_many_addressbook DROP CONSTRAINT IF EXISTS addressbook_fk CASCADE;
ALTER TABLE public.many_principal_has_many_addressbook ADD CONSTRAINT addressbook_fk FOREIGN KEY (id_addressbook)
REFERENCES public.addressbook (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: public.many_label_has_many_addressbook_object | type: TABLE --
-- DROP TABLE IF EXISTS public.many_label_has_many_addressbook_object CASCADE;
CREATE TABLE public.many_label_has_many_addressbook_object (
id_label bigint NOT NULL,
id_addressbook_object bigint NOT NULL,
CONSTRAINT many_label_has_many_addressbook_object_pk PRIMARY KEY (id_label,id_addressbook_object)
);
-- ddl-end --
ALTER TABLE public.many_label_has_many_addressbook_object OWNER TO "Contacts";
-- ddl-end --
-- object: label_fk | type: CONSTRAINT --
-- ALTER TABLE public.many_label_has_many_addressbook_object DROP CONSTRAINT IF EXISTS label_fk CASCADE;
ALTER TABLE public.many_label_has_many_addressbook_object ADD CONSTRAINT label_fk FOREIGN KEY (id_label)
REFERENCES public.label (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: addressbook_object_fk | type: CONSTRAINT --
-- ALTER TABLE public.many_label_has_many_addressbook_object DROP CONSTRAINT IF EXISTS addressbook_object_fk CASCADE;
ALTER TABLE public.many_label_has_many_addressbook_object ADD CONSTRAINT addressbook_object_fk FOREIGN KEY (id_addressbook_object)
REFERENCES public.addressbook_object (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: public.principal_with_group_id_view | type: VIEW --
-- DROP VIEW IF EXISTS public.principal_with_group_id_view CASCADE;
CREATE VIEW public.principal_with_group_id_view
AS
SELECT
P.*, G.id_group
FROM
public.principal as P
LEFT OUTER JOIN public.principal_many_group_has_many_member AS G ON P.id = G.id_member;
-- ddl-end --
ALTER VIEW public.principal_with_group_id_view OWNER TO "Contacts";
-- ddl-end --
-- object: public.principal_id_to_addressbook_view | type: VIEW --
-- DROP VIEW IF EXISTS public.principal_id_to_addressbook_view CASCADE;
CREATE VIEW public.principal_id_to_addressbook_view
AS
SELECT
DISTINCT
P.id as id_principal, P.owner_id as owner_id_principal,
A.*,
R.mode, R.status
FROM
public.addressbook as A,
public.many_principal_has_many_addressbook as R,
public.principal_with_group_id_view as P
WHERE (P.id = R.id_principal OR P.id_group = R.id_principal) AND A.id = R.id_addressbook;
-- ddl-end --
ALTER VIEW public.principal_id_to_addressbook_view OWNER TO "Contacts";
-- ddl-end --
-- object: public.update_vcard_md5_trigger_function | type: FUNCTION --
-- DROP FUNCTION IF EXISTS public.update_vcard_md5_trigger_function() CASCADE;
CREATE FUNCTION public.update_vcard_md5_trigger_function ()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
NEW.md5 = MD5(NEW.vcard_text);
return NEW;
END
$$;
-- ddl-end --
ALTER FUNCTION public.update_vcard_md5_trigger_function() OWNER TO "Contacts";
-- ddl-end --
-- object: update_md5_trigger | type: TRIGGER --
-- DROP TRIGGER IF EXISTS update_md5_trigger ON public.addressbook_object CASCADE;
CREATE TRIGGER update_md5_trigger
BEFORE INSERT OR UPDATE
ON public.addressbook_object
FOR EACH ROW
EXECUTE PROCEDURE public.update_vcard_md5_trigger_function();
-- ddl-end --
-- object: public.update_ical_md5_trigger_function | type: FUNCTION --
-- DROP FUNCTION IF EXISTS public.update_ical_md5_trigger_function() CASCADE;
CREATE FUNCTION public.update_ical_md5_trigger_function ()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
NEW.md5 = MD5(NEW.icalendar_text);
return NEW;
END
$$;
-- ddl-end --
ALTER FUNCTION public.update_ical_md5_trigger_function() OWNER TO "Contacts";
-- ddl-end --
-- object: principal_owner_id_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.principal_owner_id_idx CASCADE;
CREATE INDEX principal_owner_id_idx ON public.principal
USING btree
(
owner_id
);
-- ddl-end --
-- object: label_id_id_principal_uq | type: CONSTRAINT --
-- ALTER TABLE public.label DROP CONSTRAINT IF EXISTS label_id_id_principal_uq CASCADE;
ALTER TABLE public.label ADD CONSTRAINT label_id_id_principal_uq UNIQUE (id,id_principal);
-- ddl-end --
-- object: label_id_principal_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.label_id_principal_idx CASCADE;
CREATE INDEX label_id_principal_idx ON public.label
USING btree
(
id_principal
);
-- ddl-end --
-- object: addressbook_object_id_id_addressbook_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.addressbook_object_id_id_addressbook_idx CASCADE;
CREATE INDEX addressbook_object_id_id_addressbook_idx ON public.addressbook_object_revision
USING btree
(
id,
id_addressbook ASC NULLS LAST
);
-- ddl-end --
-- object: principal_many_group_has_many_member_id_member_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.principal_many_group_has_many_member_id_member_idx CASCADE;
CREATE INDEX principal_many_group_has_many_member_id_member_idx ON public.principal_many_group_has_many_member
USING btree
(
id_member
);
-- ddl-end --
-- object: public.principal_id_to_addressbook_privilege_view | type: VIEW --
-- DROP VIEW IF EXISTS public.principal_id_to_addressbook_privilege_view CASCADE;
CREATE VIEW public.principal_id_to_addressbook_privilege_view
AS
SELECT
DISTINCT
P.id as id_principal, P.owner_id as owner_id_principal,
R.id_addressbook, R.mode, R.status
FROM
public.many_principal_has_many_addressbook as R,
public.principal_with_group_id_view as P
WHERE P.id = R.id_principal OR P.id_group = R.id_principal;
-- ddl-end --
ALTER VIEW public.principal_id_to_addressbook_privilege_view OWNER TO "Contacts";
-- ddl-end --
-- object: many_principal_has_many_addressbook_id_addressbook_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.many_principal_has_many_addressbook_id_addressbook_idx CASCADE;
CREATE INDEX many_principal_has_many_addressbook_id_addressbook_idx ON public.many_principal_has_many_addressbook
USING btree
(
id_addressbook
);
-- ddl-end --
-- object: public.addressbook_object_search_token | type: TABLE --
-- DROP TABLE IF EXISTS public.addressbook_object_search_token CASCADE;
CREATE TABLE public.addressbook_object_search_token (
id_addressbook_object bigint,
token varchar(1024),
type smallint
);
-- ddl-end --
COMMENT ON COLUMN public.addressbook_object_search_token.type IS '0: given_name
1: middle_name
2: family_name
3: job_title
4: department
5: company
6: email
7: telephone
8: address
9: url
10: date
11: instant_messenger
12: phonetic_given_name
13: phonetic_middle_name
14: phonetic_family_name
15: nickname';
-- ddl-end --
ALTER TABLE public.addressbook_object_search_token OWNER TO "Contacts";
-- ddl-end --
-- object: addressbook_object_fk | type: CONSTRAINT --
-- ALTER TABLE public.addressbook_object_search_token DROP CONSTRAINT IF EXISTS addressbook_object_fk CASCADE;
ALTER TABLE public.addressbook_object_search_token ADD CONSTRAINT addressbook_object_fk FOREIGN KEY (id_addressbook_object)
REFERENCES public.addressbook_object (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: addressbook_object_search_token_id_addressbook_object_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.addressbook_object_search_token_id_addressbook_object_idx CASCADE;
CREATE INDEX addressbook_object_search_token_id_addressbook_object_idx ON public.addressbook_object_search_token
USING btree
(
id_addressbook_object
);
-- ddl-end --
-- object: addressbook_object_search_token_token_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.addressbook_object_search_token_token_idx CASCADE;
CREATE INDEX addressbook_object_search_token_token_idx ON public.addressbook_object_search_token
USING gin
(
(public.token_to_tsvector_immutable_function(token))
)
WITH (FASTUPDATE = ON);
-- ddl-end --
-- object: many_label_has_many_addressbook_object_id_abo_object_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.many_label_has_many_addressbook_object_id_abo_object_idx CASCADE;
CREATE INDEX many_label_has_many_addressbook_object_id_abo_object_idx ON public.many_label_has_many_addressbook_object
USING btree
(
id_addressbook_object
);
-- ddl-end --
-- object: addressbook_display_name_idx | type: INDEX --
-- DROP INDEX IF EXISTS public.addressbook_display_name_idx CASCADE;
CREATE INDEX addressbook_display_name_idx ON public.addressbook
USING btree
(
display_name
);
-- ddl-end --
-- object: public.organization_unit | type: TABLE --
-- DROP TABLE IF EXISTS public.organization_unit CASCADE;
CREATE TABLE public.organization_unit (
id bigserial NOT NULL,
dn text NOT NULL,
created_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
modified_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
CONSTRAINT organization_unit_pk PRIMARY KEY (id)
);
-- ddl-end --
ALTER TABLE public.organization_unit OWNER TO "Contacts";
-- ddl-end --
-- object: public.organization_unit_has_many_principal | type: TABLE --
-- DROP TABLE IF EXISTS public.organization_unit_has_many_principal CASCADE;
CREATE TABLE public.organization_unit_has_many_principal (
id_organization_unit bigint,
id_principal bigint
);
-- ddl-end --
ALTER TABLE public.organization_unit_has_many_principal OWNER TO "Contacts";
-- ddl-end --
-- object: update_modified_time_trigger | type: TRIGGER --
-- DROP TRIGGER IF EXISTS update_modified_time_trigger ON public.principal CASCADE;
CREATE TRIGGER update_modified_time_trigger
BEFORE UPDATE
ON public.principal
FOR EACH ROW
EXECUTE PROCEDURE public.update_modified_time_trigger_function();
-- ddl-end --
-- object: update_modified_time_trigger | type: TRIGGER --
-- DROP TRIGGER IF EXISTS update_modified_time_trigger ON public.organization_unit CASCADE;
CREATE TRIGGER update_modified_time_trigger
BEFORE UPDATE
ON public.organization_unit
FOR EACH ROW
EXECUTE PROCEDURE public.update_modified_time_trigger_function();
-- ddl-end --
-- object: public.principal_type | type: TABLE --
-- DROP TABLE IF EXISTS public.principal_type CASCADE;
CREATE TABLE public.principal_type (
type smallint NOT NULL,
description text,
CONSTRAINT principal_type_pk PRIMARY KEY (type)
);
-- ddl-end --
ALTER TABLE public.principal_type OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.principal_type (type,description) VALUES (1,'user');
INSERT INTO public.principal_type (type,description) VALUES (2,'group');
-- ddl-end --
-- object: public.many_principal_has_many_addressbook_mode | type: TABLE --
-- DROP TABLE IF EXISTS public.many_principal_has_many_addressbook_mode CASCADE;
CREATE TABLE public.many_principal_has_many_addressbook_mode (
mode smallint NOT NULL,
description text,
CONSTRAINT many_principal_has_many_addressbook_mode_pk PRIMARY KEY (mode)
);
-- ddl-end --
ALTER TABLE public.many_principal_has_many_addressbook_mode OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.many_principal_has_many_addressbook_mode (mode,description) VALUES (0,'read');
INSERT INTO public.many_principal_has_many_addressbook_mode (mode,description) VALUES (1,'write');
INSERT INTO public.many_principal_has_many_addressbook_mode (mode,description) VALUES (2,'manage');
INSERT INTO public.many_principal_has_many_addressbook_mode (mode,description) VALUES (3,'own');
INSERT INTO public.many_principal_has_many_addressbook_mode (mode,description) VALUES (4,'group');
INSERT INTO public.many_principal_has_many_addressbook_mode (mode,description) VALUES (5,'group_read');
INSERT INTO public.many_principal_has_many_addressbook_mode (mode,description) VALUES (6,'group_write');
INSERT INTO public.many_principal_has_many_addressbook_mode (mode,description) VALUES (7,'group_manage');
-- ddl-end --
-- object: public.many_principal_has_many_addressbook_status | type: TABLE --
-- DROP TABLE IF EXISTS public.many_principal_has_many_addressbook_status CASCADE;
CREATE TABLE public.many_principal_has_many_addressbook_status (
status smallint NOT NULL,
description text,
CONSTRAINT many_principal_has_many_addressbook_status_pk PRIMARY KEY (status)
);
-- ddl-end --
ALTER TABLE public.many_principal_has_many_addressbook_status OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.many_principal_has_many_addressbook_status (status,description) VALUES (0,'invited');
INSERT INTO public.many_principal_has_many_addressbook_status (status,description) VALUES (1,'declined');
INSERT INTO public.many_principal_has_many_addressbook_status (status,description) VALUES (2,'accepted');
-- ddl-end --
-- object: public.addressbook_type | type: TABLE --
-- DROP TABLE IF EXISTS public.addressbook_type CASCADE;
CREATE TABLE public.addressbook_type (
type smallint NOT NULL,
description text,
CONSTRAINT addressbook_type_pk PRIMARY KEY (type)
);
-- ddl-end --
ALTER TABLE public.addressbook_type OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.addressbook_type (type,description) VALUES (0,'public');
INSERT INTO public.addressbook_type (type,description) VALUES (1,'normal');
INSERT INTO public.addressbook_type (type,description) VALUES (2,'suggested');
INSERT INTO public.addressbook_type (type,description) VALUES (3,'default');
INSERT INTO public.addressbook_type (type,description) VALUES (4,'default_public');
INSERT INTO public.addressbook_type (type,description) VALUES (5,'external');
-- ddl-end --
-- object: public.label_type | type: TABLE --
-- DROP TABLE IF EXISTS public.label_type CASCADE;
CREATE TABLE public.label_type (
type smallint NOT NULL,
description text,
CONSTRAINT label_type_pk PRIMARY KEY (type)
);
-- ddl-end --
ALTER TABLE public.label_type OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.label_type (type,description) VALUES (0,'normal');
INSERT INTO public.label_type (type,description) VALUES (1,'starred');
-- ddl-end --
-- object: public.addressbook_object_kind | type: TABLE --
-- DROP TABLE IF EXISTS public.addressbook_object_kind CASCADE;
CREATE TABLE public.addressbook_object_kind (
kind smallint NOT NULL,
description text,
CONSTRAINT addressbook_object_kind_pk PRIMARY KEY (kind)
);
-- ddl-end --
ALTER TABLE public.addressbook_object_kind OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.addressbook_object_kind (kind,description) VALUES (0,'person');
INSERT INTO public.addressbook_object_kind (kind,description) VALUES (1,'group');
-- ddl-end --
-- object: public.addressbook_object_search_token_type | type: TABLE --
-- DROP TABLE IF EXISTS public.addressbook_object_search_token_type CASCADE;
CREATE TABLE public.addressbook_object_search_token_type (
type smallint NOT NULL,
description text,
CONSTRAINT addressbook_object_search_token_type_pk PRIMARY KEY (type)
);
-- ddl-end --
ALTER TABLE public.addressbook_object_search_token_type OWNER TO "Contacts";
-- ddl-end --
-- object: public.external_source | type: TABLE --
-- DROP TABLE IF EXISTS public.external_source CASCADE;
CREATE TABLE public.external_source(
id_addressbook bigint NOT NULL,
source varchar(32) NOT NULL DEFAULT '',
username varchar(1024) NOT NULL DEFAULT '',
token text NOT NULL DEFAULT '',
addressbook_location varchar(2048) NOT NULL DEFAULT '',
status smallint NOT NULL DEFAULT 0,
modified_time bigint NOT NULL DEFAULT extract(epoch from now())::bigint,
CONSTRAINT external_source_pk PRIMARY KEY (id_addressbook)
);
-- ddl-end --
ALTER TABLE public.external_source OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (0,'full name');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (1,'given name');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (2,'additional name');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (3,'family name');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (4,'title');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (5,'department');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (6,'company');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (7,'email');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (8,'telephone');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (9,'address');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (10,'url');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (11,'date');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (12,'instant_messenger');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (13,'phonetic_given_name');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (14,'phonetic_additional_name');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (15,'phonetic_family_name');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (16,'nickname');
INSERT INTO public.addressbook_object_search_token_type (type,description) VALUES (17,'note');
-- ddl-end --
-- object: public.addressbook_object_metadata_without_photo_vcard_view | type: VIEW --
-- DROP VIEW IF EXISTS public.addressbook_object_metadata_without_photo_vcard_view CASCADE;
CREATE VIEW public.addressbook_object_metadata_without_photo_vcard_view
AS
SELECT
ABO.id, ABO.id_addressbook, ABO.uri, ABO.kind, ABO.md5, ABO.is_hidden, ABO.usage_frequency, ABO.created_time, ABO.modified_time,
M.full_name, M.company, M.department, M.job_title, M.email, M.telephone, M.date, M.birthday
FROM
public.addressbook_object as ABO
INNER JOIN public.addressbook_object_metadata as M ON ABO.id = M.id_addressbook_object;
-- ddl-end --
ALTER VIEW public.addressbook_object_metadata_without_photo_vcard_view OWNER TO "Contacts";
-- ddl-end --
-- object: public.principal_status | type: TABLE --
-- DROP TABLE IF EXISTS public.principal_status CASCADE;
CREATE TABLE public.principal_status (
status smallint NOT NULL,
description text,
CONSTRAINT principal_status_pk PRIMARY KEY (status)
);
-- ddl-end --
ALTER TABLE public.principal_status OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.principal_status (status,description) VALUES (0,'default');
INSERT INTO public.principal_status (status,description) VALUES (1,'already migrated from mailclient');
-- ddl-end --
-- object: public.mailclient_migration | type: TABLE --
-- DROP TABLE IF EXISTS public.mailclient_migration CASCADE;
CREATE TABLE public.mailclient_migration (
id_principal bigint,
id_contacts_pkg bigint NOT NULL,
id_mailclient_pkg bigint NOT NULL,
type smallint NOT NULL
);
-- ddl-end --
ALTER TABLE public.mailclient_migration OWNER TO "Contacts";
-- ddl-end --
-- object: principal_fk | type: CONSTRAINT --
-- ALTER TABLE public.mailclient_migration DROP CONSTRAINT IF EXISTS principal_fk CASCADE;
ALTER TABLE public.mailclient_migration ADD CONSTRAINT principal_fk FOREIGN KEY (id_principal)
REFERENCES public.principal (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_principal_id_contacts_id_mailclient_type_uq | type: CONSTRAINT --
-- ALTER TABLE public.mailclient_migration DROP CONSTRAINT IF EXISTS id_principal_id_contacts_id_mailclient_type_uq CASCADE;
ALTER TABLE public.mailclient_migration ADD CONSTRAINT id_principal_id_contacts_id_mailclient_type_uq UNIQUE (id_principal,id_contacts_pkg,id_mailclient_pkg,type);
-- ddl-end --
-- object: public.mailclient_migration_type | type: TABLE --
-- DROP TABLE IF EXISTS public.mailclient_migration_type CASCADE;
CREATE TABLE public.mailclient_migration_type (
type smallint,
description text
);
-- ddl-end --
ALTER TABLE public.mailclient_migration_type OWNER TO "Contacts";
-- ddl-end --
-- Appended SQL commands --
INSERT INTO public.mailclient_migration_type (type,description) VALUES (0,'addressbook');
INSERT INTO public.mailclient_migration_type (type,description) VALUES (1,'label');
-- ddl-end --
-- object: id_group_fk | type: CONSTRAINT --
-- ALTER TABLE public.principal_many_group_has_many_member DROP CONSTRAINT IF EXISTS id_group_fk CASCADE;
ALTER TABLE public.principal_many_group_has_many_member ADD CONSTRAINT id_group_fk FOREIGN KEY (id_group)
REFERENCES public.principal (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_member_fk | type: CONSTRAINT --
-- ALTER TABLE public.principal_many_group_has_many_member DROP CONSTRAINT IF EXISTS id_member_fk CASCADE;
ALTER TABLE public.principal_many_group_has_many_member ADD CONSTRAINT id_member_fk FOREIGN KEY (id_member)
REFERENCES public.principal (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_group_fk | type: CONSTRAINT --
-- ALTER TABLE public.addressbook_object_many_group_has_many_member DROP CONSTRAINT IF EXISTS id_group_fk CASCADE;
ALTER TABLE public.addressbook_object_many_group_has_many_member ADD CONSTRAINT id_group_fk FOREIGN KEY (id_group)
REFERENCES public.addressbook_object (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_member_fk | type: CONSTRAINT --
-- ALTER TABLE public.addressbook_object_many_group_has_many_member DROP CONSTRAINT IF EXISTS id_member_fk CASCADE;
ALTER TABLE public.addressbook_object_many_group_has_many_member ADD CONSTRAINT id_member_fk FOREIGN KEY (id_member)
REFERENCES public.addressbook_object (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_principal_fk | type: CONSTRAINT --
-- ALTER TABLE public.addressbook_object_revision DROP CONSTRAINT IF EXISTS id_principal_fk CASCADE;
ALTER TABLE public.addressbook_object_revision ADD CONSTRAINT id_principal_fk FOREIGN KEY (id_principal)
REFERENCES public.principal (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_addressbook_fk | type: CONSTRAINT --
-- ALTER TABLE public.addressbook_object_revision DROP CONSTRAINT IF EXISTS id_addressbook_fk CASCADE;
ALTER TABLE public.addressbook_object_revision ADD CONSTRAINT id_addressbook_fk FOREIGN KEY (id_addressbook)
REFERENCES public.addressbook (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_directory_object_fk | type: CONSTRAINT --
-- ALTER TABLE public.id_directory_object_to_id_addressbook_object DROP CONSTRAINT IF EXISTS id_directory_object_fk CASCADE;
ALTER TABLE public.id_directory_object_to_id_addressbook_object ADD CONSTRAINT id_directory_object_fk FOREIGN KEY (id_directory_object)
REFERENCES public.directory_object (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_addressbook_object_fk | type: CONSTRAINT --
-- ALTER TABLE public.id_directory_object_to_id_addressbook_object DROP CONSTRAINT IF EXISTS id_addressbook_object_fk CASCADE;
ALTER TABLE public.id_directory_object_to_id_addressbook_object ADD CONSTRAINT id_addressbook_object_fk FOREIGN KEY (id_addressbook_object)
REFERENCES public.addressbook_object (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_organization_unit_fk | type: CONSTRAINT --
-- ALTER TABLE public.organization_unit_has_many_principal DROP CONSTRAINT IF EXISTS id_organization_unit_fk CASCADE;
ALTER TABLE public.organization_unit_has_many_principal ADD CONSTRAINT id_organization_unit_fk FOREIGN KEY (id_organization_unit)
REFERENCES public.organization_unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_principal_fk | type: CONSTRAINT --
-- ALTER TABLE public.organization_unit_has_many_principal DROP CONSTRAINT IF EXISTS id_principal_fk CASCADE;
ALTER TABLE public.organization_unit_has_many_principal ADD CONSTRAINT id_principal_fk FOREIGN KEY (id_principal)
REFERENCES public.principal (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --
-- object: id_external_source_fk | type: CONSTRAINT --
-- ALTER TABLE public.external_source DROP CONSTRAINT IF EXISTS id_external_source_fk CASCADE;
ALTER TABLE public.external_source ADD CONSTRAINT id_external_source_fk FOREIGN KEY (id_addressbook)
REFERENCES public.addressbook (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
-- ddl-end --