.\" Automatically generated by Pod::Man 2.27 (Pod::Simple 3.28)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{
.    if \nF \{
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\"
.\" Accent mark definitions (@(#)ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
.\" Fear.  Run.  Save yourself.  No user-serviceable parts.
.    \" fudge factors for nroff and troff
.if n \{\
.    ds #H 0
.    ds #V .8m
.    ds #F .3m
.    ds #[ \f1
.    ds #] \fP
.\}
.if t \{\
.    ds #H ((1u-(\\\\n(.fu%2u))*.13m)
.    ds #V .6m
.    ds #F 0
.    ds #[ \&
.    ds #] \&
.\}
.    \" simple accents for nroff and troff
.if n \{\
.    ds ' \&
.    ds ` \&
.    ds ^ \&
.    ds , \&
.    ds ~ ~
.    ds /
.\}
.if t \{\
.    ds ' \\k:\h'-(\\n(.wu*8/10-\*(#H)'\'\h"|\\n:u"
.    ds ` \\k:\h'-(\\n(.wu*8/10-\*(#H)'\`\h'|\\n:u'
.    ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'^\h'|\\n:u'
.    ds , \\k:\h'-(\\n(.wu*8/10)',\h'|\\n:u'
.    ds ~ \\k:\h'-(\\n(.wu-\*(#H-.1m)'~\h'|\\n:u'
.    ds / \\k:\h'-(\\n(.wu*8/10-\*(#H)'\z\(sl\h'|\\n:u'
.\}
.    \" troff and (daisy-wheel) nroff accents
.ds : \\k:\h'-(\\n(.wu*8/10-\*(#H+.1m+\*(#F)'\v'-\*(#V'\z.\h'.2m+\*(#F'.\h'|\\n:u'\v'\*(#V'
.ds 8 \h'\*(#H'\(*b\h'-\*(#H'
.ds o \\k:\h'-(\\n(.wu+\w'\(de'u-\*(#H)/2u'\v'-.3n'\*(#[\z\(de\v'.3n'\h'|\\n:u'\*(#]
.ds d- \h'\*(#H'\(pd\h'-\w'~'u'\v'-.25m'\f2\(hy\fP\v'.25m'\h'-\*(#H'
.ds D- D\\k:\h'-\w'D'u'\v'-.11m'\z\(hy\v'.11m'\h'|\\n:u'
.ds th \*(#[\v'.3m'\s+1I\s-1\v'-.3m'\h'-(\w'I'u*2/3)'\s-1o\s+1\*(#]
.ds Th \*(#[\s+2I\s-2\h'-\w'I'u*3/5'\v'-.3m'o\v'.3m'\*(#]
.ds ae a\h'-(\w'a'u*4/10)'e
.ds Ae A\h'-(\w'A'u*4/10)'E
.    \" corrections for vroff
.if v .ds ~ \\k:\h'-(\\n(.wu*9/10-\*(#H)'\s-2\u~\d\s+2\h'|\\n:u'
.if v .ds ^ \\k:\h'-(\\n(.wu*10/11-\*(#H)'\v'-.4m'^\v'.4m'\h'|\\n:u'
.    \" for low resolution devices (crt and lpr)
.if \n(.H>23 .if \n(.V>19 \
\{\
.    ds : e
.    ds 8 ss
.    ds o a
.    ds d- d\h'-1'\(ga
.    ds D- D\h'-1'\(hy
.    ds th \o'bp'
.    ds Th \o'LP'
.    ds ae ae
.    ds Ae AE
.\}
.rm #[ #] #H #V #F C
.\" ========================================================================
.\"
.IX Title "GMOD_MATERIALIZED_VIEW_TOOL 1p"
.TH GMOD_MATERIALIZED_VIEW_TOOL 1p "2014-05-10" "perl v5.18.2" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
gmod_materialized_view_tool.pl \- a tool for creating and mangaing
materialized views for Chado.
.SH "SYNOPSYS"
.IX Header "SYNOPSYS"
.Vb 1
\& % gmod_materialized_view_tool.pl [options]
.Ve
.SH "COMMAND-LINE OPTIONS"
.IX Header "COMMAND-LINE OPTIONS"
.Vb 10
\& \-\-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 \*(Aqdefault\*(Aq)
\& \-\-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
.Ve
.PP
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.
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
\&\s-1WARNING:\s0 This script creates a rather large security hole that could 
result in data loss.  Users could easily enter \s-1SQL\s0 queries through this
interface that could damage your database.
.PP
This tool provides several useful functions for creating and maintaining
materialized views (\s-1MV\s0) 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:
.IP "1 Data syncronisity" 4
.IX Item "1 Data syncronisity"
When normal tables are updated with values that are reflected in a \s-1MV,\s0
there will be a delay (usually a very noticeable one) between when
the normal table is updated and when the \s-1MV\s0 is updated.  This tool
provides the means of updating the MVs; see \-\-automatic below.
.IP "2 Disk space" 4
.IX Item "2 Disk space"
Since MVs are actual tables, they will take up actual disk space.  It
is possible, depending on how the \s-1MV\s0 is created, it may take up an
enormous amount of disk space.
.SS "A Note about \s-1SQL\s0 for populating the table"
.IX Subsection "A Note about SQL for populating the table"
When constructing the \s-1SELECT\s0 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 \*(L"\s-1AS\*(R"\s0 option
to rename the resulting column, like:
.PP
.Vb 1
\&  SELECT feature_id, uniquename AS name ...
.Ve
.PP
If you don't do this, the affected column in the resulting table will
be empty.
.SH "OPTIONS"
.IX Header "OPTIONS"
.IP "\-\-create_view" 4
.IX Item "--create_view"
Guides the user through a series of prompts to create a new materialized view.
.IP "\-\-update_view viewname" 4
.IX Item "--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.
.IP "\-\-automatic" 4
.IX Item "--automatic"
Automatically updates all of the MVs that are currently marked out of 
date according to the update frequency that was specified when the \s-1MV\s0
was created.  This option is very useful in a cron job to update MVs
on a regular basis.
.IP "\-\-dematerialize viewname" 4
.IX Item "--dematerialize viewname"
Takes a \s-1MV\s0 and turns into a standard view.  This might be done if
the database administrator desides that the downsides of the \s-1MV\s0 scheme
is not working for a given view, if for example, the data in the underlying
tables is changing to frequently or the \s-1MV\s0 is taking up too much disk space.
.IP "\-\-dbprofile" 4
.IX Item "--dbprofile"
The name of the \s-1DB\s0 profile to use for database connectivity.  These
profiles are kept in \f(CW$GMOD_ROOT\fR/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.
.IP "\-\-list" 4
.IX Item "--list"
Gives a list of current MVs.
.IP "\-\-status" 4
.IX Item "--status"
Gives the status of all MVs, including whether they are considered
current or out of date.
.IP "\-\-help" 4
.IX Item "--help"
Prints this documetation and quits.
.SH "NONINTERACTIVE VIEW CREATION"
.IX Header "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.
.IP "\-\-view_name" 4
.IX Item "--view_name"
This is the name that this tool will use later to refer to the \s-1MV\s0 as; 
typically it will be the same as the name of the \s-1MV\s0 in the database, 
but it doesn't have to be.
.IP "\-\-table_name" 4
.IX Item "--table_name"
The schema qualified name of the table, like \*(L"public.all_feature_names\*(R"
.IP "\-\-refresh_time" 4
.IX Item "--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'.
.IP "\-\-column_def" 4
.IX Item "--column_def"
List of columns with types, like
\&\*(L"feature_id integer,name varchar(255),organism_id integer\*(R".
.IP "\-\-sql_query" 4
.IX Item "--sql_query"
Select query to define table contents; see the note above about how
the \s-1SQL\s0 must be written for this query.
.IP "\-\-index_fields" 4
.IX Item "--index_fields"
List of fields to build indexes on.
.IP "\-\-special_index" 4
.IX Item "--special_index"
\&\s-1SQL\s0 to create special indexes.  This allows you to create functional
and full text search indexes.
.IP "\-\-yes" 4
.IX Item "--yes"
Assume yes to any yes/no question
.SH "AUTHORS"
.IX Header "AUTHORS"
Chris Carpita <ccarpita at gmail dot com>, with some minor additions and
\&\s-1GMOD\s0 specific alterations from Scott Cain <cain@cshl.edu>.
.PP
Copyright (c) 2007
.PP
This library is free software; you can redistribute it and/or modify
it under the same terms as Perl itself.
