NAME

       gmod_materialized_view_tool.pl - a tool for creating and mangaing materialized views for
       Chado.

SYNOPSYS

        % gmod_materialized_view_tool.pl [options]

COMMAND-LINE OPTIONS

        --create_view              Guides user through creating a MV
        --update_view viewname     Refreshes data in named MV
        --automatic                Refreshes data in all MV that are out of date
        --dematerialize viewname   Creates a true view, removing the MV
        --dbprofile profilename    DB profile options to use (default is 'default')
        --list                     Gives a list of MV
        --status                   Gives the status of all MV
        --view_name                Name of the view to be created
        --table_name               Schema qualified name of the table
        --refresh_time             Frequency at which the view should be updated
        --column_def               List of columns with types
        --sql_query                Select query to define table contents
        --index_fields             List of fields to build indexes on
        --special_index            SQL to create special indexes
        --yes                      Assume yes to any yes/no question
        --help                     Prints this documentation and quits

       Note that the options can be shortened.  For example, '--de' is an acceptable shortening
       of --dematerialize.  For options that have a unique first letter, the short (single
       hyphened) version of the option may be used, like '-a' for --automatic.

DESCRIPTION

       WARNING: This script creates a rather large security hole that could result in data loss.
       Users could easily enter SQL queries through this interface that could damage your
       database.

       This tool provides several useful functions for creating and maintaining materialized
       views (MV) in a Chado schema.  A materialized view is simple a (real) database table that
       has been created and contains data from a collection of other tables.  It is like a view,
       only because it materialized, it can be indexed and searches on it will go much faster
       than on database views.  There are at least two down sides to MVs:

       1 Data syncronisity
           When normal tables are updated with values that are reflected in a MV, there will be a
           delay (usually a very noticeable one) between when the normal table is updated and
           when the MV is updated.  This tool provides the means of updating the MVs; see
           --automatic below.

       2 Disk space
           Since MVs are actual tables, they will take up actual disk space.  It is possible,
           depending on how the MV is created, it may take up an enormous amount of disk space.

   A Note about SQL for populating the table
       When constructing the SELECT clause, the names of the columns selected must match the
       names of the columns in the materalized view.  For example, if the names of the columns
       are feature_id and name, but the columns being selected are feature_id and uniquename, you
       must use the "AS" option to rename the resulting column, like:

         SELECT feature_id, uniquename AS name ...

       If you don't do this, the affected column in the resulting table will be empty.

OPTIONS

       --create_view
           Guides the user through a series of prompts to create a new materialized view.

       --update_view viewname
           Updates the data in a materialized view by first deleting the data in the table and
           then running the query that defines the data to repopulate it.

       --automatic
           Automatically updates all of the MVs that are currently marked out of date according
           to the update frequency that was specified when the MV was created.  This option is
           very useful in a cron job to update MVs on a regular basis.

       --dematerialize viewname
           Takes a MV and turns into a standard view.  This might be done if the database
           administrator desides that the downsides of the MV scheme is not working for a given
           view, if for example, the data in the underlying tables is changing to frequently or
           the MV is taking up too much disk space.

       --dbprofile
           The name of the DB profile to use for database connectivity.  These profiles are kept
           in $GMOD_ROOT/conf (typically /usr/local/gmod/conf) and contain information like the
           database name, user name and password.  The default value is 'default' which was
           created when the Chado database was created.

       --list
           Gives a list of current MVs.

       --status
           Gives the status of all MVs, including whether they are considered current or out of
           date.

       --help
           Prints this documetation and quits.

NONINTERACTIVE VIEW CREATION

       The following options are provided to allow the creation of materialized views in a non-
       interactive way.  If any of the below flags are omitted, you will be prompted for the
       appropriate values.

       --view_name
           This is the name that this tool will use later to refer to the MV as; typically it
           will be the same as the name of the MV in the database, but it doesn't have to be.

       --table_name
           The schema qualified name of the table, like "public.all_feature_names"

       --refresh_time
           Frequency at which the view should be updated.  This can either be a number of
           seconds, or one of 'daily', 'weekly', or 'monthly'.

       --column_def
           List of columns with types, like "feature_id integer,name varchar(255),organism_id
           integer".

       --sql_query
           Select query to define table contents; see the note above about how the SQL must be
           written for this query.

       --index_fields
           List of fields to build indexes on.

       --special_index
           SQL to create special indexes.  This allows you to create functional and full text
           search indexes.

       --yes
           Assume yes to any yes/no question

AUTHORS

       Chris Carpita <ccarpita at gmail dot com>, with some minor additions and GMOD specific
       alterations from Scott Cain <cain@cshl.edu>.

       Copyright (c) 2007

       This library is free software; you can redistribute it and/or modify it under the same
       terms as Perl itself.