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},
  'numeric unsigned' => {type: 'numeric', use_params: true},
  'text' => {type: 'varchar(max)'},
  'time' => {type: 'timestamp'},
  'varbinary' => {type: 'varchar', use_params: true},
  'varchar' => {type: 'varchar', use_params: true},
}
CREATE_FLYDATA_CTL_TABLE_SQL = 
<<EOS
CREATE TABLE flydata_ctl_columns (
  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,
  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 flydata_ctl_columns WHERE table_name = '%s';
INSERT INTO flydata_ctl_columns (table_name, column_name, src_data_type) VALUES
EOS

Class Method Summary collapse

Class Method Details

.column_def_sql(column) ⇒ Object 



74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 74

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

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

  rs_type = (type_info[:use_params] && params && !params.nil?) ?
               type_info[:type] + "#{params}"
             : type_info[:type]
  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 



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

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

    sql += <<EOS
COMMENT ON COLUMN #{flydata_tabledef[:table_name]}."#{col[:name]}"
IS '#{col[:comment]}';
EOS
  end
  sql
end

.create_flydata_ctl_table_sql ⇒ Object 



51
52
53
54
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 51

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 



63
64
65
66
67
68
69
70
71
72
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 63

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 



142
143
144
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 142

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

.flydata_ctl_sql(flydata_tabledef) ⇒ Object 



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

def self.flydata_ctl_sql(flydata_tabledef)
  sql = FLYDATA_CTL_COLUMNS_SQL % [ flydata_tabledef[:table_name] ]
  values = []
  flydata_tabledef[:columns].each do |col|
    values << "('#{flydata_tabledef[:table_name]}', '#{col[:name]}', '#{escape(col[:type])}')"
  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 



110
111
112
113
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 110

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 



100
101
102
103
104
105
106
107
108
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 100

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