Usage

Each custom glossary is built by extracting rows from a master glossary table and adding them to a custom glossary table. The master glossary table should have a column identifying the term that is defined (e.g., a column name), and another column containing the definition of that term. The master glossary table may also have a third column containing a URL for a location that provides additional information about the term.

The master glossary table may be in a different database than the database that is being used to summarize data, but should be of the same DBMS type. Although it would be possible to have the master glossary and custom glossary tables in databases of different DBMSs, the scripts do not currently accommodate that possibility.

Definitions may also be added to the custom glossary table that are not in the master glossary table.

Creation of a custom glossary ordinarily requires the following steps:

1. Connect to the database containing the master glossary table using an execsql CONNECT metacommand.
2. Initialize the glossary-creation scripts by providing information on the master glossary table and database, using the following script:
   • init_glossary(glossary_db_alias, glossary_table, name_col, def_col)
3. Use one or more of the following scripts to populate the custom glossary:
   • add_glossary_table(schema, table)
   • add_glossary_item(item, definition)
   • add_glossary_list(column_list)
4. Use the glossary view to retrieve the custom glossary for display or export.

An Illustration

The following set of execsql metacommands illustrate the typical steps in the creation of a custom glossary.

-- Include the glossary script file.
-- !x! include pg_glossary.sql

-- Connect to the master glossary database.
-- !x! connect to postgresql(server=kuiper, db=sedna, user=majortom, need_pwd=True) as awayhome

-- Initialize the glossary scripts.
-- Arguments:
--    glossary_db_alias : The connection alias used with the CONNECT metacommand.
--    glossary_table    : The name of the master glossary table in the master glossary database.
--    name_col          : The name of the column in the master glossary table containing the term.
--    def_col           : The name of the column in the master glossary table containing the definition of the term.
-- !x! execute script init_glossary with (glossary_db_alias=awayhome, glossary_table=glossary, name_col=item, def_col=definition)

-- Create a data summary.
create temporary view suppliers as
select 'Outbound 2122' as trip, *
from   chandlers
where  location = 'Io' and proprietor <> 'Barney';

-- Copy column definitions for the data summary from the master glossary into the custom glossary.
-- The schema is set to an empty string because a temporary view is used.
-- !x! execute script add_glossary_table with (schema='', table='suppliers')

-- Add an item that is not in the master glossary to the custom glossary.
-- !x! execute script add_glossary_item with (item=trip, definition="Name and date of travel")

-- Export the data summary.
-- !x! export suppliers to tripdata.ods as ods

-- Add the custom glossary to the export.
-- !x! export glossary append to tripdata.ods as ods

This illustration is written to run with a Postgres database. Script arguments are different for MariaDB/MySQL because those DBMSs equate a schema with a database rather than supporting schemas within databases, and because those DBMSs don’t list temporary objects in the information_schema tables.

Customizing Glossary Entries

Each term can be added to the custom glossary only once. After a term has been added, subsequent actions to add the same term will have no effect.

If a custom definition is required for a term that appears in the master glossary, and that would ordinarily be added to the custom glossary by the add_glossary_table script, a custom definition can be created by using the add_glossary_item script before using the add_glossary_table script.

The script add_glossary_item can be used to easily add a single item to the custom glossary, but rows can also be added to the custom glossary table with an ordinary SQL INSERT statement. The custom glossary table is always named gls_glossary and is created in the database that is used for the initial execsql connection. The columns of this table are identical to the columns of the master glossary table. The custom glossary table has a primary key defined on the column containing the term, so multiple definitions cannot be added for the same term.

Side Effects

The scripts create two persistent objects in the database to which execsql initially connects:

• A table named gls_glossary that contains the custom glossary. This is a temporary table in Postgres, a permanent table in MariDB/MySQL and SQL Server.
• A view named glossary that returns the custom glossary in alphabetical order. This is a temporary view in Postgres, a permanent view in MariaDB/MySQL and SQL Server.

While they are operating, the scripts will create other temporary tables and views in both the initial database and in the master glossary database. The scripts also create several global execsql substitution variables. All such objects that are created have the prefix “gls_”.

Limitations

Following are a few limitations on the conditions under which these scripts can be used.

1. The scripts create several global substitution variables as well as tables and views. The prefix “gls_” is used for all of these except for the glossary view. That prefix should not be used for other variables, tables, or views to avoid possible conflicts.
2. Table names and column names for the master and custom glossary tables should not require double-quoting.
3. The DBMS’s case-folding behavior should be respected when specifying table and column names because the scripts use the information_schema tables, and if the case of the names provided do not match the case as stored in the information_schema tables by the DBMS, the scripts will not work correctly.
4. The user used for the connection to the master glossary database must have permissions to create regular and temporary tables and views in that database. Only SELECT permission is needed for the master glossary table itself.
5. The MariaDB/MySQL implementation does not add columns from temporary tables or views because the DBMS does not list the columns for those objects in the information_schema tables.