class Clear::View

Overview

Create and maintain your database views directly in the code.

You could use the migration system to create and drop your views. However this is proven to be difficult, even more if you want to update a view which depends on another subviews.

How it works ?

When you migrate using the migration system, all the views registered are going to be destroyed and recreated again. Order of creation depends of the requirement for each views

Example

Clear::View.register :room_per_days do |view|
  view.require(:rooms, :year_days)

  view.query <<-SQL
    SELECT room_id, day
    FROM year_days
    CROSS JOIN rooms
  SQL
end

Clear::View.register :rooms do |view|
  view.query <<-SQL
  SELECT room.id as room_id
  FROM generate_series(1, 4) AS room(id)
  SQL
end

Clear::View.register :year_days do |view|
  view.query <<-SQL
  SELECT date.day::date as day
  FROM   generate_series(
    date_trunc('day', NOW()),
    date_trunc('day', NOW() + INTERVAL '364 days'),
    INTERVAL '1 day'
  ) AS date(day)
  SQL
end

In the example above, room_per_days will be first dropped before a migration start and last created after the migration finished, to prevent issue where some views are linked to others

Defined in:

clear/view/base.cr

Class Method Summary

Instance Method Summary

Class Method Detail

def self.apply(direction : Symbol, view_name : String, apply_cache : Set(String)) #

install the view into postgresql using CREATE VIEW


[View source]
def self.apply(direction : Symbol, apply_cache = Set(String).new) #

install the view into postgresql using CREATE VIEW


[View source]
def self.register(name : Clear::SQL::Symbolic, &) #

Call the DSL to register a new view

Clear::View.register(:name) do |view|
  # describe the view here.
end

[View source]

Instance Method Detail

def connection(connection : String) #

database connection where is installed the view


[View source]
def connection : String #

[View source]
def full_name #

[View source]
def materialized(mat : Bool) #

whether the view is materialized or not. I would recommend to use migration execute create/drop whenever the view is a materialized view


[View source]
def materialized : Bool #

[View source]
def name(value : String | Symbol) #

name of the view


[View source]
def name : String #

[View source]
def query(query : String) #

query body related to the view. Must be a SELECT clause


[View source]
def query : String #

[View source]
def require(*req) #

list of dependencies from the other view related to this view


[View source]
def requirement : Set(String) #

[View source]
def schema(value : String | Symbol) #

schema to store the view (default public)


[View source]
def schema : String #

[View source]
def to_create_sql #

[View source]
def to_drop_sql #

[View source]