CSCF Course Resource Data

We record our understanding of what computing resources instructors need. It is used to determine if we have the right software installed, to email instructors each term asking them about their needs, and to make the information visible in WWW pages (TBD).


The data is stored in a relational database, for ease of access by software, and a text file, for ease of updating by humans. The text file is considered authoritative.

The text file is

linux.cscf:/software/courses-cscf/data/config/sponsorships/Term     Bill had said linux.cs
where term names are of the form [WSF]\d\d\d\d, e.g.
cp F2015 W2016

When the file is updated, the database is updated by:

crdb-in /software/courses-cscf/data/config/sponsorships/Term | psql -h postgres.cs -d postgres -f /dev/stdin
which assumes that you have a login to the database, and that it has the needed permissions, by being a member of the "_cscf_root" role. It's a PostgreSQL database and the Who Does What list suggests (as of 2015-08) that Isaac can provide the needed access. The access approach is an example of the general approach to access to public data.

The software used is on linux.cs, in

A simple description of command options is available via the "-v -h" options for each command. Details of a primary use may be found in description of the start of term email to instructors. The command
provides a dump of the current data, in tab separated columns, with one row for each resource.

The Text Form of the Data

The text file is a set of multiline directives. Indented text is considered to be part of the previous line. Comments are allowed, starting with "#", both as complete lines and appended to data lines.

A typical example of both single line and multiline syntax is:

course cs487 wants the software Maple in the student/Unix environment.
course cs488 wants the software
    Gtkmm 2.4.x,
    Lua 5.1.2,
    OpenGL,      # is there a specific version needed ?
    "Python 2.x",
    freeglut3, freeglut3-dev,
    glc-capture, glc-play,
    "gtkglextmm 1.0.*",
    libSDL, libSDL_mixer,
    qt5         (deb:qt5, deb:qt5-qmake, deb:qtbase5-dev-tools),
    AntTweakBar (ST #101410,,
    GLFW        (ST #101410,,
    Premake5    (ST #101410,
  in the student/lab/graphics environment.

The general syntax is:

 <sponsor> wants the <resources> 

A <sponsor> is one of:

  course <course_name>
  userid <userid>
  event <event>
Courses can be pluralized, with a list of comma and slash separated courses, e.g.
course cs488/688 wants ...
courses cs371, cm271, amat341 wants ...

<resources> is one of:

  software <software> [somewhere | in the <environment> environment].
  <environment> environment.
  userid <userid>.
<software> is either a comma separated list of software names, or a pair of software names separated by "and". Each software name can have a "hint" about where to find the software, and/or the ST number of why it's needed. The <hint> is parenthesized text.
<environment> is a computing environment using the internal form of the names that ST uses:
Environment Description
student/Unix linux.student.cs
student/WWW www.student.cs
student/lab/graphics the graphics lab (MC3007)
student/lab/mac the Mac labs
student/lab/networks the Networks lab (MC3007a)
student/lab/realtime the Realtime lab (MC3022)
student/utility the Ugsters

A dump of the database in text form can be had by


The Database Form of the Data

The database resides on postgres.cs in the _cscf schema. There are three tables, each owned by _cscf_root, with read access for _cscf_web. The

  course-sql -d CSCF
command provides direct SQL access to it. The SQL to create the tables, enums, types, and functions used resides in
There are some other handy SQL fragments as well, documented in

The tables describe "sponsors", the "resources" they can sponsor, and the many-to-many relationship between the two. There are SQL comments associated with fields and tables and types. The following descriptions are just copies of what the '\\d' command in `psql` shows ('\\d+' is too wide for this TWiki page).


The table "sponsors" describes who or what is causing a resource to exist. It's almost always a course. Later it might expand to include instructors that have specific preferences. It is defined as:

postgres=> \d _cscf.sponsors
                            Table "_cscf.sponsors"
 Column  |    Type     |                       Modifiers                       
 id      | integer     | not null default nextval('sponsors_id_seq'::regclass)
 type    | sponsortype | not null
 sponsor | text        | not null
    "sponsors_pkey" PRIMARY KEY, btree (id)
    "sponsors_type_sponsor_key" UNIQUE CONSTRAINT, btree (type, sponsor)
Referenced by:
    TABLE "sponsorship" CONSTRAINT "sponsorship_sponsor_id_fkey" FOREIGN KEY (sponsor_id) REFERENCES sponsors(id)

postgres=> select unnest(enum_range(NULL::sponsorType)) as sponsorType;

An example of the data:

postgres=> select * from _cscf.sponsors order by type,sponsor limit 2;
 id |  type  | sponsor 
  1 | course | amat341
  2 | course | amat740


The table "resources" describes what a sponsor can cause to exist. It's almost always a software need. It is defined as:

postgres=> \d _cscf.resources

                               Table "_cscf.resources"
  Column  |       Type       |                       Modifiers                  
 id       | integer          | not null default nextval('resources_id_seq'::regclass)
 type     | resourcetype     | not null
 resource | text             | not null
 location | resourcelocation | not null default ''::resourcelocation
 userid   | text             | not null default ''::text
 hint     | resourcehint     | not null default ''::text
    "resources_pkey" PRIMARY KEY, btree (id)
    "resources_type_resource_location_userid_hint_key" UNIQUE CONSTRAINT, btree (type, resource, location, userid, hint)
Referenced by:
    TABLE "sponsorship" CONSTRAINT "sponsorship_resource_id_fkey" FOREIGN KEY (resource_id) REFERENCES resources(id)

postgres=> \dT
                                              List of data types
 Schema |       Name       |                                   Description      
 _cscf  | client_type      | the client, group of clients, or activity that one can be a Point of Contact for
 _cscf  | resourcehint     | an environment specific package name if resourceType is "software"
 _cscf  | resourcelocation | where the resource is to be instantiated, if not deducible
 _cscf  | resourcetype     | about the "resource" value:                                                     +
        |                  |   software: the well known name of a software package/system                    +
        |                  |   userid: the standard userid to allocate, or a pathname of userids             +
        |                  |   group: the name of a group, with possible membership of the "userid" field    +
        |                  |   
 _cscf  | sponsortype      | the type of sponsor

postgres=> select unnest(enum_range(NULL::resourcelocation)) as resourcelocation;
postgres=> select unnest(enum_range(NULL::resourceType)) as resourceType;

An example of the data:

postgres=> select * from _cscf.resources limit 2;
 id |   type   |     resource      |    location     | userid | hint 
  1 | software | Acrobat Reader    | student/lab/mac |        | 
  2 | software | Adobe Illustrator | student/lab/mac |        | 


The "sponsorship" table describes the relationships between sponsors and resources. Each row provides the term to which it applies. We want it to sort easily, so it's a date (of the first day of the appropriate month).

Each of the sponsors and resources tables have a primary id that increments, used in this table.

postgres=> \d _cscf.sponsorship
      Table "_cscf.sponsorship"
    Column     |  Type   | Modifiers 
 sponsor_id    | integer | not null
 resource_id   | integer | not null
 when_term     | date    | not null
 documentation | text    | 
    "sponsorship_pkey" PRIMARY KEY, btree (sponsor_id, resource_id, when_term)
Foreign-key constraints:
    "sponsorship_resource_id_fkey" FOREIGN KEY (resource_id) REFERENCES resources(id)
    "sponsorship_sponsor_id_fkey" FOREIGN KEY (sponsor_id) REFERENCES sponsors(id)

As such, the raw data doesn't visualize well. To help with that, there's some SQL to show the tables, and then show the result of applying the sponsorships.

postgres=> \i data/sql/show
-------------------------------------------------------------- sponsors
 id |  type  | sponsor 
  1 | course | amat341
  2 | course | amat740
  3 | course | cm271
  4 | course | cm375
  5 | course | cm433
  6 | course | cm770
  7 | course | cs100
  8 | course | cs115

-------------------------------------------------------------- resources
 id  |   type   |                   resource                    |       location | userid | hint 
   1 | software | Acrobat Reader                                | student/lab/mac      |        | 
   2 | software | Adobe Illustrator                             | student/lab/mac      |        | 
   3 | software | Adobe Photoshop Elements CS5                  | student/lab/mac      |        | 
   4 | software | Apache                                        | student/lab/mac      |        | 

-------------------------------------------------------------- sponsorship
 sponsor_id | resource_id | when_term  | documentation 
         67 |          24 | 2015-09-01 | 
         67 |          25 | 2015-09-01 | 
         67 |          33 | 2015-09-01 | 
         67 |          48 | 2015-09-01 | 
         67 |          56 | 2015-09-01 | 

-------------------------------------------------------------- result
  type  | sponsor |   type   |                   resource                    |  location             |    term    
 course | amat341 | software | Matlab                                        | student/Unix          | 2015-09-01
 course | amat740 | software | Matlab                                        | student/Unix          | 2015-09-01
 course | cm271   | software | Matlab                                        | student/Unix          | 2015-09-01
 course | cm375   | software | Matlab                                        | student/Unix          | 2015-09-01
 course | cm433   | software | Maple                                         | student/Unix          | 2015-09-01

The Basic Query

The basic query to join the three tables is:

select sponsors.type, sponsors.sponsor,
       resources.type, resources.resource,
       resources.location, resources.userid, resources.hint
from sponsors, resources, sponsorship where
    sponsorship.sponsor_id =
    sponsorship.resource_id  =
    sponsorship.when_term = '$term_date'
for some choice of $term_date, e.g. '2015-09-01'.

If you're using Perl to access the data, this can be seen in /software/courses-cscf/lib/ in the &sponsorships() function.

Updating the Schema

Sometimes it's necessary to change the schema, typically by modifying the ResourceLocation to include a new environment. The first step is to update /software/courses-cscf/data/sql/schema. Then

  • run /software/courses-cscf/data/sql/init to delete the old schema (and data) and create a new empty schema,
  • initialize the new schema as described above (using `crdb-in`).
Or, if it's just a simple addition, run (for example)
alter type ResourceLocation add value 'student/lab/mobile' after 'student/lab/realtime';
Topic revision: r8 - 2017-11-23 - AdrianPepper
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback