Class: Rmap::Table

Inherits:
Object
  • Object
show all
Defined in:
lib/rmap/table.rb

Constant Summary collapse

BINARY_FILTER_METHODS = 
{
  'eq' => lambda {|left,right| "#{left} = #{right}"},
  'ne' => lambda {|left,right| "#{left} != #{right}"}, 
  'lt' => lambda {|left,right| "#{left} < #{right}"}, 
  'gt' => lambda {|left,right| "#{left} > #{right}"}, 
  'le' => lambda {|left,right| "#{left} <= #{right}"}, 
  'ge' => lambda {|left,right| "#{left} >= #{right}"}, 
  'contains' => lambda {|left,right| "#{left} like concat('%', #{right}, '%')"}, 
  'begins_with' => lambda {|left,right| "#{left} like concat('%', #{right})"}, 
  'ends_with' => lambda {|left,right| "#{left} like concat(#{right}, '%')"},
  'year_eq' => lambda {|left,right| "year(#{left}) = #{right}"},
  'year_ne' => lambda {|left,right| "year(#{left}) != #{right}"}, 
  'year_lt' => lambda {|left,right| "year(#{left}) < #{right}"}, 
  'year_gt' => lambda {|left,right| "year(#{left}) > #{right}"}, 
  'year_le' => lambda {|left,right| "year(#{left}) <= #{right}"}, 
  'year_ge' => lambda {|left,right| "year(#{left}) >= #{right}"},
  'month_eq' => lambda {|left,right| "month(#{left}) = #{right}"},
  'month_ne' => lambda {|left,right| "month(#{left}) != #{right}"}, 
  'month_lt' => lambda {|left,right| "month(#{left}) < #{right}"}, 
  'month_gt' => lambda {|left,right| "month(#{left}) > #{right}"}, 
  'month_le' => lambda {|left,right| "month(#{left}) <= #{right}"}, 
  'month_ge' => lambda {|left,right| "month(#{left}) >= #{right}"},
  'day_eq' => lambda {|left,right| "day(#{left}) = #{right}"},
  'day_ne' => lambda {|left,right| "day(#{left}) != #{right}"}, 
  'day_lt' => lambda {|left,right| "day(#{left}) < #{right}"}, 
  'day_gt' => lambda {|left,right| "day(#{left}) > #{right}"}, 
  'day_le' => lambda {|left,right| "day(#{left}) <= #{right}"}, 
  'day_ge' => lambda {|left,right| "day(#{left}) >= #{right}"},
  'hour_eq' => lambda {|left,right| "hour(#{left}) = #{right}"},
  'hour_ne' => lambda {|left,right| "hour(#{left}) != #{right}"}, 
  'hour_lt' => lambda {|left,right| "hour(#{left}) < #{right}"}, 
  'hour_gt' => lambda {|left,right| "hour(#{left}) > #{right}"}, 
  'hour_le' => lambda {|left,right| "hour(#{left}) <= #{right}"}, 
  'hour_ge' => lambda {|left,right| "hour(#{left}) >= #{right}"},
  'minute_eq' => lambda {|left,right| "minute(#{left}) = #{right}"},
  'minute_ne' => lambda {|left,right| "minute(#{left}) != #{right}"}, 
  'minute_lt' => lambda {|left,right| "minute(#{left}) < #{right}"}, 
  'minute_gt' => lambda {|left,right| "minute(#{left}) > #{right}"}, 
  'minute_le' => lambda {|left,right| "minute(#{left}) <= #{right}"}, 
  'minute_ge' => lambda {|left,right| "minute(#{left}) >= #{right}"},
  'second_eq' => lambda {|left,right| "second(#{left}) = #{right}"},
  'second_ne' => lambda {|left,right| "second(#{left}) != #{right}"}, 
  'second_lt' => lambda {|left,right| "second(#{left}) < #{right}"}, 
  'second_gt' => lambda {|left,right| "second(#{left}) > #{right}"}, 
  'second_le' => lambda {|left,right| "second(#{left}) <= #{right}"}, 
  'second_ge' => lambda {|left,right| "second(#{left}) >= #{right}"},
}

Instance Attribute Summary collapse

Instance Method Summary collapse

Constructor Details

#initialize(database, name) ⇒ Table

Returns a new instance of Table.



54
55
56
57
58
59
60
61
 
# File 'lib/rmap/table.rb', line 54

def initialize(database, name)
  @database = database
  @name = name
  @binary_filter_methods_args = {}
  Table::BINARY_FILTER_METHODS.each {|name, block| @binary_filter_methods_args[name] = []}
  @join_list = []
  @order_by_list = []
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(name, *args) ⇒ Object 



84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
# File 'lib/rmap/table.rb', line 84

def method_missing name, *args
  if @database.table? name
    Table.new(@database, name).join(self, *args)
  elsif column? name
    all.map{|row| row.fetch(name).first}
  elsif column?(name.to_s.sub(/=\Z/, "")) && name.match(/\A(.*)=\Z/)
    all.each{|row| row.update($1 => args[0])}
  elsif name.match /\A(.*?)_(#{BINARY_FILTER_METHODS.keys.join('|')})\Z/
    @binary_filter_methods_args[$2].push([$1.split(/_or_/),args[0]])
    self
  else
    super
  end
end

Instance Attribute Details

#name ⇒ Object

Returns the value of attribute name.



52
53
54
 
# File 'lib/rmap/table.rb', line 52

def name
  @name
end

Instance Method Details

#add(name, type, options = {}) ⇒ Object 



293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
 
# File 'lib/rmap/table.rb', line 293

def add(name, type, options = {})
  case type
  when :string
    @database.client.query("alter table `#{@name}` add `#{name}` varchar(255) not null")
  when :text
    @database.client.query("alter table `#{@name}` add `#{name}` longtext not null")
  when :binary
    @database.client.query("alter table `#{@name}` add `#{name}` longblob not null")
  when :integer
    @database.client.query("alter table `#{@name}` add `#{name}` int signed not null")
  when :foreign_key
    @database.client.query("alter table `#{@name}` add `#{name}` int unsigned not null")
    @database.client.query("alter table `#{@name}` add index(`#{name}`)")
  when :date
    @database.client.query("alter table `#{@name}` add `#{name}` date not null")
  when :datetime
    @database.client.query("alter table `#{@name}` add `#{name}` datetime not null")
  when :boolean
    @database.client.query("alter table `#{@name}` add `#{name}` enum('true', 'false') not null")
  when :decimal
    @database.client.query("alter table `#{@name}` add `#{name}` decimal not null")
  end
end

#all(limit = nil) ⇒ Object 



253
254
255
256
257
258
259
 
# File 'lib/rmap/table.rb', line 253

def all(limit = nil)
  out = []
  @database.client.query(generate_select_sql('id', limit), :as => :hash).each do |row|
    out.push(Row.new(@database, @name, row['id']))
  end
  out
end

#column?(name) ⇒ Boolean

Returns:

  • (Boolean) 


80
81
82
 
# File 'lib/rmap/table.rb', line 80

def column?(name)
  @database.client.query("describe #{@name} `#{name}`").count > 0
end

#column_names ⇒ Object 



321
322
323
 
# File 'lib/rmap/table.rb', line 321

def column_names
  @database.client.query("describe `#{@name}`", :as => :hash).map {|row| row['Field']}
end

#count(limit = nil) ⇒ Object 



249
250
251
 
# File 'lib/rmap/table.rb', line 249

def count(limit = nil)
  @database.client.query(generate_select_sql('id', limit)).count
end

#delete ⇒ Object 



274
275
276
 
# File 'lib/rmap/table.rb', line 274

def delete
  each {|row| row.delete}
end

#drop ⇒ Object 



288
289
290
291
 
# File 'lib/rmap/table.rb', line 288

def drop
  eval("@table_names = nil", @database.bindings)
  @database.client.query("drop table `#{@name}`")
end

#each(&block) ⇒ Object 



261
262
263
264
 
# File 'lib/rmap/table.rb', line 261

def each &block
  all.each &block
  nil
end

#first ⇒ Object 



266
267
268
 
# File 'lib/rmap/table.rb', line 266

def first
  all(1).first
end

#format_sql(sql) ⇒ Object 



99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/rmap/table.rb', line 99

def format_sql(sql)
  out_buffer = []
  sql = sql.to_s
  while sql.length > 0
    if sql.match(/\A(\s+|\d+\.\d+|\d+|\w+\()(.*)/)
      out_buffer.push($1)
      sql = $2
    elsif sql.match(/\A(\w+)\.(\w+)(.*)/)
      out_buffer.push("`#{$1}`.`#{$2}`")
      sql = $3
    elsif sql.match(/\A(\w+)(.*)/)
      out_buffer.push("`#{@name}`.`#{$1}`")
      sql = $2
    else
      sql.match(/\A(.)(.*)/)
      out_buffer.push($1)
      sql = $2
    end
  end 
  out_buffer.join
end

#generate_filter_conditions_sql ⇒ Object 



125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
 
# File 'lib/rmap/table.rb', line 125

def generate_filter_conditions_sql
  and_sql = []
  Table::BINARY_FILTER_METHODS.each do |name, block|
    @binary_filter_methods_args[name].each do |args|
      or_sql = []
      if args[0].class.name == 'Array'
        args[0].each do |left|
          or_sql.push(block.call(format_sql(left.to_s), quote(args[1])))
        end
      elsif args[1].class.name == 'Array'
        args[1].each do |right|
          or_sql.push(block.call(format_sql(args[0].to_s), quote(right)))
        end
      else
        or_sql.push(block.call(format_sql(args[0].to_s), quote(args[1])))
      end
      and_sql.push("(" + or_sql.join(' or ') + ")")
    end
  end
  @join_list.each do |join|
    and_sql.push(join[:table].generate_filter_conditions_sql)
  end
  and_sql.join(' and ')
end

#generate_from_sql ⇒ Object 



162
163
164
 
# File 'lib/rmap/table.rb', line 162

def generate_from_sql
  "from " + generate_table_list_sql
end

#generate_group_by_sql ⇒ Object 



211
212
213
214
215
 
# File 'lib/rmap/table.rb', line 211

def generate_group_by_sql
  if @join_list.count > 0
    "group by #{name}.id"
  end
end

#generate_inner_join_conditions_sql ⇒ Object 



182
183
184
185
186
187
188
189
190
191
192
 
# File 'lib/rmap/table.rb', line 182

def generate_inner_join_conditions_sql
  out = []
  @join_list.each do |join|
    out.push(generate_join_condition_sql(self, join[:table], join[:options][:using]))
    inner_join_conditions_sql = join[:table].generate_inner_join_conditions_sql
    if inner_join_conditions_sql != ''
      out.push(inner_join_conditions_sql)
    end
  end
  out.join(" and ")
end

#generate_join_condition_sql(table1, table2, foreign_key) ⇒ Object 



166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
 
# File 'lib/rmap/table.rb', line 166

def generate_join_condition_sql(table1, table2, foreign_key)
  if !foreign_key.nil?
    if table2.column? foreign_key
      "#{table1.name}.id = #{table2.name}.#{foreign_key}"
    else
      "#{table1.name}.#{foreign_key} = #{table2.name}.id"
    end
  else
    if table2.column? "#{to_singular(table1.name)}_id"
      "#{table1.name}.id = #{to_singular(table2.name)}.#{table1.name}_id"
    else
      "#{table1.name}.#{to_singular(table2.name)}_id = #{table2.name}.id"
    end
  end
end

#generate_order_by_sql ⇒ Object 



217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
 
# File 'lib/rmap/table.rb', line 217

def generate_order_by_sql
  out = []
  @order_by_list.each do |order_by|
    (sql_expression, desc) = order_by
    if desc
      out.push "#{format_sql(sql_expression)} desc"
    else
      out.push "#{format_sql(sql_expression)} asc"
    end
  end
  @join_list.each do |join|
    order_by_sql = join[:table].generate_order_by_sql
    if order_by_sql != ''
      out.push(order_by_sql)
    end
  end
  out = out.join(', ')
  if out != ''
    out = "order by #{out}"
  end
  out
end

#generate_select_sql(expression_list_sql, limit = nil) ⇒ Object 



240
241
242
243
244
245
246
247
 
# File 'lib/rmap/table.rb', line 240

def generate_select_sql(expression_list_sql, limit = nil)
  if !limit.nil?
    limit_sql = "limit #{limit}"
  else 
    limit_sql = ''
  end
  "select #{format_sql(expression_list_sql)} #{generate_from_sql} #{generate_where_sql} #{generate_group_by_sql} #{generate_order_by_sql} #{limit_sql}"
end

#generate_table_list_sql ⇒ Object 



150
151
152
153
154
155
156
157
158
159
160
 
# File 'lib/rmap/table.rb', line 150

def generate_table_list_sql
  out = []
  out.push(name)
  @join_list.each do |join|
    table_list_sql = join[:table].generate_table_list_sql
    if table_list_sql != ''
      out.push(table_list_sql)
    end
  end
  out.join(', ')
end

#generate_where_sql ⇒ Object 



194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
 
# File 'lib/rmap/table.rb', line 194

def generate_where_sql
  out = []
  inner_join_conditions_sql = generate_inner_join_conditions_sql
  if inner_join_conditions_sql != ''
    out.push inner_join_conditions_sql
  end
  filter_conditions_sql = generate_filter_conditions_sql
  if filter_conditions_sql != ''
    out.push filter_conditions_sql
  end
  out = out.join(' and ')
  if out != ''
    out = "where #{out}"
  end
  out
end

#insert(hash) ⇒ Object 



278
279
280
 
# File 'lib/rmap/table.rb', line 278

def insert(hash)
  @database.client.query("insert into `#{@name}`(#{(hash.map{|k,v| "`#{k}`"}).join(', ')}) values(#{(hash.map{|k,v| quote(v)}).join(', ')})")
end

#join(table, options = {}) ⇒ Object 



70
71
72
73
 
# File 'lib/rmap/table.rb', line 70

def join(table, options = {})
  @join_list.push({:table => table, :options => options});
  self
end

#order_by(sql_exression, desc = false) ⇒ Object 



75
76
77
78
 
# File 'lib/rmap/table.rb', line 75

def order_by(sql_exression, desc = false)
  @order_by_list.push([sql_exression, desc])
  self
end

#quote(data) ⇒ Object 



121
122
123
 
# File 'lib/rmap/table.rb', line 121

def quote(data)
  "'" + @database.client.escape(data.to_s) +  "'"
end

#remove(name) ⇒ Object 



317
318
319
 
# File 'lib/rmap/table.rb', line 317

def remove(name)
  @database.client.query("alter table `#{@name}` drop `#{name}`")
end

#sum(sql_expression, limit = nil) ⇒ Object 



282
283
284
285
286
 
# File 'lib/rmap/table.rb', line 282

def sum(sql_expression, limit = nil)
  out = 0
  @database.client.query(generate_select_sql("sum(#{sql_expression})", limit), :as => :array).each{|row| out += row.first}
  out
end

#to_s ⇒ Object 



325
326
327
 
# File 'lib/rmap/table.rb', line 325

def to_s
  all.to_s
end

#update(hash) ⇒ Object 



270
271
272
 
# File 'lib/rmap/table.rb', line 270

def update(hash)
  all.each {|row| row.update(hash)}
end