Module: Spectacles::SchemaStatements::PostgreSQLAdapter

Includes:
AbstractAdapter
Defined in:
lib/spectacles/schema_statements/postgresql_adapter.rb

Instance Method Summary collapse

Methods included from AbstractAdapter

#create_view, #create_view_statement, #drop_view, #drop_view_statement, #materialized_view_exists?, #view_exists?

Instance Method Details

#create_materialized_view(view_name, *args) ⇒ Object 



112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 112

def create_materialized_view(view_name, *args)
  options = args.extract_options!
  build_query = args.shift

  raise "#create_materialized_view requires a query or block" if build_query.nil? && !block_given?

  build_query = yield if block_given?
  build_query = build_query.to_sql if build_query.respond_to?(:to_sql)

  if options[:force] && materialized_view_exists?(view_name)
    drop_materialized_view(view_name)
  end

  query = create_materialized_view_statement(view_name, build_query, options)
  execute(query)
end

#create_materialized_view_statement(view_name, query, options = {}) ⇒ Object 



77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 77

def create_materialized_view_statement(view_name, query, options = {})
  columns = if options[:columns]
    "(" + options[:columns].map { |c| quote_column_name(c) }.join(",") + ")"
  else
    ""
  end

  storage = if options[:storage]&.any?
    "WITH (" + options[:storage].map { |key, value| "#{key}=#{value}" }.join(", ") + ")"
  else
    ""
  end

  tablespace = if options[:tablespace]
    "TABLESPACE #{quote_table_name(options[:tablespace])}"
  else
    ""
  end

  with_data = if options.fetch(:data, true)
    "WITH DATA"
  else
    "WITH NO DATA"
  end

  <<-SQL.squish
    CREATE MATERIALIZED VIEW #{quote_table_name(view_name)}
      #{columns}
      #{storage}
      #{tablespace}
      AS #{query}
      #{with_data}
  SQL
end

#database_username ⇒ Object 



153
154
155
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 153

def database_username
  @config[:username]
end

#drop_materialized_view(view_name) ⇒ Object 



129
130
131
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 129

def drop_materialized_view(view_name)
  execute "DROP MATERIALIZED VIEW IF EXISTS #{quote_table_name(view_name)}"
end

#materialized_view_build_query(view, name = nil) ⇒ Object

Returns a tuple [string, hash], where string is the query used to construct the view, and hash contains the options given when the view was created.



55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 55

def materialized_view_build_query(view, name = nil)
  result = execute <<-SQL.squish, name
    SELECT a.reloptions, b.tablespace, b.ispopulated, b.definition
      FROM pg_class a, pg_matviews b
     WHERE a.relname=#{quote(view)}
       AND b.matviewname=a.relname
  SQL
  row = result.to_a[0]

  storage = row["reloptions"]
  tablespace = row["tablespace"]
  ispopulated = row["ispopulated"]
  definition = row["definition"].strip.sub(/;$/, "")

  options = {}
  options[:data] = false if ispopulated == "f" || ispopulated == false
  options[:storage] = parse_storage_definition(storage) if storage.present?
  options[:tablespace] = tablespace if tablespace.present?

  [definition, options]
end

#materialized_views(name = nil) ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 38

def materialized_views(name = nil)
  query = <<-SQL.squish
    SELECT relname
      FROM pg_class
     WHERE relnamespace IN (
              SELECT oid
                FROM pg_namespace
               WHERE nspname = ANY(current_schemas(false)))
       AND relkind = 'm';
  SQL

  execute(query, name).map { |row| row["relname"] }
end

#parse_storage_definition(storage) ⇒ Object 



141
142
143
144
145
146
147
148
149
150
151
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 141

def parse_storage_definition(storage)
  # JRuby 9000 returns storage as an Array, whereas
  # MRI returns a string.
  storage = storage.first if storage.is_a?(Array)

  storage = storage.gsub(/^{|}$/, "")
  storage.split(",").each_with_object({}) do |item, hash|
    key, value = item.strip.split("=")
    hash[key.to_sym] = value
  end
end

#refresh_materialized_view(view_name) ⇒ Object 



133
134
135
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 133

def refresh_materialized_view(view_name)
  execute "REFRESH MATERIALIZED VIEW #{quote_table_name(view_name)}"
end

#refresh_materialized_view_concurrently(view_name) ⇒ Object 



137
138
139
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 137

def refresh_materialized_view_concurrently(view_name)
  execute "REFRESH MATERIALIZED VIEW CONCURRENTLY #{quote_table_name(view_name)}"
end

#supports_materialized_views? ⇒ Boolean

Returns:

  • (Boolean) 


34
35
36
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 34

def supports_materialized_views?
  true
end

#view_build_query(view, name = nil) ⇒ Object 



21
22
23
24
25
26
27
28
29
30
31
32
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 21

def view_build_query(view, name = nil)
  q = <<-SQL
  SELECT view_definition
    FROM information_schema.views
   WHERE table_catalog = (SELECT catalog_name FROM information_schema.information_schema_catalog_name)
     AND table_schema = ANY(current_schemas(false))
     AND table_name = '#{view}'
  SQL

  view_sql = select_value(q, name) or raise "No view called #{view} found"
  view_sql.gsub("\"", "\\\"")
end

#views(name = nil) ⇒ Object

:nodoc:



8
9
10
11
12
13
14
15
16
17
18
19
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 8

def views(name = nil) # :nodoc:
  q = <<-SQL
        SELECT t.table_name
          FROM information_schema.views AS t
    INNER JOIN pg_class AS c ON c.relname = t.table_name AND c.relnamespace = to_regnamespace(t.table_schema)::oid
         WHERE t.table_schema = ANY(current_schemas(true))
           AND table_schema NOT IN ('information_schema', 'pg_catalog')
           AND pg_catalog.pg_get_userbyid(c.relowner) = #{quote(database_username)}
  SQL

  execute(q, name).map { |row| row["table_name"] }
end