MySQL snippets and other material relating to reporting from the Onyx database.

In the early phase of BRICCS, before i2b2 is deployed, obtaining data on BRICCS participants is possible only from within the Onyx database, via MySQL queries.

There is a web page for reporting from the Onyx database, accessible via password within the UHL network, contact Nick or Vasil for information. In addition, the following is provided as useful material to assist when writing custom MySQL queries for reporting:

Table Structure

mysql> describe participant;
+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key | Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| id               | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| barcode          | varchar(250) | YES  |     | NULL    |                |
| birth_date       | date         | YES  |     | NULL    |                |
| enrollment_id    | varchar(255) | YES  |     | NULL    |                |
| first_name       | varchar(250) | NO   |     | NULL    |                |
| gender           | varchar(255) | YES  |     | NULL    |                |
| last_name        | varchar(250) | NO   |     | NULL    |                |
| recruitment_type | varchar(255) | YES  |     | NULL    |                |
| site_no          | varchar(255) | NO   |     | NULL    |                |
+------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> describe question_answer;
+------------------------------+---------------+------+-----+---------+----------------+
| Field                        | Type          | Null | Key | Default | Extra          |
+------------------------------+---------------+------+-----+---------+----------------+
| id                           | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| active                       | bit(1)        | NO   |     | NULL    |                |
| comment                      | varchar(2000) | YES  |     | NULL    |                |
| question_name                | varchar(255)  | NO   | MUL | NULL    |                |
| questionnaire_participant_id | bigint(20)    | NO   | MUL | NULL    |                |
+------------------------------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> describe questionnaire_participant;
+-----------------------+--------------+------+-----+---------+----------------+
| Field                 | Type         | Null | Key | Default | Extra          |
+-----------------------+--------------+------+-----+---------+----------------+
| id                    | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| locale                | varchar(255) | NO   |     | NULL    |                |
| questionnaire_name    | varchar(255) | NO   |     | NULL    |                |
| questionnaire_version | varchar(255) | NO   |     | NULL    |                |
| resume_page           | varchar(255) | YES  |     | NULL    |                |
| time_end              | datetime     | YES  |     | NULL    |                |
| time_start            | datetime     | NO   |     | NULL    |                |
| participant_id        | bigint(20)   | NO   | MUL | NULL    |                |
| user_id               | bigint(20)   | NO   | MUL | NULL    |                |
+-----------------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

MySQL snippets

Lists primary diagnoses with a count (used in the reporting table):

SELECT category_name, count(category_name)

FROM question_answer, category_answer where question_answer.question_name = 'epi_pridiag'

and category_answer.question_answer_id = question_answer.id group by category_name order by count(category_name) desc

Lists BPt numbers, grouped and counted by primary diagnosis:

SELECT category_name, barcode, count(*)

FROM participant, questionnaire_participant, question_answer, category_answer WHERE participant.id = questionnaire_participant.participant_id AND question_answer.questionnaire_participant_id = questionnaire_participant.id AND question_answer.question_name = 'epi_pridiag' AND category_answer.question_answer_id = question_answer.id GROUP BY category_name, barcode WITH ROLLUP;

From the command line, to query for gender and birth date for specific diagnosis, into an outfile:#

mysql -h briccsdb -u auditor -p briccs -e " SELECT category_name, birth_date, gender FROM participant, questionnaire_participant, question_answer, category_answer WHERE participant.id = questionnaire_participant.participant_id AND question_answer.questionnaire_participant_id = questionnaire_participant.id AND question_answer.question_name = 'epi_pridiag' AND category_answer.question_answer_id = question_answer.id AND ( category_answer.category_name LIKE '%ACS%' OR category_answer.category_name = 'STEMI' OR category_answer.category_name = 'Stable Angina' ) ; " > mysql-output.txt