Class: Flydata::TableDef::RedshiftTableDef

Inherits:
Object
  • Object
show all
Defined in:
lib/flydata/table_def/redshift_table_def.rb

Constant Summary collapse

TYPE_MAP_F2R = 
{
  'binary' => {type: 'varchar', use_params: true},
  'char' => {type: 'char', use_params: true},
  'date' => {type: 'date'},
  'datetime' => {type: 'timestamp'},
  'enum' => {type: 'varchar encode bytedict'},
  'float4' => {type: 'float4'},
  'float4 unsigned' => {type: 'float4'},
  'float8' => {type: 'float8'},
  'float8 unsigned' => {type: 'float8'},
  'int1' => {type: 'int2'},
  'int1 unsigned' => {type: 'int2', unsigned: true},
  'int2' => {type: 'int2'},
  'int2 unsigned' => {type: 'int4', unsigned: true},
  'int3' => {type: 'int4'},
  'int3 unsigned' => {type: 'int4', unsigned: true},
  'int4' => {type: 'int4'},
  'int4 unsigned' => {type: 'int8', unsigned: true},
  'int8' => {type: 'int8'},
  'int8 unsigned' => {type: 'numeric(20,0)', unsigned: true},
  'numeric' => {type: 'numeric', use_params: true, max_size: [38,37]},
  'numeric unsigned' => {type: 'numeric', use_params: true, max_size: [38,37]},
  'text' => {type: 'varchar(max)'},
  'time' => {type: 'timestamp'},
  'varbinary' => {type: 'varchar', use_params: true, max_size: 65535},
  'varchar' => {type: 'varchar', use_params: true, max_size: 65535},
}
CREATE_FLYDATA_CTL_TABLE_SQL = 
"DROP TABLE flydata_ctl_columns;\nCREATE TABLE flydata_ctl_columns (\n  id integer NOT NULL IDENTITY(1,1),\n  table_name varchar(128) NOT NULL,\n  column_name varchar(128) NOT NULL,\n  src_data_type varchar(1024) NOT NULL,\n  revision int NOT NULL DEFAULT 1,\n  ordinal_position int NOT NULL,\n  PRIMARY KEY(id)\n) DISTKEY(table_name) SORTKEY(table_name);\n"
CREATE_TABLE_SQL = 
"DROP TABLE %s;\nCREATE TABLE %s (\n%s\n);\n"
FLYDATA_CTL_COLUMNS_SQL = 
"DELETE FROM flydata_ctl_columns WHERE table_name = '%s';\nINSERT INTO flydata_ctl_columns (table_name, column_name, src_data_type, ordinal_position) VALUES\n"

Class Method Summary collapse

Class Method Details

.check_and_replace_max(params, max_size_a) ⇒ Object 



152
153
154
155
156
157
158
159
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 152

def self.check_and_replace_max(params, max_size_a)
    final_params = []
    params.split(",").each_with_index do |param, i|
      final_params << (/\d+/.match(param) && max_size_a[i] && param.to_i > max_size_a[i].to_i ? 
                          max_size_a[i] : param)
    end
    final_params.join(",")
end

.column_def_sql(column) ⇒ Object 



77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 77

def self.column_def_sql(column)
  type = column[:type]
  if type =~ /\((.*?)\)/
    type = $` + $'
    params = $1
  end

  type_info = TYPE_MAP_F2R[type]
  raise "Unsupported type '#{column[:type]}'" if type_info.nil?

  rs_type = if type_info[:use_params] && params && !params.nil?
              params = check_and_replace_max(params, Array(type_info[:max_size])) if type_info[:max_size]
              type_info[:type] + "(#{params})"
            else
              type_info[:type]
            end
  line = %Q|  "#{column[:name]}" #{rs_type}|
  line += " NOT NULL" if column[:not_null]
  if (column.has_key?(:default))
    val = replace_default_value(type_info[:type], column[:default])
    line += " DEFAULT #{val}"
  end
# Commented out because no IDENTITY column must be used for a replicated table.
# Values come from the master.
#    line += " IDENTITY(1, 1)" if (column[:auto_increment])

  line
end

.comment_sql(flydata_tabledef) ⇒ Object 



121
122
123
124
125
126
127
128
129
130
131
132
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 121

def self.comment_sql(flydata_tabledef)
  sql = ""
  flydata_tabledef[:columns].each do |col|
    next unless col[:comment]

    sql += "COMMENT ON COLUMN \#{flydata_tabledef[:table_name]}.\"\#{col[:name]}\"\nIS '\#{col[:comment]}';\n"
  end
  sql
end

.create_flydata_ctl_table_sql ⇒ Object 



54
55
56
57
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 54

def self.create_flydata_ctl_table_sql
  # No drop table here intentionally because losing the data is fatal.
  CREATE_FLYDATA_CTL_TABLE_SQL
end

.create_table_sql(flydata_tabledef) ⇒ Object 



66
67
68
69
70
71
72
73
74
75
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 66

def self.create_table_sql(flydata_tabledef)
  lines = flydata_tabledef[:columns].collect{|column| column_def_sql(column) }
  pk_def = primary_key_sql(flydata_tabledef)
  lines << pk_def if pk_def

  contents = lines.join(",\n")

  table_name = flydata_tabledef[:table_name]
  CREATE_TABLE_SQL % [table_name, table_name, contents]
end

.escape(text) ⇒ Object 



148
149
150
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 148

def self.escape(text)
  text.gsub("'", "\\\\'")
end

.flydata_ctl_sql(flydata_tabledef) ⇒ Object 



138
139
140
141
142
143
144
145
146
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 138

def self.flydata_ctl_sql(flydata_tabledef)
  sql = FLYDATA_CTL_COLUMNS_SQL % [ flydata_tabledef[:table_name] ]
  values = []
  flydata_tabledef[:columns].each.with_index(1) do |col, i|
    values << "('#{flydata_tabledef[:table_name]}', '#{col[:name]}', '#{escape(col[:type])}', #{i})"
  end
  sql += values.join(",\n") + ';'
  sql
end

.from_flydata_tabledef(flydata_tabledef, options = {}) ⇒ Object 



32
33
34
35
36
37
38
39
40
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 32

def self.from_flydata_tabledef(flydata_tabledef, options = {})
  options[:flydata_ctl_table] = true unless options.has_key?(:flydata_ctl_table)

  tabledef = ""
  tabledef += create_flydata_ctl_table_sql if options[:flydata_ctl_table]
  tabledef += create_table_sql(flydata_tabledef)
  tabledef += comment_sql(flydata_tabledef)
  tabledef += flydata_ctl_sql(flydata_tabledef)
end

.primary_key_sql(flydata_tabledef) ⇒ Object 



116
117
118
119
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 116

def self.primary_key_sql(flydata_tabledef)
  pks = flydata_tabledef[:columns].select{|col| col[:primary_key]}.collect{|col| col[:name]}
  pks.empty? ? nil : "  PRIMARY KEY (#{pks.join(',')})"
end

.replace_default_value(type, default_value) ⇒ Object 



106
107
108
109
110
111
112
113
114
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 106

def self.replace_default_value(type, default_value)
  val = default_value ? "'#{default_value}'" : "NULL"
  case type
  when 'timestamp'
    (val.upcase == "'CURRENT_TIMESTAMP'") ? 'SYSDATE' : val
  else
    val
  end
end