Class: Zena::DbHelper::Mysql

Inherits:
Object
  • Object
show all
Extended by:
AbstractDb
Defined in:
lib/zena/db_helper/mysql.rb

Overview

Singleton to help with database queries.

Constant Summary collapse

NOW = 
'now()'
TRUE = 
'1'
TRUE_RESULT = 
'1'
FALSE = 
'0'
DEADLOCK_REGEX =

Deadlock retry

%r{Deadlock found when trying to get lock}
DEADLOCK_MAX_RETRY = 
3

Class Method Summary collapse

Methods included from AbstractDb

adapter, add_column, add_unique_key, change_column, change_engine, connection, date_condition, delete, execute, fetch_attribute, fetch_attributes, fetch_ids, insensitive_find, insert_dummy_ids, insert_many, migrated_once?, next_zip, prepare_connection, quote, quote_column_name, quote_date, select_all, set_attribute, sql_function, table_options, update, update_value

Class Method Details

.add_column(table, column_name, type, opts = {}) ⇒ Object 



14
15
16
17
18
19
20
21
 
# File 'lib/zena/db_helper/mysql.rb', line 14

def add_column(table, column_name, type, opts={})
  # Force the use of :longtext instead of :text
  if type == :text
    execute "ALTER TABLE #{table} ADD COLUMN #{column_name} LONGTEXT"
  else
    super
  end
end

.add_unique_key(table, keys) ⇒ Object 



53
54
55
 
# File 'lib/zena/db_helper/mysql.rb', line 53

def add_unique_key(table, keys)
  execute "ALTER IGNORE TABLE #{table} ADD UNIQUE KEY(#{keys.join(', ')})"
end

.change_column(table, column_name, type, opts = {}) ⇒ Object 



23
24
25
26
27
28
29
 
# File 'lib/zena/db_helper/mysql.rb', line 23

def change_column(table, column_name, type, opts={})
  if type == :text
    execute "ALTER TABLE #{table} CHANGE COLUMN #{column_name} #{column_name} LONGTEXT"
  else
    super
  end
end

.change_engine(table, engine) ⇒ Object 



49
50
51
 
# File 'lib/zena/db_helper/mysql.rb', line 49

def change_engine(table, engine)
  execute "ALTER TABLE #{table} ENGINE = #{engine}"
end

.date_condition(date_cond, field, ref_date) ⇒ Object

This is used by zafu and it’s a mess. ref_date can be a string (‘2005-05-03’) or ruby (‘Time.now’). It should not come uncleaned from evil web.



108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
 
# File 'lib/zena/db_helper/mysql.rb', line 108

def date_condition(date_cond, field, ref_date)
  # raise "DEPRECATED"
  case date_cond
  when 'today', 'current', 'same'
    "DATE(#{field}) = DATE(#{ref_date})"
  when 'week'
    "date_format(#{ref_date},'%Y-%v') = date_format(#{field}, '%Y-%v')"
  when 'month'
    "date_format(#{ref_date},'%Y-%m') = date_format(#{field}, '%Y-%m')"
  when 'year'
    "date_format(#{ref_date},'%Y') = date_format(#{field}, '%Y')"
  when 'upcoming'
    "#{field} >= #{ref_date}"
  else
    # '2008-01-31 23:50' + INTERVAL 1 hour
    if date_cond =~ /^(\+|-|)\s*(\d+)\s*(second|minute|hour|day|week|month|year)/
      count = $2.to_i
      if $1 == ''
        # +/-
        "#{field} > #{ref_date} - INTERVAL #{count} #{$3.upcase} AND #{field} < #{ref_date} + INTERVAL #{count} #{$3.upcase}"
      elsif $1 == '+'
        # x upcoming days
        "#{field} > #{ref_date} AND #{field} < #{ref_date} + INTERVAL #{count} #{$3.upcase}"
      else
        # x days in the past
        "#{field} < #{ref_date} AND #{field} > #{ref_date} - INTERVAL #{count} #{$3.upcase}"
      end
    else
      # bad date_cond
      nil
    end
  end
end

.delete(table, opts) ⇒ Object

‘DELETE’ depending on a two table query.



58
59
60
61
62
 
# File 'lib/zena/db_helper/mysql.rb', line 58

def delete(table, opts)
  tbl1, tbl2 = opts[:from]
  fld1, fld2 = opts[:fields]
  execute "DELETE #{table} FROM #{opts[:from].join(',')} WHERE #{tbl1}.#{fld1} = #{tbl2}.#{fld2} AND #{opts[:where]}"
end

.fetch_attribute(sql) ⇒ Object

Fetch a single row of raw data from db



65
66
67
68
 
# File 'lib/zena/db_helper/mysql.rb', line 65

def fetch_attribute(sql)
  res = execute(sql).fetch_row
  res ? res.first : nil
end

.next_zip(site_id) ⇒ Object 



70
71
72
73
74
75
76
77
78
 
# File 'lib/zena/db_helper/mysql.rb', line 70

def next_zip(site_id)
  res = update "UPDATE zips SET zip=@zip:=zip+1 WHERE site_id = '#{site_id}'"
  if res == 0
    # error
    raise Zena::BadConfiguration, "no zip entry for (#{site_id})"
  end
  rows = execute "SELECT @zip"
  rows.fetch_row[0].to_i
end

.prepare_connection ⇒ Object 



146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
 
# File 'lib/zena/db_helper/mysql.rb', line 146

def prepare_connection
  # Fixes timezone to "+0:0"
  raise "prepare_connection executed too late, connection already active." if Class.new(ActiveRecord::Base).connected?

  ActiveRecord::ConnectionAdapters::MysqlAdapter.class_eval do
    def configure_connection_with_zena
      configure_connection_without_zena
      tz = ActiveRecord::Base.default_timezone == :utc ? "+0:0" : "SYSTEM"
      execute("SET time_zone = '#{tz}'")
      execute("SET collation_connection = 'utf8_unicode_ci'")
    end
    alias_method_chain :configure_connection, :zena
  end

  class << ActiveRecord::Base
    def transaction_with_deadlock_retry(*args, &block)
      retry_count = 0

      begin
        transaction_without_deadlock_retry(*args, &block)
      rescue ActiveRecord::StatementInvalid => error
        # Raise if we are in a nested transaction
        raise if connection.open_transactions != 0
        if error.message =~ DEADLOCK_REGEX
          retry_count += 1
          if retry_count < DEADLOCK_MAX_RETRY
            Node.logger.warn "#{Time.now.strftime('%Y-%m-%d %H:%M:%S')} [#{current_site.host}] Retry (#{retry_count}) #{error.message}"
            retry
          else
            raise
          end
        else
          # Not a deadlock error
          raise
        end
      end
    end
    alias_method_chain :transaction, :deadlock_retry
  end # class << ActiveRecord::Base
end

.quote_date(date) ⇒ Object 



31
32
33
34
35
36
37
 
# File 'lib/zena/db_helper/mysql.rb', line 31

def quote_date(date)
  if date.kind_of?(Time)
    date.strftime('%Y%m%d%H%M%S')
  else
    "''"
  end
end

.sql_function(function, *args) ⇒ Object

Return a string matching the SQLiss function.



81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
 
# File 'lib/zena/db_helper/mysql.rb', line 81

def sql_function(function, *args)
  return args.first unless function
  arg = args.first
  case function
  when 'year'
    "year(#{arg})"
  when 'month'
    "date_format(#{arg},'%Y-%m')"
  when 'week'
    "date_format(#{arg},'%Y-%v')"
  when 'day'
    "DATE(#{arg})"
  when 'random'
    'RAND()'
  when 'min'
    "MIN(#{args.join(',')})"
  when 'max'
    "MAX(#{args.join(',')})"
  when 'coalesce'
    "COALESCE(#{args.join(',')})"
  else
    super
  end
end

.table_options ⇒ Object 



39
40
41
 
# File 'lib/zena/db_helper/mysql.rb', line 39

def table_options
  'ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'
end

.update_value(name, opts) ⇒ Object 



43
44
45
46
47
 
# File 'lib/zena/db_helper/mysql.rb', line 43

def update_value(name, opts)
  tbl1, fld1 = name.split('.')
  tbl2, fld2 = opts[:from].split('.')
  execute "UPDATE #{tbl1},#{tbl2} SET #{tbl1}.#{fld1}=#{tbl2}.#{fld2} WHERE #{opts[:where]}"
end