Foreign keys: Successful Nov 20 2017

https://foreign-keys.staging.clippings.com

Haralan Dobrev
By Haralan Dobrev

What is this about?

Add missing foreign keys to database tables.

Checklist

  • [x] Identify most potential foreign keys automatically.
  • [x] Add additional foreign keys manually
  • [x] Remove false positives - potential foreign keys detected automatically which are invalid
  • [x] Normalise data before constraints in terms of type
  • [x] Delete stale records which do not make sense on their own
  • [x] Nullify records which point to missing entries, but the record make sense on its own
  • [x] Nullify foreign keys containing zeros
  • [x] Assigned people to review

Staging to review

https://foreign-keys.staging.clippings.com

Notes for deployment

  • [ ] This needs to be tested that it could run on production database safely and perhaps we could investigate more performant ways to run it.

Screenshots, links and notes

· I've used the following query to find most potential foreign keys: ```sql SELECT C.TABLE_NAME, C.COLUMN_NAME, COALESCE( CONCAT(P1.TABLE_NAME, '.id'), CONCAT(P2.TABLE_NAME, '.id'), IF(C.COLUMN_NAME IN ('creator_id', 'creatorId', 'author_id', 'recommender_id', 'assignee_id'), 'users.id', NULL), IF(C.COLUMN_NAME = 'parent_id' AND PC.COLUMN_NAME IS NULL, CONCAT(C.TABLE_NAME, '.id'), NULL), CONCAT(P5.TABLE_NAME, '.id') ) POTENTIAL_FOREIGN_KEY FROM INFORMATION_SCHEMA.COLUMNS C INNER JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME AND T.TABLE_TYPE = 'Base Table' AND T.TABLE_SCHEMA = C.TABLE_SCHEMA AND T.TABLE_SCHEMA = 'clippings' LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON C.TABLE_NAME = U.TABLE_NAME AND C.COLUMN_NAME = U.COLUMN_NAME AND U.TABLE_SCHEMA = C.TABLE_SCHEMA LEFT JOIN INFORMATION_SCHEMA.TABLES P1 ON P1.TABLE_SCHEMA = C.TABLE_SCHEMA AND P1.TABLE_NAME IN ( CONCAT(SUBSTRING(C.COLUMN_NAME, 1, INSTR(C.COLUMN_NAME, '_id') - 1), 's'), CONCAT(SUBSTRING(C.COLUMN_NAME, 1, INSTR(C.COLUMN_NAME, '_id') - 1), 'es'), CONCAT(SUBSTRING(C.COLUMN_NAME, 1, INSTR(C.COLUMN_NAME, '_id') - 2), 'ies') ) AND P1.TABLE_NAME != C.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.TABLES P2 ON P2.TABLE_SCHEMA = C.TABLE_SCHEMA AND P2.TABLE_NAME IN ( CONCAT(SUBSTRING(SUBSTRING(C.COLUMN_NAME, INSTR(C.COLUMN_NAME, '_') + 1), 1, INSTR(SUBSTRING(C.COLUMN_NAME, INSTR(C.COLUMN_NAME, '_') + 1), '_id') - 1), 's'), CONCAT(SUBSTRING(SUBSTRING(C.COLUMN_NAME, INSTR(C.COLUMN_NAME, '_') + 1), 1, INSTR(SUBSTRING(C.COLUMN_NAME, INSTR(C.COLUMN_NAME, '_') + 1), '_id') - 1), 'es'), CONCAT(SUBSTRING(SUBSTRING(C.COLUMN_NAME, INSTR(C.COLUMN_NAME, '_') + 1), 1, INSTR(SUBSTRING(C.COLUMN_NAME, INSTR(C.COLUMN_NAME, '_') + 1), '_id') - 2), 'ies') ) AND P2.TABLE_NAME != C.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.COLUMNS PC ON PC.TABLE_NAME = C.TABLE_NAME AND PC.TABLE_SCHEMA = C.TABLE_SCHEMA AND PC.COLUMN_NAME = 'parent_model' LEFT JOIN INFORMATION_SCHEMA.TABLES P5 ON P5.TABLE_SCHEMA = C.TABLE_SCHEMA AND P5.TABLE_NAME = CONCAT(SUBSTRING(C.COLUMN_NAME, 1, INSTR(C.COLUMN_NAME, 'Id') - 1), 's') AND C.COLUMN_NAME LIKE BINARY '%Id' AND P5.TABLE_NAME != C.TABLE_NAME WHERE U.COLUMN_NAME IS NULL AND (C.COLUMN_NAME LIKE '%_id' OR C.COLUMN_NAME LIKE BINARY '%Id') GROUP BY C.TABLE_NAME, C.COLUMN_NAME HAVING POTENTIAL_FOREIGN_KEY IS NOT NULL ORDER BY C.TABLE_NAME, C.COLUMN_NAME; ```
· I have manually identified the remaining foreign keys based on the columns ending in `id` which were not detected by the above query: ``` addresses.country_id -> locations.id addresses.city_id -> locations.id brands.logo_id -> pictures.id brand_categories.category_id -> terms.id locations_branches.ansestor_id -> locations.id locations_branches.descendant_id -> locations.id messages.threadId -> message_threads.id pictures.type_id -> terms.id products.color_property_id -> product_properties.id products.cover_id -> pictures.id products.material_property_id -> product_properties.id products_index.id -> products.id products_index.productBaseId -> product_bases.id product_bases.category_id -> terms.id product_bases.default_cover_id -> pictures.id product_bases.successor_id -> products.id promotions.region_id -> locations.id purchases.next_purchase_promo_code_id -> promo_codes.id purchase_datespans.item_id -> purchase_items.id purchase_items.reference_item_id -> purchase_items.id purchase_item_product_data.productBrandId -> brands.id purchase_item_product_data.productCategoryId -> terms.id purchase_item_product_data.productCoverId -> pictures.id purchase_item_product_data.productShippingId -> shippings.id purchase_item_product_data.purchaseItemId -> purchase_items.id quote_item_products.category_id -> terms.id shippings.fallback_profile_id -> shippings.id shippings.ships_from_id -> addresses.id shipping_groups.method_id -> shipping_methods.id swatches.upholstery_material_id -> terms.id visitors.country_id -> locations.id ```
· I have identified false positives from the automatic detection: ``` promotions.parent_id ```

I then removed the ones about swatches, because of the migrations in #2139.

I have commented out some foreign keys related to products and product_properties which are making MySQL to go away locally.

This is not yet complete for two reasons:

  • The commented out lines should be made to work somehow.
  • The updated schema.sql file is not up-to-date with the latest changes, because of the MySQL server going away a few times.

However, the whole notion behind that could be reviewed.

Last Change

Use truncating of all DB tables in Behat

Git fetch and reset hard
HEAD is now at 12c0789 Use truncating of all DB tables in Behat
Installing composer dependencies
Installing javascript dependencies
yarn install v1.3.2
[1/4] Resolving packages...
[2/4] Fetching packages...
[3/4] Linking dependencies...
warning " > react-intl@2.4.0" has unmet peer dependency "prop-types@^15.5.4".
warning " > react-moment@0.6.6" has unmet peer dependency "prop-types@^15.6.0".
warning " > react-moment@0.6.6" has incorrect peer dependency "react@^15.6.0 || ^16.0.0".
warning " > react-paginate@5.0.0" has incorrect peer dependency "react@^16.0.0".
[4/4] Rebuilding all packages...
success Saved lockfile.
Done in 10.75s.
Compiling assets
[21:57:43] Using gulpfile ~/staging-sites/foreign-keys/gulpfile.js
[21:57:43] Starting 'build-production'...
[21:57:43] Starting 'scss'...
[21:57:43] Starting 'babel'...
[21:57:43] Starting 'browserify-production'...
[21:57:43] Finished 'build-production' after 58 ms
[21:57:44] Finished 'babel' after 1.31 s
[21:58:13] Finished 'browserify-production' after 30 s
[21:58:13] Finished 'scss' after 31 s
[21:58:13] Starting 'useref'...
[21:59:15] Finished 'useref' after 1.02 min
Clearing cache
Clearing Kohana application cache
Done
Clearing default cache
Done
Clearing doctrine cache:
Clearing ALL Metadata cache entries
Successfully deleted cache entries.
Clearing ALL Query cache entries
Successfully deleted cache entries.
Clearing ALL Result cache entries
Successfully deleted cache entries.
Done
Migrating
Nothing to do