Module: Repertoire::Faceting::PostgreSQLAdapter

Includes:
MigrationMethods
Included in:
ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
Defined in:
lib/repertoire-faceting/adapters/postgresql_adapter.rb

Overview

:nodoc:

Defined Under Namespace

Modules: MigrationMethods

Instance Method Summary collapse

Methods included from MigrationMethods

#create_materialized_view, #drop_materialized_view, #faceting_api_sql, #refresh_materialized_view

Instance Method Details

#current_facet_api_binding ⇒ Object

Returns the currently active faceting API binding (only when installed as an extension)



33
34
35
 
# File 'lib/repertoire-faceting/adapters/postgresql_adapter.rb', line 33

def current_facet_api_binding
  @current_api_binding ||= select_value "SELECT extname FROM pg_extension WHERE extname LIKE 'faceting%';"
end

#facet_api_bindings ⇒ Object

Returns the available in-database faceting APIs (only when installed as an extension)



28
29
30
 
# File 'lib/repertoire-faceting/adapters/postgresql_adapter.rb', line 28

def facet_api_bindings
  @api_bindings ||= select_values "SELECT name FROM pg_available_extensions WHERE name LIKE 'faceting%'"
end

#facet_schema ⇒ Object

Methods for access to facet indices



41
42
43
44
45
 
# File 'lib/repertoire-faceting/adapters/postgresql_adapter.rb', line 41

def facet_schema
  # TODO. Not clear how to get the schema associated with a PostgreSQL extension from the
  #       system tables. So we limit to loading into a schema named 'facet' for now.
  'facet'
end

#facet_table_name(model_name, name) ⇒ Object 



47
48
49
 
# File 'lib/repertoire-faceting/adapters/postgresql_adapter.rb', line 47

def facet_table_name(model_name, name)
  "#{facet_schema}.#{model_name}_#{name}_index"
end

#indexed_facets(model_name) ⇒ Object 



51
52
53
54
55
56
 
# File 'lib/repertoire-faceting/adapters/postgresql_adapter.rb', line 51

def indexed_facets(model_name)
  sql = "SELECT matviewname FROM pg_matviews WHERE schemaname = 'facet'"
  tables = select_values(sql)

  tables.grep(/#{model_name}_(\w+)_(\d*)?index/) { $1 }
end

#mask_members_sql(masks, table_name, faceting_id) ⇒ Object 



117
118
119
120
121
 
# File 'lib/repertoire-faceting/adapters/postgresql_adapter.rb', line 117

def mask_members_sql(masks, table_name, faceting_id)
  bit_and = " OPERATOR(#{facet_schema}.&) "
  exprs = masks.map { |mask| "(#{mask.to_sql})" }
  "INNER JOIN #{facet_schema}.members(#{exprs.join(bit_and)}) AS _refinements_id ON (#{table_name}.#{faceting_id} = _refinements_id)"
end

#population(facet, masks, signatures) ⇒ Object

Methods for running facet value counts



89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
 
# File 'lib/repertoire-faceting/adapters/postgresql_adapter.rb', line 89

def population(facet, masks, signatures)
  # Would be nice to use Arel here... but recent versions (~ 2.0.1) have removed the property of closure under
  # composition (e.g. joining two select managers / sub-selects)... why?!?
  sigs  = [ 'fct.signature' ]
  exprs = masks.map{|mask| "(#{mask.to_sql})"}
  sigs << 'mask.signature' unless masks.empty?

  bit_and = " OPERATOR(#{facet_schema}.&) "

  sql  = "SELECT fct.#{facet.facet_name}, #{facet_schema}.count(#{ sigs.join(bit_and) }) "
  sql += "FROM (#{signatures.to_sql}) AS fct "
  sql += ", (SELECT (#{exprs.join(bit_and)}) AS signature) AS mask " unless masks.empty?
  sql += "ORDER BY #{facet.order_values.join(', ')} " if facet.order_values.present?
  sql += "OFFSET #{facet.offset_value} "              if facet.offset_value.present?
  sql += "LIMIT #{facet.limit_value} "                if facet.limit_value.present?

  # run query and type cast
  results = query(sql)
  results = results.map { |key, count| [ key, count.to_i] }
  results = ActiveSupport::OrderedHash[results]

  # minimums and nils
  results.reject! { |key, count| count < (facet.minimum_value || 1) }
  results.delete(nil)            if facet.nils_value == :exclude

  results
end

#signature_wastage(table_name, faceting_id) ⇒ Object

Methods for managing packed id columns on models



62
63
64
65
66
 
# File 'lib/repertoire-faceting/adapters/postgresql_adapter.rb', line 62

def signature_wastage(table_name, faceting_id)
  sql = "SELECT #{facet_schema}.wastage(#{faceting_id}) FROM #{table_name}"
  result = select_value(sql)
  Float(result)
end

#stat_table(table_name, column = "updated_at") ⇒ Object

Methods for detecting table content changes

(If a later version of PostgreSQL can hashcode a table/timestamp in the system catalog, switch to use that instead.)



74
75
76
77
78
79
80
81
82
83
 
# File 'lib/repertoire-faceting/adapters/postgresql_adapter.rb', line 74

def stat_table(table_name, column="updated_at")
  sql = "SELECT COUNT(#{column}), MAX(#{column}) AS timestamp FROM #{table_name}"
  result = select_one(sql)
  result = HashWithIndifferentAccess.new({
    :count     => Integer(result["count"]),
    :timestamp => Time.parse(result["timestamp"])
  })

  result
end