Create the tables for participant questions in beta
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Daimona
	Jun 21 2023, 12:32 AM

Description

Acceptance criteria

The new schema defined in T335526 is created in beta
This should happen after the patch with the schema is merged

Checklist

Log to the beta cluster
Log in the releng SAL (#wikimedia-releng): !log Creating new DB tables on beta wikishared for the CampaignEvents extension # T339997
Run the following in beta:

$ sql wikishared --write

CREATE TABLE /*_*/ce_event_questions (
  ceeq_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
  ceeq_event_id BIGINT UNSIGNED NOT NULL,
  ceeq_question_id INT UNSIGNED NOT NULL,
  UNIQUE INDEX ceeq_event_question (ceeq_event_id, ceeq_question_id),
  PRIMARY KEY(ceeq_id)
) /*$wgDBTableOptions*/;


CREATE TABLE /*_*/ce_question_answers (
  ceqa_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
  ceqa_event_id BIGINT UNSIGNED NOT NULL,
  ceqa_user_id INT UNSIGNED NOT NULL,
  ceqa_question_id INT UNSIGNED NOT NULL,
  ceqa_answer_option INT UNSIGNED DEFAULT NULL,
  ceqa_answer_text BLOB DEFAULT NULL,
  UNIQUE INDEX ceqa_event_user_question (
    ceqa_event_id, ceqa_user_id, ceqa_question_id
  ),
  PRIMARY KEY(ceqa_id)
) /*$wgDBTableOptions*/;


CREATE TABLE /*_*/ce_question_aggregation (
  ceqag_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
  ceqag_event_id BIGINT UNSIGNED NOT NULL,
  ceqag_question_id INT UNSIGNED NOT NULL,
  ceqag_answer_option INT UNSIGNED NOT NULL,
  ceqag_answers_amount INT UNSIGNED NOT NULL,
  UNIQUE INDEX ceqag_event_question (
    ceqag_event_id, ceqag_question_id
  ),
  PRIMARY KEY(ceqag_id)
) /*$wgDBTableOptions*/;

Related Objects
Search...

Status	Assigned	Task
Resolved	Daimona	T341392 Mark the CampaignEvents extension as stable
Resolved	cmelo	T321822 [EPIC] Participant Questions - MVP
Resolved	Daimona	T339997 Create the tables for participant questions in beta
Resolved	Daimona	T335526 [IMPLEMENTATION] - DB: create the tables: ce_question_answers, ce_question_aggregation and ce_event_questions
Resolved	Daimona	T328032 [SPIKE] How will we handle storage of PII data?

Event Timeline

Daimona created this task.Jun 21 2023, 12:32 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJun 21 2023, 12:32 AM

Daimona updated the task description. (Show Details)Jun 21 2023, 12:32 AM

Daimona added a subtask: T335526: [IMPLEMENTATION] - DB: create the tables: ce_question_answers, ce_question_aggregation and ce_event_questions.

Daimona added a parent task: T321822: [EPIC] Participant Questions - MVP.

Daimona updated the task description. (Show Details)Jun 26 2023, 10:16 AM

Mentioned in SAL (#wikimedia-releng) [2023-06-27T13:47:46Z] <Daimona> Creating new DB tables on beta wikishared for the CampaignEvents extension # T339997

Daimona claimed this task.Jun 27 2023, 1:53 PM

Daimona edited projects, added Campaigns-Product-Team (Campaign-Tools-Current-Sprint); removed Campaigns-Product-Team.

Daimona moved this task from Upcoming / refining 💡 to QA 🐛 on the Campaigns-Product-Team (Campaign-Tools-Current-Sprint) board.

Daimona closed subtask T335526: [IMPLEMENTATION] - DB: create the tables: ce_question_answers, ce_question_aggregation and ce_event_questions as Resolved.Jun 27 2023, 2:40 PM

Daimona mentioned this in T335526: [IMPLEMENTATION] - DB: create the tables: ce_question_answers, ce_question_aggregation and ce_event_questions.

✅ This looks correct in betacluster, based on the above table create queries - moving to done / resolved.

MariaDB [wikishared]> DESCRIBE ce_event_questions;
+------------------+---------------------+------+-----+---------+----------------+
| Field            | Type                | Null | Key | Default | Extra          |
+------------------+---------------------+------+-----+---------+----------------+
| ceeq_id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| ceeq_event_id    | bigint(20) unsigned | NO   | MUL | NULL    |                |
| ceeq_question_id | int(10) unsigned    | NO   |     | NULL    |                |
+------------------+---------------------+------+-----+---------+----------------+
3 rows in set (0.002 sec)

MariaDB [wikishared]> SHOW INDEX FROM ce_event_questions;
+--------------------+------------+---------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table              | Non_unique | Key_name            | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+--------------------+------------+---------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| ce_event_questions |          0 | PRIMARY             |            1 | ceeq_id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| ce_event_questions |          0 | ceeq_event_question |            1 | ceeq_event_id    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| ce_event_questions |          0 | ceeq_event_question |            2 | ceeq_question_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+--------------------+------------+---------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.001 sec)

MariaDB [wikishared]> DESCRIBE ce_question_answers;
+--------------------+---------------------+------+-----+---------+----------------+
| Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| ceqa_id            | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| ceqa_event_id      | bigint(20) unsigned | NO   | MUL | NULL    |                |
| ceqa_user_id       | int(10) unsigned    | NO   |     | NULL    |                |
| ceqa_question_id   | int(10) unsigned    | NO   |     | NULL    |                |
| ceqa_answer_option | int(10) unsigned    | YES  |     | NULL    |                |
| ceqa_answer_text   | blob                | YES  |     | NULL    |                |
+--------------------+---------------------+------+-----+---------+----------------+
6 rows in set (0.002 sec)

MariaDB [wikishared]> SHOW INDEX FROM ce_question_answers;
+---------------------+------------+--------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table               | Non_unique | Key_name                 | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+---------------------+------------+--------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| ce_question_answers |          0 | PRIMARY                  |            1 | ceqa_id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| ce_question_answers |          0 | ceqa_event_user_question |            1 | ceqa_event_id    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| ce_question_answers |          0 | ceqa_event_user_question |            2 | ceqa_user_id     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| ce_question_answers |          0 | ceqa_event_user_question |            3 | ceqa_question_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+---------------------+------------+--------------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
4 rows in set (0.001 sec)

MariaDB [wikishared]> DESCRIBE ce_question_aggregation;
+----------------------+---------------------+------+-----+---------+----------------+
| Field                | Type                | Null | Key | Default | Extra          |
+----------------------+---------------------+------+-----+---------+----------------+
| ceqag_id             | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| ceqag_event_id       | bigint(20) unsigned | NO   | MUL | NULL    |                |
| ceqag_question_id    | int(10) unsigned    | NO   |     | NULL    |                |
| ceqag_answer_option  | int(10) unsigned    | NO   |     | NULL    |                |
| ceqag_answers_amount | int(10) unsigned    | NO   |     | NULL    |                |
+----------------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.002 sec)

MariaDB [wikishared]> SHOW INDEX FROM ce_question_aggregation;
+-------------------------+------------+----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table                   | Non_unique | Key_name             | Seq_in_index | Column_name       | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+-------------------------+------------+----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| ce_question_aggregation |          0 | PRIMARY              |            1 | ceqag_id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| ce_question_aggregation |          0 | ceqag_event_question |            1 | ceqag_event_id    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
| ce_question_aggregation |          0 | ceqag_event_question |            2 | ceqag_question_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+-------------------------+------------+----------------------+--------------+-------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
3 rows in set (0.001 sec)

Create the tables for participant questions in betaClosed, ResolvedPublicActions