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},
}
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 



165
166
167
168
169
170
171
172
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 165

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 



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
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 89

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[: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}"
  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 



133
134
135
136
137
138
139
140
141
142
143
144
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 133

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 



161
162
163
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 161

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

.flydata_ctl_sql(flydata_tabledef, schema_name) ⇒ Object 



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

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 



128
129
130
131
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 128

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 



118
119
120
121
122
123
124
125
126
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 118

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