Page MenuHomePhabricator

Create the tables for participant questions in beta
Closed, ResolvedPublic

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*/;

Event Timeline

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

vaughnwalters added a subscriber: vaughnwalters.

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