· 8 years ago · Mar 12, 2018, 08:38 AM
1#!/usr/bin/env ruby
2
3require 'optparse'
4require 'optparse/time'
5
6$source_database = 'texpert_production'
7$since = nil
8$dump = false
9$environment = 'slave'
10$force = false
11
12opts = OptionParser.new do |opts|
13 opts.banner = "Usage: #{$PROGRAM_NAME} [options]"
14 opts.separator ''
15
16 opts.on('-d', '--source DATABASE', "(#{$source_database})") do |d|
17 $source_database = d
18 end
19
20 opts.on('-s', '--since WHEN', Time, "(1.month.ago)") do |s|
21 $since = s
22 end
23
24 opts.on('-e', '--environment ENV', "(slave)") do |env|
25 $environment = env
26 end
27
28 opts.on('--force') { $force = true }
29
30 opts.on('--dump') { $dump = true }
31
32 opts.on('--help') { puts opts; exit }
33end
34opts.parse!
35
36unless $force || ['slave', 'development'].include?($environment)
37 puts "WARNING!: This script should only be run against the slave database"
38 puts "Use --force if you really know what you're doing"
39 exit
40end
41
42ENV['RAILS_ENV'] = $environment
43
44require File.dirname(__FILE__) + '/../config/boot'
45require File.dirname(__FILE__) + '/../config/environment'
46
47ActiveRecord::Base.establish_connection
48$c = ActiveRecord::Base.connection
49
50$since ||= 1.month.ago
51
52puts "Copying from: #{$source_database}"
53puts "Since: #{$since.to_s(:db)}"
54puts
55
56def handle(table)
57 $tables -= [table]
58end
59
60def id_table(table)
61 "ids_for_#{table}"
62end
63
64def copy(table, options = {})
65 puts "Copying #{table}..."
66
67 where = options[:where]
68 unless where
69 conditions = []
70
71 referenced_table = options[:by_ref]
72 if referenced_table
73 foreign_key = options[:foreign_key] || referenced_table.classify.foreign_key
74 conditions << <<_SQL
75 EXISTS (
76 SELECT * FROM #{id_table(referenced_table)}
77 WHERE #{foreign_key} = #{id_table(referenced_table)}.id)
78_SQL
79 end
80
81 if options[:since]
82 column_name = if options[:since].is_a?(String) then options[:since] else 'created_at' end
83 conditions << "#{column_name} > '#{$since.to_s(:db)}'"
84 end
85
86 where = '(' + conditions.join(') AND (') + ')' unless conditions.empty?
87 end
88
89 $c.execute "CREATE TEMPORARY TABLE #{id_table(table)} (id INTEGER NOT NULL, PRIMARY KEY (id))"
90 $c.execute <<_SQL
91 INSERT INTO #{id_table(table)}
92 SELECT id FROM #{$source_database}.#{table}
93 #{"WHERE #{where}" if where}
94_SQL
95 outfile = ("#{$directory}/#{'%03d' % $sequence}-#{table}.sql" if $dump)
96 $sequence += 1
97 $c.execute <<_SQL
98 #{"INSERT INTO texpert_recent.#{table}" unless outfile}
99 SELECT #{$source_database}.#{table}.* FROM #{$source_database}.#{table}
100 INNER JOIN #{id_table(table)} USING (id)
101 #{"INTO OUTFILE '#{outfile}'" if outfile}
102_SQL
103 handle table
104end
105
106def verify_empty(table)
107 puts "Confirming that #{table} is empty..."
108 raise "#{table} not empty!" unless $c.select_value("SELECT COUNT(*) FROM #{$source_database}.#{table}") == '0'
109 handle table
110end
111
112def schema_for(table)
113 $c.select_one("SHOW CREATE TABLE #{$source_database}.#{table}")['Create Table']
114end
115
116$tables = $c.select_values("SHOW TABLES FROM #{$source_database}")
117
118if $dump
119 $directory = "#{FileUtils::pwd}/texpert_recent"
120 FileUtils::mkdir $directory, :mode => 0777
121
122 puts "Copying schema..."
123 File.open("#{$directory}/schema.sql", 'w') do |f|
124 $tables.each do |table|
125 f << schema_for(table);
126 f << ";\n\n"
127 end
128 end
129
130 $sequence = 0
131else
132 puts "Clearing old database..."
133 $c.drop_database "texpert_recent"
134 $c.create_database "texpert_recent"
135
136 puts "Copying schema..."
137 $c.execute "USE texpert_recent"
138 $c.execute "SET FOREIGN_KEY_CHECKS = 0"
139 $tables.each do |table|
140 $c.execute(schema_for(table))
141 end
142 $c.execute "SET FOREIGN_KEY_CHECKS = 1"
143end
144
145$c.transaction do
146 copy 'parent_categories'
147 copy 'super_categories'
148 copy 'categories'
149 copy 'texperts'
150 copy 'rights'
151 copy 'marketing_messages'
152 copy 'promo_codes'
153 copy 'billing_models'
154 copy 'billing_codes'
155 copy 'network_operators'
156
157 copy 'customers', :where => <<_SQL
158 EXISTS (
159 SELECT * FROM #{$source_database}.questions
160 WHERE
161 (
162 created_at > '#{$since.to_s(:db)}'
163 OR EXISTS (
164 SELECT * FROM #{$source_database}.open_questions
165 WHERE question_id = questions.id))
166 AND customer_id = #{$source_database}.customers.id)
167 OR msisdn IN ('stock_answer', 'PrototypicalAnswer')
168_SQL
169
170 copy 'questions', :where => <<_SQL
171 created_at > '#{$since.to_s(:db)}'
172 OR EXISTS (
173 SELECT * FROM #{$source_database}.prototypical_answers
174 WHERE question_id = questions.id)
175 OR EXISTS (
176 SELECT * FROM #{$source_database}.open_questions
177 WHERE question_id = questions.id)
178 OR EXISTS (
179 SELECT * FROM #{$source_database}.stock_answers
180 WHERE question_id = questions.id)
181_SQL
182
183 copy 'viewed_questions'
184
185 copy 'stock_answers'
186 copy 'prototypical_answers'
187 copy 'automatic_answers'
188 copy 'not_automatic_answers'
189 copy 'incoming_messages', :by_ref => 'questions'
190 copy 'mx_telecom_incoming_messages', :by_ref => 'incoming_messages'
191
192 copy 'moniker_groups'
193 copy 'monikers'
194 copy 'outgoing_messages', :by_ref => 'questions'
195 copy 'associated_outgoing_messages', :by_ref => 'outgoing_messages'
196 copy 'mx_telecom_sms_gateway_accounts'
197 copy 'mx_telecom_outgoing_messages', :by_ref => 'outgoing_messages'
198 copy 'mx_telecom_outgoing_sms', :by_ref => 'mx_telecom_outgoing_messages'
199 copy 'sybase_gateway_accounts'
200
201 copy 'mx_telecom_delivery_reports', :where => <<_SQL
202 EXISTS (
203 SELECT * FROM #{id_table('mx_telecom_outgoing_sms')}
204 INNER JOIN #{$source_database}.mx_telecom_outgoing_sms USING (id)
205 WHERE mx_telecom_outgoing_sms.sms_id = mx_telecom_delivery_reports.sms_id)
206_SQL
207
208 copy 'user_delivery_warnings', :by_ref => 'outgoing_messages'
209 copy 'outgoing_message_delivery_informations', :by_ref => 'outgoing_messages'
210 copy 'comments', :by_ref => 'outgoing_messages'
211
212 copy 'sources', :where => <<_SQL
213 EXISTS (
214 SELECT * FROM #{$source_database}.outgoing_messages_sources
215 INNER JOIN #{id_table('outgoing_messages')} ON outgoing_message_id = #{id_table('outgoing_messages')}.id
216 WHERE source_id = sources.id)
217 OR EXISTS (
218 SELECT * FROM #{$source_database}.favourite_sources
219 WHERE source_id = sources.id)
220_SQL
221
222 copy 'favourite_sources'
223
224 copy 'outgoing_messages_sources', :by_ref => 'outgoing_messages'
225 copy 'fair_usage_records', :by_ref => 'outgoing_messages'
226
227 copy 'texpert_time_records', :by_ref => 'questions'
228 copy 'changed_answers', :by_ref => 'incoming_messages'
229 copy 'categoriser_suggestions', :by_ref => 'incoming_messages'
230 copy 'question_time_records', :by_ref => 'questions'
231
232 copy 'meter_periods', :since => 'start'
233 copy 'breaks', :since => 'start'
234 copy 'mentor_unavailability_periods', :since => 'start_time'
235
236 copy 'used_promo_codes', :since => true
237 copy 'sent_marketing_messages', :since => true
238
239 copy 'marketing_triggers'
240 copy 'uber_marketing_messages'
241 copy 'uber_marketing_message_categories'
242 copy 'marketing_generators'
243 copy 'wakeup_message_settings'
244
245 copy 'free_credits', :by_ref => 'customers'
246 copy 'free_credit_creation_audit_trails', :by_ref => 'free_credits'
247 copy 'free_credit_settings'
248 copy 'regular_free_credits', :by_ref => 'customers'
249 copy 'unbilled_outgoing_message_explanations', :by_ref => 'outgoing_messages'
250
251 copy 'active_shift_management_data', :since => true
252 copy 'active_shift_management_statuses', :since => true
253 copy 'active_shift_management_offers', :since => true
254 copy 'active_shift_management_predictions', :since => 'predicted_at'
255 copy 'active_shift_management_settings'
256 copy 'active_shift_management_global_settings'
257 copy 'expected_hourly_demands', :since => 'start_time'
258 copy 'marketing_totals', :since => 'start_time'
259
260 copy 'training_modules'
261 copy 'education_incoming_messages'
262 copy 'training_message_statuses', :by_ref => 'incoming_messages'
263 copy 'training_module_invitations', :since => true
264 copy 'training_notifications', :since => true
265 copy 'training_module_pay_settings'
266 copy 'completed_training_modules'
267
268 copy 'shifts', :since => 'start'
269 copy 'audits', :since => true
270 copy 'leveller_totals', :since => true
271 copy 'leveller_entries', :by_ref => 'outgoing_messages'
272 copy 'staff_availabilities', :since => true
273 copy 'shift_reminders', :since => true
274
275 copy 'one_off_reviews', :by_ref => 'outgoing_messages'
276 copy 'reviewing_periods', :since => 'start_time'
277 copy 'reviews_available_caches', :since => true
278 copy 'review_batch_receipts', :since => 'reviewed_at'
279 copy 'review_settings'
280
281 copy 'marked_customers', :by_ref => 'customers'
282 copy 'customer_service_incidents', :by_ref => 'incoming_messages'
283
284 copy 'shadowed_messages', :by_ref => 'outgoing_messages'
285 copy 'shadowing_records', :by_ref => 'outgoing_messages'
286
287 copy 'sessions'
288
289 copy 'apology_messages'
290 copy 'apology_settings'
291
292 copy 'automatic_answer_histories', :by_ref => 'incoming_messages'
293 copy 'automatic_use_settings'
294
295 copy 'cache_statuses'
296 copy 'texpert_detail_caches'
297
298 copy 'customer_billing_records', :since => true, :by_ref => 'customers'
299 copy 'free_periods', :since => 'end_time', :by_ref => 'customers'
300 copy 'customer_settings'
301 copy 'customer_types'
302
303 copy 'delivery_settings'
304 copy 'email_subjects'
305 copy 'epochs'
306 copy 'internal_email_addresses'
307 copy 'standard_messages'
308
309 copy 'global_pay_settings'
310 copy 'pay_profiles'
311 copy 'incoming_shortcodes'
312 copy 'network_behaviour_settings'
313
314 copy 'mobile_find_signups'
315
316 copy 'global_opt_in_settings'
317 copy 'opt_in_settings'
318 copy 'opt_ins', :by_ref => 'customers', :foreign_key => 'pending_incoming_message_id'
319 copy 'opt_out_settings'
320 copy 'opt_outs', :by_ref => 'questions', :foreign_key => 'opt_out_question_id'
321
322 copy 'otto_panic_subscribers'
323
324 copy 'pattern_business_rules'
325
326 copy 'queue_settings'
327
328 copy 'reaper_time_stamps'
329
330 copy 'response_times', :since => true
331
332 copy 'roles'
333 copy 'titles'
334
335 copy 'schema_migrations'
336
337 copy 'sphinx_counters'
338
339 copy 'warned_words'
340
341 copy 'web_incoming_settings'
342
343 copy 'iphone_incoming_messages', :by_ref => 'incoming_messages'
344 copy 'iphone_outgoing_messages', :by_ref => 'outgoing_messages'
345 copy 'iphone_sms_billing_requests', :by_ref => 'outgoing_messages'
346 copy 'iphone_products'
347 copy 'iphone_store_transactions', :since => true
348 copy 'iphone_customer_sms_accounts', :by_ref => 'customers', :foreign_key => 'iphone_customer_id'
349 copy 'iphone_device_tokens', :since => true, :by_ref => 'customers'
350
351 verify_empty 'active_shift_management_mismatching_statuses'
352 verify_empty 'alert_message_audit_trails'
353 verify_empty 'alert_messages'
354 verify_empty 'alerts'
355 verify_empty 'apology_candidates'
356 verify_empty 'apology_logs'
357 verify_empty 'apology_marketing_messages'
358 verify_empty 'automatic_dq_attempts'
359 verify_empty 'automatic_flight_info_histories'
360 verify_empty 'automatic_lottery_results_histories'
361 verify_empty 'automatic_number_ones_histories'
362 verify_empty 'automatic_weather_forecast_histories'
363 verify_empty 'billing_code_responses'
364 verify_empty 'billing_codes'
365 verify_empty 'billing_messages'
366 verify_empty 'bundle_billing_records'
367 verify_empty 'bundle_special_offer_uses'
368 verify_empty 'bundle_subscriptions'
369 verify_empty 'bundles'
370 verify_empty 'control_group_entries'
371 verify_empty 'control_groups'
372 verify_empty 'customer_alert_billing_codes'
373 verify_empty 'customer_details'
374 verify_empty 'customer_difficulties'
375 verify_empty 'customer_free_category_billing_codes'
376 verify_empty 'customer_nudge_details'
377 verify_empty 'customer_signup_variables'
378 verify_empty 'data_feed_entries'
379 verify_empty 'dq_constraints'
380 verify_empty 'filter_suggestions'
381 verify_empty 'free_category_details'
382 verify_empty 'invalid_cache_entries'
383 verify_empty 'keywords'
384 verify_empty 'log_entries'
385 verify_empty 'map_requests'
386 verify_empty 'missing_intraday_statistics'
387 verify_empty 'nudge_candidates'
388 verify_empty 'nudge_configurations'
389 verify_empty 'nudge_messages'
390 verify_empty 'number_one_albums'
391 verify_empty 'number_one_singles'
392 verify_empty 'number_one_special_cases'
393 verify_empty 'old_free_periods'
394 verify_empty 'question_group_cache_info'
395 verify_empty 'question_group_entries'
396 verify_empty 'question_group_user_cache_entries'
397 verify_empty 'question_group_user_cache_info'
398 verify_empty 'repeat_billing_message_details'
399 verify_empty 'repeat_billing_messages'
400 verify_empty 'repeat_charges'
401 verify_empty 'rota_event_volumes'
402 verify_empty 'sybase_delivery_reports'
403 verify_empty 'sybase_incoming_messages'
404 verify_empty 'sybase_outgoing_messages'
405 verify_empty 'topup_requests'
406 verify_empty 'use_match_evaluations'
407 verify_empty 'wakeup_candidates'
408 verify_empty 'web_incoming_messages'
409 verify_empty 'wizard_uses'
410 verify_empty 'worldpay_transactions'
411end
412
413unless $tables.empty?
414 puts "Unhandled tables:"
415 $tables.each do |table|
416 puts " #{table}"
417 end
418end