Class: Zena::DbHelper::Sqlite3

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

Constant Summary collapse

NOW =
"datetime('now')"
TRUE =
'1'
TRUE_RESULT =
't'
FALSE =
'0'

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_unique_key(table, keys) ⇒ Object



33
34
35
# File 'lib/zena/db_helper/sqlite3.rb', line 33

def add_unique_key(table, keys)
  execute "CREATE UNIQUE INDEX IF NOT EXISTS #{([table] + keys).join('_').gsub(/[^\w]/,'')} ON #{table} (#{keys.join(', ')})"
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.



98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
# File 'lib/zena/db_helper/sqlite3.rb', line 98

def date_condition(date_cond, field, ref_date)
  case date_cond
  when 'today', 'current', 'same'
    "DATE(#{field}) = DATE(#{ref_date})"
  when 'week'
    "strftime('%Y-%W', #{ref_date}) = strftime('%Y-%W', #{field})"
  when 'month'
    "strftime('%Y-%m', #{ref_date}) = strftime('%Y-%m', #{field})"
  when 'year'
    # we multiply by '1' to force a cast to INTEGER so that comparaison against
    # numbers works.
    "strftime('%Y', #{ref_date}) = strftime('%Y', #{field})"
  when 'upcoming'
    "#{field} >= #{ref_date}"
  else
    # date('2008-01-31 23:50','+1 hour')
    if date_cond =~ /^(\+|-|)\s*(\d+)\s*(second|minute|hour|day|week|month|year)/
      count = $2.to_i
      if $1 == ''
        # +/-
        "#{field} > DATE(#{ref_date}, '-#{count} #{$3.upcase}') AND #{field} < DATE(#{ref_date}, '+#{count} #{$3.upcase}')"
      elsif $1 == '+'
        # x upcoming days
        "#{field} > #{ref_date} AND #{field} < DATE(#{ref_date}, '+#{count} #{$3.upcase}')"
      else
        # x days in the past
        "#{field} < #{ref_date} AND #{field} > DATE(#{ref_date}, '-#{count} #{$3.upcase}')"
      end
    else
      # bad date_cond
      nil
    end
  end
end

.delete(table, opts) ⇒ Object

‘DELETE’ depending on a two table query.



38
39
40
41
42
# File 'lib/zena/db_helper/sqlite3.rb', line 38

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

.fetch_attribute(sql) ⇒ Object

Fetch a single row of raw data from db



60
61
62
63
# File 'lib/zena/db_helper/sqlite3.rb', line 60

def fetch_attribute(sql)
  res = execute(sql)
  res.empty? ? nil : res.first[0]
end

.insensitive_find(klass, count, attributes) ⇒ Object



12
13
14
15
16
17
18
19
20
21
22
23
24
25
# File 'lib/zena/db_helper/sqlite3.rb', line 12

def insensitive_find(klass, count, attributes)
  cond = [[]]
  attributes.each do |attribute, value|
    if value.kind_of?(String)
      cond[0] << "lower(#{attribute}) = ?"
      cond << value.downcase
    else
      cond[0] << "#{attribute} = ?"
      cond << value
    end
  end
  cond[0] = cond[0].join(' AND ')
  klass.find(count, :conditions => cond)
end

.insert_many(table, columns, values) ⇒ Object

Insert a list of values (multicolumn insert). The values should be properly escaped before being passed to this method.



46
47
48
49
50
51
52
53
54
55
56
57
# File 'lib/zena/db_helper/sqlite3.rb', line 46

def insert_many(table, columns, values)
  values = values.compact.uniq.map do |list|
    list.map {|e| quote(e)}
  end

  columns = columns.map{|c| quote_column_name(c)}.join(',')

  pre_query = "INSERT INTO #{table} (#{columns}) VALUES "
  values.each do |value|
    execute pre_query + "(#{value.join(',')})"
  end
end

.next_zip(site_id) ⇒ Object



65
66
67
68
69
70
71
72
73
# File 'lib/zena/db_helper/sqlite3.rb', line 65

def next_zip(site_id)
  # FIXME: is there a way to make this thread safe and atomic (like it is with mysql) ?
  res = update "UPDATE zips SET zip=zip+1 WHERE site_id = '#{site_id}'"
  if res == 0
    # error
    raise Zena::BadConfiguration, "no zip entry for (#{site_id})"
  end
  fetch_attribute("SELECT zip FROM zips WHERE site_id = '#{site_id}'").to_i
end

.sql_function(function, arg) ⇒ Object

Return a string matching the SQLiss function.



76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
# File 'lib/zena/db_helper/sqlite3.rb', line 76

def sql_function(function, arg)
  return arg unless function
  case function
  when 'year'
    # we multiply by '1' to force a cast to INTEGER so that comparaison against
    # numbers works.
    "strftime('%Y', #{arg})*1"
  when 'month'
    "strftime('%Y-%m', #{arg})"
  when 'week'
    "strftime('%Y-%W', #{arg})"
  when 'day'
    "DATE(#{arg})"
  when 'random'
    'random()'
  else
    super
  end
end

.update_value(name, opts) ⇒ Object



27
28
29
30
31
# File 'lib/zena/db_helper/sqlite3.rb', line 27

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