Metadata-Version: 2.0
Name: pg-materialize
Version: 0.2.6
Summary: Postgres Materialized View Dependency Manager
Home-page: http://github.com/aanari/pg-materialize
Author: Ali Anari
Author-email: ali@anari.io
License: MIT
Keywords: pg postgres psql pgsql postgresql materialized view dependency dependencies tool
Platform: UNKNOWN
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 2.7
Classifier: Topic :: Database
Classifier: Topic :: Utilities
Requires-Dist: Pygments
Requires-Dist: click
Requires-Dist: psqlparse (==1.0rc5)
Requires-Dist: pypandoc
Requires-Dist: six
Requires-Dist: toolz
Requires-Dist: toposort

pg\_materialize |Travis Status| |AppVeyor Status|
=================================================

.. figure:: logo.jpg
   :alt: pg\_materialize

   pg\_materialize

Description
-----------

``pg_materialize`` is a utility for generating PostgreSQL creation and
refresh scripts from a directory containing `Materialized
View <https://www.postgresql.org/docs/9.6/static/rules-materializedviews.html>`__
SQL definitions. It uses
`psqlparse <https://github.com/alculquicondor/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.6, 2.7
-  Python 3.3+

Installing
----------

If you have `pip <https://pip.pypa.io/>`__ on your system, you can
simply install or upgrade the Python library:

.. code:: sh

    pip install -U pg_materialize

Alternately, you can download the source distribution from
`PyPI <http://pypi.python.org/pypi/pg-materialize>`__, unarchive it, and
run:

.. code:: sh

    python setup.py install

Note: both of the methods described above install ``pg_materialize`` as
a system-wide package. You may consider using
`virtualenv <http://www.virtualenv.org/>`__ to create isolated Python
environments instead.

Usage
-----

**Example Command:**

.. code:: sh

    pg_materialize -v \
        -i ~/Projects/my_project/src \
        -o ~/Projects/my_project/output \
        -p _mv \
        -I invites

**Example Output:**

.. code:: sh

    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:**

.. code:: sql

    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:**

.. code:: sql

    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.

License (MIT)
-------------

Copyright (c) 2017 Ali Anari

Permission is hereby granted, free of charge, to any person obtaining a
copy of this software and associated documentation files (the
"Software"), to deal in the Software without restriction, including
without limitation the rights to use, copy, modify, merge, publish,
distribute, sublicense, and/or sell copies of the Software, and to
permit persons to whom the Software is furnished to do so, subject to
the following conditions:

The above copyright notice and this permission notice shall be included
in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

.. |Travis Status| image:: https://travis-ci.org/aanari/pg-materialize.svg?branch=master
   :target: //travis-ci.org/aanari/pg-materialize
.. |AppVeyor Status| image:: https://ci.appveyor.com/api/projects/status/xfuqfl2pv1728c6x?svg=true
   :target: https://ci.appveyor.com/project/aanari/pg-materialize/branch/master


