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, default_value: ''},
  'char' => {type: 'char', use_params: true, default_value: ''},
  'date' => {type: 'date', default_value: '0000-01-01'},
  'datetime' => {type: 'timestamp', default_value: '0000-01-01'},
  'enum' => {type: 'varchar encode bytedict', default_value: ''},
  'float4' => {type: 'float4', default_value: '0'},
  'float4 unsigned' => {type: 'float4', default_value: '0'},
  'float8' => {type: 'float8', default_value: '0'},
  'float8 unsigned' => {type: 'float8', default_value: '0'},
  'int1' => {type: 'int2', default_value: '0'},
  'int1 unsigned' => {type: 'int2', unsigned: true, default_value: '0'},
  'int2' => {type: 'int2', default_value: '0'},
  'int2 unsigned' => {type: 'int4', unsigned: true, default_value: '0'},
  'int3' => {type: 'int4', default_value: '0'},
  'int3 unsigned' => {type: 'int4', unsigned: true, default_value: '0'},
  'int4' => {type: 'int4', default_value: '0'},
  'int4 unsigned' => {type: 'int8', unsigned: true, default_value: '0'},
  'int8' => {type: 'int8', default_value: '0'},
  'int8 unsigned' => {type: 'numeric(20,0)', unsigned: true, default_value: '0'},
  'numeric' => {type: 'numeric', use_params: true, max_size: [38,37], default_value: '0'},
  'numeric unsigned' => {type: 'numeric', use_params: true, max_size: [38,37], default_value: '0'},
  'text' => {type: 'varchar(max)', default_value: ''},
  'time' => {type: 'timestamp', default_value: '0000-01-01'},
  'varbinary' => {type: 'varchar', use_params: true, max_size: 65535, default_value: ''},
  'varchar' => {type: 'varchar', use_params: true, max_size: 65535, default_value: ''},
}
FLYDATA_CTL_COLUMNS_TABLE = 
"flydata_ctl_columns"
CREATE_FLYDATA_CTL_TABLE_SQL = 
<<EOS
CREATE TABLE %s(
  id integer NOT NULL IDENTITY(1,1),
  table_name varchar(128) NOT NULL,
  column_name varchar(128) NOT NULL,
  src_data_type varchar(1024) NOT NULL,
  revision int NOT NULL DEFAULT 1,
  ordinal_position int NOT NULL,
  PRIMARY KEY(id)
) DISTKEY(table_name) SORTKEY(table_name);
EOS
CREATE_TABLE_SQL = 
<<EOS
DROP TABLE %s;
CREATE TABLE %s (
%s
);
EOS
FLYDATA_CTL_COLUMNS_SQL = 
<<EOS
DELETE FROM %s WHERE table_name = '%s';
INSERT INTO %s (table_name, column_name, src_data_type, ordinal_position) VALUES
EOS

Class Method Summary collapse

Class Method Details

.check_and_replace_max(params, max_size_a) ⇒ Object 



169
170
171
172
173
174
175
176
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 169

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, opt = {}) ⇒ Object 



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 89

def self.column_def_sql(column, opt = {})
  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[:column]}" #{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}"
  elsif column[:not_null] && opt[:for] == :alter_table
    # Redshift doesn't allow adding a not null column without default value
    # Add a defalt value
    line += " DEFAULT '#{type_info[:default_value]}'"
  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, schema_name) ⇒ Object 



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

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

    sql += <<EOS
COMMENT ON COLUMN #{table_name_for_ddl(flydata_tabledef[:table_name], schema_name)}."#{col[:column]}"
IS '#{col[:comment]}';
EOS
  end
  sql
end

.create_flydata_ctl_table_sql(schema_name) ⇒ Object 



64
65
66
67
68
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 64

def self.create_flydata_ctl_table_sql(schema_name)
  # No drop table here intentionally because losing the data is fatal.
  tbl = flydata_ctl_table_for_ddl(schema_name)
  CREATE_FLYDATA_CTL_TABLE_SQL % [tbl, tbl]
end

.create_table_sql(flydata_tabledef, schema_name) ⇒ Object 



77
78
79
80
81
82
83
84
85
86
87
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 77

def self.create_table_sql(flydata_tabledef, schema_name)
  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]
  redshift_tbl = table_name_for_ddl(table_name, schema_name)
  CREATE_TABLE_SQL % [redshift_tbl, redshift_tbl, contents]
end

.escape(text) ⇒ Object 



165
166
167
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 165

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

.flydata_ctl_sql(flydata_tabledef, schema_name) ⇒ Object 



154
155
156
157
158
159
160
161
162
163
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 154

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

.flydata_ctl_table_for_ddl(schema_name) ⇒ Object 



60
61
62
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 60

def self.flydata_ctl_table_for_ddl(schema_name)
  table_name_for_ddl(FLYDATA_CTL_COLUMNS_TABLE, schema_name)
end

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



32
33
34
35
36
37
38
39
40
41
 
# 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)
  schema_name = options[:schema_name]

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

.primary_key_sql(flydata_tabledef) ⇒ Object 



132
133
134
135
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 132

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

.replace_default_value(type, default_value) ⇒ Object 



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

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

.table_name_for_ddl(table_name, schema_name) ⇒ Object 



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

def self.table_name_for_ddl(table_name, schema_name)
  schema_name.to_s.empty? ? "\"#{table_name}\"" : "\"#{schema_name}\".\"#{table_name}\""
end