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 (could be deleted):

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')

All non-current relations pointing at current entities:

select * from openmind.node rel, openmind.node src, openmind.node tar
where 
rel.target_id = tar.id
and rel.source_id = src.id
and rel.source_modif = src.modification_time
and rel.target_modif = tar.modification_time
and rel.system_status = 'PREVIOUS_VERSION'
and src.system_status = 'CURRENT_VERSION'
and tar.system_status = 'CURRENT_VERSION'
order by rel.id;

The number of attributes without source_id (where a different version has a source_id) grouped by source object type.

select count(*), source_obj_class from openmind.node
where 
node_type = 'ATTRIBUTE'
and 
source_id is not null
and id in
(select distinct id from openmind.node
where source_id is null
and node_type = 'ATTRIBUTE')
group by source_obj_class

The number of entries modified in a date range group by type.

select count(*), node_type from node
where 
modification_time > unix_timestamp('2016-07-27')*1000
and 
modification_time < unix_timestamp('2016-07-28')*1000
group by node_type

The number of REFERENCEs without attributes but with relations grouped by date.

select count(*), from_unixtime(modification_time/1000, '%Y-%m-%d')
from node e
where 
e.object_class = 'REFERENCE'
and system_status = 'CURRENT_VERSION'
and e.id not in (
select source_id from node
where node_type = 'ATTRIBUTE'
)
and e.id in (
select source_id from node
where node_type = 'RELATION'
)
group by from_unixtime(modification_time/1000, '%Y-%m-%d')
order by 2

DANGEROUS: Set system_status of every last version of is_reference_of relations with target 74095 to CURRENT_VERSION. (Needs two nested subselects and safe_mode=off)

update node
set system_status = 'CURRENT_VERSION'
-- select * from node
where (id, version) in 
(
select * from
(
select id, max(version)
from node 
where 
target_id = 74095
and object_class = 'is_reference_of'
group by id
) as d
)