===Acceptance criteria===
- The new schema defined in T335526 is created in production
===Partial checklist (from [[https://wikitech.wikimedia.org/wiki/Creating_new_tables | mw:Creating_new_tables ]])===
[x] Create a Phabricator task to track the table creation
[x] Design the table schema
[x] Write the gerrit patch and get it merged
[x] Request DBA signoff
** See information below in the Details section.
** [x] Move the task to the Triage column on the DBA workboard.
[x] Create the views with appropriate restrictions -- not needed, these tables will be in x1
=== Details ===
- Should this table be replicated to wiki replicas (does it not contain private data)?
-- The `ce_event_questions` table is public, but in order to respect (soft) deletion of events, rows should not be visible if `ceeq_question_id` corresponds to a row in `campaign_events` with `event_id=ceeq_question_id` and `event_deleted_at IS NOT NULL`.
-- The other two tables (`ce_question_answers` and `ce_question_aggregation`) must be private because they will contain PII or information that is otherwise hidden to unprivileged users.
- Will you be doing cross-joins with the wiki metadata?
-- No
- Size of the table (number of rows expected).
-- They'd start off as 0, then they'll grow as explained in the next section.
- Expected growth per year (number of rows).
-- According to P35379, some (over)estimates could be:
--- `ce_event_questions`: 5 * number_of_events, i.e., 30 000
--- `ce_question_answers`: Assuming that 3/5 of the participants will answer all questions, it would be 5*300*500*12 = 9 000 000. But 3/5 of the answers would be deleted after 90 days, so a better estimate would be 2*300*500*12 + 3*300*500*3 = 4 950 000
--- `ce_question_aggregation`: 5 * number_of_events, i.e., 30 000
- Expected amount of queries, both writes and reads (per minute, per hour...per day, any of those are ok).
-- According to P35379, queries per month:
--- `ce_event_questions`:
---- read: every time the registration form is opened, events_per_month * 1 = 500
---- write: roughly the same as the reads, events_per_month * 1 = 500
--- `ce_question_answers`:
---- read: register or edit your registration, assuming 1 every 2 participants does this once: participants_per_month * 1.5 * 2 queries = 500 * 300 * 1.5 * 2 = 450 000; aggregate data: 500 * 300 = 150 000.
---- write: register or edit your registration, assuming 1 every 3 participants will change something: participants_per_month * 1/3 * 2 queries = 100 000
--- `ce_question_aggregation`:
---- read: every time organizer opens stats: assuming they'll do so 20 times per event: events_per_month * 20 = 500 * 20 = 10 000
---- write: every time participant answers are aggregated: roughly 500 * 300 = 150 000.
- Examples of queries that will be using the table.
-- See T335526#8947228 for the full list
- The release plan for the feature (are there specific wikis you'd like to test first etc).
-- The extension is only enabled on **testwiki**, **test2wiki**, **officewiki**, and **metawiki**, and the schema should be created on these wikis.
=== Deployment ===
[] Make sure the patch which introduces the tables got merged, and confirm files were deployed to production (will be in 1.41.0-wmf.15)
[x] Schedule table creation at a time that doesn't conflict with the deployment schedule; note the time & date here.
-- [[https://wikitech.wikimedia.org/w/index.php?title=Deployments&diff=2088453&oldid=2088442 | Scheduled ]] for 2023-06-29 15:00 UTC
[] Log the table creation in the SAL (`#wikimedia-operations`): `!log Creating new DB tables for the CampaignEvents extension in x1.testwiki, x1.test2wiki, x1.officewiki, and x1.wikishared # T340000`
[] Create the tables for each wiki. Note that we don't have a standalone .sql file, so the SQL below should be run interactively for each wiki:
-- `mwscript sql.php --wiki=testwiki --cluster=extension1`
-- `mwscript sql.php --wiki=test2wiki --cluster=extension1`
-- `mwscript sql.php --wiki=officewiki --cluster=extension1`
-- `mwscript sql.php --wiki=metawiki --cluster=extension1 --wikidb=wikishared`
```lang=sql
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*/;
```