In my application I have reference data tables that I setup on my staging server and then when it is tested I want to move it to production. Initially I thought ‘rails db:seed:dump’ may be useful but there are keys between reference data and the rest of the database so this probably wont work. My current research indicated I probably need to write a .rb script and use rails runner (rails run script.rb’). Wondering how best to approach this.
So some context probably best to give details of the specific application. Its a listings site. The main tables are venues and events. It gets listings from venue websites (using web scraping) and facebook (using the graph API). There is also a table called event_modifiers which holds details on transformations that need doing to the date. These include not including where there is a certain event title (i.e. PRIVATE PARTY) and also has rules to try to work out the type of event (i.e. if title contains ‘CLUB NIGHT’ set event_type = ‘CLUB’). Some of these event_modifiers are global and some only for a specific venue. This is a vast oversimplification of the application but should be enough to show what the challenges are in moving reference date. One example is I there is a foreign key in event_modifiers that may point to a venue (rails 5 suports these optional associations). There is also a foregn key between the events (non ref data) and venues (ref data).
So far it would be possible to simply truncate all the tables and reload the database (although this seems a bit drastic) but there is another issue. However users (another table) can bookmark events so there is also a foregn key between user_bookmarks and events. If I dropped the reference data the association between user_bookmarks, events and venues need keeping (the venue_id foreign key).
so this is far from trivial and some pointers would be good.