NAME

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

VERSION

Version 0.115

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,
        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'.

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); (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 (
          scratch_sid  integer NOT NULL,
          intvl        tsrange NOT NULL,
          EXCLUDE USING gist (scratch_sid 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 accessors (scratch_sid and remark)

• 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/007-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)

• get_json function (get JSON string associated with a given SID)

For basic workflow, see t/007-schedule.t.

EXPORTS

This module provides the following exports:

get_json

METHODS

spawn

Constructor. See Employee.pm->spawn for general comments.

reset

Instance method. Resets object, either to its primal state (no arguments) or to the state given in PARAMHASH.

Accessor methods

Basic accessor methods for all the fields of Schedule table. These functions return whatever value happens to be associated with the object, with no guarantee that it matches the database.

sid

Accessor method.

schedule

Accessor method.

remark

Accessor method.

insert

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

update

There is no update method for schedules. To update a schedule, delete it and then re-create it (see Spec.pm for a description of how to do this, or refer to t/007-schedule.t).

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_sid

Given a SID, load the schedule into the object. Returns a status value.

FUNCTIONS

get_json

Given a database handle and a SID, queries the database for the JSON string associated with the SID. Returns undef if not found.

AUTHOR

Nathan Cutler, <presnypreklad@gmail.com>

cpanm App::Dochazka::REST

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