Module: ViewsSchema

Defined in:
lib/views_schema.rb

Overview

Provides the ability to create and manage views through ActiveRecord

See Also:

Constant Summary collapse

WARNING =

Warning displayed in the event that the views have been broken

<<~HEREDOC
  ╔════════════════════════════════════════════════════════════╗
  ║                          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'
REGEXP =

rubocop:todo Layout/LineLength

/\ACREATE ALGORITHM=(?<algorithm>\w*) DEFINER=`[^`]*`@`[^`]*` SQL SECURITY (?<security>\w*) VIEW `[^`]+` AS (?<statement>.*)\z/i

Class Method Summary collapse

Class Method Details

.all_views ⇒ Object

rubocop:todo Metrics/MethodLength



50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/views_schema.rb', line 50

def self.all_views # rubocop:todo Metrics/MethodLength
  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.

Parameters:

  • name (String) —

    The name of the view to create

  • statement (String, ActiveRecord::Relation) —

    SQL select statement or equivalent rails relation object

  • algorithm (String) (defaults to: ALGORITHMS.first) —

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

  • security (String) (defaults to: SECURITIES.first) —

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

Returns:

  • (Void) 


74
75
76
 
# File 'lib/views_schema.rb', line 74

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

Parameters:

  • name (String) —

    The name of the view to drop

Returns:

  • (Void) 


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

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_view ⇒ Object

rubocop:enable Layout/LineLength



39
40
41
42
43
44
45
46
47
48
 
# File 'lib/views_schema.rb', line 39

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

Parameters:

  • args (Hash) —

    The options for the new view

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)



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

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.

Parameters:

  • name (String) —

    The name of the view to create

  • statement (String, ActiveRecord::Relation) —

    SQL select statement or equivalent rails relation object

  • algorithm (String) (defaults to: ALGORITHMS.first) —

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

  • security (String) (defaults to: SECURITIES.first) —

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

Returns:

  • (Void) 


86
87
88
 
# File 'lib/views_schema.rb', line 86

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