Class: Zena::DbHelper::Sqlite3
- Inherits:
-
Object
- Object
- Zena::DbHelper::Sqlite3
- 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
- .add_unique_key(table, keys) ⇒ Object
-
.date_condition(date_cond, field, ref_date) ⇒ Object
This is used by zafu and it’s a mess.
-
.delete(table, opts) ⇒ Object
‘DELETE’ depending on a two table query.
-
.fetch_attribute(sql) ⇒ Object
Fetch a single row of raw data from db.
- .insensitive_find(klass, count, attributes) ⇒ Object
-
.insert_many(table, columns, values) ⇒ Object
Insert a list of values (multicolumn insert).
- .next_zip(site_id) ⇒ Object
-
.sql_function(function, arg) ⇒ Object
Return a string matching the SQLiss function.
- .update_value(name, opts) ⇒ Object
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 |