· 8 years ago · May 24, 2018, 04:12 AM
1#!/usr/bin/env ruby
2
3# DB Trickle Archiver (db_trickle.rb) v1.1.0
4#
5# The MIT License
6#
7# Copyright (c) 2007,2008 John M Lauck (john AT recaffeinated d0t com / isosceles @ rubyforge)
8#
9# Permission is hereby granted, free of charge, to any person obtaining a copy
10# of this software and associated documentation files (the "Software"), to deal
11# in the Software without restriction, including without limitation the rights
12# to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
13# copies of the Software, and to permit persons to whom the Software is
14# furnished to do so, subject to the following conditions:
15#
16# The above copyright notice and this permission notice shall be included in
17# all copies or substantial portions of the Software.
18#
19# THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
20# IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
21# FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
22# AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
23# LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
24# OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
25# THE SOFTWARE.
26
27require 'rubygems'
28require 'mysql'
29require 'cooloptions'
30require 'log4r'
31require 'strscan'
32
33include Log4r
34
35# Date time method for parsing mysql datetime
36module DateTimeImport
37
38 PRINTABLE_DATETIME_FORMAT = '%Y-%m-%d %H:%M:%S'
39 MYSQL_DATETIME_FORMAT = '%Y-%m-%d %H:%M:%S'
40 LOGGER = Logger.new('log')
41 LOGGER.level = DEBUG
42 LOGGER.outputters = Outputter.stdout
43
44 # Detects time in unix time stamp or mysql datetime format
45 # and creates a ruby Time object
46 def DateTimeImport.convert_time(time)
47 case time.to_s
48
49 # unix time
50 when /^\d+$/
51 LOGGER.info "Using unix time format for #{time.to_s}"
52 get_time_from_unixtime(time.to_s)
53
54 # mysql datetime
55 when /^(\d{2,4})-(\d{1,2})-(\d{1,2}) (\d{1,2}):(\d{1,2}):(\d{1,2})$/
56 LOGGER.info "Using mysql datetime format for #{time.to_s}"
57 get_time_from_mysql_datetime(time.to_s)
58 else
59 nil
60 end
61 end
62
63 # convert a time from unix timestamp to ruby Time obj
64 def DateTimeImport.get_time_from_unixtime(unixtime)
65 rubytime = nil
66
67 begin
68 rubytime = Time.at(unixtime.to_i)
69 rescue TypeError => te
70 rubytime = nil
71 LOGGER.error "Error in type conversion from unix time format."
72 end
73
74 rubytime
75 end
76
77 # Convert a time from mysql datetime to ruby time object
78 def DateTimeImport.get_time_from_mysql_datetime(mysql_datetime)
79 datetime_parser = StringScanner.new(mysql_datetime)
80 datetime_parser.scan(/^(\d{2,4})-(\d{1,2})-(\d{1,2}) (\d{1,2}):(\d{1,2}):(\d{1,2})$/)
81 year=datetime_parser[1]
82 month=datetime_parser[2]
83 day=datetime_parser[3]
84 hour=datetime_parser[4]
85 min=datetime_parser[5]
86 sec=datetime_parser[6]
87 rubytime = nil
88
89 begin
90 rubytime = Time.mktime(year, month, day, hour, min, sec, 0)
91 rescue ArgumentError => ae
92 LOGGER.error "Error converting time format."
93 rubytime = nil
94 end
95
96 rubytime
97 end
98
99end
100
101# ===========
102# = Methods =
103# ===========
104
105# gets the schema of the source table
106def get_schema_struct(table_name)
107 dbres = do_sql_command("DESC #{table_name};")
108
109 dbstruct = []
110
111 if(dbres) then
112 dbres.each_hash do | row |
113 dbstruct_hash = {}
114 row.each {|key, val|
115 dbstruct_hash[key.downcase.to_sym] = val
116 }
117 dbstruct << dbstruct_hash
118 end
119 end
120
121 dbstruct
122end
123
124# takes the slightly modified hash from a mysql result and creates a table schema
125# this ignores keys other than the pk
126def get_pkey_fields(table_struct)
127 pkeys = []
128
129 table_struct.each do | row |
130 pkeys << row[:field] if row[:key] == 'PRI'
131 end
132
133 pkeys
134end
135
136# takes the slightly modified hash from a mysql result and creates a table schema
137# this ignores keys other than the pk
138def get_schema_sql(table_struct, table_name = NEW_TABLE_NAME)
139 dbstruct = []
140 pkeys = []
141
142 table_struct.each do | row |
143 dbstruct << "`#{row[:field]}` #{row[:type]} #{(!row[:default].nil? && row[:default] != '' ) ? "default '#{row[:default]}'" : ''} #{row[:null] == 'NO' ? 'NOT NULL' : ''}"
144 pkeys << "`#{row[:field]}`" if row[:key] == 'PRI'
145 end
146
147 dbstruct << "PRIMARY KEY (%s)" % [pkeys.join(', ')]
148 dbstring = "CREATE TABLE `%s` (\n\t%s\n)" % [table_name, dbstruct.join(",\n\t")]
149
150 dbstring
151end
152
153# take given a set of keys and a row return a hash for the primary key index of this row,
154# this means whatever this returns can be used to lookup the row
155def make_key_hash_for_row(keys, row)
156 key_hash = {}
157 keys.each {|k|
158 key_hash[k] = row[k]
159 }
160
161 key_hash
162end
163
164# grabs the rows from the src db
165def grab_rows(field, src_table_name = TABLE_NAME, num_rows = ROWS_PER_TRANSACTION)
166 LOGGER.info "Creating select statement based on field `#{field[:name]}` (#{field[:type]})"
167
168 if !(field[:type] =~ /int/).nil?
169 LOGGER.info "Using integer type for select."
170 sql = "SELECT * FROM `%s` WHERE `%s` >= '%s' AND `%s` < '%s' ORDER BY `%s` LIMIT %s;" % [ Mysql::escape_string(src_table_name),
171 Mysql::escape_string(field[:name]),
172 Mysql::escape_string(field[:min].to_i.to_s),
173 Mysql::escape_string(field[:name]),
174 Mysql::escape_string(field[:max].to_i.to_s),
175 Mysql::escape_string(field[:name]),
176 num_rows]
177 elsif !(field[:type] =~ /datetime/).nil?
178 LOGGER.info "Using datetime type for select."
179 sql = "SELECT * FROM `%s` WHERE `%s` >= '%s' AND `%s` < '%s' ORDER BY `%s` LIMIT %s;" % [ Mysql::escape_string(src_table_name),
180 Mysql::escape_string(field[:name]),
181 Mysql::escape_string(field[:min].strftime(MYSQL_DATETIME_FORMAT)),
182 Mysql::escape_string(field[:name]),
183 Mysql::escape_string(field[:max].strftime(MYSQL_DATETIME_FORMAT)),
184 Mysql::escape_string(field[:name]),
185 num_rows]
186 else
187 LOGGER.info "Using default type for select, this isn't expected."
188 sql = "SELECT * FROM `%s` WHERE `%s` >= '%s' AND `%s` < '%s' ORDER BY `%s` LIMIT %s;" % [ Mysql::escape_string(src_table_name),
189 Mysql::escape_string(field[:name]),
190 Mysql::escape_string(field[:min]),
191 Mysql::escape_string(field[:name]),
192 Mysql::escape_string(field[:max]),
193 Mysql::escape_string(field[:name]),
194 num_rows]
195 end
196
197 LOGGER.debug "SQL: #{sql}"
198 dbres = do_sql_command(sql)
199 dbres
200end
201
202# inserts rows and returns the rows to delete
203def insert_rows(rows, field, table_struct, dest_table_name = NEW_TABLE_NAME)
204 fields = get_fields(table_struct)
205 insert_tmplt = row_sql_insert(dest_table_name, table_struct)
206 primary_keys = get_pkey_fields(table_struct)
207 errs = []
208 row_action_data = []
209 del_keys = []
210
211 if (rows) then
212 rows.each_hash do | row |
213 row_action_data << {
214 :sql_insert => make_sql_insert_row(fields, insert_tmplt, row),
215 :key => make_key_hash_for_row(primary_keys, row)
216 }
217 end
218 end
219
220 row_action_data.each { |row|
221 begin
222 dbres = do_sql_command(row[:sql_insert])
223 if dbres.nil?
224 del_keys << row[:key]
225 end
226 rescue Mysql::Error
227 if !($! =~ /^Duplicate entry .* for key/).nil?
228 # i'll consider a duplicate entry okay for a delete
229 LOGGER.warn "Database error! Duplicate key found on insert, marking for deletion anyway, moving on: #{$!}"
230 del_keys << row[:key]
231 else
232 #errs << "Database error, moving on: #{$!}"
233 LOGGER.error "Database error, not sure what, moving on: #{$!}"
234 end
235 end
236 }
237
238 del_keys
239end
240
241# copy rows from src table to dest table
242def copy_rows( field,
243 table_struct,
244 src_table_name = TABLE_NAME,
245 dest_table_name = NEW_TABLE_NAME,
246 num_rows = ROWS_PER_TRANSACTION)
247 rows = grab_rows(field, src_table_name, num_rows)
248 keys_for_delete = insert_rows(rows, field, table_struct, dest_table_name)
249 keys_for_delete
250end
251
252# copies and then deletes rows in chunks of num_rows size
253def move_rows( field,
254 table_struct,
255 src_table_name = TABLE_NAME,
256 dest_table_name = NEW_TABLE_NAME,
257 num_rows = ROWS_PER_TRANSACTION,
258 max_rows = MAX_RECORDS,
259 sleepy_time = SLEEP_TIME)
260 iteration = 1
261 count = 0
262
263 # prime the pump vars for loop
264 keys_for_delete = []
265
266 if max_rows != 0 && max_rows < num_rows
267 LOGGER.info "Adjusting per row transaction due to maximum row limit. This move will only require one transaction."
268 upper_bound = max_rows
269 num_rows = max_rows
270 else
271 upper_bound = num_rows
272 end
273
274 lower_bound = 1
275 max_iterations = max_rows == 0 ? 0 : (max_rows.to_f/num_rows.to_f).ceil
276 remaining_rows = max_rows % num_rows
277
278 while ((iteration == 1 || !keys_for_delete.empty?) && (max_iterations == 0 || iteration <= max_iterations)) do
279 # sleep if we need another iteration
280 if iteration > 1
281 LOGGER.info "Sleeping for #{sleepy_time}"
282 sleep sleepy_time
283 end
284
285 LOGGER.debug "upper_bound: #{upper_bound}\nlower_bound: #{lower_bound}\nnum_rows: #{num_rows}\niteration: #{iteration}\nmax_rows: #{max_rows}\nmax_rows: #{max_iterations}"
286
287 LOGGER.info "Starting move transactions iteration #{iteration} (records #{lower_bound} to #{upper_bound})"
288
289 LOGGER.info "Copying up to #{num_rows} rows..."
290 keys_for_delete = copy_rows(field, table_struct, src_table_name, dest_table_name, num_rows)
291 LOGGER.info "...done"
292
293 if keys_for_delete.size > 0
294 count += keys_for_delete.size
295 LOGGER.info "Deleting #{keys_for_delete.size} rows..."
296 delete_rows(keys_for_delete, src_table_name)
297 LOGGER.info "...done\n"
298 else
299 LOGGER.info "No rows to delete. This could be a problem, but should just mean that it's last iteration."
300 end
301
302 # do calculations for next iterations
303 iteration += 1
304
305 upper_bound = (iteration * num_rows)
306 lower_bound = upper_bound - num_rows + 1
307
308 # this is the last iteration
309 if remaining_rows != 0 && iteration == max_iterations
310 LOGGER.info "Last iteration, only a partial per transaction is needed."
311 num_rows = remaining_rows
312 upper_bound = max_rows
313 end
314
315 end
316
317 count
318end
319
320def row_sql_delete(src_table_name)
321 sql = <<-EOF
322 DELETE FROM `#{src_table_name}`
323 WHERE
324 %s
325 LIMIT 1;
326 EOF
327
328 sql
329end
330
331def make_where_clause(keys)
332 clauses = []
333 keys.each { |key|
334 tmp_clause = []
335 key.each { |i, val|
336 tmp_clause << "`#{i}` = '#{Mysql::escape_string(val)}'"
337 }
338 clauses << tmp_clause.join(' AND ')
339 }
340
341 clauses
342end
343
344def make_sql_delete_rows(src_table_name, keys)
345 whereless_sql = row_sql_delete(src_table_name)
346 where_clauses = make_where_clause(keys)
347
348 sql_deletes = []
349 where_clauses.each {|where|
350 sql_deletes << whereless_sql % where
351 }
352
353 sql_deletes
354end
355
356def delete_rows(keys, src_table_name = TABLE_NAME)
357 errs = []
358 sql_delete_statements = make_sql_delete_rows(src_table_name, keys)
359
360 sql_delete_statements.each{ |sql|
361 LOGGER.debug "DELETE SQL: #{sql}"
362 begin
363 dbres = do_sql_command(sql)
364 rescue Mysql::Error
365 LOGGER.error "Database error, moving on: #{$!}"
366 end
367 }
368
369end
370
371def get_fields(table_struct)
372 fields = []
373 table_struct.each do | row |
374 fields.push << row[:field]
375 end
376
377 fields
378end
379
380def make_sql_insert_row(field_names, row_template, row)
381 values = []
382 field_names.each { |field|
383 values << Mysql::escape_string(row[field])
384 }
385 sql = row_template % values
386
387 sql
388end
389
390# makes a template for sql row inserts (based on the current schema)
391def row_sql_insert(table_name, table_struct)
392 fields = get_fields(table_struct)
393
394 sql = <<-EOF
395 INSERT INTO `#{DBNAME}`.`#{table_name}` (
396 #{fields.collect { |f| "`#{f}`" }.join(", ")}
397 )
398 VALUES (
399 #{fields.collect { |f| "'%s'" }.join(", ")}
400 );
401 EOF
402
403 sql
404end
405
406# creates the new table (really just wrapps the do sql method, but make its readable)
407# that new method kind of makes this useless
408def create_new_table(table_sql)
409 begin
410 do_sql_command(table_sql)
411 LOGGER.info "Successfully reated new table: \n#{table_sql}\n"
412 rescue Mysql::Error
413 if ($!.to_s =~ /^Table .* already exists$/) == 0
414 LOGGER.warn "Database error, duplicate table is okay, moving on: #{$!}"
415 else
416 LOGGER.error "Database error, moving on: #{$!}"
417 end
418 end
419end
420
421# get the field type (useful to determine if a time field is datetime or timestamp/integer)
422def get_field_datatype(table_struct, field_name = FIELD_NAME)
423 datatype = nil
424
425 table_struct.each { |row|
426 break if !datatype.nil?
427 if row[:field] == field_name
428 datatype = row[:type]
429 end
430 }
431
432 datatype
433end
434
435# optimize table
436def optimize_table(table)
437 result = do_sql_command("OPTIMIZE TABLE `#{Mysql::escape_string(table)}`")
438 if !result['Msg_type'].nil? && !result['Msg_text'].nil?
439 {:type => result['Msg_type'], :text => result['Msg_text']}
440 else
441 nil
442 end
443end
444
445# just pass a sql command and this does it all and returns the result
446def do_sql_command(sql, use_global_connection = true, close_connection = false)
447 begin
448
449 if use_global_connection
450 dbconn = open_conn
451 else
452 dbconn = open_conn(false)
453 end
454
455 dbres = dbconn.query(sql)
456
457 rescue Mysql::Error => err
458 raise err
459 ensure
460 close_conn(dbconn) if close_connection
461 end
462
463 dbres
464end
465
466# returns true if the global database connection is available
467def is_global_db_connected?
468 if defined?($global_db_conn).nil? || $global_db_conn.class != Mysql || $global_db_conn.stat == 'MySQL server has gone away'
469 return false
470 end
471
472 return true
473end
474
475# opens a database conneciton with the set params, uses a global connection by default but can create a new connection and return that instead
476def open_conn(global = true, set_wait = true, host = DBHOST, user = DBUSER, pass = DBPASS, name = DBNAME)
477 LOGGER.debug "Opening db connection"
478 conn = nil
479
480 #use global connection
481 if global
482 LOGGER.debug "Using global database connection"
483
484 # global connection is already defined
485 if is_global_db_connected?
486 LOGGER.debug "Global connection is set, just giving it back"
487 conn = $global_db_conn
488
489 # global connection is not defined or not set
490 else
491
492 # open new global connection
493 LOGGER.debug "Global connection isn't defined or isn't set; attempting to reconnect..."
494 begin
495 $global_db_conn = Mysql::new(host, user, pass, name)
496 conn = $global_db_conn
497
498 if set_wait && defined?(DBWAIT)
499 LOGGER.debug "Settign wait time for db connection to #{DBWAIT}"
500 sql = "SET SESSION WAIT_TIMEOUT = #{DBWAIT};"
501 dbres = conn.query(sql)
502 end
503
504 rescue Mysql::Error => err
505 LOGGER.error "Error making db connection: #{$1}"
506 raise err
507 end
508 end
509
510 # don't use global connection
511 else
512 LOGGER.debug "Not using global connection, creating anew..."
513 # open new global connection
514
515 begin
516 conn = Mysql::new(host, user, pass, name)
517
518 if set_wait && defined?(DBWAIT)
519 LOGGER.debug "Settign wait time for db connection to #{DBWAIT}"
520 sql = "SET SESSION WAIT_TIMEOUT = #{DBWAIT};"
521 dbres = conn.query(sql)
522 end
523 rescue Mysql::Error => err
524 LOGGER.error "Error making db connection: #{$1}"
525 raise err
526 end
527 end
528
529 conn
530end
531
532# close the database connection
533def close_conn(conn)
534 LOGGER.debug "Closing db connection"
535
536 begin
537 conn.close if conn
538 rescue Mysql::Error => err
539 LOGGER.error "Error closing db connection: #{$1}"
540 end
541
542end
543
544def determine_log_level(level)
545 case level
546 when 1
547 DEBUG
548 when 2
549 INFO
550 when 3
551 WARN
552 when 4
553 ERROR
554 when 5
555 FATAL
556 else
557 FATAL
558 end
559end
560
561def parse_command_line_options
562 options = CoolOptions.parse!("[options] <table name> <field name>") do |o|
563 o.desc %q(Archive a database table in small bite size portions.
564Note: Time zones are ignored in all dates and comparisons are based on >= start and < end, so the start is inclusive and ends is not.
565New table schemas ignore the "extra" params. This is because auto_increment fields could interfere with adding records and keeping referential integrity.
566)
567 o.on "db-host DATABASE_HOST", "Database host name"
568 o.on "name DATABASE_NAME", "Database name"
569 o.on "user DATABASE_USER", "Database user"
570 o.on "password DATABASE_PASS", "Database password"
571
572 o.on "default", "Start now and run for an hour (previous hours worth of records) (ignore start, end and lapse params)", false
573 o.on "start TIME", "Start time (mysql or epoch). A lower time bound. ex 2007-10-01 16:00:00", ''
574 o.on "end TIME", "End time (mysql or epoch). An upper time bound. ex 2007-10-01 17:00:00", ''
575 o.on "lapse SECONDS", "Time lapse from start in seconds.", DEFAULT_TIME_LAPSE
576 o.on "max-records NUMBER", "Number of records to copy. 0 = all", DEFAULT_MAX_RECORDS
577 o.on "records-per-transaction NUMBER", "Number of records to grab per transaction", DEFAULT_RECORDS_PER_TRANSACTION
578 o.on "wait-time SECONDS", "Number of seconds to sleep between move transactions", DEFAULT_SLEEP_TIME
579 o.on "output-log-level NUMBER", "Log level, 0-5 (off, debug, info, warn, error, fatal)", DEFAULT_LOG_LEVEL
580 o.on "table-name DEST_TABLE_NAME", "Destination table name. Default table name is <table_name>_<datestamp>.", false
581
582 o.after do |r|
583
584 o.error("Error: Max records value must be an integer numeric value.") if (r.max_records =~ /^\d+$/).nil?
585 o.error("Error: Per transaction value must be an integer numeric value.") if (r.records_per_transaction =~ /^\d+$/).nil?
586 o.error("Error: Wait time must be an integer numeric value.") if (r.wait_time =~ /^\d+$/).nil?
587 o.error("Error: Log level must be a numeric value 1-5") if (r.output_log_level =~ /^\d+$/).nil? || (r.output_log_level.to_i > 5) || (r.output_log_level.to_i < 0)
588
589 r.max_records = r.max_records.to_i
590 r.wait_time = r.wait_time.to_i
591 r.records_per_transaction = r.records_per_transaction.to_i
592 r.output_log_level = r.output_log_level.to_i
593
594 # generally test purposes, these defaults could be changed:
595 if r.default
596 r.end = SCRIPT_RUN_TIME #now
597 r.lapse = 60 # one minute
598 r.start = SCRIPT_RUN_TIME - r.lapse
599
600 # start and end were specified
601 elsif (r.start != '' && r.end != '' && r.lapse == 0)
602 r.start = DateTimeImport.convert_time(r.start)
603 r.end = DateTimeImport.convert_time(r.end)
604
605 o.error("Error: Start date format is invalid. For best results, use YYYY-MM-DD HH:MM:SS") if r.start.nil?
606 o.error("Error: End date format is invalid. For best results, use YYYY-MM-DD HH:MM:SS") if r.end.nil?
607 o.error("Error: Start date must be priaor to end date.") if r.start >= r.end
608
609 r.lapse = (r.end - r.start).to_i
610
611 # if you aren't using the default you have to specify a start or end
612 elsif (r.start != '' && r.end != '' && r.lapse != 0)
613 o.error("Error: Please specify either start/end, start/lapse, end/lapse options")
614
615 elsif (r.start == '' && r.end == '')
616 o.error("Error: Start or end must be specified.")
617
618 # we know the start and end both aren't empty, so check the other options
619 # this means the start was specified and the lapse was nil
620 elsif (r.lapse == 0 && r.start != '')
621 o.error("Error: A lapse or end time must be specified")
622
623 # this means the end was specified, but no start or lapse
624 elsif (r.lapse == 0 && r.end != '')
625 o.error("Error: A lapse or start time must be specified")
626
627 # using start and lapse
628 elsif (r.start != '' && r.lapse != 0)
629 o.error("Error: Lapse must be an integer value") if (r.lapse.to_s =~ /^[0-9]+$/).nil?
630
631 r.lapse = r.lapse.to_i
632 r.start = DateTimeImport.convert_time(r.start)
633 r.end = (r.start + r.lapse)
634
635 o.error("Error: Start date format is invalid. For best results, use 'YYYY-MM-DD HH:MM:SS' or a unix time integer value.") if r.start.nil?
636 o.error("Error: End date format is invalid. For best results, use 'YYYY-MM-DD HH:MM:SS' or a unix time integer value.") if r.end.nil?
637 o.error("Error: Start date must be priaor to end date.") if r.start >= r.end
638
639 # using end and lapse
640 elsif (r.end != '' && r.lapse != 0)
641 o.error("Error: Lapse must be an integer value") if (r.lapse.to_s =~ /^[0-9]+$/).nil?
642
643 r.lapse = r.lapse.to_i
644 r.end = DateTimeImport.convert_time(r.end)
645 r.start = r.end - r.lapse
646
647 o.error("Error: Start date format is invalid. For best results, use 'YYYY-MM-DD HH:MM:SS' or a unix time integer value.") if r.start.nil?
648 o.error("Error: End date format is invalid. For best results, use 'YYYY-MM-DD HH:MM:SS' or a unix time integer value.") if r.end.nil?
649 o.error("Error: Start date must be priaor to end date.") if r.start >= r.end
650 end
651
652 end
653 end
654
655 options
656end
657
658# ======================
659# = Constants and more =
660# ======================
661
662# constants
663DEFAULT_MAX_RECORDS = 0
664DEFAULT_RECORDS_PER_TRANSACTION = 10
665DEFAULT_TIME_LAPSE = 0
666DEFAULT_SLEEP_TIME = 5
667DEFAULT_LOG_LEVEL = 2
668
669MYSQL_DATETIME_FORMAT = '%Y-%m-%d %H:%M:%S'
670PRINTABLE_DATETIME_FORMAT = '%Y-%m-%d %H:%M:%S'
671SECONDS_IN_WEEK = 604800
672SCRIPT_RUN_TIME = Time.now
673FIELD_NAME_TYPE = ['int', 'datetime'] # this is for the future, to automatically pull out the field name
674
675# parse command line options
676OPTIONS = parse_command_line_options()
677
678TABLE_NAME = ARGV.shift || nil
679NEW_TABLE_NAME = OPTIONS.table_name.is_a?(String) ? OPTIONS.table_name : (!TABLE_NAME.nil? ? "#{TABLE_NAME}_#{SCRIPT_RUN_TIME.strftime("%Y%m%d")}" : nil)
680FIELD_NAME = ARGV.shift || nil
681START_TIME = OPTIONS.start
682TIME_LAPSE = OPTIONS.lapse
683END_TIME = OPTIONS.end
684MAX_RECORDS = OPTIONS.max_records
685ROWS_PER_TRANSACTION = OPTIONS.records_per_transaction
686SLEEP_TIME = OPTIONS.wait_time
687LOGGER = Logger.new('log')
688LOGGER.level = determine_log_level(OPTIONS.output_log_level)
689if OPTIONS.output_log_level > 0
690 LOGGER.outputters = Outputter.stdout
691end
692
693DBHOST = OPTIONS.db_host
694DBUSER = OPTIONS.user
695DBPASS = OPTIONS.password
696DBNAME = OPTIONS.name
697DBWAIT = 43200
698
699
700$global_db_conn = nil
701
702
703# ========
704# = Main =
705# ========
706
707if !TABLE_NAME.nil?
708 LOGGER.info "Starting..."
709 LOGGER.info "Records from #{START_TIME.strftime(PRINTABLE_DATETIME_FORMAT)} to #{END_TIME.strftime(PRINTABLE_DATETIME_FORMAT)} are being moved from #{TABLE_NAME} to #{NEW_TABLE_NAME}"
710 LOGGER.info "This is a period of #{TIME_LAPSE} seconds, but could be interrupted at the limit of #{MAX_RECORDS} records"
711 LOGGER.info "Note: Time zones are ignored and comparisons are based on >= start and < end, so the start is inclusive and ends is not."
712
713 table_struct_hash = get_schema_struct(TABLE_NAME)
714 create_new_table(get_schema_sql(table_struct_hash))
715 field_data = {:name => FIELD_NAME, :min => START_TIME, :max => END_TIME, :type => get_field_datatype(table_struct_hash)}
716 move_row_count = move_rows(field_data, table_struct_hash)
717 close_conn($global_db_conn)
718 LOGGER.info "Moved #{move_row_count} rows."
719 LOGGER.info "You should probably run OPTIMIZE TABLE `#{TABLE_NAME}`."
720end