GitLocker: The Coding Marketplace

Description:

pgmaterialize 0.4.1

pg_materialize is a utility for generating PostgreSQL creation and refresh scripts from a directory containing Materialized View SQL definitions. It uses psqlparse to transform the SQL into parse trees, identifies which Materialized Views have a dependency on other views by generating a DAG, and produces the correct order for constructing and refreshing these views. The source directory is traversed recursively, cross-schema views are handled correctly, and extraneous transaction syntax blocks are filtered out from the final output. The files from each subsequent run are timestamped with the suffix YYYYMMDD-HHMMSS.sql.

Supported Python Versions

Python 2.7
Python 3.3+

Installation
If you have pip on your system, you can simply install or upgrade the Python library:
pip install -U pg_materialize
Alternately, you can download the source distribution from PyPI, unarchive it, and run:
python setup.py install
Note: both of the methods described above install pg_materialize as a system-wide package. You may consider using virtualenv to create isolated Python environments instead.

Usage
Example Command:
pg_materialize -v \
-i ~/Projects/my_project/src \
-o ~/Projects/my_project/output \
-p _mv \
-I invites
Example Output:
Found 97 Scripts in /Users/ali/Projects/my_project/src
Identified 169 Materialized Views, Containing 90 View Dependencies

Materialized View Dependencies:
'public.users_mv': ['public.user_addresses_mv', 'public.user_invites_mv'],
'public.orders_mv': ['public.payment_methods_mv']

Selecting 97 Materialized Views for Refresh
Successfully Saved Creation Script to ~/Projects/my_project/output/create-20170824-120626.sql
Successfully Saved Refresh Script to ~/Projects/my_project/output/refresh-20170824-120626.sql
Process Complete!
Example Creation Script:
BEGIN;

-- ~/Projects/my_project/src/public/user_addresses.sql

CREATE MATERIALIZED VIEW IF NOT EXISTS public.user_addresses_mv AS (
SELECT *
FROM public.user_addresses
WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
) WITH DATA;

-- ~/Projects/my_project/src/public/user_invites.sql

CREATE MATERIALIZED VIEW IF NOT EXISTS public.user_invites_mv AS (
SELECT *
FROM public.user_invites
WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
) WITH DATA;

-- ~/Projects/my_project/src/public/payment_methods.sql

CREATE MATERIALIZED VIEW IF NOT EXISTS public.payment_methods_mv AS (
SELECT *
FROM public.payment_methods
WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
) WITH DATA;

-- ~/Projects/my_project/src/public/users.sql

CREATE MATERIALIZED VIEW IF NOT EXISTS public.users_mv AS (
SELECT *
FROM public.users
WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
) WITH DATA;

-- ~/Projects/my_project/src/public/orders.sql

CREATE MATERIALIZED VIEW IF NOT EXISTS public.orders_mv AS (
SELECT *
FROM public.orders
WHERE created_at >= CURRENT_DATE - INTERVAL '6 MONTHS'
) WITH DATA;

COMMIT;
Example Refresh Script:
BEGIN;

REFRESH MATERIALIZED VIEW CONCURRENTLY public.user_addresses_mv;

REFRESH MATERIALIZED VIEW CONCURRENTLY public.payment_methods_mv;

REFRESH MATERIALIZED VIEW CONCURRENTLY public.users_mv;

REFRESH MATERIALIZED VIEW CONCURRENTLY public.orders_mv;

COMMIT;

Options
pg_materialize accepts the following command line arguments.

-d
--dry-run

Analyzes dependencies without actually generating the output files.

-i
--input-dir

The directory for the PostgreSQL scripts to analyze.

-I
--ignore-refresh

Regex pattern to match when ignoring refresh on Materialized Views (i.e. hist for users_mv_hist).

-o
--output_dir

The directory for the output creation and refresh scripts.

-p
--pattern

Materialized View regex pattern to match (i.e. _mv for users_mv).

-v
--verbose

Enables verbose logging.