Familiar (familiar v0.1.4) 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, version: 1
  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, version: 2, revert: 1
  end
end

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

The new version number can be specified explicitly if desired:

$ mix familiar.gen.view my_view --version 3

Non default schema

Definition to be created in the non default schema can be placed in a subdirectory with the name of the schema. For example priv/repo/views/bi/analytics_v1.sql will create a the analytics view in the bi schema.

The :schema option then needs to be added to each function call. The --schema option can be also be passed to familiar.gen.view.

Link to this section Summary

Functions

create_function(function_name, opts)

Creates a new database function from a function definition file.

create_view(view_name, opts)

Creates a new database view from a view definition file.

drop_function(function_name, opts \\ [])

Drops a database function.

drop_view(view_name, opts \\ [])

Drops a database view.

replace_function(function_name, opts)

Replaces a new database function from a function definition file.

replace_view(view_name, opts)

Replaces a database view from a view definition file.

update_function(function_name, opts)

Updates a new database function from a function definition file.

update_view(view_name, opts)

Updates a database view from a view definition file.

Link to this section Functions

create_function(function_name, opts)

Creates a new database function from a function definition file.

Options:

:version - the version of the function to create
:schema - the schema to create the function in. Uses default schema if not specified

create_view(view_name, opts)

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.
:schema - the schema to create the view in. Creates in default schema if not specified

drop_function(function_name, opts \\ [])

Drops a database function.

Options:

:revert - the version to create if the migration is rolled back
:schema - the schema the function lives in. Uses default schema if not specified

drop_view(view_name, opts \\ [])

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
:schema - the schema the view lives in. Uses default schema if not specified

replace_function(function_name, opts)

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
:schema - the schema the function lives in. Uses default schema if not specified

replace_view(view_name, opts)

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
:schema - the schema the view lives in. Uses default schema if not specified

update_function(function_name, opts)

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
:schema - the schema the function lives in. Uses default schema if not specified

update_view(view_name, opts)

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
:schema - the schema the view lives in. Uses default schema if not specified