Crap Database Entries Cleanup

Why do we need this ?

Simple, because they are not being deleted by v7 itself. Honestly they are harmless and only occupy database space. Unfortunately this can change easily, like when you start using those relationship with some addons/modules or when you try to export/import database tables those nice harmless records become a problem.

OK, What can be done ?

You can have some automation for cleaning them up periodically, like maybe every night with cron or weekly, monthly it is up you as the admin of the VA. Also you can use the queries here at your database workbench tool whenever you need to. Technically there will be no difference between a cron/automation and a manual cleanup.

⚠️ As a general reminder, I advise having a database backup before conducting delete operations. ⚠️

SQL Queries for Database cleanup

Personally I have them in my cron tasks, some running every night some each month, so I am ok with deleting old and useless records. Below you can find the simplified versions of the queries I use.

Crap Acars Entries

DELETE 
FROM acars
WHERE pirep_id NOT IN (SELECT id FROM pireps);

Crap Flight <> Subfleet Relationship Entries

DELETE 
FROM flight_subfleet
WHERE flight_id NOT IN (SELECT id FROM flights);

DELETE  
FROM flight_subfleet
WHERE subfleet_id NOT IN (SELECT id FROM subfleets);

Crap Flight <> Fare Relationship Entries

DELETE
FROM flight_fare
WHERE flight_id NOT IN (SELECT id FROM flights);

DELETE
FROM flight_fare
WHERE fare_id NOT IN (SELECT id FROM fares);

Crap Subfleet <> Fare Relationship Entries

DELETE
FROM subfleet_fare
WHERE subfleet_id NOT IN (SELECT id FROM subfleets);

DELETE
FROM subfleet_fare
WHERE fare_id NOT IN (SELECT id FROM fares);

Crap Subfleet <> Rank Relationship Entries

DELETE
FROM subfleet_rank
WHERE subfleet_id NOT IN (SELECT id FROM subfleets);

DELETE
FROM subfleet_rank
WHERE rank_id NOT IN (SELECT id FROM ranks);

Crap TypeRating <> Subfleet & User Relationship Entries

DELETE
FROM typerating_subfleet
WHERE subfleet_id NOT IN (SELECT id FROM subfleets);

DELETE
FROM typerating_user
WHERE user_id NOT IN (SELECT id FROM users);

Crap Custom User (Profile) Field Entries

DELETE
FROM user_field_values
WHERE user_id NOT IN (SELECT id FROM users);

Crap Journal Entries

DELETE
FROM journals
WHERE `morphed_type` LIKE '%Airline' AND `morphed_id` NOT IN (SELECT id FROM airlines);

DELETE
FROM journals
WHERE `morphed_type` LIKE '%User' AND `morphed_id` NOT IN (SELECT id FROM users);

Crap Journal Transaction Entries

DELETE
FROM journal_transactions
WHERE journal_id NOT IN (SELECT id FROM journals);

DELETE 
FROM journal_transactions
WHERE ref_model LIKE '%Pirep' AND ref_model_id NOT IN (SELECT id FROM pireps);