NAME

App::Dochazka::REST::Model::Schedule - schedule functions

SYNOPSIS

use App::Dochazka::REST::Model::Schedule;

...

DESCRIPTION

A description of the schedule data model follows.

Schedules in the database

Table

Schedules are stored the schedules table. For any given schedule, there is always only one record in the table -- i.e., individual schedules can be used for multiple employees. (For example, an organization might have hundreds of employees on a single, unified schedule.)

CREATE TABLE IF NOT EXISTS schedules (
  sid        serial PRIMARY KEY,
  schedule   text UNIQUE NOT NULL,
  disabled   boolean,
  remark     text
);

The value of the 'schedule' field is a JSON array which looks something like this:

[
    { low_dow:"MON", low_time:"08:00", high_dow:"MON", high_time:"12:00" },  
    { low_dow:"MON", low_time:"12:30", high_dow:"MON", high_time:"16:30" },  
    { low_dow:"TUE", low_time:"08:00", high_dow:"TUE", high_time:"12:00" },  
    { low_dow:"TUE", low_time:"12:30", high_dow:"TUE", high_time:"16:30" },
    ...
]   

Or, to give an example of a more convoluted schedule:

[   
    { low_dow:"WED", low_time:"22:15", high_dow:"THU", high_time:"03:25" }, 
    { low_dow:"THU", low_time:"05:25", high_dow:"THU", high_time:"09:55" },
    { low_dow:"SAT", low_time:"19:05", high_dow:"SUN", high_time:"24:00" } 
] 

The intervals in the JSON string must be sorted and the whitespace, etc. must be consistent in order for the UNIQUE constraint in the 'schedule' table to work properly. However, these precautions will no longer be necessary after PostgreSQL 9.4 comes out and the field type is changed to 'jsonb'.

The 'disabled' field is intended go be used to control which schedules get offered in, e.g., front-end dialogs when administrators choose which schedule to assign to a new employee, and the like. For example, there may be schedules in the database that were used in the past, but it is no longer desirable to offer these schedules in the front-end dialog, so the administrator can "remove" them from the dialog by setting this field to 'true'.

Process for creating new schedules

It is important to understand how the JSON string introduced in the previous section is assembled -- or, more generally, how a schedule is created. Essentially, the schedule is first created in a schedintvls table, with a record for each time interval in the schedule. This table has triggers and a gist index that enforce schedule data integrity so that only a valid schedule can be inserted. Once the schedule has been successfully built up in schedintvls, it is "translated" (using a stored procedure) into a single JSON string, which is stored in the schedules table. This process is described in more detail below:

First, if the schedule already exists in the schedules table, nothing more need be done -- we can skip to Schedhistory

If the schedule we need is not yet in the database, we will have to create it. This is a three-step process: (1) build up the schedule in the schedintvls table (sometimes referred to as the "scratch schedule" table because it is used to store an intermediate product with only a short lifespan); (2) translate the schedule to form the schedule's JSON representation; (3) insert the JSON string into the schedules table.

The schedintvls, or "scratch schedule", table:

CREATE SEQUENCE scratch_sid_seq;

CREATE TABLE IF NOT EXISTS schedintvls (
  int_id  serial PRIMARY KEY,
  ssid    integer NOT NULL,
  intvl   tsrange NOT NULL,
  EXCLUDE USING gist (ssid WITH =, intvl WITH &&)
)/,

As stated above, before the schedule table is touched, a "scratch schedule" must first be created in the schedintvls table. Although this operation changes the database, it should be seen as a "dry run". The gist index and a trigger assure that:

• no overlapping entries are entered

• all the entries fall within a single 168-hour period

• all the times are evenly divisible by five minutes

# # FIXME: expand the trigger to check for "closed-open" [ ..., ... ) tsrange #

If the schedule is successfully inserted into schedintvls, the next step is to "translate", or convert, the individual intervals (expressed as tsrange values) into the four-key hashes described in "Schedules in the database", assemble the JSON string, and insert a new row in schedules.

To facilitate this conversion, a stored procedure translate_schedintvl was developed.

Successful insertion into schedules will generate a Schedule ID (SID) for the schedule, enabling it to be used to make Schedhistory objects.

At this point, the scratch schedule is deleted from the schedintvls table.

Schedules in the Perl API

Schedintvls class

• constructor (spawn)

• reset method (recycles an existing object)

• basic accessor (ssid)

• intvls accessor (arrayref containing all tsrange intervals in schedule)

• schedule accessor (arrayref containing "translated" intervals)

• load method (load the object from the database and translate the tsrange intervals)

• insert method (insert all the tsrange elements in one go)

• delete method (delete all the tsrange elements when we're done with them)

• json method (generate JSON string from the translated intervals)

For basic workflow, see t/model/schedule.t.

Schedule class

• constructor (spawn)

• reset method (recycles an existing object)

• basic accessors (sid, schedule, remark)

• insert method (inserts the schedule if it isn't in the database already)

• delete method

• load method (not implemented yet)

  #=item * get_schedule_json function (get JSON string associated with a given SID) # =back

  For basic workflow, see t/model/schedule.t.

EXPORTS

This module provides the following exports:

#=item * get_schedule_json # =item * get_all_schedules

* sid_exists (boolean)

METHODS

insert

Instance method. Attempts to INSERT a record into the 'schedules' table. Field values are taken from the object. Returns a status object.

If the "schedule" field of the schedule to be inserted matches an existing schedule, no new record is inserted. Instead, the existing schedule record is returned. In such a case, the "scode", "remark", and "disabled" fields are ignored - except when they are NULL in the existing record.

update

Although we do not allow the 'sid' or 'schedule' fields to be updated, schedule records have 'scode', 'remark' and 'disabled' fields that can be updated via this method.

delete

Instance method. Attempts to DELETE a schedule record. This may succeed if no other records in the database refer to this schedule.

load_by_scode

Analogous function to "load_by_aid" in App::Dochazka::REST::Model::Activity.

load_by_sid

Analogous function to "load_by_aid" in App::Dochazka::REST::Model::Activity.

FUNCTIONS

sid_exists

Boolean function

get_all_schedules

Returns a list of all schedule objects, ordered by sid. Takes one argument - a paramhash that can contain only one key, 'disabled', which can be either true or false (defaults to true).

AUTHOR

Nathan Cutler, <presnypreklad@gmail.com>

cpanm App::Dochazka::REST

perl -MCPAN -e shell
install App::Dochazka::REST