Module: RailsOnPg::Views

Defined in:
lib/rails_on_pg/views.rb

Defined Under Namespace

Classes: ViewDefinition, ViewNotExistException

Instance Method Summary collapse

Instance Method Details

#create_view(name, options = {}, &block) ⇒ Object

Create new view name - name of view Example: 

create_view :active_patients do |v|
  v.select 'p.patient_id as id' ,'p.id as visit_id'
  v.from 'patients as p'
  v.join 'left join demographics d on d.visit_id=v.id'
  v.join 'left join diagnoses di on di.visit_id=v.id and di.row_index=0'
  v.conditions 'p.status'=>'active','p.name' => 'John' #or "p.status='active' and p.name='John'"
end

See ViewDefinition class



15
16
17
18
19
20
 
# File 'lib/rails_on_pg/views.rb', line 15

def create_view name, options={}, &block      
  view_def = ViewDefinition.new name, &block
  
  drop_views name, options[:dependent_views]      
  execute view_def.to_sql
end

#drop_views(name, defs = nil) ⇒ Object

drop dependent views before if exists Options :dependent_views - if view has dependent views(views where current view used) then you need list them here



60
61
62
63
64
65
66
67
68
 
# File 'lib/rails_on_pg/views.rb', line 60

def drop_views name, defs=nil
  defs = defs.delete(:dependent_views) if defs.is_a?(Hash)
  defs.each do |dependent_view|
    execute "DROP VIEW IF EXISTS #{dependent_view}"
  end if defs
  
  execute "DROP VIEW IF EXISTS #{name}"

end

#recreate_view(name) ⇒ Object

recreate view without changes



71
72
73
74
75
76
77
 
# File 'lib/rails_on_pg/views.rb', line 71

def recreate_view name
  view_structure = ActiveRecord::Base.connection.select_value("select definition from pg_views where viewname='#{name}'")
  if view_structure
    execute "DROP VIEW IF EXISTS #{name}"
    execute "CREATE VIEW #{name} AS #{view_structure};"
  end
end

#update_view(name, type, columns, options = {}) ⇒ Object

Update view’s select columns name - name of existed view type - type of action(:add,:remove or :replace) columns - array of columns or string options - options Options: :dependent_views - if view has dependent views(views where current view used) then you need list them here Example:

update_view :active_patients, :add, ['p.first_name as name','p.age as dob']
update_view :active_patients, :add, 'p.first_name as name', :dependent_views=>['view0','view1']
update_view :active_patients, :remove, 'p.first_name as name', :dependent_views=>['view0','view1']
update_view :active_patients, :replace, ['p.first_name as name','p.age as dob'] #replace all select columns



34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/rails_on_pg/views.rb', line 34

def update_view name, type, columns, options={}
  view_structure = ActiveRecord::Base.connection.select_value("select definition from pg_views where viewname='#{name}'")
  raise ViewNotExistException("View #{name} does not exist in current db") unless view_structure
  
  columns_str = columns.is_a?(Array) ? columns.join(',') : columns
  
  select_pattern = /select (.*) from/i
  select_str = view_structure[select_pattern,1]

  case type
    when :add
      view_structure.gsub!(select_pattern, "SELECT #{select_str}, #{columns_str} FROM")
    when :remove
      select_str.gsub!(", #{columns_str}", '')
      view_structure.gsub!(select_pattern, "SELECT #{select_str} FROM")
    when :replace
      view_structure.gsub!(select_pattern, "SELECT #{columns_str} FROM")
  end

  drop_views name, options[:dependent_views] 
  execute "CREATE VIEW #{name} AS #{view_structure};"
end