class SqlPartitioner::SQL
Public Class Methods
compress_lines(string, spaced = true)
click to toggle source
Replace sequences of whitespace (including newlines) with either a single space or remove them entirely (according to param spaced).
Copied from:
https://github.com/datamapper/dm-core/blob/master/lib/dm-core/support/ext/string.rb compress_lines(<<QUERY) SELECT name FROM users QUERY => "SELECT name FROM users"
@param [String] string
The input string.
@param [TrueClass, FalseClass] spaced (default=true)
Determines whether returned string has whitespace collapsed or removed.
@return [String] The input string with whitespace (including newlines) replaced.
# File lib/sql_partitioner/sql_helper.rb, line 117 def self.compress_lines(string, spaced = true) string.split($/).map { |line| line.strip }.join(spaced ? ' ' : '') end
create_partition(table_name, partition_name, until_timestamp)
click to toggle source
# File lib/sql_partitioner/sql_helper.rb, line 48 def self.create_partition(table_name, partition_name, until_timestamp) compress_lines(<<-SQL) ALTER TABLE #{table_name} ADD PARTITION (PARTITION #{partition_name} VALUES LESS THAN (#{until_timestamp})) SQL end
drop_partitions(table_name, partition_names)
click to toggle source
# File lib/sql_partitioner/sql_helper.rb, line 39 def self.drop_partitions(table_name, partition_names) return nil if partition_names.empty? compress_lines(<<-SQL) ALTER TABLE #{table_name} DROP PARTITION #{partition_names.join(',')} SQL end
initialize_partitioning(table_name, partition_data)
click to toggle source
# File lib/sql_partitioner/sql_helper.rb, line 71 def self.initialize_partitioning(table_name, partition_data) partition_sub_query = sort_partition_data(partition_data).map do |partition_name, until_timestamp| "PARTITION #{partition_name} VALUES LESS THAN (#{until_timestamp})" end.join(',') compress_lines(<<-SQL) ALTER TABLE #{table_name} PARTITION BY RANGE(timestamp) (#{partition_sub_query}) SQL end
partition_info()
click to toggle source
SQL
query will return rows having the following columns:
- TABLE_CATALOG - TABLE_SCHEMA - TABLE_NAME - PARTITION_NAME - SUBPARTITION_NAME - PARTITION_ORDINAL_POSITION - SUBPARTITION_ORDINAL_POSITION - PARTITION_METHOD - SUBPARTITION_METHOD - PARTITION_EXPRESSION - SUBPARTITION_EXPRESSION - PARTITION_DESCRIPTION - TABLE_ROWS - AVG_ROW_LENGTH - DATA_LENGTH - MAX_DATA_LENGTH - INDEX_LENGTH - DATA_FREE - CREATE_TIME - UPDATE_TIME - CHECK_TIME - CHECKSUM - PARTITION_COMMENT - NODEGROUP - TABLESPACE_NAME
# File lib/sql_partitioner/sql_helper.rb, line 30 def self.partition_info compress_lines(<<-SQL) SELECT * FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? SQL end
reorg_partitions(table_name, new_partition_data, reorg_partition_name)
click to toggle source
# File lib/sql_partitioner/sql_helper.rb, line 57 def self.reorg_partitions(table_name, new_partition_data, reorg_partition_name) return nil if new_partition_data.empty? partition_suq_query = sort_partition_data(new_partition_data).map do |partition_name, until_timestamp| "PARTITION #{partition_name} VALUES LESS THAN (#{until_timestamp})" end.join(',') compress_lines(<<-SQL) ALTER TABLE #{table_name} REORGANIZE PARTITION #{reorg_partition_name} INTO (#{partition_suq_query}) SQL end
sort_partition_data(partition_data)
click to toggle source
@param [Hash<String,Fixnum>] partition_data hash of name to timestamp @return [Array] array of partitions sorted by timestamp ascending, with the ‘future’ partition at the end
# File lib/sql_partitioner/sql_helper.rb, line 86 def self.sort_partition_data(partition_data) partition_data.to_a.sort do |x,y| if x[1] == "MAXVALUE" 1 elsif y[1] == "MAXVALUE" -1 else x[1] <=> y[1] end end end