Skip to content

Create CSV Issue Imports table

Josianne Hyson requested to merge jh-235732_csv_import_gmau into master

🌳 What does this MR do?

Related issue: #235732 (closed)

We want to be able to report on the unique user usage of the CSV Issue Import feature. Currently we have no persisted data that we can use to retrospectively look this up. We are interested in reporting any attempted imports, regardless of whether or not it finished successfully.

This MR:

  1. creates a join table between User and Project that is populated each time a user tries to import issues to that project via CSV upload.
  2. look up the data in this table for the import usage data so that we can report on this.

These events have been added to the event dictionary according to the guide.

⚙️ Migrations

CreateCsvIssueImports

rails db:migrate:up VERSION=20201008125643

== 20201008125643 CreateCsvIssueImports: migrating ============================
-- create_table(:csv_issue_imports)
   -> 0.0089s
== 20201008125643 CreateCsvIssueImports: migrated (0.0090s) ===================

rails db:migrate:down VERSION=20201008125643

== 20201008125643 CreateCsvIssueImports: reverting ============================
-- drop_table(:csv_issue_imports)
   -> 0.0025s
== 20201008125643 CreateCsvIssueImports: reverted (0.0026s) ===================

AddProjectForeignKeyToCsvIssueImports

rails db:migrate:up VERSION=20201015121805

== 20201015121805 AddProjectForeignKeyToCsvIssueImports: migrating ============
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:csv_issue_imports)
   -> 0.0029s
-- execute("ALTER TABLE csv_issue_imports\nADD CONSTRAINT fk_e71c0ae362\nFOREIGN KEY (project_id)\nREFERENCES projects (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0020s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- execute("ALTER TABLE csv_issue_imports VALIDATE CONSTRAINT fk_e71c0ae362;")
   -> 0.0039s
-- execute("RESET ALL")
   -> 0.0002s
== 20201015121805 AddProjectForeignKeyToCsvIssueImports: migrated (0.0153s) ===

rails db:migrate:down VERSION=20201015121805

== 20201015121805 AddProjectForeignKeyToCsvIssueImports: reverting ============
-- remove_foreign_key(:csv_issue_imports, {:column=>:project_id})
   -> 0.0045s
== 20201015121805 AddProjectForeignKeyToCsvIssueImports: reverted (0.0093s) ===

AddUserForeignKeyToCsvIssueImports

rails db:migrate:up VERSION=20201015121825

== 20201015121825 AddUserForeignKeyToCsvIssueImports: migrating ===============
-- transaction_open?()
   -> 0.0000s
-- foreign_keys(:csv_issue_imports)
   -> 0.0026s
-- execute("ALTER TABLE csv_issue_imports\nADD CONSTRAINT fk_5e1572387c\nFOREIGN KEY (user_id)\nREFERENCES users (id)\nON DELETE CASCADE\nNOT VALID;\n")
   -> 0.0015s
-- execute("SET statement_timeout TO 0")
   -> 0.0001s
-- execute("ALTER TABLE csv_issue_imports VALIDATE CONSTRAINT fk_5e1572387c;")
   -> 0.0020s
-- execute("RESET ALL")
   -> 0.0002s
== 20201015121825 AddUserForeignKeyToCsvIssueImports: migrated (0.0097s) ======

rails db:migrate:down VERSION=20201015121825

== 20201015121825 AddUserForeignKeyToCsvIssueImports: reverting ===============
-- remove_foreign_key(:csv_issue_imports, {:column=>:user_id})
   -> 0.0040s
== 20201015121825 AddUserForeignKeyToCsvIssueImports: reverted (0.0071s) ======

🔍 Queries

Set up database-lab with the new table

CREATE TABLE "csv_issue_imports" (
  "id" bigserial primary key,
  "project_id" bigint NOT NULL,
  "user_id" bigint NOT NULL,
  "created_at" timestamptz NOT NULL,
  "updated_at" timestamptz NOT NULL
);

CREATE INDEX "index_csv_issue_imports_on_project_id" ON "csv_issue_imports" ("project_id");
CREATE INDEX "index_csv_issue_imports_on_user_id" ON "csv_issue_imports" ("user_id");

ALTER TABLE csv_issue_imports
  ADD CONSTRAINT fk_5e1572387c
  FOREIGN KEY (user_id)
  REFERENCES users (id)
  ON DELETE CASCADE
  NOT VALID;

ALTER TABLE csv_issue_imports
  ADD CONSTRAINT fk_e71c0ae362
  FOREIGN KEY (project_id)
  REFERENCES projects (id)
  ON DELETE CASCADE
  NOT VALID;

Query plans

With time range

explain SELECT COUNT(DISTINCT "csv_issue_imports"."user_id") FROM "csv_issue_imports"
  WHERE "csv_issue_imports"."created_at" BETWEEN '2020-09-16 10:10:15.175155' AND '2020-10-14 10:10:15.175269'
  AND "csv_issue_imports"."user_id" BETWEEN 0 AND 9999;

https://explain.depesz.com/s/osVb

Aggregate  (cost=9.30..9.31 rows=1 width=8) (actual time=0.039..0.039 rows=1 loops=1)
   Buffers: shared hit=1
   ->  Index Scan using index_csv_issue_imports_on_user_id on public.csv_issue_imports  (cost=0.15..9.30 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1)
         Index Cond: ((csv_issue_imports.user_id >= 0) AND (csv_issue_imports.user_id <= 9999))
         Filter: ((csv_issue_imports.created_at >= '2020-09-16 10:10:15.175155+00'::timestamp with time zone) AND (csv_issue_imports.created_at <= '2020-10-14 10:10:15.175269+00'::timestamp with time zone))
         Rows Removed by Filter: 0
         Buffers: shared hit=1

Without time range

explain SELECT COUNT(DISTINCT "csv_issue_imports"."user_id") FROM "csv_issue_imports"
  WHERE "csv_issue_imports"."user_id" BETWEEN 0 AND 9999;

https://explain.depesz.com/s/Hy4h

Aggregate  (cost=9.29..9.30 rows=1 width=8) (actual time=0.059..0.059 rows=1 loops=1)
  Buffers: shared hit=1
  ->  Index Only Scan using index_csv_issue_imports_on_user_id on public.csv_issue_imports  (cost=0.15..9.27 rows=6 width=8) (actual time=0.041..0.041 rows=0 loops=1)
        Index Cond: ((csv_issue_imports.user_id >= 0) AND (csv_issue_imports.user_id <= 9999))
        Heap Fetches: 0
        Buffers: shared hit=1

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • ~~Label as security and @ mention @gitlab-com/gl-security/appsec~~
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Josianne Hyson

Merge request reports