StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow do I manage a set of mysql tables in a production Rails app that are periodically recreated?
text
Body
copied!<p>I have a production Rails app that serves data from a set of tables that are built from a <code>LOAD DATA LOCAL INFILE</code> MYSQL import of CSV files, via a ruby script. The tables are consistently named and the schema does not change. The script drops/creates the tables and schema, then loads the data.</p> <p>However I want to re-do how I manage data changes. I need a suggestion on how to manage new published data over time, since the app is in production, so I can (1) push data updates frequently without breaking the application servicing user requests and (2) make the new set of data "testable" before it is live (with the ability to roll back to the previous tables/data if something went wrong).</p> <p>What I'm thinking is keeping a table of "versions" and creating a record each time a new rebuild is done. The latest version ID could be stuck into the <code>database.yml</code>, and each model could specify a table name from <code>database.yml</code>. A script could move the version forward or backward to make sure everything is ok on the new import, without destroying the old version.</p> <p>Is that a good approach? Any patterns like this already? It seems similar to Rails' migrations somewhat. Any plugins or gems that help with this sort of data management?</p> <p><strong>UPDATE/current solution:</strong> I ended up creating <code>database.yml</code> configuration and creating the tables at import time there. The data doesn't change based on the environment, so it is a "peer" to the environment-specific config. Since there are only four models to update, I added the database connection explicitly:</p> <p><code>establish_connection Rails.configuration.database_configuration["other_db"]</code></p> <p>This way migrations and queries work as normal with Rails. To I can keep running imports, I update the database name in the separate config for each import. I could manually specify the previous database version this way and restart the app if there was a problem.</p> <pre><code>config = YAML.load_file(File.join("config/database.yml")) config["other_db"]["database"] = OTHER_DB_NAME File.open(path, 'w'){|f| f.write(config.to_yaml)} </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload