SET check_function_bodies = false;
CREATE SCHEMA test;
SET search_path TO pg_catalog,public,test;
CREATE TABLE public.item(
  id serial NOT NULL,
  id_user integer NOT NULL,
  type int2 NOT NULL,
  CONSTRAINT item_id_primary PRIMARY KEY (id)
);
CREATE TABLE public.album(
  id serial NOT NULL,
  id_user integer NOT NULL,
  name text NOT NULL,
  type int2 NOT NULL,
  shared bool NOT NULL DEFAULT false,
  create_time bigint NOT NULL,
  cover int4 NOT NULL DEFAULT 0,
  sort_by int2 NOT NULL DEFAULT 0,
  sort_direction int2 NOT NULL DEFAULT 0,
  normalized_name text NOT NULL DEFAULT '',
  version bigint NOT NULL,
  passphrase_share text,
  item_count int4,
  CONSTRAINT album_id_primary PRIMARY KEY (id)
);
CREATE TABLE public.share(
  passphrase text NOT NULL,
  privacy_type smallint NOT NULL DEFAULT 0,
  modified_time bigint NOT NULL DEFAULT 0,
  id_user integer NOT NULL,
  expired_time bigint NOT NULL,
  hashed_password text NOT NULL,
  enable bool NOT NULL,
  type smallint NOT NULL,
  CONSTRAINT passphrase_pk PRIMARY KEY (passphrase)
);
CREATE TABLE public.geocoding(
  id serial NOT NULL,
  id_user integer NOT NULL,
  grouping_key text NOT NULL,
  level_1 int4,
  level_2 int4,
  level_3 int4,
  level_4 int4,
  level_5 int4,
  level_6 int4,
  admin_1 int2,
  admin_2 int2,
  admin_3 int2,
  admin_4 int2,
  admin_5 int2,
  admin_6 int2,
  CONSTRAINT geocoding_id_primary PRIMARY KEY (id)
);
CREATE TABLE public.person(
  id serial NOT NULL,
  id_user smallint NOT NULL,
  name text NOT NULL,
  hidden bool NOT NULL DEFAULT false,
  custom_cover bool NOT NULL DEFAULT false,
  cover integer,
  normalized_name text NOT NULL DEFAULT '',
  CONSTRAINT person_id_primary PRIMARY KEY (id)
);
CREATE TABLE public.config(
  key text NOT NULL,
  value text NOT NULL,
  CONSTRAINT config_key_primary PRIMARY KEY (key)
);
CREATE TABLE public.metadata(
  id_unit integer,
  id_user integer NOT NULL,
  description text NOT NULL,
  orientation int2 NOT NULL DEFAULT 1,
  focal_length text NOT NULL,
  iso text NOT NULL,
  exposure_time text NOT NULL,
  aperture text NOT NULL,
  lens text NOT NULL,
  camera text NOT NULL,
  latitude float8,
  longitude float8,
  flash smallint,
  orientation_original smallint NOT NULL DEFAULT 1
);
CREATE TABLE public.condition_album(
  condition json,
  CONSTRAINT condition_album_pk PRIMARY KEY (id)
) INHERITS(public.album)
;
CREATE TABLE public.normal_album(
  cant_migrate_condition json,
  condition json,
  status int2 NOT NULL DEFAULT 0,
  start_time bigint,
  end_time bigint,
  CONSTRAINT normal_album_pk PRIMARY KEY (id)
) INHERITS(public.album)
;
CREATE TABLE public.user_info(
  id serial NOT NULL,
  uid bigint,
  name text NOT NULL,
  config json NOT NULL DEFAULT '{}',
  enable bool NOT NULL DEFAULT false,
  CONSTRAINT user_id_primary PRIMARY KEY (id),
  CONSTRAINT uid_uk UNIQUE (uid)
);
CREATE TABLE public.group_info(
  id serial NOT NULL,
  gid bigint,
  name text NOT NULL,
  enable bool NOT NULL DEFAULT false,
  CONSTRAINT group_id_primary PRIMARY KEY (id),
  CONSTRAINT gid_uk UNIQUE (gid)
);
CREATE TABLE public.person_group(
  id serial NOT NULL,
  weight int4,
  feature bytea NOT NULL,
  id_user integer NOT NULL,
  id_cluster integer,
  CONSTRAINT person_group_id_primary PRIMARY KEY (id)
);
CREATE TABLE public.cluster(
  id serial NOT NULL,
  id_person integer,
  id_user integer NOT NULL,
  CONSTRAINT cluster_id_primary PRIMARY KEY (id)
);
ALTER TABLE public.cluster ADD CONSTRAINT person_fk FOREIGN KEY (id_person)
REFERENCES public.person (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE public.face(
  id serial NOT NULL,
  id_user integer NOT NULL,
  bounding_box json NOT NULL,
  landmark json NOT NULL,
  feature bytea NOT NULL,
  picture oid,
  score integer NOT NULL,
  id_unit integer,
  ref_id_unit integer NOT NULL,
  id_person_group integer,
  id_person integer,
  CONSTRAINT face_id_primary PRIMARY KEY (id)
);
ALTER TABLE public.face ADD CONSTRAINT person_group_fk FOREIGN KEY (id_person_group)
REFERENCES public.person_group (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE public.unit(
  id serial NOT NULL,
  id_user integer NOT NULL,
  type int2 NOT NULL DEFAULT 0,
  item_type int2 NOT NULL DEFAULT 0,
  filename text NOT NULL,
  filesize bigint NOT NULL DEFAULT 0,
  createtime bigint NOT NULL DEFAULT 0,
  mtime bigint NOT NULL DEFAULT 0,
  takentime bigint NOT NULL DEFAULT 0,
  duplicate_hash text NOT NULL,
  cache_key text NOT NULL,
  resolution json NOT NULL,
  index_stage int2 NOT NULL,
  version bigint NOT NULL,
  id_geocoding integer,
  id_item integer NOT NULL,
  mobile_cache_mtime bigint NOT NULL DEFAULT 0,
  reindex_flag int2 NOT NULL DEFAULT 0,
  normalized_filename text NOT NULL DEFAULT '',
  is_major bool NOT NULL,
  id_folder integer NOT NULL,
  CONSTRAINT unit_id_primary PRIMARY KEY (id)
);
ALTER TABLE public.unit ADD CONSTRAINT item_fk FOREIGN KEY (id_item)
REFERENCES public.item (id) MATCH FULL
ON DELETE CASCADE ON UPDATE RESTRICT;
CREATE TABLE public.thumbnail(
  type int2 NOT NULL,
  id_user integer NOT NULL,
  status int2 NOT NULL,
  id_unit integer
);
CREATE TABLE public.folder(
  id serial NOT NULL,
  id_user integer NOT NULL,
  name text NOT NULL,
  parent int4 NOT NULL,
  name_for_sort text NOT NULL DEFAULT '',
  permission text,
  mtime bigint NOT NULL DEFAULT 0,
  passphrase_share text,
  shared bool NOT NULL,
  sort_by int2 NOT NULL DEFAULT 0,
  sort_direction int2 NOT NULL DEFAULT 0,
  permission_parent integer NOT NULL DEFAULT 0,
  CONSTRAINT folder_id_primary PRIMARY KEY (id)
);
ALTER TABLE public.thumbnail ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.metadata ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.metadata ADD CONSTRAINT metadata_uq UNIQUE (id_unit);
ALTER TABLE public.unit ADD CONSTRAINT folder_fk FOREIGN KEY (id_folder)
REFERENCES public.folder (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX thumbnail_id_type_idx ON public.thumbnail
  USING btree
  (
    id_unit ASC NULLS LAST,
    type ASC NULLS LAST
  );
CREATE TABLE public.general_tag(
  id serial NOT NULL,
  id_user integer NOT NULL,
  name text NOT NULL,
  count integer NOT NULL DEFAULT 0,
  normalized_name text NOT NULL DEFAULT '',
  CONSTRAINT general_tag_id_primary PRIMARY KEY (id)
);
CREATE TABLE public.video_additional(
  duration int8 NOT NULL,
  video_info json NOT NULL,
  audio_info json NOT NULL,
  id_unit integer,
  id_user integer NOT NULL
);
CREATE TABLE public.geocoding_info(
  id serial NOT NULL,
  id_user integer NOT NULL,
  lang int2 NOT NULL,
  first_level text NOT NULL,
  second_level text NOT NULL,
  country text NOT NULL,
  id_geocoding integer,
  CONSTRAINT geocoding_info_id_primary PRIMARY KEY (id)
);
ALTER TABLE public.geocoding_info ADD CONSTRAINT geocoding_fk FOREIGN KEY (id_geocoding)
REFERENCES public.geocoding (id) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;
CREATE TABLE public.address(
  id serial NOT NULL,
  lang int2 NOT NULL,
  admin int2 NOT NULL,
  level int4 NOT NULL,
  value text NOT NULL,
  id_unit integer,
  id_user integer NOT NULL,
  CONSTRAINT address_id_primary PRIMARY KEY (id)
);
CREATE TABLE public.version_time(
  version bigserial NOT NULL,
  modified_time bigint NOT NULL,
  CONSTRAINT version_id_primary PRIMARY KEY (version)
);
CREATE INDEX time_idx ON public.version_time
  USING btree
  (
    modified_time ASC NULLS LAST
  );
CREATE TABLE public.many_item_has_many_normal_album(
  sequence float8 NOT NULL DEFAULT 0,
  item_provider_id_user integer,
  id_item integer NOT NULL,
  id_normal_album integer NOT NULL,
  album_id_user integer NOT NULL,
  CONSTRAINT many_item_has_many_normal_album_pk PRIMARY KEY (id_item,id_normal_album)
);
ALTER TABLE public.many_item_has_many_normal_album ADD CONSTRAINT item_fk FOREIGN KEY (id_item)
REFERENCES public.item (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.many_item_has_many_normal_album ADD CONSTRAINT normal_album_fk FOREIGN KEY (id_normal_album)
REFERENCES public.normal_album (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX unit_id_user_takentime_idx ON public.unit
  USING btree
  (
    id_user,
    takentime ASC NULLS LAST
  );
CREATE INDEX create_time_idx ON public.album
  USING btree
  (
    create_time ASC NULLS LAST
  );
CREATE TABLE public.live_additional(
  grouping_key text NOT NULL,
  id_unit integer,
  id_user integer NOT NULL
);
CREATE TABLE public.burst_additional(
  grouping_key text NOT NULL,
  sequence bigint NOT NULL,
  id_unit integer,
  id_user integer NOT NULL
);
CREATE VIEW public.timeline_view
AS
SELECT
 id_item,
 id_user,
 item_type as item_type,
 type as unit_type,
 takentime,
 id as id_unit
FROM unit WHERE is_major = 't';
ALTER TABLE public.address ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE VIEW public.geocoding_album_view
AS
SELECT s.id_geocoding,
    geocoding.id_user,
    s.item_count,
    album_count_table.count AS album_count,
    geocoding.level_1
   FROM geocoding,
    ( SELECT u.id_geocoding,
             u.id_user,
            count(DISTINCT(u.id_item)) AS item_count
           FROM unit u
           WHERE u.is_major = 't'
           GROUP BY u.id_geocoding, u.id_user
         HAVING u.id_geocoding IS NOT NULL) s,
    ( SELECT level_1, id_user, COUNT(*)
           FROM geocoding
          GROUP BY level_1, id_user) album_count_table
  WHERE geocoding.id = s.id_geocoding AND
        geocoding.level_1 = album_count_table.level_1 AND
        geocoding.id_user = s.id_user AND
        s.id_user = album_count_table.id_user;
CREATE VIEW public.geocoding_timeline_view
AS
SELECT
    id_item,
    id_user,
    item_type,
    type as unit_type,
    takentime,
    id as id_unit,
    id_geocoding
FROM unit WHERE is_major = 't';
ALTER TABLE public.face ADD CONSTRAINT person_fk FOREIGN KEY (id_person)
REFERENCES public.person (id) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;
CREATE VIEW public.person_album_view
AS
SELECT
    id_person,
    person.id_user,
    person.name,
    person.hidden,
    person.cover,
    item_count,
    person.normalized_name
FROM person ,(
    SELECT
        face.id_person,
        face.id_user,
        count(distinct(unit.id_item)) as item_count
    FROM face, unit
    WHERE unit.id = face.id_unit AND unit.id_user = face.id_user
    GROUP BY id_person, face.id_user
) SS WHERE SS.id_person = person.id AND SS.id_user = person.id_user;
CREATE VIEW public.person_timeline_view
AS
SELECT
U.id_item,
U.id_user,
U.item_type,
U.type as unit_type,
U.id as id_unit,
AGG_PERSON.persons AS id_person,
U.takentime
FROM unit as U left outer join (
    SELECT unit.id, unit.id_user, array_agg(DISTINCT(F.id_person)) as persons
    FROM unit left outer join face as F on unit.id = F.id_unit AND unit.id_user = F.id_user
    GROUP BY unit.id, unit.id_user
) as AGG_PERSON ON U.id = AGG_PERSON.id AND U.id_user = AGG_PERSON.id_user AND U.is_major = 't';
CREATE TABLE public.administrative(
  id serial NOT NULL,
  value text NOT NULL,
  CONSTRAINT adminstrative_pk PRIMARY KEY (id),
  CONSTRAINT value_uk UNIQUE (value)
);
CREATE TABLE public.index_queue(
  id serial NOT NULL,
  id_user integer NOT NULL,
  id_unit integer NOT NULL,
  type int2 NOT NULL DEFAULT 0,
  status int2 NOT NULL DEFAULT 0,
  task bytea NOT NULL,
  CONSTRAINT index_id_pk PRIMARY KEY (id)
);
CREATE TABLE public.thumb_preview(
  picture oid NOT NULL,
  id_user integer NOT NULL,
  id_unit integer NOT NULL
);
CREATE RULE drop_preview_lo_on_delete AS ON DELETE
  TO public.thumb_preview
  DO ALSO (SELECT lo_unlink(OLD.picture));
CREATE RULE drop_preview_lo_on_update AS ON UPDATE
  TO public.thumb_preview
  WHERE NEW.picture != OLD.picture
  DO ALSO (SELECT lo_unlink(OLD.picture));
CREATE RULE drop_face_lo_on_update AS ON UPDATE
  TO public.face
  WHERE NEW.picture != OLD.picture
  DO ALSO (SELECT lo_unlink(OLD.picture));
CREATE RULE drop_face_lo_on_delete AS ON DELETE
  TO public.face
  DO ALSO (SELECT lo_unlink(OLD.picture));
CREATE INDEX adress_id_unit_lang_idx ON public.address
  USING btree
  (
    id ASC NULLS LAST,
    lang ASC NULLS LAST
  );
CREATE INDEX geocoding_grouping_key_idx ON public.geocoding
  USING btree
  (
    grouping_key ASC NULLS LAST
  );
CREATE INDEX geocoding_level_1_idx ON public.geocoding
  USING btree
  (
    level_1 ASC NULLS LAST
  );
CREATE INDEX geocoding_level_2_idx ON public.geocoding
  USING btree
  (
    level_2 ASC NULLS LAST
  );
CREATE INDEX geocoding_level_3_idx ON public.geocoding
  USING btree
  (
    level_3 ASC NULLS LAST
  );
CREATE INDEX geocoding_level_4_idx ON public.geocoding
  USING btree
  (
    level_4 ASC NULLS LAST
  );
CREATE INDEX geocoding_level_5_idx ON public.geocoding
  USING btree
  (
    level_5 ASC NULLS LAST
  );
CREATE INDEX geocoding_level_6_idx ON public.geocoding
  USING btree
  (
    level_6 ASC NULLS LAST
  );
CREATE INDEX geocoding_info_id_geocoding_lang_idx ON public.geocoding_info
  USING btree
  (
    id_geocoding ASC NULLS LAST,
    lang ASC NULLS LAST
  );
CREATE INDEX unit_id_folder_filename_idx ON public.unit
  USING btree
  (
    id_folder ASC NULLS LAST,
    filename ASC NULLS LAST
  );
CREATE INDEX item_type_idx ON public.item
  USING btree
  (
    type ASC NULLS LAST
  );
CREATE INDEX cluster_id_person_idx ON public.cluster
  USING btree
  (
    id_person ASC NULLS LAST
  );
CREATE TABLE public.many_unit_has_many_general_tag(
  id_unit integer NOT NULL,
  id_general_tag integer NOT NULL,
  id_user integer NOT NULL,
  CONSTRAINT many_unit_has_many_general_tag_pk PRIMARY KEY (id_unit,id_general_tag)
);
ALTER TABLE public.many_unit_has_many_general_tag ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.many_unit_has_many_general_tag ADD CONSTRAINT general_tag_fk FOREIGN KEY (id_general_tag)
REFERENCES public.general_tag (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX unit_id_user_createtime_idx ON public.unit
  USING btree
  (
    id_user,
    createtime ASC NULLS LAST
  );
CREATE INDEX unit_index_stage_idx ON public.unit
  USING btree
  (
    index_stage ASC NULLS LAST
  );
CREATE TABLE public.delete_item(
  id_item integer NOT NULL,
  id_user integer NOT NULL,
  version bigint NOT NULL
);
CREATE TABLE public.delete_album(
  id_normal_album integer NOT NULL,
  id_user integer NOT NULL,
  version bigint NOT NULL
);
CREATE FUNCTION public.update_tag_ref_count_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
     tag_id integer;
     check_id integer;
     ref_count integer;
     general_tag_table text;
      BEGIN
          general_tag_table = TG_TABLE_SCHEMA || '.general_tag';
          IF (TG_OP = 'INSERT') THEN
              tag_id = NEW.id_general_tag;
              EXECUTE 'UPDATE ' || general_tag_table || ' SET count = COALESCE(count, 0) + 1 WHERE id = ' || tag_id;
          ELSIF (TG_OP = 'UPDATE') THEN
               tag_id = NEW.id_general_tag;
               check_id = OLD.id_general_tag;
              EXECUTE 'UPDATE ' || general_tag_table || ' SET count = COALESCE(count, 0) + 1 WHERE id = ' || tag_id;
              EXECUTE 'UPDATE ' || general_tag_table || ' SET count = COALESCE(count, 0) - 1 WHERE id = ' || check_id;
          ELSIF (TG_OP = 'DELETE') THEN
              check_id = OLD.id_general_tag;
              EXECUTE 'UPDATE ' || general_tag_table || ' SET count = COALESCE(count, 0) - 1 WHERE id = ' || check_id;
          END IF;
          IF check_id IS NOT NULL THEN
             EXECUTE 'SELECT count  FROM '|| general_tag_table || ' WHERE id = ' || check_id INTO ref_count;
             IF (ref_count  <=0 ) THEN
              EXECUTE 'DELETE FROM ' || general_tag_table || ' WHERE id = ' || check_id;
              END IF;
          END IF;
          RETURN NULL;
      END;
$$;
CREATE TRIGGER update_tag_ref_trigger
  AFTER INSERT OR DELETE OR UPDATE
  ON public.many_unit_has_many_general_tag
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_tag_ref_count_func();
CREATE FUNCTION public.notification_rotate_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
    count integer;
    ctime bigint;
    notification_table text;
BEGIN
    notification_table = TG_TABLE_SCHEMA || '.notification';
    EXECUTE 'SELECT count(*) FROM ' || notification_table INTO count;
    IF (count > 200) THEN
          EXECUTE 'SELECT createtime FROM ' || notification_table || ' ORDER BY createtime DESC OFFSET 200 LIMIT 1' INTO ctime;
          EXECUTE 'DELETE FROM ' || notification_table || ' WHERE createtime <= ' || ctime;
    END IF;
    RETURN NULL;
END;
$$;
CREATE VIEW public.unit_to_general_tag_view
AS
SELECT
   G.id as id_general_tag, name, R.id_unit, G.id_user
   FROM
   public.general_tag as G
   JOIN public.many_unit_has_many_general_tag as R ON G.id = R.id_general_tag AND G.id_user = R.id_user;
CREATE INDEX index_queue_user_unit_type_uk ON public.index_queue
  USING btree
  (
    id_user ASC NULLS LAST,
    id_unit ASC NULLS LAST,
    type ASC NULLS LAST
  );
CREATE INDEX index_queue_status_id_uk ON public.index_queue
  USING btree
  (
    status ASC NULLS LAST,
    type ASC NULLS LAST,
    id ASC NULLS LAST
  );
CREATE INDEX unit_id_item_id_geocoding_idx ON public.unit
  USING btree
  (
    id_item ASC NULLS LAST,
    id_geocoding ASC NULLS LAST
  );
ALTER TABLE public.video_additional ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.video_additional ADD CONSTRAINT video_additional_uq UNIQUE (id_unit);
ALTER TABLE public.live_additional ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.live_additional ADD CONSTRAINT live_additional_uq UNIQUE (id_unit);
ALTER TABLE public.burst_additional ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.burst_additional ADD CONSTRAINT burst_additional_uq UNIQUE (id_unit);
CREATE VIEW public.video
AS
SELECT
   video_unit.id,
   video_unit.id_user,
   video_unit.item_type,
   video_unit.type,
   video_unit.filename,
   video_unit.filesize,
   video_unit.createtime,
   video_unit.takentime,
   video_unit.mtime,
   video_unit.duplicate_hash,
   video_unit.cache_key,
   video_unit.resolution,
   video_unit.index_stage,
   video_unit.version,
   video_unit.id_item,
   video_unit.id_folder,
   video_unit.id_geocoding,
   video_unit.is_major,
   video_additional.duration,
   video_additional.video_info,
   video_additional.audio_info
FROM
   video_additional,
   (SELECT * FROM unit WHERE type = 1) AS video_unit
WHERE
   video_unit.id = video_additional.id_unit AND
   video_unit.id_user = video_additional.id_user;
CREATE VIEW public.live
AS
SELECT
   live_unit.id,
   live_unit.id_user,
   live_unit.item_type,
   live_unit.type,
   live_unit.filename,
   live_unit.filesize,
   live_unit.createtime,
   live_unit.takentime,
   live_unit.mtime,
   live_unit.duplicate_hash,
   live_unit.cache_key,
   live_unit.resolution,
   live_unit.index_stage,
   live_unit.version,
   live_unit.id_item,
   live_unit.id_folder,
   live_unit.id_geocoding,
   live_unit.is_major,
   live_additional.grouping_key
FROM
   live_additional,
   (SELECT * FROM unit WHERE item_type = 3) AS live_unit
WHERE
   live_unit.id = live_additional.id_unit AND
   live_unit.id_user = live_additional.id_user;
CREATE FUNCTION public.delete_normal_album_after_trigger_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
    next_version bigint;
    delete_album_table text;
    share_table text;
    condition_album_item_table text;
    BEGIN
        delete_album_table = TG_TABLE_SCHEMA || '.delete_album';
        share_table = TG_TABLE_SCHEMA || '.share';
        IF (TG_OP = 'DELETE') THEN
           SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO next_version;
           EXECUTE 'INSERT INTO ' || delete_album_table || ' (id_normal_album, id_user, version)
            VALUES ( '|| OLD.id || ', ' || OLD.id_user || ', ' || next_version || ')';
           IF OLD.passphrase_share IS NOT NULL THEN
               EXECUTE format('DELETE FROM %s WHERE passphrase = %L', share_table, OLD.passphrase_share);
           END IF;
           IF OLD.type = 1 THEN
               condition_album_item_table = TG_TABLE_SCHEMA || '.condition_album_item_' || OLD.id;
               EXECUTE format('DROP TABLE IF EXISTS %s', condition_album_item_table);
           END IF;
        END IF;
        RETURN NULL;
    END;
$$;
CREATE TRIGGER normal_delete_cascade_to_share_trigger
  AFTER DELETE OR UPDATE
  ON public.normal_album
  FOR EACH ROW
  EXECUTE PROCEDURE public.delete_normal_album_after_trigger_func();
CREATE TRIGGER condition_delete_cascade_to_share_trigger
  AFTER DELETE OR UPDATE
  ON public.condition_album
  FOR EACH ROW
  EXECUTE PROCEDURE public.delete_normal_album_after_trigger_func();
CREATE INDEX unit_type_idx ON public.unit
  USING btree
  (
    type ASC NULLS LAST
  );
CREATE FUNCTION public.update_unit_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
    new_version bigint;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
        NEW.version = new_version;
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        IF (NEW.index_stage != OLD.index_stage OR
            NEW.filename != OLD.filename OR
            NEW.id_folder != OLD.id_folder OR
            NEW.takentime != OLD.takentime OR
            NEW.id_geocoding != OLD.id_geocoding OR
            NEW.mtime != OLD.mtime OR
            NEW.cache_key != OLD.cache_key) THEN
           SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
           NEW.version = new_version;
        END IF;
        RETURN NEW;
    END IF;
    RETURN NULL;
END
$$;
CREATE TRIGGER update_unit_version_trigger
  BEFORE INSERT OR UPDATE
  ON public.unit
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_unit_version_func();
CREATE FUNCTION public.get_next_version_func ( schema text)
  RETURNS bigint
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  next_version bigint;
  version_table text;
BEGIN
  version_table = schema || '.version_time';
  EXECUTE 'INSERT INTO ' || version_table || '(modified_time)
    SELECT round(extract(epoch FROM now()) * 1000) RETURNING version ' INTO next_version;
  RETURN next_version;
END
$$;
CREATE FUNCTION public.update_normal_album_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  next_version bigint;
BEGIN
  IF (TG_OP = 'INSERT' or (TG_OP = 'UPDATE' AND (NEW.item_count != OLD.item_count OR NEW.cover != OLD.cover OR NEW.name != OLD.name OR NEW.shared != OLD.shared OR NEW.sort_by != OLD.sort_by OR NEW.sort_direction != OLD.sort_direction OR NEW.passphrase_share != OLD.passphrase_share))) THEN
       SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO next_version;
       NEW.version = next_version;
  END IF;
  RETURN NEW;
END
$$;
CREATE TRIGGER update_normal_album_version_trigger
  BEFORE INSERT OR UPDATE
  ON public.normal_album
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_normal_album_version_func();
CREATE FUNCTION public.face_update_unit_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
  DECLARE
    new_version bigint;
    unit_table text;
  BEGIN
    unit_table = TG_TABLE_SCHEMA || '.unit';
    IF (TG_OP = 'UPDATE' AND NEW.id_person != OLD.id_person) THEN
         SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
         EXECUTE 'UPDATE ' || unit_table || ' SET version = ' || new_version || ' WHERE id = ' || NEW.ref_id_unit;
    END IF;
    RETURN NULL;
  END
$$;
CREATE TRIGGER face_update_unit_version_trigger
  AFTER UPDATE
  ON public.face
  FOR EACH ROW
  EXECUTE PROCEDURE public.face_update_unit_version_func();
CREATE FUNCTION public.metadata_update_unit_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  new_version bigint;
  unit_table text;
BEGIN
  unit_table = TG_TABLE_SCHEMA || '.unit';
  IF (TG_OP = 'UPDATE') THEN
    IF (NEW.description != OLD.description OR NEW.orientation != OLD.orientation) THEN
       SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
       EXECUTE 'UPDATE ' || unit_table || ' SET version = ' || new_version || ' WHERE id = ' || NEW.id_unit;
    END IF;
  END IF;
  RETURN NULL;
END
$$;
CREATE TRIGGER metadata_update_unit_version_trigger
  AFTER UPDATE
  ON public.metadata
  FOR EACH ROW
  EXECUTE PROCEDURE public.metadata_update_unit_version_func();
CREATE FUNCTION public.tag_update_unit_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  new_version bigint;
  unit_table text;
BEGIN
  unit_table = TG_TABLE_SCHEMA || '.unit';
  SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
  IF (TG_OP = 'INSERT') THEN
     EXECUTE 'UPDATE ' || unit_table || ' SET version = ' || new_version || ' WHERE id = ' || NEW.id_unit;
  ELSIF (TG_OP = 'DELETE') THEN
     EXECUTE 'UPDATE ' || unit_table || ' SET version = ' || new_version || ' WHERE id = ' || OLD.id_unit;
  END IF;
  RETURN NULL;
END
$$;
CREATE TRIGGER tag_update_unit_version_trigger
  AFTER INSERT OR DELETE
  ON public.many_unit_has_many_general_tag
  FOR EACH ROW
  EXECUTE PROCEDURE public.tag_update_unit_version_func();
CREATE FUNCTION public.delete_unit_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  count integer;
  new_version bigint;
  item_table text;
  unit_table text;
  id_item integer;
  id_user integer;
BEGIN
  item_table = TG_TABLE_SCHEMA || '.item';
  unit_table = TG_TABLE_SCHEMA || '.unit';
  IF (TG_OP = 'DELETE') THEN
    id_item = OLD.id_item;
    EXECUTE format('SELECT id_user, count(*) FROM %s WHERE id_item = %L GROUP BY id_user', unit_table, id_item) INTO id_user, count;
    SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
    IF (count > 0) THEN
      EXECUTE format('UPDATE %s SET version = %L  WHERE id_item = %L', unit_table, new_version, id_item);
    ELSE
      EXECUTE format('DELETE FROM %s WHERE id = %L', item_table, id_item);
    END IF;
    EXECUTE format('DELETE FROM index_queue WHERE id_user = %L and id_unit = %L', id_user, OLD.id);
  ELSIF (TG_OP = 'UPDATE' AND OLD.reindex_flag = NEW.reindex_flag) THEN
    id_item = OLD.id_item;
    IF (NEW.id_item != OLD.id_item) THEN
      EXECUTE format('SELECT public.unit_update_id_item_func(%L, %L, %L)', TG_TABLE_SCHEMA, id_item, NEW.id_item);
    END IF;
  END IF;
  IF id_item IS NOT NULL THEN
    EXECUTE format('SELECT public.unit_update_normal_album_func(%L, %L)', TG_TABLE_SCHEMA, id_item);
    EXECUTE format('SELECT public.unit_update_condition_album_func(%L, %L)', TG_TABLE_SCHEMA, id_item);
  END IF;
  RETURN NULL;
END
$$;
CREATE TRIGGER update_unit_after_delete_trigger
  AFTER DELETE OR UPDATE
  ON public.unit
  FOR EACH ROW
  EXECUTE PROCEDURE public.delete_unit_version_func();
CREATE TABLE public.search_timeline(
  id_item integer NOT NULL,
  id_user integer NOT NULL,
  item_type int2 NOT NULL DEFAULT 0,
  unit_type int2 NOT NULL,
  takentime bigint NOT NULL DEFAULT 0,
  id_unit integer NOT NULL,
  CONSTRAINT search_timeline_pk PRIMARY KEY (id_item)
);
CREATE INDEX search_timeline_idx ON public.search_timeline
  USING btree
  (
    takentime ASC NULLS LAST
  );
CREATE FUNCTION public.delete_share_cascade_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
  DECLARE
    key text;
    delete_success bool;
  BEGIN
    key = OLD.passphrase;
    SELECT delete_sharing_entry(key) INTO delete_success;
    IF (delete_success = false) THEN
      RAISE EXCEPTION 'delete synoshareing entry failed';
    END IF;
    RETURN NULL;
  END
$$;
CREATE TRIGGER delete_share_cascade_trigger
  AFTER DELETE
  ON public.share
  FOR EACH ROW
  EXECUTE PROCEDURE public.delete_share_cascade_func();
CREATE TABLE public.video_convert(
  duration int8 NOT NULL,
  id_user integer NOT NULL,
  quality text NOT NULL DEFAULT '',
  video_info json NOT NULL DEFAULT '{}',
  audio_info json NOT NULL DEFAULT '{}',
  id_unit integer
);
ALTER TABLE public.video_convert ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX video_convert_id_unit_idx ON public.video_convert
  USING btree
  (
    id_unit ASC NULLS LAST
  );
CREATE FUNCTION public.update_normal_album_cover_func ( schema text,  album_id integer,  old_item_id integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
many_item_has_many_normal_album_table text;
normal_album_table  text;
item_id integer;
BEGIN
  many_item_has_many_normal_album_table = schema || '.many_item_has_many_normal_album';
  normal_album_table = schema || '.normal_album';
  EXECUTE format('SELECT id_item FROM %s WHERE id_normal_album = %L AND id_item != %L', many_item_has_many_normal_album_table, album_id, old_item_id) INTO item_id;
  IF item_id IS NULL THEN
    item_id = 0;
  END IF;
  EXECUTE format('UPDATE %s SET cover = %L WHERE id = %L', normal_album_table, item_id, album_id);
END
$$;
CREATE FUNCTION public.update_condition_album_cover_func ( schema text,  album_id integer,  old_item_id integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
condition_album_table  text;
condition_album_item_table text;
item_id integer;
BEGIN
  condition_album_table = schema || '.condition_album';
  condition_album_item_table = schema || '.condition_album_item_' || album_id;
  EXECUTE format('SELECT id_item FROM %s WHERE id_item != %L', condition_album_item_table, old_item_id) INTO item_id;
  IF item_id IS NULL THEN
    item_id = 0;
  END IF;
  EXECUTE format('UPDATE %s SET cover = %L WHERE id = %L', condition_album_table, item_id, album_id);
END
$$;
CREATE FUNCTION public.increase_album_version_func ( schema text,  album_id integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
    album_table  text;
    new_version integer;
    item_id integer;
BEGIN
    album_table = schema || '.album';
    SELECT public.get_next_version_func(schema) INTO new_version;
    EXECUTE format('UPDATE %s SET version = %L WHERE id = %L', album_table, new_version, album_id);
END
$$;
CREATE FUNCTION public.unit_update_normal_album_func ( schema text,  item_id integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  normal_album_table text;
  unit_table text;
  count integer;
  record RECORD;
BEGIN
  normal_album_table = schema || '.normal_album';
  unit_table = schema || '.unit';
  EXECUTE format('SELECT count(*) FROM %s WHERE id_item = %L', unit_table, item_id) INTO count;
  IF (count > 0) THEN
    FOR record IN EXECUTE format('SELECT * FROM %s WHERE cover = %L', normal_album_table, item_id) LOOP
      EXECUTE format('SELECT public.increase_album_version_func(%L, %s)', schema, record.id);
    END LOOP;
  ELSE
    FOR record IN EXECUTE format('SELECT * FROM %s WHERE cover = %L', normal_album_table, item_id) LOOP
      EXECUTE format('SELECT public.update_normal_album_cover_func(%L, %s, %L)', schema, record.id, item_id);
    END LOOP;
  END IF;
END
$$;
CREATE FUNCTION public.unit_update_condition_album_func ( schema text,  item_id integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  condition_album_table text;
  unit_table text;
  count integer;
  record RECORD;
BEGIN
  condition_album_table = schema || '.condition_album';
  unit_table = schema || '.unit';
  EXECUTE format('SELECT count(*) FROM %s WHERE id_item = %L', unit_table, item_id) INTO count;
  IF (count > 0) THEN
    FOR record IN EXECUTE format('SELECT * FROM %s WHERE cover = %L', condition_album_table, item_id) LOOP
      EXECUTE format('SELECT public.increase_album_version_func(%L, %s)', schema, record.id);
    END LOOP;
  ELSE
    FOR record IN EXECUTE format('SELECT * FROM %s WHERE cover = %L', condition_album_table, item_id) LOOP
      EXECUTE format('SELECT public.update_condition_album_cover_func(%L, %s, %L)', schema, record.id, item_id);
    END LOOP;
  END IF;
END
$$;
CREATE VIEW public.recently_added_timeline_view
AS
SELECT
 id_item,
 id_user,
 item_type,
 type as unit_type,
 createtime/1000 AS createtime,
 id as id_unit
FROM unit WHERE is_major = 't';
CREATE FUNCTION public.delete_sharing_entry ( passphrase text)
  RETURNS bool
  LANGUAGE c
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS '/var/packages/SynologyPhotos/target/usr/lib/libsynofoto-db-trigger.so', 'pg_delete_sharing_entry';
CREATE VIEW public.normal_album_photowall_view
AS
SELECT
  M.id_normal_album,
  M.id_item,
  M.item_provider_id_user,
  U.id_unit,
  U.takentime,
  U.filesize,
  U.normalized_filename,
  M.sequence,
  M.album_id_user AS id_user,
  I.type AS item_type
FROM
  many_item_has_many_normal_album M,
  item I,
  (SELECT
    id_item,
    id AS id_unit,
    takentime,
    filesize,
    normalized_filename
  FROM unit WHERE is_major = 't') AS U
WHERE U.id_item = M.id_item and I.id = M.id_item;
CREATE FUNCTION public.update_face_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  custom_cover bool;
  cover integer;
BEGIN
  IF (OLD.id_person IS NOT NULL) THEN
    EXECUTE format('SELECT cover FROM %I.person WHERE id = %s', TG_TABLE_SCHEMA, OLD.id_person) INTO cover;
    IF (cover = OLD.id) THEN
      PERFORM public.update_person_best_cover(OLD.id_person, TG_TABLE_SCHEMA);
    END IF;
  END IF;
  RETURN NULL;
END;
$$;
CREATE TRIGGER update_face_trigger
  AFTER UPDATE
  ON public.face
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_face_func();
CREATE FUNCTION public.update_person_best_cover ( id_person integer,  schema text)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  cover integer;
BEGIN
  EXECUTE format('SELECT id FROM %I.face WHERE id_person = %s and id_unit != 0 ORDER BY score DESC LIMIT 1',
    schema, id_person) INTO cover;
  IF (cover IS NOT NULL) THEN
    EXECUTE format('UPDATE %I.person SET cover = %s WHERE id = %s', schema, cover, id_person);
  END IF;
END;
$$;
CREATE FUNCTION public.delete_face_trigger_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  face_id integer;
  person_id integer;
  group_id integer;
  cluster_id integer;
  group_weight integer;
  remain_group_count integer;
  remain_cluster_count integer;
  person_group_table text;
  cluster_table text;
  person_table text;
  face_table text;
  cover integer;
  BEGIN
    person_group_table = TG_TABLE_SCHEMA || '.person_group';
    person_table = TG_TABLE_SCHEMA || '.person';
    cluster_table = TG_TABLE_SCHEMA || '.cluster';
    face_table = TG_TABLE_SCHEMA || '.face';
    person_id = OLD.id_person;
    group_id = OLD.id_person_group;
    face_id = OLD.id;
    IF (person_id IS NOT NULL) THEN
      EXECUTE format('SELECT cover FROM %I.person WHERE id = %s', TG_TABLE_SCHEMA, person_id) INTO cover;
      IF (cover IS NULL OR cover = OLD.id) THEN
        PERFORM public.update_person_best_cover(person_id, TG_TABLE_SCHEMA);
      END IF;
    END IF;
    IF (group_id IS NOT NULL) THEN
      EXECUTE 'SELECT COUNT(*) FROM ' || face_table || ' WHERE id_person_group = ' || group_id INTO group_weight;
      IF (group_weight > 0) THEN
        RETURN NULL;
      END IF;
      EXECUTE 'SELECT id_cluster FROM ' || person_group_table || ' WHERE id = ' || group_id INTO cluster_id;
      EXECUTE 'DELETE FROM ' || person_group_table || ' WHERE id = ' || group_id;
      IF (cluster_id IS NOT NULL) THEN
        EXECUTE 'SELECT COUNT(*) FROM ' || person_group_table || ' WHERE id_cluster = ' || cluster_id INTO remain_group_count;
        IF (remain_group_count > 0) THEN
          RETURN NULL;
        END IF;
        EXECUTE 'DELETE FROM ' || cluster_table || ' WHERE id = ' || cluster_id;
      END IF;
    END IF;
    IF (person_id IS NOT NULL) THEN
      EXECUTE 'SELECT COUNT(*) FROM ' || cluster_table || ' WHERE id_person = ' || person_id INTO remain_cluster_count;
      IF (remain_cluster_count > 0) THEN
        RETURN NULL;
      END IF;
      EXECUTE 'DELETE FROM ' || person_table || ' WHERE id = ' || person_id;
    END IF;
    RETURN NULL;
  END;
$$;
CREATE TRIGGER delete_face_trigger
  AFTER DELETE
  ON public.face
  FOR EACH ROW
  EXECUTE PROCEDURE public.delete_face_trigger_func();
CREATE FUNCTION public.get_unit_takentime_by_album ( id_album int4,  schema text)
  RETURNS TABLE ( max bigint,  min bigint)
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  unit_table text;
  many_table text;
BEGIN
    unit_table = schema || '.unit';
    many_table = schema || '.many_item_has_many_normal_album';
    RETURN QUERY EXECUTE 'SELECT MAX(takentime), MIN(takentime) FROM ' || unit_table || ' WHERE id_item IN(
        SELECT id_item FROM '|| many_table || ' WHERE id_normal_album = ' || id_album ||')';
END;
$$;
CREATE VIEW public.person_face_view
AS
SELECT
  face.id,
  face.id_user,
  id_person,
  id_unit,
  score
FROM face, unit
WHERE unit.id = face.id_unit AND unit.id_user = face.id_user;
CREATE FUNCTION public.delete_nonexisting_geocoding_after_delete_unit ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  geocoding_id integer;
  geocoding_table text;
  unit_count integer;
  unit_table text;
BEGIN
  IF (OLD.id_geocoding IS NULL) THEN
     RETURN OLD;
  END IF;
  unit_table = TG_TABLE_SCHEMA || '.unit';
  geocoding_table = TG_TABLE_SCHEMA || '.geocoding';
  geocoding_id = OLD.id_geocoding;
  EXECUTE 'SELECT COUNT(*) FROM ' || unit_table || ' WHERE id_geocoding = ' || geocoding_id INTO unit_count;
  IF (0 = unit_count) THEN
    EXECUTE 'DELETE FROM ' || geocoding_table || ' WHERE id = ' || geocoding_id;
  END IF;
  RETURN OLD;
END;
$$;
CREATE TRIGGER update_geocoding_after_delete_unit_trigger
  AFTER DELETE
  ON public.unit
  FOR EACH ROW
  EXECUTE PROCEDURE public.delete_nonexisting_geocoding_after_delete_unit();
CREATE INDEX unit_id_folder_normalized_filename_idx ON public.unit
  USING btree
  (
    id_folder ASC NULLS LAST,
    normalized_filename ASC NULLS LAST
  );
CREATE TABLE public.team_library_user_permission(
  id_user integer NOT NULL,
  permission smallint NOT NULL DEFAULT 0,
  auto_backup bool NOT NULL DEFAULT false,
  CONSTRAINT team_library_user_permission_pk PRIMARY KEY (id_user)
);
CREATE TABLE public.similar_group(
  id serial NOT NULL,
  id_user integer NOT NULL,
  cover integer NOT NULL,
  item_count integer NOT NULL DEFAULT 0,
  CONSTRAINT similar_gorup_pk PRIMARY KEY (id)
);
CREATE TABLE public.many_similar_group_has_many_item(
  hidden bool NOT NULL DEFAULT false,
  id_user integer NOT NULL,
  id_similar_group integer NOT NULL,
  id_item integer NOT NULL,
  CONSTRAINT many_similar_group_has_many_item_pk PRIMARY KEY (id_similar_group,id_item)
);
ALTER TABLE public.many_similar_group_has_many_item ADD CONSTRAINT similar_group_fk FOREIGN KEY (id_similar_group)
REFERENCES public.similar_group (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.many_similar_group_has_many_item ADD CONSTRAINT item_fk FOREIGN KEY (id_item)
REFERENCES public.item (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX many_similar_group_has_many_item_idx ON public.many_similar_group_has_many_item
  USING btree
  (
    id_similar_group ASC NULLS LAST,
    hidden ASC NULLS LAST
  );
CREATE TABLE public.unit_similar_hash(
  id_unit integer NOT NULL,
  id_user integer NOT NULL,
  perceptual_hash text NOT NULL DEFAULT ''
);
CREATE TABLE test.tables(
  table_schema text NOT NULL,
  table_name text NOT NULL,
  table_type text NOT NULL
);
CREATE TABLE public.delete_condition_album(
  id_condition_album integer NOT NULL,
  id_user integer NOT NULL,
  version bigint NOT NULL
);
CREATE TABLE public.user_flag(
  id_user integer NOT NULL,
  flag text NOT NULL,
  value text
);
CREATE TABLE public.acl_permission(
  fullpath text NOT NULL,
  permission text NOT NULL,
  type smallint NOT NULL,
  id_type integer NOT NULL
);
ALTER TABLE public.person_group ADD CONSTRAINT cluster_fk FOREIGN KEY (id_cluster)
REFERENCES public.cluster (id) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;
CREATE TABLE public.mobile_config(
  uuid text NOT NULL,
  config text NOT NULL
);
CREATE VIEW public.folder_timeline_view
AS
SELECT
u.id_item,
u.id_user,
u.item_type,
u.type as unit_type,
u.id AS id_unit,
u.id_folder AS id_folder,
u.takentime AS takentime,
u.filesize,
u.normalized_filename
FROM
folder f,
unit u
WHERE f.id = u.id_folder AND u.id_user = f.id_user AND u.is_major = 't';
CREATE VIEW public.general_tag_album_view
AS
SELECT
  T.id as id_general_tag, T.name, SS.item_count, T.id_user
FROM
  general_tag as T, (
      SELECT count(DISTINCT(U.id_item)) AS item_count, R.id_general_tag, U.id_user
      FROM many_unit_has_many_general_tag as R, unit as U
      WHERE U.id = R.id_unit AND R.id_user = U.id_user
      GROUP BY R.id_general_tag, U.id_user
    ) SS WHERE T.id = SS.id_general_tag AND T.id_user = SS.id_user;
CREATE VIEW public.general_tag_timeline_view
AS
SELECT
U.id_item,
U.id_user,
U.item_type,
U.type as unit_type,
U.id as id_unit,
AGG_TAG.tags AS id_general_tag,
U.takentime
FROM unit as U left outer join (
    SELECT unit.id, unit.id_user, array_agg(distinct(R.id_general_tag)) as tags
    FROM unit left outer join many_unit_has_many_general_tag as R on unit.id = R.id_unit AND unit.id_user = R.id_user
    GROUP BY unit.id, unit.id_user
) as AGG_TAG ON U.id = AGG_TAG.id AND U.id_user = AGG_TAG.id_user AND U.is_major = 't';
CREATE FUNCTION public.init_user_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  root_id integer;
BEGIN
  SELECT nextval('folder_id_seq') INTO root_id;
  INSERT INTO folder (id, id_user, name, parent, name_for_sort, shared, permission_parent) VALUES (root_id, NEW.id, '/', root_id, '/', false, root_id);
  RETURN NULL;
END
$$;
CREATE FUNCTION public.update_normal_album_time_trigger_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  id_item integer;
  many_table text;
  normal_album_table text;
  max_time bigint;
  min_time bigint;
  record RECORD;
BEGIN
  id_item  = OLD.id_item;
  normal_album_table = TG_TABLE_SCHEMA || '.normal_album';
  many_table = TG_TABLE_SCHEMA || '.many_item_has_many_normal_album';
  FOR record IN EXECUTE format('SELECT * FROM %s WHERE id_item = %L', many_table, id_item) LOOP
    EXECUTE format('SELECT COALESCE(max, 0), COALESCE(min, 0) FROM public.get_unit_takentime_by_album(%L, %L)', record.id_normal_album, TG_TABLE_SCHEMA) INTO max_time, min_time;
    EXECUTE 'UPDATE ' || normal_album_table || ' SET start_time = ' || min_time || ', end_time = ' || max_time || ' WHERE id = ' || record.id_normal_album;
  END LOOP;
  RETURN NULL;
END;
$$;
CREATE TRIGGER init_user_trigger
  AFTER INSERT
  ON public.user_info
  FOR EACH ROW
  EXECUTE PROCEDURE public.init_user_func();
CREATE TRIGGER unit_update_takentime_cascade_to_noraml_album_trigger
  AFTER UPDATE OF takentime
  ON public.unit
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_normal_album_time_trigger_func();
CREATE VIEW public.level_1_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_1 AS level,
    geocoding.admin_1 AS admin
   FROM unit
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_2_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_2 AS level,
    geocoding.admin_2 AS admin
   FROM unit
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_3_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_3 AS level,
    geocoding.admin_3 AS admin
   FROM unit
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_4_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_4 AS level,
    geocoding.admin_4 AS admin
   FROM unit
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_5_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_5 AS level,
    geocoding.admin_5 AS admin
   FROM unit
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_6_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_6 AS level,
    geocoding.admin_6 AS admin
   FROM unit
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_1_person_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_1 AS level,
    geocoding.admin_1 AS admin,
    person.id_person
   FROM (
     SELECT face.id_unit, array_agg(DISTINCT face.id_person) AS id_person
     FROM face
     GROUP BY face.id_unit ) person
  LEFT JOIN unit ON person.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_2_person_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_2 AS level,
    geocoding.admin_2 AS admin,
    person.id_person
   FROM (
     SELECT face.id_unit, array_agg(DISTINCT face.id_person) AS id_person
     FROM face
     GROUP BY face.id_unit ) person
  LEFT JOIN unit ON person.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_3_person_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_3 AS level,
    geocoding.admin_3 AS admin,
    person.id_person
   FROM (
     SELECT face.id_unit, array_agg(DISTINCT face.id_person) AS id_person
     FROM face
     GROUP BY face.id_unit ) person
  LEFT JOIN unit ON person.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_4_person_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_4 AS level,
    geocoding.admin_4 AS admin,
    person.id_person
   FROM (
     SELECT face.id_unit, array_agg(DISTINCT face.id_person) AS id_person
     FROM face
     GROUP BY face.id_unit ) person
  LEFT JOIN unit ON person.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_5_person_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_5 AS level,
    geocoding.admin_5 AS admin,
    person.id_person
   FROM (
     SELECT face.id_unit, array_agg(DISTINCT face.id_person) AS id_person
     FROM face
     GROUP BY face.id_unit ) person
  LEFT JOIN unit ON person.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_6_person_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_6 AS level,
    geocoding.admin_6 AS admin,
    person.id_person
   FROM (
     SELECT face.id_unit, array_agg(DISTINCT face.id_person) AS id_person
     FROM face
     GROUP BY face.id_unit ) person
  LEFT JOIN unit ON person.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_1_general_tag_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_1 AS level,
    geocoding.admin_1 AS admin,
    tag.id_general_tag
   FROM (
     SELECT many_unit_has_many_general_tag.id_unit, array_agg(DISTINCT many_unit_has_many_general_tag.id_general_tag) AS id_general_tag
     FROM many_unit_has_many_general_tag
     GROUP BY many_unit_has_many_general_tag.id_unit ) tag
  LEFT JOIN unit ON tag.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_2_general_tag_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_2 AS level,
    geocoding.admin_2 AS admin,
    tag.id_general_tag
   FROM (
     SELECT many_unit_has_many_general_tag.id_unit, array_agg(DISTINCT many_unit_has_many_general_tag.id_general_tag) AS id_general_tag
     FROM many_unit_has_many_general_tag
     GROUP BY many_unit_has_many_general_tag.id_unit ) tag
  LEFT JOIN unit ON tag.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_3_general_tag_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_3 AS level,
    geocoding.admin_3 AS admin,
    tag.id_general_tag
   FROM (
     SELECT many_unit_has_many_general_tag.id_unit, array_agg(DISTINCT many_unit_has_many_general_tag.id_general_tag) AS id_general_tag
     FROM many_unit_has_many_general_tag
     GROUP BY many_unit_has_many_general_tag.id_unit ) tag
  LEFT JOIN unit ON tag.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_4_general_tag_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_4 AS level,
    geocoding.admin_4 AS admin,
    tag.id_general_tag
   FROM (
     SELECT many_unit_has_many_general_tag.id_unit, array_agg(DISTINCT many_unit_has_many_general_tag.id_general_tag) AS id_general_tag
     FROM many_unit_has_many_general_tag
     GROUP BY many_unit_has_many_general_tag.id_unit ) tag
  LEFT JOIN unit ON tag.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_5_general_tag_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_5 AS level,
    geocoding.admin_5 AS admin,
    tag.id_general_tag
   FROM (
     SELECT many_unit_has_many_general_tag.id_unit, array_agg(DISTINCT many_unit_has_many_general_tag.id_general_tag) AS id_general_tag
     FROM many_unit_has_many_general_tag
     GROUP BY many_unit_has_many_general_tag.id_unit ) tag
  LEFT JOIN unit ON tag.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_6_general_tag_timeline_view
AS
SELECT unit.id_item,
    unit.id_user,
    unit.item_type,
    unit.type AS unit_type,
    unit.takentime,
    unit.id AS id_unit,
    geocoding.level_6 AS level,
    geocoding.admin_6 AS admin,
    tag.id_general_tag
   FROM (
     SELECT many_unit_has_many_general_tag.id_unit, array_agg(DISTINCT many_unit_has_many_general_tag.id_general_tag) AS id_general_tag
     FROM many_unit_has_many_general_tag
     GROUP BY many_unit_has_many_general_tag.id_unit ) tag
  LEFT JOIN unit ON tag.id_unit = unit.id
  LEFT JOIN geocoding ON unit.id_geocoding = geocoding.id AND unit.id_user = geocoding.id_user
  WHERE unit.is_major = true AND unit.id_geocoding IS NOT NULL;
CREATE VIEW public.level_1_search_timeline_view
AS
SELECT U.id_item,
    U.id_user,
    U.item_type,
    U.type AS unit_type,
    U.takentime,
    U.id_unit,
    geocoding.level_1 AS level,
    geocoding.admin_1 AS admin
   FROM (
      SELECT
        unit.id_user,
        unit.id_geocoding,
        unit.id_item,
        unit.item_type,
        unit.type,
        unit.takentime,
        unit.id AS id_unit,
        unit.is_major
      FROM search_timeline s, unit
      WHERE s.id_unit = unit.id ) U
  LEFT JOIN geocoding ON U.id_geocoding = geocoding.id AND U.id_user = geocoding.id_user
  WHERE U.is_major = true AND U.id_geocoding IS NOT NULL;
CREATE VIEW public.level_2_search_timeline_view
AS
SELECT U.id_item,
    U.id_user,
    U.item_type,
    U.type AS unit_type,
    U.takentime,
    U.id_unit,
    geocoding.level_2 AS level,
    geocoding.admin_2 AS admin
   FROM (
      SELECT
        unit.id_user,
        unit.id_geocoding,
        unit.id_item,
        unit.item_type,
        unit.type,
        unit.takentime,
        unit.id AS id_unit,
        unit.is_major
      FROM search_timeline s, unit
      WHERE s.id_unit = unit.id ) U
  LEFT JOIN geocoding ON U.id_geocoding = geocoding.id AND U.id_user = geocoding.id_user
  WHERE U.is_major = true AND U.id_geocoding IS NOT NULL;
CREATE VIEW public.level_3_search_timeline_view
AS
SELECT U.id_item,
    U.id_user,
    U.item_type,
    U.type AS unit_type,
    U.takentime,
    U.id_unit,
    geocoding.level_3 AS level,
    geocoding.admin_3 AS admin
   FROM (
      SELECT
        unit.id_user,
        unit.id_geocoding,
        unit.id_item,
        unit.item_type,
        unit.type,
        unit.takentime,
        unit.id AS id_unit,
        unit.is_major
      FROM search_timeline s, unit
      WHERE s.id_unit = unit.id ) U
  LEFT JOIN geocoding ON U.id_geocoding = geocoding.id AND U.id_user = geocoding.id_user
  WHERE U.is_major = true AND U.id_geocoding IS NOT NULL;
CREATE VIEW public.level_4_search_timeline_view
AS
SELECT U.id_item,
    U.id_user,
    U.item_type,
    U.type AS unit_type,
    U.takentime,
    U.id_unit,
    geocoding.level_4 AS level,
    geocoding.admin_4 AS admin
   FROM (
      SELECT
        unit.id_user,
        unit.id_geocoding,
        unit.id_item,
        unit.item_type,
        unit.type,
        unit.takentime,
        unit.id AS id_unit,
        unit.is_major
      FROM search_timeline s, unit
      WHERE s.id_unit = unit.id ) U
  LEFT JOIN geocoding ON U.id_geocoding = geocoding.id AND U.id_user = geocoding.id_user
  WHERE U.is_major = true AND U.id_geocoding IS NOT NULL;
CREATE VIEW public.level_5_search_timeline_view
AS
SELECT U.id_item,
    U.id_user,
    U.item_type,
    U.type AS unit_type,
    U.takentime,
    U.id_unit,
    geocoding.level_5 AS level,
    geocoding.admin_5 AS admin
   FROM (
      SELECT
        unit.id_user,
        unit.id_geocoding,
        unit.id_item,
        unit.item_type,
        unit.type,
        unit.takentime,
        unit.id AS id_unit,
        unit.is_major
      FROM search_timeline s, unit
      WHERE s.id_unit = unit.id ) U
  LEFT JOIN geocoding ON U.id_geocoding = geocoding.id AND U.id_user = geocoding.id_user
  WHERE U.is_major = true AND U.id_geocoding IS NOT NULL;
CREATE VIEW public.level_6_search_timeline_view
AS
SELECT U.id_item,
    U.id_user,
    U.item_type,
    U.type AS unit_type,
    U.takentime,
    U.id_unit,
    geocoding.level_6 AS level,
    geocoding.admin_6 AS admin
   FROM (
      SELECT
        unit.id_user,
        unit.id_geocoding,
        unit.id_item,
        unit.item_type,
        unit.type,
        unit.takentime,
        unit.id AS id_unit,
        unit.is_major
      FROM search_timeline s, unit
      WHERE s.id_unit = unit.id ) U
  LEFT JOIN geocoding ON U.id_geocoding = geocoding.id AND U.id_user = geocoding.id_user
  WHERE U.is_major = true AND U.id_geocoding IS NOT NULL;
CREATE VIEW public.share_album_view
AS
SELECT album.id,
    album.id_user,
    album.name,
    album.type,
    album.shared,
    album.create_time,
    album.cover,
    album.sort_by,
    album.sort_direction,
    album.passphrase_share,
    album.version,
    album.item_count,
    share.modified_time,
    album.normalized_name
   FROM album, share
  WHERE album.passphrase_share = share.passphrase;
CREATE FUNCTION public.item_type_update_unit_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  new_version bigint;
  unit_table text;
BEGIN
  unit_table = TG_TABLE_SCHEMA || '.unit';
  IF (TG_OP = 'UPDATE') THEN
    SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
    EXECUTE 'UPDATE ' || unit_table || ' SET version = ' || new_version || ' WHERE id_item = ' || NEW.id;
  END IF;
  RETURN NULL;
END
$$;
CREATE FUNCTION public.insert_item_to_update_normal_album_func ( id_album int4)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  many_table text;
  normal_album_table text;
  max_time bigint;
  min_time bigint;
  count integer;
BEGIN
  normal_album_table = 'public.normal_album';
  many_table = 'public.many_item_has_many_normal_album';
  EXECUTE 'SELECT count(*) FROM ' || many_table || ' WHERE id_normal_album = ' || id_album INTO count;
  EXECUTE format('SELECT COALESCE(max, 0), COALESCE(min, 0) FROM public.get_unit_takentime_by_album(%L, %L)', id_album, 'public') INTO max_time, min_time;
  EXECUTE 'UPDATE ' || normal_album_table || ' SET start_time = ' || min_time || ', end_time = ' || max_time || ', item_count = ' || count || ' WHERE id = ' || id_album;
END;
$$;
CREATE FUNCTION public.delete_expired_version_time_data_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  count bigint;
  version_time text;
BEGIN
  version_time = TG_TABLE_SCHEMA || '.version_time';
  EXECUTE format('SELECT count(*) FROM %s', version_time) INTO count;
  IF (count > 110000) THEN
        EXECUTE format('DELETE FROM %s WHERE version IN (SELECT version FROM %s order by version limit %L)', version_time, version_time, 10000);
  END IF;
    RETURN NEW;
END
$$;
CREATE FUNCTION public.unit_update_id_item_func ( schema text,  item_id integer,  new_item_id integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  many_item_has_many_normal_album_table text;
  many_similar_group_has_many_item_table text;
  album_array bigint[];
  album_id bigint;
  similar_group_array bigint[];
  similar_group bigint;
  record RECORD;
BEGIN
  many_item_has_many_normal_album_table = schema || '.many_item_has_many_normal_album';
  many_similar_group_has_many_item_table = schema || '.many_similar_group_has_many_item';
  EXECUTE format('SELECT array_agg(id_normal_album) FROM %s WHERE id_item = %L', many_item_has_many_normal_album_table, item_id) INTO album_array;
  IF (album_array IS NOT NULL) THEN
    FOREACH album_id IN ARRAY album_array LOOP
      EXECUTE format('SELECT * FROM %s WHERE id_normal_album = %L AND id_item = %L', many_item_has_many_normal_album_table, album_id, new_item_id) INTO record;
      IF (record IS NULL) THEN
        EXECUTE format('UPDATE %s SET id_item = %L WHERE id_normal_album = %L AND id_item = %L', many_item_has_many_normal_album_table, new_item_id, album_id, item_id);
      END IF;
    END LOOP;
  END IF;
  EXECUTE format('SELECT array_agg(id_similar_group) FROM %s WHERE id_item = %L', many_similar_group_has_many_item_table, item_id) INTO similar_group_array;
  IF (similar_group_array IS NOT NULL) THEN
    FOREACH similar_group IN ARRAY similar_group_array LOOP
      EXECUTE format('SELECT * FROM %s WHERE id_similar_group = %L AND id_item = %L', many_similar_group_has_many_item_table, similar_group, new_item_id) INTO record;
      IF (record IS NULL) THEN
        EXECUTE format('UPDATE %s SET id_item = %L WHERE id_similar_group = %L AND id_item = %L', many_similar_group_has_many_item_table, new_item_id, similar_group, item_id);
      END IF;
    END LOOP;
  END IF;
END
$$;
CREATE FUNCTION public.delete_item_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  delete_item_table text;
  new_version bigint;
  id_user integer;
BEGIN
  delete_item_table = TG_TABLE_SCHEMA || '.delete_item';
  SELECT id from user_info where id = OLD.id_user INTO id_user;
  if (id_user IS NOT NULL) THEN
    SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
    EXECUTE format('INSERT INTO %s (id_item, id_user, version) VALUES (%L, %L, %L)', delete_item_table, OLD.id, OLD.id_user, new_version);
  END IF;
  RETURN NULL;
END
$$;
CREATE FUNCTION public.update_similar_info_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  similar_group_table text;
  many_similar_group_has_many_item_table text;
  cover text;
  current_cover bigint;
  item_count bigint;
  similar_group_id bigint;
  need_update_cover bool;
BEGIN
  similar_group_table = TG_TABLE_SCHEMA || '.similar_group';
  many_similar_group_has_many_item_table = TG_TABLE_SCHEMA || '.many_similar_group_has_many_item';
  need_update_cover = false;
  IF (TG_OP = 'DELETE') THEN
    similar_group_id = OLD.id_similar_group;
  ELSE
    similar_group_id = NEW.id_similar_group;
  END IF;
  EXECUTE format('SELECT count(*) FROM %s WHERE id_similar_group = %L', many_similar_group_has_many_item_table, similar_group_id) INTO item_count;
  EXECUTE format('SELECT cover FROM %s WHERE id = %L', similar_group_table, similar_group_id) INTO current_cover;
  IF (TG_OP = 'INSERT' AND current_cover IS NULL) THEN
    need_update_cover = true;
  ELSIF (TG_OP = 'UPDATE' AND current_cover = NEW.id_item AND NEW.hidden = 't') THEN
    need_update_cover = true;
  ELSIF (TG_OP = 'DELETE' AND current_cover = OLD.id_item) THEN
    need_update_cover = true;
  END IF;
  IF (item_count IS NULL) THEN
    EXECUTE format('DELETE FROM %s WHERE id = %L', similar_group_table, similar_group_id);
    RETURN NULL;
  END IF;
  IF (need_update_cover OR current_cover = 0) THEN
    EXECUTE format('SELECT public.update_similar_group_cover(%L, %s)', TG_TABLE_SCHEMA, similar_group_id);
  END IF;
  EXECUTE format('SELECT public.update_similar_group_count(%L, %s)', TG_TABLE_SCHEMA, similar_group_id);
  RETURN NULL;
END
$$;
CREATE FUNCTION public.delete_item_to_update_normal_album_trigger_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
    album_id integer;
    item_id integer;
    album_start_time bigint;
    album_end_time bigint;
    unit_time bigint;
    unit_table text;
    normal_album_table text;
    album_takentime_max bigint;
    album_takentime_min bigint;
BEGIN
  unit_table = TG_TABLE_SCHEMA || '.unit';
  normal_album_table = TG_TABLE_SCHEMA || '.normal_album';
  album_id = OLD.id_normal_album;
  item_id  = OLD.id_item;
  EXECUTE 'UPDATE ' || normal_album_table || ' SET item_count = COALESCE(item_count, 0) - 1 WHERE id =' || album_id;
  EXECUTE 'SELECT takentime FROM ' || unit_table || ' WHERE id_item = ' || item_id INTO unit_time;
  EXECUTE 'SELECT COALESCE(start_time, 0), COALESCE(end_time, 0) FROM ' || normal_album_table ||
          ' WHERE id =' || album_id INTO album_start_time, album_end_time;
  IF (unit_time IS NULL OR unit_time = album_start_time OR unit_time = album_end_time) THEN
    EXECUTE format('SELECT COALESCE(max, 0), COALESCE(min, 0) FROM public.get_unit_takentime_by_album(%L, %L)', album_id, TG_TABLE_SCHEMA) INTO album_takentime_max, album_takentime_min;
    EXECUTE 'UPDATE ' || normal_album_table || ' SET start_time = ' || album_takentime_min || ', end_time = ' || album_takentime_max || ' WHERE id = ' || album_id;
  END IF;
  RETURN NULL;
END
$$;
CREATE TRIGGER delete_item_trigger
  AFTER DELETE
  ON public.item
  FOR EACH ROW
  EXECUTE PROCEDURE public.delete_item_func();
CREATE TRIGGER delete_item_to_update_normal_album_trigger_func
  AFTER DELETE
  ON public.many_item_has_many_normal_album
  FOR EACH ROW
  EXECUTE PROCEDURE public.delete_item_to_update_normal_album_trigger_func();
CREATE FUNCTION public.update_similar_group_count ( schema text,  similar_group_id integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  similar_group_table text;
  many_similar_group_has_many_item_table text;
  unit_table text;
  visable_count bigint;
BEGIN
  similar_group_table = schema || '.similar_group';
  many_similar_group_has_many_item_table = schema || '.many_similar_group_has_many_item';
  unit_table = schema || '.unit';
  EXECUTE format('SELECT COUNT(DISTINCT(id_item)) from %s WHERE id_item in (SELECT id_item FROM %s WHERE id_similar_group = %L AND hidden = false ORDER BY id_item)', unit_table, many_similar_group_has_many_item_table, similar_group_id) INTO visable_count;
  IF (visable_count IS NULL) THEN
    visable_count = 0;
  END IF;
  EXECUTE format('UPDATE %s SET item_count = %L WHERE id = %L', similar_group_table, visable_count, similar_group_id);
END
$$;
CREATE FUNCTION public.update_similar_group_cover ( schema text,  similar_group_id integer)
  RETURNS void
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  similar_group_table text;
  many_similar_group_has_many_item_table text;
  cover text;
BEGIN
  similar_group_table = schema || '.similar_group';
  many_similar_group_has_many_item_table = schema || '.many_similar_group_has_many_item';
  EXECUTE format('SELECT id_item from unit WHERE id_item in (SELECT id_item FROM %s WHERE id_similar_group = %L AND hidden = false ORDER BY id_item) limit 1', many_similar_group_has_many_item_table, similar_group_id) INTO cover;
  IF (cover IS NULL) THEN
    cover = 0;
  END IF;
  EXECUTE format('UPDATE %s SET cover = %L WHERE id = %L', similar_group_table, cover, similar_group_id);
END
$$;
CREATE TRIGGER update_similar_group_trigger
  AFTER INSERT OR DELETE OR UPDATE
  ON public.many_similar_group_has_many_item
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_similar_info_func();
CREATE TABLE public.aperture(
  id serial NOT NULL,
  name text NOT NULL,
  normalized_name text NOT NULL,
  CONSTRAINT aperture_pk PRIMARY KEY (id),
  CONSTRAINT aperture_uq UNIQUE (name)
);
CREATE TABLE public.camera(
  id serial NOT NULL,
  name text NOT NULL,
  normalized_name text NOT NULL,
  CONSTRAINT camera_pk PRIMARY KEY (id),
  CONSTRAINT camera_uq UNIQUE (name)
);
CREATE TABLE public.exposure_time(
  id serial NOT NULL,
  name text NOT NULL,
  normalized_name text NOT NULL,
  CONSTRAINT exposure_time_pk PRIMARY KEY (id),
  CONSTRAINT exposure_time_uq UNIQUE (name)
);
CREATE TABLE public.focal_length(
  id serial NOT NULL,
  name text NOT NULL,
  normalized_name text NOT NULL,
  CONSTRAINT focal_length_pk PRIMARY KEY (id),
  CONSTRAINT focal_length_uq UNIQUE (name)
);
CREATE TABLE public.iso(
  id serial NOT NULL,
  name text NOT NULL,
  normalized_name text NOT NULL,
  CONSTRAINT iso_pk PRIMARY KEY (id),
  CONSTRAINT iso_uq UNIQUE (name)
);
CREATE TABLE public.lens(
  id serial NOT NULL,
  name text NOT NULL,
  normalized_name text NOT NULL,
  CONSTRAINT lens_pk PRIMARY KEY (id),
  CONSTRAINT lens_qu UNIQUE (name)
);
CREATE TABLE public.filter(
  id_unit integer NOT NULL,
  id_filter integer NOT NULL,
  filter_type text NOT NULL,
  id_user integer NOT NULL,
  CONSTRAINT filter_uq UNIQUE (id_unit,id_filter,filter_type,id_user)
);
CREATE INDEX filter_idx ON public.filter
  USING btree
  (
    id_user,
    filter_type,
    id_filter
  );
CREATE TABLE public.share_permission(
  id serial NOT NULL,
  id_user integer NOT NULL,
  permission int2 NOT NULL,
  target_type smallint NOT NULL,
  target_id int4 NOT NULL,
  passphrase_share text NOT NULL,
  CONSTRAINT share_permission_pk PRIMARY KEY (id)
);
CREATE VIEW public.share_with_member_view
AS
SELECT share_permission.id,
    share_permission.permission,
    share_permission.target_type,
    share_permission.target_id,
    share.id_user,
    share.passphrase,
    share.type,
    share.expired_time,
    share.modified_time
   FROM share_permission, share
  WHERE share_permission.passphrase_share = share.passphrase AND share.enable = true AND (share.id_user IN (SELECT user_info.id FROM user_info WHERE user_info.enable = 't'));
ALTER TABLE public.share_permission ADD CONSTRAINT share_fk FOREIGN KEY (passphrase_share)
REFERENCES public.share (passphrase) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
CREATE INDEX id_user_idx ON public.share_permission
  USING btree
  (
    id_user
  );
CREATE INDEX id_user_target_type_target_id_idx ON public.share_permission
  USING btree
  (
    id_user,
    target_type ASC NULLS LAST,
    target_id ASC NULLS LAST
  );
ALTER TABLE public.share_permission ADD CONSTRAINT passpharase_target_type_target_id_unique UNIQUE (passphrase_share,target_type,target_id);
ALTER TABLE public.album ADD CONSTRAINT share_fk FOREIGN KEY (passphrase_share)
REFERENCES public.share (passphrase) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE public.album ADD CONSTRAINT album_uq UNIQUE (passphrase_share);
ALTER TABLE public.folder ADD CONSTRAINT share_fk FOREIGN KEY (passphrase_share)
REFERENCES public.share (passphrase) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE public.folder ADD CONSTRAINT folder_uq UNIQUE (passphrase_share);
CREATE TABLE public.team_library_group_permission(
  id_group integer NOT NULL,
  permission smallint NOT NULL DEFAULT 0,
  auto_backup bool NOT NULL DEFAULT false,
  CONSTRAINT team_library_group_permission_pk PRIMARY KEY (id_group)
);
CREATE INDEX metadata_id_unit_idx ON public.metadata
  USING btree
  (
    id_unit
  );
CREATE INDEX address_id_unit_idx ON public.address
  USING btree
  (
    id_unit ASC NULLS LAST
  );
CREATE INDEX many_similar_group_has_many_item_id_item_idx ON public.many_similar_group_has_many_item
  USING btree
  (
    id_item
  );
CREATE INDEX delete_album_version_idx ON public.delete_album
  USING btree
  (
    version
  );
CREATE INDEX delete_item_version_idx ON public.delete_item
  USING btree
  (
    version
  );
CREATE INDEX unit_id_user_idx ON public.unit
  USING btree
  (
    id_user
  );
CREATE INDEX face_id_user_idx ON public.face
  USING btree
  (
    id_user
  );
CREATE INDEX address_id_user_lang_id_unit_idx ON public.address
  USING btree
  (
    id_user,
    lang ASC NULLS LAST,
    id_unit ASC NULLS LAST
  );
CREATE TABLE public.takentime(
  id serial NOT NULL,
  takentime_day text NOT NULL,
  takentime_month text NOT NULL,
  takentime bigint NOT NULL DEFAULT 0,
  CONSTRAINT takentime_pk PRIMARY KEY (id),
  CONSTRAINT takentime_uq UNIQUE (takentime_day)
);
CREATE INDEX takentime_day_idx ON public.takentime
  USING btree
  (
    takentime_day
  );
CREATE INDEX takentime_month_idx ON public.takentime
  USING btree
  (
    id
  );
CREATE INDEX takentime_takentime_idx ON public.takentime
  USING btree
  (
    takentime ASC NULLS LAST
  );
CREATE FUNCTION public.share_permission_update_album_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  new_version bigint;
  album_table text;
  passphrase text;
BEGIN
  album_table = TG_TABLE_SCHEMA || '.album';
  IF (TG_OP = 'INSERT' OR (TG_OP = 'UPDATE' AND (NEW.permission != OLD.permission))) THEN
    passphrase = NEW.passphrase_share;
  ELSEIF (TG_OP = 'DELETE') THEN
    passphrase = OLD.passphrase_share;
  END IF;
  if passphrase IS NOT NULL THEN
    SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
    EXECUTE format('UPDATE %s SET version = %L WHERE passphrase_share = %L', album_table, new_version, passphrase);
  END IF;
  RETURN NULL;
END
$$;
CREATE FUNCTION public.share_update_album_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  new_version bigint;
  album_table text;
BEGIN
  album_table = TG_TABLE_SCHEMA || '.album';
  IF (TG_OP = 'UPDATE') THEN
    IF (NEW.privacy_type != OLD.privacy_type OR NEW.modified_time != OLD.modified_time OR NEW.expired_time != OLD.expired_time OR NEW.hashed_password != OLD.hashed_password OR NEW.enable != OLD.enable) THEN
      SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
      EXECUTE format('UPDATE %s SET version = %L WHERE passphrase_share = %L', album_table, new_version, NEW.passphrase);
    END IF;
  END IF;
  RETURN NULL;
END
$$;
CREATE TRIGGER share_update_album_version_trigger
  AFTER UPDATE
  ON public.share
  FOR EACH ROW
  EXECUTE PROCEDURE public.share_update_album_version_func();
CREATE TRIGGER share_permission_update_album_version_trigger
  AFTER INSERT OR DELETE OR UPDATE
  ON public.share_permission
  FOR EACH ROW
  EXECUTE PROCEDURE public.share_permission_update_album_version_func();
CREATE FUNCTION public.update_condition_album_version_func ()
  RETURNS trigger
  LANGUAGE plpgsql
  VOLATILE
  CALLED ON NULL INPUT
  SECURITY INVOKER
  COST 1
  AS $$
DECLARE
  new_version bigint;
BEGIN
  IF (TG_OP = 'INSERT' or
      (TG_OP = 'UPDATE' AND
        (
          NEW.condition::text != OLD.condition::text
          OR NEW.item_count != OLD.item_count
          OR NEW.cover != OLD.cover
          OR NEW.name != OLD.name
          OR NEW.shared != OLD.shared
          OR NEW.sort_by != OLD.sort_by
          OR NEW.sort_direction != OLD.sort_direction
          OR NEW.passphrase_share != OLD.passphrase_share
        )
      )
     ) THEN
    SELECT public.get_next_version_func(TG_TABLE_SCHEMA) INTO new_version;
    NEW.version = new_version;
  END IF;
  RETURN NEW;
END
$$;
CREATE TRIGGER update_condition_album_version_func
  BEFORE INSERT OR UPDATE
  ON public.condition_album
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_condition_album_version_func();
CREATE INDEX album_id_user_idx ON public.album
  USING btree
  (
    id_user ASC NULLS LAST
  );
CREATE INDEX normal_album_id_user_idx ON public.normal_album
  USING btree
  (
    id_user ASC NULLS LAST
  );
CREATE INDEX normal_album_id_user_status_idx ON public.normal_album
  USING btree
  (
    id_user ASC NULLS LAST,
    status ASC NULLS LAST
  );
CREATE INDEX condition_album_id_user_idx ON public.condition_album
  USING btree
  (
    id_user ASC NULLS LAST
  );
CREATE TABLE public.background_task(
  id serial NOT NULL,
  uid bigint NOT NULL,
  id_dsm_task text NOT NULL,
  operation int2 NOT NULL,
  status int2 NOT NULL,
  created_time bigint NOT NULL,
  modified_time bigint NOT NULL,
  total int8 NOT NULL,
  completion int8 NOT NULL,
  CONSTRAINT id PRIMARY KEY (id),
  CONSTRAINT id_dsm_task_unique UNIQUE (id_dsm_task)
);
CREATE TABLE public.file_operation_task(
  id_task integer NOT NULL,
  id integer[],
  team_prefix text,
  remove_src bool,
  target_folder_id integer,
  policy int2,
  CONSTRAINT pk_id_task PRIMARY KEY (id_task)
);
CREATE INDEX id_person_id_unit_score_idx ON public.face
  USING btree
  (
    id_person ASC NULLS LAST,
    id_unit ASC NULLS LAST,
    score ASC NULLS LAST
  );
CREATE TABLE public.check_album_task(
  id serial NOT NULL,
  id_user integer NOT NULL DEFAULT 0,
  id_group integer NOT NULL DEFAULT 0,
  id_folder integer NOT NULL DEFAULT 0,
  status smallint NOT NULL DEFAULT 0,
  CONSTRAINT check_album_task_pk PRIMARY KEY (id)
);
CREATE INDEX check_album_task_status_user_group_folder_idx ON public.check_album_task
  USING btree
  (
    status,
    id_user ASC NULLS LAST,
    id_group ASC NULLS LAST,
    id_folder ASC NULLS LAST
  );
ALTER TABLE public.item ADD CONSTRAINT user_id_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.album ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.share ADD CONSTRAINT share_id_user_info_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.geocoding ADD CONSTRAINT level_1_fk FOREIGN KEY (level_1)
REFERENCES public.administrative (id) MATCH FULL
ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE public.geocoding ADD CONSTRAINT level_2_fk FOREIGN KEY (level_2)
REFERENCES public.administrative (id) MATCH FULL
ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE public.geocoding ADD CONSTRAINT level_3_fk FOREIGN KEY (level_3)
REFERENCES public.administrative (id) MATCH FULL
ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE public.geocoding ADD CONSTRAINT level_4_fk FOREIGN KEY (level_4)
REFERENCES public.administrative (id) MATCH FULL
ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE public.geocoding ADD CONSTRAINT level_5_fk FOREIGN KEY (level_5)
REFERENCES public.administrative (id) MATCH FULL
ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE public.geocoding ADD CONSTRAINT level_6_fk FOREIGN KEY (level_6)
REFERENCES public.administrative (id) MATCH FULL
ON DELETE SET NULL ON UPDATE RESTRICT;
ALTER TABLE public.geocoding ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.person ADD CONSTRAINT cover_id_face FOREIGN KEY (cover)
REFERENCES public.face (id) MATCH FULL
ON DELETE SET NULL ON UPDATE RESTRICT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE public.person ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.metadata ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.condition_album ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.normal_album ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.person_group ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.cluster ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.face ADD CONSTRAINT person_ref_id_unit_fk FOREIGN KEY (ref_id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.face ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.unit ADD CONSTRAINT unit_id_geocoding_fk FOREIGN KEY (id_geocoding)
REFERENCES public.geocoding (id) MATCH FULL
ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE public.unit ADD CONSTRAINT user_id_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.thumbnail ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.folder ADD CONSTRAINT folder_parent_fk FOREIGN KEY (parent)
REFERENCES public.folder (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.folder ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.video_additional ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.geocoding_info ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.address ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.address ADD CONSTRAINT level_fk FOREIGN KEY (level)
REFERENCES public.administrative (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.many_item_has_many_normal_album ADD CONSTRAINT item_provider_id_user_fk FOREIGN KEY (item_provider_id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.many_item_has_many_normal_album ADD CONSTRAINT album_id_user_fk FOREIGN KEY (album_id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.live_additional ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.burst_additional ADD CONSTRAINT user_id_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE public.thumb_preview ADD CONSTRAINT id_unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH SIMPLE
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.thumb_preview ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.delete_item ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.delete_album ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.video_convert ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.team_library_user_permission ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.similar_group ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.many_similar_group_has_many_item ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.unit_similar_hash ADD CONSTRAINT unit_similar_hash_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.unit_similar_hash ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.delete_condition_album ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.user_flag ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.filter ADD CONSTRAINT unit_fk FOREIGN KEY (id_unit)
REFERENCES public.unit (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.filter ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.share_permission ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user)
REFERENCES public.user_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.team_library_group_permission ADD CONSTRAINT id_group_fk FOREIGN KEY (id_group)
REFERENCES public.group_info (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.file_operation_task ADD CONSTRAINT id_task_fk FOREIGN KEY (id_task)
REFERENCES public.background_task (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;