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, max_size: 65535},
}
CREATE_FLYDATA_CTL_TABLE_SQL = 
<<EOS
DROP TABLE flydata_ctl_columns;
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,
  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 flydata_ctl_columns WHERE table_name = '%s';
INSERT INTO flydata_ctl_columns (table_name, column_name, src_data_type, ordinal_position) VALUES
EOS

Class Method Summary collapse

Class Method Details

.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
105
106
 
# 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?
              if type_info[:max_size] && /\d+/.match(params) && params.to_i > type_info[:max_size]
                params = type_info[:max_size]
              end
              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 



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

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 



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 



150
151
152
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 150

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

.flydata_ctl_sql(flydata_tabledef) ⇒ Object 



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

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 



118
119
120
121
 
# File 'lib/flydata/table_def/redshift_table_def.rb', line 118

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 



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

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