Class: Zena::DbHelper::Postgresql
- Inherits:
-
Object
- Object
- Zena::DbHelper::Postgresql
- Extended by:
- AbstractDb
- Defined in:
- lib/zena/db_helper/postgresql.rb
Overview
Singleton to help with database queries.
Constant Summary collapse
- NOW =
'now()'
- TRUE =
'true'
- TRUE_RESULT =
't'
- FALSE =
'false'
Class Method Summary collapse
- .add_unique_key(table, keys) ⇒ Object
-
.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
- .next_zip(site_id) ⇒ Object
- .prepare_connection_for_timezone ⇒ 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
29 30 31 |
# File 'lib/zena/db_helper/postgresql.rb', line 29 def add_unique_key(table, keys) execute "ALTER TABLE #{table} ADD CONSTRAINT #{keys.join('_')} UNIQUE (#{keys.join(', ')})" end |
.delete(table, opts) ⇒ Object
‘DELETE’ depending on a two table query.
34 35 36 37 38 39 |
# File 'lib/zena/db_helper/postgresql.rb', line 34 def delete(table, opts) tbl1, tbl2 = opts[:from] fld1, fld2 = opts[:fields] using = opts[:from].reject {|t| t == table} execute "DELETE FROM #{table} USING #{using.join(', ')} WHERE #{tbl1}.#{fld1} = #{tbl2}.#{fld2} AND #{opts[:where]}" end |
.fetch_attribute(sql) ⇒ Object
Fetch a single row of raw data from db
42 43 44 45 |
# File 'lib/zena/db_helper/postgresql.rb', line 42 def fetch_attribute(sql) res = connection.select_rows(sql) res.empty? ? nil : res.first.first end |
.insensitive_find(klass, count, attributes) ⇒ Object
13 14 15 16 17 18 19 20 21 |
# File 'lib/zena/db_helper/postgresql.rb', line 13 def insensitive_find(klass, count, attributes) cond = [[]] attributes.each do |attribute, value| cond[0] << (value.kind_of?(String) ? "#{attribute} ILIKE ?" : "#{attribute} = ?") cond << value end cond[0] = cond[0].join(' AND ') klass.find(count, :conditions => cond) end |
.next_zip(site_id) ⇒ Object
47 48 49 50 51 52 53 54 |
# File 'lib/zena/db_helper/postgresql.rb', line 47 def next_zip(site_id) res = execute("UPDATE zips SET zip=zip+1 WHERE site_id = #{site_id} RETURNING zip").first if res.nil? # error raise Zena::BadConfiguration, "no zip entry for (#{site_id})" end res['zip'].to_i end |
.prepare_connection_for_timezone ⇒ Object
62 63 64 65 66 67 68 69 70 71 72 73 74 |
# File 'lib/zena/db_helper/postgresql.rb', line 62 def prepare_connection_for_timezone # Fixes timezone to "+0:0" raise "prepare_connection_for_timezone executed too late, connection already active." if Class.new(ActiveRecord::Base).connected? ActiveRecord::ConnectionAdapters::PostgreSQLAdapter.class_eval do def configure_connection_with_timezone configure_connection_without_timezone tz = ActiveRecord::Base.default_timezone == :utc ? "UTC" : "SYSTEM" execute("SET TIMEZONE = '#{tz}'") end alias_method_chain :configure_connection, :timezone end end |
.sql_function(function, arg) ⇒ Object
Return a string matching the SQLiss function.
57 58 59 60 |
# File 'lib/zena/db_helper/postgresql.rb', line 57 def sql_function(function, arg) # TODO super end |
.update_value(name, opts) ⇒ Object
23 24 25 26 27 |
# File 'lib/zena/db_helper/postgresql.rb', line 23 def update_value(name, opts) tbl1, fld1 = name.split('.') tbl2, fld2 = opts[:from].split('.') execute "UPDATE #{tbl1} SET #{fld1}=#{tbl2}.#{fld2} FROM #{tbl2} WHERE #{opts[:where]}" end |