Clean database from undeleted replicated subjects (NO PRIVATE DATA HERE)

[julien-louis]

ON OFSEP

(count subjects in the datasets schema that have no parent subject in studies schema, then count linked exams, acq and ds)

mysql@debian2 ofsep-qualif-datasets> select count(id) from datasets.subject where datasets.subject.id not in (select id from studies.subject);
+-----------+
| count(id) |
+-----------+
|        61 |
+-----------+
1 row in set (0.015 sec)


mysql@debian2 ofsep-qualif-datasets> select count(*) from examination where subject_id in (select id from datasets.subject where datasets.subject.id not in (select id from studies.subject));
+----------+
| count(*) |
+----------+
|      493 |
+----------+
1 row in set (0.066 sec)

mysql@debian2 ofsep-qualif-datasets> select count(*) from dataset_acquisition where examination_id in (select id from examination where subject_id in (select id from datasets.subject where datasets.subject.id no
t in (select id from studies.subject)));
+----------+
| count(*) |
+----------+
|     5949 |
+----------+
1 row in set (0.061 sec)

mysql@debian2 ofsep-qualif-datasets> select count(*) from dataset where dataset_acquisition_id in (select id from dataset_acquisition where examination_id in (select id from examination where subject_id in (sele
ct id from datasets.subject where datasets.subject.id not in (select id from studies.subject))));
+----------+
| count(*) |
+----------+
|     7101 |
+----------+
1 row in set (0.131 sec)

[julien-louis]

ON NEURINFO

mysql@debian2 neurinfo-qualif-datasets> select count(id) from datasets.subject where datasets.subject.id not in (select id from studies.subject);
+-----------+
| count(id) |
+-----------+
|        58 |
+-----------+
1 row in set (0.037 sec)

mysql@debian2 neurinfo-qualif-datasets> select count(*) from examination where subject_id in (select id from datasets.subject where datasets.subject.id not in (select id from studies.subject));
+----------+
| count(*) |
+----------+
|       31 |
+----------+
1 row in set (0.069 sec)

mysql@debian2 neurinfo-qualif-datasets> select count(*) from dataset_acquisition where examination_id in (select id from examination where subject_id in (select id from datasets.subject where datasets.subject.id not in (select id from studies.subject)));
+----------+
| count(*) |
+----------+
|      342 |
+----------+
1 row in set (0.034 sec)

mysql@debian2 neurinfo-qualif-datasets> select count(*) from dataset where dataset_acquisition_id in (select id from dataset_acquisition where examination_id in (select id from examination where subject_id in (select id from datasets.subject where datasets.subject.id not in (select id from studies.subject))));
+----------+
| count(*) |
+----------+
|      114 |
+----------+
1 row in set (0.051 sec)

[julien-louis]

Hints on dependencies

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('subject_id') AND TABLE_SCHEMA='datasets';
+------------------+
| TABLE_NAME       |
+------------------+
| dataset          |
| examination      |
| shanoir_metadata |
| subject_study    |
+------------------+
4 rows in set (0.01 sec)

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('examination_id') AND TABLE_SCHEMA='datasets';
+-----------------------------+
| TABLE_NAME                  |
+-----------------------------+
| dataset_acquisition         |
| extra_data_file_path        |
| instrument_based_assessment |
| shanoir_metadata            |
+-----------------------------+
4 rows in set (0.00 sec)

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('dataset_acquisition_id') AND TABLE_SCHEMA='datasets';
+------------+
| TABLE_NAME |
+------------+
| dataset    |
+------------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME IN ('dataset_id') AND TABLE_SCHEMA='datasets';
+-----------------------------+
| TABLE_NAME                  |
+-----------------------------+
| channel                     |
| dataset_expression          |
| dataset_property            |
| dataset_tag                 |
| event                       |
| input_of_dataset_processing |
| processing_resource         |
| related_datasets            |
| shanoir_metadata            |
+-----------------------------+
9 rows in set (0.00 sec)