Class: Zena::DbHelper::Postgresql

Inherits:
Object
  • Object
show all
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

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_timezoneObject



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