Class: SqlPartitioner::SQL

Inherits:
Object
  • Object
show all
Defined in:
lib/sql_partitioner/sql_helper.rb

Class Method Summary collapse

Class Method Details

.compress_lines(string, spaced = true) ⇒ String

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"

Parameters:

  • string (String) —

    The input string.

  • spaced (TrueClass, FalseClass) (defaults to: true) —

    (default=true) Determines whether returned string has whitespace collapsed or removed.

Returns:

  • (String) —

    The input string with whitespace (including newlines) replaced.



117
118
119
 
# 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) ⇒ Object 



48
49
50
51
52
53
54
55
 
# 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) ⇒ Object 



39
40
41
42
43
44
45
46
 
# 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) ⇒ Object 



71
72
73
74
75
76
77
78
79
80
81
 
# 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 ⇒ Object

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



30
31
32
33
34
35
36
37
 
# 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) ⇒ Object 



57
58
59
60
61
62
63
64
65
66
67
68
69
 
# 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) ⇒ Array

Returns array of partitions sorted by timestamp ascending, with the ‘future’ partition at the end.

Parameters:

  • partition_data (Hash<String,Fixnum>) —

    hash of name to timestamp

Returns:

  • (Array) —

    array of partitions sorted by timestamp ascending, with the ‘future’ partition at the end



86
87
88
89
90
91
92
93
94
95
96
 
# 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