Familiar (familiar v0.1.1) View Source

Helper functions for creating database views and functions - your database's familiars.

View and function definitions are stored as SQL files in priv/repo/views and priv/repo/functions respectively.

Each definition file has the following file name format:

NAME_vNUMBER.sql

The NAME will be the name of the created database object and will be what is used to refer to it in the functions defined in this module. The NUMBER is the version number and should be incremented whenever a view or function is revised. Old versions should be kept and shouldn't be modified once deployed.

A view definition file can be generated using the following mix task:

$ mix familiar.gen.view my_view

Example

Given the following view definition in priv/repo/views/active_users_v1.sql:

SELECT * FROM users
WHERE users.active = TRUE;

The view can be created like so:

defmodule MyRepo.Migrations.CreateActiveUsers do
  use Ecto.Migration
  use Familiar

  def change do
    create_view :active_users
  end
end

Updating the view

If we want to update the active_users view created above, we can first generate a new version of the view by running:

$ mix familiar.gen.view active_users

And then editing the generated active_users_v2.sql as needed. Then the view can be updated in a migration:

defmodule MyRepo.Migrations.UpdateActiveUsers do
  use Ecto.Migration
  use Familiar

  def change do
    update_view :active_users, revert: 1
  end
end

The :revert option is optional however if it is omitted the migration will not be reversible.

Link to this section Summary

Functions

Creates a new database function from a function definition file.

Creates a new database view from a view definition file.

Drops a database function.

Drops a database view.

Replaces a new database function from a function definition file.

Replaces a database view from a view definition file.

Updates a new database function from a function definition file.

Updates a database view from a view definition file.

Link to this section Functions

Link to this function

create_function(function_name, opts)

View Source

Creates a new database function from a function definition file.

Options:

  • :version - the version of the function to create
Link to this function

create_view(view_name, opts)

View Source

Creates a new database view from a view definition file.

Options:

  • :version - the version of the view to create
  • :materialized - whether the view is materialized or not. Defaults to false.
Link to this function

drop_function(function_name, opts \\ [])

View Source

Drops a database function.

Options:

  • :revert - the version to create if the migration is rolled back
Link to this function

drop_view(view_name, opts \\ [])

View Source

Drops a database view.

Options:

  • :materialized - whether the view is materialized or not. Defaults to false.
  • :revert - the version to create if the migration is rolled back
Link to this function

replace_function(function_name, opts)

View Source

Replaces a new database function from a function definition file.

This function will use CREATE OR REPLACE FUNCTION so can only be used if the new version has the same arguments and return type as the old version.

Options:

  • :version - the version of the updated function
  • :revert - the version to revert to if the migration is rolled back
Link to this function

replace_view(view_name, opts)

View Source

Replaces a database view from a view definition file.

This function will use CREATE OR REPLACE VIEW so can only be used if the new version has the same columns as the old version.

Options:

  • :version - the version of the updated view
  • :materialized - whether the view is materialized or not. Defaults to false.
  • :revert - the version to revert to if the migration is rolled back
Link to this function

update_function(function_name, opts)

View Source

Updates a new database function from a function definition file.

This function will drop the existing function before creating a new function so dependant views, functions, triggers etc will need to be dropped first.

Options:

  • :version - the version of the updated function
  • :revert - the version to revert to if the migration is rolled back
Link to this function

update_view(view_name, opts)

View Source

Updates a database view from a view definition file.

This function will drop the existing view and then create the new version of the view so dependant views, functions, triggers etc will need to be dropped first.

Options:

  • :version - the version of the updated view
  • :materialized - whether the view is materialized or not. Defaults to false.
  • :revert - the version to revert to if the migration is rolled back