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.
So use only the reference data to seed the production database.
Is this something you expect to have to do more than once? Will the
reference data change over time? If it's a one-off thing I'd probably
just do a DB dump of the relevant tables and be done with it...
Using seed.rb, from what I can gather, can only add data (i.e. if record already exists it creates duplicates).
The data transformation(table) will be changed fairly regularly (i.e. if I spot a venue listing that should not be included I define a transformation rather than delete it manually, this way if a similar thing happens again it will automatically be removed. As the data comes from external listings sites and facebook groups the transformation will need constantly tweaking based observations. It may be I have to do this by loading the data into staging tables and process them. Just looking for some pointers.
If your reference data is not immutable load-one-time-only then
your change/transform/whatever process should live and run on the
production server.
Mine is! Maybe I should call it something else but in my 25 years in IT i’ve worked on several projects that use data in a database to transform data and transformation change (i.e. data warehouse load scripts).
A “staging” server is for realistic testing of your procedures that will
run on production, not doing data transformation to export.
Again, maybe we need to call the server something else. Also it is testing (or the transformations).
Anyway worked it out. I simply reaload the reference tables. The trick is to keep all the keys the same and use deferred foreign keys.
Mine is! Maybe I should call it something else but in my 25 years in IT
i've worked on several projects that use data in a database to transform
data and transformation change (i.e. data warehouse load scripts).
I can't even parse what that sentence means, so...
A "staging" server is for realistic testing of your *procedures* that will
run on production, not doing data transformation to export.
Again, maybe we need to call the server something else. Also it is testing
(or the transformations).
Using commonly agreed-on definitions of things is generally a
Good Idea, especially given that Rails is a convention-oriented
framework.
Anyway worked it out. I simply reaload the reference tables. The trick is
to keep all the keys the same and use deferred foreign keys.
Sounds operationally brittle, but I'm sure your customers will let
you know when that stops working
Rules are there to be broken;).
"Good decisions are based on experience; experience is based
on bad decisions."
