Removing K6 generated data from eazy_tms_nightly

During the execution of the performance tests, K6 has been created data in multiple tables. The affected tables were the following: ahola_transport.organization, ahola_transport.department, ahola_transport.person, ahola_transport.media, ahola_transport.vehicle_unit, ahola_transport.customer_agreements.

Before deleting the following queries were run and the data found saved in case it's needed in the future.

The following queries were run first to make sure the right information was going to be deleted.

Search queries

-- Look for organizations
SELECT * FROM ahola_transport.organization WHERE name LIKE '%k6 Test Org%' 

-- Look for departments
SELECT * FROM ahola_transport.department WHERE name LIKE '%k6 Test Dep%'

-- Look for people 
SELECT * FROM ahola_transport.person WHERE name_first LIKE '%TestDude%'

-- Look for media (phone numbers)
SELECT m.*, p.name_first AS person_name FROM ahola_transport.media m JOIN ahola_transport.person p ON m.owner_uuid = p.uuid  WHERE p.name_first LIKE '%TestDude%'

-- Look for customer agreements
SELECT * FROM ahola_transport.customer_agreement WHERE revision ->> 'name' LIKE '%TestOrg%'

SELECT 
  *
FROM ahola_transport.customer_agreement cu
JOIN ahola_transport.department dep 
  -- Get the array, then check if dep.uuid (as text) exists inside it
  ON (cu.departments -> 'department_uuids') ? dep.uuid::text
WHERE 
  dep.name LIKE '%k6 Test Dep%';

-- Look for vehicle units
SELECT 
    vu.index,
    vu.uuid,           
    vu.reg_no, 
    vu.make, 
    vu.model, 
    dep.name as department_name
FROM ahola_transport.vehicle_unit vu
JOIN ahola_transport.department dep 
  ON (vu.agreement_properties ->> 'owner_department_uuid')::uuid = dep.uuid
WHERE dep.name LIKE 'k6 Test Dep%';

The following were the queries used to delete the data.

DELETE queries

-- Deleting customer agreements
DELETE FROM ahola_transport.customer_agreement cu
USING ahola_transport.department dep
WHERE (cu.departments -> 'department_uuids') ? dep.uuid::text
  AND dep.name LIKE '%k6 Test Dep%';

-- Deleting media
DELETE FROM ahola_transport.media m
USING ahola_transport.person p
WHERE m.owner_uuid = p.uuid
  AND p.name_first LIKE '%TestDude%';

-- Deleting people
DELETE FROM ahola_transport.person WHERE name_first LIKE '%TestDude%'

-- Deleting department
DELETE FROM ahola_transport.department WHERE name LIKE '%k6 Test Dep%';

DELETE FROM ahola_transport.organization WHERE name LIKE '%k6 Test Org%';

A mistake was made when the departments was deleted. The mistake was the before the departments were deleted the vehicle_units should have been deleted first, because the search was done by associating the vehicle unit to the department they belonged to. Since the departments were deleted before the association could not be made. The good thing was that the searches were saved in .csv files it was possible to obtain make, model and uuids of the the units and build a new query around that and filter all the vehicle units that match the make and model with no department associated to them.

-- To find vehicle_units without department
SELECT *
FROM ahola_transport.vehicle_unit vu
WHERE vu.make = 'scania' 
  AND vu.model = 'super'
  -- This part finds units linked to a department that no longer exists
  AND NOT EXISTS (
      SELECT 1 
      FROM ahola_transport.department dep 
      WHERE dep.uuid = (vu.agreement_properties ->> 'owner_department_uuid')::uuid
  );

-- Delete vehicle_units
DELETE FROM ahola_transport.vehicle_unit vu
WHERE vu.make = 'scania' 
  AND vu.model = 'super'
  AND NOT EXISTS (
      SELECT 1 
      FROM ahola_transport.department dep 
      WHERE dep.uuid = (vu.agreement_properties ->> 'owner_department_uuid')::uuid
  );