Class: Flydata::TableDef::RedshiftTableDef
- Inherits:
-
Object
- Object
- Flydata::TableDef::RedshiftTableDef
- 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 DROP TABLE %s; 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
- .check_and_replace_max(params, max_size_a) ⇒ Object
- .column_def_sql(column) ⇒ Object
- .comment_sql(flydata_tabledef, schema_name) ⇒ Object
- .create_flydata_ctl_table_sql(schema_name) ⇒ Object
- .create_table_sql(flydata_tabledef, schema_name) ⇒ Object
- .escape(text) ⇒ Object
- .flydata_ctl_sql(flydata_tabledef, schema_name) ⇒ Object
- .flydata_ctl_table_for_ddl(schema_name) ⇒ Object
- .from_flydata_tabledef(flydata_tabledef, options = {}) ⇒ Object
- .primary_key_sql(flydata_tabledef) ⇒ Object
- .replace_default_value(type, default_value) ⇒ Object
- .table_name_for_ddl(table_name, schema_name) ⇒ Object
Class Method Details
.check_and_replace_max(params, max_size_a) ⇒ Object
166 167 168 169 170 171 172 173 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 166 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
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 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 90 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, schema_name) ⇒ Object
134 135 136 137 138 139 140 141 142 143 144 145 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 134 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[:name]}" IS '#{col[:comment]}'; EOS end sql end |
.create_flydata_ctl_table_sql(schema_name) ⇒ Object
65 66 67 68 69 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 65 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
78 79 80 81 82 83 84 85 86 87 88 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 78 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
162 163 164 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 162 def self.escape(text) text.gsub("'", "\\\\'") end |
.flydata_ctl_sql(flydata_tabledef, schema_name) ⇒ Object
151 152 153 154 155 156 157 158 159 160 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 151 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[:name]}', '#{escape(col[:type])}', #{i})" end sql += values.join(",\n") + ';' sql end |
.flydata_ctl_table_for_ddl(schema_name) ⇒ Object
61 62 63 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 61 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, = {}) [:flydata_ctl_table] = true unless .has_key?(:flydata_ctl_table) schema_name = [:schema_name] tabledef = "" tabledef += create_flydata_ctl_table_sql(schema_name) if [:flydata_ctl_table] tabledef += create_table_sql(flydata_tabledef, schema_name) tabledef += comment_sql(flydata_tabledef, schema_name) tabledef += flydata_ctl_sql(flydata_tabledef, schema_name) end |
.primary_key_sql(flydata_tabledef) ⇒ Object
129 130 131 132 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 129 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
119 120 121 122 123 124 125 126 127 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 119 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
57 58 59 |
# File 'lib/flydata/table_def/redshift_table_def.rb', line 57 def self.table_name_for_ddl(table_name, schema_name) schema_name.to_s.empty? ? "\"#{table_name}\"" : "\"#{schema_name}\".\"#{table_name}\"" end |