GMOD_M...EW_TOOL(1p)

💖 GMOD_MATERIALIZED_VIEW_TOOL.PL 💖

okay bestie, so this is gmod_materialized_view_tool.pl — it's a User Contributed Perl Documentation page (that's the manual name, just so you know what you're reading 🫶) and it's literally the tool for creating and managing materialized views for Chado. no cap.

---

✨ SYNOPSYS ✨

(yeah the heading is spelled like that, we don't fix it, we respect it 💅)

% gmod_materialized_view_tool.pl [options]

---

🎀 COMMAND-LINE OPTIONS 🎀

here's the whole menu, babes. every single one, no skipping 🌸

• --create_view — walks you through creating a MV, hand in hand, like a good friend 🫶
• --update_view viewname — refreshes the data in the named MV
• --automatic — refreshes the data in all the MVs that are out of date
• --dematerialize viewname — creates a true view, removing the MV (yes it's a breakup, and yes it's for the best 💔)
• --dbprofile profilename — the DB profile options to use (default is 'default')
• --list — gives you a list of MV
• --status — gives you 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

💡 little life hack: the options can be shortened. for example, --de is an acceptable shortening of --dematerialize. and for options that have a unique first letter, the short (single hyphened) version of the option may be used, like -a for --automatic. iconic behaviour honestly ✨

---

🌈 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. ⚠️

like, bestie, i need you to hear me on that one. it's giving "powerful but please be careful" 💀

so anyway, 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's like a view, only because it materialized, it can be indexed and searches on it will go much faster than on database views. literally the glow-up of views 👑

there are at least two down sides to MVs though, and we're gonna be honest about them:

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. so like, the tea is slightly stale sometimes 😭
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. she's hungry. she eats gigabytes. 🍓

🦋 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. like, ghost town. nobody home. 🙈 so do the rename, okay? i'm begging.

---

💅 OPTIONS 💅

• --create_view — guides the user through a series of prompts to create a new materialized view. it's a whole little interview, but make it database ✨
• --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. out with the old, in with the new, periodt.
• --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. set it and forget it, main character energy 👑
• --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. vibe check for your views 🔥
• --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. so like, either you tell her everything up front, or she's gonna ask. her choice, honestly 💖

• --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. nickname privileges 🎀
• --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. fancy fr fr 🌟
• --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.

---

and that's the whole page, bestie!! this one's from perl v5.30.0, dated 2019-12-05 — yeah she's vintage, but she ate and left no crumbs 💅✨ go make some materialized views, but remember that security hole warning okay love u bye 🫶💖