File: /volume1/@appstore/SynologyPhotos/etc/sql/function.sql
CREATE OR REPLACE 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('DELETE FROM %s WHERE id_item = %L', condition_album_item_table, old_item_id);
EXECUTE format('SELECT id_item FROM %s LIMIT 1', condition_album_item_table) INTO item_id;
IF item_id IS NULL THEN
item_id = 0;
END IF;
EXECUTE format('UPDATE %s SET cover = %L, need_update_item = true WHERE id = %L', condition_album_table, item_id, album_id);
END
$$;
CREATE OR REPLACE FUNCTION public.decrease_person_album_count_for_delete_bg_task(task_id integer, user_id integer)
RETURNS VOID AS
$$
DECLARE
temp_table_name TEXT;
query TEXT;
BEGIN
temp_table_name := 'temp_table_for_delete_task_' || task_id;
query := 'UPDATE person_item_count
SET item_count = person_item_count.item_count - subquery.item_count
FROM (
SELECT id_person, id_user, COUNT(DISTINCT(id_item)) AS item_count
FROM person_face_view
WHERE id_user = ' || user_id || ' AND id_item IN (' || 'SELECT id FROM ' || quote_ident(temp_table_name) || ')
GROUP BY id_person, id_user
) AS subquery
WHERE person_item_count.id_person = subquery.id_person;';
EXECUTE query;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.read_only_table()
RETURNS TRIGGER
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
RAISE EXCEPTION 'Table is read-only';
END;
$$;
CREATE OR REPLACE FUNCTION public.delete_index_task_after_delete_unit ()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
BEGIN
DELETE FROM index_queue WHERE id_unit = OLD.id;
RETURN NULL;
END;
$$;
CREATE OR REPLACE FUNCTION public.get_next_version_func (schema text, id_user integer)
RETURNS bigint
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
next_version bigint;
version_table text;
id_user_bucket integer;
BEGIN
id_user_bucket := CASE WHEN id_user = 0 THEN 0 ELSE (id_user - 1) % 13 + 1 END;
version_table = schema || '.version_time_' || id_user_bucket;
EXECUTE 'INSERT INTO ' || version_table || '(modified_time)
SELECT round(extract(epoch FROM now()) * 1000) RETURNING version ' INTO next_version;
RETURN next_version;
END
$$;
CREATE OR REPLACE FUNCTION public.level_concept_timeline(start_time bigint, end_time bigint, concept_id integer, user_id integer, level_param integer)
RETURNS TABLE (
level integer,
admin smallint,
id_unit integer,
item_count bigint,
id_item integer
) AS $$
BEGIN
RETURN QUERY
SELECT
CASE
WHEN level_param = 1 THEN g.level_1
WHEN level_param = 2 THEN g.level_2
WHEN level_param = 3 THEN g.level_3
WHEN level_param = 4 THEN g.level_4
WHEN level_param = 5 THEN g.level_5
WHEN level_param = 6 THEN g.level_6
ELSE NULL
END AS level,
CASE
WHEN level_param = 1 THEN g.admin_1
WHEN level_param = 2 THEN g.admin_2
WHEN level_param = 3 THEN g.admin_3
WHEN level_param = 4 THEN g.admin_4
WHEN level_param = 5 THEN g.admin_5
WHEN level_param = 6 THEN g.admin_6
ELSE NULL
END AS admin,
MIN(u.id) AS id_unit,
COUNT(DISTINCT u.id_item) AS item_count,
MIN(u.id_item) AS id_item
FROM
public.unit u
JOIN
many_unit_has_many_concept r ON u.id = r.id_unit
JOIN
public.geocoding g ON u.id_geocoding = g.id
WHERE
u.takentime BETWEEN start_time AND end_time
AND r.id_concept = concept_id
AND u.id_user = user_id
AND u.is_major = true
GROUP BY
level, admin
ORDER BY
item_count DESC NULLS LAST,
id_item ASC NULLS LAST
LIMIT 4;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.update_one_filter_has_many_unit_is_major_func()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
one_filter_has_many_unit_table text;
BEGIN
one_filter_has_many_unit_table = TG_TABLE_SCHEMA || '.one_filter_has_many_unit';
EXECUTE format('UPDATE %s SET is_major = %L WHERE id_unit = %L AND id_user = %L', one_filter_has_many_unit_table, NEW.is_major, OLD.id, OLD.id_user);
RETURN NULL;
END;
$$;
CREATE OR REPLACE 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 OR REPLACE FUNCTION public.update_similar_timeline_after_item_update_func()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE public.similar_timeline
SET
id_similar_group = NEW.id_similar_group
WHERE id_item = NEW.id;
IF (NEW.id_similar_group IS NULL) THEN
UPDATE public.similar_timeline
SET
similar_group_item_count = NULL
WHERE id_item = NEW.id;
END IF;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION public.increase_concept_album_count_by_item_id(item_id integer, user_id integer)
RETURNS VOID AS
$$
BEGIN
UPDATE concept_album_additional
SET item_count = item_count + subquery.count
FROM (
SELECT id_concept, id_user, COUNT(*) AS count
FROM many_unit_has_many_concept m
JOIN unit ON unit.id = m.id_unit
WHERE m.id_user = user_id AND unit.is_major AND unit.id_item = item_id
GROUP BY id_concept
) AS subquery
WHERE concept_album_additional.id_concept = subquery.id_concept AND concept_album_additional.id_user = user_id;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE 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;
IF (count > 0) THEN
SELECT public.get_next_version_func(TG_TABLE_SCHEMA, id_user) INTO new_version;
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);
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);
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;
IF (NEW.cache_key != OLD.cache_key) 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;
END IF;
RETURN NULL;
END
$$;
CREATE OR REPLACE FUNCTION public.create_production_index_mapping_func(production_tables TEXT[])
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
table_name TEXT;
BEGIN
CREATE TABLE IF NOT EXISTS production_table_index_mapping (
tb_name TEXT,
index_name TEXT,
index_def TEXT
);
TRUNCATE production_table_index_mapping;
FOR table_name IN SELECT unnest(production_tables) LOOP
INSERT INTO production_table_index_mapping (tb_name, index_name, index_def)
SELECT tablename, indexname,
split_part(indexdef, 'USING ', 2)
FROM pg_indexes
WHERE tablename = table_name;
END LOOP;
END
$$;
CREATE OR REPLACE FUNCTION public.insert_similar_hash_missing_units_func( user_id integer )
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO similar_hash (id_unit, id_user, takentime, is_major, id_item, type)
SELECT unit.id, unit.id_user, unit.takentime, unit.is_major, unit.id_item, unit.type
FROM unit LEFT JOIN similar_hash
ON unit.id = similar_hash.id_unit
WHERE similar_hash.id_unit IS NULL and unit.id_user = user_id;
END;
$$;
CREATE OR REPLACE FUNCTION public.create_version_time_by_id_user_trigger_func ()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
id_user integer := NEW.id;
new_version_time_table text;
new_version_time_table_name text;
trigger_name text;
pk_name text;
idx_name text;
new_sequence_name text;
id_user_bucket integer;
BEGIN
id_user_bucket := CASE WHEN id_user = 0 THEN 0 ELSE (id_user - 1) % 13 + 1 END;
new_version_time_table = 'public.version_time_' || id_user_bucket;
new_version_time_table_name = 'version_time_' || id_user_bucket;
pk_name = 'version_id_' || id_user_bucket || '_primary';
idx_name = 'version_time_' || id_user_bucket || '_idx';
new_sequence_name = 'public.version_time_' || id_user_bucket || '_version_seq';
trigger_name = 'delete_expired_version_time_' || id_user_bucket || '_data_trigger';
-- check if the sequence and table already exists
RAISE NOTICE 'Checking if sequence % and table % exist', new_sequence_name, new_version_time_table;
IF EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = new_version_time_table_name
) THEN
RETURN NEW;
END IF;
EXECUTE format('CREATE SEQUENCE %s START WITH 1', new_sequence_name);
EXECUTE format('CREATE TABLE %s (version BIGINT NOT NULL DEFAULT nextval(''%s''), modified_time BIGINT NOT NULL, CONSTRAINT %s PRIMARY KEY (version))',
new_version_time_table, new_sequence_name, pk_name);
EXECUTE format('CREATE INDEX %s ON %s USING btree (modified_time ASC NULLS LAST)', idx_name, new_version_time_table);
EXECUTE format('CREATE TRIGGER %s BEFORE INSERT ON %s FOR EACH ROW EXECUTE PROCEDURE public.delete_expired_version_time_data_func()',
trigger_name, new_version_time_table);
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION public.drop_temp_table_for_delete_task(task_id integer)
RETURNS VOID AS
$$
DECLARE
temp_table_name TEXT;
BEGIN
temp_table_name := 'temp_table_for_delete_task_' || task_id;
EXECUTE 'DROP TABLE ' || temp_table_name;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.remove_person_migration_tables_id_sequence_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
ALTER TABLE IF EXISTS face_migration
ALTER COLUMN id DROP DEFAULT;
ALTER TABLE IF EXISTS person_group_migration
ALTER COLUMN id DROP DEFAULT;
ALTER TABLE IF EXISTS cluster_migration
ALTER COLUMN id DROP DEFAULT;
ALTER TABLE IF EXISTS person_migration
ALTER COLUMN id DROP DEFAULT;
END;
$$;
CREATE OR REPLACE 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 OR REPLACE FUNCTION public.create_face_migration_manual_map_table_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
CREATE TABLE IF NOT EXISTS face_migration_manual_map
AS
SELECT
id,
id_user,
false AS visited FROM face_migration WHERE is_manual = true;
IF NOT EXISTS (
SELECT
constraint_name
FROM
information_schema.table_constraints
WHERE
table_name = 'face_migration_manual_map' AND constraint_type = 'PRIMARY KEY'
) THEN
ALTER TABLE face_migration_manual_map ADD PRIMARY KEY (id);
END IF;
END
$$;
CREATE OR REPLACE FUNCTION public.display_shared_status_order (id_user integer, owner_id integer, enable boolean)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
display_order integer;
BEGIN
-- shared, order 0
-- shared_with_me, order 1
-- not shared, order 2
IF (enable AND id_user = owner_id) THEN
display_order = 0;
ELSIF (enable AND id_user != owner_id) THEN
display_order = 1;
ELSE
display_order = 2;
END IF;
RETURN display_order;
END
$$;
CREATE OR REPLACE FUNCTION public.set_person_table_read_only_top_down_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
PERFORM public.drop_read_only_trigger_func('face');
PERFORM public.drop_read_only_trigger_func('person_group');
PERFORM public.drop_read_only_trigger_func('cluster');
PERFORM public.drop_read_only_trigger_func('person');
PERFORM public.create_read_only_trigger_func('face');
PERFORM public.create_read_only_trigger_func('person_group');
PERFORM public.create_read_only_trigger_func('cluster');
PERFORM public.create_read_only_trigger_func('person');
END
$$;
CREATE OR REPLACE FUNCTION public.analyze_temp_table_for_delete_task(task_id integer)
RETURNS VOID AS
$$
DECLARE
temp_table_name TEXT;
BEGIN
temp_table_name := 'temp_table_for_delete_task_' || task_id;
EXECUTE 'ANALYZE ' || temp_table_name;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.recalculate_person_album_item_count(user_id integer)
RETURNS VOID AS
$$
BEGIN
INSERT INTO
person_item_count (id_person, id_user, item_count)
SELECT id_person, id_user, count(DISTINCT id_item) AS item_count
FROM person_face_view
WHERE id_user = user_id AND is_major = true AND id_person IS NOT NULL
GROUP BY id_person, id_user
ON CONFLICT(id_user, id_person) DO UPDATE
SET
(id_person, id_user, item_count) = (EXCLUDED.id_person, EXCLUDED.id_user, EXCLUDED.item_count);
DELETE FROM person_item_count WHERE item_count = 0 AND id_user = user_id;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.delete_similar_timeline_by_unit_id(target_id_unit integer, target_id_item integer)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
DELETE FROM public.similar_timeline
WHERE id_unit = target_id_unit
AND id_item = target_id_item
AND (
EXISTS (
SELECT 1
FROM unit
WHERE id = target_id_unit
AND is_major IS FALSE
)
OR EXISTS (
SELECT 1
FROM item
WHERE id = target_id_item
AND is_similar_top_pick IS FALSE
)
);
END;
$$;
CREATE OR REPLACE FUNCTION public.level_general_tag_timeline(start_time bigint, end_time bigint, general_tag_id integer, user_id integer, level_param integer)
RETURNS TABLE (
level integer,
admin smallint,
id_unit integer,
item_count bigint,
id_item integer
) AS $$
BEGIN
RETURN QUERY
SELECT
CASE
WHEN level_param = 1 THEN g.level_1
WHEN level_param = 2 THEN g.level_2
WHEN level_param = 3 THEN g.level_3
WHEN level_param = 4 THEN g.level_4
WHEN level_param = 5 THEN g.level_5
WHEN level_param = 6 THEN g.level_6
ELSE NULL
END AS level,
CASE
WHEN level_param = 1 THEN g.admin_1
WHEN level_param = 2 THEN g.admin_2
WHEN level_param = 3 THEN g.admin_3
WHEN level_param = 4 THEN g.admin_4
WHEN level_param = 5 THEN g.admin_5
WHEN level_param = 6 THEN g.admin_6
ELSE NULL
END AS admin,
MIN(u.id) AS id_unit,
COUNT(DISTINCT u.id_item) AS item_count,
MIN(u.id_item) AS id_item
FROM
public.unit u
JOIN
many_unit_has_many_general_tag r ON u.id = r.id_unit
JOIN
public.geocoding g ON u.id_geocoding = g.id
WHERE
u.takentime BETWEEN start_time AND end_time
AND r.id_general_tag = general_tag_id
AND u.id_user = user_id
AND u.is_major = true
GROUP BY
level, admin
ORDER BY
item_count DESC NULLS LAST,
id_item ASC NULLS LAST
LIMIT 4;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE 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 OR REPLACE FUNCTION public.drop_read_only_trigger_func(table_name text)
RETURNS VOID
LANGUAGE plpgsql
SECURITY INVOKER
COST 1
AS $$
BEGIN
EXECUTE format('DROP TRIGGER IF EXISTS read_only_trigger ON %I CASCADE', table_name);
END;
$$;
CREATE OR REPLACE FUNCTION public.set_person_item_count_fk_refer_to_production_tables_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
TRUNCATE person_item_count;
ALTER TABLE person_item_count
DROP CONSTRAINT IF EXISTS person_item_count_person_fk,
ADD CONSTRAINT person_item_count_person_fk FOREIGN KEY (id_person) REFERENCES person(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE;
END
$$;
CREATE OR REPLACE FUNCTION public.update_filter_after_many_unit_has_many_concept_delete_func()
RETURNS TRIGGER AS
$$
BEGIN
DELETE FROM public.filter
WHERE id_unit = OLD.id_unit AND id_user = OLD.id_user AND filter_type = 'concept';
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE 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 OR REPLACE 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 OR REPLACE FUNCTION public.get_json_value(json_data JSON, key TEXT)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE AS $$
BEGIN
RETURN json_data ->> key;
END
$$;
CREATE OR REPLACE FUNCTION public.concept_album_list(user_id integer)
RETURNS TABLE(
id_concept integer,
stem text,
parent integer[],
display_threshold smallint,
sort_index integer,
item_count bigint,
default_cover_id_unit integer[],
custom_cover_id_unit integer,
visibility_status smallint
) AS $$ BEGIN RETURN QUERY
WITH main AS(
SELECT concept.id AS id_concept,
(
SELECT caa.custom_cover_id_unit
FROM concept_album_additional caa
WHERE concept.id = caa.id_concept
AND caa.id_user = user_id
) as custom_cover_unit_id,
(
SELECT caa.item_count
FROM concept_album_additional caa
WHERE concept.id = caa.id_concept
AND caa.id_user = user_id
) as item_count,
ARRAY(
(
SELECT m.id_unit
FROM many_unit_has_many_concept m
JOIN unit ON unit.id = m.id_unit
WHERE m.id_user = user_id
AND concept.id = m.id_concept
AND unit.is_major = true
ORDER BY m.confidence
LIMIT 5
)
) as default_cover_id_unit,
(
SELECT caa.visibility_status
FROM concept_album_additional caa
WHERE concept.id = caa.id_concept
AND caa.id_user = user_id
) as visibility_status
FROM concept
WHERE concept.hidden = false
ORDER BY concept.sort_index
)
SELECT main.id_concept,
concept.stem,
concept.parent,
concept.display_threshold,
concept.sort_index,
main.item_count,
main.default_cover_id_unit,
CASE
WHEN (
EXISTS(
SELECT 1
FROM many_unit_has_many_concept m
WHERE m.id_unit = main.custom_cover_unit_id
AND m.id_concept = main.id_concept
)
) THEN main.custom_cover_unit_id
ELSE NULL
END as custom_cover_id_unit,
main.visibility_status
FROM main
JOIN concept on concept.id = main.id_concept;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.increase_concept_album_additional()
RETURNS TRIGGER AS
$$
BEGIN
IF EXISTS (SELECT id_concept, id_user FROM concept_album_additional WHERE id_user = NEW.id_user AND id_concept = NEW.id_concept) THEN
UPDATE concept_album_additional SET item_count = item_count + 1
WHERE
(SELECT is_major FROM unit WHERE unit.id = NEW.id_unit)
AND concept_album_additional.id_user = NEW.id_user
AND concept_album_additional.id_concept = NEW.id_concept;
ELSIF (SELECT is_major FROM unit WHERE id = NEW.id_unit) THEN
INSERT INTO concept_album_additional (id_user, id_concept, item_count) VALUES (NEW.id_user, NEW.id_concept, 1);
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.update_condition_album_time_trigger_func()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
unit_time bigint;
condition_album_table text;
max_time bigint;
min_time bigint;
record RECORD;
BEGIN
unit_time = OLD.takentime;
condition_album_table = TG_TABLE_SCHEMA || '.condition_album';
FOR record IN EXECUTE format('SELECT * FROM %s WHERE start_time = %L OR end_time = %L', condition_album_table, unit_time, unit_time) LOOP
EXECUTE format('SELECT max, min FROM public.get_unit_takentime_by_condition_album(%L)', record.id) INTO max_time, min_time;
EXECUTE 'UPDATE ' || condition_album_table || COALESCE(' SET start_time = ' || min_time, ' SET start_time = NULL') || COALESCE(', end_time = ' || max_time, ', end_time = NULL') || ' WHERE id = ' || record.id;
END LOOP;
RETURN NULL;
END;
$$;
CREATE OR REPLACE 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 max, min 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 || COALESCE(' SET start_time = ' || min_time, ' SET start_time = NULL') || COALESCE(', end_time = ' || max_time, ', end_time = NULL') || ' WHERE id = ' || record.id_normal_album;
END LOOP;
RETURN NULL;
END;
$$;
CREATE OR REPLACE 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, NEW.id_user) INTO new_version;
EXECUTE 'UPDATE ' || unit_table || ' SET version = ' || new_version || ' WHERE id = ' || NEW.ref_id_unit;
END IF;
RETURN NULL;
END
$$;
CREATE OR REPLACE FUNCTION public.recalculate_concept_album_additional(user_id integer)
RETURNS VOID AS
$$
BEGIN
UPDATE concept_album_additional set item_count = 0 WHERE id_user = user_id;
INSERT INTO
concept_album_additional (id_concept, item_count, id_user)
SELECT
id_concept, COUNT(*) as item_count, user_id as id_user
FROM
many_unit_has_many_concept m
JOIN unit on unit.id = m.id_unit
WHERE
m.id_user = user_id
AND unit.is_major
GROUP BY id_concept, m.id_user
ON CONFLICT(id_user, id_concept) DO UPDATE
SET
(id_concept, item_count, id_user) = (EXCLUDED.id_concept, EXCLUDED.item_count, EXCLUDED.id_user);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION update_similar_group_top_pick_func( group_id integer )
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
new_top_pick integer;
BEGIN
new_top_pick := (
SELECT i.id
FROM item i
JOIN unit u ON i.id = u.id_item
WHERE i.id_similar_group = group_id
ORDER BY (u.resolution->>'height')::integer * (u.resolution->>'width')::integer DESC, u.filesize DESC, u.takentime DESC
LIMIT 1
);
UPDATE similar_group
SET
top_pick = new_top_pick,
update_at = round(extract(epoch FROM now()) * 1000)
WHERE
id = group_id;
UPDATE item
SET
is_similar_top_pick = true
WHERE
id = new_top_pick;
END;
$$;
CREATE OR REPLACE FUNCTION public.level_search_timeline(start_time bigint, end_time bigint, _ integer, user_id integer, level_param integer)
RETURNS TABLE (
level integer,
admin smallint,
id_unit integer,
item_count bigint,
id_item integer
) AS $$
BEGIN
RETURN QUERY
SELECT
CASE
WHEN level_param = 1 THEN g.level_1
WHEN level_param = 2 THEN g.level_2
WHEN level_param = 3 THEN g.level_3
WHEN level_param = 4 THEN g.level_4
WHEN level_param = 5 THEN g.level_5
WHEN level_param = 6 THEN g.level_6
ELSE NULL
END AS level,
CASE
WHEN level_param = 1 THEN g.admin_1
WHEN level_param = 2 THEN g.admin_2
WHEN level_param = 3 THEN g.admin_3
WHEN level_param = 4 THEN g.admin_4
WHEN level_param = 5 THEN g.admin_5
WHEN level_param = 6 THEN g.admin_6
ELSE NULL
END AS admin,
MIN(u.id) AS id_unit,
COUNT(DISTINCT u.id_item) AS item_count,
MIN(u.id_item) AS id_item
FROM
public.unit u
JOIN
search_timeline s ON u.id = s.id_unit
JOIN
public.geocoding g ON u.id_geocoding = g.id
WHERE
u.takentime BETWEEN start_time AND end_time
AND u.id_user = user_id
AND u.is_major = true
GROUP BY
level, admin
ORDER BY
item_count DESC NULLS LAST,
id_item ASC NULLS LAST
LIMIT 4;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.define_person_album_view_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
CREATE OR REPLACE VIEW public.person_album_view
AS
SELECT person_item_count.id_person,
person.id_user,
person.name,
person.hidden,
person.cover,
person_item_count.item_count,
person.normalized_name
FROM person,
person_item_count
WHERE person_item_count.id_person = person.id AND person_item_count.id_user = person.id_user;
END
$$;
CREATE OR REPLACE FUNCTION public.create_version_related_table_trigger_func()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
-- take care, version_time table should be created before user_event_history table
PERFORM public.create_version_time_by_id_user_func(NEW.id);
PERFORM public.create_user_event_history_table_by_id_user_func(NEW.id);
RETURN NEW;
END
$$;
CREATE OR REPLACE 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 max, min FROM public.get_unit_takentime_by_album(%L, %L)', id_album, 'public') INTO max_time, min_time;
EXECUTE 'UPDATE ' || normal_album_table || COALESCE(' SET start_time = ' || min_time, ' SET start_time = NULL') || COALESCE(', end_time = ' || max_time, ', end_time = NULL') || ', item_count = ' || count || ' WHERE id = ' || id_album;
END;
$$;
CREATE OR REPLACE FUNCTION public.set_person_migration_tables_func()
RETURNS void
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
production_tables CONSTANT TEXT[] := ARRAY['face', 'person_group', 'cluster', 'person'];
table_name TEXT;
r RECORD;
production_index_name TEXT;
BEGIN
FOR table_name IN SELECT unnest(production_tables) LOOP
EXECUTE format('ALTER TABLE %s_migration RENAME TO %s', table_name, table_name);
EXECUTE format('CREATE SEQUENCE %s_id_seq OWNED BY %I.id', table_name, table_name);
EXECUTE format('SELECT setval(%L, max(id)) FROM %I', table_name || '_id_seq', table_name);
EXECUTE format('ALTER TABLE %I ALTER COLUMN id SET DEFAULT nextval(%L)', table_name, table_name || '_id_seq');
-- collect all index_def fist and drop duplicate indexes
DECLARE
idx_seen TEXT[] := ARRAY[]::TEXT[];
BEGIN
FOR r IN SELECT indexname AS index_name, split_part(indexdef, 'USING ', 2) AS index_def FROM pg_indexes WHERE tablename = table_name LOOP
IF r.index_def = ANY(idx_seen) THEN
-- found existed, drop index
EXECUTE format('DROP INDEX IF EXISTS %I', r.index_name);
ELSE
-- not found, rename to production index name
production_index_name := (SELECT index_name FROM production_table_index_mapping WHERE tb_name = table_name AND index_def = r.index_def LIMIT 1);
EXECUTE format('ALTER INDEX %I RENAME TO %s', r.index_name, production_index_name);
idx_seen := array_append(idx_seen, r.index_def);
END IF;
END LOOP;
END;
END LOOP;
DROP TABLE production_table_index_mapping;
CREATE TRIGGER delete_face_trigger
AFTER DELETE
ON public.face
FOR EACH ROW
EXECUTE PROCEDURE public.delete_face_trigger_func();
CREATE TRIGGER update_face_trigger
AFTER UPDATE
ON public.face
FOR EACH ROW
EXECUTE PROCEDURE public.update_face_func();
DROP RULE IF EXISTS drop_face_lo_on_update ON public.face CASCADE;
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));
DROP RULE IF EXISTS drop_face_lo_on_delete ON public.face CASCADE;
CREATE RULE drop_face_lo_on_delete AS ON DELETE
TO public.face
DO ALSO (SELECT lo_unlink(OLD.picture));
DROP TABLE IF EXISTS face_migration_manual_map;
END
$$;
CREATE OR REPLACE 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 || '.' || TG_TABLE_NAME;
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 OR REPLACE FUNCTION public.create_person_migration_migration_tables_func()
RETURNS void
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
production_tables CONSTANT TEXT[] := ARRAY['face', 'person_group', 'cluster', 'person'];
table_name TEXT;
BEGIN
FOR table_name IN SELECT unnest(production_tables) LOOP
EXECUTE format('CREATE TABLE IF NOT EXISTS %s_migration (LIKE %s INCLUDING ALL)', table_name, table_name);
EXECUTE format('TRUNCATE %s_migration CASCADE', table_name);
END LOOP;
PERFORM public.redefine_migration_constraint_func(production_tables);
PERFORM public.create_production_index_mapping_func(production_tables);
DROP TRIGGER IF EXISTS face_update_unit_version_trigger ON public.face_migration CASCADE;
CREATE TRIGGER face_update_unit_version_trigger
AFTER UPDATE
ON public.face_migration
FOR EACH ROW
EXECUTE PROCEDURE public.face_update_unit_version_func();
END
$$;
CREATE OR REPLACE FUNCTION public.insert_item_to_update_condition_album_func(id_album integer)
RETURNS void
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
item_table text;
condition_album_table text;
max_time bigint;
min_time bigint;
count integer;
BEGIN
condition_album_table = 'public.condition_album';
item_table = 'public.condition_album_item_' || id_album;
EXECUTE 'SELECT count(*) FROM ' || item_table INTO count;
EXECUTE format('SELECT max, min FROM public.get_unit_takentime_by_condition_album(%L)', id_album) INTO max_time, min_time;
EXECUTE 'UPDATE ' || condition_album_table || COALESCE(' SET start_time = ' || min_time, ' SET start_time = NULL') || COALESCE(', end_time = ' || max_time, ', end_time = NULL') || ', item_count = ' || count || ' WHERE id = ' || id_album;
END;
$$;
CREATE OR REPLACE 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, NEW.id_user) 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 OR REPLACE FUNCTION public.redefine_migration_constraint_func(production_tables TEXT[])
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
table_name TEXT;
r RECORD;
referenced_table_name TEXT;
BEGIN
CREATE TEMP TABLE _migration_table_fk_def (
tb_name TEXT,
fk_name TEXT,
fk_def TEXT
);
-- change the definition of foreign key constraint to *_migration table
FOR table_name IN SELECT unnest(production_tables) LOOP
-- insert table name, fk name and fk defination into _migration_table_fk_def
INSERT INTO _migration_table_fk_def (tb_name, fk_name, fk_def)
SELECT CAST(conrelid::regclass AS text) AS tb_name,
conname AS fk_name,
pg_get_constraintdef(c.oid) AS fk_def
FROM pg_constraint c JOIN pg_namespace n ON n.oid = c.connamespace
WHERE CAST(conrelid::regclass AS text) = table_name AND contype = 'f';
-- if referenced table is in production_tables, rename it to `referenced_table_name`_migration
FOR r IN SELECT * FROM _migration_table_fk_def WHERE tb_name = table_name LOOP
-- return `REFERENCES` and `referenced_table_name` from fk_def, something like: "REFERENCES person_group"
referenced_table_name := (SELECT substring(r.fk_def, '(REFERENCES[[:space:]]+[A-Za-z0-9_]+)'));
if split_part(referenced_table_name, ' ', 2) IN (SELECT unnest(production_tables)) THEN
UPDATE _migration_table_fk_def
SET fk_def = replace(fk_def, referenced_table_name, referenced_table_name || '_migration')
WHERE fk_name = r.fk_name;
END IF;
END LOOP;
FOR r IN SELECT * FROM _migration_table_fk_def WHERE tb_name = table_name LOOP
EXECUTE format('ALTER TABLE %s_migration DROP CONSTRAINT IF EXISTS %I', table_name, r.fk_name);
EXECUTE format('ALTER TABLE %s_migration ADD CONSTRAINT %I %s', table_name, r.fk_name, r.fk_def);
END LOOP;
END LOOP;
DROP TABLE _migration_table_fk_def;
END
$$;
CREATE OR REPLACE 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;
id_user integer;
BEGIN
album_table = schema || '.album';
EXECUTE 'SELECT id_user FROM ' || album_table || ' WHERE id = ' || album_id INTO id_user;
SELECT public.get_next_version_func(schema, id_user) INTO new_version;
EXECUTE format('UPDATE %s SET version = %L WHERE id = %L', album_table, new_version, album_id);
END
$$;
CREATE OR REPLACE FUNCTION public.update_similar_timeline_after_unit_insert_func()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO public.similar_timeline (id_unit, id_item, id_user, takentime)
SELECT
NEW.id,
NEW.id_item,
NEW.id_user,
NEW.takentime
FROM item
WHERE NEW.id_item = item.id
AND item.is_similar_top_pick is NOT FALSE
ON CONFLICT (id_unit, id_item, id_user)
DO UPDATE SET
takentime = EXCLUDED.takentime;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION public.update_filter_with_conflict_delete(p_old_id_filter INTEGER, p_new_id_filter INTEGER, p_id_user INTEGER, p_max_id_unit INTEGER, p_filter_type TEXT)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
-- remove conflict rows first
DELETE FROM filter f
USING filter conflict
WHERE
f.filter_type = p_filter_type
AND f.id_user = p_id_user
AND f.id_filter = p_old_id_filter
AND f.id_unit <= p_max_id_unit
AND conflict.id_unit = f.id_unit
AND conflict.id_filter = p_new_id_filter
AND conflict.filter_type = f.filter_type
AND conflict.id_user = f.id_user
AND conflict.ctid <> f.ctid;
UPDATE filter
SET id_filter = p_new_id_filter
WHERE
filter_type = p_filter_type
AND id_user = p_id_user
AND id_filter = p_old_id_filter
AND id_unit <= p_max_id_unit;
END;
$$;
CREATE OR REPLACE FUNCTION public.update_one_filter_has_many_unit_func ()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
one_filter_has_many_unit_table text;
target_column text;
filter_type text;
is_major boolean;
BEGIN
filter_type = COALESCE(NEW.filter_type, OLD.filter_type);
IF (filter_type IN (
'aperture',
'camera',
'exposure_time',
'flash',
'focal_length',
'folder',
'iso',
'item_type',
'lens',
'takentime',
'rating'
))
THEN
one_filter_has_many_unit_table = TG_TABLE_SCHEMA || '.one_filter_has_many_unit';
target_column = 'id_' || filter_type;
IF (TG_OP = 'INSERT') THEN
EXECUTE format('SELECT is_major FROM unit WHERE unit.id = %L', NEW.id_unit) INTO is_major;
EXECUTE format('INSERT INTO %s (id_unit, %s, id_user, is_major) VALUES (%L, %L, %L, %L) ON CONFLICT (id_unit) DO UPDATE SET %s = %L', one_filter_has_many_unit_table, target_column, NEW.id_unit, NEW.id_filter, NEW.id_user, is_major, target_column, NEW.id_filter);
ELSIF (TG_OP = 'DELETE') THEN
IF (filter_type IN ('rating')) THEN
EXECUTE format('UPDATE %s SET %s = 0 WHERE id_unit = %L', one_filter_has_many_unit_table, target_column, OLD.id_unit);
ELSE
EXECUTE format('UPDATE %s SET %s = NULL WHERE id_unit = %L', one_filter_has_many_unit_table, target_column, OLD.id_unit);
END IF;
ELSIF (TG_OP = 'UPDATE') THEN
EXECUTE format('UPDATE %s SET %s = %L WHERE id_unit = %L AND id_user = %L', one_filter_has_many_unit_table, target_column, NEW.id_filter, NEW.id_unit, NEW.id_user);
END IF;
END IF;
RETURN NULL;
END;
$$;
CREATE OR REPLACE 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;
id_user integer;
BEGIN
delete_album_table = TG_TABLE_SCHEMA || '.delete_album';
share_table = TG_TABLE_SCHEMA || '.share';
IF (TG_OP = 'DELETE') THEN
SELECT id from user_info where id = OLD.id_user INTO id_user;
if (id_user IS NULL) THEN
RETURN NULL;
END IF;
SELECT public.get_next_version_func(TG_TABLE_SCHEMA, OLD.id_user) 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 OR REPLACE FUNCTION public.delete_redundant_person_data(user_id integer)
RETURNS VOID AS
$$
BEGIN
DELETE FROM person_group
WHERE id_user = user_id AND
NOT EXISTS (SELECT 1 FROM face WHERE face.id_person_group = person_group.id);
DELETE FROM cluster
WHERE id_user = user_id AND
NOT EXISTS (SELECT 1 FROM person_group WHERE person_group.id_cluster = cluster.id);
DELETE FROM person
WHERE id_user = user_id AND
NOT EXISTS (SELECT 1 FROM cluster WHERE cluster.id_person = person.id);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.create_temp_table_for_delete_task(task_id integer)
RETURNS VOID AS
$$
DECLARE
temp_table_name TEXT;
BEGIN
temp_table_name := 'temp_table_for_delete_task_' || task_id;
EXECUTE 'CREATE TABLE ' || temp_table_name || ' (id INTEGER)';
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.update_user_flag_after_delete_item_update_func()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.user_flag (id_user, flag, value)
VALUES
(NEW.id_user, 'need_recount_concept_item', 'true'),
(NEW.id_user, 'need_recount_person_item', 'true')
ON CONFLICT (id_user, flag) DO UPDATE SET value = 'true';
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.delete_group_func ()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
BEGIN
DELETE FROM share_permission WHERE target_type = 2 AND target_id = OLD.id;
RETURN NULL;
END
$$;
CREATE OR REPLACE 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;
id_user integer;
id_unit integer;
BEGIN
unit_table = TG_TABLE_SCHEMA || '.unit';
IF (TG_OP = 'INSERT') THEN
EXECUTE 'SELECT id_user FROM ' || unit_table || ' WHERE id = ' || NEW.id_unit INTO id_user;
SELECT public.get_next_version_func(TG_TABLE_SCHEMA, id_user) INTO new_version;
EXECUTE 'UPDATE ' || unit_table || ' SET version = ' || new_version || ' WHERE id = ' || NEW.id_unit;
ELSIF (TG_OP = 'DELETE') THEN
EXECUTE 'SELECT id_user FROM ' || unit_table || ' WHERE id = ' || OLD.id_unit INTO id_user;
IF (id_user IS NULL) THEN
RETURN NULL;
END IF;
SELECT public.get_next_version_func(TG_TABLE_SCHEMA, id_user) INTO new_version;
EXECUTE 'UPDATE ' || unit_table || ' SET version = ' || new_version || ' WHERE id = ' || OLD.id_unit;
END IF;
RETURN NULL;
END
$$;
CREATE OR REPLACE 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 OR REPLACE FUNCTION public.insert_into_temp_table_for_delete_task(task_id integer, VARIADIC ids int[])
RETURNS VOID AS
$$
DECLARE
sql_statement TEXT;
BEGIN
sql_statement := 'INSERT INTO temp_table_for_delete_task_' || task_id || ' (id) VALUES ';
sql_statement := sql_statement || '(' || array_to_string(ids, '),(') || ')';
EXECUTE sql_statement;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.update_many_unit_has_many_person_func()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
many_unit_has_many_person_table text;
BEGIN
IF (NEW.filter_type = 'person' OR OLD.filter_type = 'person') THEN
many_unit_has_many_person_table = TG_TABLE_SCHEMA || '.many_unit_has_many_person';
IF (TG_OP = 'INSERT') THEN
EXECUTE format('INSERT INTO %s (id_unit, id_person, id_user) VALUES (%L, %L, %L) ON CONFLICT (id_unit, id_person) DO NOTHING', many_unit_has_many_person_table, NEW.id_unit, NEW.id_filter, NEW.id_user);
ELSIF (TG_OP = 'DELETE') THEN
EXECUTE format('DELETE FROM %s WHERE id_unit = %L and id_person = %L', many_unit_has_many_person_table, OLD.id_unit, OLD.id_filter);
ELSIF (TG_OP = 'UPDATE') THEN
EXECUTE format('UPDATE %s SET id_person = %L WHERE id_unit = %L AND id_user = %L AND id_person = %L', many_unit_has_many_person_table, NEW.id_filter, NEW.id_unit, NEW.id_user, OLD.id_filter);
END IF;
END IF;
RETURN NULL;
END;
$$;
CREATE OR REPLACE FUNCTION public.unset_person_table_read_only_top_down_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
PERFORM public.drop_read_only_trigger_func('face');
PERFORM public.drop_read_only_trigger_func('person_group');
PERFORM public.drop_read_only_trigger_func('cluster');
PERFORM public.drop_read_only_trigger_func('person');
END
$$;
CREATE OR REPLACE FUNCTION public.update_many_unit_has_many_administrative_func()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
many_unit_has_many_administrative_table text;
filter_type text;
BEGIN
filter_type = COALESCE(NEW.filter_type, OLD.filter_type);
IF (filter_type = 'administrative') THEN
many_unit_has_many_administrative_table = TG_TABLE_SCHEMA || '.many_unit_has_many_administrative';
IF (TG_OP = 'INSERT') THEN
EXECUTE format('INSERT INTO %s (id_unit, id_administrative, id_user) VALUES (%L, %L, %L) ON CONFLICT (id_unit, id_administrative) DO NOTHING', many_unit_has_many_administrative_table, NEW.id_unit, NEW.id_filter, NEW.id_user);
ELSIF (TG_OP = 'DELETE') THEN
EXECUTE format('DELETE FROM %s WHERE id_unit = %L and id_administrative = %L', many_unit_has_many_administrative_table, OLD.id_unit, OLD.id_filter);
ELSIF (TG_OP = 'UPDATE') THEN
EXECUTE format('UPDATE %s SET id_administrative = %L WHERE id_unit = %L AND id_user = %L', many_unit_has_many_administrative_table, NEW.id_filter, NEW.id_unit, NEW.id_user);
END IF;
END IF;
RETURN NULL;
END;
$$;
CREATE OR REPLACE 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 OR REPLACE FUNCTION public.decrease_concept_album_count_for_delete_bg_task(task_id integer, user_id integer)
RETURNS VOID AS
$$
DECLARE
temp_table_name TEXT;
query TEXT;
BEGIN
temp_table_name := 'temp_table_for_delete_task_' || task_id;
query := 'UPDATE concept_album_additional
SET item_count = item_count - subquery.count
FROM (
SELECT id_concept, m.id_user, COUNT(*) AS count
FROM many_unit_has_many_concept m
JOIN unit ON unit.id = m.id_unit
WHERE m.id_user = ' || user_id || ' AND unit.is_major AND unit.id_item IN (' || 'SELECT id FROM ' || quote_ident(temp_table_name) ||')
GROUP BY id_concept, m.id_user
) AS subquery
WHERE concept_album_additional.id_concept = subquery.id_concept AND concept_album_additional.id_user = subquery.id_user;';
EXECUTE query;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.update_similar_timeline_after_similar_group_update_func()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE public.similar_timeline
SET
similar_group_item_count = NEW.item_count
WHERE id_similar_group = NEW.id;
RETURN NEW;
END;
$$;
CREATE OR REPLACE 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, NEW.id_user) INTO new_version;
NEW.version = new_version;
END IF;
RETURN NEW;
END
$$;
CREATE OR REPLACE FUNCTION public.create_read_only_trigger_func(table_name text)
RETURNS VOID
LANGUAGE plpgsql
SECURITY INVOKER
COST 1
AS $$
BEGIN
EXECUTE format('CREATE TRIGGER read_only_trigger
BEFORE INSERT OR UPDATE ON %I
FOR EACH STATEMENT
EXECUTE PROCEDURE public.read_only_table()', table_name);
END;
$$;
CREATE OR REPLACE FUNCTION public.define_person_timeline_view_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
CREATE OR REPLACE 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,
array_agg(DISTINCT f.id_person) AS id_person,
u.takentime
FROM unit u
JOIN face f ON u.id = f.id_unit
WHERE u.is_major = true
GROUP BY u.id_user, u.id;
END
$$;
CREATE OR REPLACE FUNCTION public.get_unit_takentime_by_condition_album ( id_album integer)
RETURNS TABLE ( max bigint, min bigint)
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
unit_table text;
item_table text;
BEGIN
unit_table = 'public.unit';
item_table = 'public.condition_album_item_' || id_album;
RETURN QUERY EXECUTE 'SELECT MAX(takentime), MIN(takentime) FROM ' || unit_table || ' WHERE id_item IN(
SELECT id_item FROM '|| item_table ||')';
END;
$$;
CREATE OR REPLACE 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;
id_user integer;
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;
id_user = NEW.id_user;
ELSIF (TG_OP = 'DELETE') THEN
passphrase = OLD.passphrase_share;
id_user = OLD.id_user;
END IF;
if passphrase IS NOT NULL THEN
SELECT public.get_next_version_func(TG_TABLE_SCHEMA, id_user) 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 OR REPLACE 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 OR REPLACE FUNCTION public.define_person_face_view_func()
RETURNS VOID
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
CREATE OR REPLACE VIEW public.person_face_view
AS
SELECT
face.id,
face.id_user,
id_person,
id_unit,
score,
is_major,
id_item
FROM face, unit
WHERE unit.id = face.id_unit AND unit.id_user = face.id_user;
END
$$;
CREATE OR REPLACE FUNCTION public.delete_similar_timeline_after_item_update_func()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
IF
NEW.is_similar_top_pick IS FALSE
THEN
DELETE FROM public.similar_timeline WHERE id_item = NEW.id;
END IF;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION public.increase_person_album_count_by_item_id(item_id integer, user_id integer)
RETURNS VOID AS
$$
BEGIN
UPDATE person_item_count
SET item_count = item_count + subquery.count
FROM (
SELECT id_person, COUNT(DISTINCT(id_item)) AS count
FROM person_face_view
WHERE id_item = item_id AND id_user = user_id
GROUP BY id_person
) AS subquery
WHERE person_item_count.id_person = subquery.id_person;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE 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, NEW.id_user) 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.id_user != OLD.id_user 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, NEW.id_user) INTO new_version;
NEW.version = new_version;
END IF;
RETURN NEW;
END IF;
RETURN NULL;
END
$$;
CREATE OR REPLACE 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, NEW.id_user) INTO next_version;
NEW.version = next_version;
END IF;
RETURN NEW;
END
$$;
CREATE OR REPLACE FUNCTION public.rename_fk_constraints(replaced_patterns text[])
RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
new_name text;
exists_check int;
BEGIN
FOR r IN
SELECT
con.conname AS old_name,
c.relname AS table_name,
n.nspname AS schema_name
FROM pg_constraint con
JOIN pg_class c ON c.oid = con.conrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE con.contype = 'f'
AND con.conname = ANY (replaced_patterns)
LOOP
new_name := r.table_name || '_' || r.old_name;
SELECT 1 INTO exists_check
FROM pg_constraint
WHERE conname = new_name;
IF exists_check IS NULL THEN
EXECUTE format(
'ALTER TABLE %I.%I RENAME CONSTRAINT %I TO %I;',
r.schema_name,
r.table_name,
r.old_name,
new_name
);
END IF;
END LOOP;
END;
$$;
CREATE OR REPLACE FUNCTION public.update_user_flag_after_live_photo_update_func()
RETURNS TRIGGER AS $$
DECLARE
affected_id_user integer;
BEGIN
IF (TG_OP = 'INSERT') THEN
SELECT DISTINCT n.id_user from new_table n where n.item_type=3 INTO affected_id_user;
ELSIF (TG_OP = 'UPDATE') THEN
SELECT DISTINCT n.id_user
FROM new_table n
JOIN old_table o ON n.id_user = o.id_user
WHERE n.is_major <> o.is_major
INTO affected_id_user;
END IF;
IF (affected_id_user IS NULL) THEN
RETURN NULL;
END IF;
INSERT INTO public.user_flag (id_user, flag, value)
VALUES
(affected_id_user, 'need_recount_concept_item', 'true'),
(affected_id_user, 'need_recount_person_item', 'true')
ON CONFLICT (id_user, flag) DO UPDATE SET value = 'true';
RETURN NULL;
END
$$ LANGUAGE plpgsql;
CREATE OR REPLACE 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 OR REPLACE FUNCTION public.delete_redundant_migration_person_data_func(user_id integer)
RETURNS VOID AS
$$
BEGIN
DELETE FROM person_group_migration
WHERE id_user = user_id AND
NOT EXISTS (SELECT 1 FROM face_migration WHERE face_migration.id_person_group = person_group_migration.id);
DELETE FROM cluster_migration
WHERE id_user = user_id AND
NOT EXISTS (SELECT 1 FROM person_group_migration WHERE person_group_migration.id_cluster = cluster_migration.id);
DELETE FROM person_migration
WHERE id_user = user_id AND
NOT EXISTS (SELECT 1 FROM cluster_migration WHERE cluster_migration.id_person = person_migration.id);
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE 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 OR REPLACE 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;
id_user integer;
BEGIN
unit_table = TG_TABLE_SCHEMA || '.unit';
IF (TG_OP = 'UPDATE') THEN
IF (NEW.description != OLD.description OR NEW.orientation != OLD.orientation) THEN
EXECUTE 'SELECT id_user FROM ' || unit_table || ' WHERE id = ' || NEW.id_unit INTO id_user;
SELECT public.get_next_version_func(TG_TABLE_SCHEMA, id_user) INTO new_version;
EXECUTE 'UPDATE ' || unit_table || ' SET version = ' || new_version || ' WHERE id = ' || NEW.id_unit;
END IF;
END IF;
RETURN NULL;
END
$$;
CREATE OR REPLACE FUNCTION public.create_user_event_history_table_by_id_user_func (id_user integer)
RETURNS void
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
id_user_bucket INT;
user_event_history_table text;
user_event_history_table_name text;
BEGIN
id_user_bucket := CASE WHEN id_user = 0 THEN 0 ELSE (id_user - 1) % 13 + 1 END;
user_event_history_table = 'public.user_event_history_' || id_user_bucket;
user_event_history_table_name = 'user_event_history_' || id_user_bucket;
IF EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = user_event_history_table_name
) THEN
RETURN;
END IF;
EXECUTE format('CREATE TABLE %s (
version BIGINT PRIMARY KEY,
id_user INTEGER NOT NULL,
target_type SMALLINT NOT NULL,
target_id INTEGER[] NOT NULL,
target_id_user INTEGER NOT NULL,
trigger_id_user INTEGER,
event_type TEXT NOT NULL,
event_detail JSON)', user_event_history_table
);
EXECUTE format('CREATE INDEX IF NOT EXISTS user_event_history_%s_version_id_user_idx ON %s (id_user, version);', id_user_bucket, user_event_history_table);
END
$$;
CREATE OR REPLACE FUNCTION public.delete_user_func ()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
BEGIN
DELETE FROM share_permission WHERE target_type = 1 AND target_id = OLD.id;
RETURN NULL;
END
$$;
CREATE OR REPLACE 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;
album_array bigint[];
album_id bigint;
record RECORD;
BEGIN
many_item_has_many_normal_album_table = schema || '.many_item_has_many_normal_album';
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;
END
$$;
CREATE OR REPLACE 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 OR REPLACE FUNCTION public.list_cover_for_id_folders(cover_limit integer, user_id integer, id_folders int[])
RETURNS TABLE (id_folder integer, id_unit integer[], cache_key text[])
AS $$
DECLARE
folder_id integer;
id_cover integer[];
id_cover_cache_key text[];
BEGIN
FOREACH folder_id IN ARRAY id_folders
LOOP
SELECT ARRAY_AGG(id), ARRAY_AGG(subquery.cache_key) INTO id_cover, id_cover_cache_key
FROM (
SELECT id, unit.cache_key
FROM unit
WHERE unit.id_folder = folder_id AND unit.is_major = TRUE AND unit.id_user = user_id
ORDER BY id DESC
LIMIT cover_limit
) AS subquery;
RETURN QUERY SELECT folder_id, id_cover, id_cover_cache_key;
END LOOP;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.set_person_migration_original_tables_backup_func()
RETURNS void
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
production_tables CONSTANT TEXT[] := ARRAY['face', 'person_group', 'cluster', 'person'];
var_table_name TEXT;
r RECORD;
BEGIN
FOR var_table_name IN SELECT unnest(production_tables) LOOP
EXECUTE format('DROP TABLE IF EXISTS %s_backup CASCADE', var_table_name);
EXECUTE format('ALTER SEQUENCE %s_id_seq RENAME TO %s_id_seq_backup', var_table_name, var_table_name);
FOR r in SELECT indexname AS index_name FROM pg_indexes WHERE tablename = var_table_name LOOP
EXECUTE format('ALTER INDEX %I RENAME TO %s_backup', r.index_name, r.index_name);
END LOOP;
FOR r in SELECT constraint_name
FROM information_schema.table_constraints
WHERE table_name = var_table_name AND constraint_type = 'FOREIGN KEY'
LOOP
EXECUTE format('ALTER TABLE %I DROP CONSTRAINT %I CASCADE', var_table_name, r.constraint_name);
END LOOP;
FOR r in SELECT DISTINCT trigger_name FROM information_schema.triggers WHERE event_object_table = var_table_name LOOP
EXECUTE format('DROP TRIGGER %I ON %I CASCADE', r.trigger_name, var_table_name);
END LOOP;
FOR r in SELECT rulename AS rule_name FROM pg_rewrite pr JOIN pg_class c
ON c.oid = pr.ev_class WHERE c.relname = var_table_name
LOOP
EXECUTE format('DROP RULE %I ON %I CASCADE', r.rule_name, var_table_name);
END LOOP;
IF var_table_name = 'face' OR var_table_name = 'person_group' THEN
EXECUTE format('ALTER TABLE %I DROP COLUMN feature', var_table_name);
END IF;
EXECUTE format('ALTER TABLE %I RENAME TO %s_backup', var_table_name, var_table_name);
END LOOP;
END
$$;
CREATE OR REPLACE FUNCTION public.copy_version_time_to_version_time_by_user_func (id_user integer)
RETURNS void
LANGUAGE plpgsql
VOLATILE
STRICT
SECURITY INVOKER
COST 1
AS $$
DECLARE
version_time_table text := 'public.version_time';
new_version_time_table text;
trigger_name text;
max_version bigint;
has_data_below_max_version INTEGER;
id_user_bucket INTEGER;
migrated_version_time_count INTEGER;
migrated_version_table text;
migrated_version_table_name text;
has_migrated_version_table boolean;
BEGIN
id_user_bucket := CASE WHEN id_user = 0 THEN 0 ELSE (id_user - 1) % 13 + 1 END;
new_version_time_table = 'public.version_time_' || id_user_bucket;
migrated_version_table = 'public.version_time_' || id_user;
migrated_version_table_name = 'version_time_' || id_user;
trigger_name = 'delete_expired_version_time_' || id_user_bucket || '_data_trigger';
has_migrated_version_table := EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = migrated_version_table_name);
IF NOT has_migrated_version_table THEN
EXECUTE format('DROP TRIGGER IF EXISTS %s ON %s CASCADE', trigger_name, new_version_time_table);
EXECUTE format('INSERT INTO %s (version, modified_time) SELECT version, modified_time FROM %s ON CONFLICT (version, modified_time) DO NOTHING',
new_version_time_table, version_time_table);
EXECUTE format('CREATE TRIGGER %s BEFORE INSERT ON %s FOR EACH ROW EXECUTE PROCEDURE public.delete_expired_version_time_data_func()',
trigger_name, new_version_time_table);
ELSE
EXECUTE format('DROP TRIGGER IF EXISTS %s ON %s CASCADE', trigger_name, new_version_time_table);
EXECUTE format('INSERT INTO %s SELECT * FROM %s ON CONFLICT (version, modified_time) DO NOTHING', new_version_time_table, migrated_version_table);
-- check if there are already data under max_version of version_time in the new_version_time_table
SELECT COALESCE(MAX(version), 0) FROM public.version_time INTO max_version;
EXECUTE format('SELECT 1 FROM %s WHERE version <= %L LIMIT 1', new_version_time_table, max_version) INTO has_data_below_max_version;
EXECUTE format('SELECT COUNT(*) FROM %s', migrated_version_table) INTO migrated_version_time_count;
IF migrated_version_time_count < 100000 AND has_data_below_max_version IS NULL THEN
EXECUTE format('INSERT INTO %s (version, modified_time) SELECT version, modified_time FROM %s ON CONFLICT (version, modified_time) DO NOTHING',
new_version_time_table, version_time_table);
END IF;
EXECUTE format('CREATE TRIGGER %s BEFORE INSERT ON %s FOR EACH ROW EXECUTE PROCEDURE public.delete_expired_version_time_data_func()',
trigger_name, new_version_time_table);
IF id_user > 13 THEN
EXECUTE format('DROP TABLE IF EXISTS %s CASCADE', migrated_version_table);
END IF;
END IF;
END;
$$;
CREATE OR REPLACE 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 start_time, end_time 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 max, min 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 || COALESCE(' SET start_time = ' || album_takentime_min, ' SET start_time = NULL') || COALESCE(', end_time = ' || album_takentime_max, ', end_time = NULL') || ' WHERE id = ' || album_id;
END IF;
RETURN NULL;
END
$$;
CREATE OR REPLACE FUNCTION public.any_user_need_migrate_similar_hash ()
RETURNS boolean
LANGUAGE plpgsql
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
BEGIN
RETURN EXISTS (
SELECT 1 FROM public.user_info
WHERE user_info.config ->> 'enable_similar' = 'true'
AND user_info.enable = 'true'
AND NOT EXISTS (
SELECT 1 FROM public.user_flag
WHERE user_info.id = user_flag.id_user
AND user_flag.flag = 'similar_hash_migration_done'
AND (user_flag.value = 'true' OR user_flag.value = 'error')
)
);
END
$$;
CREATE OR REPLACE 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 OR REPLACE FUNCTION public.level_person_timeline(start_time bigint, end_time bigint, person_id integer, user_id integer, level_param integer)
RETURNS TABLE (
level integer,
admin smallint,
id_unit integer,
item_count bigint,
id_item integer
) AS $$
BEGIN
RETURN QUERY
SELECT
CASE
WHEN level_param = 1 THEN g.level_1
WHEN level_param = 2 THEN g.level_2
WHEN level_param = 3 THEN g.level_3
WHEN level_param = 4 THEN g.level_4
WHEN level_param = 5 THEN g.level_5
WHEN level_param = 6 THEN g.level_6
ELSE NULL
END AS level,
CASE
WHEN level_param = 1 THEN g.admin_1
WHEN level_param = 2 THEN g.admin_2
WHEN level_param = 3 THEN g.admin_3
WHEN level_param = 4 THEN g.admin_4
WHEN level_param = 5 THEN g.admin_5
WHEN level_param = 6 THEN g.admin_6
ELSE NULL
END AS admin,
MIN(u.id) AS id_unit,
COUNT(DISTINCT u.id_item) AS item_count,
MIN(u.id_item) AS id_item
FROM
public.unit u
JOIN
public.face f ON u.id = f.id_unit
JOIN
public.geocoding g ON u.id_geocoding = g.id
WHERE
u.takentime BETWEEN start_time AND end_time
AND f.id_person = person_id
AND u.id_user = user_id
AND u.is_major = true
GROUP BY
level, admin
ORDER BY
item_count DESC NULLS LAST,
id_item ASC NULLS LAST
LIMIT 4;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE 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 OR REPLACE FUNCTION public.insert_similar_timeline_after_item_update_func()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
unit_id integer;
unit_takentime bigint;
similar_group_item_count bigint;
BEGIN
IF
NEW.is_similar_top_pick IS TRUE OR NEW.id_similar_group IS NULL
THEN
SELECT id, takentime INTO unit_id, unit_takentime FROM unit WHERE NEW.id = unit.id_item AND unit.is_major IS TRUE;
SELECT
item_count INTO similar_group_item_count
FROM
similar_group
WHERE
NEW.id_similar_group = similar_group.id;
INSERT INTO public.similar_timeline
VALUES (unit_id, NEW.id, NEW.id_user, unit_takentime, NEW.id_similar_group, similar_group_item_count)
ON CONFLICT (id_unit, id_item, id_user)
DO UPDATE SET
takentime = EXCLUDED.takentime,
id_similar_group = EXCLUDED.id_similar_group,
similar_group_item_count = EXCLUDED.similar_group_item_count;
END IF;
RETURN NEW;
END;
$$;
CREATE OR REPLACE 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, id_user) 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;
IF (OLD.is_similar_top_pick IS TRUE) THEN
PERFORM public.update_similar_group_top_pick_func(OLD.id_similar_group);
END IF;
RETURN NULL;
END
$$;
CREATE OR REPLACE FUNCTION public.display_item_type_order (item_type integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
display_order integer;
order_map integer[];
BEGIN
-- kPhoto = 0, order 0
-- kVideo = 1, order 4
-- kBurst = 2, order 6
-- kLive = 3, order 1
-- kPhoto360 = 4, order 3
-- kVideo360 = 5, order 5
-- kMotionPhoto = 6, order 2
SELECT ARRAY[0, 4, 6, 1, 3, 5, 2] INTO order_map;
display_order = order_map[item_type + 1];
RETURN display_order;
END
$$;
CREATE OR REPLACE FUNCTION public.create_version_time_by_id_user_func (id_user integer)
RETURNS void
LANGUAGE plpgsql
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 1
AS $$
DECLARE
new_version_time_table text;
new_version_time_table_name text;
trigger_name text;
pk_name text;
idx_name text;
new_sequence_name text;
id_user_bucket integer;
BEGIN
id_user_bucket := CASE WHEN id_user = 0 THEN 0 ELSE (id_user - 1) % 13 + 1 END;
new_version_time_table = 'public.version_time_' || id_user_bucket;
new_version_time_table_name = 'version_time_' || id_user_bucket;
pk_name = 'version_id_' || id_user_bucket || '_primary';
idx_name = 'version_time_' || id_user_bucket || '_idx';
new_sequence_name = 'public.version_time_' || id_user_bucket || '_version_seq';
trigger_name = 'delete_expired_version_time_' || id_user_bucket || '_data_trigger';
-- check if the sequence and table already exists
RAISE NOTICE 'Checking if sequence % and table % exist', new_sequence_name, new_version_time_table;
IF EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name = new_version_time_table_name
) THEN
RETURN;
END IF;
EXECUTE format('CREATE SEQUENCE %s START WITH 1', new_sequence_name);
EXECUTE format('CREATE TABLE %s (version BIGINT NOT NULL DEFAULT nextval(''%s''), modified_time BIGINT NOT NULL)',
new_version_time_table, new_sequence_name, pk_name);
EXECUTE format('CREATE UNIQUE INDEX %s ON %s USING btree (version, modified_time ASC NULLS LAST)', idx_name, new_version_time_table);
EXECUTE format('CREATE TRIGGER %s BEFORE INSERT ON %s FOR EACH ROW EXECUTE PROCEDURE public.delete_expired_version_time_data_func()',
trigger_name, new_version_time_table);
END;
$$;
CREATE OR REPLACE FUNCTION public.insert_similar_timeline_by_unit_id(target_id_unit integer)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO public.similar_timeline (id_unit, id_item, id_user, takentime)
SELECT
unit.id,
unit.id_item,
unit.id_user,
unit.takentime
FROM
unit, item
WHERE unit.id = target_id_unit AND unit.id_item = item.id
AND unit.is_major is TRUE
AND item.is_similar_top_pick is NOT FALSE
ON CONFLICT (id_unit, id_item, id_user)
DO NOTHING;
END;
$$;