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 



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

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 



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

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

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

#database_username ⇒ Object 



151
152
153
 
# File 'lib/spectacles/schema_statements/postgresql_adapter.rb', line 151

def database_username
  @config[:username]
end

#drop_materialized_view(view_name) ⇒ Object 



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

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
76
 
# 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[0]

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

  options = {}
  options[:data] = false if ispopulated == 'f'
  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 



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

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(/,/).inject({}) do |hash, item|
    key, value = item.strip.split(/=/)
    hash[key.to_sym] = value
    hash
  end
end

#refresh_materialized_view(view_name) ⇒ Object 



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

def refresh_materialized_view(view_name)
  execute "REFRESH MATERIALIZED VIEW #{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, t.table_type
          FROM information_schema.tables AS t
    INNER JOIN pg_class AS c ON c.relname = t.table_name
         WHERE t.table_schema = ANY(current_schemas(false))
           AND t.table_type = 'VIEW'
           AND pg_catalog.pg_get_userbyid(c.relowner) = #{quote(database_username)}
  SQL

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