View on GitHub

ClassDB

An open-source system to let students experiment with relational data

ClassDB Home | Table of Contents

Scripts

Author: Steven Rollo

This documents the scripts included in ClassDB. A description of each script, what permissions it requires, and when to use it are provided. The directory structure of the ClassDB repository is also provided.

Directory Structure

The src folder is organized in a hierarchy designed to clarify the usage of each ClassDB script. There are two top level directories:

Name Description
db The scripts in this folder contain the database-level components of ClassDB. They must be run once per database.
server The scripts in this folder contain the server-level components of ClassDB. They only need to be ran once per server.

Each of these directories contains up to three sub-directories. Additionally, each contains install and uninstall script for all components located in the sub-directories.

Name Description
core These scripts install components that are required for ClassDB to function
opt These scripts install components that are useful, but are not necessary to use ClassDB to its full potential
reco These scripts install components that significantly increase the utility of ClassDB, but are not necessary for ClassDB to function

Database-Level

addAllToDB.psql

addAllToDB.psql installs all database level ClassDB components. This is useful if you want to perform a full installation without selecting individual components to install.

Core Scripts

addAllDBCore.psql

addAllDBCore.psql is a psql helper script that runs all of the core database-level installation scripts at once. It uses psql meta-commands to do this, so it must be run using psql. The .psql file extension was added to help distinguish it from normal sql scripts.

addClassDBRolesMgmtCore.sql

addClassDBRolesMgmtCore.sql creates functions for managing ClassDB users.

addClassDBRolesViewsCore.sql

addClassDBRolesViewsCore.sql creates views displaying each type of ClassDB user.

addHelpersCore.sql

addHelpers.sql creates several helper functions that are used internally by ClassDB.

addRoleBaseMgmtCore.sql

addRoleBaseMgmtCore.sql creates the base ClassDB role system, which all other ClassDB roles are based on.

addUserMgmtCore.sql

addUserMgmtCore.sql creates tables for logging information about ClassDB users.

initializeDBCore.sql

initializeDBCore.sql sets appropriate permissions for each ClassDB role on the current database, and creates the ClassDB schema.

Optional Scripts

addAllDBOpt.psql

addAllDBOpt.psql is a psql helper script that runs all of the optional database-level installation scripts at once.

addCatalogMgmtOpt.sql

addCatalogMgmtOpt.sql provides two helper functions intended to be used by students. These functions provide an easy way for a student to list all tables in a schema, and to get information about all columns in a given table.

Recommend Scripts

addAllDBReco.psql.psql

addAllDBReco.psql is a psql helper script that runs all of the recommend database-level installation scripts at once.

addDisallowSchemaDropReco.sql

addDisallowSchemaDropReco.sql provides functions to let instructors and dbmanagers control whether students can drop any schema. This script by default disallows students from dropping any schema. When schema-drop is disallowed, students are prevented from executing the statements DROP SCHEMA and DROP OWNED BY.

addConnectionActivityLoggingReco.sql

addConnectionActivityLoggingReco.sql provides a function to record connections made by ClassDB users to the DBMS in ClassDB.ConnectionActivity. This function imports Postgres’ external server logs to get connection records. enableConnectionLoggingReco.psql must also be run in order for connection logging to function.

addConnectionMgmtReco.sql

addConnectionMgmtReco.sql provides several function allowing instructors and dbmanagers to monitor and shutdown connections to the Postgres server.

addDDLActivityLoggingReco.sql

addDDLActivityLoggingReco.sql provides DDL statement logging for all ClassDB users. This script installs triggers that record every DDL statement performed by a ClassDB user in ClassDB.DDLActivity.

addFrequentViewsReco.sql

addFrequentViewsReco.sql provides several views accessible to ClassDB users summarizing user data and activity. A detailed description of each can be found here.

Server-Level

addAllToServer.psql

addAllToServer.psql installs all server level ClassDB components. This is useful if you want to perform a full installation without selecting individual components to install.

Core Scripts

addAllServerCore.psql

addAllServerCore.psql is a psql helper script that install all of the core server-level components at once.

initializeServerCore.sql

initializeServerCore.sql performs server level configuration for ClassDB, and should be run once per Postgres instance. It creates the five server-level roles that are used by ClassDB:

These roles are explained in detail in the Roles overview.

Recommend Scripts

addAllServerReco.psql

addAllServerReco.psql is a psql helper script that install all of the recommend server-level components at once.

enableConnectionLoggingReco.psql and disableConnectionLoggingReco.psql

enableConnectionLoggingReco.psql modifies the Postgres logging system configuration. These changes cause Postgres to log connections made to the DBMS in the external server logs. This is intended to be used with addConnectionActivityLoggingReco.sql to log connections made by ClassDB users to the DBMS. disableConnectionLoggingReco.psql turns off server connection logging. Thus, it only needs to be run if you want to disable connection logging after running enableConnectionLoggingReco.psql. We recomend that psql be used to run these scripts.

Example Schema

ClassDB also includes an example schema called Shelter. This schema contains a system for managing adoptions at a fictional animal shelter. It is intended as an example students can refer to. The schema is read-only for Students, while Instructors and DBManagers have read and write access. This example schema is not required, and is only intended as a teaching aid.

createShelterSchema.sql

This script creates the Shelter schema. It first creates a separate schema called shelter, then creates the schema tables. It also sets the appropriate permissions for the schema and tables. It should be run once per database

populateShelterSchema.sql

This script populates the shelter schema with sample data. Any user with write access to the shelter schema can execute this script. By default, that includes all Instructors and DBManagers. It should be run once per database.

dropShelterSchema.sql

This script removes the shelter schema and all objects contained within. It should be run once per database.

Uninstall Scripts

The following scripts are used to remove ClassDB components from a database or instance.

removeAllFromDB.sql

removeAllFromDB.sql removes all ClassDB database level components from a single database. This includes the ClassDB schema and all contained objects, the catalog management functions, and the DDL monitoring triggers. It makes efforts to not interfere with user data - user schemas are not removed, and the uninstall will fail if there are user-created objects dervided from ClassDB objects.

removeAllFromServer.sql

removeAllFromServer.sql removes all ClassDB server level components. This includes all the ClassDB roles. removeAllFromDB.sql must be run on all database ClassDB is installed on before removeAllFromServer.sql can be used.