OnWorks favicon

gmod_materialized_view_tool.plp - Online in the Cloud

Run gmod_materialized_view_tool.plp in OnWorks free hosting provider over Ubuntu Online, Fedora Online, Windows online emulator or MAC OS online emulator

This is the command gmod_materialized_view_tool.plp that can be run in the OnWorks free hosting provider using one of our multiple free online workstations such as Ubuntu Online, Fedora Online, Windows online emulator or MAC OS online emulator



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


% 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 --automatic.


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

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.


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.

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

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 "public.all_feature_names"

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

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

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 protected]>.

Copyright (c) 2007

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

Use gmod_materialized_view_tool.plp online using onworks.net services

Free Servers & Workstations

Download Windows & Linux apps

  • 1
    Firebird RDBMS offers ANSI SQL features
    & runs on Linux, Windows &
    several Unix platforms. Features
    excellent concurrency & performance
    & power...
    Download Firebird
  • 2
    KompoZer is a wysiwyg HTML editor using
    the Mozilla Composer codebase. As
    Nvu's development has been stopped
    in 2005, KompoZer fixes many bugs and
    adds a f...
    Download KompoZer
  • 3
    Free Manga Downloader
    Free Manga Downloader
    The Free Manga Downloader (FMD) is an
    open source application written in
    Object-Pascal for managing and
    downloading manga from various websites.
    This is a mirr...
    Download Free Manga Downloader
  • 4
    UNetbootin allows you to create bootable
    Live USB drives for Ubuntu, Fedora, and
    other Linux distributions without
    burning a CD. It runs on Windows, Linux,
    and ...
    Download UNetbootin
  • 5
    Dolibarr ERP - CRM
    Dolibarr ERP - CRM
    Dolibarr ERP - CRM is an easy to use
    ERP and CRM open source software package
    (run with a web php server or as
    standalone software) for businesses,
    Download Dolibarr ERP - CRM
  • 6
    SQuirreL SQL Client
    SQuirreL SQL Client
    SQuirreL SQL Client is a graphical SQL
    client written in Java that will allow
    you to view the structure of a JDBC
    compliant database, browse the data in
    Download SQuirreL SQL Client
  • More »

Linux commands