set autocommit=0;
begin;

-- get all active references to alf_prop_root
create temporary table temp1 (id bigint(20), index (id));
insert into temp1 select disabled_paths_id as id from alf_audit_app;
insert into temp1 select audit_values_id as id from alf_audit_entry;
insert into temp1 select prop1_id as id from alf_prop_unique_ctx;

-- determine the obsolete entries from alf_prop_root
create temporary table temp2 (id bigint(20), index (id));
insert into temp2 select alf_prop_root.id as id from alf_prop_root left join temp1 on temp1.id = alf_prop_root.id where temp1.id is null;

-- clear alf_prop_root which cascades DELETE to alf_prop_link
delete from alf_prop_root where id in (select id from temp2);

-- cleanup temporary structures
drop table temp1;
drop table temp2;

-- get all active references to alf_prop_value
create temporary table temp1 (id bigint(20), index (id));

insert into temp1 select id from alf_prop_value where id in (select app_name_id from alf_audit_app);
insert into temp1 select audit_user_id as id from alf_audit_entry;
insert into temp1 select key_prop_id as id from alf_prop_link;
insert into temp1 select value_prop_id as id from alf_prop_link;
insert into temp1 select value1_prop_id as id from alf_prop_unique_ctx;
insert into temp1 select value2_prop_id as id from alf_prop_unique_ctx;
insert into temp1 select value3_prop_id as id from alf_prop_unique_ctx;

-- determine the obsolete entries from alf_prop_value
create temporary table temp2 (id bigint(20), index (id));
insert into temp2 select alf_prop_value.id as id from alf_prop_value left join temp1 on alf_prop_value.id = temp1.id where temp1.id is null;

-- clear the obsolete entries
delete from alf_prop_value where id in (select id from temp2);

-- cleanup temporary structures
drop table temp1;
drop table temp2;

-- find and clear obsoleted serialized values
create temporary table temp1 (id bigint(20), index (id));
insert into temp1 select alf_prop_serializable_value.id as id from alf_prop_serializable_value left join alf_prop_value on alf_prop_value.long_value = alf_prop_serializable_value.id and alf_prop_value.persisted_type = 4 where alf_prop_value.id is null;

delete from alf_prop_serializable_value where id IN (select id from temp1);

-- find and clear obsoleted string values
create temporary table temp2 (id bigint(20), index (id));
insert into temp2 select alf_prop_string_value.id as id from alf_prop_string_value left join alf_prop_value on alf_prop_value.long_value = alf_prop_string_value.id and alf_prop_value.persisted_type in (3,5,6) where alf_prop_value.id is null;
delete from alf_prop_string_value where id in (select id from temp2);

-- find and clear obsoleted double values
create temporary table temp3 (id bigint(20), index (id));
insert into temp3 select alf_prop_double_value.id as id from alf_prop_double_value left join alf_prop_value on alf_prop_value.long_value = alf_prop_double_value.id and alf_prop_value.persisted_type = 2 where alf_prop_value.id is null;
delete from alf_prop_double_value where id in (select id from temp3);

-- cleanup temporary structures
drop table temp1;
drop table temp2;
drop table temp3;

commit;