copy all tables in dev instance to production instance

14/10/16

Permalink 04:02:39 pm, by sarneil, 315 words, 96 views   English (CA)
Categories: Activity Log; Mins. worked: 180

copy all tables in dev instance to production instance

Many of the tables in the bailey db have foreign key constraints to other tables.

So, if I simply try to export an sql dump of one instance and import that into an existing instance, I get "foreign key constraint violation" errors.

SQL won't let me truncate a table which has a foreign key pointing to it from another table. Only solution I could come up with was to figure out the foreign keys for each table (by looking at the xsl export file), then in the destination DB drop the tables in reverse order of dependencies (so that for each table, I had already dropped any tables that had foreign keys pointing to that table). Then I could import the .sql file into the database containing no tables (and thus no constraints on tables).

Specifically, for Bailey
1) EXPORT dev database (e.g. dev_db.sql), ensure you include the "DROP TABLE" checkbox
2) EXPORT prod database (e.g. prod_db.sql), ensure you include the "DROP TABLE" checkbox

3) On production database:

3a) DON'T DROP this field
saved_searches

3b) drop fields in this order

FIELD HAS _id_fk POINTER TO FIELD(S)
execution_specials execution_mode, outcome
outcome_durations outcome

outcomes trial, outcome_normalized
respites trial, respite_delay, respite_punishment, respite_normalized
judge_respites trial, respite_delay, respite_punishment, respite_normalized
crimes trial, crime_normalized
mercy_appeals trial, mercy_appeal_who, mercy_appeal_why

trials trial_file, judge, jury

trial_files criminal
aliases criminal

outcome_normalizeds outcome_group
crime_normalizeds crime_group
criminals gender

age_groups 0
crime_groups 0
execution_modes 0
genders 0
judges 0
judge_respite_normalizeds 0
juries 0
mercy_appeal_whos 0
mercy_appeal_whys 0
outcome_exceptionals 0
outcome_groups 0
respite_delays 0
respite_normalizeds 0
respite_punishments 0

3c) import dev_db.sql (it may time out and tell you to upload again to continue) until you get "Import has been successfully finished" message

4) test to make sure everything is working.

Pingbacks:

No Pingbacks for this post yet...

Capital Trials at the Old Bailey

Simon Devereaux has approximately 10,000 records of people convicted in potentially capital cases between 1710 and 1840 in London heard at the Old Bailey court. This project will create a web-based database which will allow interested researchers and members of the public to compose queries on that data (e.g. women charged with robbery 1710-1720). It must be able to support a range of queries and produce output allowing researchers to identify trends in judicial practice over that time.

Reports

XML Feeds