.\" Automatically generated by Pod::Man 4.07 (Pod::Simple 3.32) .\" .\" 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 >0, 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 .. .if !\nF .nr F 0 .if \nF>0 \{\ . de IX . tm Index:\\$1\t\\n%\t"\\$2" .. . if !\nF==2 \{\ . nr % 0 . nr F 2 . \} .\} .\" ======================================================================== .\" .IX Title "GMOD_MATERIALIZED_VIEW_TOOL 1p" .TH GMOD_MATERIALIZED_VIEW_TOOL 1p "2016-12-17" "perl v5.24.1" "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 , with some minor additions and \&\s-1GMOD\s0 specific alterations from Scott Cain . .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.