Database cleanup queries

All entities with more than one CURRENT_VERSION:

select count(*) as cnt, id, node_type from openmind.node
where system_status = 'CURRENT_VERSION'
group by id
having cnt > 1
order by cnt desc;

All entities with more than one node_type:

select count(distinct node_type) as cnt, id from openmind.node
group by id
having cnt > 1
order by 1 desc;

All entities with more than one object_class:

select count(distinct object_class) as cnt, id from openmind.node
group by id
having cnt > 1
order by 1 desc;

All relations without source or target:

select * from openmind.node where node_type = 'RELATION' and (source_id is null or target_id is null);

All relations with target pointing at nonexistent entity:

select * from openmind.node rel 
left outer join openmind.node ent
on rel.target_id = ent.id
where 
rel.node_type = 'RELATION'
and ent.id is null;

All current relations pointing at non-current target entities:

select * from openmind.node rel, openmind.node ent 
where 
rel.target_id = ent.id
and rel.target_modif = ent.modification_time
and rel.system_status = 'CURRENT_VERSION'
and ent.system_status != 'CURRENT_VERSION';

All relations that have no current version:

select id from openmind.node
where system_status = 'PREVIOUS_VERSION'
and node_type = 'RELATION'
and id not in
(select distinct id from openmind.node
where system_status = 'CURRENT_VERSION')