Data can be migrated from Stroudco to OFN database

[pmackay]

• Define data file format for database migration #237
• Build tool to extract data from Stroudco database #238
• Password migration #355
• Migration of Stroudco database in single extract, transform, load. #356
• Add data migration tool functionality for update and delete #357
  Add default Payment Methods, Shipping Methods and Enterprise Fees to Migration Tool #382

https://github.com/Stroudco/stroudco-data-migration

[aidandunsdon]

Description

Create a model to describe the data mapping and transformation from Stroudco database format to OFN.
https://onedrive.live.com/redir?resid=D12AF2DA8A234E98!1172&authkey=!AD9Lhku4welJ63k&ithint=file%2cxlsx

[aidandunsdon]

In the destination OFN database an owner (user) is mandatory for an enterprise. In the source Stroudco database some suppliers do not have an associated member. This meant that in the test migration process in supplier.yml there was a supplier created but in users.yml there was no corresponding user with supplier.id. This crashed the migration tool until temporary users were created for each of the enterprises.

[aidandunsdon]

In the destination OFN database there are there are two address details fields , in Stroudco there are 3.

[aidandunsdon]

To create a mapping from product_VAT_rate to tax_category_id , had to create 3 tax categories Exempt Rate, Low Rate, Full Rate

[aidandunsdon]

Passwords are encrypted and therefore cant be migrated.

[aidandunsdon]

OFN uses 3 fields variant_unit, variant_unit_scale, variant_unit_name for Stroudco single field product_units which is free text that is hard to parse and transform to correct 3 fields

[pmackay]

Aidan, is the scripts/config for the ETL tool you're using something you
could commit to a project? Would be great to see how it works.

[aidandunsdon]

@pmackay Plan to v. soon. Started off with an external tool found it overkill and ended up prototyping in .net to get things moving quicker and translating back to Ruby with some help - but hey if it works... Am still on track to complete before end sprint 2. Have migrated the main entities successfully - still some mapping issues to resolve but no show stoppers.

[aidandunsdon]

Migration of images needs further work. I still have an error with images on Windows Rails which I raised on Slack but haven't had time to follow up. This is currently preventing me testing image migration.
error when trying to load a product image Paperclip::Errors::NotIdentifiedByImageMagickError in Spree::Admin::ImagesController#create

[pmackay]

@aidandunsdon it might be good to create a separate bug issue for the image thing you're finding and put in Sprint 2? Also be good to capture there the links Rohan sent if any are useful.

[RohanM]

Hi @aidandunsdon,

Lots going on here! I'll tackle each of your comments in turn:

In the destination OFN database there are there are two address details fields , in Stroudco there are 3.

Were you able to merge these down to OFN's two fields? Do most stroudco addresses use three fields? Is this something you think OFN should support?

OFN uses 3 fields variant_unit, variant_unit_scale, variant_unit_name for Stroudco single field product_units which is free text that is hard to parse and transform to correct 3 fields

Could you give me a representative sample of the kinds of unit names you see in Stroudco? You might be able to make a temporary import by using:

Product: variant_unit='items', variant_unit_scale=nil, variant_unit_name='item'
Variant: display_as=unit_name_from_stroudco

This is currently preventing me testing image migration.
error when trying to load a product image Paperclip::Errors::NotIdentifiedByImageMagickError in Spree::Admin::ImagesController#create

This is most commonly caused by imagemagick not being installed, or Rails not being able to find it. Check if it's installed by running convert or identify from the command line. If those commands are not found, you'll need to install imagemagick. If they work, then perhaps Rails can't find it. Here's how to fix that:

thoughtbot/paperclip#1405 (comment)

Let me know how you go and if you want to skype to discuss anything :)

[aidandunsdon]

https://github.com/Stroudco/stroudco-data-migration

[RohanM]

This is looking really good. I've logged a couple of issues to that repo with some thoughts on migration ids and atomicity. Thanks Aidan!

[NickWeir63]

at the UK deve skype this morning @stveep offered to help with this. I then spoke to @aidandunsdon who will be in touch with Steve to discuss how they can share this work. Aidan mentioned that the following tables will need to be updated:

• users (although on reflection i think we do not need to update new shoppers - this can be done manually)
• enterprises
• products
• variants

Aidan is working on the Stroudco data today to produce a diff to show which products need updating.
Aidan suggested that @pmackay puts the production database on staging so that we can test the data changes on there
thanks all
Nick

[NickWeir63]

Aidan has now run the diff on the Stroudco databases (thanks very much Aidan) and it looks like the data migration updates are few enough that the Stroudco team can do manual updates. So thanks @stveep but we dont need you help with an automatic update. And thanks @pmackay but we do not need a copy of the production database. Sorry for the hassle
@lin-d-hop Aidan asked where DFFH is up to in terms of updating data - do they want help with an automatic update?

[lin-d-hop]

The DFFH team are holding back from OFN for the moment so don't worry about them. I'm sure updating the products manually won't be a big challenge for us either.
Thanks @aidandunsdon and @NickWeir63