gmod_materialized_view_tool.pl - a tool for creating and mangaing materialized
views for Chado.
% gmod_materialized_view_tool.pl [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
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.
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.
- Guides the user through a series of prompts to create a new
- --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
- 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.
- 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.
- Gives a list of current MVs.
- Gives the status of all MVs, including whether they are
considered current or out of date.
- Prints this documetation and quits.
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.
- 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.
- The schema qualified name of the table, like
- Frequency at which the view should be updated. This can
either be a number of seconds, or one of 'daily', 'weekly', or
- List of columns with types, like "feature_id
integer,name varchar(255),organism_id integer".
- Select query to define table contents; see the note above
about how the SQL must be written for this query.
- List of fields to build indexes on.
- SQL to create special indexes. This allows you to create
functional and full text search indexes.
- Assume yes to any yes/no question
Chris Carpita <ccarpita at gmail dot com>, with some minor additions and
GMOD specific alterations from Scott Cain <email@example.com>.
Copyright (c) 2007
This library is free software; you can redistribute it and/or modify it under
the same terms as Perl itself.