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).

Summary

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

/software/courses-cscf/maintenance/

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

/software/courses-cscf/maintenance/course-software-sum

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
    Gtkglext,
    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.*",
    libOpenAL,
    libSDL, libSDL_mixer,
    libboost-dev,
    libbullet-dev,
    libglfw-dev,
    libpng12,
    qt5         (deb:qt5, deb:qt5-qmake, deb:qtbase5-dev-tools),
    AntTweakBar (ST #101410, http://anttweakbar.sourceforge.net/doc/),
    GLFW        (ST #101410, http://www.glfw.org/),
    Premake5    (ST #101410, https://premake.github.io/download.html)
  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

crdb-out

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

  /software/courses-cscf/data/sql/schema 

There are some other handy SQL fragments as well, documented in

  /software/courses-cscf/data/sql/.ReadMe 

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).

Sponsors

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
Indexes:
    "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;
 sponsortype
--------
 course
 userid

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

Resources

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
Indexes:
    "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;
   resourcelocation
----------------------
 student
 student/Unix
 student/WWW
 student/utility
 student/lab/mac
 student/lab/graphics
 student/lab/networks
 student/lab/realtime
 
postgres=> select unnest(enum_range(NULL::resourceType)) as resourceType;
 resourcetype 
--------------
 software
 userid
 group

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 |        | 

Sponsorships

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    | 
Indexes:
    "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 = sponsors.id
and
    sponsorship.resource_id  = resources.id
and
    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/course_database.pm 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';