Module: ViewsSchema

Defined in:
lib/views_schema.rb

Overview

Provides the ability to create and manage views through ActiveRecord

Constant Summary collapse

WARNING =

Warning displayed in the event that the views have been broken

<<~HEREDOC.freeze
  ╔════════════════════════════════════════════════════════════╗
  ║                          WARNING!                          ║
  ║        The attempt to dump the view schema failed.         ║
  ║ It is likely that your migrations have broken one or more  ║
  ║      of the views. It is CRITICAL that this problem is     ║
  ║       addressed before you commit these migrations.        ║
  ║   To ensure that reporting is not affected please ensure   ║
  ║    that the updated view accurately reflects the data.     ║
  ║    DO NOT change the schema of the view, merely how it     ║
  ║   retrieves the data. Ensure the changes are thoroughly    ║
  ║            tested against production like data.            ║
  ║                                                            ║
  ║      Downstream users should be notified of potential      ║
  ║                        disruption.                         ║
  ╚════════════════════════════════════════════════════════════╝
HEREDOC
ALGORITHMS =

Valid algorithm options, first option is default

%w[UNDEFINED MERGE TEMPTABLE].freeze
SECURITIES =

Valid security options, first option is default

%w[DEFINER INVOKER].freeze
VIEW_STATEMENT =
'%{action} ALGORITHM=%<algorithm>s SQL SECURITY %<security>s VIEW `%<name>s` AS %<statement>s'.freeze
REGEXP =
/\ACREATE ALGORITHM=(?<algorithm>\w*) DEFINER=`[^`]*`@`[^`]*` SQL SECURITY (?<security>\w*) VIEW `[^`]+` AS (?<statement>.*)\z/i.freeze

Class Method Summary collapse

Class Method Details

.all_viewsObject


43
44
45
46
47
48
49
50
51
# File 'lib/views_schema.rb', line 43

def self.all_views
  ActiveRecord::Base.connection.execute("
    SELECT TABLE_NAME AS name
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_SCHEMA = '#{ActiveRecord::Base.connection.current_database}';").map do |v|
    # Behaviour depends on ruby version, so we need to work out what we have
    v.is_a?(Hash) ? v['name'] : v.first
  end.flatten
end

.create_view(name, statement, algorithm: ALGORITHMS.first, security: SECURITIES.first) ⇒ Void

Creates a new view. Will fail if the view already exists.


61
62
63
64
65
66
67
68
69
# File 'lib/views_schema.rb', line 61

def self.create_view(name, statement, algorithm: ALGORITHMS.first, security: SECURITIES.first)
  execute(
    action: 'CREATE',
    name: name,
    statement: statement,
    algorithm: algorithm,
    security: security
  )
end

.drop_view(name) ⇒ Void

Drops the view


94
95
96
97
98
# File 'lib/views_schema.rb', line 94

def self.drop_view(name)
  raise "Invalid name: `#{args[:name]}`" unless /^[a-z0-9_]*$/.match?(args[:name])

  ActiveRecord::Base.connection.execute("DROP VIEW IF EXISTS `#{name}`;")
end

.each_viewObject


32
33
34
35
36
37
38
39
40
41
# File 'lib/views_schema.rb', line 32

def self.each_view
  all_views.each do |name|
    query = ActiveRecord::Base.connection.exec_query("SHOW CREATE TABLE #{name}").first
    matched = REGEXP.match(query['Create View'])
    yield(name, matched[:statement], matched[:algorithm], matched[:security])
  end
rescue ActiveRecord::StatementInvalid => e
  puts Rainbow(WARNING).red.inverse
  raise e
end

.execute(args) ⇒ Object

Note:

Use create_view or update_view

Generates the SQL for view creation/updating

Options Hash (args):

  • name (String)

    The name of the view to create

  • action (String)

    Whether to create or update, can be 'CREATE' or 'CREATE OR REPLACE'

  • statement (String, ActiveRecord::Relation)

    SQL select statement or equivalent rails relation object

  • algorithm (String)

    View algorithm to use, either UNDEFINED MERGE TEMPTABLE (default UNDEFINED)

  • security (String)

    View security to use, either DEFINER INVOKER (default DEFINER)


109
110
111
112
113
114
# File 'lib/views_schema.rb', line 109

def self.execute(args)
  raise "Invalid name: `#{args[:name]}`" unless /^[a-z0-9_]*$/.match?(args[:name])

  args[:statement] = args[:statement].to_sql if args[:statement].respond_to?(:to_sql)
  ActiveRecord::Base.connection.execute(VIEW_STATEMENT % args)
end

.update_view(name, statement, algorithm: ALGORITHMS.first, security: SECURITIES.first) ⇒ Void

Updates an existing view, or creates a new view if it doesn't exist already.


79
80
81
82
83
84
85
86
87
# File 'lib/views_schema.rb', line 79

def self.update_view(name, statement, algorithm: ALGORITHMS.first, security: SECURITIES.first)
  execute(
    action: 'CREATE OR REPLACE',
    name: name,
    statement: statement,
    algorithm: algorithm,
    security: security
  )
end