ontohub/ontohub — pull request #1018 by 0robustus1 (2014-08-23 14:05:03+00:00)
Title: 648 ensure consistency of count columns

Shall fix #648.

After deploying this to a machine, we should check that there are actually no mismatches anymore.
This query will return the count of ontologies with mismatches:

```SQL
SELECT COUNT(compared.id) FROM
(
SELECT DISTINCT ON (id) * FROM
(SELECT o.id, o.name, o.entities_count, COUNT(e.id) as counted_entities FROM ontologies o INNER JOIN entities e ON e.ontology_id = o.id GROUP BY o.id ORDER BY o.id) e
JOIN
(SELECT o.name, o.sentences_count, COUNT(s.id) as counted_sentences FROM ontologies o INNER JOIN sentences s ON s.ontology_id = o.id GROUP BY o.id ORDER BY o.id) s USING (name)
JOIN (SELECT o.name, o.versions_count, COALESCE(COUNT(v.id), 0) as counted_versions FROM ontologies o INNER JOIN ontology_versions v ON v.ontology_id = o.id GROUP BY o.id ORDER BY o.id) v USING (name)
JOIN (SELECT o.name, o.metadata_count, COALESCE(COUNT(m.id), 0) as counted_metadata FROM ontologies o LEFT JOIN metadata m ON m.metadatable_id = o.id AND m.metadatable_type = 'Ontology' WHERE o.type = 'SingleOntology' GROUP BY o.id ORDER BY o.id) m USING (name)
JOIN (SELECT o.name, o.comments_count, COALESCE(COUNT(c.id), 0) as counted_comments FROM ontologies o LEFT JOIN comments c ON c.commentable_id = o.id AND c.commentable_type = 'Ontology' WHERE o.type = 'SingleOntology' GROUP BY o.id ORDER BY o.id) c USING (name)
) compared
WHERE compared.entities_count != compared.counted_entities OR
compared.sentences_count != compared.counted_sentences OR
compared.versions_count != compared.counted_versions OR
compared.metadata_count != compared.counted_metadata OR
compared.comments_count != compared.counted_comments;
```

But i suggest to run this query on *ontohub.org* and *staging.ontohub.org* **only**.
That is because *develop.ontohub.org* has more than 12.000 ontologies, which will put
a strain on the machine. When i executed it ran out of temporary-file-memory.
But it'll work on *ontohub.org* and *staging.ontohub.org*.