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 



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

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 



76
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
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 76

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] && 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

  "    CREATE MATERIALIZED VIEW \#{quote_table_name(view_name)}\n      \#{columns}\n      \#{storage}\n      \#{tablespace}\n      AS \#{query}\n      \#{with_data}\n  SQL\nend\n".squish

#drop_materialized_view(view_name) ⇒ Object 



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

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.



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

def materialized_view_build_query(view, name = nil)
  result = execute "    SELECT a.reloptions, b.tablespace, b.ispopulated, b.definition\n      FROM pg_class a, pg_matviews b\n     WHERE a.relname=\#{quote(view)}\n       AND b.matviewname=a.relname\n  SQL\n\n  row = result[0]\n\n  storage = row[\"reloptions\"]\n  tablespace = row[\"tablespace\"]\n  ispopulated = row[\"ispopulated\"]\n  definition = row[\"definition\"].strip.sub(/;$/, \"\")\n\n  options = {}\n  options[:data] = false if ispopulated == 'f'\n  options[:storage] = parse_storage_definition(storage) if storage.present?\n  options[:tablespace] = tablespace if tablespace.present?\n\n  [definition, options]\nend\n".squish, name

#materialized_views(name = nil) ⇒ Object 



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

def materialized_views(name = nil)
  query = "    SELECT relname\n      FROM pg_class\n     WHERE relnamespace IN (\n              SELECT oid\n                FROM pg_namespace\n               WHERE nspname = ANY(current_schemas(false)))\n       AND relkind = 'm';\n  SQL\n\n  execute(query, name).map { |row| row['relname'] }\nend\n".squish

#parse_storage_definition(storage) ⇒ Object 



136
137
138
139
140
141
142
143
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 136

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

#refresh_materialized_view(view_name) ⇒ Object 



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

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

#supports_materialized_views? ⇒ Boolean

Returns:

  • (Boolean) 


32
33
34
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 32

def supports_materialized_views?
  true
end

#view_build_query(view, name = nil) ⇒ Object 



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

def view_build_query(view, name = nil)
  q = "  SELECT view_definition\n    FROM information_schema.views\n   WHERE table_catalog = (SELECT catalog_name FROM information_schema.information_schema_catalog_name)\n     AND table_schema = ANY(current_schemas(false))\n     AND table_name = '\#{view}'\n  SQL\n\n  view_sql = select_value(q, name) or raise \"No view called \#{view} found\"\n  view_sql.gsub(\"\\\"\", \"\\\\\\\"\")\nend\n"

#views(name = nil) ⇒ Object

:nodoc:



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

def views(name = nil) #:nodoc:
  q = "  SELECT table_name, table_type\n    FROM information_schema.tables\n   WHERE table_schema = ANY(current_schemas(false))\n     AND table_type = 'VIEW'\n  SQL\n\n  execute(q, name).map { |row| row['table_name'] }\nend\n"